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

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

-- 1. Конверзија на валути
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;


-- 2. Проверка дали сметка е активна
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;


-- 3. Враќа вкупен неплатен долг на клиент (сума на сите PENDING/LATE рати)
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. Го пресметува очекуваниот износ на камата за штедна сметка за даден период
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_result          DECIMAL(20, 2);
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_capitalization = 'SIMPLE' THEN
        -- Проста камата: P * r * t
        v_result := v_balance * (v_interest_rate / 100.0) * (v_days / 365.0);
    ELSE
        -- Сложена камата: P * (1 + r)^t - P
        v_result := v_balance * (
            POWER(1 + (v_interest_rate / 100.0), v_days / 365.0) - 1
            );
    END IF;

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


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