/// A "relation", as defined by the [relational
/// terminology](https://en.wikipedia.org/wiki/Relational_database#Terminology),
/// is "a set of tuples sharing the same attributes; a set of columns and rows."
///
/// SQLRelation is defined with a selection, a source table of query, and an
/// eventual filter and ordering.
///
///     SELECT ... FROM ... WHERE ... ORDER BY ...
///            |        |         |            |
///            |        |         |            • ordering
///            |        |         • filter
///            |        • source
///            • selection
///
/// Other SQL clauses such as GROUP BY, LIMIT are defined one level up, in the
/// SQLQuery type.
///
/// ## Promises
///
/// Filter and ordering are actually "promises", which are only resolved
/// when a database connection is available. This is how we can implement
/// requests such as `Record.filter(key: 1)` or `Record.orderByPrimaryKey()`:
/// both need a database connection in order to introspect the primary key.
///
///     // SELECT * FROM player
///     // WHERE continent = 'EU'
///     // ORDER BY code -- primary key infered from the database schema
///     Country
///         .filter(Column("continent") == "EU")
///         .orderByPrimaryKey()
///
/// ## Children
///
/// Relations may also have children. A child is a link to another relation, and
/// provide support for joins and prefetched relations.
///
/// Relations and their children constitute a tree of relations, which the user
/// builds with associations:
///
///     // Builds a relation with two children:
///     Author
///         .including(required: Author.country)
///         .including(all: Author.books)
///
/// There are four kinds of children:
///
/// - `.oneOptional`:
///
///     Such children are left joined. They may, or not, be included in
///     the selection.
///
///         // SELECT book.*
///         // FROM book
///         // LEFT JOIN author ON author.id = book.id
///         Book.joining(optional: Book.author)
///
///         // SELECT book.*, author.*
///         // FROM book
///         // LEFT JOIN author ON author.id = book.id
///         Book.including(optional: Book.author)
///
/// - `.oneRequired`:
///
///     Such children are inner joined. They may, or not, be included in
///     the selection.
///
///         // SELECT book.*
///         // FROM book
///         // JOIN author ON author.id = book.id AND author.country = 'FR'
///         Book.joining(required: Book.author.filter(Column("country") == "FR"))
///
///         // SELECT book.*, author.*
///         // FROM book
///         // JOIN author ON author.id = book.id AND author.country = 'FR'
///         Book.including(required: Book.author.filter(Column("country") == "FR"))
///
/// - `.allPrefetched`:
///
///     Such children are prefetched using several SQL requests:
///
///         // SELECT * FROM countries WHERE continent = 'EU'
///         // SELECT * FROM passport WHERE countryCode IN ('BE', 'DE', 'FR', ...)
///         Country
///             .filter(Column("continent") == "EU")
///             .including(all: Country.passports)
///
/// - `.allNotPrefetched`:
///
///     Such children are not joined, and not prefetched. They are used as
///     intermediate children towards a prefetched child. In the example
///     below, the country relation has a `.allNotPrefetched` child to
///     passports, and the passport relation has a `.allPrefetched` child
///     to citizens.
///
///         // SELECT * FROM countries WHERE continent = 'EU'
///         // SELECT citizens.* FROM citizens
///         // JOIN passport ON passport.citizenId = citizens.id
///         //              AND passport.countryCode IN ('BE', 'DE', 'FR', ...)
///         Country
///             .filter(Column("continent") == "EU")
///             .including(all: Country.citizens)
struct SQLRelation {
    struct Child: Refinable {
        enum Kind {
            // Record.including(optional: association)
            case oneOptional
            // Record.including(required: association)
            case oneRequired
            // Record.including(all: association)
            case allPrefetched
            // Record.including(all: associationThroughPivot)
            case allNotPrefetched
            
            var isSingular: Bool {
                switch self {
                case .oneOptional, .oneRequired:
                    return true
                case .allPrefetched, .allNotPrefetched:
                    return false
                }
            }
        }
        
