import type { RequestHandler } from './$types';
import { getDb } from '$lib/server/db';

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

	const groupId = event.params.id;
	if (!groupId) {
		return new Response('Bad Request', { status: 400 });
	}

	const url = new URL(event.request.url);
	const unassignedFirst =
		(url.searchParams.get('unassignedFirst') || '').trim().toLowerCase() === 'true';
	const onlyUnassigned =
		(url.searchParams.get('onlyUnassigned') || '').trim().toLowerCase() === 'true';
	let limit = parseInt(url.searchParams.get('limit') || '', 10);
	if (!Number.isFinite(limit) || limit <= 0) limit = 0;
	if (limit > 100) limit = 100;

	const db = await getDb();

	// Gating: grupo permitido + usuario es miembro activo
	const allowed = db
		.prepare(`SELECT 1 FROM allowed_groups WHERE group_id = ? AND status = 'allowed' LIMIT 1`)
		.get(groupId);
	const active = db
		.prepare(
			`SELECT 1 FROM group_members WHERE group_id = ? AND user_id = ? AND is_active = 1 LIMIT 1`
		)
		.get(groupId, userId);

	if (!allowed || !active) {
		return new Response('Forbidden', { status: 403 });
	}

	const orderParts: string[] = [];
	if (unassignedFirst) {
		orderParts.push(
			`CASE WHEN EXISTS (SELECT 1 FROM task_assignments a WHERE a.task_id = t.id) THEN 1 ELSE 0 END ASC`
		);
	}
	orderParts.push(
		`CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END`,
		`t.due_date ASC`,
		`t.id ASC`
	);

	const whereParts = [
		`t.group_id = ?`,
		`COALESCE(t.completed, 0) = 0`,
		`t.completed_at IS NULL`
	];
	if (onlyUnassigned) {
		whereParts.push(
			`NOT EXISTS (SELECT 1 FROM task_assignments a WHERE a.task_id = t.id)`
		);
	}

	const params: any[] = [groupId];

	const sql = `
			SELECT t.id, t.description, t.due_date, t.group_id, t.display_code
       FROM tasks t
       WHERE ${whereParts.join(' AND ')}
       ORDER BY ${orderParts.join(', ')}${limit > 0 ? ' LIMIT ?' : ''}`;

	if (limit > 0) params.push(limit);

	const rows = db.prepare(sql).all(...params) as any[];

	let items = rows.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,
		assignees: [] as string[]
	}));

	// Cargar asignados
	if (items.length > 0 && !onlyUnassigned) {
		const ids = items.map((it) => it.id);
		const placeholders = ids.map(() => '?').join(',');
		const assignRows = db
			.prepare(
				`SELECT task_id, user_id
         FROM task_assignments
         WHERE task_id IN (${placeholders})
         ORDER BY assigned_at ASC`
			)
			.all(...ids) as any[];

		const map = new Map<number, string[]>();
		for (const row of assignRows) {
			const tid = Number(row.task_id);
			const uid = String(row.user_id);
			if (!map.has(tid)) map.set(tid, []);
			map.get(tid)!.push(uid);
		}
		for (const it of items) {
			it.assignees = map.get(it.id) || [];
		}
	}

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