compact.scala

package com.tinylabproductions.analytics_collector.commands

import java.sql.Connection
import java.time.{LocalDate, LocalDateTime, LocalTime}

import com.lucidchart.relate._
import com.tinylabproductions.analytics_collector.StaticConfig
import com.tinylabproductions.analytics_collector.db.DB
import com.tinylabproductions.analytics_collector.db.Tables.CompactableTable

import scalaz.std.AllInstances._
import scalaz.syntax.traverse._

object Compact {
  def apply(tableNames: List[String])(implicit connection: Connection): Unit = {
    val tablesV = tableNames.traverseU { name =>
      CompactableTable.values.find(_.tableName.toLowerCase == name.toLowerCase)
        .toRight(s"Can't find date time table with name '$name', known tables: ${CompactableTable.values}")
    }
    tablesV match {
      case Right(tables) =>
        tables.foreach { table =>
          println(s"Compacting $table")
          compact(table, StaticConfig.now().minusMonths(1).toLocalDate)
          println(s"Compacted $table")
        }
      case Left(error) =>
        println(error)
    }
  }

  def compact(
    table: CompactableTable, compactUntil: LocalDate
  )(implicit connection: Connection): Unit = {
    val zeroTime = LocalTime.of(0, 0)
    val oneTime = LocalTime.of(0, 0, 1)
    val dayEndTime = LocalTime.of(23, 59, 59)
    val compactingThreshold = LocalDateTime.of(compactUntil, zeroTime)
    val selectDay =
      sql"""
SELECT ${table.entryDateTime.sql}
FROM ${table.sql}
WHERE
  ${table.entryDateTime.sql} < ${compactingThreshold ->: table.entryDateTime}
  AND ${table.entryDateTime.sql}::time <> '00:00:00'
LIMIT 1
       """

    val pkColumns = table.pkColumns.map(_.sql)
    val pkColumnsSql = DB.joinSqlParts(pkColumns)
    val countersSql = DB.joinSqlParts(table.counters.map(_.sql))
    val compacterPkColumns = DB.joinSqlParts(
      table.pkColumns.filterNot(_ == table.entryDateTime).map(_.sql)
    )
    val compacterSelect = DB.joinSqlParts(
      compacterPkColumns +: table.counters.map(c => sql"sum(${c.sql}) as ${c.sql}")
    )
    val conflictResolution =
      DB.joinSqlParts(table.counters.map(c => sql"${c.sql} = excluded.${c.sql}"))

    println(selectDay)
    def run(): Unit = {
      selectDay.asSingleOption(table.entryDateTime.get(_)) match {
        case Some(localDateTime) =>
          val date = localDateTime.toLocalDate
          println(s"$table [$date]")
          val start = LocalDateTime.of(date, zeroTime)
          val startExceptCompacted = LocalDateTime.of(date, oneTime)
          val end = LocalDateTime.of(date, dayEndTime)
          def whereSql(s: LocalDateTime) =
sql"""
${table.entryDateTime.sql}
  BETWEEN ${s ->: table.entryDateTime} AND ${end ->: table.entryDateTime}
"""
          val upsertSql =
sql"""
INSERT INTO ${table.sql} ($compacterPkColumns, $countersSql, ${table.entryDateTime.sql})
(
  SELECT
    $compacterSelect,
    ${start ->: table.entryDateTime} as ${table.entryDateTime.sql}
  FROM ${table.sql}
  WHERE ${whereSql(start)}
  GROUP BY $compacterPkColumns
)
ON CONFLICT ($pkColumnsSql) DO UPDATE
  SET $conflictResolution
"""
          val deletionSql =
            sql"""
DELETE FROM ${table.sql}
WHERE ${whereSql(startExceptCompacted)}
"""

          println(upsertSql)
          println(deletionSql)

          upsertSql.execute()
          deletionSql.execute()
          connection.commit()

          run()
        case None =>
      }
    }

    val oldAutoCommit = connection.getAutoCommit
    try {
      connection.setAutoCommit(false)
      run()
    }
    finally {
      connection.setAutoCommit(oldAutoCommit)
    }
  }
}