import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import type {
	FilterHutangPiutang,
	HutangPiutang,
	HutangPiutangSummary,
	JenisHutangPiutang
} from '$lib/types/hutang-piutang';
import db from '$lib/server/db';
import { getTodayDateString } from '$lib/utils/parse';

interface HutangPiutangRow extends RowDataPacket, HutangPiutang {
	jatuh_tempo: Date | 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 mapRow(row: HutangPiutangRow): HutangPiutang {
	return {
		id: row.id,
		jenis: row.jenis,
		nama: row.nama,
		deskripsi: row.deskripsi,
		jumlah: toNumber(row.jumlah),
		sisa: toNumber(row.sisa),
		status: row.status,
		tanggal: formatDateLocal(new Date(row.tanggal)),
		jatuh_tempo: row.jatuh_tempo ? formatDateLocal(new Date(row.jatuh_tempo)) : null,
		created_at: new Date(row.created_at).toISOString()
	};
}

function getFilterCondition(filter: FilterHutangPiutang): { sql: string; params: string[] } {
	switch (filter) {
		case 'hutang':
			return { sql: "AND jenis = 'hutang'", params: [] };
		case 'piutang':
			return { sql: "AND jenis = 'piutang'", params: [] };
		case 'belum_lunas':
			return { sql: "AND status = 'belum_lunas'", params: [] };
		default:
			return { sql: '', params: [] };
	}
}

export async function getHutangPiutangList(
	memberId: number,
	filter: FilterHutangPiutang = 'semua'
) {
	const { sql } = getFilterCondition(filter);

	const [rows] = await db.query<HutangPiutangRow[]>(
		`
		SELECT id, jenis, nama, deskripsi, jumlah, sisa, status, tanggal, jatuh_tempo, created_at
		FROM hutang_piutang
		WHERE member_id = ?
			${sql}
		ORDER BY status ASC, created_at DESC
		`,
		[memberId]
	);

	const [summaryRows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			COALESCE(SUM(CASE WHEN jenis = 'hutang' AND status = 'belum_lunas' THEN sisa END), 0) AS total_hutang,
			COALESCE(SUM(CASE WHEN jenis = 'piutang' AND status = 'belum_lunas' THEN sisa END), 0) AS total_piutang,
			COUNT(CASE WHEN status = 'belum_lunas' THEN 1 END) AS jumlah_belum_lunas
		FROM hutang_piutang
		WHERE member_id = ?
		`,
		[memberId]
	);

	const summaryRow = summaryRows[0];
	const summary: HutangPiutangSummary = {
		totalHutang: toNumber(summaryRow?.total_hutang),
		totalPiutang: toNumber(summaryRow?.total_piutang),
		jumlahBelumLunas: toNumber(summaryRow?.jumlah_belum_lunas)
	};

	return {
		items: rows.map(mapRow),
		summary,
		filter
	};
}

export interface CreateHutangPiutangInput {
	memberId: number;
	jenis: JenisHutangPiutang;
	nama: string;
	deskripsi: string;
	jumlah: number;
	tanggal: string;
	jatuhTempo: string | null;
}

export async function createHutangPiutang(input: CreateHutangPiutangInput): Promise<number> {
	const [result] = await db.query<ResultSetHeader>(
		`
		INSERT INTO hutang_piutang (member_id, jenis, nama, deskripsi, jumlah, sisa, tanggal, jatuh_tempo)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
		`,
		[
			input.memberId,
			input.jenis,
			input.nama,
			input.deskripsi,
			input.jumlah,
			input.jumlah,
			input.tanggal,
			input.jatuhTempo
		]
	);

	return result.insertId;
}

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

	try {
		await connection.beginTransaction();

		const [rows] = await connection.query<RowDataPacket[]>(
			`
			SELECT jenis, nama, deskripsi, sisa
			FROM hutang_piutang
			WHERE id = ? AND member_id = ? AND status = 'belum_lunas'
			LIMIT 1
			`,
			[id, memberId]
		);

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

		const sisa = toNumber(item.sisa);
		const [updateResult] = await connection.query<ResultSetHeader>(
			`
			UPDATE hutang_piutang
			SET sisa = 0, status = 'lunas'
			WHERE id = ? AND member_id = ?
			`,
			[id, memberId]
		);

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

		const jenisTransaksi = item.jenis === 'hutang' ? 'pengeluaran' : 'pendapatan';
		const kategori = item.jenis === 'hutang' ? 'Operasional' : 'Pendapatan Lain';
		const deskripsi = `Pelunasan ${item.jenis} — ${item.nama}${item.deskripsi ? `: ${item.deskripsi}` : ''}`;

		await connection.query(
			`
			INSERT INTO transaksi (member_id, jenis, sumber, kategori, deskripsi, jumlah, tanggal)
			VALUES (?, ?, 'catatan', ?, ?, ?, ?)
			`,
			[memberId, jenisTransaksi, kategori, deskripsi, sisa, getTodayDateString()]
		);

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

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

	return result.affectedRows > 0;
}