---

-- DROP PROCEDURE back_office.pr_create_fin_oper_from_rops(int4, int4);

CREATE OR REPLACE PROCEDURE back_office.pr_create_fin_oper_from_rops(IN p_rops_decision integer, IN p_user_id integer)
LANGUAGE plpgsql
AS $procedure$
begin
/*
* DESCRIPTION: Процедура создания фин. операции при проведении решения возврата из РОПС
* AUTHOR: Сулима Роман (ri.sulima)
* CREATED: 2026-10-21
*
* HISTORY:
*/

drop table if exists operation;
create temporary table operation(
    operation_id integer not null,
    rops_decisions integer not null,
    sharer_id integer not null,
    period_date date,
    comment text,
    death_fact boolean
);


/* Заполнена временная таблица операций */
insert into operation(
    operation_id,
    rops_decisions,
    sharer_id,
    period_date,
    comment,
    death_fact
) select
    nextval('back_office.operation_sequence') as operation_id,
    rd.id,
    rd.sharer_id as sharer_id,
    rdr.create_date,
    rdr.comment,
    case
        when rdr.reason = 665 then true
        else false
    end
from back_office.rops_decisions rd
join back_office.rops_decisions_register rdr on rdr.id = rd.rops_decisions_register_id
where rdr.id = p_rops_decision;

/* Зарегистрированы операции начисления из резерва */
insert into back_office.operation(
    id,
    operation_type_id,
    comment,
    author_agent_id,
    effective_date,
    operation_date,
    period_date,
    created,
    version
) select
    o.operation_id,
    ot.id,
    o.comment,
    a.id,
    o.period_date,
    o.period_date,
    o.period_date,
    current_timestamp,
    current_timestamp
from operation o
join back_office.operation_type ot on ot.code =
    case
        when o.death_fact then 'TRANSFER_FROM_FUND_RESERVE'
        else 'RETURN_FROM_FUND_RESERVE'
    end
join ourpension.agent a on a.user_id = p_user_id;
    
/* Создана временная таблица транзакций */
drop table if exists "transaction";
create temporary table "transaction"(
    id int4 not NULL,
    operation_id int4 not NULL,
    debit_account_id int4 not NULL,
    credit_account_id int4 not NULL,
    value numeric(22, 2) not NULL,
    version timestamp,
    comment varchar(250)
);


insert into "transaction" (
    id,
    operation_id,
    debit_account_id,
    credit_account_id,
    value,
    version,
    comment
)
select
    nextval('BACK_OFFICE.TRANSACTION_SEQUENCE'),
    mnem.operation_id,

    case
        when mnem.death_fact then da.id
        else ca.id
    end as debit_account_id,

    case
        when mnem.death_fact then ca.id
        else da.id
    end as credit_account_id,

    mnem.payment_value,
    current_timestamp,
    cast(mnem.id as varchar)
from (
    select
        o.operation_id,
        o.death_fact,
        rd.id,
        rd.sharer_id,
        rdb.account_type_id,
        sum(rdb.value) as payment_value
    from operation o
    join back_office.rops_decisions rd
        on rd.id = o.rops_decisions
    join back_office.rops_decisions_balance rdb
        on rd.id = rdb.rops_decisions_id
    group by
        o.operation_id,
        o.death_fact,
        rd.id,
        rd.sharer_id,
        rdb.account_type_id
) mnem
join lateral (
    select
        da.id
    from back_office.account da
    where da.owner_sharer_id = mnem.sharer_id
     and da.account_type_id = mnem.account_type_id
) da on true
join lateral (
    select
        a.id
    from back_office.account a
    join back_office.account_type act
        on act.id = a.account_type_id
    where act.mnemonics in ('Р ОПС')
) ca on true;
    /*
insert into "transaction"(id, operation_id, debit_account_id, credit_account_id, value, version, comment)
select
    nextval('BACK_OFFICE.TRANSACTION_SEQUENCE'),
    mnem.operation_id,
    da.id,
    ca.id,
    mnem.payment_value,
    current_timestamp,
    cast(mnem.id as varchar)
from (
    select
        o.operation_id,
        rd.id,
        rd.sharer_id,
        rdb.account_type_id,
        sum(rdb.value) as payment_value
    from operation o
    join back_office.rops_decisions rd on rd.id = o.rops_decisions
    join back_office.rops_decisions_balance rdb on rd.id = rdb.rops_decisions_id
    group by o.operation_id, rd.id, rd.sharer_id, rdb.account_type_id
) mnem
join lateral (
    select
        da.id
    from back_office.account da
    where da.owner_sharer_id = mnem.sharer_id
        and da.account_type_id = mnem.account_type_id
) da on TRUE
inner join lateral (
    select a.id
    from back_office.account a
    inner join back_office.account_type act on act.id = a.account_type_id
    where act.mnemonics in ('Р ОПС')
) ca on true;
*/



/* Транзакции перенесены в БД */
insert into back_office.transaction_without_effective_date (id, operation_id, debit_account_id, credit_account_id, value, version, "comment")
select
    id,
    operation_id,
    debit_account_id,
    credit_account_id,
    value,
    version ,
    "comment"
from "transaction";

update back_office.rops_decisions rd
    set operation_id = o.operation_id
from operation o
where rd.id = o.rops_decisions;

end;
$procedure$
;