Функции, процедури и тригери

Функции

Функција 1: Проверка дали корисникот има доволно баланс за BUY

CREATE OR REPLACE FUNCTION check_portfolio_balance(
    p_portfolio_id BIGINT,
    p_price FLOAT8,
    p_quantity INT
)
RETURNS BOOLEAN AS $$
DECLARE
    v_balance NUMERIC(18,2);
    v_total FLOAT8;
BEGIN
    SELECT balance INTO v_balance
    FROM portfolios
    WHERE id = p_portfolio_id;

    v_total := p_price * p_quantity;

    IF v_balance >= v_total THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

Функција 2: Вредност на портфолио за конкретен корисник

CREATE OR REPLACE FUNCTION get_portfolio_value(
    p_user_id BIGINT
)
RETURNS FLOAT8 AS $$
DECLARE
    v_value FLOAT8;
BEGIN
    SELECT SUM(ph.quantity * s.current_price) INTO v_value
    FROM portfolios p
    JOIN portfolio_holdings ph ON p.id = ph.portfolio_id
    JOIN stock s ON ph.stock_id = s.id
    WHERE p.user_id = p_user_id;

    RETURN COALESCE(v_value, 0);
END;
$$ LANGUAGE plpgsql;

Функција 3: цената треба автоматски да се менува кога ќе се случи нова трансакција

CREATE OR REPLACE FUNCTION update_stock_price()
RETURNS TRIGGER AS
$$
BEGIN
    UPDATE stock
    SET 
        last_price = current_price,
        current_price = (
            SELECT AVG(price)
            FROM trade_transaction
            WHERE stock_id = NEW.stock_id
        ),
        last_updated = NOW()
    WHERE id = NEW.stock_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Процедури

Процедура 1: Креирање на trade request од корисник

CREATE OR REPLACE PROCEDURE create_trade_request(
    p_portfolio_id BIGINT,
    p_price FLOAT8,
    p_quantity INT,
    p_stock_symbol VARCHAR(20),
    p_type VARCHAR(10)
)
AS $$
DECLARE
    v_user_id BIGINT;
BEGIN
    SELECT user_id INTO v_user_id
    FROM portfolios
    WHERE id = p_portfolio_id;

    IF p_type = 'BUY' THEN
        IF NOT check_portfolio_balance(p_portfolio_id, p_price, p_quantity) THEN
            RAISE EXCEPTION 'Недоволен баланс за купување. Portfolio ID: %', p_portfolio_id;
        END IF;
    END IF;

    INSERT INTO trade_request (
        portfolio_id, price_per_unit, quantity,
        status, stock_symbol, timestamp, type
    )
    VALUES (
        p_portfolio_id, p_price, p_quantity,
        'OPEN', p_stock_symbol, NOW(), p_type
    );

    RAISE NOTICE 'Trade request креиран за корисник % — % x % на %',
        v_user_id, p_type, p_quantity, p_stock_symbol;
END;
$$ LANGUAGE plpgsql;

Процедура 2: Одобрување на trade request од admin - креира broker order и settlement

CREATE OR REPLACE PROCEDURE approve_trade_request(
    p_trade_request_id BIGINT
)
AS $$
DECLARE
    v_tr trade_request%ROWTYPE;
    v_portfolio portfolios%ROWTYPE;
    v_broker_order_id BIGINT;
    v_stock_id BIGINT;
BEGIN

    SELECT * INTO v_tr
    FROM trade_request
    WHERE id = p_trade_request_id AND status = 'OPEN';

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Trade request % не постои или не е OPEN', p_trade_request_id;
    END IF;

    SELECT * INTO v_portfolio
    FROM portfolios
    WHERE id = v_tr.portfolio_id;


    SELECT id INTO v_stock_id
    FROM stock
    WHERE symbol = v_tr.stock_symbol;


    UPDATE trade_request
    SET status = 'CLOSED'
    WHERE id = p_trade_request_id;


    INSERT INTO broker_order (
        order_type, limit_price, quantity, status,
        executed_price, broker_fee, mse_reference_number,
        submitted_at, executed_at, trade_request_id
    )
    VALUES (
        v_tr.type, v_tr.price_per_unit, v_tr.quantity, 'EXECUTED',
        v_tr.price_per_unit, (random() * 50)::FLOAT8,
        'MSE-' || p_trade_request_id,
        v_tr.timestamp, NOW(), p_trade_request_id
    )
    RETURNING id INTO v_broker_order_id;


    INSERT INTO settlement_record (
        broker_order_id, user_id, stock_symbol, quantity,
        settled_price, total_cost, settlement_status, settled_at
    )
    VALUES (
        v_broker_order_id, v_portfolio.user_id, v_tr.stock_symbol,
        v_tr.quantity, v_tr.price_per_unit,
        v_tr.price_per_unit * v_tr.quantity,
        'SETTLED', NOW()
    );


    INSERT INTO trade_transaction (
        price, quantity, timestamp, type, origin, user_id, stock_id
    )
    VALUES (
        v_tr.price_per_unit, v_tr.quantity, NOW(),
        v_tr.type, 'INTERNAL', v_portfolio.user_id, v_stock_id
    );

    RAISE NOTICE 'Trade request % одобрен — Broker Order % креиран', 
        p_trade_request_id, v_broker_order_id;
END;
$$ LANGUAGE plpgsql;

Тригери

Тригер 1: При SELL - провери дали корисникот има доволно акции во holdings

CREATE OR REPLACE FUNCTION check_holdings_before_sell()
RETURNS TRIGGER AS $$
DECLARE
    v_stock_id BIGINT;
    v_available_qty INT;
BEGIN
    IF NEW.type = 'SELL' THEN
        SELECT id INTO v_stock_id
        FROM stock WHERE symbol = NEW.stock_symbol;

        SELECT quantity INTO v_available_qty
        FROM portfolio_holdings
        WHERE portfolio_id = NEW.portfolio_id
          AND stock_id = v_stock_id;

        IF NOT FOUND OR v_available_qty < NEW.quantity THEN
            RAISE EXCEPTION 'Недоволно акции за продажба. Достапно: %, Побарано: %',
                COALESCE(v_available_qty, 0), NEW.quantity;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_holdings_before_sell
BEFORE INSERT ON trade_request
FOR EACH ROW
EXECUTE FUNCTION check_holdings_before_sell();

Тригер 2: цената треба автоматски да се менува кога ќе се случи нова трансакција

CREATE TRIGGER trg_update_stock_price
AFTER INSERT ON trade_transaction
FOR EACH ROW
EXECUTE FUNCTION update_stock_price();