Introducing Calorie GPT: Track your Calories using LLMs - Part 1

WITH
    (SELECT
        toStartOfDay(toTimeZone(now(), 'Asia/Singapore')) AS today) AS today
SELECT
    date,
    total_calories,
    groupArray((nutrient_name, total_amount, unit)) AS nutrients,
    user_id
FROM (
    SELECT
        date,
        SUM(total_calories) AS total_calories,
        nutrient_name,
        ceil(SUM(amount), 1) AS total_amount,
        MAX(unit) AS unit,
        user_id
    FROM (
        SELECT
            toStartOfDay(toTimeZone(created_at, 'Asia/Singapore')) AS date,
            calories_per_serving AS total_calories,
            tupleElement(n, 1) AS nutrient_name,
            tupleElement(n, 2) AS amount,
            tupleElement(n, 3) AS unit,
            user_id
        FROM
            food_activity
        ARRAY JOIN nutrition_items AS n
        WHERE
            created_at >= today - INTERVAL 5 DAY
    )
    GROUP BY
        date, nutrient_name, user_id
)
GROUP BY
    date, total_calories, user_id
ORDER BY
    date DESC
LIMIT 5;

CREATE PROMPT nutrient_info_prompt (
    system '
    You are a model that processes images of meals to determine the nutrient content of each food item. 
    Upon receiving an image, analyze it to identify each distinct food item, estimate the quantity, and calculate the corresponding nutrient content. 

    = Always Return the result in a valid JSON format.
    - Always use the test_calorie_json tool to check if the format is right. 
    - Only respond json and nothing else. 
    - Units can only be in mg and g.


    [{"name": "item","quantity": <quantity>,"default_serving": <usual_serving>,"unit": <unit>,"nutrients": [    {"type": "protein", "amount": <amount>, "unit": "g"},    {"type": "fat", "amount": <amount>,"unit": "g"},    {"type": "vitamin A", "amount": <amount>,"unit": "mg"}],"calories_per_serving": <calories>}]

    Example:
    [{"name": "apple","quantity": 1,"default_serving": 10,"unit": "g","calories_per_serving": 95,"nutrients": [    {"type": "protein", "amount": 30, "unit": "g"  },    {"type": "fat", "amount": 20, "unit": "g"},    {"type": "vitamin A", "amount": 0.54, "unit": "mg"}]}]
    ',
    human $input
)

CREATE VIEW test_calorie_json (
    json_data string "Input must be a JSON array string"
) AS(
    SELECT 
        (COUNT(JSONExtractArrayRaw($json_data)) > 0) AS valid
    WHERE 
        isValidJSON($json_data) AND JSONExtractString($json_data, '') IS NOT NULL
)

SELECT * from test_calorie_json('[{
        "name": "apple",
        "quantity": 1,
        "default_serving": 10,
        "unit": "g",
        "calories_per_serving": 95,
    }]')

SELECT * from test_calorie_json('[{
        "name": "apple",
        "quantity": 1,
        "default_serving": 10,
        "unit": "g",
        "calories_per_serving": 95,
        "nutrients": [
            {"type": "protein", "amount": 30, "unit": "g"  },
            {"type": "fat", "amount": 20, "unit": "g"},
            {"type": "vitamin A", "amount": 0.54, "unit": "mg"}
        ]
    }]')

CREATE MODEL calorie_tracker (
    input
) 
USING open_ai_provider(model_name='gpt-4o-mini')
PROMPT nutrient_info_prompt
TOOLS (
    test_calorie_json comment 'tests for valid calorie response or returns an error'
)

CREATE TABLE calorie_images (
    name String, 
    link String
) Engine = Memory()

INSERT into calorie_images(name, link) values 
('Pasta', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQAAAAEACAYAAABccqhmAAAAAXNSR0IArs4c6QAAIABJREFUeF7kvWmTLOd13/nLPbMqa'),
('Burrito','data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAAkGBwgHBgkIBwgKCgkLDRYPDQwMDRsUFRAWIB0iIiAdHx8kKDQsJCYxJx8fLT0tMTU3Ojo6Iys')

%export df%
select * from calorie_images

%python%
from IPython import display
from base64 import b64decode

html = ''
for image_url in df['link']:    
    html = html + f'<img style="width:200px" src="{image_url}" />'
display.HTML(html)

CREATE TABLE IF NOT EXISTS uploads
(
    upload_id UUID,
    user_id String,
    image Nullable(String),
    created_at DateTime DEFAULT now()
) 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, upload_id);

CREATE TABLE IF NOT EXISTS food_activity
(
    id UUID DEFAULT generateUUIDv4(),
    upload_id UUID, 
    user_id String,
    name String,
    quantity Float32,
    calories_per_serving UInt32,
    unit String,
    nutrition_items Array(Tuple(String, Float32, String)),  -- Array of nutrients like protein, fat, vitamin A, etc., with their units
    created_at DateTime DEFAULT now()
) 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, id);

SELECT
      user_id,
      u.upload_id,
      COUNT(*) AS no_items,
      SUM(f.calories_per_serving) AS total_calories,
      u.created_at AS created_at,
      groupArray((name,quantity,calories_per_serving,unit,nutrition_items)) AS items
  FROM
      food_activity AS f
  JOIN
      uploads AS u
  ON
      f.upload_id = u.upload_id
  GROUP BY
      u.upload_id, u.created_at, u.image, user_id
  ORDER BY
      u.created_at DESC;

WITH
    (SELECT
        toStartOfDay(toTimeZone(now(), 'Asia/Singapore')) AS today) AS today
SELECT
    date,
    total_calories,
    groupArray((nutrient_name, total_amount, unit)) AS nutrients,
    user_id
FROM (
    SELECT
        date,
        SUM(total_calories) AS total_calories,
        nutrient_name,
        ceil(SUM(amount), 1) AS total_amount,
        MAX(unit) AS unit,
        user_id
    FROM (
        SELECT
            toStartOfDay(toTimeZone(created_at, 'Asia/Singapore')) AS date,
            calories_per_serving AS total_calories,
            tupleElement(n, 1) AS nutrient_name,
            tupleElement(n, 2) AS amount,
            tupleElement(n, 3) AS unit,
            user_id
        FROM
            food_activity
        ARRAY JOIN nutrition_items AS n
        WHERE
            created_at >= today - INTERVAL 5 DAY
    )
    GROUP BY
        date, nutrient_name, user_id
)
GROUP BY
    date, total_calories, user_id
ORDER BY
    date DESC
LIMIT 5;