CREATE OR REPLACE EDITIONABLE TRIGGER "CASEMGMT"."ECM_ENTITY_LOCK_TRIGGER"
-- Срабатывает на вставку и удаление
FOR INSERT OR DELETE ON CASEMGMT.ECM_ENTITY_LOCK
-- Триггер составной для обхода ошибки mutating table
COMPOUND TRIGGER
  -- numberList_type is table of number
  -- Список блокировок заявок (RK) на удаление
  g_c_delete_list NumberList_type := NumberList_type();
  -- Список инцидентов (RK) на блокировку
  g_i_insert_list NumberList_type := NumberList_type();
  -- Список блокировок инцидентов (RK) на удаление
  g_i_delete_list NumberList_type := NumberList_type();
  -- Пользователь, который устанавливает или снимает блокировку
  g_lock_user_id VARCHAR2(60 CHAR);

  -- Секция, которая срабатывает для каждой строки
  -- здесь только составляются списки (g_*_list) на вставку и удаление
  AFTER EACH ROW IS
    -- Номер заявки, с которой связана блокировка
    v_case_rk NUMBER(10, 0);
    -- Верификатор, с которым связана заявка
    v_case_investigator VARCHAR2(60 CHAR);
    -- Исключение для выхода из секции, если входные условия не подходят
    do_nothing EXCEPTION;
  BEGIN
    -- Если блокировка снимается
    IF DELETING THEN
      -- Запомним, чья это была блокировка
      g_lock_user_id := :old.LOCK_USER_ID;

      BEGIN
        -- В зависимости от типа разблокируемой сущности определим связанную заявку
        CASE :old.ENTITY_NM
          -- Разблокируется заявка - сразу имеем её RK
          WHEN 'CASE' THEN v_case_rk := :old.ENTITY_RK;
          -- Разблокируется инцидент - запросим RK в INCIDENT_LIVE
          WHEN 'INCIDENT' THEN
            SELECT CASE_RK INTO v_case_rk FROM CASEMGMT.INCIDENT_LIVE WHERE INCIDENT_RK = :old.ENTITY_RK;
          -- Разблокируется иная сущность - пропустим действие
          ELSE RAISE DO_NOTHING;
        END CASE;
      EXCEPTION
        -- Если не смогли определить заявку - пропустим действие
        WHEN NO_DATA_FOUND THEN RAISE DO_NOTHING;
        -- Для других ошибок выбрасываем исключение
        WHEN OTHERS THEN RAISE;
      END;
      -- Еслли не смогли определить заявку - пропустим действие
      IF (v_case_rk IS NULL) THEN RAISE DO_NOTHING; END IF;

      /* Если снимается блокировка с инцидента или заявки, то
        снимаем блокировку того же пользователя со всех связанных инцидентов */
      BEGIN
        SELECT INCIDENT_RK
        -- Запомним снимаемые блокировки с инцидентов в списке
        BULK COLLECT INTO g_i_delete_list
        FROM CASEMGMT.INCIDENT_LIVE
        WHERE
          CASE_RK = v_case_rk
          -- Исключим текущий инцидент, т.к. блокировка с него и так сейчас снимается
          AND INCIDENT_RK <> CASE :old.ENTITY_NM WHEN 'INCIDENT' THEN :old.ENTITY_RK ELSE -1 END
        ;
      EXCEPTION
        -- Если нет связанных блокировок, то продолжаем
        WHEN NO_DATA_FOUND THEN NULL;
        -- Для других ошибок выбрасываем исключение
        WHEN OTHERS THEN RAISE;
      END;

      -- Добавим заявку в список для снятия блокировок
      IF (:old.ENTITY_NM = 'INCIDENT') THEN
        g_c_delete_list.EXTEND; g_c_delete_list(g_c_delete_list.COUNT) := v_case_rk;
      END IF;
    -- Если блокировка устанавливается
    ELSIF INSERTING THEN
      -- запомним пользователя, кто блокирует заявку
      g_lock_user_id := :NEW.LOCK_USER_ID;

      BEGIN
        /* В зависимости от типа блокируемой сущности определим связанную заявку */
        CASE :NEW.ENTITY_NM
          -- Разблокируется заявка - сразу имеем её RK
          WHEN 'CASE' THEN v_case_rk := :NEW.ENTITY_RK;
          -- Разблокируется инцидент - запросим RK в INCIDENT_LIVE
          WHEN 'INCIDENT' THEN
            SELECT CASE_RK INTO v_case_rk FROM CASEMGMT.INCIDENT_LIVE WHERE INCIDENT_RK = :NEW.ENTITY_RK;
          -- Разблокируется иная сущность - пропустим действие
          ELSE RAISE DO_NOTHING;
        END CASE;
      EXCEPTION
        -- Если не смогли определить заявку - пропустим действие
        WHEN NO_DATA_FOUND THEN RAISE DO_NOTHING;
        -- Для других ошибок выбрасываем исключение
        WHEN OTHERS THEN RAISE;
      END;
      -- Если не смогли определить заявку - пропустим действие
      IF (v_case_rk IS NULL) THEN RAISE DO_NOTHING; END IF;

      BEGIN
        -- Выберем верификатора, которому назначена заявка
        SELECT UPPER(INVESTIGATOR_USER_ID) INTO v_case_investigator FROM CASEMGMT.CASE_LIVE WHERE CASE_RK = v_case_rk;
      EXCEPTION
        -- Если верификатор не назначен - пропускаем ошибку
        WHEN NO_DATA_FOUND THEN NULL;
        -- Для других ошибок выбрасываем исключение
        WHEN OTHERS THEN RAISE;
      END;

      -- Проверим, можно ли конкретному пользователю блокировать заявку/инцидент
      -- Если заявка не назначена пользователю, который пытается её заблокировать, то запретим такую блокировку
      IF (UPPER(g_lock_user_id) <> NVL(v_case_investigator, '_foo_')) THEN
        -- Снимем "несанкционированную" блокировку
        CASE :NEW.ENTITY_NM
          -- если блокируется заявка, добавим её RK в список для удаления
          WHEN 'CASE'     THEN g_c_delete_list.EXTEND; g_c_delete_list(g_c_delete_list.COUNT) := :NEW.ENTITY_RK;
          -- если блокируется инцидент, добавим его RK в список для удаления
          WHEN 'INCIDENT' THEN g_i_delete_list.EXTEND; g_i_delete_list(g_i_delete_list.COUNT) := :NEW.ENTITY_RK;
        END CASE;
      -- Иначе (блокировка "санкционирована")
      ELSE
        -- Нужно заблокировать связанные инциденты (кроме инцидентов ТВ)
        BEGIN
          SELECT i.INCIDENT_RK
          -- Запомним связанные инциденты в списке
          BULK COLLECT INTO g_i_insert_list
          FROM CASEMGMT.INCIDENT_LIVE i
          -- посмотрим номер итерации инцидента
          INNER JOIN CASEMGMT.INCIDENT_UDF_NUM_VALUE iItNo ON (
            iItNo.INCIDENT_RK = i.INCIDENT_RK
            AND iItNo.VALID_FROM_DTTM = i.VALID_FROM_DTTM
            AND iItNo.UDF_TABLE_NM = 'INCIDENT'
            AND iItNo.UDF_NM = 'X_ITERATION_NUM'
            AND iItNo.ROW_NO = 1
          )
          -- посмотрим текущий номер итерации заявки
          INNER JOIN CASE_LIVE c ON (c.CASE_RK = i.CASE_RK)
          INNER JOIN CASEMGMT.CASE_UDF_NUM_VALUE cItNo ON (
            cItNo.CASE_RK = c.CASE_RK
            AND cItNo.VALID_FROM_DTTM = c.VALID_FROM_DTTM
            AND cItNo.UDF_TABLE_NM = 'CASE'
            AND cItNo.UDF_NM = iItNo.UDF_NM
            AND cItNo.ROW_NO = 1
          )
          WHERE
            i.CASE_RK = v_case_rk
            -- исключим инциденты ТВ (они назначаются "каруселью" по одному)
            AND i.INCIDENT_TYPE_CD NOT IN ('CV')
            -- блокируем только связанные инциденты из текущей итерации заявки
            AND cItNo.UDF_VALUE = iItNo.UDF_VALUE
          ;
        EXCEPTION
          -- если нет связанных инцидентов, пропустим
          WHEN NO_DATA_FOUND THEN NULL;
          -- Для других ошибок выбрасываем исключение
          WHEN OTHERS THEN RAISE;
        END;

      END IF;

    END IF; /* DELETING|INSERTING */

  EXCEPTION
    -- Если дана команда на пропуск секции, то ничего не делаем
    WHEN DO_NOTHING THEN NULL;
    -- Остальные исключения пробрасываем дальше
    WHEN OTHERS THEN RAISE;
  END AFTER EACH ROW;

  -- Секция выполняется однократно по завершении выполнения конкретного INSERT или UPDATE
  AFTER STATEMENT IS
    -- временный список
    v_temp_list NumberList_type := NumberList_type();
  BEGIN
    -- если надо заблокировать инциденты
    IF (g_i_insert_list.COUNT > 0) THEN

      -- Пользователь может держать блокировки только по одной заявке,
      BEGIN
        -- очистим временный список
        v_temp_list.DELETE;

        SELECT ENTITY_RK
        -- запомним в списке блокировки, которые надо снять
        BULK COLLECT INTO v_temp_list
        FROM CASEMGMT.ECM_ENTITY_LOCK
        WHERE
          ENTITY_NM = 'INCIDENT'
          -- всё, что этим пользователем не блокируется, должно быть разблокировано
          AND ENTITY_RK NOT IN (SELECT column_value FROM TABLE(g_i_insert_list))
          AND LOCK_USER_ID = g_lock_user_id
        ;
        -- Если есть посторонние блокировки
        IF (v_temp_list.COUNT > 0) THEN
          -- добавляем их в список на удаление
          g_i_delete_list := g_i_delete_list MULTISET UNION v_temp_list;
          -- очистим временный список
          v_temp_list.DELETE;
        END IF;
      EXCEPTION
        -- Если посторонних блокировок нет, пропустим этот шаг
        WHEN NO_DATA_FOUND THEN NULL;
        -- Остальные исключения пробрасываем дальше
        WHEN OTHERS THEN RAISE;
      END;

      -- Если снимаются блокировки с инцидентов, значит разблокируются и связанные с ними заявки
      IF (g_i_delete_list.COUNT > 0) THEN
        BEGIN
          -- очистим временный список
          v_temp_list.DELETE;

          SELECT CASE_RK AS val
          -- запомним связанные заявки во временном списке
          BULK COLLECT INTO v_temp_list
          -- в таблицу блокировок сейчас не будем смотреть, т.к. её всё равно будет просматривать оператор DELETE
          FROM CASEMGMT.INCIDENT_LIVE
          WHERE INCIDENT_RK IN (SELECT column_value FROM TABLE(g_i_delete_list))
          ;

          IF (v_temp_list.COUNT > 0) THEN
            -- добавим связанные заявки в список на удаление блокировок
            g_c_delete_list := g_c_delete_list MULTISET UNION v_temp_list;
            -- очистим временный список
            v_temp_list.DELETE;
          END IF;
        EXCEPTION
          -- Если заявки не выбраны, пропустим этот шаг
          -- (ситуация, правда, странная, но это проблема не для решения на уровне триггера)
          WHEN NO_DATA_FOUND THEN NULL;
          -- Остальные исключения пробрасываем дальше
          WHEN OTHERS THEN RAISE;
        END;
      END IF;

      -- Вставим блокировки из списка на добавление
      BEGIN
        -- MERGE - для избегания попыток вставки блокировок, которые в таблице уже могут быть
        MERGE INTO CASEMGMT.ECM_ENTITY_LOCK tgt
        USING (
          -- Обрабатываем список блокировок инцидентов
          SELECT DISTINCT
            column_value AS ENTITY_RK, 'INCIDENT' AS ENTITY_NM, g_lock_user_id AS LOCK_USER_ID, CURRENT_TIMESTAMP AS LOCK_DTTM
          FROM TABLE(g_i_insert_list)
        ) src
        ON (tgt.ENTITY_RK = src.ENTITY_RK AND tgt.ENTITY_NM = src.ENTITY_NM)
        -- Уберем возможные дубликаты
        WHEN NOT MATCHED THEN INSERT (ENTITY_RK, ENTITY_NM, LOCK_USER_ID, LOCK_DTTM)
        VALUES (src.ENTITY_RK, src.ENTITY_NM, src.LOCK_USER_ID, src.LOCK_DTTM)
        ;
      EXCEPTION
        /* Если список блокировок не пуст, но ничего не выбралось, то пропустим этот шаг, т.к. иначе ЕСМ остановится;
        могут копиться блокировки, но их администратор может снять вручную в административном поиске, либо они отвалятся по времени (ниже) */
        WHEN NO_DATA_FOUND THEN NULL;
        -- Остальные исключения пробрасываем дальше
        WHEN OTHERS THEN RAISE;
      END;
      -- список обработали, очистим
      g_i_insert_list.DELETE;
    END IF;

    -- Если нужно разблокировать заявки или инциденты
    IF (g_c_delete_list.COUNT > 0 OR g_i_delete_list.COUNT > 0) THEN
      BEGIN
        -- Удалим просрочку
        DELETE FROM CASEMGMT.ECM_ENTITY_LOCK
        WHERE ROWID IN (
          SELECT t1.ROWID
          -- Просмотрим блокировки инцидентов,
          FROM CASEMGMT.ECM_ENTITY_LOCK t1
          -- по которым пользователь не проявлял никакой активности более получаса
          LEFT JOIN CASEMGMT.ECM_EVENT t2 ON (
            t2.CREATE_DTTM >= CURRENT_TIMESTAMP - INTERVAL '30' MINUTE
            AND t2.BUSINESS_OBJECT_NM = t1.ENTITY_NM
            AND t2.BUSINESS_OBJECT_RK = t1.ENTITY_RK
          )
          WHERE
            t1.LOCK_DTTM <= CURRENT_TIMESTAMP - INTERVAL '30' MINUTE
            AND t1.ENTITY_NM IN ('INCIDENT')
            AND t2.BUSINESS_OBJECT_RK IS NULL
        );
      EXCEPTION
        -- Если нет просроченных заявок
        WHEN NO_DATA_FOUND THEN
          BEGIN
            -- всё равно удалим блокировки, которые длятся явно дольше рабочего дня
            DELETE FROM CASEMGMT.ECM_ENTITY_LOCK
            WHERE ENTITY_NM IN ('CASE', 'INCIDENT')
              AND LOCK_DTTM <= CURRENT_TIMESTAMP - INTERVAL '1' DAY
            ;
          EXCEPTION
            -- если и таковых нет, пропустим шаг
            WHEN NO_DATA_FOUND THEN NULL;
            -- Остальные исключения пробрасываем дальше
            WHEN OTHERS THEN RAISE;
          END;
        -- Остальные исключения пробрасываем дальше
        WHEN OTHERS THEN RAISE;
      END;

      -- Обработаем список удалемых блокировок инцидентов
      IF (g_i_delete_list.COUNT > 0) THEN
        BEGIN
          DELETE FROM CASEMGMT.ECM_ENTITY_LOCK t1
          WHERE
            t1.ENTITY_NM = 'INCIDENT' AND t1.LOCK_USER_ID = g_lock_user_id
            AND t1.ENTITY_RK IN (SELECT column_value FROM TABLE(g_i_delete_list))
          ;
        EXCEPTION
          -- если блокировок уже нет, пропустим шаг
          WHEN NO_DATA_FOUND THEN NULL;
          -- Остальные исключения пробрасываем дальше
          WHEN OTHERS THEN RAISE;
        END;
        -- список обработали, очистим
        g_i_delete_list.DELETE;
      END IF; -- g_i_delete_list

      -- Обработаем список удалемых блокировок заявок
      IF (g_c_delete_list.COUNT > 0) THEN
        BEGIN
          DELETE FROM CASEMGMT.ECM_ENTITY_LOCK t1
          WHERE
            t1.ENTITY_NM = 'CASE' AND t1.LOCK_USER_ID = g_lock_user_id
            AND t1.ENTITY_RK IN (SELECT column_value FROM TABLE(g_c_delete_list))
          ;
        EXCEPTION
          -- если блокировок уже нет, пропустим шаг
          WHEN NO_DATA_FOUND THEN NULL;
          -- Остальные исключения пробрасываем дальше
          WHEN OTHERS THEN RAISE;
        END;
        -- список обработали, очистим
        g_c_delete_list.DELETE;
      END IF; -- g_c_delete_list

    END IF;

  END AFTER STATEMENT;

END ECM_ENTITY_LOCK_TRIGGER;