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

const Department = sequelize.define('departments', {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: Sequelize.STRING, allowNull: false },
    forward_number: { type: Sequelize.STRING, allowNull: true,defaultValue:null },
    added_by: { type: Sequelize.INTEGER, allowNull: false },
    added_time: { type: Sequelize.INTEGER, allowNull: false },
    updated_by: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 0 },
    updated_time: { type: Sequelize.INTEGER, allowNull: true, defaultValue: 0 },
    // timestamp: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    status: { type: Sequelize.TINYINT, allowNull: false, defaultValue: 1 },
    
},{timestamps: false})

const getDepartmentById = async (id) => {
    let query = await sequelize.query(`SELECT * FROM departments WHERE status=1 AND id=${id}`
    )
    if (query[0].length === 1) {
        return query[0];
    }
    else {
        return false;
    }
}

const getAllDepartments = async () => {
  try{
    const result = await sequelize.query(`SELECT d.id, d.name,d.forward_number, d.updated_time, d.status, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS added_by`+
      ` FROM departments d`+
      ` LEFT JOIN users u ON d.updated_by=u.id`+
      ` WHERE d.status!= 2`+
      ` ORDER BY d.updated_time DESC`)
    if(result){
        if(result[0].length > 0){
            return result[0];
        }
        else{
          return false;
        }
    }else{
      return false;
    }
  }
  catch(error){
    console.log("getAllDepartments error",error);
  }
}

const getDepartmentByIdForEdit = async (id) => {
  const result= await sequelize.query(`select d.id, d.name, d.updated_time, d.status, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) as added_by`+
  ` FROM departments d`+
  ` LEFT JOIN users u ON d.updated_by=u.id`+
  ` WHERE d.id = ${id}`);

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

const addDepartment = async (data) => {
  try{
    const result = await Department.create(data);
    return result;
  }
  catch(error){
    console.log("add department error",error);
    return false;
  }
}

const updateDepartment = async (id,data) => {
  try{
    const result = await Department.update(data,{
      where : { id : id }
    })
    return result;
  }
  catch(error){
    console.log(error)
    return false;
  }
}

module.exports={ getDepartmentById, Department, getAllDepartments, getDepartmentByIdForEdit, addDepartment, updateDepartment }