// users table
// id | name | email

// posts table
// id | user_id | title | body

// profiles table
// id | user_id | bio | avatar_url

struct User: PrismRelatable {
    static var tableName: String { "users" }

    static var relations: [String: PrismRelation] {
        [
            "posts": .hasMany("posts", foreignKey: "user_id"),
            "profile": .hasOne("profiles", foreignKey: "user_id")
        ]
    }

    let id: Int
    let name: String
    let email: String

    init(row: PrismRow) {
        self.id = row["id"]?.intValue ?? 0
        self.name = row["name"]?.textValue ?? ""
        self.email = row["email"]?.textValue ?? ""
    }

    func toValues() -> [String: PrismDatabaseValue] {
        ["name": .text(name), "email": .text(email)]
    }
}

struct Post: PrismRelatable {
    static var tableName: String { "posts" }

    static var relations: [String: PrismRelation] {
        [
            "author": .belongsTo("users", foreignKey: "user_id")
        ]
    }

    let id: Int
    let userId: Int
    let title: String

    init(row: PrismRow) {
        self.id = row["id"]?.intValue ?? 0
        self.userId = row["user_id"]?.intValue ?? 0
        self.title = row["title"]?.textValue ?? ""
    }

    func toValues() -> [String: PrismDatabaseValue] {
        ["user_id": .integer(userId), "title": .text(title)]
    }
}

let user = try await db.find(User.self, id: 1)!
let posts = try await db.loadHasMany("posts", foreignKey: "user_id", localValue: "\(user.id)")

for post in posts {
    print(post["title"]?.textValue ?? "")
}

let post = try await db.find(Post.self, id: 1)!
let author = try await db.loadBelongsTo("users", primaryKey: "id", foreignValue: "\(post.userId)")

if let author {
    print("Author: \(author["name"]?.textValue ?? "")")
}

let profile = try await db.loadHasOne("profiles", foreignKey: "user_id", localValue: "\(user.id)")

if let profile {
    print("Bio: \(profile["bio"]?.textValue ?? "")")
}

await server.get("/users/:id") { request in
    guard let id = Int(request.parameters["id"] ?? "") else {
        return .json(["error": "Invalid ID"], status: .badRequest)
    }

    guard let user = try await db.find(User.self, id: id) else {
        return .json(["error": "Not found"], status: .notFound)
    }

    let posts = try await db.loadHasMany("posts", foreignKey: "user_id", localValue: "\(user.id)")
    let profile = try await db.loadHasOne("profiles", foreignKey: "user_id", localValue: "\(user.id)")

    return .json([
        "id": user.id,
        "name": user.name,
        "email": user.email,
        "bio": profile?["bio"]?.textValue ?? "",
        "posts": posts.map { row in
            [
                "id": row["id"]?.intValue ?? 0,
                "title": row["title"]?.textValue ?? ""
            ] as [String: Any]
        }
    ] as [String: Any])
}