011 - product_type, discount channels, redemptions

• Tanggal: 2026-08-10
• DB: mpcdb (PostgreSQL)
• Ringkas: Rename products.category → product_type; bind item_groups.type as product_type filter; add promo/voucher channel; voucher payment_methods + auto_apply; voucher_redemptions.

ALTER (jalankan di DB server)

-- 1) products.category → product_type
ALTER TABLE products RENAME COLUMN category TO product_type;

-- 2) item_groups: ensure type aligns (COURT | FnB | ProShop | Rent); rename column for clarity
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='item_groups' AND column_name='type'
  ) AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='item_groups' AND column_name='product_type'
  ) THEN
    ALTER TABLE item_groups RENAME COLUMN type TO product_type;
  END IF;
END $$;

-- Normalize legacy Merchandise/Equipment/COURT casing
UPDATE item_groups SET product_type = 'ProShop' WHERE product_type IN ('Merchandise', 'Merch');
UPDATE item_groups SET product_type = 'Rent' WHERE product_type IN ('Equipment');
UPDATE item_groups SET product_type = 'COURT' WHERE upper(product_type) = 'COURT';

-- 3) Promo / voucher channel
ALTER TABLE promos ADD COLUMN IF NOT EXISTS channel text NOT NULL DEFAULT 'Both';
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS channel text NOT NULL DEFAULT 'Both';
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS payment_methods text[] DEFAULT NULL;
ALTER TABLE vouchers ADD COLUMN IF NOT EXISTS auto_apply boolean NOT NULL DEFAULT false;

-- 4) Voucher redemptions ledger
CREATE TABLE IF NOT EXISTS voucher_redemptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  voucher_id uuid NOT NULL REFERENCES vouchers(id),
  customer_id uuid REFERENCES customers(id),
  transaction_id uuid REFERENCES transactions(id),
  amount numeric(12,2) NOT NULL DEFAULT 0,
  channel text NOT NULL DEFAULT 'Online',
  code text UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid,
  updated_at timestamptz DEFAULT now(),
  updated_by uuid,
  deleted_at timestamptz,
  deleted_by uuid
);
CREATE INDEX IF NOT EXISTS idx_voucher_redemptions_voucher ON voucher_redemptions(voucher_id);
CREATE INDEX IF NOT EXISTS idx_voucher_redemptions_tx ON voucher_redemptions(transaction_id);

Rollback (opsional)

ALTER TABLE products RENAME COLUMN product_type TO category;
ALTER TABLE item_groups RENAME COLUMN product_type TO type;
ALTER TABLE promos DROP COLUMN IF EXISTS channel;
ALTER TABLE vouchers DROP COLUMN IF EXISTS channel;
ALTER TABLE vouchers DROP COLUMN IF EXISTS payment_methods;
ALTER TABLE vouchers DROP COLUMN IF EXISTS auto_apply;
DROP TABLE IF EXISTS voucher_redemptions;

Catatan

• BE/POS/admin harus baca product_type.
• category_id tetap FK ke item_categories (Coffee, VIP, dll.).