-- =====================================================
-- TABLES CME GROUP - Provider Complet
-- =====================================================
-- Date: 2025-11-08
-- Description: Architecture complète pour CME Group avec tous les produits identifiés

-- Table principale CME (settlements générique)
CREATE TABLE IF NOT EXISTS dm_cme_settlements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Identification produit
    product_code VARCHAR(50) NOT NULL COMMENT 'Code CME (ex: NGZ25, 6EZ5)',
    product_name VARCHAR(255) NOT NULL COMMENT 'Nom complet du produit',
    product_category ENUM('FX','ENERGY','COMMODITIES','METALS','AGRICULTURE') NOT NULL,
    exchange_code ENUM('CME','NYMEX','CBOT','COMEX') NOT NULL COMMENT 'Sous-exchange CME',
    
    -- Contract details
    contract_month VARCHAR(10) NOT NULL COMMENT 'DEC25, JAN26, etc.',
    expiration_date DATE NULL COMMENT 'Date d\'expiration du contrat',
    globex_code VARCHAR(20) NOT NULL COMMENT 'Code Globex trading',
    
    -- Prix OHLCS (Open, High, Low, Close, Settlement)
    open_price DECIMAL(15,6) NULL COMMENT 'Prix d\'ouverture',
    high_price DECIMAL(15,6) NULL COMMENT 'Plus haut',
    low_price DECIMAL(15,6) NULL COMMENT 'Plus bas', 
    close_price DECIMAL(15,6) NULL COMMENT 'Prix de clôture',
    settlement_price DECIMAL(15,6) NOT NULL COMMENT 'Prix de settlement',
    
    -- Variations
    price_change DECIMAL(15,6) NULL COMMENT 'Variation absolue',
    price_change_percent DECIMAL(8,4) NULL COMMENT 'Variation en %',
    
    -- Volumes et intérêts
    volume BIGINT NULL DEFAULT 0 COMMENT 'Volume journalier',
    open_interest BIGINT NULL DEFAULT 0 COMMENT 'Intérêt ouvert',
    estimated_volume BIGINT NULL DEFAULT 0 COMMENT 'Volume estimé',
    prior_day_open_interest BIGINT NULL DEFAULT 0 COMMENT 'OI jour précédent',
    
    -- Métadonnées trading
    trade_date DATE NOT NULL COMMENT 'Date de trading',
    settlement_date DATE NOT NULL COMMENT 'Date de settlement',
    currency VARCHAR(10) NOT NULL DEFAULT 'USD' COMMENT 'Devise',
    unit_of_measure VARCHAR(50) NULL COMMENT 'Unité de mesure',
    
    -- Bid/Ask si disponible
    bid_price DECIMAL(15,6) NULL COMMENT 'Prix bid',
    ask_price DECIMAL(15,6) NULL COMMENT 'Prix ask',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Index pour performance
    INDEX idx_product_date (product_code, trade_date),
    INDEX idx_category_date (product_category, trade_date),
    INDEX idx_globex_date (globex_code, trade_date),
    INDEX idx_settlement_date (settlement_date),
    UNIQUE KEY unique_contract_date (product_code, trade_date, settlement_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Table principale CME Group settlements - tous produits';

-- =====================================================
-- TABLE SPÉCIALISÉE FX (Foreign Exchange)
-- =====================================================
CREATE TABLE IF NOT EXISTS dm_cme_fx_futures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Référence vers table principale
    settlement_id INT NULL COMMENT 'Référence dm_cme_settlements',
    
    -- Identification FX
    currency_pair VARCHAR(10) NOT NULL COMMENT 'EURUSD, GBPUSD, etc.',
    base_currency VARCHAR(3) NOT NULL COMMENT 'EUR, GBP, etc.',
    quote_currency VARCHAR(3) NOT NULL DEFAULT 'USD' COMMENT 'Toujours USD pour CME',
    
    -- Prix FX spécifiques
    fx_rate DECIMAL(12,8) NOT NULL COMMENT 'Taux de change settlement',
    fx_change DECIMAL(12,8) NULL COMMENT 'Variation du taux',
    
    -- Contract specs FX
    contract_size INT NOT NULL DEFAULT 125000 COMMENT 'Taille du contrat',
    tick_size DECIMAL(10,8) NOT NULL DEFAULT 0.0001 COMMENT 'Plus petite variation',
    
    -- Dates
    trade_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_currency_pair_date (currency_pair, trade_date),
    INDEX idx_fx_rate (fx_rate),
    FOREIGN KEY (settlement_id) REFERENCES dm_cme_settlements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='CME FX Futures - Euro, GBP, etc.';

-- =====================================================
-- TABLE SPÉCIALISÉE ÉNERGIE
-- =====================================================
CREATE TABLE IF NOT EXISTS dm_cme_energy_futures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Référence vers table principale
    settlement_id INT NULL COMMENT 'Référence dm_cme_settlements',
    
    -- Identification énergie
    energy_type ENUM('NATURAL_GAS','CRUDE_OIL','LNG','PROPANE','COAL','BRENT') NOT NULL,
    delivery_location VARCHAR(100) NULL COMMENT 'Henry Hub, NBP, TTF, etc.',
    
    -- Prix énergie (multiples unités possibles)
    price_per_unit DECIMAL(15,6) NOT NULL COMMENT 'Prix par unité',
    unit_measure ENUM('MMBtu','barrel','therm','tonne','gallon') NOT NULL,
    
    -- Spécifications contrat
    contract_size VARCHAR(50) NULL COMMENT '10,000 MMBtu, 1,000 barrels, etc.',
    delivery_method ENUM('PHYSICAL','FINANCIAL','CASH_SETTLED') NOT NULL DEFAULT 'FINANCIAL',
    
    -- Geographic/Quality specs
    region VARCHAR(50) NULL COMMENT 'US, Europe, Asia-Pacific',
    quality_spec VARCHAR(100) NULL COMMENT 'API gravity, sulfur content, etc.',
    
    -- Données spécifiques énergie
    heating_value DECIMAL(10,4) NULL COMMENT 'Valeur calorifique si gaz',
    api_gravity DECIMAL(6,2) NULL COMMENT 'Gravité API si pétrole',
    
    -- Dates
    trade_date DATE NOT NULL,
    delivery_start DATE NULL COMMENT 'Début livraison',
    delivery_end DATE NULL COMMENT 'Fin livraison',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_energy_type_date (energy_type, trade_date),
    INDEX idx_location_date (delivery_location, trade_date),
    INDEX idx_delivery_period (delivery_start, delivery_end),
    FOREIGN KEY (settlement_id) REFERENCES dm_cme_settlements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='CME Energy Futures - Gas, Oil, LNG, etc.';

-- =====================================================
-- TABLE SPÉCIALISÉE COMMODITÉS
-- =====================================================
CREATE TABLE IF NOT EXISTS dm_cme_commodities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Référence vers table principale  
    settlement_id INT NULL COMMENT 'Référence dm_cme_settlements',
    
    -- Identification commodité
    commodity_type ENUM('COAL','METALS','AGRICULTURE','FREIGHT','LIVESTOCK') NOT NULL,
    commodity_grade VARCHAR(100) NULL COMMENT 'API2, API4, etc.',
    
    -- Prix commodité
    price_per_unit DECIMAL(15,6) NOT NULL,
    price_currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    unit_measure ENUM('tonne','MT','cwt','bushel','pound','ounce','TEU') NOT NULL,
    
    -- Spécifications produit
    quality_standard VARCHAR(100) NULL COMMENT 'ARGUS-McCloskey, Platts, etc.',
    origin_region VARCHAR(100) NULL COMMENT 'CIF ARA, FOB Australia, etc.',
    
    -- Contract specs
    contract_size VARCHAR(50) NULL,
    delivery_terms VARCHAR(100) NULL COMMENT 'CIF, FOB, DDP, etc.',
    
    -- Index/Benchmark
    pricing_basis VARCHAR(100) NULL COMMENT 'ARGUS, Platts, Baltic Exchange',
    benchmark_index VARCHAR(100) NULL COMMENT 'Index de référence',
    
    -- Dates
    trade_date DATE NOT NULL,
    delivery_period_start DATE NULL,
    delivery_period_end DATE NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_commodity_type_date (commodity_type, trade_date),
    INDEX idx_grade_date (commodity_grade, trade_date),
    INDEX idx_pricing_basis (pricing_basis),
    FOREIGN KEY (settlement_id) REFERENCES dm_cme_settlements(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='CME Commodities - Coal, Freight, Agriculture, etc.';

-- =====================================================
-- TABLE DE CONFIGURATION CME
-- =====================================================
CREATE TABLE IF NOT EXISTS cme_product_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Identification produit
    product_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Code CME du produit',
    product_name VARCHAR(255) NOT NULL,
    product_category ENUM('FX','ENERGY','COMMODITIES','METALS','AGRICULTURE') NOT NULL,
    exchange_code ENUM('CME','NYMEX','CBOT','COMEX') NOT NULL,
    
    -- URLs de scraping
    settlements_url VARCHAR(500) NULL COMMENT 'URL page settlements',
    quotes_url VARCHAR(500) NULL COMMENT 'URL page quotes',
    volume_url VARCHAR(500) NULL COMMENT 'URL page volume',
    
    -- Configuration scraping
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    scraper_frequency ENUM('DAILY','HOURLY','REALTIME') DEFAULT 'DAILY',
    last_scraped TIMESTAMP NULL,
    
    -- Spécifications contrat
    contract_size VARCHAR(100) NULL,
    tick_size DECIMAL(15,8) NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    unit_of_measure VARCHAR(50) NULL,
    
    -- Mapping vers tables spécialisées
    target_table ENUM('settlements','fx_futures','energy_futures','commodities') NOT NULL DEFAULT 'settlements',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_category (product_category),
    INDEX idx_exchange (exchange_code),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Configuration des produits CME pour scraping';

-- =====================================================
-- INSERTION DES PRODUITS IDENTIFIÉS
-- =====================================================

INSERT INTO cme_product_config (
    product_code, product_name, product_category, exchange_code,
    settlements_url, target_table, contract_size, currency, unit_of_measure
) VALUES 
-- FX PRODUCTS
('6E', 'Euro FX Futures', 'FX', 'CME', 
 'https://www.cmegroup.com/markets/fx/g10/euro-fx.settlements.html',
 'fx_futures', '125,000 EUR', 'USD', 'EUR per USD'),

('6B', 'British Pound Futures', 'FX', 'CME',
 'https://www.cmegroup.com/markets/fx/g10/british-pound.settlements.html', 
 'fx_futures', '62,500 GBP', 'USD', 'GBP per USD'),

-- ENERGY PRODUCTS  
('NG', 'Natural Gas Henry Hub', 'ENERGY', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/natural-gas/natural-gas.settlements.html',
 'energy_futures', '10,000 MMBtu', 'USD', 'per MMBtu'),

('TTF', 'Dutch TTF Natural Gas', 'ENERGY', 'NYMEX', 
 'https://www.cmegroup.com/markets/energy/natural-gas/dutch-ttf-natural-gas-calendar-month.settlements.html',
 'energy_futures', '1,000 MMBtu', 'USD', 'per MMBtu'),

('M6E', 'UK NBP Natural Gas', 'ENERGY', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/natural-gas/uk-nbp-natural-gas-calendar-month.settlements.html', 
 'energy_futures', '1,000 MMBtu', 'USD', 'per MMBtu'),

('CL', 'Crude Oil WTI', 'ENERGY', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/crude-oil/light-sweet-crude.settlements.html',
 'energy_futures', '1,000 barrels', 'USD', 'per barrel'),

('BZ', 'Brent Crude Oil', 'ENERGY', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/crude-oil/brent-crude-oil.settlements.html',
 'energy_futures', '1,000 barrels', 'USD', 'per barrel'),

('JKM', 'LNG Japan/Korea Marker', 'ENERGY', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/natural-gas/lng-japan-korea-marker-platts-swap.settlements.html',
 'energy_futures', '1,000 MMBtu', 'USD', 'per MMBtu'),

-- COMMODITIES
('MTF', 'Coal API2 CIF ARA', 'COMMODITIES', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/coal/coal-api-2-cif-ara-argus-mccloskey-swap.settlements.html',
 'commodities', '1,000 MT', 'USD', 'per tonne'),

('A1L', 'Mini European Propane CIF ARA', 'COMMODITIES', 'NYMEX',
 'https://www.cmegroup.com/markets/energy/petrochemicals/mini-european-propane-cif-ara-argus-swap.settlements.html',
 'commodities', '100 MT', 'USD', 'per tonne')

ON DUPLICATE KEY UPDATE 
    product_name = VALUES(product_name),
    settlements_url = VALUES(settlements_url),
    updated_at = CURRENT_TIMESTAMP;

-- =====================================================
-- MISE À JOUR DU MÉGA CATALOGUE
-- =====================================================

-- Ajouter CME comme nouveau provider
INSERT INTO dm_mega_catalog (
    provider, product_type, product_code, product_name, 
    country_code, country_name, currency, is_active,
    created_at, updated_at
) 
SELECT 
    'CME' as provider,
    CASE 
        WHEN product_category = 'FX' THEN 'FX_FUTURE'
        WHEN product_category = 'ENERGY' THEN 'ENERGY_FUTURE' 
        WHEN product_category = 'COMMODITIES' THEN 'COMMODITY_FUTURE'
        ELSE 'FUTURE'
    END as product_type,
    product_code,
    product_name,
    'US' as country_code,
    'United States' as country_name,
    currency,
    is_active,
    NOW(),
    NOW()
FROM cme_product_config
WHERE NOT EXISTS (
    SELECT 1 FROM dm_mega_catalog 
    WHERE provider = 'CME' AND product_code = cme_product_config.product_code
);

COMMIT;