
const { INTEGER, STRING, TEXT, FLOAT, TINYINT, CHAR, DECIMAL, Sequelize, BIGINT } = require("sequelize");
const { getOtherInstallationDatabases, getDatabaseByName, CURRENT_INSTALLATION, FACILITY_ID, qbCustomerName } = require("../config/SplitManagementDb");
const sequelize = require("../config/dbconnection");
const moment = require("moment");
const crypto = require('crypto');
const Settings = require('./settings'); // NEW: Import Settings model
const { empty } = require("../Helpers/Common");
const axios = require('axios');
const { AVALARA_ACCOUNT_ID, AVALARA_LICENSE_KEY, AVALARA_BASE_URL, getNewAccessToken, PRODUCTION, realmId, departmentId, getAccountIdByName, getAccounts, findItemIdByName } = require("../quickbook/quickbook.js");
const { RequestsModel, RequestAttachmentsModel, RequestLocationsModel } = require("./request.js");
const Request = require("./request.js");
const { RoutesModel } = require("./routes.js");
const { CustomerOrdersModel } = require("./customerOrder.js");
const { CustomerOrderProductsModel } = require("./customerOrderProduct.js");
const { RequestUpdatesModel } = require("./RequestActivity.js");
const { findItemByNamewithLocationInventoryCode, editEstimateInQuickBooks, fetchTaxAmountFromEstimateById } = require("../controllers/qbController.js");

console.log("realmIdrealmId",realmId)
//////////////////////Split Estimate Management//////////////////////
// Define models for other installations
const createSplitEstimateModel = (sequelize) => {
    return sequelize.define('split_estimates', {
        id: { type: BIGINT, allowNull: false, primaryKey: true, autoIncrement: true },
        name: { type: TEXT, allowNull: true, defaultValue: null },
        description: { type: TEXT, allowNull: true, defaultValue: null },
        origin_facility_id: { type: INTEGER, allowNull: true, defaultValue: null },
        to_facility_id: { type: INTEGER, allowNull: true, defaultValue: null },
        status: { type: INTEGER, allowNull: false, defaultValue: 1 },
        created_at: { type: INTEGER(20), allowNull: true, defaultValue: null },
        timestamp: { type: INTEGER(20), allowNull: true, defaultValue: null },
        added_by: { type: INTEGER, allowNull: true, defaultValue: null },
        total_cost: { type: FLOAT(12,2), allowNull: true, defaultValue: 0 },
        reason: { type: TEXT, allowNull: true ,defaultValue:null},
        isFromDesignOrder: { type: INTEGER, allowNull: true, defaultValue: null },
    }, { timestamps: false });
};

const createSplitEstimateAttachmentsModel = (sequelize) => {
    return sequelize.define('split_estimates_attachments', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        url: { type: TEXT, allowNull: true, defaultValue: null },
        file_type: { type: TEXT, allowNull: true, defaultValue: null },
        split_estimate_id: { type: BIGINT, allowNull: false },
        status: { type: INTEGER, allowNull: false, defaultValue: 1 },
        timestamp: { type: INTEGER(20), allowNull: true, defaultValue: null },
    }, { timestamps: false });
};

const createSplitEstimateRecipesModel = (sequelize) => {
    return sequelize.define('split_estimate_recipes', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        split_estimate_id: { type: BIGINT, allowNull: false },
        recipe_name: { type: TEXT, allowNull: true, defaultValue: null },
        expected_cost: { type: FLOAT(12,2), allowNull: false, defaultValue: 0 },
        total_cost: { type: FLOAT(12,2), allowNull: false ,defaultValue:0},
        split_percentage: { type: FLOAT(12,2), allowNull: false ,defaultValue:0},
        recipe_id: { type: INTEGER, allowNull: true, defaultValue: null },
        product_name: { type: TEXT, allowNull: true, defaultValue: null },
        product_id: { type: INTEGER, allowNull: true, defaultValue: null },
        product_added: { type: TINYINT, allowNull: false, defaultValue: 0 },
        expected_install_labor_cost: { type: FLOAT(12,2), allowNull: true, defaultValue: 0 },
    }, { timestamps: false });
};

const createSplitEstimateServicesModel = (sequelize) => {
    return sequelize.define('split_estimate_services', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        name: { type: TEXT, allowNull: true, defaultValue: null },
        labour_cost_service_id:{ type: INTEGER, allowNull: false },
        quantity:{ type: FLOAT(14,2), allowNull: false },
        total:{ type: FLOAT(12,2), allowNull: false },
        unit_cost:{ type: FLOAT(14,2), allowNull: false },
        taken:{ type: INTEGER, allowNull: false,defaultValue:0 },
        job_cost:{ type: INTEGER, allowNull: false,defaultValue:0 },
        jobcost_quantity:{ type: FLOAT(14,2), allowNull: false,defaultValue:0.00 },
        jobcost_total_cost:{ type: FLOAT(14,2), allowNull: false,defaultValue:0.00 },
        split_estimate_id:{ type: BIGINT, allowNull: false },
        complete:{ type: INTEGER, allowNull: false, defaultValue: 0 },
        status:{ type: INTEGER, allowNull: false, defaultValue: 1 },
    }, { timestamps: false, tableName:'split_estimate_services' });
};

// Helper function to get target installation database based on facility_id
const getTargetInstallation = (facilityId) => {
    const otherDatabases = getOtherInstallationDatabases();
    return otherDatabases.find(db => db.id === facilityId);
};


const getAllSplitManagementSettings = async () => {
    try {
      let result = await sequelize.query(`SELECT * FROM split_management_settings where status = 1`);
      return result[0];
    } catch (error) {
      console.error('Error fetching getAllSplitManagementSettings:', error);
      throw error; 
    }
  };

  const updateSplitManagementSettings = async (id, splitPercentage, incomingPercentage) => {
    try {
      let result = await sequelize.query(`UPDATE split_management_settings SET split_percentage = ${splitPercentage}, incoming_percentage = ${incomingPercentage} WHERE id = ${id}`);
     if(result){
      return true;
     }else{
      return false;
     }
    } catch (error) {
      console.error('Error updating split management settings:', error);
      return false;
    }
  };

// Add split estimate to other installation
const addSplitEstimateToOtherInstallation = async (data, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Adding split estimate to ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimate = createSplitEstimateModel(targetDb.connection);
        
        // Add split estimate to target installation
        const splitEstimate = await SplitEstimate.create(data);
        return splitEstimate.id;
        
    } catch (error) {
        console.log(`Error adding split estimate to other installation:`, error);
        return null;
    }
};

// Add split estimate attachments to other installation
const addSplitEstimateAttachmentsToOtherInstallation = async (attachmentsData, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Adding split estimate attachments to ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimateAttachments = createSplitEstimateAttachmentsModel(targetDb.connection);
        
        // Add attachments to target installation
        const attachments = await SplitEstimateAttachments.bulkCreate(attachmentsData);
        return attachments;
        
    } catch (error) {
        console.log(`Error adding split estimate attachments to other installation:`, error);
        return null;
    }
};

// Add split estimate recipes to other installation
const addSplitEstimateRecipesToOtherInstallation = async (recipesData, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Adding split estimate recipes to ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimateRecipes = createSplitEstimateRecipesModel(targetDb.connection);
        
        // Add recipes to target installation
        const recipes = await SplitEstimateRecipes.bulkCreate(recipesData);
        return recipes;
        
    } catch (error) {
        console.log(`Error adding split estimate recipes to other installation:`, error);
        return null;
    }
};

// Add split estimate services to other installation
const addSplitEstimateServicesToOtherInstallation = async (servicesData, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Adding split estimate services to ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimateServices = createSplitEstimateServicesModel(targetDb.connection);
        
        // Add services to target installation
        const services = await SplitEstimateServices.bulkCreate(servicesData);
        return services;
        
    } catch (error) {
        console.log(`Error adding split estimate services to other installation:`, error);
        return null;
    }
};

// Main function to sync split estimate across installations
const syncSplitEstimateAcrossInstallations = async (splitEstimateData, attachmentsData, targetFacilityId,recipesData) => {
    try {
        console.log(`Syncing split estimate to facility_id: ${targetFacilityId}`);
        
        // Add split estimate to target installation
        const splitEstimateId = await addSplitEstimateToOtherInstallation(splitEstimateData, targetFacilityId);
        
        if (!splitEstimateId) {
            console.log(`Failed to add split estimate to target installation`);
            return { success: false, message: "Failed to add split estimate to target installation" };
        }

        // Update attachments data with the new split_estimate_id
        const updatedAttachmentsData = attachmentsData.map(attachment => ({
            ...attachment,
            split_estimate_id: splitEstimateId
        }));

        // Add attachments to target installation
        const attachmentsResult = await addSplitEstimateAttachmentsToOtherInstallation(updatedAttachmentsData, targetFacilityId);
        
        if (!attachmentsResult) {
            console.log(`Failed to add attachments to target installation`);
            return { success: false, message: "Failed to add attachments to target installation" };
        }

        if (recipesData && recipesData.length > 0) {
            // Update recipes data with the split_estimate_id
            const updatedRecipesData = recipesData.map(recipe => ({
                ...recipe,
                split_estimate_id: splitEstimateId
            }));

            const recipesResult = await addSplitEstimateRecipesToOtherInstallation(updatedRecipesData, targetFacilityId);
            
            if (!recipesResult) {
                console.log(`Failed to add updated recipes to target installation`);
                return { success: false, message: "Failed to add updated recipes to target installation" };
            }
            recipesCount = recipesResult.length;
        }

        return { 
            success: true, 
            message: "Split estimate synced successfully across installations",
            splitEstimateId: splitEstimateId,
            attachmentsCount: attachmentsResult.length,
            recipesCount: recipesCount
        };
        
    } catch (error) {
        console.log(`Error syncing split estimate across installations:`, error);
        return { success: false, message: "Error syncing split estimate across installations", error: error };
    }
};

// Update split estimate in other installation
const updateSplitEstimateInOtherInstallation = async (data, splitEstimateId, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Updating split estimate ${splitEstimateId} in ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimate = createSplitEstimateModel(targetDb.connection);
        
        // Update split estimate in target installation
        const [affectedRows] = await SplitEstimate.update(data, { where: { id: splitEstimateId } });
        
        // If no rows were affected, add as new data
        if (affectedRows === 0) {
            console.log(`No existing split estimate found with id ${splitEstimateId}, adding as new data`);
            
            // Add the data as new record with the same ID
            const newData = {
                ...data,
                id: splitEstimateId
            };
            
            const newResult = await SplitEstimate.create(newData);
            console.log(`Successfully added new split estimate with id ${splitEstimateId}`);
            return newResult;
        }
        
        console.log(`Successfully updated ${affectedRows} row(s) for split estimate ${splitEstimateId}`);
        return { affectedRows };
        
    } catch (error) {
        console.log(`Error updating split estimate in other installation:`, error);
        return null;
    }
};

const updateSplitEstimateProductAddedStatusInOtherInstallation = async (data, splitEstimateId, facilityId,recipeId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Updating split estimate ${splitEstimateId} in ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimate = createSplitEstimateRecipesModel(targetDb.connection);
        
        // Update split estimate in target installation
        const result = await SplitEstimate.update(data, { where: { split_estimate_id: splitEstimateId ,recipe_id:recipeId} });
        return result;
        
    } catch (error) {
        console.log(`Error updating split estimate  recipe status in other installation:`, error);
        return null;
    }
};

// Delete all attachments for split estimate in other installation
const deleteAllSplitEstimateAttachmentsInOtherInstallation = async (splitEstimateId, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Deleting attachments for split estimate ${splitEstimateId} in ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimateAttachments = createSplitEstimateAttachmentsModel(targetDb.connection);
        
        // Delete attachments in target installation
        const result = await SplitEstimateAttachments.destroy({ where: { split_estimate_id: splitEstimateId } });
        return result;
        
    } catch (error) {
        console.log(`Error deleting attachments in other installation:`, error);
        return null;
    }
};

// Delete all recipes for split estimate in other installation
const deleteAllSplitEstimateRecipesInOtherInstallation = async (splitEstimateId, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Deleting recipes for split estimate ${splitEstimateId} in ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimateRecipes = createSplitEstimateRecipesModel(targetDb.connection);
        
        // Delete recipes in target installation
        const result = await SplitEstimateRecipes.destroy({ where: { split_estimate_id: splitEstimateId } });
        return result;
        
    } catch (error) {
        console.log(`Error deleting recipes in other installation:`, error);
        return null;
    }
};

// Delete all split estimate services in other installation
const deleteAllSplitEstimateServicesInOtherInstallation = async (splitEstimateId, facilityId) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Deleting all split estimate services for split estimate ${splitEstimateId} in ${targetDb.name} installation`);
        
        // Create models for the target database
        const SplitEstimateServices = createSplitEstimateServicesModel(targetDb.connection);
        
        // Delete all services for the split estimate
        const result = await SplitEstimateServices.destroy({ where: { split_estimate_id: splitEstimateId } });
        return result;
        
    } catch (error) {
        console.log(`Error deleting split estimate services in other installation:`, error);
        return null;
    }
};

// Sync split estimate status update across installations
const syncSplitEstimateStatusUpdateAcrossInstallations = async (statusData, splitEstimateId, targetFacilityId) => {
    try {
        console.log(`Syncing split estimate status update ${splitEstimateId} to facility_id: ${targetFacilityId}`);
        
        // Update split estimate status in target installation
        const updateResult = await updateSplitEstimateInOtherInstallation(statusData, splitEstimateId, targetFacilityId);
        
        if (!updateResult) {
            console.log(`Failed to update split estimate status in target installation`);
            return { success: false, message: "Failed to update split estimate status in target installation" };
        }

        return { 
            success: true, 
            message: "Split estimate status update synced successfully across installations"
        };
        
    } catch (error) {
        console.log(`Error syncing split estimate status update across installations:`, error);
        return { success: false, message: "Error syncing split estimate status update across installations", error: error };
    }
};

const syncSplitEstimateProductAddedStatusUpdateAcrossInstallations = async (statusData, splitEstimateId, targetFacilityId,recipeId) => {
    try {
        console.log(`Syncing split estimate product status update ${splitEstimateId} to facility_id: ${targetFacilityId}`);
        
        // Update split estimate status in target installation
        const updateResult = await updateSplitEstimateProductAddedStatusInOtherInstallation(statusData, splitEstimateId, targetFacilityId,recipeId);
        
        if (!updateResult) {
            console.log(`Failed to update split estimate product status in target installation`);
            return { success: false, message: "Failed to update split estimate product status in target installation" };
        }

        return { 
            success: true, 
            message: "Split estimate product status update synced successfully across installations"
        };
        
    } catch (error) {
        console.log(`Error syncing split estimate status update across installations:`, error);
        return { success: false, message: "Error syncing split estimate status update across installations", error: error };
    }
};

// Sync split estimate update across installations
const syncSplitEstimateUpdateAcrossInstallations = async (splitEstimateData, attachmentsData, recipesData, servicesData, splitEstimateId, targetFacilityId) => {
    try {
        console.log(`Syncing split estimate update ${splitEstimateId} to facility_id: ${targetFacilityId}`);
        
        // Update split estimate in target installation
        const updateResult = await updateSplitEstimateInOtherInstallation(splitEstimateData, splitEstimateId, targetFacilityId);
        
        if (!updateResult) {
            console.log(`Failed to update split estimate in target installation`);
            return { success: false, message: "Failed to update split estimate in target installation" };
        }

        // Delete existing attachments in target installation
        const deleteAttachmentsResult = await deleteAllSplitEstimateAttachmentsInOtherInstallation(splitEstimateId, targetFacilityId);
        
        if (deleteAttachmentsResult === null) {
            console.log(`Failed to delete existing attachments in target installation`);
            return { success: false, message: "Failed to delete existing attachments in target installation" };
        }

        // Delete existing recipes in target installation
        const deleteRecipesResult = await deleteAllSplitEstimateRecipesInOtherInstallation(splitEstimateId, targetFacilityId);
        
        if (deleteRecipesResult === null) {
            console.log(`Failed to delete existing recipes in target installation`);
            return { success: false, message: "Failed to delete existing recipes in target installation" };
        }

        const deleteServicesResult = await deleteAllSplitEstimateServicesInOtherInstallation(splitEstimateId, targetFacilityId);

        let attachmentsCount = 0;
        let recipesCount = 0;
        let servicesCount = 0;

        // Add new attachments to target installation if any
        if (attachmentsData && attachmentsData.length > 0) {
            // Update attachments data with the split_estimate_id
            const updatedAttachmentsData = attachmentsData.map(attachment => ({
                ...attachment,
                split_estimate_id: splitEstimateId
            }));

            const attachmentsResult = await addSplitEstimateAttachmentsToOtherInstallation(updatedAttachmentsData, targetFacilityId);
            
            if (!attachmentsResult) {
                console.log(`Failed to add updated attachments to target installation`);
                return { success: false, message: "Failed to add updated attachments to target installation" };
            }
            attachmentsCount = attachmentsResult.length;
        }

        // Add new recipes to target installation if any
        if (recipesData && recipesData.length > 0) {
            // Update recipes data with the split_estimate_id
            const updatedRecipesData = recipesData.map(recipe => ({
                ...recipe,
                split_estimate_id: splitEstimateId
            }));

            const recipesResult = await addSplitEstimateRecipesToOtherInstallation(updatedRecipesData, targetFacilityId);
            
            if (!recipesResult) {
                console.log(`Failed to add updated recipes to target installation`);
                return { success: false, message: "Failed to add updated recipes to target installation" };
            }
            recipesCount = recipesResult.length;
        }

        // Add new services to target installation if any (only for design order split estimates)
        if (servicesData && servicesData.length > 0) {
            // Process services data similar to attachments and recipes
            const processedServicesData = [];
            for (let i = 0; i < servicesData.length; i++) {
                const service = servicesData[i];
                const serviceData = {
                    labour_cost_service_id: service.labour_cost_service_id,
                    name: service.name,
                    quantity: service.quantity || 0,
                    total: service.total || 0,
                    unit_cost: service.unit_cost || 0,
                    taken: service.taken || 0,
                    job_cost: service.job_cost || 0,
                    jobcost_quantity: service.jobcost_quantity || 0.00,
                    jobcost_total_cost: service.jobcost_total_cost || 0.00,
                    split_estimate_id: splitEstimateId,
                    complete: service.complete || 0,
                    status: service.status || 1
                };
                processedServicesData.push(serviceData);
            }

            const servicesResult = await addSplitEstimateServicesToOtherInstallation(processedServicesData, targetFacilityId);
            
            if (!servicesResult) {
                console.log(`Failed to add updated services to target installation`);
                return { success: false, message: "Failed to add updated services to target installation" };
            }
            servicesCount = servicesResult.length;
        }

        return { 
            success: true, 
            message: "Split estimate update synced successfully across installations",
            attachmentsCount: attachmentsCount,
            recipesCount: recipesCount,
            servicesCount: servicesCount
        };
        
    } catch (error) {
        console.log(`Error syncing split estimate update across installations:`, error);
        return { success: false, message: "Error syncing split estimate update across installations", error: error };
    }
};

// Get split estimates from other installations
const getSplitEstimatesFromOtherInstallation = async (facilityId, search, page, rowsPerPage, sort, whereCondition) => {
    try {
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${facilityId}`);
            return null;
        }

        console.log(`Fetching split estimates from ${targetDb.name} installation`);
        
        let query = 'SELECT * FROM split_estimates sp';
        let whereClause = '';
        let orderClause = '';
        let limitClause = '';
        
        // Build WHERE clause
        const conditions = [];
        
        // Add whereCondition if provided
        if (whereCondition) {
            conditions.push(whereCondition);
        }
        
        // Add search by name if provided
        if (search) {
            conditions.push(`sp.name LIKE '%${search}%'`);
        }
        
        if (conditions.length > 0) {
            whereClause = ' WHERE ' + conditions.join(' AND ');
        }
        
        // Build ORDER BY clause
        if (sort) {
            switch (sort) {
                case 'new':
                    orderClause = ' ORDER BY sp.id DESC';
                    break;
                case 'old':
                    orderClause = ' ORDER BY sp.id ASC';
                    break;
                case 'last_updated':
                    orderClause = ' ORDER BY sp.timestamp DESC';
                    break;
                default:
                    orderClause = ' ORDER BY sp.id DESC';
            }
        } else {
            orderClause = ' ORDER BY sp.id DESC';
        }
        
        // Build LIMIT clause
        if (rowsPerPage) {
            const offset = page ? (page - 1) * rowsPerPage : 0;
            limitClause = ` LIMIT ${rowsPerPage} OFFSET ${offset}`;
        }
        
        // Combine all parts
        const finalQuery = query + whereClause + orderClause + limitClause;
        
        const splitEstimates = await targetDb.connection.query(finalQuery, {
            type: targetDb.connection.QueryTypes.SELECT
        });
        
        // Get total count for pagination
        const countQuery = 'SELECT COUNT(*) as total FROM split_estimates sp' + whereClause;
        const totalResult = await targetDb.connection.query(countQuery, {
            type: targetDb.connection.QueryTypes.SELECT
        });
        
        return {
            data: splitEstimates,
            totalCount: totalResult[0].total
        };
        
    } catch (error) {
        console.log(`Error fetching split estimates from other installation:`, error);
        return null;
    }
};


/////////////////////////The End of Split Estimate Management//////////////////////////




//////////////////////////Sales Order Management//////////////////////////

