= Индексирање и оптимизација на прашалници =

Во оваа фаза се оптимизирани десет прашалници што ги претставуваат најважните начини на користење на Telecom и GIS погледите. Тестовите се извршени директно врз основните табели за јасно да се измери влијанието на конкретниот индекс. Наведениот поглед ја користи истата пристапна шема, но не бил директен предмет на мерењето.

За секој прашалник прво е измерено времето без опционалниот индекс. Потоа планот е проверен со:

{{{
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
}}}

`Seq Scan` значи дека PostgreSQL чита голем дел или цела табела. `Rows Removed by Filter` покажува колку непотребни редови биле прочитани. По оптимизацијата, `Index Scan`, `Index Only Scan` или `Bitmap Index Scan` покажува дека базата пристапува само до потребните записи. `Index Cond` покажува кои услови навистина го користат индексот.

Кај составните B-tree индекси прва е колоната со точен услов, а втора е колоната со временски опсег. `INCLUDE` колоните не служат за пребарување, туку овозможуваат потребните вредности да се прочитаат директно од индексот. За точки, полигони и растојанија се користат GiST индекси.


== 1. Претплати по корисничка сметка ==

Поврзан поглед: `v_customer_subscription_overview`

{{{
SELECT count(*), max(subscription_id), max(plan_id), max(status)
FROM public.subscriptions
WHERE account_id = :account_id;
}}}

=== Пред оптимизацијата

Времето било **0.347 ms**. Без индекс, планот користи `Seq Scan` и го применува `account_id = ...` како Filter. Така се проверуваат и претплатите од другите сметки.

=== Избран индекс

{{{
CREATE INDEX idx_bench_subscriptions_account
ON public.subscriptions (account_id, subscription_id)
INCLUDE (plan_id, contract_id, subscription_number, status);
}}}

`account_id` е прв бидејќи е условот за пребарување. `subscription_id` е втор за организирање на претплатите на сметката. Другите колони се во INCLUDE бидејќи ги користи погледот, но не се филтри.

=== По оптимизацијата

Планот може да користи `Index Only Scan` со `Index Cond: account_id = ...`. Времето се намалило на **0.051 ms**, односно прашалникот е **6.80 пати побрз**.


== 2. Историја на повици по претплата и време ==

Поврзан поглед: `v_customer_call_history`

{{{
SELECT count(*), max(event_start_time), sum(duration_seconds)
FROM public.usage_cdr_calls
WHERE subscription_id = :subscription_id
  AND event_start_time >= TIMESTAMPTZ '2026-05-20 00:00:00+02'
  AND event_start_time <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
}}}

=== Пред оптимизацијата

Времето било **0.103 ms**. PostgreSQL ги проверува повиците и дополнително ги филтрира според претплатата и периодот.

=== Избран индекс

{{{
CREATE INDEX idx_bench_calls_subscription_time
ON public.usage_cdr_calls (subscription_id, event_start_time DESC);
}}}

`subscription_id` е прв бидејќи се бара една претплата. `event_start_time` е втор бидејќи потоа се ограничува временскиот период. По креирањето, двата услови може да се појават во `Index Cond`.

=== По оптимизацијата

Времето се намалило на **0.093 ms**, односно подобрување од **9.7%**. Разликата е мала поради малиот резултат, но индексот станува поважен со растењето на CDR табелата.


== 3. Интернет сесии по претплата и време ==

Поврзан поглед: `v_customer_data_usage_history`

{{{
SELECT count(*), max(session_start),
       sum(data_used_mb), sum(charge_amount)
FROM public.usage_cdr_data
WHERE subscription_id = :subscription_id
  AND session_start >= TIMESTAMPTZ '2026-05-20 00:00:00+02'
  AND session_start <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
}}}

=== Пред оптимизацијата

Времето било **0.152 ms**. Планот без индекс ги филтрира интернет сесиите по читањето на табелата.

=== Избран индекс

{{{
CREATE INDEX idx_bench_data_subscription_time
ON public.usage_cdr_data (subscription_id, session_start DESC);
}}}

Редоследот го следи прашалникот: точна претплата, а потоа временски опсег. Со индексот, двата услови може заедно да се користат во `Index Cond`.

=== По оптимизацијата

Времето се намалило на **0.141 ms**, односно подобрување од **7.2%**.


