import path from 'node:path';
import { randomUUID } from 'node:crypto';
import type pg from 'pg';
import { pool } from '../db.js';
import { config } from '../config.js';
import { closePool as closeApiExportPool } from '../../../api/src/core/db.js';
import { buildExportPayloadForJob } from '../../../api/src/features/tenant-content/exports.js';
import { normalizeStorageProvider, putStorageObject, storageBucket, validateObjectKey } from '../storage.js';
import { renderDailyPracticePackage } from './daily-practice-package.js';
import { renderExportDocx, renderExportPdf } from './export-renderer.js';

interface ExportJobRow {
  id: string;
  tenantId: string;
  createdBy: string | null;
  exportType: string;
  format: 'pdf' | 'docx' | 'daily_practice_zip';
  scopeType: string;
  scopeId: string;
  status: string;
  attemptCount: number;
  maxAttempts: number;
  options: Record<string, unknown>;
  outputMetadata: Record<string, unknown>;
  createdAt: string;
}

interface ExportWorkerResult {
  processed: number;
  completed: number;
  failed: number;
  retrying: number;
  skipped: number;
}

interface RenderedExport {
  body: Buffer;
  mimeType: string;
  extension: 'pdf' | 'docx' | 'zip';
}

const ASSET_VISIBILITIES = new Set(['public', 'tenant', 'members', 'svip', 'private']);

function objectValue(value: unknown): Record<string, unknown> {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as Record<string, unknown> : {};
}

function boolValue(value: unknown, fallback: boolean) {
  return typeof value === 'boolean' ? value : fallback;
}

function stringValue(value: unknown, fallback = '') {
  return typeof value === 'string' && value.trim() ? value.trim() : fallback;
}

function truncate(value: unknown, max = 1900) {
  return String(value ?? '').slice(0, max);
}

function errorMessage(error: unknown) {
  return error instanceof Error ? error.message : String(error);
}

function errorCode(error: unknown) {
  return typeof error === 'object' && error !== null && 'code' in error
    ? String((error as { code?: unknown }).code || 'EXPORT_WORKER_ERROR')
    : 'EXPORT_WORKER_ERROR';
}

function backoffSeconds(attemptCount: number) {
  const backoffs = config.exportBackoffSeconds.length ? config.exportBackoffSeconds : [30, 120, 600, 1800];
  return backoffs[Math.min(Math.max(0, attemptCount - 1), backoffs.length - 1)];
}

function safeFileBase(value: string) {
  const ascii = value
    .normalize('NFKD')
    .replace(/[^\w.-]+/g, '-')
    .replace(/-+/g, '-')
    .replace(/^-+|-+$/g, '')
    .slice(0, 80);
  return ascii || 'question-export';
}

function safeDisplayFileName(title: string, extension: string) {
  return `${title.trim().replace(/[\\/:*?"<>|]+/g, '-').replace(/\s+/g, '-').slice(0, 120) || '题库导出'}.${extension}`;
}

function objectKeyFor(job: ExportJobRow, title: string, extension: string) {
  const created = new Date(job.createdAt || Date.now());
  const yyyy = Number.isFinite(created.getTime()) ? created.getUTCFullYear() : new Date().getUTCFullYear();
  const mm = String((Number.isFinite(created.getTime()) ? created.getUTCMonth() : new Date().getUTCMonth()) + 1).padStart(2, '0');
  return validateObjectKey(
    job.tenantId,
    `${job.tenantId}/exports/${yyyy}/${mm}/${job.id}-${safeFileBase(title)}.${extension}`,
  );
}

function assetVisibility(options: Record<string, unknown>) {
  const requested = stringValue(options.assetVisibility, boolValue(options.publishToAssets, false) ? 'tenant' : 'private');
  return ASSET_VISIBILITIES.has(requested) ? requested : 'private';
}

function assetStatus(options: Record<string, unknown>) {
  return boolValue(options.publishToAssets, false) ? 'active' : 'active';
}

function accessRules(options: Record<string, unknown>) {
  return objectValue(options.assetAccessRules);
}

async function claimExportJobs() {
  const client = await pool.connect();
  try {
    await client.query('begin');
    const result = await client.query<ExportJobRow>(
      `
      select id, tenant_id as "tenantId", created_by as "createdBy",
             export_type as "exportType", format, scope_type as "scopeType",
             scope_id as "scopeId", status, attempt_count as "attemptCount",
             max_attempts as "maxAttempts", options,
             output_metadata as "outputMetadata", created_at as "createdAt"
      from public.content_export_jobs
      where status = 'pending'
        and format in ('pdf', 'docx', 'daily_practice_zip')
        and attempt_count < max_attempts
        and (next_attempt_at is null or next_attempt_at <= now())
      order by created_at asc
      limit $1
      for update skip locked
      `,
      [config.exportBatchSize],
    );

    const ids = result.rows.map(row => row.id);
    if (ids.length) {
      await client.query(
        `
        update public.content_export_jobs
        set status = 'rendering',
            locked_at = now(),
            locked_by = $2,
            attempt_count = attempt_count + 1,
            started_at = coalesce(started_at, now()),
            error_message = null,
            updated_at = now()
        where id = any($1::uuid[])
        `,
        [ids, config.exportWorkerId],
      );
    }
    await client.query('commit');
    return result.rows;
  } catch (error) {
    await client.query('rollback');
    throw error;
  } finally {
    client.release();
  }
}

