const sequelize = require("../config/dbconnection")
const Sequelize = require('sequelize')

const Opportunities = sequelize.define('opportunities', {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      autoIncrement: true,
      primaryKey: true
    },
    name: {
      type: Sequelize.STRING(100),
      allowNull: false
    },
    stage_id: {
      type: Sequelize.INTEGER,
      allowNull: true
    },
    amount: {
      type: Sequelize.FLOAT(12, 2),
      allowNull: false
    },
    probability: {
      type: Sequelize.INTEGER,
      allowNull: false
    },
    close_date: {
      type: Sequelize.INTEGER,
      allowNull: false
    },
    lead_source_id: {
      type: Sequelize.INTEGER,
      allowNull: true
    },
    description: {
      type: Sequelize.TEXT,
      allowNull: true
    },
    project_id: {
      type: Sequelize.INTEGER,
      allowNull: true
    },
    added_by: {
      type: Sequelize.INTEGER,
      allowNull: false
    },
    added_time: {
      type: Sequelize.INTEGER,
      allowNull: false
    },
    updated_by: {
      type: Sequelize.INTEGER,
      allowNull: false
    },
    updated_time: {
      type: Sequelize.INTEGER,
      allowNull: false
    },
    status: {
      type: Sequelize.TINYINT,
      allowNull: false,
      defaultValue: 1
    }
  }, {
    timestamps: false
  });
  

const getAllOpportunities = async () => {
    const result= await sequelize.query(`SELECT o.id, o.name, o.stage_id, o.amount, o.probability, o.close_date, `+
    ` o.lead_source_id, o.description, o.updated_time, o.project_id, o.status,`+
    ` CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) as added_by`+
    ` FROM opportunities o`+
    ` LEFT JOIN users u ON o.updated_by=u.id`+
    ` WHERE o.status!= 2`+
    ` ORDER BY o.updated_time DESC`);
	if(result){
		if(result[0].length > 0){
			return result[0]
        }
    }
}

const addOpportunity = async (data) => {
    let addOpportunityresult = await Opportunities.create(data);
    return addOpportunityresult.dataValues.id;
}

const updateOpportunity = async (id,data) => {
    let updateOpportunityresult = await Opportunities.update(data,{
        where : {
            id: id
        }
    });
    return updateOpportunityresult ? true : false;
}

const getOpportunityById = async (id) => {
    let result = await sequelize.query(`SELECT o.id, o.name, o.stage_id, o.amount, o.probability, o.close_date, o.lead_source_id, `+
    ` o.description, o.updated_time, o.status, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) as added_by`+
    ` FROM opportunities o`+
    ` LEFT JOIN users u ON o.updated_by=u.id`+
    ` WHERE o.id = ${id}`);

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

module.exports = { Opportunities,getAllOpportunities,addOpportunity,updateOpportunity,getOpportunityById }