-- ============================================================
--  Asanté Restaurant — Point of Sale Database Schema
--  Run this ONCE against your existing database
-- ============================================================

SET SQL_MODE   = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone  = "+00:00";
/*!40101 SET NAMES utf8mb4 */;

-- ── pos_workers ─────────────────────────────────────────────────────
-- Staff accounts created by admin; separate from customer accounts
CREATE TABLE IF NOT EXISTS `pos_workers` (
  `id`           INT(11)      NOT NULL AUTO_INCREMENT,
  `username`     VARCHAR(50)  NOT NULL,
  `full_name`    VARCHAR(100) NOT NULL,
  `pin`          VARCHAR(6)   NOT NULL COMMENT '4-digit PIN for quick terminal login',
  `password`     VARCHAR(255) NOT NULL,
  `role`         ENUM('cashier','supervisor','manager') DEFAULT 'cashier',
  `status`       ENUM('active','inactive') DEFAULT 'active',
  `created_by`   INT(11)      DEFAULT NULL COMMENT 'admin.id who created this worker',
  `last_login`   DATETIME     DEFAULT NULL,
  `avatar_color` VARCHAR(7)   DEFAULT '#C9A84C',
  `created_at`   DATETIME     DEFAULT NOW(),
  `updated_at`   DATETIME     DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`),
  KEY `fk_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── pos_sessions ────────────────────────────────────────────────────
-- One row per shift (clock-in → clock-out)
CREATE TABLE IF NOT EXISTS `pos_sessions` (
  `id`            INT(11)       NOT NULL AUTO_INCREMENT,
  `worker_id`     INT(11)       NOT NULL,
  `clock_in`      DATETIME      NOT NULL DEFAULT NOW(),
  `clock_out`     DATETIME      DEFAULT NULL,
  `opening_float` DECIMAL(12,2) DEFAULT 0.00 COMMENT 'Cash float at start of shift',
  `closing_float` DECIMAL(12,2) DEFAULT NULL COMMENT 'Declared cash at end of shift',
  `total_orders`  INT(11)       DEFAULT 0,
  `total_revenue` DECIMAL(12,2) DEFAULT 0.00,
  `cash_revenue`  DECIMAL(12,2) DEFAULT 0.00,
  `momo_revenue`  DECIMAL(12,2) DEFAULT 0.00,
  `card_revenue`  DECIMAL(12,2) DEFAULT 0.00,
  `void_count`    INT(11)       DEFAULT 0,
  `notes`         TEXT          DEFAULT NULL,
  `status`        ENUM('open','closed') DEFAULT 'open',
  PRIMARY KEY (`id`),
  KEY `fk_ps_worker` (`worker_id`),
  FOREIGN KEY (`worker_id`) REFERENCES `pos_workers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── pos_orders ──────────────────────────────────────────────────────
-- Standalone POS orders (separate from online orders)
CREATE TABLE IF NOT EXISTS `pos_orders` (
  `id`             INT(11)       NOT NULL AUTO_INCREMENT,
  `receipt_no`     VARCHAR(20)   NOT NULL COMMENT 'e.g. POS-20240115-0042',
  `session_id`     INT(11)       DEFAULT NULL,
  `worker_id`      INT(11)       NOT NULL,
  `table_no`       VARCHAR(10)   DEFAULT NULL,
  `customer_name`  VARCHAR(100)  DEFAULT NULL,
  `covers`         TINYINT(3)    DEFAULT 1 COMMENT 'Number of guests at table',
  `subtotal`       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount`       DECIMAL(12,2) DEFAULT 0.00,
  `discount_type`  ENUM('amount','percent') DEFAULT 'amount',
  `tax`            DECIMAL(12,2) DEFAULT 0.00,
  `total`          DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payment_method` ENUM('cash','momo','card','split','complimentary') DEFAULT 'cash',
  `amount_tendered` DECIMAL(12,2) DEFAULT 0.00,
  `change_given`   DECIMAL(12,2) DEFAULT 0.00,
  `momo_ref`       VARCHAR(100)  DEFAULT NULL COMMENT 'MoMo transaction reference',
  `status`         ENUM('open','completed','voided','refunded') DEFAULT 'completed',
  `void_reason`    VARCHAR(255)  DEFAULT NULL,
  `order_notes`    TEXT          DEFAULT NULL,
  `created_at`     DATETIME      NOT NULL DEFAULT NOW(),
  `updated_at`     DATETIME      DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_receipt` (`receipt_no`),
  KEY `fk_po_session` (`session_id`),
  KEY `fk_po_worker`  (`worker_id`),
  KEY `idx_created`   (`created_at`),
  FOREIGN KEY (`session_id`) REFERENCES `pos_sessions`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`worker_id`)  REFERENCES `pos_workers`(`id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── pos_order_items ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `pos_order_items` (
  `id`         INT(11)       NOT NULL AUTO_INCREMENT,
  `order_id`   INT(11)       NOT NULL,
  `product_id` INT(11)       DEFAULT NULL,
  `name`       VARCHAR(200)  NOT NULL,
  `sku`        VARCHAR(50)   DEFAULT NULL,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `qty`        INT(11)       NOT NULL DEFAULT 1,
  `modifiers`  TEXT          DEFAULT NULL COMMENT 'JSON: e.g. {"note":"no onions","extras":["extra sauce"]}',
  `subtotal`   DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_poi_order`   (`order_id`),
  KEY `fk_poi_product` (`product_id`),
  FOREIGN KEY (`order_id`) REFERENCES `pos_orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── pos_void_log ────────────────────────────────────────────────────
-- Audit trail for all voided transactions
CREATE TABLE IF NOT EXISTS `pos_void_log` (
  `id`         INT(11)      NOT NULL AUTO_INCREMENT,
  `order_id`   INT(11)      NOT NULL,
  `worker_id`  INT(11)      NOT NULL,
  `receipt_no` VARCHAR(20)  NOT NULL,
  `amount`     DECIMAL(12,2) NOT NULL,
  `reason`     VARCHAR(255) DEFAULT NULL,
  `voided_at`  DATETIME     DEFAULT NOW(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;