const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')
const TwilioCallModel = require('../models/twilioModel');

const Leads = sequelize.define('leads', {
    id: { type: Sequelize.INTEGER, allowNull: false, autoIncrement: true, primaryKey: true, },
    name: { type: Sequelize.STRING(100), allowNull: false, },
    partner_name: { type: Sequelize.STRING(200), allowNull:true, defaultValue:null },
    title: { type: Sequelize.STRING(200), allowNull:true, defaultValue:null },
    website_url : { type: Sequelize.STRING(200), allowNull:true, defaultValue:null },
    email_id : { type: Sequelize.STRING(190), allowNull:true, defaultValue:null },
    phone : { type: Sequelize.STRING(20), allowNull:true, defaultValue:null },
    phone2 : { type: Sequelize.STRING(20), allowNull:true, defaultValue:null },
    address : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    city : { type: Sequelize.STRING(200), allowNull:true, defaultValue:null },
    state_id : { type: Sequelize.INTEGER, allowNull:true, defaultValue:null },
    zip_code : { type: Sequelize.STRING(12), allowNull:true, defaultValue:null },
    country : { type: Sequelize.STRING(100), allowNull:true, defaultValue:null },
    lead_status : { type: Sequelize.TINYINT, allowNull:true, defaultValue:null },
    source_id : { type: Sequelize.TINYINT, allowNull:true, defaultValue:null },
    opportunity_amount : { type: Sequelize.FLOAT(12, 2), allowNull:true, defaultValue:null },
    industry_id : { type: Sequelize.TINYINT, allowNull:true, defaultValue:null },
    description : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    campaign_id : { type: Sequelize.INTEGER, allowNull:true, defaultValue:null },
    added_by : { type: Sequelize.TINYINT, allowNull:false },
    added_time : { type: Sequelize.INTEGER, allowNull:false },
    updated_by : { type: Sequelize.TINYINT, allowNull:false },
    updated_time : { type: Sequelize.INTEGER, allowNull:false },
    conversion_status : { type: Sequelize.TINYINT, allowNull:true, defaultValue:null },
    status : { type: Sequelize.TINYINT, allowNull:false, defaultValue:1 },
    industry_other : { type: Sequelize.STRING(255), allowNull:true, defaultValue:null },
    constant_changes : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    tracking_database : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    is_seed_to_sale_software : { type: Sequelize.TINYINT, allowNull:false, defaultValue:2 },
    is_receiving_newsletter : { type: Sequelize.TINYINT, allowNull:false, defaultValue:2 },
    marketing : { type: Sequelize.STRING(255), allowNull:true, defaultValue:null },
    printing_and_labels : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    is_marketplace_to_sell : { type: Sequelize.TINYINT, allowNull:false, defaultValue:2 },
    marketplace_to_sell : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    is_card_processing : { type: Sequelize.TINYINT, allowNull:false, defaultValue:2 },
    problem_with_industry : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
    source_website : { type: Sequelize.TEXT, allowNull:true, defaultValue:null },
  }, {
    timestamps: false,
  });

const TwilioCallNotes = sequelize.define('twilio_call_notes', {
    id: { type: Sequelize.INTEGER, allowNull: false, autoIncrement: true, primaryKey: true, },
    call_sid: { type: Sequelize.STRING(200), allowNull: true, defaultValue:null },
    parent_call_sid: { type: Sequelize.STRING(200), allowNull: true, defaultValue:null },
    notes: { type: Sequelize.TEXT, allowNull: false },
    partner_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    contact_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    lead_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue:null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    timestamp: { type: Sequelize.INTEGER, allowNull: false },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue:1 },
}, {
timestamps: false,
});

const LeadStatus = sequelize.define('lead_status', {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    name: {
      type: Sequelize.STRING(255),
      allowNull: false,
    },
    added_by: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    timestamp: {
      type: Sequelize.INTEGER,
      allowNull: false,
    },
    status: {
      type: Sequelize.TINYINT,
      allowNull: false,
    },
  }, {
    tableName: 'lead_status',
    timestamps: false,
    underscored: true,
});

