QUICK_SQL_FIX.sql

-- QUICK COPY-PASTE FIX for Supabase Dashboard
-- This fixes the "column streak_days does not exist" error

-- Step 1: Drop the old broken function
DROP FUNCTION IF EXISTS public.get_user_stats(uuid) CASCADE;

-- Step 2: Create the fixed function
CREATE OR REPLACE FUNCTION public.get_user_stats(user_id_param uuid) 
RETURNS json
LANGUAGE plpgsql 
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  result JSON;
  current_streak_val INTEGER;
  longest_streak_val INTEGER;
BEGIN
  -- Get streak data from streaks table (instead of non-existent profiles.streak_days)
  SELECT 
    COALESCE(MAX(current_count), 0),
    COALESCE(MAX(longest_count), 0)
  INTO current_streak_val, longest_streak_val
  FROM streaks
  WHERE user_id = user_id_param;
  
  -- Build the complete stats JSON
  SELECT json_build_object(
    'total_milestones', COUNT(*),
    'completed_milestones', COUNT(*) FILTER (WHERE is_completed = true),
    'current_streak', current_streak_val,
    'longest_streak', longest_streak_val,
    'total_prayer_points', COUNT(*) FILTER (WHERE prayer_points IS NOT NULL),
    'total_reflections', COUNT(*) FILTER (WHERE type = 'reflection'),
    'milestones_by_category', (
      SELECT json_object_agg(category, count)
      FROM (
        SELECT category, COUNT(*) as count
        FROM milestones
        WHERE user_id = user_id_param
        GROUP BY category
      ) t
    ),
    'milestones_by_month', (
      SELECT json_object_agg(month, count)
      FROM (
        SELECT 
          TO_CHAR(DATE_TRUNC('month', TO_TIMESTAMP(start_timestamp / 1000)), 'YYYY-MM') as month,
          COUNT(*) as count
        FROM milestones
        WHERE user_id = user_id_param
        GROUP BY DATE_TRUNC('month', TO_TIMESTAMP(start_timestamp / 1000))
        ORDER BY month DESC
        LIMIT 12
      ) t
    ),
    'average_completion_time', (
      SELECT AVG(EXTRACT(EPOCH FROM (TO_TIMESTAMP(completed_at / 1000.0) - TO_TIMESTAMP(start_timestamp / 1000.0))) / 86400.0)
      FROM milestones
      WHERE user_id = user_id_param 
        AND is_completed = true 
        AND completed_at IS NOT NULL
    )
  ) INTO result
  FROM milestones
  WHERE user_id = user_id_param;
  
  RETURN COALESCE(result, '{}'::JSON);
END;
$$;

-- Grant permissions to ensure function is visible to PostgREST
GRANT EXECUTE ON FUNCTION public.get_user_stats(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_user_stats(uuid) TO anon;
GRANT EXECUTE ON FUNCTION public.get_user_stats(uuid) TO service_role;