Public/apps/api/prisma/wash-oa-ledger.ts

import { Prisma, PrismaClient } from '@prisma/client';
import * as fs from 'fs';

const prisma = new PrismaClient();
const DUMP = '/opt/import/prod/oa_fresh.json';

type Row = Record<string, unknown>;

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

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

function when(v: unknown) {
  const s = str(v);
  if (!s || s.startsWith('0000')) return null;
  const d = new Date(s.includes('T') ? s : s.replace(' ', 'T'));
  return Number.isNaN(d.getTime()) ? null : d;
}

function clip(v: unknown, n = 200) {
  const s = str(v);
  return s.length > n ? s.slice(0, n) : s;
}

function realPartyName(raw: unknown) {
  let name = str(raw).replace(/\s+/g, ' ');
  if (!name) return '';
  const alias: Record<string, string> = {
    '73047': '中国人民解放军73047部队保障部',
    '中国人民解放军 32272 部队': '中国人民解放军32272部队',
  };
  name = alias[name] || name;
  if (/^未指定/.test(name)) return '';
  if (/^\d+$/.test(name)) return '';
  if (name.startsWith('/') || name.endsWith('.pdf')) return '';
  if (name.length < 2) return '';
  return name;
}

function compactName(name: string) {
  return str(name)
    .replace(/[（）()\[\]【】\s\-—_.,，、]/g, '')
    .replace(/招标公告|采购项目|技术服务合同|合同/g, '')
    .replace(/检查院/g, '检察院');
}

function namesOverlap(a: string, b: string) {
  const na = compactName(a);
  const nb = compactName(b);
  if (!na || !nb) return false;
  if (na === nb) return true;
  const ka = groupKey(a);
  const kb = groupKey(b);
  if (ka && ka === kb && ka.length >= 3 && ka !== na && ka !== nb) return true;
  const shorter = na.length <= nb.length ? na : nb;
  const longer = na.length <= nb.length ? nb : na;
  return shorter.length >= 8 && longer.includes(shorter);
}

function groupKey(name: string) {
  const anchors = [
    '信息化教材管理服务平台',
    '教务管理系统国产化',
    '中船建模',
    '课程录播资源管理平台',
    '九江市国动办',
    '北京顺义',
    '天长市',
    '国防科技大学试验训练',
    '银川低空',
    '无锡联勤',
    '南京军代',
    '日常办公（江苏',
    '日常办公（南京',
    '数字人',
    '沉浸式体验区',
    '某演练',
    '某单位陈列室',
    'OA网络教学',
    '某部软件系统定制',
    '院感及互联互通',
    '财经管控',
    '智能控制系统',
    '某部门户网站',
  ];
  const hit = anchors.find((a) => name.includes(a));
  return hit || compactName(name) || name;
}

function mapBidStatus(row: Row) {
  const st = str(row.project_status);
  const win = str(row.winning_status);
  if (st === '中标' || (st === '' && win.includes('中标'))) return 'WON';
  if (st === '未中' || (st === '' && win.includes('未中'))) return 'LOST';
  if (st === '流标') return 'FAILED';
  if (st === '弃标' || win.includes('弃标')) return 'TERMINATED';
  return '';
}

function mapContractStatus(v: unknown) {
  const s = str(v);
  if (s === '已通过') return 'APPROVED';
  if (s === '审批中') return 'PENDING';
  return 'DRAFT';
}

function mapProjectStatus(v: unknown) {
  const s = str(v);
  if (s === '验收通过') return 'ACCEPTED';
  if (s === '已完成') return 'DONE';
  if (s === '待验收') return 'INTERNAL_ACCEPTED';
  if (s === '未开始') return 'NOT_STARTED';
  return 'ACTIVE';
}

const STATUS_RANK: Record<string, number> = {
  验收通过: 5,
  已完成: 4,
  待验收: 3,
  进行中: 2,
  未开始: 1,
};

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

function mapProjectType(v: unknown, projectName?: unknown) {
  const name = str(projectName);
  if (name.includes('三维')) return '三维';
  if (/(视频.{0,12}(制作|摄制|拍摄)|摄制|拍摄|摄影|宣传片|专题片|纪录片|微电影|影视制作)/.test(name)) return '摄制';
  const s = str(v);
  if (s.includes('三维')) return '三维';
  if (s.includes('摄制') || s.includes('视频')) return '摄制';
  if (s.includes('物资')) return '物资';
  if (s.includes('集成')) return '集成';
  return '软件';
}

