-- ==========================================================
-- SPRINT 6 — PORTAL CLAIM (PENDING) + POS AUTO APPLY
-- ==========================================================

CREATE TABLE IF NOT EXISTS customer_vouchers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  customer_id BIGINT UNSIGNED NOT NULL,

  code VARCHAR(40) NOT NULL,

  -- pending / applied / used / invalid / expired / cancelled
  status VARCHAR(20) NOT NULL DEFAULT 'pending',

  sales_order_id BIGINT UNSIGNED NULL,
  pos_txn_id VARCHAR(80) NULL,

  meta_json TEXT NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  applied_at DATETIME NULL,
  used_at DATETIME NULL,

  INDEX idx_cv_tenant_customer_status (tenant_id, customer_id, status),
  INDEX idx_cv_tenant_code (tenant_id, code),
  INDEX idx_cv_tenant_order (tenant_id, sales_order_id),

  UNIQUE KEY uq_cv_pending (tenant_id, customer_id, code, status)
);