import ownConn from "../dbConfig.js";

//get single employee by id
export const getEmployeeById = async (id, result) => {
    try {
        let sql = `SELECT * FROM customeremployees WHERE primaryID = ?`;
        const results = await ownConn.execute(sql, [id])
        result(null, results[0]);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

//get employees by customer id
export const getEmployeesByCustomerId = async (customerId, result) => {
    try {
        let sql = `SELECT * FROM customeremployees WHERE customerID = ? ORDER BY name ASC`;
        const results = await ownConn.query(sql, [customerId])
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

// Update employee to Database
export const updateEmployeeById = async (data, result) => {
    const id = data.primaryID;
    try {
        let sql = `UPDATE customeremployees SET name = ?, pronouns = ?, title = ?, firstName = ?, lastName = ?, preferredName = ?, department = ?, departmentID = ?, jobTitle = ?, phonenumber = ?, eMail = ?, notes = ? WHERE primaryID = ?`;
        const results = await ownConn.query(sql, [data.name, data.pronouns, data.title, data.firstName, data.lastName, data.preferredName, data.department, data.departmentID, data.jobTitle, data.phonenumber, data.eMail, data.notes, id]);
        results.insertId = results.insertId.toString();
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

//insert employee to databased
export const insertEmployee = async (data, result) => {
    try {
        const results = await ownConn.query(`INSERT INTO customeremployees(customerID, customername, name, pronouns, title, firstName, lastName, preferredName, department, departmentID, jobTitle, phonenumber, eMail, notes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [data.customerID, data.customername, data.name, data.pronouns, data.title, data.firstName, data.lastName, data.preferredName, data.department, data.departmentID, data.jobTitle, data.phonenumber, data.eMail, data.notes])
        results.insertId = results.insertId.toString();
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

//get all selected employees by name
export const getSelectedEmployeesByName = async (selected, result) => {
    try {
        let sql = `SELECT * FROM customeremployees WHERE LOWER(name) LIKE '%${selected.toLowerCase()}%' ORDER BY name ASC`;
        const results = await ownConn.execute(sql)
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

//get all selected employees by department name
export const getSelectedEmployeesByDepartmentName = async (selected, result) => {
    try {
        let sql = `SELECT * FROM customeremployees WHERE LOWER(department) LIKE '%${selected.toLowerCase()}%' ORDER BY department ASC`;
        const results = await ownConn.execute(sql)
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

//get all selected employees by departmentID
export const getSelectedEmployeesByDepartment = async (selected, result) => {
    try {
        let sql = `SELECT * FROM customeremployees WHERE departmentID = ? ORDER BY name ASC`;
        const results = await ownConn.execute(sql)
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};

// Delete employee to Database
export const deleteEmployeeById = async (id, result) => {
    try {
        let sql = `DELETE FROM customeremployees WHERE primaryID = ?`;
        const results = await ownConn.query(sql, [id])
        results.insertId = results.insertId.toString();
        result(null, results);
    }
    catch (err) {
        // Manage Errors
        console.log("SQL error : ", err);
        result(err, null);
    }
};