"""Shared fixtures for tests."""

import contextlib
import json
import logging
import os
from functools import lru_cache
from typing import Any, Generator, cast

import pytest
from _pytest.nodes import Node
from abacus_common_logic.connectors.database import db
from abacus_common_logic.db.adapters import Dialect, get_adapter, is_dialect
from flask import Flask
from sqlalchemy import event
from sqlalchemy.engine import Engine, Transaction
from sqlalchemy.orm import Session, SessionTransaction

from abacus_state.api import create_app
from abacus_state.config import Config

from tests.utils.collections import dedupe

TOP_LEVEL_TABLES = [
    'abacus_state',
    'account',
    'account_payee',
    'payee',
    'payee_collaborator',
    'reference_payoneer_program',
    'statement_period_adjustment_file',
    'sales_file',
    'accounting_run',
]


class TestConfig(Config):
    """Test configuration."""

    Testing = True
    ENVIRONMENT = os.environ.get('TEST_ENVIRONMENT', Config.ENVIRONMENT)

    # DB name will be overridden per worker.
    MYSQL_DB_NAME = os.environ.get('MYSQL_TEST_DB_NAME', Config.MYSQL_DB_NAME + '_test')

    if ENVIRONMENT == Config.DEV_ENVIRONMENT:
        # Database vendor
        DB_VENDOR = os.environ.get('DB_VENDOR', 'sqlite,mysql')

        # SQLite database connection
        SQLITE_DB_NAME = os.environ.get('SQLITE_DB_NAME', Config.MYSQL_DB_NAME)
        SQLITE_DB_NAME = os.environ.get('SQLITE_TEST_DB_NAME', f'{SQLITE_DB_NAME}_test')
        SQLITE_DIR = os.environ.get('SQLITE_DIR', '/var/lib/sqlite')
        SQLITE_EXT = os.environ.get('SQLITE_EXT', '.sqlite3')


@pytest.fixture(scope='session', autouse=True)
def _prologue() -> None:
    """Log the testing configuration."""
    payload = {
        'env': TestConfig.ENVIRONMENT,
        'db(s)': TestConfig.DB_VENDOR,
        'mysql': TestConfig.MYSQL_DB_NAME,
    }

    if TestConfig.ENVIRONMENT == Config.DEV_ENVIRONMENT:
        sqlite = f'{TestConfig.SQLITE_DIR}/{TestConfig.SQLITE_DB_NAME}{TestConfig.SQLITE_EXT}'
        payload.update(sqlite=sqlite)

    logging.getLogger(__name__).info('Test config: %s', json.dumps(payload))


# --- Helpers ---


@lru_cache(maxsize=None)
def _create_vendor_app(vendor: Dialect, worker_id: str | None) -> Flask:
    """Create a Flask app once per vendor and reuse across tests."""

    class AppConfig(TestConfig):
        DB_VENDOR = vendor

    adapter = get_adapter(vendor)

    # Create the worker DB
    if worker_id:
        if hasattr(AppConfig, 'MYSQL_DB_NAME'):
            AppConfig.MYSQL_DB_NAME = f'{AppConfig.MYSQL_DB_NAME}_{worker_id}'
        if hasattr(AppConfig, 'SQLITE_DB_NAME'):
            AppConfig.SQLITE_DB_NAME = f'{AppConfig.SQLITE_DB_NAME}_{worker_id}'
        if not adapter.db_exists(AppConfig):
            adapter.create_db(AppConfig)

    # Create the app
    app = create_app(AppConfig)

    # Clone source DB -> worker DB
    if worker_id:
        src_engine = adapter.create_engine(TestConfig)
        dst_engine = adapter.create_engine(AppConfig)
        try:
            if str(src_engine.url) != str(dst_engine.url):
                adapter.clone_db(src_engine, dst_engine)
        finally:
            src_engine.dispose()
            dst_engine.dispose()

    # Create DB tables
    with app.app_context():
        db.create_all()

    return app


@lru_cache(maxsize=None)
def _create_vendor_engine(vendor: Dialect) -> Engine:
    engine = get_adapter(vendor).create_engine(TestConfig)
    with engine.connect() as conn:
        conn.exec_driver_sql('SELECT 1')
    return engine