const createTargetDatabaseModels = (connection) => {
    const { INTEGER, STRING, TEXT, FLOAT, TINYINT, CHAR, DECIMAL, Sequelize } = require("sequelize");
    
    // Request model
    const Requests = connection.define('request', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        name: { type: STRING, allowNull: false },
        catalog_type: { type: STRING, allowNull: true, defaultValue: null  },
        description: { type: TEXT, allowNull: false },
        thumbnail: { type: STRING(2000), allowNull: true, defaultValue: null },
        services: { type: INTEGER, allowNull: true },
        workflows: { type: INTEGER, allowNull: true },
        reason: { type: TEXT, allowNull: true, defaultValue: null },
        edit_reason: { type: TEXT, allowNull: true, defaultValue: null },
        project_type: { type: INTEGER, allowNull: true, defaultValue: null },
        project_category: { type: TINYINT, allowNull: true, defaultValue: null },
        no_charge_type: { type: TINYINT, allowNull: true, defaultValue: null },
        community: { type: INTEGER, allowNull: true, defaultValue: null },
        map_service: { type: INTEGER, allowNull: true, defaultValue: null },
        submission_deadline: { type: STRING, defaultValue: null, allowNull: true },
        participation_ends: { type: STRING, defaultValue: null, allowNull: true },
        installation_start_date: { type: INTEGER, allowNull: true, defaultValue: null },
        installation_end_date: { type: INTEGER, allowNull: true, defaultValue: null },
        start_date: { type: STRING, defaultValue: null, allowNull: true },
        subject: { type: TEXT, defaultValue: null },
        closed_for_proposal: { type: INTEGER, defaultValue: 0 },
        email_content: { type: TEXT, allowNull: true },
        type: { type: TINYINT, defaultValue: null, },
        welcome_message: { type: TINYINT, defaultValue: null, },
        markup_type: { type: INTEGER, allowNull: false, defaultValue: 1 },
        status: { type: TINYINT, defaultValue: 1 },
        added_by: { type: INTEGER, defaultValue: null },
        edited_by: { type: INTEGER, defaultValue: null },
        onboard_complete: { type: INTEGER, defaultValue: 0 },
        timestamp: { type: INTEGER, allowNull: false },
        noServiceNoTask: { type: TINYINT, allowNull: false, defaultValue: 0 },
        labour_cost_services: { type: STRING(200), allowNull: true, defaultValue: null },
        labour_cost: { type: INTEGER, allowNull: false, defaultValue: 0 },
        is_location: { type: INTEGER, allowNull: false, defaultValue: 0 },
        is_service_location: { type: INTEGER, allowNull: false, defaultValue: 0 },
        is_rfp_service_location: { type: INTEGER, allowNull: false, defaultValue: 0 },
        is_rfp_product_location: { type: INTEGER, allowNull: false, defaultValue: 0 },
        notes: { type: TEXT, allowNull: false },
        quotation: { type: TINYINT, defaultValue: 0 },
        is_request_estimate: { type: TINYINT, defaultValue: 0 },
        estimator_ids: { type: STRING(250), allowNull: true, defaultValue: null },
        is_create_estimate: { type: TINYINT, defaultValue: 0 },
        quotation_status: { type: TINYINT, defaultValue: 2 },
        estimate_id: { type: INTEGER, allowNull: true },
        syncWithQuickbooks: { type: INTEGER, allowNull: true },
        reasonWithQb: { type: STRING(3000), allowNull: true },
        card_order: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
        doc_number: { type: STRING(200), allowNull: true , defaultValue: null},
        est_doc_number: { type: TEXT, allowNull: true , defaultValue: null},
        po_number: { type: STRING(200), allowNull: true, defaultValue: null },
        isReview: { type: INTEGER, allowNull: true, defaultValue: 0 },
        installation_acceptance: { type: INTEGER, allowNull: true, defaultValue: null },
        min_payment_status:{ type: INTEGER, allowNull: false, defaultValue: 0 },
        client_approve: { type: INTEGER, allowNull: false, defaultValue: 0  },
        isFromDesignOrder: { type: INTEGER, allowNull: false, defaultValue: 0  },
        isFromEstimate: { type: INTEGER, allowNull: false, defaultValue: 0  },
        design_order_id: { type: INTEGER, allowNull: true, defaultValue: null },
        is_single_card: { type: INTEGER, allowNull: false, defaultValue: 0  },
        send_to_client: { type: INTEGER, allowNull: false, defaultValue: 1  },
        duplicated_from: { type: INTEGER, allowNull: true, defaultValue: null },
        hold: {type:INTEGER,allowNull:false,defaultValue:0},
        hold_reason: { type: TEXT, allowNull: true, defaultValue: null },
        avalara_transaction_id: { type: TEXT, allowNull: true, defaultValue: null },
        avalara_code: { type: TEXT, allowNull: true, defaultValue: null },
        created_at: { type: INTEGER, allowNull: false, defaultValue: 0 },
        facility_type: { type: TEXT, allowNull: true, defaultValue: null },
        origin_facility_id: { type: INTEGER, allowNull: true, defaultValue: null },
        origin_facility_request_id: { type: INTEGER, allowNull: true, defaultValue: null },
        is_split_order: { type: INTEGER, allowNull: false, defaultValue: 0 },
        for_parent_facility: { type: INTEGER, allowNull: false, defaultValue: 0 },
        split_mirror_status: { type: INTEGER, allowNull: false, defaultValue: 0 },
        new_order: { type: TINYINT, allowNull: false, defaultValue: 0 },
        mirror_approve_status: { type: TEXT, allowNull: true, defaultValue: null },
        received_status: { type: INTEGER, allowNull: false, defaultValue: 0 },
    }, { timestamps: false, tableName: 'requests' });

    // Customer Order model
    const CustomerOrders = connection.define('customer_orders', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        request_id: { type: INTEGER, allowNull: false },
        total: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        grandTotal: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        tax_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        tax_category: { type: INTEGER, allowNull: true, defaultValue: null },
        budget: { type: DECIMAL(12, 2), allowNull: true, defaultValue: null },
        extra_discount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        timestamp: { type: INTEGER, allowNull: false },
        sub_total: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        tax: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        status: { type: TINYINT, allowNull: false, defaultValue: 1 },
        customer_id: { type: INTEGER, allowNull: true, defaultValue: null },
        customer_name: { type: STRING(255), allowNull: true, defaultValue: null },
        customer_phone: { type: STRING(20), allowNull: true, defaultValue: null },
        customer_address: { type: TEXT, allowNull: true, defaultValue: null },
    }, { timestamps: false, tableName: 'customer_orders' });

    // Customer Order Products model
    const CustomerOrderProducts = connection.define('customer_order_products', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        facility_id:{type:INTEGER,allowNull:true,defaultValue:null},
        transfer_status: {type:INTEGER,allowNull:false,defaultValue:0},
        product_index:{type:INTEGER,allowNull:false,defaultValue:0},
        order_id:{type:INTEGER,allowNull:false},
        unit_cost:{type:FLOAT(12,2),allowNull:true,defaultValue:null},
        product_name:{type:TEXT,allowNull:false},
        product_desc:{type:TEXT,allowNull:true, defaultValue:null},
        product_notes:{type:TEXT(2000),allowNull:true, defaultValue:null},
        install_notes:{type:TEXT(2000),allowNull:true, defaultValue:null},
        product_price:{type:DECIMAL(12,2),allowNull:false,defaultValue:0.00},
        install_cost:{type:FLOAT(12,2),allowNull:true,defaultValue:null},
        product_retail_price:{type:DECIMAL(12,2),allowNull:true,defaultValue:null},
        expected_formula_price:{type:DECIMAL(12,2),allowNull:true,defaultValue:0},
        product_markup:{type:DECIMAL(12,2),allowNull:true,defaultValue:null},
        product_qty:{type:DECIMAL(12,2),allowNull:false},
        location_quantity:{type:INTEGER,allowNull:false,defaultValue:1},
        product_id:{type:INTEGER,allowNull:true, defaultValue:null},
        workflow:{type:INTEGER,allowNull:true,defaultValue:null},
        product_sub_total:{type:DECIMAL(12,2),allowNull:false},
        product_tax:{type:DECIMAL(12,2),allowNull:false,defaultValue:0.00},
        product_total:{type:DECIMAL(14,2),allowNull:false},
        discount:{type:DECIMAL(12,2),allowNull:false},
        adjustment:{type:DECIMAL(12,2),allowNull:false, defaultValue:0.00},
        timestamp:{type:INTEGER,allowNull:false},
        status:{type:TINYINT(2),allowNull:false,defaultValue:1},
        rfp:{type:TINYINT,allowNull:false,defaultValue:0},
        material:{type:INTEGER,allowNull:false,defaultValue:0},
        markAdjType:{type:INTEGER,allowNull:true,defaultValue:1},
        map_service:{type:INTEGER,allowNull:false,defaultValue:0},
        isAdditionalProduct:{ type: INTEGER, allowNull: false,defaultValue: 0 },
        allLocationSame:{type:INTEGER,allowNull:false,defaultValue:0},
        is_single_card: { type: INTEGER, allowNull: false, defaultValue: 0  },
        convertion_unit:{ type: INTEGER, allowNull: true,defaultValue:null },
        stock_type:{ type: INTEGER, allowNull: true,defaultValue:1 },
        vendor_price:{ type: FLOAT(12,2), allowNull: true,defaultValue:0 },
        jobcost_quantity: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        jobcost_total_cost: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        taken: { type: TINYINT, allowNull: false, defaultValue: 0 },
        split_percentage: { type: FLOAT, allowNull: true,defaultValue:0 },
        incoming_split_percentage: { type: FLOAT, allowNull: true,defaultValue:0 },
        delivery_type_id: { type: INTEGER, allowNull: true,defaultValue:null },
        installer_facility_id: { type: INTEGER, allowNull: true,defaultValue:null },
        install_qty:{type:DECIMAL(12,2),allowNull:false,defaultValue:1},
        install_adjustment:{type:DECIMAL(12,2),allowNull:false,defaultValue:0.00},
        install_adj_value_type:{type:INTEGER,allowNull:false,defaultValue:1},
        installMarkAdjType:{type:INTEGER,allowNull:true,defaultValue:1},
    }, { timestamps: false, tableName: 'customer_order_products' });

    // Request Attachments model
    const RequestAttachments = connection.define('request_attachments', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        request_id: { type: INTEGER, allowNull: false },
        name: { type: STRING(500), allowNull: false },
        file_type: { type: STRING(100), allowNull: true, defaultValue: null },
        extension: { type: STRING(10), allowNull: true, defaultValue: null },
        file_size: { type: INTEGER, allowNull: true, defaultValue: null },
        added_by: { type: INTEGER, allowNull: false },
        timestamp: { type: INTEGER, allowNull: false },
        approved: { type: TINYINT, allowNull: false, defaultValue: 0 },
        approved_by: { type: INTEGER, allowNull: true, defaultValue: null },
        product_id: { type: INTEGER, allowNull: true, defaultValue: null },
        product_ids: { type: STRING, allowNull: true, defaultValue: null },
        location_ids: { type: STRING, allowNull: true, defaultValue: null },
        reason: { type: TEXT, allowNull: true, defaultValue: null },
        status: { type: TINYINT, defaultValue: 1 },
    }, { timestamps: false, tableName: 'request_attachments' });

    // Request Locations model
    const RequestLocations = connection.define('request_locations', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        order_id: { type: INTEGER, allowNull: true, defaultValue: null },
        product_id: { type: INTEGER, allowNull: true, defaultValue: null },
        address: { type: STRING(500), allowNull: false },
        city: { type: STRING(100), allowNull: false },
        state: { type: STRING(100), allowNull: false },
        zip: { type: STRING(20), allowNull: false },
        route_location_id: { type: INTEGER, allowNull: true, defaultValue: null },
        timestamp: { type: INTEGER, allowNull: false },
        added_by: { type: INTEGER, allowNull: false },
        isLinkedAll: { type: INTEGER, allowNull: false, defaultValue: 0 },
        lng: { type: STRING, allowNull: true, defaultValue: null },
        lat: { type: STRING, allowNull: true, defaultValue: null },
        updated_by: { type: INTEGER, allowNull: true },
        status: { type: TINYINT, defaultValue: 1 },
        starting_point: { type: INTEGER, allowNull: true, defaultValue: null },
        ending_point: { type: INTEGER, allowNull: true, defaultValue: null },
        color: { type: TEXT, allowNull: true, defaultValue: null },
    }, { timestamps: false, tableName: 'request_locations' });

    // Routes model
    const Routes = connection.define('routes', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        name: { type: STRING(255), allowNull: false },
        assigned_driver: { type: INTEGER, allowNull: true, defaultValue: null },
        timestamp: { type: INTEGER, allowNull: false },
        added_by: { type: INTEGER, allowNull: false },
        status: { type: TINYINT, allowNull: false, defaultValue: 1 },
        assigned_sales_order: { type: INTEGER, allowNull: true, defaultValue: null },
        assigned: { type: TINYINT, allowNull: false, defaultValue: 0 },
        unique_id: { type: STRING(50), allowNull: true, defaultValue: null },
    }, { timestamps: false, tableName: 'routes' });

    // Products model
    const Products = connection.define('products', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        name: { type: STRING, allowNull: false },
        barcode: { type: STRING, allowNull: true, defaultValue: null },
        description: { type: TEXT, allowNull: true },
        vendor_id: { type: TEXT, allowNull: true, defaultValue: null },
        vpt_number: { type: STRING, allowNull: true, defaultValue: null },
        thumbnail: { type: STRING, allowNull: false, defaultValue: 'default.jpg' },
        price: { type: FLOAT(12, 2), allowNull: true, defaultValue: 0.00 },
        retail_price: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        actual_retail_price: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        install_cost: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        formula_cost: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        pf_retail_markup: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        special_price: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        unit: { type: INTEGER, allowNull: true, defaultValue: null },
        material_types: { type: INTEGER, allowNull: true, defaultValue: null },
        unit_qty: { type: INTEGER, allowNull: true, defaultValue: null },
        start_date: { type: INTEGER, allowNull: true, defaultValue: null },
        end_date: { type: INTEGER, allowNull: true, defaultValue: null },
        added_by: { type: INTEGER, allowNull: false },
        category_id: { type: INTEGER, allowNull: true, defaultValue: null },
        sub_category_id: { type: INTEGER, allowNull: true, defaultValue: null },
        qb_category: { type: INTEGER, allowNull: true, defaultValue: null },
        qb_sub_category: { type: INTEGER, allowNull: true, defaultValue: null },
        qb_income_account: { type: INTEGER, allowNull: true, defaultValue: null },
        qb_expense_account: { type: INTEGER, allowNull: true, defaultValue: null },
        tags: { type: TEXT, allowNull: true, defaultValue: null },
        workflow: { type: INTEGER, allowNull: true },
        min_value: { type: INTEGER, allowNull: false, defaultValue: 0 },
        max_value: { type: INTEGER, allowNull: false, defaultValue: 0 },
        timestamp: { type: INTEGER, allowNull: false },
        meterial: { type: TINYINT, allowNull: false, defaultValue: 0 },
        status: { type: TINYINT, allowNull: false, defaultValue: 1 },
        twilio_field_value_sid: { type: STRING, allowNull: true, defaultValue: null },
        tax_category: { type: INTEGER, allowNull: true, defaultValue: null },
        recipe_id: { type: INTEGER, allowNull: true, defaultValue: null },
        inhouse: { type: TINYINT, allowNull: false, defaultValue: 1 },
        quickbookId: { type: INTEGER, allowNull: true },
        syncWithQuickbooks: { type: INTEGER, allowNull: false, defaultValue: 0 },
        account_type: { type: STRING, allowNull: false, defaultValue: '1' },
        isAdditionalProduct: { type: INTEGER, allowNull: false, defaultValue: 0 },
        isTrafficCatalog: { type: INTEGER, allowNull: false, defaultValue: 0 },
        taxable: { type: TINYINT, allowNull: false, defaultValue: 1 },
        markup: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        origin_catalog: { type: STRING(50), allowNull: true, defaultValue: null },
        sub_contract_price: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        in_stock_no: { type: INTEGER, allowNull: true, defaultValue: null },
        taxCodeId: { type: INTEGER, allowNull: true, defaultValue: null },
        taxCode: { type: TEXT, allowNull: true, defaultValue: null },
        facility_id: { type: INTEGER, allowNull: true, defaultValue:null },
        install_labour_quantity:{type:INTEGER,allowNull:true, defaultValue:null},
        lead_time: { type: INTEGER, allowNull: true, defaultValue: null },
        bin_location: { type: STRING, allowNull: true, defaultValue: null },
        last_ordered_date: { type: INTEGER, allowNull: true, defaultValue: null },
        transfered_product_id: { type: INTEGER, allowNull: true, defaultValue: null },
    }, { timestamps: false, tableName: 'products' });

    const Projects = connection.define('projects', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        project_uid: { type: STRING, allowNull: false },
        name: { type: STRING, allowNull: false },
        short_code: { type: STRING, allowNull: false },
        request_id: { type: INTEGER, allowNull: false },
        tags: { type: TEXT, allowNull: false },
        start_date: { type: INTEGER, allowNull: true, defaultValue: null },
        due_date: { type: INTEGER, allowNull: true, defaultValue: null },
        installation_date: { type: INTEGER, allowNull: true, defaultValue: null },
        installation_start_date: { type: INTEGER, allowNull: true, defaultValue: null },
        installation_end_date: { type: INTEGER, allowNull: true, defaultValue: null },
        project_category: { type: INTEGER, allowNull: false },
        project_type: { type: INTEGER, allowNull: false },
        website_url: { type: TEXT, allowNull: false },
        state_id: { type: INTEGER, allowNull: false },
        city: { type: STRING, allowNull: false },
        cost: { type: DECIMAL(14,2), allowNull: true },
        description: { type: TEXT, allowNull: false },
        approved_image: { type: TEXT, allowNull: false },
        community: { type: INTEGER, allowNull: true, defaultValue: null },
        cover_image: { type: TEXT, allowNull: true, defaultValue: null },
        added_by: { type: INTEGER, allowNull: false },
        reason: { type: TEXT, allowNull: false },
        timestamp: { type: INTEGER, allowNull: false },
        noServiceNoTask: { type: TINYINT, allowNull: false, defaultValue: 0 },
        status: { type: TINYINT, allowNull: false, defaultValue: 1 },
        installation_status: { type: INTEGER, allowNull: false },
        completion: { type: TINYINT, allowNull: true, defaultValue: 0 },
        jobcost: { type: TINYINT, allowNull: true, defaultValue: 0 },
        notifyJobcost:{ type: TEXT, allowNull: true, defaultValue:null},
        hold: { type: TINYINT, allowNull: false, defaultValue: 0 },
        card_order: { type: TINYINT, allowNull: false, defaultValue: 1 },
        is_estimate_job: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    }, { timestamps: false, tableName: 'projects' })

    const ProjectTasks = connection.define('project_tasks', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        project_id: { type: INTEGER, allowNull: false, },
        project_task_uid: { type: STRING, allowNull: false, },
        name: { type: STRING, allowNull: false },
        task_uid: { type: INTEGER, allowNull: false, },
        description: { type: STRING, allowNull: false },
        is_manually_added:{ type: TINYINT, allowNull: false, defaultValue: 0 },
        start_date: { type: INTEGER, allowNull: true, defaultValue: null },
        due_date: { type: INTEGER, allowNull: true, defaultValue: null },
        cover_image: { type: STRING, allowNull: true,defaultValue:null },
        product_id: { type: INTEGER, allowNull: true,defaultValue:null },
        service_id: { type: INTEGER, allowNull: true,defaultValue:null },
        reporter_id: { type: INTEGER, allowNull: false,defaultValue:1 },
        priority: { type: TINYINT, allowNull: false, },
        status: { type: TINYINT, allowNull: false },
        userid: { type: TINYINT, allowNull: false, },
        timestamp: { type: INTEGER, allowNull: false },
        complete: { type: TINYINT, allowNull: false, },
        complete_sync: { type: TINYINT, allowNull: false, defaultValue: 1 },
        from_product: { type: TINYINT, allowNull: false, defaultValue: 1 },
        order_id: { type: INTEGER, allowNull: true, defaultValue:null },
        customer_order_product_id:{ type: INTEGER, allowNull: true, defaultValue:null },
        request_location_id:{ type: INTEGER, allowNull: true, defaultValue:null },
        facility_id: { type: INTEGER, allowNull: true, defaultValue: null }
    }, { timestamps: false, tableName: 'project_tasks' })

    // Request Updates model
    const RequestUpdates = connection.define('request_updates', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        request_id: { type: INTEGER, allowNull: false },
        partner_id: { type: INTEGER, allowNull: true },
        partner_name: { type: STRING, allowNull: true },
        activity: { type: TEXT, allowNull: false, defaultValue: false },
        added_by: { type: INTEGER, allowNull: false },
        timestamp: { type: INTEGER, allowNull: false },
        status: { type: INTEGER, allowNull: false, defaultValue: 1 },
    }, { timestamps: false, tableName: 'request_updates' });

    const QuickbooksRefreshToken = connection.define('quickbooks_refresh_token', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        name: { type: STRING(200), allowNull: false },
        company: { type: STRING(300), allowNull: false },
        companyId: { type: STRING(300), allowNull: false },
        refreshToken: { type: STRING(300), allowNull: false },
        accessToken: { type: TEXT, allowNull: true },
    }, { timestamps: false, tableName: 'quickbooks_refresh_token' });

      const RequestServices = connection.define('request_services', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        product_index: { type: INTEGER, allowNull: false, defaultValue: 0 },
        request_id: { type: INTEGER, allowNull: false },
        labour_cost_service_id: { type: INTEGER, allowNull: true, defaultValue: null },
        recipe_id: { type: INTEGER, allowNull: true, defaultValue: null },
        product_id: { type: INTEGER, allowNull: true, defaultValue: null },
        qty: { type: FLOAT(12, 2), allowNull: false },
        markup: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
        unit_cost: { type: FLOAT(12, 2), allowNull: false },
        total_cost: { type: FLOAT(12, 2), allowNull: false },
        job_cost: { type: INTEGER, allowNull: true, defaultValue: 0 },
        taken: { type: TINYINT, allowNull: false, defaultValue: 0 },
        rfp: { type: TINYINT, allowNull: false, defaultValue: 0 },
        map_service: { type: TINYINT, allowNull: false, defaultValue: 0 },
        jobcost_unit_cost: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        jobcost_quantity: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        jobcost_total_cost: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0 },
        is_dos: { type:INTEGER, allowNull: false, defaultValue: 0 },
        isAdditionalProduct: { type:INTEGER, allowNull: false, defaultValue: 0 },
        makeAsTask: { type:TINYINT(4), allowNull: false, defaultValue: 0 },
        makeInstallCard: { type:TINYINT(4), allowNull: false, defaultValue: 0 },
        from_job: { type:INTEGER, allowNull: false, defaultValue: 0 },
        fixed_service_cost: { type:INTEGER, allowNull: false, defaultValue: 0 },
        is_addition_service: { type:INTEGER, allowNull: true, defaultValue: 0 },
        service_notes: { type: TEXT(2000), allowNull: true, defaultValue: null },
        status: { type: TINYINT, allowNull: false, defaultValue: 1 },
        service_name : { type: STRING, allowNull: true, defaultValue: null },
        facility_id:{type:INTEGER,allowNull:true,defaultValue:null},
        split_percentage: { type: FLOAT, allowNull: true,defaultValue:0 },
        incoming_split_percentage: { type: FLOAT, allowNull: true,defaultValue:0 },
        delivery_type_id: { type: INTEGER, allowNull: true,defaultValue:null },
        installer_facility_id: { type: INTEGER, allowNull: true,defaultValue:null },  
        service_desc:{type:TEXT,allowNull:true, defaultValue:null}, 
        is_service_row: { type: INTEGER, allowNull: true, defaultValue: null },
      }, { timestamps: false, tableName: 'request_services' })

      const RequestServiceLocations = connection.define('request_service_locations', {
        id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
        request_id: { type: INTEGER, allowNull: false },
        service_id: { type: INTEGER, allowNull: false },
        address: { type: STRING, allowNull: true, },
        city: { type: STRING, allowNull: true, },
        state: { type: STRING, allowNull: true, },
        zip: { type: STRING, allowNull: true, },
        lng: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
        lat: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
        added_by: { type: INTEGER, defaultValue: null },
        timestamp: { type: INTEGER, allowNull: false },
        updated_by: { type: INTEGER, allowNull: true },
        status: { type: TINYINT, defaultValue: 1 },
        starting_point: { type: INTEGER, allowNull: true, defaultValue: null },
        ending_point: { type: INTEGER, allowNull: true, defaultValue: null },
        color: { type: TEXT, allowNull: true, defaultValue: null },
      }, { timestamps: false })

    return {
        Requests,
        CustomerOrders,
        CustomerOrderProducts,
        RequestAttachments,
        RequestLocations,
        Routes,
        Products,
        Projects,
        ProjectTasks,
        RequestUpdates,
        QuickbooksRefreshToken,
        RequestServices,
        RequestServiceLocations
    };
};


