// Books — the centrepiece of the advanced schema-DSL tour. One table,
// five specialized column features:
//
//   - `authorId`  → reference(() => authors)  (FK + auto B-tree index)
//   - `genre`     → dbEnum(...).column()      (native ENUM type)
//   - `tags`      → array(text())             (native text[] on postgres,
//                                              JSON fallback elsewhere)
//   - `slug`      → text().generatedAs(..., { stored: true })
//                                             (DB-computed, persisted, indexable)
//   - `.fullTextIndex('bookSearch', [...])`   (FTS over title + summary;
//                                              query with `.matching(...)`)

import { array, dbEnum, id, reference, table, text } from '@voltro/database'
import { tenant } from '@voltro/plugin-multitenancy/mixin'
import { authors } from './authors.entity'

// A native ENUM type. Declared once (module-level) so it's a reusable handle:
//   - postgres → CREATE TYPE book_genre AS ENUM (...). ADD VALUE is O(1).
//   - mysql / mariadb → native ENUM('fiction', ...) column.
//   - mssql / sqlite → NVARCHAR / TEXT + CHECK fallback.
// Distinct from `text().oneOf([...])`, which is a CROSS-DIALECT CHECK on every
// backend — reach for `dbEnum` on postgres-primary apps for the cheap
// ADD-VALUE migration. The `as const` is required for the literal-union type.
export const bookGenre = dbEnum('book_genre', [
  'fiction',
  'nonfiction',
  'fantasy',
  'sciFi',
  'mystery',
  'biography',
] as const)

export const books = table('books', {
  id:       id({ prefix: 'book' }),
  authorId: reference(() => authors),                       // FK → authors, auto-indexed
  title:    text(),
  summary:  text(),
  // Native ENUM column, defaulting to 'fiction'.
  genre:    bookGenre.column().default('fiction'),
  // Array column → `text[]` on postgres; JSON-string codec elsewhere. The
  // user-facing API stays "I get back a JS string array" on every dialect.
  tags:     array(text()).default([]),
  // DB-computed STORED column — a URL-friendly slug derived from `title` in
  // the DB engine on INSERT + UPDATE. `stored: true` persists it on disk so
  // it's indexable. Same-row columns only (it can't see subject/tenant —
  // that's what `.computed(row => ...)` is for). The expression is raw SQL
  // emitted verbatim, so it must stay portable (double-quoted identifier).
  slug:     text().generatedAs(`lower("title")`, { stored: true }),
})
  // Full-text search index over title + summary. One declaration, three
  // backends (postgres tsvector+GIN, mysql/mariadb FULLTEXT, sqlite FTS5;
  // mssql ranked-LIKE fallback). Query it with `.matching('bookSearch', q)`.
  .fullTextIndex('bookSearch', ['title', 'summary'], {
    config:  'english',
    weights: { title: 'A', summary: 'B' },
  })
  // tenant() pulls audit() transitively (tenantId + audit columns).
  .with(tenant())