const getAllLeadAjax = async (where=false,orderBy=false, orderType=false ,start=false,length=false) => {
    try{
        let orderStr='',limitstr='';
        let calls;
        let data=[];
        let whereStr=' WHERE l.status!=2 ';
        if(where){
            whereStr +=` AND ${where}`;
        }
        
        if(orderBy && orderType){
            orderStr=` ORDER BY l.${orderBy} ${orderType}`;
        }else if(orderBy){
            orderStr=` ORDER BY l.${orderBy} ASC`;
        }else{
            orderStr=` ORDER BY l.updated_time DESC`;
        }
        
        if(start && length){
            limitstr=` LIMIT ${start},${length}`;			
        }else if(start){
            limitstr=` LIMIT ${start},10`;			
        }
        else {
            limitstr=` LIMIT 50`;			
        }
        
        const result = await sequelize.query(`SELECT l.id, l.name, l.partner_name, l.title, l.source_website, l.website_url,`+
        ` l.email_id, l.phone, l.address, l.city, l.state_id, l.zip_code, l.country, lst.name as lead_status, l.source_id, `+
        ` l.opportunity_amount, l.industry_id, l.description, l.campaign_id, l.updated_time, l.conversion_status, l.status, `+
        ` CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS added_by FROM leads l`+
        ` LEFT JOIN users u ON l.updated_by=u.id `+
        ` LEFT JOIN lead_status lst ON l.lead_status=lst.id AND lst.status!=2 `+
        ` ${whereStr} `+
        ` ${orderStr} `
        + ` ${limitstr} `
        );

        if(result[0]){
            if(result[0].length > 0){
                for(const row of result[0]){
                    let rowData = {};
                    if(row['status']===1){
                        rowData['status']=1;
                        rowData['statusText']='Active';
                    }else {
                        rowData['status']=row['status'];
                        rowData['statusText']='Inactive';
                    }
                    row['call_status']=1;
                    if(row['phone']){
                        let phone=row['phone'];
                        let phoneNumber = '+' + phone.replace(/\s/g, '');
                        let callsResult = await TwilioCallModel.getAllCalls(` AND tc.to = ${phoneNumber}`,'DESC',3);
                        // console.log("callsResult",callsResult);
                            if(callsResult.length > 0){
                                calls = callsResult
                            }
                        
                            if(calls){
                            let missedCount=0;
                            let ansCount=0;
                            let i=0;
                            if((calls['call_status']==='completed') || (calls['call_status']==='in-progress')){
                                ansCount=1;
                            }else{
                                // console.log("calls",calls);
                                for(const call of calls){
                                    if(i<3){
                                        if(!(call['call_status']=='completed' || call['call_status']=='in-progress')){ 
                                            missedCount++;
                                        }
                                    }else{
                                        break;
                                    }
                                    i++;
                                }
                            }
                            if(missedCount >= 3){
                                row['call_status']=3;
                            }else if(missedCount >= 1){
                                row['call_status']=2;
                            }else if(ansCount === 1){
                                row['call_status']=4;
                            }				
                        }
                        rowData['phone']=phoneNumber
                    }

                    rowData['callStatus']=row['call_status'];
                    rowData['name']=row['name'];
                    rowData['email_id']=row['email_id'];
                    rowData['leadStatus']=row['lead_status'];
                    rowData['source_website']=row['source_website'];
                    rowData['updated_by']=row['added_by'];
                    rowData['lastUpdated']=row['updated_time'];
                    rowData['id']=row['id']
                    data.push(rowData);
                }
            }
        }
        return data;
    }
    catch(error){
        console.log("getAllleadajax error",error);
    }
}

const addLead = async (data) => {
    const result = await Leads.create(data)
    if(result){
        return result.dataValues.id
    }
    else { 
        return false;
    }
}

const batchInsertLeadNote = async (data) => {
    try{
        const result = await TwilioCallNotes.bulkCreate(data);
        const insertedIds = result.map(record => record.dataValues.id);
        return insertedIds;
    } catch (error) {
        console.error('Error in batchInsertLeadNote:', error);
        return false;
    }
}

