Advanced: 04 Customer coverage detail.sql

-- One row for every active sector covering an active customer's primary address.
-- QGIS point layer. Unique key can be customer_id + coverage_zone_id.
CREATE OR REPLACE VIEW public.v_customer_coverage_detail AS
SELECT
    c.customer_id,
    concat_ws(' ', c.first_name, c.last_name) AS customer_name,
    ca.address_id,
    concat_ws(', ', ca.street, ca.city, ca.country) AS full_address,
    cz.coverage_zone_id,
    ns.site_id,
    ns.site_code,
    ns.site_name,
    ct.tower_code,
    ts.sector_id,
    ts.sector_label,
    nt.technology_name,
    nt.generation,
    cz.signal_quality_score,
    round(
        ST_Distance(ca.location::geography, ns.location::geography)::numeric,
        1
    ) AS distance_to_site_m,
    ca.location AS customer_location
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 ca.location IS NOT NULL
 AND cz.coverage_area 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.status = 'active'
  AND ns.status = 'active'
  AND ct.status = 'active'
  AND ts.status = 'active';