= Други Развојни Активности =

== Анализа на перформанси ==

=== Извештај за диверзификација на портфолио (report_portfolio_diversification) ===

Анализата на перформанси се врши врз основа на моменталната состојба во базата.

'''SQL:'''
{{{
CREATE OR REPLACE FUNCTION report_portfolio_diversification()
    RETURNS TABLE (
        user_id                   INTEGER,
        username                  TEXT,
        portfolio_id              INTEGER,
        portfolio_cash_balance    NUMERIC,
        total_holdings_value      NUMERIC,
        total_portfolio_value     NUMERIC,
        num_distinct_stocks       BIGINT,
        total_lots                BIGINT,
        unrealized_pnl            NUMERIC,
        unrealized_pnl_pct        NUMERIC,
        hhi_score                 NUMERIC,
        risk_classification       TEXT,
        dominant_stock_symbol     TEXT,
        dominant_stock_weight_pct NUMERIC,
        max_single_weight_pct     NUMERIC,
        min_single_weight_pct     NUMERIC,
        diversification_rank      BIGINT
    ) AS $$
BEGIN
    RETURN QUERY
        WITH
        holding_values AS (
            SELECT
                ph.portfolio_id                                                             AS hv_portfolio_id,
                ph.stock_id                                                                 AS hv_stock_id,
                s.symbol                                                                    AS hv_symbol,
                ph.quantity::NUMERIC * s.current_price::NUMERIC                            AS market_value,
                ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC)  AS unrealized_pnl
            FROM portfolio_holdings ph
            JOIN stock s ON ph.stock_id = s.id
            WHERE ph.quantity > 0
        ),
        stock_weights AS (
            SELECT
                hv.hv_portfolio_id,
                hv.hv_stock_id,
                hv.hv_symbol,
                SUM(hv.market_value)   AS stock_market_value,
                SUM(hv.unrealized_pnl) AS stock_unrealized_pnl,
                COUNT(*)               AS lots_count
            FROM holding_values hv
            GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol
        ),
        portfolio_totals AS (
            SELECT
                sw.hv_portfolio_id,
                SUM(sw.stock_market_value)     AS total_holdings_value,
                SUM(sw.stock_unrealized_pnl)   AS total_unrealized_pnl,
                COUNT(DISTINCT sw.hv_stock_id) AS num_stocks,
                SUM(sw.lots_count)             AS total_lots
            FROM stock_weights sw
            GROUP BY sw.hv_portfolio_id
        ),
        hhi_calc AS (
            SELECT
                sw.hv_portfolio_id AS hhi_portfolio_id,
                SUM(POWER(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), 2::NUMERIC)) AS hhi,
                MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC)     AS max_weight_pct,
                MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC)     AS min_weight_pct
            FROM stock_weights sw
            JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
            GROUP BY sw.hv_portfolio_id
        ),
        dominant_stock AS (
            SELECT DISTINCT ON (sw.hv_portfolio_id)
                sw.hv_portfolio_id AS ds_portfolio_id,
                sw.hv_symbol       AS dominant_symbol,
                ROUND(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC, 2) AS dominant_weight_pct
            FROM stock_weights sw
            JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
            ORDER BY sw.hv_portfolio_id,
                     sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
        )
        SELECT
            u.id::INTEGER,
            u.username::TEXT,
            p.id::INTEGER,
            p.balance::NUMERIC,
            COALESCE(pt.total_holdings_value, 0::NUMERIC),
            (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)),
            COALESCE(pt.num_stocks, 0)::BIGINT,
            COALESCE(pt.total_lots, 0)::BIGINT,
            COALESCE(pt.total_unrealized_pnl, 0::NUMERIC),
            ROUND(COALESCE(pt.total_unrealized_pnl, 0::NUMERIC)
                / NULLIF(pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC), 0::NUMERIC) * 100::NUMERIC, 2),
            ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4),
            CASE
                WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK'
                WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK'
                ELSE                      'WELL DIVERSIFIED'
            END::TEXT,
            COALESCE(ds.dominant_symbol, 'N/A')::TEXT,
            COALESCE(ds.dominant_weight_pct, 0::NUMERIC),
            COALESCE(hhi.max_weight_pct, 0::NUMERIC),
            COALESCE(hhi.min_weight_pct, 0::NUMERIC),
            RANK() OVER (ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC)::BIGINT
        FROM users u
        JOIN portfolios            p   ON u.id = p.user_id
        LEFT JOIN portfolio_totals pt  ON p.id = pt.hv_portfolio_id
        LEFT JOIN hhi_calc         hhi ON p.id = hhi.hhi_portfolio_id
        LEFT JOIN dominant_stock   ds  ON p.id = ds.ds_portfolio_id
        WHERE u.role = 'USER'
        ORDER BY hhi_score ASC, total_portfolio_value DESC;
