import type { RowDataPacket } from 'mysql2';
import type {
	LaporanData,
	LaporanHarian,
	LaporanHutangPiutang,
	LaporanKategori,
	LaporanRingkasan,
	LaporanTransaksi,
	PeriodeLaporan
} from '$lib/types/laporan';
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 parseDateFromDb(value: unknown): string {
	if (value instanceof Date) {
		return formatDateLocal(value);
	}

	const str = String(value).slice(0, 10);
	const [year, month, day] = str.split('-').map(Number);
	return formatDateLocal(new Date(year, month - 1, day));
}

function startOfToday(): Date {
	const today = new Date();
	today.setHours(0, 0, 0, 0);
	return today;
}

function getPeriodeConfig(periode: PeriodeLaporan): { sql: string; label: string } {
	switch (periode) {
		case 'hari':
			return { sql: 'AND tanggal = CURDATE()', label: 'Hari Ini' };
		case 'minggu':
			return {
				sql: 'AND tanggal >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND tanggal <= CURDATE()',
				label: 'Minggu Ini'
			};
		case 'bulan':
			return {
				sql: 'AND tanggal >= DATE_FORMAT(CURDATE(), "%Y-%m-01") AND tanggal <= CURDATE()',
				label: 'Bulan Ini'
			};
		default:
			return { sql: '', label: 'Semua' };
	}
}

function getChartDateRange(
	periode: PeriodeLaporan,
	tanggalAwal: string | null,
	tanggalAkhir: string | null
): { start: Date; end: Date } {
	const today = startOfToday();

	switch (periode) {
		case 'hari':
			return { start: new Date(today), end: new Date(today) };
		case 'minggu': {
			const start = new Date(today);
			start.setDate(today.getDate() - 6);
			return { start, end: new Date(today) };
		}
		case 'bulan': {
			const start = new Date(today.getFullYear(), today.getMonth(), 1);
			return { start, end: new Date(today) };
		}
		default: {
			const end = tanggalAkhir
				? new Date(
						Number(tanggalAkhir.slice(0, 4)),
						Number(tanggalAkhir.slice(5, 7)) - 1,
						Number(tanggalAkhir.slice(8, 10))
					)
				: new Date(today);
			const start = tanggalAwal
				? new Date(
						Number(tanggalAwal.slice(0, 4)),
						Number(tanggalAwal.slice(5, 7)) - 1,
						Number(tanggalAwal.slice(8, 10))
					)
				: (() => {
						const fallback = new Date(today);
						fallback.setDate(today.getDate() - 13);
						return fallback;
					})();

			end.setHours(0, 0, 0, 0);
			start.setHours(0, 0, 0, 0);
			return { start, end };
		}
	}
}

function buildHarian(rows: RowDataPacket[], startDate: Date, endDate: Date): LaporanHarian[] {
	const dayNames = ['Min', 'Sen', 'Sel', 'Rab', 'Kam', 'Jum', 'Sab'];
	const map = new Map<string, { penjualan: number; pengeluaran: number }>();

	for (const row of rows) {
		const dateKey = parseDateFromDb(row.tanggal);
		const current = map.get(dateKey) ?? { penjualan: 0, pengeluaran: 0 };
		if (row.jenis === 'pendapatan' && row.sumber === 'penjualan') {
			current.penjualan += toNumber(row.jumlah);
		} else if (row.jenis === 'pengeluaran') {
			current.pengeluaran += toNumber(row.jumlah);
		}
		map.set(dateKey, current);
	}

	const start = new Date(startDate);
	const end = new Date(endDate);
	start.setHours(0, 0, 0, 0);
	end.setHours(0, 0, 0, 0);

	const allDays: Date[] = [];
	const cursor = new Date(start);
	while (cursor <= end) {
		allDays.push(new Date(cursor));
		cursor.setDate(cursor.getDate() + 1);
	}

	const maxChartDays = 31;
	const daysToShow =
		allDays.length > maxChartDays ? allDays.slice(allDays.length - maxChartDays) : allDays;

	return daysToShow.map((date) => {
		const dateKey = formatDateLocal(date);
		const data = map.get(dateKey) ?? { penjualan: 0, pengeluaran: 0 };

		return {
			label: dayNames[date.getDay()],
			date: dateKey,
			penjualan: data.penjualan,
			pengeluaran: data.pengeluaran
		};
	});
}

function buildKategori(rows: RowDataPacket[]): LaporanKategori[] {
	const total = rows.reduce((sum, row) => sum + toNumber(row.total), 0);
	return rows.map((row) => {
		const t = toNumber(row.total);
		return {
			kategori: row.kategori,
			total: t,
			persen: total > 0 ? (t / total) * 100 : 0
		};
	});
}

