import { db, tickets } from "@/database";
import {
	type ChatInputCommandInteraction,
	type Client,
	SlashCommandBuilder,
} from "discord.js";
import { and, eq, gte, isNotNull, ne } from "drizzle-orm";

const commandData = new SlashCommandBuilder()
	.setName("stats")
	.setDescription("View ticket closure stats")
	.addSubcommand((sub) =>
		sub
			.setName("overall")
			.setDescription("View overall and hourly ticket stats")
			.addIntegerOption((opt) =>
				opt
					.setName("days")
					.setDescription("Number of days to look back (default: all time)")
					.setRequired(false)
					.setMinValue(1),
			),
	)
	.addSubcommand((sub) =>
		sub
			.setName("leaderboard")
			.setDescription("View the moderator leaderboard")
			.addStringOption((opt) =>
				opt
					.setName("order_by")
					.setDescription("Metric to order the leaderboard by")
					.setRequired(false)
					.addChoices(
						{ name: "Median TTC", value: "median" },
						{ name: "Mean TTC", value: "mean" },
						{ name: "Tickets Closed", value: "count" },
					),
			)
			.addIntegerOption((opt) =>
				opt
					.setName("days")
					.setDescription("Number of days to look back (default: all time)")
					.setRequired(false)
					.setMinValue(1),
			),
	);

function formatMs(ms: number) {
	const secs = Math.floor(ms / 1000);
	if (secs < 60) return `${secs}s`;
	const mins = Math.floor(secs / 60);
	if (mins < 60) return `${mins}m ${secs % 60}s`;
	const hours = Math.floor(mins / 60);
	if (hours < 24) return `${hours}h ${mins % 60}m`;
	const days = Math.floor(hours / 24);
	return `${days}d ${hours % 24}h`;
}

function getStats(durations: number[]) {
	if (durations.length === 0) return { mean: 0, median: 0, count: 0 };
	const sum = durations.reduce((a, b) => a + b, 0);
	const mean = sum / durations.length;

	const sorted = [...durations].sort((a, b) => a - b);
	const mid = Math.floor(sorted.length / 2);
	const median =
		sorted.length % 2 !== 0 ? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2;

	return { mean, median, count: durations.length };
}