function isRealTender(row: Row) {
  return /^TB-\d/.test(str(row.system_number)) && Boolean(str(row.project_name)) && !str(row.project_name).startsWith('/profile/');
}

function isGenericContractName(name: string) {
  return compactName(name) === '采购' || str(name) === '采购合同';
}

async function ensureParty(name: string, partyByName: Map<string, string>, remark: string) {
  const existing = partyByName.get(name);
  if (existing) return existing;
  const row = await prisma.businessParty.create({ data: { name, remark, level: 'B' } });
  partyByName.set(name, row.id);
  return row.id;
}

type MergedProject = {
  systemNumber: string;
  name: string;
  status: string;
  oaStatus: string;
  contractProdId: string;
  contractName: string;
  budget: number;
  createdAt: Date | null;
};

function mergeProjects(rows: Row[]): MergedProject[] {
  const groups = new Map<string, Row[]>();
  for (const p of rows) {
    const name = str(p.project_name);
    if (!name || !str(p.system_number)) continue;
    const key = groupKey(name);
    if (!groups.has(key)) groups.set(key, []);
    groups.get(key)!.push(p);
  }
  const out: MergedProject[] = [];
  for (const items of groups.values()) {
    const withContract = items.find((p) => p.contract_id != null && str(p.contract_id));
    const progressed = [...items].sort((a, b) => (STATUS_RANK[str(b.project_status)] || 0) - (STATUS_RANK[str(a.project_status)] || 0))[0];
    const pick = withContract || progressed;
    out.push({
      systemNumber: str(pick.system_number),
      name: str(progressed.project_name),
      status: mapProjectStatus(progressed.project_status),
      oaStatus: str(progressed.project_status),
      contractProdId: withContract ? str(withContract.contract_id) : '',
      contractName: str((withContract || pick).contract_name),
      budget: Math.max(...items.map((p) => num(p.project_budget))),
      createdAt: when(pick.create_time),
    });
  }
  return out;
}

