import os
from contextlib import asynccontextmanager
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
from fastapi.staticfiles import StaticFiles

from database import engine, Base
from middleware.license_check import LicenseCheckMiddleware
from services.cloud_sync import start_cloud_sync

# Import all models so SQLAlchemy can create their tables
import models.user          # noqa: F401 — also registers WaiterZone
import models.table         # noqa: F401
import models.printer       # noqa: F401
import models.product       # noqa: F401
import models.order         # noqa: F401 — also registers OrderAuditLog, OrderDiscount, PrintJob
import models.business_day  # noqa: F401
import models.shift         # noqa: F401 — registers WaiterShift, ShiftBreak
import models.settings      # noqa: F401
import models.flag          # noqa: F401 — registers TableFlagDef, TableFlagAssignment
import models.message       # noqa: F401 — registers StaffMessage, StaffMessageAck, QuickMessageTemplate
import models.reservation   # noqa: F401
import models.notes          # noqa: F401 — registers SiteNote, SiteTodo
import models.expenses       # noqa: F401 — registers Contact, Expense, ExpensePayment
import models.customers      # noqa: F401 — registers Customer
import models.tabs           # noqa: F401 — registers Tab, TabEntry, TabPayment
import models.waste          # noqa: F401 — registers WasteLog
import models.schedule       # noqa: F401 — registers ScheduledShift
import models.chat           # noqa: F401 — registers Conversation, ConversationParticipant, ChatMessage
import models.prep_zone      # noqa: F401 — registers PrepZone, prep_zone_printers, product_prep_zones
import models.pricing        # noqa: F401 — registers PriceGroup, PriceModifier, Deal, PriceEventLog, WaiterDiscountSettings
import models.recovery_code  # noqa: F401 — registers RecoveryCode

from routers import auth, tables, products, orders, waiters, reports, system, setup as setup_router
from routers import business_day as business_day_router
from routers import shifts as shifts_router
from routers import settings as settings_router
from routers import flags as flags_router
from routers import messages as messages_router
from routers import sse as sse_router
from routers import ws as ws_router
from routers import data_transfer as data_transfer_router
from routers import connect_orders as connect_orders_router
from routers import reservations as reservations_router
from routers import notes as notes_router
from routers.expenses import contacts_router, expenses_router
from routers import customers as customers_router
from routers import tabs as tabs_router
from routers import waste as waste_router
from routers import kds as kds_router
from routers import schedule as schedule_router
from routers import chat as chat_router
from routers import prep_zones as prep_zones_router
from routers import pricing as pricing_router
from routers import fiscal as fiscal_router
from routers import phone as phone_router
from routers import recovery as recovery_router


