-- Table de configuration des produits CME
-- Cette table stocke les URLs et paramètres de scraping pour chaque produit CME

CREATE TABLE IF NOT EXISTS cme_product_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(10) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    scraper_url TEXT,
    url_pattern VARCHAR(500),
    active BOOLEAN DEFAULT 1,
    scraping_enabled BOOLEAN DEFAULT 1,
    data_source ENUM('CME_WEBSITE', 'CME_API', 'FTP', 'OTHER') DEFAULT 'CME_WEBSITE',
    update_frequency ENUM('REALTIME', 'HOURLY', 'DAILY', 'WEEKLY') DEFAULT 'DAILY',
    last_scraped TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_product (product_code),
    INDEX idx_active (active),
    INDEX idx_scraping_enabled (scraping_enabled)
);

-- Insertion des configurations pour les produits CME existants
INSERT INTO cme_product_config (product_code, product_name, scraper_url, active, scraping_enabled, data_source) VALUES
-- FX Futures
('6E', 'Euro FX Futures', 'https://www.cmegroup.com/markets/fx/g10/euro-fx.html', 1, 1, 'CME_WEBSITE'),
('6B', 'British Pound Futures', 'https://www.cmegroup.com/markets/fx/g10/british-pound.html', 1, 1, 'CME_WEBSITE'),
('6J', 'Japanese Yen Futures', 'https://www.cmegroup.com/markets/fx/g10/japanese-yen.html', 1, 1, 'CME_WEBSITE'),
('6S', 'Swiss Franc Futures', 'https://www.cmegroup.com/markets/fx/g10/swiss-franc.html', 1, 1, 'CME_WEBSITE'),

-- Energy Futures  
('NG', 'Natural Gas Futures', 'https://www.cmegroup.com/markets/energy/natural-gas/natural-gas.html', 1, 1, 'CME_WEBSITE'),
('TTF', 'Dutch TTF Gas Futures', 'https://www.cmegroup.com/markets/energy/natural-gas/dutch-ttf-natural-gas-calendar-month.html', 1, 1, 'CME_WEBSITE'),

-- Commodities
('ZC', 'Corn Futures', 'https://www.cmegroup.com/markets/agriculture/grains/corn.html', 1, 1, 'CME_WEBSITE'),
('ZW', 'Wheat Futures', 'https://www.cmegroup.com/markets/agriculture/grains/wheat.html', 1, 1, 'CME_WEBSITE'),
('ZS', 'Soybean Futures', 'https://www.cmegroup.com/markets/agriculture/oilseeds/soybean.html', 1, 1, 'CME_WEBSITE'),
('CL', 'Crude Oil Futures', 'https://www.cmegroup.com/markets/energy/crude-oil/light-sweet-crude.html', 1, 1, 'CME_WEBSITE')

ON DUPLICATE KEY UPDATE
    scraper_url = VALUES(scraper_url),
    active = VALUES(active),
    scraping_enabled = VALUES(scraping_enabled),
    updated_at = CURRENT_TIMESTAMP;

-- Vérification
SELECT 
    product_code,
    product_name,
    active,
    scraping_enabled,
    data_source
FROM cme_product_config 
ORDER BY product_code;