"""Migrate artwork assets from ows_assets to ows_asset_transcoder."""
# from __future__ import print_function

import logging
import sys
from snowflake_connector.snowflake_conn import fetchall
import mysql.connector
from mysql.connector import errorcode
from datetime import date, datetime, timedelta

import config


# logger settings
log = logging.getLogger()
log.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
fh = logging.FileHandler('migration.log')
fh.setLevel(logging.INFO)
fh.setFormatter(formatter)
log.addHandler(fh)
stdout = logging.StreamHandler(sys.stdout)
stdout.setFormatter(formatter)
stdout.setLevel(logging.INFO)
log.addHandler(stdout)


SQL_SELECT_OA_ASSET_UPLOAD_RECORDS =  """
    SELECT asset_upload.id, asset_upload.user_id, asset_upload.asset_type,
        asset_upload.product_id as object_id, asset_upload.filename,
        asset_upload.original_filename, asset_upload.deleted,
        asset_upload.created_timestamp, asset_upload.updated_timestamp
    FROM ORCHARD_APP_REPORTING.OWS_ASSETS.ASSET_UPLOAD as asset_upload
    WHERE asset_upload.api_version = 2
    AND asset_upload.product_id <> 0
    AND asset_upload.deleted = 0
    AND asset_upload.track_unique_id = 0
    LIMIT 10;
"""


def get_snowflake_data():
    """Get 10 files from ows_assets.asset_upload."""
    images = []
    # params = {
    #     id: [e.g. 1, 2, 3]
    # }
    images = fetchall(SQL_SELECT_OA_ASSET_UPLOAD_RECORDS)
    return images


def connection_setup():
    """Setup a MySQL connection."""
    try:
        cnx = mysql.connector.connect(**config.MYSQL_ASSET_TRANSCODER_CREDS)
        cursor = cnx.cursor()
        if cnx:
            log.info('Connection successful')
        else:
            raise Exception('Connection failed')
    except mysql.connector.Error as err:
        raise Exception("Error connecting", err)
    return cnx, cursor


def create_staging_table(cnx, cursor):
    """Create ows_asset_transcoder.asset_upload_test table."""
    TABLE = {}
    table_name = 'asset_upload_test'
    TABLE[table_name] = (
        "CREATE TABLE `asset_upload_test` ("
        " `id` int(11) NOT NULL AUTO_INCREMENT,"
        " `corresponding_oa_id` int(11) DEFAULT NULL,"
        " `user_id` varchar(127) NOT NULL,"
        " `asset_type` varchar(16) DEFAULT NULL,"
        " `object_id` varchar(255) DEFAULT NULL,"
        " `object_type` enum('episode','podcast','adupload','test','release') DEFAULT NULL,"
        " `filename` varchar(64) NOT NULL,"
        " `original_filename` varchar(255) DEFAULT NULL,"
        " `deleted` tinyint(1) DEFAULT 0,"
        " `created_timestamp` timestamp DEFAULT NULL,"
        " `updated_timestamp` timestamp DEFAULT NULL,"
        " PRIMARY KEY(`id`)"
    ") ENGINE=InnoDB")

    table_description = TABLE[table_name]
    try:
        log.info(f"Creating table {table_name}: ")
        cursor.execute(table_description)
    except mysql.connector.Error as err:
        raise Exception('Issue creating table', err)


def insert_into_table(cnx, cursor):
    """Insert asset data to asset_upload_test table."""
    images = get_snowflake_data()

    for image in images:
        try:
            image = list(image)
            image.insert(4, 'release')
            image = tuple(image)

            add_image_asset = ("INSERT INTO asset_upload_test "
                            "(corresponding_oa_id, user_id, asset_type, object_id, object_type, filename, original_filename, deleted, created_timestamp, updated_timestamp) "
                            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")

            cursor.execute(add_image_asset, image)
            last_row_id = cursor.lastrowid

            cnx.commit()
            log.info(f"Row {last_row_id}, image asset {image[0]} successfully inserted.")
        except Exception as e:
            log.info(f"Image asset {image[0]} failed, {e}.")


if __name__ == '__main__':
    cnx, cursor = connection_setup()
    create_staging_table(cnx, cursor)
    insert_into_table(cnx, cursor)
    cursor.close()
    cnx.close()