const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const { empty } = require('../Helpers/Common')

const parseDecimalFieldsForOrderProducts = (orderProduct) => {
    orderProduct.product_price = orderProduct.product_price ? parseFloat(orderProduct.product_price) : null;
    orderProduct.product_retail_price = orderProduct.product_retail_price ? parseFloat(orderProduct.product_retail_price) : null;
    orderProduct.product_markup = orderProduct.product_markup ? parseFloat(orderProduct.product_markup) : null;
    orderProduct.product_qty = orderProduct.product_qty ? parseFloat(orderProduct.product_qty) : null;
    orderProduct.product_sub_total = orderProduct.product_sub_total ? parseFloat(orderProduct.product_sub_total) : null;
    orderProduct.product_tax = orderProduct.product_tax ? parseFloat(orderProduct.product_tax) : null;
    orderProduct.product_total = orderProduct.product_total ? parseFloat(orderProduct.product_total) : null;
    orderProduct.discount = orderProduct.discount ? parseFloat(orderProduct.discount) : null;
    orderProduct.quantity = orderProduct.quantity ? parseFloat(orderProduct.quantity) : null;
    return orderProduct;
}

const Recipe = sequelize.define('recipes', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    workflow_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    name: { type: Sequelize.STRING, allowNull: false },
    expected_cost:{type:Sequelize.FLOAT(12,4),allowNull:true,defaultValue:null},
    expected_material_cost:{type:Sequelize.FLOAT(12,4),allowNull:true,defaultValue:null},
    expected_service_cost:{type:Sequelize.FLOAT(12,4),allowNull:true,defaultValue:null},
    expected_install_labor_cost:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    expected_total_service_cost:{type:Sequelize.FLOAT(12,4),allowNull:true,defaultValue:null},
    total_retail_markup:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    expected_material_cost_with_markup:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    expected_service_cost_with_markup:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    expected_install_labor_cost_with_markup:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    expected_cost_with_markup:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    type: { type: Sequelize.INTEGER, allowNull: false,defaultValue: 1 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    name_or_description: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    isNew: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
}, { timestamps: false, tableName: 'recipes' })

