DataManipulationLanguage.sql

USE Bank;

DROP TRIGGER IF EXISTS TR_INIT_CUSTOMER;
DELIMITER //
CREATE TRIGGER TR_INIT_CUSTOMER
AFTER INSERT ON T_CUSTOMER
FOR EACH ROW
BEGIN
INSERT INTO T_ACCOUNT (ACCOUNT_ID, ACCOUNT_TYPE, AMOUNT) VALUES (NEW.ACCOUNT_ID, 1, 0);
INSERT INTO T_ACCOUNT (ACCOUNT_ID, ACCOUNT_TYPE, AMOUNT) VALUES (NEW.ACCOUNT_ID, 2, 0);
END//
DELIMITER ;

DROP TRIGGER IF EXISTS TR_ADD_ARCHIVE;
DELIMITER //
CREATE TRIGGER TR_ADD_ARCHIVE
BEFORE DELETE ON T_TRANSANCTION_HISTORY
FOR EACH ROW
BEGIN
INSERT INTO T_TRANSANCTION_HISTORY_ARCHIVE (ACCOUNT_ID, TRANSANCTION_TYPE, ACCOUNT_TYPE, AMOUNT, TRANSANCTION_DATE, UPDATED_AT)
	SELECT OLD.ACCOUNT_ID, OLD.TRANSANCTION_TYPE, OLD.ACCOUNT_TYPE, OLD.AMOUNT, OLD.TRANSANCTION_DATE, CURRENT_TIMESTAMP ;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_VIEW_BALANCE;
DELIMITER //
CREATE PROCEDURE SP_VIEW_BALANCE(IN
P_ACCOUNT_ID INT,
P_ACCOUNT_TYPE INT)
BEGIN
SELECT AMOUNT
FROM T_ACCOUNT
WHERE ACCOUNT_ID = P_ACCOUNT_ID AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;
END//Zz
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_WITHDRAW_AMOUNT;
DELIMITER //
CREATE PROCEDURE SP_WITHDRAW_AMOUNT(IN
P_ACCOUNT_ID INT,
P_ACCOUNT_TYPE INT,
P_NEW_BALANCE DOUBLE)
BEGIN
	UPDATE T_ACCOUNT 
	SET AMOUNT = AMOUNT - P_NEW_BALANCE 
	WHERE ACCOUNT_ID = P_ACCOUNT_ID 
		AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;
INSERT INTO T_TRANSANCTION_HISTORY (ACCOUNT_ID, TRANSANCTION_TYPE, ACCOUNT_TYPE, AMOUNT)
VALUES (P_ACCOUNT_ID, 3, P_ACCOUNT_TYPE, P_NEW_BALANCE);
SELECT AMOUNT FROM T_ACCOUNT WHERE ACCOUNT_ID = P_ACCOUNT_ID AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;
END//
DELIMITER ;


DROP PROCEDURE IF EXISTS SP_DEPOSIT_AMOUNT;
DELIMITER //
CREATE PROCEDURE SP_DEPOSIT_AMOUNT(IN
P_ACCOUNT_ID INT,
P_ACCOUNT_TYPE INT,
P_NEW_BALANCE DOUBLE)
BEGIN
UPDATE T_ACCOUNT
SET AMOUNT = AMOUNT + P_NEW_BALANCE
WHERE ACCOUNT_ID = P_ACCOUNT_ID AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;
INSERT INTO T_TRANSANCTION_HISTORY (ACCOUNT_ID, TRANSANCTION_TYPE, ACCOUNT_TYPE, AMOUNT)
VALUES (P_ACCOUNT_ID, 4, P_ACCOUNT_TYPE, P_NEW_BALANCE);
SELECT AMOUNT FROM T_ACCOUNT WHERE ACCOUNT_ID = P_ACCOUNT_ID AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_DELETE_ACCOUNT;
DELIMITER //
CREATE PROCEDURE SP_DELETE_ACCOUNT(IN
P_USERNAME VARCHAR(50),
P_PASSWORD VARCHAR(50))
BEGIN
UPDATE T_CUSTOMER SET ACTIVE = 0 WHERE USERNAME = P_USERNAME AND PASS = P_PASSWORD;

