= Nutritioneer | Напредни извештаи од базата

//[https://dbis-uibk.github.io/relax/calc/local/misc/local/0 Алатка за релациона алгебра]
||= Ознака =||= Операција =||
|| σ || Selection  ||
|| π || Projection  ||
|| ⋈ || Join ||
|| ⟕ || Left outer join ||
|| γ || Аggregation ||
|| τ || Sorting ||
|| − || Difference ||
|| ω || Complete Set ||
|| ← || Temp relation ||
|| SUM(x | у) || Conditional Sum ||




== Извештај 1: Преглед на активноста во заедницата
//Account Activity Log//
=== Опис на барањата за податоци
Извештајот покажува колку е активна секоја сметка во четирите дела на апликацијата.
За секој корисник ги брои креираните рецепти, објавите, коментарите и записите во дневникот, заедно со неговата улога 
од USER кон RECIPE, POST, COMMENT и INTAKE_PLANNER.
=== SQL - приказ
{{{
SELECT u.username,
       u.role,
       COUNT(DISTINCT r.id)  AS recipes_created,
       COUNT(DISTINCT p.id)  AS posts_created,
       COUNT(DISTINCT c.id)  AS comments_written,
       COUNT(DISTINCT ip.id) AS diary_entries
FROM "user" u
LEFT JOIN recipe r          ON r.user_id  = u.id
LEFT JOIN post p            ON p.user_id  = u.id
LEFT JOIN comment c         ON c.user_id  = u.id
LEFT JOIN intake_planner ip ON ip.user_id = u.id
GROUP BY u.id, u.username, u.role
ORDER BY recipes_created DESC, posts_created DESC, u.username;
}}}
=== Релациона алгебра - приказ
{{{
τ_{recipes_created DESC, posts_created DESC, username} (
  γ_{u.id, u.username, u.role;
      COUNT(DISTINCT r.id)  → recipes_created,
      COUNT(DISTINCT p.id)  → posts_created,
      COUNT(DISTINCT c.id)  → comments_written,
      COUNT(DISTINCT ip.id) → diary_entries} (
    User ⟕_{u.id = r.user_id}  Recipe
         ⟕_{u.id = p.user_id}  Post
         ⟕_{u.id = c.user_id}  Comment
         ⟕_{u.id = ip.user_id} Intake_Planner
  )
)
}}}
== Извештај 2: Профил на макронутриенти по порција
//Health Stat Check//
=== Опис на барањата за податоци
Протеини, јаглехидрати, масти и влакна во една порција од секој рецепт.
Спојува две одделни M:N врски во еден нутритивен приказ: рецепт содржи состојки, а состојка содржи нутриенти. За секој рецепт пресметува колку грама од секој макронутриент дава една порција, и ги рангира рецептите по протеини.
* (нутриент на 100 g) × (употребени грамови) / 100 преку сите состојки, поделен со бројот на порции.
=== SQL - приказ
{{{
SELECT r.name                          AS recipe,
       ROUND(r.kcal_sum / r.servings)  AS kcal_per_portion,
       ROUND(SUM(CASE WHEN n.description = 'Protein'
                      THEN icn.quantity * rci.ingredient_quantity / 100
                 END) / r.servings, 1) AS protein_g,
       ROUND(SUM(CASE WHEN n.description = 'Total carbohydrate'
                      THEN icn.quantity * rci.ingredient_quantity / 100
                 END) / r.servings, 1) AS carbs_g,
       ROUND(SUM(CASE WHEN n.description = 'Total fat'
                      THEN icn.quantity * rci.ingredient_quantity / 100
                 END) / r.servings, 1) AS fat_g,
       ROUND(SUM(CASE WHEN n.description = 'Dietary fibre'
                      THEN icn.quantity * rci.ingredient_quantity / 100
                 END) / r.servings, 1) AS fibre_g
FROM recipe r
JOIN recipe_contains_ingredient   rci ON rci.recipe_id     = r.id
JOIN ingredient_contains_nutrient icn ON icn.ingredient_id = rci.ingredient_id
JOIN nutrient n                       ON n.id              = icn.nutrient_id
WHERE n.type = 'macro'
GROUP BY r.id, r.name, r.kcal_sum, r.servings
ORDER BY protein_g DESC;
}}}
=== Релациона алгебра - приказ
{{{
amount ≡ icn.quantity · rci.ingredient_quantity / 100

τ_{protein_g DESC} (
  γ_{r.id, r.name, r.kcal_sum, r.servings;
      SUM(amount | n.description = 'Protein')            / r.servings → protein_g,
      SUM(amount | n.description = 'Total carbohydrate') / r.servings → carbs_g,
      SUM(amount | n.description = 'Total fat')          / r.servings → fat_g,
      SUM(amount | n.description = 'Dietary fibre')      / r.servings → fibre_g} (
    σ_{n.type = 'macro'} (
      Recipe
        ⋈_{r.id = rci.recipe_id}              Recipe_Contains_Ingredient
        ⋈_{rci.ingredient_id = icn.ingredient_id} Ingredient_Contains_Nutrient
        ⋈_{icn.nutrient_id = n.id}            Nutrient
    )
  )
)
}}}
== Извештај 3: Ден со најголем внес по корисник (АЖУРИРАНО)
// Callorie-maxing view //
=== Опис на барањата за податоци
Фокус на денот со најмногу калории на секој корисник наспроти неговиот сопствен просек. секој корисник што води дневник на исхрана, извештајот го наоѓа денот во кој внел најмногу енергија. Тој ден го споредува со просечниот дневен внес на истиот корисник.
* RANK() го избира најтешкиот ден
* AVG() врз партицијата личниот просек
* LAG() претходниот ден
* втор AVG() со ROWS BETWEEN 2 PRECEDING AND CURRENT ROW тридневен подвижен просек.

