DROP PROCEDURE IF EXISTS sp_updateAccBalance;

CREATE PROCEDURE sp_updateAccBalance()
BEGIN

    DROP TEMPORARY TABLE IF EXISTS TMP_ACC_BLANCE;

	CREATE TEMPORARY TABLE TMP_ACC_BLANCE(
		ACC_HD_ID INT DEFAULT 0,
        REF_IDS TEXT(500000) NOT NULL,
		AMT DECIMAL(10,2) NULL DEFAULT 0
	);

    INSERT INTO TMP_ACC_BLANCE(
        ACC_HD_ID,
        REF_IDS,
        AMT
    )
    SELECT
        voucher_details.account_head_id,
        GROUP_CONCAT(voucher_narration.id),
        SUM(CASE WHEN voucher_details.debit_credit_flag = 'C' THEN (0 - voucher_details.voucher_amount) ELSE voucher_details.voucher_amount END) AMT
    FROM
        voucher_narration 
        JOIN voucher_details ON voucher_narration.id = voucher_details.voucher_narration_id
    WHERE
        voucher_narration.voucher_status = 'A' 
        AND voucher_narration.is_balance_generated = 'N'
    GROUP BY voucher_details.account_head_id
    ORDER BY voucher_details.account_head_id;

    SET @UPDATED = '0';

    SET @COUNTER = 1; /* LOOP COUNTER */
    SELECT COUNT(1) INTO @MAX_COUNT FROM TMP_ACC_BLANCE; /* LOOP'S MAX ITERATION COUNTER */
    ACC_BAL_UPDT:LOOP
        IF @COUNTER > @MAX_COUNT THEN
            LEAVE ACC_BAL_UPDT;
        END IF;

        PREPARE stmt FROM "SELECT 
                                ACC_HD_ID,
                                REF_IDS,
                                AMT
                            INTO 
                                @ACC_HD_ID,
                                @REF_IDS,
                                @AMT
                            FROM TMP_ACC_BLANCE 
                            LIMIT ?, 1;";
        EXECUTE stmt USING @COUNTER;
        DEALLOCATE PREPARE stmt;

        SELECT COUNT(1) INTO @HAS_RECORD FROM acc_balance WHERE acc_balance.acc_code = @ACC_HD_ID;
        IF @HAS_RECORD > 0 THEN
            UPDATE acc_balance SET acc_balance.balance_amount = acc_balance.balance_amount + @AMT, acc_balance.balance_date = CURDATE() WHERE acc_balance.acc_code = @ACC_HD_ID;
        ELSE
            INSERT INTO acc_balance VALUES(CURDATE(),@ACC_HD_ID,@AMT);
        END IF;

        SET @UPDATED = CONCAT(@UPDATED, @REF_IDS);

        SET @COUNTER = @COUNTER + 1;
    END LOOP ACC_BAL_UPDT;

    SET @QRY = CONCAT("UPDATE voucher_narration SET voucher_narration.is_balance_generated = 'Y' WHERE id IN(", @UPDATED,");");
    PREPARE BALANCE_UPDT_STMNT FROM @QRY;
    EXECUTE BALANCE_UPDT_STMNT;
    DEALLOCATE PREPARE BALANCE_UPDT_STMNT;
END;