INSERT INTO T_TRANSANCTION_HISTORY (ACCOUNT_ID, TRANSANCTION_TYPE)
SELECT ACCOUNT_ID,6 FROM T_CUSTOMER WHERE USERNAME = P_USERNAME;

IF EXISTS(SELECT ACTIVE FROM T_CUSTOMER WHERE USERNAME = P_USERNAME AND PASS = P_PASSWORD AND ACTIVE = 0) THEN 
SELECT 1 AS result;
ELSE SELECT 0 AS result;
END IF;
END//
DELIMITER  ;

DROP PROCEDURE IF EXISTS SP_CREATE_ACCOUNT;
DELIMITER //
CREATE PROCEDURE SP_CREATE_ACCOUNT(IN
P_F_NAME VARCHAR(50),
P_L_NAME VARCHAR(50),
P_EMAIL VARCHAR(50), 
P_USERNAME VARCHAR(50),
P_PASS VARCHAR(50))
BEGIN
INSERT INTO T_CUSTOMER (EMAIL, F_NAME, L_NAME, USERNAME, PASS)
VALUES (P_EMAIL, P_F_NAME, P_L_NAME, P_USERNAME, P_PASS);

INSERT INTO T_TRANSANCTION_HISTORY (ACCOUNT_ID, TRANSANCTION_TYPE)
SELECT ACCOUNT_ID,7 FROM T_CUSTOMER WHERE USERNAME = P_USERNAME;

SELECT ACCOUNT_ID
FROM T_CUSTOMER
WHERE USERNAME = P_USERNAME ;
END//
DELIMITER ;



DROP PROCEDURE IF EXISTS SP_LOGIN;
DELIMITER //
CREATE PROCEDURE SP_LOGIN(IN
P_USERNAME VARCHAR(50),
P_PASSWORD VARCHAR(50))
BEGIN
	IF EXISTS(SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE USERNAME = P_USERNAME AND PASS = P_PASSWORD AND ACTIVE = 0)
		THEN SELECT -1 AS ACCOUNT_ID;
	ELSEIF EXISTS(SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE USERNAME = P_USERNAME AND PASS = P_PASSWORD AND ACTIVE = 1)
		THEN SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE USERNAME = P_USERNAME AND PASS = P_PASSWORD AND ACTIVE = 1;
	ELSE SELECT -2 AS ACCOUNT_ID;
	END IF;
	UPDATE T_CUSTOMER
	SET LAST_LOGIN_DATE = CURRENT_TIMESTAMP WHERE USERNAME = P_USERNAME;
END//
DELIMITER  ;

DROP PROCEDURE IF EXISTS SP_CHECK_USERNAME;
DELIMITER //
CREATE PROCEDURE SP_CHECK_USERNAME(IN
P_USERNAME VARCHAR(50))
BEGIN
IF EXISTS(SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE USERNAME = P_USERNAME) THEN SELECT 1 AS result;
ELSE SELECT 0 AS result;
END IF;
END//
DELIMITER  ;

DROP PROCEDURE IF EXISTS SP_TRANSFER_AMOUNT;
DELIMITER //
CREATE PROCEDURE SP_TRANSFER_AMOUNT(IN
P_ACCOUNT_ID INT,
P_ACCOUNT_TYPE INT,
P_TO_ACCOUNT_ID INT,
P_TO_ACCOUNT_TYPE INT,
P_NEW_BALANCE DOUBLE)
BEGIN
UPDATE T_ACCOUNT
SET AMOUNT = AMOUNT - P_NEW_BALANCE
WHERE ACCOUNT_ID = P_ACCOUNT_ID AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;

UPDATE T_ACCOUNT
SET AMOUNT = AMOUNT + P_NEW_BALANCE
WHERE ACCOUNT_ID = P_TO_ACCOUNT_ID AND ACCOUNT_TYPE = P_TO_ACCOUNT_TYPE;

INSERT INTO T_TRANSANCTION_HISTORY (ACCOUNT_ID, TRANSANCTION_TYPE, ACCOUNT_TYPE, AMOUNT)
VALUES (P_ACCOUNT_ID, 5, P_ACCOUNT_TYPE, 0-P_NEW_BALANCE);

