/**
 * 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();