const XLSX = require('xlsx');
const mysql = require('mysql2/promise');
const dotenv = require('dotenv');
const path = require('path');

dotenv.config({ path: path.join(__dirname, '../.env') });

const { DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT } = process.env;

async function importAssets() {
  const connection = await mysql.createConnection({
    host: DB_HOST,
    user: DB_USER,
    password: DB_PASS,
    database: DB_NAME,
    port: parseInt(DB_PORT || '3306')
  });

  console.log('🚀 [Step 1] 데이터 로드 및 사전 준비...');
  
  // 1. 엑셀 파일 로드
  const workbook = XLSX.readFile('asset_pc (2026.06.15).xlsx');
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const rawData = XLSX.utils.sheet_to_json(sheet);

  // 2. system_users 데이터 맵 생성 (사번 기준 빠른 조회를 위함)
  const [userRows] = await connection.query('SELECT emp_no, user_name, dept_name, position, status FROM system_users');
  const userMap = new Map();
  userRows.forEach(u => userMap.set(String(u.emp_no), u));

  // 3. 기존 자산 중복 체크용 맵 생성 (emp_no + asset_type + category)
  const [existingAssets] = await connection.query('SELECT emp_no, asset_type, category FROM asset_core');
  const existingSet = new Set();
  existingAssets.forEach(a => {
    existingSet.add(`${a.emp_no}|${a.asset_type}|${a.category}`);
  });

  console.log(`📊 처리 대상 데이터: ${rawData.length}건`);
  
  let skipCount = 0;
  let insertCount = 0;

  for (let i = 0; i < rawData.length; i++) {
    const row = rawData[i];
    const empNo = String(row.emp_no);
    const assetType = row.asset_type || '개인PC';
    const category = row.category || 'PC';
    
    // 중복 체크
    if (existingSet.has(`${empNo}|${assetType}|${category}`)) {
      skipCount++;
      continue;
    }

    // [Step 2] 데이터 정제
    // 1. 사용자 정보 매칭
    const matchedUser = userMap.get(empNo);
    const userName = matchedUser ? matchedUser.user_name : row.user_current;
    const deptName = matchedUser ? matchedUser.dept_name : row.current_dept;
    const position = matchedUser ? matchedUser.position : '';
    
    // 2. 날짜 최적화 (purchase_date_1, purchase_date_2 중 최신값)
    const d1 = parseInt(row.purchase_date_1) || 0;
    const d2 = parseInt(row.purchase_date_2) || 0;
    const latestDate = Math.max(d1, d2);
    const purchaseDate = latestDate > 0 ? String(latestDate) : '';

    // 3. 고유 ID 생성
    const assetId = `PC_20260615_${String(i + 1).padStart(4, '0')}`;
    const now = new Date().toISOString().replace('T', ' ').substring(0, 19);

    try {
      // [Step 3] DB 입력
      // A. asset_core 입력
      await connection.query(
        `INSERT INTO asset_core (id, asset_code, category, asset_type, current_role, asset_purpose, service_type, 
         purchase_corp, purchase_date, memo, manager_primary, current_dept, user_current, emp_no, user_position, created_at, updated_at) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [assetId, assetId, category, assetType, row.current_role, row.asset_purpose, row.service_type, 
         '', purchaseDate, row.memo || '', '', deptName, userName, empNo, position, now, now]
      );

      // B. asset_spec 입력
      await connection.query(
        `INSERT INTO asset_spec (asset_id, model_name, mainboard, cpu, ram, gpu) VALUES (?, ?, ?, ?, ?, ?)`,
        [assetId, '', row.mainboard || '', row.cpu || '', row.ram || '', row.gpu || '']
      );

      // C. asset_volume 입력 (SSD1, SSD2, HDD1~4)
      const volumes = [
        { type: 'SSD', cap: row.SDD1, slot: 1 },
        { type: 'SSD', cap: row.SDD2, slot: 2 },
        { type: 'HDD', cap: row.HDD1, slot: 3 },
        { type: 'HDD', cap: row.HDD2, slot: 4 },
        { type: 'HDD', cap: row.HDD3, slot: 5 },
        { type: 'HDD', cap: row.HDD4, slot: 6 }
      ];

      for (const vol of volumes) {
        if (vol.cap && vol.cap !== '0' && vol.cap !== 0) {
          await connection.query(
            `INSERT INTO asset_volume (asset_id, disk_type, capacity, slot_no) VALUES (?, ?, ?, ?)`,
            [assetId, vol.type, String(vol.cap), vol.slot]
          );
        }
      }

      insertCount++;
      existingSet.add(`${empNo}|${assetType}|${category}`); // 실시간 중복 방지 추가
    } catch (err) {
      console.error(`❌ [${empNo}] 처리 중 오류:`, err.message);
    }
  }

  console.log(`\n✨ 작업 완료!`);
  console.log(`- 신규 입력: ${insertCount}건`);
  console.log(`- 중복 스킵: ${skipCount}건`);
  
  await connection.end();
}

importAssets().catch(console.error);