const { Op } = require("sequelize");
const dbSAPPS = require("../../helpers/db");
require("dotenv").config();

module.exports = {
	getAll,
	option,
	count,
	create,
	update,
	enabled,
	delete: _delete,
};

const formatWithModifier = async (cities) => {
	return Promise.all(
		cities.map(async (city) => {
			const modifier = await dbSAPPS.User.findOne({
				where: {
					id: city.modified_by,
				},
				attributes: ["full_name"],
			});

			return {
				...city,
				modifier: modifier?.full_name,
			};
		}),
	);
};

async function getAll(params) {
	try {
		// base filter conditions
		const filterConditions = {};

		// precedence: explicit province_id from UI filter wins
		if (params.province_id) {
			filterConditions.province_id = params.province_id;
		}

		// column-level filters from frontend
		if (params.f_code) {
			filterConditions.code = {
				[Op.like]: `%${params.f_code}%`,
			};
		}

		if (params.f_display_name) {
			filterConditions.display_name = {
				[Op.like]: `%${params.f_display_name}%`,
			};
		}

		if (params.f_province_name) {
			filterConditions["$province.display_name$"] = {
				[Op.like]: `%${params.f_province_name}%`,
			};
		}

		// global search q across key fields
		const andConditions = [];
		if (params.q) {
			andConditions.push({
				[Op.or]: [
					{ display_name: { [Op.like]: `%${params.q}%` } },
					{ code: { [Op.like]: `%${params.q}%` } },
					{ "$province.display_name$": { [Op.like]: `%${params.q}%` } },
				],
			});
		}

		const whereClause = {
			...filterConditions,
			...(andConditions.length
				? {
						[Op.and]: andConditions,
					}
				: {}),
		};

		// robust pagination defaults
		const rawSize = parseInt(params?.pageSize, 10);
		const pageSize = Number.isFinite(rawSize) && rawSize > 0 ? Math.min(rawSize, 5000) : 100;
		const rawIndex = parseInt(params?.pageIndex, 10);
		const pageIndex = Number.isFinite(rawIndex) && rawIndex >= 0 ? rawIndex : 0;

		// optional whitelist for sorting
		const allowedSortBy = ["modified", "display_name", "code", "province_name"];
		let sortBy = allowedSortBy.includes(params?.sortBy) ? params.sortBy : "modified";
		if (sortBy === "province_name") sortBy = "province_id";
		const sortDir = (params?.sortDir || "DESC").toString().toUpperCase() === "ASC" ? "ASC" : "DESC";

		const { rows, count } = await dbSAPPS.City.findAndCountAll({
			where: whereClause,
			order: [[sortBy, sortDir]],
			offset: pageIndex * pageSize,
			limit: pageSize,
			subQuery: false,
			distinct: true,
			include: [
				{
					model: dbSAPPS.Province,
					as: "province",
					attributes: ["id", "display_name", "code"],
				},
			],
		});

		let cities = rows.map((city) => {
			return {
				...city.dataValues,
				province_name: city?.province?.display_name || null,
			};
		});

		cities = await formatWithModifier(cities);

		return {
			cities,
			totalCount: count,
			pageIndex,
			pageSize,
			sortBy,
			sortDir,
			success: true,
			message: "cities loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load cities!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function filter(params) {
	// Membuat objek filter dengan validasi
	const filterConditions = {};

	if (params.province_id) {
		filterConditions.province_id = {
			[Op.like]: `%${params.province_id}%`,
		};
	}

	if (params.display_name) {
		filterConditions.display_name = {
			[Op.like]: `%${params.display_name}%`,
		};
	}

	if (params.code) {
		filterConditions.code = {
			[Op.like]: `%${params.code}%`,
		};
	}

	if (params.enabled) {
		filterConditions.enabled = {
			[Op.eq]: params.enabled === "1" ? true : false,
		};
	}

	try {
		// Mencari pengguna berdasarkan filter conditions
		const cities = await dbSAPPS.City.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]], // Opsional: Mengurutkan hasil berdasarkan kolom 'modified'
			include: [
				{
					model: dbSAPPS.Province,
					as: "province",
					attributes: ["id", "display_name", "code"],
				},
			],
		});

		// Mengembalikan hasil pencarian
		return {
			success: true,
			cities,
			message: "cities loaded successfully!",
		};
	} catch (error) {
		// Logging error untuk keperluan debugging
		console.error("Error retrieving cities: ", error);
		const errorMessage = error.message || "Failed to retrieve cities. Please try again later.";
		// Mengembalikan respons dengan status 500 Internal Server Error
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function option(params = {}) {
	try {
		const whereClause = { enabled: true };

		// Optional filter: province_id (used by Outlets filter)
		if (params?.province_id) {
			whereClause.province_id = params.province_id;
		}

		// Mencari city yang diaktifkan dengan atribut tertentu
		const cities = await dbSAPPS.City.findAll({
			attributes: ["id", "province_id", "display_name", "code"],
			where: whereClause,
			order: [["display_name", "ASC"]], // Opsional: Mengurutkan hasil berdasarkan
		});

		// Mengembalikan hasil pencarian
		return {
			success: true,
			cities,
			message: "cities loaded successfully!",
		};
	} catch (error) {
		// Logging error untuk keperluan debugging
		console.error("Error retrieving cities: ", error);
		const errorMessage = error.message || "Failed to retrieve cities. Please try again later.";
		// Mengembalikan respons dengan status 500 Internal Server Error
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function count() {
	try {
		// Menghitung jumlah pengguna yang diaktifkan
		const count = await dbSAPPS.City.count({
			where: { enabled: true },
		});

		// Mengembalikan hasil hitung
		return {
			success: true,
			count,
			message: "cities loaded successfully!",
		};
	} catch (error) {
		// Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
		console.error("Error counting cities: ", error);
		return {
			success: false,
			message: "Failed to retrieve cities count. Please try again later.",
		};
	}
}

async function create(params) {
	try {
		// Validasi input
		if (!params.display_name || !params.code)
			return {
				success: false,
				message: "City name and city code are required!",
			};

		// Periksa apakah email sudah digunakan
		const existingNameNNumber = await dbSAPPS.City.findOne({
			where: {
				display_name: params.display_name,
				code: params.code,
			},
		});
		if (existingNameNNumber)
			return {
				success: false,
				message: `City name "${params.display_name}" and code "${params.code}" is already taken.`,
			};

		if (!params.enabled) params.enabled = true;
		const newCity = await dbSAPPS.City.create(params);

		// Respons sukses
		return {
			success: true,
			message: "Data city has been successfully adeded!",
			city: newCity,
		};
	} catch (error) {
		// Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
		const errorMessage = error.message || "An error occurred during city creation.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function update(id, params) {
	try {
		// Ambil dari database
		const city = await dbSAPPS.City.findByPk(id);
		if (!city)
			return {
				success: false,
				message: "City not found!",
			};

		// Validasi perubahan
		if (params.display_name && params.display_name !== city.display_name) {
			const existingCityName = await dbSAPPS.City.findOne({
				where: { display_name: params.display_name },
			});
			if (existingCityName) {
				return {
					success: false,
					message: `City name "${params.display_name}" is already taken. Try again!`,
				};
			}
		}

		if (params.code && params.code !== city.code) {
			const existingCityCode = await dbSAPPS.City.findOne({
				where: { code: params.code },
			});
			if (existingCityCode) {
				return {
					success: false,
					message: `City Code "${params.code}" is already taken. Try again!`,
				};
			}
		}

		// Perbarui data kota
		await city.update(params);

		// Respons sukses
		return {
			success: true,
			message: "Your data city has been saved!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to update city data.";
		// Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function enabled(id, params) {
	try {
		const city = await dbSAPPS.City.findByPk(id);
		if (!city)
			return {
				success: false,
				message: "City not found!",
			};

		const updateData = {};
		updateData.enabled = params.enabled;
		if (params.modified_by) updateData.modified_by = params.modified_by;
		if (params.modified) updateData.modified = params.modified;

		// debug
		// console.log('id', id);
		// console.log('params', params);
		// console.log('updateData', updateData)

		await city.update(updateData);

		return {
			success: true,
			message: "City has been updated!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to update City.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function _delete(id) {
	try {
		// Mengambil pengguna dari database
		const city = await dbSAPPS.City.findByPk(id);
		if (!city) return { success: false, message: "city not found!" };

		// Menghapus pengguna dari database
		await city.destroy();

		// Respons sukses
		return {
			success: true,
			message: "city successfully deleted!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to delete city.";
		// Menangani error dan mengembalikan respons dengan status 500 Internal Server Error
		return {
			success: false,
			message: errorMessage,
		};
	}
}
