"""Fixtures for functional tests against a real MySQL schema.

These tests require the dockerized MySQL with the royalty_accounting schema
applied (sync-contract-sap-mysql + sync-contract-sap-liquibase-runner).
Run via `make docker_test_functional`.
"""

from __future__ import annotations

import os
from collections.abc import Generator

import pytest
from pymysql.connections import Connection
from pymysql.cursors import DictCursor

from sync_contract_sap.db import mysql_connection

# IDs at or above this value are owned (created and deleted) by these tests.
TEST_ID_START = 990000


@pytest.fixture
def conn() -> Generator[Connection[DictCursor], None, None]:
    """Yield a connection to the dockerized MySQL, cleaning test rows around each test."""
    with mysql_connection(
        host=os.environ.get('MYSQL_DB_HOST', '127.0.0.1'),
        user=os.environ.get('MYSQL_DB_USER', 'db_user'),
        password=os.environ.get('MYSQL_DB_PASS', 'db_pass'),
        database=os.environ.get('MYSQL_DB_NAME', 'royalty_accounting'),
        port=int(os.environ.get('MYSQL_DB_PORT', '3306')),
    ) as connection:
        _delete_test_rows(connection)
        _seed_reference_rows(connection)
        yield connection
        _delete_test_rows(connection)


def _seed_reference_rows(conn: Connection[DictCursor]) -> None:
    """Create the reference rows contract inserts depend on.

    Contract inserts are validated by the before_contract_insert_se_pc_authorized
    trigger (ACC-10454): the (signing entity, profit center) pair must exist as a
    live signing_entity_sap_profit_center row. Mirrors ows-royalties'
    create_signing_entity_with_profit_center test helper.
    """
    with conn.cursor() as cursor:
        cursor.execute(
            """
            INSERT IGNORE INTO reference_payment_entity (
                reference_payment_entity_id, payment_entity_name,
                country_of_tax_reporting,
                created_by, created_at, last_modified_by, last_modified
            ) VALUES (
                %s, 'functional tests', 'GBR',
                'functional', NOW(), 'functional', NOW()
            )
            """,
            (TEST_ID_START,),
        )
        cursor.execute(
            """
            INSERT IGNORE INTO reference_sap_profit_center (
                reference_sap_profit_center_id, profit_center,
                company_code, business_group, display_name
            ) VALUES (%s, 'FUNCTEST', '4914', 'ORC', 'functional tests')
            """,
            (TEST_ID_START,),
        )
        cursor.execute(
            """
            INSERT IGNORE INTO reference_signing_entity (
                reference_signing_entity_id, reference_payment_entity_id,
                reference_sap_profit_center_id, company_code, legal_name
            ) VALUES (%s, %s, %s, '4914', 'functional tests SE')
            """,
            (TEST_ID_START, TEST_ID_START, TEST_ID_START),
        )
        cursor.execute(
            """
            INSERT IGNORE INTO signing_entity_sap_profit_center (
                reference_signing_entity_id, reference_sap_profit_center_id,
                created_by, created_at, last_modified_by, last_modified
            ) VALUES (%s, %s, 'functional', NOW(), 'functional', NOW())
            """,
            (TEST_ID_START, TEST_ID_START),
        )
    conn.commit()


def _delete_test_rows(conn: Connection[DictCursor]) -> None:
    """Remove all rows in the test ID range, children before parents."""
    with conn.cursor() as cursor:
        cursor.execute(
            'DELETE FROM abacus_state '
            'WHERE parent_table_name = %s '
            'AND CAST(parent_table_id AS UNSIGNED) >= %s',
            ('contract', TEST_ID_START),
        )
        cursor.execute(
            'DELETE FROM account_contract WHERE contract_id >= %s', (TEST_ID_START,)
        )
        cursor.execute('DELETE FROM contract WHERE contract_id >= %s', (TEST_ID_START,))
        cursor.execute('DELETE FROM account WHERE account_id >= %s', (TEST_ID_START,))
        cursor.execute(
            'DELETE FROM signing_entity_sap_profit_center '
            'WHERE reference_signing_entity_id >= %s',
            (TEST_ID_START,),
        )
        cursor.execute(
            'DELETE FROM reference_signing_entity '
            'WHERE reference_signing_entity_id >= %s',
            (TEST_ID_START,),
        )
        cursor.execute(
            'DELETE FROM reference_sap_profit_center '
            'WHERE reference_sap_profit_center_id >= %s',
            (TEST_ID_START,),
        )
        cursor.execute(
            'DELETE FROM reference_payment_entity '
            'WHERE reference_payment_entity_id >= %s',
            (TEST_ID_START,),
        )
    conn.commit()


def insert_contract(
    conn: Connection[DictCursor],
    contract_id: int,
    last_modified: str = '2024-01-01 00:00:00',
    sap_created_at: str | None = None,
) -> None:
    """Insert a contract row with controlled timestamps."""
    with conn.cursor() as cursor:
        cursor.execute(
            """
            INSERT INTO contract (
                contract_id, reference_signing_entity_id,
                reference_sap_profit_center_id, contract_name,
                term_start, term_end, sap_created_at,
                created_by, created_at, last_modified_by, last_modified
            ) VALUES (
                %s, %s, %s, %s, '2021-01-01', '2030-01-01', %s,
                'functional', '2021-01-01 00:00:00', 'functional', %s
            )
            """,
            (
                contract_id,
                TEST_ID_START,
                TEST_ID_START,
                f'functional test {contract_id}',
                sap_created_at,
                last_modified,
            ),
        )
    conn.commit()


def insert_sap_sync_state(
    conn: Connection[DictCursor],
    contract_id: int,
    action_status: str,
    created_at: str = '2024-01-01 00:00:00',
    last_modified: str = '2024-01-01 00:00:00',
) -> None:
    """Insert a sap_sync abacus_state row with controlled timestamps."""
    with conn.cursor() as cursor:
        cursor.execute(
            """
            INSERT INTO abacus_state (
                parent_table_id, parent_table_name, action_name, action_status,
                created_by, created_at, last_modified_by, last_modified
            ) VALUES (
                %s, 'contract', 'sap_sync', %s,
                'functional', %s, 'functional', %s
            )
            """,
            (contract_id, action_status, created_at, last_modified),
        )
    conn.commit()


def insert_account_for_contract(
    conn: Connection[DictCursor], account_id: int, contract_id: int
) -> None:
    """Insert an account and map it to the contract via account_contract."""
    with conn.cursor() as cursor:
        cursor.execute(
            """
            INSERT INTO account (
                account_id, account_name,
                created_by, created_at, last_modified_by, last_modified
            ) VALUES (
                %s, %s, 'functional', '2021-01-01 00:00:00',
                'functional', '2021-01-01 00:00:00'
            )
            """,
            (account_id, f'functional test {account_id}'),
        )
        cursor.execute(
            'INSERT INTO account_contract (account_id, contract_id) VALUES (%s, %s)',
            (account_id, contract_id),
        )
    conn.commit()