Advanced: 03 Site coverage summary.sql

-- Management summary and QGIS point layer. Geometry: location.
CREATE OR REPLACE VIEW public.v_site_coverage_summary AS
WITH coverage_stats AS
(
    SELECT
        ns.site_id,
        count(DISTINCT ts.sector_id) AS sector_count,
        count(DISTINCT cz.coverage_zone_id) AS coverage_zone_count,
        round(avg(cz.signal_quality_score), 2) AS avg_signal_quality,
        round(
            (
                ST_Area(ST_Union(cz.coverage_area)::geography) / 1000000.0
            )::numeric,
            2
        ) AS coverage_area_sq_km
    FROM public.network_sites ns
    LEFT JOIN public.cell_towers ct ON ct.site_id = ns.site_id
    LEFT JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
    LEFT JOIN public.coverage_zones cz ON cz.sector_id = ts.sector_id
    GROUP BY ns.site_id
),
outage_stats AS
(
    SELECT site_id, count(*) AS open_outage_count
    FROM public.outages
    WHERE status = 'open'
    GROUP BY site_id
)
SELECT
    ns.site_id,
    ns.site_code,
    ns.site_name,
    ns.region,
    ns.status,
    ns.latitude,
    ns.longitude,
    ns.location,
    coalesce(cs.sector_count, 0) AS sector_count,
    coalesce(cs.coverage_zone_count, 0) AS coverage_zone_count,
    cs.avg_signal_quality,
    cs.coverage_area_sq_km,
    coalesce(os.open_outage_count, 0) AS open_outage_count
FROM public.network_sites ns
LEFT JOIN coverage_stats cs ON cs.site_id = ns.site_id
LEFT JOIN outage_stats os ON os.site_id = ns.site_id;