#!/usr/bin/env python3
"""Generate the review Flyway table baseline from dictionary.md.

The Markdown dictionary remains the source of truth until the first Flyway
baseline is released.  This generator deliberately handles only mechanical
DDL: columns, common metadata, single-column PK/UQ, simple CHECK constraints,
and declared foreign keys.  Cross-row and workflow constraints live in later,
hand-reviewed migrations.
"""

from __future__ import annotations

from dataclasses import dataclass, field
from hashlib import sha1
from pathlib import Path
import re


IMPLEMENTATION_ROOT = Path(__file__).resolve().parents[1]
DICTIONARY = IMPLEMENTATION_ROOT / "dictionary.md"
OUTPUT = IMPLEMENTATION_ROOT / "flyway" / "sql"


@dataclass
class Column:
    name: str
    sql_type: str
    constraint_text: str
    description: str


@dataclass
class Table:
    number: str
    name: str
    heading_line: int
    prose: list[str] = field(default_factory=list)
    columns: list[Column] = field(default_factory=list)

    @property
    def chapter(self) -> int:
        return int(self.number.split(".", 1)[0])

    @property
    def immutable(self) -> bool:
        return self.name in IMMUTABLE_TABLES


HEADING = re.compile(r"^###\s+(\d+\.\d+)\s+`([^`]+)`")
ROW = re.compile(r"^\|\s*`([^`]+)`\s*\|\s*`([^`]+)`\s*\|\s*([^|]+?)\s*\|\s*(.*?)\s*\|$")
FK = re.compile(r"FK\s*→\s*`([^`.]+)\.([^`]+)`")
ENUM_TOKEN = re.compile(r"^[A-Z][A-Z0-9_]*(?:/[A-Z][A-Z0-9_]*)+$")

EXPECTED_TABLE_COUNT = 92
EXPECTED_EXPLICIT_FIELD_COUNT = 1001
DICTIONARY_VERSION = "V1.5-review"

SHARED_PRIMARY_KEY = {"local_credential", "user_preference", "user_timebank"}
IMMUTABLE_TABLES = {
    "outbox_event",
    "player_login_event",
    "protocol_acceptance",
    "invite_application_event",
    "invite_redemption",
    "ledger_entry",
    "revenue_note",
    "room_rule_snapshot",
    "room_state_snapshot",
    "room_state_event",
    "hand_action",
    "hand_event",
    "hand_pot_award",
    "commission_attribution",
    "commission_statement_adjustment",
    "commission_test_payment_mark",
    "report_appendix",
    "ticket_event",
    "private_card_access_log",
    "private_card_access_log_player",
    "admin_auth_event",
    "audit_log",
    "export_download_log",
    "analytics_event",
}
MIGRATION_GROUPS = {
    "V002__identity.sql": {4},
    "V003__organization_and_rules.sql": {5, 6},
    "V004__ledger.sql": {7},
    "V005__room_and_seat.sql": {8},
    "V006__hand_and_settlement.sql": {9},
    "V007__commission_and_support.sql": {10, 11},
    "V008__admin_and_operations.sql": {12, 13},
    "V009__test_and_analytics.sql": {14},
}


