Advanced: 05 Open outage customer impact.sql

-- Customers whose primary address lies in a sector belonging to an open-outage site.
-- QGIS point layer. Geometry: customer_location.
CREATE OR REPLACE VIEW public.v_open_outage_customer_impact AS
SELECT
    o.outage_id,
    o.site_id,
    ns.site_code,
    ns.site_name,
    o.outage_type,
    o.start_time,
    c.customer_id,
    ca.address_id,
    concat_ws(', ', ca.street, ca.city, ca.country) AS customer_address,
    count(DISTINCT cz.coverage_zone_id) AS affected_coverage_zones,
    round(
        ST_Distance(ca.location::geography, ns.location::geography)::numeric,
        1
    ) AS distance_to_site_m,
    ca.location AS customer_location
FROM public.outages o
JOIN public.network_sites ns ON ns.site_id = o.site_id
JOIN public.cell_towers ct ON ct.site_id = ns.site_id
JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
JOIN public.coverage_zones cz ON cz.sector_id = ts.sector_id
JOIN public.customer_addresses ca
  ON ca.is_primary
 AND ca.location IS NOT NULL
 AND cz.coverage_area IS NOT NULL
 AND ST_Covers(cz.coverage_area, ca.location)
JOIN public.customers c
  ON c.customer_id = ca.customer_id
 AND c.status = 'active'
WHERE o.status = 'open'
GROUP BY
    o.outage_id,
    o.site_id,
    ns.site_code,
    ns.site_name,
    o.outage_type,
    o.start_time,
    c.customer_id,
    ca.address_id,
    ca.street,
    ca.city,
    ca.country,
    ca.location,
    ns.location;