| 1 | DROP VIEW IF EXISTS public.v_customer_call_history;
|
|---|
| 2 |
|
|---|
| 3 | CREATE VIEW public.v_customer_call_history AS
|
|---|
| 4 | SELECT
|
|---|
| 5 | c.customer_id,
|
|---|
| 6 | CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
|
|---|
| 7 | a.account_number,
|
|---|
| 8 | s.subscription_number,
|
|---|
| 9 | p.plan_name,
|
|---|
| 10 | cdr.call_cdr_id,
|
|---|
| 11 | cdr.originating_msisdn AS from_number,
|
|---|
| 12 | cdr.destination_msisdn AS to_number,
|
|---|
| 13 | cdr.event_start_time AS call_started_at,
|
|---|
| 14 | cdr.event_end_time AS call_ended_at,
|
|---|
| 15 | ROUND(cdr.duration_seconds::numeric / 60.0, 2) AS duration_minutes,
|
|---|
| 16 | cdr.call_type,
|
|---|
| 17 | cdr.direction,
|
|---|
| 18 | cdr.charge_amount,
|
|---|
| 19 | cdr.fraud_score,
|
|---|
| 20 | cdr.roaming_partner_id IS NOT NULL AS is_roaming,
|
|---|
| 21 | COALESCE(rp.country, 'North Macedonia') AS network_country,
|
|---|
| 22 | ns.region AS network_region,
|
|---|
| 23 | ns.site_code,
|
|---|
| 24 | ct.tower_code,
|
|---|
| 25 | ts.sector_label,
|
|---|
| 26 | ts.frequency_band,
|
|---|
| 27 | nt.generation AS network_generation
|
|---|
| 28 | FROM public.customers c
|
|---|
| 29 | JOIN public.accounts a ON a.customer_id = c.customer_id
|
|---|
| 30 | JOIN public.subscriptions s ON s.account_id = a.account_id
|
|---|
| 31 | JOIN public.plans p ON p.plan_id = s.plan_id
|
|---|
| 32 | JOIN public.usage_cdr_calls cdr ON cdr.subscription_id = s.subscription_id
|
|---|
| 33 | LEFT JOIN public.roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id
|
|---|
| 34 | LEFT JOIN public.tower_sectors ts ON ts.sector_id = cdr.sector_id
|
|---|
| 35 | LEFT JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
|
|---|
| 36 | LEFT JOIN public.network_sites ns ON ns.site_id = ct.site_id
|
|---|
| 37 | LEFT JOIN public.network_technologies nt ON nt.technology_id = ts.technology_id;
|
|---|
| 38 |
|
|---|
