= Напреден развој на базата =

== Валидација на трговски барања (Trade Request Validation) ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
 * Корисникот не смее да поднесе BUY барање ако неговото портфолио нема доволно готовина (balance) за покривање на вкупната вредност на барањето (price_per_unit * quantity)
 * Корисникот не смее да поднесе SELL барање за акција ако неговото портфолио не ги поседува бараните акции во доволна количина
 * Статусот на trade_request смее да биде само: PENDING, EXECUTED, CANCELLED
 * Типот на trade_request смее да биде само: BUY, SELL

=== Имплементација ===

==== Прилагодени домени (Custom Domains) ====

{{{
CREATE DOMAIN trade_status_domain AS VARCHAR(255)
CHECK (VALUE IN ('PENDING', 'COMPLETED', 'DECLINED'));

CREATE DOMAIN trade_type_domain AS VARCHAR(255)
CHECK (VALUE IN ('BUY', 'SELL'));

ALTER TABLE trade_request ALTER COLUMN status TYPE trade_status_domain;
ALTER TABLE trade_request ALTER COLUMN type   TYPE trade_type_domain;
}}}


==== Тригери ====

BEFORE INSERT тригер на trade_request кој ги валидира BUY и SELL барањата пред да бидат зачувани.
{{{
CREATE OR REPLACE FUNCTION validate_trade_request()
RETURNS TRIGGER AS $$
DECLARE
    v_balance        NUMERIC(18,2);
    v_total_cost     NUMERIC(18,2);
    v_owned_quantity INTEGER;
    v_stock_id       BIGINT;
BEGIN
    -- total cost of the request
    v_total_cost := NEW.price_per_unit * NEW.quantity;

    IF NEW.type = 'BUY' THEN
        -- Check  -  portfolio has enough balance
        SELECT p.balance
        INTO v_balance
        FROM portfolios p
        WHERE p.id = NEW.portfolio_id;

        IF v_balance IS NULL THEN
            RAISE EXCEPTION 'Portfolio with id=% not found', NEW.portfolio_id;
        END IF;

        IF v_balance < v_total_cost THEN
            RAISE EXCEPTION
                'Insufficient balance: portfolio has %, but trade requires %',
                v_balance, v_total_cost;
        END IF;

    ELSIF NEW.type = 'SELL' THEN
        --  stock_id from symbol
        SELECT s.id INTO v_stock_id
        FROM stock s
        WHERE s.symbol = NEW.stock_symbol;

        IF v_stock_id IS NULL THEN
            RAISE EXCEPTION 'Stock with symbol=% not found', NEW.stock_symbol;
        END IF;

        -- Sum all  stock in the portfolio
        SELECT COALESCE(SUM(ph.quantity), 0)
        INTO v_owned_quantity
        FROM portfolio_holdings ph
        WHERE ph.portfolio_id = NEW.portfolio_id
          AND ph.stock_id     = v_stock_id;

        IF v_owned_quantity < NEW.quantity THEN
            RAISE EXCEPTION
                'Insufficient holdings: portfolio owns % shares of %, but trade requires %',
                v_owned_quantity, NEW.stock_symbol, NEW.quantity;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_trade_request
BEFORE INSERT ON trade_request
FOR EACH ROW
EXECUTE FUNCTION validate_trade_request();
}}}


==== Погледи (Views) ====

Поглед за преглед на сите pending барања со информации за корисникот и портфолиото.

{{{
CREATE OR REPLACE VIEW pending_trade_requests AS
SELECT
    tr.id                                        AS trade_id,
    tr.type                                      AS trade_type,
    tr.stock_symbol,
    tr.quantity,
    tr.price_per_unit,
    (tr.price_per_unit * tr.quantity)::NUMERIC   AS total_value,
    tr.status,
    tr.timestamp,
    p.id                                         AS portfolio_id,
    p.balance                                    AS portfolio_balance,
    u.id                                         AS user_id,
    u.username,
    u.email
FROM trade_request tr
JOIN portfolios p ON tr.portfolio_id = p.id
JOIN users      u ON p.user_id       = u.id
WHERE tr.status = 'PENDING'
ORDER BY tr.timestamp DESC;
}}}


----

== Автоматско ажурирање на портфолио при извршување на трансакција ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
 * Кога се INSERT-ира нова трансакција од тип BUY, балансот на портфолиото на корисникот автоматски се намалува за (price * quantity), а во portfolio_holdings се додава или се ажурира avg_price на постоечкото холдинг за таа акција
 * Кога се INSERT-ира нова трансакција од тип SELL, балансот на портфолиото автоматски се зголемува за (price * quantity), а количината во portfolio_holdings се намалува; ако quantity достигне 0, записот се брише
 * avg_price при BUY се пресметува со weighted average формула: (стара_вредност + нова_вредност) / (стар_qty + нов_qty)

