-- MenuMatic schema. Every table starts with the prefix dvmmatic_ (change it with: php install.php, which reads the prefix from the config). MySQL 8 or MariaDB 10.5+, utf8mb4. -- Run once on an empty database, in this order. Every DATETIME is stored in UTC. -- Create the first platform admin afterwards with: php admin/cli/create_admin.php superadmin you@company.com "Your Name" SET NAMES utf8mb4; CREATE TABLE dvmmatic_food_outlets ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(64) NOT NULL COMMENT 'public url key: lowercase letters, digits, hyphen', name VARCHAR(120) NOT NULL, name_ar VARCHAR(120) NULL, timezone VARCHAR(40) NOT NULL DEFAULT 'Asia/Dubai' COMMENT 'opening hours are in this time zone', logo_file VARCHAR(190) NULL COMMENT 'file name only - folder is /uploads/o{id}/logos/', primary_color CHAR(7) NOT NULL DEFAULT '#16a34a', secondary_color CHAR(7) NULL, whatsapp_number VARCHAR(20) NULL COMMENT 'digits only incl. country code, e.g. 971501234567', phone VARCHAR(30) NULL, address VARCHAR(255) NULL, address_ar VARCHAR(255) NULL, maps_url VARCHAR(500) NULL, latitude DECIMAL(9,6) NULL, longitude DECIMAL(9,6) NULL, facebook_url VARCHAR(255) NULL, instagram_url VARCHAR(255) NULL, currency_code CHAR(3) NOT NULL DEFAULT 'AED' COMMENT 'ISO 4217: AED, SAR, QAR ...', tax_pct DECIMAL(5,2) NOT NULL DEFAULT 5.00, tax_inclusive TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = menu prices already include tax', default_lang ENUM('en','ar') NOT NULL DEFAULT 'en', arabic_enabled TINYINT(1) NOT NULL DEFAULT 0, status ENUM('trial','active','suspended') NOT NULL DEFAULT 'trial', plan VARCHAR(30) NOT NULL DEFAULT 'basic' COMMENT 'dvmmatic_plans.code; foreign key added in 3.12', plan_overrides JSON NULL COMMENT 'platform-admin changes to this outlet only, e.g. {"max_items":500,"max_storage_mb":2000}', storage_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cached SUM(dvmmatic_outlet_files.bytes); the app keeps it in step', trial_ends_at DATETIME NULL, is_demo TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = prefill demo customer, dvmmatic_orders flagged', crm_ref VARCHAR(64) NULL COMMENT 'the customer id in the external CRM; set when created through the API', api_client_id INT UNSIGNED NULL COMMENT 'which API client owns this outlet; NULL = created in the admin panel', suspended_at DATETIME NULL, suspended_reason VARCHAR(190) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_outlets_slug (slug), UNIQUE KEY uq_outlets_crm (api_client_id, crm_ref), KEY ix_outlets_status (status, deleted_at), CONSTRAINT ck_outlets_primary CHECK (primary_color REGEXP '^#[0-9a-fA-F]{6}$'), CONSTRAINT ck_outlets_secondary CHECK (secondary_color IS NULL OR secondary_color REGEXP '^#[0-9a-fA-F]{6}$'), CONSTRAINT ck_outlets_tax CHECK (tax_pct >= 0 AND tax_pct <= 100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_outlet_order_settings ( outlet_id INT UNSIGNED NOT NULL, accepting_orders TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'switch off to show the menu but block ordering', delivery_enabled TINYINT(1) NOT NULL DEFAULT 1, pickup_enabled TINYINT(1) NOT NULL DEFAULT 0, delivery_fee DECIMAL(8,2) NOT NULL DEFAULT 25.00, free_delivery_over DECIMAL(10,2) NULL COMMENT 'NULL = never free', min_order DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'minimum subtotal for delivery', pay_card TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'card machine on delivery/pickup', pay_cash TINYINT(1) NOT NULL DEFAULT 1, prep_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 15, delivery_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30, order_notice VARCHAR(255) NULL COMMENT 'shown on the cart, e.g. "Orders after 11 PM ship tomorrow"', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (outlet_id), CONSTRAINT fk_oos_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT ck_oos_modes CHECK (delivery_enabled = 1 OR pickup_enabled = 1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_outlet_hours ( outlet_id INT UNSIGNED NOT NULL, weekday TINYINT UNSIGNED NOT NULL COMMENT '0=Sunday ... 6=Saturday', opens_at TIME NOT NULL, closes_at TIME NOT NULL COMMENT 'if earlier than opens_at the shift ends after midnight', PRIMARY KEY (outlet_id, weekday, opens_at), CONSTRAINT fk_hours_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT ck_hours_day CHECK (weekday BETWEEN 0 AND 6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_categories ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, name_ar VARCHAR(120) NULL, image_file VARCHAR(190) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_categories_id_outlet (id, outlet_id), KEY ix_categories_list (outlet_id, deleted_at, is_active, sort_order), CONSTRAINT fk_categories_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, name_ar VARCHAR(160) NULL, description VARCHAR(500) NULL, description_ar VARCHAR(500) NULL, price DECIMAL(10,2) NOT NULL, compare_price DECIMAL(10,2) NULL COMMENT 'price before discount - shown struck through with a Sale tag', image_file VARCHAR(190) NULL, calories SMALLINT UNSIGNED NULL, allergens SET('milk','egg','gluten','nuts','peanuts','soy','fish','shellfish','sesame','mustard','celery','lupin','sulphites','molluscs') NULL, dietary SET('vegetarian','vegan','halal','gluten_free','spicy') NULL, is_featured TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'chef''s special', is_available TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'sold out today: shown but cannot be added', is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'hidden from the menu', sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_items_id_outlet (id, outlet_id), KEY ix_items_menu (outlet_id, category_id, deleted_at, is_active, sort_order), KEY ix_items_featured (outlet_id, is_featured), CONSTRAINT fk_items_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT fk_items_category FOREIGN KEY (category_id, outlet_id) REFERENCES dvmmatic_categories (id, outlet_id) ON DELETE CASCADE, CONSTRAINT ck_items_price CHECK (price >= 0), CONSTRAINT ck_items_compare CHECK (compare_price IS NULL OR compare_price > price) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_option_groups ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, name VARCHAR(80) NOT NULL COMMENT 'admin and customer label, e.g. Size', name_ar VARCHAR(80) NULL, type ENUM('single','multi') NOT NULL DEFAULT 'single', is_required TINYINT(1) NOT NULL DEFAULT 0, min_select TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'multi only - "choose 2 sides"', max_select TINYINT UNSIGNED NULL COMMENT 'multi only - NULL = no limit', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_og_id_outlet (id, outlet_id), KEY ix_og_outlet (outlet_id, is_active), CONSTRAINT fk_og_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT ck_og_range CHECK (max_select IS NULL OR max_select >= min_select) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_option_choices ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, group_id INT UNSIGNED NOT NULL, label VARCHAR(80) NOT NULL, label_ar VARCHAR(80) NULL, price_delta DECIMAL(8,2) NOT NULL DEFAULT 0.00, is_default TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'preselected (single groups: at most one)', is_available TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY ix_oc_group (group_id, sort_order), CONSTRAINT fk_oc_group FOREIGN KEY (group_id) REFERENCES dvmmatic_option_groups (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_item_option_groups ( item_id INT UNSIGNED NOT NULL, group_id INT UNSIGNED NOT NULL, outlet_id INT UNSIGNED NOT NULL, sort_order INT NOT NULL DEFAULT 0, PRIMARY KEY (item_id, group_id), KEY ix_iog_group (group_id), CONSTRAINT fk_iog_item FOREIGN KEY (item_id, outlet_id) REFERENCES dvmmatic_items (id, outlet_id) ON DELETE CASCADE, CONSTRAINT fk_iog_group FOREIGN KEY (group_id, outlet_id) REFERENCES dvmmatic_option_groups (id, outlet_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_banners ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, image_file VARCHAR(190) NOT NULL, heading VARCHAR(120) NULL, heading_ar VARCHAR(120) NULL, description VARCHAR(255) NULL, description_ar VARCHAR(255) NULL, badge VARCHAR(30) NULL, link_category_id INT UNSIGNED NULL COMMENT 'the button scrolls to this category', sort_order INT NOT NULL DEFAULT 0, starts_at DATETIME NULL, ends_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_banners_list (outlet_id, is_active, sort_order), CONSTRAINT fk_banners_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT fk_banners_category FOREIGN KEY (link_category_id) REFERENCES dvmmatic_categories (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_promo_codes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, code VARCHAR(32) NOT NULL COMMENT 'stored uppercase', type ENUM('percent','fixed') NOT NULL, value DECIMAL(10,2) NOT NULL, max_discount DECIMAL(10,2) NULL COMMENT 'cap for percent codes', min_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00, starts_at DATETIME NULL, ends_at DATETIME NULL, usage_limit INT UNSIGNED NULL COMMENT 'NULL = unlimited', used_count INT UNSIGNED NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_promo_code (outlet_id, code), CONSTRAINT fk_promo_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT ck_promo_value CHECK (value > 0 AND (type <> 'percent' OR value <= 100)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, public_ref CHAR(8) NOT NULL COMMENT 'short code shown to the customer, e.g. K7Q2M9TX', status ENUM('new','sent_whatsapp','confirmed','preparing','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'new', order_type ENUM('delivery','pickup') NOT NULL, customer_name VARCHAR(120) NOT NULL, customer_phone VARCHAR(30) NOT NULL, delivery_address VARCHAR(500) NULL, customer_notes VARCHAR(500) NULL, payment_method ENUM('card','cash') NOT NULL, lang ENUM('en','ar') NOT NULL DEFAULT 'en', currency_code CHAR(3) NOT NULL, tax_pct DECIMAL(5,2) NOT NULL, tax_inclusive TINYINT(1) NOT NULL, promo_code VARCHAR(32) NULL, subtotal DECIMAL(10,2) NOT NULL, discount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'total discount = promo + loyalty', loyalty_customer_id BIGINT UNSIGNED NULL, loyalty_points_used INT UNSIGNED NOT NULL DEFAULT 0, loyalty_discount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'the part of discount paid for with points', loyalty_points_earned INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'credited when the order is completed', delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00, tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, total DECIMAL(10,2) NOT NULL, is_demo TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, whatsapp_opened_at DATETIME NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_orders_ref (public_ref), KEY ix_orders_outlet_date (outlet_id, created_at), KEY ix_orders_outlet_status (outlet_id, status), CONSTRAINT fk_orders_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE, CONSTRAINT ck_orders_total CHECK (total >= 0 AND subtotal >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_order_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT UNSIGNED NOT NULL, item_id INT UNSIGNED NULL COMMENT 'SET NULL if the item is ever hard-deleted', name VARCHAR(160) NOT NULL COMMENT 'snapshot', base_price DECIMAL(10,2) NOT NULL COMMENT 'snapshot, before options', unit_price DECIMAL(10,2) NOT NULL COMMENT 'base_price + chosen option deltas', quantity SMALLINT UNSIGNED NOT NULL, line_total DECIMAL(10,2) NOT NULL, note VARCHAR(200) NULL, PRIMARY KEY (id), KEY ix_oi_order (order_id), KEY ix_oi_item (item_id), CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES dvmmatic_orders (id) ON DELETE CASCADE, CONSTRAINT fk_oi_item FOREIGN KEY (item_id) REFERENCES dvmmatic_items (id) ON DELETE SET NULL, CONSTRAINT ck_oi_qty CHECK (quantity BETWEEN 1 AND 99) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_order_item_options ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_item_id BIGINT UNSIGNED NOT NULL, group_name VARCHAR(80) NOT NULL, choice_label VARCHAR(80) NOT NULL, price_delta DECIMAL(8,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (id), KEY ix_oio_item (order_item_id), CONSTRAINT fk_oio_item FOREIGN KEY (order_item_id) REFERENCES dvmmatic_order_items (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_admin_users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NULL COMMENT 'NULL only for superadmin', email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NULL COMMENT 'password_hash() output, never plain text. NULL until the invite is accepted', invite_token_hash CHAR(64) NULL COMMENT 'sha256 of the one-time invite token; the token itself is never stored', invite_expires_at DATETIME NULL, reset_token_hash CHAR(64) NULL COMMENT 'sha256 of the password-reset token. Single use, 30 minutes', reset_expires_at DATETIME NULL, pin_hash VARCHAR(255) NULL COMMENT 'password_hash() of the 4 to 6 digit PIN that approves irreversible actions', session_version INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'raised on password change, so all other sessions end', name VARCHAR(120) NOT NULL, role ENUM('superadmin','support','billing','owner','staff') NOT NULL DEFAULT 'owner' COMMENT 'superadmin, support, billing = platform team (no outlet). owner, staff = outlet', is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_admin_email (email), UNIQUE KEY uq_admin_invite (invite_token_hash), UNIQUE KEY uq_admin_reset (reset_token_hash), KEY ix_admin_outlet (outlet_id), CONSTRAINT fk_admin_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_admin_audit_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NULL, email VARCHAR(190) NULL COMMENT 'the address that was typed, lowercased', event VARCHAR(30) NOT NULL COMMENT 'login_ok, login_fail, logout, expired, pin_ok, pin_fail, pw_changed, reset_req, invite_ok, token_fail, action', detail VARCHAR(255) NULL, ip VARCHAR(45) NOT NULL, user_agent VARCHAR(120) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_aal_ip_time (ip, event, created_at), KEY ix_aal_email_time (email, event, created_at), KEY ix_aal_user (user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_api_clients ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(80) NOT NULL COMMENT 'e.g. Company CRM', key_id CHAR(16) NOT NULL COMMENT 'public part of the key, used to look the client up', secret_hash CHAR(64) NOT NULL COMMENT 'sha256 of the secret part. The secret itself is never stored', scopes VARCHAR(255) NOT NULL COMMENT 'comma list: outlets:read,outlets:create,outlets:update,outlets:suspend,outlets:delete,outlets:purge', allowed_ips VARCHAR(500) NULL COMMENT 'comma list of IPv4, IPv4/CIDR or IPv6. NULL = any address', rate_per_min SMALLINT UNSIGNED NOT NULL DEFAULT 60, status ENUM('active','disabled') NOT NULL DEFAULT 'active', expires_at DATETIME NULL, last_used_at DATETIME NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_api_key_id (key_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE dvmmatic_food_outlets ADD CONSTRAINT fk_outlets_apiclient FOREIGN KEY (api_client_id) REFERENCES dvmmatic_api_clients (id) ON DELETE SET NULL; CREATE TABLE dvmmatic_api_audit_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, client_id INT UNSIGNED NOT NULL, request_id CHAR(16) NOT NULL, method VARCHAR(8) NOT NULL, route VARCHAR(80) NOT NULL COMMENT 'route pattern, e.g. POST /v1/outlets/{id}/suspend', outlet_id INT UNSIGNED NULL COMMENT 'no foreign key: the row must survive a purge', status_code SMALLINT UNSIGNED NOT NULL, error_code VARCHAR(40) NULL, ip VARCHAR(45) NOT NULL, idempotency_key VARCHAR(128) NULL, user_agent VARCHAR(120) NULL, duration_ms INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_audit_client_time (client_id, created_at), KEY ix_audit_outlet (outlet_id), CONSTRAINT fk_audit_client FOREIGN KEY (client_id) REFERENCES dvmmatic_api_clients (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_api_idempotency ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, client_id INT UNSIGNED NOT NULL, idem_key VARCHAR(128) NOT NULL, request_hash CHAR(64) NOT NULL, status_code SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = still running', response_body MEDIUMTEXT NULL COMMENT 'one-time secrets are removed before saving', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_idem (client_id, idem_key), KEY ix_idem_created (created_at), CONSTRAINT fk_idem_client FOREIGN KEY (client_id) REFERENCES dvmmatic_api_clients (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_plans ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(30) NOT NULL COMMENT 'the value stored in dvmmatic_food_outlets.plan and sent by the CRM', name VARCHAR(80) NOT NULL, price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'default monthly charge used when invoices are generated', max_categories INT UNSIGNED NULL, max_items INT UNSIGNED NULL, max_storage_mb INT UNSIGNED NULL, max_admin_users SMALLINT UNSIGNED NULL, max_orders_month INT UNSIGNED NULL, feature_promo TINYINT(1) NOT NULL DEFAULT 0, feature_loyalty TINYINT(1) NOT NULL DEFAULT 0, sort_order SMALLINT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = hidden from new sign-ups. Existing outlets keep it', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_plans_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO dvmmatic_plans (code, name, price_monthly, max_categories, max_items, max_storage_mb, max_admin_users, max_orders_month, feature_promo, feature_loyalty, sort_order) VALUES ('basic', 'Basic', 49.00, 8, 60, 200, 2, 500, 0, 0, 1), ('pro', 'Pro', 129.00, 30, 300, 1000, 5, 5000, 1, 1, 2), ('business', 'Business',299.00, 100, 1500, 5000, 20, NULL, 1, 1, 3); ALTER TABLE dvmmatic_food_outlets ADD CONSTRAINT fk_outlets_plan FOREIGN KEY (plan) REFERENCES dvmmatic_plans (code) ON UPDATE CASCADE; CREATE TABLE dvmmatic_outlet_files ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, kind ENUM('logo','item_photo','banner','other') NOT NULL, file_name VARCHAR(120) NOT NULL COMMENT 'generated name, never the uploaded one', bytes INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_files_name (outlet_id, file_name), CONSTRAINT fk_files_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_loyalty_programs ( outlet_id INT UNSIGNED NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 0, points_per_unit DECIMAL(6,2) NOT NULL DEFAULT 1.00 COMMENT 'points earned per 1.00 of currency on the subtotal after discounts', redeem_points INT UNSIGNED NOT NULL DEFAULT 100 COMMENT 'this many points ...', redeem_value DECIMAL(10,2) NOT NULL DEFAULT 10.00 COMMENT '... are worth this much discount', min_redeem_points INT UNSIGNED NOT NULL DEFAULT 100, max_redeem_pct TINYINT UNSIGNED NOT NULL DEFAULT 50 COMMENT 'points can pay at most this % of the subtotal', welcome_points INT UNSIGNED NOT NULL DEFAULT 0, expiry_months TINYINT UNSIGNED NULL COMMENT 'NULL = points never expire', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (outlet_id), CONSTRAINT fk_loyprog_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_loyalty_customers ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, whatsapp_number VARCHAR(20) NOT NULL COMMENT 'digits only', name VARCHAR(120) NULL, points_balance INT UNSIGNED NOT NULL DEFAULT 0, lifetime_points INT UNSIGNED NOT NULL DEFAULT 0, last_order_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_loycust_phone (outlet_id, whatsapp_number), UNIQUE KEY uq_loycust_outlet (id, outlet_id), CONSTRAINT fk_loycust_outlet FOREIGN KEY (outlet_id) REFERENCES dvmmatic_food_outlets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_loyalty_ledger ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, outlet_id INT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED NULL, type ENUM('welcome','earn','redeem','adjust','expire','reverse') NOT NULL, points INT NOT NULL COMMENT 'positive adds, negative removes', balance_after INT UNSIGNED NOT NULL, note VARCHAR(190) NULL, created_by INT UNSIGNED NULL COMMENT 'dvmmatic_admin_users.id for manual adjustments', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_loyled_customer (customer_id, created_at), KEY ix_loyled_order (order_id), CONSTRAINT fk_loyled_cust FOREIGN KEY (customer_id, outlet_id) REFERENCES dvmmatic_loyalty_customers (id, outlet_id) ON DELETE CASCADE, CONSTRAINT fk_loyled_order FOREIGN KEY (order_id) REFERENCES dvmmatic_orders (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE dvmmatic_orders ADD CONSTRAINT fk_orders_loycust FOREIGN KEY (loyalty_customer_id) REFERENCES dvmmatic_loyalty_customers (id) ON DELETE SET NULL; -- usage for one outlet (the app compares these with the effective limits) SELECT (SELECT COUNT(*) FROM dvmmatic_categories WHERE outlet_id = ? AND deleted_at IS NULL) AS dvmmatic_categories, (SELECT COUNT(*) FROM dvmmatic_items WHERE outlet_id = ? AND deleted_at IS NULL) AS dvmmatic_items, (SELECT COALESCE(SUM(bytes),0) FROM dvmmatic_outlet_files WHERE outlet_id = ?) AS storage_bytes, (SELECT COUNT(*) FROM dvmmatic_admin_users WHERE outlet_id = ? AND is_active = 1) AS dvmmatic_admin_users, (SELECT COUNT(*) FROM dvmmatic_orders WHERE outlet_id = ? AND created_at >= DATE_FORMAT(UTC_TIMESTAMP(), '%Y-%m-01')) AS orders_month; -- --------------------------------------------------------------------------- -- Billing (platform to outlet invoices). Invoices keep their own copy of the outlet name, -- so they stay readable after an outlet is erased. -- --------------------------------------------------------------------------- CREATE TABLE dvmmatic_platform_settings ( k VARCHAR(60) NOT NULL, v TEXT NULL, PRIMARY KEY (k) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_invoices ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, number VARCHAR(40) NULL COMMENT 'set when the invoice is issued', outlet_id INT UNSIGNED NULL, outlet_name VARCHAR(160) NOT NULL, bill_to TEXT NULL COMMENT 'name and address printed under Bill to', period_start DATE NULL, period_end DATE NULL, issue_date DATE NULL, due_date DATE NULL, currency VARCHAR(8) NOT NULL DEFAULT 'AED', subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax_pct DECIMAL(5,2) NOT NULL DEFAULT 0.00, tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, total DECIMAL(12,2) NOT NULL DEFAULT 0.00, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('draft','issued','paid','void') NOT NULL DEFAULT 'draft', notes VARCHAR(500) NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_invoice_number (number), KEY ix_invoice_outlet (outlet_id), KEY ix_invoice_status (status, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_invoice_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, invoice_id INT UNSIGNED NOT NULL, description VARCHAR(255) NOT NULL, qty DECIMAL(10,2) NOT NULL DEFAULT 1.00, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (id), KEY ix_line_invoice (invoice_id), CONSTRAINT fk_line_invoice FOREIGN KEY (invoice_id) REFERENCES dvmmatic_invoices (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE dvmmatic_payments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, invoice_id INT UNSIGNED NOT NULL, paid_on DATE NOT NULL, amount DECIMAL(12,2) NOT NULL, method VARCHAR(30) NOT NULL DEFAULT 'bank', reference VARCHAR(120) NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY ix_pay_invoice (invoice_id), CONSTRAINT fk_pay_invoice FOREIGN KEY (invoice_id) REFERENCES dvmmatic_invoices (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;