from collections.abc import Sequence
from contextlib import contextmanager
from typing import Any

import pymysql  # type: ignore[import-untyped]

from . import config


@contextmanager
def mysql_connection() -> Any:
    connection = pymysql.connect(
        host=config.DB_COLLABORATORS_HOST,
        user=config.DB_COLLABORATORS_USERNAME,
        password=config.DB_COLLABORATORS_PASSWORD,
        database=config.DB_COLLABORATORS_DATABASE,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=False,
    )
    try:
        yield connection
        connection.commit()
    except Exception:
        connection.rollback()
        raise
    finally:
        connection.close()


def fetch_target_payments(
    dp_payment_ids: Sequence[int],
    abacus_sp_ids: Sequence[int],
    limit: int,
) -> list[dict[str, Any]]:
    where_clauses = [
        "payoneer_program_id IS NOT NULL",
        "payoneer_payment_id IS NOT NULL",
        "payoneer_payment_status IS NULL",
    ]
    params: list[Any] = []

    if abacus_sp_ids:
        placeholders = ", ".join(["%s"] * len(abacus_sp_ids))
        where_clauses.append(f"abacus_statement_period_id IN ({placeholders})")
        params.extend(abacus_sp_ids)
    elif dp_payment_ids:
        placeholders = ", ".join(["%s"] * len(dp_payment_ids))
        where_clauses.append(f"id IN ({placeholders})")
        params.extend(dp_payment_ids)

    params.append(limit)

    sql = f"""
        SELECT
            id AS dp_payment_id,
            payoneer_program_id,
            payoneer_payment_id,
            payoneer_payment_status
        FROM dp_payment
        WHERE {" AND ".join(where_clauses)}
        ORDER BY id ASC
        LIMIT %s
    """

    with mysql_connection() as connection, connection.cursor() as cursor:
        cursor.execute(sql, params)
        rows = cursor.fetchall()
        return list(rows)


def update_payoneer_status_bulk(updates: Sequence[tuple[int, str, str | None]]) -> int:
    """Bulk update Payoneer statuses.

    Args:
        updates: Sequence of ``(dp_payment_id, payoneer_status, reason)`` tuples.

    Returns:
        Number of rows updated.
    """
    if not updates:
        return 0

    payload_sql = " UNION ALL ".join(["SELECT %s AS id, %s AS status, %s AS reason"] * len(updates))
    payload_params: list[Any] = []
    for dp_payment_id, status, reason in updates:
        payload_params.extend([dp_payment_id, status, reason])

    with mysql_connection() as connection, connection.cursor() as cursor:
        cursor.execute(
            f"""
            UPDATE dp_payment AS d
            JOIN (
                {payload_sql}
            ) AS payload ON payload.id = d.id
            SET d.payoneer_payment_status = payload.status,
                d.latest_payoneer_event_reason = payload.reason
            WHERE d.payoneer_payment_status IS NULL
            """,
            payload_params,
        )
        return int(cursor.rowcount)