"""
Base de données SQLite pour la gestion des licences et abonnements
"""
import sqlite3
import secrets
from datetime import datetime, timezone
from pathlib import Path
from typing import Optional, Dict, Any
import logging

from ..subscription.models import SubscriptionTier, get_features_for_tier

logger = logging.getLogger(__name__)


class LicenseDatabase:
    """Gestionnaire de base de données pour les licences"""
    
    def __init__(self, db_path: Optional[Path] = None):
        """
        Initialise la connexion à la base de données
        
        Args:
            db_path: Chemin vers le fichier SQLite (défaut: ~/.web-sentinel/licenses.db)
        """
        if db_path is None:
            db_path = Path.home() / '.web-sentinel' / 'licenses.db'
        
        self.db_path = db_path
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        
        # Initialiser les tables
        self._init_database()
    
    def _init_database(self):
        """Crée les tables si elles n'existent pas"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # Table des licences
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS licenses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                api_key TEXT UNIQUE NOT NULL,
                email TEXT NOT NULL,
                stripe_customer_id TEXT,
                stripe_subscription_id TEXT,
                tier TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'active',
                max_domains INTEGER DEFAULT 10,
                max_users INTEGER DEFAULT 1,
                allow_source_scan INTEGER DEFAULT 0,
                max_source_files INTEGER DEFAULT 0,
                max_source_size_mb INTEGER DEFAULT 0,
                advanced_rules INTEGER DEFAULT 0,
                features TEXT,
                created_at TEXT NOT NULL,
                expires_at TEXT,
                last_used_at TEXT,
                usage_count INTEGER DEFAULT 0
            )
        ''')
        
        # Index pour recherches rapides
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_api_key ON licenses(api_key)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_email ON licenses(email)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_stripe_customer ON licenses(stripe_customer_id)')
        
        # Table d'historique des événements
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS license_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                license_id INTEGER NOT NULL,
                event_type TEXT NOT NULL,
                event_data TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (license_id) REFERENCES licenses (id)
            )
        ''')
        
        conn.commit()
        conn.close()
        
        logger.info(f"Base de données initialisée: {self.db_path}")

    @staticmethod
    def _sast_defaults_for_tier(tier: str) -> Dict[str, int]:
        try:
            tier_enum = SubscriptionTier(tier.lower())
        except ValueError:
            tier_enum = SubscriptionTier.FREE
        features = get_features_for_tier(tier_enum)
        return {
            "allow_source_scan": int(features.allow_source_scan),
            "max_source_files": features.max_source_files,
            "max_source_size_mb": features.max_source_size_mb,
            "advanced_rules": int(features.advanced_rules),
        }
    
    def generate_api_key(self) -> str:
        """
        Génère une clé API unique et sécurisée
        
        Format: ws_live_<32 caractères aléatoires>
        
        Returns:
            Clé API unique
        """
        return f"ws_live_{secrets.token_urlsafe(32)}"
    
    def create_license(
        self,
        email: str,
        tier: str,
        stripe_customer_id: Optional[str] = None,
        stripe_subscription_id: Optional[str] = None,
        max_domains: int = 10,
        max_users: int = 1,
        features: Optional[str] = None,
        expires_at: Optional[datetime] = None,
        allow_source_scan: Optional[bool] = None,
        max_source_files: Optional[int] = None,
        max_source_size_mb: Optional[int] = None,
        advanced_rules: Optional[bool] = None,
    ) -> Dict[str, Any]:
        """
        Crée une nouvelle licence
        
        Args:
            email: Email du propriétaire
            tier: Niveau d'abonnement (STARTER, PRO, ENTERPRISE)
            stripe_customer_id: ID client Stripe
            stripe_subscription_id: ID abonnement Stripe
            max_domains: Nombre max de domaines
            max_users: Nombre max d'utilisateurs
            features: Features JSON stringifié
            expires_at: Date d'expiration
            
        Returns:
            Dictionnaire avec les détails de la licence créée
        """
        api_key = self.generate_api_key()
        created_at = datetime.now(timezone.utc).isoformat()
        expires_str = expires_at.isoformat() if expires_at else None
        
        tier_label = tier.upper()
        sast_defaults = self._sast_defaults_for_tier(tier_label)
        sast_settings = {
            "allow_source_scan": int(allow_source_scan if allow_source_scan is not None else sast_defaults["allow_source_scan"]),
            "max_source_files": max_source_files if max_source_files is not None else sast_defaults["max_source_files"],
            "max_source_size_mb": max_source_size_mb if max_source_size_mb is not None else sast_defaults["max_source_size_mb"],
            "advanced_rules": int(advanced_rules if advanced_rules is not None else sast_defaults["advanced_rules"]),
        }

        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                INSERT INTO licenses (
                    api_key, email, stripe_customer_id, stripe_subscription_id,
                    tier, status, max_domains, max_users,
                    allow_source_scan, max_source_files, max_source_size_mb, advanced_rules,
                    features,
                    created_at, expires_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                api_key, email, stripe_customer_id, stripe_subscription_id,
                tier_label, 'active', max_domains, max_users,
                sast_settings["allow_source_scan"], sast_settings["max_source_files"],
                sast_settings["max_source_size_mb"], sast_settings["advanced_rules"],
                features,
                created_at, expires_str
            ))
            
            license_id = cursor.lastrowid
            
            # Enregistrer l'événement
            cursor.execute('''
                INSERT INTO license_events (license_id, event_type, event_data, created_at)
                VALUES (?, ?, ?, ?)
            ''', (license_id, 'license_created', f'Tier: {tier}', created_at))
            
            conn.commit()
            
            logger.info(f"Licence créée: {api_key} pour {email} (tier: {tier})")
            
            return {
                'id': license_id,
                'api_key': api_key,
                'email': email,
                'tier': tier_label,
                'status': 'active',
                'max_domains': max_domains,
                'max_users': max_users,
                'allow_source_scan': bool(sast_settings["allow_source_scan"]),
                'max_source_files': sast_settings["max_source_files"],
                'max_source_size_mb': sast_settings["max_source_size_mb"],
                'advanced_rules': bool(sast_settings["advanced_rules"]),
                'created_at': created_at,
                'expires_at': expires_str
            }
            
        except sqlite3.IntegrityError as e:
            conn.rollback()
            logger.error(f"Erreur création licence: {e}")
            raise
        finally:
            conn.close()
    
    def get_license_by_api_key(self, api_key: str) -> Optional[Dict[str, Any]]:
        """
        Récupère une licence par sa clé API
        
        Args:
            api_key: Clé API à rechercher
            
        Returns:
            Dictionnaire avec les détails de la licence ou None
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM licenses WHERE api_key = ?', (api_key,))
        row = cursor.fetchone()
        conn.close()
        
        if row:
            return dict(row)
        return None
    
    def get_license_by_email(self, email: str) -> Optional[Dict[str, Any]]:
        """
        Récupère la dernière licence active d'un email
        
        Args:
            email: Email à rechercher
            
        Returns:
            Dictionnaire avec les détails de la licence ou None
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM licenses 
            WHERE email = ? AND status = 'active'
            ORDER BY created_at DESC
            LIMIT 1
        ''', (email,))
        
        row = cursor.fetchone()
        conn.close()
        
        if row:
            return dict(row)
        return None
    
    def update_license_usage(self, api_key: str):
        """
        Met à jour les statistiques d'utilisation d'une licence
        
        Args:
            api_key: Clé API de la licence
        """
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        now = datetime.now(timezone.utc).isoformat()
        
        cursor.execute('''
            UPDATE licenses 
            SET last_used_at = ?, usage_count = usage_count + 1
            WHERE api_key = ?
        ''', (now, api_key))
        
        conn.commit()
        conn.close()
    
    def update_license_status(
        self, 
        api_key: str, 
        status: str,
        event_type: Optional[str] = None,
        event_data: Optional[str] = None
    ):
        """
        Met à jour le statut d'une licence
        
        Args:
            api_key: Clé API de la licence
            status: Nouveau statut (active, suspended, cancelled, expired)
            event_type: Type d'événement à enregistrer
            event_data: Données de l'événement
        """
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                UPDATE licenses 
                SET status = ?
                WHERE api_key = ?
            ''', (status, api_key))
            
            # Enregistrer l'événement si fourni
            if event_type:
                cursor.execute('SELECT id FROM licenses WHERE api_key = ?', (api_key,))
                license_id = cursor.fetchone()[0]
                
                now = datetime.now(timezone.utc).isoformat()
                cursor.execute('''
                    INSERT INTO license_events (license_id, event_type, event_data, created_at)
                    VALUES (?, ?, ?, ?)
                ''', (license_id, event_type, event_data or '', now))
            
            conn.commit()
            logger.info(f"Statut licence mis à jour: {api_key} -> {status}")
            
        finally:
            conn.close()
    
    def get_license_by_stripe_subscription(self, subscription_id: str) -> Optional[Dict[str, Any]]:
        """
        Récupère une licence par son ID d'abonnement Stripe
        
        Args:
            subscription_id: ID d'abonnement Stripe
            
        Returns:
            Dictionnaire avec les détails de la licence ou None
        """
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM licenses 
            WHERE stripe_subscription_id = ?
        ''', (subscription_id,))
        
        row = cursor.fetchone()
        conn.close()
        
        if row:
            return dict(row)
        return None
    
    def validate_license(self, api_key: str) -> Dict[str, Any]:
        """
        Valide une licence et retourne son statut détaillé
        
        Args:
            api_key: Clé API à valider
            
        Returns:
            Dictionnaire avec validation et détails
        """
        license_data = self.get_license_by_api_key(api_key)
        
        if not license_data:
            return {
                'valid': False,
                'error': 'API key invalide',
                'status': 'not_found'
            }
        
        # Vérifier le statut
        if license_data['status'] != 'active':
            return {
                'valid': False,
                'error': f"Licence {license_data['status']}",
                'status': license_data['status'],
                'tier': license_data['tier']
            }
        
        # Vérifier l'expiration
        if license_data['expires_at']:
            expires = datetime.fromisoformat(license_data['expires_at'])
            if expires < datetime.now(timezone.utc):
                # Marquer comme expirée
                self.update_license_status(api_key, 'expired', 'license_expired')
                return {
                    'valid': False,
                    'error': 'Licence expirée',
                    'status': 'expired',
                    'tier': license_data['tier']
                }
        
        # Mettre à jour l'usage
        self.update_license_usage(api_key)
        
        return {
            'valid': True,
            'status': 'active',
            'tier': license_data['tier'],
            'email': license_data['email'],
            'max_domains': license_data['max_domains'],
            'max_users': license_data['max_users'],
            'allow_source_scan': bool(license_data.get('allow_source_scan', 0)),
            'max_source_files': license_data.get('max_source_files', 0),
            'max_source_size_mb': license_data.get('max_source_size_mb', 0),
            'advanced_rules': bool(license_data.get('advanced_rules', 0)),
            'usage_count': license_data['usage_count'] + 1,
            'created_at': license_data['created_at']
        }