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

// --- In-memory cache for get-by-hostname (sapps/companies/get-by-hostname?website=...) ---
// TTL dari config.backendConfig.getByHostnameCacheTtlMs (default 5 menit).
const getByHostnameCache = new Map();
const getByHostnameInflight = new Map();
const GET_BY_HOSTNAME_TTL_MS = config.backendConfig?.getByHostnameCacheTtlMs ?? 5 * 60 * 1000;

function isGetByHostname(params) {
	if (!params || typeof params !== "object" || !params.website) return false;
	const filterKeys = ["companiesId", "is_parent", "type"];
	if (filterKeys.some((k) => params[k] != null && params[k] !== "")) return false;
	return true;
}

function invalidateGetByHostnameCache() {
	getByHostnameCache.clear();
}

async function getByHostnameCached(website) {
	const key = String(website || "").trim() || "unknown";
	const now = Date.now();
	const ent = getByHostnameCache.get(key);
	if (ent && ent.expiry > now) return ent.data;

	let prom = getByHostnameInflight.get(key);
	if (prom) return prom;

	prom = getAllInternal({ website: key })
		.then((data) => {
			getByHostnameInflight.delete(key);
			if (data && data.success === true) {
				getByHostnameCache.set(key, { data, expiry: Date.now() + GET_BY_HOSTNAME_TTL_MS });
			}
			return data;
		})
		.catch((err) => {
			getByHostnameInflight.delete(key);
			throw err;
		});
	getByHostnameInflight.set(key, prom);
	return prom;
}

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

const DELIVERY_TRIP_APPROVER_KEYS = [
	"delivery_trip_workflow_approver_type",
	"delivery_trip_workflow_designation_ids",
	"delivery_trip_workflow_territory_ids",
];

/**
 * Get Delivery Trip Approver configuration for a company (from Company Settings > PRIME).
 * Used by Delivery Trips create flow to set workflow approvers by Designation or Territory.
 * @param {number} companyId - Company ID
 * @returns {Promise<{ success: boolean, approverType?: string, designation_ids?: number[], territory_ids?: number[], message?: string }>}
 */
async function getDeliveryTripApproverSettings(companyId) {
	try {
		const id = parseInt(companyId, 10);
		if (!id || isNaN(id)) {
			return { success: false, message: "Invalid company ID." };
		}
		const rows = await dbSAPPS.CompanySetting.findAll({
			where: {
				company_id: id,
				key: { [Op.in]: DELIVERY_TRIP_APPROVER_KEYS },
			},
			attributes: ["key", "value"],
			raw: true,
		});
		const map = {};
		for (const row of rows) {
			map[row.key] = row.value;
		}
		const approverTypeRaw = map.delivery_trip_workflow_approver_type;
		const approverType =
			approverTypeRaw === "Territory" ? "Territory" : "Designation";

		const parseIds = (raw) => {
			if (raw == null) return [];
			try {
				const parsed = typeof raw === "string" ? JSON.parse(raw) : raw;
				if (!Array.isArray(parsed)) return [];
				return parsed.map((v) => parseInt(v, 10)).filter((v) => !isNaN(v) && v > 0);
			} catch {
				return [];
			}
		};

		const designation_ids = parseIds(map.delivery_trip_workflow_designation_ids);
		const territory_ids = parseIds(map.delivery_trip_workflow_territory_ids);

		return {
			success: true,
			approverType,
			designation_ids,
			territory_ids,
		};
	} catch (error) {
		const message = error?.message || "Failed to load delivery trip approver settings.";
		return { success: false, message };
	}
}