=== Имплементација ===

==== Тригери ====

AFTER INSERT тригер на transactions кој автоматски го ажурира балансот на портфолиото и portfolio_holdings.

{{{
CREATE OR REPLACE FUNCTION sync_portfolio_on_transaction()
RETURNS TRIGGER AS $$
DECLARE
    v_portfolio_id   BIGINT;
    v_existing_qty   INTEGER;
    v_existing_avg   NUMERIC(38,2);
    v_new_avg        NUMERIC(38,2);
    v_total_value    NUMERIC(38,2);
BEGIN
    v_total_value := NEW.price * NEW.quantity;

    --  portfolio from user
    SELECT p.id INTO v_portfolio_id
    FROM portfolios p
    WHERE p.user_id = NEW.user_id;

    IF v_portfolio_id IS NULL THEN
        RAISE EXCEPTION 'No portfolio found for user_id=%', NEW.user_id;
    END IF;

    IF NEW.type = 'BUY' THEN
        --  balance
        UPDATE portfolios
        SET balance = balance - v_total_value
        WHERE id = v_portfolio_id;

        -- chekc if holding already exists
        SELECT ph.quantity, ph.avg_price
        INTO v_existing_qty, v_existing_avg
        FROM portfolio_holdings ph
        WHERE ph.portfolio_id = v_portfolio_id
          AND ph.stock_id     = NEW.stock_id
        LIMIT 1;

        IF v_existing_qty IS NOT NULL THEN
            -- weighted average price
            v_new_avg := ((v_existing_avg * v_existing_qty) + (NEW.price * NEW.quantity))
                         / (v_existing_qty + NEW.quantity);

            UPDATE portfolio_holdings
            SET quantity  = quantity + NEW.quantity,
                avg_price = v_new_avg
            WHERE portfolio_id = v_portfolio_id
              AND stock_id     = NEW.stock_id;
        ELSE
            -- insert new holding lot
            INSERT INTO portfolio_holdings (id, quantity, avg_price, stock_id, portfolio_id)
            VALUES (
                nextval('portfolio_holdings_id_seq'),
                NEW.quantity,
                NEW.price,
                NEW.stock_id,
                v_portfolio_id
            );
        END IF;

    ELSIF NEW.type = 'SELL' THEN
        -- add proceeds to balance
        UPDATE portfolios
        SET balance = balance + v_total_value
        WHERE id = v_portfolio_id;

        -- reduce holding quantity
        UPDATE portfolio_holdings
        SET quantity = quantity - NEW.quantity
        WHERE portfolio_id = v_portfolio_id
          AND stock_id     = NEW.stock_id;

        -- remove holding if quantity reaches zero
        DELETE FROM portfolio_holdings
        WHERE portfolio_id = v_portfolio_id
          AND stock_id     = NEW.stock_id
          AND quantity    <= 0;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_portfolio_on_transaction
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION sync_portfolio_on_transaction();
}}}

==== Погледи (Views) ====
Поглед за преглед на вкупната вредност на портфолиото за секој корисник, со тековните пазарни цени.


{{{
CREATE OR REPLACE VIEW portfolio_summary AS
SELECT
    u.id                                                        AS user_id,
    u.username,
    p.id                                                        AS portfolio_id,
    p.balance                                                   AS cash_balance,
    COALESCE(SUM(ph.quantity * s.current_price), 0)            AS holdings_market_value,
    COALESCE(SUM(ph.quantity * ph.avg_price),    0)            AS holdings_cost_basis,
    COALESCE(SUM(ph.quantity * s.current_price)
           - SUM(ph.quantity * ph.avg_price),    0)            AS unrealized_pnl,
    p.balance + COALESCE(SUM(ph.quantity * s.current_price), 0) AS total_portfolio_value,
    COUNT(DISTINCT ph.stock_id)                                 AS num_stocks_held
FROM users u
JOIN portfolios         p  ON u.id       = p.user_id
LEFT JOIN portfolio_holdings ph ON p.id  = ph.portfolio_id
LEFT JOIN stock             s  ON ph.stock_id = s.id
WHERE u.role = 'USER'
GROUP BY u.id, u.username, p.id, p.balance
ORDER BY total_portfolio_value DESC;

}}}


----