END;
$$ LANGUAGE plpgsql;
}}}


'''EXPLAIN ANALYZE без индекси:'''
{{{
EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();

-- резултат:
QUERY PLAN                                                                                                                           |
-------------------------------------------------------------------------------------------------------------------------------------+
Function Scan on report_portfolio_diversification  (cost=0.25..10.25 rows=1000 width=416) (actual time=10.842..10.843 rows=2 loops=1)|
Planning Time: 0.084 ms                                                                                                              |
Execution Time: 12.265 ms                                                                                                            |

}}}



'''Индекси:'''

**1. portfolio_holdings - Index (portfolio_id)**
{{{
CREATE INDEX idx_ph_portfolio_id
ON portfolio_holdings(portfolio_id);
}}}
Користење: GROUP BY и JOIN по `portfolio_id` во CTE-ата `stock_weights` и `portfolio_totals`

Подобрување: Index Scan наместо Seq Scan при групирање по портфолио

---

**2. portfolio_holdings - Index (stock_id)**
{{{
CREATE INDEX idx_ph_stock_id
ON portfolio_holdings(stock_id);
}}}
Користење: JOIN `ph.stock_id = s.id` во `holding_values` CTE

Подобрување: Директен lookup наместо Hash Join со Seq Scan

---

**3. portfolio_holdings - Partial Index (quantity > 0)**
{{{
CREATE INDEX idx_ph_quantity_positive
ON portfolio_holdings(portfolio_id, stock_id)
WHERE quantity > 0;
}}}
Користење: WHERE филтер `ph.quantity > 0` во `holding_values` CTE

Подобрување: Индексот ги содржи само редовите со quantity > 0, елиминирајќи ги нулираните холдинзи уште при скенирањето

---

**4. users - Index (role)**
{{{
CREATE INDEX idx_users_role
ON users(role);
}}}
Користење: WHERE филтер `u.role = 'USER'` во финалниот SELECT

Подобрување: Директен Index Scan наместо Seq Scan на целата users табела

---

'''EXPLAIN ANALYZE со индекси:'''
{{{
EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();

--  резултат:
                                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------+
Function Scan on report_portfolio_diversification  (cost=0.25..10.25 rows=1000 width=416) (actual time=4.145..4.146 rows=2 loops=1)|
Planning Time: 0.070 ms                                                                                                            |
Execution Time: 4.212 ms                                                                                                           |
}}}

'''Заклучок:'''
Имаме забрзување.


