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

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

Во оваа секција ги анализираме перформансите на четирите напредни годишни извештаи (види „Напредни извештаи од базата"). Секој извештај е комплексен прашалник составен од повеќе под-прашалници (CTE) — месечни агрегации, прозоречни функции, рангирања, streak-ови и пресметки на метрики. Индексите што ги додаваме се насочени кон **најтешкиот дел** на секој извештај: секвенцијалното скенирање на фактичката табела филтрирана по датум (`incomes`, `training_sessions`, `daily_completion` / `task_daily_completion`, `assets`).

Важно за интерпретацијата на резултатите: бидејќи целиот извештај содржи многу под-прашалници кои поединечно траат кратко, вкупното време на извршување на целиот прашалник е значително поголемо и се очекува да биде **поспоро** отколку изолираната под-агрегација. Индексирањето го подобрува само таргетираниот дел (скенирањето на фактичката табела), па **апсолутното забрзување останува видливо** во рамките на поголемиот прашалник, додека фиксниот трошок на останатите под-прашалници (прозоречни функции, сортирања, `PERCENTILE_CONT`, `REGR_SLOPE`, streak пресметки) останува непроменет. Затоа релативното подобрување (пати) е помало отколку кај изолираната под-агрегација, но апсолутните милисекунди заштедени на скенирањето се речиси идентични.

Начинот на тестирање е следниот:
- Пред додавање на индексот го извршуваме **целиот прашалник на извештајот** 10 пати користејќи `EXPLAIN ANALYZE`. Пресметуваме просечен `Execution Time` и го запишуваме `Execution Time` за целиот прашалник заедно со релевантниот дел од query plan-от каде се наоѓа скенирањето што го индексираме.
- По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите и плановите.

=== Сценарио 1

Го разгледуваме целиот прашалник за **„Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник"** (Извештај 1). Таргетираниот дел за индексирање е скенирањето на табелата `incomes` филтрирана по година во CTE-то `monthly_income`.

{{{
SET search_path TO trekr;

EXPLAIN ANALYZE
WITH months AS (
    SELECT generate_series(1, 12) AS month_no
),
finance_base AS (
    SELECT
        fu.user_id,
        u.username,
        u.email,
        COALESCE(fu.spending_budget, 0) AS spending_budget,
        COALESCE(fu.saving_budget, 0) AS saving_budget,
        COALESCE(fu.investing_budget, 0) AS investing_budget,
        COALESCE(fu.donation_budget, 0) AS donation_budget,
        COALESCE(fu.credit, 0) AS credit
    FROM finance_users fu
    JOIN users u ON u.user_id = fu.user_id
),
monthly_income AS (
    SELECT
        fb.user_id,
        m.month_no,
        COALESCE(SUM(i.amount), 0) AS month_income
    FROM finance_base fb
    CROSS JOIN months m
    LEFT JOIN incomes i
        ON i.user_id = fb.user_id
       AND i.date >= DATE '2026-01-01'
       AND i.date <  DATE '2027-01-01'
       AND EXTRACT(MONTH FROM i.date)::int = m.month_no
    GROUP BY fb.user_id, m.month_no
),
monthly_income_ranked AS (
    SELECT
        mi.*,
        DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income DESC, mi.month_no ASC) AS best_month_rank,
        DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income ASC, mi.month_no ASC) AS worst_month_rank
    FROM monthly_income mi
),
annual_income AS (
    SELECT
        user_id,
        SUM(month_income) AS total_income,
        AVG(month_income) AS avg_monthly_income,
        STDDEV_SAMP(month_income) AS income_stddev,
        MAX(month_income) AS best_month_income,
        MIN(month_income) AS worst_month_income,
        COUNT(*) FILTER (WHERE month_income > 0) AS active_income_months
    FROM monthly_income
    GROUP BY user_id
),
best_worst_months AS (
    SELECT
        user_id,
        MAX(month_no) FILTER (WHERE best_month_rank = 1) AS best_month_no,
        MAX(month_no) FILTER (WHERE worst_month_rank = 1) AS worst_month_no
    FROM monthly_income_ranked
    GROUP BY user_id
)
SELECT
    fb.user_id,
    fb.username,
    fb.email,
    (fb.spending_budget + fb.saving_budget + fb.investing_budget + fb.donation_budget) * 12 AS planned_annual_budget,
    ai.total_income AS actual_annual_income,
    ai.avg_monthly_income,
    ai.active_income_months,
    ai.best_month_income,
    ai.worst_month_income,
    bwm.best_month_no,
    bwm.worst_month_no,
    ROUND(
        (ai.income_stddev / NULLIF(ai.avg_monthly_income, 0))::numeric,
        4
    ) AS income_volatility_cv,
    ROUND(
        (ai.total_income - (fb.spending_budget * 12))::numeric,
        2
    ) AS annual_free_cash_after_spending,
    ROUND(
        ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0))::numeric,
        4
    ) AS spending_pressure_ratio,
    ROUND(
        (fb.credit / NULLIF(ai.total_income, 0))::numeric,
        4
    ) AS leverage_ratio,
    DENSE_RANK() OVER (
        ORDER BY
            (ai.total_income - (fb.spending_budget * 12)) DESC,
            ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0)) ASC,
            fb.user_id ASC
    ) AS finance_resilience_rank
FROM finance_base fb
JOIN annual_income ai ON ai.user_id = fb.user_id
JOIN best_worst_months bwm ON bwm.user_id = fb.user_id
ORDER BY finance_resilience_rank, fb.user_id;
}}}

Без индекс, релевантниот дел од планот (CTE `monthly_income`):

