PRAGMA foreign_keys=ON;
CREATE TABLE IF NOT EXISTS businesses(id INTEGER PRIMARY KEY, name TEXT NOT NULL, timezone TEXT NOT NULL DEFAULT 'Asia/Kolkata');
CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, role TEXT NOT NULL CHECK(role IN ('admin','staff','driver')), driver_id INTEGER REFERENCES drivers(id));
CREATE TABLE IF NOT EXISTS routes(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL, weekday INTEGER NOT NULL CHECK(weekday BETWEEN 0 AND 6), cutoff_days INTEGER NOT NULL DEFAULT 1 CHECK(cutoff_days BETWEEN 0 AND 6), cutoff_time TEXT NOT NULL DEFAULT '20:00', departure_time TEXT NOT NULL DEFAULT '08:00', active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)));
CREATE TABLE IF NOT EXISTS areas(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL COLLATE NOCASE, city TEXT NOT NULL DEFAULT '', aliases TEXT NOT NULL DEFAULT '', UNIQUE(business_id,name));
CREATE TABLE IF NOT EXISTS route_stops(id INTEGER PRIMARY KEY, route_id INTEGER NOT NULL REFERENCES routes(id), area_id INTEGER NOT NULL UNIQUE REFERENCES areas(id), position INTEGER NOT NULL CHECK(position>0), UNIQUE(route_id,position));
CREATE TABLE IF NOT EXISTS customers(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL, shop TEXT NOT NULL DEFAULT '', phone TEXT NOT NULL, alternate_phone TEXT NOT NULL DEFAULT '', area_id INTEGER REFERENCES areas(id), address TEXT NOT NULL DEFAULT '', landmark TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'active', notes TEXT NOT NULL DEFAULT '', UNIQUE(business_id,phone));
CREATE TABLE IF NOT EXISTS products(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL, sku TEXT NOT NULL, category TEXT NOT NULL DEFAULT '', unit TEXT NOT NULL DEFAULT 'pcs', price REAL NOT NULL DEFAULT 0 CHECK(price>=0), aliases TEXT NOT NULL DEFAULT '', active INTEGER NOT NULL DEFAULT 1, UNIQUE(business_id,sku));
CREATE TABLE IF NOT EXISTS whatsapp_numbers(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), label TEXT NOT NULL, phone_number_id TEXT NOT NULL UNIQUE, token_env TEXT NOT NULL DEFAULT 'WHATSAPP_ACCESS_TOKEN', active INTEGER NOT NULL DEFAULT 1);
CREATE TABLE IF NOT EXISTS conversations(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), customer_id INTEGER NOT NULL REFERENCES customers(id), number_id INTEGER NOT NULL REFERENCES whatsapp_numbers(id), takeover INTEGER NOT NULL DEFAULT 0, pending_json TEXT, last_inbound_at TEXT, UNIQUE(customer_id,number_id));
CREATE TABLE IF NOT EXISTS webhook_events(id INTEGER PRIMARY KEY, body_hash TEXT UNIQUE NOT NULL, raw_body TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'pending', attempts INTEGER NOT NULL DEFAULT 0, error TEXT, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS messages(id INTEGER PRIMARY KEY, conversation_id INTEGER NOT NULL REFERENCES conversations(id), external_id TEXT UNIQUE, direction TEXT NOT NULL CHECK(direction IN ('in','out')), kind TEXT NOT NULL DEFAULT 'text', body TEXT NOT NULL, raw_json TEXT NOT NULL DEFAULT '{}', state TEXT NOT NULL DEFAULT 'pending', intent TEXT, confidence REAL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS drivers(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL, phone TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS vehicles(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL, registration TEXT NOT NULL UNIQUE, active INTEGER NOT NULL DEFAULT 1);
CREATE TABLE IF NOT EXISTS delivery_schedules(id INTEGER PRIMARY KEY, route_id INTEGER NOT NULL REFERENCES routes(id), date TEXT NOT NULL, departed INTEGER NOT NULL DEFAULT 0, vehicle_id INTEGER REFERENCES vehicles(id), driver_id INTEGER REFERENCES drivers(id), UNIQUE(route_id,date));
CREATE TABLE IF NOT EXISTS holidays(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), date TEXT NOT NULL, reason TEXT NOT NULL DEFAULT '', UNIQUE(business_id,date));
CREATE TABLE IF NOT EXISTS orders(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), customer_id INTEGER NOT NULL REFERENCES customers(id), number_id INTEGER REFERENCES whatsapp_numbers(id), message_id INTEGER UNIQUE REFERENCES messages(id), area_id INTEGER NOT NULL REFERENCES areas(id), route_id INTEGER NOT NULL REFERENCES routes(id), delivery_date TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'Scheduled' CHECK(status IN ('New','Needs Confirmation','Confirmed','Scheduled','Out for Delivery','Delivered','Cancelled','Rescheduled')), payment_status TEXT NOT NULL DEFAULT 'Pending' CHECK(payment_status IN ('Pending','Paid','Credit')), source TEXT NOT NULL DEFAULT 'manual', notes TEXT NOT NULL DEFAULT '', fingerprint TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS order_items(id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id INTEGER NOT NULL REFERENCES products(id), quantity REAL NOT NULL CHECK(quantity>0), unit TEXT NOT NULL, price REAL NOT NULL CHECK(price>=0));
CREATE TABLE IF NOT EXISTS deliveries(id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL UNIQUE REFERENCES orders(id), status TEXT NOT NULL DEFAULT 'Pending', notes TEXT NOT NULL DEFAULT '', updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS message_templates(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), name TEXT NOT NULL, body TEXT NOT NULL, UNIQUE(business_id,name));
CREATE TABLE IF NOT EXISTS notifications(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), category TEXT NOT NULL, body TEXT NOT NULL, message_id INTEGER REFERENCES messages(id), resolved INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS outbox(id INTEGER PRIMARY KEY, conversation_id INTEGER NOT NULL REFERENCES conversations(id), message_id INTEGER NOT NULL UNIQUE REFERENCES messages(id), payload_json TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'pending', attempts INTEGER NOT NULL DEFAULT 0, next_attempt_at TEXT NOT NULL, external_id TEXT, error TEXT);
CREATE TABLE IF NOT EXISTS audit_logs(id INTEGER PRIMARY KEY, business_id INTEGER NOT NULL REFERENCES businesses(id), actor TEXT NOT NULL, action TEXT NOT NULL, entity TEXT NOT NULL, entity_id INTEGER, detail TEXT NOT NULL DEFAULT '{}', created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS settings(business_id INTEGER NOT NULL REFERENCES businesses(id), key TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY(business_id,key));
CREATE INDEX IF NOT EXISTS idx_orders_day_route ON orders(business_id,delivery_date,route_id,status);
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id,created_at);
CREATE INDEX IF NOT EXISTS idx_messages_pending ON messages(state,direction,id);
CREATE INDEX IF NOT EXISTS idx_outbox_pending ON outbox(state,next_attempt_at);
CREATE INDEX IF NOT EXISTS idx_audit_time ON audit_logs(business_id,created_at);
CREATE TABLE IF NOT EXISTS keyword_flows(id INTEGER PRIMARY KEY,business_id INTEGER NOT NULL REFERENCES businesses(id),name TEXT NOT NULL,keywords TEXT NOT NULL,match_mode TEXT NOT NULL DEFAULT 'any' CHECK(match_mode IN ('any','all','exact')),action TEXT NOT NULL CHECK(action IN ('reply','order','review')),response TEXT NOT NULL DEFAULT '',priority INTEGER NOT NULL DEFAULT 0,active INTEGER NOT NULL DEFAULT 0,number_id INTEGER REFERENCES whatsapp_numbers(id));
CREATE TABLE IF NOT EXISTS linked_chats(id INTEGER PRIMARY KEY,session TEXT NOT NULL,jid TEXT NOT NULL,name TEXT NOT NULL,phone TEXT,is_group INTEGER NOT NULL DEFAULT 0,customer_id INTEGER REFERENCES customers(id),updated_at TEXT NOT NULL,UNIQUE(session,jid));
CREATE TABLE IF NOT EXISTS linked_messages(id INTEGER PRIMARY KEY,chat_id INTEGER NOT NULL REFERENCES linked_chats(id),external_id TEXT UNIQUE NOT NULL,direction TEXT NOT NULL,kind TEXT NOT NULL,body TEXT NOT NULL,raw_json TEXT NOT NULL,is_history INTEGER NOT NULL DEFAULT 1,created_at TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS idx_linked_chat_messages ON linked_messages(chat_id,created_at);