"""
This script is used under the project "Universal Transcriptome"

Input: components CSV file (original & collective) generated by the sciprt "originalComponentsToCollectiveComponents.py"
Operation: Insert into mysql table

Run:
python mySQL_insert_components.py.py <components.csv>

"""

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
from configparser import ConfigParser
import subprocess
import time
from tqdm import tqdm
import sys

def read_db_config(filename='config.ini', section='mysql'):
    parser = ConfigParser()
    parser.read(filename)
    db = {}
    
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))
 
    return db

if len(sys.argv) < 2:
    sys.exit("run: python mySQL_insert_components.py <components.csv>")

components_csv = sys.argv[1]
start_time = time.time()
db_config = read_db_config()

originalCompsNo = int(subprocess.getoutput(f"wc -l {components_csv}").split()[0])

try:
    connection = mysql.connector.connect(**db_config)

    cursor = connection.cursor()

    with open(components_csv, 'r') as componentsReader:
        for line in tqdm(componentsReader, total=originalCompsNo):
            line = line.strip().split(',')
            original_compID = line[0]
            collective_compID = line[1]
            for unitig_id in line[2:]:
                mySql_insert_query = f"""INSERT INTO `unitigs_tracker` 
                            (`ID`, `unitig_ID`, `originalComponent_ID`, `collectiveComponent_ID`, `finalComponent_ID`) 
                            VALUES
                            (NULL, {unitig_id}, {original_compID}, {collective_compID}, 0)"""

                cursor.execute(mySql_insert_query)
        

    connection.commit()
    cursor.close()

except mysql.connector.Error as error:
    print("Failed to insert record into `unitigs` table {}".format(error))

finally:
    if (connection.is_connected()):
        connection.close()
        print("MySQL connection is closed")


print(f"{originalCompsNo} components inserted in {(time.time() - start_time)} secs")