/**
 * 导入服务器资产数据
 * 用法: npx tsx scripts/import-servers.ts [excel文件路径]
 * 默认路径: templates-docs/服务器信息-issue.xlsx
 */

import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'

// 动态导入 xlsx
const XLSX = require('xlsx')

const dbPath = process.env.DATABASE_PATH || './data/assets.db'
const dbDir = path.dirname(dbPath)
if (!fs.existsSync(dbDir)) fs.mkdirSync(dbDir, { recursive: true })

const db = new Database(dbPath)
db.pragma('journal_mode = WAL')
db.pragma('foreign_keys = ON')

// 初始化数据库
function initDb() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      display_name TEXT NOT NULL,
      email TEXT,
      role TEXT NOT NULL DEFAULT 'viewer',
      is_active INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS roles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      display_name TEXT NOT NULL,
      permissions TEXT NOT NULL DEFAULT '[]',
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      expires_at TEXT NOT NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS api_keys (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      key_hash TEXT NOT NULL,
      permissions TEXT NOT NULL DEFAULT '["assets:read"]',
      last_used_at TEXT,
      expires_at TEXT,
      is_active INTEGER NOT NULL DEFAULT 1,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS assets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      serial_number TEXT UNIQUE,
      device_type TEXT NOT NULL,
      device_purpose TEXT,
      room TEXT,
      rack_position TEXT,
      node_name TEXT,
      business_ip TEXT,
      hdm_ip TEXT,
      manufacturer TEXT,
      device_model TEXT,
      status TEXT NOT NULL DEFAULT '在用',
      warranty_date TEXT,
      cpu_model TEXT, cpu_generation TEXT, cpu_cores INTEGER,
      cpu_count INTEGER, cpu_threads INTEGER, cpu_spec TEXT,
      memory_model TEXT, memory_frequency TEXT, memory_unit_capacity TEXT,
      memory_count INTEGER, memory_total TEXT,
      gpu_model TEXT, gpu_power TEXT, gpu_count INTEGER,
      nic1_model TEXT, nic1_type TEXT, nic1_speed TEXT, nic1_count INTEGER,
      nic2_model TEXT, nic2_type TEXT, nic2_speed TEXT, nic2_count INTEGER,
      nic3_model TEXT, nic3_type TEXT, nic3_speed TEXT, nic3_count INTEGER,
      sys_disk_model TEXT, sys_disk_spec TEXT, sys_disk_capacity TEXT,
      sys_disk_type TEXT, sys_disk_protocol TEXT, sys_disk_speed TEXT, sys_disk_count INTEGER,
      data_disk1_model TEXT, data_disk1_spec TEXT, data_disk1_capacity TEXT,
      data_disk1_type TEXT, data_disk1_protocol TEXT, data_disk1_speed TEXT, data_disk1_count INTEGER,
      data_disk2_model TEXT, data_disk2_spec TEXT, data_disk2_capacity TEXT,
      data_disk2_type TEXT, data_disk2_protocol TEXT, data_disk2_speed TEXT, data_disk2_count INTEGER,
      data_disk_total_space TEXT,
      raid_model TEXT, raid_spec TEXT, raid_count INTEGER,
      psu1_model TEXT, psu1_power TEXT, psu1_count INTEGER,
      psu2_model TEXT, psu2_power TEXT, psu2_count INTEGER,
      psu_total_power TEXT,
      board_model TEXT, board_count INTEGER,
      raw_data TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE IF NOT EXISTS audit_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER REFERENCES users(id),
      api_key_id INTEGER REFERENCES api_keys(id),
      action TEXT NOT NULL,
      entity_type TEXT NOT NULL,
      entity_id INTEGER,
      details TEXT,
      ip_address TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE INDEX IF NOT EXISTS idx_assets_node_name ON assets(node_name);
    CREATE INDEX IF NOT EXISTS idx_assets_business_ip ON assets(business_ip);
    CREATE INDEX IF NOT EXISTS idx_assets_device_type ON assets(device_type);
    CREATE INDEX IF NOT EXISTS idx_assets_status ON assets(status);
  `)

  // 创建默认 admin
  const bcrypt = require('bcryptjs')
  const existingAdmin = db.prepare('SELECT id FROM users WHERE username = ?').get('admin')
  if (!existingAdmin) {
    const passwordHash = bcrypt.hashSync('admin123', 10)
    db.prepare('INSERT INTO users (username, password_hash, display_name, role) VALUES (?, ?, ?, ?)')
      .run('admin', passwordHash, '管理员', 'admin')
  }

  const defaultRoles = [
    { name: 'admin', display_name: '管理员', permissions: '["*"]' },
    { name: 'editor', display_name: '编辑者', permissions: '["assets:read","assets:write","assets:delete"]' },
    { name: 'viewer', display_name: '查看者', permissions: '["assets:read"]' },
  ]
  for (const role of defaultRoles) {
    const existing = db.prepare('SELECT id FROM roles WHERE name = ?').get(role.name)
    if (!existing) {
      db.prepare('INSERT INTO roles (name, display_name, permissions) VALUES (?, ?, ?)')
        .run(role.name, role.display_name, role.permissions)
    }
  }
}

// Excel 列名到数据库字段的映射
const COL_MAP: Record<string, string> = {
  '序号': '_skip',
  '机房': 'room',
  '机架位置': 'rack_position',
  '节点名称': 'node_name',
  '设备型号': 'device_model',
  '序列号': 'serial_number',
  '状态': 'status',
  'HDM地址\n掩码255.255.255.128\n网关29.237.252.126/25': 'hdm_ip',
  '业务管理IP\n掩码255.255.255.128\n网关29.237.253.126/25': 'business_ip',
  '维保截止日期': 'warranty_date',
  'CPU型号': 'cpu_model',
  'CPU代数': 'cpu_generation',
  'CPU核心数': 'cpu_cores',
  'CPU数量': 'cpu_count',
  'CPU总线程数': 'cpu_threads',
  'CPU规格': 'cpu_spec',
  '内存型号': 'memory_model',
  '最大频率': 'memory_frequency',
  '单条内存容量': 'memory_unit_capacity',
  '数量': 'memory_count',
  '内存总量': 'memory_total',
  '系统盘型号': 'sys_disk_model',
  '系统盘规格': 'sys_disk_spec',
  '介质类型': 'sys_disk_type',
  '协议': 'sys_disk_protocol',
  '最大速率': 'sys_disk_speed',
  '数量.1': 'sys_disk_count',
  '数据盘型号': 'data_disk1_model',
  '数据盘规格': 'data_disk1_spec',
  '介质类型.1': 'data_disk1_type',
  '协议.1': 'data_disk1_protocol',
  '最大速率.1': 'data_disk1_speed',
  '数量.2': 'data_disk1_count',
  '数据盘总空间': 'data_disk_total_space',
  'GPU卡型号': 'gpu_model',
  '功率': 'gpu_power',
  '数量.3': 'gpu_count',
  'RAID卡型号': 'raid_model',
  '芯片型号': 'raid_spec',
  '数量.4': 'raid_count',
  '主板型号': 'board_model',
  '网卡1': 'nic1_model',
  '数量.5': 'nic1_count',
  '芯片型号.1': 'nic1_type',
  '端口类型': 'nic1_speed',
  '端口数量': '_skip',
  '端口总数': '_skip',
  '网卡2': 'nic2_model',
  '数量.6': 'nic2_count',
  '芯片型号.2': 'nic2_type',
  '端口类型.1': 'nic2_speed',
  '端口数量.1': '_skip',
  '端口总数.1': '_skip',
  '网卡3': 'nic3_model',
  '数量.7': 'nic3_count',
  '芯片型号.3': 'nic3_type',
  '端口类型.2': 'nic3_speed',
  '端口数量.2': '_skip',
  '端口总数.2': '_skip',
  '电源型号1': 'psu1_model',
  '额定功率': 'psu1_power',
  '数量.8': 'psu1_count',
  '电源型号2': 'psu2_model',
  '额定功率.1': 'psu2_power',
  '数量.9': 'psu2_count',
  '电源总功率': 'psu_total_power',
}

function cleanValue(val: any): string | number | null {
  if (val === null || val === undefined || val === '' || (typeof val === 'string' && val.trim() === '')) return null
  if (typeof val === 'number') return val
  return String(val).trim()
}

function importSheet(ws: any, deviceType: string, devicePurpose: string) {
  const data = XLSX.utils.sheet_to_json(ws, { header: 1, defval: '' })
  if (data.length < 2) {
    console.log(`  Sheet has no data rows`)
    return 0
  }

  const headers = data[0] as string[]
  const rows = data.slice(1)

  // Map column index to db field
  const fieldMap: { idx: number; field: string }[] = []
  for (let i = 0; i < headers.length; i++) {
    const header = headers[i]
    const field = COL_MAP[header]
    if (field && field !== '_skip') {
      fieldMap.push({ idx: i, field })
    }
  }

  // 按位置兜底：如果关键字段尚未映射（列名匹配失败时），按固定列索引补上
  const hasField = (f: string) => fieldMap.some(m => m.field === f)
  if (!hasField('business_ip') && headers.length > 8) {
    fieldMap.push({ idx: 8, field: 'business_ip' })
    console.log('  警告: business_ip 未通过列名匹配，按列索引 8 兜底')
  }
  if (!hasField('hdm_ip') && headers.length > 7) {
    fieldMap.push({ idx: 7, field: 'hdm_ip' })
    console.log('  警告: hdm_ip 未通过列名匹配，按列索引 7 兜底')
  }

  const insertFields = ['device_type', 'device_purpose', ...fieldMap.map(f => f.field)]
  const placeholders = insertFields.map(() => '?').join(',')
  const stmt = db.prepare(`
    INSERT OR REPLACE INTO assets (${insertFields.join(',')}, raw_data)
    VALUES (${placeholders}, ?)
  `)

  let count = 0
  const importTime = new Date().toISOString()

  const transaction = db.transaction(() => {
    for (const row of rows) {
      const r = row as any[]
      if (!r || r.length === 0 || !r[0]) continue

      const values: any[] = [deviceType, devicePurpose]
      for (const fm of fieldMap) {
        values.push(cleanValue(r[fm.idx]))
      }
      values.push(JSON.stringify({ imported_at: importTime, raw_row: r }))

      stmt.run(...values)
      count++
    }
  })

  transaction()
  return count
}

async function main() {
  const excelPath = process.argv[2] || path.join(__dirname, '..', 'templates-docs', '服务器信息-issue.xlsx')

  if (!fs.existsSync(excelPath)) {
    console.error(`Excel file not found: ${excelPath}`)
    process.exit(1)
  }

  console.log('Initializing database...')
  initDb()

  console.log(`Reading Excel: ${excelPath}`)
  const wb = XLSX.readFile(excelPath)

  console.log('Importing GPU servers...')
  const gpuSheet = wb.Sheets['GPU服务器']
  const gpuCount = importSheet(gpuSheet, 'GPU服务器', 'GPU计算')
  console.log(`  Imported ${gpuCount} GPU servers`)

  console.log('Importing storage servers...')
  const storSheet = wb.Sheets['存储服务器']
  const storCount = importSheet(storSheet, '存储服务器', '存储')
  console.log(`  Imported ${storCount} storage servers`)

  console.log(`\nTotal: ${gpuCount + storCount} servers imported`)
  db.close()
}

main().catch(console.error)