import necto

# Composable, type-safe query
let users = repo.all(
  Query.fromSchema(User)
    .where("age", Gte, "18")
    .orderBy("name", Asc)
)

# Changeset-driven writes
let cs = newChangeset(newUser(), {"name": "Ivan", "email": "ivan@test.com"}.toTable)
  .castFields(@["name", "email"])
  .validateRequired(@["name", "email"])
if cs.isValid:
  let user = repo.insert!(cs)

import necto
import necto/adapters/postgres

necto_repo AppRepo:
  adapter PostgresAdapter
  host "localhost"
  port 5432
  user "postgres"
  password "pas+123"
  database "my_app"
  pool_size 10

import necto
import necto/adapters/mariadb

necto_repo AppRepo:
  adapter MariaDbAdapter
  host "localhost"
  port 3306
  user "root"
  password "pas+123"
  database "my_app"
  pool_size 10

import necto
import necto/adapters/sqlite

necto_repo AppRepo:
  adapter SqliteAdapter
  database "my_app.db"  # or ":memory:" for tests
  pool_size 1

let repo = apprepoInstance

necto_schema User:
  table "users"
  field id: int64 {.primary_key, auto_increment.}
  field name: string {.not_null.}
  field email: string {.not_null, unique.}
  field age: int
  timestamps

# All users
let all = repo.all(fromSchema(User))

# Filtering and sorting
let adults = repo.all(
  fromSchema(User)
    .where("age", Gte, "18")
    .orderBy("name", Asc)
    .limit(10)
)

# Single result
let maybe = repo.one(fromSchema(User).where("email", Eq, "ivan@test.com"))

# Count
let count = repo.count(fromSchema(User).where("active", Eq, "true"))

# Subqueries (IN / EXISTS)
let sq = fromSchema(Order).select("user_id").where("total", Gt, "100").subquery()
let bigSpenders = repo.all(
  fromSchema(User).whereIn("id", sq)
)

# Full-Text Search
let articles = repo.all(
  fromSchema(Article)
    .whereTsVectorMatches("search_vector", plaintoTsQuery("simple", "nim orm"))
    .orderByTsRank("search_vector", plaintoTsQuery("simple", "nim orm"), Desc)
    .limit(10)
)

# INSERT
var cs = newChangeset(newUser(), {"name": "Ivan", "email": "ivan@test.com"}.toTable)
cs = cs.castFields(@["name", "email"])
  .validateRequired(@["name", "email"])
let user = repo.insert!(cs)

# UPDATE
var cs2 = newChangeset(user, {"name": "Ivan Petrov"}.toTable)
cs2 = cs2.castFields(@["name"])
let updated = repo.update!(cs2)

# DELETE
var cs3 = newChangeset(updated, initTable[string, string]())
cs3.changes["id"] = $updated.id
repo.delete!(cs3)

# Batch insert
var css: seq[Changeset[User]] = @[]
for name in @["Alice", "Bob", "Charlie"]:
  var cs = newChangeset(newUser(), {"name": name}.toTable)
  cs = cs.castFields(@["name"])
  css.add(cs)
let users = repo.insert_all(css)  # Single batch query, RETURNING *

# Batch update
let updated = repo.update_all(
  fromSchema(User).where("active", Eq, "false"),
  {"active": "true"}.toTable
)

# Batch delete
let deleted = repo.delete_all(
  fromSchema(User).where("last_login", Lt, "2020-01-01")
)

# Pipe operator (Elixir-style)
let adults = User
  |> fromSchema
  |> where("age", Gte, "18")
  |> orderBy("name", Asc)
  |> limit(10)
  |> repo.all

repo.transaction proc() =
  let user = repo.insert!(newChangeset(newUser(), params))
  let post = repo.insert!(newChangeset(newPost(), params2))
  # Exception → automatic ROLLBACK

necto_schema Post:
  table "posts"
  field id: int64 {.primary_key.}
  field title: string
  belongs_to author: User
  timestamps

# Load posts
let posts = repo.all(fromSchema(Post).orderBy("id", Asc))

# Batch preload authors (2 queries, N+1 safe)
let authors = preloadBelongsTo[Post, User](repo, posts)
for p in posts:
  echo authors[p.author_id].name

# Automatic preload (even more convenient)
let postsWithAuthors = repo.allWithPreload(
  fromSchema(Post).orderBy("id", Asc),
  "author"
)
# Posts are loaded; authors are batch-preloaded automatically

# Multiple associations at once
let usersWithPosts = repo.allWithPreload(
  fromSchema(User).where("active", Eq, "true"),
  "posts", "profile"
)