-- ═══════════════════════════════════════════════════════════
-- TABLA PRINCIPAL: cargas_historial (ya existente)
-- ═══════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS cargas_historial (
  id                      BIGSERIAL     PRIMARY KEY,
  fecha_carga             TIMESTAMPTZ   DEFAULT NOW(),
  tipo_carga              TEXT          DEFAULT 'completo',
  nombre_archivo          TEXT,
  periodo                 TEXT,         -- 'YYYY-MM' del último mes de los datos
  total_cuotas            INTEGER       DEFAULT 0,
  -- Matrículas
  matriculados_vigentes   INTEGER       DEFAULT 0,
  total_retiros           INTEGER       DEFAULT 0,
  nuevas_matriculas_anio  INTEGER       DEFAULT 0,
  total_becados           INTEGER       DEFAULT 0,
  -- Cuotas
  monto_proyectado        NUMERIC(15,2) DEFAULT 0,
  monto_pagado            NUMERIC(15,2) DEFAULT 0,
  monto_moroso            NUMERIC(15,2) DEFAULT 0,
  total_intereses         NUMERIC(15,2) DEFAULT 0,
  -- JSONB
  vacantes_por_curso      JSONB         DEFAULT '{}',
  matriculas_por_curso    JSONB         DEFAULT '{}',
  retiros_por_mes         JSONB         DEFAULT '{}',
  matriculas_por_mes      JSONB         DEFAULT '{}',
  resumen_completo        JSONB         DEFAULT '{}',
  created_at              TIMESTAMPTZ   DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════
-- NUEVA TABLA: kpi_historico (para tendencias)
-- Ejecuta esto ADICIONAL si ya tienes cargas_historial
-- ═══════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS kpi_historico (
  id                  BIGSERIAL     PRIMARY KEY,

  -- Período
  fecha_registro      TIMESTAMPTZ   DEFAULT NOW(),
  periodo             TEXT          NOT NULL,   -- 'YYYY-MM' ej: '2026-03'
  tipo_periodo        TEXT          DEFAULT 'mensual',  -- mensual / cierre

  -- 1. Morosidad (el rey)
  monto_moroso        NUMERIC(15,2) DEFAULT 0,
  pct_morosidad       NUMERIC(6,2)  DEFAULT 0,  -- %

  -- 2. Alumnos
  alumnos_vigentes    INTEGER       DEFAULT 0,
  alumnos_morosos     INTEGER       DEFAULT 0,
  alumnos_retirados   INTEGER       DEFAULT 0,

  -- 3. Becados
  alumnos_becados     INTEGER       DEFAULT 0,
  pct_becados         NUMERIC(6,2)  DEFAULT 0,
  deuda_con_beca      NUMERIC(15,2) DEFAULT 0,

  -- 4. Proyección
  monto_proyectado    NUMERIC(15,2) DEFAULT 0,
  monto_pagado        NUMERIC(15,2) DEFAULT 0,
  pct_recaudacion     NUMERIC(6,2)  DEFAULT 0,  -- pagado/proyectado %

  -- 5. Top 3 cursos morosos (JSONB: [{curso, deuda}])
  top3_cursos_morosos JSONB         DEFAULT '[]',

  -- 6. Variación vs período anterior
  variacion_monto     NUMERIC(15,2) DEFAULT 0,  -- moroso actual - anterior
  variacion_pct       NUMERIC(6,2)  DEFAULT 0,  -- variación en %

  -- Metadata
  nombre_colegio      TEXT,
  dataset_hash        TEXT,         -- hash para detectar si el CSV cambió
  created_at          TIMESTAMPTZ   DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_kpih_periodo ON kpi_historico(periodo DESC);
CREATE INDEX IF NOT EXISTS idx_kpih_fecha   ON kpi_historico(fecha_registro DESC);

-- Vista para tendencias fáciles de consultar
CREATE OR REPLACE VIEW v_tendencia_morosidad AS
SELECT
  periodo,
  fecha_registro,
  alumnos_vigentes,
  alumnos_morosos,
  monto_moroso,
  monto_pagado,
  pct_morosidad,
  monto_proyectado,
  pct_recaudacion,
  variacion_monto,
  variacion_pct,
  top3_cursos_morosos
FROM kpi_historico
ORDER BY periodo DESC;

-- ═══════════════════════════════════════════════════════════
-- MIGRACIÓN (si ya tenías las tablas, ejecuta esto):
-- ═══════════════════════════════════════════════════════════
ALTER TABLE cargas_historial ADD COLUMN IF NOT EXISTS periodo TEXT;
ALTER TABLE cargas_historial ADD COLUMN IF NOT EXISTS total_cuotas INTEGER DEFAULT 0;
ALTER TABLE cargas_historial ADD COLUMN IF NOT EXISTS monto_pagado NUMERIC(15,2) DEFAULT 0;
-- Nuevas columnas para reconstruir dashboard sin CSV:
ALTER TABLE cargas_historial ADD COLUMN IF NOT EXISTS detalle_alumnos JSONB DEFAULT '[]';
ALTER TABLE cargas_historial ADD COLUMN IF NOT EXISTS meses_detalle JSONB DEFAULT '{}';

SELECT 'Tablas listas ✓' AS resultado;

CREATE TABLE IF NOT EXISTS app_usuarios (
  id          BIGSERIAL PRIMARY KEY,
  username    TEXT NOT NULL UNIQUE,
  pass_hash   TEXT NOT NULL,
  rol         TEXT DEFAULT 'lector',
  nombre      TEXT,
  apellido    TEXT,
  activo      BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  created_by  TEXT
);
-- Migración si ya tienes la tabla:
ALTER TABLE app_usuarios ADD COLUMN IF NOT EXISTS nombre TEXT;
ALTER TABLE app_usuarios ADD COLUMN IF NOT EXISTS apellido TEXT;
INSERT INTO app_usuarios (username, pass_hash, rol)
VALUES ('admin','ac9689e2272427085e35b9d3e3e8bed88cb3434828b43b86fc0596cad4c6e270','admin')
ON CONFLICT (username) DO NOTHING;
SELECT 'Usuarios listos' AS resultado;

Contraseña inicial del admin: admin1234 — cámbiala después.