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

dotenv.config();

const app = express();
app.use(cors());
app.use(express.json({ limit: '100mb' }));

// Request Logger
app.use((req, res, next) => {
  console.log(`[${new Date().toISOString()}] ${req.method} ${req.url}`);
  next();
});

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  port: parseInt(process.env.DB_PORT || '3306'),
  charset: 'utf8mb4'
});

const handleError = (res, err, context, isGet = false) => {
  console.error(`❌ [${context}] Error:`, err.message);
  if (isGet) res.json([]);
  else res.status(500).json({ error: err.message });
};

// --- Mapping Definitions ---

const HW_SELECT_FIELDS = `
  id, 
  asset_type AS \`type\`, 
  corp AS \`법인\`, 
  asset_code AS \`자산코드\`, 
  purchase_date AS \`구매일\`,
  user_name AS \`사용자\`,
  dept AS \`현사용조직\`,
  prev_org AS \`이전사용조직\`,
  location AS \`위치\`,
  manager_primary AS \`담당자_정\`,
  manager_secondary AS \`담당자_부\`,
  product_name AS \`모델명\`,
  usage_category AS \`상세용도\`,
  usage_description AS \`상세\`,
  os AS \`OS\`,
  cpu AS \`CPU\`,
  gpu AS \`GPU\`,
  ram AS \`RAM\`,
  storage1 AS \`SSD1\`,
  storage2 AS \`SSD2\`,
  storage3 AS \`SSD3\`,
  mainboard AS \`메인보드\`,
  ip_address AS \`IP주소\`,
  remote_tool AS \`원격접속\`,
  server_id AS \`서버ID\`,
  server_pw AS \`서버PW\`,
  monitoring AS \`모니터링\`,
  price AS \`금액\`,
  vendor AS \`납품업체\`,
  remarks AS \`비고\`,
  asset_category AS \`category\`
`;

const HW_REVERSE_MAP = {
  'id': 'id',
  'type': 'asset_type',
  '법인': 'corp',
  '자산코드': 'asset_code',
  '구매일': 'purchase_date',
  '구매연월': 'purchase_date',
  '사용자': 'user_name',
  '현사용조직': 'dept',
  '이전사용조직': 'prev_org',
  '위치': 'location',
  '담당자_정': 'manager_primary',
  '담당자_부': 'manager_secondary',
  '모델명': 'product_name',
  '상세용도': 'usage_category',
  '상세': 'usage_description',
  'OS': 'os',
  'CPU': 'cpu',
  'GPU': 'gpu',
  'RAM': 'ram',
  'SSD1': 'storage1',
  'SSD2': 'storage2',
  'SSD3': 'storage3',
  '메인보드': 'mainboard',
  'IP주소': 'ip_address',
  '원격접속': 'remote_tool',
  '서버ID': 'server_id',
  '서버PW': 'server_pw',
  '모니터링': 'monitoring',
  '금액': 'price',
  '납품업체': 'vendor',
  '비고': 'remarks',
  'category': 'asset_category'
};

const mapObject = (obj, mapping) => {
  const mapped = {};
  Object.entries(obj).forEach(([key, val]) => {
    const dbKey = mapping[key] || key;
    mapped[dbKey] = val;
  });
  return mapped;
};

// --- GET Routes ---

app.get('/api/pc', async (req, res) => {
  try {
    const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "개인PC" OR asset_code LIKE "PC%"`);
    console.log(`📡 [GET /api/pc] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '개인PC', 구매연월: r.구매일 })));
  } catch (err) { handleError(res, err, 'GET /api/pc', true); }
});

app.get('/api/server', async (req, res) => {
  try {
    const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "서버" OR asset_code LIKE "SVR%"`);
    console.log(`📡 [GET /api/server] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '서버', 구매연월: r.구매일 })));
  } catch (err) { handleError(res, err, 'GET /api/server', true); }
});

