import { env } from '$env/dynamic/private';
import { json, type RequestEvent } from '@sveltejs/kit';
import type { RowDataPacket } from 'mysql2';
import type { BackofficeRole } from '$lib/types/backoffice-user';
import type { KategoriWarung, ModeInventori, Warung } from '$lib/types/warung';
import db from '$lib/server/db';

export interface CentrawarungPagination {
	page: number;
	limit: number;
	total: number;
	total_pages: number;
}

export interface CentrawarungListResponse<T> {
	success: true;
	data: T[];
	pagination: CentrawarungPagination;
}

export interface CentrawarungItemResponse<T> {
	success: true;
	data: T;
}

export interface BackofficeUserApi {
	id: number;
	nama: string;
	email: string;
	role: BackofficeRole;
	is_aktif: boolean;
	created_at: string;
	updated_at: string;
}

export interface RefKategoriWarungApi {
	id: number;
	kode: string;
	nama: string;
	icon: string;
	urutan: number;
	is_aktif: boolean;
}

export interface TblKodeposApi {
	id: number;
	kelurahan: string;
	kecamatan: string;
	kabupaten: string;
	provinsi: string;
	kodepos: string;
}

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

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

function parsePositiveInt(value: string | null, fallback: number): number {
	const parsed = Number.parseInt(value ?? '', 10);
	return Number.isFinite(parsed) && parsed > 0 ? parsed : fallback;
}

export function parsePagination(url: URL, defaultLimit = 50, maxLimit = 500) {
	const page = parsePositiveInt(url.searchParams.get('page'), 1);
	const limit = Math.min(maxLimit, parsePositiveInt(url.searchParams.get('limit'), defaultLimit));
	const offset = (page - 1) * limit;
	return { page, limit, offset };
}

export function buildPagination(page: number, limit: number, total: number): CentrawarungPagination {
	return {
		page,
		limit,
		total,
		total_pages: total > 0 ? Math.ceil(total / limit) : 0
	};
}

function getApiKeyFromRequest(request: Request): string | null {
	const authorization = request.headers.get('authorization');
	if (authorization?.startsWith('Bearer ')) {
		return authorization.slice(7).trim();
	}
	return request.headers.get('x-api-key')?.trim() ?? null;
}

export function centrawarungAuthError(
	message = 'Unauthorized. Kirim API key via header Authorization: Bearer <key> atau X-API-Key.'
) {
	return json({ success: false, message }, { status: 401 });
}

export function centrawarungForbidden(message = 'Metode tidak diizinkan. API ini hanya mendukung GET.') {
	return json({ success: false, message }, { status: 405 });
}

export function centrawarungNotFound(message = 'Data tidak ditemukan.') {
	return json({ success: false, message }, { status: 404 });
}

export function centrawarungServerError(message = 'Gagal mengambil data.') {
	return json({ success: false, message }, { status: 500 });
}

export function centrawarungBadRequest(message: string) {
	return json({ success: false, message }, { status: 400 });
}

export async function ensureKodeposDataAvailable(): Promise<Response | null> {
	const { hasKodeposData } = await import('$lib/server/referensi');
	if (!(await hasKodeposData())) {
		return json(
			{
				success: false,
				message: 'Data kodepos belum diimpor. Jalankan: npm run db:kodepos'
			},
			{ status: 503 }
		);
	}
	return null;
}

export function getKabupatenParam(url: URL): string {
	return url.searchParams.get('kabupaten')?.trim() || url.searchParams.get('kota')?.trim() || '';
}

export function requireCentrawarungAuth(request: Request): Response | null {
	const configuredKey = env.CENTRAWARUNG_API_KEY?.trim();

	if (!configuredKey) {
		return json(
			{ success: false, message: 'CENTRAWARUNG_API_KEY belum dikonfigurasi di server.' },
			{ status: 503 }
		);
	}

	const providedKey = getApiKeyFromRequest(request);
	if (!providedKey || providedKey !== configuredKey) {
		return centrawarungAuthError();
	}

	return null;
}

