const { INTEGER, STRING, TEXT, FLOAT, TINYINT, DECIMAL } = require("sequelize");
const sequelize = require("../config/dbconnection");
const { Op } = require('sequelize');
const { FACILITY_ID, arizonaDbName, riversideDbName, getOtherInstallationDatabases } = require("../config/SplitManagementDb");

const CustomerOrder = sequelize.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},
  is_product_row: {type:TINYINT,allowNull:false,defaultValue:0},
  product_index:{type:INTEGER,allowNull:false,defaultValue:0},
  order_id:{type:INTEGER,allowNull:false},
  product_name:{type:TEXT,allowNull:false},
  product_desc:{type:TEXT,allowNull:true, defaultValue:null},
  product_notes:{type:TEXT(2000),allowNull:true, defaultValue:null},
  material_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},
  unit_cost:{type:FLOAT(12,2),allowNull:true,defaultValue:null},
  install_cost:{type:FLOAT(12,2),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},
  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},
  materialInvId:{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},
  adj_value_type:{type:INTEGER,allowNull:false, defaultValue:1},
  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:2},
  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 },
  is_existing_catalog: { type: INTEGER, allowNull: false, defaultValue: 0 },
},{tableName:'customer_order_products', timestamps:false})

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 = sanitizeDecimalForDb(orderProduct.product_markup);
  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.install_adjustment = orderProduct.install_adjustment ? parseFloat(orderProduct.install_adjustment) : 0;
  orderProduct.install_qty = orderProduct.install_qty ? parseFloat(orderProduct.install_qty) : null;
  return orderProduct;
}

/** Ensure a value is a finite number for DB decimal columns (MySQL rejects Infinity/NaN). */
const sanitizeDecimalForDb = (value) => {
  if (value == null || value === '') return null;
  const num = typeof value === 'number' ? value : parseFloat(value);
  return Number.isFinite(num) ? num : null;
};

const createCustomerOrder=async(data)=>{
    const payload = { ...data };
    if ('product_markup' in payload) payload.product_markup = sanitizeDecimalForDb(payload.product_markup);
    let res = await CustomerOrder.create(payload)
    return res.id;
}

const updateCustomerOrderProduct = async (data, id) => {
  let result = await CustomerOrder.update(data, {

    where: { id: id }
  })
  console.log("#######Result", result);
  if (result) {
    return result;
  } else {
    return false;
  }
}

const getProductDetailsById = async (backendId) => {
  try {
    // Find the product by backendId
    const product = await CustomerOrder.findOne({
      where: { id: backendId },
      attributes: ['product_name', 'product_desc'], // Only fetch product_name and product_desc
    });

    // If the product exists, return its details; otherwise, return null
    if (product) {
      return product.get({ plain: true });
    } else {
      return null;
    }
  } catch (error) {
    console.error('Error fetching product details by ID:', error);
    throw new Error('Failed to retrieve product details');
  }
};

const getCustomerOrderProductByOrderId = async (order_id) => {
  console.log('order_iddddddd',order_id)
  const results = await CustomerOrder.findAll({
    where: { order_id },
  });

  return results.length > 0 ? results.map(r => r.get({ plain: true })) : null;
};

// const getCustomerOrderProductsbyId = async (order_id) => {
//   let query = '';
 
