"""
ERGON F8 migration - Tabla `documentos` + indices.

Schema del prompt Sesion F §4.2:
    documentos (id, obra_id FK, categoria enum(8), titulo, tags CSV,
                blob_path, mime_type, size_bytes, sha256,
                fecha_doc, subido_por, notas, created_at)
    idx_docs_obra_cat (obra_id, categoria)
    idx_docs_sha      (sha256)

Idempotente: CREATE TABLE IF NOT EXISTS + chequeo por pragma. Se puede re-correr
multiples veces sin efectos secundarios.

Uso:
    python db/migrate_f8.py
"""
from __future__ import annotations

import os
import sqlite3
import sys
from pathlib import Path

HERE = Path(__file__).resolve().parent
DB_PATH = Path(os.environ.get("DATABASE_PATH") or (HERE / "ergon.db"))

MIGRATION_SQL = """
CREATE TABLE IF NOT EXISTS documentos (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  obra_id         INTEGER NOT NULL REFERENCES obras(id) ON DELETE CASCADE,
  categoria       TEXT NOT NULL CHECK (categoria IN
                    ('plano','contrato','certificado','acta',
                     'orden_pago','informe','foto','garantia')),
  titulo          TEXT NOT NULL,
  tags            TEXT,
  blob_path       TEXT NOT NULL,
  mime_type       TEXT NOT NULL,
  size_bytes      INTEGER NOT NULL CHECK (size_bytes >= 0),
  sha256          TEXT NOT NULL,
  fecha_doc       TEXT,
  subido_por      TEXT,
  notas           TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_docs_obra_cat ON documentos (obra_id, categoria);
CREATE INDEX IF NOT EXISTS idx_docs_sha      ON documentos (sha256);
"""


def main() -> int:
    if not DB_PATH.exists():
        print(f"[ERROR] DB no existe: {DB_PATH}", file=sys.stderr)
        print("Corra db/init_db.py primero.", file=sys.stderr)
        return 1
    conn = sqlite3.connect(str(DB_PATH))
    try:
        conn.executescript(MIGRATION_SQL)
        conn.commit()
        # Verificacion post-migration
        cur = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='documentos'"
        )
        if not cur.fetchone():
            print("[ERROR] tabla documentos no se creo", file=sys.stderr)
            return 1
        cnt = conn.execute("SELECT COUNT(*) FROM documentos").fetchone()[0]
        idx_rows = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='documentos'"
        ).fetchall()
        idx_names = sorted(r[0] for r in idx_rows if not r[0].startswith("sqlite_"))
        print(f"  [migrate_f8] OK tabla documentos ({cnt} filas). "
              f"Indices: {', '.join(idx_names)}")
        return 0
    except sqlite3.Error as e:
        print(f"[ERROR] migration fallo: {e}", file=sys.stderr)
        return 1
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())