-- =====================================================================
-- Restaurant QR Ordering & Payment SaaS — initial schema
-- Target: MariaDB 10.3+ (also compatible with MySQL 5.7+)
-- Rules:
--   * All timestamps are stored in UTC (connection time_zone = '+00:00').
--   * Every tenant-owned table carries restaurant_id (multi-tenant isolation).
--   * JSON columns never have DB defaults (MySQL 5.7 limitation) — the
--     application always writes them explicitly.
--   * Money = DECIMAL(12,2). Percentages = DECIMAL(5,2).
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- Tenants
-- ---------------------------------------------------------------------
CREATE TABLE restaurants (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(150) NOT NULL,
  slug            VARCHAR(160) NOT NULL,
  description     TEXT NULL,
  logo_url        VARCHAR(500) NULL,
  cover_image_url VARCHAR(500) NULL,
  phone           VARCHAR(40)  NULL,
  email           VARCHAR(190) NULL,
  address         VARCHAR(500) NULL,
  cuisine         VARCHAR(150) NULL,
  status          ENUM('active','suspended') NOT NULL DEFAULT 'active',
  rating_avg      DECIMAL(3,2) NOT NULL DEFAULT 0.00,
  rating_count    INT UNSIGNED NOT NULL DEFAULT 0,
  order_seq       INT UNSIGNED NOT NULL DEFAULT 0,
  bill_seq        INT UNSIGNED NOT NULL DEFAULT 0,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_restaurants_slug (slug),
  KEY idx_restaurants_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Business / billing / customer-experience settings (1 row per restaurant)
CREATE TABLE restaurant_settings (
  restaurant_id                 INT UNSIGNED NOT NULL,
  currency                      CHAR(3)      NOT NULL DEFAULT 'USD',
  locale                        VARCHAR(20)  NOT NULL DEFAULT 'en-US',
  timezone                      VARCHAR(64)  NOT NULL DEFAULT 'UTC',
  tax_label                     VARCHAR(40)  NOT NULL DEFAULT 'Tax',
  tax_rate                      DECIMAL(6,3) NOT NULL DEFAULT 0.000,
  tax_inclusive                 TINYINT(1)   NOT NULL DEFAULT 0,
  service_charge_rate           DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  tips_enabled                  TINYINT(1)   NOT NULL DEFAULT 1,
  tip_percentages               JSON         NOT NULL,            -- e.g. [5,10,15,20]
  allow_custom_tip              TINYINT(1)   NOT NULL DEFAULT 1,
  primary_color                 VARCHAR(20)  NOT NULL DEFAULT '#f97316',
  ordering_enabled              TINYINT(1)   NOT NULL DEFAULT 1,
  ratings_enabled               TINYINT(1)   NOT NULL DEFAULT 1,
  auto_close_session_on_payment TINYINT(1)   NOT NULL DEFAULT 0,
  updated_at                    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (restaurant_id),
  CONSTRAINT fk_settings_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ordering rules (1 row per restaurant)
CREATE TABLE restaurant_order_rules (
  restaurant_id           INT UNSIGNED  NOT NULL,
  min_order_amount        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  max_items_per_order     INT UNSIGNED  NOT NULL DEFAULT 50,      -- max total quantity per order
  auto_confirm_orders     TINYINT(1)    NOT NULL DEFAULT 0,
  allow_customer_cancel   TINYINT(1)    NOT NULL DEFAULT 1,
  cancel_allowed_statuses JSON          NOT NULL,                 -- e.g. ["pending","confirmed"]
  cancel_window_minutes   INT UNSIGNED  NOT NULL DEFAULT 5,       -- 0 = no time limit
  allow_customer_edit     TINYINT(1)    NOT NULL DEFAULT 1,
  edit_allowed_statuses   JSON          NOT NULL,                 -- e.g. ["pending"]
  edit_window_minutes     INT UNSIGNED  NOT NULL DEFAULT 5,       -- 0 = no time limit
  allow_order_pause       TINYINT(1)    NOT NULL DEFAULT 1,
  pause_allowed_statuses  JSON          NOT NULL,                 -- e.g. ["pending","confirmed","preparing"]
  show_unavailable_items  TINYINT(1)    NOT NULL DEFAULT 1,       -- 1 = show as "Sold out", 0 = hide
  require_customer_name   TINYINT(1)    NOT NULL DEFAULT 0,
  updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (restaurant_id),
  CONSTRAINT fk_rules_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Branches / outlets of a restaurant
CREATE TABLE locations (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  name          VARCHAR(150) NOT NULL,
  address       VARCHAR(500) NULL,
  phone         VARCHAR(40)  NULL,
  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,
  PRIMARY KEY (id),
  KEY idx_locations_restaurant (restaurant_id),
  CONSTRAINT fk_locations_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Users & auth
-- ---------------------------------------------------------------------
CREATE TABLE users (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NULL,                                   -- NULL only for platform_admin
  name          VARCHAR(150) NOT NULL,
  email         VARCHAR(190) NOT NULL,
  phone         VARCHAR(40)  NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('platform_admin','restaurant_admin','restaurant_staff') NOT NULL,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_restaurant (restaurant_id),
  CONSTRAINT fk_users_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE refresh_tokens (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  token_hash  CHAR(64)     NOT NULL,                                 -- sha256 hex of the opaque token
  expires_at  DATETIME     NOT NULL,
  revoked_at  DATETIME     NULL,
  user_agent  VARCHAR(255) NULL,
  ip          VARCHAR(64)  NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_refresh_token_hash (token_hash),
  KEY idx_refresh_user (user_id),
  CONSTRAINT fk_refresh_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Tables, dining sessions
-- ---------------------------------------------------------------------
CREATE TABLE dining_tables (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  location_id        INT UNSIGNED NOT NULL,
  name               VARCHAR(60)  NOT NULL,                          -- "T1", "Patio 4"
  area               VARCHAR(80)  NULL,                              -- zone inside the location
  capacity           SMALLINT UNSIGNED NOT NULL DEFAULT 4,
  qr_token           VARCHAR(64)  NOT NULL,                          -- random, unique, regenerable
  status             ENUM('available','occupied','ordering','preparing','bill_open','payment_pending','paid') NOT NULL DEFAULT 'available',
  current_session_id INT UNSIGNED NULL,                              -- latest session in ('open','paid'); NULL when available
  is_active          TINYINT(1)   NOT NULL DEFAULT 1,
  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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tables_qr_token (qr_token),
  UNIQUE KEY uq_tables_location_name (location_id, name),
  KEY idx_tables_restaurant (restaurant_id, status),
  CONSTRAINT fk_tables_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_tables_location   FOREIGN KEY (location_id)   REFERENCES locations (id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One dining session per table occupancy. All devices at the table share it.
CREATE TABLE table_sessions (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id     INT UNSIGNED NOT NULL,
  location_id       INT UNSIGNED NOT NULL,
  table_id          INT UNSIGNED NOT NULL,
  status            ENUM('open','paid','closed') NOT NULL DEFAULT 'open',
  opened_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at           DATETIME NULL,
  closed_at         DATETIME NULL,
  closed_by_user_id INT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_sessions_table_status (table_id, status),
  KEY idx_sessions_restaurant_status (restaurant_id, status),
  CONSTRAINT fk_sessions_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_sessions_table      FOREIGN KEY (table_id)      REFERENCES dining_tables (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Menu
-- ---------------------------------------------------------------------
CREATE TABLE menu_categories (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  name          VARCHAR(100) NOT NULL,
  description   VARCHAR(500) NULL,
  image_url     VARCHAR(500) NULL,
  icon          VARCHAR(16)  NULL,                                   -- emoji, e.g. "🍨"
  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,
  PRIMARY KEY (id),
  KEY idx_categories_restaurant (restaurant_id, sort_order),
  CONSTRAINT fk_categories_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_items (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id     INT UNSIGNED NOT NULL,
  category_id       INT UNSIGNED NOT NULL,
  name              VARCHAR(150) NOT NULL,
  description       TEXT NULL,
  image_url         VARCHAR(500) NULL,
  price             DECIMAL(12,2) NOT NULL,                          -- base price (used when no variants)
  discount_type     ENUM('none','percent','flat') NOT NULL DEFAULT 'none',
  discount_value    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  food_type         ENUM('veg','non_veg') NOT NULL DEFAULT 'veg',
  is_available      TINYINT(1) NOT NULL DEFAULT 1,                   -- staff toggle ("sold out")
  available_from    TIME NULL,                                       -- optional daily window (restaurant timezone)
  available_to      TIME NULL,
  prep_time_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 15,
  badges            JSON NOT NULL,                                   -- subset of ["recommended","popular","chef_special","best_seller","new"]
  is_featured       TINYINT(1) NOT NULL DEFAULT 0,                   -- restaurant suggestion / highlight
  featured_note     VARCHAR(255) NULL,
  rating_avg        DECIMAL(3,2) NOT NULL DEFAULT 0.00,
  rating_count      INT UNSIGNED NOT NULL DEFAULT 0,
  order_count       INT UNSIGNED NOT NULL DEFAULT 0,                 -- popularity (total quantity ordered)
  sort_order        INT NOT NULL DEFAULT 0,
  is_active         TINYINT(1) NOT NULL DEFAULT 1,                   -- 0 = hidden from customers
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_items_restaurant_category (restaurant_id, category_id, sort_order),
  KEY idx_items_restaurant_featured (restaurant_id, is_featured),
  CONSTRAINT fk_items_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_items_category   FOREIGN KEY (category_id)   REFERENCES menu_categories (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_item_variants (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  menu_item_id  INT UNSIGNED NOT NULL,
  name          VARCHAR(100) NOT NULL,                               -- "Half", "Full", "Large"
  price         DECIMAL(12,2) NOT NULL,                              -- absolute price, replaces item price
  is_default    TINYINT(1) NOT NULL DEFAULT 0,
  is_available  TINYINT(1) NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_variants_item (menu_item_id),
  CONSTRAINT fk_variants_item FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_addon_groups (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  menu_item_id  INT UNSIGNED NOT NULL,
  name          VARCHAR(100) NOT NULL,                               -- "Extra toppings"
  min_select    TINYINT UNSIGNED NOT NULL DEFAULT 0,                 -- >0 means required
  max_select    TINYINT UNSIGNED NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_addon_groups_item (menu_item_id),
  CONSTRAINT fk_addon_groups_item FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_addons (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  group_id      INT UNSIGNED NOT NULL,
  name          VARCHAR(100) NOT NULL,
  price         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_available  TINYINT(1) NOT NULL DEFAULT 1,
  sort_order    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_addons_group (group_id),
  CONSTRAINT fk_addons_group FOREIGN KEY (group_id) REFERENCES menu_addon_groups (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Bills & orders
-- ---------------------------------------------------------------------
CREATE TABLE bills (
  id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id         INT UNSIGNED NOT NULL,
  table_session_id      INT UNSIGNED NOT NULL,
  table_id              INT UNSIGNED NOT NULL,
  bill_number           VARCHAR(40)  NOT NULL,                       -- "B-000123"
  status                ENUM('open','payment_pending','paid','void') NOT NULL DEFAULT 'open',
  currency              CHAR(3) NOT NULL,
  subtotal              DECIMAL(12,2) NOT NULL DEFAULT 0.00,         -- Σ order_items.line_total of non-cancelled orders
  item_discount_total   DECIMAL(12,2) NOT NULL DEFAULT 0.00,         -- informational: savings from item discounts
  discount_amount       DECIMAL(12,2) NOT NULL DEFAULT 0.00,         -- manual bill-level discount (staff)
  discount_reason       VARCHAR(255) NULL,
  service_charge_rate   DECIMAL(5,2)  NOT NULL DEFAULT 0.00,         -- snapshot
  service_charge_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  tax_label             VARCHAR(40)   NOT NULL DEFAULT 'Tax',        -- snapshot
  tax_rate              DECIMAL(6,3)  NOT NULL DEFAULT 0.000,        -- snapshot (3dp: e.g. 8.875)
  tax_inclusive         TINYINT(1)    NOT NULL DEFAULT 0,            -- snapshot
  tax_amount            DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  tip_percentage        DECIMAL(5,2)  NULL,                          -- NULL when custom amount / no tip
  tip_amount            DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount          DECIMAL(12,2) NOT NULL DEFAULT 0.00,         -- grand total INCLUDING tip
  amount_paid           DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  pos_sync_status       ENUM('not_required','pending','synced','failed') NOT NULL DEFAULT 'not_required',
  pos_external_ref      VARCHAR(191) NULL,
  paid_at               DATETIME NULL,
  created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_bills_session (table_session_id),
  UNIQUE KEY uq_bills_number (restaurant_id, bill_number),
  KEY idx_bills_restaurant_status (restaurant_id, status, created_at),
  CONSTRAINT fk_bills_restaurant FOREIGN KEY (restaurant_id)    REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_bills_session    FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
  id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id       INT UNSIGNED NOT NULL,
  location_id         INT UNSIGNED NOT NULL,
  table_id            INT UNSIGNED NOT NULL,
  table_session_id    INT UNSIGNED NOT NULL,
  bill_id             INT UNSIGNED NOT NULL,
  order_number        VARCHAR(30)  NOT NULL,                         -- "#0042"
  source              ENUM('qr','pos') NOT NULL DEFAULT 'qr',
  customer_device_id  VARCHAR(64)  NULL,
  customer_name       VARCHAR(100) NULL,
  created_by_user_id  INT UNSIGNED NULL,                             -- staff who created a POS order
  status              ENUM('pending','confirmed','preparing','ready','served','completed','cancelled','paused') NOT NULL DEFAULT 'pending',
  paused_from_status  ENUM('pending','confirmed','preparing','ready','served') NULL,
  notes               VARCHAR(500) NULL,
  subtotal            DECIMAL(12,2) NOT NULL DEFAULT 0.00,           -- Σ order_items.line_total
  item_count          INT UNSIGNED NOT NULL DEFAULT 0,               -- Σ quantity
  estimated_ready_at  DATETIME NULL,
  cancelled_reason    VARCHAR(255) NULL,
  cancelled_by        ENUM('customer','staff') NULL,
  placed_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  confirmed_at        DATETIME NULL,
  preparing_at        DATETIME NULL,
  ready_at            DATETIME NULL,
  served_at           DATETIME NULL,
  completed_at        DATETIME NULL,
  cancelled_at        DATETIME NULL,
  paused_at           DATETIME NULL,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_orders_number (restaurant_id, order_number),
  KEY idx_orders_restaurant_status (restaurant_id, status, placed_at),
  KEY idx_orders_session (table_session_id),
  KEY idx_orders_bill (bill_id),
  CONSTRAINT fk_orders_restaurant FOREIGN KEY (restaurant_id)    REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_orders_session    FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE,
  CONSTRAINT fk_orders_bill       FOREIGN KEY (bill_id)          REFERENCES bills (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id        INT UNSIGNED NOT NULL,
  order_id             INT UNSIGNED NOT NULL,
  menu_item_id         INT UNSIGNED NULL,
  variant_id           INT UNSIGNED NULL,
  item_name            VARCHAR(150) NOT NULL,                        -- snapshot
  variant_name         VARCHAR(100) NULL,                            -- snapshot
  food_type            ENUM('veg','non_veg') NOT NULL,
  image_url            VARCHAR(500) NULL,
  original_unit_price  DECIMAL(12,2) NOT NULL,                       -- variant/item price before discount
  unit_price           DECIMAL(12,2) NOT NULL,                       -- after item discount
  addons               JSON NOT NULL,                                -- [{id, groupName, name, price}]
  addons_total         DECIMAL(12,2) NOT NULL DEFAULT 0.00,          -- per unit
  quantity             INT UNSIGNED NOT NULL,
  line_total           DECIMAL(12,2) NOT NULL,                       -- (unit_price + addons_total) * quantity
  special_instructions VARCHAR(500) NULL,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_order_items_order (order_id),
  KEY idx_order_items_menu_item (menu_item_id),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)     REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_item  FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_status_history (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  order_id      INT UNSIGNED NOT NULL,
  from_status   VARCHAR(20)  NULL,
  to_status     VARCHAR(20)  NOT NULL,
  actor_type    ENUM('customer','staff','system') NOT NULL,
  actor_user_id INT UNSIGNED NULL,
  note          VARCHAR(255) NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_history_order (order_id),
  CONSTRAINT fk_history_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Payments
-- ---------------------------------------------------------------------
CREATE TABLE payment_gateway_configs (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  provider      VARCHAR(40)  NOT NULL,                               -- 'stripe' | 'razorpay' | 'mock' | future
  display_name  VARCHAR(100) NULL,
  is_active     TINYINT(1)   NOT NULL DEFAULT 0,                     -- at most one active per restaurant (service-enforced)
  mode          ENUM('test','live') NOT NULL DEFAULT 'test',
  public_config JSON NOT NULL,                                       -- non-secret fields
  secret_config TEXT NULL,                                           -- AES-256-GCM encrypted JSON of secret fields
  webhook_key   VARCHAR(64)  NOT NULL,                               -- random path segment routing webhooks to this config
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_gateway_restaurant_provider (restaurant_id, provider),
  UNIQUE KEY uq_gateway_webhook_key (webhook_key),
  CONSTRAINT fk_gateway_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id                      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id           INT UNSIGNED NOT NULL,
  bill_id                 INT UNSIGNED NOT NULL,
  table_session_id        INT UNSIGNED NOT NULL,
  gateway_config_id       INT UNSIGNED NULL,
  provider                VARCHAR(40)  NOT NULL,                     -- gateway key, or 'manual' for staff-recorded payments
  method                  ENUM('online','cash','card_terminal','other') NOT NULL DEFAULT 'online',
  status                  ENUM('pending','processing','succeeded','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
  amount                  DECIMAL(12,2) NOT NULL,                    -- total charged INCLUDING tip
  tip_amount              DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  currency                CHAR(3) NOT NULL,
  provider_payment_id     VARCHAR(191) NULL,                         -- checkout session id / gateway order id
  provider_transaction_id VARCHAR(191) NULL,                         -- payment intent / gateway payment id
  checkout_url            VARCHAR(1000) NULL,
  idempotency_key         VARCHAR(64)  NOT NULL,
  failure_reason          VARCHAR(500) NULL,
  recorded_by_user_id     INT UNSIGNED NULL,                         -- staff for manual payments
  metadata                JSON NULL,
  paid_at                 DATETIME NULL,
  created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payments_idempotency (idempotency_key),
  KEY idx_payments_restaurant (restaurant_id, status, created_at),
  KEY idx_payments_bill (bill_id),
  KEY idx_payments_provider_ref (provider, provider_payment_id),
  CONSTRAINT fk_payments_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_bill       FOREIGN KEY (bill_id)       REFERENCES bills (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_webhook_events (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id    INT UNSIGNED NULL,
  provider         VARCHAR(40)  NOT NULL,
  event_id         VARCHAR(191) NOT NULL,                            -- provider event id (idempotency)
  event_type       VARCHAR(100) NULL,
  payment_id       INT UNSIGNED NULL,
  signature_valid  TINYINT(1)   NOT NULL DEFAULT 0,
  processed        TINYINT(1)   NOT NULL DEFAULT 0,
  processing_error VARCHAR(1000) NULL,
  payload          LONGTEXT NULL,                                    -- raw body (never contains card data)
  received_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at     DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_webhook_provider_event (provider, event_id),
  KEY idx_webhook_payment (payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- POS integration
-- ---------------------------------------------------------------------
CREATE TABLE pos_configs (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id     INT UNSIGNED NOT NULL,
  provider          VARCHAR(40)  NOT NULL,                           -- 'internal' | 'square' | 'clover' | 'lightspeed' | future
  is_active         TINYINT(1)   NOT NULL DEFAULT 0,                 -- at most one active per restaurant (service-enforced)
  public_config     JSON NOT NULL,
  secret_config     TEXT NULL,                                       -- AES-256-GCM encrypted JSON
  last_tested_at    DATETIME NULL,
  last_test_ok      TINYINT(1) NULL,
  last_test_message VARCHAR(500) NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pos_restaurant_provider (restaurant_id, provider),
  CONSTRAINT fk_pos_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pos_sync_jobs (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  bill_id            INT UNSIGNED NOT NULL,
  payment_id         INT UNSIGNED NOT NULL,
  pos_config_id      INT UNSIGNED NULL,
  provider           VARCHAR(40)  NOT NULL,
  job_type           VARCHAR(40)  NOT NULL DEFAULT 'payment_completed',
  status             ENUM('pending','processing','succeeded','failed','dead') NOT NULL DEFAULT 'pending',
  attempts           INT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts       INT UNSIGNED NOT NULL DEFAULT 8,
  next_attempt_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_at          DATETIME NULL,
  last_error         VARCHAR(1000) NULL,
  external_reference VARCHAR(191) NULL,
  idempotency_key    VARCHAR(100) NOT NULL,                          -- "payment:{paymentId}" → never duplicated
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at       DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pos_jobs_idempotency (idempotency_key),
  KEY idx_pos_jobs_due (status, next_attempt_at),
  KEY idx_pos_jobs_restaurant (restaurant_id, status, created_at),
  CONSTRAINT fk_pos_jobs_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_pos_jobs_bill       FOREIGN KEY (bill_id)       REFERENCES bills (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pos_sync_attempts (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id       INT UNSIGNED NOT NULL,
  attempt_no   INT UNSIGNED NOT NULL,
  success      TINYINT(1)   NOT NULL,
  message      VARCHAR(1000) NULL,
  duration_ms  INT UNSIGNED NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pos_attempts_job (job_id),
  CONSTRAINT fk_pos_attempts_job FOREIGN KEY (job_id) REFERENCES pos_sync_jobs (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Ratings & reviews
-- ---------------------------------------------------------------------
CREATE TABLE restaurant_reviews (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  table_session_id   INT UNSIGNED NULL,
  table_id           INT UNSIGNED NULL,
  customer_device_id VARCHAR(64)  NULL,
  customer_name      VARCHAR(100) NULL,
  rating             TINYINT UNSIGNED NOT NULL,                      -- 1..5
  review             TEXT NULL,
  is_visible         TINYINT(1) NOT NULL DEFAULT 1,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_restaurant_review_session_device (table_session_id, customer_device_id),
  KEY idx_restaurant_reviews (restaurant_id, is_visible, created_at),
  CONSTRAINT fk_restaurant_reviews_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE item_reviews (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  menu_item_id       INT UNSIGNED NOT NULL,
  order_item_id      INT UNSIGNED NOT NULL,
  table_session_id   INT UNSIGNED NULL,
  customer_device_id VARCHAR(64)  NULL,
  customer_name      VARCHAR(100) NULL,
  rating             TINYINT UNSIGNED NOT NULL,                      -- 1..5
  review             TEXT NULL,
  is_visible         TINYINT(1) NOT NULL DEFAULT 1,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_item_review_order_item (order_item_id),
  KEY idx_item_reviews_item (menu_item_id, is_visible, created_at),
  KEY idx_item_reviews_restaurant (restaurant_id, created_at),
  CONSTRAINT fk_item_reviews_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_item_reviews_item       FOREIGN KEY (menu_item_id)  REFERENCES menu_items (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