        var kind: Kind
        var condition: SQLAssociationCondition
        var relation: SQLRelation
        
        /// Returns true iff this child can change the parent count.
        ///
        /// Record.including(required: association) // true
        /// Record.including(all: association)      // false
        var impactsParentCount: Bool {
            switch kind {
            case .oneOptional, .oneRequired:
                return true
            case .allPrefetched, .allNotPrefetched:
                return false
            }
        }
        
        fileprivate func makeAssociationForKey(_ key: String) -> SQLAssociation {
            let key = SQLAssociationKey.fixed(key)
            
            let cardinality: SQLAssociationCardinality
            switch kind {
            case .oneOptional, .oneRequired:
                cardinality = .toOne
            case .allPrefetched, .allNotPrefetched:
                cardinality = .toMany
            }
            
            return SQLAssociation(
                key: key,
                condition: condition,
                relation: relation,
                cardinality: cardinality)
        }
    }
    
    var source: SQLSource
    var selectionPromise: DatabasePromise<[SQLSelectable]> = DatabasePromise(value: [])
    // Filter is an array of expressions that we'll join with the AND operator.
    // This gives nicer output in generated SQL: `(a AND b AND c)` instead of
    // `((a AND b) AND c)`.
    var filtersPromise: DatabasePromise<[SQLExpression]> = DatabasePromise(value: [])
    var ordering: SQLRelation.Ordering = SQLRelation.Ordering()
    var children: OrderedDictionary<String, Child> = [:]
    
    var prefetchedAssociations: [SQLAssociation] {
        children.flatMap { key, child -> [SQLAssociation] in
            switch child.kind {
            case .allPrefetched:
                return [child.makeAssociationForKey(key)]
            case .oneOptional, .oneRequired, .allNotPrefetched:
                return child.relation.prefetchedAssociations.map { association in
                    // Remove redundant pivot child
                    let pivotKey = association.pivot.keyName
                    let child = child.map(\.relation) { relation in
                        assert(relation.children[pivotKey] != nil)
                        return relation.removingChild(forKey: pivotKey)
                    }
                    return association.through(child.makeAssociationForKey(key))
                }
            }
        }
    }
}

extension SQLRelation: Refinable {
    func select(_ selection: @escaping (Database) throws -> [SQLSelectable]) -> Self {
        with(\.selectionPromise, DatabasePromise(selection))
    }
    
    // Convenience
    func select(_ selection: [SQLSelectable]) -> Self {
        select { _ in selection }
    }
    
    /// Removes all selections from chidren
    func selectOnly(_ selection: [SQLSelectable]) -> Self {
        select(selection).map(\.children, { $0.mapValues { $0.map(\.relation, { $0.selectOnly([]) }) } })
    }
    
    func annotated(with selection: @escaping (Database) throws -> [SQLSelectable]) -> Self {
        map(\.selectionPromise) { selectionPromise in
            DatabasePromise { db in
                try selectionPromise.resolve(db) + selection(db)
            }
        }
    }
    
    // Convenience
    func annotated(with selection: [SQLSelectable]) -> Self {
        annotated(with: { _ in selection })
    }
    
    func filter(_ predicate: @escaping (Database) throws -> SQLExpressible) -> Self {
        map(\.filtersPromise) { filtersPromise in
            DatabasePromise { db in
                try filtersPromise.resolve(db) + [predicate(db).sqlExpression]
            }
        }
    }
    
    func order(_ orderings: @escaping (Database) throws -> [SQLOrderingTerm]) -> Self {
        with(\.ordering, SQLRelation.Ordering(orderings: orderings))
    }
    
    func reversed() -> Self {
        map(\.ordering, \.reversed)
    }
    
    func unordered() -> Self {
        self.with(\.ordering, SQLRelation.Ordering())
            .map(\.children, { $0.mapValues { $0.map(\.relation, { $0.unordered() }) } })
    }
    
    func qualified(with alias: TableAlias) -> Self {
        map(\.source) { $0.qualified(with: alias) }
    }
}

