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

// Case-insensitive like: Postgres uses ILIKE; MariaDB/MySQL use LIKE (collation handles case)
const likeOp = (() => {
	try {
		return dbSAPPS?.sequelize?.getDialect?.() === "postgres" ? Op.iLike : Op.like;
	} catch {
		return Op.like;
	}
})();

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

async function getAll(params) {
	try {
		const offset = (parseInt(params?.page) - 1) * parseInt(params?.limit);
		const whereClause = params?.search
			? {
					[Op.or]: [{ display_name: { [likeOp]: `%${params?.search}%` } }, { code: { [likeOp]: `%${params?.search}%` } }],
				}
			: {};

		const { count, rows } = await dbSAPPS.Client.findAndCountAll({
			where: whereClause,
			order: [["modified", "DESC"]],
			limit: parseInt(params?.limit),
			offset,
			include: [
				{
					model: dbSAPPS.ClientType,
					as: "clienttype",
					attributes: ["id", "display_name"],
				},
				{
					model: dbSAPPS.Specialist,
					as: "specialist",
					attributes: ["id", "display_name", "code"],
				},
				{
					model: dbSAPPS.SubSpecialist,
					as: "subspecialist",
					attributes: ["id", "display_name", "code"],
				},
				{
					model: dbSAPPS.ClientSubtype,
					as: "clientsubtype",
					attributes: ["id", "display_name", "code"],
				},
				{
					model: dbSAPPS.Province,
					as: "province",
					attributes: ["id", "display_name", "code"],
				},
				{
					model: dbSAPPS.City,
					as: "city",
					attributes: ["id", "display_name", "code"],
				},
			],
		});

		return {
			success: true,
			message: "Clients loaded successfully",
			clients: rows,
			total: count,
		};
	} catch (error) {
		console.error("Error fetching clients:", error);
		return {
			success: false,
			message: error.message || "Failed to load clients",
		};
	}
}

