import config
import mysql


def main():
    try:
        print('--liquibase formatted sql \n')
        print('--changeset bhavinrshah:1 \n')
        video_isrc_list = []

        # Get the isrc from `art_relations.track` table for only video isrc (not for both video and music).
        track_query = """SELECT t.isrc
        FROM track t
        WHERE track_type = 'video' AND isrc != '' AND isrc IS NOT NULL AND NOT EXISTS (SELECT t2.isrc FROM track t2 WHERE t2.track_type = 'music' AND t2.`isrc` = t.isrc);"""

        isrcs = []
        art_relations_cursor.execute(track_query)
        track_isrcs = art_relations_cursor.fetchall()
        for row in track_isrcs:
            isrcs.append(row[0])
        chunks = array_chunk(isrcs, 1000)

        # Get the isrc from `delivery_history.masters_delivery_history` table.
        for isrcs in chunks:
            isrc_data = "'"+"','".join(isrcs)+"'"
            track_query = "SELECT isrc FROM masters_delivery_history WHERE isrc IN ( " + isrc_data + ");"
            ows_delivery_history_cursor.execute(track_query)
            track_isrcs = ows_delivery_history_cursor.fetchall()
            for row in track_isrcs:
                video_isrc_list.append(row[0])
        final_isrc = "'"+"','".join(video_isrc_list)+"'"

        # Create temporary table from `delivery_history.masters_delivery_history` table's isrc.
        print('Create a Temporary Table....\n')
        temp_table = """DROP TABLE IF EXISTS `ROLLBACK-MR-1840_masters_delivery_history`;
        CREATE TABLE `ROLLBACK-MR-1840_masters_delivery_history`
        SELECT * FROM masters_delivery_history WHERE isrc IN ("""+final_isrc.strip(',')+""");"""

        creat_file = open("MR-1840-Query-Script.txt", "w")
        creat_file.write(temp_table + '\n\n')
        print(temp_table + '\n\n')

        # Delete data into `delivery_history.masters_delivery_history` by isrcs.
        print('Delete Query......\n')
        final_query = "DELETE FROM masters_delivery_history where isrc in ("+final_isrc.strip(',')+");"
        creat_file.write(final_query + '\n\n')
        print(final_query + '\n\n')

        # Create rollback for deleted data form temporary table
        print('Roolback Query......\n')
        rollback_query = "INSERT INTO `masters_delivery_history` (SELECT * FROM `ROLLBACK-MR-1840_masters_delivery_history`);"
        creat_file.write('--rollback ' + rollback_query + '\n\n')
        print('--rollback ' + rollback_query + '\n')
        creat_file.close()
    except Exception as ex:
        print("Error occurred  due to: " + str(ex.message))
        traceback.print_exc()


def array_chunk(arr, size):
    chunks = [arr[x:x + size] for x in range(0, len(arr), size)]
    return chunks


# connect art_relations db
art_relations_cursor = mysql.connect_db({
    'host': config.AR_DB_HOST,
    'user': config.AR_DB_USERNAME,
    'password': config.AR_DB_PASSWORD,
    'db': config.AR_DATABASE
})

# connect delivery_history db
ows_delivery_history_cursor = mysql.connect_db({
    'host': config.DH_DB_HOST,
    'user': config.DH_DB_USERNAME,
    'password': config.DH_DB_PASSWORD,
    'db': config.DH_DATABASE
})

main()