import sqlite3
import os
import hashlib
import secrets
import json
import re
import decimal
import datetime

DB_TYPE = "sqlite"
DB_FILE = "sagra.db"
DB_HOST = ""
DB_PORT = 3306
DB_USER = ""
DB_PASSWORD = ""
DB_NAME = ""

if os.path.exists("settings.json"):
    try:
        with open("settings.json", "r", encoding="utf-8") as f:
            settings = json.load(f)
            DB_TYPE = settings.get("db_type", DB_TYPE).lower()
            DB_FILE = settings.get("db_file", DB_FILE)
            DB_HOST = settings.get("db_host", DB_HOST)
            DB_PORT = int(settings.get("db_port", DB_PORT))
            DB_USER = settings.get("db_user", DB_USER)
            DB_PASSWORD = settings.get("db_password", DB_PASSWORD)
            DB_NAME = settings.get("db_name", DB_NAME)
    except Exception:
        pass

# Environment overrides
DB_TYPE = os.environ.get("DB_TYPE", DB_TYPE).lower()
DB_FILE = os.environ.get("DB_FILE", DB_FILE)
DB_HOST = os.environ.get("DB_HOST", DB_HOST)
DB_PORT = int(os.environ.get("DB_PORT", DB_PORT))
DB_USER = os.environ.get("DB_USER", DB_USER)
DB_PASSWORD = os.environ.get("DB_PASSWORD", DB_PASSWORD)
DB_NAME = os.environ.get("DB_NAME", DB_NAME)

# Check and replace database from a pending backup restore (.new file)
# This is safe to run on startup before any connection is opened.
if DB_TYPE == "sqlite" and DB_FILE:
    db_new_file = DB_FILE + ".new"
    if os.path.exists(db_new_file):
        try:
            print(f"[DB] Rilevato backup database ripristinato: {db_new_file}")
            # Delete old db files (including WAL/SHM to avoid synchronization mismatch)
            for ext in ["", "-wal", "-shm"]:
                target_f = DB_FILE + ext
                if os.path.exists(target_f):
                    os.remove(target_f)
            # Rename the new database file to the active one
            os.rename(db_new_file, DB_FILE)
            print(f"[DB] Sostituzione database completata: {DB_FILE}")
        except Exception as e:
            print(f"[DB] ERRORE durante il ripristino del backup del database: {e}")

# Set up integrity errors depending on DB type
DB_INTEGRITY_ERRORS = (sqlite3.IntegrityError,)
try:
    import pymysql
    import pymysql.err  # import esplicito necessario per alcune versioni
    DB_INTEGRITY_ERRORS = (sqlite3.IntegrityError, pymysql.err.IntegrityError)
except ImportError:
    pass


def get_no_kitchen_categories():
    categories_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), "categories.json")
    if os.path.exists(categories_file):
        try:
            with open(categories_file, "r", encoding="utf-8") as f:
                categories = json.load(f)
                return [cat["id"] for cat in categories if cat.get("no_kitchen") is True]
        except Exception:
            pass
    return []

def translate_query(query):
    if DB_TYPE == "mysql":
        if query.strip().upper().startswith("PRAGMA"):
            if "FOREIGN_KEYS" in query.upper():
                return "SET FOREIGN_KEY_CHECKS = 1"
            return "SELECT 1"
        query = query.replace("?", "%s")
        
        # Match datetime('now', 'localtime', '+/- N units')
        # e.g. datetime('now', 'localtime', '+24 hours') -> NOW() + INTERVAL 24 HOUR
        # e.g. datetime('now', 'localtime', '-60 seconds') -> NOW() - INTERVAL 60 SECOND
        def replace_datetime_offset(match):
            val = match.group(1)
            unit = match.group(2).upper()
            if unit.endswith('S'):
                unit = unit[:-1]
            op = "+" if not val.startswith("-") else "-"
            num = val.lstrip("+-").strip()
            return f"NOW() {op} INTERVAL {num} {unit}"
            
        query = re.sub(
            r"datetime\(\s*'now'\s*,\s*'localtime'\s*,\s*'([+-]?\s*\d+)\s+([a-zA-Z]+)'\s*\)",
            replace_datetime_offset,
            query
        )
        
        query = query.replace("datetime('now', 'localtime')", "NOW()")
        query = query.replace("date('now', 'localtime')", "CURDATE()")
        query = query.replace("date(created_at) = date('now', 'localtime')", "DATE(created_at) = CURDATE()")
        query = query.replace("INTEGER PRIMARY KEY AUTOINCREMENT", "INT AUTO_INCREMENT PRIMARY KEY")
        query = query.replace("DEFAULT (datetime('now', 'localtime'))", "DEFAULT CURRENT_TIMESTAMP")
        query = query.replace("TEXT UNIQUE", "VARCHAR(191) UNIQUE")
        query = query.replace("TEXT PRIMARY KEY", "VARCHAR(191) PRIMARY KEY")
    return query

class QueryTranslatingRow(dict):
    def __init__(self, d):
        converted = {}
        for k, v in d.items():
            if isinstance(v, decimal.Decimal):
                if v % 1 == 0:
                    converted[k] = int(v)
                else:
                    converted[k] = float(v)
            else:
                converted[k] = v
        super().__init__(converted)

    def __getitem__(self, key):
        if isinstance(key, int):
            return list(self.values())[key]
        return super().__getitem__(key)

class QueryTranslatingCursor:
    def __init__(self, cursor):
        self.cursor = cursor
        
    def execute(self, query, params=None):
        translated = translate_query(query)
        if DB_TYPE == "mysql" and params is not None and len(params) == 0:
            params = None
        if params is not None:
            return self.cursor.execute(translated, params)
        else:
            return self.cursor.execute(translated)
            
    def executemany(self, query, seq_of_params):
        translated = translate_query(query)
        return self.cursor.executemany(translated, seq_of_params)
        
    def fetchone(self):
        row = self.cursor.fetchone()
        if row is not None and DB_TYPE == "mysql":
            return QueryTranslatingRow(row)
        return row
        
    def fetchall(self):
        rows = self.cursor.fetchall()
        if DB_TYPE == "mysql":
            return [QueryTranslatingRow(r) for r in rows]
        return rows
        
    @property
    def lastrowid(self):
        return self.cursor.lastrowid
        
    @property
    def rowcount(self):
        return self.cursor.rowcount

class QueryTranslatingConnection:
    def __init__(self, conn):
        self.conn = conn
    def cursor(self):
        return QueryTranslatingCursor(self.conn.cursor())
    def commit(self):
        return self.conn.commit()
    def rollback(self):
        return self.conn.rollback()
    def close(self):
        return self.conn.close()

def hash_password(password):
    return hashlib.sha256(password.encode('utf-8')).hexdigest()

def get_connection():
    if DB_TYPE == "mysql":
        try:
            import pymysql
            import pymysql.cursors
        except ImportError:
            raise ImportError("Libreria 'pymysql' mancante. Installa con: pip3 install pymysql")
        conn = pymysql.connect(
            host=DB_HOST,
            port=DB_PORT,
            user=DB_USER,
            password=DB_PASSWORD,
            database=DB_NAME,
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=True
        )
        return QueryTranslatingConnection(conn)
    else:
        conn = sqlite3.connect(DB_FILE, timeout=30.0)
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA foreign_keys = ON;")
        conn.row_factory = sqlite3.Row
        return QueryTranslatingConnection(conn)

FOODCOST_DB_FILE = "foodcost.db"

def get_foodcost_connection():
    conn = sqlite3.connect(FOODCOST_DB_FILE, timeout=30.0)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.row_factory = sqlite3.Row
    return conn

def init_foodcost_db():
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    
    # 7. Ingredients Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ingredients (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        unit TEXT NOT NULL,
        package_size REAL NOT NULL,
        package_price REAL NOT NULL,
        iva REAL NOT NULL DEFAULT 10
    );
    """)

    # 8. Recipe Ingredients Table (without foreign key constraint to menu_items since it's a different database)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS recipe_ingredients (
        menu_item_id INTEGER,
        ingredient_id INTEGER,
        quantity REAL NOT NULL,
        PRIMARY KEY (menu_item_id, ingredient_id),
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE
    );
    """)

    # 9. Ingredient Purchases Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS ingredient_purchases (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ingredient_id INTEGER NOT NULL,
        purchase_date DATETIME DEFAULT (datetime('now', 'localtime')),
        quantity REAL NOT NULL,
        package_size REAL NOT NULL,
        package_price REAL NOT NULL,
        supplier TEXT DEFAULT '',
        doc_number TEXT DEFAULT '',
        payment_status TEXT DEFAULT 'DA PAGARE',
        payment_date TEXT DEFAULT '',
        iva REAL NOT NULL DEFAULT 10,
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE
    );
    """)

    # Migration to add new columns to ingredient_purchases if missing
    try:
        cursor.execute("PRAGMA table_info(ingredient_purchases);")
        columns = [row[1] for row in cursor.fetchall()]
        if 'supplier' not in columns:
            cursor.execute("ALTER TABLE ingredient_purchases ADD COLUMN supplier TEXT DEFAULT '';")
            conn.commit()
            print("Migration: Added supplier column to ingredient_purchases table.")
        if 'doc_number' not in columns:
            cursor.execute("ALTER TABLE ingredient_purchases ADD COLUMN doc_number TEXT DEFAULT '';")
            conn.commit()
            print("Migration: Added doc_number column to ingredient_purchases table.")
        if 'payment_status' not in columns:
            cursor.execute("ALTER TABLE ingredient_purchases ADD COLUMN payment_status TEXT DEFAULT 'DA PAGARE';")
            conn.commit()
            print("Migration: Added payment_status column to ingredient_purchases table.")
        if 'payment_date' not in columns:
            cursor.execute("ALTER TABLE ingredient_purchases ADD COLUMN payment_date TEXT DEFAULT '';")
            conn.commit()
            print("Migration: Added payment_date column to ingredient_purchases table.")
    except Exception as e:
        print(f"Migration warning for foodcost: {e}")

    # 10. Menu Item Costs Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS menu_item_costs (
        menu_item_id INTEGER PRIMARY KEY,
        purchase_cost REAL NOT NULL DEFAULT 0.00
    );
    """)

    # 11. Simulated Sales Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS simulated_sales (
        menu_item_id INTEGER PRIMARY KEY,
        quantity REAL NOT NULL DEFAULT 0.0,
        quantity_free REAL NOT NULL DEFAULT 0.0
    );
    """)

    # 12. Simulated Sales Daily Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS simulated_sales_daily (
        sales_date TEXT,
        menu_item_id INTEGER,
        quantity REAL NOT NULL DEFAULT 0.0,
        quantity_free REAL NOT NULL DEFAULT 0.0,
        PRIMARY KEY (sales_date, menu_item_id)
    );
    """)

    # Migrazioni per database esistenti
    try:
        cursor.execute("PRAGMA table_info(simulated_sales);")
        cols = [r[1] for r in cursor.fetchall()]
        if "quantity_free" not in cols:
            cursor.execute("ALTER TABLE simulated_sales ADD COLUMN quantity_free REAL DEFAULT 0.0;")
            
        cursor.execute("PRAGMA table_info(simulated_sales_daily);")
        cols_daily = [r[1] for r in cursor.fetchall()]
        if "quantity_free" not in cols_daily:
            cursor.execute("ALTER TABLE simulated_sales_daily ADD COLUMN quantity_free REAL DEFAULT 0.0;")
    except Exception as me:
        print(f"[Migration Warning] Errore migrazione simulated_sales: {me}")



    # 14. Checkout Expenses Table (synchronized from sagra.db for pricing optimizer)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS checkout_expenses (
        id INTEGER PRIMARY KEY,
        checkout_date TEXT NOT NULL,
        amount REAL NOT NULL,
        supplier TEXT NOT NULL,
        description TEXT,
        expense_type TEXT NOT NULL,
        payment_method TEXT NOT NULL DEFAULT 'CONTANTI'
    );
    """)

    # 15. Checkout Summaries Table (synchronized from sagra.db for pricing optimizer)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS checkout_summaries (
        id INTEGER PRIMARY KEY,
        date TEXT UNIQUE NOT NULL,
        cassa_iniziale REAL NOT NULL DEFAULT 0.0,
        pos_amount REAL NOT NULL DEFAULT 0.0,
        soldi_da_versare REAL NOT NULL DEFAULT 0.0,
        quota_tombola REAL NOT NULL DEFAULT 0.0,
        cash_chiusura REAL NOT NULL DEFAULT 0.0,
        totale_spese_contanti REAL NOT NULL DEFAULT 0.0,
        totale_spese_non_contanti REAL NOT NULL DEFAULT 0.0,
        totale_spese REAL NOT NULL DEFAULT 0.0,
        ricavo_serata REAL NOT NULL DEFAULT 0.0,
        totale_spese_ordinarie REAL NOT NULL DEFAULT 0.0,
        totale_spese_straordinarie REAL NOT NULL DEFAULT 0.0,
        totale_spese_associazione REAL NOT NULL DEFAULT 0.0,
        totale_spese_attrezzature REAL NOT NULL DEFAULT 0.0,
        totale_spese_manutenzione REAL NOT NULL DEFAULT 0.0,
        cassa_prossima_serata REAL NOT NULL DEFAULT 0.0,
        cassa_rimanente REAL NOT NULL DEFAULT 0.0,
        guadagno_netto REAL NOT NULL DEFAULT 0.0,
        notes TEXT
    );
    """)

    # Migrazioni per aggiungere colonne a checkout_summaries
    try:
        cursor.execute("ALTER TABLE checkout_summaries ADD COLUMN totale_spese_associazione REAL NOT NULL DEFAULT 0.0;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE checkout_summaries ADD COLUMN totale_spese_attrezzature REAL NOT NULL DEFAULT 0.0;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE checkout_summaries ADD COLUMN totale_spese_manutenzione REAL NOT NULL DEFAULT 0.0;")
    except Exception:
        pass

    # Migrazione una tantum per impostare tutti gli acquisti pregressi a 'DA PAGARE'
    try:
        cursor.execute("CREATE TABLE IF NOT EXISTS foodcost_migrations (name TEXT PRIMARY KEY);")
        
        # Migrazioni per IVA in ottimizzatore prezzi
        try:
            cursor.execute("ALTER TABLE ingredients ADD COLUMN iva REAL NOT NULL DEFAULT 10;")
        except Exception:
            pass
        try:
            cursor.execute("ALTER TABLE ingredient_purchases ADD COLUMN iva REAL NOT NULL DEFAULT 10;")
        except Exception:
            pass
        cursor.execute("SELECT name FROM foodcost_migrations WHERE name = 'update_purchases_to_da_pagare';")
        if not cursor.fetchone():
            cursor.execute("UPDATE ingredient_purchases SET payment_status = 'DA PAGARE';")
            cursor.execute("INSERT INTO foodcost_migrations (name) VALUES ('update_purchases_to_da_pagare');")
            print("Migration: Updated all existing purchases to 'DA PAGARE' on database startup.")
    except Exception as e:
        print(f"Migration error (update purchases to da_pagare): {e}")

    conn.commit()
    conn.close()

