tenderpilot/verify-all-open.mjs

import pg from 'pg';

const pool = new pg.Pool({
  connectionString: 'postgresql://tenderpilot:jqrmilIBr6imtT0fKS01@localhost:5432/tenderpilot'
});

const delay = (ms) => new Promise(resolve => setTimeout(resolve, ms));

async function verifyAll() {
  try {
    console.log('Getting all open tenders...\n');
    
    const result = await pool.query(
      "SELECT id, title, notice_url, source FROM tenders WHERE status = 'open' ORDER BY source, id"
    );
    
    console.log('Found', result.rows.length, 'open tenders to verify\n');
    
    let working = 0;
    let broken = 0;
    const brokenUrls = [];
    
    for (let i = 0; i < result.rows.length; i++) {
      const tender = result.rows[i];
      const progress = (i + 1) + '/' + result.rows.length;
      
      try {
        const response = await fetch(tender.notice_url, {
          method: 'HEAD',
          redirect: 'follow',
          signal: AbortSignal.timeout(10000)
        });
        
        const status = response.status;
        const finalUrl = response.url;
        
        // Check for actual 404s or redirects to error pages
        const isBroken = (
          status === 404 || 
          finalUrl.includes('/syserror/') || 
          finalUrl.includes('/notfound') ||
          finalUrl.includes('/error')
        );
        
        if (isBroken) {
          console.log('[' + progress + '] BROKEN:', tender.source, '-', tender.title.substring(0, 50));
          console.log('    URL:', tender.notice_url);
          console.log('    Status:', status, '| Final:', finalUrl.substring(0, 80));
          console.log('');
          broken++;
          brokenUrls.push({
            id: tender.id,
            source: tender.source,
            title: tender.title,
            url: tender.notice_url,
            status: status,
            finalUrl: finalUrl
          });
          
          // Mark as closed
          await pool.query('UPDATE tenders SET status =  WHERE id = ', ['closed', tender.id]);
        } else {
          working++;
          if (working % 10 === 0) {
            console.log('[' + progress + '] OK - ' + working + ' working so far...');
          }
        }
        
        await delay(500);
        
      } catch (error) {
        console.log('[' + progress + '] ERROR:', tender.source, '-', tender.title.substring(0, 50));
        console.log('    ', error.message);
        console.log('');
        broken++;
        brokenUrls.push({
          id: tender.id,
          source: tender.source,
          title: tender.title,
          url: tender.notice_url,
          error: error.message
        });
      }
    }
    
    console.log('\n=== VERIFICATION COMPLETE ===\n');
    console.log('Total checked:', result.rows.length);
    console.log('Working:', working);
    console.log('Broken:', broken);
    console.log('Success rate:', ((working / result.rows.length) * 100).toFixed(1) + '%');
    
    if (brokenUrls.length > 0) {
      console.log('\n=== BROKEN URLS ===\n');
      brokenUrls.forEach((b, idx) => {
        console.log((idx + 1) + '. [' + b.source + '] ' + b.title.substring(0, 60));
        console.log('   URL:', b.url);
        if (b.error) {
          console.log('   Error:', b.error);
        } else {
          console.log('   Status:', b.status, '| Final URL:', b.finalUrl.substring(0, 80));
        }
        console.log('');
      });
    }
    
  } catch (error) {
    console.error('Verification failed:', error);
  } finally {
    await pool.end();
  }
}

verifyAll();