//   if (order_id) {
//     query = `
//       SELECT cust_ord_pro.*,co.request_id,p.vpt_number,p.quickbookId,i.id AS installation_id,p.actual_retail_price,cust_ord_pro.install_cost,p.taxCode,cust_ord_pro.product_markup AS markup,p.taxable,i.installation_temp_id,it.status AS published_status,ct.name as installation_temp_name,i.location_id,w.name as workflow_name,p.unit,p.unit_qty,cust_ord_pro.location_quantity as locationQuantity,pc.markup as RetailMarkup,pc.wholesale_markup as WholesaleMarkup,p.description, rl.id AS request_location_id,
//         SUM(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 0 THEN mi.qty ELSE 0 END) AS finishedProductQty,
//         SUM(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 THEN mi.qty ELSE 0 END) AS partialProductQty,
//         SUM(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.partial_unit = cust_ord_pro.convertion_unit THEN mi.qty ELSE 0 END) AS partial_stock
//       FROM customer_order_products cust_ord_pro
//       left join request_locations rl on rl.order_id = cust_ord_pro.id
//       left join customer_orders co on cust_ord_pro.order_id = co.id
//       left join material_inventory mi on cust_ord_pro.product_id = mi.material_id
//       left join receive_orders ro ON ro.id = mi.receive_order_id
//       left join installations i on i.location_id = rl.id
//       LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
//       LEFT JOIN installation_temp_fields it on it.template_id=i.installation_temp_id and it.installation_id = i.id
//       left join products p on p.id = cust_ord_pro.product_id
//        left join product_category pc on pc.id =p.category_id
//       left join onboard_workflows w on w.id = p.workflow
//       WHERE cust_ord_pro.order_id = ${order_id} GROUP BY cust_ord_pro.id
//     `;

//     const result = await sequelize.query(query, {
//       type: sequelize.QueryTypes.SELECT,
//     });
    
//     if (result.length >= 1) {
//       const newData = result.map((row) => row);
//       const data = newData?.map(record => parseDecimalFieldsForOrderProducts(record));
//       const modifiedData = await Promise.all(data.map(async (row) => {
//         const { product_qty, ...rest } = row;
//         const finishedProductQty = row.finishedProductQty + row.locationQuantity;
//         const partialProductQty = row.partialProductQty + row.locationQuantity; 
//         const partial_stock = row.partial_stock + row.locationQuantity; 
//         console.log("###########",row.id);
//         console.log("###########row",row);
//         const queryResult = await sequelize.query(`SELECT rl.*, rl.id AS req_loc_id FROM request_locations rl WHERE rl.order_id = ${row.id}`);
//         if(queryResult[0].length > 0){
//           row.locations = queryResult[0];
//         } else {
//           row.locations = [];
//         }
//         const attachmentResult = await sequelize.query(
//           `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name FROM request_attachments ra
//            LEFT JOIN users u ON u.id = ra.approved_by
//            WHERE ra.status = :status 
//            AND ra.approved = :approved 
//            AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
//            AND ra.request_id = :requestId 
//            AND FIND_IN_SET(:locationId, ra.location_ids)`,
//           //  AND FIND_IN_SET(:productId, ra.product_ids)
//           {
//             type: sequelize.QueryTypes.SELECT,
//             replacements: {
//               status: 1,
//               approved: 1,
//               imgType: 'img',
//               requestId: row.request_id,
//               // productId: row.id,
//               locationId: row.request_location_id
//             }
//           }
//         );        
//         console.log('attachmentResult', attachmentResult)
//         if (attachmentResult && attachmentResult.length > 0) {
//           row.attachments = attachmentResult;
//         } else {
//           row.attachments = [];
//         }        
//         const workflowRes = await sequelize.query(`select * from onboard_workflows where id = ${row.workflow}`)
//         if(workflowRes[0].length > 0){
//           row.workflow_detail = workflowRes[0][0];
//         } else {
//           row.workflow_detail = {};
//         }
//         return { ...rest, 
//           finishedProductQty,  // Add the adjusted finishedProductQty
//           partialProductQty,
//           partial_stock,   // Add the adjusted partialProductQty
//           quantity: product_qty,attachments:row.attachments,location: row.locations,workflow_detail:row.workflow_detail };
//       }));
      
//       return modifiedData;
//     } else {
//       return false;
//     }
   
//   }
// };


