from contextlib import contextmanager

from src import connection

VALIDATE_CHANNEL_SQL = """
SELECT id, youtube_channel_id
FROM youtube_channel
WHERE youtube_channel_id = %s;
"""

CHECK_HISTORY_SQL = """
SELECT ycca.`id`, ycca.`cmsa_display_name`, yc.*
FROM youtube_channel yc
INNER JOIN youtube_channel_cms_account_history yccah
    ON yccah.id = yc.youtube_channel_cms_account_history_id
INNER JOIN youtube_channel_cms_account ycca
    ON ycca.id = yccah.youtube_channel_cms_account_id
WHERE yc.youtube_channel_id = %s
AND ycca.id = %s;
"""

INSERT_HISTORY_SQL = """
INSERT INTO youtube_channel_cms_account_history
(youtube_channel_id, youtube_channel_cms_account_id, orchadmin_users_id)
VALUES
(%s, %s, 179);
"""

UPDATE_CHANNEL_SQL = """
UPDATE youtube_channel
SET youtube_channel_cms_account_history_id = %s
WHERE id = %s;
"""


@contextmanager
def _db_cursor():
    db_connection = connection.ar_db_connection()
    cursor = db_connection.cursor()
    try:
        yield cursor
        db_connection.commit()
    finally:
        cursor.close()
        db_connection.close()


def validate_youtube_channel_data(channel_id):
    with _db_cursor() as cursor:
        cursor.execute(VALIDATE_CHANNEL_SQL, (channel_id,))
        return cursor.fetchone()


def check_existing_cms_account_history(channel_id, cms_account_id):
    with _db_cursor() as cursor:
        cursor.execute(CHECK_HISTORY_SQL, (channel_id, cms_account_id))
        return cursor.rowcount


def update_cms_account(channel_row_id, cms_account_id):
    with _db_cursor() as cursor:
        cursor.execute(INSERT_HISTORY_SQL, (channel_row_id, cms_account_id))
        cms_account_history_id = cursor.lastrowid

    print(
        f"Data inserted into youtube_channel_cms_account_history for channel row id {channel_row_id} and cms account {cms_account_id}."
    )

    if cms_account_history_id:
        with _db_cursor() as cursor:
            cursor.execute(UPDATE_CHANNEL_SQL, (cms_account_history_id, channel_row_id))
        print(f"CMS account {cms_account_id} is updated for channel row id {channel_row_id}.")


def update_cms_account_for_channel(channel_id, cms_account_id):
    print("Running script to update cms account")
    youtube_channel_data = validate_youtube_channel_data(channel_id)
    if youtube_channel_data is None:
        print(f"Given channel id {channel_id} is invalid")
        return
    existing_record_count = check_existing_cms_account_history(channel_id, cms_account_id)
    print(f"Records fetched for channel_id {channel_id} and CMS account {cms_account_id}: {existing_record_count}")
    if existing_record_count != 0:
        print(f"CMS Account {cms_account_id} is already assigned to youtube channel id {channel_id}")
        return
    update_cms_account(youtube_channel_data[0], cms_account_id)
    print(f"Youtube channel CMS account is updated successfully to {cms_account_id} for Youtube channel {channel_id}.")