import { db as defaultDb } from '$lib/server/db';
import { routines, routineCompletions, projects } from '$lib/server/db/schema';
import { eq, inArray, desc, sql } from 'drizzle-orm';
import type { AppDatabase } from './groceries';
import type { Routine, RoutineCompletion } from './types';
import {
	computeRoutineState,
	computeHealthScore,
	computeProjectHealthSeries,
	type RoutineState,
	type HealthPoint,
} from '$lib/shared/routine-metrics';

// ponytail: next routine id via MAX(id)+1; fine while single-user.

export async function nextRoutineId(db: AppDatabase): Promise<number> {
	const [{ maxId }] = await db
		.select({ maxId: sql<number>`COALESCE(MAX(${routines.id}), 0)` })
		.from(routines);
	return (maxId ?? 0) + 1;
}

export async function add(
	projectId: number,
	name: string,
	frequencyDays: number,
	db = defaultDb,
): Promise<Routine> {
	const id = await nextRoutineId(db);
	const [inserted] = await db
		.insert(routines)
		.values({
			id,
			version: 1,
			projectId,
			name,
			frequencyDays,
			createdAt: Temporal.Now.instant().toString({
				smallestUnit: 'millisecond',
			}),
		})
		.returning();
	return inserted;
}

/** Edit = insert a version+1 snapshot, copying unchanged fields forward. */
export async function edit(
	id: number,
	fields: Partial<Pick<Routine, 'name' | 'frequencyDays' | 'description'>>,
	db = defaultDb,
): Promise<Routine> {
	const [current] = await db
		.select()
		.from(routines)
		.where(eq(routines.id, id))
		.orderBy(sql`${routines.version} DESC`)
		.limit(1);
	if (!current) throw new Error('Routine not found');

	const [inserted] = await db
		.insert(routines)
		.values({
			id: current.id,
			version: current.version + 1,
			projectId: current.projectId,
			name: fields.name ?? current.name,
			frequencyDays: fields.frequencyDays ?? current.frequencyDays,
			description: fields.description ?? current.description,
			archived: current.archived,
			archivedAt: current.archivedAt,
			createdAt: current.createdAt,
		})
		.returning();
	return inserted;
}

export async function archive(id: number, db = defaultDb): Promise<void> {
	const [current] = await db
		.select()
		.from(routines)
		.where(eq(routines.id, id))
		.orderBy(sql`${routines.version} DESC`)
		.limit(1);
	if (!current) throw new Error('Routine not found');

	await db.insert(routines).values({
		...current,
		version: current.version + 1,
		archived: true,
		archivedAt: Temporal.Now.instant().toString({
			smallestUnit: 'millisecond',
		}),
	});
}

export interface ArchivedRoutine {
	id: number;
	name: string;
	frequencyDays: number;
	createdAt: string;
	archivedAt: string;
}

/** Archived routines of a project, most recently archived first. */
export async function loadArchived(
	projectId: number,
	db = defaultDb,
): Promise<ArchivedRoutine[]> {
	const versions = await db
		.select()
		.from(routines)
		.where(eq(routines.projectId, projectId));

	// Max-version row per routine id; archived = max snapshot archived.
	const current = new Map<number, Routine>();
	for (const r of versions) {
		const existing = current.get(r.id);
		if (!existing || r.version > existing.version) current.set(r.id, r);
	}
	return (
		[...current.values()]
			.filter((r) => r.archived)
			// Rows archived before archived_at existed fall back to creation date.
			.sort((a, b) => ((a.archivedAt ?? '') < (b.archivedAt ?? '') ? 1 : -1))
			.map((r) => ({
				id: r.id,
				name: r.name,
				frequencyDays: r.frequencyDays,
				createdAt: r.createdAt,
				archivedAt: r.archivedAt ?? r.createdAt,
			}))
	);
}

export async function loadAllVersions(
	routineId: number,
	db = defaultDb,
): Promise<Routine[]> {
	return db
		.select()
		.from(routines)
		.where(eq(routines.id, routineId))
		.orderBy(routines.version);
}

/** Current (max-version) snapshot of a routine, or undefined when unknown. */
export async function loadCurrent(
	routineId: number,
	db = defaultDb,
): Promise<Routine | undefined> {
	const [row] = await db
		.select()
		.from(routines)
		.where(eq(routines.id, routineId))
		.orderBy(sql`${routines.version} DESC`)
		.limit(1);
	return row;
}

export async function loadCompletions(
	routineId: number,
	db = defaultDb,
): Promise<RoutineCompletion[]> {
	return db
		.select()
		.from(routineCompletions)
		.where(eq(routineCompletions.routineId, routineId))
		.orderBy(desc(routineCompletions.doneAt));
}

export async function complete(
	routineId: number,
	note: string | null,
	doneAt: string,
	db = defaultDb,
): Promise<RoutineCompletion> {
	const [current] = await db
		.select()
		.from(routines)
		.where(eq(routines.id, routineId))
		.orderBy(sql`${routines.version} DESC`)
		.limit(1);
	if (!current) throw new Error('Routine not found');

	const [inserted] = await db
		.insert(routineCompletions)
		.values({
			routineId: current.id,
			routineVersion: current.version,
			doneAt,
			note,
		})
		.returning();
	return inserted;
}

export interface ProjectDashboard {
	routines: RoutineState[];
	healthScore: number;
	healthSeries: HealthPoint[];
}

export async function loadProjectDashboard(
	projectId: number,
	now: Temporal.Instant,
	db = defaultDb,
): Promise<ProjectDashboard> {
	const versions = await db
		.select()
		.from(routines)
		.where(eq(routines.projectId, projectId));

	// Max-version row per routine id, then drop archived ones.
	const current = new Map<number, Routine>();
	for (const r of versions) {
		const existing = current.get(r.id);
		if (!existing || r.version > existing.version) current.set(r.id, r);
	}
	const routines_ = [...current.values()].filter((r) => !r.archived);
	if (routines_.length === 0)
		return { routines: [], healthScore: 100, healthSeries: [] };

	const completions = await db
		.select()
		.from(routineCompletions)
		.where(
			inArray(
				routineCompletions.routineId,
				routines_.map((r) => r.id),
			),
		);

	const states = routines_.map((r) => computeRoutineState(r, completions, now));
	return {
		routines: states,
		healthScore: computeHealthScore(states),
		healthSeries: computeProjectHealthSeries(routines_, completions, now),
	};
}

export interface ProjectsOverview {
	projects: { project: { id: number; name: string }; healthScore: number }[];
	globalScore: number;
}

export async function loadOverview(
	now: Temporal.Instant,
	db = defaultDb,
): Promise<ProjectsOverview> {
	const activeProjects = await db
		.select({ id: projects.id, name: projects.name })
		.from(projects)
		.where(eq(projects.archived, false));

	const entries = await Promise.all(
		activeProjects.map(async (p) => ({
			project: p,
			healthScore: (await loadProjectDashboard(p.id, now, db)).healthScore,
		})),
	);

	return {
		projects: entries,
		globalScore: computeHealthScore(
			entries.map((e) => ({ health: e.healthScore })),
		),
	};
}