Untitled

DROP PROCEDURE IF EXISTS db_bukuku_solutions.validate_balance_before_delete;
DELIMITER //
CREATE FUNCTION db_bukuku_solutions.validate_balance_before_delete(
    jurnalId VARCHAR(36),
    cashbankId VARCHAR(36),
    userId VARCHAR(36),
    createdAt DATETIME
) RETURNS INT

BEGIN
    DECLARE CURSOR_JOURNAL_ID INT;
    DECLARE CURSOR_JOURNAL_CASHBANK_ID VARCHAR(190);
    DECLARE CURSOR_JOURNAL_USER_ID VARCHAR(190);
    DECLARE CURSOR_JOURNAL_AMOUNT INT;
    DECLARE CURSOR_JOURNAL_STATUS_CREDIT INT;
    DECLARE CURSOR_JOURNAL_CREATED_AT DATETIME;
    DECLARE CURSOR_JOURNAL_DELETED_AT DATETIME;

    DECLARE totalBalanceBeforeDelete INT DEFAULT 0;
    DECLARE VALID_FOR_DELETE INT DEFAULT 1;
    DECLARE BALANCE INT DEFAULT 0;
    DECLARE continueLoop INT DEFAULT FALSE;
    DECLARE cursor_journals CURSOR FOR
        SELECT id, cashbank_id, user_id, amount, status_credit, created_at, deleted_at
        from db_bukuku_solutions.journals
        where journals.cashbank_id = cashbankId
          and journals.user_id = userId
          and journals.created_at > createdAt
          and journals.id != jurnalId
          and journals.deleted_at is null;;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET continueLoop = TRUE;
    OPEN cursor_journals;

    SELECT sum(amount * status_credit)
    into totalBalanceBeforeDelete
    from db_bukuku_solutions.journals
    where journals.cashbank_id = cashbankId
      and journals.user_id = userId
      and journals.created_at < createdAt
      and journals.id != jurnalId
      and journals.deleted_at is null;

    IF totalBalanceBeforeDelete < 0 THEN
        set VALID_FOR_DELETE = 0;
        SELECT VALID_FOR_DELETE;
    END IF;

    SET BALANCE = totalBalanceBeforeDelete;
    loop_through_rows:
    LOOP
        FETCH cursor_journals INTO
            CURSOR_JOURNAL_ID,
            CURSOR_JOURNAL_CASHBANK_ID,
            CURSOR_JOURNAL_USER_ID,
            CURSOR_JOURNAL_AMOUNT,
            CURSOR_JOURNAL_STATUS_CREDIT,
            CURSOR_JOURNAL_CREATED_AT,
            CURSOR_JOURNAL_DELETED_AT;

        SET BALANCE = BALANCE + (CURSOR_JOURNAL_AMOUNT * CURSOR_JOURNAL_STATUS_CREDIT);

        IF BALANCE < 0 THEN
            SET VALID_FOR_DELETE = FALSE;
            LEAVE loop_through_rows;
        END IF;
        //
    end LOOP
    //
    close cursor_journals;
    SELECT VALID_FOR_DELETE;
END;
//