const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const { empty } = require('../Helpers/Common')
const fs = require('node:fs');
const util = require('node:util');
const { pipeline } = require('node:stream');
const { Identity } = require("twilio/lib/twiml/VoiceResponse");
const pump = util.promisify(pipeline);
const Client = require("../models/client");
const { values } = require("@babel/runtime/regenerator");
const { FACILITY_ID } = require("../config/SplitManagementDb");


const Products = sequelize.define('products', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.TEXT, allowNull: false },
    barcode: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    description: { type: Sequelize.TEXT, allowNull: true },
    vendor_id: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    vpt_number: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    ul_number: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    secondary_vendor_id: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    secondary_sku: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    thumbnail: { type: Sequelize.STRING, allowNull: true,defaultValue: 'default.jpg' },
    price: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: 0.00 },
    retail_price: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    actual_retail_price: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    install_cost: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    formula_cost: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    pf_retail_markup: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    special_price: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    unit: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    material_types: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    unit_qty: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    start_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    end_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    category_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    facility_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    install_labour_quantity:{type:Sequelize.INTEGER,allowNull:true, defaultValue:null},
    sub_category_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    qb_category:{ type: Sequelize.INTEGER, allowNull: true,defaultValue: null },
    qb_sub_category:{ type: Sequelize.INTEGER, allowNull: true,defaultValue: null },

    qb_income_account:{ type: Sequelize.INTEGER, allowNull: true,defaultValue: null },
    qb_expense_account:{ type: Sequelize.INTEGER, allowNull: true,defaultValue: null },

    tags: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    workflow: { type: Sequelize.INTEGER, allowNull: true },
    min_value: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    max_value: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    meterial: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    isNew: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    twilio_field_value_sid: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    tax_category: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    recipe_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    inhouse: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    quickbookId:{ type: Sequelize.INTEGER, allowNull: true },
    syncWithQuickbooks:{ type: Sequelize.INTEGER, allowNull: false,defaultValue: 0 },
    account_type:{ type: Sequelize.STRING, allowNull: false,defaultValue: '1' },
    isAdditionalProduct:{ type: Sequelize.INTEGER, allowNull: false,defaultValue: 0 },
    isTrafficCatalog:{ type: Sequelize.INTEGER, allowNull: false,defaultValue: 0 },
    taxable: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    markup: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: 1 },
    origin_catalog: { type: Sequelize.STRING(50), allowNull: true, defaultValue: null },
    sub_contract_price: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    in_stock_no: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    taxCodeId: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    taxCode: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    lead_time: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    bin_location: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    last_ordered_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    transfered_product_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
}, { timestamps: false, tableName: 'products' })

const ClientStockLogs = sequelize.define('client_stock_logs', {
  id: { type: Sequelize.INTEGER, allowNull: false, autoIncrement: true, primaryKey: true },
  catalog_source: { type: Sequelize.INTEGER, allowNull: false },
  added_by: { type: Sequelize.INTEGER, allowNull: false },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
  catalog_id: { type: Sequelize.INTEGER, allowNull: false },
  catalog_log_message: { type: Sequelize.STRING(600), allowNull: false }
}, {
  timestamps: false,
  tableName: 'client_stock_logs'
});


const LabourCostServices = sequelize.define('labour_cost_services', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    description: { type: Sequelize.TEXT, allowNull: true },
    service_type: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    markup: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    total_cost: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    cost: { type: Sequelize.FLOAT(12, 2), allowNull: true, defaultValue: null },
    workflow: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    inhouse: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    originInhouse: { type: Sequelize.TINYINT, allowNull: true, defaultValue: null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    quickbookId:{ type: Sequelize.INTEGER, allowNull: true },
    syncWithQuickbooks:{ type: Sequelize.INTEGER, allowNull: false,defaultValue: 0 },
    qb_income_account :{ type: Sequelize.INTEGER, allowNull: true,defaultValue: null },
    qb_expense_account :{ type: Sequelize.INTEGER, allowNull: true,defaultValue: null },
    avatarUrl: { type: Sequelize.TEXT, allowNull: true },
    makeAsTask: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    makeInstallCard: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    jobCost: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    taxable: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    taxCodeId: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    taxCode: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    isNew: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
}, { timestamps: false, tableName: 'labour_cost_services' })

const LabourCostServiceTypes = sequelize.define('labour_cost_services_type', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName: 'labour_cost_services_type' })

const MaterialInventory = sequelize.define('material_inventory', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    barcode: { 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 },
    unit_cost: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    actual_qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    actual_unit_qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    unit_qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    shipping_cost: { type: Sequelize.FLOAT, allowNull: false, defaultValue: 0 },
    service_cost: { type: Sequelize.FLOAT, allowNull: false, defaultValue: 0 },
    actual_total_cost: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    total_cost: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    source: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    receive_order_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    partial: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    partial_unit: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    parent_finished_product_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    autofill: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
}, { timestamps: false, tableName: 'material_inventory' })

const DeletedMaterialInventory = sequelize.define('deleted_material_inventory', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    barcode: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    material_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    unit_cost: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    actual_qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    actual_unit_qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    unit_qty: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    actual_total_cost: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    total_cost: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    source: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    receive_order_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    partial: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    partial_unit: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    parent_finished_product_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    autofill: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
}, { timestamps: false, tableName: 'deleted_material_inventory' })

const ReceiveOrders = sequelize.define('receive_orders', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    orderType: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 1 },
    vendor_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    project_type: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    transaction_id: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    received_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    expected_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    include_tax: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    sub_total: { type: Sequelize.FLOAT, allowNull: false },
    tax: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    grand_total: { type: Sequelize.FLOAT, allowNull: false },
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    payment_mode: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    payment_status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
    syncWithQuickbooks:{ type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    quickbookId:{ type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    doc_number:{ type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    qb_bill_id:{ type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    notSyncedMessage:{ type: Sequelize.STRING, allowNull: true, defaultValue: null },
    tax_category:{type: Sequelize.INTEGER, allowNull: true, defaultValue: null},
    sales_order_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    ship_via:{ type: Sequelize.STRING, allowNull: true, defaultValue: null },
    notes:{ type: Sequelize.TEXT, allowNull: true, defaultValue: null }
}, { timestamps: false, tableName: 'receive_orders' })

const ReceiveOrderServices = sequelize.define('receive_order_services', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    receive_order_id: { type: Sequelize.INTEGER, allowNull: false },
    service_id: { type: Sequelize.INTEGER, allowNull: false },
    price: { type: Sequelize.FLOAT(14,2), allowNull: false, defaultValue: 0.00 },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
}, { timestamps: false, tableName: 'receive_order_services' })

const ProductDataSet = sequelize.define('product_data_set', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    min_value: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    max_value: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
}, { timestamps: false, tableName: 'product_data_set' })

const ProductCategory = sequelize.define('product_category', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.CHAR, allowNull: false },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    category_type: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 2 },
    markup: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    wholesale_markup: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    qbCategoryId: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName: 'product_category' })

const ProductSubCategory = sequelize.define('product_sub_category', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.CHAR, allowNull: false },
    category_id: { type: Sequelize.INTEGER, allowNull: false },
    markup: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    wholesale_markup: { type: Sequelize.FLOAT, allowNull: true, defaultValue: null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    qbSubCategoryId: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
}, { timestamps: false, tableName: 'product_sub_category' })

const InventoryAudit = sequelize.define('inventory_audit', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    material_inventory_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    material_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    reason: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    added_by: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    timestamp: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
}, { timestamps: false, tableName: 'inventory_audit' })

const TaxCategory = sequelize.define('tax_category', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING(200), allowNull: false },
    value: { type: Sequelize.FLOAT(12,2), allowNull: false },
    type: { type: Sequelize.INTEGER, allowNull: false, defaultValue:1 },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue:1 },
}, { timestamps: false, tableName: 'tax_category' })

const MainLabelSettings = sequelize.define('main_label_settings', {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    scale: {
      type: Sequelize.FLOAT,
      allowNull: true,
      defaultValue: 0.9
    },
    paddingTop: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 10
    },
    paddingRight: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 12
    },
    paddingBottom: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 0
    },
    paddingLeft: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 0
    },
    marginTop: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 0
    },
    marginRight: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 0
    },
    marginBottom: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 0
    },
    marginLeft: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 0
    },
    qrSize: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 45
    },
    width: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: '62'
    },
    height: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: '20'
    },
    pageWidth: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: '210'
    },
    pageHeight: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: '297'
    },
    titleFontSize: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 8
    },
    skuFontSize: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 7
    },
  }, {
    timestamps: false,
    tableName: 'main_label_settings' 
  });
  
const SmallLabelSettings = sequelize.define('small_label_settings', {
id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true
},
scale: {
    type: Sequelize.FLOAT,
    allowNull: true,
    defaultValue: 1
},
qrSize: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: 50
},
paddingTop: Sequelize.INTEGER,
paddingRight: Sequelize.INTEGER,
paddingBottom: Sequelize.INTEGER,
paddingLeft: Sequelize.INTEGER,
marginTop: Sequelize.INTEGER,
marginRight: Sequelize.INTEGER,
marginBottom: Sequelize.INTEGER,
marginLeft: Sequelize.INTEGER,
width: {
    type: Sequelize.STRING,
    allowNull: true,
    defaultValue: '0.9'
},
height: {
    type: Sequelize.STRING,
    allowNull: true,
    defaultValue: '0.8'
},
pageWidth: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: '210'
    },
    pageHeight: {
      type: Sequelize.STRING,
      allowNull: true,
      defaultValue: '297'
    },
    smallTitleFontSize: {
      type: Sequelize.INTEGER,
      allowNull: true,
      defaultValue: 6
    }
}, {
timestamps: false,
tableName: 'small_label_settings'
});
  
  
  

const UNITS = [
    { id: 1, name: 'Square feet', short_name: 'Sq ft', unit_id: 1 },
    { id: 2, name: 'Linear feet', short_name: 'Lin ft', unit_id: 1 },
    { id: 3, name: 'Square inches', short_name: 'Sq in', unit_id: 1 },
    { id: 4, name: 'Unit', short_name: 'Unit', unit_id: 2 },
    { id: 5, name: 'Gallons', short_name: 'Gal', unit_id: 3 },
    { id: 6, name: 'Quarts', short_name: 'Qt', unit_id: 3 },
    { id: 7, name: 'Milliliter', short_name: 'ml', unit_id: 3 },
    { id: 8, name: 'Liter', short_name: 'L', unit_id: 3 },
    { id: 9, name: 'Pint', short_name: 'pt', unit_id: 3 },
    { id: 10, name: 'Half Pint', short_name: 'half-pt', unit_id: 3 },
    { id: 11, name: 'Ounce', short_name: 'oz', unit_id: 3 },
    { id: 12, name: 'Kilogram', short_name: 'kg', unit_id: 4 },
    { id: 13, name: 'Gram', short_name: 'g', unit_id: 4 },
    { id: 14, name: 'Milligram', short_name: 'mg', unit_id: 4 },
    { id: 15, name: 'Each', short_name: 'EA', unit_id: 2 },
  
  ];

  const MATERIALTYPES = [
    { id: 1, name: 'Inventory', },
    { id: 2, name: 'Non-Inventory', },
    { id: 3, name: 'Services', },
]

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;
    return orderProduct;
}

