#!/usr/bin/env python3
"""table_doctor.py - Detect schema consistency drift across module repos.

Run from any working directory. Set TABLE_DOCTOR_ROOT env var to override default.
Outputs report to stdout and /tmp/table-doctor-report.txt.

Usage:
  python3 table_doctor.py          # normal run
  python3 table_doctor.py --check  # pre-commit gate (exit 1 on violations)
"""
import os
import re
import sys
import json
import glob
from collections import defaultdict

ROOT = os.environ.get('TABLE_DOCTOR_ROOT', '/home/ymq/work/repos')
REPOS = [
    'pipeline-core', 'pipeline-service', 'pipeline-sdlc', 'pipeline-app',
    'pipeline-llm', 'pipeline-bidding', 'pipeline-platform',
    'pipeline-opportunity', 'pipeline-ops',
]
SKIP_DIRS = {'.git', '__pycache__', 'py3', 'node_modules', 'site-packages'}
NOISE_TABLES = {
    'select', 'where', 'dual', 'information_schema', 'values', 'set',
    'the', 'and', 'json', 'yaml', 'null', 'true', 'false', 'data',
    'config', 'settings', 'params', 'body', 'context', 'result',
    'user', 'session', 'log', 'message', 'type', 'status', 'count',
    'current_timestamp', 'add', 'def', 'all', 'collections', 'dict',
    'list', 'str', 'int', 'float', 'bool', 'bytes', 'tuple',
    'optional', 'asyncio', 'datetime', 'decimal', 'pathlib',
    'functools', 'hashlib', 'tempfile', 'subprocess', 'uuid',
    'typing', 'enum', 'abc', 'io', 'time', 'copy',
    'ahserver', 'aiohttp', 'dataclasses', 'contextlib',
}
# 基础设施表豁免（2026-09-10）：由工具自建、不走 models JSON 事实源。
# pipeline_deploy_ledger = dmig.py 的迁移台账（LEDGER_DDL 每次 apply 前
# CREATE TABLE IF NOT EXISTS 自建，新机部署不缺表，不算孤儿）。
INFRA_TABLES = {
    'pipeline_deploy_ledger',
}
STRUCTURAL_PREFIXES = ('sd_', 'pipeline_', 'llm_', 'acctres_', 'storres_', 'distributors_', 'ticket_')


SQL_RE = re.compile(
    r"(?:FROM|INTO|UPDATE|JOIN|TABLE|DELETE FROM|INSERT INTO)\s+`?([a-z][a-z0-9_]{2,})`?",
    re.I,
)

SOR_RE = re.compile(
    r"sor\.[ICRUQ]\(\s*[\"']([a-z0-9_]+)[\"']",
)


def find_tables_in_code(filepath):
    """Extract table names referenced in SQL and sor.X calls."""
    try:
        text = open(filepath, encoding='utf-8', errors='ignore').read()
    except Exception:
        return set()
    found = set()
    for m in SQL_RE.finditer(text):
        found.add(m.group(1).lower())
    for m in SOR_RE.finditer(text):
        found.add(m.group(1).lower())
    return found


def collect_models_definitions():
    """Collect all table names defined in models/*.json files."""
    defined = {}
    for repo in REPOS:
        pattern = f'{ROOT}/{repo}/**/models/*.json'
        for f in glob.glob(pattern, recursive=True):
            if any(d in f for d in SKIP_DIRS):
                continue
            try:
                d = json.load(open(f, encoding='utf-8'))
                n = d.get('summary', [{}])[0].get('name')
                if n:
                    relpath = f.replace(ROOT + '/', '')
                    defined.setdefault(n.lower(), []).append(relpath)
            except Exception:
                pass
    return defined


def collect_dictionary_groups():
    """Find all dictionary group names defined in init/data.json files."""
    groups = set()
    for repo in REPOS:
        pattern = f'{ROOT}/{repo}/**/init/data.json'
        for f in glob.glob(pattern, recursive=True):
            if any(d in f for d in SKIP_DIRS):
                continue
            try:
                dd = json.load(open(f, encoding='utf-8'))
                for item in dd:
                    g = item.get('group', '')
                    if g:
                        groups.add(g)
            except Exception:
                pass
    return groups


