import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import { PRIVILEGE_DEFS, type PaketKode, type PrivilegeKode } from '$lib/constants/privilege';
import type {
	AdminMemberRow,
	AssignLanggananInput,
	IntervalLangganan,
	MemberEntitlements,
	MemberLangganan,
	PaketHarga,
	PaketLangganan,
	PaketPrivilege,
	StatusLangganan
} from '$lib/types/langganan';
import db from '$lib/server/db';

interface PaketRow extends RowDataPacket {
	id: number;
	kode: PaketKode;
	nama: string;
	deskripsi: string;
	urutan: number;
	is_aktif: number;
}

interface HargaRow extends RowDataPacket {
	paket_id: number;
	interval: IntervalLangganan;
	harga: string;
}

interface PrivilegeRow extends RowDataPacket {
	paket_id: number;
	privilege_kode: PrivilegeKode;
	nilai: string;
}

interface LanggananRow extends RowDataPacket {
	id: number;
	member_id: number;
	paket_id: number;
	paket_kode: PaketKode;
	paket_nama: string;
	interval: IntervalLangganan;
	status: StatusLangganan;
	mulai_at: Date;
	berakhir_at: Date | null;
	keterangan: string;
}

function toBool(value: unknown): boolean {
	return value === 1 || value === true || value === '1' || value === 'true';
}

function privilegeDef(kode: PrivilegeKode) {
	return PRIVILEGE_DEFS.find((p) => p.kode === kode);
}

export function hasPrivilege(entitlements: MemberEntitlements, kode: PrivilegeKode): boolean {
	const def = privilegeDef(kode);
	const nilai = entitlements.privileges[kode];
	if (!nilai) return false;
	if (def?.tipe === 'boolean') return nilai === 'true' || nilai === '1';
	return Number(nilai) > 0;
}

export function getPrivilegeNumber(entitlements: MemberEntitlements, kode: PrivilegeKode): number {
	const nilai = entitlements.privileges[kode];
	return Number(nilai ?? 0);
}

export function isUnlimited(entitlements: MemberEntitlements, kode: PrivilegeKode): boolean {
	return getPrivilegeNumber(entitlements, kode) === 0;
}

async function getPaketPrivileges(paketId: number): Promise<PaketPrivilege[]> {
	const [rows] = await db.query<PrivilegeRow[]>(
		'SELECT paket_id, privilege_kode, nilai FROM paket_privilege WHERE paket_id = ?',
		[paketId]
	);

	const map = new Map(rows.map((r) => [r.privilege_kode, r.nilai]));

	return PRIVILEGE_DEFS.map((def) => ({
		kode: def.kode,
		nama: def.nama,
		deskripsi: def.deskripsi,
		tipe: def.tipe,
		nilai: map.get(def.kode) ?? (def.tipe === 'boolean' ? 'false' : '0')
	}));
}

async function getPaketHarga(paketId: number): Promise<PaketHarga[]> {
	const [rows] = await db.query<HargaRow[]>(
		'SELECT paket_id, `interval`, harga FROM paket_harga WHERE paket_id = ? ORDER BY `interval`',
		[paketId]
	);
	return rows.map((r) => ({
		interval: r.interval,
		harga: Number(r.harga)
	}));
}

async function mapPaket(row: PaketRow): Promise<PaketLangganan> {
	const [harga, privileges] = await Promise.all([
		getPaketHarga(row.id),
		getPaketPrivileges(row.id)
	]);

	return {
		id: row.id,
		kode: row.kode,
		nama: row.nama,
		deskripsi: row.deskripsi,
		urutan: row.urutan,
		is_aktif: toBool(row.is_aktif),
		harga,
		privileges
	};
}

export async function getAllPaket(onlyAktif = false): Promise<PaketLangganan[]> {
	const sql = onlyAktif
		? 'SELECT * FROM paket_langganan WHERE is_aktif = 1 ORDER BY urutan'
		: 'SELECT * FROM paket_langganan ORDER BY urutan';

	const [rows] = await db.query<PaketRow[]>(sql);
	return Promise.all(rows.map(mapPaket));
}

export async function getPaketById(id: number): Promise<PaketLangganan | null> {
	const [rows] = await db.query<PaketRow[]>(
		'SELECT * FROM paket_langganan WHERE id = ? LIMIT 1',
		[id]
	);
	const row = rows[0];
	return row ? mapPaket(row) : null;
}

export async function getPaketByKode(kode: PaketKode): Promise<PaketLangganan | null> {
	const [rows] = await db.query<PaketRow[]>(
		'SELECT * FROM paket_langganan WHERE kode = ? LIMIT 1',
		[kode]
	);
	const row = rows[0];
	return row ? mapPaket(row) : null;
}

