const { err } = require("pino-std-serializers");
const { empty } = require("../Helpers/Common");
const sequelize = require("../config/dbconnection");
const Sequelize = require('sequelize')

 const CustomFieldTemplate = sequelize.define('questionnaire_templates', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name:{type:Sequelize.CHAR,allowNull:false,defaultValue:false},
    template_uid:{type:Sequelize.INTEGER,allowNull:false,defaultValue:false},
    stage_uid:{type:Sequelize.INTEGER,allowNull:false,defaultValue:false},
    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 CustomField=sequelize.define(`questionnaire_fields`,{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    template_id: { type: Sequelize.INTEGER, allowNull: false, defaultValue: false },
    name: { type: Sequelize.STRING, allowNull: false,defaultValue:"input" },
    html_tag: { type: Sequelize.STRING, allowNull: false },
    input_type: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    is_required: { type: Sequelize.TINYINT, allowNull: false, defaultValue:0 },
    placeholder: { type: Sequelize.STRING, allowNull: true, defaultValue: null },
    help_text: { type: Sequelize.STRING, allowNull: true, },
    date_format: { type: Sequelize.STRING, allowNull: true,defaultValue: null },
    pattern: { type: Sequelize.STRING, allowNull: true,defaultValue: null    },
    content: { type: Sequelize.STRING, allowNull: true,    },
   
    a_href: { type: Sequelize.STRING, allowNull: true,    },
    a_target: { type: Sequelize.TINYINT, allowNull: true,defaultValue:0    },
    row_id: { type: Sequelize.STRING, allowNull: true,defaultValue: null    },
    column_id: { type: Sequelize.STRING, allowNull: true,defaultValue: null    },
    field_uid: { type: Sequelize.STRING, allowNull: true,defaultValue: null    },
    tag_uid: { type: Sequelize.STRING, allowNull: true,defaultValue: null    },
    position_order: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    added_by: { type: Sequelize.INTEGER, allowNull: false, defaultValue: false },
    timestamp:{ type: Sequelize.INTEGER, allowNull: false, defaultValue: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },

}, { timestamps: false })
const customFieldOptions=sequelize.define(`questionnaire_field_options`,{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    field_id: { type: Sequelize.INTEGER, allowNull: false, defaultValue: false },
    option_name: { type: Sequelize.STRING, allowNull: false, },
    selected: { type: Sequelize.TINYINT, allowNull: false,defaultValue: 0    },
    position_order: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 0 },
    added_by: { type: Sequelize.INTEGER, allowNull: false, defaultValue: false },
    timestamp:{ type: Sequelize.INTEGER, allowNull: false, defaultValue: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },

}, { timestamps: false })
const getAllQuestionaireTemplates=async()=>{
    try {
        let result = await sequelize.query(`
        SELECT cft.* FROM questionnaire_templates as cft WHERE status = 1 ORDER BY cft.id DESC
        `)
        if(!empty(result)){
            if (result[0].length > 0) {
                data = result[0];
              }
              return data;
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}
const getAllQuestionaireTemplateIds=async(ids)=>{
    try {
        let result = await sequelize.query(`
        select cft.* from questionnaire_templates as cft where cft.status = 1 and cft.id in (${ids}) order by cft.id
        `)
        if (result && result[0].length > 0) {
            return result[0];
          } else {
            return false;
          }
    } catch (error) {
        console.log(error)
    }
}
const getAllCustomFields = async () => {
    try {
        let result = await sequelize.query("SELECT `cft`.*, concat(u.first_name, ' ', u.last_name) as authorized_by, coalesce(p.no_of_partners, 0) as no_of_partners FROM `questionnaire_templates` `cft` "    
        +" LEFT JOIN `users` `u` ON `cft`.`added_by`=`u`.`id` " 
        +"  LEFT JOIN (select count(owf.id) as no_of_partners,owf.qns_template_id from onboard_workflow_stages ows "
        +" left join onboard_workflow_questionnaires owf on owf.stage_id=ows.id where workflow_id in(select workflow_id from (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) w where no_of_partners>0) and stage_type=1 group by owf.qns_template_id) p ON `p`.`qns_template_id`=`cft`.`id`"
        +" WHERE `cft`.`status` != 2 ORDER BY `cft`.`id` DESC"
       
  );
        if (!empty(result)) {
            if (result[0].length > 0) {
                let data = result[0];
                return data;
            } else {
                return false;
            }
        } else {
            return false
        }
    } catch (error) {
        console.log(error, "error in getting all custom fields");
    }
};
const addTemplate = async (data) => {
    try {
        let results = await CustomFieldTemplate.create(data)
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'error in adding templates')
    }
}
const updateCustomFieldTemplate = async (id, data) => {
    try {
        let result = await CustomFieldTemplate.update(data, {
            where: {
                id: id
            }
        })
        if (result) {
            return id
        } else {
            return false
        }
    } catch (error) {
        console.log(error)
    }
}
const updateCustomField = async (id) => {
  try {
    data={status:2}
      let result = await CustomField.update(data, {
          where: {
            template_id: id
          }
      })
      if (result) {
          return true
      } else {
          return false
      }
  } catch (error) {
      console.log(error)
  }
}
const getTemplateByid = async (id) => {
    try {
        const [result] = await sequelize.query(
            `SELECT cft.*, CONCAT(u.first_name, ' ', u.last_name) AS authorized_by
            FROM questionnaire_templates cft
            LEFT JOIN users u ON cft.added_by = u.id
            WHERE cft.id = ${id};
            `
        );

        if (result.length === 1) {
            const data = result[0];
            return data;
        } else {
            return false;
        }
    } catch (error) {
        console.error('Error in getTemplateById:', error);
        return false;
    }
};

const deteleCustomTemplateById = async (templateId) => {
    try {
        let result = await sequelize.query(`
      UPDATE questionnaire_templates
      SET status = 2
      WHERE id = ${templateId} AND status != 2;
      `);
        if (!empty(result)) {
            return true
        } else {
            return false
        }
    } catch (error) {
        console.log(error, 'error in deleting template by id');
        return false;
    }
};
const getAllTemplateIds = async (ids) => {
    try {
        let result = await sequelize.query(`
        SELECT cft.*
        FROM questionnaire_templates as cft
        WHERE cft.id IN (${ids})
          AND cft.status = 1
        ORDER BY cft.id;
      `);

        if (result && result[0].length > 0) {
            return result[0];
        } else {
            return false;
        }
    } catch (error) {
        console.log(error)
    }
}
const addTemplateField = async (data) => {
    try {
        let results = await CustomField.create(data)
        console.log("#################RESULT",results);
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'error in adding templates')
    }
}
const addTemplateFieldOptions=async(data)=>{
    try {
        let results = await customFieldOptions.create(data)
        if (results) {
            return results.id;
        }
        else {
            return false;
        }
    } catch (error) {
        console.log(error, 'error in adding templates')
    }
}

