//设置package包名称以及导入依赖的类
package cz.alenkacz.db.postgresscala

import java.net.InetAddress
import java.sql.Time
import java.time.Instant
import java.util.UUID

trait DbValue {
  def string: String
  def stringOpt: Option[String]
  def strings: Seq[String]
  def int: Int
  def intOpt: Option[Int]
  def ints: Seq[Int]
  def bigInt: BigInt
  def bigIntOpt: Option[BigInt]
  def bigInts: Seq[BigInt]
  def double: Double
  def doubleOpt: Option[Double]
  def doubles: Seq[Double]
  def float: Float
  def floatOpt: Option[Float]
  def floats: Seq[Float]
  def long: Long
  def longOpt: Option[Long]
  def longs: Seq[Long]
  def bool: Boolean
  def boolOpt: Option[Boolean]
  def bools: Seq[Boolean]
  def short: Short
  def shortOpt: Option[Short]
  def shorts: Seq[Short]
  def inetAddress: InetAddress
  def inetAddresses: Seq[InetAddress]
  def inetAddressOpt: Option[InetAddress]
  def uuid: UUID
  def uuids: Seq[UUID]
  def uuidOpt: Option[UUID]
  def instant: Instant
  def instantOpt: Option[Instant]
  def time: Time
  def timeOpt: Option[Time]
  def bytes: Seq[Byte]
  def bytesOpt: Option[Seq[Byte]]
  def any: Any
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.{Connection, PreparedStatement, ResultSet}
import play.api.db._
import play.api.Play.current
import java.sql.Date
import java.sql.Time


case class ConsultaReserva(var id: Int, var fecha: String, var hora: String, var usuario_email: String, var mesa_numero: Int, var restaurante_nit: String, var estado: Int)


object ConsultaReservaRepository {
  val Insert = "INSERT INTO reserva(fecha,hora,usuario_email,mesa_numero,restaurante_nit) values(?,?,?,?,?)"

}


class ConsultaReservas {

  import ConsultaReservaRepository._

  def traerTodo1(date_ini : Long, date_end : Long): Seq[ConsultaReserva] = {
    val conn = DB.getConnection()
    try {
      val stmt = conn.createStatement

      val rs = stmt.executeQuery("SELECT id, fecha, hora, usuario_email, mesa_numero, restaurante_nit, estado FROM reserva WHERE fecha BETWEEN '"+date_ini+"' AND '"+date_end+"' ORDER BY id DESC")

      var ConsultaReservas: Seq[ConsultaReserva] = Seq.empty

      while (rs.next) {
        val consultaReserva = new ConsultaReserva(rs.getInt("id"),
          rs.getString("fecha"),
          rs.getString("hora"),
          rs.getString("usuario_email"),
          rs.getInt("mesa_numero"),
          rs.getString("restaurante_nit"),
          rs.getInt("estado")
        )
        ConsultaReservas = ConsultaReservas :+ consultaReserva
      }
      ConsultaReservas
    } finally {
      conn.close()
    }

  }

  private def crearPrepareStatementGuardar(conexion: Connection, consultaReserva: ConsultaReserva): PreparedStatement = {
    val preparedStatement = conexion.prepareStatement(Insert)
    var date: Date = Date.valueOf(consultaReserva.fecha)
    var time: Time = Time.valueOf(consultaReserva.hora)
    preparedStatement.setDate(1, date)
    preparedStatement.setTime(2, time)
    preparedStatement.setString(3, consultaReserva.usuario_email)
    preparedStatement.setInt(4, consultaReserva.mesa_numero)
    preparedStatement.setString(5, consultaReserva.restaurante_nit)
    preparedStatement
  }
} 

//设置package包名称以及导入依赖的类
package utilities

import java.sql.{Timestamp,Date,Time}
import org.joda.time.{DateTime,LocalDate,LocalTime,DateTimeZone}
import org.joda.time.format._

object DateHelper {
  def dateTimeToSqlTimestamp: DateTime => Timestamp = { dt => new Timestamp(dt.getMillis) }
  def sqlTimestampToDateTime: Timestamp => DateTime = { ts => new DateTime(ts.getTime) }
  def localDateToSqlDate: LocalDate => Date = { ld => new Date(ld.toDateTimeAtStartOfDay(DateTimeZone.UTC).getMillis) }
  def sqlDateToLocalDate: Date => LocalDate = { d  => new LocalDate(d.getTime) }
  def localTimeToSqlTime: LocalTime => Time = { lt => new Time(lt.toDateTimeToday.getMillis) }
  def sqlTimeToLocalTime: Time => LocalTime = { t  => new LocalTime(t, DateTimeZone.UTC) }
  