def parse_dictionary() -> list[Table]:
    lines = DICTIONARY.read_text(encoding="utf-8").splitlines()
    tables: list[Table] = []
    current: Table | None = None
    for line_no, line in enumerate(lines, 1):
        heading = HEADING.match(line)
        if heading:
            current = Table(heading.group(1), heading.group(2), line_no)
            tables.append(current)
            continue
        if current is None:
            continue
        if line.startswith("## "):
            current = None
            continue
        row = ROW.match(line)
        if row and row.group(1) != "字段":
            current.columns.append(Column(*(value.strip() for value in row.groups())))
        elif line and not line.startswith("|"):
            current.prose.append(line)

    explicit_fields = sum(len(table.columns) for table in tables)
    if len(tables) != EXPECTED_TABLE_COUNT or explicit_fields != EXPECTED_EXPLICIT_FIELD_COUNT:
        raise SystemExit(
            f"dictionary shape changed: tables={len(tables)} fields={explicit_fields}; "
            "review the generator before accepting a new baseline"
        )
    names = {table.name for table in tables}
    if len(names) != len(tables):
        raise SystemExit("duplicate table name in dictionary")
    for table in tables:
        seen: set[str] = set()
        for column in table.columns:
            if column.name in seen:
                raise SystemExit(f"duplicate column {table.name}.{column.name}")
            seen.add(column.name)
            match = FK.search(column.constraint_text)
            if match and match.group(1) not in names:
                raise SystemExit(
                    f"unknown FK target {match.group(1)} at {table.name}.{column.name}"
                )
    return tables


def sql_name(prefix: str, *parts: str) -> str:
    raw = "_".join((prefix, *parts))
    if len(raw) <= 60:
        return raw
    digest = sha1(raw.encode("utf-8")).hexdigest()[:8]
    return f"{raw[:51]}_{digest}"


def sql_type(table: str, column: Column) -> str:
    if column.sql_type == "GENERATED/投影" or "GENERATED/投影" in column.constraint_text:
        if table == "wallet_balance_projection" and column.name == "total_amount":
            return (
                "bigint GENERATED ALWAYS AS "
                "(available_amount + topup_frozen_amount + table_escrow_amount) STORED"
            )
        raise SystemExit(f"unmapped generated column {table}.{column.name}")
    return column.sql_type


def default_clause(column: Column) -> str | None:
    match = re.search(r"default\s+([^,|]+)", column.constraint_text, re.I)
    if not match:
        return None
    value = match.group(1).strip().strip("`")
    if value == "{}":
        if column.sql_type == "jsonb":
            return "'{}'::jsonb"
        if column.sql_type.endswith("[]"):
            return f"'{{}}'::{column.sql_type}"
    return value


def enum_values(column: Column) -> list[str]:
    # Most dictionary enums are declared in the constraint column (for
    # example ``CK `ACTIVE/DISABLED```); a few legacy rows only repeat the
    # values in the description.  Read both so generated DDL cannot silently
    # lose a domain check merely because the prose layout differs.
    source = f"{column.constraint_text} {column.description}"
    for token in re.findall(r"`([^`]+)`", source):
        if ENUM_TOKEN.fullmatch(token):
            return token.split("/")
    return []


def simple_checks(column: Column) -> list[str]:
    text = column.constraint_text.replace("–", "-").replace("≤", "<=").replace("≥", ">=")
    result: list[str] = []
    name = column.name
    numeric_type = column.sql_type.startswith(("smallint", "integer", "bigint", "numeric"))
    if "CK" in text:
        if numeric_type and re.search(r"CK\s*>\s*0", text):
            result.append(f"{name} > 0")
        elif numeric_type and re.search(r"CK\s*>=\s*0", text):
            result.append(f"{name} >= 0")
        elif numeric_type and re.search(r"CK\s*<>\s*0", text):
            result.append(f"{name} <> 0")
        elif numeric_type and re.search(r"CK\s*=\s*1", text):
            result.append(f"{name} = 1")
        else:
            between = re.search(r"CK\s*(-?\d+)\s*-\s*(-?\d+)", text)
            if numeric_type and between:
                result.append(f"{name} BETWEEN {between.group(1)} AND {between.group(2)}")
            upper = re.search(r"CK\s*<=\s*(\d+)", text)
            if numeric_type and upper:
                result.append(f"{name} <= {upper.group(1)}")
            literal = re.search(r"CK\s+`([A-Z0-9_]+)`", text)
            if literal:
                result.append(f"{name} = '{literal.group(1)}'")
    values = enum_values(column)
    if values:
        quoted = ", ".join(f"'{value}'" for value in values)
        result.append(f"{name} IN ({quoted})")
    return result


