"""Smoke test de ergon.db tras init + seed."""
import sqlite3
import sys
from pathlib import Path

if sys.stdout.encoding and sys.stdout.encoding.lower() != "utf-8":
    sys.stdout.reconfigure(encoding="utf-8")

DB_PATH = Path(__file__).parent / "ergon.db"
conn = sqlite3.connect(str(DB_PATH))
conn.row_factory = sqlite3.Row
cur = conn.cursor()

def show(label, rows, cols=None):
    print(f"\n-- {label} --")
    if not rows:
        print("  (sin filas)")
        return
    if isinstance(rows[0], sqlite3.Row):
        keys = rows[0].keys()
    else:
        keys = cols or []
    if keys:
        print("  " + " | ".join(f"{k:>20}" for k in keys))
    for r in rows:
        vals = [r[k] if isinstance(r, sqlite3.Row) else r[i] for i, k in enumerate(keys or range(len(r)))]
        fmt = [f"{str(v)[:20]:>20}" for v in vals]
        print("  " + " | ".join(fmt))

# 1. Listar obras
show("OBRAS", cur.execute("""
    SELECT codigo, nombre, cliente, moneda, presupuesto_total,
           dg_nivel_servicio, es_demo
    FROM obras ORDER BY codigo
""").fetchall())

# 2. Conteo por tabla
print("\n-- CONTEO POR TABLA --")
for t in ["obras","rubros_obra","subcontratistas","asistencia_diaria",
          "avance_mensual","presupuesto_mensual","contratos","materiales","cortes"]:
    n = cur.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
    print(f"  {t:25s} {n}")

# 3. Suma de pesos rubros (debe dar 100)
show("SUMA PESOS RUBROS (debe = 100)", cur.execute("""
    SELECT o.codigo, ROUND(SUM(r.peso_pct), 6) AS suma_pesos, COUNT(*) AS n_rubros
    FROM rubros_obra r JOIN obras o ON o.id = r.obra_id
    GROUP BY o.codigo
""").fetchall())

# 4. Avance ultimo mes observado
show("AVANCE MES CORTE - 5 RUBROS DESTACADOS", cur.execute("""
    SELECT r.orden, r.nombre, a.mes, a.plan_acum_pct, a.real_acum_pct,
           ROUND(a.plan_acum_pct - a.real_acum_pct, 2) AS desvio_pp
    FROM avance_mensual a
    JOIN rubros_obra r ON r.id = a.rubro_id
    JOIN obras o ON o.id = a.obra_id
    WHERE o.codigo = 'TDG1'
      AND a.mes = '2025-01'
      AND a.real_acum_pct IS NOT NULL
    ORDER BY ABS(a.plan_acum_pct - a.real_acum_pct) DESC
    LIMIT 5
""").fetchall())

# 5. Contratos con subcontratista vinculado (verifica Bug 3 fix)
show("CONTRATOS VINCULADOS A SUBCONTRATISTA", cur.execute("""
    SELECT c.tipo, c.estado, s.nombre AS subcontratista
    FROM contratos c
    LEFT JOIN subcontratistas s ON s.id = c.contratista_id
    JOIN obras o ON o.id = c.obra_id
    WHERE o.codigo = 'TDG1'
    ORDER BY c.tipo
""").fetchall())

# 6. EVM agregado: plan total vs real total en corte 2025-01
show("EVM CORTE 2025-01 (Gs total, PV vs EV)", cur.execute("""
    SELECT
        ROUND(SUM(CASE WHEN p.mes <= '2025-01' THEN p.plan ELSE 0 END), 0) AS plan_total,
        ROUND(SUM(CASE WHEN p.mes <= '2025-01' THEN p.real ELSE 0 END), 0) AS real_total,
        ROUND(SUM(CASE WHEN p.mes <= '2025-01' THEN p.plan ELSE 0 END)
            - SUM(CASE WHEN p.mes <= '2025-01' THEN p.real ELSE 0 END), 0) AS diferencia
    FROM presupuesto_mensual p
    JOIN obras o ON o.id = p.obra_id
    WHERE o.codigo = 'TDG1'
""").fetchall())

# 7. Asistencia promedio y dias perdidos
show("ASISTENCIA AGREGADA ULT 60 DIAS", cur.execute("""
    SELECT
        ROUND(AVG(total_personal), 1) AS promedio,
        SUM(dia_perdido) AS dias_perdidos,
        ROUND(SUM(lluvia_mm), 1) AS lluvia_total_mm,
        COUNT(*) AS n_dias
    FROM asistencia_diaria a
    JOIN obras o ON o.id = a.obra_id
    WHERE o.codigo = 'TDG1'
""").fetchall())

# 8. Test CHECK constraints - intento insertar codigo invalido
print("\n-- TEST CHECK CONSTRAINTS (deben fallar) --")
for bad_codigo in ["ab", "abcdefg", "TDG 1", "tdg1"]:
    try:
        cur.execute("""INSERT INTO obras
            (codigo, nombre, cliente, presupuesto_total,
             fecha_inicio, fecha_fin_plan, fecha_corte_actual,
             dg_nivel_servicio)
            VALUES (?, 'x', 'y', 1, '2026-01-01', '2026-06-01', '2026-03-01', 2)""",
            (bad_codigo,))
        print(f"  [FAIL] {bad_codigo!r} fue aceptado (no deberia)")
        conn.rollback()
    except sqlite3.IntegrityError as e:
        print(f"  [OK] {bad_codigo!r} rechazado: {str(e)[:60]}")

conn.close()
print("\nsmoke test OK.")