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

const Proposals = sequelize.define('proposals', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  name: { type: STRING, allowNull: false },
  description: { type: TEXT, allowNull: false },
  thumbnail:{type:STRING(2000),allowNull:true,defaultValue:null},
  services: { type: INTEGER, allowNull: true },
  workflows: { type: INTEGER, allowNull: true },
  reason:{type:TEXT,allowNull:true,defaultValue:null},
  project_type:{type:INTEGER,allowNull:true,defaultValue:null},
  project_category:{type:TINYINT,allowNull:true,defaultValue:null},
  community:{type:INTEGER,allowNull:true,defaultValue:null},
  submission_deadline: { type: STRING, defaultValue: null, allowNull: true },
  participation_ends: { type: STRING, defaultValue: null, allowNull: true },
  start_date: { type: STRING, defaultValue: null, allowNull: true },
  subject: { type: TEXT, defaultValue: null },
  closed_for_proposal: { type: INTEGER, defaultValue: 0 },
  email_content: { type: TEXT, allowNull: true },
  type: { type: TINYINT, defaultValue: null, },
  welcome_message: { type: TINYINT, defaultValue: null, },
  markup_type: { type: INTEGER, allowNull: false,defaultValue:1 },
  status: { type: TINYINT, defaultValue: 1 },
  added_by: { type: INTEGER, defaultValue: null },
  onboard_complete: { type: INTEGER, defaultValue: 0 },
  timestamp: { type: INTEGER, allowNull: false },
  labour_cost_services	: { type: STRING(200), allowNull: true,defaultValue:null },
  labour_cost	: { type: INTEGER, allowNull: false,defaultValue:0 },
  is_location: { type: INTEGER, allowNull: false,defaultValue:0 },
  notes: { type: TEXT, allowNull: false },
  quotation: { type: TINYINT, defaultValue: 0},
  quotation_status: { type: TINYINT, defaultValue: 2},
  syncWithQuickbooks:{ type: INTEGER,allowNull: false, defaultValue: 0},
  rfpQbId:{ type: INTEGER,allowNull: true},
  card_order: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
  doc_number:{ type: INTEGER,allowNull: true,defaultValue: null},
  reasonWithQb:{ type: STRING(3000), allowNull: true,defaultValue:null }
}, { timestamps: false })

const RequestProposalAttachments = sequelize.define('request_proposal_attachments', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  name: { type: TEXT, allowNull: false },
  proposal_id: { type: INTEGER, allowNull: false },
  extension: { type: STRING, allowNull: false, defaultValue: "pdf" },
  file_size: { type: INTEGER, allowNull: false, defaultValue: 2 },
  file_type: { type: String, allowNull: false, defaultValue: "pdf" },
  // approved: { type: TINYINT, allowNull: false, defaultValue: 0 },
  // added_by: { type: INTEGER, allowNull: true, defaultValue: null },
  // product_id: { type: INTEGER, allowNull: true, defaultValue: null },
  // reason: { type: TEXT, allowNull: true, defaultValue: null },
  status: { type: TINYINT, defaultValue: 1 },
  added_by: { type: INTEGER, defaultValue: null },
  timestamp: { type: INTEGER, allowNull: false }
}, { timestamps: false })



