Функции, процедури, тригери : procedures.sql

--======================================================================================================================
-- PROCEDURES
--======================================================================================================================

--1.Трансфер во иста валута
--Процедурата овозможува трансфер на средства помеѓу две сметки кои користат иста валута.
-- Се проверува дали сметките се активни и дали има доволно средства за трансакцијата.
-- По успешниот трансфер, автоматски се евидентира нова трансакција во системот.
CREATE OR REPLACE PROCEDURE sp_transfer_same_currency(
    p_amount DECIMAL(20, 2),
    p_from_acc INT,
    p_to_acc INT,
    p_emp_counter INT
)
    LANGUAGE plpgsql AS
$$
DECLARE
    v_ref     VARCHAR(30);
    v_type_id INT;
    v_curr    INT;
BEGIN
    IF NOT fn_is_account_eligible(p_from_acc)
        OR NOT fn_is_account_eligible(p_to_acc) THEN
        RAISE EXCEPTION 'Nevalidni smetki';
    END IF;

    IF (SELECT balance FROM Account WHERE account_id = p_from_acc) < p_amount THEN
        RAISE EXCEPTION 'Nema dovolno sredstva';
    END IF;

    SELECT currency_id
    INTO v_curr
    FROM Account
    WHERE account_id = p_from_acc;

    IF v_curr != (SELECT currency_id
                  FROM Account
                  WHERE account_id = p_to_acc) THEN
        RAISE EXCEPTION 'Valutite ne se isti';
    END IF;

    v_ref := 'TRX-' || TO_CHAR(NOW(), 'YYYYMMDDHH');

    SELECT transaction_type_id
    INTO v_type_id
    FROM Transaction_type
    WHERE type_name = 'TRANSFER';

    UPDATE Account
    SET balance = balance - p_amount
    WHERE account_id = p_from_acc;

    UPDATE Account
    SET balance = balance + p_amount
    WHERE account_id = p_to_acc;

    INSERT INTO Transaction(amount, status, reference_number,
                            account_id, account_id2,
                            transaction_type_id,
                            currency_id, currency_id2,
                            emp_counter_id)
    VALUES (p_amount, 'COMPLETED', v_ref,
            p_from_acc, p_to_acc,
            v_type_id,
            v_curr, v_curr,
            p_emp_counter);
END;
$$;

CALL sp_transfer_same_currency(
    100,
    10360,
    25132,
    61735
);

SELECT account_id, balance
FROM Account
WHERE account_id IN (10360, 25132);




--2.Трансфер со различни валути
--Оваа процедура врши трансфер помеѓу сметки со различни валути. Пред префрлување на средствата,
-- износот автоматски се конвертира преку функцијата fn_convert_currency.
-- На крај се ажурираат салдата на двете сметки и се снима трансакцијата со соодветните валути.
CREATE OR REPLACE PROCEDURE sp_transfer_foreign_currency(
    p_amount DECIMAL(20, 2),
    p_from_acc INT,
    p_to_acc INT,
    p_emp_counter INT
)
    LANGUAGE plpgsql AS
$$
DECLARE
    v_from_curr INT;
    v_to_curr   INT;
    v_ref       VARCHAR(30);
    v_type_id   INT;
    v_converted_amount DECIMAL(20,2);
BEGIN
    IF NOT fn_is_account_eligible(p_from_acc)
        OR NOT fn_is_account_eligible(p_to_acc) THEN
        RAISE EXCEPTION 'Nevalidni smetki';
    END IF;

    IF (SELECT balance FROM Account WHERE account_id = p_from_acc) < p_amount THEN
        RAISE EXCEPTION 'Nema dovolno sredstva';
    END IF;

    SELECT currency_id
    INTO v_from_curr
    FROM Account
    WHERE account_id = p_from_acc;

    SELECT currency_id
    INTO v_to_curr
    FROM Account
    WHERE account_id = p_to_acc;

    v_converted_amount := fn_convert_currency(v_from_curr, v_to_curr, p_amount,'2026-04-12');

    v_ref := 'TRX-' || TO_CHAR(NOW(), 'YYYYMMDDHH');

    SELECT transaction_type_id
    INTO v_type_id
    FROM Transaction_type
    WHERE type_name = 'TRANSFER';

    UPDATE Account
    SET balance = balance - p_amount
    WHERE account_id = p_from_acc;

    UPDATE Account
    SET balance = balance + v_converted_amount
    WHERE account_id = p_to_acc;

    INSERT INTO Transaction(
        amount,
        status,
        reference_number,
        account_id,
        account_id2,
        transaction_type_id,
        currency_id,
        currency_id2,
        emp_counter_id
    )
    VALUES (
        v_converted_amount,
        'COMPLETED',
        v_ref,
        p_from_acc,
        p_to_acc,
        v_type_id,
        v_from_curr,
        v_to_curr,
        p_emp_counter
    );