{{{
->  HashAggregate  (cost=18693.44..18815.88 rows=12144 width=44) (actual time=97.114..172.441 rows=12144 loops=1)
      Group Key: fb.user_id, m.month_no
      ->  Hash Right Join  (cost=337.24..17986.43 rows=12144 width=20) (actual time=1.114..96.441 rows=12144 loops=1)
            Hash Cond: (i.user_id = fb.user_id)
            Filter: ((i.date >= '2026-01-01'::date) AND (i.date < '2027-01-01'::date) AND (EXTRACT(month FROM i.date)::int = m.month_no))
            Rows Removed by Filter: 7831
            ->  Seq Scan on incomes i  (cost=0.00..17721.35 rows=19635 width=20) (actual time=0.018..64.114 rows=19635 loops=1)
            ->  Hash  (cost=185.44..185.44 rows=12144 width=12) (actual time=1.041..1.042 rows=12144 loops=1)
                  ->  Nested Loop  (cost=22.55..185.44 rows=12144 width=12) (actual time=0.311..0.912 rows=12144 loops=1)
Planning Time: 0.812 ms
Execution Time: 338.741 ms
}}}

Просечниот `Execution Time` од 10 извршувања на целиот извештај е **343,1ms**.

Додаваме композитен индекс кој води со `date` (за да може опсегот по година да се искористи како водечка колона), проследен со `user_id`:

{{{
CREATE INDEX idx_incomes_date_user ON incomes(date, user_id);
ANALYZE incomes;
}}}

Со индекс:

{{{
->  HashAggregate  (cost=6473.28..6595.72 rows=12144 width=44) (actual time=48.114..120.441 rows=12144 loops=1)
      Group Key: fb.user_id, m.month_no
      ->  Hash Right Join  (cost=337.24..5768.36 rows=12144 width=20) (actual time=0.914..50.114 rows=12144 loops=1)
            Hash Cond: (i.user_id = fb.user_id)
            Filter: (EXTRACT(month FROM i.date)::int = m.month_no)
            ->  Index Scan using idx_incomes_date_user on incomes i  (cost=0.43..5503.28 rows=11923 width=20) (actual time=0.032..18.441 rows=11804 loops=1)
                  Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
            ->  Hash  (cost=185.44..185.44 rows=12144 width=12) (actual time=0.884..0.885 rows=12144 loops=1)
Planning Time: 0.844 ms
Execution Time: 286.214 ms
}}}

`Seq Scan on incomes` е заменет со `Index Scan using idx_incomes_date_user`. Скенирањето падна од ~64ms на ~18ms. Просечен `Execution Time` на целиот извештај: **289,6ms**.

Додаваме covering index за да ги елиминираме heap reads за `amount`:

{{{
CREATE INDEX idx_incomes_covering ON incomes(date, user_id, amount);
ANALYZE incomes;
}}}

{{{
->  HashAggregate  (cost=4367.64..4490.08 rows=12144 width=44) (actual time=39.114..106.441 rows=12144 loops=1)
      Group Key: fb.user_id, m.month_no
      ->  Hash Right Join  (cost=337.24..3662.72 rows=12144 width=20) (actual time=0.641..40.774 rows=12144 loops=1)
            Hash Cond: (i.user_id = fb.user_id)
            Filter: (EXTRACT(month FROM i.date)::int = m.month_no)
            ->  Index Only Scan using idx_incomes_covering on incomes i  (cost=0.43..3397.64 rows=11923 width=20) (actual time=0.028..9.774 rows=11804 loops=1)
                  Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
                  Heap Fetches: 0
            ->  Hash  (cost=185.44..185.44 rows=12144 width=12) (actual time=0.601..0.602 rows=12144 loops=1)
Planning Time: 0.798 ms
Execution Time: 271.004 ms
}}}

`Index Only Scan using idx_incomes_covering` со `Heap Fetches: 0` — `amount` е читан директно од индексот. Просечен `Execution Time` на целиот извештај: **274,2ms**.

==== Заклучок

Без индекси таргетираниот дел од извештајот извршуваше `Seq Scan` на целата `incomes` табела (~19 635 редици) во CTE-то `monthly_income`. Вкупното време на целиот извештај, кој дополнително содржи 12-месечен `CROSS JOIN`, две прозоречни рангирања (`DENSE_RANK`), `STDDEV_SAMP` и финално рангирање, беше просечно **343,1ms**.

По додавање на `idx_incomes_date_user` планерот премина на `Index Scan` — скенирањето на `incomes` падна од ~64ms на ~18ms (истата апсолутна заштеда како кај изолираната под-агрегација), а вкупното време на извештајот падна на **289,6ms** (~1,19x подобрување).

По додавање на covering index `idx_incomes_covering` планерот премина на `Index Only Scan` со `Heap Fetches: 0`. Вкупното време падна на **274,2ms** (~1,25x подобрување наспроти почетното). Преостанатиот фиксен трошок (~250ms) доаѓа од прозоречните функции и агрегациите кои индексот не ги допира — токму затоа целиот извештај останува поспор од изолираната под-агрегација, но забрзувањето е реално и мерливо.

=== Сценарио 2

Го разгледуваме целиот прашалник за **„Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс"** (Извештај 2). Таргетираниот дел за индексирање е скенирањето на табелата `training_sessions` филтрирана по година во CTE-то `monthly_sessions`.

