bonamin
934fda9405
local_backend now uploads each product's image file to cloud_backend during the existing ~5 min menu sync, so the QR menu can show real photos without needing a manually-set digital_image_url. Only re-uploads images whose content hash changed since the last push (Product.cloud_image_hash), to avoid re-sending unchanged binaries every cycle. Also adds a manual "sync now" trigger (POST /api/system/sync-menu) and a matching button in Settings → Operation, for pushing menu/price/image changes immediately instead of waiting for the next automatic cycle. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
839 lines
49 KiB
Python
839 lines
49 KiB
Python
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"])
|