import crypto from 'node:crypto';
import { pool } from '../db.js';
import { config } from '../config.js';
import {
  executePublicQuestionBankSync,
} from '../../../api/src/features/tenant-content/public-banks.js';
import { closePool as closeApiPublicBankPool } from '../../../api/src/core/db.js';

interface PublicBankSyncCandidate {
  id: string;
  tenantId: string;
  createdBy: string | null;
  updatedBy: string | null;
}

interface PublicBankSyncWorkerResult {
  processed: number;
  synced: number;
  conflicts: number;
  failed: number;
  skipped: number;
}

function nowIso() {
  return new Date().toISOString();
}

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 || 'PUBLIC_BANK_SYNC_WORKER_ERROR')
    : 'PUBLIC_BANK_SYNC_WORKER_ERROR';
}

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

function publicBankFailureDedupeKey(candidate: PublicBankSyncCandidate, code: string) {
  return [
    'public-bank-sync-failed',
    candidate.id,
    crypto.createHash('sha256').update(code).digest('hex').slice(0, 16),
  ].join(':');
}

async function claimPublicBankSyncCandidates(limit: number, claimId: string) {
  const client = await pool.connect();
  try {
    await client.query('begin');
    const result = await client.query<PublicBankSyncCandidate>(
      `
      with candidates as (
        select a.id
        from public.tenant_question_bank_adoptions a
        join public.question_banks qb on qb.id = a.source_question_bank_id
        left join public.question_bank_grants g on g.id = a.grant_id
        where a.status in ('active', 'sync_pending')
          and (a.sync_status <> 'failed' or a.status = 'sync_pending')
          and a.grant_id is not null
          and a.target_question_bank_id is not null
          and a.target_entry_id is not null
          and a.target_collection_id is not null
          and qb.source_scope = 'platform'
          and qb.status = 'active'
          and (
            a.sync_status = 'pending'
            or a.status = 'sync_pending'
            or a.last_synced_at is null
            or qb.updated_at > coalesce(a.last_synced_at, '1970-01-01'::timestamptz)
            or coalesce(g.updated_at, '1970-01-01'::timestamptz) > coalesce(a.last_synced_at, '1970-01-01'::timestamptz)
            or exists (
              select 1
              from public.questions q
              where q.tenant_id = qb.tenant_id
                and q.question_bank_id = qb.id
                and q.status = 'published'
                and q.updated_at > coalesce(a.last_synced_at, '1970-01-01'::timestamptz)
            )
          )
          and (
            a.status <> 'sync_pending'
            or coalesce(nullif(a.metadata #>> '{publicBankSyncWorker,claimedAt}', '')::timestamptz, '1970-01-01'::timestamptz)
              <= now() - ($2::integer * interval '1 second')
          )
        order by coalesce(a.last_synced_at, '1970-01-01'::timestamptz) asc, a.updated_at asc
        limit $1
        for update of a skip locked
      )
      update public.tenant_question_bank_adoptions a
      set status = 'sync_pending',
          sync_status = 'pending',
          metadata = jsonb_set(
            coalesce(a.metadata, '{}'::jsonb),
            '{publicBankSyncWorker}',
            coalesce(a.metadata->'publicBankSyncWorker', '{}'::jsonb) || $3::jsonb,
            true
          ),
          updated_at = now()
      from candidates
      where a.id = candidates.id
      returning a.id,
                a.tenant_id as "tenantId",
                a.created_by as "createdBy",
                a.updated_by as "updatedBy"
      `,
      [
        limit,
        Math.max(60, config.publicBankSyncClaimStaleSeconds),
        JSON.stringify({
          claimId,
          workerId: config.publicBankSyncWorkerId,
          claimedAt: nowIso(),
        }),
      ],
    );
    await client.query('commit');
    return result.rows;
  } catch (error) {
    await client.query('rollback');
    throw error;
  } finally {
    client.release();
  }
}