const getAllProductsFromMultipleDatabase = async (all = false, material) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        }
        else {
            whereCondition = `p.status = 1`;
        }
        if (material) {
            // console.log("########%$$$$$$$$");
            whereCondition += ` and p.meterial = 1`;
        }
        else {
            whereCondition += ` and p.meterial = 0`;
        }
        // Get all other installation databases
        const otherDatabases = getOtherInstallationDatabases();
        const allProducts = [];

        // Query each database
        for (const dbInfo of otherDatabases) {
            try {
                let query = await dbInfo.connection.query(
                    ` SELECT p.*,pc.name as category,psc.name as sub_category, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, tc.name AS tax, rc.name AS recipe_name, ow.name AS workflow_name,psc.markup as retailMarkup,psc.wholesale_markup as wholesaleMarkup, ` +
                    ` rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost` +
                    ` FROM products p `  +
                    ` LEFT JOIN product_category pc ON pc.id = p.category_id `  +
                    ` LEFT JOIN product_sub_category psc ON psc.id = p.sub_category_id ` +
                    ` LEFT JOIN users u ON p.added_by = u.id ` +
                    ` LEFT JOIN tax_category tc ON p.tax_category = tc.id ` +
                    ` LEFT JOIN recipes rc ON p.recipe_id = rc.id ` +
                    ` LEFT JOIN onboard_workflows ow ON p.workflow = ow.id ` +
                    ` WHERE ${whereCondition} AND p.recipe_id IS NOT NULL ` +
                    ` ORDER BY p.id DESC`,
                );

                if (query.length > 0) {
                    // Add installation information to each product
                    const productsWithInstallation = query[0].map(product => ({
                        ...product,
                        facility_id: dbInfo.id,
                    }));
                    allProducts.push(...productsWithInstallation);
                }
            } catch (error) {
                console.error(`Error fetching products from ${dbInfo.name} database:`, error);
                // Continue with other databases even if one fails
            }
        }

        return allProducts || [];
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getAllProductsFromSpecificFacility = async (facilityId, all = false, material) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        }
        else {
            whereCondition = `p.status = 1`;
        }
        if (material) {
            whereCondition += ` and p.meterial = 1`;
        }
        else {
            whereCondition += ` and p.meterial = 0`;
        }

        // Get all other installation databases
        const otherDatabases = getOtherInstallationDatabases();
        const allProducts = [];

        // Query each database
        for (const dbInfo of otherDatabases) {
            // Only query the specific facility database
            if (dbInfo.id === facilityId) {
                try {
                    let query = await dbInfo.connection.query(
                        ` SELECT p.*,pc.name as category,psc.name as sub_category, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, tc.name AS tax, rc.name AS recipe_name, ow.name AS workflow_name,psc.markup as retailMarkup,psc.wholesale_markup as wholesaleMarkup, ` +
                        ` rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost` +
                        ` FROM products p `  +
                        ` LEFT JOIN product_category pc ON pc.id = p.category_id `  +
                        ` LEFT JOIN product_sub_category psc ON psc.id = p.sub_category_id ` +
                        ` LEFT JOIN users u ON p.added_by = u.id ` +
                        ` LEFT JOIN tax_category tc ON p.tax_category = tc.id ` +
                        ` LEFT JOIN recipes rc ON p.recipe_id = rc.id ` +
                        ` LEFT JOIN onboard_workflows ow ON p.workflow = ow.id ` +
                        ` WHERE ${whereCondition} ` +
                        ` ORDER BY p.id DESC`,
                    );

                    if (query.length > 0) {
                        // Add installation information to each product
                        const productsWithInstallation = query[0].map(product => ({
                            ...product,
                            facility_id: dbInfo.id,
                        }));
                        allProducts.push(...productsWithInstallation);
                    }
                } catch (error) {
                    console.error(`Error fetching products from ${dbInfo.name} database:`, error);
                    // Continue with other databases even if one fails
                }
            }
        }

        return allProducts || [];
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getProjectIdFromOtherFacility = async (facilityId, originRequestId) => {
    console.log('originRequestId',facilityId, originRequestId)
    try {

        const otherDatabases = getOtherInstallationDatabases();
        let projectId = null;

        for (const dbInfo of otherDatabases) {
            if (String(dbInfo.id) === String(facilityId)) {
                try {
                    const query = await dbInfo.connection.query(
                        `SELECT p.id AS projectId 
                        FROM requests r
                        LEFT JOIN projects p ON r.id = p.request_id 
                        WHERE r.id = ${originRequestId}`
                    );
                    if (query && query.length > 0 && query[0].length > 0) {
                        projectId = query[0][0].projectId;
                        console.log('Found project ID:', projectId);
                        break; 
                    }
                } catch (error) {
                    console.error(`Error fetching project from ${dbInfo.name} database:`, error);
                }
            }
        }

        return projectId; // Return the project ID, or null if not found
    } catch (error) {
        console.error('Error fetching project ID from other facility:', error);
        throw new Error('Error fetching project ID from other facility');
    }
};