app.get('/api/storage', async (req, res) => {
  try {
    const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "스토리지" OR asset_code LIKE "STO%"`);
    console.log(`📡 [GET /api/storage] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '스토리지', 구매연월: r.구매일 })));
  } catch (err) { handleError(res, err, 'GET /api/storage', true); }
});

app.get('/api/equip', async (req, res) => {
  try {
    const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "전산비품" OR asset_code LIKE "EQP%"`);
    console.log(`📡 [GET /api/equip] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '전산비품', 구매연월: r.구매일 })));
  } catch (err) { handleError(res, err, 'GET /api/equip', true); }
});

app.get('/api/mobile', async (req, res) => {
  try {
    const [rows] = await pool.query(`SELECT ${HW_SELECT_FIELDS} FROM asset_hardware WHERE asset_category = "모바일기기" OR asset_category LIKE "%모바일%" OR asset_code LIKE "MOB%"`);
    console.log(`📡 [GET /api/mobile] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '모바일기기', 구매연월: r.구매일 })));
  } catch (err) { handleError(res, err, 'GET /api/mobile', true); }
});

app.get('/api/asset/software/subscription', async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT 
        id, category AS \`분야\`, corp AS \`법인\`, dept AS \`부서\`, product_name AS \`제품명\`, 
        quantity AS \`수량\`, price AS \`금액\`, purchase_date AS \`구매일\`, start_date AS \`시작일\`, 
        expiry_date AS \`만료일\`, vendor AS \`납품업체\`, remarks AS \`비고\`, license_type AS \`라이선스유형\`, account_name AS \`계정명\`
      FROM asset_software_subscription
    `);
    console.log(`📡 [GET /api/asset/software/subscription] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '구독SW' })));
  } catch (err) { handleError(res, err, 'GET /api/asset/software/subscription', true); }
});

app.get('/api/asset/software/perpetual', async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT 
        id, category AS \`분야\`, corp AS \`법인\`, dept AS \`부서\`, product_name AS \`제품명\`, 
        quantity AS \`수량\`, price AS \`금액\`, purchase_date AS \`구매일\`, start_date AS \`시작일\`, 
        expiry_date AS \`만료일\`, vendor AS \`납품업체\`, remarks AS \`비고\`, license_key AS \`라이선스키\`, account_name AS \`계정명\`
      FROM asset_software_perpetual
    `);
    console.log(`📡 [GET /api/asset/software/perpetual] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '영구SW' })));
  } catch (err) { handleError(res, err, 'GET /api/asset/software/perpetual', true); }
});

app.get('/api/asset/cloud', async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT 
        id, platform_name AS \`플랫폼명\`, corp AS \`법인\`, dept AS \`부서\`, product_name AS \`제품명\`, 
        account_name AS \`계정명\`, pay_method AS \`결제수단\`, pay_day AS \`결제일\`, card_num AS \`연결카드번호\`, 
        monthly_cost AS \`당월청구액\`, remarks AS \`비고\`
      FROM asset_cloud
    `);
    console.log(`📡 [GET /api/asset/cloud] Returning ${rows.length} rows`);
    res.json(rows.map(r => ({ ...r, type: '클라우드' })));
  } catch (err) { handleError(res, err, 'GET /api/asset/cloud', true); }
});

app.get('/api/asset/domain', async (req, res) => {
  try {
    const [rows] = await pool.query(`SELECT * FROM asset_domain`);
    res.json(rows);
  } catch (err) { handleError(res, err, 'GET /api/asset/domain', true); }
});

app.get('/api/asset/software/assignment', async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT 
        id, sw_id, corp AS \`법인\`, dept AS \`부서\`, position AS \`직위\`, user_name AS \`이름\`, usage_period AS \`사용기간\`, doc_name AS \`신청서명\`
      FROM asset_software_assignment
    `);
    res.json(rows);
  } catch (err) { handleError(res, err, 'GET /api/asset/software/assignment', true); }
});

