const { empty } = require("../Helpers/Common");
const moment = require('moment')
const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const Onboard = require('../models/onboard')
const OnboardWorkFlowFields = require('../models/onboardWorkflowFields')
const OnboardWorkFlowDocuments = require('../models/onboardWorkflowDocuments')
const OnboardWorkFlowQuestionnaires = require('../models/onboardWorkflowQuestionnaires');
const { res, err } = require("pino-std-serializers");
//------------------------------------------------------------------
let currentDate = Math.round(moment().valueOf() / 1000);
const OnboardWorkFlowStages = sequelize.define(
  "onboard_workflow_stages",
  {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
    },
    workflow_id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: false,
    },
    name: { type: Sequelize.STRING, allowNull: false, defaultValue: false },
    stage_type: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    flow_type: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    internal_stage: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: false,
    },
    message: { type: Sequelize.TEXT, allowNull: true },
    partner_type: {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: 0
    },
    group_ids: { type: Sequelize.CHAR, allowNull: true, },
    skill_ids: { type: Sequelize.CHAR, allowNull: true, },
    service_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    relationship_managers: {
      type: Sequelize.CHAR,
      allowNull: true,

    },
    bank_form_type: {
      type: Sequelize.CHAR,
      allowNull: true,

    },
    position_order: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 1,
    },
    bank_form_type: {
      type: Sequelize.CHAR,
      allowNull: true,

    },
    added_by: {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: false,
    },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    updated_on: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
  },
  { timestamps: false }
);

const addStage = async (data) => {
  try {
    let results = await OnboardWorkFlowStages.create(data);
    if (results) {
      return results;
    } else {
      return false;
    }
  } catch (error) {
    console.log(error, "Error in adding stage in onbaord ");
  }
};
const deleteWorkFlowStage = async (id) => {

  try {
    const deleted = await OnboardWorkFlowStages.destroy({
      where: {
        id: id
      },
    });

    if (deleted) {
      return deleted;
    }
    else {
      return false;
    }
  }
  catch (error) {
    console.log("Delete error......", error)
  }
}
// const getStagesDataByWorkflowId = async (id) => {
//   try {
//     let result = await sequelize.query(`
//     SELECT
//     ows.id,
//     ows.name,
//     ows.stage_type,
//     ows.flow_type AS type_of_stage,
//     ows.internal_stage,
//     ows.position_order,
//     ows.message AS exit_message,
//     ows.partner_type,
//     ows.group_ids,
//     ows.relationship_managers,
//     ows.bank_form_type
// FROM
//     onboard_workflow_stages ows
// WHERE
//     ows.status = 1
//     AND ows.workflow_id = ${id}
// ORDER BY
//     ows.position_order ASC,
//     ows.id ASC;
//     `);
//     let lastPartnerStage = await getLaststageByid('partner_stages',id);
//     let lastTeamStage = await getLaststageByid('team_stages',id);
//     console.log(lastTeamStage,'---------------------------------------------------------lastTeamStage')
//     if(result){
//       let data =[]
//       if(result.length>0){
//         for(let row of result){
//           row['order'] = row['position_order']
//           delete row.position_order;
//           if(!empty(row["group_ids"])&&row["group_ids"]>0){
//             row["group_ids"] = row["group_ids"].split(',')
//           }else{
//             row["group_ids"]=[]
//           }
//           if(!empty(row['relationship_managers'])){
//             row["relationship_manager_ids"] = row["relationship_manager_ids"].split(',')
//             row["relationship_managers"] = row["relationship_managers"].split(',')
//           }else{
//             row["relationship_manager_ids"]=[]
//             row["relationship_managers"]=[]
//           }
//           if(!empty(row["bank_form_type"])){
//             row['bank_form_type'] = row["bank_form_type"].split(',')
//           }else{
//             row['bank_form_type']=[]
//           }
//           row['field_template_ids']= await OnboardWorkFlowFields.getfieldTemplatesbyStageId(row[0]['id'],1);
//           if(row['stage_type']==1){
//             row['requested_document_ids']= await OnboardWorkFlowDocuments.getDocumenetsByStageId(row[0]['id'],1);
//           }else{
//             row['requested_document_ids']=[];
//           }
//           if(row['stage_type']==6){           
//             row['sign_agreement_ids']=await OnboardWorkFlowDocuments.getDocumenetsByStageId(row[0]['id'],1);
//           }else{
//             row['sign_agreement_ids']=[];
//           }
//           row['questionnaire_ids']= await OnboardWorkFlowQuestionnaires.getQuestionairebyStageId(row[0]['id'],1);
//           row['old_stage']=0;
//           if(!empty(lastPartnerStage)){
//             if(lastPartnerStage['position_order']>=row['order']&&row['internal_stage']==0){
//               row['old_stage']=1;
//             }
//           }
//           console.log('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$',row)
//                if(!empty(lastTeamStage)){
//                          if(lastTeamStage['position_order']>=row['order']&&row['internal_stage']==1){
//                            row['old_stage']=1;
//                          }
//                 }
//               data['stage-'+currentDate+'-'+row['id']]=row;
//               console.log('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$',data)
//         }
//         return data
//       }
//     }
//   } catch (error) {
//     console.log(error, "error in getng stage data");
//   }
// };


