/**
 * Drizzle ORM schema - Multideal (מולטידיל) database
 *
 * All 13 FDS data models + supporting tables.
 * snake_case column names (enforced by drizzle.config.ts casing: 'snake_case').
 * Every timestamp uses { withTimezone: true }.
 * Every enum uses pgEnum.
 *
 * PII columns (phone, email, address, token) are stored as encrypted text via pgcrypto.
 * Blind-index columns provide deterministic lookup without decryption.
 */

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

import { vendors, dealCategories, dealTags } from './core.js';
import { languages } from './languages.js';
import { deals } from './deal-variants.js';

// ─── i18n: Deal Translations ──────────────────────────────────────────────────

export const dealTranslations = pgTable(
  'deal_translations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    locale: text('locale')
      .notNull()
      .references(() => languages.code),
    slug: text('slug').notNull(),
    title: text('title').notNull(),
    description: text('description').notNull().default(''),
    specialInstructions: text('special_instructions'),
    pickupAddress: text('pickup_address').notNull().default(''),
    // Per-field source hashes — populated by the translation orchestrator on each
    // translateDealFields run. Empty string means "never translated yet" (safe default
    // for the backfill rows that predate Plan 2). Used to skip re-translation when the
    // source field text has not changed since the last run.
    titleSourceHash: text('title_source_hash').notNull().default(''),
    descriptionSourceHash: text('description_source_hash').notNull().default(''),
    specialInstructionsSourceHash: text('special_instructions_source_hash'),
    titleManualOverride: boolean('title_manual_override').notNull().default(false),
    descriptionManualOverride: boolean('description_manual_override').notNull().default(false),
    specialInstructionsManualOverride: boolean('special_instructions_manual_override')
      .notNull()
      .default(false),
    // search column populated and GIN-indexed in Plan 2 — Translation Engine.
    search: text('search'),
    modelId: text('model_id'),
    status: text('status').notNull().default('OK'), // 'OK' | 'STALE' | 'FAILED'
    translatedAt: timestamp('translated_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('deal_translations_deal_locale_idx').on(t.dealId, t.locale),
    uniqueIndex('deal_translations_locale_slug_idx').on(t.locale, t.slug),
    index('deal_translations_locale_status_idx').on(t.locale, t.status),
  ],
);
export type DealTranslation = typeof dealTranslations.$inferSelect;
export type NewDealTranslation = typeof dealTranslations.$inferInsert;

// ─── i18n: Deal Slug Redirects ────────────────────────────────────────────────

export const dealSlugRedirects = pgTable(
  'deal_slug_redirects',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    locale: text('locale')
      .notNull()
      .references(() => languages.code),
    oldSlug: text('old_slug').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('deal_slug_redirects_locale_old_idx').on(t.locale, t.oldSlug),
    index('deal_slug_redirects_deal_idx').on(t.dealId),
  ],
);
export type DealSlugRedirect = typeof dealSlugRedirects.$inferSelect;
export type NewDealSlugRedirect = typeof dealSlugRedirects.$inferInsert;

// ─── i18n: Category Translations ─────────────────────────────────────────────

export const categoryTranslations = pgTable(
  'category_translations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    categoryId: uuid('category_id')
      .notNull()
      .references(() => dealCategories.id, { onDelete: 'cascade' }),
    locale: text('locale')
      .notNull()
      .references(() => languages.code),
    name: text('name').notNull(),
    nameSourceHash: text('name_source_hash').notNull(),
    nameManualOverride: boolean('name_manual_override').notNull().default(false),
    modelId: text('model_id'),
    status: text('status').notNull().default('OK'), // 'OK' | 'STALE' | 'FAILED'
    translatedAt: timestamp('translated_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('category_translations_cat_locale_idx').on(t.categoryId, t.locale),
    index('category_translations_locale_status_idx').on(t.locale, t.status),
  ],
);
export type CategoryTranslation = typeof categoryTranslations.$inferSelect;
export type NewCategoryTranslation = typeof categoryTranslations.$inferInsert;

// ─── i18n: Tag Translations ───────────────────────────────────────────────────

export const tagTranslations = pgTable(
  'tag_translations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tagId: uuid('tag_id')
      .notNull()
      .references(() => dealTags.id, { onDelete: 'cascade' }),
    locale: text('locale')
      .notNull()
      .references(() => languages.code),
    name: text('name').notNull(),
    nameSourceHash: text('name_source_hash').notNull(),
    nameManualOverride: boolean('name_manual_override').notNull().default(false),
    modelId: text('model_id'),
    status: text('status').notNull().default('OK'), // 'OK' | 'STALE' | 'FAILED'
    translatedAt: timestamp('translated_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('tag_translations_tag_locale_idx').on(t.tagId, t.locale),
    index('tag_translations_locale_status_idx').on(t.locale, t.status),
  ],
);
export type TagTranslation = typeof tagTranslations.$inferSelect;
export type NewTagTranslation = typeof tagTranslations.$inferInsert;

