import sqlite3
import datetime
import math
from pathlib import Path
from typing import Dict, Any, List

DB_PATH = Path(__file__).parent / "flight_history.db"

class DatabaseManager:
    def __init__(self):
        self.init_db()

    def init_db(self):
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            # Table pour l'historique global des avions vus
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS flights (
                    hex_id TEXT PRIMARY KEY,
                    callsign TEXT,
                    first_seen TIMESTAMP,
                    last_seen TIMESTAMP,
                    max_altitude INTEGER,
                    max_speed REAL,
                    min_distance REAL,
                    min_altitude INTEGER,
                    min_3d_distance REAL
                )
            ''')
            try:
                cursor.execute("ALTER TABLE flights ADD COLUMN min_altitude INTEGER")
            except: pass
            try:
                cursor.execute("ALTER TABLE flights ADD COLUMN min_3d_distance REAL")
            except: pass
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS coverage (
                    bearing INTEGER PRIMARY KEY,
                    max_distance REAL
                )
            ''')
            conn.commit()

    def update_flight(self, aircraft: Any):
        """Met à jour ou insère un vol dans l'historique."""
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            
            now = datetime.datetime.now().isoformat()
            
            # Filtrer les altitudes aberrantes (ex: > 60000 ft ou < 0 ft)
            alt_val = aircraft.altitude
            if alt_val is not None and (alt_val > 60000 or alt_val < 0):
                alt_val = None
            
            dist_3d = None
            if aircraft.distance is not None and alt_val is not None:
                alt_km = alt_val * 0.0003048
                dist_3d = math.sqrt(aircraft.distance**2 + alt_km**2)
            elif aircraft.distance is not None:
                dist_3d = aircraft.distance
            
            cursor.execute("SELECT max_altitude, max_speed, min_distance, min_altitude, min_3d_distance, callsign FROM flights WHERE hex_id = ?", (aircraft.hex_id,))
            row = cursor.fetchone()
            
            if row:
                old_max_alt = row[0] or 0
                old_max_speed = row[1] or 0
                old_min_dist = row[2] if row[2] is not None else 999999
                old_min_alt = row[3] if row[3] is not None else 999999
                old_min_3d = row[4] if row[4] is not None else 999999
                old_callsign = row[5]
                
                new_max_alt = max(old_max_alt, alt_val or 0)
                new_max_speed = max(old_max_speed, aircraft.ground_speed or 0)
                new_min_dist = min(old_min_dist, aircraft.distance if aircraft.distance is not None else 999999)
                
                alt_val_min = alt_val if (alt_val is not None and alt_val > 0) else 999999
                new_min_alt = min(old_min_alt, alt_val_min)
                
                dist_3d_val = dist_3d if dist_3d is not None else 999999
                new_min_3d = min(old_min_3d, dist_3d_val)
                
                callsign = aircraft.callsign if aircraft.callsign != "N/A" else old_callsign
                
                cursor.execute('''
                    UPDATE flights 
                    SET callsign = ?, last_seen = ?, max_altitude = ?, max_speed = ?, min_distance = ?, min_altitude = ?, min_3d_distance = ?
                    WHERE hex_id = ?
                ''', (callsign, now, new_max_alt if new_max_alt > 0 else None, new_max_speed, new_min_dist, new_min_alt if new_min_alt < 999999 else None, new_min_3d if new_min_3d < 999999 else None, aircraft.hex_id))
            else:
                new_min_alt = alt_val if (alt_val is not None and alt_val > 0) else None
                cursor.execute('''
                    INSERT INTO flights (hex_id, callsign, first_seen, last_seen, max_altitude, max_speed, min_distance, min_altitude, min_3d_distance)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    aircraft.hex_id, 
                    aircraft.callsign, 
                    now, 
                    now, 
                    alt_val, 
                    aircraft.ground_speed,
                    aircraft.distance,
                    new_min_alt,
                    dist_3d
                ))
            
            if aircraft.distance and hasattr(aircraft, 'bearing') and aircraft.bearing is not None:
                b = int(aircraft.bearing)
                cursor.execute('''
                    INSERT INTO coverage (bearing, max_distance) VALUES (?, ?)
                    ON CONFLICT(bearing) DO UPDATE SET max_distance = MAX(max_distance, excluded.max_distance)
                ''', (b, aircraft.distance))
                
            conn.commit()

    def get_stats(self) -> Dict[str, Any]:
        """Récupère quelques statistiques globales."""
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM flights")
            total_unique = cursor.fetchone()[0]
            
            cursor.execute("SELECT MAX(max_altitude) FROM flights")
            record_alt = cursor.fetchone()[0]
            
            return {
                "total_unique_aircraft": total_unique,
                "record_altitude_ft": record_alt or 0
            }

    def get_recent_history(self, limit: int = 50) -> List[Dict[str, Any]]:
        """Récupère les derniers vols aperçus."""
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("""
                SELECT * FROM flights 
                WHERE (max_altitude IS NOT NULL AND max_altitude > 0)
                  AND (max_speed IS NOT NULL AND max_speed > 0)
                  AND (min_distance IS NOT NULL AND min_distance < 999999)
                ORDER BY last_seen DESC 
                LIMIT ?
            """, (limit,))
            rows = cursor.fetchall()
            return [dict(r) for r in rows]

    def get_coverage(self):
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT bearing, max_distance FROM coverage")
            return {row[0]: row[1] for row in cursor.fetchall()}

    def get_charts_data(self):
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT 
                    SUM(CASE WHEN max_altitude < 10000 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN max_altitude BETWEEN 10000 AND 20000 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN max_altitude BETWEEN 20000 AND 30000 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN max_altitude > 30000 THEN 1 ELSE 0 END)
                FROM flights
            """)
            alt_row = cursor.fetchone()
            altitudes = {
                "< 10k ft": alt_row[0] or 0, 
                "10k-20k ft": alt_row[1] or 0, 
                "20k-30k ft": alt_row[2] or 0, 
                "> 30k ft": alt_row[3] or 0
            }
            
            cursor.execute("SELECT date(first_seen), COUNT(*) FROM flights GROUP BY date(first_seen) ORDER BY date(first_seen) DESC LIMIT 7")
            dates = cursor.fetchall()
            activity = {r[0]: r[1] for r in reversed(dates)}
            
            cursor.execute("""
                SELECT SUBSTR(callsign, 1, 3) as airline, COUNT(*) as count 
                FROM flights 
                WHERE callsign IS NOT NULL AND length(callsign) >= 3 
                GROUP BY airline 
                ORDER BY count DESC 
                LIMIT 5
            """)
            airlines = {row[0]: row[1] for row in cursor.fetchall()}

            cursor.execute("""
                SELECT 
                    SUM(CASE WHEN min_distance < 50 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN min_distance BETWEEN 50 AND 150 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN min_distance BETWEEN 150 AND 250 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN min_distance > 250 THEN 1 ELSE 0 END)
                FROM flights
            """)
            dist_row = cursor.fetchone()
            distances = {
                "< 50 km": dist_row[0] or 0,
                "50-150 km": dist_row[1] or 0,
                "150-250 km": dist_row[2] or 0,
                "> 250 km": dist_row[3] or 0
            }
            
            return {
                "altitudes": altitudes, 
                "activity": activity,
                "airlines": airlines,
                "distances": distances
            }

    def get_achievements(self):
        with sqlite3.connect(DB_PATH) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            achievements = []
            
            cursor.execute("SELECT * FROM flights WHERE max_altitude IS NOT NULL ORDER BY max_altitude DESC LIMIT 1")
            alt_row = cursor.fetchone()
            if alt_row and alt_row['max_altitude'] and alt_row['max_altitude'] > 30000:
                achievements.append({
                    "id": "altitude",
                    "icon": "🌌" if alt_row['max_altitude'] > 40000 else "✈️",
                    "title": "Stratosphère" if alt_row['max_altitude'] > 40000 else "Croisière Haute",
                    "value": alt_row['max_altitude'],
                    "unit": "ft",
                    "hex": alt_row['hex_id'],
                    "callsign": alt_row['callsign'],
                    "date": alt_row['last_seen']
                })
                
            cursor.execute("SELECT * FROM flights WHERE max_speed IS NOT NULL ORDER BY max_speed DESC LIMIT 1")
            spd_row = cursor.fetchone()
            if spd_row and spd_row['max_speed'] and spd_row['max_speed'] > 300:
                achievements.append({
                    "id": "speed",
                    "icon": "⚡",
                    "title": "Mur du Son" if spd_row['max_speed'] > 540 else "Vitesse Élevée",
                    "value": spd_row['max_speed'],
                    "unit": "kt",
                    "hex": spd_row['hex_id'],
                    "callsign": spd_row['callsign'],
                    "date": spd_row['last_seen']
                })
                
            cursor.execute("SELECT * FROM flights WHERE min_distance IS NOT NULL ORDER BY min_distance ASC LIMIT 1")
            vert_row = cursor.fetchone()
            if vert_row and vert_row['min_distance'] is not None:
                achievements.append({
                    "id": "vertical",
                    "icon": "🎯",
                    "title": "À la Verticale",
                    "value": vert_row['min_distance'],
                    "unit": "km",
                    "hex": vert_row['hex_id'],
                    "callsign": vert_row['callsign'],
                    "date": vert_row['last_seen']
                })
                
            cursor.execute("SELECT * FROM flights WHERE min_altitude IS NOT NULL AND min_altitude > 0 ORDER BY min_altitude ASC LIMIT 1")
            low_row = cursor.fetchone()
            if low_row and low_row['min_altitude'] is not None:
                achievements.append({
                    "id": "low_alt",
                    "icon": "🚁",
                    "title": "Rase-motte",
                    "value": low_row['min_altitude'],
                    "unit": "ft",
                    "hex": low_row['hex_id'],
                    "callsign": low_row['callsign'],
                    "date": low_row['last_seen']
                })
                
            cursor.execute("SELECT * FROM flights WHERE min_3d_distance IS NOT NULL ORDER BY min_3d_distance ASC LIMIT 1")
            dist3d_row = cursor.fetchone()
            if dist3d_row and dist3d_row['min_3d_distance'] is not None:
                achievements.append({
                    "id": "dist_3d",
                    "icon": "📐",
                    "title": "Proximité 3D",
                    "value": dist3d_row['min_3d_distance'],
                    "unit": "km",
                    "hex": dist3d_row['hex_id'],
                    "callsign": dist3d_row['callsign'],
                    "date": dist3d_row['last_seen']
                })
                
            return achievements

    def cleanup_history(self, days: int) -> int:
        """Supprime les vols plus vieux que `days` jours, tout en gardant les records pour les trophées."""
        if days <= 0: return 0
        with sqlite3.connect(DB_PATH) as conn:
            cursor = conn.cursor()
            # On ne supprime pas les vols qui détiennent un record absolu (sinon on perd les trophées)
            # Cette requête supprime uniquement les vieux vols standards.
            cursor.execute("""
                DELETE FROM flights 
                WHERE last_seen < datetime('now', ?)
                AND max_altitude < (SELECT MAX(max_altitude) FROM flights)
                AND max_speed < (SELECT MAX(max_speed) FROM flights)
                AND min_distance > (SELECT MIN(min_distance) FROM flights)
                AND min_altitude > (SELECT MIN(min_altitude) FROM flights WHERE min_altitude > 0)
                AND min_3d_distance > (SELECT MIN(min_3d_distance) FROM flights)
            """, (f"-{days} days",))
            deleted = cursor.rowcount
            conn.commit()
            return deleted
