CREATE TABLE IF NOT EXISTS tenants (id INTEGER PRIMARY KEY AUTOINCREMENT, slug TEXT UNIQUE NOT NULL, name TEXT NOT NULL, logo TEXT, primary_color TEXT DEFAULT '#f58220', active INTEGER DEFAULT 1, created_at TEXT);
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, tenant_id INTEGER NOT NULL, name TEXT, email TEXT NOT NULL, role TEXT DEFAULT 'staff', active INTEGER DEFAULT 1, UNIQUE(tenant_id,email));
CREATE TABLE IF NOT EXISTS stores (id INTEGER PRIMARY KEY AUTOINCREMENT, tenant_id INTEGER NOT NULL, name TEXT NOT NULL, code TEXT, address TEXT, active INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS tills (id INTEGER PRIMARY KEY AUTOINCREMENT, tenant_id INTEGER NOT NULL, store_id INTEGER NOT NULL, name TEXT NOT NULL, code TEXT, active INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS customers (id TEXT PRIMARY KEY, tenant_id INTEGER NOT NULL, name TEXT, organisation TEXT, email TEXT, phone TEXT, postcode TEXT, address TEXT, created_at TEXT, updated_at TEXT);
CREATE TABLE IF NOT EXISTS products (id TEXT PRIMARY KEY, tenant_id INTEGER NOT NULL, sku TEXT, barcode TEXT, name TEXT, description TEXT, price REAL DEFAULT 0, vat_rate REAL DEFAULT 20, pack_size TEXT, active INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS stock (tenant_id INTEGER NOT NULL, product_id TEXT NOT NULL, store_id INTEGER NOT NULL, qty INTEGER DEFAULT 0, bin_location TEXT, PRIMARY KEY(tenant_id,product_id,store_id));
CREATE TABLE IF NOT EXISTS orders (id TEXT PRIMARY KEY, tenant_id INTEGER NOT NULL, customer_id TEXT, store_id INTEGER, till_id INTEGER, staff_id INTEGER, status TEXT, total REAL, payment_json TEXT, created_at TEXT);
CREATE TABLE IF NOT EXISTS order_items (id INTEGER PRIMARY KEY AUTOINCREMENT, order_id TEXT, tenant_id INTEGER, product_id TEXT, sku TEXT, name TEXT, qty INTEGER, price REAL, total REAL);
