-- ============================================================
-- BASE DE DATOS REGULATORIA — MERCADO ELÉCTRICO COLOMBIANO
-- Schema v1.0 | Entidades: CREG, MME, CNO
-- ============================================================

-- Eliminar base si existe y recrear
-- DROP DATABASE IF EXISTS regulatoria_electrica;
-- CREATE DATABASE regulatoria_electrica;

-- Extensiones útiles
CREATE EXTENSION IF NOT EXISTS "unaccent";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ============================================================
-- ENUMS
-- ============================================================

CREATE TYPE estado_documento AS ENUM (
    'Proyecto',
    'En comentarios',
    'Pendiente de comentarios',
    'Vigente',
    'Derogado',
    'Modificado',
    'Compilado',
    'Histórico'
);

CREATE TYPE tipo_impacto AS ENUM (
    'modifica a',
    'modificado por',
    'deroga a',
    'derogado por',
    'cita a',
    'relacionado con'
);

CREATE TYPE formato_archivo AS ENUM (
    'PDF',
    'HTML',
    'DOCX',
    'XLSX',
    'OTRO'
);

-- ============================================================
-- TABLA: entidades
-- ============================================================
CREATE TABLE IF NOT EXISTS entidades (
    id              SERIAL PRIMARY KEY,
    codigo          VARCHAR(10) NOT NULL UNIQUE,   -- CREG, MME, CNO
    nombre_completo TEXT        NOT NULL,
    descripcion     TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- TABLA: tipos_documento
-- ============================================================
CREATE TABLE IF NOT EXISTS tipos_documento (
    id          SERIAL PRIMARY KEY,
    entidad_id  INT         NOT NULL REFERENCES entidades(id) ON DELETE CASCADE,
    codigo      VARCHAR(30) NOT NULL,              -- RES, DEC, ACUERDO, etc.
    nombre      VARCHAR(100) NOT NULL,
    UNIQUE (entidad_id, codigo)
);

-- ============================================================
-- TABLA: temas
-- ============================================================
CREATE TABLE IF NOT EXISTS temas (
    id      SERIAL PRIMARY KEY,
    codigo  VARCHAR(50) NOT NULL UNIQUE,
    nombre  VARCHAR(150) NOT NULL
);

-- ============================================================
-- TABLA: agentes
-- ============================================================
CREATE TABLE IF NOT EXISTS agentes (
    id      SERIAL PRIMARY KEY,
    codigo  VARCHAR(30) NOT NULL UNIQUE,
    nombre  VARCHAR(100) NOT NULL
);

-- ============================================================
-- TABLA: documentos (tabla maestra)
-- ============================================================
CREATE TABLE IF NOT EXISTS documentos (
    id                  SERIAL PRIMARY KEY,
    entidad_id          INT          NOT NULL REFERENCES entidades(id),
    tipo_documento_id   INT          NOT NULL REFERENCES tipos_documento(id),

    -- Identificación
    numero              VARCHAR(50),               -- número del acto (ej: 101066)
    titulo              TEXT         NOT NULL,
    resumen             TEXT,

    -- Fechas
    fecha_documento     DATE         NOT NULL,
    anio                SMALLINT GENERATED ALWAYS AS (EXTRACT(YEAR FROM fecha_documento)::SMALLINT) STORED,
    mes                 SMALLINT GENERATED ALWAYS AS (EXTRACT(MONTH FROM fecha_documento)::SMALLINT) STORED,
    dia                 SMALLINT GENERATED ALWAYS AS (EXTRACT(DAY FROM fecha_documento)::SMALLINT) STORED,

    -- Archivo físico
    nombre_archivo      VARCHAR(300),              -- nombre normalizado
    ruta_local          TEXT,                      -- ruta en disco /docs/...
    url_origen          TEXT,                      -- URL oficial de origen
    formato             formato_archivo DEFAULT 'PDF',

    -- Estado
    estado              estado_documento NOT NULL DEFAULT 'Vigente',

    -- Metadatos
    created_at          TIMESTAMPTZ DEFAULT NOW(),
    updated_at          TIMESTAMPTZ DEFAULT NOW()
);

-- Índices de búsqueda
CREATE INDEX IF NOT EXISTS idx_doc_entidad    ON documentos(entidad_id);
CREATE INDEX IF NOT EXISTS idx_doc_tipo       ON documentos(tipo_documento_id);
CREATE INDEX IF NOT EXISTS idx_doc_fecha      ON documentos(fecha_documento);
CREATE INDEX IF NOT EXISTS idx_doc_anio       ON documentos(anio);
CREATE INDEX IF NOT EXISTS idx_doc_estado     ON documentos(estado);
CREATE INDEX IF NOT EXISTS idx_doc_titulo     ON documentos USING GIN (to_tsvector('spanish', titulo));

-- Trigger para updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_documentos_updated_at
    BEFORE UPDATE ON documentos
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ============================================================
-- TABLA: documento_temas (N:M)
-- ============================================================
CREATE TABLE IF NOT EXISTS documento_temas (
    documento_id    INT NOT NULL REFERENCES documentos(id) ON DELETE CASCADE,
    tema_id         INT NOT NULL REFERENCES temas(id)      ON DELETE CASCADE,
    PRIMARY KEY (documento_id, tema_id)
);

-- ============================================================
-- TABLA: documento_agentes (N:M)
-- ============================================================
CREATE TABLE IF NOT EXISTS documento_agentes (
    documento_id    INT NOT NULL REFERENCES documentos(id)  ON DELETE CASCADE,
    agente_id       INT NOT NULL REFERENCES agentes(id)     ON DELETE CASCADE,
    PRIMARY KEY (documento_id, agente_id)
);

-- ============================================================
-- TABLA: impactos_regulatorios
-- Registra las relaciones entre documentos
-- ============================================================
CREATE TABLE IF NOT EXISTS impactos_regulatorios (
    id                  SERIAL PRIMARY KEY,
    documento_origen_id INT          NOT NULL REFERENCES documentos(id) ON DELETE CASCADE,
    documento_destino_id INT         NOT NULL REFERENCES documentos(id) ON DELETE CASCADE,
    tipo                tipo_impacto NOT NULL,
    descripcion         TEXT,
    created_at          TIMESTAMPTZ DEFAULT NOW(),
    CHECK (documento_origen_id <> documento_destino_id)
);

CREATE INDEX IF NOT EXISTS idx_impacto_origen  ON impactos_regulatorios(documento_origen_id);
CREATE INDEX IF NOT EXISTS idx_impacto_destino ON impactos_regulatorios(documento_destino_id);