extension SQLRelation {
    /// Returns a relation extended with an association.
    ///
    /// This method provides support for public joining methods such
    /// as `including(required:)`:
    ///
    ///     struct Destination: TableRecord { }
    ///     struct Origin: TableRecord {
    ///         static let destination = belongsTo(Destination.self)
    ///     }
    ///
    ///     // SELECT origin.*, destination.*
    ///     // FROM origin
    ///     // JOIN destination ON destination.id = origin.destinationId
    ///     let request = Origin.including(required: Origin.destination)
    ///
    /// At low-level, this gives:
    ///
    ///     let sqlAssociation = Origin.destination.sqlAssociation
    ///     let origin = Origin.all().query.relation
    ///     let relation = origin.appending(sqlAssociation, kind: .oneRequired)
    ///     let query = SQLQuery(relation: relation)
    ///     let generator = SQLQueryGenerator(query)
    ///     let statement, _ = try generator.prepare(db)
    ///     print(statement.sql)
    ///     // SELECT origin.*, destination.*
    ///     // FROM origin
    ///     // JOIN destination ON destination.originId = origin.id
    ///
    /// This method works for simple direct associations such as BelongsTo or
    /// HasMany in the above examples, but also for indirect associations such
    /// as HasManyThrough, which have any number of pivot relations between the
    /// origin and the destination.
    func appendingChild(for association: SQLAssociation, kind: SQLRelation.Child.Kind) -> Self {
        // Preserve association cardinality in intermediate steps of
        // including(all:), and force desired cardinality otherwize
        let isSingular = (kind == .allNotPrefetched)
            ? association.destination.isSingular
            : kind.isSingular
        let childKey = association.destination.key.name(singular: isSingular)
        let child = SQLRelation.Child(
            kind: kind,
            condition: association.destination.condition,
            relation: association.destination.relation)
        
        let initialSteps = association.steps.dropLast()
        if initialSteps.isEmpty {
            // This is a direct join from origin to destination, without
            // intermediate step.
            //
            // SELECT origin.*, destination.*
            // FROM origin
            // JOIN destination ON destination.id = origin.destinationId
            //
            // let association = Origin.belongsTo(Destination.self)
            // Origin.including(required: association)
            return appendingChild(child, forKey: childKey)
        }
        
        // This is an indirect join from origin to destination, through
        // some pivot(s):
        //
        // SELECT origin.*, destination.*
        // FROM origin
        // JOIN pivot ON pivot.originId = origin.id
        // JOIN destination ON destination.id = pivot.destinationId
        //
        // let association = Origin.hasMany(
        //     Destination.self,
        //     through: Origin.hasMany(Pivot.self),
        //     via: Pivot.belongsTo(Destination.self))
        // Origin.including(required: association)
        //
        // Let's recurse toward a direct join, by making a new association which
        // ends on the last pivot, to which we join our destination:
        var reducedAssociation = SQLAssociation(steps: Array(initialSteps))
        
        reducedAssociation.destination.relation = reducedAssociation.destination.relation
            .select([]) // Intermediate steps are not prefetched
            .appendingChild(child, forKey: childKey)
        
        switch kind {
        case .oneRequired, .oneOptional, .allNotPrefetched:
            return appendingChild(for: reducedAssociation, kind: kind)
        case .allPrefetched:
            // Intermediate steps of indirect associations are not prefetched.
            //
            // For example, the request below prefetches citizens, not
            // intermediate passports:
            //
            //      extension Country {
            //          static let passports = hasMany(Passport.self)
            //          static let citizens = hasMany(Citizens.self, through: passports, using: Passport.citizen)
            //      }
            //      let request = Country.including(all: Country.citizens)
            return appendingChild(for: reducedAssociation, kind: .allNotPrefetched)
        }
    }
    
