competition-website/scripts/diagnose_upload_download.cjs

'use strict';

/**
 * 生产环境 上传/下载 链路诊断脚本
 * ---------------------------------------------------------------------------
 * 用途：在生产后端目录下运行，回答三个问题：
 *   1) app/public/{works,answers,materials,tmp} 目录是否存在、是否可写？
 *   2) 代码里「上传存盘路径」与「下载读取路径」是否真的指向同一文件？（路径自洽性）
 *   3) 数据库里已有的 submission 记录，其 file_path 是否对应磁盘上真实存在的文件？
 *
 * 运行：
 *   cd <生产 backend 目录>
 *   node scripts/diagnose_upload_download.cjs
 *   # 如需抽查数据库（可选）：
 *   DB_HOST=127.0.0.1 DB_USER=root DB_PASS='<本机 DB 口令>' DB_NAME=competition_db \
 *     node scripts/diagnose_upload_download.cjs
 *
 * 不依赖 Egg 启动，纯 Node + fs 即可；DB 部分为可选（拿不到模块/连接失败则跳过）。
 */

const fs = require('fs');
const path = require('path');

// ---------- 1. 定位 backend 根目录（app/public 的上一级） ----------
function resolveBackendRoot() {
  // 脚本放在 backend/scripts/ 下时，根目录是 scripts 的上一级
  let here = __dirname;
  for (let i = 0; i < 4; i++) {
    if (fs.existsSync(path.join(here, 'app', 'public'))) return here;
    const parent = path.dirname(here);
    if (parent === here) break;
    here = parent;
  }
  // 兜底：以当前工作目录为根
  return process.cwd();
}

const root = resolveBackendRoot();
const publicDir = path.join(root, 'app', 'public');

console.log('==================================================');
console.log(' 上传/下载 链路诊断');
console.log('==================================================');
console.log('backend 根目录 :', root);
console.log('app/public    :', publicDir);
console.log('app/public 存在:', fs.existsSync(publicDir));
console.log('');

// ---------- 2. 检查各业务目录的存在性与可写性 ----------
const subDirs = ['works', 'answers', 'materials', 'tmp'];
const groups = ['zhongzhi', 'gaozhi', 'benke', 'jiaoshi'];

function checkDir(p) {
  const exists = fs.existsSync(p);
  let type = exists ? (fs.statSync(p).isDirectory() ? '目录' : '非目录!') : '不存在';
  let writable = false;
  if (exists) {
    try {
      const probe = path.join(p, '.write_probe_' + Date.now());
      fs.writeFileSync(probe, 'x');
      fs.unlinkSync(probe);
      writable = true;
    } catch (e) {
      writable = false;
    }
  }
  return { exists, type, writable };
}

console.log('--- 2.1 业务目录存在性 / 可写性 ---');
const dirReport = {};
for (const d of subDirs) {
  const full = path.join(publicDir, d);
  const r = checkDir(full);
  dirReport[d] = r;
  console.log(
    `  app/public/${d.padEnd(9)} : ${r.type.padEnd(8)}  可写=${r.writable}`
  );
  if (d === 'works' || d === 'materials') {
    for (const g of groups) {
      const gp = path.join(full, g);
      const gr = checkDir(gp);
      console.log(
        `    └─ ${g.padEnd(9)} : ${gr.type.padEnd(8)}  可写=${gr.writable}`
      );
    }
  }
}
console.log('');

// ---------- 3. 路径自洽性模拟（与源码逻辑一致） ----------
console.log('--- 3. 路径自洽性（模拟一次上传→下载应指向同一文件）---');
function simulate(group, safeName) {
  // 上传存盘：service.upload.saveWorkFile / admin.uploadMaterial / exam.uploadWord
  const savedAbs = path.join(publicDir, 'works', group, safeName); // 例：works/benke/...
  // 下载读取：admin.downloadSubmissionFile -> path.join(app.baseDir,'app/public',submission.file_path)
  const dbRelative = path.join('works', group, safeName).replace(/\\/g, '/');
  const readAbs = path.join(publicDir, dbRelative);
  const ok = path.resolve(savedAbs) === path.resolve(readAbs);
  console.log(`  ${group}/${safeName}`);
  console.log(`    上传落盘路径 : ${savedAbs}`);
  console.log(`    下载读取路径 : ${readAbs}`);
  console.log(`    自洽一致     : ${ok ? '✓ YES' : '✗ NO  —— 代码有路径错配!'}`);
  return ok;
}
const selfConsistent =
  simulate('benke', 'work_1700000000000_ab12cd.docx') &&
  simulate('zhongzhi', 'exam_1_1700000000000.docx') &&
  simulate('zhongzhi', '报告.pdf');