INSERT INTO T_TRANSANCTION_HISTORY (ACCOUNT_ID, TRANSANCTION_TYPE, ACCOUNT_TYPE, AMOUNT)
VALUES (P_TO_ACCOUNT_ID, 5, P_TO_ACCOUNT_TYPE, P_NEW_BALANCE);

SELECT AMOUNT FROM T_ACCOUNT WHERE ACCOUNT_ID = P_ACCOUNT_ID AND ACCOUNT_TYPE = P_ACCOUNT_TYPE;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_ACCOUNT_EXISTS;
DELIMITER //
CREATE PROCEDURE SP_ACCOUNT_EXISTS(IN
P_ACCOUNT_ID INT)
BEGIN
	SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE ACCOUNT_ID = P_ACCOUNT_ID;
END//
DELIMITER ;

#CALL SP_ARCHIVE_HISTORY(current_timestamp())
DROP PROCEDURE IF EXISTS SP_ARCHIVE_HISTORY;
DELIMITER //
CREATE PROCEDURE SP_ARCHIVE_HISTORY(IN
P_DATE timestamp)
BEGIN
	SET SQL_SAFE_UPDATES = 0;
	UPDATE T_TRANSANCTION_HISTORY 
		SET UPDATED_AT=CURRENT_TIMESTAMP
		WHERE TRANSANCTION_DATE <= P_DATE;
	#FIRES A ARCHIVE TRIGGER
	DELETE FROM T_TRANSANCTION_HISTORY WHERE TRANSANCTION_DATE <= P_DATE;
    SET SQL_SAFE_UPDATES = 1;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_CHECK_HISTORY;
DELIMITER //
CREATE PROCEDURE SP_CHECK_HISTORY(IN
P_ACCOUNT_ID INT,
P_HISTORY_AMOUNT INT)
BEGIN
	SELECT * FROM V_USER_TRANS_HISTORY WHERE ACCOUNT_ID = P_ACCOUNT_ID LIMIT P_HISTORY_AMOUNT;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_TOTAL_CASH;
DELIMITER //
CREATE PROCEDURE SP_TOTAL_CASH()
BEGIN
	SELECT SUM(AMOUNT) AS totalCash FROM T_ACCOUNT;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS SP_REACTIVATE_ACCOUNT;
DELIMITER //
CREATE PROCEDURE SP_REACTIVATE_ACCOUNT(IN
P_ACCOUNT_ID INT,
P_PASSWORD VARCHAR(50),
P_USERNAME VARCHAR(50))
BEGIN
IF EXISTS(SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE ACCOUNT_ID = P_ACCOUNT_ID AND PASS = P_PASSWORD)
THEN
	IF EXISTS(SELECT ACCOUNT_ID FROM T_CUSTOMER WHERE USERNAME = P_USERNAME)
	THEN
		UPDATE T_CUSTOMER
		SET ACTIVE = 1
		WHERE USERNAME = P_USERNAME;

		SELECT 1 AS result;
	ELSE SELECT 0 as result;
	END IF;
ELSE SELECT -1 AS result;
END IF;
END//
DELIMITER ;

#GROUP BY REQUIREMENT
DROP PROCEDURE IF EXISTS SP_TOTAL_CUSTOMERS;
DELIMITER //
CREATE PROCEDURE SP_TOTAL_CUSTOMERS()
BEGIN
	SELECT ACTIVE, COUNT(*) AS TOTAL FROM T_CUSTOMER
	GROUP BY ACTIVE;
END//
DELIMITER ;

#CALL SP_ARCHIVE_HISTORY(current_timestamp())
DROP PROCEDURE IF EXISTS SP_CHECK_PREMIUM;
DELIMITER //
CREATE PROCEDURE SP_CHECK_PREMIUM(IN
P_ACCOUNT_ID INT)
BEGIN
	IF EXISTS(SELECT ACCOUNT_ID FROM V_PREMIUM_MEMBERS WHERE ACCOUNT_ID = P_ACCOUNT_ID)
	THEN SELECT true as result;
	ELSE SELECT false as result;
	END IF;
END//
DELIMITER ;



#TODO MISSING ONE MORE TRIGGER