1  #!/usr/bin/env node
  2  /**
  3   * Pipeline Progress Report - Shows current pipeline status and recent activity
  4   * Usage: node scripts/pipeline-progress-report.js [hours]
  5   * Default: Last 24 hours
  6   */
  7  
  8  import { createDatabaseConnection } from '../src/utils/db.js';
  9  import { fileURLToPath } from 'url';
 10  import { dirname, join } from 'path';
 11  
 12  const __filename = fileURLToPath(import.meta.url);
 13  const __dirname = dirname(__filename);
 14  const db = createDatabaseConnection(process.env.DATABASE_PATH || join(__dirname, '../db/sites.db'));
 15  
 16  const hoursAgo = parseInt(process.argv[2]) || 24;
 17  const since = new Date(Date.now() - hoursAgo * 60 * 60 * 1000)
 18    .toISOString()
 19    .slice(0, 19)
 20    .replace('T', ' ');
 21  
 22  console.log(`\n📊 Pipeline Progress Report (Last ${hoursAgo} hours)\n`);
 23  console.log(`Generated: ${new Date().toLocaleString()}`);
 24  console.log(`Since: ${since}\n`);
 25  
 26  // Current status breakdown
 27  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
 28  console.log('📋 CURRENT STATUS');
 29  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n');
 30  
 31  const currentStatus = db
 32    .prepare(
 33      `
 34    SELECT
 35      status,
 36      COUNT(*) as count,
 37      ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sites), 2) as percentage
 38    FROM sites
 39    GROUP BY status
 40    ORDER BY count DESC
 41  `
 42    )
 43    .all();
 44  
 45  currentStatus.forEach(row => {
 46    const bar = '█'.repeat(Math.floor(row.percentage / 2));
 47    const emoji = getStatusEmoji(row.status);
 48    console.log(
 49      `${emoji} ${row.status.padEnd(20)} ${String(row.count).padStart(6)} (${row.percentage}%) ${bar}`
 50    );
 51  });
 52  
 53  // Recent activity (sites that changed status)
 54  console.log('\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
 55  console.log(`🚀 RECENT ACTIVITY (Last ${hoursAgo}h)`);
 56  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n');
 57  
 58  const recentActivity = db
 59    .prepare(
 60      `
 61    SELECT
 62      status,
 63      COUNT(*) as count
 64    FROM sites
 65    WHERE updated_at > ?
 66    GROUP BY status
 67    ORDER BY count DESC
 68  `
 69    )
 70    .all(since);
 71  
 72  if (recentActivity.length > 0) {
 73    recentActivity.forEach(row => {
 74      const emoji = getStatusEmoji(row.status);
 75      console.log(`${emoji} ${row.status.padEnd(20)} ${row.count} sites updated`);
 76    });
 77  } else {
 78    console.log(`⚠️  No activity in the last ${hoursAgo} hours`);
 79  }
 80  
 81  // Ready for next stage
 82  console.log('\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
 83  console.log('✅ READY FOR PROCESSING');
 84  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n');
 85  
 86  const readyStages = [
 87    { status: 'found', nextStage: 'assets (screenshot capture)', command: 'npm run assets' },
 88    { status: 'assets_captured', nextStage: 'scoring', command: 'npm run scoring' },
 89    { status: 'prog_scored', nextStage: 'rescoring', command: 'npm run rescoring' },
 90    { status: 'semantic_scored', nextStage: 'enrichment', command: 'npm run enrich' },
 91    { status: 'vision_scored', nextStage: 'enrichment', command: 'npm run enrich' },
 92    { status: 'enriched', nextStage: 'proposals', command: 'npm run proposals' },
 93    {
 94      status: 'proposals_drafted',
 95      nextStage: 'outreach',
 96      command: 'npm run outreach:export (QA first)',
 97    },
 98  ];
 99  
100  readyStages.forEach(({ status, nextStage, command }) => {
101    const count = currentStatus.find(s => s.status === status)?.count || 0;
102    if (count > 0) {
103      console.log(`🎯 ${String(count).padStart(6)} sites ready for ${nextStage}`);
104      console.log(`   Command: ${command}\n`);
105    }
106  });
107  
108  // Failing sites that need attention
109  const failingCount = currentStatus.find(s => s.status === 'failing')?.count || 0;
110  if (failingCount > 0) {
111    console.log('\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
112    console.log('⚠️  FAILING SITES');
113    console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n');
114  
115    const failureReasons = db
116      .prepare(
117        `
118      SELECT
119        CASE
120          WHEN error_message LIKE '%403%' THEN 'HTTP 403 Forbidden'
121          WHEN error_message LIKE '%404%' THEN 'HTTP 404 Not Found'
122          WHEN error_message LIKE '%timeout%' THEN 'Timeout'
123          WHEN error_message LIKE '%breaker%' THEN 'Circuit Breaker Open'
124          ELSE 'Other'
125        END as error_type,
126        COUNT(*) as count
127      FROM sites
128      WHERE status = 'failing'
129      GROUP BY error_type
130      ORDER BY count DESC
131    `
132      )
133      .all();
134  
135    failureReasons.forEach(row => {
136      console.log(`   ${row.error_type.padEnd(25)} ${row.count} sites`);
137    });
138  
139    const nextRetry = db
140      .prepare(
141        `
142      SELECT MIN(recapture_at) as next_retry
143      FROM sites
144      WHERE status = 'failing' AND recapture_at IS NOT NULL
145    `
146      )
147      .get();
148  
149    if (nextRetry?.next_retry) {
150      console.log(`\n   Next retry scheduled: ${nextRetry.next_retry}`);
151    }
152  }
153  
154  // Outreach stats (if any sent)
155  console.log('\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
156  console.log('📧 OUTREACH STATUS');
157  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n');
158  
159  const outreachStats = db
160    .prepare(
161      `
162    SELECT
163      contact_method,
164      status,
165      COUNT(*) as count
166    FROM outreaches
167    WHERE created_at > ?
168    GROUP BY contact_method, status
169    ORDER BY contact_method, status
170  `
171    )
172    .all(since);
173  
174  if (outreachStats.length > 0) {
175    outreachStats.forEach(row => {
176      const emoji = getDeliveryEmoji(row.status);
177      console.log(`${emoji} ${row.contact_method.padEnd(10)} ${row.status.padEnd(15)} ${row.count}`);
178    });
179  } else {
180    console.log(`   No outreach activity in the last ${hoursAgo} hours`);
181  }
182  
183  // Conversation stats
184  const conversationStats = db
185    .prepare(
186      `
187    SELECT
188      intent,
189      COUNT(*) as count
190    FROM conversations
191    WHERE received_at > ?
192    GROUP BY intent
193    ORDER BY count DESC
194  `
195    )
196    .all(since);
197  
198  if (conversationStats.length > 0) {
199    console.log('\n💬 Recent Conversations:\n');
200    conversationStats.forEach(row => {
201      const emoji = getIntentEmoji(row.intent);
202      console.log(`${emoji} ${row.intent.padEnd(20)} ${row.count}`);
203    });
204  }
205  
206  console.log('\n━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\n');
207  
208  db.close();
209  
210  function getStatusEmoji(status) {
211    const emojiMap = {
212      ignore: '🚫',
213      found: '🔍',
214      failing: '❌',
215      assets_captured: '📸',
216      scored: '📊',
217      rescored: '🔄',
218      enriched: '✨',
219      proposals_drafted: '📝',
220      outreach_sent: '📤',
221    };
222    return emojiMap[status] || '📌';
223  }
224  
225  function getDeliveryEmoji(status) {
226    const emojiMap = {
227      pending: '⏳',
228      sent: '📤',
229      delivered: '✅',
230      failed: '❌',
231      bounced: '⚠️',
232    };
233    return emojiMap[status] || '📧';
234  }
235  
236  function getIntentEmoji(intent) {
237    const emojiMap = {
238      interested: '✅',
239      not_interested: '❌',
240      question: '❓',
241      complaint: '😠',
242      neutral: '➖',
243    };
244    return emojiMap[intent] || '💬';
245  }