-- =============================================================================
-- MIGRATION: Per-store storage allocation inside the merchant's pool
-- The platform sells storage to the ORGANIZATION and never to a store; this
-- adds the merchant-owned split of that one pool across their stores —
-- shared (default), fixed bytes, or a percentage share.
-- 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 "storageAllocationMode" TEXT NOT NULL DEFAULT 'shared';

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'organizations_storageAllocationMode_check') THEN
    ALTER TABLE platform.organizations
      ADD CONSTRAINT "organizations_storageAllocationMode_check"
      CHECK ("storageAllocationMode" IN ('shared', 'fixed', 'percent'));
  END IF;
END $$;

ALTER TABLE store.stores
  ADD COLUMN IF NOT EXISTS "storageQuotaBytes"   BIGINT,
  ADD COLUMN IF NOT EXISTS "storageQuotaPercent" INTEGER,
  ADD COLUMN IF NOT EXISTS "storageUsedBytes"    BIGINT  NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "storageAssetCount"   INTEGER NOT NULL DEFAULT 0;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'stores_storageQuotaPercent_check') THEN
    ALTER TABLE store.stores
      ADD CONSTRAINT "stores_storageQuotaPercent_check"
      CHECK ("storageQuotaPercent" IS NULL OR ("storageQuotaPercent" >= 0 AND "storageQuotaPercent" <= 100));
  END IF;
END $$;

-- Seed per-store usage from the registry. Assets with a NULL storeId are
-- organization-level (a merchant logo, a document) and deliberately belong to
-- no store's bar while still counting against the org total.
UPDATE store.stores s
SET "storageUsedBytes"  = COALESCE(agg.bytes, 0),
    "storageAssetCount" = COALESCE(agg.cnt, 0)
FROM (
  SELECT "storeId", SUM("sizeBytes")::BIGINT AS bytes, COUNT(*)::INTEGER AS cnt
  FROM platform.media_assets
  WHERE "status" IN ('reserved', 'active', 'trashed') AND "storeId" IS NOT NULL
  GROUP BY "storeId"
) agg
WHERE agg."storeId" = s."id";

COMMIT;