"""
ENIGMA Inventario - API Routes FIXED
=====================================

Endpoints REST completi per inventario.

Author: HetGi & Claude  
Date: 2026-01-14 (FIXED VERSION)
"""

from flask import Blueprint, request, jsonify
from functools import wraps
from datetime import datetime
import logging
import os

logger = logging.getLogger(__name__)

inventory_bp = Blueprint('inventory', __name__, url_prefix='/api/v1/inventory')


def require_auth(f):
    """Decorator auth (mock per demo)."""
    @wraps(f)
    def decorated(*args, **kwargs):
        request.user = {
            'id': 1,
            'tenant_id': 1,
            'email': 'demo@studium.it',
            'role': 'admin'
        }
        return f(*args, **kwargs)
    return decorated


def get_service():
    """Factory per InventoryService."""
    from app.services.inventory_service import create_inventory_service
    
    db_config = {
        'host': os.getenv('DB_HOST', 'localhost'),
        'user': os.getenv('DB_USER', 'enigma_user'),
        'password': os.getenv('DB_PASSWORD', 'arcana2026'),
        'database': os.getenv('DB_NAME', 'enigma')
    }
    
    # IMPORTANTE: Usa SOLO la chiave dal .env, senza default!
    api_key = os.getenv('CASSANOVA_API_KEY')
    if not api_key:
        raise ValueError("CASSANOVA_API_KEY non trovata nel .env!")
    
    return create_inventory_service(db_config, api_key)


# ============================================================================
# SALES POINTS (SEDI)
# ============================================================================

