-- =====================================================================
-- 011: Back office (v8) — GST tax invoice, India defaults, split bills,
--      thermal printing (KOT/bills), reports & day-end, inventory,
--      online orders (Zomato/Swiggy via webhook or manual entry),
--      offline counter billing. Spec: docs/FEATURES-BACK-OFFICE.md
-- =====================================================================

-- ---------------------------------------------------------------------
-- India defaults: tips OFF for new restaurants (service charge already defaults to 0).
-- Existing restaurants keep their current choice.
-- ---------------------------------------------------------------------
ALTER TABLE restaurant_settings ALTER COLUMN tips_enabled SET DEFAULT 0;

ALTER TABLE restaurant_settings
  ADD COLUMN service_charge_optional  TINYINT(1)   NOT NULL DEFAULT 1 AFTER menu_book_show_descriptions, -- shown as "optional"; staff can remove it per bill
  ADD COLUMN invoice_legal_name       VARCHAR(150) NULL AFTER service_charge_optional, -- null = restaurant name
  ADD COLUMN gstin                    VARCHAR(15)  NULL AFTER invoice_legal_name,
  ADD COLUMN fssai_number             VARCHAR(14)  NULL AFTER gstin,
  ADD COLUMN gst_mode                 ENUM('none','cgst_sgst','igst') NOT NULL DEFAULT 'none' AFTER fssai_number,
  ADD COLUMN sac_code                 VARCHAR(8)   NOT NULL DEFAULT '996331' AFTER gst_mode,      -- restaurant service
  ADD COLUMN invoice_footer           VARCHAR(255) NULL AFTER sac_code,
  ADD COLUMN print_paper_width        ENUM('58','80') NOT NULL DEFAULT '80' AFTER invoice_footer,
  ADD COLUMN print_auto_kot           TINYINT(1)   NOT NULL DEFAULT 1 AFTER print_paper_width,
  ADD COLUMN print_kot_copies         TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER print_auto_kot,
  ADD COLUMN print_auto_bill          TINYINT(1)   NOT NULL DEFAULT 0 AFTER print_kot_copies,
  ADD COLUMN business_day_start_hour  TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER print_auto_bill, -- 0..6: sales before this hour count for the previous day
  ADD COLUMN inventory_enabled        TINYINT(1)   NOT NULL DEFAULT 0 AFTER business_day_start_hour,
  ADD COLUMN online_orders_enabled    TINYINT(1)   NOT NULL DEFAULT 0 AFTER inventory_enabled;

-- ---------------------------------------------------------------------
-- Bills: optional service charge, frozen invoice details, UPI as a manual payment method
-- ---------------------------------------------------------------------
ALTER TABLE bills
  ADD COLUMN service_charge_waived TINYINT(1) NOT NULL DEFAULT 0 AFTER service_charge_amount,
  ADD COLUMN invoice_snapshot      JSON NULL AFTER pos_external_ref;   -- InvoiceInfo frozen when the bill is paid

ALTER TABLE payments
  MODIFY COLUMN method ENUM('online','cash','card_terminal','upi','other') NOT NULL DEFAULT 'online',
  ADD COLUMN bill_split_id INT UNSIGNED NULL AFTER bill_id,
  ADD KEY idx_payments_bill_split (bill_split_id);

