mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
123 lines
3.9 KiB
PL/PgSQL
123 lines
3.9 KiB
PL/PgSQL
-- Create function to dynamically get entity data from submission tables
|
|
CREATE OR REPLACE FUNCTION get_submission_item_entity_data(
|
|
p_item_type text,
|
|
p_item_data_id uuid
|
|
) RETURNS jsonb AS $$
|
|
DECLARE
|
|
v_result jsonb;
|
|
BEGIN
|
|
CASE p_item_type
|
|
WHEN 'park' THEN
|
|
SELECT to_jsonb(ps.*) INTO v_result
|
|
FROM park_submissions ps
|
|
WHERE ps.id = p_item_data_id;
|
|
WHEN 'ride' THEN
|
|
SELECT to_jsonb(rs.*) INTO v_result
|
|
FROM ride_submissions rs
|
|
WHERE rs.id = p_item_data_id;
|
|
WHEN 'manufacturer', 'operator', 'designer', 'property_owner' THEN
|
|
SELECT to_jsonb(cs.*) INTO v_result
|
|
FROM company_submissions cs
|
|
WHERE cs.id = p_item_data_id;
|
|
WHEN 'ride_model' THEN
|
|
SELECT to_jsonb(rms.*) INTO v_result
|
|
FROM ride_model_submissions rms
|
|
WHERE rms.id = p_item_data_id;
|
|
WHEN 'photo' THEN
|
|
SELECT to_jsonb(ps.*) INTO v_result
|
|
FROM photo_submissions ps
|
|
WHERE ps.id = p_item_data_id;
|
|
ELSE
|
|
v_result := NULL;
|
|
END CASE;
|
|
|
|
RETURN v_result;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
-- Create optimized view for moderation queue with pre-joined entity data
|
|
CREATE OR REPLACE VIEW moderation_queue_with_entities AS
|
|
SELECT
|
|
cs.id,
|
|
cs.submission_type,
|
|
cs.status,
|
|
cs.submitted_at,
|
|
cs.user_id as submitted_by,
|
|
cs.reviewed_at,
|
|
cs.reviewer_id as reviewed_by,
|
|
cs.reviewer_notes as review_notes,
|
|
cs.assigned_to,
|
|
cs.locked_until,
|
|
cs.escalated,
|
|
cs.escalation_reason,
|
|
cs.is_test_data,
|
|
-- Pre-load submitter profile
|
|
jsonb_build_object(
|
|
'id', submitter.id,
|
|
'username', submitter.username,
|
|
'role', submitter_role.role
|
|
) as submitter_profile,
|
|
-- Pre-load reviewer profile (if exists)
|
|
CASE
|
|
WHEN reviewer.id IS NOT NULL THEN
|
|
jsonb_build_object(
|
|
'id', reviewer.id,
|
|
'username', reviewer.username,
|
|
'role', reviewer_role.role
|
|
)
|
|
ELSE NULL
|
|
END as reviewer_profile,
|
|
-- Pre-load assigned moderator profile (if exists)
|
|
CASE
|
|
WHEN assigned.id IS NOT NULL THEN
|
|
jsonb_build_object(
|
|
'id', assigned.id,
|
|
'username', assigned.username,
|
|
'role', assigned_role.role
|
|
)
|
|
ELSE NULL
|
|
END as assigned_profile,
|
|
-- Pre-load submission items with entity data
|
|
COALESCE(
|
|
(
|
|
SELECT jsonb_agg(
|
|
jsonb_build_object(
|
|
'id', si.id,
|
|
'submission_id', si.submission_id,
|
|
'item_type', si.item_type,
|
|
'item_data_id', si.item_data_id,
|
|
'action_type', si.action_type,
|
|
'status', si.status,
|
|
'depends_on', si.depends_on,
|
|
'rejection_reason', si.rejection_reason,
|
|
'order_index', si.order_index,
|
|
'approved_entity_id', si.approved_entity_id,
|
|
'created_at', si.created_at,
|
|
'entity_data', get_submission_item_entity_data(si.item_type, si.item_data_id)
|
|
)
|
|
ORDER BY si.order_index, si.created_at
|
|
)
|
|
FROM submission_items si
|
|
WHERE si.submission_id = cs.id
|
|
),
|
|
'[]'::jsonb
|
|
) as submission_items
|
|
FROM content_submissions cs
|
|
LEFT JOIN profiles submitter ON cs.user_id = submitter.user_id
|
|
LEFT JOIN user_roles submitter_role ON submitter.user_id = submitter_role.user_id
|
|
LEFT JOIN profiles reviewer ON cs.reviewer_id = reviewer.user_id
|
|
LEFT JOIN user_roles reviewer_role ON reviewer.user_id = reviewer_role.user_id
|
|
LEFT JOIN profiles assigned ON cs.assigned_to = assigned.user_id
|
|
LEFT JOIN user_roles assigned_role ON assigned.user_id = assigned_role.user_id;
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX IF NOT EXISTS idx_content_submissions_queue
|
|
ON content_submissions(status, submitted_at DESC)
|
|
WHERE status IN ('pending', 'in_review');
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_content_submissions_locks
|
|
ON content_submissions(assigned_to, locked_until)
|
|
WHERE assigned_to IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_submission_items_item_data_id
|
|
ON submission_items(item_data_id, item_type); |