def init_db():
    init_foodcost_db()
    conn = get_connection()
    cursor = conn.cursor()
    
    # Enable foreign keys
    cursor.execute("PRAGMA foreign_keys = ON;")
    
    # Schema migration to move purchase_cost to foodcost.db if needed
    try:
        cursor.execute("PRAGMA table_info(menu_items);")
        columns = [row[1] for row in cursor.fetchall()]
        if 'purchase_cost' in columns:
            print("Migration: Moving purchase_cost from sagra.db to foodcost.db...")
            init_foodcost_db()
            
            cursor.execute("SELECT id, purchase_cost FROM menu_items;")
            existing_costs = cursor.fetchall()
            
            fc_conn = get_foodcost_connection()
            fc_cursor = fc_conn.cursor()
            for item_id, cost in existing_costs:
                fc_cursor.execute("""
                    INSERT INTO menu_item_costs (menu_item_id, purchase_cost)
                    VALUES (?, ?)
                    ON CONFLICT(menu_item_id) DO UPDATE SET purchase_cost = excluded.purchase_cost;
                """, (item_id, cost or 0.0))
            fc_conn.commit()
            fc_conn.close()
            
            cursor.execute("ALTER TABLE menu_items DROP COLUMN purchase_cost;")
            conn.commit()
            print("Migration completed successfully!")
    except Exception as e:
        print(f"Migration warning: {e}")

    try:
        if DB_TYPE == "mysql":
            cursor.execute("SHOW COLUMNS FROM orders LIKE 'payment_type';")
            if not cursor.fetchone():
                cursor.execute("ALTER TABLE orders ADD COLUMN payment_type VARCHAR(20) DEFAULT 'CONTANTI';")
            cursor.execute("SHOW COLUMNS FROM orders LIKE 'is_printed';")
            if not cursor.fetchone():
                cursor.execute("ALTER TABLE orders ADD COLUMN is_printed TINYINT(1) DEFAULT 0;")
            cursor.execute("SHOW COLUMNS FROM orders LIKE 'printed_at';")
            if not cursor.fetchone():
                cursor.execute("ALTER TABLE orders ADD COLUMN printed_at DATETIME NULL;")
            cursor.execute("SHOW COLUMNS FROM orders LIKE 'pickup_pin';")
            if not cursor.fetchone():
                cursor.execute("ALTER TABLE orders ADD COLUMN pickup_pin VARCHAR(10) NULL;")
            conn.commit()
            print("Migration: MySQL orders table updated.")
        else:
            cursor.execute("PRAGMA table_info(orders);")
            columns = [row[1] for row in cursor.fetchall()]
            if 'payment_type' not in columns:
                cursor.execute("ALTER TABLE orders ADD COLUMN payment_type TEXT DEFAULT 'CONTANTI';")
            if 'is_printed' not in columns:
                cursor.execute("ALTER TABLE orders ADD COLUMN is_printed INTEGER DEFAULT 0;")
            if 'printed_at' not in columns:
                cursor.execute("ALTER TABLE orders ADD COLUMN printed_at DATETIME;")
            if 'pickup_pin' not in columns:
                cursor.execute("ALTER TABLE orders ADD COLUMN pickup_pin TEXT;")
            conn.commit()
            print("Migration: SQLite orders table updated.")
    except Exception as e:
        print(f"Migration error for orders table: {e}")
    
    # 1. Menu Items Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS menu_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        price REAL NOT NULL,
        category TEXT NOT NULL, -- 'PIZZERIA', 'GRIGLIA', 'CUCINA', 'BEVANDE', 'DOLCI'
        is_available INTEGER DEFAULT 1,
        is_active INTEGER DEFAULT 1
    );
    """)

    # Users Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        role TEXT NOT NULL, -- 'ADMIN', 'CASSIERE', 'CUCINA'
        sector TEXT, -- e.g., 'PIZZERIA'
        created_at DATETIME DEFAULT (datetime('now', 'localtime'))
    );
    """)
    
    # Sessions Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS sessions (
        token TEXT PRIMARY KEY,
        user_id INTEGER,
        expires_at DATETIME,
        FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
    );
    """)
    
    # 2. Orders Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_number INTEGER NOT NULL,
        customer_name TEXT NOT NULL,
        customer_phone TEXT NOT NULL,
        table_number TEXT NOT NULL,
        status TEXT DEFAULT 'PENDING', -- 'PENDING', 'VALIDATED', 'READY', 'DELIVERED', 'CANCELLED'
        total_price REAL NOT NULL DEFAULT 0.00,
        wa_contact_saved INTEGER DEFAULT 0,
        payment_type TEXT DEFAULT 'CONTANTI', -- 'CONTANTI', 'POS', 'PERSONALE', 'OMAGGIO'
        is_printed INTEGER DEFAULT 0,
        printed_at DATETIME NULL,
        pickup_pin TEXT NULL,
        created_at DATETIME DEFAULT (datetime('now', 'localtime')),
        updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
    );
    """)
    
    # 3. Order Items Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS order_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER,
        menu_item_id INTEGER,
        quantity INTEGER NOT NULL DEFAULT 1,
        notes TEXT,
        status TEXT DEFAULT 'PENDING', -- 'PENDING', 'PREPARING', 'READY'
        price_at_order REAL NOT NULL,
        created_at DATETIME DEFAULT (datetime('now', 'localtime')),
        released_at DATETIME,
        is_client_locked INTEGER DEFAULT 0,
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
        FOREIGN KEY (menu_item_id) REFERENCES menu_items(id)
    );
    """)

    # 4. Daily Checkouts Table
    
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS category_counters (
        category TEXT PRIMARY KEY,
        current_value INTEGER DEFAULT 0
    );
    """)
    
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS daily_checkouts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT UNIQUE NOT NULL,
        cassa_iniziale REAL NOT NULL DEFAULT 0.0,
        pos_amount REAL NOT NULL DEFAULT 0.0,
        soldi_da_versare REAL NOT NULL DEFAULT 0.0,
        quota_tombola REAL NOT NULL DEFAULT 0.0,
        notes TEXT,
        created_at DATETIME DEFAULT (datetime('now', 'localtime')),
        updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
    );
    """)

    # Migration for daily_checkouts notes column
    try:
        cursor.execute("ALTER TABLE daily_checkouts ADD COLUMN notes TEXT;")
    except Exception:
        pass

    # 5. Checkout Denominations Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS checkout_denominations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        checkout_id INTEGER NOT NULL,
        count_type TEXT NOT NULL, -- 'CHIUSURA', 'PROSSIMA_SERATA', 'RIMANENTI'
        denomination REAL NOT NULL,
        quantity INTEGER NOT NULL DEFAULT 0,
        FOREIGN KEY(checkout_id) REFERENCES daily_checkouts(id) ON DELETE CASCADE
    );
    """)

    # 6. Checkout Expenses Table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS checkout_expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        checkout_id INTEGER NOT NULL,
        amount REAL NOT NULL,
        supplier TEXT NOT NULL,
        description TEXT,
        expense_type TEXT NOT NULL, -- 'ORDINARIA', 'STRAORDINARIA'
        payment_method TEXT NOT NULL DEFAULT 'CONTANTI', -- 'CONTANTI', 'BONIFICO', 'CARTA'
        FOREIGN KEY(checkout_id) REFERENCES daily_checkouts(id) ON DELETE CASCADE
    );
    """)
    
    # Migrations for checkout_expenses payment_method
    try:
        cursor.execute("PRAGMA table_info(checkout_expenses);")
        cols_exp = [r[1] for r in cursor.fetchall()]
        if "payment_method" not in cols_exp:
            cursor.execute("ALTER TABLE checkout_expenses ADD COLUMN payment_method TEXT NOT NULL DEFAULT 'CONTANTI';")
    except Exception as me:
        print(f"[Migration Warning] Errore migrazione checkout_expenses payment_method: {me}")
    
    # Ingredients, Recipe Ingredients, and Purchases are now created in foodcost.db (SQLite)
    # inside init_foodcost_db(), completely isolated from the operational Sagra database.
    
    conn.commit()
    
    # Migrations for multi-cashier tracking and locking
    try:
        cursor.execute("ALTER TABLE menu_items ADD COLUMN is_active INTEGER DEFAULT 1;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN locked_by TEXT;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN locked_at DATETIME;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE menu_items ADD COLUMN stock_limit INTEGER DEFAULT NULL;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN validated_by TEXT;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN wa_contact_saved INTEGER DEFAULT 0;")
    except Exception:
        pass
    # Sync migrations
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN remote_id INTEGER;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN sync_status TEXT DEFAULT 'PENDING_PUSH';")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN is_synced INTEGER DEFAULT 0;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE order_items ADD COLUMN released_at DATETIME;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE order_items ADD COLUMN queue_index INTEGER;")
    except Exception:
        pass
    # Online payment migrations
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN payment_gateway TEXT;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN payment_status TEXT DEFAULT 'UNPAID';")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN transaction_id TEXT;")
    except Exception:
        pass
    try:
        cursor.execute("ALTER TABLE orders ADD COLUMN paid_at DATETIME;")
    except Exception:
        pass
    
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS payment_transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER NOT NULL,
        gateway TEXT NOT NULL,
        transaction_id TEXT UNIQUE NOT NULL,
        amount REAL NOT NULL,
        status TEXT NOT NULL,
        payload_json TEXT,
        created_at DATETIME DEFAULT (datetime('now', 'localtime')),
        FOREIGN KEY(order_id) REFERENCES orders(id)
    );
    """)
    conn.commit()
    
    # Populate default menu items if the table is empty
    cursor.execute("SELECT COUNT(*) FROM menu_items;")
    if cursor.fetchone()[0] == 0:
        default_menu = [
            # Pizzeria
            ("Pizza Margherita", "Pomodoro, mozzarella, origano", 6.00, "PIZZERIA"),
            ("Pizza Diavola", "Pomodoro, mozzarella, salame piccante", 7.50, "PIZZERIA"),
            ("Pizza 4 Formaggi", "Pomodoro, mozzarella, gorgonzola, fontina, grana", 8.00, "PIZZERIA"),
            # Griglia
            ("Grigliata Mista", "Costine, salsiccia, capocollo alla brace", 12.00, "GRIGLIA"),
            ("Panino con Salamella", "Salamella alla griglia, salse a scelta", 5.00, "GRIGLIA"),
            ("Wurstel e Patatine", "Due wurstel grandi alla griglia con patatine", 6.50, "GRIGLIA"),
            # Cucina
            ("Casoncelli alla Bergamasca", "Pasta fresca ripiena con burro, pancetta e salvia", 8.00, "CUCINA"),
            ("Polenta e Zola", "Polenta calda servita con formaggio gorgonzola fuso", 6.50, "CUCINA"),
            ("Patatine Fritte", "Porzione abbondante di patatine fritte croccanti", 3.50, "CUCINA"),
            # Bevande
            ("Acqua Naturale 50cl", "Bottiglia di acqua naturale", 1.00, "BEVANDE"),
            ("Acqua Frizzante 50cl", "Bottiglia di acqua frizzante", 1.00, "BEVANDE"),
            ("Birra Media Artigianale", "Birra alla spina bionda 40cl", 4.50, "BEVANDE"),
            ("Coca Cola Lattina", "Coca Cola classica in lattina 33cl", 2.50, "BEVANDE"),
            ("Vino Rosso (Bicchiere)", "Vino rosso locale della casa", 2.00, "BEVANDE"),
            # Dolci
            ("Tiramisù della Sagra", "Tiramisù artigianale fatto in casa", 4.00, "DOLCI"),
            ("Crostata di Mele", "Fetta di crostata di mele", 3.00, "DOLCI")
        ]
        cursor.executemany("""
            INSERT INTO menu_items (name, description, price, category) 
            VALUES (?, ?, ?, ?);
        """, default_menu)
        conn.commit()
        
    # Seed default users
    cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'admin';")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO users (username, password_hash, role)
            VALUES (?, ?, ?);
        """, ('admin', hash_password('24030pacEB'), 'SUPER_ADMIN'))
        conn.commit()
        
    cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'Pamela';")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO users (username, password_hash, role)
            VALUES (?, ?, ?);
        """, ('Pamela', hash_password('Pamela'), 'ADMIN'))
        conn.commit()

    cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'Thomas';")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO users (username, password_hash, role, sector)
            VALUES (?, ?, ?, ?);
        """, ('Thomas', hash_password('Thomas'), 'CUCINA', 'GRIGLIA'))
        conn.commit()

    cursor.execute("SELECT COUNT(*) FROM users WHERE username = 'Bruno';")
    if cursor.fetchone()[0] == 0:
        cursor.execute("""
            INSERT INTO users (username, password_hash, role)
            VALUES (?, ?, ?);
        """, ('Bruno', hash_password('Bruno'), 'F&B_MANAGER'))
        conn.commit()

    # Migration: check if there is a SUPER_ADMIN. If not, upgrade the default 'admin' if it exists.
    cursor.execute("SELECT COUNT(*) FROM users WHERE role = 'SUPER_ADMIN';")
    if cursor.fetchone()[0] == 0:
        cursor.execute("UPDATE users SET role = 'SUPER_ADMIN' WHERE username = 'admin' AND role = 'ADMIN';")
        conn.commit()
        
    # --- Startup Cleanup: release orphaned order locks from previous server crash ---
    # If the server crashed while a cashier had an order open, the locked_by field
    # remains set in the DB forever. On every boot we clear all locks on PENDING orders
    # so that any cashier can pick them up again on the new session.
    try:
        cursor.execute("""
            UPDATE orders
            SET locked_by = NULL, locked_at = NULL
            WHERE locked_by IS NOT NULL
              AND status = 'PENDING'
        """)
        unlocked_count = cursor.rowcount
        conn.commit()
        if unlocked_count > 0:
            print(f"[Startup] Sbloccati {unlocked_count} ordini rimasti in lock da sessione precedente.")
    except Exception as e:
        print(f"[Startup] Errore nella pulizia dei lock ordini: {e}")

    conn.close()


def get_menu(include_unavailable=False, include_inactive=False):
    # 1. Fetch distinct recipe item IDs and costs from foodcost.db
    has_recipe_ids = set()
    item_costs = {}
    try:
        fc_conn = get_foodcost_connection()
        fc_cursor = fc_conn.cursor()
        fc_cursor.execute("SELECT DISTINCT menu_item_id FROM recipe_ingredients;")
        has_recipe_ids = {row[0] for row in fc_cursor.fetchall()}
        
        fc_cursor.execute("SELECT menu_item_id, purchase_cost FROM menu_item_costs;")
        item_costs = {row[0]: row[1] for row in fc_cursor.fetchall()}
        fc_conn.close()
    except Exception:
        pass

    # 2. Query main database
    conn = get_connection()
    cursor = conn.cursor()
    query = "SELECT m.* FROM menu_items m"
    
    if include_inactive:
        cursor.execute(query + ";")
    elif include_unavailable:
        cursor.execute(query + " WHERE m.is_active = 1;")
    else:
        cursor.execute(query + " WHERE m.is_active = 1 AND m.is_available = 1;")
    rows = cursor.fetchall()
    conn.close()
    
    # 3. Add has_recipe flag and purchase_cost in Python
    menu_list = []
    for row in rows:
        d = dict(row)
        d['has_recipe'] = 1 if d['id'] in has_recipe_ids else 0
        d['purchase_cost'] = item_costs.get(d['id'], 0.0)
        menu_list.append(d)
    return menu_list

def get_menu_item(item_id):
    # 1. Check if it has a recipe and get purchase_cost from foodcost.db
    has_recipe = 0
    purchase_cost = 0.0
    try:
        fc_conn = get_foodcost_connection()
        fc_cursor = fc_conn.cursor()
        fc_cursor.execute("SELECT COUNT(*) FROM recipe_ingredients WHERE menu_item_id = ?;", (item_id,))
        count = fc_cursor.fetchone()[0]
        has_recipe = 1 if count > 0 else 0
        
        fc_cursor.execute("SELECT purchase_cost FROM menu_item_costs WHERE menu_item_id = ?;", (item_id,))
        cost_row = fc_cursor.fetchone()
        if cost_row:
            purchase_cost = cost_row[0]
        fc_conn.close()
    except Exception:
        pass

    # 2. Query main database
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT m.* FROM menu_items m WHERE m.id = ?;", (item_id,))
    row = cursor.fetchone()
    conn.close()
    
    if row:
        d = dict(row)
        d['has_recipe'] = has_recipe
        d['purchase_cost'] = purchase_cost
        return d
    return None

def create_menu_item(name, description, price, category, stock_limit=None):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        INSERT INTO menu_items (name, description, price, category, is_available, is_active, stock_limit)
        VALUES (?, ?, ?, ?, 1, 1, ?);
    """, (name, description, price, category, stock_limit))
    conn.commit()
    new_id = cursor.lastrowid
    conn.close()
    return new_id

def update_menu_item(item_id, name, description, price, category, is_available, stock_limit=None):
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE menu_items
        SET name = ?, description = ?, price = ?, category = ?, is_available = ?, stock_limit = ?, is_active = 1
        WHERE id = ?;
    """, (name, description, price, category, int(is_available), stock_limit, item_id))
    conn.commit()
    conn.close()

def delete_menu_item(item_id):
    conn = get_connection()
    cursor = conn.cursor()
    success = False
    try:
        # Tenta l'eliminazione fisica del piatto
        cursor.execute("DELETE FROM menu_items WHERE id = ?;", (item_id,))
        conn.commit()
        success = True
    except DB_INTEGRITY_ERRORS:
        # Se c'è una violazione di chiave esterna (es. il piatto è in ordini esistenti),
        # facciamo soft-delete impostando is_available = 0 e is_active = 0
        conn.rollback()
        cursor.execute("UPDATE menu_items SET is_available = 0, is_active = 0 WHERE id = ?;", (item_id,))
        conn.commit()
        success = False
    finally:
        conn.close()
    return success

def cleanup_expired_pending_orders(cursor):
    """
    Cancella gli ordini in stato 'PENDING' da più di 2 ore ripristinando il loro stock.
    """
    try:
        # Trova gli ordini PENDING scaduti (creati da più di 2 ore)
        cursor.execute("""
            SELECT id FROM orders 
            WHERE status = 'PENDING' 
              AND created_at < datetime('now', 'localtime', '-2 hours');
        """)
        expired_order_ids = [row[0] for row in cursor.fetchall()]
        
        # Per ciascun ordine scaduto, ripristina lo stock prima della cancellazione
        for ord_id in expired_order_ids:
            cursor.execute("SELECT menu_item_id, quantity FROM order_items WHERE order_id = ?;", (ord_id,))
            items = cursor.fetchall()
            for item in items:
                m_item_id = item['menu_item_id']
                qty = item['quantity']
                cursor.execute("SELECT stock_limit, is_available, is_active FROM menu_items WHERE id = ?;", (m_item_id,))
                m_row = cursor.fetchone()
                if m_row and m_row['stock_limit'] is not None:
                    new_stock = m_row['stock_limit'] + qty
                    if m_row['stock_limit'] == 0 and m_row['is_available'] == 0 and m_row['is_active'] == 1:
                        cursor.execute("UPDATE menu_items SET stock_limit = ?, is_available = 1 WHERE id = ?;", (new_stock, m_item_id))
                    else:
                        cursor.execute("UPDATE menu_items SET stock_limit = ? WHERE id = ?;", (new_stock, m_item_id))
            
            # Cancella voci d'ordine e ordine
            cursor.execute("DELETE FROM order_items WHERE order_id = ?;", (ord_id,))
            cursor.execute("DELETE FROM orders WHERE id = ?;", (ord_id,))
            
    except Exception as e:
        print(f"[Cleanup] Errore durante la cancellazione degli ordini pendenti scaduti: {e}")

def generate_pickup_pin(cursor):
    import random
    date_filter = "DATE(created_at) = CURDATE()" if DB_TYPE == "mysql" else "date(created_at) = date('now', 'localtime')"
    for _ in range(100):
        pin = f"{random.randint(1000, 9999)}"
        cursor.execute(f"SELECT id FROM orders WHERE pickup_pin = ? AND {date_filter} LIMIT 1;", (pin,))
        if not cursor.fetchone():
            return pin
    return f"{random.randint(1000, 9999)}"

def create_order(customer_name, customer_phone, table_number, items, wa_contact_saved=0, is_cashier=False):
    """
    items is a list of dicts: [{'menu_item_id': 1, 'quantity': 2, 'notes': 'no onions', 'locked': False}]
    """
    if not table_number:
        table_number = ""
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        # Avvia una transazione con lock esclusivo di scrittura (SQLite: IMMEDIATE,
        # MySQL: usa autocommit=False che è già gestito dalla connessione).
        # Questo garantisce che il calcolo MAX(order_number)+1 sia atomico e
        # non produca duplicati anche con più thread concorrenti.
        if DB_TYPE == "sqlite":
            cursor.execute("BEGIN IMMEDIATE;")
        cleanup_expired_pending_orders(cursor)
        
        # 1. Determine next order number (ora atomico dentro la transazione, incrementale giornaliero)
        if DB_TYPE == "mysql":
            cursor.execute("""
                SELECT COALESCE(MAX(order_number), 0) + 1 
                FROM orders 
                WHERE DATE(created_at) = CURDATE()
                FOR UPDATE
            """)
        else:
            cursor.execute("""
                SELECT COALESCE(MAX(order_number), 0) + 1 
                FROM orders 
                WHERE date(created_at) = date('now', 'localtime')
            """)
        next_order_num = cursor.fetchone()[0]
        
        # 2. Fetch prices, check stock, and calculate total
        total_price = 0.0
        validated_items = []
        for item in items:
            item_id = item['menu_item_id']
            qty = item.get('quantity', 1)
            if not isinstance(qty, int) or qty <= 0:
                raise ValueError("La quantità deve essere un numero intero maggiore di zero.")
            notes = item.get('notes', '')
            locked = item.get('locked', False)
            
            cursor.execute("SELECT price, name, category, is_available, stock_limit FROM menu_items WHERE id = ?;", (item_id,))
            menu_row = cursor.fetchone()
            if not menu_row:
                raise ValueError(f"Il piatto con ID {item_id} non esiste.")
            
            # Check availability
            if not menu_row['is_available']:
                raise ValueError(f"Il piatto '{menu_row['name']}' non è attualmente disponibile.")
            
            # Check and decrement stock limit
            stock_limit = menu_row['stock_limit']
            if stock_limit is not None:
                if stock_limit < qty:
                    raise ValueError(f"Disponibilità insufficiente per il piatto '{menu_row['name']}'. Rimanenti: {stock_limit}, Richiesti: {qty}.")
                new_stock = stock_limit - qty
                if new_stock == 0:
                    cursor.execute("UPDATE menu_items SET stock_limit = 0, is_available = 0 WHERE id = ?;", (item_id,))
                else:
                    cursor.execute("UPDATE menu_items SET stock_limit = ? WHERE id = ?;", (new_stock, item_id))
            
            price = menu_row['price']
            name = menu_row['name']
            category = menu_row['category']
            total_price += price * qty
            validated_items.append({
                'menu_item_id': item_id,
                'name': name,
                'quantity': qty,
                'notes': notes,
                'price': price,
                'category': category,
                'locked': locked
            })
            
        # 3. Create the order row
        pickup_pin = generate_pickup_pin(cursor)
        cursor.execute("""
            INSERT INTO orders (order_number, customer_name, customer_phone, table_number, total_price, status, wa_contact_saved, is_printed, pickup_pin)
            VALUES (?, ?, ?, ?, ?, 'PENDING', ?, 0, ?);
        """, (next_order_num, customer_name, customer_phone, table_number, total_price, wa_contact_saved, pickup_pin))
        
        order_id = cursor.lastrowid
        
        # 4. Create order items rows
        no_kitchen_cats = get_no_kitchen_categories()
        for v_item in validated_items:
            if v_item['category'] in no_kitchen_cats:
                status = 'READY'
                is_client_locked = 0
            else:
                status = 'HOLD' if v_item['locked'] else 'PENDING'
                is_client_locked = 1 if (v_item['locked'] and not is_cashier) else 0
            cursor.execute("""
                INSERT INTO order_items (order_id, menu_item_id, quantity, notes, price_at_order, status, is_client_locked)
                VALUES (?, ?, ?, ?, ?, ?, ?);
            """, (order_id, v_item['menu_item_id'], v_item['quantity'], v_item['notes'], v_item['price'], status, is_client_locked))
            
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()
        
    return get_order_status(order_id)


def get_pending_orders():
    conn = get_connection()
    cursor = conn.cursor()
    
    # Release expired locks (> 60s)
    cursor.execute("""
        UPDATE orders 
        SET locked_by = NULL, locked_at = NULL 
        WHERE status = 'PENDING' AND locked_at < datetime('now', 'localtime', '-60 seconds');
    """)
    cleanup_expired_pending_orders(cursor)
    conn.commit()
    
    # Get all pending orders
    cursor.execute("""
        SELECT * FROM orders 
        WHERE status = 'PENDING' 
        ORDER BY created_at ASC;
    """)
    orders = [dict(row) for row in cursor.fetchall()]
    
    # Populate items for each order
    for o in orders:
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (o['id'],))
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def validate_order(order_id, cashier_name=None, payment_type='CONTANTI'):
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        # Also ensure items are reset to PENDING or READY depending on category config
        no_kitchen_cats = get_no_kitchen_categories()
        if no_kitchen_cats:
            placeholders = ', '.join(['?'] * len(no_kitchen_cats))
            cursor.execute(f"""
                UPDATE order_items
                SET status = 'READY'
                WHERE order_id = ? AND status != 'DELIVERED' AND status != 'HOLD' AND menu_item_id IN (
                    SELECT id FROM menu_items WHERE category IN ({placeholders})
                );
            """, [order_id] + no_kitchen_cats)
            
            condition = f"WHERE order_id = ? AND status != 'HOLD' AND status != 'DELIVERED' AND queue_index IS NULL AND menu_item_id NOT IN (SELECT id FROM menu_items WHERE category IN ({placeholders}))"
            params = [order_id] + no_kitchen_cats
        else:
            condition = "WHERE order_id = ? AND status != 'HOLD' AND status != 'DELIVERED' AND queue_index IS NULL"
            params = [order_id]
            
        cursor.execute(f"SELECT oi.id, mi.category FROM order_items oi JOIN menu_items mi ON oi.menu_item_id = mi.id {condition}", params)
        items_to_update = cursor.fetchall()
        
        category_indices = {}
        for item in items_to_update:
            cat = item['category']
            if DB_TYPE == "mysql":
                q_idx = None
            else:
                if cat not in category_indices:
                    category_indices[cat] = get_next_queue_index(cat, cursor)
                q_idx = category_indices[cat]
            cursor.execute("UPDATE order_items SET status = 'PENDING', released_at = COALESCE(released_at, datetime('now', 'localtime')), queue_index = ? WHERE id = ?", (q_idx, item['id']))
            
        # Check if all items in this order are now READY (e.g. order consists only of drinks)
        cursor.execute("""
            SELECT COUNT(*) FROM order_items 
            WHERE order_id = ? AND status NOT IN ('READY', 'DELIVERED');
        """, (order_id,))
        unfinished_count = cursor.fetchone()[0]
        
        # Check if there are any kitchen-enabled items in this order
        if no_kitchen_cats:
            placeholders = ', '.join(['?'] * len(no_kitchen_cats))
            cursor.execute(f"""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND menu_item_id NOT IN (
                    SELECT id FROM menu_items WHERE category IN ({placeholders})
                );
            """, [order_id] + no_kitchen_cats)
        else:
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ?;
            """, (order_id,))
        kitchen_items_count = cursor.fetchone()[0]
        
        if kitchen_items_count == 0:
            # Only no-kitchen items (like drinks). They are served immediately.
            # Mark them all as DELIVERED and close the order immediately.
            cursor.execute("""
                UPDATE order_items
                SET status = 'DELIVERED'
                WHERE order_id = ?;
            """, (order_id,))
            order_status = 'DELIVERED'
        else:
            order_status = 'READY' if unfinished_count == 0 else 'VALIDATED'
        
        # Update order status, track cashier, payment type and release lock
        cursor.execute("""
            UPDATE orders 
            SET status = ?, 
                validated_by = ?, 
                payment_type = ?,
                locked_by = NULL, 
                locked_at = NULL, 
                sync_status = 'PENDING_PUSH',
                updated_at = datetime('now', 'localtime') 
            WHERE id = ?;
        """, (order_status, cashier_name, payment_type, order_id))
        conn.commit()
        
        # Get order details for receipt/WhatsApp
        cursor.execute("SELECT * FROM orders WHERE id = ?;", (order_id,))
        order_row = dict(cursor.fetchone())
        
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (order_id,))
        order_row['items'] = [dict(row) for row in cursor.fetchall()]
        
        return order_row
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


