package demo

import java.sql.{Connection, ResultSet, Statement}

import scala.io.Source

object Main {

  def main(args: Array[String]): Unit = {
    implicit val connection:Connection = MyFactory.connection
    val employees = readAllEmployeesFromDb
    println(employees)
    //Now read data from csv file and create domain objects and insert into DB
    //remember, they are relational. So, one's generated ID would be used in another.
    val csvDataSeq = readDataFromCsvFile()
    println(csvDataSeq)
    //Let's use currying and apply it using partially applied functions
    val empSeq = csvDataSeq.map(d => Employee(firstName = d.fName, lastName = d.lName))
    val addressSeq = csvDataSeq.map(d => Address(line1 = d.line1, line2 = d.line2, postcode = d.postcode))
    //here we know mapping is 1:1, so we cam zip both of them
    //if mapping is 1:MANY, then group them as Map[Employee, Set[Address]]
    val zippedEmpAndAddress = empSeq.zip(addressSeq)
    zippedEmpAndAddress foreach { empAndAdd =>
      val updatedEmployee = insertEmployee(empAndAdd._1)(connection)
      println(updatedEmployee)
      //this is not exactly partially applied function.
      val updatedAddress = insertAddress(empAndAdd._2, connection)(updatedEmployee)
      println(updatedAddress)
    }
    connection.commit()

    val insertedEmployees = readAllEmployeesFromDb
    println(insertedEmployees)

  }

  def readAllEmployeesFromDb(implicit connection: Connection): Seq[Employee] = {
    val ps = connection.prepareStatement("SELECT * FROM EMP_DB.EMPLOYEE")
    val rs = ps.executeQuery()
    lazy val rsToEmp: ResultSet => Employee =
      resultSet => Employee(
        Some(resultSet.getLong("id")),
        resultSet.getString("first_name"),
        resultSet.getString("last_name"))
    resultSetToRows(rs, rsToEmp)
  }

  def resultSetToRows[T](rs: ResultSet, f: ResultSet => T): Seq[T] = {
    def loop(accumulator: Seq[T]): Seq[T] = {
      if (rs.next()) loop(f(rs) +: accumulator)
      else accumulator
    }

    loop(Nil)
  }

  private def readDataFromCsvFile(): Seq[CsvModel] = {
    val data = Source.fromResource("emp_data.csv").getLines().drop(1).toList
    data.map(_.split(",")).map(row => CsvModel(row.head, row(1), row(2), row(3), row(4)))
  }

  def insertEmployee(e: Employee)(implicit connection: Connection): Employee = {
    val sql = "INSERT INTO EMP_DB.EMPLOYEE(first_name,last_name) values(?,?)"
    val ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
    ps.setString(1, e.firstName)
    ps.setString(2, e.lastName)
    ps.executeUpdate()
    val rs = ps.getGeneratedKeys
    println("insert-employee-resultSet-get-next = " + rs.next())
    val empId = rs.getLong(1)
    e.copy(id = Some(empId))
  }

  def insertAddress(a: Address, connection: Connection)(e: Employee): Address = {
    val sql = "INSERT INTO EMP_DB.ADDRESS(line_1,line_2,postcode,emp_id) values(?,?,?,?)"
    val ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
    ps.setString(1, a.line1)
    ps.setString(2, a.line2)
    ps.setString(3, a.postcode)
    ps.setLong(4, e.id.getOrElse(throw new RuntimeException("emp-id not available for insert")))
    ps.executeUpdate()
    val rs = ps.getGeneratedKeys
    println("insert-address-resultSet-get-next = " + rs.next())
    val addressId = rs.getLong(1)
    a.copy(id = Some(addressId), empId = e.id)
  }

  //is there any use of this val??
  val partiallyAppliedInsertAddress: (Address, Connection) => Employee => Address = {
    (a: Address, connection: Connection) => insertAddress(a, connection)
  }

}