-- =============================================================================
-- MIGRATION: Media storage & backup (docs/19-media-management-plan.md)
-- Adds the media registry (media_assets), per-org storage backends
-- (merchant_storage_configs) with their encrypted credentials in a separate
-- table, backup/migration job tables, and the org-level rolling usage totals.
-- Backfills every existing product image into the registry against a
-- synthetic local_disk config, so nothing is unrouteable before the R2 move.
-- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB
-- Run: idempotent — safe to re-execute
-- =============================================================================

BEGIN;

-- --- 1. Organization: rolling usage + BYOS grant ------------------------------

ALTER TABLE platform.organizations
  ADD COLUMN IF NOT EXISTS "storageUsedBytes"  BIGINT NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "storageAssetCount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "storageUsedAt"     TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "byosEnabled"       BOOLEAN NOT NULL DEFAULT FALSE;

-- --- 2. Storage backends -----------------------------------------------------

CREATE TABLE IF NOT EXISTS platform.merchant_storage_configs (
  "id"                TEXT PRIMARY KEY,
  "organizationId"    TEXT NOT NULL REFERENCES platform.organizations("id") ON DELETE RESTRICT,
  "mode"              TEXT NOT NULL,
  "status"            TEXT NOT NULL DEFAULT 'active',
  "bucket"            TEXT NOT NULL,
  "region"            TEXT NOT NULL DEFAULT 'auto',
  "endpoint"          TEXT,
  "publicBaseUrl"     TEXT,
  "pathPrefix"        TEXT,
  "accessKeyIdMasked" TEXT,
  "healthState"       TEXT NOT NULL DEFAULT 'config_error',
  "healthMessage"     TEXT,
  "lastVerifiedAt"    TIMESTAMP(3),
  "assetCount"        INTEGER NOT NULL DEFAULT 0,
  "assetBytes"        BIGINT NOT NULL DEFAULT 0,
  "isActive"          BOOLEAN NOT NULL DEFAULT TRUE,
  "createdBy"         TEXT NOT NULL DEFAULT 'system',
  "createdOn"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"         TEXT NOT NULL DEFAULT 'system',
  "updatedOn"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS "merchant_storage_configs_org_status_idx"
  ON platform.merchant_storage_configs("organizationId", "status");
CREATE INDEX IF NOT EXISTS "merchant_storage_configs_health_idx"
  ON platform.merchant_storage_configs("healthState");

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'merchant_storage_configs_mode_check') THEN
    ALTER TABLE platform.merchant_storage_configs
      ADD CONSTRAINT "merchant_storage_configs_mode_check"
      CHECK ("mode" IN ('platform_r2', 'byos_r2', 'byos_s3', 'local_disk'));
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'merchant_storage_configs_status_check') THEN
    ALTER TABLE platform.merchant_storage_configs
      ADD CONSTRAINT "merchant_storage_configs_status_check"
      CHECK ("status" IN ('active', 'retained', 'archived'));
  END IF;
END $$;

-- At most one active backend per organization. A partial unique index rather
-- than application-level checking: two concurrent "switch storage" requests
-- would otherwise both read "no other active" and both write one, leaving the
-- org with two write targets and no deterministic answer to "where does the
-- next upload go".
CREATE UNIQUE INDEX IF NOT EXISTS "merchant_storage_configs_one_active_idx"
  ON platform.merchant_storage_configs("organizationId")
  WHERE "status" = 'active';

-- --- 3. Credentials (separate table, never selected casually) ----------------

