thrilltrack-explorer/supabase/migrations/20250920125518_8928dce2-8523-4d7a-9867-02995ed31587.sql

-- Reviews System Database Restructure Migration

-- 1. Add rating fields to companies table (manufacturers, operators, owners)
ALTER TABLE public.companies 
ADD COLUMN IF NOT EXISTS average_rating DECIMAL(3,2) DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS review_count INTEGER DEFAULT 0;

-- 2. Add validation constraint to reviews table to ensure exactly one parent (ride OR park)
ALTER TABLE public.reviews 
ADD CONSTRAINT reviews_single_parent_check 
CHECK (
  (park_id IS NOT NULL AND ride_id IS NULL) OR 
  (park_id IS NULL AND ride_id IS NOT NULL)
);

-- 3. Create function to recalculate park ratings
CREATE OR REPLACE FUNCTION public.update_park_ratings(target_park_id UUID)
RETURNS void AS $$
DECLARE
  avg_rating DECIMAL(3,2);
  review_cnt INTEGER;
BEGIN
  SELECT 
    COALESCE(AVG(rating), 0)::DECIMAL(3,2),
    COUNT(*)
  INTO avg_rating, review_cnt
  FROM public.reviews 
  WHERE park_id = target_park_id AND moderation_status = 'approved';
  
  UPDATE public.parks 
  SET 
    average_rating = avg_rating,
    review_count = review_cnt,
    updated_at = now()
  WHERE id = target_park_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 4. Create function to recalculate ride ratings
CREATE OR REPLACE FUNCTION public.update_ride_ratings(target_ride_id UUID)
RETURNS void AS $$
DECLARE
  avg_rating DECIMAL(3,2);
  review_cnt INTEGER;
BEGIN
  SELECT 
    COALESCE(AVG(rating), 0)::DECIMAL(3,2),
    COUNT(*)
  INTO avg_rating, review_cnt
  FROM public.reviews 
  WHERE ride_id = target_ride_id AND moderation_status = 'approved';
  
  UPDATE public.rides 
  SET 
    average_rating = avg_rating,
    review_count = review_cnt,
    updated_at = now()
  WHERE id = target_ride_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 5. Create function to recalculate company ratings (manufacturers, operators)
CREATE OR REPLACE FUNCTION public.update_company_ratings(target_company_id UUID)
RETURNS void AS $$
DECLARE
  avg_rating DECIMAL(3,2);
  review_cnt INTEGER;
BEGIN
  -- Calculate ratings from parks operated by this company
  WITH park_reviews AS (
    SELECT r.rating
    FROM public.reviews r
    JOIN public.parks p ON r.park_id = p.id
    WHERE (p.operator_id = target_company_id OR p.property_owner_id = target_company_id)
      AND r.moderation_status = 'approved'
  ),
  -- Calculate ratings from rides manufactured/designed by this company
  ride_reviews AS (
    SELECT r.rating
    FROM public.reviews r
    JOIN public.rides rd ON r.ride_id = rd.id
    WHERE (rd.manufacturer_id = target_company_id OR rd.designer_id = target_company_id)
      AND r.moderation_status = 'approved'
  ),
  -- Combine all reviews
  all_reviews AS (
    SELECT rating FROM park_reviews
    UNION ALL
    SELECT rating FROM ride_reviews
  )
  SELECT 
    COALESCE(AVG(rating), 0)::DECIMAL(3,2),
    COUNT(*)
  INTO avg_rating, review_cnt
  FROM all_reviews;
  
  UPDATE public.companies 
  SET 
    average_rating = avg_rating,
    review_count = review_cnt,
    updated_at = now()
  WHERE id = target_company_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 6. Create function to update all related ratings when a review changes
CREATE OR REPLACE FUNCTION public.update_all_ratings_for_review()
RETURNS trigger AS $$
DECLARE
  company_ids UUID[];
