import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import bcrypt from 'bcryptjs';
import type {
	CreateMemberInput,
	Member,
	MemberProfile,
	MemberSession,
	MemberStats
} from '$lib/types/member';
import db from '$lib/server/db';

interface MemberRow extends RowDataPacket, Member {}

function toNumber(value: unknown): number {
	return Number(value ?? 0);
}

function mapMemberRow(row: MemberRow): Member {
	return {
		...row,
		password: row.password ?? null,
		google_id: row.google_id ?? null,
		avatar_url: row.avatar_url ?? null
	};
}

export async function findMemberByEmail(email: string): Promise<Member | null> {
	const [rows] = await db.query<MemberRow[]>(
		`SELECT id, nama, email, password, google_id, avatar_url, created_at, updated_at
		 FROM member WHERE email = ? LIMIT 1`,
		[email]
	);

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

export async function findMemberByGoogleId(googleId: string): Promise<MemberSession | null> {
	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT id, nama, email FROM member WHERE google_id = ? LIMIT 1',
		[googleId]
	);
	return (rows[0] as MemberSession | undefined) ?? null;
}

export async function createMember(input: CreateMemberInput): Promise<number> {
	const hashedPassword = input.password ? await bcrypt.hash(input.password, 10) : null;

	const [result] = await db.query<ResultSetHeader>(
		`INSERT INTO member (nama, email, password, google_id, avatar_url)
		 VALUES (?, ?, ?, ?, ?)`,
		[
			input.nama.trim(),
			input.email.trim().toLowerCase(),
			hashedPassword,
			input.google_id ?? null,
			input.avatar_url ?? null
		]
	);

	return result.insertId;
}

export async function linkMemberGoogle(
	memberId: number,
	input: { google_id: string; avatar_url?: string | null }
): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		'UPDATE member SET google_id = ?, avatar_url = COALESCE(?, avatar_url) WHERE id = ?',
		[input.google_id, input.avatar_url ?? null, memberId]
	);
	return result.affectedRows > 0;
}

export function isGoogleOnlyMember(member: Member): boolean {
	return Boolean(member.google_id && !member.password);
}

export async function findMemberById(id: number): Promise<MemberSession | null> {
	const [rows] = await db.query<RowDataPacket[]>(
		'SELECT id, nama, email FROM member WHERE id = ? LIMIT 1',
		[id]
	);

	return (rows[0] as MemberSession | undefined) ?? null;
}

export async function getMemberProfile(id: number): Promise<MemberProfile | null> {
	const [rows] = await db.query<MemberRow[]>(
		'SELECT id, nama, email, avatar_url, created_at, updated_at FROM member WHERE id = ? LIMIT 1',
		[id]
	);

	const row = rows[0];
	if (!row) return null;

	return {
		id: row.id,
		nama: row.nama,
		email: row.email,
		avatar_url: row.avatar_url ?? null,
		created_at: new Date(row.created_at).toISOString(),
		updated_at: new Date(row.updated_at).toISOString()
	};
}

export async function getMemberStats(memberId: number): Promise<MemberStats> {
	const [rows] = await db.query<RowDataPacket[]>(
		`
		SELECT
			COALESCE(SUM(CASE WHEN sumber = 'penjualan' THEN jumlah END), 0) AS total_penjualan,
			COUNT(*) AS total_transaksi,
			COALESCE(SUM(CASE WHEN sumber = 'catatan' THEN 1 END), 0) AS total_catatan,
			(SELECT COUNT(*) FROM hutang_piutang WHERE member_id = ? AND status = 'belum_lunas') AS hutang_piutang_aktif
		FROM transaksi
		WHERE member_id = ?
		`,
		[memberId, memberId]
	);

	const row = rows[0];
	return {
		totalPenjualan: toNumber(row?.total_penjualan),
		totalTransaksi: toNumber(row?.total_transaksi),
		totalCatatan: toNumber(row?.total_catatan),
		hutangPiutangAktif: toNumber(row?.hutang_piutang_aktif)
	};
}

export interface UpdateProfileInput {
	id: number;
	nama: string;
	email: string;
}

export async function updateMemberAvatar(memberId: number, avatarUrl: string): Promise<boolean> {
	const [result] = await db.query<ResultSetHeader>(
		'UPDATE member SET avatar_url = ? WHERE id = ?',
		[avatarUrl, memberId]
	);
	return result.affectedRows > 0;
}

export async function updateMemberProfile(input: UpdateProfileInput): Promise<boolean> {
	const [existing] = await db.query<RowDataPacket[]>(
		'SELECT id FROM member WHERE email = ? AND id != ? LIMIT 1',
		[input.email, input.id]
	);

	if (existing.length > 0) return false;

	const [result] = await db.query<ResultSetHeader>(
		'UPDATE member SET nama = ?, email = ? WHERE id = ?',
		[input.nama, input.email, input.id]
	);

	return result.affectedRows > 0;
}

export async function updateMemberPassword(
	memberId: number,
	passwordLama: string,
	passwordBaru: string
): Promise<'ok' | 'wrong_password' | 'failed'> {
	const [rows] = await db.query<MemberRow[]>(
		'SELECT password FROM member WHERE id = ? LIMIT 1',
		[memberId]
	);

	const member = rows[0];
	if (!member) return 'failed';

	if (!member.password) return 'wrong_password';

	const valid = await bcrypt.compare(passwordLama, member.password);
	if (!valid) return 'wrong_password';

	const hashed = await bcrypt.hash(passwordBaru, 10);
	const [result] = await db.query<ResultSetHeader>(
		'UPDATE member SET password = ? WHERE id = ?',
		[hashed, memberId]
	);

	return result.affectedRows > 0 ? 'ok' : 'failed';
}