// ---------------------------------------------------------------------------
// Cloud-specific identity & multi-tenancy tables
// ---------------------------------------------------------------------------
//
// AuthKit owns the canonical user/membership data. We mirror it locally:
//
//   - `accounts`       — login identity + profile (foreign key anchor for
//                        created_by, etc.; email/name/avatar for member lists)
//   - `organizations`  — billing entity, scoping root for all domain data
//   - `memberships`    — which accounts belong to which organizations, with
//                        the WorkOS role and status
//   - `workos_sync`    — the WorkOS Events API cursor the reconciler resumes
//                        from, and the replay boundary the one-off backfill
//                        records
//
// The mirror is fed by login (the callback has the user + memberships in
// hand), write-through on every Executor-initiated change, and the WorkOS
// Events API (dashboard-side changes). It is the read path for membership and
// member lists — WorkOS is a write target and an event source, never a
// per-request read. Invitations are NOT mirrored; they stay live in WorkOS.
//
// `workos_updated_at` on `accounts` and `memberships` is the WorkOS
// `updatedAt` of the payload that last wrote the row. Every upsert is guarded
// on it, so feeders can be replayed and reordered without an older payload
// clobbering a newer one.

import { sql } from "drizzle-orm";
import { index, pgTable, primaryKey, text, timestamp, uniqueIndex } from "drizzle-orm/pg-core";

/**
 * Login identity + mirrored WorkOS profile. The `id` is the WorkOS user ID.
 * Profile columns are nullable because a row can be minted by `ensureAccount`
 * (an api-key path, a membership arriving before its user event) with nothing
 * but the id; the next user payload fills them in.
 */
export const accounts = pgTable(
  "accounts",
  {
    id: text("id").primaryKey(),
    email: text("email"),
    firstName: text("first_name"),
    lastName: text("last_name"),
    avatarUrl: text("avatar_url"),
    /** WorkOS `updatedAt` of the user payload that last wrote this row. */
    workosUpdatedAt: timestamp("workos_updated_at", { withTimezone: true }),
    lastSignInAt: timestamp("last_sign_in_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // `findByEmail` and the search filter compare lower-cased; the index
    // matches that expression so the lookup stays indexed.
    emailLowerIdx: index("accounts_email_lower_idx").on(sql`lower(${t.email})`),
  }),
);

/**
 * Organization (billing entity, scoping root). The `id` is the WorkOS
 * organization ID. The `slug` is OURS, not WorkOS's (their org object has no
 * slug): minted at the moment a row is inserted (the single mint point is
 * `upsertOrganization`) and stable across renames so org URLs don't break.
 * NOT NULL — there is no nullable window: legacy rows were backfilled once and
 * every insert since carries a slug.
 */
export const organizations = pgTable(
  "organizations",
  {
    id: text("id").primaryKey(),
    name: text("name").notNull(),
    slug: text("slug").notNull(),
    /**
     * When this organization's membership list was last FULLY scanned from
     * WorkOS (the one-off backfill, or the on-demand scan a seat count
     * triggers), or null if it never was. Until then the mirror may hold only
     * the members login and write-through happened to record, so a count read
     * from it is partial; every seat gate checks this mark first. Per
     * organization, never database-wide: an org mirrored lazily after a
     * backfill ran starts unmarked and is scanned on its first count. The
     * mark also orders membership writes: a payload stamped before it is
     * refused, since the scan was the full listing at that instant and a
     * membership it did not contain was revoked before it — before the
     * events replay boundary, so nothing would tombstone it again.
     */
    backfilledAt: timestamp("backfilled_at", { withTimezone: true }),
    /**
     * When this organization was deleted, or null while it is live. Set FIRST
     * by cloud's own deletion flow (`auth/handlers.ts` deleteOrganization),
     * before the WorkOS delete and the local purge, and by the
     * `organization.deleted` event for an org deleted in the WorkOS dashboard
     * — which MINTS the row as a tombstone when the mirror has never seen the
     * organization: membership is authorized from the local mirror, so a
     * marked organization refuses every session at once, whether or not the
     * later steps land. Membership rows are left as they are until the purge
     * (so the admin who started the deletion can retry it after a step
     * failed), and the purge (`db/org-deletion.ts`) removes them but KEEPS
     * this row as a tombstone: a feeder that fetched a membership before the
     * deletion and writes it after (a login that stalled across the deletion)
     * finds the tombstone and does not mint the organization live. A marked
     * organization is never renamed.
     */
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
    /**
     * The instant the stored `name` is known to have been the organization's
     * name in WorkOS: the WorkOS `updatedAt` of the organization payload that
     * wrote it, or — for a name learned from a membership list at sign-in,
     * which carries no organization timestamp — the instant that list was
     * fetched. A name write stamped earlier than this is refused
     * (`upsertOrganization`), so a sign-in whose list predates a rename cannot
     * revert it. Null only on rows written before the stamp existed.
     */
    workosUpdatedAt: timestamp("workos_updated_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    slugUnique: uniqueIndex("organizations_slug_unique").on(t.slug),
  }),
);