app.get('/api/asset/history', async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT 
        id, asset_id AS assetId, log_date AS \`date\`, log_user AS \`user\`, details, cost
      FROM asset_history
    `);
    res.json(rows);
  } catch (err) { handleError(res, err, 'GET /api/asset/history', true); }
});

// --- POST Batch Routes ---

async function saveHwBatch(category, items, res) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    // 1. 해당 카테고리 기존 데이터 삭제
    await connection.query('DELETE FROM asset_hardware WHERE asset_category = ?', [category]);
    
    // 2. 새 데이터 삽입
    for (const item of items) {
      const dbRow = mapObject(item, HW_REVERSE_MAP);
      dbRow.asset_category = category;
      if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
      
      // DB 컬럼에 없는 필드 제거
      const validColumns = ['id', 'asset_category', 'corp', 'asset_code', 'purchase_date', 'asset_type', 'usage_category', 'user_name', 'usage_description', 'dept', 'prev_org', 'location', 'manager_primary', 'manager_secondary', 'ip_address', 'remote_tool', 'server_id', 'server_pw', 'product_name', 'mainboard', 'os', 'cpu', 'ram', 'gpu', 'storage1', 'storage2', 'storage3', 'monitoring', 'price', 'vendor', 'remarks', 'storage_location', 'status'];
      const filteredRow = {};
      validColumns.forEach(col => { if (dbRow[col] !== undefined) filteredRow[col] = dbRow[col]; });

      await connection.query('INSERT INTO asset_hardware SET ?', [filteredRow]);
    }
    await connection.commit();
    res.json({ success: true, count: items.length });
  } catch (err) {
    await connection.rollback();
    handleError(res, err, `BATCH SAVE ${category}`);
  } finally {
    connection.release();
  }
}

app.post('/api/pc/batch', (req, res) => saveHwBatch('개인PC', req.body, res));
app.post('/api/server/batch', (req, res) => saveHwBatch('서버', req.body, res));
app.post('/api/storage/batch', (req, res) => saveHwBatch('스토리지', req.body, res));
app.post('/api/equip/batch', (req, res) => saveHwBatch('전산비품', req.body, res));
app.post('/api/mobile/batch', (req, res) => saveHwBatch('모바일기기', req.body, res));

app.post('/api/asset/software/subscription/batch', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await connection.query('DELETE FROM asset_software_subscription');
    const mapping = { '분야': 'category', '법인': 'corp', '부서': 'dept', '제품명': 'product_name', '수량': 'quantity', '금액': 'price', '구매일': 'purchase_date', '시작일': 'start_date', '만료일': 'expiry_date', '납품업체': 'vendor', '비고': 'remarks', '라이선스유형': 'license_type', '계정명': 'account_name' };
    for (const item of req.body) {
      const dbRow = mapObject(item, mapping);
      if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
      const filteredRow = {};
      ['id', 'corp', 'category', 'dept', 'product_name', 'license_type', 'quantity', 'price', 'purchase_date', 'start_date', 'expiry_date', 'vendor', 'remarks', 'account_name'].forEach(c => { if (dbRow[c] !== undefined) filteredRow[c] = dbRow[c]; });
      await connection.query('INSERT INTO asset_software_subscription SET ?', [filteredRow]);
    }
    await connection.commit();
    res.json({ success: true });
  } catch (err) { await connection.rollback(); handleError(res, err, 'BATCH SW SUB'); } finally { connection.release(); }
});

app.post('/api/asset/software/perpetual/batch', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await connection.query('DELETE FROM asset_software_perpetual');
    const mapping = { '분야': 'category', '법인': 'corp', '부서': 'dept', '제품명': 'product_name', '수량': 'quantity', '금액': 'price', '구매일': 'purchase_date', '시작일': 'start_date', '만료일': 'expiry_date', '납품업체': 'vendor', '비고': 'remarks', '라이선스키': 'license_key', '계정명': 'account_name' };
    for (const item of req.body) {
      const dbRow = mapObject(item, mapping);
      if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
      const filteredRow = {};
      ['id', 'corp', 'category', 'dept', 'product_name', 'license_key', 'quantity', 'price', 'purchase_date', 'start_date', 'expiry_date', 'vendor', 'remarks', 'account_name'].forEach(c => { if (dbRow[c] !== undefined) filteredRow[c] = dbRow[c]; });
      await connection.query('INSERT INTO asset_software_perpetual SET ?', [filteredRow]);
    }
    await connection.commit();
    res.json({ success: true });
  } catch (err) { await connection.rollback(); handleError(res, err, 'BATCH SW PERM'); } finally { connection.release(); }
});