    private func appendingChild(_ child: SQLRelation.Child, forKey key: String) -> Self {
        var relation = self
        if let existingChild = relation.children.removeValue(forKey: key) {
            guard let mergedChild = existingChild.merged(with: child) else {
                // can't merge
                fatalError("""
                    The association key \"\(key)\" is ambiguous. \
                    Use the Association.forKey(_:) method is order to disambiguate.
                    """)
            }
            relation.children.appendValue(mergedChild, forKey: key)
        } else {
            relation.children.appendValue(child, forKey: key)
        }
        return relation
    }
    
    func removingChild(forKey key: String) -> Self {
        mapInto(\.children) { $0.removeValue(forKey: key) }
    }
    
    func filteringChildren(_ included: (Child) throws -> Bool) rethrows -> Self {
        try map(\.children) { try $0.filter { try included($1) } }
    }
}

extension SQLRelation: _JoinableRequest {
    func _including(all association: SQLAssociation) -> Self {
        appendingChild(for: association, kind: .allPrefetched)
    }
    
    func _including(optional association: SQLAssociation) -> Self {
        appendingChild(for: association, kind: .oneOptional)
    }
    
    func _including(required association: SQLAssociation) -> Self {
        appendingChild(for: association, kind: .oneRequired)
    }
    
    func _joining(optional association: SQLAssociation) -> Self {
        appendingChild(for: association.map(\.destination.relation, { $0.select([]) }), kind: .oneOptional)
    }
    
    func _joining(required association: SQLAssociation) -> Self {
        appendingChild(for: association.map(\.destination.relation, { $0.select([]) }), kind: .oneRequired)
    }
}

// MARK: - SQLSource

enum SQLSource {
    case table(tableName: String, alias: TableAlias?)
    indirect case subquery(SQLQuery)
    
    func qualified(with alias: TableAlias) -> SQLSource {
        switch self {
        case let .table(tableName, sourceAlias):
            if let sourceAlias = sourceAlias {
                alias.becomeProxy(of: sourceAlias)
                return self
            } else {
                alias.setTableName(tableName)
                return .table(tableName: tableName, alias: alias)
            }
        case .subquery:
            return self
        }
    }
}

// MARK: - SQLRelation.Ordering

extension SQLRelation {
    /// SQLRelation.Ordering provides the order clause to SQLRelation.
    struct Ordering {
        private enum Element {
            case terms(DatabasePromise<[SQLOrderingTerm]>)
            case ordering(SQLRelation.Ordering)
            
            var reversed: Element {
                switch self {
                case .terms(let terms):
                    return .terms(terms.map { $0.map(\.reversed) })
                case .ordering(let ordering):
                    return .ordering(ordering.reversed)
                }
            }
            
            func qualified(with alias: TableAlias) -> Element {
                switch self {
                case .terms(let terms):
                    return .terms(terms.map { $0.map { $0.qualifiedOrdering(with: alias) } })
                case .ordering(let ordering):
                    return .ordering(ordering.qualified(with: alias))
                }
            }
            
            func resolve(_ db: Database) throws -> [SQLOrderingTerm] {
                switch self {
                case .terms(let terms):
                    return try terms.resolve(db)
                case .ordering(let ordering):
                    return try ordering.resolve(db)
                }
            }
        }
        
        private var elements: [Element] = []
        var isReversed: Bool
        
        var isEmpty: Bool { elements.isEmpty }
        
        private init(elements: [Element], isReversed: Bool) {
            self.elements = elements
            self.isReversed = isReversed
        }
        
        init() {
            self.init(
                elements: [],
                isReversed: false)
        }
        
        init(orderings: @escaping (Database) throws -> [SQLOrderingTerm]) {
            self.init(
                elements: [.terms(DatabasePromise(orderings))],
                isReversed: false)
        }
        
        var reversed: Ordering {
            Ordering(
                elements: elements,
                isReversed: !isReversed)
        }
        
        func qualified(with alias: TableAlias) -> Ordering {
            Ordering(
                elements: elements.map { $0.qualified(with: alias) },
                isReversed: isReversed)
        }
        
        func appending(_ ordering: Ordering) -> Ordering {
            Ordering(
                elements: elements + [.ordering(ordering)],
                isReversed: isReversed)
        }
        
