import XCTest
import GRDB

private struct Team: Codable, FetchableRecord, PersistableRecord {
    static let players = hasMany(Player.self)
    static let awards = hasMany(Award.self)
    static let customPlayers = hasMany(Player.self, key: "customPlayers")
    var id: Int64
    var name: String
}

private struct Player: Codable, FetchableRecord, PersistableRecord {
    static let awards = hasMany(Award.self, through: belongsTo(Team.self), using: Team.awards)
    var id: Int64
    var teamId: Int64?
    var name: String
    var score: Int
}

private struct Award: Codable, FetchableRecord, PersistableRecord {
    var customPrimaryKey: Int64
    var teamId: Int64?
    var name: String
}

private struct TeamInfo: Decodable, FetchableRecord {
    var team: Team
    var averagePlayerScore: Double?
    var playerCount: Int?
    var maxPlayerScore: Int?
    var minPlayerScore: Int?
    var playerScoreSum: Int?
}

private struct CustomTeamInfo: Decodable, FetchableRecord {
    var team: Team
    var averageCustomPlayerScore: Double?
    var customPlayerCount: Int?
    var maxCustomPlayerScore: Int?
    var minCustomPlayerScore: Int?
    var customPlayerScoreSum: Int?
}

class AssociationAggregateTests: GRDBTestCase {
    
    override func setup(_ dbWriter: some DatabaseWriter) throws {
        try dbWriter.write { db in
            try db.create(table: "team") { t in
                t.primaryKey("id", .integer)
                t.column("name", .text)
            }
            try db.create(table: "player") { t in
                t.primaryKey("id", .integer)
                t.belongsTo("team")
                t.column("name", .text)
                t.column("score", .integer)
            }
            try db.create(table: "award") { t in
                t.primaryKey("customPrimaryKey", .integer)
                t.belongsTo("team")
                t.column("name", .text)
            }

            try Team(id: 1, name: "Reds").insert(db)
            try Player(id: 1, teamId: 1, name: "Arthur", score: 100).insert(db)
            try Player(id: 2, teamId: 1, name: "Barbara", score: 1000).insert(db)
            try Award(customPrimaryKey: 1, teamId: 1, name: "World cup 2035").insert(db)
            try Award(customPrimaryKey: 2, teamId: 1, name: "World cup 2038").insert(db)
            try Award(customPrimaryKey: 3, teamId: 1, name: "European cup 2038").insert(db)
            try Team(id: 2, name: "Blues").insert(db)
            try Player(id: 3, teamId: 2, name: "Craig", score: 200).insert(db)
            try Player(id: 4, teamId: 2, name: "David", score: 500).insert(db)
            try Player(id: 5, teamId: 2, name: "Elise", score: 800).insert(db)
            try Award(customPrimaryKey: 4, teamId: 2, name: "European cup 2036").insert(db)
            try Team(id: 3, name: "Greens").insert(db)
            try Award(customPrimaryKey: 5, teamId: 3, name: "World cup 2037").insert(db)
            try Team(id: 4, name: "Oranges").insert(db)
            try Player(id: 6, teamId: 4, name: "Fiona", score: 0).insert(db)
        }
    }
    