async function renderJob(job: ExportJobRow): Promise<{ rendered: RenderedExport; title: string; questionCount: number }> {
  const client = await pool.connect();
  try {
    const payload = await buildExportPayloadForJob({
      client,
      tenantId: job.tenantId,
      jobId: job.id,
      createdAt: new Date().toISOString(),
    });
    const rendered = job.format === 'pdf'
      ? await renderExportPdf(payload)
      : job.format === 'docx'
        ? await renderExportDocx(payload)
        : await renderDailyPracticePackage(payload);
    return {
      rendered,
      title: payload.title || '题库导出',
      questionCount: payload.summary.questionCount,
    };
  } finally {
    client.release();
  }
}

async function insertExportAsset(
  client: pg.PoolClient,
  job: ExportJobRow,
  input: {
    title: string;
    fileName: string;
    extension: 'pdf' | 'docx' | 'zip';
    mimeType: string;
    bucket: string;
    objectKey: string;
    sizeBytes: number;
    checksumSha256: string;
    uploadDetails: Record<string, unknown>;
  },
) {
  const visibility = assetVisibility(job.options || {});
  const status = assetStatus(job.options || {});
  const assetType = input.extension === 'pdf' ? 'pdf' : input.extension === 'docx' ? 'document' : 'package';
  const asset = await client.query<{ id: string }>(
    `
    insert into public.content_assets (
      tenant_id, asset_key, title, category, description, file_name,
      asset_type, storage_provider, bucket, object_key, mime_type,
      file_size_bytes, checksum_sha256, upload_status, verified_at,
      verified_size_bytes, verified_checksum_sha256, verification_details,
      security_scan_status, security_scanned_at, security_scan_provider,
      security_scan_summary,
      preview_status, visibility, is_public, status, access_rules,
      metadata, created_by, updated_by, source
    )
    values (
      $1, $2, $3, $4, $5, $6,
      $7, $8, $9, $10, $11,
      $12, $13, 'verified', now(),
      $12, $13, $14::jsonb,
      'passed', now(), 'trusted_export_worker',
      $15::jsonb,
      $16, $17, $18, $19, $20::jsonb,
      $21::jsonb, $22, $22, 'content_export_worker'
    )
    returning id
    `,
    [
      job.tenantId,
      `content-export:${job.id}`,
      input.title,
      '题库导出',
      `由题库导出任务 ${job.id} 自动生成`,
      input.fileName,
      assetType,
      normalizeStorageProvider(String(job.outputMetadata?.storageProvider || config.storageDefaultProvider)),
      input.bucket,
      input.objectKey,
      input.mimeType,
      input.sizeBytes,
      input.checksumSha256,
      JSON.stringify({
        exportWorker: {
          ...input.uploadDetails,
          workerId: config.exportWorkerId,
          checkedAt: new Date().toISOString(),
        },
      }),
      JSON.stringify({
        riskLevel: 'none',
        issueCodes: [],
        provider: 'trusted_export_worker',
        generatedBy: 'content_export_worker',
      }),
      input.extension === 'pdf' ? 'ready' : 'none',
      visibility,
      visibility === 'public',
      status,
      JSON.stringify(accessRules(job.options || {})),
      JSON.stringify({
        exportJobId: job.id,
        exportType: job.exportType,
        format: job.format,
        scopeType: job.scopeType,
        scopeId: job.scopeId,
        publishToAssets: boolValue(job.options?.publishToAssets, false),
        watermarkText: stringValue(job.options?.watermarkText, ''),
      }),
      job.createdBy,
    ],
  );
  return asset.rows[0].id;
}

async function markExportCompleted(job: ExportJobRow, input: {
  assetId: string;
  title: string;
  fileName: string;
  mimeType: string;
  sizeBytes: number;
  checksumSha256: string;
  bucket: string;
  objectKey: string;
  questionCount: number;
  uploadDetails: Record<string, unknown>;
}) {
  const client = await pool.connect();
  try {
    await client.query('begin');
    await client.query(
      `
      update public.content_export_jobs
      set status = 'completed',
          asset_id = $3,
          question_count = $4,
          summary = coalesce(summary, '{}'::jsonb) || $5::jsonb,
          output_metadata = coalesce(output_metadata, '{}'::jsonb) || $6::jsonb,
          error_message = null,
          locked_at = null,
          locked_by = null,
          next_attempt_at = null,
          finished_at = now(),
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        job.tenantId,
        job.id,
        input.assetId,
        input.questionCount,
        JSON.stringify({
          questionCount: input.questionCount,
          renderedTitle: input.title,
        }),
        JSON.stringify({
          delivery: 'content_asset',
          assetId: input.assetId,
          fileName: input.fileName,
          mimeType: input.mimeType,
          sizeBytes: input.sizeBytes,
          checksumSha256: input.checksumSha256,
          bucket: input.bucket,
          objectKey: input.objectKey,
          renderer: 'worker:exports',
          renderedAt: new Date().toISOString(),
          upload: input.uploadDetails,
        }),
      ],
    );
    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.questions.export_rendered', 'content_export_job', $3, $4::jsonb)
      `,
      [
        job.tenantId,
        job.createdBy,
        job.id,
        JSON.stringify({
          assetId: input.assetId,
          format: job.format,
          mimeType: input.mimeType,
          sizeBytes: input.sizeBytes,
          checksumSha256: input.checksumSha256,
          workerId: config.exportWorkerId,
        }),
      ],
    );
    await client.query('commit');
  } catch (error) {
    await client.query('rollback');
    throw error;
  } finally {
    client.release();
  }
}

