import sqlite3
from loguru import logger
import pandas as pd

ZTMZ_DB_PATH = '/mnt/c/Users/Admin/lab/ztmz/ztmz_pacenote/src/ZTMZ.PacenoteTool.Base/data.zdb'
RBR_DB_PATH = '/mnt/c/Users/Admin/lab/ztmz/ztmz_pacenote/src/ZTMZ.PacenoteTool.RBR/jannemod_v3.zdb'
RBR_CSV_PATH = '/mnt/c/Users/Admin/lab/ztmz/ztmz_pacenote/src/ZTMZ.PacenoteTool.RBR/Jannemod V3.csv'

def main():
    conn_ztmz = sqlite3.connect(ZTMZ_DB_PATH)
    conn_rbr = sqlite3.connect(RBR_DB_PATH)

    c_ztmz = conn_ztmz.cursor()
    res = c_ztmz.execute('SELECT id, filename FROM pacenote_filenames')
    pacenote_filenames = res.fetchall()
    ztmz_filename2id = {filename: id for id, filename in pacenote_filenames}
    logger.info(f'loaded {len(ztmz_filename2id)} ztmz pacenote filenames')

    df_rbr_pacenote = pd.read_csv(RBR_CSV_PATH)
    df_rbr_pacenote['ZTMZSequence'] = df_rbr_pacenote['ZTMZSequence'].fillna('')

    rbr_pacenote = []
    rbr_pacenote_ztmz = []
    rbr_modifier_ztmz = []

    # check if all ZTMZSequence are in ztmz
    valid_seqs = 0
    for i, row in df_rbr_pacenote.iterrows():
        rbr_pacenote.append((row["Id"], row['Pacenote'], row['Description'], row['Category']))
        if row['ZTMZSequence']:
            seqs = row['ZTMZSequence'].split('>')
            for seq in seqs:
                if seq not in ztmz_filename2id:
                    logger.error(f'fallback {seq} not found in ztmz, on row: {row["Id"]}: {row["Pacenote"]}, {row["Category"]}, {row["Description"]}')
                    raise Exception(f'fallback {seq} not found in ztmz')
                    pass
                else:
                    # ready to insert
                    pass
            valid_seqs += len(seqs)
            if row['Category'] == 'MODIFIER':
                rbr_modifier_ztmz.extend([(row['Id'], ztmz_filename2id[seq], order_id) for order_id, seq in enumerate(seqs)])
            else:
                rbr_pacenote_ztmz.extend([(row['Id'], ztmz_filename2id[seq], order_id) for order_id, seq in enumerate(seqs)])
    
    logger.info(f'valid sequences: {valid_seqs} out of {len(df_rbr_pacenote)}')

    c_rbr = conn_rbr.cursor()
    c_rbr.executemany('INSERT INTO pacenote(`id`, `name`, `description`, `category`) VALUES (?, ?, ?, ?)', rbr_pacenote)
    c_rbr.executemany('INSERT INTO pacenote_ztmz(`id`, `ztmz_id`, `order_id`) VALUES (?, ?, ?)', rbr_pacenote_ztmz)
    c_rbr.executemany('INSERT INTO modifier_ztmz(`id`, `ztmz_id`, `order_id`) VALUES (?, ?, ?)', rbr_modifier_ztmz)
    conn_rbr.commit()

    pass

if __name__ == '__main__':
    main()