1. weight_near_peak: тежината чиј датум е еднаков на датумот избран од внатрешен потпрашалник што ги подредува мерењата по временска оддалеченост од врвниот ден.
2. avg_peak_all_users и users_with_higher_peak: агрегат врз изведена табела од максимуми по корисник, која самата е агрегат врз изведена табела од дневни збирови.
=== SQL - приказ
{{{
WITH daily AS (
    SELECT ip.user_id,
           ip.date_time::date AS intake_day,
           SUM(ip.kcal)       AS kcal_consumed,
           COUNT(*)           AS meals_logged
    FROM intake_planner ip
    WHERE ip.is_consumed = TRUE
    GROUP BY ip.user_id, ip.date_time::date
),
ranked AS (
    SELECT d.*,
           RANK() OVER (PARTITION BY d.user_id
                        ORDER BY d.kcal_consumed DESC)      AS day_rank,
           ROUND(AVG(d.kcal_consumed)
                 OVER (PARTITION BY d.user_id))             AS avg_daily_kcal,
           LAG(d.kcal_consumed) OVER (PARTITION BY d.user_id
                                      ORDER BY d.intake_day) AS prev_day_kcal,
           ROUND(AVG(d.kcal_consumed) OVER (PARTITION BY d.user_id
                        ORDER BY d.intake_day
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1)
                                                            AS moving_avg_3d
    FROM daily d
)
SELECT (SELECT u.username FROM "user" u WHERE u.id = r.user_id) AS username,
       r.intake_day,
       r.kcal_consumed,
       r.meals_logged,
       r.avg_daily_kcal,
       ROUND(r.kcal_consumed - r.prev_day_kcal, 2) AS change_vs_prev_day,
       r.moving_avg_3d,
       -- level 2: the weight measured closest in time to the peak day
       (SELECT bm.weight
        FROM biometrics bm
        WHERE bm.user_id = r.user_id
          AND bm.date = (SELECT bm2.date
                         FROM biometrics bm2
                         WHERE bm2.user_id = r.user_id
                         ORDER BY abs(bm2.date - r.intake_day)
                         LIMIT 1)) AS weight_near_peak,
       -- level 3: the average peak day across all users, for context
       (SELECT ROUND(AVG(peaks.peak))
        FROM (SELECT MAX(inner_daily.k) AS peak
              FROM (SELECT ip2.user_id AS uid,
                           ip2.date_time::date AS d,
                           SUM(ip2.kcal) AS k
                    FROM intake_planner ip2
                    WHERE ip2.is_consumed = TRUE
                    GROUP BY ip2.user_id, ip2.date_time::date) inner_daily
              GROUP BY inner_daily.uid) peaks) AS avg_peak_all_users,
       -- level 3: how many users had a higher peak than this one
       (SELECT COUNT(*)
        FROM (SELECT MAX(x.k) AS peak
              FROM (SELECT ip3.user_id AS uid,
                           ip3.date_time::date AS d,
                           SUM(ip3.kcal) AS k
                    FROM intake_planner ip3
                    WHERE ip3.is_consumed = TRUE
                    GROUP BY ip3.user_id, ip3.date_time::date) x
              GROUP BY x.uid) others
        WHERE others.peak > r.kcal_consumed) AS users_with_higher_peak,
       CASE WHEN r.kcal_consumed > 1.25 * r.avg_daily_kcal THEN 'spike day'
            WHEN r.kcal_consumed < 0.75 * r.avg_daily_kcal THEN 'light day'
            ELSE 'typical day' END AS day_profile
FROM ranked r
WHERE r.day_rank = 1
ORDER BY r.kcal_consumed DESC;

}}}
=== Релациона алгебра - приказ (АЖУРИРАНО)
{{{
Daily    ← γ_{ip.user_id, date(ip.date_time) → intake_day;
             SUM(ip.kcal) → kcal_consumed, COUNT(*) → meals_logged} (
             σ_{ip.is_consumed = TRUE} (Intake_Planner))
 
Ranked   ← ω_{PARTITION BY user_id;
             RANK() ORDER BY kcal_consumed DESC     → day_rank,
             AVG(kcal_consumed)                     → avg_daily_kcal,
             LAG(kcal_consumed) ORDER BY intake_day → prev_day_kcal,
             AVG(kcal_consumed) ORDER BY intake_day
                 ROWS [-2, 0]                       → moving_avg_3d} (Daily)
 
Peaks    ← γ_{user_id; MAX(kcal_consumed) → peak} (Daily)
AvgPeak  ← γ_{; AVG(peak) → avg_peak_all_users} (Peaks)
Higher(x) ← γ_{; COUNT(*) → users_with_higher_peak} (σ_{peak > x} (Peaks))
 
Nearest(row) ← π_{weight} (
               σ_{bm.user_id = row.user_id ∧
                  bm.date ∈ π_{date}( τ_{|bm2.date − row.intake_day|}
                                      (σ_{bm2.user_id = row.user_id}(Biometrics)) [1] )}
               (Biometrics))
 
τ_{kcal_consumed DESC} (
  π_{username, intake_day, kcal_consumed, meals_logged, avg_daily_kcal,
     kcal_consumed − prev_day_kcal → change_vs_prev_day, moving_avg_3d,
     weight_near_peak, avg_peak_all_users, users_with_higher_peak, day_profile} (
    σ_{day_rank = 1} (Ranked) ⋈_{user_id = u.id} User
      ⟕ Nearest × AvgPeak × Higher(kcal_consumed)
  )
)

}}}
== Извештај 4: Целосно растителни рецепти
//Go Green Filter//
=== Опис на барањата за податоци
Рецепти чија секоја состојка е од растителна категорија, проверени наспроти веганската ознака. пронаоѓа рецептите во кои секоја состојка е од растително потекло. Тоа е релациско делење: рецептот се квалификува само ако бројот на неговите растителни состојки е еднаков на бројот на сите негови состојки. Една состојка од животинско потекло го дисквалификува целиот рецепт. Растителни се типовите:
* vegetable
* fruit
* legume
* nut
* seed
* oil
* herb
* spice
* carb
* beverage 
* //Медот и јајцето// се од типот **other** -> дисквалификуваат
=== SQL - приказ
{{{
SELECT r.name                          AS recipe,
       u.username                      AS author,
       COUNT(*)                        AS ingredient_count,
       ROUND(r.kcal_sum / r.servings)  AS kcal_per_portion,
       EXISTS (SELECT 1
               FROM recipe_contains_restriction rcr
               JOIN restriction res ON res.id = rcr.restriction_id
               WHERE rcr.recipe_id = r.id
                 AND res.type = 'vegan')  AS tagged_vegan
FROM recipe r
JOIN "user" u                        ON u.id = r.user_id
JOIN recipe_contains_ingredient rci  ON rci.recipe_id = r.id
JOIN ingredient i                    ON i.id = rci.ingredient_id
WHERE i.type IN ('vegetable', 'fruit', 'legume', 'nut', 'seed',
                 'oil', 'herb', 'spice', 'carb', 'beverage')
GROUP BY r.id, r.name, u.username, r.kcal_sum, r.servings
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM recipe_contains_ingredient rci2
                   WHERE rci2.recipe_id = r.id)
ORDER BY kcal_per_portion;
}}}
=== Релациона алгебра - приказ
{{{
Plant ← {'vegetable', 'fruit', 'legume', 'nut', 'seed',
          'oil', 'herb', 'spice', 'carb', 'beverage'}

AnimalUse ← π_{rci.recipe_id} (
    σ_{i.type ∉ Plant} (
        Recipe_Contains_Ingredient ⋈_{rci.ingredient_id = i.id} Ingredient
    )
)

PlantOnly ← π_{rci.recipe_id} (Recipe_Contains_Ingredient) − AnimalUse

τ_{kcal_per_portion} (
  γ_{r.id, r.name, u.username; COUNT(*) → ingredient_count} (
    (PlantOnly ⋈_{recipe_id = r.id} Recipe)
      ⋈_{r.user_id = u.id} User
      ⋈_{r.id = rci.recipe_id} Recipe_Contains_Ingredient
  )
)
}}}