CREATE TABLE IF NOT EXISTS platform.storage_credentials (
  "id"              TEXT PRIMARY KEY,
  "storageConfigId" TEXT NOT NULL UNIQUE REFERENCES platform.merchant_storage_configs("id") ON DELETE CASCADE,
  "accessKeyIdEnc"  TEXT NOT NULL,
  "secretKeyEnc"    TEXT NOT NULL,
  "dataKeyWrapped"  TEXT NOT NULL,
  "envelopeVersion" TEXT NOT NULL DEFAULT 'v1',
  "rotatedAt"       TIMESTAMP(3),
  "rotatedBy"       TEXT,
  "createdOn"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedOn"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE platform.storage_credentials IS
  'Encrypted merchant bucket credentials. Read only by StorageCredentialVault; no API of any role returns these columns.';

-- --- 4. Media registry -------------------------------------------------------

CREATE TABLE IF NOT EXISTS platform.media_assets (
  "id"              TEXT PRIMARY KEY,
  "organizationId"  TEXT NOT NULL,
  "storeId"         TEXT,
  "ownerType"       TEXT NOT NULL,
  "ownerId"         TEXT,
  "storageConfigId" TEXT NOT NULL REFERENCES platform.merchant_storage_configs("id") ON DELETE RESTRICT,
  "storageKey"      TEXT NOT NULL,
  "publicUrl"       TEXT,
  "fileName"        TEXT NOT NULL,
  "mimeType"        TEXT NOT NULL,
  "sizeBytes"       INTEGER NOT NULL DEFAULT 0,
  "checksum"        TEXT,
  "status"          TEXT NOT NULL DEFAULT 'active',
  "variantOf"       TEXT,
  "purgeAttempts"   INTEGER NOT NULL DEFAULT 0,
  "purgeError"      TEXT,
  "trashedAt"       TIMESTAMP(3),
  "reservedUntil"   TIMESTAMP(3),
  "createdBy"       TEXT NOT NULL DEFAULT 'system',
  "createdOn"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedBy"       TEXT NOT NULL DEFAULT 'system',
  "updatedOn"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "media_assets_config_key_unique" UNIQUE ("storageConfigId", "storageKey")
);

CREATE INDEX IF NOT EXISTS "media_assets_org_status_idx"   ON platform.media_assets("organizationId", "status");
CREATE INDEX IF NOT EXISTS "media_assets_owner_idx"        ON platform.media_assets("ownerType", "ownerId");
CREATE INDEX IF NOT EXISTS "media_assets_config_idx"       ON platform.media_assets("storageConfigId");
CREATE INDEX IF NOT EXISTS "media_assets_status_trash_idx" ON platform.media_assets("status", "trashedAt");

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'media_assets_status_check') THEN
    ALTER TABLE platform.media_assets
      ADD CONSTRAINT "media_assets_status_check"
      CHECK ("status" IN ('reserved', 'active', 'trashed', 'purge_failed'));
  END IF;
END $$;

-- --- 5. Backup + migration job tables ----------------------------------------

CREATE TABLE IF NOT EXISTS platform.media_backup_runs (
  "id"             TEXT PRIMARY KEY,
  "organizationId" TEXT NOT NULL,
  "sourceConfigId" TEXT NOT NULL,
  "kind"           TEXT NOT NULL DEFAULT 'incremental',
  "status"         TEXT NOT NULL DEFAULT 'running',
  "objectCount"    INTEGER NOT NULL DEFAULT 0,
  "byteCount"      BIGINT NOT NULL DEFAULT 0,
  "failedCount"    INTEGER NOT NULL DEFAULT 0,
  "error"          TEXT,
  "startedAt"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "finishedAt"     TIMESTAMP(3)
);
CREATE INDEX IF NOT EXISTS "media_backup_runs_org_idx" ON platform.media_backup_runs("organizationId", "startedAt");