const getCustomerOrderProductsbyId = async (order_id) => {
  if (!order_id) return false;

  try {
    const currentFacility = FACILITY_ID; // current installation facility id
    const installations = getOtherInstallationDatabases();

    // Quick helpers
    const getDbByFacilityId = (fid) => installations.find((db) => Number(db.id) === Number(fid));
    const getDbNameByWhich = (whichDb) => {
      if (whichDb === 'local') return '';
      const db = installations.find((d) => d.name === whichDb);
      return db?.database_name || '';
    };

    // 1) Get all customer_order_products for this order (no DISTINCT)
    const copRows = await sequelize.query(
      `SELECT id, facility_id
       FROM customer_order_products
       WHERE order_id = :order_id`,
      { type: sequelize.QueryTypes.SELECT, replacements: { order_id } }
    );

    if (!copRows.length) return [];

    // Helper to normalize facility_id (treat null/0 as current)
    const norm = (fid) => (fid == null || fid === 0 ? currentFacility : Number(fid));

    // 2) Partition rows into groups by where the product data should be read from
    const groupMap = new Map(); // whichDb => [ids]

    for (const r of copRows) {
      const fid = norm(r.facility_id);
      const whichDb = (fid === currentFacility)
        ? 'local'
        : (getDbByFacilityId(fid)?.name || 'local');
      if (!groupMap.has(whichDb)) groupMap.set(whichDb, []);
      groupMap.get(whichDb).push(r.id);
    }

    // 3) Build a reusable group query with the right cross-DB joins
    const runGroupQuery = async (ids, whichDb) => {
      if (!ids.length) return [];

      let productDatabase = 'products p';
      let recipeDatabase = 'recipes rc';
      let productCategoryDatabase = 'product_category pc';
      let workflowDatabase = 'onboard_workflows w';

      if (whichDb !== 'local') {
        const dbName = getDbNameByWhich(whichDb);
        if (dbName) {
          productDatabase = `${dbName}.products p`;
          productCategoryDatabase = `${dbName}.product_category pc`;
          workflowDatabase = `${dbName}.onboard_workflows w`;
          recipeDatabase = `${dbName}.recipes rc`;
        }
      }

      const placeholders = ids.map(() => '?').join(',');

      const sql = `
        SELECT
          cust_ord_pro.*,
          co.request_id,
          p.quickbookId,p.formula_cost,
          p.workflow AS workflow,                        -- ensure available later
          p.actual_retail_price,
          cust_ord_pro.install_cost,
          cust_ord_pro.product_markup AS markup,
          p.taxCode,
          p.inhouse,
          p.taxable,
          p.unit,
          p.syncWithQuickbooks,
          p.vpt_number,
          p.unit_qty,
          p.description,
          pc.markup AS RetailMarkup,
          pc.wholesale_markup AS WholesaleMarkup,
          w.name AS workflow_name,
          i.id AS installation_id,
          i.installation_temp_id,
          it.status AS published_status,
          ct.name AS installation_temp_name,
          i.location_id,
          i.complete_status as installation_status,
          rl.id AS request_location_id,
          rc.expected_cost,
          cust_ord_pro.location_quantity AS locationQuantity,
          SUM(
            CASE
              WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 0
              THEN mi.qty ELSE 0
            END
          ) AS finishedProductQty,
          SUM(
            CASE
              WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1
              THEN mi.qty ELSE 0
            END
          ) AS partialProductQty,
          SUM(
            CASE
              WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
                   AND mi.partial = 1
                   AND mi.partial_unit = cust_ord_pro.convertion_unit
              THEN mi.qty ELSE 0
            END
          ) AS partial_stock
        FROM customer_order_products cust_ord_pro
        LEFT JOIN request_locations rl ON rl.order_id = cust_ord_pro.id
        LEFT JOIN customer_orders co ON cust_ord_pro.order_id = co.id
        LEFT JOIN material_inventory mi ON cust_ord_pro.product_id = mi.material_id
        LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
        LEFT JOIN installations i ON i.location_id = rl.id
        LEFT JOIN custom_field_templates ct ON ct.id = i.installation_temp_id
        LEFT JOIN installation_temp_fields it
          ON it.template_id = i.installation_temp_id
         AND it.installation_id = i.id
         LEFT JOIN ${productDatabase} ON p.id = cust_ord_pro.product_id
         LEFT JOIN ${recipeDatabase} ON rc.id = p.recipe_id
        LEFT JOIN ${productCategoryDatabase} ON pc.id = p.category_id
        LEFT JOIN ${workflowDatabase} ON w.id = p.workflow
        WHERE cust_ord_pro.id IN (${placeholders})
        GROUP BY cust_ord_pro.id
      `;

      const result = await sequelize.query(sql, {
        type: sequelize.QueryTypes.SELECT,
        replacements: ids
      });

      // Tag each record with which DB was used (helps later for workflow_detail fetch)
      return result.map(r => ({ ...r,description: r.description ? r.description : r.product_desc , __whichDb: whichDb }));
    };

    const groupPromises = [];
    for (const [whichDb, ids] of groupMap.entries()) {
      groupPromises.push(runGroupQuery(ids, whichDb));
    }
    const groupResults = await Promise.all(groupPromises);
    const merged = groupResults.flat();
    if (!merged.length) return [];

    // 4) Post-process each row (keep your existing logic)
    const parsed = merged.map(parseDecimalFieldsForOrderProducts);

    const modifiedData = await Promise.all(
      parsed.map(async (row) => {
        const { product_qty, __whichDb, ...rest } = row;

        const finishedProductQty = (row.finishedProductQty || 0) + (row.locationQuantity || 0);
        const partialProductQty  = (row.partialProductQty  || 0) + (row.locationQuantity || 0);
        const partial_stock      = (row.partial_stock      || 0) + (row.locationQuantity || 0);

        // request_locations for this COP id
        const [locs] = await sequelize.query(
          `SELECT rl.*, rl.id AS req_loc_id
           FROM request_locations rl
           WHERE rl.order_id = :cop_id`,
          { replacements: { cop_id: row.id } }
        );
        const locations = Array.isArray(locs) ? locs : [];

        // Attachments filtered by location_ids & request
        const attachments = await sequelize.query(
          `SELECT ra.*,
                  CONCAT(u.first_name,' ',u.last_name) AS approved_by_name
             FROM request_attachments ra
             LEFT JOIN users u ON u.id = ra.approved_by
            WHERE ra.status = :status
              AND ra.approved = :approved
              -- AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType)
              AND ra.request_id = :requestId
              AND FIND_IN_SET(:locationId, ra.location_ids)`,
          {
            type: sequelize.QueryTypes.SELECT,
            replacements: {
              status: 1,
              approved: 1,
              imgType: 'img',
              requestId: row.request_id,
              locationId: row.request_location_id || 0
            }
          }
        );

        // Fetch workflow_detail from the SAME DB as product
        let wfPrefix = '';
        if (__whichDb && __whichDb !== 'local') {
          const dbName = getDbNameByWhich(__whichDb);
          if (dbName) wfPrefix = `${dbName}.`;
        }

        let workflow_detail = {};
        if (row.workflow) {
          const [wf] = await sequelize.query(
            `SELECT * FROM ${wfPrefix}onboard_workflows WHERE id = :wid`,
            { replacements: { wid: row.workflow } }
          );
          if (Array.isArray(wf) && wf.length) workflow_detail = wf[0];
        }

        return {
          ...rest,
          quantity: product_qty,
          finishedProductQty,
          partialProductQty,
          partial_stock,
          attachments: Array.isArray(attachments) ? attachments : [],
          location: locations,
          workflow_detail
        };
      })
    );

    // (Optional) sort by COP id for stability
    modifiedData.sort((a, b) => Number(a.id) - Number(b.id));
    return modifiedData;
  } catch (error) {
    console.error('Error in getCustomerOrderProductsbyId:', error);
    return false;
  }
};