def confirm_online_payment(order_id, gateway, transaction_id, amount, payload_json=None):
    """
    Idempotent validation of online payments (Stripe, Satispay, Sandbox).
    Validates order, assigns queue indices, records transaction, and flags sync_status.
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        if DB_TYPE == "sqlite":
            cursor.execute("BEGIN IMMEDIATE;")
            
        # 1. Check idempotency: if transaction already processed, return existing order
        cursor.execute("SELECT id FROM payment_transactions WHERE transaction_id = ?;", (transaction_id,))
        if cursor.fetchone():
            conn.close()
            return get_order_status(order_id)
            
        # 2. Record transaction in payment_transactions
        cursor.execute("""
            INSERT INTO payment_transactions (order_id, gateway, transaction_id, amount, status, payload_json)
            VALUES (?, ?, ?, ?, 'SUCCESS', ?);
        """, (order_id, gateway, transaction_id, float(amount), json.dumps(payload_json) if isinstance(payload_json, (dict, list)) else (str(payload_json) if payload_json else None)))
        
        # 3. Validation logic (similar to validate_order)
        no_kitchen_cats = get_no_kitchen_categories()
        if no_kitchen_cats:
            placeholders = ', '.join(['?'] * len(no_kitchen_cats))
            cursor.execute(f"""
                UPDATE order_items
                SET status = 'READY'
                WHERE order_id = ? AND status != 'DELIVERED' AND status != 'HOLD' AND menu_item_id IN (
                    SELECT id FROM menu_items WHERE category IN ({placeholders})
                );
            """, [order_id] + no_kitchen_cats)
            
            condition = f"WHERE order_id = ? AND status != 'HOLD' AND status != 'DELIVERED' AND queue_index IS NULL AND menu_item_id NOT IN (SELECT id FROM menu_items WHERE category IN ({placeholders}))"
            params = [order_id] + no_kitchen_cats
        else:
            condition = "WHERE order_id = ? AND status != 'HOLD' AND status != 'DELIVERED' AND queue_index IS NULL"
            params = [order_id]
            
        cursor.execute(f"SELECT oi.id, mi.category FROM order_items oi JOIN menu_items mi ON oi.menu_item_id = mi.id {condition}", params)
        items_to_update = cursor.fetchall()
        
        category_indices = {}
        for item in items_to_update:
            cat = item['category']
            if DB_TYPE == "mysql":
                q_idx = None
            else:
                if cat not in category_indices:
                    category_indices[cat] = get_next_queue_index(cat, cursor)
                q_idx = category_indices[cat]
            cursor.execute("UPDATE order_items SET status = 'PENDING', released_at = COALESCE(released_at, datetime('now', 'localtime')), queue_index = ? WHERE id = ?", (q_idx, item['id']))
            
        # Check if all items in this order are READY
        cursor.execute("""
            SELECT COUNT(*) FROM order_items 
            WHERE order_id = ? AND status NOT IN ('READY', 'DELIVERED');
        """, (order_id,))
        unfinished_count = cursor.fetchone()[0]
        
        # Check kitchen items count
        if no_kitchen_cats:
            placeholders = ', '.join(['?'] * len(no_kitchen_cats))
            cursor.execute(f"""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND menu_item_id NOT IN (
                    SELECT id FROM menu_items WHERE category IN ({placeholders})
                );
            """, [order_id] + no_kitchen_cats)
        else:
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ?;
            """, (order_id,))
        kitchen_items_count = cursor.fetchone()[0]
        
        if kitchen_items_count == 0:
            cursor.execute("""
                UPDATE order_items
                SET status = 'DELIVERED'
                WHERE order_id = ?;
            """, (order_id,))
            order_status = 'DELIVERED'
        else:
            order_status = 'READY' if unfinished_count == 0 else 'VALIDATED'
            
        # Update order with payment details
        cursor.execute("""
            UPDATE orders 
            SET status = ?, 
                validated_by = 'ONLINE_PAY', 
                payment_type = ?,
                payment_gateway = ?,
                payment_status = 'PAID',
                transaction_id = ?,
                paid_at = datetime('now', 'localtime'),
                locked_by = NULL, 
                locked_at = NULL, 
                sync_status = 'PENDING_PUSH',
                updated_at = datetime('now', 'localtime') 
            WHERE id = ?;
        """, (order_status, gateway, gateway, transaction_id, order_id))
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()
        
    return get_order_status(order_id)

def lock_order(order_id, cashier_name):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # First, release expired locks to ensure accurate lock checking
        cursor.execute("""
            UPDATE orders 
            SET locked_by = NULL, locked_at = NULL 
            WHERE status = 'PENDING' AND locked_at < datetime('now', 'localtime', '-60 seconds');
        """)
        
        # Try to lock: succeeds if lock is NULL or already held by the same cashier
        cursor.execute("""
            UPDATE orders
            SET locked_by = ?, locked_at = datetime('now', 'localtime')
            WHERE id = ? AND status = 'PENDING' AND (locked_by IS NULL OR locked_by = ?);
        """, (cashier_name, order_id, cashier_name))
        conn.commit()
        return cursor.rowcount > 0
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def unlock_order(order_id, cashier_name):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Unlock only if it is currently locked by this cashier
        cursor.execute("""
            UPDATE orders
            SET locked_by = NULL, locked_at = NULL
            WHERE id = ? AND locked_by = ?;
        """, (order_id, cashier_name))
        conn.commit()
        return cursor.rowcount > 0
    except Exception as e:
        conn.rollback()
        raise e
def get_hold_orders(category=None):
    """
    Get all validated orders that contain items in 'HOLD' status for the specified category/department.
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    query = """
        SELECT DISTINCT o.id, o.order_number, o.remote_id, o.customer_name, o.table_number, o.created_at, o.updated_at
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.id
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        WHERE o.status = 'VALIDATED' AND oi.status = 'HOLD'
    """
    params = []
    if category:
        query += " AND mi.category = ?"
        params.append(category)
        
    query += " ORDER BY o.order_number ASC;"
    
    cursor.execute(query, params)
    orders = [dict(row) for row in cursor.fetchall()]
    
    # Now, for each order, fetch the hold items (optionally filtered by category)
    for o in orders:
        item_query = """
            SELECT oi.id as order_item_id, oi.quantity, oi.notes, oi.status as item_status, 
                   mi.name as item_name, mi.category, oi.price_at_order, oi.is_client_locked
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ? AND oi.status = 'HOLD'
        """
        item_params = [o['id']]
        if category:
            item_query += " AND mi.category = ?"
            item_params.append(category)
            
        cursor.execute(item_query, item_params)
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def get_next_queue_index(category, cursor):
    # Upsert pattern for sqlite/mysql
    try:
        cursor.execute("INSERT INTO category_counters (category, current_value) VALUES (?, 1)", (category,))
        return 1
    except Exception:
        cursor.execute("UPDATE category_counters SET current_value = current_value + 1 WHERE category = ?", (category,))
        cursor.execute("SELECT current_value FROM category_counters WHERE category = ?", (category,))
        return cursor.fetchone()[0]

def reset_queue_counters():
    """Reset all category queue counters to 0."""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("UPDATE category_counters SET current_value = 0")
        conn.commit()
        # Return current state for confirmation
        cursor.execute("SELECT category, current_value FROM category_counters ORDER BY category")
        rows = [dict(row) for row in cursor.fetchall()]
        return rows
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def release_order_items(order_id, release_items=None):
    """
    release_items: list of dicts like [{'order_item_id': 12, 'quantity': 1}]
    If release_items is None, release ALL items in 'HOLD' for this order.
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        if DB_TYPE == "sqlite":
            cursor.execute("BEGIN IMMEDIATE;")
            
        # Get category closing times from categories.json to check if any category is closed
        categories_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), "categories.json")
        categories_map = {}
        if os.path.exists(categories_file):
            try:
                with open(categories_file, "r", encoding="utf-8") as f:
                    cats = json.load(f)
                    for cat in cats:
                        if cat.get("closing_time"):
                            categories_map[cat["id"]] = cat["closing_time"]
            except Exception:
                pass
                
        def check_closed(category):
            closing_time = categories_map.get(category)
            if not closing_time:
                return False
            import datetime
            now_str = datetime.datetime.now().strftime("%H:%M")
            if closing_time >= "12:00":
                return now_str >= closing_time or now_str < "06:00"
            else:
                return now_str >= closing_time and now_str < "06:00"

        # If release_items is None, we find all order items for this order that are in 'HOLD' status
        if not release_items:
            cursor.execute("""
                SELECT oi.id, oi.quantity, oi.is_client_locked, mi.category, mi.name 
                FROM order_items oi
                JOIN menu_items mi ON oi.menu_item_id = mi.id
                WHERE oi.order_id = ? AND oi.status = 'HOLD';
            """, (order_id,))
            hold_items = cursor.fetchall()
            
            for item in hold_items:
                if item['is_client_locked'] == 1:
                    raise ValueError(f"Non è possibile sbloccare {item['name']} dalla cucina perché è bloccato da smartphone.")
                if check_closed(item['category']):
                    raise ValueError(f"Il reparto {item['category']} è chiuso. Impossibile preparare {item['name']}.")
                    
# Release all non-client-locked
            category_indices = {}
            for item in hold_items:
                cat = item['category']
                if cat not in category_indices:
                    category_indices[cat] = get_next_queue_index(cat, cursor)
                
                q_idx = category_indices[cat]
                cursor.execute("""
                    UPDATE order_items
                    SET status = 'PENDING', released_at = datetime('now', 'localtime'), queue_index = ?
                    WHERE id = ?;
                """, (q_idx, item['id']))
        else:
            category_indices = {}
            for r_item in release_items:
                oi_id = r_item['order_item_id']
                qty_to_release = r_item['quantity']
                
                # Fetch current item details
                cursor.execute("""
                    SELECT oi.quantity, oi.status, oi.order_id, oi.menu_item_id, oi.notes, oi.price_at_order, oi.is_client_locked, mi.category, mi.name
                    FROM order_items oi
                    JOIN menu_items mi ON oi.menu_item_id = mi.id
                    WHERE oi.id = ?;
                """, (oi_id,))
                row = cursor.fetchone()
                if not row:
                    raise ValueError("Item non trovato.")
                if row['order_id'] != order_id:
                    raise ValueError("Item non appartiene a questo ordine.")
                if row['status'] != 'HOLD':
                    # Already released, ignore to avoid duplicate actions
                    continue
                if row['is_client_locked'] == 1:
                    raise ValueError(f"Non è possibile sbloccare {row['name']} dalla cucina perché è bloccato da smartphone.")
                if qty_to_release <= 0:
                    continue
                    
                if check_closed(row['category']):
                    raise ValueError(f"Il reparto {row['category']} è chiuso. Impossibile preparare {row['name']}.")

                current_qty = row['quantity']
                cat = row['category']
                if cat not in category_indices:
                    category_indices[cat] = get_next_queue_index(cat, cursor)
                q_idx = category_indices[cat]

                if qty_to_release >= current_qty:
                    # Release full quantity
                    cursor.execute("""
                        UPDATE order_items
                        SET status = 'PENDING', released_at = datetime('now', 'localtime'), queue_index = ?
                        WHERE id = ?;
                    """, (q_idx, oi_id))
                else:
                    # Release partial quantity
                    # 1. Decrease Qty on the original 'HOLD' row
                    cursor.execute("""
                        UPDATE order_items
                        SET quantity = ?
                        WHERE id = ?;
                    """, (current_qty - qty_to_release, oi_id))
                    
                    # 2. Insert new 'PENDING' row with qty_to_release
                    cursor.execute("""
                        INSERT INTO order_items (order_id, menu_item_id, quantity, notes, price_at_order, status, released_at, queue_index)
                        VALUES (?, ?, ?, ?, ?, 'PENDING', datetime('now', 'localtime'), ?);
                    """, (order_id, row['menu_item_id'], qty_to_release, row['notes'], row['price_at_order'], q_idx))

        # Set sync_status and update timestamp of the order
        cursor.execute("""
            UPDATE orders
            SET updated_at = datetime('now', 'localtime'), sync_status = 'PENDING_PUSH'
            WHERE id = ?;
        """, (order_id,))
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def get_kitchen_items(category=None):
    """
    Get all active order items for the kitchen.
    Active means the parent order is VALIDATED or READY (so cooking can still see it or check history)
    but specifically we show items where order status = 'VALIDATED' and order_item status IN ('PENDING', 'PREPARING').
    """
    conn = get_connection()
    cursor = conn.cursor()
    query = """
        SELECT oi.id as order_item_id, oi.quantity, oi.notes, oi.status as item_status, 
               oi.released_at, oi.is_client_locked, oi.queue_index, mi.name as item_name, mi.category,
               o.id as order_id, o.order_number, o.remote_id, o.customer_name, o.table_number, o.created_at, o.updated_at
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status IN ('VALIDATED', 'READY', 'DELIVERED')
          AND o.created_at >= datetime('now', 'localtime', '-16 hours')
          AND oi.status IN ('HOLD', 'PENDING', 'PREPARING', 'READY', 'DELIVERED')
    """
    params = []
    
    # We allow filtering by category (e.g. PIZZERIA, GRIGLIA, CUCINA, BEVANDE, DOLCI)
    if category:
        query += " AND mi.category = ?"
        params.append(category)
        
    query += " ORDER BY CASE WHEN oi.status = 'HOLD' THEN 1 ELSE 0 END ASC, COALESCE(oi.released_at, oi.created_at) ASC, oi.id ASC;"
    
    cursor.execute(query, params)
    items = [dict(row) for row in cursor.fetchall()]
    print(f"[DB DEBUG] get_kitchen_items({category}): trovati {len(items)} piatti nel DB", flush=True)
    conn.close()
    return items

def release_order_item(order_id, item_id):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Get category closing times from categories.json to check if the category is closed
        categories_file = os.path.join(os.path.dirname(os.path.abspath(__file__)), "categories.json")
        categories_map = {}
        if os.path.exists(categories_file):
            try:
                with open(categories_file, "r", encoding="utf-8") as f:
                    cats = json.load(f)
                    for cat in cats:
                        if cat.get("closing_time"):
                            categories_map[cat["id"]] = cat["closing_time"]
            except Exception:
                pass
                
        def check_closed(category):
            closing_time = categories_map.get(category)
            if not closing_time:
                return False
            import datetime
            now_str = datetime.datetime.now().strftime("%H:%M")
            if closing_time >= "12:00":
                return now_str >= closing_time or now_str < "06:00"
            else:
                return now_str >= closing_time and now_str < "06:00"

        # Fetch current item details to check if its category is closed
        cursor.execute("""
            SELECT mi.category, mi.name
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.id = ? AND oi.order_id = ? AND oi.status = 'HOLD';
        """, (item_id, order_id))
        row = cursor.fetchone()
        if row:
            category = row['category']
            item_name = row['name']
            if check_closed(category):
                raise ValueError(f"Il reparto {category} è chiuso. Impossibile sbloccare {item_name}.")

            if DB_TYPE == "sqlite":
                q_idx = get_next_queue_index(category, cursor)
                print(f"[DEBUG] release_order_item: assigning queue_index {q_idx} to item_id {item_id}")

                cursor.execute("""
                    UPDATE order_items
                    SET status = 'PENDING', released_at = datetime('now', 'localtime'), queue_index = ?
                    WHERE id = ? AND order_id = ? AND status = 'HOLD';
                """, (q_idx, item_id, order_id))
            else:
                cursor.execute("""
                    UPDATE order_items
                    SET status = 'PENDING', released_at = datetime('now', 'localtime')
                    WHERE id = ? AND order_id = ? AND status = 'HOLD';
                """, (item_id, order_id))
        
        # We need to trigger an update to the order's updated_at timestamp
        if cursor.rowcount > 0:
            cursor.execute("""
                UPDATE orders
                SET updated_at = datetime('now', 'localtime'), sync_status = 'PENDING_PUSH'
                WHERE id = ?;
            """, (order_id,))
        
        conn.commit()
        success = cursor.rowcount > 0
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()
    return success


def get_item_category(order_item_id):
    conn = get_connection()
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT mi.category FROM order_items oi JOIN menu_items mi ON oi.menu_item_id = mi.id WHERE oi.id = ?", (order_item_id,))
        row = cursor.fetchone()
        return row['category'] if row else None
    except Exception as e:
        print(f"Error in get_item_category: {e}")
        return None
    finally:
        conn.close()


