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

--================================================================================================================
-- FUNCTIONS
--================================================================================================================

-- 1. Конверзија на валути
--Функцијата служи за конвертирање на паричен износ од една валута во друга според
-- курсевите зачувани во табелата Exchange_rate за одреден датум.
-- Доколку валутите се исти се враќа соодветниот износ,
-- а доколку не постои курс за дадениот датум се фрла exception.
CREATE OR REPLACE FUNCTION fn_convert_currency(
    p_from_curr INT,
    p_to_curr INT,
    p_amount DECIMAL(20, 2),
    p_date DATE DEFAULT CURRENT_DATE
)
    RETURNS DECIMAL(20, 2) AS
$$
DECLARE
    v_rate DECIMAL(15, 6);
BEGIN
    IF p_from_curr = p_to_curr THEN
        RETURN p_amount;
    END IF;

    SELECT (er_to.rate / er_from.rate)
    INTO v_rate
    FROM Exchange_rate er_from
             JOIN Exchange_rate er_to
                  ON er_to.currency_id = p_to_curr
                      AND er_to.date_updated = p_date
    WHERE er_from.currency_id = p_from_curr
      AND er_from.date_updated = p_date;

    IF v_rate IS NULL THEN
        RAISE EXCEPTION 'Nema kurs za %', p_date;
    END IF;

    RETURN ROUND(p_amount * v_rate, 2);
END;
$$ LANGUAGE plpgsql;

SELECT fn_convert_currency(2, 3, 100.00, '2026-04-12');


-- 2. Проверка дали сметка е активна
--Функцијата проверува дали даден клиент и неговата сметка се активни.
-- Се користи како безбедносна проверка пред извршување финансиски операции како трансфер, уплата или исплата.
-- Доколку сметката не постои или статусот не е ACTIVE, враќа FALSE.
CREATE OR REPLACE FUNCTION fn_is_account_eligible(p_account_id INT)
    RETURNS BOOLEAN AS
$$
DECLARE
    v_acc_status    VARCHAR(20);
    v_client_status VARCHAR(20);
BEGIN
    SELECT a.status, c.status
    INTO v_acc_status, v_client_status
    FROM Account a
             JOIN Client c ON a.client_id = c.client_id
    WHERE a.account_id = p_account_id;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    RETURN (v_acc_status = 'ACTIVE' AND v_client_status = 'ACTIVE');
END;
$$ LANGUAGE plpgsql;

SELECT fn_is_account_eligible(1003);


-- 3. Враќа вкупен неплатен долг на клиент (сума на сите PENDING/LATE рати)
--Функцијата го пресметува вкупниот неплатен долг на клиентот преку собирање
-- на сите рати со статус PENDING или LATE од одобрени кредити. Нејзината намена е следење
-- на задолженоста на клиентите, кредитна анализа.
-- Доколку клиентот нема неплатени обврски функцијата враќа вредност 0.
CREATE OR REPLACE FUNCTION fn_get_client_total_debt(p_client_id INT)
    RETURNS DECIMAL(20, 2) AS
$$
DECLARE
    v_total DECIMAL(20, 2);
BEGIN
    SELECT COALESCE(SUM(li.amount), 0)
    INTO v_total
    FROM Loan_installment li
             JOIN Loan l ON li.loan_id = l.loan_id
    WHERE l.client_id = p_client_id
      AND li.status IN ('PENDING', 'LATE')
      AND l.status = 'APPROVED';

    RETURN v_total;
END;
$$ LANGUAGE plpgsql;


SELECT fn_get_client_total_debt(318290);



-- 4. Враќа преостаната сума за отплата на кредит
--Функцијата враќа детален преглед за состојбата на конкретен кредит.
-- Се прикажуваат вкупниот износ, веќе исплатениот дел, преостанатиот долг,
-- како и бројот на доцнења и неплатени рати.
-- Доколку кредитот не постои, се прикажува соодветна грешка.
CREATE OR REPLACE FUNCTION fn_get_loan_remaining_amount(p_loan_id INT)
    RETURNS TABLE
            (
                loan_id          INT,
                total_amount     DECIMAL(15, 2),
                paid_amount      DECIMAL(15, 2),
                remaining_amount DECIMAL(15, 2),
                late_count       INT,
                pending_count    INT
            )
AS
$$
BEGIN
    RETURN QUERY
        SELECT l.loan_id,
               l.amount,
               COALESCE(SUM(li.amount) FILTER (WHERE li.status = 'PAID'), 0)::DECIMAL(15, 2),
               COALESCE(SUM(li.amount) FILTER (WHERE li.status != 'PAID'), 0)::DECIMAL(15, 2),
               COUNT(*) FILTER (WHERE li.status = 'LATE')::INT,
               COUNT(*) FILTER (WHERE li.status = 'PENDING')::INT
        FROM Loan l
                 JOIN Loan_installment li ON l.loan_id = li.loan_id
        WHERE l.loan_id = p_loan_id
        GROUP BY l.loan_id, l.amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Кредитот со ID % не постои.', p_loan_id;
    END IF;
END;
$$ LANGUAGE plpgsql;


SELECT *
FROM fn_get_loan_remaining_amount(86961);


-- 5. Го пресметува очекуваниот износ на камата за штедна сметка за даден период
--Оваа функција пресметува очекувана камата за штедна сметка во зададен временски период.
-- Поддржува едноставна (SIMPLE) и сложена (COMPOUND) камата, како и различни периоди на капитализација. 
-- Пресметката се базира на моменталното салдо и каматната стапка на сметката.
CREATE OR REPLACE FUNCTION fn_calculate_savings_interest(
    p_account_id INT,
    p_period_start DATE,
    p_period_end DATE
)
    RETURNS DECIMAL(20, 2) AS
$$
DECLARE
    v_balance              DECIMAL(20, 2);
    v_interest_rate        DECIMAL(5, 2);
    v_interest_period      VARCHAR(20);
    v_capitalization       VARCHAR(20);
    v_days                 INT;
    v_interest_result      DECIMAL(20, 2);

    v_time_factor_years    DECIMAL(20, 6);
    v_compounding_per_year INT;
BEGIN
    SELECT a.balance, sa.interest_rate, sa.interest_period, sa.capitalization_type
    INTO v_balance, v_interest_rate, v_interest_period, v_capitalization
    FROM Account a
    JOIN SavingsAccount sa ON a.account_id = sa.account_id
    WHERE a.account_id = p_account_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Сметка % не е штедна сметка или не постои.', p_account_id;
    END IF;

    v_days := p_period_end - p_period_start;

    IF v_interest_period = 'DAILY' THEN
        v_time_factor_years := v_days / 365.0;
        v_compounding_per_year := 365;

    ELSIF v_interest_period = 'MONTHLY' THEN
        v_time_factor_years := v_days / 365.0;
        v_compounding_per_year := 12;

    ELSE
        
        v_time_factor_years := v_days / 365.0;
        v_compounding_per_year := 1;
    END IF;

    -- SIMPLE INTEREST
    IF v_capitalization = 'SIMPLE' THEN

        v_interest_result :=
            v_balance * (v_interest_rate / 100.0) * v_time_factor_years;

    -- COMPOUND INTEREST
    ELSE

        v_interest_result :=
            v_balance * (
                POWER(
                    1 + (v_interest_rate / 100.0) / v_compounding_per_year,
                    v_compounding_per_year * v_time_factor_years
                ) - 1
            );

    END IF;

    RETURN ROUND(v_interest_result, 2);
END;
$$ LANGUAGE plpgsql;


SELECT fn_calculate_savings_interest(201, '2026-04-01', '2026-04-30');