1  #!/usr/bin/env node
  2  
  3  /**
  4   * Reset sites with status='failing' back to their appropriate stage for retry.
  5   *
  6   * The chromium-nice bug has been fixed, so these sites should be retried.
  7   * Sites are reset to the stage where they failed, with error_message cleared.
  8   */
  9  
 10  import { createDatabaseConnection } from '../src/utils/db.js';
 11  import { fileURLToPath } from 'url';
 12  import { dirname, join } from 'path';
 13  
 14  const __filename = fileURLToPath(import.meta.url);
 15  const __dirname = dirname(__filename);
 16  
 17  const dbPath = process.env.DATABASE_PATH || join(__dirname, '../db/sites.db');
 18  const db = createDatabaseConnection(dbPath);
 19  
 20  console.log('🔍 Analyzing failing sites...\n');
 21  
 22  // Get failing sites grouped by their error patterns
 23  const failingSites = db
 24    .prepare(
 25      `SELECT
 26        id,
 27        domain,
 28        status,
 29        error_message,
 30        retry_count,
 31        last_retry_at
 32      FROM sites
 33      WHERE status = 'failing'
 34      ORDER BY error_message`
 35    )
 36    .all();
 37  
 38  if (failingSites.length === 0) {
 39    console.log('✅ No sites with status="failing" found.');
 40    db.close();
 41    process.exit(0);
 42  }
 43  
 44  console.log(`Found ${failingSites.length} sites with status='failing':\n`);
 45  
 46  // Group by error message
 47  const errorGroups = {};
 48  failingSites.forEach(site => {
 49    const errorKey = site.error_message || 'Unknown error';
 50    if (!errorGroups[errorKey]) {
 51      errorGroups[errorKey] = [];
 52    }
 53    errorGroups[errorKey].push(site);
 54  });
 55  
 56  // Display grouped errors
 57  Object.entries(errorGroups).forEach(([error, sites]) => {
 58    console.log(`📋 Error: ${error}`);
 59    console.log(`   Sites (${sites.length}): ${sites.map(s => s.domain).join(', ')}`);
 60    console.log();
 61  });
 62  
 63  // Determine target status based on error pattern
 64  function getTargetStatus(errorMessage) {
 65    if (!errorMessage) return 'found';
 66  
 67    if (
 68      errorMessage.includes('Cannot capture assets') ||
 69      errorMessage.includes('browserType.launch')
 70    ) {
 71      return 'found'; // Retry from Assets stage
 72    }
 73    if (errorMessage.includes('status code 400')) {
 74      return 'found'; // Retry from Assets stage (likely ZenRows issue)
 75    }
 76    if (errorMessage.includes('Breaker is open')) {
 77      return 'assets_captured'; // Retry from Scoring stage
 78    }
 79    if (errorMessage.includes('Invalid proposal') || errorMessage.includes('CHECK constraint')) {
 80      return 'enriched'; // Retry from Proposals stage
 81    }
 82    if (errorMessage.includes('UNIQUE constraint')) {
 83      return 'proposals_drafted'; // Retry from Outreach stage
 84    }
 85  
 86    // Default: go back to 'found' to restart from Assets
 87    return 'found';
 88  }
 89  
 90  console.log('🔄 Reset Plan:\n');
 91  
 92  const resetPlan = failingSites.map(site => {
 93    const targetStatus = getTargetStatus(site.error_message);
 94    return {
 95      id: site.id,
 96      domain: site.domain,
 97      currentStatus: site.status,
 98      targetStatus,
 99      errorMessage: site.error_message,
100    };
101  });
102  
103  // Group by target status
104  const resetGroups = {};
105  resetPlan.forEach(plan => {
106    if (!resetGroups[plan.targetStatus]) {
107      resetGroups[plan.targetStatus] = [];
108    }
109    resetGroups[plan.targetStatus].push(plan);
110  });
111  
112  Object.entries(resetGroups).forEach(([targetStatus, plans]) => {
113    console.log(`➡️  Reset ${plans.length} sites to status='${targetStatus}'`);
114    plans.forEach(p => {
115      console.log(`   - ${p.domain}: ${p.errorMessage?.substring(0, 80) || 'Unknown error'}`);
116    });
117    console.log();
118  });
119  
120  // Confirm before proceeding
121  console.log('⚠️  This will:');
122  console.log('   1. Change status from "failing" to appropriate stage');
123  console.log('   2. Clear error_message to allow retry');
124  console.log('   3. Reset retry_count to 0');
125  console.log('   4. Log the status change in site_status table\n');
126  
127  const dryRun = process.argv.includes('--dry-run');
128  
129  if (dryRun) {
130    console.log('🔍 DRY RUN - No changes made. Run without --dry-run to apply changes.');
131    db.close();
132    process.exit(0);
133  }
134  
135  // Execute reset
136  console.log('🚀 Applying reset...\n');
137  
138  const updateStmt = db.prepare(`
139    UPDATE sites
140    SET
141      status = ?,
142      error_message = NULL,
143      retry_count = 0,
144      last_retry_at = NULL,
145      updated_at = CURRENT_TIMESTAMP
146    WHERE id = ?
147  `);
148  
149  const logStatusStmt = db.prepare(`
150    INSERT INTO site_status (site_id, status, error_message)
151    VALUES (?, ?, ?)
152  `);
153  
154  db.transaction(() => {
155    resetPlan.forEach(plan => {
156      updateStmt.run(plan.targetStatus, plan.id);
157      logStatusStmt.run(
158        plan.id,
159        plan.targetStatus,
160        'Reset from failing status after chromium-nice fix'
161      );
162      console.log(`✅ Reset ${plan.domain}: failing → ${plan.targetStatus}`);
163    });
164  })();
165  
166  console.log(`\n✨ Successfully reset ${failingSites.length} sites.`);
167  console.log('\n📊 Next steps:');
168  console.log('   - Sites with status="found" will retry on: npm run assets');
169  console.log('   - Sites with status="assets_captured" will retry on: npm run scoring');
170  console.log('   - Sites with status="enriched" will retry on: npm run proposals');
171  console.log('   - Sites with status="proposals_drafted" will retry on: npm run outreach');
172  
173  db.close();