#!/usr/bin/env python3
"""Generate Mermaid ER diagrams (domain-split + full overview) from the live schema.

Reads docs/audit/raw/columns_full.csv and docs/audit/raw/constraints.csv,
which are dumped from an isolated PostgreSQL 16 instance that had every
migration in apps/backend/migrations applied in version order.

Output: docs/diagrams/*.mmd
"""
import csv
import pathlib
import re

ROOT = pathlib.Path(__file__).resolve().parents[2]
RAW = ROOT / "docs" / "audit" / "raw"
OUT = ROOT / "docs" / "diagrams"
OUT.mkdir(parents=True, exist_ok=True)

cols = list(csv.DictReader(open(RAW / "columns_full.csv", encoding="utf-8")))
cons = list(csv.DictReader(open(RAW / "constraints.csv", encoding="utf-8")))

by_table = {}
for c in cols:
    by_table.setdefault(c["table_name"], []).append(c)

fks = {}
pk = {}
for r in cons:
    if r["contype"] == "f":
        m = re.match(r"FOREIGN KEY \(([^)]+)\) REFERENCES ([a-z_]+)\(([^)]+)\)(.*)", r["definition"])
        if m:
            fks.setdefault(r["table_name"], []).append((m.group(1), m.group(2), m.group(4).strip()))
    elif r["contype"] == "p":
        m = re.search(r"PRIMARY KEY \(([^)]+)\)", r["definition"])
        if m:
            pk[r["table_name"]] = m.group(1)

# Logical domains. Each table appears in exactly one domain ERD; the full ERD
# lists every table (attributes trimmed to PK/FK/money/status).
DOMAINS = {
    "auth-tenancy": ["users", "roles", "role_permissions", "personal_access_tokens",
                     "password_reset_tokens", "stores", "store_users", "activity_logs",
                     "app_settings", "schema_migrations", "migrations", "document_number_sequences"],
    "catalog": ["products", "categories", "units", "brands", "product_units",
                "product_barcodes", "suppliers", "customers", "memberships",
                "membership_tiers", "membership_tier_history", "membership_tier_config_history"],
    "inventory": ["warehouses", "stocks", "stock_movements", "stock_opnames",
                  "stock_opname_items", "stock_transfers", "stock_transfer_items"],
    "sales": ["sales_orders", "sales_order_items", "sales_order_status_history",
              "sales_returns", "sales_return_items", "sales_vouchers", "sales_targets",
              "galon_transactions", "carts", "cart_items"],
    "purchasing": ["purchase_orders", "purchase_order_items", "goods_receipts",
                   "goods_receipt_items", "vouchers", "warehouse_deposits"],
    "finance": ["invoices", "payments", "cashflows", "cash_accounts", "payment_vouchers",
                "payment_voucher_lines", "advances", "advance_transactions"],
    "accounting": ["accounts", "accounting_departments", "accounting_periods",
                   "journal_entries", "journal_lines"],
    "pos": ["cashier_shifts", "cashier_drawer_entries"],
    "distribution": ["delivery_orders", "delivery_order_items", "shipments",
                     "shipment_items", "shipment_tracking_events", "dispatches",
                     "delivery_status_history", "fleet", "lhpp_groups", "lhpp_group_items",
                     "lhpp_group_history"],
    "hr": ["employees", "attendances", "leaves", "payrolls", "performances"],
    "salestracking": ["sales_visits", "sales_activities", "sales_locations", "sales_routes"],
    "support-reports": ["tickets", "ticket_comments", "report_templates"],
    "framework": ["cache", "cache_locks", "jobs", "job_batches", "failed_jobs", "sessions"],
}

KEEP_ATTRS = {"id", "status", "type", "order_type", "source_type", "amount", "total_amount",
              "quantity", "remaining_amount", "paid_amount", "discount_amount",
              "quantity_before", "quantity_after", "role", "permission", "code",
              "email", "name", "is_active", "deleted_at", "created_at", "updated_at",
              "snap_order_ref", "tier", "points", "period_year", "period_month"}


def attrs_for(table, trim=True):
    out = []
    for c in by_table.get(table, []):
        col = c["column_name"]
        is_pk = col == pk.get(table)
        is_fk = any(col == f[0] for f in fks.get(table, []))
        if trim and not (is_pk or is_fk or col in KEEP_ATTRS):
            continue
        typ = c["type"].replace(" ", "_").replace("(", "_").replace(")", "").replace(",", "_")
        marks = []
        if is_pk:
            marks.append("PK")
        if is_fk:
            marks.append("FK")
        suffix = f' "{",".join(marks)}"' if marks else ""
        out.append(f"        {typ} {col}{suffix}")
    return out


def render(name, tables, trim=True, title=""):
    lines = ["erDiagram"]
    present = set()
    for t in tables:
        if t not in by_table:
            continue
        present.add(t)
        lines.append(f"    {t} {{")
        lines.extend(attrs_for(t, trim=trim))
        lines.append("    }")
    seen = set()
    for t in tables:
        for col, ref, _rest in fks.get(t, []):
            if ref not in present:
                continue
            key = (t, ref, col)
            if key in seen:
                continue
            seen.add(key)
            lines.append(f'    {ref} ||--o{{ {t} : "{col}"')
    path = OUT / f"{name}.mmd"
    path.write_text("\n".join(lines) + "\n", encoding="utf-8")
    return path, len(present), len(seen)


def main():
    report = []
    for name, tables in DOMAINS.items():
        p, nt, ne = render(f"erd-{name}", tables)
        report.append((name, nt, ne, p))
    # Full overview: every table, PK/FK only.
    all_tables = sorted(by_table)
    p, nt, ne = render("erd-full", all_tables, trim=True)
    report.append(("full", nt, ne, p))
    for r in report:
        print(r)


if __name__ == "__main__":
    main()