---

/*
    Эффективность сессий Сирены, в которых был хотя бы один RAG-запрос.

    В отчёт входят сессии с любым RAG-запросом:
      - успешные ответы:
          ragSuccess, ragPartialAnswer, ragClarify;
      - без ответа:
          filterBlocked, ragLowProbability, ragMissingContext,
          ragTimeout, ragError, ragConcurrencyLimit,
          ragOperatorEscalation.

    Сессия — интервал между ConnectedToIssue и ближайшим
    DisconnectedFromIssue для техника Сирены (sirena_id).

    Эффективность сессии:
      - берём время последнего RAG-запроса внутри сессии;
      - если в сессии НЕ было ни одного успешного RAG-ответа
        (ragSuccess / ragPartialAnswer / ragClarify) → неэффективная;
      - если успешный ответ был:
          - есть ForwardedToTopic в окне
            [последний запрос; последний запрос + 24 часа] → неэффективная;
          - перевода нет → эффективная.

    Обе границы окна включены.

    Фильтры:
      - Начало;
      - Конец;
      - Продукт.
*/

WITH

    toDate('{{ Начало }}') AS date_from,
    toDate('{{ Конец }}') AS date_to,
    addDays(date_to, 2) AS date_extended,

    'a5e8bebe-dea1-444d-8178-5d966b962d8c' AS sirena_id,


/* ============================================================
   1. ВСЕ RAG-ЗАПРОСЫ ЗА ПЕРИОД (с учётом фильтров)
   ============================================================ */

RagRequests AS (

    SELECT
        Session_CustomSessionId,
        toString(Session_CustomSessionId) AS SessionIdString,
        Time_ClientTime,
        Path_Label

    FROM metrika.events

    WHERE Topic_Key = 'Sirena'
      AND Path_Action = 'ragAutoReply'

      AND ClientDate BETWEEN date_from AND date_to

      AND Path_Label IN (
          /* успешные ответы */
          'ragSuccess',
          'ragPartialAnswer',
          'ragClarify',

          /* без ответа */
          'filterBlocked',
          'ragLowProbability',
          'ragMissingContext',
          'ragTimeout',
          'ragError',
          'ragConcurrencyLimit',
          'ragOperatorEscalation'
      )

      /* Фильтр по продукту */
      AND (
          '{{ Продукт }}' = 'Все'

          OR visitParamExtractString(
              Variables,
              'ragOriginProduct'
          ) = '{{ Продукт }}'
      )

),


/* ============================================================
   2. ПОДКЛЮЧЕНИЯ СИРЕНЫ
   ============================================================ */

Starts AS (

    SELECT DISTINCT
        visitParamExtractString(Variables, 'issueId') AS IssueId,
        Time_ClientTime AS SessionStart

    FROM metrika.events

    WHERE Topic_Key = 'LiveChat'
      AND Path_Action = 'ConnectedToIssue'

      AND ClientDate BETWEEN date_from AND date_to

      AND visitParamExtractString(Variables, 'technicianId') = sirena_id

      AND visitParamExtractString(Variables, 'issueId') != ''

      /* Ограничиваем чаты, в которых был хотя бы один RAG-запрос */
      AND visitParamExtractString(Variables, 'issueId') GLOBAL IN (
          SELECT DISTINCT SessionIdString
          FROM RagRequests
      )

),


/* ============================================================
   3. ОТКЛЮЧЕНИЯ СИРЕНЫ
   ============================================================ */

Ends AS (

    SELECT DISTINCT
        visitParamExtractString(Variables, 'issueId') AS IssueId,
        Time_ClientTime AS SessionEnd

    FROM metrika.events

    WHERE Topic_Key = 'LiveChat'
      AND Path_Action = 'DisconnectedFromIssue'

      /* Захватываем дополнительные дни для сессий,
         закрывшихся после полуночи и для 24-часового окна */
      AND ClientDate BETWEEN date_from AND date_extended

      AND visitParamExtractString(Variables, 'technicianId') = sirena_id

      AND visitParamExtractString(Variables, 'issueId') != ''

      AND visitParamExtractString(Variables, 'issueId') GLOBAL IN (
          SELECT DISTINCT SessionIdString
          FROM RagRequests
      )

),