== 4. Дневна потрошувачка по претплата и датум ==

Поврзан поглед: `v_customer_daily_usage_summary`

{{{
SELECT count(*), sum(total_call_seconds), sum(total_sms_count),
       sum(total_data_mb), sum(total_charge_amount)
FROM public.usage_aggregates_daily
WHERE subscription_id = :subscription_id
  AND usage_date BETWEEN DATE '2026-05-20' AND DATE '2026-08-20';
}}}

=== Пред оптимизацијата

Времето било **0.180 ms**. Без индекс се проверуваат и дневни записи што не припаѓаат на избраната претплата или период.

=== Избран индекс

{{{
CREATE INDEX idx_bench_daily_subscription_date
ON public.usage_aggregates_daily (subscription_id, usage_date DESC);
}}}

`subscription_id` е колоната со еднаквост, а `usage_date` е временскиот опсег. Индексот го намалува бројот на редови што стигнуваат до агрегатните функции.

=== По оптимизацијата

Времето се намалило на **0.162 ms**, односно подобрување од **10.0%**.


== 5. CRM тикети по корисник и датум ==

Поврзан поглед: `v_customer_support_ticket_timeline`

{{{
SELECT count(*), max(created_at), max(priority), max(status)
FROM public.crm_tickets
WHERE customer_id = :customer_id
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+01'
  AND created_at <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
}}}

=== Пред оптимизацијата

Времето било **0.473 ms**. Планот чита повеќе тикети и потоа ги отстранува тие што не припаѓаат на избраниот корисник и период.

=== Избран индекс

{{{
CREATE INDEX idx_bench_tickets_customer_date
ON public.crm_tickets (customer_id, created_at DESC)
INCLUDE (account_id, subscription_id,
         assigned_employee_id, status, priority);
}}}

`customer_id` и `created_at` се индексни клучеви бидејќи се користат во WHERE. Другите колони се во INCLUDE бидејќи се потребни за timeline погледот. Така е можен `Index Only Scan`.

=== По оптимизацијата

Времето се намалило на **0.079 ms**, што е подобрување од **83.3%** и забрзување од **5.99 пати**.


== 6. Мрежни прекини по локација и време ==

Поврзан поглед: `v_network_outage_operations`

{{{
SELECT count(*), max(start_time), max(status)
FROM public.outages
WHERE site_id = :site_id
  AND start_time >= TIMESTAMPTZ '2025-01-01 00:00:00+01'
  AND start_time <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
}}}

=== Пред оптимизацијата

Времето било **0.071 ms**. Условите за локација и време се применуваат врз веќе прочитаните прекини.

=== Избран индекс

{{{
CREATE INDEX idx_bench_outages_site_date
ON public.outages (site_id, start_time DESC)
INCLUDE (alarm_id, status, outage_type, end_time);
}}}

`site_id` е прв затоа што се анализира една локација. `start_time` е втор за периодот. INCLUDE колоните ги содржат главните податоци од оперативниот поглед.

=== По оптимизацијата

Времето се намалило на **0.059 ms**, односно подобрување од **16.9%**.


== 7. Сообраќај на повици по сектор и време ==

Поврзан поглед: `v_sector_traffic_map`

{{{
SELECT count(*), max(event_start_time),
       sum(duration_seconds), sum(charge_amount)
FROM public.usage_cdr_calls
WHERE sector_id = :sector_id
  AND event_start_time >= TIMESTAMPTZ '2026-05-20 00:00:00+02'
  AND event_start_time <  TIMESTAMPTZ '2026-08-21 00:00:00+02';
}}}

=== Пред оптимизацијата

Времето било **50.042 ms**. EXPLAIN ANALYZE покажува `Seq Scan` врз големата CDR табела. Се читаат многу повици, а потоа се отстрануваат тие што не припаѓаат на избраниот сектор или период.

=== Избран индекс

{{{
CREATE INDEX idx_bench_calls_sector_time
ON public.usage_cdr_calls (sector_id, event_start_time)
INCLUDE (duration_seconds, charge_amount);
}}}

`sector_id` е прв поради точниот услов, а `event_start_time` е втор поради временскиот опсег. Вредностите за SUM се во INCLUDE. По додавањето, планот може да користи `Index Only Scan` и да ги чита само потребните повици.