const getCustomerOrderProductsbyIdWithLocation = async (order_id) => {
  let query = '';
 
  if (order_id) {
    query = `
      SELECT cust_ord_pro.*,co.request_id,i.id AS installation_id,i.installation_temp_id,it.status AS published_status,ct.name as installation_temp_name,i.location_id,w.name as workflow_name,p.unit,p.unit_qty,cust_ord_pro.location_quantity as locationQuantity,pc.markup as RetailMarkup,pc.wholesale_markup as WholesaleMarkup, rl.id AS request_location_id FROM customer_order_products cust_ord_pro
      left join request_locations rl on rl.order_id = cust_ord_pro.id
      left join customer_orders co on cust_ord_pro.order_id = co.id
      left join installations i on i.location_id = rl.id
      LEFT JOIN custom_field_templates ct on ct.id = i.installation_temp_id
      LEFT JOIN installation_temp_fields it on it.template_id=i.installation_temp_id and it.installation_id = i.id
      left join products p on p.id = cust_ord_pro.product_id
      left join product_category pc on pc.id =p.category_id
      left join onboard_workflows w on w.id = p.workflow
      WHERE cust_ord_pro.order_id = ${order_id} GROUP BY rl.id
    `;

    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
    
    if (result.length >= 1) {
      const newData = result.map((row) => row);
      const data = newData?.map(record => parseDecimalFieldsForOrderProducts(record));
      const modifiedData = await Promise.all(data.map(async (row) => {
        const { product_qty, ...rest } = row;
        console.log("###########",row.id);
        console.log("###########row",row);
        const queryResult = await sequelize.query(`SELECT * FROM request_locations WHERE order_id = ${row.id}`);
        if(queryResult[0].length > 0){
          row.locations = queryResult[0];
        } else {
          row.locations = [];
        }
        const attachmentResult = await sequelize.query(
          `SELECT ra.*, CONCAT(u.first_name,' ',u.last_name) AS approved_by_name FROM request_attachments ra
           LEFT JOIN users u ON u.id = ra.approved_by
           WHERE ra.status = :status 
           AND ra.approved = :approved 
           AND (ra.file_type LIKE 'image/%' OR ra.file_type = :imgType) 
           AND ra.request_id = :requestId 
           AND FIND_IN_SET(:locationId, ra.location_ids)`,
          //  AND FIND_IN_SET(:productId, ra.product_ids)
          {
            type: sequelize.QueryTypes.SELECT,
            replacements: {
              status: 1,
              approved: 1,
              imgType: 'img',
              requestId: row.request_id,
              // productId: row.id,
              locationId: row.request_location_id
            }
          }
        );        
        console.log('attachmentResult', attachmentResult)
        if (attachmentResult && attachmentResult.length > 0) {
          row.attachments = attachmentResult;
        } else {
          row.attachments = [];
        }        
        const workflowRes = await sequelize.query(`select * from onboard_workflows where id = ${row.workflow}`)
        if(workflowRes[0].length > 0){
          row.workflow_detail = workflowRes[0][0];
        } else {
          row.workflow_detail = {};
        }
        return { ...rest, quantity: product_qty,attachments:row.attachments,location: row.locations,workflow_detail:row.workflow_detail };
      }));
      
      return modifiedData;
    } else {
      return false;
    }
   
  }
};
// const getJobCostProductsbyId = async (order_id) => {
//   let query = '';
 