/**
 * Account ↔ organization link, mirroring the WorkOS organization membership.
 * Answers "which workspaces does this account belong to?" and "is this caller
 * an active member with which role?" without a WorkOS round-trip, and gives
 * per-(account, organization) data a foreign key to point at.
 *
 * `membershipId` is the WorkOS `om_…` id — nullable only because rows written
 * before the mirror existed carry none; every feeder sets it. `role` is the
 * WorkOS role slug as issued (`admin` / `member`); `status` is the WorkOS
 * membership status (`active` / `pending` / `inactive`). A membership deleted
 * in WorkOS is never dropped here: it is tombstoned as `inactive` with
 * `deleted_at` set, so a feeder replaying an older payload cannot resurrect it.
 *
 * `deleted_at` means "the membership under `membership_id` was DELETED in
 * WorkOS" — an identity fact, not a timestamp to order payloads by. WorkOS
 * never reuses a deleted `om_…` id, so any payload naming that id is stale
 * however it is stamped, and a payload naming a DIFFERENT id for the same
 * (account, organization) is the member re-added: a replacement, ordered by
 * `workos_updated_at` like every other write. Distinct from `status =
 * 'inactive'` with `deleted_at` null, which is a membership WorkOS
 * deactivated but still holds and can reactivate under the same id.
 */
export const memberships = pgTable(
  "memberships",
  {
    accountId: text("account_id")
      .notNull()
      .references(() => accounts.id, { onDelete: "cascade" }),
    organizationId: text("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    membershipId: text("membership_id"),
    role: text("role").notNull().default("member"),
    status: text("status", { enum: ["active", "pending", "inactive"] })
      .notNull()
      .default("active"),
    /** WorkOS `updatedAt` of the membership payload that last wrote this row. */
    workosUpdatedAt: timestamp("workos_updated_at", { withTimezone: true }),
    /**
     * When the membership under `membership_id` was deleted in WorkOS, or null
     * while WorkOS still holds it (whatever its `status`). Set once; cleared
     * only when a replacement membership (another id) takes the row over.
     */
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.accountId, t.organizationId] }),
    membershipIdUnique: uniqueIndex("memberships_membership_id_unique").on(t.membershipId),
    organizationIdx: index("memberships_organization_id_idx").on(t.organizationId),
  }),
);

/**
 * Every WorkOS membership id (`om_…`) a DELETE has named, keyed by that id
 * alone. The `memberships` row is keyed by (account, organization) and holds
 * ONE membership id, so a row-level tombstone can only record the deletion of
 * the id the row happens to carry: a delete of membership B arriving while
 * the row still holds an older membership A of the same pair (A replaced by
 * B in WorkOS before the mirror saw either, B then deleted) has no row to
 * tombstone, and a later payload of B — stamped after A, under another id —
 * would take the row over live. This ledger is what the mirror consults
 * instead: a delete always records the id here, whatever the row holds, and
 * no membership write ever names a recorded id again, however it is stamped
 * — WorkOS never reuses a deleted `om_…` id. `deleted_at` records when; it
 * orders nothing. Rows cascade with their account and organization.
 */
export const membershipTombstones = pgTable(
  "membership_tombstones",
  {
    membershipId: text("membership_id").primaryKey(),
    accountId: text("account_id")
      .notNull()
      .references(() => accounts.id, { onDelete: "cascade" }),
    organizationId: text("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    deletedAt: timestamp("deleted_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    organizationIdx: index("membership_tombstones_organization_id_idx").on(t.organizationId),
  }),
);

/**
 * The WorkOS Events API sync state. One row per stream (`id` names the
 * stream; the reconciler uses `"events"`), holding the id of the last event
 * applied. Advanced only by compare-and-set, so two concurrent reconciler
 * runs cannot both believe they own the stream: the loser's CAS fails and it
 * stops.
 *
 * `range_start` on the `"events"` row is the REPLAY BOUNDARY: the instant the
 * FIRST one-off backfill run (`scripts/backfill-workos-mirror.ts`) began
 * reading WorkOS, recorded BEFORE its first listing. Everything before it is
 * covered by that backfill; the reconciler's first run (no cursor yet) reads
 * the events stream from here, so a revocation between the backfill and the
 * first run is never skipped. Written once: a run that fails part-way leaves
 * it standing and its retry keeps it, and a later run keeps it too, because
 * the backfill does not refresh everything the events stream carries
 * (organization renames, deleted users' profiles) — those after the first
 * boundary are replayed from it. Without a cursor or a boundary the
 * reconciler does not guess; it waits for the backfill.
 *
 * `backfill_completed_at` is when a backfill run first wrote EVERY live
 * organization (`scripts/backfill-workos-mirror.ts` completing, or refusing
 * an organization only because a later listing was already applied). Until
 * it is set, the mirror may lack members who have not signed in since it
 * shipped, so a membership check read from it would deny them: it is the
 * first half of the mirror-readiness mark the authorization path consults
 * before trusting the mirror over WorkOS. Write-once — a later completed run
 * keeps the first instant, so readiness never flips back. Per-organization
 * completeness for the seat gates is tracked separately
 * (`organizations.backfilled_at`).
 *
 * `drained_at` is when a reconciler run last read the events stream to its
 * END (an empty page, or a page with nothing after it) — the second half of
 * the readiness mark: a mirror whose reconciler has not caught up recently
 * may still grant a member WorkOS already revoked, so the authorization
 * path trusts the mirror only while this is within its lag budget. Moved
 * forward by every draining run; never cleared. A run that stops at its
 * page budget or yields to another run leaves it as it was.
 *
 * Migration 0019 seeds the boundary and the completion mark on a database
 * with no organizations, where there is nothing to backfill; `drained_at`
 * is left for the reconciler's first run to set (migration 0020).
 */
export const workosSync = pgTable("workos_sync", {
  id: text("id").primaryKey(),
  cursor: text("cursor"),
  rangeStart: timestamp("range_start", { withTimezone: true }),
  backfillCompletedAt: timestamp("backfill_completed_at", { withTimezone: true }),
  drainedAt: timestamp("drained_at", { withTimezone: true }),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});