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

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

/**
 * Sync TerritoryVersionChilds when master Territory is updated
 * Updates all territoryversionchilds that reference this territory_id
 * @param {Number} territoryId - The ID of the master territory
 * @param {Object} updatedTerritoryData - The updated territory data
 * @param {Object} transaction - Sequelize transaction object
 * @returns {Promise<void>}
 */
async function syncTerritoryVersionChildsOnMasterUpdate(territoryId, updatedTerritoryData, transaction) {
	try {
		// Find all territoryversionchilds that reference this territory
		const territoryVersionChilds = await dbSAPPS.TerritoryVersionChild.findAll({
			where: { territory_id: territoryId },
			transaction,
		});

		if (!territoryVersionChilds || territoryVersionChilds.length === 0) {
			return; // No childs to sync
		}

		// Fields to sync from master territory
		const fieldsToSync = ["name", "display_name", "slug_name", "code", "type", "is_parent", "description", "company_id"];

		// Prepare update data (only include fields that exist in updatedTerritoryData)
		const updateData = {};
		fieldsToSync.forEach((field) => {
			if (updatedTerritoryData[field] !== undefined && updatedTerritoryData[field] !== null) {
				updateData[field] = updatedTerritoryData[field];
			}
		});

		// Also update modified timestamp
		updateData.modified = Date.now();

		// Update all related territoryversionchilds
		if (Object.keys(updateData).length > 0) {
			await dbSAPPS.TerritoryVersionChild.update(updateData, {
				where: { territory_id: territoryId },
				transaction,
			});
		}
	} catch (error) {
		console.error("Error syncing TerritoryVersionChilds on master update:", error);
		// Re-throw to trigger transaction rollback and prevent inconsistent data
		throw error;
	}
}

/**
 * Delete TerritoryVersionChilds when master Territory is deleted
 * Removes all territoryversionchilds that reference this territory_id
 * @param {Number} territoryId - The ID of the master territory
 * @param {Object} transaction - Sequelize transaction object
 * @returns {Promise<void>}
 */
async function deleteTerritoryVersionChildsOnMasterDelete(territoryId, transaction) {
	try {
		// Find all territoryversionchilds that reference this territory
		const territoryVersionChilds = await dbSAPPS.TerritoryVersionChild.findAll({
			where: { territory_id: territoryId },
			transaction,
		});

		if (!territoryVersionChilds || territoryVersionChilds.length === 0) {
			return; // No childs to delete
		}

		// Delete all related territoryversionchilds
		await dbSAPPS.TerritoryVersionChild.destroy({
			where: { territory_id: territoryId },
			transaction,
		});
	} catch (error) {
		console.error("Error deleting TerritoryVersionChilds on master delete:", error);
		// Re-throw to trigger transaction rollback and prevent orphaned data
		throw error;
	}
}

function parseCompaniesId(input) {
	if (input == null) return [];
	// Express query could provide: ['1','2'], '1,2', '1', or JSON string '[1,2]'
	let raw = input;
	if (typeof raw === "string") {
		const trimmed = raw.trim();
		if (trimmed.startsWith("[") && trimmed.endsWith("]")) {
			try {
				raw = JSON.parse(trimmed);
			} catch (e) {
				raw = trimmed;
			}
		} else if (trimmed.includes(",")) {
			raw = trimmed.split(",");
		}
	}
	const arr = Array.isArray(raw) ? raw : [raw];
	return arr.map((v) => Number.parseInt(String(v), 10)).filter((n) => Number.isFinite(n));
}

