WITH 
checks AS (
    SELECT 
        h.host AS host_name,
        COALESCE(h.name, h.host) AS visible_name,
        COALESCE(hinv.name, 'unknown') AS equipment_type,
        COALESCE(hinv.notes, '') AS object_number,
        CASE 
            WHEN hinv.location_lat IS NOT NULL AND hinv.location_lon IS NOT NULL 
                 AND hinv.location_lat != '' AND hinv.location_lon != ''
            THEN hinv.location_lat || ' ' || hinv.location_lon
            WHEN hinv.location_lat IS NOT NULL AND hinv.location_lat != '' 
            THEN hinv.location_lat
            WHEN hinv.location_lon IS NOT NULL AND hinv.location_lon != '' 
            THEN hinv.location_lon
            ELSE ''
        END AS coordinates,
        TO_TIMESTAMP(hx.clock) AT TIME ZONE 'Europe/Moscow' AS check_time,
        hx.value AS status
    FROM hosts h
    INNER JOIN items i 
        ON i.hostid = h.hostid 
        AND i.key_ = 'icmpping'
        AND i.status = 0
        AND i.value_type = 3
    INNER JOIN history_uint hx 
        ON hx.itemid = i.itemid
    LEFT JOIN host_inventory hinv 
        ON hinv.hostid = h.hostid
    WHERE 
        h.status = 0
        and CAST(nullif(regexp_replace(hinv.notes, 'ОБЪЕКТ №\s*', '', 'g'),'') as INTEGER) in (44,77,43,54,27,48,12,49,65,9,60,35,38,1,51,41,53,84,8) 
        --AND UPPER(h.host) NOT LIKE 'IRZ%'
        AND hx.clock >= EXTRACT(EPOCH FROM TIMESTAMP '2026-05-25 00:00:00')
        AND hx.clock < EXTRACT(EPOCH FROM TIMESTAMP '2026-06-01 00:00:00')
),

daily_stats AS (
    SELECT 
        host_name,
        visible_name,
        equipment_type,
        object_number,
        coordinates,
        DATE(check_time) AS check_date,
        ROUND((SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) * 60.0) / 3600.0, 2) AS available_hours,
        ROUND((SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) * 60.0) / 3600.0, 2) AS unavailable_hours
    FROM checks
    GROUP BY 
        host_name,
        visible_name,
        equipment_type,
        object_number,
        coordinates,
        DATE(check_time)
),

daily_graph AS (
    SELECT 
        visible_name,
        equipment_type,
        object_number,
        coordinates,
        STRING_AGG(
            TO_CHAR(check_date, 'DD.MM') || ': Доступно ' || available_hours || 'h, Недоступно ' || unavailable_hours || 'h', 
            '; ' ORDER BY check_date
        ) AS daily_schedule
    FROM daily_stats
    GROUP BY 
        visible_name,
        equipment_type,
        object_number,
        coordinates
),

device_summary AS (
    SELECT 
        visible_name,
        equipment_type,
        object_number,
        coordinates,
        SUM(CASE WHEN available_hours > 12 THEN 1 ELSE 0 END) AS days_available_gt_12h,
        SUM(CASE WHEN available_hours <= 12 THEN 1 ELSE 0 END) AS days_unavailable_ge_12h
    FROM daily_stats
    GROUP BY 
        visible_name,
        equipment_type,
        object_number,
        coordinates
)

SELECT 
    ROW_NUMBER() OVER (ORDER BY dg.equipment_type) AS row_number,
    dg.equipment_type AS equipment_type,
    dg.object_number AS object_number,
    dg.coordinates AS coordinates,
    dg.daily_schedule AS daily_schedule,
    ds.days_available_gt_12h AS days_available_more_than_12h,
    ds.days_unavailable_ge_12h AS days_unavailable_12h_or_more
FROM daily_graph dg
INNER JOIN device_summary ds 
    ON ds.visible_name = dg.visible_name
ORDER BY dg.object_number;