const { err, res } = require("pino-std-serializers");
const { empty } = require("../Helpers/Common");
const sequelize = require("../config/dbconnection");
const Sequelize = require('sequelize')
const OnboardWorkFlowStages = require('../models/onboardWorkFlowStages');
const  addOnboardworkFlowFields  = require("./onboardWorkflowFields");
const OnboardWorkFlowDocuments=require("./onboardWorkflowDocuments");
const OnboardWorkflow = sequelize.define('onboard_workflows', {
  id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  name: { type: Sequelize.STRING, allowNull: false },
  is_default: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
  is_auto_generated: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
  added_by: { type: Sequelize.INTEGER, allowNull: false },
  timestamp: { type: Sequelize.INTEGER, allowNull: false },
  status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },

  updated_on: { type: Sequelize.INTEGER, allowNull: false },
}, { timestamps: false }
)

const addWorkFlow = async (data) => {
  try {
    let results = await OnboardWorkflow.create(data)
    if (results) {
      return results;
    }
    else {
      return false;
    }
  } catch (error) {
    console.log(error, 'error in adding workflow')
  }
}

const getAllOnboards = async () => {
  let result = await sequelize.query(`
  SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE ow.status !=2
  ORDER BY ow.id DESC;
  `);
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return false;
  }
};
const getAllOnboardingWorkflows = async () => {
  let result = await sequelize.query(`
  SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE ow.status =1||ow.status=0
  ORDER BY ow.id DESC;
  `);
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return false;
  }
};
const getAllOnboardsProduction = async () => {
  let result = await sequelize.query(`
  SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE ow.status =4 or status=8
  ORDER BY ow.id DESC;
  `);
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return false;
  }
};
const getAllOnboardsService = async () => {
  let result = await sequelize.query(`
  SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE ow.status =9 or status=10
  ORDER BY ow.id DESC;
  `);
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return false;
  }
};
const getAllClientFacingWorkflow=async(req)=>{
  let all=req?.body?.all||false
  let WHERE=" ow.status =1"
  if(all){
  WHERE=" (ow.status =1 or ow.status=0)"
  }
  console.log("################REQ",all);
  let result=await sequelize.query(`SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE  ${WHERE}
  AND EXISTS (
      SELECT 1
      FROM onboard_workflow_stages ows_check
      WHERE ows_check.workflow_id = ow.id
      AND ows_check.internal_stage = 0
  )
  ORDER BY ow.id DESC`)
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return [];
  }
}
const getAllProductWorkflow=async()=>{
  let result=await sequelize.query(`SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners,
     COALESCE(owt.no_of_teams, 0) AS no_of_teams, GROUP_CONCAT(DISTINCT ows_check.service_id) AS allServiceIds
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
   LEFT JOIN onboard_workflow_stages ows_check ON ows_check.workflow_id = ow.id
  WHERE ow.status =4
  AND EXISTS (
      SELECT 1
      FROM onboard_workflow_stages ows_check
      WHERE ows_check.workflow_id = ow.id
      AND ows_check.internal_stage = 0
  )
  GROUP BY ow.id
  ORDER BY ow.id DESC`)
  console.log("################Hifi");
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return [];
  }
}
const getAllServiceWorkflow=async()=>{
  let result=await sequelize.query(`SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE ow.status =9
  AND EXISTS (
      SELECT 1
      FROM onboard_workflow_stages ows_check
      WHERE ows_check.workflow_id = ow.id
      AND ows_check.internal_stage = 0
  )
  ORDER BY ow.id DESC`)
  console.log("################Hifi");
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return [];
  }
}
const getAllTeamFacingWorkflow=async()=>{
  let result=await sequelize.query(`SELECT ow.*, ows.no_of_stages, COALESCE(owp.no_of_partners, 0) AS no_of_partners, COALESCE(owt.no_of_teams, 0) AS no_of_teams
  FROM onboard_workflows ow
  LEFT JOIN (
      SELECT COUNT(id) AS no_of_stages, workflow_id
      FROM onboard_workflow_stages
      WHERE status = 1 AND stage_type != 7
      GROUP BY workflow_id
  ) ows ON ows.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
      FROM partners p, onboard_workflows w
      WHERE w.status != 2 AND p.status = 1
      GROUP BY w.id
  ) owp ON owp.workflow_id = ow.id
  LEFT JOIN (
      SELECT w.id AS workflow_id, SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
      FROM staff_members t, onboard_workflows w
      WHERE w.status != 2 AND t.status = 1
      GROUP BY w.id
  ) owt ON owt.workflow_id = ow.id
  WHERE ow.status != 2
  AND EXISTS (
      SELECT 1
      FROM onboard_workflow_stages ows_check
      WHERE ows_check.workflow_id = ow.id
      AND ows_check.internal_stage = 1
  )
  ORDER BY ow.id DESC`)
  if (!empty(result)) {
    let data = [];
    if (result[0].length > 0) {
      data = result[0];
    }
    return data;
  } else {
    return [];
  }
}
const getWorkFlowbyId = async (id) => {
  try {
    const result = await sequelize.query(
      `SELECT 
        ow.*,
        company_name AS authorized_by,
        COALESCE(ni.no_of_ni_stages, 0) AS no_of_ni_stages,
        COALESCE(i.no_of_i_stages, 0) AS no_of_i_stages,
        COALESCE(owp.no_of_partners, 0) AS no_of_partners,
        COALESCE(owt.no_of_teams, 0) AS no_of_teams
      FROM 
        onboard_workflows ow
      LEFT JOIN 
        users u ON ow.added_by = u.id
      LEFT JOIN 
        (SELECT COUNT(id) AS no_of_i_stages, workflow_id FROM onboard_workflow_stages WHERE internal_stage = 1 GROUP BY workflow_id) i ON ow.id = i.workflow_id
      LEFT JOIN 
        (SELECT COUNT(id) AS no_of_ni_stages, workflow_id FROM onboard_workflow_stages WHERE internal_stage = 0 GROUP BY workflow_id) ni ON ow.id = ni.workflow_id
      LEFT JOIN 
        (SELECT 
            w.id AS workflow_id,
            SUM(CASE WHEN FIND_IN_SET(w.id, p.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_partners
         FROM 
            partners p, onboard_workflows w
         WHERE 
            w.status != 2 AND p.status = 1
         GROUP BY 
            w.id) owp ON owp.workflow_id = ow.id
      LEFT JOIN 
        (SELECT 
            w.id AS workflow_id,
            SUM(CASE WHEN FIND_IN_SET(w.id, t.workflows) > 0 THEN 1 ELSE 0 END) AS no_of_teams
         FROM 
            staff_members t, onboard_workflows w
         WHERE 
            w.status != 2 AND t.status = 1
         GROUP BY 
            w.id) owt ON owt.workflow_id = ow.id
      WHERE 
        ow.id = :id`,
      {
        replacements: { id: id },
        type: sequelize.QueryTypes.SELECT,
      }
    );
    console.log("$$$$$$$$$$$$$$$$$$$$$$$$midhun",result);
    if (result) {
      return result;
    }
    else {
      return false;
    }

  } catch (error) {
    console.error('Error executing query:', error);
    throw error;
  }
};
const getAllWorkflowStageInProgress = async (id) => {
  try {
    let result = await sequelize.query(`
    SELECT COUNT(DISTINCT ps.partner_id) AS no_of_partners
    FROM partner_stages ps
    LEFT JOIN partners p ON p.id = ps.partner_id
    WHERE p.status = 1
      AND ps.workflow_id = ${id};
    `)
    if (result[0].length === 1) {
      return result[0];
    }
    else {
      return false;
    }
  } catch (error) {
    console.log(error, 'error')
  }

}
const getAllWorkFlowTeamStageInProgress = async (id) => {
  try {
    let result = await sequelize.query(`
     SELECT COUNT(DISTINCT ps.team_id) as no_of_teams FROM team_stages as ps
     LEFT JOIN staff_members p on p.id = ps.team_id WHERE p.status =1 AND   ps.workflow_id = ${id}`)
    console.log("Nushhhhhhhhhhhhhhhhh", result);
    if (!empty(result[0])) {
      if (result[0].length === 1) {
        return result[0];
      }
      else {
        return false;
      }
    }
  } catch (error) {
    console.log(error, 'error')
  }
}
const getAllWorkflowsStageStatus = async (id) => {
  try {
    let result = await sequelize.query(`
    SELECT
    ows.id AS stage_id,ows.stage_type,
    ows.name AS stage_name,
    ows.internal_stage,
    COALESCE(ps.no_of_partners, 0) AS no_of_partners,
    COALESCE(ts.no_of_teams, 0) AS no_of_teams
FROM
    onboard_workflow_stages ows
LEFT JOIN (
    SELECT
        COUNT(ps.partner_id) AS no_of_partners,
        ps.stage_id
    FROM
        partner_stages ps
    LEFT JOIN
        partners p ON p.id = ps.partner_id
    WHERE
        ps.status = 0 AND p.status = 1
    GROUP BY
        ps.stage_id
) ps ON ows.id = ps.stage_id
LEFT JOIN (
    SELECT
        COUNT(ts.team_id) AS no_of_teams,
        ts.stage_id
    FROM
        team_stages ts
    LEFT JOIN
        staff_members t ON t.id = ts.team_id
    WHERE
        ts.status = 0 AND t.status = 1
    GROUP BY
        ts.stage_id
) ts ON ows.id = ts.stage_id
WHERE
    ows.status = 1
    AND ows.stage_type != 7
    AND ows.workflow_id = ${id}
ORDER BY
    ows.position_order ASC;
    `)
    if (!empty(result)) {
      let data = [];
      if (result[0].length > 0) {
        data = result[0];
      }
      return data;
    } else {
      return false;
    }
  } catch (error) {
    console.log(error, 'error')
  }
}
const updateWorkFlow = async (id, data) => {
  try {
    const updatedRows = await OnboardWorkflow.update(data, {
      where: {
        id: id
      },
      returning: true,
    });
    if (updatedRows) {
      return true;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    return false;
  }
};

const getstageIdByWorkflowId = async (id, status) => {
  try {
    let result = await sequelize.query(`
    SELECT ows.id FROM onboard_workflow_stages as ows WHERE ows.workflow_id = ${id} AND ows.status = ${status}
    `)
    if (!empty(result)) {
      let data = [];
      if (result[0].length > 0) {
        data = result[0];
      }
      return data;
    } else {
      return false;
    }
  } catch (error) {
    console.log(error)
  }
}

const getOnboardingWorkflows = async (partnerId, ids = false, internalStage = 0,rfqstage=0,requestId=0) => {
 
  let whereCondition = ""
  if (!empty(ids)) {
    whereCondition += ` and ow.id in(${ids}) order by FIELD(ow.id, ${ids})`
  }
  else {
    whereCondition += " order by ow.id desc"
  }
  let internalStageWhere = ` and internal_stage=${internalStage}`
  
  let query = await sequelize.query(` select ow.*,coalesce(ows.no_of_stages,0) as no_of_stages,coalesce(ps.no_of_stages,0) as no_of_partner_stages,coalesce(pt.no_of_stages,0) as no_of_team_stages,coalesce(rs.no_of_stages,0) as no_of_rfq_stages `
    + ` from onboard_workflows ow`
    + ` left join (select count(id) as no_of_stages,workflow_id from onboard_workflow_stages where status=1 ${internalStageWhere} group by workflow_id) ows on ows.workflow_id=ow.id `
    + ` left join (select count(id) as no_of_stages,workflow_id from partner_stages where status=1 and stage_submit=1 and partner_id=${partnerId} group by workflow_id) ps on ps.workflow_id=ow.id `
    + ` left join (select count(id) as no_of_stages,workflow_id from team_stages where status=1 and stage_submit=1 and team_id=${partnerId} group by workflow_id) pt on pt.workflow_id=ow.id `
    + ` left join (select count(id) as no_of_stages,workflow_id from rfq_stages where status=1 and stage_submit=1 and request_id=${requestId} group by workflow_id) rs on rs.workflow_id=ow.id `
    + ` where ow.status=1 ${whereCondition} `
  )
  let data = []
  if (query[0].length > 0) {

    query[0].forEach(row => {
      console.log("####internal",internalStage);
      console.log("####internal",row.no_of_stages !== row.no_of_rfq_stages && internalStage === 0);
      if(rfqstage===1){
        if ((row.no_of_stages !== row.no_of_rfq_stages && internalStage === 0)) {
          data.push(row);
        }
      }
      else{
        if ((row.no_of_stages !== row.no_of_partner_stages && internalStage === 0) ||
       
        (row.no_of_stages !== row.no_of_team_stages && internalStage === 1)) {
        data.push(row);
      }
      }
      
    });
  }
console.log("HIFFFFFFFFF",data);
  return data;
}

const ispartnerStages = async (partnerId) => {
  try {
    console.log("DFDSSSSSSSSSS");
    let result = await sequelize.query(`
    select ps.id from partner_stages as ps where ps.partner_id = ${partnerId}`)
    if (result && result[0].length > 0) {
      return true
    } else {
      return false
    }
  } catch (error) {
    console.log("###############FGGGGG" + error)
  }
}
const isRfqStages = async (requestId) => {
  try {
    console.log("DFDSSSSSSSSSS");
    let result = await sequelize.query(`
    select ps.id from rfq_stages as ps where ps.request_id = ${requestId}`)
    if (result && result[0].length > 0) {
      return true
    } else {
      return false
    }
  } catch (error) {
    console.log("###############FGGGGG" + error)
  }
}
const ispartnerstageExistthenid = async (partnerid, stageid) => {
  try {
    let result = await sequelize.query(`
     select ps.id from partner_stages as ps where ps.partner_id = ${partnerid} and ps.stage_id = ${stageid}
    `)
    if (result) {
      if (result.length == 1) {
        return result['id']
      } else {
        return false
      }
    }
  } catch (error) {
    console.log(error)
  }
}
const isRfqStageExistthenid = async (partnerid, stageid) => {
  try {
    let result = await sequelize.query(`
     select ps.id from rfq_stages as ps where ps.partner_id = ${partnerid} and ps.stage_id = ${stageid}
    `)
    if (result) {
      if (result.length == 1) {
        return result['id']
      } else {
        return false
      }
    }
  } catch (error) {
    console.log(error)
  }
}
const getpartnerStageByid = async (partnerStageId) => {
  try {
    let result = await sequelize.query(`
    SELECT ps.*, ows.name, company_name AS authorized_by
    FROM partner_stages ps
    LEFT JOIN users u ON ps.added_by = u.id
    LEFT JOIN onboard_workflow_stages ows ON ps.stage_id = ows.id
    WHERE ps.id = ${partnerStageId}; 
    `)
    if (result[0].length == 1) {
      return result[0]
    } else {
      return false
    }
  } catch (error) {
    console.log(error)
  }
}
const getRfqStageByid = async (partnerStageId) => {
  try {
    let result = await sequelize.query(`
    SELECT ps.*, ows.name, company_name AS authorized_by
    FROM rfq_stages ps
    LEFT JOIN users u ON ps.added_by = u.id
    LEFT JOIN onboard_workflow_stages ows ON ps.stage_id = ows.id
    WHERE ps.id = ${partnerStageId}; 
    `)
    console.log("$$$$$$$$$$nushannus");
    if (result[0].length == 1) {
      return result[0]
    } else {
      return false
    }
  } catch (error) {
    console.log(error)
  }
}
// const updatePartnerStage = async(id,data)=>{
//   try {

//   } catch (error) {
//     console.log(error)
//   }
// }
const updateBatchData = async (table, data) => {
  
  for (const row of data) {
    const duplicateData = [];
    for (const [key, value] of Object.entries(row)) {
      duplicateData.push(`${key}='${value}'`);
    }

    const insertString = `INSERT INTO ${table} SET ${duplicateData.join(', ')}`;
    const updateString = `ON DUPLICATE KEY UPDATE ${duplicateData.join(', ')}`;
    const sql = `${insertString} ${updateString}`;

    try {
      const [result] = await sequelize.query(sql);
      // Handle result if needed
    } catch (error) {
      // Handle error
      console.error(error);
    }
  }

  return true; // You may want to return a more meaningful value
}
const OnboardBatchUpdate=async(TABLE,data)=>{
  try {
    if(!empty(data?.value)){
    let result = await sequelize.query(`Update ${TABLE} set value = "${data.value}" , updated_on = ${data.updated_on} where field_id =${data.field_id}`)
    
    }
  } catch (error) {
    console.log(error,+"RRRRRRRRRRRRRRR")
  }
 }
const deleteBatchUpdate=async(TABLE,field_id)=>{
  let result =await sequelize.query(`
  DELETE FROM ${TABLE} WHERE field_id = ${field_id}`)
  return true
}
const deleteRFQBatchUpdate=async(TABLE,request_id,field_id)=>{
  let result = await sequelize.query(`
  DELETE from ${TABLE} where request_id=${request_id} and field_id = ${field_id}`)
  return true
}

const deletePortFolio=async(TABLE,id)=>{
  let result = await sequelize.query(`delete from ${TABLE} where id=${id}`)
  return true
}
// const updatePartnerPortFolio=async(data,id)=>{
//   try {
//     let result =await sequelize.query(`update `)
//   } catch (error) {
//     console.log(error)
//   }
// }
const getTeamStagebyId = async (partnerStageId) => {
  try {
    let result = await sequelize.query(`
    SELECT ps.*, ows.name, ps.team_id AS partner_id, company_name AS authorized_by
FROM team_stages ps
LEFT JOIN users u ON ps.added_by = u.id
LEFT JOIN onboard_workflow_stages ows ON ps.stage_id = ows.id
WHERE ps.id = your_partner_stage_id;
`)
    if (result.length == 1) {
      return result
    } else {
      return false
    }
  } catch (error) {
    console.log(error)
  }
}
const isTeamStages = async (teamid) => {
  try {
    let result = await sequelize.query(`
     select ps.id from team_stages as ps where ps.team_id = ${teamid} 
    `)
    if (result && result.length > 0) {
      return true
    } else {
      false
    }
  } catch (error) {
    console.log(error)
  }
}
const isTeamStageExistThenId=async(team_id,stageId)=>{
  try {
    let result = await sequelize.query(`select ps.id from team_stages as ps where ps.team_id = ${team_id} and
    ps.stage_id=${stageId}
    `)
    if(result){
      if(result[0].length ==1){
        const data = result[0]['id']
        return data
      }else{
        false
      }
    }
  } catch (error) {
    console.log(error)
  }
}

const ispartnerteamMemberExist = async (partnerId, teamid) => {
  try {
    let result = await sequelize.query(`
    select pt.id from partner_relationship_managers as pt where pt.partner_id = ${partnerId} and pt.team_id = ${teamid}`)
    if (result[0].length == 1) {
      return true
    } else {
      return false
    }
  } catch (error) {
    console.log(error)
  }
}
const getStagePartners = async (stageId) => {
  let result = await sequelize.query(` select ps.*,p.name as company_name,p.city,s.short_name as state,u.active,u.status,u.profile_pic from partner_stages ps`
    + ` left join partners p on p.id=ps.partner_id`
    + ` left join users u on p.user_id=u.id`
    + ` left join states s on p.state_id=s.id`
    + ` where ps.stage_id=${stageId} and ps.status=0 and p.status=1 order by ps.id desc`)
    let data=[]
  if (result[0].length > 0) {
    for (let row of result[0]) {
      console.log("########BBBBBBBBBBBBBBB",row);
      row['progress'] = 0;
      if (row.stage_type === 1) {
        console.log("$$$$$$$$$$$nush");
        let fieldTemplates = await addOnboardworkFlowFields.getfieldTemplatesbyStageId(row.stage_id);
        let requestedDocuments = await OnboardWorkFlowDocuments.getDocumenetsByStageId(row.stage_id, 1)
     
        let customFieldProgress = 100
        let requestedDocumentsProgress = 100
        if (!empty(fieldTemplates)) {
         
          customFieldProgress = await getCustomFieldFilled(row.partner_id)
          console.log("$$$$$$$$$$$nushara");
        } if (!empty(requestedDocuments)) {
          requestedDocumentsProgress = await getRequestDocumentFilled(row['partner_id']);
        } else {
          requestedDocumentsProgress = 100;
        }
        row['progress'] = (customFieldProgress + requestedDocumentsProgress) / 2;
      }
      else if (row['stage_type'] == 2) {
        console.log("$$$$$$$$$$$$$$$$$$$$$$$%%%%%%%%%%%%");
        row['progress'] = await getBankInfoFilled(row['partner_id']);
      }
      else if (row['stage_type'] == 3) {
        row['progress'] = await getTaxInfoFilled(row['partner_id']);
      } else if (row['stage_type'] == 4) {
        row['progress'] = await getPortfolioFieldFilled(row['partner_id']);
      } else if (row['stage_type'] == 5) {
        row['progress'] = await getQuestionnaireFieldFilled(row['partner_id']);
      } else if (row['stage_type'] == 6) {
        row['progress'] = await getSignFilled(row['partner_id']);
      }
      data.push(row);
    }
    return data;
  }
  return false;
}

const getCustomFieldFilled = async (id, internal = 0) => {
  let whereCondition = ""
  let from = ""
  if (internal == 0) {
    whereCondition = " pcf.partner_id=" + id
    from = " partner_custom_fields pcf"
  }
  else {
    whereCondition = " pcf.team_id=" + id
    from = " team_custom_fields pcf"
  }
  let query = await sequelize.query(` select coalesce(sum((case when cf.is_required=1 then 1 else 0 end)),0) as required_field, coalesce(sum(case when (pcf.value!='' && pcf.value IS NOT NULL && cf.is_required=1) then 1 else 0 end),0) as required_field_filled`
    + ` from ${from}`
    + ` left join custom_fields cf on pcf.field_id=cf.id`
    + ` where pcf.status=1 and cf.status=1 and ${whereCondition}`)
  if (query[0].length == 1) {
    let progress = 100;
    let data = query[0][0]
    if (data['required_field'] != 0) {
      progress = data['required_field_filled'] * 100 / data['required_field'];
    }
    return progress;
  }
  else {
    return 0;
  }

}

const getRequestDocumentFilled = async (id, internal = 0) => {
  let whereCondition = ""
  let from = ""
  if (internal == 0) {
    whereCondition = " pd.partner_id=" + id
    from = " partner_documents pd"
  }
  else {
    whereCondition = " pd.team_id=" + id
    from = " team_documents pd"
  }
  let query = await sequelize.query(` select coalesce(count(pd.id),0) as required_field, coalesce(sum(case when (pd.file_name!='' && pd.file_name IS NOT NULL) then 1 else 0 end),0) as required_field_filled`
    + ` from ${from}`
    + ` left join documents d on pd.document_id=d.id`
    + ` where pd.document_type and pd.status=1 and ${whereCondition}`)
  if (query[0].length == 1) {
    let progress = 100;
    let data = query[0][0]
    if (data['required_field'] != 0) {
      progress = data['required_field_filled'] * 100 / data['required_field'];
    }
    return progress;
  }
  else {
    return 0;
  }

}
const getBankInfoFilled = async (partnerId) => {
  let whereCondition = ""

  let query = await sequelize.query(` select * from partner_bank_info pbi`
    + ` where pbi.partner_id= ${partnerId} and pbi.status=1`)
    console.log("$$$$$$$$$$$$$$$$$$$%%%%%%%%%%%%",query[0]);
  if (query[0].length == 1) {
    let data = query[0][0]
    let progress = 0;
    let required_field = 10;
    let required_field_filled = 0;
    if (!empty(data['bank_name'])) {
      required_field_filled++;
    }
    if (!empty(data['name_on_account'])) {
      required_field_filled++;
    }
    if (!empty(data['routing_number'])) {
      required_field_filled++;
    }
    if (!empty(data['account_number'])) {
      required_field_filled++;
    }
    if (!empty(data['account_type'])) {
      required_field_filled++;
    }
    if (!empty(data['name_on_card'])) {
      required_field_filled++;
    }
    if (!empty(data['card_number'])) {
      required_field_filled++;
    }
    if (!empty(data['expiration_date'])) {
      required_field_filled++;
    }
    if (!empty(data['card_cvv'])) {
      required_field_filled++;
    }
    if (!empty(data['card_type'])) {
      required_field_filled++;
    }
    progress = required_field_filled * 100 / required_field;
    return progress;
  } else {
    return 0;
  }
}
const getTaxInfoFilled = async (partnerId) => {
  let query = await sequelize.query(` select * from partner_tax_info pti`
    + ` where pti.partner_id= ${partnerId} and pti.status=1`)
  if (query[0].length == 1) {
    let data = query[0][0]
    let progress = 0;
    let required_field = 8;
    let required_field_filled = 0;
    console.log("$$$$$$$$$$$$$$$$$$",data);
    if (!empty(data['business_type'])) {
      required_field_filled++;
    }
    if (!empty(data['social_security_number'])) {
      required_field_filled++;
    }
    if (!empty(data['first_name'])) {
      required_field_filled++;
    }
    if (!empty(data['address'])) {
      required_field_filled++;
    }
    if (!empty(data['city'])) {
      required_field_filled++;
    }
    if (!empty(data['state_id'])) {
      required_field_filled++;
    }
    if (!empty(data['zipcode'])) {
      required_field_filled++;
    }
    if (!empty(data['is_business_name'])) {
      required_field_filled++;
    }
    progress = required_field_filled * 100 / required_field;
    return progress;
  } else {
    return 0;
  }

}
const getPortfolioFieldFilled = async (partnerId) => {
  let query = await sequelize.query(` select coalesce(count(pcf.id)*3,0) as required_field, coalesce(sum(
  case when (pcf.title!='' && pcf.title IS NOT NULL)&&(pcf.description!='' && pcf.description IS NOT NULL)&&(pcf.file_name!='' && pcf.file_name IS NOT NULL) 
    then 3 
  else 
    case when (((pcf.title!='' && pcf.title IS NOT NULL)&&(pcf.description!='' && pcf.description IS NOT NULL))|| ((pcf.title!='' && pcf.title IS NOT NULL)&&(pcf.file_name!='' && pcf.file_name IS NOT NULL))|| ((pcf.description!='' && pcf.description IS NOT NULL)&&(pcf.file_name!='' && pcf.file_name IS NOT NULL))) 
      then 2 
    else case when ((pcf.title!='' && pcf.title IS NOT NULL)|| (pcf.file_name!='' && pcf.file_name IS NOT NULL)|| (pcf.description!='' && pcf.description IS NOT NULL)) 
      then 1 
    else 0 end end end),0) as required_field_filled from partner_portfolio pcf`
    + ` where pcf.status=1 AND pcf.partner_id=${partnerId}`)
  if (query[0].length >= 0) {
    let data = query[0][0];
    let progress = 0;
    if (data['required_field'] != 0) {
      progress = data['required_field_filled'] * 100 / data['required_field'];
    }
    return progress;
  } else {
    return 0;
  }
}
const getQuestionnaireFieldFilled = async (id, internal = 0) => {
  let whereCondition = ""
  let from = ""
  if (internal != 0) {
    whereCondition = " pcf.team_id=" + id
    from = " team_questionnaires pcf"
  }
  else {
    whereCondition = " pcf.partner_id=" + id
    from = " partner_questionnaires pcf"
  }
  let query = await sequelize.query(` select coalesce(sum((case when cf.is_required=1 then 1 else 0 end)),0) as required_field, coalesce(sum(case when (pcf.value!='' && pcf.value IS NOT NULL && cf.is_required=1) then 1 else 0 end),0) as required_field_filled`
    + ` from ${from}`
    + ` left join questionnaire_fields cf on pcf.field_id=cf.id`
    + ` where ${whereCondition} and pcf.status=1 and cf.status=1 `)
  if (query[0].length >= 0) {
    let data = query[0][0];
    let progress = 0;
    if (data['required_field'] != 0) {
      progress = data['required_field_filled'] * 100 / data['required_field'];
    }
    return progress;
  } else {
    return 0;
  }
}
const getSignFilled = async (id, internal = 0) => {
  let whereCondition = " "
  let from = ""
  if (internal == 0) {
    whereCondition = " pd.partner_id=" + id
    from = "partner_documents pd"
  }
  else {
    whereCondition = " pd.team_id=" + id
    from = "team_documents pd"
  }
  let query = await sequelize.query(` select coalesce(count(pd.id),0) as required_field, coalesce(sum(case when (pd.file_name!='' && pd.file_name IS NOT NULL) then 1 else 0 end),0) as required_field_filled from ${from}`
    + ` left join documents d on pd.document_id=d.id`
    + ` where pd.document_type=2 and  pd.status=1 and ${whereCondition}`)
  if (query[0].length >= 0) {
    let data = query[0][0];
    let progress = 0;
    if (data['required_field'] != 0) {
      progress = data['required_field_filled'] * 100 / data['required_field'];
    }
    return progress;
  } else {
    return 0;
  }

}


const getWorkflowByName = async (name) => {
  try {
    const result = await OnboardWorkflow.findOne({
      where: {
        name: name,
        status: 1,
      },
    });
    return result;
  } catch (error) {
    console.error("Error getting workflow by name:", error);
    return null;
  }
};

const isWorkflowNameExists = async (name, excludeId = null) => {
  try {
    const workflowName = (name || "").trim().toLowerCase();
    if (!workflowName) {
      return null;
    }

    const whereClause = {
      status: {
        [Sequelize.Op.ne]: 2,
      },
      [Sequelize.Op.and]: sequelize.where(
        sequelize.fn("LOWER", sequelize.fn("TRIM", sequelize.col("name"))),
        workflowName
      ),
    };

    if (excludeId) {
      whereClause.id = {
        [Sequelize.Op.ne]: excludeId,
      };
    }

    const result = await OnboardWorkflow.findOne({
      where: whereClause,
      attributes: ["id", "name", "status"],
    });

    return result;
  } catch (error) {
    console.error("Error checking existing workflow name:", error);
    return null;
  }
};

module.exports = {
  addWorkFlow,
  getTeamStagebyId,
  getAllOnboards,
  getWorkFlowbyId,
  getAllWorkflowStageInProgress,
  getAllWorkFlowTeamStageInProgress,
  getAllWorkflowsStageStatus,
  updateWorkFlow,
  getstageIdByWorkflowId,
  getOnboardingWorkflows,
  ispartnerStages,
  ispartnerstageExistthenid,
  getpartnerStageByid,
  // updatePartnerStage,
  isTeamStages,
  isTeamStageExistThenId,
  updateBatchData,
  ispartnerteamMemberExist,
  getStagePartners,
  isRfqStages,
  isRfqStageExistthenid,
  getRfqStageByid,
  OnboardBatchUpdate,
  deleteBatchUpdate,
  deletePortFolio,
  deleteRFQBatchUpdate,
  getAllClientFacingWorkflow,
  getAllTeamFacingWorkflow,
  getAllOnboardsProduction,
  getAllOnboardsService,
  getAllServiceWorkflow,
  getAllOnboardingWorkflows,
  getAllProductWorkflow,
  getWorkflowByName,
  isWorkflowNameExists,
}