thrilltrack-explorer/supabase/migrations/20251112015316_45d7d255-3f13-496d-954c-f646eb7778d5.sql

-- Fix get_recent_additions: Remove created_by joins for tables without created_by column
-- Only entity_timeline_events has created_by column, not parks/rides/companies/ride_models/locations

CREATE OR REPLACE FUNCTION public.get_recent_additions(limit_count integer DEFAULT 50)
 RETURNS TABLE(entity_id uuid, entity_type text, entity_name text, entity_slug text, park_slug text, image_url text, created_at timestamp with time zone, created_by_id uuid, created_by_username text, created_by_avatar text)
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
BEGIN
  RETURN QUERY
  SELECT * FROM (
    -- Parks - FIXED: Removed created_by join (parks table doesn't have created_by column)
    SELECT 
      p.id as entity_id,
      'park'::text as entity_type,
      p.name as entity_name,
      p.slug as entity_slug,
      NULL::text as park_slug,
      p.card_image_url as image_url,
      p.created_at,
      NULL::uuid as created_by_id,
      NULL::text as created_by_username,
      NULL::text as created_by_avatar
    FROM parks p
    
    UNION ALL
    
    -- Rides - FIXED: Removed created_by join (rides table doesn't have created_by column)
    SELECT 
      r.id as entity_id,
      'ride'::text as entity_type,
      r.name as entity_name,
      r.slug as entity_slug,
      pk.slug as park_slug,
      r.card_image_url as image_url,
      r.created_at,
      NULL::uuid as created_by_id,
      NULL::text as created_by_username,
      NULL::text as created_by_avatar
    FROM rides r
    LEFT JOIN parks pk ON pk.id = r.park_id
    
    UNION ALL
    
    -- Companies - FIXED: Removed created_by join (companies table doesn't have created_by column)
    SELECT 
      c.id as entity_id,
      'company'::text as entity_type,
      c.name as entity_name,
      c.slug as entity_slug,
      NULL::text as park_slug,
      c.card_image_url as image_url,
      c.created_at,
      NULL::uuid as created_by_id,
      NULL::text as created_by_username,
      NULL::text as created_by_avatar
    FROM companies c
    
    UNION ALL
    
    -- Ride Models - FIXED: Removed created_by join (ride_models table doesn't have created_by column)
    SELECT 
      rm.id as entity_id,
      'ride_model'::text as entity_type,
      rm.name as entity_name,
      rm.slug as entity_slug,
      NULL::text as park_slug,
      rm.card_image_url as image_url,
      rm.created_at,
      NULL::uuid as created_by_id,
      NULL::text as created_by_username,
      NULL::text as created_by_avatar
    FROM ride_models rm
    
    UNION ALL
    
    -- Locations - FIXED: Removed created_by join (locations table doesn't have created_by column)
    SELECT 
      l.id as entity_id,
      'location'::text as entity_type,
      COALESCE(l.city || ', ' || l.country, l.country, 'Location') as entity_name,
      NULL::text as entity_slug,
      NULL::text as park_slug,
      NULL::text as image_url,
      l.created_at,
      NULL::uuid as created_by_id,
      NULL::text as created_by_username,
      NULL::text as created_by_avatar
    FROM locations l
    
    UNION ALL
    
    -- Timeline Events - KEPT: This table has created_by column
    SELECT 
      te.id as entity_id,
      'timeline_event'::text as entity_type,
      te.event_title as entity_name,
      NULL::text as entity_slug,
      NULL::text as park_slug,
      NULL::text as image_url,
      te.created_at,
      te.created_by as created_by_id,
      prof.username as created_by_username,
      prof.avatar_url as created_by_avatar
    FROM entity_timeline_events te
    LEFT JOIN profiles prof ON prof.user_id = te.created_by
    
    UNION ALL
    
    -- Photos - KEPT: This table has submitted_by column
    SELECT 
      p.id as entity_id,
      'photo'::text as entity_type,
      COALESCE(p.title, 'Photo') as entity_name,
      NULL::text as entity_slug,
      NULL::text as park_slug,
      p.cloudflare_image_url as image_url,
      p.created_at as created_at,
      p.submitted_by as created_by_id,
      prof.username as created_by_username,
      prof.avatar_url as created_by_avatar
    FROM photos p
    LEFT JOIN profiles prof ON prof.user_id = p.submitted_by
  ) combined
  ORDER BY created_at DESC
  LIMIT limit_count;
END;
$function$;