== Валидација на Watchlist Alert прагови ==

=== Опис на барањата за податочни ограничувања ===

Системот мора да обезбеди дека:
 * Ако се зададени и двата прага (price_above и price_below), price_above мора да биде строго поголем од price_below; спротивното е логички невозможно
 * Барем еден од двата прагови мора да биде зададен (не смеат и двата да бидат NULL истовремено)
 * Праговите мора да бидат позитивни броеви (> 0)

=== Имплементација ===

==== Тригери ====

BEFORE INSERT OR UPDATE тригер на watchlist кој ги валидира alert праговите.

{{{
CREATE OR REPLACE FUNCTION validate_watchlist_thresholds()
RETURNS TRIGGER AS $$
BEGIN
    -- 1one threshold must be set
    IF NEW.price_above IS NULL AND NEW.price_below IS NULL THEN
        RAISE EXCEPTION
            'Watchlist entry must have at least one threshold set (price_above or price_below)';
    END IF;

    -- thresholds must be positive 
    IF NEW.price_above IS NOT NULL AND NEW.price_above <= 0 THEN
        RAISE EXCEPTION 'price_above must be a positive value, got %', NEW.price_above;
    END IF;

    IF NEW.price_below IS NOT NULL AND NEW.price_below <= 0 THEN
        RAISE EXCEPTION 'price_below must be a positive value, got %', NEW.price_below;
    END IF;

    -- price_above --- greater than price_below when both are set
    IF NEW.price_above IS NOT NULL AND NEW.price_below IS NOT NULL THEN
        IF NEW.price_above <= NEW.price_below THEN
            RAISE EXCEPTION
                'price_above (%) must be strictly greater than price_below (%)',
                NEW.price_above, NEW.price_below;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_watchlist_thresholds
BEFORE INSERT OR UPDATE ON watchlist
FOR EACH ROW
EXECUTE FUNCTION validate_watchlist_thresholds();
}}}

==== Функции / Stored Procedures ====

Функција која ги враќа сите watchlist записи за кои тековната цена на акцијата го надминала или паднала под зададениот праг (активни alerts).

{{{

CREATE OR REPLACE FUNCTION get_triggered_watchlist_alerts()
RETURNS TABLE (
    watchlist_id   BIGINT,
    user_id        BIGINT,
    username       TEXT,
    stock_id       BIGINT,
    symbol         TEXT,
    current_price  DOUBLE PRECISION,
    price_above    DOUBLE PRECISION,
    price_below    DOUBLE PRECISION,
    alert_type     TEXT
) AS $$
BEGIN
    RETURN QUERY
        SELECT
            w.id                 AS watchlist_id,
            u.id                 AS user_id,
            u.username::TEXT,
            s.id                 AS stock_id,
            s.symbol::TEXT,
            s.current_price,
            w.price_above,
            w.price_below,
            CASE
                WHEN w.price_above IS NOT NULL
                     AND s.current_price >= w.price_above THEN 'PRICE_ABOVE_TRIGGERED'
                WHEN w.price_below IS NOT NULL
                     AND s.current_price <= w.price_below THEN 'PRICE_BELOW_TRIGGERED'
            END::TEXT            AS alert_type
        FROM watchlist w
        JOIN stock  s ON w.stock_id = s.id
        JOIN users  u ON w.user_id  = u.id
        WHERE
            (w.price_above IS NOT NULL AND s.current_price >= w.price_above)
            OR
            (w.price_below IS NOT NULL AND s.current_price <= w.price_below)
        ORDER BY u.id, s.symbol;
END;
$$ LANGUAGE plpgsql;
}}}


==== Погледи (Views) ====

Поглед за преглед на сите watchlist записи со тековните цени и статус на alert.


{{{
CREATE OR REPLACE VIEW watchlist_with_alert_status AS
SELECT
    w.id                                                         AS watchlist_id,
    u.id                                                         AS user_id,
    u.username,
    s.id                                                         AS stock_id,
    s.symbol,
    s.name                                                       AS stock_name,
    s.current_price,
    w.price_above,
    w.price_below,
    CASE
        WHEN w.price_above IS NOT NULL
             AND s.current_price >= w.price_above THEN 'PRICE_ABOVE_TRIGGERED'
        WHEN w.price_below IS NOT NULL
             AND s.current_price <= w.price_below THEN 'PRICE_BELOW_TRIGGERED'
        ELSE 'WATCHING'
    END                                                          AS alert_status
FROM watchlist w
JOIN stock s ON w.stock_id = s.id
JOIN users u ON w.user_id  = u.id
ORDER BY alert_status DESC, u.username, s.symbol;
}}}