def _get_chosen_vendor(node: Node) -> Dialect:
    """Resolve the winning vendor for the given test node (fast if cached)."""
    order = _get_node_vendors(node)
    vendor, _ = _get_engine(order)
    return vendor


def _get_engine(vendors: list[Dialect]) -> tuple[Dialect, Engine]:
    errors: list[tuple[str, Exception]] = []
    for vendor in vendors:
        try:
            return vendor, _create_vendor_engine(vendor)
        except Exception as e:
            errors.append((vendor, e))
    out = [f'{v}: {e}' for (v, e) in errors]
    pytest.skip('No DB vendor is available: ' + '; '.join(out))


def _get_node_vendors(node: Node) -> list[Dialect]:
    groups: list[Any] = [marker.args for marker in node.iter_markers(name='db')]
    groups.append([getattr(TestConfig, 'DB_VENDOR', '')])

    vendors: list[Dialect] = []
    for group in groups:
        for arg in group:
            parts = arg.split(',') if isinstance(arg, str) else [str(arg)]
            for vendor in parts:
                vendor = vendor.strip().lower()
                if vendor and is_dialect(vendor):
                    vendors.append(cast(Dialect, vendor))
        if vendors:
            return dedupe(vendors)

    raise ValueError('No supported DB vendors configured.')


# --- App ---


@pytest.fixture
def test_app(request, worker_id: str):
    """Create a test application."""
    vendor = _get_chosen_vendor(request.node)
    return _create_vendor_app(vendor, worker_id)


@pytest.fixture(autouse=True)
def test_app_in_context(test_app):
    """Push the test app onto the context."""
    with test_app.app_context():
        yield test_app


@pytest.fixture(autouse=True)
def test_app_request(test_app_in_context):
    """Push the test app onto the context and trigger preprocessing."""
    with test_app_in_context.test_request_context():
        test_app_in_context.preprocess_request()
        yield test_app_in_context


@pytest.fixture
def fixture_client(test_app_in_context):
    """Create a client fixture."""
    with test_app_in_context.test_client() as test_client:
        yield test_client


@pytest.fixture
def db_session(test_app, request) -> Generator[Session]:
    """Transactional SQLAlchemy Session that rolls back after each test."""
    # Dedicated connection
    conn = db.engine.connect()

    # If the test asked for fresh_db, truncate tables before any txn
    if 'fresh_db' in request.fixturenames:
        get_adapter(conn).truncate_tables(conn, TOP_LEVEL_TABLES)

    # Outer transaction
    txn = cast(Transaction, conn.begin())

    # Bind Flask session to the connection
    db.session.remove()
    db.session.configure(bind=conn)

    # Start a SAVEPOINT so tests can commit/flush
    db_session = db.session()
    db_session.begin_nested()

    @event.listens_for(db_session, 'after_transaction_end')
    def _restart_savepoint(session_: Session, session_txn: SessionTransaction) -> None:
        parent = getattr(session_txn, '_parent', None)
        if session_txn.nested and parent is not None and not parent.nested:
            # Re-open the SAVEPOINT after each nested txn ends
            session_.begin_nested()

    # Seed here so it’s inside the txn
    if 'fresh_db' in request.fixturenames:
        seed_database()

    try:
        yield db_session
    finally:
        with contextlib.suppress(Exception):
            event.remove(db.session, 'after_transaction_end', _restart_savepoint)
        with contextlib.suppress(Exception):
            db.session.remove()
        with contextlib.suppress(Exception):
            db.session.configure(bind=None)
        with contextlib.suppress(Exception):
            txn.rollback()
        with contextlib.suppress(Exception):
            conn.close()


@pytest.fixture
def fresh_db(test_app, request):
    """Refresh the test database by truncating and seeding."""
    # If db_session is also being used, let it handle the truncate/seed
    if 'db_session' in request.fixturenames:
        return True

    conn = db.engine.connect()
    try:
        get_adapter(conn).truncate_tables(conn, TOP_LEVEL_TABLES)
        seed_database()
        db.session.commit()
    finally:
        conn.close()

    return True


