setup-database.cjs

#!/usr/bin/env node

/**
 * Azure PostgreSQL Database Setup Script
 * Creates application database and user
 */

require('dotenv').config();
const { Client } = require('pg');

async function setupDatabase() {
    console.log('🗄️ Setting up Azure PostgreSQL Database\n');
    console.log('=' .repeat(60));
    
    // Get admin credentials from config
    const adminConfig = {
        host: process.env.DB_HOST || 'ai-answer-ninja-postgres.postgres.database.azure.com',
        port: parseInt(process.env.DB_PORT || '5432'),
        database: 'postgres', // Connect to default postgres database first
        user: process.env.DB_ADMIN_USER || 'ai_ninja_admin',
        password: process.env.DB_ADMIN_PASSWORD,
        ssl: { rejectUnauthorized: false }
    };
    
    const appDatabase = process.env.DB_NAME || 'ai_ninja_db';
    const appUser = process.env.DB_USER || 'ai_ninja';
    const appPassword = 'AiNinja2024App!'; // Generate a secure password
    
    console.log('📋 Setup Configuration:');
    console.log('-'.repeat(30));
    console.log(`Host: ${adminConfig.host}`);
    console.log(`Admin User: ${adminConfig.user}`);
    console.log(`Target Database: ${appDatabase}`);
    console.log(`App User: ${appUser}`);
    console.log(`App Password: ${'*'.repeat(8)}${appPassword.slice(-4)}`);
    
    if (!adminConfig.password) {
        console.log('❌ Admin password not found in configuration');
        console.log('   Please update DB_ADMIN_PASSWORD in azure-database-config.env');
        return false;
    }
    
    console.log('\n🔄 Connecting to PostgreSQL server...');
    
    const adminClient = new Client(adminConfig);
    
    try {
        await adminClient.connect();
        console.log('✅ Connected to PostgreSQL server as admin');
        
        // Step 1: Check if database exists
        console.log('\n📊 Checking existing databases...');
        const dbCheckResult = await adminClient.query(`
            SELECT datname FROM pg_database WHERE datname = $1
        `, [appDatabase]);
        
        if (dbCheckResult.rows.length > 0) {
            console.log(`✅ Database '${appDatabase}' already exists`);
        } else {
            console.log(`🔨 Creating database '${appDatabase}'...`);
            await adminClient.query(`
                CREATE DATABASE ${appDatabase} 
                WITH ENCODING 'UTF8' 
                LC_COLLATE = 'en_US.UTF-8' 
                LC_CTYPE = 'en_US.UTF-8'
                TEMPLATE template0
            `);
            console.log(`✅ Database '${appDatabase}' created successfully`);
        }
        
        // Step 2: Check if user exists
        console.log('\n👤 Checking existing users...');
        const userCheckResult = await adminClient.query(`
            SELECT usename FROM pg_user WHERE usename = $1
        `, [appUser]);
        
        if (userCheckResult.rows.length > 0) {
            console.log(`✅ User '${appUser}' already exists`);
            console.log('🔄 Updating user password...');
            await adminClient.query(`ALTER USER ${appUser} WITH PASSWORD $1`, [appPassword]);
            console.log('✅ User password updated');
        } else {
            console.log(`🔨 Creating user '${appUser}'...`);
            await adminClient.query(`
                CREATE USER ${appUser} WITH PASSWORD $1
            `, [appPassword]);
            console.log(`✅ User '${appUser}' created successfully`);
        }
        
        // Step 3: Grant permissions on database
        console.log('\n🔐 Setting up permissions...');
        await adminClient.query(`GRANT ALL PRIVILEGES ON DATABASE ${appDatabase} TO ${appUser}`);
        console.log(`✅ Granted database privileges to '${appUser}'`);
        
        await adminClient.end();
        
        // Step 4: Connect to app database and set schema permissions
        console.log('\n🔄 Connecting to application database...');
        
        const appDbConfig = {
            ...adminConfig,
            database: appDatabase
        };
        
        const appDbClient = new Client(appDbConfig);
        await appDbClient.connect();
        console.log(`✅ Connected to database '${appDatabase}'`);
        
        // Grant schema permissions
        await appDbClient.query(`GRANT ALL ON SCHEMA public TO ${appUser}`);
        await appDbClient.query(`GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${appUser}`);
        await appDbClient.query(`GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${appUser}`);
        
        // Set default privileges for future objects
        await appDbClient.query(`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO ${appUser}`);
        await appDbClient.query(`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO ${appUser}`);
        
        console.log('✅ Schema permissions configured');
        
        // Step 5: Test application user connection
        console.log('\n🧪 Testing application user connection...');
        
        await appDbClient.end();
        
        const testConfig = {
            host: adminConfig.host,
            port: adminConfig.port,
            database: appDatabase,
            user: appUser,
            password: appPassword,
            ssl: { rejectUnauthorized: false }
        };
        
        const testClient = new Client(testConfig);
        await testClient.connect();
        
        // Test basic operations
        const versionResult = await testClient.query('SELECT version()');
        console.log(`✅ App user connection successful`);
        console.log(`   PostgreSQL: ${versionResult.rows[0].version.split(' ')[1]}`);
        
        // Test table creation permissions
        await testClient.query(`
            CREATE TABLE IF NOT EXISTS connection_test (
                id SERIAL PRIMARY KEY,
                test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        await testClient.query(`
            INSERT INTO connection_test DEFAULT VALUES
        `);
        
        const testResult = await testClient.query(`
            SELECT COUNT(*) as test_count FROM connection_test
        `);
        
        console.log(`✅ Database write permissions verified (${testResult.rows[0].test_count} test records)`);
        
        // Clean up test table
        await testClient.query('DROP TABLE IF EXISTS connection_test');
        
        await testClient.end();
        
        console.log('\n' + '=' .repeat(60));
        console.log('\n🎉 Database setup completed successfully!');
        
        console.log('\n📝 Application Configuration:');
        console.log(`DB_HOST=${adminConfig.host}`);
        console.log(`DB_PORT=${adminConfig.port}`);
        console.log(`DB_NAME=${appDatabase}`);
        console.log(`DB_USER=${appUser}`);
        console.log(`DB_PASSWORD=${appPassword}`);
        console.log(`DB_SSL=true`);
        
        console.log('\n📋 Next Steps:');
        console.log('1. Update your .env file with the application configuration above');
        console.log('2. Run database migrations to create tables');
        console.log('3. Test the connection with: node test-database-config.cjs');
        console.log('4. Seed initial data if needed');
        
        return {
            success: true,
            config: {
                host: adminConfig.host,
                port: adminConfig.port,
                database: appDatabase,
                user: appUser,
                password: appPassword
            }
        };
        
    } catch (error) {
        console.error('❌ Database setup failed:', error.message);
        
        if (error.code === 'ENOTFOUND') {
            console.log('   Check server name and network connectivity');
        } else if (error.code === 'ECONNREFUSED') {
            console.log('   Check firewall rules and server status');
        } else if (error.code === '28P01') {
            console.log('   Check admin username and password');
        } else if (error.code === '3D000') {
            console.log('   Database connection issue');
        } else {
            console.log(`   Error Code: ${error.code}`);
        }
        
        return { success: false, error: error.message };
        
    } finally {
        try {
            await adminClient.end();
        } catch (e) {
            // Client might already be closed
        }
    }
}

// Run setup
setupDatabase().then(result => {
    if (result.success) {
        console.log('\n✅ Database setup completed! Ready for migrations.');
        process.exit(0);
    } else {
        console.log('\n❌ Database setup failed. Please check configuration.');
        process.exit(1);
    }
}).catch(error => {
    console.error('\n💥 Setup script failed:', error.message);
    process.exit(1);
});