create view public.v_customer_roaming_activity
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, contract_number,
             roaming_country, usage_type, event_time, call_duration_minutes, data_used_gb, direction, charge_amount)
as
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       con.contract_number,
       rp.country                                                           AS roaming_country,
       'Call'::text                                                         AS usage_type,
       cdr.event_start_time                                                 AS event_time,
       round(cdr.duration_seconds::numeric / 60.0, 2)                       AS call_duration_minutes,
       NULL::numeric                                                        AS data_used_gb,
       cdr.direction,
       cdr.charge_amount
FROM customers c
         JOIN accounts a ON a.customer_id = c.customer_id
         JOIN subscriptions s ON s.account_id = a.account_id
         JOIN plans p ON p.plan_id = s.plan_id
         LEFT JOIN contracts con ON con.contract_id = s.contract_id
         JOIN usage_cdr_calls cdr ON cdr.subscription_id = s.subscription_id
         JOIN roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id
UNION ALL
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       con.contract_number,
       rp.country                                                           AS roaming_country,
       'SMS'::text                                                          AS usage_type,
       sms.event_time,
       NULL::numeric                                                        AS call_duration_minutes,
       NULL::numeric                                                        AS data_used_gb,
       sms.direction,
       sms.charge_amount
FROM customers c
         JOIN accounts a ON a.customer_id = c.customer_id
         JOIN subscriptions s ON s.account_id = a.account_id
         JOIN plans p ON p.plan_id = s.plan_id
         LEFT JOIN contracts con ON con.contract_id = s.contract_id
         JOIN usage_cdr_sms sms ON sms.subscription_id = s.subscription_id
         JOIN roaming_partners rp ON rp.roaming_partner_id = sms.roaming_partner_id
UNION ALL
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       con.contract_number,
       rp.country                                                           AS roaming_country,
       'Data'::text                                                         AS usage_type,
       d.session_start                                                      AS event_time,
       NULL::numeric                                                        AS call_duration_minutes,
       round(d.data_used_mb / 1024.0, 3)                                    AS data_used_gb,
       NULL::text                                                           AS direction,
       d.charge_amount
FROM customers c
         JOIN accounts a ON a.customer_id = c.customer_id
         JOIN subscriptions s ON s.account_id = a.account_id
         JOIN plans p ON p.plan_id = s.plan_id
         LEFT JOIN contracts con ON con.contract_id = s.contract_id
         JOIN usage_cdr_data d ON d.subscription_id = s.subscription_id
         JOIN roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id;

alter table public.v_customer_roaming_activity
    owner to postgres;