const CustomerOrder = sequelize.define('proposal_order',{
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    customer_id:{type:INTEGER,allowNull:true,defaultValue:null},
    customer_name:{type:STRING,allowNull:true},
    customer_phone:{type:INTEGER,allowNull:true,defaultValue:'9345534'},
    customer_address:{type:TEXT,allowNull:true},
    tax:{type:FLOAT,allowNull:false},
    sub_total:{type:FLOAT,allowNull:true},
    total:{type:FLOAT(14,2),allowNull:true,defaultValue:null},
    budget:{type:FLOAT(12,2),allowNull:true,defaultValue:null},
    request_id:{type:INTEGER,allowNull:false},
    tax_category:{type:INTEGER,allowNull:true, defaultValue:null},
    tax_amount:{type:FLOAT(12,2),allowNull:false, defaultValue:0.00},
    extra_discount:{type:FLOAT,allowNull:true},
    timestamp:{type:INTEGER,allowNull:false,},
    status:{type:TINYINT,allowNull:false},
},{timestamps: false})
const CustomerOrderProduct = sequelize.define('proposal_order_products',{
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    order_id:{type:INTEGER,allowNull:true,defaultValue:null},
    product_name:{type:STRING,allowNull:true,defaultValue:null},
    product_price:{type:FLOAT,allowNull:true,defaultValue:null},
    product_retail_price:{type:FLOAT,allowNull:true,defaultValue:null},
    product_markup:{type:FLOAT,allowNull:true,defaultValue:null},
    product_qty:{type:INTEGER,allowNull:true,defaultValue:1},
    location_quantity:{type:INTEGER,allowNull:true,defaultValue:1},
    product_id:{type:INTEGER,allowNull:true,defaultValue:null},
    workflow:{type:INTEGER,allowNull:true,defaultValue:null},
    product_sub_total:{type:FLOAT,allowNull:true,defaultValue:null},
    material:{type:INTEGER,allowNull:true,defaultValue:0},
    product_tax:{type:FLOAT,allowNull:true,defaultValue:null},
    product_total:{type:FLOAT,allowNull:true,defaultValue:null},
    discount:{type:FLOAT,allowNull:true,defaultValue:null},
    timestamp:{type:INTEGER,allowNull:true,defaultValue:null},
    status:{type:INTEGER,allowNull:true,defaultValue:1},
},{timestamps:false})
const RequestServices = sequelize.define('proposal_services', {
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    request_id: { type: INTEGER, allowNull: false },
    labour_cost_service_id: { type: INTEGER, allowNull: false, },
    recipe_id: { type: INTEGER, allowNull: true, defaultValue: null },
    product_id: { type: INTEGER, allowNull: true, defaultValue: null },
    qty: { type: FLOAT(12, 2), allowNull: false },
    markup: { type: FLOAT(12, 2), allowNull: true, defaultValue: null },
    unit_cost: { type: FLOAT(12, 2), allowNull: false },
    total_cost: { type: FLOAT(12, 2), allowNull: false },
    job_cost:{type: INTEGER, allowNull: true, defaultValue:0},
    taken:{type: TINYINT, allowNull: false, defaultValue:0}
  }, { timestamps: false, tableName: 'proposal_services' })
  const requestPartners = sequelize.define('proposal_partners', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    request_id: { type: Sequelize.INTEGER, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: false },
    last_seen: { type: Sequelize.INTEGER, allowNull: false, },
    added_by: { type: Sequelize.INTEGER, allowNull: false, },
    timestamp: { type: Sequelize.INTEGER, allowNull: false, },
    status: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    accept_status: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 0 }
}, { timestamps: false })
const getAllProposals = async (requestId, userId, userType,page,rowPerpage,search,where='',sort,fromDate,toDate) => {
    try {
      const fromTimestamp = fromDate ? moment(fromDate).startOf('day').unix() : null;
      const toTimestamp = toDate ? moment(toDate).endOf('day').unix() : null;
  
      let dateCondition = '';
      if (fromTimestamp && toTimestamp) {
        dateCondition = `AND r.participation_ends BETWEEN ${fromTimestamp} AND ${toTimestamp}`;
      } else if (fromTimestamp) {
        dateCondition = `AND r.participation_ends >= ${fromTimestamp}`;
      } else if (toTimestamp) {
        dateCondition = `AND r.participation_ends <= ${toTimestamp}`;
      }

      let createdAt = ''
         if(sort==='new'){
            createdAt = `ORDER BY r.id DESC `
         }else if(sort==='old'){
             createdAt = `ORDER BY r.id ASC `
         }else if(sort === 'custom_order'){
          createdAt=`ORDER BY r.card_order ASC`
         } else{
            createdAt=`ORDER BY r.timestamp DESC`
         }

      let offset = null
      if(rowPerpage && page){
       offset = (page - 1) * rowPerpage;
      }
      let totalCount=0
      let partnerWhere = '';
      let query = '';
      let content = ''
      // if(search){
      //     content=`and r.name LIKE '%${search}%'`
      // }
      if (search) {
        const escapedSearch = search.replace(/'/g, "''").trim();
        const normalizedSearch = escapedSearch.toLowerCase().replace(/["'/\-]/g, '').replace(/\s+/g, '');
      
        // Constructing the match condition
        content = `AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(r.name), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%' 
                  OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(r.description), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%')`;

        // content = `AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(r.description), '"', ''), '/', ''), '-', ''), ' ', ''), '''', '') LIKE '%${normalizedSearch}%')`;
      }
      let WhereCondition = ''
      if (requestId) {
        WhereCondition = ` r.id=${requestId}`
      }
      else {
        WhereCondition = ` r.status <>0  and r.status<>8 ${dateCondition}`
        if(!empty(where)){
          WhereCondition=` ${where} ${dateCondition}`
        }
      }
      let limit = ''
      if(rowPerpage){
        limit = `LIMIT ${rowPerpage} OFFSET ${offset}`
      }
  console.log("#############Hifiiiiiiiiiiii## ");
      if (userType !=3) {
        query = await sequelize.query("SELECT `r`.*,c_ord.total as invoice_total,rfq_prop.avalara_code,rfq_prop.avalara_transaction_id, rpsa.invoice_grand_total, u2.profile_pic AS customer_pic,com.name AS community_name, coalesce(rm.cnt, 0) as msg_count, coalesce(rps.cnt, 0) as proposal_count,i.status as installationStatus,c_ord.customer_name,rpsa.awarded,rps.paymentMode FROM `proposals` `r` "
          + " LEFT JOIN (select count(c.id) as cnt,c.request_id from request_messages c "
          + " left join partners p on p.id=c.partner_id where c.timestamp>=COALESCE((select rml.timestamp from request_message_last_seen rml where rml.request_id=c.request_id and rml.user_id=1 and rml.partner_id=c.partner_id),0) and c.user_id!=1 group by c.request_id) rm ON `r`.`id`=`rm`.`request_id` "
          + " LEFT JOIN (SELECT COUNT(id) as cnt,request_id,paymentMode FROM `rfq_proposals` where status<>0 group by request_id) rps ON `rps`.`request_id`=`r`.`id` "
          + " LEFT JOIN (SELECT request_id  as awarded,budget AS invoice_grand_total FROM `rfq_proposals` WHERE status IN (2, 4) group by request_id) rpsa ON `rpsa`.`awarded`=`r`.`id` "
          // + ` LEFT JOIN community com ON com.id = r.community`
         
          + ` left join proposal_orders c_ord on c_ord.request_id = r.id `
          + ` left join rfq_proposals rfq_prop on rfq_prop.request_id = r.id `
          +` LEFT JOIN  community com ON com.id=r.community and FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1`
          + ` left join partners p2 on p2.id = c_ord.customer_id `
          + ` left join users u2 on u2.id = p2.user_id `
          +` LEFT JOIN (SELECT MAX(status) AS status,request_id FROM installations GROUP BY 
           request_id) i ON i.request_id = r.id `
          + ` JOIN users u ON r.added_by=u.id WHERE  ${WhereCondition} ${content}  ${createdAt} ${limit}`)
          totalCount = await sequelize.query(`
            SELECT COUNT(r.id) as totalCount
            FROM proposals r
            LEFT JOIN (
                SELECT MAX(status) AS status, request_id 
                FROM installations 
                GROUP BY request_id
            ) i ON i.request_id = r.id
            LEFT JOIN (
                SELECT request_id AS awarded, budget AS invoice_grand_total 
                FROM rfq_proposals 
                WHERE status = 2 
                GROUP BY request_id
            ) rpsa ON rpsa.awarded = r.id
             LEFT JOIN (
                SELECT request_id, paymentMode
                FROM rfq_proposals
                WHERE status <> 0
                GROUP BY request_id
            ) rps ON rps.request_id = r.id
            WHERE ${WhereCondition} ${content}
        `);       
      }
      if (userType == 3) {
        query = await sequelize.query(` SELECT  rp . id  as  rt_partner_id , rpsa.invoice_grand_total, u2.profile_pic AS customer_pic,com.name AS community_name,  rp . accept_status ,  p . user_id  as  partner_user_id ,  trps . proposal_added_by ,  trps . proposal_status ,  r .*, coalesce(rm.cnt, 0) as msg_count, coalesce(rps.cnt, 0) as proposal_count,concat(u.first_name," ", u.last_name) as send_to,u.id as sender_id,u.profile_pic as pic,c_ord.customer_name,v.id as proposalId,rpsa.statusRfp,rps.paymentMode  FROM  proposals   r  `
          + ` LEFT JOIN (select count(c.id) as cnt,c.request_id from request_messages c `
          + ` left join partners p on p.id=c.partner_id where c.timestamp>=COALESCE((select rml.timestamp from request_message_last_seen rml where rml.request_id=c.request_id and rml.user_id=${userId} and rml.partner_id=c.partner_id),0) and c.user_id!=${userId} and p.user_id=${userId} group by c.request_id) rm ON  r.id = rm.request_id `
          + ` LEFT JOIN (SELECT COUNT(id) as cnt,request_id,paymentMode FROM rfq_proposals`
          + ` where status<>0 and vendor_id=${userId} group by request_id) rps ON rps.request_id=r.id`
          + ` LEFT JOIN (SELECT request_id  as awarded,vendor_id as vendorId,status as statusRfp, budget AS invoice_grand_total FROM rfq_proposals  group by request_id) rpsa ON rpsa.awarded=r.id and rpsa.vendorId=${userId} `
          + ` JOIN users u ON r.added_by=u.id`
          + ` JOIN proposal_partners rp ON rp.request_id=r.id`
          + `  JOIN partners p ON p.id=rp.partner_id `
          + ` left join proposal_orders c_ord on c_ord.request_id = r.id `
          +` left join rfq_proposals v on v.request_id=c_ord.request_id and v.vendor_id=${userId}`
         
          + ` left join partners p2 on p2.id = c_ord.customer_id `
          + ` left join users u2 on u2.id = p2.user_id `
           +` LEFT JOIN  community com ON com.id=r.community and FIND_IN_SET(c_ord.customer_id, com.clients) > 0 AND com.status = 1`
          + ` LEFT JOIN (select request_id as rid, added_by as proposal_added_by,COALESCE(status,'NA') as proposal_status from request_proposals where added_by=${userId}) trps ON trps.rid=r.id `
          +`  LEFT JOIN (SELECT MAX(status) AS status,request_id FROM installations GROUP BY 
           request_id) i ON i.request_id = r.id `
          + ` WHERE p.user_id = ${userId} AND  ${WhereCondition} and r.status!=1 ${content} ${createdAt} ${limit}`)
           totalCount = await sequelize.query(`select count (r.id) as totalCount from proposals r
          JOIN proposal_partners rp ON rp.request_id=r.id
          JOIN partners p ON p.id=rp.partner_id 
            LEFT JOIN (SELECT MAX(status) AS status,request_id FROM installations GROUP BY 
           request_id) i ON i.request_id = r.id 
           LEFT JOIN (
              SELECT request_id AS awarded, MAX(budget) AS invoice_grand_total 
              FROM rfq_proposals 
              WHERE status IN (2, 4) 
              GROUP BY request_id
          ) rpsa ON rpsa.awarded = r.id
          LEFT JOIN (
              SELECT request_id, paymentMode
              FROM rfq_proposals
              WHERE status <> 0
              GROUP BY request_id
          ) rps ON rps.request_id = r.id
           WHERE p.user_id = ${userId} ${content} and ${WhereCondition} and r.status!=1`
        )
      }
     console.log("#################@@@@@@@@@@@@@@@@$$$$$$$$");
      let result = query[0]
      
      if (result.length >= 1) {
        const data = await Promise.all(result.map(async (row) => {
        //  let image = await FetchAllApprovedDesigns(row.id);
        let image =[]
         let partners = await getRequestPartnerByIds(false,row.id);
         if(!empty(image[0])){
          row.thumbnail=image[0].name?image[0].name:""
         }
         else{
         row.thumbnail=""
         }
         if(partners?.length > 0){
          row.partners = partners
         }else{
          row.partners = []
         }
          return row;
        }));
        return {data,totalCount:totalCount[0][0].totalCount};
      }
      else {
        return [];
      }
    } catch (error) {
      console.error(error);
      return null;
    }
  };
const getAllAwardedProposals=async()=>{

  let query = await sequelize.query("SELECT `r`.*,c_ord.total as invoice_total,CONCAT(u.first_name, ' ', u.last_name) as vendor_name ,c_ord.customer_name,rps.id as proposalId,rps.vendor_id as vendor,rps.request_id  FROM `proposals` `r` "
    +"LEFT JOIN rfq_proposals rps on `rps`.`request_id`=`r`.`id` " 
    // + " LEFT JOIN (SELECT COUNT(id) as cnt,request_id FROM `rfq_proposals` where status=2 group by request_id) rps ON `rps`.`request_id`=`r`.`id` "
    + ` left join vendor_proposals c_ord on c_ord.request_id = r.id `
    + ` left join users u on rps.vendor_id = u.id `
    
  //  +` where (rps.status=2 OR rps.status=4) and r.status!=0`)
   +` where r.status!=0`)

  let result=query[0]
     if(query[0].length>0){
      // return query[0]
      const data = await Promise.all(result.map(async (row) => {
       let orderData = await getCustomerOrderRequestId(row.request_id, row.vendor)
        row.orderData=orderData
        row.products=await getVendorOrderProductsbyId(orderData.proposalOrder) 
        row.services=  await getVendorServices(row.proposalId)
        return row;
      }))
      return data
     }
     else{
      return []
     }
}

  const addProposal = async (data) => {

    let res = await Proposals.create(data)
   
    return res.id;
  }
const addRequestProposaAttachments = async (data) => {

  let res = await RequestProposalAttachments.create(data)
  return res.id;
}

// const updateRequestProposalAttachmentsByProposalId = async (proposal_id, updatedData) => {
//   try {
//     const [affectedRows] = await RequestProposalAttachments.update(updatedData, {
//       where: { proposal_id },
//     });

//     if (affectedRows === 0) {
//       return false
//     }

//     return true
//   } catch (error) {
//     console.error("Error updating RequestProposalAttachments:", error.message);
//     throw error;
//   }
// };


const updateRequestProposalAttachmentsByProposalId = async (proposal_id, updatedData) => {
  try {
    console.log('bshdfbgisdbgisfigb1-model')
    // Try updating the record
    const [affectedRows] = await RequestProposalAttachments.update(updatedData, {
      where: { proposal_id },
    });

    // If no rows were affected, create a new record
    if (affectedRows === 0) {
      const newData = {
        ...updatedData,
        proposal_id, // Ensure the proposal_id is included
      };

      const newRecord = await RequestProposalAttachments.create(newData);
      return newRecord; // Return the newly created record
    }

    // Return true if the update was successful
    return true;
  } catch (error) {
    console.error("Error in updateOrCreateRequestProposalAttachments:", error.message);
    throw error;
  }
};
const deleteRequestProposalAttachmentById = async (proposal_id) => {
  try {
    console.log('uploadedfile-name-model',)
    // Delete the record with the given ID
    const deletedRows = await RequestProposalAttachments.destroy({
      where: { proposal_id },
    });

    // Return a success message or throw an error if no rows were deleted
    // if (deletedRows === 0) {
    //   throw new Error(`Attachment with ID ${proposal_id} not found.`);
    // }

    return { success: true, message: `Attachment with ID ${proposal_id} deleted successfully.` };
  } catch (error) {
    console.error("Error in deleteRequestProposalAttachmentById:", error.message);
    throw error;
  }
};


const getRequestProposaAttachmentsbyId = async (proposal_id) => {
  if (!proposal_id) {
    return []; // Explicitly return an empty array if no proposal_id is provided
  }

  const query = `
    SELECT r.* 
    FROM request_proposal_attachments AS r 
    WHERE r.proposal_id = :proposalId
  `;

  try {
    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
      replacements: { proposalId: proposal_id }, // Safely inject the proposal_id
    });
    console.log("######Rewqqqq1", result);
    console.log("######Rewqqqq1", proposal_id);
    return result; // Return all attachments associated with the proposal_id
  } catch (error) {
    console.error('Error fetching attachments:', error);
    throw error; // Propagate the error to be handled by the caller
  }
};

const createCustomerOrder=async(data)=>{
    console.log("Hifiiiiiiiiiiiiiiiiiiii");
    let res = await CustomerOrder.create(data)
    return res.id;
}


const createCustomerOrderProduct=async(data)=>{
    let res = await CustomerOrderProduct.create(data)
    return res.id;
}
const addRequestServices = async (data) => {
    try {
      const result = RequestServices.create(data)
      if (result) {
        return true
      } else {
        return false
      }
    } catch (error) {
      console.log(error, 'error in addRequestServices')
      return false
    }
  }
  const addRequestPartners = async (data) => {
    try {
        let results = await requestPartners.create(data)
        if (results) {
            return true;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'Error in adding partner ')
    }
}
const getCustomerOrderRequestId = async (request_id,userId) => {
  
    let query = `
      SELECT cust_ord.*,r.name as requestName,r.status as requestStatus,vp.tariff,vp.shipping_cost,r.id as requestId,p.email as clientEmail,v.id as proposalId,vp.id as proposalOrder,v.status as propsalStatus
      FROM proposal_orders cust_ord
      left join proposals r on r.id=cust_ord.request_id
      left join partners p on p.id=cust_ord.customer_id
      left join rfq_proposals v on v.request_id=cust_ord.request_id and v.vendor_id=${userId}
      left join vendor_proposals vp on vp.request_id=v.id
        WHERE cust_ord.request_id = ${request_id}`
    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
    console.log("######hifiiiiiiiiii");
    if (result.length === 1) {
      return result[0];
    } else {
      return false;
    }
  };
  const updateCustomerOrder=async(order_id,data)=>{
    let result = await CustomerOrder.update(data,{
     where:{
       id:order_id
     }
    })
    if (result[0] > 0) {
        return result.id;
      } else {
        return false;
      }
}
const getRequestById = async (request_id) => {
    if (!empty(request_id)) {
      let query = `SELECT rt.*, co.total,co.budget FROM proposals rt `
      + `LEFT JOIN proposal_orders co ON co.request_id = rt.id `
      + `WHERE rt.id = ${request_id}`;
  
      const result = await sequelize.query(query, {
        type: sequelize.QueryTypes.SELECT,
      });
  
      if (result.length === 1) {
        const data = result.map((row) => row);
        return data;
      } else {
        return false;
      }
    } else {
      return false;
    }
  };
  const requestAwaitingPartners = async (requestId, acceptStatus) => {
    let query = await sequelize.query(`SELECT p.id,p.name,p.isVendor,p.user_id, accept_status, u.profile_pic, (case when p.onboard_complete=0 then 'N' else 'Y' end) as active, (coalesce(rr.rating, 0)/coalesce(pr.no_of_reviews, 1)) as rating, coalesce(pr.no_of_reviews, 0) as no_of_reviews FROM partners p `
        + ` JOIN users u ON p.user_id=u.id `
        + ` JOIN proposal_partners rp ON p.id=rp.partner_id `
        + ` LEFT JOIN (select count(id) as no_of_reviews,partner_id from partner_reviews where status=1 group by partner_id ) pr ON p.id=pr.partner_id `
        + ` LEFT JOIN (select (sum(quality_rating)+sum(delivery_rating)+sum(value_for_money_rating))/3 as rating,partner_id from partner_reviews where status=1 group by partner_id ) rr ON p.id=rr.partner_id WHERE rp.request_id =${requestId} AND rp.accept_status =${acceptStatus}`)
    let data = [];

    if (query[0].length > 0) {
        data = query[0];
    }
    return data;
}
const getRequestServices = async (id, serviceId=false,productId=false) => {
    try {
      console.log("##############pr data");
      let whereCondition = ''
      if (serviceId) {
        whereCondition += ` AND rs.labour_cost_service_id=${serviceId} `
      }
      if(productId){
        whereCondition += ` AND rs.product_id=${productId} `
      }
      const result = await sequelize.query(`SELECT DISTINCT rs.*,sr.quantity AS prQty,rs.qty AS quantity,ls.name,ls.cost,ls.markup as originalMarkup,ls.taxable FROM proposal_services rs 
        LEFT JOIN labour_cost_services ls ON ls.id = rs.labour_cost_service_id
        left JOIN services_recipe sr ON sr.recipe_id = rs.recipe_id and sr.service_id = rs.labour_cost_service_id 
         WHERE rs.request_id=${id} ${whereCondition}`)
      if (result[0]?.length > 0) {
        return result[0]
      } else {
        return []
      }
    } catch (error) {
      console.log(error, 'error in getRequestServices')
      return false
    }
  }
  const getVendorServices = async (id, serviceId=false,productId=false) => {
    try {
      console.log("##############pr data");
      let whereCondition = ''
      if (serviceId) {
        whereCondition += ` AND rs.labour_cost_service_id=${serviceId} `
      }
      if(productId){
        whereCondition += ` AND rs.product_id=${productId} `
      }
      const result = await sequelize.query(`SELECT DISTINCT rs.*,sr.quantity AS prQty,
        rs.qty AS quantity,ls.name,ls.makeAsTask AS makeAsTask_lcs,ls.syncWithQuickbooks,
        ls.makeInstallCard AS makeInstallCard_lcs,ls.quickbookId,ls.cost,ls.markup as originalMarkup,
        ls.taxable,ls.taxCode,rs.unit_cost as sellingPrice FROM vendor_services rs 
        LEFT JOIN labour_cost_services ls ON ls.id = rs.labour_cost_service_id
        left JOIN services_recipe sr ON sr.recipe_id = rs.recipe_id and sr.service_id = rs.labour_cost_service_id 
         WHERE ls.status <> 2 AND rs.request_id=${id} ${whereCondition}`)
      if (result[0]?.length > 0) {
        return result[0]
      } else {
        return []
      }
    } catch (error) {
      console.log(error, 'error in getRequestServices')
      return false
    }
  }
  const getCustomerOrderProductsbyId = async (order_id) => {
    let query = '';
   
    if (order_id) {
      query = `
        SELECT cust_ord_pro.*,w.name as workflow_name,p.unit,p.unit_qty,cust_ord_pro.location_quantity as locationQuantity FROM proposal_order_products cust_ord_pro
        left join products p on p.id = cust_ord_pro.product_id
        left join onboard_workflows w on w.id = p.workflow
        WHERE cust_ord_pro.order_id = ${order_id}
      `;
  
      const result = await sequelize.query(query, {
        type: sequelize.QueryTypes.SELECT,
      });
      
      if (result.length >= 1) {
        const data = result.map((row) => row);
        const modifiedData = await Promise.all(data.map(async (row) => {
          const { product_qty, ...rest } = row;
          console.log("###########",row.id);
          const queryResult = await sequelize.query(`SELECT * FROM request_locations WHERE order_id = ${row.id}`);
          if(queryResult[0].length > 0){
            row.locations = queryResult[0];
          } else {
            row.locations = [];
          }
          const workflowRes = await sequelize.query(`select * from onboard_workflows where id = ${row.workflow}`)
          if(workflowRes[0].length > 0){
            row.workflow_detail = workflowRes[0][0];
          } else {
            row.workflow_detail = {};
          }
          return { ...rest, quantity: product_qty,location: row.locations,workflow_detail:row.workflow_detail };
        }));
        
        return modifiedData;
      } else {
        return false;
      }
     
    }
  };
  const getVendorOrderProductsbyId = async (order_id) => {
    let query = '';
   
    if (order_id) {
      query = `
        SELECT cust_ord_pro.*,w.name as workflow_name,p.quickbookId,p.unit,p.unit_qty,
        cust_ord_pro.location_quantity as locationQuantity,p.syncWithQuickbooks,
        p.taxCode,
        p.markup,p.markup AS catalog_markup, p.sub_contract_price AS catalog_price,cust_ord_pro.product_price as sellingPrice,pc.markup as RetailMarkup,
        cust_ord_pro.product_price as vendor_price, pc.wholesale_markup as WholesaleMarkup FROM vendor_order_products cust_ord_pro
        left join products p on p.id = cust_ord_pro.product_id
        left join product_category pc on pc.id =p.category_id
        left join onboard_workflows w on w.id = p.workflow
        WHERE cust_ord_pro.order_id = ${order_id} AND p.status <> 2
      `;
  
      const result = await sequelize.query(query, {
        type: sequelize.QueryTypes.SELECT,
      });
      
      if (result.length >= 1) {
        const data = result.map((row) => row);
        const modifiedData = await Promise.all(data.map(async (row) => {
          const { product_qty, ...rest } = row;
          console.log("###########getVendorOrderProductsbyId",row);

          if (row.material == 0) {
            row.product_price = row.catalog_price || 0;
            row.product_retail_price = row.catalog_price || 0;
            row.markup = row.catalog_markup || 0;
          }

          const queryResult = await sequelize.query(`SELECT * FROM request_locations WHERE order_id = ${row.id}`);
          if(queryResult[0].length > 0){
            row.locations = queryResult[0];
          } else {
            row.locations = [];
          }
          const workflowRes = await sequelize.query(`select * from onboard_workflows where id = ${row.workflow}`)
          if(workflowRes[0].length > 0){
            row.workflow_detail = workflowRes[0][0];
          } else {
            row.workflow_detail = {};
          }
          return {
            ...rest,
            quantity: product_qty,
            location: row.locations,
            workflow_detail: row.workflow_detail,
            product_price: row.product_price,
            product_retail_price: row.product_retail_price,
            markup: row.markup,
          };
        }));
        
        return modifiedData;
      } else {
        return [];
      }
     
    }
  };
  const updateRequets = async (request_id, data) => {
    console.log("############RQ",data);
    let result = await Proposals.update(data, {
  
      where: { id: request_id }
    })
  console.log("$$$$$$$$$$Update",result);
    if (result[0] > 0) {
      return true;
    } else {
      return false;
    }
  }
  const deletedCustomerOrderProduct = async (order_id) => {
    try {
      const result = await CustomerOrderProduct.destroy({
        where: {
          order_id: order_id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };
  const deleteProposalPartners = async (order_id) => {
    try {
      const result = await requestPartners.destroy({
        where: {
          request_id: order_id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };
  const getRequestPartnersByIds = async (partner_ids = [], request_id = 0) => {
    let query = '';
    let clients = partner_ids.join(',')
  
    if (!empty(partner_ids)) {
      query = `SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) as name FROM partners t
               LEFT JOIN users u ON t.user_id = u.id 
               WHERE u.status = 1 AND t.id in ( ${clients} )
               ORDER BY u.id DESC`;
  
    } else {
      query = `SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) as name 
               FROM proposal_partners r 
               LEFT JOIN partners t ON r.partner_id = t.id 
               LEFT JOIN users u ON t.user_id = u.id 
               WHERE u.status = 1 AND r.request_id = ${request_id}
               ORDER BY u.id DESC`;
    }
  
    const result = await sequelize.query(query, {
      type: sequelize.QueryTypes.SELECT,
    });
  
    if (result.length >= 1) {
  
      return result;
    } else {
      return [];
    }
  };
const deleteRequestServices= async (order_id) => {
    try {
      const result = await RequestServices.destroy({
        where: {
            request_id: order_id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };
  const removeRequestPartner= async (id,order_id) => {
    try {
      const result = await requestPartners.destroy({
        where: {
            request_id: order_id,
            partner_id:id
        }
      });
      return true;
    } catch (error) {
      console.error('Error deleting customer order product:', error);
      return false;
    }
  };
  const deleteProposalById = async (request_id) => {
    if (!empty(request_id)) {
      let query = `
      UPDATE proposals
      SET status = 0
      WHERE id = ${request_id}`;
      const result = await sequelize.query(query, {
        type: sequelize.QueryTypes.UPDATE,
      });
  
      if (result) {
        return true
      } else {
        false
      }
    }
  }

  const updateCardOrder = async (cards) => {
    if (!cards || !cards.length) {
      return false;
    }
    try {
      const updatePromises = cards.map((card, index) => {
        return Proposals.update(
          { card_order: index },
          { where: { id: card.id } }
        );
      });
  
      await Promise.all(updatePromises);
  
      return true;
    } catch (error) {
      console.log("Error updating card order:", error);
      throw new Error("Error updating card order");
    }
  };


  const updateProposalById = async (id, updateData) => {
    try {
      // Ensure that the id is provided
      if (!id) {
        throw new Error('Proposal ID is required for updating.');
      }
  
      // Perform the update operation
      const [updatedRowsCount] = await Proposals.update(updateData, {
        where: { id: id }
      });
  
      // Check if any rows were updated
      if (updatedRowsCount === 0) {
        throw new Error(`No proposal found with ID: ${id} or no changes made.`);
      }
  
      console.log(`Proposal with ID: ${id} has been updated successfully.`);
      return { success: true, message: `Proposal with ID: ${id} updated successfully.` };
    } catch (error) {
      console.error('Error updating proposal:', error);
      return { success: false, error: error.message };
    }
  };
  
  module.exports={getAllProposals,addRequestProposaAttachments,getRequestProposaAttachmentsbyId,addProposal,createCustomerOrder,createCustomerOrderProduct,addRequestServices,addRequestPartners,
    getCustomerOrderRequestId,updateCustomerOrder,getRequestById,requestAwaitingPartners,getRequestServices,
    getCustomerOrderProductsbyId,updateRequets,deletedCustomerOrderProduct,getRequestPartnersByIds,deleteProposalPartners,
    deleteRequestServices,getVendorOrderProductsbyId,getVendorServices,getAllAwardedProposals,removeRequestPartner,
    deleteProposalById,updateCardOrder,updateProposalById,deleteRequestProposalAttachmentById,updateRequestProposalAttachmentsByProposalId

  }