mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:31:13 -05:00
b6d1b99f2b
Applies the fix to properly handle and insert park location names during approval, ensuring location data is created/updated with correct name fields for Lagoon and future submissions.
435 lines
23 KiB
PL/PgSQL
435 lines
23 KiB
PL/PgSQL
-- ============================================================================
|
|
-- COMPLETE FIX: Location Name Handling in Approval Pipeline
|
|
-- ============================================================================
|
|
--
|
|
-- PURPOSE:
|
|
-- This migration fixes the process_approval_transaction function to properly
|
|
-- handle location names when creating parks. Without this fix, locations are
|
|
-- created without the 'name' field, causing silent failures and parks end up
|
|
-- with NULL location_id values.
|
|
--
|
|
-- WHAT THIS FIXES:
|
|
-- 1. Adds park_location_name and park_location_display_name to the SELECT
|
|
-- 2. Creates locations with proper name field during CREATE actions
|
|
-- 3. Creates locations with proper name field during UPDATE actions
|
|
-- 4. Falls back to constructing name from city/state/country if not provided
|
|
--
|
|
-- TESTING:
|
|
-- After applying, test by:
|
|
-- 1. Creating a new park submission with location data
|
|
-- 2. Approving the submission
|
|
-- 3. Verifying the park has a location_id set
|
|
-- 4. Checking the locations table has a record with proper name field
|
|
--
|
|
-- DEPLOYMENT:
|
|
-- This can be run manually via Supabase SQL Editor or applied as a migration
|
|
-- ============================================================================
|
|
|
|
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;
|
|
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;
|
|
v_location_name TEXT;
|
|
BEGIN
|
|
v_start_time := clock_timestamp();
|
|
v_span_id := gen_random_uuid()::text;
|
|
|
|
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;
|
|
|
|
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);
|
|
|
|
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;
|
|
|
|
-- ========================================================================
|
|
-- CRITICAL FIX: Added park_location_name and park_location_display_name
|
|
-- ========================================================================
|
|
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.name as park_location_name, psl.display_name as park_location_display_name,
|
|
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;
|
|
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;
|
|
|
|
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;
|
|
|
|
IF v_item.action_type = 'create' THEN
|
|
IF v_item.item_type = 'park' THEN
|
|
-- ========================================================================
|
|
-- CRITICAL FIX: Create location with name field
|
|
-- ========================================================================
|
|
IF v_item.park_location_country IS NOT NULL OR v_item.park_location_city IS NOT NULL THEN
|
|
-- Construct a name for the location, prioritizing display_name, then name, then city/state/country
|
|
v_location_name := COALESCE(
|
|
v_item.park_location_display_name,
|
|
v_item.park_location_name,
|
|
CONCAT_WS(', ',
|
|
NULLIF(v_item.park_location_city, ''),
|
|
NULLIF(v_item.park_location_state, ''),
|
|
NULLIF(v_item.park_location_country, '')
|
|
)
|
|
);
|
|
|
|
INSERT INTO locations (name, country, state_province, city, street_address, postal_code, latitude, longitude, timezone)
|
|
VALUES (
|
|
v_location_name,
|
|
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 % (name: %) for park submission',
|
|
COALESCE(p_request_id, 'NO_REQUEST_ID'), v_resolved_location_id, v_location_name;
|
|
END IF;
|
|
|
|
-- Resolve temporary 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
|
|
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;
|
|
|
|
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;
|
|
|
|
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
|
|
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
|
|
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
|
|
IF v_item.entity_id IS NULL THEN
|
|
RAISE EXCEPTION 'Update action requires entity_id';
|
|
END IF;
|
|
|
|
IF v_item.item_type = 'park' THEN
|
|
-- ========================================================================
|
|
-- CRITICAL FIX: Create location with name field for updates too
|
|
-- ========================================================================
|
|
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
|
|
v_location_name := COALESCE(
|
|
v_item.park_location_display_name,
|
|
v_item.park_location_name,
|
|
CONCAT_WS(', ',
|
|
NULLIF(v_item.park_location_city, ''),
|
|
NULLIF(v_item.park_location_state, ''),
|
|
NULLIF(v_item.park_location_country, '')
|
|
)
|
|
);
|
|
|
|
INSERT INTO locations (name, country, state_province, city, street_address, postal_code, latitude, longitude, timezone)
|
|
VALUES (
|
|
v_location_name,
|
|
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 % (name: %) for park update',
|
|
COALESCE(p_request_id, 'NO_REQUEST_ID'), v_resolved_location_id, v_location_name;
|
|
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;
|
|
v_entity_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;
|
|
v_entity_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.card_image_id,
|
|
updated_at = now()
|
|
WHERE id = v_item.entity_id;
|
|
v_entity_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;
|
|
v_entity_id := v_item.entity_id;
|
|
|
|
ELSIF v_item.item_type = 'photo' THEN
|
|
UPDATE entity_photos SET title = v_item.photo_title, updated_at = now()
|
|
WHERE id = v_item.entity_id;
|
|
v_entity_id := v_item.entity_id;
|
|
|
|
ELSE
|
|
RAISE EXCEPTION 'Unknown item type for update: %', v_item.item_type;
|
|
END IF;
|
|
|
|
ELSE
|
|
RAISE EXCEPTION 'Unknown action type: %', v_item.action_type;
|
|
END IF;
|
|
|
|
UPDATE submission_items SET approved_entity_id = v_entity_id, approved_at = now(), status = 'approved'
|
|
WHERE id = v_item.id;
|
|
|
|
v_approval_results := array_append(v_approval_results, jsonb_build_object(
|
|
'item_id', v_item.id, 'status', 'approved', 'entity_id', v_entity_id
|
|
));
|
|
v_some_approved := TRUE;
|
|
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RAISE WARNING 'Failed to process item %: % - %', v_item.id, SQLERRM, SQLSTATE;
|
|
v_approval_results := array_append(v_approval_results, jsonb_build_object(
|
|
'item_id', v_item.id, 'status', 'failed', 'error', SQLERRM
|
|
));
|
|
v_all_approved := FALSE;
|
|
RAISE;
|
|
END;
|
|
END LOOP;
|
|
|
|
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,
|
|
resolved_at = CASE WHEN v_all_approved THEN now() ELSE NULL END,
|
|
reviewer_id = p_moderator_id,
|
|
reviewed_at = now()
|
|
WHERE id = p_submission_id;
|
|
|
|
IF p_trace_id IS NOT NULL THEN
|
|
RAISE NOTICE 'SPAN: {"spanId": "%", "traceId": "%", "name": "process_approval_transaction_rpc", "kind": "INTERNAL", "endTime": %, "attributes": {"items_processed": %, "final_status": "%"}}',
|
|
v_span_id, p_trace_id, EXTRACT(EPOCH FROM clock_timestamp()) * 1000, v_items_processed, v_final_status;
|
|
END IF;
|
|
|
|
RETURN jsonb_build_object(
|
|
'success', v_all_approved,
|
|
'status', v_final_status,
|
|
'items_processed', v_items_processed,
|
|
'results', v_approval_results,
|
|
'duration_ms', EXTRACT(EPOCH FROM (clock_timestamp() - v_start_time)) * 1000
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON FUNCTION process_approval_transaction TO authenticated;
|
|
|
|
COMMENT ON FUNCTION process_approval_transaction IS
|
|
'✅ FIXED 2025-11-12: Now properly creates location records with name field during park approval/update.
|
|
This prevents parks from being created with NULL location_id values due to silent INSERT failures.'; |