"""
ERGON - Seed minimo VITR1 para test multi-obra (F6).

Obra real (es_demo=0) con 4 rubros y 3 meses de data suficiente para que
/api/obras/VITR1/evm devuelva numeros validos y el dashboard render completo.

Idempotente: reusa VITR1 si ya existe (borra data dependiente y regenera).

Uso:
    python seed_vitrium_test.py
"""
from __future__ import annotations
import sqlite3
import sys
from pathlib import Path

HERE = Path(__file__).parent
DB_PATH = HERE / "ergon.db"
if sys.stdout.encoding and sys.stdout.encoding.lower() != "utf-8":
    sys.stdout.reconfigure(encoding="utf-8")

RUBROS = [
    ("Demolicion",      10.0),
    ("Estructura",      50.0),
    ("Albanileria",     25.0),
    ("Instalaciones",   15.0),
]

# avance_mensual: (mes, plan_acum_pct, real_acum_pct) por rubro.
# Coherencia sintetica: avance real muy cerca del plan, pequeno desvio positivo.
AVANCE = {
    "Demolicion":    [("2026-02", 40.0, 45.0), ("2026-03", 80.0, 85.0), ("2026-04", 100.0, 100.0)],
    "Estructura":    [("2026-02",  5.0,  4.0), ("2026-03", 15.0, 12.0), ("2026-04", 25.0, 22.0)],
    "Albanileria":   [("2026-02",  0.0,  0.0), ("2026-03",  2.0,  1.0), ("2026-04",  8.0,  6.0)],
    "Instalaciones": [("2026-02",  0.0,  0.0), ("2026-03",  1.0,  0.0), ("2026-04",  4.0,  3.0)],
}

# presupuesto_mensual: plan/real en PYG coherentes con BAC y avance.
BAC = 28_000_000_000.0

def pyg_plan(peso_pct, plan_acum_pct, plan_acum_pct_prev):
    """Delta del mes para el rubro, basado en plan_acum_pct."""
    delta_pct = plan_acum_pct - plan_acum_pct_prev
    return BAC * (peso_pct / 100.0) * (delta_pct / 100.0)


def main() -> int:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")

    existing = conn.execute("SELECT id, es_demo FROM obras WHERE codigo = 'VITR1'").fetchone()
    if existing and existing["es_demo"]:
        print("ERROR: VITR1 existe como demo. Abortando (no se toca data demo).", file=sys.stderr)
        return 1

    if existing:
        print(f"VITR1 existe (id={existing['id']}). Borrando data dependiente y regenerando.")
        conn.execute("DELETE FROM avance_mensual WHERE obra_id = ?", (existing["id"],))
        conn.execute("DELETE FROM presupuesto_mensual WHERE obra_id = ?", (existing["id"],))
        conn.execute("DELETE FROM rubros_obra WHERE obra_id = ?", (existing["id"],))
        conn.execute("DELETE FROM obras WHERE id = ?", (existing["id"],))

    # Insert obra
    conn.execute("""
        INSERT INTO obras (codigo, nombre, cliente, ubicacion, tipo, moneda,
                           presupuesto_total, fecha_inicio, fecha_fin_plan,
                           fecha_corte_actual, umbral_desvio_pct, umbral_critico_pct,
                           dg_nivel_servicio, dg_responsable, es_demo)
        VALUES ('VITR1', 'Vitrium Torre Boggiani', 'Vitrium SA', 'Asuncion, Paraguay',
                'Torre residencial', 'PYG', ?, '2026-02-01', '2028-06-30',
                '2026-04-30', 10.0, 20.0, 2, 'Victor Del Puerto', 0)
    """, (BAC,))
    obra_id = conn.execute("SELECT id FROM obras WHERE codigo = 'VITR1'").fetchone()["id"]
    print(f"Obra VITR1 creada (id={obra_id}, BAC=Gs. {BAC/1e9:.1f}B)")

    # Insert rubros
    rubro_ids = {}
    for i, (nombre, peso) in enumerate(RUBROS, start=1):
        conn.execute("""INSERT INTO rubros_obra (obra_id, orden, nombre, peso_pct)
                        VALUES (?, ?, ?, ?)""", (obra_id, i, nombre, peso))
        rubro_ids[nombre] = conn.execute(
            "SELECT id FROM rubros_obra WHERE obra_id = ? AND nombre = ?",
            (obra_id, nombre)
        ).fetchone()["id"]
    print(f"  {len(RUBROS)} rubros cargados (sum pesos = {sum(p for _,p in RUBROS):.1f}%)")

    # Insert avance + presupuesto
    n_av = 0
    n_pr = 0
    for nombre, serie in AVANCE.items():
        rid = rubro_ids[nombre]
        peso = next(p for n, p in RUBROS if n == nombre)
        plan_prev = 0.0
        real_prev = 0.0
        for mes, plan_pct, real_pct in serie:
            conn.execute("""INSERT INTO avance_mensual (obra_id, rubro_id, mes,
                                                        plan_acum_pct, real_acum_pct)
                            VALUES (?, ?, ?, ?, ?)""",
                         (obra_id, rid, mes, plan_pct, real_pct))
            n_av += 1
            plan_delta = BAC * (peso / 100.0) * ((plan_pct - plan_prev) / 100.0)
            real_delta = BAC * (peso / 100.0) * ((real_pct - real_prev) / 100.0)
            conn.execute("""INSERT INTO presupuesto_mensual (obra_id, rubro_id, mes, plan, real)
                            VALUES (?, ?, ?, ?, ?)""",
                         (obra_id, rid, mes, plan_delta, real_delta))
            n_pr += 1
            plan_prev = plan_pct
            real_prev = real_pct
    print(f"  {n_av} filas avance_mensual + {n_pr} filas presupuesto_mensual")

    conn.commit()

    # Verificacion
    total_plan_pyg = conn.execute("""SELECT ROUND(SUM(plan), 0) AS s
                                     FROM presupuesto_mensual WHERE obra_id = ?""",
                                  (obra_id,)).fetchone()["s"]
    total_real_pyg = conn.execute("""SELECT ROUND(SUM(real), 0) AS s
                                     FROM presupuesto_mensual WHERE obra_id = ?""",
                                  (obra_id,)).fetchone()["s"]
    print(f"\nVerificacion flujo al corte:")
    print(f"  sum(plan) = Gs. {total_plan_pyg/1e6:,.0f}M")
    print(f"  sum(real) = Gs. {total_real_pyg/1e6:,.0f}M")
    print("\nVITR1 listo. Reload dashboard para ver en selector.")
    conn.close()
    return 0


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