-- QGIS polygon layer. Unique key: coverage_zone_id; geometry: coverage_area.
CREATE OR REPLACE VIEW public.v_network_coverage_map AS
SELECT
    cz.coverage_zone_id,
    ns.site_id,
    ns.site_code,
    ns.site_name,
    ns.region,
    ns.status AS site_status,
    ct.tower_id,
    ct.tower_code,
    ts.sector_id,
    ts.sector_label,
    ts.azimuth,
    ts.beamwidth,
    ts.frequency_band,
    nt.technology_name,
    nt.generation,
    cz.coverage_type,
    cz.coverage_radius_m,
    cz.signal_quality_score,
    cz.last_measured_at,
    ns.latitude AS site_latitude,
    ns.longitude AS site_longitude,
    cz.coverage_area
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
JOIN public.network_technologies nt
  ON nt.technology_id = ts.technology_id;