-- Run this in Supabase SQL Editor to add view tracking

-- 1. Create forum_views table
CREATE TABLE IF NOT EXISTS public.forum_views (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  topic_id UUID NOT NULL REFERENCES public.forum_topics(id) ON DELETE CASCADE,
  user_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
  viewed_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(topic_id, user_id)
);

-- 2. Add views_count column to forum_topics
ALTER TABLE public.forum_topics ADD COLUMN IF NOT EXISTS views_count INTEGER DEFAULT 0;

-- 3. Create function to increment view count
CREATE OR REPLACE FUNCTION public.increment_topic_view(_topic_id UUID, _user_id UUID DEFAULT NULL)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Insert view record (or ignore if duplicate)
  INSERT INTO public.forum_views (topic_id, user_id)
  VALUES (_topic_id, _user_id)
  ON CONFLICT (topic_id, user_id) DO NOTHING;
  
  -- Update topic view count
  UPDATE public.forum_topics 
  SET views_count = (
    SELECT COUNT(*) FROM public.forum_views 
    WHERE topic_id = _topic_id
  )
  WHERE id = _topic_id;
END;
$$;

-- 4. Grant permissions
GRANT SELECT, INSERT ON public.forum_views TO authenticated, anon;
GRANT EXECUTE ON FUNCTION public.increment_topic_view(UUID, UUID) TO authenticated, anon;

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

-- 6. Create RLS policies
DROP POLICY IF EXISTS forum_views_select_all ON public.forum_views;
CREATE POLICY forum_views_select_all ON public.forum_views
  FOR SELECT USING (true);

DROP POLICY IF EXISTS forum_views_insert_authenticated ON public.forum_views;
CREATE POLICY forum_views_insert_authenticated ON public.forum_views
  FOR INSERT WITH CHECK (auth.role() = 'authenticated' OR auth.role() = 'anon');

-- 7. Update existing topics to have correct view counts
UPDATE public.forum_topics 
SET views_count = (
  SELECT COUNT(*) FROM public.forum_views 
  WHERE topic_id = forum_topics.id
)
WHERE views_count IS NULL OR views_count = 0;