app.post('/api/asset/cloud/batch', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await connection.query('DELETE FROM asset_cloud');
    const mapping = { '플랫폼명': 'platform_name', '법인': 'corp', '부서': 'dept', '제품명': 'product_name', '계정명': 'account_name', '결제수단': 'pay_method', '결제일': 'pay_day', '연결카드번호': 'card_num', '당월청구액': 'monthly_cost', '비고': 'remarks' };
    for (const item of req.body) {
      const dbRow = mapObject(item, mapping);
      if (!dbRow.id) dbRow.id = Math.random().toString(36).substring(2, 9);
      const filteredRow = {};
      ['id', 'platform_name', 'corp', 'dept', 'product_name', 'account_name', 'pay_method', 'pay_day', 'card_num', 'monthly_cost', 'remarks'].forEach(c => { if (dbRow[c] !== undefined) filteredRow[c] = dbRow[c]; });
      await connection.query('INSERT INTO asset_cloud SET ?', [filteredRow]);
    }
    await connection.commit();
    res.json({ success: true });
  } catch (err) { await connection.rollback(); handleError(res, err, 'BATCH CLOUD'); } finally { connection.release(); }
});

app.post('/api/asset/software/assignment/batch', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await connection.query('DELETE FROM asset_software_assignment');
    const mapping = { '법인': 'corp', '부서': 'dept', '직위': 'position', '이름': 'user_name', '사용기간': 'usage_period', '신청서명': 'doc_name' };
    for (const item of req.body) {
      const dbRow = mapObject(item, mapping);
      delete dbRow.id; // Auto-increment
      await connection.query('INSERT INTO asset_software_assignment SET ?', [dbRow]);
    }
    await connection.commit();
    res.json({ success: true });
  } catch (err) { await connection.rollback(); handleError(res, err, 'BATCH SW ASSIGN'); } finally { connection.release(); }
});

app.post('/api/asset/history/batch', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await connection.query('DELETE FROM asset_history');
    for (const item of req.body) {
      const dbRow = {
        asset_id: item.assetId,
        log_date: item.date,
        log_user: item.user,
        details: item.details,
        cost: item.cost || 0
      };
      await connection.query('INSERT INTO asset_history SET ?', [dbRow]);
    }
    await connection.commit();
    res.json({ success: true });
  } catch (err) { await connection.rollback(); handleError(res, err, 'BATCH HISTORY'); } finally { connection.release(); }
});

app.get('/api/generate-asset-code', async (req, res) => {
  try {
    const { prefix } = req.query;
    if (!prefix) return res.status(400).json({ error: 'Prefix is required' });
    const [rows] = await pool.query('SELECT asset_code FROM asset_hardware WHERE asset_code LIKE ? ORDER BY asset_code DESC LIMIT 1', [`${prefix}%`]);
    let nextNum = 1;
    if (rows.length > 0) {
      const lastCode = rows[0].asset_code;
      const lastNum = parseInt(lastCode.split('-').pop() || '0');
      nextNum = lastNum + 1;
    }
    res.json({ nextCode: `${prefix}${String(nextNum).padStart(3, '0')}` });
  } catch (err) { handleError(res, err, 'GENERATE CODE'); }
});

app.listen(3000, '0.0.0.0', () => {
  console.log('📡 ITAM BACKEND SERVER RUNNING ON PORT 3000 (Safe Korean Mapping)');
});