migrations.scala

import fs2.Task
import cats.implicits._
import doobie.imports._
import doobie.free.{databasemetadata => DMD}

package object migrations {
  case class TableDesc(
    catalog: Option[String],
    schema: Option[String],
    name: String
  )

  val getTables: ConnectionIO[List[TableDesc]] =
    HC.getMetaData(DMD.getTables(null, null, null, null))
      .flatMap(HRS.list[TableDesc].transK[ConnectionIO].run)

  def versionTableExists(t: List[TableDesc]) =
    t.exists(_.name == "schema_version")

  val createSchemaVersionTableQ =
    sql"CREATE TABLE schema_version (version int NOT NULL)".update

  def setInitialVersionQ(v: Int) =
    sql"INSERT INTO schema_version (version) VALUES ($v)".update

  def setVersionQ(v: Int) =
    sql"UPDATE schema_version SET version = $v".update

  val currentVersionQ =
    sql"SELECT version FROM schema_version".query[Int]

  def migrate(migrations: List[ConnectionIO[Unit]]) =
    getTables.flatMap { tds =>
      val version = migrations.length
      if (versionTableExists(tds)) {
        currentVersionQ.unique.flatMap { currentVersion =>
          if (currentVersion < version) {
            val migrationsToPerform = migrations.drop(currentVersion)
            migrationsToPerform.sequence_ *>
              setVersionQ(newVersion).run.void
          } else ().pure[ConnectionIO]
        }
      } else {
        createSchemaVersionTableQ.run *>
          migrations.sequence_ *>
          setInitialVersionQ(version).run.void
      }
    }
}

object example extends App {
  val xa = DriverManagerTransactor[Task](
    "org.postgresql.Driver",
    "jdbc:postgresql:test",
    "postgres",
    ""
  )
  
  val migrations = List(
    sql"create table test (id int not null, name text not null)".update.run.void,
    sql"insert into test values (1, 'test1')".update.run.void
  )
  
  val p = migrations.migrate(migrations).transact(xa)
  
  p.unsafeValue
}