ExecuBoard/scripts/init-database.js

// 資料庫初始化腳本
require('dotenv').config({ path: '.env.local' })
const mysql = require('mysql2/promise')
const fs = require('fs')
const path = require('path')

async function initDatabase() {
  const config = {
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    database: process.env.DB_DATABASE,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
  }

  console.log('🔗 連接到 MySQL 資料庫...')
  console.log(`主機: ${config.host}:${config.port}`)
  console.log(`資料庫: ${config.database}`)
  console.log(`用戶: ${config.user}`)

  try {
    const connection = await mysql.createConnection(config)
    console.log('✅ 資料庫連接成功！')

    // 讀取 SQL schema 檔案
    const schemaPath = path.join(__dirname, 'create-database-schema.sql')
    const schemaSQL = fs.readFileSync(schemaPath, 'utf8')
    
    // 執行 schema
    console.log('📋 執行資料庫 schema...')
    const statements = schemaSQL.split(';').filter(stmt => stmt.trim())
    
    for (const statement of statements) {
      if (statement.trim()) {
        await connection.execute(statement)
      }
    }
    console.log('✅ Schema 執行完成')

    // 插入測試數據
    console.log('📊 插入測試數據...')
    
    // 插入用戶數據
    const users = [
      {
        id: 'user_admin',
        name: '系統管理員',
        email: 'admin@company.com',
        role: 'executive',
        avatar_url: null
      },
      {
        id: 'user_001',
        name: '陳雅雯',
        email: 'chen@company.com',
        role: 'manager',
        avatar_url: null
      },
      {
        id: 'user_002',
        name: '王志明',
        email: 'wang@company.com',
        role: 'manager',
        avatar_url: null
      },
      {
        id: 'user_003',
        name: '李美玲',
        email: 'li@company.com',
        role: 'manager',
        avatar_url: null
      },
      {
        id: 'user_004',
        name: '張建國',
        email: 'zhang@company.com',
        role: 'manager',
        avatar_url: null
      }
    ]

    for (const user of users) {
      await connection.execute(
        'INSERT IGNORE INTO users (id, name, email, role, avatar_url) VALUES (?, ?, ?, ?, ?)',
        [user.id, user.name, user.email, user.role, user.avatar_url]
      )
    }
    console.log('✅ 用戶數據插入完成')

    // 插入 KPI 數據
    const kpis = [
      {
        id: 'kpi_001',
        user_id: 'user_001',
        title: '營收成長率',
        description: '年度營收成長百分比目標',
        category: 'financial',
        weight: 30,
        target_value: 100,
        current_value: 85,
        unit: '%',
        start_date: '2024-01-01',
        end_date: '2024-12-31',
        approved_by: 'user_admin',
        status: 'active'
      },
      {
        id: 'kpi_002',
        user_id: 'user_002',
        title: '團隊滿意度',
        description: '員工滿意度調查分數',
        category: 'team',
        weight: 25,
        target_value: 90,
        current_value: 92,
        unit: '分',
        start_date: '2024-01-01',
        end_date: '2024-12-31',
        approved_by: 'user_admin',
        status: 'active'
      },
      {
        id: 'kpi_003',
        user_id: 'user_003',
        title: '市場佔有率',
        description: '公司在目標市場的佔有率',
        category: 'operational',
        weight: 20,
        target_value: 75,
        current_value: 68,
        unit: '%',
        start_date: '2024-01-01',
        end_date: '2024-12-31',
        approved_by: 'user_admin',
        status: 'active'
      },
      {
        id: 'kpi_004',
        user_id: 'user_004',
        title: '創新指數',
        description: '新產品開發和創新項目數量',
        category: 'innovation',
        weight: 25,
        target_value: 80,
        current_value: 45,
        unit: '項',
        start_date: '2024-01-01',
        end_date: '2024-12-31',
        approved_by: 'user_admin',
        status: 'at_risk'
      }
    ]

    for (const kpi of kpis) {
      await connection.execute(
        `INSERT IGNORE INTO kpi (
          id, user_id, title, description, category, weight, 
          target_value, current_value, unit, start_date, end_date, 
          approved_by, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          kpi.id, kpi.user_id, kpi.title, kpi.description, kpi.category,
          kpi.weight, kpi.target_value, kpi.current_value, kpi.unit,
          kpi.start_date, kpi.end_date, kpi.approved_by, kpi.status
        ]
      )
    }
    console.log('✅ KPI 數據插入完成')

    // 插入評審數據
    const reviews = [
      {
        id: 'review_001',
        reviewer_id: 'user_admin',
        reviewee_id: 'user_001',
        review_type: 'quarterly',
        scheduled_date: '2024-03-15 14:00:00',
        status: 'scheduled',
        notes: 'Q1 績效評審'
      },
      {
        id: 'review_002',
        reviewer_id: 'user_admin',
        reviewee_id: 'user_002',
        review_type: 'quarterly',
        scheduled_date: '2024-03-20 10:00:00',
        status: 'scheduled',
        notes: 'Q1 績效評審'
      }
    ]

    for (const review of reviews) {
      await connection.execute(
        `INSERT IGNORE INTO reviews (
          id, reviewer_id, reviewee_id, review_type, 
          scheduled_date, status, notes
        ) VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          review.id, review.reviewer_id, review.reviewee_id,
          review.review_type, review.scheduled_date, review.status, review.notes
        ]
      )
    }
    console.log('✅ 評審數據插入完成')

    // 插入 KPI 進度數據
    const progressData = [
      {
        kpi_id: 'kpi_001',
        progress: 85,
        comment: 'Q1 表現良好，預計 Q2 可達標',
        blockers: null,
        created_by: 'user_001'
      },
      {
        kpi_id: 'kpi_002',
        progress: 92,
        comment: '團隊滿意度超出預期',
        blockers: null,
        created_by: 'user_002'
      },
      {
        kpi_id: 'kpi_003',
        progress: 68,
        comment: '市場競爭激烈，需要調整策略',
        blockers: '競爭對手價格戰',
        created_by: 'user_003'
      },
      {
        kpi_id: 'kpi_004',
        progress: 45,
        comment: '創新項目進度落後，需要加速',
        blockers: '研發資源不足',
        created_by: 'user_004'
      }
    ]

    for (const progress of progressData) {
      await connection.execute(
        `INSERT IGNORE INTO kpi_progress (
          kpi_id, progress, comment, blockers, created_by
        ) VALUES (?, ?, ?, ?, ?)`,
        [
          progress.kpi_id, progress.progress, progress.comment,
          progress.blockers, progress.created_by
        ]
      )
    }
    console.log('✅ KPI 進度數據插入完成')

    // 驗證數據
    console.log('🔍 驗證數據...')
    const [userCount] = await connection.execute('SELECT COUNT(*) as count FROM users')
    const [kpiCount] = await connection.execute('SELECT COUNT(*) as count FROM kpi')
    const [reviewCount] = await connection.execute('SELECT COUNT(*) as count FROM reviews')
    const [progressCount] = await connection.execute('SELECT COUNT(*) as count FROM kpi_progress')

    console.log(`📊 數據統計:`)
    console.log(`   - 用戶: ${userCount[0].count} 筆`)
    console.log(`   - KPI: ${kpiCount[0].count} 筆`)
    console.log(`   - 評審: ${reviewCount[0].count} 筆`)
    console.log(`   - 進度記錄: ${progressCount[0].count} 筆`)

    await connection.end()
    console.log('🎉 資料庫初始化完成！')
    console.log('💡 您現在可以啟動應用程式並測試功能')

  } catch (error) {
    console.error('❌ 資料庫初始化失敗:', error)
    process.exit(1)
  }
}

// 執行初始化
initDatabase()