const getAllLeadStatus = async (status=false) => {
    let sql = `SELECT l.id, l.name, l.timestamp, l.status, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) as added_by `+
    ` FROM lead_status l`+
    ` LEFT JOIN users u ON l.added_by=u.id`
    if(status !== false){
        sql += ` WHERE l.status = ${status}`;
    }else{
        sql += ` WHERE l.status != 2`;
    }
    sql += ` ORDER BY l.timestamp DESC `

    const result= await sequelize.query(sql);
    if(result){
        if(result[0].length > 0){
            return result[0]
        }
    }
}

const getLeadById = async (id) => {
    const result= await sequelize.query(`SELECT l.id, l.name, l.partner_name, l.title, l.website_url, l.email_id, l.phone, `+
    ` l.phone2, l.address, l.city, l.state_id, l.zip_code, l.country, l.lead_status, l.source_id, l.opportunity_amount, `+
    ` l.industry_id, l.description, l.campaign_id, l.updated_time, l.conversion_status, l.status, l.industry_other, `+
    ` l.constant_changes, l.tracking_database, l.is_seed_to_sale_software, l.is_receiving_newsletter, l.marketing, `+
    ` l.printing_and_labels, l.is_marketplace_to_sell, l.marketplace_to_sell, l.is_card_processing, l.problem_with_industry,`+
    ` l.source_website, s.name as state_name, i.name as industry_name, c.name as campaign_name, lst.name as lead_status_name,`+
    ` CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) as added_by `+
    ` FROM leads l`+
    ` LEFT JOIN states s ON s.id=l.state_id`+
    ` LEFT JOIN industries i ON i.id=l.industry_id`+
    ` LEFT JOIN campaigns c ON c.id=l.campaign_id`+
    ` LEFT JOIN lead_status lst ON lst.id = l.lead_status AND lst.status!=2`+
    ` LEFT JOIN users u ON l.updated_by=u.id`+
    ` WHERE l.id = ${id}`);

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

const getAllCallNotes = async (id,orderBy='DESC',includeCall=true) => {
    let sql = `SELECT l.*, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS added_by`+
            ` FROM twilio_call_notes l`+
            ` LEFT JOIN users u ON l.added_by=u.id`+
            ` WHERE l.lead_id = ${id}`+
            ` AND l.status = 1`
    if(!includeCall){
        sql += ` AND l.call_sid IS NULL`;
    }
    sql += ` ORDER BY l.id ${orderBy}`
    const result=await sequelize.query(sql);
    if(result){
        if(result[0].length > 0){
            return result[0];
        }
    }
}

const updateLead = async (id,data) => {
    const updatedRows = await Leads.update(data,{
        where : {
            id : id
        }
    });
    if(updatedRows){
        return true
    }else{
        return false
    }
}

const updateBatchData = async (Model,data) => {	
    try {
        for (const item of data) {
            if (item.id) {
                await Model.update(item, {
                where: { id: item.id },
                fields: Object.keys(item).filter(key => key !== 'added_by' && key !== 'timestamp')
                });
            } else {
                await Model.create(item);
            }
        }
        return true;
    } catch (error) {
        console.error('Error updating data:', error);
        return false;
    }
}

const deleteLeadNote = async (id) => {
    try{
        const result = await TwilioCallNotes.destroy({
            where : {
                id : id
            }
        });
        return true;
    }
    catch(error){
        console.log("deleteLeadNote error",error)
        return false;
    }
}

const addLeadStatus = async (data) => {
    const result = await LeadStatus.create(data);
    return result;
}

const updateLeadStatus = async (id,data) => {
    const result = await LeadStatus.update(data,{
        where : { id : id}
    })
    return result ? true : false;
}

const getLeadStatusById = async (id) => {
    const result = await sequelize.query(`SELECT l.id, l.name, l.timestamp, l.status, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS added_by`+
        ` FROM lead_status l`+
        ` LEFT JOIN users u ON l.added_by=u.id`+
        ` WHERE l.id = ${id}`);

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

module.exports = { Leads, TwilioCallNotes, LeadStatus, getAllLeadAjax, addLead, batchInsertLeadNote, getAllLeadStatus, getLeadById, 
    getAllCallNotes, updateLead, updateBatchData, deleteLeadNote, addLeadStatus, updateLeadStatus, getLeadStatusById }