import argparse
import csv
import math

from src import connection
from src.sql import product as product_sql
from src.sql import tracks, youtube_video_status


def main() -> None:
    # load and sanitize input data
    args = get_args()
    filename = args.input_filename
    with open(f"/tmp/input/{filename}", "r") as f:
        reader = csv.DictReader(f)
        rows = [
            {
                "video_id": row["video_id"],
                "channel_id": row["channel_id"],
                "video_title": row["video_title"],
                "video_length": int(row["video_length"]),
                "claim_id": row["claim_id"],
                "asset_id": row["asset_id"],
            }
            for row in reader
        ]

    db_connection = connection.ar_db_connection()
    cursor = db_connection.cursor()

    output_rows = []
    for video in rows:
        channel_id = video["channel_id"]
        track_name = str(video["video_title"]).encode("utf-8", errors="ignore")
        video_id = video["video_id"]
        claim_id = video["claim_id"] or None
        asset_id = video["asset_id"] or None
        length_minute = math.floor(int(video["video_length"]) / 60)
        length_seconds = int(video["video_length"]) % 60

        upc = ""
        release_id = ""
        unique_track_id = ""
        new_isrc = ""
        error = ""

        # Check if video is available on youtube_channel_video_status table
        cursor.execute(
            youtube_video_status.SQL_GET_VIDEO_STATUS.format(
                video_id, asset_id, claim_id
            )
        )
        youtube_video = cursor.fetchone()
        if not youtube_video:
            # Get products information from channel_id
            cursor.execute(product_sql.SQL_GET_UPC.format(channel_id))
            product = cursor.fetchone()
            if product:
                upc = product.get("upc")
                # Assumed track.p_line = product c_line
                p_line = product.get("c_line")
                release_id = product.get("release_id")
                if release_id:
                    cursor.execute(tracks.SQL_CLAIM_ISRC)
                    isrc_obj = cursor.fetchone()
                    new_isrc = isrc_obj.get("isrc")
                    cursor.execute(tracks.SQL_GET_TRACK_ID_BY_UPC.format(upc))
                    track = cursor.fetchone()
                    track_id = track.get("track_id")
                    # Save track data
                    cursor.execute(
                        tracks.SQL_INSERT_TRACK,
                        (
                            track_id,
                            upc,
                            release_id,
                            track_name,
                            new_isrc,
                            length_minute,
                            length_seconds,
                            p_line,
                        ),
                    )
                    unique_track_id = cursor.lastrowid

                    # Save youtube_channel_video_status data
                    cursor.execute(
                        youtube_video_status.SQL_INSERT_VIDEO_STATUS,
                        (
                            video_id,
                            channel_id,
                            release_id,
                            unique_track_id,
                            asset_id,
                            claim_id,
                        ),
                    )

                    db_connection.commit()
            else:
                error = "No product found for this channel"
        else:
            error = "Video already exists with either video or asset or claim IDs"

        output_row = {
            "channel_id": channel_id,
            "video_id": video_id,
            "upc": upc,
            "unique_track_id": unique_track_id,
            "isrc": new_isrc,
            "claim_id": claim_id,
            "asset_id": asset_id,
            "error": error,
        }
        output_rows.append(output_row)

    save_output(filename, output_rows)


def save_output(filename: str, output_rows: list[dict[str, str | int | None]]) -> None:
    headers = [
        "channel_id",
        "video_id",
        "upc",
        "unique_track_id",
        "isrc",
        "claim_id",
        "asset_id",
        "error",
    ]
    with open(f"/tmp/output/{filename}", "w", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(output_rows)


def get_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        formatter_class=argparse.ArgumentDefaultsHelpFormatter
    )
    parser.add_argument(
        "input_filename",
        type=str,
        help="Name of the csv in ./input",
    )
    return parser.parse_args()


if __name__ == "__main__":
    main()