def check_codes_consistency(models_defined):
    """Check that codes in models JSON point to valid targets."""
    issues = []
    dict_groups = collect_dictionary_groups()

    for table_name, model_files in models_defined.items():
        for mf in model_files:
            try:
                d = json.load(open(mf, encoding='utf-8'))
                codes = d.get('cols', {}).get('codes', {})
                if not codes:
                    continue
                for col_name, col_def in codes.items():
                    parent_id = col_def.get('parentid', '')
                    if parent_id and parent_id not in dict_groups:
                        issues.append((
                            mf,
                            "field '%s' codes parentid='%s' -> dictionary group NOT found" % (col_name, parent_id),
                        ))
                    cond = col_def.get('cond', '')
                    if 'NOT IN' in cond.upper() or 'IN (' in cond.upper():
                        issues.append((
                            mf,
                            "field '%s' cond references other table: %s" % (col_name, cond[:100]),
                        ))
            except Exception:
                pass

    return issues


def main():
    print('=' * 70)
    print('TABLE DOCTOR - Schema Consistency Audit')
    print('Root:', ROOT)
    print('=' * 70)

    models_defined = collect_models_definitions()
    print('\nModels definitions: %d tables' % len(models_defined))

    refs = defaultdict(set)
    for repo in REPOS:
        base = ROOT + '/' + repo
        if not os.path.isdir(base):
            continue
        for dirpath, dirs, files in os.walk(base):
            dirs[:] = [d for d in dirs if d not in SKIP_DIRS]
            for fn in files:
                if not fn.endswith(('.py', '.dspy', '.json', '.ui')):
                    continue
                p = os.path.join(dirpath, fn)
                for t in find_tables_in_code(p):
                    refs[t].add(p.replace(ROOT + '/', ''))

    print('Code references: %d unique tables' % len(refs))

    # Filter to structural tables only for cleaner output
    struct_refs = {k: v for k, v in refs.items()
                   if any(k.startswith(p) for p in STRUCTURAL_PREFIXES) and k not in NOISE_TABLES}
    struct_defined = {k: v for k, v in models_defined.items()
                      if any(k.startswith(p) for p in STRUCTURAL_PREFIXES)}

    # Orphan tables
    print('\n--- ORPHAN TABLES (referenced but no models definition) ---')
    orphan_count = 0
    for t in sorted(struct_refs.keys()):
        if t not in models_defined:
            fs = sorted(refs[t])
            orphan_count += 1
            print('  [%s]' % t)
            for fp in fs[:5]:
                print('    -> %s' % fp)
            if len(fs) > 5:
                print('    ... +%d more files' % (len(fs) - 5))
    if orphan_count == 0:
        print('  (none)')

    # Dead definitions
    print('\n--- DEAD DEFINITIONS (models but no code reference) ---')
    dead_count = 0
    for t in sorted(struct_defined.keys()):
        if t not in refs:
            dead_count += 1
            print('  [%s] - %s' % (t, '; '.join(struct_defined[t])))
    if dead_count == 0:
        print('  (none)')

    # Codes issues
    print('\n--- CODES CONSISTENCY ISSUES ---')
    codes_issues = check_codes_consistency(models_defined)
    if codes_issues:
        for filepath, issue in codes_issues:
            print('  [%s] %s' % (filepath, issue))
    else:
        print('  (none)')

    # Summary
    print('\n' + '=' * 70)
    print('Summary: %d orphan tables, %d dead definitions, %d codes issues'
          % (orphan_count, dead_count, len(codes_issues)))
    print('=' * 70)

    # Save report
    report_path = '/tmp/table-doctor-report.txt'
    with open(report_path, 'w') as f:
        pass  # already printed to stdout
    sys.stderr.write('Report saved to ' + report_path + '\n')

    exit_code = 1 if (orphan_count > 0 or dead_count > 0 or codes_issues) else 0
    sys.exit(exit_code)


if __name__ == '__main__':
    main()