import type { RowDataPacket } from 'mysql2';
import type {
	KodeposSearchResult,
	RefHariBuka,
	RefKategoriWarung,
	RefKecamatan,
	RefKelurahan,
	RefKota,
	RefProvinsi,
	WilayahResolved,
	WilayahSelection
} from '$lib/types/referensi';
import type { KategoriWarung } from '$lib/types/warung';
import db from '$lib/server/db';

function normalize(value: string): string {
	return value.trim().toLowerCase();
}

export async function hasKodeposData(): Promise<boolean> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT COUNT(*) AS total
		 FROM information_schema.tables
		 WHERE table_schema = DATABASE() AND table_name = 'tbl_kodepos'`
	);

	if (!rows[0]?.total) return false;

	const [countRows] = await db.query<RowDataPacket[]>(
		'SELECT COUNT(*) AS total FROM tbl_kodepos'
	);

	return Number(countRows[0]?.total ?? 0) > 0;
}

export async function getProvinsiList(): Promise<RefProvinsi[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT DISTINCT provinsi AS nama FROM tbl_kodepos ORDER BY provinsi ASC'
	);
	return rows.map((row) => ({ nama: row.nama }));
}

export async function getKotaByProvinsi(provinsi: string): Promise<RefKota[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT DISTINCT kabupaten AS nama
		 FROM tbl_kodepos
		 WHERE provinsi = ?
		 ORDER BY kabupaten ASC`,
		[provinsi]
	);
	return rows.map((row) => ({ nama: row.nama }));
}

export async function getKecamatanByKota(provinsi: string, kabupaten: string): Promise<RefKecamatan[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT DISTINCT kecamatan AS nama
		 FROM tbl_kodepos
		 WHERE provinsi = ? AND kabupaten = ?
		 ORDER BY kecamatan ASC`,
		[provinsi, kabupaten]
	);
	return rows.map((row) => ({ nama: row.nama }));
}

export async function getKelurahanByKecamatan(
	provinsi: string,
	kabupaten: string,
	kecamatan: string
): Promise<RefKelurahan[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, kelurahan AS nama, kodepos AS kode_pos
		 FROM tbl_kodepos
		 WHERE provinsi = ? AND kabupaten = ? AND kecamatan = ?
		 ORDER BY kelurahan ASC, kodepos ASC`,
		[provinsi, kabupaten, kecamatan]
	);
	return rows.map((row) => ({
		id: row.id,
		nama: row.nama,
		kode_pos: row.kode_pos
	}));
}

export async function getKodeposById(id: number): Promise<WilayahResolved | null> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT provinsi, kabupaten, kecamatan, kelurahan, kodepos
		 FROM tbl_kodepos WHERE id = ? LIMIT 1`,
		[id]
	);

	if (!rows[0]) return null;

	return {
		provinsi: rows[0].provinsi,
		kota: rows[0].kabupaten,
		kecamatan: rows[0].kecamatan,
		kelurahan: rows[0].kelurahan,
		kode_pos: rows[0].kodepos
	};
}

export async function searchKodepos(query: string, limit = 25): Promise<KodeposSearchResult[]> {
	const keyword = query.trim();
	if (keyword.length < 2) return [];

	const like = `%${keyword}%`;
	const exactStart = `${keyword}%`;

	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT id, kelurahan, kecamatan, kabupaten, provinsi, kodepos
		FROM tbl_kodepos
		WHERE kelurahan LIKE ? OR kecamatan LIKE ? OR kabupaten LIKE ? OR kodepos LIKE ?
		ORDER BY
			CASE WHEN kelurahan LIKE ? THEN 0 WHEN kecamatan LIKE ? THEN 1 ELSE 2 END,
			kelurahan ASC,
			kodepos ASC
		LIMIT ?
		`,
		[like, like, like, like, exactStart, exactStart, limit]
	);

	return rows.map((row) => ({
		id: row.id,
		kelurahan: row.kelurahan,
		kecamatan: row.kecamatan,
		kota: row.kabupaten,
		provinsi: row.provinsi,
		kode_pos: row.kodepos
	}));
}

