import pymysql

GET_VIDEOS = """
SELECT
    vs.last_updated,
    vs.youtube_video_id,
    ycca.cmsa_content_owner,
    yc.monetization_status,
    yc.auto_claim,
    vs.in_processing,
    vs.youtube_channel_id,
    vs.youtube_asset_id,
    vs.youtube_claim_id,
    vs.youtube_reference_id,
    vs.error_type
FROM art_relations.youtube_channel_video_status vs
JOIN art_relations.youtube_channel yc
    ON yc.youtube_channel_id = vs.youtube_channel_id
JOIN art_relations.youtube_channel_cms_account_history yccah
    ON yc.youtube_channel_cms_account_history_id = yccah.id
JOIN art_relations.youtube_channel_cms_account ycca
    ON ycca.id = yccah.youtube_channel_cms_account_id
WHERE
    vs.youtube_video_id IN %(video_ids)s
;
"""

GET_CHANNEL = """
SELECT *
FROM art_relations.youtube_channel yc
WHERE
    yc.youtube_channel_id = %(youtube_channel_id)s
"""


class Client:
    db_conn: pymysql.connections.Connection

    def __init__(
        self, host: str, user: str, password: str, schema: str, port: int
    ) -> None:
        self.db_conn = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=schema,
            port=port,
            cursorclass=pymysql.cursors.DictCursor,
        )
        with self.db_conn.cursor() as cursor:
            cursor.execute("SELECT 1")

    def get_videos(self, video_ids: list[str]) -> dict[str, dict]:
        with self.db_conn.cursor() as cursor:
            cursor.execute(GET_VIDEOS, {"video_ids": tuple(video_ids)})
            return {x["youtube_video_id"]: x for x in cursor.fetchall()}

    def get_channel(self, yt_channel_id: str) -> dict:
        with self.db_conn.cursor() as cursor:
            cursor.execute(GET_CHANNEL, {"youtube_channel_id": yt_channel_id})
            result = cursor.fetchone()
            return result