-- User: add accountNumber (auto-incrementing unique identifier)
CREATE SEQUENCE IF NOT EXISTS "User_accountNumber_seq";
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "accountNumber" INTEGER NOT NULL DEFAULT nextval('"User_accountNumber_seq"');
ALTER SEQUENCE "User_accountNumber_seq" OWNED BY "User"."accountNumber";
-- Populate existing rows with sequential numbers
DO $$
DECLARE
  r RECORD;
  counter INTEGER := 1;
BEGIN
  FOR r IN SELECT id FROM "User" ORDER BY "createdAt" ASC LOOP
    UPDATE "User" SET "accountNumber" = counter WHERE id = r.id;
    counter := counter + 1;
  END LOOP;
  -- Set sequence to continue after the highest assigned number
  PERFORM setval('"User_accountNumber_seq"', GREATEST(COALESCE((SELECT MAX("accountNumber") FROM "User"), 1), 1));
END $$;
CREATE UNIQUE INDEX IF NOT EXISTS "User_accountNumber_key" ON "User"("accountNumber");

-- User: add quick settings preferences
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "showCompletedTasks" BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "showLines" BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "startDayOffset" INTEGER NOT NULL DEFAULT -1;
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "quoteSourceUrls" TEXT[] DEFAULT ARRAY[]::TEXT[];

-- CachedCalendarEvent: add url, recurringEventId, isRecurring
ALTER TABLE "CachedCalendarEvent" ADD COLUMN IF NOT EXISTS "url" TEXT;
ALTER TABLE "CachedCalendarEvent" ADD COLUMN IF NOT EXISTS "recurringEventId" TEXT;
ALTER TABLE "CachedCalendarEvent" ADD COLUMN IF NOT EXISTS "isRecurring" BOOLEAN NOT NULL DEFAULT false;

-- Task: add projectId
ALTER TABLE "Task" ADD COLUMN IF NOT EXISTS "projectId" TEXT;

-- Project model
CREATE TABLE IF NOT EXISTS "Project" (
    "id" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "icon" TEXT,
    "color" TEXT,
    "description" TEXT,
    "order" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
);

-- Indexes
CREATE INDEX IF NOT EXISTS "Project_userId_idx" ON "Project"("userId");
CREATE INDEX IF NOT EXISTS "Task_userId_projectId_idx" ON "Task"("userId", "projectId");

-- Foreign keys
DO $$ BEGIN
  ALTER TABLE "Task" ADD CONSTRAINT "Task_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE SET NULL ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  ALTER TABLE "Project" ADD CONSTRAINT "Project_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