export async function getLaporanData(
	memberId: number,
	periode: PeriodeLaporan = 'bulan',
	memberNama: string,
	warungNama: string
): Promise<LaporanData> {
	const config = getPeriodeConfig(periode);

	const [ringkasanRows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			COALESCE(SUM(CASE WHEN jenis = 'pendapatan' AND sumber = 'penjualan' THEN jumlah END), 0) AS pendapatan_penjualan,
			COALESCE(SUM(CASE WHEN jenis = 'pendapatan' AND sumber = 'catatan' THEN jumlah END), 0) AS pendapatan_lain,
			COALESCE(SUM(CASE WHEN jenis = 'pendapatan' THEN jumlah END), 0) AS total_pendapatan,
			COALESCE(SUM(CASE WHEN jenis = 'pengeluaran' THEN jumlah END), 0) AS total_pengeluaran,
			COUNT(*) AS jumlah_transaksi,
			COUNT(CASE WHEN sumber = 'penjualan' THEN 1 END) AS jumlah_penjualan,
			MIN(tanggal) AS tanggal_awal,
			MAX(tanggal) AS tanggal_akhir
		FROM transaksi
		WHERE member_id = ? ${config.sql}
		`,
		[memberId]
	);

	const raw = ringkasanRows[0];
	const ringkasan: LaporanRingkasan = {
		pendapatanPenjualan: toNumber(raw?.pendapatan_penjualan),
		pendapatanLain: toNumber(raw?.pendapatan_lain),
		totalPendapatan: toNumber(raw?.total_pendapatan),
		totalPengeluaran: toNumber(raw?.total_pengeluaran),
		saldo: toNumber(raw?.total_pendapatan) - toNumber(raw?.total_pengeluaran),
		jumlahTransaksi: toNumber(raw?.jumlah_transaksi),
		jumlahPenjualan: toNumber(raw?.jumlah_penjualan)
	};

	const [harianRows] = await db.query<RowDataPacket[]>(
		`
		SELECT tanggal, jenis, sumber, jumlah
		FROM transaksi
		WHERE member_id = ? ${config.sql}
		ORDER BY tanggal ASC
		`,
		[memberId]
	);

	const tanggalAwalDb = raw?.tanggal_awal ? parseDateFromDb(raw.tanggal_awal) : null;
	const tanggalAkhirDb = raw?.tanggal_akhir ? parseDateFromDb(raw.tanggal_akhir) : null;
	const chartRange = getChartDateRange(periode, tanggalAwalDb, tanggalAkhirDb);
	const harian = buildHarian(harianRows, chartRange.start, chartRange.end);

	const [katPenjualanRows] = await db.query<RowDataPacket[]>(
		`
		SELECT kategori, COALESCE(SUM(jumlah), 0) AS total
		FROM transaksi
		WHERE member_id = ? AND sumber = 'penjualan' ${config.sql}
		GROUP BY kategori ORDER BY total DESC LIMIT 8
		`,
		[memberId]
	);

	const [katPengeluaranRows] = await db.query<RowDataPacket[]>(
		`
		SELECT kategori, COALESCE(SUM(jumlah), 0) AS total
		FROM transaksi
		WHERE member_id = ? AND jenis = 'pengeluaran' ${config.sql}
		GROUP BY kategori ORDER BY total DESC LIMIT 8
		`,
		[memberId]
	);

	const [transaksiRows] = await db.query<RowDataPacket[]>(
		`
		SELECT tanggal, jenis, sumber, kategori, deskripsi, jumlah
		FROM transaksi
		WHERE member_id = ? ${config.sql}
		ORDER BY tanggal DESC, created_at DESC
		LIMIT 50
		`,
		[memberId]
	);

	const transaksi: LaporanTransaksi[] = transaksiRows.map((row) => ({
		tanggal: parseDateFromDb(row.tanggal),
		jenis: row.jenis,
		sumber: row.sumber,
		kategori: row.kategori,
		deskripsi: row.deskripsi,
		jumlah: toNumber(row.jumlah)
	}));

	const [hpRows] = await db.query<RowDataPacket[]>(
		`
		SELECT jenis, nama, sisa, status
		FROM hutang_piutang
		WHERE member_id = ?
		ORDER BY status ASC, sisa DESC
		LIMIT 20
		`,
		[memberId]
	);

	const hutangPiutang: LaporanHutangPiutang[] = hpRows.map((row) => ({
		jenis: row.jenis,
		nama: row.nama,
		sisa: toNumber(row.sisa),
		status: row.status
	}));

	const tanggalAwal = tanggalAwalDb ?? formatDateLocal(chartRange.start);
	const tanggalAkhir = tanggalAkhirDb ?? formatDateLocal(chartRange.end);

	return {
		periode,
		periodeLabel: config.label,
		tanggalAwal,
		tanggalAkhir,
		warungNama: warungNama || 'Warung',
		memberNama,
		ringkasan,
		harian,
		kategoriPenjualan: buildKategori(katPenjualanRows),
		kategoriPengeluaran: buildKategori(katPengeluaranRows),
		transaksi,
		hutangPiutang
	};
}