Напреден развој на базата

Валидација на финансиски проценти (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;
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_trigger t
        JOIN pg_class c ON t.tgrelid = c.oid
        WHERE t.tgname = 'trg_validate_finance_percentages' AND c.relname = 'finance_users'
    ) THEN
        CREATE TRIGGER trg_validate_finance_percentages
        BEFORE INSERT OR UPDATE ON trekr.finance_users
        FOR EACH ROW
        EXECUTE FUNCTION trekr.fn_validate_finance_percentages();
    END IF;
END$$;

---

Пресметување на дневни завршувања (Daily Completion Computation)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

• Дневното завршување може да се пресмета само за корисник со овозможено следење (discipline_users)
• Не смее да се пресметува за иден датум
• Ако веќе постои запис за тој корисник и датум, се враќа постоечкиот резултат без дупликат
• По пресметувањето, завршените задачи се врзуваат за дневниот запис, а потоа нивниот статус се ресетира

Имплементација

Функции / Stored Procedures

Функција за пресметување на дневно завршување за еден корисник и датум. Вметнува ред во daily_completions (доколку не постои), ги поврзува завршените задачи и го ресетира нивниот статус.

CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion(user_id bigint, 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;
    finished_tasks RECORD;
BEGIN
    IF user_id IS NULL THEN
        RAISE EXCEPTION 'user_id is required';
    END IF;
    IF day IS NULL THEN
        RAISE EXCEPTION 'day is required';
    END IF;
    IF 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 = 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 = user_id AND dc.date = 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 = (
        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
    );

    SELECT COUNT(*) INTO finished_count FROM trekr.tasks t WHERE t.discipline_user_id = (
        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
    ) AND t.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_completions (user_id, date, procent)
    VALUES (user_id, day, pct)
    RETURNING daily_completion_id INTO dc_id;

    FOR finished_tasks IN
        SELECT t.task_id FROM trekr.tasks t
        WHERE t.discipline_user_id = (
            SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
        ) AND t.finished = true
    LOOP
        INSERT INTO trekr.task_daily_completions (task_id, daily_completion_id)
        VALUES (finished_tasks.task_id, dc_id)
        ON CONFLICT DO NOTHING;
    END LOOP;

    UPDATE trekr.tasks t SET finished = false
    WHERE t.discipline_user_id = (
        SELECT du.discipline_user_id FROM trekr.discipline_users du WHERE du.user_id = user_id
    );

    RETURN QUERY SELECT true, dc_id, pct;
END;
$$;

Функција за пресметување на дневни завршувања за сите корисници со овозможено следење за даден датум. Грешките по корисник се логираат и се продолжува понатаму.

CREATE OR REPLACE FUNCTION trekr.fn_compute_daily_completion_for_all(day date)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    u RECORD;
BEGIN
    IF 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, 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 за осигурување дека еден корисник може да има најмногу еден внес по датум.

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 (weight_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;
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_trigger t
        JOIN pg_class c ON t.tgrelid = c.oid
        WHERE t.tgname = 'trg_check_training_date' AND c.relname = 'training_sessions'
    ) THEN
        CREATE TRIGGER trg_check_training_date
        BEFORE INSERT OR UPDATE ON trekr.training_sessions
        FOR EACH ROW
        EXECUTE FUNCTION trekr.fn_check_training_date();
    END IF;
END$$;

---

Прегледи за финансии (Finance Views)

Опис на барањата за податочни ограничувања

Системот мора да обезбеди дека:

• Постои преглед за месечен приход по корисник и период
• Постои преглед за вкупниот приход на корисникот во тековниот месец
• Постои преглед за пресметани апсолутни износи по категорија врз основа на процентите и приходот во тековниот месец

Имплементација

Погледи (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_current_month AS
SELECT
    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;