---

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

CREATE OR REPLACE PROCEDURE back_office.proc__change_status_accrual_repurchase_amount_payment(IN p_accrual_repurchase_amount_payment_id integer, IN p_status_id integer, IN p_user_id integer)
LANGUAGE plpgsql
AS $procedure$
declare
    v_old_status_id int;
    v_not_confirmed_amount int = 0;
    v_id_ndfl int;
begin
    -- Получаем предыдущий статус документа
    select
         status_id into v_old_status_id
    from back_office.accrual_repurchase_amount_payments
    where id = p_accrual_repurchase_amount_payment_id;

    -- Перевод с состояние "Принят"
    if v_old_status_id = 10 and p_status_id = 20 then
        select
            COUNT(*) into v_not_confirmed_amount
        from back_office.accrual_repurchase_amount_payments_details
        where
            accrual_repurchase_amount_payments_id = p_accrual_repurchase_amount_payment_id
            and not is_confirmed;

        if (v_not_confirmed_amount > 0) then
            raise exception 'В документе осталось % неподтвержденных ПС. Перевод в состояние "Принят" невозможен', v_not_confirmed_amount;
            return;
        end if;    
    end if;

    -- Перевод в состояние "Удален"
    if p_status_id = 40 then
        if not (
            select
                created_manually
            from back_office.accrual_repurchase_amount_payments
            where id = p_accrual_repurchase_amount_payment_id
        ) then
            raise exception 'Нельзя удалить документ, созданный автоматически';
            return;        
        end if;
        
        if v_old_status_id in (20, 30) then
            raise exception 'Нельзя удалить документ, находящийся в состоянии "%"', (select "name" from back_office.accrual_repurchase_amount_payments_statuses where id = v_old_status_id);
            return;
        end if;
    end if;

    update back_office.accrual_repurchase_amount_payments
    set status_id = p_status_id
    where id = p_accrual_repurchase_amount_payment_id;

    -- Если перевод с состояние "Принят", то создаем начисление НДФЛ
    if v_old_status_id = 10 and p_status_id = 20 then
        select id_ndfl into v_id_ndfl from ourpension.calc_ndfl_accrual_repurchase(p_accrual_repurchase_amount_payment_id, p_user_id) as id_ndfl;
        
        insert into back_office.ndfl_personal_income_information(individual_id, sharer_id, period_date, "service_type", income, npo_decision_payment_type_code, registration_period, accrual_type_code,
                                                                                                            accrual_document_id, accrual_category)
        select
            i.id,
            arapd.sharer_id,
            arap.date_of_period,
            c.service_type,
            arapd.amount,
            tc.decision_payment_type_code,
            arap."version",
            'OTHER_ACCR',
            arap.id,
            2
        from back_office.accrual_repurchase_amount_payments arap
        join back_office.accrual_repurchase_amount_payments_details arapd
        JOIN back_office.termination_contract_calculation tc ON tc.id = arapd.termination_contract_calculation_id
        left join ourpension.sharer s on s.id = arapd.sharer_id
        left join individual i on i.id = s.individual_id
        left join ourpension.contract c on c.id = s.contract_id
            on arapd.accrual_repurchase_amount_payments_id = arap.id
        where arap.id = p_accrual_repurchase_amount_payment_id;
    end if;
end;
$procedure$
;