-- Script de Inicialización de Base de Datos (Full Schema)

-- 1. Tabla de Configuración de Fuentes (etl_sources)
-- Esta tabla define qué fuentes se deben monitorear y cómo.
CREATE TABLE IF NOT EXISTS public.etl_sources (
  id text not null,
  name text not null,
  active boolean not null default false,
  type text null, -- Ej: 'api', 'scrape', 'complex_scraper'
  schedule_cron text not null, -- Ej: '*/5 * * * *'
  config jsonb null default '{}'::jsonb, -- Ej: { "url": "...", "headers": {...} }
  storage_config jsonb null default '{}'::jsonb, -- Ej: { "path": "raw/gas" }
  created_at timestamp with time zone null default timezone ('utc'::text, now()),
  updated_at timestamp with time zone null default timezone ('utc'::text, now()),
  constraint etl_sources_pkey primary key (id)
) TABLESPACE pg_default;

COMMENT ON TABLE public.etl_sources IS 'Configuración maestra de las fuentes de datos para el ETL';

-- 2. Tipos ENUM para el historial
-- Usamos bloques DO para evitar errores si los tipos ya existen
DO $$ BEGIN
    CREATE TYPE source_update_method AS ENUM ('api', 'scraping', 'html', 'archivo', 'complex_scraper');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE source_check_status AS ENUM ('no_change', 'changed', 'failed');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- 3. Tabla de Historial de Ejecuciones (source_check_history)
-- Registra cada vez que el scheduler verifica una fuente.
CREATE TABLE IF NOT EXISTS public.source_check_history (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    source_id TEXT NOT NULL REFERENCES public.etl_sources(id) ON DELETE CASCADE,
    status source_check_status NOT NULL,
    checksum TEXT, -- Hash del contenido para detectar cambios
    metadata JSONB DEFAULT '{}'::jsonb, -- Almacena url, method, notes, error details
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
);

-- Índices para optimización
-- Permite obtener rápidamente el último estado: ORDER BY created_at DESC LIMIT 1
CREATE INDEX IF NOT EXISTS idx_history_source_latest ON public.source_check_history (source_id, created_at DESC);
-- Permite filtrar por estado (ej: ver todos los fallos recientes)
CREATE INDEX IF NOT EXISTS idx_history_status ON public.source_check_history (status, created_at DESC);

COMMENT ON TABLE public.source_check_history IS 'Log inmutable de verificaciones de fuentes (Check Updates)';

-- ============================================
-- 4. Dimension temporal (dim_tiempo)