=== По оптимизацијата

Времето се намалило на **0.627 ms**. Прашалникот е **79.81 пати побрз**, односно времето е намалено за **98.7%**. Ова е најголемото Telecom подобрување.


== 8. Кориснички адреси покриени со GIS полигон ==

Поврзан поглед: `v_customer_coverage_detail`

{{{
SELECT count(*)
FROM public.customer_addresses address
WHERE address.is_primary
  AND address.location IS NOT NULL
  AND EXISTS (
      SELECT 1
      FROM public.coverage_zones coverage
      WHERE coverage.coverage_area IS NOT NULL
        AND coverage.coverage_area && address.location
        AND ST_Covers(coverage.coverage_area, address.location)
  );
}}}

=== Пред оптимизацијата

Времето било **152.839 ms**. Без просторен индекс се проверуваат многу комбинации од адресни точки и полигони.

=== Избран индекс

{{{
CREATE INDEX idx_coverage_zones_area_gist
ON public.coverage_zones
USING gist (coverage_area);
}}}

GiST е избран затоа што прашалникот работи со полигони. Операторот `&&` преку индексот прво избира можни полигони, а `ST_Covers` ја прави точната проверка само врз тие кандидати.

=== По оптимизацијата

Времето се намалило на **149.056 ms**, односно подобрување од **2.5%**. Добивката е мала бидејќи ST_Covers сè уште мора да провери голем број адреси.


== 9. Најблиска активна мрежна локација ==

Поврзан поглед и функција: `v_network_site_points` и `find_nearest_network_sites`

Прашалникот ја наоѓа најблиската активна локација за 2 000 адреси. Растојанието се пресметува во метри со geography.

=== Пред оптимизацијата

Времето било **208.732 ms**. Без соодветен индекс PostgreSQL ги споредува и сортира мрежните локации за секоја адреса.

=== Избран индекс

{{{
CREATE INDEX idx_network_sites_location_geography_gist
ON public.network_sites
USING gist ((location::geography));
}}}

Индексот е врз `location::geography` бидејќи прашалникот го користи истиот израз. GiST го поддржува операторот `<->`, со кој директно се бара најблиската локација.

=== По оптимизацијата

Планот може да користи `Index Scan` подреден по растојание. Времето се намалило на **52.625 ms**, што е подобрување од **74.8%** и забрзување од **3.97 пати**.


== 10. Кориснички адреси во радиус од 2 km ==

{{{
SELECT count(*), max(address.address_id)
FROM public.customer_addresses address
JOIN public.network_sites site ON site.site_id = :site_id
WHERE address.location IS NOT NULL
  AND address.location && ST_Envelope(
      ST_Buffer(site.location::geography, 2000)::geometry
  )
  AND ST_DWithin(
      address.location::geography,
      site.location::geography,
      2000
  );
}}}

=== Пред оптимизацијата

Времето било **39.591 ms**. Без индекс PostgreSQL ја чита customer_addresses и ја проверува оддалеченоста за голем број адреси.

=== Избран индекс

{{{
CREATE INDEX idx_customer_addresses_location_gist
ON public.customer_addresses
USING gist (location);
}}}

GiST индексот го користи условот `location && ...` за да ги избере точките во приближната област. `ST_DWithin` потоа го проверува точното растојание само за тие кандидати. Во планот ова може да се појави како `Bitmap Index Scan`.

=== По оптимизацијата

Времето се намалило на **1.149 ms**. Прашалникот е **34.46 пати побрз**, односно времето е намалено за **97.1%**. Ова е најголемото GIS подобрување.


== Вкупен резултат ==

За десетте прашалници, збирот на медијаните се намалил од **452.530 ms** на **204.042 ms**. Вкупно се заштедени **248.488 ms**, што е намалување од **54.9%** и забрзување од **2.22 пати**.

Најголемо подобрување има кај сообраќајот по сектор и GIS пребарувањето на адреси во радиус од 2 km. Во двата случаи EXPLAIN ANALYZE покажува дека проблемот бил читање голем број непотребни редови, а индексот овозможува директен пристап до релевантното множество.

Индексите се избрани според реалните услови во прашалниците. Колоните во `Index Cond` се клучеви на индексот, колоните потребни само за резултатот се ставаат во INCLUDE, а за просторни операции се користи GiST. Така секој задржан индекс има конкретна намена и измерено подобрување.

