celo-poll/scripts/setup-db.js
2025-12-08 16:31:30 +03:00

152 lines
5.1 KiB
JavaScript

#!/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);