CREATE TABLE IF NOT EXISTS platform.media_backup_entries (
  "id"         TEXT PRIMARY KEY,
  "runId"      TEXT NOT NULL REFERENCES platform.media_backup_runs("id") ON DELETE CASCADE,
  "assetId"    TEXT,
  "storageKey" TEXT NOT NULL,
  "sizeBytes"  INTEGER NOT NULL DEFAULT 0,
  "checksum"   TEXT,
  "versionId"  TEXT,
  "status"     TEXT NOT NULL DEFAULT 'copied',
  "error"      TEXT,
  "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS "media_backup_entries_run_idx"   ON platform.media_backup_entries("runId");
CREATE INDEX IF NOT EXISTS "media_backup_entries_asset_idx" ON platform.media_backup_entries("assetId");

CREATE TABLE IF NOT EXISTS platform.media_migration_jobs (
  "id"             TEXT PRIMARY KEY,
  "organizationId" TEXT NOT NULL,
  "sourceConfigId" TEXT NOT NULL,
  "targetConfigId" TEXT NOT NULL,
  "kind"           TEXT NOT NULL,
  "status"         TEXT NOT NULL DEFAULT 'pending',
  "totalCount"     INTEGER NOT NULL DEFAULT 0,
  "copiedCount"    INTEGER NOT NULL DEFAULT 0,
  "failedCount"    INTEGER NOT NULL DEFAULT 0,
  "lastAssetId"    TEXT,
  "error"          TEXT,
  "startedAt"      TIMESTAMP(3),
  "finishedAt"     TIMESTAMP(3),
  "createdBy"      TEXT NOT NULL DEFAULT 'system',
  "createdOn"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedOn"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS "media_migration_jobs_org_idx" ON platform.media_migration_jobs("organizationId", "status");

-- --- 6. Owner-table links ----------------------------------------------------

ALTER TABLE products.product_images
  ADD COLUMN IF NOT EXISTS "mediaAssetId" TEXT;

ALTER TABLE reviews.review_photos
  ADD COLUMN IF NOT EXISTS "sizeBytes"    INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS "storageKey"   TEXT,
  ADD COLUMN IF NOT EXISTS "mediaAssetId" TEXT;

-- --- 7. Backfill: a local_disk config per org that owns existing images -------
-- Every pre-existing object must be routeable before the R2 migration runs,
-- otherwise a purge or delete has no adapter to dispatch to. These configs are
-- created 'retained', not 'active': the R2 config becomes the write target,
-- and these keep serving reads until 033 moves the bytes.

INSERT INTO platform.merchant_storage_configs
  ("id", "organizationId", "mode", "status", "bucket", "region", "publicBaseUrl", "healthState", "createdBy", "updatedBy")
SELECT
  'stgcfg_local_' || o."id",
  o."id",
  'local_disk',
  'retained',
  'local',
  'auto',
  '/api/uploads',
  'connected',
  'system',
  'system'
FROM platform.organizations o
WHERE EXISTS (
  SELECT 1 FROM products.product_images pi
  JOIN store.stores s ON s."id" = pi."storeId"
  WHERE s."organizationId" = o."id"
)
ON CONFLICT ("id") DO NOTHING;

-- An organization needs exactly one ACTIVE backend or it has nowhere to write,
-- and uploads fail outright. Platform R2 is not configured until the cutover
-- (docs/19-media-storage-setup.md), so the local-disk backend stays the write
-- target until `usePlatformStorage()` promotes R2 and demotes this one to
-- retained. Guarded by the "no active config yet" check so re-running this
-- after the cutover cannot collide with the one-active-per-org unique index.
UPDATE platform.merchant_storage_configs c
SET "status" = 'active'
WHERE c."mode" = 'local_disk'
  AND c."status" = 'retained'
  AND NOT EXISTS (
    SELECT 1 FROM platform.merchant_storage_configs other
    WHERE other."organizationId" = c."organizationId" AND other."status" = 'active'
  );

-- Product images → registry. storageKey is NULL for the oldest rows (they
-- predate the column); those are registered by URL so they still resolve, and
-- the migration job in 033 skips what it cannot locate on disk rather than
-- failing the whole org.
INSERT INTO platform.media_assets
  ("id", "organizationId", "storeId", "ownerType", "ownerId", "storageConfigId",
   "storageKey", "publicUrl", "fileName", "mimeType", "sizeBytes", "status",
   "trashedAt", "createdBy", "updatedBy", "createdOn")
SELECT
  'media_' || pi."id",
  s."organizationId",
  pi."storeId",
  'product',
  pi."productId",
  'stgcfg_local_' || s."organizationId",
  COALESCE(pi."storageKey", regexp_replace(pi."url", '^/api/uploads/', '')),
  pi."url",
  regexp_replace(COALESCE(pi."storageKey", pi."url"), '^.*/', ''),
  CASE
    WHEN pi."url" ILIKE '%.png'  THEN 'image/png'
    WHEN pi."url" ILIKE '%.webp' THEN 'image/webp'
    WHEN pi."url" ILIKE '%.gif'  THEN 'image/gif'
    ELSE 'image/jpeg'
  END,
  pi."sizeBytes",
  CASE WHEN pi."isActive" THEN 'active' ELSE 'trashed' END,
  pi."trashedAt",
  'system',
  'system',
  pi."createdOn"
FROM products.product_images pi
JOIN store.stores s ON s."id" = pi."storeId"
WHERE pi."url" NOT ILIKE 'http%'
ON CONFLICT ("storageConfigId", "storageKey") DO NOTHING;

UPDATE products.product_images pi
SET "mediaAssetId" = 'media_' || pi."id"
WHERE pi."mediaAssetId" IS NULL
  AND EXISTS (SELECT 1 FROM platform.media_assets ma WHERE ma."id" = 'media_' || pi."id");

-- --- 8. Seed the rolling usage totals from what was just registered ----------

UPDATE platform.organizations o
SET "storageUsedBytes"  = COALESCE(agg.bytes, 0),
    "storageAssetCount" = COALESCE(agg.cnt, 0),
    "storageUsedAt"     = CURRENT_TIMESTAMP
FROM (
  SELECT "organizationId", SUM("sizeBytes")::BIGINT AS bytes, COUNT(*)::INTEGER AS cnt
  FROM platform.media_assets
  WHERE "status" IN ('reserved', 'active', 'trashed')
  GROUP BY "organizationId"
) agg
WHERE agg."organizationId" = o."id";

UPDATE platform.merchant_storage_configs c
SET "assetBytes" = COALESCE(agg.bytes, 0),
    "assetCount" = COALESCE(agg.cnt, 0)
FROM (
  SELECT "storageConfigId", SUM("sizeBytes")::BIGINT AS bytes, COUNT(*)::INTEGER AS cnt
  FROM platform.media_assets
  WHERE "status" IN ('reserved', 'active', 'trashed')
  GROUP BY "storageConfigId"
) agg
WHERE agg."storageConfigId" = c."id";

COMMIT;