def update_order_item_status(order_item_id, new_status, qty=None):
    """
    new_status should be 'PREPARING' or 'READY' or 'DELIVERED'
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        # 1. Update the individual item status (and split if necessary)
        cursor.execute("SELECT * FROM order_items WHERE id = ?;", (order_item_id,))
        current_item = cursor.fetchone()
        if not current_item:
            raise ValueError("Order item not found.")
            
        if current_item['status'] == 'HOLD' and current_item['is_client_locked'] == 1:
            raise ValueError("Il piatto è bloccato dal cliente e non può essere avanzato.")
            
        current_qty = current_item['quantity']
        
        if qty is not None and 0 < qty < current_qty:
            # Scale down current row
            new_remaining_qty = current_qty - qty
            cursor.execute("""
                UPDATE order_items
                SET quantity = ?
                WHERE id = ?;
            """, (new_remaining_qty, order_item_id))
            
            # Determine released_at and queue_index
            new_released_at = current_item['released_at']
            new_queue_index = current_item['queue_index']
            if current_item['status'] == 'HOLD' and new_status != 'HOLD':
                new_released_at = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                # get category
                cursor.execute("SELECT category FROM menu_items WHERE id = ?", (current_item['menu_item_id'],))
                cat = cursor.fetchone()[0]
                new_queue_index = get_next_queue_index(cat, cursor)

            # Insert new row with requested status
            cursor.execute("""
                INSERT INTO order_items (order_id, menu_item_id, quantity, notes, price_at_order, status, released_at, queue_index)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?);
            """, (
                current_item['order_id'], 
                current_item['menu_item_id'], 
                qty, 
                current_item['notes'], 
                current_item['price_at_order'], 
                new_status,
                new_released_at,
                new_queue_index
            ))
        else:
            if current_item['status'] == 'HOLD' and new_status != 'HOLD':
                cursor.execute("SELECT category FROM menu_items WHERE id = ?", (current_item['menu_item_id'],))
                cat = cursor.fetchone()[0]
                new_queue_index = get_next_queue_index(cat, cursor)
                cursor.execute("""
                    UPDATE order_items
                    SET status = ?, released_at = datetime('now', 'localtime'), queue_index = ?
                    WHERE id = ?;
                """, (new_status, new_queue_index, order_item_id))
            else:
                cursor.execute("""
                    UPDATE order_items
                    SET status = ?
                    WHERE id = ?;
                """, (new_status, order_item_id))
        
        # 2. Get the order_id for this item
        cursor.execute("SELECT order_id FROM order_items WHERE id = ?;", (order_item_id,))
        row = cursor.fetchone()
        if not row:
            raise ValueError("Order item not found.")
        order_id = row['order_id']
        
        # 3. Check if all items in this order are now READY or DELIVERED
        cursor.execute("""
            SELECT COUNT(*) FROM order_items 
            WHERE order_id = ? AND status NOT IN ('READY', 'DELIVERED');
        """, (order_id,))
        unfinished_count = cursor.fetchone()[0]
        
        # 4. If all ready, check if we need to auto-deliver drinks
        if unfinished_count == 0:
            no_kitchen_cats = get_no_kitchen_categories()
            if no_kitchen_cats:
                placeholders = ', '.join(['?'] * len(no_kitchen_cats))
                
                # Check how many KITCHEN items are NOT delivered
                cursor.execute(f"""
                    SELECT COUNT(*) FROM order_items oi
                    JOIN menu_items mi ON oi.menu_item_id = mi.id
                    WHERE oi.order_id = ? AND oi.status != 'DELIVERED' 
                      AND mi.category NOT IN ({placeholders})
                """, [order_id] + no_kitchen_cats)
                kitchen_undelivered = cursor.fetchone()[0]
                
                # If all kitchen items are DELIVERED, force auto-delivery of drinks
                if kitchen_undelivered == 0:
                    cursor.execute(f"""
                        UPDATE order_items 
                        SET status = 'DELIVERED'
                        WHERE order_id = ? AND status != 'DELIVERED'
                          AND menu_item_id IN (SELECT id FROM menu_items WHERE category IN ({placeholders}))
                    """, [order_id] + no_kitchen_cats)
            
            # Recalculate undelivered_count (might have changed if drinks were auto-delivered)
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND status != 'DELIVERED';
            """, (order_id,))
            undelivered_count = cursor.fetchone()[0]
            
            new_status = 'DELIVERED' if undelivered_count == 0 else 'READY'
            cursor.execute("""
                UPDATE orders
                SET status = ?, 
                    sync_status = 'PENDING_PUSH',
                    updated_at = datetime('now', 'localtime')
                WHERE id = ?;
            """, (new_status, order_id))
        else:
            cursor.execute("""
                UPDATE orders
                SET status = 'VALIDATED',
                    sync_status = 'PENDING_PUSH',
                    updated_at = datetime('now', 'localtime')
                WHERE id = ?;
            """, (order_id,))
            
        conn.commit()
        
        # Fetch updated order details
        cursor.execute("SELECT * FROM orders WHERE id = ?;", (order_id,))
        order_row = dict(cursor.fetchone())
        return order_row
        
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def get_ready_orders():
    """
    Get orders that are either completely READY (all items prepared, waiting pickup)
    or VALIDATED (currently being prepared, so pickup manager can see progress).
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute("""
        SELECT * FROM orders 
        WHERE status IN ('VALIDATED', 'READY')
        ORDER BY status DESC, created_at ASC;
    """)
    orders = [dict(row) for row in cursor.fetchall()]
    
    for o in orders:
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (o['id'],))
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def get_public_display_orders(category=None, pending_mode="hide"):
    """
    Get public display data (only order numbers) for VALIDATED (preparing) and READY (ready) orders.
    If category is provided, filters the state based on the items in that category.
    """
    conn = get_connection()
    cursor = conn.cursor()
    
    waiting = []
    preparing = []
    ready = []
    
    # We always need all items for the order to determine if it is completely ready.
    if not category:
        cursor.execute("""
            SELECT o.order_number, o.remote_id, 
                SUM(CASE WHEN oi.status IN ('PENDING', 'HOLD') THEN 1 ELSE 0 END) as waiting_count,
                SUM(CASE WHEN oi.status = 'PREPARING' THEN 1 ELSE 0 END) as preparing_count,
                SUM(CASE WHEN oi.status = 'READY' THEN 1 ELSE 0 END) as ready_count,
                SUM(CASE WHEN oi.status = 'DELIVERED' THEN 1 ELSE 0 END) as delivered_count
            FROM orders o
            JOIN order_items oi ON oi.order_id = o.id
            WHERE o.status IN ('VALIDATED', 'READY')
            GROUP BY o.id, o.order_number, o.remote_id
            ORDER BY o.created_at ASC;
        """)
        rows = cursor.fetchall()
        for row in rows:
            total = row['waiting_count'] + row['preparing_count'] + row['ready_count'] + row['delivered_count']
            if total == 0:
                continue
                
            if row['ready_count'] > 0:

                disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                ready.append(disp_num)

            
            if pending_mode == "merge":
                if row['waiting_count'] > 0 or row['preparing_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    preparing.append(disp_num)

            elif pending_mode == "hide":
                if row['preparing_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    preparing.append(disp_num)

            elif pending_mode == "separate":
                if row['preparing_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    preparing.append(disp_num)

                elif row['waiting_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    waiting.append(disp_num)

    else:
        cursor.execute("""
            SELECT o.order_number, o.remote_id, 
                SUM(CASE WHEN oi.status IN ('PENDING', 'HOLD') THEN 1 ELSE 0 END) as waiting_count,
                SUM(CASE WHEN oi.status = 'PREPARING' THEN 1 ELSE 0 END) as preparing_count,
                SUM(CASE WHEN oi.status = 'READY' THEN 1 ELSE 0 END) as ready_count,
                SUM(CASE WHEN oi.status = 'DELIVERED' THEN 1 ELSE 0 END) as delivered_count
            FROM orders o
            JOIN order_items oi ON oi.order_id = o.id
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE mi.category = ? AND o.status IN ('VALIDATED', 'READY')
            GROUP BY o.id, o.order_number, o.remote_id
            ORDER BY o.created_at ASC;
        """, (category,))
        rows = cursor.fetchall()
        
        for row in rows:
            # Total items in this category for this order
            total = row['waiting_count'] + row['preparing_count'] + row['ready_count'] + row['delivered_count']
            if total == 0:
                continue
                
            if row['ready_count'] > 0:

                disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                ready.append(disp_num)

                
            if pending_mode == "merge":
                if row['waiting_count'] > 0 or row['preparing_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    preparing.append(disp_num)

            elif pending_mode == "hide":
                if row['preparing_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    preparing.append(disp_num)

            elif pending_mode == "separate":
                if row['preparing_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    preparing.append(disp_num)

                elif row['waiting_count'] > 0:

                    disp_num = f"{row['order_number']}W" if DB_TYPE == "mysql" or row['remote_id'] is not None else str(row['order_number'])
                    waiting.append(disp_num)

            # If they are all DELIVERED, we don't show the order
            
    # Get total quantity in queue for each category (items in validated orders that are PENDING or PREPARING)
    cursor.execute("""
        SELECT mi.category, SUM(oi.quantity) as total_qty
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status = 'VALIDATED' AND oi.status IN ('PENDING', 'PREPARING')
        GROUP BY mi.category;
    """)
    queue_rows = cursor.fetchall()
    queue_by_category = {row['category']: (row['total_qty'] or 0) for row in queue_rows}
    
    # Calculate active queue quantity (piatti in coda, excluding HOLD)
    if not category:
        queue_dishes_count = sum(queue_by_category.values())
    else:
        queue_dishes_count = queue_by_category.get(category, 0)
        
    # Calculate estimated wait time based on category metrics (with base_time = 1)
    prep_metrics = {
        'PIZZERIA': {'prep_time': 0.5, 'cooks': 2, 'base_time': 1},
        'GRIGLIA': {'prep_time': 0.5, 'cooks': 1, 'base_time': 1},
        'CUCINA': {'prep_time': 0.5, 'cooks': 2, 'base_time': 1},
        'BEVANDE': {'prep_time': 0.5, 'cooks': 1, 'base_time': 1},
        'DOLCI': {'prep_time': 0.5, 'cooks': 1, 'base_time': 1}
    }
    
    max_wait_time = 0
    if category:
        categories_to_check = [category]
    else:
        categories_to_check = list(queue_by_category.keys())
        if not categories_to_check:
            categories_to_check = list(prep_metrics.keys())
            
    for cat in categories_to_check:
        metrics = prep_metrics.get(cat, {'prep_time': 2.0, 'cooks': 1, 'base_time': 1})
        queue_qty = queue_by_category.get(cat, 0)
        estimated_wait = (queue_qty * metrics['prep_time']) / metrics['cooks'] + metrics['base_time']
        if estimated_wait > max_wait_time:
            max_wait_time = estimated_wait
            
    estimated_wait_minutes = int(round(max_wait_time))
    if queue_dishes_count == 0:
        estimated_wait_minutes = 0

    conn.close()
    return {
        "pending_mode": pending_mode,
        "waiting": waiting,
        "preparing": preparing,
        "ready": ready,
        "estimated_wait_minutes": estimated_wait_minutes,
        "queue_dishes_count": queue_dishes_count
    }

def deliver_order(order_id, category=None):
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        if category:
            cursor.execute("""
                UPDATE order_items
                SET status = 'DELIVERED'
                WHERE order_id = ? AND menu_item_id IN (
                    SELECT id FROM menu_items WHERE category = ?
                );
            """, (order_id, category))
        else:
            cursor.execute("""
                UPDATE order_items
                SET status = 'DELIVERED'
                WHERE order_id = ?;
            """, (order_id,))
            
        # Get no-kitchen categories
        no_kitchen_cats = get_no_kitchen_categories()
        
        # Check if all kitchen-enabled items in this order are now DELIVERED
        if no_kitchen_cats:
            placeholders = ', '.join(['?'] * len(no_kitchen_cats))
            cursor.execute(f"""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND status != 'DELIVERED' AND menu_item_id NOT IN (
                    SELECT id FROM menu_items WHERE category IN ({placeholders})
                );
            """, [order_id] + no_kitchen_cats)
        else:
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND status != 'DELIVERED';
            """, (order_id,))
        undelivered_kitchen_count = cursor.fetchone()[0]
        
        if undelivered_kitchen_count == 0:
            # All kitchen-enabled items are delivered. 
            # We can now automatically mark all items (including no_kitchen ones) as DELIVERED
            cursor.execute("""
                UPDATE order_items
                SET status = 'DELIVERED'
                WHERE order_id = ?;
            """, (order_id,))
            undelivered_count = 0
        else:
            # Check how many undelivered items are left total
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND status != 'DELIVERED';
            """, (order_id,))
            undelivered_count = cursor.fetchone()[0]
            
        if undelivered_count == 0:
            cursor.execute("""
                UPDATE orders 
                SET status = 'DELIVERED', 
                    sync_status = 'PENDING_PUSH',
                    updated_at = datetime('now', 'localtime') 
                WHERE id = ?;
            """, (order_id,))
        else:
            # Check if all remaining items are READY or DELIVERED (if so, order is READY)
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND status NOT IN ('READY', 'DELIVERED');
            """, (order_id,))
            unfinished_count = cursor.fetchone()[0]
            
            new_order_status = 'READY' if unfinished_count == 0 else 'VALIDATED'
            cursor.execute("""
                UPDATE orders
                SET status = ?,
                    sync_status = 'PENDING_PUSH',
                    updated_at = datetime('now', 'localtime')
                WHERE id = ?;
            """, (new_order_status, order_id))
            
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def cancel_order(order_id):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Check current status to avoid double-cancelling and double-restoring stock
        cursor.execute("SELECT status FROM orders WHERE id = ?;", (order_id,))
        o_row = cursor.fetchone()
        if not o_row or o_row['status'] == 'CANCELLED':
            return
            
        cursor.execute("""
            UPDATE orders 
            SET status = 'CANCELLED', 
                locked_by = NULL, 
                locked_at = NULL, 
                sync_status = 'PENDING_PUSH',
                updated_at = datetime('now', 'localtime') 
            WHERE id = ?;
        """, (order_id,))
        
        cursor.execute("""
            UPDATE order_items
            SET status = 'CANCELLED'
            WHERE order_id = ?;
        """, (order_id,))
        
        # Restore stock for each item if applicable
        cursor.execute("SELECT menu_item_id, quantity FROM order_items WHERE order_id = ?;", (order_id,))
        cancelled_items = cursor.fetchall()
        for ci in cancelled_items:
            m_item_id = ci['menu_item_id']
            qty = ci['quantity']
            cursor.execute("SELECT stock_limit, is_available, is_active FROM menu_items WHERE id = ?;", (m_item_id,))
            m_row = cursor.fetchone()
            if m_row and m_row['stock_limit'] is not None:
                new_stock = m_row['stock_limit'] + qty
                if m_row['stock_limit'] == 0 and m_row['is_available'] == 0 and m_row['is_active'] == 1:
                    cursor.execute("UPDATE menu_items SET stock_limit = ?, is_available = 1 WHERE id = ?;", (new_stock, m_item_id))
                else:
                    cursor.execute("UPDATE menu_items SET stock_limit = ? WHERE id = ?;", (new_stock, m_item_id))
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def calculate_wait_time(cursor, order_id, order_status, items):
    if order_status in ['READY', 'DELIVERED', 'CANCELLED']:
        return 0
        
    # Typical preparation time (minutes per item) and parallel preparation capability (cooks/stations)
    prep_metrics = {
        'PIZZERIA': {'prep_time': 0.5, 'cooks': 2, 'base_time': 1},
        'GRIGLIA': {'prep_time': 0.5, 'cooks': 1, 'base_time': 1},
        'CUCINA': {'prep_time': 0.5, 'cooks': 2, 'base_time': 1},
        'BEVANDE': {'prep_time': 0.5, 'cooks': 1, 'base_time': 1},
        'DOLCI': {'prep_time': 0.5, 'cooks': 1, 'base_time': 1}
    }
    
    # Get total quantity in queue for each category (items in validated orders that are PENDING or PREPARING)
    cursor.execute("""
        SELECT mi.category, SUM(oi.quantity) as total_qty
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.status = 'VALIDATED' AND oi.status IN ('PENDING', 'PREPARING')
        GROUP BY mi.category;
    """)
    queue_rows = cursor.fetchall()
    queue_by_category = {row['category']: (row['total_qty'] or 0) for row in queue_rows}
    
    # Calculate wait time for each category needed in our order
    max_wait_time = 0
    
    # Group our own items by category
    my_items_by_cat = {}
    for item in items:
        # If the item is already READY or CANCELLED, we don't wait for it anymore
        if item.get('status') in ['READY', 'CANCELLED']:
            continue
        cat = item.get('category')
        my_items_by_cat[cat] = my_items_by_cat.get(cat, 0) + item.get('quantity', 1)
        
    # Calculate estimated wait time for each category
    for cat, my_qty in my_items_by_cat.items():
        metrics = prep_metrics.get(cat, {'prep_time': 2.0, 'cooks': 1, 'base_time': 5})
        
        queue_qty = queue_by_category.get(cat, 0)
        
        # If our order is PENDING, our items are not yet in the validated queue, so we add them
        if order_status == 'PENDING':
            queue_qty += my_qty
            
        # Estimate: (total quantity in queue * prep time per item) / parallel cooks + base time
        estimated_wait = (queue_qty * metrics['prep_time']) / metrics['cooks'] + metrics['base_time']
        
        if estimated_wait > max_wait_time:
            max_wait_time = estimated_wait
            
    return int(round(max_wait_time))


def mark_order_printed(order_id):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        now_sql = "NOW()" if DB_TYPE == "mysql" else "datetime('now', 'localtime')"
        cursor.execute(f"""
            UPDATE orders
            SET is_printed = 1, printed_at = {now_sql}, sync_status = 'PENDING_PUSH', updated_at = {now_sql}
            WHERE id = ? AND (is_printed = 0 OR is_printed IS NULL);
        """, (order_id,))
        rows_affected = cursor.rowcount
        conn.commit()
        
        cursor.execute("SELECT id, order_number, is_printed, printed_at, pickup_pin, status FROM orders WHERE id = ?;", (order_id,))
        row = cursor.fetchone()
        order_info = dict(row) if row else None
        
        if rows_affected > 0:
            return {"success": True, "already_printed": False, "order": order_info}
        else:
            return {"success": False, "already_printed": True, "order": order_info}
    finally:
        conn.close()

def get_totem_order(code):
    conn = get_connection()
    cursor = conn.cursor()
    code_str = str(code).strip()
    clean_code = code_str.upper().replace('W', '').replace('#', '').strip()
    
    date_filter = "DATE(created_at) = CURDATE()" if DB_TYPE == "mysql" else "date(created_at) = date('now', 'localtime')"

    # Il Totem cerca ESCLUSIVAMENTE tramite il pickup_pin univoco di 4 cifre
    cursor.execute(f"""
        SELECT id FROM orders 
        WHERE pickup_pin = ? 
          AND {date_filter} 
        ORDER BY id DESC LIMIT 1;
    """, (clean_code,))
        
    row = cursor.fetchone()
    order_id = row['id'] if row else None

    conn.close()

    if order_id:
        return get_order_status(order_id)
    return None

def get_order_status(order_id):
    conn = get_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM orders WHERE id = ?;", (order_id,))
    row = cursor.fetchone()
    if not row:
        conn.close()
        return None
        
    order_row = dict(row)
    
    cursor.execute("""
        SELECT oi.*, mi.name as item_name, mi.category
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        WHERE oi.order_id = ?;
    """, (order_id,))
    order_row['items'] = [dict(row) for row in cursor.fetchall()]
    
    # Calculate and insert wait time estimate
    order_row['estimated_wait_time'] = calculate_wait_time(cursor, order_id, order_row['status'], order_row['items'])
    
    conn.close()
    return order_row