----
=== Извештај за волатилност на акции (report_stock_volatility_and_activity) ===
'''SQL:'''
{{{
CREATE OR REPLACE FUNCTION report_stock_volatility_and_activity(
    p_from_date TIMESTAMP DEFAULT NULL,
    p_to_date   TIMESTAMP DEFAULT NULL
)
    RETURNS TABLE (
        stock_id                  INTEGER,
        symbol                    TEXT,
        stock_name                TEXT,
        current_price             NUMERIC,
        period_min_price          NUMERIC,
        period_max_price          NUMERIC,
        price_range_pct           NUMERIC,
        avg_daily_price           NUMERIC,
        historical_volatility_pct NUMERIC,
        total_history_points      BIGINT,
        total_txn_volume          NUMERIC,
        total_txn_count           BIGINT,
        buy_txn_count             BIGINT,
        sell_txn_count            BIGINT,
        buy_sell_ratio            NUMERIC,
        active_watchlist_users    BIGINT,
        pending_trade_requests    BIGINT,
        trend_direction           TEXT,
        trend_slope               NUMERIC,
        activity_score            NUMERIC,
        volatility_rank           BIGINT,
        activity_rank             BIGINT
    ) AS $$
BEGIN
    RETURN QUERY
        WITH
        -- Volatility: stddev of daily returns computed with LAG window function
        -- computing daily return per row using LAG window function
        daily_returns AS (
            SELECT
                sh.stock_id                AS dr_stock_id,
                sh.price                   AS dr_price,
                (sh.price - LAG(sh.price) OVER (
                    PARTITION BY sh.stock_id ORDER BY sh.timestamp
                )) / NULLIF(LAG(sh.price) OVER (
                    PARTITION BY sh.stock_id ORDER BY sh.timestamp
                ), 0)                      AS dr_daily_return
            FROM stock_history sh
            WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date)
              AND (p_to_date   IS NULL OR sh.timestamp <= p_to_date)
        ),
        -- aggregate per stock 
        price_stats AS (
            SELECT
                dr.dr_stock_id             AS ps_stock_id,
                COUNT(*)                   AS ps_total_points,
                MIN(dr.dr_price)           AS ps_min_price,
                MAX(dr.dr_price)           AS ps_max_price,
                AVG(dr.dr_price)           AS ps_avg_price,
                COALESCE(STDDEV(dr.dr_daily_return) * 100, 0) AS ps_volatility_pct
            FROM daily_returns dr
            GROUP BY dr.dr_stock_id
        ),
        -- trend least-squares linear regression slope on (row_number, price)
      
        price_ordered AS (
            SELECT
                sh.stock_id            AS po_stock_id,
                ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp) AS po_rn,
                sh.price                   AS po_price
            FROM stock_history sh
            WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date)
              AND (p_to_date   IS NULL OR sh.timestamp <= p_to_date)
        ),
        trend_calc AS (
            SELECT
                po.po_stock_id             AS tc_stock_id,
                ROUND(
                    ((COUNT(*) * SUM(po.po_rn * po.po_price) - SUM(po.po_rn) * SUM(po.po_price))
                    / NULLIF(COUNT(*) * SUM(po.po_rn * po.po_rn) - SUM(po.po_rn) * SUM(po.po_rn), 0))::NUMERIC
                , 6)                       AS tc_slope
            FROM price_ordered po
            GROUP BY po.po_stock_id
        ),
        -- transctikomn stats per stock in period
        txn_stats AS (
            SELECT
                t.stock_id                 AS ts_stock_id,
                SUM(t.price * t.quantity)  AS ts_total_volume,
                COUNT(*)                   AS ts_total_count,
                COUNT(*) FILTER (WHERE t.type = 'BUY')  AS ts_buy_count,
                COUNT(*) FILTER (WHERE t.type = 'SELL') AS ts_sell_count
            FROM transactions t
            WHERE (p_from_date IS NULL OR t.timestamp >= p_from_date)
              AND (p_to_date   IS NULL OR t.timestamp <= p_to_date)
            GROUP BY t.stock_id
        ),
        -- watchlist interest  stock
        watchlist_stats AS (
            SELECT
                w.stock_id                 AS ws_stock_id,
                COUNT(DISTINCT w.user_id)  AS ws_active_watchers
            FROM watchlist w
            GROUP BY w.stock_id
        ),
        -- pending trade requests per stock
        pending_stats AS (
            SELECT
                s.id                       AS pst_stock_id,
                COUNT(tr.id)               AS pst_pending_count
            FROM trade_request tr
            JOIN stock s ON tr.stock_symbol = s.symbol
            WHERE tr.status = 'PENDING'
            GROUP BY s.id
        )

        SELECT
            s.id::INTEGER                                                AS stock_id,
            s.symbol::TEXT,
            s.name::TEXT                                                 AS stock_name,
            s.current_price::NUMERIC,
            COALESCE(ps.ps_min_price, s.current_price)::NUMERIC         AS period_min_price,
            COALESCE(ps.ps_max_price, s.current_price)::NUMERIC         AS period_max_price,
            ROUND(
                ((COALESCE(ps.ps_max_price, s.current_price)
                 - COALESCE(ps.ps_min_price, s.current_price))
                / NULLIF(COALESCE(ps.ps_min_price, s.current_price), 0) * 100)::NUMERIC
            , 2)::NUMERIC                                                AS price_range_pct,
            COALESCE(ps.ps_avg_price, s.current_price)::NUMERIC         AS avg_daily_price,
            COALESCE(ps.ps_volatility_pct, 0)::NUMERIC                  AS historical_volatility_pct,
            COALESCE(ps.ps_total_points, 0)::BIGINT                     AS total_history_points,
            COALESCE(ts.ts_total_volume, 0)::NUMERIC                    AS total_txn_volume,
            COALESCE(ts.ts_total_count,  0)::BIGINT                     AS total_txn_count,
            COALESCE(ts.ts_buy_count,    0)::BIGINT                     AS buy_txn_count,
            COALESCE(ts.ts_sell_count,   0)::BIGINT                     AS sell_txn_count,
            ROUND(
                (COALESCE(ts.ts_buy_count, 0)::NUMERIC
                / NULLIF(COALESCE(ts.ts_sell_count, 0), 0))::NUMERIC
            , 2)::NUMERIC                                                AS buy_sell_ratio,
            COALESCE(ws.ws_active_watchers, 0)::BIGINT                  AS active_watchlist_users,
            COALESCE(pst.pst_pending_count, 0)::BIGINT                  AS pending_trade_requests,
            CASE
                WHEN tc.tc_slope >  0.01 THEN 'UPTREND'
                WHEN tc.tc_slope < -0.01 THEN 'DOWNTREND'
                ELSE                          'SIDEWAYS'
            END::TEXT                                                    AS trend_direction,
            COALESCE(tc.tc_slope, 0)::NUMERIC                           AS trend_slope,
            ROUND(
                (COALESCE(ws.ws_active_watchers, 0) * 2.0
                + COALESCE(ts.ts_total_count,    0) * 1.0
                + COALESCE(pst.pst_pending_count, 0) * 3.0)::NUMERIC
            , 2)::NUMERIC                                                AS activity_score,
            RANK() OVER (
                ORDER BY COALESCE(ps.ps_volatility_pct, 0) DESC
            )::BIGINT                                                    AS volatility_rank,
            RANK() OVER (
                ORDER BY (
                    COALESCE(ws.ws_active_watchers, 0) * 2.0
                    + COALESCE(ts.ts_total_count,    0) * 1.0
                    + COALESCE(pst.pst_pending_count, 0) * 3.0
                ) DESC
            )::BIGINT                                                    AS activity_rank
        FROM stock s
        LEFT JOIN price_stats     ps  ON s.id = ps.ps_stock_id
        LEFT JOIN trend_calc      tc  ON s.id = tc.tc_stock_id
        LEFT JOIN txn_stats       ts  ON s.id = ts.ts_stock_id
        LEFT JOIN watchlist_stats ws  ON s.id = ws.ws_stock_id
        LEFT JOIN pending_stats   pst ON s.id = pst.pst_stock_id
        ORDER BY historical_volatility_pct DESC, activity_score DESC;
