-- Cashi terminology fix:
-- - Cashi Coins: earned from savings (discountAmount); used to claim coupons
-- - Cashi Points: loyalty points (₹1 = 1 point); still tracked via grants for expiry, but also cached on users for fast fetch

-- 1) Users: add separate cached balances
ALTER TABLE "users"
  ADD COLUMN IF NOT EXISTS "cashi_coins" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "users"
  ADD COLUMN IF NOT EXISTS "cashi_points" INTEGER NOT NULL DEFAULT 0;

-- Backfill coins from the old platform points wallet only if the legacy column still exists.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_name = 'users'
      AND column_name = 'cashiPoints'
  ) THEN
    EXECUTE '
      UPDATE "users"
      SET "cashi_coins" = COALESCE("cashiPoints", 0)
      WHERE "cashi_coins" = 0
    ';
  END IF;
END $$;

-- Backfill loyalty points cache from non-expired grants (best-effort snapshot)
UPDATE "users" u
SET "cashi_points" = COALESCE((
  SELECT SUM(g."pointsRemaining")
  FROM "loyalty_point_grants" g
  WHERE g."customerId" = u."id"
    AND g."expiresAt" > NOW()
    AND g."pointsRemaining" > 0
), 0);

-- 2) Sales: add cashi_coins_earned (saved amount)
ALTER TABLE "sales"
  ADD COLUMN IF NOT EXISTS "cashi_coins_earned" INTEGER NOT NULL DEFAULT 0;

-- Backfill from savings (discountAmount = coupon discount + points redeemed)
UPDATE "sales"
SET "cashi_coins_earned" = COALESCE("discountAmount", 0)
WHERE "cashi_coins_earned" = 0;

-- 3) Coupons: rename cost semantics to coins (keep old column for compatibility)
ALTER TABLE "coupons"
  ADD COLUMN IF NOT EXISTS "cashi_coins_cost" INTEGER NOT NULL DEFAULT 0;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_name = 'coupons'
      AND column_name = 'cashiPointsCost'
  ) THEN
    EXECUTE '
      UPDATE "coupons"
      SET "cashi_coins_cost" = COALESCE("cashiPointsCost", 0)
      WHERE "cashi_coins_cost" = 0
    ';
  END IF;
END $$;

-- 4) Customer coupon assignments: track coins spent on claim (0 for distributed coupons)
ALTER TABLE "customer_coupons"
  ADD COLUMN IF NOT EXISTS "cashi_coins_spent" INTEGER NOT NULL DEFAULT 0;

-- NOTE:
-- - We intentionally do NOT drop legacy columns (`users`.`cashiPoints`, `sales`.`cashiPointsEarned`, `coupons`.`cashiPointsCost`).
--   They can be removed in a later cleanup migration after the apps fully switch.