-- Script SQL pour créer une table de référence des symbols/codes utilisés dans le projet EEX
-- Basé sur l'analyse des fichiers importer.py, newsletter.py et backend.py

-- Création de la table de référence des symbols
CREATE TABLE IF NOT EXISTS eex_symbols_reference (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol_native VARCHAR(255) NOT NULL COMMENT 'Valeur native du symbol telle que définie dans le code',
    symbol_api_format VARCHAR(255) NOT NULL COMMENT 'Valeur formatée envoyée dans l\'API',
    symbol_stored_format VARCHAR(255) NOT NULL COMMENT 'Valeur stockée en base de données',
    symbol_type ENUM('FUTURES', 'SPOT', 'POWER_SPOT') NOT NULL,
    market_category ENUM('POWER', 'GAS', 'EUA', 'CO2') NOT NULL,
    country_code VARCHAR(10),
    country_name VARCHAR(50),
    product_type ENUM('BASE', 'PEAK', 'DAY_AHEAD', 'WEEKEND', 'NEXT_DAY') NULL,
    period_type ENUM('YEAR', 'QUARTER', 'MONTH', 'SEASON', 'DAILY') NULL,
    exchange_name VARCHAR(50),
    description_fr TEXT,
    description_en TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_native_symbol (symbol_native)
) ENGINE = InnoDB;

-- Index pour optimiser les recherches
CREATE INDEX idx_symbol_type ON eex_symbols_reference(symbol_type);
CREATE INDEX idx_market_category ON eex_symbols_reference(market_category);
CREATE INDEX idx_country_code ON eex_symbols_reference(country_code);
CREATE INDEX idx_product_period ON eex_symbols_reference(product_type, period_type);
CREATE INDEX idx_api_format ON eex_symbols_reference(symbol_api_format);
CREATE INDEX idx_stored_format ON eex_symbols_reference(symbol_stored_format);

-- Insertion des données basées sur l'analyse du code

-- 1. POWER FUTURES - France (Format stocké en BDD avec / initial, format API avec guillemets "/E.xxx")
INSERT INTO eex_symbols_reference (symbol_native, symbol_api_format, symbol_stored_format, symbol_type, market_category, country_code, country_name, product_type, period_type, exchange_name, description_fr, description_en) VALUES
('/E.F7BYF{year}', '"/E.F7BYF{year}"', '/E.F7BYF{year}', 'FUTURES', 'POWER', 'F7', 'France', 'BASE', 'YEAR', 'EEX', 'Futures électricité France Base - Année', 'France Power Base Load Futures - Year'),
('/E.F7BQF{year}', '"/E.F7BQF{year}"', '/E.F7BQF{year}', 'FUTURES', 'POWER', 'F7', 'France', 'BASE', 'QUARTER', 'EEX', 'Futures électricité France Base - Trimestre', 'France Power Base Load Futures - Quarter'),
('/E.F7BMF{year}', '"/E.F7BMF{year}"', '/E.F7BMF{year}', 'FUTURES', 'POWER', 'F7', 'France', 'BASE', 'MONTH', 'EEX', 'Futures électricité France Base - Mois', 'France Power Base Load Futures - Month'),
('/E.F7PYF{year}', '"/E.F7PYF{year}"', '/E.F7PYF{year}', 'FUTURES', 'POWER', 'F7', 'France', 'PEAK', 'YEAR', 'EEX', 'Futures électricité France Peak - Année', 'France Power Peak Load Futures - Year'),
('/E.F7PQF{year}', '"/E.F7PQF{year}"', '/E.F7PQF{year}', 'FUTURES', 'POWER', 'F7', 'France', 'PEAK', 'QUARTER', 'EEX', 'Futures électricité France Peak - Trimestre', 'France Power Peak Load Futures - Quarter'),
('/E.F7PMF{year}', '"/E.F7PMF{year}"', '/E.F7PMF{year}', 'FUTURES', 'POWER', 'F7', 'France', 'PEAK', 'MONTH', 'EEX', 'Futures électricité France Peak - Mois', 'France Power Peak Load Futures - Month');