console.log('');

// ---------- 4. 抽查数据库里 submission 的 file_path 是否真实存在 ----------
console.log('--- 4. 数据库 submission.file_path 真实性抽查（可选）---');
const dbCfg = {
  host: process.env.DB_HOST || '127.0.0.1',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASS,
  database: process.env.DB_NAME || 'competition_db',
};

(async () => {
  let mysqlOk = false;
  try {
    const mysql = require(path.join(root, 'node_modules', 'mysql2', 'promise'));
    const conn = await mysql.createConnection({
      host: dbCfg.host,
      user: dbCfg.user,
      password: dbCfg.password,
      database: dbCfg.database,
      connectTimeout: 4000,
    });
    mysqlOk = true;
    console.log(`  已连接数据库 ${dbCfg.database} @ ${dbCfg.host}`);

    const tables = [
      { name: 'benke_submissions', label: '本科', col: 'file_path' },
      { name: 'jiaoshi_submissions', label: '教师', col: 'file_path' },
      { name: 'exam_submissions', label: '中职/高职(word)', col: 'word_file_path' },
    ];
    for (const t of tables) {
      try {
        const [rows] = await conn.query(
          `SELECT id, ${t.col} FROM ${t.name} WHERE ${t.col} IS NOT NULL AND ${t.col} <> '' LIMIT 10`
        );
        console.log(`  [${t.label}] ${t.name}.${t.col} 抽样 ${rows.length} 条:`);
        if (rows.length === 0) {
          console.log('      （无带路径的记录，或该表无文件）');
        }
        for (const r of rows) {
          const fp = r[t.col];
          const abs = path.join(publicDir, fp);
          const exists = fs.existsSync(abs);
          console.log(
            `      id=${r.id}  file_path="${fp}"  -> 磁盘${exists ? '✓ 存在' : '✗ 缺失!'}`
          );
          if (!exists) console.log(`           解析绝对路径: ${abs}`);
        }
      } catch (e) {
        console.log(`  [${t.label}] 查询 ${t.name} 失败: ${e.message}`);
      }
    }
    await conn.end();
  } catch (e) {
    console.log(`  数据库检查跳过（未连接: ${e.message}）`);
    console.log('  如需抽查，请在环境变量中提供 DB_HOST/DB_USER/DB_PASS/DB_NAME 后重跑。');
  }
  console.log('');

  // ---------- 5. 结论 ----------
  console.log('==================================================');
  console.log(' 诊断结论');
  console.log('==================================================');
  const dirsMissing = subDirs.filter(d => !dirReport[d].exists);
  const dirsNotWritable = subDirs.filter(d => dirReport[d].exists && !dirReport[d].writable);
  if (dirsMissing.length) {
    console.log(`✗ 关键目录缺失: app/public/${dirsMissing.join(', ')}`);
    console.log('  → 上传文件无处落盘，下载必然 404。请在服务器后端目录执行：');
    for (const d of dirsMissing) {
      console.log(`    mkdir -p app/public/${d}/{zhongzhi,gaozhi,benke,jiaoshi}`);
    }
  } else {
    console.log('✓ 业务目录均存在');
  }
  if (dirsNotWritable.length) {
    console.log(`✗ 目录不可写: app/public/${dirsNotWritable.join(', ')}`);
    console.log('  → 检查进程用户对该目录的写权限（chown/chmod）。');
  } else {
    console.log('✓ 业务目录均可写');
  }
  console.log(selfConsistent ? '✓ 代码路径自洽（上传/下载指向同一文件）' : '✗ 代码路径不自洽，需修代码');
  if (mysqlOk) {
    console.log('  → 查看上方「磁盘缺失」标记：若有，说明文件没落盘（上传未成功或目录曾缺失）。');
  }
  console.log('==================================================');
})();