/**
 * Translation Service
 *
 * Core translation logic for synchronous and asynchronous translation jobs
 */

import { PrismaClient, TranslationJobStatus } from '@prisma/client';
import crypto from 'crypto';
import { translationQueue } from '../queue';
import { geminiClient } from './geminiClient';
import { exceptionService } from './exceptionService';
import { verifyExceptions } from './exceptionVerifier';
import { deductCredits, getCurrentBalance } from './creditService';
import { logger } from '../utils/logger';
import { countSourceCharacters, calculateCost, calculateCustomerCostByChars } from '../utils/tokenCalculation';
import {
  countStructuredCharacters,
  mergeStructuredFields,
  parseSerializedStructuredFields,
  serializeStructuredFields,
  splitStructuredTranslation,
  StructuredFields,
} from '../utils/structuredFields';
import { generateContentHash } from '../utils/encryption';
import {
  GEMINI_CALL_CHAR_BUDGET,
  GEMINI_CALL_MAX_PAYLOAD_BYTES,
  GEMINI_CALL_MAX_STRINGS,
} from '../config/batchLimits';
import { planBatches } from '../utils/batchPlanner';
import {
  countSiteContentCharacters,
  isSiteContentRequest,
  parseSerializedSiteContentRequest,
  serializeSiteContentRequest,
  siteContentPollMetadata,
  SiteContentJobSubmitRequest,
  validateSiteContentRequest,
} from './siteContentTranslation';
import { trackTranslationJob, trackTokensProcessed } from '../utils/metrics';
import { config } from '../config';
import {
  TranslationRequest,
  TranslationResponse,
  JobSubmitRequest,
  JobSubmitResponse,
  JobStatusResponse,
  BulkTranslationRequest,
  BulkTranslationResponse,
  Tone,
  TranslationStatus,
} from '../types';

const prisma = new PrismaClient();

function asyncJobIdentity(
  userId: string,
  plugin: string,
  sourceContent: string,
  sourceLang: string,
  targetLang: string,
  tone: Tone,
  clientJobId: string | undefined,
  callbackUrl: string | undefined,
  callbackSecret: string | undefined,
  siteUrl: string | undefined
): string {
  const callbackSecretFingerprint = crypto
    .createHash('sha256')
    .update(callbackSecret ?? '')
    .digest('hex');
  const canonicalIdentity = JSON.stringify({
    callback_secret_fingerprint: callbackSecretFingerprint,
    callback_url: callbackUrl ?? null,
    client_job_id: clientJobId ?? null,
    site_url: siteUrl ?? null,
    content: sourceContent,
    plugin,
    source_lang: sourceLang,
    target_lang: targetLang,
    tone,
    user_id: userId,
  });
  return crypto.createHash('sha256').update(canonicalIdentity).digest('hex');
}

export function mapStoredTranslation(rawTranslation: string | null): {
  translation?: string;
  translatedTitle?: string;
  translatedExcerpt?: string;
  translatedContent?: string;
  translatedFields?: StructuredFields;
} {
  if (!rawTranslation) {
    return {};
  }

  try {
    const parsed = parseSerializedStructuredFields(rawTranslation);
    if (!parsed) {
      return { translation: rawTranslation };
    }

    const split = splitStructuredTranslation(parsed);
    const legacyTranslation = split.translatedContent ??
      [split.translatedTitle, split.translatedExcerpt, ...Object.values(split.translatedFields)]
        .filter((value): value is string => value !== undefined)
        .join('\\n\\n');

    return {
      translation: legacyTranslation || rawTranslation,
      translatedTitle: split.translatedTitle,
      translatedExcerpt: split.translatedExcerpt,
      translatedContent: split.translatedContent,
      translatedFields: Object.keys(split.translatedFields).length > 0 ? split.translatedFields : undefined,
    };
  } catch {
    return { translation: rawTranslation };
  }
}


