#!/usr/bin/env python3
"""Static gates for the review database baseline.

This does not replace executing Flyway on PostgreSQL 16.  It catches drift,
broken references and common migration assembly mistakes before that gate.
"""

from __future__ import annotations

import importlib.util
from pathlib import Path
import re
import sys

sys.dont_write_bytecode = True


ROOT = Path(__file__).resolve().parents[1]
SQL_DIR = ROOT / "flyway" / "sql"
GENERATOR = ROOT / "tools" / "generate_flyway.py"


def fail(message: str) -> None:
    raise AssertionError(message)


def load_generator():
    spec = importlib.util.spec_from_file_location("generate_flyway", GENERATOR)
    if spec is None or spec.loader is None:
        fail("cannot load Flyway generator")
    module = importlib.util.module_from_spec(spec)
    sys.modules[spec.name] = module
    spec.loader.exec_module(module)
    return module


def migration_files() -> list[Path]:
    files = sorted(SQL_DIR.glob("V*__*.sql"), key=lambda p: int(p.name.split("__", 1)[0][1:]))
    versions = [int(path.name.split("__", 1)[0][1:]) for path in files]
    expected = list(range(1, max(versions, default=0) + 1))
    if versions != expected or versions[-1:] != [15]:
        fail(f"migration versions must be exactly V001..V015; got {versions}")
    return files


def find_matching_parenthesis(sql: str, opening: int) -> int:
    depth = 0
    i = opening
    quote: str | None = None
    dollar: str | None = None
    while i < len(sql):
        if dollar is not None:
            if sql.startswith(dollar, i):
                i += len(dollar)
                dollar = None
            else:
                i += 1
            continue
        char = sql[i]
        if quote is not None:
            if char == quote:
                if i + 1 < len(sql) and sql[i + 1] == quote:
                    i += 2
                    continue
                quote = None
            i += 1
            continue
        if sql.startswith("--", i):
            newline = sql.find("\n", i + 2)
            i = len(sql) if newline < 0 else newline + 1
            continue
        if sql.startswith("/*", i):
            end = sql.find("*/", i + 2)
            if end < 0:
                fail("unterminated block comment")
            i = end + 2
            continue
        if char in "'\"":
            quote = char
            i += 1
            continue
        if char == "$":
            match = re.match(r"\$[A-Za-z_][A-Za-z0-9_]*\$|\$\$", sql[i:])
            if match:
                dollar = match.group(0)
                i += len(dollar)
                continue
        if char == "(":
            depth += 1
        elif char == ")":
            depth -= 1
            if depth == 0:
                return i
            if depth < 0:
                fail("unexpected closing parenthesis")
        i += 1
    fail("unterminated parenthesized expression")
    return -1


def validate_lexical_balance(path: Path, sql: str) -> None:
    # Reuse the scanner from every top-level opening parenthesis and separately
    # ensure dollar quote tags have even occurrences.
    depth = 0
    i = 0
    quote: str | None = None
    dollar: str | None = None
    while i < len(sql):
        if dollar is not None:
            if sql.startswith(dollar, i):
                i += len(dollar)
                dollar = None
            else:
                i += 1
            continue
        char = sql[i]
        if quote is not None:
            if char == quote:
                if i + 1 < len(sql) and sql[i + 1] == quote:
                    i += 2
                    continue
                quote = None
            i += 1
            continue
        if sql.startswith("--", i):
            newline = sql.find("\n", i + 2)
            i = len(sql) if newline < 0 else newline + 1
            continue
        if sql.startswith("/*", i):
            end = sql.find("*/", i + 2)
            if end < 0:
                fail(f"{path.name}: unterminated block comment")
            i = end + 2
            continue
        if char in "'\"":
            quote = char
        elif char == "$":
            match = re.match(r"\$[A-Za-z_][A-Za-z0-9_]*\$|\$\$", sql[i:])
            if match:
                dollar = match.group(0)
                i += len(dollar) - 1
        elif char == "(":
            depth += 1
        elif char == ")":
            depth -= 1
            if depth < 0:
                fail(f"{path.name}: unexpected closing parenthesis")
        i += 1
    if quote or dollar or depth:
        fail(f"{path.name}: lexical imbalance quote={quote} dollar={dollar} depth={depth}")


