// src/db/models/author.ts
import type { ModelDefinition } from "@casekit/orm";
import { sql } from "@casekit/sql";

export const author = {
    fields: {
        id: { type: "serial", primaryKey: true },
        name: { type: "text" },
        email: { type: "text", unique: true },
        createdAt: { type: "timestamptz", default: sql`now()` },
    },
    relations: {
        books: {
            type: "1:N",
            model: "book",
            fromField: "id",
            toField: "authorId",
        },
    },
} as const satisfies ModelDefinition;

// src/db/index.ts
import { orm, type Config } from "@casekit/orm";
import { snakeCase } from "es-toolkit";
import * as models from "./models/index.js";

const config = {
    models,
    naming: { column: snakeCase },
    connection: { database: "myapp" },
} as const satisfies Config;

export const db = orm(config);

import { $ilike } from "@casekit/orm";

const authorsWithBooks = await db.findMany("author", {
    select: ["id", "name"],
    include: {
        books: {
            select: ["id", "title"],
            where: { published: true },
            orderBy: [["title", "asc"]],
            limit: 5,
        },
    },
    where: {
        email: { [$ilike]: "%@example.com" },
    },
});

// Create
const author = await db.createOne("author", {
    values: { name: "Jane", email: "jane@example.com" },
    returning: ["id", "createdAt"],
});

// Read
const authors = await db.findMany("author", {
    select: ["id", "name"],
    where: { name: { [$ilike]: "%john%" } },
    orderBy: [["name", "asc"]],
    limit: 10,
});

// Update
await db.updateOne("author", {
    set: { name: "Jane Doe" },
    where: { id: 1 },
});

// Delete
await db.deleteOne("author", {
    where: { id: 1 },
});

await db.transact(async (tx) => {
    const author = await tx.createOne("author", {
        values: { name: "New Author", email: "new@example.com" },
        returning: ["id"],
    });

    await tx.createOne("book", {
        values: { title: "New Book", authorId: author.id },
    });
});

const dbWithMiddleware = db.middleware([
    tenancy({ org: currentOrg }),
    userstamps({ user: currentUser }),
]);

// All queries automatically filtered by org and stamped with user

import { z } from "zod";
import { sql } from "@casekit/sql";

const results = await db.query(
    z.object({ total: z.number() }),
    sql`SELECT COUNT(*) as total FROM authors WHERE active = true`
);