DELIMITER $$ 

DROP TRIGGER IF EXISTS `accounts`$$

CREATE TRIGGER `accounts` BEFORE INSERT ON `accounts` 
    FOR EACH ROW BEGIN
	
	DECLARE last_runningcredit DOUBLE;
	DECLARE last_runningdebit DOUBLE;
	DECLARE last_accountruncredit DOUBLE;
	DECLARE last_accountrundebit DOUBLE;
	DECLARE tot_rows INTEGER;
	
	SELECT 
		runningcredit, runningdebit, id
	INTO 
		last_runningcredit, last_runningdebit, tot_rows
	FROM accounts 
	WHERE entityid = NEW.entityid AND account = NEW.account
	ORDER BY id DESC LIMIT 1;
	
	IF tot_rows > 0 THEN
		SET new.runningcredit = last_runningcredit + new.credit; 
		SET new.runningdebit = last_runningdebit + new.debit;
	ELSE
		SET new.runningcredit = new.credit; 
		SET new.runningdebit = new.debit;
	END IF;
	
	IF new.account = 1 THEN
		IF new.credit > 0 THEN
		    UPDATE tillclients SET b_current = b_current - new.credit WHERE id = new.entityid;
			UPDATE tillclients SET w_current = w_current - new.credit WHERE id = new.entityid;
		ELSE
		    UPDATE tillclients SET b_current = b_current + new.debit WHERE id = new.entityid;
			UPDATE tillclients SET w_current = w_current + new.debit WHERE id = new.entityid;
		END IF;
	ELSE
		IF new.credit > 0 THEN
		    UPDATE suppliers SET b_current = b_current + new.credit WHERE id = new.entityid;
			UPDATE suppliers SET w_current = w_current + new.credit WHERE id = new.entityid;
		ELSE
		    UPDATE suppliers SET b_current = b_current - new.debit WHERE id = new.entityid;
			UPDATE suppliers SET w_current = w_current - new.debit WHERE id = new.entityid;
		END IF;
	END IF;
	
	/***************/
	/*** ACCOUNT ***/
	SELECT 
		accountruncredit, accountrundebit, id
	INTO 
		last_accountruncredit, last_accountrundebit, tot_rows
	FROM accounts 
	WHERE account = NEW.account
	ORDER BY id DESC LIMIT 1;
	IF tot_rows > 0 THEN
		SET new.accountruncredit = last_accountruncredit + new.credit; 
		SET new.accountrundebit = last_accountrundebit + new.debit;
	ELSE
		SET new.accountruncredit = new.credit; 
		SET new.accountrundebit = new.debit;
	END IF;
	
	/**** UPDATE BALANCE ****/
	SELECT COUNT(*) INTO tot_rows FROM account_balance_entity
	WHERE entityid = NEW.entityid AND account = NEW.account;
	
	IF tot_rows > 0 THEN
		UPDATE account_balance_entity
		SET 
		entity_debit = new.runningdebit,
		entity_credit = new.runningcredit
		WHERE
		entityid= new.entityid AND account = new.account;
		
		UPDATE account_balance
		SET 
		account_debit = new.accountrundebit,
		account_credit = new.accountruncredit
		WHERE
		account = new.account;
	ELSE
		INSERT INTO account_balance_entity
		(
		entity_debit, 
		entity_credit,
		entityid,
		account
		)
		VALUES
		(
		new.runningdebit,
		new.runningcredit,
		new.entityid,
		new.account
		);
		SELECT COUNT(*) INTO tot_rows FROM account_balance
		WHERE account = NEW.account;
		
		IF tot_rows > 0 THEN
		
			UPDATE account_balance
			SET 
			account_debit = new.accountrundebit,
			account_credit = new.accountruncredit
			WHERE
			account = new.account;
		
		ELSE
			INSERT INTO account_balance
			(
			account_debit,
			account_credit,
			account
			)
			VALUES
			(
			new.accountrundebit,
			new.accountruncredit,
			new.account
			);
		END IF;
		
	END IF;
END;
$$

DELIMITER ;