        func resolve(_ db: Database) throws -> [SQLOrderingTerm] {
            if isReversed {
                return try elements.flatMap { try $0.reversed.resolve(db) }
            } else {
                return try elements.flatMap { try $0.resolve(db) }
            }
        }
    }
}

// MARK: - SQLAssociationCondition

/// The condition that links two tables.
///
/// Currently, we only support one kind of condition: foreign keys.
///
///     SELECT ... FROM book JOIN author ON author.id = book.authorId
///                                         <---- the condition ---->
///
/// When we eventually add support for new ways to link tables,
/// SQLAssociationCondition is the type we'll need to update.
///
/// SQLAssociationCondition adopts Equatable so that we can merge associations:
///
///     // request1 and request2 are equivalent
///     let request1 = Book
///         .including(required: Book.author)
///     let request2 = Book
///         .including(required: Book.author)
///         .including(required: Book.author)
///
///     // request3 and request4 are equivalent
///     let request3 = Book
///         .including(required: Book.author.filter(condition1 && condition2))
///     let request4 = Book
///         .joining(required: Book.author.filter(condition1))
///         .including(optional: Book.author.filter(condition2))
struct SQLAssociationCondition: Equatable {
    /// Definition of a foreign key
    var foreignKeyRequest: SQLForeignKeyRequest
    
    /// True if the table at the origin of the foreign key is on the left of
    /// the sql JOIN operator.
    ///
    /// Let's consider the `book.authorId -> author.id` foreign key.
    /// Its origin table is `book`.
    ///
    /// The origin table `book` is on the left of the JOIN operator for
    /// the BelongsTo association:
    ///
    ///     -- Book.including(required: Book.author)
    ///     SELECT ... FROM book JOIN author ON author.id = book.authorId
    ///
    /// The origin table `book`is on the right of the JOIN operator for
    /// the HasMany and HasOne associations:
    ///
    ///     -- Author.including(required: Author.books)
    ///     SELECT ... FROM author JOIN book ON author.id = book.authorId
    var originIsLeft: Bool
    
    var reversed: SQLAssociationCondition {
        SQLAssociationCondition(
            foreignKeyRequest: foreignKeyRequest,
            originIsLeft: !originIsLeft)
    }
    
    /// Orient foreignKey according to the originIsLeft flag
    func columnMappings(_ db: Database) throws -> [(left: String, right: String)] {
        let foreignKeyMapping = try foreignKeyRequest.fetchMapping(db)
        if originIsLeft {
            return foreignKeyMapping.map { (left: $0.origin, right: $0.destination) }
        } else {
            return foreignKeyMapping.map { (left: $0.destination, right: $0.origin) }
        }
    }
    
    /// Resolves the condition into SQL expressions which involve both left
    /// and right tables.
    ///
    ///     SELECT * FROM left JOIN right ON (right.a = left.b)
    ///                                      <---------------->
    ///
    /// - parameter db: A database connection.
    /// - parameter leftAlias: A TableAlias for the table on the left of the
    ///   JOIN operator.
    /// - Returns: An array of SQL expression that should be joined with
    ///   the AND operator and qualified with the right table.
    func expressions(_ db: Database, leftAlias: TableAlias) throws -> [SQLExpression] {
        try columnMappings(db).map {
            Column($0.right) == QualifiedColumn($0.left, alias: leftAlias)
        }
    }
    
