/** Canonical database contracts. Domain authority: docs/ONTOLOGY.md. */
import { sql } from 'drizzle-orm'
import {
  boolean,
  check,
  date,
  foreignKey,
  integer,
  numeric,
  pgEnum,
  index,
  jsonb,
  pgPolicy,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core'
export const cycleStatus = pgEnum('cycle_status', ['active', 'archived'])
export const objectiveStatus = pgEnum('objective_status', ['on_track', 'off_track', 'completed'])
export const metricType = pgEnum('metric_type', [
  'number',
  'percentage',
  'milestone',
  'habit',
  'performance',
])
export const resultStatus = pgEnum('result_status', ['in_progress', 'completed'])
export const habitCadence = pgEnum('habit_cadence', ['weekly', 'monthly'])
export const actionStatus = pgEnum('action_status', ['open', 'in_progress', 'done'])
export const sessionVendor = pgEnum('session_vendor', ['claude', 'openai'])
export const sessionType = pgEnum('session_type', [
  'goal_setup',
  'check_in',
  'review',
  'open_coaching',
])
const TENANT_USING = sql`user_id = nullif(current_setting('app.user_id', true), '')::uuid`
export const user = pgTable(
  'user',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull().default(''),
    email: text('email').notNull().unique(),
    emailVerified: boolean('email_verified').notNull().default(false),
    image: text('image'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    stripeCustomerId: text('stripe_customer_id').unique(),
    aboutMe: text('about_me').notNull().default(''),
    timezone: text('timezone').notNull().default('UTC'),
  },
  (table) => [
    pgPolicy('user_self_isolation', {
      for: 'all',
      to: 'authenticated',
      using: sql`${table.id} = nullif(current_setting('app.user_id', true), '')::uuid`,
      withCheck: sql`${table.id} = nullif(current_setting('app.user_id', true), '')::uuid`,
    }),
  ],
).enableRLS()
export const authSession = pgTable(
  'auth_session',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    token: text('token').notNull().unique(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    ipAddress: text('ip_address'),
    userAgent: text('user_agent'),
    userId: uuid('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
  },
  (table) => [
    index('session_user_id_idx').on(table.userId),
    pgPolicy('session_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
export const account = pgTable(
  'account',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    accountId: text('account_id').notNull(),
    providerId: text('provider_id').notNull(),
    userId: uuid('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    accessToken: text('access_token'),
    refreshToken: text('refresh_token'),
    idToken: text('id_token'),
    accessTokenExpiresAt: timestamp('access_token_expires_at', { withTimezone: true }),
    refreshTokenExpiresAt: timestamp('refresh_token_expires_at', { withTimezone: true }),
    scope: text('scope'),
    password: text('password'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('account_user_id_idx').on(table.userId),
    pgPolicy('account_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
export const verification = pgTable(
  'verification',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    identifier: text('identifier').notNull(),
    value: text('value').notNull(),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index('verification_identifier_idx').on(table.identifier)],
)
export const jwks = pgTable(
  'jwks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    publicKey: text('public_key').notNull(),
    privateKey: text('private_key').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    expiresAt: timestamp('expires_at', { withTimezone: true }),
    alg: text('alg'),
    crv: text('crv'),
  },
  (table) => [],
)
export const oauthClient = pgTable(
  'oauth_client',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    clientId: text('client_id').notNull().unique(),
    clientSecret: text('client_secret'),
    clientDiscoveryId: text('client_discovery_id'),
    disabled: boolean('disabled').default(false),
    skipConsent: boolean('skip_consent'),
    enableEndSession: boolean('enable_end_session'),
    subjectType: text('subject_type'),
    scopes: text('scopes').array(),
    clientCredentialsScopes: text('client_credentials_scopes').array().default([]),
    userId: uuid('user_id').references(() => user.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow(),
    name: text('name'),
    uri: text('uri'),
    icon: text('icon'),
    contacts: text('contacts').array(),
    tos: text('tos'),
    policy: text('policy'),
    softwareId: text('software_id'),
    softwareVersion: text('software_version'),
    softwareStatement: text('software_statement'),
    redirectUris: text('redirect_uris').array().notNull(),
    postLogoutRedirectUris: text('post_logout_redirect_uris').array(),
    backchannelLogoutUri: text('backchannel_logout_uri'),
    backchannelLogoutSessionRequired: boolean('backchannel_logout_session_required'),
    tokenEndpointAuthMethod: text('token_endpoint_auth_method'),
    applicationType: text('application_type'),
    jwks: text('jwks'),
    jwksUri: text('jwks_uri'),
    grantTypes: text('grant_types').array(),
    responseTypes: text('response_types').array(),
    requirePKCE: boolean('require_pkce'),
    dpopBoundAccessTokens: boolean('dpop_bound_access_tokens').default(false),
    referenceId: text('reference_id'),
    metadata: jsonb('metadata'),
  },
  (table) => [
    index('oauth_client_user_id_idx').on(table.userId),
    index('oauth_client_client_id_idx').on(table.clientId),
  ],
)
export const oauthAccessToken = pgTable(
  'oauth_access_token',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    token: text('token').unique().notNull(),
    clientId: text('client_id')
      .notNull()
      .references(() => oauthClient.clientId, { onDelete: 'cascade' }),
    sessionId: uuid('session_id').references(() => authSession.id, {
      onDelete: 'set null',
    }),
    userId: uuid('user_id').references(() => user.id, { onDelete: 'cascade' }),
    referenceId: text('reference_id'),
    authorizationCodeId: text('authorization_code_id'),
    resources: text('resources').array(),
    requestedUserInfoClaims: text('requested_user_info_claims').array(),
    refreshId: uuid('refresh_id').references(() => oauthRefreshToken.id, {
      onDelete: 'cascade',
    }),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    revoked: timestamp('revoked', { withTimezone: true }),
    confirmation: jsonb('confirmation'),
    scopes: text('scopes').array().notNull(),
  },
  (table) => [
    index('oauth_access_token_client_id_idx').on(table.clientId),
    index('oauth_access_token_session_id_idx').on(table.sessionId),
    index('oauth_access_token_user_id_idx').on(table.userId),
    index('oauth_access_token_authorization_code_id_idx').on(table.authorizationCodeId),
    index('oauth_access_token_refresh_id_idx').on(table.refreshId),
    pgPolicy('oauth_access_token_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
export const oauthRefreshToken = pgTable(
  'oauth_refresh_token',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    token: text('token').notNull().unique(),
    clientId: text('client_id')
      .notNull()
      .references(() => oauthClient.clientId, { onDelete: 'cascade' }),
    sessionId: uuid('session_id').references(() => authSession.id, {
      onDelete: 'set null',
    }),
    userId: uuid('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    referenceId: text('reference_id'),
    authorizationCodeId: text('authorization_code_id'),
    resources: text('resources').array(),
    requestedUserInfoClaims: text('requested_user_info_claims').array(),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    revoked: timestamp('revoked', { withTimezone: true }),
    rotatedAt: timestamp('rotated_at', { withTimezone: true }),
    rotationReplayResponse: text('rotation_replay_response'),
    rotationReplayExpiresAt: timestamp('rotation_replay_expires_at', { withTimezone: true }),
    authTime: timestamp('auth_time', { withTimezone: true }),
    confirmation: jsonb('confirmation'),
    scopes: text('scopes').array().notNull(),
  },
  (table) => [
    index('oauth_refresh_token_client_id_idx').on(table.clientId),
    index('oauth_refresh_token_session_id_idx').on(table.sessionId),
    index('oauth_refresh_token_user_id_idx').on(table.userId),
    index('oauth_refresh_token_authorization_code_id_idx').on(table.authorizationCodeId),
    pgPolicy('oauth_refresh_token_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
export const oauthConsent = pgTable(
  'oauth_consent',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    clientId: text('client_id')
      .notNull()
      .references(() => oauthClient.clientId, { onDelete: 'cascade' }),
    userId: uuid('user_id').references(() => user.id, { onDelete: 'cascade' }),
    referenceId: text('reference_id'),
    resources: text('resources').array(),
    requestedUserInfoClaims: text('requested_user_info_claims').array(),
    scopes: text('scopes').array().notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('oauth_consent_client_id_idx').on(table.clientId),
    index('oauth_consent_user_id_idx').on(table.userId),
    pgPolicy('oauth_consent_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
export const productEvents = pgTable(
  'product_events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id').references(() => user.id, { onDelete: 'set null' }),
    eventType: text('event_type').notNull(),
    occurredAt: timestamp('occurred_at', { withTimezone: true }).notNull().defaultNow(),
    properties: jsonb('properties'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('product_events_user_id_idx').on(table.userId),
    index('product_events_event_type_idx').on(table.eventType),
    pgPolicy('product_events_tenant_insert', {
      for: 'insert',
      to: 'authenticated',
      withCheck: sql`${table.userId} = nullif(current_setting('app.user_id', true), '')::uuid`,
    }),
  ],
).enableRLS()
export const idempotencyKeys = pgTable(
  'idempotency_keys',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    operation: text('operation').notNull(),
    idempotencyKey: text('idempotency_key').notNull(),
    requestHash: text('request_hash').notNull(),
    responseRef: text('response_ref'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    uniqueIndex('idempotency_keys_user_op_key_uidx').on(
      table.userId,
      table.operation,
      table.idempotencyKey,
    ),
    pgPolicy('idempotency_keys_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
const tenantPolicy = (name: string) =>
  pgPolicy(`${name}_tenant_isolation`, {
    for: 'all',
    to: 'authenticated',
    using: TENANT_USING,
    withCheck: TENANT_USING,
  })
const productId = () => uuid('id').primaryKey().defaultRandom()
const tenantId = () =>
  uuid('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' })
const created = () => timestamp('created_at', { withTimezone: true }).notNull().defaultNow()
const updated = () => timestamp('updated_at', { withTimezone: true }).notNull().defaultNow()
export const cycles = pgTable(
  'cycles',
  {
    id: productId(),
    userId: tenantId(),
    intention: text('intention'),
    startAt: date('start_at').notNull(),
    endAt: date('end_at').notNull(),
    status: cycleStatus('status').notNull().default('active'),
    createdAt: created(),
    updatedAt: updated(),
  },
  (t) => [
    uniqueIndex('cycles_id_user_uidx').on(t.id, t.userId),
    index('cycles_user_id_idx').on(t.userId),
    uniqueIndex('cycles_one_active_uidx')
      .on(t.userId)
      .where(sql`${t.status} = 'active'`),
    check('cycles_date_range_check', sql`${t.endAt} > ${t.startAt}`),
    tenantPolicy('cycles'),
  ],
).enableRLS()
export const objectives = pgTable(
  'objectives',
  {
    id: productId(),
    userId: tenantId(),
    cycleId: uuid('cycle_id').notNull(),
    title: text('title').notNull(),
    description: text('description').notNull().default(''),
    deadlineAt: date('deadline_at'),
    status: objectiveStatus('status').notNull().default('on_track'),
    createdAt: created(),
    updatedAt: updated(),
  },
  (t) => [
    uniqueIndex('objectives_id_user_uidx').on(t.id, t.userId),
    uniqueIndex('objectives_id_cycle_user_uidx').on(t.id, t.cycleId, t.userId),
    index('objectives_user_id_idx').on(t.userId),
    index('objectives_cycle_user_idx').on(t.cycleId, t.userId),
    foreignKey({
      name: 'objectives_cycle_tenant_fk',
      columns: [t.cycleId, t.userId],
      foreignColumns: [cycles.id, cycles.userId],
    }).onDelete('cascade'),
    tenantPolicy('objectives'),
  ],
).enableRLS()
export const sessions = pgTable(
  'sessions',
  {
    id: productId(),
    userId: tenantId(),
    vendor: sessionVendor('vendor').notNull(),
    sourceUrl: text('source_url'),
    type: sessionType('type'),
    briefingNotes: text('briefing_notes').notNull(),
    headline: text('headline'),
    summaryNotes: text('summary_notes'),
    startedAt: timestamp('started_at', { withTimezone: true }).notNull().defaultNow(),
    endedAt: timestamp('ended_at', { withTimezone: true }),
    createdAt: created(),
    updatedAt: updated(),
  },
  (t) => [
    uniqueIndex('sessions_id_user_uidx').on(t.id, t.userId),
    index('sessions_user_id_idx').on(t.userId),
    uniqueIndex('sessions_one_open_uidx')
      .on(t.userId)
      .where(sql`${t.endedAt} IS NULL`),
    index('sessions_boundary_idx')
      .on(t.userId, t.endedAt.desc())
      .where(sql`${t.endedAt} IS NOT NULL`),
    tenantPolicy('sessions'),
  ],
).enableRLS()
export const results = pgTable(
  'results',
  {
    id: productId(),
    userId: tenantId(),
    objectiveId: uuid('objective_id').notNull(),
    title: text('title').notNull(),
    description: text('description'),
    metricType: metricType('metric_type').notNull(),
    startValue: numeric('start_value', { mode: 'number' }),
    targetValue: numeric('target_value', { mode: 'number' }),
    unit: text('unit'),
    cadence: habitCadence('cadence'),
    per: integer('per'),
    since: date('since'),
    status: resultStatus('status').notNull().default('in_progress'),
    createdAt: created(),
    updatedAt: updated(),
  },
  (t) => [
    uniqueIndex('results_id_user_uidx').on(t.id, t.userId),
    index('results_user_id_idx').on(t.userId),
    index('results_objective_user_idx').on(t.objectiveId, t.userId),
    foreignKey({
      name: 'results_objective_tenant_fk',
      columns: [t.objectiveId, t.userId],
      foreignColumns: [objectives.id, objectives.userId],
    }).onDelete('cascade'),
    check(
      'results_number_check',
      sql`${t.metricType} <> 'number' OR (${t.startValue} IS NOT NULL AND ${t.targetValue} IS NOT NULL AND ${t.cadence} IS NULL AND ${t.per} IS NULL AND ${t.since} IS NULL)`,
    ),
    check(
      'results_percentage_check',
      sql`${t.metricType} <> 'percentage' OR (${t.startValue} IS NOT NULL AND ${t.targetValue} IS NOT NULL AND ${t.startValue} BETWEEN 0 AND 100 AND ${t.targetValue} BETWEEN 0 AND 100 AND ${t.cadence} IS NULL AND ${t.per} IS NULL AND ${t.since} IS NULL AND ${t.unit} IS NULL)`,
    ),
    check(
      'results_milestone_check',
      sql`${t.metricType} <> 'milestone' OR (${t.startValue} IS NOT NULL AND ${t.startValue} = 0 AND ${t.targetValue} IS NOT NULL AND ${t.targetValue} = 1 AND ${t.cadence} IS NULL AND ${t.per} IS NULL AND ${t.since} IS NULL AND ${t.unit} IS NULL)`,
    ),
    check(
      'results_habit_check',
      sql`${t.metricType} <> 'habit' OR (${t.cadence} IS NOT NULL AND ${t.per} IS NOT NULL AND ${t.per} >= 1 AND ${t.since} IS NOT NULL AND ${t.startValue} IS NULL AND ${t.targetValue} IS NULL AND ${t.unit} IS NULL)`,
    ),
    check(
      'results_performance_check',
      sql`${t.metricType} <> 'performance' OR (${t.targetValue} IS NOT NULL AND ${t.since} IS NOT NULL AND ${t.cadence} IS NULL AND ${t.per} IS NULL)`,
    ),
    check(
      'results_distinct_target_check',
      sql`${t.metricType} NOT IN ('number', 'percentage') OR ${t.targetValue} <> ${t.startValue}`,
    ),
    check(
      'results_finite_values_check',
      sql`(${t.startValue} IS NULL OR ${t.startValue} NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric)) AND (${t.targetValue} IS NULL OR ${t.targetValue} NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric))`,
    ),
    tenantPolicy('results'),
  ],
).enableRLS()
export const actions = pgTable(
  'actions',
  {
    id: productId(),
    userId: tenantId(),
    cycleId: uuid('cycle_id').notNull(),
    objectiveId: uuid('objective_id'),
    title: text('title').notNull(),
    description: text('description'),
    dueAt: date('due_at'),
    status: actionStatus('status').notNull().default('open'),
    sortOrder: numeric('sort_order', { mode: 'number' }),
    sessionId: uuid('session_id'),
    createdAt: created(),
    updatedAt: updated(),
  },
  (t) => [
    uniqueIndex('actions_id_user_uidx').on(t.id, t.userId),
    index('actions_user_id_idx').on(t.userId),
    index('actions_cycle_user_idx').on(t.cycleId, t.userId),
    index('actions_objective_cycle_user_idx').on(t.objectiveId, t.cycleId, t.userId),
    index('actions_session_user_idx').on(t.sessionId, t.userId),
    index('actions_outstanding_order_idx').on(
      t.cycleId,
      t.objectiveId,
      t.sortOrder.asc().nullsLast(),
      t.dueAt,
    ),
    foreignKey({
      name: 'actions_cycle_tenant_fk',
      columns: [t.cycleId, t.userId],
      foreignColumns: [cycles.id, cycles.userId],
    }).onDelete('cascade'),
    foreignKey({
      name: 'actions_objective_cycle_tenant_fk',
      columns: [t.objectiveId, t.cycleId, t.userId],
      foreignColumns: [objectives.id, objectives.cycleId, objectives.userId],
    }).onDelete('cascade'),
    foreignKey({
      name: 'actions_session_tenant_fk',
      columns: [t.sessionId, t.userId],
      foreignColumns: [sessions.id, sessions.userId],
    }).onDelete('set null'),
    tenantPolicy('actions'),
  ],
).enableRLS()
export const result_logs = pgTable(
  'result_logs',
  {
    id: productId(),
    userId: tenantId(),
    resultId: uuid('result_id').notNull(),
    value: numeric('value', { mode: 'number' }),
    comment: text('comment'),
    sessionId: uuid('session_id'),
    recordedAt: timestamp('recorded_at', { withTimezone: true }).notNull().defaultNow(),
    createdAt: created(),
  },
  (t) => [
    index('result_logs_user_id_idx').on(t.userId),
    index('result_logs_result_user_idx').on(t.resultId, t.userId),
    index('result_logs_session_user_idx').on(t.sessionId, t.userId),
    index('result_logs_history_idx').on(t.resultId, t.recordedAt.desc(), t.id.desc()),
    foreignKey({
      name: 'result_logs_result_tenant_fk',
      columns: [t.resultId, t.userId],
      foreignColumns: [results.id, results.userId],
    }).onDelete('cascade'),
    foreignKey({
      name: 'result_logs_session_tenant_fk',
      columns: [t.sessionId, t.userId],
      foreignColumns: [sessions.id, sessions.userId],
    }),
    check(
      'result_logs_finite_value_check',
      sql`${t.value} IS NULL OR ${t.value} NOT IN ('NaN'::numeric, 'Infinity'::numeric, '-Infinity'::numeric)`,
    ),
    tenantPolicy('result_logs'),
  ],
).enableRLS()
export const action_logs = pgTable(
  'action_logs',
  {
    id: productId(),
    userId: tenantId(),
    actionId: uuid('action_id').notNull(),
    status: actionStatus('status').notNull(),
    comment: text('comment'),
    sessionId: uuid('session_id'),
    recordedAt: timestamp('recorded_at', { withTimezone: true }).notNull().defaultNow(),
    createdAt: created(),
  },
  (t) => [
    index('action_logs_user_id_idx').on(t.userId),
    index('action_logs_action_user_idx').on(t.actionId, t.userId),
    index('action_logs_session_user_idx').on(t.sessionId, t.userId),
    index('action_logs_history_idx').on(t.actionId, t.recordedAt.desc(), t.id.desc()),
    foreignKey({
      name: 'action_logs_action_tenant_fk',
      columns: [t.actionId, t.userId],
      foreignColumns: [actions.id, actions.userId],
    }).onDelete('cascade'),
    foreignKey({
      name: 'action_logs_session_tenant_fk',
      columns: [t.sessionId, t.userId],
      foreignColumns: [sessions.id, sessions.userId],
    }),
    tenantPolicy('action_logs'),
  ],
).enableRLS()
export const notes = pgTable(
  'notes',
  {
    id: productId(),
    userId: tenantId(),
    text: text('text').notNull(),
    recordedAt: timestamp('recorded_at', { withTimezone: true }).notNull().defaultNow(),
    createdAt: created(),
    updatedAt: updated(),
  },
  (t) => [
    index('notes_history_idx').on(t.userId, t.recordedAt.desc(), t.id.desc()),
    check('notes_nonblank_text_check', sql`${t.text} ~ '[^[:space:]]'`),
    tenantPolicy('notes'),
  ],
).enableRLS()
export const habit_logs = pgTable(
  'habit_logs',
  {
    id: productId(),
    userId: tenantId(),
    resultId: uuid('result_id').notNull(),
    day: date('day').notNull(),
    sessionId: uuid('session_id'),
    createdAt: created(),
  },
  (t) => [
    index('habit_logs_user_id_idx').on(t.userId),
    index('habit_logs_result_user_idx').on(t.resultId, t.userId),
    index('habit_logs_session_user_idx').on(t.sessionId, t.userId),
    uniqueIndex('habit_logs_result_day_uidx').on(t.resultId, t.day),
    foreignKey({
      name: 'habit_logs_result_tenant_fk',
      columns: [t.resultId, t.userId],
      foreignColumns: [results.id, results.userId],
    }).onDelete('cascade'),
    foreignKey({
      name: 'habit_logs_session_tenant_fk',
      columns: [t.sessionId, t.userId],
      foreignColumns: [sessions.id, sessions.userId],
    }),
    tenantPolicy('habit_logs'),
  ],
).enableRLS()
export const example = pgTable(
  'example',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('example_user_id_idx').on(table.userId),
    pgPolicy('example_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: TENANT_USING,
      withCheck: TENANT_USING,
    }),
  ],
).enableRLS()
export const oauthResource = pgTable(
  'oauth_resource',
  {
    id: uuid('id')
      .default(sql`pg_catalog.gen_random_uuid()`)
      .primaryKey(),
    identifier: text('identifier').notNull().unique(),
    name: text('name').notNull(),
    accessTokenTtl: integer('access_token_ttl'),
    refreshTokenTtl: integer('refresh_token_ttl'),
    signingAlgorithm: text('signing_algorithm'),
    signingKeyId: text('signing_key_id'),
    allowedScopes: text('allowed_scopes').array(),
    customClaims: jsonb('custom_claims'),
    dpopBoundAccessTokensRequired: boolean('dpop_bound_access_tokens_required').default(false),
    disabled: boolean('disabled').default(false),
    createdAt: timestamp('created_at', { withTimezone: true }),
    updatedAt: timestamp('updated_at', { withTimezone: true }),
    policyVersion: integer('policy_version').default(1),
    metadata: jsonb('metadata'),
  },
  (table) => [],
)
export const oauthClientResource = pgTable(
  'oauth_client_resource',
  {
    id: uuid('id')
      .default(sql`pg_catalog.gen_random_uuid()`)
      .primaryKey(),
    clientId: text('client_id')
      .notNull()
      .references(() => oauthClient.clientId, { onDelete: 'cascade' }),
    resourceId: text('resource_id')
      .notNull()
      .references(() => oauthResource.identifier, { onDelete: 'cascade' }),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }),
  },
  (table) => [
    index('oauth_client_resource_resource_id_idx').on(table.resourceId),
    uniqueIndex('oauth_client_resource_client_id_resource_id_uidx').on(
      table.clientId,
      table.resourceId,
    ),
  ],
)
export const oauthClientAssertion = pgTable(
  'oauth_client_assertion',
  {
    id: text('id').primaryKey(),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  },
  (table) => [],
)
export const subscription = pgTable(
  'subscription',
  {
    id: uuid('id')
      .default(sql`pg_catalog.gen_random_uuid()`)
      .primaryKey(),
    plan: text('plan').notNull(),
    referenceId: uuid('reference_id')
      .notNull()
      .references(() => user.id, { onDelete: 'cascade' }),
    stripeCustomerId: text('stripe_customer_id'),
    stripeSubscriptionId: text('stripe_subscription_id'),
    status: text('status').default('incomplete').notNull(),
    periodStart: timestamp('period_start', { withTimezone: true }),
    periodEnd: timestamp('period_end', { withTimezone: true }),
    trialStart: timestamp('trial_start', { withTimezone: true }),
    trialEnd: timestamp('trial_end', { withTimezone: true }),
    cancelAtPeriodEnd: boolean('cancel_at_period_end').default(false),
    cancelAt: timestamp('cancel_at', { withTimezone: true }),
    canceledAt: timestamp('canceled_at', { withTimezone: true }),
    endedAt: timestamp('ended_at', { withTimezone: true }),
    seats: integer('seats'),
    billingInterval: text('billing_interval'),
    stripeScheduleId: text('stripe_schedule_id'),
  },
  (table) => [
    index('subscription_reference_id_idx').on(table.referenceId),
    uniqueIndex('subscription_stripe_subscription_id_uidx').on(table.stripeSubscriptionId),
    index('subscription_stripe_customer_id_idx').on(table.stripeCustomerId),
    pgPolicy('subscription_tenant_isolation', {
      for: 'all',
      to: 'authenticated',
      using: sql`${table.referenceId} = nullif(current_setting('app.user_id', true), '')::uuid`,
      withCheck: sql`${table.referenceId} = nullif(current_setting('app.user_id', true), '')::uuid`,
    }),
  ],
).enableRLS()