import java.sql.{DriverManager, ResultSet, ResultSetMetaData}

object SelectToInsertSample extends App {

  Class.forName("org.postgresql.Driver")

  withResource(
    DriverManager.getConnection(
      "jdbc:postgresql://localhost:5432/test_db",
      "root",
      "password"
    )
  ) { connection =>
    connection.setSchema("ec")
    withResource(connection.createStatement) { statement =>
      statement.setFetchSize(500)

      val sql =
        """
          |SELECT
          |  c.*
          |  , o.*
          |  , od.*
          |FROM
          |  customers c LEFT JOIN orders o
          |    on c.id = o.customer_id
          |  LEFT JOIN order_details od
          |    on o.id = od.order_id;
        """.stripMargin
      val resultSet = statement.executeQuery(sql)

      while (resultSet.next()) {
        val meta: ResultSetMetaData = resultSet.getMetaData
        val columnCount             = meta.getColumnCount

        Range(0, columnCount)
          .map(findColumnMetaAndValue(meta, resultSet, _))
          .groupBy(_.tableName)
          .foreach(printInsertSQL)
      }
    }
  }

  def findColumnMetaAndValue: (ResultSetMetaData, ResultSet, Int) => ColumnMetaAndValue = { (meta, resultSet, i) =>
    val columnIndex = i + 1
    ColumnMetaAndValue(
      meta.getTableName(columnIndex),
      columnIndex,
      meta.getColumnName(columnIndex),
      meta.getColumnTypeName(columnIndex),
      resultSet.getString(columnIndex)
    )
  }

  def printInsertSQL(entry: (String, Seq[ColumnMetaAndValue])): Unit = {
    val (tableName, values) = entry

    if (values.nonEmpty && !values.forall(_.value == null)) {
      val (columnNames, sqlValues) = values
        .map(v => (v.columnName, v.sqlValue))
        .unzip

      val sql =
        s"INSERT INTO $tableName ${columnNames.mkString("(", ", ", ")")} VALUES ${sqlValues.mkString("(", ", ", ")")};"
      println(sql)
    } else ()
  }

  def withResource[T <: AutoCloseable, R](resource: T)(f: T => R) {
    try {
      f(resource)
    } finally {
      resource.close()
    }
  }
}

case class ColumnMetaAndValue(
    tableName: String,
    columnIndex: Int,
    columnName: String,
    columnTypeName: String,
    value: String
) {
  val NumberOrBoolPrefixes = List(
    "bigserial",
    "serial",
    "smallserial",
    "smallint",
    "integer",
    "bigint",
    "numeric",
    "numeric",
    "real",
    "double precision",
    "boolean",
    "int",
    "float",
    "decimal",
    "bool",
  )

  def sqlValue: String =
    if (NumberOrBoolPrefixes.exists(columnTypeName.startsWith) || value == null) value
    else s"'$value'"
}