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

export const load: PageServerLoad = async ({ locals }) => {
	const userId = locals.user?.id;
	
	if (!userId) {
		return {
			invoices: [],
			paidCampaigns: [],
			pendingCampaigns: [],
			stats: { total: 0, paid: 0, pending: 0, overdue: 0 }
		};
	}
	
	const cleanUserId = userId.replace('user:', '');
	const db = await getSurrealDB();
	
	try {
		// Récupérer les factures
		const invoicesResult = await db.query<[any[]]>(`
			SELECT 
				*,
				(SELECT name FROM campaign WHERE id = $parent.campaign)[0].name as campaign_name
			FROM invoice 
			WHERE user = type::thing("user", $userId)
			ORDER BY issue_date DESC
		`, { userId: cleanUserId });
		
		// Récupérer les campagnes payées (active ou completed)
		const paidCampaignsResult = await db.query<[any[]]>(`
			SELECT 
				id, name, status, start_date, end_date, created_at, updated_at,
				budget, total_price, paid_amount,
				cached_screens_count, cached_budget_used,
				(SELECT count() FROM campaign_booking WHERE campaign = $parent.id GROUP ALL)[0].count as booking_count,
				(SELECT id, name FROM ad WHERE id = $parent.ad)[0] as ad
			FROM campaign 
			WHERE user = type::thing("user", $userId) 
				AND status IN ["active", "completed", "live"]
			ORDER BY start_date DESC
		`, { userId: cleanUserId });
		
		// Récupérer les campagnes en attente de paiement
		const pendingCampaignsResult = await db.query<[any[]]>(`
			SELECT 
				id, name, status, start_date, end_date, created_at, updated_at,
				budget, total_price, budget_used, cached_budget_used, cached_screens_count,
				(SELECT count() FROM campaign_booking WHERE campaign = $parent.id GROUP ALL)[0].count as booking_count,
				(SELECT math::sum(price) FROM campaign_booking WHERE campaign = $parent.id GROUP ALL)[0]["math::sum"] as total_booked_price,
				(SELECT id, name FROM ad WHERE id = $parent.ad)[0] as ad
			FROM campaign 
			WHERE user = type::thing("user", $userId) 
				AND status = "pending_payment"
			ORDER BY created_at DESC
		`, { userId: cleanUserId });
		
		// Statistiques
		const statsResult = await db.query<[any[]]>(`
			SELECT
				count() as total,
				math::sum((SELECT total FROM invoice WHERE user = type::thing("user", $userId) AND status = "paid").total) as total_paid,
				count(SELECT * FROM invoice WHERE user = type::thing("user", $userId) AND status = "paid") as paid_count,
				count(SELECT * FROM invoice WHERE user = type::thing("user", $userId) AND status IN ["draft", "sent"]) as pending_count,
				count(SELECT * FROM invoice WHERE user = type::thing("user", $userId) AND status = "overdue") as overdue_count
			FROM invoice WHERE user = type::thing("user", $userId) GROUP ALL
		`, { userId: cleanUserId });
		
		return {
			invoices: serializeData(invoicesResult[0] || []),
			paidCampaigns: serializeData(paidCampaignsResult[0] || []),
			pendingCampaigns: serializeData(pendingCampaignsResult[0] || []),
			stats: {
				total: statsResult[0]?.[0]?.total || 0,
				total_paid: statsResult[0]?.[0]?.total_paid || 0,
				paid: statsResult[0]?.[0]?.paid_count || 0,
				pending: statsResult[0]?.[0]?.pending_count || 0,
				overdue: statsResult[0]?.[0]?.overdue_count || 0
			}
		};
	} catch (error) {
		console.error('Error loading invoices:', error);
		return {
			invoices: [],
			paidCampaigns: [],
			pendingCampaigns: [],
			stats: { total: 0, total_paid: 0, paid: 0, pending: 0, overdue: 0 }
		};
	}
};