Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

05/07/26 13:51:36 (3 days ago)

Author:

233062

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Напреден развој на базата =
+
+== Финансиски месечни пресметки и распределба на буџет (Finance Monthly Calculations & Allocation) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Финансискиот корисник мора да внесе точно 5 процентуални вредности за распределба на буџетот.
+* Секоја од процентуалните вредности мора да биде во опсег од 0 до 100.
+* Збирот на сите 5 проценти мора да биде 100.
+* Месечните приходи по корисник мора да можат да се пресметуваат без дополнителна логика во апликацијата.
+* За тековниот месец мора да постои преглед на вкупно заработените средства и распределбата по буџетски категории.
+
+=== Имплементација ===
+
+==== Тригери ====
+
+BEFORE INSERT и BEFORE UPDATE тригер на finance_users за валидација на 5-те процентуални полиња.
+Тригeрот гарантира дека сите вредности се внесени, се во дозволен опсег и дека збирот е 100.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_validate_finance_percentages()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    s NUMERIC;
+    v NUMERIC;
+    vals NUMERIC[] := ARRAY[
+        NEW.spending_budget,
+        NEW.saving_budget,
+        NEW.investing_budget,
+        NEW.donation_budget,
+        NEW.credit
+    ];
+    eps CONSTANT NUMERIC := 0.01;
+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;
+$$;
+
+CREATE TRIGGER trg_validate_finance_percentages
+BEFORE INSERT OR UPDATE ON trekr.finance_users
+FOR EACH ROW
+EXECUTE FUNCTION trekr.fn_validate_finance_percentages();
+}}}
+
+==== Функции / Stored Procedures ====
+
+Функција за пресметка на вкупно заработени средства во тековниот месец за даден финансиски корисник.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_finance_total_earned_this_month(p_user_id bigint)
+RETURNS numeric
+LANGUAGE sql
+AS $$
+    SELECT COALESCE(SUM(i.amount), 0)
+    FROM trekr.incomes i
+    WHERE i.finance_user_id = p_user_id
+      AND date_trunc('month', i.date) = date_trunc('month', current_date);
+$$;
+}}}
+
+Функција за пресметка на распределбата на тековниот месечен приход по 5-те финансиски категории.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_finance_monthly_allocation(p_user_id bigint)
+RETURNS TABLE (
+    spending_amount numeric,
+    saving_amount numeric,
+    investing_amount numeric,
+    donation_amount numeric,
+    credit_amount numeric
+)
+LANGUAGE sql
+AS $$
+    SELECT
+        COALESCE(f.spending_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS spending_amount,
+        COALESCE(f.saving_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS saving_amount,
+        COALESCE(f.investing_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS investing_amount,
+        COALESCE(f.donation_budget, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS donation_amount,
+        COALESCE(f.credit, 0) * COALESCE(v.total_earned_this_month, 0) / 100.0 AS credit_amount
+    FROM trekr.finance_users f
+    LEFT JOIN trekr.vw_finance_current_month v
+        ON v.user_id = f.user_id
+    WHERE f.user_id = p_user_id;
+$$;
+}}}
+
+==== Погледи (Views) ====
+
+Поглед за месечен приход по корисник, по година и месец.
+Овој поглед се користи за историјски извештаи и агрегирани графици.
+{{{
+CREATE OR REPLACE VIEW trekr.vw_finance_monthly_summary AS
+SELECT
+    i.finance_user_id AS 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.finance_user_id, EXTRACT(YEAR FROM i.date), EXTRACT(MONTH FROM i.date);
+}}}
+
+Поглед за вкупно заработени средства во тековниот месец по корисник.
+Овој поглед е основа за пресметка на распределбата на буџетот.
+{{{
+CREATE OR REPLACE VIEW trekr.vw_finance_current_month AS
+SELECT
+    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.finance_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 AS
+SELECT
+    f.user_id,
+    v.total_earned_this_month,
+    (f.spending_budget * v.total_earned_this_month / 100.0) AS spending_amount,
+    (f.saving_budget * v.total_earned_this_month / 100.0) AS saving_amount,
+    (f.investing_budget * v.total_earned_this_month / 100.0) AS investing_amount,
+    (f.donation_budget * v.total_earned_this_month / 100.0) AS donation_amount,
+    (f.credit * v.total_earned_this_month / 100.0) AS credit_amount
+FROM trekr.finance_users f
+LEFT JOIN trekr.vw_finance_current_month v
+    ON v.user_id = f.user_id;
+}}}
+
+----
+
+== Следење на дневна завршеност на дисциплински задачи (Daily Discipline Completion) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Дневната завршеност се пресметува за конкретен корисник и конкретен датум.
+* Ако дневната завршеност за тој корисник и датум веќе постои, не смее да се креира дупликат.
+* По пресметка на дневната завршеност, сите дисциплински задачи на тој корисник мора да се ресетираат на `is_finished = false`.
+* Решението мора да може да се извршува и без апликациски сервер, преку складирана функција што може да се повикува од cron.
+
+=== Имплементација ===
+
+==== Тригери / Functions ====
+
+Функција за пресметка на дневна завршеност за еден корисник и еден ден.
+Функцијата пресметува процент на завршени задачи и ја зачувува дневната завршеност само ако не постои веќе запис за тој датум.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(p_user_id bigint, p_day date)
+RETURNS TABLE(created boolean, daily_completion_id bigint, procent numeric)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    total_count bigint;
+    finished_count bigint;
+    pct numeric;
+    dc_id bigint;
+BEGIN
+    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_completions 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;
+
+    pct := CASE
+        WHEN total_count = 0 THEN 0
+        ELSE ROUND((finished_count::numeric / total_count::numeric) * 100, 2)
+    END;
+
+    INSERT INTO trekr.daily_completions (user_id, date, procent)
+    VALUES (p_user_id, p_day, pct)
+    RETURNING daily_completion_id INTO dc_id;
+
+    UPDATE trekr.tasks
+    SET is_finished = FALSE
+    WHERE discipline_user_id = p_user_id
+      AND is_finished = TRUE;
+
+    RETURN QUERY SELECT true, dc_id, pct;
+END;
+$$;
+}}}
+
+Функција за пресметка на дневна завршеност за сите дисциплински корисници за даден датум.
+Оваа функција е погодна за автоматско стартување преку cron job.
+{{{
+CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(p_day date)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    u RECORD;
+BEGIN
+    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;
+$$;
+}}}
+
+==== Погледи (Views) ====
+
+Поглед за преглед на сите дневни завршености по корисник.
+Ги прикажува историјските проценти, датумите и корисникот на кој припаѓаат.
+{{{
+CREATE OR REPLACE VIEW trekr.vw_daily_completion_history AS
+SELECT
+    dc.daily_completion_id,
+    dc.user_id,
+    dc.date,
+    dc.procent
+FROM trekr.daily_completions dc
+ORDER BY dc.date DESC, dc.daily_completion_id DESC;
+}}}
+
+----
+
+== Интеграција и употреба во апликацијата ==
+
+=== Опис ===
+
+Овие тригери, функции и погледи се користат за:
+* валидација на внесот во финансискиот модул,
+* автоматска пресметка на месечни финансиски суми,
+* пресметка на распределба на буџет според тековниот приход,
+* пресметка и архивирање на дневна дисциплинска завршеност,
+* прикажување на агрегирани извештаи во frontend без дополнителни сложени пресметки.
+
+=== Забелешка ===
+
+SQL објектите се дизајнирани да бидат:
+* идемпотентни каде што е можно,
+* лесни за користење од backend и frontend,
+* погодни за annual / monthly reporting,
+* компатибилни со директно повикување од апликацијата или преку scheduled job.