async function getAll(params = {}) {
	try {
		const { q, company_id, companiesId, f_name, f_display_name, f_slug_name, f_code, f_company, enabled } = params || {};

		const andConditions = [];

		// Company precedence: company_id wins over companiesId
		if (company_id !== undefined && company_id !== null && String(company_id).trim() !== "") {
			const cid = Number.parseInt(String(company_id), 10);
			if (Number.isFinite(cid)) {
				andConditions.push({ company_id: { [Op.eq]: cid } });
			}
		} else {
			const ids = parseCompaniesId(companiesId);
			if (ids.length > 0) {
				andConditions.push({ company_id: { [Op.in]: ids } });
			}
		}

		// Column filters (optional)
		if (f_name) {
			andConditions.push({ name: { [Op.like]: `%${String(f_name)}%` } });
		}
		if (f_display_name) {
			andConditions.push({ display_name: { [Op.like]: `%${String(f_display_name)}%` } });
		}
		if (f_slug_name) {
			andConditions.push({ slug_name: { [Op.like]: `%${String(f_slug_name)}%` } });
		}
		if (f_code) {
			andConditions.push({ code: { [Op.like]: `%${String(f_code)}%` } });
		}
		if (f_company) {
			andConditions.push({ "$company.name$": { [Op.like]: `%${String(f_company)}%` } });
		}
		if (enabled !== undefined && enabled !== null && String(enabled) !== "") {
			const en = typeof enabled === "string" ? enabled === "1" || enabled.toLowerCase() === "true" : !!enabled;
			andConditions.push({ enabled: { [Op.eq]: en } });
		}

		// Global search q across fields + company
		if (q && String(q).trim().length > 0) {
			const search = String(q).trim();
			andConditions.push({
				[Op.or]: [
					{ name: { [Op.like]: `%${search}%` } },
					{ display_name: { [Op.like]: `%${search}%` } },
					{ slug_name: { [Op.like]: `%${search}%` } },
					{ code: { [Op.like]: `%${search}%` } },
					{ "$company.name$": { [Op.like]: `%${search}%` } },
				],
			});
		}

		const where = andConditions.length > 0 ? { [Op.and]: andConditions } : {};

		const territories = await dbSAPPS.Territory.findAll({
			where,
			order: [["modified", "DESC"]],
			include: {
				model: dbSAPPS.Company,
				as: "company",
				attributes: ["name"],
			},
		});

		const rowCount = Array.isArray(territories) ? territories.length : 0;
		const enabledCount = Array.isArray(territories) ? territories.filter((t) => t && t.enabled === true).length : 0;
		const summaryCounts = {
			total: rowCount,
			enabled: enabledCount,
			disabled: Math.max(rowCount - enabledCount, 0),
		};

		return {
			territories,
			rowCount,
			summaryCounts,
			success: true,
			message: "Territories has been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed to load territories!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

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

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

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

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

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

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

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

	try {
		const territories = await dbSAPPS.Territory.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			include: {
				model: dbSAPPS.Company,
				as: "company",
				attributes: ["name"],
			},
		});

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

async function option() {
	try {
		const territories = await dbSAPPS.Territory.findAll({
			attributes: ["id", "name", "display_name", "slug_name", "code", "is_parent", "description", "company_id"],
			where: { enabled: true },
			order: [["name", "ASC"]],
			include: {
				model: dbSAPPS.Company,
				as: "company",
				attributes: ["name"],
			},
		});

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

async function optionParent() {
	try {
		const territories = await dbSAPPS.Territory.findAll({
			attributes: ["id", "name", "display_name", "slug_name", "code", "is_parent", "description"],
			where: {
				enabled: true,
				is_parent: true,
			},
			order: [["name", "ASC"]],
		});

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

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

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

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		if (!params.name || !params.display_name) return { success: false, message: "Name and display name are required!" };

		const existingName = await dbSAPPS.Territory.findOne({
			where: { name: params.name, company_id: params.company_id },
			transaction,
		});
		if (existingName)
			return {
				success: false,
				message: `Name "${params.name}" is already taken.`,
			};

		const existingDisplayName = await dbSAPPS.Territory.findOne({
			where: {
				display_name: params.display_name,
				company_id: params.company_id,
			},
			transaction,
		});
		if (existingDisplayName)
			return {
				success: false,
				message: `display name "${params.display_name}" is already taken.`,
			};

		await dbSAPPS.Territory.create(params, { transaction });
		await transaction.commit();
		return {
			success: true,
			message: "Territory has been created!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "An error occurred during creating territory.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		if (params.name && params.name !== territory.name) {
			const existingName = await dbSAPPS.Territory.findOne({
				where: { name: params.name, company_id: params.company_id },
				transaction,
			});
			if (existingName) {
				if (existingName.id != params.id) {
					return {
						success: false,
						message: `Name "${params.name}" is already taken. Try again!`,
					};
				}
			}
		}

		if (params.display_name && params.display_name !== territory.display_name) {
			const existingDisplayName = await dbSAPPS.Territory.findOne({
				where: {
					display_name: params.display_name,
					company_id: params.company_id,
				},
				transaction,
			});
			if (existingDisplayName) {
				if (existingDisplayName.id != params.id) {
					return {
						success: false,
						message: `Display name "${params.display_name}" is already taken. Try again!`,
					};
				}
			}
		}

		// Get current territory data before update (for comparison)
		const currentTerritoryData = {
			name: territory.name,
			display_name: territory.display_name,
			slug_name: territory.slug_name,
			code: territory.code,
			type: territory.type,
			is_parent: territory.is_parent,
			description: territory.description,
			company_id: territory.company_id,
		};

		// Update master territory
		await territory.update(params, { transaction });

		// Check if any syncable fields have changed
		const fieldsToSync = ["name", "display_name", "slug_name", "code", "type", "is_parent", "description", "company_id"];
		const hasChanges = fieldsToSync.some((field) => params[field] !== undefined && params[field] !== null && params[field] !== currentTerritoryData[field]);

		// Sync territoryversionchilds if there are changes to syncable fields
		if (hasChanges) {
			// Merge current data with updated params for complete sync
			const syncData = { ...currentTerritoryData, ...params };
			await syncTerritoryVersionChildsOnMasterUpdate(id, syncData, transaction);
		}

		await transaction.commit();
		return {
			success: true,
			message: "Territory has been updated!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to update territory.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function enabled(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const territory = await dbSAPPS.Territory.findByPk(id, { transaction });
		if (!territory)
			return {
				success: false,
				message: "Territory 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 territory.update(updateData, { transaction });
		await transaction.commit();
		return {
			success: true,
			message: "Territory has been updated!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to update territory.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

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

		// Delete all related territoryversionchilds first
		await deleteTerritoryVersionChildsOnMasterDelete(id, transaction);

		// Then delete the master territory
		await territory.destroy({ transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Territory has been deleted!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "Failed to delete territory.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