  def dateToString(date:java.util.Date, format:String = "yyyy-MM-dd HH:mm:ss"):String = {
    import java.text._
    val sdf = new SimpleDateFormat(format)
    sdf.format(date)
  }
  def stringToDate(datestr:String, format:String = "yyyy-MM-dd HH:mm:ss"):java.util.Date = {
    import java.text._
    val sdf = new SimpleDateFormat(format)
    sdf.parse(datestr)
  }
} 

//设置package包名称以及导入依赖的类
package utils.db

import java.sql.{ Date, Time, Timestamp }

import com.github.tminglei.slickpg._
import models.db.AccountRole
import play.api.libs.json.{ JsValue, Json }

trait SilverPostgresDriver extends ExPostgresDriver
    with PgArraySupport
    with PgEnumSupport
    with PgRangeSupport
    with PgDate2Support
    with PgHStoreSupport
    with PgSearchSupport
    with PgNetSupport
    with PgPlayJsonSupport
    with PgLTreeSupport {

  def pgjson = "jsonb"

  override val api = SilverAPI

  object SilverAPI extends API with ArrayImplicits
      with DateTimeImplicits
      with JsonImplicits
      with NetImplicits
      with LTreeImplicits
      with RangeImplicits
      with HStoreImplicits
      with SearchImplicits
      with SearchAssistants {

    implicit val tAccountRole = createEnumJdbcType("account_role", AccountRole)
    implicit val lAccountRole = createEnumListJdbcType("account_role", AccountRole)
    implicit val cAccountRole = createEnumColumnExtensionMethodsBuilder(AccountRole)
    implicit val oAccountRole = createEnumOptionColumnExtensionMethodsBuilder(AccountRole)

    implicit val strListTypeMapper = new SimpleArrayJdbcType[String]("text").to(_.toList)
    implicit val dateListTypeMapper = new SimpleArrayJdbcType[Date]("date").to(_.toList)

    implicit val playJsonArrayTypeMapper = new AdvancedArrayJdbcType[JsValue](
      pgjson,
      (s) => utils.SimpleArrayUtils.fromString[JsValue](Json.parse(_))(s).orNull,
      (v) => utils.SimpleArrayUtils.mkString[JsValue](_.toString())(v)
    ).to(_.toList)
  }

}

object SilverPostgresDriver extends SilverPostgresDriver 

//设置package包名称以及导入依赖的类
package models

import java.sql.{Date, Time}
import java.text.SimpleDateFormat

import play.api.libs.json._

object Tournament {

  case class Tournament(tournamentid: Int, street: String, zipcode: String, city: String, date: Date, begintime: Time,
                        endtime: Time, gettogethertime: Time, contact: Option[String], email: Option[String],
                        telefon: Option[String], web: Option[String])

  implicit object timeFormat extends Format[Time] {
    def reads(json: JsValue) = {
      val str = json.as[String]
      JsSuccess(Time.valueOf(str))
    }
    def writes(ts: Time) = JsString(ts.toString)
  }

  implicit object dateFormat extends Format[Date] {
    val format = new SimpleDateFormat("dd.MM.yyyy")

    def reads(json: JsValue) = {
      val str = json.as[String]
      JsSuccess(Date.valueOf(str))
    }

    def writes(ts: Date) = JsString(ts.toString)
  }

  implicit val userWrites = Json.writes[Tournament]
  implicit val userReads = Json.reads[Tournament]
} 

//设置package包名称以及导入依赖的类
package models

import java.sql.Time
import java.text.SimpleDateFormat
import java.sql.Date

import play.api.libs.json._

object Training {

  case class Training(trainingid: Int, street: String, zipcode: String, city: String, date: Date, begintime: Time,
                      endtime: Time, gettogethertime: Time)

  implicit object timeFormat extends Format[Time] {
    def reads(json: JsValue) = {
      val str = json.as[String]
      JsSuccess(Time.valueOf(str))
    }
    def writes(ts: Time) = JsString(ts.toString)
  }