    /// Resolves the condition into an SQL expression which involves only the
    /// right table.
    ///
    /// Given `right.a = left.b`, returns `right.a = 1` or
    /// `right.a IN (1, 2, 3)`.
    func filteringExpression<Row: ColumnAddressable>(_ db: Database, leftRows: [Row]) throws -> SQLExpression {
        guard let firstLeftRow = leftRows.first else {
            // Degenerate case: there is no row to attach
            return false.sqlExpression
        }
        
        // The (left, right) column mapping pairs
        let columnMappings = try self.columnMappings(db)
        
        // We assume that all rows have the same layout, and that addressing
        // rows by index is faster than addressing them by column name.
        // So we use the first left row in order to convert left column names
        // into row indexes.
        let mappings: [(leftIndex: Row.ColumnIndex, rightColumn: Column)] = columnMappings.map { mapping in
            guard let leftIndex = firstLeftRow.index(forColumn: mapping.left) else {
                fatalError("Missing column: \(mapping.left)")
            }
            return (leftIndex: leftIndex, rightColumn: Column(mapping.right))
        }
        guard let mapping = mappings.first else {
            // Degenerate case: no joining column
            return true.sqlExpression
        }
        
        if mappings.count == 1 {
            // Join on a single right column.
            
            // Unique database values and filter out NULL:
            let leftIndex = mapping.leftIndex
            var dbValues = Set(leftRows.map { $0.databaseValue(at: leftIndex) })
            dbValues.remove(.null)
            
            if dbValues.isEmpty {
                return mapping.rightColumn == nil
            } else {
                // table.a IN (1, 2, 3, ...)
                // Sort database values for nicer output.
                return dbValues.sorted(by: <).contains(mapping.rightColumn)
            }
        } else {
            // Join on a multiple columns.
            // ((table.a = 1) AND (table.b = 2)) OR ((table.a = 3) AND (table.b = 4)) ...
            return leftRows
                .map({ leftRow in
                    // (table.a = 1) AND (table.b = 2)
                    mappings
                        .map({ mapping -> SQLExpression in
                            let leftValue = leftRow.databaseValue(at: mapping.leftIndex)
                            return mapping.rightColumn == leftValue
                        })
                        .joined(operator: .and)
                })
                .joined(operator: .or)
        }
    }
}

/// A protocol for row-like containers
protocol ColumnAddressable {
    associatedtype ColumnIndex
    func index(forColumn column: String) -> ColumnIndex?
    func databaseValue(at index: ColumnIndex) -> DatabaseValue
}

/// A "row" that contains null values for all columns
struct NullRow: ColumnAddressable {
    struct DummyIndex { }
    func index(forColumn column: String) -> DummyIndex? { DummyIndex() }
    @inline(__always)
    func databaseValue(at index: DummyIndex) -> DatabaseValue { .null }
}

/// Row has columns
extension Row: ColumnAddressable {
    @inline(__always)
    func databaseValue(at index: Int) -> DatabaseValue { self[index] }
}

/// PersistenceContainer has columns
extension PersistenceContainer: ColumnAddressable {
    func index(forColumn column: String) -> String? { column }
    @inline(__always)
    func databaseValue(at column: String) -> DatabaseValue {
        guard let value = self[caseInsensitive: column] else {
            fatalError("Missing column: \(column)")
        }
        return value.databaseValue
    }
}

// MARK: - Merging
//
// "Merging" is an operation that takes two relations and, if they are
// compatible, gathers them into a merged relation.
//
// It is an important feature that allows the user to define associated requests
// in several steps. For example, in the sample code below, both requests are
// equivalent and generate the same SQL query, thanks to merging:
//
//      let request1 = Book.including(required: Book.author)
//
//      let request2 = Book
//          .including(required: Book.author)
//          .including(required: Book.author)

