Public/apps/api/prisma/backfill-project-ledger.ts

import { Prisma, PrismaClient } from '@prisma/client';
import { readFileSync, existsSync } from 'fs';
import { join } from 'path';
import { normalizeProjectType } from '../src/project/project-workflow';

const prisma = new PrismaClient();

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

function num(v: unknown) {
  const n = Number(v);
  return Number.isFinite(n) ? n : 0;
}

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

function loadCore(): { project_list?: Record<string, unknown>[]; reimbursement?: Record<string, unknown>[] } {
  const candidates = [
    join('/opt/import/prod/oa_core.json'),
    join(process.cwd(), '../../import/prod/oa_core.json'),
    join(process.cwd(), 'import/prod/oa_core.json'),
  ];
  for (const p of candidates) {
    if (existsSync(p)) return JSON.parse(readFileSync(p, 'utf8'));
  }
  return {};
}

async function main() {
  const core = loadCore();
  const oldProjects = core.project_list || [];
  const reimburses = core.reimbursement || [];

  const projects = await prisma.project.findMany({
    include: { contract: { include: { bidCase: { select: { projectType: true, name: true } } } }, expenses: { select: { id: true, projectId: true } } },
  });
  const expenses = await prisma.expenseClaim.findMany({
    where: { tenantId: '1' },
    select: { id: true, projectId: true, purpose: true, remark: true },
  });

  let typed = 0;
  let amounted = 0;
  let linked = 0;

  for (const p of projects) {
    const old = oldProjects.find((o) => {
      const code = str(o.project_code || o.system_number);
      const name = str(o.project_name);
      return (code && (code === p.projectNo || p.projectNo.endsWith(code))) ||
        (name && (name === p.name || compact(name) === compact(p.name)));
    });
    const nextType =
      normalizeProjectType(p.projectType) ||
      normalizeProjectType(p.contract?.bidCase?.projectType) ||
      normalizeProjectType(str(old?.project_category || old?.project_type));
    const nextAmount =
      num(p.amount) ||
      num(p.contract?.amount) ||
      num(old?.project_budget);
    const data: Prisma.ProjectUpdateInput = {};
    if (nextType && nextType !== p.projectType) {
      data.projectType = nextType;
      typed += 1;
    }
    if (nextAmount > 0 && num(p.amount) <= 0) {
      data.amount = nextAmount;
      amounted += 1;
    }
    if (Object.keys(data).length) {
      await prisma.project.update({ where: { id: p.id }, data });
    }
  }

  const refreshed = await prisma.project.findMany({ select: { id: true, name: true, projectNo: true } });
  for (const e of expenses) {
    if (e.projectId) continue;
    const hit = refreshed.find((p) => {
      const blob = `${e.purpose || ''} ${e.remark || ''}`;
      return blob.includes(p.projectNo) || blob.includes(p.name) || (p.name.length >= 8 && blob.includes(p.name.slice(0, 12)));
    });
    const old = reimburses.find((r) => str(r.project_name) && hit && compact(str(r.project_name)) === compact(hit.name));
    const byName = refreshed.find((p) => {
      const r = reimburses.find((x) => str(x.id) && String(e.remark || '').includes(String(x.id)));
      return r ? compact(str(r.project_name)) === compact(p.name) : false;
    });
    const target = hit || byName;
    if (!target) continue;
    await prisma.expenseClaim.update({ where: { id: e.id }, data: { projectId: target.id, costType: 'PROJECT' } });
    linked += 1;
  }

  // old reimbursements already imported with project names
  for (const r of reimburses) {
    const name = str(r.project_name);
    if (!name) continue;
    const project = refreshed.find((p) => p.name === name || compact(p.name) === compact(name));
    if (!project) continue;
    const updated = await prisma.expenseClaim.updateMany({
      where: {
        tenantId: '1',
        projectId: null,
        OR: [{ purpose: { contains: name.slice(0, 16) } }, { remark: { contains: name.slice(0, 16) } }],
      },
      data: { projectId: project.id, costType: 'PROJECT' },
    });
    linked += updated.count;
  }

  console.log(`project ledger backfill: typed=${typed} amounted=${amounted} expensesLinked=${linked}`);
}

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