const { INTEGER, STRING, TEXT, FLOAT, TINYINT, CHAR, Sequelize } = require("sequelize");
const sequelize = require("../config/dbconnection");
const { empty } = require("../Helpers/Common");

const JobCost = sequelize.define('job_costs', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  project_id: { type: INTEGER, defaultValue: null },
  product_id: { type: INTEGER, defaultValue: null },
  for_product: { type: INTEGER, defaultValue: null, allowNull: true },
  for_service: { type: INTEGER, defaultValue: null, allowNull: true },
  quantity: { type: FLOAT(14, 2), defaultValue: 0.00 },
  actual_quantity: { type: FLOAT(14, 2), defaultValue: 0.00 },
  unit_price: { type: FLOAT(14, 2), defaultValue: 0.00 },
  actual_unit_price: { type: FLOAT(14, 2), defaultValue: 0.00 },
  total: { type: FLOAT(14, 2), defaultValue: 0.00 },
  actual_total: { type: FLOAT(14, 2), defaultValue: 0.00 },
  recipe_name: { type: STRING, defaultValue: "" },
  recipe: { type: TINYINT, defaultValue: 1 },
  status: { type: TINYINT, defaultValue: 1 },
  added_by: { type: INTEGER, defaultValue: null },
  timestamp: { type: INTEGER, allowNull: false },
  taken: { type: TINYINT, allowNull: false, defaultValue: 0 },
  task_id: { type: INTEGER, allowNull: true, defaultValue: null },
  rfp: { type: TINYINT, allowNull: false, defaultValue: 0 },
  qb_journal_entry: { type: INTEGER, allowNull: false, defaultValue: 0 },
  customer_order_product_id: { type: INTEGER, allowNull: true, defaultValue: null },
}, { timestamps: false })
const PayOut = sequelize.define('payouts', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  project_id: { type: INTEGER, defaultValue: null },
  task_id: { type: INTEGER, defaultValue: null },
  log_id: { type: STRING, allowNull: true, defaultValue: null },
  timelog_id: { type: INTEGER, allowNull: true, defaultValue: null },
  installation_id: { type: INTEGER, allowNull: true, defaultValue: null },
  paid_at: { type: INTEGER, defaultValue: null },
  end_date: { type: INTEGER, allowNull: true, defaultValue: null },
  is_time_tracking: { type: INTEGER, allowNull: false, defaultValue: 0 },
  amount: { type: FLOAT, defaultValue: null },
  hours: { type: FLOAT, defaultValue: null },
  cover: { type: TEXT, defaultValue: null },
  description: { type: STRING, defaultValue: null },
  team: { type: INTEGER, defaultValue: null },
  status: { type: TINYINT, defaultValue: 1 },
  service_id: { type: INTEGER, defaultValue: null },
  added_by: { type: INTEGER, defaultValue: null },
  timestamp: { type: INTEGER, allowNull: false },
  is_auto_pay: { type: INTEGER, defaultValue: 0 },
}, { timestamps: false })

const StickyNote = sequelize.define('sticky_notes_job_cost', {
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  sticky_note: {
    type: Sequelize.STRING(600),
    allowNull: true,
    charset: 'utf8mb4',
    collate: 'utf8mb4_unicode_ci',
  },
  added_by: { type: Sequelize.INTEGER, allowNull: false },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  status: { type: Sequelize.TINYINT, allowNull: true, defaultValue: 1 },
  updated_by: { type: Sequelize.INTEGER, allowNull: true },
  updated_on: { type: Sequelize.INTEGER, allowNull: true },
  project_id: { type: Sequelize.INTEGER, allowNull: true },
}, {
  timestamps: false,
  tableName: 'sticky_notes_job_cost',
});

const JobCostLogs = sequelize.define('job_cost_logs', {
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  message: {
    type: Sequelize.STRING(1000),
    allowNull: false,
  },
  user_id: { type: Sequelize.INTEGER, allowNull: false },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  status: { type: Sequelize.TINYINT, allowNull: true, defaultValue: 1 },
  project_id: { type: Sequelize.INTEGER, allowNull: true },
}, {
  timestamps: false,
  tableName: 'job_cost_logs',
});

