
const { empty } = require("../Helpers/Common");
const sequelize = require("../config/dbconnection");
const Sequelize = require('sequelize')


const Campaign = sequelize.define('campaigns', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    campaign_type:{type:Sequelize.TINYINT,allowNull:true,defaultValue:null},
    budget:{type:Sequelize.FLOAT(12,2),allowNull:true,defaultValue:null},
    target_list:{type:Sequelize.TEXT,allowNull:true},
    campaign_status:{type:Sequelize.TINYINT(2),allowNull:true,defaultValue:null},
    start_date:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    end_date:{type:Sequelize.INTEGER,allowNull:true,defaultValue:null},
    description:{type:Sequelize.TEXT,allowNull:true,defaultValue:null},
    added_by:{type:Sequelize.TINYINT,allowNull:true,defaultValue:null},
    added_time:{type:Sequelize.INTEGER,allowNull:false,defaultValue:null},
    updated_time:{type:Sequelize.INTEGER,allowNull:false,defaultValue:null},
    updated_by:{type:Sequelize.TINYINT,allowNull:false,defaultValue:null},
    status:{type:Sequelize.TINYINT,allowNull:false,defaultValue:null},

},{timestamps:false})
///campaign teams
const CampaignTeam = sequelize.define('campaign_teams',{
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    campaign_id:{type: Sequelize.INTEGER, allowNull: false,defaultValue:false  },
    team_id:{type: Sequelize.INTEGER, allowNull: false,defaultValue:false  },
    added_by:{type:Sequelize.TINYINT,allowNull:false,defaultValue:null},
    added_time:{type:Sequelize.INTEGER,allowNull:false,defaultValue:null},
    status:{type:Sequelize.TINYINT,allowNull:false,defaultValue:null},
},{timestamps:false})



const createCampaigns = async(data)=>{
    try {
        let result = await Campaign.create(data)
        return result.id
    } catch (error) {
        console.log(error)
    }
}
const createBatchCampaignTeam=async(data)=>{
    try {
        let result = await CampaignTeam.create(data)
        return result.id
    } catch (error) {
        console.log(error)
    }
}
const updateCampaign=async(id,data)=>{
    try {
        let result = await Campaign.update(data,{
            where:{
                id:id
            }, returning:true
        })
        if(result){
            return true
        }else{
            return false
        }
    } catch (error) {
        console.log(error)
    }
}
const getAllcampaigns = async (page, rowPerPage, searchData) => {
    try {
        const offset = (page - 1) * rowPerPage;
        let whereClause = '';

        if (searchData) {
            whereClause = `AND cn.name LIKE '%${searchData}%'`;
        }

        const query = `
            SELECT 
                cn.id, cn.name, cn.campaign_type, cn.budget, cn.target_list, 
                cn.campaign_status, cn.start_date, cn.end_date, cn.description, 
                cn.updated_time, cn.status, CONCAT(COALESCE(u.first_name, ''), COALESCE(u.last_name, '')) as added_by
            FROM 
                campaigns cn
            LEFT JOIN 
                users u ON cn.updated_by = u.id
            WHERE 
                cn.status != 2 ${whereClause}
            ORDER BY 
                cn.updated_time DESC
            LIMIT 
                ${rowPerPage} OFFSET ${offset};
        `;

        const result = await sequelize.query(query);

        const countQueryResult = await sequelize.query('SELECT COUNT(cn.id) as count FROM campaigns as cn');
        const totalCount = countQueryResult[0][0].count;

        if (result && result[0].length > 0) {          
            return {
                data: result[0],
                totalCount: totalCount
            };
        } else {
            return {
                data: [],
                totalCount: 0
            };
        }
    } catch (error) {
        console.error(error);
        throw error; 
    }
};

const getCampaignById=async(id)=>{
    try {
        let result = await sequelize.query(`
        SELECT cn.id, cn.name, cn.campaign_type, cn.budget, cn.target_list, cn.campaign_status, cn.start_date, cn.end_date, cn.description, cn.updated_time, cn.status, tl.name as target_name, CONCAT(COALESCE(u.first_name, ''), COALESCE(u.last_name, '')) as added_by
FROM campaigns cn
LEFT JOIN target_lists tl ON tl.id = cn.target_list
LEFT JOIN users u ON cn.updated_by = u.id
WHERE cn.id = ${id}
ORDER BY cn.updated_time DESC;
`)

if(!empty(result)&& result[0].length ===1){
    return result[0]
}else{
    return false
}
    } catch (error) {
        console.log(error)
    }
}
const getAllCampaignFormById=async(id)=>{
    try {
        let result = await sequelize.query(`
                  SELECT 
               cft.*,
               CONCAT(u.first_name, ' ', u.last_name) AS authorized_by     
           FROM 
               custom_field_templates cft
           LEFT JOIN 
              users u ON cft.added_by = u.id       
          WHERE 
              cft.status = 5 and cft.campaignId = ${id}
          ORDER BY 
              cft.id DESC;
          `);
        if (!empty(result)) {
          if (result[0].length > 0) {
            let data = result[0];
            return data;
          } else {
            return [];
          }
        } else {
          return [];
        }
              
    } catch (error) {
        console.log(error)
    }
}

const getCampaignFormDataById=async(id)=>{
    try {
        let 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 = ${id}
        `)
        let partnerId=0
        let teamId=0
        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 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 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}`
   
           }
           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,
                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) {
                console.log("Nushannus", f.tag_uid);
                customField = await getCustomFieldValues(partnerId, f.id,);
                console.log("Nushannus", customField);
              }
              else {
                console.log("Midh$$$$$$$$$", teamId);
                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).join(', ');
              }
             
            }
          }
          templateData.push(formData);
            }
            return templateData
        }else{
            return []
        }
    } catch (error) {
        console.log(error)
    }
}
//////////function 
const getCustomFieldValues = async (id, field_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 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 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);      
      } catch (error) {     
        console.error(error);
      }
    }
    return true;
  }
  const getFormUsersDataByFormId=async(id)=>{
    try {
      let result = await sequelize.query(`SELECT c.*,cf.name,cf.content FROM campaign_form_fields as c `+ 
      `left join custom_fields cf on cf.id=c.field_id where c.form_id=${id}
      `)
      if(!empty(result)){
        return result[0]
      }else{
        return false
      }
    } catch (error) {
      console.log(error)
    }
  }
module.exports={
    createCampaigns,
    createBatchCampaignTeam,
    getAllcampaigns,
    updateCampaign,
    getCampaignById,
    getAllCampaignFormById,
    getCampaignFormDataById,
    updateBatchData,
    getFormUsersDataByFormId,
}