BEGIN
  -- Handle both INSERT/UPDATE and DELETE cases
  IF TG_OP = 'DELETE' THEN
    -- Update park rating if this was a park review
    IF OLD.park_id IS NOT NULL THEN
      PERFORM public.update_park_ratings(OLD.park_id);
      
      -- Update related company ratings
      SELECT ARRAY[p.operator_id, p.property_owner_id]
      INTO company_ids
      FROM public.parks p
      WHERE p.id = OLD.park_id;
    END IF;
    
    -- Update ride rating if this was a ride review
    IF OLD.ride_id IS NOT NULL THEN
      PERFORM public.update_ride_ratings(OLD.ride_id);
      
      -- Update related company ratings
      SELECT ARRAY[r.manufacturer_id, r.designer_id]
      INTO company_ids
      FROM public.rides r
      WHERE r.id = OLD.ride_id;
    END IF;
    
    RETURN OLD;
  ELSE
    -- Handle INSERT/UPDATE
    -- Update park rating if this is a park review
    IF NEW.park_id IS NOT NULL THEN
      PERFORM public.update_park_ratings(NEW.park_id);
      
      -- Update related company ratings
      SELECT ARRAY[p.operator_id, p.property_owner_id]
      INTO company_ids
      FROM public.parks p
      WHERE p.id = NEW.park_id;
    END IF;
    
    -- Update ride rating if this is a ride review
    IF NEW.ride_id IS NOT NULL THEN
      PERFORM public.update_ride_ratings(NEW.ride_id);
      
      -- Update related company ratings
      SELECT ARRAY[r.manufacturer_id, r.designer_id]
      INTO company_ids
      FROM public.rides r
      WHERE r.id = NEW.ride_id;
    END IF;
    
    RETURN NEW;
  END IF;
  
  -- Update company ratings for all related companies
  IF company_ids IS NOT NULL THEN
    FOR i IN 1..array_length(company_ids, 1) LOOP
      IF company_ids[i] IS NOT NULL THEN
        PERFORM public.update_company_ratings(company_ids[i]);
      END IF;
    END LOOP;
  END IF;
  
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 7. Create triggers for automatic rating updates
DROP TRIGGER IF EXISTS reviews_rating_update_trigger ON public.reviews;
CREATE TRIGGER reviews_rating_update_trigger
  AFTER INSERT OR UPDATE OR DELETE ON public.reviews
  FOR EACH ROW
  EXECUTE FUNCTION public.update_all_ratings_for_review();

-- 8. Add performance indexes
CREATE INDEX IF NOT EXISTS idx_reviews_park_id_status ON public.reviews(park_id, moderation_status);
CREATE INDEX IF NOT EXISTS idx_reviews_ride_id_status ON public.reviews(ride_id, moderation_status);
CREATE INDEX IF NOT EXISTS idx_parks_operator_id ON public.parks(operator_id);
CREATE INDEX IF NOT EXISTS idx_parks_property_owner_id ON public.parks(property_owner_id);
CREATE INDEX IF NOT EXISTS idx_rides_manufacturer_id ON public.rides(manufacturer_id);
CREATE INDEX IF NOT EXISTS idx_rides_designer_id ON public.rides(designer_id);

-- 9. Initial calculation of all existing ratings
-- Update all park ratings
SELECT public.update_park_ratings(id) FROM public.parks;

-- Update all ride ratings
SELECT public.update_ride_ratings(id) FROM public.rides;

-- Update all company ratings
SELECT public.update_company_ratings(id) FROM public.companies;

-- 10. Add helpful comments
COMMENT ON COLUMN public.companies.average_rating IS 'Average rating calculated from all reviews of parks operated and rides manufactured by this company';
COMMENT ON COLUMN public.companies.review_count IS 'Total number of reviews for parks and rides associated with this company';
COMMENT ON CONSTRAINT reviews_single_parent_check ON public.reviews IS 'Ensures each review belongs to exactly one parent entity (park OR ride, not both)';

-- 11. Grant necessary permissions
GRANT EXECUTE ON FUNCTION public.update_park_ratings(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.update_ride_ratings(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION public.update_company_ratings(UUID) TO authenticated;