/**
 * 数据库迁移脚本：添加专业大类字段
 * 执行方式: node scripts/add-professional-category.js
 */

const mysql = require('mysql2/promise');
const fs = require('fs').promises;
const path = require('path');

// 数据库配置
const dbConfig = {
    host: '123.60.55.248',
    port: 3306,
    user: 'ddcz_bitmap',
    password: 'EyTimzFHa8YfYEfY',
    database: 'ddcz_bitmap'
};

async function runMigration() {
    let connection;

    try {
        console.log('🔄 连接数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功\n');

        // 执行ALTER TABLE添加字段
        console.log('🔧 执行ALTER TABLE添加专业大类字段...');
        const alterSQL = `
            ALTER TABLE \`user_profiles\`
            ADD COLUMN \`professional_category\` VARCHAR(100) DEFAULT NULL COMMENT '专业大类名称' AFTER \`major\`,
            ADD COLUMN \`professional_category_code\` VARCHAR(20) DEFAULT NULL COMMENT '专业大类代码' AFTER \`professional_category\`
        `;

        try {
            await connection.query(alterSQL);
            console.log('✅ 字段添加成功\n');
        } catch (alterError) {
            if (alterError.code === 'ER_DUP_FIELDNAME') {
                console.log('⚠️  字段已存在，跳过添加\n');
            } else {
                throw alterError;
            }
        }

        // 验证字段添加
        console.log('🔍 验证字段添加...');
        const [rows] = await connection.query(`
            SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = 'ddcz_bitmap'
            AND TABLE_NAME = 'user_profiles'
            AND COLUMN_NAME IN ('professional_category', 'professional_category_code')
        `);

        if (rows.length === 2) {
            console.log('✅ 验证成功！新增字段如下：\n');
            rows.forEach(row => {
                console.log(`   - ${row.COLUMN_NAME}: ${row.COLUMN_TYPE}`);
                console.log(`     注释: ${row.COLUMN_COMMENT}\n`);
            });
        } else {
            console.log(`⚠️  警告：预期找到2个字段，实际找到 ${rows.length} 个`);
        }

        console.log('🎉 数据库迁移完成！\n');

        // 显示当前user_profiles表的所有字段
        console.log('📋 user_profiles 表当前所有字段：\n');
        const [columns] = await connection.query(`
            SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = 'ddcz_bitmap'
            AND TABLE_NAME = 'user_profiles'
            ORDER BY ORDINAL_POSITION
        `);

        columns.forEach(col => {
            console.log(`   ${col.COLUMN_NAME.padEnd(30)} ${col.COLUMN_TYPE.padEnd(20)} ${col.COLUMN_COMMENT || ''}`);
        });

    } catch (error) {
        console.error('\n❌ 迁移失败：', error.message);

        if (error.code === 'ER_DUP_FIELDNAME') {
            console.log('\n💡 提示：字段已存在，无需重复添加');
        } else if (error.code === 'ECONNREFUSED') {
            console.log('\n💡 提示：无法连接到数据库，请检查：');
            console.log('   1. 数据库服务器是否运行');
            console.log('   2. 网络连接是否正常');
            console.log('   3. 数据库配置是否正确');
        } else {
            console.log('\n💡 提示：请检查错误信息并修正后重试');
        }

        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('\n🔌 数据库连接已关闭');
        }
    }
}

// 执行迁移
console.log('='.repeat(60));
console.log('数据库迁移：添加专业大类字段');
console.log('='.repeat(60));

runMigration();