import type { PageServerLoad } from './$types';
import { withRetry } from '$lib/server/db';
import { serializeData } from '$lib/utils/serialize';

const locationTypeTranslations: Record<string, string> = {
	mall: 'Centre commercial',
	store: 'Magasin',
	restaurant: 'Restaurant',
	cafe: 'Café',
	hotel: 'Hôtel',
	airport: 'Aéroport',
	station: 'Gare',
	hospital: 'Hôpital',
	gym: 'Salle de sport',
	office: 'Bureau',
	outdoor: 'Extérieur',
	indoor: 'Intérieur',
	interactive: 'Interactif',
	event: 'Événementiel',
	other: 'Autre',
	supermarket: 'Supermarché'
};

const normalizeLocationType = (value: any): string | null => {
	if (!value) return null;
	if (typeof value === 'string') {
		return value.includes(':') ? value.split(':')[1] : value;
	}
	const id = value.id ?? value;
	if (typeof id === 'string') {
		return id.includes(':') ? id.split(':')[1] : id;
	}
	if (typeof id?.id === 'string') {
		return id.id.includes(':') ? id.id.split(':')[1] : id.id;
	}
	return null;
};

export const load: PageServerLoad = async ({ locals, url }) => {
	if (!locals.user) {
		return { ads: [], campaign: null, campaignBookings: [], countries: [], locationTypes: [], providers: [] };
	}

	const userId = locals.user.id.replace('user:', '');
	const campaignId = url.searchParams.get('edit');

	try {
		return await withRetry(async (db) => {
			// Récupérer toutes les publicités de l'utilisateur
			const adsResult = await db.query<[any[]]>(
				`SELECT 
					*
				 FROM ad 
				 WHERE user = type::thing("user", $userId) 
				 ORDER BY updated_at DESC`,
				{ userId }
			);

			const ads = serializeData(adsResult[0] || []);

			// Récupérer les pays
			const countriesResult = await db.query<[any[]]>(
				`SELECT * FROM country ORDER BY name ASC`
			);

			// Récupérer les types de lieux depuis les locations existantes
			const locationTypesResult = await db.query<[any[]]>(
				`SELECT location_type FROM location WHERE location_type != NONE GROUP BY location_type`
			);

			let locationTypeValues = (locationTypesResult[0] || [])
				.map((item: any) => normalizeLocationType(item.location_type))
				.filter((type: string | null) => !!type) as string[];

			// Fallback: si aucun type, essayer depuis les écrans
			if (locationTypeValues.length === 0) {
				const screenTypesResult = await db.query<[any[]]>(
					`SELECT type FROM screen WHERE type != NONE GROUP BY type`
				);
				locationTypeValues = (screenTypesResult[0] || [])
					.map((item: any) => item.type)
					.filter((type: string) => !!type);
			}

			// Récupérer les prestataires
			const providersResult = await db.query<[any[]]>(
				`SELECT * FROM provider ORDER BY name ASC`
			);

			// Si on est en mode édition, charger la campagne ET ses bookings en parallèle
			let campaign = null;
			let campaignBookings: any[] = [];
			if (campaignId) {
				const [campaignResult, bookingsResult] = await Promise.all([
					db.query<[any[]]>(
					`SELECT *, 
						ad.name as ad_name,
						(SELECT count() FROM campaign_booking WHERE campaign = $parent.id GROUP ALL)[0].count as screens_count,
						(SELECT math::sum(price) FROM campaign_booking WHERE campaign = $parent.id GROUP ALL)[0]["math::sum(price)"] as budget_used
					FROM campaign 
					WHERE id = type::thing("campaign", $campaignId) 
					AND user = type::thing("user", $userId)`,
					{ campaignId, userId }
				),
				db.query<[any[]]>(
					`SELECT 
						*,
						screen.* as screen_info,
						screen.location.* as location_info
					FROM campaign_booking 
					WHERE campaign = type::thing("campaign", $campaignId)
					FETCH screen, screen.location`,
					{ campaignId }
				)
			]);
			campaign = campaignResult[0]?.[0] || null;
			campaignBookings = bookingsResult[0] || [];

			// Pour les campagnes automatiques, ajouter aussi les écrans du champ screens
			if (campaign?.screens && Array.isArray(campaign.screens) && campaign.screens.length > 0) {
				const screenIds = campaign.screens.map((s: any) => {
					if (typeof s === 'string') return s.replace('screen:', '');
					if (s?.id) {
						const id = typeof s.id === 'string' ? s.id : s.id?.id || s.id;
						return typeof id === 'string' ? id.replace('screen:', '') : id;
					}
					return s;
				}).filter((id: any) => id);

				if (screenIds.length > 0) {
					try {
						const screenThings = screenIds.map((id: string) => `type::thing("screen", "${id}")`).join(', ');
						const batchResult = await db.query<[any[]]>(
							`SELECT *, location.* as location_info FROM screen WHERE id IN [${screenThings}] FETCH location`
						);
						const screens = batchResult[0] || [];
						const startDate = campaign.start_date ? new Date(campaign.start_date) : new Date();
						const endDate = campaign.end_date ? new Date(campaign.end_date) : new Date();
						const daysDiff = Math.ceil((endDate.getTime() - startDate.getTime()) / (1000 * 60 * 60 * 24)) + 1;
						const weeks = Math.max(1, Math.ceil(daysDiff / 7));
						const existingScreenIds = new Set(campaignBookings.map((b: any) =>
							typeof b.screen === 'string' ? b.screen : b.screen?.id
						));
						for (const screen of screens) {
							const screenId = typeof screen.id === 'string' ? screen.id : screen.id?.id || screen.id;
							if (!existingScreenIds.has(screenId)) {
								const weeklyPrice = Number(screen.weekly_price) || 178;
								campaignBookings.push({
									id: `auto_${screen.id}`,
									campaign: campaign.id,
									screen, screen_info: screen,
									location_info: screen.location_info,
									start_date: campaign.start_date,
									end_date: campaign.end_date,
									slot_type: 'week',
									price: weeklyPrice * weeks,
									status: 'active',
									is_auto: true
								});
							}
						}
					} catch (e) {
						console.error('Error batch fetching auto screens:', e);
					}
				}
			}
			}

			return {
				ads,
				campaign: campaign ? serializeData(campaign) : null,
				campaignBookings: serializeData(campaignBookings),
				countries: serializeData(countriesResult[0] || []),
				locationTypes: locationTypeValues
					.map((type: string) => ({
						name: type,
						label: locationTypeTranslations[type] || type.charAt(0).toUpperCase() + type.slice(1)
					}))
					.sort((a, b) => a.label.localeCompare(b.label)),
				providers: serializeData(providersResult[0] || [])
			};
		});
	} catch (error) {
		console.error('[campaigns/new] CRITICAL ERROR:', error);
		console.error('[campaigns/new] Error stack:', error instanceof Error ? error.stack : 'no stack');
		return { ads: [], campaign: null, campaignBookings: [], countries: [], locationTypes: [], providers: [] };
	}
};