import type { Tour } from "@circuy/contracts";
import { and, count, countDistinct, eq } from "drizzle-orm";
import type { Database } from "./db/client.js";
import { tourEvents, tourVersions, tours } from "./db/schema.js";

export type Analytics = {
	version: number;
	starts: number;
	completes: number;
	funnel: Array<{
		stepId: string;
		title: string;
		sessions: number;
		targetLost: number;
	}>;
	choices: Array<{
		stepId: string;
		stepTitle: string;
		optionId: string;
		label: string;
		count: number;
	}>;
};

type Row = {
	type: string;
	stepId: string | null;
	choiceId: string | null;
	events: number;
	sessions: number;
};

export async function loadAnalytics(
	db: Database,
	tourId: string,
	version: number,
): Promise<Analytics | undefined> {
	const published = await db
		.select({ content: tourVersions.content })
		.from(tourVersions)
		.where(
			and(eq(tourVersions.tourId, tourId), eq(tourVersions.version, version)),
		)
		.limit(1)
		.then((rows) => rows[0]);
	if (!published) return undefined;

	const tour = published.content as Tour;
	const rows = (await db
		.select({
			type: tourEvents.type,
			stepId: tourEvents.stepId,
			choiceId: tourEvents.choiceId,
			events: count(),
			sessions: countDistinct(tourEvents.sessionId),
		})
		.from(tourEvents)
		.where(
			and(eq(tourEvents.tourId, tourId), eq(tourEvents.tourVersion, version)),
		)
		.groupBy(tourEvents.type, tourEvents.stepId, tourEvents.choiceId)) as Row[];

	return assemble(tour, version, rows);
}

function n(value: number | string | bigint | undefined): number {
	return Number(value ?? 0);
}

function assemble(tour: Tour, version: number, rows: Row[]): Analytics {
	const starts = n(rows.find((row) => row.type === "start")?.sessions);
	const completes = n(rows.find((row) => row.type === "complete")?.sessions);

	const funnel = tour.steps.map((step) => ({
		stepId: step.id,
		title: step.title,
		sessions: n(
			rows.find((row) => row.type === "step_view" && row.stepId === step.id)
				?.sessions,
		),
		targetLost: n(
			rows.find((row) => row.type === "target_lost" && row.stepId === step.id)
				?.events,
		),
	}));

	const choices: Analytics["choices"] = [];
	for (const step of tour.steps) {
		if (step.type !== "question") continue;
		for (const option of step.options) {
			choices.push({
				stepId: step.id,
				stepTitle: step.title,
				optionId: option.id,
				label: option.label,
				count: n(
					rows.find(
						(row) =>
							row.type === "choice" &&
							row.stepId === step.id &&
							row.choiceId === option.id,
					)?.events,
				),
			});
		}
	}

	return { version, starts, completes, funnel, choices };
}

export async function targetLostByTour(
	db: Database,
	projectId: string,
): Promise<Map<string, number>> {
	const rows = await db
		.select({
			tourId: tourEvents.tourId,
			lost: count(),
		})
		.from(tourEvents)
		.innerJoin(
			tours,
			and(
				eq(tours.id, tourEvents.tourId),
				eq(tours.publishedVersion, tourEvents.tourVersion),
			),
		)
		.where(
			and(eq(tours.projectId, projectId), eq(tourEvents.type, "target_lost")),
		)
		.groupBy(tourEvents.tourId);

	return new Map(rows.map((row) => [row.tourId, Number(row.lost)]));
}