// ─── Slug History ─────────────────────────────────────────────────────────────

/** Polymorphic slug redirect table — shared by deal_tags and deal_categories.
 *  When a slug is renamed, old slug is archived here so old URLs 301-redirect. */
export const slugHistory = pgTable(
  'slug_history',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    entityType: text('entity_type').notNull(), // 'tag' | 'category'
    entityId: uuid('entity_id').notNull(),
    oldSlug: text('old_slug').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('slug_history_lookup_uidx').on(t.entityType, t.oldSlug),
    index('slug_history_entity_idx').on(t.entityType, t.entityId),
  ],
);
export type SlugHistory = typeof slugHistory.$inferSelect;
export type NewSlugHistory = typeof slugHistory.$inferInsert;

// ─── i18n: Vendor Translations ────────────────────────────────────────────────

export const vendorTranslations = pgTable(
  'vendor_translations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id, { onDelete: 'cascade' }),
    locale: text('locale')
      .notNull()
      .references(() => languages.code),
    displayName: text('display_name'),
    displayNameSourceHash: text('display_name_source_hash'),
    displayNameManualOverride: boolean('display_name_manual_override').notNull().default(false),
    description: text('description'),
    descriptionSourceHash: text('description_source_hash'),
    descriptionManualOverride: boolean('description_manual_override').notNull().default(false),
    modelId: text('model_id'),
    status: text('status').notNull().default('OK'), // 'OK' | 'STALE' | 'FAILED'
    translatedAt: timestamp('translated_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('vendor_translations_vendor_locale_idx').on(t.vendorId, t.locale),
    index('vendor_translations_locale_status_idx').on(t.locale, t.status),
  ],
);
export type VendorTranslation = typeof vendorTranslations.$inferSelect;
export type NewVendorTranslation = typeof vendorTranslations.$inferInsert;

// ─── Translation Memory ────────────────────────────────────────────────────────

export const translationMemory = pgTable(
  'translation_memory',
  {
    sourceHash: text('source_hash').notNull(),
    sourceLocale: text('source_locale')
      .notNull()
      .references(() => languages.code),
    targetLocale: text('target_locale')
      .notNull()
      .references(() => languages.code),
    sourceText: text('source_text').notNull(),
    translatedText: text('translated_text').notNull(),
    modelId: text('model_id').notNull(),
    qualityScore: integer('quality_score'),
    usageCount: integer('usage_count').notNull().default(1),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }).notNull().defaultNow(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.sourceHash, t.sourceLocale, t.targetLocale] }),
    index('tm_last_used_idx').on(t.lastUsedAt),
    index('tm_locale_pair_idx').on(t.sourceLocale, t.targetLocale),
  ],
);
export type TranslationMemory = typeof translationMemory.$inferSelect;
export type NewTranslationMemory = typeof translationMemory.$inferInsert;

// ─── Translation Jobs ──────────────────────────────────────────────────────────

export const translationJobs = pgTable(
  'translation_jobs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    targetLocale: text('target_locale')
      .notNull()
      .references(() => languages.code),
    status: text('status').notNull().default('PENDING'), // PENDING | RUNNING | DONE | FAILED | PENDING_BUDGET
    attempt: integer('attempt').notNull().default(0),
    lastError: text('last_error'),
    costUsd: numeric('cost_usd', { precision: 10, scale: 6 }),
    inputTokens: integer('input_tokens'),
    outputTokens: integer('output_tokens'),
    forceFresh: boolean('force_fresh').notNull().default(false),
    scheduledAt: timestamp('scheduled_at', { withTimezone: true }),
    startedAt: timestamp('started_at', { withTimezone: true }),
    finishedAt: timestamp('finished_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('translation_jobs_deal_idx').on(t.dealId),
    index('translation_jobs_status_idx').on(t.status),
    index('translation_jobs_scheduled_at_idx').on(t.scheduledAt),
    uniqueIndex('translation_jobs_deal_locale_active_idx')
      .on(t.dealId, t.targetLocale)
      .where(sql`${t.status} IN ('PENDING','RUNNING','PENDING_BUDGET')`),
  ],
);
export type TranslationJob = typeof translationJobs.$inferSelect;
export type NewTranslationJob = typeof translationJobs.$inferInsert;