import { sql } from "drizzle-orm";
import { index, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";

// `email` is required: tessera asserts `email_verified=true` on every new
// user (see auth/index.ts databaseHooks.user.create.before), and that claim
// is only honest when the invite address itself proves email ownership.
//
// Single-use consume is enforced by a CAS in api/invites.ts:
// `UPDATE invites SET consumed_at = ?
//    WHERE token_hash = ? AND consumed_at IS NULL AND expires_at > ?`.
// Both placeholders are JS ISO timestamps so lexicographic comparison
// matches chronological order.
// SQLite serializes writers; one request sets the column and concurrent
// callers see zero affected rows.
export const invites = sqliteTable(
  "invites",
  {
    id: text("id").primaryKey(),
    tokenHash: text("token_hash").notNull(),
    email: text("email").notNull(),
    createdBy: text("created_by").notNull(),
    createdAt: text("created_at")
      .notNull()
      .default(sql`(current_timestamp)`),
    expiresAt: text("expires_at").notNull(),
    consumedAt: text("consumed_at"),
  },
  (table) => [
    uniqueIndex("idx_invites_token_hash").on(table.tokenHash),
    // One invite row per email, consumed or not. Admins must DELETE
    // before re-issuing for the same address; the application also
    // refuses minting when a `users` row with that email already exists
    // (see `handleCreateInvite`). Defense-in-depth against TOCTOU
    // between two concurrent admin mints.
    uniqueIndex("idx_invites_email").on(table.email),
    index("idx_invites_created_by_created_at").on(table.createdBy, table.createdAt),
    index("idx_invites_expires_at").on(table.expiresAt),
  ],
);