const RecipeHistory = sequelize.define('recipe_history', {
    id : { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    recipe_id : { type: Sequelize.INTEGER, allowNull: false },
    name : {type:Sequelize.STRING,allowNull:true,defaultValue:null},
    material_id : {type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    service_id : {type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    added_by : {type:Sequelize.INTEGER,allowNull:false},
    old_price : {type:Sequelize.FLOAT(16,4),allowNull:true,defaultValue:0.0000},
    new_price : { type: Sequelize.FLOAT(16,4),allowNull:true,defaultValue:0.0000},
    new_qty : { type: Sequelize.FLOAT(16,4),allowNull:true,defaultValue:0.0000},
    timestamp : { type: Sequelize.INTEGER, allowNull: false },
}, { timestamps: false, tableName: 'recipe_history' })

const Ingredients=sequelize.define('catelog_recipe',{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    recipe_id: { type: Sequelize.INTEGER, allowNull: false },
    product_id: { type: Sequelize.INTEGER, allowNull: false },
    quantity: { type: Sequelize.FLOAT, allowNull: false },
    // added_by: { type: Sequelize.INTEGER, allowNull: false },
    stockType:{ type: Sequelize.INTEGER, allowNull: false ,defaultValue:1},
    width:{ type: Sequelize.FLOAT(12,4), allowNull: true },
    convertionUnit:{ type: Sequelize.INTEGER, allowNull: true },
    convertionQty:{ type: Sequelize.FLOAT(12,4), allowNull: true },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName: 'catelog_recipe' })

const ServicesRecipe=sequelize.define('services_recipe',{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    recipe_id: { type: Sequelize.INTEGER, allowNull: false },
    service_id: { type: Sequelize.INTEGER, allowNull: false },
    quantity: { type: Sequelize.FLOAT(12,2), allowNull: false },
    facility_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName: 'services_recipe' })

const getAllReceipes=async()=>{
    let query = await sequelize.query(`select r.*,r.id as recipe_id,ow.name as workflow_name,concat(u.first_name,' ',u.last_name) as authorized_by from recipes r `
        + ` left join users u on r.added_by=u.id `
        + ` left join onboard_workflows ow on ow.id=r.workflow_id`
        + ` where r.status=1 `
      )
      let data;
      if (query[0].length > 0) {
          data = query[0];
            for (const row of data){
                let query2=await sequelize.query(`select r.*,p.name,p.unit from catelog_recipe r `
                +` left join products p on p.id=r.product_id`
                +` where r.recipe_id=${row.id}`)

                let query3=await sequelize.query(`select sr.*,p.name,p.cost,p.total_cost,p.markup from services_recipe sr `
                +` left join labour_cost_services p on p.id=sr.service_id`
                +` where sr.recipe_id=${row.id}`)
                
                if(query2.length > 0){
                    row.ingredients=query2[0]
                }
                if(query3.length > 0){
                    row.services=query3[0]
                    
                    // Calculate install labour cost (service_id = 10)
                    let installLabourCost = 0;
                    let installLabourCostWithMarkup = 0;
                    
                    query3[0].forEach(service => {
                        if (service.service_id === 10) {
                            // Calculate cost with quantity - always multiply cost by quantity
                            const serviceCost = service.cost || 0;
                            const quantity = service.quantity || 1;
                            const markup = service.markup || 0;
                            
                            // Calculate base cost: cost * quantity
                            const baseCost = serviceCost * quantity;
                            installLabourCost += baseCost;
                            
                            // Calculate with markup
                            const costWithMarkup = baseCost + (baseCost * markup / 100);
                            installLabourCostWithMarkup += costWithMarkup;
                        }
                    });
                    
                    // Add install labour cost fields to the recipe
                    row.install_labour_cost = installLabourCost;
                    row.install_labour_cost_with_markup = installLabourCostWithMarkup;
                    
                    // Calculate service cost excluding install labour
                    let totalServiceCost = 0;
                    let totalServiceCostWithMarkup = 0;
                    
                    query3[0].forEach(service => {
                        if (service.service_id !== 10) { // Exclude install labour (service_id = 10)
                            const serviceCost = service.cost || 0;
                            const quantity = service.quantity || 1;
                            const markup = service.markup || 0;
                            
                            const baseCost = serviceCost * quantity;
                            totalServiceCost += baseCost;
                            
                            const costWithMarkup = baseCost + (baseCost * markup / 100);
                            totalServiceCostWithMarkup += costWithMarkup;
                        }
                    });
                    
                    // Update service cost fields to exclude install labour
                    row.expected_service_cost = totalServiceCost;
                    row.expected_service_cost_with_markup = totalServiceCostWithMarkup;
                } else {
                    // No services found, set install labour cost to 0
                    row.install_labour_cost = 0;
                    row.install_labour_cost_with_markup = 0;
                }
            }
          return data;
      } else {
          return [];
      }
}
const CreateRecipe = async(data)=>{
    console.log("########Reeeee",data);
    let result = await Recipe.create(data)
    console.log("########Reeeee",result.id);
    if(result){
        return result.id;
    }else{
        return false
    }
}
const CreateIngredients = async(data)=>{
    let result = await Ingredients.create(data)
    console.log("########Reeeee",result.id);
    if(result){
        return true;
    }else{
        return false
    }
}
const CreateServicesRecipe = async(data)=>{
    let result = await ServicesRecipe.create(data)
    console.log("########Reeeee",result.id);
    if(result){
        return true;
    }else{
        return false
    }
}
const getRecipeById= async(id)=>{
    // let query=await sequelize(`select r.id as recipie_id,r.name,ri.product_id,ri.quantity from recipies r
    // left join catelog_recipie ri on ri.recipe_id`)
    let query=await sequelize.query(`select r.* from recipes r where r.id=${id}`)
    let data=[]
    if(query[0].length>0){
        data= await Promise.all(query[0].map(async row=>{
        
            row.ingredients=[]
            let query2=await sequelize.query(`select r.*,p.price as materialCost,p.name AS ing_name,p.unit from catelog_recipe r 
                left join products p on p.id = r.product_id
                where r.recipe_id=${row.id}`)
            let query3=await sequelize.query(`select r.*,l.total_cost as serviceAmount,cost as serviceValue, l.inhouse,l.name AS ing_name,l.markup from services_recipe r
                left join labour_cost_services l on l.id=r.service_id
                where r.recipe_id=${row.id}`)
        //   if(query2.length>0){
        //     row.ingredients=query2[0]
        //   }
            if (query2[0]?.length > 0) {  // ✅ Ensure query2[0] exists
                row.ingredients = await Promise.all(query2[0].map(async ingredient => {
                    
                    // ✅ Fetch total partial stock & max unit cost for the ingredient's conversion unit
                    const stockQuery = await sequelize.query(
                        `SELECT 
                            SUM(mi.qty) AS total_qty, 
                            MAX(mi.unit_cost) AS max_unit_cost
                        FROM material_inventory mi
                        LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
                        WHERE (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
                        AND mi.partial = 1 
                        AND mi.status != 0 
                        AND mi.partial_unit = ? 
                        AND mi.material_id = ?`, 
                        { replacements: [ingredient.convertionUnit, ingredient.product_id], type: sequelize.QueryTypes.SELECT }
                    );
            
                    // ✅ Ensure stock data is always available
                    let stockData = stockQuery?.[0] || { total_qty: 0, max_unit_cost: 0 };
            
                    // ✅ Attach stock details to ingredient
                    ingredient.total_partial_stock = stockData.total_qty;
                    ingredient.max_partial_unit_cost = stockData.max_unit_cost;
            
                    return ingredient;
                }));
            }
          if(query3.length>0){
            row.services=query3[0]
            
            // Calculate install labour cost (service_id = 10)
            let installLabourCost = 0;
            let installLabourCostWithMarkup = 0;
            
            query3[0].forEach(service => {
                if (service.service_id === 10) {
                    // Calculate cost with quantity - always multiply cost by quantity
                    const serviceCost = service.serviceValue || 0;
                    const quantity = service.quantity || 1;
                    const markup = service.markup || 0;
                    
                    // Calculate base cost: cost * quantity
                    const baseCost = serviceCost * quantity;
                    installLabourCost += baseCost;
                    
                    // Calculate with markup
                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                    installLabourCostWithMarkup += costWithMarkup;
                }
            });
            
            // Add install labour cost fields to the recipe
            row.install_labour_cost = installLabourCost;
            row.install_labour_cost_with_markup = installLabourCostWithMarkup;
            
            // Calculate service cost excluding install labour
            let totalServiceCost = 0;
            let totalServiceCostWithMarkup = 0;
            
            query3[0].forEach(service => {
                if (service.service_id !== 10) { // Exclude install labour (service_id = 10)
                    const serviceCost = service.serviceValue || 0;
                    const quantity = service.quantity || 1;
                    const markup = service.markup || 0;
                    
                    const baseCost = serviceCost * quantity;
                    totalServiceCost += baseCost;
                    
                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                    totalServiceCostWithMarkup += costWithMarkup;
                }
            });
            
            // Update service cost fields to exclude install labour
            row.expected_service_cost = totalServiceCost;
            row.expected_service_cost_with_markup = totalServiceCostWithMarkup;
          } else {
            // No services found, set install labour cost to 0
            row.install_labour_cost = 0;
            row.install_labour_cost_with_markup = 0;
          }
          return row
        }))

    }
    return data;
}
const deleteRecipe=async(id)=>{
    const deleted = await Ingredients.destroy({
        where: {
            recipe_id: id
        },
    });
    console.log("deleteProduct..result...", deleted);
    if (deleted) {
        return deleted;
    }
    else {
        return false;
    }
}
const deleteServicesRecipe=async(id)=>{
    const deleted = await ServicesRecipe.destroy({
        where: {
            recipe_id: id
        },
    });
    console.log("deleteService..result...", deleted);
    if (deleted) {
        return deleted;
    }
    else {
        return false;
    }
}
const updateRecipe = async ( data,id) => {
    console.log("-------update-data------", data)
    try {
        const updatedRows = await Recipe.update(data, {
            where: {
                id:id
            },
            returning: true,
        });
        if (updatedRows) {
            return updatedRows
        }
        console.log("---------updatedRows------------",updatedRows)
    }
    catch (error) {
        console.error("Error updating product:", error);
        return false;
    }
}
const getAllReceipesByProjectId=async(id,request)=>{
    let whereCondition = '';
    if(id){
        whereCondition = ` and p.id in (${id}) `
    }
    let query = await sequelize.query(`select r.*,p.name as productName,c.id AS customer_order_product_id, concat(u.first_name, ' ', u.last_name) as authorized_by,p.id as productId,c.product_qty as quantity,c.location_quantity from recipes r `
        // +` left join catelog_recipe c on c.recipe_id=r.id`
        + ` left join users u on r.added_by=u.id `
        +` left join products p on p.recipe_id=r.id`
        +` left join customer_order_products c on c.product_id=p.id `
        + ` left join customer_orders co on co.id=c.order_id` 
        +` where r.status=1 ${whereCondition} and co.request_id=${request} `
      )
      console.log("############$$$$$$$$$$%%%%%%%%%",query[0]);
      let data;
      if (query[0].length > 0) {
        const result = query[0]?.map(record => parseDecimalFieldsForOrderProducts(record));
        data= await Promise.all(result?.map(async row=>{
        
            row.ingredients=[]
            let query2=await sequelize.query(`select r.*,p.name,p.unit from catelog_recipe r
            LEFT JOIN products p ON p.id = r.product_id where r.recipe_id=${row.id} and r.status=1`)

            let query3=await sequelize.query(`select r.*,p.name,p.cost,p.total_cost,p.markup from services_recipe r
            LEFT JOIN labour_cost_services p ON p.id = r.service_id where r.recipe_id=${row.id} `)
          if(query2.length>0){
            row.ingredients=query2[0]
          }

          if(query3?.length>0){
            row.services=query3[0]
            
            // Calculate install labour cost (service_id = 10)
            let installLabourCost = 0;
            let installLabourCostWithMarkup = 0;
            
            query3[0].forEach(service => {
                if (service.service_id === 10) {
                    // Calculate cost with quantity
                    const serviceCost = service.cost || 0;
                    const serviceTotalCost = service.total_cost || 0;
                    const quantity = service.quantity || 1;
                    const markup = service.markup || 0;
                    
                    // Use total_cost if available, otherwise calculate from cost * quantity
                    const baseCost = serviceTotalCost > 0 ? serviceTotalCost : (serviceCost * quantity);
                    installLabourCost += baseCost;
                    
                    // Calculate with markup
                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                    installLabourCostWithMarkup += costWithMarkup;
                }
            });
            
            // Add install labour cost fields to the recipe
            row.install_labour_cost = installLabourCost;
            row.install_labour_cost_with_markup = installLabourCostWithMarkup;
            
            // Calculate service cost excluding install labour
            let totalServiceCost = 0;
            let totalServiceCostWithMarkup = 0;
            
            query3[0].forEach(service => {
                if (service.service_id !== 10) { // Exclude install labour (service_id = 10)
                    const serviceCost = service.serviceValue || 0;
                    const quantity = service.quantity || 1;
                    const markup = service.markup || 0;
                    
                    const baseCost = serviceCost * quantity;
                    totalServiceCost += baseCost;
                    
                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                    totalServiceCostWithMarkup += costWithMarkup;
                }
            });
            
            // Update service cost fields to exclude install labour
            row.expected_service_cost = totalServiceCost;
            row.expected_service_cost_with_markup = totalServiceCostWithMarkup;
          } else {
            // No services found, set install labour cost to 0
            row.install_labour_cost = 0;
            row.install_labour_cost_with_markup = 0;
          }
          return row
        }))
          return data;
      } else {
          return [];
      }
}
const fetchAllServicesByRequest=async(requestId)=>{
    let query=await sequelize.query(`select rs.*,l.name,rs.taken as finalTaken,  CASE 
        WHEN l.cost = 0 THEN rs.unit_cost 
        ELSE COALESCE(l.cost, rs.unit_cost) 
    END as originalCost,r.name as recipeName from request_services rs`
        +` left join labour_cost_services l on l.id=rs.labour_cost_service_id `
        +` left join recipes r on r.id=rs.recipe_id`
        +` where rs.request_id=${requestId} and rs.job_cost=0 and rs.status = 1 ORDER BY rs.id ASC `
    )
    let data=[];
      if (query[0].length > 0){
        data=query[0]
      }
      return data;
}
// const fetchAllServicesByProject=async(projectId, taskId)=>{
//     let query=await sequelize.query(`SELECT
//             rs.*,
//             l.name,
//             rs.taken AS finalTaken,
//             CASE WHEN l.cost = 0 THEN rs.unit_cost ELSE COALESCE(l.cost, rs.unit_cost)
//         END AS originalCost,
//         r.name AS recipeName
//         FROM
//             project_tasks pt
//         JOIN projects p ON
//             p.id = ${projectId}
//         JOIN request_services rs ON
//             ((rs.product_id = pt.product_id AND rs.request_id = p.request_id) OR (rs.recipe_id IS NULL AND rs.request_id = p.request_id))
//         LEFT JOIN customer_order_products cop ON
//             cop.product_index = rs.product_index
//         LEFT JOIN labour_cost_services l ON
//             l.id = rs.labour_cost_service_id
//         LEFT JOIN recipes r ON
//             r.id = rs.recipe_id
//         WHERE
//             pt.ID = ${taskId} AND rs.job_cost = 0
//         GROUP BY
//             rs.id`
//     )
//     // let query=await sequelize.query(`select rs.*,l.name,rs.taken as finalTaken,  CASE 
//     //     WHEN l.cost = 0 THEN rs.unit_cost 
//     //     ELSE COALESCE(l.cost, rs.unit_cost) 
//     // END as originalCost,r.name as recipeName from request_services rs`
//     //     +` left join projects p on p.id=${projectId}`
//     //     +` left join project_tasks pt on pt.ID=${taskId}`
//     //     +` left join requests rq on rq.id=p.request_id`
//     //     +` left join labour_cost_services l on l.id=rs.labour_cost_service_id `
//     //     +` left join recipes r on r.id=rs.recipe_id`
//     //     +` where rs.request_id=p.request_id and rs.job_cost=0 
//     //     AND pt.service_id IS NOT NULL AND pt.customer_order_product_id = rs.id`
//     // )
//     let data=[];
//       if (query[0].length > 0){
//         data=query[0]
//       }
//       return data;
// }
const fetchAllServicesByProject = async (projectId, taskId) => {
  let query = await sequelize.query(`
    SELECT
      rs.*,
      l.name,
      rs.taken AS finalTaken,
      CASE WHEN l.cost = 0 THEN rs.unit_cost ELSE COALESCE(l.cost, rs.unit_cost)
      END AS originalCost,
      p.request_id,
      r.name AS recipeName
    FROM
      project_tasks pt
    JOIN projects p ON
      p.id = ${projectId}
    JOIN request_services rs ON
      rs.request_id = p.request_id
    LEFT JOIN customer_order_products cop ON
      cop.product_index = rs.product_index
    LEFT JOIN labour_cost_services l ON
      l.id = rs.labour_cost_service_id
    LEFT JOIN recipes r ON
      r.id = rs.recipe_id
    WHERE
      pt.ID = ${taskId}
      AND rs.job_cost = 0
      AND (
        (pt.product_id IS NULL AND rs.id = pt.customer_order_product_id)
        OR
        (pt.product_id IS NOT NULL AND rs.recipe_id IS NOT NULL)
      )
    GROUP BY rs.id
    ORDER BY rs.id ASC
  `);

  let data = [];
  if (query[0].length > 0) {
    data = query[0];
  }
  return data;
};

const fetchAllAdditionalServicesByProject=async(projectId)=>{
    let query=await sequelize.query(`select rs.*,l.name,l.cost as originalCost from request_services rs`
        +` left join labour_cost_services l on l.id=rs.labour_cost_service_id `
        +` left join projects p on p.request_id = rs.request_id `
        +` where p.id=${projectId} ORDER BY rs.id ASC`
    )
    let data=[];
      if (query[0].length > 0){
        data=query[0]
      }
      return data;
}
const fetchAllAdditionalServicesByRequest=async(requestId)=>{
    let query=await sequelize.query(`select rs.*,l.name,l.cost as originalCost from request_services rs`
        +` left join labour_cost_services l on l.id=rs.labour_cost_service_id `
        +` where rs.request_id=${requestId} and rs.job_cost=1`
    )
    let data=[];
      if (query[0].length > 0){
        data=query[0]
      }
      return data;
}

const CreateRecipeHistory = async(data)=>{
    try{
        let result = await RecipeHistory.create(data)
        if(result){
            return true;
        }else{
            return false
        }
    }catch(error){
        console.log('Error in CreateRecipeHistory : ',error)
        return false;
    }
}

const fetchRecipeHistoryById = async(recipe_id)=>{
    try{
        let result = await sequelize.query(`SELECT rh.*,l.name AS service_name,p.name AS material_name, concat(u.first_name, ' ', u.last_name) as authorized_by FROM recipe_history rh `
            + ` LEFT JOIN products p on p.id=rh.material_id `
            + ` LEFT JOIN labour_cost_services l on l.id=rh.service_id `
            + ` LEFT JOIN users u on u.id=rh.added_by `
            + ` where rh.recipe_id=${recipe_id} `
        )
        if(result[0]?.length > 0){
            return result[0];
        }else{
            return [];
        }
    }catch(error){
        console.log('Error in fetchRecipeHistoryById : ',error)
        return [];
    }
}

const GetServiceRecipesByServiceId = async (serviceId) => {
    try {
        const results = await sequelize.query(`
            SELECT sr.*, r.name, r.expected_cost, r.expected_material_cost, 
                   r.expected_service_cost, r.expected_total_service_cost,
                   r.total_retail_markup, r.expected_material_cost_with_markup,
                   r.expected_service_cost_with_markup, r.expected_cost_with_markup
            FROM services_recipe AS sr
            LEFT JOIN recipes AS r ON sr.recipe_id = r.id
            WHERE sr.service_id = :serviceId
        `, {
            replacements: { serviceId },
            type: sequelize.QueryTypes.SELECT
        });

        return results;
    } catch (error) {
        console.error("Error fetching service recipes:", error);
        return [];
    }
};

const checkMaterialInRecipe = async (materialId) => {
    try {
        const results = await sequelize.query(`
            SELECT cr.*, r.name as recipe_name, r.id as recipe_id
            FROM catelog_recipe cr
            LEFT JOIN recipes r ON cr.recipe_id = r.id
            WHERE cr.product_id = :materialId AND cr.status = 1 AND r.status = 1
        `, {
            replacements: { materialId },
            type: sequelize.QueryTypes.SELECT
        });

        return results.length > 0 ? results : null;
    } catch (error) {
        console.error("Error checking material in recipe:", error);
        return null;
    }
};


// const getRecipeByIdForAutoUpdate= async(id)=>{
//     // let query=await sequelize(`select r.id as recipie_id,r.name,ri.product_id,ri.quantity from recipies r
//     // left join catelog_recipie ri on ri.recipe_id`)
//     let query=await sequelize.query(`select r.* from recipes r where r.id=${id}`)
//     let data=[]
//     if(query[0].length>0){
//         data= await Promise.all(query[0].map(async row=>{
        
//             row.ingredients=[]
//             let query2 = await sequelize.query(`
//                 SELECT r.*, 
//                        p.price AS materialCost,
//                        p.name AS ing_name,
//                        p.unit,
//                        mi.unit_cost AS highest_unit_cost,
//                        p.retail_price AS markup
//                 FROM catelog_recipe r
//                 LEFT JOIN products p ON p.id = r.product_id
//                 LEFT JOIN (
//                     SELECT material_id, MAX(unit_cost) AS unit_cost
//                     FROM material_inventory
//                     WHERE status != 0
//                     GROUP BY material_id
//                 ) mi ON mi.material_id = p.id
//                 WHERE r.recipe_id = ${row.id} ORDER BY r.id
//             `);
//             let query3=await sequelize.query(`select r.*,l.total_cost as serviceAmount,cost as serviceValue, l.inhouse,l.name AS ing_name from services_recipe r
//                 left join labour_cost_services l on l.id=r.service_id
//                 where r.recipe_id=${row.id}`)
//         //   if(query2.length>0){
//         //     row.ingredients=query2[0]
//         //   }
//             if (query2[0]?.length > 0) {  // ✅ Ensure query2[0] exists
//                 row.ingredients = await Promise.all(query2[0].map(async ingredient => {
                    
//                     // ✅ Fetch total partial stock & max unit cost for the ingredient's conversion unit
//                     const stockQuery = await sequelize.query(
//                         `SELECT 
//                             SUM(mi.qty) AS total_qty, 
//                             MAX(mi.unit_cost) AS max_unit_cost
//                         FROM material_inventory mi
//                         LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
//                         WHERE (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
//                         AND mi.partial = 1 
//                         AND mi.status != 0 
//                         AND mi.partial_unit = ? 
//                         AND mi.material_id = ?`, 
//                         { replacements: [ingredient.convertionUnit, ingredient.product_id], type: sequelize.QueryTypes.SELECT }
//                     );
            
//                     // ✅ Ensure stock data is always available
//                     let stockData = stockQuery?.[0] || { total_qty: 0, max_unit_cost: 0 };
            
//                     // ✅ Attach stock details to ingredient
//                     ingredient.total_partial_stock = stockData.total_qty;
//                     ingredient.max_partial_unit_cost = stockData.max_unit_cost;
            
//                     return ingredient;
//                 }));
//             }
//           if(query3.length>0){
//             row.services=query3[0]
//           }
//           return row
//         }))

//     }
//     return data;
// }

const getRecipeByIdForAutoUpdate= async(id)=>{
    // let query=await sequelize(`select r.id as recipie_id,r.name,ri.product_id,ri.quantity from recipies r
    // left join catelog_recipie ri on ri.recipe_id`)
    let query=await sequelize.query(`select r.* from recipes r where r.id=${id}`)
    let data=[]
    if(query[0].length>0){
        data= await Promise.all(query[0].map(async row=>{
        
            row.ingredients=[]
            let query2 = await sequelize.query(`
                SELECT r.*, 
                       p.price AS materialCost,
                       p.name AS ing_name,
                       p.unit,
                       mi.highest_partial_unit_cost,
                       mi.highest_finished_unit_cost,
                       mi.highest_overall_unit_cost AS highest_unit_cost,
                       p.retail_price AS markup
                FROM catelog_recipe r
                LEFT JOIN products p ON p.id = r.product_id
                LEFT JOIN (
                  SELECT
                    material_id,
                    MAX(CASE WHEN partial = 1 THEN unit_cost END)                            AS highest_partial_unit_cost,
                    MAX(CASE WHEN partial <> 1 OR partial IS NULL THEN unit_cost END)        AS highest_finished_unit_cost,
                    MAX(unit_cost)                                                           AS highest_overall_unit_cost
                  FROM material_inventory
                  WHERE status <> 0 AND qty > 0
                  GROUP BY material_id
                ) mi ON mi.material_id = p.id
                WHERE r.recipe_id = ${row.id} ORDER BY r.id
            `);
            let query3=await sequelize.query(`select r.*,l.total_cost as serviceAmount,cost as serviceValue, l.inhouse,l.name AS ing_name,l.markup from services_recipe r
                left join labour_cost_services l on l.id=r.service_id
                where r.recipe_id=${row.id}`)
        //   if(query2.length>0){
        //     row.ingredients=query2[0]
        //   }
            if (query2[0]?.length > 0) {  // ✅ Ensure query2[0] exists
                row.ingredients = await Promise.all(query2[0].map(async ingredient => {
                    
                    // ✅ Fetch total partial stock & max unit cost for the ingredient's conversion unit
                    const stockQuery = await sequelize.query(
                        `SELECT 
                            material_id,
                            MAX(CASE WHEN partial = 1 THEN unit_cost END) AS highest_partial_unit_cost,
                            MAX(CASE WHEN partial <> 1 OR partial IS NULL THEN unit_cost END) AS highest_finished_unit_cost,
                            MAX(unit_cost) AS highest_overall_unit_cost
                        FROM material_inventory mi
                        LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
                        WHERE (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
                        AND mi.partial = 1
                        AND mi.status != 0
                        AND mi.partial_unit = ? 
                        AND mi.material_id = ?
                        AND mi.qty > 0
                        GROUP BY material_id`,
                        { replacements: [ingredient.convertionUnit, ingredient.product_id], type: sequelize.QueryTypes.SELECT }
                    );
            
                    // ✅ Ensure stock data is always available
                    let stockData = stockQuery?.[0] || { total_qty: 0, max_unit_cost: 0 };
            
                    // ✅ Attach stock details to ingredient
                    ingredient.highest_unit_cost = ingredient.stockType == 1 ? ingredient?.highest_finished_unit_cost : ingredient?.highest_partial_unit_cost || 0;
                    ingredient.total_partial_stock = stockData.total_qty;
                    ingredient.max_partial_unit_cost = stockData.max_unit_cost;
            
                    return ingredient;
                }));
            }
          if(query3.length>0){
            row.services=query3[0]
            
            // Calculate install labour cost (service_id = 10)
            let installLabourCost = 0;
            let installLabourCostWithMarkup = 0;
            
            query3[0].forEach(service => {
                if (service.service_id === 10) {
                    // Calculate cost with quantity - always multiply cost by quantity
                    const serviceCost = service.serviceValue || 0;
                    const quantity = service.quantity || 1;
                    const markup = service.markup || 0;
                    
                    // Calculate base cost: cost * quantity
                    const baseCost = serviceCost * quantity;
                    installLabourCost += baseCost;
                    
                    // Calculate with markup
                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                    installLabourCostWithMarkup += costWithMarkup;
                }
            });
            
            // Add install labour cost fields to the recipe
            row.install_labour_cost = installLabourCost;
            row.install_labour_cost_with_markup = installLabourCostWithMarkup;
            
            // Calculate service cost excluding install labour
            let totalServiceCost = 0;
            let totalServiceCostWithMarkup = 0;
            
            query3[0].forEach(service => {
                if (service.service_id !== 10) { // Exclude install labour (service_id = 10)
                    const serviceCost = service.serviceValue || 0;
                    const quantity = service.quantity || 1;
                    const markup = service.markup || 0;
                    
                    const baseCost = serviceCost * quantity;
                    totalServiceCost += baseCost;
                    
                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                    totalServiceCostWithMarkup += costWithMarkup;
                }
            });
            
            // Update service cost fields to exclude install labour
            row.expected_service_cost = totalServiceCost;
            row.expected_service_cost_with_markup = totalServiceCostWithMarkup;
          } else {
            // No services found, set install labour cost to 0
            row.install_labour_cost = 0;
            row.install_labour_cost_with_markup = 0;
          }
          return row
        }))

    }
    return data;
}

module.exports = {getAllReceipes,CreateRecipe,CreateIngredients,getRecipeById,deleteRecipe,updateRecipe,
    getAllReceipesByProjectId,CreateServicesRecipe,deleteServicesRecipe,fetchAllServicesByRequest, fetchAllAdditionalServicesByProject,
    fetchAllAdditionalServicesByRequest, CreateRecipeHistory, fetchRecipeHistoryById,fetchAllServicesByProject, GetServiceRecipesByServiceId,getRecipeByIdForAutoUpdate, checkMaterialInRecipe,  }