-- SPRINT 6 — PATCH 7: PROMO & DISKON (Campaign Promo / Voucher)
-- Voucher dihitung dari subtotal item (sebelum ongkir). Tidak ada pajak di core system.
-- Notes:
-- - Voucher global boleh pakai minimal pembelanjaan (min_order_amount)
-- - Voucher produk tertentu: min_order_amount diabaikan, tapi wajib ada item eligible.

CREATE TABLE IF NOT EXISTS vouchers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(64) NOT NULL,
  title VARCHAR(191) NOT NULL,
  type ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
  value DECIMAL(18,2) NOT NULL DEFAULT 0,
  max_discount DECIMAL(18,2) NULL,
  min_order_amount DECIMAL(18,2) NULL,
  start_at DATETIME NULL,
  end_at DATETIME NULL,
  usage_limit_total INT NULL,
  usage_limit_per_customer INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_voucher_code_tenant (tenant_id, code),
  INDEX idx_vouchers_tenant_active (tenant_id, is_active),
  INDEX idx_vouchers_period (start_at, end_at)
);

CREATE TABLE IF NOT EXISTS voucher_products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  tenant_id BIGINT UNSIGNED NOT NULL,
  voucher_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_voucher_product (voucher_id, product_id),
  INDEX idx_vp_tenant (tenant_id),
  INDEX idx_vp_voucher (voucher_id),
  INDEX idx_vp_product (product_id)
);

CREATE TABLE IF NOT EXISTS voucher_redemptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  tenant_id BIGINT UNSIGNED NOT NULL,
  voucher_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  discount_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  status ENUM('applied','void') NOT NULL DEFAULT 'applied',
  redeemed_at DATETIME NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_vr_tenant (tenant_id),
  INDEX idx_vr_voucher (voucher_id),
  INDEX idx_vr_order (order_id),
  INDEX idx_vr_customer (customer_id),
  INDEX idx_vr_status (status),
  INDEX idx_vr_redeemed (redeemed_at)
);