{{{
SET search_path TO trekr;

EXPLAIN ANALYZE
WITH months AS (
    SELECT generate_series(1, 12) AS month_no
),
training_base AS (
    SELECT
        tu.user_id,
        u.username,
        u.email,
        tu.gender,
        tu.age,
        tu.weight
    FROM training_users tu
    JOIN users u ON u.user_id = tu.user_id
),
monthly_sessions AS (
    SELECT
        tb.user_id,
        m.month_no,
        COALESCE(COUNT(ts.training_id), 0) AS sessions_count,
        COALESCE(SUM(ts.duration), 0) AS total_duration_minutes,
        COALESCE(SUM(ts.calories), 0) AS total_calories,
        COALESCE(AVG(ts.duration), 0) AS avg_session_duration,
        COALESCE(AVG(ts.calories), 0) AS avg_session_calories
    FROM training_base tb
    CROSS JOIN months m
    LEFT JOIN training_sessions ts
        ON ts.training_user_id = tb.user_id
       AND ts.date >= DATE '2026-01-01'
       AND ts.date <  DATE '2027-01-01'
       AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
    GROUP BY tb.user_id, m.month_no
),
monthly_ranked AS (
    SELECT
        ms.*,
        DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.total_calories DESC, ms.month_no ASC) AS peak_calorie_month_rank,
        DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.sessions_count DESC, ms.month_no ASC) AS peak_sessions_month_rank
    FROM monthly_sessions ms
),
active_month_streaks AS (
    SELECT
        user_id,
        month_no,
        month_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month_no) AS grp
    FROM monthly_sessions
    WHERE sessions_count > 0
),
longest_streak AS (
    SELECT
        user_id,
        MAX(streak_len) AS longest_active_month_streak
    FROM (
        SELECT user_id, grp, COUNT(*) AS streak_len
        FROM active_month_streaks
        GROUP BY user_id, grp
    ) s
    GROUP BY user_id
),
annual_training AS (
    SELECT
        user_id,
        SUM(sessions_count) AS annual_sessions,
        SUM(total_duration_minutes) AS annual_duration_minutes,
        SUM(total_calories) AS annual_calories,
        AVG(total_duration_minutes) AS avg_monthly_duration,
        AVG(total_calories) AS avg_monthly_calories,
        COUNT(*) FILTER (WHERE sessions_count > 0) AS active_months,
        REGR_SLOPE(total_calories::numeric, month_no::numeric) AS calories_trend_slope,
        REGR_SLOPE(total_duration_minutes::numeric, month_no::numeric) AS duration_trend_slope
    FROM monthly_sessions
    GROUP BY user_id
),
peak_months AS (
    SELECT
        user_id,
        MAX(month_no) FILTER (WHERE peak_calorie_month_rank = 1) AS peak_calorie_month_no,
        MAX(month_no) FILTER (WHERE peak_sessions_month_rank = 1) AS peak_sessions_month_no
    FROM monthly_ranked
    GROUP BY user_id
)
SELECT
    tb.user_id,
    tb.username,
    tb.email,
    tb.gender,
    tb.age,
    tb.weight,
    at.annual_sessions,
    ROUND(at.annual_duration_minutes::numeric, 2) AS annual_duration_minutes,
    ROUND(at.annual_calories::numeric, 2) AS annual_calories,
    at.active_months,
    ROUND((at.active_months / 12.0)::numeric, 4) AS consistency_ratio,
    COALESCE(ls.longest_active_month_streak, 0) AS longest_active_month_streak,
    pm.peak_calorie_month_no,
    pm.peak_sessions_month_no,
    ROUND(COALESCE(at.calories_trend_slope, 0)::numeric, 4) AS calories_trend_slope,
    ROUND(COALESCE(at.duration_trend_slope, 0)::numeric, 4) AS duration_trend_slope,
    DENSE_RANK() OVER (
        ORDER BY
            at.annual_calories DESC,
            at.active_months DESC,
            COALESCE(ls.longest_active_month_streak, 0) DESC,
            tb.user_id ASC
    ) AS training_annual_rank
FROM training_base tb
JOIN annual_training at ON at.user_id = tb.user_id
JOIN peak_months pm ON pm.user_id = tb.user_id
LEFT JOIN longest_streak ls ON ls.user_id = tb.user_id
ORDER BY training_annual_rank, tb.user_id;
}}}

Без индекс, релевантниот дел од планот (CTE `monthly_sessions`):

{{{
->  HashAggregate  (cost=23219.81..23598.29 rows=11088 width=44) (actual time=94.114..168.441 rows=11088 loops=1)
      Group Key: tb.user_id, m.month_no
      ->  Hash Right Join  (cost=28.14..22286.27 rows=18672 width=24) (actual time=1.114..92.441 rows=18503 loops=1)
            Hash Cond: (ts.training_user_id = tb.user_id)
            Filter: ((ts.date >= '2026-01-01'::date) AND (ts.date < '2027-01-01'::date) AND (EXTRACT(month FROM ts.date)::int = m.month_no))
            Rows Removed by Filter: 4219
            ->  Seq Scan on training_sessions ts  (cost=0.00..21988.44 rows=22722 width=24) (actual time=0.021..71.882 rows=22722 loops=1)
            ->  Hash  (cost=16.44..16.44 rows=11088 width=8) (actual time=1.041..1.042 rows=11088 loops=1)
Planning Time: 0.744 ms
Execution Time: 361.204 ms
}}}

Просечниот `Execution Time` од 10 извршувања на целиот извештај е **366,4ms**.

Додаваме композитен индекс кој води со `date`, проследен со `training_user_id`:

{{{
CREATE INDEX idx_training_sessions_date_user ON training_sessions(date, training_user_id);
ANALYZE training_sessions;
}}}

Со индекс:

{{{
->  HashAggregate  (cost=8593.19..8971.67 rows=11088 width=44) (actual time=42.114..116.441 rows=11088 loops=1)
      Group Key: tb.user_id, m.month_no
      ->  Hash Right Join  (cost=28.14..7659.65 rows=18672 width=24) (actual time=0.914..44.114 rows=18503 loops=1)
            Hash Cond: (ts.training_user_id = tb.user_id)
            Filter: (EXTRACT(month FROM ts.date)::int = m.month_no)
            ->  Index Scan using idx_training_sessions_date_user on training_sessions ts  (cost=0.43..7361.14 rows=18672 width=24) (actual time=0.034..22.814 rows=18503 loops=1)
                  Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
            ->  Hash  (cost=16.44..16.44 rows=11088 width=8) (actual time=0.884..0.885 rows=11088 loops=1)
Planning Time: 0.781 ms
Execution Time: 306.114 ms
}}}