const addJobCost = async (data) => {
  console.log("#####insertedStatus addJobCost", data)
  let res = await JobCost.create(data)
  return res.id;
}
const updateJobCost = async (data, id) => {
  try {
    let res = await JobCost.update(data, {
      where: { id: id }
    })
    console.log("data", id);
    console.log("result-----------", res);
    return res;
  }
  catch (error) {
    console.log("error in update", error);
    return false;
  }
}
const deleteJobCost = async (id, recipe = 0) => {
  try {
    let res = await JobCost.destroy({
      where: { id: id, recipe: recipe }
    })
    console.log("result-----------", res);
    return res;
  }
  catch (error) {
    console.log("error in delete", error);
    return false;
  }
  return true;
}
const addPayOut = async (data) => {
  let res = await PayOut.create(data)
  return res.id;
}

const getPayOutByInstallation = async (installation_id) => {
  try {
    const payout = await PayOut.findOne({
      where: { installation_id: installation_id }
    });
    return payout;
  } catch (error) {
    console.log("Error fetching payout by installation_id:", error);
    return null;
  }
};
const updatePayOutByInstallation = async (installation_id, service_id) => {
  try {

    const payouts = await PayOut.findAll({
      where: { installation_id: installation_id }
    });

    if (payouts.length === 0) {
      console.log("No payouts found for installation_id:", installation_id);
      return null;
    }


    const updatedPayouts = await PayOut.update(
      { service_id: service_id },
      { where: { installation_id: installation_id } }
    );

    console.log(`Updated ${updatedPayouts[0]} payouts for installation_id:`, service_id, installation_id);
    return updatedPayouts;
  } catch (error) {
    console.log("Error updating payouts by installation_id:", error);
    return null;
  }
};

const updateStaffTaskTimelogByInstallation = async (installation_id, service_id) => {
  try {
    // run a raw UPDATE
    const [result] = await sequelize.query(
      `
      UPDATE staff_task_timelogs 
      SET service_id = :service_id
      WHERE installation_id = :installation_id
      `,
      {
        replacements: { service_id, installation_id },
      }
    );

    console.log(`Updated timelogs for installation_id=${installation_id}, set service_id=${service_id}`);
    return result;
  } catch (error) {
    console.error("Error updating staff_task_timelogs by installation_id:", error);
    return null;
  }
};


