struct PublicUser: Codable {
    var name: String
    var petName: String
    var petType: String
    var petToysQuantity: Int
}
try FQL()
    .select(all: User.self)
    .select(\Pet.name, as: "petName")
    .select(\PetType.name, as: "petType")
    .select(.count(\PetToy.id), as: "petToysQuantity")
    .from(User.self)
    .join(.left, Pet.self, where: \Pet.id == \User.idPet)
    .join(.left, PetType.self, where: \PetType.id == \Pet.idType)
    .join(.left, PetToy.self, where: \PetToy.idPet == \Pet.id)
    .groupBy(\User.id, \Pet.id, \PetType.id, \PetToy.id)
    .execute(on: conn)
    .decode(PublicUser.self) // -> Future<[PublicUser]> 🔥🔥🔥

//add this repo to dependencies
.package(url: "https://github.com/MihaelIsaev/FluentQuery.git", from: "0.4.30")
//and don't forget about targets
//"FluentQuery"

import FluentQuery

let query = FQL()
//some building
print("rawQuery: \(query)")

// SELECT * FROM "User" WHERE age > 18
let fql = FQL().select(all: User.self)
               .from(User.self)
               .where(\User.age > 18)
               .execute(on: conn)
               .decode(User.self)

// SELECT u.*, r.name as region FROM "User" as u WHERE u.age > 18 LEFT JOIN "UserRegion" as r ON u.idRegion = r.id
let fql = FQL().select(all: User.self)
               .select(\UserRegion.name)
               .from(User.self)
               .where(\User.age > 18)
               .join(.left, UserRegion.self, where: \User.idRegion == \UserRegion.id)
               .execute(on: conn)
               .decode(UserWithRegion.self)

// SELECT (SELECT to_jsonb(u)) as user, (SELECT to_jsonb(r)) as region FROM "User" as u WHERE u.age > 18 LEFT JOIN "UserRegion" as r ON u.idRegion = r.id
let fql = FQL().select(.row(User.self), as: "user")
               .select(.row(UserRegion.self), as: "region")
               .from(User.self)
               .where(\User.age > 18)
               .join(.left, UserRegion.self, where: \User.idRegion == \UserRegion.id)
               .execute(on: conn)
               .decode(UserWithRegion.self)
// in this case UserWithRegion struct will look like this
struct UserWithRegion: Codable {
    var user: User
    var region: UserRegion
}

final class Car: Model {
  var id: UUID?
  var year: String
  var color: String
  var engineCapacity: Double
  var idBrand: UUID
  var idModel: UUID
  var idBodyType: UUID
  var idEngineType: UUID
  var idGearboxType: UUID
}

final class Brand: Decodable {
  var id: UUID?
  var value: String
}
final class Model: Decodable {
  var id: UUID?
  var value: String
}
final class BodyType: Decodable {
  var id: UUID?
  var value: String
}
final class EngineType: Decodable {
  var id: UUID?
  var value: String
}
final class GearboxType: Decodable {
  var id: UUID?
  var value: String
}

struct PublicCar: Content {
  var id: UUID
  var year: String
  var color: String
  var engineCapacity: Double
  var brand: Brand
  var model: Model
  var bodyType: BodyType
  var engineType: EngineType
  var gearboxType: GearboxType
}

func getListOfCars(_ req: Request) throws -> Future<[PublicCar]> {
  return req.requestPooledConnection(to: .psql).flatMap { conn -> EventLoopFuture<[PublicCar]> in
      defer { try? req.releasePooledConnection(conn, to: .psql) }
      return FQL()
        .select(distinct: \Car.id)
        .select(\Car.year, as: "year")
        .select(\Car.color, as: "color")
        .select(\Car.engineCapacity, as: "engineCapacity")
        .select(.row(Brand.self), as: "brand")
        .select(.row(Model.self), as: "model")
        .select(.row(BodyType.self), as: "bodyType")
        .select(.row(EngineType.self), as: "engineType")
        .select(.row(GearboxType.self), as: "gearboxType")
        .from(Car.self)
        .join(.left, Brand.self, where: \Brand.id == \Car.idBrand)
        .join(.left, Model.self, where: \Model.id == \Car.idModel)
        .join(.left, BodyType.self, where: \BodyType.id == \Car.idBodyType)
        .join(.left, EngineType.self, where: \EngineType.id == \Car.idEngineType)
        .join(.left, GearboxType.self, where: \GearboxType.id == \Car.idGearboxType)
        .groupBy(\Car.id, \Brand.id, \Model.id, \BodyType.id, \EngineType.id, \GearboxType.id)
        .orderBy(.asc(\Brand.value), .asc(\Model.value))
        .execute(on: conn)
        .decode(PublicCar.self)
  }
}

let fqo = FQOver(.partition)
            .by(\Record.title, \Record.tag)
            .orderBy(.asc(\Record.priority))

let FQL()
    .select(\Record.id)
    .select(by: .rowNumber, over: fqo, as: "rowNumber")
    .from(Record.self)

enum FQJoinMode {
    case left, right, inner, outer
}

FQWhere(predicate).and(predicate).or(predicate).and(FQWhere).or(FQWhere)

\User.email == "[email protected]" && \User.password == "qwerty" && \User.active == true

\User.email == "[email protected]" || \User.email == "[email protected]" || \User.email == "[email protected]"

\User.email == "[email protected]" && FQWhere(\User.role == .admin || \User.role == .staff)

FQWhere(someAlias.k(\.deletedAt) == nil)
FQWhere(someAlias.k(\.id) == 12).and(\Car.color ~~ ["blue", "red", "white"])
FQWhere(\Car.year == "2018").and(\Brand.value !~ ["Chevrolet", "Toyota"])
FQWhere(\Car.year != "2005").and(someAlias.k(\.engineCapacity) > 1.6)

FQWhere(\Car.engineCapacity > 1.6).and(FQWhere(\Brand.value ~~ "YO").or(\Brand.value ~= "ET"))

.having(FQWhere(.count(\Car.id) > 0))
//OR
.having(FQWhere(.count(someAlias.k(\.id)) > 0))
//and of course you an use .and().or().groupStart().groupEnd()

.groupBy(\Car.id, \Brand.id, \Model.id)

.groupBy(FQGroupBy(\Car.id).and(\Brand.id).and(\Model.id))

let groupBy = FQGroupBy(\Car.id)
groupBy.and(\Brand.id)
groupBy.and(\Model.id)
.groupBy(groupBy)

.orderBy(FQOrderBy(\Car.year, .asc).and(someAlias.k(\.name), .desc))

.orderBy(.asc(\Car.year), .desc(someAlias.k(\.name)))

FQJSON(.binary).field("brand", \Brand.value).field("model", someAlias.k(\.value))

//"CarBrand" as b
let aliasBrand = CarBrand.alias("b")
//"CarModel" as m
let aliasModel = CarModel.alias("m")
//"EngineType" as e
let aliasEngineType = EngineType.alias("e")

aliasBrand.k(\.id)
aliasBrand.k(\.value)
aliasModel.k(\.id)
aliasModel.k(\.value)
aliasEngineType.k(\.id)
aliasEngineType.k(\.value)

try FQL().select(all: User.self).execute(on: conn)

try FQL().select(all: User.self).execute(on: conn).decode(PublicUser.self)

try FQL().select(all: User.self).execute(on: conn).decode(PublicUser.self, dateDecodingStrategy: .secondsSince1970)

let formatter = DateFormatter()
formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
try FQL().select(all: User.self).execute(on: conn).decode(PublicUser.self, dateDecodingStrategy: .formatted(formatter))