import { sql, type SQL } from 'drizzle-orm'
import {
  bigint,
  boolean,
  bytea,
  customType,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar
} from 'drizzle-orm/pg-core'

// == CUSTOM TYPES =====================

// -> Typed as a string: an ltree path comes back from the driver as its dotted text form, and every
//    caller treats it as one
const ltree = customType<{ data: string }>({
  dataType() {
    return 'ltree'
  }
})
const tsvector = customType({
  dataType() {
    return 'tsvector'
  }
})

// == TABLES ===========================

// API KEYS ----------------------------
export const apiKeys = pgTable('apiKeys', {
  id: uuid().primaryKey().defaultRandom(),
  name: varchar({ length: 255 }).notNull(),
  // -> Only the tail of the token, to tell keys apart in the admin list. The token itself is a
  //    signed JWT shown once at creation and never stored: it is a bearer credential, and
  //    verification needs the public key plus this row's state, not the token.
  keyShort: varchar({ length: 8 }).notNull(),
  // -> IDs of the groups whose permissions the key carries. Resolved on every request, so editing a
  //    group immediately affects the keys pointing at it.
  groups: jsonb().notNull().default([]),
  expiration: timestamp().notNull().defaultNow(),
  isRevoked: boolean().notNull().default(false),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow()
})

// APPROVAL RULES ----------------------
/**
 * Which pages accept edit suggestions, who may submit them, and who reviews them.
 *
 * Per site, and matched the way group page rules are: a mode plus a pattern. A page no rule matches
 * accepts no suggestions at all, so this table being empty means the feature is off.
 */
