-- Add roomId to Schedule table
ALTER TABLE "Schedule" ADD COLUMN "roomId" TEXT;

-- Create MeetingCode table
CREATE TABLE "MeetingCode" (
    "id" TEXT NOT NULL,
    "scheduleId" TEXT NOT NULL,
    "userId" TEXT NOT NULL,
    "roomId" TEXT NOT NULL,
    "type" TEXT NOT NULL DEFAULT 'guest',
    "code" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- Create indexes
CREATE INDEX "MeetingCode_scheduleId_idx" ON "MeetingCode"("scheduleId");
CREATE INDEX "MeetingCode_userId_idx" ON "MeetingCode"("userId");
CREATE INDEX "MeetingCode_type_idx" ON "MeetingCode"("type");

-- Create unique constraints
CREATE UNIQUE INDEX "MeetingCode_code_key" ON "MeetingCode"("code");
CREATE UNIQUE INDEX "MeetingCode_scheduleId_userId_type_key" ON "MeetingCode"("scheduleId", "userId", "type");

-- Add foreign key constraints
ALTER TABLE "MeetingCode" ADD CONSTRAINT "MeetingCode_scheduleId_fkey" FOREIGN KEY ("scheduleId") REFERENCES "Schedule"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "MeetingCode" ADD CONSTRAINT "MeetingCode_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Migrate existing host codes and guest codes to MeetingCode table
INSERT INTO "MeetingCode" ("id", "scheduleId", "userId", "roomId", "type", "code", "createdAt", "updatedAt")
SELECT 
    gen_random_uuid()::text,
    id,
    "creatorId",
    COALESCE("hostCode", "guestCode"),
    'host',
    "hostCode",
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM "Schedule"
WHERE "hostCode" IS NOT NULL;

INSERT INTO "MeetingCode" ("id", "scheduleId", "userId", "roomId", "type", "code", "createdAt", "updatedAt")
SELECT 
    gen_random_uuid()::text,
    s.id,
    sp."userId",
    COALESCE(s."guestCode", s."hostCode"),
    'guest',
    s."guestCode",
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM "Schedule" s
JOIN "ScheduleParticipant" sp ON s.id = sp."scheduleId"
WHERE s."guestCode" IS NOT NULL;

-- Update roomId in Schedule table from existing codes
UPDATE "Schedule"
SET "roomId" = COALESCE("hostCode", "guestCode")
WHERE "hostCode" IS NOT NULL OR "guestCode" IS NOT NULL;

-- Remove hostCode and guestCode columns
ALTER TABLE "Schedule" DROP COLUMN "hostCode";
ALTER TABLE "Schedule" DROP COLUMN "guestCode";