// Function to get all installation company addresses
const getAllInstallationCompanyAddresses = async () => {
    try {
        // Get all other installation databases
        const otherDatabases = getOtherInstallationDatabases();
        const allCompanyAddresses = [];

        // Query each database for company profile details
        for (const dbInfo of otherDatabases) {
            try {
                let query = await dbInfo.connection.query(
                    `SELECT * FROM company_profile`
                );

                if (query.length > 0) {
                    // Add installation information to each company profile
                    const companyWithInstallation = query[0].map(company => ({
                        ...company,
                        sourceInstallation: dbInfo.name,
                        sourceInstallationId: dbInfo.id,
                        facility_id: dbInfo.id,
                    }));
                    allCompanyAddresses.push(...companyWithInstallation);
                }
            } catch (error) {
                console.error(`Error fetching company profile from ${dbInfo.name} database:`, error);
                // Continue with other databases even if one fails
            }
        }

        return allCompanyAddresses;
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getAllSplitManagementSettingsFromOtherInstallations = async () => {
    try {
        // Get all other installation databases
        const otherDatabases = getOtherInstallationDatabases();
        const allSplitSettings = [];

        // Query each database for split management settings
        for (const dbInfo of otherDatabases) {
            try {
                let query = await dbInfo.connection.query(
                    `SELECT * FROM split_management_settings WHERE status = 1`
                );

                if (query.length > 0) {
                    // Add installation information to each split setting
                    const settingsWithInstallation = query[0].map(setting => ({
                        ...setting,
                        sourceInstallation: dbInfo.name,
                        sourceInstallationId: dbInfo.id,
                        facility_id: dbInfo.id,
                    }));
                    allSplitSettings.push(...settingsWithInstallation);
                }
            } catch (error) {
                console.error(`Error fetching split management settings from ${dbInfo.name} database:`, error);
                // Continue with other databases even if one fails
            }
        }

        return allSplitSettings;
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getAllInstallLabourFromOtherInstallations = async () => {
    try {
        // Get all other installation databases
        const otherDatabases = getOtherInstallationDatabases();
        const allInstallLabor = [];

        // Query each database for split management settings
        for (const dbInfo of otherDatabases) {
            try {
                let query = await dbInfo.connection.query(
                    `SELECT * FROM labour_cost_services WHERE status = 1 AND id = 10`
                );

                if (query.length > 0) {
                    const installLabourOtherinstallation = query[0].map(setting => ({
                        ...setting,
                        sourceInstallation: dbInfo.name,
                        sourceInstallationId: dbInfo.id,
                        facility_id: dbInfo.id,
                    }));
                    allInstallLabor.push(...installLabourOtherinstallation);
                }
            } catch (error) {
                console.error(`Error fetching Install labour from ${dbInfo.name} database:`, error);
                // Continue with other databases even if one fails
            }
        }

        return allInstallLabor;
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

// Get all labour cost services from other installation databases (similar to product.js -> getAllLabourCostServices)
const getAllLabourCostServicesFromOtherInstallations = async () => {
    try {
        const otherDatabases = getOtherInstallationDatabases();
        const allServices = [];

        for (const dbInfo of otherDatabases) {
            try {
                const query = await dbInfo.connection.query(
                    `SELECT ls.*,lst.name AS type_name, CONCAT(u.first_name,' ',u.last_name) AS authorized_by,ls.markup as originalMarkup,ow.name as workflow_name FROM labour_cost_services ls` +
                    ` LEFT JOIN users u ON u.id = ls.added_by` +
                    ` LEFT JOIN onboard_workflows ow ON ow.id = ls.workflow` +
                    ` LEFT JOIN labour_cost_services_type lst ON lst.id = ls.service_type` +
                    ` WHERE ls.status != 2 ORDER BY ls.id DESC`
                );

                if (query && query.length > 0 && query[0]?.length > 0) {
                    const servicesWithInstallation = query[0].map(service => ({
                        ...service,
                        sourceInstallation: dbInfo.name,
                        sourceInstallationId: dbInfo.id,
                        facility_id: dbInfo.id,
                    }));
                    allServices.push(...servicesWithInstallation);
                }
            } catch (error) {
                console.error(`Error fetching labour cost services from ${dbInfo.name} database:`, error);
            }
        }

        return allServices || [];
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

// Simple Avalara void function for build orders
async function voidAvalaraTransactionForBuildOrder(transactionCode) {
    if (!transactionCode) {
        console.log('⚠️ No Avalara transaction code provided for voiding');
        return { success: false, message: 'No transaction code provided' };
    }

    const authHeader = 'Basic ' + Buffer.from(
        `${AVALARA_ACCOUNT_ID}:${AVALARA_LICENSE_KEY}`
    ).toString('base64');

    const baseURL = (AVALARA_BASE_URL || 'https://sandbox-rest.avatax.com') + '/api/v2';
    const url = `${baseURL}/companies/DEFAULT/transactions/${transactionCode}/void`;
    
    console.log('🗑️ [BUILD ORDER DELETE] Voiding Avalara transaction:', transactionCode);

    try {
        const { data } = await axios.post(url, {
            code: "DocVoided",
            reason: "Build order deleted - voiding transaction"
        }, {
            headers: {
                Authorization: authHeader,
                'Content-Type': 'application/json'
            },
            timeout: 20000
        });

        console.log('✅ [BUILD ORDER DELETE] Successfully voided Avalara transaction:', transactionCode);
        return { success: true, data: data };

    } catch (err) {
        const msg = err?.response?.data || err.message;
        console.error('❌ [BUILD ORDER DELETE] Error voiding transaction:', msg);
        return { success: false, message: `Avalara void failed: ${JSON.stringify(msg)}` };
    }
}

// Avalara function for build orders (copied from request.js)
async function createAvalaraEstimateForBuildOrder(estimateData) {
    // For build orders, we want to store amounts but with 0 tax (exempt case)
    const isNoCharge = false; // Don't zero out amounts
    
    const shipFrom = {
        line1: '680 Columbia Ave',
        city: 'Riverside',
        region: 'CA',
        postalCode: '92507',
        country: 'US'
    };
    
    const client = estimateData.clientDetails || {};
    
    // NEW: Get company address from settings instead of project address
    let shipTo = {
        line1: '680 Columbia Ave',
        city: 'Riverside',
        region: 'CA',
        postalCode: '92507',
        country: 'US'
    };
    
    try {
        // Get company profile details for ship-to address
        const companyProfile = await Settings.getCompanyProfileDetails();
        if (companyProfile && companyProfile.length > 0) {
            const companyData = companyProfile[0];
            shipTo = {
                line1: companyData?.address || '680 Columbia Ave',
                city: companyData?.city || 'Riverside',
                region: companyData?.state || 'CA',
                postalCode: companyData?.zipcode || '92507',
                country: 'US'
            };
            console.log('🏢 [BUILD ORDER] Using company address for ship-to:', shipTo);
        }
    } catch (error) {
        console.warn('⚠️ [BUILD ORDER] Failed to get company address, using default:', error.message);
        // shipTo remains as default
    }
    
    const lines = [];
    const discount = Number(estimateData.discount || 0);
    
    function pushLine(name, qty, unit, taxCode) {
        const amount = isNoCharge ? 0 : +(unit * qty).toFixed(2);
        lines.push({
            number: String(lines.length + 1),
            quantity: qty,
            amount,
            description: name,
            itemCode: name,
            taxCode: taxCode || 'SI026666'
        });
    }
    
    // Process catalogs (products)
    (estimateData.catalogs || []).forEach(p => {
        console.log('ppppppppppppp', p)
        const qty = Number(p.quantity || 1) * Number(p.locationQuantity || 1);
        
        // Create separate line items for product and install price (like normal sales orders)
        
        // 1. Product price line item
        const productPrice = isNoCharge ? 0 : Number(p.expected_cost || p.SPMA || p.price || 0);
        if (productPrice > 0) {
            pushLine(p.name, qty, productPrice, p.taxCode);
        }
        
        // 2. Install price line item (if exists)
        const installPrice = isNoCharge ? 0 : Number(p.install_cost || 0);
        if (installPrice > 0) {
            pushLine(`Install Price`, qty, installPrice, p.taxCode);
        }
        
        console.log('Product price:', productPrice, 'Install price:', installPrice);
    });
    
    if (!lines.length) {
        // throw new Error('No sellable line-items found.');
        return{}
    }
    
    const payload = {
        type: 'SalesInvoice',
        commit: false,
        code: `${Date.now()}`,
        date: moment().format('YYYY-MM-DD'),
        customerCode: String(client?.id || 'UNKNOWN'),
        companyCode: 'DEFAULT',
        discount,
        addresses: { shipFrom, shipTo },
        lines,
        // Tax override to force 0 tax for build orders (exempt case)
        taxOverride: {
            type: 'TaxAmount',
            reason: 'Build order - exempt from tax',
            taxAmount: 0
        }
    };
    console.log('payload', payload)
    console.log('Avalara addresses:', {
        shipFrom: shipFrom,
        shipTo: shipTo
    });
    console.log('Avalara credentials:', {
        accountId: AVALARA_ACCOUNT_ID ? 'Present' : 'Missing',
        licenseKey: AVALARA_LICENSE_KEY ? 'Present' : 'Missing',
        baseUrl: AVALARA_BASE_URL || 'Using default'
    });
    
    const authHeader = 'Basic ' + Buffer.from(
        `${AVALARA_ACCOUNT_ID}:${AVALARA_LICENSE_KEY}`
    ).toString('base64');
    const baseURL = (AVALARA_BASE_URL || 'https://sandbox-rest.avatax.com') + '/api/v2';
    
    try {
        const { data } = await axios.post(
            `${baseURL}/transactions/create`,
            payload,
            { headers: { Authorization: authHeader, 'Content-Type': 'application/json' }, timeout: 30000 }
        );
        
        return {
            avalara_transaction_id: data.id,
            avalara_code: data.code,
            totalTax: data.totalTax,
        };
    } catch (err) {
        console.error('Avalara API Error:', err.response?.data || err.message);
        // throw err;
        return {}
    }
}
//////////////////////////////Quickbook functions//////////////////////////////

const findCustomerIdByName = async (customerName) => { //this is for installation eg clients are arizona,riverside,la
    let name =customerName
    try {
      // Ensure valid access token
      let token = await getNewAccessToken();
  
      const apiUrl = PRODUCTION
      ? `https://quickbooks.api.intuit.com/v3/company/${realmId}/query`
      : `https://sandbox-quickbooks.api.intuit.com/v3/company/${realmId}/query`;
  
  
      const response = await axios.get(
        apiUrl,
        {
          headers: {
            'Authorization': `Bearer ${token}`,
            'Content-Type': 'application/text'
          },
          params: {
            query: `SELECT * FROM Customer WHERE DisplayName = '${name.trim()}'`
          }
        }
      );
  
      const customers = response.data.QueryResponse.Customer;
  
      if (customers && customers.length > 0) {
        return customers[0].Id;
      } else {
       return null
      }
    } catch (err) {
      console.error('Error finding customer by name:', err.response ? err.response.data : err.message);
      return null
    }
  };
  const findItemById = async (id) => {
    const token = await getNewAccessToken();
    const apiUrl = PRODUCTION
      ? `https://quickbooks.api.intuit.com/v3/company/${realmId}/query`
      : `https://sandbox-quickbooks.api.intuit.com/v3/company/${realmId}/query`;
  
    try {
      const response = await axios.get(
        apiUrl,
        {
          headers: {
            'Authorization': `Bearer ${token}`,
            'Content-Type': 'application/text'
          },
          params: {
            query: `SELECT * FROM Item WHERE Id = '${id}'`
          }
        }
      );
  
      console.log('response.data', response.data);
      const items = response.data.QueryResponse.Item;
      if (items && items.length > 0) {
        return items[0];
      } else {
        return null
      }
    } catch (err) {
      console.error('Error finding item by ID:', id, err.response ? err.response.data : err.message);
      return null
    }
  };


let UniqueEstimateId =null
let doc_number = null

const addEstimateToQuickBooks = async (estimateData) => {
    console.log("Attempting to add estimate to QuickBooks...");
    let token = await getNewAccessToken();
  
    const postEstimate = async (useNewToken = false, attempt = 0) => {
      if (useNewToken) {
        console.log('Refreshing access token...');
        token = await getNewAccessToken();
      }
  
      try {
        // Ensure all items have valid inventory start dates
        // for (let item of estimateData.items) {
        //   // const itemDetails = await findItemByName(item.name);
        //   const itemDetails = await findItemById(item.itemId);
        //   item.inventoryStartDate = itemDetails.InventoryStartDate;
        // }
  
        // Use the latest inventory start date to set the transaction date
        // const txnDate = moment.max(estimateData.items.map(item => moment(item.inventoryStartDate))).add(10, 'days').format('YYYY-MM-DD');
        const txnDate = moment().format('YYYY-MM-DD');
  
        const discountAccounts = await getAccounts('Income', 'DiscountsRefundsGiven');
        const dynamicDiscountAccRefId = discountAccounts?.length > 0 ? discountAccounts[0].Id : null;
  
        const discountAccId= dynamicDiscountAccRefId || await getAccountIdByName("Discounts") 
  
        // Prepare the QuickBooks estimate data
        let quickBooksEstimateData = {
          CustomerRef: { value: estimateData.customerId },
          TxnDate: txnDate,
          DepartmentRef: { value: departmentId },
          Line: [
            ...estimateData.items.map(item => ({
              Amount: item.amount,
              DetailType: "SalesItemLineDetail",
              SalesItemLineDetail: {
                ItemRef: { value: item.itemId },
                Qty: item.quantity,
                UnitPrice: item.unitPrice,
                TaxCodeRef: { value: 'TAX' } 
              }
            })),
            {
              Amount: estimateData.discount,
              DetailType: "DiscountLineDetail",
              DiscountLineDetail: {
                DiscountAccountRef: { value: discountAccId } // Your Discount Account ID
              }
            }
          ],
          PrivateNote: estimateData.name || estimateData.description,
          // TxnTaxDetail: {
          //   TxnTaxCodeRef: { value: 'TAX' },
          //   TotalTax: estimateData.taxAmount // Set the total tax amount here
          // },
          CustomField: [
            {
              DefinitionId: "1", // Replace this with your actual custom field DefinitionId
              Name: "P.O. Number", // The name of your custom field
              Type: "StringType",
              StringValue: (estimateData.poNumber || "")?.slice(0, 30) // The value you want to set in the custom field
            },
            {
              DefinitionId: "2", // Replace this with your actual custom field DefinitionId
              Name: "Project", // The name of your custom field
              Type: "StringType",
              StringValue: (estimateData.projectName || "")?.slice(0, 30)// The value you want to set in the custom field
            },
            {
              DefinitionId: "3", // Replace this with your actual custom field DefinitionId
              Name: "Sales Rep Name", // The name of your custom field
              Type: "StringType",
              StringValue: (estimateData?.salesRepName || "")?.slice(0, 30)// The value you want to set in the custom field
            },
          ],
        };
  
        if(estimateData.jobClass){
          quickBooksEstimateData.ClassRef = { value: estimateData.jobClass }
        }
          if(estimateData?.clientData){
            quickBooksEstimateData.BillEmail = {
              Address: estimateData?.clientData?.email || ""
            }
            quickBooksEstimateData.BillAddr = {
              Line1: estimateData.clientData?.quickbookCustomerName || "",
              Line2: estimateData.clientData?.address || "",
              City: estimateData.clientData?.city || "",
              CountrySubDivisionCode: estimateData.clientData?.state || "",
              PostalCode: estimateData.clientData?.zip_code || "",
              // Country: estimateData.clientData?.country || ""
            }
          }
          if(estimateData?.ProjectData){
            quickBooksEstimateData.ShipAddr = {
              Line1: estimateData.ProjectData?.name || "",
              Line2: estimateData.ProjectData?.address || "",
              City: estimateData.ProjectData?.city || "",
              CountrySubDivisionCode: estimateData.ProjectData?.state || "",
              PostalCode: estimateData.ProjectData?.zip || estimateData.ProjectData?.zip_code || "",
              // Country: estimateData.ProjectData?.country || ""
            }
          }
  
        console.log("QuickBooks Estimate Data:", JSON.stringify(quickBooksEstimateData, null, 2));
  
        const apiUrl = PRODUCTION
        ? `https://quickbooks.api.intuit.com/v3/company/${realmId}`
        : `https://sandbox-quickbooks.api.intuit.com/v3/company/${realmId}`;
    
  
        const response = await axios.post(
          // `https://sandbox-quickbooks.api.intuit.com/v3/company/${realmId}/estimate`,
          `${apiUrl}/estimate`,
          quickBooksEstimateData,
          {
            headers: {
              Authorization: `Bearer ${token}`,
              'Content-Type': 'application/json',
              Accept: 'application/json'
            },
            // timeout: 30000 // 30 seconds
          }
        );
  
        if (response.status === 200) {
          console.log("Estimate has been successfully added to QuickBooks:", response.data);
          console.log("Unique Estimate ID:", response.data.Estimate.Id);
          UniqueEstimateId = response.data.Estimate.Id;
          doc_number = response.data.Estimate.DocNumber;
          // createAvalaraSalesOrder(response.data)
          return response.data;
        } else {
          console.error("Failed to add the estimate to QuickBooks:", response.data);
          return null;
        }
  
      } catch (error) {
        if (error.code === 'ENOTFOUND' && attempt < 5) {
          // Retry logic with exponential backoff in case of DNS lookup failure
          const backoffTime = Math.pow(2, attempt) * 1000;
          console.log(`DNS lookup failed, retrying in ${backoffTime / 1000} seconds... (Attempt ${attempt + 1})`);
          await new Promise(resolve => setTimeout(resolve, backoffTime));
          return postEstimate(useNewToken, attempt + 1); // Retry
        }
  
        // Other errors
        console.error("Error adding estimate to QuickBooks:", error.response ? error.response.data : error.message);
        const errs = error.response?.data?.Fault?.Error || [];
        errs.forEach((e, i) => {
        console.log(`--- QBO Error #${i + 1} ---`);
        console.log("Message :", e.Message);
        console.log("Detail  :", e.Detail);
        console.log("Code    :", e.code);
        console.log("Element :", e.element); // <-- THIS often includes Line[xx]
        });
        const faultType = error.response?.data?.Fault?.type;
        if (faultType === 'AUTHENTICATION') {
          console.log('Access token expired, refreshing token...');
          return postEstimate(true); // Retry with new access token
        } else {
          throw error; // Rethrow error if not related to DNS or authentication
        }
      }
    };
  
    return postEstimate();
  };
  



const addCompleteSalesOrderToOtherInstallation = async (facilityId, requestData, userId, uploaded, values, avalaraTaxAmount, negativeAmount, taxCategory, address,productAttachments,isFromEdit,matchingOtherInstallationRequestData) => {
    console.log('requestData__ build order to other installation', requestData)
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === facilityId);
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        const currentDate = moment().unix();
        
        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        
        try {
            // Step 0: Avalara Sync for Build Orders (with tax = 0)
            let addRequestToAvalata = {};
            let avalaraTaxAmount = 0;
            
            // try {
            //     // Filter products for this specific facility (same logic as customer order creation)
            //     let filteredProducts = values.products.filter(product => product.facility_id == facilityId);
            //     console.log(`filteredProducts for facility ${facilityId}---`, filteredProducts);
                
            //     // Check if there are products for this facility
            //     const hasProductsForFacility = filteredProducts && filteredProducts.length > 0;
                
            //     if (hasProductsForFacility) {
            //         console.log(`🔄 Syncing build order with Avalara for facility ${facilityId}...`);
            //         console.log(`📊 Products for facility ${facilityId}:`, filteredProducts.length);
                    
            //         // Prepare Avalara data for build order (only products, no materials/rfp items)
            //         const avalaraData = {
            //             name: requestData.name,
            //             description: requestData.description,
            //             clientDetails: null, // Build orders don't have client data - will use 'UNKNOWN' in Avalara
            //             catalogs: filteredProducts, // Only filtered products for this facility
            //             services: [], // Build orders don't have services
            //             materials: [], // Build orders don't have materials
            //             rfpProducts: [], // Build orders don't have rfp products
            //             rfpServices: [], // Build orders don't have rfp services
            //             produtFormulaMaterial: [],
            //             discount: 0, // No discount for build orders
            //             isNoCharge: false, // Store actual amounts but with 0 tax (exempt case)
            //             projectData: null // Build orders don't have project data
            //         };
                    
            //         console.log(`📤 Avalara data prepared for facility ${facilityId}:`, {
            //             name: avalaraData.name,
            //             catalogsCount: avalaraData.catalogs.length,
            //             isNoCharge: avalaraData.isNoCharge
            //         });
                    
            //         addRequestToAvalata = await createAvalaraEstimateForBuildOrder(avalaraData);
            //         console.log(`✅ Avalara sync completed for build order in facility ${facilityId}:`, {
            //             transactionId: addRequestToAvalata?.avalara_transaction_id,
            //             code: addRequestToAvalata?.avalara_code
            //         });
            //     } else {
            //         console.log(`⚠️ Skipping Avalara sync for facility ${facilityId}: No products for this facility`);
            //     }
            // } catch (e) {
            //     console.warn(`⚠️ Avalara sync failed for facility ${facilityId}:`, e.message);
            //     // Continue without Avalara sync
            // }
            
            // Update request data with Avalara information
            // requestData.avalara_transaction_id = addRequestToAvalata?.avalara_transaction_id || null;
            // requestData.avalara_code = Number(addRequestToAvalata?.avalara_code) || null;

            //step 0.1 : sync to QB
            let filteredProductsForQb = values.products.filter(product => product.facility_id == facilityId);
            let filteredServicesForQb = [];
            if (values.additionalSelectedServices && Array.isArray(values.additionalSelectedServices)) {
                filteredServicesForQb = values.additionalSelectedServices.filter(service => service.facility_id == facilityId);
                console.log(`filteredServicesqb`, filteredServicesForQb);
            }
            // let filteredServicesForQb = values.additionalSelectedServices.filter(service => service.facility_id == facilityId);   
            console.log('filteredServicesForQb values.additionalSelectedServices', values.additionalSelectedServices)
            try {
                requestData['estimate_id'] = null
                requestData['syncWithQuickbooks'] = 0
                requestData['doc_number'] = null
                requestData['est_doc_number'] = null

                let installPriceQbId = null;
                let findInstallPriceQbId = await findItemByNamewithLocationInventoryCode('Install Price');
                if(findInstallPriceQbId && findInstallPriceQbId.Id){
                  installPriceQbId = findInstallPriceQbId.Id;
                }

                let productItems = await Promise.all(
                    filteredProductsForQb?.map(async product => {
                      console.log('product product', product)
                      let productId =  product?.quickbookId                         //await findItemIdByName(product.name);
                      let  Quantity = product.quantity * product.locationQuantity
                      
                      let productPrice = (product?.isAdditionalProduct ? parseFloat(product?.price)?.toFixed(2) || 0 : parseFloat(product?.SPMA)?.toFixed(2) || 0)
                      
                      const installQty = Number(product?.install_quantity ?? product?.install_qty ?? 1);
                      const installCostToAdd = product?.install_cost && installQty > 0 ? Number(product.install_cost) : 0;
                    //   const retailPrice = (Number(product?.expected_cost) + installCostToAdd) || product?.retail_price;
                      const retailPrice = (product?.expected_cost) || product?.retail_price;
              
                    //   return {
                    //     itemId: productId,
                    //     name: product.name ,
                    //     amount: retailPrice *  Quantity,
                    //     quantity: Quantity ,
                    //     unitPrice: retailPrice,
                    //   };
                    const productData =   {
                        itemId: productId,
                        name: product.name ,
                        amount: retailPrice *  Quantity,
                        quantity: Quantity ,
                        unitPrice: retailPrice,
                      };

                      if (product?.install_cost && product?.install_cost > 0) {
                        const installPriceData = {
                          itemId: installPriceQbId,  // Assuming `installPriceQbId` is defined elsewhere
                          name: "Install Price",
                          amount:  product?.install_cost * product?.install_quantity,
                          quantity: product?.install_quantity,
                          unitPrice:  product?.install_cost,
                        };
                  
                        // Return both rows (product and install price)
                        return [productData, installPriceData];
                      } else {
                        // Return only the base product data if no install cost
                        return [productData];
                      }

                    })
                  );
                  productItems = productItems.flat();

                  console.log('productItems productItems', productItems)
                  const serviceItems = await Promise.all(
                    filteredServicesForQb  // Filter out services with quantityDifference == 0
                      .map(async service => {
                        let serviceId = service.quickbookId;
                        let quantity = service.quantity || 1;
                        let cost = parseFloat(service.costWithOutgoing || service.total_cost).toFixed(2) || 0;
                        console.log("Quantity serviceItems--------", quantity);
                        return {
                          itemId: serviceId,
                          name: service.name,
                          amount: cost * quantity,
                          quantity: quantity,  // Ensure quantity is greater than zero
                          unitPrice: cost,
                        };
                      })
                  );
                  console.log('serviceItems serviceItems', serviceItems)
                  let clientName =qbCustomerName
                  let custimerId = await findCustomerIdByName(clientName);
                  let QbRequestData = {
                    name: requestData.name,
                    description: requestData.description,
                    customerId: custimerId,
                    items: [...productItems,...serviceItems], // Only filtered products for this facility
                    discount: 0, // No discount for build orders
                    poNumber:requestData.po_number || null,
                    jobClass:values?.project_type?.quickBookId || null,
                    // salesRepName :requestData.salesRepName || null,
                  }
                  let ReasonWithQb =""
                  const nonSyncedProducts =filteredProductsForQb.filter(product => product.quickbookId === null);
                  const nonSyncedServices =filteredServicesForQb.filter(service => service.quickbookId === null);
               //    if(nonSyncedProducts.length > 0){
                   ReasonWithQb = `The following products are not in QuickBooks:
                   ${!custimerId ? `Customer : ${clientName} is not in QuickBooks` : ''}
                   ${nonSyncedProducts.length > 0 ? `Products : ${nonSyncedProducts.map(product => product.name).join(', ')}` : ''}
                   ${nonSyncedServices.length > 0 ? `Services : ${nonSyncedServices.map(service => service.name).join(', ')}` : ''}
                    `;
                   requestData['reasonWithQb'] = ReasonWithQb;
               //    }
               const readyToSync =  (custimerId && nonSyncedProducts.length === 0 && nonSyncedServices.length === 0);

               if(isFromEdit && matchingOtherInstallationRequestData && matchingOtherInstallationRequestData.estimate_id){
                   let qbRes = await editEstimateInQuickBooks(QbRequestData,matchingOtherInstallationRequestData.estimate_id);
                   requestData['estimate_id'] =qbRes?.Estimate?.Id || matchingOtherInstallationRequestData.estimate_id
                   requestData['syncWithQuickbooks'] = 1
                   requestData['doc_number'] =qbRes?.Estimate?.DocNumber || matchingOtherInstallationRequestData.doc_number
                   requestData['est_doc_number'] =qbRes?.Estimate?.DocNumber || matchingOtherInstallationRequestData.est_doc_number
               }else{
                  let quickbookEstimateId =readyToSync ? await addEstimateToQuickBooks(QbRequestData) : null;
                  doc_number = quickbookEstimateId.Estimate.DocNumber || doc_number;
               }
    
                  if(UniqueEstimateId != null){
                    requestData['estimate_id'] = UniqueEstimateId
                    requestData['syncWithQuickbooks'] = 1
                    requestData['doc_number'] = doc_number
                    requestData['est_doc_number'] = doc_number
                  }
               } catch (error) {
                console.error('Error syncing to QuickBooks:', error);
               }

            // Step 1: Create Request
            const request = await targetModels.Requests.create(requestData, { transaction });
            const requestId = request.id;
            
            // Step 2: Create Route
            const routeData = {
                name: `route ${currentDate}`,
                assigned_driver: null,
                timestamp: currentDate,
                added_by: userId,
                status: 1,
                assigned_sales_order: requestId,
                assigned: 0,
                unique_id: 'SWS' + (parseInt(crypto.randomBytes(2).toString('hex'), 16) % 90000 + 10000)
            };
            
            const route = await targetModels.Routes.create(routeData, { transaction });
            
            // Step 3: Create Request Attachments
            if (uploaded && uploaded.length > 0) {
                for (const row of uploaded) {
                    let approvedImage = {};
                    
                    if (requestData?.project_category == 11) {
                        approvedImage.approved = 1;
                    } else if (row?.file_type?.startsWith('image')) {
                        approvedImage.approved = 0;
                    } else {
                        approvedImage.approved = 1;
                    }
                    
                    approvedImage.name = row?.name || row?.url;
                    approvedImage.file_type = row.file_type;
                    approvedImage.extension = row.extension;
                    approvedImage.file_size = row.size || 2;
                    approvedImage.request_id = requestId;
                    approvedImage.added_by = userId;
                    approvedImage.timestamp = moment().unix();
                    
                    await targetModels.RequestAttachments.create(approvedImage, { transaction });
                }
            }
            
            // Step 4: Update Route with map service
            if (requestData.map_service) {
                await targetModels.Routes.update(
                    { assigned_sales_order: requestId },
                    { where: { id: route.id }, transaction }
                );
            }
            
            // Step 5: Create Customer Order
            let filteredProducts = values.products.filter(product => product.facility_id == facilityId);
            console.log('filteredProducts---', filteredProducts)
            
            // Filter services by facility_id
            let filteredServices = [];
            if (values.selectedServices && Array.isArray(values.selectedServices)) {
                filteredServices = values.selectedServices.filter(service => service.facility_id == facilityId);
                console.log(`filteredServices for facility ${facilityId}---`, filteredServices);
            }
            
            let totalCost = filteredProducts.reduce((sum, product) => {
                const installQtyValue = (product?.install_quantity !== null && product?.install_quantity !== undefined) 
                ? Number(product.install_quantity) 
                : ((product?.install_qty !== null && product?.install_qty !== undefined) 
                ? Number(product.install_qty) 
                : 1);
                return sum + (Number(product.expected_cost) * Number(product.locationQuantity)) + (Number(product.install_cost || 0) * Number(installQtyValue));
              }, 0);
              
            // Add services cost to total
            let servicesCost = filteredServices.reduce((sum, service) => {
                const serviceQty = service.quantity ? Number(service.quantity || 0) : 0;
                const serviceTotal = Number(Number(service.costWithOutgoing) * serviceQty);
                return sum + serviceTotal;
            }, 0);
            
            totalCost = totalCost + servicesCost;
              
              console.log("totalCost (with services)----", totalCost);
            //   avalaraTaxAmount = await fetchTaxAmountFromEstimateById(requestData.estimate_id);
            // const totalAmount = ((parseFloat(parseFloat(totalCost).toFixed(2)) + parseFloat(parseFloat(avalaraTaxAmount).toFixed(2))) * (negativeAmount || 1)).toFixed(2);
            // const totalAmount = (parseFloat(parseFloat(totalCost).toFixed(2)) * (1)).toFixed(2);
            const totalAmount = parseFloat(Number(totalCost || 0) + Number(avalaraTaxAmount || 0)).toFixed(2)
            const compnyProfile = await Settings.getCompanyProfileDetails()
            let partnerData = values?.clientsData
            console.log('partnerData-----', partnerData)
            const orderData = {
                total: totalAmount,
                grandTotal: totalAmount,
                // tax_amount: avalaraTaxAmount,
                tax_amount: 0,
                tax_category: taxCategory,
                budget: values.budget,
                extra_discount: values.invoice_discount || 0,
                request_id: requestId,
                timestamp: currentDate,
                sub_total: !empty(Number(totalCost)) ? Number(totalCost) : totalAmount,
                tax: 0,
                status: 1,
                customer_name: partnerData[0]['name'] || compnyProfile[0]?.name || null,
                customer_phone: partnerData[0]['phone'] || compnyProfile[0]?.phone || null,
                customer_address: partnerData[0]['address'] || `${compnyProfile[0]?.address || ''}${compnyProfile[0]?.city ? ', ' + compnyProfile[0]?.city : ''}${compnyProfile[0]?.state ? ', ' + compnyProfile[0]?.state : ''}${compnyProfile[0]?.zipcode ? ' ' + compnyProfile[0]?.zipcode : ''}` || null,
            };
            console.log('orderData-----', orderData)
            const customerOrder = await targetModels.CustomerOrders.create(orderData, { transaction });
            const orderId = customerOrder.id;
            
            // Step 6: Create Customer Order Products and Locations
            for (const row of filteredProducts) {
                console.log('rowwwwwww_1', row)
                const rowQuantity = row.quantity ? row.quantity : 1;
                const rowDiscount = 0;
                const rowAdjustment = row?.adjustment ? row?.adjustment : 0;
                
                // Find existing product by name if no ID provided
                let existingProductId = null;
                if (!row.id) {
                    // Note: You might need to implement findProductByName for target database
                    // For now, we'll use the provided ID or null
                    existingProductId = null;
                }
                const installQtyValue = Number(row?.install_quantity ?? row?.install_qty ?? 1);
                const installCostToAdd = row?.install_cost && installQtyValue > 0 ? Number(row.install_cost) : 0;
                const retailPrice = (Number(row?.expected_cost) + installCostToAdd) || row?.retail_price;
                const unitCost = Number(row?.expected_cost) || row?.retail_price;
                const ProductTotal = (Number(row?.expected_cost) * Number(row?.locationQuantity)) + (Number(row?.install_cost || 0) * Number(installQtyValue))
                const requestProduct = {
                    order_id: orderId,
                    product_index: row.indexId,
                    product_id: row.id || existingProductId || null,
                    product_name: row.name,
                    product_notes: row.product_notes || null,
                    install_notes: row.install_notes || null,
                    product_price: row.price || 0.00,
                    product_retail_price: retailPrice,
                    product_markup: row.markup ? row.markup : null,
                    product_qty: rowQuantity,
                    location_quantity: row.locationQuantity || 1,
                    product_sub_total: ProductTotal || 0,
                    product_tax: 0,
                    product_total: ProductTotal|| 0,
                    workflow: row.workflow,
                    discount: rowDiscount,
                    adjustment: rowAdjustment,
                    timestamp: currentDate,
                    status: row.status,
                    markAdjType: 2,
                    map_service: row?.fromMapServices ? 1 : 0,
                    allLocationSame: row?.allLocationSame ? 1 : 0,
                    isAdditionalProduct: row?.isAdditionalProduct ? 1 : 0,
                    is_single_card: row?.is_single_card ? 1 : 0,
                    facility_id: row?.facility_id || 1,
                    split_percentage: row?.split_percentage || 0,
                    incoming_split_percentage: row?.incoming_split_percentage || 0,
                    expected_formula_price: row?.originalExpectedCost || 0,
                    install_cost: row?.install_cost || null,
                    unit_cost: unitCost || null,
                    delivery_type_id : row?.delivery_installation?.delivery_type_id || row?.delivery_type_id || null,
                    installer_facility_id : row?.delivery_installation?.installer_facility_id || row?.installer_facility_id || null,
                    install_qty : installQtyValue ?? 1,
                    // install_cost: row?.install_cost || null,
                };
                console.log('requestProductrequestProduct', requestProduct)
                const orderProduct = await targetModels.CustomerOrderProducts.create(requestProduct, { transaction });
                
                // Step 7: Create Locations for this product
                if (address && address.length > 0) {
                    const productAddresses = address.filter(raw => (raw.productId === row.id && raw.productIndex === row.indexId));
                    console.log('productAddressesss', productAddresses)
                    
                    let locationData = {}
                                // Match attachments using product_index_id from payload or row.indexId
            const productIndexId = row?.indexId;
            console.log('productIndexIddd', productIndexId)
            console.log('productAttachmentsssss', productAttachments)
            // Get attachments for this product - check both by indexId key and by product_index_id in attachment payload
            let attachments = productAttachments[productIndexId] || [];
            console.log("attachments1234567890", attachments)
            // Also check if attachments have product_index_id field and match it
            if (attachments.length === 0 && productIndexId) {
              // Fallback: search all productAttachments for matching product_index_id
              for (const key in productAttachments) {
                console.log('keyyyyy', key)
                const attArray = productAttachments[key] || [];
                console.log('attArray', attArray)
                const matchingAttachments = attArray.filter(att => 
                  att.product_index_id === productIndexId || att.productIndexId === productIndexId
                );
                console.log('matchingAttachmentsmatchingAttachments', matchingAttachments)
                if (matchingAttachments.length > 0) {
                  attachments = matchingAttachments;
                  break;
                }
              }
            }

                                // Track existing attachments to avoid duplicates within the same batch
              let existingAttachmentUrls = new Set();
              let existingAttachmentIds = new Set();
                    for (const addr of productAddresses) {
                        //  locationData = {
                            locationData.order_id= orderProduct.id,
                            locationData.product_id= row.id || existingProductId || null,
                            locationData.address= addr.address,
                            locationData.city= addr.city,
                            locationData.state= addr.state,
                            locationData.zip= addr.zip?.trim(),
                            locationData.route_location_id= addr.route_location_id || null,
                            locationData.timestamp= moment().unix(),
                            locationData.added_by= userId,
                            locationData.isLinkedAll= attachments.length > 0 ? 1 : 0
                        // };
                        
                       const addLocation = await targetModels.RequestLocations.create(locationData, { transaction });
                       console.log('addLocation addLocation 1', addLocation.id)
                       console.log('addLocation addLocation 1', addLocation?.dataValues?.id)
                                       // Process product attachments for this location
                if (addLocation && attachments.length > 0 && addr) {
                    // Add attachments for this product/location (only if not already exists in this batch)
                    let addedCount = 0;
                    let skippedCount = 0;
                    
                    for (const attachment of attachments) {
                      const attachmentUrl = attachment.url || attachment.name;
                      const attachmentId = attachment.id;
                      
                      console.log('📎 [ADD] Checking attachment:', {
                        id: attachmentId,
                        url: attachmentUrl,
                        file_type: attachment.file_type,
                        productIndexId,
                        product_index_id: attachment.product_index_id,
                        addLocation,
                        alreadyExistsInBatch: existingAttachmentUrls.has(attachmentUrl)
                      });
                      
                      // Check if attachment already exists in this batch (same URL)
                      const existsInBatch = existingAttachmentUrls.has(attachmentUrl);
                      
                      if (existsInBatch) {
                        console.log('⏭️ [ADD] Attachment already exists in batch, skipping:', {
                          id: attachmentId,
                          url: attachmentUrl
                        });
                        skippedCount++;
                        continue;
                      }
                      
                      const attachmentData = {
                          request_id: requestId,
                          name: attachmentUrl,
                          file_type: attachment.file_type,
                          extension: attachment.extension,
                          file_size: attachment.size || attachment.file_size,
                          product_ids: null,
                          location_ids: String(addLocation?.dataValues?.id),
                          approved: 1,
                          approved_by: userId,
                          status: 1,
                          timestamp: moment().unix(),
                          added_by: userId
                        };
                        console.log('attachmentData attachmentData 1', attachmentData)
                      const attachmentResult = await targetModels.RequestAttachments.create(attachmentData, { transaction });
                      console.log('✅ [ADD] Attachment added successfully:', attachmentResult);
                      addedCount++;
                      
                      // Add to sets to avoid duplicates in same batch
                      existingAttachmentUrls.add(attachmentUrl);
                      if (attachmentResult && attachmentId) {
                        existingAttachmentIds.add(attachmentId);
                      }
                    }
                    
                    console.log('📊 [ADD] Attachment processing summary:', {
                      total: attachments.length,
                      added: addedCount,
                      skipped: skippedCount
                    });
                    
                    // Update isLinkedAll to 1 for this location
                    // await Request.updateLocationIsLinkedAll([addLocation]);
                    // console.log('✅ [ADD] Updated isLinkedAll for location:', addLocation);
                  }
                    }
                }
            }
            
            // Step 7: Create Request Services and Service Locations
            if (filteredServices && filteredServices.length > 0) {
                console.log(`Processing ${filteredServices.length} services for facility ${facilityId}...`);
                for (const row of filteredServices) {
                    let rowQuantity = row.quantity ? Number(row?.quantity || 0) : 0;
                    console.log('Processing service:', row.name, 'qty:', rowQuantity);
                    
                    let existingServiceId = null;
                    if (!row.id) {
                        // Note: You might need to implement findServiceByName for target database
                        // For now, we'll use the provided ID or null
                        existingServiceId = null;
                    }
                    
                    const requestService = {
                        product_index: row.productIndex || 0,
                        request_id: requestId,
                        labour_cost_service_id: isFromEdit ? (row?.labour_cost_service_id ?? row.id ?? existingServiceId) : row.id || existingServiceId || null,
                        recipe_id: row.recipe_id || null,
                        product_id: row.product_id || null,
                        qty: (values?.project_categories?.name == 'Time & Material' && row.id == 10) ? 0 : rowQuantity || 0,
                        unit_cost: Number(row.costWithOutgoing),
                        markup: row?.isAdditionalProduct ? 0 : Number(row.markup || 0),
                        total_cost: Number(row.costWithOutgoing * rowQuantity),
                        rfp: 0,
                        map_service: row?.fromMapServices ? 1 : 0,
                        jobcost_unit_cost: 0,
                        jobcost_quantity: (!row?.makeAsTask && !row.recipe_id && row?.labour_cost_service_id !== 10) ? rowQuantity : 0,
                        jobcost_total_cost: (!row?.makeAsTask && !row.recipe_id && row?.labour_cost_service_id !== 10) ? Number(row?.cost * rowQuantity) : 0,
                        isAdditionalProduct: row?.isAdditionalProduct ? 1 : 0,
                        makeAsTask: 1 ,
                        makeInstallCard: 1,
                        service_notes: row?.service_notes || null,
                        taken: values?.project_categories?.name == 'Time & Material' ? 1 : (!row?.makeAsTask && !row.recipe_id && row?.labour_cost_service_id !== 10) ? 1 : 0,
                        service_name: row?.name || null,
                        facility_id: row?.facility_id || null,
                        split_percentage: row?.split_percentage || 0,
                        incoming_split_percentage: row?.incoming_split_percentage || 0,
                        delivery_type_id: row?.delivery_installation?.delivery_type_id || null,
                        installer_facility_id: row?.delivery_installation?.installer_facility_id || null,
                    };
                    
                    // Adjust makeInstallCard for specific conditions
                    let lcsId = row?.id || existingServiceId;
                    if (values?.project_categories?.id !== 11 && lcsId == 10) {
                        requestService.makeInstallCard = 0;
                    }
                    
                    console.log('Creating service in target database:', requestService);
                    const serviceResponse = await targetModels.RequestServices.create(requestService, { transaction });
                    console.log('Service created with ID:', serviceResponse.id);
                    
                    // Create service locations
                    const serviceLocations = address?.filter(raw => raw.isService == 1);
                    if (serviceResponse && serviceLocations?.length > 0) {
                        const productId = isFromEdit ? (row?.labour_cost_service_id ?? row.id ?? existingServiceId) : row.id || existingServiceId || null;
                        let addResult = serviceLocations.filter(raw => (raw.productId === productId && raw.productIndex === row.indexId));
                        console.log('addResult addResult', addResult)
                        if (addResult && addResult.length > 0) {
                            for (let i = 0; i < addResult.length; i++) {
                                const locationData = {
                                    request_id: requestId,
                                    service_id: serviceResponse.id,
                                    address: addResult[i].address,
                                    city: addResult[i].city,
                                    state: addResult[i].state,
                                    zip: addResult[i].zip?.trim(),
                                    timestamp: moment().unix(),
                                    added_by: userId
                                };
                                const serviceLocation = await targetModels.RequestServiceLocations.create(locationData, { transaction });
                                console.log('serviceLocation serviceLocation', serviceLocation)
                            }
                        }
                    }
                }
                console.log(`✅ Successfully processed ${filteredServices.length} services for facility ${facilityId}`);
            }
            
            // Step 8: Create Request Update for split order conversion
            const updateData = {
                request_id: requestId,
                partner_id: null,
                partner_name: null,
                activity: `Sales Order "${requestData.name}" converted from ${CURRENT_INSTALLATION} (Split)`,
                added_by: userId,
                timestamp: moment().unix(),
                status: 1
            };
            await targetModels.RequestUpdates.create(updateData, { transaction });
            
            // Commit transaction
            await transaction.commit();
            
            return {
                success: true,
                data: {
                    requestId: requestId,
                    routeId: route.id,
                    orderId: orderId,
                    facilityId: facilityId,
                    installationName: targetDatabase.name
                }
            };
            
        } catch (error) {
            console.error('Error adding complete sales order to other installation:', error);
            // Rollback transaction on error
            await transaction.rollback();
           return error
        }
        
    } catch (error) {
        console.error('Error adding complete sales order to other installation:', error);
        return { success: false, message: error.message };
    }
};

const addCompleteSalesOrderToCurrentInstallation = async (facilityId, requestData, userId, uploaded, values, avalaraTaxAmount, negativeAmount, taxCategory, address,productAttachments,isFromEdit) => {
    console.log('requestData__ build order to current installation', requestData)
    try {
        const currentDate = moment().unix();

        // Start transaction (so this behaves like addMirrorCompleteSalesOrderToOtherInstallation: DB-only + atomic)
        const transaction = await sequelize.transaction();

        try {
            // Step 1: Create Request
            const request = await RequestsModel.create(requestData, { transaction });
            const requestId = request.id;
            
            // Step 2: Create Route
            const routeData = {
                name: `route ${currentDate}`,
                assigned_driver: null,
                timestamp: currentDate,
                added_by: userId,
                status: 1,
                assigned_sales_order: requestId,
                assigned: 0,
                unique_id: 'SWS' + (parseInt(crypto.randomBytes(2).toString('hex'), 16) % 90000 + 10000)
            };
            
            const route = await RoutesModel.create(routeData, { transaction });
            
            // Step 3: Create Request Attachments
            if (uploaded && uploaded.length > 0) {
                for (const row of uploaded) {
                    let approvedImage = {};
                    
                    if (requestData?.project_category == 11) {
                        approvedImage.approved = 1;
                    } else if (row?.file_type?.startsWith('image')) {
                        approvedImage.approved = 0;
                    } else {
                        approvedImage.approved = 1;
                    }
                    
                    approvedImage.name = row?.name || row?.url;
                    approvedImage.file_type = row.file_type;
                    approvedImage.extension = row.extension;
                    approvedImage.file_size = row.size || 2;
                    approvedImage.request_id = requestId;
                    approvedImage.added_by = userId;
                    approvedImage.timestamp = moment().unix();
                    
                    await RequestAttachmentsModel.create(approvedImage, { transaction });
                }
            }
            
            // Step 4: Update Route with map service
            if (requestData.map_service) {
                await RoutesModel.update(
                    { assigned_sales_order: requestId },
                    { where: { id: route.id }, transaction }
                );
            }
            
            // Step 5: Create Customer Order
            let filteredProducts = values?.products?.filter(product => product.facility_id == facilityId) || [];
            console.log('filteredProducts---', filteredProducts)
            
            // Filter services by facility_id
            let filteredServices = [];
            if (values.selectedServices && Array.isArray(values.selectedServices)) {
                filteredServices = values.selectedServices.filter(service => service.facility_id == facilityId);
                console.log(`filteredServices for facility ${facilityId}---`, filteredServices);
            }
            
            let totalCost = filteredProducts.reduce((sum, product) => {
                const installQtyValue = (product?.install_quantity !== null && product?.install_quantity !== undefined) 
                ? Number(product.install_quantity) 
                : ((product?.install_qty !== null && product?.install_qty !== undefined) 
                ? Number(product.install_qty) 
                : 1);
                return sum + (Number(product.expected_cost) * Number(product.locationQuantity)) + (Number(product.install_cost || 0) * Number(installQtyValue));
              }, 0);
              
            // Add services cost to total
            let servicesCost = filteredServices.reduce((sum, service) => {
                const serviceQty = service.quantity ? Number(service.quantity || 0) : 0;
                console.log('service.costWithOutgoing', service.costWithOutgoing)
                const serviceTotal = Number(Number(service.costWithOutgoing) * serviceQty);
                console.log('serviceTotal', serviceTotal)
                return sum + serviceTotal;
            }, 0);
            console.log('servicesCost', servicesCost)
            totalCost = totalCost + servicesCost;
              
              console.log("totalCost (with services)----", totalCost);
            // const totalAmount = ((parseFloat(parseFloat(totalCost).toFixed(2)) + parseFloat(parseFloat(avalaraTaxAmount).toFixed(2))) * (negativeAmount || 1)).toFixed(2);
            const totalAmount = (parseFloat(parseFloat(totalCost).toFixed(2)) * (1)).toFixed(2);
            console.log('totalAmount11', totalAmount)
            const compnyProfile = await Settings.getCompanyProfileDetails()
            let partnerData = values?.clientsData
            console.log('partnerData-----', partnerData)
            const orderData = {
                total: totalAmount,
                grandTotal: totalAmount,
                // tax_amount: avalaraTaxAmount,
                tax_amount: 0,
                tax_category: taxCategory,
                budget: values.budget,
                extra_discount: values.invoice_discount || 0,
                request_id: requestId,
                timestamp: currentDate,
                sub_total: !empty(Number(totalCost)) ? Number(totalCost) : totalAmount,
                tax: 0,
                status: 1,
                customer_name: partnerData[0]['name'] || compnyProfile[0]?.name || null,
                customer_phone: partnerData[0]['phone'] || compnyProfile[0]?.phone || null,
                customer_address: partnerData[0]['address'] || `${compnyProfile[0]?.address || ''}${compnyProfile[0]?.city ? ', ' + compnyProfile[0]?.city : ''}${compnyProfile[0]?.state ? ', ' + compnyProfile[0]?.state : ''}${compnyProfile[0]?.zipcode ? ' ' + compnyProfile[0]?.zipcode : ''}` || null,
            };
            console.log('orderData-----', orderData)
            const customerOrder = await CustomerOrdersModel.create(orderData, { transaction });
            const orderId = customerOrder.id;
            
            // Step 6: Create Customer Order Products and Locations
            for (const row of filteredProducts) {
                console.log('rowwwwwww_2', row)
                const rowQuantity = row.quantity ? row.quantity : 1;
                const rowDiscount = 0;
                const rowAdjustment = row?.adjustment ? row?.adjustment : 0;
                
                // Find existing product by name if no ID provided
                let existingProductId = null;
                if (!row.id) {
                    // Note: You might need to implement findProductByName for target database
                    // For now, we'll use the provided ID or null
                    existingProductId = null;
                }
                const installQtyValue = (row?.install_quantity !== null && row?.install_quantity !== undefined) 
                ? Number(row.install_quantity) 
                : ((row?.install_qty !== null && row?.install_qty !== undefined) 
                ? Number(row.install_qty) 
                : 1)
                const installCostToAdd = (installQtyValue > 0 && row?.install_cost) ? Number(row.install_cost) : 0;
                const retailPrice = (Number(row?.expected_cost) + installCostToAdd) || row?.retail_price;
                const unitCost = Number(row?.expected_cost) || row?.retail_price;
                const ProductTotal = (Number(row?.expected_cost) * Number(row?.locationQuantity)) + (Number(row?.install_cost || 0) * Number(installQtyValue))
                const requestProduct = {
                    order_id: orderId,
                    product_index: row.indexId,
                    product_id: row.id || existingProductId || null,
                    product_name: row.name,
                    product_notes: row.product_notes || null,
                    product_price: row.price || 0.00,
                    product_retail_price: retailPrice,
                    product_markup: row.markup ? row.markup : null,
                    product_qty: rowQuantity,
                    location_quantity: row.locationQuantity || 1,
                    product_sub_total: row.product_sub_total || ProductTotal || 0,
                    product_tax: 0,
                    product_total: row.product_total || ProductTotal|| 0,
                    workflow: row.workflow,
                    discount: rowDiscount,
                    adjustment: rowAdjustment,
                    timestamp: currentDate,
                    status: row.status,
                    markAdjType: 2,
                    map_service: row?.fromMapServices ? 1 : 0,
                    allLocationSame: row?.allLocationSame ? 1 : 0,
                    isAdditionalProduct: row?.isAdditionalProduct ? 1 : 0,
                    is_single_card: row?.is_single_card ? 1 : 0,
                    facility_id: row?.facility_id || 1,
                    split_percentage: row?.split_percentage || 0,
                    incoming_split_percentage: row?.incoming_split_percentage || 0,
                    expected_formula_price: row?.originalExpectedCost || 0,
                    install_cost: row?.install_cost || null,
                    unit_cost: unitCost || null,
                    // install_cost: row?.install_cost || null,
                    install_qty : installQtyValue ?? 1
                };
                console.log('requestProductrequestProduct', requestProduct)
                const orderProduct = await CustomerOrderProductsModel.create(requestProduct, { transaction });
                
                // Step 7: Create Locations for this product
                if (address && address.length > 0) {
                    const productAddresses = address.filter(raw => (raw.productId === row.id && raw.productIndex === row.indexId));
                    
                    let locationData = {}
                    const productIndexId = row?.indexId;
            // Get attachments for this product - check both by indexId key and by product_index_id in attachment payload
            console.log('productAttachmentsssss 2', productAttachments)
            let attachments = productAttachments[productIndexId] || [];
            console.log("attachments1234567890 2", attachments)
            // Also check if attachments have product_index_id field and match it
            if (attachments.length === 0 && productIndexId) {
              // Fallback: search all productAttachments for matching product_index_id
              for (const key in productAttachments) {
                const attArray = productAttachments[key] || [];
                const matchingAttachments = attArray.filter(att => 
                  att.product_index_id === productIndexId || att.productIndexId === productIndexId
                );
                if (matchingAttachments.length > 0) {
                  attachments = matchingAttachments;
                  break;
                }
              }
            }
                    for (const addr of productAddresses) {
                        locationData.order_id= orderProduct.id,
                            locationData.product_id= row.id || existingProductId || null,
                            locationData.address= addr.address,
                            locationData.city= addr.city,
                            locationData.state= addr.state,
                            locationData.zip= addr.zip?.trim(),
                            locationData.route_location_id= addr.route_location_id || null,
                            locationData.timestamp= moment().unix(),
                            locationData.added_by= userId,
                            locationData.isLinkedAll= attachments.length > 0 ? 1 : 0
                        
                       const addLocation = await RequestLocationsModel.create(locationData, { transaction });
                       console.log('addLocation addLocation 2', addLocation)
                                       // Process product attachments for this location
                if (addLocation && attachments.length > 0 && addr) {
                    // Add attachments for this product/location (only if not already exists in this batch)
                    let addedCount = 0;
                    let skippedCount = 0;
                    for (const attachment of attachments) {
                        console.log('attachmenttt', attachment)
                        const attachmentUrl = attachment.url || `attachment`;
                        const attachmentData = {
                            request_id: requestId,
                            name: attachmentUrl,
                            file_type: attachment.file_type,
                            extension: attachment.extension,
                            file_size: attachment.size || attachment.file_size,
                            product_ids: null,
                            location_ids: String(addLocation?.dataValues?.id),
                            approved: 1,
                            approved_by: userId,
                            status: 1,
                            timestamp: moment().unix(),
                            added_by: userId
                        };
                        console.log('attachmentData attachmentData 2', attachmentData)
                        const attachmentResult = await RequestAttachmentsModel.create(attachmentData, { transaction });
                        console.log('attachmentResult attachmentResult 2', attachmentResult)
                    }
                }
                    }
                }
            }
            
            // Step 7: Create Request Services and Service Locations
            if (filteredServices && filteredServices.length > 0) {
                console.log(`Processing ${filteredServices.length} services for facility ${facilityId}...`);
                for (const row of filteredServices) {
                    let rowQuantity = row.quantity ? Number(row?.quantity || 0) : 0;
                    console.log('Processing service:', row.name, 'qty:', rowQuantity);
                    
                    let existingServiceId = null;
                    if (!row.id) {
                        // Note: You might need to implement findServiceByName for current database
                        // For now, we'll use the provided ID or null
                        existingServiceId = null;
                    }
                    
                    const requestService = {
                        product_index: row.productIndex || 0,
                        request_id: requestId,
                        labour_cost_service_id: isFromEdit ? (row?.labour_cost_service_id ?? row.id ?? existingServiceId) : row.id || existingServiceId || null,
                        recipe_id: row.recipe_id || null,
                        product_id: row.product_id || null,
                        qty: (values?.project_categories?.name == 'Time & Material' && row.id == 10) ? 0 : rowQuantity || 0,
                        unit_cost: Number(row.costWithOutgoing),
                        markup: row?.isAdditionalProduct ? 0 : Number(row.markup || 0),
                        total_cost: Number(row.costWithOutgoing  * rowQuantity),
                        rfp: 0,
                        map_service: row?.fromMapServices ? 1 : 0,
                        jobcost_unit_cost: 0,
                        jobcost_quantity: (!row?.makeAsTask && !row.recipe_id && row?.labour_cost_service_id !== 10) ? rowQuantity : 0,
                        jobcost_total_cost: (!row?.makeAsTask && !row.recipe_id && row?.labour_cost_service_id !== 10) ? Number(row?.cost * rowQuantity) : 0,
                        isAdditionalProduct: row?.isAdditionalProduct ? 1 : 0,
                        makeAsTask: 1 ,
                        makeInstallCard: 1,
                        service_notes: row?.service_notes || null,
                        taken: values?.project_categories?.name == 'Time & Material' ? 1 : (!row?.makeAsTask && !row.recipe_id && row?.labour_cost_service_id !== 10) ? 1 : 0,
                        service_name: row?.name || null,
                        facility_id: row?.facility_id || null,
                        split_percentage: row?.split_percentage || 0,
                        incoming_split_percentage: row?.incoming_split_percentage || 0,
                        delivery_type_id: row?.delivery_installation?.delivery_type_id || null,
                        installer_facility_id: row?.delivery_installation?.installer_facility_id || null,
                    };
                    
                    // Adjust makeInstallCard for specific conditions
                    let lcsId = row?.id || existingServiceId;
                    if (values?.project_categories?.id !== 11 && lcsId == 10) {
                        requestService.makeInstallCard = 0;
                    }
                    
                    console.log('Creating service in current database:', requestService);
                    const serviceResponse = await Request.addRequestServices(requestService);
                    console.log('Service created with ID:', serviceResponse);
                    
                    // Create service locations
                    const serviceLocations = address?.filter(raw => raw.isService == 1);
                    if (serviceResponse && serviceLocations?.length > 0) {
                        const productId = isFromEdit ? (row?.labour_cost_service_id ?? row.id ?? existingServiceId) : row.id || existingServiceId || null;
                        let addResult = serviceLocations.filter(raw => (raw.productId === productId && raw.productIndex === row.indexId));
                        if (addResult && addResult.length > 0) {
                            for (let i = 0; i < addResult.length; i++) {
                                const locationData = {
                                    request_id: requestId,
                                    service_id: serviceResponse,
                                    address: addResult[i].address,
                                    city: addResult[i].city,
                                    state: addResult[i].state,
                                    zip: addResult[i].zip?.trim(),
                                    timestamp: moment().unix(),
                                    added_by: userId
                                };
                                await Request.addServiceLocation(locationData);
                                console.log('Service location created for service:', serviceResponse);
                            }
                        }
                    }
                }
                console.log(`✅ Successfully processed ${filteredServices.length} services for facility ${facilityId}`);
            }
            
            // Step 8: Create Request Update for split order conversion
            const updateData = {
                request_id: requestId,
                partner_id: null,
                partner_name: null,
                activity: `Sales Order "${requestData.name}" converted from ${CURRENT_INSTALLATION} (Split)`,
                added_by: userId,
                timestamp: moment().unix(),
                status: 1
            };
            await RequestUpdatesModel.create(updateData, { transaction });

            // Commit transaction
            await transaction.commit();
                        
            return {
                success: true,
                data: {
                    requestId: requestId,
                    routeId: route.id,
                    orderId: orderId,
                    facilityId: facilityId,
                    installationName: CURRENT_INSTALLATION
                }
            };
            
        } catch (error) {
            console.error('Error adding complete sales order to current installation:', error);
            // Rollback transaction on error
            await transaction.rollback();
            return { success: false, message: error.message };
        }
        
    } catch (error) {
        console.error('Error adding complete sales order to current installation:', error);
        return { success: false, message: error.message };
    }
};

const addMirrorCompleteSalesOrderToOtherInstallation = async (facilityId, requestData, userId, uploaded, values, avalaraTaxAmount, negativeAmount, taxCategory, address, mirrorData) => {

    try {
        console.log("Starting to process mirror sales order for facility ID:", facilityId);
        
        const otherInstallationDatabases = getOtherInstallationDatabases();
        console.log("Fetched other installation databases:", otherInstallationDatabases);
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === facilityId);
        
        if (!targetDatabase) {
            console.log('Target database not found for facility ID:', facilityId);
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        console.log("Target models created:", targetModels);
        
        const currentDate = moment().unix();
        console.log("Current date in Unix timestamp:", currentDate);

        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        console.log("Transaction started.");

        try {
            // Step 1: Create Request in Target Facility
            console.log("Creating request in target facility...");
            const request = await targetModels.Requests.create(requestData, { transaction });
            console.log('Request created:', request);
            const requestId = request.id;

            // Step 2: Create Route in Target Facility
            const routeData = {
                name: `route ${currentDate}`,
                assigned_driver: null,
                timestamp: currentDate,
                added_by: userId,
                status: 1,
                assigned_sales_order: requestId,
                assigned: 0,
                unique_id: 'SWS' + (parseInt(crypto.randomBytes(2).toString('hex'), 16) % 90000 + 10000)
            };
            
            console.log("Creating route in target facility...");
            const route = await targetModels.Routes.create(routeData, { transaction });
            console.log('Route created:', route);

            // Step 3: Create Request Attachments in Target Facility
            if (uploaded && uploaded.length > 0) {
                console.log("Processing uploaded attachments...");
                for (const row of uploaded) {
                    let approvedImage = {};
                    
                    if (requestData?.project_category == 11) {
                        approvedImage.approved = 1;
                    } else if (row?.file_type?.startsWith('image')) {
                        approvedImage.approved = 0;
                    } else {
                        approvedImage.approved = 1;
                    }
                    
                    approvedImage.name = row?.name || row?.url;
                    approvedImage.file_type = row.file_type;
                    approvedImage.extension = row.extension;
                    approvedImage.file_size = row.size || 2;
                    approvedImage.request_id = requestId;
                    approvedImage.added_by = userId;
                    approvedImage.timestamp = moment().unix();
                    
                    console.log("Creating attachment for request:", approvedImage);
                    await targetModels.RequestAttachments.create(approvedImage, { transaction });
                }
            }

            let filteredProducts = values?.products?.filter(product => product.facility_id == facilityId);
            console.log('filteredProducts---234', filteredProducts)
            let totalCost = filteredProducts.reduce((sum, product) => {
                const installQtyValue = (product?.install_quantity !== null && product?.install_quantity !== undefined) 
                ? Number(product.install_quantity) 
                : ((product?.install_qty !== null && product?.install_qty !== undefined) 
                ? Number(product.install_qty) 
                : 1);
                return sum + (Number(product.expected_cost) * Number(product.locationQuantity)) + (Number(product.install_cost || 0) * Number(installQtyValue));
              }, 0);
              
              console.log("totalCost----234", totalCost);
            // const totalAmount = ((parseFloat(parseFloat(totalCost).toFixed(2)) + parseFloat(parseFloat(avalaraTaxAmount).toFixed(2))) * (negativeAmount || 1)).toFixed(2);
            const totalAmount = (parseFloat(parseFloat(totalCost).toFixed(2)) * (1)).toFixed(2);
            const compnyProfile = await Settings.getCompanyProfileDetails()
            let partnerData = values?.clientsData
            console.log('partnerData-----', partnerData)
            const orderData = {
                total: totalAmount,
                grandTotal: totalAmount,
                // tax_amount: avalaraTaxAmount,
                tax_amount: 0,
                tax_category: taxCategory,
                budget: values.budget,
                extra_discount: values.invoice_discount || 0,
                request_id: requestId,
                timestamp: currentDate,
                sub_total: !empty(Number(totalCost)) ? Number(totalCost) : totalAmount,
                tax: 0,
                status: 1,
                customer_name: partnerData[0]['name'] || compnyProfile[0]?.name || null,
                customer_phone: partnerData[0]['phone'] || compnyProfile[0]?.phone || null,
                customer_address: partnerData[0]['address'] ||  `${compnyProfile[0]?.address || ''}${compnyProfile[0]?.city ? ', ' + compnyProfile[0]?.city : ''}${compnyProfile[0]?.state ? ', ' + compnyProfile[0]?.state : ''}${compnyProfile[0]?.zipcode ? ' ' + compnyProfile[0]?.zipcode : ''}` || null,
            };
            console.log('orderData-----234', orderData)
            const customerOrder = await targetModels.CustomerOrders.create(orderData, { transaction });
            const orderId = customerOrder.id;
            
            // Step 6: Create Customer Order Products and Locations
            for (const row of filteredProducts) {
                console.log('row-----234', row)
                const rowQuantity = row.quantity ? row.quantity : 1;
                const rowDiscount = 0;
                const rowAdjustment = row?.adjustment ? row?.adjustment : 0;
                
                // Find existing product by name if no ID provided
                let existingProductId = null;
                if (!row.id) {
                    // Note: You might need to implement findProductByName for target database
                    // For now, we'll use the provided ID or null
                    existingProductId = null;
                }
                const installQtyValue = (row?.install_quantity !== null && row?.install_quantity !== undefined) 
                ? Number(row.install_quantity) 
                : ((row?.install_qty !== null && row?.install_qty !== undefined) 
                ? Number(row.install_qty) 
                : 1)
                const retailPrice = Number(row?.expected_cost) + Number(row?.install_cost || 0) || row?.retail_price;
                const ProductTotal = (Number(row?.expected_cost) * Number(row?.locationQuantity)) + (Number(row?.install_cost || 0) * Number(installQtyValue))
                const requestProduct = {
                    order_id: orderId,
                    product_index: row.indexId,
                    product_id: row.id || existingProductId || null,
                    product_name: row.name,
                    product_notes: row.product_notes || null,
                    install_notes: row.install_notes || null,
                    product_price: row.price || 0.00,
                    product_retail_price: retailPrice,
                    product_markup: row.markup ? row.markup : null,
                    product_qty: rowQuantity,
                    location_quantity: row.locationQuantity || 1,
                    product_sub_total: ProductTotal || 0,
                    product_tax: 0,
                    product_total: ProductTotal|| 0,
                    workflow: row.workflow,
                    discount: rowDiscount,
                    adjustment: rowAdjustment,
                    timestamp: currentDate,
                    status: row.status,
                    markAdjType: 2,
                    map_service: row?.fromMapServices ? 1 : 0,
                    allLocationSame: row?.allLocationSame ? 1 : 0,
                    isAdditionalProduct: row?.isAdditionalProduct ? 1 : 0,
                    is_single_card: row?.is_single_card ? 1 : 0,
                    facility_id: row?.facility_id || 1,
                    split_percentage: row?.split_percentage || 0,
                    incoming_split_percentage: row?.incoming_split_percentage || 0,
                    expected_formula_price: row?.originalExpectedCost || 0,
                    install_cost: row?.install_cost || null,
                    unit_cost: retailPrice || null,
                    delivery_type_id : row?.delivery_installation?.delivery_type_id || row?.delivery_type_id || null,
                    installer_facility_id : row?.delivery_installation?.installer_facility_id || row?.installer_facility_id || null,
                    // install_cost: row?.install_cost || null,
                    install_qty : installQtyValue ?? 1,
                };
                console.log('requestProductrequestProduct234', requestProduct)
                const orderProduct = await targetModels.CustomerOrderProducts.create(requestProduct, { transaction });
                
                // Step 7: Create Locations for this product
                if (address && address.length > 0) {
                    const productAddresses = address.filter(raw => (raw.productId === row.id && raw.productIndex === row.indexId));
                    
                    for (const addr of productAddresses) {
                        const locationData = {
                            order_id: orderProduct.id,
                            product_id: row.id || existingProductId || null,
                            address: addr.address,
                            city: addr.city,
                            state: addr.state,
                            zip: addr.zip?.trim(),
                            route_location_id: addr.route_location_id || null,
                            timestamp: moment().unix(),
                            added_by: userId
                        };
                        
                        await targetModels.RequestLocations.create(locationData, { transaction });
                    }
                }
            }
            // Step 5: Process mirrorData for Products, Services, Materials, Locations
            // if (mirrorData) {
            //     const customerOrderData = {
            //         ...mirrorData.orderData,   // Spread the existing order data into this object
            //         request_id: requestId,     // Add or overwrite the request_id with requestId
            //         customer_name: requestData.customer_name ||  values?.clients?.name ||null,
            //     };
            //     console.log("Creating customer order in target facility...",customerOrderData);
            //     const customerOrder = await targetModels.CustomerOrders.create(customerOrderData, { transaction });
            //     const orderId = customerOrder.id;
            //     console.log('Customer order created:', customerOrder);
            //     console.log("Processing mirror data...");
                
            //     // Step 5.1 Process product data
            // if (mirrorData?.productData?.length > 0) {
            //     console.log("Processing product data...");
            //     for (const row of mirrorData.productData) {
            //         console.log("Creating product in target facility:", row);
            //         const data = {
            //             ...row,
            //             order_id: orderId,
            //         }
            //         console.log('data1234567890', data)
            //         const productResponse = await targetModels.CustomerOrderProducts.create(data, { transaction });
            //         console.log('Product created:', productResponse);

            //         // Create location for each product inside the product loop
            //         const result = address.filter(raw => raw.productId === row.product_id && raw.productIndex === row.product_index);
            //         if (result && result.length > 0) {
            //             for (let i = 0; i < result.length; i++) {
            //                 const data = {
            //                     order_id: productResponse.id,
            //                     product_id: row.product_id || null,
            //                     address: result[i].address,
            //                     city: result[i].city,
            //                     state: result[i].state,
            //                     zip: result[i].zip?.trim(),
            //                     route_location_id: result[i].route_location_id || null,
            //                     timestamp: moment().unix(),
            //                     added_by: userId
            //                 };
            //                 await targetModels.RequestLocations.create(data, { transaction });
            //             }
            //         }
            //     }
            // }

            //     // Step 5.2 Process material data
            // // if (mirrorData?.materialData?.length > 0) {
            // //     console.log("Processing material data...");
            // //     for (const row of mirrorData.materialData) {
            // //         const data = {
            // //             ...row,
            // //             order_id: orderId,
            // //         }
            // //         console.log("Creating material in target facility:", data);
            // //         const materialResponse = await targetModels.CustomerOrderProducts.create(data, { transaction });
            // //         console.log('Material created:', materialResponse);

            // //         // Create location for each material inside the material loop
            // //         const result = address.filter(raw => raw.productId === row.product_id && raw.productIndex === row.product_index);
            // //         if (result && result.length > 0) {
            // //             for (let i = 0; i < result.length; i++) {
            // //                 const data = {
            // //                     order_id: materialResponse.id,
            // //                     product_id: row.product_id || null,
            // //                     address: result[i].address,
            // //                     city: result[i].city,
            // //                     state: result[i].state,
            // //                     zip: result[i].zip?.trim(),
            // //                     route_location_id: result[i].route_location_id || null,
            // //                     timestamp: moment().unix(),
            // //                     added_by: userId
            // //                 };
            // //                 await targetModels.RequestLocations.create(data, { transaction });
            // //             }
            // //         }
            // //     }
            // // }

            // //     // Step 5.3 Process service data
            // // if (mirrorData?.serviceData?.length > 0) {
            // //     console.log("Processing service data...");
            // //     for (const row of mirrorData.serviceData) {
            // //         console.log("Creating service data in target facility:", row);
            // //         const data = {
            // //             ...row,
            // //             request_id: requestId,
            // //         }
            // //         const serviceResponse = await targetModels.RequestServices.create(data, { transaction });
            // //         console.log('Service data created:', serviceResponse);

            // //         // Create location for each service inside the service loop
            // //         const result = address.filter(raw => raw.productId === row.product_id && raw.productIndex === row.product_index);
            // //         if (result && result.length > 0) {
            // //             for (let i = 0; i < result.length; i++) {
            // //                 const data = {
            // //                     request_id: requestId,
            // //                     service_id: serviceResponse.id || null,
            // //                     address: result[i].address,
            // //                     city: result[i].city,
            // //                     state: result[i].state,
            // //                     zip: result[i].zip?.trim(),
            // //                     timestamp: moment().unix(),
            // //                     added_by: userId
            // //                 };
            // //                 await targetModels.RequestServiceLocations.create(data, { transaction });
            // //             }
            // //         }
            // //     }
            // // }
            // }

            // Step 7: Create Request Update for split order conversion
            const updateData = {
                request_id: requestId,
                partner_id: null,
                partner_name: null,
                activity: `Split Sales Order "${requestData.name}" mirrored from ${CURRENT_INSTALLATION} (Split)`,
                added_by: userId,
                timestamp: moment().unix(),
                status: 1
            };
            console.log("Creating request update...");
            await targetModels.RequestUpdates.create(updateData, { transaction });

            // Commit transaction
            console.log("Committing transaction...");
            await transaction.commit();
            
            console.log("Mirror sales order creation completed successfully.");
            
            return {
                success: true,
                data: {
                    requestId: requestId,
                    routeId: route.id,
                    facilityId: facilityId
                }
            };
            
        } catch (error) {
            console.error('Error while copying data:', error);
            // Rollback transaction on error
            await transaction.rollback();
            return { success: false, message: error.message };
        }
        
    } catch (error) {
        console.error('Error adding complete sales order to other installation:', error);
        return { success: false, message: error.message };
    }
};

const updateMirrorCompleteSalesOrderToOtherInstallation = async (facilityId, requestData, userId, uploaded, values, avalaraTaxAmount, negativeAmount, taxCategory, address, mirrorData, request_id) => {
console.log('requestdataaaaaaaaaaaaaaaaaaa', requestData)

    try {    
        const otherInstallationDatabases = getOtherInstallationDatabases();
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === facilityId);
        
        if (!targetDatabase) {
            console.log('Target database not found for facility ID:', facilityId);
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        const currentDate = moment().unix();
        console.log("Current date in Unix timestamp:", currentDate);

        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        console.log("Transaction started.");

        try {
            // Step 1: Create Request in Target Facility
            const existingRequest = await targetModels.Requests.findOne({
                where: { origin_facility_request_id: request_id,for_parent_facility: 2 }, // Match using origin_facility_request_id
            });
            console.log('existingRequest1234567890', existingRequest)
            let requestId = null;
            if (existingRequest) {
                // If the request exists, update it
                await existingRequest.update(requestData, { transaction });
                console.log('Request updated:', existingRequest);
                 requestId = existingRequest?.dataValues?.id;;  // Use the existing request's ID
            // Find the existing route where assigned_sales_order is equal to requestId
            const existingRoute = await targetModels.Routes.findOne({
                where: { assigned_sales_order: requestId }, // Match using assigned_sales_order
            });

            if (existingRoute) {
                // If the route exists, update it
                console.log('Found existing route, updating...');
                await existingRoute.update({
                    name: `route ${currentDate}`,
                    assigned_driver: null,
                    timestamp: currentDate,
                    added_by: userId,
                    status: 1,
                    assigned: 0,
                    unique_id: 'SWS' + (parseInt(crypto.randomBytes(2).toString('hex'), 16) % 90000 + 10000)
                }, { transaction });
                console.log('Route updated:', existingRoute);
            } else {
                // If the route does not exist, create a new one
                console.log('No existing route found, creating a new route...');
                const route = await targetModels.Routes.create({
                    name: `route ${currentDate}`,
                    assigned_driver: null,
                    timestamp: currentDate,
                    added_by: userId,
                    status: 1,
                    assigned_sales_order: requestId,
                    assigned: 0,
                    unique_id: 'SWS' + (parseInt(crypto.randomBytes(2).toString('hex'), 16) % 90000 + 10000)
                }, { transaction });
                console.log('Route created:', route);
            }

            // Step 3: Create Request Attachments in Target Facility
            if (uploaded && uploaded.length > 0) {
                console.log("Processing uploaded attachments...");

                // Check if there are existing attachments with the same request_id and delete them
                const existingAttachments = await targetModels.RequestAttachments.findAll({
                    where: { request_id: requestId }
                });

                if (existingAttachments.length > 0) {
                    console.log(`Found existing attachments for request_id: ${requestId}, deleting...`);
                    await targetModels.RequestAttachments.destroy({
                        where: { request_id: requestId },
                        transaction
                    });
                    console.log(`Deleted ${existingAttachments.length} attachments.`);
                }

                // Now insert the new attachments
                for (const row of uploaded) {
                    let approvedImage = {};

                    // Set the approved status based on conditions
                    if (requestData?.project_category == 11) {
                        approvedImage.approved = 1;
                    } else if (row?.file_type?.startsWith('image')) {
                        approvedImage.approved = 0;
                    } else {
                        approvedImage.approved = 1;
                    }

                    approvedImage.name = row?.name || row?.url;
                    approvedImage.file_type = row.file_type;
                    approvedImage.extension = row.extension;
                    approvedImage.file_size = row.size || 2;
                    approvedImage.request_id = requestId;
                    approvedImage.added_by = userId;
                    approvedImage.timestamp = moment().unix();

                    console.log("Creating attachment for request:", approvedImage);
                    await targetModels.RequestAttachments.create(approvedImage, { transaction });
                }
            }
            
            // Step 5: Process mirrorData for Products, Services, Materials, Locations
            if (mirrorData) {
                const customerOrderData = {
                    ...mirrorData.orderData,
                    request_id: requestId, 
                    customer_name: requestData.customer_name ||  values?.clients?.name || null,
                };
            
                console.log("Processing mirror data...",customerOrderData);
            
                // Step 1: Check if a customer order already exists for the given request_id and update if found
                const existingCustomerOrder = await targetModels.CustomerOrders.findOne({
                    where: { request_id: requestId } 
                });
            
                if (existingCustomerOrder) {
                    await existingCustomerOrder.update(customerOrderData, { transaction });
                    console.log('Customer order updated:', existingCustomerOrder);
                }
                const orderId = existingCustomerOrder ? existingCustomerOrder.id : null;  // If updated, use the existing order's ID
                console.log('Using order ID:', orderId);

                const deletedRequestLocations = await targetDatabase.connection.query(
                    `DELETE rl
                    FROM request_locations rl
                    LEFT JOIN customer_order_products cop
                        ON rl.order_id = cop.id
                    LEFT JOIN customer_orders co
                        ON cop.order_id = co.id
                    WHERE co.id = :orderId`, 
                    {
                        replacements: { orderId },
                        transaction
                    }
                );
                console.log("Unmatched request_locations deleted for order_id:", orderId);
                // Step 1: Delete existing products for the given orderId
                const deletedProducts = await targetModels.CustomerOrderProducts.destroy({
                    where: { order_id: orderId }, // Delete products where order_id matches the existing orderId
                    transaction
                });
                const deletedServices = await targetModels.RequestServices.destroy({
                    where: { request_id: requestId }, // Delete services where request_id matches the existing requestId
                    transaction
                });
                    // Step 1: Delete existing service locations for the given requestId
                const deletedServiceLocations = await targetModels.RequestServiceLocations.destroy({
                    where: { request_id: requestId }, // Delete locations where request_id matches the existing requestId
                    transaction
                });
                console.log('Existing customer order products deleted.');
                // Step 5.1 Process product data
                if (mirrorData?.productData?.length > 0 ) {
                    console.log("Processing product data...");
                    // Step 2: Add new products
                    for (const row of mirrorData.productData) {
                        console.log("Creating product in target facility:", row);
                        const data = {
                            ...row,
                            order_id: orderId,
                        };
                
                        const productResponse = await targetModels.CustomerOrderProducts.create(data, { transaction });
                        console.log('Product created:', productResponse);  
                        // Step 3: Create location for each product inside the product loop
                        const result = address.filter(raw => raw.productId === row.product_id && raw.productIndex === row.product_index);
                        if (result && result.length > 0) {
                            for (let i = 0; i < result.length; i++) {
                                const locationData = {
                                    order_id: productResponse.id,
                                    product_id: row.product_id || null,
                                    address: result[i].address,
                                    city: result[i].city,
                                    state: result[i].state,
                                    zip: result[i].zip?.trim(),
                                    route_location_id: result[i].route_location_id || null,
                                    timestamp: moment().unix(),
                                    added_by: userId
                                };
                                await targetModels.RequestLocations.create(locationData, { transaction });
                            }
                        }
                    }
                }
                
                    // Step 5.2 Process material data
                if (mirrorData?.materialData?.length > 0) {
                    console.log("Processing material data...");
                    for (const row of mirrorData.materialData) {
                        const data = {
                            ...row,
                            order_id: orderId,
                        }
                        console.log("Creating material in target facility:", data);
                        const materialResponse = await targetModels.CustomerOrderProducts.create(data, { transaction });
                        console.log('Material created:', materialResponse);

                        // Create location for each material inside the material loop
                        const result = address.filter(raw => raw.productId === row.product_id && raw.productIndex === row.product_index);
                        if (result && result.length > 0) {
                            for (let i = 0; i < result.length; i++) {
                                const data = {
                                    order_id: materialResponse.id,
                                    product_id: row.product_id || null,
                                    address: result[i].address,
                                    city: result[i].city,
                                    state: result[i].state,
                                    zip: result[i].zip?.trim(),
                                    route_location_id: result[i].route_location_id || null,
                                    timestamp: moment().unix(),
                                    added_by: userId
                                };
                                await targetModels.RequestLocations.create(data, { transaction });
                            }
                        }
                    }
                }

                    // Step 5.3 Process service data
                if (mirrorData?.serviceData?.length > 0) {
                console.log("Processing update mirror service data...",mirrorData.serviceData);
                for (const row of mirrorData.serviceData) {
                    console.log("mirror Creating service data in target facility:", row);
                    const data = {
                        ...row,
                        request_id: requestId,
                    }
                    console.log('data1234567890', data)
                    const serviceResponse = await targetModels.RequestServices.create(data, { transaction });
                    console.log('updated Service data created:', serviceResponse);

                    // Create location for each service inside the service loop
                    const result = address.filter(raw => raw.productId === row.product_id && raw.productIndex === row.product_index);
                    if (result && result.length > 0) {
                        for (let i = 0; i < result.length; i++) {
                            const data = {
                                request_id: requestId,
                                service_id: serviceResponse.id || null,
                                address: result[i].address,
                                city: result[i].city,
                                state: result[i].state,
                                zip: result[i].zip?.trim(),
                                timestamp: moment().unix(),
                                added_by: userId
                            };
                            await targetModels.RequestServiceLocations.create(data, { transaction });
                        }
                    }
                }
            }
            }

            // Step 7: Create Request Update for split order conversion
            const updateData = {
                request_id: requestId,
                partner_id: null,
                partner_name: null,
                activity: `Split Sales Order "${requestData.name}" updated from ${CURRENT_INSTALLATION} (Split)`,
                added_by: userId,
                timestamp: moment().unix(),
                status: 1
            };
            console.log("Creating request update...");
            await targetModels.RequestUpdates.create(updateData, { transaction });
         }
            // Commit transaction
            console.log("Committing transaction...");
            await transaction.commit();
            
            console.log("Mirror sales order creation completed successfully.");
            
            return {
                success: true,
                data: {
                    requestId: requestId,
                    facilityId: facilityId
                }
            };
            
        } catch (error) {
            console.error('Error while copying data:', error);
            // Rollback transaction on error
            await transaction.rollback();
            return { success: false, message: error.message };
        }
        
    } catch (error) {
        console.error('Error adding complete sales order to other installation:', error);
        return { success: false, message: error.message };
    }
};

const findExistingMirrorSalesOrder = async (request_id, facilityId) => {
    try {    
        const otherInstallationDatabases = getOtherInstallationDatabases();
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === facilityId);
        
        if (!targetDatabase) {
            console.log('Target database not found for facility ID:', facilityId);
            return null; // Return null if target database not found
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Step 1: Create Request in Target Facility
        const existingRequest = await targetModels.Requests.findOne({
            where: { origin_facility_request_id: request_id, for_parent_facility: 2 }, // Match using origin_facility_request_id
        });
        return existingRequest ? true : false; // Return the found request or null

    } catch (error) {
        console.error('Error finding existing mirror sales order:', error);
        return null; // Return null on error
    }
};

const getRequestByMirrorBuildOrder = async (request_id, facilityId) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        if (!targetDatabase) return null;
        
        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        // Find the Build Order in the Build Facility's database
        const existingRequest = await targetModels.Requests.findOne({
            where: { id: request_id },
        });
        return existingRequest;
    }
    catch (error) {
        console.error('Error getting request by mirror build order:', error);
        return null;
    }
};

const deleteCompleteSalesOrderFromOtherInstallation = async (facilityId, requestId) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        
        try {
            // Step 1: Find existing request by origin_facility_request_id
            const existingRequest = await targetModels.Requests.findOne({
                where: { origin_facility_request_id: requestId,for_parent_facility: 1 },
                transaction
            });
            
            if (!existingRequest) {
                await transaction.rollback();
                return { success: false, message: 'Original request not found in target installation' };
            }
            
            const targetRequestId = existingRequest.id;
            
            // NEW: Void Avalara transaction before deletion if it exists
            if (existingRequest.avalara_code) {
                try {
                    console.log(`🗑️ [BUILD ORDER DELETE] Voiding Avalara transaction ${existingRequest.avalara_code} for facility ${facilityId}`);
                    const voidResult = await voidAvalaraTransactionForBuildOrder(existingRequest.avalara_code);
                    if (voidResult.success) {
                        console.log(`✅ [BUILD ORDER DELETE] Successfully voided Avalara transaction for facility ${facilityId}`);
                    } else {
                        console.warn(`⚠️ [BUILD ORDER DELETE] Failed to void Avalara transaction for facility ${facilityId}:`, voidResult.message);
                        // Continue with deletion even if Avalara void fails
                    }
                } catch (avalaraError) {
                    console.warn(`⚠️ [BUILD ORDER DELETE] Error voiding Avalara transaction for facility ${facilityId}:`, avalaraError.message);
                    // Continue with deletion even if Avalara void fails
                }
            }
            
            // Step 2: Find existing customer order
            const existingOrder = await targetModels.CustomerOrders.findOne({
                where: { request_id: targetRequestId },
                transaction
            });
            
            if (!existingOrder) {
                await transaction.rollback();
                return { success: false, message: 'Customer order not found in target installation' };
            }
            
            const orderId = existingOrder.id;
            
            // Step 3: Delete all related data in correct order (respecting foreign key constraints)
            
            // Delete locations first (they reference customer order products)
            await targetModels.RequestLocations.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order products
            await targetModels.CustomerOrderProducts.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order
            await targetModels.CustomerOrders.destroy({
                where: { id: orderId },
                transaction
            });
            
            // Delete request attachments
            await targetModels.RequestAttachments.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Delete routes associated with this request
            await targetModels.Routes.destroy({
                where: { assigned_sales_order: targetRequestId },
                transaction
            });
            
            // Delete projects associated with this request
            await targetModels.Projects.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Finally, delete the request itself
            await targetModels.Requests.destroy({
                where: { id: targetRequestId },
                transaction
            });

            await targetModels.RequestUpdates.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Commit transaction
            await transaction.commit();
            
            return {
                success: true,
                data: {
                    requestId: targetRequestId,
                    orderId: orderId,
                    facilityId: facilityId,
                    installationName: targetDatabase.name
                }
            };
            
        } catch (error) {
            console.error('Error deleting complete sales order from other installation:', error);
            // Rollback transaction on error
            await transaction.rollback();
           return error
        }
        
    } catch (error) {
        console.error('Error deleting complete sales order from other installation:', error);
        return { success: false, message: error.message };
    }
};

const BuildOrderFromOtherInstallationsDelete = async (facilityId, requestId ,deleteMirror = false) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        
        try {
            // Step 1: Find existing request by origin_facility_request_id
            let existingRequest = null;
            if(deleteMirror){
             existingRequest = await targetModels.Requests.findOne({
                where: { origin_facility_request_id: requestId,for_parent_facility: 2 },
                transaction
            });
            
            }else{
             existingRequest = await targetModels.Requests.findOne({
                where: { id: requestId, },
                transaction
            });
            }
            if (!existingRequest) {
                await transaction.rollback();
                return { success: false, message: 'Original request not found in target installation' };
            }
            console.log('existingRequest---', existingRequest);
            const targetRequestId = existingRequest.id;
            
            // NEW: Void Avalara transaction before deletion if it exists
            if (existingRequest.avalara_code) {
                try {
                    console.log(`🗑️ [BUILD ORDER DELETE] Voiding Avalara transaction ${existingRequest.avalara_code} for facility ${facilityId}`);
                    const voidResult = await voidAvalaraTransactionForBuildOrder(existingRequest.avalara_code);
                    if (voidResult.success) {
                        console.log(`✅ [BUILD ORDER DELETE] Successfully voided Avalara transaction for facility ${facilityId}`);
                    } else {
                        console.warn(`⚠️ [BUILD ORDER DELETE] Failed to void Avalara transaction for facility ${facilityId}:`, voidResult.message);
                        // Continue with deletion even if Avalara void fails
                    }
                } catch (avalaraError) {
                    console.warn(`⚠️ [BUILD ORDER DELETE] Error voiding Avalara transaction for facility ${facilityId}:`, avalaraError.message);
                    // Continue with deletion even if Avalara void fails
                }
            }
            
            // Step 2: Find existing customer order
            const existingOrder = await targetModels.CustomerOrders.findOne({
                where: { request_id: targetRequestId },
                transaction
            });
            
            if (!existingOrder) {
                await transaction.rollback();
                return { success: false, message: 'Customer order not found in target installation' };
            }
            
            const orderId = existingOrder.id;
            
            // Step 3: Delete all related data in correct order (respecting foreign key constraints)
            
            // Delete locations first (they reference customer order products)
            await targetModels.RequestLocations.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order products
            await targetModels.CustomerOrderProducts.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order
            await targetModels.CustomerOrders.destroy({
                where: { id: orderId },
                transaction
            });
            
            // Delete request attachments
            await targetModels.RequestAttachments.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Delete routes associated with this request
            await targetModels.Routes.destroy({
                where: { assigned_sales_order: targetRequestId },
                transaction
            });
            
            // Delete projects associated with this request
            await targetModels.Projects.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Finally, delete the request itself
            await targetModels.Requests.destroy({
                where: { id: targetRequestId },
                transaction
            });

            await targetModels.RequestUpdates.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Commit transaction
            await transaction.commit();
            
            return {
                success: true,
                data: {
                    requestId: targetRequestId,
                    orderId: orderId,
                    facilityId: facilityId,
                    installationName: targetDatabase.name
                }
            };
            
        } catch (error) {
            console.error('Error deleting complete sales order from other installation:', error);
            // Rollback transaction on error
            await transaction.rollback();
           return error
        }
        
    } catch (error) {
        console.error('Error deleting complete sales order from other installation:', error);
        return { success: false, message: error.message };
    }
};