CREATE TABLE IF NOT EXISTS public.dim_tiempo (
  id SERIAL PRIMARY KEY,
  fecha DATE NOT NULL UNIQUE,
  anio SMALLINT NOT NULL,
  mes SMALLINT NOT NULL CHECK (mes BETWEEN 1 AND 12),
  trimestre SMALLINT GENERATED ALWAYS AS (((mes - 1) / 3) + 1) STORED,
  semestre SMALLINT GENERATED ALWAYS AS (CASE WHEN mes <= 6 THEN 1 ELSE 2 END) STORED,
  nombre_mes TEXT NOT NULL,
  es_proyeccion BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tiempo_anio_mes ON public.dim_tiempo(anio, mes);

COMMENT ON TABLE public.dim_tiempo IS 'Dimensión temporal compartida - granularidad mensual con campos derivados (trimestre, semestre)';

-- ============================================
-- 5. Dimension geografica (dim_territorios)
-- Departamentos/municipios con código DIVIPOLA

CREATE TABLE IF NOT EXISTS public.dim_territorios (
  id SERIAL PRIMARY KEY,
  departamento TEXT NOT NULL,
  municipio TEXT NOT NULL,
  latitud NUMERIC(10, 7),
  longitud NUMERIC(10, 7),
  divipola TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(departamento, municipio)
);

COMMENT ON TABLE public.dim_territorios IS 'Dimensión geográfica - departamentos/municipios con código DANE DIVIPOLA';

-- ============================================
-- 6. Dimension de campos (dim_campos)
-- Campos petroleros/gasíferos con contrato, operador y participación estatal

CREATE TABLE IF NOT EXISTS public.dim_campos (
  id SERIAL PRIMARY KEY,
  nombre_campo TEXT NOT NULL UNIQUE,
  contrato TEXT,
  operador TEXT,
  asociados TEXT[],
  participacion_estado NUMERIC(5, 2),
  territorio_id INT REFERENCES public.dim_territorios(id),
  activo BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_campos_territorio ON public.dim_campos(territorio_id);

COMMENT ON TABLE public.dim_campos IS 'Dimensión de campos de hidrocarburos con contrato, operador y participación estatal';

-- ============================================
-- 7. Tabla de referencia: unidades (ref_unidades)
-- Factores de conversión entre unidades de medida

CREATE TABLE IF NOT EXISTS public.ref_unidades (
  id SERIAL PRIMARY KEY,
  codigo TEXT NOT NULL UNIQUE,
  nombre TEXT NOT NULL,
  factor_a_gbtud NUMERIC(18, 10),
  descripcion TEXT
);

-- Insertar unidades base y factores de conversion
INSERT INTO public.ref_unidades (codigo, nombre, factor_a_gbtud, descripcion) VALUES
  ('GBTUD', 'Giga BTU por Día', 1.0, 'Unidad base para demanda'),
  ('KPCD', 'Kilo Pies Cúbicos por Día', 0.001, '1 KPCD ≈ 0.001 GBTUD'),
  ('MPCD', 'Millones Pies Cúbicos por Día', 1.0, '1 MPCD ≈ 1 GBTUD'),
  ('BLS', 'Barriles', NULL, 'Unidad de líquidos - sin conversión a gas'),
  ('KPC', 'Kilo Pies Cúbicos', 0.001, 'Unidad de volumen gas')
ON CONFLICT (codigo) DO NOTHING;

COMMENT ON TABLE public.ref_unidades IS 'Referencia de unidades de medida y factores de conversión a GBTUD';

-- ============================================
-- 8. Tabla de hechos: regalías (fact_regalias)
-- Fuente 1: API Socrata ANH - Liquidación de regalías por campo

CREATE TABLE IF NOT EXISTS public.fact_regalias (
  id BIGSERIAL PRIMARY KEY,
  tiempo_id INT REFERENCES public.dim_tiempo(id),
  campo_id INT REFERENCES public.dim_campos(id),
  
  -- Clasificación del hidrocarburo
  tipo_produccion TEXT,
  tipo_hidrocarburo CHAR(1) CHECK (tipo_hidrocarburo IN ('G', 'O')), -- G=Gas, O=Petróleo
  
  -- Valores económicos y volumétricos
  precio_usd NUMERIC(12, 4),
  porcentaje_regalia NUMERIC(5, 2),
  produccion_gravable NUMERIC(18, 4),
  volumen_regalia NUMERIC(18, 4),
  unidad TEXT DEFAULT 'Bls/Kpc',
  valor_regalias_cop NUMERIC(18, 2),
  
  -- Trazabilidad ETL
  source_id TEXT REFERENCES public.etl_sources(id),
  etl_timestamp TIMESTAMPTZ DEFAULT NOW(),
  
  -- Constraint UNIQUE: evita duplicados al re-ejecutar ETL
  -- Un campo solo puede tener un registro por mes y tipo de hidrocarburo
  CONSTRAINT fact_regalias_unique_key UNIQUE (tiempo_id, campo_id, tipo_hidrocarburo)
);

CREATE INDEX IF NOT EXISTS idx_regalias_tiempo ON public.fact_regalias(tiempo_id);
CREATE INDEX IF NOT EXISTS idx_regalias_campo ON public.fact_regalias(campo_id);
CREATE INDEX IF NOT EXISTS idx_regalias_tipo ON public.fact_regalias(tipo_hidrocarburo);
CREATE INDEX IF NOT EXISTS idx_regalias_source ON public.fact_regalias(source_id);

COMMENT ON TABLE public.fact_regalias IS 'Hechos: Liquidación de regalías por campo - Fuente ANH API Socrata (j7js-yk74)';

-- ============================================
-- 9. Tabla de hechos: demanda gas (fact_demanda_gas)
-- Fuente 2: UPME - Proyección de demanda con CHECK constraints

CREATE TABLE IF NOT EXISTS public.fact_demanda_gas (
  id BIGSERIAL PRIMARY KEY,
  tiempo_id INT REFERENCES public.dim_tiempo(id),
  
  -- Dimensiones categóricas con CHECK constraints
  escenario TEXT CHECK (escenario IN ('MEDIO', 'ALTO', 'BAJO', 'IC_95', 'IC_68')),
  sector TEXT CHECK (sector IN ('RESIDENCIAL', 'TERCIARIO', 'INDUSTRIAL', 'PETROQUIMICA', 'PETROLERO', 'GNV', 'TERMOELECTRICO', 'REFINERIA')),
  region TEXT CHECK (region IN ('CENTRO', 'COSTA', 'CQR', 'NOROESTE', 'OCCIDENTE', 'ORIENTE', 'SUR', 'TOLIMA_GRANDE')),
  segmento TEXT CHECK (segmento IN ('TOTAL', 'PETROLERO', 'TERMOELECTRICO', 'NO_TERMOELECTRICO')),
  nivel_agregacion TEXT CHECK (nivel_agregacion IN ('nacional', 'sectorial', 'regional')),
  
  -- Valor de demanda
  valor_demanda_gbtud NUMERIC(18, 6),
  
  -- Trazabilidad ETL
  source_id TEXT REFERENCES public.etl_sources(id),
  etl_timestamp TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_demanda_tiempo ON public.fact_demanda_gas(tiempo_id);
CREATE INDEX IF NOT EXISTS idx_demanda_escenario ON public.fact_demanda_gas(escenario);
CREATE INDEX IF NOT EXISTS idx_demanda_sector ON public.fact_demanda_gas(sector);
CREATE INDEX IF NOT EXISTS idx_demanda_region ON public.fact_demanda_gas(region);
CREATE INDEX IF NOT EXISTS idx_demanda_source ON public.fact_demanda_gas(source_id);

COMMENT ON TABLE public.fact_demanda_gas IS 'Hechos: Proyección de demanda de gas natural - Fuente UPME Anexo Proyección';

-- ============================================
-- 10. Dimension de resoluciones (dim_resoluciones)
-- Metadata de resoluciones MinMinas con periodos de vigencia

CREATE TABLE IF NOT EXISTS public.dim_resoluciones (
  id SERIAL PRIMARY KEY,
  numero_resolucion TEXT NOT NULL,
  fecha_resolucion DATE,
  periodo_desde DATE NOT NULL,
  periodo_hasta DATE NOT NULL,
  titulo TEXT,
  url_pdf TEXT,
  url_soporte_magnetico TEXT,
  
  -- Trazabilidad
  source_id TEXT REFERENCES public.etl_sources(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  CONSTRAINT dim_resoluciones_unique_numero UNIQUE (numero_resolucion)
);

CREATE INDEX IF NOT EXISTS idx_resoluciones_periodo ON public.dim_resoluciones(periodo_desde, periodo_hasta);

COMMENT ON TABLE public.dim_resoluciones IS 'Dimensión de resoluciones MinMinas - cada resolución define un periodo de declaración de producción';

-- ============================================
-- 11. Tabla de hechos: oferta/producción (fact_oferta_gas)
-- Fuente: MinMinas - Declaración de producción por campo, operador y tipo

CREATE TABLE IF NOT EXISTS public.fact_oferta_gas (
  id BIGSERIAL PRIMARY KEY,
  
  -- Dimensiones FK
  tiempo_id INT NOT NULL REFERENCES public.dim_tiempo(id),
  campo_id INT NOT NULL REFERENCES public.dim_campos(id),
  resolucion_id INT REFERENCES public.dim_resoluciones(id),
  
  -- Tipo de producción/destino (sin tabla dimensión)
  tipo_produccion TEXT CHECK (tipo_produccion IN (
    'PTDV',           -- Producción Total Disponible para Venta
    'PC_CONTRATOS',   -- Producción Comprometida - Contratos consumo interno
    'PC_EXPORTACIONES', -- Producción Comprometida - Exportaciones
    'PC_REF_BARRANCA', -- Producción Comprometida - Refinería Barrancabermeja
    'PC_REF_CARTAGENA', -- Producción Comprometida - Refinería Cartagena
    'PP',             -- Potencial Producción (declarado por operador)
    'GAS_OPERACION',  -- Gas consumido en operaciones del campo
    'CIDV'            -- Capacidad Instalada Disponible para Venta
  )),
  
  -- Operador (puede diferir del operador principal del campo si hay asociados)
  operador TEXT NOT NULL,
  es_operador_campo BOOLEAN DEFAULT TRUE, -- TRUE si es operador principal, FALSE si es asociado/estado
  es_participacion_estado BOOLEAN DEFAULT FALSE, -- TRUE si es la parte correspondiente al Estado
  
  -- Valores de producción
  valor_gbtud NUMERIC(18, 6) NOT NULL, -- Valor en GBTUD (unidad normalizada)
  
  -- Metadata del campo (snapshot del Excel)
  poder_calorifico_btu_pc NUMERIC(12, 4), -- BTU/PC del campo en esta resolución
  
  -- Trazabilidad ETL
  source_id TEXT REFERENCES public.etl_sources(id),
  etl_timestamp TIMESTAMPTZ DEFAULT NOW(),
  
  -- Constraint UNIQUE para evitar duplicados: un operador solo puede tener un valor
  -- por campo, mes, tipo de producción y resolución
  CONSTRAINT fact_oferta_gas_unique_key UNIQUE (
    tiempo_id, campo_id, resolucion_id, tipo_produccion, operador, es_participacion_estado
  )
);

-- Índices para consultas frecuentes
CREATE INDEX IF NOT EXISTS idx_oferta_tiempo ON public.fact_oferta_gas(tiempo_id);
CREATE INDEX IF NOT EXISTS idx_oferta_campo ON public.fact_oferta_gas(campo_id);
CREATE INDEX IF NOT EXISTS idx_oferta_resolucion ON public.fact_oferta_gas(resolucion_id);
CREATE INDEX IF NOT EXISTS idx_oferta_tipo ON public.fact_oferta_gas(tipo_produccion);
CREATE INDEX IF NOT EXISTS idx_oferta_operador ON public.fact_oferta_gas(operador);
CREATE INDEX IF NOT EXISTS idx_oferta_source ON public.fact_oferta_gas(source_id);

COMMENT ON TABLE public.fact_oferta_gas IS 'Hechos: Declaración de producción de gas natural por operador - Fuente MinMinas';

-- ============================================
-- 13. Tabla de participación por resolución (fact_participacion_campo)
-- Registra la participación de asociados/estado por campo y período

CREATE TABLE IF NOT EXISTS public.fact_participacion_campo (
  id BIGSERIAL PRIMARY KEY,
  
  -- Dimensiones FK
  campo_id INT NOT NULL REFERENCES public.dim_campos(id),
  resolucion_id INT NOT NULL REFERENCES public.dim_resoluciones(id),
  
  -- Período específico dentro de la resolución
  periodo_desde DATE NOT NULL,
  periodo_hasta DATE NOT NULL,
  
  -- Asociado y participación
  asociado TEXT NOT NULL, -- Nombre de la empresa asociada
  participacion_pct NUMERIC(5, 2) NOT NULL, -- Porcentaje de participación
  estado_pct NUMERIC(5, 2), -- Porcentaje correspondiente al Estado
  
  -- Trazabilidad
  source_id TEXT REFERENCES public.etl_sources(id),
  etl_timestamp TIMESTAMPTZ DEFAULT NOW(),
  
  CONSTRAINT fact_participacion_unique_key UNIQUE (
    campo_id, resolucion_id, periodo_desde, asociado
  )
);

CREATE INDEX IF NOT EXISTS idx_participacion_campo ON public.fact_participacion_campo(campo_id);
CREATE INDEX IF NOT EXISTS idx_participacion_resolucion ON public.fact_participacion_campo(resolucion_id);

COMMENT ON TABLE public.fact_participacion_campo IS 'Participación de asociados y estado por campo según resoluciones MinMinas';
