"""
Write docs/DATABASE_REFERENCE.md from the live database schema.

Lists every table by business area (from app/models/table_catalog.py) with its
description, the model file that defines it, its columns, keys and references.
Re-run after a migration so the reference stays accurate:

    python scripts/generate_db_reference.py
"""
import asyncio
import os
import sys
from collections import defaultdict

sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy import text
from app.core.database import engine, Base
from app.models.table_catalog import TABLE_CATALOG
import app.models  # noqa: F401  (registers every model)

OUTPUT = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'docs', 'DATABASE_REFERENCE.md'))

COLUMNS_SQL = """
SELECT c.table_name, c.column_name, c.data_type, c.character_maximum_length,
       c.is_nullable, c.column_default, c.ordinal_position
FROM information_schema.columns c
WHERE c.table_schema = current_schema()
ORDER BY c.table_name, c.ordinal_position
"""

KEYS_SQL = """
SELECT tc.table_name, kcu.column_name, tc.constraint_type,
       ccu.table_name AS ref_table, ccu.column_name AS ref_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema
LEFT JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name AND tc.constraint_type = 'FOREIGN KEY'
WHERE tc.table_schema = current_schema()
  AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')
"""


def _type(row) -> str:
    t = row.data_type
    t = {'character varying': 'varchar', 'timestamp with time zone': 'timestamptz',
         'timestamp without time zone': 'timestamp', 'double precision': 'float8',
         'USER-DEFINED': 'enum'}.get(t, t)
    if row.character_maximum_length:
        t += f'({row.character_maximum_length})'
    return t


def _model_files() -> dict[str, str]:
    files = {}
    for table in Base.metadata.sorted_tables:
        mapper_cls = next((m.class_ for m in Base.registry.mappers if m.local_table is table), None)
        if mapper_cls is not None:
            files[table.name] = mapper_cls.__module__.replace('app.models.', '') + '.py'
    return files


async def main() -> None:
    async with engine.connect() as conn:
        columns = (await conn.execute(text(COLUMNS_SQL))).all()
        keys = (await conn.execute(text(KEYS_SQL))).all()

    by_table = defaultdict(list)
    for row in columns:
        by_table[row.table_name].append(row)
    pk, unique, fk = defaultdict(set), defaultdict(set), defaultdict(dict)
    for k in keys:
        if k.constraint_type == 'PRIMARY KEY':
            pk[k.table_name].add(k.column_name)
        elif k.constraint_type == 'UNIQUE':
            unique[k.table_name].add(k.column_name)
        elif k.ref_table:
            fk[k.table_name][k.column_name] = f'{k.ref_table}.{k.ref_column}'
    model_files = _model_files()

    lines = [
        '# Database reference',
        '',
        'Generated from the live schema by `backend/scripts/generate_db_reference.py`. '
        'Do not edit by hand; re-run the script after a migration.',
        '',
        'Key: **PK** primary key, **U** unique, **→** references (foreign key).',
        '',
        '## Contents',
        '',
    ]
    documented = set()
    for area, tables in TABLE_CATALOG.items():
        anchor = area.lower().replace(' ', '-').replace(',', '').replace('(', '').replace(')', '')
        lines.append(f'- [{area}](#{anchor}) ({len(tables)} tables)')
    lines.append('')

    for area, tables in TABLE_CATALOG.items():
        lines += [f'## {area}', '']
        for table, description in tables.items():
            documented.add(table)
            if table not in by_table:
                continue
            lines += [
                f'### `{table}`',
                '',
                description,
                '',
                f'Model: `app/models/{model_files.get(table, "-")}`',
                '',
                '| Column | Type | Null | Key / reference |',
                '|---|---|---|---|',
            ]
            for col in by_table[table]:
                marks = []
                if col.column_name in pk[table]:
                    marks.append('PK')
                if col.column_name in unique[table]:
                    marks.append('U')
                if col.column_name in fk[table]:
                    marks.append(f'→ `{fk[table][col.column_name]}`')
                lines.append(
                    f'| `{col.column_name}` | {_type(col)} | {"yes" if col.is_nullable == "YES" else "no"} | {" ".join(marks)} |'
                )
            lines.append('')

    others = sorted(set(by_table) - documented - {'alembic_version'})
    if others:
        lines += ['## Tables not yet described in table_catalog.py', '']
        lines += [f'- `{t}`' for t in others]
        lines.append('')

    os.makedirs(os.path.dirname(OUTPUT), exist_ok=True)
    with open(OUTPUT, 'w', encoding='utf-8') as f:
        f.write('\n'.join(lines))
    print(f'Wrote {OUTPUT} ({len(by_table)} tables)')


if __name__ == '__main__':
    asyncio.run(main())