"""Repository for payment allocation operations."""

from __future__ import annotations

from decimal import Decimal

from src.connectors.db import Connection
from src.connectors.mysql import handle_mysql_errors
from src.enums import (
    LedgerStatus,
    PaymentStatus,
)
from src.schemas import (
    ContractAdjustmentCount,
    LedgerAdjustmentApplied,
    StatementPeriodPaymentEntity,
)


class Repository:
    """Repository for database operations."""

    def __init__(self, conn: Connection) -> None:
        """Initialize repository.

        Args:
            conn: MySQL database connection
        """
        self.conn = conn

    @handle_mysql_errors
    def get_statement_period_payment_entity(
        self,
        statement_period_payment_entity_id: int,
    ) -> StatementPeriodPaymentEntity | None:
        """Get a statement_period_payment_entity record by ID.

        Args:
            statement_period_payment_entity_id: The SPPE ID

        Returns:
            StatementPeriodPaymentEntity if found, None otherwise

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT
                    statement_period_payment_entity_id,
                    statement_period_id,
                    reference_payment_entity_id
                FROM statement_period_payment_entity
                WHERE statement_period_payment_entity_id = %s
                """,
                (statement_period_payment_entity_id,),
            )

            row = cursor.fetchone()
            if row is None:
                return None
            return StatementPeriodPaymentEntity(**row)

    @handle_mysql_errors
    def get_adjustments_for_payment_entity(
        self,
        statement_period_id: int,
        reference_payment_entity_id: int,
        batch_size: int,
    ) -> list[LedgerAdjustmentApplied]:
        """Get flowthrough ledger_adjustment_applied records for a payment entity.

        Joins through account_payment_term to find accounts belonging to
        the payment entity, and through account_payee to get payee info.
        Excludes adjustments already linked to a payment allocation.

        Args:
            statement_period_id: The statement period ID
            reference_payment_entity_id: The reference payment entity ID
            batch_size: Maximum number of records to return

        Returns:
            List of LedgerAdjustmentApplied records

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT
                    laa.ledger_adjustment_applied_id,
                    laa.account_id,
                    laa.contract_id,
                    laa.adjustment_amount,
                    laa.adjustment_currency_code,
                    laa.adjustment_amount_payee_currency,
                    laa.adjustment_payee_currency_code,
                    ap.account_payee_id
                FROM ledger_adjustment_applied AS laa
                INNER JOIN account_payment_term AS apt
                    ON laa.account_id = apt.account_id
                    AND apt.payment_entity_id = %s
                INNER JOIN account_payee AS ap
                    ON laa.account_id = ap.account_id
                WHERE laa.statement_period_id = %s
                    AND laa.apply_to_flowthrough_payment = 1
                    AND NOT EXISTS (
                        SELECT 1
                        FROM payment_allocation_ledger_adjustment pala
                        WHERE pala.ledger_adjustment_applied_id
                            = laa.ledger_adjustment_applied_id
                    )
                LIMIT %s
                """,
                (reference_payment_entity_id, statement_period_id, batch_size),
            )

            results = cursor.fetchall()
            return [LedgerAdjustmentApplied(**row) for row in results]

    @handle_mysql_errors
    def get_contract_adjustment_counts(
        self,
        statement_period_id: int,
        reference_payment_entity_id: int,
        limit: int,
    ) -> list[ContractAdjustmentCount]:
        """Get per-contract counts of unlinked flowthrough adjustments.

        Args:
            statement_period_id: The statement period ID
            reference_payment_entity_id: The reference payment entity ID
            limit: Maximum number of contracts to return

        Returns:
            List of ContractAdjustmentCount records

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT laa.contract_id, COUNT(*) AS adjustment_count
                FROM ledger_adjustment_applied AS laa
                INNER JOIN account_payment_term AS apt
                    ON laa.account_id = apt.account_id
                    AND apt.payment_entity_id = %s
                WHERE laa.statement_period_id = %s
                    AND laa.apply_to_flowthrough_payment = 1
                    AND NOT EXISTS (
                        SELECT 1
                        FROM payment_allocation_ledger_adjustment pala
                        WHERE pala.ledger_adjustment_applied_id
                            = laa.ledger_adjustment_applied_id
                    )
                GROUP BY laa.contract_id
                LIMIT %s
                """,
                (reference_payment_entity_id, statement_period_id, limit),
            )

            results = cursor.fetchall()
            return [ContractAdjustmentCount(**row) for row in results]

    @handle_mysql_errors
    def get_adjustments_for_contracts(
        self,
        statement_period_id: int,
        reference_payment_entity_id: int,
        contract_ids: list[int],
        limit: int | None = None,
    ) -> list[LedgerAdjustmentApplied]:
        """Get flowthrough adjustments for specific contracts.

        Args:
            statement_period_id: The statement period ID
            reference_payment_entity_id: The reference payment entity ID
            contract_ids: Contract IDs to fetch adjustments for
            limit: Optional maximum number of records to return

        Returns:
            List of LedgerAdjustmentApplied records

        Raises:
            TransientError: If database connection fails
        """
        placeholders = ', '.join(['%s'] * len(contract_ids))
        query = f"""
            SELECT
                laa.ledger_adjustment_applied_id,
                laa.account_id,
                laa.contract_id,
                laa.adjustment_amount,
                laa.adjustment_currency_code,
                laa.adjustment_amount_payee_currency,
                laa.adjustment_payee_currency_code,
                ap.account_payee_id
            FROM ledger_adjustment_applied AS laa
            INNER JOIN account_payment_term AS apt
                ON laa.account_id = apt.account_id
                AND apt.payment_entity_id = %s
            INNER JOIN account_payee AS ap
                ON laa.account_id = ap.account_id
            WHERE laa.statement_period_id = %s
                AND laa.apply_to_flowthrough_payment = 1
                AND laa.contract_id IN ({placeholders})
                AND NOT EXISTS (
                    SELECT 1
                    FROM payment_allocation_ledger_adjustment pala
                    WHERE pala.ledger_adjustment_applied_id
                        = laa.ledger_adjustment_applied_id
                )
            """

        params: tuple = (
            reference_payment_entity_id,
            statement_period_id,
            *contract_ids,
        )

        if limit is not None:
            query += 'LIMIT %s\n'
            params = (*params, limit)

        with self.conn.cursor() as cursor:
            cursor.execute(query, params)

            results = cursor.fetchall()
            return [LedgerAdjustmentApplied(**row) for row in results]

    @handle_mysql_errors
    def create_payment_allocation(
        self,
        contract_id: int,
        payee_type: str,
        payee_id: int,
        statement_period_id: int,
        payment_allocation_type: str,
        amount_to_payment: Decimal,
        amount_to_ledger: Decimal,
        currency_code: str,
        description: str,
        created_by: str,
    ) -> int:
        """Create a payment_allocation record.

        Note: Caller is responsible for committing the transaction.

        Args:
            contract_id: The contract ID
            payee_type: The payee type (e.g., 'account_payee')
            payee_id: The payee ID (e.g., account_payee_id)
            statement_period_id: The statement period ID
            payment_allocation_type: The allocation type (e.g., 'flowthrough')
            amount_to_payment: Amount to pay to payee
            amount_to_ledger: Amount to add/deduct from ledger
            currency_code: Currency code
            description: Description of the allocation
            created_by: User/service that created this record

        Returns:
            int: Created payment_allocation_id

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO payment_allocation (
                    contract_id,
                    payee_type,
                    payee_id,
                    statement_period_id,
                    payment_allocation_type,
                    amount_to_payment,
                    payment_status,
                    payment_status_modified,
                    amount_to_ledger,
                    ledger_status,
                    ledger_status_modified,
                    currency_code,
                    description,
                    created_by,
                    last_modified_by
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), %s, %s, NOW(), %s, %s, %s, %s)
                """,
                (
                    contract_id,
                    payee_type,
                    payee_id,
                    statement_period_id,
                    payment_allocation_type,
                    amount_to_payment,
                    PaymentStatus.INIT,
                    amount_to_ledger,
                    LedgerStatus.INIT,
                    currency_code,
                    description,
                    created_by,
                    created_by,
                ),
            )

            return cursor.lastrowid

    @handle_mysql_errors
    def create_payment_allocation_ledger_adjustment(
        self,
        payment_allocation_id: int,
        ledger_adjustment_applied_id: int,
        created_by: str,
    ) -> int:
        """Create a payment_allocation_ledger_adjustment record.

        Note: Caller is responsible for committing the transaction.

        Args:
            payment_allocation_id: The payment allocation ID
            ledger_adjustment_applied_id: The ledger adjustment applied ID
            created_by: User/service that created this record

        Returns:
            int: Created payment_allocation_ledger_adjustment_id

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO payment_allocation_ledger_adjustment (
                    payment_allocation_id,
                    ledger_adjustment_applied_id,
                    created_by,
                    last_modified_by
                ) VALUES (%s, %s, %s, %s)
                """,
                (
                    payment_allocation_id,
                    ledger_adjustment_applied_id,
                    created_by,
                    created_by,
                ),
            )

            return cursor.lastrowid

    @handle_mysql_errors
    def get_max_allowed_packet(self) -> int | None:
        """Get max_allowed_packet from MySQL server.

        Returns:
            The max_allowed_packet in bytes, or None if undetermined.

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute("SHOW VARIABLES LIKE 'max_allowed_packet'")
            row = cursor.fetchone()
            return int(row['Value']) if row else None

    @handle_mysql_errors
    def get_state_status(
        self,
        parent_table_name: str,
        parent_table_id: int,
        action_name: str,
    ) -> str | None:
        """Get the action_status from abacus_state for a given entity and action.

        Args:
            parent_table_name: The parent table name (e.g., 'statement_period_payment_entity')
            parent_table_id: The parent table row ID
            action_name: The action name (e.g., 'close_balance')

        Returns:
            The action_status string if found, None otherwise

        Raises:
            TransientError: If database connection fails
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT action_status
                FROM abacus_state
                WHERE parent_table_name = %s
                    AND parent_table_id = %s
                    AND action_name = %s
                """,
                (parent_table_name, parent_table_id, action_name),
            )
            row = cursor.fetchone()
            return row['action_status'] if row else None