async function filter(params) {
	const filterConditions = {};

	// Pagination
	const page = parseInt(params.page) || 1;
	const limit = parseInt(params.limit) || 10;
	const offset = (page - 1) * limit;

	// Handle 'f_' prefix from frontend table filters
	const cleanParams = { ...params };
	Object.keys(cleanParams).forEach((key) => {
		if (key.startsWith("f_")) {
			cleanParams[key.substring(2)] = cleanParams[key];
		}
	});

	// Global Search (q)
	if (cleanParams.q) {
		filterConditions[Op.or] = [{ display_name: { [likeOp]: `%${cleanParams.q}%` } }, { code: { [likeOp]: `%${cleanParams.q}%` } }];
	}

	// Column Filters (from MRT column filters - direct fields)
	if (cleanParams.display_name) {
		filterConditions.display_name = { [likeOp]: `%${cleanParams.display_name}%` };
	}
	if (cleanParams.code) {
		filterConditions.code = { [likeOp]: `%${cleanParams.code}%` };
	}

	// ID Filters (Exact or In) - from FilterCard (direct filter by ID)
	// Helper to handle comma-separated IDs
	const handleIdFilter = (key, value) => {
		if (typeof value === "string" && value.includes(",")) {
			filterConditions[key] = { [Op.in]: value.split(",") };
		} else {
			filterConditions[key] = { [Op.eq]: value };
		}
	};

	// FilterCard filters - direct ID filtering
	if (cleanParams.clienttype_id) handleIdFilter("clienttype_id", cleanParams.clienttype_id);
	if (cleanParams.province_id) handleIdFilter("province_id", cleanParams.province_id);
	if (cleanParams.city_id) handleIdFilter("city_id", cleanParams.city_id);
	if (cleanParams.specialist_id) handleIdFilter("specialist_id", cleanParams.specialist_id);
	if (cleanParams.subspecialist_id) handleIdFilter("subspecialist_id", cleanParams.subspecialist_id);
	if (cleanParams.clientsubtype_id) handleIdFilter("clientsubtype_id", cleanParams.clientsubtype_id);
	if (cleanParams.company_id) handleIdFilter("company_id", cleanParams.company_id);

	// MRT Column filters for related tables (filter by display_name of related entity)
	// These come from MRT column filter on columns like province.display_name, clienttype.display_name, etc.
	
	// Build include array for related table filtering
	const includeForFiltering = [];
	
	// Province filter from MRT column (by display_name)
	if (cleanParams.province_display_name) {
		includeForFiltering.push({
			model: dbSAPPS.Province,
			as: "province",
			attributes: [],
			where: {
				display_name: { [likeOp]: `%${cleanParams.province_display_name}%` }
			},
			required: true
		});
	}
	
	// City filter from MRT column (by display_name)
	if (cleanParams.city_display_name) {
		includeForFiltering.push({
			model: dbSAPPS.City,
			as: "city",
			attributes: [],
			where: {
				display_name: { [likeOp]: `%${cleanParams.city_display_name}%` }
			},
			required: true
		});
	}
	
	// ClientType filter from MRT column (by display_name)
	if (cleanParams.clienttype_display_name) {
		includeForFiltering.push({
			model: dbSAPPS.ClientType,
			as: "clienttype",
			attributes: [],
			where: {
				display_name: { [likeOp]: `%${cleanParams.clienttype_display_name}%` }
			},
			required: true
		});
	}
	
	// Specialist filter from MRT column (by display_name)
	if (cleanParams.specialist_display_name) {
		includeForFiltering.push({
			model: dbSAPPS.Specialist,
			as: "specialist",
			attributes: [],
			where: {
				display_name: { [likeOp]: `%${cleanParams.specialist_display_name}%` }
			},
			required: true
		});
	}

	// company scope precedence: company_id wins over companiesId
	if (!cleanParams.company_id && cleanParams.companiesId) {
		const raw = cleanParams.companiesId;
		const ids = Array.isArray(raw) ? raw : typeof raw === "string" && raw.includes(",") ? raw.split(",") : [raw];

		const parsedIds = ids.map((v) => Number(v)).filter((n) => Number.isFinite(n));

		if (parsedIds.length) {
			filterConditions.company_id = { [Op.in]: parsedIds };
		}
	}

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

	// Base include array for fetching related data
	const baseInclude = [
		{
			model: dbSAPPS.ClientType,
			as: "clienttype",
			attributes: ["id", "display_name", "code"],
		},
		{
			model: dbSAPPS.Specialist,
			as: "specialist",
			attributes: ["id", "display_name", "code"],
		},
		{
			model: dbSAPPS.SubSpecialist,
			as: "subspecialist",
			attributes: ["id", "display_name", "code"],
		},
		{
			model: dbSAPPS.ClientSubtype,
			as: "clientsubtype",
			attributes: ["id", "display_name", "code"],
		},
		{
			model: dbSAPPS.Province,
			as: "province",
			attributes: ["id", "display_name", "code"],
		},
		{
			model: dbSAPPS.City,
			as: "city",
			attributes: ["id", "display_name", "code"],
		},
	];

	// Merge base include with filtering includes (if any)
	// This allows combining MRT column filters on related tables with the base include
	const finalInclude = [...baseInclude, ...includeForFiltering];

	try {
		const { count, rows } = await dbSAPPS.Client.findAndCountAll({
			where: filterConditions,
			limit: params.no_pagination ? undefined : limit,
			offset: params.no_pagination ? undefined : offset,
			order: [["modified", "DESC"]],
			include: finalInclude,
		});

		// Summary counts must follow the same filters as the table (server-side pagination)
		const hasEnabledFilter = cleanParams.enabled !== undefined && cleanParams.enabled !== null && String(cleanParams.enabled).trim().length > 0;
		let enabledCount = 0;
		let disabledCount = 0;
		if (hasEnabledFilter) {
			const enabledBool = String(cleanParams.enabled) === "1" || String(cleanParams.enabled).toLowerCase() === "true";
			enabledCount = enabledBool ? count : 0;
			disabledCount = enabledBool ? 0 : count;
		} else {
			const [en, dis] = await Promise.all([
				dbSAPPS.Client.count({ where: { ...filterConditions, enabled: true } }),
				dbSAPPS.Client.count({ where: { ...filterConditions, enabled: false } }),
			]);
			enabledCount = en;
			disabledCount = dis;
		}

		const dataClients = await Promise.all(
			rows.map(async (item) => {
				const modifiedBy = await dbSAPPS.User.findOne({ where: { id: parseInt(item.modified_by) }, attributes: ["full_name"] });
				return {
					...item.toJSON(),
					modifiedBy: modifiedBy ? modifiedBy.dataValues.full_name : null,
				};
			}),
		);

		return {
			clients: dataClients,
			total: count,
			summaryCounts: {
				total: count,
				enabled: enabledCount,
				disabled: disabledCount,
			},
			success: true,
			message: "clients loaded successfully!",
		};
	} catch (error) {
		console.error("Error retrieving clients: ", error);
		const errorMessage = error.message || "Failed to retrieve clients. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function option() {
	try {
		const clients = await dbSAPPS.Client.findAll({
			attributes: ["id", "display_name", "code"],
			where: { enabled: true },
			order: [["display_name", "ASC"]], // Opsional: Mengurutkan hasil berdasarkan
		});

		return {
			success: true,
			clients,
			message: "clients loaded successfully!",
		};
	} catch (error) {
		console.error("Error retrieving clients: ", error);
		const errorMessage = error.message || "Failed to retrieve clients. Please try again later.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function count() {
	try {
		const count = await dbSAPPS.Client.count({
			where: { enabled: true },
		});

		return {
			success: true,
			count,
			message: "clients loaded successfully!",
		};
	} catch (error) {
		console.error("Error counting clients: ", error);
		return {
			success: false,
			message: "Failed to retrieve clients count. Please try again later.",
		};
	}
}

async function create(params) {
	try {
		if (!params.display_name) {
			return {
				success: false,
				message: "Clients name",
			};
		}

		console.log("params", params);

		const existingNameNNumber = await dbSAPPS.Client.findOne({
			where: {
				display_name: params.display_name,
			},
		});

		if (existingNameNNumber)
			return {
				success: false,
				message: `Client name "${params.display_name}" is already taken.`,
			};

		if (!params.enabled) params.enabled = true;

		const newclients = await dbSAPPS.Client.create(params);

		return {
			success: true,
			message: "clients data has been successfully added!",
			clients: newclients,
		};
	} catch (error) {
		const errorMessage = error.message || "An error occurred during create clients processing.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function generateCode(params, incrementValue) {
	try {
		let code = "C";
		let proviceCode = "00",
			cityCode = "00",
			clientTypeCode = "00";
		let specialistCode = "00",
			subSpecialistCode = "00",
			clientSubTypeCode = "00";

		if (params.province_id) {
			const province = await dbSAPPS.Province.findByPk(params.province_id);
			proviceCode = province?.code || "00";
		}

		if (params.city_id) {
			const city = await dbSAPPS.City.findByPk(params.city_id);
			cityCode = city?.code || "00";
		}

		if (params.clienttype_id) {
			const clientType = await dbSAPPS.ClientType.findByPk(params.clienttype_id);
			clientTypeCode = clientType?.code || "00";
		}

		if (params.specialist_id) {
			const specialist = await dbSAPPS.Specialist.findByPk(params.specialist_id);
			specialistCode = specialist?.code || "00";
		}

		if (params.subspecialist_id) {
			const subSpecialist = await dbSAPPS.SubSpecialist.findByPk(params.subspecialist_id);
			subSpecialistCode = subSpecialist?.code || "00";
		}

		if (params.clientsubtype_id) {
			const clientSubtype = await dbSAPPS.ClientSubtype.findByPk(params.clientsubtype_id);
			clientSubTypeCode = clientSubtype?.code || "00";
		}

		// Gunakan nomor urut yang dikirim dari service massUpdateCode
		let increment4DigitAngka = incrementValue.toString().padStart(4, "0");

		const newCode = code + proviceCode + cityCode + clientTypeCode + specialistCode + subSpecialistCode + clientSubTypeCode + increment4DigitAngka;

		params.code = newCode;
		return { success: true, code: newCode };
	} catch (error) {
		console.error("Error generating code:", error);
		return { success: false, error: error.message };
	}
}

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

		if (params.display_name && params.display_name !== clients.display_name) {
			const existingclientsName = await dbSAPPS.Client.findOne({
				where: { display_name: params.display_name },
			});
			if (existingclientsName) {
				return {
					success: false,
					message: `Client name "${params.display_name}" is already taken. Try again!`,
				};
			}
		}

		await clients.update(params);

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

async function massUpdateCode(paramsArray) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const clientIds = paramsArray.map((params) => params.id);

		// Ambil semua clients yang diperlukan dalam satu query
		const clients = await dbSAPPS.Client.findAll({
			where: { id: clientIds },
			transaction,
		});

		if (clients.length !== paramsArray.length) {
			throw new Error("Some clients were not found!");
		}

		// Mapping clients berdasarkan ID
		const clientsMap = new Map(clients.map((client) => [client.id, client]));

		// Ambil client terakhir untuk mendapatkan nomor urut terakhir
		const lastClient = await dbSAPPS.Client.findOne({
			order: [["code", "DESC"]],
			transaction,
		});

		let lastIncrement = 1;
		if (lastClient && lastClient.code) {
			lastIncrement = parseInt(lastClient.code.slice(-4), 10) + 1; // Ambil 4 digit terakhir dan tambahkan 1
		}

		for (const params of paramsArray) {
			const client = clientsMap.get(params.id);
			if (!client) {
				throw new Error(`Client with ID ${params.id} not found!`);
			}

			if (params.code && params.code !== client.code) {
				const existingClientCode = await dbSAPPS.Client.findOne({
					where: { code: params.code },
					transaction,
				});
				if (existingClientCode) {
					throw new Error(`Clients code "${params.code}" is already taken. Try again!`);
				}
			}

			// Gunakan lastIncrement yang dikelola dalam loop ini
			const codeResult = await generateCode(params, lastIncrement);
			if (!codeResult.success) {
				throw new Error(codeResult.error || "Error generating code");
			}

			params.code = codeResult.code;
			lastIncrement++; // Tambah nomor urut agar tidak duplikat

			await client.update(params, { transaction });
		}

		await transaction.commit();
		return {
			success: true,
			message: "All clients data have been successfully updated!",
		};
	} catch (error) {
		await transaction.rollback();
		return {
			success: false,
			message: error.message || "Failed to update clients data.",
		};
	}
}

async function enabled(id, params) {
	try {
		const clients = await dbSAPPS.Client.findByPk(id);
		if (!clients)
			return {
				success: false,
				message: "clients 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;

		await clients.update(updateData);

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

async function _delete(id) {
	try {
		const clients = await dbSAPPS.Client.findByPk(id);
		if (!clients) return { success: false, message: "clients not found!" };

		await clients.destroy();

		return {
			success: true,
			message: "clients successfully deleted!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to delete clients.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
