DROP VIEW IF EXISTS public.v_customer_data_usage_history;

CREATE VIEW public.v_customer_data_usage_history AS
SELECT
    c.customer_id,
    CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
    a.account_number,
    s.subscription_number,
    p.plan_name,
    d.data_cdr_id,
    d.session_start,
    d.session_end,
    ROUND(EXTRACT(EPOCH FROM (d.session_end - d.session_start))::numeric / 60.0, 2) AS session_duration_minutes,
    d.data_used_mb,
    ROUND(d.data_used_mb / 1024.0, 4) AS data_used_gb,
    d.apn,
    d.ip_address,
    d.charge_amount,
    d.roaming_partner_id IS NOT NULL AS is_roaming,
    COALESCE(rp.country, 'North Macedonia') AS network_country,
    ns.region AS network_region,
    ns.site_code,
    ts.sector_label,
    ts.frequency_band,
    nt.generation AS network_generation
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
JOIN public.subscriptions s ON s.account_id = a.account_id
JOIN public.plans p ON p.plan_id = s.plan_id
JOIN public.usage_cdr_data d ON d.subscription_id = s.subscription_id
LEFT JOIN public.roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id
LEFT JOIN public.tower_sectors ts ON ts.sector_id = d.sector_id
LEFT JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
LEFT JOIN public.network_sites ns ON ns.site_id = ct.site_id
LEFT JOIN public.network_technologies nt ON nt.technology_id = ts.technology_id;