export async function updatePaketInfo(
	id: number,
	input: { nama: string; deskripsi: string; is_aktif: boolean }
): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		'UPDATE paket_langganan SET nama = ?, deskripsi = ?, is_aktif = ? WHERE id = ?',
		[input.nama, input.deskripsi, input.is_aktif ? 1 : 0, id]
	);
	return result.affectedRows > 0;
}

export async function updatePaketHarga(
	paketId: number,
	interval: IntervalLangganan,
	harga: number
): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		`INSERT INTO paket_harga (paket_id, \`interval\`, harga)
		 VALUES (?, ?, ?)
		 ON DUPLICATE KEY UPDATE harga = VALUES(harga)`,
		[paketId, interval, harga]
	);
	return result.affectedRows > 0;
}

export async function updatePaketPrivilege(
	paketId: number,
	privilegeKode: PrivilegeKode,
	nilai: string
): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		`INSERT INTO paket_privilege (paket_id, privilege_kode, nilai)
		 VALUES (?, ?, ?)
		 ON DUPLICATE KEY UPDATE nilai = VALUES(nilai)`,
		[paketId, privilegeKode, nilai]
	);
	return result.affectedRows > 0;
}

function mapLangganan(row: LanggananRow): MemberLangganan {
	return {
		id: row.id,
		member_id: row.member_id,
		paket_id: row.paket_id,
		paket_kode: row.paket_kode,
		paket_nama: row.paket_nama,
		interval: row.interval,
		status: row.status,
		mulai_at: new Date(row.mulai_at).toISOString(),
		berakhir_at: row.berakhir_at ? new Date(row.berakhir_at).toISOString() : null,
		keterangan: row.keterangan ?? ''
	};
}

export async function getMemberLangganan(memberId: number): Promise<MemberLangganan | null> {
	const [rows] = await db.query<LanggananRow[]>(
		`
		SELECT ml.*, pl.kode AS paket_kode, pl.nama AS paket_nama
		FROM member_langganan ml
		JOIN paket_langganan pl ON pl.id = ml.paket_id
		WHERE ml.member_id = ?
		  AND ml.status = 'aktif'
		  AND (ml.berakhir_at IS NULL OR ml.berakhir_at >= CURDATE())
		ORDER BY ml.mulai_at DESC
		LIMIT 1
		`,
		[memberId]
	);

	const row = rows[0];
	return row ? mapLangganan(row) : null;
}

function addMonths(date: Date, months: number): Date {
	const result = new Date(date);
	result.setMonth(result.getMonth() + months);
	return result;
}

function defaultBerakhirAt(interval: IntervalLangganan): string {
	const now = new Date();
	const end = interval === 'tahunan' ? addMonths(now, 12) : addMonths(now, 1);
	return end.toISOString().slice(0, 10);
}