== SQL дефиниции на поврзаните погледи и функции ==

Подолу е вметнат SQL кодот што претходно беше во посебните датотеки.

=== Customer Subscription Overview ===

{{{
DROP VIEW IF EXISTS public.v_customer_subscription_overview;

CREATE VIEW public.v_customer_subscription_overview AS
SELECT
    c.customer_id,
    CASE
        WHEN c.customer_type = 'business' THEN c.company_name || ' (' || c.first_name || ' ' || c.last_name || ')'
        ELSE c.first_name || ' ' || c.last_name
    END AS customer_name,
    c.customer_type,
    c.email,
    a.account_id,
    a.account_number,
    a.account_status,
    a.current_balance,
    bc.cycle_name AS billing_cycle,
    s.subscription_id,
    s.subscription_number,
    s.status AS subscription_status,
    s.activation_date,
    s.end_date,
    p.plan_name,
    p.monthly_fee,
    con.contract_number,
    con.contract_type,
    con.status AS contract_status,
    current_sim.msisdn,
    current_sim.sim_type,
    current_device.manufacturer AS device_manufacturer,
    current_device.model AS device_model,
    current_device.device_type,
    COALESCE(active_addons.recurring_addon_charge, 0) AS recurring_addon_charge,
    p.monthly_fee + COALESCE(active_addons.recurring_addon_charge, 0) AS total_monthly_recurring_charge
FROM public.customers c
JOIN public.accounts a ON a.customer_id = c.customer_id
LEFT JOIN public.billing_cycles bc ON bc.billing_cycle_id = a.billing_cycle_id
JOIN public.subscriptions s ON s.account_id = a.account_id
JOIN public.plans p ON p.plan_id = s.plan_id
LEFT JOIN public.contracts con ON con.contract_id = s.contract_id
LEFT JOIN LATERAL (
    SELECT sc.msisdn, sc.sim_type
    FROM public.sim_card_subscription_history ssh
    JOIN public.sim_cards sc ON sc.sim_id = ssh.sim_id
    WHERE ssh.subscription_id = s.subscription_id
      AND ssh.end_date IS NULL
    ORDER BY ssh.start_date DESC
    LIMIT 1
) current_sim ON TRUE
LEFT JOIN LATERAL (
    SELECT d.manufacturer, d.model, d.device_type
    FROM public.device_assignments da
    JOIN public.devices d ON d.device_id = da.device_id
    WHERE da.subscription_id = s.subscription_id
      AND da.assigned_to IS NULL
    ORDER BY da.assigned_from DESC
    LIMIT 1
) current_device ON TRUE
LEFT JOIN LATERAL (
    SELECT SUM(sa.price_at_activation) AS recurring_addon_charge
    FROM public.subscription_addons sa
    JOIN public.addons ad ON ad.addon_id = sa.addon_id
    WHERE sa.subscription_id = s.subscription_id
      AND sa.status = 'active'
      AND sa.deactivation_date IS NULL
      AND ad.is_recurring
) active_addons ON TRUE;
}}}

=== Customer Call History ===

{{{
DROP VIEW IF EXISTS public.v_customer_call_history;

CREATE VIEW public.v_customer_call_history 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,
    p.plan_name,
    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,
    cdr.fraud_score,
    cdr.roaming_partner_id IS NOT NULL AS is_roaming,
    COALESCE(rp.country, 'North Macedonia') AS network_country,
    ns.region AS network_region,
    ns.site_code,
    ct.tower_code,
    ts.sector_label,
    ts.frequency_band,
    nt.generation AS network_generation
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.plans p ON p.plan_id = s.plan_id
JOIN public.usage_cdr_calls cdr ON cdr.subscription_id = s.subscription_id
LEFT JOIN public.roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id
LEFT JOIN public.tower_sectors ts ON ts.sector_id = cdr.sector_id
LEFT JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
LEFT JOIN public.network_sites ns ON ns.site_id = ct.site_id
LEFT JOIN public.network_technologies nt ON nt.technology_id = ts.technology_id;
}}}

=== Customer Data Usage History ===