-- 2. POWER FUTURES - Allemagne
INSERT INTO eex_symbols_reference (symbol_native, symbol_api_format, symbol_stored_format, symbol_type, market_category, country_code, country_name, product_type, period_type, exchange_name, description_fr, description_en) VALUES
('/E.DEBYF{year}', '"/E.DEBYF{year}"', '/E.DEBYF{year}', 'FUTURES', 'POWER', 'DE', 'Allemagne', 'BASE', 'YEAR', 'EEX', 'Futures électricité Allemagne Base - Année', 'Germany Power Base Load Futures - Year'),
('/E.DEBQF{year}', '"/E.DEBQF{year}"', '/E.DEBQF{year}', 'FUTURES', 'POWER', 'DE', 'Allemagne', 'BASE', 'QUARTER', 'EEX', 'Futures électricité Allemagne Base - Trimestre', 'Germany Power Base Load Futures - Quarter'),
('/E.DEBMF{year}', '"/E.DEBMF{year}"', '/E.DEBMF{year}', 'FUTURES', 'POWER', 'DE', 'Allemagne', 'BASE', 'MONTH', 'EEX', 'Futures électricité Allemagne Base - Mois', 'Germany Power Base Load Futures - Month'),
('/E.DEPYF{year}', '"/E.DEPYF{year}"', '/E.DEPYF{year}', 'FUTURES', 'POWER', 'DE', 'Allemagne', 'PEAK', 'YEAR', 'EEX', 'Futures électricité Allemagne Peak - Année', 'Germany Power Peak Load Futures - Year'),
('/E.DEPQF{year}', '"/E.DEPQF{year}"', '/E.DEPQF{year}', 'FUTURES', 'POWER', 'DE', 'Allemagne', 'PEAK', 'QUARTER', 'EEX', 'Futures électricité Allemagne Peak - Trimestre', 'Germany Power Peak Load Futures - Quarter'),
('/E.DEPMF{year}', '"/E.DEPMF{year}"', '/E.DEPMF{year}', 'FUTURES', 'POWER', 'DE', 'Allemagne', 'PEAK', 'MONTH', 'EEX', 'Futures électricité Allemagne Peak - Mois', 'Germany Power Peak Load Futures - Month');

-- 3. EUA/CO2 FUTURES
INSERT INTO eex_symbols_reference (symbol_native, symbol_api_format, symbol_stored_format, symbol_type, market_category, country_code, country_name, product_type, period_type, exchange_name, description_fr, description_en) VALUES
('/E.FEUA', '"/E.FEUA"', '/E.FEUA', 'FUTURES', 'EUA', 'EU', 'Union Européenne', NULL, NULL, 'EEX', 'Futures EUA (European Union Allowances)', 'EUA Futures (European Union Allowances)'),
('/E.FEUAZ{year}', '"/E.FEUAZ{year}"', '/E.FEUAZ{year}', 'FUTURES', 'CO2', 'EU', 'Union Européenne', NULL, 'YEAR', 'EEX', 'Futures CO2 EUA - Année', 'CO2 EUA Futures - Year');

-- 4. GAZ NATUREL FUTURES
INSERT INTO eex_symbols_reference (symbol_native, symbol_api_format, symbol_stored_format, symbol_type, market_category, country_code, country_name, product_type, period_type, exchange_name, description_fr, description_en) VALUES
('/E.G5BY{year}', '"/E.G5BY"', '/E.G5BY{year}', 'FUTURES', 'GAS', 'EU', 'Europe', NULL, 'YEAR', 'EEX', 'Futures Gaz Naturel - Année', 'Natural Gas Futures - Year'),
('/E.G5BQ{year}', '"/E.G5BQ"', '/E.G5BQ{year}', 'FUTURES', 'GAS', 'EU', 'Europe', NULL, 'QUARTER', 'EEX', 'Futures Gaz Naturel - Trimestre', 'Natural Gas Futures - Quarter'),
('/E.G5BM{year}', '"/E.G5BM"', '/E.G5BM{year}', 'FUTURES', 'GAS', 'EU', 'Europe', NULL, 'MONTH', 'EEX', 'Futures Gaz Naturel - Mois', 'Natural Gas Futures - Month'),
('/E.G5BS{year}', '"/E.G5BS"', '/E.G5BS{year}', 'FUTURES', 'GAS', 'EU', 'Europe', NULL, 'SEASON', 'EEX', 'Futures Gaz Naturel - Saison', 'Natural Gas Futures - Season');

