import { sql } from 'drizzle-orm'
import { boolean, index, integer, pgTable, text, timestamp, uniqueIndex, uuid } from 'drizzle-orm/pg-core'

export const languages = pgTable(
  'languages',
  {
    code: text('code').primaryKey(),
    nameNative: text('name_native'),
    nameEnglish: text('name_english'),
    direction: text('direction'),
    searchConfig: text('search_config'),
    isActive: boolean('is_active').notNull(),
    isDefault: boolean('is_default').notNull().default(false),
    sortOrder: integer('sort_order').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
  },
  (t) => [
    uniqueIndex('languages_one_default_uq')
      .on(t.isDefault)
      .where(sql`${t.isDefault} = true`),
    index('languages_active_sort_idx').on(t.isActive, t.sortOrder),
  ],
)

export const translationValue = pgTable(
  'translation_value',
  {
    id: uuid('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
    entityType: text('entity_type').notNull(),
    entityId: text('entity_id').notNull(),
    fieldKey: text('field_key').notNull(),
    locale: text('locale')
      .notNull()
      .references(() => languages.code),
    value: text('value').notNull(),
    sourceHash: text('source_hash').notNull().default(''),
    manualOverride: boolean('manual_override').notNull().default(false),
    modelId: text('model_id'),
    status: text('status').notNull().default('OK'),
    translatedAt: timestamp('translated_at', { withTimezone: true, precision: 3 }),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().$defaultFn(() => new Date()),
  },
  (t) => [
    uniqueIndex('tv_entity_field_locale_uq').on(t.entityType, t.entityId, t.fieldKey, t.locale),
    index('tv_entity_locale_idx').on(t.entityType, t.entityId, t.locale),
    index('tv_locale_status_idx').on(t.locale, t.status),
    index('tv_entity_field_locale_val_idx')
      .on(t.entityType, t.fieldKey, t.locale, t.value)
      .where(sql`length(value) <= 512`),
  ],
)

export const i18nContentSchema = { translationValue, languages }
export type I18nContentSchema = typeof i18nContentSchema