{{{
DROP VIEW IF EXISTS public.v_customer_data_usage_history;

CREATE VIEW public.v_customer_data_usage_history 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,
    p.plan_name,
    d.data_cdr_id,
    d.session_start,
    d.session_end,
    ROUND(EXTRACT(EPOCH FROM (d.session_end - d.session_start))::numeric / 60.0, 2) AS session_duration_minutes,
    d.data_used_mb,
    ROUND(d.data_used_mb / 1024.0, 4) AS data_used_gb,
    d.apn,
    d.ip_address,
    d.charge_amount,
    d.roaming_partner_id IS NOT NULL AS is_roaming,
    COALESCE(rp.country, 'North Macedonia') AS network_country,
    ns.region AS network_region,
    ns.site_code,
    ts.sector_label,
    ts.frequency_band,
    nt.generation AS network_generation
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.plans p ON p.plan_id = s.plan_id
JOIN public.usage_cdr_data d ON d.subscription_id = s.subscription_id
LEFT JOIN public.roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id
LEFT JOIN public.tower_sectors ts ON ts.sector_id = d.sector_id
LEFT JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
LEFT JOIN public.network_sites ns ON ns.site_id = ct.site_id
LEFT JOIN public.network_technologies nt ON nt.technology_id = ts.technology_id;
}}}

=== Customer Daily Usage Summary ===

{{{
DROP VIEW IF EXISTS public.v_customer_daily_usage_summary;

CREATE VIEW public.v_customer_daily_usage_summary 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_id,
    s.subscription_number,
    p.plan_name,
    uad.usage_date,
    ROUND(uad.total_call_seconds::numeric / 60.0, 2) AS total_call_minutes,
    uad.total_sms_count,
    ROUND(uad.total_data_mb / 1024.0, 4) AS total_data_gb,
    uad.total_charge_amount,
    SUM(uad.total_data_mb) OVER (
        PARTITION BY s.subscription_id, date_trunc('month', uad.usage_date::timestamp)
        ORDER BY uad.usage_date
    ) AS month_to_date_data_mb
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.plans p ON p.plan_id = s.plan_id
JOIN public.usage_aggregates_daily uad ON uad.subscription_id = s.subscription_id;
}}}

=== Customer Support Ticket Timeline ===

{{{
DROP VIEW IF EXISTS public.v_customer_support_ticket_timeline;

CREATE VIEW public.v_customer_support_ticket_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,
    p.plan_name,
    t.ticket_id,
    t.ticket_type,
    t.subject,
    t.priority,
    t.status AS ticket_status,
    t.created_at AS ticket_created_at,
    t.closed_at AS ticket_closed_at,
    ROUND(EXTRACT(EPOCH FROM (t.closed_at - t.created_at))::numeric / 3600.0, 2) AS resolution_hours,
    owner.first_name || ' ' || owner.last_name AS assigned_employee,
    ci.interaction_id,
    ci.interaction_type,
    ci.channel,
    ci.interaction_time,
    ci.notes,
    ci.old_status,
    ci.new_status,
    actor.first_name || ' ' || actor.last_name AS interaction_employee,
    ROW_NUMBER() OVER (PARTITION BY t.ticket_id ORDER BY ci.interaction_time NULLS LAST, ci.interaction_id) AS interaction_sequence
FROM public.customers c
JOIN public.crm_tickets t ON t.customer_id = c.customer_id
LEFT JOIN public.accounts a ON a.account_id = t.account_id
LEFT JOIN public.subscriptions s ON s.subscription_id = t.subscription_id
LEFT JOIN public.plans p ON p.plan_id = s.plan_id
LEFT JOIN public.employees owner ON owner.employee_id = t.assigned_employee_id
LEFT JOIN public.crm_interactions ci ON ci.ticket_id = t.ticket_id
LEFT JOIN public.employees actor ON actor.employee_id = ci.employee_id;
}}}

=== Network Outage Operations ===

