#!/usr/bin/env bash
# Extracts table → column inventory from all *QB.cs files in the GB5 solution.
# Output: docs/GB5-Schema-Registry.md
# Usage: bash tools/extract-schema.sh
# Re-run after every DB migration to keep the registry current.

set -euo pipefail
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
REPO_ROOT="$(dirname "$SCRIPT_DIR")"

python3 - "$REPO_ROOT" <<'PYEOF'
import sys, os, re, glob
from collections import defaultdict

repo_root = sys.argv[1]
output_path = os.path.join(repo_root, "docs", "GB5-Schema-Registry.md")
os.makedirs(os.path.join(repo_root, "docs"), exist_ok=True)

# Find all *QB.cs files
qb_files = glob.glob(os.path.join(repo_root, "**", "*QB.cs"), recursive=True)
qb_files = [f for f in qb_files if "/bin/" not in f and "/obj/" not in f]
qb_files.sort()

# table -> {columns: set, module: str, file: str}
tables = defaultdict(lambda: {"columns": [], "columns_set": set(), "module": "", "file": ""})

INSERT_RE = re.compile(r'INSERT\s+INTO\s+([A-Z_][A-Z0-9_]*)', re.IGNORECASE)
COL_NAME_RE = re.compile(r'\b([A-Z][A-Z0-9_]{2,})\b')
SKIP_KEYWORDS = {
    'INSERT','INTO','VALUES','SELECT','FROM','WHERE','AND','OR','NOT','NULL',
    'DEFAULT','SET','UPDATE','DELETE','BEGIN','END','TRANSACTION','COMMIT',
    'ROLLBACK','CREATE','TABLE','INDEX','ON','AS','BY','GROUP','ORDER',
    'INNER','LEFT','RIGHT','JOIN','OUTER','FULL','CROSS','HAVING','WITH',
    'DISTINCT','TOP','NOLOCK','UPDLOCK','ROWLOCK','GETUTCDATE','GETDATE',
    'IDENTITY','INT','NVARCHAR','VARCHAR','BIT','DATETIME','DECIMAL','BIGINT',
    'SMALLINT','TINYINT','UNIQUEIDENTIFIER','NCHAR','CHAR','FLOAT','MONEY',
    'CAST','CONVERT','ISNULL','COALESCE','CASE','WHEN','THEN','ELSE',
    'CONSTRAINT','PRIMARY','KEY','FOREIGN','REFERENCES','UNIQUE',
    'SCOPE_IDENTITY','OUTPUT','INSERTED','DELETED','NEWID',
}

for qb_file in qb_files:
    # Infer module name from path (e.g. MMDAL, AccountsDAL)
    parts = qb_file.replace(repo_root, "").split(os.sep)
    module = next((p for p in parts if p.endswith("DAL") or p.endswith("BLL")), "Unknown")

    try:
        content = open(qb_file, encoding="utf-8", errors="ignore").read()
    except Exception:
        continue

    # Find all INSERT INTO blocks
    for m in INSERT_RE.finditer(content):
        table = m.group(1).upper()
        # Find the opening paren after the table name
        start = m.end()
        paren_start = content.find("(", start)
        if paren_start == -1:
            continue
        # Find VALUES keyword to close the column list
        values_match = re.search(r'\bVALUES\b', content[paren_start:], re.IGNORECASE)
        if not values_match:
            continue
        col_block = content[paren_start : paren_start + values_match.start()]

        cols = [
            c for c in COL_NAME_RE.findall(col_block)
            if c not in SKIP_KEYWORDS and not c.startswith("@")
        ]

        entry = tables[table]
        entry["module"] = module
        entry["file"] = qb_file.replace(repo_root + "/", "")
        for col in cols:
            if col not in entry["columns_set"]:
                entry["columns_set"].add(col)
                entry["columns"].append(col)

lines = [
    "# GB5 Schema Registry",
    f"Source: INSERT INTO patterns in *QB.cs files  |  Generated: {__import__('datetime').datetime.now().strftime('%Y-%m-%d %H:%M')}",
    "Re-run: `bash tools/extract-schema.sh`",
    "",
    "---",
    "",
]

for table in sorted(tables.keys()):
    entry = tables[table]
    lines.append(f"## {table}")
    lines.append(f"**Module:** {entry['module']}  |  **Source:** `{entry['file']}`")
    lines.append("")
    lines.append("**Columns (from INSERT INTO):**")
    for col in entry["columns"]:
        lines.append(f"- `{col}`")
    lines.append("")
    lines.append("---")
    lines.append("")

with open(output_path, "w") as f:
    f.write("\n".join(lines))

print(f"Registry written to: {output_path}")
print(f"Tables captured: {len(tables)}")
PYEOF