thrilltrack-explorer/supabase/migrations/20251112140129_f49e9ec3-ed7d-40ef-88ea-e2b2ea85dca4.sql

-- Create materialized view for approval history with detailed audit trail
CREATE MATERIALIZED VIEW approval_history_detailed AS
SELECT 
  si.id as item_id,
  si.submission_id,
  si.item_type,
  si.action_type,
  si.status,
  si.approved_at,
  si.approved_entity_id,
  si.created_at,
  si.updated_at,
  -- Calculate approval duration (seconds)
  EXTRACT(EPOCH FROM (si.approved_at - si.created_at)) as approval_time_seconds,
  -- Submission context
  cs.submission_type,
  cs.user_id as submitter_id,
  cs.reviewer_id as approver_id,
  cs.submitted_at,
  -- Submitter profile
  p_submitter.username as submitter_username,
  p_submitter.display_name as submitter_display_name,
  p_submitter.avatar_url as submitter_avatar_url,
  -- Approver profile
  p_approver.username as approver_username,
  p_approver.display_name as approver_display_name,
  p_approver.avatar_url as approver_avatar_url,
  -- Entity slugs for linking (dynamic based on item_type)
  CASE 
    WHEN si.item_type = 'park' THEN (SELECT slug FROM parks WHERE id = si.approved_entity_id)
    WHEN si.item_type = 'ride' THEN (SELECT slug FROM rides WHERE id = si.approved_entity_id)
    WHEN si.item_type = 'manufacturer' THEN (SELECT slug FROM companies WHERE id = si.approved_entity_id AND company_type = 'manufacturer')
    WHEN si.item_type = 'designer' THEN (SELECT slug FROM companies WHERE id = si.approved_entity_id AND company_type = 'designer')
    WHEN si.item_type = 'operator' THEN (SELECT slug FROM companies WHERE id = si.approved_entity_id AND company_type = 'operator')
    WHEN si.item_type = 'ride_model' THEN (SELECT slug FROM ride_models WHERE id = si.approved_entity_id)
    ELSE NULL
  END as entity_slug,
  -- Entity names for display
  CASE 
    WHEN si.item_type = 'park' THEN (SELECT name FROM parks WHERE id = si.approved_entity_id)
    WHEN si.item_type = 'ride' THEN (SELECT name FROM rides WHERE id = si.approved_entity_id)
    WHEN si.item_type = 'manufacturer' THEN (SELECT name FROM companies WHERE id = si.approved_entity_id AND company_type = 'manufacturer')
    WHEN si.item_type = 'designer' THEN (SELECT name FROM companies WHERE id = si.approved_entity_id AND company_type = 'designer')
    WHEN si.item_type = 'operator' THEN (SELECT name FROM companies WHERE id = si.approved_entity_id AND company_type = 'operator')
    WHEN si.item_type = 'ride_model' THEN (SELECT name FROM ride_models WHERE id = si.approved_entity_id)
    ELSE NULL
  END as entity_name
FROM submission_items si
JOIN content_submissions cs ON cs.id = si.submission_id
LEFT JOIN profiles p_submitter ON p_submitter.user_id = cs.user_id
LEFT JOIN profiles p_approver ON p_approver.user_id = cs.reviewer_id
WHERE si.approved_at IS NOT NULL
  AND si.status = 'approved'
ORDER BY si.approved_at DESC;

-- Create indexes for fast lookups
CREATE INDEX idx_approval_history_approved_at ON approval_history_detailed(approved_at DESC);
CREATE INDEX idx_approval_history_item_type ON approval_history_detailed(item_type);
CREATE INDEX idx_approval_history_approver ON approval_history_detailed(approver_id);
CREATE INDEX idx_approval_history_submitter ON approval_history_detailed(submitter_id);

-- Function to refresh the materialized view
CREATE OR REPLACE FUNCTION refresh_approval_history()
RETURNS void 
LANGUAGE plpgsql 
SECURITY DEFINER
SET search_path TO 'public'
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY approval_history_detailed;
END;
$$;

-- Security-definer function to query approval history (moderators only)
CREATE OR REPLACE FUNCTION get_approval_history(
  p_item_type text DEFAULT NULL,
  p_approver_id uuid DEFAULT NULL,
  p_from_date timestamptz DEFAULT NULL,
  p_to_date timestamptz DEFAULT NULL,
  p_limit integer DEFAULT 100,
  p_offset integer DEFAULT 0
)
RETURNS TABLE (
  item_id uuid,
  submission_id uuid,
  item_type text,
  action_type text,
  status text,
  approved_at timestamptz,
  approved_entity_id uuid,
  created_at timestamptz,
  updated_at timestamptz,
  approval_time_seconds numeric,
  submission_type text,
  submitter_id uuid,
  approver_id uuid,
  submitted_at timestamptz,
  submitter_username text,
  submitter_display_name text,
  submitter_avatar_url text,
  approver_username text,
  approver_display_name text,
  approver_avatar_url text,
  entity_slug text,
  entity_name text
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
BEGIN
  -- Check if user is a moderator
  IF NOT is_moderator(auth.uid()) THEN
    RAISE EXCEPTION 'Access denied: Moderator role required';
  END IF;

  -- Return filtered results
  RETURN QUERY
  SELECT 
    ahd.item_id,
    ahd.submission_id,
    ahd.item_type,
    ahd.action_type,
    ahd.status,
    ahd.approved_at,
    ahd.approved_entity_id,
    ahd.created_at,
    ahd.updated_at,
    ahd.approval_time_seconds,
    ahd.submission_type,
    ahd.submitter_id,
    ahd.approver_id,
    ahd.submitted_at,
    ahd.submitter_username,
    ahd.submitter_display_name,
    ahd.submitter_avatar_url,
    ahd.approver_username,
    ahd.approver_display_name,
    ahd.approver_avatar_url,
    ahd.entity_slug,
    ahd.entity_name
  FROM approval_history_detailed ahd
  WHERE (p_item_type IS NULL OR ahd.item_type = p_item_type)
    AND (p_approver_id IS NULL OR ahd.approver_id = p_approver_id)
    AND (p_from_date IS NULL OR ahd.approved_at >= p_from_date)
    AND (p_to_date IS NULL OR ahd.approved_at < p_to_date + interval '1 day')
  ORDER BY ahd.approved_at DESC
  LIMIT p_limit
  OFFSET p_offset;
END;
$$;

-- Grant execute permission to authenticated users (function checks moderator role internally)
GRANT EXECUTE ON FUNCTION get_approval_history TO authenticated;

COMMENT ON MATERIALIZED VIEW approval_history_detailed IS 'Materialized view storing approval history data - access via get_approval_history() function';
COMMENT ON FUNCTION refresh_approval_history() IS 'Refreshes the approval history materialized view - call after bulk approvals';
COMMENT ON FUNCTION get_approval_history IS 'Query approval history with filters - moderators only';