-- Table for AGSI (Gas Storage) data
CREATE TABLE IF NOT EXISTS dm_agsi_storage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    gas_date DATE NOT NULL,
    country_code VARCHAR(10) NOT NULL,
    country_name VARCHAR(100) NOT NULL,
    gas_in_storage DECIMAL(12,4) NULL COMMENT 'TWh',
    consumption DECIMAL(12,4) NULL COMMENT 'TWh',
    consumption_full DECIMAL(8,2) NULL COMMENT '%',
    injection DECIMAL(12,4) NULL COMMENT 'GWh/day',
    withdrawal DECIMAL(12,4) NULL COMMENT 'GWh/day',
    net_withdrawal DECIMAL(12,4) NULL COMMENT 'GWh/day',
    working_gas_volume DECIMAL(12,4) NULL COMMENT 'TWh capacity',
    injection_capacity DECIMAL(12,4) NULL COMMENT 'GWh/day',
    withdrawal_capacity DECIMAL(12,4) NULL COMMENT 'GWh/day',
    contracted_capacity DECIMAL(12,4) NULL COMMENT 'TWh',
    available_capacity DECIMAL(12,4) NULL COMMENT 'TWh',
    covered_capacity DECIMAL(8,2) NULL COMMENT '%',
    full_percent DECIMAL(8,2) NULL COMMENT '% of working gas volume',
    trend DECIMAL(8,2) NULL COMMENT 'Daily change %',
    status CHAR(1) NULL COMMENT 'C=Confirmed, E=Estimated, N=No Data',
    updated_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_gas_storage (gas_date, country_code),
    KEY idx_gas_date (gas_date),
    KEY idx_country (country_code),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='AGSI Gas Storage data by country - daily updates at 19:30 CET';

-- Table for ALSI (LNG Terminals) data
CREATE TABLE IF NOT EXISTS dm_alsi_lng (
    id INT AUTO_INCREMENT PRIMARY KEY,
    gas_date DATE NOT NULL,
    country_code VARCHAR(10) NOT NULL,
    country_name VARCHAR(100) NOT NULL,
    lng_inventory DECIMAL(12,4) NULL COMMENT 'LNG in 1000 m³',
    gwh_inventory DECIMAL(12,4) NULL COMMENT 'LNG in GWh',
    send_out DECIMAL(12,4) NULL COMMENT 'Regasification GWh/day',
    dtmi_lng DECIMAL(12,4) NULL COMMENT 'Day-ahead Technical Maximum Injection (LNG)',
    dtmi_gwh DECIMAL(12,4) NULL COMMENT 'Day-ahead Technical Maximum Injection (GWh)',
    dtrs DECIMAL(12,4) NULL COMMENT 'Day-ahead Technical Regasification Send-out GWh/day',
    contracted_capacity DECIMAL(12,4) NULL COMMENT 'GWh/day',
    available_capacity DECIMAL(12,4) NULL COMMENT 'GWh/day',
    covered_capacity DECIMAL(8,2) NULL COMMENT '%',
    status CHAR(1) NULL COMMENT 'C=Confirmed, E=Estimated, N=No Data',
    updated_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_lng_terminal (gas_date, country_code),
    KEY idx_gas_date_lng (gas_date),
    KEY idx_country_lng (country_code),
    KEY idx_status_lng (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='ALSI LNG Terminal data by country - daily updates at 19:30 CET';