const fetchAllJobCostByProjectId = async (projectId, userID, recipe = 0, rfp = false, isMaterialOnly = false) => {
  try {
    let whereCondition = '';
    if (rfp) {
      whereCondition = ` AND jc.rfp != 1`
    }
    if (isMaterialOnly) {
      whereCondition = ` AND p.meterial = 1`
    }
    let query = await sequelize.query(`SELECT jc.id,jc.task_id,jc.for_product,jc.for_service,jc.product_id,jc.quantity,jc.actual_quantity,p.id as material_id,
    COALESCE(jc.unit_price, 0) as unit_price, COALESCE(jc.actual_unit_price, 0) as actual_unit_price, 
    COALESCE(jc.unit_price, 0) as unit_cost, COALESCE(mi.unit_cost, 0) as material_unit_cost,
    SUM(mi.qty + jc.quantity) AS available_stock,p.meterial AS isMaterial,
    mi.partial_unit, jc.total,jc.actual_total,jc.recipe_name as recipeName,cop.vendor_price,mi.partial, jc.customer_order_product_id,
    jc.taken,jc.taken AS finalTaken,jc.rfp, p.name as productName, p1.name AS rfpProductName FROM  job_costs jc
    LEFT JOIN material_inventory mi ON mi.id = jc.product_id
    LEFT JOIN  products p ON p.id = mi.material_id 
    LEFT JOIN  customer_order_products cop ON cop.product_id = jc.product_id 
    LEFT JOIN  products p1 ON p1.id = jc.product_id AND jc.rfp = 1
    WHERE  jc.project_id = ${projectId}  AND jc.status = 1 ${whereCondition}
    AND jc.recipe = '${recipe}'
    GROUP BY jc.id`)
    let data = []
    console.log("$$$$##################");
    if (query[0].length > 0) {
      data = query[0]
    }
    return data;
  } catch (error) {
    console.log('fetchAllJobCostByProjectId error : ', error)
  }
}
const newFetchAllJobCostByProjectId = async (projectId, userID, recipe = 0, rfp = false) => {
  try {
    let whereCondition = '';
    if (rfp) {
      whereCondition = ` AND jc.rfp != 1`
    }
    let query = await sequelize.query(`SELECT jc.id,jc.task_id,jc.product_id,jc.quantity,jc.actual_quantity,p.id as material_id, 
    COALESCE(jc.unit_price, 0) as unit_price, COALESCE(jc.actual_unit_price, 0) as actual_unit_price, 
    COALESCE(jc.actual_unit_price, 0) as jobcost_actual_unit_cost, 
    COALESCE(jc.actual_total, 0) as jobcost_actual_total,
    COALESCE(jc.total, 0) as jobcost_total,
    COALESCE(jc.actual_quantity, 0) as jobcost_actual_quantity,
    COALESCE(jc.unit_price, 0) as unit_cost, COALESCE(mi.unit_cost, 0) as material_unit_cost,
    SUM(mi.qty + jc.quantity) AS available_stock,
    mi.partial_unit, jc.total,jc.actual_total,jc.recipe_name as recipeName,cop.vendor_price,mi.partial, jc.customer_order_product_id,
    jc.taken,jc.taken AS finalTaken,jc.rfp, p.name as productName, p1.name AS rfpProductName FROM  job_costs jc
    LEFT JOIN material_inventory mi ON mi.id = jc.product_id
    LEFT JOIN  products p ON p.id = mi.material_id 
    LEFT JOIN  customer_order_products cop ON cop.product_id = jc.product_id 
    LEFT JOIN  products p1 ON p1.id = jc.product_id AND jc.rfp = 1
    WHERE  jc.project_id = ${projectId}  AND jc.status = 1 ${whereCondition}
    GROUP BY jc.id`)
    let data = []
    console.log("$$$$##################");
    if (query[0].length > 0) {
      data = query[0]
    }
    return data;
  } catch (error) {
    console.log('fetchAllJobCostByProjectId error : ', error)
  }
}
const fetchAllProductsStock = async (projectId) => {
  try {
    const query = await sequelize.query(
      `
      SELECT cop.*, p.inhouse,mi.barcode, p.isTrafficCatalog,cop.taken as finalTaken,p.in_stock_no
      FROM customer_order_products cop
      LEFT JOIN customer_orders co ON co.id = cop.order_id
      LEFT JOIN requests r ON r.id = co.request_id
      LEFT JOIN projects pj ON pj.request_id = r.id
      LEFT JOIN material_inventory mi ON mi.id = cop.materialInvId
      LEFT JOIN products p ON p.id = cop.product_id
      WHERE pj.id = :projectId
      AND cop.material = 0 
      AND (
        p.inhouse != 1 OR 
        (p.inhouse = 1 AND p.isTrafficCatalog = 1)
      )
      `,
      {
        replacements: { projectId },
        type: sequelize.QueryTypes.SELECT
      }
    );

    return query;
  } catch (error) {
    console.log('fetchAllProductsStock error:', error);
    return [];
  }
};

const fetchAllJobCostProjectId = async (projectId) => {
  let query = await sequelize.query(`SELECT id,product_id ,quantity,COALESCE(unit_price, 0),total,recipe_name as recipeName,taken FROM job_costs
   WHERE project_id=${projectId} AND status=1 `)
  let data = []
  if (query[0].length > 0) {
    data = query[0]
  }
  return data;
}

