create view public.v_customer_sms_history
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, contract_number,
             contract_status, sms_cdr_id, from_number, to_number, sent_at, sms_type, direction, charge_amount,
             is_roaming, network_country)
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,
       con.status                                                           AS contract_status,
       sms.sms_cdr_id,
       sms.source_msisdn                                                    AS from_number,
       sms.destination_msisdn                                               AS to_number,
       sms.event_time                                                       AS sent_at,
       sms.sms_type,
       sms.direction,
       sms.charge_amount,
       CASE
           WHEN sms.roaming_partner_id IS NOT NULL THEN true
           ELSE false
           END                                                              AS is_roaming,
       COALESCE(rp.country, 'Home'::text)                                   AS network_country
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
         LEFT JOIN roaming_partners rp ON rp.roaming_partner_id = sms.roaming_partner_id;

alter table public.v_customer_sms_history
    owner to postgres;