import json
import os
import re
import uuid
from datetime import UTC, datetime

from snowflake.connector import DictCursor


def get_test_name():
    return f"E2E Report Gen Test {datetime.now(UTC).isoformat(sep=' ', timespec='seconds')}"


def get_report_run_uuid():
    return str(uuid.uuid4())


def create_statement_period_if_not_exists(collaborators_conn, test_config):
    with collaborators_conn.cursor() as cursor:
        cursor.execute(
            """
                INSERT INTO statement_period (vendor_id)
                SELECT %s WHERE NOT EXISTS (
                    SELECT 1 FROM statement_period
                    WHERE status = 'OPEN'
                        AND vendor_id = %s
                )
            """,
            (
                test_config.vendor_id,
                test_config.vendor_id,
            ),
        )


def delete_existing_splits(collaborators_conn, test_config):
    with collaborators_conn.cursor() as cursor:
        cursor.execute(
            """
                DELETE split
                FROM split
                JOIN collaborator ON
                    collaborator.id = split.collaborator_id
                    AND collaborator.vendor_id = %s
            """,
            (test_config.vendor_id,),
        )


def create_collaborators(collaborators_conn, config, test_name, splits):
    collaborators = []

    for index, split in enumerate(splits):
        with collaborators_conn.cursor() as cursor:
            cursor.execute(
                """
                    INSERT INTO collaborator (vendor_id, currency, name)
                    VALUES (%s, %s, %s)
                """,
                (
                    config.vendor_id,
                    config.vendor_currency,
                    f"{test_name} #{index + 1}",
                ),
            )

            collaborators.append(
                {
                    "collaborator_id": cursor.lastrowid,
                    "split": split,
                }
            )

    return collaborators


def get_tracks_for_vendor(art_relations_conn, config):
    with art_relations_conn.cursor() as cursor:
        cursor.execute(
            """
                SELECT track.id
                FROM vw_product
                    JOIN track ON track.release_id = vw_product.release_id
                WHERE vw_product.vendor_id = %s
                    AND vw_product.release_status = 'in_content'
            """,
            (config.vendor_id,),
        )

        return [row["id"] for row in cursor.fetchall()]


def create_splits(collaborators_conn, collaborators, tuids):
    with collaborators_conn.cursor() as cursor:
        cursor.execute(
            """
                INSERT INTO split (
                    collaborator_id,
                    identifier,
                    split_rate,
                    split_type_id,
                    rate_type
                )
                VALUES {split_values}
            """.format(
                split_values=(", ".join(["(%s, %s, %s, 2, %s)"] * len(collaborators) * len(tuids)))
            ),
            [
                value
                for tuid in tuids
                for collaborator in collaborators
                for value in (
                    collaborator["collaborator_id"],
                    tuid,
                    collaborator["split"].rate / 100,
                    collaborator["split"].type,
                )
            ],
        )


def create_report_run(
    collaborators_conn, config, report_run_uuid, collaborators, test_name, trigger_type
):
    with collaborators_conn.cursor() as cursor:
        cursor.execute(
            """
                INSERT INTO report_run (
                    name,
                    uuid,
                    period_ids,
                    period_name,
                    file_format,
                    source,
                    number_format,
                    notification_email,
                    trigger_type
                )
                VALUES (
                    %s,
                    %s,
                    %s,
                    %s,
                    'xls',
                    'ABACUS',
                    'us',
                    'donotreply@dev.theorchard.io',
                    %s
                )
            """,
            (
                test_name,
                report_run_uuid,
                config.statement_period_id,
                config.statement_period_id,
                trigger_type,
            ),
        )

        report_run_id = cursor.lastrowid

        cursor.executemany(
            """
                INSERT INTO report (report_run_id, collaborator_id, filename)
                VALUES (%s, %s, %s)
            """,
            [
                (
                    report_run_id,
                    collaborator["collaborator_id"],
                    re.sub("[^0-9a-zA-Z]+", "_", f"{test_name} {collaborator['collaborator_id']}"),
                )
                for collaborator in collaborators
            ],
        )


def trigger_report_generation(sqs_client, report_run_uuid):
    sqs_client.send_message(
        QueueUrl=os.environ["REPORT_GENERATION_QUEUE_URL"],
        MessageBody=json.dumps(
            {
                "report_run_uuid": report_run_uuid,
            }
        ),
    )


def get_reports_with_status(collaborators_conn, report_run_uuid, status):
    with collaborators_conn.cursor() as cursor:
        cursor.execute(
            """
                SELECT *
                FROM report
                JOIN report_run ON
                    report_run.id = report.report_run_id
                    AND report_run.uuid = %s
                WHERE report.status = %s
            """,
            (report_run_uuid, status),
        )

        return cursor.fetchall()


def get_vendor_fact_sales_sum(snowflake_conn, config):
    with snowflake_conn.cursor(DictCursor) as cursor:
        cursor.execute(
            """
                SELECT
                    SUM(net_revenue_after_mechanical_payee_currency) AS amount,
                    ANY_VALUE(account_payee_currency) AS currency
                FROM royalty_accounting.prod.abacus_fact_sales_unified_dbt
                WHERE account_id = %s
                    AND statement_period_id = %s
            """,
            (config.vendor_id, config.statement_period_id),
        )

        return cursor.fetchone()