thrilltrack-explorer/supabase/migrations/20251103204247_8061cb88-20af-4d81-b203-d8e243a59d31.sql

-- Update log_admin_action to write to relational admin_audit_details table
CREATE OR REPLACE FUNCTION public.log_admin_action(
  _admin_user_id uuid,
  _target_user_id uuid,
  _action text,
  _details jsonb DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_audit_log_id uuid;
  v_detail_record record;
BEGIN
  -- Insert into admin_audit_log (without details JSONB)
  INSERT INTO public.admin_audit_log (
    admin_user_id,
    target_user_id,
    action
  ) VALUES (
    _admin_user_id,
    _target_user_id,
    _action
  )
  RETURNING id INTO v_audit_log_id;
  
  -- Write details to relational admin_audit_details table
  IF _details IS NOT NULL AND jsonb_typeof(_details) = 'object' THEN
    FOR v_detail_record IN
      SELECT key, value::text as text_value
      FROM jsonb_each_text(_details)
    LOOP
      INSERT INTO public.admin_audit_details (
        audit_log_id,
        detail_key,
        detail_value
      ) VALUES (
        v_audit_log_id,
        v_detail_record.key,
        v_detail_record.text_value
      );
    END LOOP;
  END IF;
END;
$$;