Advanced: 07 Get customer primary address coverage.sql

CREATE OR REPLACE FUNCTION public.get_customer_primary_address_coverage(
    p_customer_id bigint
)
RETURNS TABLE
(
    customer_id bigint,
    address_id bigint,
    full_address text,
    site_code text,
    tower_code text,
    sector_label text,
    technology_name text,
    signal_quality_score numeric,
    distance_to_site_m numeric
)
LANGUAGE sql
STABLE
AS $$
    SELECT
        c.customer_id,
        ca.address_id,
        concat_ws(', ', ca.street, ca.city, ca.country) AS full_address,
        ns.site_code,
        ct.tower_code,
        ts.sector_label,
        nt.technology_name,
        cz.signal_quality_score,
        round(
            ST_Distance(ca.location::geography, ns.location::geography)::numeric,
            1
        ) AS distance_to_site_m
    FROM public.customers c
    JOIN public.customer_addresses ca
      ON ca.customer_id = c.customer_id
     AND ca.is_primary
    JOIN public.coverage_zones cz
      ON cz.coverage_area IS NOT NULL
     AND ca.location IS NOT NULL
     AND ST_Covers(cz.coverage_area, ca.location)
    JOIN public.tower_sectors ts ON ts.sector_id = cz.sector_id
    JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
    JOIN public.network_sites ns ON ns.site_id = ct.site_id
    JOIN public.network_technologies nt
      ON nt.technology_id = ts.technology_id
    WHERE c.customer_id = p_customer_id
      AND c.status = 'active'
      AND ns.status = 'active'
      AND ct.status = 'active'
      AND ts.status = 'active'
    ORDER BY cz.signal_quality_score DESC NULLS LAST,
             distance_to_site_m;
$$;