pacnpal/thrilltrack-explorer
1
0
Fork 0
mirror of https://github.com/pacnpal/thrilltrack-explorer.git synced 2025-12-20 04:51:11 -05:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
thrilltrack-explorer/supabase/migrations/20251111153552_5b400540-3922-43fc-9770-afb9eee35bbd.sql
gpt-engineer-app[bot] 2aebe6a041 Connect to Lovable Cloud
2025-11-11 15:36:09 +00:00

712 lines
27 KiB
PL/PgSQL
Raw Permalink Blame History

-- ============================================================================
-- Fix: Add Location Creation Logic to process_approval_transaction
-- ============================================================================
-- Problem: Parks created without location data because park_submission_locations
-- table is not joined and location creation logic is missing
-- Solution: Add LEFT JOIN, fetch location data, create location before park INSERT
-- ============================================================================
-- Drop existing function
DROP FUNCTION IF EXISTS process_approval_transaction(UUID, UUID[], UUID, UUID, TEXT, TEXT, TEXT);
CREATE OR REPLACE FUNCTION process_approval_transaction(
p_submission_id UUID,
p_item_ids UUID[],
p_moderator_id UUID,
p_submitter_id UUID,
p_request_id TEXT DEFAULT NULL,
p_trace_id TEXT DEFAULT NULL,
p_parent_span_id TEXT DEFAULT NULL
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
v_start_time TIMESTAMPTZ;
v_result JSONB;
v_item RECORD;
v_entity_id UUID;
v_approval_results JSONB[] := ARRAY[]::JSONB[];
v_final_status TEXT;
v_all_approved BOOLEAN := TRUE;
v_some_approved BOOLEAN := FALSE;
v_items_processed INTEGER := 0;
v_span_id TEXT;
-- Variables for temporary reference resolution
v_resolved_park_id UUID;
v_resolved_manufacturer_id UUID;
v_resolved_ride_model_id UUID;
v_resolved_operator_id UUID;
v_resolved_property_owner_id UUID;
v_resolved_location_id UUID;
BEGIN
v_start_time := clock_timestamp();
v_span_id := gen_random_uuid()::text;
-- Log span start with trace context
IF p_trace_id IS NOT NULL THEN
RAISE NOTICE 'SPAN: {"spanId": "%", "traceId": "%", "parentSpanId": "%", "name": "process_approval_transaction_rpc", "kind": "INTERNAL", "startTime": %, "attributes": {"submission.id": "%", "item_count": %}}',
v_span_id,
p_trace_id,
p_parent_span_id,
EXTRACT(EPOCH FROM v_start_time) * 1000,
p_submission_id,
array_length(p_item_ids, 1);
END IF;
RAISE NOTICE '[%] Starting atomic approval transaction for submission %',
COALESCE(p_request_id, 'NO_REQUEST_ID'),
p_submission_id;
-- ========================================================================
-- STEP 1: Set session variables (transaction-scoped with is_local=true)
-- ========================================================================
PERFORM set_config('app.current_user_id', p_submitter_id::text, true);
PERFORM set_config('app.submission_id', p_submission_id::text, true);
PERFORM set_config('app.moderator_id', p_moderator_id::text, true);
-- ========================================================================
-- STEP 2: Validate submission ownership and lock status
-- ========================================================================
IF NOT EXISTS (
SELECT 1 FROM content_submissions
WHERE id = p_submission_id
AND (assigned_to = p_moderator_id OR assigned_to IS NULL)
AND status IN ('pending', 'partially_approved')
) THEN
RAISE EXCEPTION 'Submission not found, locked by another moderator, or already processed'
USING ERRCODE = '42501';
END IF;
-- ========================================================================
-- STEP 3: Process each item sequentially within this transaction
-- ========================================================================
FOR v_item IN
SELECT
si.*,
ps.name as park_name,
ps.slug as park_slug,
ps.description as park_description,
ps.park_type,
ps.status as park_status,
ps.location_id,
ps.operator_id,
ps.property_owner_id,
ps.opening_date as park_opening_date,
ps.closing_date as park_closing_date,
ps.opening_date_precision as park_opening_date_precision,
ps.closing_date_precision as park_closing_date_precision,
ps.website_url as park_website_url,
ps.phone as park_phone,
ps.email as park_email,
ps.banner_image_url as park_banner_image_url,
ps.banner_image_id as park_banner_image_id,
ps.card_image_url as park_card_image_url,
ps.card_image_id as park_card_image_id,
psl.country as park_location_country,
psl.state_province as park_location_state,
psl.city as park_location_city,
psl.street_address as park_location_street,
psl.postal_code as park_location_postal,
psl.latitude as park_location_lat,
psl.longitude as park_location_lng,
psl.timezone as park_location_timezone,
rs.name as ride_name,
rs.slug as ride_slug,
rs.park_id as ride_park_id,
rs.category as ride_category,
rs.status as ride_status,
rs.manufacturer_id,
rs.ride_model_id,
rs.opening_date as ride_opening_date,
rs.closing_date as ride_closing_date,
rs.opening_date_precision as ride_opening_date_precision,
rs.closing_date_precision as ride_closing_date_precision,
rs.description as ride_description,
rs.banner_image_url as ride_banner_image_url,
rs.banner_image_id as ride_banner_image_id,
rs.card_image_url as ride_card_image_url,
rs.card_image_id as ride_card_image_id,
cs.name as company_name,
cs.slug as company_slug,
cs.description as company_description,
cs.company_type,
cs.website_url as company_website_url,
cs.founded_year,
cs.founded_date,
cs.founded_date_precision,
cs.headquarters_location,
cs.logo_url,
cs.person_type,
cs.banner_image_url as company_banner_image_url,
cs.banner_image_id as company_banner_image_id,
cs.card_image_url as company_card_image_url,
cs.card_image_id as company_card_image_id,
rms.name as ride_model_name,
rms.slug as ride_model_slug,
rms.manufacturer_id as ride_model_manufacturer_id,
rms.category as ride_model_category,
rms.description as ride_model_description,
rms.banner_image_url as ride_model_banner_image_url,
rms.banner_image_id as ride_model_banner_image_id,
rms.card_image_url as ride_model_card_image_url,
rms.card_image_id as ride_model_card_image_id,
phs.entity_id as photo_entity_id,
phs.entity_type as photo_entity_type,
phs.title as photo_title
FROM submission_items si
LEFT JOIN park_submissions ps ON si.park_submission_id = ps.id
LEFT JOIN park_submission_locations psl ON ps.id = psl.park_submission_id
LEFT JOIN ride_submissions rs ON si.ride_submission_id = rs.id
LEFT JOIN company_submissions cs ON si.company_submission_id = cs.id
LEFT JOIN ride_model_submissions rms ON si.ride_model_submission_id = rms.id
LEFT JOIN photo_submissions phs ON si.photo_submission_id = phs.id
WHERE si.id = ANY(p_item_ids)
ORDER BY si.order_index, si.created_at
LOOP
BEGIN
v_items_processed := v_items_processed + 1;
v_entity_id := NULL;
-- Reset temporary reference variables
v_resolved_park_id := NULL;
v_resolved_manufacturer_id := NULL;
v_resolved_ride_model_id := NULL;
v_resolved_operator_id := NULL;
v_resolved_property_owner_id := NULL;
v_resolved_location_id := NULL;
-- Log item processing span event
IF p_trace_id IS NOT NULL THEN
RAISE NOTICE 'SPAN_EVENT: {"traceId": "%", "parentSpanId": "%", "name": "process_item", "timestamp": %, "attributes": {"item.id": "%", "item.type": "%", "item.action": "%"}}',
p_trace_id,
v_span_id,
EXTRACT(EPOCH FROM clock_timestamp()) * 1000,
v_item.id,
v_item.item_type,
v_item.action_type;
END IF;
-- ====================================================================
-- RELATIONAL LOGIC: Direct INSERT/UPDATE without JSONB
-- ====================================================================
IF v_item.action_type = 'create' THEN
-- ----------------------------------------------------------------
-- CREATE ACTIONS: Direct INSERT into entity tables
-- ----------------------------------------------------------------
IF v_item.item_type = 'park' THEN
-- Create location if location data exists
IF v_item.park_location_country IS NOT NULL OR v_item.park_location_city IS NOT NULL THEN
INSERT INTO locations (
country,
state_province,
city,
street_address,
postal_code,
latitude,
longitude,
timezone
) VALUES (
v_item.park_location_country,
v_item.park_location_state,
v_item.park_location_city,
v_item.park_location_street,
v_item.park_location_postal,
v_item.park_location_lat,
v_item.park_location_lng,
v_item.park_location_timezone
)
RETURNING id INTO v_resolved_location_id;
RAISE NOTICE '[%] Created location % for park submission',
COALESCE(p_request_id, 'NO_REQUEST_ID'),
v_resolved_location_id;
END IF;
-- Resolve temporary operator/property_owner references
IF v_item.operator_id IS NULL THEN
SELECT approved_entity_id INTO v_resolved_operator_id
FROM submission_items
WHERE submission_id = p_submission_id
AND item_type IN ('operator', 'company')
AND approved_entity_id IS NOT NULL
LIMIT 1;
END IF;
IF v_item.property_owner_id IS NULL THEN
SELECT approved_entity_id INTO v_resolved_property_owner_id
FROM submission_items
WHERE submission_id = p_submission_id
AND item_type IN ('property_owner', 'company')
AND approved_entity_id IS NOT NULL
LIMIT 1;
END IF;
INSERT INTO parks (
name, slug, description, park_type, status,
location_id, operator_id, property_owner_id,
opening_date, closing_date,
opening_date_precision, closing_date_precision,
website_url, phone, email,
banner_image_url, banner_image_id,
card_image_url, card_image_id
) VALUES (
v_item.park_name,
v_item.park_slug,
v_item.park_description,
v_item.park_type,
v_item.park_status,
COALESCE(v_resolved_location_id, v_item.location_id),
COALESCE(v_item.operator_id, v_resolved_operator_id),
COALESCE(v_item.property_owner_id, v_resolved_property_owner_id),
v_item.park_opening_date,
v_item.park_closing_date,
v_item.park_opening_date_precision,
v_item.park_closing_date_precision,
v_item.park_website_url,
v_item.park_phone,
v_item.park_email,
v_item.park_banner_image_url,
v_item.park_banner_image_id,
v_item.park_card_image_url,
v_item.park_card_image_id
)
RETURNING id INTO v_entity_id;
ELSIF v_item.item_type = 'ride' THEN
-- Resolve temporary park/manufacturer/ride_model references
IF v_item.ride_park_id IS NULL THEN
SELECT approved_entity_id INTO v_resolved_park_id
FROM submission_items
WHERE submission_id = p_submission_id
AND item_type = 'park'
AND approved_entity_id IS NOT NULL
LIMIT 1;
END IF;
IF v_item.manufacturer_id IS NULL THEN
SELECT approved_entity_id INTO v_resolved_manufacturer_id
FROM submission_items
WHERE submission_id = p_submission_id
AND item_type IN ('manufacturer', 'company')
AND approved_entity_id IS NOT NULL
LIMIT 1;
END IF;
IF v_item.ride_model_id IS NULL THEN
SELECT approved_entity_id INTO v_resolved_ride_model_id
FROM submission_items
WHERE submission_id = p_submission_id
AND item_type = 'ride_model'
AND approved_entity_id IS NOT NULL
LIMIT 1;
END IF;
INSERT INTO rides (
name, slug, park_id,
category, status,
manufacturer_id, ride_model_id,
opening_date, closing_date,
opening_date_precision, closing_date_precision,
description,
banner_image_url, banner_image_id,
card_image_url, card_image_id
) VALUES (
v_item.ride_name,
v_item.ride_slug,
COALESCE(v_item.ride_park_id, v_resolved_park_id),
v_item.ride_category,
v_item.ride_status,
COALESCE(v_item.manufacturer_id, v_resolved_manufacturer_id),
COALESCE(v_item.ride_model_id, v_resolved_ride_model_id),
v_item.ride_opening_date,
v_item.ride_closing_date,
v_item.ride_opening_date_precision,
v_item.ride_closing_date_precision,
v_item.ride_description,
v_item.ride_banner_image_url,
v_item.ride_banner_image_id,
v_item.ride_card_image_url,
v_item.ride_card_image_id
)
RETURNING id INTO v_entity_id;
-- Copy related technical specifications
IF v_entity_id IS NOT NULL AND v_item.ride_submission_id IS NOT NULL THEN
INSERT INTO ride_technical_specifications (
ride_id, specification_key, specification_value, unit, display_order
)
SELECT
v_entity_id,
specification_key,
specification_value,
unit,
display_order
FROM ride_technical_specifications
WHERE ride_id = v_item.ride_submission_id;
-- Copy coaster stats
INSERT INTO ride_coaster_stats (
ride_id, stat_key, stat_value, unit, display_order
)
SELECT
v_entity_id,
stat_key,
stat_value,
unit,
display_order
FROM ride_coaster_stats
WHERE ride_id = v_item.ride_submission_id;
END IF;
ELSIF v_item.item_type IN ('company', 'manufacturer', 'operator', 'property_owner', 'designer') THEN
INSERT INTO companies (
name, slug, description,
company_type, person_type,
website_url, founded_year, founded_date, founded_date_precision,
headquarters_location, logo_url,
banner_image_url, banner_image_id,
card_image_url, card_image_id
) VALUES (
v_item.company_name,
v_item.company_slug,
v_item.company_description,
v_item.company_type,
v_item.person_type,
v_item.company_website_url,
v_item.founded_year,
v_item.founded_date,
v_item.founded_date_precision,
v_item.headquarters_location,
v_item.logo_url,
v_item.company_banner_image_url,
v_item.company_banner_image_id,
v_item.company_card_image_url,
v_item.company_card_image_id
)
RETURNING id INTO v_entity_id;
ELSIF v_item.item_type = 'ride_model' THEN
-- Resolve temporary manufacturer reference
IF v_item.ride_model_manufacturer_id IS NULL THEN
SELECT approved_entity_id INTO v_resolved_manufacturer_id
FROM submission_items
WHERE submission_id = p_submission_id
AND item_type IN ('manufacturer', 'company')
AND approved_entity_id IS NOT NULL
LIMIT 1;
END IF;
INSERT INTO ride_models (
name, slug,
manufacturer_id,
category, description,
banner_image_url, banner_image_id,
card_image_url, card_image_id
) VALUES (
v_item.ride_model_name,
v_item.ride_model_slug,
COALESCE(v_item.ride_model_manufacturer_id, v_resolved_manufacturer_id),
v_item.ride_model_category,
v_item.ride_model_description,
v_item.ride_model_banner_image_url,
v_item.ride_model_banner_image_id,
v_item.ride_model_card_image_url,
v_item.ride_model_card_image_id
)
RETURNING id INTO v_entity_id;
ELSIF v_item.item_type = 'photo' THEN
-- Handle photo uploads
INSERT INTO entity_photos (
entity_id,
entity_type,
title,
photo_submission_id
) VALUES (
v_item.photo_entity_id,
v_item.photo_entity_type,
v_item.photo_title,
v_item.photo_submission_id
)
RETURNING id INTO v_entity_id;
ELSE
RAISE EXCEPTION 'Unknown item type for create: %', v_item.item_type;
END IF;
ELSIF v_item.action_type = 'update' THEN
-- ----------------------------------------------------------------
-- UPDATE ACTIONS: Direct UPDATE to entity tables
-- ----------------------------------------------------------------
IF v_item.entity_id IS NULL THEN
RAISE EXCEPTION 'Update action requires entity_id';
END IF;
IF v_item.item_type = 'park' THEN
-- Create location if location data exists and no location_id provided
IF v_item.location_id IS NULL AND (v_item.park_location_country IS NOT NULL OR v_item.park_location_city IS NOT NULL) THEN
INSERT INTO locations (
country,
state_province,
city,
street_address,
postal_code,
latitude,
longitude,
timezone
) VALUES (
v_item.park_location_country,
v_item.park_location_state,
v_item.park_location_city,
v_item.park_location_street,
v_item.park_location_postal,
v_item.park_location_lat,
v_item.park_location_lng,
v_item.park_location_timezone
)
RETURNING id INTO v_resolved_location_id;
RAISE NOTICE '[%] Created location % for park update',
COALESCE(p_request_id, 'NO_REQUEST_ID'),
v_resolved_location_id;
END IF;
UPDATE parks SET
name = v_item.park_name,
slug = v_item.park_slug,
description = v_item.park_description,
park_type = v_item.park_type,
status = v_item.park_status,
location_id = COALESCE(v_resolved_location_id, v_item.location_id),
operator_id = v_item.operator_id,
property_owner_id = v_item.property_owner_id,
opening_date = v_item.park_opening_date,
closing_date = v_item.park_closing_date,
opening_date_precision = v_item.park_opening_date_precision,
closing_date_precision = v_item.park_closing_date_precision,
website_url = v_item.park_website_url,
phone = v_item.park_phone,
email = v_item.park_email,
banner_image_url = v_item.park_banner_image_url,
banner_image_id = v_item.park_banner_image_id,
card_image_url = v_item.park_card_image_url,
card_image_id = v_item.park_card_image_id,
updated_at = now()
WHERE id = v_item.entity_id;
ELSIF v_item.item_type = 'ride' THEN
UPDATE rides SET
name = v_item.ride_name,
slug = v_item.ride_slug,
park_id = v_item.ride_park_id,
category = v_item.ride_category,
status = v_item.ride_status,
manufacturer_id = v_item.manufacturer_id,
ride_model_id = v_item.ride_model_id,
opening_date = v_item.ride_opening_date,
closing_date = v_item.ride_closing_date,
opening_date_precision = v_item.ride_opening_date_precision,
closing_date_precision = v_item.ride_closing_date_precision,
description = v_item.ride_description,
banner_image_url = v_item.ride_banner_image_url,
banner_image_id = v_item.ride_banner_image_id,
card_image_url = v_item.ride_card_image_url,
card_image_id = v_item.ride_card_image_id,
updated_at = now()
WHERE id = v_item.entity_id;
ELSIF v_item.item_type IN ('company', 'manufacturer', 'operator', 'property_owner', 'designer') THEN
UPDATE companies SET
name = v_item.company_name,
slug = v_item.company_slug,
description = v_item.company_description,
company_type = v_item.company_type,
person_type = v_item.person_type,
website_url = v_item.company_website_url,
founded_year = v_item.founded_year,
founded_date = v_item.founded_date,
founded_date_precision = v_item.founded_date_precision,
headquarters_location = v_item.headquarters_location,
logo_url = v_item.logo_url,
banner_image_url = v_item.company_banner_image_url,
banner_image_id = v_item.company_banner_image_id,
card_image_url = v_item.company_card_image_url,
card_image_id = v_item.company_card_image_id,
updated_at = now()
WHERE id = v_item.entity_id;
ELSIF v_item.item_type = 'ride_model' THEN
UPDATE ride_models SET
name = v_item.ride_model_name,
slug = v_item.ride_model_slug,
manufacturer_id = v_item.ride_model_manufacturer_id,
category = v_item.ride_model_category,
description = v_item.ride_model_description,
banner_image_url = v_item.ride_model_banner_image_url,
banner_image_id = v_item.ride_model_banner_image_id,
card_image_url = v_item.ride_model_card_image_url,
card_image_id = v_item.ride_model_card_image_id,
updated_at = now()
WHERE id = v_item.entity_id;
ELSE
RAISE EXCEPTION 'Unknown item type for update: %', v_item.item_type;
END IF;
v_entity_id := v_item.entity_id;
ELSIF v_item.action_type = 'delete' THEN
-- ----------------------------------------------------------------
-- DELETE ACTIONS: Soft delete (set status to 'removed')
-- ----------------------------------------------------------------
IF v_item.entity_id IS NULL THEN
RAISE EXCEPTION 'Delete action requires entity_id';
END IF;
IF v_item.item_type = 'park' THEN
UPDATE parks SET
status = 'removed',
updated_at = now()
WHERE id = v_item.entity_id;
ELSIF v_item.item_type = 'ride' THEN
UPDATE rides SET
status = 'removed',
updated_at = now()
WHERE id = v_item.entity_id;
ELSE
RAISE EXCEPTION 'Delete not supported for item type: %', v_item.item_type;
END IF;
v_entity_id := v_item.entity_id;
ELSE
RAISE EXCEPTION 'Unknown action type: %', v_item.action_type;
END IF;
-- ==================================================================
-- Mark submission item as approved
-- ==================================================================
UPDATE submission_items
SET
approved_entity_id = v_entity_id,
status = 'approved',
updated_at = now()
WHERE id = v_item.id;
v_approval_results := v_approval_results || jsonb_build_object(
'item_id', v_item.id,
'item_type', v_item.item_type,
'action_type', v_item.action_type,
'entity_id', v_entity_id,
'status', 'approved'
);
v_some_approved := TRUE;
RAISE NOTICE '[%] Approved item % (type: %, action: %) -> entity %',
COALESCE(p_request_id, 'NO_REQUEST_ID'),
v_item.id,
v_item.item_type,
v_item.action_type,
v_entity_id;
EXCEPTION
WHEN OTHERS THEN
-- Log error but continue processing other items
RAISE WARNING '[%] Failed to process item %: % - %',
COALESCE(p_request_id, 'NO_REQUEST_ID'),
v_item.id,
SQLERRM,
SQLSTATE;
v_approval_results := v_approval_results || jsonb_build_object(
'item_id', v_item.id,
'item_type', v_item.item_type,
'action_type', v_item.action_type,
'status', 'failed',
'error', SQLERRM
);
v_all_approved := FALSE;
END;
END LOOP;
-- ==========================================================================
-- STEP 4: Update submission status based on results
-- ==========================================================================
IF v_all_approved THEN
v_final_status := 'approved';
ELSIF v_some_approved THEN
v_final_status := 'partially_approved';
ELSE
v_final_status := 'rejected';
END IF;
UPDATE content_submissions
SET
status = v_final_status,
reviewed_at = now(),
reviewer_id = p_moderator_id,
assigned_to = NULL,
lock_expires_at = NULL
WHERE id = p_submission_id;
-- ==========================================================================
-- STEP 5: Record transaction metrics
-- ==========================================================================
INSERT INTO approval_transaction_metrics (
submission_id,
moderator_id,
items_count,
items_approved,
duration_ms,
status,
request_id
) VALUES (
p_submission_id,
p_moderator_id,
array_length(p_item_ids, 1),
v_items_processed,
EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000,
v_final_status,
p_request_id
);
-- Log span end
IF p_trace_id IS NOT NULL THEN
RAISE NOTICE 'SPAN: {"spanId": "%", "traceId": "%", "name": "process_approval_transaction_rpc", "kind": "INTERNAL", "endTime": %, "attributes": {"submission.status": "%", "items.processed": %, "items.approved": %}}',
v_span_id,
p_trace_id,
EXTRACT(EPOCH FROM clock_timestamp()) * 1000,
v_final_status,
v_items_processed,
v_items_processed;
END IF;
-- Build result
v_result := jsonb_build_object(
'success', v_all_approved,
'submissionId', p_submission_id,
'status', v_final_status,
'itemsProcessed', v_items_processed,
'results', v_approval_results,
'durationMs', EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000
);
RAISE NOTICE '[%] Completed approval transaction for submission % with status %',
COALESCE(p_request_id, 'NO_REQUEST_ID'),
p_submission_id,
v_final_status;
RETURN v_result;
END;
$$;
Reference in New Issue View Git Blame Copy Permalink