const BuildOrderFromOtherInstallationsDeleteMirror = async (facilityId, originBuildOrderId) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Start transaction on TARGET installation DB
        const transaction = await targetDatabase.connection.transaction();

        try {
            // Step 1: Find mirror request in target installation
            const existingRequest = await targetModels.Requests.findOne({
                where: {
                    origin_facility_id: FACILITY_ID,
                    origin_facility_request_id: originBuildOrderId,
                    for_parent_facility: 2,
                },
                transaction
            });

            if (!existingRequest) {
                await transaction.rollback();
                return {
                    success: false,
                    message: `Mirror request not found in target installation for origin_facility_id=${FACILITY_ID}, origin_facility_request_id=${originBuildOrderId}`
                };
            }

            const currentRequestId = existingRequest.id;

            // Step 2: Find existing customer order (if any)
            const existingOrder = await targetModels.CustomerOrders.findOne({
                where: { request_id: currentRequestId },
                transaction
            });

            // Step 3: Delete related data in correct order
            if (existingOrder) {
                const orderId = existingOrder.id;

                // request_locations.order_id points to customer_order_products.id (NOT customer_orders.id)
                const orderProducts = await targetModels.CustomerOrderProducts.findAll({
                    where: { order_id: orderId },
                    attributes: ['id'],
                    transaction
                });

                const productIds = orderProducts.map(p => p.id);

                if (productIds.length > 0) {
                    await targetModels.RequestLocations.destroy({
                        where: { order_id: { [Sequelize.Op.in]: productIds } },
                        transaction
                    });
                }

                await targetModels.CustomerOrderProducts.destroy({
                    where: { order_id: orderId },
                    transaction
                });

                await targetModels.CustomerOrders.destroy({
                    where: { id: orderId },
                    transaction
                });
            }

            await targetModels.RequestAttachments.destroy({
                where: { request_id: currentRequestId },
                transaction
            });

            await targetModels.Routes.destroy({
                where: { assigned_sales_order: currentRequestId },
                transaction
            });

            // Projects are created elsewhere for a request_id; delete defensively if present
            await targetDatabase.connection.query(
                `DELETE FROM projects WHERE request_id = :requestId`,
                { replacements: { requestId: currentRequestId }, transaction }
            );

            await targetModels.RequestUpdates.destroy({
                where: { request_id: currentRequestId },
                transaction
            });

            await targetModels.Requests.destroy({
                where: { id: currentRequestId },
                transaction
            });

            await transaction.commit();

            return {
                success: true,
                data: {
                    requestId: currentRequestId,
                    origin_facility_id: FACILITY_ID,
                    origin_facility_request_id: originBuildOrderId,
                    installationName: targetDatabase.name
                }
            };
        } catch (error) {
            console.error('Error deleting mirror sales order from target installation:', error);
            await transaction.rollback();
            return { success: false, message: error.message };
        }
    } catch (error) {
        console.error('Error deleting mirror sales order from target installation:', error);
        return { success: false, message: error.message };
    }
};