export class TranslationService {
  /**
   * Synchronous translation (for content up to 5000 characters)
   */
  async translateSync(userId: string, request: TranslationRequest, plugin: string = 'translate'): Promise<TranslationResponse> {
    const startTime = Date.now();
    const exceptionRules = exceptionService.normalizeRules(request.exceptions);

    // Detect structured (per-field) vs legacy (single-blob) mode
    const isStructured = request.title !== undefined || request.excerpt !== undefined;
    const structuredInputFields = isStructured
      ? Object.fromEntries(
        Object.entries({ title: request.title, excerpt: request.excerpt, content: request.content })
          .filter(([, value]) => value !== undefined)
      ) as StructuredFields
      : undefined;

    // Validate combined content length
    const combinedLength = structuredInputFields
      ? countStructuredCharacters(structuredInputFields)
      : request.content?.length || 0;

    if (combinedLength > config.maxSyncChars) {
      throw new Error(
        `Content too long for synchronous translation. Maximum ${config.maxSyncChars} characters. ` +
        `Use async translation for larger content (up to ${config.maxAsyncChars} characters).`
      );
    }

    if (combinedLength === 0) {
      throw new Error('Content cannot be empty.');
    }

    // Count billable characters — exact content length as received from customer
    const charactersUsed = structuredInputFields
      ? countStructuredCharacters(structuredInputFields)
      : countSourceCharacters(request.content || '');

    logger.info('Starting synchronous translation', {
      userId,
      sourceLang: request.sourceLang,
      targetLang: request.targetLang,
      tone: request.tone,
      isStructured,
      contentLength: combinedLength,
      charactersUsed,
    });

    // Check for sufficient credits (characters)
    const currentBalance = await getCurrentBalance(userId);
    const sufficient = currentBalance >= charactersUsed;

    if (!sufficient) {
      logger.warn('Insufficient credits for translation', {
        userId,
        currentBalance,
        requiredCredits: charactersUsed,
      });
      throw new Error(
        `Insufficient credits. You need ${charactersUsed} credits but only have ${currentBalance}. ` +
        `Please upgrade your plan or wait for your monthly allocation.`
      );
    }

    // Generate content hash for deduplication
    const contentForHash = isStructured
      ? [request.title, request.excerpt, request.content].filter(Boolean).join('|')
      : request.content || '';
    const contentHash = generateContentHash(contentForHash, request.sourceLang, request.targetLang);

    // Check for duplicate job in last 24 hours
    const oneDayAgo = new Date(Date.now() - 24 * 60 * 60 * 1000);
    const duplicateJob = await prisma.translationJob.findFirst({
      where: {
        user_id: userId,
        content_hash: contentHash,
        status: TranslationJobStatus.completed,
        created_at: { gte: oneDayAgo },
      },
    });

    if (duplicateJob && duplicateJob.translation) {
      logger.info('Returning cached translation', {
        userId,
        originalJobId: duplicateJob.id,
        age: Date.now() - duplicateJob.created_at.getTime(),
      });

      const cachedResult = mapStoredTranslation(duplicateJob.translation);

      return {
        jobId: duplicateJob.id,
        status: TranslationStatus.COMPLETED,
        ...cachedResult,
        translation: cachedResult.translation || duplicateJob.translation,
        charactersUsed: duplicateJob.characters_used,
        cost: parseFloat(duplicateJob.cost.toString()),
        customerCost: parseFloat(duplicateJob.customer_cost.toString()),
        processingTimeMs: duplicateJob.processing_time_ms || 0,
        creditBalance: currentBalance,
      };
    }

    // Create translation job record
    const job = await prisma.translationJob.create({
      data: {
        user_id: userId,
        client_job_id: request.clientJobId,
        plugin,
        status: TranslationJobStatus.processing,
        source_lang: request.sourceLang,
        target_lang: request.targetLang,
        tone: request.tone || Tone.NEUTRAL,
        content: contentForHash,
        content_hash: contentHash,
      },
    });

    try {
      let finalTranslation: string;
      let translatedTitle: string | undefined;
      let translatedExcerpt: string | undefined;
      let translatedContent: string | undefined;
      let warnings: string[] | undefined;
      let tokensUsed: number;
      let inputTokens: number;
      let outputTokens: number;
      let modelUsed: string;

      if (isStructured) {
        // Structured path: replace exceptions per field, call translateStructured()
        const titleExc = request.title
          ? exceptionService.replaceExceptions(exceptionRules, request.title)
          : null;
        const excerptExc = request.excerpt
          ? exceptionService.replaceExceptions(exceptionRules, request.excerpt)
          : null;
        const contentExc = request.content
          ? exceptionService.replaceExceptions(exceptionRules, request.content)
          : null;

        const geminiResponse = await geminiClient.translateStructured(
          Object.fromEntries(
            Object.entries({ title: titleExc?.processedText, excerpt: excerptExc?.processedText, content: contentExc?.processedText })
              .filter(([, value]) => value !== undefined)
          ) as Record<string, string>,
          request.sourceLang,
          request.targetLang,
          request.tone || Tone.NEUTRAL
        );

        // Restore exceptions per field
        translatedTitle = geminiResponse.translated_title;
        translatedExcerpt = geminiResponse.translated_excerpt;
        translatedContent = geminiResponse.translated_content;

        if (titleExc && titleExc.replacements.size > 0 && translatedTitle) {
          translatedTitle = exceptionService.restoreExceptions(translatedTitle, titleExc.replacements);
        }
        if (excerptExc && excerptExc.replacements.size > 0 && translatedExcerpt) {
          translatedExcerpt = exceptionService.restoreExceptions(translatedExcerpt, excerptExc.replacements);
        }
        if (contentExc && contentExc.replacements.size > 0 && translatedContent) {
          translatedContent = exceptionService.restoreExceptions(translatedContent, contentExc.replacements);
        }

        // Store as a versioned structured payload to distinguish it from legacy JSON content
        finalTranslation = serializeStructuredFields(
          Object.fromEntries(
            Object.entries({ title: translatedTitle, excerpt: translatedExcerpt, content: translatedContent })
              .filter(([, value]) => value !== undefined)
          ) as StructuredFields
        );

        tokensUsed = geminiResponse.tokens_used;
        inputTokens = geminiResponse.input_tokens;
        outputTokens = geminiResponse.output_tokens;
        modelUsed = geminiResponse.model_used;
      } else {
        // Legacy single-content path
        const { processedText, replacements, matchedExceptions } =
          exceptionService.replaceExceptions(exceptionRules, request.content!);

        const geminiResponse = await geminiClient.translate(
          processedText,
          request.sourceLang,
          request.targetLang,
          request.tone || Tone.NEUTRAL
        );

        finalTranslation = geminiResponse.translation;

        if (replacements.size > 0) {
          finalTranslation = exceptionService.restoreExceptions(finalTranslation, replacements);

          if (matchedExceptions.length > 0) {
            const verification = verifyExceptions(finalTranslation, matchedExceptions);
            if (!verification.passed) {
              warnings = verification.violations.map(
                (v) => `Exception "${v.exception}" may not be preserved (${v.status})`
              );
              logger.warn('Exception verification warnings', {
                userId,
                jobId: job.id,
                violations: verification.violations,
              });
            }
          }
        }

        tokensUsed = geminiResponse.tokens_used;
        inputTokens = geminiResponse.input_tokens;
        outputTokens = geminiResponse.output_tokens;
        modelUsed = geminiResponse.model_used;
      }

      const processingTime = Date.now() - startTime;
      const internalCost = calculateCost(inputTokens, outputTokens, modelUsed);

      // Look up user's subscription to get customer cost per character
      const subscription = await prisma.subscription.findFirst({
        where: { user_id: userId },
        select: { customer_cost_per_char: true },
      });
      const costPerChar = subscription?.customer_cost_per_char
        ? Number(subscription.customer_cost_per_char)
        : 0;
      const customerCost = calculateCustomerCostByChars(charactersUsed, costPerChar);

      // Deduct credits (characters)
      const transaction = await deductCredits(
        userId,
        charactersUsed,
        `Translation: ${request.sourceLang} → ${request.targetLang}`,
        job.id
      );
      const newBalance = transaction.balanceAfter;

      // Update job to completed
      await prisma.translationJob.update({
        where: { id: job.id },
        data: {
          status: TranslationJobStatus.completed,
          translation: finalTranslation,
          model: modelUsed,
          characters_used: charactersUsed,
          tokens_used: tokensUsed,
          input_tokens: inputTokens,
          output_tokens: outputTokens,
          cost: internalCost,
          customer_cost: customerCost,
          processing_time_ms: processingTime,
          completed_at: new Date(),
        },
      });

      // Track metrics
      trackTranslationJob('gemini', 'completed', 'sync', processingTime);
      trackTokensProcessed('gemini', tokensUsed);

      logger.info('Synchronous translation completed', {
        userId,
        jobId: job.id,
        isStructured,
        charactersUsed,
        tokensUsed,
        cost: internalCost,
        processingTimeMs: processingTime,
        newBalance,
        warnings,
      });

      // For the response, use combined text for backward-compat `translation` field
      const translationForResponse = isStructured
        ? [translatedTitle, translatedExcerpt, translatedContent].filter(Boolean).join('\n\n')
        : finalTranslation;

      const response: TranslationResponse & { warnings?: string[] } = {
        jobId: job.id,
        status: TranslationStatus.COMPLETED,
        translation: translationForResponse,
        translatedTitle,
        translatedExcerpt,
        translatedContent,
        charactersUsed,
        cost: internalCost,
        customerCost,
        processingTimeMs: processingTime,
        creditBalance: newBalance,
      };

      if (warnings && warnings.length > 0) {
        response.warnings = warnings;
      }

      return response;
    } catch (error) {
      const processingTime = Date.now() - startTime;
      const errorMessage = error instanceof Error ? error.message : 'Unknown error occurred';

      // Update job to failed
      await prisma.translationJob.update({
        where: { id: job.id },
        data: {
          status: TranslationJobStatus.failed,
          error_message: errorMessage,
          processing_time_ms: processingTime,
          completed_at: new Date(),
        },
      });

      // Track metrics
      trackTranslationJob('gemini', 'failed', 'sync', processingTime);

      logger.error('Synchronous translation failed', {
        userId,
        jobId: job.id,
        error: errorMessage,
        processingTimeMs: processingTime,
      });

      throw new Error(`Translation failed: ${errorMessage}`);
    }
  }