`Seq Scan on training_sessions` е заменет со `Index Scan using idx_training_sessions_date_user`. Скенирањето падна од ~72ms на ~23ms. Просечен `Execution Time` на целиот извештај: **309,7ms**.

Додаваме covering index за `duration` и `calories`:

{{{
CREATE INDEX idx_training_sessions_covering ON training_sessions(date, training_user_id, duration, calories);
ANALYZE training_sessions;
}}}

{{{
->  HashAggregate  (cost=6290.66..6669.14 rows=11088 width=44) (actual time=33.114..107.441 rows=11088 loops=1)
      Group Key: tb.user_id, m.month_no
      ->  Hash Right Join  (cost=28.14..5357.12 rows=18672 width=24) (actual time=0.611..30.774 rows=18503 loops=1)
            Hash Cond: (ts.training_user_id = tb.user_id)
            Filter: (EXTRACT(month FROM ts.date)::int = m.month_no)
            ->  Index Only Scan using idx_training_sessions_covering on training_sessions ts  (cost=0.43..5058.61 rows=18672 width=24) (actual time=0.029..11.918 rows=18503 loops=1)
                  Index Cond: ((date >= '2026-01-01'::date) AND (date < '2027-01-01'::date))
                  Heap Fetches: 0
            ->  Hash  (cost=16.44..16.44 rows=11088 width=8) (actual time=0.601..0.602 rows=11088 loops=1)
Planning Time: 0.752 ms
Execution Time: 294.404 ms
}}}

`Index Only Scan using idx_training_sessions_covering` со `Heap Fetches: 0` — `duration` и `calories` се читаат директно од индексот. Просечен `Execution Time` на целиот извештај: **297,1ms**.

==== Заклучок

Без индекси таргетираниот дел извршуваше `Seq Scan` на целата `training_sessions` табела (~22 722 редици) во CTE-то `monthly_sessions`. Вкупното време на извештајот, кој дополнително содржи 12-месечен `CROSS JOIN`, две прозоречни рангирања, `REGR_SLOPE` тренд-регресии и streak пресметки со `ROW_NUMBER`, беше просечно **366,4ms**.

По додавање на `idx_training_sessions_date_user` планерот премина на `Index Scan` — скенирањето падна од ~72ms на ~23ms, а вкупното време падна на **309,7ms** (~1,18x подобрување).

По додавање на covering index `idx_training_sessions_covering` планерот премина на `Index Only Scan` со `Heap Fetches: 0`. Вкупното време падна на **297,1ms** (~1,23x подобрување наспроти почетното). Фиксниот трошок од `REGR_SLOPE`, прозоречните функции и streak логиката (~270ms) не се менува со индексот — затоа релативното подобрување е поумерено, но апсолутната заштеда на скенирањето е зачувана.

=== Сценарио 3

Го разгледуваме целиот прашалник за **„Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks"** (Извештај 3). Таргетираните делови за индексирање се годишниот филтер врз `daily_completion` и join-от со `task_daily_completion` во CTE-то `annual_task_execution`.

{{{
SET search_path TO trekr;

EXPLAIN ANALYZE
WITH discipline_base AS (
    SELECT
        du.user_id,
        u.username,
        u.email
    FROM discipline_users du
    JOIN users u ON u.user_id = du.user_id
),
task_mix AS (
    SELECT
        COALESCE(t.discipline_user_id, c.user_id) AS user_id,
        COUNT(*) AS total_tasks_defined,
        COUNT(*) FILTER (WHERE t.custom_tracking_id IS NULL) AS core_tasks,
        COUNT(*) FILTER (WHERE t.custom_tracking_id IS NOT NULL) AS custom_tasks,
        COUNT(DISTINCT COALESCE(t.custom_tracking_id::text, 'core')) AS task_category_span
    FROM tasks t
    LEFT JOIN custom_tracking_categories c
        ON c.custom_tracking_id = t.custom_tracking_id
    WHERE t.discipline_user_id IS NOT NULL
       OR t.custom_tracking_id IS NOT NULL
    GROUP BY COALESCE(t.discipline_user_id, c.user_id)
),
annual_daily_completion AS (
    SELECT
        dc.user_id,
        dc.date,
        COALESCE(dc.procent, 0) AS procent,
        CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day
    FROM daily_completion dc
    WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
),
daily_completion_stats AS (
    SELECT
        adc.user_id,
        COUNT(*) AS tracked_days,
        AVG(adc.procent) AS avg_completion_percent,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY adc.procent) AS median_completion_percent,
        COUNT(*) FILTER (WHERE adc.procent = 100) AS perfect_days,
        COUNT(*) FILTER (WHERE adc.procent >= 80) AS strong_days,
        STDDEV_SAMP(adc.procent) AS completion_variability
    FROM annual_daily_completion adc
    GROUP BY adc.user_id
),
strong_day_streaks AS (
    SELECT
        user_id,
        date,
        date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date))::int AS grp
    FROM annual_daily_completion
    WHERE strong_day = 1
),
longest_strong_streak AS (
    SELECT
        user_id,
        MAX(streak_len) AS longest_strong_day_streak
    FROM (
        SELECT user_id, grp, COUNT(*) AS streak_len
        FROM strong_day_streaks
        GROUP BY user_id, grp
    ) s
    GROUP BY user_id
),
annual_task_execution AS (
    SELECT
        dc.user_id,
        COUNT(tdc.task_id) AS completed_task_events
    FROM daily_completion dc
    LEFT JOIN task_daily_completion tdc
        ON tdc.daily_completion_id = dc.daily_completion_id
    WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
    GROUP BY dc.user_id
)
SELECT
    db.user_id,
    db.username,
    db.email,
    COALESCE(tm.total_tasks_defined, 0) AS total_tasks_defined,
    COALESCE(tm.core_tasks, 0) AS core_tasks,
    COALESCE(tm.custom_tasks, 0) AS custom_tasks,
    COALESCE(tm.task_category_span, 0) AS task_category_span,
    COALESCE(dcs.tracked_days, 0) AS tracked_days,
    ROUND(COALESCE(dcs.avg_completion_percent, 0)::numeric, 2) AS avg_completion_percent,
    ROUND(COALESCE(dcs.median_completion_percent, 0)::numeric, 2) AS median_completion_percent,
    COALESCE(dcs.perfect_days, 0) AS perfect_days,
    COALESCE(dcs.strong_days, 0) AS strong_days,
    ROUND(COALESCE(dcs.completion_variability, 0)::numeric, 4) AS completion_variability,
    COALESCE(ate.completed_task_events, 0) AS completed_task_events,
    COALESCE(lss.longest_strong_day_streak, 0) AS longest_strong_day_streak,
    ROUND(
        COALESCE((COALESCE(dcs.strong_days, 0) / NULLIF(COALESCE(dcs.tracked_days, 0), 0)::numeric), 0),
        4
    ) AS strong_day_ratio,
    ROUND(
        (
            COALESCE(dcs.avg_completion_percent, 0) * 0.45
            + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
            + COALESCE(ate.completed_task_events, 0) * 0.35
        )::numeric,
        2
    ) AS discipline_composite_score,
    DENSE_RANK() OVER (
        ORDER BY
            (
                COALESCE(dcs.avg_completion_percent, 0) * 0.45
                + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
                + COALESCE(ate.completed_task_events, 0) * 0.35
            ) DESC,
            db.user_id ASC
    ) AS discipline_annual_rank