=== AI алатки во процесот
Искрористен беше агентот „deepseek.ai“ за тестирање на функциите на релационата алгебра во локален sandbox и зафаќање на edge-cases и оптимизирање на извештаите.

== Навигација низ проектот
[https://develop.finki.ukim.mk/projects/nutritioneer/wiki/WikiStart Почетна страна]
||= Фаза =||= Име на фаза =||= Статус =||
|| P0 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/About Дефинирање проект] || Одобрен ||
|| P1 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/ERModel Концептуален дизајн и ЕР Дијаграм] || Одобрен ||
|| P2 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/RelationalDesign Логички и физички дизајн - DDL] || Одобрен ||
|| P3 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/UseCaseModel Кориснички/апикациски сценарија со базата на податоци] || Одобрен ||
|| P4 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/PrototypeApplication Протип со основни функционалности] || WIP ||
|| М1 || Презентација на прототип || WIP ||
|| P5 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/Normalization Нормализација и оптимизација на дизајн] || WIP ||
|| P6 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/AdvancedReports Напредни извештаи од базата]  || WIP ||
|| P7 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/AdvancedDatabaseDevelopment Понапреден развој на базата] || WIP ||
|| P8 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/AdvancedApplicationDevelopment Напреден апликативен развој] || WIP ||
|| P9 || [https://develop.finki.ukim.mk/projects/nutritioneer/wiki/OtherDevelopment Дополнителни имплементации] || WIP ||
|| М2 || Финализиран проект || WIP ||

=== ^([#hn Trac навигација])

 * TracGuide Documentation