-- ============================================================
-- ROLLBACK: Restaurar setpaymentstatusv2 a su estado original
-- Fecha: 2026-06-09
-- ============================================================
-- Usar este script SOLO si el fix temporal causa problemas
-- y se necesita restaurar el comportamiento anterior.
-- ============================================================

CREATE OR REPLACE FUNCTION public.setpaymentstatusv2(
    expense integer,
    status integer)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    rows_updated INT := 0;
    details RECORD;
    approval int :=0;
BEGIN
    SELECT id_approval_stat INTO approval FROM expenses where id_expense = expense;
    IF(approval = 1 ) --si el gasto esta aprobado se procede al pago
    THEN
      -- Validar estados permitidos
    IF status = 1 THEN
        UPDATE payments
        SET id_payment_status = status,
            payment_date = CURRENT_DATE
        WHERE id_expense = expense;
        GET DIAGNOSTICS rows_updated = ROW_COUNT;

        IF rows_updated = 0 THEN
            RETURN -1;
        END IF;

        -- Procesar solo si existen movimientos de stock
        IF EXISTS (
            SELECT 1
            FROM stock_movements st
            INNER JOIN purchase_detail pd ON st.id_expense = pd.id_expense
            WHERE pd.id_expense = expense
        ) THEN

            -- se obtiene el id del detalle de la compra
            --Ya no se necesita con el cambio nuevo
            /*SELECT id_expense
            INTO id_purchase_detail
            FROM purchase_detail
            WHERE id_expense = expense
            LIMIT 1;*/
            -- Actualizar movimiento
            UPDATE stock_movements 
            SET date_st_mov = CURRENT_DATE
            WHERE id_expense = expense;

            -- Obtener id movimiento
            --Ya no se necesita con el cambio nuevo
            /*SELECT id_sto_mov
            INTO id_stock_movement
            FROM stock_movements
            WHERE id_purchase_dt = id_purchase_detail
            LIMIT 1;*/

            -- Actualizar ajuste
            UPDATE stock_adjusments
            SET date_adjusment = CURRENT_DATE
            WHERE id_expense = expense;

            -- Actualizar stock
            FOR details IN
                SELECT id_product, quantity
                FROM purchase_detail
                WHERE id_expense = expense
            LOOP
                UPDATE stock
                SET stock = stock + details.quantity
                WHERE id_product = details.id_product;
            END LOOP;
            /*Cambio viejo
            UPDATE stock
            SET stock = stock + (
                SELECT *
                FROM purchase_detail
                WHERE id_purchase_dt = id_purchase_detail
            )
            WHERE id_product = (
                SELECT id_product
                FROM purchase_detail
                WHERE id_purchase_dt = id_purchase_detail
            );*/
        END IF;
        RETURN status;

    ELSIF status = 3 THEN
        -- Actualizar pago
        UPDATE payments
        SET id_payment_status = status,
            payment_date = CURRENT_DATE
        WHERE id_expense = expense;
        GET DIAGNOSTICS rows_updated = ROW_COUNT;

        IF rows_updated = 0 THEN
            RETURN -1;
        END IF;

        RETURN status;

    ELSE
        -- Status invalido
        RETURN -2;
    END IF;
    ELSE
    return -3; --si no esta aprobado o rechazado no se puede actualizar el pago
    END IF;
END;
$BODY$;

-- Confirmacion
SELECT 'Funcion setpaymentstatusv2 restaurada a su estado original' AS resultado;