from __future__ import annotations

import os
from pathlib import Path
from urllib.parse import urlparse

import pytest
import sqlalchemy as sa
from alembic.config import Config
from alembic.script import ScriptDirectory

from alembic import command
from tests.support.database_bootstrap import resolve_test_database_config

SERVER_DIR = Path(__file__).resolve().parents[2]
ALEMBIC_INI_PATH = SERVER_DIR / "alembic.ini"
ALEMBIC_SCRIPT_PATH = SERVER_DIR / "alembic"


def test_migration_graph_has_exactly_one_head() -> None:
    """`alembic upgrade head` must remain deployable without manual choices."""
    alembic_config = Config(str(ALEMBIC_INI_PATH))
    alembic_config.set_main_option("script_location", str(ALEMBIC_SCRIPT_PATH))
    heads = ScriptDirectory.from_config(alembic_config).get_heads()
    assert len(heads) == 1, f"migration graph has multiple heads: {heads}"


def _reset_database(engine: sa.Engine) -> None:
    inspector = sa.inspect(engine)
    table_names = inspector.get_table_names()
    if not table_names:
        return

    with engine.begin() as connection:
        connection.execute(sa.text("SET FOREIGN_KEY_CHECKS = 0"))
        for table_name in table_names:
            connection.execute(sa.text(f"DROP TABLE IF EXISTS `{table_name}`"))
        connection.execute(sa.text("SET FOREIGN_KEY_CHECKS = 1"))


@pytest.fixture
def legacy_business_payments_database():
    base_async_url = os.environ.get(
        "TEST_DATABASE_URL",
        "mysql+aiomysql://helix:helix@localhost:3306/helix",
    )
    base_sync_url = os.environ.get(
        "TEST_DATABASE_URL_SYNC",
        "mysql+pymysql://helix:helix@localhost:3306/helix",
    )
    config = resolve_test_database_config(
        test_async_base=base_async_url,
        test_sync_base=base_sync_url,
        admin_url=os.environ.get("TEST_DATABASE_ADMIN_URL"),
    )
    normalized_url = config.async_url.replace("mysql+aiomysql://", "mysql://").replace(
        "mysql+asyncmy://", "mysql://"
    )
    parsed = urlparse(normalized_url)
    sync_url = f"mysql+pymysql://{parsed.username}:{parsed.password}@{parsed.hostname}:{parsed.port}{parsed.path}"
    engine = sa.create_engine(sync_url)
    try:
        _reset_database(engine)
        yield engine, sync_url
    finally:
        _reset_database(engine)
        engine.dispose()


def _seed_legacy_business_payment_schema(engine: sa.Engine) -> None:
    statements = [
        """
        CREATE TABLE alembic_version (
            version_num VARCHAR(32) NOT NULL PRIMARY KEY
        )
        """,
        "INSERT INTO alembic_version (version_num) VALUES ('001')",
        """
        CREATE TABLE identity_persons (
            id CHAR(32) NOT NULL PRIMARY KEY,
            display_name VARCHAR(255) NULL,
            password_hash VARCHAR(255) NULL
        )
        """,
        """
        CREATE TABLE asset_suppliers (
            id CHAR(32) NOT NULL PRIMARY KEY
        )
        """,
        """
        CREATE TABLE asset_procurement_orders (
            id CHAR(32) NOT NULL PRIMARY KEY
        )
        """,
        """
        CREATE TABLE asset_software_catalog (
            id CHAR(32) NOT NULL PRIMARY KEY
        )
        """,
        """
        CREATE TABLE asset_business_payments (
            id VARCHAR(36) NOT NULL PRIMARY KEY,
            software_name VARCHAR(128) NOT NULL,
            software_type VARCHAR(64) NULL,
            payment_frequency VARCHAR(64) NULL,
            payment_method VARCHAR(64) NULL,
            signing_entity VARCHAR(128) NULL,
            po_number VARCHAR(64) NULL,
            contract_expiry VARCHAR(128) NULL,
            requester VARCHAR(64) NULL,
            amount DECIMAL(12, 2) NULL,
            remark TEXT NULL,
            created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
            updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
        )
        """,
        """
        INSERT INTO asset_business_payments (
            id, software_name, payment_frequency, amount
        ) VALUES (
            'bp-legacy-001', 'Notion', 'annual', 100.00
        )
        """,
    ]

    with engine.begin() as connection:
        for statement in statements:
            connection.execute(sa.text(statement))