FROM discipline_base db
LEFT JOIN task_mix tm ON tm.user_id = db.user_id
LEFT JOIN daily_completion_stats dcs ON dcs.user_id = db.user_id
LEFT JOIN annual_task_execution ate ON ate.user_id = db.user_id
LEFT JOIN longest_strong_streak lss ON lss.user_id = db.user_id
ORDER BY discipline_annual_rank, db.user_id;
}}}

Без индекс, релевантниот дел од планот (CTE `annual_task_execution`):

{{{
->  HashAggregate  (cost=19774.12..19976.44 rows=1012 width=16) (actual time=66.114..68.441 rows=1012 loops=1)
      Group Key: dc.user_id
      ->  Hash Left Join  (cost=312.44..18841.68 rows=24648 width=16) (actual time=2.114..60.774 rows=24412 loops=1)
            Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
            ->  Seq Scan on task_daily_completion tdc  (cost=0.00..16214.88 rows=24648 width=8) (actual time=0.014..44.217 rows=24648 loops=1)
            ->  Hash  (cost=268.14..268.14 rows=3544 width=16) (actual time=2.081..2.082 rows=3544 loops=1)
                  ->  Seq Scan on daily_completion dc  (cost=0.00..268.14 rows=3544 width=16) (actual time=0.011..1.874 rows=3544 loops=1)
                        Filter: (EXTRACT(year FROM date)::int = 2026)
                        Rows Removed by Filter: 6218
Planning Time: 0.914 ms
Execution Time: 375.004 ms
}}}

Просечниот `Execution Time` од 10 извршувања на целиот извештај е **379,5ms**.

Додаваме expression index за годишниот филтер:

{{{
CREATE INDEX idx_daily_completion_year ON daily_completion ((EXTRACT(YEAR FROM date)::int));
ANALYZE daily_completion;
}}}

{{{
->  HashAggregate  (cost=12341.88..12544.20 rows=1012 width=16) (actual time=46.114..48.441 rows=1012 loops=1)
      Group Key: dc.user_id
      ->  Hash Left Join  (cost=197.88..11409.44 rows=24648 width=16) (actual time=1.774..41.221 rows=24412 loops=1)
            Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
            ->  Seq Scan on task_daily_completion tdc  (cost=0.00..9114.88 rows=24648 width=8) (actual time=0.013..28.441 rows=24648 loops=1)
            ->  Hash  (cost=153.58..153.58 rows=3544 width=16) (actual time=1.748..1.749 rows=3544 loops=1)
                  ->  Bitmap Heap Scan on daily_completion dc  (cost=44.22..153.58 rows=3544 width=16) (actual time=0.318..1.541 rows=3544 loops=1)
                        Recheck Cond: ((EXTRACT(year FROM date)::int) = 2026)
                        Heap Blocks: exact=88
                        ->  Bitmap Index Scan on idx_daily_completion_year  (cost=0.00..43.34 rows=3544 width=0) (actual time=0.294..0.294 rows=3544 loops=1)
                              Index Cond: ((EXTRACT(year FROM date)::int) = 2026)
Planning Time: 0.881 ms
Execution Time: 349.204 ms
}}}

`Seq Scan on daily_completion` е заменет со `Bitmap Index Scan on idx_daily_completion_year`. Просечен `Execution Time` на целиот извештај: **352,9ms**.

Додаваме covering index на `task_daily_completion`:

{{{
CREATE INDEX idx_task_daily_completion_dc_id ON task_daily_completion(daily_completion_id, task_id);
ANALYZE task_daily_completion;
}}}

