import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import type { OpnameBarangRow, StokOpname, StokOpnameItem } from '$lib/types/opname';
import db from '$lib/server/db';
import { getBarangForOpname } from '$lib/server/stok';

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}`;
}

export async function getOpnameList(memberId: number): Promise<StokOpname[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT id, tanggal, keterangan, jumlah_item, jumlah_selisih, created_at
		FROM stok_opname
		WHERE member_id = ?
		ORDER BY created_at DESC
		LIMIT 30
		`,
		[memberId]
	);

	return rows.map((row) => ({
		id: Number(row.id),
		tanggal: formatDateLocal(new Date(row.tanggal)),
		keterangan: String(row.keterangan ?? ''),
		jumlah_item: toNumber(row.jumlah_item),
		jumlah_selisih: toNumber(row.jumlah_selisih),
		created_at: new Date(row.created_at).toISOString()
	}));
}

export async function getOpnameDetail(memberId: number, id: number): Promise<StokOpname | null> {
	const [headerRows] = await db.query<RowDataPacket[]>(
		`
		SELECT id, tanggal, keterangan, jumlah_item, jumlah_selisih, created_at
		FROM stok_opname
		WHERE id = ? AND member_id = ?
		LIMIT 1
		`,
		[id, memberId]
	);

	const header = headerRows[0];
	if (!header) return null;

	const [itemRows] = await db.query<RowDataPacket[]>(
		`
		SELECT i.id, i.barang_id, b.nama AS barang_nama, b.satuan,
			i.stok_sistem, i.stok_fisik, i.selisih
		FROM stok_opname_item i
		INNER JOIN barang b ON b.id = i.barang_id
		WHERE i.opname_id = ?
		ORDER BY b.nama ASC
		`,
		[id]
	);

	const items: StokOpnameItem[] = itemRows.map((row) => ({
		id: Number(row.id),
		barang_id: Number(row.barang_id),
		barang_nama: String(row.barang_nama),
		satuan: String(row.satuan),
		stok_sistem: toNumber(row.stok_sistem),
		stok_fisik: toNumber(row.stok_fisik),
		selisih: toNumber(row.selisih)
	}));

	return {
		id: Number(header.id),
		tanggal: formatDateLocal(new Date(header.tanggal)),
		keterangan: String(header.keterangan ?? ''),
		jumlah_item: toNumber(header.jumlah_item),
		jumlah_selisih: toNumber(header.jumlah_selisih),
		created_at: new Date(header.created_at).toISOString(),
		items
	};
}

export async function getOpnameFormBarang(memberId: number): Promise<OpnameBarangRow[]> {
	return getBarangForOpname(memberId);
}

export interface CreateOpnameInput {
	memberId: number;
	tanggal: string;
	keterangan: string;
	items: { barangId: number; stokFisik: number }[];
}

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

	try {
		await connection.beginTransaction();

		const barangList = await getBarangForOpname(input.memberId);
		const barangMap = new Map(barangList.map((b) => [b.id, b]));

		const processedItems: {
			barangId: number;
			stokSistem: number;
			stokFisik: number;
			selisih: number;
		}[] = [];

		for (const item of input.items) {
			const barang = barangMap.get(item.barangId);
			if (!barang) continue;

			const stokFisik = Math.max(0, Math.floor(item.stokFisik));
			const selisih = stokFisik - barang.stok;
			processedItems.push({
				barangId: item.barangId,
				stokSistem: barang.stok,
				stokFisik,
				selisih
			});
		}

		if (processedItems.length === 0) {
			await connection.rollback();
			throw new Error('NO_ITEMS');
		}

		const jumlahSelisih = processedItems.filter((i) => i.selisih !== 0).length;

		const [opnameResult] = await connection.query<ResultSetHeader>(
			`
			INSERT INTO stok_opname (member_id, tanggal, keterangan, jumlah_item, jumlah_selisih)
			VALUES (?, ?, ?, ?, ?)
			`,
			[input.memberId, input.tanggal, input.keterangan, processedItems.length, jumlahSelisih]
		);

		const opnameId = opnameResult.insertId;

		for (const item of processedItems) {
			await connection.query(
				`
				INSERT INTO stok_opname_item (opname_id, barang_id, stok_sistem, stok_fisik, selisih)
				VALUES (?, ?, ?, ?, ?)
				`,
				[opnameId, item.barangId, item.stokSistem, item.stokFisik, item.selisih]
			);

			if (item.selisih !== 0) {
				await connection.query(`UPDATE barang SET stok = ? WHERE id = ? AND member_id = ?`, [
					item.stokFisik,
					item.barangId,
					input.memberId
				]);

				await connection.query(
					`
					INSERT INTO stok_mutasi (member_id, barang_id, jenis, jumlah, stok_sebelum, stok_sesudah, keterangan, tanggal)
					VALUES (?, ?, 'penyesuaian', ?, ?, ?, ?, ?)
					`,
					[
						input.memberId,
						item.barangId,
						Math.abs(item.selisih),
						item.stokSistem,
						item.stokFisik,
						`Stok opname #${opnameId}`,
						input.tanggal
					]
				);
			}
		}

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