  implicit object dateFormat extends Format[Date] {
    val format = new SimpleDateFormat("dd.MM.yyyy")
    def reads(json: JsValue) = {
      val str = json.as[String]
      JsSuccess(Date.valueOf(str))
    }
    def writes(ts: Date) = JsString(ts.toString)
  }

  implicit val userWrites = Json.writes[Training]
  implicit val userReads = Json.reads[Training]
} 

//设置package包名称以及导入依赖的类
package dao

import java.sql.{Date, Time}
import javax.inject.Inject

import com.google.inject.Singleton
import models.Tournament.Tournament
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.driver.JdbcProfile
import slick.driver.PostgresDriver.api._
import slick.lifted.TableQuery
import play.api.Play

import scala.annotation.StaticAnnotation
import scala.concurrent.Future

class TournamentTable(tag: Tag) extends Table[Tournament](tag, "tournament") {
  def tournamentid = column[Int]("tournamentid", O.PrimaryKey, O.AutoInc)
  def street = column[String]("street")
  def zipcode = column[String]("zipcode")
  def city = column[String]("city")
  def date = column[Date]("date")
  def begintime = column[Time]("begintime")
  def endtime = column[Time]("endtime")
  def gettogethertime = column[Time]("gettogethertime")
  def contact = column[Option[String]]("contact")
  def email = column[Option[String]]("email")
  def telefon = column[Option[String]]("telefon")
  def web = column[Option[String]]("web")
  def * = (tournamentid, street, zipcode, city, date, begintime, endtime, gettogethertime, contact,
    email, telefon, web) <> (Tournament.tupled, Tournament.unapply _)
}

@Singleton()
class TournamentDAO @Inject() (protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {

  private val tournaments = TableQuery[TournamentTable]

  def all(): Future[Seq[Tournament]] = db.run(tournaments.result)

  def getTournament(tournamentId: Int): Future[Option[Tournament]] = db.run(tournaments.filter(_.tournamentid === tournamentId).result.headOption)

  def deleteTournament(tournamentId: Int): Future[Int] = db.run(tournaments.filter(_.tournamentid === tournamentId).delete)

  def createTournament(tournament: Tournament): Future[Int] = {
    val query = (tournaments returning tournaments.map(_.tournamentid)) += tournament
    db.run(query)
  }

  def updateTournament(tournamentId: Int, tournament: Tournament): Future[Int] = {
    val tournamentToUpdate: Tournament = tournament.copy(tournamentId)
    db.run(tournaments.filter(_.tournamentid === tournamentId).update(tournamentToUpdate))
  }


} 

//设置package包名称以及导入依赖的类
package dao

import java.sql.{Date, Time, Timestamp}
import javax.inject.Inject

import com.google.inject.Singleton
import models.Training.Training
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.driver.JdbcProfile
import slick.driver.PostgresDriver.api._
import slick.lifted.TableQuery

import scala.concurrent.Future


class TrainingTable(tag: Tag) extends Table[Training](tag, "training") {

  def trainingid = column[Int]("trainingid", O.PrimaryKey, O.AutoInc)
  def street = column[String]("street")
  def zipcode = column[String]("zipcode")
  def city = column[String]("city")
  def date = column[Date]("date")
  def begintime = column[Time]("begintime")
  def endtime = column[Time]("endtime")
  def gettogethertime = column[Time]("gettogethertime")
  def * = (trainingid, street, zipcode, city, date, begintime, endtime, gettogethertime) <> (Training.tupled, Training.unapply _)
}

@Singleton()
class TrainingDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {

  private val trainings = TableQuery[TrainingTable]

  def all(): Future[Seq[Training]] = db.run(trainings.result)

  def getTraining(trainingId: Int): Future[Option[Training]] = db.run(trainings.filter(_.trainingid === trainingId).result.headOption)

  def deleteTraining(trainingId: Int): Future[Int] = db.run(trainings.filter(_.trainingid === trainingId).delete)

  def createTraining(training: Training): Future[Int] = {
    val query = (trainings returning trainings.map(_.trainingid)) += training
    db.run(query)
  }

  def updateTraining(trainingId: Int, training: Training): Future[Int] = {
    val trainingToUpdate: Training = training.copy(trainingId)
    db.run(trainings.filter(_.trainingid === trainingId).update(trainingToUpdate))
  }


}