DatabaseCreation: Customer device SIM history.sql

create view public.v_customer_device_sim_history
            (customer_id, customer_name, email, customer_type, account_number, subscription_number, plan_name,
             contract_number, device_active_from, device_active_to, device_assignment_status, manufacturer,
             device_model, device_type, sim_active_from, sim_active_to, sim_assignment_status, phone_number, sim_type,
             sim_card_status)
as
SELECT c.customer_id,
       COALESCE((c.first_name || ' '::text) || c.last_name, c.company_name) AS customer_name,
       c.email,
       c.customer_type,
       a.account_number,
       s.subscription_number,
       p.plan_name,
       con.contract_number,
       da.assigned_from                                                     AS device_active_from,
       da.assigned_to                                                       AS device_active_to,
       CASE
           WHEN da.device_assignment_id IS NULL THEN 'no_operator_device'::text
           WHEN da.assigned_to IS NULL THEN 'active_device_assignment'::text
           ELSE 'historical_device_assignment'::text
           END                                                              AS device_assignment_status,
       dev.manufacturer,
       dev.model                                                            AS device_model,
       dev.device_type,
       ssh.start_date                                                       AS sim_active_from,
       ssh.end_date                                                         AS sim_active_to,
       CASE
           WHEN ssh.sim_card_subscription_history_id IS NULL THEN 'no_sim_history'::text
           WHEN ssh.end_date IS NULL THEN 'active_sim_assignment'::text
           ELSE 'historical_sim_assignment'::text
           END                                                              AS sim_assignment_status,
       sc.msisdn                                                            AS phone_number,
       sc.sim_type,
       sc.status                                                            AS sim_card_status
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
         LEFT JOIN device_assignments da ON da.subscription_id = s.subscription_id
         LEFT JOIN devices dev ON dev.device_id = da.device_id
         LEFT JOIN sim_card_subscription_history ssh ON ssh.subscription_id = s.subscription_id
         LEFT JOIN sim_cards sc ON sc.sim_id = ssh.sim_id;

alter table public.v_customer_device_sim_history
    owner to postgres;