END;
$$;

CALL sp_transfer_foreign_currency(
    100,
    66,
    63,
    61735
);

SELECT account_id, balance
FROM Account
WHERE account_id IN (66, 63);


-- 3. Уплата на средства на сметка
--Процедурата служи за уплата на средства на банкарска сметка. Покрај проверката дали сметката е активна,
-- се креира запис за трансакцијата и се ажурира салдото на сметката.
-- Дополнително, преку тригер автоматски се генерира и receipt за уплатата.
CREATE OR REPLACE PROCEDURE sp_deposit(
    p_account_id INT,
    p_amount DECIMAL(20, 2),
    p_currency_id INT,
    p_payment_method INT,
    p_emp_counter_id INT,
    p_description VARCHAR DEFAULT 'Уплата на средства'
)
    LANGUAGE plpgsql AS
$$
DECLARE
    v_deposit_type_id INT;
    v_ref_number      VARCHAR(30);
    v_transaction_id  INT;
BEGIN
    
    IF NOT fn_is_account_eligible(p_account_id) THEN
        RAISE EXCEPTION 'Smetkata ne e aktivna';
    END IF;

    SELECT transaction_type_id
    INTO v_deposit_type_id
    FROM Transaction_type
    WHERE type_name = 'DEPOSIT';

    v_ref_number := 'TRX-' || LPAD(v_transaction_id::TEXT, 10, '0');

    INSERT INTO Transaction (amount, transaction_date, status, description,
                             reference_number, account_id, transaction_type_id,
                             payment_method_id, currency_id, emp_counter_id)
    VALUES (p_amount, CURRENT_TIMESTAMP, 'COMPLETED', p_description,
            v_ref_number, p_account_id, v_deposit_type_id,
            p_payment_method, p_currency_id, p_emp_counter_id)
    RETURNING transaction_id INTO v_transaction_id;


    UPDATE Account
    SET balance = balance + p_amount
    WHERE account_id = p_account_id;

    RAISE NOTICE 'Уплатата е успешна. Transaction ID: %, Receipt автоматски генериран.', v_transaction_id;
END;
$$;

CALL sp_deposit(18, 5000.00, 91, 1, 61735);

SELECT *
FROM Transaction
WHERE account_id = 18
ORDER BY transaction_id DESC
LIMIT 1;



-- 4. Исплата на средства од сметка
--Оваа процедура овозможува исплата на средства од сметка. 
-- Системот најпрво проверува дали сметката е активна и дали има доволен баланс за исплата. 
-- По успешната трансакција, балансот се намалува и се евидентира нов запис во табелата Transaction.
CREATE OR REPLACE PROCEDURE sp_withdraw(
    p_account_id INT,
    p_amount DECIMAL(20, 2),
    p_currency_id INT,
    p_payment_method INT,
    p_emp_counter_id INT,
    p_description VARCHAR DEFAULT 'Исплата на средства'
)
    LANGUAGE plpgsql AS
$$
DECLARE
    v_withdrawal_type_id INT;
    v_ref_number         VARCHAR(30);
    v_current_balance    DECIMAL(20, 2);
    v_transaction_id     INT;
BEGIN
    IF NOT fn_is_account_eligible(p_account_id) THEN
        RAISE EXCEPTION 'Сметката не е активна';
    END IF;

    
    SELECT balance
    INTO v_current_balance
    FROM Account
    WHERE account_id = p_account_id;

    IF v_current_balance < p_amount THEN
        RAISE EXCEPTION 'Недоволен баланс: тековен=%, побаран=%.',
            v_current_balance, p_amount;
    END IF;

    SELECT transaction_type_id
    INTO v_withdrawal_type_id
    FROM Transaction_type
    WHERE type_name = 'WITHDRAWAL';

    v_ref_number := 'TRX-' || TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH');

    INSERT INTO Transaction (amount, transaction_date, status, description,
                             reference_number, account_id, transaction_type_id,
                             payment_method_id, currency_id, emp_counter_id)
    VALUES (p_amount, CURRENT_TIMESTAMP, 'COMPLETED', p_description,
            v_ref_number, p_account_id, v_withdrawal_type_id,
            p_payment_method, p_currency_id, p_emp_counter_id)
    RETURNING transaction_id INTO v_transaction_id;

    UPDATE Account
    SET balance = balance - p_amount
    WHERE account_id = p_account_id;

    RAISE NOTICE 'Исплатата е успешна. Transaction ID: %', v_transaction_id;
END;
$$;

CALL sp_withdraw(18, 200.00, 91, 1, 61735);

SELECT *
FROM Transaction
WHERE account_id = 18
ORDER BY transaction_id DESC
LIMIT 1;