// const getLaststageByid = async (table, id) => {
//   try {
//     let result = await sequelize.query(
//     `SELECT
//         stage_id,
//         MAX(position_order) AS position_order
//       FROM
//         ${table}
//       WHERE
//         (stage_submit = 0 OR status = 0)
//         AND workflow_id = :id
//       GROUP BY
//         stage_id;`,
//       { replacements: { id } }
// );
// if(result){
//   const data = result[0]
//   return data
// }else{
//   return false
// }
//   } catch (error) {
//     console.log(error, "error");
//   }
// };
const getStagesDataByWorkflowId = async (workflowId) => {

  try {
    let query = await sequelize.query(` select ows.id,ows.id as oldId, ows.name, ows.stage_type, ows.flow_type as type_of_stage, ows.internal_stage, ows.position_order, ows.message as exit_message, ows.partner_type, ows.group_ids,ows.skill_ids, ows.service_id, ls.name AS service_name, ows.relationship_managers, ows.bank_form_type`
      + ` from onboard_workflow_stages ows`
      + ` LEFT JOIN labour_cost_services ls ON ls.id = ows.service_id`
      + ` WHERE ows.status=1  AND ows.workflow_id =${workflowId}`
      + ` ORDER BY ows.position_order ASC, ows.id ASC`)
    let last_partner_stage = await getLastStageInProcessById("partner_stages", workflowId);
    let last_team_stage = await getLastStageInProcessById("team_stages", workflowId)
    if (!empty(query)) {
      const data = [];

      if (query[0].length > 0) {
        console.log("%%%%%%%%%%%%%%%%%%%%%%", query[0]);
        // query[0].forEach(async (row) => {
        const data = await Promise.all(query[0].map(async (row) => {
          row.order = row.position_order;
          delete row.position_order;

          if (!row.group_ids) {
            row.group_ids = [];
          } else {
            row.group_ids = row.group_ids.split(',');
          }

          if (!row.relationship_managers) {
            row.relationship_managers = [];
            row.relationship_manager_ids = [];
          } else {
            row.relationship_managers = row.relationship_managers.split(',');
            // row.relationship_manager_ids = row.relationship_managers.split(',');
            row.relationship_manager_ids = row.relationship_managers;
          }

          if (!row.bank_form_type) {
            row.bank_form_type = [];
          } else {
            row.bank_form_type = row.bank_form_type.split(',');
          }

          row.field_template_ids = await get_field_templates_by_stage_id(row.id);

          if (row.stage_type === 1) {
            row.requested_document_ids = await get_documents_by_stage_id(row.id);
          } else {
            row.requested_document_ids = [];
          }

          if (row.stage_type === 6) {
            row.sign_agreement_ids = await get_documents_by_stage_id(row.id);
          } else {
            row.sign_agreement_ids = [];
          }

          row.questionnaire_ids = await get_questionnaires_by_stage_id(row.id);
          row.old_stage = 0;

          if (last_partner_stage && last_partner_stage.position_order >= row.order && row.internal_stage === 0) {
            row.old_stage = 1;
          }

          if (last_team_stage && last_team_stage.position_order >= row.order && row.internal_stage === 1) {
            row.old_stage = 1;
          }
          console.log("$$$$$$$$$$$$$$$", row);
          // data[`stage-${Date.now()}-${row.id}`] = row;

          return row;
        }));

        console.log("Onboard--------------", data);
        return data;
      } else {
        console.log("Onboard--------------", false);
        return false

      }
    } else {
      console.log("Onboard--------------", false);
      return false
    }
  }
  catch (error) {
    console.error("Error in getStagesDataByWorkflowId:", error);
    return false;
  }
}
const getLastStageInProcessById = async (table, workflowId) => {
  let query = await sequelize.query(` select stage_id,max(position_order) as position_order from ${table}`
    + ` where (stage_submit=0 or status=0) and workflow_id=${workflowId}`)
  if (query[0].length >= 0) {
    return query[0][0];

  }
  else {
    return false;
  }
}
const get_field_templates_by_stage_id = async (stageId, status = 1) => {
  let query = await sequelize.query(` select owf.id,owf.field_template_id from onboard_workflow_fields owf`
    + ` left join custom_field_templates cf on owf.field_template_id=cf.id`
    + ` where owf.stage_id=${stageId} and cf.status=1 and owf.status=${status} order by owf.id asc`)
  let data = []
  if (query[0].length > 0) {
    query[0].forEach((row) => {
      data.push(row.field_template_id);
    });
  }
  return data;
}
const get_documents_by_stage_id = async (stageId, status = 1) => {
  let query = await sequelize.query(` select owd.id,owd.document_id from onboard_workflow_documents owd`
    + ` where owd.stage_id=${stageId} and owd.status=${status} order by owd.id ASC`)
  let data = []
  if (query[0].length > 0) {
    query[0].forEach((row) => {
      data.push(row.document_id);
    });
  }
  return data;
}
const get_questionnaires_by_stage_id = async (stageId, status = 1) => {
  let query = await sequelize.query(` select owq.id,owq.qns_template_id from onboard_workflow_questionnaires owq`
    + ` left join questionnaire_templates cf on owq.qns_template_id=cf.id`
    + ` where owq.stage_id=${stageId} and cf.status=1 and owq.status=${status}`
    + ` order by owq.id ASC`)
  let data = []
  console.log("$$$$$$$$$$$$$$$", query[0]);
  if (query[0].length > 0) {
    data = query[0].map((v) => v.qns_template_id)
    return data
  }
  return data;
}
const getnextStage = async (partnerid, workflowid, stageid = 0, internalstage = 0,rfqstage=0,requestId=0) => {
  try {
    let team_id = partnerid
    let whereCondition = ""
    let select = ""
    if (internalstage != 0) {
      whereCondition = " and ows.internal_stage=1"
      select = "ows.*,ps.id as partner_stage_id,coalesce(ps.stage_submit,1) as stage_submit FROM onboard_workflow_stages ows"
        + `  LEFT JOIN team_stages ps ON ps.stage_id = ows.id AND ps.team_id = ${partnerid}`
      team_id = partnerid
      partnerid = 0
    }
    else {
      if(rfqstage==1){
        whereCondition = " and ows.internal_stage=0"
        select = "ows.*,ps.id as partner_stage_id,coalesce(ps.stage_submit,1) as stage_submit FROM onboard_workflow_stages ows"
          + `  LEFT JOIN rfq_stages  ps ON ps.stage_id=ows.id and ps.request_id=${requestId}`
      }
      else{
        whereCondition = " and ows.internal_stage=0"
        select = "ows.*,ps.id as partner_stage_id,coalesce(ps.stage_submit,1) as stage_submit FROM onboard_workflow_stages ows"
          + `  LEFT JOIN partner_stages  ps ON ps.stage_id=ows.id and ps.partner_id=${partnerid}`
      }
      
    }
    if (stageid != 0) {
      whereCondition += ` and ows.id=${stageid}`
    }
    else if (internalstage == 0) {
      
      console.log("$##########Rfqstage",rfqstage===1);
      if(rfqstage===1){
        whereCondition += ` and ows.position_order>coalesce((select position_order from rfq_stages where status=1 and stage_submit=1 and request_id=${requestId} and workflow_id=${workflowid} order by position_order desc limit 1),0)`
      }
      else{
        whereCondition += ` and ows.position_order>coalesce((select position_order from partner_stages where status=1 and stage_submit=1 and partner_id=${partnerid} and workflow_id=${workflowid} order by position_order desc limit 1),0)`
      }
    }
    else {
      whereCondition += ` and ows.position_order>coalesce((select position_order from team_stages where status=1 and stage_submit=1 and team_id=${team_id} and workflow_id=${workflowid} order by position_order desc limit 1),0)`
     
      
    }
    let result = await sequelize.query(`SELECT ${select}`
      + ` where ows.workflow_id=${workflowid} and ows.status=1 ${whereCondition}`
      + " ORDER BY `ows`.`position_order` ASC LIMIT 1"

    )
    // Assuming 'result' is an object obtained from your query

    if (result) {
     console.log("#########HIfiiiiiiiiii",result[0]);
      if (result[0].length === 1) {
        console.log("$$$$$$$$$$$$$2222", result[0]);
        let row = result[0][0];
       
        if (row.group_ids) {
          row.group_ids = row.group_ids.split(',');
        } else {
          row.group_ids = [];
        }

        if (row.relationship_managers) {
          row.relationship_managers = row.relationship_managers.split(',');
        } else {
          row.relationship_managers = [];
        }

        if (row.bank_form_type) {
          row.bank_form_type = row.bank_form_type.split(',');
        } else {
          row.bank_form_type = [];
        }

        if (row.stage_type === 1) {

          row.field_template_ids = await OnboardWorkFlowFields.getfieldTemplatesbyStageId(row.id);
          row.requested_document_ids = await OnboardWorkFlowDocuments.getDocumenetsByStageId(row.id)
          console.log("$$$$$$$$$$$nush", partnerid);

          if (row.field_template_ids.length > 0) {
            //this below is not yet started
            row.field_templates = await getTemplateFormDataById(row.field_template_ids, partnerid, team_id,rfqstage);

            console.log("$$$$$$$$$$$$$$$$$$$$$$skj", row.field_templates.values);
          } else {
            row.field_templates = [];
          }

          if (row.requested_document_ids.length > 0) {
            row.requested_documents = await OnboardWorkFlowDocuments.getallRequestDocuments(1, row.requested_document_ids, partnerid, team_id);
          } else {
            row.requested_documents = [];
          }
        } else {
          row.requested_document_ids = [];
          row.field_template_ids = [];
          row.field_templates = [];
          row.requested_documents = [];
        }

        if (row.stage_type === 2) {
          row.bank_info = await getBankinfobypartnerid(partnerid);
        } else {
          row.bank_info = [];
        }

        if (row.stage_type === 3) {
          row.tax_info = await getTaxinfobypartnerid(partnerid);
        } else {
          row.tax_info = [];
        }

        if (row.stage_type === 4) {
          row.portfolios = await getPortfoliobypartnerid(partnerid,rfqstage,requestId);
        } else {
          row.portfolios = [];
        }

        if (row.stage_type === 5) {
         
          row.questionnaire_ids = await OnboardWorkFlowQuestionnaires.getQuestionairebyStageId(row.id);

          if (row.questionnaire_ids.length > 0) {
            /// the below model fction in inscomplete  get_qns_formData_by_id
            row.field_templates = await get_qns_formData_by_id(row.questionnaire_ids, partnerid, team_id,rfqstage,requestId);
          } else {
            row.field_templates = [];
          }
        } else {
          row.questionnaire_ids = [];
          row.questionnaires = [];
        }

        if (row.stage_type === 6) {
          row.sign_agreement_ids = await OnboardWorkFlowDocuments.getDocumenetsByStageId(row.id);
          // console.log("$$$$$$$$$$$$$$$VK27", row.sign_agreement_ids);
          if (row.sign_agreement_ids.length > 0) {
            row.sign_agreements = await OnboardWorkFlowDocuments.getallRequestDocuments(2, row.sign_agreement_ids, partnerid,0,rfqstage);
          } else {
            row.sign_agreements = [];
          }
        } else {
          row.sign_agreement_ids = [];
          row.sign_agreements = [];
        }

        return row;
      } 
      else {
        return false;
      }
    }

  } catch (error) {
    console.log(error)
  }
}


