-- ==========================================================
-- SPRINT 6 — VOUCHER CAMPAIGN (kode) + lokasi checklist
-- NOTE: Claim Potongan (negosiasi) terpisah (token claim)
-- ==========================================================

CREATE TABLE IF NOT EXISTS vouchers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(60) NOT NULL,
  name VARCHAR(160) NOT NULL,
  discount_type ENUM('PERCENT','AMOUNT') NOT NULL DEFAULT 'PERCENT',
  discount_value DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  max_discount DECIMAL(18,2) NULL, -- khusus percent
  min_subtotal DECIMAL(18,2) NULL, -- untuk voucher global (atau subtotal eligible)
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  quota_total INT NULL,
  limit_per_customer INT NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_vouchers_tenant_code (tenant_id, code),
  INDEX idx_vouchers_tenant (tenant_id),
  INDEX idx_vouchers_active (tenant_id, is_active),
  CONSTRAINT fk_vouchers_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Jika voucher di-checklist lokasi, simpan di sini.
-- Jika tidak ada row untuk voucher_id -> dianggap GLOBAL (semua lokasi tenant)
CREATE TABLE IF NOT EXISTS voucher_locations (
  voucher_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (voucher_id, location_id),
  INDEX idx_voucher_locations_location (location_id),
  CONSTRAINT fk_voucher_locations_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_voucher_locations_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Scope produk per lokasi.
-- Jika tidak ada row untuk voucher_id + location -> dianggap berlaku semua produk (global) di lokasi tersebut.
CREATE TABLE IF NOT EXISTS voucher_products (
  voucher_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (voucher_id, location_id, product_id),
  INDEX idx_voucher_products_product (product_id),
  CONSTRAINT fk_voucher_products_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_voucher_products_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_voucher_products_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Redemption / pemakaian voucher.
-- Untuk Sprint 6 (tanpa modul order), order_id boleh NULL (pending untuk ditempel saat order dibuat)
CREATE TABLE IF NOT EXISTS voucher_redemptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  voucher_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  code_snapshot VARCHAR(60) NOT NULL,
  discount_snapshot DECIMAL(18,2) NULL,
  status ENUM('PENDING','APPLIED','CANCELLED') NOT NULL DEFAULT 'PENDING',
  source ENUM('PORTAL','POS') NOT NULL DEFAULT 'PORTAL',
  applied_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_redemptions_tenant (tenant_id),
  INDEX idx_redemptions_voucher (voucher_id),
  INDEX idx_redemptions_customer (customer_id, status),
  INDEX idx_redemptions_order (order_id),
  CONSTRAINT fk_redemptions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_redemptions_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_redemptions_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_redemptions_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;