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

/** Helper to clean undefined values from an object */
const clean = (obj) => {
	const newObj = {};
	Object.keys(obj).forEach((key) => {
		if (obj[key] !== undefined) newObj[key] = obj[key];
	});
	return newObj;
};

/** Normalize field types for MySQL safety */
const normalize = (data) => {
	const booleans = ["enabled"];
	const integers = ["model_id", "status_id", "category_id", "location_id", "company_id", "owner", "modified_by", "assigned_to", "default_location_id"];
	const decimals = ["purchase_cost"];

	booleans.forEach((f) => {
		if (data[f] !== undefined && data[f] !== null) {
			if (typeof data[f] === "string") data[f] = data[f].toLowerCase() === "true";
			else data[f] = !!data[f];
		}
	});

	integers.forEach((f) => {
		if (data[f] !== undefined && data[f] !== null) {
			if (data[f] === "" || data[f] === "null") data[f] = null;
			else {
				const val = parseInt(data[f], 10);
				data[f] = isNaN(val) ? null : val;
			}
		}
	});

	decimals.forEach((f) => {
		if (data[f] !== undefined && data[f] !== null) {
			if (data[f] === "" || data[f] === "null") data[f] = null;
			else {
				const val = parseFloat(data[f]);
				data[f] = isNaN(val) ? 0 : val;
			}
		}
	});

	return data;
};

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

function escapeForLike(s) {
	if (s == null || typeof s !== "string") return "";
	return String(s).replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
}

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

/**
 * Log an asset action to the assetlogs table
 */
async function logAction(params, transaction) {
    const { item_id, item_type, action, target_id, target_type, note, user_id, company_id } = params;
    await dbSAPPS.AssetLog.create({
        creation: new Date(),
        modified: new Date(),
        modified_by: user_id,
        owner: user_id,
        item_id,
        item_type,
        action,
        target_id,
        target_type,
        note,
        company_id
    }, { transaction });
}

