-- Requêtes utiles pour explorer les données ADS-B
-- Base : wpz_postgres, schéma : adsb

-- Nombre de positions par jour (7 derniers jours)
SELECT
    DATE_TRUNC('day', ts AT TIME ZONE 'Europe/Paris') AS jour,
    COUNT(*) AS nb_positions,
    COUNT(DISTINCT icao) AS nb_avions
FROM adsb.positions
WHERE ts >= NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1 DESC;

-- Avions les plus vus aujourd'hui
SELECT
    icao,
    MAX(callsign) AS callsign,
    COUNT(*) AS nb_positions,
    MIN(altitude) AS alt_min,
    MAX(altitude) AS alt_max,
    ROUND(AVG(speed)) AS vitesse_moy,
    MIN(ts) AS premiere_vue,
    MAX(ts) AS derniere_vue
FROM adsb.positions
WHERE ts >= DATE_TRUNC('day', NOW() AT TIME ZONE 'Europe/Paris')
GROUP BY icao
ORDER BY nb_positions DESC
LIMIT 20;

-- Trajectoire d'un avion spécifique (remplacer l'ICAO)
SELECT ts, callsign, altitude, speed, track, lat, lon, vrate
FROM adsb.positions
WHERE icao = '440AF4'
  AND ts >= NOW() - INTERVAL '24 hours'
ORDER BY ts;

-- Avions en dessous de 5000 ft (approche Orly/CDG)
SELECT DISTINCT ON (icao)
    icao, callsign, altitude, speed, lat, lon, ts
FROM adsb.positions
WHERE ts >= NOW() - INTERVAL '1 hour'
  AND altitude IS NOT NULL
  AND altitude < 5000
ORDER BY icao, ts DESC;

-- Statistiques globales
SELECT
    COUNT(*) AS total_positions,
    COUNT(DISTINCT icao) AS avions_distincts,
    MIN(ts) AS depuis,
    MAX(ts) AS jusqua,
    ROUND(AVG(altitude)) AS altitude_moyenne,
    ROUND(AVG(speed)) AS vitesse_moyenne
FROM adsb.positions;

-- Taille des partitions
SELECT
    c.relname AS partition,
    pg_size_pretty(pg_relation_size(c.oid)) AS taille,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS taille_totale
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'adsb'
  AND c.relkind = 'r'
ORDER BY pg_relation_size(c.oid) DESC;