-- Add metadata JSONB column to purchases (used by promo claim + future generic metadata).
ALTER TABLE purchases ADD COLUMN IF NOT EXISTS metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

-- promo_codes table
CREATE TABLE IF NOT EXISTS promo_codes (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code             TEXT NOT NULL,
  kind             TEXT NOT NULL CHECK (kind IN ('percentage','fixed_amount','bogo')),
  value_bps        INTEGER,
  value_amount     INTEGER,
  max_cap_amount   INTEGER,
  bogo_buy         INTEGER,
  bogo_get_free    INTEGER,
  funder           TEXT NOT NULL CHECK (funder IN ('platform','vendor')),
  vendor_id        UUID REFERENCES vendors(id),
  author_user_id   UUID NOT NULL REFERENCES users(id),
  status           TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','paused','archived')),
  valid_from       TIMESTAMPTZ,
  valid_until      TIMESTAMPTZ,
  min_subtotal     INTEGER,
  max_subtotal     INTEGER,
  total_cap        INTEGER,
  per_user_cap     INTEGER NOT NULL DEFAULT 1,
  redemption_count INTEGER NOT NULL DEFAULT 0,
  rules_json       JSONB NOT NULL,
  description      TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS promo_codes_code_uniq ON promo_codes (upper(code));
CREATE INDEX IF NOT EXISTS promo_codes_vendor_idx ON promo_codes (vendor_id) WHERE vendor_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS promo_codes_active_window_idx ON promo_codes (status, valid_from, valid_until)
  WHERE status = 'active';

-- promo_redemptions table (append-only ledger)
CREATE TABLE IF NOT EXISTS promo_redemptions (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  promo_code_id    UUID NOT NULL REFERENCES promo_codes(id),
  purchase_id      UUID NOT NULL REFERENCES purchases(id),
  user_id          UUID NOT NULL REFERENCES users(id),
  vendor_id        UUID REFERENCES vendors(id),
  funder           TEXT NOT NULL CHECK (funder IN ('platform','vendor')),
  discount_amount  INTEGER NOT NULL,
  applied_to_lines JSONB NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (purchase_id, promo_code_id)
);

CREATE INDEX IF NOT EXISTS promo_redemptions_code_idx ON promo_redemptions (promo_code_id);
CREATE INDEX IF NOT EXISTS promo_redemptions_user_code_idx ON promo_redemptions (user_id, promo_code_id);
CREATE INDEX IF NOT EXISTS promo_redemptions_vendor_idx ON promo_redemptions (vendor_id, created_at);
