import type { RowDataPacket } from 'mysql2';
import { listMembersForAdmin } from '$lib/server/langganan';
import type { BackofficeDashboard, PaketDistribusi } from '$lib/types/backoffice';
import db from '$lib/server/db';

export async function getPlatformStats() {
	const [rows] = await db.query<RowDataPacket[]>(`
		SELECT
			(SELECT COUNT(*) FROM member) AS total_member,
			(SELECT COUNT(*) FROM member WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AS member_baru_bulan,
			(SELECT COUNT(*) FROM warung) AS total_warung,
			(SELECT COUNT(*) FROM warung WHERE is_publik = 1 AND is_aktif = 1) AS warung_publik,
			(SELECT COUNT(*) FROM transaksi WHERE YEAR(tanggal) = YEAR(CURDATE()) AND MONTH(tanggal) = MONTH(CURDATE())) AS transaksi_bulan_ini
	`);

	const row = rows[0] ?? {};
	return {
		totalMember: Number(row.total_member ?? 0),
		memberBaruBulan: Number(row.member_baru_bulan ?? 0),
		totalWarung: Number(row.total_warung ?? 0),
		warungPublik: Number(row.warung_publik ?? 0),
		transaksiBulanIni: Number(row.transaksi_bulan_ini ?? 0)
	};
}

export async function getEstimasiMrr(): Promise<number> {
	const [rows] = await db.query<RowDataPacket[]>(`
		SELECT
			SUM(
				CASE
					WHEN ml.\`interval\` = 'bulanan' THEN ph.harga
					WHEN ml.\`interval\` = 'tahunan' THEN ph.harga / 12
					ELSE 0
				END
			) AS mrr
		FROM member_langganan ml
		JOIN paket_harga ph ON ph.paket_id = ml.paket_id AND ph.\`interval\` = ml.\`interval\`
		WHERE ml.status = 'aktif'
		  AND (ml.berakhir_at IS NULL OR ml.berakhir_at >= CURDATE())
		  AND ml.paket_id != (SELECT id FROM paket_langganan WHERE kode = 'free' LIMIT 1)
	`);

	return Number(rows[0]?.mrr ?? 0);
}

export async function getBackofficeDashboard(): Promise<BackofficeDashboard> {
	const [platform, members, mrr] = await Promise.all([
		getPlatformStats(),
		listMembersForAdmin(),
		getEstimasiMrr()
	]);

	const aktifLangganan = members.filter((m) => m.langganan_status === 'aktif' && m.paket_kode !== 'free').length;
	const totalForDistribusi = members.length || 1;

	const paketMap = new Map<string, { kode: string; nama: string; jumlah: number }>();
	for (const m of members) {
		const kode = m.paket_kode ?? 'free';
		const nama = m.paket_nama ?? 'Free';
		const existing = paketMap.get(kode);
		if (existing) {
			existing.jumlah += 1;
		} else {
			paketMap.set(kode, { kode, nama, jumlah: 1 });
		}
	}

	const paketDistribusi: PaketDistribusi[] = [...paketMap.values()]
		.sort((a, b) => b.jumlah - a.jumlah)
		.map((p) => ({
			...p,
			persen: Math.round((p.jumlah / totalForDistribusi) * 100)
		}));

	return {
		stats: {
			...platform,
			langgananAktif: aktifLangganan,
			estimasiMrr: mrr
		},
		paketDistribusi,
		members: members.slice(0, 8)
	};
}