-- 0112_promo_platform_reconcile.sql
-- Reconcile host promo storage onto platform promo/cart tables.

CREATE TABLE IF NOT EXISTS cart (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  currency TEXT NOT NULL,
  user_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS cart_user_id_key
  ON cart (user_id)
  WHERE user_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS cart_line (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cart_id UUID NOT NULL REFERENCES cart(id) ON DELETE CASCADE,
  variant_id UUID NOT NULL,
  qty INTEGER NOT NULL,
  amount BIGINT NOT NULL,
  currency TEXT NOT NULL,
  price_mode TEXT NOT NULL,
  vendor_id TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS cart_line_cart_id_variant_id_key
  ON cart_line (cart_id, variant_id);

CREATE TABLE IF NOT EXISTS promo (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT NOT NULL,
  kind TEXT NOT NULL,
  value_bps INTEGER,
  value_amount INTEGER,
  currency TEXT,
  max_discount_amount BIGINT,
  bogo_buy_qty INTEGER,
  bogo_get_qty INTEGER,
  scope JSONB NOT NULL DEFAULT '{"kind":"all"}'::jsonb,
  eligibility JSONB NOT NULL DEFAULT '{}'::jsonb,
  funder TEXT NOT NULL,
  max_uses INTEGER,
  per_user_cap INTEGER NOT NULL DEFAULT 1,
  uses INTEGER NOT NULL DEFAULT 0,
  starts_at TIMESTAMPTZ,
  ends_at TIMESTAMPTZ,
  min_order_amount BIGINT,
  max_order_amount BIGINT,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  vendor_id TEXT,
  author_user_id UUID REFERENCES users(id),
  status TEXT NOT NULL DEFAULT 'active',
  valid_from TIMESTAMPTZ,
  valid_until TIMESTAMPTZ,
  min_subtotal INTEGER,
  max_subtotal INTEGER,
  total_cap INTEGER,
  redemption_count INTEGER NOT NULL DEFAULT 0,
  rules_json JSONB NOT NULL DEFAULT '{}'::jsonb,
  description TEXT,
  max_cap_amount INTEGER,
  bogo_buy INTEGER,
  bogo_get_free INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE promo ADD COLUMN IF NOT EXISTS currency TEXT;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS max_discount_amount BIGINT;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS bogo_buy_qty INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS bogo_get_qty INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS scope JSONB NOT NULL DEFAULT '{"kind":"all"}'::jsonb;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS eligibility JSONB NOT NULL DEFAULT '{}'::jsonb;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS max_uses INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS per_user_cap INTEGER NOT NULL DEFAULT 1;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS uses INTEGER NOT NULL DEFAULT 0;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS starts_at TIMESTAMPTZ;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS ends_at TIMESTAMPTZ;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS min_order_amount BIGINT;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS max_order_amount BIGINT;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS active BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS author_user_id UUID REFERENCES users(id);
ALTER TABLE promo ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'active';
ALTER TABLE promo ADD COLUMN IF NOT EXISTS valid_from TIMESTAMPTZ;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS valid_until TIMESTAMPTZ;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS min_subtotal INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS max_subtotal INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS total_cap INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS redemption_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS rules_json JSONB NOT NULL DEFAULT '{}'::jsonb;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS max_cap_amount INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS bogo_buy INTEGER;
ALTER TABLE promo ADD COLUMN IF NOT EXISTS bogo_get_free INTEGER;

CREATE UNIQUE INDEX IF NOT EXISTS promo_code_uniq ON promo (UPPER(code));
CREATE INDEX IF NOT EXISTS promo_vendor_idx ON promo (vendor_id) WHERE vendor_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS promo_active_window_idx
  ON promo (active, starts_at, ends_at)
  WHERE active = TRUE;

CREATE TABLE IF NOT EXISTS promo_user_usage (
  promo_id UUID NOT NULL REFERENCES promo(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL,
  uses INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (promo_id, user_id)
);

CREATE TABLE IF NOT EXISTS promo_allowlist (
  promo_id UUID NOT NULL REFERENCES promo(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (promo_id, user_id)
);

CREATE INDEX IF NOT EXISTS promo_allowlist_user_idx ON promo_allowlist (user_id);

DO $$
BEGIN
  IF to_regclass('public.promo_codes') IS NOT NULL THEN
    INSERT INTO promo (
      id,
      code,
      kind,
      value_bps,
      value_amount,
      currency,
      max_discount_amount,
      bogo_buy_qty,
      bogo_get_qty,
      scope,
      eligibility,
      funder,
      max_uses,
      per_user_cap,
      uses,
      starts_at,
      ends_at,
      min_order_amount,
      max_order_amount,
      active,
      vendor_id,
      author_user_id,
      status,
      valid_from,
      valid_until,
      min_subtotal,
      max_subtotal,
      total_cap,
      redemption_count,
      rules_json,
      description,
      max_cap_amount,
      bogo_buy,
      bogo_get_free,
      created_at,
      updated_at
    )
    SELECT
      pc.id,
      pc.code,
      CASE WHEN pc.kind = 'fixed_amount' THEN 'fixed' ELSE pc.kind END,
      pc.value_bps,
      pc.value_amount,
      CASE
        WHEN pc.value_amount IS NOT NULL OR pc.min_subtotal IS NOT NULL OR pc.max_subtotal IS NOT NULL
          THEN 'ILS'
        ELSE NULL
      END,
      pc.max_cap_amount,
      pc.bogo_buy,
      pc.bogo_get_free,
      COALESCE(pc.rules_json -> 'scope', '{"kind":"all"}'::jsonb),
      COALESCE(pc.rules_json -> 'eligibility', '{}'::jsonb),
      pc.funder,
      pc.total_cap,
      pc.per_user_cap,
      pc.redemption_count,
      pc.valid_from,
      pc.valid_until,
      pc.min_subtotal,
      pc.max_subtotal,
      pc.status = 'active',
      pc.vendor_id::text,
      pc.author_user_id,
      pc.status,
      pc.valid_from,
      pc.valid_until,
      pc.min_subtotal,
      pc.max_subtotal,
      pc.total_cap,
      pc.redemption_count,
      pc.rules_json,
      pc.description,
      pc.max_cap_amount,
      pc.bogo_buy,
      pc.bogo_get_free,
      pc.created_at,
      pc.updated_at
    FROM promo_codes pc
    ON CONFLICT (id) DO UPDATE SET
      code = EXCLUDED.code,
      kind = EXCLUDED.kind,
      value_bps = EXCLUDED.value_bps,
      value_amount = EXCLUDED.value_amount,
      currency = EXCLUDED.currency,
      max_discount_amount = EXCLUDED.max_discount_amount,
      bogo_buy_qty = EXCLUDED.bogo_buy_qty,
      bogo_get_qty = EXCLUDED.bogo_get_qty,
      scope = EXCLUDED.scope,
      eligibility = EXCLUDED.eligibility,
      funder = EXCLUDED.funder,
      max_uses = EXCLUDED.max_uses,
      per_user_cap = EXCLUDED.per_user_cap,
      uses = EXCLUDED.uses,
      starts_at = EXCLUDED.starts_at,
      ends_at = EXCLUDED.ends_at,
      min_order_amount = EXCLUDED.min_order_amount,
      max_order_amount = EXCLUDED.max_order_amount,
      active = EXCLUDED.active,
      vendor_id = EXCLUDED.vendor_id,
      author_user_id = EXCLUDED.author_user_id,
      status = EXCLUDED.status,
      valid_from = EXCLUDED.valid_from,
      valid_until = EXCLUDED.valid_until,
      min_subtotal = EXCLUDED.min_subtotal,
      max_subtotal = EXCLUDED.max_subtotal,
      total_cap = EXCLUDED.total_cap,
      redemption_count = EXCLUDED.redemption_count,
      rules_json = EXCLUDED.rules_json,
      description = EXCLUDED.description,
      max_cap_amount = EXCLUDED.max_cap_amount,
      bogo_buy = EXCLUDED.bogo_buy,
      bogo_get_free = EXCLUDED.bogo_get_free,
      updated_at = EXCLUDED.updated_at;
  END IF;
END $$;

INSERT INTO promo_user_usage (promo_id, user_id, uses)
SELECT
  pr.promo_code_id,
  pr.user_id::text,
  COUNT(*)::int
FROM promo_redemptions pr
GROUP BY pr.promo_code_id, pr.user_id
ON CONFLICT (promo_id, user_id) DO UPDATE SET
  uses = EXCLUDED.uses,
  updated_at = NOW();

DO $$
BEGIN
  IF to_regclass('public.promo_codes') IS NOT NULL THEN
    INSERT INTO promo_allowlist (promo_id, user_id)
    SELECT
      pc.id,
      value::text::uuid
    FROM promo_codes pc
    CROSS JOIN LATERAL jsonb_array_elements_text(
      COALESCE(pc.rules_json -> 'eligibility' -> 'userAllowlist', '[]'::jsonb)
    ) AS value
    ON CONFLICT (promo_id, user_id) DO NOTHING;
  END IF;
END $$;

ALTER TABLE promo_redemptions
  DROP CONSTRAINT IF EXISTS promo_redemptions_promo_code_id_promo_codes_id_fk;

ALTER TABLE promo_redemptions
  DROP CONSTRAINT IF EXISTS promo_redemptions_promo_code_id_fkey;

ALTER TABLE referrals
  DROP CONSTRAINT IF EXISTS referrals_referee_promo_code_id_promo_codes_id_fk;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'promo_redemptions_promo_code_id_promo_id_fk'
  ) THEN
    ALTER TABLE promo_redemptions
      ADD CONSTRAINT promo_redemptions_promo_code_id_promo_id_fk
      FOREIGN KEY (promo_code_id) REFERENCES promo(id);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'referrals_referee_promo_code_id_promo_id_fk'
  ) THEN
    ALTER TABLE referrals
      ADD CONSTRAINT referrals_referee_promo_code_id_promo_id_fk
      FOREIGN KEY (referee_promo_code_id) REFERENCES promo(id);
  END IF;
END $$;

