DatabaseCreation: 10 Customer device SIM timeline.sql

DROP VIEW IF EXISTS public.v_customer_device_sim_timeline;

CREATE VIEW public.v_customer_device_sim_timeline 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,
       'device'::text AS asset_type, d.device_id AS asset_id, d.imei AS asset_identifier,
       d.manufacturer, d.model, d.device_type, NULL::text AS sim_type,
       da.assigned_from AS period_start, da.assigned_to AS period_end,
       da.assignment_status AS lifecycle_status
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.device_assignments da ON da.subscription_id = s.subscription_id
JOIN public.devices d ON d.device_id = da.device_id
UNION ALL
SELECT c.customer_id,
       CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END,
       a.account_number, s.subscription_number,
       'sim', sc.sim_id, sc.msisdn, NULL::text, NULL::text, NULL::text, sc.sim_type,
       ssh.start_date, ssh.end_date,
       CASE WHEN ssh.end_date IS NULL THEN 'active' ELSE 'historical' END
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.sim_card_subscription_history ssh ON ssh.subscription_id = s.subscription_id
JOIN public.sim_cards sc ON sc.sim_id = ssh.sim_id;


COMMENT ON VIEW public.v_customer_device_sim_timeline IS
'Unified device and SIM event timeline. UNION ALL avoids a device by SIM multiplication.';