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

== Анализа на портфолио диверзификација и концентрациски ризик ==
'''Опис:''' Извештајот за секој корисник го анализира неговото портфолио од аспект на диверзификација: пресметува Herfindahl-Hirschman Index (HHI) за концентрација на вложувања, го идентификува доминантниот холдинг, го пресметува нереализираниот P&L по холдинг (разлика помеѓу тековна цена и avg_price), вкупната вредност на портфолиото, бројот на различни акции и класификација на ризик (HIGH CONCENTRATION RISK / MEDIUM CONCENTRATION RISK / WELL DIVERSIFIED) врз основа на HHI. Наменет за полугодишен/годишен преглед на ризикот на портфолијата на корисниците.

'''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
        -- Current market value and unrealized Profit and loss per holding lot
        -- cast everything to numeric to avoid double precision propagation (error got fixed...)
        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
        ),
		-- Aggregate per stock within each portfolio
        stock_weights AS (
            SELECT
                hv.hv_portfolio_id             AS sw_portfolio_id,
                hv.hv_stock_id                 AS sw_stock_id,
                hv.hv_symbol                   AS sw_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.sw_portfolio_id             AS pt_portfolio_id,
                SUM(sw.stock_market_value)     AS total_holdings_value,
                SUM(sw.stock_unrealized_pnl)   AS total_unrealized_pnl,
                COUNT(DISTINCT sw.sw_stock_id) AS num_stocks,
                SUM(sw.lots_count)             AS total_lots
            FROM stock_weights sw
            GROUP BY sw.sw_portfolio_id
        ),
		-- HHI: Σ(weight_i)² where weight_i = stock_value / total_portfolio_value
        -- HHI close to 1 = highly concentrated (risky), close to 0 = well diversified
        hhi_calc AS (
            SELECT
                sw.sw_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.sw_portfolio_id = pt.pt_portfolio_id
            GROUP BY sw.sw_portfolio_id
        ),
        dominant_stock AS (
            SELECT DISTINCT ON (sw.sw_portfolio_id)
                sw.sw_portfolio_id AS ds_portfolio_id,
                sw.sw_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.sw_portfolio_id = pt.pt_portfolio_id
            ORDER BY sw.sw_portfolio_id,
                     sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
        )

        SELECT
            u.id::INTEGER            AS user_id,
            u.username::TEXT,
            p.id::INTEGER                                                   AS portfolio_id,
            p.balance::NUMERIC                                              AS portfolio_cash_balance,
            COALESCE(pt.total_holdings_value, 0::NUMERIC)                  AS total_holdings_value,
            (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)) AS total_portfolio_value,
            COALESCE(pt.num_stocks,  0)::BIGINT                            AS num_distinct_stocks,
            COALESCE(pt.total_lots,  0)::BIGINT                            AS total_lots,
            COALESCE(pt.total_unrealized_pnl, 0::NUMERIC)                  AS unrealized_pnl,
            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)                                                            AS unrealized_pnl_pct,
            ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4)                        AS hhi_score,
            CASE
                WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK'
                WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK'
                ELSE                      'WELL DIVERSIFIED'
            END::TEXT                                                       AS risk_classification,
            COALESCE(ds.dominant_symbol,     'N/A')::TEXT                  AS dominant_stock_symbol,
            COALESCE(ds.dominant_weight_pct, 0::NUMERIC)                   AS dominant_stock_weight_pct,
            COALESCE(hhi.max_weight_pct,     0::NUMERIC)                   AS max_single_weight_pct,
            COALESCE(hhi.min_weight_pct,     0::NUMERIC)                   AS min_single_weight_pct,
            RANK() OVER (
                ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC
            )::BIGINT                                                       AS diversification_rank
        FROM users u
        JOIN portfolios            p   ON u.id = p.user_id
        LEFT JOIN portfolio_totals pt  ON p.id = pt.pt_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;

}}}

'''Релациона алгебра:'''
{{{
-- holding_values = π hv_portfolio_id ← ph.portfolio_id,
--                    hv_stock_id ← ph.stock_id,
--                    hv_symbol ← s.symbol,
--                    market_value ← NUMERIC(ph.quantity) * NUMERIC(s.current_price),
--                    unrealized_pnl ← NUMERIC(ph.quantity) * (NUMERIC(s.current_price) - NUMERIC(ph.avg_price)) (
--                  σ quantity > 0 (portfolio_holdings) ⋈ stock_id = id stock )

-- stock_weights = γ hv_portfolio_id, hv_stock_id, hv_symbol;
--                   sw_portfolio_id ← hv_portfolio_id,
--                   sw_stock_id ← hv_stock_id,
--                   sw_symbol ← hv_symbol,
--                   stock_market_value ← SUM(market_value),
--                   stock_unrealized_pnl ← SUM(unrealized_pnl),
--                   lots_count ← COUNT(*) (holding_values)

-- portfolio_totals = γ sw_portfolio_id;
--                      pt_portfolio_id ← sw_portfolio_id,
--                      total_holdings_value ← SUM(stock_market_value),
--                      total_unrealized_pnl ← SUM(stock_unrealized_pnl),
--                      num_stocks ← COUNT(DISTINCT sw_stock_id),
--                      total_lots ← SUM(lots_count) (stock_weights)

-- hhi_calc = γ sw_portfolio_id;
--              hhi_portfolio_id ← sw_portfolio_id,
--              hhi ← Σ POWER(stock_market_value / NULLIF(total_holdings_value, 0), 2),
--              max_weight_pct ← MAX(stock_market_value / NULLIF(total_holdings_value, 0) * 100),
--              min_weight_pct ← MIN(stock_market_value / NULLIF(total_holdings_value, 0) * 100) (
--            stock_weights ⋈ sw_portfolio_id = pt_portfolio_id portfolio_totals )

-- dominant_stock = π ds_portfolio_id ← sw_portfolio_id,
--                    dominant_symbol ← sw_symbol,
--                    dominant_weight_pct ← ROUND(stock_market_value / NULLIF(total_holdings_value, 0) * 100, 2)
--                  TOP-1-PER-GROUP BY sw_portfolio_id,
--                  ORDER BY stock_market_value / NULLIF(total_holdings_value, 0) DESC (
--                  stock_weights ⋈ sw_portfolio_id = pt_portfolio_id portfolio_totals )

π user_id ← u.id,
  username ← u.username,
  portfolio_id ← p.id,
  portfolio_cash_balance ← p.balance,
  total_holdings_value ← COALESCE(pt.total_holdings_value, 0),
  total_portfolio_value ← p.balance + COALESCE(pt.total_holdings_value, 0),
  num_distinct_stocks ← COALESCE(pt.num_stocks, 0),
  total_lots ← COALESCE(pt.total_lots, 0),
  unrealized_pnl ← COALESCE(pt.total_unrealized_pnl, 0),
  unrealized_pnl_pct ← ROUND(unrealized_pnl / NULLIF(total_holdings_value - unrealized_pnl, 0) * 100, 2),
  hhi_score ← ROUND(COALESCE(hhi.hhi, 0), 4),
  risk_classification ← CASE(hhi ≥ 0.25 → 'HIGH CONCENTRATION RISK',
                              hhi ≥ 0.10 → 'MEDIUM CONCENTRATION RISK',
                              else → 'WELL DIVERSIFIED'),
  dominant_stock_symbol ← COALESCE(ds.dominant_symbol, 'N/A'),
  dominant_stock_weight_pct ← COALESCE(ds.dominant_weight_pct, 0),
  max_single_weight_pct ← COALESCE(hhi.max_weight_pct, 0),
  min_single_weight_pct ← COALESCE(hhi.min_weight_pct, 0),
  diversification_rank ← RANK() OVER ORDER BY COALESCE(hhi.hhi, 1) ASC (

  τ hhi_score ASC, total_portfolio_value DESC (

    σ role = 'USER' (users)
    ⋈ u.id = p.user_id portfolios
    ⟕ p.id = pt.pt_portfolio_id portfolio_totals
    ⟕ p.id = hhi.hhi_portfolio_id hhi_calc
    ⟕ p.id = ds.ds_portfolio_id dominant_stock

  )
)
}}}

----

== Детекција на волатилност и трговски активност по акција ==

'''Опис:''' Извештајот за секоја акција ги пресметува историската волатилност (стандардна девијација на дневни приноси пресметана со LAG прозорска функција), просечниот ценовен опсег во периодот, бројот на активни watchlist корисници, бројот на pending трговски барања и trend индикатор (UPTREND / DOWNTREND / SIDEWAYS) врз основа на наклон на линеарна регресија (least-squares slope) на историските цени. На крај се пресметуваат композитен activity_score и рангирање по волатилност и активност. Наменет за квартален/полугодишен мониторинг на добри акции наспроти лоши.

'''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;
}}}