const get_qnsTemplateData_by_id = async(templateId) => {
    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 = ${templateId}`)
  
      if (result.length > 0) {
        const templateData = [];
  
        for (const data of result[0]) {
          const rows = [];
          const columns = [];
          const fields = [];

          const distinctRows = await sequelize.query(`select distinct row_id from questionnaire_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 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 = ""

          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);
            }
          }

          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_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,
                name:f.row_id,
                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 : '';
           
              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)) {             
                
                formData.fields[f.field_uid].value = customField
                .map(obj => obj.value)
                .filter(value => value) 
                .join(', ');
              }
            }
          }
          templateData.push(formData);
        }
        return templateData;
      } else {
        return false;
      }
    } catch (error) {
      console.error(error);
      return false;
    }
  }
  const deleteCustomField=async(id)=>{
    let query=await sequelize.query(`DELETE cf, cfo
    FROM questionnaire_fields cf
    LEFT JOIN questionnaire_field_options cfo ON cf.id = cfo.field_id
    WHERE cf.template_id = ${id}`)
    if(query){
      return true;
    }
    else{
      return false;
    }
  }
 
  const removeCustomField=async(id,rowIds)=>{
    let query=await sequelize.query(`delete from questionnaire_fields where row_id not in(${rowIds}) and template_id=${id}`)
    if(query){
      return true;
    }
    else{
      return false;
    }
  }
  const checkExisting=async(id,rowIds)=>{
    let query=await sequelize.query(`select * from questionnaire_fields where row_id="${rowIds}" and template_id=${id}`)
    if(query.length>0){
      return query[0][0];
    }
    else{
      return false;
    }
  }
  const updateTemplateField = async (id, data) => {
    try {
        let result = await CustomField.update(data, {
            where: {
                id: id
            }
        })
        console.log("########REsuklt",result);
        if (result) {
            return id
        } else {
            return false
        }
    } catch (error) {
        console.log(error)
    }
  }
  const fetchOptions=async(id)=>{
    let query=await sequelize.query(`select * from questionnaire_field_options where field_id=${id} `)
    if(query.length>0){
      return query[0];
    }
    else{
      return false;
    }
  }
  const removeCustomFieldOptions=async(id,rowIds)=>{
    let whereCondition=""
    if(rowIds!=""){
    whereCondition=` and id not in(${rowIds}) `
    }
    let query=await sequelize.query(`delete from questionnaire_field_options where  field_id=${id} ${whereCondition}`)
    if(query){
      return true;
    }
    else{
      return false;
    }
  }
  const updateTemplateFieldOptions=async(data,id)=>{
    try {
     
        let result = await customFieldOptions.update(data, {
            where: {
              id:id
  
            }
        })
        if (result) {
            return true
        } else {
            return false
        }
    } catch (error) {
        console.log(error)
    }
  }
  const checkExistingOptions=async(id,rowIds)=>{
    let query=await sequelize.query(`select * from questionnaire_field_options where id="${rowIds}" and field_id=${id}`)
    if(query.length>0){
      return query[0][0];
    }
    else{
      return false;
    }
  }
module.exports={
    getAllQuestionaireTemplates,
    getAllQuestionaireTemplateIds,
    addTemplate,
    deteleCustomTemplateById,
    getTemplateByid,
    updateCustomFieldTemplate,
    getAllTemplateIds,
    addTemplateField,
    addTemplateFieldOptions,
    getAllCustomFields,
    get_qnsTemplateData_by_id,
    deleteCustomField,
    updateCustomField,
    removeCustomField,
    checkExisting,
    updateTemplateField,
    fetchOptions,
    removeCustomFieldOptions,
    checkExistingOptions,
    updateTemplateFieldOptions
}