MOPC-App/prisma/migrations/20260212000000_add_live_voting_enhancements/migration.sql

-- Migration: Add live voting enhancements (criteria voting, audience voting, AudienceVoter)
-- Brings LiveVotingSession, LiveVote, and new AudienceVoter model in sync with schema.prisma
-- Uses IF NOT EXISTS / DO $$ guards for idempotent execution

-- =============================================================================
-- 1. LiveVotingSession: Add criteria-based & audience voting columns
-- =============================================================================

ALTER TABLE "LiveVotingSession" ADD COLUMN IF NOT EXISTS "votingMode" TEXT NOT NULL DEFAULT 'simple';
ALTER TABLE "LiveVotingSession" ADD COLUMN IF NOT EXISTS "criteriaJson" JSONB;
ALTER TABLE "LiveVotingSession" ADD COLUMN IF NOT EXISTS "audienceVotingMode" TEXT NOT NULL DEFAULT 'disabled';
ALTER TABLE "LiveVotingSession" ADD COLUMN IF NOT EXISTS "audienceMaxFavorites" INTEGER NOT NULL DEFAULT 3;
ALTER TABLE "LiveVotingSession" ADD COLUMN IF NOT EXISTS "audienceRequireId" BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE "LiveVotingSession" ADD COLUMN IF NOT EXISTS "audienceVotingDuration" INTEGER;

-- =============================================================================
-- 2. LiveVote: Add criteria scores, audience voter link, make userId nullable
-- =============================================================================

ALTER TABLE "LiveVote" ADD COLUMN IF NOT EXISTS "criterionScoresJson" JSONB;
ALTER TABLE "LiveVote" ADD COLUMN IF NOT EXISTS "audienceVoterId" TEXT;

-- Make userId nullable (was NOT NULL in init migration)
ALTER TABLE "LiveVote" ALTER COLUMN "userId" DROP NOT NULL;

-- =============================================================================
-- 3. AudienceVoter: New table for audience participation
-- =============================================================================

CREATE TABLE IF NOT EXISTS "AudienceVoter" (
    "id" TEXT NOT NULL,
    "sessionId" TEXT NOT NULL,
    "token" TEXT NOT NULL,
    "identifier" TEXT,
    "identifierType" TEXT,
    "ipAddress" TEXT,
    "userAgent" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- Unique constraint on token
DO $$ BEGIN
    ALTER TABLE "AudienceVoter" ADD CONSTRAINT "AudienceVoter_token_key" UNIQUE ("token");
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- Indexes
CREATE INDEX IF NOT EXISTS "AudienceVoter_sessionId_idx" ON "AudienceVoter"("sessionId");
CREATE INDEX IF NOT EXISTS "AudienceVoter_token_idx" ON "AudienceVoter"("token");

-- Foreign key: AudienceVoter.sessionId -> LiveVotingSession.id
DO $$ BEGIN
    ALTER TABLE "AudienceVoter" ADD CONSTRAINT "AudienceVoter_sessionId_fkey"
        FOREIGN KEY ("sessionId") REFERENCES "LiveVotingSession"("id") ON DELETE CASCADE ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- =============================================================================
-- 4. LiveVote: Foreign key and indexes for audienceVoterId
-- =============================================================================

CREATE INDEX IF NOT EXISTS "LiveVote_audienceVoterId_idx" ON "LiveVote"("audienceVoterId");

-- Foreign key: LiveVote.audienceVoterId -> AudienceVoter.id
DO $$ BEGIN
    ALTER TABLE "LiveVote" ADD CONSTRAINT "LiveVote_audienceVoterId_fkey"
        FOREIGN KEY ("audienceVoterId") REFERENCES "AudienceVoter"("id") ON DELETE CASCADE ON UPDATE CASCADE;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- Unique constraint: sessionId + projectId + audienceVoterId
DO $$ BEGIN
    ALTER TABLE "LiveVote" ADD CONSTRAINT "LiveVote_sessionId_projectId_audienceVoterId_key"
        UNIQUE ("sessionId", "projectId", "audienceVoterId");
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- =============================================================================
-- SUMMARY:
--
-- LiveVotingSession new columns:
--   - votingMode (TEXT, default 'simple')
--   - criteriaJson (JSONB, nullable)
--   - audienceVotingMode (TEXT, default 'disabled')
--   - audienceMaxFavorites (INTEGER, default 3)
--   - audienceRequireId (BOOLEAN, default false)
--   - audienceVotingDuration (INTEGER, nullable)
--
-- LiveVote changes:
--   - criterionScoresJson (JSONB, nullable) - new column
--   - audienceVoterId (TEXT, nullable) - new column
--   - userId changed from NOT NULL to nullable
--   - New unique: (sessionId, projectId, audienceVoterId)
--   - New index: audienceVoterId
--   - New FK: audienceVoterId -> AudienceVoter(id)
--
-- New table: AudienceVoter
--   - id, sessionId, token (unique), identifier, identifierType,
--     ipAddress, userAgent, createdAt
--   - FK: sessionId -> LiveVotingSession(id) CASCADE
-- =============================================================================