# Functions for filling the database with garbage.
# DELETE CONTENTS OF DB BEFORE EXPORTING!
# 

# Imports
from faker import Faker
import MySQLdb
import random, datetime
# create faker
m_fake = Faker()
arab_fake = Faker(['ar_AA'])
# Connection information to localhosted DB
# MUST BE RUNNING (Don't forget to start your db server)
db_connection = {
    'host': 'localhost',
    'user': 'root',
    'password': '', #No pass on my root (fix for your machine)
    'port': 3306,
    'db': 'ProjectDB'
}
# Connection
m_conn = MySQLdb.connect(
    host=db_connection['host'],
    user=db_connection['user'],
    passwd=db_connection['password'],
    db=db_connection['db'],
    port=db_connection['port']
)

def createFakeData():
    print("Starting createDB function")
    tables = [
        'department',
        'company',
        'priority',
        'direction',
        'objective'
        'supporters', 
        'stakeholders',
        'milestones',
        'KPI',
        'projecttype',
        'project'
    ]
    fill_table(tables[0], get_table_fields(tables[0]), 16, create_names)
    fill_table(tables[1], get_table_fields(tables[1]), 20, create_names)
    


def fill_table(table_name: str, field_names: list, nb_entries: int, creator_func):
    sql_query = f'INSERT INTO {table_name} ('
    for i in field_names:
        sql_query += i
    sql_query += f') VALUES ' + create_query(creator_func, nb_entries, table_name)
    print(sql_query)
    # The printed outputs will be put into an SQL script and executed within DBeaver

def create_query(single_query, nb_entries: int, p_type: str):
    """
    Creates a query given a function for creating a singluar entry
    """
    qualified_query = ''
    for i in range(0, nb_entries):
        qualified_query += f'({single_query(p_type)})'
        if (i < nb_entries-1):
            qualified_query += ', '
    return qualified_query

def get_table_fields(table_name: str):
    """
    Get the field_names from the specified table
    """
    cursor = m_conn.cursor()
    cursor.execute(f'SELECT * FROM {table_name} LIMIT 0')
    field_names = [ i[0] for i in cursor.description ]
    cursor.execute(f'SHOW KEYS FROM {table_name} WHERE Key_name = \'PRIMARY\'')
    n = 0
    for i in cursor.description:
        if (i[0] == 'Column_name'):
            break
        n += 1 
    primary_keys = [i[n] for i in cursor]
    field_names = [i for i in field_names if i not in primary_keys]
    return field_names

def create_names(p_type: str):
    """
    returns random name generated by the function
    """
    faker_split = m_fake.sentence().split(' ')
    name = faker_split[0] 
    hasExtra = random.randint(0,2)
    if (hasExtra > 0 and p_type == 'company'):
        name += " " + faker_split[1][:1].upper() + faker_split[1][1:] 
    if (hasExtra > 1 and len(faker_split) > 1 and p_type == 'company'):
        faker_split[2] =  faker_split[2][:1].upper() + faker_split[2][1:]
        faker_split[2] = faker_split[2].replace(".", '')
        name += " " + faker_split[2]
    return f'\'{name}\''

def create_strategicKPI():
    stratkpi = 'INSERT INTO projectdb.strategickpi (kpi, objectiveid) VALUES \n'
    for _ in range(16):
        stratkpi += f'(\'{m_fake.sentence()}\', {random.randint(8,16)}),\n'
    print(stratkpi)

def create_bd_amount(nb_slices, amount):
    if (nb_slices <= 1):
        return [amount]
    else:
        mslice = int(amount/random.randint(1, 100))
        return [mslice] + create_bd_amount(nb_slices-1, amount-mslice)

def create_stakeholder(start=0, nb_projects=5):
    stake_sql = 'INSERT INTO projectdb.stakeholder (departmentid, companyid, projectid) VALUES\n'
    for i in range(start, nb_projects+start):
        for _ in range(random.randint(1,4)):
            if random.randint(0,1) == 1:
                stake_sql += f'(NULL,{random.randint(1,20)},{i+1}),\n'
            else:
                stake_sql += f'({random.randint(1,16)}, NULL ,{i+1}),\n'
    print(stake_sql)

