import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import type {
	Catatan,
	CatatanListData,
	FilterPeriode,
	JenisCatatan,
	Penjualan,
	PenjualanListData
} from '$lib/types/transaksi';
import db from '$lib/server/db';

interface TransaksiRow extends RowDataPacket {
	id: number;
	jenis: JenisCatatan;
	kategori: string;
	deskripsi: string;
	jumlah: number;
	barang_id: number | null;
	barang_nama: string | null;
	qty: number | null;
	tanggal: Date;
	created_at: Date;
}

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 getFilterCondition(filter: FilterPeriode): string {
	switch (filter) {
		case 'hari':
			return 'AND tanggal = CURDATE()';
		case 'minggu':
			return 'AND tanggal >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)';
		default:
			return '';
	}
}

function mapTransaksiRow(row: TransaksiRow) {
	return {
		id: row.id,
		kategori: row.kategori,
		deskripsi: row.deskripsi,
		jumlah: toNumber(row.jumlah),
		barang_id: row.barang_id ?? null,
		barang_nama: row.barang_nama ?? null,
		qty: row.qty != null ? toNumber(row.qty) : null,
		tanggal: formatDateLocal(new Date(row.tanggal)),
		created_at: new Date(row.created_at).toISOString()
	};
}

export async function getPenjualanList(
	memberId: number,
	filter: FilterPeriode = 'hari'
): Promise<PenjualanListData> {
	const filterCondition = getFilterCondition(filter);

	const [rows] = await db.query<TransaksiRow[]>(
		`
		SELECT t.id, t.kategori, t.deskripsi, t.jumlah, t.barang_id, t.qty, t.tanggal, t.created_at,
			b.nama AS barang_nama
		FROM transaksi t
		LEFT JOIN barang b ON b.id = t.barang_id
		WHERE t.member_id = ?
			AND t.sumber = 'penjualan'
			${filterCondition.replaceAll('tanggal', 't.tanggal')}
		ORDER BY t.created_at DESC
		`,
		[memberId]
	);

	const penjualan: Penjualan[] = rows.map(mapTransaksiRow);
	const total = penjualan.reduce((sum, item) => sum + item.jumlah, 0);

	return { penjualan, total, jumlah: penjualan.length, filter };
}

export async function getCatatanList(
	memberId: number,
	filter: FilterPeriode = 'hari'
): Promise<CatatanListData> {
	const filterCondition = getFilterCondition(filter);

	const [rows] = await db.query<TransaksiRow[]>(
		`
		SELECT id, jenis, kategori, deskripsi, jumlah, tanggal, created_at
		FROM transaksi
		WHERE member_id = ?
			AND sumber = 'catatan'
			${filterCondition}
		ORDER BY created_at DESC
		`,
		[memberId]
	);

	const catatan: Catatan[] = rows.map((row) => ({
		...mapTransaksiRow(row),
		jenis: row.jenis
	}));

	const totalPendapatan = catatan
		.filter((c) => c.jenis === 'pendapatan')
		.reduce((sum, c) => sum + c.jumlah, 0);
	const totalPengeluaran = catatan
		.filter((c) => c.jenis === 'pengeluaran')
		.reduce((sum, c) => sum + c.jumlah, 0);

	return {
		catatan,
		totalPendapatan,
		totalPengeluaran,
		saldo: totalPendapatan - totalPengeluaran,
		jumlah: catatan.length,
		filter
	};
}

export interface CreatePenjualanInput {
	memberId: number;
	kategori: string;
	deskripsi: string;
	jumlah: number;
	tanggal: string;
}

export interface CreateCatatanInput {
	memberId: number;
	jenis: JenisCatatan;
	kategori: string;
	deskripsi: string;
	jumlah: number;
	tanggal: string;
}

export async function createPenjualan(input: CreatePenjualanInput): Promise<number> {
	const [result] = await db.query<ResultSetHeader>(
		`
		INSERT INTO transaksi (member_id, jenis, sumber, kategori, deskripsi, jumlah, tanggal)
		VALUES (?, 'pendapatan', 'penjualan', ?, ?, ?, ?)
		`,
		[input.memberId, input.kategori, input.deskripsi, input.jumlah, input.tanggal]
	);

	return result.insertId;
}

export interface CreatePenjualanStokInput {
	memberId: number;
	barangId: number;
	qty: number;
	jumlah: number;
	tanggal: string;
}