export async function resolveKodeposId(
	provinsi: string,
	kota: string,
	kecamatan: string,
	kelurahan: string,
	kodePos?: string
): Promise<string> {
	if (!provinsi.trim() || !kota.trim() || !kecamatan.trim() || !kelurahan.trim()) {
		return '';
	}

	const params: (string | number)[] = [
		provinsi.trim(),
		kota.trim(),
		kecamatan.trim(),
		kelurahan.trim()
	];

	let sql = `
		SELECT id FROM tbl_kodepos
		WHERE provinsi = ? AND kabupaten = ? AND kecamatan = ? AND kelurahan = ?
	`;

	if (kodePos?.trim()) {
		sql += ' AND kodepos = ?';
		params.push(kodePos.trim());
	}

	sql += ' ORDER BY id ASC LIMIT 1';

	const [rows] = await db.query<RowDataPacket[]>(sql, params);
	return rows[0] ? String(rows[0].id) : '';
}

export async function resolveWilayahSelection(
	provinsi: string,
	kota: string,
	kecamatan: string,
	kelurahan: string,
	kodePos?: string
): Promise<WilayahSelection> {
	const kodepos_id = await resolveKodeposId(provinsi, kota, kecamatan, kelurahan, kodePos);

	if (!kodepos_id) {
		return { provinsi: '', kota: '', kecamatan: '', kodepos_id: '' };
	}

	const resolved = await getKodeposById(Number(kodepos_id));
	if (!resolved) {
		return { provinsi: '', kota: '', kecamatan: '', kodepos_id: '' };
	}

	return {
		provinsi: resolved.provinsi,
		kota: resolved.kota,
		kecamatan: resolved.kecamatan,
		kodepos_id
	};
}

export async function validateAndResolveWilayah(
	selection: WilayahSelection
): Promise<{ ok: true; data: WilayahResolved } | { ok: false; message: string }> {
	const kodeposId = Number(selection.kodepos_id);

	if (!selection.provinsi || !selection.kota || !selection.kecamatan || !kodeposId) {
		return { ok: false, message: 'Cari dan pilih kelurahan dari hasil pencarian kodepos.' };
	}

	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT provinsi, kabupaten, kecamatan, kelurahan, kodepos
		 FROM tbl_kodepos
		 WHERE id = ? AND provinsi = ? AND kabupaten = ? AND kecamatan = ?
		 LIMIT 1`,
		[kodeposId, selection.provinsi, selection.kota, selection.kecamatan]
	);

	if (!rows[0]) {
		return { ok: false, message: 'Wilayah tidak valid. Pilih ulang dari daftar kodepos.' };
	}

	return {
		ok: true,
		data: {
			provinsi: rows[0].provinsi,
			kota: rows[0].kabupaten,
			kecamatan: rows[0].kecamatan,
			kelurahan: rows[0].kelurahan,
			kode_pos: rows[0].kodepos
		}
	};
}

export async function getKategoriWarungList(): Promise<RefKategoriWarung[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, kode, nama, icon
		 FROM ref_kategori_warung WHERE is_aktif = 1 ORDER BY urutan ASC, nama ASC`
	);
	return rows.map((row) => ({
		id: row.id,
		kode: row.kode as KategoriWarung,
		nama: row.nama,
		icon: row.icon
	}));
}

export async function getHariBukaList(): Promise<RefHariBuka[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, kode, label
		 FROM ref_hari_buka WHERE is_aktif = 1 ORDER BY urutan ASC, label ASC`
	);
	return rows.map((row) => ({
		id: row.id,
		kode: row.kode,
		label: row.label
	}));
}

export async function validateKategoriWarung(kode: string): Promise<KategoriWarung | null> {
	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT kode FROM ref_kategori_warung WHERE kode = ? AND is_aktif = 1 LIMIT 1',
		[kode]
	);
	return rows[0] ? (rows[0].kode as KategoriWarung) : null;
}

export async function validateHariBuka(kode: string): Promise<string | null> {
	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT label FROM ref_hari_buka WHERE kode = ? AND is_aktif = 1 LIMIT 1',
		[kode]
	);
	return rows[0]?.label ?? null;
}

export async function resolveHariBukaKode(label: string): Promise<string> {
	if (!label.trim()) return 'senin-minggu';

	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT kode FROM ref_hari_buka WHERE LOWER(label) = ? AND is_aktif = 1 LIMIT 1',
		[normalize(label)]
	);

	return rows[0]?.kode ?? 'senin-minggu';
}