{{{
DROP VIEW IF EXISTS public.v_network_outage_operations;

CREATE VIEW public.v_network_outage_operations AS
WITH assignment_summary AS (
    SELECT outage_id,
           COUNT(*) AS assignment_count,
           COUNT(*) FILTER (WHERE assignment_type = 'outage_lead') AS lead_assignments,
           COUNT(*) FILTER (WHERE assignment_type = 'field_support') AS field_assignments,
           MIN(start_time) AS first_assignment_at,
           MAX(end_time) AS last_assignment_end
    FROM public.employee_assignments
    WHERE outage_id IS NOT NULL
    GROUP BY outage_id
)
SELECT
    o.outage_id,
    ns.site_id,
    ns.site_code,
    ns.site_name,
    ns.region,
    o.outage_type,
    o.status,
    o.start_time,
    o.end_time,
    CASE WHEN o.end_time IS NOT NULL
         THEN ROUND(EXTRACT(EPOCH FROM (o.end_time - o.start_time))::numeric / 60.0, 2)
    END AS outage_duration_minutes,
    CASE
        WHEN o.end_time IS NULL THEN 'open'
        WHEN o.end_time - o.start_time <= INTERVAL '60 minutes' THEN 'under_1_hour'
        WHEN o.end_time - o.start_time <= INTERVAL '4 hours' THEN 'one_to_four_hours'
        ELSE 'over_4_hours'
    END AS duration_band,
    o.root_cause,
    na.alarm_id,
    na.alarm_type,
    na.severity AS alarm_severity,
    na.raised_at AS alarm_raised_at,
    COALESCE(ass.assignment_count, 0) AS assignment_count,
    COALESCE(ass.lead_assignments, 0) AS lead_assignments,
    COALESCE(ass.field_assignments, 0) AS field_assignments,
    ass.first_assignment_at,
    ass.last_assignment_end
FROM public.outages o
JOIN public.network_sites ns ON ns.site_id = o.site_id
LEFT JOIN public.network_alarms na ON na.alarm_id = o.alarm_id
LEFT JOIN assignment_summary ass ON ass.outage_id = o.outage_id;
}}}

=== Sector Traffic Map ===

{{{
-- CDR activity summarized per map polygon without multiplying event rows.
-- QGIS polygon layer. Unique key: coverage_zone_id; geometry: coverage_area.
CREATE OR REPLACE VIEW public.v_sector_traffic_map AS
WITH call_stats AS
(
    SELECT
        sector_id,
        count(*) AS call_count,
        coalesce(sum(duration_seconds), 0) AS call_seconds,
        max(event_start_time) AS last_call_at
    FROM public.usage_cdr_calls
    WHERE sector_id IS NOT NULL
    GROUP BY sector_id
),
sms_stats AS
(
    SELECT
        sector_id,
        count(*) AS sms_count,
        max(event_time) AS last_sms_at
    FROM public.usage_cdr_sms
    WHERE sector_id IS NOT NULL
    GROUP BY sector_id
),
data_stats AS
(
    SELECT
        sector_id,
        count(*) AS data_session_count,
        coalesce(sum(data_used_mb), 0) AS data_used_mb,
        max(session_start) AS last_data_at
    FROM public.usage_cdr_data
    WHERE sector_id IS NOT NULL
    GROUP BY sector_id
)
SELECT
    coverage.coverage_zone_id,
    coverage.site_id,
    coverage.site_code,
    coverage.site_name,
    coverage.region,
    coverage.tower_id,
    coverage.tower_code,
    coverage.sector_id,
    coverage.sector_label,
    coverage.technology_name,
    coverage.generation,
    coalesce(calls.call_count, 0) AS call_count,
    coalesce(calls.call_seconds, 0) AS call_seconds,
    coalesce(messages.sms_count, 0) AS sms_count,
    coalesce(data_usage.data_session_count, 0) AS data_session_count,
    coalesce(data_usage.data_used_mb, 0) AS data_used_mb,
    coalesce(calls.call_count, 0)
      + coalesce(messages.sms_count, 0)
      + coalesce(data_usage.data_session_count, 0) AS total_events,
    greatest(calls.last_call_at, messages.last_sms_at, data_usage.last_data_at)
        AS last_event_at,
    coverage.coverage_area
FROM public.v_network_coverage_map coverage
LEFT JOIN call_stats calls ON calls.sector_id = coverage.sector_id
LEFT JOIN sms_stats messages ON messages.sector_id = coverage.sector_id
LEFT JOIN data_stats data_usage ON data_usage.sector_id = coverage.sector_id;
}}}

=== Customer Coverage Detail ===

