"""
Migration F9 - tablas fotos_obra + daily_log.

Ejecutar:
    cd 02_HERRAMIENTAS/db
    python migrate_f9.py

Idempotente: usa CREATE TABLE IF NOT EXISTS. No toca data existente.
No corre init_db.sql completo (eso resetearia obras/rubros con seeds existentes).
"""
from __future__ import annotations

import os
import sqlite3
from pathlib import Path

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

DDL = [
    """
    CREATE TABLE IF NOT EXISTS fotos_obra (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      obra_id         INTEGER NOT NULL REFERENCES obras(id) ON DELETE CASCADE,
      fecha           TEXT NOT NULL,
      sector          TEXT,
      piso            TEXT,
      nota            TEXT,
      blob_path       TEXT NOT NULL,
      exif_gps_lat    REAL,
      exif_gps_lon    REAL,
      exif_fecha      TEXT,
      usuario         TEXT,
      mime_type       TEXT NOT NULL DEFAULT 'image/jpeg',
      size_bytes      INTEGER NOT NULL DEFAULT 0 CHECK (size_bytes >= 0),
      created_at      TEXT NOT NULL DEFAULT (datetime('now'))
    )
    """,
    """
    CREATE TABLE IF NOT EXISTS daily_log (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      obra_id         INTEGER NOT NULL REFERENCES obras(id) ON DELETE CASCADE,
      fecha           TEXT NOT NULL,
      autor           TEXT,
      actividades     TEXT,
      observaciones   TEXT,
      clima           TEXT,
      decisiones_key  TEXT,
      created_at      TEXT NOT NULL DEFAULT (datetime('now'))
    )
    """,
    "CREATE INDEX IF NOT EXISTS idx_fotos_obra_fecha ON fotos_obra (obra_id, fecha)",
    "CREATE INDEX IF NOT EXISTS idx_daily_log_fecha  ON daily_log (obra_id, fecha)",
]


def main() -> None:
    if not DB_PATH.exists():
        raise SystemExit(f"ergon.db no encontrada en {DB_PATH}. Corre init_db.py primero.")

    os.makedirs(BLOBS_DIR, exist_ok=True)
    print(f"  [migrate_f9] blobs dir: {BLOBS_DIR}")

    conn = sqlite3.connect(str(DB_PATH))
    conn.execute("PRAGMA foreign_keys = ON")
    try:
        for stmt in DDL:
            conn.execute(stmt)
        conn.commit()

        rows = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table' "
            "AND name IN ('fotos_obra','daily_log') ORDER BY name"
        ).fetchall()
        print(f"  [migrate_f9] tablas presentes: {[r[0] for r in rows]}")

        cf = conn.execute("SELECT COUNT(*) FROM fotos_obra").fetchone()[0]
        cd = conn.execute("SELECT COUNT(*) FROM daily_log").fetchone()[0]
        print(f"  [migrate_f9] filas: fotos_obra={cf}, daily_log={cd}")
        print("  [migrate_f9] OK")
    finally:
        conn.close()


if __name__ == "__main__":
    main()