const mariadb = require('mariadb');

const connectonString = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    connectionLimit: 5
}

const pool = mariadb.createPool(connectonString)

async function getConnection() {
    return await pool.getConnection();    
}

async function getEmployees(conn) {
    const sql = 'select * from employees';
    return await conn.query(sql);
}

async function getEmployee(conn, id) {
    const sql = 'select * from employees where id=?';
    return await conn.query(sql, [id]);
}

async function createEmployee(conn, emp) {
    const sql = 'insert into employees ' +
        '(name, city, salary) ' +
        'values (?, ?, ?)';
    return await conn.query(sql,
        [emp.name, emp.city, emp.salary]);
}

async function deleteEmployee(conn, id) {
    const sql = 'delete from employees ' +
        'where id=?';
    return await conn.query(sql, [id]);
}

async function updateEmployee(conn, emp, id) {
    const sql = 'update employees set ' +
        'name=?, city=?, salary=? ' +
        'where id=?';
    return await conn.query(sql, [emp.name, emp.city, emp.salary, id]);
}

module.exports = {    
    getConnection,
    getEmployees,
    getEmployee,
    createEmployee,
    deleteEmployee,
    updateEmployee
}