export async function createPenjualanStok(input: CreatePenjualanStokInput): Promise<number> {
	const connection = await db.getConnection();

	try {
		await connection.beginTransaction();

		const [barangRows] = await connection.query<RowDataPacket[]>(
			`
			SELECT id, nama, kategori, stok, harga_jual, satuan
			FROM barang
			WHERE id = ? AND member_id = ? AND is_aktif = 1
			LIMIT 1
			`,
			[input.barangId, input.memberId]
		);

		const barang = barangRows[0];
		if (!barang) {
			await connection.rollback();
			throw new Error('BARANG_NOT_FOUND');
		}

		const stok = toNumber(barang.stok);
		if (input.qty > stok) {
			await connection.rollback();
			throw new Error('STOK_TIDAK_CUKUP');
		}

		const deskripsi = `Penjualan: ${barang.nama} × ${input.qty} ${barang.satuan}`;
		const [result] = await connection.query<ResultSetHeader>(
			`
			INSERT INTO transaksi (member_id, jenis, sumber, kategori, deskripsi, jumlah, barang_id, qty, tanggal)
			VALUES (?, 'pendapatan', 'penjualan', ?, ?, ?, ?, ?, ?)
			`,
			[
				input.memberId,
				barang.kategori,
				deskripsi,
				input.jumlah,
				input.barangId,
				input.qty,
				input.tanggal
			]
		);

		const stokSesudah = stok - input.qty;
		await connection.query(`UPDATE barang SET stok = ? WHERE id = ? AND member_id = ?`, [
			stokSesudah,
			input.barangId,
			input.memberId
		]);

		await connection.query(
			`
			INSERT INTO stok_mutasi (member_id, barang_id, jenis, jumlah, stok_sebelum, stok_sesudah, keterangan, tanggal)
			VALUES (?, ?, 'keluar', ?, ?, ?, ?, ?)
			`,
			[
				input.memberId,
				input.barangId,
				input.qty,
				stok,
				stokSesudah,
				`Penjualan #${result.insertId}`,
				input.tanggal
			]
		);

		await connection.commit();
		return result.insertId;
	} catch (error) {
		await connection.rollback();
		throw error;
	} finally {
		connection.release();
	}
}

export async function createCatatan(input: CreateCatatanInput): Promise<number> {
	const [result] = await db.query<ResultSetHeader>(
		`
		INSERT INTO transaksi (member_id, jenis, sumber, kategori, deskripsi, jumlah, tanggal)
		VALUES (?, ?, 'catatan', ?, ?, ?, ?)
		`,
		[input.memberId, input.jenis, input.kategori, input.deskripsi, input.jumlah, input.tanggal]
	);

	return result.insertId;
}

export async function deletePenjualan(memberId: number, id: number): Promise<boolean> {
	const connection = await db.getConnection();

	try {
		await connection.beginTransaction();

		const [rows] = await connection.query<RowDataPacket[]>(
			`
			SELECT barang_id, qty, tanggal
			FROM transaksi
			WHERE id = ? AND member_id = ? AND sumber = 'penjualan'
			LIMIT 1
			`,
			[id, memberId]
		);

		const transaksi = rows[0];
		if (!transaksi) {
			await connection.rollback();
			return false;
		}

		if (transaksi.barang_id && transaksi.qty) {
			const barangId = Number(transaksi.barang_id);
			const qty = toNumber(transaksi.qty);

			const [barangRows] = await connection.query<RowDataPacket[]>(
				`SELECT stok FROM barang WHERE id = ? AND member_id = ? LIMIT 1`,
				[barangId, memberId]
			);

			const stokSebelum = toNumber(barangRows[0]?.stok);
			const stokSesudah = stokSebelum + qty;

			await connection.query(`UPDATE barang SET stok = ? WHERE id = ? AND member_id = ?`, [
				stokSesudah,
				barangId,
				memberId
			]);

			await connection.query(
				`
				INSERT INTO stok_mutasi (member_id, barang_id, jenis, jumlah, stok_sebelum, stok_sesudah, keterangan, tanggal)
				VALUES (?, ?, 'masuk', ?, ?, ?, ?, ?)
				`,
				[
					memberId,
					barangId,
					qty,
					stokSebelum,
					stokSesudah,
					`Batal penjualan #${id}`,
					formatDateLocal(new Date(transaksi.tanggal))
				]
			);
		}

		const [result] = await connection.query<ResultSetHeader>(
			`DELETE FROM transaksi WHERE id = ? AND member_id = ? AND sumber = 'penjualan'`,
			[id, memberId]
		);

		if (result.affectedRows === 0) {
			await connection.rollback();
			return false;
		}

		await connection.commit();
		return true;
	} catch (error) {
		await connection.rollback();
		throw error;
	} finally {
		connection.release();
	}
}

export async function deleteCatatan(memberId: number, id: number): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		`
		DELETE FROM transaksi
		WHERE id = ? AND member_id = ? AND sumber = 'catatan'
		`,
		[id, memberId]
	);

	return result.affectedRows > 0;
}

export async function getTransaksiRingkasan(memberId: number) {
	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			COALESCE(SUM(CASE WHEN sumber = 'penjualan' AND tanggal = CURDATE() THEN jumlah END), 0) AS penjualan_hari,
			COALESCE(SUM(CASE WHEN sumber = 'catatan' AND jenis = 'pengeluaran' AND tanggal = CURDATE() THEN jumlah END), 0) AS pengeluaran_hari,
			COALESCE(SUM(CASE WHEN sumber = 'catatan' AND jenis = 'pendapatan' AND tanggal = CURDATE() THEN jumlah END), 0) AS pendapatan_lain_hari,
			(SELECT COUNT(*) FROM hutang_piutang WHERE member_id = ? AND status = 'belum_lunas') AS hutang_piutang_aktif
		FROM transaksi
		WHERE member_id = ?
		`,
		[memberId, memberId]
	);

	const row = rows[0];
	return {
		penjualanHari: toNumber(row?.penjualan_hari),
		pengeluaranHari: toNumber(row?.pengeluaran_hari),
		pendapatanLainHari: toNumber(row?.pendapatan_lain_hari),
		hutangPiutangAktif: toNumber(row?.hutang_piutang_aktif)
	};
}