"""
ENIGMA Inventario - Database Models
====================================

Models per gestione inventario con database MySQL.
Usa le tabelle esistenti:
- inv_sessioni_inventario
- inv_risultati_inventario
- inv_cache_prodotti

Author: HetGi & Claude
Date: 2026-01-14
"""

import pymysql
import json
from datetime import datetime
from typing import Optional, Dict, List, Any
import logging

logger = logging.getLogger(__name__)


class InventorySession:
    """Model per gestione sessioni inventario."""
    
    def __init__(self, db_config: Dict):
        """
        Inizializza model.
        
        Args:
            db_config: {host, user, password, database}
        """
        self.db_config = db_config
    
    def _get_connection(self):
        """Crea connessione database."""
        return pymysql.connect(
            host=self.db_config['host'],
            user=self.db_config['user'],
            password=self.db_config['password'],
            database=self.db_config['database'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def create_session(self, tenant_id: int, user_id: int, sede_id: int = 17117) -> Optional[int]:
        """
        Crea nuova sessione inventario.
        
        Args:
            tenant_id: ID tenant
            user_id: ID utente
            sede_id: ID sede (default 17117 - Sales Point principale)
        
        Returns:
            ID sessione creata, None se errore
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            # Verifica se c'è già una sessione aperta per questo utente
            cursor.execute("""
                SELECT id FROM inv_sessioni_inventario
                WHERE id_tenant = %s AND id_user = %s AND stato = 'aperta'
                LIMIT 1
            """, (tenant_id, user_id))
            
            existing = cursor.fetchone()
            if existing:
                logger.warning(f"Sessione già aperta per user {user_id}: {existing['id']}")
                return existing['id']
            
            # Crea nuova sessione
            sql = """
                INSERT INTO inv_sessioni_inventario (
                    id_tenant, id_user, id_sede, timestamp_inizio, stato
                ) VALUES (
                    %s, %s, %s, NOW(), 'aperta'
                )
            """
            cursor.execute(sql, (tenant_id, user_id, sede_id))
            conn.commit()
            
            session_id = cursor.lastrowid
            logger.info(f"Sessione creata: {session_id} per user {user_id}")
            return session_id
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore creazione sessione: {e}")
            return None
        finally:
            cursor.close()
            conn.close()
    
    def get_active_session(self, tenant_id: int, user_id: int) -> Optional[Dict]:
        """
        Ottiene sessione aperta corrente.
        
        Args:
            tenant_id: ID tenant
            user_id: ID utente
        
        Returns:
            Dati sessione o None
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                SELECT 
                    s.id, s.id_tenant, s.id_user, s.id_sede,
                    s.timestamp_inizio, s.stato, s.note, s.nome_sessione,
                    COUNT(DISTINCT r.id) as total_items,
                    SUM(r.quantita_fisica) as total_scans
                FROM inv_sessioni_inventario s
                LEFT JOIN inv_risultati_inventario r ON s.id = r.id_sessione
                WHERE s.id_tenant = %s AND s.id_user = %s AND s.stato = 'aperta'
                GROUP BY s.id
                ORDER BY s.timestamp_inizio DESC
                LIMIT 1
            """
            cursor.execute(sql, (tenant_id, user_id))
            return cursor.fetchone()
        finally:
            cursor.close()
            conn.close()
    
    def get_session(self, session_id: int, tenant_id: int) -> Optional[Dict]:
        """
        Ottiene dettagli sessione.
        
        Args:
            session_id: ID sessione
            tenant_id: ID tenant
        
        Returns:
            Dati sessione o None
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                SELECT 
                    s.*,
                    u.email as user_email,
                    u.nome as user_name,
                    COUNT(r.id) as items_count
                FROM inv_sessioni_inventario s
                LEFT JOIN tenant_users u ON s.id_user = u.id
                LEFT JOIN inv_risultati_inventario r ON s.id = r.id_sessione
                WHERE s.id = %s AND s.id_tenant = %s
                GROUP BY s.id
            """
            cursor.execute(sql, (session_id, tenant_id))
            return cursor.fetchone()
        finally:
            cursor.close()
            conn.close()
    
    def close_session(self, session_id: int, tenant_id: int, note: str = None) -> bool:
        """
        Chiude una sessione inventario.
        
        Args:
            session_id: ID sessione
            tenant_id: ID tenant
            note: Note opzionali
        
        Returns:
            True se successo, False altrimenti
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            # Conta items
            cursor.execute("""
                SELECT COUNT(DISTINCT id) as total_items, COUNT(id) as total_scans
                FROM inv_risultati_inventario
                WHERE id_sessione = %s
            """, (session_id,))
            
            counts = cursor.fetchone()
            total_items = counts['total_items'] if counts else 0
            total_scans = counts['total_scans'] if counts else 0
            
            # Chiudi sessione
            sql = """
                UPDATE inv_sessioni_inventario
                SET stato = 'chiusa',
                    timestamp_fine = NOW(),
                    total_items = %s,
                    total_scans = %s,
                    note = COALESCE(%s, note)
                WHERE id = %s AND id_tenant = %s AND stato = 'aperta'
            """
            cursor.execute(sql, (total_items, total_scans, note, session_id, tenant_id))
            conn.commit()
            
            affected = cursor.rowcount
            logger.info(f"Sessione {session_id} chiusa ({total_items} items)")
            return affected > 0
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore chiusura sessione: {e}")
            return False
        finally:
            cursor.close()
            conn.close()
    
    def get_recent_sessions(self, tenant_id: int, limit: int = 10) -> List[Dict]:
        """
        Ottiene sessioni recenti.
        
        Args:
            tenant_id: ID tenant
            limit: Numero massimo risultati
        
        Returns:
            Lista sessioni
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                SELECT 
                    s.*,
                    u.email as user_email,
                    u.nome as user_name,
                    COUNT(r.id) as items_count
                FROM inv_sessioni_inventario s
                LEFT JOIN tenant_users u ON s.id_user = u.id
                LEFT JOIN inv_risultati_inventario r ON s.id = r.id_sessione
                WHERE s.id_tenant = %s
                GROUP BY s.id
                ORDER BY s.timestamp_inizio DESC
                LIMIT %s
            """
            cursor.execute(sql, (tenant_id, limit))
            return cursor.fetchall()
        finally:
            cursor.close()
            conn.close()
    
    def update_session_name(self, session_id: int, tenant_id: int, name: str) -> bool:
        """
        Aggiorna nome/descrizione sessione.
        
        Args:
            session_id: ID sessione
            tenant_id: ID tenant
            name: Nome sessione
,        
        Returns:
            True se successo, False altrimenti
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                UPDATE inv_sessioni_inventario
                SET note = %s
                WHERE id = %s AND id_tenant = %s
            """
            cursor.execute(sql, (name, session_id, tenant_id))
            conn.commit()
            
            affected = cursor.rowcount
            logger.info(f"Nome sessione {session_id} aggiornato: {name}")
            return affected > 0
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore update nome sessione: {e}")
            return False
        finally:
            cursor.close()
            conn.close()


class InventoryItem:
    """Model per gestione item inventario."""
    
    def __init__(self, db_config: Dict):
        """
        Inizializza model.
        
        Args:
            db_config: {host, user, password, database}
        """
        self.db_config = db_config
    
    def _get_connection(self):
        """Crea connessione database."""
        return pymysql.connect(
            host=self.db_config['host'],
            user=self.db_config['user'],
            password=self.db_config['password'],
            database=self.db_config['database'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def add_item(self, session_id: int, tenant_id: int, barcode: str,
                 product_data: Optional[Dict] = None, found_in_api: bool = False,
                 quantita: int = 1, id_sede: int = None) -> Optional[int]:
        """
        Aggiunge item alla sessione inventario.
        
        Args:
            session_id: ID sessione
            tenant_id: ID tenant
            barcode: Codice a barre
            product_data: Dati prodotto da API (opzionale)
            found_in_api: Se trovato in Cassanova
            quantita: Quantità (default 1)
        
        Returns:
            ID item creato, None se errore
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            # Estrai dati dal product_data se disponibile
            codice_prodotto = None
            nome_prodotto = None
            categoria = None
            prezzo_pubblico = None
            
            if product_data:
                codice_prodotto = product_data.get('id')
                nome_prodotto = product_data.get('description') or product_data.get('name')
                categoria = product_data.get('category', {}).get('name') if isinstance(product_data.get('category'), dict) else None
                
                # Estrai prezzo dall'array prices[]
                prices = product_data.get('prices', [])
                if prices and len(prices) > 0:
                    prezzo_pubblico = prices[0].get('value')
                else:
                    prezzo_pubblico = None
            
            # Verifica se item con stesso barcode esiste già nella sessione
            cursor.execute("""
                SELECT id, quantita_fisica FROM inv_risultati_inventario
                WHERE id_sessione = %s AND barcode = %s
                LIMIT 1
            """, (session_id, barcode))
            
            existing = cursor.fetchone()
            
            if existing:
                # Aggiorna quantità
                new_quantity = existing['quantita_fisica'] + quantita
                cursor.execute("""
                    UPDATE inv_risultati_inventario
                    SET quantita_fisica = %s
                    WHERE id = %s
                """, (new_quantity, existing['id']))
                conn.commit()
                logger.info(f"Item aggiornato: {existing['id']}, nuova quantità: {new_quantity}")
                return existing['id']
            
            else:
                # Inserisci nuovo item
                sql = """
                    INSERT INTO inv_risultati_inventario (
                        id_tenant, id_sessione, id_sede, barcode, codice_prodotto,
                        product_data, found_in_api, manual_entry,
                        scanned_at, nome_prodotto, categoria, 
                        quantita_fisica, prezzo_pubblico
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, NOW(), %s, %s, %s, %s
                    )
                """
                
                product_json = json.dumps(product_data) if product_data else None
                manual_entry = not found_in_api
                
                cursor.execute(sql, (
                    tenant_id, session_id, id_sede, barcode, codice_prodotto,
                    product_json, found_in_api, manual_entry,
                    nome_prodotto, categoria, quantita, prezzo_pubblico
                ))
                conn.commit()
                
                item_id = cursor.lastrowid
                logger.info(f"Item creato: {item_id} per sessione {session_id}")
                return item_id
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore add_item: {e}")
            return None
        finally:
            cursor.close()
            conn.close()
    
    def get_session_items(self, session_id: int, tenant_id: int, id_sede: int = None) -> List[Dict]:
        """Ottiene items di una sessione filtrati per sede."""
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            if id_sede:
                sql = """
                    SELECT *
                    FROM inv_risultati_inventario
                    WHERE id_sessione = %s AND id_tenant = %s AND id_sede = %s
                    ORDER BY scanned_at DESC
                """
                cursor.execute(sql, (session_id, tenant_id, id_sede))
            else:
                sql = """
                    SELECT *
                    FROM inv_risultati_inventario
                    WHERE id_sessione = %s AND id_tenant = %s
                    ORDER BY scanned_at DESC
                """
                cursor.execute(sql, (session_id, tenant_id))
            return cursor.fetchall()
        finally:
            cursor.close()
            conn.close()##
    
    def delete_item(self, item_id: int, session_id: int) -> bool:
        """
        Elimina un item dalla sessione.
        
        Args:
            item_id: ID item
            session_id: ID sessione
        
        Returns:
            True se successo, False altrimenti
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                DELETE FROM inv_risultati_inventario
                WHERE id = %s AND id_sessione = %s
            """
            cursor.execute(sql, (item_id, session_id))
            conn.commit()
            
            affected = cursor.rowcount
            logger.info(f"Item {item_id} eliminato")
            return affected > 0
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore delete_item: {e}")
            return False
        finally:
            cursor.close()
            conn.close()
    
    def update_item_quantity(self, item_id: int, session_id: int, new_quantity: int) -> bool:
        """
        Modifica la quantità di un item esistente.
        
        Args:
            item_id: ID item
            session_id: ID sessione
            new_quantity: Nuova quantità
        
        Returns:
            True se successo, False altrimenti
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                UPDATE inv_risultati_inventario
                SET quantita_fisica = %s
                WHERE id = %s AND id_sessione = %s
            """
            cursor.execute(sql, (new_quantity, item_id, session_id))
            conn.commit()
            
            affected = cursor.rowcount
            logger.info(f"Item {item_id} aggiornato a quantità {new_quantity}")
            return affected > 0
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore update_item_quantity: {e}")
            return False
        finally:
            cursor.close()
            conn.close()


class ProductCache:
    """Model per cache prodotti da Cassanova."""
    
    def __init__(self, db_config: Dict):
        """
        Inizializza model.
        
        Args:
            db_config: {host, user, password, database}
        """
        self.db_config = db_config
    
    def _get_connection(self):
        """Crea connessione database."""
        return pymysql.connect(
            host=self.db_config['host'],
            user=self.db_config['user'],
            password=self.db_config['password'],
            database=self.db_config['database'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
    
    def cache_product(self, tenant_id: int, barcode: str, 
                     product_data: Dict, ttl: int = None) -> bool:
        """
        Salva prodotto in cache.
        
        Args:
            tenant_id: ID tenant
            barcode: Codice a barre
            product_data: Dati prodotto da Cassanova
            ttl: Time-to-live in secondi (default 24h)
        
        Returns:
            True se successo, False altrimenti
        """
        # Se ttl non è passato, leggi da .env
        if ttl is None:
            import os
            ttl = int(os.getenv('CACHE_TTL_SECONDS', 604800))  # default 7 giorni
	
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                INSERT INTO inv_cache_prodotti (
                    id_tenant, codice_barcode, dati_cassanova, 
                    cache_timestamp, ttl
                ) VALUES (
                    %s, %s, %s, NOW(), %s
                )
                ON DUPLICATE KEY UPDATE
                    dati_cassanova = VALUES(dati_cassanova),
                    cache_timestamp = NOW(),
                    ttl = VALUES(ttl)
            """
            
            product_json = json.dumps(product_data)
            cursor.execute(sql, (tenant_id, barcode, product_json, ttl))
            conn.commit()
            
            logger.debug(f"Prodotto {barcode} salvato in cache")
            return True
            
        except Exception as e:
            conn.rollback()
            logger.error(f"Errore cache_product: {e}")
            return False
        finally:
            cursor.close()
            conn.close()
    
    def get_cached_product(self, tenant_id: int, barcode: str) -> Optional[Dict]:
        """
        Recupera prodotto dalla cache.
        
        Args:
            tenant_id: ID tenant
            barcode: Codice a barre
        
        Returns:
            Dati prodotto o None se non in cache o scaduto
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            sql = """
                SELECT dati_cassanova, cache_timestamp, ttl
                FROM inv_cache_prodotti
                WHERE id_tenant = %s AND codice_barcode = %s
                AND TIMESTAMPDIFF(SECOND, cache_timestamp, NOW()) < ttl
            """
            cursor.execute(sql, (tenant_id, barcode))
            result = cursor.fetchone()
            
            if result and result['dati_cassanova']:
                return json.loads(result['dati_cassanova'])
            return None
            
        except Exception as e:
            logger.error(f"Errore get_cached_product: {e}")
            return None
        finally:
            cursor.close()
            conn.close()
    
    def search_products_local(self, tenant_id: int, query: str, limit: int = 20) -> List[Dict]:
        """
        Cerca prodotti nella cache locale per nome.
        
        Args:
            tenant_id: ID tenant
            query: Testo da cercare (minimo 3 caratteri)
            limit: Numero massimo risultati
        
        Returns:
            Lista prodotti trovati
        """
        conn = self._get_connection()
        cursor = conn.cursor()
        
        try:
            # Validazione query
            #non cerco se ho meno di tre caratteri
            if len(query) < 3:
                return []
            
            # Ricerca con LIKE su JSON (funziona ma non è ottimale)
            # Alternativa: full-text search se necessario
            sql = """
                SELECT codice_barcode, dati_cassanova
                FROM inv_cache_prodotti
                WHERE id_tenant = %s
                AND (
                    dati_cassanova LIKE %s
                    OR codice_barcode LIKE %s
                )
                LIMIT %s
            """
            
            search_pattern = f"%{query}%"
            cursor.execute(sql, (tenant_id, search_pattern, search_pattern, limit))
            results = cursor.fetchall()
            
            # Parse JSON e ritorna
            products = []
            for row in results:
                if row['dati_cassanova']:
                    try:
                        product = json.loads(row['dati_cassanova'])
                        products.append(product)
                    except json.JSONDecodeError:
                        logger.error(f"JSON invalido per barcode {row['codice_barcode']}")
            
            logger.info(f"Trovati {len(products)} prodotti per query: {query}")
            return products
            
        except Exception as e:
            logger.error(f"Errore search_products_local: {e}")
            return []
        finally:
            cursor.close()
            conn.close()