const updateDataSet = async (data, id) => {
    try {
        let result = await ProductDataSet.update(data, {
            where: {
                id: id
            },
            returning: true
        });
        return result

    } catch (error) {
        throw error;
    }
};
const StockUpdate = async (stock, id) => {
    try {
        let result = await Products.update({
            in_stock_no:stock
        }, {
            where: {
                id: id
            },
            returning: true
        });
        return result

    } catch (error) {
        throw error;
    }
};
const getDataSet = async () => {
    try {
        let result = await sequelize.query(`select ds.* from product_data_set as ds`)
        if (!empty(result)) {
            return result[0]
        } else {
            return []
        }
    } catch (error) {
        console.log(error)
    }
}
const getProductById = async (id) => {
    try {
        let results = await sequelize.query(
            ` SELECT p.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, `
            + ` SUM ( CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END ) AS total_qty, `
            // + ` MAX(mi.unit_cost) AS max_price `
            // + ` MAX(CASE WHEN mi.qty > 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
            + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
            // + ` MAX(CASE WHEN mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,  `
            + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, `
            // + ` SUM(CASE WHEN 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 = 0 THEN mi.qty ELSE 0 END) AS finishedProductQty, `
            // + ` SUM(CASE WHEN 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 THEN mi.qty ELSE 0 END) AS partialProductQty `
            + ` FROM products p `
            + ` LEFT JOIN material_inventory mi ON p.id = mi.material_id `
            + ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id `
            + ` LEFT JOIN users u ON p.added_by = u.id `
            + ` WHERE p.id = :id AND p.status != :status `,
            {
                replacements: {
                    id: id,
                    status: 2
                },
                type: Sequelize.QueryTypes.SELECT
            }
        );

        if (results.length === 1) {
            return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const checkMaterialCategoryExist=async(category)=>{
    let query=await sequelize.query(` select id from product_category where name="${category}" and category_type=2`)
    // let query=await sequelize.query(` select id from product_category where name="${category}" and category_type=1`)
    if(query[0].length>0){
        return query[0][0].id;
    }
    else{
        return false;
    }
}

const checkCatalogSubCategoryExist=async(subCategoryId)=>{
    let query=await sequelize.query(` select * from product_sub_category where id="${subCategoryId}"`)
    console.log("queryqueryquery",query)
    if(query[0].length>0){
        return query[0][0];
    }
    else{
        return false;
    }
}
const checkMaterialSubCategoryExist=async(subCategory,category)=>{
    let query=await sequelize.query(` select pc.id from product_sub_category pc 
    left join product_category c on c.category_type=pc.id
    where pc.name="${subCategory}"  and pc.category_id=${category}`)
    if(query[0].length>0){
        return query[0][0].id;
    }
    else{
        return false;
    }
}
const checkCatalogCategoryExist=async(category)=>{
    let query=await sequelize.query(` select * from product_category where name="${category}" and category_type=1`)
    if(query[0].length>0){
        return query[0][0];
    }
    else{
        return false;
    }
}
const checkWorkflowExist=async(workflow)=>{
    let query=await sequelize.query(` select id from onboard_workflows where name="${workflow}" and status=4`)
    if(query[0].length>0){
        return query[0][0].id;
    }
    else{
        return false;
    }
}
const checkRecipeExist=async(recipe)=>{
    let query=await sequelize.query(` select id,expected_cost,expected_total_service_cost,expected_material_cost,total_retail_markup from recipes where name="${recipe}" and status=1`)
    if(query[0].length>0){
        return query[0][0];
    }
    else{
        return false;
    }
}
const getAllProducts = 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 and p.status != 3`;
        }
        let query = await sequelize.query(
            ` SELECT p.*,pc.name as category,psc.name as sub_category, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, ` +
            ` TRIM(BOTH ',' FROM CONCAT_WS(',', ` +
            `   NULLIF(p.barcode, ''), ` +
            `   GROUP_CONCAT(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0 AND mi.barcode IS NOT NULL AND mi.barcode != '' THEN mi.barcode END ORDER BY mi.barcode SEPARATOR ',') ` +
            ` )) AS barcode, ` +
            ` 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, ` +
            ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, ` +
            ` (SELECT mi_max.id FROM material_inventory mi_max LEFT JOIN receive_orders ro_max ON ro_max.id = mi_max.receive_order_id WHERE mi_max.material_id = p.id AND (mi_max.source = 0 OR (mi_max.source = 1 AND ro_max.status != 0)) AND mi_max.qty > 0 AND mi_max.partial = 0 ORDER BY mi_max.unit_cost DESC LIMIT 1) AS max_price_mi_id, ` +
            ` SUM(DISTINCT 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, ` +
            ` rc.expected_material_cost_with_markup, rc.expected_service_cost_with_markup,` +
            ` rc.expected_install_labor_cost, rc.expected_install_labor_cost_with_markup ` +
            ` 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 material_inventory mi ON p.id = mi.material_id ` +
            ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id ` +
            ` LEFT JOIN recipes rc ON p.recipe_id = rc.id ` +
            ` LEFT JOIN onboard_workflows ow ON p.workflow = ow.id ` +
            ` WHERE ${whereCondition} ` +
            ` GROUP BY p.id ` +
            ` ORDER BY ` +
            `   CASE WHEN p.ul_number IS NULL OR p.ul_number = '' THEN 1 ELSE 0 END, ` +
            `   p.ul_number ASC, ` +
            `   p.id DESC`,
        );

        // let query = await sequelize.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, ` +
        //     ` rc.expected_material_cost_with_markup, rc.expected_service_cost_with_markup, rc.install_labour_cost_with_markup` +
        //     ` 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`,
        // );

        let data;
        
        if (query.length > 0) {
            data = query[0];
            
            // Calculate install labour cost for products with recipes
            for (const product of data) {
                if (product.recipe_id) {
                    try {
                        // Get recipe details with install labour cost calculation
                        const recipeQuery = await sequelize.query(
                            `SELECT sr.*, l.cost, l.markup FROM services_recipe sr 
                             LEFT JOIN labour_cost_services l ON l.id = sr.service_id 
                             WHERE sr.recipe_id = ${product.recipe_id} AND sr.status = 1`
                        );
                        
                        if (recipeQuery[0] && recipeQuery[0].length > 0) {
                            let installLabourCostWithMarkup = 0;
                            
                            recipeQuery[0].forEach(service => {
                                if (service.service_id === 10) { // Install labour service
                                    const serviceCost = service.cost || 0;
                                    const quantity = service.quantity || 1;
                                    const markup = service.markup || 0;
                                    
                                    const baseCost = serviceCost * quantity;
                                    const costWithMarkup = baseCost + (baseCost * markup / 100);
                                    installLabourCostWithMarkup += costWithMarkup;
                                }
                            });
                            
                            product.install_labour_cost_with_markup = installLabourCostWithMarkup;
                        } else {
                            product.install_labour_cost_with_markup = 0;
                        }
                    } catch (error) {
                        console.log('Error calculating install labour cost for product:', product.id, error);
                        product.install_labour_cost_with_markup = 0;
                    }
                } else {
                    product.install_labour_cost_with_markup = 0;
                }
            }
            
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const getAllFilteredProducts = async (all = false, material, options = {}) => {
    try {
        const {
            page = 1,
            limit = false,
            search = '',
            selectedTab = null,
            searchColumn = null,
        } = options || {};

        const pageNumber = Number(page) > 0 ? Number(page) : 1;
        const limitNumber = Number(limit) > 0 ? Number(limit) : 0;
        const offset = limitNumber > 0 ? (pageNumber - 1) * limitNumber : 0;
        const replacements = {};
        const whereConditions = [
            all ? `p.status != 2` : `p.status = 1`,
        ];

        if (material) {
            whereConditions.push(`p.meterial = 1`);
        } else {
            whereConditions.push(`p.meterial = 0`);
            whereConditions.push(`p.status != 3`);
        }

        switch (selectedTab) {
            case 'in_house':
                whereConditions.push(`p.inhouse = 1`);
                whereConditions.push(`p.status != 0`);
                whereConditions.push(`COALESCE(p.isTrafficCatalog, 0) != 1`);
                break;
            case 'time_and_material':
                whereConditions.push(`p.inhouse = 2`);
                whereConditions.push(`p.status != 0`);
                whereConditions.push(`COALESCE(p.isTrafficCatalog, 0) != 1`);
                break;
            case 'inactive_catalogs':
                whereConditions.push(`p.status = 0`);
                break;
            case 'traffic_catalog':
                whereConditions.push(`COALESCE(p.isTrafficCatalog, 0) = 1`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'in_stock_catalog':
                whereConditions.push(`p.inhouse = 4`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'split_catalog':
                whereConditions.push(`p.inhouse = 6`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'client_owned_catalog':
                whereConditions.push(`p.inhouse = 5`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'estimate_catalog':
                whereConditions.push(`p.inhouse = 7`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'sub_contract':
                whereConditions.push(`p.inhouse = 0`);
                whereConditions.push(`p.status != 0`);
                whereConditions.push(`COALESCE(p.isTrafficCatalog, 0) != 1`);
                break;
            default:
                break;
        }

        const normalizedSearch = String(search || '').toLowerCase().replace(/\s+/g, ' ').trim();
        const trimmedSearch = String(search || '').trim();
        const searchableFields = {
            id: `CAST(p.id AS CHAR)`,
            name: `COALESCE(p.name, '')`,
            tags: `COALESCE(p.tags, '')`,
            description: `COALESCE(REPLACE(REPLACE(p.description, '\r', ' '), '\n', ' '), '')`,
            vpt_number: `COALESCE(p.vpt_number, '')`,
            ul_number: `COALESCE(p.ul_number, '')`,
            category: `COALESCE(pc.name, '')`,
            sub_category: `COALESCE(psc.name, '')`,
            workflow_name: `COALESCE(ow.name, '')`,
        };

        if (normalizedSearch) {
            replacements.searchLike = `%${normalizedSearch}%`;

            const barcodeSearchCondition = `(
                LOWER(COALESCE(p.barcode, '')) LIKE :searchLike
                OR EXISTS (
                    SELECT 1
                    FROM material_inventory mi_search
                    LEFT JOIN receive_orders ro_search ON ro_search.id = mi_search.receive_order_id
                    WHERE mi_search.material_id = p.id
                    AND (mi_search.source = 0 OR (mi_search.source = 1 AND ro_search.status != 0))
                    AND mi_search.status != 0
                    AND mi_search.barcode IS NOT NULL
                    AND mi_search.barcode != ''
                    AND LOWER(mi_search.barcode) LIKE :searchLike
                )
            )`;

            if (searchColumn && searchColumn !== 'all') {
                if (searchColumn === 'barcode') {
                    whereConditions.push(barcodeSearchCondition);
                } else if (searchableFields[searchColumn]) {
                    whereConditions.push(`LOWER(${searchableFields[searchColumn]}) LIKE :searchLike`);
                }
            } else {
                const searchConditions = [
                    `LOWER(${searchableFields.id}) LIKE :searchLike`,
                    `LOWER(${searchableFields.name}) LIKE :searchLike`,
                    `LOWER(${searchableFields.tags}) LIKE :searchLike`,
                    `LOWER(${searchableFields.description}) LIKE :searchLike`,
                    `LOWER(${searchableFields.vpt_number}) LIKE :searchLike`,
                    `LOWER(${searchableFields.ul_number}) LIKE :searchLike`,
                    barcodeSearchCondition,
                ];

                if (/^\d+$/.test(trimmedSearch)) {
                    replacements.exactId = trimmedSearch;
                    searchConditions.unshift(`CAST(p.id AS CHAR) = :exactId`);
                }

                whereConditions.push(`(${searchConditions.join(' OR ')})`);
            }
        }

        const whereClause = whereConditions.join(' AND ');
        const baseFromClause =
            ` 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 material_inventory mi ON p.id = mi.material_id ` +
            ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id ` +
            ` LEFT JOIN recipes rc ON p.recipe_id = rc.id ` +
            ` LEFT JOIN onboard_workflows ow ON p.workflow = ow.id `;

        const selectClause =
            ` SELECT p.*,pc.name as category,psc.name as sub_category, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, ` +
            ` TRIM(BOTH ',' FROM CONCAT_WS(',', ` +
            `   NULLIF(p.barcode, ''), ` +
            `   GROUP_CONCAT(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0 AND mi.barcode IS NOT NULL AND mi.barcode != '' THEN mi.barcode END ORDER BY mi.barcode SEPARATOR ',') ` +
            ` )) AS barcode, ` +
            ` 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, ` +
            ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, ` +
            ` (SELECT mi_max.id FROM material_inventory mi_max LEFT JOIN receive_orders ro_max ON ro_max.id = mi_max.receive_order_id WHERE mi_max.material_id = p.id AND (mi_max.source = 0 OR (mi_max.source = 1 AND ro_max.status != 0)) AND mi_max.qty > 0 AND mi_max.partial = 0 ORDER BY mi_max.unit_cost DESC LIMIT 1) AS max_price_mi_id, ` +
            ` SUM(DISTINCT 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, ` +
            ` rc.expected_material_cost_with_markup, rc.expected_service_cost_with_markup,` +
            ` rc.expected_install_labor_cost, rc.expected_install_labor_cost_with_markup `;

        const groupedQuery =
            `${selectClause} ${baseFromClause} WHERE ${whereClause} GROUP BY p.id`;

        const countResult = await sequelize.query(
            `SELECT COUNT(DISTINCT p.id) AS totalCount ${baseFromClause} WHERE ${whereClause}`,
            {
                replacements,
                type: sequelize.QueryTypes.SELECT,
            }
        );

        const totalCount = Number(countResult?.[0]?.totalCount || 0);
        const paginatedQuery =
            `${groupedQuery} ORDER BY ` +
            ` CASE WHEN p.ul_number IS NULL OR p.ul_number = '' THEN 1 ELSE 0 END, ` +
            ` p.ul_number ASC, ` +
            ` p.id DESC` +
            (limitNumber > 0 ? ` LIMIT ${limitNumber} OFFSET ${offset}` : '');

        const data = await sequelize.query(paginatedQuery, {
            replacements,
            type: sequelize.QueryTypes.SELECT,
        });

        const recipeIds = [...new Set((data || []).map((product) => product.recipe_id).filter(Boolean))];
        const installLabourCostMap = new Map();

        if (recipeIds.length > 0) {
            const recipeServices = await sequelize.query(
                `SELECT sr.recipe_id, sr.quantity, l.cost, l.markup
                 FROM services_recipe sr
                 LEFT JOIN labour_cost_services l ON l.id = sr.service_id
                 WHERE sr.recipe_id IN (:recipeIds) AND sr.status = 1 AND sr.service_id = 10`,
                {
                    replacements: { recipeIds },
                    type: sequelize.QueryTypes.SELECT,
                }
            );

            recipeServices.forEach((service) => {
                const serviceCost = Number(service.cost) || 0;
                const quantity = Number(service.quantity) || 1;
                const markup = Number(service.markup) || 0;
                const baseCost = serviceCost * quantity;
                const costWithMarkup = baseCost + (baseCost * markup / 100);
                installLabourCostMap.set(
                    service.recipe_id,
                    (installLabourCostMap.get(service.recipe_id) || 0) + costWithMarkup
                );
            });
        }

        data.forEach((product) => {
            product.install_labour_cost_with_markup = installLabourCostMap.get(product.recipe_id) || 0;
        });

        return {
            results: data || [],
            totalCount,
            pagination: {
                currentPage: pageNumber,
                limit: limitNumber || (data || []).length,
                totalCount,
                totalPages: limitNumber > 0 ? Math.ceil(totalCount / limitNumber) : ((data || []).length > 0 ? 1 : 0),
            },
        };
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

const getAllProductsNew = async (all = false, material) => {
    try {
      let whereCondition = all ? `p.status != 2` : `p.status = 1`;
  
      if (material) {
        whereCondition += ` AND p.meterial = 1`;
      } else {
        whereCondition += ` AND p.meterial = 0 AND p.status != 3`;
      }
  
      // ✅ 1) BASE PRODUCT QUERY (KEEPING your joins, but REMOVING material_inventory join)
      // NOTE: we are NOT removing any of your existing joins EXCEPT mi/ro, because that join is what makes it unstable.
        const query = await sequelize.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,
            rc.expected_material_cost_with_markup,
            rc.expected_service_cost_with_markup,
            rc.expected_install_labor_cost,
            rc.expected_install_labor_cost_with_markup
            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
            CASE WHEN p.ul_number IS NULL OR p.ul_number = '' THEN 1 ELSE 0 END,
            p.ul_number ASC,
            p.id DESC
            `
        );
  
      if (!query?.length || !query[0]?.length) return [];
  
      const data = query[0];
  
      const productIds = data.map((p) => p.id);
      const recipeIds = data.filter((p) => p.recipe_id).map((p) => p.recipe_id);
  
      // ✅ 2) INVENTORY AGGREGATION (replaces old GROUP BY with join)
      // This produces the SAME values you used in the big query:
      // - inv_barcodes (inventory barcodes only)
      // - max_price
      // - max_price_mi_id (best row id by unit_cost desc)
      // - finishedProductQty
      let invAgg = [];
      if (productIds.length > 0) {
        invAgg = await sequelize.query(
          `
          SELECT 
            mi.material_id,
  
            -- inventory barcodes only (p.barcode will be merged in JS)
            GROUP_CONCAT(
              DISTINCT CASE 
                WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
                  AND mi.status != 0
                  AND mi.barcode IS NOT NULL
                  AND mi.barcode != ''
                THEN mi.barcode
              END
              ORDER BY mi.barcode SEPARATOR ','
            ) AS inv_barcodes,
  
            MAX(
              CASE 
                WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
                  AND mi.qty > 0
                  AND mi.partial = 0
                THEN mi.unit_cost
                ELSE NULL
              END
            ) AS max_price,
  
            -- pick the id of the max unit_cost row (MySQL 5.7 compatible trick)
            SUBSTRING_INDEX(
              GROUP_CONCAT(
                CASE 
                  WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
                    AND mi.qty > 0
                    AND mi.partial = 0
                  THEN mi.id
                END
                ORDER BY mi.unit_cost DESC SEPARATOR ','
              ),
              ',', 1
            ) AS max_price_mi_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
  
          FROM material_inventory mi
          LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
          WHERE mi.material_id IN (:productIds)
          GROUP BY mi.material_id
          `,
          {
            replacements: { productIds },
            type: sequelize.QueryTypes.SELECT,
          }
        );
      }
  
      const invMap = {};
      invAgg.forEach((row) => {
        invMap[row.material_id] = row;
      });
  
      // ✅ merge inventory results back into products (same output fields as old query)
      data.forEach((p) => {
        const inv = invMap[p.id];
  
        const invBarcodes = inv?.inv_barcodes || '';
        const pBarcode = p?.barcode || ''; // product barcode column exists on p.*
  
        // replicate: TRIM(BOTH ',' FROM CONCAT_WS(',', NULLIF(p.barcode,''), inv_barcodes))
        const mergedBarcode = [pBarcode && pBarcode.trim() ? pBarcode.trim() : null, invBarcodes && invBarcodes.trim() ? invBarcodes.trim() : null]
          .filter(Boolean)
          .join(',')
          .replace(/^,|,$/g, '');
  
        p.barcode = mergedBarcode;
        p.max_price = inv?.max_price ?? null;
        p.max_price_mi_id = inv?.max_price_mi_id ? Number(inv.max_price_mi_id) : null;
        p.finishedProductQty = inv?.finishedProductQty ? Number(inv.finishedProductQty) : 0;
      });
  
      // ✅ 3) BULK FETCH SERVICES (same as yours)
      let allServices = [];
      if (productIds.length > 0) {
        allServices = await sequelize.query(
          `
          SELECT 
            ls.*,
            ls.total_cost AS unit_cost,
            sr.quantity,
            sr.quantity AS prQty,
            p.id AS product_id,
            r.id AS recipe_id,
            ls.markup AS originalMarkup
          FROM products p
          INNER JOIN recipes r ON r.id = p.recipe_id
          INNER JOIN services_recipe sr ON sr.recipe_id = r.id
          INNER JOIN labour_cost_services ls ON ls.id = sr.service_id
          WHERE p.id IN (:productIds)
          `,
          {
            replacements: { productIds },
            type: sequelize.QueryTypes.SELECT,
          }
        );
      }
  
      // ✅ 4) BULK FETCH MATERIALS (same as yours)
      let allMaterials = [];
      if (productIds.length > 0) {
        allMaterials = await sequelize.query(
          `
          SELECT 
            p2.*,
            p2.price AS unit_cost,
            (
              SELECT MAX(minv.unit_cost)
              FROM material_inventory minv
              WHERE minv.material_id = p2.id
                AND minv.partial = (cr.stockType - 1)
            ) AS max_unit_cost,
            cr.quantity,
            cr.convertionUnit,
            cr.quantity AS prQty,
            cr.stockType,
            p.id AS product_id,
            r.id AS recipe_id
          FROM products p
          INNER JOIN recipes r ON r.id = p.recipe_id
          INNER JOIN catelog_recipe cr ON cr.recipe_id = r.id
          INNER JOIN products p2 ON p2.id = cr.product_id
          WHERE p.id IN (:productIds)
          `,
          {
            replacements: { productIds },
            type: sequelize.QueryTypes.SELECT,
          }
        );
      }
  
      // ✅ 5) group services/materials by product_id
      const serviceMap = {};
      allServices.forEach((s) => {
        (serviceMap[s.product_id] ||= []).push(s);
      });
  
      const materialMap = {};
      allMaterials.forEach((m) => {
        (materialMap[m.product_id] ||= []).push(m);
      });
  
      // ✅ 6) Install labour cost (OPTIMIZED: no per-product filter loop)
      let recipeInstallMap = {};
      if (recipeIds.length > 0) {
        const allRecipeServices = await sequelize.query(
          `
          SELECT sr.recipe_id, sr.service_id, sr.quantity, l.cost, l.markup
          FROM services_recipe sr
          LEFT JOIN labour_cost_services l ON l.id = sr.service_id
          WHERE sr.recipe_id IN (:recipeIds)
            AND sr.status = 1
          `,
          {
            replacements: { recipeIds },
            type: sequelize.QueryTypes.SELECT,
          }
        );
  
        allRecipeServices.forEach((row) => {
          if (row.service_id !== 10) return;
          const cost = Number(row.cost || 0);
          const qty = Number(row.quantity || 1);
          const markup = Number(row.markup || 0);
          const base = cost * qty;
          const total = base + (base * markup) / 100;
  
          recipeInstallMap[row.recipe_id] = (recipeInstallMap[row.recipe_id] || 0) + total;
        });
      }
  
      // ✅ 7) attach final arrays + install labour to product row
      data.forEach((p) => {
        p.services = serviceMap[p.id] || [];
        p.materials = materialMap[p.id] || [];
        p.install_labour_cost_with_markup = p.recipe_id ? (recipeInstallMap[p.recipe_id] || 0) : 0;
      });
  
      return data;
    } catch (error) {
      console.log(error);
      throw new Error("Server error");
    }
  };

const getAllMaterials = async (all = false,isNoAdditionalProduct = false,inHouse = false) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        }
        else {
            whereCondition = `p.status = 1`;
        }
        console.log("isNoAdditionalProduct",isNoAdditionalProduct);
        if(isNoAdditionalProduct){
            whereCondition += ` and p.isAdditionalProduct = 0`;
        }
        if(inHouse){
            whereCondition += ` and p.inhouse = 1`;
        }
        let query = await sequelize.query(
            ` SELECT p.*,p.retail_price as markup, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, tc.name AS tax, pc.name AS cat_name,psc.name AS sub_cat_name,p.id as material_id, ` +
            ` TRIM(BOTH ',' FROM CONCAT_WS(',', ` +
            `   NULLIF(p.barcode, ''), ` +
            `   GROUP_CONCAT(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0 AND mi.barcode IS NOT NULL AND mi.barcode != '' THEN mi.barcode END ORDER BY mi.barcode SEPARATOR ',') ` +
            ` )) AS barcode, ` +
            ` SUM (DISTINCT  CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END ) AS total_qty,`
            // + ` MAX(mi.unit_cost) AS max_price, `
            // + ` MAX(CASE WHEN mi.qty > 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
            + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
            // + ` MAX(CASE WHEN mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,  `
            + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, `
            // + ` SUM(CASE WHEN mi.partial = 0 THEN mi.qty ELSE 0 END) AS finishedProductQty,`
            + ` SUM(DISTINCT 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.partial = 1 THEN mi.qty ELSE 0 END) AS partialProductQty,`
            + ` SUM(DISTINCT  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, `
            + ` rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost,`
            + `  GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names `
            + ` FROM products p `
            + ` LEFT JOIN users u ON p.added_by = u.id `
            + ` LEFT JOIN tax_category tc ON p.tax_category = tc.id `
            + ` LEFT JOIN material_inventory mi ON p.id = mi.material_id `
            + ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id `
            + ` 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 recipes rc ON p.recipe_id = rc.id`
            + ` LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0  `
            + ` WHERE ${whereCondition} and p.meterial = 1`
            + ` GROUP BY p.id ORDER BY p.id DESC `,
        )
        let data;
        if (query.length > 0) {
            data = query[0];
            data = data.map(item => {
                if (empty(item.price)) {
                    item.price = 0;
                }
                return item
            })
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const getAllMaterialsNew = async (all = false) => {
    try {
      const statusCondition = all ? 'p.status != 2' : 'p.status = 1';
  
      const query = `
        SELECT 
          p.*,
          CONCAT(u.first_name, ' ', u.last_name) AS authorized_by,
          tc.name AS tax,
          pc.name AS cat_name,
          psc.name AS sub_cat_name,
          p.id AS material_id,
          TRIM(BOTH ',' FROM CONCAT_WS(',', 
            NULLIF(p.barcode, ''),
            GROUP_CONCAT(DISTINCT CASE 
              WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
                AND mi.status != 0 
                AND mi.barcode IS NOT NULL 
                AND mi.barcode != '' 
              THEN mi.barcode 
            END ORDER BY mi.barcode SEPARATOR ',')
          )) AS barcode,
          SUM(DISTINCT CASE 
            WHEN mi.source = 0 THEN mi.qty 
            WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty 
            ELSE 0 
          END) AS total_qty,
          MAX(CASE 
            WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
              AND mi.qty > 0 AND mi.partial = 0 
            THEN mi.unit_cost 
            ELSE NULL 
          END) AS max_price,
          MAX(CASE 
            WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
              AND mi.partial = 1 AND mi.status = 1 
            THEN 1 
            ELSE 0 
          END) AS partial,
          SUM(DISTINCT 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(DISTINCT 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,
          rc.expected_cost,
          rc.expected_material_cost,
          rc.expected_service_cost,
          rc.expected_total_service_cost,
          GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names
        FROM products p
        LEFT JOIN users u ON p.added_by = u.id
        LEFT JOIN tax_category tc ON p.tax_category = tc.id
        LEFT JOIN material_inventory mi ON p.id = mi.material_id
        LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
        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 recipes rc ON p.recipe_id = rc.id
        LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
        WHERE ${statusCondition} AND p.meterial = 1
        GROUP BY p.id
        ORDER BY p.id DESC
      `;
  
      const result = await sequelize.query(query, {
        type: sequelize.QueryTypes.SELECT,
      });
  
      return result.map(item => ({
        ...item,
        price: item.price ?? 0
      }));
    } catch (error) {
      console.error(error);
      throw new Error("Server error");
    }
  };
  
const getAllInhouseAndClientOwnedMaterials = async (all = false) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        }
        else {
            whereCondition = `p.status = 1`;
        }
        let query = await sequelize.query(
            ` SELECT p.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, tc.name AS tax, pc.name AS cat_name,psc.name AS sub_cat_name, ` +
            ` TRIM(BOTH ',' FROM CONCAT_WS(',', ` +
            `   NULLIF(p.barcode, ''), ` +
            `   GROUP_CONCAT(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0 AND mi.barcode IS NOT NULL AND mi.barcode != '' THEN mi.barcode END ORDER BY mi.barcode SEPARATOR ',') ` +
            ` )) AS barcode, ` +
            ` SUM (DISTINCT  CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END ) AS total_qty,`
            // + ` MAX(mi.unit_cost) AS max_price, `
            // + ` MAX(CASE WHEN mi.qty > 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
            + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
            // + ` MAX(CASE WHEN mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,  `
            + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, `
            // + ` SUM(CASE WHEN mi.partial = 0 THEN mi.qty ELSE 0 END) AS finishedProductQty,`
            + ` SUM(DISTINCT 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.partial = 1 THEN mi.qty ELSE 0 END) AS partialProductQty,`
            + ` SUM(DISTINCT  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, `
            + ` rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost,`
            + `  GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names `
            + ` FROM products p `
            + ` LEFT JOIN users u ON p.added_by = u.id `
            + ` LEFT JOIN tax_category tc ON p.tax_category = tc.id `
            + ` LEFT JOIN material_inventory mi ON p.id = mi.material_id `
            + ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id `
            + ` 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 recipes rc ON p.recipe_id = rc.id`
            + ` LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0  `
            + ` WHERE ${whereCondition} and p.meterial = 1 AND p.inhouse IN (0, 1)`
            + ` GROUP BY p.id ORDER BY p.id DESC `,
        )
        let data;
        if (query.length > 0) {
            data = query[0];
            data = data.map(item => {
                if (empty(item.price)) {
                    item.price = 0;
                }
                return item
            })
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}

// const getAllMaterialsMain = async (all = false) => {
//     try {
//         let whereCondition;

//         if (all) {
//             whereCondition = `p.status != 2`;
//         }
//         else {
//             whereCondition = `p.status = 1`;
//         }
//         let query = await sequelize.query(
//             ` SELECT p.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, tc.name AS tax, pc.name AS cat_name,psc.name AS sub_cat_name, `
//             + ` SUM (DISTINCT  CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END ) AS total_qty,`
//             // + ` MAX(mi.unit_cost) AS max_price, `
//             // + ` MAX(CASE WHEN mi.qty > 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
//             + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, `
//             // + ` MAX(CASE WHEN mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,  `
//             + ` MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, `
//             // + ` SUM(CASE WHEN mi.partial = 0 THEN mi.qty ELSE 0 END) AS finishedProductQty,`
//             + ` SUM(DISTINCT 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.partial = 1 THEN mi.qty ELSE 0 END) AS partialProductQty,`
//             + ` SUM(DISTINCT  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, `
//             + ` rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost,`
//             + `  GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names `
//             + ` FROM products p `
//             + ` LEFT JOIN users u ON p.added_by = u.id `
//             + ` LEFT JOIN tax_category tc ON p.tax_category = tc.id `
//             + ` LEFT JOIN material_inventory mi ON p.id = mi.material_id `
//             + ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id `
//             + ` 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 recipes rc ON p.recipe_id = rc.id`
//             + ` LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0  `
//             + ` WHERE ${whereCondition} and p.meterial = 1`
//             + ` GROUP BY p.id ORDER BY p.id DESC `,
//         )
//         let data;
//         if (query.length > 0) {
//             data = query[0];
//             data = data.map(item => {
//                 if (empty(item.price)) {
//                     item.price = 0;
//                 }
//                 return item
//             })
//             return data;
//         } else {
//             return [];
//         }
//     }
//     catch (error) {
//         console.log(error);
//         throw new Error("Server error");
//     }
// }
const getAllMaterialsMain = async (all = false) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        } else {
            whereCondition = `p.status = 1`;
        }

        let query = await sequelize.query(
            ` SELECT p.*, 
                     CONCAT(u.first_name,' ',u.last_name) AS authorized_by, 
                     tc.name AS tax, 
                     pc.name AS cat_name, 
                     psc.name AS sub_cat_name,
                     TRIM(BOTH ',' FROM CONCAT_WS(',', 
                       NULLIF(p.barcode, ''), 
                       GROUP_CONCAT(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0 AND mi.barcode IS NOT NULL AND mi.barcode != '' THEN mi.barcode END ORDER BY mi.barcode SEPARATOR ',') 
                     )) AS barcode, 
                     SUM(CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END) AS total_qty,
                     MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, 
                     MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, 
                     (SELECT SUM(CASE 
                     WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
                     AND mi.partial = 0 THEN mi.qty 
                     ELSE 0 
                     END) 
                     FROM material_inventory mi 
                     LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id 
                     WHERE mi.material_id = p.id
                     ) 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, 
                     rc.expected_cost, 
                     rc.expected_material_cost, 
                     rc.expected_service_cost, 
                     rc.expected_total_service_cost, 
                     GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names 
              FROM products p 
              LEFT JOIN users u ON p.added_by = u.id 
              LEFT JOIN tax_category tc ON p.tax_category = tc.id 
              LEFT JOIN material_inventory mi ON p.id = mi.material_id 
              LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id 
              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 recipes rc ON p.recipe_id = rc.id
              LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0  
              WHERE ${whereCondition} AND p.meterial = 1
              GROUP BY p.id, u.first_name, u.last_name, tc.name, pc.name, psc.name, rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost
              ORDER BY p.id DESC`,
        );

        let data;
        if (query.length > 0) {
            data = query[0].map(item => {
                if (!item.price) {  // Replacing 'empty' with correct JavaScript check
                    item.price = 0;
                }
                return item;
            });
            return data;
        } else {
            return [];
        }
    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
};

/** Normalize filter ids from JSON body (strings vs numbers). */
const normalizeMaterialFilterIdList = (arr) => {
    if (!Array.isArray(arr)) return [];
    const seen = new Set();
    const out = [];
    for (const x of arr) {
        const n = parseInt(x, 10);
        if (!Number.isNaN(n) && !seen.has(n)) {
            seen.add(n);
            out.push(n);
        }
    }
    return out;
};

/** Paginated materials for inventory list (tab, search, category filters) — matches MaterialInventoryList.js tabs */
const getAllFilteredMaterialsMain = async (options = {}) => {
    try {
        const {
            page = 1,
            limit = 50,
            search = '',
            materialTab = null,
            categoryIds = [],
            subCategoryIds = [],
        } = options || {};

        let categoryIdList = normalizeMaterialFilterIdList(categoryIds);
        let subCategoryIdList = normalizeMaterialFilterIdList(subCategoryIds);
        // Subcategory filter only applies when exactly one category is selected (matches UI).
        if (categoryIdList.length !== 1) {
            subCategoryIdList = [];
        }

        const pageNumber = Number(page) > 0 ? Number(page) : 1;
        const limitNumber = Number(limit) > 0 ? Number(limit) : 50;
        const offset = (pageNumber - 1) * limitNumber;

        const replacements = {};
        const whereConditions = [`p.meterial = 1`, `p.status != 2`];

        switch (materialTab) {
            case 'Inhouse':
                whereConditions.push(`p.inhouse = 1`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'Inactive Materials':
                whereConditions.push(`p.status = 0`);
                break;
            case 'Equipments':
                whereConditions.push(`p.inhouse = 3`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'Miscellaneous':
                whereConditions.push(`p.inhouse = 4`);
                whereConditions.push(`p.status != 0`);
                break;
            case 'Client Owned':
                whereConditions.push(`p.inhouse = 0`);
                whereConditions.push(`p.status != 0`);
                break;
            default:
                break;
        }

        if (categoryIdList.length > 0) {
            replacements.categoryIds = categoryIdList;
            whereConditions.push(`p.category_id IN (:categoryIds)`);
        }
        if (subCategoryIdList.length > 0) {
            replacements.subCategoryIds = subCategoryIdList;
            whereConditions.push(`p.sub_category_id IN (:subCategoryIds)`);
        }

        const normalizedSearch = String(search || '').toLowerCase().replace(/\s+/g, ' ').trim();
        const trimmedSearch = String(search || '').trim();

        if (normalizedSearch) {
            replacements.searchLike = `%${normalizedSearch}%`;
            const barcodeSearchCondition = `(
                LOWER(COALESCE(p.barcode, '')) LIKE :searchLike
                OR EXISTS (
                    SELECT 1
                    FROM material_inventory mi_search
                    LEFT JOIN receive_orders ro_search ON ro_search.id = mi_search.receive_order_id
                    WHERE mi_search.material_id = p.id
                    AND (mi_search.source = 0 OR (mi_search.source = 1 AND ro_search.status != 0))
                    AND mi_search.status != 0
                    AND mi_search.barcode IS NOT NULL
                    AND mi_search.barcode != ''
                    AND LOWER(mi_search.barcode) LIKE :searchLike
                )
            )`;
            const searchConditions = [
                `LOWER(COALESCE(CAST(p.id AS CHAR), '')) LIKE :searchLike`,
                `LOWER(COALESCE(p.name, '')) LIKE :searchLike`,
                `LOWER(COALESCE(p.tags, '')) LIKE :searchLike`,
                `LOWER(COALESCE(REPLACE(REPLACE(p.description, '\r', ' '), '\n', ' '), '')) LIKE :searchLike`,
                `LOWER(COALESCE(p.vpt_number, '')) LIKE :searchLike`,
                barcodeSearchCondition,
            ];
            if (/^\d+$/.test(trimmedSearch)) {
                replacements.exactId = trimmedSearch;
                searchConditions.unshift(`CAST(p.id AS CHAR) = :exactId`);
            }
            whereConditions.push(`(${searchConditions.join(' OR ')})`);
        }

        const whereClause = whereConditions.join(' AND ');

        const baseFromClause =
            `FROM products p ` +
            `LEFT JOIN users u ON p.added_by = u.id ` +
            `LEFT JOIN tax_category tc ON p.tax_category = tc.id ` +
            `LEFT JOIN material_inventory mi ON p.id = mi.material_id ` +
            `LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id ` +
            `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 recipes rc ON p.recipe_id = rc.id ` +
            `LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0 `;

        const selectClause =
            `SELECT p.*, ` +
            `CONCAT(u.first_name,' ',u.last_name) AS authorized_by, ` +
            `tc.name AS tax, ` +
            `pc.name AS cat_name, ` +
            `psc.name AS sub_cat_name, ` +
            `TRIM(BOTH ',' FROM CONCAT_WS(',', ` +
            `NULLIF(p.barcode, ''), ` +
            `GROUP_CONCAT(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0 AND mi.barcode IS NOT NULL AND mi.barcode != '' THEN mi.barcode END ORDER BY mi.barcode SEPARATOR ',') ` +
            `)) AS barcode, ` +
            `SUM(CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END) AS total_qty, ` +
            `MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, ` +
            `MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, ` +
            `(SELECT SUM(CASE ` +
            `WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) ` +
            `AND mi.partial = 0 THEN mi.qty ` +
            `ELSE 0 ` +
            `END) ` +
            `FROM material_inventory mi ` +
            `LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id ` +
            `WHERE mi.material_id = p.id ` +
            `) 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, ` +
            `rc.expected_cost, ` +
            `rc.expected_material_cost, ` +
            `rc.expected_service_cost, ` +
            `rc.expected_total_service_cost, ` +
            `GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names `;

        const groupByClause =
            `GROUP BY p.id, u.first_name, u.last_name, tc.name, pc.name, psc.name, rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost `;

        const countResult = await sequelize.query(
            `SELECT COUNT(DISTINCT p.id) AS totalCount ${baseFromClause} WHERE ${whereClause}`,
            {
                replacements,
                type: sequelize.QueryTypes.SELECT,
            }
        );

        const totalCount = Number(countResult?.[0]?.totalCount || 0);

        const paginatedQuery =
            `${selectClause} ${baseFromClause} WHERE ${whereClause} ` +
            `${groupByClause}` +
            `ORDER BY p.id DESC LIMIT ${limitNumber} OFFSET ${offset}`;

        const rawData = await sequelize.query(paginatedQuery, {
            replacements,
            type: sequelize.QueryTypes.SELECT,
        });

        const data = (rawData || []).map((item) => {
            if (!item.price) {
                item.price = 0;
            }
            return item;
        });

        return {
            results: data || [],
            totalCount,
            pagination: {
                currentPage: pageNumber,
                limit: limitNumber,
                totalCount,
                totalPages: limitNumber > 0 ? Math.ceil(totalCount / limitNumber) : 0,
            },
        };
    } catch (error) {
        console.log(error);
        throw new Error('Server error');
    }
};

const getAllMaterialsOutOfStock = async (all = false) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        }
        else {
            whereCondition = `p.status = 1`;
        }
        let query = await sequelize.query(
            ` SELECT p.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, tc.name AS tax, pc.name AS cat_name,psc.name AS sub_cat_name, `
            + ` SUM ( CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END ) AS total_qty,`
            + ` rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost`
            + ` FROM products p `
            + ` LEFT JOIN users u ON p.added_by = u.id `
            + ` LEFT JOIN tax_category tc ON p.tax_category = tc.id `
            + ` LEFT JOIN material_inventory mi ON p.id = mi.material_id `
            + ` LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id `
            + ` 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 recipes rc ON p.recipe_id = rc.id`
            + ` WHERE ${whereCondition} and p.meterial = 1`
            + ` GROUP BY p.id HAVING total_qty < p.min_value ORDER BY p.id DESC `,
        )
        let data;
        if (query.length > 0) {
            data = query[0];
            data = data.map(item => {
                if (empty(item.price)) {
                    item.price = 0;
                }
                return item
            })
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const addProduct = async (data) => {
    try {
        let results = await Products.create(data)
        // console.log("###########Result",results);
        if (results) {
            return results;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the product ", error);
        return error;
    }
}

const addClientStockLog = async (data) => {
  try {
    let result = await ClientStockLogs.create(data);
    // console.log("###########StockLogResult", result);
    if (result) {
      return result;
    } else {
      return false;
    }
  } catch (error) {
    console.error("Error adding client stock log", error);
    return error;
  }
};

const getClientStockLogs = async (catalog_id = null) => {
  try {
    let query = `
      SELECT 
        csl.id,
        csl.catalog_source,
        csl.timestamp,
        csl.status,
        csl.catalog_id,
        csl.catalog_log_message,
        p.name AS product_name,
        p.in_stock_no,
        u.first_name,
        u.last_name,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name
      FROM client_stock_logs csl
      LEFT JOIN products p ON csl.catalog_id = p.id
      LEFT JOIN users u ON p.added_by = u.id
      WHERE csl.status = 1
    `;

    if (catalog_id) {
      query += ` AND csl.catalog_id = ${catalog_id}`;
    }

    query += ` ORDER BY csl.timestamp DESC`;

    const [rows] = await sequelize.query(query);
    return rows;
  } catch (error) {
    console.error("Error fetching client stock logs", error);
    return error;
  }
};

const isProductNameExist = async (name, productId = 0) => {
    let where = {};
    if (productId !== 0) {
        where = { id: { [Sequelize.Op.not]: productId } };
        // console.log("productId -- ", productId)
    }
    const results = await Products.findAll({
        attributes: ['id'],
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: name } },
                { status: { [Sequelize.Op.ne]: 2 } },
                // { meterial: { [Sequelize.Op.eq]: 0 } },
                where
            ]
        }
    });
    console.log("results : ", results.length)
    return results.length > 0;
}

const isMaterialNameExist = async (name, productId = 0) => {
    let where = {};
    if (productId !== 0) {
        where = { id: { [Sequelize.Op.not]: productId } };
        console.log("productId -- ", productId)
    }
    const results = await Products.findAll({
        attributes: ['id'],
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: name } },
                { status: { [Sequelize.Op.ne]: 2 } },
                // { meterial: { [Sequelize.Op.eq]: 1 } },
                where
            ]
        }
    });
    console.log("results : ", results.length)
    return results.length > 0;
}

const isServiceNameExist = async (name, serviceId = 0) => {
    let where = {};
    if (serviceId !== 0) {
        where = { id: { [Sequelize.Op.not]: serviceId } };
        console.log("productId -- ", serviceId)
    }
    const results = await LabourCostServices.findAll({
        attributes: ['id'],
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: name } },
                { status: { [Sequelize.Op.ne]: 2 } },
                where
            ]
        }
    });
    console.log("results : ", results.length)
    return results.length > 0;
}

const doUpload = async (file, filename) => {
    console.log("$$$$$$$$$$$$$$$$$$ filename : ", filename)
    console.log("@@@@@@@@@@@@@@@@@@ file :", file)
    await pump(file, fs.createWriteStream(`./build/images/products/${filename}`));
    return true
}

const
    updateProduct = async (prodid, data) => {
        console.log("-------update-data------", data)
        try {
            const updatedRows = await Products.update(data, {
                where: {
                    id: prodid
                },
                returning: true,
            });
            if (updatedRows) {
                return updatedRows
            }
            console.log("---------updatedRows------------", updatedRows)
        }
        catch (error) {
            console.error("Error updating product:", error);
            return false;
        }
    }

const updateInhouseCatalogStatus = async (prodId, data) => {
  try {
    const ids = Array.isArray(prodId) ? prodId : [prodId];

    const [affectedCount] = await Products.update(
      data,
      {
        where: { id: { [Sequelize.Op.in]: ids } }
      }
    );

    if (affectedCount > 0) {
      const updatedProducts = await Products.findAll({ where: { id: { [Sequelize.Op.in]: ids } } });
      return Array.isArray(prodId) ? updatedProducts : updatedProducts[0];
    }

    return false;
  } catch (error) {
    console.error("Error updating catalog status:", error);
    return false;
  }
};

const deleteProduct = async (prodId, prodStatus = 0) => {
    console.log("deleteProduct---id---", prodId, " status---", prodStatus);
    try {
        const deleted = await Products.destroy({
            where: {
                id: prodId
            },
        });
        console.log("deleteProduct..result...", deleted);
        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}

const getTaxCategory = async () => {
    const result = await sequelize.query(`SELECT tc.*, CONCAT(u.first_name,' ',u.last_name) AS updated_by FROM tax_category tc `
        +` left join users u on u.id = tc.added_by`
        +` where tc.status = 1`);
    if (result[0]?.length > 0) {
        return result[0];
    } else {
        return [];
    }
}

const getAllTaxCategory = async () => {
    const result = await sequelize.query(`SELECT tc.*, CONCAT(u.first_name,' ',u.last_name) AS updated_by FROM tax_category tc `
        +` left join users u on u.id = tc.added_by`
        +` where tc.status != 2`);
    if (result[0]?.length > 0) {
        return result[0];
    } else {
        return [];
    }
}

const getTaxCategoryById = async (id) => {
    const result = await sequelize.query('SELECT * FROM tax_category WHERE id = :id',
        {
          replacements: { id },
          type: sequelize.QueryTypes.SELECT,
        }
    );

    if (result && result.length > 0) {
        return result[0];
    } else {
        return null;
    } 
}

const addTaxCategory = async (data) => {
    try {
        const result = await TaxCategory.create(data)
        if (result) {
            return true
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding TaxCategory - ", error);
        return false
    }
}

const updateTaxCategory = async (id, data) => {
    try {
        const result = await TaxCategory.update(data, {
            where: { id: id }
        })
        if (result) {
            return true
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error updating TaxCategory - ", error);
        return false
    }
}

const addLabourCostServices = async (data) => {
    try {
        const result = await LabourCostServices.create(data)
        if (result) {
            // return true
            return result?.id;
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding LabourCostServices - ", error);
    }
}

const updateLabourCostServices = async (id, data) => {
    try {
        const result = await LabourCostServices.update(data, {
            where: { id: id }
        })
        if (result) {
            return result
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error updating LabourCostServices - ", error);
    }
}

const getAllLabourCostServices = async (inHouse = false) => {
    try {
        let whereCondition = '';
        if(inHouse){
            whereCondition = ` AND ls.inhouse = 1`;
        }
        const result = await sequelize.query(`SELECT ls.*,${FACILITY_ID} AS facility_id,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 ${whereCondition} ORDER BY ls.id DESC`)
        if (result[0]?.length > 0) {
            return result[0]
        } else {
            return false
        }
    } catch (error) {
        console.log('getAllLabourCostServices Error : ',);
        return false
    }
}

const getAllLabourCostServicesForJobCost = async () => {
    try {
        const result = await sequelize.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 AND ls.jobCost = 1 ORDER BY ls.id DESC`)
        if (result[0]?.length > 0) {
            return result[0]
        } else {
            return false
        }
    } catch (error) {
        console.log('getAllLabourCostServices Error : ',);
        return false
    }
}

const getAllServices = async () => {
    try {
        const result = await sequelize.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 = 1 ORDER BY ls.id DESC`)
        if (result[0]?.length > 0) {
            return result[0]
        } else {
            return false
        }
    } catch (error) {
        console.log('getAllLabourCostServices Error : ',);
        return false
    }
}


const getLabourCostServicesById = async (id) => {
    try {
        const result = await sequelize.query(`SELECT ls.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by FROM labour_cost_services ls`
            + ` LEFT JOIN users u ON u.id = ls.added_by `
            + ` WHERE ls.id=${id} AND ls.status != 2`
        )
        if (result[0]?.length > 0) {
            return result[0]
        } else {
            return false
        }
    } catch (error) {
        console.log('getLabourCostServicesById Error : ',);
        return false
    }
}

const addLabourCostServiceType = async (data) => {
    try {
        const result = await LabourCostServiceTypes.create(data)
        if (result) {
            return true
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding LabourCostServiceTypes - ", error);
    }
}

const updateLabourCostServiceType = async (id, data) => {
    try {
        const result = await LabourCostServiceTypes.update(data, {
            where: { id: id }
        })
        if (result) {
            return true
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error updating LabourCostServiceTypes - ", error);
    }
}

const getAllLabourCostServiceTypes = async () => {
    try {
        const result = await sequelize.query(`SELECT ls.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by FROM labour_cost_services_type ls`
            + ` LEFT JOIN users u ON u.id = ls.added_by WHERE ls.status != 2`
            + ` ORDER BY ls.id DESC`)
        if (result[0]?.length > 0) {
            return result[0]
        } else {
            return false
        }
    } catch (error) {
        console.log('getAllLabourCostServiceTypes Error : ',);
        return false
    }
}

const getLabourCostServiceTypesById = async (id) => {
    try {
        const result = await sequelize.query(`SELECT ls.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by,l.name AS service_name FROM labour_cost_services_type ls`
            + ` LEFT JOIN labour_cost_services l ON l.service_type = ls.id`
            + ` LEFT JOIN users u ON u.id = ls.added_by`
            + ` WHERE ls.id=${id} AND ls.status != 2 AND l.status !=2 `
        )
        if (result[0]?.length > 0) {
            return result[0]
        } else {
            return false
        }
    } catch (error) {
        console.log('getLabourCostServiceTypesById Error : ',);
        return false
    }
}

const addFinishedProduct = async (data) => {
    try {
        const result = await MaterialInventory.create(data)
        if (result) {
            return true
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding finished product - ", error);
    }
}

const addDeletedMaterialInventory = async (data) => {
    try {
        const result = await DeletedMaterialInventory.create(data)
        if (result) {
            return true
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding deleted finished product - ", error);
    }
}

const getFinishedProducts = async (editId = 0,orderBy = false,notPartial=false) => {
    try {
        let whereCondition = '';
        let partialCondition = '';
        let orderByCondition = '';
        if (editId !== 0) {
            whereCondition = ` AND (mi.material_id = ${editId}) `
        }
        if (notPartial) {
            partialCondition = ` AND mi.partial = 0 `
        }
        if (orderBy) {
            orderByCondition = ` ORDER BY mi.unit_cost DESC `
        }
        const result = await sequelize.query(
            `SELECT mi.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by,p.meterial AS isMaterial,p.retail_price AS markup,p.inhouse, p.name,p.unit,p.unit_qty AS number_of_units,v.name as vendor from material_inventory mi `
            + ` LEFT JOIN users u on mi.added_by = u.id`
            + ` LEFT JOIN receive_orders ro on ro.id = mi.receive_order_id`
            + ` left join partners v on v.id=ro.vendor_id`
            + ` LEFT JOIN products p on mi.material_id = p.id `
            + ` WHERE (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) ${partialCondition} AND mi.status != 0 ${whereCondition} ${orderByCondition} `)
        if (result[0]) {
            return result[0]
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching finished products - ", error);
    }
}
const getPartialFinishedProducts = async (editId = 0,orderBy = false) => {
    try {
        let whereCondition = '';
        let orderByCondition = '';
        if (editId !== 0) {
            whereCondition = ` AND (mi.material_id = ${editId}) `
        }
        if (orderBy) {
            orderByCondition = ` ORDER BY mi.unit_cost DESC `
        }
        const result = await sequelize.query(
            `SELECT mi.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by, p.name,p.unit,p.unit_qty AS number_of_units,v.id AS vendor_id,v.name as vendor from material_inventory mi `
            + ` LEFT JOIN users u on mi.added_by = u.id`
            + ` LEFT JOIN receive_orders ro on ro.id = mi.receive_order_id`
            + ` left join partners v on v.id=ro.vendor_id`
            + ` LEFT JOIN products p on mi.material_id = p.id `
            + ` WHERE (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status != 0 ${whereCondition} ${orderByCondition} `)
        if (result[0]) {
            return result[0]
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching finished products - ", error);
    }
}

const getPartialFinishedProductsStockByUnit = async (editId = 0, orderBy = false) => {
    try {
        let whereCondition = '';
        let orderByCondition = '';

        if (editId !== 0) {
            whereCondition = ` AND mi.material_id = ${editId} `;
        }

        if (orderBy) {
            orderByCondition = ` ORDER BY max_unit_cost DESC `;
        }

        const result = await sequelize.query(
            `SELECT 
                mi.partial_unit, 
                mi.partial,
                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.status != 0 
             ${whereCondition}
             GROUP BY mi.partial, mi.partial_unit
             ${orderByCondition}`
        );

        return result[0] || false;
    } catch (error) {
        console.log("Error fetching partial finished products stock - ", error);
        return false;
    }
};

const getFinishedProductsData = async (id) => {
    try {
        const result = await sequelize.query(
            `SELECT 
        mi.*,
        CONCAT(u.first_name,' ',u.last_name) AS authorized_by,
        p.name 
    FROM 
        material_inventory mi
    LEFT JOIN 
        users u ON mi.added_by = u.id
    LEFT JOIN 
        receive_orders ro ON ro.id = mi.receive_order_id
    LEFT JOIN 
        products p ON mi.material_id = p.id 
    WHERE 
        mi.id = ${id} AND mi.status != 0 AND 
        (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
    ` )
        if (result[0]) {
            return result[0][0]
        } else {
            return []
        }
    }
    catch (error) {
        console.log("Error fetching finished products - ", error);
    }
}
const getFinishedProductsDataByBarcode = async (barcode) => {
    try {
        const result = await sequelize.query(`SELECT mi.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, p.name 
            FROM material_inventory mi
            LEFT JOIN users u ON mi.added_by = u.id
            LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
            LEFT JOIN products p ON mi.material_id = p.id 
            WHERE mi.barcode = '${barcode}' AND (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.status != 0` )
        if (result[0]) {
            return result[0][0]
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching finished products - ", error);
        return false
    }
}

const getProductByBarcode = async (barcode) => {
  try {
    const query = `
      SELECT *
      FROM products
      WHERE barcode = :barcode
        AND status <> 0
      LIMIT 1
    `;

    const rows = await sequelize.query(query, {
      replacements: { barcode },
      type: sequelize.QueryTypes.SELECT
    });

    return rows[0] || false;
  } catch (error) {
    console.error("Error fetching product:", error);
    return false;
  }
};

const getProductDataByBarcode = async (barcode, requestId) => {
  try {
    //   const query = `
    //   SELECT
    //     cop.*,
    //     p.name
    //   FROM customer_order_products AS cop
    //   INNER JOIN customer_orders AS co
    //     ON co.id = cop.order_id
    //   INNER JOIN requests AS r
    //     ON r.id = co.request_id
    //   INNER JOIN products AS p
    //     ON p.id = cop.product_id
    //   WHERE r.id = :requestId
    //     AND p.barcode = :barcode
    //     AND p.status <> 0
    //   ORDER BY cop.id DESC
    //   LIMIT 1
    // `;
    const query = `
      SELECT
        cop.*,
        p.name,p.in_stock_no
      FROM customer_order_products AS cop
      LEFT JOIN customer_orders AS co
        ON co.id = cop.order_id
      LEFT JOIN requests AS r
        ON r.id = co.request_id
      LEFT JOIN products AS p
        ON p.id = cop.product_id
        AND p.barcode = :barcode
        AND p.status <> 0
      WHERE r.id = :requestId
      ORDER BY cop.id DESC
      LIMIT 1
    `;
    const rows = await sequelize.query(query, {
      replacements: { barcode, requestId },
      type: sequelize.QueryTypes.SELECT
    });

    return rows[0] || false;
  } catch (error) {
    console.error("Error fetching products:", error);
    return false;
  }
};


const deleteFinishedProduct = async (prodId) => {
    const results = await MaterialInventory.destroy({
        where: {
            id: prodId
        },
    })
    if (results) {
        return true;
    } else {
        return false
    }
}
const deletePartialsOfFinishedProduct = async (prodId) => {
    const results = await MaterialInventory.destroy({
        where: {
            parent_finished_product_id: prodId
        },
    })
    if (results) {
        return true;
    } else {
        return false
    }
}

const getFinishedProductById = async (editId) => {
    const result = await sequelize.query(`SELECT mi.*,p.name AS product_name,p.quickbookId,v.id AS vendor_id FROM material_inventory mi 
        LEFT JOIN products p ON p.id = mi.material_id 
        LEFT JOIN receive_orders ro on ro.id = mi.receive_order_id
        LEFT JOIN partners v on v.id=ro.vendor_id
        WHERE mi.id=${editId} AND mi.status != 0`)
    if (result[0]) {
        return result[0]
    } else {
        return false
    }
}

const updateFinishedProduct = async (editId, data) => {
    const result = await MaterialInventory.update(data, {
        where: {
            id: editId
        }
    })
    if (result) {
        return result
    } else {
        return false
    }
}

const addInventoryAuditLog = async (data) => {
    const result = await InventoryAudit.create(data)
    if (result) {
        return result
    } else {
        return false
    }
}

const fetchInventoryAuditLog = async (id) => {
    const result = await sequelize.query(`SELECT I.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by, mi.barcode `
        + ` FROM inventory_audit I `
        + ` LEFT JOIN users u ON u.id = I.added_by `
        + ` LEFT JOIN material_inventory mi ON mi.id = I.material_inventory_id `
        + ` WHERE I.material_id = ${id} AND mi.status != 0 `
        + ` ORDER BY I.id DESC`)
    if (result[0].length > 0) {
        return result[0]
    } else {
        return false
    }
}

const CreateReceiveOrder = async (data) => {
    try {
        const result = await ReceiveOrders.create(data)
        if (result) {
            return result?.dataValues?.id
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding receive order - ", error);
    }
}

const CreateReceiveOrderServices = async (data) => {
    try {
        const result = await ReceiveOrderServices.create(data)
        if (result) {
            return result?.dataValues?.id
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding receive order services- ", error);
        return false
    }
}

const DeleteReceiveOrderServices = async (orderId) => {
    try {
        const result = await ReceiveOrderServices.destroy({
            where: {
                receive_order_id: orderId
            }
        })
        if (result) {
            return result
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error deleting receive order services- ", error);
        return false
    }
}

const UpdateReceiveOrderServices = async (data, orderServiceId) => {
    try {
        const result = await ReceiveOrderServices.update(data,{
            where: {
                id: orderServiceId
            }
        })
        if (result) {
            return result
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error deleting receive order services- ", error);
        return false
    }
}

const deleteOrderProducts = async (orderId) => {
    try {
        const result = await MaterialInventory.destroy({
            where: {
                receive_order_id: orderId
            }
        })
        if (result) {
            return result
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log(error);
    }
}
const deleteFinishedProductsByOrderAndMaterial = async (orderId, id) => {
    try {
        const result = await MaterialInventory.destroy({
            where: {
                receive_order_id: orderId,
                id:id
            }
        })
        if (result) {
            return result
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log(error);
    }
}

const EditReceiveOrder = async (orderId, data) => {
    try {
        const result = await ReceiveOrders.update(data, {
            where: { id: orderId }
        })
        if (result) {
            return result
        } else {
            return false
        }
    }
    catch (error) {
        console.log("Error adding receive order - ", error);
    }
}

const getAllReceiveOrders = async (userType,userId,where) => {
    try {
        let whereCondition = '';
        if(userType==3){
            whereCondition = ` WHERE pt.user_id = ${userId}`
            if(where){
            whereCondition += ` AND ${where}`
            }
        }else{
            if(where){
            whereCondition += ` WHERE ${where}`
            }
        }
        
        const result = await sequelize.query(
            `SELECT ro.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, pt.name AS vendor_name, pt.fax_number AS vendor_fax,`
            + ` so.id AS sales_order_id, so.name AS sales_order_name, so.doc_number AS sales_order_doc_number,`
            + ` so_co.id AS sales_order_order_id, so_co.customer_id AS sales_order_customer_id, so_co.customer_name AS sales_order_customer_name,`
            + ` so_proj.id AS sales_order_project_id, so_proj.status AS sales_order_project_status, so_proj.noServiceNoTask AS sales_order_project_noServiceNoTask,`
            + ` so_inst.status AS sales_order_installation_status, so_inst.complete_status AS sales_order_installation_complete_status,`
            + ` COALESCE(so_cop.product_count,0) AS sales_order_product_count, COALESCE(so_rs.service_count,0) AS sales_order_service_count`
            + ` FROM receive_orders ro `
            + ` LEFT JOIN users u on ro.added_by = u.id`
            + ` LEFT JOIN partners pt on ro.vendor_id = pt.id`
            + ` LEFT JOIN requests so ON so.id = ro.sales_order_id`
            + ` LEFT JOIN customer_orders so_co ON so_co.request_id = so.id`
            + ` LEFT JOIN projects so_proj ON so_proj.request_id = so.id AND so_proj.is_estimate_job = 0`
            + ` LEFT JOIN (SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY request_id) so_inst ON so_inst.request_id = so.id`
            + ` LEFT JOIN (`
            + `   SELECT co.request_id,`
            + `     SUM(CASE`
            + `           WHEN cop.is_single_card = 1 THEN 1`
            + `           ELSE cop.location_quantity`
            + `         END) AS product_count`
            + `   FROM customer_orders co`
            + `   JOIN customer_order_products cop ON co.id = cop.order_id`
            + `   WHERE cop.material = 0`
            + `   GROUP BY co.request_id`
            + ` ) so_cop ON so_cop.request_id = so.id`
            + ` LEFT JOIN (`
            + `   SELECT co.request_id, COUNT(rss.id) AS service_count`
            + `   FROM customer_orders co`
            + `   JOIN request_services rss ON co.request_id = rss.request_id`
            + `   GROUP BY co.request_id`
            + ` ) so_rs ON so_rs.request_id = so.id`
            + ` ${whereCondition}`)
        if (result[0]) {
            return result[0]
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching receive orders - ", error);
    }
}

const getAllReceiveOrdersLatestFirst = async (userType,userId,where) => {
    try {
        let whereCondition = '';
        if(userType==3){
            whereCondition = ` WHERE pt.user_id = ${userId}`
            if(where){
            whereCondition += ` AND ${where}`
            }
        }else{
            if(where){
            whereCondition += ` WHERE ${where}`
            }
        }
        
        const result = await sequelize.query(
            `SELECT ro.*, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, pt.name AS vendor_name, pt.fax_number AS vendor_fax, `
            + `COALESCE(NULLIF(TRIM(CONCAT_WS(', ', NULLIF(TRIM(pt.remit_to_address), ''), NULLIF(TRIM(pt.remit_to_city), ''), NULLIF(TRIM(pt.remit_to_zip_code), ''), NULLIF(TRIM(pt.remit_to_country), ''))), ''), `
            + `CONCAT_WS(', ', NULLIF(TRIM(pt.address), ''), NULLIF(TRIM(pt.city), ''), NULLIF(TRIM(pt.zip_code), ''))) AS vendor_address, `
            + `so.id AS sales_order_id, so.name AS sales_order_name, so.doc_number AS sales_order_doc_number, `
            + `so_co.id AS sales_order_order_id, so_co.customer_id AS sales_order_customer_id, so_co.customer_name AS sales_order_customer_name, `
            + `so_proj.id AS sales_order_project_id, so_proj.status AS sales_order_project_status, so_proj.noServiceNoTask AS sales_order_project_noServiceNoTask, `
            + `so_inst.status AS sales_order_installation_status, so_inst.complete_status AS sales_order_installation_complete_status, `
            + `COALESCE(so_cop.product_count,0) AS sales_order_product_count, COALESCE(so_rs.service_count,0) AS sales_order_service_count `
            + `FROM receive_orders ro `
            + ` LEFT JOIN users u on ro.added_by = u.id`
            + ` LEFT JOIN partners pt on ro.vendor_id = pt.id`
            + ` LEFT JOIN requests so ON so.id = ro.sales_order_id`
            + ` LEFT JOIN customer_orders so_co ON so_co.request_id = so.id`
            + ` LEFT JOIN projects so_proj ON so_proj.request_id = so.id AND so_proj.is_estimate_job = 0`
            + ` LEFT JOIN (SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY request_id) so_inst ON so_inst.request_id = so.id`
            + ` LEFT JOIN (`
            + `   SELECT co.request_id,`
            + `     SUM(CASE`
            + `           WHEN cop.is_single_card = 1 THEN 1`
            + `           ELSE cop.location_quantity`
            + `         END) AS product_count`
            + `   FROM customer_orders co`
            + `   JOIN customer_order_products cop ON co.id = cop.order_id`
            + `   WHERE cop.material = 0`
            + `   GROUP BY co.request_id`
            + ` ) so_cop ON so_cop.request_id = so.id`
            + ` LEFT JOIN (`
            + `   SELECT co.request_id, COUNT(rss.id) AS service_count`
            + `   FROM customer_orders co`
            + `   JOIN request_services rss ON co.request_id = rss.request_id`
            + `   GROUP BY co.request_id`
            + ` ) so_rs ON so_rs.request_id = so.id`
            + ` ${whereCondition} ORDER BY ro.id DESC`)
        if (result[0]) {
            return result[0]
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching receive orders - ", error);
    }
}

const getOrderById = async (orderId, noStatusCheck=false) => {
    try {
        // First, get the order to check its type
        const orderCheck = await sequelize.query(
            `SELECT ro.orderType FROM receive_orders ro WHERE ro.id = ${orderId}`
        );
        
        const isServiceOnly = orderCheck[0] && orderCheck[0][0] && orderCheck[0][0].orderType == 3;
        
        let whereCondition = ` AND mi.status != 0 `
        if(noStatusCheck){
            whereCondition = ''
        }
        
        // For service-only orders, we don't need material_inventory joins
        if (isServiceOnly) {
            const result = await sequelize.query(
                `SELECT ro.id AS order_id,ro.project_type,pts.name AS project_type_name,pts.quickBookId AS project_type_quickbook_id, CONCAT(u.first_name,' ',u.last_name) AS authorized_by, ro.name AS order_name, pt.name AS vendor_name, pt.fax_number AS vendor_fax, ct2.name AS customer_billing_type, ct2.billing_type AS customer_billing_type_value, so.participation_ends AS sales_order_participation_ends, so.installation_end_date AS sales_order_installation_end_date, pt.syncWithQuickbook AS vendor_synced, pt.phone AS vendor_phone, `
                + ` COALESCE(NULLIF(TRIM(CONCAT_WS(', ', NULLIF(TRIM(pt.remit_to_address), ''), NULLIF(TRIM(pt.remit_to_city), ''), NULLIF(TRIM(pt.remit_to_zip_code), ''), NULLIF(TRIM(pt.remit_to_country), ''))), ''), CONCAT_WS(', ', NULLIF(TRIM(pt.address), ''), NULLIF(TRIM(pt.city), ''), NULLIF(TRIM(pt.zip_code), ''))) AS vendor_address, `
                + ` ro.vendor_id, ro.transaction_id, ro.received_date, ro.expected_date, ro.include_tax, `
                + ` ro.sub_total, ro.tax, ro.orderType, ro.grand_total, ro.payment_mode, ro.payment_status, ro.status AS order_status, `
                + ` ro.notSyncedMessage, ro.syncWithQuickbooks, ro.tax_category, ro.doc_number, ro.ship_via, ro.notes, `
                + ` so.id AS sales_order_id, so.name AS sales_order_name, so.doc_number AS sales_order_doc_number, `
                + ` so_co.id AS sales_order_order_id, so_co.customer_id AS sales_order_customer_id, so_co.customer_name AS sales_order_customer_name, `
                + ` so_proj.id AS sales_order_project_id, so_proj.status AS sales_order_project_status, so_proj.noServiceNoTask AS sales_order_project_noServiceNoTask, `
                + ` so_inst.status AS sales_order_installation_status, so_inst.complete_status AS sales_order_installation_complete_status, `
                + ` COALESCE(so_cop.product_count,0) AS sales_order_product_count, COALESCE(so_rs.service_count,0) AS sales_order_service_count `
                + ` FROM receive_orders ro `
                + ` LEFT JOIN project_types pts ON pts.id = ro.project_type`
                + ` LEFT JOIN users u on ro.added_by = u.id`
                + ` LEFT JOIN customer_orders co2 ON co2.request_id = ro.sales_order_id`
                + ` LEFT JOIN partners pt2 ON co2.customer_id = pt2.id`
                + ` LEFT JOIN customer_type ct2 ON pt2.customerTypeId = ct2.id`
                + ` LEFT JOIN partners pt on ro.vendor_id = pt.id `
                + ` LEFT JOIN customer_type ct on pt.customerTypeId = ct.id`
                + ` LEFT JOIN requests so ON so.id = ro.sales_order_id`
                + ` LEFT JOIN customer_orders so_co ON so_co.request_id = so.id`
                + ` LEFT JOIN projects so_proj ON so_proj.request_id = so.id AND so_proj.is_estimate_job = 0`
                + ` LEFT JOIN (SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY request_id) so_inst ON so_inst.request_id = so.id`
                + ` LEFT JOIN (`
                + `   SELECT co.request_id,`
                + `     SUM(CASE`
                + `           WHEN cop.is_single_card = 1 THEN 1`
                + `           ELSE cop.location_quantity`
                + `         END) AS product_count`
                + `   FROM customer_orders co`
                + `   JOIN customer_order_products cop ON co.id = cop.order_id`
                + `   WHERE cop.material = 0`
                + `   GROUP BY co.request_id`
                + ` ) so_cop ON so_cop.request_id = so.id`
                + ` LEFT JOIN (`
                + `   SELECT co.request_id, COUNT(rss.id) AS service_count`
                + `   FROM customer_orders co`
                + `   JOIN request_services rss ON co.request_id = rss.request_id`
                + `   GROUP BY co.request_id`
                + ` ) so_rs ON so_rs.request_id = so.id`
                + ` WHERE ro.id = ${orderId}`)
            if (result[0] && result[0].length > 0) {
                return result[0]
            }
            else {
                return false
            }
        } else {
            // For materials/stockCatalog orders, use the existing query
            const result = await sequelize.query(
                `SELECT mi.*,CONCAT(u.first_name,' ',u.last_name) AS authorized_by,p.meterial,lcs.name as service_name,lcs.quickbookId AS service_qb_id,lcs.syncWithQuickbooks AS serviceSyncedWithQb, p.name AS product_name,p.material_types,p.unit, ro.id AS order_id,p.qb_income_account AS incomeAccountId,p.qb_expense_account AS expenseAccountId, `
                + ` ro.name AS order_name,ro.project_type,pts.name AS project_type_name,pts.quickBookId AS project_type_quickbook_id,pt.name AS vendor_name, pt.fax_number AS vendor_fax, ct2.name AS customer_billing_type, ct2.billing_type AS customer_billing_type_value, so.participation_ends AS sales_order_participation_ends, so.installation_end_date AS sales_order_installation_end_date, p.syncWithQuickbooks AS material_synced,p.quickbookId AS material_qb_id,pt.syncWithQuickbook AS vendor_synced,pt.phone AS vendor_phone, COALESCE(NULLIF(TRIM(CONCAT_WS(', ', NULLIF(TRIM(pt.remit_to_address), ''), NULLIF(TRIM(pt.remit_to_city), ''), NULLIF(TRIM(pt.remit_to_zip_code), ''), NULLIF(TRIM(pt.remit_to_country), ''))), ''), CONCAT_WS(', ', NULLIF(TRIM(pt.address), ''), NULLIF(TRIM(pt.city), ''), NULLIF(TRIM(pt.zip_code), ''))) AS vendor_address, ro.vendor_id, ro.transaction_id, ro.received_date , ro.expected_date , ro.include_tax, `
                + ` ro.sub_total, ro.tax,ro.orderType, ro.grand_total, ro.payment_mode, ro.payment_status,ro.status AS order_status, ro.notSyncedMessage, ro.syncWithQuickbooks,ro.tax_category,ro.doc_number, ro.ship_via, ro.notes, `
                + ` so.id AS sales_order_id, so.name AS sales_order_name, so.doc_number AS sales_order_doc_number, `
                + ` so_co.id AS sales_order_order_id, so_co.customer_id AS sales_order_customer_id, so_co.customer_name AS sales_order_customer_name,	`
                + ` so_proj.id AS sales_order_project_id, so_proj.status AS sales_order_project_status, so_proj.noServiceNoTask AS sales_order_project_noServiceNoTask, `
                + ` so_inst.status AS sales_order_installation_status, so_inst.complete_status AS sales_order_installation_complete_status, `
                + ` COALESCE(so_cop.product_count,0) AS sales_order_product_count, COALESCE(so_rs.service_count,0) AS sales_order_service_count`
                + ` FROM receive_orders ro `
                + ` LEFT JOIN material_inventory mi on ro.id = mi.receive_order_id`
                + ` LEFT JOIN users u on mi.added_by = u.id`
                + ` LEFT JOIN labour_cost_services lcs ON mi.service_id = lcs.id`
                + ` LEFT JOIN partners pt on ro.vendor_id = pt.id`
                + ` LEFT JOIN customer_orders co2 ON co2.request_id = ro.sales_order_id`
                + ` LEFT JOIN partners pt2 ON co2.customer_id = pt2.id`
                + ` LEFT JOIN customer_type ct2 ON pt2.customerTypeId = ct2.id`
                + ` LEFT JOIN project_types pts ON pts.id = ro.project_type`
                + ` LEFT JOIN products p on mi.material_id = p.id`
                + ` LEFT JOIN requests so ON so.id = ro.sales_order_id`
                + ` LEFT JOIN customer_orders so_co ON so_co.request_id = so.id`
                + ` LEFT JOIN projects so_proj ON so_proj.request_id = so.id AND so_proj.is_estimate_job = 0`
                + ` LEFT JOIN (SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY request_id) so_inst ON so_inst.request_id = so.id`
                + ` LEFT JOIN (`
                + `   SELECT co.request_id,`
                + `     SUM(CASE`
                + `           WHEN cop.is_single_card = 1 THEN 1`
                + `           ELSE cop.location_quantity`
                + `         END) AS product_count`
                + `   FROM customer_orders co`
                + `   JOIN customer_order_products cop ON co.id = cop.order_id`
                + `   WHERE cop.material = 0`
                + `   GROUP BY co.request_id`
                + ` ) so_cop ON so_cop.request_id = so.id`
                + ` LEFT JOIN (`
                + `   SELECT co.request_id, COUNT(rss.id) AS service_count`
                + `   FROM customer_orders co`
                + `   JOIN request_services rss ON co.request_id = rss.request_id`
                + `   GROUP BY co.request_id`
                + ` ) so_rs ON so_rs.request_id = so.id`
                + ` WHERE ro.id = ${orderId} ${whereCondition}`)
            if (result[0]) {
                return result[0]
            }
            else {
                return false
            }
        }
    }
    catch (error) {
        console.log("Error fetching receive orders - ", error);
        return false
    }
}

const getOrderProducts = async (orderId, noStatusCheck=false) => {
    try {
        let whereCondition = ` AND mi.status != 0 `
        if(noStatusCheck){
            whereCondition = ''
        }
        const result = await sequelize.query(
            `SELECT p.*,p.syncWithQuickbooks AS material_synced,pt.syncWithQuickbook AS vendor_synced,lcs.name as service_name, ro.id AS order_id, ro.name AS order_name,pt.name AS vendor_name, pt.fax_number AS vendor_fax, ro.vendor_id, ro.transaction_id, `
            + ` ro.received_date , ro.expected_date , ro.include_tax, mi.id AS material_inventory_id, mi.unit_cost, mi.qty, mi.total_cost, mi.actual_qty, mi.actual_total_cost, `
            + ` ro.sub_total, ro.tax,ro.orderType, ro.grand_total, ro.payment_mode, ro.payment_status,ro.status AS order_status,ro.tax_category, ro.ship_via, ro.notes, mi.service_id,mi.service_cost,mi.shipping_cost, `
            + ` so.id AS sales_order_id, so.name AS sales_order_name, so.doc_number AS sales_order_doc_number, `
            + ` so_co.id AS sales_order_order_id, so_co.customer_id AS sales_order_customer_id, so_co.customer_name AS sales_order_customer_name,`
            + ` so_proj.id AS sales_order_project_id, so_proj.status AS sales_order_project_status, so_proj.noServiceNoTask AS sales_order_project_noServiceNoTask, `
            + ` so_inst.status AS sales_order_installation_status, so_inst.complete_status AS sales_order_installation_complete_status, `
            + ` COALESCE(so_cop.product_count,0) AS sales_order_product_count, COALESCE(so_rs.service_count,0) AS sales_order_service_count`
            + ` FROM receive_orders ro `
            + ` LEFT JOIN material_inventory mi on ro.id = mi.receive_order_id`
            + ` LEFT JOIN labour_cost_services lcs ON mi.service_id = lcs.id`
            + ` LEFT JOIN partners pt on ro.vendor_id = pt.id`
            + ` LEFT JOIN products p on mi.material_id = p.id`
            + ` LEFT JOIN requests so ON so.id = ro.sales_order_id`
            + ` LEFT JOIN customer_orders so_co ON so_co.request_id = so.id`
            + ` LEFT JOIN projects so_proj ON so_proj.request_id = so.id AND so_proj.is_estimate_job = 0`
            + ` LEFT JOIN (SELECT request_id, MAX(status) AS status, MAX(complete_status) AS complete_status FROM installations GROUP BY request_id) so_inst ON so_inst.request_id = so.id`
            + ` LEFT JOIN (`
            + `   SELECT co.request_id,`
            + `     SUM(CASE`
            + `           WHEN cop.is_single_card = 1 THEN 1`
            + `           ELSE cop.location_quantity`
            + `         END) AS product_count`
            + `   FROM customer_orders co`
            + `   JOIN customer_order_products cop ON co.id = cop.order_id`
            + `   WHERE cop.material = 0`
            + `   GROUP BY co.request_id`
            + ` ) so_cop ON so_cop.request_id = so.id`
            + ` LEFT JOIN (`
            + `   SELECT co.request_id, COUNT(rss.id) AS service_count`
            + `   FROM customer_orders co`
            + `   JOIN request_services rss ON co.request_id = rss.request_id`
            + `   GROUP BY co.request_id`
            + ` ) so_rs ON so_rs.request_id = so.id`
            + ` WHERE ro.id = ${orderId} ${whereCondition}`)
        if (result[0] && result[0].length > 0) {
            return result[0]
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching order products - ", error);
    }
}

const getOrderServices = async (orderId) => {
    try {
        const result = await sequelize.query(
            `SELECT ros.*, lcs.id AS labour_cost_service_id, lcs.name, lcs.total_cost AS service_total_cost, lcs.syncWithQuickbooks, lcs.quickbookId AS service_qb_id
            FROM receive_order_services ros
            LEFT JOIN labour_cost_services lcs ON ros.service_id = lcs.id
            WHERE ros.receive_order_id = ${orderId} AND ros.status = 1
            ORDER BY ros.id ASC`)
        if (result[0]) {
            return result[0]
        }
        else {
            return []
        }
    }
    catch (error) {
        console.log("Error fetching order services - ", error);
        return []
    }
}

const getFinishedProductsByReceiveOrderId = async (orderId) => {
    try {
        const result = await sequelize.query(`SELECT mi.* FROM material_inventory mi WHERE mi.receive_order_id = ${orderId} AND mi.status != 0`)
        if (result[0]) {
            return result[0]
        }else {
            return []
        }
    }catch (error) {
        console.log("Error fetching order products - ", error);
        return []
    }
}

const getFinishedProductsByUnitAndVendor = async (vendor_id,unit, orderId, material_id) => {
    try {
        const result = await sequelize.query(`
            SELECT mi.*,ro.vendor_id,p.unit FROM material_inventory mi 
            LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
            LEFT JOIN products p on mi.material_id = p.id
            WHERE ro.vendor_id = ${vendor_id} AND p.unit = ${unit} 
                AND mi.receive_order_id IS NOT NULL AND mi.receive_order_id != ${orderId}
                AND mi.status != 0 AND mi.partial = 0 AND mi.material_id = ${material_id}
            ORDER BY mi.id DESC
        `)
        if (result[0]) {
            return result[0]
        }else {
            return []
        }
    }catch (error) {
        console.log("Error fetching order products - ", error);
        return []
    }
}

const updateReceiveOrder = async (orderId, data) => {
    try {
        const result = await ReceiveOrders.update(data, {
            where: {
                id: orderId
            },
            returning: true,
        });
        if (result) {
            return result
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching receive orders - ", error);
    }
}

const cancelReceiveOrder = async (orderId) => {
    try {
        const result = await ReceiveOrders.destroy({
            where: {
                id: orderId
            },
        });
        if (result) {
            return result
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error cancelling order - ", error);
    }
}

const getMaterialById = async (materialId) => {
    try {
        const result = await sequelize.query(
            `SELECT mi.*, p.name AS product_name
             FROM material_inventory mi 
             LEFT JOIN products p ON p.id = mi.material_id 
             WHERE mi.id = ? AND mi.status != 0 ORDER BY mi.unit_cost DESC`, 
            {
                replacements: [materialId],
                type: sequelize.QueryTypes.SELECT
            }
        );        
        if (result) {
            return result;
        } else {
            console.log("Material not found for id:", materialId);
            return [];
        }
    } catch (error) {
        console.error("Error in getMaterialById:", error);
        return [];
    }
};


const updateMaterialStock = async (productId, data) => {
    try{
        const result = await MaterialInventory.update(data, {
            where: { id: productId }
        })
        if (result) {
            return true
        } else {
            return false
        }
    }catch(error){
        return false
    }
}
const updateMaterialInventory = async (id, data) => {
    try{
        const result = await MaterialInventory.update(data, {
            where: { material_id: id }
        })
        if (result) {
            return true
        } else {
            return false
        }
    }catch(error){
        return false
    }
}

const updateMainLabelSettingsDB = async (data) => {
    console.log('data',data)
    try {
        const [affectedRows] = await MainLabelSettings.update(data, {
            where: { id: 1 }
          });
          return affectedRows > 0; 
    } catch (error) {
      throw error;
    }
  };

const getMainLabelSettingsDB = async () => {
try {
    const result = await MainLabelSettings.findOne({
    where: { id: 1 }
    });
    return result;
} catch (error) {
    throw error;
}
};
const getSmallLabelSettingsDB = async () => {
    try {
        const result = await SmallLabelSettings.findOne({
        where: { id: 1 }
        });
        return result;
    } catch (error) {
        throw error;
    }
    };

const updateSmallLabelSettingsDB = async (data) => {
    try {
        const [affectedRows] = await SmallLabelSettings.update(data, {
            where: { id: 1 }
          });
          return affectedRows > 0; 
    } catch (error) {
      throw error;
    }
  };

const isProductCategoryExist = async (name, productId = 0) => {
    let whereCondition = "";
    if (productId !== 0) {
        whereCondition = `  and p.id!=${productId}`
    }
    let query = await sequelize.query(`select id,p.name from product_category p  where p.name like '${name}' ${whereCondition}`);

    if (query[0].length >= 1) {
        return true;
    } else {
        return false;
    }
}
//sub category
const isProductSubCategoryExist = async (name, id = 0) => {
    let whereCondition = "";
    if (id !== 0) {
        whereCondition = `  and p.id!=${id}`
    }
    let query = await sequelize.query(`select id,p.name from product_sub_category p  where p.name like '${name}' ${whereCondition}`);

    if (query[0].length >= 1) {
        return true;
    } else {
        return false;
    }
}

const addProductCategory = async (data) => {

    try {

        let results = await ProductCategory.create(data)
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the projects ", error);
        return false;
    }
}
//sub category
const addProductSubCategory = async (data) => {

    try {

        let results = await ProductSubCategory.create(data)
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.error("Error adding the projects ", error);
        return false;
    }
}
const getProductCategoryById = async (id) => {
    try {
        let query = await sequelize.query(`SELECT p.*, concat(u.first_name,' ',u.last_name) as authorized_by FROM  product_category p`
            + ` LEFT JOIN users u ON p.added_by=u.id `
            + ` WHERE p.id = ${id} and p.status!=2`
        );

        if (query[0].length === 1) {
            return query[0][0];
        } else {
            return false;
        }

    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
//sub category
const getProductSubCategoryById = async (id) => {
    try {
        let query = await sequelize.query(`SELECT p.*, concat(u.first_name,' ',u.last_name) as authorized_by FROM  product_sub_category p`
            + ` LEFT JOIN users u ON p.added_by=u.id `
            + ` WHERE p.id = ${id} and p.status!=2`
        );

        if (query[0].length === 1) {
            return query[0][0];
        } else {
            return false;
        }

    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const updateProductCategory = async (data, id) => {
    let res = await ProductCategory.update(data, { where: { id: id } });
    return res[0] ? true : false;
}
//sub category
const updateProductSubCategory = async (data, id) => {
    let res = await ProductSubCategory.update(data, { where: { id: id } });
    return res[0] ? true : false;
}
const deleteProductCategory = async (id, status = 0) => {
    try {
        const deleted = await ProductCategory.destroy({
            where: {
                id: id
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}
//sub category
const deleteProductSubCategory = async (id, status = 0) => {
    try {
        const deleted = await ProductSubCategory.destroy({
            where: {
                id: id
            },
        });

        if (deleted) {
            return deleted;
        }
        else {
            return false;
        }
    }
    catch (error) {
        console.log("Delete error......", error)
    }
}
const getAllProductCategories = async (all = false, where = "") => {
    let whereCondition = "";
    if (all) {
        whereCondition = " where p.status!=2";
    }
    else {
        whereCondition = " where p.status=1";
    }
    if(!empty(where)){
        whereCondition+=where
    }
    let query = await sequelize.query(`select p.*,p.id as c_id,p.qbCategoryId, concat(u.first_name,' ',u.last_name) as authorized_by from product_category p `
        + ` left join users u on p.added_by=u.id `
        + ` ${whereCondition} order by p.id desc`)
    let data = [];
    if (query[0].length > 0) {
        data = query[0];
        return data;
    } else {
        return [];
    }
}
const getAllProductSubCategories = async (all = false, where = []) => {
    let whereCondition = "";
    if (all) {
        whereCondition = " where p.status!=2";
    }
    else {
        whereCondition = " where p.status=1";
    }
    let query = await sequelize.query(`select p.*,pc.name as category_name,pc.category_type, concat(u.first_name,' ',u.last_name) as authorized_by from product_sub_category p `
        + ` left join users u on p.added_by=u.id `
        + `left join product_category pc on pc.id = p.category_id`
        + ` ${whereCondition} order by p.id desc`)
    let data;
    if (query[0].length > 0) {
        data = query[0];
        return data;
    } else {
        return [];
    }
}
const getSubCatFromCategoryId = async (id) => {
    try {
        let result = await sequelize.query(`
        select sc.* from product_sub_category as sc where sc.category_id = ${id} and sc.status = 1`)
        if (!empty(result)) {
            return result[0]
        } else {
            return []
        }
    } catch (error) {
        console.log(error)
    }
}
const getAllLocations = async (id) => {
    let query = await sequelize.query(`select o.* from customer_order_products o`
        + ` left join customer_orders c on c.id = o.order_id `
        + ` where c.request_id=${id} `

    )
    console.log("$$$$$$$$TTTTTTTTTTTT")
    if (query[0]?.length > 0) {
        const productIds = query?.[0]?.map(item => item?.product_id).filter(Boolean);
        return productIds?.length > 0 ? productIds.join(', ') : '';         
    }
    else {
        return ""
    }
}
const getNonStockProducts = async (id) => {
    let query = await sequelize.query(`select o.* from customer_order_products o`
        + ` left join customer_orders c on c.id = o.order_id `
        + ` left join products p on o.product_id = p.id `
        + ` where c.request_id=${id} `
        + `AND o.material = 0 `
        + `AND p.inhouse = 1 `
        + `AND (p.isTrafficCatalog IS NULL OR p.isTrafficCatalog != 1)`

    )
    console.log("$$$$$$$$TTTTTTTTTTTT")
    if (query[0]?.length > 0) {
        const productIds = query?.[0]?.map(item => item?.product_id).filter(Boolean);
        return productIds?.length > 0 ? productIds.join(', ') : '';         
    }
    else {
        return ""
    }
}
const getAllProductsBasedOnRequest = async (id) => {
    let query = await sequelize.query(`select o.*,rl.id AS location_id,p.in_stock_no, p.workflow as workflow_id,MAX(ra.name) AS product_design,p.recipe_id, p.inhouse, p.isTrafficCatalog`
        + ` from customer_order_products o`
        + ` left join request_locations rl on rl.order_id = o.id `
        + ` left join customer_orders c on c.id = o.order_id `
        + ` left join products p on p.id=o.product_id`
        + ` LEFT JOIN request_attachments ra ON ra.product_id = p.id AND ra.request_id = c.request_id `
        + ` where c.request_id=${id} and o.material=0 GROUP BY rl.id`
    )
    console.log("$$$$$$$$TTTTTTTTTTTT")
    if (query[0].length > 0) {
        const result = query[0].map(record => parseDecimalFieldsForOrderProducts(record));
        let data = await Promise.all(result?.map(async row => {
            const workflowId = row?.workflow ? row?.workflow : row?.workflow_id
            let query2 = await sequelize.query(`SELECT w.*,ow.* FROM onboard_workflows w
        left join onboard_workflow_stages ow on ow.workflow_id=w.id
        where w.id=${workflowId} and ow.internal_stage=0`)


            row.workflow = query2[0]
            return row
        }))
        console.log("########Workflow data", data);
        return data;
    }
    else {
        return []
    }
}

// const getAllServicesBasedOnRequest = async (id,excludeLaborInstall=false) => {
//     let whereCondition = ` and o.product_id IS NULL `
//     if(excludeLaborInstall){
//         whereCondition += ` and o.labour_cost_service_id != ${10} `
//     }
//     let query = await sequelize.query(`select o.*,p.name as service_name, p.workflow as workflow_id,MAX(ra.name) AS product_design`
//         + ` from request_services o`
//         + ` left join requests r on r.id = o.request_id `
//         + ` left join customer_orders c on c.request_id = r.id `
//         + ` left join labour_cost_services p on p.id=o.labour_cost_service_id`
//         + ` LEFT JOIN request_attachments ra ON ra.product_id = p.id AND ra.request_id = c.request_id `
//         + ` where c.request_id=${id} and o.is_dos = 0 ${whereCondition} GROUP BY o.id`
//     ) // and o.rfp=0
//     console.log("$$$$$$$$TTTTTTTTTTTT")
//     if (query[0].length > 0) {
//         const result = query[0].map(record => parseDecimalFieldsForOrderProducts(record));
//         let data = await Promise.all(result?.map(async row => {
//             let query2 = await sequelize.query(`SELECT w.*,ow.* FROM onboard_workflows w
//                 left join onboard_workflow_stages ow on ow.workflow_id=w.id
//                 where w.id=${row.workflow_id} and ow.internal_stage=0`)
//             row.workflow = query2[0]
//             return row
//         }))
//         console.log("########Workflow data", data);
//         return data;
//     }
//     else {
//         return []
//     }
// }

const getAllServicesBasedOnRequest = async (id, excludeLaborInstall = false) => {
    try {
      // Build the conditional WHERE clause
      let includeLabourInstallService = true;
      let makeAsTaskCondition = true ? ` AND o.makeAsTask = 1 ` : ` AND (r.project_category = 11 OR (r.project_category != 11 AND o.makeAsTask = 1))`
      let whereCondition = `
        AND o.is_dos = 0 
        ${makeAsTaskCondition}
        AND (
          o.product_id IS NULL
          ${excludeLaborInstall ? 
            `OR o.labour_cost_service_id = 10`
            : includeLabourInstallService 
            ? '' :
            `AND o.labour_cost_service_id != 10` 
            }
        )
      `;
  
      // Main query to fetch request services
      const query = await sequelize.query(
        `
        SELECT 
          o.*, 
          p.name AS service_name, 
          p.workflow AS workflow_id,
          MAX(ra.name) AS product_design
        FROM 
          request_services o
        LEFT JOIN 
          requests r ON r.id = o.request_id
        LEFT JOIN 
          customer_orders c ON c.request_id = r.id
        LEFT JOIN 
          labour_cost_services p ON p.id = o.labour_cost_service_id
        LEFT JOIN 
          request_attachments ra ON ra.product_id = p.id AND ra.request_id = c.request_id
        WHERE 
          c.request_id = :id
          ${whereCondition}
        GROUP BY 
          o.id
        `,
        {
          replacements: { id },
          type: sequelize.QueryTypes.SELECT
        }
      );
  
      console.log("$$$$$$$$TTTTTTTTTTTT");
  
      if (query.length > 0) {
        // Parse decimal fields (if you have a custom function for that)
        const result = query.map(record => parseDecimalFieldsForOrderProducts(record));
  
        // Fetch associated workflow data
        const data = await Promise.all(
          result.map(async (row) => {
            const workflowQuery = await sequelize.query(
              `
              SELECT 
                w.*, 
                ow.* 
              FROM 
                onboard_workflows w
              LEFT JOIN 
                onboard_workflow_stages ow ON ow.workflow_id = w.id
              WHERE 
                w.id = :workflow_id AND ow.internal_stage = 0
              `,
              {
                replacements: { workflow_id: row.workflow_id },
                type: sequelize.QueryTypes.SELECT
              }
            );
  
            row.workflow = workflowQuery;
            return row;
          })
        );
  
        console.log("########Workflow data", data);
        return data;
      } else {
        return [];
      }
  
    } catch (error) {
      console.error("Error in getAllServicesBasedOnRequest:", error);
      return [];
    }
  };

  
const getServiceWithWorkflowById = async (serviceId) => {
    try {
      console.log('🔍 MODEL: Getting service with workflow for serviceId:', serviceId);
      const query = await sequelize.query(
        `
        SELECT 
          lcs.*,
          lcs.name AS service_name,
          lcs.workflow AS workflow_id
        FROM 
          labour_cost_services lcs
        WHERE 
          lcs.id = :serviceId
        `,
        {
          replacements: { serviceId },
          type: sequelize.QueryTypes.SELECT
        }
      );
      if (query && query.length > 0) {
        console.log('🔍 MODEL: Returning service data:', query[0]);
        return query[0];
      } else {
        console.log('🔍 MODEL: No service found for serviceId:', serviceId);
        return null;
      }
    } catch (error) {
      console.error("Error in getServiceWithWorkflowById:", error);
      return null;
    }
};

const getWorkflowById = async (id) => {
    console.log("############Workflowwwwwww", id);
    let query = await sequelize.query(`SELECT w.*,ow.* FROM onboard_workflows w
        left join onboard_workflow_stages ow on ow.workflow_id=w.id
        where w.id in (${id})`)
      console.log("############Workflowwwwwwwxxx", query);
        if (query[0].length > 0) {
            return query[0]
        }
        else {
            return []
        }
}

const getServiceByProduct = async (id) => {
    try {

        let query = await sequelize.query(
            ` SELECT ls.*,ls.total_cost as unit_cost,sr.quantity,sr.quantity as prQty,p.id AS product_id,r.id AS recipe_id,ls.markup as originalMarkup from products p `
            + ` INNER JOIN recipes r ON r.id = p.recipe_id `
            + ` INNER JOIN services_recipe sr ON sr.recipe_id = r.id `
            + ` INNER JOIN labour_cost_services ls ON ls.id = sr.service_id `
            + ` WHERE p.id = ${id} `
        );

        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const getServiceByProductNew = async (id) => {
    try {
      const query = `
        SELECT 
          ls.*,
          ls.total_cost AS unit_cost,
          sr.quantity,
          sr.quantity AS prQty,
          p.id AS product_id,
          r.id AS recipe_id,
          ls.markup AS originalMarkup
        FROM products p
        INNER JOIN recipes r ON r.id = p.recipe_id
        INNER JOIN services_recipe sr ON sr.recipe_id = r.id
        INNER JOIN labour_cost_services ls ON ls.id = sr.service_id
        WHERE p.id = :productId
      `;
  
      const result = await sequelize.query(query, {
        replacements: { productId: id },
        type: sequelize.QueryTypes.SELECT,
      });
  
      return result.length ? result : [];
    } catch (error) {
      console.error(error);
      throw new Error("Server error");
    }
  };
const getServicesForBulkProductsByIds = async (productIds) => {
try {
    const query = `
    SELECT 
        ls.*,
        ls.total_cost AS unit_cost,
        sr.quantity,
        sr.quantity AS prQty,
        p.id AS product_id,
        r.id AS recipe_id,
        ls.markup AS originalMarkup
    FROM products p
    INNER JOIN recipes r ON r.id = p.recipe_id
    INNER JOIN services_recipe sr ON sr.recipe_id = r.id
    INNER JOIN labour_cost_services ls ON ls.id = sr.service_id
    WHERE p.id IN (:productIds)
    `;

    return await sequelize.query(query, {
    replacements: { productIds },
    type: sequelize.QueryTypes.SELECT,
    });

} catch (error) {
    console.error(error);
    throw new Error("Server error");
}
};
  
const getMaterialByProduct = async (id) => {
    try {

        let query = await sequelize.query(
            ` SELECT p2.*,p2.price as unit_cost,
                (
                    SELECT MAX(minv.unit_cost)
                    FROM material_inventory minv
                    WHERE minv.material_id = p2.id
                    AND minv.partial = (cr.stockType - 1)
                ) AS max_unit_cost,
                cr.quantity,cr.convertionUnit,cr.quantity as prQty,cr.stockType,p.id AS product_id,r.id AS recipe_id from products p `
            + ` INNER JOIN recipes r ON r.id = p.recipe_id `
            + ` INNER JOIN catelog_recipe cr ON cr.recipe_id = r.id `
            + ` INNER JOIN products p2 ON p2.id = cr.product_id `
            + ` WHERE p.id = ${id} `
        );

        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const getMaterialByProductNew = async (id) => {
    try {
      const query = `
        SELECT 
          p2.*,
          p2.price AS unit_cost,
          (
            SELECT MAX(minv.unit_cost)
            FROM material_inventory minv
            WHERE minv.material_id = p2.id
              AND minv.partial = (cr.stockType - 1)
          ) AS max_unit_cost,
          cr.quantity,
          cr.convertionUnit,
          cr.quantity AS prQty,
          cr.stockType,
          p.id AS product_id,
          r.id AS recipe_id
        FROM products p
        INNER JOIN recipes r ON r.id = p.recipe_id
        INNER JOIN catelog_recipe cr ON cr.recipe_id = r.id
        INNER JOIN products p2 ON p2.id = cr.product_id
        WHERE p.id = :productId
      `;
  
      const result = await sequelize.query(query, {
        replacements: { productId: id },
        type: sequelize.QueryTypes.SELECT,
      });
  
      return result.length ? result : [];
    } catch (error) {
      console.error(error);
      throw new Error("Server error");
    }
  };
  const getMaterialsForBulkProductsByIds = async (productIds) => {
    try {
      const query = `
        SELECT 
          p2.*,
          p2.price AS unit_cost,
          (
            SELECT MAX(minv.unit_cost)
            FROM material_inventory minv
            WHERE minv.material_id = p2.id
              AND minv.partial = (cr.stockType - 1)
          ) AS max_unit_cost,
          cr.quantity,
          cr.convertionUnit,
          cr.quantity AS prQty,
          cr.stockType,
          p.id AS product_id,
          r.id AS recipe_id
        FROM products p
        INNER JOIN recipes r ON r.id = p.recipe_id
        INNER JOIN catelog_recipe cr ON cr.recipe_id = r.id
        INNER JOIN products p2 ON p2.id = cr.product_id
        WHERE p.id IN (:productIds)
      `;
  
      return await sequelize.query(query, {
        replacements: { productIds },
        type: sequelize.QueryTypes.SELECT,
      });
  
    } catch (error) {
      console.error(error);
      throw new Error("Server error");
    }
  };
   
  
const fetchAllProductsByCategory = async (all = false, material,category) => {
    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`;
        }
        let query = await sequelize.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,pc.markup as retailMarkup,pc.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.category_id=${category} ` +
            ` ORDER BY p.id DESC `,
        );

        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const fetchAllProductsByRecipe = async (all = false, material,recipe) => {
    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`;
        }
        let query = await sequelize.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=${recipe} ` +
            ` ORDER BY p.id DESC `,
        );

        let data;
        if (query.length > 0) {
            data = query[0];
            return data;
        } else {
            return [];
        }
    }
    catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
}
const fetchRecipesBasedOnService=async(id)=>{
    let query=await sequelize.query(` select recipe_id from services_recipe where  service_id=${id} `)
    if (query.length > 0) {
        data = query[0];
        return data;
    } else {
        return [];
    }
}
const fetchRecipesBasedOnMaterial=async(id)=>{
    let query=await sequelize.query(` select recipe_id from catelog_recipe where product_id=${id} `)
    if (query.length > 0) {
        data = query[0];
        return data;
    } else {
        return [];
    }
}

const getMaterialInventoryByMaterialId = async (material_id) => {
    try {
        let materialInventory = await sequelize.query(
            `SELECT mi.* 
            FROM material_inventory mi 
            LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id 
            WHERE mi.material_id = :material_id 
            AND mi.status != 0
            AND (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))`, 
            {
                replacements: { material_id },
                type: sequelize.QueryTypes.SELECT
            }
        );
        console.log('materialInventory---',materialInventory)
        if (materialInventory.length > 0) {
            return materialInventory;
        } else {
            return false;
        }
    } catch (error) {
        console.error('Error fetching MaterialInventory by material_id:', error);
        throw error;
    }
};

// Get last unit cost for a vendor-material combination
const getLastVendorMaterialUnitCost = async (vendor_id, material_id) => {
    try {
        const result = await sequelize.query(
            `SELECT mi.unit_cost 
            FROM material_inventory mi 
            LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id 
            WHERE mi.material_id = :material_id 
            AND ro.vendor_id = :vendor_id
            AND mi.status != 0
            ORDER BY mi.timestamp DESC
            LIMIT 1`, 
            {
                replacements: { vendor_id, material_id },
                type: sequelize.QueryTypes.SELECT
            }
        );
        
        if (result && result.length > 0 && result[0].unit_cost) {
            return parseFloat(result[0].unit_cost);
        } else {
            return null;
        }
    } catch (error) {
        console.error('Error fetching last vendor material unit cost:', error);
        return null;
    }
};


  const findProductByName = async (productName) => {
    try {
    
      const product = await Products.findOne({
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: productName } },
                { status: { [Sequelize.Op.ne]: 2 } },
            ]
        }
      });
  
     
      if (product) {
        return product;
      } else {
        console.log('Product not found');
        return null;
      }
    } catch (error) {
      console.error('Error finding product by name:', error);
      throw error; 
    }
  };

  const findServiceByName = async (serviceName) => {
    try {
    
      const service = await LabourCostServices.findOne({
        where: {
            [Sequelize.Op.and]: [
                { name: { [Sequelize.Op.eq]: serviceName } },
                { status: { [Sequelize.Op.ne]: 2 } },
            ]
        }
      });
  
     
      console.log('serviceservice--2-', service)
      if (service) {
        return service;
      } else {
        console.log('Service not found');
        return null;
      }
    } catch (error) {
      console.error('Error finding service by name:', error);
      throw error; 
    }
  };
  
  const getFinishedProductsByProductId = async (editId) => {
    const result = await sequelize.query(`SELECT mi.*,p.name AS product_name FROM material_inventory mi 
        LEFT JOIN products p ON p.id = mi.material_id 
        LEFT JOIN receive_orders ro on ro.id = mi.receive_order_id
        WHERE p.id=${editId} and mi.qty > 0 AND mi.status != 0 AND (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) LIMIT 1`)
    if (result[0]?.length == 1) {
        return result[0][0]
    } else {
        return false
    }
}

const getAllFinishedProductsByProductId = async (editId) => {
    const result = await sequelize.query(`SELECT mi.*,p.name AS product_name FROM material_inventory mi 
        LEFT JOIN products p ON p.id = mi.material_id 
        LEFT JOIN receive_orders ro on ro.id = mi.receive_order_id
        WHERE p.id=${editId} and mi.qty > 0 AND mi.status != 0 AND (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))`)
    if (result[0]?.length > 0) {
        return result[0]
    } else {
        return []
    }
}

const getMaterialData = async (material_id) => {
    try {
      // Query to get the product data by material ID
      const productQuery = 'SELECT * FROM products WHERE id = :materialId';
      const productData = await sequelize.query(productQuery, {
        replacements: { materialId: material_id },
        type: sequelize.QueryTypes.SELECT
      });

  
      // Returning the fetched data
      return  productData[0]
      
    } catch (error) {
      console.error('Error fetching material and vendor data:', error);
      throw error;
    }
  };
const getVendorData = async (vendor_id) => {
    try {

  
      // Query to get the vendor data by vendor ID
      const vendorQuery = 'SELECT * FROM partners WHERE id = :vendorId';
      const vendorData = await sequelize.query(vendorQuery, {
        replacements: { vendorId: vendor_id },
        type: sequelize.QueryTypes.SELECT
      });
  
      // Returning the fetched data
      return vendorData[0]
    } catch (error) {
      console.error('Error fetching material and vendor data:', error);
      throw error;
    }
  };

  const checkMaterialQbCategoryExist=async(category)=>{
    let query=await sequelize.query(` select qbCategoryId from product_category where name="${category}" and category_type=2`)
    // let query=await sequelize.query(` select id from product_category where name="${category}" and category_type=1`)
    if(query[0].length>0){
        return query[0][0].qbCategoryId;
    }
    else{
        return false;
    }
}

const checkMaterialQbSubCategoryExist=async(subCategory,category)=>{
    let query=await sequelize.query(` select pc.qbSubCategoryId from product_sub_category pc 
    left join product_category c on c.category_type=pc.id
    where pc.name="${subCategory}"  and pc.category_id=${category}`)
    if(query[0].length>0){
        return query[0][0].qbSubCategoryId;
    }
    else{
        return false;
    }
}

const findProductCategoryById = async (categoryId) => {
    try {
      const category = await ProductCategory.findOne({
        where: { id: categoryId }
      });
  
      if (!category) {
        console.error(`Category with ID ${categoryId} not found.`);
        return null;
      }
  
      console.log('Category found:', category);
      return category.dataValues;
    } catch (error) {
      console.error('Error finding product category by ID:', error);
      return null;
    }
  };
const findProductSubCategoryById = async (categoryId) => {
    try {
      const category = await ProductSubCategory.findOne({
        where: { id: categoryId }
      });
  
      if (!category) {
        console.error(`Category with ID ${categoryId} not found.`);
        return null;
      }
  
      console.log('Category found:', category);
      return category.dataValues;
    } catch (error) {
      console.error('Error finding product category by ID:', error);
      return null;
    }
  };










//its return exact match for all fields
// const getAllMaterialsForInvAudit = async (all = false, page = 1, limit = 10, search = '') => {
//     try {
//       const offset = (page - 1) * limit;
//       let whereCondition = all ? `p.status != 2` : `p.status = 1`;
  
//       let searchCondition = '';
//       if (search) {
//         const escapedSearch = search.replace(/'/g, "''").trim();
//         const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
  
//         const matchedUnit = UNITS.find(
//           (u) =>
//             u.name.toLowerCase() === escapedSearch.toLowerCase() ||
//             u.short_name.toLowerCase() === escapedSearch.toLowerCase()
//         );
//         const unitIdMatch = matchedUnit ? matchedUnit.id : null;
  
//         const matchedMaterialType = MATERIALTYPES.find(
//           (m) => m.name.toLowerCase() === escapedSearch.toLowerCase()
//         );
//         const materialTypeIdMatch = matchedMaterialType ? matchedMaterialType.id : null;
  
//         const conditions = [];
  
//         if (unitIdMatch) {
//           conditions.push(`p.unit = ${unitIdMatch}`);
//         }
  
//         if (materialTypeIdMatch) {
//           conditions.push(`p.material_types = ${materialTypeIdMatch}`);
//         }
  
//         // Exact match for other fields (normalized)
//         const fieldsToMatch = [
//           'p.name',
//           'p.description',
//           'p.vpt_number',
//           'p.tags',
//           'pc.name',
//           'psc.name'
//         ];
  
//         fieldsToMatch.forEach(field => {
//           conditions.push(`
//             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(${field}), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') = '${normalizedSearch}'
//           `);
//         });
  
//         // Vendor name match
//         conditions.push(`
//           EXISTS (
//             SELECT 1 FROM partners pr2
//             WHERE FIND_IN_SET(pr2.id, p.vendor_id) > 0
//             AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(pr2.name), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') = '${normalizedSearch}'
//           )
//         `);
  
//         searchCondition = `AND (${conditions.join(' OR ')})`;
//       }
  
//       const query = await sequelize.query(
//         `SELECT p.*,
//           CONCAT(u.first_name,' ',u.last_name) AS authorized_by,
//           tc.name AS tax,
//           pc.name AS cat_name,
//           psc.name AS sub_cat_name,
//           SUM(DISTINCT CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END) AS total_qty,
//           MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price,
//           MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,
//           SUM(DISTINCT 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(DISTINCT 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,
//           rc.expected_cost,
//           rc.expected_material_cost,
//           rc.expected_service_cost,
//           rc.expected_total_service_cost,
//           GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names
//         FROM products p
//         LEFT JOIN users u ON p.added_by = u.id
//         LEFT JOIN tax_category tc ON p.tax_category = tc.id
//         LEFT JOIN material_inventory mi ON p.id = mi.material_id
//         LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
//         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 recipes rc ON p.recipe_id = rc.id
//         LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
//         WHERE ${whereCondition}
//           AND p.meterial = 1
//           AND p.isAdditionalProduct = 0
//           AND p.status != 0
//           ${searchCondition}
//         GROUP BY p.id
//         ORDER BY p.id DESC
//         LIMIT ${limit} OFFSET ${offset}`
//       );
  
//       let data = query[0] || [];
//       data = data.map((item) => {
//         if (!item.price) item.price = 0;
//         return item;
//       });
  
//       const countQuery = await sequelize.query(
//         `SELECT COUNT(DISTINCT p.id) AS total
//          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 partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
//          WHERE ${whereCondition}
//            AND p.meterial = 1
//            AND p.isAdditionalProduct = 0
//            AND p.status != 0
//            ${searchCondition}`
//       );
  
//       const total = countQuery[0][0].total || 0;
  
//       return { data, total };
  
//     } catch (error) {
//       console.error("Server error:", error);
//       throw new Error("Server error");
//     }
//   };
  
// its return  exact match first and then partial match next
//not working unit search
// const getAllMaterialsForInvAudit = async (all = false, page = 1, limit = 10, search = '') => {
//     try {
//       const offset = (page - 1) * limit;
//       let whereCondition = all ? `p.status != 2` : `p.status = 1`;
  
//       let matchConditions = '';
//       let relevanceScore = '';
//       const relevanceParts = [];
  
//       if (search) {
//         const escapedSearch = search.replace(/'/g, "''").trim();
//         const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
  
//         const matchedUnit = UNITS.find(
//           (u) =>
//             u.name.toLowerCase() === escapedSearch.toLowerCase() ||
//             u.short_name.toLowerCase() === escapedSearch.toLowerCase()
//         );
//         const unitIdMatch = matchedUnit ? matchedUnit.id : null;
  
//         const matchedMaterialType = MATERIALTYPES.find(
//           (m) => m.name.toLowerCase() === escapedSearch.toLowerCase()
//         );
//         const materialTypeIdMatch = matchedMaterialType ? matchedMaterialType.id : null;
  
//         const fields = [
//           { field: 'p.name', label: 'name' },
//           { field: 'p.description', label: 'description' },
//           { field: 'p.vpt_number', label: 'vpt_number' },
//           { field: 'p.tags', label: 'tags' },
//           { field: 'pc.name', label: 'category' },
//           { field: 'psc.name', label: 'subcategory' }
//         ];
  
//         const matchLikeConditions = [];
  
//         fields.forEach(({ field }) => {
//           const cleanField = `REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(${field}), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '')`;
//           matchLikeConditions.push(`${cleanField} LIKE '%${normalizedSearch}%'`);
//           relevanceParts.push(`(CASE WHEN ${cleanField} = '${normalizedSearch}' THEN 5
//                               WHEN ${cleanField} LIKE '${normalizedSearch}%' THEN 3
//                               WHEN ${cleanField} LIKE '%${normalizedSearch}%' THEN 1
//                               ELSE 0 END)`);
//         });
  
//         // Vendor match
//         const vendorMatchCondition = `
//           EXISTS (
//             SELECT 1 FROM partners pr2
//             WHERE FIND_IN_SET(pr2.id, p.vendor_id) > 0
//             AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(pr2.name), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%'
//           )
//         `;
//         matchLikeConditions.push(vendorMatchCondition);
//         relevanceParts.push(`(CASE WHEN ${vendorMatchCondition} THEN 2 ELSE 0 END)`);
  
//         // Unit match
//         if (unitIdMatch) {
//           matchLikeConditions.push(`p.unit = ${unitIdMatch}`);
//           relevanceParts.push(`5`);
//         }
  
//         // Material type match
//         if (materialTypeIdMatch) {
//           matchLikeConditions.push(`p.material_types = ${materialTypeIdMatch}`);
//           relevanceParts.push(`5`);
//         }
  
//         matchConditions = `AND (${matchLikeConditions.join(' OR ')})`;
//         relevanceScore = `(${relevanceParts.join(' + ')}) AS relevance`;
//       }
  
//       const query = await sequelize.query(
//         `SELECT p.*,
//           CONCAT(u.first_name,' ',u.last_name) AS authorized_by,
//           tc.name AS tax,
//           pc.name AS cat_name,
//           psc.name AS sub_cat_name,
//           SUM(DISTINCT CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END) AS total_qty,
//           MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price,
//           MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,
//           SUM(DISTINCT 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(DISTINCT 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,
//           rc.expected_cost,
//           rc.expected_material_cost,
//           rc.expected_service_cost,
//           rc.expected_total_service_cost,
//           GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names,
//           ${relevanceScore || '0 AS relevance'}
//         FROM products p
//         LEFT JOIN users u ON p.added_by = u.id
//         LEFT JOIN tax_category tc ON p.tax_category = tc.id
//         LEFT JOIN material_inventory mi ON p.id = mi.material_id
//         LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
//         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 recipes rc ON p.recipe_id = rc.id
//         LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
//         WHERE ${whereCondition}
//           AND p.meterial = 1
//           AND p.isAdditionalProduct = 0
//           AND p.status != 0
//           ${matchConditions}
//         GROUP BY p.id
//         ORDER BY relevance DESC, p.id DESC
//         LIMIT ${limit} OFFSET ${offset}`
//       );
  
//       let data = query[0] || [];
//       data = data.map((item) => {
//         if (!item.price) item.price = 0;
//         return item;
//       });
  
//       const countQuery = await sequelize.query(
//         `SELECT COUNT(DISTINCT p.id) AS total
//          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 partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
//          WHERE ${whereCondition}
//            AND p.meterial = 1
//            AND p.isAdditionalProduct = 0
//            AND p.status != 0
//            ${matchConditions}`
//       );
  
//       const total = countQuery[0]?.[0]?.total || 0;
  
//       return { data, total };
  
//     } catch (error) {
//       console.error("Server error:", error);
//       throw new Error("Server error");
//     }
//   };
  
//all good exept min,max,partial,finished
const getAllMaterialsForInvAudit = async (all = false, page = 1, limit = 10, search = '') => {
    try {
      const offset = (page - 1) * limit;
      let whereCondition = all ? `p.status != 2` : `p.status = 1`;
  
      let matchConditions = '';
      let relevanceScore = '';
      const relevanceParts = [];
  
      if (search) {
        const escapedSearch = search.replace(/'/g, "''").trim();
        const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
  
        const fields = [
          { field: 'p.name' },
          { field: 'p.description' },
          { field: 'p.vpt_number' },
          { field: 'p.tags' },
          { field: 'mi.barcode' },
        //   { field: 'pc.name' },
        //   { field: 'psc.name' }
        ];
  
        const matchLikeConditions = [];
  
        // Search field matching (normalized)
        fields.forEach(({ field }) => {
          const cleanField = `REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(${field}), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '')`;
          matchLikeConditions.push(`${cleanField} LIKE '%${normalizedSearch}%'`);
          relevanceParts.push(`(CASE 
            WHEN ${cleanField} = '${normalizedSearch}' THEN 5
            WHEN ${cleanField} LIKE '${normalizedSearch}%' THEN 3
            WHEN ${cleanField} LIKE '%${normalizedSearch}%' THEN 1
            ELSE 0 END)`);
        });
  
        // Vendor match
        const vendorMatchCondition = `
          EXISTS (
            SELECT 1 FROM partners pr2
            WHERE FIND_IN_SET(pr2.id, p.vendor_id) > 0
            AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(pr2.name), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%'
          )
        `;
        matchLikeConditions.push(vendorMatchCondition);
        relevanceParts.push(`(CASE WHEN ${vendorMatchCondition} THEN 2 ELSE 0 END)`);
  
        // Unit matches (by UNITS list)
        const matchedUnits = UNITS.filter(
          u =>
            u.name.toLowerCase().includes(normalizedSearch) ||
            u.short_name.toLowerCase().includes(normalizedSearch)
        ).map(u => u.id);
  
        if (matchedUnits.length > 0) {
          matchLikeConditions.push(`p.unit IN (${matchedUnits.join(',')})`);
          relevanceParts.push(`5`);
        }
  
        // Material type matches (by MATERIALTYPES list)
        const matchedMaterialTypes = MATERIALTYPES.filter(
          m => m.name.toLowerCase().includes(normalizedSearch)
        ).map(m => m.id);
  
        if (matchedMaterialTypes.length > 0) {
          matchLikeConditions.push(`p.material_types IN (${matchedMaterialTypes.join(',')})`);
          relevanceParts.push(`5`);
        }
  
        matchConditions = `AND (${matchLikeConditions.join(' OR ')})`;
        relevanceScore = `(${relevanceParts.join(' + ')}) AS relevance`;
      }
  
      const query = await sequelize.query(
        `SELECT p.*,
          CONCAT(u.first_name,' ',u.last_name) AS authorized_by,
          tc.name AS tax,
          pc.name AS cat_name,
          psc.name AS sub_cat_name,
          SUM(DISTINCT CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END) AS total_qty,
          MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price,
          MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial,
          SUM(DISTINCT 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(DISTINCT 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,
          rc.expected_cost,
          rc.expected_material_cost,
          rc.expected_service_cost,
          rc.expected_total_service_cost,
          GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names,
          ${relevanceScore || '0 AS relevance'}
        FROM products p
        LEFT JOIN users u ON p.added_by = u.id
        LEFT JOIN tax_category tc ON p.tax_category = tc.id
        LEFT JOIN material_inventory mi ON p.id = mi.material_id
        LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
        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 recipes rc ON p.recipe_id = rc.id
        LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
        WHERE ${whereCondition}
          AND p.meterial = 1
          AND p.isAdditionalProduct = 0
          AND p.status != 0
          ${matchConditions}
        GROUP BY p.id
        ORDER BY relevance DESC, p.name ASC
        LIMIT ${limit} OFFSET ${offset}`
      );
  
      let data = query[0] || [];
      data = data.map((item) => {
        if (!item.price) item.price = 0;
        return item;
      });
  
      const countQuery = await sequelize.query(
        `SELECT COUNT(DISTINCT p.id) AS total
         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 partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0
         LEFT JOIN material_inventory mi ON p.id = mi.material_id
         WHERE ${whereCondition}
           AND p.meterial = 1
           AND p.isAdditionalProduct = 0
           AND p.status != 0
           ${matchConditions}`
      );
  
      const total = parseInt(countQuery[0]?.[0]?.total) || 0;
  
      return { data, total };
  
    } catch (error) {
      console.error("Server error:", error);
      throw new Error("Server error");
    }
  };
  
const searchFinishedProductByBarcode = async (barcode, isMaterial) => {
    try {
        const query = await sequelize.query(
            `SELECT p.*,
        CONCAT(u.first_name,' ',u.last_name) AS authorized_by,
        tc.name AS tax,
        pc.name AS cat_name,
        psc.name AS sub_cat_name,

        -- Total quantity
        SUM(DISTINCT CASE 
          WHEN mi.source = 0 THEN mi.qty 
          WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty 
          ELSE 0 
        END) AS total_qty,

        -- Max price for finished products
        MAX(CASE 
          WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
            AND mi.qty > 0 
            AND mi.partial = 0 
          THEN mi.unit_cost 
          ELSE NULL 
        END) AS max_price,

        -- Partial flag
        MAX(CASE 
          WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) 
            AND mi.partial = 1 
            AND mi.status = 1 
          THEN 1 
          ELSE 0 
        END) AS partial,

        -- Finished product quantity
        SUM(DISTINCT 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,

        -- Partial product quantity
        SUM(DISTINCT 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,

        rc.expected_cost,
        rc.expected_material_cost,
        rc.expected_service_cost,
        rc.expected_total_service_cost,
        GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names

      FROM products p
      LEFT JOIN users u ON p.added_by = u.id
      LEFT JOIN tax_category tc ON p.tax_category = tc.id
      LEFT JOIN material_inventory mi ON p.id = mi.material_id
      LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
      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 recipes rc ON p.recipe_id = rc.id
      LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0

      WHERE p.id = (
        SELECT mi2.material_id 
        FROM material_inventory mi2 
        WHERE mi2.barcode = :barcode 
        LIMIT 1
      )
        AND p.isAdditionalProduct = 0
        AND p.status != 0
        ${isMaterial ? 'AND p.meterial = 1' : ''}
        AND (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
        AND mi.status != 0

      GROUP BY p.id`,
            {
                replacements: { barcode },
                type: sequelize.QueryTypes.SELECT
            }
        );

        return {
            data: query,
            total: query.length
        };
    } catch (error) {
        console.error("Error searching finished product by barcode:", error);
        throw error;
    }
};

  const getMaterialDataFromRequestId = async (requestId) => {
    try {
      const query = `
        SELECT 
          cop.*, 
          (
            SELECT ro.doc_number
            FROM material_inventory mi
            LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id
            WHERE mi.material_id = cop.product_id
              AND mi.receive_order_id IS NOT NULL
            LIMIT 1
          ) AS receive_doc_number,
          CASE 
            WHEN EXISTS (
              SELECT 1
              FROM material_inventory mi
              WHERE mi.material_id = cop.product_id
                AND mi.receive_order_id IS NOT NULL
            ) THEN TRUE 
            ELSE FALSE 
          END AS "isReceived"
        FROM customer_order_products cop
        JOIN customer_orders co ON co.id = cop.order_id
        JOIN products p ON p.id = cop.product_id
        WHERE cop.material = 1 
          AND p.syncWithQuickbooks = 1
          AND co.request_id = :requestId
        ORDER BY cop.id;
      `;
  
      const results = await sequelize.query(query, {
        replacements: { requestId },
        type: sequelize.QueryTypes.SELECT
      });
  
      return results; // ✅ Correct: returns an array of rows
  
    } catch (error) {
      console.error("Error fetching material data:", error);
      return [];
    }
  };
  
  
  
const getOrderProductsFromQbBillId = async (bill_id) => {
    try {
       
        const result = await sequelize.query(
            `SELECT p.*,p.syncWithQuickbooks AS material_synced,pt.syncWithQuickbook AS vendor_synced,pt.quickbookCustomerName AS qbVendorName, ro.id AS order_id, ro.name AS order_name,pt.name AS vendor_name, pt.fax_number AS vendor_fax, ro.vendor_id, ro.transaction_id, `
            + ` ro.received_date , ro.expected_date , ro.include_tax, mi.unit_cost, mi.qty, mi.total_cost, mi.actual_qty, mi.actual_total_cost, `
            + ` ro.sub_total,ro.qb_bill_id, ro.tax, ro.grand_total, ro.payment_mode, ro.payment_status,ro.status AS order_status,ro.tax_category, ro.ship_via, ro.notes	`
            + ` FROM receive_orders ro `
            + ` LEFT JOIN material_inventory mi on ro.id = mi.receive_order_id`
            + ` LEFT JOIN partners pt on ro.vendor_id = pt.id`
            + ` LEFT JOIN products p on mi.material_id = p.id`
            + ` WHERE ro.qb_bill_id = ${bill_id} AND ro.status =1`)
        if (result[0]) {
            return result[0]
        }
        else {
            return false
        }
    }
    catch (error) {
        console.log("Error fetching order products - ", error);
    }
}

  
  const updateOrderStatusFromQbBillId = async (bill_id, newStatus, newPaymentStatus) => {
  try {
    const [result] = await sequelize.query(
      `UPDATE receive_orders 
       SET status = :status, 
           payment_status = :paymentStatus 
       WHERE qb_bill_id = :billId`,
      {
        replacements: {
          status: newStatus,
          paymentStatus: newPaymentStatus,
          billId: bill_id,
        },
        type: sequelize.QueryTypes.UPDATE
      }
    );

    return result; // number of affected rows
  } catch (error) {
    console.error("Error updating order status by bill ID:", error);
    throw error;
  }
};

  
const getMaterialProductsBySubCategory = async (subCategoryIdOrIds) => {
  const subCategoryId = subCategoryIdOrIds;
  try {
    const results = await sequelize.query(
      `
      SELECT p.*
      FROM products AS p
      WHERE p.sub_category_id ${Array.isArray(subCategoryId) ? 'IN (:subCategoryId)' : '= :subCategoryId'}
        AND p.meterial = 1
      ORDER BY p.id DESC
      `,
      { replacements: { subCategoryId }, type: sequelize.QueryTypes.SELECT }
    );
    return results;
  } catch (error) {
    console.error('Error fetching products by sub category (material=1):', error);
    return [];
  }
};

const catalogByReceipte = async (recipeId) => {
  try {
    const rows = await sequelize.query(
      `
      SELECT p.*
      FROM products AS p
      WHERE p.recipe_id = :recipeId
        AND p.meterial = 0
        AND p.status = 1
      ORDER BY p.id DESC
      `,
      { replacements: { recipeId }, type: sequelize.QueryTypes.SELECT }
    );
    return rows;
  } catch (err) {
    console.error('catalogByReceipte error:', err);
    return [];
  }
};

const getAllStockCatalogForPO = async (all = false) => {
    try {
        let whereCondition;

        if (all) {
            whereCondition = `p.status != 2`;
        } else {
            whereCondition = `p.status = 1`;
        }

        let query = await sequelize.query(
            ` SELECT p.*, 
                     CONCAT(u.first_name,' ',u.last_name) AS authorized_by, 
                     tc.name AS tax, 
                     pc.name AS cat_name, 
                     psc.name AS sub_cat_name, 
                     SUM(CASE WHEN mi.source = 0 THEN mi.qty WHEN mi.source = 1 AND ro.status != 0 THEN mi.qty ELSE 0 END) AS total_qty,
                     MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.qty > 0 AND mi.partial = 0 THEN mi.unit_cost ELSE NULL END) AS max_price, 
                     MAX(CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0)) AND mi.partial = 1 AND mi.status = 1 THEN 1 ELSE 0 END) AS partial, 
                     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, 
                     rc.expected_cost, 
                     rc.expected_material_cost, 
                     rc.expected_service_cost, 
                     rc.expected_total_service_cost, 
                     GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.name SEPARATOR ', ') AS vendor_names 
              FROM products p 
              LEFT JOIN users u ON p.added_by = u.id 
              LEFT JOIN tax_category tc ON p.tax_category = tc.id 
              LEFT JOIN material_inventory mi ON p.id = mi.material_id 
              LEFT JOIN receive_orders ro ON ro.id = mi.receive_order_id 
              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 recipes rc ON p.recipe_id = rc.id
              LEFT JOIN partners pr ON FIND_IN_SET(pr.id, p.vendor_id) > 0  
              WHERE ${whereCondition} AND p.meterial = 0 AND ((p.inhouse != 1) OR (p.inhouse = 1 AND p.isTrafficCatalog = 1))
              GROUP BY p.id, u.first_name, u.last_name, tc.name, pc.name, psc.name, rc.expected_cost, rc.expected_material_cost, rc.expected_service_cost, rc.expected_total_service_cost
              ORDER BY p.id DESC`,
        );

        let data;
        if (query.length > 0) {
            data = query[0].map(item => {
                if (!item.price) {  // Replacing 'empty' with correct JavaScript check
                    item.price = 0;
                }
                return item;
            });
            return data;
        } else {
            return [];
        }
    } catch (error) {
        console.log(error);
        throw new Error("Server error");
    }
};

const getProductDetailsFromId = async (productId) => {
    try {
        const query = await sequelize.query(
            `SELECT * FROM products WHERE id = ${productId}`
        );
        return query[0][0];
    }
    catch (error) {
        console.error('getProductDetailsFromId error:', error);
        return false;
    }
}

const updateCustomerOrderProductTransferStatus = async (productId,requestId,transferStatus) => {
    try {
        const rows = await sequelize.query(
            `UPDATE customer_order_products cop
            LEFT JOIN customer_orders co ON cop.order_id = co.id
            LEFT JOIN requests r ON co.request_id = r.id
            SET cop.transfer_status = ${transferStatus}
            WHERE r.is_split_order = 1
            AND cop.product_id =${productId} AND r.id = ${requestId} `
        );
        return rows[0];
    } catch (error) {
        console.error('updateCustomerOrderProductTransferStatus error:', error);
        return [];
    }
}

const updateProductsByTransferedProductIds = async (productIds) => {
    try {
        if (!productIds || productIds.length === 0) {
            return 0;
        }

        const placeholders = productIds.map((_, index) => `$${index + 1}`).join(',');
        const query = `UPDATE products SET status = 1 WHERE transfered_product_id IN (${placeholders})`;

        const [result] = await sequelize.query(query, {
            bind: productIds,
            type: sequelize.QueryTypes.UPDATE
        });

        console.log(`Updated ${result} products with transfered_product_id in [${productIds.join(',')}]`);
        return result;
    } catch (error) {
        console.error('updateProductsByTransferedProductIds error:', error);
        return 0;
    }
}

const getAllCatalogProducts = async (limit) => {
    try {
        const products = await sequelize.query(`
            SELECT p.*,rc.expected_cost AS pf_cost_without_markup,
              SUM(DISTINCT 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
            FROM products p
            LEFT JOIN material_inventory mi ON p.id = mi.material_id
            LEFT JOIN recipes rc ON p.recipe_id = rc.id
            LEFT JOIN receive_orders ro ON mi.receive_order_id = ro.id
            WHERE p.meterial = 0 AND p.syncWithQuickbooks !=1
            GROUP BY p.id
              ORDER BY p.id DESC
              LIMIT ${limit}
          `);
        return products[0];
    } catch (error) {
        console.log('getAllCatalogProducts error',error);
        return[]
    }
}

const getAllCatalogMaterials = async (limit) => {
    try {
        const products = await sequelize.query(`
            SELECT p.*,
              SUM(DISTINCT 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(DISTINCT CASE WHEN (mi.source = 0 OR (mi.source = 1 AND ro.status != 0))
                  THEN mi.qty ELSE 0 END) AS totalStock
            FROM products p
            LEFT JOIN material_inventory mi ON p.id = mi.material_id
            LEFT JOIN receive_orders ro ON mi.receive_order_id = ro.id
            WHERE p.meterial = 1 AND p.syncWithQuickbooks !=1
            GROUP BY p.id
              ORDER BY p.id DESC
              LIMIT ${limit}
          `);
        return products[0];
    } catch (error) {
        console.log('getAllCatalogProducts error',error);
        return[]
    }
}


const getSkillsIdFromService = async (serviceId) => {
    try {
        const query = await sequelize.query(`
            SELECT GROUP_CONCAT(ows.skill_ids ORDER BY ows.skill_ids) AS skill_ids
            FROM onboard_workflow_stages ows
            JOIN labour_cost_services lcs ON lcs.workflow = ows.workflow_id
            WHERE lcs.id = ${serviceId}
            GROUP BY ows.workflow_id
        `);

        // Extract the concatenated skill_ids and split them into an array
        const skillIds = query[0][0]?.skill_ids?.split(',') || [];

        // Returning unique skill_ids after splitting and ensuring no duplicates
        return [...new Set(skillIds)];
    } catch (error) {
        console.log('getSkillsIdFromService error', error);
        return [];
    }
}

const getOnboardWorkflowFieldTemplateIdFromService = async (serviceId) => {
    try {
        const query = await sequelize.query(`
            SELECT GROUP_CONCAT(owf.field_template_id ORDER BY owf.field_template_id) AS field_template_ids
            FROM onboard_workflow_stages ows
            JOIN labour_cost_services lcs ON lcs.workflow = ows.workflow_id
            JOIN onboard_workflow_fields owf ON owf.stage_id = ows.id
            WHERE lcs.id = ${serviceId}
            GROUP BY ows.workflow_id
        `);

        // Extract the concatenated field_template_ids and split them into an array
        const fieldTemplateIds = query[0][0]?.field_template_ids?.split(',') || [];

        // Returning unique field_template_ids after splitting and ensuring no duplicates
        return [...new Set(fieldTemplateIds)];
    } catch (error) {
        console.log('getOnboardWorkflowFieldTemplateIdFromService error', error);
        return [];
    }
}

const updateServiceByQbId = async (quickbookId,data) => {
    try {
        const result = LabourCostServices.update(data, {
            where: {
                quickbookId: quickbookId
            }
        });
        if(result){
            return true
        }
        else {
            return false
        }
    } catch (error) {
        console.log('updateServiceByQbId error', error);
        return false
}
}

const updateProductByQbId = async (quickbookId,data) => {
    try {
        const result = Products.update(data, {
            where: {
                quickbookId: quickbookId
            }
        });
    if(result){
        return true
    }
    else {
        return false
    }
}  
catch (error) {
    console.log('updateProductByQbId error', error);
    return false
}
}






module.exports = {
    Products, getProductById, getAllProducts, addProduct,addClientStockLog,getClientStockLogs, isProductNameExist, isMaterialNameExist, isServiceNameExist,
    doUpload, updateProduct,updateInhouseCatalogStatus, deleteProduct, getTaxCategory, addFinishedProduct, getFinishedProducts, deleteFinishedProduct,
    CreateReceiveOrder, EditReceiveOrder, getAllReceiveOrders, getAllReceiveOrdersLatestFirst, getOrderById, updateReceiveOrder, getFinishedProductById,
    updateFinishedProduct, getOrderProducts, getOrderServices, deleteOrderProducts, cancelReceiveOrder, getAllMaterials,getAllMaterialsMain, getMaterialById, updateMaterialStock, updateDataSet,
    getDataSet,updateServiceByQbId,updateProductByQbId,
    deletePartialsOfFinishedProduct,
    getFinishedProductsByReceiveOrderId,
    getFinishedProductsByUnitAndVendor,
    deleteFinishedProductsByOrderAndMaterial,
    getPartialFinishedProducts,
    getPartialFinishedProductsStockByUnit,
    addDeletedMaterialInventory,
    getTaxCategoryById,
    addTaxCategory,
    updateTaxCategory,
    isProductCategoryExist,
    addProductCategory,
    getProductCategoryById,
    updateProductCategory,
    deleteProductCategory,
    getAllProductCategories,
    isProductSubCategoryExist,
    addProductSubCategory,
    getProductSubCategoryById,
    updateProductSubCategory,
    deleteProductSubCategory,
    getAllProductSubCategories,
    getSubCatFromCategoryId,
    addInventoryAuditLog,
    fetchInventoryAuditLog,
    getFinishedProductsData,
    getAllLocations,
    getAllProductsBasedOnRequest,
    getAllServicesBasedOnRequest,
    getServiceWithWorkflowById,
    getWorkflowById,
    addLabourCostServices,
    updateLabourCostServices,
    getAllLabourCostServices,
    getAllLabourCostServicesForJobCost,
    getLabourCostServicesById,
    getServiceByProduct,
    addLabourCostServiceType,
    updateLabourCostServiceType,
    getAllLabourCostServiceTypes,
    getLabourCostServiceTypesById,
    checkMaterialCategoryExist,
    checkMaterialSubCategoryExist,
    checkCatalogCategoryExist,
    checkRecipeExist,
    checkWorkflowExist,
    getAllMaterialsOutOfStock,
    fetchAllProductsByCategory,
    fetchAllProductsByRecipe,
    fetchRecipesBasedOnService,
    fetchRecipesBasedOnMaterial,
    getMaterialInventoryByMaterialId,
    getLastVendorMaterialUnitCost,
    findProductByName,
    findServiceByName,
    getFinishedProductsByProductId,
    getAllFinishedProductsByProductId,
    getMaterialData,
    getVendorData,
    getFinishedProductsDataByBarcode,
    getAllTaxCategory,
    checkCatalogSubCategoryExist,
    updateMaterialInventory,
    checkMaterialQbCategoryExist,
    checkMaterialQbSubCategoryExist,
    findProductCategoryById,
    findProductSubCategoryById,
    getAllMaterialsForInvAudit,
    searchFinishedProductByBarcode,
    updateMainLabelSettingsDB,
    getMainLabelSettingsDB,
    updateSmallLabelSettingsDB,
    getSmallLabelSettingsDB,
    getMaterialDataFromRequestId,
    getMaterialByProduct,
    getMaterialByProductNew,
    getMaterialsForBulkProductsByIds,
    getServiceByProductNew,
    getServicesForBulkProductsByIds,
    getOrderProductsFromQbBillId,updateOrderStatusFromQbBillId,
    getAllInhouseAndClientOwnedMaterials,
    searchFinishedProductByBarcode,
    getNonStockProducts,
    StockUpdate,
    getProductDataByBarcode,
    getProductByBarcode,
    getAllServices,
    getMaterialProductsBySubCategory,
    catalogByReceipte,
    getProductDetailsFromId,
    updateCustomerOrderProductTransferStatus,
    updateProductsByTransferedProductIds,
    getAllStockCatalogForPO,
    getAllCatalogProducts,
    getAllCatalogMaterials,
    getAllFilteredProducts,
    getAllFilteredMaterialsMain,
    CreateReceiveOrderServices, DeleteReceiveOrderServices, UpdateReceiveOrderServices,
    getSkillsIdFromService,getOnboardWorkflowFieldTemplateIdFromService,getAllMaterialsNew,getAllProductsNew
}
