const XLSX = require('xlsx');
const dbSAPPS = require('../../helpers/db');
const { Op } = require('sequelize');

module.exports = {
    importAssets
};

/**
 * Bulk import assets from Excel
 * @param {object} params - { filename, company_id, user_id }
 */
async function importAssets(params) {
    const { filename, company_id, user_id } = params;
    const filePath = `./public/data/imports/${filename}`; // Adjust path as needed

    try {
        const workbook = XLSX.readFile(filePath);
        const sheet = workbook.Sheets[workbook.SheetNames[0]];
        const rows = XLSX.utils.sheet_to_json(sheet);

        const results = {
            total: rows.length,
            success: 0,
            failed: 0,
            errors: []
        };

        for (const row of rows) {
            const transaction = await dbSAPPS.sequelize.transaction();
            try {
                // Mapping (Customizable based on Excel header)
                const assetData = {
                    company_id,
                    owner: user_id,
                    modified_by: user_id,
                    creation: new Date(),
                    modified: new Date(),
                    enabled: true,
                    asset_tag: row['Asset Tag'] || row['Tag'],
                    name: row['Name'] || row['Asset Name'],
                    serial: row['Serial'] || row['Serial Number'],
                    model_id: await getOrCreateId('AssetModel', row['Model'], company_id, transaction),
                    status_id: await getOrCreateId('AssetStatusLabel', row['Status'], company_id, transaction),
                    category_id: await getOrCreateId('AssetCategory', row['Category'], company_id, transaction),
                    location_id: await getOrCreateId('AssetLocation', row['Location'], company_id, transaction),
                    purchase_date: row['Purchase Date'],
                    purchase_cost: row['Purchase Cost'],
                    order_number: row['Order Number'],
                    notes: row['Notes']
                };

                if (!assetData.asset_tag) throw new Error('Asset Tag is missing');

                // Check duplicate tag
                const existing = await dbSAPPS.Asset.findOne({ where: { asset_tag: assetData.asset_tag, company_id }, transaction });
                if (existing) {
                    await existing.update(assetData, { transaction });
                } else {
                    await dbSAPPS.Asset.create(assetData, { transaction });
                }

                await transaction.commit();
                results.success++;
            } catch (err) {
                await transaction.rollback();
                results.failed++;
                results.errors.push({ row: row['Asset Tag'] || 'unknown', error: err.message });
            }
        }

        return { success: true, results };
    } catch (err) {
        return { success: false, message: err.message };
    }
}

/**
 * Helper to fetch ID by name or create if missing
 */
async function getOrCreateId(modelName, name, company_id, transaction) {
    if (!name) return null;
    const model = dbSAPPS[modelName];
    let row = await model.findOne({ 
        where: { 
            [Op.or]: [
                { name: name },
                { display_name: name }
            ],
            company_id 
        }, 
        transaction 
    });

    if (!row) {
        row = await model.create({
            name,
            display_name: name,
            company_id,
            enabled: true,
            creation: new Date(),
            modified: new Date(),
            owner: 1, // System default
            modified_by: 1
        }, { transaction });
    }
    return row.id;
}