//   if (order_id) {
//     query = `
//       SELECT jc.*,p.unit,p.unit_qty,p.id AS material_id,p.name AS product_name FROM job_costs jc
//       left join material_inventory mi on mi.id = jc.product_id
//       left join products p on mi.material_id = p.id
//       left join projects prj on prj.id = jc.project_id
//       left join customer_orders co on co.request_id = prj.request_id
//       WHERE co.id = ${order_id} AND jc.recipe=0 GROUP BY jc.id
//     `;

//     const result = await sequelize.query(query, {
//       type: sequelize.QueryTypes.SELECT,
//     });
    
//     if (result.length >= 1) {
//       const newData = result.map((row) => row);
//       const data = newData?.map(record => parseDecimalFieldsForOrderProducts(record));
//       const modifiedData = await Promise.all(data.map(async (row) => {
//         const { product_qty, ...rest } = row;
//         console.log("###########",row.id);
//         return row;
//       }));
      
//       return modifiedData;
//     } else {
//       return false;
//     }
//   }
// };

const getJobCostProductsbyId = async (order_id) => {
  let query = '';
 
  if (order_id) {
    query = `
      SELECT jc.*,p.unit,p.retail_price AS material_markup_percentage,p.unit_qty,p.id AS material_id,cop.product_markup,cop.material_notes,p.name AS product_name, cop.id AS customer_order_product_id, mi.partial_unit FROM job_costs jc
      left join material_inventory mi on mi.id = jc.product_id
      left join products p on mi.material_id = p.id
      left join projects prj on prj.id = jc.project_id
      left join customer_orders co on co.request_id = prj.request_id
      left join customer_order_products cop on cop.order_id = co.id AND cop.product_id = p.id
      WHERE co.id = ${order_id} AND jc.recipe=0 GROUP BY jc.id 
    `; // AND cop.id IS NULL

    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
    
    if (result.length >= 1) {
      const newData = result.map((row) => row);
      const data = newData?.map(record => parseDecimalFieldsForOrderProducts(record));
      // const modifiedData = await Promise.all(data.map(async (row) => {
      //   const { product_qty, ...rest } = row;
      //   console.log("###########",row.id);
      //   return row;
      // }));

      const modifiedData = data.map((row) => {
        const { unit_price, product_markup, quantity, total } = row;
      
        const newTotal = product_markup
          ? ((unit_price || 0) + ((unit_price || 0) * (Number(product_markup || 0) / 100))) * (quantity || 0)
          : total;
        const newUnitPrice = product_markup
          ? ((unit_price || 0) + ((unit_price || 0) * (Number(product_markup || 0) / 100)))
          : unit_price;
      
        return {
          ...row,
          unit_price:newUnitPrice ?  Number(newUnitPrice.toFixed(2)) : 0, 
          total: newTotal ? Number(newTotal.toFixed(2)) : 0, 
        };
      });
      
      return modifiedData;      

      // const modifiedData = data.map((row) => {
      //   const {unit_price, product_markup, quantity, total } = row;
      //   const newTotal = product_markup ? ((unit_price + (unit_price * (Number(product_markup||0) / 100))) * quantity)?.toFixed(2) : total;
      //   return {
      //     ...row,
      //     total: newTotal
      //   };
      // });
      
      // return modifiedData;
    } else {
      return false;
    }
  }
};
const getRFPProductByRequest = async (request_id) => {
  let query = '';
 
  if (request_id) {
    query = `
      SELECT cust_ord_pro.* FROM customer_order_products cust_ord_pro
      left join customer_orders c on c.id = cust_ord_pro.order_id
      left join requests r on r.id=c.request_id
      WHERE r.id = ${request_id} and rfp=1
    `;

    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
    
    if (result.length >= 1) {
      return result.map(record => parseDecimalFieldsForOrderProducts(record));
    } else {
      return []
    }
   
  }
};
const getMaterialProductByRequest = async (request_id,bringStockCatalog=false) => {
  let query = '';
  whereCondition = '';
  if(bringStockCatalog){
    whereCondition = ``
  }else{
    whereCondition = `and material=1`
  }
 
  if (request_id) {
    query = `
      SELECT cust_ord_pro.* FROM customer_order_products cust_ord_pro
      left join customer_orders c on c.id = cust_ord_pro.order_id
      left join requests r on r.id=c.request_id
      WHERE r.id = ${request_id} ${whereCondition}
      `;
      // WHERE r.id = ${request_id} and material=1

    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
    
    if (result.length >= 1) {
      return result.map(record => parseDecimalFieldsForOrderProducts(record));
    } else {
      return []
    }
   
  }
};
  const deletedCustomerOrderProduct = async (order_id) => {
    try {
      const result = await CustomerOrder.destroy({
        where: {
          order_id: order_id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };
  
  
  const deletedCustomerOrderProductById = async (order_id, keepIds = []) => {
    try {
      const result = await CustomerOrder.destroy({
        where: {
          order_id,
          id: {
            [Op.notIn]: keepIds.length ? keepIds : [0]
          }
        }
      });

      console.log(`Deleted ${result} customer_order_product record(s) for order_id ${order_id}, except IDs:`, keepIds);
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };

  const deleteCustomerOrderProductById = async (productId) => {
    try {
      const result = await CustomerOrder.destroy({
        where: {
          id: productId
        }
      });
      return result > 0;
    } catch (error) {
      console.error('Error deleting customer order product by ID:', error);
      return false;
    }
  };

  const getCustomerOrderProductsbyProductId = async (product_id) => {
    let query = '';
   
    if (product_id) {
      query = ` SELECT cust_ord_pro.* FROM customer_order_products cust_ord_pro
        WHERE cust_ord_pro.product_id = ${product_id}
      `;
  
      const result = await sequelize.query(query, {
        type: sequelize.QueryTypes.SELECT,
      });
      
      if (result.length >= 1) {
        const newData = result.map((row) => row);
        const modifiedData = newData?.map(record => parseDecimalFieldsForOrderProducts(record));
        return modifiedData;
      } else {
        return false;
      }
    }
  };

  const getCustomerOrderProductsbyRequestId = async (request_id) => {
    const query = `
      SELECT cust_ord_pro.*,r.origin_facility_id
      FROM customer_order_products cust_ord_pro
      LEFT JOIN customer_orders co ON co.id = cust_ord_pro.order_id
      LEFT JOIN requests r ON r.id = co.request_id
      WHERE r.id = :request_id
    `;
  
    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
      replacements: { request_id }, // prevent SQL injection
    });
  
    if (result.length >= 1) {
      const modifiedData = result.map(record =>
        parseDecimalFieldsForOrderProducts(record)
      );
      return modifiedData;
    } else {
      return false;
    }
  };

  const getCustomerOrderProductsbyProduct = async (request_id,product_id) => {
    const query = `
      SELECT cust_ord_pro.*,r.origin_facility_id
      FROM customer_order_products cust_ord_pro
      LEFT JOIN customer_orders co ON co.id = cust_ord_pro.order_id
      LEFT JOIN requests r ON r.id = co.request_id
      WHERE cust_ord_pro.product_id = :product_id AND r.id = :request_id
    `;
  
    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
      replacements: { request_id,product_id }, // prevent SQL injection
    });
  
    if (result.length >= 1) {
      const modifiedData = result.map(record =>
        parseDecimalFieldsForOrderProducts(record)
      );
      return modifiedData;
    } else {
      return false;
    }
  };

  const getCustomerOrderProductStockCatDetailsByMaterialInvId = async (project_id,material_inv_id) => {
    try {
      const result = await sequelize.query(
        `
        SELECT cop.*
        FROM customer_order_products AS cop
        LEFT JOIN customer_orders AS co ON cop.order_id = co.id
        LEFT JOIN projects AS p ON co.request_id = p.request_id
        WHERE p.id = :project_id AND cop.materialInvId =${material_inv_id}
        `,
        {
          replacements: { project_id }, // ✅ Prevents SQL injection
          type: sequelize.QueryTypes.SELECT,
        }
      );
  
      return result.length >= 1 ? result : [];
    } catch (error) {
      console.error('Error getting customer order product details by project id:', error);
      return [];
    }
  };
  
  const deleteAllCustomerOrderProductsByOrderId = async (orderId, transaction = null) => {
    try {
      const result = await CustomerOrder.destroy({
        where: { order_id: orderId },
        transaction
      });
  
      return result > 0; // returns true if any rows deleted
    } catch (error) {
      console.error('Error deleting all customer order products by order_id:', error);
      return false;
    }
  };

module.exports={
    CustomerOrderProductsModel:CustomerOrder,
    createCustomerOrder,
    getCustomerOrderProductsbyId,
    getCustomerOrderProductsbyIdWithLocation,
    getJobCostProductsbyId,
    deletedCustomerOrderProduct,
    getRFPProductByRequest,
    getMaterialProductByRequest,
    getCustomerOrderProductByOrderId,
    updateCustomerOrderProduct,
    deletedCustomerOrderProductById,
    deleteCustomerOrderProductById,
    getCustomerOrderProductsbyProductId,
    getCustomerOrderProductsbyRequestId,
    getCustomerOrderProductsbyProduct,
    getCustomerOrderProductStockCatDetailsByMaterialInvId,
    getProductDetailsById,
    deleteAllCustomerOrderProductsByOrderId
}