Changes between Version 5 and Version 6 of AdvancedDatabaseDevelopment

Timestamp:

07/02/26 21:33:02 (4 days ago)

Author:

233062

Comment:

--

AdvancedDatabaseDevelopment

@@ -1 @@
-= Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)
-
-=== 1. Детален годишен извештај за финансиска резилиентност, стабилност на приходи и буџетски притисок по корисник
-
-==== SQL
-{{{
-SET search_path TO trekr;
-
-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
+= Напреден развој на базата =
+
+== Валидација на финансиски проценти (Finance Percentage Validation) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Сите пет вредности за буџет мора да бидат внесени (не смеат да бидат NULL)
+* Секоја вредност мора да биде во опсегот [0, 100]
+* Збирот на сите пет вредности мора да биде 100 (со толеранција од 0.01 за мали разлики при заокружување)
+* Ограничувањето се применува и при INSERT и при UPDATE на табелата finance_users
+
+=== Имплементација ===
+
+==== Тригери ====
+
+BEFORE INSERT OR UPDATE тригер на finance_users за валидација дека сите пет буџетски проценти се валидни и нивниот збир е 100.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_validate_finance_percentages()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    s   NUMERIC;
+    eps CONSTANT NUMERIC := 0.01;
+    vals NUMERIC[] := ARRAY[NEW.spending_budget, NEW.saving_budget,
+                            NEW.investing_budget, NEW.donation_budget, NEW.credit];
+    v NUMERIC;
+BEGIN
+    FOREACH v IN ARRAY vals LOOP
+        IF v IS NULL THEN
+            RAISE EXCEPTION 'All 5 finance percentage values are required';
+        END IF;
+        IF v < 0 OR v > 100 THEN
+            RAISE EXCEPTION 'Finance percentage values must be between 0 and 100';
+        END IF;
+    END LOOP;
+
+    s := (NEW.spending_budget + NEW.saving_budget + NEW.investing_budget
+          + NEW.donation_budget + NEW.credit)::numeric;
+    IF abs(s - 100) > eps THEN
+        RAISE EXCEPTION 'Finance percentages must sum to 100 (got: %)', s;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_validate_finance_percentages ON trekr.finance_users;
+CREATE TRIGGER trg_validate_finance_percentages
+    BEFORE INSERT OR UPDATE ON trekr.finance_users
+    FOR EACH ROW
+    EXECUTE FUNCTION trekr.fn_validate_finance_percentages();
+}}}
+
+----
+
+== Пресметување на дневни завршувања (Daily Completion Computation) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Дневното завршување може да се пресмета само за корисник со овозможено следење (discipline_users)
+* Не смее да се пресметува за иден датум
+* Ако веќе постои запис за тој корисник и датум, се враќа постоечкиот резултат без дупликат
+* По пресметувањето, завршените задачи се врзуваат за дневниот запис, а потоа нивниот статус се ресетира
+
+=== Имплементација ===
+
+==== Функции / Stored Procedures ====
+
+Функција за пресметување на дневно завршување за еден корисник и датум. Вметнува ред во daily_completion (доколку не постои), ги поврзува завршените задачи и го ресетира нивниот статус. Параметрите се именувани со префикс `p_` за да се избегне двосмисленост со имињата на колоните во plpgsql.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(
+    p_user_id bigint,
+    p_day     date
 )
