import csv, os

from szkolenie_python_podstawy.constants import PROJECT_PATH

from mysql.connector import connect, CMySQLConnection
from mysql.connector.cursor_cext import CMySQLCursor

szkolenie_wroclaw_db: CMySQLConnection = connect(
    host='localhost',
    port='3306',
    user='root',
    password='test1test'
)

db_cursor: CMySQLCursor = szkolenie_wroclaw_db.cursor()
db_cursor.execute('USE uczelnia')

statement_create_table_pattern = """create table if not exists przedmioty
(
    {} varchar(12)  null,
    {} varchar(30)  null,
    {} timestamp    null,
    {} varchar(30)  null,
    {} varchar(1)   null,
    {} varchar(64)  null,
    {} int          null,
    {} varchar(150) null,
    {} varchar(10)  null,
    {} varchar(100) null
)
"""

statement_pattern = """INSERT INTO uczelnia.przedmioty (CDYD_KOD, PRZ_KOD, UTW_DATA, TPRO_KOD, UCZESTNICY, GUID, PRAC_ID, NAZWA, JED_ORG_KOD, NAME) VALUES ('{}', '{}', '{}', '{}', '{}', '{}', {}, '{}', '{}', '{}')"""

with open(os.path.join(PROJECT_PATH, 'przed_cykli.csv'), mode='r', encoding='windows-1250') as przedmioty_file:
    reader = csv.DictReader(przedmioty_file, delimiter=',')
    statement_create_table_pattern = statement_create_table_pattern.format(
        *reader.fieldnames
    )
    db_cursor.execute(statement_create_table_pattern)
    szkolenie_wroclaw_db.commit()
    for row in reader:
        for field in ['NAME', 'NAZWA']:
            if '\'' in row[field]:
                row[field] = row[field].replace('\'', '\'\'')
        statement = statement_pattern.format(*tuple(row.values()))
        db_cursor.execute(statement)
    szkolenie_wroclaw_db.commit()