1  /**
  2   * Screenshot Migration Script
  3   * Migrates screenshot BLOBs from database to file system
  4   * Run with: node scripts/migrate-screenshots-to-files.js [--dry-run]
  5   */
  6  
  7  /* eslint-disable max-depth, security/detect-non-literal-fs-filename */
  8  
  9  import { createDatabaseConnection } from '../src/utils/db.js';
 10  import fs from 'fs/promises';
 11  import path from 'path';
 12  import { fileURLToPath } from 'url';
 13  
 14  const __dirname = path.dirname(fileURLToPath(import.meta.url));
 15  const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '../db/sites.db');
 16  const screenshotsDir = path.join(__dirname, '../screenshots');
 17  
 18  // Parse command line args
 19  const isDryRun = process.argv.includes('--dry-run');
 20  
 21  async function migrateScreenshots() {
 22    const db = createDatabaseConnection(dbPath);
 23    db.pragma('foreign_keys = ON');
 24  
 25    try {
 26      console.log('=== Screenshot Migration to File System ===\n');
 27      console.log(`Mode: ${isDryRun ? 'DRY RUN (no changes will be made)' : 'LIVE'}`);
 28      console.log(`Database: ${dbPath}`);
 29      console.log(`Screenshots directory: ${screenshotsDir}\n`);
 30  
 31      // Get all sites with screenshots
 32      const sites = db
 33        .prepare(
 34          `
 35        SELECT
 36          id,
 37          domain,
 38          screenshot_above_desktop,
 39          screenshot_below_desktop,
 40          screenshot_above_mobile,
 41          screenshot_above_desktop_uncropped,
 42          screenshot_below_desktop_uncropped,
 43          screenshot_above_mobile_uncropped,
 44          screenshot_path
 45        FROM sites
 46        WHERE screenshot_above_desktop IS NOT NULL
 47      `
 48        )
 49        .all();
 50  
 51      if (sites.length === 0) {
 52        console.log('No sites with screenshots found. Migration complete.');
 53        return;
 54      }
 55  
 56      console.log(`Found ${sites.length} sites with screenshots to migrate\n`);
 57  
 58      let migratedCount = 0;
 59      let skippedCount = 0;
 60      let errorCount = 0;
 61  
 62      for (const site of sites) {
 63        try {
 64          // Skip if already migrated
 65          if (site.screenshot_path) {
 66            console.log(`  [SKIP] Site ${site.id} (${site.domain}) - already migrated`);
 67            skippedCount++;
 68            continue;
 69          }
 70  
 71          const siteDir = path.join(screenshotsDir, site.id.toString());
 72          const screenshotPath = `screenshots/${site.id}`;
 73  
 74          if (!isDryRun) {
 75            // Create directory
 76            await fs.mkdir(siteDir, { recursive: true });
 77  
 78            // Write each screenshot file
 79            const screenshots = [
 80              { blob: site.screenshot_above_desktop, filename: 'desktop_above.jpg' },
 81              { blob: site.screenshot_below_desktop, filename: 'desktop_below.jpg' },
 82              { blob: site.screenshot_above_mobile, filename: 'mobile_above.jpg' },
 83              {
 84                blob: site.screenshot_above_desktop_uncropped,
 85                filename: 'desktop_above_uncropped.jpg',
 86              },
 87              {
 88                blob: site.screenshot_below_desktop_uncropped,
 89                filename: 'desktop_below_uncropped.jpg',
 90              },
 91              {
 92                blob: site.screenshot_above_mobile_uncropped,
 93                filename: 'mobile_above_uncropped.jpg',
 94              },
 95            ];
 96  
 97            for (const { blob, filename } of screenshots) {
 98              if (blob) {
 99                const filePath = path.join(siteDir, filename);
100                await fs.writeFile(filePath, blob);
101              }
102            }
103  
104            // Update database with screenshot path
105            db.prepare(
106              `
107              UPDATE sites
108              SET screenshot_path = ?
109              WHERE id = ?
110            `
111            ).run(screenshotPath, site.id);
112          }
113  
114          console.log(`  [OK] Site ${site.id} (${site.domain}) -> ${screenshotPath}/`);
115          migratedCount++;
116        } catch (error) {
117          console.error(`  [ERROR] Site ${site.id} (${site.domain}): ${error.message}`);
118          errorCount++;
119        }
120      }
121  
122      console.log('\n=== Migration Summary ===');
123      console.log(`Total sites: ${sites.length}`);
124      console.log(`Migrated: ${migratedCount}`);
125      console.log(`Skipped: ${skippedCount}`);
126      console.log(`Errors: ${errorCount}`);
127  
128      if (isDryRun) {
129        console.log('\nDRY RUN - No changes were made. Run without --dry-run to apply migration.');
130      } else {
131        console.log('\nMigration complete!');
132        console.log('\nNext steps:');
133        console.log('1. Verify screenshots are accessible from the file system');
134        console.log('2. Test scoring and rescoring stages with file-based screenshots');
135        console.log(
136          '3. Once verified, you can remove BLOB columns with: db/migrations/006-remove-screenshot-blobs.sql'
137        );
138      }
139    } catch (error) {
140      console.error('\nMigration failed:', error);
141      throw error;
142    } finally {
143      db.close();
144    }
145  }
146  
147  // Run migration
148  migrateScreenshots().catch(error => {
149    console.error('Fatal error:', error);
150    process.exit(1);
151  });