import { json, error } from '@sveltejs/kit';
import type { RequestHandler } from './$types';
import { getSurrealDB } from '$lib/server/db';

export const GET: RequestHandler = async ({ url, locals }) => {
	try {
		const db = await getSurrealDB();

		const country = url.searchParams.get('country');
		const region = url.searchParams.get('region');
		const department = url.searchParams.get('department');
		const locationTypesStr = url.searchParams.get('locationTypes');
		const providersStr = url.searchParams.get('providers');
		const latitude = url.searchParams.get('latitude');
		const longitude = url.searchParams.get('longitude');
		const radiusStr = url.searchParams.get('radius');
		const startDate = url.searchParams.get('start_date');
		const endDate = url.searchParams.get('end_date');

		let query = 'SELECT * FROM screen WHERE is_active = true AND is_online = true';

		// Build filter conditions
		const conditions: string[] = [];

		if (country) {
			let countryName = country;
			// If a country code is provided (e.g., FR), map it to the country name
			if (country.length <= 3) {
				const countryResult = await db.query<[any[]]>(
					`SELECT name FROM country WHERE code = $code LIMIT 1`,
					{ code: country }
				);
				const resolved = countryResult?.[0]?.[0]?.name;
				if (resolved) countryName = resolved;
			}
			conditions.push(`location.country = "${countryName}"`);
		}

		if (region) {
			conditions.push(`location.region = "${region}"`);
		}

		if (department) {
			conditions.push(`location.department = "${department}"`);
		}

		// Type de lieu filter
		if (locationTypesStr) {
			const types = locationTypesStr.split(',').filter((t) => t.trim());
			if (types.length > 0) {
				const typeConditions = types
					.map((t) => `location.location_type = "${t}"`)
					.join(' OR ');
				conditions.push(`(${typeConditions})`);
			}
		}

		// Geo filter if latitude/longitude provided
		// Use Equirectangular approximation which is faster and accurate enough for moderate distances
		if (latitude && longitude && radiusStr) {
			const lat = parseFloat(latitude);
			const lon = parseFloat(longitude);
			const radius = parseFloat(radiusStr);

			if (!isNaN(lat) && !isNaN(lon) && !isNaN(radius)) {
				// Equirectangular approximation: 
				// dx = (lon2 - lon1) * cos((lat1 + lat2) / 2)
				// dy = lat2 - lat1
				// distance = R * sqrt(dx² + dy²) where R = 6371 km
				// Convert degrees to radians: deg * π / 180
				const latRad = lat * Math.PI / 180;
				const cosLat = Math.cos(latRad);
				
				// Pre-calculate constants for the query
				// Distance in degrees: radius / 111.32 (km per degree at equator) adjusted for latitude
				const latDegrees = radius / 111.32;
				const lonDegrees = radius / (111.32 * cosLat);
				
				// Bounding box filter first (fast), then precise distance check
				conditions.push(`location.latitude >= ${lat - latDegrees}`);
				conditions.push(`location.latitude <= ${lat + latDegrees}`);
				conditions.push(`location.longitude >= ${lon - lonDegrees}`);
				conditions.push(`location.longitude <= ${lon + lonDegrees}`);
				
				// More precise distance using equirectangular formula
				// 111.32 km per degree of latitude
				// For longitude: 111.32 * cos(lat) km per degree
				conditions.push(
					`math::sqrt(
						math::pow((location.latitude - ${lat}) * 111.32, 2) + 
						math::pow((location.longitude - ${lon}) * 111.32 * ${cosLat}, 2)
					) <= ${radius}`
				);
			}
		}

		// Combine conditions
		if (conditions.length > 0) {
			query += ' AND ' + conditions.join(' AND ');
		}

		query += ' ORDER BY name ASC FETCH location';

const screens = await db.query<[any[]]>(query);
	let result: any[] = Array.isArray(screens) && screens.length > 0 ? screens[0] : [];

		// Vérifier la disponibilité des écrans si des dates sont fournies
		if (startDate && endDate && result.length > 0) {
			// Récupérer toutes les réservations qui chevauchent la période demandée
			const bookingsQuery = `
				SELECT screen, start_date, end_date 
				FROM campaign_booking 
				WHERE status != "cancelled" 
				AND (
					(start_date <= <datetime>$endDate AND end_date >= <datetime>$startDate)
				)
			`;
			
			const bookingsResult = await db.query<[any[]]>(bookingsQuery, {
				startDate: `${startDate}T00:00:00Z`,
				endDate: `${endDate}T23:59:59Z`
			});
			
			const bookings: any[] = Array.isArray(bookingsResult) && bookingsResult.length > 0 ? bookingsResult[0] : [];
			
			// Grouper les réservations par screen ID
			const screenBookingsMap = new Map<string, Array<{ start: string; end: string }>>();
			for (const b of bookings) {
				const screenId = typeof b.screen === 'string' 
					? b.screen.replace('screen:', '')
					: (b.screen?.id || '').toString().replace('screen:', '');
				if (!screenId) continue;
				
				if (!screenBookingsMap.has(screenId)) {
					screenBookingsMap.set(screenId, []);
				}
				screenBookingsMap.get(screenId)!.push({
					start: typeof b.start_date === 'string' ? b.start_date.split('T')[0] : new Date(b.start_date).toISOString().split('T')[0],
					end: typeof b.end_date === 'string' ? b.end_date.split('T')[0] : new Date(b.end_date).toISOString().split('T')[0],
				});
			}
			
			// Calculer les semaines de la campagne (utilise UTC pour éviter les bugs DST)
			const campStart = new Date(`${startDate}T00:00:00Z`);
			const campEnd = new Date(`${endDate}T23:59:59Z`);
			const campaignWeeks: Array<{ start: string; end: string }> = [];
			const cursor = new Date(campStart);
			while (cursor < campEnd) {
				const weekStart = new Date(cursor);
				const weekEnd = new Date(cursor);
				weekEnd.setUTCDate(weekEnd.getUTCDate() + 6);
				if (weekEnd > campEnd) weekEnd.setTime(campEnd.getTime());
				campaignWeeks.push({
					start: weekStart.toISOString().split('T')[0],
					end: weekEnd.toISOString().split('T')[0],
				});
				cursor.setUTCDate(cursor.getUTCDate() + 7);
			}
			
			// Marquer les écrans avec leurs semaines disponibles
			result = result.map((screen: any) => {
				const screenId = typeof screen.id === 'string' 
					? screen.id.replace('screen:', '') 
					: screen.id;
				const bookedPeriods = screenBookingsMap.get(screenId) || [];
				
				// Calculer les semaines disponibles
				const availableWeeks = campaignWeeks.filter(week => {
					const weekStart = new Date(week.start);
					const weekEnd = new Date(week.end);
					// La semaine est disponible si aucune réservation ne la chevauche
					return !bookedPeriods.some(bp => {
						const bpStart = new Date(bp.start);
						const bpEnd = new Date(bp.end);
						return bpStart <= weekEnd && bpEnd >= weekStart;
					});
				});
				
				return {
					...screen,
					available: availableWeeks.length === campaignWeeks.length, // fully available
					partially_available: availableWeeks.length > 0 && availableWeeks.length < campaignWeeks.length,
					available_weeks: availableWeeks,
					total_weeks: campaignWeeks.length,
					booked_periods: bookedPeriods,
				};
			});
		} else {
			// Si pas de dates, tous les écrans sont considérés comme disponibles
			result = result.map((screen: any) => ({
				...screen,
				available: true,
				partially_available: false,
				available_weeks: [],
				total_weeks: 0,
				booked_periods: [],
			}));
		}

		return json(result);
	} catch (err) {
		console.error('Screen filter error:', err);
		throw error(500, `Failed to filter screens: ${err}`);
	}
};