4aiman
9251c1dd90
Separate BleBaroService so the HR strap and the barometer each hold their own GATT link and work at the same time (one adapter, several LE connections). Verified on device against a PC BlueZ emulator of the sensor: BleHR and BleBaro deliver in parallel, no cross-talk. - BleBaroService: dedicated baro connection (scan-before-connect + direct-connect fallback, ESS subscribe, writes ble_baro_data.json). MainActivity routes baro connects to it and auto-connects both HR (ble_device.json) and baro (baro_device.json) at start. - Rust: ble_baro_disconnect, ble_save_baro_device. - Settings: a 'Датчики (BLE)' section - heart-rate and barometer as separate rows with their own slots, bind/connect/unbind, and a live pressure/temperature/altitude readout. - Tracking: altitude_baro_m stored per point, preferred in elevation stats (null without baro). - Record screen: 'Высота' from the barometer (1 Hz, dot marker), temperature from the sensor. - Fixes: direct-connect on scan timeout; Locale.US JSON (RU decimal comma broke the parse); parsing routed by characteristic (baro no longer written into the pulse); VR/HR stop returns to the tracks list instead of into details. - tools/baro-sensor/emulate_baro.py: PC BlueZ peripheral that emulates the sensor for testing. Emulator-verified only; real board still pending.
2130 lines
95 KiB
JavaScript
2130 lines
95 KiB
JavaScript
// Database layer — talks to tauri-plugin-sql via raw Tauri IPC.
|
||
// No imports, no npm. window.__TAURI__ is injected by Tauri (withGlobalTauri: true).
|
||
// The DB is preloaded at startup via tauri.conf.json plugins.sql.preload.
|
||
|
||
const DB_PATH = "sqlite:baldav.db";
|
||
|
||
function inv(cmd, args) {
|
||
return window.__TAURI__.core.invoke(`plugin:sql|${cmd}`, args);
|
||
}
|
||
|
||
async function execute(query, params) {
|
||
return inv("execute", { db: DB_PATH, query, values: params ?? [] });
|
||
}
|
||
|
||
async function select(query, params) {
|
||
return inv("select", { db: DB_PATH, query, values: params ?? [] });
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Profile (key-value store)
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getProfileValue(key) {
|
||
const rows = await select("SELECT value FROM profile WHERE key = ?1", [key]);
|
||
return rows.length ? rows[0].value : null;
|
||
}
|
||
|
||
async function setProfileValue(key, value) {
|
||
await execute(
|
||
"INSERT OR REPLACE INTO profile (key, value) VALUES (?1, ?2)",
|
||
[key, String(value)]
|
||
);
|
||
}
|
||
|
||
async function getFullProfile() {
|
||
const rows = await select("SELECT key, value FROM profile");
|
||
const p = {};
|
||
for (const r of rows) p[r.key] = r.value;
|
||
return p;
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Weight log
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function addWeight(date, time, kg) {
|
||
await execute(
|
||
"INSERT INTO weight_log (date, time, weight_kg) VALUES (?1, ?2, ?3)",
|
||
[date, time, kg]
|
||
);
|
||
// Keep the settings weight in sync with the latest weigh-in (today's if there is one).
|
||
const latest = await getLatestWeight();
|
||
if (latest && latest.weight_kg != null) await setProfileValue("weight_kg", latest.weight_kg);
|
||
}
|
||
|
||
async function deleteWeight(id) {
|
||
await execute("DELETE FROM weight_log WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function getWeightsForDate(date) {
|
||
return select("SELECT * FROM weight_log WHERE date = ?1 ORDER BY time ASC", [date]);
|
||
}
|
||
|
||
async function getLatestWeight() {
|
||
const rows = await select(
|
||
"SELECT * FROM weight_log ORDER BY date DESC, time DESC LIMIT 1"
|
||
);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
// `days` calendar days ENDING at endDate (default today). Range is (end-days, end] so it is
|
||
// exactly `days` days (the old `>= date('now','-N')` gave N+1) and follows the selected day.
|
||
async function getRecentWeights(days, endDate) {
|
||
const end = endDate || "now";
|
||
return select(
|
||
`SELECT date, MIN(weight_kg) as weight_kg
|
||
FROM weight_log
|
||
WHERE date > date(?2, ?1) AND date <= date(?2)
|
||
GROUP BY date ORDER BY date ASC`,
|
||
[`-${days ?? 30} days`, end]
|
||
);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Sleep log
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getSleepForDate(date) {
|
||
const rows = await select("SELECT * FROM sleep_log WHERE date = ?1", [date]);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function saveSleep(date, bedTime, asleepTime, wakeTime, napMinutes, notes) {
|
||
const existing = await getSleepForDate(date);
|
||
if (existing) {
|
||
await execute(
|
||
`UPDATE sleep_log SET bed_time=?1, asleep_time=?2, wake_time=?3,
|
||
nap_minutes=?4, notes=?5 WHERE date=?6`,
|
||
[bedTime, asleepTime, wakeTime, napMinutes ?? 0, notes ?? null, date]
|
||
);
|
||
} else {
|
||
await execute(
|
||
`INSERT INTO sleep_log (date, bed_time, asleep_time, wake_time, nap_minutes, notes)
|
||
VALUES (?1, ?2, ?3, ?4, ?5, ?6)`,
|
||
[date, bedTime, asleepTime, wakeTime, napMinutes ?? 0, notes ?? null]
|
||
);
|
||
}
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Products
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function searchProducts(query, limit) {
|
||
const q = (query || "").toLowerCase().trim();
|
||
// Order-independent: every whitespace-separated word must appear in the name
|
||
// (in any order). Fixes "надо вводить слова в порядке названия".
|
||
const tokens = q.split(/\s+/).filter(Boolean);
|
||
if (tokens.length === 0) return [];
|
||
const cols = `id, name, brand, category, weight_g, kcal_per_100g,
|
||
protein_per_100g, fat_per_100g, carbs_per_100g, fiber_per_100g,
|
||
composition, nutrients_guessed, source, water_pct,
|
||
vit_a_mcg, vit_c_mg, vit_b1_mg, vit_b2_mg, vit_b3_mg, vit_e_mg,
|
||
calcium_mg, iron_mg, magnesium_mg, potassium_mg, sodium_mg, phosphorus_mg,
|
||
carotene_mcg, selenium_mcg, zinc_mg, copper_mg, folate_mcg,
|
||
vit_d_mcg, vit_k_mcg, vit_b6_mg, vit_b12_mcg`;
|
||
const where = tokens.map((_, i) => `name_lower LIKE ?${i + 1}`).join(" AND ");
|
||
const vals = tokens.map(t => `%${t}%`);
|
||
const nFull = vals.length + 1, nPrefix = vals.length + 2, nLimit = vals.length + 3;
|
||
vals.push(`%${q}%`); // full contiguous string present → rank first
|
||
vals.push(`${q}%`); // name starts with the query → rank next
|
||
vals.push(limit ?? 200);
|
||
return select(
|
||
`SELECT ${cols}
|
||
FROM products
|
||
WHERE ${where}
|
||
GROUP BY COALESCE(store_id, id)
|
||
ORDER BY
|
||
CASE WHEN name_lower LIKE ?${nFull} THEN 0 ELSE 1 END,
|
||
CASE WHEN name_lower LIKE ?${nPrefix} THEN 0 ELSE 1 END,
|
||
length(name),
|
||
name COLLATE NOCASE
|
||
LIMIT ?${nLimit}`,
|
||
vals
|
||
);
|
||
}
|
||
|
||
async function getProduct(id) {
|
||
const rows = await select("SELECT * FROM products WHERE id = ?1", [id]);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function addProduct(p) {
|
||
await execute(
|
||
`INSERT INTO products
|
||
(name, name_lower, brand, category, weight_g, kcal_per_100g, protein_per_100g,
|
||
fat_per_100g, carbs_per_100g, fiber_per_100g,
|
||
vit_a_mcg, vit_c_mg, vit_d_mcg, vit_e_mg, vit_k_mcg,
|
||
vit_b1_mg, vit_b2_mg, vit_b3_mg, vit_b6_mg, vit_b12_mcg, folate_mcg,
|
||
calcium_mg, iron_mg, magnesium_mg, zinc_mg, copper_mg,
|
||
selenium_mcg, potassium_mg, sodium_mg, phosphorus_mg, carotene_mcg, source, water_pct)
|
||
VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16,?17,?18,?19,?20,?21,?22,?23,?24,?25,?26,?27,?28,?29,?30,?31,?32,?33)`,
|
||
[
|
||
p.name, p.name?.toLowerCase() ?? null,
|
||
p.brand ?? null, p.category ?? null, p.weight_g ?? null,
|
||
p.kcal_per_100g ?? null, p.protein_per_100g ?? null,
|
||
p.fat_per_100g ?? null, p.carbs_per_100g ?? null, p.fiber_per_100g ?? null,
|
||
p.vit_a_mcg ?? null, p.vit_c_mg ?? null, p.vit_d_mcg ?? null,
|
||
p.vit_e_mg ?? null, p.vit_k_mcg ?? null,
|
||
p.vit_b1_mg ?? null, p.vit_b2_mg ?? null, p.vit_b3_mg ?? null,
|
||
p.vit_b6_mg ?? null, p.vit_b12_mcg ?? null, p.folate_mcg ?? null,
|
||
p.calcium_mg ?? null, p.iron_mg ?? null, p.magnesium_mg ?? null,
|
||
p.zinc_mg ?? null, p.copper_mg ?? null,
|
||
p.selenium_mcg ?? null, p.potassium_mg ?? null, p.sodium_mg ?? null,
|
||
p.phosphorus_mg ?? null, p.carotene_mcg ?? null,
|
||
p.source ?? "manual", p.water_pct ?? null,
|
||
]
|
||
);
|
||
const rows = await select("SELECT id FROM products ORDER BY id DESC LIMIT 1");
|
||
return rows[0]?.id;
|
||
}
|
||
|
||
async function getProductRefs(id) {
|
||
const food = await select("SELECT COUNT(*) as cnt FROM food_log WHERE product_id = ?1", [id]);
|
||
const recipes = await select("SELECT COUNT(*) as cnt FROM recipe_ingredients WHERE product_id = ?1", [id]);
|
||
return { food: food[0]?.cnt || 0, recipes: recipes[0]?.cnt || 0 };
|
||
}
|
||
|
||
async function deleteProduct(id) {
|
||
await execute("DELETE FROM products WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// Catalog cleanup: sources that carry a "плашка" (badge) and are worth keeping even
|
||
// if never eaten. Everything else (health-diet / ashan / fast food / null source) is
|
||
// removed ONLY when unused — i.e. not referenced by food_log or recipe_ingredients,
|
||
// so eaten products and recipe ingredients always survive.
|
||
const PRODUCT_KEEP_SOURCES = "'magnit','5ka','ion','kdv','manual','recipe_import','gridnev'";
|
||
const UNLABELED_UNUSED_WHERE =
|
||
`(source IS NULL OR source NOT IN (${PRODUCT_KEEP_SOURCES}))
|
||
AND id NOT IN (SELECT product_id FROM food_log WHERE product_id IS NOT NULL)
|
||
AND id NOT IN (SELECT product_id FROM recipe_ingredients WHERE product_id > 0)`;
|
||
|
||
async function countUnlabeledUnusedProducts() {
|
||
const rows = await select(`SELECT COUNT(*) AS cnt FROM products WHERE ${UNLABELED_UNUSED_WHERE}`);
|
||
return rows[0]?.cnt || 0;
|
||
}
|
||
|
||
async function cleanupUnlabeledProducts() {
|
||
const n = await countUnlabeledUnusedProducts();
|
||
await execute(`DELETE FROM products WHERE ${UNLABELED_UNUSED_WHERE}`);
|
||
return n;
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Cooking methods & Recipes
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getCookingMethods() {
|
||
return select("SELECT * FROM cooking_methods ORDER BY id");
|
||
}
|
||
|
||
async function getCookingMethod(id) {
|
||
const rows = await select("SELECT * FROM cooking_methods WHERE id = ?1", [id]);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function getRecipes() {
|
||
return select(
|
||
`SELECT r.*, cm.name as cooking_method_name
|
||
FROM recipes r
|
||
LEFT JOIN cooking_methods cm ON r.cooking_method_id = cm.id
|
||
ORDER BY r.created_at DESC`
|
||
);
|
||
}
|
||
|
||
async function getRecipe(id) {
|
||
const rows = await select(
|
||
`SELECT r.*, cm.name as cooking_method_name
|
||
FROM recipes r
|
||
LEFT JOIN cooking_methods cm ON r.cooking_method_id = cm.id
|
||
WHERE r.id = ?1`,
|
||
[id]
|
||
);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function getRecipeIngredients(recipeId) {
|
||
return select(
|
||
`SELECT ri.*, p.name as product_name, p.kcal_per_100g,
|
||
p.protein_per_100g, p.fat_per_100g, p.carbs_per_100g,
|
||
p.fiber_per_100g,
|
||
p.vit_a_mcg, p.vit_c_mg, p.vit_d_mcg, p.vit_e_mg, p.vit_k_mcg,
|
||
p.vit_b1_mg, p.vit_b2_mg, p.vit_b3_mg, p.vit_b6_mg,
|
||
p.vit_b12_mcg, p.folate_mcg,
|
||
p.calcium_mg, p.iron_mg, p.magnesium_mg, p.zinc_mg, p.copper_mg,
|
||
p.selenium_mcg, p.potassium_mg, p.sodium_mg,
|
||
p.water_pct, cm.name as cooking_method_name
|
||
FROM recipe_ingredients ri
|
||
LEFT JOIN products p ON ri.product_id = p.id AND ri.product_id > 0
|
||
LEFT JOIN cooking_methods cm ON ri.cooking_method_id = cm.id
|
||
WHERE ri.recipe_id = ?1
|
||
ORDER BY ri.id`,
|
||
[recipeId]
|
||
);
|
||
}
|
||
|
||
async function saveRecipe(name, cookingMethodId, servings, notes) {
|
||
await execute(
|
||
`INSERT INTO recipes (name, cooking_method_id, servings, notes, created_at)
|
||
VALUES (?1, ?2, ?3, ?4, datetime('now'))`,
|
||
[name, cookingMethodId ?? null, servings ?? 1, notes ?? null]
|
||
);
|
||
// lastInsertId from tauri-plugin-sql is unreliable; query the actual id
|
||
const rows = await select("SELECT id FROM recipes ORDER BY id DESC LIMIT 1");
|
||
return rows[0]?.id;
|
||
}
|
||
|
||
async function updateRecipe(id, name, cookingMethodId, servings, notes) {
|
||
await execute(
|
||
`UPDATE recipes SET name=?1, cooking_method_id=?2, servings=?3, notes=?4
|
||
WHERE id=?5`,
|
||
[name, cookingMethodId ?? null, servings ?? 1, notes ?? null, id]
|
||
);
|
||
}
|
||
|
||
async function deleteRecipe(id) {
|
||
await execute("DELETE FROM recipe_ingredients WHERE recipe_id = ?1", [id]);
|
||
await execute("DELETE FROM recipes WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function addRecipeIngredient(recipeId, productId, customName, weightG, cookingMethodId) {
|
||
const r = await execute(
|
||
`INSERT INTO recipe_ingredients (recipe_id, product_id, custom_name, amount_g, cooking_method_id)
|
||
VALUES (?1, ?2, ?3, ?4, ?5)`,
|
||
[recipeId, productId ?? 0, customName ?? null, weightG, cookingMethodId ?? null]
|
||
);
|
||
return r.lastInsertId;
|
||
}
|
||
|
||
async function deleteRecipeIngredient(id) {
|
||
await execute("DELETE FROM recipe_ingredients WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function clearRecipeIngredients(recipeId) {
|
||
await execute("DELETE FROM recipe_ingredients WHERE recipe_id = ?1", [recipeId]);
|
||
}
|
||
|
||
// Calculate nutrition for a recipe per serving.
|
||
// For each ingredient: (product nutrients / 100) * weight_g, then apply cooking loss.
|
||
// Cooking loss is taken from ingredient's own cooking_method_id, or recipe's default.
|
||
async function calculateRecipeNutrition(recipeId) {
|
||
const recipe = await getRecipe(recipeId);
|
||
if (!recipe) return null;
|
||
const ingredients = await getRecipeIngredients(recipeId);
|
||
const servings = recipe.servings || 1;
|
||
|
||
// Cache cooking methods to avoid repeated queries
|
||
const cmCache = {};
|
||
async function getCM(cmId) {
|
||
if (!cmId) return null;
|
||
if (cmCache[cmId]) return cmCache[cmId];
|
||
cmCache[cmId] = await getCookingMethod(cmId);
|
||
return cmCache[cmId];
|
||
}
|
||
|
||
// All micronutrient keys tracked in products table
|
||
const microKeys = [
|
||
"vit_a_mcg", "vit_c_mg", "vit_d_mcg", "vit_e_mg", "vit_k_mcg",
|
||
"vit_b1_mg", "vit_b2_mg", "vit_b3_mg", "vit_b6_mg",
|
||
"vit_b12_mcg", "folate_mcg",
|
||
"calcium_mg", "iron_mg", "magnesium_mg", "zinc_mg", "copper_mg",
|
||
"selenium_mcg", "potassium_mg", "sodium_mg",
|
||
];
|
||
|
||
const totals = { kcal: 0, protein: 0, fat: 0, carbs: 0, fiber: 0, water_ml: 0 };
|
||
for (const mk of microKeys) totals[mk] = 0;
|
||
|
||
for (const ing of ingredients) {
|
||
if (!ing.product_id || ing.product_id === 0) continue; // skip custom ingredients without product data
|
||
const factor = (ing.amount_g || 0) / 100;
|
||
|
||
// Raw nutrient values for this ingredient
|
||
const raw = {
|
||
kcal: (ing.kcal_per_100g ?? 0) * factor,
|
||
protein: (ing.protein_per_100g ?? 0) * factor,
|
||
fat: (ing.fat_per_100g ?? 0) * factor,
|
||
carbs: (ing.carbs_per_100g ?? 0) * factor,
|
||
fiber: (ing.fiber_per_100g ?? 0) * factor,
|
||
};
|
||
for (const mk of microKeys) raw[mk] = (ing[mk] ?? 0) * factor;
|
||
raw.water_ml = (ing.water_pct ?? 0) * (ing.amount_g || 0) / 100;
|
||
|
||
// Apply cooking loss: use ingredient's method, or recipe's default
|
||
const cmId = ing.cooking_method_id || recipe.cooking_method_id;
|
||
const cm = await getCM(cmId);
|
||
if (cm) {
|
||
// loss_pct is percentage lost (positive = loss, negative = gain like frying fat)
|
||
raw.kcal *= (1 - (cm.kcal_loss_pct ?? 0) / 100);
|
||
raw.protein *= (1 - (cm.protein_loss_pct ?? 0) / 100);
|
||
raw.fat *= (1 - (cm.fat_loss_pct ?? 0) / 100);
|
||
raw.carbs *= (1 - (cm.carbs_loss_pct ?? 0) / 100);
|
||
raw.fiber *= (1 - (cm.fiber_loss_pct ?? 0) / 100);
|
||
// Vitamin losses: cooking_methods table only has columns for a, c, b1, b2
|
||
raw.vit_a_mcg *= (1 - (cm.vit_a_loss_pct ?? 0) / 100);
|
||
raw.vit_c_mg *= (1 - (cm.vit_c_loss_pct ?? 0) / 100);
|
||
raw.vit_b1_mg *= (1 - (cm.vit_b1_loss_pct ?? 0) / 100);
|
||
raw.vit_b2_mg *= (1 - (cm.vit_b2_loss_pct ?? 0) / 100);
|
||
}
|
||
|
||
for (const k of Object.keys(totals)) {
|
||
totals[k] += raw[k];
|
||
}
|
||
}
|
||
|
||
// Per serving
|
||
const result = {
|
||
kcal: Math.round(totals.kcal / servings),
|
||
protein: Math.round(totals.protein / servings * 10) / 10,
|
||
fat: Math.round(totals.fat / servings * 10) / 10,
|
||
carbs: Math.round(totals.carbs / servings * 10) / 10,
|
||
fiber: Math.round(totals.fiber / servings * 10) / 10,
|
||
servings: servings,
|
||
totalWeight: ingredients.reduce((s, i) => s + (i.amount_g || 0), 0),
|
||
};
|
||
// Add all micronutrients per serving (rounded)
|
||
for (const mk of microKeys) {
|
||
result[mk] = Math.round(totals[mk] / servings * 100) / 100;
|
||
}
|
||
result.water_ml = Math.round(totals.water_ml / servings);
|
||
return result;
|
||
}
|
||
|
||
// Log a recipe as a food entry — stores calculated nutrition per serving
|
||
async function addFoodFromRecipe(date, time, recipeId, customAmountG) {
|
||
const nutr = await calculateRecipeNutrition(recipeId);
|
||
if (!nutr) return;
|
||
const servingG = Math.round(nutr.totalWeight / nutr.servings);
|
||
const amountG = customAmountG ?? servingG;
|
||
const scale = servingG > 0 ? amountG / servingG : 1;
|
||
await execute(
|
||
`INSERT INTO food_log
|
||
(date, time, recipe_id, amount_g, kcal, protein_g, fat_g, carbs_g, fiber_g, note)
|
||
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)`,
|
||
[date, time, recipeId, amountG,
|
||
Math.round(nutr.kcal * scale),
|
||
Math.round(nutr.protein * scale * 10) / 10,
|
||
Math.round(nutr.fat * scale * 10) / 10,
|
||
Math.round(nutr.carbs * scale * 10) / 10,
|
||
Math.round(nutr.fiber * scale * 10) / 10, null]
|
||
);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Food log
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getFoodForDate(date) {
|
||
return select(
|
||
`SELECT fl.*, p.name as product_name, p.brand as product_brand,
|
||
COALESCE(p.nutrients_guessed, 0) as nutrients_guessed, p.source as product_source,
|
||
p.vit_a_mcg, p.vit_c_mg, p.vit_d_mcg, p.vit_e_mg, p.vit_k_mcg,
|
||
p.vit_b1_mg, p.vit_b2_mg, p.vit_b3_mg, p.vit_b6_mg,
|
||
p.vit_b12_mcg, p.folate_mcg,
|
||
p.calcium_mg, p.iron_mg, p.magnesium_mg, p.zinc_mg, p.copper_mg,
|
||
p.selenium_mcg, p.potassium_mg, p.sodium_mg,
|
||
p.phosphorus_mg, p.carotene_mcg, p.water_pct,
|
||
r.name as recipe_name, r.cached_nutrients,
|
||
su.name as unit_name, su.name_short as unit_short
|
||
FROM food_log fl
|
||
LEFT JOIN products p ON fl.product_id = p.id
|
||
LEFT JOIN recipes r ON fl.recipe_id = r.id
|
||
LEFT JOIN serving_units su ON fl.unit_id = su.id
|
||
WHERE fl.date = ?1 ORDER BY fl.time ASC`,
|
||
[date]
|
||
);
|
||
}
|
||
|
||
async function addFoodManual(date, time, desc, kcal) {
|
||
await execute(
|
||
"INSERT INTO food_log (date, time, note, amount_g, kcal) VALUES (?1,?2,?3,0,?4)",
|
||
[date, time, desc, kcal ?? null]
|
||
);
|
||
}
|
||
|
||
async function addFoodEntry(date, time, productId, amountG, note, unitId, unitAmount) {
|
||
let kcal = null, protein = null, fat = null, carbs = null, fiber = null;
|
||
if (productId) {
|
||
const p = await getProduct(productId);
|
||
if (p && p.kcal_per_100g != null) {
|
||
const f = amountG / 100;
|
||
kcal = Math.round((p.kcal_per_100g ?? 0) * f);
|
||
protein = Math.round((p.protein_per_100g ?? 0) * f * 10) / 10;
|
||
fat = Math.round((p.fat_per_100g ?? 0) * f * 10) / 10;
|
||
carbs = Math.round((p.carbs_per_100g ?? 0) * f * 10) / 10;
|
||
fiber = Math.round((p.fiber_per_100g ?? 0) * f * 10) / 10;
|
||
}
|
||
}
|
||
await execute(
|
||
`INSERT INTO food_log
|
||
(date, time, product_id, amount_g, kcal, protein_g, fat_g, carbs_g, fiber_g, note, unit_id, unit_amount)
|
||
VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12)`,
|
||
[date, time, productId ?? null, amountG,
|
||
kcal, protein, fat, carbs, fiber, note ?? null,
|
||
unitId ?? null, unitAmount ?? null]
|
||
);
|
||
}
|
||
|
||
async function updateFoodEntry(id, time, amountG, note, kcalOverride, unitId, unitAmount) {
|
||
// Recalculate nutrition from product or recipe
|
||
const rows = await select(
|
||
"SELECT product_id, recipe_id, amount_g, kcal, protein_g, fat_g, carbs_g, fiber_g FROM food_log WHERE id = ?1", [id]);
|
||
if (!rows.length) return;
|
||
const row = rows[0];
|
||
|
||
let kcal = kcalOverride, protein = null, fat = null, carbs = null, fiber = null;
|
||
if (row.product_id && kcalOverride == null) {
|
||
// Product: recalculate from kcal_per_100g
|
||
const p = await getProduct(row.product_id);
|
||
if (p && p.kcal_per_100g != null) {
|
||
const f = amountG / 100;
|
||
kcal = Math.round((p.kcal_per_100g ?? 0) * f);
|
||
protein = Math.round((p.protein_per_100g ?? 0) * f * 10) / 10;
|
||
fat = Math.round((p.fat_per_100g ?? 0) * f * 10) / 10;
|
||
carbs = Math.round((p.carbs_per_100g ?? 0) * f * 10) / 10;
|
||
fiber = Math.round((p.fiber_per_100g ?? 0) * f * 10) / 10;
|
||
}
|
||
} else if (row.recipe_id && kcalOverride == null && row.amount_g > 0) {
|
||
// Recipe: scale proportionally to amount change
|
||
const scale = amountG / row.amount_g;
|
||
kcal = Math.round((row.kcal ?? 0) * scale);
|
||
protein = Math.round((row.protein_g ?? 0) * scale * 10) / 10;
|
||
fat = Math.round((row.fat_g ?? 0) * scale * 10) / 10;
|
||
carbs = Math.round((row.carbs_g ?? 0) * scale * 10) / 10;
|
||
fiber = Math.round((row.fiber_g ?? 0) * scale * 10) / 10;
|
||
}
|
||
await execute(
|
||
`UPDATE food_log SET time=?1, amount_g=?2, note=?3,
|
||
kcal=?4, protein_g=?5, fat_g=?6, carbs_g=?7, fiber_g=?8,
|
||
unit_id=?9, unit_amount=?10
|
||
WHERE id=?11`,
|
||
[time, amountG, note ?? null, kcal, protein, fat, carbs, fiber,
|
||
unitId ?? null, unitAmount ?? null, id]
|
||
);
|
||
}
|
||
|
||
async function deleteFoodEntry(id) {
|
||
await execute("DELETE FROM food_log WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Activity log
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getActivitiesForDate(date) {
|
||
return select("SELECT * FROM activity_log WHERE date = ?1 ORDER BY time ASC", [date]);
|
||
}
|
||
|
||
async function addActivity(date, time, type, desc, minutes, kcal, opts) {
|
||
// Build column list dynamically — new columns may not exist yet on first run
|
||
const cols = ["date", "time", "type", "desc", "minutes", "kcal"];
|
||
const vals = [date, time, type ?? "manual", desc, minutes ?? null, kcal ?? null];
|
||
if (opts) {
|
||
const actCols = (await select("PRAGMA table_info(activity_log)")).map(c => c.name);
|
||
if (actCols.includes("activity_type")) { cols.push("activity_type"); vals.push(opts.activity_type ?? "manual"); }
|
||
if (actCols.includes("sets") && opts.sets) { cols.push("sets"); vals.push(opts.sets); }
|
||
if (actCols.includes("reps") && opts.reps) { cols.push("reps"); vals.push(opts.reps); }
|
||
if (actCols.includes("distance_m") && opts.distance_m) { cols.push("distance_m"); vals.push(opts.distance_m); }
|
||
}
|
||
const placeholders = cols.map((_, i) => `?${i + 1}`).join(",");
|
||
await execute(`INSERT INTO activity_log (${cols.join(",")}) VALUES (${placeholders})`, vals);
|
||
}
|
||
|
||
async function deleteActivity(id) {
|
||
await execute("DELETE FROM activity_log WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Supplements
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getSupplements() {
|
||
return select("SELECT * FROM supplements ORDER BY name COLLATE NOCASE");
|
||
}
|
||
|
||
async function addSupplement(s) {
|
||
const cols = [
|
||
"name", "daily_dose_g",
|
||
"vit_a_mcg", "vit_c_mg", "vit_d_mcg", "vit_e_mg", "vit_k_mcg",
|
||
"vit_b1_mg", "vit_b2_mg", "vit_b3_mg", "vit_b5_mg", "vit_b6_mg",
|
||
"vit_b7_mcg", "vit_b12_mcg", "folate_mcg",
|
||
"calcium_mg", "iron_mg", "magnesium_mg", "zinc_mg", "copper_mg",
|
||
"selenium_mcg", "potassium_mg", "sodium_mg", "phosphorus_mg",
|
||
"iodine_mg", "chromium_mg", "manganese_mg", "molybdenum_mcg",
|
||
"silicon_mg", "vanadium_mg", "lutein_mg", "lycopene_mg", "carotene_mcg",
|
||
"fish_oil_mg", "omega3_mg", "epa_mg", "dha_mg", "omega6_mg", "omega9_mg",
|
||
"ginseng_mg", "resveratrol_mg", "coq10_mg",
|
||
];
|
||
const placeholders = cols.map((_, i) => `?${i + 1}`).join(",");
|
||
const vals = cols.map(c => s[c] ?? null);
|
||
const r = await execute(
|
||
`INSERT INTO supplements (${cols.join(",")}) VALUES (${placeholders})`,
|
||
vals
|
||
);
|
||
return r.lastInsertId;
|
||
}
|
||
|
||
async function getSupplement(id) {
|
||
const rows = await select("SELECT * FROM supplements WHERE id = ?1", [id]);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function deleteSupplement(id) {
|
||
await execute("DELETE FROM supplement_log WHERE supplement_id = ?1", [id]);
|
||
await execute("DELETE FROM supplements WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function logSupplement(date, time, supplementId, doseG) {
|
||
await execute(
|
||
"INSERT INTO supplement_log (date, time, supplement_id, dose_g) VALUES (?1,?2,?3,?4)",
|
||
[date, time, supplementId, doseG ?? null]
|
||
);
|
||
}
|
||
|
||
async function getSupplementLogForDate(date) {
|
||
return select(
|
||
`SELECT sl.*, s.name as supplement_name, s.daily_dose_g
|
||
FROM supplement_log sl
|
||
JOIN supplements s ON sl.supplement_id = s.id
|
||
WHERE sl.date = ?1 ORDER BY sl.time ASC`,
|
||
[date]
|
||
);
|
||
}
|
||
|
||
async function deleteSupplementLog(id) {
|
||
await execute("DELETE FROM supplement_log WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function getSupplementTotalsForDate(date) {
|
||
const rows = await select(
|
||
`SELECT
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_a_mcg) as vit_a_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_c_mg) as vit_c_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_d_mcg) as vit_d_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_e_mg) as vit_e_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_k_mcg) as vit_k_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b1_mg) as vit_b1_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b2_mg) as vit_b2_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b3_mg) as vit_b3_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b5_mg) as vit_b5_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b6_mg) as vit_b6_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b7_mcg) as vit_b7_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.vit_b12_mcg) as vit_b12_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.folate_mcg) as folate_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.calcium_mg) as calcium_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.iron_mg) as iron_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.magnesium_mg) as magnesium_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.zinc_mg) as zinc_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.copper_mg) as copper_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.selenium_mcg) as selenium_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.potassium_mg) as potassium_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.sodium_mg) as sodium_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.chromium_mg) as chromium_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.manganese_mg) as manganese_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.molybdenum_mcg) as molybdenum_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.iodine_mg) as iodine_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.phosphorus_mg) as phosphorus_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.carotene_mcg) as carotene_mcg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.omega3_mg) as omega3_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.epa_mg) as epa_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.dha_mg) as dha_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.omega6_mg) as omega6_mg,
|
||
SUM(COALESCE(sl.dose_g,1) * s.omega9_mg) as omega9_mg
|
||
FROM supplement_log sl
|
||
JOIN supplements s ON sl.supplement_id = s.id
|
||
WHERE sl.date = ?1`,
|
||
[date]
|
||
);
|
||
return rows[0] || {};
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Serving units
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getServingUnits() {
|
||
return select("SELECT * FROM serving_units ORDER BY id");
|
||
}
|
||
|
||
async function getUnitsForProduct(productId) {
|
||
// Returns product-specific units (with their grams_per_unit) + universal units
|
||
return select(
|
||
`SELECT su.*, COALESCE(pu.grams_per_unit, su.grams_equivalent) as grams_per_unit
|
||
FROM serving_units su
|
||
LEFT JOIN product_units pu ON pu.unit_id = su.id AND pu.product_id = ?1
|
||
WHERE su.category = 'universal' OR pu.id IS NOT NULL
|
||
ORDER BY CASE WHEN pu.id IS NOT NULL THEN 0 ELSE 1 END, su.id`,
|
||
[productId]
|
||
);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Analytics
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getFoodTotalsForDate(date) {
|
||
const rows = await select(
|
||
`SELECT SUM(kcal) as kcal, SUM(protein_g) as protein_g,
|
||
SUM(fat_g) as fat_g, SUM(carbs_g) as carbs_g, SUM(fiber_g) as fiber_g
|
||
FROM food_log WHERE date = ?1`,
|
||
[date]
|
||
);
|
||
return rows[0] ?? { kcal: 0, protein_g: 0, fat_g: 0, carbs_g: 0, fiber_g: 0 };
|
||
}
|
||
|
||
async function getActivityTotalsForDate(date) {
|
||
const manual = await select(
|
||
"SELECT SUM(kcal) as kcal, SUM(minutes) as minutes FROM activity_log WHERE date = ?1",
|
||
[date]
|
||
);
|
||
const gps = await select(
|
||
"SELECT SUM(total_kcal) as kcal FROM gps_sessions WHERE date = ?1 AND total_kcal IS NOT NULL",
|
||
[date]
|
||
);
|
||
const manualKcal = manual[0]?.kcal ?? 0;
|
||
const gpsKcal = gps[0]?.kcal ?? 0;
|
||
return { kcal: manualKcal + gpsKcal, minutes: manual[0]?.minutes ?? 0 };
|
||
}
|
||
|
||
// N calendar days ending at endDate (default today), range (end-N, end] = exactly N days.
|
||
async function getDailyFoodTotals(days, endDate) {
|
||
const end = endDate || "now";
|
||
return select(
|
||
`SELECT date, SUM(kcal) as kcal, SUM(protein_g) as protein_g,
|
||
SUM(fat_g) as fat_g, SUM(carbs_g) as carbs_g
|
||
FROM food_log WHERE date > date(?2, ?1) AND date <= date(?2)
|
||
GROUP BY date ORDER BY date ASC`,
|
||
[`-${days ?? 30} days`, end]
|
||
);
|
||
}
|
||
|
||
async function getDailyActivityTotals(days, endDate) {
|
||
const offset = `-${days ?? 30} days`;
|
||
const end = endDate || "now";
|
||
return select(
|
||
`SELECT date, SUM(kcal) as kcal FROM (
|
||
SELECT date, kcal FROM activity_log WHERE date > date(?2, ?1) AND date <= date(?2)
|
||
UNION ALL
|
||
SELECT date, total_kcal as kcal FROM gps_sessions WHERE date > date(?2, ?1) AND date <= date(?2) AND total_kcal IS NOT NULL
|
||
) GROUP BY date ORDER BY date ASC`,
|
||
[offset, end]
|
||
);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// GPS sessions & points
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function createGpsSession(date, startTime, activityType) {
|
||
const id = Date.now();
|
||
await execute(
|
||
`INSERT INTO gps_sessions (id, date, start_time, activity_type, source)
|
||
VALUES (?1, ?2, ?3, ?4, 'recorded')`,
|
||
[id, date, startTime, activityType]
|
||
);
|
||
return id;
|
||
}
|
||
|
||
// Column list cached after first call (avoids PRAGMA per insert)
|
||
let _gpsPointCols = null;
|
||
|
||
async function addGpsPoint(sessionId, ts, lat, lon, altGps, speedMs, seq, bleData, sensorData) {
|
||
// Build INSERT dynamically based on existing columns (avoids crash if migration hasn't run)
|
||
if (!_gpsPointCols) {
|
||
try {
|
||
const info = await select("PRAGMA table_info(gps_points)");
|
||
_gpsPointCols = new Set(info.map(r => r.name));
|
||
} catch { _gpsPointCols = new Set(["session_id","ts","lat","lon","altitude_gps_m","speed_ms","seq"]); }
|
||
}
|
||
const cols = ["session_id","ts","lat","lon","altitude_gps_m","speed_ms","seq"];
|
||
const vals = [sessionId, ts, lat, lon, altGps ?? null, speedMs ?? null, seq ?? null];
|
||
|
||
const extra = [
|
||
["heart_rate", bleData?.hr], ["spo2", bleData?.spo2],
|
||
["bp_sys", bleData?.bp_sys], ["bp_dia", bleData?.bp_dia],
|
||
["accel_rms", sensorData?.accel_rms], ["accel_max", sensorData?.accel_max],
|
||
["gyro_pitch", sensorData?.gyro_pitch], ["gyro_yaw", sensorData?.gyro_yaw],
|
||
["bearing_deg", sensorData?.bearing], ["accuracy_m", sensorData?.accuracy],
|
||
["altitude_baro_m", sensorData?.altitude_baro_m],
|
||
];
|
||
for (const [col, val] of extra) {
|
||
if (_gpsPointCols.has(col)) { cols.push(col); vals.push(val ?? null); }
|
||
}
|
||
const ph = cols.map((_, i) => `?${i + 1}`).join(",");
|
||
await execute(`INSERT INTO gps_points (${cols.join(",")}) VALUES (${ph})`, vals);
|
||
}
|
||
|
||
// Bulk insert native points (recovery). Multi-row INSERT in chunks — MUCH faster
|
||
// than per-point addGpsPoint (one IPC per ~75 rows instead of per row). `points`
|
||
// are native point objects {ts, lat, lon, altitude, speed_ms, bearing, accel_*, gyro_*}.
|
||
// Returns the number of rows inserted. seq continues from startSeq.
|
||
async function addGpsPointsBulk(sessionId, points, startSeq) {
|
||
if (!points || !points.length) return 0;
|
||
if (!_gpsPointCols) {
|
||
try { const info = await select("PRAGMA table_info(gps_points)"); _gpsPointCols = new Set(info.map(r => r.name)); }
|
||
catch { _gpsPointCols = new Set(["session_id","ts","lat","lon","altitude_gps_m","speed_ms","seq"]); }
|
||
}
|
||
const base = ["session_id","ts","lat","lon","altitude_gps_m","speed_ms","seq"];
|
||
const extra = ["accel_rms","accel_max","gyro_pitch","gyro_yaw","bearing_deg","accuracy_m"].filter(c => _gpsPointCols.has(c));
|
||
const allCols = base.concat(extra);
|
||
const perRow = allCols.length;
|
||
const maxRows = Math.max(1, Math.floor(900 / perRow)); // stay under SQLite bind-var limit
|
||
let seq = startSeq || 0;
|
||
let inserted = 0;
|
||
for (let i = 0; i < points.length; i += maxRows) {
|
||
const chunk = points.slice(i, i + maxRows);
|
||
const rowSql = [];
|
||
const vals = [];
|
||
let ph = 1;
|
||
for (const p of chunk) {
|
||
seq++;
|
||
const row = [sessionId, p.ts, p.lat, p.lon, p.altitude ?? null, p.speed_ms ?? null, seq];
|
||
for (const c of extra) row.push(c === "bearing_deg" ? (p.bearing ?? null) : c === "accuracy_m" ? (p.accuracy ?? null) : (p[c] ?? null));
|
||
rowSql.push("(" + row.map(() => `?${ph++}`).join(",") + ")");
|
||
vals.push(...row);
|
||
}
|
||
await execute(`INSERT INTO gps_points (${allCols.join(",")}) VALUES ${rowSql.join(",")}`, vals);
|
||
inserted += chunk.length;
|
||
}
|
||
return inserted;
|
||
}
|
||
|
||
// Gap-fill (BRouter): persist the routed dropout points as estimated=1, reversibly.
|
||
// `points` is the full fillGaps result array (real + estimated interleaved, ordered).
|
||
// Each estimated point gets seq = the preceding real point's seq, so the existing
|
||
// ORDER BY COALESCE(seq,999999), ts places it inside the gap; ts is already interpolated.
|
||
// Original points are untouched → revertGapFill removes only estimated=1.
|
||
async function saveGapFill(sessionId, points) {
|
||
let lastSeq = 0, toInsert = [];
|
||
for (let p of points) {
|
||
if (p.estimated) toInsert.push({ ts: p.ts, lat: p.lat, lon: p.lon, seq: lastSeq });
|
||
else if (p.seq != null) lastSeq = p.seq;
|
||
}
|
||
if (!toInsert.length) return 0;
|
||
let BATCH = 120; // 5 params/row → under SQLite bind-var limit
|
||
for (let i = 0; i < toInsert.length; i += BATCH) {
|
||
let chunk = toInsert.slice(i, i + BATCH);
|
||
let rowSql = [], vals = [], ph = 1;
|
||
for (let p of chunk) {
|
||
rowSql.push(`(?${ph},?${ph+1},?${ph+2},?${ph+3},?${ph+4},1)`);
|
||
vals.push(sessionId, p.ts, p.lat, p.lon, p.seq); ph += 5;
|
||
}
|
||
await execute(`INSERT INTO gps_points (session_id, ts, lat, lon, seq, estimated) VALUES ${rowSql.join(",")}`, vals);
|
||
}
|
||
// Re-sequence the WHOLE session by ts so getGpsPoints (ORDER BY seq,ts) returns the true
|
||
// order. Without this, on merged/split tracks the real points have seq=NULL, the inserted
|
||
// gap-fill points get seq=0, and they sort to the FRONT — a straight line from the start to
|
||
// the gap ("returns to the beginning"). Re-numbering by ts is a no-op for dense tracks and
|
||
// fixes NULL/mixed ones. Batched CASE update (like saveSmoothedTrack) to beat the IPC/bind cost.
|
||
let ordered = await select("SELECT id FROM gps_points WHERE session_id=?1 ORDER BY ts ASC, id ASC", [sessionId]);
|
||
let SEQCHUNK = 300; // 2 params/row (id, seq) → under SQLite's 999 bind-var limit
|
||
for (let i = 0; i < ordered.length; i += SEQCHUNK) {
|
||
let chunk = ordered.slice(i, i + SEQCHUNK);
|
||
let when = [], ids = [], params = [];
|
||
for (let k = 0; k < chunk.length; k++) {
|
||
params.push(chunk[k].id); let idi = params.length;
|
||
params.push(i + k + 1); let sqi = params.length;
|
||
when.push(`WHEN ?${idi} THEN ?${sqi}`);
|
||
ids.push(`?${idi}`);
|
||
}
|
||
await execute(`UPDATE gps_points SET seq=CASE id ${when.join(" ")} END WHERE id IN (${ids.join(",")})`, params);
|
||
}
|
||
await execute("UPDATE gps_sessions SET gap_filled=1 WHERE id=?1", [sessionId]);
|
||
return toInsert.length;
|
||
}
|
||
|
||
async function revertGapFill(sessionId) {
|
||
await execute("DELETE FROM gps_points WHERE session_id=?1 AND estimated=1", [sessionId]);
|
||
await execute("UPDATE gps_sessions SET gap_filled=0 WHERE id=?1", [sessionId]);
|
||
}
|
||
|
||
// Persist the per-point "walking" flag (walkIds = point ids in walking runs). Used by the
|
||
// track colour, the ridden-roads overlay (rebuild_roads) and the ride-graph router.
|
||
async function setWalkFlags(sessionId, walkIds) {
|
||
await execute("UPDATE gps_points SET walk=0 WHERE session_id=?1", [sessionId]);
|
||
let CHUNK = 200;
|
||
for (let i = 0; i < walkIds.length; i += CHUNK) {
|
||
let ch = walkIds.slice(i, i + CHUNK);
|
||
let ph = ch.map((_, k) => "?" + (k + 1)).join(",");
|
||
await execute(`UPDATE gps_points SET walk=1 WHERE id IN (${ph})`, ch);
|
||
}
|
||
}
|
||
|
||
async function finishGpsSession(id, endTime, distM, kcal, avgSpd, maxSpd, elevGain, elevLoss, pauses) {
|
||
// Guard against NaN — SQLite may reject or corrupt NaN values
|
||
const safe = v => (v != null && !isNaN(v)) ? v : null;
|
||
// pauses: array of manual-pause intervals; store as JSON. undefined = leave column as-is.
|
||
const hasPauses = pauses !== undefined;
|
||
const pausesJson = hasPauses ? JSON.stringify(pauses || []) : null;
|
||
await execute(
|
||
`UPDATE gps_sessions SET end_time=?1, total_distance_m=?2, total_kcal=?3,
|
||
avg_speed_kmh=?4, max_speed_kmh=?5, elevation_gain_m=?6, elevation_loss_m=?7${hasPauses ? ", pauses=?9" : ""}
|
||
WHERE id=?8`,
|
||
hasPauses
|
||
? [endTime, safe(distM), safe(kcal), safe(avgSpd), safe(maxSpd), safe(elevGain), safe(elevLoss), id, pausesJson]
|
||
: [endTime, safe(distM), safe(kcal), safe(avgSpd), safe(maxSpd), safe(elevGain), safe(elevLoss), id]
|
||
);
|
||
}
|
||
|
||
// Persist a de-jittered (smoothed) variant: write lat_smoothed/lon_smoothed per
|
||
// point (original lat/lon untouched) and flag the session. Reversible via
|
||
// setSessionSmoothed(id, false). `smoothedPoints` carry each point's `id` + the
|
||
// smoothed `lat`/`lon`.
|
||
async function saveSmoothedTrack(sessionId, smoothedPoints, window, iters, onProgress) {
|
||
// One UPDATE per point over IPC is thousands of round-trips (slow, ~1 pt/ms).
|
||
// Batch into a single CASE-per-chunk UPDATE — ~15 statements instead of 4000+.
|
||
// Chunk keeps bound params under SQLite's 999 limit (250 pts × 3 params = 750).
|
||
let pts = smoothedPoints.filter(p => p.id != null);
|
||
let CHUNK = 250;
|
||
for (let start = 0; start < pts.length; start += CHUNK) {
|
||
let chunk = pts.slice(start, start + CHUNK);
|
||
let latWhen = [], lonWhen = [], ids = [], params = [];
|
||
for (let p of chunk) {
|
||
params.push(p.id); let idi = params.length;
|
||
params.push(p.lat); let lati = params.length;
|
||
params.push(p.lon); let loni = params.length;
|
||
latWhen.push(`WHEN ?${idi} THEN ?${lati}`);
|
||
lonWhen.push(`WHEN ?${idi} THEN ?${loni}`);
|
||
ids.push(`?${idi}`);
|
||
}
|
||
await execute(
|
||
`UPDATE gps_points SET lat_smoothed=CASE id ${latWhen.join(" ")} END, ` +
|
||
`lon_smoothed=CASE id ${lonWhen.join(" ")} END WHERE id IN (${ids.join(",")})`,
|
||
params
|
||
);
|
||
if (onProgress) { try { onProgress(Math.min(start + CHUNK, pts.length), pts.length); } catch {} }
|
||
}
|
||
await execute("UPDATE gps_sessions SET smoothed=1, smooth_window=?1, smooth_iters=?2 WHERE id=?3", [window, iters, sessionId]);
|
||
}
|
||
|
||
async function setSessionSmoothed(sessionId, on) {
|
||
await execute("UPDATE gps_sessions SET smoothed=?1 WHERE id=?2", [on ? 1 : 0, sessionId]);
|
||
}
|
||
|
||
// NOTE: tauri-plugin-sql corrupts large i64 ids when returned as JS numbers
|
||
// (off by ±1). Always read/pass session ids via the text `id_str` column.
|
||
async function getGpsSessions(limit, offset) {
|
||
return select(
|
||
"SELECT *, CAST(id AS TEXT) AS id_str FROM gps_sessions ORDER BY date DESC, start_time DESC, id DESC LIMIT ?1 OFFSET ?2",
|
||
[limit ?? 50, offset ?? 0]
|
||
);
|
||
}
|
||
|
||
async function getGpsSessionsForDate(date) {
|
||
return select(
|
||
"SELECT *, CAST(id AS TEXT) AS id_str FROM gps_sessions WHERE date = ?1 ORDER BY start_time ASC",
|
||
[date]
|
||
);
|
||
}
|
||
|
||
async function getGpsSession(id) {
|
||
const rows = await select("SELECT *, CAST(id AS TEXT) AS id_str FROM gps_sessions WHERE id = ?1", [id]);
|
||
if (!rows.length) return null;
|
||
const s = rows[0];
|
||
if (typeof s.pauses === "string") { try { s.pauses = JSON.parse(s.pauses); } catch { s.pauses = []; } }
|
||
if (typeof s.wind_marks === "string") { try { s.wind_marks = JSON.parse(s.wind_marks); } catch { s.wind_marks = []; } }
|
||
return s;
|
||
}
|
||
|
||
// Save headwind labels for a session (JSON). Kept separate from finishGpsSession so
|
||
// recompute paths preserve marks (they never call this). Marks are ground truth for
|
||
// the wind estimate — they do not affect distance/kcal/power.
|
||
async function setSessionWindMarks(id, marks) {
|
||
await execute("UPDATE gps_sessions SET wind_marks=?1 WHERE id=?2", [JSON.stringify(marks || []), id]);
|
||
}
|
||
|
||
async function getGpsPoints(sessionId) {
|
||
return select("SELECT * FROM gps_points WHERE session_id = ?1 ORDER BY COALESCE(seq, 999999), ts ASC", [sessionId]);
|
||
}
|
||
|
||
async function deleteGpsSession(id) {
|
||
await execute("DELETE FROM gps_points WHERE session_id = ?1", [id]);
|
||
await execute("DELETE FROM gps_sessions WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// Merge several sessions into one. Points from every session move into the EARLIEST
|
||
// (by start time) session; each moved point keeps its original session in
|
||
// orig_session_id (so a future "split" can undo this), the target's end_time extends to
|
||
// the latest, and the other session rows are deleted. Ids are text (large i64 read back
|
||
// off-by-one), passed as strings and CAST to INTEGER for the id/session_id columns.
|
||
// Returns the surviving (target) session id_str. Caller recomputes stats afterwards.
|
||
async function mergeGpsSessions(idStrs) {
|
||
if (!Array.isArray(idStrs) || idStrs.length < 2) return null;
|
||
const sessions = [];
|
||
for (const id of idStrs) { const s = await getGpsSession(id); if (s) sessions.push(s); }
|
||
if (sessions.length < 2) return null;
|
||
const ord = (s) => (s.date || "1970-01-01") + "T" + (s.start_time || "00:00:00");
|
||
sessions.sort((a, b) => ord(a) < ord(b) ? -1 : ord(a) > ord(b) ? 1 : 0);
|
||
const target = sessions[0];
|
||
const targetId = target.id_str;
|
||
const endOf = (s) => (s.date || "1970-01-01") + "T" + (s.end_time || s.start_time || "00:00:00");
|
||
const latestEnd = sessions.reduce((m, s) => endOf(s) > endOf(m) ? s : m, target).end_time;
|
||
// Tag the target's own points too, so orig_session_id is complete for a later split.
|
||
await execute("UPDATE gps_points SET orig_session_id = CAST(?1 AS TEXT) WHERE session_id = CAST(?1 AS INTEGER) AND orig_session_id IS NULL", [targetId]);
|
||
for (const s of sessions.slice(1)) {
|
||
await execute(
|
||
"UPDATE gps_points SET orig_session_id = COALESCE(orig_session_id, CAST(session_id AS TEXT)), session_id = CAST(?1 AS INTEGER) WHERE session_id = CAST(?2 AS INTEGER)",
|
||
[targetId, s.id_str]
|
||
);
|
||
await execute("DELETE FROM gps_sessions WHERE id = CAST(?1 AS INTEGER)", [s.id_str]);
|
||
}
|
||
if (latestEnd) await execute("UPDATE gps_sessions SET end_time = ?1 WHERE id = CAST(?2 AS INTEGER)", [latestEnd, targetId]);
|
||
// Each source session numbered its points seq 1..N, so after the move the seq values
|
||
// collide and "ORDER BY seq" would interleave the tracks (measured: a 43 km "segment"
|
||
// and 6745 km/h passes). Clear seq so getGpsPoints falls back to timestamp order, which
|
||
// is the true order across the merged tracks.
|
||
await execute("UPDATE gps_points SET seq = NULL WHERE session_id = CAST(?1 AS INTEGER)", [targetId]);
|
||
return targetId;
|
||
}
|
||
|
||
// Split one session in two at a chosen point. The second part (points whose ids are in
|
||
// part2Ids) moves to a NEW session that copies the original's date / activity / bike /
|
||
// source; the first part keeps the original session. Bike stays the same on both halves -
|
||
// the rider then reassigns the bike on a half and recomputes if the gear differed. Times
|
||
// are "HH:MM:SS". Returns the new (second-part) session id_str. Caller recomputes both.
|
||
async function splitGpsSession(sessionId, part2Ids, part1EndTime, part2StartTime) {
|
||
if (!Array.isArray(part2Ids) || part2Ids.length === 0) return null;
|
||
const s = await getGpsSession(sessionId);
|
||
if (!s) return null;
|
||
const newId = Date.now();
|
||
await execute(
|
||
`INSERT INTO gps_sessions (id, date, start_time, end_time, activity_type, source, bike_id)
|
||
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)`,
|
||
[newId, s.date, part2StartTime, s.end_time, s.activity_type, s.source ?? "recorded", s.bike_id ?? null]
|
||
);
|
||
let CHUNK = 200;
|
||
for (let i = 0; i < part2Ids.length; i += CHUNK) {
|
||
let ch = part2Ids.slice(i, i + CHUNK);
|
||
let ph = ch.map((_, k) => "?" + (k + 2)).join(",");
|
||
await execute(`UPDATE gps_points SET session_id = CAST(?1 AS INTEGER), seq = NULL WHERE id IN (${ph})`, [newId, ...ch]);
|
||
}
|
||
if (part1EndTime) await execute("UPDATE gps_sessions SET end_time = ?1 WHERE id = CAST(?2 AS INTEGER)", [part1EndTime, sessionId]);
|
||
return String(newId);
|
||
}
|
||
|
||
// Undo a merge: split a merged session back into its original sessions by orig_session_id
|
||
// (merge preserved it on every point). The current session keeps its own original group; each
|
||
// other orig_session_id gets its session row recreated (metadata copied). Points are retagged
|
||
// (orig_session_id cleared, seq cleared → timestamp order). Returns the list of resulting
|
||
// session id_strs (incl. the current one), or null if this session isn't a merge. The caller
|
||
// recomputes stats + times for each (computeStats + finishGpsSession).
|
||
async function unmergeGpsSession(sessionId) {
|
||
const s = await getGpsSession(sessionId);
|
||
if (!s) return null;
|
||
const rows = await select(
|
||
"SELECT DISTINCT CAST(orig_session_id AS TEXT) AS o FROM gps_points WHERE session_id = CAST(?1 AS INTEGER) AND orig_session_id IS NOT NULL",
|
||
[s.id_str]
|
||
);
|
||
const origs = rows.map(r => r.o).filter(Boolean);
|
||
if (origs.length < 2) return null; // not a merge — nothing to split
|
||
const resultIds = [];
|
||
for (const oid of origs) {
|
||
if (oid !== s.id_str) {
|
||
await execute(
|
||
`INSERT INTO gps_sessions (id, date, start_time, end_time, activity_type, source, bike_id)
|
||
VALUES (CAST(?1 AS INTEGER), ?2, ?3, ?4, ?5, ?6, ?7)`,
|
||
[oid, s.date, s.start_time, s.end_time, s.activity_type, s.source ?? "recorded", s.bike_id ?? null]
|
||
);
|
||
await execute(
|
||
"UPDATE gps_points SET session_id = CAST(?1 AS INTEGER), orig_session_id = NULL, seq = NULL WHERE session_id = CAST(?2 AS INTEGER) AND orig_session_id = CAST(?1 AS TEXT)",
|
||
[oid, s.id_str]
|
||
);
|
||
} else {
|
||
await execute(
|
||
"UPDATE gps_points SET orig_session_id = NULL, seq = NULL WHERE session_id = CAST(?1 AS INTEGER) AND orig_session_id = CAST(?1 AS TEXT)",
|
||
[s.id_str]
|
||
);
|
||
}
|
||
resultIds.push(oid);
|
||
}
|
||
// Fix each piece's date/times from its own points (merge had stretched them to the whole span).
|
||
for (const id of resultIds) {
|
||
const ts = await select("SELECT MIN(ts) AS a, MAX(ts) AS b FROM gps_points WHERE session_id = CAST(?1 AS INTEGER)", [id]);
|
||
const a = ts[0] && ts[0].a;
|
||
if (a) {
|
||
const hm = (t) => new Date(t).toTimeString().slice(0, 8);
|
||
await execute("UPDATE gps_sessions SET date = ?1, start_time = ?2, end_time = ?3 WHERE id = CAST(?4 AS INTEGER)",
|
||
[new Date(a).toISOString().slice(0, 10), hm(a), hm(ts[0].b), id]);
|
||
}
|
||
}
|
||
return resultIds;
|
||
}
|
||
|
||
// How many distinct original sessions are glued into this one (>1 ⇒ it's a merge that can be split).
|
||
async function countMergedOrigins(sessionId) {
|
||
const s = await getGpsSession(sessionId);
|
||
if (!s) return 0;
|
||
const r = await select(
|
||
"SELECT COUNT(DISTINCT orig_session_id) AS c FROM gps_points WHERE session_id = CAST(?1 AS INTEGER) AND orig_session_id IS NOT NULL",
|
||
[s.id_str]
|
||
);
|
||
return (r[0] && r[0].c) || 0;
|
||
}
|
||
|
||
async function importGpxSession(session, points) {
|
||
await execute(
|
||
`INSERT INTO gps_sessions (date, start_time, end_time, activity_type,
|
||
total_distance_m, total_kcal, avg_speed_kmh, max_speed_kmh,
|
||
elevation_gain_m, elevation_loss_m, source)
|
||
VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11)`,
|
||
[session.date, session.start_time, session.end_time, session.activity_type,
|
||
session.total_distance_m, session.total_kcal, session.avg_speed_kmh,
|
||
session.max_speed_kmh, session.elevation_gain_m, session.elevation_loss_m,
|
||
session.source ?? "gpx_import"]
|
||
);
|
||
// Get id of just-inserted session by matching unique fields.
|
||
// CAST to TEXT — numeric i64 id is corrupted by the SQL plugin; use string
|
||
// sid for point inserts (SQLite coerces it to the INTEGER session_id column).
|
||
const rows = await select(
|
||
"SELECT CAST(id AS TEXT) AS id FROM gps_sessions WHERE date=?1 AND start_time=?2 AND source=?3 ORDER BY id DESC LIMIT 1",
|
||
[session.date, session.start_time, session.source ?? "gpx_import"]
|
||
);
|
||
const sid = rows[0].id;
|
||
// Batch insert points — build multi-row INSERT to avoid 1 IPC per point
|
||
const BATCH = 50;
|
||
for (let i = 0; i < points.length; i += BATCH) {
|
||
const chunk = points.slice(i, i + BATCH);
|
||
const placeholders = [];
|
||
const vals = [];
|
||
let idx = 1;
|
||
for (const p of chunk) {
|
||
placeholders.push(`(?${idx},?${idx+1},?${idx+2},?${idx+3},?${idx+4},?${idx+5},?${idx+6})`);
|
||
vals.push(sid, p.ts, p.lat, p.lon, p.altitude ?? null, p.speed ?? null, p.seq ?? null);
|
||
idx += 7;
|
||
}
|
||
await execute(
|
||
`INSERT INTO gps_points (session_id, ts, lat, lon, altitude_gps_m, speed_ms, seq) VALUES ${placeholders.join(",")}`,
|
||
vals
|
||
);
|
||
}
|
||
return sid;
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Bikes
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getBikes() {
|
||
return select("SELECT * FROM bikes ORDER BY is_active DESC, name");
|
||
}
|
||
|
||
async function getBike(id) {
|
||
const rows = await select("SELECT * FROM bikes WHERE id = ?1", [id]);
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function getActiveBike() {
|
||
const rows = await select("SELECT * FROM bikes WHERE is_active = 1 LIMIT 1");
|
||
return rows.length ? rows[0] : null;
|
||
}
|
||
|
||
async function saveBike(data) {
|
||
if (data.is_active) {
|
||
await execute("UPDATE bikes SET is_active = 0 WHERE is_active = 1");
|
||
}
|
||
const r = await execute(
|
||
`INSERT INTO bikes (name, weight_kg, wheel_diameter_inch, chainring_teeth,
|
||
cog_teeth, gear_count, surface_type, notes, is_active, tire_pressure_bar)
|
||
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)`,
|
||
[data.name, data.weight_kg ?? null, data.wheel_diameter_inch ?? 26,
|
||
data.chainring_teeth ?? null, data.cog_teeth ?? null,
|
||
data.gear_count ?? null, data.surface_type ?? "mixed",
|
||
data.notes ?? null, data.is_active ? 1 : 0,
|
||
data.tire_pressure_bar ?? null]
|
||
);
|
||
return r.lastInsertId;
|
||
}
|
||
|
||
async function updateBike(id, data) {
|
||
if (data.is_active) {
|
||
await execute("UPDATE bikes SET is_active = 0 WHERE is_active = 1");
|
||
}
|
||
await execute(
|
||
`UPDATE bikes SET name=?1, weight_kg=?2, wheel_diameter_inch=?3,
|
||
chainring_teeth=?4, cog_teeth=?5, gear_count=?6, surface_type=?7,
|
||
notes=?8, is_active=?9, tire_pressure_bar=?10 WHERE id=?11`,
|
||
[data.name, data.weight_kg ?? null, data.wheel_diameter_inch ?? 26,
|
||
data.chainring_teeth ?? null, data.cog_teeth ?? null,
|
||
data.gear_count ?? null, data.surface_type ?? "mixed",
|
||
data.notes ?? null, data.is_active ? 1 : 0,
|
||
data.tire_pressure_bar ?? null, id]
|
||
);
|
||
}
|
||
|
||
async function deleteBike(id) {
|
||
await execute("DELETE FROM bikes WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function updateSessionBike(sessionId, bikeId) {
|
||
await execute("UPDATE gps_sessions SET bike_id = ?1 WHERE id = ?2", [bikeId, sessionId]);
|
||
}
|
||
|
||
// Store the rider's weight at ride time (set once when a recording finalises). A separate
|
||
// setter from finishGpsSession, which recompute also calls — recompute must NOT overwrite it.
|
||
async function setSessionRiderWeight(sessionId, kg) {
|
||
if (kg == null || isNaN(kg)) return;
|
||
await execute("UPDATE gps_sessions SET rider_weight_kg = ?1 WHERE id = ?2", [kg, sessionId]);
|
||
}
|
||
|
||
// HR-only "workout" session: store the heart-rate time series as JSON [[relSec,bpm],...].
|
||
async function setSessionHrSamples(sessionId, samplesJson) {
|
||
await execute("UPDATE gps_sessions SET hr_samples = ?1 WHERE id = ?2", [samplesJson, sessionId]);
|
||
}
|
||
|
||
// Bulk-set terrain altitude (altitude_srtm_m) for points by id. rows: [{id, srtm}].
|
||
// Batched CASE update — values are numbers (ids small, no i64 corruption), safe to inline.
|
||
async function updatePointsSrtm(rows) {
|
||
const BATCH = 200;
|
||
for (let i = 0; i < rows.length; i += BATCH) {
|
||
const batch = rows.slice(i, i + BATCH).filter(r => r.id != null && r.srtm != null && isFinite(r.srtm));
|
||
if (!batch.length) continue;
|
||
const cases = batch.map(r => `WHEN ${parseInt(r.id, 10)} THEN ${Number(r.srtm)}`).join(" ");
|
||
const ids = batch.map(r => parseInt(r.id, 10)).join(",");
|
||
await execute(`UPDATE gps_points SET altitude_srtm_m = CASE id ${cases} END WHERE id IN (${ids})`, []);
|
||
}
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Bike maintenance log
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getBikeMaintenanceLog(bikeId) {
|
||
return select(
|
||
"SELECT * FROM bike_maintenance WHERE bike_id = ?1 ORDER BY date DESC",
|
||
[bikeId]
|
||
);
|
||
}
|
||
|
||
async function addBikeMaintenance(bikeId, date, description, mileageKm, nextServiceKm, notes) {
|
||
const r = await execute(
|
||
`INSERT INTO bike_maintenance (bike_id, date, description, mileage_km, next_service_km, notes)
|
||
VALUES (?1, ?2, ?3, ?4, ?5, ?6)`,
|
||
[bikeId, date, description, mileageKm ?? null, nextServiceKm ?? null, notes ?? null]
|
||
);
|
||
return r.lastInsertId;
|
||
}
|
||
|
||
async function deleteBikeMaintenance(id) {
|
||
await execute("DELETE FROM bike_maintenance WHERE id = ?1", [id]);
|
||
}
|
||
|
||
async function getBikeTotalMileage(bikeId) {
|
||
const rows = await select(
|
||
"SELECT SUM(total_distance_m) as total FROM gps_sessions WHERE bike_id = ?1 AND total_distance_m IS NOT NULL",
|
||
[bikeId]
|
||
);
|
||
const totalM = rows[0]?.total ?? 0;
|
||
return Math.round(totalM / 100) / 10; // km, 1 decimal
|
||
}
|
||
|
||
async function getBikeMaintenanceDue(bikeId) {
|
||
const mileage = await getBikeTotalMileage(bikeId);
|
||
const all = await getBikeMaintenanceLog(bikeId);
|
||
return all.filter(e => e.next_service_km != null && mileage >= e.next_service_km);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Goal markers
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getGoalMarkers() {
|
||
return select("SELECT * FROM goal_markers ORDER BY created_at DESC");
|
||
}
|
||
|
||
async function addGoalMarker(name, lat, lon, notes) {
|
||
const r = await execute(
|
||
`INSERT INTO goal_markers (name, lat, lon, notes) VALUES (?1, ?2, ?3, ?4)`,
|
||
[name, lat, lon, notes ?? null]
|
||
);
|
||
return r.lastInsertId;
|
||
}
|
||
|
||
async function deleteGoalMarker(id) {
|
||
await execute("DELETE FROM goal_markers WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Nogo areas — impassable spots the router must avoid (circle: lat/lon + radius)
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getNogoAreas() {
|
||
return select("SELECT * FROM nogo_areas ORDER BY created_at DESC");
|
||
}
|
||
|
||
async function addNogoArea(lat, lon, radiusM, note) {
|
||
await execute(
|
||
"INSERT INTO nogo_areas (lat, lon, radius_m, note) VALUES (?1, ?2, ?3, ?4)",
|
||
[lat, lon, radiusM ?? 25, note ?? null]
|
||
);
|
||
let rows = await select("SELECT CAST(id AS TEXT) AS id FROM nogo_areas ORDER BY id DESC LIMIT 1");
|
||
return rows.length ? rows[0].id : null;
|
||
}
|
||
|
||
async function deleteNogoArea(id) {
|
||
await execute("DELETE FROM nogo_areas WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Saved routes (hand-drawn reference paths overlaid on the map)
|
||
// ---------------------------------------------------------------------------
|
||
async function getRoutes() {
|
||
// Routes are few and small — load the blob to report a point count, drop it.
|
||
let rows = await select("SELECT id, name, color, created_at, points_json, waypoints_json FROM routes ORDER BY created_at DESC");
|
||
return rows.map(r => {
|
||
let n = 0, hasEle = false, wpts = null;
|
||
try { let pts = JSON.parse(r.points_json) || []; n = pts.length; hasEle = pts.some(p => p.ele != null); } catch {}
|
||
// wpts = count of editable waypoints (routes built in the on-phone planner); null = imported
|
||
try { if (r.waypoints_json) { let w = JSON.parse(r.waypoints_json); wpts = (w.waypoints || w).length; } } catch {}
|
||
return { id: r.id, name: r.name, color: r.color, created_at: r.created_at, pts: n, hasEle, wpts };
|
||
});
|
||
}
|
||
|
||
async function getRoute(id) {
|
||
let rows = await select("SELECT * FROM routes WHERE id = ?1", [id]);
|
||
if (!rows.length) return null;
|
||
let r = rows[0];
|
||
let points = [];
|
||
try { points = JSON.parse(r.points_json) || []; } catch {}
|
||
// waypoints = { waypoints:[{lat,lon}], legMode:[...] } for planner-built routes; null otherwise
|
||
let waypoints = null;
|
||
try { if (r.waypoints_json) waypoints = JSON.parse(r.waypoints_json); } catch {}
|
||
return { id: r.id, name: r.name, color: r.color, created_at: r.created_at, points, waypoints };
|
||
}
|
||
|
||
async function addRoute(name, color, points, waypoints) {
|
||
await execute(
|
||
"INSERT INTO routes (name, color, points_json, waypoints_json) VALUES (?1, ?2, ?3, ?4)",
|
||
[name, color || "#e53935", JSON.stringify(points || []), waypoints ? JSON.stringify(waypoints) : null]
|
||
);
|
||
// lastInsertId comes back as an i64 the SQL plugin can corrupt by ±1 — re-read the id as
|
||
// text instead (same guard as importGpxSession), so the caller opens the right route.
|
||
let rows = await select("SELECT CAST(id AS TEXT) AS id FROM routes ORDER BY id DESC LIMIT 1");
|
||
return rows.length ? rows[0].id : null;
|
||
}
|
||
|
||
async function renameRoute(id, name) {
|
||
await execute("UPDATE routes SET name = ?1 WHERE id = ?2", [name, id]);
|
||
}
|
||
|
||
async function setRouteColor(id, color) {
|
||
await execute("UPDATE routes SET color = ?1 WHERE id = ?2", [color, id]);
|
||
}
|
||
|
||
async function deleteRoute(id) {
|
||
await execute("DELETE FROM routes WHERE id = ?1", [id]);
|
||
}
|
||
|
||
// Overwrite a route's points (used to persist fetched elevations back into
|
||
// points_json — points are {lat, lon, ele?}).
|
||
async function updateRoutePoints(id, points, waypoints) {
|
||
if (waypoints !== undefined) {
|
||
await execute("UPDATE routes SET points_json = ?1, waypoints_json = ?2 WHERE id = ?3",
|
||
[JSON.stringify(points || []), waypoints ? JSON.stringify(waypoints) : null, id]);
|
||
} else {
|
||
await execute("UPDATE routes SET points_json = ?1 WHERE id = ?2", [JSON.stringify(points || []), id]);
|
||
}
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Food cost helper — most frequently eaten foods in last 7 days
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getFrequentFoods(days) {
|
||
return select(
|
||
`SELECT product_name, kcal, amount_g,
|
||
CASE WHEN amount_g > 0 THEN ROUND(kcal * 100.0 / amount_g) ELSE kcal END as kcal_per_100g
|
||
FROM (
|
||
SELECT p.name as product_name, fl.kcal, fl.amount_g,
|
||
COUNT(*) as cnt
|
||
FROM food_log fl
|
||
JOIN products p ON fl.product_id = p.id
|
||
WHERE fl.date >= date('now', ?1)
|
||
AND fl.kcal > 0 AND fl.amount_g > 0
|
||
GROUP BY p.name
|
||
ORDER BY cnt DESC
|
||
LIMIT 5
|
||
)`,
|
||
[`-${days ?? 7} days`]
|
||
);
|
||
}
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Closest approach of a session to a goal marker
|
||
// Returns {minDist, closestIdx} — computed in JS since SQLite can't do haversine
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getGoalProgress(markerId, sessionId) {
|
||
// Fetch all sessions that got close to this marker, ordered by date
|
||
// We actually compute this in JS, but store the result concept here
|
||
// This function just returns all sessions with their closest approach to a marker
|
||
const sessions = await select(
|
||
`SELECT gs.id, gs.date, gs.start_time, gs.total_distance_m
|
||
FROM gps_sessions gs
|
||
WHERE gs.id != ?1
|
||
ORDER BY gs.date DESC, gs.start_time DESC
|
||
LIMIT 20`,
|
||
[sessionId]
|
||
);
|
||
return sessions;
|
||
}
|
||
|
||
// Expose as globals — no ES module system, loaded via <script> tags.
|
||
// main.js accesses these as window.DB.xxx
|
||
window.DB = {};
|
||
window.DB.getProfileValue = getProfileValue;
|
||
window.DB.setProfileValue = setProfileValue;
|
||
window.DB.getFullProfile = getFullProfile;
|
||
window.DB.addWeight = addWeight;
|
||
window.DB.deleteWeight = deleteWeight;
|
||
window.DB.getWeightsForDate = getWeightsForDate;
|
||
window.DB.getLatestWeight = getLatestWeight;
|
||
window.DB.getRecentWeights = getRecentWeights;
|
||
window.DB.getSleepForDate = getSleepForDate;
|
||
window.DB.saveSleep = saveSleep;
|
||
window.DB.searchProducts = searchProducts;
|
||
window.DB.getProduct = getProduct;
|
||
|
||
async function getManualProducts() {
|
||
return select("SELECT * FROM products WHERE source = 'manual' ORDER BY name");
|
||
}
|
||
window.DB.getManualProducts = getManualProducts;
|
||
window.DB.addProduct = addProduct;
|
||
window.DB.deleteProduct = deleteProduct;
|
||
window.DB.getProductRefs = getProductRefs;
|
||
window.DB.countUnlabeledUnusedProducts = countUnlabeledUnusedProducts;
|
||
window.DB.cleanupUnlabeledProducts = cleanupUnlabeledProducts;
|
||
window.DB.getFoodForDate = getFoodForDate;
|
||
window.DB.addFoodManual = addFoodManual;
|
||
window.DB.addFoodEntry = addFoodEntry;
|
||
window.DB.updateFoodEntry = updateFoodEntry;
|
||
window.DB.deleteFoodEntry = deleteFoodEntry;
|
||
window.DB.getActivitiesForDate = getActivitiesForDate;
|
||
window.DB.addActivity = addActivity;
|
||
window.DB.deleteActivity = deleteActivity;
|
||
window.DB.getSupplements = getSupplements;
|
||
window.DB.getSupplement = getSupplement;
|
||
window.DB.addSupplement = addSupplement;
|
||
window.DB.deleteSupplement = deleteSupplement;
|
||
window.DB.logSupplement = logSupplement;
|
||
window.DB.getSupplementLogForDate = getSupplementLogForDate;
|
||
window.DB.deleteSupplementLog = deleteSupplementLog;
|
||
window.DB.getSupplementTotalsForDate = getSupplementTotalsForDate;
|
||
window.DB.getFoodTotalsForDate = getFoodTotalsForDate;
|
||
window.DB.getActivityTotalsForDate = getActivityTotalsForDate;
|
||
window.DB.getServingUnits = getServingUnits;
|
||
window.DB.getUnitsForProduct = getUnitsForProduct;
|
||
window.DB.getDailyFoodTotals = getDailyFoodTotals;
|
||
window.DB.getDailyActivityTotals = getDailyActivityTotals;
|
||
window.DB.getCookingMethods = getCookingMethods;
|
||
window.DB.getCookingMethod = getCookingMethod;
|
||
window.DB.getRecipes = getRecipes;
|
||
window.DB.getRecipe = getRecipe;
|
||
window.DB.getRecipeIngredients = getRecipeIngredients;
|
||
window.DB.saveRecipe = saveRecipe;
|
||
window.DB.updateRecipe = updateRecipe;
|
||
window.DB.deleteRecipe = deleteRecipe;
|
||
window.DB.addRecipeIngredient = addRecipeIngredient;
|
||
window.DB.deleteRecipeIngredient = deleteRecipeIngredient;
|
||
window.DB.clearRecipeIngredients = clearRecipeIngredients;
|
||
window.DB.calculateRecipeNutrition = calculateRecipeNutrition;
|
||
|
||
async function cacheRecipeNutrients(recipeId) {
|
||
const nutr = await calculateRecipeNutrition(recipeId);
|
||
if (!nutr) return;
|
||
await execute("UPDATE recipes SET cached_nutrients = ?1 WHERE id = ?2",
|
||
[JSON.stringify(nutr), recipeId]);
|
||
}
|
||
window.DB.cacheRecipeNutrients = cacheRecipeNutrients;
|
||
window.DB.addFoodFromRecipe = addFoodFromRecipe;
|
||
window.DB.createGpsSession = createGpsSession;
|
||
window.DB.addGpsPoint = addGpsPoint;
|
||
window.DB.addGpsPointsBulk = addGpsPointsBulk;
|
||
window.DB.finishGpsSession = finishGpsSession;
|
||
window.DB.setSessionWindMarks = setSessionWindMarks;
|
||
window.DB.saveSmoothedTrack = saveSmoothedTrack;
|
||
window.DB.setSessionSmoothed = setSessionSmoothed;
|
||
window.DB.getGpsSessions = getGpsSessions;
|
||
window.DB.getGpsSessionsForDate = getGpsSessionsForDate;
|
||
window.DB.getGpsSession = getGpsSession;
|
||
window.DB.getGpsPoints = getGpsPoints;
|
||
window.DB.deleteGpsSession = deleteGpsSession;
|
||
window.DB.mergeGpsSessions = mergeGpsSessions;
|
||
window.DB.unmergeGpsSession = unmergeGpsSession;
|
||
window.DB.countMergedOrigins = countMergedOrigins;
|
||
window.DB.splitGpsSession = splitGpsSession;
|
||
window.DB.importGpxSession = importGpxSession;
|
||
window.DB.saveGapFill = saveGapFill;
|
||
window.DB.revertGapFill = revertGapFill;
|
||
window.DB.setWalkFlags = setWalkFlags;
|
||
|
||
async function findNearestAltitude(lat, lon, radiusM) {
|
||
const dLat = radiusM / 111000;
|
||
const dLon = radiusM / (111000 * Math.cos(lat * Math.PI / 180));
|
||
const rows = await select(`
|
||
SELECT altitude_gps_m FROM gps_points
|
||
WHERE altitude_gps_m IS NOT NULL
|
||
AND lat BETWEEN ?1 AND ?2 AND lon BETWEEN ?3 AND ?4
|
||
ORDER BY (lat - ?5)*(lat - ?5) + (lon - ?6)*(lon - ?6)
|
||
LIMIT 1`, [lat - dLat, lat + dLat, lon - dLon, lon + dLon, lat, lon]);
|
||
return rows[0]?.altitude_gps_m ?? null;
|
||
}
|
||
window.DB.findNearestAltitude = findNearestAltitude;
|
||
window.DB.getBikes = getBikes;
|
||
window.DB.getBike = getBike;
|
||
window.DB.getActiveBike = getActiveBike;
|
||
window.DB.saveBike = saveBike;
|
||
window.DB.updateBike = updateBike;
|
||
window.DB.deleteBike = deleteBike;
|
||
window.DB.updateSessionBike = updateSessionBike;
|
||
window.DB.setSessionRiderWeight = setSessionRiderWeight;
|
||
window.DB.setSessionHrSamples = setSessionHrSamples;
|
||
window.DB.updatePointsSrtm = updatePointsSrtm;
|
||
window.DB.getBikeMaintenanceLog = getBikeMaintenanceLog;
|
||
window.DB.addBikeMaintenance = addBikeMaintenance;
|
||
window.DB.deleteBikeMaintenance = deleteBikeMaintenance;
|
||
window.DB.getBikeTotalMileage = getBikeTotalMileage;
|
||
window.DB.getBikeMaintenanceDue = getBikeMaintenanceDue;
|
||
window.DB.getGoalMarkers = getGoalMarkers;
|
||
window.DB.addGoalMarker = addGoalMarker;
|
||
window.DB.deleteGoalMarker = deleteGoalMarker;
|
||
window.DB.getNogoAreas = getNogoAreas;
|
||
window.DB.addNogoArea = addNogoArea;
|
||
window.DB.deleteNogoArea = deleteNogoArea;
|
||
window.DB.getRoutes = getRoutes;
|
||
window.DB.getRoute = getRoute;
|
||
window.DB.addRoute = addRoute;
|
||
window.DB.renameRoute = renameRoute;
|
||
window.DB.setRouteColor = setRouteColor;
|
||
window.DB.deleteRoute = deleteRoute;
|
||
window.DB.updateRoutePoints = updateRoutePoints;
|
||
window.DB.getFrequentFoods = getFrequentFoods;
|
||
window.DB.getGoalProgress = getGoalProgress;
|
||
|
||
// ---------------------------------------------------------------------------
|
||
// Nutrient reserves (accumulated body stores)
|
||
// ---------------------------------------------------------------------------
|
||
|
||
async function getReserves(date) {
|
||
const rows = await select("SELECT reserves FROM nutrient_reserves WHERE date = ?1", [date]);
|
||
if (!rows.length || !rows[0].reserves) return {};
|
||
try { return JSON.parse(rows[0].reserves); } catch { return {}; }
|
||
}
|
||
|
||
async function saveReserves(date, reserves) {
|
||
await execute(
|
||
`INSERT INTO nutrient_reserves (date, reserves) VALUES (?1, ?2)
|
||
ON CONFLICT(date) DO UPDATE SET reserves = ?2`,
|
||
[date, JSON.stringify(reserves)]
|
||
);
|
||
}
|
||
|
||
async function getAllReservesDates() {
|
||
return select("SELECT date FROM nutrient_reserves ORDER BY date");
|
||
}
|
||
|
||
async function getDaysWithFood() {
|
||
return select(
|
||
`SELECT DISTINCT date FROM (
|
||
SELECT date FROM food_log
|
||
UNION SELECT date FROM supplement_log
|
||
) ORDER BY date`
|
||
);
|
||
}
|
||
|
||
async function getDailyWaterMl(date) {
|
||
// From products with water_pct
|
||
const prodRows = await select(
|
||
`SELECT COALESCE(SUM(fl.amount_g * p.water_pct / 100.0), 0) as ml
|
||
FROM food_log fl JOIN products p ON fl.product_id = p.id
|
||
WHERE fl.date = ?1 AND p.water_pct IS NOT NULL AND p.water_pct > 0`,
|
||
[date]
|
||
);
|
||
let total = prodRows[0]?.ml || 0;
|
||
// From recipes — calculate water from ingredients on-the-fly (cache may be stale)
|
||
const recipeRows = await select(
|
||
`SELECT fl.amount_g, fl.recipe_id, r.servings,
|
||
(SELECT SUM(ri.amount_g) FROM recipe_ingredients ri WHERE ri.recipe_id = r.id) as recipe_weight
|
||
FROM food_log fl JOIN recipes r ON fl.recipe_id = r.id
|
||
WHERE fl.date = ?1`,
|
||
[date]
|
||
);
|
||
for (const rr of recipeRows) {
|
||
try {
|
||
// Sum water from recipe ingredients
|
||
const ings = await select(
|
||
`SELECT ri.amount_g, COALESCE(p.water_pct, 0) as water_pct
|
||
FROM recipe_ingredients ri LEFT JOIN products p ON ri.product_id = p.id
|
||
WHERE ri.recipe_id = ?1`,
|
||
[rr.recipe_id]
|
||
);
|
||
let recipeWaterMl = 0;
|
||
for (const ing of ings) {
|
||
recipeWaterMl += (ing.amount_g || 0) * (ing.water_pct || 0) / 100;
|
||
}
|
||
// Scale by portion: how much of the recipe was eaten
|
||
const servingG = Math.round((rr.recipe_weight || 0) / (rr.servings || 1));
|
||
const scale = servingG > 0 ? rr.amount_g / servingG : 1;
|
||
total += recipeWaterMl / (rr.servings || 1) * scale;
|
||
} catch {}
|
||
}
|
||
return Math.round(total);
|
||
}
|
||
|
||
window.DB.getReserves = getReserves;
|
||
window.DB.saveReserves = saveReserves;
|
||
window.DB.getAllReservesDates = getAllReservesDates;
|
||
window.DB.getDaysWithFood = getDaysWithFood;
|
||
window.DB.getDailyWaterMl = getDailyWaterMl;
|
||
|
||
// All GPS tracks as arrays of [lat, lon] grouped by session_id.
|
||
// minSpeedMs filters out stopped/slow points. minPoints filters out tiny sessions.
|
||
// Fetches per-session to avoid Tauri SQL plugin row limit.
|
||
// Road points — all GPS points merged into one flat table.
|
||
// Rebuild: INSERT...SELECT runs entirely in SQLite (no JS iteration, no ID issues).
|
||
// Reading: paginated to avoid Tauri SQL plugin row limit (~28K).
|
||
|
||
async function rebuildRoads() {
|
||
const invoke = window.__TAURI__.core.invoke;
|
||
const raw = await invoke("rebuild_roads", { minSpeedMs: 0.5 });
|
||
const resp = JSON.parse(raw);
|
||
console.log("[rebuildRoads]", resp);
|
||
return resp.road_points;
|
||
}
|
||
|
||
// Read road points via Rust command (Tauri SQL plugin has ~28K row limit)
|
||
async function getRoadPoints() {
|
||
const invoke = window.__TAURI__.core.invoke;
|
||
const BATCH = 10000;
|
||
let offset = 0;
|
||
const all = [];
|
||
while (true) {
|
||
const raw = await invoke("get_road_points", { offset, batchSize: BATCH });
|
||
const resp = JSON.parse(raw);
|
||
for (const p of resp.points) all.push({ lat: p[0], lon: p[1], atype: p[2] || "b", passes: p[3] || 1 });
|
||
console.log(`[getRoadPoints] batch offset=${offset} got=${resp.points.length} total_db=${resp.total}`);
|
||
if (!resp.has_more) break;
|
||
offset += BATCH;
|
||
}
|
||
console.log(`[getRoadPoints] total=${all.length}`);
|
||
return all;
|
||
}
|
||
|
||
window.DB.rebuildRoads = rebuildRoads;
|
||
window.DB.getRoadPoints = getRoadPoints;
|
||
|
||
// JS-side migrations — safe for any DB state (checks before altering)
|
||
async function runMigrations() {
|
||
// Disable WAL — all writes go directly to main DB file (no -wal/-shm files)
|
||
await execute("PRAGMA journal_mode=DELETE");
|
||
|
||
// Sync columns: sync_id (UUID), updated_at (ISO timestamp), sync_dirty (0/1)
|
||
const syncTables = ["food_log","weight_log","supplement_log","sleep_log","activity_log",
|
||
"gps_sessions","recipes","recipe_ingredients","supplements"];
|
||
for (const tbl of syncTables) {
|
||
const cols = (await select(`PRAGMA table_info(${tbl})`)).map(r => r.name);
|
||
if (!cols.includes("sync_id")) {
|
||
await execute(`ALTER TABLE ${tbl} ADD COLUMN sync_id TEXT`);
|
||
await execute(`ALTER TABLE ${tbl} ADD COLUMN updated_at TEXT`);
|
||
await execute(`ALTER TABLE ${tbl} ADD COLUMN sync_dirty INTEGER DEFAULT 0`);
|
||
// Backfill sync_id for existing rows
|
||
await execute(`UPDATE ${tbl} SET sync_id = lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(6))) WHERE sync_id IS NULL`);
|
||
await execute(`UPDATE ${tbl} SET updated_at = datetime('now') WHERE updated_at IS NULL`);
|
||
}
|
||
// Triggers: auto-set sync_id, updated_at, sync_dirty on INSERT/UPDATE
|
||
await execute(`CREATE TRIGGER IF NOT EXISTS ${tbl}_sync_insert
|
||
AFTER INSERT ON ${tbl} WHEN NEW.sync_id IS NULL
|
||
BEGIN UPDATE ${tbl} SET
|
||
sync_id = lower(hex(randomblob(4))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(6))),
|
||
updated_at = datetime('now'), sync_dirty = 1
|
||
WHERE rowid = NEW.rowid; END`);
|
||
await execute(`CREATE TRIGGER IF NOT EXISTS ${tbl}_sync_update
|
||
AFTER UPDATE ON ${tbl} WHEN NEW.sync_dirty != 0 OR OLD.sync_dirty = 0
|
||
BEGIN UPDATE ${tbl} SET
|
||
updated_at = datetime('now'), sync_dirty = 1
|
||
WHERE rowid = NEW.rowid AND sync_dirty = 0; END`);
|
||
// First sync: mark all existing rows as dirty so they get pushed
|
||
const dirtyCount = (await select(`SELECT COUNT(*) as c FROM ${tbl} WHERE sync_dirty = 1`))[0].c;
|
||
if (dirtyCount === 0) {
|
||
const total = (await select(`SELECT COUNT(*) as c FROM ${tbl}`))[0].c;
|
||
if (total > 0) {
|
||
await execute(`UPDATE ${tbl} SET sync_dirty = 1`);
|
||
console.log(`[Sync] Marked ${total} rows in ${tbl} as dirty for first sync`);
|
||
}
|
||
}
|
||
}
|
||
|
||
// Batch column checks — one PRAGMA per table instead of N separate IPC calls
|
||
const productCols = (await select("PRAGMA table_info(products)")).map(r => r.name);
|
||
const addProdCol = async (col, type) => { if (!productCols.includes(col)) await execute(`ALTER TABLE products ADD COLUMN ${col} ${type}`); };
|
||
await addProdCol("composition", "TEXT");
|
||
await addProdCol("name_lower", "TEXT");
|
||
await addProdCol("nutrients_guessed", "INTEGER DEFAULT 0");
|
||
await addProdCol("user_edited", "INTEGER DEFAULT 0");
|
||
await addProdCol("ginseng_mg", "REAL");
|
||
await addProdCol("resveratrol_mg", "REAL");
|
||
await addProdCol("coq10_mg", "REAL");
|
||
|
||
// New supplement columns (ginseng, resveratrol, coq10) — added after existing suppCols migration below
|
||
|
||
const gpsCols = (await select("PRAGMA table_info(gps_points)")).map(r => r.name);
|
||
if (!gpsCols.includes("seq")) await execute("ALTER TABLE gps_points ADD COLUMN seq INTEGER");
|
||
if (!gpsCols.includes("spo2")) await execute("ALTER TABLE gps_points ADD COLUMN spo2 INTEGER");
|
||
if (!gpsCols.includes("bp_sys")) await execute("ALTER TABLE gps_points ADD COLUMN bp_sys INTEGER");
|
||
if (!gpsCols.includes("bp_dia")) await execute("ALTER TABLE gps_points ADD COLUMN bp_dia INTEGER");
|
||
if (!gpsCols.includes("accel_rms")) await execute("ALTER TABLE gps_points ADD COLUMN accel_rms REAL");
|
||
if (!gpsCols.includes("accel_max")) await execute("ALTER TABLE gps_points ADD COLUMN accel_max REAL");
|
||
if (!gpsCols.includes("gyro_pitch")) await execute("ALTER TABLE gps_points ADD COLUMN gyro_pitch REAL");
|
||
if (!gpsCols.includes("gyro_yaw")) await execute("ALTER TABLE gps_points ADD COLUMN gyro_yaw REAL");
|
||
if (!gpsCols.includes("bearing_deg")) await execute("ALTER TABLE gps_points ADD COLUMN bearing_deg REAL");
|
||
if (!gpsCols.includes("accuracy_m")) await execute("ALTER TABLE gps_points ADD COLUMN accuracy_m REAL");
|
||
if (!gpsCols.includes("altitude_baro_m")) await execute("ALTER TABLE gps_points ADD COLUMN altitude_baro_m REAL"); // barometric altitude from the BLE sensor (null without it)
|
||
if (!gpsCols.includes("lat_smoothed")) await execute("ALTER TABLE gps_points ADD COLUMN lat_smoothed REAL");
|
||
if (!gpsCols.includes("lon_smoothed")) await execute("ALTER TABLE gps_points ADD COLUMN lon_smoothed REAL");
|
||
if (!gpsCols.includes("estimated")) await execute("ALTER TABLE gps_points ADD COLUMN estimated INTEGER DEFAULT 0"); // gap-fill (BRouter) points, reversible
|
||
if (!gpsCols.includes("walk")) await execute("ALTER TABLE gps_points ADD COLUMN walk INTEGER DEFAULT 0"); // point is in a "walking" run inside a ride (excluded from ridden roads)
|
||
if (!gpsCols.includes("orig_session_id")) await execute("ALTER TABLE gps_points ADD COLUMN orig_session_id TEXT"); // set on merge = the session this point first belonged to (lets a future split undo the merge)
|
||
|
||
// --- Cooking methods table ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS cooking_methods (
|
||
id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
protein_loss_pct REAL DEFAULT 0,
|
||
fat_loss_pct REAL DEFAULT 0,
|
||
carbs_loss_pct REAL DEFAULT 0,
|
||
fiber_loss_pct REAL DEFAULT 0,
|
||
vit_a_loss_pct REAL DEFAULT 0,
|
||
vit_c_loss_pct REAL DEFAULT 0,
|
||
vit_b1_loss_pct REAL DEFAULT 0,
|
||
vit_b2_loss_pct REAL DEFAULT 0,
|
||
kcal_loss_pct REAL DEFAULT 0
|
||
)`);
|
||
|
||
// Seed cooking methods if empty
|
||
const cmCount = await select("SELECT COUNT(*) as cnt FROM cooking_methods");
|
||
if (cmCount[0].cnt === 0) {
|
||
const methods = [
|
||
[1, "Без обработки", 0, 0, 0, 0, 0, 0, 0, 0, 0],
|
||
[2, "Варка", 2, 5, 3, 0, 0, 50, 30, 10, 0],
|
||
[3, "Жарка", 5, -10, 3, 0, 0, 60, 35, 0, 0],
|
||
[4, "Тушение", 2, 10, 2, 0, 0, 40, 25, 0, 0],
|
||
[5, "Запекание", 3, 8, 2, 0, 0, 45, 20, 0, 0],
|
||
[6, "На пару", 1, 2, 1, 0, 0, 25, 15, 0, 0],
|
||
];
|
||
for (const m of methods) {
|
||
await execute(
|
||
`INSERT INTO cooking_methods (id, name, protein_loss_pct, fat_loss_pct,
|
||
carbs_loss_pct, fiber_loss_pct, vit_a_loss_pct, vit_c_loss_pct,
|
||
vit_b1_loss_pct, vit_b2_loss_pct, kcal_loss_pct)
|
||
VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11)`,
|
||
m
|
||
);
|
||
}
|
||
console.log("[db.js] Seeded 6 cooking methods");
|
||
}
|
||
|
||
// --- Recipes + ingredients: batch column checks ---
|
||
const recipeCols = (await select("PRAGMA table_info(recipes)")).map(r => r.name);
|
||
if (!recipeCols.includes("cooking_method_id")) await execute("ALTER TABLE recipes ADD COLUMN cooking_method_id INTEGER");
|
||
if (!recipeCols.includes("created_at")) await execute("ALTER TABLE recipes ADD COLUMN created_at TEXT DEFAULT (datetime('now'))");
|
||
|
||
const riCols = (await select("PRAGMA table_info(recipe_ingredients)")).map(r => r.name);
|
||
if (!riCols.includes("custom_name")) await execute("ALTER TABLE recipe_ingredients ADD COLUMN custom_name TEXT");
|
||
if (!riCols.includes("cooking_method_id")) await execute("ALTER TABLE recipe_ingredients ADD COLUMN cooking_method_id INTEGER");
|
||
|
||
// --- Serving units table ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS serving_units (
|
||
id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
name_short TEXT,
|
||
grams_equivalent REAL,
|
||
category TEXT DEFAULT 'universal'
|
||
)`);
|
||
|
||
// --- Product units junction table ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS product_units (
|
||
id INTEGER PRIMARY KEY,
|
||
product_id INTEGER NOT NULL REFERENCES products(id),
|
||
unit_id INTEGER NOT NULL REFERENCES serving_units(id),
|
||
grams_per_unit REAL NOT NULL,
|
||
UNIQUE(product_id, unit_id)
|
||
)`);
|
||
|
||
// --- Add unit columns to food_log ---
|
||
const flCols = (await select("PRAGMA table_info(food_log)")).map(r => r.name);
|
||
if (!flCols.includes("unit_id")) await execute("ALTER TABLE food_log ADD COLUMN unit_id INTEGER");
|
||
if (!flCols.includes("unit_amount")) await execute("ALTER TABLE food_log ADD COLUMN unit_amount REAL");
|
||
|
||
// Seed serving_units if empty
|
||
const suCnt = await select("SELECT COUNT(*) as cnt FROM serving_units");
|
||
if (suCnt[0].cnt === 0) {
|
||
const units = [
|
||
[1, "шт", "шт", null, "product_specific"],
|
||
[2, "ст.л.", "ст.л.", 15, "universal"],
|
||
[3, "ч.л.", "ч.л.", 5, "universal"],
|
||
[4, "стакан", "стак.", 250, "universal"],
|
||
[5, "мл", "мл", 1, "universal"],
|
||
];
|
||
for (const u of units) {
|
||
await execute(
|
||
`INSERT INTO serving_units (id, name, name_short, grams_equivalent, category)
|
||
VALUES (?1, ?2, ?3, ?4, ?5)`, u
|
||
);
|
||
}
|
||
console.log("[db.js] Seeded 5 serving units");
|
||
}
|
||
|
||
// product_units: user adds per-product unit mappings manually via UI.
|
||
// No auto-seeding — product names too varied for reliable matching.
|
||
|
||
// --- Bikes table ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS bikes (
|
||
id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
weight_kg REAL,
|
||
wheel_diameter_inch REAL,
|
||
chainring_teeth INTEGER,
|
||
cog_teeth INTEGER,
|
||
gear_count INTEGER,
|
||
surface_type TEXT DEFAULT 'mixed',
|
||
notes TEXT,
|
||
is_active INTEGER DEFAULT 0
|
||
)`);
|
||
|
||
// Add bike_id column to gps_sessions
|
||
const gsCols = (await select("PRAGMA table_info(gps_sessions)")).map(r => r.name);
|
||
if (!gsCols.includes("bike_id")) await execute("ALTER TABLE gps_sessions ADD COLUMN bike_id INTEGER");
|
||
// Track de-jitter: reversible smoothing (points keep both original lat/lon and
|
||
// lat_smoothed/lon_smoothed; `smoothed`=1 means the smoothed track is active).
|
||
if (!gsCols.includes("smoothed")) await execute("ALTER TABLE gps_sessions ADD COLUMN smoothed INTEGER DEFAULT 0");
|
||
if (!gsCols.includes("smooth_window")) await execute("ALTER TABLE gps_sessions ADD COLUMN smooth_window INTEGER");
|
||
if (!gsCols.includes("smooth_iters")) await execute("ALTER TABLE gps_sessions ADD COLUMN smooth_iters INTEGER");
|
||
if (!gsCols.includes("pauses")) await execute("ALTER TABLE gps_sessions ADD COLUMN pauses TEXT"); // JSON [{startTs,endTs,...}] manual pauses
|
||
if (!gsCols.includes("wind_marks")) await execute("ALTER TABLE gps_sessions ADD COLUMN wind_marks TEXT"); // JSON [{startTs,endTs}] headwind labels (ground truth for wind estimate)
|
||
if (!gsCols.includes("gap_filled")) await execute("ALTER TABLE gps_sessions ADD COLUMN gap_filled INTEGER DEFAULT 0"); // BRouter gap-fill applied (reversible)
|
||
if (!gsCols.includes("rider_weight_kg")) await execute("ALTER TABLE gps_sessions ADD COLUMN rider_weight_kg REAL"); // rider weight at ride time; recompute uses it (NULL on old sessions -> latest weigh-in)
|
||
if (!gsCols.includes("hr_samples")) await execute("ALTER TABLE gps_sessions ADD COLUMN hr_samples TEXT"); // HR-only "workout" sessions (no GPS): JSON [[relSec,bpm],...]
|
||
|
||
// Add tire_pressure_bar to bikes (for Crr correction by tire pressure)
|
||
const bikeCols = (await select("PRAGMA table_info(bikes)")).map(r => r.name);
|
||
if (!bikeCols.includes("tire_pressure_bar")) await execute("ALTER TABLE bikes ADD COLUMN tire_pressure_bar REAL");
|
||
|
||
// --- Bike maintenance log table ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS bike_maintenance (
|
||
id INTEGER PRIMARY KEY,
|
||
bike_id INTEGER NOT NULL REFERENCES bikes(id) ON DELETE CASCADE,
|
||
date TEXT NOT NULL,
|
||
description TEXT NOT NULL,
|
||
mileage_km REAL,
|
||
next_service_km REAL,
|
||
notes TEXT
|
||
)`);
|
||
|
||
// Seed default bike if table is empty
|
||
const bikeCnt = await select("SELECT COUNT(*) as cnt FROM bikes");
|
||
if (bikeCnt[0].cnt === 0) {
|
||
await execute(
|
||
`INSERT INTO bikes (name, weight_kg, wheel_diameter_inch, chainring_teeth,
|
||
cog_teeth, surface_type, is_active)
|
||
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)`,
|
||
["Мой велосипед", 12, 26, 42, 18, "mixed", 1]
|
||
);
|
||
console.log("[db.js] Seeded default bike");
|
||
}
|
||
|
||
// --- Goal markers table ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS goal_markers (
|
||
id INTEGER PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
lat REAL NOT NULL,
|
||
lon REAL NOT NULL,
|
||
notes TEXT,
|
||
created_at TEXT DEFAULT (datetime('now'))
|
||
)`);
|
||
|
||
// --- Nogo areas (impassable spots the router avoids) ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS nogo_areas (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
lat REAL NOT NULL,
|
||
lon REAL NOT NULL,
|
||
radius_m REAL DEFAULT 25,
|
||
note TEXT,
|
||
created_at TEXT DEFAULT (datetime('now'))
|
||
)`);
|
||
|
||
// --- Saved routes (hand-drawn reference paths overlaid on the map) ---
|
||
await execute(`CREATE TABLE IF NOT EXISTS routes (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
color TEXT DEFAULT '#e53935',
|
||
points_json TEXT NOT NULL,
|
||
created_at TEXT DEFAULT (datetime('now'))
|
||
)`);
|
||
// Planner-built routes also store their editable waypoints + per-leg mode, so editing
|
||
// shows 5-6 points instead of the dense routed geometry. Imported routes leave it null.
|
||
let routesCols = (await select("PRAGMA table_info(routes)")).map(r => r.name);
|
||
if (!routesCols.includes("waypoints_json")) await execute("ALTER TABLE routes ADD COLUMN waypoints_json TEXT");
|
||
|
||
// --- Supplement columns migration: add missing micronutrient columns ---
|
||
const suppCols = (await select("PRAGMA table_info(supplements)")).map(r => r.name);
|
||
const addSuppCol = async (col, type) => { if (!suppCols.includes(col)) await execute(`ALTER TABLE supplements ADD COLUMN ${col} ${type}`); };
|
||
await addSuppCol("vit_b5_mg", "REAL");
|
||
await addSuppCol("vit_b7_mcg", "REAL"); // biotin
|
||
await addSuppCol("chromium_mg", "REAL");
|
||
await addSuppCol("manganese_mg", "REAL");
|
||
await addSuppCol("molybdenum_mcg", "REAL");
|
||
await addSuppCol("iodine_mg", "REAL");
|
||
await addSuppCol("phosphorus_mg", "REAL");
|
||
await addSuppCol("silicon_mg", "REAL"); // кремний — no RDI
|
||
await addSuppCol("vanadium_mg", "REAL"); // ванадий — no RDI
|
||
await addSuppCol("lutein_mg", "REAL"); // лютеин — no RDI
|
||
await addSuppCol("lycopene_mg", "REAL"); // ликопин — no RDI
|
||
await addSuppCol("carotene_mcg", "REAL"); // бета-каротин
|
||
await addSuppCol("fish_oil_mg", "REAL"); // рыбий жир (общий объём)
|
||
await addSuppCol("omega3_mg", "REAL"); // омега-3 общая
|
||
await addSuppCol("epa_mg", "REAL"); // EPA (эйкозапентаеновая)
|
||
await addSuppCol("dha_mg", "REAL"); // DHA (докозагексаеновая)
|
||
await addSuppCol("omega6_mg", "REAL"); // омега-6
|
||
await addSuppCol("omega9_mg", "REAL"); // омега-9
|
||
await addSuppCol("ginseng_mg", "REAL"); // женьшень
|
||
await addSuppCol("resveratrol_mg", "REAL"); // ресвератрол
|
||
await addSuppCol("coq10_mg", "REAL"); // коэнзим Q10
|
||
|
||
// Product dedup handled in Rust import_seed_products (UNIQUE INDEX on name_lower+source+store_id)
|
||
|
||
// RDI standards tables
|
||
await execute(`CREATE TABLE IF NOT EXISTS rdi_standards (
|
||
id INTEGER PRIMARY KEY,
|
||
code TEXT NOT NULL UNIQUE,
|
||
name TEXT NOT NULL
|
||
)`);
|
||
await execute(`CREATE TABLE IF NOT EXISTS rdi_values (
|
||
id INTEGER PRIMARY KEY,
|
||
standard_id INTEGER NOT NULL REFERENCES rdi_standards(id),
|
||
nutrient_key TEXT NOT NULL,
|
||
value REAL NOT NULL,
|
||
UNIQUE(standard_id, nutrient_key)
|
||
)`);
|
||
|
||
// Seed RDI standards if empty
|
||
const rdiCount = await select("SELECT COUNT(*) as cnt FROM rdi_standards");
|
||
if (rdiCount[0].cnt === 0) {
|
||
await execute("INSERT INTO rdi_standards (id, code, name) VALUES (1, 'RU', 'РФ (ТР ТС 022/2011)')");
|
||
await execute("INSERT INTO rdi_standards (id, code, name) VALUES (2, 'EU', 'ЕС (EU RDA)')");
|
||
await execute("INSERT INTO rdi_standards (id, code, name) VALUES (3, 'US', 'США (US DV)')");
|
||
|
||
const standards = {
|
||
// nutrient_key: [RU, EU, US]
|
||
vit_a_mcg: [800, 800, 900],
|
||
vit_c_mg: [60, 80, 90],
|
||
vit_e_mg: [10, 12, 15],
|
||
vit_b1_mg: [1.4, 1.1, 1.2],
|
||
vit_b2_mg: [1.6, 1.4, 1.3],
|
||
vit_b3_mg: [18, 16, 16],
|
||
vit_b5_mg: [6, 6, 5],
|
||
vit_b6_mg: [2, 1.4, 1.7],
|
||
vit_b7_mcg: [50, 50, 30],
|
||
folate_mcg: [200, 200, 400],
|
||
vit_b12_mcg: [1, 2.5, 2.4],
|
||
vit_d_mcg: [5, 15, 15],
|
||
vit_k_mcg: [120, 75, 120],
|
||
calcium_mg: [1000, 800, 1000],
|
||
magnesium_mg: [400, 375, 420],
|
||
zinc_mg: [15, 10, 11],
|
||
iron_mg: [14, 14, 18],
|
||
iodine_mg: [0.15, 0.15, 0.15],
|
||
selenium_mcg: [70, 55, 55],
|
||
molybdenum_mcg: [70, 50, 45],
|
||
manganese_mg: [2, 2, 2.3],
|
||
copper_mg: [1, 1, 0.9],
|
||
chromium_mg: [0.05, 0.04, 0.035],
|
||
phosphorus_mg: [800, 700, 700],
|
||
potassium_mg: [3500, 2000, 2600],
|
||
sodium_mg: [2400, 2400, 2300],
|
||
};
|
||
for (const [key, vals] of Object.entries(standards)) {
|
||
for (let i = 0; i < 3; i++) {
|
||
await execute("INSERT INTO rdi_values (standard_id, nutrient_key, value) VALUES (?1, ?2, ?3)", [i + 1, key, vals[i]]);
|
||
}
|
||
}
|
||
console.log("[db.js] Seeded 3 RDI standards (RU/EU/US)");
|
||
}
|
||
|
||
// Add norm_type to rdi_values (rdi vs aup)
|
||
const rdiValCols = (await select("PRAGMA table_info(rdi_values)")).map(r => r.name);
|
||
if (!rdiValCols.includes("norm_type")) {
|
||
await execute("ALTER TABLE rdi_values ADD COLUMN norm_type TEXT DEFAULT 'rdi'");
|
||
}
|
||
|
||
// Seed АУП values (one-time, check if omega3 already exists)
|
||
const hasOmega3 = await select("SELECT COUNT(*) as cnt FROM rdi_values WHERE nutrient_key = 'omega3_mg'");
|
||
if (hasOmega3[0].cnt === 0) {
|
||
const aupData = {
|
||
// key: [RU, EU, US] — all marked as 'aup'
|
||
omega3_mg: [1000, 250, 1600],
|
||
epa_mg: [250, 250, 500],
|
||
dha_mg: [250, 250, 500],
|
||
lutein_mg: [5, 10, 6],
|
||
lycopene_mg: [5, 5, 5],
|
||
silicon_mg: [30, 30, 30],
|
||
vanadium_mg: [0.04, 0.04, 0.04],
|
||
};
|
||
for (const [key, vals] of Object.entries(aupData)) {
|
||
for (let i = 0; i < 3; i++) {
|
||
await execute("INSERT OR IGNORE INTO rdi_values (standard_id, nutrient_key, value, norm_type) VALUES (?1, ?2, ?3, 'aup')", [i + 1, key, vals[i]]);
|
||
}
|
||
}
|
||
console.log("[db.js] Seeded АУП values for omega/lutein/lycopene/silicon/vanadium");
|
||
}
|
||
|
||
// Nutrient half-life table (whole-body, in days)
|
||
await execute(`CREATE TABLE IF NOT EXISTS nutrient_halflife (
|
||
nutrient_key TEXT PRIMARY KEY,
|
||
halflife_min_days REAL,
|
||
halflife_max_days REAL,
|
||
halflife_avg_days REAL,
|
||
source TEXT
|
||
)`);
|
||
|
||
const hlCount = await select("SELECT COUNT(*) as cnt FROM nutrient_halflife");
|
||
if (hlCount[0].cnt === 0) {
|
||
const hl = [
|
||
["vit_a_mcg", 50, 180, 115, "fat-soluble, liver stores"],
|
||
["vit_c_mg", 10, 20, 15, "Performance Lab review"],
|
||
["vit_d_mcg", 30, 90, 60, "fat tissue, PMC4207933"],
|
||
["vit_e_mg", 2, 5, 3.5, "ZIO review"],
|
||
["vit_k_mcg", 1, 3, 1.5, "K1 pool turnover"],
|
||
["vit_b1_mg", 9, 18, 13.5, "Ariaey-Nejad 1970"],
|
||
["vit_b2_mg", 2, 7, 4, "limited storage"],
|
||
["vit_b3_mg", 0.5, 2, 1, "rapid excretion"],
|
||
["vit_b5_mg", 0.5, 2, 1, "rapid excretion"],
|
||
["vit_b6_mg", 15, 20, 17.5, "muscle stores"],
|
||
["vit_b7_mcg", 1, 3, 2, "tissue stores > plasma"],
|
||
["folate_mcg", 60, 120, 100, "RBC folate ~8 weeks"],
|
||
["vit_b12_mcg", 200, 770, 400, "liver, PubMed 14010994"],
|
||
["calcium_mg", 1, 10, 5, "fast exchangeable pool"],
|
||
["magnesium_mg", 30, 60, 42, "~1000 hours"],
|
||
["iron_mg", 2000, 5000, 3650, "~10 years, no excretion"],
|
||
["zinc_mg", 12, 30, 20, "fast pool"],
|
||
["copper_mg", 13, 33, 23, "NCBI/PMC review"],
|
||
["selenium_mcg", 50, 150, 100, "Griffith 1976"],
|
||
["iodine_mg", 40, 80, 66, "ICRP/measured"],
|
||
["manganese_mg", 20, 50, 37, "clinical data"],
|
||
["molybdenum_mcg", 1, 3, 2, "plasma ~24-51h"],
|
||
["chromium_mg", 300, 900, 616, "ICRP"],
|
||
["phosphorus_mg", 14, 60, 30, "soft tissue pool"],
|
||
["potassium_mg", 10, 28, 16, "K-42 tracer study"],
|
||
["sodium_mg", 7, 15, 11, "ICRP"],
|
||
["omega3_mg", 30, 90, 60, "fat-soluble stored"],
|
||
["epa_mg", 30, 90, 60, "same pool as omega3"],
|
||
["dha_mg", 30, 90, 60, "same pool as omega3"],
|
||
["carotene_mcg", 5, 30, 15, "fat tissue"],
|
||
["lutein_mg", 10, 30, 20, "macular pigment"],
|
||
["lycopene_mg", 14, 28, 21, "fat tissue"],
|
||
];
|
||
for (const [key, min, max, avg, src] of hl) {
|
||
await execute("INSERT INTO nutrient_halflife VALUES (?1,?2,?3,?4,?5)", [key, min, max, avg, src]);
|
||
}
|
||
console.log("[db.js] Seeded nutrient half-life data (32 nutrients)");
|
||
}
|
||
|
||
// Nutrient reserves table — accumulated body stores per day
|
||
await execute(`CREATE TABLE IF NOT EXISTS nutrient_reserves (
|
||
date TEXT PRIMARY KEY,
|
||
reserves TEXT
|
||
)`);
|
||
|
||
// Road points — deduplicated points from all GPS tracks
|
||
await execute(`CREATE TABLE IF NOT EXISTS road_points (
|
||
id INTEGER PRIMARY KEY,
|
||
lat REAL NOT NULL,
|
||
lon REAL NOT NULL
|
||
)`);
|
||
// Spatial index for fast 2m proximity checks
|
||
await execute(`CREATE INDEX IF NOT EXISTS idx_road_lat_lon ON road_points(lat, lon)`);
|
||
|
||
// Activity types: extend activity_log with structured fields
|
||
const actCols = (await select("PRAGMA table_info(activity_log)")).map(c => c.name);
|
||
if (!actCols.includes("activity_type")) {
|
||
await execute("ALTER TABLE activity_log ADD COLUMN activity_type TEXT");
|
||
await execute("UPDATE activity_log SET activity_type = 'manual' WHERE activity_type IS NULL");
|
||
}
|
||
if (!actCols.includes("sets")) await execute("ALTER TABLE activity_log ADD COLUMN sets INTEGER");
|
||
if (!actCols.includes("reps")) await execute("ALTER TABLE activity_log ADD COLUMN reps INTEGER");
|
||
if (!actCols.includes("distance_m")) await execute("ALTER TABLE activity_log ADD COLUMN distance_m REAL");
|
||
|
||
// Water content column for hydration tracking
|
||
if (!productCols.includes("water_pct")) {
|
||
await execute("ALTER TABLE products ADD COLUMN water_pct REAL");
|
||
// Seed water_pct for known liquid products by name pattern
|
||
const waterPatterns = [
|
||
[100, ["% вода %", "% вода", "вода %", "вода питьевая%", "вода минеральная%", "вода газированная%"]],
|
||
[99, ["%чай %", "%чай", "чай %"]],
|
||
[99, ["%кофе %", "%кофе", "кофе %"]],
|
||
[87, ["%молоко %", "%молоко", "молоко %", "%кефир%", "%ряженка%", "%простокваша%", "%ацидофилин%"]],
|
||
[88, ["%сок %", "%сок", "сок %", "%нектар%"]],
|
||
[85, ["%компот%", "%морс%", "%кисель%", "%какао%напит%"]],
|
||
[90, ["%суп %", "%суп", "суп %", "%бульон%", "%борщ%", "%щи %", "%солянка%", "%рассольник%", "%уха %", "%окрошка%"]],
|
||
[89, ["%газировка%", "%кола %", "%кола", "%фанта%", "%спрайт%", "%лимонад%", "%тархун%", "%байкал%", "%напиток газ%"]],
|
||
[92, ["%пиво %", "%пиво", "пиво %", "%пивн%"]],
|
||
[93, ["%квас%"]],
|
||
[80, ["%смузи%"]],
|
||
];
|
||
for (const [pct, patterns] of waterPatterns) {
|
||
for (const p of patterns) {
|
||
await execute(`UPDATE products SET water_pct = ?1 WHERE water_pct IS NULL AND name_lower LIKE ?2`, [pct, p]);
|
||
}
|
||
}
|
||
const waterCount = (await select("SELECT COUNT(*) as cnt FROM products WHERE water_pct IS NOT NULL"))[0].cnt;
|
||
console.log(`[db.js] Seeded water_pct for ${waterCount} products`);
|
||
}
|
||
|
||
// Recipe cached nutrients columns
|
||
const recipeCols2 = (await select("PRAGMA table_info(recipes)")).map(r => r.name);
|
||
if (!recipeCols2.includes("cached_nutrients")) {
|
||
await execute("ALTER TABLE recipes ADD COLUMN cached_nutrients TEXT");
|
||
}
|
||
|
||
console.log("[db.js] recipe migrations done, checking seq repair...");
|
||
|
||
// Repair old tracks: assign seq to points where it's NULL, ordered by timestamp.
|
||
// Only runs if there are NULL-seq points (should be one-time after migration).
|
||
try {
|
||
const nullSeqSessions = await select(
|
||
"SELECT DISTINCT session_id FROM gps_points WHERE seq IS NULL"
|
||
);
|
||
if (nullSeqSessions.length > 0) {
|
||
console.log(`[db.js] Repairing seq for ${nullSeqSessions.length} sessions...`);
|
||
for (let row of nullSeqSessions) {
|
||
// Batched CASE update (like saveGapFill/saveSmoothedTrack): one UPDATE per ~300 points
|
||
// instead of one per point. The per-point loop was ~15000 awaited IPC round-trips on
|
||
// merged/split tracks (seq=NULL by design) — many seconds of startup, EVERY boot, and
|
||
// for the big sessions it did not even persist before the next launch re-ran it.
|
||
let pts = await select(
|
||
"SELECT id FROM gps_points WHERE session_id = ?1 ORDER BY ts ASC, id ASC",
|
||
[row.session_id]
|
||
);
|
||
let SEQCHUNK = 300; // 2 params/row (id, seq) → under SQLite's 999 bind-var limit
|
||
for (let i = 0; i < pts.length; i += SEQCHUNK) {
|
||
let chunk = pts.slice(i, i + SEQCHUNK);
|
||
let when = [], ids = [], params = [];
|
||
for (let k = 0; k < chunk.length; k++) {
|
||
params.push(chunk[k].id); let idi = params.length;
|
||
params.push(i + k + 1); let sqi = params.length;
|
||
when.push(`WHEN ?${idi} THEN ?${sqi}`);
|
||
ids.push(`?${idi}`);
|
||
}
|
||
await execute(`UPDATE gps_points SET seq=CASE id ${when.join(" ")} END WHERE id IN (${ids.join(",")})`, params);
|
||
}
|
||
console.log(`[db.js] Repaired seq for session ${row.session_id}: ${pts.length} points`);
|
||
}
|
||
}
|
||
} catch (e) { console.error("[db.js] seq repair error:", e); }
|
||
}
|
||
|
||
window.DB._migrationsReady = runMigrations().then(() => {
|
||
console.log("[db.js] migrations done");
|
||
}).catch(e => {
|
||
console.error("[db.js] migration error:", e);
|
||
});
|
||
|
||
console.log("[db.js] window.DB ready, keys:", Object.keys(window.DB).length);
|