-- Pairs coverage zones from different sites and maps only their shared area.
-- QGIS polygon layer. Unique key: first_zone_id + second_zone_id.
CREATE OR REPLACE VIEW public.v_coverage_overlap_analysis AS
WITH overlaps AS
(
    SELECT
        first_zone.coverage_zone_id AS first_zone_id,
        first_zone.site_id AS first_site_id,
        first_zone.site_code AS first_site_code,
        first_zone.sector_label AS first_sector,
        second_zone.coverage_zone_id AS second_zone_id,
        second_zone.site_id AS second_site_id,
        second_zone.site_code AS second_site_code,
        second_zone.sector_label AS second_sector,
        ST_Multi(
            ST_CollectionExtract(
                ST_Intersection(
                    first_zone.coverage_area,
                    second_zone.coverage_area
                ),
                3
            )
        )::geometry(MultiPolygon, 4326) AS overlap_area
    FROM public.v_network_coverage_map first_zone
    JOIN public.v_network_coverage_map second_zone
      ON first_zone.coverage_zone_id < second_zone.coverage_zone_id
     AND first_zone.site_id <> second_zone.site_id
     AND first_zone.coverage_area && second_zone.coverage_area
     AND ST_Intersects(first_zone.coverage_area, second_zone.coverage_area)
)
SELECT
    first_zone_id,
    first_site_id,
    first_site_code,
    first_sector,
    second_zone_id,
    second_site_id,
    second_site_code,
    second_sector,
    round(
        (ST_Area(overlap_area::geography) / 1000000.0)::numeric,
        3
    ) AS overlap_area_sq_km,
    overlap_area
FROM overlaps
WHERE NOT ST_IsEmpty(overlap_area);