const fetchAllJobCostByProjectandMaterialId = async (projectId, material) => {
  try {
    let query = await sequelize.query(`SELECT mi.id AS material_inventory_id,mi.unit_cost AS material_unit_cost, 
      mi.barcode,p.id AS material_id, jc.id, jc.product_id, jc.quantity, jc.actual_quantity	,
      mi.partial, mi.partial_unit,
      COALESCE(jc.unit_price, 0) as unit_price, COALESCE(jc.actual_unit_price, 0) as actual_unit_price,
      COALESCE(jc.unit_price, 0) as unit_cost, jc.total, jc.recipe_name as recipeName, jc.taken,
      jc.rfp, p.name as productName FROM  job_costs jc
      LEFT JOIN  material_inventory mi ON mi.id = jc.product_id 
      LEFT JOIN  products p ON p.id = mi.material_id 
      WHERE  jc.project_id = :projectId AND jc.status = 1 AND p.id = :material`,
      {
        replacements: { projectId, material },
        type: sequelize.QueryTypes.SELECT
      }
    )
    let data = [];
    if (query.length > 0) {
      data = query;
    }
    return data;
  } catch (error) {
    console.log('fetchAllJobCostByProjectandMaterialId error : ', error)
    return []
  }
}

const fetchAllPayOut = async (userType, user_id, all = false, AllPermission) => {
  let data = []
  let whereCondition = ""
  if (all == true) {
    whereCondition = "or p.status=5"
  }
  let query = ""
  if (userType == 1 || AllPermission) {
    query = await sequelize.query(`SELECT  p.*,lcs.name AS service_name,concat(sm.first_name,' ',sm.last_name)  as team_name,ur.name as designation,0 AS fromDesign, sm.hourly_rate, concat(u2.first_name,' ',u2.last_name) as authorized_by, pt.name AS project_name, pts.name AS task_name FROM payouts p
      left join staff_members sm on sm.id=p.team 
      left join request_services rs on rs.id = p.service_id
      left join labour_cost_services lcs on lcs.id = rs.labour_cost_service_id
      left join users u on u.id=sm.user_id
      left join users u2 on u2.id=p.added_by
      join projects pt on pt.id=p.project_id
      left join project_tasks pts on pts.id=p.task_id
      left join user_roles ur on ur.id=u.user_type WHERE sm.status=1 AND p.status=1 ${whereCondition} order by p.id desc`)
  }
  else {
    query = await sequelize.query(`SELECT  p.*,lcs.name AS service_name,concat(sm.first_name,' ',sm.last_name)  as team_name,ur.name as designation,0 AS fromDesign, sm.hourly_rate, concat(u2.first_name,' ',u2.last_name) as authorized_by, pt.name AS project_name, pts.name AS task_name FROM payouts p
      left join staff_members sm on sm.id=p.team 
      left join request_services rs on rs.id = p.service_id
      left join labour_cost_services lcs on lcs.id = rs.labour_cost_service_id
      left join users u on u.id=sm.user_id
      left join users u2 on u2.id=p.added_by
      left join projects pt on pt.id=p.project_id
      left join project_tasks pts on pts.id=p.task_id
      left join user_roles ur on ur.id=u.user_type WHERE sm.status=1 AND (p.status=1 ${whereCondition}) and p.added_by=${user_id} order by p.id desc `)
  }
  if (query[0].length > 0) {
    data = query[0]
  }
  return data;
}