END;
$$ LANGUAGE plpgsql;
}}}



'''EXPLAIN ANALYZE без индекси:'''
{{{
EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31');
-- резултат:
QUERY PLAN                                                                                                                               |
-----------------------------------------------------------------------------------------------------------------------------------------+
Function Scan on report_stock_volatility_and_activity  (cost=0.25..10.25 rows=1000 width=484) (actual time=13.601..13.602 rows=4 loops=1)|
Planning Time: 0.094 ms                                                                                                                  |
Execution Time: 15.248 ms                                                                                                                |
}}}


'''Индекси:'''

**1. stock_history - Composite Index (stock_id, timestamp)**
{{{
CREATE INDEX idx_sh_stock_id_timestamp
ON stock_history(stock_id, timestamp);
}}}
Користење: `PARTITION BY sh.stock_id ORDER BY sh.timestamp` во LAG прозорската функција и WHERE филтерот за дати

Подобрување: Composite индексот ги покрива и партиционирањето и сортирањето -= елиминира Sort чекор

---

**2. transactions - Composite Index (stock_id, timestamp)**
{{{
CREATE INDEX idx_tx_stock_id_timestamp
ON transactions(stock_id, timestamp);
}}}
Користење: GROUP BY `t.stock_id` и WHERE на `timestamp` во `txn_stats` 

Подобрување: Директен Index Scan наместо Seq Scan + Sort при временски филтрирани агрегации

---

**3. watchlist - Index (stock_id)**
{{{
CREATE INDEX idx_watchlist_stock_id
ON watchlist(stock_id);
}}}
Користење: GROUP BY `stock_id` во `watchlist_stats` 

Подобрување: Index Scan при агрегација по акција наместо Seq Scan

---

**4. trade_request - Composite Index (stock_symbol, status)**
{{{
CREATE INDEX idx_tr_symbol_status
ON trade_request(stock_symbol, status);
}}}
Користење: JOIN `tr.stock_symbol = s.symbol` и WHERE `tr.status = 'PENDING'` во `pending_stats` 

Подобрување: Composite индексот ги покрива и JOIN условот и WHERE филтерот истовремено

---

'''EXPLAIN ANALYZE со индекси:'''
{{{
EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31');
-- резултат:
QUERY PLAN                                                                                                                             |
---------------------------------------------------------------------------------------------------------------------------------------+
Function Scan on report_stock_volatility_and_activity  (cost=0.25..10.25 rows=1000 width=484) (actual time=6.624..6.625 rows=4 loops=1)|
Planning Time: 0.111 ms                                                                                                                |
Execution Time: 6.693 ms                                                                                                               |
}}}

'''Заклучок:'''
Имаме забрзување.
----

== Безбедност и заштита ==

=== JWT Token Authorization (Spring Security) ===