export function centrawarungOptionsResponse(): Response {
	const headers = new Headers();
	applyCentrawarungCors(headers);
	headers.set('Access-Control-Allow-Methods', 'GET, OPTIONS');
	headers.set('Access-Control-Allow-Headers', 'Authorization, X-API-Key, Content-Type');
	return new Response(null, { status: 204, headers });
}

export function applyCentrawarungCors(headers: Headers): void {
	const origin = env.CENTRAWARUNG_CORS_ORIGIN?.trim();
	if (origin) {
		headers.set('Access-Control-Allow-Origin', origin);
		headers.set('Vary', 'Origin');
	}
}

export function centrawarungJson<T>(body: T, init: ResponseInit = {}): Response {
	const headers = new Headers(init.headers);
	headers.set('Content-Type', 'application/json; charset=utf-8');
	applyCentrawarungCors(headers);
	return json(body, { ...init, headers });
}

export async function guardCentrawarungGet(event: RequestEvent): Promise<Response | null> {
	if (event.request.method === 'OPTIONS') {
		return centrawarungOptionsResponse();
	}

	if (event.request.method !== 'GET') {
		return centrawarungForbidden();
	}

	return requireCentrawarungAuth(event.request);
}

interface WarungRow extends RowDataPacket, Warung {
	is_aktif: number;
	is_publik: number;
	jam_buka: string;
	jam_tutup: string;
	created_at: Date;
	updated_at: Date;
}

function mapWarungRow(row: WarungRow) {
	return {
		id: row.id,
		member_id: row.member_id,
		nama: row.nama,
		slug: row.slug,
		deskripsi: row.deskripsi ?? '',
		kategori: row.kategori as KategoriWarung,
		alamat: row.alamat,
		kelurahan: row.kelurahan ?? '',
		kecamatan: row.kecamatan ?? '',
		kota: row.kota ?? '',
		provinsi: row.provinsi ?? '',
		kode_pos: row.kode_pos ?? '',
		telepon: row.telepon ?? '',
		whatsapp: row.whatsapp ?? '',
		latitude: toNumber(row.latitude),
		longitude: toNumber(row.longitude),
		jam_buka: row.jam_buka?.slice(0, 5) ?? '08:00',
		jam_tutup: row.jam_tutup?.slice(0, 5) ?? '21:00',
		hari_buka: row.hari_buka ?? 'Senin-Minggu',
		foto_url: row.foto_url ?? '',
		is_aktif: toBool(row.is_aktif),
		is_publik: toBool(row.is_publik),
		mode_inventori: (row.mode_inventori ?? 'tanpa_stok') as ModeInventori,
		created_at: new Date(row.created_at).toISOString(),
		updated_at: new Date(row.updated_at).toISOString()
	};
}

interface BackofficeUserRow extends RowDataPacket {
	id: number;
	nama: string;
	email: string;
	role: BackofficeRole;
	is_aktif: number;
	created_at: Date;
	updated_at: Date;
}

function mapBackofficeUserRow(row: BackofficeUserRow): BackofficeUserApi {
	return {
		id: row.id,
		nama: row.nama,
		email: row.email,
		role: row.role,
		is_aktif: toBool(row.is_aktif),
		created_at: new Date(row.created_at).toISOString(),
		updated_at: new Date(row.updated_at).toISOString()
	};
}

function mapRefKategoriRow(row: RowDataPacket): RefKategoriWarungApi {
	return {
		id: row.id,
		kode: row.kode,
		nama: row.nama,
		icon: row.icon,
		urutan: toNumber(row.urutan),
		is_aktif: toBool(row.is_aktif)
	};
}

