const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");
const Admin = require('./admin')
const {TWILIO_PHONE} = require('../constants/constant')
const TwilioCallModel = require('../models/twilioModel')

const Contacts = sequelize.define('contacts', {
    id: {type: Sequelize.INTEGER,primaryKey: true,autoIncrement: true,},
    name: {type: Sequelize.STRING(200),allowNull: false,},
    email_id: {type: Sequelize.STRING(200),defaultValue: null,},
    phone: {type: Sequelize.STRING(20),defaultValue: null,},
    address: {type: Sequelize.TEXT,},
    city: {type: Sequelize.STRING(200),defaultValue: null,},
    state_id: {type: Sequelize.INTEGER,defaultValue: null,},
    zip_code: {type: Sequelize.STRING(12),defaultValue: null,},
    country: {type: Sequelize.STRING(200),defaultValue: null,},
    description: {type: Sequelize.TEXT,},
    project_id: {type: Sequelize.INTEGER,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,},
    status: {type: Sequelize.TINYINT,allowNull: false,},
  }, {
    timestamps: false,
  });

const getAllContacts = async () =>{
    const result = await sequelize.query(
        `SELECT c.id, c.name, c.email_id, c.phone, c.address, c.city, c.state_id, c.zip_code,  c.country, c.description, `+
        ` c.updated_time, c.status, c.project_id, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) as added_by `+
        ` FROM contacts c`+
        ` LEFT JOIN users u ON c.updated_by=u.id`+
        ` WHERE c.status!= 2`+
        ` ORDER BY c.updated_time DESC`);
    let data=[];
    let calls;
	if(result[0]){
		if(result[0].length > 0){
			for(const row of result[0]){
				row['call_status']=1;
				if(row['phone']){
                    let phone=row['phone'];
                    let phoneNumber = '+' + phone.replace(/\+/g, '');
					let result= await TwilioCallModel.getAllCalls(` AND tc.to = ${phoneNumber}`,'DESC',3);
                        if(result.length > 0){
                            calls = result
                        }
					// let calls = $this->twilio_call_model->get_all_calls("(to='+".str_replace("+", "", $row['phone'])."')",'DESC',3);
                    if(calls){
						let missedCount=0;
						let ansCount=0;
						let i=0;
						if(calls['call_status']=='completed' || calls['call_status']=='in-progress'){
						    ansCount=1;
						}else{
							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;
						}
					}
                    row['phone'] = phoneNumber;
				}
                data.push(row);
            }
        }
    }
    return data;
}

const addContact = async (data) => {
    const result = await Contacts.create(data)
    return result;
}

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

const getContactById = async (id) => {
    const result= await sequelize.query(
        `SELECT c.id, c.name, c.email_id, c.phone, c.address, c.city, c.state_id, c.zip_code,  c.country, c.description,`+
        ` c.updated_time, c.status, CONCAT(COALESCE(u.first_name,''),COALESCE(u.last_name,'')) as added_by, `+
        ` s.name state_name, cn.name country_name`+
        ` FROM contacts c`+
        ` LEFT JOIN users u ON c.updated_by=u.id`+
        ` LEFT JOIN states s ON s.id=c.state_id`+
        ` LEFT JOIN countries cn ON cn.id=c.country`+
        ` WHERE c.id = ${id}`)

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

const getAllCallNotes = async (id) => {
    const result= await sequelize.query (`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.contact_id = ${id}`+
        ` AND l.status = 1`+
        ` ORDER BY l.id DESC`);
        if(result){
            if(result[0].length > 0){
                return result[0]
            }
        }
}

const getAllCalls = async (where='',order='DESC', limit=false) => {

    let sql = `SELECT tc.*,concat(u.first_name,' ',u.last_name) AS from_user, concat(u2.first_name,' ',u2.last_name) AS to_user`+
    ` FROM twilio_calls tc`+
    ` LEFT JOIN users u ON tc.from_user_id=u.id`+
    ` LEFT JOIN users u2 ON tc.to_user_id=u2.id`+
    ` WHERE tc.status = 1`+
    ` AND tc.direction != "outbound-api"`+
    ` AND tc.from != "client:support_agent"`+
    ` AND (tc.from=${TWILIO_PHONE} OR tc.to=${TWILIO_PHONE})`
    if(where){
        sql += where;
    }
    if(limit){
        sql += ` LIMIT ${limit}`;
    }
    sql += ` ORDER BY tc.start_time ${order}`

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

module.exports={ Contacts, getAllContacts, addContact, updateContact, getContactById, getAllCallNotes,getAllCalls }