CREATE OR REPLACE FUNCTION public.calculate_site_coverage_area_sq_km(
    p_site_id bigint
)
RETURNS numeric
LANGUAGE sql
STABLE
STRICT
AS $$
    SELECT round(
               (
                   ST_Area(ST_Union(cz.coverage_area)::geography)
                   / 1000000.0
               )::numeric,
               3
           )
    FROM public.cell_towers ct
    JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
    JOIN public.coverage_zones cz ON cz.sector_id = ts.sector_id
    WHERE ct.site_id = p_site_id
      AND cz.coverage_area IS NOT NULL;
$$;

COMMENT ON FUNCTION public.calculate_site_coverage_area_sq_km IS
    'Returns the unioned coverage area for one network site in square kilometres.';