-- brands.db: brand-name foods sold in Canada and the US, as GymFork ships them inside the app.
-- Built by build_brands.py from USDA FoodData Central Branded Foods (CC0 1.0) and Open Food Facts
-- (ODbL 1.0). The whole file is under the Open Database License: see the `meta` table.
--
-- Read by app/src/features/foods/brandsDb.ts and by the Jest fixture in brandsDbFixture.ts; this
-- file is the one copy of the shape, so change it here and both follow.
--
-- Numbers are per 100 g (ml counted as g) and stored as whole tenths to keep the file small:
-- kcal_x10 = 523 means 52.3 kcal. The view `brand_foods_per_100g` shows them as plain numbers.

CREATE TABLE brand_foods (
  -- Rank: 1 is the most scanned in Canada and the US. Search reads matches in this order.
  id INTEGER PRIMARY KEY,
  -- The product's main barcode as a number: a 13-digit code as is (leading zeros dropped), an
  -- EAN-8 as 10^13 plus its number, so the two never clash. Every barcode is in brand_barcodes.
  code INTEGER NOT NULL,
  name TEXT NOT NULL,
  brand TEXT,
  kcal_x10 INTEGER NOT NULL,
  protein_x10 INTEGER NOT NULL,
  carbs_x10 INTEGER NOT NULL,
  fat_x10 INTEGER NOT NULL,
  fiber_x10 INTEGER,
  sugar_x10 INTEGER,
  sat_fat_x10 INTEGER,
  sodium_mg_x10 INTEGER,
  serving_g_x10 INTEGER,
  serving_label TEXT,
  -- Where the row came from. 0: USDA FoodData Central only. 1: the numbers came from Open Food
  -- Facts. 2: USDA's numbers, with details USDA left empty (serving, fibre, sugar, sat fat, sodium
  -- or brand) filled in from Open Food Facts, so both are credited.
  from_off INTEGER NOT NULL
);

-- Every barcode (same number form as brand_foods.code) -> the food it scans to. Pack sizes of one
-- product share a food.
CREATE TABLE brand_barcodes (
  code INTEGER PRIMARY KEY,
  food_id INTEGER NOT NULL
) WITHOUT ROWID;

-- Licence, credit, build date and inputs.
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL) WITHOUT ROWID;

-- Word index over brand + name (rowid = brand_foods.id). Contentless: it answers "which foods
-- have these words" and keeps no copy of the text. The words are the app's own (build_brands.py
-- index_text = brandsDb.ts brandIndexText, from searchWords.ts indexWords). prefix='1 2' keeps a
-- ready list for every 1- and 2-letter word start, so a search with a short word ("b fit",
-- "oikos p", "c h") reads one list instead of merging thousands: worst case 6.5 ms instead of
-- 59 ms on a Mac, for 2.4 MB more download (measured 2026-10-04 at 450,000 foods).
CREATE VIRTUAL TABLE brand_fts USING fts5(search, content='', detail=none, columnsize=0, prefix='1 2');

CREATE VIEW brand_foods_per_100g AS
SELECT
  id,
  CASE WHEN code >= 10000000000000 THEN printf('%08d', code - 10000000000000) ELSE printf('%013d', code) END AS barcode,
  name,
  brand,
  kcal_x10 / 10.0 AS kcal,
  protein_x10 / 10.0 AS protein_g,
  carbs_x10 / 10.0 AS carbs_g,
  fat_x10 / 10.0 AS fat_g,
  fiber_x10 / 10.0 AS fiber_g,
  sugar_x10 / 10.0 AS sugar_g,
  sat_fat_x10 / 10.0 AS sat_fat_g,
  sodium_mg_x10 / 10.0 AS sodium_mg,
  serving_g_x10 / 10.0 AS serving_g,
  serving_label,
  CASE from_off WHEN 1 THEN 'Open Food Facts' WHEN 2 THEN 'USDA FoodData Central + Open Food Facts' ELSE 'USDA FoodData Central' END AS source
FROM brand_foods;