function mapKodeposRow(row: RowDataPacket): TblKodeposApi {
	return {
		id: row.id,
		kelurahan: row.kelurahan,
		kecamatan: row.kecamatan,
		kabupaten: row.kabupaten,
		provinsi: row.provinsi,
		kodepos: row.kodepos
	};
}

export async function listWarungForCentrawarung(url: URL): Promise<CentrawarungListResponse<ReturnType<typeof mapWarungRow>>> {
	const { page, limit, offset } = parsePagination(url);
	const conditions: string[] = ['1=1'];
	const params: (string | number)[] = [];

	const q = url.searchParams.get('q')?.trim();
	const kota = url.searchParams.get('kota')?.trim();
	const kategori = url.searchParams.get('kategori')?.trim();
	const memberId = url.searchParams.get('member_id')?.trim();
	const isAktif = url.searchParams.get('is_aktif');
	const isPublik = url.searchParams.get('is_publik');

	if (q) {
		conditions.push('(nama LIKE ? OR slug LIKE ? OR alamat LIKE ? OR kelurahan LIKE ?)');
		const term = `%${q}%`;
		params.push(term, term, term, term);
	}
	if (kota) {
		conditions.push('kota LIKE ?');
		params.push(`%${kota}%`);
	}
	if (kategori) {
		conditions.push('kategori = ?');
		params.push(kategori);
	}
	if (memberId) {
		conditions.push('member_id = ?');
		params.push(Number(memberId));
	}
	if (isAktif === '0' || isAktif === '1') {
		conditions.push('is_aktif = ?');
		params.push(Number(isAktif));
	}
	if (isPublik === '0' || isPublik === '1') {
		conditions.push('is_publik = ?');
		params.push(Number(isPublik));
	}

	const where = conditions.join(' AND ');

	const [countRows] = await db.query<RowDataPacket[]>(
		`SELECT COUNT(*) AS total FROM warung WHERE ${where}`,
		params
	);
	const total = toNumber(countRows[0]?.total);

	const [rows] = await db.query<WarungRow[]>(
		`SELECT * FROM warung WHERE ${where} ORDER BY id ASC LIMIT ? OFFSET ?`,
		[...params, limit, offset]
	);

	return {
		success: true,
		data: rows.map(mapWarungRow),
		pagination: buildPagination(page, limit, total)
	};
}

export async function getWarungByIdForCentrawarung(id: number) {
	const [rows] = await db.query<WarungRow[]>('SELECT * FROM warung WHERE id = ? LIMIT 1', [id]);
	return rows[0] ? mapWarungRow(rows[0]) : null;
}

export async function listBackofficeUsersForCentrawarung(url: URL): Promise<CentrawarungListResponse<BackofficeUserApi>> {
	const { page, limit, offset } = parsePagination(url, 50, 200);
	const conditions: string[] = ['1=1'];
	const params: (string | number)[] = [];

	const role = url.searchParams.get('role')?.trim();
	const isAktif = url.searchParams.get('is_aktif');
	const q = url.searchParams.get('q')?.trim();

	if (role) {
		conditions.push('role = ?');
		params.push(role);
	}
	if (isAktif === '0' || isAktif === '1') {
		conditions.push('is_aktif = ?');
		params.push(Number(isAktif));
	}
	if (q) {
		conditions.push('(nama LIKE ? OR email LIKE ?)');
		const term = `%${q}%`;
		params.push(term, term);
	}

	const where = conditions.join(' AND ');

	const [countRows] = await db.query<RowDataPacket[]>(
		`SELECT COUNT(*) AS total FROM backoffice_user WHERE ${where}`,
		params
	);
	const total = toNumber(countRows[0]?.total);

	const [rows] = await db.query<BackofficeUserRow[]>(
		`SELECT id, nama, email, role, is_aktif, created_at, updated_at
		 FROM backoffice_user WHERE ${where}
		 ORDER BY id ASC LIMIT ? OFFSET ?`,
		[...params, limit, offset]
	);

	return {
		success: true,
		data: rows.map(mapBackofficeUserRow),
		pagination: buildPagination(page, limit, total)
	};
}

