-- Complete migration script for all Prisma tables
-- Run this directly on your PostgreSQL server

-- Create User table if it doesn't exist
CREATE TABLE IF NOT EXISTS "User" (
  "id" TEXT PRIMARY KEY,
  "email" TEXT UNIQUE NOT NULL,
  "password" TEXT NOT NULL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL
);

-- Create Calendar table if it doesn't exist
CREATE TABLE IF NOT EXISTS "Calendar" (
  "id" TEXT PRIMARY KEY,
  "name" TEXT NOT NULL,
  "color" TEXT NOT NULL DEFAULT '#0082c9',
  "description" TEXT,
  "userId" TEXT NOT NULL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE
);

-- Create index on Calendar
CREATE INDEX IF NOT EXISTS "Calendar_userId_idx" ON "Calendar"("userId");

-- Create Event table if it doesn't exist
CREATE TABLE IF NOT EXISTS "Event" (
  "id" TEXT PRIMARY KEY,
  "title" TEXT NOT NULL,
  "description" TEXT,
  "start" TIMESTAMP(3) NOT NULL,
  "end" TIMESTAMP(3) NOT NULL,
  "location" TEXT,
  "isAllDay" BOOLEAN NOT NULL DEFAULT false,
  "calendarId" TEXT NOT NULL,
  "userId" TEXT NOT NULL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  FOREIGN KEY ("calendarId") REFERENCES "Calendar"("id") ON DELETE CASCADE,
  FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE
);

-- Create indexes on Event
CREATE INDEX IF NOT EXISTS "Event_calendarId_idx" ON "Event"("calendarId");
CREATE INDEX IF NOT EXISTS "Event_userId_idx" ON "Event"("userId");

-- Create or update MailCredentials table with OAuth support
DO $$ 
BEGIN
  -- Check if MailCredentials table exists
  IF EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'MailCredentials') THEN
    -- Alter existing table to add OAuth fields
    BEGIN
      -- Make password column nullable
      ALTER TABLE "MailCredentials" ALTER COLUMN "password" DROP NOT NULL;
    EXCEPTION WHEN undefined_column THEN
      -- Password column doesn't exist or is already nullable
    END;

    -- Add OAuth columns if they don't exist
    BEGIN
      ALTER TABLE "MailCredentials" 
        ADD COLUMN IF NOT EXISTS "useOAuth" BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS "refreshToken" TEXT,
        ADD COLUMN IF NOT EXISTS "accessToken" TEXT,
        ADD COLUMN IF NOT EXISTS "tokenExpiry" TIMESTAMP(3);
    EXCEPTION WHEN duplicate_column THEN
      -- Columns already exist
    END;
  ELSE
    -- Create MailCredentials table with OAuth support
    CREATE TABLE "MailCredentials" (
      "id" TEXT PRIMARY KEY,
      "userId" TEXT NOT NULL,
      "email" TEXT NOT NULL,
      "password" TEXT,
      "host" TEXT NOT NULL,
      "port" INTEGER NOT NULL,
      "secure" BOOLEAN NOT NULL DEFAULT true,
      "useOAuth" BOOLEAN NOT NULL DEFAULT false,
      "refreshToken" TEXT,
      "accessToken" TEXT,
      "tokenExpiry" TIMESTAMP(3),
      "smtp_host" TEXT,
      "smtp_port" INTEGER,
      "smtp_secure" BOOLEAN DEFAULT false,
      "display_name" TEXT,
      "color" TEXT DEFAULT '#0082c9',
      "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updatedAt" TIMESTAMP(3) NOT NULL,
      FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE,
      UNIQUE("userId", "email")
    );

    -- Create index on MailCredentials
    CREATE INDEX IF NOT EXISTS "MailCredentials_userId_idx" ON "MailCredentials"("userId");
  END IF;
END $$;

-- Create WebDAVCredentials table if it doesn't exist
CREATE TABLE IF NOT EXISTS "WebDAVCredentials" (
  "id" TEXT PRIMARY KEY,
  "userId" TEXT UNIQUE NOT NULL,
  "username" TEXT NOT NULL,
  "password" TEXT NOT NULL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE
);

-- Create index on WebDAVCredentials
CREATE INDEX IF NOT EXISTS "WebDAVCredentials_userId_idx" ON "WebDAVCredentials"("userId");