async function markPublicBankSyncFailed(candidate: PublicBankSyncCandidate, error: unknown) {
  const details = {
    code: errorCode(error),
    message: truncate(errorMessage(error)),
    workerId: config.publicBankSyncWorkerId,
    failedAt: nowIso(),
  };
  const client = await pool.connect();
  try {
    await client.query('begin');
    await client.query(
      `
      update public.tenant_question_bank_adoptions
      set status = 'active',
          sync_status = 'failed',
          metadata = jsonb_set(
            coalesce(metadata, '{}'::jsonb),
            '{publicBankSyncWorker}',
            coalesce(metadata->'publicBankSyncWorker', '{}'::jsonb) || $3::jsonb,
            true
          ),
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        candidate.tenantId,
        candidate.id,
        JSON.stringify({
          lastStatus: 'failed',
          lastError: details,
          lastWorkerId: config.publicBankSyncWorkerId,
          lastFinishedAt: nowIso(),
        }),
      ],
    );

    const adoptionResult = await client.query<{
      source_question_bank_id: string;
      target_question_bank_id: string | null;
      target_entry_id: string | null;
      target_collection_id: string | null;
      source_question_bank_name: string | null;
      target_question_bank_name: string | null;
    }>(
      `
      select a.source_question_bank_id,
             a.target_question_bank_id,
             a.target_entry_id,
             a.target_collection_id,
             source_qb.name as source_question_bank_name,
             target_qb.name as target_question_bank_name
      from public.tenant_question_bank_adoptions a
      join public.question_banks source_qb
        on source_qb.id = a.source_question_bank_id
      left join public.question_banks target_qb
        on target_qb.id = a.target_question_bank_id
       and target_qb.tenant_id = a.tenant_id
      where a.tenant_id = $1
        and a.id = $2
      `,
      [candidate.tenantId, candidate.id],
    );
    const adoption = adoptionResult.rows[0] || null;
    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, null, 'content.public_question_bank.sync_worker_failed', 'tenant_question_bank_adoption', $2, $3::jsonb)
      `,
      [candidate.tenantId, candidate.id, JSON.stringify(details)],
    );

    if (adoption) {
      await client.query(
        `
        insert into public.tenant_content_notifications (
          tenant_id, notification_type, status, severity,
          adoption_id, source_question_bank_id,
          title, message, action_label, action_path,
          dedupe_key, metadata, created_by
        )
        values (
          $1, 'public_question_bank_sync_failed', 'unread', 'error',
          $2, $3,
          $4, $5, '查看同步状态', $6,
          $7, $8::jsonb, null
        )
        on conflict (tenant_id, notification_type, dedupe_key)
        where dedupe_key is not null
        do update set status = case
                        when public.tenant_content_notifications.status = 'dismissed' then public.tenant_content_notifications.status
                        else 'unread'
                      end,
                      severity = excluded.severity,
                      title = excluded.title,
                      message = excluded.message,
                      action_label = excluded.action_label,
                      action_path = excluded.action_path,
                      metadata = excluded.metadata,
                      resolved_at = null,
                      updated_at = now()
        `,
        [
          candidate.tenantId,
          candidate.id,
          adoption.source_question_bank_id,
          `${adoption.source_question_bank_name || '公共题库'} 同步失败`,
          `公共题库自动同步失败，错误码：${details.code}。请稍后重试或联系平台处理。`,
          `/tenant-admin/content?adoptionId=${candidate.id}&panel=public-banks`,
          publicBankFailureDedupeKey(candidate, details.code),
          JSON.stringify({
            adoptionId: candidate.id,
            sourceQuestionBankId: adoption.source_question_bank_id,
            sourceQuestionBankName: adoption.source_question_bank_name,
            targetQuestionBankId: adoption.target_question_bank_id,
            targetQuestionBankName: adoption.target_question_bank_name,
            targetEntryId: adoption.target_entry_id,
            targetCollectionId: adoption.target_collection_id,
            syncStatus: 'failed',
            errorCode: details.code,
            errorMessage: details.message,
            triggeredBy: 'worker',
            workerId: config.publicBankSyncWorkerId,
            failedAt: details.failedAt,
          }),
        ],
      );
    }
    await client.query('commit');
  } catch (failure) {
    await client.query('rollback');
    throw failure;
  } finally {
    client.release();
  }
}

export async function processPublicBankSyncBatch(limit = config.publicBankSyncBatchSize): Promise<PublicBankSyncWorkerResult> {
  const candidates = await claimPublicBankSyncCandidates(limit, crypto.randomUUID());
  const result: PublicBankSyncWorkerResult = {
    processed: candidates.length,
    synced: 0,
    conflicts: 0,
    failed: 0,
    skipped: 0,
  };

  for (const candidate of candidates) {
    try {
      const sync = await executePublicQuestionBankSync({
        tenantId: candidate.tenantId,
        adoptionId: candidate.id,
        actorUserId: candidate.updatedBy || candidate.createdBy || null,
        copyLimit: config.publicBankSyncCopyLimit,
        triggeredBy: 'worker',
        workerId: config.publicBankSyncWorkerId,
      });
      if (sync.sync.status === 'conflict') result.conflicts += 1;
      else if (sync.sync.counts.inserted || sync.sync.counts.updated || sync.sync.counts.skipped) result.synced += 1;
      else result.skipped += 1;
    } catch (error) {
      await markPublicBankSyncFailed(candidate, error);
      result.failed += 1;
    }
  }

  return result;
}

export async function closePublicBankSyncExecutorPool() {
  await closeApiPublicBankPool();
}