{{{
-- One row for every active sector covering an active customer's primary address.
-- QGIS point layer. Unique key can be customer_id + coverage_zone_id.
CREATE OR REPLACE VIEW public.v_customer_coverage_detail AS
SELECT
    c.customer_id,
    concat_ws(' ', c.first_name, c.last_name) AS customer_name,
    ca.address_id,
    concat_ws(', ', ca.street, ca.city, ca.country) AS full_address,
    cz.coverage_zone_id,
    ns.site_id,
    ns.site_code,
    ns.site_name,
    ct.tower_code,
    ts.sector_id,
    ts.sector_label,
    nt.technology_name,
    nt.generation,
    cz.signal_quality_score,
    round(
        ST_Distance(ca.location::geography, ns.location::geography)::numeric,
        1
    ) AS distance_to_site_m,
    ca.location AS customer_location
FROM public.customers c
JOIN public.customer_addresses ca
  ON ca.customer_id = c.customer_id
 AND ca.is_primary
JOIN public.coverage_zones cz
  ON ca.location IS NOT NULL
 AND cz.coverage_area IS NOT NULL
 AND ST_Covers(cz.coverage_area, ca.location)
JOIN public.tower_sectors ts ON ts.sector_id = cz.sector_id
JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
JOIN public.network_sites ns ON ns.site_id = ct.site_id
JOIN public.network_technologies nt
  ON nt.technology_id = ts.technology_id
WHERE c.status = 'active'
  AND ns.status = 'active'
  AND ct.status = 'active'
  AND ts.status = 'active';
}}}

=== Network Site Points ===

{{{
-- QGIS point layer. Unique key: site_id; geometry: location.
CREATE OR REPLACE VIEW public.v_network_site_points AS
SELECT
    ns.site_id,
    ns.site_code,
    ns.site_name,
    ns.address,
    ns.region,
    ns.site_type,
    ns.status,
    ns.opened_at,
    ns.location
FROM public.network_sites ns
WHERE ns.location IS NOT NULL;
}}}

=== Find Nearest Network Sites ===

{{{
CREATE OR REPLACE FUNCTION public.find_nearest_network_sites(
    p_latitude double precision,
    p_longitude double precision,
    p_max_distance_m double precision DEFAULT 10000,
    p_limit integer DEFAULT 5
)
RETURNS TABLE
(
    site_id bigint,
    site_code text,
    site_name text,
    region text,
    distance_m numeric,
    active_technologies text
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    IF p_latitude < -90 OR p_latitude > 90 THEN
        RAISE EXCEPTION 'Latitude must be between -90 and 90';
    END IF;

    IF p_longitude < -180 OR p_longitude > 180 THEN
        RAISE EXCEPTION 'Longitude must be between -180 and 180';
    END IF;

    IF p_max_distance_m <= 0 THEN
        RAISE EXCEPTION 'Maximum distance must be positive';
    END IF;

    IF p_limit < 1 OR p_limit > 100 THEN
        RAISE EXCEPTION 'Limit must be between 1 and 100';
    END IF;

    RETURN QUERY
    WITH search_point AS
    (
        SELECT ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326) AS location
    )
    SELECT
        ns.site_id,
        ns.site_code,
        ns.site_name,
        ns.region,
        round(
            ST_Distance(ns.location::geography, sp.location::geography)::numeric,
            1
        ) AS distance_m,
        coalesce(technologies.names, 'No active sectors') AS active_technologies
    FROM public.network_sites ns
    CROSS JOIN search_point sp
    LEFT JOIN LATERAL
    (
        SELECT string_agg(
                   DISTINCT concat(nt.generation, ' ', nt.technology_name),
                   ', '
                   ORDER BY concat(nt.generation, ' ', nt.technology_name)
               ) AS names
        FROM public.cell_towers ct
        JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
        JOIN public.network_technologies nt
          ON nt.technology_id = ts.technology_id
        WHERE ct.site_id = ns.site_id
          AND ct.status = 'active'
          AND ts.status = 'active'
          AND nt.status = 'active'
    ) technologies ON true
    WHERE ns.status = 'active'
      AND ns.location IS NOT NULL
      AND ST_DWithin(
              ns.location::geography,
              sp.location::geography,
              p_max_distance_m
          )
    ORDER BY ns.location::geography <-> sp.location::geography
    LIMIT p_limit;
END;
$$;
}}}