"""
Script de migration pour créer les tables manquantes dans la base de données
"""

import sys
import sqlite3
import os
from datetime import datetime

# Fix encoding for Windows console
sys.stdout.reconfigure(encoding='utf-8')

def create_database_tables():
    """Créer toutes les tables nécessaires pour le système de trading"""

    # Chemin vers la base de données (même que dans crypto_trader/config.py)
    db_path = "crypto_trader.db"

    # Créer la connexion
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    print(f"Connexion à la base de données: {db_path}")

    # 1. Table executed_signals - pour le tracking des signaux exécutés
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS executed_signals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            signal_id TEXT UNIQUE,
            symbol TEXT NOT NULL,
            signal_type TEXT NOT NULL,
            source TEXT,
            entry_price REAL NOT NULL,
            target_price REAL,
            stop_loss REAL,
            confidence REAL,
            reasoning TEXT,
            position_size REAL,
            quantity REAL,
            is_active INTEGER DEFAULT 1,
            current_price REAL,
            pnl_usd REAL,
            pnl_percent REAL,
            max_pnl REAL,
            min_pnl REAL,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            closed_at TIMESTAMP,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            timeframe TEXT,
            execution_method TEXT
        )
    ''')
    print("✓ Table 'executed_signals' créée/vérifiée")

    # 2. Table positions - pour le tracking des positions
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS positions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            position_id TEXT UNIQUE,
            symbol TEXT NOT NULL,
            side TEXT NOT NULL,
            entry_price REAL NOT NULL,
            size REAL NOT NULL,
            leverage INTEGER DEFAULT 1,
            stop_loss REAL,
            take_profit REAL,
            current_price REAL,
            pnl_usd REAL,
            pnl_percent REAL,
            peak_pnl_percent REAL,
            status TEXT DEFAULT 'OPEN',
            opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            closed_at TIMESTAMP,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            close_reason TEXT,
            trading_mode TEXT
        )
    ''')
    print("✓ Table 'positions' créée/vérifiée")

    # 3. Table trades_history - historique des trades
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS trades_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            trade_id TEXT UNIQUE,
            symbol TEXT NOT NULL,
            side TEXT NOT NULL,
            type TEXT NOT NULL,
            price REAL NOT NULL,
            size REAL NOT NULL,
            fee REAL,
            pnl REAL,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            position_id TEXT,
            execution_id TEXT,
            notes TEXT
        )
    ''')
    print("✓ Table 'trades_history' créée/vérifiée")

    # 4. Table signals_cache - cache des signaux générés
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS signals_cache (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            symbol TEXT NOT NULL,
            signal_type TEXT NOT NULL,
            source TEXT NOT NULL,
            confidence REAL,
            quality_score REAL,
            current_price REAL,
            target_price REAL,
            stop_loss REAL,
            leverage INTEGER DEFAULT 1,
            reasoning TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP,
            is_processed INTEGER DEFAULT 0
        )
    ''')
    print("✓ Table 'signals_cache' créée/vérifiée")

    # 5. Table system_state - état du système
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS system_state (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            key TEXT UNIQUE NOT NULL,
            value TEXT,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    print("✓ Table 'system_state' créée/vérifiée")

    # 6. Table profit_taker_actions - actions du ProfitTaker
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS profit_taker_actions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            action_type TEXT NOT NULL,
            symbol TEXT NOT NULL,
            side TEXT,
            entry_price REAL,
            exit_price REAL,
            pnl_percent REAL,
            peak_pnl_percent REAL,
            reason TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    print("✓ Table 'profit_taker_actions' créée/vérifiée")

    # 7. Créer les index pour optimiser les performances
    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_executed_signals_active
        ON executed_signals(is_active)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_positions_status
        ON positions(status)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_trades_history_timestamp
        ON trades_history(timestamp)
    ''')

    cursor.execute('''
        CREATE INDEX IF NOT EXISTS idx_signals_cache_processed
        ON signals_cache(is_processed)
    ''')

    print("✓ Index créés/vérifiés")

    # Commit et fermeture
    conn.commit()

    # Vérifier les tables créées
    cursor.execute("""
        SELECT name FROM sqlite_master
        WHERE type='table'
        ORDER BY name
    """)
    tables = cursor.fetchall()

    print("\n=== TABLES DANS LA BASE DE DONNEES ===")
    for table in tables:
        cursor.execute(f"SELECT COUNT(*) FROM {table[0]}")
        count = cursor.fetchone()[0]
        print(f"  - {table[0]}: {count} enregistrements")

    conn.close()
    print("\n✅ Migration terminée avec succès!")


def check_database():
    """Vérifier l'état de la base de données"""

    db_path = "crypto_trader.db"

    if not os.path.exists(db_path):
        print(f"Base de donnees '{db_path}' non trouvee - creation...")
        return False

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Vérifier si executed_signals existe
    cursor.execute("""
        SELECT name FROM sqlite_master
        WHERE type='table' AND name='executed_signals'
    """)

    result = cursor.fetchone()
    conn.close()

    if not result:
        print("⚠️ Table 'executed_signals' manquante")
        return False

    return True


if __name__ == "__main__":
    print("=== MIGRATION BASE DE DONNÉES TRADING ===")
    print(f"Date: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("-" * 40)

    # Vérifier l'état actuel
    if check_database():
        print("Base de données existante détectée")

    # Exécuter la migration
    create_database_tables()

    print("\nPour utiliser cette base de données:")
    print("1. La base de données 'crypto_trader.db' est configurée dans crypto_trader/config.py")
    print("2. Redémarrez le serveur unified_trading_server.py")