async function main() {
  const raw = JSON.parse(fs.readFileSync(DUMP, 'utf8')) as Record<string, Row[]>;
  const admin = await prisma.user.findFirst({ where: { username: 'admin' } });
  if (!admin) throw new Error('本地没有 admin');
  const users = await prisma.user.findMany({ select: { id: true, displayName: true, username: true } });
  const userByName = new Map(users.map((u) => [u.displayName, u.id]));
  const xia = userByName.get('夏雨晴');
  const entity =
    (await prisma.legalEntity.findFirst({ where: { tenantId: '1', isDefault: true } })) ||
    (await prisma.legalEntity.findFirst({ where: { tenantId: '1' } }));
  if (!entity) throw new Error('本地没有主体');

  const stale = await prisma.contract.findMany({
    where: { contractNo: { startsWith: 'HT-2026-00' } },
    include: {
      _count: {
        select: {
          projects: true,
          invoices: true,
          bonds: true,
          sealRequests: true,
          milestones: true,
          reviewers: true,
          actionLogs: true,
        },
      },
    },
  });
  const staleIds = stale.filter((c) => Object.values(c._count).every((n) => n === 0)).map((c) => c.id);
  if (staleIds.length) await prisma.contract.deleteMany({ where: { id: { in: staleIds } } });

  const tenders = (raw.oa_tender || []).filter(isRealTender);
  const contracts = raw.oa_contract || [];
  const mergedProjects = mergeProjects(raw.project_list || []);

  const parties = await prisma.businessParty.findMany();
  const partyByName = new Map(parties.map((p) => [p.name, p.id]));

  for (const t of tenders) {
    const n = realPartyName(t.tender_dan_wei);
    if (n) await ensureParty(n, partyByName, 'OA 招标单位');
  }
  for (const c of contracts) {
    const n = realPartyName(c.party_a_name);
    if (n && n !== '江苏风影随行科技有限公司') await ensureParty(n, partyByName, 'OA 合同甲方');
  }

  const bids = await prisma.bidCase.findMany();
  const bidByNo = new Map(bids.map((b) => [b.bidNo, b]));
  let bidsUpdated = 0;
  let bidsCreated = 0;
  const usedNos = new Set(bids.map((b) => b.bidNo));

  function findExistingBid(t: Row) {
    const sys = str(t.system_number);
    const official = officialNo(t.project_number);
    const suffix = sys.split('-').pop() || '';
    const name = str(t.project_name);
    const all = [...bidByNo.values()];
    return (
      bidByNo.get(sys) ||
      all.find((b) => b.externalNo === sys) ||
      all.find((b) => b.bidNo === `${official}-${suffix}` || (official && b.bidNo.endsWith(`-${suffix}`) && b.name === name)) ||
      (official && all.filter((b) => b.bidNo === official || b.externalNo === official).length === 1
        ? all.find((b) => b.bidNo === official || b.externalNo === official)
        : undefined)
    );
  }

  const testNos = ['SP-TEST-1788068192', 'SP-FIX-1788068273'];
  const tests = bids.filter((b) => testNos.includes(b.bidNo) || testNos.includes(b.externalNo || ''));
  if (tests.length) {
    const ids = tests.map((b) => b.id);
    await prisma.todoTask.deleteMany({ where: { bizId: { in: ids } } });
    await prisma.approvalTask.deleteMany({ where: { bizId: { in: ids } } });
    await prisma.bidCase.deleteMany({ where: { id: { in: ids } } });
  }

  for (const t of tenders) {
    const sys = str(t.system_number);
    const official = officialNo(t.project_number);
    const name = clip(t.project_name, 200);
    const partyName = realPartyName(t.tender_dan_wei);
    const status = mapBidStatus(t) || 'PENDING';
    const existing = findExistingBid(t);
    const resultBits = [
      t.winning_unit ? `中标单位：${t.winning_unit}` : '',
      num(t.winning_amount) ? `中标金额：${num(t.winning_amount)}` : '',
      str(t.opening_remark),
      str(t.bid_summary),
    ].filter(Boolean);

    if (existing) {
      let bidNo = official || sys;
      if (usedNos.has(bidNo) && bidNo !== existing.bidNo) bidNo = `${official || sys}-${sys.slice(-3)}`;
      usedNos.delete(existing.bidNo);
      usedNos.add(bidNo);
      await prisma.bidCase.update({
        where: { id: existing.id },
        data: {
          bidNo,
          name,
          externalNo: official || null,
          status,
          sourceType: 'FLOW',
          partyId: partyName ? partyByName.get(partyName) : existing.partyId,
          tenderMethod: clip(t.tender_method, 50) || existing.tenderMethod,
          openPlace: clip(t.tender_address, 200) || existing.openPlace,
          priceCap: t.price_limit == null ? existing.priceCap : String(t.price_limit),
          quoteAmount: t.bid_quotation == null ? existing.quoteAmount : String(t.bid_quotation),
          resultNote: resultBits.join('；') || existing.resultNote,
          openedResultAt: when(t.winning_date) || when(t.bid_open_date) || existing.openedResultAt,
        },
      });
      existing.bidNo = bidNo;
      existing.name = name;
      existing.status = status;
      existing.externalNo = official || existing.externalNo;
      bidByNo.set(bidNo, existing);
      bidByNo.set(sys, existing);
      bidsUpdated += 1;
    } else {
      let bidNo = official || sys;
      if (usedNos.has(bidNo)) bidNo = `${sys}`;
      if (usedNos.has(bidNo)) bidNo = `${sys}-${Date.now().toString().slice(-4)}`;
      usedNos.add(bidNo);
      const row = await prisma.bidCase.create({
        data: {
          bidNo,
          name,
          externalNo: official || undefined,
          projectType: mapProjectType(t.project_type, t.project_name),
          legalEntityId: entity.id,
          partyId: partyName ? partyByName.get(partyName) : undefined,
          tenderMethod: clip(t.tender_method, 50) || undefined,
          applyEndAt: when(t.file_apply_deadline) || undefined,
          openAt: when(t.bid_open_date) || undefined,
          openPlace: clip(t.tender_address, 200) || undefined,
          securityLevel: str(t.security_level) || 'INTERNAL',
          projectNature: clip(t.project_nature, 50) || undefined,
          contactName: clip(t.bid_contact_person, 64) || undefined,
          contactPhone: clip(t.bid_contact_phone, 32) || undefined,
          priceCap: t.price_limit == null ? undefined : String(t.price_limit),
          quoteAmount: t.bid_quotation == null ? undefined : String(t.bid_quotation),
          resultNote: resultBits.join('；') || undefined,
          openedResultAt: when(t.winning_date) || when(t.bid_open_date) || undefined,
          status,
          createdById: admin.id,
          createdAt: when(t.create_time) || undefined,
        },
      });
      bidByNo.set(bidNo, row);
      bidByNo.set(sys, row);
      bidsCreated += 1;
    }
  }

  const localContracts = await prisma.contract.findMany();
  const contractByNo = new Map(localContracts.map((c) => [c.contractNo, c]));
  const contractByProd = new Map<string, string>();
  let contractsUpdated = 0;
  let contractsCreated = 0;

  for (const c of contracts) {
    const contractNo = str(c.contract_no);
    if (!contractNo) continue;
    const tb = contractNo.match(/TB-\d+-\d+/);
    const bid =
      (tb && (bidByNo.get(tb[0]) || [...bidByNo.values()].find((b) => b.bidNo === tb[0] || b.bidNo.startsWith(tb[0])))) ||
      [...bidByNo.values()].find((b) => namesOverlap(b.name, str(c.contract_name)));
    if (str(c.approval_status) === 'pending_submit' && !c.sign_date && !num(c.amount)) {
      continue;
    }
    const partyA = realPartyName(c.party_a_name);
    const partyB = str(c.party_b_name);
    const remark = [
      str(c.payment_method) ? `付款：${c.payment_method}` : '',
      str(c.contract_lb) ? `类别：${c.contract_lb}` : '',
      str(c.remark),
    ]
      .filter(Boolean)
      .join(' / ');
    const handlerId = Number(c.create_by) === 107 ? xia : bid?.createdById || xia || admin.id;
    const data = {
      name: clip(c.contract_name, 200),
      bidCaseId: bid?.id,
      legalEntityId: entity.id,
      amount: new Prisma.Decimal(num(c.amount)),
      status: mapContractStatus(c.approval_status),
      partyA: partyA || undefined,
      partyB: partyB || undefined,
      remark: remark || undefined,
      signedAt: when(c.sign_date) || undefined,
      createdById: handlerId,
    };
    const existing =
      contractByNo.get(contractNo) ||
      localContracts.find(
        (row) => row.name === clip(c.contract_name, 200) && /^HT-2026-00\d+$/.test(row.contractNo),
      );
    if (existing) {
      const nextNo = contractByNo.has(contractNo) && existing.contractNo !== contractNo ? existing.contractNo : contractNo;
      await prisma.contract.update({
        where: { id: existing.id },
        data: { ...data, contractNo: nextNo },
      });
      existing.contractNo = nextNo;
      contractByNo.set(nextNo, existing);
      contractByProd.set(String(c.id), existing.id);
      contractsUpdated += 1;
    } else {
      const row = await prisma.contract.create({
        data: {
          contractNo,
          createdAt: when(c.create_time) || undefined,
          ...data,
        },
      });
      contractByNo.set(contractNo, row);
      contractByProd.set(String(c.id), row.id);
      contractsCreated += 1;
    }
  }

  const localProjects = await prisma.project.findMany({ include: { budgets: true } });
  const usedProjectNos = new Set(localProjects.map((p) => p.projectNo));
  let projectsUpdated = 0;
  let projectsCreated = 0;
  const projectMoves: { name: string; from: string; to: string; no: string }[] = [];

  function findLocalProject(p: MergedProject) {
    return (
      localProjects.find((row) => row.projectNo === p.systemNumber) ||
      localProjects.find((row) => namesOverlap(row.name, p.name) || groupKey(row.name) === groupKey(p.name))
    );
  }

  function findContractId(p: MergedProject) {
    if (p.contractProdId && contractByProd.has(p.contractProdId)) return contractByProd.get(p.contractProdId);
    if (p.contractName) {
      const exact = [...contractByNo.values()].find((c) => c.name === p.contractName);
      if (exact) return exact.id;
    }
    const fuzzy = [...contractByNo.values()].find(
      (c) => !isGenericContractName(c.name) && namesOverlap(c.name, p.name),
    );
    return fuzzy?.id;
  }

  for (const p of mergedProjects) {
    const existing = findLocalProject(p);
    const contractId = findContractId(p);
    if (existing) {
      let projectNo = p.systemNumber;
      if (usedProjectNos.has(projectNo) && projectNo !== existing.projectNo) {
        projectNo = existing.projectNo;
      }
      usedProjectNos.delete(existing.projectNo);
      usedProjectNos.add(projectNo);
      if (existing.status !== p.status || existing.projectNo !== projectNo) {
        projectMoves.push({ name: p.name.slice(0, 24), from: `${existing.projectNo}/${existing.status}`, to: `${projectNo}/${p.status}`, no: p.oaStatus });
      }
      await prisma.project.update({
        where: { id: existing.id },
        data: {
          projectNo,
          name: clip(p.name, 200),
          contractId,
          status: p.status,
        },
      });
      if (p.budget > 0) {
        await prisma.projectBudget.upsert({
          where: { projectId_category: { projectId: existing.id, category: 'PURCHASE' } },
          update: { amount: new Prisma.Decimal(p.budget) },
          create: { projectId: existing.id, category: 'PURCHASE', amount: new Prisma.Decimal(p.budget) },
        });
      }
      existing.projectNo = projectNo;
      existing.name = p.name;
      existing.status = p.status;
      projectsUpdated += 1;
    } else {
      if (!contractId && /招标公告/.test(p.name) && p.status === 'NOT_STARTED') {
        continue;
      }
      let projectNo = p.systemNumber;
      if (usedProjectNos.has(projectNo)) projectNo = `${p.systemNumber}-2`;
      usedProjectNos.add(projectNo);
      const row = await prisma.project.create({
        data: {
          projectNo,
          name: clip(p.name, 200),
          contractId,
          status: p.status,
          createdAt: p.createdAt || undefined,
        },
      });
      if (p.budget > 0) {
        await prisma.projectBudget.create({
          data: { projectId: row.id, category: 'PURCHASE', amount: new Prisma.Decimal(p.budget) },
        });
      }
      localProjects.push({ ...row, budgets: [] });
      projectsCreated += 1;
    }
  }

  const caijing = await prisma.bidCase.findMany({
    where: { name: { contains: '财经管控' } },
    include: { _count: { select: { contracts: true, expenses: true, bonds: true, assignees: true, versions: true } } },
  });
  if (caijing.length) {
    const won = caijing.find((b) => /196/.test(b.bidNo) || b._count.contracts > 0);
    const extras = caijing.filter((b) => b.id !== won?.id);
    const keepFailed = extras.find((b) => Object.values(b._count).some((n) => n > 0)) || extras[0];
    if (keepFailed) {
      await prisma.bidCase.update({
        where: { id: keepFailed.id },
        data: { status: 'FAILED', bidNo: keepFailed.bidNo.includes('182') ? keepFailed.bidNo : '2026-JLDJAE-W3006-182' },
      });
    }
    if (won && won.status !== 'WON') {
      await prisma.bidCase.update({ where: { id: won.id }, data: { status: 'WON' } });
    }
    const drop = extras.filter((b) => b.id !== keepFailed?.id && Object.values(b._count).every((n) => n === 0)).map((b) => b.id);
    if (drop.length) await prisma.bidCase.deleteMany({ where: { id: { in: drop } } });
  }

  const bidCounts = await prisma.bidCase.groupBy({ by: ['status'], _count: true });
  const projectCounts = await prisma.project.groupBy({ by: ['status'], _count: true });
  const linked = await prisma.project.count({ where: { contractId: { not: null } } });
  console.log(
    JSON.stringify(
      {
        tenders: tenders.length,
        bidsUpdated,
        bidsCreated,
        contractsUpdated,
        contractsCreated,
        projectsMerged: mergedProjects.length,
        projectsUpdated,
        projectsCreated,
        projectMoves,
        bidCounts,
        projectCounts,
        projectsWithContract: linked,
      },
      null,
      2,
    ),
  );
}

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