export async function getBackofficeUserByIdForCentrawarung(id: number) {
	const [rows] = await db.query<BackofficeUserRow[]>(
		`SELECT id, nama, email, role, is_aktif, created_at, updated_at
		 FROM backoffice_user WHERE id = ? LIMIT 1`,
		[id]
	);
	return rows[0] ? mapBackofficeUserRow(rows[0]) : null;
}

export async function listRefKategoriWarungForCentrawarung(
	url: URL
): Promise<CentrawarungListResponse<RefKategoriWarungApi>> {
	const { page, limit, offset } = parsePagination(url, 50, 200);
	const conditions: string[] = ['1=1'];
	const params: (string | number)[] = [];

	const isAktif = url.searchParams.get('is_aktif');
	if (isAktif === '0' || isAktif === '1') {
		conditions.push('is_aktif = ?');
		params.push(Number(isAktif));
	}

	const where = conditions.join(' AND ');

	const [countRows] = await db.query<RowDataPacket[]>(
		`SELECT COUNT(*) AS total FROM ref_kategori_warung WHERE ${where}`,
		params
	);
	const total = toNumber(countRows[0]?.total);

	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, kode, nama, icon, urutan, is_aktif
		 FROM ref_kategori_warung WHERE ${where}
		 ORDER BY urutan ASC, id ASC LIMIT ? OFFSET ?`,
		[...params, limit, offset]
	);

	return {
		success: true,
		data: rows.map(mapRefKategoriRow),
		pagination: buildPagination(page, limit, total)
	};
}

export async function getRefKategoriWarungByIdForCentrawarung(id: number) {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, kode, nama, icon, urutan, is_aktif
		 FROM ref_kategori_warung WHERE id = ? LIMIT 1`,
		[id]
	);
	return rows[0] ? mapRefKategoriRow(rows[0]) : null;
}

export async function listTblKodeposForCentrawarung(url: URL): Promise<CentrawarungListResponse<TblKodeposApi>> {
	const { page, limit, offset } = parsePagination(url, 100, 1000);
	const conditions: string[] = ['1=1'];
	const params: (string | number)[] = [];

	const provinsi = url.searchParams.get('provinsi')?.trim();
	const kabupaten = url.searchParams.get('kabupaten')?.trim();
	const kecamatan = url.searchParams.get('kecamatan')?.trim();
	const kodepos = url.searchParams.get('kodepos')?.trim();
	const q = url.searchParams.get('q')?.trim();

	if (provinsi) {
		conditions.push('provinsi = ?');
		params.push(provinsi);
	}
	if (kabupaten) {
		conditions.push('kabupaten = ?');
		params.push(kabupaten);
	}
	if (kecamatan) {
		conditions.push('kecamatan = ?');
		params.push(kecamatan);
	}
	if (kodepos) {
		conditions.push('kodepos = ?');
		params.push(kodepos);
	}
	if (q) {
		conditions.push('(kelurahan LIKE ? OR kecamatan LIKE ? OR kabupaten LIKE ? OR provinsi LIKE ? OR kodepos LIKE ?)');
		const term = `%${q}%`;
		params.push(term, term, term, term, term);
	}

	const where = conditions.join(' AND ');

	const [countRows] = await db.query<RowDataPacket[]>(
		`SELECT COUNT(*) AS total FROM tbl_kodepos WHERE ${where}`,
		params
	);
	const total = toNumber(countRows[0]?.total);

	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, kelurahan, kecamatan, kabupaten, provinsi, kodepos
		 FROM tbl_kodepos WHERE ${where}
		 ORDER BY id ASC LIMIT ? OFFSET ?`,
		[...params, limit, offset]
	);

	return {
		success: true,
		data: rows.map(mapKodeposRow),
		pagination: buildPagination(page, limit, total)
	};
}

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