sql/queries/japan_routes.sql

-- $transmitters is a list of {id, latitude, longitude} structs for the operators.
-- It identifies attributed Japanese transmitters, not a configured route list.
-- The caller loads Spatial. The map supplies all groups in one startup query.
-- Only publications in the extract's own Part I count; older linked records and
-- later Part II appearances must not extend the notification chronology.
WITH notifications AS (
    SELECT DISTINCT
        attribution.transmitter.id AS competitor,
        r.source_issue,
        r.terrakey,
        r.publication_date,
        t.intent,
        UPPER(rx.site_name) AS destination_source_label,
        rx.country AS destination_country,
        rx.latitude AS destination_latitude,
        rx.longitude AS destination_longitude
    FROM hf_records AS r
    JOIN hf_endpoints AS tx USING (source_issue, terrakey)
    JOIN hf_endpoints AS rx USING (source_issue, terrakey)
    JOIN UNNEST($transmitters) AS attribution(transmitter)
        ON ABS(tx.latitude - attribution.transmitter.latitude) < 1e-8
        AND ABS(tx.longitude - attribution.transmitter.longitude) < 1e-8
    JOIN fxm_terra AS t
        ON t.source_issue = r.source_issue
        AND CAST(t.terrakey AS BIGINT) = r.terrakey
    WHERE tx.relationship = 'primary'
        AND tx.role = 'TX'
        AND tx.country = 'J'
        AND rx.relationship = 'associated_rx'
        AND EXISTS (
            SELECT 1
            FROM fxm_pub_hist AS h
            JOIN pub AS p
                ON p.source_issue = h.source_issue
                AND p.pub_prefix = h.pub_prefix
                AND p.pub_no = h.pub_no
                AND p.addendum IS NOT DISTINCT FROM h.addendum
                AND p.corrigendum IS NOT DISTINCT FROM h.corrigendum
            WHERE h.source_issue = r.source_issue
                AND CAST(h.terrakey AS BIGINT) = r.terrakey
                AND h.pub_prefix = 'NTFD_RR'
                AND h.pub_part = '1'
                AND CAST(p.circ_no AS INTEGER) = r.source_issue
        )
)
SELECT
    competitor,
    destination_country,
    destination_latitude,
    destination_longitude,
    ARG_MAX(destination_source_label, (source_issue, terrakey)) AS destination_source_label,
    LIST_SORT(LIST(DISTINCT STRUCT_PACK(
        issue := source_issue,
        date := CAST(publication_date AS VARCHAR),
        intent := intent
    ))) AS publications
FROM notifications
GROUP BY competitor, destination_country, destination_latitude, destination_longitude
ORDER BY competitor, destination_country, destination_latitude, destination_longitude;