import os
import csv
import re
from os.path import abspath
from os.path import dirname
from os.path import join
from os.path import pardir
from collections import defaultdict
from sqlalchemy import create_engine
from sqlalchemy import text
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv

directory_path = abspath(join(dirname(__file__), pardir, "backfill_ows_video"))
dotenv_path = join(directory_path, ".env")
load_dotenv(dotenv_path)

# Database credentials
VIDEO_DB_CREDENTIALS = {
    'name': os.environ.get('VIDEO_DB_NAME'),
    'host': os.environ.get('VIDEO_DB_HOST'),
    'password': os.environ.get('VIDEO_DB_PASSWORD'),
    'port': os.environ.get('VIDEO_DB_PORT'),
    'user': os.environ.get('VIDEO_DB_USER'),
}
AR_DB_CREDENTIALS = {
    'name': os.environ.get('AR_DB_NAME'),
    'host': os.environ.get('AR_DB_HOST'),
    'password': os.environ.get('AR_DB_PASSWORD'),
    'port': os.environ.get('AR_DB_PORT'),
    'user': os.environ.get('AR_DB_USER'),
}

AR_DB_URL = (
    'mysql+pymysql://{user}:{password}@{host}/{name}?charset=utf8'.format(
    user=AR_DB_CREDENTIALS.get('user'),
    password=AR_DB_CREDENTIALS.get('password'),
    host=AR_DB_CREDENTIALS.get('host'),
    name=AR_DB_CREDENTIALS.get('name')))
db_session = sessionmaker(bind=create_engine(AR_DB_URL))()

VIDEO_DB_URL = (
    'mysql+pymysql://{user}:{password}@{host}/{name}?charset=utf8'.format(
    user=VIDEO_DB_CREDENTIALS.get('user'),
    password=VIDEO_DB_CREDENTIALS.get('password'),
    host=VIDEO_DB_CREDENTIALS.get('host'),
    name=VIDEO_DB_CREDENTIALS.get('name')))
db_session_ows = sessionmaker(bind=create_engine(VIDEO_DB_URL))()

def create_json_backfill():

    GET_BACKFILL_DETAILS = """
    SELECT j.`id`, j.`datetime`, jo.`name` AS asset_type, jo.`value` AS asset_path, cf.`value` AS product_id FROM jobs j 
    JOIN job_outputs jo ON j.id = jo.job_id
    JOIN context_fields cf ON j.context_id = cf.context_id
    WHERE j.parent_id IN 
    (SELECT j.id FROM jobs j 
    JOIN job_statuses js ON j.id = js.job_id
    WHERE j.`type`= 'workflow_approval' AND js.status = 'COMPLETE' )
    AND j.`type` = 'create_mezzanines'
    AND cf.`name` = 'product_id'
    """

    GET_BACKFILL_DETAILS_AR = """
    SELECT id FROM track WHERE upc = (SELECT upc FROM releases WHERE release_id = '{product_id}') AND track_type = 'video'
    """

    CHECK_EXISTS = """
    SELECT asset_type, product_id FROM video_asset WHERE asset_type = '{asset_type}' AND product_id = '{product_id}'
    """

    offset = 0
    limit = 50000
    list_of_dicts= []

    while True:
        result = db_session_ows.execute(text(GET_BACKFILL_DETAILS + f" LIMIT {limit} OFFSET {offset}"))
        rows = result.fetchall()

        if not rows:
            break

        columns = result.keys()
        list_of_dicts += [dict(zip(columns, row)) for row in rows]
        offset += limit

    product_frequency = defaultdict(int)
    product_data = defaultdict(list)
    for row in list_of_dicts:
        product_id = row['product_id']
        product_frequency[product_id] += 1
        product_data[product_id].append(row)

    result = []

    for product_id, entries in product_data.items():
        frequency = product_frequency[product_id]

        if frequency >= 3:
            result.extend(entries[:3])

    filtered_data = []

    for entry in result:
        if entry['asset_type'] != 'create_mezzanines_mediaconvert_job_id':
            tuid_query = text(GET_BACKFILL_DETAILS_AR.format(product_id=entry['product_id']))
            tuid = db_session.execute(tuid_query).scalar()

            asset_type = entry['asset_type']
            if asset_type == 'h264_mezzanine_output_s3_key':
                asset_type = 'stored_video_337'
            elif asset_type == 'prores_mezzanine_output_s3_key':
                asset_type = 'video_master'

            check_exists_query = text(CHECK_EXISTS.format(asset_type=asset_type, product_id=product_id))
            exists = db_session_ows.execute(check_exists_query).first()

            if not exists:
                filtered_data.append({
                    'product_id': entry['product_id'],
                    'upload_date': entry['datetime'].strftime("%Y-%m-%d %H:%M:%S"),
                    'last_updated': entry['datetime'].strftime("%Y-%m-%d %H:%M:%S"),
                    'tuid': tuid,
                    'asset_type': asset_type,
                    'asset_path': re.sub(r'[,"\n]', '', entry['asset_path'])
                })

    csv_file_path = 'output.csv'

    # Write the data to a CSV file
    with open(csv_file_path, 'w', newline='') as csvfile:
        fieldnames = ['product_id', 'tuid', 'asset_type', 'asset_path', 'upload_date', 'last_updated']
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

        writer.writeheader()
        for row in filtered_data:
            writer.writerow(row)
    return True

create_json_backfill()