def parse_tables(all_sql: str) -> dict[str, set[str]]:
    result: dict[str, set[str]] = {}
    pattern = re.compile(r"CREATE\s+TABLE\s+poker\.([a-z0-9_]+)\s*\(", re.I)
    for match in pattern.finditer(all_sql):
        name = match.group(1).lower()
        if name in result:
            fail(f"duplicate CREATE TABLE for {name}")
        opening = match.end() - 1
        body = all_sql[opening + 1 : find_matching_parenthesis(all_sql, opening)]
        columns: set[str] = set()
        for line in body.splitlines():
            stripped = line.strip().rstrip(",")
            token = re.match(r"([a-z_][a-z0-9_]*)\s+(?:uuid|bigint|integer|smallint|boolean|text|varchar|char|citext|bytea|jsonb|timestamptz|timestamp|date|inet|numeric|double)\b", stripped, re.I)
            if not token or token.group(1).upper() in {"CONSTRAINT", "PRIMARY", "UNIQUE", "CHECK", "FOREIGN"}:
                continue
            column = token.group(1).lower()
            if column in columns:
                fail(f"duplicate column {name}.{column}")
            columns.add(column)
        result[name] = columns
    # Forward migrations may add columns without changing historical CREATE TABLE.
    for table, column in re.findall(r"ALTER\s+TABLE\s+poker\.([a-z0-9_]+)\s+ADD\s+COLUMN\s+([a-z0-9_]+)", all_sql, re.I):
        if table not in result or column in result[table]:
            fail(f"invalid ADD COLUMN {table}.{column}")
        result[table].add(column)
    return result


def validate_constraint_names(all_sql: str) -> None:
    indexes: set[str] = set()
    for name in re.findall(r"CREATE\s+(?:UNIQUE\s+)?INDEX\s+([a-z0-9_]+)", all_sql, re.I):
        lowered = name.lower()
        if lowered in indexes:
            fail(f"duplicate schema index name {lowered}")
        indexes.add(lowered)

    per_table: dict[str, set[str]] = {}
    for table, constraint in re.findall(
        r"ALTER\s+TABLE\s+poker\.([a-z0-9_]+)\s+ADD\s+CONSTRAINT\s+([a-z0-9_]+)",
        all_sql,
        re.I,
    ):
        names = per_table.setdefault(table.lower(), set())
        if constraint.lower() in names:
            fail(f"duplicate constraint {table}.{constraint}")
        names.add(constraint.lower())


def validate_foreign_keys(all_sql: str, tables: dict[str, set[str]]) -> int:
    count = 0
    statements = re.findall(
        r"ALTER\s+TABLE\s+poker\.[a-z0-9_]+\s+ADD\s+CONSTRAINT\s+[a-z0-9_]+\s+.*?;",
        all_sql,
        re.I | re.S,
    )
    pattern = re.compile(
        r"ALTER\s+TABLE\s+poker\.([a-z0-9_]+).*?"
        r"FOREIGN\s+KEY\s*\(([^)]+)\)\s*"
        r"REFERENCES\s+poker\.([a-z0-9_]+)\s*\(([^)]+)\)",
        re.I | re.S,
    )
    for statement in statements:
        match = pattern.search(statement)
        if not match:
            continue
        source, source_cols, target, target_cols = match.groups()
        source = source.lower()
        target = target.lower()
        if source not in tables or target not in tables:
            fail(f"FK references unknown table {source} -> {target}")
        left = [item.strip().lower() for item in source_cols.split(",")]
        right = [item.strip().lower() for item in target_cols.split(",")]
        if len(left) != len(right):
            fail(f"FK arity mismatch {source}({left}) -> {target}({right})")
        for column in left:
            if column not in tables[source]:
                fail(f"FK source column missing: {source}.{column}")
        for column in right:
            if column not in tables[target]:
                fail(f"FK target column missing: {target}.{column}")
        count += 1
    if count < 200:
        fail(f"unexpectedly low foreign-key count: {count}")
    return count