const mirrorBuildOrderFromCurrentInstallationDelete = async (facilityId, requestId ,deleteMirror = false) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        
        try {
            // Step 1: Find existing request by origin_facility_request_id
            let existingRequest = null;
            if(deleteMirror){
             existingRequest = await targetModels.Requests.findOne({
                where: { origin_facility_request_id: requestId,for_parent_facility: 2 },
                transaction
            });
            
            }else{
             existingRequest = await targetModels.Requests.findOne({
                where: { id: requestId, },
                transaction
            });
            }
            if (!existingRequest) {
                await transaction.rollback();
                return { success: false, message: 'Original request not found in target installation' };
            }
            console.log('existingRequest---', existingRequest);
            const targetRequestId = existingRequest.id;
            
            // NEW: Void Avalara transaction before deletion if it exists
            if (existingRequest.avalara_code) {
                try {
                    console.log(`🗑️ [BUILD ORDER DELETE] Voiding Avalara transaction ${existingRequest.avalara_code} for facility ${facilityId}`);
                    const voidResult = await voidAvalaraTransactionForBuildOrder(existingRequest.avalara_code);
                    if (voidResult.success) {
                        console.log(`✅ [BUILD ORDER DELETE] Successfully voided Avalara transaction for facility ${facilityId}`);
                    } else {
                        console.warn(`⚠️ [BUILD ORDER DELETE] Failed to void Avalara transaction for facility ${facilityId}:`, voidResult.message);
                        // Continue with deletion even if Avalara void fails
                    }
                } catch (avalaraError) {
                    console.warn(`⚠️ [BUILD ORDER DELETE] Error voiding Avalara transaction for facility ${facilityId}:`, avalaraError.message);
                    // Continue with deletion even if Avalara void fails
                }
            }
            
            // Step 2: Find existing customer order
            const existingOrder = await targetModels.CustomerOrders.findOne({
                where: { request_id: targetRequestId },
                transaction
            });
            
            if (!existingOrder) {
                await transaction.rollback();
                return { success: false, message: 'Customer order not found in target installation' };
            }
            
            const orderId = existingOrder.id;
            
            // Step 3: Delete all related data in correct order (respecting foreign key constraints)
            
            // Delete locations first (they reference customer order products)
            await targetModels.RequestLocations.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order products
            await targetModels.CustomerOrderProducts.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order
            await targetModels.CustomerOrders.destroy({
                where: { id: orderId },
                transaction
            });
            
            // Delete request attachments
            await targetModels.RequestAttachments.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Delete routes associated with this request
            await targetModels.Routes.destroy({
                where: { assigned_sales_order: targetRequestId },
                transaction
            });
            
            // Delete projects associated with this request
            await targetModels.Projects.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Finally, delete the request itself
            await targetModels.Requests.destroy({
                where: { id: targetRequestId },
                transaction
            });

            await targetModels.RequestUpdates.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Commit transaction
            await transaction.commit();
            
            return {
                success: true,
                data: {
                    requestId: targetRequestId,
                    orderId: orderId,
                    facilityId: facilityId,
                    installationName: targetDatabase.name
                }
            };
            
        } catch (error) {
            console.error('Error deleting complete sales order from other installation:', error);
            // Rollback transaction on error
            await transaction.rollback();
           return error
        }
        
    } catch (error) {
        console.error('Error deleting complete sales order from other installation:', error);
        return { success: false, message: error.message };
    }
};

