-- Focus becomes a pinboard. FocusItem (which owned its own title/done/topic)
-- is replaced by FocusPin, a reference to either a single reminder or a whole
-- custom list. Existing focus items are migrated:
--   • linked items (reminderId set)        → a pin on that reminder
--   • open standalone items (no reminderId) → promoted to a real reminder + pin
--   • done standalone items                 → dropped (ephemeral daily scratch)
-- Attachments on migrated items are reparented to the reminder; attachments on
-- dropped items are removed.

-- 1. The new pinboard table.
CREATE TABLE "FocusPin" (
    "id"          TEXT NOT NULL,
    "reminderId"  TEXT,
    "checklistId" TEXT,
    "order"       INTEGER NOT NULL DEFAULT 0,
    "createdAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "FocusPin_pkey" PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "FocusPin_reminderId_key" ON "FocusPin"("reminderId");
CREATE UNIQUE INDEX "FocusPin_checklistId_key" ON "FocusPin"("checklistId");
ALTER TABLE "FocusPin" ADD CONSTRAINT "FocusPin_reminderId_fkey"
    FOREIGN KEY ("reminderId") REFERENCES "Reminder"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "FocusPin" ADD CONSTRAINT "FocusPin_checklistId_fkey"
    FOREIGN KEY ("checklistId") REFERENCES "Checklist"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- 2. Migrate existing focus items into pins (promoting standalone ones).
DO $$
DECLARE
    f       RECORD;
    new_rid TEXT;
BEGIN
    FOR f IN SELECT * FROM "FocusItem" ORDER BY "createdAt" ASC LOOP
        IF f."reminderId" IS NOT NULL THEN
            -- Linked: pin the existing reminder (if it still exists), once.
            IF EXISTS (SELECT 1 FROM "Reminder" r WHERE r.id = f."reminderId")
               AND NOT EXISTS (SELECT 1 FROM "FocusPin" p WHERE p."reminderId" = f."reminderId") THEN
                INSERT INTO "FocusPin" ("id", "reminderId", "order", "createdAt")
                VALUES (gen_random_uuid(), f."reminderId", 0, f."createdAt");
            END IF;
            UPDATE "Attachment" SET "reminderId" = f."reminderId", "focusItemId" = NULL
            WHERE "focusItemId" = f.id;
        ELSIF f.done = false THEN
            -- Open standalone: promote to a real reminder and pin it.
            new_rid := gen_random_uuid();
            INSERT INTO "Reminder" ("id", "title", "completed", "flagged", "syncState", "pendingAction", "createdAt")
            VALUES (new_rid, f.title, false, false, 'PENDING_PUSH', 'create', f."createdAt");
            INSERT INTO "FocusPin" ("id", "reminderId", "order", "createdAt")
            VALUES (gen_random_uuid(), new_rid, 0, f."createdAt");
            UPDATE "Attachment" SET "reminderId" = new_rid, "focusItemId" = NULL
            WHERE "focusItemId" = f.id;
        END IF;
        -- (done standalone items fall through: nothing carried over)
    END LOOP;
END $$;

-- 3. Remove the FocusItem attach point and table. Any attachments still tied
--    to a focus item belong to a dropped (done standalone) item.
DELETE FROM "Attachment" WHERE "focusItemId" IS NOT NULL;
ALTER TABLE "Attachment" DROP COLUMN "focusItemId";
DROP TABLE "FocusItem";