async function getAll(params) {
	try {
		const page = parseInt(params.page, 10) || 1;
		const limit = parseInt(params.limit, 10) || 25;
		const offset = (page - 1) * limit;

		const filterConditions = {};
		if (params.company_id) filterConditions.company_id = { [Op.eq]: params.company_id };
		else if (params.companiesId?.length) filterConditions.company_id = { [Op.in]: params.companiesId };
		
		if (params.f_asset_tag) filterConditions.asset_tag = { [Op.like]: `%${escapeForLike(params.f_asset_tag)}%` };
		if (params.f_serial) filterConditions.serial = { [Op.like]: `%${escapeForLike(params.f_serial)}%` };
        if (params.f_name) filterConditions.name = { [Op.like]: `%${escapeForLike(params.f_name)}%` };

		const andConditions = [];
		if (params.q && String(params.q).trim()) {
			const q = escapeForLike(params.q.trim());
			andConditions.push({
				[Op.or]: [
					{ asset_tag: { [Op.like]: `%${q}%` } },
					{ serial: { [Op.like]: `%${q}%` } },
                    { name: { [Op.like]: `%${q}%` } },
					{ "$company.display_name$": { [Op.like]: `%${q}%` } },
					{ "$model.display_name$": { [Op.like]: `%${q}%` } },
				],
			});
		}
		const whereClause = { ...filterConditions, ...(andConditions.length ? { [Op.and]: andConditions } : {}) };

		const { count, rows } = await dbSAPPS.Asset.findAndCountAll({
			where: whereClause,
			order: [["modified", "DESC"]],
			limit,
			offset,
			subQuery: false,
			include: [
				{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
				{ model: dbSAPPS.AssetModel, as: "model", attributes: ["id", "display_name", "model_number"], include: [
                    { model: dbSAPPS.AssetManufacturer, as: "manufacturer", attributes: ["display_name"] },
                    { model: dbSAPPS.AssetCategory, as: "category", attributes: ["display_name"] }
                ]},
				{ model: dbSAPPS.AssetStatusLabel, as: "status", attributes: ["id", "display_name", "type", "color"] },
				{ model: dbSAPPS.AssetLocation, as: "location", attributes: ["id", "display_name"], required: false },
                { model: dbSAPPS.Employee, as: "assignedEmployee", attributes: ["id", "full_name"], required: false },
                { model: dbSAPPS.Department, as: "assignedDepartment", attributes: ["id", "name"], required: false },
			],
		});

		let list = rows.map((r) => ({
			...r.dataValues,
			company_name: r?.company?.display_name,
			model_name: r?.model?.display_name,
            manufacturer_name: r?.model?.manufacturer?.display_name,
            category_name: r?.model?.category?.display_name,
			status_name: r?.status?.display_name,
            status_type: r?.status?.type,
            status_color: r?.status?.color,
			location_name: r?.location?.display_name,
            assigned_name: r?.assigned_type === "Employee" ? r?.assignedEmployee?.full_name : (r?.assigned_type === "Department" ? r?.assignedDepartment?.name : null)
		}));
        
		list = await formatWithModifier(list);
		return { data: list, total: count, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load, please try again!" };
	}
}

async function getById(id) {
    try {
        const row = await dbSAPPS.Asset.findByPk(id, {
            include: [
                { model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
				{ model: dbSAPPS.AssetModel, as: "model", include: [
                    { model: dbSAPPS.AssetManufacturer, as: "manufacturer" },
                    { model: dbSAPPS.AssetCategory, as: "category" }
                ]},
				{ model: dbSAPPS.AssetStatusLabel, as: "status" },
				{ model: dbSAPPS.AssetLocation, as: "location" },
                { model: dbSAPPS.AssetLocation, as: "defaultLocation" },
                { model: dbSAPPS.Employee, as: "assignedEmployee", attributes: ["id", "full_name"] },
                { model: dbSAPPS.Department, as: "assignedDepartment", attributes: ["id", "name"] },
            ]
        });
        if (!row) return { success: false, message: "Asset not found!" };

        // Fetch logs
        const logs = await dbSAPPS.AssetLog.findAll({
            where: { item_id: id, item_type: "asset" },
            order: [["creation", "DESC"]],
            include: [
                { model: dbSAPPS.User, as: "executor", attributes: ["full_name"] },
                { model: dbSAPPS.Employee, as: "targetEmployee", attributes: ["full_name"], required: false },
                { model: dbSAPPS.Department, as: "targetDepartment", attributes: ["name"], required: false },
            ]
        });

        // Fetch Maintenances
        const maintenances = await dbSAPPS.AssetMaintenance.findAll({
            where: { asset_id: id },
            order: [["start_date", "DESC"]]
        });

        return { 
            asset: {
                ...row.dataValues,
                assigned_name: row?.assigned_type === "Employee" ? row?.assignedEmployee?.full_name : (row?.assigned_type === "Department" ? row?.assignedDepartment?.name : null)
            }, 
            logs, 
            maintenances,
            success: true, 
            message: "Loaded successfully!" 
        };
    } catch (error) {
        return { success: false, message: error?.message || "Failed to load!" };
    }
}

async function option(params) {
	try {
		const filterConditions = { enabled: { [Op.eq]: true } };
		if (params.company_id) filterConditions.company_id = { [Op.eq]: params.company_id };
		if (params.companiesId) filterConditions.company_id = { [Op.in]: params.companiesId };

		let options = await dbSAPPS.Asset.findAll({
			where: filterConditions,
			attributes: ["id", "asset_tag", "name", "serial"],
			order: [["asset_tag", "ASC"]],
		});
		return { options, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load!" };
	}
}

async function count(params) {
	try {
		const count = await dbSAPPS.Asset.count({ where: { enabled: { [Op.eq]: true } } });
		return { count, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load!" };
	}
}

async function create(params) {
	try {
		const data = normalize(clean(params));
		if (!data.creation) data.creation = new Date();
		data.modified = new Date();
		if (!data.owner) data.owner = 1;
		if (!data.modified_by) data.modified_by = 1;

		// uniqueness check outside transaction
		const existing = await dbSAPPS.Asset.findOne({
			where: { asset_tag: data.asset_tag, company_id: data.company_id },
		});
		if (existing) return { success: false, message: `Asset Tag "${data.asset_tag}" is already taken.` };

		const transaction = await dbSAPPS.sequelize.transaction();
		try {
			const row = await dbSAPPS.Asset.create(data, { transaction });
			
			await logAction({
				item_id: row.id,
				item_type: "asset",
				action: "create",
				note: "Asset created",
				user_id: data.owner,
				company_id: data.company_id
			}, transaction);

			await transaction.commit();
			return { success: true, message: "Saved successfully!" };
		} catch (error) {
			if (transaction) await transaction.rollback();
			throw error;
		}
	} catch (error) {
		console.error("Asset Create Error:", error);
		return { success: false, message: error?.message || "Failed to save!" };
	}
}

async function update(id, params) {
	const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };

	try {
		const row = await dbSAPPS.Asset.findByPk(pk);
		if (!row) return { success: false, message: "ID not found!" };

		const updateData = normalize(clean(params));

		// Uniqueness check outside transaction
		if (updateData.asset_tag) {
			const existing = await dbSAPPS.Asset.findOne({
				where: { asset_tag: updateData.asset_tag, company_id: updateData.company_id || row.company_id, id: { [Op.ne]: pk } },
			});
			if (existing) return { success: false, message: `Asset Tag "${updateData.asset_tag}" is already taken.` };
		}

		if (!updateData.creation) {
			if (!row.creation) updateData.creation = new Date();
			else delete updateData.creation;
		}
		updateData.modified = new Date();
		if (!updateData.modified_by) updateData.modified_by = 1;

		const transaction = await dbSAPPS.sequelize.transaction();
		try {
			await row.update(updateData, { transaction });

			await logAction({
				item_id: pk,
				item_type: "asset",
				action: "update",
				note: "Asset updated",
				user_id: updateData.modified_by,
				company_id: updateData.company_id || row.company_id
			}, transaction);

			await transaction.commit();
			return { success: true, message: "Saved successfully!" };
		} catch (error) {
			if (transaction) await transaction.rollback();
			throw error;
		}
	} catch (error) {
		console.error("Asset Update Error:", error);
		return { success: false, message: error?.message || "Failed to save!" };
	}
}

async function enabled(id, params) {
	const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const row = await dbSAPPS.Asset.findByPk(pk);
		if (!row) return { success: false, message: "ID not found!" };
		await row.update({ enabled: params.enabled, modified_by: params.modified_by, modified: new Date() }, { transaction });
		await transaction.commit();
		return { success: true, message: "Saved successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save!" };
	}
}

async function _delete(id) {
	const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const row = await dbSAPPS.Asset.findByPk(pk, { transaction });
		if (!row) return { success: false, message: "ID not found!" };

		// Check if checked out
		if (row.assigned_to) {
			await transaction.rollback();
			return { success: false, message: "Cannot delete asset that is currently checked out!" };
		}

		await row.destroy({ transaction });
		await transaction.commit();
		return { success: true, message: "Deleted successfully!" };
	} catch (error) {
		if (transaction) await transaction.rollback();
		return { success: false, message: error?.message || "Failed to delete!" };
	}
}

async function checkout(id, params) {
    const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const row = await dbSAPPS.Asset.findByPk(pk);
        if (!row) return { success: false, message: "Asset not found!" };
        if (row.assigned_to) return { success: false, message: "Asset is already checked out!" };

        await row.update({
            assigned_to: params.target_id,
            assigned_type: params.target_type,
            assigned_at: new Date(),
            modified: new Date(),
            modified_by: params.user_id
        }, { transaction });

        await logAction({
            item_id: pk,
            item_type: "asset",
            action: "checkout",
            target_id: params.target_id,
            target_type: params.target_type,
            note: params.note,
            user_id: params.user_id,
            company_id: row.company_id
        }, transaction);

        await transaction.commit();
        return { success: true, message: "Checked out successfully!" };
    } catch (error) {
        await transaction.rollback();
        return { success: false, message: error?.message || "Checkout failed!" };
    }
}

async function checkin(id, params) {
    const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const row = await dbSAPPS.Asset.findByPk(pk);
        if (!row) return { success: false, message: "Asset not found!" };
        
        await logAction({
            item_id: pk,
            item_type: "asset",
            action: "checkin",
            target_id: row.assigned_to,
            target_type: row.assigned_type,
            note: params.note,
            user_id: params.user_id,
            company_id: row.company_id
        }, transaction);

        await row.update({
            assigned_to: null,
            assigned_type: null,
            assigned_at: null,
            modified: new Date(),
            modified_by: params.user_id
        }, { transaction });

        await transaction.commit();
        return { success: true, message: "Checked in successfully!" };
    } catch (error) {
        await transaction.rollback();
        return { success: false, message: error?.message || "Checkin failed!" };
    }
}

async function maintenance(params) {
    const transaction = await dbSAPPS.sequelize.transaction();
    try {
        const maintenance = await dbSAPPS.AssetMaintenance.create(params, { transaction });
        
        await logAction({
            item_id: params.asset_id,
            item_type: "asset",
            action: "maintenance",
            note: `Maintenance: ${params.title}. Result: ${params.notes}`,
            user_id: params.owner,
            company_id: params.company_id
        }, transaction);

        await transaction.commit();
        return { success: true, message: "Maintenance recorded successfully!" };
    } catch (error) {
        await transaction.rollback();
        return { success: false, message: error?.message || "Maintenance record failed!" };
    }
}
