import { index, integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'

import { createUpdateTimestamps, uuidPrimaryKeyOrdered } from './_columnHelpers'
import { translateLanguageTable } from './translateLanguage'

/**
 * Translate history table - stores translation records
 *
 * Design notes:
 * - Data grows unbounded, renderer should use cursor paginated queries
 *   with infinite scroll instead of loading all records at once.
 * - Text search (sourceText/targetText) uses SQL LIKE at DB layer,
 *   not client-side filtering.
 * - Star + createdAt compound index supports "starred only, sorted by time" queries.
 * - sourceLanguage/targetLanguage are FK to translateLanguage.langCode (SET NULL on delete).
 */
export const translateHistoryTable = sqliteTable(
  'translate_history',
  {
    id: uuidPrimaryKeyOrdered(),
    sourceText: text().notNull(),
    targetText: text().notNull(),
    sourceLanguage: text().references(() => translateLanguageTable.langCode, { onDelete: 'set null' }),
    targetLanguage: text().references(() => translateLanguageTable.langCode, { onDelete: 'set null' }),
    star: integer({ mode: 'boolean' }).notNull().default(false),
    ...createUpdateTimestamps
  },
  (t) => [
    index('translate_history_created_at_idx').on(t.createdAt),
    index('translate_history_star_created_at_idx').on(t.star, t.createdAt)
  ]
)
