import type { RequestHandler } from './$types';
import { getDb } from '$lib/server/db';
import { loadAssignees, mapTaskRow } from '$lib/server/task-helpers';

function clamp(n: number, min: number, max: number) {
	return Math.max(min, Math.min(max, n));
}

/** Subquery compartido: filtra a tareas visibles para el usuario (grupo permitido + miembro activo). */
const GROUP_GATING_SQL =
	`(t.group_id IS NULL OR (EXISTS (SELECT 1 FROM allowed_groups ag WHERE ag.group_id = t.group_id AND ag.status = 'allowed') AND EXISTS (SELECT 1 FROM group_members gm WHERE gm.group_id = t.group_id AND gm.user_id = ? AND gm.is_active = 1) AND EXISTS (SELECT 1 FROM groups g WHERE g.id = t.group_id AND COALESCE(g.active,1)=1 AND COALESCE(g.archived,0)=0)))`;

/** Añade filtro de búsqueda por descripción si procede. */
function addSearchFilter(whereParts: string[], params: any[], search: string): void {
	if (!search) return;
	whereParts.push(`t.description LIKE ? ESCAPE '\\'`);
	params.push(`%${search.replace(/%/g, '\\%').replace(/_/g, '\\_')}%`);
}

/** Ejecuta COUNT con los whereParts + params dados y devuelve el total. */
function countTasks(db: any, whereParts: string[], params: any[]): number {
	const row = db
		.prepare(
			`SELECT COUNT(*) AS cnt
			 FROM tasks t
			 INNER JOIN task_assignments a ON a.task_id = t.id
			 WHERE ${whereParts.join(' AND ')}`
		)
		.get(...params) as any;
	return Number(row?.cnt || 0);
}

/** Construye la respuesta JSON paginada estándar. */
function jsonPage(items: any[], page: number, limit: number, total: number, offset: number): Response {
	return new Response(JSON.stringify({
		items,
		page,
		limit,
		total,
		hasMore: offset + items.length < total
	}), {
		status: 200,
		headers: { 'content-type': 'application/json; charset=utf-8', 'cache-control': 'no-store' }
	});
}

export const GET: RequestHandler = async (event) => {
	// Requiere sesión
	const userId = event.locals.userId ?? null;
	if (!userId) {
		return new Response('Unauthorized', { status: 401 });
	}

	const url = new URL(event.request.url);
	const search = (url.searchParams.get('search') || '').trim();
	const status = (url.searchParams.get('status') || 'open').trim().toLowerCase();
	const page = clamp(parseInt(url.searchParams.get('page') || '1', 10) || 1, 1, 100000);
	const limit = clamp(parseInt(url.searchParams.get('limit') || '20', 10) || 20, 1, 100);
	const orderParam = (url.searchParams.get('order') || 'due').trim().toLowerCase();
	const order = orderParam === 'group_then_due' ? 'group_then_due' : 'due';
	const dueBeforeParam = (url.searchParams.get('dueBefore') || '').trim();
	const soonDaysParam = parseInt(url.searchParams.get('soonDays') || '', 10);
	const soonDays = Number.isFinite(soonDaysParam) && soonDaysParam >= 0 ? Math.min(soonDaysParam, 365) : null;
	let dueCutoff: string | null = dueBeforeParam || null;
	if (!dueCutoff && soonDays != null) {
		const d = new Date();
		d.setUTCDate(d.getUTCDate() + soonDays);
		dueCutoff = d.toISOString().slice(0, 10);
	}

	// Acepta "open" (por defecto) o "recent" (completadas <24h)
	if (status !== 'open' && status !== 'recent') {
	 return new Response('Bad Request', { status: 400 });
	}

	const offset = (page - 1) * limit;

	const db = await getDb();

	if (status === 'recent') {
		// Construir filtros para tareas completadas en <24h asignadas al usuario.
		const whereParts = [
			`a.user_id = ?`,
			`(COALESCE(t.completed, 0) = 1 OR t.completed_at IS NOT NULL)`,
			`t.completed_at IS NOT NULL AND t.completed_at >= strftime('%Y-%m-%d %H:%M:%f','now','-24 hours')`,
			GROUP_GATING_SQL
		];
		const params: any[] = [userId, userId];
		addSearchFilter(whereParts, params, search);
		const total = countTasks(db, whereParts, params);

		// Items (order by completed_at DESC)
		const itemsRows = db
			.prepare(
				`SELECT t.id, t.description, t.due_date, t.group_id, t.display_code, COALESCE(t.completed,0) as completed, t.completed_at
         FROM tasks t
         INNER JOIN task_assignments a ON a.task_id = t.id
         WHERE ${whereParts.join(' AND ')}
         ORDER BY t.completed_at DESC, t.id DESC
         LIMIT ? OFFSET ?`
			)
			.all(...params, limit, offset) as any[];

		const items = itemsRows.map((r) => ({
			id: Number(r.id),
			description: String(r.description || ''),
			due_date: r.due_date ? String(r.due_date) : null,
			group_id: r.group_id ? String(r.group_id) : null,
			display_code: r.display_code != null ? Number(r.display_code) : null,
			completed: Number(r.completed || 0) === 1,
			completed_at: r.completed_at ? String(r.completed_at) : null,
			assignees: [] as string[]
		}));

		loadAssignees(db, items, userId);
		return jsonPage(items, page, limit, total, offset);
	}

	// OPEN (comportamiento existente)
	// Construir filtros dinámicos (con gating por grupo permitido y membresía activa)
	const whereParts = [
		`a.user_id = ?`,
		`COALESCE(t.completed, 0) = 0`,
		`t.completed_at IS NULL`,
		GROUP_GATING_SQL
	];
	const params: any[] = [userId, userId];

	addSearchFilter(whereParts, params, search);

	if (dueCutoff) {
		whereParts.push(`t.due_date IS NOT NULL AND t.due_date <= ?`);
		params.push(dueCutoff);
	}

	const total = countTasks(db, whereParts, params);

	// Items
	const orderBy =
		order === 'group_then_due'
			? `(t.group_id IS NULL) ASC, g.name COLLATE NOCASE ASC, CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END, t.due_date ASC, t.id ASC`
			: `CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END, t.due_date ASC, t.id ASC`;

	const itemsRows = db
		.prepare(
			`SELECT t.id, t.description, t.due_date, t.group_id, t.display_code
       FROM tasks t
       LEFT JOIN groups g ON g.id = t.group_id
       INNER JOIN task_assignments a ON a.task_id = t.id
       WHERE ${whereParts.join(' AND ')}
       ORDER BY ${orderBy}
       LIMIT ? OFFSET ?`
		)
		.all(...params, limit, offset) as any[];

	const items = itemsRows.map(mapTaskRow);

	loadAssignees(db, items, userId);
	return jsonPage(items, page, limit, total, offset);
};
