-- ============================================================
-- VISTAS — Base de Datos Regulatoria Eléctrica Colombiana
-- ============================================================

-- ============================================================
-- VISTA 1: v_documentos_completos
-- Vista desnormalizada para la tabla principal del frontend
-- ============================================================
CREATE OR REPLACE VIEW v_documentos_completos AS
SELECT
    d.id,
    e.codigo                                            AS entidad,
    e.nombre_completo                                   AS entidad_nombre,
    td.nombre                                           AS tipo_documento,
    td.codigo                                           AS tipo_codigo,
    d.numero,
    d.titulo,
    d.resumen,
    d.fecha_documento,
    d.anio,
    d.mes,
    d.dia,
    d.estado::TEXT                                      AS estado,
    d.nombre_archivo,
    d.ruta_local,
    d.url_origen,
    d.formato::TEXT                                     AS formato,
    d.resumen_ia                                       AS resumen_ia,

    -- Temas como array de textos y como string concatenado
    ARRAY_AGG(DISTINCT t.nombre ORDER BY t.nombre)      AS temas,
    STRING_AGG(DISTINCT t.nombre, ' | ' ORDER BY t.nombre) AS temas_str,
    ARRAY_AGG(DISTINCT t.codigo ORDER BY t.codigo)      AS temas_codigos,

    -- Agentes como array y string
    ARRAY_AGG(DISTINCT a.nombre ORDER BY a.nombre)      AS agentes,
    STRING_AGG(DISTINCT a.nombre, ' | ' ORDER BY a.nombre) AS agentes_str,
    ARRAY_AGG(DISTINCT a.codigo ORDER BY a.codigo)      AS agentes_codigos,

    d.created_at,
    d.updated_at

FROM documentos d
JOIN entidades          e  ON d.entidad_id         = e.id
JOIN tipos_documento    td ON d.tipo_documento_id   = td.id
LEFT JOIN documento_temas   dt ON d.id = dt.documento_id
LEFT JOIN temas             t  ON dt.tema_id = t.id
LEFT JOIN documento_agentes da ON d.id = da.documento_id
LEFT JOIN agentes           a  ON da.agente_id = a.id
GROUP BY
    d.id, e.id, e.codigo, e.nombre_completo,
    td.id, td.nombre, td.codigo;

-- ============================================================
-- VISTA 2: v_relaciones_regulatorias
-- Grafo de relaciones entre documentos para el panel de impacto
-- ============================================================
CREATE OR REPLACE VIEW v_relaciones_regulatorias AS
SELECT
    ir.id,
    ir.tipo::TEXT                       AS tipo_impacto,
    ir.descripcion,

    -- Documento origen
    d_orig.id                           AS origen_id,
    d_orig.numero                       AS origen_numero,
    d_orig.titulo                       AS origen_titulo,
    e_orig.codigo                       AS origen_entidad,
    td_orig.nombre                      AS origen_tipo,
    d_orig.fecha_documento              AS origen_fecha,
    d_orig.estado::TEXT                 AS origen_estado,

    -- Documento destino
    d_dest.id                           AS destino_id,
    d_dest.numero                       AS destino_numero,
    d_dest.titulo                       AS destino_titulo,
    e_dest.codigo                       AS destino_entidad,
    td_dest.nombre                      AS destino_tipo,
    d_dest.fecha_documento              AS destino_fecha,
    d_dest.estado::TEXT                 AS destino_estado

FROM impactos_regulatorios ir
JOIN documentos         d_orig   ON ir.documento_origen_id  = d_orig.id
JOIN documentos         d_dest   ON ir.documento_destino_id = d_dest.id
JOIN entidades          e_orig   ON d_orig.entidad_id        = e_orig.id
JOIN entidades          e_dest   ON d_dest.entidad_id        = e_dest.id
JOIN tipos_documento    td_orig  ON d_orig.tipo_documento_id = td_orig.id
JOIN tipos_documento    td_dest  ON d_dest.tipo_documento_id = td_dest.id;

-- ============================================================
-- VISTA 3: v_estadisticas_entidad
-- Conteos agrupados por entidad y estado para gráficos
-- ============================================================
CREATE OR REPLACE VIEW v_estadisticas_entidad AS
SELECT
    e.codigo                AS entidad,
    e.nombre_completo       AS entidad_nombre,
    d.estado::TEXT          AS estado,
    COUNT(d.id)             AS total
FROM documentos d
JOIN entidades e ON d.entidad_id = e.id
GROUP BY e.id, e.codigo, e.nombre_completo, d.estado;

-- ============================================================
-- VISTA 4: v_estadisticas_tema
-- Conteos de documentos por tema
-- ============================================================
CREATE OR REPLACE VIEW v_estadisticas_tema AS
SELECT
    t.codigo        AS tema_codigo,
    t.nombre        AS tema,
    COUNT(dt.documento_id) AS total
FROM temas t
LEFT JOIN documento_temas dt ON t.id = dt.tema_id
GROUP BY t.id, t.codigo, t.nombre
ORDER BY total DESC;

-- ============================================================
-- VISTA 5: v_estadisticas_anio
-- Documentos por año y entidad
-- ============================================================
CREATE OR REPLACE VIEW v_estadisticas_anio AS
SELECT
    d.anio,
    e.codigo        AS entidad,
    COUNT(d.id)     AS total
FROM documentos d
JOIN entidades e ON d.entidad_id = e.id
WHERE d.anio IS NOT NULL
GROUP BY d.anio, e.id, e.codigo
ORDER BY d.anio DESC, e.codigo;