const fetchAllDesignPayOut = async (userType, user_id, all = false, AllPermission) => {
  let data = [];
  let whereCondition = "";

  if (all === true) {
    whereCondition = "OR p.status=5";
  }

  let query = "";

  if (userType == 1 || AllPermission) {
    query = await sequelize.query(`
      SELECT 
        p.*, 
        CONCAT(sm.first_name, ' ', sm.last_name) AS team_name,
        ur.name AS designation, 
        sm.hourly_rate, 
        CONCAT(u2.first_name, ' ', u2.last_name) AS authorized_by,
        do.id AS project_id,
        1 AS fromDesign,
        do.name AS project_name,
        do.total_cost AS project_cost,
        com.name AS community_name,
        par.name AS client_name,
        lcs.name AS task_name
      FROM design_order_payouts p
      LEFT JOIN staff_members sm ON sm.id = p.team 
      LEFT JOIN users u ON u.id = sm.user_id
      LEFT JOIN users u2 ON u2.id = p.added_by
      LEFT JOIN user_roles ur ON ur.id = u.user_type
      LEFT JOIN design_orders do ON do.id = p.design_order_id
      LEFT JOIN community com ON do.community = com.id
      LEFT JOIN partners par ON do.client = par.id
      LEFT JOIN design_order_services dos ON dos.id = p.task_id AND dos.design_order_id = p.design_order_id
      LEFT JOIN labour_cost_services lcs ON lcs.id = dos.labour_cost_service_id
      WHERE sm.status = 1 AND p.status = 1 ${whereCondition}
      ORDER BY p.id DESC
    `);
  } else {
    query = await sequelize.query(`
      SELECT 
        p.*, 
        CONCAT(sm.first_name, ' ', sm.last_name) AS team_name,
        ur.name AS designation, 
        sm.hourly_rate, 
        CONCAT(u2.first_name, ' ', u2.last_name) AS authorized_by,
        do.id AS project_id,
        1 AS fromDesign,
        do.name AS project_name,
        do.total_cost AS project_cost,
        com.name AS community_name,
        par.name AS client_name,
        lcs.name AS task_name
      FROM design_order_payouts p
      LEFT JOIN staff_members sm ON sm.id = p.team 
      LEFT JOIN users u ON u.id = sm.user_id
      LEFT JOIN users u2 ON u2.id = p.added_by
      LEFT JOIN user_roles ur ON ur.id = u.user_type
      LEFT JOIN design_orders do ON do.id = p.design_order_id
      LEFT JOIN community com ON do.community = com.id
      LEFT JOIN partners par ON do.client = par.id
      LEFT JOIN design_order_services dos ON dos.id = p.task_id AND dos.design_order_id = p.design_order_id
      LEFT JOIN labour_cost_services lcs ON lcs.id = dos.labour_cost_service_id
      WHERE sm.status = 1 AND (p.status = 1 ${whereCondition}) AND p.added_by = ${user_id}
      ORDER BY p.id DESC
    `);
  }

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

  return data;
};

const getPayOutById = async (id) => {
  let query = await sequelize.query(`SELECT p.* FROM payouts p
  WHERE id=${id}`)
  let data = []
  if (query[0].length > 0) {
    data = query[0]
  }
  return data;
}
const getPayOutByTaskId = async (jobId, taskId, team_id = false, jobcostTimelog) => {
  try {
    let conditions = [];
    if (taskId) {
      conditions.push(`p.task_id = ${taskId}`);
    } else {
      conditions.push(`p.task_id IS NULL`);
    }

    if (jobId) {
      conditions.push(`p.project_id = ${jobId}`);
    } else {
      conditions.push(`p.project_id IS NULL`);
    }

    if (team_id) {
      conditions.push(`p.team = ${team_id}`);
    } else if (jobcostTimelog) {
      conditions.push(`p.team IS NULL`);
    }

    // Combine all conditions with "AND"
    let whereCondition = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    let query = await sequelize.query(`SELECT p.* FROM payouts p ${whereCondition}`)
    let data = []
    if (query[0].length > 0) {
      data = query[0]
    }
    return data;
  } catch (error) {
    return [];
  }
}

const getPayOutByServiceId = async (jobId, serviceId, team_id = false, jobcostTimelog) => {
  try {
    let conditions = [];

    if (serviceId) {
      conditions.push(`p.service_id = ${serviceId}`);
    } else {
      conditions.push(`p.service_id IS NULL`);
    }

    if (jobId) {
      conditions.push(`p.project_id = ${jobId}`);
    } else {
      conditions.push(`p.project_id IS NULL`);
    }

    if (team_id) {
      conditions.push(`p.team = ${team_id}`);
    } else if (jobcostTimelog) {
      conditions.push(`p.team IS NULL`);
    }

    const whereCondition = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    const query = await sequelize.query(`SELECT p.* FROM payouts p ${whereCondition}`);

    return query[0].length > 0 ? query[0] : [];
  } catch (error) {
    return [];
  }
};