    func testAggregateWithJoiningMethodAndTableAliasAndSQLSnippet() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let tableAlias = TableAlias(name: "custom")
            let request = Team
                .annotated(with: Team.players.count)
                .joining(required: Team.players.aliased(tableAlias).filter(sql: "custom.score < ?", arguments: [500]))
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, COUNT(DISTINCT "custom"."id") AS "playerCount" \
                FROM "team" \
                JOIN "player" "custom" ON ("custom"."teamId" = "team"."id") AND (custom.score < 500) \
                GROUP BY "team"."id"
                """)
        }
    }
    
    func testAggregateWithGroup() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .select(Column("name"))
                .annotated(with: Team.players.count)
                .group(Column("name"))
            
            try assertEqualSQL(db, request, """
                SELECT "team"."name", COUNT(DISTINCT "player"."id") AS "playerCount" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."name"
                """)
        }
    }

    func testAnnotatedWithHasManyDefaultAverage() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.average(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, AVG("player"."score") AS "averagePlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].averagePlayerScore, 550)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].averagePlayerScore, 500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].averagePlayerScore)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].averagePlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyDefaultCount() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.count)
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, COUNT(DISTINCT "player"."id") AS "playerCount" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].playerCount, 2)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].playerCount, 3)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertEqual(teamInfos[2].playerCount, 0)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].playerCount, 1)
        }
    }
    
    func testAnnotatedWithHasManyThroughDefaultCount() throws {
        struct PlayerInfo: Decodable, FetchableRecord {
            var player: Player
            var awardCount: Int
        }
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Player
                .annotated(with: Player.awards.count)
                .orderByPrimaryKey()
                .asRequest(of: PlayerInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "player".*, COUNT(DISTINCT "award"."customPrimaryKey") AS "awardCount" \
                FROM "player" \
                LEFT JOIN "team" ON "team"."id" = "player"."teamId" \
                LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                GROUP BY "player"."id" \
                ORDER BY "player"."id"
                """)
            
            let playerInfos = try request.fetchAll(db)
            XCTAssertEqual(playerInfos.count, 6)
            
            XCTAssertEqual(playerInfos[0].player.id, 1)
            XCTAssertEqual(playerInfos[0].awardCount, 3)
            
            XCTAssertEqual(playerInfos[1].player.id, 2)
            XCTAssertEqual(playerInfos[1].awardCount, 3)
            
            XCTAssertEqual(playerInfos[2].player.id, 3)
            XCTAssertEqual(playerInfos[2].awardCount, 1)
            
            XCTAssertEqual(playerInfos[3].player.id, 4)
            XCTAssertEqual(playerInfos[3].awardCount, 1)
            
            XCTAssertEqual(playerInfos[4].player.id, 5)
            XCTAssertEqual(playerInfos[4].awardCount, 1)
            
            XCTAssertEqual(playerInfos[5].player.id, 6)
            XCTAssertEqual(playerInfos[5].awardCount, 0)
        }
    }

    func testAnnotatedWithHasManyDefaultMax() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.max(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, MAX("player"."score") AS "maxPlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].maxPlayerScore, 1000)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].maxPlayerScore, 800)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].maxPlayerScore)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].maxPlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyDefaultMaxJoiningRequired() throws {
        // It is important to have an explicit test for this technique because
        // it is the only currently available that forces a JOIN, and we don't
        // want to break it in the future, even if association aggregates
        // change implementation eventually.

        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.max(Column("score")))
                .joining(required: Team.players) // <- the tested technique
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, MAX("player"."score") AS "maxPlayerScore" \
                FROM "team" \
                JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            // No result with nil maxPlayerScore thanks to the inner join
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 3)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].maxPlayerScore, 1000)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].maxPlayerScore, 800)
            
            XCTAssertEqual(teamInfos[2].team.id, 4)
            XCTAssertEqual(teamInfos[2].team.name, "Oranges")
            XCTAssertEqual(teamInfos[2].maxPlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyDefaultMin() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.min(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, MIN("player"."score") AS "minPlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].minPlayerScore, 100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].minPlayerScore, 200)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].minPlayerScore)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].minPlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyDefaultSum() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.sum(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, SUM("player"."score") AS "playerScoreSum" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].playerScoreSum, 1100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].playerScoreSum, 1500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].playerScoreSum)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].playerScoreSum, 0)
        }
    }
    
    func testAnnotatedWithHasManyDefaultTotal() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.total(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, TOTAL("player"."score") AS "playerScoreSum" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].playerScoreSum, 1100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].playerScoreSum, 1500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertEqual(teamInfos[2].playerScoreSum, 0)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].playerScoreSum, 0)
        }
    }
    
    func testAnnotatedWithHasManyMultipleDefaultAggregates() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.average(Column("score")))
                .annotated(with: Team.players.count)
                .annotated(with: Team.players.min(Column("score")), Team.players.max(Column("score")))
                .annotated(with: Team.players.sum(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, \
                AVG("player"."score") AS "averagePlayerScore", \
                COUNT(DISTINCT "player"."id") AS "playerCount", \
                MIN("player"."score") AS "minPlayerScore", \
                MAX("player"."score") AS "maxPlayerScore", \
                SUM("player"."score") AS "playerScoreSum" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].averagePlayerScore, 550)
            XCTAssertEqual(teamInfos[0].playerCount, 2)
            XCTAssertEqual(teamInfos[0].maxPlayerScore, 1000)
            XCTAssertEqual(teamInfos[0].minPlayerScore, 100)
            XCTAssertEqual(teamInfos[0].playerScoreSum, 1100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].averagePlayerScore, 500)
            XCTAssertEqual(teamInfos[1].playerCount, 3)
            XCTAssertEqual(teamInfos[1].maxPlayerScore, 800)
            XCTAssertEqual(teamInfos[1].minPlayerScore, 200)
            XCTAssertEqual(teamInfos[1].playerScoreSum, 1500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].averagePlayerScore)
            XCTAssertEqual(teamInfos[2].playerCount, 0)
            XCTAssertNil(teamInfos[2].maxPlayerScore)
            XCTAssertNil(teamInfos[2].minPlayerScore)
            XCTAssertNil(teamInfos[2].playerScoreSum)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].averagePlayerScore, 0)
            XCTAssertEqual(teamInfos[3].playerCount, 1)
            XCTAssertEqual(teamInfos[3].maxPlayerScore, 0)
            XCTAssertEqual(teamInfos[3].minPlayerScore, 0)
            XCTAssertEqual(teamInfos[3].playerScoreSum, 0)
        }
    }
    
    func testAnnotatedWithHasManyCustomAverage() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.average(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, AVG("player"."score") AS "averageCustomPlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].averageCustomPlayerScore, 550)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].averageCustomPlayerScore, 500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].averageCustomPlayerScore)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].averageCustomPlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyCustomCount() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.count)
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, COUNT(DISTINCT "player"."id") AS "customPlayerCount" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].customPlayerCount, 2)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].customPlayerCount, 3)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertEqual(teamInfos[2].customPlayerCount, 0)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].customPlayerCount, 1)
        }
    }
    
    func testAnnotatedWithHasManyCustomMax() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.max(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, MAX("player"."score") AS "maxCustomPlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].maxCustomPlayerScore, 1000)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].maxCustomPlayerScore, 800)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].maxCustomPlayerScore)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].maxCustomPlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyCustomMin() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.min(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, MIN("player"."score") AS "minCustomPlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].minCustomPlayerScore, 100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].minCustomPlayerScore, 200)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].minCustomPlayerScore)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].minCustomPlayerScore, 0)
        }
    }
    
    func testAnnotatedWithHasManyCustomSum() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.sum(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, SUM("player"."score") AS "customPlayerScoreSum" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].customPlayerScoreSum, 1100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].customPlayerScoreSum, 1500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].customPlayerScoreSum)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].customPlayerScoreSum, 0)
        }
    }
    
    func testAnnotatedWithHasManyCustomTotal() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.total(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, TOTAL("player"."score") AS "customPlayerScoreSum" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].customPlayerScoreSum, 1100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].customPlayerScoreSum, 1500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertEqual(teamInfos[2].customPlayerScoreSum, 0)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].customPlayerScoreSum, 0)
        }
    }
    
    func testAnnotatedWithHasManyMultipleCustomAggregates() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.customPlayers.average(Column("score")))
                .annotated(with: Team.customPlayers.count)
                .annotated(with: Team.customPlayers.min(Column("score")), Team.customPlayers.max(Column("score")))
                .annotated(with: Team.customPlayers.sum(Column("score")))
                .orderByPrimaryKey()
                .asRequest(of: CustomTeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, \
                AVG("player"."score") AS "averageCustomPlayerScore", \
                COUNT(DISTINCT "player"."id") AS "customPlayerCount", \
                MIN("player"."score") AS "minCustomPlayerScore", \
                MAX("player"."score") AS "maxCustomPlayerScore", \
                SUM("player"."score") AS "customPlayerScoreSum" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].averageCustomPlayerScore, 550)
            XCTAssertEqual(teamInfos[0].customPlayerCount, 2)
            XCTAssertEqual(teamInfos[0].maxCustomPlayerScore, 1000)
            XCTAssertEqual(teamInfos[0].minCustomPlayerScore, 100)
            XCTAssertEqual(teamInfos[0].customPlayerScoreSum, 1100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].averageCustomPlayerScore, 500)
            XCTAssertEqual(teamInfos[1].customPlayerCount, 3)
            XCTAssertEqual(teamInfos[1].maxCustomPlayerScore, 800)
            XCTAssertEqual(teamInfos[1].minCustomPlayerScore, 200)
            XCTAssertEqual(teamInfos[1].customPlayerScoreSum, 1500)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertNil(teamInfos[2].averageCustomPlayerScore)
            XCTAssertEqual(teamInfos[2].customPlayerCount, 0)
            XCTAssertNil(teamInfos[2].maxCustomPlayerScore)
            XCTAssertNil(teamInfos[2].minCustomPlayerScore)
            XCTAssertNil(teamInfos[2].customPlayerScoreSum)
            
            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].averageCustomPlayerScore, 0)
            XCTAssertEqual(teamInfos[3].customPlayerCount, 1)
            XCTAssertEqual(teamInfos[3].maxCustomPlayerScore, 0)
            XCTAssertEqual(teamInfos[3].minCustomPlayerScore, 0)
            XCTAssertEqual(teamInfos[3].customPlayerScoreSum, 0)
        }
    }
    
    func testAnnotatedWithHasManyAggregateWithCustomKey() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.average(Column("score")).forKey("a1"))
                .annotated(with: Team.players.count.forKey("a2"))
                .annotated(with: Team.players.max(Column("score")).forKey("a3"))
                .annotated(with: Team.players.min(Column("score")).forKey("a4"))
                .annotated(with: Team.players.sum(Column("score")).forKey("a5"))
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, \
                AVG("player"."score") AS "a1", \
                COUNT(DISTINCT "player"."id") AS "a2", \
                MAX("player"."score") AS "a3", \
                MIN("player"."score") AS "a4", \
                SUM("player"."score") AS "a5" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id"
                """)
        }
    }
    
    func testAnnotatedWithHasManyAggregateExpression() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.average(Column("score") * Column("score")).forKey("a1"))
                .annotated(with: Team.players.max(Column("score") * 10).forKey("a3"))
                .annotated(with: Team.players.min(-Column("score")).forKey("a4"))
                .annotated(with: Team.players.sum(Column("score") * Column("score")).forKey("a5"))
            try assertEqualSQL(db, request, """
                SELECT "team".*, \
                AVG("player"."score" * "player"."score") AS "a1", \
                MAX("player"."score" * 10) AS "a3", \
                MIN(-"player"."score") AS "a4", \
                SUM("player"."score" * "player"."score") AS "a5" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id"
                """)
        }
    }
    
    func testAnnotatedWithHasManyMultipleCount() throws {
        struct TeamInfo: Decodable, FetchableRecord {
            var team: Team
            var lowPlayerCount: Int
            var highPlayerCount: Int
        }
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.filter(Column("score") < 500).forKey("lowPlayers").count)
                .annotated(with: Team.players.filter(Column("score") >= 500).forKey("highPlayers").count)
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, \
                COUNT(DISTINCT "player1"."id") AS "lowPlayerCount", \
                COUNT(DISTINCT "player2"."id") AS "highPlayerCount" \
                FROM "team" \
                LEFT JOIN "player" "player1" ON ("player1"."teamId" = "team"."id") AND ("player1"."score" < 500) \
                LEFT JOIN "player" "player2" ON ("player2"."teamId" = "team"."id") AND ("player2"."score" >= 500) \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].lowPlayerCount, 1)
            XCTAssertEqual(teamInfos[0].highPlayerCount, 1)

            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].lowPlayerCount, 1)
            XCTAssertEqual(teamInfos[1].highPlayerCount, 2)

            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertEqual(teamInfos[2].lowPlayerCount, 0)
            XCTAssertEqual(teamInfos[2].highPlayerCount, 0)

            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].lowPlayerCount, 1)
            XCTAssertEqual(teamInfos[3].highPlayerCount, 0)
        }
    }
    
    func testHasManyIsEmpty() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: Team.players.isEmpty)
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") = 0 AS "hasNoPlayer" \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 4)
                try XCTAssertEqual(request.fetchCount(db), 4)
            }
            do {
                let request = Team.annotated(with: !Team.players.isEmpty)
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") > 0 \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 4)
                try XCTAssertEqual(request.fetchCount(db), 4)
            }
            do {
                let request = Team.having(Team.players.isEmpty)
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") = 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 1)
                try XCTAssertEqual(request.fetchCount(db), 1)
            }
            do {
                let request = Team.having(!Team.players.isEmpty)
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") > 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 3)
                try XCTAssertEqual(request.fetchCount(db), 3)
            }
            do {
                let request = Team.having(Team.players.isEmpty == false)
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") > 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 3)
                try XCTAssertEqual(request.fetchCount(db), 3)
            }
            do {
                let request = Team.having(Team.players.isEmpty == true)
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") = 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 1)
                try XCTAssertEqual(request.fetchCount(db), 1)
            }
        }
    }
    
    func testHasManyThroughIsEmpty() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Player.having(Player.awards.isEmpty)
                try assertEqualSQL(db, request, """
                    SELECT "player".* \
                    FROM "player" \
                    LEFT JOIN "team" ON "team"."id" = "player"."teamId" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "player"."id" \
                    HAVING COUNT(DISTINCT "award"."customPrimaryKey") = 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 1)
                try XCTAssertEqual(request.fetchCount(db), 1)
          }
            do {
                let request = Player.having(!Player.awards.isEmpty)
                try assertEqualSQL(db, request, """
                    SELECT "player".* \
                    FROM "player" \
                    LEFT JOIN "team" ON "team"."id" = "player"."teamId" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "player"."id" \
                    HAVING COUNT(DISTINCT "award"."customPrimaryKey") > 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 5)
                try XCTAssertEqual(request.fetchCount(db), 5)
            }
            do {
                let request = Player.having(Player.awards.isEmpty == false)
                try assertEqualSQL(db, request, """
                    SELECT "player".* \
                    FROM "player" \
                    LEFT JOIN "team" ON "team"."id" = "player"."teamId" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "player"."id" \
                    HAVING COUNT(DISTINCT "award"."customPrimaryKey") > 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 5)
                try XCTAssertEqual(request.fetchCount(db), 5)
            }
            do {
                let request = Player.having(Player.awards.isEmpty == true)
                try assertEqualSQL(db, request, """
                    SELECT "player".* \
                    FROM "player" \
                    LEFT JOIN "team" ON "team"."id" = "player"."teamId" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "player"."id" \
                    HAVING COUNT(DISTINCT "award"."customPrimaryKey") = 0
                    """)
                try XCTAssertEqual(request.fetchAll(db).count, 1)
                try XCTAssertEqual(request.fetchCount(db), 1)
            }
        }
    }

    func testEqualOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.count == 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") = 2
                    """)
            }
            do {
                let request = Team.having(2 == Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING 2 = COUNT(DISTINCT "player"."id")
                    """)
            }
            do {
                let request = Team.having(Team.players.count == Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") = COUNT(DISTINCT "award"."customPrimaryKey")
                    """)
            }
        }
    }
    
    func testNotEqualOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.count != 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") <> 2
                    """)
            }
            do {
                let request = Team.having(2 != Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING 2 <> COUNT(DISTINCT "player"."id")
                    """)
            }
            do {
                let request = Team.having(Team.players.count != Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") <> COUNT(DISTINCT "award"."customPrimaryKey")
                    """)
            }
        }
    }
    
    func testGreaterThanOrEqualOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.count >= 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") >= 2
                    """)
            }
            do {
                let request = Team.having(2 >= Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING 2 >= COUNT(DISTINCT "player"."id")
                    """)
            }
            do {
                let request = Team.having(Team.players.count >= Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") >= COUNT(DISTINCT "award"."customPrimaryKey")
                    """)
            }
        }
    }
    
    func testGreaterThanOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.count > 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") > 2
                    """)
            }
            do {
                let request = Team.having(2 > Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING 2 > COUNT(DISTINCT "player"."id")
                    """)
            }
            do {
                let request = Team.having(Team.players.count > Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") > COUNT(DISTINCT "award"."customPrimaryKey")
                    """)
            }
        }
    }
    
    func testLessThanOrEqualOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.count <= 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") <= 2
                    """)
            }
            do {
                let request = Team.having(2 <= Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING 2 <= COUNT(DISTINCT "player"."id")
                    """)
            }
            do {
                let request = Team.having(Team.players.count <= Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") <= COUNT(DISTINCT "award"."customPrimaryKey")
                    """)
            }
        }
    }
    
    func testLessThanOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.count < 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") < 2
                    """)
            }
            do {
                let request = Team.having(2 < Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING 2 < COUNT(DISTINCT "player"."id")
                    """)
            }
            do {
                let request = Team.having(Team.players.count < Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING COUNT(DISTINCT "player"."id") < COUNT(DISTINCT "award"."customPrimaryKey")
                    """)
            }
        }
    }
    
    func testLogicalOperators() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.having(Team.players.isEmpty && Team.awards.isEmpty)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING (COUNT(DISTINCT "player"."id") = 0) AND (COUNT(DISTINCT "award"."customPrimaryKey") = 0)
                    """)
            }
            do {
                let request = Team.having(Team.players.isEmpty || Team.awards.isEmpty)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING (COUNT(DISTINCT "player"."id") = 0) OR (COUNT(DISTINCT "award"."customPrimaryKey") = 0)
                    """)
            }
            do {
                let request = Team.having(!(Team.players.isEmpty || Team.awards.isEmpty))
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING NOT ((COUNT(DISTINCT "player"."id") = 0) OR (COUNT(DISTINCT "award"."customPrimaryKey") = 0))
                    """)
            }
            do {
                let request = Team.having(!(Team.players.count > 3) || Team.awards.isEmpty)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".* \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id" \
                    HAVING (NOT (COUNT(DISTINCT "player"."id") > 3)) OR (COUNT(DISTINCT "award"."customPrimaryKey") = 0)
                    """)
            }
        }
    }
    
    func testNegatedOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: -Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, -COUNT(DISTINCT "player"."id") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }

    func testAdditionOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: Team.players.count + 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") + 2 \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: 2 + Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, 2 + COUNT(DISTINCT "player"."id") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: Team.players.count + Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") + COUNT(DISTINCT "award"."customPrimaryKey") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
    
    func testSubtractionOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: Team.players.count - 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") - 2 \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: 2 - Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, 2 - COUNT(DISTINCT "player"."id") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: Team.players.count - Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") - COUNT(DISTINCT "award"."customPrimaryKey") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
    
    func testMultiplicationOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: Team.players.count * 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") * 2 \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: 2 * Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, 2 * COUNT(DISTINCT "player"."id") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: Team.players.count * Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") * COUNT(DISTINCT "award"."customPrimaryKey") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
    
    func testDivisionOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: Team.players.count / 2)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") / 2 \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: 2 / Team.players.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, 2 / COUNT(DISTINCT "player"."id") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: Team.players.count / Team.awards.count)
                
                try assertEqualSQL(db, request, """
                    SELECT "team".*, COUNT(DISTINCT "player"."id") / COUNT(DISTINCT "award"."customPrimaryKey") \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    LEFT JOIN "award" ON "award"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
    
    func testIfNullOperator() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            let request = Team
                .annotated(with: Team.players.min(Column("score")) ?? 0)
                .orderByPrimaryKey()
                .asRequest(of: TeamInfo.self)
            
            try assertEqualSQL(db, request, """
                SELECT "team".*, IFNULL(MIN("player"."score"), 0) AS "minPlayerScore" \
                FROM "team" \
                LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                GROUP BY "team"."id" \
                ORDER BY "team"."id"
                """)
            
            let teamInfos = try request.fetchAll(db)
            XCTAssertEqual(teamInfos.count, 4)
            
            XCTAssertEqual(teamInfos[0].team.id, 1)
            XCTAssertEqual(teamInfos[0].team.name, "Reds")
            XCTAssertEqual(teamInfos[0].minPlayerScore, 100)
            
            XCTAssertEqual(teamInfos[1].team.id, 2)
            XCTAssertEqual(teamInfos[1].team.name, "Blues")
            XCTAssertEqual(teamInfos[1].minPlayerScore, 200)
            
            XCTAssertEqual(teamInfos[2].team.id, 3)
            XCTAssertEqual(teamInfos[2].team.name, "Greens")
            XCTAssertEqual(teamInfos[2].minPlayerScore, 0)

            XCTAssertEqual(teamInfos[3].team.id, 4)
            XCTAssertEqual(teamInfos[3].team.name, "Oranges")
            XCTAssertEqual(teamInfos[3].minPlayerScore, 0)
        }
    }
    
    func testAbs() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: abs(Team.players.max(Column("score"))))
                try assertEqualSQL(db, request, """
                    SELECT "team".*, ABS(MAX("player"."score")) \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: abs(Team.players.max(Column("score"))).forKey("foo"))
                try assertEqualSQL(db, request, """
                    SELECT "team".*, ABS(MAX("player"."score")) AS "foo" \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
    
    func testCast() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                let request = Team.annotated(with: cast(Team.players.count, as: .real))
                try assertEqualSQL(db, request, """
                    SELECT "team".*, CAST(COUNT(DISTINCT "player"."id") AS REAL) AS "playerCount" \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                let request = Team.annotated(with: cast(Team.players.count, as: .real).forKey("foo"))
                try assertEqualSQL(db, request, """
                    SELECT "team".*, CAST(COUNT(DISTINCT "player"."id") AS REAL) AS "foo" \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
    
    func testLength() throws {
        let dbQueue = try makeDatabaseQueue()
        try dbQueue.read { db in
            do {
                // This is a meaningless request, but this is enough for this test
                let request = Team.annotated(with: length(Team.players.max(Column("score"))))
                try assertEqualSQL(db, request, """
                    SELECT "team".*, LENGTH(MAX("player"."score")) \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
            do {
                // This is a meaningless request, but this is enough for this test
                let request = Team.annotated(with: length(Team.players.max(Column("score"))).forKey("foo"))
                try assertEqualSQL(db, request, """
                    SELECT "team".*, LENGTH(MAX("player"."score")) AS "foo" \
                    FROM "team" \
                    LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
                    GROUP BY "team"."id"
                    """)
            }
        }
    }
}