extension SQLRelation {
    /// Returns nil if relations can't be merged (conflict in source, joins...)
    func merged(with other: SQLRelation) -> Self? {
        guard let mergedSource = source.merged(with: other.source) else {
            // can't merge
            return nil
        }
        
        let mergedFiltersPromise = DatabasePromise<[SQLExpression]> { db in
            try self.filtersPromise.resolve(db) + other.filtersPromise.resolve(db)
        }
        
        var mergedChildren: OrderedDictionary<String, SQLRelation.Child> = [:]
        for (key, child) in children {
            if let otherChild = other.children[key] {
                guard let mergedChild = child.merged(with: otherChild) else {
                    // can't merge
                    return nil
                }
                mergedChildren.appendValue(mergedChild, forKey: key)
            } else {
                mergedChildren.appendValue(child, forKey: key)
            }
        }
        for (key, child) in other.children where mergedChildren[key] == nil {
            mergedChildren.appendValue(child, forKey: key)
        }
        
        // replace selection unless empty
        let mergedSelectionPromise = DatabasePromise { db -> [SQLSelectable] in
            let otherSelection = try other.selectionPromise.resolve(db)
            if otherSelection.isEmpty {
                return try self.selectionPromise.resolve(db)
            } else {
                return otherSelection
            }
        }
        
        // replace ordering unless empty
        let mergedOrdering = other.ordering.isEmpty ? ordering : other.ordering
        
        return SQLRelation(
            source: mergedSource,
            selectionPromise: mergedSelectionPromise,
            filtersPromise: mergedFiltersPromise,
            ordering: mergedOrdering,
            children: mergedChildren)
    }
}

extension SQLSource {
    /// Returns nil if sources can't be merged (conflict in tables, aliases...)
    func merged(with other: SQLSource) -> SQLSource? {
        switch (self, other) {
        case let (.table(tableName: tableName, alias: alias), .table(tableName: otherTableName, alias: otherAlias)):
            guard tableName == otherTableName else {
                // can't merge
                return nil
            }
            switch (alias, otherAlias) {
            case (nil, nil):
                return .table(tableName: tableName, alias: nil)
            case let (alias?, nil), let (nil, alias?):
                return .table(tableName: tableName, alias: alias)
            case let (alias?, otherAlias?):
                guard let mergedAlias = alias.merged(with: otherAlias) else {
                    // can't merge
                    return nil
                }
                return .table(tableName: tableName, alias: mergedAlias)
            }
        default:
            // can't merge
            return nil
        }
    }
}

extension SQLRelation.Child {
    /// Returns nil if joins can't be merged (conflict in condition, relation...)
    func merged(with other: SQLRelation.Child) -> Self? {
        guard condition == other.condition else {
            // can't merge
            return nil
        }
        
        guard let mergedRelation = relation.merged(with: other.relation) else {
            // can't merge
            return nil
        }
        
        guard let mergedKind = kind.merged(with: other.kind) else {
            // can't merge
            return nil
        }
        
        return SQLRelation.Child(
            kind: mergedKind,
            condition: condition,
            relation: mergedRelation)
    }
}

extension SQLRelation.Child.Kind {
    /// Returns nil if kinds can't be merged
    func merged(with other: SQLRelation.Child.Kind) -> Self? {
        switch (self, other) {
        case (.oneRequired, .oneRequired),
             (.oneRequired, .oneOptional),
             (.oneOptional, .oneRequired):
            // Equivalent to Record.including(required: association):
            //
            // Record
            //   .including(required: association)
            //   .including(optional: association)
            return .oneRequired
            
        case (.oneOptional, .oneOptional):
            // Equivalent to Record.including(optional: association):
            //
            // Record
            //   .including(optional: association)
            //   .including(optional: association)
            return .oneOptional
            
        case (.allPrefetched, .allPrefetched):
            // Equivalent to Record.including(all: association):
            //
            // Record
            //   .including(all: association)
            //   .including(all: association)
            return .allPrefetched
            
        case (.allPrefetched, .allNotPrefetched),
             (.allNotPrefetched, .allPrefetched):
            // Record
            //   .including(all: associationToDestinationThroughPivot)
            //   .including(all: associationToPivot)
            fatalError("Not implemented: merging a direct association and an indirect one with including(all:)")
            
        case (.allNotPrefetched, .allNotPrefetched):
            // Equivalent to Record.including(all: association)
            //
            // Record
            //   .including(all: association)
            //   .including(all: association)
            return .allNotPrefetched
            
        default:
            // Likely a programmer error:
            //
            // Record
            //   .including(all: Author.books.forKey("foo"))
            //   .including(optional: Author.books.forKey("foo"))
            return nil
        }
    }
}
