DatabaseCreation: 15 Network outage operations.sql

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;