296 lines
8.4 KiB
JavaScript
296 lines
8.4 KiB
JavaScript
// 重置資料庫腳本
|
||
require('dotenv').config({ path: '.env.local' })
|
||
const mysql = require('mysql2/promise')
|
||
const fs = require('fs')
|
||
const path = require('path')
|
||
|
||
async function resetDatabase() {
|
||
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('✅ 資料庫連接成功！')
|
||
|
||
// 刪除現有的表（如果存在）
|
||
console.log('\n🗑️ 清理現有表...')
|
||
const dropTables = [
|
||
'DROP TABLE IF EXISTS review_responses',
|
||
'DROP TABLE IF EXISTS kpi_progress',
|
||
'DROP TABLE IF EXISTS kpi',
|
||
'DROP TABLE IF EXISTS reviews',
|
||
'DROP TABLE IF EXISTS users'
|
||
]
|
||
|
||
for (const dropSQL of dropTables) {
|
||
try {
|
||
await connection.execute(dropSQL)
|
||
console.log(` ✅ 刪除表: ${dropSQL.split(' ')[4]}`)
|
||
} catch (error) {
|
||
console.log(` ⚠️ 刪除表失敗: ${error.message}`)
|
||
}
|
||
}
|
||
|
||
// 讀取並執行 schema
|
||
console.log('\n📋 創建新的資料庫結構...')
|
||
const schemaPath = path.join(__dirname, 'create-database-schema.sql')
|
||
const schemaSQL = fs.readFileSync(schemaPath, 'utf8')
|
||
|
||
const statements = schemaSQL.split(';').filter(stmt => stmt.trim())
|
||
|
||
for (const statement of statements) {
|
||
if (statement.trim()) {
|
||
try {
|
||
await connection.execute(statement)
|
||
console.log(' ✅ 執行 SQL 語句成功')
|
||
} catch (error) {
|
||
console.log(` ❌ SQL 執行失敗: ${error.message}`)
|
||
}
|
||
}
|
||
}
|
||
|
||
// 插入測試數據
|
||
console.log('\n📊 插入測試數據...')
|
||
|
||
// 插入用戶數據
|
||
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, summary
|
||
) 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('\n🔍 驗證數據...')
|
||
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('\n🎉 資料庫重置完成！')
|
||
console.log('💡 您現在可以啟動應用程式並測試功能')
|
||
|
||
} catch (error) {
|
||
console.error('❌ 資料庫重置失敗:', error)
|
||
process.exit(1)
|
||
}
|
||
}
|
||
|
||
resetDatabase()
|