481 lines
27 KiB
JavaScript
481 lines
27 KiB
JavaScript
// Kilometer Log — small API + static page, backed by PostgreSQL.
|
|
const path = require("path");
|
|
const crypto = require("crypto");
|
|
const express = require("express");
|
|
const { Pool, types } = require("pg");
|
|
|
|
// Return DATE columns as 'YYYY-MM-DD' strings and NUMERIC as numbers.
|
|
types.setTypeParser(1082, v => v);
|
|
types.setTypeParser(1700, v => parseFloat(v));
|
|
|
|
const PORT = Number(process.env.PORT || 3000);
|
|
const CATEGORIES = ["PNOD", "hire", "personal"];
|
|
// Home Assistant: the webhook is only enabled when a token is set (HA_TOKEN in the stack's environment variables).
|
|
const HA_TOKEN = String(process.env.HA_TOKEN || "").trim();
|
|
const TZ = process.env.TZ || "Europe/Amsterdam"; // used to turn a reading's time into the trip date
|
|
const HA_MAX_JUMP_KM = Number(process.env.HA_MAX_JUMP_KM || 5000);
|
|
// Turning coordinates into a town name: OpenStreetMap Nominatim by default (GEOCODER=off disables it).
|
|
const GEOCODER = String(process.env.GEOCODER || "nominatim").toLowerCase();
|
|
const GEOCODER_URL = String(process.env.GEOCODER_URL || "https://nominatim.openstreetmap.org").replace(/\/+$/, "");
|
|
const GEOCODER_LANG = process.env.GEOCODER_LANG || "nl";
|
|
const GEOCODER_EMAIL = process.env.GEOCODER_EMAIL || "";
|
|
|
|
const pool = new Pool({
|
|
host: process.env.DB_HOST || "postgres",
|
|
port: Number(process.env.DB_PORT || 5432),
|
|
// DB_* wins; otherwise the same POSTGRES_* variables the database container uses (e.g. from Portainer's stack.env)
|
|
user: process.env.DB_USER || process.env.POSTGRES_USER || "kilometerlog",
|
|
password: process.env.DB_PASSWORD || process.env.POSTGRES_PASSWORD || "",
|
|
database: process.env.DB_NAME || process.env.POSTGRES_DB || "kilometerlog",
|
|
max: 5,
|
|
});
|
|
|
|
async function migrate() {
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS trips (
|
|
id SERIAL PRIMARY KEY,
|
|
km NUMERIC(8,1) NOT NULL CHECK (km > 0),
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
plate VARCHAR(16) NOT NULL,
|
|
country VARCHAR(3) NOT NULL DEFAULT 'NL',
|
|
region VARCHAR(3) NOT NULL DEFAULT '',
|
|
model VARCHAR(60) NOT NULL DEFAULT '',
|
|
location VARCHAR(255) NOT NULL DEFAULT '',
|
|
category VARCHAR(10) NOT NULL CHECK (category IN ('PNOD','hire','personal')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (end_date >= start_date)
|
|
)`);
|
|
// Upgrade tables created before make/model existed.
|
|
await pool.query("ALTER TABLE trips ADD COLUMN IF NOT EXISTS model VARCHAR(60) NOT NULL DEFAULT ''");
|
|
// Each location's country, stored once per location name.
|
|
await pool.query(`CREATE TABLE IF NOT EXISTS locations (
|
|
name VARCHAR(255) PRIMARY KEY,
|
|
country VARCHAR(3) NOT NULL DEFAULT ''
|
|
)`);
|
|
// App settings (key/value), e.g. the category names.
|
|
await pool.query(`CREATE TABLE IF NOT EXISTS settings (
|
|
key VARCHAR(64) PRIMARY KEY,
|
|
value JSONB NOT NULL
|
|
)`);
|
|
// Where a trip came from ('' = typed or imported, 'ha' = Home Assistant).
|
|
await pool.query("ALTER TABLE trips ADD COLUMN IF NOT EXISTS source VARCHAR(16) NOT NULL DEFAULT ''");
|
|
// Last odometer reading per car, received from Home Assistant.
|
|
await pool.query(`CREATE TABLE IF NOT EXISTS ha_odometer (
|
|
plate_key VARCHAR(16) PRIMARY KEY, -- plate without spaces/dashes, upper case
|
|
plate VARCHAR(16) NOT NULL,
|
|
odometer_km NUMERIC(10,1) NOT NULL,
|
|
read_at TIMESTAMPTZ NOT NULL,
|
|
last_event TEXT NOT NULL DEFAULT '',
|
|
last_event_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
)`);
|
|
// Where the car was at that reading: the departure place of the next trip.
|
|
await pool.query("ALTER TABLE ha_odometer ADD COLUMN IF NOT EXISTS lat DOUBLE PRECISION");
|
|
await pool.query("ALTER TABLE ha_odometer ADD COLUMN IF NOT EXISTS lon DOUBLE PRECISION");
|
|
await pool.query("ALTER TABLE ha_odometer ADD COLUMN IF NOT EXISTS place_text VARCHAR(255) NOT NULL DEFAULT ''");
|
|
await pool.query("CREATE INDEX IF NOT EXISTS trips_start_idx ON trips (start_date)");
|
|
await pool.query("CREATE INDEX IF NOT EXISTS trips_plate_idx ON trips (plate)");
|
|
}
|
|
|
|
async function waitForDb(retries = 30) {
|
|
for (let i = 1; i <= retries; i++) {
|
|
try { await pool.query("SELECT 1"); return; }
|
|
catch (e) {
|
|
console.log(`Waiting for PostgreSQL (${i}/${retries}): ${e.code || e.message}`);
|
|
await new Promise(r => setTimeout(r, 2000));
|
|
}
|
|
}
|
|
throw new Error("PostgreSQL not reachable");
|
|
}
|
|
|
|
const isDate = s => typeof s === "string" && /^\d{4}-\d{2}-\d{2}$/.test(s) && !isNaN(Date.parse(s));
|
|
|
|
function validate(b) {
|
|
const t = {
|
|
km: Math.round(Number(b.km) * 10) / 10,
|
|
start: b.start,
|
|
end: b.end || b.start,
|
|
plate: String(b.plate || "").trim().toUpperCase().replace(/\s+/g, " ").slice(0, 16),
|
|
country: String(b.country ?? "").trim().toUpperCase(),
|
|
region: String(b.region ?? "").trim().toUpperCase(),
|
|
model: String(b.model ?? "").trim().replace(/\s+/g, " ").slice(0, 60),
|
|
location: String(b.location || "").trim().slice(0, 255),
|
|
category: b.category,
|
|
};
|
|
if (!isDate(t.start)) return { error: "Start date must be YYYY-MM-DD." };
|
|
if (!isDate(t.end)) return { error: "End date must be YYYY-MM-DD." };
|
|
if (t.end < t.start) return { error: "The end date is before the start date." };
|
|
if (!(t.km > 0) || t.km > 9999999) return { error: "Kilometres must be a number above 0." };
|
|
if (!t.plate) return { error: "License plate is required." };
|
|
if (!/^[A-Z]{0,3}$/.test(t.country)) return { error: "Country must be a code of 1 to 3 letters, like NL, N, UK or USA." };
|
|
if (!/^[A-Z]{0,3}$/.test(t.region)) return { error: "State must be a 2-letter code, like CA or NY." };
|
|
if (t.country !== "USA") t.region = "";
|
|
if (!CATEGORIES.includes(t.category)) return { error: "Category must be business (PNOD), rental (hire) or personal." };
|
|
return { trip: t };
|
|
}
|
|
const COLS = "km, start_date, end_date, plate, country, region, model, location, category";
|
|
const NCOLS = 9;
|
|
const row = t => [t.km, t.start, t.end, t.plate, t.country, t.region, t.model, t.location, t.category];
|
|
const placeholders = (n, offset = 0) => "(" + Array.from({ length: n }, (_, i) => "$" + (offset + i + 1)).join(",") + ")";
|
|
|
|
const app = express();
|
|
app.use(express.json({ limit: "5mb" }));
|
|
const wrap = fn => (req, res) => fn(req, res).catch(e => {
|
|
console.error(e);
|
|
res.status(500).json({ error: "Database error" });
|
|
});
|
|
|
|
app.get("/healthz", wrap(async (req, res) => { await pool.query("SELECT 1"); res.json({ ok: true }); }));
|
|
|
|
app.get("/api/trips", wrap(async (req, res) => {
|
|
const { rows } = await pool.query(
|
|
`SELECT id, km, start_date AS start, end_date AS "end", plate, country, region, model, location, category, source
|
|
FROM trips ORDER BY start_date DESC, id DESC`);
|
|
res.json(rows);
|
|
}));
|
|
|
|
app.post("/api/trips", wrap(async (req, res) => {
|
|
const v = validate(req.body || {});
|
|
if (v.error) return res.status(400).json({ error: v.error });
|
|
const { rows } = await pool.query(`INSERT INTO trips (${COLS}) VALUES ${placeholders(NCOLS)} RETURNING id`, row(v.trip));
|
|
res.status(201).json({ id: rows[0].id });
|
|
}));
|
|
|
|
app.post("/api/trips/bulk", wrap(async (req, res) => {
|
|
const list = Array.isArray(req.body && req.body.trips) ? req.body.trips : null;
|
|
if (!list || !list.length) return res.status(400).json({ error: "No trips to import." });
|
|
if (list.length > 10000) return res.status(400).json({ error: "Import at most 10,000 trips at once." });
|
|
const rows = [];
|
|
for (let i = 0; i < list.length; i++) {
|
|
const v = validate(list[i]);
|
|
if (v.error) return res.status(400).json({ error: `Row ${i + 1}: ${v.error}` });
|
|
rows.push(row(v.trip));
|
|
}
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query("BEGIN");
|
|
for (let i = 0; i < rows.length; i += 500) {
|
|
const chunk = rows.slice(i, i + 500);
|
|
const values = chunk.map((_, j) => placeholders(NCOLS, j * NCOLS)).join(",");
|
|
await client.query(`INSERT INTO trips (${COLS}) VALUES ${values}`, chunk.flat());
|
|
}
|
|
await client.query("COMMIT");
|
|
} catch (e) { await client.query("ROLLBACK"); throw e; }
|
|
finally { client.release(); }
|
|
res.status(201).json({ inserted: rows.length });
|
|
}));
|
|
|
|
app.put("/api/trips/:id", wrap(async (req, res) => {
|
|
const v = validate(req.body || {});
|
|
if (v.error) return res.status(400).json({ error: v.error });
|
|
const r = await pool.query(
|
|
`UPDATE trips SET km=$1, start_date=$2, end_date=$3, plate=$4, country=$5, region=$6, model=$7, location=$8, category=$9, updated_at=now()
|
|
WHERE id=$10`, [...row(v.trip), Number(req.params.id)]);
|
|
if (!r.rowCount) return res.status(404).json({ error: "Trip not found." });
|
|
res.status(204).end();
|
|
}));
|
|
|
|
// Update a vehicle (plate + its current country) on all its trips: country, US state and make/model.
|
|
app.post("/api/vehicles/update", wrap(async (req, res) => {
|
|
const b = req.body || {};
|
|
const plate = String(b.plate || "").trim().toUpperCase();
|
|
const from = String(b.from ?? "").trim().toUpperCase(), to = String(b.country ?? "").trim().toUpperCase();
|
|
const region = to === "USA" ? String(b.region ?? "").trim().toUpperCase() : "";
|
|
const model = String(b.model ?? "").trim().replace(/\s+/g, " ").slice(0, 60);
|
|
if (!plate || !/^[A-Z]{0,3}$/.test(from) || !/^[A-Z]{0,3}$/.test(to) || !/^[A-Z]{0,3}$/.test(region))
|
|
return res.status(400).json({ error: "Give a plate and country codes of up to 3 letters." });
|
|
const r = await pool.query(
|
|
"UPDATE trips SET country=$1, region=$2, model=$3, updated_at=now() WHERE plate=$4 AND country=$5",
|
|
[to, region, model, plate, from]);
|
|
res.json({ updated: r.rowCount });
|
|
}));
|
|
|
|
// Rewrite plates in their standard format (e.g. GX482T -> GX-482-T) for a list of vehicles, in one transaction.
|
|
app.post("/api/plates/rename", wrap(async (req, res) => {
|
|
const list = Array.isArray(req.body && req.body.changes) ? req.body.changes : null;
|
|
if (!list || !list.length || list.length > 5000) return res.status(400).json({ error: "No plates to rename." });
|
|
const clean = v => String(v ?? "").trim().toUpperCase().replace(/\s+/g, " ").slice(0, 16);
|
|
const client = await pool.connect(); let updated = 0;
|
|
try {
|
|
await client.query("BEGIN");
|
|
for (const c of list) {
|
|
const from = clean(c.from), to = clean(c.to), country = String(c.country ?? "").trim().toUpperCase();
|
|
if (!from || !to || !/^[A-Z]{0,3}$/.test(country)) throw Object.assign(new Error("bad"), { status: 400 });
|
|
const r = await client.query("UPDATE trips SET plate=$1, updated_at=now() WHERE plate=$2 AND country=$3", [to, from, country]);
|
|
updated += r.rowCount;
|
|
}
|
|
await client.query("COMMIT");
|
|
} catch (e) {
|
|
await client.query("ROLLBACK");
|
|
if (e.status === 400) return res.status(400).json({ error: "Each change needs a plate, a new plate and a country code." });
|
|
throw e;
|
|
} finally { client.release(); }
|
|
res.json({ updated });
|
|
}));
|
|
|
|
app.get("/api/settings", wrap(async (req, res) => {
|
|
const { rows } = await pool.query("SELECT value FROM settings WHERE key='categoryLabels'");
|
|
res.json({ categoryLabels: rows[0] ? rows[0].value : {} });
|
|
}));
|
|
|
|
// Category names shown in the app. The stored categories (PNOD, hire, personal) never change.
|
|
app.put("/api/settings/categories", wrap(async (req, res) => {
|
|
const l = (req.body && req.body.labels) || {};
|
|
const labels = {};
|
|
for (const k of CATEGORIES) labels[k] = String(l[k] ?? "").trim().replace(/\s+/g, " ").slice(0, 30);
|
|
await pool.query(
|
|
"INSERT INTO settings (key, value) VALUES ('categoryLabels', $1) ON CONFLICT (key) DO UPDATE SET value=EXCLUDED.value",
|
|
[JSON.stringify(labels)]);
|
|
res.status(204).end();
|
|
}));
|
|
|
|
app.get("/api/locations", wrap(async (req, res) => {
|
|
const { rows } = await pool.query("SELECT name, country FROM locations ORDER BY name");
|
|
res.json(rows);
|
|
}));
|
|
|
|
// Set (or clear) the country of a location.
|
|
app.put("/api/locations", wrap(async (req, res) => {
|
|
const b = req.body || {};
|
|
const name = String(b.name ?? "").trim().replace(/\s+/g, " ").slice(0, 255);
|
|
const country = String(b.country ?? "").trim().toUpperCase();
|
|
if (!name || !/^[A-Z]{0,3}$/.test(country)) return res.status(400).json({ error: "Give a location name and a country code of up to 3 letters." });
|
|
if (country) await pool.query(
|
|
"INSERT INTO locations (name, country) VALUES ($1,$2) ON CONFLICT (name) DO UPDATE SET country=EXCLUDED.country", [name, country]);
|
|
else await pool.query("DELETE FROM locations WHERE name=$1", [name]);
|
|
res.status(204).end();
|
|
}));
|
|
|
|
// Rename a location on every trip that uses it; renaming to an existing name merges them.
|
|
// The location's country moves along, unless the target already has one.
|
|
app.post("/api/locations/rename", wrap(async (req, res) => {
|
|
const b = req.body || {};
|
|
const from = String(b.from ?? "");
|
|
const to = String(b.to ?? "").trim().replace(/\s+/g, " ").slice(0, 255);
|
|
if (!to) return res.status(400).json({ error: "Enter the new location name." });
|
|
const client = await pool.connect(); let updated = 0;
|
|
try {
|
|
await client.query("BEGIN");
|
|
const r = await client.query("UPDATE trips SET location=$1, updated_at=now() WHERE location=$2", [to, from]);
|
|
updated = r.rowCount;
|
|
if (from && from !== to) {
|
|
await client.query(
|
|
`INSERT INTO locations (name, country) SELECT $2, country FROM locations WHERE name=$1
|
|
ON CONFLICT (name) DO NOTHING`, [from, to]);
|
|
await client.query("DELETE FROM locations WHERE name=$1", [from]);
|
|
}
|
|
await client.query("COMMIT");
|
|
} catch (e) { await client.query("ROLLBACK"); throw e; }
|
|
finally { client.release(); }
|
|
res.json({ updated });
|
|
}));
|
|
|
|
// Delete every trip of one vehicle (plate + country + US state).
|
|
app.post("/api/vehicles/delete", wrap(async (req, res) => {
|
|
const b = req.body || {};
|
|
const plate = String(b.plate || "").trim().toUpperCase();
|
|
const country = String(b.country ?? "").trim().toUpperCase(), region = String(b.region ?? "").trim().toUpperCase();
|
|
if (!plate || !/^[A-Z]{0,3}$/.test(country) || !/^[A-Z]{0,3}$/.test(region))
|
|
return res.status(400).json({ error: "Give the plate and its country." });
|
|
const r = await pool.query("DELETE FROM trips WHERE plate=$1 AND country=$2 AND region=$3", [plate, country, region]);
|
|
res.json({ deleted: r.rowCount });
|
|
}));
|
|
|
|
/* ---------- Reverse geocoding (coordinates -> town + country) ---------- */
|
|
// ISO country codes from OpenStreetMap -> the international plate codes the app uses.
|
|
const ISO_TO_PLATE = { nl:"NL", be:"B", de:"D", fr:"F", lu:"L", gb:"UK", ie:"IRL", no:"N", se:"S", dk:"DK", fi:"FIN", si:"SLO",
|
|
hr:"HR", at:"A", ch:"CH", it:"I", es:"E", pt:"P", pl:"PL", cz:"CZ", sk:"SK", hu:"H", ro:"RO", bg:"BG", gr:"GR", md:"MD",
|
|
ua:"UA", is:"IS", al:"AL", rs:"SRB", ba:"BIH", me:"MNE", mk:"NMK", tr:"TR", us:"USA", ca:"CDN", li:"FL", mc:"MC", ad:"AND" };
|
|
const geoCache = new Map(); // "lat,lon" rounded to ~100 m -> {town, country}
|
|
let geoQueue = Promise.resolve(), geoLast = 0;
|
|
// Nominatim's usage policy: at most 1 request per second, identify the app, cache results.
|
|
function reverseGeocode(lat, lon) {
|
|
if (GEOCODER === "off") return Promise.resolve(null);
|
|
const key = lat.toFixed(3) + "," + lon.toFixed(3);
|
|
if (geoCache.has(key)) return Promise.resolve(geoCache.get(key));
|
|
const run = async () => {
|
|
// at least 1.5 s between lookups: comfortably under Nominatim's 1 request per second
|
|
const wait = geoLast + 1500 - Date.now(); if (wait > 0) await new Promise(r => setTimeout(r, wait));
|
|
geoLast = Date.now();
|
|
const url = `${GEOCODER_URL}/reverse?format=jsonv2&zoom=12&addressdetails=1&lat=${lat}&lon=${lon}&accept-language=${encodeURIComponent(GEOCODER_LANG)}`
|
|
+ (GEOCODER_EMAIL ? `&email=${encodeURIComponent(GEOCODER_EMAIL)}` : "");
|
|
const r = await fetch(url, { headers: { "User-Agent": "KilometerLog/1.0 (self-hosted trip log)" }, signal: AbortSignal.timeout(8000) });
|
|
if (!r.ok) throw new Error(`place lookup answered ${r.status}`);
|
|
const j = await r.json(), a = j.address || {};
|
|
const town = a.city || a.town || a.village || a.municipality || a.hamlet || a.suburb || a.county || "";
|
|
const iso = String(a.country_code || "").toLowerCase();
|
|
const out = town ? { town: String(town).slice(0, 255), country: ISO_TO_PLATE[iso] || iso.toUpperCase().slice(0, 3) } : null;
|
|
if (geoCache.size > 2000) geoCache.clear();
|
|
geoCache.set(key, out);
|
|
return out;
|
|
};
|
|
const p = geoQueue.then(run, run);
|
|
geoQueue = p.catch(() => {});
|
|
return p;
|
|
}
|
|
|
|
/* ---------- Home Assistant ---------- */
|
|
// Which place a Home Assistant trip gets: where it started ("start", the default) or where it ended ("end").
|
|
async function haLocationFrom() {
|
|
const { rows } = await pool.query("SELECT value FROM settings WHERE key='haLocationFrom'");
|
|
return rows[0] && rows[0].value === "end" ? "end" : "start";
|
|
}
|
|
const plateKey = p => String(p || "").toUpperCase().replace(/[\s\-.·]/g, "").slice(0, 16);
|
|
function tokenOk(req) {
|
|
const h = String(req.get("authorization") || ""), given = h.startsWith("Bearer ") ? h.slice(7).trim() : String(req.get("x-ha-token") || "");
|
|
if (!HA_TOKEN || !given) return false;
|
|
const a = Buffer.from(given), b = Buffer.from(HA_TOKEN);
|
|
return a.length === b.length && crypto.timingSafeEqual(a, b);
|
|
}
|
|
const localDate = d => new Intl.DateTimeFormat("en-CA", { timeZone: TZ, year: "numeric", month: "2-digit", day: "2-digit" }).format(d);
|
|
|
|
// Home Assistant sends the odometer each time it changes. The distance since the previous reading becomes a trip.
|
|
// Body: { plate, odometer, unit?: "km"|"mi", time?: ISO string, latitude?, longitude?, location?: string, category?, model? }
|
|
// With latitude/longitude the location becomes the town there (and the town's country is stored);
|
|
// a "location" text is only used when there are no coordinates or the lookup fails.
|
|
app.post("/api/ha/odometer", wrap(async (req, res) => {
|
|
if (!HA_TOKEN) return res.status(404).json({ error: "Home Assistant import is off. Set HA_TOKEN to turn it on." });
|
|
if (!tokenOk(req)) return res.status(401).json({ error: "Wrong or missing token." });
|
|
const b = req.body || {};
|
|
const key = plateKey(b.plate);
|
|
const unit = String(b.unit || "km").toLowerCase().startsWith("mi") ? "mi" : "km";
|
|
const odo = Number(String(b.odometer ?? "").replace(",", "."));
|
|
const when = b.time ? new Date(b.time) : new Date();
|
|
const category = CATEGORIES.includes(b.category) ? b.category : "personal";
|
|
let location = String(b.location ?? "").trim().replace(/\s+/g, " ").slice(0, 255);
|
|
const lat = Number(b.latitude), lon = Number(b.longitude);
|
|
const hasPos = Number.isFinite(lat) && Number.isFinite(lon) && Math.abs(lat) <= 90 && Math.abs(lon) <= 180 && !(lat === 0 && lon === 0);
|
|
if (!key) return res.status(400).json({ error: "Send the license plate as 'plate'." });
|
|
if (!Number.isFinite(odo) || odo < 0) return res.status(400).json({ error: "Send the odometer reading as a number in 'odometer'." });
|
|
if (isNaN(when)) return res.status(400).json({ error: "'time' is not a valid date." });
|
|
const odoKm = Math.round((unit === "mi" ? odo * 1.609344 : odo) * 10) / 10;
|
|
|
|
// Decide the trip's place before taking the database lock, so a slow town lookup never blocks other requests.
|
|
// - departure ("start"): where the car stood at the previous reading, i.e. where this trip began;
|
|
// - arrival ("end"): where the car is now.
|
|
// Only readings that become a trip are looked up (not repeats after a Home Assistant restart).
|
|
const here = { lat: hasPos ? lat : null, lon: hasPos ? lon : null, text: location }; // stored for the next trip
|
|
const from = await haLocationFrom();
|
|
let place = null, placeNote = "", tripLocation = "";
|
|
const { rows: [p] } = await pool.query("SELECT odometer_km, lat, lon, place_text FROM ha_odometer WHERE plate_key=$1", [key]);
|
|
if (p && odoKm > p.odometer_km && odoKm - p.odometer_km <= HA_MAX_JUMP_KM) {
|
|
let src = here;
|
|
if (from === "start") {
|
|
if (p.lat !== null || p.place_text) src = { lat: p.lat, lon: p.lon, text: p.place_text };
|
|
else placeNote = " (departure place not known yet, so the arrival place is used)";
|
|
}
|
|
tripLocation = src.text;
|
|
if (src.lat !== null && src.lon !== null) {
|
|
try { place = await reverseGeocode(src.lat, src.lon); if (!place && GEOCODER !== "off") placeNote += ` (no town found at ${src.lat.toFixed(4)}, ${src.lon.toFixed(4)})`; }
|
|
catch (e) { placeNote += ` (town lookup failed: ${e.message})`; console.log(`[ha] place lookup failed: ${e.message}`); }
|
|
if (place) tripLocation = place.town;
|
|
}
|
|
}
|
|
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query("BEGIN");
|
|
// The vehicle as it is already in the log (exact plate spelling, country, state, model), if any.
|
|
const { rows: known } = await client.query(
|
|
`SELECT plate, country, region, model FROM trips
|
|
WHERE upper(translate(plate, ' -.', '')) = $1 ORDER BY start_date DESC, id DESC LIMIT 1`, [key]);
|
|
const v = known[0] || { plate: String(b.plate).trim().toUpperCase().slice(0, 16), country: "", region: "", model: "" };
|
|
const model = v.model || String(b.model || "").trim().slice(0, 60);
|
|
|
|
const first = await client.query(
|
|
`INSERT INTO ha_odometer (plate_key, plate, odometer_km, read_at, last_event, lat, lon, place_text)
|
|
VALUES ($1,$2,$3,$4,'First reading saved; the next reading creates a trip',$5,$6,$7) ON CONFLICT (plate_key) DO NOTHING`,
|
|
[key, v.plate, odoKm, when, here.lat, here.lon, here.text]);
|
|
const { rows: [prev] } = await client.query("SELECT * FROM ha_odometer WHERE plate_key=$1 FOR UPDATE", [key]);
|
|
const diff = Math.round((odoKm - prev.odometer_km) * 10) / 10;
|
|
let result;
|
|
|
|
if (first.rowCount === 1) {
|
|
result = { status: "baseline", message: `First reading saved (${odoKm} km); the next reading creates a trip` };
|
|
await client.query("UPDATE ha_odometer SET last_event=$2 WHERE plate_key=$1", [key, result.message]);
|
|
} else if (diff <= 0) {
|
|
// Same or lower reading (Home Assistant restart, duplicate message): nothing to add.
|
|
result = { status: "unchanged", message: `Odometer ${odoKm} km, no new distance` };
|
|
// the car hasn't moved: only fill in a position if none was known yet
|
|
await client.query(`UPDATE ha_odometer SET last_event=$2, last_event_at=now(),
|
|
lat=COALESCE(lat,$3), lon=COALESCE(lon,$4), place_text=CASE WHEN place_text='' THEN $5 ELSE place_text END WHERE plate_key=$1`,
|
|
[key, result.message, here.lat, here.lon, here.text]);
|
|
} else if (diff > HA_MAX_JUMP_KM) {
|
|
result = { status: "skipped", message: `Skipped a jump of ${diff} km (more than ${HA_MAX_JUMP_KM} km); new starting point ${odoKm} km` };
|
|
await client.query("UPDATE ha_odometer SET odometer_km=$2, read_at=$3, last_event=$4, last_event_at=now(), lat=$5, lon=$6, place_text=$7 WHERE plate_key=$1",
|
|
[key, odoKm, when, result.message, here.lat, here.lon, here.text]);
|
|
} else {
|
|
const day = localDate(when);
|
|
const { rows: [t] } = await client.query(
|
|
`INSERT INTO trips (km, start_date, end_date, plate, country, region, model, location, category, source)
|
|
VALUES ($1,$2,$2,$3,$4,$5,$6,$7,$8,'ha') RETURNING id`,
|
|
[diff, day, v.plate, v.country, v.region, model, tripLocation, category]);
|
|
const location = tripLocation;
|
|
// The town's country, unless you already set one for this location yourself.
|
|
let country;
|
|
if (place && place.country && location) {
|
|
await client.query("INSERT INTO locations (name, country) VALUES ($1,$2) ON CONFLICT (name) DO NOTHING", [location, place.country]);
|
|
const { rows: [lc] } = await client.query("SELECT country FROM locations WHERE name=$1", [location]);
|
|
country = lc ? lc.country : place.country;
|
|
}
|
|
result = { status: "created", trip: t.id, km: diff, location, country,
|
|
message: `Trip of ${diff} km added for ${day}${location ? (from === "start" ? " from " : " at ") + location : ""}${country ? " (" + country + ")" : ""}${placeNote}` };
|
|
// where the car is now becomes the departure place of the next trip
|
|
await client.query("UPDATE ha_odometer SET odometer_km=$2, read_at=$3, plate=$4, last_event=$5, last_event_at=now(), lat=$6, lon=$7, place_text=$8 WHERE plate_key=$1",
|
|
[key, odoKm, when, v.plate, result.message, here.lat, here.lon, here.text]);
|
|
}
|
|
await client.query("COMMIT");
|
|
console.log(`[ha] ${v.plate}: ${result.message}`);
|
|
res.status(result.status === "created" ? 201 : 200).json(result);
|
|
} catch (e) { await client.query("ROLLBACK"); throw e; }
|
|
finally { client.release(); }
|
|
}));
|
|
|
|
// A GET here is either someone opening the address in a browser, or Home Assistant's POST after a 301/302
|
|
// redirect (e.g. http -> https), which turns it into a GET without the data. Explain instead of "Cannot GET".
|
|
let haLastGet = null;
|
|
app.get("/api/ha/odometer", (req, res) => {
|
|
haLastGet = { at: new Date().toISOString(), agent: String(req.get("user-agent") || "").slice(0, 120) };
|
|
console.log(`[ha] GET /api/ha/odometer from ${haLastGet.agent || "unknown"} — expected POST; probably a redirect`);
|
|
res.status(405).set("Allow", "POST").json({
|
|
error: "This address only accepts POST requests from Home Assistant.",
|
|
hint: "If Home Assistant gets this answer, its request was redirected (for example from http:// to https://) and turned into a GET without the data. Use the exact https:// address of the app in configuration.yaml.",
|
|
});
|
|
});
|
|
|
|
// What the Admin page shows: is the import on, and the last reading per car. Never returns the token.
|
|
app.get("/api/ha/status", wrap(async (req, res) => {
|
|
const { rows } = await pool.query(
|
|
"SELECT plate, odometer_km, read_at, last_event, last_event_at FROM ha_odometer ORDER BY plate");
|
|
res.json({ enabled: !!HA_TOKEN, timezone: TZ, lastGet: haLastGet, locationFrom: await haLocationFrom(), geocoder: GEOCODER === "off" ? null : { url: GEOCODER_URL, lang: GEOCODER_LANG }, vehicles: rows });
|
|
}));
|
|
|
|
app.put("/api/settings/ha", wrap(async (req, res) => {
|
|
const v = (req.body && req.body.locationFrom) === "end" ? "end" : "start";
|
|
await pool.query("INSERT INTO settings (key, value) VALUES ('haLocationFrom', $1) ON CONFLICT (key) DO UPDATE SET value=EXCLUDED.value",
|
|
[JSON.stringify(v)]);
|
|
res.status(204).end();
|
|
}));
|
|
|
|
// Forget a car's last reading, so the next reading starts fresh (no trip is created from it).
|
|
app.delete("/api/ha/odometer/:plate", wrap(async (req, res) => {
|
|
await pool.query("DELETE FROM ha_odometer WHERE plate_key=$1", [plateKey(req.params.plate)]);
|
|
res.status(204).end();
|
|
}));
|
|
|
|
app.delete("/api/trips/:id", wrap(async (req, res) => {
|
|
await pool.query("DELETE FROM trips WHERE id=$1", [Number(req.params.id)]);
|
|
res.status(204).end();
|
|
}));
|
|
|
|
app.use(express.static(path.join(__dirname, "public")));
|
|
|
|
(async () => {
|
|
await waitForDb();
|
|
await migrate();
|
|
app.listen(PORT, () => console.log(`Kilometer Log listening on :${PORT}`));
|
|
})().catch(e => { console.error(e); process.exit(1); });
|
|
|
|
process.on("SIGTERM", () => pool.end().finally(() => process.exit(0)));
|