#!/usr/bin/env python3
"""
Script de migration robuste pour mettre à jour la structure de la base de données
Vérifie la structure existante et ajoute les colonnes manquantes
"""

import sqlite3
import os
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def check_and_create_table(cursor):
    """Crée la table positions avec la structure complète si elle n'existe pas"""
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS positions (
            id TEXT PRIMARY KEY,
            symbol TEXT NOT NULL,
            side TEXT NOT NULL,
            size REAL NOT NULL,
            initial_quantity REAL DEFAULT 0.0,
            entry_price REAL NOT NULL,
            current_price REAL NOT NULL,
            pnl REAL DEFAULT 0.0,
            pnl_percent REAL DEFAULT 0.0,
            leverage INTEGER NOT NULL,
            stop_loss REAL NOT NULL,
            take_profit REAL NOT NULL,
            liquidation_price REAL,
            peak_price REAL DEFAULT 0.0,
            peak_pnl_percent REAL DEFAULT 0.0,
            trailing_stop_distance REAL DEFAULT 0.15,
            margin_used REAL NOT NULL,
            status TEXT DEFAULT 'ACTIVE',
            close_reason TEXT,
            open_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            close_timestamp DATETIME,
            signal_source TEXT,
            signal_confidence REAL,
            trading_mode TEXT DEFAULT 'SIMULATION',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)

def get_existing_columns(cursor):
    """Récupère la liste des colonnes existantes"""
    cursor.execute("PRAGMA table_info(positions)")
    return {column[1]: column[2] for column in cursor.fetchall()}

def add_missing_columns(cursor):
    """Ajoute les colonnes manquantes une par une"""
    existing_columns = get_existing_columns(cursor)
    
    # Définir les colonnes requises avec leurs types
    required_columns = {
        'pnl_percent': 'REAL DEFAULT 0.0',
        'peak_pnl_percent': 'REAL DEFAULT 0.0',
        'initial_quantity': 'REAL DEFAULT 0.0',
        'liquidation_price': 'REAL',
        'signal_source': 'TEXT',
        'signal_confidence': 'REAL',
        'trading_mode': 'TEXT DEFAULT "SIMULATION"',
        'close_reason': 'TEXT',
        'close_timestamp': 'DATETIME',
        'created_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP',
        'updated_at': 'DATETIME DEFAULT CURRENT_TIMESTAMP'
    }
    
    added_columns = []
    
    for column_name, column_def in required_columns.items():
        if column_name not in existing_columns:
            try:
                cursor.execute(f"ALTER TABLE positions ADD COLUMN {column_name} {column_def}")
                added_columns.append(column_name)
                logger.info(f"Ajouté colonne: {column_name}")
            except Exception as e:
                logger.warning(f"Impossible d'ajouter {column_name}: {e}")
    
    return added_columns

def update_initial_quantity(cursor):
    """Met à jour initial_quantity pour les positions existantes"""
    try:
        # Vérifier si initial_quantity existe et mettre à jour les valeurs nulles/zéro
        cursor.execute("""
            UPDATE positions 
            SET initial_quantity = size 
            WHERE initial_quantity IS NULL OR initial_quantity = 0.0
        """)
        updated = cursor.rowcount
        if updated > 0:
            logger.info(f"Mis à jour initial_quantity pour {updated} positions")
    except Exception as e:
        logger.warning(f"Erreur mise à jour initial_quantity: {e}")

def update_peak_pnl(cursor):
    """Met à jour peak_pnl_percent pour les positions existantes"""
    try:
        # Vérifier si les colonnes existent avant de faire la mise à jour
        existing_columns = get_existing_columns(cursor)
        
        if 'peak_pnl_percent' in existing_columns and 'pnl_percent' in existing_columns:
            cursor.execute("""
                UPDATE positions 
                SET peak_pnl_percent = CASE 
                    WHEN pnl_percent > peak_pnl_percent THEN pnl_percent 
                    WHEN peak_pnl_percent = 0.0 AND pnl_percent > 0 THEN pnl_percent
                    ELSE peak_pnl_percent
                END
                WHERE status = 'ACTIVE'
            """)
            updated = cursor.rowcount
            if updated > 0:
                logger.info(f"Mis à jour peak_pnl_percent pour {updated} positions")
        elif 'peak_pnl_percent' in existing_columns:
            # Juste initialiser à 0 si pnl_percent n'existe pas
            cursor.execute("""
                UPDATE positions 
                SET peak_pnl_percent = 0.0 
                WHERE peak_pnl_percent IS NULL
            """)
            logger.info("Initialisé peak_pnl_percent à 0.0")
    except Exception as e:
        logger.warning(f"Erreur mise à jour peak_pnl_percent: {e}")

def migrate_database():
    """Migration principale"""
    db_path = "crypto_trader.db"
    
    if not os.path.exists(db_path):
        logger.info(f"Base de données {db_path} n'existe pas - création d'une nouvelle")
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Vérifier si la table existe
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='positions'")
        table_exists = cursor.fetchone() is not None
        
        if not table_exists:
            logger.info("Création de la table positions...")
            check_and_create_table(cursor)
        else:
            logger.info("Table positions existe - vérification des colonnes...")
            existing_columns = get_existing_columns(cursor)
            logger.info(f"Colonnes existantes: {list(existing_columns.keys())}")
            
            # Ajouter les colonnes manquantes
            added = add_missing_columns(cursor)
            if added:
                logger.info(f"Colonnes ajoutées: {added}")
            else:
                logger.info("Toutes les colonnes requises sont présentes")
        
        # Mettre à jour les données
        update_initial_quantity(cursor)
        update_peak_pnl(cursor)
        
        conn.commit()
        logger.info("Migration terminée avec succès")
        
        # Afficher la structure finale
        final_columns = get_existing_columns(cursor)
        logger.info(f"Structure finale: {len(final_columns)} colonnes")
        
        conn.close()
        
    except Exception as e:
        logger.error(f"Erreur lors de la migration: {e}")
        if 'conn' in locals():
            conn.rollback()
            conn.close()
        raise

if __name__ == "__main__":
    migrate_database()