// Schema for the {{projectName}} governance backend — a `profiles` table with
// an ENCRYPTED column. `actors` + `tenants` are the framework core tables.

import {
  databaseHandle,
  id,
  table,
  text,
  timestamp,
  type InferRow,
} from '@voltro/database'
import { audit } from '@voltro/plugin-audit'

export const actors = table('actors', {
  id:          id(),
  kind:        text().oneOf(['user', 'serviceAccount', 'apiKey', 'system']),
  displayName: text().nullable(),
  createdAt:   timestamp().default('now'),
})

export const tenants = table('tenants', {
  id:        id(),
  name:      text(),
  createdAt: timestamp().default('now'),
})

export const profiles = table('profiles', {
  id:    id({ prefix: 'prof' }),
  name:  text(),
  email: text(),
  // TWO markers, because they answer two different questions — say both when
  // you mean both:
  //
  //   .encrypted()  → AES-256-GCM AT REST. The handler always sees plaintext;
  //                   the stored value is an opaque `enc:v1:…` string. Encrypt
  //                   only what you read back WHOLE (you can't filter/sort on
  //                   it in SQL — it's ciphertext on disk).
  //   .serverOnly() → may this value leave the server AT ALL. It may not.
  //
  // Reading `.encrypted()` as "safe to expose" is a category error and a
  // tempting one: the runtime DECRYPTS for the handler, so an encrypted column
  // flows to a client exactly like any other unless it is also `.serverOnly()`.
  // `crud.*` strips serverOnly columns for you; a hand-written procedure must
  // simply not name it in its output — see `actions/profiles.get.action.ts`,
  // which returns the last four digits it derived server-side instead.
  ssn:   text().encrypted().serverOnly(),
})
  .with(audit())     // createdAt drives the retention sweep

export type Profile = InferRow<typeof profiles>

export const database = databaseHandle({ actors, tenants, profiles })