  /**
   * Synchronous bulk translation — packs strings into Gemini-call-sized batches,
   * deducts credits once for the full batch.
   */
  async translateBulkSync(
    userId: string,
    request: BulkTranslationRequest,
    plugin: string = 'translate'
  ): Promise<BulkTranslationResponse> {
    const startTime = Date.now();
    const exceptionRules = exceptionService.normalizeRules(request.exceptions);

    // Count billable characters — exact content length as received from customer
    const totalCharactersUsed = request.strings.reduce(
      (sum, s) => sum + countSourceCharacters(s.content),
      0
    );

    logger.info('Starting bulk translation', {
      userId,
      stringCount: request.strings.length,
      totalCharactersUsed,
      sourceLang: request.sourceLang,
      targetLang: request.targetLang,
    });

    // Fail fast if insufficient credits
    const currentBalance = await getCurrentBalance(userId);
    const sufficient = currentBalance >= totalCharactersUsed;

    if (!sufficient) {
      logger.warn('Insufficient credits for bulk translation', {
        userId,
        currentBalance,
        requiredCredits: totalCharactersUsed,
      });
      throw new Error(
        `Insufficient credits. You need ${totalCharactersUsed} credits but only have ${currentBalance}. ` +
        `Please upgrade your plan or wait for your monthly allocation.`
      );
    }

    // Create a translation job record for the bulk operation
    const bulkContent = JSON.stringify(request.strings.map(s => ({ id: s.id, content: s.content })));
    const contentHash = generateContentHash(bulkContent, request.sourceLang, request.targetLang);
    const job = await prisma.translationJob.create({
      data: {
        user_id: userId,
        plugin,
        status: TranslationJobStatus.processing,
        source_lang: request.sourceLang,
        target_lang: request.targetLang,
        tone: request.tone || Tone.NEUTRAL,
        content: bulkContent,
        content_hash: contentHash,
      },
    });

    // Pre-process: replace exceptions in each string
    const perStringReplacements: Map<string, { replacements: Map<string, string>; matchedExceptions: string[] }> = new Map();
    const processedStrings = [];

    for (const s of request.strings) {
      const { processedText, replacements, matchedExceptions } =
        exceptionService.replaceExceptions(exceptionRules, s.content);
      processedStrings.push({ id: s.id, content: processedText });
      if (replacements.size > 0) {
        perStringReplacements.set(s.id, { replacements, matchedExceptions });
      }
    }

    const batches = planBatches(processedStrings, {
      maxChars: GEMINI_CALL_CHAR_BUDGET,
      maxCount: GEMINI_CALL_MAX_STRINGS,
      maxPayloadBytes: GEMINI_CALL_MAX_PAYLOAD_BYTES,
    });

    logger.info('Bulk translation batching', {
      totalStrings: processedStrings.length,
      batchCount: batches.length,
      batchSizes: batches.map(b => b.length),
      maxBatchChars: GEMINI_CALL_CHAR_BUDGET,
      maxBatchStrings: GEMINI_CALL_MAX_STRINGS,
      maxBatchPayloadBytes: GEMINI_CALL_MAX_PAYLOAD_BYTES,
    });

    // Process all batches sequentially
    const allResults: Array<{ id: string; translation: string; success: boolean }> = [];
    let modelUsed = 'gemini-3.1-flash-lite';
    let totalTokensUsed = 0;
    let totalInputTokens = 0;
    let totalOutputTokens = 0;

    for (const batch of batches) {
      const bulkResponse = await geminiClient.translateBulk(
        batch,
        request.sourceLang,
        request.targetLang,
        request.tone || Tone.NEUTRAL
      );

      modelUsed = bulkResponse.model_used;
      totalTokensUsed += bulkResponse.tokens_used;
      totalInputTokens += bulkResponse.input_tokens;
      totalOutputTokens += bulkResponse.output_tokens;

      // Post-process: restore exceptions in each result
      for (const result of bulkResponse.results) {
        const meta = perStringReplacements.get(result.id);
        if (meta && result.success && result.translation) {
          result.translation = exceptionService.restoreExceptions(result.translation, meta.replacements);
        }
      }

      allResults.push(...bulkResponse.results);
    }

    const failedCount = allResults.filter(r => !r.success).length;
    const processingTime = Date.now() - startTime;

    // Deduct credits once for the entire bulk job, linked to the job record
    await deductCredits(
      userId,
      totalCharactersUsed,
      `Bulk translation: ${request.sourceLang} → ${request.targetLang} (${request.strings.length} strings)`,
      job.id
    );

    // Update the job record with results
    const translationSummary = JSON.stringify(allResults);
    await prisma.translationJob.update({
      where: { id: job.id },
      data: {
        status: failedCount === request.strings.length
          ? TranslationJobStatus.failed
          : TranslationJobStatus.completed,
        translation: translationSummary,
        model: modelUsed,
        characters_used: totalCharactersUsed,
        tokens_used: totalTokensUsed,
        input_tokens: totalInputTokens,
        output_tokens: totalOutputTokens,
        processing_time_ms: processingTime,
        completed_at: new Date(),
        error_message: failedCount > 0
          ? `${failedCount} of ${request.strings.length} strings failed`
          : null,
      },
    });

    logger.info('Bulk translation completed', {
      userId,
      jobId: job.id,
      stringCount: request.strings.length,
      failedCount,
      totalCharactersUsed,
      totalTokensUsed,
      processingTimeMs: processingTime,
    });

    return {
      results: allResults,
      totalCharactersUsed,
      failedCount,
    };
  }

