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

import com.typesafe.config.ConfigFactory
import slick.driver.MySQLDriver.api._
import slick.jdbc.GetResult

import scala.concurrent.Await
import scala.concurrent.duration._

object rawSQLQuery extends App {
  ConfigFactory.invalidateCaches()
  val config = ConfigFactory.load("jdbc")
  val db = Database.forConfig("MySQLRaw", config)
  try {
    case class AssembledObject(cid: Int, aid: Int, channel: String, brand: String, title: String)
    implicit val getAssembledObjectResult = GetResult(r => AssembledObject(r.nextInt, r.nextInt,
      r.nextString, r.nextString, r.nextString))

    val rawSQL = sql"""select c.id as cid, a.id as aid, g.tag as channel, g2.tag as brand,
                              a.title from t_shorturl_channel as c
                      	  inner join t_shorturl_article as a
                      	       on c.article_id = c.article_id
                          inner join generic_tag as g
                              on c.channel_id = g.id
                          inner join generic_tag as g2
                              on c.brand_id = g2.id
                          where c.id = 58""".as[AssembledObject]

    val rows = Await.result(db.run(rawSQL), 30 seconds)
    rows foreach { row =>
      println(row)
    }
  } catch {
    case e: Throwable => println(e)
  } finally db.close
} 

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

import model._
import slick.driver.MySQLDriver.api._
import slick.jdbc.GetResult


object SqlQueries {
  implicit val searchResult = GetResult(r =>
    SearchResult(Country(r.<<[String], r.<<[String]), Airport(r.<<[String], r.<<[String]), Runway(r.<<[String], r.<<[Int])))

  implicit val countryReport = GetResult(r => CountryReport(Country(r.<<[String], r.<<[String]), r.<<[Int]))
  implicit val surfaceReport = GetResult(r => SurfaceReport(Country(r.<<[String], r.<<[String]), r.<<[String]))
  implicit val runwayReport = GetResult(r => RunwayReport(r.<<[String],r.<<[Int]))

  def findCountriesByNameOrCode(searchTerm: String) = {

    sql"""
       select DISTINCT COUNTRIES.NAME as CountryName, COUNTRIES.`CODE` as CountryCode, AIRPORT.`NAME` as AirportName,
       AIRPORT.`TYPE` as AirportType, RUNWAYS.`SURFACE` as RunwaySurface, RUNWAYS.`LIGHTED` as RunwayLighted from COUNTRIES
       join AIRPORT on AIRPORT.ISO_COUNTRY=COUNTRIES.CODE
       join RUNWAYS on RUNWAYS.`AIRPORT_IDENT`=AIRPORT.`IDENT`
       where COUNTRIES.name LIKE '#$searchTerm%' or COUNTRIES.code Like '#$searchTerm%';
  """.as[SearchResult]
  }

  def findCountriesWithAirport(sortBy: String) = {
    sql"""
         select COUNTRIES.NAME as CountryName,AIRPORT.ISO_COUNTRY as AirportCountry, COUNT(AIRPORT.ISO_COUNTRY) as count from AIRPORT
         JOIN COUNTRIES ON COUNTRIES.`CODE`=AIRPORT.`ISO_COUNTRY`
         GROUP BY AIRPORT.`ISO_COUNTRY`, COUNTRIES.NAME
         order by count #$sortBy, CountryName
         LIMIT 10;
    """.as[CountryReport]
  }

  def findCountryWithRunwaySurface = {
    sql"""
         select DISTINCT COUNTRIES.NAME as CountryName,COUNTRIES.`CODE` as CountryCode,  RUNWAYS.`SURFACE` as RunwaySurface from COUNTRIES
         join AIRPORT on AIRPORT.ISO_COUNTRY=COUNTRIES.CODE
         join RUNWAYS on RUNWAYS.`AIRPORT_IDENT`=AIRPORT.`IDENT`;
    """.as[SurfaceReport]
  }

  def findCommonRunwayIdents = {
    sql"""
         select RUNWAYS.`LE_IDENT` as RunwayIdent, COUNT(RUNWAYS.`LE_IDENT`) as count from RUNWAYS
         GROUP BY RUNWAYS.`LE_IDENT`
         ORDER BY count DESC
         limit 10;
    """.as[RunwayReport]
  }
} 

//设置package包名称以及导入依赖的类
package im.actor.server.persist

import slick.driver.PostgresDriver.api._
import im.actor.server.model.GroupMemberDTO
import slick.jdbc.GetResult

/**
 * Created by User on 2017/2/23.
 */
object GroupMemberDTORepo {

