script for cleaning

1 files changed, 361 insertions, 0 deletions

diff --git a/scripts/seed-sheet1-routines.ts b/scripts/seed-sheet1-routines.ts
new file mode 100644
index 0000000..3e3a286
--- /dev/null
+++ b/scripts/seed-sheet1-routines.ts
@@ -0,0 +1,361 @@
+/**
+ * Seed routines from the paper sheet photo ("Sheet1", ~/Images/Screenshots/20260912_110541.jpg).
+ *
+ * What the photo shows: rows = tasks grouped by room, columns = ISO week
+ * numbers of year 2026 (confirmed by user). An X = "done that week".
+ * No explicit target frequencies are written anywhere, so every
+ * `frequencyDays` below is INFERRED from the gaps between X marks — each one
+ * carries a `TODO(you)` comment with the observed weeks. Search for
+ * `TODO(you)` and set the real intervals.
+ * All routines live in the single 'Ménage' project; rooms are a 'Room/Task'
+ * slash prefix in the routine name (e.g. 'Chambre/Draps').
+ *
+ * Completions: exactly ONE per routine — the LAST checked week (`lastWeek2026`).
+ * The sheet only says "done that week", never which day, so by convention the
+ * completion is dated to the SATURDAY of that ISO week, 12:00 UTC (noon avoids
+ * timezone day-shifts). Shift the day if you know better — search SATURDAY.
+ * Routines with no marks at all (Salon/Placards) get no completion.
+ *
+ * Idempotent: existing names are skipped, and a completion is
+ * only added when the routine has none, so re-running is safe.
+ * Uses only `@libsql/client` (already a prod dependency) plus erasable types,
+ * so it runs directly with Node type-stripping — no build step. Shipped in
+ * the image (whole `scripts/` dir is copied).
+ *
+ * HOW TO RUN IN DOCKER PROD (db at /app/data/data.db):
+ *
+ *   docker exec <container> node --experimental-strip-types scripts/seed-sheet1-routines.ts
+ *
+ * With compose (service name e.g. `app` — adjust to yours):
+ *   docker compose exec app node --experimental-strip-types scripts/seed-sheet1-routines.ts
+ *
+ * (DATABASE_URL is already set in the image ENV; add
+ * `-e DATABASE_URL='file:./data/data.db'` if yours isn't. Node 26 also runs
+ * it flagless — the flag keeps it working on 22.6+.)
+ *
+ * Local dev sanity check against a throwaway db (never your real data.db):
+ *   DATABASE_URL='file:/tmp/seedcheck.db' node scripts/migrate.js
+ *   DATABASE_URL='file:/tmp/seedcheck.db' node --experimental-strip-types scripts/seed-sheet1-routines.ts
+ */
+
+import { createClient } from '@libsql/client';
+
+type SeedRoutine = {
+	name: string; // 'Room/Task' — slash prefix groups by room inside the single project
+	frequencyDays: number;
+	/** Last checked ISO week of 2026 read off the sheet; null = never checked. */
+	lastWeek2026: number | null;
+	/** What the task covers (common-wisdom baseline, adapt freely). */
+	description: string;
+};
+/** Single project hosting every routine; rooms live in the name prefix. */
+const PROJECT = 'Ménage';
+const DB_URL: string = process.env.DATABASE_URL ?? 'file:./data.db';
+
+/** Saturday (SATURDAY convention) of an ISO week, noon UTC. Returns ISO instant string. */
+function isoWeekSaturday(year: number, week: number): string {
+	// Monday of ISO week 1 = Monday of the week containing Jan 4.
+	const jan4 = Temporal.PlainDate.from({ year, month: 1, day: 4 });
+	const monday = jan4.subtract({ days: jan4.dayOfWeek - 1 });
+	const saturdayNoon = monday
+		.add({ days: (week - 1) * 7 + 5 })
+		.toZonedDateTime('UTC')
+		.add({ hours: 12 });
+	return saturdayNoon.toInstant().toString({ smallestUnit: 'millisecond' });
+}
+
+const ROUTINES: SeedRoutine[] = [
+	// ---- Ménage ----
+	{
+		name: 'Aspirateur',
+		frequencyDays: 7,
+		lastWeek2026: 37,
+		description:
+			'Sols, tapis, plinthes et dessous des meubles. Vider le bac, nettoyer brosse et filtre quand l’aspiration baisse.',
+	},
+	{
+		name: 'Serpillère',
+		frequencyDays: 14,
+		lastWeek2026: 34,
+		description:
+			'Après aspiration, serpillère bien essorée. Insister entrées et cuisine. Rincer la tête après usage.',
+	},
+	// --- Bureau ---
+	{
+		name: 'Bureau/Vitres',
+		frequencyDays: 180,
+		lastWeek2026: 22,
+		description:
+			'Cadres puis vitre, de haut en bas, par temps couvert. Rails et joints dépoussiérés.',
+	},
+	// ---- Chambre rose ----
+	{
+		name: 'Chambre rose/Draps',
+		frequencyDays: 14,
+		lastWeek2026: 31,
+		description: 'Draps + taies changés, matelas aéré 30 min. Lavage à 60 °C.',
+	},
+	{
+		name: 'Chambre rose/Vitres',
+		frequencyDays: 180,
+		lastWeek2026: 22,
+		description:
+			'Cadres puis vitre, de haut en bas, par temps couvert. Rails et joints dépoussiérés.',
+	},
+	// --- Chambre verte ---
+	{
+		name: 'Chambre verte/Vitres',
+		frequencyDays: 180,
+		lastWeek2026: 22,
+		description:
+			'Cadres puis vitre, de haut en bas, par temps couvert. Rails et joints dépoussiérés.',
+	},
+	// ---- Cuisine ----
+	{
+		name: 'Cuisine/Vitres',
+		frequencyDays: 180,
+		lastWeek2026: 22,
+		description:
+			'Cadres puis vitre, de haut en bas, par temps couvert. Rails et joints dépoussiérés.',
+	},
+	{
+		name: 'Cuisine/Cafetière',
+		frequencyDays: 30,
+		lastWeek2026: 12,
+		description:
+			'Détartrer puis rincer deux fois. Réservoir et porte-filtre lavés, filtre à eau selon notice.',
+	},
+	{
+		name: 'Cuisine/Lave-vaisselle',
+		frequencyDays: 30,
+		lastWeek2026: 32,
+		description:
+			'Filtre et bras gicleurs nettoyés, joints essuyés, cycle vide à chaud.',
+	},
+	{
+		name: 'Cuisine/Placard',
+		frequencyDays: 90,
+		lastWeek2026: 19,
+		description:
+			'Vider, trier dates, jeter les périmés, essuyer étagères. Lourds en bas.',
+	},
+	{
+		name: 'Cuisine/Four',
+		frequencyDays: 90,
+		lastWeek2026: 32,
+		description:
+			'Pyrolyse ou produit avec temps de pose, vitre et lèchefrite à part. Grilles trempées au bicarbonate.',
+	},
+	{
+		name: 'Cuisine/Frigo',
+		frequencyDays: 30,
+		lastWeek2026: 22,
+		description:
+			'Périmés jetés, clayettes lavées, température ~4 °C vérifiée, joints essuyés.',
+	},
+	{
+		name: 'Cuisine/Micro-ondes',
+		frequencyDays: 7,
+		lastWeek2026: 32,
+		description:
+			'Bol d’eau vinaigrée chauffé 3 à 5 min puis essuyage parois et plateau. Sans gratter à sec.',
+	},
+	{
+		name: 'Cuisine/Surfaces',
+		frequencyDays: 7,
+		lastWeek2026: 32,
+		description: 'Plan de travail et crédence dégraissés, rincés, séchés.',
+	},
+	{
+		name: 'Cuisine/Gazinière',
+		frequencyDays: 7,
+		lastWeek2026: 32,
+		description:
+			'Grilles et chapeaux trempés, plaque dégraissée, gicleurs débouchés si flamme irrégulière.',
+	},
+	{
+		name: 'Cuisine/Évier',
+		frequencyDays: 7,
+		lastWeek2026: 32,
+		description:
+			'Cuve et robinet détartrés et désinfectés, bonde et trop-plein nettoyés.',
+	},
+	// ---- Toilettes ----
+	{
+		name: 'Toilettes/Toilettes',
+		frequencyDays: 7,
+		lastWeek2026: 34,
+		description:
+			'Cuvette dedans et dehors, abattant et charnières, chasse et poignée, sol autour. Laisser agir le produit.',
+	},
+	{
+		name: 'Toilettes/Lavabo',
+		frequencyDays: 7,
+		lastWeek2026: 34,
+		description: 'Vasque et robinet, miroir, poubelle vidée, savon rechargé.',
+	},
+	// ---- Salle de bain ----
+	{
+		name: 'Salle de bain/Joints',
+		frequencyDays: 90,
+		lastWeek2026: 12,
+		description:
+			'Brossés au bicarbonate + vinaigre, rincés, séchés. Aérer pour limiter les moisissures.',
+	},
+	{
+		name: 'Salle de bain/Calcaire',
+		frequencyDays: 14,
+		lastWeek2026: 34,
+		description:
+			'Vinaigre sur paroi, robinetterie et pommeau (trempage), rinçage abondant.',
+	},
+	{
+		name: 'Salle de bain/Canalisations',
+		frequencyDays: 30,
+		lastWeek2026: 34,
+		description:
+			'Bicarbonate + vinaigre + eau chaude, temps de pose. Pas de soude sauf bouchon avéré.',
+	},
+	{
+		name: 'Salle de bain/Porte-serviettes',
+		frequencyDays: 7,
+		lastWeek2026: 34,
+		description:
+			'Serviettes lavées, barre et crochets dépoussiérés et essuyés.',
+	},
+	{
+		name: 'Salle de bain/Baignoire',
+		frequencyDays: 7,
+		lastWeek2026: 34,
+		description:
+			'Parois et fond anti-calcaire, joints, bonde et trop-plein. Rincer et sécher.',
+	},
+	{
+		name: 'Salle de bain/Miroir',
+		frequencyDays: 14,
+		lastWeek2026: 34,
+		description: 'Microfibre + eau vinaigrée, sans détremper cadre et bords.',
+	},
+	{
+		name: 'Salle de bain/Lavabo',
+		frequencyDays: 7,
+		lastWeek2026: 34,
+		description: 'Vasque, robinet, bonde (cheveux), miroir, savon rechargé.',
+	},
+	// ---- Salon ----
+	{
+		name: 'Salon/Vitres',
+		frequencyDays: 180,
+		lastWeek2026: 22,
+		description:
+			'Cadres puis vitre, de haut en bas, par temps couvert. Rails et joints dépoussiérés.',
+	},
+	{
+		name: 'Salon/Placards',
+		frequencyDays: 90,
+		lastWeek2026: null, // no marks → no completion
+		description:
+			'Vider, trier (donner, jeter), dépoussiérer, ranger par usage.',
+	},
+	// ---- Terrasse ----
+	{
+		name: 'Terrasse/Sol',
+		frequencyDays: 60,
+		lastWeek2026: 25,
+		description:
+			'Balayé puis lavé, joints désherbés, mobilier contrôlé selon saison.',
+	},
+];
+
+const now: string = Temporal.Now.instant().toString({
+	smallestUnit: 'millisecond',
+});
+
+const client = createClient({ url: DB_URL });
+
+let createdRoutines = 0;
+let skippedRoutines = 0;
+let addedCompletions = 0;
+let skippedCompletions = 0;
+
+await client.execute({
+	sql: 'INSERT OR IGNORE INTO projects (name) VALUES (?)',
+	args: [PROJECT],
+});
+const proj = await client.execute({
+	sql: 'SELECT id FROM projects WHERE name = ?',
+	args: [PROJECT],
+});
+const projectId = Number(proj.rows[0].id);
+
+for (const r of ROUTINES) {
+	const existing = await client.execute({
+		sql: 'SELECT id FROM routines WHERE project_id = ? AND name = ? LIMIT 1',
+		args: [projectId, r.name],
+	});
+	let routineId: number;
+	if (existing.rows.length > 0) {
+		routineId = Number(existing.rows[0].id);
+		console.log(`skip  [${PROJECT}] ${r.name} (exists)`);
+		skippedRoutines++;
+	} else {
+		const maxId = await client.execute(
+			'SELECT COALESCE(MAX(id), 0) AS m FROM routines',
+		);
+		routineId = Number(maxId.rows[0].m) + 1;
+
+		await client.execute({
+			sql: `INSERT INTO routines
+				(id, version, project_id, name, frequency_days, description, archived, archived_at, created_at)
+				VALUES (?, 1, ?, ?, ?, ?, 0, NULL, ?)`,
+			args: [routineId, projectId, r.name, r.frequencyDays, r.description, now],
+		});
+		console.log(`add   [${PROJECT}] ${r.name} every ${r.frequencyDays}d`);
+		createdRoutines++;
+	}
+
+	if (r.lastWeek2026 === null) {
+		console.log(`      ↳ no marks on sheet → no completion`);
+		continue;
+	}
+	const done = await client.execute({
+		sql: 'SELECT id FROM routine_completions WHERE routine_id = ? LIMIT 1',
+		args: [routineId],
+	});
+	if (done.rows.length > 0) {
+		skippedCompletions++;
+		continue;
+	}
+	const ver = await client.execute({
+		sql: 'SELECT version FROM routines WHERE id = ? ORDER BY version DESC LIMIT 1',
+		args: [routineId],
+	});
+	const doneAt = isoWeekSaturday(2026, r.lastWeek2026);
+	await client.execute({
+		sql: `INSERT INTO routine_completions (routine_id, routine_version, done_at, note)
+			VALUES (?, ?, ?, ?)`,
+		args: [
+			routineId,
+			Number(ver.rows[0].version),
+			doneAt,
+			`Dernière coche feuille Sheet1 — S${r.lastWeek2026}/2026 (jour exact inconnu ; samedi par convention).`,
+		],
+	});
+	console.log(`      ↳ done S${r.lastWeek2026}/2026 → ${doneAt}`);
+	addedCompletions++;
+}
+
+const projects = await client.execute('SELECT COUNT(*) AS c FROM projects');
+const routines = await client.execute(
+	'SELECT COUNT(*) AS c FROM routines WHERE version = 1 AND archived = 0',
+);
+const completions = await client.execute(
+	'SELECT COUNT(*) AS c FROM routine_completions',
+);
+console.log(
+	`Done. +${createdRoutines} routines (${skippedRoutines} skipped), ` +
+		`+${addedCompletions} completions (${skippedCompletions} skipped). ` +
+		`DB now: ${projects.rows[0].c} projects, ${routines.rows[0].c} routines (v1), ${completions.rows[0].c} completions. ` +
+		`Search TODO(you) to finish frequencies.`,
+);
+client.close();