import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import type {
	Barang,
	FilterStok,
	JenisMutasi,
	StatusStok,
	StokMutasi,
	StokSummary
} from '$lib/types/stok';
import db from '$lib/server/db';
import { getTodayDateString } from '$lib/utils/parse';

interface BarangRow extends RowDataPacket {
	id: number;
	nama: string;
	kategori: string;
	satuan: string;
	harga_beli: number | null;
	harga_jual: number | null;
	stok: number;
	stok_minimum: number;
	deskripsi: string;
	is_aktif: number;
	created_at: Date;
	updated_at: Date;
}

interface MutasiRow extends RowDataPacket {
	id: number;
	barang_id: number;
	barang_nama: string;
	jenis: JenisMutasi;
	jumlah: number;
	stok_sebelum: number;
	stok_sesudah: number;
	keterangan: string;
	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 getStatusStok(stok: number, stokMinimum: number): StatusStok {
	if (stok <= 0) return 'habis';
	if (stok <= stokMinimum) return 'menipis';
	return 'aman';
}

function mapBarangRow(row: BarangRow): Barang {
	const stok = toNumber(row.stok);
	const stokMinimum = toNumber(row.stok_minimum);

	return {
		id: row.id,
		nama: row.nama,
		kategori: row.kategori,
		satuan: row.satuan,
		harga_beli: row.harga_beli != null ? toNumber(row.harga_beli) : null,
		harga_jual: row.harga_jual != null ? toNumber(row.harga_jual) : null,
		stok,
		stok_minimum: stokMinimum,
		deskripsi: row.deskripsi ?? '',
		is_aktif: Boolean(row.is_aktif),
		status_stok: getStatusStok(stok, stokMinimum),
		created_at: new Date(row.created_at).toISOString(),
		updated_at: new Date(row.updated_at).toISOString()
	};
}

function mapMutasiRow(row: MutasiRow): StokMutasi {
	return {
		id: row.id,
		barang_id: row.barang_id,
		barang_nama: row.barang_nama,
		jenis: row.jenis,
		jumlah: toNumber(row.jumlah),
		stok_sebelum: toNumber(row.stok_sebelum),
		stok_sesudah: toNumber(row.stok_sesudah),
		keterangan: row.keterangan ?? '',
		tanggal: formatDateLocal(new Date(row.tanggal)),
		created_at: new Date(row.created_at).toISOString()
	};
}

function getFilterCondition(filter: FilterStok): string {
	switch (filter) {
		case 'menipis':
			return 'AND b.stok > 0 AND b.stok <= b.stok_minimum';
		case 'habis':
			return 'AND b.stok <= 0';
		case 'aktif':
			return 'AND b.is_aktif = 1';
		default:
			return '';
	}
}

export async function getStokList(
	memberId: number,
	filter: FilterStok = 'semua',
	kategoriFilter = ''
) {
	const filterSql = getFilterCondition(filter);
	const kategoriSql = kategoriFilter ? 'AND b.kategori = ?' : '';
	const params: (number | string)[] = [memberId];
	if (kategoriFilter) params.push(kategoriFilter);

	const [rows] = await db.query<BarangRow[]>(
		`
		SELECT b.id, b.nama, b.kategori, b.satuan, b.harga_beli, b.harga_jual,
			b.stok, b.stok_minimum, b.deskripsi, b.is_aktif, b.created_at, b.updated_at
		FROM barang b
		WHERE b.member_id = ?
			${filterSql}
			${kategoriSql}
		ORDER BY
			CASE WHEN b.stok <= 0 THEN 0 WHEN b.stok <= b.stok_minimum THEN 1 ELSE 2 END,
			b.nama ASC
		`,
		params
	);

	const [summaryRows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			COUNT(*) AS total_barang,
			COUNT(CASE WHEN stok > 0 AND stok <= stok_minimum THEN 1 END) AS total_menipis,
			COUNT(CASE WHEN stok <= 0 THEN 1 END) AS total_habis,
			COALESCE(SUM(stok * COALESCE(harga_beli, harga_jual, 0)), 0) AS nilai_stok
		FROM barang
		WHERE member_id = ? AND is_aktif = 1
		`,
		[memberId]
	);

	const [kategoriRows] = await db.query<RowDataPacket[]>(
		`
		SELECT DISTINCT kategori
		FROM barang
		WHERE member_id = ?
		ORDER BY kategori ASC
		`,
		[memberId]
	);

	const [mutasiRows] = await db.query<MutasiRow[]>(
		`
		SELECT m.id, m.barang_id, b.nama AS barang_nama, m.jenis, m.jumlah,
			m.stok_sebelum, m.stok_sesudah, m.keterangan, m.tanggal, m.created_at
		FROM stok_mutasi m
		INNER JOIN barang b ON b.id = m.barang_id
		WHERE m.member_id = ?
		ORDER BY m.created_at DESC
		LIMIT 8
		`,
		[memberId]
	);

	const summaryRow = summaryRows[0];
	const summary: StokSummary = {
		totalBarang: toNumber(summaryRow?.total_barang),
		totalStokMenipis: toNumber(summaryRow?.total_menipis),
		totalStokHabis: toNumber(summaryRow?.total_habis),
		nilaiStok: toNumber(summaryRow?.nilai_stok)
	};

	return {
		items: rows.map(mapBarangRow),
		mutasiTerbaru: mutasiRows.map(mapMutasiRow),
		summary,
		filter,
		kategoriFilter,
		kategoriList: kategoriRows.map((row) => String(row.kategori))
	};
}

export async function getBarangById(memberId: number, id: number): Promise<Barang | null> {
	const [rows] = await db.query<BarangRow[]>(
		`
		SELECT id, nama, kategori, satuan, harga_beli, harga_jual, stok, stok_minimum,
			deskripsi, is_aktif, created_at, updated_at
		FROM barang
		WHERE id = ? AND member_id = ?
		LIMIT 1
		`,
		[id, memberId]
	);

	return rows[0] ? mapBarangRow(rows[0]) : null;
}

export interface CreateBarangInput {
	memberId: number;
	nama: string;
	kategori: string;
	satuan: string;
	hargaBeli: number | null;
	hargaJual: number | null;
	stok: number;
	stokMinimum: number;
	deskripsi: string;
}

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

	try {
		await connection.beginTransaction();

		const [result] = await connection.query<ResultSetHeader>(
			`
			INSERT INTO barang (member_id, nama, kategori, satuan, harga_beli, harga_jual, stok, stok_minimum, deskripsi)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
			`,
			[
				input.memberId,
				input.nama,
				input.kategori,
				input.satuan,
				input.hargaBeli,
				input.hargaJual,
				input.stok,
				input.stokMinimum,
				input.deskripsi
			]
		);

		const barangId = result.insertId;

		if (input.stok > 0) {
			await connection.query(
				`
				INSERT INTO stok_mutasi (member_id, barang_id, jenis, jumlah, stok_sebelum, stok_sesudah, keterangan, tanggal)
				VALUES (?, ?, 'masuk', ?, 0, ?, 'Stok awal', ?)
				`,
				[input.memberId, barangId, input.stok, input.stok, getTodayDateString()]
			);
		}

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

export interface UpdateBarangInput {
	memberId: number;
	id: number;
	nama: string;
	kategori: string;
	satuan: string;
	hargaBeli: number | null;
	hargaJual: number | null;
	stokMinimum: number;
	deskripsi: string;
	isAktif: boolean;
}

export async function updateBarang(input: UpdateBarangInput): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		`
		UPDATE barang
		SET nama = ?, kategori = ?, satuan = ?, harga_beli = ?, harga_jual = ?,
			stok_minimum = ?, deskripsi = ?, is_aktif = ?
		WHERE id = ? AND member_id = ?
		`,
		[
			input.nama,
			input.kategori,
			input.satuan,
			input.hargaBeli,
			input.hargaJual,
			input.stokMinimum,
			input.deskripsi,
			input.isAktif ? 1 : 0,
			input.id,
			input.memberId
		]
	);

	return result.affectedRows > 0;
}

export interface MutasiStokInput {
	memberId: number;
	barangId: number;
	jenis: JenisMutasi;
	jumlah: number;
	keterangan: string;
	tanggal: string;
}

export async function mutasiStok(input: MutasiStokInput): Promise<boolean> {
	const connection = await db.getConnection();

	try {
		await connection.beginTransaction();

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

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

		const stokSebelum = toNumber(barang.stok);
		let stokSesudah = stokSebelum;

		if (input.jenis === 'masuk') {
			stokSesudah = stokSebelum + input.jumlah;
		} else if (input.jenis === 'keluar') {
			if (input.jumlah > stokSebelum) {
				await connection.rollback();
				return false;
			}
			stokSesudah = stokSebelum - input.jumlah;
		} else {
			stokSesudah = input.jumlah;
		}

		if (stokSesudah < 0) {
			await connection.rollback();
			return false;
		}

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

		const jumlahMutasi =
			input.jenis === 'penyesuaian' ? Math.abs(stokSesudah - stokSebelum) : input.jumlah;

		await connection.query(
			`
			INSERT INTO stok_mutasi (member_id, barang_id, jenis, jumlah, stok_sebelum, stok_sesudah, keterangan, tanggal)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?)
			`,
			[
				input.memberId,
				input.barangId,
				input.jenis,
				jumlahMutasi,
				stokSebelum,
				stokSesudah,
				input.keterangan,
				input.tanggal
			]
		);

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

export interface BarangPenjualan {
	id: number;
	nama: string;
	kategori: string;
	satuan: string;
	stok: number;
	harga_jual: number | null;
}

export async function getBarangForPenjualan(memberId: number): Promise<BarangPenjualan[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT id, nama, kategori, satuan, stok, harga_jual
		FROM barang
		WHERE member_id = ? AND is_aktif = 1 AND stok > 0
		ORDER BY nama ASC
		`,
		[memberId]
	);

	return rows.map((row) => ({
		id: Number(row.id),
		nama: String(row.nama),
		kategori: String(row.kategori),
		satuan: String(row.satuan),
		stok: toNumber(row.stok),
		harga_jual: row.harga_jual != null ? toNumber(row.harga_jual) : null
	}));
}

export async function getBarangForOpname(memberId: number) {
	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT id, nama, satuan, stok, kategori
		FROM barang
		WHERE member_id = ? AND is_aktif = 1
		ORDER BY nama ASC
		`,
		[memberId]
	);

	return rows.map((row) => ({
		id: Number(row.id),
		nama: String(row.nama),
		satuan: String(row.satuan),
		stok: toNumber(row.stok),
		kategori: String(row.kategori)
	}));
}

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

	try {
		await connection.beginTransaction();

		await connection.query(`DELETE FROM stok_mutasi WHERE barang_id = ? AND member_id = ?`, [
			id,
			memberId
		]);

		const [result] = await connection.query<ResultSetHeader>(
			`DELETE FROM barang WHERE id = ? AND member_id = ?`,
			[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();
	}
}