DatabaseCreation: Customer call history.sql

create view public.v_customer_call_history
            (customer_id, customer_name, email, account_number, subscription_number, plan_name, contract_number,
             contract_status, call_cdr_id, from_number, to_number, call_started_at, call_ended_at, duration_minutes,
             call_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,
       cdr.call_cdr_id,
       cdr.originating_msisdn                                               AS from_number,
       cdr.destination_msisdn                                               AS to_number,
       cdr.event_start_time                                                 AS call_started_at,
       cdr.event_end_time                                                   AS call_ended_at,
       round(cdr.duration_seconds::numeric / 60.0, 2)                       AS duration_minutes,
       cdr.call_type,
       cdr.direction,
       cdr.charge_amount,
       CASE
           WHEN cdr.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_calls cdr ON cdr.subscription_id = s.subscription_id
         LEFT JOIN roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id;

alter table public.v_customer_call_history
    owner to postgres;