Public/apps/api/prisma/sync-oa-attachments.ts

import { PrismaClient } from '@prisma/client';
import { createHash, randomUUID } from 'crypto';
import * as fs from 'fs';
import * as path from 'path';
import { parseOaJson } from './oa-json';

const prisma = new PrismaClient();
const CORE = '/opt/import/prod/oa_core.json';
const FLOW = '/opt/import/prod/oa_flow_finance.json';
const BID_FILES = '/opt/import/prod/oa_bid_files.json';
const HR = '/opt/import/prod/oa_hr.json';
const OA_FILES = '/opt/import/prod/oa-files';
const UPLOAD = path.join(process.cwd(), 'uploads');

type Row = Record<string, unknown>;

const MIME: Record<string, string> = {
  pdf: 'application/pdf',
  jpg: 'image/jpeg',
  jpeg: 'image/jpeg',
  png: 'image/png',
  gif: 'image/gif',
  webp: 'image/webp',
  doc: 'application/msword',
  docx: 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
  xls: 'application/vnd.ms-excel',
  xlsx: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  zip: 'application/zip',
  rar: 'application/x-rar-compressed',
};

const NAME_ALIAS: Record<string, string> = { 张靖妍: '张婧妍' };
const SKIP_DOC_ROLE = new Set(['draft', 'final', 'review1']);

function str(v: unknown) {
  if (v == null) return '';
  return String(v).trim();
}

function officialNo(raw: unknown) {
  const n = str(raw).replace(/[)）]+$/, '').trim();
  if (!n || n === '无' || n === '-' || n === '—') return '';
  return n;
}

function num(v: unknown) {
  if (v == null || v === '') return 0;
  const n = Number(String(v).replace(/,/g, ''));
  return Number.isFinite(n) ? n : 0;
}

function profilePaths(v: unknown) {
  const out: string[] = [];
  if (!v) return out;
  for (const chunk of str(v).split(/[,;]/)) {
    const piece = chunk.trim();
    const first = piece.split('|')[0].trim();
    const idx = first.indexOf('/profile/upload/');
    const p = idx >= 0 ? first.slice(idx) : first;
    if (p.startsWith('/profile/upload/')) out.push(p);
  }
  return out;
}

