akmon/push-receiver-service/setup-database.js

/**
 * 数据库初始化脚本
 * 自动创建数据库表结构和初始数据
 */

const DatabaseManager = require('./lib/database');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

class DatabaseSetup {
    constructor() {
        this.db = new DatabaseManager();
    }
    
    async setup() {
        try {
            console.log('🔧 开始数据库初始化...');
            
            // 测试数据库连接
            console.log('📡 测试数据库连接...');
            await this.db.testConnection();
            console.log('✅ 数据库连接成功');
            
            // 读取并执行初始化SQL
            console.log('📋 执行数据库初始化脚本...');
            const sqlPath = path.join(__dirname, 'database', 'init.sql');
            
            if (fs.existsSync(sqlPath)) {
                const initSQL = fs.readFileSync(sqlPath, 'utf8');
                
                // 将SQL分割成多个语句执行
                const statements = initSQL
                    .split(';')
                    .filter(stmt => stmt.trim().length > 0)
                    .map(stmt => stmt.trim() + ';');
                
                const client = await this.db.pool.connect();
                
                try {
                    for (let i = 0; i < statements.length; i++) {
                        const statement = statements[i];
                        if (statement.trim() === ';') continue;
                        
                        try {
                            await client.query(statement);
                            console.log(`✅ 执行语句 ${i + 1}/${statements.length}`);
                        } catch (error) {
                            // 跳过一些可能的错误（如表已存在等）
                            if (error.message.includes('already exists') || 
                                error.message.includes('duplicate key')) {
                                console.log(`⚠️  跳过语句 ${i + 1}/${statements.length}: ${error.message}`);
                            } else {
                                throw error;
                            }
                        }
                    }
                } finally {
                    client.release();
                }
                
                console.log('✅ 数据库初始化脚本执行完成');
            } else {
                console.log('❌ 未找到初始化SQL文件:', sqlPath);
            }
            
            // 验证表结构
            console.log('🔍 验证数据库表结构...');
            await this.verifyTables();
            
            // 插入测试数据
            console.log('📝 插入测试数据...');
            await this.insertTestData();
            
            console.log('🎉 数据库初始化完成!');
            
        } catch (error) {
            console.error('❌ 数据库初始化失败:', error.message);
            throw error;
        } finally {
            await this.db.close();
        }
    }
    
    async verifyTables() {
        const expectedTables = [
            'ps_push_messages',
            'ps_push_types',
            'message_processing_logs',
            'devices',
            'users',
            'system_stats'
        ];
        
        const client = await this.db.pool.connect();
        
        try {
            const result = await client.query(`
                SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema = 'public' 
                AND table_type = 'BASE TABLE'
                ORDER BY table_name
            `);
            
            const existingTables = result.rows.map(row => row.table_name);
            
            console.log('📊 现有数据表:');
            existingTables.forEach(table => {
                const exists = expectedTables.includes(table);
                console.log(`   ${exists ? '✅' : '❓'} ${table}`);
            });
            
            const missingTables = expectedTables.filter(table => !existingTables.includes(table));
            if (missingTables.length > 0) {
                console.log('❌ 缺少数据表:', missingTables.join(', '));
                throw new Error(`缺少必要的数据表: ${missingTables.join(', ')}`);
            }
            
            console.log('✅ 所有必要数据表已存在');
            
        } finally {
            client.release();
        }
    }
    
    async insertTestData() {
        const client = await this.db.pool.connect();
        
        try {
            // 插入测试推送消息
            const testMessages = [
                {
                    push_type: 'HEALTH',
                    user_id: 'test_user_001',
                    device_id: 'device_001',
                    raw_data: {
                        pushType: 'HEALTH',
                        userId: 'test_user_001',
                        H: 75,
                        O: 98,
                        T: 36.5
                    }
                },
                {
                    push_type: 'SOS',
                    user_id: 'test_user_002',
                    device_id: 'device_002',
                    raw_data: {
                        pushType: 'SOS',
                        userId: 'test_user_002',
                        emergencyLevel: 'HIGH',
                        location: {
                            lat: 39.9042,
                            lng: 116.4074
                        }
                    }
                },
                {
                    push_type: 'LOCATION',
                    user_id: 'test_user_003',
                    device_id: 'device_003',
                    raw_data: {
                        pushType: 'LOCATION',
                        userId: 'test_user_003',
                        lat: 31.2304,
                        lng: 121.4737,
                        accuracy: 10
                    }
                }
            ];
            
            for (const message of testMessages) {
                const checksum = require('crypto')
                    .createHash('sha256')
                    .update(JSON.stringify(message.raw_data))
                    .digest('hex');
                
                await client.query(`
                    INSERT INTO ps_push_messages (
                        push_type, user_id, device_id, raw_data, parsed_data, 
                        checksum, priority, processing_status
                    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
                    ON CONFLICT (checksum) DO NOTHING
                `, [
                    message.push_type,
                    message.user_id,
                    message.device_id,
                    JSON.stringify(message.raw_data),
                    JSON.stringify(message.raw_data),
                    checksum,
                    message.push_type === 'SOS' ? 1 : 5,
                    'processed'
                ]);
            }
            
            console.log('✅ 测试数据插入完成');
            
        } finally {
            client.release();
        }
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    const setup = new DatabaseSetup();
    setup.setup()
        .then(() => {
            console.log('🎉 数据库设置完成，可以启动服务了！');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 数据库设置失败:', error.message);
            process.exit(1);
        });
}

module.exports = DatabaseSetup;