import type { PageServerLoad, Actions } from './$types';
import { db } from '$lib/server/db';
import { dailyVerses, dailyCompletions } from '$lib/server/db/schema';
import { eq, sql, asc } from 'drizzle-orm';
import { fail } from '@sveltejs/kit';
import { fetchRandomVerse } from '$lib/server/bible-api';
import { getBookById } from '$lib/server/bible';
import type { DailyVerse } from '$lib/server/db/schema';
import crypto from 'node:crypto';

async function getTodayVerse(): Promise<DailyVerse> {
	// Get the current date (server-side)
	const dateStr = new Date().toLocaleDateString('en-CA', { timeZone: 'America/New_York' });

	// If there's an existing verse for the current date, return it
	const existing = await db.select().from(dailyVerses).where(eq(dailyVerses.date, dateStr)).limit(1);
	if (existing.length > 0) {
		return existing[0];
	}

	// Otherwise get a new random verse
	const apiVerse = await fetchRandomVerse();
	const createdAt = sql`${Math.floor(Date.now() / 1000)}`;

	const newVerse: Omit<DailyVerse, 'createdAt'> = {
		id: crypto.randomUUID(),
		date: dateStr,
		bookId: apiVerse.bookId,
		verseText: apiVerse.verseText,
		reference: apiVerse.reference,
	};

	const [inserted] = await db.insert(dailyVerses).values({ ...newVerse, createdAt }).returning();
	return inserted;
}

export const load: PageServerLoad = async () => {
	const dailyVerse = await getTodayVerse();
	const correctBook = getBookById(dailyVerse.bookId) ?? null;

	return {
		dailyVerse,
		correctBookId: dailyVerse.bookId,
		correctBook
	};
};

export const actions: Actions = {
	submitCompletion: async ({ request }) => {
		const formData = await request.formData();
		const anonymousId = formData.get('anonymousId') as string;
		const date = formData.get('date') as string;
		const guessCount = parseInt(formData.get('guessCount') as string, 10);

		// Validation
		if (!anonymousId || !date || isNaN(guessCount) || guessCount < 1) {
			return fail(400, { error: 'Invalid data' });
		}

		const completedAt = new Date();

		try {
			// Insert with duplicate prevention
			await db.insert(dailyCompletions).values({
				id: crypto.randomUUID(),
				anonymousId,
				date,
				guessCount,
				completedAt,
			});
		} catch (err: any) {
			if (err?.code === 'SQLITE_CONSTRAINT_UNIQUE' || err?.message?.includes('UNIQUE')) {
				return fail(409, { error: 'Already submitted' });
			}
			throw err;
		}

		// Calculate statistics
		const allCompletions = await db
			.select()
			.from(dailyCompletions)
			.where(eq(dailyCompletions.date, date))
			.orderBy(asc(dailyCompletions.completedAt));

		const totalSolves = allCompletions.length;

		// Solve rank: position in time-ordered list
		const solveRank = allCompletions.findIndex(c => c.anonymousId === anonymousId) + 1;

		// Guess rank: count how many had FEWER guesses (ties get same rank)
		const betterGuesses = allCompletions.filter(c => c.guessCount < guessCount).length;
		const guessRank = betterGuesses + 1;

		// Count ties: how many have the SAME guessCount (excluding self)
		const tiedCount = allCompletions.filter(c => c.guessCount === guessCount && c.anonymousId !== anonymousId).length;

		// Average guesses
		const totalGuesses = allCompletions.reduce((sum, c) => sum + c.guessCount, 0);
		const averageGuesses = Math.round((totalGuesses / totalSolves) * 10) / 10;

		// Percentile: what percentage of people you beat (100 - your rank percentage)
		const betterOrEqualCount = allCompletions.filter(c => c.guessCount <= guessCount).length;
		const percentile = Math.round((betterOrEqualCount / totalSolves) * 100);

		return {
			success: true,
			stats: { solveRank, guessRank, totalSolves, averageGuesses, tiedCount, percentile }
		};
	}
};