-- 5. GAZ NATUREL SPOT (attention: format différent avec # et guillemets conservés en BDD)
INSERT INTO eex_symbols_reference (symbol_native, symbol_api_format, symbol_stored_format, symbol_type, market_category, country_code, country_name, product_type, period_type, exchange_name, description_fr, description_en) VALUES
('#E.CEGH_GND1', '"#E.CEGH_GND1"', '"#E.CEGH_GND1"', 'SPOT', 'GAS', 'AT', 'Autriche', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel CEGH - Next Day', 'CEGH Natural Gas - Next Day'),
('#E.CEGH_GWE1', '"#E.CEGH_GWE1"', '"#E.CEGH_GWE1"', 'SPOT', 'GAS', 'AT', 'Autriche', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel CEGH - Weekend', 'CEGH Natural Gas - Weekend'),
('#E.OTE_GSND', '"#E.OTE_GSND"', '"#E.OTE_GSND"', 'SPOT', 'GAS', 'CZ', 'République Tchèque', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel OTE - Next Day', 'OTE Natural Gas - Next Day'),
('#E.OTE_GSWE', '"#E.OTE_GSWE"', '"#E.OTE_GSWE"', 'SPOT', 'GAS', 'CZ', 'République Tchèque', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel OTE - Weekend', 'OTE Natural Gas - Weekend'),
('#E.ETF_GND1', '"#E.ETF_GND1"', '"#E.ETF_GND1"', 'SPOT', 'GAS', 'FR', 'France', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel ETF - Next Day', 'ETF Natural Gas - Next Day'),
('#E.ETF_GWE1', '"#E.ETF_GWE1"', '"#E.ETF_GWE1"', 'SPOT', 'GAS', 'FR', 'France', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel ETF - Weekend', 'ETF Natural Gas - Weekend'),
('#E.NBP_GPND', '"#E.NBP_GPND"', '"#E.NBP_GPND"', 'SPOT', 'GAS', 'GB', 'Royaume-Uni', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel NBP - Next Day', 'NBP Natural Gas - Next Day'),
('#E.NBP_GPWE', '"#E.NBP_GPWE"', '"#E.NBP_GPWE"', 'SPOT', 'GAS', 'GB', 'Royaume-Uni', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel NBP - Weekend', 'NBP Natural Gas - Weekend'),
('#E.PEG_GND1', '"#E.PEG_GND1"', '"#E.PEG_GND1"', 'SPOT', 'GAS', 'FR', 'France', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel PEG - Next Day', 'PEG Natural Gas - Next Day'),
('#E.PEG_GWE1', '"#E.PEG_GWE1"', '"#E.PEG_GWE1"', 'SPOT', 'GAS', 'FR', 'France', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel PEG - Weekend', 'PEG Natural Gas - Weekend'),
('#E.PVB_GSND', '"#E.PVB_GSND"', '"#E.PVB_GSND"', 'SPOT', 'GAS', 'ES', 'Espagne', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel PVB - Next Day', 'PVB Natural Gas - Next Day'),
('#E.PVB_GSWE', '"#E.PVB_GSWE"', '"#E.PVB_GSWE"', 'SPOT', 'GAS', 'ES', 'Espagne', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel PVB - Weekend', 'PVB Natural Gas - Weekend'),
('#E.THE_GND1', '"#E.THE_GND1"', '"#E.THE_GND1"', 'SPOT', 'GAS', 'DE', 'Allemagne', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel THE - Next Day', 'THE Natural Gas - Next Day'),
('#E.THE_GWE1', '"#E.THE_GWE1"', '"#E.THE_GWE1"', 'SPOT', 'GAS', 'DE', 'Allemagne', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel THE - Weekend', 'THE Natural Gas - Weekend'),
('#E.TTF_GND1', '"#E.TTF_GND1"', '"#E.TTF_GND1"', 'SPOT', 'GAS', 'NL', 'Pays-Bas', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel TTF - Next Day', 'TTF Natural Gas - Next Day'),
('#E.TTF_GWE1', '"#E.TTF_GWE1"', '"#E.TTF_GWE1"', 'SPOT', 'GAS', 'NL', 'Pays-Bas', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel TTF - Weekend', 'TTF Natural Gas - Weekend'),
('#E.ZTP_GTND', '"#E.ZTP_GTND"', '"#E.ZTP_GTND"', 'SPOT', 'GAS', 'IT', 'Italie', 'NEXT_DAY', 'DAILY', 'EEX', 'Gaz Naturel ZTP - Next Day', 'ZTP Natural Gas - Next Day'),
('#E.ZTP_GTWE', '"#E.ZTP_GTWE"', '"#E.ZTP_GTWE"', 'SPOT', 'GAS', 'IT', 'Italie', 'WEEKEND', 'DAILY', 'EEX', 'Gaz Naturel ZTP - Weekend', 'ZTP Natural Gas - Weekend');

-- 6. POWER SPOT (Nord Pool - format différent)
INSERT INTO eex_symbols_reference (symbol_native, symbol_api_format, symbol_stored_format, symbol_type, market_category, country_code, country_name, product_type, period_type, exchange_name, description_fr, description_en) VALUES
('FR', 'FR', 'FR', 'POWER_SPOT', 'POWER', 'FR', 'France', 'DAY_AHEAD', 'DAILY', 'Nord Pool', 'Électricité Spot France Day-Ahead', 'France Day-Ahead Power Spot');

-- Ajout d'une vue pour faciliter les requêtes avec les patterns d'années
CREATE VIEW v_eex_symbols_expanded AS
SELECT 
    symbol_native,
    symbol_api_format,
    symbol_stored_format,
    symbol_type,
    market_category,
    country_code,
    country_name,
    product_type,
    period_type,
    exchange_name,
    description_fr,
    description_en,
    CASE 
        WHEN symbol_native LIKE '%{year}%' THEN 'Pattern avec années dynamiques'
        ELSE 'Symbol fixe'
    END as symbol_nature
FROM eex_symbols_reference
WHERE is_active = TRUE;

-- Requêtes utiles pour l'analyse
-- Commandes pour visualiser les données par catégorie :

-- SELECT * FROM eex_symbols_reference WHERE market_category = 'POWER' ORDER BY country_code, product_type, period_type;
-- SELECT * FROM eex_symbols_reference WHERE market_category = 'GAS' AND symbol_type = 'SPOT' ORDER BY country_code;
-- SELECT * FROM eex_symbols_reference WHERE market_category IN ('EUA', 'CO2') ORDER BY symbol_code;
-- SELECT country_code, COUNT(*) as nb_symbols FROM eex_symbols_reference GROUP BY country_code ORDER BY nb_symbols DESC;
-- SELECT symbol_type, market_category, COUNT(*) as nb_symbols FROM eex_symbols_reference GROUP BY symbol_type, market_category;

-- Documentation des patterns utilisés :
-- {year} : Remplacé par les années (ex: 24, 25, 26, 27, 28, 29, 30, 31, 32, 33)
-- F7 : Code pays France
-- DE : Code pays Allemagne  
-- B : Base Load
-- P : Peak Load
-- Y : Year (Année)
-- Q : Quarter (Trimestre) 
-- M : Month (Mois)
-- S : Season (Saison)

-- FORMATS DÉTAILLÉS PAR TYPE :
-- ================================
-- 
-- 1. FUTURES EEX :
--    • Native dans le code : /E.F7PYF
--    • Envoyé à l'API : "/E.F7PYF" (ajout de guillemets)
--    • Retourné par l'API : /E.F7PYF25 (avec année ajoutée)
--    • Stocké en BDD : /E.F7PYF25
--
-- 2. SPOT EEX (Gaz) :
--    • Native dans le code : #E.TTF_GND1
--    • Envoyé à l'API : "#E.TTF_GND1" (ajout de guillemets)
--    • Retourné par l'API : Pas de symbol retourné (utilise param)
--    • Stocké en BDD : "#E.TTF_GND1" (avec guillemets)
--
-- 3. POWER SPOT (Nord Pool) :
--    • Native dans le code : FR (paramètre deliveryArea)
--    • Envoyé à l'API : FR (directement)
--    • Retourné par l'API : Pas de symbol (structure JSON différente)
--    • Stocké en BDD : FR (champ deliveryAreas)