/**
 * OAuth 2.0 Authorization Code flow tables — oauth-authorization-code (wave-12).
 *
 * Five tables:
 * - oauth_clients:              registered OAuth apps (first-party + third-party)
 * - oauth_authorization_codes:  short-lived single-use codes (10 min)
 * - oauth_access_tokens:        opaque bearer tokens (1 h)
 * - oauth_refresh_tokens:       rotation family tokens (60 days)
 * - oauth_connections:          active user↔app grants (for Settings > Integrations)
 *
 * Design decisions:
 * - All PKs are UUID. `client_id` is a TEXT UNIQUE public wire identifier.
 * - All FKs are UUID→UUID; no TEXT→TEXT FK.
 * - `redirect_uris` and `scopes` are JSONB arrays.
 * - `code_challenge_method` enforced via CHECK IN ('S256').
 * - GIN/expression indexes are NOT needed here; all lookup indexes are plain B-tree.
 *   idx_oauth_rt_family / idx_oauth_at_family are plain index() — no raw SQL appendix.
 *
 * IMPORTANT: Do NOT collide with `oauth_accounts` (social-login) owned by foundation-auth-rbac.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  timestamp,
  index,
  unique,
  check,
  type AnyPgColumn,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── oauth_clients ─────────────────────────────────────────────────────────────

export const oauthClients = pgTable(
  'oauth_clients',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    /** Public wire identifier — used in all OAuth protocol messages. */
    clientId: text('client_id').notNull().unique(),
    /** SHA-256(plaintext_secret); null for public clients (PKCE-only). */
    clientSecretHash: text('client_secret_hash').notNull(),
    name: text('name').notNull(),
    /** JSONB array of allowed redirect_uri strings (exact-match enforced in app). */
    redirectUris: jsonb('redirect_uris').notNull().default(sql`'[]'::jsonb`),
    /** JSONB array of allowed scope strings. */
    scopes: jsonb('scopes').notNull().default(sql`'[]'::jsonb`),
    /** First-party clients (Zync mobile, admin tools) skip the consent screen. */
    isFirstParty: boolean('is_first_party').notNull().default(false),
    logoUrl: text('logo_url'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    clientIdIdx: index('idx_oauth_clients_client_id').on(t.clientId),
  }),
)

export type OAuthClientRow = typeof oauthClients.$inferSelect
export type NewOAuthClient = typeof oauthClients.$inferInsert

// ── oauth_authorization_codes ─────────────────────────────────────────────────

export const oauthAuthorizationCodes = pgTable(
  'oauth_authorization_codes',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    /** Random opaque 32-char code — only hash stored; wire value returned at issue. */
    code: text('code').notNull().unique(),
    oauthClientId: uuid('oauth_client_id')
      .notNull()
      .references(() => oauthClients.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    redirectUri: text('redirect_uri').notNull(),
    /** Space-separated granted scopes. */
    scope: text('scope').notNull(),
    /** PKCE S256 challenge — base64url(SHA-256(code_verifier)), no padding. */
    codeChallenge: text('code_challenge'),
    codeChallengeMethod: text('code_challenge_method'),
    /** 10 minutes from issue. */
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    /** Single-use marker — set when the code is exchanged. */
    usedAt: timestamp('used_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    codeIdx: index('idx_oauth_codes_code').on(t.code),
    codeChallengeMethodCheck: check(
      'oauth_authorization_codes_ccm_check',
      sql`${t.codeChallengeMethod} IS NULL OR ${t.codeChallengeMethod} IN ('S256')`,
    ),
  }),
)

export type OAuthAuthorizationCodeRow = typeof oauthAuthorizationCodes.$inferSelect
export type NewOAuthAuthorizationCode = typeof oauthAuthorizationCodes.$inferInsert

// ── oauth_access_tokens ───────────────────────────────────────────────────────

export const oauthAccessTokens = pgTable(
  'oauth_access_tokens',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    /** SHA-256(opaque bearer token). */
    tokenHash: text('token_hash').notNull().unique(),
    oauthClientId: uuid('oauth_client_id')
      .notNull()
      .references(() => oauthClients.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    /** Space-separated granted scopes. */
    scope: text('scope').notNull(),
    /** Token family — same as the refresh token family that minted this access token. */
    familyId: uuid('family_id').notNull(),
    /** 1 hour from issue. */
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tokenHashIdx: index('idx_oauth_at_hash').on(t.tokenHash),
    familyIdx: index('idx_oauth_at_family').on(t.familyId),
  }),
)

export type OAuthAccessTokenRow = typeof oauthAccessTokens.$inferSelect
export type NewOAuthAccessToken = typeof oauthAccessTokens.$inferInsert

// ── oauth_refresh_tokens ──────────────────────────────────────────────────────

export const oauthRefreshTokens = pgTable(
  'oauth_refresh_tokens',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    /** SHA-256(opaque refresh token). */
    tokenHash: text('token_hash').notNull().unique(),
    oauthClientId: uuid('oauth_client_id')
      .notNull()
      .references(() => oauthClients.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    /** Space-separated granted scopes. */
    scope: text('scope').notNull(),
    /** Constant UUID across all rotations of one grant — used for family revoke. */
    familyId: uuid('family_id').notNull(),
    /** UUID of successor token (set when this token is rotated/consumed). Null until rotated. */
    rotatedToId: uuid('rotated_to_id').references((): AnyPgColumn => oauthRefreshTokens.id),
    /** 60 days from issue. */
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tokenHashIdx: index('idx_oauth_rt_hash').on(t.tokenHash),
    familyIdx: index('idx_oauth_rt_family').on(t.familyId),
  }),
)

export type OAuthRefreshTokenRow = typeof oauthRefreshTokens.$inferSelect
export type NewOAuthRefreshToken = typeof oauthRefreshTokens.$inferInsert

// ── oauth_connections ─────────────────────────────────────────────────────────

export const oauthConnections = pgTable(
  'oauth_connections',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    oauthClientId: uuid('oauth_client_id')
      .notNull()
      .references(() => oauthClients.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    /** Space-separated granted scopes. */
    scope: text('scope').notNull(),
    /** Set on refresh-token reuse detection (compromise flag). */
    flaggedAt: timestamp('flagged_at', { withTimezone: true }),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    uniqueClientTenantUser: unique('oauth_connections_client_tenant_user_key').on(
      t.oauthClientId,
      t.tenantId,
      t.userId,
    ),
    tenantUserIdx: index('idx_oauth_connections_tenant_user').on(t.tenantId, t.userId),
  }),
)

export type OAuthConnectionRow = typeof oauthConnections.$inferSelect
export type NewOAuthConnection = typeof oauthConnections.$inferInsert