-- Plan verification.
--
-- Payments are not built yet, so nothing stops a merchant selecting the
-- premium plan at registration and receiving the full 10 GB allowance for
-- free. Until there is a payment to check, a human checks instead: an
-- unverified premium account runs on a provisional allowance and is promoted
-- to its real one once someone confirms it.
--
-- Deliberately separate from `approvalStatus`, which answers "may this
-- merchant trade at all". This answers "is this merchant entitled to the plan
-- they claim", and the two are decided by different people at different times
-- for different reasons.
--
-- Idempotent; safe to re-run.
BEGIN;

ALTER TABLE platform.organizations
  ADD COLUMN IF NOT EXISTS "planVerifiedAt" TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "planVerifiedBy" TEXT;

COMMENT ON COLUMN platform.organizations."planVerifiedAt" IS
  'When a platform admin confirmed this account is entitled to its plan. Null on a premium account caps storage at the provisional allowance.';

-- Existing premium accounts predate this check. Grandfathering them avoids
-- shrinking the allowance of an account that may already be storing more than
-- the provisional cap, which would block their uploads with no warning and no
-- action they could take. New premium signups start unverified.
UPDATE platform.organizations
   SET "planVerifiedAt" = COALESCE("planVerifiedAt", NOW()),
       "planVerifiedBy" = COALESCE("planVerifiedBy", 'migration-036-grandfathered')
 WHERE lower(trim(plan)) = 'premium'
   AND "planVerifiedAt" IS NULL;

COMMIT;