-- =========================================================
-- 008_pos_orders.sql
-- POS Retail — Orders + Order Items + Order Token
-- Scope: tenant_id + location_id
-- =========================================================

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  tenant_id   BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,

  -- customer bisa NULL untuk "Walk-in"
  customer_id BIGINT UNSIGNED NULL,

  -- status pondasi (Sprint 4 fokus draft)
  status ENUM('draft','quote','final','cancelled') NOT NULL DEFAULT 'draft',

  -- angka snapshot (retail)
  item_count INT NOT NULL DEFAULT 0,

  subtotal_amount  DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  discount_amount  DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  shipping_amount  DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  grand_total      DECIMAL(14,2) NOT NULL DEFAULT 0.00,

  -- catatan header (opsional)
  note TEXT NULL,
  shipping_note TEXT NULL,

  -- lock untuk Sprint 5 payment (Sprint 4 boleh tetap 0)
  is_locked TINYINT(1) NOT NULL DEFAULT 0,
  locked_at DATETIME NULL,
  lock_reason VARCHAR(120) NULL,

  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),

  KEY idx_orders_tenant (tenant_id),
  KEY idx_orders_location (location_id),
  KEY idx_orders_customer (customer_id),
  KEY idx_orders_status (status),
  KEY idx_orders_created (created_at),

  CONSTRAINT fk_orders_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_orders_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_orders_created_by FOREIGN KEY (created_by) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_orders_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  variant_id BIGINT UNSIGNED NULL,

  -- qty bisa decimal (ikuti unit allow_decimal), aman untuk retail
  qty DECIMAL(14,3) NOT NULL DEFAULT 1.000,

  -- snapshot (biar histori aman walau produk berubah)
  sku_snapshot  VARCHAR(80) NULL,
  name_snapshot VARCHAR(180) NOT NULL,

  unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,

  -- tier info retail (optional)
  tier_id BIGINT UNSIGNED NULL,
  tier_label VARCHAR(120) NULL,

  note VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),

  KEY idx_oi_order (order_id),
  KEY idx_oi_product (product_id),

  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_oi_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_oi_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_oi_tier FOREIGN KEY (tier_id) REFERENCES product_price_tiers(id)
    ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS order_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  order_id BIGINT UNSIGNED NOT NULL,
  token VARCHAR(80) NOT NULL,

  created_at DATETIME NULL,

  PRIMARY KEY (id),

  UNIQUE KEY uq_order_tokens_token (token),
  UNIQUE KEY uq_order_tokens_order (order_id),

  CONSTRAINT fk_order_tokens_order FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;