-- 030_opportunity_map_layer_url.sql
-- Opportunity module alignment — corrective migration
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- Re-points the registered map layers' REQUEST URL at the `opportunity` schema.
--
-- WHY THIS IS NEEDED ON TOP OF 025. public.map_layers stores the relation three times:
--
--   database_schema  'opportunity'   ← read by map_layer_manager.php:202-203 (save path)
--   database_table   'vw_opportunity_routes'
--   url              'fn/map_get_v2.php?geotable=prospector.vw_opportunity_routes&…'
--                                                 ^^^^^^^^^^ the fetch path, hardcoded
--
-- Migration 025 updated the first two and never looked at the third, so the editor kept
-- requesting prospector.vw_opportunity_* — relations 025 had just dropped — and
-- map_get_v2.php answered "Invalid table name." (map_get_v2.php:76). The layers were
-- registered, enabled and completely empty: no error on screen, just no routes and no
-- equipment on the map.
--
-- Scoped to the relations that actually moved (`prospector.opportunity…` and
-- `prospector.vw_opportunity…`). The rest of the prospector schema — network_points,
-- network_cables, pia_blockages and the other planner layers — is deliberately untouched.
--
-- ASSERTS ITS OWN EFFECT. 025's UPDATE matched no rows and reported success, which is how
-- this went unnoticed; any migration that repairs data should fail loudly when it repairs
-- nothing.

DO $$
DECLARE
    r          record;
    v_fixed    integer;
    v_stale    integer;
BEGIN
    FOR r IN
        SELECT layer_id, layer_name, database_schema, database_table, url
          FROM public.map_layers
         WHERE url ILIKE '%prospector.opportunity%'
            OR url ILIKE '%prospector.vw_opportunity%'
         ORDER BY layer_id
    LOOP
        RAISE NOTICE 'map_layers % (%): url=%', r.layer_id, r.layer_name, r.url;
    END LOOP;

    UPDATE public.map_layers
       SET url = replace(replace(url,
               'prospector.vw_opportunity', 'opportunity.vw_opportunity'),
               'prospector.opportunity',    'opportunity.opportunity')
     WHERE url ILIKE '%prospector.opportunity%'
        OR url ILIKE '%prospector.vw_opportunity%';

    GET DIAGNOSTICS v_fixed = ROW_COUNT;

    SELECT count(*) INTO v_stale
      FROM public.map_layers
     WHERE url ILIKE '%prospector.opportunity%'
        OR url ILIKE '%prospector.vw_opportunity%';

    RAISE NOTICE 'map_layers urls rewritten: %; still referencing prospector opportunity relations: %',
        v_fixed, v_stale;

    IF v_stale > 0 THEN
        RAISE EXCEPTION 'the rewrite left % row(s) still pointing at prospector opportunity relations', v_stale;
    END IF;

    IF v_fixed = 0 THEN
        RAISE EXCEPTION 'no map_layers url referenced a prospector opportunity relation — expected the two rows for Opportunity Routes and Opportunity Equipment; check whether they were already fixed by hand';
    END IF;
END $$;

-- Belt and braces on the other two columns, which 025 was supposed to set. Keyed on the
-- table name alone this time: 025 required database_schema = 'prospector' as well, and a
-- two-condition WHERE is exactly what let it match nothing in silence.
UPDATE public.map_layers
   SET database_schema = 'opportunity'
 WHERE database_table IN ('vw_opportunity_routes', 'vw_opportunity_network_points')
   AND COALESCE(database_schema, '') <> 'opportunity';
