SET serveroutput ON size 1000000;

DROP TABLE Accounts;
DROP TABLE AccountProperties;



CREATE TABLE AccountProperties
(
    ID    int primary key,
    Name  VARCHAR(20),
    iRATE NUMBER,
    GP    NUMBER
);


INSERT INTO AccountProperties
VALUES (1, 'savings', 5, 1);
INSERT INTO AccountProperties
VALUES (2, 'current', 10, 2);
INSERT INTO AccountProperties
VALUES (3, 'deposit', 15, 3);
INSERT INTO AccountProperties
VALUES (4, 'savings', 20, 4);
INSERT INTO AccountProperties
VALUES (5, 'deposit', 25, 5);


CREATE TABLE Accounts
(
    ID               VARCHAR(40),
    Name             VARCHAR(20),
    AccCode          NUMBER,
    Balance          NUMBER(10, 2),
    LastDateInterest DATE,
    PRIMARY KEY (ID, AccCode),
    FOREIGN KEY (AccCode) REFERENCES AccountProperties (ID)
);


INSERT INTO Accounts
VALUES ('4018-300-101', 'Aqib', 1, 1000, TO_DATE('10/01/2019', 'DD/MM/YYYY'));
INSERT INTO Accounts
VALUES ('4018-300-102', 'Shakib ', 2, 10000, TO_DATE('10/01/2019', 'DD/MM/YYYY'));
INSERT INTO Accounts
VALUES ('4018-300-103', 'Salauddin', 3, 20000, TO_DATE('10/01/2019', 'DD/MM/YYYY'));
INSERT INTO Accounts
VALUES ('4018-300-104', 'Roni', 4, 30000, TO_DATE('10/01/2019', 'DD/MM/YYYY'));
INSERT INTO Accounts
VALUES ('4018-300-105', 'Rafat', 5, 40000, TO_DATE('10/01/2019', 'DD/MM/YYYY'));

CREATE OR REPLACE FUNCTION calculateProfit(account_ID IN VARCHAR2)
    RETURN NUMBER
    IS
    profit                  NUMBER;
    acc_code                NUMBER;
    interest_rate           NUMBER;
    grace_period            NUMBER;
    present_balance         NUMBER;
    divider                 NUMBER;
    last_date_withdrawn     DATE;
    final_updated_last_date DATE;
BEGIN
    SELECT AccCode INTO acc_code FROM Accounts WHERE ID = account_ID;
    SELECT Balance INTO present_balance FROM Accounts WHERE ID = account_ID;
    SELECT LastDateInterest INTO last_date_withdrawn FROM Accounts WHERE ID = account_ID;
    SELECT iRATE INTO interest_rate FROM AccountProperties WHERE ID = acc_code;
    SELECT GP INTO grace_period FROM AccountProperties WHERE ID = acc_code;
    IF grace_period = 1 THEN
        divider := 365;
        profit := (present_balance * interest_rate / divider) * FLOOR(SYSDATE - last_date_withdrawn);
        final_updated_last_date := SYSDATE;
    ELSIF grace_period = 2 THEN
        divider := 12;
        profit := (present_balance * interest_rate / divider) * FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn));
        final_updated_last_date := last_date_withdrawn + FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn)) * 30;
    ELSIF grace_period = 3 THEN
        divider := 4;
        profit := (present_balance * interest_rate / divider) *
                  FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider));
        final_updated_last_date :=
                    last_date_withdrawn +
                    FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider)) * 30 * (12 / divider);
    ELSIF grace_period = 4 THEN
        divider := 2;
        profit := (present_balance * interest_rate / divider) *
                  FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider));
        final_updated_last_date :=
                    last_date_withdrawn +
                    FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider)) * 30 * (12 / divider);
    ELSIF grace_period = 5 THEN
        divider := 1;
        profit := (present_balance * interest_rate) * FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / 12);
        final_updated_last_date :=
                    last_date_withdrawn +
                    FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider)) * 30 * (12 / divider);
    END IF;
    RETURN profit;
END;
/

DECLARE
    ans NUMBER;
BEGIN
    ans := calculateProfit('4018-300-101');
    DBMS_OUTPUT.PUT_LINE(ans);
END;
/


CREATE OR REPLACE PROCEDURE update_values
    IS
    profit                  NUMBER;
    acc_code                NUMBER;
    grace_period            NUMBER;
    divider                 NUMBER;
    last_date_withdrawn     DATE;
    final_updated_last_date DATE;
    CURSOR lists IS SELECT *
                    FROM Accounts;
BEGIN
    FOR ele IN lists
        LOOP
            profit := calculateProfit(ele.ID);
            acc_code := ele.AccCode;
            SELECT GP INTO grace_period FROM AccountProperties WHERE ID = ele.ID;
            IF profit > 0 THEN
                IF grace_period = 1 THEN
                    divider := 365;
                    final_updated_last_date := SYSDATE;
                ELSIF grace_period = 2 THEN
                    divider := 12;
                    final_updated_last_date :=
                                last_date_withdrawn + FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn)) * 30;
                ELSIF grace_period = 3 THEN
                    divider := 4;
                    final_updated_last_date :=
                                last_date_withdrawn +
                                FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider)) * 30 *
                                (12 / divider);
                ELSIF grace_period = 4 THEN
                    divider := 2;
                    final_updated_last_date :=
                                last_date_withdrawn +
                                FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider)) * 30 *
                                (12 / divider);
                ELSIF grace_period = 5 THEN
                    divider := 1;
                    final_updated_last_date :=
                                last_date_withdrawn +
                                FLOOR(MONTHS_BETWEEN(SYSDATE, last_date_withdrawn) / (12 / divider)) * 30 *
                                (12 / divider);
                END IF;

                UPDATE Accounts
                SET LastDateInterest = final_updated_last_date,
                    Balance          = Balance + profit
                WHERE ID = ele.ID;
            END IF;
        END LOOP;
END;
/