+RETURNS TABLE(created boolean, out_daily_completion_id bigint, procent numeric)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    total_count    bigint;
+    finished_count bigint;
+    pct   numeric;
+    dc_id bigint;
+    t_row RECORD;
+BEGIN
+    IF p_user_id IS NULL THEN
+        RAISE EXCEPTION 'user_id is required';
+    END IF;
+    IF p_day IS NULL THEN
+        RAISE EXCEPTION 'day is required';
+    END IF;
+    IF p_day > current_date THEN
+        RAISE EXCEPTION 'date cannot be in the future';
+    END IF;
+
+    IF NOT EXISTS (SELECT 1 FROM trekr.discipline_users du WHERE du.user_id = p_user_id) THEN
+        RAISE EXCEPTION 'Discipline tracking is not enabled for this user';
+    END IF;
+
+    -- доколку веќе постои запис за овој корисник и датум, врати го постоечкиот (идемпотентно)
+    SELECT dc.daily_completion_id, dc.procent
+      INTO dc_id, pct
+    FROM trekr.daily_completion dc
+    WHERE dc.user_id = p_user_id AND dc.date = p_day
+    LIMIT 1;
+
+    IF dc_id IS NOT NULL THEN
+        RETURN QUERY SELECT false, dc_id, pct;
+        RETURN;
+    END IF;
+
+    SELECT COUNT(*) INTO total_count
+    FROM trekr.tasks t
+    WHERE t.discipline_user_id = p_user_id;
+
+    SELECT COUNT(*) INTO finished_count
+    FROM trekr.tasks t
+    WHERE t.discipline_user_id = p_user_id
+      AND t.is_finished = true;
+
+    IF total_count <= 0 THEN
+        pct := 0;
+    ELSE
+        pct := round((finished_count::numeric * 100) / total_count::numeric, 2);
+    END IF;
+
+    INSERT INTO trekr.daily_completion (user_id, date, procent)
+    VALUES (p_user_id, p_day, pct)
+    RETURNING daily_completion_id INTO dc_id;
+
+    -- врзи ги завршените задачи за дневниот запис
+    FOR t_row IN
+        SELECT t.task_id FROM trekr.tasks t
+        WHERE t.discipline_user_id = p_user_id
+          AND t.is_finished = true
+    LOOP
+        INSERT INTO trekr.task_daily_completion (task_id, daily_completion_id)
+        VALUES (t_row.task_id, dc_id)
+        ON CONFLICT DO NOTHING;
+    END LOOP;
+
+    -- ресетирај го статусот на завршените задачи
+    UPDATE trekr.tasks t SET is_finished = false
+    WHERE t.discipline_user_id = p_user_id;
+
+    RETURN QUERY SELECT true, dc_id, pct;
+END;
+$$;
+}}}
+
+Функција за пресметување на дневни завршувања за сите корисници со овозможено следење за даден датум. Грешките по корисник се логираат и се продолжува понатаму.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(p_day date)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    u RECORD;
+BEGIN
+    IF p_day IS NULL THEN
+        RAISE EXCEPTION 'day is required';
+    END IF;
+
+    FOR u IN SELECT user_id FROM trekr.discipline_users LOOP
+        BEGIN
+            PERFORM trekr.fn_compute_daily_completion(u.user_id, p_day);
+        EXCEPTION WHEN OTHERS THEN
+            RAISE NOTICE 'compute_daily_completion failed for user %: %', u.user_id, SQLERRM;
+        END;
+    END LOOP;
+END;
+$$;
+
+-- Опционално: pg_cron задача за секојдневно извршување (бара pg_cron екстензија)
+-- CREATE EXTENSION IF NOT EXISTS pg_cron;
+-- SELECT cron.schedule('compute_daily_completions_every_day', '59 23 * * *',
+--     $$SELECT trekr.fn_compute_daily_completion_for_all(current_date - INTERVAL '1 day')$$);
+}}}
+
+----
+
+== Дополнителни ограничувања на базата (Additional DB Constraints) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Корисникот може да има само еден дневен внес (daily intake) по датум
+* Тренинг сесиите не смеат да имаат иден датум
+
+=== Имплементација ===
+
+==== Индекси ====
+
+Уникатен индекс на daily_intakes за осигурување дека еден корисник може да има најмногу еден внес по датум. (Колоната во daily_intakes е `user_id` — надворешен клуч кон weight_users.)
+{{{
+-- Напомена: доколку веќе има дупликати (user_id, date) во податоците,
+-- прво отстранете ги, инаку креирањето на уникатниот индекс ќе падне:
+--   DELETE FROM trekr.daily_intakes a USING trekr.daily_intakes b
+--   WHERE a.ctid < b.ctid AND a.user_id = b.user_id AND a.date = b.date;
+
+DO $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM pg_indexes
+        WHERE schemaname = 'trekr'
+          AND tablename  = 'daily_intakes'
+          AND indexname  = 'uq_daily_intake_user_date'
+    ) THEN
+        CREATE UNIQUE INDEX uq_daily_intake_user_date
+        ON trekr.daily_intakes (user_id, date);
+    END IF;
+END$$;
+}}}
+
+==== Тригери ====
+
+BEFORE INSERT OR UPDATE тригер на training_sessions за спречување на внес со иден датум.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_check_training_date()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NEW.date > current_date THEN
+        RAISE EXCEPTION 'Training session date cannot be in the future: %', NEW.date;
+    END IF;
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_check_training_date ON trekr.training_sessions;
+CREATE TRIGGER trg_check_training_date
+    BEFORE INSERT OR UPDATE ON trekr.training_sessions
+    FOR EACH ROW
+    EXECUTE FUNCTION trekr.fn_check_training_date();
+}}}
+
+----
+
+== Прегледи за финансии (Finance Views) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Постои преглед за месечен приход по корисник и период
+* Постои преглед за вкупниот приход на корисникот во тековниот месец
+* Постои преглед за пресметани апсолутни износи по категорија врз основа на процентите и приходот во тековниот месец
+
+=== Имплементација ===
+
+==== Погледи (Views) ====
+
+Поглед за месечен приход по корисник — прикажува вкупен приход по корисник, месец и година.
+{{{
+CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS
 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;
-}}}
-
-==== Релациона Алгебра
-{{{
-FB <- pi_{fu.user_id, u.username, u.email,
-          COALESCE(fu.spending_budget,0)->spending_budget,
-          COALESCE(fu.saving_budget,0)->saving_budget,
-          COALESCE(fu.investing_budget,0)->investing_budget,
-          COALESCE(fu.donation_budget,0)->donation_budget,
-          COALESCE(fu.credit,0)->credit}
-      (finance_users fu bowtie_{fu.user_id = u.user_id} users u)
-
-FBM <- FB x M
-IY <- sigma_{i.date >= '2026-01-01' AND i.date < '2027-01-01'}(incomes i)
-MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY
-MI <- gamma_{user_id, month_no;
-             SUM(COALESCE(i.amount,0))->month_income}(MI0)
-
-MIR <- omega_{PARTITION BY user_id ORDER BY month_income DESC, month_no ASC;
-              DENSE_RANK()->best_month_rank,
-              DENSE_RANK(PARTITION BY user_id ORDER BY month_income ASC, month_no ASC)->worst_month_rank}(MI)
-
-AI <- gamma_{user_id;
-             SUM(month_income)->total_income,
-             AVG(month_income)->avg_monthly_income,
-             STDDEV_SAMP(month_income)->income_stddev,
-             MAX(month_income)->best_month_income,
-             MIN(month_income)->worst_month_income,
-             COUNT_IF(month_income>0)->active_income_months}(MI)
-
-BWM <- gamma_{user_id;
-              MAX_IF(month_no, best_month_rank=1)->best_month_no,
-              MAX_IF(month_no, worst_month_rank=1)->worst_month_no}(MIR)
-
-R0 <- FB bowtie_{FB.user_id=AI.user_id} AI bowtie_{FB.user_id=BWM.user_id} BWM
-R1 <- alpha_{(spending_budget+saving_budget+investing_budget+donation_budget)*12->planned_annual_budget,
-             total_income->actual_annual_income,
-             income_stddev/NULLIF(avg_monthly_income,0)->income_volatility_cv,
-             total_income-(spending_budget*12)->annual_free_cash_after_spending,
-             (spending_budget*12)/NULLIF(total_income,0)->spending_pressure_ratio,
-             credit/NULLIF(total_income,0)->leverage_ratio}(R0)
-R  <- omega_{ORDER BY annual_free_cash_after_spending DESC,
-                    spending_pressure_ratio ASC,
-                    user_id ASC;
-             DENSE_RANK()->finance_resilience_rank}(R1)
-}}}
-
-=== 2. Детален годишен извештај за конзистентност на тренинг, оптоварување и тренд на перформанс
-
-==== SQL
-{{{
-SET search_path TO trekr;
-
-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
-)
+    i.user_id,
+    EXTRACT(YEAR  FROM i.date)::int AS year,
+    EXTRACT(MONTH FROM i.date)::int AS month,
+    SUM(i.amount) AS total_income
+FROM trekr.incomes i
+GROUP BY i.user_id, EXTRACT(YEAR FROM i.date), EXTRACT(MONTH FROM i.date);
+}}}
+
+Поглед за вкупниот приход на секој корисник во тековниот месец.
+{{{
+CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS
 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;
-}}}
-
-==== Релациона Алгебра
-{{{
-TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight}
-      (training_users tu bowtie_{tu.user_id = u.user_id} users u)
-
-TBM <- TB x M
-TSY <- sigma_{ts.date >= '2026-01-01' AND ts.date < '2027-01-01'}(training_sessions ts)
-MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY
-MS <- gamma_{user_id, month_no;
-             COUNT(ts.training_id)->sessions_count,
-             SUM(COALESCE(ts.duration,0))->total_duration_minutes,
-             SUM(COALESCE(ts.calories,0))->total_calories,
-             AVG(COALESCE(ts.duration,0))->avg_session_duration,
-             AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0)
-
-MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC;
-             DENSE_RANK()->peak_calorie_month_rank,
-             DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS)
-
-AMS <- sigma_{sessions_count>0}(MS)
-AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no;
-               ROW_NUMBER()->rn}(AMS)
-AMS2 <- alpha_{month_no - rn -> grp}(AMS1)
-LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2)
-LS  <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0)
-
-AT <- gamma_{user_id;
-             SUM(sessions_count)->annual_sessions,
-             SUM(total_duration_minutes)->annual_duration_minutes,
-             SUM(total_calories)->annual_calories,
-             AVG(total_duration_minutes)->avg_monthly_duration,
-             AVG(total_calories)->avg_monthly_calories,
-             COUNT_IF(sessions_count>0)->active_months,
-             REGR_SLOPE(total_calories, month_no)->calories_trend_slope,
-             REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS)
-
-PM <- gamma_{user_id;
-             MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no,
-             MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR)
-
-R0 <- TB bowtie_{TB.user_id=AT.user_id} AT
-         bowtie_{TB.user_id=PM.user_id} PM
-         leftouterjoin_{TB.user_id=LS.user_id} LS
-R1 <- alpha_{active_months/12.0->consistency_ratio,
-             COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz,
-             COALESCE(calories_trend_slope,0)->calories_trend_slope_nz,
-             COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0)
-R  <- omega_{ORDER BY annual_calories DESC,
-                    active_months DESC,
-                    longest_active_month_streak_nz DESC,
-                    user_id ASC;
-             DENSE_RANK()->training_annual_rank}(R1)
-}}}
-
-=== 3. Детален годишен извештај за дисциплина, квалитет на завршување и однесување преку streaks
-
-==== SQL
-{{{
-SET search_path TO trekr;
-
-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
-)
+    f.user_id,
+    COALESCE(SUM(i.amount), 0) AS total_earned_this_month
+FROM trekr.finance_users f
+LEFT JOIN trekr.incomes i
+    ON i.user_id = f.user_id
+   AND date_trunc('month', i.date) = date_trunc('month', current_date)
+GROUP BY f.user_id;
+}}}
+
+Поглед за пресметување на апсолутни износи по финансиска категорија за тековниот месец, врз основа на буџетските проценти и вкупниот приход.
+{{{
+CREATE OR REPLACE VIEW trekr.vw_finance_allocations_current_month AS
 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;
-}}}
-
-==== Релациона Алгебра
-{{{
-DB <- pi_{du.user_id, u.username, u.email}
-      (discipline_users du bowtie_{du.user_id = u.user_id} users u)
-
-TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c
-TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC)
-TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0)
-TM <- gamma_{owner_user_id;
-             COUNT(*)->total_tasks_defined,
-             COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks,
-             COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks,
-             COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1)
-
-ADC0 <- sigma_{YEAR(dc.date)=2026}(daily_completion dc)
-ADC <- alpha_{COALESCE(dc.procent,0)->procent,
-              CASE(procent>=80,1,0)->strong_day}(ADC0)
-
-DCS <- gamma_{user_id;
-              COUNT(*)->tracked_days,
-              AVG(procent)->avg_completion_percent,
-              PERCENTILE_CONT_0_5(procent)->median_completion_percent,
-              COUNT_IF(procent=100)->perfect_days,
-              COUNT_IF(procent>=80)->strong_days,
-              STDDEV_SAMP(procent)->completion_variability}(ADC)
-
-SDS0 <- sigma_{strong_day=1}(ADC)
-SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0)
-SDS2 <- alpha_{date - rn -> grp}(SDS1)
-LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2)
-LSS  <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0)
-
-ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc
-ATE  <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0)
-
-R0 <- DB
-      leftouterjoin_{DB.user_id = TM.owner_user_id} TM
-      leftouterjoin_{DB.user_id = DCS.user_id} DCS
-      leftouterjoin_{DB.user_id = ATE.user_id} ATE
-      leftouterjoin_{DB.user_id = LSS.user_id} LSS
-R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz,
-             COALESCE(core_tasks,0)->core_tasks_nz,
-             COALESCE(custom_tasks,0)->custom_tasks_nz,
-             COALESCE(task_category_span,0)->task_category_span_nz,
-             COALESCE(tracked_days,0)->tracked_days_nz,
-             COALESCE(avg_completion_percent,0)->avg_completion_percent_nz,
-             COALESCE(median_completion_percent,0)->median_completion_percent_nz,
-             COALESCE(perfect_days,0)->perfect_days_nz,
-             COALESCE(strong_days,0)->strong_days_nz,
-             COALESCE(completion_variability,0)->completion_variability_nz,
-             COALESCE(completed_task_events,0)->completed_task_events_nz,
-             COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz,
-             COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio,
-             (COALESCE(avg_completion_percent,0)*0.45 +
-              COALESCE(longest_strong_day_streak,0)*2.00 +
-              COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0)
-R  <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC;
-             DENSE_RANK()->discipline_annual_rank}(R1)
-}}}
-
-=== 4. Детален годишен извештај за инвестициска диверзификација, концентрација и темпо на вложување
-
-==== SQL
-{{{
-SET search_path TO trekr;
-
-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;
-}}}
-
-==== Релациона Алгебра
-{{{
-IB <- pi_{iu.user_id, u.username, u.email}
-      (investor_users iu bowtie_{iu.user_id = u.user_id} users u)
-
-AAL <- pi_{a.user_id, a.ticker_symbol,
-           COALESCE(a.quantity,0)->quantity,
-           COALESCE(a.buy_price,0)->buy_price,
-           COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount,
-           a.buy_date}
-       (sigma_{a.buy_date >= '2026-01-01' AND a.buy_date < '2027-01-01'}(assets a))
-
-TR <- gamma_{user_id, ticker_symbol;
-             SUM(quantity)->total_quantity,
-             SUM(invested_amount)->total_invested_amount,
-             COUNT(*)->lot_count,
-             MIN(buy_date)->first_buy_date,
-             MAX(buy_date)->last_buy_date}(AAL)
-
-PT <- gamma_{user_id;
-             SUM(total_invested_amount)->annual_total_invested,
-             SUM(lot_count)->annual_lot_count,
-             COUNT(*)->distinct_tickers}(TR)
-
-W0 <- TR bowtie_{TR.user_id = PT.user_id} PT
-W1 <- alpha_{total_invested_amount/NULLIF(annual_total_invested,0)->position_weight}(W0)
-W  <- omega_{PARTITION BY user_id ORDER BY total_invested_amount DESC, ticker_symbol ASC;
-             DENSE_RANK()->position_rank}(W1)
-
-C <- gamma_{user_id;
-            SUM(position_weight*position_weight)->hhi_concentration,
-            MAX(position_weight)->top_position_weight,
-            MAX_IF(ticker_symbol, position_rank=1)->top_ticker}(W)
-
-IBM <- IB x M
-AY  <- sigma_{a.buy_date >= '2026-01-01' AND a.buy_date < '2027-01-01'}(assets a)
-MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY
-MI  <- gamma_{user_id, month_no;
-              SUM(COALESCE(a.quantity,0)*COALESCE(a.buy_price,0))->monthly_invested_amount}(MI0)
-MS  <- gamma_{user_id;
-              AVG(monthly_invested_amount)->avg_monthly_contribution,
-              STDDEV_SAMP(monthly_invested_amount)->contribution_stddev,
-              COUNT_IF(monthly_invested_amount>0)->active_investing_months}(MI)
-
-R0 <- IB
-      leftouterjoin_{IB.user_id=PT.user_id} PT
-      leftouterjoin_{IB.user_id=C.user_id} C
-      leftouterjoin_{IB.user_id=MS.user_id} MS
-R1 <- alpha_{COALESCE(annual_total_invested,0)->annual_total_invested_nz,
-             COALESCE(annual_lot_count,0)->annual_lot_count_nz,
-             COALESCE(distinct_tickers,0)->distinct_tickers_nz,
-             COALESCE(avg_monthly_contribution,0)->avg_monthly_contribution_nz,
-             COALESCE(active_investing_months,0)->active_investing_months_nz,
-             COALESCE(active_investing_months,0)/12.0->activity_ratio,
-             COALESCE(hhi_concentration,0)->hhi_concentration_nz,
-             1-COALESCE(hhi_concentration,1)->diversification_index,
-             COALESCE(top_position_weight,0)->top_position_weight_nz,
-             COALESCE(contribution_stddev/NULLIF(avg_monthly_contribution,0),0)->contribution_volatility_cv}(R0)
-R  <- omega_{ORDER BY diversification_index DESC,
-                    annual_total_invested_nz DESC,
-                    active_investing_months_nz DESC,
-                    user_id ASC;
-             DENSE_RANK()->investing_annual_rank}(R1)
-}}}
-
+    f.user_id,
+    fm.total_earned_this_month,
+    f.spending_budget,
+    f.saving_budget,
+    f.investing_budget,
+    f.donation_budget,
+    f.credit,
+    ROUND((COALESCE(f.spending_budget, 0)  / 100.0) * fm.total_earned_this_month, 2) AS spending_amount,
+    ROUND((COALESCE(f.saving_budget, 0)    / 100.0) * fm.total_earned_this_month, 2) AS saving_amount,
+    ROUND((COALESCE(f.investing_budget, 0) / 100.0) * fm.total_earned_this_month, 2) AS investing_amount,
+    ROUND((COALESCE(f.donation_budget, 0)  / 100.0) * fm.total_earned_this_month, 2) AS donation_amount,
+    ROUND((COALESCE(f.credit, 0)           / 100.0) * fm.total_earned_this_month, 2) AS credit_amount
+FROM trekr.finance_users f
+LEFT JOIN trekr.vw_finance_current_month fm ON fm.user_id = f.user_id;
+}}}