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

Функции

Функција 1: Ажурира и враќа моменталната добивка/загуба на CFD позицијата врз основа на најновата цена на акциите.

CREATE OR REPLACE FUNCTION calculate_cfd_profit_loss(p_cfd_id BIGINT)
RETURNS FLOAT8
AS $$
DECLARE
    v_quantity INT;
    v_open_price FLOAT8;
    v_current_price FLOAT8;
    v_profit_loss FLOAT8;
BEGIN
    SELECT c.quantity,
           c.open_price,
           s.current_price
    INTO v_quantity,
         v_open_price,
         v_current_price
    FROM CFD_position c
    JOIN stock s ON c.stock_id = s.id
    WHERE c.id = p_cfd_id;

    v_profit_loss :=
        (v_current_price - v_open_price) * v_quantity;

    UPDATE CFD_position
    SET profit_loss = v_profit_loss
    WHERE id = p_cfd_id;

    RETURN v_profit_loss;
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: Проверка дали корисникот има доволно баланс за 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;

Процедури

Процедура 1: Одобрување на 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;

Процедура 2: Креирање на 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;

Тригери

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

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;

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

Тригер 2: При креирање на trade_request се креира broker_order

CREATE OR REPLACE FUNCTION create_broker_order_from_trade_request()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO broker_order (
        order_type,
        limit_price,
        quantity,
        status,
        executed_price,
        broker_fee,
        mse_reference_number,
        submitted_at,
        trade_request_id
    )
    VALUES (
        NEW.type,
        NEW.price_per_unit,
        NEW.quantity,
        'PENDING',
        NULL,
        ROUND((NEW.price_per_unit * NEW.quantity * 0.005)::numeric, 2), -- 0.5% fee
        'MSE-' || NEW.id || '-' || EXTRACT(EPOCH FROM NOW())::BIGINT,
        NOW(),
        NEW.id
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trg_create_broker_order
AFTER INSERT ON trade_request
FOR EACH ROW
EXECUTE FUNCTION create_broker_order_from_trade_request();

Тригер 2: При 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();