-- Table de référence des providers et leurs configurations
-- Centralise toutes les informations techniques pour chaque type de service

CREATE TABLE IF NOT EXISTS eex_v2_provider_reference (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(50) NOT NULL COMMENT 'Nom du provider: EPEX, EEX, Nordpool',
    product_type VARCHAR(50) NOT NULL COMMENT 'Type de produit: SPOT_POWER, POWER_FUTURE, GAS_FUTURE, etc.',
    
    -- Configuration de la table de données
    data_table VARCHAR(100) NOT NULL COMMENT 'Nom de la table où sont stockées les données',
    date_column VARCHAR(50) NOT NULL COMMENT 'Nom de la colonne de date dans la table',
    
    -- Configuration de filtrage
    filter_column VARCHAR(50) DEFAULT NULL COMMENT 'Colonne utilisée pour filtrer par produit/pays (market_area, Pays, pricesymbol, etc.)',
    filter_type ENUM('EXACT', 'LIKE', 'IN', 'NONE') DEFAULT 'EXACT' COMMENT 'Type de filtrage à appliquer',
    
    -- Configuration API/Scraping
    scraping_method ENUM('API', 'HTML', 'HYBRID') DEFAULT 'API',
    api_endpoint VARCHAR(255) DEFAULT NULL COMMENT 'URL de base de l\'API',
    api_type VARCHAR(50) DEFAULT NULL COMMENT 'Type d\'API: REST, SOAP, etc.',
    
    -- Métadonnées
    expected_records_per_day INT DEFAULT NULL COMMENT 'Nombre attendu de records par jour (approximatif)',
    description TEXT DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_provider_type (provider, product_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertion des configurations connues

-- EPEX SPOT POWER
INSERT INTO eex_v2_provider_reference 
(provider, product_type, data_table, date_column, filter_column, filter_type, scraping_method, api_endpoint, expected_records_per_day, description)
VALUES 
('EPEX', 'SPOT_POWER', 'scrapp_epex_v2', 'date_epex', 'market_area', 'EXACT', 'API', 'https://www.epexspot.com/en/market-data', 24, 'Données spot EPEX par market area (FR, BE, DE, etc.)');

-- EEX POWER FUTURES
INSERT INTO eex_v2_provider_reference 
(provider, product_type, data_table, date_column, filter_column, filter_type, scraping_method, api_endpoint, expected_records_per_day, description)
VALUES 
('EEX', 'POWER_FUTURE', 'eex_v2_futures', 'tradedatetimegmt', 'pricesymbol', 'LIKE', 'API', 'https://www.eex.com/en/market-data/power/futures', 50, 'Futures électricité EEX, filtrage par pricesymbol');

-- EEX GAS FUTURES
INSERT INTO eex_v2_provider_reference 
(provider, product_type, data_table, date_column, filter_column, filter_type, scraping_method, api_endpoint, expected_records_per_day, description)
VALUES 
('EEX', 'GAS_FUTURE', 'eex_v2_futures', 'tradedatetimegmt', 'pricesymbol', 'LIKE', 'API', 'https://www.eex.com/en/market-data/natural-gas/futures', 30, 'Futures gaz EEX, filtrage par pricesymbol');

-- EEX CO2 FUTURES (EUA)
INSERT INTO eex_v2_provider_reference 
(provider, product_type, data_table, date_column, filter_column, filter_type, scraping_method, api_endpoint, expected_records_per_day, description)
VALUES 
('EEX', 'CO2_FUTURE', 'eex_v2_futures', 'tradedatetimegmt', 'pricesymbol', 'LIKE', 'API', 'https://www.eex.com/en/market-data/environmental-markets/eua-primary-auction-spot', 20, 'Futures CO2/EUA, filtrage par pricesymbol');

-- EEX GAS SPOT
INSERT INTO eex_v2_provider_reference 
(provider, product_type, data_table, date_column, filter_column, filter_type, scraping_method, api_endpoint, expected_records_per_day, description)
VALUES 
('EEX', 'GAS_SPOT', 'eex_v2_spot', 'Date', 'Contrat', 'LIKE', 'API', 'https://www.eex.com/en/market-data/natural-gas/spot', 50, 'Spot gaz EEX, filtrage par Contrat');

-- NORDPOOL SPOT POWER
INSERT INTO eex_v2_provider_reference 
(provider, product_type, data_table, date_column, filter_column, filter_type, scraping_method, api_endpoint, expected_records_per_day, description)
VALUES 
('Nordpool', 'SPOT_POWER', 'eex_v2_power_spot', 'Date', 'Pays', 'EXACT', 'HTML', 'https://www.nordpoolgroup.com/Market-data1/Dayahead/Area-Prices/', 24, 'Spot électricité Nordpool par pays');

-- Vérification
SELECT * FROM eex_v2_provider_reference ORDER BY provider, product_type;