// const getPayOutByInstallationId=async(projectId,instId, team_id=false)=>{
//   try{
//     let whereCondition = '';
//     if(team_id){
//       whereCondition += ` and team = ${team_id} `
//     }
//     // installation_id=${instId}
//     let query= await sequelize.query(`SELECT p.* FROM payouts p WHERE installation_id IS NOT NULL and project_id=${projectId} ${whereCondition}`)
//     let data=[]
//     if(query[0].length>0){
//       data=query[0]
//     }
//     return data;
//   }catch(error){
//     return [];
//   }
// }

const getPayOutByInstallationId = async (projectId, instId, team_id = false) => {
  try {
    let whereCondition = '';
    if (team_id) {
      whereCondition += ` AND team = ${team_id} `;
    }

    // 👇 Build base condition dynamically
    let baseCondition = instId
      ? `installation_id IS NOT NULL`
      : `service_id IS NOT NULL`;

    const query = await sequelize.query(
      `SELECT p.* 
       FROM payouts p 
       WHERE ${baseCondition} AND project_id = ${projectId} ${whereCondition}`
    );

    return query[0]?.length > 0 ? query[0] : [];
  } catch (error) {
    console.error("Error fetching payouts by installation/service:", error);
    return [];
  }
};


const updatePayOut = async (data, id) => {
  let res = await PayOut.update(data, {
    where: { id: id }
  })
  return res;
}

const deletePayOut = async (editId) => { //projectId,taskId,startTime,endTime,teamId)=>{
  try {
    let deleted = await PayOut.destroy({
      where: {
        id: editId
        // project_id: projectId,
        // task_id: taskId,
        // paid_at: startTime,
        // end_date: endTime,
        // team: teamId,
        // is_auto_pay: 0
      }
    });
    if (deleted) {
      return true;
    }
    else {
      return false;
    }
  } catch (error) {
    console.log("Delete error......", error)
    return false;
  }
}

const deletePayOutByProjectId = async (projectId) => {
  try {
    let deleted = await PayOut.destroy({
      where: {
        project_id: projectId,
      }
    });
    if (deleted) {
      return true;
    }
    else {
      return false;
    }
  } catch (error) {
    console.log("Delete error......", error)
    return false;
  }
}

const fetchAllPayOutByProjectId = async (projectId) => {
  let query = await sequelize.query(`SELECT p.*,pt.is_manually_added FROM payouts p
  LEFT JOIN project_tasks pt ON pt.ID = p.task_id
  WHERE p.project_id=${projectId} and p.status=1`)
  let data = []
  if (query[0].length > 0) {
    data = query[0]
  }
  return data;
}
const deleteRfpJobcost = async (id) => {
  let res = await JobCost.destroy({
    where: { project_id: id, rfp: 1 }
  })
  return res;
}
const deleteMaterialJobcost = async (id) => {
  let res = await JobCost.destroy({
    where: { project_id: id, recipe: 0, rfp: 0 }
  })
  return res;
}

const viewAddedMaterialInTask = async (projectId, id) => {
  try {
    let whereCondition = '';
    if (id) {
      whereCondition = ` jc.task_id=${id} AND `
    }
    // let result = await sequelize.query(`SELECT jc.*, p.name as productName, mi.barcode FROM job_costs jc
    //   LEFT JOIN material_inventory mi ON mi.id = jc.product_id 
    //   LEFT JOIN products p ON p.id = mi.material_id
    //   WHERE ${whereCondition} project_id=${projectId} AND jc.taken = 1 `);
    const query = `
      SELECT 
        jc.product_id,
        jc.unit_price,
        SUM(jc.quantity) AS quantity,
        SUM(jc.actual_quantity) AS actual_quantity,
        SUM(jc.total) AS total,
        SUM(jc.actual_total) AS actual_total,
        p.name AS productName, 
        mi.barcode
      FROM job_costs jc
      LEFT JOIN material_inventory mi ON mi.id = jc.product_id
      LEFT JOIN products p ON p.id = mi.material_id
      WHERE ${whereCondition} project_id=${projectId} AND jc.quantity > 0
      GROUP BY jc.product_id, jc.unit_price, p.name, mi.barcode;
    `;

    let result = await sequelize.query(query);
    if (result[0]?.length > 0) {
      return result[0];
    } else {
      return [];
    }
  } catch (error) {
    console.log('Error in viewAddedMaterialInTask : ', error);
    return [];
  }
}