@inventory_bp.route('/salespoints', methods=['GET'])
@require_auth
def get_salespoints():
    """Recupera Sales Points filtrati per ruolo dell'utente."""
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        user_id = request.user['id']
        user_role = request.user.get('ruolo', 'operatore')
        
        print(f"DEBUG: user_id={user_id}, user_role={user_role}, tenant_id={tenant_id}")
        
        import pymysql
        conn = pymysql.connect(**service.session_model.db_config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # Se manager o admin, vede tutte le sedi. Se operatore, solo la sua
        if user_role in ('manager', 'admin'):
            # Manager/Admin vede tutto
            sql = """
                SELECT 
                    s.id,
                    s.nome as name,
                    s.indirizzo as address,
                    s.città as city,
                    COUNT(DISTINCT p.codice_barcode) as product_count
                FROM inv_sedi s
                LEFT JOIN inv_cache_prodotti p ON p.id_tenant = %s
                    AND JSON_SEARCH(p.dati_cassanova, 'one', s.id, NULL, '$.prices[*].idSalesPoint') IS NOT NULL
                WHERE s.id_tenant = %s
                GROUP BY s.id
                ORDER BY s.nome
            """
            cursor.execute(sql, (tenant_id, tenant_id))
        else:
            # Operatore vede solo le sue sedi
            sql = """
                SELECT 
                    s.id,
                    s.nome as name,
                    s.indirizzo as address,
                    s.città as city,
                    COUNT(DISTINCT p.codice_barcode) as product_count
                FROM inv_sedi s
                LEFT JOIN inv_cache_prodotti p ON p.id_tenant = %s
                    AND JSON_SEARCH(p.dati_cassanova, 'one', s.id, NULL, '$.prices[*].idSalesPoint') IS NOT NULL
                INNER JOIN user_salespoints us ON s.id = us.id_salespoint
                INNER JOIN tenant_users tu ON us.id_user = tu.id
                WHERE s.id_tenant = %s AND tu.id = %s
                GROUP BY s.id
                ORDER BY s.nome
            """
            cursor.execute(sql, (tenant_id, tenant_id, user_id))
        
        salespoints = cursor.fetchall()
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {
                'salespoints': salespoints
            }
        }), 200
    except Exception as e:
        logger.error(f"Errore get salespoints: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


# ============================================================================
# SINCRONIZZAZIONE PRODOTTI
# ============================================================================

@inventory_bp.route('/products/sync', methods=['POST'])
@require_auth
def sync_products():
    """
    Sincronizza TUTTI i prodotti da Cassanova.
    
    Returns:
        {
            "success": true,
            "data": {
                "total": 1234,
                "cached": 1230,
                "errors": 4
            }
        }
    """
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        
        result = service.sync_all_products(tenant_id)
        
        return jsonify({
            'success': True,
            'data': result
        }), 200
    except Exception as e:
        logger.error(f"Errore sync: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


# ============================================================================
# INFORMAZIONI SULLA CACHE CORRENTE
# ============================================================================

@inventory_bp.route('/cache/info', methods=['GET'])
@require_auth
def get_cache_info():
    """Ritorna info sulla cache locale."""
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        
        import pymysql
        conn = pymysql.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'enigma_user'),
            password=os.getenv('DB_PASSWORD', 'arcana2026'),
            database=os.getenv('DB_NAME', 'enigma'),
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        cursor = conn.cursor()
        
        # Conta prodotti in cache
        cursor.execute("""
            SELECT COUNT(*) as total, MAX(cache_timestamp) as last_sync
            FROM inv_cache_prodotti
            WHERE id_tenant = %s
        """, (tenant_id,))
        
        result = cursor.fetchone()
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {
                'product_count': result['total'] or 0,
                'last_sync': result['last_sync'].isoformat() if result['last_sync'] else None
            }
        }), 200
    except Exception as e:
        logger.error(f"Errore get cache info: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

# ============================================================================
# RICERCA PRODOTTI
# ============================================================================

@inventory_bp.route('/products/search', methods=['GET'])
@require_auth
def search_products():
    """
    Cerca prodotti per barcode o nome.
    
    Query params:
        - barcode: Codice a barre
        - description: Nome prodotto
    
    Returns:
        {
            "success": true,
            "data": {
                "products": [...]
            }
        }
    """
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        
        barcode = request.args.get('barcode')
        description = request.args.get('description')
        
        if not barcode and not description:
            return jsonify({
                'success': False,
                'error': 'Specificare barcode o description'
            }), 400
        
        products = service.search_product(tenant_id, barcode, description)
        
        return jsonify({
            'success': True,
            'data': {
                'products': products,
                'count': len(products)
            }
        }), 200
    except Exception as e:
        logger.error(f"Errore ricerca: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


# ============================================================================
# SESSIONI INVENTARIO
# ============================================================================

@inventory_bp.route('/session/start', methods=['POST'])
@require_auth
def start_session():
    """
    Avvia nuova sessione inventario.
    
    Body:
        {
            "sede_id": 17117,
            "session_name": "Libreria Piano 1"
        }
    
    Returns:
        {
            "success": true,
            "data": {
                "session_id": 123,
                "session": {...}
            }
        }
    """
    try:
        service = get_service()
        user = request.user
        
        data = request.get_json(silent=True) or {}
        sede_id = data.get('sede_id', 17117)
        session_name = data.get('session_name', f'Inventario {datetime.now().strftime("%d/%m/%Y")}')
        
        # Crea sessione
        session_id = service.start_session(user['tenant_id'], user['id'], sede_id)
        
        # DEBUG
        import sys
        logger.info(f"Service type: {type(service)}")
        logger.info(f"Service has update_session_name: {hasattr(service, 'update_session_name')}")
        if hasattr(service, 'update_session_name'):
            logger.info(f"Method: {service.update_session_name}")
        else:
            logger.error(f"Methods available: {[m for m in dir(service) if not m.startswith('_')]}")
        sys.stdout.flush()
        
        if session_id:
            # Aggiorna con nome sessione
            service.update_session_name(session_id, user['tenant_id'], session_name)
            
            # Recupera dettagli
            session_data = service.get_session_details(session_id, user['tenant_id'])
            
            return jsonify({
                'success': True,
                'data': {
                    'session_id': session_id,
                    'session': session_data
                }
            }), 201
        else:
            return jsonify({
                'success': False,
                'error': 'Sessione già aperta o errore creazione'
            }), 400
    except Exception as e:
        logger.error(f"Errore start: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


@inventory_bp.route('/session/current', methods=['GET'])
@require_auth
def get_current_session():
    """Ottiene sessione corrente."""
    try:
        service = get_service()
        user = request.user
        
        session_data = service.get_current_session(user['tenant_id'], user['id'])
        
        if session_data:
            #aggiunto items
            items = service.get_session_items(session_data['id'], user['tenant_id'])
            session_data['items'] = items  # ← NUOVO
            
            return jsonify({
                'success': True,
                'data': {'session': session_data}
            }), 200
        else:
            return jsonify({
                'success': False,
                'error': 'Nessuna sessione aperta'
            }), 200
    except Exception as e:
        logger.error(f"Errore get current: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


@inventory_bp.route('/session/close', methods=['POST'])
@require_auth
def close_session():
    """
    Chiude sessione corrente.
    
    Body (opzionale):
        {"note": "Inventario completato"}
    
    Returns:
        {"success": true}
    """
    try:
        service = get_service()
        user = request.user
        
        # Ottieni sessione corrente
        current = service.get_current_session(user['tenant_id'], user['id'])
        if not current:
            return jsonify({
                'success': False,
                'error': 'Nessuna sessione aperta'
            }), 400
        
        data = request.get_json(silent=True) or {}
        note = data.get('note')
        
        success = service.close_session(current['id'], user['tenant_id'], note)
        
        if success:
            return jsonify({
                'success': True,
                'message': 'Sessione chiusa'
            }), 200
        else:
            return jsonify({
                'success': False,
                'error': 'Errore chiusura sessione'
            }), 500
    except Exception as e:
        logger.error(f"Errore close: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


@inventory_bp.route('/sessions/recent', methods=['GET'])
@require_auth
def get_recent_sessions():
    """Lista sessioni recenti, opzionalmente filtrate per punto vendita."""
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        limit = int(request.args.get('limit', 10))
        salespoint_id = request.args.get('salespoint_id')  # ← AGGIUNGI QUESTA RIGA
        
        import pymysql
        db_config = service.session_model.db_config
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # Query con filtro opzionale
        sql = """
            SELECT * FROM inv_sessioni_inventario
            WHERE id_tenant = %s
        """
        params = [tenant_id]
        
        if salespoint_id:
            sql += " AND id_sede = %s"
            params.append(int(salespoint_id))
        
        sql += " ORDER BY timestamp_inizio DESC LIMIT %s"
        params.append(limit)
        
        cursor.execute(sql, params)
        sessions = cursor.fetchall()
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {
                'sessions': sessions,
                'count': len(sessions)
            }
        }), 200
    except Exception as e:
        logger.error(f"Errore get recent: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


@inventory_bp.route('/session/<int:session_id>', methods=['GET'])
@require_auth
def get_session_detail(session_id):
    """Dettaglio sessione con items."""
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        
        session_data = service.get_session_details(session_id, tenant_id)
        items = service.get_session_items(session_id, tenant_id)
        
        return jsonify({
            'success': True,
            'data': {
                'session': session_data,
                'items': items,
                'items_count': len(items)
            }
        }), 200
    except Exception as e:
        logger.error(f"Errore get detail: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


# ============================================================================
# SCANSIONE BARCODE
# ============================================================================

@inventory_bp.route('/scan', methods=['POST'])
@require_auth
def scan_barcode():
    """
    Scansiona barcode e aggiunge a sessione.
    
    Body:
        {
            "barcode": "8001234567890",
            "quantita": 1  // opzionale
        }
    
    Returns:
        {
            "success": true,
            "data": {
                "item_id": 456,
                "found_in_api": true,
                "product": {...}
            }
        }
    """
    try:
        service = get_service()
        user = request.user
        
        ##inizio
        data = request.get_json()
        if not data or 'barcode' not in data:
            return jsonify({
                'success': False,
                'error': 'Campo barcode richiesto'
            }), 400
        
        barcode = data['barcode'].strip()
        quantita = data.get('quantita', 1)
        
        # ✅ LEGGI product_data dal frontend (se disponibile)
        product_data = data.get('product_data')
        
        # Se il frontend non ha mandato product_data, cerca in cache/API
        if not product_data:
            products = service.search_product(user['tenant_id'], barcode=barcode)
            product_data = products[0] if products else None
        
        found_in_api = bool(product_data)
        
        # Ottieni sessione corrente
        current = service.get_current_session(user['tenant_id'], user['id'])
        if not current:
            return jsonify({
                'success': False,
                'error': 'Nessuna sessione aperta. Avvia prima una sessione.'
            }), 400
        
        # Aggiungi a sessione
        item_id = service.add_item(
            current['id'],
            user['tenant_id'],
            barcode,
            product_data,
            found_in_api,
            quantita
        )
        
        if item_id:
            response_data = {
                'item_id': item_id,
                'barcode': barcode,
                'found_in_api': found_in_api
            }
            if product_data:
                response_data['product'] = product_data
            
            message = 'Prodotto trovato e aggiunto' if found_in_api else 'Barcode registrato (non trovato)'
            
            return jsonify({
                'success': True,
                'data': response_data,
                'message': message
            }), 201
        else:
            return jsonify({
                'success': False,
                'error': 'Errore aggiunta item'
            }), 500
    except Exception as e:
        logger.error(f"Errore scan: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


@inventory_bp.route('/item/<int:item_id>', methods=['DELETE'])
@require_auth
def delete_item(item_id):
    """Elimina item da sessione."""
    try:
        service = get_service()
        user = request.user
        
        # Ottieni sessione corrente
        current = service.get_current_session(user['tenant_id'], user['id'])
        if not current:
            return jsonify({
                'success': False,
                'error': 'Nessuna sessione aperta'
            }), 400
        
        success = service.delete_item(item_id, current['id'])
        
        if success:
            return jsonify({
                'success': True,
                'message': 'Item eliminato'
            }), 200
        else:
            return jsonify({
                'success': False,
                'error': 'Item non trovato o errore'
            }), 404
    except Exception as e:
        logger.error(f"Errore delete: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

@inventory_bp.route('/item/<int:item_id>/quantity', methods=['PUT'])
@require_auth
def update_item_quantity(item_id):
    """Modifica quantità di un item."""
    try:
        service = get_service()
        user = request.user
        
        # Ottieni sessione corrente
        current = service.get_current_session(user['tenant_id'], user['id'])
        if not current:
            return jsonify({
                'success': False,
                'error': 'Nessuna sessione aperta'
            }), 400
        
        # Leggi nuova quantità dal body
        data = request.get_json()
        new_quantity = data.get('quantity')
        
        if new_quantity is None or new_quantity < 0:
            return jsonify({
                'success': False,
                'error': 'Quantità non valida'
            }), 400
        
        # Se quantità = 0, elimina l'item
        if new_quantity == 0:
            success = service.delete_item(item_id, current['id'])
            message = 'Item eliminato (quantità 0)'
        else:
            success = service.update_item_quantity(item_id, current['id'], new_quantity)
            message = f'Quantità aggiornata a {new_quantity}'
        
        if success:
            return jsonify({
                'success': True,
                'message': message,
                'new_quantity': new_quantity if new_quantity > 0 else 0
            }), 200
        else:
            return jsonify({
                'success': False,
                'error': 'Item non trovato o errore'
            }), 404
            
    except Exception as e:
        logger.error(f"Errore update quantity: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

@inventory_bp.route('/session/<int:session_id>', methods=['DELETE'])
@require_auth
def delete_session_endpoint(session_id):
    """Elimina una sessione e tutti i suoi items."""
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        
        # Elimina gli items della sessione
        import pymysql
        conn = pymysql.connect(**get_service().session_model.db_config)
        cursor = conn.cursor()
        
        cursor.execute("DELETE FROM inv_risultati_inventario WHERE id_sessione = %s AND id_tenant = %s", 
                      (session_id, tenant_id))
        cursor.execute("DELETE FROM inv_sessioni_inventario WHERE id = %s AND id_tenant = %s", 
                      (session_id, tenant_id))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({'success': True, 'message': 'Sessione eliminata'}), 200
    except Exception as e:
        logger.error(f"Errore delete session: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

@inventory_bp.route('/salespoint/<int:salespoint_id>/stats', methods=['GET'])
@require_auth
def get_salespoint_stats(salespoint_id):
    """Statistiche per un punto vendita specifico."""
    try:
        import pymysql
        from app.models.inventory import InventorySession
        
        db_config = {
            'host': os.getenv('DB_HOST', 'localhost'),
            'user': os.getenv('DB_USER', 'enigma_user'),
            'password': os.getenv('DB_PASSWORD', 'arcana2026'),
            'database': os.getenv('DB_NAME', 'enigma'),
            'charset': 'utf8mb4'
        }
        
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        tenant_id = request.user['tenant_id']
        
        # Sessioni totali per questa sede
        cursor.execute("""
            SELECT COUNT(*) as total
            FROM inv_sessioni_inventario
            WHERE id_tenant = %s AND id_sede = %s
        """, (tenant_id, salespoint_id))
        total_sessions = cursor.fetchone()['total']
        
        # Sessione attiva
        cursor.execute("""
            SELECT id FROM inv_sessioni_inventario
            WHERE id_tenant = %s AND id_sede = %s AND stato = 'aperta'
            LIMIT 1
        """, (tenant_id, salespoint_id))
        active_session = 1 if cursor.fetchone() else 0
        
        # Prodotti scansionati (sessioni chiuse)
        cursor.execute("""
            SELECT COALESCE(SUM(i.quantita_fisica), 0) as total_items,
                   COALESCE(SUM(
                       CAST(JSON_EXTRACT(p.dati_cassanova, '$.prices[0].value') AS DECIMAL(10,2)) 
                       * i.quantita_fisica
                   ), 0) as total_value
            FROM inv_risultati_inventario i
            JOIN inv_sessioni_inventario s ON i.id_sessione = s.id
            LEFT JOIN inv_cache_prodotti p ON i.barcode = p.codice_barcode
            WHERE s.id_tenant = %s AND s.id_sede = %s AND s.stato = 'chiusa'
        """, (tenant_id, salespoint_id))
        
        items_data = cursor.fetchone()
        total_items = items_data['total_items'] or 0
        total_value = items_data['total_value'] or 0
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {
                'total_sessions': total_sessions,
                'active_session': active_session,
                'total_items': int(total_items),
                'total_value': float(total_value)
            }
        }), 200
    except Exception as e:
        logger.error(f"Errore get salespoint stats: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

@inventory_bp.route('/session/<int:session_id>/reopen', methods=['POST'])
@require_auth
def reopen_session(session_id):
    """Riapre una sessione chiusa."""
    try:
        service = get_service()
        tenant_id = request.user['tenant_id']
        
        import pymysql
        conn = pymysql.connect(**service.session_model.db_config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # Marca sessione come aperta
        cursor.execute("""
            UPDATE inv_sessioni_inventario 
            SET stato = 'aperta', timestamp_fine = NULL
            WHERE id = %s AND id_tenant = %s
        """, (session_id, tenant_id))
        
        conn.commit()
        
        # Ritorna i dati della sessione
        cursor.execute("""
            SELECT * FROM inv_sessioni_inventario
            WHERE id = %s AND id_tenant = %s
        """, (session_id, tenant_id))
        
        session_data = cursor.fetchone()
        #logger:
        logger.info(f"Session data: {session_data}")
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'data': {'session': session_data}
        }), 200
    except Exception as e:
        logger.error(f"Errore reopen session: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

# ============================================================================
# SINCRONIZZAZIONE GIACENZE
# ============================================================================

@inventory_bp.route('/sync-stocks', methods=['POST'])
@require_auth
def sync_stocks():
    """
    Sincronizza giacenze da Cassanova per un salespoint.
    
    Body:
        {
            "salespoint_id": 17117
        }
    
    Returns:
        {
            "success": true,
            "data": {
                "synced_count": 150,
                "message": "150 giacenze sincronizzate"
            }
        }
    """
    try:
        service = get_service()
        user = request.user
        data = request.get_json()
        
        salespoint_id = data.get('salespoint_id')
        if not salespoint_id:
            return jsonify({
                'success': False,
                'error': 'salespoint_id richiesto'
            }), 400
        
        # Sincronizza giacenze da Cassanova
        count = service.sync_stocks(user['tenant_id'], salespoint_id)
        
        return jsonify({
            'success': True,
            'data': {
                'synced_count': count,
                'message': f'{count} giacenze sincronizzate per sede {salespoint_id}'
            }
        }), 200
        
    except Exception as e:
        logger.error(f"Errore sync_stocks: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500


@inventory_bp.route('/stock/<product_id>', methods=['GET'])
@require_auth
def get_stock(product_id):
    """
    Ottiene giacenza di un prodotto per un salespoint.
    
    Query params:
        salespoint_id: ID sede
    
    Returns:
        {
            "success": true,
            "data": {
                "quantita": 50,
                "id_prodotto": "..."
            }
        }
    """
    try:
        service = get_service()
        user = request.user
        salespoint_id = request.args.get('salespoint_id', type=int)
        
        if not salespoint_id:
            return jsonify({'success': False, 'error': 'salespoint_id richiesto'}), 400
        
        import pymysql
        conn = pymysql.connect(**service.session_model.db_config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        cursor.execute("""
            SELECT quantita, id_prodotto
            FROM inv_giacenze
            WHERE id_tenant = %s AND id_sede = %s AND id_prodotto = %s
            LIMIT 1
        """, (user['tenant_id'], salespoint_id, product_id))
        
        result = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if result:
            return jsonify({
                'success': True,
                'data': result
            }), 200
        else:
            return jsonify({
                'success': True,
                'data': {'quantita': 0, 'id_prodotto': product_id}
            }), 200
            
    except Exception as e:
        logger.error(f"Errore get_stock: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500