export async function assignMemberLangganan(input: AssignLanggananInput): Promise<boolean> {
	const berakhirAt = input.berakhirAt ?? defaultBerakhirAt(input.interval);

	const connection = await db.getConnection();
	try {
		await connection.beginTransaction();

		await connection.query(
			`UPDATE member_langganan SET status = 'dibatalkan'
			 WHERE member_id = ? AND status = 'aktif'`,
			[input.memberId]
		);

		await connection.query(
			`INSERT INTO member_langganan
			 (member_id, paket_id, \`interval\`, status, mulai_at, berakhir_at, keterangan)
			 VALUES (?, ?, ?, 'aktif', CURDATE(), ?, ?)`,
			[
				input.memberId,
				input.paketId,
				input.interval,
				berakhirAt,
				input.keterangan ?? ''
			]
		);

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

export async function getMemberEntitlements(memberId: number): Promise<MemberEntitlements> {
	const langganan = await getMemberLangganan(memberId);

	let paketId: number;
	let paketKode: PaketKode;
	let paketNama: string;
	let interval: IntervalLangganan | null = null;
	let berakhirAt: string | null = null;

	if (langganan) {
		paketId = langganan.paket_id;
		paketKode = langganan.paket_kode;
		paketNama = langganan.paket_nama;
		interval = langganan.interval;
		berakhirAt = langganan.berakhir_at;
	} else {
		const freePaket = await getPaketByKode('free');
		if (!freePaket) {
			return {
				paketKode: 'free',
				paketNama: 'Free',
				interval: null,
				berakhirAt: null,
				privileges: Object.fromEntries(
					PRIVILEGE_DEFS.map((d) => [d.kode, d.tipe === 'boolean' ? 'false' : '0'])
				) as Record<PrivilegeKode, string>
			};
		}
		paketId = freePaket.id;
		paketKode = freePaket.kode;
		paketNama = freePaket.nama;
	}

	const [privRows, overrideRows] = await Promise.all([
		db.query<PrivilegeRow[]>(
			'SELECT paket_id, privilege_kode, nilai FROM paket_privilege WHERE paket_id = ?',
			[paketId]
		),
		db.query<PrivilegeRow[]>(
			'SELECT privilege_kode, nilai FROM member_privilege_override WHERE member_id = ?',
			[memberId]
		)
	]);

	const privileges = Object.fromEntries(
		PRIVILEGE_DEFS.map((d) => [d.kode, d.tipe === 'boolean' ? 'false' : '0'])
	) as Record<PrivilegeKode, string>;

	for (const row of privRows[0]) {
		privileges[row.privilege_kode] = row.nilai;
	}

	for (const row of overrideRows[0]) {
		privileges[row.privilege_kode] = row.nilai;
	}

	return { paketKode, paketNama, interval, berakhirAt, privileges };
}

export async function listMembersForAdmin(): Promise<AdminMemberRow[]> {
	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			m.id, m.nama, m.email, m.created_at,
			pl.kode AS paket_kode,
			pl.nama AS paket_nama,
			ml.status AS langganan_status,
			ml.berakhir_at
		FROM member m
		LEFT JOIN member_langganan ml ON ml.member_id = m.id
			AND ml.status = 'aktif'
			AND (ml.berakhir_at IS NULL OR ml.berakhir_at >= CURDATE())
		LEFT JOIN paket_langganan pl ON pl.id = ml.paket_id
		ORDER BY m.created_at DESC
		`
	);

	return rows.map((row) => ({
		id: row.id,
		nama: row.nama,
		email: row.email,
		created_at: new Date(row.created_at).toISOString(),
		paket_kode: row.paket_kode ?? null,
		paket_nama: row.paket_nama ?? 'Free',
		langganan_status: row.langganan_status ?? null,
		berakhir_at: row.berakhir_at ? new Date(row.berakhir_at).toISOString() : null
	}));
}

export async function getAdminMemberDetail(memberId: number) {
	const [memberRows] = await db.query<RowDataPacket[]>(
		'SELECT id, nama, email, created_at FROM member WHERE id = ? LIMIT 1',
		[memberId]
	);
	const member = memberRows[0];
	if (!member) return null;

	const [langganan, entitlements, paketList, overrideRows] = await Promise.all([
		getMemberLangganan(memberId),
		getMemberEntitlements(memberId),
		getAllPaket(true),
		db.query<RowDataPacket[]>(
			'SELECT privilege_kode, nilai, keterangan FROM member_privilege_override WHERE member_id = ?',
			[memberId]
		)
	]);

	return {
		member: {
			id: member.id,
			nama: member.nama,
			email: member.email,
			created_at: new Date(member.created_at).toISOString()
		},
		langganan,
		entitlements,
		paketList,
		overrides: overrideRows[0].map((r) => ({
			kode: r.privilege_kode as PrivilegeKode,
			nilai: r.nilai as string,
			keterangan: r.keterangan as string
		}))
	};
}

export async function updateMemberPrivilegeOverrides(
	memberId: number,
	overrides: { kode: PrivilegeKode; nilai: string; keterangan?: string }[]
): Promise<boolean> {
	const connection = await db.getConnection();
	try {
		await connection.beginTransaction();
		await connection.query('DELETE FROM member_privilege_override WHERE member_id = ?', [memberId]);

		for (const item of overrides) {
			if (!item.nilai.trim()) continue;
			await connection.query(
				`INSERT INTO member_privilege_override (member_id, privilege_kode, nilai, keterangan)
				 VALUES (?, ?, ?, ?)`,
				[memberId, item.kode, item.nilai, item.keterangan ?? '']
			);
		}

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

export async function countTransaksiBulanIni(memberId: number): Promise<number> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT COUNT(*) AS total FROM transaksi
		 WHERE member_id = ? AND YEAR(tanggal) = YEAR(CURDATE()) AND MONTH(tanggal) = MONTH(CURDATE())`,
		[memberId]
	);
	return Number(rows[0]?.total ?? 0);
}

export async function countBarangMember(memberId: number): Promise<number> {
	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT COUNT(*) AS total FROM barang WHERE member_id = ? AND is_aktif = 1',
		[memberId]
	);
	return Number(rows[0]?.total ?? 0);
}

export async function canAddBarang(memberId: number): Promise<boolean> {
	const entitlements = await getMemberEntitlements(memberId);
	if (isUnlimited(entitlements, 'max_barang')) return true;
	const limit = getPrivilegeNumber(entitlements, 'max_barang');
	const used = await countBarangMember(memberId);
	return used < limit;
}

export async function canCreateTransaksi(memberId: number): Promise<boolean> {
	const entitlements = await getMemberEntitlements(memberId);
	if (isUnlimited(entitlements, 'max_transaksi_bulan')) return true;
	const limit = getPrivilegeNumber(entitlements, 'max_transaksi_bulan');
	const used = await countTransaksiBulanIni(memberId);
	return used < limit;
}