import config
import mysql


def main():
        print('--liquibase formatted sql \n\n')
        print('--changeset amansingh:1 \n\n')
        unique_track_ids = []
        # Get royalty_collections.track_performer data for backfill into ows_track.parformer table
        track_query = """SELECT tp.unique_track_id, tp.performer_name, ppr.role, ppc.category FROM `track_performer` tp
        LEFT JOIN `ppl_performer_role` ppr ON ppr.id = tp.performer_role_id
        LEFT JOIN `ppl_performer_category` ppc ON ppc.id = tp.ppl_category_id;"""

        royalty_collections_cursor.execute(track_query)
        track_performer = royalty_collections_cursor.fetchall()
        performer = map(list, track_performer)
        # Dictionary of new performer rol for map to ppl performer rol
        performer_role = {'DJ': 'Electronics - DJ',
                        'Programmer': 'Other - Unlisted Performer Role',
                        'Symphony': 'Other - Unlisted Performer Role',
                        'Electric Piano': 'Keyboards - Piano',
                        'Piano': 'Keyboards - Piano',
                        'Synthesizer': 'Keyboards - Synthesizer',
                        'Acoustic Guitar': 'Guitar - Acoustic Guitar',
                        'Bass Guitar': 'Guitar - Bass Guitar',
                        'Electric Guitar': 'Guitar - Electric Guitar',
                        'Bass': 'String - Double Bass',
                        'Drums': 'Percussion - Drums',
                        'Alto (Voice)': 'Vocals - Alto',
                        'Backing Vocals': 'Vocals - Backing Vocals',
                        'Caller': 'Other - Sound Effects',
                        'Cantor': 'Vocals - Chanting',
                        'Shouting': 'Vocals - Shouting',
                        'Soprano (Voice)': 'Vocals - Soprano',
                        'Tenor (Voice)': 'Vocals - Tenor'}
        # Dictionary of new performer type for map to ppl performer category
        performer_type = {'Arranger': 'non-featured',
                        'Contracted Featured Artist': 'primary',
                        'Other Featured Artist': 'featured',
                        'Studio Personnel Non-Perf': 'non-featured',
                        'Unknown Non-Featured': 'non-featured',
                        'Conductor': 'non-featured',
                        'Featured Artist': 'featured',
                        'Mixer': 'non-featured',
                        'Non-Featured Artist': 'non-featured',
                        'Orchestra': 'primary',
                        'Recording Engineer': 'non-featured'}

        performer_roles_query = "SELECT performer_role, performer_role_id FROM performer_role"
        ows_track_cursor.execute(performer_roles_query)
        performer_roles = dict(ows_track_cursor.fetchall())
        # map ppl_category and performer_roles to performer_type and performer_role_id
        for index in performer:
            index[3] = performer_type[index[3]]
            index[2] = performer_roles[performer_role[index[2]]]

        # Create Insert Query
        insert_query = "INSERT INTO performer (unique_track_id, birth_name, performer_role_id, performer_type) VALUES\n"
        create_file = open("MR-2178-Backfill-existing-Track-Performer-data.sql", "w")
        create_file.write('--liquibase formatted SQL \n\n')
        create_file.write('--changeset amansingh:1 \n\n')
        for index in range(len(performer)):
            trackData = performer[index]
            unique_track_ids.append(trackData[0])
            if index != len(performer) - 1:
                insert_query += '({}, "{}", {}, "{}"),\n'.format(trackData[0], trackData[1], trackData[2], trackData[3])
            else:
                insert_query += '({}, "{}", {}, "{}");\n\n'.format(trackData[0], trackData[1], trackData[2], trackData[3])
        create_file.write(insert_query)
        print('--Insert Query Completed \n\n')
        print('--rollback Query \n\n')
        # Create Rollback Query
        unique_tracks = list(set(unique_track_ids))
        trackList = ', '.join(map(str, unique_tracks))
        rollback = "--rollback DELETE FROM performer WHERE unique_track_id IN ({});\n".format(trackList)
        create_file.write(rollback)
        print('--rollback Query Completed')
        create_file.close()

# connect ows_track db
ows_track_cursor = mysql.connect_db({
    'host': config.OT_DB_HOST,
    'user': config.OT_DB_USERNAME,
    'password': config.OT_DB_PASSWORD,
    'db': config.OT_DATABASE
})

# connect royalty_collections db
royalty_collections_cursor = mysql.connect_db({
    'host': config.RC_DB_HOST,
    'user': config.RC_DB_USERNAME,
    'password': config.RC_DB_PASSWORD,
    'db': config.RC_DATABASE
})

main()