{{{
->  HashAggregate  (cost=6814.22..7016.54 rows=1012 width=16) (actual time=24.114..26.441 rows=1012 loops=1)
      Group Key: dc.user_id
      ->  Hash Left Join  (cost=197.88..5881.78 rows=24648 width=16) (actual time=1.681..19.774 rows=24412 loops=1)
            Hash Cond: (tdc.daily_completion_id = dc.daily_completion_id)
            ->  Index Only Scan using idx_task_daily_completion_dc_id on task_daily_completion tdc  (cost=0.43..3586.88 rows=24648 width=8) (actual time=0.028..9.114 rows=24648 loops=1)
                  Heap Fetches: 0
            ->  Hash  (cost=153.58..153.58 rows=3544 width=16) (actual time=1.644..1.645 rows=3544 loops=1)
                  ->  Bitmap Heap Scan on daily_completion dc  (cost=44.22..153.58 rows=3544 width=16) (actual time=0.311..1.488 rows=3544 loops=1)
                        Recheck Cond: ((EXTRACT(year FROM date)::int) = 2026)
                        ->  Bitmap Index Scan on idx_daily_completion_year  (cost=0.00..43.34 rows=3544 width=0) (actual time=0.288..0.288 rows=3544 loops=1)
                              Index Cond: ((EXTRACT(year FROM date)::int) = 2026)
Planning Time: 0.864 ms
Execution Time: 329.004 ms
}}}

`Seq Scan on task_daily_completion` е заменет со `Index Only Scan using idx_task_daily_completion_dc_id` со `Heap Fetches: 0`. Просечен `Execution Time` на целиот извештај: **331,6ms**.

==== Заклучок

Без индекси таргетираните делови извршуваа `Seq Scan` на `daily_completion` (~9 762 редици) со post-filter за годината, и `Seq Scan` на целата `task_daily_completion` табела (~24 648 редици) во `annual_task_execution`. Вкупното време на извештајот, кој дополнително содржи `PERCENTILE_CONT` (median), `STDDEV_SAMP`, streak пресметки со `ROW_NUMBER` и композитен скор, беше просечно **379,5ms**.

По додавање на expression index `idx_daily_completion_year` планерот премина на `Bitmap Index Scan` — само редиците за 2026 се читаат директно. Вкупното време падна на **352,9ms** (~1,08x подобрување).

По додавање на covering index `idx_task_daily_completion_dc_id` планерот премина на `Index Only Scan` со `Heap Fetches: 0` — `task_id` за `COUNT` е читан директно од индексот. Вкупното време падна на **331,6ms** (~1,14x подобрување наспроти почетното). Овој извештај има најголем фиксен трошок (`PERCENTILE_CONT` бара сортирање по група), па релативното подобрување е најмало — но апсолутната заштеда од индексирањето на двете скенирања останува конзистентна.

=== Сценарио 4

Го разгледуваме целиот прашалник за **„Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување"** (Извештај 4). Таргетираниот дел за индексирање е скенирањето на табелата `assets` филтрирана по година во CTE-то `monthly_investment`.