# --- Seeds ---


def seed_database() -> None:
    """Seed the database by running all seed functions."""
    pass


# --- Mocks ---


@pytest.fixture
def create_mock_statement_period(db_session):
    """Mock statement_period insert - required by other fixtures."""
    SQL_QUERY = """
        INSERT INTO statement_period(
            statement_period_id,
            statement_period_name,
            statement_period_status,
            statement_month,
            statement_year
        ) VALUES (1, 'Test Period', 'open', 1, 2024)
    """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(SQL_QUERY)
    adapter.set_fk(conn, True)


@pytest.fixture
def create_mock_accounting_period(db_session, create_mock_statement_period):
    """Mock accounting_period insert - required by other fixtures."""
    SQL_QUERY = """
        INSERT INTO accounting_period (
            accounting_period_id,
            statement_period_id,
            accounting_period_name,
            accounting_period_status,
            contract_type,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES (
            1, 1, 'Test Accounting Period', 'open', 'distribution',
            'Test', CURRENT_TIMESTAMP, 'Test', CURRENT_TIMESTAMP
        )
    """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(SQL_QUERY)
    adapter.set_fk(conn, True)


@pytest.fixture
def mock_accounting_runs():
    """Mock accounting runs response."""
    return [
        {'accounting_run_id': 1, 'accounting_run_status': 'Committed'},
        {'accounting_run_id': 2, 'accounting_run_status': 'Skipped'},
    ]


@pytest.fixture
def mock_sales_file():
    """Mock sales file response."""
    return [
        {'accounting_period_id': 1, 'file_name': 'Test File 1'},
        {'accounting_period_id': 2, 'file_name': 'Test File 2'},
    ]


@pytest.fixture
def create_mock_statement_period_adjustment_file(
    db_session, create_mock_statement_period
):
    """Mock statement_period_adjustment_file insert."""
    SQL_QUERY = """
        INSERT INTO statement_period_adjustment_file(
            `statement_period_adjustment_file_id`,
            `statement_period_id`,
            `batch_type`,
            `file_name`,
            `valid_file_location`,
            `invalid_file_location`,
            `valid_row_count`,
            `invalid_row_count`,
            `total_file_amount_multicurrency`,
            `total_rounded_amount_multicurrency`,
            `md5sum`,
            `error_type`,
            `created_by`,
            `created_at`,
            `last_modified_by`,
            `last_modified`,
            `deleted_by`,
            `deleted_at`
        ) VALUES (1, 1, 'upload', 'Test Adjustment file 1', 's3://qa-abacus-adjustments/text_excel.xlsx',
                 NULL, 5, NULL, '358.90123', '358.91', NULL, NULL, 'Test User', CURRENT_TIMESTAMP,
                 'Test User', CURRENT_TIMESTAMP, NULL, NULL),
                (2, 1, 'upload', 'Test Adjustment file 2', NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, 'Test User', CURRENT_TIMESTAMP, 'Test User', CURRENT_TIMESTAMP, NULL, NULL),
                (3, 1, 'upload', 'Test Adjustment file 3', NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, NULL, 'Test User', CURRENT_TIMESTAMP, 'Test User', CURRENT_TIMESTAMP, 'Test User', CURRENT_TIMESTAMP),
                (4, 1, 'auto', 'Test Adjustment file 4', NULL, NULL, NULL, NULL, NULL,
                 NULL, NULL, 'content_error', 'Test User', CURRENT_TIMESTAMP, 'Test User', CURRENT_TIMESTAMP, NULL, NULL)
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(SQL_QUERY)
    adapter.set_fk(conn, True)


@pytest.fixture
def create_mock_accounting_run(db_session, create_mock_accounting_period):
    """Mock accounting_run insert."""
    SQL_QUERY = """
        INSERT INTO accounting_run(
            accounting_run_id,
            accounting_period_id,
            run_controller_id,
            run_status,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, 1, 1, 'Committed', 'Test User', CURRENT_TIMESTAMP,'Test User', CURRENT_TIMESTAMP),
            (2, 1, 1, 'Skipped', 'Test User', CURRENT_TIMESTAMP, 'Test User', CURRENT_TIMESTAMP)
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(SQL_QUERY)
    adapter.set_fk(conn, True)