/* ============================================================
   4. БЛИЖАЙШЕЕ ОТКЛЮЧЕНИЕ ПОСЛЕ КАЖДОГО ПОДКЛЮЧЕНИЯ
   ============================================================ */

NearestEnds AS (

    SELECT
        s.IssueId AS IssueId,
        s.SessionStart AS SessionStart,
        min(e.SessionEnd) AS SessionEnd

    FROM Starts AS s

    INNER JOIN Ends AS e
        ON s.IssueId = e.IssueId

    WHERE e.SessionEnd >= s.SessionStart

    GROUP BY
        s.IssueId,
        s.SessionStart

),


/* ============================================================
   5. СЕССИИ
   ============================================================ */

Sessions AS (

    SELECT
        IssueId,
        min(SessionStart) AS SessionStart,
        SessionEnd

    FROM NearestEnds

    GROUP BY
        IssueId,
        SessionEnd

),


/* ============================================================
   6. ПОСЛЕДНИЙ RAG-ЗАПРОС В КАЖДОЙ СЕССИИ
   ============================================================ */

SessionLastRag AS (

    SELECT
        s.IssueId AS IssueId,
        s.SessionStart AS SessionStart,
        s.SessionEnd AS SessionEnd,

        /* Последний RAG-запрос (любой лейбл из набора) */
        max(r.Time_ClientTime) AS LastRagTime,

        /* Сколько RAG-запросов попало в сессию */
        count() AS RagCount,

        /* Был ли в сессии хотя бы один успешный RAG-ответ */
        max(
            if(
                r.Path_Label IN ('ragSuccess','ragPartialAnswer','ragClarify'),
                1, 0
            )
        ) AS HasRagAnswer

    FROM Sessions AS s

    INNER JOIN RagRequests AS r
        ON toString(s.IssueId) = r.SessionIdString

    WHERE r.Time_ClientTime >= s.SessionStart
      AND r.Time_ClientTime <= s.SessionEnd

    GROUP BY
        s.IssueId,
        s.SessionStart,
        s.SessionEnd

),


/* ============================================================
   7. ПЕРЕВОДЫ НА ТОПИК
   ============================================================ */

Forwards AS (

    SELECT
        visitParamExtractString(Variables, 'issueId') AS IssueId,
        groupArray(Time_ClientTime) AS ForwardTimes

    FROM metrika.events

    WHERE Topic_Key = 'LiveChat'
      AND Path_Action = 'ForwardedToTopic'

      /* +2 дня, чтобы поймать переводы в 24-часовом окне */
      AND ClientDate BETWEEN date_from AND date_extended

      AND visitParamExtractString(Variables, 'issueId') != ''

    GROUP BY IssueId

),


/* ============================================================
   8. ЭФФЕКТИВНОСТЬ СЕССИЙ
   ============================================================ */

SessionEfficiency AS (

    SELECT
        l.IssueId AS IssueId,
        l.SessionStart AS SessionStart,
        l.SessionEnd AS SessionEnd,
        l.LastRagTime AS LastRagTime,
        l.RagCount AS RagCount,
        l.HasRagAnswer AS HasRagAnswer,

        CASE
            /* Не было ни одного успешного RAG-ответа → неэффективная */
            WHEN l.HasRagAnswer = 0 THEN 'Неэффективные'

            /* Был ответ: проверяем перевод в окне [последний запрос; +24ч] */
            WHEN arrayExists(
                    t -> t >= l.LastRagTime
                         AND t <= addMinutes(l.LastRagTime, 1440),
                    coalesce(f.ForwardTimes, emptyArrayDateTime())
                 )
                 THEN 'Неэффективные'
            ELSE 'Эффективные'
        END AS Result

    FROM SessionLastRag AS l

    GLOBAL LEFT JOIN Forwards AS f
        ON toString(l.IssueId) = f.IssueId

)

SELECT
    Result,
    count() AS SessionCount

FROM SessionEfficiency

GROUP BY
    Result

ORDER BY
    SessionCount DESC