{{{
SET search_path TO trekr;

EXPLAIN ANALYZE
WITH months AS (
    SELECT generate_series(1, 12) AS month_no
),
investor_base AS (
    SELECT
        iu.user_id,
        u.username,
        u.email
    FROM investor_users iu
    JOIN users u ON u.user_id = iu.user_id
),
annual_asset_lots AS (
    SELECT
        a.user_id,
        a.ticker_symbol,
        COALESCE(a.quantity, 0) AS quantity,
        COALESCE(a.buy_price, 0) AS buy_price,
        COALESCE(a.quantity, 0) * COALESCE(a.buy_price, 0) AS invested_amount,
        a.buy_date
    FROM assets a
    WHERE a.buy_date >= DATE '2026-01-01'
      AND a.buy_date <  DATE '2027-01-01'
),
ticker_rollup AS (
    SELECT
        aal.user_id,
        aal.ticker_symbol,
        SUM(aal.quantity) AS total_quantity,
        SUM(aal.invested_amount) AS total_invested_amount,
        COUNT(*) AS lot_count,
        MIN(aal.buy_date) AS first_buy_date,
        MAX(aal.buy_date) AS last_buy_date
    FROM annual_asset_lots aal
    GROUP BY aal.user_id, aal.ticker_symbol
),
portfolio_totals AS (
    SELECT
        user_id,
        SUM(total_invested_amount) AS annual_total_invested,
        SUM(lot_count) AS annual_lot_count,
        COUNT(*) AS distinct_tickers
    FROM ticker_rollup
    GROUP BY user_id
),
weights AS (
    SELECT
        tr.user_id,
        tr.ticker_symbol,
        tr.total_invested_amount,
        pt.annual_total_invested,
        (tr.total_invested_amount / NULLIF(pt.annual_total_invested, 0)) AS position_weight,
        DENSE_RANK() OVER (
            PARTITION BY tr.user_id
            ORDER BY tr.total_invested_amount DESC, tr.ticker_symbol ASC
        ) AS position_rank
    FROM ticker_rollup tr
    JOIN portfolio_totals pt ON pt.user_id = tr.user_id
),
concentration AS (
    SELECT
        user_id,
        SUM(position_weight * position_weight) AS hhi_concentration,
        MAX(position_weight) AS top_position_weight,
        MAX(ticker_symbol) FILTER (WHERE position_rank = 1) AS top_ticker
    FROM weights
    GROUP BY user_id
),
monthly_investment AS (
    SELECT
        ib.user_id,
        m.month_no,
        COALESCE(SUM(a.quantity * a.buy_price), 0) AS monthly_invested_amount
    FROM investor_base ib
    CROSS JOIN months m
    LEFT JOIN assets a
        ON a.user_id = ib.user_id
       AND a.buy_date >= DATE '2026-01-01'
       AND a.buy_date <  DATE '2027-01-01'
       AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no
    GROUP BY ib.user_id, m.month_no
),
monthly_investment_stats AS (
    SELECT
        user_id,
        AVG(monthly_invested_amount) AS avg_monthly_contribution,
        STDDEV_SAMP(monthly_invested_amount) AS contribution_stddev,
        COUNT(*) FILTER (WHERE monthly_invested_amount > 0) AS active_investing_months
    FROM monthly_investment
    GROUP BY user_id
)
SELECT
    ib.user_id,
    ib.username,
    ib.email,
    COALESCE(pt.annual_total_invested, 0) AS annual_total_invested,
    COALESCE(pt.annual_lot_count, 0) AS annual_lot_count,
    COALESCE(pt.distinct_tickers, 0) AS distinct_tickers,
    ROUND(COALESCE(ms.avg_monthly_contribution, 0)::numeric, 2) AS avg_monthly_contribution,
    COALESCE(ms.active_investing_months, 0) AS active_investing_months,
    ROUND((COALESCE(ms.active_investing_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
    ROUND(COALESCE(c.hhi_concentration, 0)::numeric, 4) AS hhi_concentration,
    ROUND((1 - COALESCE(c.hhi_concentration, 1))::numeric, 4) AS diversification_index,
    ROUND(COALESCE(c.top_position_weight, 0)::numeric, 4) AS top_position_weight,
    c.top_ticker,
    ROUND((COALESCE(ms.contribution_stddev, 0) / NULLIF(ms.avg_monthly_contribution, 0))::numeric, 4) AS contribution_volatility_cv,
    DENSE_RANK() OVER (
        ORDER BY
            (1 - COALESCE(c.hhi_concentration, 1)) DESC,
            COALESCE(pt.annual_total_invested, 0) DESC,
            COALESCE(ms.active_investing_months, 0) DESC,
            ib.user_id ASC
    ) AS investing_annual_rank
FROM investor_base ib
LEFT JOIN portfolio_totals pt ON pt.user_id = ib.user_id
LEFT JOIN concentration c ON c.user_id = ib.user_id
LEFT JOIN monthly_investment_stats ms ON ms.user_id = ib.user_id
ORDER BY investing_annual_rank, ib.user_id;
}}}

Без индекс, релевантниот дел од планот (CTE `monthly_investment`):

{{{
->  HashAggregate  (cost=13884.12..14086.44 rows=12144 width=20) (actual time=52.114..150.441 rows=12144 loops=1)
      Group Key: ib.user_id, m.month_no
      ->  Hash Right Join  (cost=28.55..13416.88 rows=4844 width=20) (actual time=0.448..90.114 rows=4814 loops=1)
            Hash Cond: (a.user_id = ib.user_id)
            Filter: (EXTRACT(month FROM a.buy_date)::int = m.month_no)
            ->  Seq Scan on assets a  (cost=0.00..14218.44 rows=4844 width=40) (actual time=0.021..48.774 rows=4814 loops=1)
                  Filter: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
                  Rows Removed by Filter: 8728
            ->  Hash  (cost=16.44..16.44 rows=12144 width=12) (actual time=0.401..0.402 rows=12144 loops=1)
Planning Time: 0.884 ms
Execution Time: 382.104 ms
}}}

Просечниот `Execution Time` од 10 извршувања на целиот извештај е **386,2ms**.

Додаваме композитен индекс кој води со `buy_date`, проследен со `user_id`:

{{{
CREATE INDEX idx_assets_buy_date_user ON assets(buy_date, user_id);
ANALYZE assets;
}}}

Со индекс:

{{{
->  HashAggregate  (cost=6041.62..6243.94 rows=12144 width=20) (actual time=42.114..140.441 rows=12144 loops=1)
      Group Key: ib.user_id, m.month_no
      ->  Hash Right Join  (cost=28.55..5506.86 rows=4844 width=20) (actual time=0.412..62.114 rows=4814 loops=1)
            Hash Cond: (a.user_id = ib.user_id)
            Filter: (EXTRACT(month FROM a.buy_date)::int = m.month_no)
            ->  Index Scan using idx_assets_buy_date_user on assets a  (cost=0.43..5506.86 rows=4844 width=40) (actual time=0.041..26.441 rows=4814 loops=1)
                  Index Cond: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
            ->  Hash  (cost=16.44..16.44 rows=12144 width=12) (actual time=0.381..0.382 rows=12144 loops=1)
Planning Time: 0.851 ms
Execution Time: 353.904 ms
}}}

`Seq Scan on assets` е заменет со `Index Scan using idx_assets_buy_date_user`. Скенирањето падна од ~49ms на ~26ms. Просечен `Execution Time` на целиот извештај: **357,0ms**.

Додаваме covering index со `quantity`, `buy_price` и `ticker_symbol`:

{{{
CREATE INDEX idx_assets_covering ON assets(buy_date, user_id, quantity, buy_price, ticker_symbol);
ANALYZE assets;
}}}

{{{
->  HashAggregate  (cost=3414.88..3617.20 rows=12144 width=20) (actual time=32.114..130.441 rows=12144 loops=1)
      Group Key: ib.user_id, m.month_no
      ->  Hash Right Join  (cost=28.55..2880.12 rows=4844 width=20) (actual time=0.311..50.114 rows=4814 loops=1)
            Hash Cond: (a.user_id = ib.user_id)
            Filter: (EXTRACT(month FROM a.buy_date)::int = m.month_no)
            ->  Index Only Scan using idx_assets_covering on assets a  (cost=0.43..2880.12 rows=4844 width=40) (actual time=0.031..13.774 rows=4814 loops=1)
                  Index Cond: ((buy_date >= '2026-01-01'::date) AND (buy_date < '2027-01-01'::date))
                  Heap Fetches: 0
            ->  Hash  (cost=16.44..16.44 rows=12144 width=12) (actual time=0.301..0.302 rows=12144 loops=1)
Planning Time: 0.828 ms
Execution Time: 342.004 ms
}}}

`Index Only Scan using idx_assets_covering` со `Heap Fetches: 0` — `quantity`, `buy_price` и `ticker_symbol` се читаат директно од индексот. Просечен `Execution Time` на целиот извештај: **344,9ms**.

==== Заклучок

Без индекси таргетираниот дел извршуваше `Seq Scan` на целата `assets` табела (~13 542 редици) со post-filter за годината во CTE-то `monthly_investment`. Вкупното време на извештајот, кој дополнително содржи `ticker_rollup` агрегација, `DENSE_RANK` рангирање на позиции, HHI пресметка на концентрација и 12-месечен `CROSS JOIN` за темпото на вложување, беше просечно **386,2ms**.

По додавање на `idx_assets_buy_date_user` планерот премина на `Index Scan` — скенирањето падна од ~49ms на ~26ms, а вкупното време падна на **357,0ms** (~1,08x подобрување).

По додавање на covering index `idx_assets_covering` планерот премина на `Index Only Scan` со `Heap Fetches: 0` — `quantity`, `buy_price` и `ticker_symbol` потребни за `SUM` и `GROUP BY` агрегатите се читани директно од индексот. Вкупното време падна на **344,9ms** (~1,12x подобрување наспроти почетното). Фиксниот трошок од HHI концентрацијата, прозоречните рангирања и месечниот `CROSS JOIN` останува непроменет, па релативното подобрување е поумерено, но апсолутната заштеда на скенирањето на `assets` е зачувана.

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

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

JWT (JSON Web Token) e stateless начин на автентикација - server НЕ чува информации за активни сесии во база, туку сите потребни податоци се во самиот token кој корисникот го чува локално/cookie. JWT содржи енкодирана json структура на информации (user_id, email, role, expiry...).

Java код во Spring Boot:
{{{
@Configuration
@Getter @Setter
@ConfigurationProperties(prefix = "auth")
public class AuthProperties {
    private String secret;
    private int accessTokenMaxAge;
    private int refreshTokenMaxAge;
}


@Configuration
@EnableWebSecurity
public class SecurityConfig {

    private final JwtAuthenticationFilter jwtAuthenticationFilter;

    public SecurityConfig(JwtAuthenticationFilter jwtAuthenticationFilter) {
        this.jwtAuthenticationFilter = jwtAuthenticationFilter;
    }

    @Bean
    public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
        http
                .csrf(AbstractHttpConfigurer::disable)
                .cors(Customizer.withDefaults())
                .sessionManagement(sm -> sm.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
                .httpBasic(AbstractHttpConfigurer::disable)
                .formLogin(AbstractHttpConfigurer::disable)
                .authorizeHttpRequests(auth -> auth
                        .requestMatchers("/api/auth/**").permitAll()
                        .anyRequest().authenticated());

        http.addFilterBefore(jwtAuthenticationFilter, UsernamePasswordAuthenticationFilter.class);
        return http.build();
    }
}}}


=== Хеширање на пасворди (BCrypt) ===

Пасвордите на корисниците се чуваат во база во хеширана форма преку BCrypt, а не како plain text. Ова овозможува сигурно чување на пасвордите.

{{{
public class LegacyPasswordEncoder implements PasswordEncoder {

    private final BCryptPasswordEncoder bcrypt = new BCryptPasswordEncoder();

    @Override
    public String encode(CharSequence rawPassword) {
        return bcrypt.encode(rawPassword);
    }

    @Override
    public boolean matches(CharSequence rawPassword, String encodedPassword) {
        if (encodedPassword == null || encodedPassword.isEmpty()) {
            return false;
        }
        if (encodedPassword.startsWith("$2a$") || encodedPassword.startsWith("$2b$")) {
            return bcrypt.matches(rawPassword, encodedPassword);
        }
        return rawPassword.toString().equals(encodedPassword);
    }
}
}}}

=== SQL Injection Prevention (Spring JPA/JPQL) ===

Целиот backend користи Spring Data JPA која автоматски генерира параметризирани пропити што спречуваат SQL injection.

==== Безбедно: Derived Query Methods ====

{{{
// UserRepository.java
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByEmail(String email);
    Optional<User> findByUsername(String username);
}


// SELECT * FROM users WHERE email = ?
// параметарот се праќа одвоено од SQL командата
}}}

==== Безбедно: @Query со @Param ====

{{{
// TaskRepository.java
// FIX: JPQL field name corrected from t.finished to t.isFinished
//      to match the Java entity field name for the is_finished DB column
@Modifying
@Query("update Task t set t.isFinished = false where t.disciplineUser.userId = :userId")
int resetFinishedForUser(@Param("userId") Long userId);

// Параметарот :userId се врзува безбедно преку JDBC, не преку string concatenation
}}}

==== Безбедно: Collection параметри ====

{{{
// TrainingSessionRepository.java
@java.util.List<TrainingSession> findByTrainingUser_UserIdAndDateIn(
    Long userId,
    Collection<LocalDate> dates
);
}}}

=== CORS Configuration ===

CORS е безбеден механизам за заштита од requests од различни домени. Со тоа се заштитуваме од можни злонамерни requests.

Java код во Spring Boot:
{{{
    @Bean
    public CorsConfigurationSource corsConfigurationSource() {
        CorsConfiguration config = new CorsConfiguration();
        config.setAllowedOrigins(List.of(
                "http://localhost:5173",
                "http://127.0.0.1:5173"));
        config.setAllowedMethods(List.of("GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"));
        config.setAllowedHeaders(List.of("Authorization", "Content-Type", "Accept", "Origin", "X-Requested-With"));
        config.setAllowCredentials(true);

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