mirror of
https://github.com/pacnpal/thrilltrack-explorer.git
synced 2025-12-20 07:11:12 -05:00
617e079c5a
Run backfill to populate lagoon location and fix submission join - Re-run and refine backfill to ensure Lagoon and similar parks receive proper location data by correcting join via slug and handling name/display_name in location inserts.
47 lines
2.0 KiB
PL/PgSQL
47 lines
2.0 KiB
PL/PgSQL
-- Fix backfill_park_locations to join via slug instead of park_id
|
|
CREATE OR REPLACE FUNCTION backfill_park_locations()
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
v_parks_updated INTEGER := 0;
|
|
v_locations_created INTEGER := 0;
|
|
v_park RECORD;
|
|
v_submission RECORD;
|
|
v_location_id UUID;
|
|
v_location_name TEXT;
|
|
BEGIN
|
|
FOR v_park IN
|
|
SELECT DISTINCT p.id, p.name, p.slug
|
|
FROM parks p
|
|
WHERE p.location_id IS NULL
|
|
LOOP
|
|
SELECT psl.name, psl.display_name, psl.country, psl.state_province, psl.city, psl.street_address,
|
|
psl.postal_code, psl.latitude, psl.longitude, psl.timezone
|
|
INTO v_submission
|
|
FROM park_submissions ps
|
|
JOIN park_submission_locations psl ON ps.id = psl.park_submission_id
|
|
WHERE ps.slug = v_park.slug AND ps.status = 'approved' AND (psl.country IS NOT NULL OR psl.city IS NOT NULL)
|
|
ORDER BY ps.created_at DESC LIMIT 1;
|
|
|
|
IF FOUND THEN
|
|
v_location_name := COALESCE(v_submission.name, v_submission.display_name,
|
|
CONCAT_WS(', ', NULLIF(v_submission.city, ''), NULLIF(v_submission.state_province, ''), NULLIF(v_submission.country, '')));
|
|
|
|
INSERT INTO locations (name, display_name, country, state_province, city, street_address, postal_code, latitude, longitude, timezone)
|
|
VALUES (v_location_name, COALESCE(v_submission.display_name, v_location_name), v_submission.country, v_submission.state_province,
|
|
v_submission.city, v_submission.street_address, v_submission.postal_code, v_submission.latitude, v_submission.longitude, v_submission.timezone)
|
|
RETURNING id INTO v_location_id;
|
|
|
|
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 % (name: %) for park: % (id: %)', v_location_id, v_location_name, 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;
|
|
$$; |