extract_data.js

// Extract key data from pulse-world.db for Oracle Engine analysis
// Run: node extract_data.js

const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

const DB_PATH = path.join(__dirname, 'data', 'pulse-world.db');
const OUT_DIR = path.join(__dirname, 'exports');

// Ensure output dir exists
if (!fs.existsSync(OUT_DIR)) fs.mkdirSync(OUT_DIR, { recursive: true });

const db = new Database(DB_PATH, { readonly: true });

function exportQuery(filename, sql) {
  console.log(`Exporting ${filename}...`);
  const rows = db.prepare(sql).all();
  fs.writeFileSync(path.join(OUT_DIR, filename), JSON.stringify(rows, null, 2));
  console.log(`  → ${rows.length} rows`);
  return rows.length;
}

// 1. Global stats
console.log('\n=== PULSE WORLD DATA EXTRACTION ===\n');

const tables = db.prepare(`
  SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
`).all();
console.log('Tables:', tables.map(t => t.name).join(', '));

for (const t of tables) {
  const count = db.prepare(`SELECT COUNT(*) as c FROM "${t.name}"`).get();
  console.log(`  ${t.name}: ${count.c} rows`);
}

// 2. Events - non-duplicates only, with key fields
exportQuery('events_unique.json', `
  SELECT id, source, source_name, title, entity_ids, categories, regions, country,
         sentiment, language, published_at, collected_at
  FROM events
  WHERE duplicate_of IS NULL
  ORDER BY published_at DESC
  LIMIT 5000
`);

// 3. All correlations
exportQuery('correlations.json', `
  SELECT c.*, e.title as event_title, e.published_at as event_published_at
  FROM correlations c
  LEFT JOIN events e ON c.event_id = e.id
  ORDER BY c.detected_at DESC
`);

// 4. All market anomalies
exportQuery('market_anomalies.json', `
  SELECT * FROM market_anomalies
  ORDER BY detected_at DESC
`);

// 5. AI analysis with event context - top impact
exportQuery('ai_analysis.json', `
  SELECT a.*, e.title as event_title, e.entity_ids, e.categories, e.regions, e.published_at
  FROM ai_analysis a
  LEFT JOIN events e ON a.event_id = e.id
  ORDER BY a.impact_score DESC
  LIMIT 2000
`);

// 6. Market daily for trend analysis
exportQuery('market_daily.json', `
  SELECT * FROM market_daily
  ORDER BY date DESC, ticker
`);

// 7. Fear & Greed history
exportQuery('fear_greed.json', `
  SELECT * FROM fear_greed
  ORDER BY collected_at DESC
`);

// 8. Wiki activity
exportQuery('wiki_activity.json', `
  SELECT * FROM wiki_activity
  ORDER BY edit_count DESC
`);

// 9. Crypto prices
exportQuery('crypto_prices.json', `
  SELECT * FROM crypto_prices
  ORDER BY collected_at DESC
  LIMIT 5000
`);

// 10. Hourly summaries
exportQuery('hourly_summaries.json', `
  SELECT * FROM hourly_summaries
  ORDER BY hour_key DESC
`);

// 11. Aggregate stats
exportQuery('stats_by_category.json', `
  SELECT categories, COUNT(*) as count, AVG(sentiment) as avg_sentiment
  FROM events
  WHERE duplicate_of IS NULL AND categories IS NOT NULL
  GROUP BY categories
  ORDER BY count DESC
  LIMIT 50
`);

exportQuery('stats_by_entity.json', `
  SELECT entity_ids, COUNT(*) as count, AVG(sentiment) as avg_sentiment
  FROM events
  WHERE duplicate_of IS NULL AND entity_ids IS NOT NULL AND entity_ids != '[]'
  GROUP BY entity_ids
  ORDER BY count DESC
  LIMIT 100
`);

exportQuery('stats_by_source.json', `
  SELECT source, source_name, COUNT(*) as count
  FROM events
  WHERE duplicate_of IS NULL
  GROUP BY source, source_name
  ORDER BY count DESC
`);

exportQuery('events_timeline.json', `
  SELECT 
    date(published_at) as day,
    COUNT(*) as total_events,
    SUM(CASE WHEN duplicate_of IS NULL THEN 1 ELSE 0 END) as unique_events,
    AVG(CASE WHEN duplicate_of IS NULL THEN sentiment END) as avg_sentiment
  FROM events
  WHERE published_at IS NOT NULL
  GROUP BY date(published_at)
  ORDER BY day
`);

db.close();
console.log('\n✅ Done! Check the exports/ folder');