import constants
from db import MySQLConnection

# main_db.py - Program entry point for Part 1 Database

tbl_programme = """
    CREATE TABLE IF NOT EXISTS programmes (
        programme_id VARCHAR(9) NOT NULL,
        programme_name VARCHAR(100) NOT NULL,
        PRIMARY KEY (programme_id)
    )"""

tbl_module = """
    CREATE TABLE IF NOT EXISTS modules (
        module_id VARCHAR(7) NOT NULL,
        module_name VARCHAR(100) NOT NULL,
        module_credit INT,
        PRIMARY KEY (module_id)
    )"""

tbl_mapping = """
    CREATE TABLE IF NOT EXISTS mappings (
        mapping_programme_id VARCHAR(9) NOT NULL,
        mapping_semester INT NOT NULL,
        mapping_module_id VARCHAR(7) NOT NULL,
        PRIMARY KEY (mapping_programme_id, mapping_semester, mapping_module_id),
        FOREIGN KEY (mapping_programme_id) REFERENCES programmes(programme_id),
        FOREIGN KEY (mapping_module_id) REFERENCES modules(module_id)
    )"""

radius = (('circle', 1.0),('circle', 2.0),('circle', 3.0))
query = "INSERT INTO circles (name, radius) VALUES (%s, %s)"

insert_programmes = (
    ('IT114124', "Higher Diploma in AI and Smart Technology"),
    ('IT114122', "Higher Diploma in Cybersecurity")
)
insert_programme_query = "INSERT INTO programmes (programme_id, programme_name) VALUES (%s, %s)"

insert_modules = (
    ("LAN3100","English & Communication: Workplace Interaction","6"),
    ("LAN3103","English & Communication: Workplace Correspondance","6"),
    ("LAN4107","English & Communication: Reports","9"),
    ("LAN4108","English & Communication: Persuasive Presentations","9"),
    ("LAN4101","English & Communication: Promotional Materials","10"),
    ("LAN3003","Vocational Chinese Communication: Putonghua Conversation and Reports","6"),
    ("LAN4003","Vocational Chinese Communication: Putonghua Presentations, Administrative and Technical Text Writing","9")
)

insert_modules_query = "INSERT INTO modules (module_id, module_name, module_credit) VALUES (%s, %s, %s)"

insert_mappings = (
    ("IT114124","1","LAN3100"),
    ("IT114124","1","LAN3103"),
    ("IT114124","1","LAN3003"),
    ("IT114124","2","LAN4107"),
    ("IT114124","2","LAN4003"),
    ("IT114124","4","LAN4108"),
    ("IT114124","5","LAN4101"),
    ("IT114122","1","LAN3100"),
    ("IT114122","1","LAN3103"),
    ("IT114122","1","LAN3003"),
    ("IT114122","2","LAN4107"),
    ("IT114122","2","LAN4003"),
    ("IT114122","4","LAN4108"),
    ("IT114122","5","LAN4101")
)

insert_mappings_query = "INSERT INTO mappings (mapping_programme_id, mapping_semester, mapping_module_id) VALUES (%s, %s, %s)"

if __name__ == "__main__":
    db = MySQLConnection(
        host=constants.DB_HOST,
        user=constants.DB_USER,
        password=constants.DB_PASS
    )

    with db.cursor() as cursor:
        # Recreate database and select as default
        cursor.execute(f"DROP DATABASE IF EXISTS {constants.DB_NAME};")
        cursor.execute(f"CREATE DATABASE {constants.DB_NAME};")
        cursor.execute(f"USE {constants.DB_NAME};")

        # Tables
        cursor.execute(tbl_programme)
        cursor.execute(tbl_module)
        cursor.execute(tbl_mapping)

        # Records
        # cursor.executemany(query, radius)
        cursor.executemany(insert_programme_query, insert_programmes)
        cursor.executemany(insert_modules_query, insert_modules)
        cursor.executemany(insert_mappings_query, insert_mappings)

    db.commit()
    db.close()