@pytest.fixture
def create_mock_sales_file(db_session, create_mock_accounting_period):
    """Mock sales_file insert."""
    SQL_QUERY = """
        INSERT INTO sales_file(
            `sales_file_id`,
            `file_name`,
            `accounting_period_id`,
            `row_count`,
            `main_url`,
            `amount_usd`,
            `created_by`,
            `created_at`,
            `last_modified_by`,
            `last_modified`
        ) VALUES (1, 'Test File', 1, 12, 'http://sales-file', '12.0', 'Test User', CURRENT_TIMESTAMP,'Test User', CURRENT_TIMESTAMP)
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(SQL_QUERY)
    adapter.set_fk(conn, True)


@pytest.fixture
def account_fixtures(db_session):
    """Create some accounts in the database."""
    sql = """
        INSERT INTO `account`
        (
            account_id,
            account_name,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        )
        VALUES
        (1, 'Test account 1', '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (2, 'Test account 2', '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (3, 'Test account 3', '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (4, 'Test account 4', '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (5, 'Test account 5', '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP);
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(sql)
    adapter.set_fk(conn, True)


@pytest.fixture
def account_payee_fixtures(db_session):
    """Create account_payee records in database."""
    sql = """
        INSERT INTO `account_payee`
        (
            account_payee_id,
            account_id,
            payoneer_payee_id,
            payoneer_payee_name,
            payoneer_iframe_url,
            payoneer_iframe_url_date,
            payoneer_session_id,
            payoneer_program_id,
            sap_vendor_id,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        )
        VALUES
        (1, 1, 101, 'Payoneer Payee 1', 'payoneer.com/fake_iframe_url', CURRENT_TIMESTAMP,
            'B99', 1, 1, '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (2, 2, 102, 'Payoneer Payee 2', 'payoneer.com/fake_iframe_url', CURRENT_TIMESTAMP,
            'B99', 1, 2, '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (3, 3, 103, 'Payoneer Payee 3', 'payoneer.com/fake_iframe_url', CURRENT_TIMESTAMP,
            'B99', 1, 3, '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (4, 4, 104, 'Payoneer Payee 4', 'payoneer.com/fake_iframe_url', CURRENT_TIMESTAMP,
            'B99', 1, 4, '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP),
        (5, 5, 105, 'Payoneer Payee 5', 'payoneer.com/fake_iframe_url', CURRENT_TIMESTAMP,
            'B99', 1, 5, '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP);
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(sql)
    adapter.set_fk(conn, True)


@pytest.fixture
def payoneer_program_fixtures(db_session):
    """Create some accounts in the database."""
    sql = """
        INSERT INTO `reference_payoneer_program`
        (
            payoneer_program_id,
            payoneer_program_name,
            funding_currency,
            reference_payment_type_id
        )
        VALUES
            (1, 'random program', 'TBD', 1);
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(sql)
    adapter.set_fk(conn, True)


@pytest.fixture
def payee_fixtures(db_session):
    """Create some payees in the database."""
    sql = """
        INSERT INTO `payee`
        (
            payee_id,
            reference_payment_type_id,
            payoneer_client_reference_id,
            payoneer_program_id,
            payee_type,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        )
        VALUES
        (1403, 8, '1', 1, 'collaborator', '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP);
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(sql)
    adapter.set_fk(conn, True)


@pytest.fixture
def payee_collaborator_fixtures(db_session):
    """Create some payee_collaborators in the database."""
    sql = """
        INSERT INTO `payee_collaborator`
        (
            payee_collaborator_id,
            collaborator_id,
            payee_id,
            account_id,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        )
        VALUES
        (1, 1, 1403, 1, '', CURRENT_TIMESTAMP, '', CURRENT_TIMESTAMP);
        """
    conn = db_session.connection()
    adapter = get_adapter(conn)
    adapter.set_fk(conn, False)
    conn.exec_driver_sql(sql)
    adapter.set_fk(conn, True)