#!/usr/bin/env node
/**
 * Database Migration Script
 * Renames all tables to add 5Why_ prefix
 *
 * Run: node scripts/migrate-table-prefix.js
 */

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

dotenv.config();

const dbConfig = {
  host: process.env.DB_HOST || 'mysql.theaken.com',
  port: parseInt(process.env.DB_PORT) || 33306,
  user: process.env.DB_USER || 'A102',
  password: process.env.DB_PASSWORD || 'Bb123456',
  database: process.env.DB_NAME || 'db_A102'
};

// 要重新命名的表（舊名 -> 新名）
const tableRenames = [
  { old: 'users', new: '5Why_users' },
  { old: 'analyses', new: '5Why_analyses' },
  { old: 'analysis_perspectives', new: '5Why_analysis_perspectives' },
  { old: 'analysis_whys', new: '5Why_analysis_whys' },
  { old: 'llm_configs', new: '5Why_llm_configs' },
  { old: 'system_settings', new: '5Why_system_settings' },
  { old: 'audit_logs', new: '5Why_audit_logs' },
  { old: 'sessions', new: '5Why_sessions' }
];

// 要重新建立的視圖
const viewRenames = [
  { old: 'user_analysis_stats', new: '5Why_user_analysis_stats' },
  { old: 'recent_analyses', new: '5Why_recent_analyses' }
];

async function migrateTablePrefix() {
  let connection;

  try {
    console.log('\n╔════════════════════════════════════════════════════════╗');
    console.log('║  5 Why Analyzer - Table Prefix Migration               ║');
    console.log('╚════════════════════════════════════════════════════════╝\n');

    console.log('🔄 Connecting to database...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected successfully\n');

    // 先刪除舊的視圖（因為它們依賴表）
    console.log('📋 Dropping old views...');
    for (const view of viewRenames) {
      try {
        await connection.execute(`DROP VIEW IF EXISTS ${view.old}`);
        console.log(`   ✓ Dropped view: ${view.old}`);
      } catch (err) {
        console.log(`   ⚠ View ${view.old} not found or already dropped`);
      }
    }
    console.log('');

    // 重新命名表
    console.log('📋 Renaming tables...');
    for (const table of tableRenames) {
      try {
        // 檢查舊表是否存在
        const [oldExists] = await connection.execute(
          `SELECT COUNT(*) as count FROM information_schema.tables
           WHERE table_schema = ? AND table_name = ?`,
          [dbConfig.database, table.old]
        );

        // 檢查新表是否已存在
        const [newExists] = await connection.execute(
          `SELECT COUNT(*) as count FROM information_schema.tables
           WHERE table_schema = ? AND table_name = ?`,
          [dbConfig.database, table.new]
        );

        if (oldExists[0].count > 0 && newExists[0].count === 0) {
          await connection.execute(`RENAME TABLE \`${table.old}\` TO \`${table.new}\``);
          console.log(`   ✓ Renamed: ${table.old} -> ${table.new}`);
        } else if (newExists[0].count > 0) {
          console.log(`   ⚠ Table ${table.new} already exists, skipping...`);
        } else {
          console.log(`   ⚠ Table ${table.old} not found, skipping...`);
        }
      } catch (err) {
        console.error(`   ✗ Error renaming ${table.old}: ${err.message}`);
      }
    }
    console.log('');

    // 建立新的視圖
    console.log('📋 Creating new views...');

    // user_analysis_stats 視圖
    try {
      await connection.execute(`
        CREATE OR REPLACE VIEW 5Why_user_analysis_stats AS
        SELECT
            u.id AS user_id,
            u.username,
            u.employee_id,
            u.department,
            COUNT(a.id) AS total_analyses,
            COUNT(CASE WHEN a.status = 'completed' THEN 1 END) AS completed_analyses,
            COUNT(CASE WHEN a.status = 'failed' THEN 1 END) AS failed_analyses,
            AVG(a.processing_time) AS avg_processing_time,
            MAX(a.created_at) AS last_analysis_at
        FROM 5Why_users u
        LEFT JOIN 5Why_analyses a ON u.id = a.user_id
        GROUP BY u.id, u.username, u.employee_id, u.department
      `);
      console.log('   ✓ Created view: 5Why_user_analysis_stats');
    } catch (err) {
      console.error(`   ✗ Error creating view 5Why_user_analysis_stats: ${err.message}`);
    }

    // recent_analyses 視圖
    try {
      await connection.execute(`
        CREATE OR REPLACE VIEW 5Why_recent_analyses AS
        SELECT
            a.id,
            a.finding,
            u.username,
            u.employee_id,
            a.output_language,
            a.status,
            a.processing_time,
            a.created_at
        FROM 5Why_analyses a
        JOIN 5Why_users u ON a.user_id = u.id
        ORDER BY a.created_at DESC
        LIMIT 100
      `);
      console.log('   ✓ Created view: 5Why_recent_analyses');
    } catch (err) {
      console.error(`   ✗ Error creating view 5Why_recent_analyses: ${err.message}`);
    }

    console.log('\n╔════════════════════════════════════════════════════════╗');
    console.log('║  Migration Complete!                                   ║');
    console.log('╚════════════════════════════════════════════════════════╝\n');

    // 列出所有 5Why_ 表
    const [tables] = await connection.execute(
      `SELECT table_name FROM information_schema.tables
       WHERE table_schema = ? AND table_name LIKE '5Why_%'
       ORDER BY table_name`,
      [dbConfig.database]
    );

    if (tables.length > 0) {
      console.log('📊 5Why tables in database:');
      tables.forEach((table, index) => {
        console.log(`   ${index + 1}. ${table.TABLE_NAME || table.table_name}`);
      });
    }

  } catch (error) {
    console.error('\n❌ Migration failed:');
    console.error('   Error:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 Database connection closed\n');
    }
  }
}

// 執行遷移
migrateTablePrefix();