Advanced: 08 Uncovered primary addresses.sql

-- Active customers whose primary address is outside every active sector polygon.
-- QGIS point layer. Unique key: address_id; geometry: customer_location.
CREATE OR REPLACE VIEW public.v_uncovered_primary_addresses 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,
    ca.city,
    ca.country,
    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
 AND ca.location IS NOT NULL
WHERE c.status = 'active'
  AND NOT EXISTS
  (
      SELECT 1
      FROM public.coverage_zones cz
      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
      WHERE cz.coverage_area IS NOT NULL
        AND ns.status = 'active'
        AND ct.status = 'active'
        AND ts.status = 'active'
        AND ST_Covers(cz.coverage_area, ca.location)
  );