def _run_migrations():
    """Apply additive schema changes that create_all won't handle.
    Each migration gets its own connection so a no-op (column already exists)
    doesn't leave a dirty transaction that blocks subsequent migrations."""
    from sqlalchemy import text

    migrations = [
        "ALTER TABLE product_ingredients ADD COLUMN extra_cost REAL NOT NULL DEFAULT 0.0",
        "ALTER TABLE products ADD COLUMN image_url VARCHAR",
        "ALTER TABLE tables ADD COLUMN group_id INTEGER REFERENCES table_groups(id)",
        "ALTER TABLE table_groups ADD COLUMN prefix VARCHAR",
        "ALTER TABLE table_groups ADD COLUMN color VARCHAR",
        "ALTER TABLE products ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_preference_sets ADD COLUMN default_choice_id INTEGER",
        "ALTER TABLE product_preference_choices ADD COLUMN sub_choices TEXT",
        "ALTER TABLE product_preference_choices ADD COLUMN disables_subset INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_preference_sets ADD COLUMN shared_subset TEXT",
        "ALTER TABLE product_options ADD COLUMN sub_choices TEXT",
        # Zone-based access control
        """CREATE TABLE IF NOT EXISTS waiter_zones (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            waiter_id INTEGER NOT NULL REFERENCES users(id),
            group_id INTEGER REFERENCES table_groups(id),
            assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Payment tracking on items
        "ALTER TABLE order_items ADD COLUMN paid_by INTEGER REFERENCES users(id)",
        "ALTER TABLE order_items ADD COLUMN paid_at DATETIME",
        "ALTER TABLE order_items ADD COLUMN payment_method VARCHAR",
        # Full audit log
        """CREATE TABLE IF NOT EXISTS order_audit_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL REFERENCES orders(id),
            event_type VARCHAR NOT NULL,
            waiter_id INTEGER REFERENCES users(id),
            item_ids TEXT,
            amount REAL,
            payment_method VARCHAR,
            note TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Waiter profile fields
        "ALTER TABLE users ADD COLUMN full_name VARCHAR",
        "ALTER TABLE users ADD COLUMN nickname VARCHAR",
        "ALTER TABLE users ADD COLUMN mobile_phone VARCHAR",
        "ALTER TABLE users ADD COLUMN avatar_url VARCHAR",
        # Quick options (flat, allow_multiple)
        """CREATE TABLE IF NOT EXISTS product_quick_options (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL REFERENCES products(id),
            name VARCHAR NOT NULL,
            price REAL NOT NULL DEFAULT 0.0,
            allow_multiple INTEGER NOT NULL DEFAULT 0,
            sort_order INTEGER NOT NULL DEFAULT 0
        )""",
        # allow_multiple flag on extras (product_options)
        "ALTER TABLE product_options ADD COLUMN allow_multiple INTEGER NOT NULL DEFAULT 0",
        # Discounts table (future-proofed, schema ready now)
        """CREATE TABLE IF NOT EXISTS order_discounts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL REFERENCES orders(id),
            item_id INTEGER REFERENCES order_items(id),
            discount_type VARCHAR NOT NULL,
            discount_value REAL NOT NULL,
            applied_by INTEGER NOT NULL REFERENCES users(id),
            applied_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            reason TEXT
        )""",
        # Business day scoping on orders
        "ALTER TABLE orders ADD COLUMN business_day_id INTEGER REFERENCES business_days(id)",
        # Shift attribution on paid items
        "ALTER TABLE order_items ADD COLUMN paid_in_shift_id INTEGER REFERENCES waiter_shifts(id)",
        # Seed default POS settings (INSERT OR IGNORE = no-op if already exists)
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('shifts.waiter_self_start', 'true', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('shifts.waiter_self_end', 'true', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('business_day.force_close_allowed', 'true', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('flags.display_mode', 'both', CURRENT_TIMESTAMP)",
        # Table flags
        """CREATE TABLE IF NOT EXISTS table_flag_defs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            emoji VARCHAR,
            color VARCHAR DEFAULT '#6b7280',
            text_color VARCHAR DEFAULT NULL,
            sort_order INTEGER NOT NULL DEFAULT 0,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Migration: add text_color if upgrading from older schema
        "ALTER TABLE table_flag_defs ADD COLUMN text_color VARCHAR DEFAULT NULL",
        """CREATE TABLE IF NOT EXISTS table_flag_assignments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            table_id INTEGER NOT NULL REFERENCES tables(id),
            flag_id INTEGER NOT NULL REFERENCES table_flag_defs(id),
            assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            assigned_by INTEGER REFERENCES users(id)
        )""",
        # Staff messaging
        """CREATE TABLE IF NOT EXISTS quick_message_templates (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            body VARCHAR NOT NULL,
            sort_order INTEGER NOT NULL DEFAULT 0,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        """CREATE TABLE IF NOT EXISTS staff_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sender_id INTEGER NOT NULL REFERENCES users(id),
            body TEXT NOT NULL,
            target_waiter_ids TEXT NOT NULL DEFAULT '[]',
            table_ids TEXT NOT NULL DEFAULT '[]',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        """CREATE TABLE IF NOT EXISTS staff_message_acks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            message_id INTEGER NOT NULL REFERENCES staff_messages(id),
            waiter_id INTEGER NOT NULL REFERENCES users(id),
            acked_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Seed default flag definitions
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (1, 'Χρειάζεται καθάρισμα', '🧹', '#ef4444', 1)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (2, 'Χρειάζεται Βοήθεια', '🆘', '#f97316', 2)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (3, 'Χρειάζεται Σερβιτόρο', '🔔', '#eab308', 3)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (4, 'Περιμένει να πληρώσει', '💳', '#3b82f6', 4)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (5, 'VIP', '⭐', '#8b5cf6', 5)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (6, 'Ευγενικός Πελάτης', '😊', '#22c55e', 6)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (7, 'Αγενής Πελάτης', '😤', '#dc2626', 7)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (8, 'Αλλεργίες', '⚠️', '#f59e0b', 8)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (9, 'Παιδιά στο τραπέζι', '👶', '#06b6d4', 9)",
        "INSERT OR IGNORE INTO table_flag_defs (id, name, emoji, color, sort_order) VALUES (10, 'Επέτειος / Γενέθλια', '🎂', '#ec4899', 10)",
        # Seed default quick message templates
        "INSERT OR IGNORE INTO quick_message_templates (id, body, sort_order) VALUES (1, 'Σε χρειάζομαι τώρα', 1)",
        "INSERT OR IGNORE INTO quick_message_templates (id, body, sort_order) VALUES (2, 'Πάρε διάλειμμα', 2)",
        "INSERT OR IGNORE INTO quick_message_templates (id, body, sort_order) VALUES (3, 'Ετοιμάσου για κλείσιμο', 3)",
        "INSERT OR IGNORE INTO quick_message_templates (id, body, sort_order) VALUES (4, 'Ήρθε νέος πελάτης', 4)",
        "INSERT OR IGNORE INTO quick_message_templates (id, body, sort_order) VALUES (5, 'Ο πελάτης περιμένει να πληρώσει', 5)",
        # Product lifecycle status (active / archived)
        "ALTER TABLE products ADD COLUMN lifecycle_status VARCHAR NOT NULL DEFAULT 'active'",
        # Favorite flags + ordering on all product sub-item types
        "ALTER TABLE product_quick_options ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_quick_options ADD COLUMN favorite_sort_order INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_options ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_options ADD COLUMN favorite_sort_order INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_ingredients ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_ingredients ADD COLUMN favorite_sort_order INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_preference_sets ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_preference_sets ADD COLUMN favorite_sort_order INTEGER NOT NULL DEFAULT 0",
        # Sub-category support
        "ALTER TABLE categories ADD COLUMN parent_id INTEGER REFERENCES categories(id)",
        "ALTER TABLE categories ADD COLUMN general_sort_order INTEGER NOT NULL DEFAULT 0",
        # Auto-expand flag for sub-categories on the PWA accordion
        "ALTER TABLE categories ADD COLUMN auto_expanded INTEGER NOT NULL DEFAULT 0",
        # Printer protocol field
        "ALTER TABLE printers ADD COLUMN protocol VARCHAR NOT NULL DEFAULT 'escpos_tcp'",
        # Compact (half-width) display flag for quick options
        "ALTER TABLE product_quick_options ADD COLUMN is_compact INTEGER NOT NULL DEFAULT 0",
        # Print layout + per-type font settings
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.ticket_mode', 'detailed', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_order_number', '48:1:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_meta', '0:0:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_item_name', '16:1:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_quick', '0:0:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_pref', '0:0:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_extra', '0:0:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_ingredient', '0:0:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_item_note', '0:0:0', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('print.font_order_note', '0:1:0', CURRENT_TIMESTAMP)",
        # Offline/emergency payment tracking
        "ALTER TABLE order_audit_log ADD COLUMN offline_uuid VARCHAR",
        "ALTER TABLE order_audit_log ADD COLUMN offline_at VARCHAR",
        "ALTER TABLE order_audit_log ADD COLUMN is_duplicate INTEGER NOT NULL DEFAULT 0",
        # Cancellation tracking on order items (for reports)
        "ALTER TABLE order_items ADD COLUMN cancelled_by INTEGER REFERENCES users(id)",
        "ALTER TABLE order_items ADD COLUMN cancel_reason TEXT",
        "ALTER TABLE order_items ADD COLUMN cancelled_at DATETIME",
        # Manager account fields (added for setup wizard / future password login)
        "ALTER TABLE users ADD COLUMN password_hash VARCHAR",
        "ALTER TABLE users ADD COLUMN email VARCHAR",
        # Venue identity settings
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('venue.name', '', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('venue.type', '', CURRENT_TIMESTAMP)",
        # Security settings
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('security.login_method', 'password', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('security.autofill_username', 'true', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('security.auto_lock', 'false', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('security.auto_lock_seconds', '300', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('security.auto_logout', 'false', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('security.auto_logout_seconds', '1800', CURRENT_TIMESTAMP)",
        # Xenia Connect — online order fields on orders table
        "ALTER TABLE orders ADD COLUMN source VARCHAR NOT NULL DEFAULT 'pos'",
        "ALTER TABLE orders ADD COLUMN online_order_ref VARCHAR",
        "ALTER TABLE orders ADD COLUMN online_order_cloud_id INTEGER",
        "ALTER TABLE orders ADD COLUMN online_status VARCHAR",
        "ALTER TABLE orders ADD COLUMN online_customer_name VARCHAR",
        "ALTER TABLE orders ADD COLUMN online_customer_phone VARCHAR",
        "ALTER TABLE orders ADD COLUMN online_customer_address TEXT",
        "ALTER TABLE orders ADD COLUMN online_customer_notes TEXT",
        "ALTER TABLE orders ADD COLUMN online_order_type VARCHAR",
        # Xenia Connect — digital menu fields on products
        "ALTER TABLE products ADD COLUMN digital_visible INTEGER NOT NULL DEFAULT 1",
        "ALTER TABLE products ADD COLUMN digital_available INTEGER NOT NULL DEFAULT 1",
        "ALTER TABLE products ADD COLUMN digital_name VARCHAR",
        "ALTER TABLE products ADD COLUMN digital_description VARCHAR",
        "ALTER TABLE products ADD COLUMN digital_price REAL",
        "ALTER TABLE products ADD COLUMN digital_discount REAL NOT NULL DEFAULT 0.0",
        "ALTER TABLE products ADD COLUMN digital_image_url VARCHAR",
        # Per-printer line width (chars per line) — default 48 for 80mm Jolimark
        "ALTER TABLE printers ADD COLUMN line_width INTEGER NOT NULL DEFAULT 48",
        # Product description (plain-text, for menu display)
        "ALTER TABLE products ADD COLUMN description TEXT",
        # Staff note (internal memo on a waiter's profile)
        "ALTER TABLE users ADD COLUMN note VARCHAR",
        # Reservations table
        """CREATE TABLE IF NOT EXISTS reservations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            guest_name VARCHAR NOT NULL,
            party_size INTEGER NOT NULL,
            phone VARCHAR,
            email VARCHAR,
            note TEXT,
            reserved_for DATETIME NOT NULL,
            table_id INTEGER REFERENCES tables(id),
            status VARCHAR NOT NULL DEFAULT 'pending',
            source VARCHAR NOT NULL DEFAULT 'manager_app',
            created_by_user_id INTEGER REFERENCES users(id),
            online_customer_id VARCHAR,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Product unit of measure (piece / portion / kg / liter / gram / ml)
        "ALTER TABLE products ADD COLUMN unit_type VARCHAR NOT NULL DEFAULT 'piece'",
        # Phase 2A — product cost tracking
        "ALTER TABLE products ADD COLUMN cost_simple REAL",
        "ALTER TABLE products ADD COLUMN cost_breakdown TEXT",
        "ALTER TABLE order_items ADD COLUMN unit_cost REAL",
        # Phase 2B — staff payroll
        "ALTER TABLE users ADD COLUMN hourly_rate REAL",
        "ALTER TABLE waiter_shifts ADD COLUMN hourly_rate_snapshot REAL",
        # Phase 2J — staff shift scheduling
        """CREATE TABLE IF NOT EXISTS scheduled_shifts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL REFERENCES users(id),
            scheduled_date DATE NOT NULL,
            start_time VARCHAR NOT NULL,
            end_time VARCHAR NOT NULL,
            notes TEXT,
            created_by_id INTEGER NOT NULL REFERENCES users(id),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Phase 2H — void/waste log
        """CREATE TABLE IF NOT EXISTS waste_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL REFERENCES products(id),
            quantity REAL NOT NULL,
            unit_cost_snapshot REAL,
            total_cost REAL,
            reason VARCHAR NOT NULL,
            reason_notes TEXT,
            logged_by_id INTEGER NOT NULL REFERENCES users(id),
            business_day_id INTEGER REFERENCES business_days(id),
            logged_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Phase 2G — pay-later tab system
        """CREATE TABLE IF NOT EXISTS tabs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER NOT NULL REFERENCES customers(id),
            status VARCHAR NOT NULL DEFAULT 'open',
            opened_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            closed_at DATETIME,
            closed_by_id INTEGER REFERENCES users(id),
            notes TEXT
        )""",
        """CREATE TABLE IF NOT EXISTS tab_entries (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tab_id INTEGER NOT NULL REFERENCES tabs(id),
            order_id INTEGER REFERENCES orders(id),
            order_item_id INTEGER REFERENCES order_items(id),
            amount REAL NOT NULL,
            description TEXT,
            created_by_id INTEGER NOT NULL REFERENCES users(id),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        """CREATE TABLE IF NOT EXISTS tab_payments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tab_id INTEGER NOT NULL REFERENCES tabs(id),
            amount REAL NOT NULL,
            payment_method VARCHAR,
            received_by_id INTEGER NOT NULL REFERENCES users(id),
            notes TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        # Phase 2F — customer CRM
        """CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            nickname VARCHAR,
            phone VARCHAR,
            email VARCHAR,
            notes TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_by_id INTEGER NOT NULL REFERENCES users(id)
        )""",
        "ALTER TABLE orders ADD COLUMN customer_id INTEGER REFERENCES customers(id)",
        # Phase 2E — cash drawer reconciliation
        "ALTER TABLE waiter_shifts ADD COLUMN counted_cash_end REAL",
        "ALTER TABLE waiter_shifts ADD COLUMN cash_discrepancy REAL",
        "ALTER TABLE business_days ADD COLUMN store_opening_cash REAL",
        "ALTER TABLE business_days ADD COLUMN store_closing_cash REAL",
        "ALTER TABLE business_days ADD COLUMN store_cash_discrepancy REAL",
        # Phase 2D — expense tracking + supplier contacts
        """CREATE TABLE IF NOT EXISTS contacts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            type VARCHAR NOT NULL DEFAULT 'supplier',
            phone VARCHAR,
            email VARCHAR,
            address TEXT,
            notes TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        """CREATE TABLE IF NOT EXISTS expenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            description VARCHAR NOT NULL,
            category VARCHAR NOT NULL,
            contact_id INTEGER REFERENCES contacts(id),
            total_amount REAL NOT NULL,
            paid_amount REAL NOT NULL DEFAULT 0.0,
            due_date DATETIME,
            business_day_id INTEGER REFERENCES business_days(id),
            created_by_id INTEGER NOT NULL REFERENCES users(id),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            notes TEXT
        )""",
        """CREATE TABLE IF NOT EXISTS expense_payments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            expense_id INTEGER NOT NULL REFERENCES expenses(id),
            amount REAL NOT NULL,
            paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            paid_by_id INTEGER NOT NULL REFERENCES users(id),
            notes TEXT
        )""",
        # Phase 2C — notes & todos
        """CREATE TABLE IF NOT EXISTS site_notes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            body TEXT NOT NULL,
            created_by_id INTEGER NOT NULL REFERENCES users(id),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            is_pinned INTEGER NOT NULL DEFAULT 0
        )""",
        """CREATE TABLE IF NOT EXISTS site_todos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            body TEXT NOT NULL,
            is_done INTEGER NOT NULL DEFAULT 0,
            done_at DATETIME,
            done_by_id INTEGER REFERENCES users(id),
            created_by_id INTEGER NOT NULL REFERENCES users(id),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            priority VARCHAR NOT NULL DEFAULT 'normal'
        )""",
        # Printer duplicate copies (0 = print once, 1 = print twice, etc.)
        "ALTER TABLE printers ADD COLUMN duplicates INTEGER NOT NULL DEFAULT 0",
        # Waiter cancellation permissions
        "ALTER TABLE users ADD COLUMN can_cancel_orders INTEGER NOT NULL DEFAULT 0",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('orders.waiter_cancellations_allowed', 'false', CURRENT_TIMESTAMP)",
        # KDS — kitchen display system status tracking
        "ALTER TABLE orders ADD COLUMN kds_status VARCHAR NOT NULL DEFAULT 'pending'",
        "ALTER TABLE orders ADD COLUMN order_type VARCHAR NOT NULL DEFAULT 'here'",
        "ALTER TABLE order_items ADD COLUMN kds_status VARCHAR NOT NULL DEFAULT 'pending'",
        # KDS notifications — message type + zone tagging on staff messages
        "ALTER TABLE staff_messages ADD COLUMN message_type VARCHAR NOT NULL DEFAULT 'manager'",
        "ALTER TABLE staff_messages ADD COLUMN kds_zone VARCHAR",
        # Chat system
        """CREATE TABLE IF NOT EXISTS conversations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type VARCHAR NOT NULL DEFAULT 'direct',
            name VARCHAR,
            is_system INTEGER NOT NULL DEFAULT 0,
            created_by INTEGER REFERENCES users(id),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        """CREATE TABLE IF NOT EXISTS conversation_participants (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            conversation_id INTEGER NOT NULL REFERENCES conversations(id),
            user_id INTEGER NOT NULL REFERENCES users(id),
            joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_read_at DATETIME
        )""",
        """CREATE TABLE IF NOT EXISTS chat_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            conversation_id INTEGER NOT NULL REFERENCES conversations(id),
            sender_id INTEGER NOT NULL REFERENCES users(id),
            body TEXT NOT NULL,
            sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            deleted_at DATETIME
        )""",
        # Prep Zones — replaces per-product printer assignment
        """CREATE TABLE IF NOT EXISTS prep_zones (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            description VARCHAR
        )""",
        """CREATE TABLE IF NOT EXISTS prep_zone_printers (
            prep_zone_id INTEGER NOT NULL REFERENCES prep_zones(id),
            printer_id   INTEGER NOT NULL REFERENCES printers(id),
            PRIMARY KEY (prep_zone_id, printer_id)
        )""",
        """CREATE TABLE IF NOT EXISTS product_prep_zones (
            product_id   INTEGER NOT NULL REFERENCES products(id),
            prep_zone_id INTEGER NOT NULL REFERENCES prep_zones(id),
            PRIMARY KEY (product_id, prep_zone_id)
        )""",
        # KDS decline tracking on items
        "ALTER TABLE order_items ADD COLUMN decline_note TEXT",
        # Prep zone notification name
        "ALTER TABLE prep_zones ADD COLUMN notification_name VARCHAR",
        # KDS status change timestamp on orders (for "latest change" sorting in PWA)
        "ALTER TABLE orders ADD COLUMN kds_status_changed_at DATETIME",
        # Auto-close table after full payment setting
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('orders.auto_close_on_full_payment', 'false', CURRENT_TIMESTAMP)",
        # Quick Add: allow direct 1-unit add from product list without opening config modal
        "ALTER TABLE products ADD COLUMN quick_add_enabled INTEGER NOT NULL DEFAULT 1",
        # Per-waiter settings + favorites sync (JSON blob)
        "ALTER TABLE users ADD COLUMN waiter_settings TEXT",
        # Prep zone print settings — synced with KDS auto-print controls
        "ALTER TABLE prep_zones ADD COLUMN auto_print VARCHAR NOT NULL DEFAULT 'none'",
        "ALTER TABLE prep_zones ADD COLUMN print_copies INTEGER NOT NULL DEFAULT 1",
        # Bypass KDS/serve flow: new orders immediately start as served
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('orders.bypass_kds_serve', 'false', CURRENT_TIMESTAMP)",
        # Prep zone: rename auto_print value 'primary' → 'master' for existing rows
        "UPDATE prep_zones SET auto_print = 'master' WHERE auto_print = 'primary'",
        # Prep zone: master printer (single FK) + per-role copy counts
        "ALTER TABLE prep_zones ADD COLUMN master_printer_id INTEGER REFERENCES printers(id)",
        "ALTER TABLE prep_zones ADD COLUMN master_copies INTEGER NOT NULL DEFAULT 1",
        "ALTER TABLE prep_zones ADD COLUMN secondary_copies INTEGER NOT NULL DEFAULT 1",
        # Prep zone: item sort / category grouping options
        "ALTER TABLE prep_zones ADD COLUMN sort_items_by VARCHAR NOT NULL DEFAULT 'order_time'",
        "ALTER TABLE prep_zones ADD COLUMN group_by_category INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE prep_zones ADD COLUMN category_order TEXT NOT NULL DEFAULT '[]'",
        # Prep zone: ticket formatting option
        "ALTER TABLE prep_zones ADD COLUMN print_checkboxes INTEGER NOT NULL DEFAULT 0",
        # Prep zone: KDS bypass options
        "ALTER TABLE prep_zones ADD COLUMN bypass_pending INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE prep_zones ADD COLUMN bypass_kds INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE prep_zones ADD COLUMN auto_ready_to_served INTEGER NOT NULL DEFAULT 0",
        # Prep zone: migrate existing master_printer_id from first printer in zone (best-effort)
        # (handled in Python at runtime, not SQL — no-op SQL here)
        # Product tags (JSON array of strings)
        "ALTER TABLE products ADD COLUMN tags TEXT",
        # Hide revenue from waiters on shift overview
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('shifts.hide_revenue_from_waiters', 'false', CURRENT_TIMESTAMP)",
        # WebSocket event log — append-only, pruned to 24h on startup
        """CREATE TABLE IF NOT EXISTS sync_events (
            id               INTEGER PRIMARY KEY AUTOINCREMENT,
            event_type       VARCHAR NOT NULL,
            payload          TEXT NOT NULL,
            target_user_ids  TEXT,
            created_at       VARCHAR NOT NULL
        )""",
        "CREATE INDEX IF NOT EXISTS ix_sync_events_created_at ON sync_events (created_at)",
        # Migrate from legacy per-product printer to prep zones: clear old printer_zone_id
        "UPDATE products SET printer_zone_id = NULL WHERE printer_zone_id IS NOT NULL",
        # Per-printer ESC/POS code page index (n in ESC t n) — default 29 = CP737 on Jolimark
        "ALTER TABLE printers ADD COLUMN codepage_n INTEGER NOT NULL DEFAULT 29",
        # Price adjustment: per-item on-the-fly price delta (positive or negative)
        "ALTER TABLE order_items ADD COLUMN price_adjustment REAL NOT NULL DEFAULT 0.0",
        # Customer count: how many people are at the table for this order
        "ALTER TABLE orders ADD COLUMN customer_count INTEGER",
        # Service items: flag on products, these are non-inventoried table-service items
        "ALTER TABLE products ADD COLUMN is_service_item INTEGER NOT NULL DEFAULT 0",
        # Table seat count — physical seats at each table (foundation for per-seat dish assignment)
        "ALTER TABLE tables ADD COLUMN seat_count INTEGER",
        # Courses — optional course assignment for sequenced firing
        "ALTER TABLE order_items ADD COLUMN course_id INTEGER",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('orders.courses_enabled', 'false', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('orders.courses', '[]', CURRENT_TIMESTAMP)",
        # Print job lifecycle tracking — auto-retry system
        """CREATE TABLE IF NOT EXISTS print_jobs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL REFERENCES orders(id),
            printer_id INTEGER NOT NULL REFERENCES printers(id),
            zone_id INTEGER,
            item_ids TEXT NOT NULL,
            copies INTEGER NOT NULL DEFAULT 1,
            status VARCHAR NOT NULL DEFAULT 'pending',
            retry_count INTEGER NOT NULL DEFAULT 0,
            first_attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_attempted_at DATETIME,
            succeeded_at DATETIME,
            cancelled_at DATETIME,
            cancel_reason VARCHAR
        )""",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('orders.waiter_price_adjust_allowed', 'false', CURRENT_TIMESTAMP)",
        # Pricing system — Phase 2 pricing overhaul
        """CREATE TABLE IF NOT EXISTS price_groups (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            description TEXT,
            color VARCHAR,
            is_active INTEGER NOT NULL DEFAULT 0,
            auto_enable_time VARCHAR,
            auto_disable_time VARCHAR,
            auto_days TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_by INTEGER REFERENCES users(id)
        )""",
        """CREATE TABLE IF NOT EXISTS price_modifiers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            description TEXT,
            color VARCHAR,
            is_active INTEGER NOT NULL DEFAULT 1,
            is_favorite INTEGER NOT NULL DEFAULT 0,
            allow_stack INTEGER NOT NULL DEFAULT 0,
            sort_order INTEGER NOT NULL DEFAULT 0,
            scope VARCHAR NOT NULL DEFAULT 'global',
            item_id INTEGER REFERENCES products(id),
            action_type VARCHAR NOT NULL,
            action_value REAL NOT NULL,
            round_to VARCHAR,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_by INTEGER REFERENCES users(id),
            updated_at DATETIME,
            updated_by INTEGER REFERENCES users(id)
        )""",
        """CREATE TABLE IF NOT EXISTS price_modifier_conditions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            modifier_id INTEGER NOT NULL REFERENCES price_modifiers(id) ON DELETE CASCADE,
            condition_type VARCHAR NOT NULL,
            params TEXT NOT NULL DEFAULT '{}'
        )""",
        """CREATE TABLE IF NOT EXISTS price_modifier_targets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            modifier_id INTEGER NOT NULL REFERENCES price_modifiers(id) ON DELETE CASCADE,
            target_type VARCHAR NOT NULL,
            target_id INTEGER,
            target_tag VARCHAR
        )""",
        """CREATE TABLE IF NOT EXISTS deals (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name VARCHAR NOT NULL,
            description TEXT,
            color VARCHAR,
            is_active INTEGER NOT NULL DEFAULT 1,
            sort_order INTEGER NOT NULL DEFAULT 0,
            action_type VARCHAR NOT NULL,
            action_modifier_id INTEGER REFERENCES price_modifiers(id),
            action_value REAL,
            action_free_item_id INTEGER REFERENCES products(id),
            action_free_target_type VARCHAR,
            action_free_target_ids TEXT,
            action_free_quantity INTEGER NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_by INTEGER REFERENCES users(id)
        )""",
        """CREATE TABLE IF NOT EXISTS deal_conditions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            deal_id INTEGER NOT NULL REFERENCES deals(id) ON DELETE CASCADE,
            condition_type VARCHAR NOT NULL,
            params TEXT NOT NULL DEFAULT '{}'
        )""",
        """CREATE TABLE IF NOT EXISTS deal_targets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            deal_id INTEGER NOT NULL REFERENCES deals(id) ON DELETE CASCADE,
            target_type VARCHAR NOT NULL,
            target_id INTEGER,
            target_tag VARCHAR
        )""",
        """CREATE TABLE IF NOT EXISTS price_event_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL REFERENCES orders(id),
            order_item_id INTEGER REFERENCES order_items(id),
            event_type VARCHAR NOT NULL,
            modifier_id INTEGER REFERENCES price_modifiers(id),
            deal_id INTEGER REFERENCES deals(id),
            price_before REAL,
            price_after REAL,
            delta_amount REAL,
            applied_by_user_id INTEGER REFERENCES users(id),
            waiter_note TEXT,
            conditions_snapshot TEXT,
            selected_item_ids TEXT,
            applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )""",
        "CREATE INDEX IF NOT EXISTS ix_price_event_log_order_id ON price_event_log (order_id)",
        "CREATE INDEX IF NOT EXISTS ix_price_event_log_order_item_id ON price_event_log (order_item_id)",
        """CREATE TABLE IF NOT EXISTS waiter_discount_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
            can_apply_discounts INTEGER NOT NULL DEFAULT 0,
            max_discount_percent REAL,
            max_discount_amount REAL,
            max_total_value_shift REAL,
            max_total_value_workday REAL,
            max_items_per_shift INTEGER,
            max_items_per_workday INTEGER,
            max_items_per_order INTEGER
        )""",
        # Extend order_discounts with price snapshot columns for clean audit trail
        "ALTER TABLE order_discounts ADD COLUMN price_before REAL",
        "ALTER TABLE order_discounts ADD COLUMN price_after REAL",
        # Global discount settings in pos_settings
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('discounts.enabled', 'true', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('discounts.max_total_value_workday', '', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('discounts.max_total_value_shift', '', CURRENT_TIMESTAMP)",
        "INSERT OR IGNORE INTO pos_settings (key, value, updated_at) VALUES ('discounts.max_items_per_shift', '', CURRENT_TIMESTAMP)",
        # Deal item binding — which deal created this item, and which item it's linked to
        "ALTER TABLE order_items ADD COLUMN deal_id INTEGER REFERENCES deals(id)",
        "ALTER TABLE order_items ADD COLUMN linked_item_id INTEGER REFERENCES order_items(id)",
        # Multi-select targets: JSON arrays of IDs/tags per target row
        "ALTER TABLE price_modifier_targets ADD COLUMN target_ids TEXT",
        "ALTER TABLE price_modifier_targets ADD COLUMN target_tags TEXT",
        "ALTER TABLE deal_targets ADD COLUMN target_ids TEXT",
        "ALTER TABLE deal_targets ADD COLUMN target_tags TEXT",
        # Role & permissions overhaul — granular per-user permission flags
        # Access permissions
        "ALTER TABLE users ADD COLUMN perm_access_dashboard INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_access_waiter_app INTEGER NOT NULL DEFAULT 1",
        "ALTER TABLE users ADD COLUMN perm_access_kds INTEGER NOT NULL DEFAULT 0",
        # Order action permissions
        "ALTER TABLE users ADD COLUMN perm_cancel_orders INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_apply_discounts INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_modify_prices INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_open_orders INTEGER NOT NULL DEFAULT 1",
        "ALTER TABLE users ADD COLUMN perm_close_orders INTEGER NOT NULL DEFAULT 1",
        # Management permissions
        "ALTER TABLE users ADD COLUMN perm_view_reports INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_manage_staff INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_manage_tables INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_manage_menu INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE users ADD COLUMN perm_manage_settings INTEGER NOT NULL DEFAULT 0",
        # Migrate existing managers/sysadmins to have dashboard access + all management perms
        "UPDATE users SET perm_access_dashboard=1, perm_access_waiter_app=1, perm_access_kds=1, perm_cancel_orders=1, perm_apply_discounts=1, perm_modify_prices=1, perm_open_orders=1, perm_close_orders=1, perm_view_reports=1, perm_manage_staff=1, perm_manage_tables=1, perm_manage_menu=1, perm_manage_settings=1 WHERE role IN ('manager', 'sysadmin')",
        # Migrate existing waiters: update can_cancel_orders → perm_cancel_orders
        "UPDATE users SET perm_cancel_orders = can_cancel_orders WHERE role = 'waiter'",
        # Rename legacy roles: manager → store_manager, sysadmin → superadmin
        "UPDATE users SET role = 'superadmin' WHERE role = 'sysadmin'",
        "UPDATE users SET role = 'store_manager' WHERE role = 'manager'",
        # Feature: modifier groups (folders), multi-select extras, compact mode
        """CREATE TABLE IF NOT EXISTS product_modifier_groups (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL REFERENCES products(id),
            modifier_type VARCHAR NOT NULL,
            name VARCHAR NOT NULL,
            sort_order INTEGER NOT NULL DEFAULT 0
        )""",
        "ALTER TABLE product_options ADD COLUMN multi_select INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_options ADD COLUMN is_compact INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_options ADD COLUMN group_id INTEGER REFERENCES product_modifier_groups(id)",
        "ALTER TABLE product_ingredients ADD COLUMN is_compact INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_ingredients ADD COLUMN group_id INTEGER REFERENCES product_modifier_groups(id)",
        "ALTER TABLE product_preference_choices ADD COLUMN is_compact INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE product_preference_sets ADD COLUMN group_id INTEGER REFERENCES product_modifier_groups(id)",
        # Fiscal printer (ΦΗΜ) — product fields
        "ALTER TABLE products ADD COLUMN fiscal_name VARCHAR",
        "ALTER TABLE products ADD COLUMN fiscal_vat_group_id INTEGER",
        # Fiscal status on orders (NULL=fiscal off, pending/success/failed)
        "ALTER TABLE orders ADD COLUMN fiscal_status VARCHAR",
        # Drop legacy can_cancel_orders column — data already migrated to perm_cancel_orders
        "ALTER TABLE users DROP COLUMN can_cancel_orders",
        # Recovery codes for account self-recovery
        """CREATE TABLE IF NOT EXISTS recovery_codes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            code_hash VARCHAR NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            used_at DATETIME
        )""",
        "CREATE INDEX IF NOT EXISTS ix_recovery_codes_user_id ON recovery_codes (user_id)",
        # Multi-select preference sets
        "ALTER TABLE product_preference_sets ADD COLUMN allow_multi_select INTEGER NOT NULL DEFAULT 0",
        # Per-choice quantity on multi-select preference sets (x2, x3, …)
        "ALTER TABLE product_preference_sets ADD COLUMN allow_choice_quantity INTEGER NOT NULL DEFAULT 0",
        # Tracks the hash of the product image last pushed to the cloud (Xenia Connect)
        "ALTER TABLE products ADD COLUMN cloud_image_hash VARCHAR",
    ]
    for sql in migrations:
        try:
            with engine.connect() as conn:
                conn.execute(text(sql))
                conn.commit()
        except Exception:
            pass


def _init_chat():
    """Bootstrap the system group chat and sync all active users into it."""
    from services.chat_service import ensure_system_group, add_user_to_system_group
    from models.user import User
    from database import SessionLocal
    db = SessionLocal()
    try:
        ensure_system_group(db)
        active_users = db.query(User).filter(User.is_active == True).all()  # noqa: E712
        for u in active_users:
            add_user_to_system_group(db, u.id)
    finally:
        db.close()


@asynccontextmanager
async def lifespan(app: FastAPI):
    import asyncio
    from services.sse_bus import init_loop as sse_init_loop
    from services.ws_bus import init_loop as ws_init_loop, prune_old_events
    from services.reservation_tasks import start_reservation_tasks
    from services.printer_service import start_print_retry_thread
    loop = asyncio.get_running_loop()
    sse_init_loop(loop)
    ws_init_loop(loop)
    Base.metadata.create_all(bind=engine)
    _run_migrations()
    start_print_retry_thread()
    pruned = prune_old_events(hours=24)
    if pruned:
        import logging
        logging.getLogger(__name__).info("ws_bus: pruned %d old sync_events", pruned)
    _init_chat()
    sync_task = await start_cloud_sync()
    reservation_task = await start_reservation_tasks()
    yield
    sync_task.cancel()
    reservation_task.cancel()


app = FastAPI(title="POS Local Backend", lifespan=lifespan)

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)
app.add_middleware(LicenseCheckMiddleware)

# Serve product images as static files
IMAGE_DIR = "/app/data/product_images"
os.makedirs(IMAGE_DIR, exist_ok=True)
app.mount("/static/product_images", StaticFiles(directory=IMAGE_DIR), name="product_images")

# Serve waiter avatars as static files
AVATAR_DIR = "/app/data/avatars"
os.makedirs(AVATAR_DIR, exist_ok=True)
app.mount("/static/avatars", StaticFiles(directory=AVATAR_DIR), name="avatars")

app.include_router(setup_router.router,           prefix="/api/setup",        tags=["setup"])
app.include_router(auth.router,                  prefix="/api/auth",         tags=["auth"])
app.include_router(tables.router,                prefix="/api/tables",       tags=["tables"])
app.include_router(products.router,              prefix="/api/products",     tags=["products"])
app.include_router(orders.router,                prefix="/api/orders",       tags=["orders"])
app.include_router(waiters.router,               prefix="/api/waiters",      tags=["waiters"])
app.include_router(reports.router,               prefix="/api/reports",      tags=["reports"])
app.include_router(system.router,                prefix="/api/system",       tags=["system"])
app.include_router(business_day_router.router,   prefix="/api/business-day", tags=["business-day"])
app.include_router(shifts_router.router,         prefix="/api/shifts",       tags=["shifts"])
app.include_router(settings_router.router,       prefix="/api/settings",     tags=["settings"])
app.include_router(flags_router.router,          prefix="/api/flags",        tags=["flags"])
app.include_router(messages_router.router,       prefix="/api/notifications", tags=["messages"])
app.include_router(sse_router.router,            prefix="/api/sse",          tags=["sse"])
app.include_router(ws_router.router,             prefix="/api/ws",           tags=["ws"])
app.include_router(data_transfer_router.router,  prefix="/api/data-transfer", tags=["data-transfer"])
app.include_router(connect_orders_router.router, prefix="/api/connect",      tags=["connect"])
app.include_router(reservations_router.router,   prefix="/api/reservations", tags=["reservations"])
app.include_router(notes_router.router,          prefix="/api/notes",        tags=["notes"])
app.include_router(contacts_router,              prefix="/api/contacts",     tags=["contacts"])
app.include_router(expenses_router,              prefix="/api/expenses",     tags=["expenses"])
app.include_router(customers_router.router,      prefix="/api/customers",    tags=["customers"])
app.include_router(tabs_router.router,           prefix="/api/tabs",         tags=["tabs"])
app.include_router(waste_router.router,          prefix="/api/waste",        tags=["waste"])
app.include_router(kds_router.router,            prefix="/api/kds",          tags=["kds"])
app.include_router(schedule_router.router,       prefix="/api/schedule",     tags=["schedule"])
app.include_router(chat_router.router,           prefix="/api/chat",         tags=["chat"])
app.include_router(prep_zones_router.router,     prefix="/api/prep-zones",   tags=["prep-zones"])
app.include_router(pricing_router.router,        prefix="/api/pricing",      tags=["pricing"])
app.include_router(fiscal_router.router,         prefix="/api/fiscal",       tags=["fiscal"])
app.include_router(phone_router.router,          prefix="/api/phone",        tags=["phone"])
app.include_router(recovery_router.router,       prefix="/api/recovery",     tags=["recovery"])