-- Household accounts: give every owned record an owner, add visibility, and
-- make Focus pins and Topic names per-owner rather than global. See PLAN.md §11.
--
-- Backfill strategy: the oldest User is the original owner of all pre-existing
-- data. Written as a subquery rather than a literal uuid so this migration is
-- correct in every environment (prod, a restored dump, a fresh dev DB). On a
-- database with no users every owned table is necessarily empty too, so the
-- UPDATEs are no-ops and the subsequent SET NOT NULL still succeeds.

-- CreateEnum
CREATE TYPE "Visibility" AS ENUM ('PRIVATE', 'HOUSEHOLD');

-- CreateEnum
CREATE TYPE "FocusScope" AS ENUM ('PERSONAL', 'SHARED');

-- AlterTable: access flags on User
ALTER TABLE "User" ADD COLUMN "isAdmin" BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE "User" ADD COLUMN "capabilities" TEXT[] DEFAULT ARRAY[]::TEXT[];

-- AddColumn: ownerId, nullable for the backfill
ALTER TABLE "Capture" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "ReminderList" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "Reminder" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "CalendarEvent" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "Thought" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "Checklist" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "FocusPin" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "Topic" ADD COLUMN "ownerId" TEXT;
ALTER TABLE "Activity" ADD COLUMN "ownerId" TEXT;

-- Backfill: everything that exists today belongs to the founding account.
UPDATE "Capture"       SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "ReminderList"  SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "Reminder"      SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "CalendarEvent" SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "Thought"       SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "Checklist"     SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "FocusPin"      SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "Topic"         SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;
UPDATE "Activity"      SET "ownerId" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1) WHERE "ownerId" IS NULL;

-- The founding account keeps everything it can do today: admin surfaces and
-- every outbound integration. New accounts get neither.
UPDATE "User" SET "isAdmin" = true,
                  "capabilities" = ARRAY['chores', 'points', 'timers', 'sales', 'expenses']::TEXT[]
 WHERE "id" = (SELECT "id" FROM "User" ORDER BY "createdAt" ASC LIMIT 1);

-- Now that every row has an owner, make it required.
ALTER TABLE "Capture"       ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "ReminderList"  ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "Reminder"      ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "CalendarEvent" ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "Thought"       ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "Checklist"     ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "FocusPin"      ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "Topic"         ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "Activity"      ALTER COLUMN "ownerId" SET NOT NULL;
ALTER TABLE "User"          ALTER COLUMN "capabilities" SET NOT NULL;

-- AlterTable: visibility
ALTER TABLE "Reminder"  ADD COLUMN "visibility" "Visibility" NOT NULL DEFAULT 'PRIVATE';
ALTER TABLE "Thought"   ADD COLUMN "visibility" "Visibility" NOT NULL DEFAULT 'PRIVATE';
ALTER TABLE "Checklist" ADD COLUMN "visibility" "Visibility" NOT NULL DEFAULT 'HOUSEHOLD';

-- AlterTable: Focus pin scope
ALTER TABLE "FocusPin" ADD COLUMN "scope" "FocusScope" NOT NULL DEFAULT 'PERSONAL';

-- DropIndex: the old global uniques meant one pin per item across the whole
-- database — whoever pinned an item first locked every other account out.
DROP INDEX "FocusPin_reminderId_key";
DROP INDEX "FocusPin_checklistId_key";
DROP INDEX "FocusPin_checklistItemId_key";

-- CreateIndex: per-owner uniques instead
CREATE UNIQUE INDEX "FocusPin_ownerId_reminderId_key" ON "FocusPin"("ownerId", "reminderId");
CREATE UNIQUE INDEX "FocusPin_ownerId_checklistId_key" ON "FocusPin"("ownerId", "checklistId");
CREATE UNIQUE INDEX "FocusPin_ownerId_checklistItemId_key" ON "FocusPin"("ownerId", "checklistItemId");
CREATE INDEX "FocusPin_ownerId_scope_idx" ON "FocusPin"("ownerId", "scope");

-- Topic names are unique per owner, not globally.
DROP INDEX "Topic_name_key";
CREATE UNIQUE INDEX "Topic_ownerId_name_key" ON "Topic"("ownerId", "name");

-- Activity feed is read per owner.
DROP INDEX "Activity_createdAt_idx";
CREATE INDEX "Activity_ownerId_createdAt_idx" ON "Activity"("ownerId", "createdAt");

-- AddForeignKey
ALTER TABLE "Capture"       ADD CONSTRAINT "Capture_ownerId_fkey"       FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "ReminderList"  ADD CONSTRAINT "ReminderList_ownerId_fkey"  FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "Reminder"      ADD CONSTRAINT "Reminder_ownerId_fkey"      FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "CalendarEvent" ADD CONSTRAINT "CalendarEvent_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "Thought"       ADD CONSTRAINT "Thought_ownerId_fkey"       FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "Checklist"     ADD CONSTRAINT "Checklist_ownerId_fkey"     FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "FocusPin"      ADD CONSTRAINT "FocusPin_ownerId_fkey"      FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "Topic"         ADD CONSTRAINT "Topic_ownerId_fkey"         FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "Activity"      ADD CONSTRAINT "Activity_ownerId_fkey"      FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;