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

/** Map a DB row to an overview item (includes group_name). */
function mapOverviewRow(r: any) {
	return {
		...mapTaskRow(r),
		group_name: r.group_name != null ? String(r.group_name) : null
	};
}

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 orderParam = (url.searchParams.get('order') || 'due').trim().toLowerCase();
	const order = orderParam === 'group_then_due' ? 'group_then_due' : 'due';

	const db = await getDb();

	// Orden para "assigned"
	const assignedOrder =
		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`;

	// Tareas asignadas al usuario (abiertas)
	const assignedRows = db
		.prepare(
			`SELECT t.id, t.description, t.due_date, t.group_id, t.display_code, g.name AS group_name
       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 a.user_id = ?
         AND COALESCE(t.completed, 0) = 0
         AND t.completed_at IS NULL
         AND (
           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)
           )
         )
       ORDER BY ${assignedOrder}`
		)
		.all(userId, userId) as any[];

	const assigned = assignedRows.map(mapOverviewRow);

	// Cargar asignados completos para "assigned"
	loadAssignees(db, assigned, null);

	// Orden para "unassigned"
	const unassignedOrder =
		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`;

	// Tareas sin responsable (solo de grupos permitidos donde soy miembro activo)
	const unassignedRows = db
		.prepare(
			`SELECT t.id, t.description, t.due_date, t.group_id, t.display_code, g.name AS group_name
       FROM tasks t
       LEFT JOIN groups g ON g.id = t.group_id
       WHERE t.group_id IS NOT NULL
         AND COALESCE(t.completed, 0) = 0
         AND t.completed_at IS NULL
         AND NOT EXISTS (SELECT 1 FROM task_assignments a WHERE a.task_id = t.id)
         AND 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)
       ORDER BY ${unassignedOrder}`
		)
		.all(userId) as any[];

	const unassigned = unassignedRows.map(mapOverviewRow);

	return new Response(JSON.stringify({ assigned, unassigned }), {
		status: 200,
		headers: { 'content-type': 'application/json; charset=utf-8', 'cache-control': 'no-store' }
	});
};