def get_delivered_orders():
    conn = get_connection()
    cursor = conn.cursor()
    
    # Get last 50 delivered orders
    cursor.execute("""
        SELECT * FROM orders 
        WHERE status = 'DELIVERED'
        ORDER BY updated_at DESC
        LIMIT 50;
    """)
    orders = [dict(row) for row in cursor.fetchall()]
    
    for o in orders:
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (o['id'],))
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def get_cashier_history():
    conn = get_connection()
    cursor = conn.cursor()
    
    # Fetch recently validated, ready, delivered, or cancelled orders (last 50)
    cursor.execute("""
        SELECT * FROM orders 
        WHERE status IN ('VALIDATED', 'READY', 'DELIVERED', 'CANCELLED')
        ORDER BY updated_at DESC
        LIMIT 50;
    """)
    orders = [dict(row) for row in cursor.fetchall()]
    
    for o in orders:
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (o['id'],))
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def reopen_order(order_id):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            UPDATE orders 
            SET status = 'PENDING', 
                validated_by = NULL,
                locked_by = NULL, 
                locked_at = NULL, 
                sync_status = 'PENDING_PUSH',
                updated_at = datetime('now', 'localtime') 
            WHERE id = ?;
        """, (order_id,))
        
        cursor.execute("""
            UPDATE order_items
            SET status = 'PENDING'
            WHERE order_id = ?;
        """, (order_id,))
        
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


def bulk_update_order_items_status(order_id, new_status, only_from_status=None, category=None):
    conn = get_connection()
    try:
        cursor = conn.cursor()
        
        # Prevent bulk advancing client-locked items in HOLD
        condition = "WHERE order_id = ? AND (is_client_locked = 0 OR is_client_locked IS NULL OR status != 'HOLD')"
        
        params = [order_id]
        
        if only_from_status:
            condition += " AND status = ?"
            params.append(only_from_status)
            
        if category and category != 'TUTTI':
            condition += " AND menu_item_id IN (SELECT id FROM menu_items WHERE category = ?)"
            params.append(category)

        # If we are advancing from HOLD to PENDING, we need to assign queue_index per category
        if only_from_status == 'HOLD' and new_status != 'HOLD':
            # Select the items first to assign queue_index
            cursor.execute(f"SELECT oi.id, mi.category FROM order_items oi JOIN menu_items mi ON oi.menu_item_id = mi.id {condition}", params)
            items_to_update = cursor.fetchall()
            
            # Group by category to share queue_index
            category_indices = {}
            for item in items_to_update:
                cat = item['category']
                if cat not in category_indices:
                    category_indices[cat] = get_next_queue_index(cat, cursor)
                
                q_idx = category_indices[cat]
                cursor.execute(f"UPDATE order_items SET status = ?, released_at = datetime('now', 'localtime'), queue_index = ? WHERE id = ?", [new_status, q_idx, item['id']])
            
            rowcount = len(items_to_update)
        else:
            # Normal bulk update (e.g. from PENDING to PREPARING)
            update_params = [new_status] + params
            cursor.execute(f"UPDATE order_items SET status = ? {condition}", update_params)
            rowcount = cursor.rowcount
            

        # --- Update Parent Order Status ---
        cursor.execute("SELECT COUNT(*) FROM order_items WHERE order_id = ? AND status NOT IN ('READY', 'DELIVERED');", (order_id,))
        unfinished_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM order_items WHERE order_id = ? AND status != 'DELIVERED';", (order_id,))
        not_delivered_count = cursor.fetchone()[0]
        
        if not_delivered_count == 0:
            cursor.execute("UPDATE orders SET status = 'DELIVERED', sync_status = 'PENDING_PUSH', updated_at = datetime('now', 'localtime') WHERE id = ?;", (order_id,))
        elif unfinished_count == 0:
            cursor.execute("UPDATE orders SET status = 'READY', sync_status = 'PENDING_PUSH', updated_at = datetime('now', 'localtime') WHERE id = ?;", (order_id,))
        else:
            cursor.execute("UPDATE orders SET sync_status = 'PENDING_PUSH', updated_at = datetime('now', 'localtime') WHERE id = ?;", (order_id,))
        # ----------------------------------

        conn.commit()
        return rowcount
    except Exception as e:
        print(f"Error in bulk_update_order_items_status: {e}")
        return False
    finally:
        conn.close()


def update_order(order_id, customer_name, customer_phone, table_number, items, wa_contact_saved=0):
    if not table_number:
        table_number = ""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        total_price = 0.0
        validated_items = []
        for item in items:
            item_id = item['menu_item_id']
            qty = item.get('quantity', 1)
            notes = item.get('notes', '')
            locked = item.get('locked', False)
            
            cursor.execute("SELECT price, name, category FROM menu_items WHERE id = ?;", (item_id,))
            menu_row = cursor.fetchone()
            if not menu_row:
                raise ValueError(f"Menu item with id {item_id} does not exist.")
            
            price = menu_row['price']
            name = menu_row['name']
            category = menu_row['category']
            total_price += price * qty
            validated_items.append({
                'menu_item_id': item_id,
                'name': name,
                'quantity': qty,
                'notes': notes,
                'price': price,
                'category': category,
                'locked': locked
            })
            
        # 2. Update the order row
        cursor.execute("""
            UPDATE orders
            SET customer_name = ?, customer_phone = ?, table_number = ?, total_price = ?, wa_contact_saved = ?, 
                sync_status = 'PENDING_PUSH',
                updated_at = datetime('now', 'localtime')
            WHERE id = ?;
        """, (customer_name, customer_phone, table_number, total_price, wa_contact_saved, order_id))
        
        # 3. Fetch existing order items
        cursor.execute("SELECT id, menu_item_id, status, is_client_locked FROM order_items WHERE order_id = ?;", (order_id,))
        existing_items = [dict(r) for r in cursor.fetchall()]
        reused_ids = set()
        
        no_kitchen_cats = get_no_kitchen_categories()
        
        for v_item in validated_items:
            mid = v_item['menu_item_id']
            # Trova un elemento esistente con lo stesso menu_item_id non ancora riutilizzato
            matched_item = next((item for item in existing_items if item['menu_item_id'] == mid and item['id'] not in reused_ids), None)
            
            is_no_kitchen = v_item['category'] in no_kitchen_cats
            is_locked = 1 if (v_item.get('locked', False) and not is_no_kitchen) else 0
            
            # Se l'elemento era bloccato dal cliente (is_client_locked == 1), manteniamo 1 se rimane bloccato.
            # Altrimenti (nuovo elemento, o bloccato ex-novo in cassa), impostiamo a 0.
            was_client_locked = matched_item and matched_item['is_client_locked'] == 1
            is_client_locked = 1 if (is_locked and was_client_locked) else 0
            
            status = 'READY' if is_no_kitchen else ('HOLD' if is_locked else 'PENDING')
            
            if matched_item:
                reused_ids.add(matched_item['id'])
                cursor.execute("""
                    UPDATE order_items 
                    SET quantity = ?, notes = ?, price_at_order = ?, status = ?, is_client_locked = ?
                    WHERE id = ?;
                """, (v_item['quantity'], v_item['notes'], v_item['price'], status, is_client_locked, matched_item['id']))
            else:
                cursor.execute("""
                    INSERT INTO order_items (order_id, menu_item_id, quantity, notes, price_at_order, status, is_client_locked)
                    VALUES (?, ?, ?, ?, ?, ?, ?);
                """, (order_id, mid, v_item['quantity'], v_item['notes'], v_item['price'], status, is_client_locked))
                
        # 4. Elimina gli elementi rimossi dall'ordine
        for item in existing_items:
            if item['id'] not in reused_ids:
                cursor.execute("DELETE FROM order_items WHERE id = ?;", (item['id'],))
            
        # 5. Dynamically adjust order status if already validated or ready
        cursor.execute("SELECT status FROM orders WHERE id = ?;", (order_id,))
        order_row = cursor.fetchone()
        current_status = order_row['status'] if order_row else 'PENDING'
        
        if current_status in ['VALIDATED', 'READY', 'DELIVERED']:
            cursor.execute("""
                SELECT COUNT(*) FROM order_items 
                WHERE order_id = ? AND status NOT IN ('READY', 'DELIVERED');
            """, (order_id,))
            unfinished_count = cursor.fetchone()[0]
            
            # Check if there are any kitchen-enabled items in this order
            if no_kitchen_cats:
                placeholders = ', '.join(['?'] * len(no_kitchen_cats))
                cursor.execute(f"""
                    SELECT COUNT(*) FROM order_items 
                    WHERE order_id = ? AND menu_item_id NOT IN (
                        SELECT id FROM menu_items WHERE category IN ({placeholders})
                    );
                """, [order_id] + no_kitchen_cats)
            else:
                cursor.execute("""
                    SELECT COUNT(*) FROM order_items 
                    WHERE order_id = ?;
                """, (order_id,))
            kitchen_items_count = cursor.fetchone()[0]
            
            if kitchen_items_count == 0:
                # Mark all as DELIVERED and set new_status = 'DELIVERED'
                cursor.execute("""
                    UPDATE order_items
                    SET status = 'DELIVERED'
                    WHERE order_id = ?;
                """, (order_id,))
                new_status = 'DELIVERED'
            elif unfinished_count == 0:
                # Check if all kitchen-enabled items are DELIVERED
                if no_kitchen_cats:
                    placeholders = ', '.join(['?'] * len(no_kitchen_cats))
                    cursor.execute(f"""
                        SELECT COUNT(*) FROM order_items 
                        WHERE order_id = ? AND status != 'DELIVERED' AND menu_item_id NOT IN (
                            SELECT id FROM menu_items WHERE category IN ({placeholders})
                        );
                    """, [order_id] + no_kitchen_cats)
                else:
                    cursor.execute("""
                        SELECT COUNT(*) FROM order_items 
                        WHERE order_id = ? AND status != 'DELIVERED';
                    """, (order_id,))
                undelivered_kitchen_count = cursor.fetchone()[0]
                
                if undelivered_kitchen_count == 0:
                    cursor.execute("""
                        UPDATE order_items
                        SET status = 'DELIVERED'
                        WHERE order_id = ?;
                    """, (order_id,))
                    new_status = 'DELIVERED'
                else:
                    cursor.execute("""
                        SELECT COUNT(*) FROM order_items 
                        WHERE order_id = ? AND status != 'DELIVERED';
                    """, (order_id,))
                    undelivered_count = cursor.fetchone()[0]
                    new_status = 'DELIVERED' if undelivered_count == 0 else 'READY'
            else:
                new_status = 'VALIDATED'
                
            if current_status != new_status:
                cursor.execute("""
                    UPDATE orders
                    SET status = ?,
                        sync_status = 'PENDING_PUSH',
                        updated_at = datetime('now', 'localtime')
                    WHERE id = ?;
                """, (new_status, order_id))
            
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()
        
    return get_order_status(order_id)

def validate_login(username, password):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Clean up expired sessions first
        cursor.execute("DELETE FROM sessions WHERE expires_at < datetime('now', 'localtime');")
        conn.commit()
        
        pw_hash = hash_password(password)
        cursor.execute("""
            SELECT id, username, role, sector
            FROM users
            WHERE username = ? AND password_hash = ?;
        """, (username, pw_hash))
        user_row = cursor.fetchone()
        if not user_row:
            return None
            
        user = dict(user_row)
        
        token = secrets.token_hex(16)
        
        cursor.execute("""
            INSERT INTO sessions (token, user_id, expires_at)
            VALUES (?, ?, datetime('now', 'localtime', '+24 hours'));
        """, (token, user['id']))
        conn.commit()
        
        user['token'] = token
        return user
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def logout(token):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM sessions WHERE token = ?;", (token,))
        conn.commit()
    finally:
        conn.close()

def get_user_by_token(token):
    if not token:
        return None
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Clean up expired sessions
        cursor.execute("DELETE FROM sessions WHERE expires_at < datetime('now', 'localtime');")
        conn.commit()
        
        cursor.execute("""
            SELECT u.id, u.username, u.role, u.sector
            FROM sessions s
            JOIN users u ON s.user_id = u.id
            WHERE s.token = ? AND s.expires_at > datetime('now', 'localtime');
        """, (token,))
        row = cursor.fetchone()
        if row:
            return dict(row)
        return None
    finally:
        conn.close()

def get_all_users():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT id, username, role, sector, created_at
        FROM users
        ORDER BY role ASC, username ASC;
    """)
    rows = cursor.fetchall()
    conn.close()
    return [dict(row) for row in rows]

def create_user(username, password, role, sector=None):
    if not username or not password or not role:
        raise ValueError("Campi obbligatori mancanti.")
    if role not in ['SUPER_ADMIN', 'ADMIN', 'CASSIERE', 'CUCINA']:
        raise ValueError("Ruolo non valido.")
        
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Uniqueness of SUPER_ADMIN
        if role == 'SUPER_ADMIN':
            cursor.execute("SELECT COUNT(*) FROM users WHERE role = 'SUPER_ADMIN';")
            if cursor.fetchone()[0] > 0:
                raise ValueError("Può esistere un solo Super Amministratore nel sistema.")
                
        pw_hash = hash_password(password)
        cursor.execute("""
            INSERT INTO users (username, password_hash, role, sector)
            VALUES (?, ?, ?, ?);
        """, (username, pw_hash, role, sector))
        conn.commit()
        new_id = cursor.lastrowid
        return new_id
    except DB_INTEGRITY_ERRORS:
        raise ValueError(f"L'utente '{username}' esiste già.")
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def update_user(user_id, username, password=None, role=None, sector=None):
    if not username or not role:
        raise ValueError("Campi obbligatori mancanti.")
    if role not in ['SUPER_ADMIN', 'ADMIN', 'CASSIERE', 'CUCINA']:
        raise ValueError("Ruolo non valido.")
        
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Uniqueness of SUPER_ADMIN
        if role == 'SUPER_ADMIN':
            cursor.execute("SELECT COUNT(*) FROM users WHERE role = 'SUPER_ADMIN' AND id != ?;", (user_id,))
            if cursor.fetchone()[0] > 0:
                raise ValueError("Può esistere un solo Super Amministratore nel sistema.")
                
        # Prevent demoting the only SUPER_ADMIN
        cursor.execute("SELECT role FROM users WHERE id = ?;", (user_id,))
        old_role_row = cursor.fetchone()
        if old_role_row and old_role_row['role'] == 'SUPER_ADMIN' and role != 'SUPER_ADMIN':
            raise ValueError("Impossibile modificare il ruolo dell'unico Super Amministratore.")
            
        if password and password.strip():
            pw_hash = hash_password(password)
            cursor.execute("""
                UPDATE users
                SET username = ?, password_hash = ?, role = ?, sector = ?
                WHERE id = ?;
            """, (username, pw_hash, role, sector, user_id))
        else:
            cursor.execute("""
                UPDATE users
                SET username = ?, role = ?, sector = ?
                WHERE id = ?;
            """, (username, role, sector, user_id))
        conn.commit()
    except DB_INTEGRITY_ERRORS:
        raise ValueError(f"L'utente '{username}' esiste già.")
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def delete_user(user_id, current_user_id):
    if int(user_id) == int(current_user_id):
        raise ValueError("Impossibile eliminare l'utente attualmente loggato.")
        
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT role FROM users WHERE id = ?;", (user_id,))
        row = cursor.fetchone()
        
        # Prevent deleting the SUPER_ADMIN
        if row and row['role'] == 'SUPER_ADMIN':
            raise ValueError("Impossibile eliminare il Super Amministratore (Senior).")
            
        # Check if this is the last admin
        if row and row['role'] == 'ADMIN':
            cursor.execute("SELECT COUNT(*) FROM users WHERE role = 'ADMIN';")
            admin_count = cursor.fetchone()[0]
            if admin_count <= 1:
                raise ValueError("Impossibile eliminare l'ultimo amministratore.")
                
        cursor.execute("DELETE FROM users WHERE id = ?;", (user_id,))
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


# ==========================================
# HYBRID SYNC DB HELPER FUNCTIONS
# ==========================================

