from contextlib import contextmanager

import pymysql

from ..config import COLLABORATOR_DB_CONFIG, OUTPUT_BUCKET
from .report import ReportWriter
from .transaction import Transaction


def get_report_run_info(report_run_uuid: str):
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        cursor.execute(
            """
                SELECT
                    id,
                    name,
                    period_name,
                    notification_email,
                    number_format,
                    trigger_type
                FROM report_run
                WHERE uuid = %(report_run_uuid)s
            """,
            {"report_run_uuid": report_run_uuid},
        )

        row = cursor.fetchone()

        return row


def get_report_info_by_collaborator_id(report_run_uuid: str):
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        cursor.execute(
            """
                SELECT
                    report.id,
                    report.filename,
                    report.collaborator_id,
                    collaborator.name AS collaborator_name,
                    collaborator.internal_id AS collaborator_internal_id,
                    collaborator.currency,
                    collaborator.vendor_id,
                    report.status
                FROM report_run
                INNER JOIN report
                    ON report.report_run_id = report_run.id
                    AND report_run.uuid = %(report_run_uuid)s
                INNER JOIN collaborator
                    ON collaborator.id = report.collaborator_id
            """,
            {"report_run_uuid": report_run_uuid},
        )

        return {str(row["collaborator_id"]): row for row in cursor.fetchall()}


def update_report(writer: ReportWriter, output_path: str):
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        report = writer.report
        cursor.execute(
            """
                UPDATE report SET
                    status = 'GENERATED',
                    file_location = %(file_location)s,
                    amount = %(amount)s,
                    currency = %(currency)s,
                    generated_datetime = NOW()
                WHERE id = %(report_id)s
            """,
            {
                "file_location": f"s3://{OUTPUT_BUCKET}/{output_path}",
                "amount": report.total,
                "currency": report.currency,
                "report_id": report.report_id,
            },
        )

        cursor.executemany(
            """
                INSERT INTO report_contract (report_id, contract_id, amount, currency)
                VALUES (%s, %s, %s, %s)
            """,
            [
                [report.report_id, contract_id, total, report.currency]
                for contract_id, total in report.contract_totals.items()
            ],
        )


def update_reports_status(report_run_uuid: str, status: str):
    """Update the status of all reports for a given report run.

    Args:
        report_run_uuid (str): UUID v4 unique identifier for the report run.
        status (str): New status to set for the reports.
    """
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        cursor.execute(
            """
                UPDATE report
                SET status = %(status)s
                WHERE report_run_id = (
                    SELECT id FROM report_run WHERE uuid = %(report_run_uuid)s
                )
            """,
            {
                "report_run_uuid": report_run_uuid,
                "status": status,
            },
        )

        return cursor.fetchone()


def get_open_statement_period_ids_for_vendors(vendor_ids: list[int]):
    """Get the last statement period.

    Args:
        vendor_id (str): vendor ID

    Returns:
        str: last statement period
    """
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        placeholders = ",".join(["%s" for _ in vendor_ids])

        cursor.execute(
            f"""
                SELECT 
                    sp.id,
                    sp.vendor_id
                FROM statement_period sp
                WHERE 
                    sp.vendor_id IN({placeholders}) 
                    AND sp.status = "OPEN"
            """,
            vendor_ids,
        )

        return {row["vendor_id"]: row["id"] for row in cursor.fetchall()}


def create_transactions(transactions: list[Transaction]):
    """Create transactions in the database.

    Args:
        transactions (List[Transaction]): list of transactions to create
    """
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        columns = ", ".join(Transaction._fields)
        column_placeholders = ", ".join(["%s" for _ in Transaction._fields])

        cursor.executemany(
            f"""
                INSERT INTO transaction ({columns})
                VALUES ({column_placeholders})
            """,
            [
                [getattr(transaction, field) for field in Transaction._fields]
                for transaction in transactions
            ],
        )

        report_id_placeholders = ", ".join(["%s" for _ in transactions])

        cursor.execute(
            f"""
                UPDATE report
                JOIN transaction ON transaction.report_id = report.id
                SET report.transaction_id = transaction.id
                WHERE report.id IN ({report_id_placeholders})
            """,
            [transaction.report_id for transaction in transactions],
        )


def clear_report_contract(report_run_uuid: str):
    """Clear any existing report contract entries for a given report run.

    Args:
        report_run_uuid (str): UUID of the report run to clear
    """
    with (
        mysql_connection(**COLLABORATOR_DB_CONFIG) as connection,
        connection.cursor() as cursor,
    ):
        cursor.execute(
            """
                DELETE report_contract
                FROM report_contract
                    JOIN report ON report_contract.report_id = report.id
                    JOIN report_run ON report.report_run_id = report_run.id
                WHERE report_run.uuid = %(report_run_uuid)s
            """,
            {
                "report_run_uuid": report_run_uuid,
            },
        )


@contextmanager
def mysql_connection(host, user, password, database, connect_timeout=5, port=3306):
    """Context manager for mysql connection objects.

    Args:
        host (str): hostname of database server
        user (str): user name
        password (str): password
        database (str): database name
        connect_timeout (int): time to wait for db connection
        port (int): the port of the sql connection

    Yields:
        pymysql.connections.Connection: connection to direct delivery db host
    """
    conn = None
    try:
        conn = pymysql.connect(
            host=host,
            user=user,
            passwd=password,
            db=database,
            connect_timeout=connect_timeout,
            port=port,
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=True,
        )

        yield conn
    finally:
        if conn:
            conn.close()