-- =============================================================================
-- Отчет повременного учета работоспособности периферийного оборудования
-- Период: май 2026
-- Исключены устройства, начинающиеся на IRZ
-- Тип оборудования берется из инвентаризации Zabbix (поле type)
-- Координаты: широта + долгота из полей location_lat и location_lon
-- =============================================================================

WITH 
-- 1. Получаем все проверки ICMP за период
checks AS (
    SELECT 
        h.host AS "Устройство",
        COALESCE(h.name, h.host) AS "Видимое имя",
        -- Тип оборудования из инвентаризации (поле type), если пусто - берем hardware
        COALESCE(hinv."name", 'не указан') AS "Тип оборудования",
        -- Номер объекта ИТС РО (храним в поле notes инвентаризации)
        COALESCE(hinv.notes, '') AS "Номер_объекта_ИТС_РО",
        -- Координаты: объединяем широту и долготу через пробел
        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 "Координаты_расположения",
        -- Время проверки с учетом часового пояса
        TO_TIMESTAMP(hx.clock) AT TIME ZONE 'Europe/Moscow' AS "ts",
        -- Статус: 1 = доступно, 0 = недоступно
        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  -- unsigned numeric
    INNER JOIN history_uint hx 
        ON hx.itemid = i.itemid
    LEFT JOIN host_inventory hinv 
        ON hinv.hostid = h.hostid
    WHERE 
        -- Только мониторимые хосты
        h.status = 0
        -- Исключаем оборудование, начинающееся на IRZ (любой регистр)
        --AND UPPER(h.host) NOT LIKE 'IRZ%'
        -- Период: май 2026 года (измените под свои даты)
        --and hinv.notes = 'ОБЪЕКТ № 9'
        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 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')
),

-- 2. Добавляем предыдущий статус для определения моментов смены
checks_with_prev AS (
    SELECT 
        *,
        LAG("status") OVER (PARTITION BY "Устройство" ORDER BY "ts") AS "prev_status"
    FROM checks
),

-- 3. Определяем моменты смены статуса (начало нового интервала)
status_changes AS (
    SELECT 
        *,
        CASE 
            WHEN "prev_status" IS NULL OR "status" != "prev_status" THEN 1 
            ELSE 0 
        END AS "is_start"
    FROM checks_with_prev
),

-- 4. Нумеруем интервалы для каждого устройства
intervals AS (
    SELECT 
        *,
        SUM("is_start") OVER (PARTITION BY "Устройство" ORDER BY "ts") AS "interval_id"
    FROM status_changes
    WHERE "status" IS NOT NULL
),

-- 5. Агрегируем каждый непрерывный интервал
grouped_intervals AS (
    SELECT 
        "Устройство",
        "Видимое имя",
        "Тип оборудования",
        "Номер_объекта_ИТС_РО",
        "Координаты_расположения",
        MIN("ts") AS "time_start",
        MAX("ts") AS "time_end",
        MAX("status") AS "status"
    FROM intervals
    GROUP BY 
        "Устройство", 
        "Видимое имя",
        "Тип оборудования",
        "Номер_объекта_ИТС_РО",
        "Координаты_расположения",
        "interval_id"
)

-- 6. Финальный вывод отчета
SELECT 
    ROW_NUMBER() OVER (ORDER BY "Устройство", "time_start") AS "№ п/п",
    TO_CHAR(DATE("time_start"), 'DD.MM.YYYY') AS "Дата",
    "Тип оборудования",
    "Номер_объекта_ИТС_РО",
    "Координаты_расположения",
    TO_CHAR("time_start", 'HH24:MI:SS') AS "Время начала статуса",
    TO_CHAR("time_end", 'HH24:MI:SS') AS "Время окончания статуса",
    CASE 
        WHEN "status" = 1 THEN 'доступно'
        ELSE 'недоступно'
    END AS "Статус"
FROM grouped_intervals
ORDER BY "Номер_объекта_ИТС_РО";