from faker import Faker
import random

NUM_EMPLOYEES = 200
FILENAME = "join_corporation.sql"


fake = Faker()

class Employee:
    def __init__(self, _id, fname, lname, birthday, reports):
        self.id = _id
        self.first_name = fname
        self.last_name = lname
        self.birthday = birthday
        self.reports_to = reports

    def __repr__(self):
        if self.reports_to:
            return str((str(self.id), self.first_name, self.last_name, f"{self.birthday:%Y-%m-%d}", str(self.reports_to)))
        else:
            return str((str(self.id), self.first_name, self.last_name, f"{self.birthday:%Y-%m-%d}", 'NULL'))

def generate_employees():
    employees = []
    for i in range(NUM_EMPLOYEES):
        employees.append(Employee(i+1, fake.first_name(), fake.last_name(), fake.date_of_birth(), None))
    return employees

def populate_hierarchy(employees):
    def assign_subordinates(current, employees):
        if len(employees) == 0:
            return
        else:
            if len(employees) <= 2:
                num_subs = len(employees)
            else:
                num_subs = random.randint(2, min(5, len(employees)))
            subs = random.sample(employees, k=num_subs)
            for sub in subs:
                sub.reports_to = current.id
                employees.remove(sub)
            for sub in subs:
                assign_subordinates(sub, employees)

    boss = random.choice(employees)
    working_copy = employees.copy()
    working_copy.remove(boss)
    assign_subordinates(boss, working_copy)


def print_table(name, data, columns, types):
    print(f"CREATE TABLE {name} (") #)
    cols = [f"  {c} {t}" for c,t in zip(columns, types)]
    print(",\n".join(cols))
    print(");")

    print(f"INSERT INTO {name} VALUES")
    print(",\n".join(map(str,data)))
    print(";")



if __name__ == '__main__':
    employees = generate_employees()
    populate_hierarchy(employees)
    print(employees)
    print("CREATE SCHEMA lec8;")
    print_table("lec8.employees", employees, ["id", "first_name", "last_name", "birthday", "reports_to"], ["TEXT", "TEXT", "TEXT", "DATE", "TEXT"])
    # print_table("lec7.hw_info", assignments, ["id", "name", "total_points", "due_date"], ["TEXT", "TEXT", "INT", "DATE"])
    # print_table("lec7.submissions", submissions, ["student_id", "hw_id", "submission_date", "earned_points"], ["TEXT", "TEXT", "DATE", "INT"])