/**
 * 从 Excel 导入"更换配件名称"到工单数据库
 * 用法: npx tsx scripts/import-parts-name.ts
 */

import Database from 'better-sqlite3';
import * as XLSX from 'xlsx';
import * as path from 'path';

const EXCEL_PATH = path.resolve(__dirname, '../../reference/图灵机房工单跟踪记录-总表.xlsx');
const DB_PATH = path.resolve(__dirname, '../data/issue.db');

function convertTicketId(raw: string): string {
  let id = raw.trim();
  // 去掉旧格式 "故障编号" 前缀
  if (id.startsWith('故障编号')) {
    id = id.replace('故障编号', '');
  }
  // 去掉连字符
  id = id.replace(/-/g, '');
  // 右补零至 14 位
  id = id.padEnd(14, '0');
  return id;
}

function main() {
  // 读取 Excel
  const wb = XLSX.readFile(EXCEL_PATH);
  const sheet = wb.Sheets[wb.SheetNames[0]];
  const data = XLSX.utils.sheet_to_json<Record<string, string>>(sheet, { defval: '' });

  // 打开数据库
  const db = new Database(DB_PATH);
  db.pragma('journal_mode = WAL');

  const updateStmt = db.prepare('UPDATE tickets SET parts_name = ? WHERE id = ?');

  let updated = 0;
  let notFound = 0;
  const notFoundList: string[] = [];

  for (const row of data) {
    const rawId = row['故障编号/工单号'] || '';
    const partsName = (row['更换配件名称'] || '').trim();
    if (!rawId || !partsName) continue;

    const ticketId = convertTicketId(rawId);
    const result = updateStmt.run(partsName, ticketId);

    if (result.changes > 0) {
      console.log(`  ✓ ${ticketId} → "${partsName}"`);
      updated++;
    } else {
      console.log(`  ✗ ${ticketId} → 未找到工单 (原始: "${rawId}")`);
      notFound++;
      notFoundList.push(`${rawId} → ${ticketId}`);
    }
  }

  console.log(`\n完成: 更新 ${updated} 条, 未找到 ${notFound} 条`);
  if (notFoundList.length > 0) {
    console.log('未找到的工单号:');
    notFoundList.forEach(s => console.log(`  - ${s}`));
  }

  db.close();
}

main();