thrilltrack-explorer/supabase/migrations/20251006154129_c511f797-c8c5-4e87-82e8-6a139739beab.sql

-- Phase 2: Fix rollback function and add auto-versioning triggers

-- Drop the placeholder rollback function
DROP FUNCTION IF EXISTS public.rollback_to_version(text, uuid, uuid, uuid, text);

-- Enhanced rollback function with dynamic SQL
CREATE OR REPLACE FUNCTION public.rollback_to_version(
  p_entity_type TEXT,
  p_entity_id UUID,
  p_target_version_id UUID,
  p_changed_by UUID,
  p_reason TEXT
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_target_data JSONB;
  v_table_name TEXT;
  v_new_version_id UUID;
  v_key TEXT;
  v_value TEXT;
  v_update_parts TEXT[];
  v_sql TEXT;
BEGIN
  -- Get target version data
  SELECT version_data INTO v_target_data
  FROM public.entity_versions
  WHERE id = p_target_version_id
    AND entity_type = p_entity_type
    AND entity_id = p_entity_id;

  IF v_target_data IS NULL THEN
    RAISE EXCEPTION 'Target version not found';
  END IF;

  -- Determine table name from entity type
  v_table_name := CASE p_entity_type
    WHEN 'park' THEN 'parks'
    WHEN 'ride' THEN 'rides'
    WHEN 'company' THEN 'companies'
    WHEN 'ride_model' THEN 'ride_models'
    ELSE p_entity_type || 's'
  END;

  -- Build UPDATE statement dynamically
  v_update_parts := ARRAY[]::TEXT[];
  
  FOR v_key, v_value IN SELECT * FROM jsonb_each_text(v_target_data)
  LOOP
    -- Skip metadata fields
    IF v_key NOT IN ('id', 'created_at', 'updated_at') THEN
      v_update_parts := array_append(v_update_parts, 
        format('%I = %L', v_key, v_value)
      );
    END IF;
  END LOOP;

  -- Execute the UPDATE
  IF array_length(v_update_parts, 1) > 0 THEN
    v_sql := format(
      'UPDATE %I SET %s, updated_at = now() WHERE id = %L',
      v_table_name,
      array_to_string(v_update_parts, ', '),
      p_entity_id
    );
    
    EXECUTE v_sql;
  END IF;

  -- Create new version with restored change type
  v_new_version_id := public.create_entity_version(
    p_entity_type,
    p_entity_id,
    v_target_data,
    p_changed_by,
    'Rolled back: ' || p_reason,
    NULL,
    'restored'
  );

  RETURN v_new_version_id;
END;
$$;

-- Auto-version creation trigger function
CREATE OR REPLACE FUNCTION public.auto_create_entity_version()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_entity_type TEXT;
  v_change_type version_change_type;
  v_user_id UUID;
  v_version_data JSONB;
BEGIN
  -- Determine entity type from table name
  v_entity_type := CASE TG_TABLE_NAME
    WHEN 'parks' THEN 'park'
    WHEN 'rides' THEN 'ride'
    WHEN 'companies' THEN 'company'
    WHEN 'ride_models' THEN 'ride_model'
    ELSE substring(TG_TABLE_NAME from 1 for length(TG_TABLE_NAME) - 1)
  END;

  -- Determine change type
  v_change_type := CASE TG_OP
    WHEN 'INSERT' THEN 'created'::version_change_type
    WHEN 'UPDATE' THEN 'updated'::version_change_type
    ELSE 'updated'::version_change_type
  END;

  -- Get user from session or auth context
  BEGIN
    v_user_id := current_setting('app.current_user_id', true)::UUID;
  EXCEPTION WHEN OTHERS THEN
    v_user_id := auth.uid();
  END;

  -- Convert NEW record to JSONB
  v_version_data := to_jsonb(NEW);

  -- Create version (only if we have a user context)
  IF v_user_id IS NOT NULL THEN
    PERFORM public.create_entity_version(
      v_entity_type,
      NEW.id,
      v_version_data,
      v_user_id,
      CASE TG_OP
        WHEN 'INSERT' THEN 'Entity created'
        WHEN 'UPDATE' THEN 'Entity updated'
        ELSE 'Entity modified'
      END,
      NULL,
      v_change_type
    );
  END IF;

  RETURN NEW;
END;
$$;

-- Add triggers to entity tables
DROP TRIGGER IF EXISTS auto_version_parks ON public.parks;
CREATE TRIGGER auto_version_parks
  AFTER INSERT OR UPDATE ON public.parks
  FOR EACH ROW
  EXECUTE FUNCTION public.auto_create_entity_version();

DROP TRIGGER IF EXISTS auto_version_rides ON public.rides;
CREATE TRIGGER auto_version_rides
  AFTER INSERT OR UPDATE ON public.rides
  FOR EACH ROW
  EXECUTE FUNCTION public.auto_create_entity_version();

DROP TRIGGER IF EXISTS auto_version_companies ON public.companies;
CREATE TRIGGER auto_version_companies
  AFTER INSERT OR UPDATE ON public.companies
  FOR EACH ROW
  EXECUTE FUNCTION public.auto_create_entity_version();

DROP TRIGGER IF EXISTS auto_version_ride_models ON public.ride_models;
CREATE TRIGGER auto_version_ride_models
  AFTER INSERT OR UPDATE ON public.ride_models
  FOR EACH ROW
  EXECUTE FUNCTION public.auto_create_entity_version();

COMMENT ON FUNCTION public.rollback_to_version IS 'Restores an entity to a previous version using dynamic SQL';
COMMENT ON FUNCTION public.auto_create_entity_version IS 'Automatically creates version records when entities are modified';