JWT е stateless начин на автентикација - серверот НЕ чува информации за активни сесии во база, туку сите потребни податоци се енкодирани во самиот токен кој корисникот го чува локално. JWT содржи енкодирани информации: user_id, email, role, expiry.

Имплементацијата во `SecurityConfig.java` дефинира кои endpoints се јавни и кои бараат автентикација, со посебни правила за ADMIN:

{{{
// SecurityConfig.java
@Bean
public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
    http
        .cors(cors -> cors.configurationSource(corsConfigurationSource()))
        .csrf(csrf -> csrf.disable())
        .authorizeHttpRequests(auth -> auth
            // Public endpoints - no token required
            .requestMatchers("/api/auth/**").permitAll()
            .requestMatchers("/api/stocks/**").permitAll()
            .requestMatchers("/ws/**", "/topic/**").permitAll()
            .requestMatchers("/api/history/**").permitAll()
            // Admin only - role-based access control
            .requestMatchers("/api/trades/*/approve").hasAuthority("ADMIN")
            .requestMatchers("/api/trades/*/decline").hasAuthority("ADMIN")
            .requestMatchers("/api/trades/pending").hasAuthority("ADMIN")
            // Authenticated users
            .requestMatchers("/api/trades/**").authenticated()
            .requestMatchers("/api/watchlist/**").authenticated()
            .requestMatchers("/api/transactions/**").authenticated()
            .anyRequest().authenticated()
        )
        .sessionManagement(s -> s
            .sessionCreationPolicy(SessionCreationPolicy.STATELESS)
        )
        .authenticationProvider(authenticationProvider)
        .addFilterBefore(jwtAuthenticationFilter, UsernamePasswordAuthenticationFilter.class);

    return http.build();
}
}}}

Секој HTTP барање поминува низ `JwtAuthenticationFilter` пред да стигне до контролерот. Филтерот го верификува потписот на токенот, го проверува expiry и го вчитува корисникот. Корисник без валиден JWT токен добива HTTP 401 Unauthorized.

=== Хеширање на лозинки (BCrypt) ===

Лозинките на корисниците се чуваат во базата во хеширана форма преку BCrypt алгоритам, а никогаш како plain text.

Во базата колоната `users.password` содржи BCrypt hash со формат `$2a$10$...`.



=== CORS Конфигурација ===

CORS е безбеден механизам кој ги ограничува HTTP барањата само од дозволени домени.

Апликацијата има двојна CORS конфигурација:

{{{
// SecurityConfig.java - CORS на Spring Security ниво
@Bean
public CorsConfigurationSource corsConfigurationSource() {
    CorsConfiguration config = new CorsConfiguration();
    config.setAllowedOrigins(List.of(
        "http://localhost:5173",
        "http://localhost:5174",
        "http://localhost:5175",
        "http://localhost:5176",
        "http://localhost:3000"
    ));
    config.setAllowedMethods(List.of("GET", "POST", "PUT", "DELETE", "OPTIONS", "PATCH"));
    config.setAllowedHeaders(List.of("*"));
    config.setAllowCredentials(true);

    UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
    source.registerCorsConfiguration("/**", source);
    return source;
}
}}}

{{{
// WebConfig.java - CORS на Spring MVC ниво
@Configuration
public class WebConfig implements WebMvcConfigurer {
    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**")
            .allowedOrigins(
                "http://localhost:5173",
                "http://localhost:5174",
                "http://localhost:5175",
                "http://localhost:3000"
            )
            .allowedMethods("*")
            .allowedHeaders("*")
            .allowCredentials(true);
    }
}
}}}

Само барања кои потекнуваат од дозволените localhost портови ќе бидат прифатени. Секое барање од непознат домен добива HTTP 403 уште на ниво на CORS preflight проверката.

=== Заштита на податоци на ниво на база ===

На ниво на базата на податоци, безбедноста е обезбедена преку:

 * '''Role-based CHECK constraints''' - `users.role CHECK (role IN ('USER','ADMIN'))` спречува внесување на невалидни улоги директно во базата
 * '''Enum CHECK constraints''' - `transactions.origin CHECK (origin IN ('INTERNAL','EXTERNAL'))` и `user_auth_providers.auth_providers CHECK (auth_providers IN ('INTERNAL','GOOGLE'))` обезбедуваат интегритет на enum вредностите
 * '''Foreign key constraints''' - сите референцијални правила се дефинирани на ниво на база, не само на апликациско ниво
 * '''UNIQUE constraints''' - `users.email UNIQUE`, `users.username UNIQUE`, `stock.symbol UNIQUE` и `portfolios.user_id UNIQUE` спречуваат дупликати кои би можеле да доведат до безбедносни пропусти
...

----