152 lines
5.1 KiB
JavaScript
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);
|