def get_unsynced_local_updates():
    conn = get_connection()
    cursor = conn.cursor()
    # Recupera tutti gli ordini locali che richiedono sincronizzazione
    cursor.execute("SELECT * FROM orders WHERE sync_status = 'PENDING_PUSH';")
    orders = [dict(row) for row in cursor.fetchall()]
    
    for o in orders:
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (o['id'],))
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def mark_local_orders_synced(order_ids):
    if not order_ids:
        return
    conn = get_connection()
    cursor = conn.cursor()
    try:
        placeholders = ",".join("?" for _ in order_ids)
        cursor.execute(f"""
            UPDATE orders
            SET sync_status = 'SYNCED'
            WHERE id IN ({placeholders});
        """, order_ids)
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def insert_synced_order(remote_id, order_number, customer_name, customer_phone, table_number, total_price, status, created_at, items, pickup_pin=None, is_printed=0, printed_at=None):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Verifica se l'ordine remoto è già presente localmente
        cursor.execute("SELECT * FROM orders WHERE remote_id = ?;", (remote_id,))
        existing = cursor.fetchone()
        if existing:
            order_id = existing['id']
            # Se per qualche motivo il numero d'ordine locale è diverso da quello remoto, lo aggiorniamo per allinearlo
            if existing['order_number'] != order_number:
                cursor.execute("UPDATE orders SET order_number = ? WHERE id = ?;", (order_number, order_id))
            
            # Sincronizza PIN e stato stampa se presenti
            if pickup_pin and existing['pickup_pin'] != pickup_pin:
                cursor.execute("UPDATE orders SET pickup_pin = ? WHERE id = ?;", (pickup_pin, order_id))
            if is_printed and not existing['is_printed']:
                cursor.execute("UPDATE orders SET is_printed = ?, printed_at = ? WHERE id = ?;", (is_printed, printed_at, order_id))
            
            # Aggiorna lo stato dei piatti locali se sono stati sbloccati sul cloud
            for item in items:
                r_menu_item_id = item['menu_item_id']
                r_notes = item.get('notes', '')
                r_status = item.get('status', 'PENDING')
                
                if r_status != 'HOLD':
                    # Controlla se ci sono item in HOLD da sbloccare e recupera la categoria
                    cursor.execute("""
                        SELECT oi.id, mi.category 
                        FROM order_items oi
                        JOIN menu_items mi ON oi.menu_item_id = mi.id
                        WHERE oi.order_id = ? AND oi.menu_item_id = ? 
                          AND COALESCE(oi.notes, '') = COALESCE(?, '') AND oi.status = 'HOLD'
                    """, (order_id, r_menu_item_id, r_notes))
                    
                    rows_to_unlock = cursor.fetchall()
                    if rows_to_unlock:
                        for row_to_unlock in rows_to_unlock:
                            category = row_to_unlock['category']
                            q_idx = get_next_queue_index(category, cursor)
                            cursor.execute("""
                                UPDATE order_items
                                SET status = 'PENDING', is_client_locked = 0, 
                                    released_at = COALESCE(released_at, datetime('now', 'localtime')),
                                    queue_index = ?
                                WHERE id = ?;
                            """, (q_idx, row_to_unlock['id']))
                            
                        # Mark the order for push so the new queue_index goes back to remote
                        cursor.execute("UPDATE orders SET sync_status = 'PENDING_PUSH', updated_at = datetime('now', 'localtime') WHERE id = ?;", (order_id,))
            conn.commit()

            cursor.execute("SELECT * FROM orders WHERE id = ?;", (order_id,))
            order_row = dict(cursor.fetchone())
            cursor.execute("""
                SELECT oi.*, mi.name as item_name, mi.category
                FROM order_items oi
                JOIN menu_items mi ON oi.menu_item_id = mi.id
                WHERE oi.order_id = ?;
            """, (order_id,))
            order_row['items'] = [dict(row) for row in cursor.fetchall()]
            return order_row

        # Inserisce l'ordine con remote_id
        initial_sync_status = 'SYNCED'
        no_kitchen_cats = get_no_kitchen_categories()
        category_indices = {}

        cursor.execute("""
            INSERT INTO orders (order_number, customer_name, customer_phone, table_number, total_price, status, remote_id, sync_status, created_at, updated_at, pickup_pin, is_printed, printed_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
        """, (order_number, customer_name, customer_phone, table_number, total_price, status, remote_id, initial_sync_status, created_at, created_at, pickup_pin, is_printed, printed_at))
        
        order_id = cursor.lastrowid
        assigned_local_queues = False
        
        # Inserisce i piatti associati
        for item in items:
            cursor.execute("SELECT id, category FROM menu_items WHERE id = ?;", (item['menu_item_id'],))
            menu_row = cursor.fetchone()
            if not menu_row:
                cursor.execute("SELECT id, category FROM menu_items WHERE name = ?;", (item.get('item_name'),))
                menu_row = cursor.fetchone()
                if menu_row:
                    item_id = menu_row['id']
                    item_category = menu_row['category']
                else:
                    # Inserisce un piatto fittizio temporaneo se manca (fallback di sicurezza)
                    cursor.execute("""
                        INSERT INTO menu_items (name, price, category, is_available)
                        VALUES (?, ?, ?, 1);
                    """, (item.get('item_name', 'Piatto Sconosciuto'), item.get('price_at_order', 0.0), item.get('category', 'CUCINA')))
                    item_id = cursor.lastrowid
                    item_category = item.get('category', 'CUCINA')
            else:
                item_id = menu_row['id']
                item_category = menu_row['category']
                
            item_status = item.get('status', 'PENDING')
            item_locked = item.get('is_client_locked', 0)
            q_idx = item.get('queue_index')

            # Se l'ordine è già convalidato (es. pagato online), assegna l'indice di coda locale autorevole per la cucina
            if status in ('VALIDATED', 'READY') and item_status != 'HOLD' and not item_locked:
                if not no_kitchen_cats or item_category not in no_kitchen_cats:
                    if not q_idx or q_idx <= 0:
                        if item_category not in category_indices:
                            category_indices[item_category] = get_next_queue_index(item_category, cursor)
                        q_idx = category_indices[item_category]
                        assigned_local_queues = True

            cursor.execute("""
                INSERT INTO order_items (order_id, menu_item_id, quantity, notes, price_at_order, status, is_client_locked, queue_index, released_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
            """, (order_id, item_id, item['quantity'], item.get('notes', ''), item['price_at_order'], item_status, item_locked, q_idx, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') if q_idx else None))
            
            # Decremento dello stock limit locale per i piatti sincronizzati
            cursor.execute("SELECT stock_limit, is_available, is_active FROM menu_items WHERE id = ?;", (item_id,))
            m_row = cursor.fetchone()
            if m_row and m_row['stock_limit'] is not None:
                qty = item['quantity']
                new_stock = max(0, m_row['stock_limit'] - qty)
                if new_stock == 0:
                    cursor.execute("UPDATE menu_items SET stock_limit = 0, is_available = 0 WHERE id = ?;", (item_id,))
                else:
                    cursor.execute("UPDATE menu_items SET stock_limit = ? WHERE id = ?;", (new_stock, item_id))
            
        if assigned_local_queues:
            cursor.execute("UPDATE orders SET sync_status = 'PENDING_PUSH', updated_at = datetime('now', 'localtime') WHERE id = ?;", (order_id,))

        conn.commit()
        
        # Restituisce l'ordine inserito
        cursor.execute("SELECT * FROM orders WHERE id = ?;", (order_id,))
        order_row = dict(cursor.fetchone())
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (order_id,))
        order_row['items'] = [dict(row) for row in cursor.fetchall()]
        return order_row
        
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def get_unpulled_remote_orders():
    conn = get_connection()
    cursor = conn.cursor()
    cleanup_expired_pending_orders(cursor)
    conn.commit()
    # Ordini sul Cloud non ancora sincronizzati (is_synced = 0 AND status = 'PENDING')
    # O ordini che hanno ricevuto aggiornamenti dal cliente (sync_status = 'PENDING_PUSH')
    cursor.execute("""
        SELECT * FROM orders 
        WHERE (is_synced = 0 AND status = 'PENDING') 
           OR (sync_status = 'PENDING_PUSH');
    """)
    orders = [dict(row) for row in cursor.fetchall()]
    
    for o in orders:
        cursor.execute("""
            SELECT oi.*, mi.name as item_name, mi.category
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            WHERE oi.order_id = ?;
        """, (o['id'],))
        o['items'] = [dict(row) for row in cursor.fetchall()]
        
    conn.close()
    return orders

def get_remote_order_by_pin(code_or_pin):
    """
    Used on Remote Cloud to fetch a single order on-demand by pickup_pin, order_number or id.
    Returns complete order dict with items, or None.
    """
    if not code_or_pin:
        return None
    conn = get_connection()
    cursor = conn.cursor()
    code_str = str(code_or_pin).strip()
    clean_code = code_str.upper().replace('W', '').replace('#', '').strip()
    num_val = int(clean_code) if clean_code.isdigit() else -1
    date_filter = "DATE(created_at) = CURDATE()" if DB_TYPE == "mysql" else "date(created_at) = date('now', 'localtime')"

    cursor.execute(f"""
        SELECT * FROM orders 
        WHERE (pickup_pin = ? OR order_number = ? OR id = ?)
          AND {date_filter} 
        ORDER BY id DESC LIMIT 1;
    """, (clean_code, clean_code, num_val))
    
    row = cursor.fetchone()
    if not row:
        conn.close()
        return None

    order_row = dict(row)
    cursor.execute("""
        SELECT oi.*, mi.name as item_name, mi.category
        FROM order_items oi
        JOIN menu_items mi ON oi.menu_item_id = mi.id
        WHERE oi.order_id = ?;
    """, (order_row['id'],))
    order_row['items'] = [dict(r) for r in cursor.fetchall()]
    conn.close()
    return order_row


def confirm_remote_orders_synced(confirmations):
    if not confirmations:
        return
    conn = get_connection()
    for conf in confirmations:
        # Ogni conferma è indipendente: un errore su un ordine non blocca gli altri
        try:
            cursor = conn.cursor()
            remote_id = conf['remote_id']
            # Imposta solo is_synced=1 — non sovrascrivere order_number del NAS
            # (il Mac ha una numerazione locale diversa da quella del NAS)
            # Inoltre azzera sync_status sul server remoto cloud
            cursor.execute("""
                UPDATE orders
                SET is_synced = 1, sync_status = 'SYNCED'
                WHERE id = ?;
            """, (remote_id,))
            conn.commit()
        except Exception as e:
            try:
                conn.rollback()
            except Exception:
                pass
            print(f"[Sync Confirm] Errore conferma ordine remote_id={conf.get('remote_id')}: {e}")
    conn.close()

def update_remote_order_status(remote_id, status, items=None, is_printed=None, printed_at=None):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Recupera lo stato attuale dell'ordine prima dell'aggiornamento
        cursor.execute("SELECT status, is_printed FROM orders WHERE id = ?;", (remote_id,))
        old_status_row = cursor.fetchone()
        old_status = old_status_row['status'] if old_status_row else None
        
        now_sql = "NOW()" if DB_TYPE == "mysql" else "datetime('now', 'localtime')"
        if is_printed is not None:
            cursor.execute(f"""
                UPDATE orders
                SET status = ?, is_printed = ?, printed_at = COALESCE(?, printed_at), updated_at = {now_sql}, sync_status = 'SYNCED'
                WHERE id = ?;
            """, (status, is_printed, printed_at, remote_id))
        else:
            cursor.execute(f"""
                UPDATE orders
                SET status = ?, updated_at = {now_sql}, sync_status = 'SYNCED'
                WHERE id = ?;
            """, (status, remote_id))
        
        if cursor.rowcount > 0:
            # Se l'ordine è stato annullato, ripristina lo stock degli articoli associati anche sul remoto
            if status == 'CANCELLED' and old_status != 'CANCELLED':
                cursor.execute("SELECT menu_item_id, quantity FROM order_items WHERE order_id = ?;", (remote_id,))
                cancelled_items = cursor.fetchall()
                for ci in cancelled_items:
                    m_item_id = ci['menu_item_id']
                    qty = ci['quantity']
                    cursor.execute("SELECT stock_limit, is_available, is_active FROM menu_items WHERE id = ?;", (m_item_id,))
                    m_row = cursor.fetchone()
                    if m_row and m_row['stock_limit'] is not None:
                        new_stock = m_row['stock_limit'] + qty
                        if m_row['stock_limit'] == 0 and m_row['is_available'] == 0 and m_row['is_active'] == 1:
                            cursor.execute("UPDATE menu_items SET stock_limit = ?, is_available = 1 WHERE id = ?;", (new_stock, m_item_id))
                        else:
                            cursor.execute("UPDATE menu_items SET stock_limit = ? WHERE id = ?;", (new_stock, m_item_id))

            if items is not None:
                # Recreate order items to exactly mirror local state (handles partial splits/releases)
                cursor.execute("DELETE FROM order_items WHERE order_id = ?;", (remote_id,))
                for item in items:
                    cursor.execute("""
                        INSERT INTO order_items (order_id, menu_item_id, quantity, notes, price_at_order, status, is_client_locked, queue_index)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?);
                    """, (remote_id, item['menu_item_id'], item.get('quantity', 1), item.get('notes', ''), item.get('price_at_order', 0.0), item.get('status', 'PENDING'), item.get('is_client_locked', 0), item.get('queue_index')))
            else:
                if status == 'DELIVERED':
                    cursor.execute("UPDATE order_items SET status = 'READY' WHERE order_id = ?;", (remote_id,))
                elif status == 'VALIDATED':
                    cursor.execute("UPDATE order_items SET status = 'PENDING' WHERE order_id = ?;", (remote_id,))
                elif status == 'READY':
                    cursor.execute("UPDATE order_items SET status = 'READY' WHERE order_id = ?;", (remote_id,))
            
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def sync_menu_items(items):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        incoming_ids = [item['id'] for item in items]
        
        # 1. Update or Insert incoming items
        for item in items:
            cursor.execute("SELECT id FROM menu_items WHERE id = ?;", (item['id'],))
            exists = cursor.fetchone()
            if exists:
                cursor.execute("""
                    UPDATE menu_items
                    SET name = ?, description = ?, price = ?, category = ?, is_available = ?, is_active = ?, stock_limit = ?
                    WHERE id = ?;
                """, (item['name'], item.get('description', ''), item['price'], item['category'], item.get('is_available', 1), item.get('is_active', 1), item.get('stock_limit'), item['id']))
            else:
                cursor.execute("""
                    INSERT INTO menu_items (id, name, description, price, category, is_available, is_active, stock_limit)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?);
                """, (item['id'], item['name'], item.get('description', ''), item['price'], item['category'], item.get('is_available', 1), item.get('is_active', 1), item.get('stock_limit')))
        
        # 2. Mark items not in the incoming list as unavailable (or delete if not referenced)
        cursor.execute("SELECT id FROM menu_items;")
        db_ids = [row['id'] for row in cursor.fetchall()]
        
        for db_id in db_ids:
            if db_id not in incoming_ids:
                # Check if referenced in order_items
                cursor.execute("SELECT COUNT(*) FROM order_items WHERE menu_item_id = ?;", (db_id,))
                referenced = cursor.fetchone()[0] > 0
                if referenced:
                    cursor.execute("UPDATE menu_items SET is_available = 0, is_active = 0 WHERE id = ?;", (db_id,))
                else:
                    cursor.execute("DELETE FROM menu_items WHERE id = ?;", (db_id,))
                    
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def reset_all_product_stock():
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Re-attiva solo i piatti disattivati automaticamente a causa del fine stock (stock_limit = 0)
        cursor.execute("UPDATE menu_items SET is_available = 1 WHERE stock_limit = 0 AND is_available = 0 AND is_active = 1;")
        # Imposta lo stock limit di tutti i piatti attivi a NULL (illimitato)
        cursor.execute("UPDATE menu_items SET stock_limit = NULL WHERE is_active = 1;")
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def save_daily_checkout(date_str, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, denominations, expenses, notes=None):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Check if already exists
        cursor.execute("SELECT id FROM daily_checkouts WHERE date = ?;", (date_str,))
        row = cursor.fetchone()
        
        if row:
            checkout_id = row['id']
            # Update main table
            cursor.execute("""
                UPDATE daily_checkouts
                SET cassa_iniziale = ?, pos_amount = ?, soldi_da_versare = ?, quota_tombola = ?, notes = ?, updated_at = datetime('now', 'localtime')
                WHERE id = ?;
            """, (cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, notes, checkout_id))
            
            # Delete old denominations and expenses
            cursor.execute("DELETE FROM checkout_denominations WHERE checkout_id = ?;", (checkout_id,))
            cursor.execute("DELETE FROM checkout_expenses WHERE checkout_id = ?;", (checkout_id,))
        else:
            # Insert new
            cursor.execute("""
                INSERT INTO daily_checkouts (date, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, notes)
                VALUES (?, ?, ?, ?, ?, ?);
            """, (date_str, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, notes))
            checkout_id = cursor.lastrowid
            
        # Insert denominations
        for d in denominations:
            cursor.execute("""
                INSERT INTO checkout_denominations (checkout_id, count_type, denomination, quantity)
                VALUES (?, ?, ?, ?);
            """, (checkout_id, d['count_type'], d['denomination'], d['quantity']))
            
        # Insert expenses
        for e in expenses:
            pm = e.get('payment_method') or 'CONTANTI'
            cursor.execute("""
                INSERT INTO checkout_expenses (checkout_id, amount, supplier, description, expense_type, payment_method)
                VALUES (?, ?, ?, ?, ?, ?);
            """, (checkout_id, e['amount'], e['supplier'], e['description'], e['expense_type'], pm))
            
        conn.commit()
        return checkout_id
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def get_daily_checkout(date_str):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM daily_checkouts WHERE date = ?;", (date_str,))
        row = cursor.fetchone()
        if not row:
            return None
            
        checkout = dict(row)
        checkout_id = checkout['id']
        
        # Get denominations
        cursor.execute("SELECT count_type, denomination, quantity FROM checkout_denominations WHERE checkout_id = ?;", (checkout_id,))
        checkout['denominations'] = [dict(r) for r in cursor.fetchall()]
        
        # Get expenses
        cursor.execute("SELECT amount, supplier, description, expense_type, COALESCE(payment_method, 'CONTANTI') as payment_method FROM checkout_expenses WHERE checkout_id = ?;", (checkout_id,))
        checkout['expenses'] = [dict(r) for r in cursor.fetchall()]
        
        return checkout
    finally:
        conn.close()

def get_manifestation_summary():
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # Get daily checkout summaries
        cursor.execute("""
            SELECT id, date, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, notes
            FROM daily_checkouts
            ORDER BY date ASC;
        """)
        rows = cursor.fetchall()
        checkouts = [dict(r) for r in rows]
        
        for c in checkouts:
            c_id = c['id']
            # Sum of counted cash at closing (CHIUSURA)
            cursor.execute("""
                SELECT SUM(denomination * quantity)
                FROM checkout_denominations
                WHERE checkout_id = ? AND count_type = 'CHIUSURA';
            """, (c_id,))
            cash_chiusura = cursor.fetchone()[0] or 0.0
            
            c['cash_chiusura'] = cash_chiusura
            
            # Sum of expenses (Contanti vs Non-Contanti)
            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND UPPER(COALESCE(payment_method, 'CONTANTI')) = 'CONTANTI';
            """, (c_id,))
            tot_exp_contanti = cursor.fetchone()[0] or 0.0
            c['totale_spese_contanti'] = tot_exp_contanti

            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND UPPER(COALESCE(payment_method, 'CONTANTI')) != 'CONTANTI';
            """, (c_id,))
            tot_exp_non_contanti = cursor.fetchone()[0] or 0.0
            c['totale_spese_non_contanti'] = tot_exp_non_contanti
            
            tot_expenses = tot_exp_contanti + tot_exp_non_contanti
            c['totale_spese'] = tot_expenses
            
            c['ricavo_serata'] = cash_chiusura + c['pos_amount'] - c['cassa_iniziale']
            
            # Sub-categories of expenses
            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND expense_type = 'ORDINARIA';
            """, (c_id,))
            c['totale_spese_ordinarie'] = cursor.fetchone()[0] or 0.0
            
            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND expense_type = 'STRAORDINARIA';
            """, (c_id,))
            c['totale_spese_straordinarie'] = cursor.fetchone()[0] or 0.0

            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND expense_type = 'ASSOCIAZIONE';
            """, (c_id,))
            c['totale_spese_associazione'] = cursor.fetchone()[0] or 0.0

            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND expense_type = 'ATTREZZATURE';
            """, (c_id,))
            c['totale_spese_attrezzature'] = cursor.fetchone()[0] or 0.0

            cursor.execute("""
                SELECT SUM(amount)
                FROM checkout_expenses
                WHERE checkout_id = ? AND expense_type = 'MANUTENZIONE';
            """, (c_id,))
            c['totale_spese_manutenzione'] = cursor.fetchone()[0] or 0.0

            # Sum of next night cash
            cursor.execute("""
                SELECT SUM(denomination * quantity)
                FROM checkout_denominations
                WHERE checkout_id = ? AND count_type = 'PROSSIMA_SERATA';
            """, (c_id,))
            c['cassa_prossima_serata'] = cursor.fetchone()[0] or 0.0

            # Sum of remaining cash
            cursor.execute("""
                SELECT SUM(denomination * quantity)
                FROM checkout_denominations
                WHERE checkout_id = ? AND count_type = 'RIMANENTI';
            """, (c_id,))
            c['cassa_rimanente'] = cursor.fetchone()[0] or 0.0
            
            # Now calculate guadagno_netto as cash_chiusura + pos_amount - tot_expenses - cassa_prossima_serata (A + B - C - D)
            c['guadagno_netto'] = cash_chiusura + c['pos_amount'] - tot_expenses - c['cassa_prossima_serata']
            
        return checkouts
    finally:
        conn.close()

def get_sales_analytics(start_date=None, end_date=None, category=None):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        # We only consider orders that have been processed by the cashier (VALIDATED, PREPARING, READY, DELIVERED)
        # We don't use 'status IN (...)' with lowercase to be safe. We'll uppercase before comparison or just check != CANCELLED and != PENDING/HOLD
        # The user requested 'solo quelli validati', meaning the ones that passed the cashier.
        base_where = "UPPER(o.status) IN ('VALIDATED', 'PREPARING', 'READY', 'DELIVERED')"
        params = []
        
        if start_date:
            base_where += " AND date(o.created_at) >= ?"
            params.append(start_date)
        if end_date:
            base_where += " AND date(o.created_at) <= ?"
            params.append(end_date)
            
        category_where = ""
        cat_params = []
        if category and category.upper() != 'ALL':
            category_where = " AND UPPER(m.category) = UPPER(?)"
            cat_params.append(category)

        # 1. Total Revenue and Total Items Sold
        cursor.execute(f"""
            SELECT 
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity * oi.price_at_order ELSE 0 END) as total_revenue,
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity ELSE 0 END) as total_items
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN menu_items m ON oi.menu_item_id = m.id
            WHERE {base_where} {category_where}
        """, tuple(params + cat_params))
        totals = cursor.fetchone()
        total_revenue = totals[0] or 0.0
        total_items = totals[1] or 0

        # Total Orders count (excluding staff/free)
        cursor.execute(f"""
            SELECT COUNT(DISTINCT o.id)
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN menu_items m ON oi.menu_item_id = m.id
            WHERE {base_where} {category_where} AND UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO')
        """, tuple(params + cat_params))
        total_orders = cursor.fetchone()[0] or 0

        # 2. Sales by Category
        cursor.execute(f"""
            SELECT 
                m.category,
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity * oi.price_at_order ELSE 0 END) as revenue,
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity ELSE 0 END) as items
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN menu_items m ON oi.menu_item_id = m.id
            WHERE {base_where}
            GROUP BY m.category
            ORDER BY revenue DESC
        """, tuple(params))
        by_category = [dict(r) for r in cursor.fetchall()]

        # 3. Top Selling Items
        cursor.execute(f"""
            SELECT 
                m.name,
                m.category,
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity ELSE 0 END) as qty,
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity * oi.price_at_order ELSE 0 END) as revenue,
                SUM(CASE WHEN UPPER(o.payment_type) IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity ELSE 0 END) as free_qty
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN menu_items m ON oi.menu_item_id = m.id
            WHERE {base_where} {category_where}
            GROUP BY m.id
            ORDER BY qty DESC
        """, tuple(params + cat_params))
        top_items = [dict(r) for r in cursor.fetchall()]

        # 4. Sales Trend (by day)
        cursor.execute(f"""
            SELECT 
                date(o.created_at) as sales_date,
                SUM(CASE WHEN UPPER(o.payment_type) NOT IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity * oi.price_at_order ELSE 0 END) as revenue
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN menu_items m ON oi.menu_item_id = m.id
            WHERE {base_where} {category_where}
            GROUP BY sales_date
            ORDER BY sales_date ASC
        """, tuple(params + cat_params))
        by_date = [dict(r) for r in cursor.fetchall()]

        # Total Staff Orders count and theoretical revenue
        cursor.execute(f"""
            SELECT 
                COUNT(DISTINCT o.id) as staff_orders,
                SUM(oi.quantity) as staff_items,
                SUM(oi.quantity * m.price) as staff_revenue
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN menu_items m ON oi.menu_item_id = m.id
            WHERE {base_where} {category_where} AND UPPER(o.payment_type) IN ('PERSONALE', 'OMAGGIO')
        """, tuple(params + cat_params))
        staff_row = cursor.fetchone()
        total_staff_orders = staff_row[0] or 0
        total_staff_items = staff_row[1] or 0
        total_staff_revenue = staff_row[2] or 0.0

        return {
            "total_revenue": total_revenue,
            "total_items": total_items,
            "total_orders": total_orders,
            "by_category": by_category,
            "top_items": top_items,
            "by_date": by_date,
            "total_staff_orders": total_staff_orders,
            "total_staff_items": total_staff_items,
            "total_staff_revenue": total_staff_revenue
        }
    except Exception as e:
        print(f"Error in get_sales_analytics: {e}")
        return {
            "total_revenue": 0, "total_items": 0, "total_orders": 0,
            "by_category": [], "top_items": [], "by_date": [],
            "total_staff_orders": 0, "total_staff_items": 0, "total_staff_revenue": 0
        }
    finally:
        conn.close()

def clear_all_orders():
    conn = get_connection()
    cursor = conn.cursor()
    try:
        if DB_TYPE == "mysql":
            cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
            cursor.execute("DELETE FROM order_items;")
            cursor.execute("DELETE FROM orders;")
            cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")
        else:
            cursor.execute("PRAGMA foreign_keys = OFF;")
            cursor.execute("DELETE FROM order_items;")
            cursor.execute("DELETE FROM orders;")
            cursor.execute("PRAGMA foreign_keys = ON;")
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