def main() -> None:
    generator = load_generator()
    dictionary_tables = generator.parse_dictionary()
    explicit_fields = sum(len(table.columns) for table in dictionary_tables)
    if explicit_fields != generator.EXPECTED_EXPLICIT_FIELD_COUNT:
        fail(
            "dictionary explicit-field count drifted from "
            f"{generator.EXPECTED_EXPLICIT_FIELD_COUNT}"
        )

    files = migration_files()
    chunks: list[str] = []
    for path in files:
        sql = path.read_text(encoding="utf-8")
        validate_lexical_balance(path, sql)
        chunks.append(sql)
    all_sql = "\n".join(chunks)

    tables = parse_tables(all_sql)
    expected_names = {table.name for table in dictionary_tables}
    if set(tables) != expected_names or len(tables) != generator.EXPECTED_TABLE_COUNT:
        fail(
            f"DDL/dictionary table mismatch: ddl={len(tables)} dictionary={len(expected_names)} "
            f"missing={sorted(expected_names - set(tables))} extra={sorted(set(tables) - expected_names)}"
        )

    for table in dictionary_tables:
        expected_columns = {c.name for c in table.columns}
        expected_columns |= {"created_at"}
        if table.name not in generator.SHARED_PRIMARY_KEY:
            expected_columns.add("id")
        if not table.immutable:
            expected_columns |= {"updated_at", "row_version"}
        if expected_columns != tables[table.name]:
            fail(f"DDL/dictionary column mismatch {table.name}: {expected_columns ^ tables[table.name]}")
    validate_constraint_names(all_sql)
    fk_count = validate_foreign_keys(all_sql, tables)

    for table in generator.SHARED_PRIMARY_KEY:
        required = {"created_at", "updated_at", "row_version"}
        if not required <= tables[table]:
            fail(f"shared-PK table {table} lost lifecycle columns")
    for table in generator.IMMUTABLE_TABLES:
        forbidden = {"updated_at", "row_version"} & tables[table]
        if forbidden:
            fail(f"immutable table {table} has mutable lifecycle columns {forbidden}")

    wallet_sql = next(chunk for path, chunk in zip(files, chunks) if path.name.startswith("V004__"))
    expected_generated = (
        "total_amount bigint GENERATED ALWAYS AS "
        "(available_amount + topup_frozen_amount + table_escrow_amount) STORED"
    )
    if expected_generated not in wallet_sql:
        fail("wallet total_amount is not the reviewed generated column")

    if "ck_app_user_status_1 CHECK (status IN ('ACTIVE', 'DISABLED'))" not in all_sql:
        fail("constraint-column enum extraction regressed")
    if {"tg_user_id", "tg_username", "tg_display_name"} & tables["app_user"]:
        fail("app_user still contains Telegram-specific identity fields")
    required_identity_columns = {
        "user_id", "provider", "provider_subject", "verified_at", "bound_at", "unbound_at"
    }
    if not required_identity_columns <= tables["player_identity"]:
        fail("player_identity lost required provider mapping columns")
    required_credential_columns = {
        "identity_id", "provider", "password_hash", "failed_login_count", "locked_until", "status"
    }
    if not required_credential_columns <= tables["local_credential"]:
        fail("local_credential lost required lock or secret columns")
    required_identity_guards = (
        "uq_player_identity_subject",
        "uq_player_identity_active_provider",
        "ck_player_identity_subject",
        "fk_local_credential_identity_provider",
        "ck_local_credential_password_hash",
        "ck_local_credential_lock_state",
        "fk_player_session_identity_user",
        "fk_login_event_identity_user",
        "fk_login_event_identity_provider",
    )
    for guard in required_identity_guards:
        if guard not in all_sql:
            fail(f"identity guard is missing: {guard}")
    if all_sql.count("'BP-") < 31:
        fail("permission seed does not contain BP-01..BP-31")
    if "AGENT_GENERAL_DEFAULT" not in all_sql:
        fail("GENERAL zero-rate default policy is missing")
    if re.search(r"GRANT\s+SELECT\s+ON\s+ALL\s+TABLES[^;]*poker_readonly", all_sql, re.I):
        fail("poker_readonly must not receive raw-table SELECT")
    if "ALTER SCHEMA poker OWNER TO poker_owner" not in all_sql:
        fail("poker objects are not transferred to the NOLOGIN owner")

    function = re.search(
        r"CREATE\s+OR\s+REPLACE\s+FUNCTION\s+poker\.post_ledger_transaction\s*\((.*?)\)\s*RETURNS",
        all_sql,
        re.I | re.S,
    )
    if not function or function.group(1).count("p_") != 12:
        fail("post_ledger_transaction signature drifted")
    if all_sql.count("ON FUNCTION poker.post_ledger_transaction(") != 2:
        fail("ledger function must have matching REVOKE and GRANT signatures")

    test_files = sorted((ROOT / "tests" / "sql").glob("*.sql"))
    if len(test_files) != 5:
        fail(f"expected five PostgreSQL test scripts, got {len(test_files)}")
    for test_path in test_files:
        validate_lexical_balance(test_path, test_path.read_text(encoding="utf-8"))

    print(
        "static validation passed: "
        f"migrations={len(files)} tables={len(tables)} explicit_fields={explicit_fields} "
        f"foreign_keys={fk_count} indexes={len(re.findall(r'CREATE\s+(?:UNIQUE\s+)?INDEX', all_sql, re.I))}"
    )


if __name__ == "__main__":
    main()