'''Релациона алгебра:'''
{{{
-- price_stats = γ sh.stock_id;
--                 ps_stock_id ← sh.stock_id,
--                 ps_total_points ← COUNT(*),
--                 ps_min_price ← MIN(sh.price),
--                 ps_max_price ← MAX(sh.price),
--                 ps_avg_price ← AVG(sh.price),
--                 ps_volatility_pct ← STDDEV(daily_return) * 100,
--               каде daily_return = (price_t - price_{t-1}) / price_{t-1}
--               пресметан преку LAG прозорска функција (
--               σ timestamp ∈ [from, to] (stock_history sh) )

-- price_ordered = π po_stock_id ← sh.stock_id,
--                   po_rn ← ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp),
--                   po_price ← sh.price (
--                 σ timestamp ∈ [from, to] (stock_history sh) )

-- trend_calc = γ po.po_stock_id;
--                tc_stock_id ← po_stock_id,
--                tc_slope ← (n·Σ(po_rn·po_price) − Σpo_rn·Σpo_price) / (n·Σpo_rn² − (Σpo_rn)²) (
--              price_ordered po )

-- txn_stats = γ t.stock_id;
--               ts_stock_id ← t.stock_id,
--               ts_total_volume ← SUM(t.price * t.quantity),
--               ts_total_count ← COUNT(*),
--               ts_buy_count ← COUNT(* | type = 'BUY'),
--               ts_sell_count ← COUNT(* | type = 'SELL') (
--             σ timestamp ∈ [from, to] (transactions t) )

-- watchlist_stats = γ w.stock_id;
--                     ws_stock_id ← w.stock_id,
--                     ws_active_watchers ← COUNT(DISTINCT w.user_id) (watchlist w)

-- pending_stats = γ s.id;
--                   pst_stock_id ← s.id,
--                   pst_pending_count ← COUNT(tr.id) (
--                 σ status = 'PENDING' (trade_request tr) ⋈ trade_stock_symbol = symbol stock s )

π stock_id ← s.id, symbol ← s.symbol, stock_name ← s.name, current_price ← s.current_price,
  period_min_price ← COALESCE(ps_min_price, current_price),
  period_max_price ← COALESCE(ps_max_price, current_price),
  price_range_pct ← (ps_max_price - ps_min_price) / NULLIF(ps_min_price, 0) * 100,
  avg_daily_price ← COALESCE(ps_avg_price, current_price),
  historical_volatility_pct ← COALESCE(ps_volatility_pct, 0),
  total_history_points ← COALESCE(ps_total_points, 0),
  total_txn_volume ← COALESCE(ts_total_volume, 0),
  total_txn_count ← COALESCE(ts_total_count, 0),
  buy_txn_count ← COALESCE(ts_buy_count, 0),
  sell_txn_count ← COALESCE(ts_sell_count, 0),
  buy_sell_ratio ← COALESCE(ts_buy_count, 0) / NULLIF(ts_sell_count, 0),
  active_watchlist_users ← COALESCE(ws_active_watchers, 0),
  pending_trade_requests ← COALESCE(pst_pending_count, 0),
  trend_direction ← CASE(tc_slope > 0.01 → 'UPTREND', tc_slope < -0.01 → 'DOWNTREND', else → 'SIDEWAYS'),
  trend_slope ← COALESCE(tc_slope, 0),
  activity_score ← ws_active_watchers*2 + ts_total_count*1 + pst_pending_count*3,
  volatility_rank ← RANK() OVER ORDER BY ps_volatility_pct DESC,
  activity_rank ← RANK() OVER ORDER BY activity_score DESC (

  τ historical_volatility_pct DESC, activity_score DESC (

    stock s
    ⟕ s.id = ps.ps_stock_id  price_stats ps
    ⟕ s.id = tc.tc_stock_id  trend_calc tc
    ⟕ s.id = ts.ts_stock_id  txn_stats ts
    ⟕ s.id = ws.ws_stock_id  watchlist_stats ws
    ⟕ s.id = pst.pst_stock_id  pending_stats pst

  )
)
}}}