SLICK1

import scala.concurrent.{Await, Future}
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.Duration
import slick.basic.DatabasePublisher
import slick.dbio.Effect
import slick.jdbc.H2Profile.api._
import slick.lifted.QueryBase
import slick.sql.FixedSqlAction

object HelloSlick1 extends App{
  val zaFilter = RandomCaseClass(None,None)

  val db = Database.forConfig("h2mem1")
  try {

    // The query interface for the Suppliers table
    val suppliers: TableQuery[Suppliers] = TableQuery[Suppliers]

    // the query interface for the Coffees table
    val coffees: TableQuery[Coffees] = TableQuery[Coffees]

    val setupAction: DBIO[Unit] = DBIO.seq(
      // Create the schema by combining the DDLs for the Suppliers and Coffees
      // tables using the query interfaces
      (suppliers.schema ++ coffees.schema).create,

      // Insert some suppliers
      suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199"),
      suppliers += ( 49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460"),
      suppliers += (150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966"),
      suppliers += (200, "Kafe Uvoz izvoz", "Pero nakov bb", "Przino", "MK", "1050")
    )

    val setupFuture: Future[Unit] = db.run(setupAction)
    val f = setupFuture.flatMap { _ =>

      // Insert some coffees (using JDBC's batch insert feature)
      val insertAction:DBIO[Option[Int]]= coffees ++= Seq (
        ("Colombian",         101, 7.99, 0, 0),
        ("French_Roast",       150, 8.99, 0, 0),
        ("Espresso",          150, 15.99, 0, 0),
        ("Colombian_Decaf",   101, 8.99, 0, 0),
        ("French_Roast_Decaf", 49, 9.99, 0, 0),
        ("Grand", 200, 20.99, 0, 0),
        ("Kafe_C", 200, 30.99, 0, 0)
      )

      db.run(insertAction)

    }.flatMap{ _ =>
        println(suppliers.result.statements.mkString)
      db.run(suppliers.result)
    }.flatMap{ _ =>
      val kveri: Query[Rep[String], String, Seq] = coffees
        .filter(cof=> cof.supID === 101)
        .map(x=>x.name)
      println(kveri.result.statements.mkString)
      db.run(kveri.result)
    }.flatMap { _ =>      //site kafinja da gi updatenam shto go sodrzat imeto Colombian so toa shto cenata kje im ja smenam UPDATE!!!

      val updateQuery = coffees.filter(_.name.like("Colombian%")).map(_.price)

      val updateAction: DBIO[Int] = updateQuery.update(9999)

      db.run(updateAction)

    }.flatMap{ _ =>

      val i = coffees

      db.run(i.result)


    }.flatMap{ _ =>        //da gi izbrisham site kafinja na koi shto cenata im e 9999 DELETE!!!
      val zaBrishenje = coffees.filter(_.price === 9999.0)

      val deleteAction: FixedSqlAction[Int, NoStream, Effect.Write] =zaBrishenje.delete
      db.run(deleteAction)
    }.flatMap{_ =>
      val c= coffees
      db.run(c.result)

    }.flatMap{ _ =>     // da dodadam ushte edno kafe vo kafinjata     INSERT!!!
      val insertActions = DBIO.seq(
        coffees += ("Bravo", 101, 99.99, 0, 0))

      db.run(insertActions)

    }.flatMap { _ =>

      db.run(coffees.result)

    }.flatMap { _ =>              //SO FOR JOIN DA NAPRAAM NA COFFEE I NA SUPPLIERS
      val joinQuery = for {
        (c,s) <- coffees join suppliers
      } yield (c.name, s.name)

      db.run(joinQuery.result)
    }.flatMap{ _ =>                //BEZ FOR JOIN NA COFFEE I NA SUPPLIERS I SAMO IMETO NA KAFETO DA GI ZEMAM I IMETO NA SUPPLIEROT
      val q= coffees join suppliers on (_.supID === _.id)
      val p=q.map{ case (kafe, suplier) =>
        (kafe.name, suplier.name)
      }

      db.run(p.result)
    }.flatMap{_ =>          // FilterIf i FilterOpt upotreba(za da nemam eden kup if-ovi od site moznosti na some-none, none-none....)
      val ime:Option[String] =Some("Espresso")
      val supId:Option[Int] = None//Some(150)
      val temp = 300
      val temp2 = 150

      //val p = coffees.filter(_.name === ime.get)  //get iako e nebezbedno tuka e taman zashto proveruvam dali e NONE
      val xx = coffees
        .filterOpt(ime){case (cof,i) => cof.name === i }
        .filterOpt(supId){ case (cof,sId) => cof.supID === sId}
        .filterIf(temp-temp2 == 150) { c=>c.supID === 49}
      println(xx.result.statements.mkString)
      db.run(xx.result)
    }.flatMap { _ =>

      db.run((coffees.groupBy(_.supID).map{ case (sId, cof) => sId -> cof.map{x => x.price}.avg }).result)

    }.flatMap { _ =>  //da gi grupiram site supp po ime i da vratam kolku kafinja ima sekoj od niv(INT)
      val q = coffees.join(suppliers).on(_.supID === _.id) //ova vrakja dve tabeli
        .groupBy{ case (coffees, sups) => sups.name }.map{ case (supName, group) =>
          supName -> group.map{
            case(cof, sup) => cof.name
          }.length
        }
      db.run(q.result)
    }.flatMap{_ =>
      val q = coffees.join(suppliers).on(_.supID === _.id)//zaradi toa shto ova vrakja tuple od dvete tabeli
          .map{ case (coffee, supp) => coffee.name}//vaka go zemam samo imeto na kafeto
      db.run(q.result)

    }.flatMap{ _ => // mozam i klasichen SQL DA SI NAPISHAM na sledniov nachin !!
      val state = "CA"

      val plainQuery = sql"select SUP_NAME from SUPPLIERS where STATE = $state".as[String]
      db.run(plainQuery)
    }


      /*.flatMap { _ =>

        /* Streaming */

        val coffeeNamesAction: StreamingDBIO[Seq[String], String] =
          coffees.map(_.name).result

        val coffeeNamesPublisher: DatabasePublisher[String] =
          db.stream(coffeeNamesAction)

        coffeeNamesPublisher.foreach(println)

      }*/

    val jj = Await.result(f, Duration.Inf)
    println(jj)

  } finally db.close
}