"use strict";

module.exports = {
	async up(queryInterface) {
		// Safety-first data migration:
		// - Create JobRole master rows from existing employees.job_role
		// - Only set employees.jobrole_id when it is NULL
		// - Never deletes or overwrites existing jobrole_id
		const tableJobRoles = "jobroles";
		const tableEmployees = "employees";

		const existingTablesRaw = await queryInterface.showAllTables();
		const existingTables = (existingTablesRaw || [])
			.map((t) => (typeof t === "string" ? t : t?.tableName || t?.name))
			.filter(Boolean)
			.map((t) => String(t).toLowerCase());
		if (!existingTables.includes(tableJobRoles)) {
			// If jobroles table doesn't exist, skip silently (migration order issue).
			return;
		}
		if (!existingTables.includes(tableEmployees)) return;

		// 1) Insert missing jobroles from employees.job_role
		await queryInterface.sequelize.query(
			`
			INSERT INTO jobroles (
				creation,
				modified,
				modified_by,
				owner,
				enabled,
				name,
				display_name,
				company_id
			)
			SELECT
				NOW() AS creation,
				NOW() AS modified,
				1 AS modified_by,
				1 AS owner,
				1 AS enabled,
				t.job_role AS name,
				t.job_role AS display_name,
				t.company_id AS company_id
			FROM (
				SELECT DISTINCT
					e.company_id,
					TRIM(e.job_role) AS job_role
				FROM employees e
				WHERE e.job_role IS NOT NULL
				  AND TRIM(e.job_role) <> ''
			) t
			WHERE NOT EXISTS (
				SELECT 1
				FROM jobroles jr
				WHERE jr.company_id <=> t.company_id
				  AND (jr.display_name = t.job_role OR jr.name = t.job_role)
			);
			`,
		);

		// 2) Backfill employees.jobrole_id using the master
		await queryInterface.sequelize.query(
			`
			UPDATE employees e
			JOIN jobroles jr
			  ON jr.company_id <=> e.company_id
			 AND (jr.display_name = TRIM(e.job_role) OR jr.name = TRIM(e.job_role))
			SET e.jobrole_id = jr.id
			WHERE e.jobrole_id IS NULL
			  AND e.job_role IS NOT NULL
			  AND TRIM(e.job_role) <> '';
			`,
		);
	},

	// Down is intentionally a no-op to avoid destructive changes.
	async down() {
		return;
	},
};
