--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.0
 * date: 2024-10-02
 * 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;
  log_id int8;
begin

  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 
  
    delete from order_control."checkpoint" where begin_ts = delete_date;   
    delete from order_control.order_result where begin_ts = delete_date;   
    delete from order_control.order where begin_ts = delete_date;   
    delete from order_control.project where begin_ts = delete_date;
    delete from order_control.event where begin_ts = delete_date;
    delete from order_control.contract where begin_ts = delete_date;
    delete from order_control.object where begin_ts = delete_date;

    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;

	  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
    delete from order_control."checkpoint" where begin_ts = delete_date;   
    delete from order_control.order_result where begin_ts = delete_date;   
    delete from order_control.order where begin_ts = delete_date;   
    delete from order_control.project where begin_ts = delete_date;
    delete from order_control.event where begin_ts = delete_date;
    delete from order_control.contract where begin_ts = delete_date;
    delete from order_control.object where begin_ts = delete_date;
  end if;
end;
$procedure$;
/

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;
сделай для этой процедуры таблицу с её логами