mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 04:51:11 -05:00
054348b9c4
Apply migrations and backfill scripts for location data and approval flow, including: - Implemented backfill_park_locations function to populate missing park locations from approved submissions - Adjusted process_approval_transaction to properly handle location creation and linking - Added admin-triggered backfill trigger point and related migration scaffolding for execution - Created edge-function and admin UI hook to run backfill as needed - Ensure search_path and relational data usage fixes for stability
77 lines
2.0 KiB
PL/PgSQL
77 lines
2.0 KiB
PL/PgSQL
-- Function to backfill missing park locations from submission data
|
|
CREATE OR REPLACE FUNCTION backfill_park_locations()
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_parks_updated INTEGER := 0;
|
|
v_locations_created INTEGER := 0;
|
|
v_park RECORD;
|
|
v_submission RECORD;
|
|
v_location_id UUID;
|
|
BEGIN
|
|
-- Find parks without locations that have approved submissions with location data
|
|
FOR v_park IN
|
|
SELECT DISTINCT p.id, p.name, p.slug
|
|
FROM parks p
|
|
WHERE p.location_id IS NULL
|
|
LOOP
|
|
-- Find the most recent approved submission for this park with location data
|
|
SELECT
|
|
psl.country,
|
|
psl.state_province,
|
|
psl.city,
|
|
psl.street_address,
|
|
psl.postal_code,
|
|
psl.latitude,
|
|
psl.longitude
|
|
INTO v_submission
|
|
FROM park_submissions ps
|
|
JOIN park_submission_locations psl ON ps.id = psl.park_submission_id
|
|
WHERE ps.park_id = v_park.id
|
|
AND ps.status = 'approved'
|
|
AND psl.country IS NOT NULL
|
|
ORDER BY ps.created_at DESC
|
|
LIMIT 1;
|
|
|
|
-- If we found location data, create a location record
|
|
IF FOUND THEN
|
|
INSERT INTO locations (
|
|
country,
|
|
state_province,
|
|
city,
|
|
street_address,
|
|
postal_code,
|
|
latitude,
|
|
longitude
|
|
) VALUES (
|
|
v_submission.country,
|
|
v_submission.state_province,
|
|
v_submission.city,
|
|
v_submission.street_address,
|
|
v_submission.postal_code,
|
|
v_submission.latitude,
|
|
v_submission.longitude
|
|
)
|
|
RETURNING id INTO v_location_id;
|
|
|
|
-- Update the park with the new location
|
|
UPDATE parks
|
|
SET location_id = v_location_id
|
|
WHERE id = v_park.id;
|
|
|
|
v_parks_updated := v_parks_updated + 1;
|
|
v_locations_created := v_locations_created + 1;
|
|
|
|
RAISE NOTICE 'Backfilled location for park: % (id: %)', v_park.name, v_park.id;
|
|
END IF;
|
|
END LOOP;
|
|
|
|
RETURN jsonb_build_object(
|
|
'success', true,
|
|
'parks_updated', v_parks_updated,
|
|
'locations_created', v_locations_created
|
|
);
|
|
END;
|
|
$$; |