-- Fix foreign key relationships for forum tables
-- Run this in Supabase SQL Editor

-- 1. First, check if the foreign key constraints exist and drop them if they're broken
DO $$ 
BEGIN
  -- Drop broken foreign key constraints
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_topics_author_id_fkey' 
    AND table_name = 'forum_topics'
  ) THEN
    ALTER TABLE public.forum_topics DROP CONSTRAINT forum_topics_author_id_fkey;
  END IF;
  
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_replies_author_id_fkey' 
    AND table_name = 'forum_replies'
  ) THEN
    ALTER TABLE public.forum_replies DROP CONSTRAINT forum_replies_author_id_fkey;
  END IF;
  
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_replies_topic_id_fkey' 
    AND table_name = 'forum_replies'
  ) THEN
    ALTER TABLE public.forum_replies DROP CONSTRAINT forum_replies_topic_id_fkey;
  END IF;
  
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_upvotes_user_id_fkey' 
    AND table_name = 'forum_upvotes'
  ) THEN
    ALTER TABLE public.forum_upvotes DROP CONSTRAINT forum_upvotes_user_id_fkey;
  END IF;
  
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_upvotes_topic_id_fkey' 
    AND table_name = 'forum_upvotes'
  ) THEN
    ALTER TABLE public.forum_upvotes DROP CONSTRAINT forum_upvotes_topic_id_fkey;
  END IF;
  
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_views_user_id_fkey' 
    AND table_name = 'forum_views'
  ) THEN
    ALTER TABLE public.forum_views DROP CONSTRAINT forum_views_user_id_fkey;
  END IF;
  
  IF EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'forum_views_topic_id_fkey' 
    AND table_name = 'forum_views'
  ) THEN
    ALTER TABLE public.forum_views DROP CONSTRAINT forum_views_topic_id_fkey;
  END IF;
END $$;

-- 2. Recreate the foreign key constraints with proper references
ALTER TABLE public.forum_topics 
ADD CONSTRAINT forum_topics_author_id_fkey 
FOREIGN KEY (author_id) REFERENCES public.profiles(id) ON DELETE SET NULL;

ALTER TABLE public.forum_replies 
ADD CONSTRAINT forum_replies_author_id_fkey 
FOREIGN KEY (author_id) REFERENCES public.profiles(id) ON DELETE SET NULL;

ALTER TABLE public.forum_replies 
ADD CONSTRAINT forum_replies_topic_id_fkey 
FOREIGN KEY (topic_id) REFERENCES public.forum_topics(id) ON DELETE CASCADE;

ALTER TABLE public.forum_upvotes 
ADD CONSTRAINT forum_upvotes_user_id_fkey 
FOREIGN KEY (user_id) REFERENCES public.profiles(id) ON DELETE SET NULL;

ALTER TABLE public.forum_upvotes 
ADD CONSTRAINT forum_upvotes_topic_id_fkey 
FOREIGN KEY (topic_id) REFERENCES public.forum_topics(id) ON DELETE CASCADE;

ALTER TABLE public.forum_views 
ADD CONSTRAINT forum_views_user_id_fkey 
FOREIGN KEY (user_id) REFERENCES public.profiles(id) ON DELETE SET NULL;

ALTER TABLE public.forum_views 
ADD CONSTRAINT forum_views_topic_id_fkey 
FOREIGN KEY (topic_id) REFERENCES public.forum_topics(id) ON DELETE CASCADE;

-- 3. Make sure profiles table has the correct primary key
-- This ensures the foreign key references work properly
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'profiles_pkey' 
    AND table_name = 'profiles'
  ) THEN
    ALTER TABLE public.profiles ADD PRIMARY KEY (id);
  END IF;
END $$;

-- 4. Update any orphaned records to prevent foreign key violations
UPDATE public.forum_topics 
SET author_id = NULL 
WHERE author_id NOT IN (SELECT id FROM public.profiles);

UPDATE public.forum_replies 
SET author_id = NULL 
WHERE author_id NOT IN (SELECT id FROM public.profiles);

UPDATE public.forum_upvotes 
SET user_id = NULL 
WHERE user_id NOT IN (SELECT id FROM public.profiles);

UPDATE public.forum_views 
SET user_id = NULL 
WHERE user_id NOT IN (SELECT id FROM public.profiles);