export const approvalRules = pgTable(
  'approvalRules',
  {
    id: uuid().primaryKey().defaultRandom(),
    name: varchar({ length: 255 }).notNull().default(''),
    // -> A rule can be turned off without losing what it says, which is how an administrator suspends
    //    suggestions on a section without having to write the rule again afterwards.
    isEnabled: boolean().notNull().default(true),
    // -> One of START / EXACT / END / REGEX / TAG / TAGALL, the same set group page rules use. A
    //    varchar rather than an enum so that adding a mode does not need a migration; the API schema
    //    is what rejects an unknown one.
    match: varchar({ length: 16 }).notNull().default('START'),
    path: varchar({ length: 2048 }).notNull().default(''),
    // -> Group IDs. Resolved on use rather than joined, so deleting a group takes effect at once, the
    //    way `apiKeys.groups` works.
    submitterGroups: jsonb().notNull().default([]),
    reviewerGroups: jsonb().notNull().default([]),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [index('approvalRules_siteId_idx').on(table.siteId)]
)

// ASSETS ------------------------------
export const assetKindEnum = pgEnum('assetKind', ['document', 'image', 'other'])
export const assets = pgTable(
  'assets',
  {
    id: uuid().primaryKey().defaultRandom(),
    fileName: varchar({ length: 255 }).notNull(),
    fileExt: varchar({ length: 255 }).notNull(),
    isSystem: boolean().notNull().default(false),
    kind: assetKindEnum().notNull().default('other'),
    mimeType: varchar({ length: 255 }).notNull().default('application/octet-stream'),
    fileSize: bigint({ mode: 'number' }), // in bytes
    meta: jsonb().notNull().default({}),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    data: bytea(),
    preview: bytea(),
    storageInfo: jsonb(),
    authorId: uuid()
      .notNull()
      .references(() => users.id),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [index('assets_siteId_idx').on(table.siteId)]
)

// AUTHENTICATION ----------------------
export const authentication = pgTable('authentication', {
  id: uuid().primaryKey().defaultRandom(),
  module: varchar({ length: 255 }).notNull(),
  isEnabled: boolean().notNull().default(false),
  displayName: varchar({ length: 255 }).notNull().default(''),
  config: jsonb().notNull().default({}),
  registration: boolean().notNull().default(false),
  allowedEmailRegex: varchar({ length: 255 }).notNull().default(''),
  autoEnrollGroups: uuid().array().default([])
})

// BLOCKS ------------------------------
export const blocks = pgTable(
  'blocks',
  {
    id: uuid().primaryKey().defaultRandom(),
    block: varchar({ length: 255 }).notNull(),
    name: varchar({ length: 255 }).notNull(),
    description: varchar({ length: 255 }).notNull(),
    icon: varchar({ length: 255 }).notNull(),
    isEnabled: boolean().notNull().default(false),
    isCustom: boolean().notNull().default(false),
    config: jsonb().notNull().default({}),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [index('blocks_siteId_idx').on(table.siteId)]
)

// GROUPS ------------------------------
export const groups = pgTable('groups', {
  id: uuid().primaryKey().defaultRandom(),
  name: varchar({ length: 255 }).notNull(),
  permissions: jsonb().notNull(),
  rules: jsonb().notNull(),
  redirectOnLogin: varchar({ length: 255 }).notNull().default(''),
  redirectOnFirstLogin: varchar({ length: 255 }).notNull().default(''),
  redirectOnLogout: varchar({ length: 255 }).notNull().default(''),
  isSystem: boolean().notNull().default(false),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow()
})

// HOOKS -------------------------------
export const hookStateEnum = pgEnum('hookState', ['pending', 'success', 'error'])
export const hooks = pgTable('hooks', {
  id: uuid().primaryKey().defaultRandom(),
  name: varchar({ length: 255 }).notNull(),
  // -> Event keys such as `page:create`, matched against what the server emits
  events: text()
    .array()
    .notNull()
    .default(sql`ARRAY[]::text[]`),
  url: text().notNull(),
  includeMetadata: boolean().notNull().default(true),
  includeContent: boolean().notNull().default(false),
  acceptUntrusted: boolean().notNull().default(false),
  // -> Sent verbatim as the Authorization header, so it holds whatever secret the remote expects
  authHeader: text(),
  // -> Outcome of the most recent delivery, which is what the admin list shows
  state: hookStateEnum().notNull().default('pending'),
  lastErrorMessage: text(),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow()
})

// ICONS -------------------------------
// -> An Iconify icon set the wiki draws icons from, e.g. `mdi`. Adding one makes its icons
//    searchable; individual icons are only stored once something references them.
export const iconSets = pgTable('iconSets', {
  // -> The Iconify prefix, which is what content references: `<prefix>:<name>`
  prefix: varchar({ length: 64 }).primaryKey(),
  name: varchar({ length: 255 }).notNull(),
  isEnabled: boolean().notNull().default(true),
  // -> Iconify collection metadata (author, license, total, palette, samples, ...) as published by
  //    the upstream API, refreshed on demand rather than being authored here
  info: jsonb().notNull().default({}),
  refreshedAt: timestamp(),
  createdAt: timestamp().notNull().defaultNow()
})

// -> The permanent home of every icon the wiki has ever served. Fetched from the Iconify API on first
//    use, then never fetched again: the disk cache is derived from these rows and may be empty.
export const icons = pgTable(
  'icons',
  {
    prefix: varchar({ length: 64 })
      .notNull()
      .references(() => iconSets.prefix),
    name: varchar({ length: 255 }).notNull(),
    // -> The SVG markup inside the `<svg>` element, with `currentColor` left as-is
    body: text().notNull(),
    // -> Resolved Iconify icon properties: the viewBox is `left top width height`, and the transform
    //    flags apply on top of it. Aliases are resolved before storing, so a row is self-contained.
    width: integer().notNull().default(16),
    height: integer().notNull().default(16),
    left: integer().notNull().default(0),
    top: integer().notNull().default(0),
    rotate: integer().notNull().default(0),
    hFlip: boolean().notNull().default(false),
    vFlip: boolean().notNull().default(false),
    createdAt: timestamp().notNull().defaultNow()
  },
  (table) => [primaryKey({ columns: [table.prefix, table.name] })]
)

// JOB HISTORY -------------------------
export const jobHistoryStateEnum = pgEnum('jobHistoryState', [
  'active',
  'completed',
  'failed',
  'interrupted'
])
export const jobHistory = pgTable('jobHistory', {
  id: uuid().primaryKey().defaultRandom(),
  task: varchar({ length: 255 }).notNull(),
  state: jobHistoryStateEnum().notNull(),
  useWorker: boolean().notNull().default(false),
  wasScheduled: boolean().notNull().default(false),
  payload: jsonb(),
  attempt: integer().notNull().default(1),
  maxRetries: integer().notNull().default(0),
  lastErrorMessage: text(),
  executedBy: varchar({ length: 255 }),
  createdAt: timestamp().notNull(),
  startedAt: timestamp().notNull().defaultNow(),
  completedAt: timestamp()
})

// JOB SCHEDULE ------------------------
export const jobSchedule = pgTable('jobSchedule', {
  id: uuid().primaryKey().defaultRandom(),
  task: varchar({ length: 255 }).notNull(),
  cron: varchar({ length: 255 }).notNull(),
  type: varchar({ length: 255 }).notNull().default('system'),
  payload: jsonb(),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow()
})

// JOB LOCK ----------------------------
export const jobLock = pgTable('jobLock', {
  key: varchar({ length: 255 }).primaryKey(),
  lastCheckedBy: varchar({ length: 255 }),
  lastCheckedAt: timestamp().notNull().defaultNow()
})

// JOBS --------------------------------
export const jobs = pgTable('jobs', {
  id: uuid().primaryKey().defaultRandom(),
  task: varchar({ length: 255 }).notNull(),
  useWorker: boolean().notNull().default(false),
  payload: jsonb(),
  retries: integer().notNull().default(0),
  maxRetries: integer().notNull().default(0),
  waitUntil: timestamp(),
  isScheduled: boolean().notNull().default(false),
  createdBy: varchar({ length: 255 }),
  createdAt: timestamp().notNull().defaultNow(),
  updatedAt: timestamp().notNull().defaultNow()
})

// LOCALES -----------------------------
export const locales = pgTable(
  'locales',
  {
    code: varchar({ length: 255 }).primaryKey(),
    name: varchar({ length: 255 }).notNull(),
    nativeName: varchar({ length: 255 }).notNull(),
    language: varchar({ length: 8 }).notNull(), // Unicode language subtag
    region: varchar({ length: 3 }).notNull(), // Unicode region subtag
    script: varchar({ length: 4 }).notNull(), // Unicode script subtag
    isRTL: boolean().notNull().default(false),
    strings: jsonb().notNull().default([]),
    completeness: integer().notNull().default(0),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow()
  },
  (table) => [index('locales_language_idx').on(table.language)]
)

// NAVIGATION --------------------------
export const navigation = pgTable(
  'navigation',
  {
    id: uuid().primaryKey().defaultRandom(),
    items: jsonb().notNull().default([]),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [index('navigation_siteId_idx').on(table.siteId)]
)

// PAGES ------------------------------
export const pagePublishStateEnum = pgEnum('pagePublishState', ['draft', 'published', 'scheduled'])
export const pages = pgTable(
  'pages',
  {
    id: uuid().primaryKey().defaultRandom(),
    // -> A BCP-47 code, matched only ever for equality. Not `ltree`: a hyphenated code is a single
    //    label to it, so `'pt-BR'::ltree <@ 'pt'` is false and the type buys no locale-family
    //    matching -- see the note on `pageHistory.locale`.
    locale: varchar({ length: 255 }).notNull(),
    path: varchar({ length: 255 }).notNull(),
    hash: varchar({ length: 255 }).notNull(),
    alias: varchar({ length: 255 }),
    title: varchar({ length: 255 }).notNull(),
    description: varchar({ length: 255 }),
    icon: varchar({ length: 255 }),
    publishState: pagePublishStateEnum('publishState').notNull().default('draft'),
    publishStartDate: timestamp(),
    publishEndDate: timestamp(),
    config: jsonb().notNull().default({}),
    relations: jsonb().notNull().default([]),
    content: text(),
    render: text(),
    searchContent: text(),
    ts: tsvector('ts'),
    tags: text()
      .array()
      .notNull()
      .default(sql`ARRAY[]::text[]`),
    toc: jsonb(),
    editor: varchar({ length: 255 }).notNull(),
    contentType: varchar({ length: 255 }).notNull(),
    isBrowsable: boolean().notNull().default(true),
    isSearchable: boolean().notNull().default(true),
    // -> The generated expression references its own table, so the return type must be annotated
    //    explicitly to break the circular inference (TS7022/TS7024).
    isSearchableComputed: boolean('isSearchableComputed').generatedAlwaysAs(
      (): SQL => sql`${pages.publishState} != 'draft' AND ${pages.isSearchable}`
    ),
    password: varchar({ length: 255 }),
    ratingScore: integer().notNull().default(0),
    ratingCount: timestamp().notNull().defaultNow(),
    scripts: jsonb().notNull().default({}),
    historyData: jsonb().notNull().default({}),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    authorId: uuid()
      .notNull()
      .references(() => users.id),
    creatorId: uuid()
      .notNull()
      .references(() => users.id),
    ownerId: uuid()
      .notNull()
      .references(() => users.id),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [
    index('pages_authorId_idx').on(table.authorId),
    index('pages_creatorId_idx').on(table.creatorId),
    index('pages_ownerId_idx').on(table.ownerId),
    index('pages_siteId_idx').on(table.siteId),
    index('pages_ts_idx').using('gin', table.ts),
    index('pages_tags_idx').using('gin', table.tags),
    index('pages_isSearchableComputed_idx').on(table.isSearchableComputed)
  ]
)

// PAGE HISTORY ------------------------
/**
 * One row per change to a page: what it looked like afterwards, who made it, and what kind of change
 * it was.
 *
 * Every row is a complete version rather than a delta, which is what makes the three things this
 * exists for straightforward: comparing any two versions, putting a page back to one of them, and
 * recovering a page that was deleted. The deletion itself is recorded the same way, carrying the page
 * as it stood when it went — that row is the whole of what a recovery needs.
 *
 * The render is deliberately not kept. It is derived from the content by a pipeline that lives in the
 * frontend, and storing a second copy of every page's HTML for every version is a great deal of space
 * for something a restore can regenerate.
 */
export const pageHistory = pgTable(
  'pageHistory',
  {
    id: uuid().primaryKey().defaultRandom(),
    // -> Not a foreign key: the history of a deleted page is exactly what recovering it needs, so it
    //    has to outlive the row it points at
    pageId: uuid().notNull(),
    /**
     * `created`, `updated`, `moved` or `deleted`. A varchar rather than an enum so that naming another
     * kind of change later does not need a migration.
     */
    action: varchar({ length: 16 }).notNull().default('updated'),
    /** Which fields this change touched, so a history list can summarise it without diffing. */
    changedFields: text()
      .array()
      .notNull()
      .default(sql`ARRAY[]::text[]`),
    /*
      Columns rather than part of `meta` below: a history list shows these for every row, a page that
      has moved needs the path it had at the time rather than the one it has now, and looking a history
      up by where the page was — the only way in once the page itself is gone — means matching on the
      locale and the path together.

      A locale code is BCP-47 with hyphens (`pt-BR`), and every comparison anywhere is an equality
      one. `locales.code`, which these values come from, is a varchar too.
    */
    locale: varchar({ length: 255 }).notNull(),
    path: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 255 }).notNull(),
    content: text(),
    /**
     * The rest of the page as it stood: description, icon, tags, publish state and dates, relations,
     * scripts, config, editor and content type. Kept whole rather than as columns of its own so that a
     * field added to a page does not have to be added here too.
     */
    meta: jsonb().notNull().default({}),
    /**
     * Why the change was made, in the author's words, as the editor's reason-for-change prompt
     * collected it. Null when the site does not ask for one, or asks and is not answered.
     */
    reason: varchar({ length: 255 }),
    versionDate: timestamp().notNull().defaultNow(),
    // -> Null once the account is gone, rather than holding the account hostage: a history row is a
    //    record of what happened to the page, and requiring its author to exist for ever would mean
    //    that editing a page once made an account undeletable — even after the page itself was gone.
    authorId: uuid().references(() => users.id, { onDelete: 'set null' }),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [
    index('pageHistory_pageId_idx').on(table.pageId, table.versionDate),
    // -> "What happened to the page at this path, in this locale", which is how a deleted page is
    //    found again: there is no page row left to look its ID up from. Leading with `siteId` means
    //    this also serves the plain per-site queries.
    index('pageHistory_siteId_idx').on(table.siteId, table.locale, table.path, table.versionDate),
    index('pageHistory_authorId_idx').on(table.authorId)
  ]
)

// PAGE EDIT SUBMISSIONS ---------------
/**
 * An edit suggested by somebody who may read a page but not change it, waiting to be reviewed.
 *
 * Both the resulting source and a patch are kept, because they answer different questions. The patch
 * is what a reviewer merges — it is computed against the page as it stood at submission time, so two
 * people suggesting edits to different parts of a page can both be accepted. The source is what the
 * author resumes from and what a review screen shows, and it cannot be reconstructed from the patch
 * alone once the page has moved on.
 */
export const pageEditSubmissions = pgTable(
  'pageEditSubmissions',
  {
    id: uuid().primaryKey().defaultRandom(),
    content: text().notNull(),
    /** Unified diff, from the page content this was based on to `content`. */
    patch: text().notNull(),
    /** SHA-256 of that base content, so a reviewer can tell the page has changed underneath. */
    baseHash: varchar({ length: 64 }).notNull(),
    // -> A guest has no account to attribute the suggestion to, so it says who sent it. Null for a
    //    logged in author, whose name is on `authorId` instead.
    guestName: varchar({ length: 255 }),
    guestEmail: varchar({ length: 255 }),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    pageId: uuid()
      .notNull()
      .references(() => pages.id, { onDelete: 'cascade' }),
    siteId: uuid()
      .notNull()
      .references(() => sites.id),
    authorId: uuid().references(() => users.id)
  },
  (table) => [
    index('pageEditSubmissions_pageId_idx').on(table.pageId),
    index('pageEditSubmissions_siteId_idx').on(table.siteId),
    index('pageEditSubmissions_authorId_idx').on(table.authorId),
    // -> One open suggestion per person per page: coming back to the button continues that one rather
    //    than starting a second. Guests are excluded because they are all the same nobody.
    uniqueIndex('pageEditSubmissions_page_author_idx')
      .on(table.pageId, table.authorId)
      .where(sql`"authorId" IS NOT NULL`)
  ]
)

// PAGE WATCHING -----------------------
/**
 * A page somebody asked to be told about, one row per person per page.
 *
 * A row IS the watch: there is no `isEnabled` to turn off, because unwatching a page is not a state a
 * page keeps — it is the absence of interest, and the row goes. Which is also why the whole table can
 * be read as "everyone to notify about this page" when notifications are built on top of it.
 *
 * `siteId` is carried alongside `pageId` rather than reached through the page, since every query here
 * is scoped to one site: the watch list belongs to an inbox, and an inbox belongs to a site.
 */
export const pageWatching = pgTable(
  'pageWatching',
  {
    id: uuid().primaryKey().defaultRandom(),
    createdAt: timestamp().notNull().defaultNow(),
    pageId: uuid()
      .notNull()
      .references(() => pages.id, { onDelete: 'cascade' }),
    siteId: uuid()
      .notNull()
      .references(() => sites.id),
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' })
  },
  (table) => [
    // -> Covers the site scoping too, being the leading column: this is the inbox's own query
    index('pageWatching_user_site_idx').on(table.userId, table.siteId),
    // -> Watching a page twice is watching it once, so the second attempt is a no-op rather than a row
    uniqueIndex('pageWatching_page_user_idx').on(table.pageId, table.userId)
  ]
)

// PAGE RENDER QUEUE -------------------
/**
 * A page waiting for the server to render it, one row per page.
 *
 * The markdown pipeline lives in the frontend, so rendering a page here means driving a headless
 * browser — too heavy to hold a request open for, and ruinous to do several times at once. A row is a
 * request for a render, and the `renderPages` task drains the table one page at a time through a
 * single browser (`models/rendering.ts`).
 *
 * A row IS the request, so asking twice for the same page updates the row instead of adding a second:
 * what gets rendered is the content as it stands when the browser reaches it, and rendering it twice
 * would produce the same HTML. `createdAt` keeps its place in the queue across those repeats.
 *
 * The two permissions travel with the row because a render is sanitized against what the person who
 * asked for it may embed, and by the time the job runs there is no session left to ask.
 */
export const pageRenderQueue = pgTable(
  'pageRenderQueue',
  {
    id: uuid().primaryKey().defaultRandom(),
    /** `write:scripts` — whether this render may keep `<script>` and inline handlers. */
    allowScripts: boolean().notNull().default(false),
    /** `write:styles` — whether this render may keep `<style>` and inline `style` attributes. */
    allowStyles: boolean().notNull().default(false),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    pageId: uuid()
      .notNull()
      .unique()
      .references(() => pages.id, { onDelete: 'cascade' }),
    siteId: uuid()
      .notNull()
      .references(() => sites.id),
    // -> Only ever logged, and a deleted account is no reason to drop a render somebody is waiting for
    requestedById: uuid().references(() => users.id, { onDelete: 'set null' })
  },
  // -> How the drain picks what to render next
  (table) => [index('pageRenderQueue_createdAt_idx').on(table.createdAt)]
)

// RATE LIMITS -------------------------
/**
 * One counter per rate-limited client, and the ban it has earned itself.
 *
 * In the database rather than in each instance's memory because a limit every instance enforces on
 * its own is a limit multiplied by however many are running — and because a ban has to hold when the
 * next attempt lands on another one. Every read and write of a row happens in a single upserting
 * statement (`models/rateLimits.ts`), which is what makes concurrent attempts count exactly once.
 *
 * Rows are self-correcting: an expired window or ban is reset by the next attempt on that key. They
 * are only ever deleted to reclaim space — see the `purgeRateLimits` task.
 */
export const rateLimits = pgTable(
  'rateLimits',
  {
    /** What is being limited and who by, e.g. `auth:203.0.113.4`. */
    key: varchar({ length: 255 }).primaryKey(),
    /** Attempts made inside the current window. */
    hits: integer().notNull().default(0),
    windowStartedAt: timestamp().notNull().defaultNow(),
    /** When the ban lifts. Null for a client that has not earned one. */
    bannedUntil: timestamp(),
    updatedAt: timestamp().notNull().defaultNow()
  },
  // -> How the purge finds rows nothing has touched in a long while
  (table) => [index('rateLimits_updatedAt_idx').on(table.updatedAt)]
)

// SETTINGS ----------------------------
export const settings = pgTable('settings', {
  key: varchar({ length: 255 }).notNull().primaryKey(),
  value: jsonb().notNull().default({})
})

// SESSIONS ----------------------------
export const sessions = pgTable(
  'sessions',
  {
    id: varchar({ length: 255 }).primaryKey(),
    userId: uuid().references(() => users.id),
    data: jsonb().notNull().default({}),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow()
  },
  (table) => [index('sessions_userId_idx').on(table.userId)]
)

// SITES -------------------------------
export const sites = pgTable('sites', {
  id: uuid().primaryKey().defaultRandom(),
  hostname: varchar({ length: 255 }).notNull().unique(),
  isEnabled: boolean().notNull().default(false),
  config: jsonb().notNull(),
  createdAt: timestamp().notNull().defaultNow()
})

// -> The images an administrator uploads for a site — its logo, favicon and login background — one row
//    per kind. Held in the database rather than under `dataPath`, which is a cache: an instance that
//    comes back with an empty data directory must still look like itself. Whether a kind has been
//    uploaded at all is mirrored in the site's `config.assets`, so serving a site that has uploaded
//    nothing costs no query here.
export const siteAssets = pgTable(
  'siteAssets',
  {
    siteId: uuid()
      .notNull()
      .references(() => sites.id),
    kind: varchar({ length: 255 }).notNull(),
    data: bytea().notNull()
  },
  (table) => [primaryKey({ columns: [table.siteId, table.kind] })]
)

// STORAGE -----------------------------
export const storage = pgTable(
  'storage',
  {
    id: uuid().primaryKey().defaultRandom(),
    // -> Directory name under `modules/storage`, one row per module per site
    module: varchar({ length: 255 }).notNull(),
    isEnabled: boolean().notNull().default(false),
    // -> `{ activeTypes: string[], largeThreshold: string }`
    contentTypes: jsonb().notNull().default({}),
    // -> `{ streaming: boolean, directAccess: boolean }`
    assetDelivery: jsonb().notNull().default({}),
    // -> `{ enabled: boolean }`
    versioning: jsonb().notNull().default({}),
    // -> Values for the props the module declares in its `definition.yml`
    config: jsonb().notNull().default({}),
    // -> Where the module stands, as opposed to how it is configured: `{ setup: 'notconfigured' |
    //    'pendinginstall' | 'configured' }` for a module that has a setup process to go through.
    state: jsonb().notNull().default({}),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  // -> Covers lookups by site as well, being the leading column
  (table) => [uniqueIndex('storage_composite_idx').on(table.siteId, table.module)]
)

// TAGS --------------------------------
export const tags = pgTable(
  'tags',
  {
    id: uuid().primaryKey().defaultRandom(),
    tag: varchar({ length: 255 }).notNull(),
    usageCount: integer().notNull().default(0),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [
    index('tags_siteId_idx').on(table.siteId),
    uniqueIndex('tags_composite_idx').on(table.siteId, table.tag)
  ]
)

// TREE --------------------------------
export const treeTypeEnum = pgEnum('treeType', ['folder', 'page', 'asset'])
export const treeNavigationModeEnum = pgEnum('treeNavigationMode', [
  'inherit',
  'override',
  'overrideExact',
  'hide',
  'hideExact'
])
export const tree = pgTable(
  'tree',
  {
    id: uuid().primaryKey().defaultRandom(),
    // -> Genuinely hierarchical, and queried as such with `<@`, `@>` and lquery: this is what ltree is
    //    for. The locale beside it is not, and is a plain string.
    folderPath: ltree('folderPath'),
    fileName: varchar({ length: 255 }).notNull(),
    hash: varchar({ length: 255 }).notNull(),
    type: treeTypeEnum('tree').notNull(),
    locale: varchar({ length: 255 }).notNull(),
    title: varchar({ length: 255 }).notNull(),
    navigationMode: treeNavigationModeEnum('navigationMode').notNull().default('inherit'),
    navigationId: uuid(),
    tags: text()
      .array()
      .notNull()
      .default(sql`ARRAY[]::text[]`),
    meta: jsonb().notNull().default({}),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow(),
    siteId: uuid()
      .notNull()
      .references(() => sites.id)
  },
  (table) => [
    index('tree_folderpath_idx').on(table.folderPath),
    index('tree_folderpath_gist_idx').using('gist', table.folderPath),
    index('tree_fileName_idx').on(table.fileName),
    index('tree_hash_idx').on(table.hash),
    index('tree_type_idx').on(table.type),
    // -> A plain btree: the locale is a string compared for equality, and GiST — which is what an
    //    ltree column wanted — has no operator class for varchar at all
    index('tree_locale_idx').on(table.locale),
    index('tree_navigationMode_idx').on(table.navigationMode),
    index('tree_navigationId_idx').on(table.navigationId),
    index('tree_tags_idx').using('gin', table.tags),
    index('tree_siteId_idx').on(table.siteId)
  ]
)

// USER AVATARS ------------------------
export const userAvatars = pgTable('userAvatars', {
  id: uuid().primaryKey(),
  data: bytea().notNull()
})

// USER KEYS ---------------------------
export const userKeys = pgTable(
  'userKeys',
  {
    id: uuid().primaryKey().defaultRandom(),
    kind: varchar({ length: 255 }).notNull(),
    token: varchar({ length: 255 }).notNull(),
    meta: jsonb().notNull().default({}),
    createdAt: timestamp().notNull().defaultNow(),
    validUntil: timestamp().notNull(),
    userId: uuid()
      .notNull()
      .references(() => users.id)
  },
  (table) => [index('userKeys_userId_idx').on(table.userId)]
)

// USERS -------------------------------
export const users = pgTable(
  'users',
  {
    id: uuid().primaryKey().defaultRandom(),
    email: varchar({ length: 255 }).notNull().unique(),
    name: varchar({ length: 255 }).notNull(),
    auth: jsonb().notNull().default({}),
    meta: jsonb().notNull().default({}),
    passkeys: jsonb().notNull().default({}),
    prefs: jsonb().notNull().default({}),
    hasAvatar: boolean().notNull().default(false),
    isActive: boolean().notNull().default(false),
    isSystem: boolean().notNull().default(false),
    isVerified: boolean().notNull().default(false),
    lastLoginAt: timestamp(),
    createdAt: timestamp().notNull().defaultNow(),
    updatedAt: timestamp().notNull().defaultNow()
  },
  (table) => [index('users_lastLoginAt_idx').on(table.lastLoginAt)]
)

// == RELATION TABLES ==================

// USER GROUPS -------------------------
export const userGroups = pgTable(
  'userGroups',
  {
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    groupId: uuid()
      .notNull()
      .references(() => groups.id, { onDelete: 'cascade' })
  },
  (table) => [
    primaryKey({ columns: [table.userId, table.groupId] }),
    index('userGroups_userId_idx').on(table.userId),
    index('userGroups_groupId_idx').on(table.groupId),
    index('userGroups_composite_idx').on(table.userId, table.groupId)
  ]
)