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

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

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

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

export class RoutinesService {
	constructor(private readonly db: AppDatabase) {}

	// ponytail: next routine id via MAX(id)+1; fine while single-user.
	private async nextRoutineId(): Promise<number> {
		const [{ maxId }] = await this.db
			.select({ maxId: sql<number>`COALESCE(MAX(${routines.id}), 0)` })
			.from(routines);
		return (maxId ?? 0) + 1;
	}

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

	/** Edit = insert a version+1 snapshot, copying unchanged fields forward. */
	async edit(
		id: number,
		fields: Partial<Pick<Routine, 'name' | 'frequency' | 'description'>>,
	): Promise<Routine> {
		const [current] = await this.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 this.db
			.insert(routines)
			.values({
				id: current.id,
				version: current.version + 1,
				projectId: current.projectId,
				name: fields.name ?? current.name,
				frequency: fields.frequency ?? current.frequency,
				description: fields.description ?? current.description,
				archived: current.archived,
				archivedAt: current.archivedAt,
				createdAt: current.createdAt,
			})
			.returning();
		return inserted;
	}

	async archive(id: number): Promise<void> {
		const [current] = await this.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 this.db.insert(routines).values({
			...current,
			version: current.version + 1,
			archived: true,
			archivedAt: Temporal.Now.instant().toString({
				smallestUnit: 'millisecond',
			}),
		});
	}

	/** Archived routines of a project, most recently archived first. */
	async loadArchived(projectId: number): Promise<ArchivedRoutine[]> {
		const versions = await this.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,
					frequency: r.frequency,
					createdAt: r.createdAt,
					archivedAt: r.archivedAt ?? r.createdAt,
				}))
		);
	}

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

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

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

	async complete(
		routineId: number,
		note: string | null,
		doneAt: string,
	): Promise<RoutineCompletion> {
		const [current] = await this.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 this.db
			.insert(routineCompletions)
			.values({
				routineId: current.id,
				routineVersion: current.version,
				doneAt,
				note,
			})
			.returning();
		return inserted;
	}

	async loadProjectDashboard(
		projectId: number,
		now: Temporal.Instant,
	): Promise<ProjectDashboard> {
		const versions = await this.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 live = [...current.values()].filter((r) => !r.archived);
		if (live.length === 0)
			return { routines: [], healthScore: 100, healthSeries: [] };

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

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

	async loadOverview(now: Temporal.Instant): Promise<ProjectsOverview> {
		const activeProjects = await this.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 this.loadProjectDashboard(p.id, now)).healthScore,
			})),
		);

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