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

--====================================================================================================================
-- TRIGGERS
--====================================================================================================================

-- 1.  Затворање на кредит кога сите рати се платени
--Овој тригер автоматски го затвора кредитот кога сите негови рати се означени како PAID.
-- Покрај промена на статусот во CLOSED, се поставува и датум на затворање на кредитот.
CREATE OR REPLACE FUNCTION fn_close_loan_when_paid()
    RETURNS TRIGGER AS
$$
DECLARE
    v_unpaid_count INT;
BEGIN
    IF NEW.loan_id IS NULL THEN
        RETURN NEW;
    END IF;

    IF NEW.status = 'PAID' AND OLD.status IS DISTINCT FROM 'PAID' THEN

        SELECT COUNT(*)
        INTO v_unpaid_count
        FROM Loan_installment
        WHERE loan_id = NEW.loan_id
          AND status != 'PAID'
          AND installment_id != NEW.installment_id;

        IF v_unpaid_count = 0 THEN
            UPDATE Loan
            SET status   = 'CLOSED',
                end_date = CURRENT_DATE
            WHERE loan_id = NEW.loan_id
              AND status = 'APPROVED';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_close_loan_when_paid ON Loan_installment;

CREATE TRIGGER trg_close_loan_when_paid
    AFTER UPDATE OF status
    ON Loan_installment
    FOR EACH ROW
EXECUTE FUNCTION fn_close_loan_when_paid();


-- 2. Превенција на негативен баланс
--Тригерот спречува сметката да има негативен баланс или да падне под минимално дозволеното салдо.
--Пред секое ажурирање на балансот се врши проверка и доколку условот не е исполнет се фрла грешка
CREATE OR REPLACE FUNCTION fn_prevent_negative_balance()
    RETURNS TRIGGER AS
$$
DECLARE
    v_min_balance DECIMAL(20, 2) := 0;
BEGIN
    SELECT COALESCE(minimum_balance, 0)
    INTO v_min_balance
    FROM SavingsAccount
    WHERE account_id = NEW.account_id;

    IF NEW.balance < v_min_balance THEN
        RAISE EXCEPTION 'Недоволен баланс';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_prevent_negative_balance ON Account;

CREATE TRIGGER trg_prevent_negative_balance
    BEFORE UPDATE OF balance
    ON Account
    FOR EACH ROW
EXECUTE FUNCTION fn_prevent_negative_balance();


-- 3. Логирање на сомнителни трансакции (Audit trail)
--Овој тригер автоматски логира сомнителни трансакции во посебна табела (suspicious_transaction_log).
--Со ова се овозможува полесно следење и анализа на потенцијално ризични активности.
CREATE TABLE IF NOT EXISTS Suspicious_transaction_log
(
    log_id         SERIAL PRIMARY KEY,
    transaction_id INT NOT NULL,
    account_id     INT,
    amount         DECIMAL(20, 2),
    status         VARCHAR(20),
    flagged_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason         VARCHAR(255)
);

CREATE OR REPLACE FUNCTION fn_log_suspicious_transaction()
    RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.amount > 9000 AND NEW.status IN ('PENDING', 'FAILED') THEN
        INSERT INTO Suspicious_transaction_log(transaction_id, account_id, amount, status, reason)
        VALUES (NEW.transaction_id, NEW.account_id, NEW.amount, NEW.status, 'High risk');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_log_suspicious_transaction ON Transaction;

CREATE TRIGGER trg_log_suspicious_transaction
    AFTER INSERT OR UPDATE OF status
    ON Transaction
    FOR EACH ROW
EXECUTE FUNCTION fn_log_suspicious_transaction();

INSERT INTO Transaction (
    transaction_id,
    account_id,
    amount,
    status
)
VALUES (
    10000019,
    18,
    15000.00,
    'FAILED'
);


-- 4. Ажурирање на Daily Report при комплетирана трансакција
--Тригерот автоматски го ажурира дневниот извештај за филијалата при секоја
-- успешно завршена трансакција (COMPLETED). Се зголемува бројот на трансакции и вкупниот промет
-- за тековниот ден. Ова овозможува автоматско генерирање статистика и финансиски извештаи.
CREATE OR REPLACE FUNCTION fn_update_daily_report()
    RETURNS TRIGGER AS
$$
DECLARE
    v_branch_id INT;
BEGIN
    IF NEW.status = 'COMPLETED'
        AND (TG_OP = 'INSERT' OR OLD.status IS DISTINCT FROM 'COMPLETED') THEN

        SELECT branch_id
        INTO v_branch_id
        FROM Account
        WHERE account_id = NEW.account_id;

        IF v_branch_id IS NULL THEN
            RETURN NEW;
        END IF;

        INSERT INTO Daily_report(report_date, total_transactions, total_amount, branch_id)
        VALUES (CURRENT_DATE, 1, NEW.amount, v_branch_id)
        ON CONFLICT DO NOTHING;

        UPDATE Daily_report
        SET total_transactions = total_transactions + 1,
            total_amount       = total_amount + NEW.amount
        WHERE report_date = CURRENT_DATE
          AND branch_id = v_branch_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_daily_report ON Transaction;

CREATE TRIGGER trg_update_daily_report
    AFTER INSERT OR UPDATE OF status
    ON Transaction
    FOR EACH ROW
EXECUTE FUNCTION fn_update_daily_report();


-- 5. Автоматско генерирање Receipt при COMPLETED трансакција
--Овој тригер автоматски генерира receipt за секоја успешно извршена трансакција.
-- Со тоа системот обезбедува автоматска евиденција и потврда за извршените трансакции.
CREATE OR REPLACE FUNCTION fn_auto_generate_receipt()
    RETURNS TRIGGER AS
$$
DECLARE
    v_receipt_number VARCHAR(30);
BEGIN
    IF NEW.status = 'COMPLETED'
        AND (TG_OP = 'INSERT' OR OLD.status IS DISTINCT FROM 'COMPLETED') THEN

        v_receipt_number := 'RCPT-' ||
                            TO_CHAR(NEW.transaction_date, 'YYYYMMDD') ||
                            '-' ||
                            LPAD(NEW.transaction_id::TEXT, 9, '0');

        IF NOT EXISTS (SELECT 1
                       FROM Receipt
                       WHERE transaction_id = NEW.transaction_id) THEN
            INSERT INTO Receipt(print_time, receipt_number, transaction_id)
            VALUES (CURRENT_TIMESTAMP, v_receipt_number, NEW.transaction_id);
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_auto_generate_receipt ON Transaction;

CREATE TRIGGER trg_auto_generate_receipt
    AFTER INSERT OR UPDATE OF status
    ON Transaction
    FOR EACH ROW
EXECUTE FUNCTION fn_auto_generate_receipt();