-- ---------------------------------------------------------------------
-- Split bills: the bill (one tax invoice) divided into parts paid separately
-- ---------------------------------------------------------------------
CREATE TABLE bill_splits (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  bill_id            INT UNSIGNED NOT NULL,
  mode               ENUM('equal','items','custom') NOT NULL,
  part_no            TINYINT UNSIGNED NOT NULL,              -- 1..20
  label              VARCHAR(60)  NOT NULL,                  -- "Part 1" or a guest name
  amount             DECIMAL(12,2) NOT NULL,                 -- share of the bill total (incl. tax, charges and bill tip)
  items              JSON NULL,                              -- items mode: [{orderItemId, quantity}]
  status             ENUM('unpaid','paid') NOT NULL DEFAULT 'unpaid',
  paid_at            DATETIME NULL,
  created_by_user_id INT UNSIGNED 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_bill_splits_part (bill_id, part_no),
  KEY idx_bill_splits_restaurant (restaurant_id, bill_id),
  CONSTRAINT fk_bill_splits_bill FOREIGN KEY (bill_id) REFERENCES bills (id) ON DELETE CASCADE,
  CONSTRAINT fk_bill_splits_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Orders: sales channel (Zomato/Swiggy/…), external id, online details, inventory state
-- ---------------------------------------------------------------------
ALTER TABLE orders
  MODIFY COLUMN source ENUM('qr','pos','online') NOT NULL DEFAULT 'qr',
  ADD COLUMN channel           ENUM('direct','zomato','swiggy','other') NOT NULL DEFAULT 'direct' AFTER source,
  ADD COLUMN external_order_id VARCHAR(64) NULL AFTER channel,
  ADD COLUMN online_meta       JSON NULL AFTER external_order_id,      -- OnlineOrderMeta
  ADD COLUMN inventory_state   ENUM('none','deducted','reversed') NOT NULL DEFAULT 'none' AFTER online_meta,
  ADD UNIQUE KEY uq_orders_external (restaurant_id, channel, external_order_id),
  ADD KEY idx_orders_channel (restaurant_id, channel, placed_at);

-- System counters (one per location and kind, created on demand): never a guest QR, hidden from QR printing.
ALTER TABLE dining_tables
  ADD COLUMN system_kind ENUM('online','counter_sale') NULL AFTER kind,
  ADD UNIQUE KEY uq_tables_system_kind (location_id, system_kind);

-- Aggregator item mapping (Zomato/Swiggy item codes → our dishes)
ALTER TABLE menu_items
  ADD COLUMN external_code VARCHAR(40) NULL,
  ADD KEY idx_menu_items_external_code (restaurant_id, external_code);

-- ---------------------------------------------------------------------
-- Printing: stations (a browser kept open next to a printer) and print jobs
-- ---------------------------------------------------------------------
CREATE TABLE print_stations (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id  INT UNSIGNED NOT NULL,
  location_id    INT UNSIGNED NOT NULL,
  name           VARCHAR(60)  NOT NULL,
  kinds          SET('kot','bill','split','day_end') NOT NULL DEFAULT 'kot,bill,split,day_end',
  category_ids   JSON NULL,                      -- KOTs only for these menu categories (null = all)
  paper_width    ENUM('58','80') NULL,           -- null = restaurant default
  is_active      TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_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),
  KEY idx_print_stations_restaurant (restaurant_id, location_id),
  CONSTRAINT fk_print_stations_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_print_stations_location FOREIGN KEY (location_id) REFERENCES locations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE print_jobs (
  id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id         INT UNSIGNED NOT NULL,
  location_id           INT UNSIGNED NOT NULL,
  station_id            INT UNSIGNED NULL,       -- null = any station of the location that prints this kind
  kind                  ENUM('kot','bill','split','day_end') NOT NULL,
  reason                ENUM('auto','reprint','manual') NOT NULL DEFAULT 'auto',
  order_id              INT UNSIGNED NULL,
  bill_id               INT UNSIGNED NULL,
  bill_split_id         INT UNSIGNED NULL,
  day_closing_id        INT UNSIGNED NULL,
  payload               JSON NULL,               -- kot: {orderItemIds:[…], revised:bool, itemsHash}
  title                 VARCHAR(80) NOT NULL,    -- "KOT #0042 · T1"
  status                ENUM('pending','claimed','printed','failed','cancelled') NOT NULL DEFAULT 'pending',
  claimed_by_station_id INT UNSIGNED NULL,
  claimed_at            DATETIME NULL,
  printed_at            DATETIME NULL,
  attempts              TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error                 VARCHAR(255) NULL,
  created_by_user_id    INT UNSIGNED NULL,
  created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_print_jobs_queue (restaurant_id, location_id, status, created_at),
  KEY idx_print_jobs_order (order_id),
  KEY idx_print_jobs_bill (bill_id),
  CONSTRAINT fk_print_jobs_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Reports: day-end closing (Z report)
-- ---------------------------------------------------------------------
CREATE TABLE day_closings (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  location_id        INT UNSIGNED NOT NULL DEFAULT 0,   -- 0 = all locations
  business_date      DATE NOT NULL,
  opening_cash       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  expected_cash      DECIMAL(12,2) NOT NULL,
  counted_cash       DECIMAL(12,2) NOT NULL,
  cash_difference    DECIMAL(12,2) NOT NULL,
  totals             JSON NOT NULL,                     -- DayEndReport snapshot at closing time
  notes              VARCHAR(255) NULL,
  closed_by_user_id  INT UNSIGNED NULL,
  closed_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_day_closings (restaurant_id, location_id, business_date),
  CONSTRAINT fk_day_closings_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Inventory: raw materials, stock per location, recipes, movements, purchases
-- ---------------------------------------------------------------------
CREATE TABLE inventory_items (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id   INT UNSIGNED NOT NULL,
  name            VARCHAR(100) NOT NULL,
  unit            ENUM('kg','g','l','ml','pcs','pack','dozen') NOT NULL,
  category        VARCHAR(60) NULL,
  cost_per_unit   DECIMAL(12,4) NOT NULL DEFAULT 0,
  low_stock_level DECIMAL(14,3) 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),
  UNIQUE KEY uq_inventory_items_name (restaurant_id, name),
  CONSTRAINT fk_inventory_items_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inventory_stock (
  item_id        INT UNSIGNED NOT NULL,
  location_id    INT UNSIGNED NOT NULL,
  restaurant_id  INT UNSIGNED NOT NULL,
  quantity       DECIMAL(14,3) NOT NULL DEFAULT 0,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (item_id, location_id),
  KEY idx_inventory_stock_restaurant (restaurant_id, location_id),
  CONSTRAINT fk_inventory_stock_item FOREIGN KEY (item_id) REFERENCES inventory_items (id) ON DELETE CASCADE,
  CONSTRAINT fk_inventory_stock_location FOREIGN KEY (location_id) REFERENCES locations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recipe_lines (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  menu_item_id       INT UNSIGNED NOT NULL,
  variant_id         INT UNSIGNED NOT NULL DEFAULT 0,   -- 0 = the item (any variant without its own recipe)
  inventory_item_id  INT UNSIGNED NOT NULL,
  quantity           DECIMAL(14,4) NOT NULL,            -- per 1 portion sold, in the inventory item's unit
  PRIMARY KEY (id),
  UNIQUE KEY uq_recipe_lines (menu_item_id, variant_id, inventory_item_id),
  KEY idx_recipe_lines_restaurant (restaurant_id),
  CONSTRAINT fk_recipe_lines_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE CASCADE,
  CONSTRAINT fk_recipe_lines_inventory_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE purchases (
  id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  location_id        INT UNSIGNED NOT NULL,
  supplier_name      VARCHAR(120) NOT NULL,
  invoice_number     VARCHAR(60) NULL,
  purchase_date      DATE NOT NULL,
  total_amount       DECIMAL(12,2) NOT NULL,
  notes              VARCHAR(255) NULL,
  created_by_user_id INT UNSIGNED NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_purchases_restaurant (restaurant_id, purchase_date),
  CONSTRAINT fk_purchases_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_movements (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id   INT UNSIGNED NOT NULL,
  location_id     INT UNSIGNED NOT NULL,
  item_id         INT UNSIGNED NOT NULL,
  type            ENUM('opening','purchase','consumption','reversal','wastage','adjustment') NOT NULL,
  quantity        DECIMAL(14,3) NOT NULL,                -- signed: + in, − out
  unit_cost       DECIMAL(12,4) NULL,
  balance_after   DECIMAL(14,3) NOT NULL,
  order_id        INT UNSIGNED NULL,
  purchase_id     INT UNSIGNED NULL,
  note            VARCHAR(255) NULL,
  user_id         INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stock_movements_item (restaurant_id, item_id, created_at),
  KEY idx_stock_movements_order (order_id),
  KEY idx_stock_movements_purchase (purchase_id),
  CONSTRAINT fk_stock_movements_item FOREIGN KEY (item_id) REFERENCES inventory_items (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Online orders: aggregator connections (signed webhook) and an event log
-- ---------------------------------------------------------------------
CREATE TABLE aggregator_configs (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id        INT UNSIGNED NOT NULL,
  provider             VARCHAR(30) NOT NULL DEFAULT 'generic_webhook',
  channel              ENUM('zomato','swiggy','other') NOT NULL,
  name                 VARCHAR(60) NOT NULL,
  location_id          INT UNSIGNED NOT NULL,
  webhook_key          CHAR(32) NOT NULL,                 -- in the webhook URL
  secret_encrypted     TEXT NOT NULL,                     -- HMAC secret (encrypted like gateway secrets)
  status_callback_url  VARCHAR(500) NULL,
  auto_accept          TINYINT(1) NOT NULL DEFAULT 0,
  is_active            TINYINT(1) NOT NULL DEFAULT 1,
  last_event_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_aggregator_configs_key (webhook_key),
  KEY idx_aggregator_configs_restaurant (restaurant_id),
  CONSTRAINT fk_aggregator_configs_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE aggregator_events (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  config_id          INT UNSIGNED NULL,                  -- null = manual entry
  external_order_id  VARCHAR(64) NULL,
  event_type         VARCHAR(40) NOT NULL,               -- order.created / order.cancelled / status.sent / …
  status             ENUM('processed','failed','ignored') NOT NULL,
  error              VARCHAR(255) NULL,
  order_id           INT UNSIGNED NULL,
  payload            LONGTEXT NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_aggregator_events_restaurant (restaurant_id, created_at),
  KEY idx_aggregator_events_external (restaurant_id, external_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Offline counter billing: idempotent sync log
-- ---------------------------------------------------------------------
CREATE TABLE offline_sales (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id      INT UNSIGNED NOT NULL,
  client_uuid        CHAR(36) NOT NULL,
  device_id          VARCHAR(64) NOT NULL,
  provisional_number VARCHAR(30) NOT NULL,
  status             ENUM('synced','failed') NOT NULL,
  bill_id            INT UNSIGNED NULL,
  order_id           INT UNSIGNED NULL,
  expected_total     DECIMAL(12,2) NOT NULL,
  server_total       DECIMAL(12,2) NULL,
  error              VARCHAR(255) NULL,
  payload            LONGTEXT NOT NULL,
  user_id            INT UNSIGNED NULL,
  created_offline_at DATETIME NOT NULL,
  synced_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_offline_sales_client (restaurant_id, client_uuid),
  KEY idx_offline_sales_restaurant (restaurant_id, synced_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