const mirrorBuildOrderFromOtherInstallationsDelete = async (facilityId, originBuildOrderId) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Start transaction on TARGET installation DB
        const transaction = await targetDatabase.connection.transaction();

        try {
            // Step 1: Find mirror request in target installation
            const existingRequest = await targetModels.Requests.findOne({
                where: {
                    origin_facility_id: FACILITY_ID,
                    origin_facility_request_id: originBuildOrderId,
                    for_parent_facility: 2,
                },
                transaction
            });

            if (!existingRequest) {
                await transaction.rollback();
                return {
                    success: false,
                    message: `Mirror request not found in target installation for origin_facility_id=${FACILITY_ID}, origin_facility_request_id=${originBuildOrderId}`
                };
            }

            const currentRequestId = existingRequest.id;

            // Step 2: Find existing customer order (if any)
            const existingOrder = await targetModels.CustomerOrders.findOne({
                where: { request_id: currentRequestId },
                transaction
            });

            // Step 3: Delete related data in correct order
            if (existingOrder) {
                const orderId = existingOrder.id;

                // request_locations.order_id points to customer_order_products.id (NOT customer_orders.id)
                const orderProducts = await targetModels.CustomerOrderProducts.findAll({
                    where: { order_id: orderId },
                    attributes: ['id'],
                    transaction
                });

                const productIds = orderProducts.map(p => p.id);

                if (productIds.length > 0) {
                    await targetModels.RequestLocations.destroy({
                        where: { order_id: { [Sequelize.Op.in]: productIds } },
                        transaction
                    });
                }

                await targetModels.CustomerOrderProducts.destroy({
                    where: { order_id: orderId },
                    transaction
                });

                await targetModels.CustomerOrders.destroy({
                    where: { id: orderId },
                    transaction
                });
            }

            await targetModels.RequestAttachments.destroy({
                where: { request_id: currentRequestId },
                transaction
            });

            await targetModels.Routes.destroy({
                where: { assigned_sales_order: currentRequestId },
                transaction
            });

            // Projects are created elsewhere for a request_id; delete defensively if present
            await targetDatabase.connection.query(
                `DELETE FROM projects WHERE request_id = :requestId`,
                { replacements: { requestId: currentRequestId }, transaction }
            );

            await targetModels.RequestUpdates.destroy({
                where: { request_id: currentRequestId },
                transaction
            });

            await targetModels.Requests.destroy({
                where: { id: currentRequestId },
                transaction
            });

            await transaction.commit();

            return {
                success: true,
                data: {
                    requestId: currentRequestId,
                    origin_facility_id: FACILITY_ID,
                    origin_facility_request_id: originBuildOrderId,
                    installationName: targetDatabase.name
                }
            };
        } catch (error) {
            console.error('Error deleting mirror sales order from target installation:', error);
            await transaction.rollback();
            return { success: false, message: error.message };
        }
    } catch (error) {
        console.error('Error deleting mirror sales order from target installation:', error);
        return { success: false, message: error.message };
    }
};
// Delete the "mirror" build order that was created in the CURRENT installation
// Linkage is created in SplitManagementController via:
//   origin_facility_id = facilityId
//   origin_facility_request_id = <otherInstallationRequestId>
//   for_parent_facility = 2
const deleteCompleteSalesOrderFromCurrentInstallation = async (facilityId, originFacilityRequestId) => {
    try {
        // Start transaction on CURRENT installation DB
        const transaction = await sequelize.transaction();

        try {
            // Step 1: Find mirror request in current installation
            const existingRequest = await RequestsModel.findOne({
                where: {
                    origin_facility_id: facilityId,
                    origin_facility_request_id: originFacilityRequestId,
                    for_parent_facility: 2,
                },
                transaction
            });

            if (!existingRequest) {
                await transaction.rollback();
                return {
                    success: false,
                    message: `Mirror request not found in current installation for origin_facility_id=${facilityId}, origin_facility_request_id=${originFacilityRequestId}`
                };
            }

            const currentRequestId = existingRequest.id;

            // Step 2: Find existing customer order (if any)
            const existingOrder = await CustomerOrdersModel.findOne({
                where: { request_id: currentRequestId },
                transaction
            });

            // Step 3: Delete related data in correct order
            if (existingOrder) {
                const orderId = existingOrder.id;

                // request_locations.order_id points to customer_order_products.id (NOT customer_orders.id)
                const orderProducts = await CustomerOrderProductsModel.findAll({
                    where: { order_id: orderId },
                    attributes: ['id'],
                    transaction
                });

                const productIds = orderProducts.map(p => p.id);

                if (productIds.length > 0) {
                    await RequestLocationsModel.destroy({
                        where: { order_id: { [Sequelize.Op.in]: productIds } },
                        transaction
                    });
                }

                await CustomerOrderProductsModel.destroy({
                    where: { order_id: orderId },
                    transaction
                });

                await CustomerOrdersModel.destroy({
                    where: { id: orderId },
                    transaction
                });
            }

            await RequestAttachmentsModel.destroy({
                where: { request_id: currentRequestId },
                transaction
            });

            await RoutesModel.destroy({
                where: { assigned_sales_order: currentRequestId },
                transaction
            });

            // Projects are created elsewhere for a request_id; delete defensively if present
            await sequelize.query(
                `DELETE FROM projects WHERE request_id = :requestId`,
                { replacements: { requestId: currentRequestId }, transaction }
            );

            await RequestUpdatesModel.destroy({
                where: { request_id: currentRequestId },
                transaction
            });

            await RequestsModel.destroy({
                where: { id: currentRequestId },
                transaction
            });

            await transaction.commit();

            return {
                success: true,
                data: {
                    requestId: currentRequestId,
                    origin_facility_id: facilityId,
                    origin_facility_request_id: originFacilityRequestId,
                    installationName: CURRENT_INSTALLATION
                }
            };
        } catch (error) {
            console.error('Error deleting mirror sales order from current installation:', error);
            await transaction.rollback();
            return { success: false, message: error.message };
        }
    } catch (error) {
        console.error('Error deleting mirror sales order from current installation:', error);
        return { success: false, message: error.message };
    }
};

const deleteCompleteMirrorSalesOrderFromOtherInstallation = async (facilityId, requestId) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        
        try {
            // Step 1: Find existing request by origin_facility_request_id
            const existingRequest = await targetModels.Requests.findOne({
                where: { origin_facility_request_id: requestId,for_parent_facility: 2 },
                transaction
            });
            
            if (!existingRequest) {
                await transaction.rollback();
                return { success: false, message: 'Original request not found in target installation' };
            }
            
            const targetRequestId = existingRequest.id;
            
            // Step 2: Find existing customer order
            const existingOrder = await targetModels.CustomerOrders.findOne({
                where: { request_id: targetRequestId },
                transaction
            });
            
            if (!existingOrder) {
                await transaction.rollback();
                return { success: false, message: 'Customer order not found in target installation' };
            }
            
            const orderId = existingOrder.id;
            
            // Step 3: Delete all related data in correct order (respecting foreign key constraints)
            
            // Delete locations first (they reference customer order products)
            await targetModels.RequestLocations.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order products
            await targetModels.CustomerOrderProducts.destroy({
                where: { order_id: orderId },
                transaction
            });
            
            // Delete customer order
            await targetModels.CustomerOrders.destroy({
                where: { id: orderId },
                transaction
            });
            
            // Delete request attachments
            await targetModels.RequestAttachments.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Delete routes associated with this request
            await targetModels.Routes.destroy({
                where: { assigned_sales_order: targetRequestId },
                transaction
            });
            
            // Delete projects associated with this request
            await targetModels.Projects.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Finally, delete the request itself
            await targetModels.Requests.destroy({
                where: { id: targetRequestId },
                transaction
            });

            await targetModels.RequestUpdates.destroy({
                where: { request_id: targetRequestId },
                transaction
            });
            
            // Commit transaction
            await transaction.commit();
            
            return {
                success: true,
                data: {
                    requestId: targetRequestId,
                    orderId: orderId,
                    facilityId: facilityId,
                    installationName: targetDatabase.name
                }
            };
            
        } catch (error) {
            console.error('Error deleting complete mirror sales order from other installation:', error);
            // Rollback transaction on error
            await transaction.rollback();
           return error
        }
        
    } catch (error) {
        console.error('Error deleting complete mirror sales order from other installation:', error);
        return { success: false, message: error.message };
    }
};

const updateOtherfacilityApproveStatus = async (requestId,originFacilityRequestId,originFacilityId, targettedFacilityId, data) => {
    try {
        console.log("Starting to update other facility approve status for facility ID:", originFacilityId);
        
        const otherInstallationDatabases = getOtherInstallationDatabases();
        console.log("Fetched other installation databases:", otherInstallationDatabases);
        
        const targetDatabase = otherInstallationDatabases.find(db => db.id === originFacilityId);
        
        if (!targetDatabase) {
            console.log('Target database not found for facility ID:', originFacilityId);
            return { success: false, message: 'Target database not found for facility ID: ' + originFacilityId };
        }

        // Filter out the originFacilityId from the targettedFacilityId array
        const filteredTargetFacilities = targettedFacilityId.filter(
            id => Number(id) !== Number(originFacilityId)
          );

        let allTargetFacilitiesApproved = true;
                const approvedFacilities = [CURRENT_INSTALLATION.toUpperCase()]; // Include origin facility in approved from the start
        const pendingFacilities = [];

        for (const facilityId of filteredTargetFacilities) {
            const currentTargetDatabase = otherInstallationDatabases.find(db => db.id === parseInt(facilityId, 10));
            if (!currentTargetDatabase) {
                console.error(`Target database not found for facility ID: ${facilityId}`);
                allTargetFacilitiesApproved = false;
                continue; // Skip this facility but mark overall as not approved
            }

            const currentTargetModels = createTargetDatabaseModels(currentTargetDatabase.connection);
            const currentTargetRequests = currentTargetModels.Requests;

            // Find requests in the target facility that are mirrored from the origin facility
            const mirroredRequests = await currentTargetRequests.findAll({
                where: {
                    origin_facility_request_id: originFacilityRequestId, // Assuming requestId refers to the original request ID in the origin facility
                    for_parent_facility: 1 // Assuming this condition is still relevant for mirrored requests
                }
            });

            // Check if all mirrored requests in this facility have status === 3
            const allRequestsInFacilityApproved = mirroredRequests.length > 0 && mirroredRequests.every(req => req.status === 3);

            if (allRequestsInFacilityApproved) {
                approvedFacilities.push(currentTargetDatabase.name.toUpperCase());
            } else {
                pendingFacilities.push(currentTargetDatabase.name.toUpperCase());
                allTargetFacilitiesApproved = false;
            }
        }
        const originModels = createTargetDatabaseModels(targetDatabase.connection);
        const OriginRequests = originModels.Requests;
        try {
            console.log('requestId', requestId)
            const updateRequest = await OriginRequests.update(
                { split_mirror_status: 0,status: 3 },
                {
                    where: {
                        origin_facility_request_id: requestId, // Assuming requestId refers to the original request ID in the origin facility
                        for_parent_facility: 2
                    }
                }
            );
            console.log('updateRequestttttttttttttt 1234', updateRequest)

        } catch (error) {
            console.log('error updating mirror order status', error)
        }

        let mirrorApproveStatusMessage;
        if (allTargetFacilitiesApproved) {
            mirrorApproveStatusMessage = "All mirrored facilities have approved.";
        } else {
            const approvedNames = approvedFacilities.length > 0 ? approvedFacilities.join(", ") + "is approved." : "";
            const pendingNames = pendingFacilities.length > 0 ? pendingFacilities.join(", ") + " needs approval." : "";
            mirrorApproveStatusMessage = `${approvedNames} ${pendingNames}`.trim();
        }


        

                if (allTargetFacilitiesApproved) {
            const [affectedRows] = await OriginRequests.update(
                { ...data, mirror_approve_status: null },
                { where: { id: originFacilityRequestId } }
            );

            for (const facilityId of filteredTargetFacilities) {
                const currentTargetDatabase = otherInstallationDatabases.find(db => db.id === parseInt(facilityId, 10));
            if (!currentTargetDatabase) {
                console.error(`Target database not found for facility ID: ${facilityId}`);
                allTargetFacilitiesApproved = false;
                continue; // Skip this facility but mark overall as not approved
            }

            const currentTargetModels = createTargetDatabaseModels(currentTargetDatabase.connection);
            const currentTargetRequests = currentTargetModels.Requests;
                // const updateRequest = await OriginRequests.update(
                //     { split_mirror_status: 0 },
                //     { where: { origin_facility_request_id: requestId, // Assuming requestId refers to the original request ID in the origin facility
                //         for_parent_facility: 2  } }
                //     );
                    // console.log('updateRequestttttttttttttt', updateRequest)
            }

            return { success: true, affectedRows , approved:true };
        } else {
            await OriginRequests.update(
                { mirror_approve_status: mirrorApproveStatusMessage },
                { where: { id: originFacilityRequestId } }
            );
            console.log("Not all targeted facilities have approved their mirrored requests. Status message updated.");
            return { success: true, message: "Not all targeted facilities have approved their mirrored requests. Status message updated." };
        }
    } catch (error) {
        console.error('Error updating other facility approve status:', error);
        return { success: false, message: error.message };
    }
};