def create_supporters(start=0 , nb_projects=50):
    sup_sql = 'INSERT INTO projectdb.supporter (departmentid, projectid) VALUES\n'
    for i in range(start, nb_projects+start):
        used_dept = []
        for _ in range(1, random.randint(2,10)):
            dept = random.randint(1,16)
            if (dept not in used_dept):
                sup_sql += f'({dept}, {i+1}, \'{m_fake.sentence()}\'),\n'
                used_dept.append(dept)
    sup_sql = sup_sql[:-2] +';\n'
    print(sup_sql)

def create_projects(start=0, nb_projects=5):
    strat = get_table_entries('strategickpi')
    constraints = {}
    # Creates a project which is properly formatted.
    p_sql = 'INSERT INTO projectdb.project (name, owner, objectiveid, description, budget, projecttype, projectscope, vision, weight, survery) VALUES\n'
    p_kpi_sql = 'INSERT INTO projectdb.projectkpi (projectid, kpi) VALUES\n'
    stratkpi_p_sql = 'INSERT INTO projectdb.projectstrategickpi (projectid, strategickpiid) VALUES\n'
    b_bd_sql = 'INSERT INTO projectdb.budgetbreakdown (projectid, bdamount, bddescr) VALUES\n'
    mile_sql = 'INSERT INTO projectdb.milestone (projectid, description, deadline, startdate) VALUES\n'
    stake_sql = 'INSERT INTO projectdb.stakeholder (departmentid, companyid, projectid) VALUES\n'
    for i in range(start, nb_projects+start):
        desc = f'\'{m_fake.sentence()}\''
        name = f'\'{arab_fake.sentence()}\''
        budget = random.randint(5000, 500000)
        owner = random.randint(1,16)
        objectiveid = random.randint(1,16)
        projecttype = random.randint(1,12)
        projectscope = f'\'{m_fake.sentence()}\''
        vision = f'\'{m_fake.sentence()}\''
        weight = random.randint(0, 100)
        survery = False
        if random.randint(0,1) == 0:
            survery = True 
        p_sql += f'({name}, {owner}, {objectiveid}, {desc}, {budget}, {projecttype}, {projectscope}, {vision}, {weight}, {survery}),\n'
        p_kpi_sql += f'({i+1}, \'{m_fake.sentence()}\'),\n'
        for el in strat:
            if el[2] == objectiveid and f'{i+1}' not in constraints:
                stratkpi_p_sql += f'({i+1}, {objectiveid}),\n'
                constraints[f'{i+1}'] = [objectiveid]
            elif el[2] == objectiveid and objectiveid not in constraints[f'{i+1}']:
                stratkpi_p_sql += f'({i+1}, {objectiveid}),\n'
                constraints[f'{i+1}'] = constraints[f'{i+1}'] + [objectiveid]
        deadline = m_fake.date()
        d1 = datetime.datetime.strptime(deadline, "%Y-%m-%d")

        mile_sql += f'({i+1}, \'{m_fake.sentence()}\', \'{deadline}\', \'{m_fake.date(end_datetime=d1)}\'),\n'
        # Create budget breakdown and stake holders
        bd_num = random.randint(0, 5)
        amlist = create_bd_amount(bd_num, budget)
        for en in amlist:
            budget = budget - en
        if budget >= 0 :
            amlist[0] += budget
        else:
            print("==============ERROR==========")
        for en in amlist:
            b_bd_sql += f'({i+1},{en}, \'{m_fake.sentence()}\'),\n'

        for _ in range(random.randint(1,4)):
            if random.randint(0,1) == 1:
                stake_sql += f'(NULL,{random.randint(1,20)},{i+1}),\n'
            else:
                stake_sql += f'({random.randint(1,16)}, NULL ,{i+1}),\n'
    # remove every extra line and comma
    p_sql = p_sql[:-2] +';\n'
    p_kpi_sql = p_kpi_sql[:-2] +';\n'
    stratkpi_p_sql = stratkpi_p_sql[:-2] +';\n'
    b_bd_sql = b_bd_sql[:-2] +';\n'
    mile_sql = mile_sql[:-2] +';\n'
    stake_sql = stake_sql[:-2] +';\n'
    print(p_sql)
    print(p_kpi_sql)
    print(stratkpi_p_sql)
    print(b_bd_sql)
    print(mile_sql)
    print(stake_sql)
    
    

def get_table_entries(table_name):
    cur = m_conn.cursor()
    cur.execute(f'SELECT * FROM {table_name}')
    query = []
    entries = cur.fetchall()
    for entry in entries:
        query.append(entry)
    return query

if __name__=='__main__':
    print("Started!")
    get_table_entries()
    m_conn.close()