const postgres = require('pg')
// const dburl = 'postgres://user:pass@localhost:5432/database'
// const dburl = 'postgres://postgres:pass1234@localhost:5432/payroll'
const dburl = process.env.DATABASEURL || ''  // secret
if(!dburl){
  console.error('DATABASE URL NOT AVAILABLE')
}

async function list(){
  try {
    const client = new postgres.Pool({ connectionString: dburl })
    await client.connect()
    const sql = 'SELECT * FROM employees ORDER BY id'
    const result = await client.query(sql)
    return result.rows
  } catch(ex) {
    console.error(ex)
    return {error: 'Could not connect to DB'}
  }
}

async function get(id){
  try {
    const client = new postgres.Pool({ connectionString: dburl })
    await client.connect()
    const sql = 'SELECT * FROM employees WHERE id=$1'
    const values = [id]
    const result = await client.query(sql, values)
    let data = null
    if(result.rows && result.rows.length>0) { 
      data = result.rows[0]
    }
    return data
  } catch(ex) {
    console.error(ex)
    return {error: 'Could not connect to DB'}
  }
}

async function add(name, phone, salary){
  try {
    const client = new postgres.Pool({ connectionString: dburl })
    await client.connect()
    const sql = `INSERT INTO employees(name, phone, salary)
                 VALUES($1, $2, $3) RETURNING id`
    const values = [name, phone, salary]
    const result = await client.query(sql, values)
    if(result.rowCount>0) {
      return result.rows[0].id
    }
    return null
  } catch(ex) {
    console.error(ex)
    return {error: 'Could not connect to DB'}
  }
}

async function save(name, phone, salary, id){
  try {
    const client = new postgres.Pool({ connectionString: dburl })
    await client.connect()
    const sql = 'UPDATE employees SET name=$1, phone=$2, salary=$3 WHERE id=$4'
    const values = [name, phone, salary, id]
    const result = await client.query(sql, values)
    if(result.rowCount>0) {
      return result.rowCount
    }
    return null
  } catch(ex) {
    console.error(ex)
    return {error: 'Could not connect to DB'}
  }
}

async function remove(id){
  try {
    const client = new postgres.Pool({ connectionString: dburl })
    await client.connect()
    const sql = 'DELETE FROM employees WHERE id=$1'
    const values = [id]
    const result = await client.query(sql, values)
    if(result.rowCount>0) {
      return result.rowCount
    }
    return null
  } catch(ex) {
    console.error(ex)
    return {error: 'Could not connect to DB'}
  }
}

const Employees = {
  list,
  get,
  add,
  save,
  remove
}

module.exports = Employees
        

const Employees = require('./employees.js')

const list = await Employees.list()
console.log(list)

const employee = await Employees.get(1)
console.log(employee)

const newId = await Employees.add('John Doe', '555-1234', 8500)
console.log(newId)

const count = await Employees.save('John Doe', '555-1234', 9750, 1)
console.log(count)

const result = await Employees.remove(1)
console.log(result)