import os
import pymysql
import click
from dotenv import load_dotenv


def find_table(file_name, connection):
    with connection.cursor() as cursor:
        sql = "SELECT ft.table_name FROM file_table ft WHERE ft.file_name=%s"
        cursor.execute(sql, file_name)
        result = cursor.fetchone()

        if not result:
            return None
        else:
            return result[0]


def update_stuck_row(table_name, row, connection, dry_run=False):
    with connection.cursor() as cursor:
        sql = "UPDATE `{}` SET `STATUS`='X', `MATCHED ON`='NONE', `MATCH CODE`='XXX', NOTES='MANUAL SKIP - ERROR' WHERE ROW_NUM={}".format(
            table_name, row)
        if dry_run:
            print("Would execute: \n'{}'".format(sql))
        else:
            cursor.execute(sql)
            print("Updated row with row_num {} in table {}".format(row, table_name))

    connection.commit()


@click.command()
@click.option("--file", prompt=True)
@click.option("--row", prompt=True)
@click.option("--dry-run/--no-dry-run", default=False)
def unstick(file, row, dry_run):
    connection = pymysql.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        database=os.getenv("DB_SCHEMA"))

    print("Unsticking {} row {}".format(file, row))

    try:
        table_name = find_table(file, connection)
        if not table_name:
            print("No table named {} found. Exiting.".format(file))
            exit(-1)
        update_stuck_row(table_name, row, connection, dry_run)
    finally:
        connection.close()


if __name__ == '__main__':
    load_dotenv()
    unstick()