/**
 * _helpers.ts — DB helpers for translateDealFields orchestrator.
 *
 * All functions accept a `DrizzleClient` as first argument for testability.
 * These are internal to the `fields/` module; do not import from outside.
 */

import { eq, and } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { deals, dealTranslations } from '@/server/db/schema.js';
import type { DealTranslation } from '@/server/db/schema.js';
import { getSystemConfig } from '@/server/db/queries/system-config.js';
import {
  upsertDealTranslationField as writeField,
  pinDealTranslationSlug,
  fillSourceTranslation,
} from '@/server/db/queries/translation/deal-translations.js';

// ─── Load helpers ─────────────────────────────────────────────────────────────

export type DealSource = {
  id: string;
  title: string;
  description: string;
  specialInstructions: string | null;
  pickupAddress: string;
  sourceLanguage: string;
};

/** Load the deal row, selecting only fields relevant to the translation engine. */
export async function loadDealWithSource(db: DrizzleClient, dealId: string): Promise<DealSource> {
  const rows = await db
    .select({
      id: deals.id,
      title: deals.title,
      description: deals.description,
      specialInstructions: deals.specialInstructions,
      pickupAddress: deals.pickupAddress,
      sourceLanguage: deals.sourceLanguage,
    })
    .from(deals)
    .where(eq(deals.id, dealId))
    .limit(1);

  if (!rows[0]) throw new Error(`Deal not found: ${dealId}`);
  return rows[0];
}

/** Load an existing deal_translations row, or null if none yet. */
export async function loadDealTranslation(
  db: DrizzleClient,
  dealId: string,
  locale: string,
): Promise<DealTranslation | null> {
  const rows = await db
    .select()
    .from(dealTranslations)
    .where(and(eq(dealTranslations.dealId, dealId), eq(dealTranslations.locale, locale)))
    .limit(1);
  return rows[0] ?? null;
}

// ─── Upsert helpers ───────────────────────────────────────────────────────────

export type FieldName = 'title' | 'description' | 'specialInstructions';

/**
 * Upsert a single translated field + its source hash into deal_translations.
 *
 * Uses INSERT … ON CONFLICT (deal_id, locale) DO UPDATE so it works whether
 * the row already exists or not. The slug is required for the INSERT path
 * (NOT NULL in schema); pass the already-pinned slug from the orchestrator.
 * Only the specified field columns are updated on conflict — other fields
 * are not clobbered.
 */
export async function upsertDealTranslationField(
  db: DrizzleClient,
  dealId: string,
  locale: string,
  slug: string,
  field: FieldName,
  translatedText: string,
  sourceHash: string,
  modelId: string,
): Promise<void> {
  await writeField(db, { dealId, locale, slug, field, translatedText, sourceHash, modelId });
}

// ─── Slug helpers ─────────────────────────────────────────────────────────────

/**
 * Generate a URL-safe slug from arbitrary text.
 *
 * Steps:
 *   1. Strip Unicode bidi marks.
 *   2. Lowercase.
 *   3. Collapse whitespace → hyphens.
 *   4. Strip anything not [a-z0-9-].
 *   5. Collapse multiple hyphens.
 *   6. Trim leading/trailing hyphens.
 *   7. If result is < 2 chars (e.g. pure Hebrew input), fall back to
 *      `deal-<dealId[:8]>` which is stable and guaranteed unique per deal.
 */
export function slugify(text: string, fallbackSeed: string): string {
  const BIDI = /[\u{FEFF}\u{200E}\u{200F}\u{202A}-\u{202E}\u{2066}-\u{2069}]/gu;
  const candidate = text
    .replace(BIDI, '')
    .toLowerCase()
    .replace(/\s+/g, '-')
    .replace(/[^\p{L}\p{N}-]/gu, '')
    .replace(/-{2,}/g, '-')
    .replace(/^-+|-+$/g, '');

  return candidate.length >= 2 ? candidate : `deal-${fallbackSeed.slice(0, 8)}`;
}

/**
 * Slug pinning invariant — see spec §7.3.
 *
 * A slug, once written for `(dealId, locale)`, is IMMUTABLE. Changing a
 * published slug breaks SEO + redirects (Plan 4 Task 3 handles redirect rows).
 *
 * Algorithm:
 *   1. If the row has a non-empty slug already → no-op, return existing slug.
 *   2. Find a collision-free slug (candidateSlug, then candidateSlug-2, …-99).
 *   3. Write the slug via UPDATE (row always exists at this point, created by
 *      the first upsertDealTranslationField call in the field loop).
 */
export async function ensureSlugPinned(
  db: DrizzleClient,
  dealId: string,
  locale: string,
  candidateSlug: string,
): Promise<string> {
  const existing = await loadDealTranslation(db, dealId, locale);

  // Already pinned — invariant: never overwrite.
  if (existing && existing.slug && existing.slug.length > 0) {
    return existing.slug;
  }

  // Find a non-colliding slug (append -2, -3, … on collision).
  let slug = candidateSlug;
  let suffix = 1;
  while (suffix <= 99) {
    const collision = await db
      .select({ id: dealTranslations.id })
      .from(dealTranslations)
      .where(and(eq(dealTranslations.locale, locale), eq(dealTranslations.slug, slug)))
      .limit(1);

    if (collision.length === 0) break;
    suffix += 1;
    slug = `${candidateSlug}-${suffix}`;
  }

  await pinDealTranslationSlug(db, { dealId, locale, slug });

  return slug;
}

// ─── Source-locale slug provisioning ─────────────────────────────────────────

/**
 * Ensure a deal_translations row exists for the deal's own source language.
 * The source locale (e.g. 'he') is never processed by translateDealFields,
 * so it needs its own row created at approval time using the original title.
 *
 * Idempotent — no-op if the row already exists.
 */
export async function pinSourceSlug(
  db: DrizzleClient,
  dealId: string,
  title: string,
  description: string,
  locale: string,
): Promise<void> {
  const existing = await loadDealTranslation(db, dealId, locale);
  if (existing) return; // already exists

  const candidateSlug = slugify(title, dealId);
  await ensureSlugPinned(db, dealId, locale, candidateSlug);

  // Fill in the title + description on the skeleton row ensureSlugPinned created.
  await fillSourceTranslation(db, { dealId, locale, title, description });
}

// ─── Cost estimation ──────────────────────────────────────────────────────────

/**
 * Estimate translation cost for a batch of miss texts.
 *
 * Formula: totalChars / 1000 × priceUsdPerKchar
 *
 * `translation_price_usd_per_kchar` in system_config is a single number (e.g.
 * "0.01") representing USD per thousand characters. This is a conservative
 * over-estimate used only for the pre-call budget gate; `reconcileBudget`
 * corrects it afterwards using actual token spend.
 */
export async function estimateCost(db: DrizzleClient, texts: string[]): Promise<number> {
  const priceRaw = await getSystemConfig(db, 'translation_price_usd_per_kchar');
  const pricePerKchar = priceRaw ? parseFloat(priceRaw) : 0.01; // $0.01/kchar safe default
  const totalChars = texts.reduce((acc, t) => acc + t.length, 0);
  return (totalChars / 1000) * pricePerKchar;
}