  def findByGroupId(groupId: Int) = {
    implicit val getMessageResult: GetResult[GroupMemberDTO] = GetResult(r ?
      GroupMemberDTO(
        memberId = r.nextInt(),
        memberName = r.nextString(),
        isAdmin = r.nextBoolean()
      ))

    sql"""SELECT u.id,u.name,gu.is_admin FROM group_users gu INNER JOIN users u ON gu.user_id=u.id WHERE gu.group_id=$groupId""".as[GroupMemberDTO]
  }

} 

//设置package包名称以及导入依赖的类
package im.actor.server.persist

import slick.driver.PostgresDriver.api._
import im.actor.server.model.GroupDTO
import slick.jdbc.GetResult

/**
 * Created by User on 2017/2/23.
 */
object GroupDTORepo {

  def findById(groupId: Int) = {
    implicit val getMessageResult: GetResult[GroupDTO] = GetResult(r ?
      GroupDTO(
        groupId = r.nextInt(),
        groupName = r.nextString(),
        creatorUserId = r.nextInt(),
        about = r.nextString()
      ))

    sql"""SELECT id,title,creator_user_id,about FROM groups where id=$groupId""".as[GroupDTO].headOption
  }

} 

//设置package包名称以及导入依赖的类
package im.actor.server.persist

import com.github.tototoshi.slick.PostgresJodaSupport._
import slick.driver.PostgresDriver.api._
import slick.jdbc.GetResult
import im.actor.server.model.HistoryMessagePatches

/**
 * Created by User on 2017/2/27.
 */
object HistoryMessageRepoPatches {

  def loadHistoryMessages(peerId: Int, historyOwner: Int, userId: Int, date: String, limit: Int) = {
    implicit val getMessageResult: GetResult[HistoryMessagePatches] = GetResult(r ?
      HistoryMessagePatches(
        userId = r.nextInt,
        peerType = r.nextInt,
        peerId = r.nextInt,
        date = getDatetimeResult(r),
        senderUserId = r.nextInt,
        randomId = r.nextLong,
        messageContentHeader = r.nextInt,
        messageContentData = r.nextBytes,
        deletedAt = getDatetimeOptionResult(r),
        senderName = r.nextString(),
        senderNickName = r.nextString,
        isOut = r.nextBoolean
      ))

    val sb: StringBuilder = new StringBuilder(1024)
    sb.append("SELECT m.*,u.name as sender_name,u.nickname,(")
    sb.append(userId).append("=m.sender_user_id) as is_out ")
    sb.append("FROM history_messages m INNER JOIN users u ON m.sender_user_id=u.id ")
    sb.append("WHERE m.peer_id=").append(peerId).append(" AND m.date<'").append(date).append("'")
    if (historyOwner > 0) {
      sb.append(" AND m.user_id=").append(userId)
    }
    sb.append(" ORDER BY date DESC LIMIT ").append(limit)
    val querySql = sb.toString()
    sql"""#$querySql""".as[HistoryMessagePatches]
  }

  def groupIsShared(groupId: Int) = {
    sql"""SELECT is_share FROM groups WHERE id=$groupId""".as[Boolean].headOption
  }

} 

//设置package包名称以及导入依赖的类
package de.twentyone.slick.mappings

import slick.jdbc.{GetResult, PositionedParameters, PositionedResult, SetParameter}

trait PureMappingSupport {
  protected def getResultPure[A](func: PositionedResult => A): GetResult[A] = new GetResult[A] {
    override def apply(positionedResult: PositionedResult): A = {
      func(positionedResult)
    }
  }
  protected def setParameterPure[A](func: (A, PositionedParameters) => Unit): SetParameter[A] =
    new SetParameter[A] {
      override def apply(value: A, positionedParameters: PositionedParameters): Unit =
        func(value, positionedParameters)
    }
} 

//设置package包名称以及导入依赖的类
package de.twentyone.slick.mappings

import java.sql.Timestamp
import java.time.Instant

import slick.jdbc.JdbcProfile
import slick.jdbc.{GetResult, PositionedParameters, PositionedResult, SetParameter}

trait JavaInstantMappings {

  protected val driver: JdbcProfile

  import driver.api._

  // scalastyle:off
  private def instantToTimeStamp(instant: Instant) =
    if (instant == null) null else new Timestamp(instant.toEpochMilli)

  private def timeStampToInstant(timestamp: Timestamp) =
    if (timestamp == null) null else timestamp.toInstant
  // scalastyle:on

  protected implicit val instantMappedColumnType: JdbcProfile#BaseColumnType[Instant] =
    MappedColumnType.base[Instant, Timestamp](
      d => instantToTimeStamp(d),
      d => timeStampToInstant(d)
    )

  protected implicit val instantGetResult: GetResult[Instant] = new GetResult[Instant] {
    override def apply(positionedResult: PositionedResult): Instant =
      timeStampToInstant(positionedResult.nextTimestamp())
  }

