--liquibase formatted sql
--changeset eshardakov:create_procedure_p_delete_import_data runOnChange:true endDelimiter:/ labels:GASUCONORD-336
--comment АРМ Контроль поручений. Процедура удаления загруженных данных за определенное число.

--drop procedure if exists order_control.p_delete_import_data(timestamp, boolean);

create or replace procedure order_control.p_delete_import_data(delete_date in timestamp, update_obsolete in boolean default false)
language plpgsql
as $procedure$
/*
 * version: 1.1
 * date: 2024-10-07
 * author: eshardakov
 * task: GASUCONORD-336
 * description: АРМ Контроль поручений. Процедура удаления загруженных данных за определенное число.
 * version: 1.0, GASUCONORD-336, eshardakov, 2024-10-02 - Первоначальное добавление.
 * version: 1.1, GASUCONORD-336, eshardakov, 2024-10-07 - Добавил логирование.
 */
declare
  max_begin_ts timestamp;
  existence_data_t boolean;
  existence_data_f boolean;
  total_rows_deleted int8 := 0;
  deleted_count int8 := 0;
  log_id int8;
begin
  
  insert into order_control.import_data_delete_log (log_comment, launch_props)
  values ('Запуск процедуры удаления загруженных данных за определенное число'
        , delete_date || ' ' || update_obsolete);
 

  select max (begin_ts) 
    into max_begin_ts
    from order_control.project
   where obsolete is false;

  if (delete_date < max_begin_ts) and update_obsolete then
    raise exception 'Выбранный набор параметров приведет к появлению нескольких актуальных версий данных. Актуальная версия может быть только одна!';
  end if;
  
  if update_obsolete = true then
    select exists (
      select 1  
        from order_control.project
       where obsolete is false
         and begin_ts = delete_date
    ) into existence_data_t;
  else
   select exists (
      select 1  
        from order_control.project
       where obsolete is true
         and begin_ts = delete_date
    ) into existence_data_f;
  end if;

  if existence_data_t then 
  
    select count(*) into deleted_count from order_control."checkpoint" where begin_ts = delete_date;
    delete from order_control."checkpoint" where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.order_result where begin_ts = delete_date;
    delete from order_control.order_result where begin_ts = delete_date;   
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.order where begin_ts = delete_date;
    delete from order_control.order where begin_ts = delete_date;   
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.project where begin_ts = delete_date;
    delete from order_control.project where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.event where begin_ts = delete_date;
    delete from order_control.event where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.contract where begin_ts = delete_date;
    delete from order_control.contract where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.object where begin_ts = delete_date;
    delete from order_control.object where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select id
      into log_id
      from order_control.data_import_log
     where file_count = files_count_successful
       and error_info = '{"STATUS": "Успешно"}'
     order by import_date_start desc
     limit 1;

    update order_control.data_import_log
       set files_count_successful = -1
     where id = log_id;

    select max (begin_ts) 
      into max_begin_ts
      from order_control.project
     where obsolete is false;

	  update order_control.checkpoint_responsible
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.checkpoint_deadline_reschedule_reason
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.checkpoint_deadline_reschedule_problem
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.checkpoint
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_result_deadline_reschedule
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_result_link
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_result_problem
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.risk_ext
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_result
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.additional_funding
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.field_event
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.negative_fact
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_deadline_reschedule
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_executor
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_problem
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order_timing_history
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.related_order
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.order
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.event_value
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.event
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.object
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.contract
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;

      update order_control.project
         set obsolete = false, end_ts = order_control.f_max_timestamp()
       where begin_ts = max_begin_ts;
    end if;

  if existence_data_f or max_begin_ts = delete_date then
    select count(*) into deleted_count from order_control."checkpoint" where begin_ts = delete_date;
    delete from order_control."checkpoint" where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.order_result where begin_ts = delete_date;
    delete from order_control.order_result where begin_ts = delete_date;   
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.order where begin_ts = delete_date;
    delete from order_control.order where begin_ts = delete_date;   
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.project where begin_ts = delete_date;
    delete from order_control.project where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.event where begin_ts = delete_date;
    delete from order_control.event where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.contract where begin_ts = delete_date;
    delete from order_control.contract where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;

    select count(*) into deleted_count from order_control.object where begin_ts = delete_date;
    delete from order_control.object where begin_ts = delete_date;
    total_rows_deleted := total_rows_deleted + deleted_count;
  end if;

  insert into order_control.import_data_delete_log (log_date, log_comment, launch_props)
  values (current_timestamp
       , 'Процедура удаления успешно завершена | удалено ' || total_rows_deleted || ' строк'
       , delete_date || ' ' || update_obsolete);
end;
$procedure$;
/

/*
    Примеры запуска:
    1. Удаление данных за текущее число и обновление предыдущей версии: call order_control.p_delete_import_data(current_date, true);
    2. Удаление данных за определенное число: call order_control.p_delete_import_data('2024-10-01', false);
*/

comment on procedure order_control.p_delete_import_data is 'АРМ Контроль поручений. Процедура удаления загруженных данных за определенное число.';
/

-- Permissions
alter procedure order_control.p_delete_import_data(timestamp, boolean) owner to order_control;
grant all on procedure order_control.p_delete_import_data(timestamp, boolean) to order_control;

сделай запись ошибок в лог еще
потому что у меня в конце процедура успешно завершена, но это может быть не так )
т.е. их надо отлавливать и вставлять в лог, но всё равно raise делать