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

--======================================================================================================================
-- 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 := 'TRF-' || TO_CHAR(NOW(), 'YYYYMMDDHH24MISS');

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

    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;
$$;

--2. Трансфер на сметки во различна валута
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;
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_ref := 'TRF-' || TO_CHAR(NOW(), 'YYYYMMDDHH24MISS');

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

    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_from_curr, v_to_curr,
            p_emp_counter);
END;
$$;


-- 3. Уплата на средства на сметка
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;
    -- Тригерот trg_update_account_balance го ажурира балансот
    -- Тригерот trg_auto_generate_receipt генерира Receipt

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

CALL sp_deposit(1001, 5000.00, 1, 1, 61735);



-- 4. Исплата на средства од сметка
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-' || 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_withdrawal_type_id,
            p_payment_method, p_currency_id, p_emp_counter_id)
    RETURNING transaction_id INTO v_transaction_id;
    -- Тригерот trg_prevent_negative_balance штити од негативен баланс
    -- Тригерот trg_update_account_balance го ажурира балансот

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

CALL sp_withdraw(1001, 1500.00, 1, 1, 61735);