const updateJobCostStatus = async (id) => {
  try {
    let res = await JobCost.update({ status: 0 }, {
      where: { id: id }
    });
    return res;
  } catch (error) {
    console.log("Error in updating status to 0:", error);
    return false;
  }
}

// MODEL
const addStickyNote = async (data) => {
  try {
    const result = await StickyNote.create(data);
    return result.id;  // Return created ID
  } catch (error) {
    console.error('Error adding sticky note:', error);
    throw error;
  }
};

const editStickyNote = async (id, data) => {
  try {
    const result = await StickyNote.update(data, {
      where: { id }
    });
    return result[0] > 0; // true if rows were affected
  } catch (error) {
    console.error('Error editing sticky note:', error);
    throw error;
  }
};

const getStickyNote = async (projectId) => {
  try {
    const result = await sequelize.query(`
      SELECT sn.*, 
             CONCAT(u.first_name, ' ', u.last_name) AS user_name,
             CONCAT(u2.first_name, ' ', u2.last_name) AS updated_by_name,
             sn.updated_on
      FROM sticky_notes_job_cost sn
      LEFT JOIN users u ON sn.added_by = u.id
      LEFT JOIN users u2 ON sn.updated_by = u2.id
      WHERE sn.status = 1 AND sn.project_id = :projectId
      ORDER BY sn.id DESC
      LIMIT 1
    `, {
      type: sequelize.QueryTypes.SELECT,
      replacements: { projectId }
    });

    return result.length > 0 ? result[0] : null;
  } catch (error) {
    console.error('Error fetching sticky note:', error);
    throw error;
  }
};

const addJobCostLog = async (data) => {
  try {
    const res = await JobCostLogs.create(data);
    return res.id;
  } catch (error) {
    console.error('Error adding job cost log:', error);
    throw error;
  }
};

const getJobCostLogs = async (projectId) => {
  try {
    const rows = await sequelize.query(
      `
      SELECT 
        jcl.*,
        CONCAT(u.first_name, ' ', u.last_name) AS user_name
      FROM job_cost_logs jcl
      LEFT JOIN users u ON u.id = jcl.user_id
      WHERE jcl.status = 1 AND jcl.project_id = :projectId
      ORDER BY jcl.id DESC
      `,
      {
        type: sequelize.QueryTypes.SELECT,
        replacements: { projectId }
      }
    );
    return rows;
  } catch (error) {
    console.error('Error fetching job cost logs:', error);
    return [];
  }
};

const getProjectAndRecipeDetails = async (jobCostId) => {
  try {
    const query = `
      SELECT 
        p.name AS project_name,  -- Fetch project name from projects table
        jc.recipe_name,
        mi.material_id,  -- Fetch material ID from material_inventory table
        prod.name AS material_name  -- Fetch material name from products table
      FROM job_costs jc
      LEFT JOIN projects p ON p.id = jc.project_id  -- Join job_costs to projects to get project name
      LEFT JOIN material_inventory mi ON mi.id = jc.product_id  -- Join material_inventory on product_id from job_costs
      LEFT JOIN products prod ON prod.id = mi.material_id  -- Join products to material_inventory on material_id to get material name
      WHERE jc.id = :jobCostId
    `;

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

    if (result.length > 0) {
      return {
        project_name: result[0].project_name,
        recipe_name: result[0].recipe_name,
        material_name: result[0].material_name  // Correct material name retrieval from products table
      };
    } else {
      console.log("No project, recipe, or material found for the given job cost ID.");
      return null;
    }
  } catch (error) {
    console.error("Error fetching project and recipe details:", error);
    throw error;
  }
};