def render_column(table: Table, column: Column) -> tuple[str, list[str]]:
    pieces = [column.name, sql_type(table.name, column)]
    constraints = column.constraint_text
    if "PK" in constraints:
        pieces.append("PRIMARY KEY")
    if "NOT NULL" in constraints and "PRIMARY KEY" not in pieces:
        pieces.append("NOT NULL")
    default = default_clause(column)
    if default is not None:
        pieces.extend(("DEFAULT", default))
    if "UQ" in constraints:
        pieces.append("UNIQUE")
    checks = []
    for expression in simple_checks(column):
        name = sql_name("ck", table.name, column.name, str(len(checks) + 1))
        checks.append(f"CONSTRAINT {name} CHECK ({expression})")
    return " ".join(pieces), checks


def common_columns(table: Table) -> list[str]:
    if table.name in SHARED_PRIMARY_KEY:
        return [
            "created_at timestamptz NOT NULL DEFAULT now()",
            "updated_at timestamptz NOT NULL DEFAULT now()",
            "row_version bigint NOT NULL DEFAULT 0",
        ]
    columns = ["id uuid PRIMARY KEY DEFAULT gen_random_uuid()"]
    columns.append("created_at timestamptz NOT NULL DEFAULT now()")
    if not table.immutable:
        columns.extend(
            (
                "updated_at timestamptz NOT NULL DEFAULT now()",
                "row_version bigint NOT NULL DEFAULT 0",
            )
        )
    return columns


def render_table(table: Table) -> str:
    definitions = common_columns(table)
    table_checks: list[str] = []
    for column in table.columns:
        rendered, checks = render_column(table, column)
        definitions.append(rendered)
        table_checks.extend(checks)
    definitions.extend(table_checks)
    body = ",\n".join(f"    {definition}" for definition in definitions)
    return (
        f"-- Dictionary {table.number}, source line {table.heading_line}\n"
        f"CREATE TABLE poker.{table.name} (\n{body}\n);\n"
    )


def render_fk_migration(tables: list[Table]) -> str:
    statements = [
        "-- Generated foreign keys. Cross-table composite constraints are added later.",
        "SET search_path TO poker, public;",
        "",
    ]
    for table in tables:
        for column in table.columns:
            match = FK.search(column.constraint_text)
            if not match:
                continue
            target_table, target_column = match.groups()
            constraint = sql_name("fk", table.name, column.name, target_table)
            statements.append(
                f"ALTER TABLE poker.{table.name} ADD CONSTRAINT {constraint} "
                f"FOREIGN KEY ({column.name}) REFERENCES poker.{target_table}({target_column}) "
                "DEFERRABLE INITIALLY IMMEDIATE;"
            )
    return "\n".join(statements) + "\n"


def write_migrations(tables: list[Table]) -> None:
    OUTPUT.mkdir(parents=True, exist_ok=True)
    for filename, chapters in MIGRATION_GROUPS.items():
        selected = [table for table in tables if table.chapter in chapters]
        content = [
            f"-- Generated from dictionary.md {DICTIONARY_VERSION}.",
            "-- Do not edit after release; create a new Flyway migration instead.",
            "SET search_path TO poker, public;",
            "",
        ]
        content.extend(render_table(table) for table in selected)
        (OUTPUT / filename).write_text("\n".join(content), encoding="utf-8", newline="\n")
    (OUTPUT / "V010__declared_foreign_keys.sql").write_text(
        render_fk_migration(tables), encoding="utf-8", newline="\n"
    )


def main() -> None:
    tables = parse_dictionary()
    raise SystemExit("V001..V014 are frozen. Add a reviewed forward migration; generator writing is disabled in IB-1.1.")
    write_migrations(tables)
    print(
        f"generated {len(MIGRATION_GROUPS) + 1} migrations for "
        f"{len(tables)} tables and {sum(len(t.columns) for t in tables)} explicit fields"
    )


if __name__ == "__main__":
    main()