// ROBUST: Disabled caching to ensure fresh data from database
const formatWithModifier = async (companies) => {
	return Promise.all(
		companies.map(async (company) => {
			// ROBUST: Query with NO CACHING - force fresh data from database
			const modifier = await dbSAPPS.User.findOne({
				where: {
					id: company.modified_by,
				},
				attributes: ["full_name"],
				benchmark: false, // ROBUST: Disable benchmark to prevent caching
				logging: false, // ROBUST: Disable logging overhead
				raw: false, // Keep as false to maintain proper data structure
			});

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

function formatWithSettings(companies) {
	return companies.map((value) => {
		return {
			...value.dataValues,
			companysettings: Object.keys(value.companysettings).reduce((acc, cur) => {
				acc[value.companysettings[cur].key] = value.companysettings[cur].value;
				return acc;
			}, {}),
		};
	});
}

/**
 * Retrieves all. Untuk get-by-hostname?website=... dipakai cache 5 menit (getByHostnameCached).
 * ROBUST: getAllInternal tanpa cache untuk penggunaan lain (admin, option, dll).
 */
async function getAll(params) {
	if (isGetByHostname(params)) return getByHostnameCached(params.website);
	return getAllInternal(params);
}

/**
 * Implementasi getAll tanpa cache (dipakai getByHostnameCached dan pemanggil getAll dengan filter lain).
 */
async function getAllInternal(params) {
	try {
		const filterConditions = {};

		if (params.companiesId) {
			filterConditions.id = {
				[Op.in]: params.companiesId,
			};
		}

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

		if (params.type) {
			filterConditions.type = {
				[Op.eq]: params.type,
			};
		}

		if (params.website) {
			filterConditions.website = {
				[Op.eq]: params.website,
			};
		}

		let companies = [];
		companies = await dbSAPPS.Company.findAll({
			where: filterConditions,
			order: [["modified", "DESC"]],
			benchmark: false,
			logging: false,
			include: [
				{
					model: dbSAPPS.CompanyAsset,
					as: "companyassets",
					attributes: ["id", "company_id", "logo", "navbar_logo", "login_logo", "loading_logo", "report_logo", "favicon"],
					required: false,
				},
				{
					model: dbSAPPS.CompanySetting,
					as: "companysettings",
					attributes: ["id", "company_id", "key", "value"],
					required: false,
				},
			],
		});

		companies = formatWithSettings(companies);
		companies = await formatWithModifier(companies);

		return {
			companies,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Get options
 * ROBUST: Added user parameter to validate company association with robust error handling and no caching
 */
async function option(params, user = null) {
	try {
		// ROBUST: Validate params first
		if (!params) {
			params = {};
		}

		// ROBUST: Validate company association with user if user is provided
		// Handle case where user might be null/undefined in production
		if (user && user.id) {
			try {
				// ROBUST: Get user's associated companies with proper error handling and NO CACHING
				// Convert user.id to integer to prevent type mismatch issues
				const userId = parseInt(user.id, 10);
				if (isNaN(userId)) {
					console.error('[Company Service] Invalid user.id:', user.id);
					return {
						success: false,
						message: "Invalid user ID provided.",
					};
				}

				// ROBUST: Verify database is ready before querying
				if (dbSAPPS.isReady && !dbSAPPS.isReady()) {
					console.error('[Company Service] Database not ready when querying UserCompany');
					// Log to file for debugging in cPanel
					try {
						const fs = require('fs');
						const path = require('path');
						// Path: sapps-backend/apps/sapps/masters/companies -> sapps-backend/logs
						const logDir = path.join(__dirname, '../../../../logs');
						const errorLogFile = path.join(logDir, 'association-errors.log');
						
						if (!fs.existsSync(logDir)) {
							fs.mkdirSync(logDir, { recursive: true });
						}
						
						fs.appendFileSync(errorLogFile, JSON.stringify({
							timestamp: new Date().toISOString(),
							service: 'company.service',
							function: 'option',
							error: 'Database not ready',
							userId: userId,
						}) + '\n', 'utf8');
					} catch (logError) {
						console.error('Failed to write error log:', logError);
					}
					
					return {
						success: false,
						message: "Database is initializing. Please wait a moment and try again.",
					};
				}
				
				// ROBUST: Verify UserCompany model exists and has associations
				if (!dbSAPPS.UserCompany) {
					console.error('[Company Service] UserCompany model is not available');
					// Log detailed error for debugging in cPanel
					try {
						const fs = require('fs');
						const path = require('path');
						// Path: sapps-backend/apps/sapps/masters/companies -> sapps-backend/logs
						const logDir = path.join(__dirname, '../../../../logs');
						const errorLogFile = path.join(logDir, 'association-errors.log');
						
						if (!fs.existsSync(logDir)) {
							fs.mkdirSync(logDir, { recursive: true });
						}
						
						fs.appendFileSync(errorLogFile, JSON.stringify({
							timestamp: new Date().toISOString(),
							service: 'company.service',
							function: 'option',
							error: 'UserCompany model missing',
							userId: userId,
							availableModels: Object.keys(dbSAPPS).filter(key => typeof dbSAPPS[key] !== 'function'),
						}) + '\n', 'utf8');
					} catch (logError) {
						console.error('Failed to write error log:', logError);
					}
					
					return {
						success: false,
						message: "UserCompany model not loaded. Please contact administrator.",
					};
				}
				
				// ROBUST: Verify UserCompany has required associations
				if (!dbSAPPS.UserCompany.associations || !dbSAPPS.UserCompany.associations.company) {
					console.error('[Company Service] UserCompany association with Company is missing');
					console.error('[Company Service] UserCompany.associations:', dbSAPPS.UserCompany.associations ? Object.keys(dbSAPPS.UserCompany.associations) : 'null');
					
					// Log detailed error for debugging in cPanel
					try {
						const fs = require('fs');
						const path = require('path');
						// Path: sapps-backend/apps/sapps/masters/companies -> sapps-backend/logs
						const logDir = path.join(__dirname, '../../../../logs');
						const errorLogFile = path.join(logDir, 'association-errors.log');
						
						if (!fs.existsSync(logDir)) {
							fs.mkdirSync(logDir, { recursive: true });
						}
						
						fs.appendFileSync(errorLogFile, JSON.stringify({
							timestamp: new Date().toISOString(),
							service: 'company.service',
							function: 'option',
							error: 'UserCompany-Company association missing',
							userId: userId,
							hasAssociations: !!dbSAPPS.UserCompany.associations,
							associationKeys: dbSAPPS.UserCompany.associations ? Object.keys(dbSAPPS.UserCompany.associations) : [],
							hasCompanyAssociation: !!(dbSAPPS.UserCompany.associations && dbSAPPS.UserCompany.associations.company),
							initStatus: dbSAPPS.getInitializationStatus ? dbSAPPS.getInitializationStatus() : 'not available',
						}) + '\n', 'utf8');
					} catch (logError) {
						console.error('Failed to write error log:', logError);
					}
					
					return {
						success: false,
						message: "Database associations not properly initialized. Please contact administrator.",
					};
				}
				
				// ROBUST: Query with NO CACHING - force fresh data from database
				// Use raw: true to get plain objects and avoid Sequelize instance caching
				// Add benchmark: false and ensure fresh connection
				const userCompanies = await dbSAPPS.UserCompany.findAll({
					where: { user_id: userId },
					attributes: ["company_id"],
					raw: true, // ROBUST: Use raw to get plain objects and avoid instance caching
					benchmark: false, // ROBUST: Disable benchmark to prevent caching
					logging: false, // ROBUST: Disable logging overhead
					// ROBUST: Force fresh query by not using model cache
					subQuery: false,
				});

				// ROBUST: Convert company_id to integer and filter out null/undefined values
				// Since raw: true, we get plain objects with company_id directly accessible
				const userCompanyIds = userCompanies
					.map((uc) => {
						// ROBUST: Since raw: true, uc is a plain object with company_id directly accessible
						// Handle both raw objects (plain JS objects from raw: true) and Sequelize instances (fallback)
						let companyId = null;
						
						// Handle raw object (primary case)
						if (uc && typeof uc === 'object' && 'company_id' in uc) {
							companyId = uc.company_id;
						}
						// Handle Sequelize instance (fallback if raw somehow doesn't work)
						else if (uc && typeof uc.get === 'function') {
							companyId = uc.get('company_id');
						}
						// Handle dataValues (Sequelize instance fallback)
						else if (uc && uc.dataValues && 'company_id' in uc.dataValues) {
							companyId = uc.dataValues.company_id;
						}
						
						// Convert to integer if valid
						if (companyId !== null && companyId !== undefined) {
							const parsedId = parseInt(companyId, 10);
							return !isNaN(parsedId) ? parsedId : null;
						}
						return null;
					})
					.filter((id) => id !== null && !isNaN(id));

				// ROBUST: If user has no associated companies, return empty array (not error)
				// This is a valid case - user might not have companies assigned yet
				if (userCompanyIds.length === 0) {
					console.warn(`[Company Service] User ${userId} has no associated companies`);
					return {
						options: [],
						success: true,
						message: "No companies associated with this user.",
					};
				}

				// ROBUST: If params.companiesId is provided, validate that all requested companies are associated with user
				// Handle both array and single value, and convert to integers for comparison
				if (params.companiesId) {
					let requestedCompanyIds = [];
					
					if (Array.isArray(params.companiesId)) {
						requestedCompanyIds = params.companiesId.map(id => parseInt(id, 10)).filter(id => !isNaN(id));
					} else {
						const singleId = parseInt(params.companiesId, 10);
						if (!isNaN(singleId)) {
							requestedCompanyIds = [singleId];
						}
					}

					if (requestedCompanyIds.length > 0) {
						// ROBUST: Check if all requested companies are in user's associated companies
						// Use strict comparison to ensure type matching
						const invalidCompanyIds = requestedCompanyIds.filter((id) => {
							// Strict type and value comparison
							return !userCompanyIds.some((validId) => validId === id);
						});

						if (invalidCompanyIds.length > 0) {
							console.error(`[Company Service] User ${userId} requested invalid company IDs: ${invalidCompanyIds.join(", ")}`);
							console.error(`[Company Service] User ${userId} has access to company IDs: ${userCompanyIds.join(", ")}`);
							// ROBUST: Return error message that matches frontend expectation
							return {
								success: false,
								message: `companie is not associated to usercompanie!`,
							};
						}
					}
				} else {
					// ROBUST: If no companiesId in params, just use all user's companies (normal case)
					// This is the typical flow - user requests all their companies
				}

				// ROBUST: Filter by user's associated companies
				params.companiesId = userCompanyIds;
			} catch (dbError) {
				// ROBUST: Handle database errors gracefully including Sequelize association errors
				console.error('[Company Service] Database error when fetching user companies:', dbError);
				
				// ROBUST: Handle specific Sequelize association errors
				if (dbError.name === 'SequelizeAssociationError' || 
				    dbError.message && dbError.message.includes('not associated')) {
					console.error('[Company Service] Association error detected - this might be a cache issue');
					return {
						success: false,
						message: "companie is not associated to usercompanie!",
					};
				}
				
				return {
					success: false,
					message: "Failed to fetch user companies. Please try again.",
				};
			}
		} else {
			// ROBUST: Log warning if user is not provided (should not happen in production with authorize middleware)
			if (!user) {
				console.warn('[Company Service] User not provided to option function - this should not happen with authorize middleware');
			} else if (!user.id) {
				console.warn('[Company Service] User provided but user.id is missing:', user);
			}
		}

		// filter
		const filterConditions = {};

		if (params.companiesId) {
			filterConditions.id = {
				[Op.in]: params.companiesId,
			};
		}

		if (params.is_parent) {
			filterConditions.is_parent = {
				// true is always sent as a string from the frontend
				[Op.eq]: params.is_parent === "true" ? true : false,
			};
		}

		if (params.type) {
			filterConditions.type = {
				[Op.eq]: params.type,
			};
		}

		if (params.website) {
			filterConditions.website = {
				[Op.eq]: params.website,
			};
		}

		// must be enabled
		filterConditions.enabled = {
			[Op.eq]: true,
		};

		// ROBUST: Query with NO CACHING - force fresh data from database
		let options = [];
		options = await dbSAPPS.Company.findAll({
			attributes: ["id", "display_name", "slug_name", "abbr"],
			where: filterConditions,
			order: [["name", "ASC"]],
			raw: false, // Keep as false for Company query to maintain proper data structure
			benchmark: false, // ROBUST: Disable benchmark to prevent caching
			logging: false, // ROBUST: Disable logging overhead
		});

		return {
			options,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		// ROBUST: Enhanced error handling including Sequelize association errors
		console.error('[Company Service] Error in option function:', error);
		
		// ROBUST: Handle specific Sequelize association errors
		if (error.name === 'SequelizeAssociationError' || 
		    (error.message && error.message.includes('not associated'))) {
			return {
				success: false,
				message: "companie is not associated to usercompanie!",
			};
		}
		
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Count
 * ROBUST: Disabled caching to ensure fresh data from database
 */
async function count(params) {
	try {
		// filter
		const filterConditions = {};

		// must be enabled
		filterConditions.enabled = {
			[Op.eq]: true,
		};

		// ROBUST: Query with NO CACHING - force fresh data from database
		const count = await dbSAPPS.Company.count({
			where: filterConditions,
			benchmark: false, // ROBUST: Disable benchmark to prevent caching
			logging: false, // ROBUST: Disable logging overhead
		});

		return {
			count,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Creates a new
 */
async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const existing = await dbSAPPS.Company.findOne({
			where: {
				[Op.or]: [{ name: params.name }, { display_name: params.display_name }, { slug_name: params.slug_name }],
			},
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `Data is already taken, please try again!`,
			};
		}

		// save
		const company = await dbSAPPS.Company.create(params, { transaction });

		// save settings
		if (params.settings) {
			const settings = JSON.parse(params.settings);
			try {
				await dbSAPPS.CompanySetting.bulkCreate(
					Object.keys(settings)
						.map((key) => {
							if (settings[key] !== null && settings[key] !== "") {
								return {
									company_id: company.id,
									key,
									value: settings[key],
									creation: params.creation,
									owner: params.owner,
									modified: params.modified,
									modified_by: params.modified_by,
								};
							}
						})
						.filter((item) => item),
					{ transaction }
				);
			} catch (error) {
				await transaction.rollback();
				return {
					success: false,
					message: error?.message || `Failed to save company assets, please try again!`,
				};
			}
		}

		// save logo and other assets
		const companyAssets = {
			company_id: company.id,
			logo: params.logo || null,
			navbar_logo: params.navbar_logo || null,
			login_logo: params.login_logo || null,
			loading_logo: params.loading_logo || null,
			report_logo: params.report_logo || null,
			favicon: params.favicon || null,
			creation: params.creation,
			owner: params.owner,
			modified: params.modified,
			modified_by: params.modified_by,
		};
		try {
			await dbSAPPS.CompanyAsset.create(companyAssets, { transaction });
		} catch (error) {
			await transaction.rollback();
			return {
				success: false,
				message: `Failed to save company assets, please try again!`,
			};
		}

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Update an existing
 */
async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const company = await dbSAPPS.Company.findByPk(id);
		if (!company) return { success: false, message: "ID not found!" };

		const fields = ["name", "display_name", "slug_name"];
		const existing = await dbSAPPS.Company.findOne({
			where: {
				[Op.or]: fields.map((field) => ({
					[field]: params[field],
					id: { [Op.ne]: id },
				})),
			},
			transaction,
		});
		if (existing) {
			return {
				success: false,
				message: `Data is already taken, please try again!`,
			};
		}

		// save
		await company.update(params, { transaction });

		// save settings
		if (params.settings) {
			const settings = JSON.parse(params.settings);
			try {
				await dbSAPPS.CompanySetting.destroy({
					where: { company_id: id },
					transaction,
				});
				await dbSAPPS.CompanySetting.bulkCreate(
					Object.keys(settings)
						.map((key) => {
							if (settings[key] !== null && settings[key] !== "") {
								return {
									company_id: id,
									key,
									value: settings[key],
									creation: new Date(),
									owner: params.modified_by,
									modified: params.modified,
									modified_by: params.modified_by,
								};
							}
						})
						.filter((item) => item),
					{ transaction }
				);
			} catch (error) {
				await transaction.rollback();
				return {
					success: false,
					message: error?.message || `Failed to save company assets, please try again!`,
				};
			}
		}

		// save logo and other assets
		const companyAssets = {
			logo: params.logo || null,
			navbar_logo: params.navbar_logo || null,
			login_logo: params.login_logo || null,
			loading_logo: params.loading_logo || null,
			report_logo: params.report_logo || null,
			favicon: params.favicon || null,
			creation: new Date(),
			owner: params.modified_by,
			modified: params.modified,
			modified_by: params.modified_by,
		};
		try {
			// delete all existing assets
			await dbSAPPS.CompanyAsset.destroy({
				where: {
					company_id: id,
				},
				transaction,
			});

			// create new assets
			await dbSAPPS.CompanyAsset.create({ company_id: id, ...companyAssets }, { transaction });
		} catch (error) {
			await transaction.rollback();
			return {
				success: false,
				message: error?.message || `Failed to save company assets, please try again!`,
			};
		}

		await transaction.commit();
		invalidateGetByHostnameCache();
		return {
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Enabled / Disabled
 */
async function enabled(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const company = await dbSAPPS.Company.findByPk(id);
		if (!company) return { success: false, message: "ID not found!" };

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

		// save
		await company.update(updateData, { transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Delete
 */
async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const company = await dbSAPPS.Company.findByPk(id);
		if (!company) return { success: false, message: "ID not found!" };

		// validate used in attendance
		const usedInUserCompany = await dbSAPPS.UserCompany.count({
			where: {
				company_id: id,
			},
			transaction,
		});
		if (usedInUserCompany > 0) {
			throw new Error(`This company is being used in User, cannot be deleted!`);
		}

		const usedInEmployee = await dbSAPPS.Employee.count({
			where: {
				company_id: id,
			},
			transaction,
		});
		if (usedInEmployee > 0) {
			throw new Error(`This company is being used in Employee, cannot be deleted!`);
		}

		const usedInPatient = await dbSAPPS.Patient.count({
			where: {
				company_id: id,
			},
			transaction,
		});
		if (usedInPatient > 0) {
			throw new Error(`This company is being used in Patient, cannot be deleted!`);
		}

		// delete
		await company.destroy({ transaction });

		await transaction.commit();
		invalidateGetByHostnameCache();
		return {
			success: true,
			message: "Deleted successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to delete, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
