CREATE OR REPLACE FUNCTION public.fn_get_customer_usage_between(
    p_customer_id bigint,
    p_start_date date,
    p_end_date date
)
RETURNS TABLE (
    subscription_id bigint,
    subscription_number text,
    call_minutes numeric,
    sms_count bigint,
    data_gb numeric,
    usage_charges numeric
)
LANGUAGE sql
STABLE
AS $$
    SELECT s.subscription_id,
           s.subscription_number,
           ROUND(SUM(u.total_call_seconds)::numeric / 60.0, 2),
           SUM(u.total_sms_count)::bigint,
           ROUND(SUM(u.total_data_mb) / 1024.0, 3),
           SUM(u.total_charge_amount)
    FROM public.accounts a
    JOIN public.subscriptions s ON s.account_id = a.account_id
    JOIN public.usage_aggregates_daily u ON u.subscription_id = s.subscription_id
    WHERE a.customer_id = p_customer_id
      AND u.usage_date BETWEEN p_start_date AND p_end_date
    GROUP BY s.subscription_id, s.subscription_number
    ORDER BY s.subscription_id;
$$;