-- ========================================================================
-- EXEMPLE DE JOINTURES AVEC timestamp_datetime
-- ========================================================================
-- Démonstration de l'utilisation du nouveau champ timestamp_datetime
-- pour faciliter les JOIN avec d'autres tables
-- ========================================================================

-- EXEMPLE 1: Comparaison Nordpool vs EEX Power Futures (même timestamp UTC)
-- ========================================================================
SELECT 
    n.timestamp_datetime as time_utc,
    n.date_heure as time_fr,
    n.prix_eur_mwh as nordpool_spot_price,
    e.close as eex_future_price,
    e.pricesymbol as eex_contract,
    ROUND(ABS(n.prix_eur_mwh - e.close), 2) as price_difference
FROM appros_dm.dm_nordpool_spot n
LEFT JOIN appros_dm.dm_eex_power_future e 
    ON DATE(n.timestamp_datetime) = DATE(e.tradedatetimegmt)
    AND e.pricesymbol LIKE '%M1%' -- Contrat mensuel proche
WHERE n.timestamp_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND n.prix_eur_mwh IS NOT NULL
    AND e.close IS NOT NULL
ORDER BY n.timestamp_datetime DESC
LIMIT 20;


-- EXEMPLE 2: Analyse horaire spot vs futures (même heure UTC)
-- ========================================================================
SELECT 
    DATE(n.timestamp_datetime) as trade_date,
    HOUR(n.timestamp_datetime) as hour_utc,
    AVG(n.prix_eur_mwh) as avg_spot_price,
    COUNT(n.id) as nb_spot_records,
    AVG(e.close) as avg_future_price,
    COUNT(e.id) as nb_future_records
FROM appros_dm.dm_nordpool_spot n
LEFT JOIN appros_dm.dm_eex_power_future e 
    ON DATE(n.timestamp_datetime) = DATE(e.tradedatetimegmt)
    AND HOUR(n.timestamp_datetime) = HOUR(e.tradedatetimegmt)
WHERE n.timestamp_datetime >= DATE_SUB(NOW(), INTERVAL 3 DAY)
GROUP BY DATE(n.timestamp_datetime), HOUR(n.timestamp_datetime)
ORDER BY trade_date DESC, hour_utc DESC
LIMIT 50;


-- EXEMPLE 3: Comparaison CO2 Futures et prix spot électricité
-- ========================================================================
SELECT 
    DATE(n.timestamp_datetime) as trade_date,
    AVG(n.prix_eur_mwh) as avg_power_spot_eur_mwh,
    AVG(co2.close) as avg_co2_price_eur_t,
    COUNT(n.id) as nb_power_records,
    COUNT(co2.id) as nb_co2_records,
    ROUND(AVG(n.prix_eur_mwh) / NULLIF(AVG(co2.close), 0), 4) as power_to_co2_ratio
FROM appros_dm.dm_nordpool_spot n
LEFT JOIN appros_dm.dm_eex_co2_future co2
    ON DATE(n.timestamp_datetime) = DATE(co2.tradedatetimegmt)
WHERE n.timestamp_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND n.prix_eur_mwh IS NOT NULL
    AND co2.close IS NOT NULL
GROUP BY DATE(n.timestamp_datetime)
ORDER BY trade_date DESC
LIMIT 10;


-- EXEMPLE 4: Vue complète multi-markets (spot + futures + CO2)
-- ========================================================================
CREATE OR REPLACE VIEW v_energy_markets_comparison AS
SELECT 
    n.timestamp_datetime as utc_time,
    n.date_heure as fr_time,
    n.prix_eur_mwh as nordpool_spot,
    n.zone_livraison,
    epf.close as eex_power_future,
    epf.pricesymbol as power_contract,
    egf.close as eex_gas_future,
    egf.pricesymbol as gas_contract,
    co2.close as eex_co2_future,
    co2.pricesymbol as co2_contract,
    n.created_at
