thrilltrack-explorer/supabase/migrations/20251103003450_d88ec137-2cfb-446d-a28e-08a1a045b12c.sql

-- Phase 1: Add conflict resolution tracking
CREATE TABLE IF NOT EXISTS public.conflict_resolutions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  submission_id UUID NOT NULL REFERENCES public.content_submissions(id) ON DELETE CASCADE,
  detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  resolved_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  resolution_strategy TEXT NOT NULL CHECK (resolution_strategy IN ('keep-mine', 'keep-theirs', 'reload', 'merge')),
  conflict_details JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add index for performance
CREATE INDEX IF NOT EXISTS idx_conflict_resolutions_submission 
ON public.conflict_resolutions(submission_id);

CREATE INDEX IF NOT EXISTS idx_conflict_resolutions_detected_at 
ON public.conflict_resolutions(detected_at DESC);

-- Enable RLS
ALTER TABLE public.conflict_resolutions ENABLE ROW LEVEL SECURITY;

-- Policy: Moderators can view all conflict resolutions
CREATE POLICY "Moderators can view conflict resolutions"
ON public.conflict_resolutions
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = auth.uid()
    AND role IN ('moderator', 'admin', 'superuser')
  )
);

-- Policy: Moderators can insert conflict resolutions
CREATE POLICY "Moderators can insert conflict resolutions"
ON public.conflict_resolutions
FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = auth.uid()
    AND role IN ('moderator', 'admin', 'superuser')
  )
  AND resolved_by = auth.uid()
);

-- Add index for notification deduplication performance (Phase 3)
CREATE INDEX IF NOT EXISTS idx_notification_logs_dedup 
ON public.notification_logs(user_id, idempotency_key, created_at);

-- Add comment
COMMENT ON TABLE public.conflict_resolutions IS 'Tracks resolution of concurrent edit conflicts in moderation system';