/**
 * AI chat schema — ai-assistant.
 *
 * - ai_chat_sessions:  per-user conversation sessions (in_app, telegram, whatsapp)
 * - ai_chat_messages:  ordered messages within a session
 *
 * Postgres / Neon via Hyperdrive.
 */
import {
  pgTable,
  uuid,
  text,
  integer,
  jsonb,
  timestamp,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── ai_chat_sessions ──────────────────────────────────────────────────────────

export const aiChatSessions = pgTable(
  'ai_chat_sessions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    /**
     * NULL for bot-channel sessions (telegram/whatsapp) where no in-app user exists.
     * NOT NULL for in_app sessions — enforced by CHECK constraint below.
     */
    userId: uuid('user_id')
      .references(() => users.id, { onDelete: 'cascade' }),
    /** Derived from first user message; nullable until first message saved. */
    title: text('title'),
    channel: text('channel').notNull().default('in_app'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    sessionUserIdx: index('ai_chat_sessions_user').on(t.tenantId, t.userId, t.createdAt.desc()),
    channelCheck: check(
      'ai_chat_sessions_channel_check',
      sql`${t.channel} IN ('in_app', 'telegram', 'whatsapp')`,
    ),
    /**
     * In-app sessions must have a user_id; bot sessions (telegram/whatsapp) may be NULL.
     * This enforces the invariant without making the column globally NOT NULL.
     */
    userIdInAppCheck: check(
      'ai_chat_sessions_user_id_in_app_check',
      sql`${t.channel} <> 'in_app' OR ${t.userId} IS NOT NULL`,
    ),
  }),
)

export type AiChatSessionRow = typeof aiChatSessions.$inferSelect
export type NewAiChatSession = typeof aiChatSessions.$inferInsert

// ── ai_chat_messages ──────────────────────────────────────────────────────────

export const aiChatMessages = pgTable(
  'ai_chat_messages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sessionId: uuid('session_id')
      .notNull()
      .references(() => aiChatSessions.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    role: text('role').notNull(),
    content: text('content').notNull(),
    tokensUsed: integer('tokens_used'),
    /** JSONB for extra metadata: telegram_chat_id, whatsapp_chat_id, model, etc. */
    metadata: jsonb('metadata')
      .notNull()
      .default(sql`'{}'::jsonb`),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    messageSessionIdx: index('ai_chat_messages_session').on(t.sessionId, t.createdAt),
    /**
     * Full partial expression index on (metadata->>'telegram_chat_id') WHERE metadata ? 'telegram_chat_id'
     * cannot be expressed in Drizzle pg-core (no WHERE predicate on non-unique indexes).
     * Defined in raw_ddl for the controller to append to the wave migration.
     */
    rolCheck: check(
      'ai_chat_messages_role_check',
      sql`${t.role} IN ('user', 'assistant', 'system')`,
    ),
  }),
)

export type AiChatMessageRow = typeof aiChatMessages.$inferSelect
export type NewAiChatMessage = typeof aiChatMessages.$inferInsert