import type { RowDataPacket } from 'mysql2';
import type {
	DashboardData,
	DashboardStats,
	RecentTransaction,
	TopCategory,
	WeeklySales
} from '$lib/types/dashboard';
import db from '$lib/server/db';

function toNumber(value: unknown): number {
	return Number(value ?? 0);
}

function formatDateLocal(date: Date): string {
	const year = date.getFullYear();
	const month = String(date.getMonth() + 1).padStart(2, '0');
	const day = String(date.getDate()).padStart(2, '0');
	return `${year}-${month}-${day}`;
}

function buildWeeklySales(rows: RowDataPacket[]): WeeklySales[] {
	const dayNames = ['Min', 'Sen', 'Sel', 'Rab', 'Kam', 'Jum', 'Sab'];
	const salesMap = new Map<string, number>();

	for (const row of rows) {
		const dateKey = formatDateLocal(new Date(row.hari));
		salesMap.set(dateKey, toNumber(row.total));
	}

	const today = new Date();
	const weeklySales: WeeklySales[] = [];

	for (let i = 6; i >= 0; i--) {
		const date = new Date(today);
		date.setDate(today.getDate() - i);
		const dateKey = formatDateLocal(date);

		weeklySales.push({
			label: dayNames[date.getDay()],
			date: dateKey,
			total: salesMap.get(dateKey) ?? 0,
			isToday: i === 0
		});
	}

	return weeklySales;
}

export async function getDashboardData(memberId: number): Promise<DashboardData> {
	const [statsRows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			COALESCE(SUM(CASE WHEN jenis = 'pendapatan' AND MONTH(tanggal) = MONTH(CURDATE()) AND YEAR(tanggal) = YEAR(CURDATE()) THEN jumlah END), 0) AS pendapatan_bulan,
			COALESCE(SUM(CASE WHEN jenis = 'pengeluaran' AND MONTH(tanggal) = MONTH(CURDATE()) AND YEAR(tanggal) = YEAR(CURDATE()) THEN jumlah END), 0) AS pengeluaran_bulan,
			COALESCE(SUM(CASE WHEN jenis = 'pendapatan' AND tanggal = CURDATE() THEN jumlah END), 0) AS pendapatan_hari,
			COALESCE(SUM(CASE WHEN jenis = 'pengeluaran' AND tanggal = CURDATE() THEN jumlah END), 0) AS pengeluaran_hari,
			COALESCE(SUM(CASE WHEN jenis = 'pendapatan' AND tanggal = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN jumlah END), 0) AS pendapatan_kemarin,
			COUNT(CASE WHEN tanggal = CURDATE() THEN 1 END) AS jumlah_transaksi_hari
		FROM transaksi
		WHERE member_id = ?
		`,
		[memberId]
	);

	const rawStats = statsRows[0];
	const pendapatanBulan = toNumber(rawStats?.pendapatan_bulan);
	const pengeluaranBulan = toNumber(rawStats?.pengeluaran_bulan);
	const pendapatanHari = toNumber(rawStats?.pendapatan_hari);
	const pengeluaranHari = toNumber(rawStats?.pengeluaran_hari);
	const pendapatanKemarin = toNumber(rawStats?.pendapatan_kemarin);

	const perubahanHari =
		pendapatanKemarin > 0
			? ((pendapatanHari - pendapatanKemarin) / pendapatanKemarin) * 100
			: pendapatanHari > 0
				? 100
				: 0;

	const stats: DashboardStats = {
		pendapatanBulan,
		pengeluaranBulan,
		pendapatanHari,
		pengeluaranHari,
		pendapatanKemarin,
		perubahanHari,
		jumlahTransaksiHari: toNumber(rawStats?.jumlah_transaksi_hari)
	};

	const [weeklyRows] = await db.query<RowDataPacket[]>(
		`
		SELECT tanggal AS hari, COALESCE(SUM(jumlah), 0) AS total
		FROM transaksi
		WHERE member_id = ?
			AND sumber = 'penjualan'
			AND tanggal >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
		GROUP BY tanggal
		ORDER BY tanggal ASC
		`,
		[memberId]
	);

	const [recentRows] = await db.query<RowDataPacket[]>(
		`
		SELECT id, jenis, deskripsi, kategori, jumlah, tanggal, created_at
		FROM transaksi
		WHERE member_id = ?
		ORDER BY created_at DESC
		LIMIT 8
		`,
		[memberId]
	);

	const [categoryRows] = await db.query<RowDataPacket[]>(
		`
		SELECT kategori, COALESCE(SUM(jumlah), 0) AS total
		FROM transaksi
		WHERE member_id = ?
			AND sumber = 'penjualan'
			AND tanggal >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
		GROUP BY kategori
		ORDER BY total DESC
		LIMIT 5
		`,
		[memberId]
	);

	const categoryTotal = categoryRows.reduce((sum, row) => sum + toNumber(row.total), 0);

	const recentTransactions: RecentTransaction[] = recentRows.map((row) => ({
		id: row.id,
		jenis: row.jenis,
		deskripsi: row.deskripsi,
		kategori: row.kategori,
		jumlah: toNumber(row.jumlah),
		tanggal: formatDateLocal(new Date(row.tanggal)),
		waktu: new Date(row.created_at).toISOString()
	}));

	const topCategories: TopCategory[] = categoryRows.map((row) => {
		const total = toNumber(row.total);
		return {
			kategori: row.kategori,
			total,
			persen: categoryTotal > 0 ? (total / categoryTotal) * 100 : 0
		};
	});

	return {
		stats,
		weeklySales: buildWeeklySales(weeklyRows),
		recentTransactions,
		topCategories
	};
}