/**
 * session-security — wave-13.
 *
 * user_sessions: per-request session rows tracking active tokens.
 * tenant_security_settings: per-tenant idle-timeout, session cap, 2FA policy.
 */
import { pgTable, uuid, text, boolean, integer, timestamp, index, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import type { AnyPgColumn } from 'drizzle-orm/pg-core'
import { tenants } from './tenants'
import { users } from './users'

export const userSessions = pgTable(
  'user_sessions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references((): AnyPgColumn => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references((): AnyPgColumn => users.id, { onDelete: 'cascade' }),
    tokenHash: text('token_hash').notNull().unique(),
    deviceName: text('device_name'),
    ipAddress: text('ip_address'), // stored as text; INET in DB via migration
    countryCode: text('country_code'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    lastActiveAt: timestamp('last_active_at', { withTimezone: true }).notNull().defaultNow(),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
    revokedReason: text('revoked_reason'),
  },
  (t) => ({
    idxSessionsUser: index('idx_sessions_user').on(t.userId, t.revokedAt),
    idxSessionsTenant: index('idx_sessions_tenant').on(t.tenantId, t.lastActiveAt),
    idxSessionsExpiresAt: index('idx_sessions_expires_at').on(t.expiresAt),
    idxSessionsUserId: index('idx_sessions_user_id').on(t.userId, t.createdAt),
    idxSessionsTenantId: index('idx_sessions_tenant_id').on(t.tenantId),
    revokedReasonCheck: check(
      'user_sessions_revoked_reason_check',
      sql`revoked_reason IN ('user', 'admin', 'idle_timeout', 'suspicious')`,
    ),
    validExpiryCheck: check(
      'user_sessions_valid_expiry_check',
      sql`expires_at > created_at`,
    ),
  }),
)

export type UserSessionRow = typeof userSessions.$inferSelect
export type NewUserSession = typeof userSessions.$inferInsert

export const tenantSecuritySettings = pgTable(
  'tenant_security_settings',
  {
    tenantId: uuid('tenant_id')
      .primaryKey()
      .references((): AnyPgColumn => tenants.id, { onDelete: 'cascade' }),
    idleTimeoutMinutes: integer('idle_timeout_minutes'), // NULL = disabled
    maxSessionsPerUser: integer('max_sessions_per_user').notNull().default(10),
    require2faForRoles: text('require_2fa_for_roles')
      .array()
      .notNull()
      .default(sql`'{}'`),
    blockSuspiciousLogins: boolean('block_suspicious_logins').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
)

export type TenantSecuritySettingsRow = typeof tenantSecuritySettings.$inferSelect
export type NewTenantSecuritySettings = typeof tenantSecuritySettings.$inferInsert