function relOf(p: string) {
  return p.replace(/^\/profile\/upload\//, '');
}

function displayName(p: string) {
  const pipe = str(p).split('|')[1];
  if (pipe && !pipe.includes('/')) return path.basename(pipe);
  return path.basename(relOf(p.split('|')[0]));
}

function mimeOf(name: string) {
  const ext = name.split('.').pop()?.toLowerCase() || 'bin';
  return MIME[ext] || 'application/octet-stream';
}

function prettyName(name: string) {
  try {
    return decodeURIComponent(name);
  } catch {
    return name;
  }
}

async function attach(
  bizType: string,
  bizId: string,
  urls: unknown,
  uploadedById: string,
  seen: Set<string>,
) {
  let n = 0;
  let missing = 0;
  for (const raw of profilePaths(urls)) {
    const rel = relOf(raw);
    const src = path.join(OA_FILES, rel);
    if (!fs.existsSync(src)) {
      console.warn(`[missing-source] ${bizType} ${rel}`);
      missing += 1;
      continue;
    }
    const buf = fs.readFileSync(src);
    const hash = createHash('sha1').update(buf).digest('hex').slice(0, 16);
    const key = `${bizId}:${hash}`;
    if (seen.has(key)) continue;
    seen.add(key);
    const id = randomUUID();
    const ext = (path.extname(rel).replace('.', '') || 'bin').replace(/[^a-zA-Z0-9]/g, '').slice(0, 8);
    const storageKey = `OA/${bizType}/${bizId}/${id}.${ext || 'bin'}`;
    const dest = path.join(UPLOAD, storageKey);
    fs.mkdirSync(path.dirname(dest), { recursive: true });
    fs.copyFileSync(src, dest);
    const name = prettyName(displayName(raw) || path.basename(rel));
    await prisma.fileAsset.create({
      data: {
        id,
        tenantId: '1',
        bizType,
        bizId,
        fileName: name.slice(0, 180),
        mimeType: mimeOf(name),
        size: buf.length,
        storageKey,
        uploadedById,
      },
    });
    n += 1;
  }
  return { n, missing };
}

async function main() {
  const core = parseOaJson<Record<string, Row[]>>(CORE);
  const flow = parseOaJson<Record<string, Row[]>>(FLOW);
  const docFiles = JSON.parse(fs.readFileSync(BID_FILES, 'utf8')) as Row[];
  const hr = JSON.parse(fs.readFileSync(HR, 'utf8')) as { users: { id: number; userName: string; nickName: string }[] };
  const admin = await prisma.user.findFirst({
    where: { OR: [{ username: 'admin' }, { displayName: '系统管理员' }] },
    orderBy: { createdAt: 'asc' },
  });
  if (!admin) throw new Error('没有可用于迁移附件的系统管理员账号');
  fs.mkdirSync(UPLOAD, { recursive: true });

  const users = await prisma.user.findMany();
  const byUsername = new Map(users.map((u) => [u.username, u]));
  const byName = new Map(users.map((u) => [u.displayName, u]));
  for (const [a, b] of Object.entries(NAME_ALIAS)) {
    const u = byName.get(b);
    if (u) byName.set(a, u);
  }
  const oaUser = new Map(hr.users.map((u) => [String(u.id), u.userName]));
  const userFromOaId = (id: unknown) => {
    const uname = oaUser.get(str(id));
    return uname ? byUsername.get(uname) : undefined;
  };
  const userFromName = (name: unknown) => {
    const n = NAME_ALIAS[str(name)] || str(name);
    return byName.get(n);
  };

  const existing = await prisma.fileAsset.findMany({ select: { bizId: true, storageKey: true, size: true, fileName: true } });
  const seen = new Set<string>();
  for (const f of existing) {
    const src = path.join(process.cwd(), 'uploads', f.storageKey);
    if (!fs.existsSync(src)) continue;
    const hash = createHash('sha1').update(fs.readFileSync(src)).digest('hex').slice(0, 16);
    seen.add(`${f.bizId}:${hash}`);
  }

  const bids = await prisma.bidCase.findMany();
  const findBids = (nos: string[], name?: string) => {
    const wanted = new Set(nos.filter(Boolean));
    const hits = bids.filter(
      (b) => wanted.has(b.bidNo) || (b.externalNo && wanted.has(b.externalNo)),
    );
    if (hits.length) return hits;
    if (name) return bids.filter((b) => b.name === name);
    return [];
  };

  const stats = {
    bidAnnounce: 0,
    bidFile: 0,
    bidProof: 0,
    contracts: 0,
    expenses: 0,
    projects: 0,
    purchases: 0,
    missing: 0,
    unmatchedBids: 0,
  };

  for (const t of core.oa_tender || []) {
    const localHits = findBids(
      [officialNo(t.project_number), str(t.system_number)],
      str(t.project_name),
    );
    if (!localHits.length) continue;
    const who = userFromOaId(t.create_by) || userFromName(t.bid_prepare_person) || admin;
    for (const local of localHits) {
      stats.bidAnnounce += (await attach('BID_ANNOUNCE', local.id, t.announcement_file, who.id, seen)).n;
      const extra = await attach('BID_FILE', local.id, t.attachment, who.id, seen);
      stats.bidFile += extra.n;
      stats.missing += extra.missing;
      stats.bidProof += (await attach('BID_PROOF', local.id, t.submit_proof_file, who.id, seen)).n;
    }
  }

  for (const p of flow.project_preinvest_apply || []) {
    const nos = [str(p.apply_no), officialNo(p.project_number)];
    const localHits = findBids(nos, str(p.project_name));
    if (!localHits.length) {
      if (profilePaths(p.attachment).length || profilePaths(p.business_attachment).length) stats.unmatchedBids += 1;
      continue;
    }
    const who = userFromName(p.applicant_name) || userFromName(p.business_handler_name) || admin;
    for (const local of localHits) {
      const a = await attach('BID_ANNOUNCE', local.id, p.attachment, who.id, seen);
      const b = await attach('BID_FILE', local.id, p.business_attachment, who.id, seen);
      const c = await attach('BID_PROOF', local.id, p.material_decision_proof, who.id, seen);
      const d = await attach('BID_FILE', local.id, p.business_prep_attachment, who.id, seen);
      stats.bidAnnounce += a.n;
      stats.bidFile += b.n + d.n;
      stats.bidProof += c.n;
      stats.missing += a.missing + b.missing + c.missing + d.missing;
    }
  }

  const taskById = new Map((flow.bid_document_task || []).map((t) => [String(t.id), t]));
  for (const f of docFiles) {
    if (SKIP_DOC_ROLE.has(str(f.file_role))) continue;
    const task = taskById.get(str(f.task_id));
    const nos = task
      ? [str(task.task_no), officialNo(task.project_number), str(task.preinvest_apply_no)]
      : [];
    const localHits = findBids(nos, str(task?.project_name));
    if (!localHits.length) {
      stats.unmatchedBids += 1;
      continue;
    }
    const who = userFromName(f.uploaded_by_name) || userFromName(task?.dispatcher_name) || admin;
    const role = str(f.file_role);
    const from = str(f.source_preinvest_field);
    const bizType =
      role === 'submit_proof' || from === 'material_decision_proof'
        ? 'BID_PROOF'
        : from === 'attachment'
          ? 'BID_ANNOUNCE'
          : 'BID_FILE';
    for (const local of localHits) {
      const r = await attach(bizType, local.id, f.file_url, who.id, seen);
      if (bizType === 'BID_ANNOUNCE') stats.bidAnnounce += r.n;
      else if (bizType === 'BID_PROOF') stats.bidProof += r.n;
      else stats.bidFile += r.n;
      stats.missing += r.missing;
    }
  }

  const contracts = await prisma.contract.findMany();
  const contractByName = new Map(contracts.map((c) => [c.name, c]));
  for (const c of core.oa_contract || []) {
    const local = contractByName.get(str(c.contract_name));
    if (!local) continue;
    const who = userFromOaId(c.create_by) || userFromOaId(c.creator_id) || admin;
    stats.contracts += (await attach('CONTRACT', local.id, c.contract_original_url, who.id, seen)).n;
    stats.contracts += (await attach('CONTRACT_ACCEPT', local.id, c.acceptance_url, who.id, seen)).n;
    stats.contracts += (await attach('CONTRACT_AWARD', local.id, c.award_notice_url, who.id, seen)).n;
  }

  const expenses = await prisma.expenseClaim.findMany();
  for (const r of flow.reimbursement || core.reimbursement || []) {
    const amount = num(r.total_amount);
    const name = str(r.employee_name);
    const claimNo = str(r.reimburse_no);
    const oaId = str(r.id);
    if (!oaId) continue;
    let hits = claimNo ? expenses.filter((e) => e.claimNo === claimNo) : [];
    if (!hits.length) {
      hits = expenses.filter((e) => Math.abs(Number(e.amount) - amount) < 0.009 && name && (e.remark || '').includes(name));
    }
    const local = hits[0];
    if (!local) continue;
    const who = userFromName(name) || users.find((u) => u.id === local.applicantId) || admin;
    const items = (flow.reimbursement_item || core.reimbursement_item || []).filter(
      (i) => str(i.reimbursement_id) && str(i.reimbursement_id) === oaId,
    );
    for (const item of items) {
      const x = await attach('EXPENSE', local.id, item.url, who.id, seen);
      stats.expenses += x.n;
      stats.missing += x.missing;
    }
    const x = await attach('EXPENSE', local.id, r.attachment_url, who.id, seen);
    stats.expenses += x.n;
    stats.missing += x.missing;
  }

  const projects = await prisma.project.findMany();
  const projectByName = new Map(projects.map((p) => [p.name, p]));
  for (const p of core.project_list || []) {
    const local =
      projects.find((x) => x.projectNo === str(p.system_number)) || projectByName.get(str(p.project_name));
    if (!local) continue;
    stats.projects += (await attach('PROJECT', local.id, p.bzj_attachment, admin.id, seen)).n;
  }

  const purchases = await prisma.purchaseRequest.findMany();
  for (const r of core.oa_purchasing || []) {
    const titleBits = [str(r.contract_no), str(r.contract_name)].filter(Boolean);
    const local = purchases.find((p) => titleBits.some((b) => p.title.includes(b)));
    if (!local) continue;
    const who = userFromOaId(r.create_by) || userFromOaId(r.creator_id) || admin;
    stats.purchases += (await attach('PURCHASE', local.id, r.contract_original_url, who.id, seen)).n;
    stats.purchases += (await attach('PURCHASE_ACCEPT', local.id, r.acceptance_url, who.id, seen)).n;
  }

  const fileCount = await prisma.fileAsset.count();
  const byType = await prisma.fileAsset.groupBy({ by: ['bizType'], _count: true });
  console.log(JSON.stringify({ added: stats, fileAssetTotal: fileCount, byType }, null, 2));
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());