FROM appros_dm.dm_nordpool_spot n
LEFT JOIN appros_dm.dm_eex_power_future epf 
    ON DATE(n.timestamp_datetime) = DATE(epf.tradedatetimegmt)
    AND epf.pricesymbol LIKE '%M1%'
LEFT JOIN appros_dm.dm_eex_gas_future egf
    ON DATE(n.timestamp_datetime) = DATE(egf.tradedatetimegmt)
    AND egf.pricesymbol LIKE '%M1%'
LEFT JOIN appros_dm.dm_eex_co2_future co2
    ON DATE(n.timestamp_datetime) = DATE(co2.tradedatetimegmt)
    AND co2.pricesymbol = '/E.FEUA'
WHERE n.timestamp_datetime >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY n.timestamp_datetime DESC;

-- Utilisation de la vue
SELECT * FROM v_energy_markets_comparison LIMIT 20;


-- EXEMPLE 5: Statistiques quotidiennes multi-marchés
-- ========================================================================
SELECT 
    DATE(n.timestamp_datetime) as date_trading,
    ROUND(AVG(n.prix_eur_mwh), 2) as avg_spot_power,
    ROUND(MIN(n.prix_eur_mwh), 2) as min_spot_power,
    ROUND(MAX(n.prix_eur_mwh), 2) as max_spot_power,
    ROUND(AVG(epf.close), 2) as avg_future_power,
    ROUND(AVG(co2.close), 2) as avg_co2,
    COUNT(DISTINCT n.id) as nb_spot_records,
    COUNT(DISTINCT epf.id) as nb_power_future_records,
    COUNT(DISTINCT co2.id) as nb_co2_records
FROM appros_dm.dm_nordpool_spot n
LEFT JOIN appros_dm.dm_eex_power_future epf 
    ON DATE(n.timestamp_datetime) = DATE(epf.tradedatetimegmt)
LEFT JOIN appros_dm.dm_eex_co2_future co2
    ON DATE(n.timestamp_datetime) = DATE(co2.tradedatetimegmt)
WHERE n.timestamp_datetime >= DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP BY DATE(n.timestamp_datetime)
ORDER BY date_trading DESC;


-- EXEMPLE 6: Détection des écarts anormaux spot vs futures
-- ========================================================================
SELECT 
    n.timestamp_datetime,
    n.prix_eur_mwh as spot_price,
    epf.close as future_price,
    ABS(n.prix_eur_mwh - epf.close) as price_gap,
    ROUND((ABS(n.prix_eur_mwh - epf.close) / n.prix_eur_mwh) * 100, 2) as gap_percentage,
    CASE 
        WHEN ABS(n.prix_eur_mwh - epf.close) / n.prix_eur_mwh > 0.2 THEN '🔴 ALERTE: Écart >20%'
        WHEN ABS(n.prix_eur_mwh - epf.close) / n.prix_eur_mwh > 0.1 THEN '🟡 ATTENTION: Écart >10%'
        ELSE '🟢 Normal'
    END as status
FROM appros_dm.dm_nordpool_spot n
INNER JOIN appros_dm.dm_eex_power_future epf 
    ON DATE(n.timestamp_datetime) = DATE(epf.tradedatetimegmt)
    AND epf.pricesymbol LIKE '%M1%'
WHERE n.timestamp_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    AND n.prix_eur_mwh IS NOT NULL
    AND epf.close IS NOT NULL
    AND ABS(n.prix_eur_mwh - epf.close) > 10  -- Écart significatif
ORDER BY price_gap DESC
LIMIT 30;


-- ========================================================================
-- NOTES:
-- ========================================================================
-- 1. timestamp_datetime contient l'heure UTC originale (depuis timestamp_original)
-- 2. date_heure contient l'heure française (UTC+1 ou UTC+2 selon saison)
-- 3. Utiliser timestamp_datetime pour les JOIN entre différentes sources de données
-- 4. Les JOIN par DATE() permettent de comparer les données du même jour de trading
-- 5. Pour des JOIN plus précis, utiliser aussi HOUR() si nécessaire
-- ========================================================================