async function markExportFailed(job: ExportJobRow, error: unknown) {
  const nextAttempt = job.attemptCount + 1;
  const willRetry = nextAttempt < job.maxAttempts;
  const status = willRetry ? 'pending' : 'failed';
  await pool.query(
    `
    update public.content_export_jobs
    set status = $3,
        error_message = $4,
        output_metadata = coalesce(output_metadata, '{}'::jsonb) || $5::jsonb,
        next_attempt_at = case when $6::boolean then now() + make_interval(secs => $7::integer) else null end,
        locked_at = null,
        locked_by = null,
        finished_at = case when $3 = 'failed' then now() else finished_at end,
        updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [
      job.tenantId,
      job.id,
      status,
      truncate(errorMessage(error)),
      JSON.stringify({
        lastWorkerError: {
          code: errorCode(error),
          message: truncate(errorMessage(error)),
          workerId: config.exportWorkerId,
          failedAt: new Date().toISOString(),
          nextAttempt,
          maxAttempts: job.maxAttempts,
          willRetry,
        },
      }),
      willRetry,
      backoffSeconds(nextAttempt),
    ],
  );

  await pool.query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2, $3, 'content_export_job', $4, $5::jsonb)
    `,
    [
      job.tenantId,
      job.createdBy,
      willRetry ? 'content.questions.export_retry_scheduled' : 'content.questions.export_failed',
      job.id,
      JSON.stringify({
        code: errorCode(error),
        message: truncate(errorMessage(error)),
        workerId: config.exportWorkerId,
        nextAttempt,
        maxAttempts: job.maxAttempts,
      }),
    ],
  );

  return willRetry ? 'retrying' : 'failed';
}

async function processExportJob(job: ExportJobRow) {
  const { rendered, title, questionCount } = await renderJob(job);
  const extension = rendered.extension;
  const bucket = storageBucket(String(job.outputMetadata?.storageBucket || ''));
  const objectKey = objectKeyFor(job, title, extension);
  const fileName = safeDisplayFileName(title, extension);
  const upload = await putStorageObject({
    tenantId: job.tenantId,
    provider: normalizeStorageProvider(String(job.outputMetadata?.storageProvider || config.storageDefaultProvider)),
    bucket,
    objectKey,
    body: rendered.body,
    mimeType: rendered.mimeType,
  });

  const client = await pool.connect();
  let assetId = '';
  try {
    await client.query('begin');
    assetId = await insertExportAsset(client, job, {
      title,
      fileName,
      extension,
      mimeType: rendered.mimeType,
      bucket,
      objectKey,
      sizeBytes: upload.sizeBytes,
      checksumSha256: upload.checksumSha256,
      uploadDetails: {
        provider: upload.provider,
        localPath: upload.localPath ? path.relative(process.cwd(), upload.localPath) : undefined,
        etag: upload.etag,
      },
    });
    await client.query('commit');
  } catch (error) {
    await client.query('rollback');
    throw error;
  } finally {
    client.release();
  }

  await markExportCompleted(job, {
    assetId,
    title,
    fileName,
    mimeType: rendered.mimeType,
    sizeBytes: upload.sizeBytes,
    checksumSha256: upload.checksumSha256,
    bucket,
    objectKey,
    questionCount,
    uploadDetails: {
      provider: upload.provider,
      localPath: upload.localPath ? path.relative(process.cwd(), upload.localPath) : undefined,
      etag: upload.etag,
    },
  });
}

export async function processExportBatch(): Promise<ExportWorkerResult> {
  const jobs = await claimExportJobs();
  const result: ExportWorkerResult = {
    processed: jobs.length,
    completed: 0,
    failed: 0,
    retrying: 0,
    skipped: 0,
  };

  for (const job of jobs) {
    try {
      if (job.format !== 'pdf' && job.format !== 'docx' && job.format !== 'daily_practice_zip') {
        result.skipped += 1;
        continue;
      }
      await processExportJob(job);
      result.completed += 1;
    } catch (error) {
      const state = await markExportFailed(job, error);
      if (state === 'retrying') result.retrying += 1;
      else result.failed += 1;
    }
  }

  return result;
}

export async function closeExportExecutorPool() {
  await closeApiExportPool();
}
