"""Read schema text only. Never connects to or imports a production database."""
import argparse
import hashlib
import json
import re
import sqlite3
from pathlib import Path

parser = argparse.ArgumentParser()
parser.add_argument('schema', type=Path)
parser.add_argument('--fixtures', action='store_true')
args = parser.parse_args()
root = Path(__file__).resolve().parents[1]
sql = args.schema.read_text(encoding='utf-8-sig')
if re.search(r'^\s*(INSERT|REPLACE)\s', sql, re.M | re.I):
    raise SystemExit('Expected schema-only input; refusing a data dump.')
tables = {}
for table, body, suffix in re.findall(r'^CREATE TABLE `([^`]+)` \(\r?\n(.*?)^\)([^\r\n]*);', sql, re.S | re.M):
    if not suffix.lstrip().startswith('ENGINE='):
        continue  # phpMyAdmin view placeholders are not base tables.
    tables[table] = {name: definition for name, definition in re.findall(r'^  `([^`]+)` (.*?)(?:,)?$', body, re.M)}
foreign_keys = []
primary = {}
for table, body in re.findall(r'ALTER TABLE `([^`]+)`(.*?);', sql, re.S):
    for column, parent, parent_column in re.findall(r'FOREIGN KEY \(`([^`]+)`\) REFERENCES `([^`]+)` \(`([^`]+)`\)', body):
        foreign_keys.append({'table': table, 'column': column, 'parent': parent, 'parent_column': parent_column})
    found = re.search(r'ADD PRIMARY KEY \(([^)]+)\)', body)
    if found:
        primary[table] = re.findall(r'`([^`]+)`', found[1])
direct = {'business'} | {name for name, columns in tables.items() if 'business_id' in columns}
reachable = set(direct)
while True:
    more = reachable | {f['table'] for f in foreign_keys if f['parent'] in reachable}
    if more == reachable:
        break
    reachable = more
report = {
    'schema_sha256': hashlib.sha256(args.schema.read_bytes()).hexdigest(),
    'tables': {name: {'columns': list(columns), 'primary': primary.get(name, []),
                      'classification': 'direct_candidate' if name in direct else 'relationship_candidate' if name in reachable else 'requires_review'}
               for name, columns in tables.items()},
    'foreign_keys': foreign_keys,
    'unreviewed': sorted(set(tables) - reachable),
    'production_export_allowed': False,
    'note': 'Reachability is a candidate only: shared, polymorphic, cross-tenant references and files require explicit review.'
}
output = root / 'docs/platform/schema-inventory.json'
output.write_text(json.dumps(report, indent=2), encoding='utf-8')
if args.fixtures:
    target = root / 'runtime/platform-fixture.sqlite'
    target.parent.mkdir(exist_ok=True)
    if target.exists():
        raise SystemExit('Fixture already exists; refusing to replace it.')
    db = sqlite3.connect(target)
    for name, columns in tables.items():
        definitions = []
        for column, definition in columns.items():
            if primary.get(name) == [column] and re.match(r'(?:big|small|tiny|medium)?int\b', definition):
                definitions.append(f'"{column}" INTEGER PRIMARY KEY AUTOINCREMENT')
            else:
                kind = 'INTEGER' if re.match(r'(?:big|small|tiny|medium)?int\b', definition) else 'REAL' if re.match(r'(?:decimal|float|double)\b', definition) else 'TEXT'
                default = re.search(r"DEFAULT ('(?:[^']|'')*'|NULL|[-0-9.]+)", definition)
                clause = f' DEFAULT {default[1]}' if default else ''
                definitions.append(f'"{column}" {kind}{clause}')
        db.execute(f'CREATE TABLE "{name}" ({", ".join(definitions)})')
    db.commit()
    db.close()
print(json.dumps({'tables': len(tables), 'foreign_keys': len(foreign_keys), 'unreviewed_tables': len(report['unreviewed']), 'production_export_allowed': False}))