1  #!/usr/bin/env node
  2  
  3  /**
  4   * Database Initialization Script
  5   * Creates SQLite database and populates config table with .env values
  6   */
  7  
  8  import { createDatabaseConnection } from '../src/utils/db.js';
  9  import { readFileSync } from 'fs';
 10  import { fileURLToPath } from 'url';
 11  import { dirname, join } from 'path';
 12  import dotenv from 'dotenv';
 13  
 14  // Load environment variables
 15  dotenv.config();
 16  
 17  const __filename = fileURLToPath(import.meta.url);
 18  const __dirname = dirname(__filename);
 19  const projectRoot = join(__dirname, '..');
 20  
 21  const dbPath = process.env.DATABASE_PATH || './db/sites.db';
 22  const schemaPath = join(projectRoot, 'db', 'schema.sql');
 23  
 24  console.log('🔧 Initializing 333 Method Automation Database...\n');
 25  
 26  try {
 27    // Read schema SQL
 28    const schema = readFileSync(schemaPath, 'utf-8');
 29  
 30    // Create/open database
 31    const db = createDatabaseConnection(dbPath);
 32    db.pragma('journal_mode = WAL'); // Better concurrency
 33    db.pragma('foreign_keys = ON'); // Enforce foreign key constraints
 34  
 35    console.log('✅ Database file:', dbPath);
 36  
 37    // Execute schema
 38    db.exec(schema);
 39    console.log('✅ Schema created/verified');
 40  
 41    // Populate config table with values from .env
 42    const configData = [
 43      ['sender_name', process.env.SENDER_NAME || 'Your Name', 'Name for outreach'],
 44      ['sender_email', process.env.SENDER_EMAIL || 'you@example.com', 'Sender email address'],
 45      ['sender_phone', process.env.SENDER_PHONE || '+1234567890', 'Sender phone number'],
 46      ['sender_company', process.env.SENDER_COMPANY || 'Your Company', 'Company name'],
 47      [
 48        'sender_address',
 49        process.env.SENDER_ADDRESS || '123 Main St, City, State ZIP',
 50        'Physical address for CAN-SPAM',
 51      ],
 52      [
 53        'smtp_signature',
 54        `Best regards,\n${process.env.SENDER_NAME || 'Your Name'}`,
 55        'Email signature block',
 56      ],
 57    ];
 58  
 59    const insertConfig = db.prepare(`
 60      INSERT OR REPLACE INTO config (key, value, description) 
 61      VALUES (?, ?, ?)
 62    `);
 63  
 64    const insertMany = db.transaction(configs => {
 65      for (const config of configs) {
 66        insertConfig.run(config);
 67      }
 68    });
 69  
 70    insertMany(configData);
 71    console.log('✅ Config table populated with .env values');
 72  
 73    // Display stats
 74    const tableCount = db
 75      .prepare(
 76        `
 77      SELECT COUNT(*) as count 
 78      FROM sqlite_master 
 79      WHERE type='table' AND name NOT LIKE 'sqlite_%'
 80    `
 81      )
 82      .get();
 83  
 84    const indexCount = db
 85      .prepare(
 86        `
 87      SELECT COUNT(*) as count 
 88      FROM sqlite_master 
 89      WHERE type='index' AND name NOT LIKE 'sqlite_%'
 90    `
 91      )
 92      .get();
 93  
 94    console.log(`\n📊 Database Statistics:`);
 95    console.log(`   Tables: ${tableCount.count}`);
 96    console.log(`   Indexes: ${indexCount.count}`);
 97    console.log(`   Config entries: ${configData.length}`);
 98  
 99    db.close();
100  
101    console.log('\n✅ Database initialization complete!');
102    console.log('\n📝 Next steps:');
103    console.log('   1. Copy .env.example to .env and fill in API keys');
104    console.log('   2. Run: npm install');
105    console.log('   3. Run: npm run poc "plumber seattle"');
106  } catch (error) {
107    console.error('❌ Error initializing database:', error.message);
108    process.exit(1);
109  }