def delete_orders_by_day(date_str):
    """
    Deletes all orders and their associated order items for a specific day.
    date_str format: 'YYYY-MM-DD'
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        if DB_TYPE == "mysql":
            cursor.execute("SELECT id FROM orders WHERE DATE(created_at) = %s;", (date_str,))
            order_ids = [row[0] for row in cursor.fetchall()]
            if not order_ids:
                return 0
            cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
            format_strings = ','.join('%s' for _ in order_ids)
            cursor.execute(f"DELETE FROM order_items WHERE order_id IN ({format_strings});", tuple(order_ids))
            cursor.execute(f"DELETE FROM orders WHERE id IN ({format_strings});", tuple(order_ids))
            cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")
        else:
            cursor.execute("SELECT id FROM orders WHERE date(created_at) = ?;", (date_str,))
            order_ids = [row[0] for row in cursor.fetchall()]
            if not order_ids:
                return 0
            cursor.execute("PRAGMA foreign_keys = OFF;")
            format_strings = ','.join('?' for _ in order_ids)
            cursor.execute(f"DELETE FROM order_items WHERE order_id IN ({format_strings});", tuple(order_ids))
            cursor.execute(f"DELETE FROM orders WHERE id IN ({format_strings});", tuple(order_ids))
            cursor.execute("PRAGMA foreign_keys = ON;")
        conn.commit()
        return len(order_ids)
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()



def checkpoint_db():
    if DB_TYPE == "sqlite":
        try:
            conn = get_connection()
            cursor = conn.cursor()
            cursor.execute("PRAGMA wal_checkpoint(TRUNCATE);")
            conn.close()
            print("[DB] Checkpoint completato con successo (WAL flushed).")
        except Exception as e:
            print(f"[DB] Errore durante il checkpoint: {e}")


def get_sagra_stats():
    """
    Retrieves key performance indicators (KPIs) and flow statistics.
    Processes dates in Python to be database-agnostic.
    """
    import datetime
    conn = get_connection()
    cursor = conn.cursor()
    
    try:
        # 1. General order stats
        cursor.execute("SELECT status, total_price, payment_type FROM orders;")
        order_rows = cursor.fetchall()
        
        status_counts = {'PENDING': 0, 'VALIDATED': 0, 'READY': 0, 'DELIVERED': 0, 'CANCELLED': 0}
        status_revenue = status_counts.copy()
        total_orders = 0
        total_revenue = 0.0
        
        for row in order_rows:
            status = row['status']
            price = row['total_price'] or 0.0
            payment_type = row['payment_type']
            if status in status_counts:
                status_counts[status] += 1
                if payment_type not in ('PERSONALE', 'OMAGGIO'):
                    status_revenue[status] += price
                total_orders += 1
                if status != 'CANCELLED' and payment_type not in ('PERSONALE', 'OMAGGIO'):
                    total_revenue += price

        # 2. Get order items timestamps to calculate durations in Python
        cursor.execute("""
            SELECT o.id, o.status, o.created_at, o.updated_at, oi.released_at
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id;
        """)
        rows = cursor.fetchall()
        
        order_data = {}
        for row in rows:
            oid = row['id']
            if oid not in order_data:
                order_data[oid] = {
                    'status': row['status'],
                    'created_at': row['created_at'],
                    'updated_at': row['updated_at'],
                    'released_times': []
                }
            if row['released_at']:
                order_data[oid]['released_times'].append(row['released_at'])

        def parse_dt(dt_str):
            if not dt_str:
                return None
            if isinstance(dt_str, datetime.datetime):
                return dt_str
            for fmt in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%d'):
                try:
                    return datetime.datetime.strptime(dt_str, fmt)
                except ValueError:
                    pass
            return None

        cassa_waits = []
        prep_waits = []
        total_waits = []
        
        hourly_counts = {}
        hourly_cassa = {}
        hourly_prep = {}

        for oid, data in order_data.items():
            created_dt = parse_dt(data['created_at'])
            updated_dt = parse_dt(data['updated_at'])
            
            released_dts = [parse_dt(rt) for rt in data['released_times'] if rt]
            released_dts = [rd for rd in released_dts if rd is not None]
            min_released_dt = min(released_dts) if released_dts else None
            
            hour_str = None
            if created_dt:
                hour_str = f"{created_dt.hour:02d}"
                if hour_str not in hourly_counts:
                    hourly_counts[hour_str] = 0
                    hourly_cassa[hour_str] = []
                    hourly_prep[hour_str] = []
                hourly_counts[hour_str] += 1

            # Cashier wait (for VALIDATED, READY, DELIVERED)
            if created_dt and min_released_dt and data['status'] in ('VALIDATED', 'READY', 'DELIVERED'):
                diff = (min_released_dt - created_dt).total_seconds() / 60.0
                if diff >= 0:
                    cassa_waits.append(diff)
                    if hour_str:
                        hourly_cassa[hour_str].append(diff)
                        
            # Prep and Total wait (for READY, DELIVERED)
            if data['status'] in ('READY', 'DELIVERED') and min_released_dt and updated_dt:
                diff_prep = (updated_dt - min_released_dt).total_seconds() / 60.0
                diff_total = (updated_dt - created_dt).total_seconds() / 60.0
                if diff_prep >= 0:
                    prep_waits.append(diff_prep)
                    if hour_str:
                        hourly_prep[hour_str].append(diff_prep)
                if diff_total >= 0:
                    total_waits.append(diff_total)

        avg_cassa = sum(cassa_waits) / len(cassa_waits) if cassa_waits else None
        avg_prep = sum(prep_waits) / len(prep_waits) if prep_waits else None
        avg_total = sum(total_waits) / len(total_waits) if total_waits else None

        hourly_flow = []
        for hr in sorted(hourly_counts.keys()):
            hc = hourly_cassa[hr]
            hp = hourly_prep[hr]
            hourly_flow.append({
                'hour': hr,
                'count': hourly_counts[hr],
                'avg_cassa': sum(hc) / len(hc) if hc else None,
                'avg_prep': sum(hp) / len(hp) if hp else None
            })

        # 3. Popular products
        cursor.execute("""
            SELECT 
                mi.id,
                mi.category,
                mi.name,
                SUM(oi.quantity) AS total_qty
            FROM order_items oi
            JOIN menu_items mi ON oi.menu_item_id = mi.id
            JOIN orders o ON oi.order_id = o.id
            WHERE UPPER(o.status) IN ('VALIDATED', 'PREPARING', 'READY', 'DELIVERED')
            GROUP BY mi.id
            ORDER BY mi.category ASC, total_qty DESC;
        """)
        item_rows = cursor.fetchall()
        top_items = []
        for row in item_rows:
            top_items.append({
                'id': row['id'],
                'category': row['category'],
                'name': row['name'],
                'quantity': row['total_qty']
            })

        return {
            'status_counts': status_counts,
            'status_revenue': status_revenue,
            'total_orders': total_orders,
            'total_revenue': total_revenue,
            'avg_cassa': avg_cassa,
            'avg_prep': avg_prep,
            'avg_total': avg_total,
            'hourly_flow': hourly_flow,
            'top_items': top_items
        }
    finally:
        conn.close()


def update_menu_item_purchase_cost(item_id, cost):
    """
    Updates the purchase (food) cost of a menu item in the foodcost database.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO menu_item_costs (menu_item_id, purchase_cost)
            VALUES (?, ?)
            ON CONFLICT(menu_item_id) DO UPDATE SET purchase_cost = excluded.purchase_cost;
        """, (item_id, cost))
        conn.commit()
    finally:
        conn.close()


def get_ingredients():
    """
    Returns all ingredients from warehouse, including their calculated WAC unit_cost.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT 
                i.*,
                COALESCE(
                    (SELECT SUM(ip.quantity * ip.package_price) / SUM(ip.quantity * ip.package_size)
                     FROM ingredient_purchases ip
                     WHERE ip.ingredient_id = i.id),
                    (i.package_price / i.package_size)
                ) AS unit_cost,
                (SELECT COUNT(*) FROM ingredient_purchases ip WHERE ip.ingredient_id = i.id) AS purchases_count
            FROM ingredients i
            ORDER BY i.name ASC;
        """)
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def add_ingredient(name, unit, package_size, package_price, initial_quantity=0.0, supplier="", doc_number="", payment_status="DA PAGARE", payment_date="", iva=10.0):
    """
    Adds a new ingredient to the warehouse and optionally registers its first delivery.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO ingredients (name, unit, package_size, package_price, iva)
            VALUES (?, ?, ?, ?, ?);
        """, (name, unit, package_size, package_price, iva))
        new_id = cursor.lastrowid
        
        if initial_quantity > 0:
            cursor.execute("""
                INSERT INTO ingredient_purchases (ingredient_id, quantity, package_size, package_price, supplier, doc_number, payment_status, payment_date, iva)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
            """, (new_id, initial_quantity, package_size, package_price, supplier, doc_number, payment_status, payment_date, iva))
            
        conn.commit()
        recalculate_all_recipes_costs(cursor)
        conn.commit()
        return new_id
    finally:
        conn.close()


def update_ingredient(ing_id, name, unit, package_size, package_price, iva=10.0):
    """
    Updates an ingredient's properties and recalculates affected recipe costs.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            UPDATE ingredients
            SET name = ?, unit = ?, package_size = ?, package_price = ?, iva = ?
            WHERE id = ?;
        """, (name, unit, package_size, package_price, iva, ing_id))
        conn.commit()
        recalculate_all_recipes_costs(cursor)
        conn.commit()
    finally:
        conn.close()


def delete_ingredient(ing_id):
    """
    Deletes an ingredient and recalculates affected recipe costs.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM ingredients WHERE id = ?;", (ing_id,))
        conn.commit()
        recalculate_all_recipes_costs(cursor)
        conn.commit()
    finally:
        conn.close()


def get_recipe(menu_item_id):
    """
    Gets the list of ingredients and quantities for a menu item, using WAC.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT 
                ri.ingredient_id,
                i.name AS ingredient_name,
                i.unit,
                i.package_size,
                i.package_price,
                ri.quantity,
                COALESCE(
                    (SELECT SUM(ip.quantity * ip.package_price) / SUM(ip.quantity * ip.package_size)
                     FROM ingredient_purchases ip
                     WHERE ip.ingredient_id = i.id),
                    (i.package_price / i.package_size)
                ) AS unit_cost,
                (ri.quantity * COALESCE(
                    (SELECT SUM(ip.quantity * ip.package_price) / SUM(ip.quantity * ip.package_size)
                     FROM ingredient_purchases ip
                     WHERE ip.ingredient_id = i.id),
                    (i.package_price / i.package_size)
                )) AS subtotal
            FROM recipe_ingredients ri
            JOIN ingredients i ON ri.ingredient_id = i.id
            WHERE ri.menu_item_id = ?;
        """, (menu_item_id,))
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def save_recipe(menu_item_id, ingredients_list):
    """
    Saves a recipe for a menu item and updates its purchase_cost in the main DB.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM recipe_ingredients WHERE menu_item_id = ?;", (menu_item_id,))
        for item in ingredients_list:
            ing_id = item['ingredient_id']
            qty = item['quantity']
            cursor.execute("""
                INSERT INTO recipe_ingredients (menu_item_id, ingredient_id, quantity)
                VALUES (?, ?, ?);
            """, (menu_item_id, ing_id, qty))
        conn.commit()
        recalculate_recipe_cost(cursor, menu_item_id)
        conn.commit()
    finally:
        conn.close()


def recalculate_recipe_cost(fc_cursor, menu_item_id):
    """
    Sums ingredient WAC costs and updates the corresponding menu_item's purchase_cost in the main operational DB.
    """
    fc_cursor.execute("""
        SELECT SUM(ri.quantity * COALESCE(
            (SELECT SUM(ip.quantity * ip.package_price) / SUM(ip.quantity * ip.package_size)
             FROM ingredient_purchases ip
             WHERE ip.ingredient_id = i.id),
            (i.package_price / i.package_size)
        )) AS total_cost
        FROM recipe_ingredients ri
        JOIN ingredients i ON ri.ingredient_id = i.id
        WHERE ri.menu_item_id = ?;
    """, (menu_item_id,))
    res = fc_cursor.fetchone()
    total_cost = res['total_cost'] if res and res['total_cost'] is not None else 0.0
    
    # Update purchase cost cache in the main Sagra database
    update_menu_item_purchase_cost(menu_item_id, total_cost)


def recalculate_all_recipes_costs(fc_cursor):
    """
    Recalculates all menu items that have active recipe ingredients, and resets costs for those without recipes.
    """
    fc_cursor.execute("SELECT DISTINCT menu_item_id FROM recipe_ingredients;")
    menu_items_with_recipes = {row['menu_item_id'] for row in fc_cursor.fetchall()}
    for item_id in menu_items_with_recipes:
        recalculate_recipe_cost(fc_cursor, item_id)
        
    # Reset cost to 0 for items that no longer have active recipe ingredients
    fc_cursor.execute("SELECT menu_item_id FROM menu_item_costs;")
    all_cost_items = [row['menu_item_id'] for row in fc_cursor.fetchall()]
    for item_id in all_cost_items:
        if item_id not in menu_items_with_recipes:
            fc_cursor.execute("""
                UPDATE menu_item_costs
                SET purchase_cost = 0.0
                WHERE menu_item_id = ?;
            """, (item_id,))


def get_ingredient_purchases(ingredient_id):
    """
    Returns all supplier deliveries for a given ingredient, newest first.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT * FROM ingredient_purchases
            WHERE ingredient_id = ?
            ORDER BY purchase_date DESC, id DESC;
        """, (ingredient_id,))
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def get_total_purchases_sum():
    """
    Returns the sum of total costs of all ingredient purchases (quantity * package_price).
    """
    conn = get_foodcost_connection()
    cursor = conn.conn.cursor() if hasattr(conn, 'conn') else conn.cursor()
    try:
        cursor.execute("SELECT SUM(quantity * package_price) FROM ingredient_purchases;")
        row = cursor.fetchone()
        return row[0] if row and row[0] is not None else 0.0
    finally:
        conn.close()


def add_ingredient_purchase(ingredient_id, quantity, package_size, package_price, supplier="", doc_number="", payment_status="DA PAGARE", payment_date="", iva=10.0):
    """
    Registers a new delivery (purchase) and triggers cascade recipe cost recalculation.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO ingredient_purchases (ingredient_id, quantity, package_size, package_price, supplier, doc_number, payment_status, payment_date, iva)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
        """, (ingredient_id, quantity, package_size, package_price, supplier, doc_number, payment_status, payment_date, iva))
        conn.commit()
        recalculate_all_recipes_costs(cursor)
        conn.commit()
        return cursor.lastrowid
    finally:
        conn.close()


def update_ingredient_purchase(purchase_id, quantity, package_size, package_price, supplier="", doc_number="", payment_status="DA PAGARE", payment_date="", iva=10.0):
    """
    Updates an existing delivery (purchase) and triggers cascade recipe cost recalculation.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            UPDATE ingredient_purchases 
            SET quantity = ?, package_size = ?, package_price = ?, supplier = ?, doc_number = ?, payment_status = ?, payment_date = ?, iva = ?
            WHERE id = ?;
        """, (quantity, package_size, package_price, supplier, doc_number, payment_status, payment_date, iva, purchase_id))
        conn.commit()
        recalculate_all_recipes_costs(cursor)
        conn.commit()
    finally:
        conn.close()


def get_all_ingredient_purchases():
    """
    Returns all supplier deliveries for all ingredients, sorted by date descending.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT ip.*, i.name AS ingredient_name, i.unit AS ingredient_unit
            FROM ingredient_purchases ip
            JOIN ingredients i ON ip.ingredient_id = i.id
            ORDER BY ip.purchase_date DESC, ip.id DESC;
        """)
        rows = cursor.fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def delete_ingredient_purchase(purchase_id):
    """
    Deletes a delivery (purchase) and triggers cascade recipe cost recalculation.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM ingredient_purchases WHERE id = ?;", (purchase_id,))
        conn.commit()
        recalculate_all_recipes_costs(cursor)
        conn.commit()
    finally:
        conn.close()


def get_simulated_sales():
    """
    Returns simulated sales quantities as a dictionary { menu_item_id: quantity }
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT menu_item_id, quantity FROM simulated_sales;")
        return {row[0]: row[1] for row in cursor.fetchall()}
    finally:
        conn.close()


def save_simulated_sale(menu_item_id, quantity):
    """
    Saves or updates a simulated quantity for a menu item.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO simulated_sales (menu_item_id, quantity)
            VALUES (?, ?)
            ON CONFLICT(menu_item_id) DO UPDATE SET quantity = excluded.quantity;
        """, (menu_item_id, quantity))
        conn.commit()
    finally:
        conn.close()


def clear_simulated_sales():
    """
    Resets all simulated sales to 0.0.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM simulated_sales;")
        conn.commit()
    finally:
        conn.close()


def align_foodcost_sales():
    """
    Copies real sales from sagra.db to simulated_sales and simulated_sales_daily in foodcost.db.
    Also copies checkout expenses from sagra.db to checkout_expenses in foodcost.db.
    """
    # 1. Fetch real sales from sagra.db
    conn_sagra = get_connection()
    cursor_sagra = conn_sagra.cursor()
    
    base_where = "UPPER(o.status) IN ('VALIDATED', 'PREPARING', 'READY', 'DELIVERED')"
    
    try:
        # Fetch daily sales per item
        cursor_sagra.execute(f"""
            SELECT 
                date(o.created_at) as sales_date,
                oi.menu_item_id,
                SUM(oi.quantity) as quantity,
                SUM(CASE WHEN UPPER(o.payment_type) IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity ELSE 0 END) as quantity_free
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            WHERE {base_where}
            GROUP BY sales_date, oi.menu_item_id;
        """)
        daily_sales = cursor_sagra.fetchall()
        
        # Fetch total sales per item
        cursor_sagra.execute(f"""
            SELECT 
                oi.menu_item_id,
                SUM(oi.quantity) as quantity,
                SUM(CASE WHEN UPPER(o.payment_type) IN ('PERSONALE', 'OMAGGIO') THEN oi.quantity ELSE 0 END) as quantity_free
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            WHERE {base_where}
            GROUP BY oi.menu_item_id;
        """)
        total_sales = cursor_sagra.fetchall()
    finally:
        conn_sagra.close()
        
    # Fetch all checkout expenses
    expenses = get_all_checkout_expenses()
    
    # Fetch all daily checkout summaries
    summaries = get_manifestation_summary()
        
    # 2. Write to foodcost.db
    conn_fc = get_foodcost_connection()
    cursor_fc = conn_fc.cursor()
    try:
        # Clear existing
        cursor_fc.execute("DELETE FROM simulated_sales;")
        cursor_fc.execute("DELETE FROM simulated_sales_daily;")
        cursor_fc.execute("DELETE FROM checkout_expenses;")
        cursor_fc.execute("DELETE FROM checkout_summaries;")
        
        # Insert daily
        for row in daily_sales:
            cursor_fc.execute("""
                INSERT INTO simulated_sales_daily (sales_date, menu_item_id, quantity, quantity_free)
                VALUES (?, ?, ?, ?);
            """, (row[0], row[1], row[2], row[3]))
            
        # Insert total
        for row in total_sales:
            cursor_fc.execute("""
                INSERT INTO simulated_sales (menu_item_id, quantity, quantity_free)
                VALUES (?, ?, ?);
            """, (row[0], row[1], row[2]))
            
        # Insert checkout expenses
        for exp in expenses:
            cursor_fc.execute("""
                INSERT INTO checkout_expenses (id, checkout_date, amount, supplier, description, expense_type, payment_method)
                VALUES (?, ?, ?, ?, ?, ?, ?);
            """, (exp["id"], exp["checkout_date"], exp["amount"], exp["supplier"], exp["description"], exp["expense_type"], exp["payment_method"]))
            
        # Insert checkout summaries
        for s in summaries:
            cursor_fc.execute("""
                INSERT INTO checkout_summaries (id, date, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, 
                                               cash_chiusura, totale_spese_contanti, totale_spese_non_contanti, totale_spese,
                                               ricavo_serata, totale_spese_ordinarie, totale_spese_straordinarie, totale_spese_associazione,
                                               cassa_prossima_serata, cassa_rimanente, guadagno_netto, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
            """, (s["id"], s["date"], s["cassa_iniziale"], s["pos_amount"], s["soldi_da_versare"], s["quota_tombola"],
                  s["cash_chiusura"], s["totale_spese_contanti"], s["totale_spese_non_contanti"], s["totale_spese"],
                  s["ricavo_serata"], s["totale_spese_ordinarie"], s["totale_spese_straordinarie"], s.get("totale_spese_associazione", 0.0),
                  s["cassa_prossima_serata"], s["cassa_rimanente"], s["guadagno_netto"], s["notes"]))
            
        conn_fc.commit()
    finally:
        conn_fc.close()


