-- =============================================================================
-- MIGRATION: Merchant self-registration + admin approval workflow
-- Adds approval lifecycle + a per-org store quota to platform.organizations.
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

ALTER TABLE platform.organizations
  ADD COLUMN IF NOT EXISTS "approvalStatus"  TEXT NOT NULL DEFAULT 'approved',
  ADD COLUMN IF NOT EXISTS "approvedAt"      TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "approvedBy"      TEXT,
  ADD COLUMN IF NOT EXISTS "rejectedAt"      TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "rejectedBy"      TEXT,
  ADD COLUMN IF NOT EXISTS "rejectionReason" TEXT,
  ADD COLUMN IF NOT EXISTS "maxStores"       INTEGER,
  ADD COLUMN IF NOT EXISTS "businessType"    TEXT,
  ADD COLUMN IF NOT EXISTS "gstin"           TEXT,
  ADD COLUMN IF NOT EXISTS "city"            TEXT,
  ADD COLUMN IF NOT EXISTS "state"           TEXT;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'organizations_approvalStatus_check'
  ) THEN
    ALTER TABLE platform.organizations
      ADD CONSTRAINT "organizations_approvalStatus_check"
      CHECK ("approvalStatus" IN ('pending', 'approved', 'rejected'));
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS "organizations_approvalStatus_idx"
  ON platform.organizations ("approvalStatus");

-- One-time backfill: every org that already existed before this feature
-- shipped never went through a review — grandfather them in as approved
-- (same pattern as migration 018's emailVerifiedAt backfill). The column
-- DEFAULT above only covers rows written without approvalStatus set
-- explicitly, which registerMerchant() never does — this UPDATE is what
-- actually accounts for pre-existing rows. Safe to re-run: only touches
-- rows still at the default with no approval timestamp either way.
UPDATE platform.organizations
SET "approvalStatus" = 'approved'
WHERE "approvalStatus" = 'approved' AND "approvedAt" IS NULL AND "rejectedAt" IS NULL;

COMMIT;