  /**
   * Asynchronous translation job submission (for content up to 50000 characters)
   */
  async translateAsync(
    userId: string,
    request: JobSubmitRequest | SiteContentJobSubmitRequest,
    plugin: string = 'translate',
    siteUrl?: string
  ): Promise<JobSubmitResponse> {
    const timingStartMs = Date.now();
    const siteContent = isSiteContentRequest(request)
      ? validateSiteContentRequest(request, config.maxAsyncChars)
      : undefined;
    const legacyRequest = siteContent ? undefined : request as JobSubmitRequest;
    const hasLegacyStructuredField = legacyRequest !== undefined &&
      (legacyRequest.title !== undefined || legacyRequest.excerpt !== undefined);
    const hasCustomFields = legacyRequest?.fields !== undefined;
    const isStructured = hasLegacyStructuredField || hasCustomFields;
    const structuredFields = isStructured && legacyRequest
      ? mergeStructuredFields(
        {
          title: legacyRequest.title,
          excerpt: legacyRequest.excerpt,
          content: legacyRequest.content,
        },
        legacyRequest.fields,
        config.maxAsyncChars
      )
      : undefined;
    const legacyContent = legacyRequest?.content || '';
    const sourceContent = siteContent
      ? serializeSiteContentRequest(siteContent)
      : structuredFields
        ? serializeStructuredFields(structuredFields)
        : legacyContent;
    const charactersUsed = siteContent
      ? countSiteContentCharacters(siteContent)
      : structuredFields
        ? countStructuredCharacters(structuredFields)
        : countSourceCharacters(legacyContent);

    if (charactersUsed > config.maxAsyncChars) {
      throw new Error(
        `Content too long. Maximum ${config.maxAsyncChars} characters allowed for async translation.`
      );
    }

    if (charactersUsed === 0) {
      throw new Error('Content cannot be empty.');
    }

    logger.info('Submitting async translation job', {
      userId,
      sourceLang: request.sourceLang,
      targetLang: request.targetLang,
      tone: request.tone,
      contentLength: sourceContent.length,
      charactersUsed,
      isStructured,
      resourceType: siteContent?.resourceType,
      fieldCount: structuredFields ? Object.keys(structuredFields).length : 0,
      hasCallback: !!request.callbackUrl,
    });

    // Check for sufficient credits (characters)
    const currentBalance = await getCurrentBalance(userId);
    const timingAfterBalanceMs = Date.now();
    const sufficient = currentBalance >= charactersUsed;

    if (!sufficient) {
      logger.warn('Insufficient credits for async translation', {
        userId,
        currentBalance,
        requiredCredits: charactersUsed,
      });
      throw new Error(
        `Insufficient credits. You need ${charactersUsed} credits but only have ${currentBalance}. ` +
        `Please upgrade your plan or wait for your monthly allocation.`
      );
    }

    const contentHash = generateContentHash(sourceContent, request.sourceLang, request.targetLang);
    const deduplicationWindow = new Date(Date.now() - 10 * 60 * 1000);
    const identity = asyncJobIdentity(
      userId,
      plugin,
      sourceContent,
      request.sourceLang,
      request.targetLang,
      request.tone || Tone.NEUTRAL,
      request.clientJobId,
      request.callbackUrl,
      request.callbackSecret,
      siteUrl
    );
    const claimed = await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`SELECT pg_advisory_xact_lock(hashtextextended(${identity}, 0))`;

      const duplicatePendingJob = await tx.translationJob.findFirst({
        where: {
          user_id: userId,
          plugin,
          content_hash: contentHash,
          source_lang: request.sourceLang,
          target_lang: request.targetLang,
          tone: request.tone || Tone.NEUTRAL,
          status: { in: [TranslationJobStatus.pending, TranslationJobStatus.processing] },
          created_at: { gte: deduplicationWindow },
          client_job_id: request.clientJobId ?? null,
          callback_url: request.callbackUrl ?? null,
          callback_secret: request.callbackSecret ?? null,
          site_url: siteUrl ?? null,
        },
      });

      if (duplicatePendingJob) {
        return { job: duplicatePendingJob, created: false };
      }

      const job = await tx.translationJob.create({
        data: {
          user_id: userId,
          client_job_id: request.clientJobId,
          plugin,
          status: TranslationJobStatus.pending,
          source_lang: request.sourceLang,
          target_lang: request.targetLang,
          tone: request.tone || Tone.NEUTRAL,
          content: sourceContent,
          content_hash: contentHash,
          callback_url: request.callbackUrl,
          callback_secret: request.callbackSecret,
          site_url: siteUrl,
        },
      });

      await translationQueue.add('translate', {
        jobId: job.id,
        clientJobId: request.clientJobId,
        userId,
        exceptions: exceptionService.normalizeRules(request.exceptions),
        content: siteContent || isStructured ? undefined : legacyContent,
        fields: structuredFields,
        siteContent,
        sourceLang: request.sourceLang,
        targetLang: request.targetLang,
        tone: request.tone || Tone.NEUTRAL,
        callbackUrl: request.callbackUrl,
        callbackSecret: request.callbackSecret,
      });

      return { job, created: true };
    });

    const timingAfterTransactionMs = Date.now();

    if (!claimed.created) {
      const duplicatePendingJob = claimed.job;
      logger.info('Duplicate async job found', {
        userId,
        existingJobId: duplicatePendingJob.id,
        timing_ms: {
          balance: timingAfterBalanceMs - timingStartMs,
          transaction: timingAfterTransactionMs - timingAfterBalanceMs,
          total: Date.now() - timingStartMs,
        },
      });

      return {
        jobId: duplicatePendingJob.id,
        status: duplicatePendingJob.status as TranslationStatus,
        clientJobId: duplicatePendingJob.client_job_id || undefined,
      };
    }

    const job = claimed.job;

    logger.info('Async translation job created', {
      userId,
      jobId: job.id,
      clientJobId: request.clientJobId,
      isStructured,
      timing_ms: {
        balance: timingAfterBalanceMs - timingStartMs,
        transaction: timingAfterTransactionMs - timingAfterBalanceMs,
        total: Date.now() - timingStartMs,
      },
    });

    return {
      jobId: job.id,
      status: TranslationStatus.PENDING,
      clientJobId: request.clientJobId,
    };
  }

  /**
   * Get job status
   */
  async getJobStatus(userId: string, jobId: string): Promise<JobStatusResponse> {
    const job = await prisma.translationJob.findUnique({
      where: { id: jobId },
    });

    if (!job) {
      throw new Error('Translation job not found.');
    }

    if (job.user_id !== userId) {
      throw new Error('Access denied. This job does not belong to you.');
    }

    const storedTranslation = mapStoredTranslation(job.translation);
    const storedSiteContent = parseSerializedSiteContentRequest(job.content);

    return {
      jobId: job.id,
      clientJobId: job.client_job_id || undefined,
      status: job.status as TranslationStatus,
      sourceLang: job.source_lang,
      targetLang: job.target_lang,
      tone: job.tone as Tone,
      ...(storedSiteContent ? siteContentPollMetadata(storedSiteContent) : {}),
      ...storedTranslation,
      charactersUsed: job.characters_used > 0 ? job.characters_used : undefined,
      cost: job.cost.toNumber() > 0 ? job.cost.toNumber() : undefined,
      customerCost: job.customer_cost.toNumber() > 0 ? job.customer_cost.toNumber() : undefined,
      errorMessage: job.error_message || undefined,
      processingTimeMs: job.processing_time_ms || undefined,
      createdAt: job.created_at.toISOString(),
      updatedAt: job.updated_at.toISOString(),
      completedAt: job.completed_at?.toISOString(),
    };
  }

  /**
   * Cancel an active job
   */
  async cancelJob(
    userId: string,
    jobId: string,
    scope: { siteUrl: string; plugin: string }
  ): Promise<JobStatusResponse> {
    const ownershipScope = {
      id: jobId,
      user_id: userId,
      plugin: scope.plugin,
      site_url: scope.siteUrl,
    };
    const job = await prisma.translationJob.findFirst({
      where: ownershipScope,
    });

    if (!job) {
      throw new Error('Translation job not found.');
    }

    if (job.status === TranslationJobStatus.cancelled) {
      return this.toJobStatusResponse(job);
    }

    const terminalStatuses = new Set<TranslationJobStatus>([
      TranslationJobStatus.completed,
      TranslationJobStatus.failed,
    ]);
    if (terminalStatuses.has(job.status)) {
      throw new Error(`Cannot cancel job with terminal status '${job.status}'.`);
    }

    const activeStatuses = new Set<TranslationJobStatus>([
      TranslationJobStatus.pending,
      TranslationJobStatus.processing,
    ]);
    if (!activeStatuses.has(job.status)) {
      throw new Error(
        `Cannot cancel job with status '${job.status}'. Only active jobs can be cancelled.`
      );
    }

    const cancellation = await prisma.translationJob.updateMany({
      where: {
        id: jobId,
        user_id: userId,
        plugin: scope.plugin,
        site_url: scope.siteUrl,
        status: { in: [TranslationJobStatus.pending, TranslationJobStatus.processing] },
      },
      data: {
        status: TranslationJobStatus.cancelled,
        completed_at: new Date(),
      },
    });

    if (!cancellation || typeof cancellation.count !== 'number') {
      throw new Error('Cancellation could not be persisted.');
    }

    const updatedJob = await prisma.translationJob.findUnique({
      where: { id: jobId },
    });

    if (!updatedJob) {
      throw new Error('Cancellation could not be persisted.');
    }

    if (cancellation.count !== 1) {
      if (updatedJob.status === TranslationJobStatus.cancelled) {
        return this.toJobStatusResponse(updatedJob);
      }
      if (terminalStatuses.has(updatedJob.status)) {
        throw new Error(`Cannot cancel job with terminal status '${updatedJob.status}'.`);
      }
      throw new Error('Cancellation could not be persisted.');
    }

    // Track metrics
    trackTranslationJob('gemini', 'cancelled', 'async');

    logger.info('Translation job cancelled', {
      userId,
      jobId,
    });

    return this.toJobStatusResponse(updatedJob);
  }

  private toJobStatusResponse(job: any): JobStatusResponse {
    const storedSiteContent = parseSerializedSiteContentRequest(job.content);
    return {
      jobId: job.id,
      clientJobId: job.client_job_id || undefined,
      status: job.status as TranslationStatus,
      sourceLang: job.source_lang,
      targetLang: job.target_lang,
      tone: job.tone as Tone,
      ...(storedSiteContent ? siteContentPollMetadata(storedSiteContent) : {}),
      createdAt: job.created_at.toISOString(),
      updatedAt: job.updated_at.toISOString(),
      completedAt: job.completed_at?.toISOString(),
    };
  }

  /**
   * Get translation jobs for a user (with pagination and filtering)
   */
  async getJobs(
    userId: string,
    options: {
      status?: TranslationStatus | TranslationStatus[];
      clientJobId?: string;
      siteUrl?: string;
      plugin?: string;
      limit?: number;
      offset?: number;
      sortBy?: 'created_at' | 'updated_at';
      sortOrder?: 'asc' | 'desc';
    } = {}
  ) {
    const {
      status,
      clientJobId,
      siteUrl,
      plugin,
      limit = 50,
      offset = 0,
      sortBy = 'created_at',
      sortOrder = 'desc',
    } = options;

    const where: any = { user_id: userId };

    if (clientJobId) {
      where.client_job_id = clientJobId;
    }

    if (siteUrl) {
      where.site_url = siteUrl;
    }

    if (plugin) {
      where.plugin = plugin;
    }

    if (status) {
      where.status = Array.isArray(status)
        ? { in: status }
        : status;
    }

    const jobs = await prisma.translationJob.findMany({
      where,
      orderBy: { [sortBy]: sortOrder },
      take: limit,
      skip: offset,
      select: {
        id: true,
        client_job_id: true,
        status: true,
        source_lang: true,
        target_lang: true,
        tone: true,
        characters_used: true,
        cost: true,
        customer_cost: true,
        error_message: true,
        processing_time_ms: true,
        created_at: true,
        updated_at: true,
        completed_at: true,
      },
    });

    const total = await prisma.translationJob.count({ where });

    return {
      jobs: jobs.map((job) => ({
        jobId: job.id,
        clientJobId: job.client_job_id || undefined,
        status: job.status as TranslationStatus,
        sourceLang: job.source_lang,
        targetLang: job.target_lang,
        tone: job.tone as Tone,
        charactersUsed: job.characters_used > 0 ? job.characters_used : undefined,
        cost: job.cost.toNumber() > 0 ? job.cost.toNumber() : undefined,
        customerCost: job.customer_cost.toNumber() > 0 ? job.customer_cost.toNumber() : undefined,
        errorMessage: job.error_message || undefined,
        processingTimeMs: job.processing_time_ms || undefined,
        createdAt: job.created_at.toISOString(),
        updatedAt: job.updated_at.toISOString(),
        completedAt: job.completed_at?.toISOString(),
      })),
      total,
      limit,
      offset,
    };
  }

  /**
   * Retry a failed translation job
   */
  async retryJob(userId: string, jobId: string): Promise<JobStatusResponse> {
    const job = await prisma.translationJob.findUnique({
      where: { id: jobId },
    });

    if (!job) {
      throw new Error('Translation job not found.');
    }

    if (job.user_id !== userId) {
      throw new Error('Access denied. This job does not belong to you.');
    }

    // Can only retry failed jobs
    if (job.status !== TranslationJobStatus.failed) {
      throw new Error(
        `Cannot retry job with status '${job.status}'. Only failed jobs can be retried.`
      );
    }

    let retryFields: StructuredFields | undefined;
    let retrySiteContent: SiteContentJobSubmitRequest | undefined;
    let retryContent: string | undefined = job.content;
    retrySiteContent = parseSerializedSiteContentRequest(job.content);
    const parsedRetryFields = retrySiteContent
      ? undefined
      : parseSerializedStructuredFields(job.content);
    if (retrySiteContent) {
      retryContent = undefined;
    } else if (parsedRetryFields) {
      retryFields = parsedRetryFields;
      retryContent = undefined;
    }

    // Reset job to pending status
    const updatedJob = await prisma.translationJob.update({
      where: { id: jobId },
      data: {
        status: TranslationJobStatus.pending,
        error_message: null,
        translation: null,
        tokens_used: 0,
        cost: 0,
        processing_time_ms: null,
        completed_at: null,
      },
    });

    await translationQueue.add('translate', {
      jobId: updatedJob.id,
      clientJobId: updatedJob.client_job_id || undefined,
      userId,
      content: retryContent,
      fields: retryFields,
      siteContent: retrySiteContent,
      exceptions: retrySiteContent
        ? exceptionService.normalizeRules(retrySiteContent.exceptions)
        : undefined,
      sourceLang: job.source_lang,
      targetLang: job.target_lang,
      tone: job.tone as Tone,
      callbackUrl: job.callback_url || undefined,
      callbackSecret: job.callback_secret || undefined,
    });

    logger.info('Translation job queued for retry', {
      userId,
      jobId,
      previousErrorMessage: job.error_message,
    });

    return {
      jobId: updatedJob.id,
      clientJobId: updatedJob.client_job_id || undefined,
      status: updatedJob.status as TranslationStatus,
      sourceLang: updatedJob.source_lang,
      targetLang: updatedJob.target_lang,
      tone: updatedJob.tone as Tone,
      ...(retrySiteContent ? siteContentPollMetadata(retrySiteContent) : {}),
      createdAt: updatedJob.created_at.toISOString(),
      updatedAt: updatedJob.updated_at.toISOString(),
      completedAt: updatedJob.completed_at?.toISOString(),
    };
  }
}

// Export singleton instance
export const translationService = new TranslationService();