def get_foodcost_sales_analytics(start_date=None, end_date=None, category=None):
    """
    Returns aggregated sales data from foodcost.db (simulated_sales_daily) but maps item names/prices from sagra.db menu.
    """
    # 1. Fetch menu info from sagra.db to map item names, prices, categories
    conn_sagra = get_connection()
    cursor_sagra = conn_sagra.cursor()
    try:
        cursor_sagra.execute("SELECT id, name, price, category FROM menu_items;")
        menu_rows = cursor_sagra.fetchall()
        menu_items_map = {
            row[0]: {
                "name": row[1],
                "price": row[2],
                "category": row[3]
            }
            for row in menu_rows
        }
    finally:
        conn_sagra.close()
        
    # 2. Query foodcost.db simulated_sales_daily e i relativi costi calcolati
    conn_fc = get_foodcost_connection()
    cursor_fc = conn_fc.cursor()
    
    try:
        # Leggi i costi dei piatti
        cursor_fc.execute("SELECT menu_item_id, purchase_cost FROM menu_item_costs;")
        costs_rows = cursor_fc.fetchall()
        costs_map = {row[0]: row[1] for row in costs_rows}
        
        for m_id, m_info in menu_items_map.items():
            m_info["cost"] = costs_map.get(m_id, 0.0)
        query_str = "SELECT sales_date, menu_item_id, quantity, quantity_free FROM simulated_sales_daily WHERE 1=1"
        params = []
        if start_date:
            query_str += " AND sales_date >= ?"
            params.append(start_date)
        if end_date:
            query_str += " AND sales_date <= ?"
            params.append(end_date)
            
        cursor_fc.execute(query_str, tuple(params))
        rows = cursor_fc.fetchall()
        
        # Aggregate
        total_revenue = 0.0
        total_cost = 0.0
        total_items = 0
        total_free_items = 0
        total_free_value = 0.0
        total_free_cost = 0.0
        unique_dates = set()
        
        top_items_dict = {} # menu_item_id -> { paid_qty, free_qty }
        by_category_dict = {} # category -> { revenue, items }
        by_date_dict = {} # sales_date -> revenue
        
        for row in rows:
            sales_date, item_id, qty, qty_free = row[0], row[1], row[2], row[3]
            item_info = menu_items_map.get(item_id)
            if not item_info:
                continue
                
            item_cat = item_info["category"]
            if category and category.upper() != 'ALL' and item_cat.upper() != category.upper():
                continue
                
            unique_dates.add(sales_date)
            price = item_info["price"]
            const_cost = item_info.get("cost", 0.0)
            
            paid_qty = max(0.0, qty - qty_free)
            revenue = paid_qty * price
            cost = qty * const_cost
            
            total_revenue += revenue
            total_cost += cost
            total_items += paid_qty
            total_free_items += qty_free
            total_free_value += qty_free * price
            total_free_cost += qty_free * const_cost
            
            # Top items
            if item_id not in top_items_dict:
                top_items_dict[item_id] = {"paid_qty": 0.0, "free_qty": 0.0}
            top_items_dict[item_id]["paid_qty"] += paid_qty
            top_items_dict[item_id]["free_qty"] += qty_free
            
            # By category
            if item_cat not in by_category_dict:
                by_category_dict[item_cat] = {"revenue": 0.0, "items": 0}
            by_category_dict[item_cat]["revenue"] += revenue
            by_category_dict[item_cat]["items"] += paid_qty
            
            # By date
            if sales_date not in by_date_dict:
                by_date_dict[sales_date] = 0.0
            by_date_dict[sales_date] += revenue
            
        # Format results
        top_items = []
        for item_id, q_dict in top_items_dict.items():
            qty = q_dict["paid_qty"]
            free_qty = q_dict["free_qty"]
            item_info = menu_items_map[item_id]
            const_cost = item_info.get("cost", 0.0)
            tot_revenue = qty * item_info["price"]
            tot_cost = (qty + free_qty) * const_cost
            top_items.append({
                "name": item_info["name"],
                "category": item_info["category"],
                "qty": qty,
                "free_qty": free_qty,
                "revenue": tot_revenue,
                "cost": tot_cost,
                "profit": tot_revenue - tot_cost
            })
        top_items.sort(key=lambda x: x["qty"], reverse=True)
        
        by_category = []
        for cat, val in by_category_dict.items():
            by_category.append({
                "category": cat,
                "revenue": val["revenue"],
                "items": val["items"]
            })
        by_category.sort(key=lambda x: x["revenue"], reverse=True)
        
        by_date = []
        for s_date, rev in by_date_dict.items():
            by_date.append({
                "sales_date": s_date,
                "revenue": rev
            })
        by_date.sort(key=lambda x: x["sales_date"])
        
        return {
            "total_revenue": total_revenue,
            "total_cost": total_cost,
            "total_profit": total_revenue - total_cost,
            "total_items": int(total_items),
            "total_free_items": int(total_free_items),
            "total_free_value": total_free_value,
            "total_free_cost": total_free_cost,
            "total_orders": len(unique_dates),
            "by_category": by_category,
            "top_items": top_items,
            "by_date": by_date
        }
    finally:
        conn_fc.close()


def get_menu_config():
    """
    Retrieves the entire menu, ingredients, recipe, and purchase configuration.
    """
    fc_conn = get_foodcost_connection()
    fc_cursor = fc_conn.cursor()
    try:
        fc_cursor.execute("SELECT * FROM ingredients;")
        ingredients = [dict(row) for row in fc_cursor.fetchall()]

        fc_cursor.execute("SELECT * FROM ingredient_purchases;")
        purchases = [dict(row) for row in fc_cursor.fetchall()]

        fc_cursor.execute("SELECT * FROM recipe_ingredients;")
        recipes = [dict(row) for row in fc_cursor.fetchall()]

        fc_cursor.execute("SELECT menu_item_id, purchase_cost FROM menu_item_costs;")
        item_costs = {row[0]: row[1] for row in fc_cursor.fetchall()}

        fc_cursor.execute("SELECT menu_item_id, quantity FROM simulated_sales;")
        simulated_sales = [dict(row) for row in fc_cursor.fetchall()]
    finally:
        fc_conn.close()

    main_conn = get_connection()
    main_cursor = main_conn.cursor()
    try:
        main_cursor.execute("SELECT id, price FROM menu_items;")
        menu_items = []
        for row in main_cursor.fetchall():
            d = dict(row)
            d['purchase_cost'] = item_costs.get(d['id'], 0.0)
            menu_items.append(d)
    finally:
        main_conn.close()

    return {
        "ingredients": ingredients,
        "purchases": purchases,
        "recipes": recipes,
        "menu_items": menu_items,
        "simulated_sales": simulated_sales
    }


def apply_synced_menu_config(config_data):
    """
    Applies a synchronized menu configuration by purging and replacing local data.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        # Clean up tables
        cursor.execute("DELETE FROM recipe_ingredients;")
        cursor.execute("DELETE FROM ingredient_purchases;")
        cursor.execute("DELETE FROM ingredients;")
        cursor.execute("DELETE FROM menu_item_costs;")
        cursor.execute("DELETE FROM simulated_sales;")
        
        # Insert ingredients
        for ing in config_data.get('ingredients', []):
            cursor.execute("""
                INSERT INTO ingredients (id, name, unit, package_size, package_price)
                VALUES (?, ?, ?, ?, ?);
            """, (ing['id'], ing['name'], ing['unit'], ing['package_size'], ing['package_price']))
            
        # Insert purchases
        for p in config_data.get('purchases', []):
            cursor.execute("""
                INSERT INTO ingredient_purchases (id, ingredient_id, purchase_date, quantity, package_size, package_price, supplier, doc_number, payment_status, payment_date)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
            """, (p['id'], p['ingredient_id'], p.get('purchase_date'), p['quantity'], p['package_size'], p['package_price'], p.get('supplier', ''), p.get('doc_number', ''), p.get('payment_status', 'DA PAGARE'), p.get('payment_date', '')))

        # Insert recipes
        for r in config_data.get('recipes', []):
            cursor.execute("""
                INSERT INTO recipe_ingredients (menu_item_id, ingredient_id, quantity)
                VALUES (?, ?, ?);
            """, (r['menu_item_id'], r['ingredient_id'], r['quantity']))

        # Insert menu item costs
        for item in config_data.get('menu_items', []):
            cursor.execute("""
                INSERT INTO menu_item_costs (menu_item_id, purchase_cost)
                VALUES (?, ?);
            """, (item['id'], item.get('purchase_cost', 0.0)))

        # Insert simulated sales if present
        for s in config_data.get('simulated_sales', []):
            cursor.execute("""
                INSERT INTO simulated_sales (menu_item_id, quantity)
                VALUES (?, ?);
            """, (s['menu_item_id'], s['quantity']))

        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

    main_conn = get_connection()
    main_cursor = main_conn.cursor()
    try:
        # Update menu item prices in the main DB
        for item in config_data.get('menu_items', []):
            main_cursor.execute("""
                UPDATE menu_items
                SET price = ?
                WHERE id = ?;
            """, (item['price'], item['id']))
        main_conn.commit()
    except Exception as e:
        main_conn.rollback()
        raise e
    finally:
        main_conn.close()


def get_foodcost_sales_payload():
    """
    Retrieves the contents of simulated_sales, simulated_sales_daily, checkout_expenses, and checkout_summaries from local foodcost.db.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT menu_item_id, quantity, quantity_free FROM simulated_sales;")
        simulated_sales = [{"menu_item_id": row[0], "quantity": row[1], "quantity_free": row[2]} for row in cursor.fetchall()]
        
        cursor.execute("SELECT sales_date, menu_item_id, quantity, quantity_free FROM simulated_sales_daily;")
        simulated_sales_daily = [{"sales_date": row[0], "menu_item_id": row[1], "quantity": row[2], "quantity_free": row[3]} for row in cursor.fetchall()]
        
        cursor.execute("SELECT id, checkout_date, amount, supplier, description, expense_type, payment_method FROM checkout_expenses;")
        checkout_expenses = [{"id": row[0], "checkout_date": row[1], "amount": row[2], "supplier": row[3], "description": row[4], "expense_type": row[5], "payment_method": row[6]} for row in cursor.fetchall()]
        
        cursor.execute("""
            SELECT id, date, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, 
                   cash_chiusura, totale_spese_contanti, totale_spese_non_contanti, totale_spese,
                   ricavo_serata, totale_spese_ordinarie, totale_spese_straordinarie, totale_spese_associazione,
                   cassa_prossima_serata, cassa_rimanente, guadagno_netto, notes,
                   totale_spese_attrezzature, totale_spese_manutenzione
            FROM checkout_summaries;
        """)
        checkout_summaries = [
            {
                "id": r[0], "date": r[1], "cassa_iniziale": r[2], "pos_amount": r[3], "soldi_da_versare": r[4], "quota_tombola": r[5],
                "cash_chiusura": r[6], "totale_spese_contanti": r[7], "totale_spese_non_contanti": r[8], "totale_spese": r[9],
                "ricavo_serata": r[10], "totale_spese_ordinarie": r[11], "totale_spese_straordinarie": r[12],
                "totale_spese_associazione": r[13], "cassa_prossima_serata": r[14], "cassa_rimanente": r[15], "guadagno_netto": r[16], "notes": r[17],
                "totale_spese_attrezzature": r[18], "totale_spese_manutenzione": r[19]
            } for r in cursor.fetchall()
        ]
        
        return {
            "simulated_sales": simulated_sales,
            "simulated_sales_daily": simulated_sales_daily,
            "checkout_expenses": checkout_expenses,
            "checkout_summaries": checkout_summaries
        }
    finally:
        conn.close()


def apply_synced_foodcost_sales(sales_data):
    """
    Overwrites the simulated_sales, simulated_sales_daily, checkout_expenses, and checkout_summaries tables in the remote foodcost.db.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM simulated_sales;")
        cursor.execute("DELETE FROM simulated_sales_daily;")
        cursor.execute("DELETE FROM checkout_expenses;")
        cursor.execute("DELETE FROM checkout_summaries;")
        
        for s in sales_data.get("simulated_sales", []):
            cursor.execute("""
                INSERT INTO simulated_sales (menu_item_id, quantity, quantity_free)
                VALUES (?, ?, ?);
            """, (s["menu_item_id"], s["quantity"], s.get("quantity_free", 0.0)))
            
        for sd in sales_data.get("simulated_sales_daily", []):
            cursor.execute("""
                INSERT INTO simulated_sales_daily (sales_date, menu_item_id, quantity, quantity_free)
                VALUES (?, ?, ?, ?);
            """, (sd["sales_date"], sd["menu_item_id"], sd["quantity"], sd.get("quantity_free", 0.0)))
            
        for ce in sales_data.get("checkout_expenses", []):
            cursor.execute("""
                INSERT INTO checkout_expenses (id, checkout_date, amount, supplier, description, expense_type, payment_method)
                VALUES (?, ?, ?, ?, ?, ?, ?);
            """, (ce["id"], ce["checkout_date"], ce["amount"], ce["supplier"], ce["description"], ce["expense_type"], ce.get("payment_method", "CONTANTI")))
            
        for cs in sales_data.get("checkout_summaries", []):
            cursor.execute("""
                INSERT INTO checkout_summaries (id, date, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, 
                                               cash_chiusura, totale_spese_contanti, totale_spese_non_contanti, totale_spese,
                                               ricavo_serata, totale_spese_ordinarie, totale_spese_straordinarie, totale_spese_associazione,
                                               cassa_prossima_serata, cassa_rimanente, guadagno_netto, notes,
                                               totale_spese_attrezzature, totale_spese_manutenzione)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
            """, (cs["id"], cs["date"], cs["cassa_iniziale"], cs["pos_amount"], cs["soldi_da_versare"], cs["quota_tombola"],
                  cs["cash_chiusura"], cs["totale_spese_contanti"], cs["totale_spese_non_contanti"], cs["totale_spese"],
                  cs["ricavo_serata"], cs["totale_spese_ordinarie"], cs["totale_spese_straordinarie"], cs.get("totale_spese_associazione", 0.0),
                  cs["cassa_prossima_serata"], cs["cassa_rimanente"], cs["guadagno_netto"], cs["notes"],
                  cs.get("totale_spese_attrezzature", 0.0), cs.get("totale_spese_manutenzione", 0.0)))
            
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


def get_foodcost_checkout_expenses():
    """
    Returns all synchronized checkout expenses from foodcost.db.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT id, checkout_date, amount, supplier, description, expense_type, payment_method
            FROM checkout_expenses
            ORDER BY checkout_date DESC, id DESC;
        """)
        rows = cursor.fetchall()
        return [
            {
                "id": r[0],
                "checkout_date": r[1],
                "amount": r[2],
                "supplier": r[3],
                "description": r[4],
                "expense_type": r[5],
                "payment_method": r[6]
            }
            for r in rows
        ]
    finally:
        conn.close()


def get_foodcost_checkout_summaries():
    """
    Returns all synchronized daily checkout summaries from foodcost.db.
    """
    conn = get_foodcost_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT id, date, cassa_iniziale, pos_amount, soldi_da_versare, quota_tombola, 
                   cash_chiusura, totale_spese_contanti, totale_spese_non_contanti, totale_spese,
                   ricavo_serata, totale_spese_ordinarie, totale_spese_straordinarie, totale_spese_associazione,
                   cassa_prossima_serata, cassa_rimanente, guadagno_netto, notes,
                   totale_spese_attrezzature, totale_spese_manutenzione
            FROM checkout_summaries
            ORDER BY date ASC;
        """)
        rows = cursor.fetchall()
        return [
            {
                "id": r[0],
                "date": r[1],
                "cassa_iniziale": r[2],
                "pos_amount": r[3],
                "soldi_da_versare": r[4],
                "quota_tombola": r[5],
                "cash_chiusura": r[6],
                "totale_spese_contanti": r[7],
                "totale_spese_non_contanti": r[8],
                "totale_spese": r[9],
                "ricavo_serata": r[10],
                "totale_spese_ordinarie": r[11],
                "totale_spese_straordinarie": r[12],
                "totale_spese_associazione": r[13],
                "cassa_prossima_serata": r[14],
                "cassa_rimanente": r[15],
                "guadagno_netto": r[16],
                "notes": r[17],
                "totale_spese_attrezzature": r[18],
                "totale_spese_manutenzione": r[19]
            }
            for r in rows
        ]
    finally:
        conn.close()





def get_all_checkout_expenses():
    """
    Returns all checkout expenses joined with daily checkout date.
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT 
                e.id,
                c.date as checkout_date,
                e.supplier,
                e.amount,
                e.description,
                e.expense_type,
                COALESCE(e.payment_method, 'CONTANTI') as payment_method
            FROM checkout_expenses e
            JOIN daily_checkouts c ON e.checkout_id = c.id
            ORDER BY c.date DESC, e.id DESC;
        """)
        rows = cursor.fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()


def get_web_adoption_analytics(start_date=None, end_date=None):
    """
    Calculates web order adoption statistics vs cashier orders.
    Considers processed orders (VALIDATED, PREPARING, READY, DELIVERED).
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        base_where = "UPPER(status) IN ('VALIDATED', 'PREPARING', 'READY', 'DELIVERED')"
        params = []
        if start_date:
            base_where += " AND date(created_at) >= ?"
            params.append(start_date)
        if end_date:
            base_where += " AND date(created_at) <= ?"
            params.append(end_date)

        query = f"""
            SELECT 
                id,
                order_number,
                remote_id,
                total_price,
                payment_type,
                date(created_at) as order_date,
                strftime('%H', created_at) as order_hour
            FROM orders
            WHERE {base_where}
            ORDER BY created_at ASC;
        """
        cursor.execute(query, tuple(params))
        rows = [dict(r) for r in cursor.fetchall()]

        total_orders = len(rows)
        web_orders_count = 0
        cashier_orders_count = 0

        total_revenue = 0.0
        web_revenue = 0.0
        cashier_revenue = 0.0

        by_date_dict = {}
        by_hour_dict = {f"{h:02d}": {"web_count": 0, "cashier_count": 0, "web_rev": 0.0, "cashier_rev": 0.0} for h in range(24)}

        for r in rows:
            rev = r['total_price'] or 0.0
            total_revenue += rev
            
            # Orders with remote_id are web orders synced from remote cloud
            is_web = (r['remote_id'] is not None)
            
            o_date = r['order_date'] or ''
            o_hour = r['order_hour'] if r['order_hour'] is not None else '00'

            if o_date not in by_date_dict:
                by_date_dict[o_date] = {
                    "date": o_date,
                    "web_count": 0,
                    "cashier_count": 0,
                    "total_count": 0,
                    "web_rev": 0.0,
                    "cashier_rev": 0.0,
                    "total_rev": 0.0
                }

            if is_web:
                web_orders_count += 1
                web_revenue += rev
                by_date_dict[o_date]["web_count"] += 1
                by_date_dict[o_date]["web_rev"] += rev
                if o_hour in by_hour_dict:
                    by_hour_dict[o_hour]["web_count"] += 1
                    by_hour_dict[o_hour]["web_rev"] += rev
            else:
                cashier_orders_count += 1
                cashier_revenue += rev
                by_date_dict[o_date]["cashier_count"] += 1
                by_date_dict[o_date]["cashier_rev"] += rev
                if o_hour in by_hour_dict:
                    by_hour_dict[o_hour]["cashier_count"] += 1
                    by_hour_dict[o_hour]["cashier_rev"] += rev

            by_date_dict[o_date]["total_count"] += 1
            by_date_dict[o_date]["total_rev"] += rev

        by_date = []
        for d, vals in sorted(by_date_dict.items(), key=lambda x: x[0]):
            tot_cnt = vals["total_count"]
            pct = (vals["web_count"] / tot_cnt * 100) if tot_cnt > 0 else 0.0
            by_date.append({
                "date": vals["date"],
                "web_count": vals["web_count"],
                "cashier_count": vals["cashier_count"],
                "total_count": tot_cnt,
                "web_pct": round(pct, 1),
                "web_rev": round(vals["web_rev"], 2),
                "cashier_rev": round(vals["cashier_rev"], 2),
                "total_rev": round(vals["total_rev"], 2)
            })

        by_hour = []
        for h_str in sorted(by_hour_dict.keys()):
            h_data = by_hour_dict[h_str]
            h_tot = h_data["web_count"] + h_data["cashier_count"]
            h_pct = (h_data["web_count"] / h_tot * 100) if h_tot > 0 else 0.0
            by_hour.append({
                "hour": f"{h_str}:00",
                "web_count": h_data["web_count"],
                "cashier_count": h_data["cashier_count"],
                "total_count": h_tot,
                "web_pct": round(h_pct, 1)
            })

        adoption_pct = (web_orders_count / total_orders * 100) if total_orders > 0 else 0.0
        web_avg_order = (web_revenue / web_orders_count) if web_orders_count > 0 else 0.0
        cashier_avg_order = (cashier_revenue / cashier_orders_count) if cashier_orders_count > 0 else 0.0

        return {
            "total_orders": total_orders,
            "web_orders_count": web_orders_count,
            "cashier_orders_count": cashier_orders_count,
            "adoption_pct": round(adoption_pct, 1),
            "total_revenue": round(total_revenue, 2),
            "web_revenue": round(web_revenue, 2),
            "cashier_revenue": round(cashier_revenue, 2),
            "web_avg_order": round(web_avg_order, 2),
            "cashier_avg_order": round(cashier_avg_order, 2),
            "by_date": by_date,
            "by_hour": by_hour
        }
    finally:
        conn.close()


def get_web_orders_history(limit=200):
    """
    Returns all Web orders (remote_id IS NOT NULL) along with their items.
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT * FROM orders 
            WHERE remote_id IS NOT NULL
            ORDER BY created_at DESC
            LIMIT ?;
        """, (limit,))
        orders = [dict(row) for row in cursor.fetchall()]
        
        for o in orders:
            cursor.execute("""
                SELECT oi.*, mi.name as item_name, mi.category
                FROM order_items oi
                JOIN menu_items mi ON oi.menu_item_id = mi.id
                WHERE oi.order_id = ?;
            """, (o['id'],))
            o['items'] = [dict(row) for row in cursor.fetchall()]
            
        return orders
    finally:
        conn.close()