  protected implicit val instantOptionGetResult: GetResult[Option[Instant]] =
    new GetResult[Option[Instant]] {
      override def apply(positionedResult: PositionedResult): Option[Instant] =
        positionedResult.nextTimestampOption().map(timestamp => timeStampToInstant(timestamp))
    }

  protected implicit val instantSetParameter: SetParameter[Instant] = new SetParameter[Instant] {
    override def apply(instant: Instant, positionedParameter: PositionedParameters): Unit =
      positionedParameter.setTimestamp(instantToTimeStamp(instant))
  }

  protected implicit val instantOptionSetParameter: SetParameter[Option[Instant]] =
    new SetParameter[Option[Instant]] {
      override def apply(instantOption: Option[Instant],
                         positionedParameter: PositionedParameters): Unit =
        positionedParameter.setTimestampOption(
          instantOption.map(instant => instantToTimeStamp(instant)))
    }
} 

//设置package包名称以及导入依赖的类
package de.twentyone.slick.mappings

import slick.jdbc.{GetResult, JdbcProfile, JdbcType, SetParameter}

import scala.reflect.ClassTag

class AnyValMapping[T <: AnyVal, V](anyValApply: (V => T), anyValUnapply: (T => Option[V]))(
    implicit val driver: JdbcProfile,
    innerGetResult: GetResult[V],
    innerOptionGetResult: GetResult[Option[V]],
    innerSetParameter: SetParameter[V],
    optionInnerSetParameter: SetParameter[Option[V]],
    classTag: ClassTag[T])
    extends PureMappingSupport {

  val getResult: GetResult[T] = getResultPure { positionedResult =>
    anyValApply(innerGetResult(positionedResult))
  }

  val optionGetResult: GetResult[Option[T]] = getResultPure { positionedResult =>
    innerOptionGetResult(positionedResult).map(anyValApply)
  }

  val setParameter: SetParameter[T] = setParameterPure { (value, positionedParameters) =>
    innerSetParameter.apply(anyValUnapply(value).get, positionedParameters)
  }

  val optionSetParameter: SetParameter[Option[T]] = setParameterPure {
    (value, positionedParameters) =>
      optionInnerSetParameter.apply(value.flatMap(anyValUnapply), positionedParameters)
  }

  import driver.api._

  def mappedColumnType(implicit baseColumnType: BaseColumnType[V]): JdbcType[T] =
    MappedColumnType.base[T, V](t => anyValUnapply(t).get, s => anyValApply(s))
} 

//设置package包名称以及导入依赖的类
package de.twentyone.slick.mappings

import slick.jdbc.JdbcProfile
import slick.jdbc.{GetResult, SetParameter}

