Changes between Version 4 and Version 5 of AdvancedDatabaseDevelopment


Ignore:
Timestamp:
07/02/26 21:32:30 (4 days ago)
Author:
233062
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedDatabaseDevelopment

    v4 v5  
    1 = Напреден развој на базата =
    2 
    3 == Валидација на финансиски проценти (Finance Percentage Validation) ==
    4 
    5 === Опис на барањата за податочни ограничувања ===
    6 
    7 Системот мора да обезбеди дека:
    8 * Сите пет вредности за буџет мора да бидат внесени (не смеат да бидат NULL)
    9 * Секоја вредност мора да биде во опсегот [0, 100]
    10 * Збирот на сите пет вредности мора да биде 100 (со толеранција од 0.01 за мали разлики при заокружување)
    11 * Ограничувањето се применува и при INSERT и при UPDATE на табелата finance_users
    12 
    13 === Имплементација ===
    14 
    15 ==== Тригери ====
    16 
    17 BEFORE INSERT OR UPDATE тригер на finance_users за валидација дека сите пет буџетски проценти се валидни и нивниот збир е 100.
    18 {{{
    19 CREATE OR REPLACE FUNCTION trekr.fn_validate_finance_percentages()
    20 RETURNS trigger
    21 LANGUAGE plpgsql
    22 AS $$
    23 DECLARE
    24     s NUMERIC;
    25     eps CONSTANT NUMERIC := 0.01;
    26     vals NUMERIC[] := ARRAY[NEW.spending_budget, NEW.saving_budget, NEW.investing_budget, NEW.donation_budget, NEW.credit];
    27     v NUMERIC;
    28 BEGIN
    29     FOREACH v IN ARRAY vals LOOP
    30         IF v IS NULL THEN
    31             RAISE EXCEPTION 'All 5 finance percentage values are required';
    32         END IF;
    33         IF v < 0 OR v > 100 THEN
    34             RAISE EXCEPTION 'Finance percentage values must be between 0 and 100';
    35         END IF;
    36     END LOOP;
    37 
    38     s := (NEW.spending_budget + NEW.saving_budget + NEW.investing_budget + NEW.donation_budget + NEW.credit)::numeric;
    39     IF abs(s - 100) > eps THEN
    40         RAISE EXCEPTION 'Finance percentages must sum to 100 (got: %)', s;
    41     END IF;
    42 
    43     RETURN NEW;
    44 END;
    45 $$;
    46 
    47 DO $$
    48 BEGIN
    49     IF NOT EXISTS (
    50         SELECT 1 FROM pg_trigger t
    51         JOIN pg_class c ON t.tgrelid = c.oid
    52         WHERE t.tgname = 'trg_validate_finance_percentages' AND c.relname = 'finance_users'
    53     ) THEN
    54         CREATE TRIGGER trg_validate_finance_percentages
    55         BEFORE INSERT OR UPDATE ON trekr.finance_users
    56         FOR EACH ROW
    57         EXECUTE FUNCTION trekr.fn_validate_finance_percentages();
    58     END IF;
    59 END$$;
    60 }}}
    61 
    62 ----
    63 
    64 == Пресметување на дневни завршувања (Daily Completion Computation) ==
    65 
    66 === Опис на барањата за податочни ограничувања ===
    67 
    68 Системот мора да обезбеди дека:
    69 * Дневното завршување може да се пресмета само за корисник со овозможено следење (discipline_users)
    70 * Не смее да се пресметува за иден датум
    71 * Ако веќе постои запис за тој корисник и датум, се враќа постоечкиот резултат без дупликат
    72 * По пресметувањето, завршените задачи се врзуваат за дневниот запис, а потоа нивниот статус се ресетира
    73 
    74 === Имплементација ===
    75 
    76 ==== Функции / Stored Procedures ====
    77 
    78 Функција за пресметување на дневно завршување за еден корисник и датум. Вметнува ред во daily_completion (доколку не постои), ги поврзува завршените задачи и го ресетира нивниот статус.
    79 {{{
    80 CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(user_id bigint, day date)
    81 RETURNS TABLE(created boolean, daily_completion_id bigint, procent numeric)
    82 LANGUAGE plpgsql
    83 AS $$
    84 DECLARE
    85     total_count bigint;
    86     finished_count bigint;
    87     pct numeric;
    88     dc_id bigint;
    89     finished_tasks RECORD;
    90 BEGIN
    91     IF user_id IS NULL THEN
    92         RAISE EXCEPTION 'user_id is required';
    93     END IF;
    94     IF day IS NULL THEN
    95         RAISE EXCEPTION 'day is required';
    96     END IF;
    97     IF day > current_date THEN
    98         RAISE EXCEPTION 'date cannot be in the future';
    99     END IF;
    100 
    101     IF NOT EXISTS (SELECT 1 FROM trekr.discipline_users du WHERE du.user_id = user_id) THEN
    102         RAISE EXCEPTION 'Discipline tracking is not enabled for this user';
    103     END IF;
    104 
    105    
    106     SELECT dc.daily_completion_id, dc.procent INTO dc_id, pct
    107     FROM trekr.daily_completion dc
    108     WHERE dc.user_id = user_id AND dc.date = day
    109     LIMIT 1;
    110 
    111     IF dc_id IS NOT NULL THEN
    112         RETURN QUERY SELECT false, dc_id, pct;
    113         RETURN;
    114     END IF;
    115 
    116     SELECT COUNT(*) INTO total_count
    117     FROM trekr.tasks t
    118     WHERE t.discipline_user_id = user_id;
    119 
    120     SELECT COUNT(*) INTO finished_count
    121     FROM trekr.tasks t
    122     WHERE t.discipline_user_id = user_id
    123       AND t.is_finished = true;
    124 
    125     IF total_count <= 0 THEN
    126         pct := 0;
    127     ELSE
    128         pct := round((finished_count::numeric * 100) / total_count::numeric, 2);
    129     END IF;
    130 
    131    
    132     INSERT INTO trekr.daily_completion (user_id, date, procent)
    133     VALUES (user_id, day, pct)
    134     RETURNING daily_completion_id INTO dc_id;
    135 
    136    
    137     FOR finished_tasks IN
    138         SELECT t.task_id FROM trekr.tasks t
    139         WHERE t.discipline_user_id = user_id
    140           AND t.is_finished = true
    141     LOOP
    142         INSERT INTO trekr.task_daily_completion (task_id, daily_completion_id)
    143         VALUES (finished_tasks.task_id, dc_id)
    144         ON CONFLICT DO NOTHING;
    145     END LOOP;
    146 
    147     UPDATE trekr.tasks t SET is_finished = false
    148     WHERE t.discipline_user_id = user_id;
    149 
    150     RETURN QUERY SELECT true, dc_id, pct;
    151 END;
    152 $$;
    153 }}}
    154 
    155 Функција за пресметување на дневни завршувања за сите корисници со овозможено следење за даден датум. Грешките по корисник се логираат и се продолжува понатаму.
    156 {{{
    157 CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(day date)
    158 RETURNS void
    159 LANGUAGE plpgsql
    160 AS $$
    161 DECLARE
    162     u RECORD;
    163 BEGIN
    164     IF day IS NULL THEN
    165         RAISE EXCEPTION 'day is required';
    166     END IF;
    167 
    168     FOR u IN SELECT user_id FROM trekr.discipline_users LOOP
    169         BEGIN
    170             PERFORM trekr.fn_compute_daily_completion(u.user_id, day);
    171         EXCEPTION WHEN OTHERS THEN
    172             RAISE NOTICE 'compute_daily_completion failed for user %: %', u.user_id, SQLERRM;
    173         END;
    174     END LOOP;
    175 END;
    176 $$;
    177 
    178 -- Опционално: pg_cron задача за секојдневно извршување (бара pg_cron екстензија)
    179 -- CREATE EXTENSION IF NOT EXISTS pg_cron;
    180 -- SELECT cron.schedule('compute_daily_completions_every_day', '59 23 * * *',
    181 --     $$SELECT trekr.fn_compute_daily_completion_for_all(current_date - INTERVAL '1 day')$$);
    182 }}}
    183 
    184 ----
    185 
    186 == Дополнителни ограничувања на базата (Additional DB Constraints) ==
    187 
    188 === Опис на барањата за податочни ограничувања ===
    189 
    190 Системот мора да обезбеди дека:
    191 * Корисникот може да има само еден дневен внес (daily intake) по датум
    192 * Тренинг сесиите не смеат да имаат иден датум
    193 
    194 === Имплементација ===
    195 
    196 ==== Индекси ====
    197 
    198 Уникатен индекс на daily_intakes за осигурување дека еден корисник може да има најмногу еден внес по датум.
    199 {{{
    200 DO $$
    201 BEGIN
    202     IF NOT EXISTS (
    203         SELECT 1 FROM pg_indexes
    204         WHERE schemaname = 'trekr'
    205           AND tablename = 'daily_intakes'
    206           AND indexname = 'uq_daily_intake_user_date'
    207     ) THEN
    208         CREATE UNIQUE INDEX uq_daily_intake_user_date
    209         ON trekr.daily_intakes (weight_user_id, date);
    210     END IF;
    211 END$$;
    212 }}}
    213 
    214 ==== Тригери ====
    215 
    216 BEFORE INSERT OR UPDATE тригер на training_sessions за спречување на внес со иден датум.
    217 {{{
    218 CREATE OR REPLACE FUNCTION trekr.fn_check_training_date()
    219 RETURNS trigger
    220 LANGUAGE plpgsql
    221 AS $$
    222 BEGIN
    223     IF NEW.date > current_date THEN
    224         RAISE EXCEPTION 'Training session date cannot be in the future: %', NEW.date;
    225     END IF;
    226     RETURN NEW;
    227 END;
    228 $$;
    229 
    230 DO $$
    231 BEGIN
    232     IF NOT EXISTS (
    233         SELECT 1 FROM pg_trigger t
    234         JOIN pg_class c ON t.tgrelid = c.oid
    235         WHERE t.tgname = 'trg_check_training_date' AND c.relname = 'training_sessions'
    236     ) THEN
    237         CREATE TRIGGER trg_check_training_date
    238         BEFORE INSERT OR UPDATE ON trekr.training_sessions
    239         FOR EACH ROW
    240         EXECUTE FUNCTION trekr.fn_check_training_date();
    241     END IF;
    242 END$$;
    243 }}}
    244 
    245 ----
    246 
    247 == Прегледи за финансии (Finance Views) ==
    248 
    249 === Опис на барањата за податочни ограничувања ===
    250 
    251 Системот мора да обезбеди дека:
    252 * Постои преглед за месечен приход по корисник и период
    253 * Постои преглед за вкупниот приход на корисникот во тековниот месец
    254 * Постои преглед за пресметани апсолутни износи по категорија врз основа на процентите и приходот во тековниот месец
    255 
    256 === Имплементација ===
    257 
    258 ==== Погледи (Views) ====
    259 
    260 Поглед за месечен приход по корисник — прикажува вкупен приход по корисник, месец и година.
    261 {{{
    262 
    263 CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS
     1= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)
     2
     3=== 1. Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник
     4
     5==== SQL
     6{{{
     7SET search_path TO trekr;
     8
     9WITH months AS (
     10    SELECT generate_series(1, 12) AS month_no
     11),
     12finance_base AS (
     13    SELECT
     14        fu.user_id,
     15        u.username,
     16        u.email,
     17        COALESCE(fu.spending_budget, 0) AS spending_budget,
     18        COALESCE(fu.saving_budget, 0) AS saving_budget,
     19        COALESCE(fu.investing_budget, 0) AS investing_budget,
     20        COALESCE(fu.donation_budget, 0) AS donation_budget,
     21        COALESCE(fu.credit, 0) AS credit
     22    FROM finance_users fu
     23    JOIN users u ON u.user_id = fu.user_id
     24),
     25monthly_income AS (
     26    SELECT
     27        fb.user_id,
     28        m.month_no,
     29        COALESCE(SUM(i.amount), 0) AS month_income
     30    FROM finance_base fb
     31    CROSS JOIN months m
     32    LEFT JOIN incomes i
     33        ON i.user_id = fb.user_id
     34       AND i.date >= DATE '2026-01-01'
     35       AND i.date <  DATE '2027-01-01'
     36       AND EXTRACT(MONTH FROM i.date)::int = m.month_no
     37    GROUP BY fb.user_id, m.month_no
     38),
     39monthly_income_ranked AS (
     40    SELECT
     41        mi.*,
     42        DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income DESC, mi.month_no ASC) AS best_month_rank,
     43        DENSE_RANK() OVER (PARTITION BY mi.user_id ORDER BY mi.month_income ASC, mi.month_no ASC) AS worst_month_rank
     44    FROM monthly_income mi
     45),
     46annual_income AS (
     47    SELECT
     48        user_id,
     49        SUM(month_income) AS total_income,
     50        AVG(month_income) AS avg_monthly_income,
     51        STDDEV_SAMP(month_income) AS income_stddev,
     52        MAX(month_income) AS best_month_income,
     53        MIN(month_income) AS worst_month_income,
     54        COUNT(*) FILTER (WHERE month_income > 0) AS active_income_months
     55    FROM monthly_income
     56    GROUP BY user_id
     57),
     58best_worst_months AS (
     59    SELECT
     60        user_id,
     61        MAX(month_no) FILTER (WHERE best_month_rank = 1) AS best_month_no,
     62        MAX(month_no) FILTER (WHERE worst_month_rank = 1) AS worst_month_no
     63    FROM monthly_income_ranked
     64    GROUP BY user_id
     65)
    26466SELECT
    265     i.user_id,
    266     EXTRACT(YEAR FROM i.date)::int AS year,
    267     EXTRACT(MONTH FROM i.date)::int AS month,
    268     SUM(i.amount) AS total_income
    269 FROM trekr.incomes i
    270 GROUP BY i.user_id, EXTRACT(YEAR FROM i.date), EXTRACT(MONTH FROM i.date);
    271 }}}
    272 
    273 Поглед за вкупниот приход на секој корисник во тековниот месец.
    274 {{{
    275 
    276 CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS
     67    fb.user_id,
     68    fb.username,
     69    fb.email,
     70    (fb.spending_budget + fb.saving_budget + fb.investing_budget + fb.donation_budget) * 12 AS planned_annual_budget,
     71    ai.total_income AS actual_annual_income,
     72    ai.avg_monthly_income,
     73    ai.active_income_months,
     74    ai.best_month_income,
     75    ai.worst_month_income,
     76    bwm.best_month_no,
     77    bwm.worst_month_no,
     78    ROUND(
     79        (ai.income_stddev / NULLIF(ai.avg_monthly_income, 0))::numeric,
     80        4
     81    ) AS income_volatility_cv,
     82    ROUND(
     83        (ai.total_income - (fb.spending_budget * 12))::numeric,
     84        2
     85    ) AS annual_free_cash_after_spending,
     86    ROUND(
     87        ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0))::numeric,
     88        4
     89    ) AS spending_pressure_ratio,
     90    ROUND(
     91        (fb.credit / NULLIF(ai.total_income, 0))::numeric,
     92        4
     93    ) AS leverage_ratio,
     94    DENSE_RANK() OVER (
     95        ORDER BY
     96            (ai.total_income - (fb.spending_budget * 12)) DESC,
     97            ((fb.spending_budget * 12) / NULLIF(ai.total_income, 0)) ASC,
     98            fb.user_id ASC
     99    ) AS finance_resilience_rank
     100FROM finance_base fb
     101JOIN annual_income ai ON ai.user_id = fb.user_id
     102JOIN best_worst_months bwm ON bwm.user_id = fb.user_id
     103ORDER BY finance_resilience_rank, fb.user_id;
     104}}}
     105
     106==== Релациона Алгебра
     107{{{
     108FB <- pi_{fu.user_id, u.username, u.email,
     109          COALESCE(fu.spending_budget,0)->spending_budget,
     110          COALESCE(fu.saving_budget,0)->saving_budget,
     111          COALESCE(fu.investing_budget,0)->investing_budget,
     112          COALESCE(fu.donation_budget,0)->donation_budget,
     113          COALESCE(fu.credit,0)->credit}
     114      (finance_users fu bowtie_{fu.user_id = u.user_id} users u)
     115
     116FBM <- FB x M
     117IY <- sigma_{i.date >= '2026-01-01' AND i.date < '2027-01-01'}(incomes i)
     118MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY
     119MI <- gamma_{user_id, month_no;
     120             SUM(COALESCE(i.amount,0))->month_income}(MI0)
     121
     122MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC;
     123              DENSE_RANK()->best_month_rank,
     124              DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI)
     125
     126AI <- gamma_{user_id;
     127             SUM(month_income)->total_income,
     128             AVG(month_income)->avg_monthly_income,
     129             STDDEV_SAMP(month_income)->income_stddev,
     130             MAX(month_income)->best_month_income,
     131             MIN(month_income)->worst_month_income,
     132             COUNT_IF(month_income>0)->active_income_months}(MI)
     133
     134BWM <- gamma_{user_id;
     135              MAX_IF(month_no, best_month_rank=1)->best_month_no,
     136              MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR)
     137
     138R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM
     139R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget,
     140             total_income->actual_annual_income,
     141             income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv,
     142             total_income-(spending_budget*12)->annual_free_cash_after_spending,
     143             (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio,
     144             credit/NULLIF(total_income,0)->leverage_ratio}(R0)
     145R  <- omega_{ORDER BY annual_free_cash_after_spending DESC,
     146                    spending_pressure_ratio ASC,
     147                    user_id ASC;
     148             DENSE_RANK()->finance_resilience_rank}(R1)
     149}}}
     150
     151=== 2. Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс
     152
     153==== SQL
     154{{{
     155SET search_path TO trekr;
     156
     157WITH months AS (
     158    SELECT generate_series(1, 12) AS month_no
     159),
     160training_base AS (
     161    SELECT
     162        tu.user_id,
     163        u.username,
     164        u.email,
     165        tu.gender,
     166        tu.age,
     167        tu.weight
     168    FROM training_users tu
     169    JOIN users u ON u.user_id = tu.user_id
     170),
     171monthly_sessions AS (
     172    SELECT
     173        tb.user_id,
     174        m.month_no,
     175        COALESCE(COUNT(ts.training_id), 0) AS sessions_count,
     176        COALESCE(SUM(ts.duration), 0) AS total_duration_minutes,
     177        COALESCE(SUM(ts.calories), 0) AS total_calories,
     178        COALESCE(AVG(ts.duration), 0) AS avg_session_duration,
     179        COALESCE(AVG(ts.calories), 0) AS avg_session_calories
     180    FROM training_base tb
     181    CROSS JOIN months m
     182    LEFT JOIN training_sessions ts
     183        ON ts.training_user_id = tb.user_id
     184       AND ts.date >= DATE '2026-01-01'
     185       AND ts.date <  DATE '2027-01-01'
     186       AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
     187    GROUP BY tb.user_id, m.month_no
     188),
     189monthly_ranked AS (
     190    SELECT
     191        ms.*,
     192        DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.total_calories DESC, ms.month_no ASC) AS peak_calorie_month_rank,
     193        DENSE_RANK() OVER (PARTITION BY ms.user_id ORDER BY ms.sessions_count DESC, ms.month_no ASC) AS peak_sessions_month_rank
     194    FROM monthly_sessions ms
     195),
     196active_month_streaks AS (
     197    SELECT
     198        user_id,
     199        month_no,
     200        month_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY month_no) AS grp
     201    FROM monthly_sessions
     202    WHERE sessions_count > 0
     203),
     204longest_streak AS (
     205    SELECT
     206        user_id,
     207        MAX(streak_len) AS longest_active_month_streak
     208    FROM (
     209        SELECT user_id, grp, COUNT(*) AS streak_len
     210        FROM active_month_streaks
     211        GROUP BY user_id, grp
     212    ) s
     213    GROUP BY user_id
     214),
     215annual_training AS (
     216    SELECT
     217        user_id,
     218        SUM(sessions_count) AS annual_sessions,
     219        SUM(total_duration_minutes) AS annual_duration_minutes,
     220        SUM(total_calories) AS annual_calories,
     221        AVG(total_duration_minutes) AS avg_monthly_duration,
     222        AVG(total_calories) AS avg_monthly_calories,
     223        COUNT(*) FILTER (WHERE sessions_count > 0) AS active_months,
     224        REGR_SLOPE(total_calories::numeric, month_no::numeric) AS calories_trend_slope,
     225        REGR_SLOPE(total_duration_minutes::numeric, month_no::numeric) AS duration_trend_slope
     226    FROM monthly_sessions
     227    GROUP BY user_id
     228),
     229peak_months AS (
     230    SELECT
     231        user_id,
     232        MAX(month_no) FILTER (WHERE peak_calorie_month_rank = 1) AS peak_calorie_month_no,
     233        MAX(month_no) FILTER (WHERE peak_sessions_month_rank = 1) AS peak_sessions_month_no
     234    FROM monthly_ranked
     235    GROUP BY user_id
     236)
    277237SELECT
    278     f.user_id,
    279     COALESCE(SUM(i.amount), 0) AS total_earned_this_month
    280 FROM trekr.finance_users f
    281 LEFT JOIN trekr.incomes i
    282     ON i.user_id = f.user_id
    283     AND date_trunc('month', i.date) = date_trunc('month', current_date)
    284 GROUP BY f.user_id;
    285 }}}
    286 
    287 Поглед за пресметување на апсолутни износи по финансиска категорија за тековниот месец, врз основа на буџетските проценти и вкупниот приход.
    288 {{{
    289 CREATE OR REPLACE VIEW trekr.vw_finance_allocations_current_month AS
     238    tb.user_id,
     239    tb.username,
     240    tb.email,
     241    tb.gender,
     242    tb.age,
     243    tb.weight,
     244    at.annual_sessions,
     245    ROUND(at.annual_duration_minutes::numeric, 2) AS annual_duration_minutes,
     246    ROUND(at.annual_calories::numeric, 2) AS annual_calories,
     247    at.active_months,
     248    ROUND((at.active_months / 12.0)::numeric, 4) AS consistency_ratio,
     249    COALESCE(ls.longest_active_month_streak, 0) AS longest_active_month_streak,
     250    pm.peak_calorie_month_no,
     251    pm.peak_sessions_month_no,
     252    ROUND(COALESCE(at.calories_trend_slope, 0)::numeric, 4) AS calories_trend_slope,
     253    ROUND(COALESCE(at.duration_trend_slope, 0)::numeric, 4) AS duration_trend_slope,
     254    DENSE_RANK() OVER (
     255        ORDER BY
     256            at.annual_calories DESC,
     257            at.active_months DESC,
     258            COALESCE(ls.longest_active_month_streak, 0) DESC,
     259            tb.user_id ASC
     260    ) AS training_annual_rank
     261FROM training_base tb
     262JOIN annual_training at ON at.user_id = tb.user_id
     263JOIN peak_months pm ON pm.user_id = tb.user_id
     264LEFT JOIN longest_streak ls ON ls.user_id = tb.user_id
     265ORDER BY training_annual_rank, tb.user_id;
     266}}}
     267
     268==== Релациона Алгебра
     269{{{
     270TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight}
     271      (training_users tu bowtie_{tu.user_id = u.user_id} users u)
     272
     273TBM <- TB x M
     274TSY <- sigma_{ts.date >= '2026-01-01' AND ts.date < '2027-01-01'}(training_sessions ts)
     275MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY
     276MS <- gamma_{user_id, month_no;
     277             COUNT(ts.training_id)->sessions_count,
     278             SUM(COALESCE(ts.duration,0))->total_duration_minutes,
     279             SUM(COALESCE(ts.calories,0))->total_calories,
     280             AVG(COALESCE(ts.duration,0))->avg_session_duration,
     281             AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0)
     282
     283MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC;
     284             DENSE_RANK()->peak_calorie_month_rank,
     285             DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS)
     286
     287AMS <- sigma_{sessions_count>0}(MS)
     288AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no;
     289               ROW_NUMBER()->rn}(AMS)
     290AMS2 <- alpha_{month_no - rn -> grp}(AMS1)
     291LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2)
     292LS  <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0)
     293
     294AT <- gamma_{user_id;
     295             SUM(sessions_count)->annual_sessions,
     296             SUM(total_duration_minutes)->annual_duration_minutes,
     297             SUM(total_calories)->annual_calories,
     298             AVG(total_duration_minutes)->avg_monthly_duration,
     299             AVG(total_calories)->avg_monthly_calories,
     300             COUNT_IF(sessions_count>0)->active_months,
     301             REGR_SLOPE(total_calories, month_no)->calories_trend_slope,
     302             REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS)
     303
     304PM <- gamma_{user_id;
     305             MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no,
     306             MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR)
     307
     308R0 <- TB bowtie_{TB.user_id=AT.user_id} AT
     309         bowtie_{TB.user_id=PM.user_id} PM
     310         leftouterjoin_{TB.user_id=LS.user_id} LS
     311R1 <- alpha_{active_months/12.0->consistency_ratio,
     312             COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz,
     313             COALESCE(calories_trend_slope,0)->calories_trend_slope_nz,
     314             COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0)
     315R  <- omega_{ORDER BY annual_calories DESC,
     316                    active_months DESC,
     317                    longest_active_month_streak_nz DESC,
     318                    user_id ASC;
     319             DENSE_RANK()->training_annual_rank}(R1)
     320}}}
     321
     322=== 3. Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks
     323
     324==== SQL
     325{{{
     326SET search_path TO trekr;
     327
     328WITH discipline_base AS (
     329    SELECT
     330        du.user_id,
     331        u.username,
     332        u.email
     333    FROM discipline_users du
     334    JOIN users u ON u.user_id = du.user_id
     335),
     336task_mix AS (
     337    SELECT
     338        COALESCE(t.discipline_user_id, c.user_id) AS user_id,
     339        COUNT(*) AS total_tasks_defined,
     340        COUNT(*) FILTER (WHERE t.custom_tracking_id IS NULL) AS core_tasks,
     341        COUNT(*) FILTER (WHERE t.custom_tracking_id IS NOT NULL) AS custom_tasks,
     342        COUNT(DISTINCT COALESCE(t.custom_tracking_id::text, 'core')) AS task_category_span
     343    FROM tasks t
     344    LEFT JOIN custom_tracking_categories c
     345        ON c.custom_tracking_id = t.custom_tracking_id
     346    WHERE t.discipline_user_id IS NOT NULL
     347       OR t.custom_tracking_id IS NOT NULL
     348    GROUP BY COALESCE(t.discipline_user_id, c.user_id)
     349),
     350annual_daily_completion AS (
     351    SELECT
     352        dc.user_id,
     353        dc.date,
     354        COALESCE(dc.procent, 0) AS procent,
     355        CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day
     356    FROM daily_completion dc
     357    WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
     358),
     359daily_completion_stats AS (
     360    SELECT
     361        adc.user_id,
     362        COUNT(*) AS tracked_days,
     363        AVG(adc.procent) AS avg_completion_percent,
     364        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY adc.procent) AS median_completion_percent,
     365        COUNT(*) FILTER (WHERE adc.procent = 100) AS perfect_days,
     366        COUNT(*) FILTER (WHERE adc.procent >= 80) AS strong_days,
     367        STDDEV_SAMP(adc.procent) AS completion_variability
     368    FROM annual_daily_completion adc
     369    GROUP BY adc.user_id
     370),
     371strong_day_streaks AS (
     372    SELECT
     373        user_id,
     374        date,
     375        date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date))::int AS grp
     376    FROM annual_daily_completion
     377    WHERE strong_day = 1
     378),
     379longest_strong_streak AS (
     380    SELECT
     381        user_id,
     382        MAX(streak_len) AS longest_strong_day_streak
     383    FROM (
     384        SELECT user_id, grp, COUNT(*) AS streak_len
     385        FROM strong_day_streaks
     386        GROUP BY user_id, grp
     387    ) s
     388    GROUP BY user_id
     389),
     390annual_task_execution AS (
     391    SELECT
     392        dc.user_id,
     393        COUNT(tdc.task_id) AS completed_task_events
     394    FROM daily_completion dc
     395    LEFT JOIN task_daily_completion tdc
     396        ON tdc.daily_completion_id = dc.daily_completion_id
     397    WHERE EXTRACT(YEAR FROM dc.date)::int = 2026
     398    GROUP BY dc.user_id
     399)
    290400SELECT
    291     f.user_id,
    292     fm.total_earned_this_month,
    293     f.spending_budget,
    294     f.saving_budget,
    295     f.investing_budget,
    296     f.donation_budget,
    297     f.credit,
    298     ROUND((COALESCE(f.spending_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS spending_amount,
    299     ROUND((COALESCE(f.saving_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS saving_amount,
    300     ROUND((COALESCE(f.investing_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS investing_amount,
    301     ROUND((COALESCE(f.donation_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS donation_amount,
    302     ROUND((COALESCE(f.credit, 0) / 100.0) * fm.total_earned_this_month, 2) AS credit_amount
    303 FROM trekr.finance_users f
    304 LEFT JOIN trekr.vw_finance_current_month fm ON fm.user_id = f.user_id;
    305 }}}
     401    db.user_id,
     402    db.username,
     403    db.email,
     404    COALESCE(tm.total_tasks_defined, 0) AS total_tasks_defined,
     405    COALESCE(tm.core_tasks, 0) AS core_tasks,
     406    COALESCE(tm.custom_tasks, 0) AS custom_tasks,
     407    COALESCE(tm.task_category_span, 0) AS task_category_span,
     408    COALESCE(dcs.tracked_days, 0) AS tracked_days,
     409    ROUND(COALESCE(dcs.avg_completion_percent, 0)::numeric, 2) AS avg_completion_percent,
     410    ROUND(COALESCE(dcs.median_completion_percent, 0)::numeric, 2) AS median_completion_percent,
     411    COALESCE(dcs.perfect_days, 0) AS perfect_days,
     412    COALESCE(dcs.strong_days, 0) AS strong_days,
     413    ROUND(COALESCE(dcs.completion_variability, 0)::numeric, 4) AS completion_variability,
     414    COALESCE(ate.completed_task_events, 0) AS completed_task_events,
     415    COALESCE(lss.longest_strong_day_streak, 0) AS longest_strong_day_streak,
     416    ROUND(
     417        COALESCE((COALESCE(dcs.strong_days, 0) / NULLIF(COALESCE(dcs.tracked_days, 0), 0)::numeric), 0),
     418        4
     419    ) AS strong_day_ratio,
     420    ROUND(
     421        (
     422            COALESCE(dcs.avg_completion_percent, 0) * 0.45
     423            + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
     424            + COALESCE(ate.completed_task_events, 0) * 0.35
     425        )::numeric,
     426        2
     427    ) AS discipline_composite_score,
     428    DENSE_RANK() OVER (
     429        ORDER BY
     430            (
     431                COALESCE(dcs.avg_completion_percent, 0) * 0.45
     432                + COALESCE(lss.longest_strong_day_streak, 0) * 2.00
     433                + COALESCE(ate.completed_task_events, 0) * 0.35
     434            ) DESC,
     435            db.user_id ASC
     436    ) AS discipline_annual_rank
     437FROM discipline_base db
     438LEFT JOIN task_mix tm ON tm.user_id = db.user_id
     439LEFT JOIN daily_completion_stats dcs ON dcs.user_id = db.user_id
     440LEFT JOIN annual_task_execution ate ON ate.user_id = db.user_id
     441LEFT JOIN longest_strong_streak lss ON lss.user_id = db.user_id
     442ORDER BY discipline_annual_rank, db.user_id;
     443}}}
     444
     445==== Релациона Алгебра
     446{{{
     447DB <- pi_{du.user_id, u.username, u.email}
     448      (discipline_users du bowtie_{du.user_id = u.user_id} users u)
     449
     450TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c
     451TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC)
     452TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0)
     453TM <- gamma_{owner_user_id;
     454             COUNT(*)->total_tasks_defined,
     455             COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks,
     456             COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks,
     457             COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1)
     458
     459ADC0 <- sigma_{YEAR(dc.date)=2026}(daily_completion dc)
     460ADC <- alpha_{COALESCE(dc.procent,0)->procent,
     461              CASE(procent>=80,1,0)->strong_day}(ADC0)
     462
     463DCS <- gamma_{user_id;
     464              COUNT(*)->tracked_days,
     465              AVG(procent)->avg_completion_percent,
     466              PERCENTILE_CONT_0_5(procent)->median_completion_percent,
     467              COUNT_IF(procent=100)->perfect_days,
     468              COUNT_IF(procent>=80)->strong_days,
     469              STDDEV_SAMP(procent)->completion_variability}(ADC)
     470
     471SDS0 <- sigma_{strong_day=1}(ADC)
     472SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0)
     473SDS2 <- alpha_{date - rn -> grp}(SDS1)
     474LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2)
     475LSS  <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0)
     476
     477ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc
     478ATE  <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0)
     479
     480R0 <- DB
     481      leftouterjoin_{DB.user_id = TM.owner_user_id} TM
     482      leftouterjoin_{DB.user_id = DCS.user_id} DCS
     483      leftouterjoin_{DB.user_id = ATE.user_id} ATE
     484      leftouterjoin_{DB.user_id = LSS.user_id} LSS
     485R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz,
     486             COALESCE(core_tasks,0)->core_tasks_nz,
     487             COALESCE(custom_tasks,0)->custom_tasks_nz,
     488             COALESCE(task_category_span,0)->task_category_span_nz,
     489             COALESCE(tracked_days,0)->tracked_days_nz,
     490             COALESCE(avg_completion_percent,0)->avg_completion_percent_nz,
     491             COALESCE(median_completion_percent,0)->median_completion_percent_nz,
     492             COALESCE(perfect_days,0)->perfect_days_nz,
     493             COALESCE(strong_days,0)->strong_days_nz,
     494             COALESCE(completion_variability,0)->completion_variability_nz,
     495             COALESCE(completed_task_events,0)->completed_task_events_nz,
     496             COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz,
     497             COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio,
     498             (COALESCE(avg_completion_percent,0)*0.45 +
     499              COALESCE(longest_strong_day_streak,0)*2.00 +
     500              COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0)
     501R  <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC;
     502             DENSE_RANK()->discipline_annual_rank}(R1)
     503}}}
     504
     505=== 4. Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување
     506
     507==== SQL
     508{{{
     509SET search_path TO trekr;
     510
     511WITH months AS (
     512    SELECT generate_series(1, 12) AS month_no
     513),
     514investor_base AS (
     515    SELECT
     516        iu.user_id,
     517        u.username,
     518        u.email
     519    FROM investor_users iu
     520    JOIN users u ON u.user_id = iu.user_id
     521),
     522annual_asset_lots AS (
     523    SELECT
     524        a.user_id,
     525        a.ticker_symbol,
     526        COALESCE(a.quantity, 0) AS quantity,
     527        COALESCE(a.buy_price, 0) AS buy_price,
     528        COALESCE(a.quantity, 0) * COALESCE(a.buy_price, 0) AS invested_amount,
     529        a.buy_date
     530    FROM assets a
     531    WHERE a.buy_date >= DATE '2026-01-01'
     532      AND a.buy_date <  DATE '2027-01-01'
     533),
     534ticker_rollup AS (
     535    SELECT
     536        aal.user_id,
     537        aal.ticker_symbol,
     538        SUM(aal.quantity) AS total_quantity,
     539        SUM(aal.invested_amount) AS total_invested_amount,
     540        COUNT(*) AS lot_count,
     541        MIN(aal.buy_date) AS first_buy_date,
     542        MAX(aal.buy_date) AS last_buy_date
     543    FROM annual_asset_lots aal
     544    GROUP BY aal.user_id, aal.ticker_symbol
     545),
     546portfolio_totals AS (
     547    SELECT
     548        user_id,
     549        SUM(total_invested_amount) AS annual_total_invested,
     550        SUM(lot_count) AS annual_lot_count,
     551        COUNT(*) AS distinct_tickers
     552    FROM ticker_rollup
     553    GROUP BY user_id
     554),
     555weights AS (
     556    SELECT
     557        tr.user_id,
     558        tr.ticker_symbol,
     559        tr.total_invested_amount,
     560        pt.annual_total_invested,
     561        (tr.total_invested_amount / NULLIF(pt.annual_total_invested, 0)) AS position_weight,
     562        DENSE_RANK() OVER (
     563            PARTITION BY tr.user_id
     564            ORDER BY tr.total_invested_amount DESC, tr.ticker_symbol ASC
     565        ) AS position_rank
     566    FROM ticker_rollup tr
     567    JOIN portfolio_totals pt ON pt.user_id = tr.user_id
     568),
     569concentration AS (
     570    SELECT
     571        user_id,
     572        SUM(position_weight * position_weight) AS hhi_concentration,
     573        MAX(position_weight) AS top_position_weight,
     574        MAX(ticker_symbol) FILTER (WHERE position_rank = 1) AS top_ticker
     575    FROM weights
     576    GROUP BY user_id
     577),
     578monthly_investment AS (
     579    SELECT
     580        ib.user_id,
     581        m.month_no,
     582        COALESCE(SUM(a.quantity * a.buy_price), 0) AS monthly_invested_amount
     583    FROM investor_base ib
     584    CROSS JOIN months m
     585    LEFT JOIN assets a
     586        ON a.user_id = ib.user_id
     587       AND a.buy_date >= DATE '2026-01-01'
     588       AND a.buy_date <  DATE '2027-01-01'
     589       AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no
     590    GROUP BY ib.user_id, m.month_no
     591),
     592monthly_investment_stats AS (
     593    SELECT
     594        user_id,
     595        AVG(monthly_invested_amount) AS avg_monthly_contribution,
     596        STDDEV_SAMP(monthly_invested_amount) AS contribution_stddev,
     597        COUNT(*) FILTER (WHERE monthly_invested_amount > 0) AS active_investing_months
     598    FROM monthly_investment
     599    GROUP BY user_id
     600)
     601SELECT
     602    ib.user_id,
     603    ib.username,
     604    ib.email,
     605    COALESCE(pt.annual_total_invested, 0) AS annual_total_invested,
     606    COALESCE(pt.annual_lot_count, 0) AS annual_lot_count,
     607    COALESCE(pt.distinct_tickers, 0) AS distinct_tickers,
     608    ROUND(COALESCE(ms.avg_monthly_contribution, 0)::numeric, 2) AS avg_monthly_contribution,
     609    COALESCE(ms.active_investing_months, 0) AS active_investing_months,
     610    ROUND((COALESCE(ms.active_investing_months, 0) / 12.0)::numeric, 4) AS activity_ratio,
     611    ROUND(COALESCE(c.hhi_concentration, 0)::numeric, 4) AS hhi_concentration,
     612    ROUND((1 - COALESCE(c.hhi_concentration, 1))::numeric, 4) AS diversification_index,
     613    ROUND(COALESCE(c.top_position_weight, 0)::numeric, 4) AS top_position_weight,
     614    c.top_ticker,
     615    ROUND((COALESCE(ms.contribution_stddev, 0) / NULLIF(ms.avg_monthly_contribution, 0))::numeric, 4) AS contribution_volatility_cv,
     616    DENSE_RANK() OVER (
     617        ORDER BY
     618            (1 - COALESCE(c.hhi_concentration, 1)) DESC,
     619            COALESCE(pt.annual_total_invested, 0) DESC,
     620            COALESCE(ms.active_investing_months, 0) DESC,
     621            ib.user_id ASC
     622    ) AS investing_annual_rank
     623FROM investor_base ib
     624LEFT JOIN portfolio_totals pt ON pt.user_id = ib.user_id
     625LEFT JOIN concentration c ON c.user_id = ib.user_id
     626LEFT JOIN monthly_investment_stats ms ON ms.user_id = ib.user_id
     627ORDER BY investing_annual_rank, ib.user_id;
     628}}}
     629
     630==== Релациона Алгебра
     631{{{
     632IB <- pi_{iu.user_id, u.username, u.email}
     633      (investor_users iu bowtie_{iu.user_id = u.user_id} users u)
     634
     635AAL <- pi_{a.user_id, a.ticker_symbol,
     636           COALESCE(a.quantity,0)->quantity,
     637           COALESCE(a.buy_price,0)->buy_price,
     638           COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount,
     639           a.buy_date}
     640       (sigma_{a.buy_date >= '2026-01-01' AND a.buy_date < '2027-01-01'}(assets a))
     641
     642TR <- gamma_{user_id, ticker_symbol;
     643             SUM(quantity)->total_quantity,
     644             SUM(invested_amount)->total_invested_amount,
     645             COUNT(*)->lot_count,
     646             MIN(buy_date)->first_buy_date,
     647             MAX(buy_date)->last_buy_date}(AAL)
     648
     649PT <- gamma_{user_id;
     650             SUM(total_invested_amount)->annual_total_invested,
     651             SUM(lot_count)->annual_lot_count,
     652             COUNT(*)->distinct_tickers}(TR)
     653
     654W0 <- TR bowtie_{TR.user_id = PT.user_id} PT
     655W1 <- alpha_{total_invested_amount/NULLIF(annual_total_invested,0)->position_weight}(W0)
     656W  <- omega_{PARTITION BY user_id ORDER BY total_invested_amount DESC, ticker_symbol ASC;
     657             DENSE_RANK()->position_rank}(W1)
     658
     659C <- gamma_{user_id;
     660            SUM(position_weight*position_weight)->hhi_concentration,
     661            MAX(position_weight)->top_position_weight,
     662            MAX_IF(ticker_symbol, position_rank=1)->top_ticker}(W)
     663
     664IBM <- IB x M
     665AY  <- sigma_{a.buy_date >= '2026-01-01' AND a.buy_date < '2027-01-01'}(assets a)
     666MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY
     667MI  <- gamma_{user_id, month_no;
     668              SUM(COALESCE(a.quantity,0)*COALESCE(a.buy_price,0))->monthly_invested_amount}(MI0)
     669MS  <- gamma_{user_id;
     670              AVG(monthly_invested_amount)->avg_monthly_contribution,
     671              STDDEV_SAMP(monthly_invested_amount)->contribution_stddev,
     672              COUNT_IF(monthly_invested_amount>0)->active_investing_months}(MI)
     673
     674R0 <- IB
     675      leftouterjoin_{IB.user_id=PT.user_id} PT
     676      leftouterjoin_{IB.user_id=C.user_id} C
     677      leftouterjoin_{IB.user_id=MS.user_id} MS
     678R1 <- alpha_{COALESCE(annual_total_invested,0)->annual_total_invested_nz,
     679             COALESCE(annual_lot_count,0)->annual_lot_count_nz,
     680             COALESCE(distinct_tickers,0)->distinct_tickers_nz,
     681             COALESCE(avg_monthly_contribution,0)->avg_monthly_contribution_nz,
     682             COALESCE(active_investing_months,0)->active_investing_months_nz,
     683             COALESCE(active_investing_months,0)/12.0->activity_ratio,
     684             COALESCE(hhi_concentration,0)->hhi_concentration_nz,
     685             1-COALESCE(hhi_concentration,1)->diversification_index,
     686             COALESCE(top_position_weight,0)->top_position_weight_nz,
     687             COALESCE(contribution_stddev/NULLIF(avg_monthly_contribution,0),0)->contribution_volatility_cv}(R0)
     688R  <- omega_{ORDER BY diversification_index DESC,
     689                    annual_total_invested_nz DESC,
     690                    active_investing_months_nz DESC,
     691                    user_id ASC;
     692             DENSE_RANK()->investing_annual_rank}(R1)
     693}}}
     694