#!/usr/bin/env node /** * PollCelo Database Setup Script * * Sets up the production Neon PostgreSQL database with proper schema * * Usage: * node scripts/setup-db.js * * Requires: * DATABASE_URL environment variable * DATABASE_URL should be in format: * postgresql://username:password@hostname:5432/database_name?sslmode=require */ const { readFileSync } = require('fs'); const { Client } = require('pg'); require('dotenv').config({ path: '.env.local' }); const SCHEMA_PATH = './database/schema.sql'; async function setupDatabase() { console.log('๐Ÿš€ Setting up PollCelo Production Database...\n'); // Check environment variables if (!process.env.DATABASE_URL) { console.error('โŒ DATABASE_URL environment variable not set!'); console.error('Please set your Neon database URL in .env.local:'); console.error('DATABASE_URL=postgresql://username:password@hostname:5432/database_name?sslmode=require\n'); process.exit(1); } const client = new Client({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } }); try { // Connect to database console.log('๐Ÿ“ก Connecting to database...'); await client.connect(); console.log('โœ… Connected successfully!\n'); // Read schema file console.log('๐Ÿ“– Reading database schema...'); const schema = readFileSync(SCHEMA_PATH, 'utf8'); console.log('โœ… Schema loaded!\n'); // Execute schema console.log('๐Ÿ—๏ธ Creating database schema...'); // Split schema into individual statements const statements = schema .split(';') .map(stmt => stmt.trim()) .filter(stmt => stmt.length > 0 && !stmt.startsWith('--')); for (const statement of statements) { try { await client.query(statement + ';'); } catch (error) { // Ignore errors for statements that might already exist if (error.message.includes('already exists') || error.message.includes('duplicate key') || error.message.includes('already defined')) { console.log('โš ๏ธ Statement already applied, skipping...'); } else { console.error(`โŒ Failed to execute statement: ${error.message}`); throw error; } } } console.log('โœ… Database schema created successfully!\n'); // Create initial poll (if none exists) console.log('๐ŸŒฑ Creating initial poll...'); const { rows: existingPolls } = await client.query('SELECT COUNT(*) FROM polls'); if (parseInt(existingPolls[0].count) === 0) { const insertPollQuery = ` INSERT INTO polls ( creator_fid, creator_address, question, description, emoji, start_time, end_time, status, verification_type, verification_config, creation_fee, created_at ) VALUES ( 1, '0x742d35Cc6634C0532925a3b844Bc454e4438f44e', 'Will BTC hit $120,000 this week?', 'Bitcoin price prediction for the upcoming week', 'โ‚ฟ', $1, $2, 'active', 'chainlink', '{"feedAddress": "0x898E82a89c21469df34A8A9a36D1873f74Be3A1D", "targetPrice": 120000, "comparisonType": "above"}'::jsonb, 5, NOW() ) RETURNING id `; const now = Date.now(); const endTime = now + (24 * 60 * 60 * 1000); // 24 hours from now const { rows: pollResult } = await client.query(insertPollQuery, [now, endTime]); const pollId = pollResult[0].id; // Insert poll options const insertOptionsQuery = ` INSERT INTO poll_options (poll_id, text, emoji, position) VALUES ($1, 'Yes, Bitcoin will hit $120K', '๐Ÿš€', 0), ($1, 'No, Bitcoin will stay below $120K', '๐Ÿ“‰', 1) `; await client.query(insertOptionsQuery, [pollId]); console.log('โœ… Initial poll created!'); console.log(`๐Ÿ“Š Poll ID: ${pollId}`); } else { console.log('โœ… Database already contains polls, skipping initial data'); } // Test queries console.log('\n๐Ÿงช Testing database queries...'); const pollCount = await client.query('SELECT COUNT(*) FROM polls'); console.log(`๐Ÿ“Š Total polls: ${pollCount.rows[0].count}`); const voteCount = await client.query('SELECT COUNT(*) FROM votes'); console.log(`๐Ÿ—ณ๏ธ Total votes: ${voteCount.rows[0].count}`); console.log('\n๐ŸŽ‰ Database setup completed successfully!'); console.log('\n๐Ÿ“‹ Next steps:'); console.log('1. Set up your Vercel environment variables'); console.log('2. Deploy the application'); console.log('3. Test the oracle verification system'); console.log('4. Create your first real poll!\n'); } catch (error) { console.error('โŒ Database setup failed:', error.message); console.error('\n๐Ÿ”ง Troubleshooting:'); console.error('- Check your DATABASE_URL is correct'); console.error('- Ensure your Neon database is running'); console.error('- Verify SSL settings for Neon PostgreSQL'); console.error('- Check network connectivity\n'); process.exit(1); } finally { await client.end(); } } // Run setup setupDatabase().catch(console.error);