const getBankinfobypartnerid = async (partnerid) => {
  try {
    let result = await sequelize.query(`
    select pbi.* from partner_bank_info as pbi where pbi.partner_id = ${partnerid} and pbi.status = 1
    `)
    if (result[0].length == 1) {
      const data = result[0]
      return data
    } else {
      return []
    }
  } catch (error) {
    console.log(error)
  }
}
const getTaxinfobypartnerid = async (partnerid) => {
  try {
    let result = await sequelize.query(`
    select pti.* from partner_tax_info as pti where pti.partner_id  = ${partnerid} and pti.status = 1
    `)

    if (result[0].length === 1) {
      let data = result[0];
      return data;
    } else {
      return [];
    }

  } catch (error) {
    console.log(error)
  }
}
const getPortfoliobypartnerid = async (partnerid,rfqstage=0,request_id=0) => {
  try {
    let result=""
    if(rfqstage==1){
       result = await sequelize.query(`
    select pp.* from rfq_portfolio as pp where pp.request_id=${request_id} and pp.status = 1`)
    
    }
    else{
      result = await sequelize.query(`  
      select pp.* from partner_portfolio as pp where pp.partner_id=${partnerid} and pp.status = 1`)
    }
     
    if (result[0].length > 0) {
      const data = result[0].map((row) => row)
      return data
    } else {
      return []
    }
  } catch (error) {
    console.log(error)
  }
}
const getTemplateFormDataById = async (templateIds, partnerId = 0, teamId = 0,rfqstage=0,requestId=0) => {
  try {
  
    const result = await sequelize.query(`select cft.*, company_name as authorized_by from custom_field_templates as cft `
      + ` left join users as u on cft.added_by= u.id`
      + ` where cft.id in( ${templateIds.join(',')})`)

   
    if (result.length > 0) {
      const templateData = [];

      for (const data of result[0]) {
        const rows = [];
        const columns = [];
        const fields = [];

        // Fields configuration
        
        const distinctRows = await sequelize.query(`select distinct row_id from custom_fields cf where cf.template_id=${data.id} and cf.status=1`)


        if (distinctRows.length > 0) {
          for (const row of distinctRows[0]) {
            rows.push(row.row_id);
          }
        }

        // const distinctColumns = await db.distinct(['row_id', 'column_id']).from('custom_fields cf').where('cf.template_id', data.id).where('cf.status', 1);
        const distinctColumns = await sequelize.query(`select distinct row_id,column_id from custom_fields cf where cf.template_id=${data.id} and cf.status=1`)
        if (distinctColumns.length > 0) {
          for (const row of distinctColumns[0]) {
            columns[row.row_id] = columns[row.row_id] || [];
            columns[row.row_id].push(row.column_id);
          }
        }

        let select = ""
        let join = ""
        
        if (!empty(partnerId)) {
          
          select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_custom_field_value"
          join = ` left join partner_custom_fields pcf on pcf.field_id=cf.id and pcf.partner_id=${partnerId}`
          
        }
        if (!empty(teamId)) {
          select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_custom_field_value";
          join = ` left join team_custom_fields pcf on pcf.field_id=cf.id and pcf.team_id=${teamId}`

        }
        if(rfqstage===1){
          select = " ,pcf.id as partner_custom_field_id, pcf.value as rfq_custom_field_value,pcf.partner_id"
          join = ` left join rfq_custom_fields pcf on pcf.field_id=cf.id and pcf.request_id=${requestId}`
        }
       
        const result2 = await sequelize.query(` select cf.*, company_name as authorized_by ${select} from custom_fields as cf `
          + ` ${join}`
          + ` left join users as u on cf.added_by= u.id`
          + ` where cf.template_id=${data.id} and cf.status=1`
          + ` order by cf.position_order,cf.id`)


        if (result2[0].length > 0) {
          for (const row of result2[0]) {
            fields[row.column_id] = fields[row.column_id] || [];
            fields[row.column_id].push(row);
          }
        }

        
        const formData = {
          id: data.template_uid,
          template_name: data.name,
          settings: {},
          stages: {
            [data.stage_uid]: {
              id: data.stage_uid,
              settings: {},
              rows: rows,
            },
          },
          rows: {},
          columns: {},
          fields: {},
        };
       
        for (const key in columns) {

          formData.rows[key] = {
            id: key,
            config: {
              fieldset: false,
              legend: '',
              inputGroup: false,
            },
            attrs: {
              className: 'f-row',
            },
          };

          for (const col of columns[key]) {

            formData.rows[key].columns = formData.rows[key].columns || [];
            formData.rows[key].columns.push(col);
          }
        }

        for (const key in fields) {
          formData.columns[key] = {
            id: key,
            config: {
              width: `${100 / fields[key].length}%`,
            },
            className: [],
            fields: [],
          };

          for (const f of fields[key]) {
            formData.columns[key].fields.push(f.field_uid);
          }
        }

        for (const key in fields) {
          for (const f of fields[key]) {


            let formDataField = {
              partner_custom_field_id: f.partner_custom_field_id,
              partner_custom_field_value: f.partner_custom_field_value,
              partnerId:f.partner_id,
              field_id: f.id,
              id: f.field_uid,
              tag: f.html_tag,
              config: { label: f.name !== '' ? f.name : ' ' },
              attrs: {
                required: f.is_required === 1 ? true : false,
                description: f.help_text,
              },
            };
            formDataField.attrs.placeholder = f.placeholder ? f.placeholder : '';
            formDataField.attrs.date_format = f.date_format ? f.date_format : '';
            formDataField.attrs.pattern = f.pattern ? f.pattern : '';

            if (!empty(f.content)) {
              formDataField.content = f.content;
            }
            formDataField.required = f.is_required

            formDataField.attrs.href = f.a_href ? f.a_href : '';

            if (f.a_target !== undefined) {
              formDataField.attrs.target = f.a_target === 1 ? true : false;
            }

            if (f.tag_uid === 'header' && f.default_select) {
              formDataField.attrs.tag?.forEach((headerTag, keyTag) => {
                if (f.default_select === headerTag.value) {
                  formDataField.attrs.tag[keyTag].selected = true;
                }
              });
            }


            formData.fields[f.field_uid] = formDataField;
           
            let customField
            if (teamId==0) {
             
              customField = await getCustomFieldValues(partnerId, f.id,0);
              if(rfqstage==1){
                customField =  await getCustomFieldValues(partnerId, f.id,0,1,requestId);
               }
              
            }
            else {
              customField =  await getCustomFieldValues(teamId, f.id, 1);
                          
            }
            if (f.tag_uid === 'select' || f.tag_uid === 'radio-group' || f.tag_uid === 'checkbox-group' || f.tag_uid == 'autocomplete') {
              formData.fields[f.field_uid].options = [];


              let query = await sequelize.query(` select cfo.*, company_name as authorized_by from custom_field_options as cfo `
                + ` left join users as u on cfo.added_by=u.id`
                + ` where cfo.field_id=${f.id} and cfo.status=1 order by cfo.position_order,cfo.id`)
              if (query[0].length > 0) {
                for (const row of query[0]) {
                  formData.fields[f.field_uid].options.push({
                    label: row.option_name,
                    value: row.id,
                    selected: row.selected === 1 ? true : false,
                  });

                  if (row.selected === 1) {
                    formData.fields[f.field_uid].content = row.option_name;
                  }

                }
              }
             

            }

            if (!empty(customField)) {
              formData.fields[f.field_uid].value = customField
              .map(obj => obj.value)
              .filter(value => value) // Filter out empty values
              .join(', ');
            
            }
           

          }
        }

        // templateData.push(formData);
        templateData.push(formData);
      }
      return templateData;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    return false;
  }
}

const get_qns_formData_by_id = async (templateIds, partnerId = 0, teamId = 0,rfqstage=0,request_id) => {
  try {
    
    const result = await sequelize.query(`select cft.*, company_name as authorized_by from questionnaire_templates as cft `
      + ` left join users as u on cft.added_by= u.id`
      + ` where cft.id in( ${templateIds.join(',')})`)

    // console.log("WERRRRRRRRRRRRRR", result[0]);
    if (result.length > 0) {
      const templateData = [];

      for (const data of result[0]) {
        const rows = [];
        const columns = [];
        const fields = [];

        // Fields configuration
        const fieldsConfigs = {
          'name-input': {
            config: {
              label: 'Name',
              disabledAttrs: ['type', 'pattern'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'name-input',
              icon: 'user'
            },
            attrs: {
              required: false,
              placeholder: '',
              description: '',
              type: 'text',
              pattern: '^[a-zA-Z0-9\s]+$'
            }
          },
          'email': {
            config: {
              label: 'Email',
              disabledAttrs: ['type'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'email',
              icon: 'envelope'
            },
            attrs: {
              required: false,
              placeholder: '',
              description: '',
              type: 'email'
            }
          },
          'text-input': {
            config: {
              label: 'Text',
              disabledAttrs: ['type'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'text-input',
              icon: 'text-input'
            },
            attrs: {
              required: false,
              placeholder: '',
              description: ''
            }
          },
          'number': {
            config: {
              label: 'Number',
              disabledAttrs: ['type'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'number',
              icon: 'hash'
            },
            attrs: {
              required: false,
              placeholder: '',
              description: '',
              type: 'number'
            }
          },
          'date-input': {
            config: {
              label: 'Date',
              disabledAttrs: ['type'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'date-input',
              icon: 'hash'
            },
            attrs: {
              required: false,
              placeholder: '',
              description: '',
              date_format: '',
              type: 'date'
            }
          },
          'upload': {
            config: {
              label: 'File Upload',
              disabledAttrs: ['type'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'upload',
              icon: 'hash'
            },
            attrs: {
              required: false,
              description: '',
              type: 'file'
            }
          },
          'textarea': {
            config: {
              label: 'Textarea',
              disabledAttrs: ['type'],
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'textarea',
              icon: 'textarea'
            },
            attrs: {
              required: false,
              description: '',
              placeholder: ''
            }
          },
          'radio': {
            config: {
              label: 'Radio Group',
              disabledAttrs: ['type']
            },
            meta: {
              group: 'common',
              id: 'radio',
              icon: 'radio-group'
            },
            attrs: {
              required: false,
              description: '',
              type: 'radio'
            }
          },
          'checkbox': {
            config: {
              label: 'Checkbox/Group',
              disabledAttrs: ['type']
            },
            meta: {
              group: 'common',
              id: 'checkbox',
              icon: 'checkbox'
            },
            attrs: {
              required: false,
              description: '',
              type: 'checkbox'
            }
          },
          'select': {
            config: {
              label: 'Select',
              lockedAttrs: ['required']
            },
            meta: {
              group: 'common',
              id: 'select',
              icon: 'select'
            },
            attrs: {
              required: false,
              description: '',
              placeholder: ''
            }
          },
          'divider': {
            config: {
              label: 'Separator',
              hideLabel: true
            },
            meta: {
              group: 'html',
              id: 'divider',
              icon: 'divider'
            }
          },
          'header': {
            config: {
              label: 'Header',
              editable: true,
              hideLabel: true
            },
            meta: {
              group: 'html',
              id: 'header',
              icon: 'header'
            },
            attrs: {
              tag: [
                { value: "h1", label: "H1" },
                { value: "h2", label: "H2" },
                { value: "h3", label: "H3" },
                { value: "h4", label: "H4" }
              ],
              className: ""
            },
            content: "Header"
          },
          'paragraph': {
            config: {
              label: 'Paragraph',
              editable: true,
              hideLabel: true
            },
            meta: {
              group: 'html',
              id: 'paragraph',
              icon: 'paragraph'
            },
            attrs: {
              className: ""
            },
            content: " "
          },
          'a-link': {
            config: {
              label: 'Button Link',
              disabledAttrs: ['type']
            },
            meta: {
              group: 'html',
              id: 'a-link',
              icon: 'button'
            },
            attrs: {
              href: '',
              target: true
            }
          }
        };
        const distinctRows = await sequelize.query(`select distinct row_id from questionnaire_fields cf where cf.template_id=${data.id} and cf.status=1`)


        // const distinctRows = await db.distinct('row_id').from('custom_fields cf').where('cf.template_id', data.id).where('cf.status', 1);

        if (distinctRows.length > 0) {
          for (const row of distinctRows[0]) {
            rows.push(row.row_id);
          }
        }

        // const distinctColumns = await db.distinct(['row_id', 'column_id']).from('custom_fields cf').where('cf.template_id', data.id).where('cf.status', 1);
        const distinctColumns = await sequelize.query(`select distinct row_id,column_id from questionnaire_fields cf where cf.template_id=${data.id} and cf.status=1`)
        if (distinctColumns.length > 0) {
          for (const row of distinctColumns[0]) {
            columns[row.row_id] = columns[row.row_id] || [];
            columns[row.row_id].push(row.column_id);
          }
        }

        
        let select = ""
        let join = ""
        if (!empty(partnerId)) {
          select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_questionnaire_value"
          join = ` left join partner_questionnaires  pcf on pcf.field_id=cf.id and pcf.partner_id=${partnerId}`
          
        }
        if (!empty(teamId)) {
          select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_questionnaire_value";
          join = ` left join team_questionnaires  pcf on pcf.field_id=cf.id and pcf.team_id=${teamId}`

        }
        if(rfqstage==1){
          select = " ,pcf.id as partner_custom_field_id, pcf.value as partner_questionnaire_value,pcf.partner_id"
          join = ` left join rfq_questionnaires  pcf on pcf.field_id=cf.id and pcf.request_id=${request_id}`
        }
        const result2 = await sequelize.query(` select cf.*, company_name as authorized_by ${select} from questionnaire_fields as cf `
          + ` ${join}`
          + ` left join users as u on cf.added_by= u.id`
          + ` where cf.template_id=${data.id} and cf.status=1`
          + ` order by cf.position_order,cf.id`)


        if (result2[0].length > 0) {
          for (const row of result2[0]) {
            fields[row.column_id] = fields[row.column_id] || [];
            fields[row.column_id].push(row);
          }
        }
        // console.log("$$$$$$$$$$$$$$$virat", fields);
        const formData = {
          id: data.template_uid,
          template_name: data.name,
          settings: {},
          stages: {
            [data.stage_uid]: {
              id: data.stage_uid,
              settings: {},
              rows: rows,
            },
          },
          rows: {},
          columns: {},
          fields: {},
        };
        // console.log("$$$$$$$$$$$$fff", columns);
        for (const key in columns) {

          formData.rows[key] = {
            id: key,
            config: {
              fieldset: false,
              legend: '',
              inputGroup: false,
            },
            attrs: {
              className: 'f-row',
            },
          };

          for (const col of columns[key]) {

            formData.rows[key].columns = formData.rows[key].columns || [];
            formData.rows[key].columns.push(col);
          }
        }

        for (const key in fields) {
          formData.columns[key] = {
            id: key,
            config: {
              width: `${100 / fields[key].length}%`,
            },
            className: [],
            fields: [],
          };

          for (const f of fields[key]) {
            formData.columns[key].fields.push(f.field_uid);
          }
        }

        for (const key in fields) {
          for (const f of fields[key]) {


            let formDataField = {
              partner_questionnaire_id: f.partner_questionnaire_id,
              partner_questionnaire_value: f.partner_questionnaire_value,
              partnerId:f.partner_id,
              field_id: f.id,
              id: f.field_uid,
              tag: f.html_tag,
              config: { label: f.name !== '' ? f.name : ' ' },
              attrs: {
                required: f.is_required === 1 ? true : false,
                description: f.help_text,
              },
            };
            formDataField.attrs.placeholder = f.placeholder ? f.placeholder : '';
            formDataField.attrs.date_format = f.date_format ? f.date_format : '';
            formDataField.attrs.pattern = f.pattern ? f.pattern : '';

            if (f.tag_uid === 'paragraph' || f.tag_uid === 'header') {
              formDataField.content = f.content;
            }
            if (!empty(f.content)) {
              formDataField.content = f.content;
            }
            formDataField.required = f.is_required
            formDataField.attrs.href = f.a_href ? f.a_href : '';
            // console.log("Midhnnnn$$$$$$$$$", teamId);
            let customField
            if (teamId==0) {
              // console.log("Nushannus",rfqstage);
           
              if(rfqstage===1){
                customField =  await getQuestionFieldValues(partnerId, f.id,0,1,request_id);
               }
               else{
                customField = await getQuestionFieldValues(partnerId, f.id,);
               }
              
            }
            else {
              // console.log("Midh$$$$$$$$$", teamId);
              customField =  await getQuestionFieldValues(teamId, f.id, 1);
            }

           
            if (f.a_target !== undefined) {
              formDataField.attrs.target = f.a_target === 1 ? true : false;
            }

            if (f.tag_uid === 'header' && f.default_select) {
              formDataField.attrs.tag?.forEach((headerTag, keyTag) => {
                if (f.default_select === headerTag.value) {
                  formDataField.attrs.tag[keyTag].selected = true;
                }
              });
            }


            formData.fields[f.field_uid] = formDataField;

            if (f.tag_uid === 'select' || f.tag_uid === 'radio-group' || f.tag_uid === 'checkbox-group' || f.tag_uid == 'autocomplete') {
              formData.fields[f.field_uid].options = [];


              let query = await sequelize.query(` select cfo.*, company_name as authorized_by from questionnaire_field_options as cfo `
                + ` left join users as u on cfo.added_by=u.id`
                + ` where cfo.field_id=${f.id} and cfo.status=1 order by cfo.position_order,cfo.id`)
              if (query[0].length > 0) {
                for (const row of query[0]) {
                  formData.fields[f.field_uid].options.push({
                    label: row.option_name,
                    value: row.id,
                    selected: row.selected === 1 ? true : false,
                  });

                  if (f.tag_uid === 'select' && row.selected === 1) {
                    formData.fields[f.field_uid].content = row.option_name;
                  }
                }
              }
            }
            if (!empty(customField)) {
              // console.log("Nushannusffff", customField);
              
              formData.fields[f.field_uid].value = customField
              .map(obj => obj.value)
              .filter(value => value) // Filter out empty values
              .join(', ');
            }
          }
        }

        // templateData.push(formData);
        templateData.push(formData);
      }
      return templateData;
    } else {
      return false;
    }
  } catch (error) {
    console.error(error);
    return false;
  }
}

const getCustomFieldValues = async (id, field_id, internal = 0,rfqStage=0,requestId) => {
  let whereCondition = ""
  let from = ""
  if (internal == 0) {
    whereCondition = " pcf.partner_id=" + id
    from = " partner_custom_fields pcf"
    if(rfqStage==1){
      whereCondition = " pcf.request_id=" + requestId
    from = " rfq_custom_fields pcf"
    }
  }
  else {
    whereCondition = " pcf.team_id=" + id
    from = " team_custom_fields pcf"
  }
  let query = await sequelize.query(` select value `
    + ` from ${from}`
    + ` left join custom_fields cf on pcf.field_id=cf.id`
    + ` where pcf.status=1 and cf.status=1 and pcf.field_id=${field_id} and ${whereCondition}`)
  console.log("######$$$$$$$$$$$$NUshhhhh", query[0]);
  if (query[0].length > 0) {

    return query[0];
  }
  else {
    return 0;
  }

}

const getQuestionFieldValues= async (id, field_id, internal = 0,rfqstage=0,requestId=0) => {
  console.log("#$$$NNNNNNNNUssssssss",requestId);
  console.log("#$$$NNNNNNNNUssssssss",rfqstage);
  let whereCondition = ""
  let from = ""
  if (internal == 0) {
    whereCondition = " pcf.partner_id=" + id
    from = " partner_questionnaires pcf"
    if(rfqstage==1){
      whereCondition = " pcf.request_id=" + requestId
    from = " rfq_questionnaires pcf"
    }
    
  }
  else {
    whereCondition = " pcf.team_id=" + id
    from = " team_questionnaires pcf"
  }
  let query = await sequelize.query(` select value `
    + ` from ${from}`
    + ` left join custom_fields cf on pcf.field_id=cf.id`
    + ` where pcf.status=1  and pcf.field_id=${field_id} and ${whereCondition}`)
  console.log("######$$$$$$$$$$$$NUshhhhh", query[0]);
  if (query[0].length > 0) {

    return query[0];
  }
  else {
    return 0;
  }

}
const getRfqData=async(id)=>{
  let query=await sequelize.query(`SELECT 
  ct.name AS field_name,
  JSON_ARRAYAGG(JSON_OBJECT('id', r.id, 'value', r.value,'content',r.field_name,"tag",cf.html_tag)) AS field_values
FROM 
  rfq_custom_fields r
LEFT JOIN 
  custom_fields cf ON cf.id = r.field_id
LEFT JOIN 
  custom_field_templates ct ON ct.id = cf.template_id
WHERE 
  r.request_id =${id}
GROUP BY 
  ct.name`)
  if (query[0].length > 0) {

    return query[0];
  }
  else {
    return [];
  }
}

const deleteWorkFlowStageByWorkflowId = async (id) => {

  try {
    const deleted = await OnboardWorkFlowStages.destroy({
      where: {
        workflow_id: id
      },
    });

    if (deleted) {
      return true;
    }
    else {
      return false;
    }
  }
  catch (error) {
    console.log("Delete error......", error)
  }
}


module.exports = {
  addStage,
  getStagesDataByWorkflowId,
  getnextStage,
  getBankinfobypartnerid,
  getTaxinfobypartnerid,
  getPortfoliobypartnerid,
  deleteWorkFlowStage,
  getTemplateFormDataById,
  get_field_templates_by_stage_id,
  get_documents_by_stage_id,
  get_questionnaires_by_stage_id,
  get_qns_formData_by_id,
  getRfqData,
  deleteWorkFlowStageByWorkflowId
};