class EnumMappings[T <: Enumeration](enu: T)(implicit val driver: JdbcProfile)
    extends PureMappingSupport {
  val getResult: GetResult[T#Value] = getResultPure { positionedResult =>
    enu.withName(positionedResult.nextString())
  }

  val optionGetResult: GetResult[Option[T#Value]] = getResultPure { positionedResult =>
    positionedResult.nextStringOption().map(enu.withName)
  }

  val setParameter: SetParameter[T#Value] = setParameterPure { (value, positionedParameters) =>
    positionedParameters.setString(value.toString)
  }

  val optionSetParameter: SetParameter[Option[T#Value]] = setParameterPure {
    (value, positionedParameters) =>
      positionedParameters.setStringOption(value.map(v => value.toString))
  }

  import driver.api._

  val mappedColumnType = MappedColumnType.base[T#Value, String](_.toString, s => enu.withName(s))
} 

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

import java.sql.Timestamp
import java.text.SimpleDateFormat

import play.api.libs.json._
import play.api.libs.json.Json
import slick.jdbc.GetResult

case class Sample (id: Option[Long],
                   temperature: Float,
                   humidity: Float,
                   windDirection: String,
                   windSpeed: Float,
                   rainLevel: Float,
                   clocked: Timestamp){
}

object Sample {
  implicit val getSampleResult = GetResult(r => Sample(r.nextLongOption(), r.nextFloat(), r.nextFloat(),
    r.nextString(), r.nextFloat(), r.nextFloat(), r.nextTimestamp()))

  implicit object timestampFormat extends Format1653281767 {
    val format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")

    def reads(json: JsValue) = {
      val str = json.as[String]
      JsSuccess(new Timestamp(format.parse(str).getTime))
    }

    def writes(ts: Timestamp) = JsString(format.format(ts))
  }
  implicit object tempAndClockedFormat extends Writes[Seq[Sample]] {
    val format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
    def writes(samples: Seq[Sample]) = {
      val seq = samples.map(sample => {
        Json.obj(
          "temperature" -> JsString(sample.temperature.toString),
          "windSpeed" -> JsString(sample.windSpeed.toString),
          "humidity" -> JsString(sample.humidity.toString),
          "clocked" -> JsString(format.format(sample.clocked)),
          "rainLevel" -> JsString(format.format(sample.rainLevel))
        )
      })
      JsArray(seq)
    }
  }
  implicit val sampleReads = Json.reads[Sample]
  implicit val sampleWrites = Json.writes[Sample]

} 

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

import java.sql.Timestamp
import java.util.UUID

import com.google.inject.{Inject, Singleton}
import org.joda.time.DateTime
import play.api.db.slick.DatabaseConfigProvider
import slick.driver.JdbcProfile
import slick.jdbc.{GetResult, PositionedParameters, SetParameter}

import scala.concurrent.ExecutionContext

case class AccountDb(id: Option[Long] = None, accountId: UUID, accountHolder: String, createdOn: DateTime)

@Singleton
class AccountRepository @Inject() (val dbConfigProvider: DatabaseConfigProvider)(implicit ec: ExecutionContext) {


  val dbConfig = dbConfigProvider.get[JdbcProfile]

  import dbConfig.driver.api._

  implicit val accountDb = GetResult(r => AccountDb(
    Some(r.nextInt()),
    UUID.fromString(r.nextString()),
    r.nextString(),
    new DateTime(r.nextTimestamp().getTime)))

  implicit object SetDateTime extends SetParameter[DateTime] {
    override def apply(v: DateTime, pp: PositionedParameters): Unit = {
      pp.setTimestamp(new Timestamp(v.getMillis))
    }
  }

  implicit object SetUUID extends SetParameter[UUID] {
    override def apply(v: UUID, pp: PositionedParameters): Unit = {
      pp.setString(v.toString)
    }
  }

  def create(db: AccountDb): DBIO[AccountDb] = {
    sqlu"""insert into "accounts" values (null, ${db.accountId}, ${db.accountHolder}, ${db.createdOn})"""
      .flatMap { _ => sql"SELECT LASTVAL()".as[Int].head }
      .map(id => db.copy(id = Some(id)))
  }

  def getAccounts: DBIO[Seq[AccountDb]] = sql"""SELECT * FROM "accounts" """.as[AccountDb]

} 

//设置package包名称以及导入依赖的类
import slick.driver.SQLiteDriver.api._
import slick.jdbc.GetResult

import scala.concurrent.Await
import scala.concurrent.duration.Duration
import scala.concurrent.ExecutionContext.Implicits.global


object Main {
  def main(args: Array[String]): Unit = {
    val db = Database.forConfig("sqliteconf")

    val name = "????"

    try {
      Await.result(
        db.run(
          sql"""SELECT * FROM artist WHERE name = ${name} LIMIT 1""".as[Artist]
        ).map(println),
        Duration.Inf
      )
    } finally db.close
  }

  implicit val userGetResult = GetResult(r => Artist(r.<<, r.<<, r.<<))
} 

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

import models.{CommentId, CommentLike, User, UserId}
import org.joda.time.DateTime
import repositories.Models.CommentDbModel
import slick.jdbc.GetResult

object RepositoryResultMappers {

  implicit val commentResult = GetResult(r => CommentDbModel(
    id = CommentId(r.nextLong),
    user = UserId(r.nextLong),
    repositoryOwner = r.nextString,
    repositoryName = r.nextString,
    comment = r.nextString,
    createdOn = new DateTime(r.nextTimestamp())))

  implicit val userResult = GetResult(r => User(id = UserId(r.nextLong()), username = r.nextString()))

  implicit val commentsWithUserResult = GetResult(r =>
    (commentResult(r), userResult(r)))

  implicit val commentLikeResult = GetResult(r => CommentLike(
    r.nextLong(), CommentId(r.nextLong()), UserId(r.nextLong())))

  implicit val commentIdResult = GetResult(r => CommentId(r.nextLong()))
} 

//设置package包名称以及导入依赖的类
package com.packt.masteringakka.bookstore.user

import com.packt.masteringakka.bookstore.common.EntityRepository
import scala.concurrent.ExecutionContext
import slick.jdbc.GetResult

object BookstoreUserRepository{  
  val SelectFields = "select id, firstName, lastName, email, createTs, modifyTs from StoreUser " 
  implicit val GetUser = GetResult{r => BookstoreUserFO(r.<<, r.<<, r.<<, r.<<, r.nextTimestamp, r.nextTimestamp)}  
}


  def updateUserInfo(user:BookstoreUserFO) = {
    val update = sqlu"""
      update StoreUser set firstName = ${user.firstName}, 
      lastName = ${user.lastName}, email = ${user.email} where id = ${user.id}  
    """
    db.run(update)
  } 
}