-- Phase 6 schema hardening
-- Adds @db.VarChar() length bounds to all string columns, updatedAt to
-- Image/AdminUser/Code, an index on Image.key, and documents the
-- existing Code.createdById onDelete:RESTRICT behaviour with a schema
-- comment (no DDL change there; it was already RESTRICT by default).

-- === User: add VarChar bounds ===
ALTER TABLE "User" ALTER COLUMN "id" SET DATA TYPE VARCHAR(30);
ALTER TABLE "User" ALTER COLUMN "clerkId" SET DATA TYPE VARCHAR(100);
ALTER TABLE "User" ALTER COLUMN "email" SET DATA TYPE VARCHAR(255);
ALTER TABLE "User" ALTER COLUMN "name" SET DATA TYPE VARCHAR(100);
ALTER TABLE "User" ALTER COLUMN "subdomain" SET DATA TYPE VARCHAR(32);
ALTER TABLE "User" ALTER COLUMN "title" SET DATA TYPE VARCHAR(100);
ALTER TABLE "User" ALTER COLUMN "description" SET DATA TYPE VARCHAR(2000);
ALTER TABLE "User" ALTER COLUMN "bornDate" SET DATA TYPE VARCHAR(50);
ALTER TABLE "User" ALTER COLUMN "passedDate" SET DATA TYPE VARCHAR(50);

-- === Image: bounds + updatedAt + index on key ===
ALTER TABLE "Image" ALTER COLUMN "id" SET DATA TYPE VARCHAR(30);
ALTER TABLE "Image" ALTER COLUMN "url" SET DATA TYPE VARCHAR(255);
ALTER TABLE "Image" ALTER COLUMN "key" SET DATA TYPE VARCHAR(255);
ALTER TABLE "Image" ALTER COLUMN "userId" SET DATA TYPE VARCHAR(30);
ALTER TABLE "Image" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;
CREATE INDEX "Image_key_idx" ON "Image"("key");

-- === AdminUser: bounds + updatedAt ===
ALTER TABLE "AdminUser" ALTER COLUMN "id" SET DATA TYPE VARCHAR(30);
ALTER TABLE "AdminUser" ALTER COLUMN "username" SET DATA TYPE VARCHAR(50);
ALTER TABLE "AdminUser" ALTER COLUMN "passwordHash" SET DATA TYPE VARCHAR(100);
ALTER TABLE "AdminUser" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- === Code: bounds + updatedAt ===
ALTER TABLE "Code" ALTER COLUMN "id" SET DATA TYPE VARCHAR(30);
ALTER TABLE "Code" ALTER COLUMN "code" SET DATA TYPE VARCHAR(12);
ALTER TABLE "Code" ALTER COLUMN "createdById" SET DATA TYPE VARCHAR(30);
ALTER TABLE "Code" ALTER COLUMN "usedByUserId" SET DATA TYPE VARCHAR(100);
ALTER TABLE "Code" ADD COLUMN "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- Backfill any rows whose string columns exceed the new bounds (defensive;
-- production data was capped at the maxLength on the input side already but
-- the persistence layer was unbounded). Truncating is safer than failing.
UPDATE "User" SET "bornDate" = LEFT("bornDate", 50) WHERE "bornDate" IS NOT NULL AND LENGTH("bornDate") > 50;
UPDATE "User" SET "passedDate" = LEFT("passedDate", 50) WHERE "passedDate" IS NOT NULL AND LENGTH("passedDate") > 50;