async function execute(
	client: Client,
	interaction: ChatInputCommandInteraction,
) {
	await interaction.deferReply({});

	const days = interaction.options.getInteger("days");
	const cutoffDate = days
		? new Date(Date.now() - days * 24 * 60 * 60 * 1000)
		: null;

	const closedTickets = await db
		.select()
		.from(tickets)
		.where(
			and(
				isNotNull(tickets.closedAt),
				isNotNull(tickets.closedBy),
				ne(tickets.closedBy, "reporter"),
				ne(tickets.closedBy, "system"),
				interaction.guildId
					? eq(tickets.guildId, interaction.guildId)
					: undefined,
				cutoffDate ? gte(tickets.closedAt, cutoffDate) : undefined,
			),
		);

	const timeFrameStr = days ? `(Last ${days} Days)` : "(All Time)";

	if (closedTickets.length === 0) {
		return interaction.editReply(
			`No closed tickets found for statistics ${timeFrameStr}.`,
		);
	}

	const sub = interaction.options.getSubcommand();

	if (sub === "overall") {
		const allDurations = closedTickets.map(
			// biome-ignore lint/style/noNonNullAssertion: .
			(t) => t.closedAt!.getTime() - t.createdAt.getTime(),
		);
		const globalStats = getStats(allDurations);

		const hourlyDurations = new Map<number, number[]>();
		for (const t of closedTickets) {
			// biome-ignore lint/style/noNonNullAssertion: .
			const hour = t.closedAt!.getHours();
			if (!hourlyDurations.has(hour)) hourlyDurations.set(hour, []);
			hourlyDurations
				.get(hour)
				// biome-ignore lint/style/noNonNullAssertion: .
				?.push(t.closedAt!.getTime() - t.createdAt.getTime());
		}

		const rowsData = [];
		for (let i = 0; i < 24; i++) {
			const durations = hourlyDurations.get(i) || [];
			if (durations.length === 0) continue;

			const stats = getStats(durations);

			rowsData.push({
				hour: `${i}`,
				count: String(stats.count),
				mean: formatMs(stats.mean),
				median: formatMs(stats.median),
			});
		}

		let hourlyTableText = "- No data";
		if (rowsData.length > 0) {
			const widths = {
				hour: Math.max(4, ...rowsData.map((r) => r.hour.length)),
				count: Math.max(6, ...rowsData.map((r) => r.count.length)),
				mean: Math.max(8, ...rowsData.map((r) => r.mean.length)),
				median: Math.max(10, ...rowsData.map((r) => r.median.length)),
			};

			const header = [
				"Hour".padEnd(widths.hour),
				"Closed".padEnd(widths.count),
				"Mean TTC".padEnd(widths.mean),
				"Median TTC".padEnd(widths.median),
			].join(" | ");

			const divider = "-".repeat(header.length);

			const rows = rowsData.map((r) =>
				[
					r.hour.padEnd(widths.hour),
					r.count.padEnd(widths.count),
					r.mean.padEnd(widths.mean),
					r.median.padEnd(widths.median),
				].join(" | "),
			);

			hourlyTableText = `\`\`\`\n${[header, divider, ...rows].join("\n")}\n\`\`\``;
		}

		const text = [
			`## Overall Ticket Stats ${timeFrameStr}`,
			`**Total closed**: ${globalStats.count}`,
			`**Mean TTC**: ${formatMs(globalStats.mean)}`,
			`**Median TTC**: ${formatMs(globalStats.median)}`,
			"### Per Hour",
			hourlyTableText,
		].join("\n");

		await interaction.editReply({
			content: text,
			allowedMentions: {
				parse: [],
			},
		});
	} else if (sub === "leaderboard") {
		const modDurations = new Map<string, number[]>();
		for (const t of closedTickets) {
			const modId = t.closedBy;
			if (!modId) continue;
			if (!modDurations.has(modId)) modDurations.set(modId, []);
			modDurations
				.get(modId)
				// biome-ignore lint/style/noNonNullAssertion: .
				?.push(t.closedAt!.getTime() - t.createdAt.getTime());
		}

		const modStats = Array.from(modDurations.entries()).map(
			([modId, durations]) => {
				const stats = getStats(durations);
				return { modId, ...stats };
			},
		);

		const orderBy = interaction.options.getString("order_by") || "median";

		if (orderBy === "mean") modStats.sort((a, b) => a.mean - b.mean);
		else if (orderBy === "count") modStats.sort((a, b) => b.count - a.count);
		else modStats.sort((a, b) => a.median - b.median);

		const rowsData = await Promise.all(
			modStats.map(async (mod, i) => {
				const user = await client.users.fetch(mod.modId).catch(() => null);
				const username = user ? user.username : mod.modId;
				return {
					rank: String(i + 1),
					username,
					count: String(mod.count),
					mean: formatMs(mod.mean),
					median: formatMs(mod.median),
				};
			}),
		);

		const countTitle = orderBy === "count" ? "*Closed" : "Closed";
		const medianTitle = orderBy === "median" ? "*Median TTC" : "Median TTC";
		const meanTitle = orderBy === "mean" ? "*Mean TTC" : "Mean TTC";

		const widths = {
			rank: Math.max(4, ...rowsData.map((r) => r.rank.length)),
			user: Math.max(8, ...rowsData.map((r) => r.username.length)),
			count: Math.max(
				countTitle.length,
				...rowsData.map((r) => r.count.length),
			),
			mean: Math.max(meanTitle.length, ...rowsData.map((r) => r.mean.length)),
			median: Math.max(
				medianTitle.length,
				...rowsData.map((r) => r.median.length),
			),
		};

		const header = [
			"Rank".padEnd(widths.rank),
			"User".padEnd(widths.user),
			countTitle.padEnd(widths.count),
			medianTitle.padEnd(widths.median),
			meanTitle.padEnd(widths.mean),
		].join(" | ");

		const divider = "-".repeat(header.length);

		const rows = rowsData.map((r) =>
			[
				r.rank.padEnd(widths.rank),
				r.username.padEnd(widths.user),
				r.count.padEnd(widths.count),
				r.median.padEnd(widths.median),
				r.mean.padEnd(widths.mean),
			].join(" | "),
		);

		const tableText = [header, divider, ...rows].join("\n");

		await interaction.editReply({
			content: `## Moderator Leaderboard ${timeFrameStr}\n\`\`\`\n${tableText}\n\`\`\``,
			allowedMentions: {
				parse: [],
			},
		});

		return;
	}
}

export default {
	data: commandData,
	execute,
};