ITAM/db_init.js

import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

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

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

  console.log('🔄 DB 초기화 시작 (영문 표준 스키마 적용)...');

  const tablesToDrop = [
    'pc_assets', 'server_assets', 'storage_assets', 'equip_assets', 'mobile_assets', 
    'sw_sub_assets', 'sw_perm_assets', 'cloud_assets', 'sw_users', 'asset_logs'
  ];
  for (const table of tablesToDrop) {
    await connection.query(`DROP TABLE IF EXISTS ${table}`);
  }

  const createHardwareTable = (tableName, comment) => `
    CREATE TABLE ${tableName} (
      id VARCHAR(50) PRIMARY KEY,
      corp VARCHAR(100),
      asset_code VARCHAR(100),
      purchase_date VARCHAR(50),
      type VARCHAR(50),
      detail_purpose VARCHAR(50),
      purpose VARCHAR(255),
      details TEXT,
      current_org VARCHAR(255),
      prev_org VARCHAR(255),
      location VARCHAR(255),
      manager_main VARCHAR(100),
      manager_sub VARCHAR(100),
      ip_address VARCHAR(100),
      remote_tool VARCHAR(100),
      server_id VARCHAR(100),
      server_pw VARCHAR(100),
      model_name VARCHAR(255),
      mainboard VARCHAR(255) COMMENT '메인보드',
      os VARCHAR(100),
      cpu VARCHAR(255),
      ram VARCHAR(100),
      gpu VARCHAR(100),
      storage1 VARCHAR(255),
      storage2 VARCHAR(255),
      storage3 VARCHAR(255),
      monitoring VARCHAR(100),
      price VARCHAR(100),
      remarks TEXT,
      storage_location VARCHAR(255),
      status VARCHAR(50),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `;

  await connection.query(createHardwareTable('pc_assets', 'PC'));
  await connection.query(createHardwareTable('server_assets', 'Server'));
  await connection.query(createHardwareTable('storage_assets', 'Storage'));
  await connection.query(createHardwareTable('equip_assets', 'Equipment'));
  await connection.query(createHardwareTable('mobile_assets', 'Mobile'));

  await connection.query(`
    CREATE TABLE sw_sub_assets (
      id VARCHAR(50) PRIMARY KEY,
      corp VARCHAR(100) COMMENT '구매법인',
      category VARCHAR(100) COMMENT '분야',
      dept VARCHAR(100) COMMENT '부서',
      product_name VARCHAR(255) COMMENT '제품명',
      license_type VARCHAR(100) COMMENT '라이선스 유형',
      quantity INT COMMENT '수량',
      price VARCHAR(100) COMMENT '금액',
      purchase_date VARCHAR(50) COMMENT '구매일',
      start_date VARCHAR(50) COMMENT '시작일',
      expiry_date VARCHAR(50) COMMENT '만료일',
      vendor VARCHAR(255) COMMENT '납품업체',
      remarks TEXT COMMENT '비고',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await connection.query(`
    CREATE TABLE sw_perm_assets (
      id VARCHAR(50) PRIMARY KEY,
      corp VARCHAR(100) COMMENT '구매법인',
      category VARCHAR(100) COMMENT '분야',
      dept VARCHAR(100) COMMENT '부서',
      product_name VARCHAR(255) COMMENT '제품명',
      license_key VARCHAR(255) COMMENT '라이선스 키',
      quantity INT COMMENT '수량',
      price VARCHAR(100) COMMENT '금액',
      purchase_date VARCHAR(50) COMMENT '구매일',
      start_date VARCHAR(50) COMMENT '시작일',
      expiry_date VARCHAR(50) COMMENT '만료일',
      vendor VARCHAR(255) COMMENT '납품업체',
      remarks TEXT COMMENT '비고',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await connection.query(`
    CREATE TABLE cloud_assets (
      id VARCHAR(50) PRIMARY KEY,
      platform_name VARCHAR(100),
      corp VARCHAR(100),
      dept VARCHAR(100),
      product_name VARCHAR(255),
      account_name VARCHAR(255),
      pay_method VARCHAR(100),
      pay_day VARCHAR(50),
      card_num VARCHAR(100),
      monthly_fee VARCHAR(100),
      remarks TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await connection.query(`
    CREATE TABLE sw_users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      sw_id VARCHAR(50),
      corp VARCHAR(100),
      dept VARCHAR(100),
      position VARCHAR(50),
      user_name VARCHAR(100),
      usage_period VARCHAR(100),
      doc_name VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await connection.query(`
    CREATE TABLE asset_logs (
      id INT AUTO_INCREMENT PRIMARY KEY,
      asset_id VARCHAR(50),
      log_date VARCHAR(50),
      log_user VARCHAR(100),
      details TEXT,
      cost DECIMAL(15,2) DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  await connection.query(`
    CREATE TABLE ops_domain_assets (
      id VARCHAR(50) PRIMARY KEY,
      type VARCHAR(50) COMMENT '유형',
      corp VARCHAR(100) COMMENT '법인',
      service_name VARCHAR(255) COMMENT '서비스명',
      domain_name VARCHAR(255) COMMENT '관리도메인',
      start_date VARCHAR(50) COMMENT '시작일',
      expiry_date VARCHAR(50) COMMENT '만료일',
      price VARCHAR(100) COMMENT '금액',
      manager_main VARCHAR(100) COMMENT '담당자',
      manager_sub VARCHAR(100) COMMENT '담당자(부)',
      remarks TEXT COMMENT '비고',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  `);

  console.log('✅ 모든 테이블이 영문 표준 스키마로 재생성되었습니다.');
  await connection.end();
}

initDB().catch(err => {
  console.error('❌ DB 초기화 실패:', err);
  process.exit(1);
});