-- 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,
  ip_address INET,
  user_agent TEXT,
  viewed_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  UNIQUE(topic_id, user_id, ip_address)
);

-- Add views_count column to forum_topics if it doesn't exist
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'forum_topics' AND column_name = 'views_count') THEN
    ALTER TABLE public.forum_topics ADD COLUMN views_count INTEGER DEFAULT 0;
  END IF;
END $$;

-- Function to increment view count
CREATE OR REPLACE FUNCTION public.increment_topic_view(_topic_id UUID, _user_id UUID DEFAULT NULL, _ip_address INET DEFAULT NULL, _user_agent TEXT 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, ip_address, user_agent)
  VALUES (_topic_id, _user_id, _ip_address, _user_agent)
  ON CONFLICT (topic_id, user_id, ip_address) 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;
$$;

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

-- RLS policies for forum_views
ALTER TABLE public.forum_views ENABLE ROW LEVEL SECURITY;

CREATE POLICY IF NOT EXISTS forum_views_select_all ON public.forum_views
  FOR SELECT USING (true);

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

-- 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;