def _seed_partially_migrated_business_payment_schema(engine: sa.Engine) -> None:
    _seed_legacy_business_payment_schema(engine)
    statements = [
        "ALTER TABLE asset_business_payments ADD COLUMN software_catalog_id VARCHAR(36) NULL",
        "ALTER TABLE asset_business_payments ADD COLUMN procurement_order_id VARCHAR(36) NULL",
        "ALTER TABLE asset_business_payments ADD COLUMN supplier_id VARCHAR(36) NULL",
        "ALTER TABLE asset_business_payments ADD COLUMN requester_person_id VARCHAR(36) NULL",
    ]

    with engine.begin() as connection:
        for statement in statements:
            connection.execute(sa.text(statement))


def _assert_business_payment_relationship_shape(engine: sa.Engine) -> None:
    inspector = sa.inspect(engine)
    columns = {
        column["name"]: column
        for column in inspector.get_columns("asset_business_payments")
    }

    required_columns = {
        "software_catalog_id",
        "procurement_order_id",
        "supplier_id",
        "requester_person_id",
    }
    assert required_columns.issubset(columns)
    for column_name in required_columns:
        assert str(columns[column_name]["type"]).upper().startswith("CHAR(32)")

    foreign_keys = {
        (tuple(foreign_key["constrained_columns"]), foreign_key["referred_table"])
        for foreign_key in inspector.get_foreign_keys("asset_business_payments")
    }
    assert ((("software_catalog_id",), "asset_software_catalog")) in foreign_keys
    assert ((("procurement_order_id",), "asset_procurement_orders")) in foreign_keys
    assert ((("supplier_id",), "asset_suppliers")) in foreign_keys
    assert ((("requester_person_id",), "identity_persons")) in foreign_keys


@pytest.mark.skip(
    reason="Legacy fixture stamps alembic_version='001' but only seeds a minimal "
    "subset of tables. Later migrations (approval FKs, index/trigger/dedup on "
    "tables that 001 would have created) break against this artificial state. "
    "No real deployment can be in this state. Fix: either rebuild the fixture "
    "to include everything 001 creates, or delete these tests."
)
def test_upgrade_repairs_business_payment_relationship_columns(
    legacy_business_payments_database,
    monkeypatch: pytest.MonkeyPatch,
):
    engine, sync_url = legacy_business_payments_database
    _seed_legacy_business_payment_schema(engine)

    alembic_config = Config(str(ALEMBIC_INI_PATH))
    alembic_config.set_main_option("script_location", str(ALEMBIC_SCRIPT_PATH))
    alembic_config.set_main_option("sqlalchemy.url", sync_url)
    monkeypatch.setenv("DATABASE_URL_SYNC", sync_url)

    command.upgrade(alembic_config, "head")

    _assert_business_payment_relationship_shape(engine)

    with engine.connect() as connection:
        version = connection.execute(
            sa.text("SELECT version_num FROM alembic_version")
        ).scalar_one()
        row = connection.execute(
            sa.text(
                """
                SELECT software_name, software_catalog_id, procurement_order_id,
                       supplier_id, requester_person_id
                FROM asset_business_payments
                WHERE id = 'bp-legacy-001'
                """
            )
        ).mappings().one()

    expected_head = ScriptDirectory.from_config(alembic_config).get_current_head()
    assert version == expected_head
    assert row["software_name"] == "Notion"
    assert row["software_catalog_id"] is None
    assert row["procurement_order_id"] is None
    assert row["supplier_id"] is None
    assert row["requester_person_id"] is None


@pytest.mark.skip(
    reason="Legacy fixture only creates a tiny table subset — see reason on "
    "test_upgrade_repairs_business_payment_relationship_columns."
)
def test_upgrade_recovers_from_partial_business_payment_migration(
    legacy_business_payments_database,
    monkeypatch: pytest.MonkeyPatch,
):
    engine, sync_url = legacy_business_payments_database
    _seed_partially_migrated_business_payment_schema(engine)

    alembic_config = Config(str(ALEMBIC_INI_PATH))
    alembic_config.set_main_option("script_location", str(ALEMBIC_SCRIPT_PATH))
    alembic_config.set_main_option("sqlalchemy.url", sync_url)
    monkeypatch.setenv("DATABASE_URL_SYNC", sync_url)

    command.upgrade(alembic_config, "head")

    _assert_business_payment_relationship_shape(engine)