const updateOtherfacilityRejectStatus = async (requestId,originFacilityRequestId,originFacilityId, targettedFacilityId, data) => {
    try {
        console.log("Starting to update other facility reject status for facility ID:", originFacilityId);
        
        const otherInstallationDatabases = getOtherInstallationDatabases();
        console.log("Fetched other installation databases:", otherInstallationDatabases);
        
        const targetDatabase = otherInstallationDatabases.find(db => db.id === originFacilityId);
        
        if (!targetDatabase) {
            console.log('Target database not found for facility ID:', originFacilityId);
            return { success: false, message: 'Target database not found for facility ID: ' + originFacilityId };
        }

        // Filter out the originFacilityId from the targettedFacilityId array
        const filteredTargetFacilities = targettedFacilityId.filter(id => id !== String(originFacilityId));

        // Update all targeted facilities' mirrored requests to the rejected status
        for (const facilityId of filteredTargetFacilities) {
            const currentTargetDatabase = otherInstallationDatabases.find(db => db.id === parseInt(facilityId, 10));
            if (!currentTargetDatabase) {
                console.error(`Target database not found for facility ID: ${facilityId}. Skipping update.`);
                continue; // Continue to next facility if database not found
            }

            const currentTargetModels = createTargetDatabaseModels(currentTargetDatabase.connection);
            const currentTargetRequests = currentTargetModels.Requests;

            await currentTargetRequests.update(data, {
                where: {
                    origin_facility_request_id: originFacilityRequestId,
                    for_parent_facility: 2
                }
            });
            console.log(`Updated mirrored requests in facility ${currentTargetDatabase.name} to status: ${data.status}`);
        }

        // Update the origin facility's request
        const originModels = createTargetDatabaseModels(targetDatabase.connection);
        const OriginRequests = originModels.Requests;
         const currentFacilityName = CURRENT_INSTALLATION.toUpperCase(); // Assuming qbCustomerName holds the current facility's name
        const updatedData = {
            ...data,
            reason: `${data?.reason} — rejected by ${currentFacilityName}`
        };
        const [affectedRows] = await OriginRequests.update(updatedData, { where: { id: originFacilityRequestId } });
        try {
            const updatedData = {
                ...data,
                reason: null
            };
            const [affectedRows] = await OriginRequests.update(updatedData, { where: { origin_facility_request_id: requestId , for_parent_facility: 2 } });
            console.log('affectedRows build order to other installation', affectedRows)
        } catch (error) {
            console.log('error updating other facility reject status', error)
        }
        return { success: true, affectedRows };
    } catch (error) {
        console.error('Error updating other facility reject status:', error);
        return { success: false, message: error.message };
    }
};

// Function to transfer product stock to other installation
const transferProductStockToOtherInstallation = async (facilityId, productData) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database
        const targetDatabase = otherInstallationDatabases.find(db => db.id === facilityId);
        
        if (!targetDatabase) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        const currentDate = moment().unix();
        
        // Start transaction
        const transaction = await targetDatabase.connection.transaction();
        
        try {
            // Step 1: Check if product exists by name in target installation
            const existingProduct = await targetModels.Products.findOne({
                where: { name: productData.name },
                transaction
            });
            
            if (existingProduct) {
                // Product exists - update stock
                const newStock = (existingProduct.in_stock_no || 0) + (productData.in_stock_no || 0);
                
                await targetModels.Products.update(
                    { 
                        in_stock_no: newStock,
                        updated_at: currentDate,
                        transfered_product_id: productData.transfered_product_id,
                    },
                    { 
                        where: { id: existingProduct.id },
                        transaction
                    }
                );
                
                await transaction.commit();
                
                return {
                    success: true,
                    action: 'updated',
                    data: {
                        productId: existingProduct.id,
                        productName: existingProduct.name,
                        oldStock: existingProduct.in_stock_no || 0,
                        addedStock: productData.in_stock_no || 0,
                        newStock: newStock,
                        facilityId: facilityId,
                        installationName: targetDatabase.name
                    }
                };
            } else {
                // Product doesn't exist - create new product
                const newProductData = {
                    name: productData.name,
                    barcode: productData.barcode,
                    description: productData.description,
                    vendor_id: productData.vendor_id,
                    vpt_number: productData.vpt_number,
                    thumbnail: productData.thumbnail || 'default.jpg',
                    price: productData.price || 0.00,
                    retail_price: productData.retail_price,
                    actual_retail_price: productData.actual_retail_price,
                    install_cost: productData.install_cost,
                    formula_cost: productData.formula_cost,
                    pf_retail_markup: productData.pf_retail_markup,
                    special_price: productData.special_price,
                    unit: productData.unit,
                    material_types: productData.material_types,
                    unit_qty: productData.unit_qty,
                    start_date: productData.start_date,
                    end_date: productData.end_date,
                    added_by: productData.added_by,
                    category_id: productData.category_id,
                    sub_category_id: productData.sub_category_id,
                    qb_category: productData.qb_category,
                    qb_sub_category: productData.qb_sub_category,
                    qb_income_account: productData.qb_income_account,
                    qb_expense_account: productData.qb_expense_account,
                    tags: productData.tags,
                    workflow: productData.workflow,
                    min_value: productData.min_value || 0,
                    max_value: productData.max_value || 0,
                    timestamp: currentDate,
                    meterial: productData.meterial || 0,
                    status: productData.status || 3,
                    twilio_field_value_sid: productData.twilio_field_value_sid,
                    tax_category: productData.tax_category,
                    // recipe_id: productData.recipe_id,
                    inhouse: productData.inhouse || 6,
                    quickbookId:null,
                    syncWithQuickbooks: 0, // Reset sync status for new installation
                    account_type: productData.account_type || '1',
                    isAdditionalProduct: productData.isAdditionalProduct || 0,
                    isTrafficCatalog: productData.isTrafficCatalog || 0,
                    taxable: productData.taxable || 1,
                    markup: productData.markup,
                    origin_catalog: productData.origin_catalog,
                    sub_contract_price: productData.sub_contract_price,
                    in_stock_no: productData.in_stock_no || 0,
                    taxCodeId: productData.taxCodeId,
                    taxCode: productData.taxCode,
                    transfered_product_id: productData.transfered_product_id,
                };
                console.log('newProductData----', newProductData)
                const newProduct = await targetModels.Products.create(newProductData, { transaction });
                console.log('newProduct----', newProduct)
                await transaction.commit();
                
                return {
                    success: true,
                    action: 'created',
                    data: {
                        productId: newProduct.id,
                        productName: newProduct.name,
                        stock: newProduct.in_stock_no,
                        facilityId: facilityId,
                        installationName: targetDatabase.name
                    }
                };
            }
            
        } catch (error) {
            // Rollback transaction on error
            await transaction.rollback();
            return {
                success: false,
                message: 'Transaction failed: ' + error.message
            };
        }
        
    } catch (error) {
        console.error('Error transferring product stock to other installation:', error);
        return { success: false, message: error.message };
    }
};




// Function to create completed project task in parent facility after product transfer
const createCompletedTaskInParentFacility = async (parentFacilityId, originFacilityRequestId, productData, eachProduct, currentFacilityId) => {
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        
        // Find target database (parent facility)
        const targetDatabase = otherInstallationDatabases.find(db => db.id === parentFacilityId);
        
        if (!targetDatabase) {
            return { success: false, message: 'Parent facility database not found for facility ID: ' + parentFacilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDatabase.connection);
        
        
        // Get project ID from the parent facility using the parent request ID
        const parentProjectId = await targetModels.Projects.findOne({
            where: { request_id: originFacilityRequestId }
        });
        console.log('parentProjectId', parentProjectId)
        
        if (!parentProjectId) {
            return { success: false, message: 'Project not found in parent facility for request ID: ' + originFacilityRequestId };
        }
        
        // Validate required data
        if (!productData.name) {
            return { success: false, message: 'Product name is required for task creation' };
        }
        
        // Create completed task in parent facility
        const taskData = {
            project_id: parentProjectId.id,
            project_task_uid: `TASK_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
            name: productData.name,
            task_uid: 1,
            description: productData.name,
            is_manually_added: 0,
            start_date: moment().unix(),
            due_date: moment().unix(),
            product_id: eachProduct.product_id,
            customer_order_product_id: eachProduct.id,
            reporter_id: 1,
            priority: 3,
            status: 4, // Completed status
            userid: 1,
            timestamp: moment().unix(),
            complete: 100, // 100% completion
            complete_sync: 1,
            from_product: 1,
            order_id: null,
            request_location_id: null,
            facility_id: currentFacilityId,
        };
        console.log('taskData----', taskData);
        const createdTask = await targetModels.ProjectTasks.create(taskData);
        console.log('Task created in parent facility:', createdTask);
        
        return {
            success: true,
            message: 'Task created successfully in parent facility',
            data: {
                taskId: createdTask.id,
                projectId: parentProjectId.id,
                facilityId: parentFacilityId,
                installationName: targetDatabase.name
            }
        };
        
    } catch (error) {
        console.error('Error creating task in parent facility:', error);
        return { success: false, message: 'Error creating task in parent facility: ' + error.message };
    }
};

/////////////////////////The End of Sales Order Management//////////////////////////

///////////////////////////////QB Token Management///////////////////////////////
// updateQbTokenOnAllInstallations is now imported from quickbook.js to avoid circular dependency
///////////////////////////////QB Token Management End///////////////////////////////

const updateSplitMirrorStatus = async (originRequestId,targetFacilityId,status,receivedStatus) => {
    console.log('status----', status)
    console.log('receivedStatus-----', receivedStatus)
    console.log('targetFacilityIdddd', targetFacilityId)
    try {
        const facilityId = Number(targetFacilityId)
        const targetDb = getTargetInstallation(facilityId);
        
        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${targetFacilityId}`);
            return { success: false, message: `No target installation found for facility_id: ${targetFacilityId}` };
        }

        console.log(`Updating mirror status for originRequestId ${originRequestId} in ${targetDb.name} installation`);
        
        // Create Requests model for the target database
        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;

        const data = { split_mirror_status: status };

        if (receivedStatus !== undefined) {
            data.received_status = receivedStatus;
        }
        // Update request in target installation
        const [affectedRows] = await Requests.update(data, { 
            where: { origin_facility_request_id: originRequestId, for_parent_facility: 2 } 
        });
        console.log(`Successfully updated ${affectedRows} row(s) for mirrored request ${originRequestId}`);
        return { success: true, affectedRows };
        
    } catch (error) {
        console.log(`Error updating mirror status in other installation:`, error);
        return { success: false, message: error.message };
    }
};

// Update the main status of a mirrored request in another installation.
// Uses the current request id as origin_facility_request_id in the target DB
// and only updates rows where for_parent_facility = 2 (mirrored records).
const updateOriginFacilityRequestStatus = async (originRequestId, targetFacilityId, data) => {
    console.log('updateOriginFacilityRequestStatus targetFacilityId:', targetFacilityId);
    try {
        const facilityId = Number(targetFacilityId);
        const targetDb = getTargetInstallation(facilityId);

        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${targetFacilityId}`);
            return { success: false, message: `No target installation found for facility_id: ${targetFacilityId}` };
        }

        console.log(`Updating status for originRequestId ${originRequestId} in ${targetDb.name} installation`);

        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;


        const [affectedRows] = await Requests.update(data, {
            where: { origin_facility_request_id: originRequestId, for_parent_facility: 2 },
        });

        console.log(`Successfully updated ${affectedRows} row(s) status for mirrored request ${originRequestId}`);
        return { success: true, affectedRows };
    } catch (error) {
        console.log(`Error updating status in other installation:`, error);
        return { success: false, message: error.message };
    }
};

const updateOriginBuildOrderRequestStatus = async (originRequestId, targetFacilityId, data) => {
    console.log('updateOriginFacilityRequestStatus targetFacilityId:', targetFacilityId);
    try {
        const facilityId = Number(targetFacilityId);
        const targetDb = getTargetInstallation(facilityId);

        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${targetFacilityId}`);
            return { success: false, message: `No target installation found for facility_id: ${targetFacilityId}` };
        }

        console.log(`Updating status for originRequestId ${originRequestId} in ${targetDb.name} installation`);

        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;


        const [affectedRows] = await Requests.update(data, {
            where: { id: originRequestId, for_parent_facility: 1 },
        });

        console.log(`Successfully updated ${affectedRows} row(s) status for mirrored request ${originRequestId}`);
        return { success: true, affectedRows };
    } catch (error) {
        console.log(`Error updating status in other installation:`, error);
        return { success: false, message: error.message };
    }
};

const updateTargetedFacilityHoldStatus = async (originRequestId, targetFacilityId, project,jobHoldData) => {
    try {
        const facilityId = Number(targetFacilityId);
        const targetDb = getTargetInstallation(facilityId);

        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${targetFacilityId}`);
            return { success: false, message: `No target installation found for facility_id: ${targetFacilityId}` };
        }

        console.log(`Updating hold status for originRequestId ${originRequestId} in ${targetDb.name} installation to ${project.hold}`);

        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;
        const Projects = targetModels.Projects;

        // Update request table
        const [affectedRequests] = await Requests.update(
            project,
            { where: { origin_facility_request_id: originRequestId, for_parent_facility: 2 } }
        );

        // Update projects table
        // Find the project linked to the request using origin_facility_request_id
        const projectRecord = await Requests.findOne({
            where: { origin_facility_request_id: originRequestId, for_parent_facility: 2 },
            attributes: ['id'] // Assuming 'id' in Requests table is the request_id for Projects table
        });

        let affectedProjects = 0;
        if (projectRecord && projectRecord.id) {
            // Update the Projects table using the request_id
            const [updatedRows] = await Projects.update(
                jobHoldData, // Corrected field name to 'hold'
                { where: { request_id: projectRecord.id } } // Corrected to use request_id for Projects table
            );
            affectedProjects = updatedRows;
        }

        console.log(`Successfully updated ${affectedRequests} request(s) and ${affectedProjects} project(s) for mirrored request ${originRequestId}`);
        return { success: true, affectedRequests, affectedProjects };

    } catch (error) {
        console.log(`Error updating hold status in other installation:`, error);
        return { success: false, message: error.message };
    }
};

const updateBuildOrderToOtherInstallation = async (facilityId,request_id, data) => {
    console.log('facilityIddd', facilityId)
    try {
        const otherInstallationDatabases = getOtherInstallationDatabases();
        console.log("Fetched other installation databases:", otherInstallationDatabases);
        
        const targetDb = otherInstallationDatabases.find(db => db.id === Number(facilityId));
        console.log('targetDb ===', targetDb)
        
        if (!targetDb) {
            return { success: false, message: 'Target database not found for facility ID: ' + facilityId };
        }

        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;

            // Update the build order in the other installation
            const [affectedRows] = await Requests.update(data, { where: { origin_facility_request_id: request_id, origin_facility_id: FACILITY_ID } });
            
            // Get the updated request to get its ID (status might have changed, so don't filter by status)
            const updatedRequest = await Requests.findOne({ where: { origin_facility_request_id: request_id, origin_facility_id: FACILITY_ID } });
            
            const id = updatedRequest?.dataValues?.id || updatedRequest.id;
            console.log('id ===', id)
            if (updatedRequest && id) {
                // Update the mirrored request in the current facility using the updated request's ID as origin_facility_request_id
                const updatedMirroredRequest = await RequestsModel.update(data, { 
                    where: { 
                        origin_facility_request_id: id, 
                        for_parent_facility: 2 
                    } 
                });
            }
            
            return { success: true, affectedRows };
    } catch (error) {
        console.error('Error in updateBuildOrderToOtherInstallation:', error);
        return { success: false, message: error.message };
    }
}

const getSplitMirrorData = async (originRequestId, targetFacilityId) => {
    try {
        const facilityId = Number(targetFacilityId);
        const targetDb = getTargetInstallation(facilityId);

        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${targetFacilityId}`);
            return { success: false, message: `No target installation found for facility_id: ${targetFacilityId}` };
        }

        console.log(`Getting mirror data for originRequestId ${originRequestId} from ${targetDb.name} installation`);

        // Create Requests model for the target database
        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;

        // Get request data from target installation
        const mirrorRequest = await Requests.findOne({
            where: {
                origin_facility_request_id: originRequestId,
                for_parent_facility: 1
            }
        });

        if (!mirrorRequest) {
            console.log(`No mirror request found for originRequestId ${originRequestId} in facility ${targetFacilityId}`);
            return { success: false, message: `No mirror request found for originRequestId ${originRequestId} in facility ${targetFacilityId}` };
        }

        // Now find the corresponding request in current database
        const currentDbRequest = await RequestsModel.findOne({
            where: {
                origin_facility_request_id: mirrorRequest.id,
                for_parent_facility: 2
            }
        });

        if (!currentDbRequest) {
            console.log(`No corresponding request found in current database for mirrorRequest.id ${mirrorRequest.id}`);
            return { success: false, message: `No corresponding request found in current database for mirrorRequest.id ${mirrorRequest.id}` };
        }

        const mirrorData = {
            facility_id: targetFacilityId,
            status: currentDbRequest.status,
            split_mirror_status: currentDbRequest.split_mirror_status,
            received_status: currentDbRequest.received_status,
            min_payment_status: currentDbRequest.min_payment_status,
            request_id: currentDbRequest.id
        };

        console.log(`Successfully retrieved mirror data for facility ${targetFacilityId}:`, mirrorData);
        return { success: true, data: mirrorData };

    } catch (error) {
        console.log(`Error getting mirror data from other installation:`, error);
        return { success: false, message: error.message };
    }
};


const getOtherInstallationRequestData = async (originRequestId, targetFacilityId) => {
    try {
        const facilityId = Number(targetFacilityId);
        const targetDb = getTargetInstallation(facilityId);

        if (!targetDb) {
            console.log(`No target installation found for facility_id: ${targetFacilityId}`);
            return {
                success: false,
                message: `No target installation found for facility_id: ${targetFacilityId}`
            };
        }

        console.log(`Getting request data for originRequestId ${originRequestId} from ${targetDb.name} installation`);

        // Create models for target database
        const targetModels = createTargetDatabaseModels(targetDb.connection);
        const Requests = targetModels.Requests;

        // Get request from other installation
        const requestData = await Requests.findOne({
            where: {
                origin_facility_request_id: originRequestId
            }
        });

        if (!requestData) {
            console.log(`No request found for originRequestId ${originRequestId} in facility ${targetFacilityId}`);
            return null
        }

        console.log(`Successfully retrieved request from facility ${targetFacilityId}`);

        return requestData?.dataValues

    } catch (error) {
        console.log(`Error getting request data from other installation:`, error);
        return null
    }
};
// (async () => {
//     const result = await getOtherInstallationRequestData(2626, 2);
//     console.log('result---', result);
// })();

module.exports = {
    addSplitEstimateToOtherInstallation,
    addSplitEstimateAttachmentsToOtherInstallation,
    addSplitEstimateRecipesToOtherInstallation,
    addSplitEstimateServicesToOtherInstallation,
    syncSplitEstimateAcrossInstallations,
    updateSplitEstimateInOtherInstallation,
    deleteAllSplitEstimateAttachmentsInOtherInstallation,
    deleteAllSplitEstimateRecipesInOtherInstallation,
    deleteAllSplitEstimateServicesInOtherInstallation,
    syncSplitEstimateUpdateAcrossInstallations,
    syncSplitEstimateStatusUpdateAcrossInstallations,
    getSplitEstimatesFromOtherInstallation,
    getTargetInstallation,
    createSplitEstimateModel,
    createSplitEstimateAttachmentsModel,
    createSplitEstimateRecipesModel,
    createSplitEstimateServicesModel,
    getAllSplitManagementSettings,
    updateSplitManagementSettings,

    getAllProductsFromMultipleDatabase,
    getAllProductsFromSpecificFacility,
    getAllInstallationCompanyAddresses,
    getAllSplitManagementSettingsFromOtherInstallations,
    addCompleteSalesOrderToOtherInstallation,
    addCompleteSalesOrderToCurrentInstallation,
    deleteCompleteSalesOrderFromOtherInstallation,
    BuildOrderFromOtherInstallationsDelete,
    deleteCompleteSalesOrderFromCurrentInstallation,
    getAllInstallLabourFromOtherInstallations,
    getAllLabourCostServicesFromOtherInstallations,
    syncSplitEstimateProductAddedStatusUpdateAcrossInstallations,
    transferProductStockToOtherInstallation,
    createCompletedTaskInParentFacility,
    createTargetDatabaseModels,
    addMirrorCompleteSalesOrderToOtherInstallation,
    updateMirrorCompleteSalesOrderToOtherInstallation,
    getProjectIdFromOtherFacility,
    updateSplitMirrorStatus,
    updateOriginFacilityRequestStatus,
    findExistingMirrorSalesOrder,
    deleteCompleteMirrorSalesOrderFromOtherInstallation,
    updateOtherfacilityApproveStatus,
    updateOtherfacilityRejectStatus,
    updateTargetedFacilityHoldStatus,
    updateBuildOrderToOtherInstallation,
    updateOriginBuildOrderRequestStatus,
    getSplitMirrorData,
    BuildOrderFromOtherInstallationsDeleteMirror,
    mirrorBuildOrderFromCurrentInstallationDelete,
    mirrorBuildOrderFromOtherInstallationsDelete,
    getRequestByMirrorBuildOrder,
    getOtherInstallationRequestData
};