----

== Управување со OAuth токени (OAuth Token Expiry Management) ==

=== Опис на барањата за податочни ограничувања ===


Системот мора да обезбеди дека:
 * Не смее да се INSERT-ира нов OAuth токен за корисник кој веќе има активен (неистечен) токен за ист провајдер
 * При обид за користење на OAuth токен, системот мора да провери дали е истечен (expires_at < NOW()) и да фрли грешка ако е
 * Истечените токени треба автоматски да се чистат преку background job за да не се трупаат во базата

=== Имплементација ===

==== Тригери ====

BEFORE INSERT тригер на oauth_pending_links кој спречува дупликат активни токени за ист корисник и провајдер.

{{{
CREATE OR REPLACE FUNCTION validate_oauth_token_before_insert()
RETURNS TRIGGER AS $$
DECLARE
    v_existing_count INTEGER;
BEGIN
    -- check for existing non-expired token for same user and provider
    SELECT COUNT(*) INTO v_existing_count
    FROM oauth_pending_links
    WHERE user_id    = NEW.user_id
      AND provider   = NEW.provider
      AND expires_at > NOW();

    IF v_existing_count > 0 THEN
        RAISE EXCEPTION
            'User % already has an active OAuth token for provider %. Wait for it to expire or revoke it first.',
            NEW.user_id, NEW.provider;
    END IF;

    --  token is not created already expired
    IF NEW.expires_at <= NEW.created_at THEN
        RAISE EXCEPTION
            'expires_at (%) must be after created_at (%)',
            NEW.expires_at, NEW.created_at;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_oauth_token
BEFORE INSERT ON oauth_pending_links
FOR EACH ROW
EXECUTE FUNCTION validate_oauth_token_before_insert();
}}}

==== Функции / Stored Procedures ====

Функција за верификација на OAuth токен проверува дали токенот постои и не е истечен, и го враќа корисничкото id и провајдерот.

{{{
CREATE OR REPLACE FUNCTION verify_oauth_token(p_token VARCHAR(255))
RETURNS TABLE (
    user_id    BIGINT,
    provider   TEXT,
    email      TEXT,
    is_valid   BOOLEAN,
    reason     TEXT
) AS $$
DECLARE
    v_record oauth_pending_links%ROWTYPE;
BEGIN
    SELECT * INTO v_record
    FROM oauth_pending_links
    WHERE token = p_token;

    IF v_record.token IS NULL THEN
        RETURN QUERY SELECT
            NULL::BIGINT, NULL::TEXT, NULL::TEXT,
            FALSE, 'Token does not exist';
        RETURN;
    END IF;

    IF v_record.expires_at < NOW() THEN
        RETURN QUERY SELECT
            v_record.user_id,
            v_record.provider::TEXT,
            v_record.email::TEXT,
            FALSE, 'Token has expired';
        RETURN;
    END IF;

    RETURN QUERY SELECT
        v_record.user_id,
        v_record.provider::TEXT,
        v_record.email::TEXT,
        TRUE, 'Token is valid';
END;
$$ LANGUAGE plpgsql;
}}}

Процедура за рачно чистење на истечени OAuth токени (може да се повика и преку background job).



{{{
CREATE OR REPLACE PROCEDURE cleanup_expired_oauth_tokens()
LANGUAGE plpgsql AS $$
DECLARE
    v_deleted_count INTEGER;
BEGIN
    DELETE FROM oauth_pending_links
    WHERE expires_at < NOW();

    GET DIAGNOSTICS v_deleted_count = ROW_COUNT;

    RAISE NOTICE 'Cleaned up % expired OAuth token(s) at %', v_deleted_count, NOW();
END;
$$;
}}}

==== Погледи (Views) ====

Поглед за преглед на сите OAuth токени со нивниот статус (активен / истечен).

{{{
CREATE OR REPLACE VIEW oauth_tokens_status AS
SELECT
    o.token,
    o.user_id,
    u.username,
    u.email                                    AS user_email,
    o.provider,
    o.email                                    AS oauth_email,
    o.created_at,
    o.expires_at,
    CASE
        WHEN o.expires_at > NOW() THEN 'ACTIVE'
        ELSE                           'EXPIRED'
    END                                        AS token_status,
    EXTRACT(EPOCH FROM (o.expires_at - NOW()))::INTEGER AS seconds_until_expiry
FROM oauth_pending_links o
JOIN users u ON o.user_id = u.id
ORDER BY o.expires_at DESC;
}}}