-- database/schema.sql
-- Base de Datos para el Dashboard de Gas Natural & Hub Waha

CREATE DATABASE IF NOT EXISTS gas_dashboard CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gas_dashboard;

-- 1. Tabla principal de precios (particionada por año)
CREATE TABLE IF NOT EXISTS price_snapshots (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    hub_code VARCHAR(20) NOT NULL, -- 'WAHA', 'HH', 'SOCAL'
    price DECIMAL(10,4) NOT NULL,
    basis_vs_hh DECIMAL(10,4) DEFAULT NULL,
    volume DECIMAL(15,2) DEFAULT NULL,
    source VARCHAR(50) NOT NULL, -- 'EIA','CME','MANUAL'
    captured_at DATETIME NOT NULL,
    PRIMARY KEY (id, captured_at),
    INDEX idx_hub_date (hub_code, captured_at),
    INDEX idx_date (captured_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(captured_at)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 2. Curvas Forward (Futuros)
CREATE TABLE IF NOT EXISTS forward_curves (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    hub_code VARCHAR(20) NOT NULL,
    contract_month DATE NOT NULL, -- primer día del mes
    fixed_price DECIMAL(10,4) NOT NULL,
    basis DECIMAL(10,4) DEFAULT NULL,
    source VARCHAR(50) NOT NULL,
    captured_at DATETIME NOT NULL,
    INDEX idx_hub_month (hub_code, contract_month)
) ENGINE=InnoDB;

-- 3. Almacenamiento semanal de la EIA
CREATE TABLE IF NOT EXISTS storage_weekly (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(30) NOT NULL, -- 'LOWER48','EAST','WEST','SOUTH'
    bcf_value DECIMAL(10,2) NOT NULL,
    change_bcf DECIMAL(8,2) DEFAULT NULL,
    avg_5yr_bcf DECIMAL(10,2) DEFAULT NULL,
    pct_vs_5yr DECIMAL(6,2) DEFAULT NULL,
    report_date DATE NOT NULL,
    UNIQUE KEY uq_region_date (region, report_date)
) ENGINE=InnoDB;

-- 4. Coberturas Propias (Hedges)
CREATE TABLE IF NOT EXISTS positions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id VARCHAR(50) NOT NULL,
    type ENUM('FORWARD','FUTURES','SWAP','SPOT') NOT NULL,
    volume_mmbtu DECIMAL(15,2) NOT NULL,
    fixed_price DECIMAL(10,4) NOT NULL,
    index_ref VARCHAR(20) NOT NULL, -- 'WAHA','HH'
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    counterparty VARCHAR(100) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 5. Bitácora de Alertas Enviadas
CREATE TABLE IF NOT EXISTS alerts_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    alert_type VARCHAR(50) NOT NULL, -- 'CRITICA', 'URGENTE', 'INFO'
    hub_code VARCHAR(20) NOT NULL,
    triggered_val DECIMAL(10,4) NOT NULL,
    threshold_val DECIMAL(10,4) NOT NULL,
    message TEXT NOT NULL,
    recipients TEXT NOT NULL, -- JSON array of emails
    sent_at DATETIME NOT NULL,
    INDEX idx_type_date (alert_type, sent_at)
) ENGINE=InnoDB;

-- 6. Pronósticos guardados (Calculados por procesos nocturnos)
CREATE TABLE IF NOT EXISTS forecasts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    model_name VARCHAR(30) NOT NULL, -- 'SMA30','EMA','ARIMA','PROPHET'
    hub_code VARCHAR(20) NOT NULL,
    forecast_date DATE NOT NULL,
    predicted_val DECIMAL(10,4) NOT NULL,
    ci_lower_80 DECIMAL(10,4) DEFAULT NULL,
    ci_upper_80 DECIMAL(10,4) DEFAULT NULL,
    mape_pct DECIMAL(6,3) DEFAULT NULL,
    run_at DATETIME NOT NULL,
    INDEX idx_hub_date (hub_code, forecast_date)
) ENGINE=InnoDB;

-- 7. Usuarios y Roles del Sistema
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    role ENUM('CEO','ADMIN','FINANCE','VIEWER') NOT NULL,
    alert_prefs JSON DEFAULT NULL,
    last_login DATETIME DEFAULT NULL,
    active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

-- 8. Datos del Clima para Invernaderos (Correlación clima-consumo)
CREATE TABLE IF NOT EXISTS weather_data (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone VARCHAR(50) NOT NULL, -- ej. 'COAHUILA_GREENHOUSE'
    temp_actual DECIMAL(5,2) DEFAULT NULL,
    temp_normal DECIMAL(5,2) DEFAULT NULL,
    hdd_actual DECIMAL(8,2) DEFAULT NULL,
    hdd_normal DECIMAL(8,2) DEFAULT NULL,
    date DATE NOT NULL,
    UNIQUE KEY uq_zone_date (zone, date)
) ENGINE=InnoDB;