const getFutureInstallationProducts = async (projectId) => {
  try {
    const query = `
    SELECT cop.id,rl.id AS location_id, cop.product_name AS name,1 AS IsProductInstallation,0 AS IsServiceInstallation 
    FROM projects p
    LEFT JOIN customer_orders co ON co.request_id = p.request_id
    LEFT JOIN customer_order_products cop ON cop.order_id = co.id
    LEFT JOIN request_locations rl ON rl.order_id = cop.id
    WHERE p.id = ${projectId} AND cop.material = 0
    `;
    let result = await sequelize.query(query);
    if (result[0]?.length > 0) {
      return result[0];
    } else {
      return [];
    }
  } catch (error) {
    console.error("Error fetching future installation products:", error);
    throw error;
  }
};

const getFutureInstallationServices = async (projectId) => {
  try {
    const query = `
    SELECT rs.id,rsl.id AS location_id, rs.service_name AS name, 0 AS IsProductInstallation, 1 AS IsServiceInstallation
    FROM projects p
    LEFT JOIN request_services rs ON rs.request_id = p.request_id
    LEFT JOIN request_service_locations rsl ON rsl.service_id = rs.id
     WHERE p.id = ${projectId} AND (rs.recipe_id IS NULL AND rs.makeInstallCard =1 AND rs.from_job =0);
    `;
    let result = await sequelize.query(query);
    if (result[0]?.length > 0) {
      return result[0]; 6
    } else {
      return [];
    }
  } catch (error) {
    console.error("Error fetching future installation services:", error);
    throw error;
  }
};

const fetchJobCostAdditionalMaterialByInventoryId = async (inventoryId, projectId) => {
  try {
    const query = `
    SELECT  jc.id,jc.project_id,jc.for_product,jc.for_Service FROM job_costs jc 
    left join material_inventory mi on mi.id = jc.product_id
    left join products p on p.id = mi.material_id
    where jc.product_id = ${inventoryId} AND jc.recipe = 0 AND p.meterial = 1 AND jc.project_id = ${projectId}
    `;
    let result = await sequelize.query(query);
    if (result[0]?.length > 0) {
      return result[0];
    } else {
      return [];
    }
  }
  catch (error) {
    console.error("Error fetching job cost additional material by inventory id:", error);
    throw error;
  }
}

const getJobCostByProjectAndMaterialId = async (projectId, materialId) => {
  try {
    const query = `
      SELECT jc.* FROM job_costs jc
      JOIN material_inventory mi ON jc.product_id = mi.id
      JOIN products p ON mi.material_id = p.id
      WHERE jc.project_id = :projectId AND p.id = :materialId AND jc.recipe = 0 AND jc.status = 1
      LIMIT 1
    `;
    const result = await sequelize.query(query, {
      replacements: { projectId, materialId },
      type: sequelize.QueryTypes.SELECT
    });
    return result.length > 0 ? result[0] : null;
  } catch (error) {
    console.log('getJobCostByProjectAndMaterialId error:', error);
    return null;
  }
};

module.exports = {
  addJobCost, fetchAllJobCostByProjectId, updateJobCost, deleteJobCost,
  fetchAllJobCostByProjectandMaterialId, getPayOutByTaskId,
  addPayOut, fetchAllPayOut, getPayOutById, fetchAllPayOutByProjectId, updatePayOut, fetchAllJobCostProjectId,
  deleteRfpJobcost, deleteMaterialJobcost, viewAddedMaterialInTask, deletePayOut, updatePayOutByInstallation, updateStaffTaskTimelogByInstallation,
  getPayOutByInstallationId, fetchAllDesignPayOut, deletePayOutByProjectId, getPayOutByServiceId, fetchAllProductsStock, getPayOutByInstallation, updateJobCostStatus, addStickyNote, editStickyNote, getStickyNote, addJobCostLog, getJobCostLogs, getProjectAndRecipeDetails,
  newFetchAllJobCostByProjectId, getFutureInstallationProducts, getFutureInstallationServices, fetchJobCostAdditionalMaterialByInventoryId,
  getJobCostByProjectAndMaterialId,
}
