1  #!/usr/bin/env node
   2  
   3  /**
   4   * Unified Auto-Fix Script
   5   *
   6   * Runs ALL automated maintenance tasks and commits fixes to shared "autofix" branch.
   7   *
   8   * Tasks included:
   9   * 1. Prettier formatting
  10   * 2. ESLint auto-fix
  11   * 3. Security audit fixes (npm audit fix)
  12   * 4. Dependency updates (patches and minors)
  13   * 5. Sage AI quality fixes
  14   * 6. Stale documentation updates
  15   * 7. Documentation consistency check
  16   * 8. Test generation for low-coverage files
  17   * 9. Snyk security scan and fixes
  18   * 10. Semgrep security scan (flags issues for human review)
  19   * 11. Deep code analysis (flags issues for human review)
  20   * 12. Database maintenance (ANALYZE + apply recommended indexes)
  21   * 13. Archive completed TODO.md tasks to CHANGELOG.md
  22   *
  23   * All fixes are committed to the "autofix" branch for manual review and testing.
  24   * Items requiring human judgment are flagged in the Human Review dashboard.
  25   * User reviews the entire branch, tests, and merges when satisfied.
  26   */
  27  
  28  import { execSync } from 'child_process';
  29  import { existsSync } from 'fs';
  30  import { join } from 'path';
  31  import { fileURLToPath } from 'url';
  32  import { dirname } from 'path';
  33  import { createDatabaseConnection } from '../src/utils/db.js';
  34  import {
  35    ensureAutofixBranch,
  36    commitAutofix,
  37    getAutofixSummary,
  38    isGitRepo,
  39  } from './autofix-branch.js';
  40  
  41  const __filename = fileURLToPath(import.meta.url);
  42  const __dirname = dirname(__filename);
  43  const projectRoot = join(__dirname, '..');
  44  
  45  console.log('🔧 Unified Auto-Fix: Running all automated maintenance tasks\n');
  46  console.log(`${'═'.repeat(60)}\n`);
  47  
  48  // Track overall stats
  49  const overallStats = {
  50    startTime: new Date().toISOString(),
  51    tasks: [],
  52    commitsCreated: 0,
  53    totalChanges: 0,
  54    errors: [],
  55  };
  56  
  57  /**
  58   * Run a maintenance task
  59   */
  60  async function runTask(name, description, taskFn) {
  61    console.log(`\n${'─'.repeat(60)}`);
  62    console.log(`\n🔧 ${name}`);
  63    console.log(`   ${description}\n`);
  64  
  65    const taskStart = Date.now();
  66    const task = {
  67      name,
  68      description,
  69      success: false,
  70      committed: false,
  71      duration: 0,
  72    };
  73  
  74    try {
  75      const result = await taskFn();
  76      task.success = true;
  77      task.committed = result?.committed || false;
  78      task.details = result;
  79    } catch (error) {
  80      task.success = false;
  81      task.error = error.message;
  82      overallStats.errors.push({ task: name, error: error.message });
  83      console.error(`❌ ${name} failed: ${error.message}\n`);
  84    }
  85  
  86    task.duration = ((Date.now() - taskStart) / 1000).toFixed(2);
  87    overallStats.tasks.push(task);
  88  
  89    if (task.committed) {
  90      overallStats.commitsCreated++;
  91    }
  92  }
  93  
  94  /**
  95   * Task 1: Prettier formatting
  96   */
  97  async function prettierTask() {
  98    console.log('Running prettier --write ...');
  99    try {
 100      execSync('npm run format', {
 101        cwd: projectRoot,
 102        stdio: 'inherit',
 103      });
 104  
 105      // Check if there are changes
 106      const status = execSync('git status --porcelain', {
 107        cwd: projectRoot,
 108        encoding: 'utf-8',
 109      });
 110  
 111      if (!status.trim()) {
 112        console.log('✅ No formatting changes needed\n');
 113        return { committed: false, message: 'No changes' };
 114      }
 115  
 116      // Commit changes
 117      const result = commitAutofix('format', 'apply prettier formatting', {
 118        'Files formatted': status.trim().split('\n').length,
 119      });
 120  
 121      console.log(`✅ ${result.message}\n`);
 122      return result;
 123    } catch (error) {
 124      throw new Error(`Prettier failed: ${error.message}`);
 125    }
 126  }
 127  
 128  /**
 129   * Task 2: ESLint auto-fix
 130   */
 131  async function eslintTask() {
 132    console.log('Running eslint --fix ...');
 133    try {
 134      execSync('npm run lint:fix', {
 135        cwd: projectRoot,
 136        stdio: 'inherit',
 137      });
 138  
 139      // Check if there are changes
 140      const status = execSync('git status --porcelain', {
 141        cwd: projectRoot,
 142        encoding: 'utf-8',
 143      });
 144  
 145      if (!status.trim()) {
 146        console.log('✅ No linting changes needed\n');
 147        return { committed: false, message: 'No changes' };
 148      }
 149  
 150      // Commit changes
 151      const result = commitAutofix('lint', 'apply eslint auto-fixes', {
 152        'Files fixed': status.trim().split('\n').length,
 153      });
 154  
 155      console.log(`✅ ${result.message}\n`);
 156      return result;
 157    } catch (error) {
 158      // ESLint may exit with error if unfixable issues remain - that's OK
 159      console.log('⚠️  Some linting issues may remain (will be handled by Sage AI)\n');
 160  
 161      const status = execSync('git status --porcelain', {
 162        cwd: projectRoot,
 163        encoding: 'utf-8',
 164      });
 165  
 166      if (status.trim()) {
 167        const result = commitAutofix('lint', 'apply partial eslint auto-fixes', {
 168          'Files fixed': status.trim().split('\n').length,
 169          Note: 'Some issues remain for Sage AI review',
 170        });
 171        return result;
 172      }
 173  
 174      return { committed: false, message: 'No fixable issues' };
 175    }
 176  }
 177  
 178  /**
 179   * Task 3: Security audit fixes
 180   */
 181  async function securityTask() {
 182    console.log('Running npm audit fix ...');
 183    try {
 184      // Run audit fix (use npm script for proper logging)
 185      execSync('npm run security:audit:fix', {
 186        cwd: projectRoot,
 187        stdio: 'inherit',
 188      });
 189  
 190      // Check if package-lock.json changed
 191      const status = execSync('git status --porcelain package-lock.json', {
 192        cwd: projectRoot,
 193        encoding: 'utf-8',
 194      });
 195  
 196      if (!status.trim()) {
 197        console.log('✅ No security fixes needed\n');
 198        return { committed: false, message: 'No vulnerabilities' };
 199      }
 200  
 201      // Run tests to verify fixes don't break anything
 202      console.log('\n🧪 Running tests to verify security fixes...');
 203      try {
 204        execSync('npm test', {
 205          cwd: projectRoot,
 206          stdio: 'inherit',
 207        });
 208      } catch (error) {
 209        console.error('❌ Tests failed after security fixes - rolling back');
 210        execSync('git checkout package-lock.json', { cwd: projectRoot });
 211        throw new Error('Security fixes broke tests');
 212      }
 213  
 214      // Commit changes
 215      const result = commitAutofix('security', 'apply npm audit fixes', {
 216        'Tests passed': 'yes',
 217      });
 218  
 219      console.log(`✅ ${result.message}\n`);
 220      return result;
 221    } catch (error) {
 222      throw new Error(`Security audit failed: ${error.message}`);
 223    }
 224  }
 225  
 226  /**
 227   * Task 4: Dependency updates (patches and minors only)
 228   */
 229  async function dependencyTask() {
 230    console.log('Running dependency updates (patches and minors)...');
 231    try {
 232      // Check if update script exists
 233      const updateScript = join(projectRoot, 'scripts/update-dependencies.js');
 234      if (!existsSync(updateScript)) {
 235        console.log('ℹ️  Dependency update script not found, skipping\n');
 236        return { committed: false, message: 'Script not found' };
 237      }
 238  
 239      // Run with --no-commit flag (we'll commit manually)
 240      execSync('node scripts/update-dependencies.js --level=minors --no-commit', {
 241        cwd: projectRoot,
 242        stdio: 'inherit',
 243      });
 244  
 245      // Check if there are changes
 246      const status = execSync('git status --porcelain package.json package-lock.json', {
 247        cwd: projectRoot,
 248        encoding: 'utf-8',
 249      });
 250  
 251      if (!status.trim()) {
 252        console.log('✅ Dependencies already up to date\n');
 253        return { committed: false, message: 'No updates available' };
 254      }
 255  
 256      // Commit changes
 257      const result = commitAutofix('deps', 'update dependencies (patches and minors)', {
 258        'Tests passed': 'yes (verified by update script)',
 259      });
 260  
 261      console.log(`✅ ${result.message}\n`);
 262      return result;
 263    } catch (error) {
 264      throw new Error(`Dependency update failed: ${error.message}`);
 265    }
 266  }
 267  
 268  /**
 269   * Task 5: Sage AI quality fixes
 270   */
 271  async function sageTask() {
 272    console.log('Running Sage AI auto-fix ...');
 273    try {
 274      // Check if sage-auto-fix script exists
 275      const sageScript = join(projectRoot, 'scripts/sage-auto-fix.js');
 276      if (!existsSync(sageScript)) {
 277        console.log('ℹ️  Sage auto-fix script not found, skipping\n');
 278        return { committed: false, message: 'Script not found' };
 279      }
 280  
 281      // Check for claude CLI (sage-auto-fix.js now uses claude CLI instead of Anthropic SDK)
 282      try {
 283        execSync('which claude', { stdio: 'ignore' });
 284      } catch {
 285        console.log('claude CLI not found in PATH, skipping Sage AI fixes\n');
 286        return { committed: false, message: 'claude CLI not available' };
 287      }
 288  
 289      // Run sage auto-fix with --no-commit flag (we manage commits here)
 290      execSync('node scripts/sage-auto-fix.js --no-commit', {
 291        cwd: projectRoot,
 292        stdio: 'inherit',
 293      });
 294  
 295      // Check if there are changes
 296      const status = execSync('git status --porcelain', {
 297        cwd: projectRoot,
 298        encoding: 'utf-8',
 299      });
 300  
 301      if (!status.trim()) {
 302        console.log('✅ No Sage AI fixes needed\n');
 303        return { committed: false, message: 'No issues found' };
 304      }
 305  
 306      // Commit changes
 307      const result = commitAutofix('sage', 'apply AI-powered quality fixes', {
 308        'Fixed by': 'Claude API via Sage review',
 309      });
 310  
 311      console.log(`✅ ${result.message}\n`);
 312      return result;
 313    } catch (error) {
 314      // Sage might fail but we want to continue with other tasks
 315      console.log('⚠️  Sage AI fixes skipped or partial\n');
 316      return { committed: false, message: error.message };
 317    }
 318  }
 319  
 320  /**
 321   * Task 6: Update stale documentation
 322   */
 323  async function staleDocsTask() {
 324    console.log('Updating stale documentation...');
 325    try {
 326      // Check if update-stale-docs script exists
 327      const docScript = join(projectRoot, 'scripts/update-stale-docs.js');
 328      if (!existsSync(docScript)) {
 329        console.log('ℹ️  Stale docs update script not found, skipping\n');
 330        return { committed: false, message: 'Script not found' };
 331      }
 332  
 333      // Check for claude CLI (update-stale-docs.js now uses claude CLI instead of Anthropic SDK)
 334      try {
 335        execSync('which claude', { stdio: 'ignore' });
 336      } catch {
 337        console.log('claude CLI not found in PATH, skipping doc updates\n');
 338        return { committed: false, message: 'claude CLI not available' };
 339      }
 340  
 341      // Run stale docs updater
 342      execSync('node scripts/update-stale-docs.js', {
 343        cwd: projectRoot,
 344        stdio: 'inherit',
 345      });
 346  
 347      // Check if there are changes
 348      const status = execSync('git status --porcelain *.md docs/ .env.example', {
 349        cwd: projectRoot,
 350        encoding: 'utf-8',
 351      });
 352  
 353      if (!status.trim()) {
 354        console.log('✅ All documentation is up to date\n');
 355        return { committed: false, message: 'No updates needed' };
 356      }
 357  
 358      // Commit changes
 359      const updatedFiles = status.trim().split('\n').length;
 360      const result = commitAutofix('docs', 'update stale documentation', {
 361        'Files updated': updatedFiles,
 362        'Updated by': 'Claude API',
 363        Note: 'Review flags marked with HUMAN_REVIEW_REQUIRED',
 364      });
 365  
 366      console.log(`✅ ${result.message}\n`);
 367      return result;
 368    } catch (error) {
 369      // Doc updates might fail for some files - that's OK
 370      console.log('⚠️  Documentation update completed with some issues\n');
 371      return { committed: false, message: error.message };
 372    }
 373  }
 374  
 375  /**
 376   * Task 7: Documentation consistency check
 377   */
 378  async function docsTask() {
 379    console.log('Running documentation consistency check...');
 380    try {
 381      // Check if doc-check script exists
 382      const docScript = join(projectRoot, 'scripts/doc-code-check.js');
 383      if (!existsSync(docScript)) {
 384        console.log('ℹ️  Documentation check script not found, skipping\n');
 385        return { committed: false, message: 'Script not found' };
 386      }
 387  
 388      // Run doc check (it generates reports, doesn't auto-fix)
 389      execSync('node scripts/doc-code-check.js', {
 390        cwd: projectRoot,
 391        stdio: 'inherit',
 392      });
 393  
 394      console.log('✅ Documentation check completed (report generated)\n');
 395      return { committed: false, message: 'Report generated in .analysis-reports/' };
 396    } catch (error) {
 397      throw new Error(`Documentation check failed: ${error.message}`);
 398    }
 399  }
 400  
 401  /**
 402   * Task 8: Generate missing unit tests
 403   */
 404  async function testGenerationTask() {
 405    console.log('Generating unit tests for low-coverage files...');
 406    try {
 407      // Check if generate-tests script exists
 408      const testScript = join(projectRoot, 'scripts/generate-tests.js');
 409      if (!existsSync(testScript)) {
 410        console.log('ℹ️  Test generation script not found, skipping\n');
 411        return { committed: false, message: 'Script not found' };
 412      }
 413  
 414      // Check for claude CLI (generate-tests.js now uses claude CLI instead of Anthropic SDK)
 415      try {
 416        execSync('which claude', { stdio: 'ignore' });
 417      } catch {
 418        console.log('claude CLI not found in PATH, skipping test generation\n');
 419        return { committed: false, message: 'claude CLI not available' };
 420      }
 421  
 422      // Run test generation
 423      execSync('node scripts/generate-tests.js', {
 424        cwd: projectRoot,
 425        stdio: 'inherit',
 426      });
 427  
 428      // Check if there are changes (new test files)
 429      const status = execSync('git status --porcelain tests/', {
 430        cwd: projectRoot,
 431        encoding: 'utf-8',
 432      });
 433  
 434      if (!status.trim()) {
 435        console.log('✅ All files meet coverage target\n');
 436        return { committed: false, message: 'No tests needed' };
 437      }
 438  
 439      // Commit changes
 440      const newTests = status
 441        .trim()
 442        .split('\n')
 443        .filter(line => line.includes('.test.js'));
 444      const result = commitAutofix('tests', 'generate unit tests for low-coverage files', {
 445        'Tests created': newTests.length,
 446        'Generated by': 'Claude API',
 447      });
 448  
 449      console.log(`✅ ${result.message}\n`);
 450      return result;
 451    } catch (error) {
 452      // Test generation might fail for some files - that's OK
 453      console.log('⚠️  Test generation completed with some failures\n');
 454  
 455      // Still commit any successful tests
 456      try {
 457        const status = execSync('git status --porcelain tests/', {
 458          cwd: projectRoot,
 459          encoding: 'utf-8',
 460        });
 461  
 462        if (status.trim()) {
 463          const result = commitAutofix('tests', 'generate partial unit tests', {
 464            Note: 'Some test generation failed, but these tests passed',
 465          });
 466          return result;
 467        }
 468      } catch {
 469        // No tests to commit
 470      }
 471  
 472      return { committed: false, message: error.message };
 473    }
 474  }
 475  
 476  /**
 477   * Task 9: Snyk security scan
 478   */
 479  async function snykTask() {
 480    console.log('Running Snyk security scan...');
 481    try {
 482      // Check if snyk is installed
 483      try {
 484        execSync('which snyk', { stdio: 'pipe' });
 485      } catch {
 486        console.log('ℹ️  Snyk not installed, skipping\n');
 487        return { committed: false, message: 'Snyk not installed' };
 488      }
 489  
 490      // Run snyk test (don't fail on vulnerabilities)
 491      console.log('Scanning for vulnerabilities...');
 492      try {
 493        execSync('npm run security:snyk', {
 494          cwd: projectRoot,
 495          stdio: 'inherit',
 496        });
 497      } catch {
 498        // Snyk exits with error if vulnerabilities found - that's expected
 499        console.log('⚠️  Vulnerabilities found, attempting fixes...');
 500      }
 501  
 502      // Try to auto-fix
 503      try {
 504        execSync('npm run security:snyk:fix', {
 505          cwd: projectRoot,
 506          stdio: 'inherit',
 507        });
 508      } catch {
 509        console.log('⚠️  Some vulnerabilities cannot be auto-fixed\n');
 510      }
 511  
 512      // Check if package files changed
 513      const status = execSync('git status --porcelain package.json package-lock.json', {
 514        cwd: projectRoot,
 515        encoding: 'utf-8',
 516      });
 517  
 518      if (!status.trim()) {
 519        console.log('✅ No Snyk fixes applied\n');
 520        return { committed: false, message: 'No fixes available' };
 521      }
 522  
 523      // Run tests to verify fixes don't break anything
 524      console.log('\n🧪 Running tests to verify Snyk fixes...');
 525      try {
 526        execSync('npm test', {
 527          cwd: projectRoot,
 528          stdio: 'inherit',
 529        });
 530      } catch {
 531        console.error('❌ Tests failed after Snyk fixes - rolling back');
 532        execSync('git checkout package.json package-lock.json', { cwd: projectRoot });
 533        throw new Error('Snyk fixes broke tests');
 534      }
 535  
 536      // Commit changes
 537      const result = commitAutofix('snyk', 'apply Snyk security fixes', {
 538        'Tests passed': 'yes',
 539      });
 540  
 541      console.log(`✅ ${result.message}\n`);
 542      return result;
 543    } catch (error) {
 544      console.log('⚠️  Snyk scan completed with issues\n');
 545      return { committed: false, message: error.message };
 546    }
 547  }
 548  
 549  /**
 550   * Task 10: Semgrep security scan
 551   */
 552  async function semgrepTask() {
 553    console.log('Running Semgrep security scan...');
 554    try {
 555      // Check if semgrep is installed
 556      try {
 557        execSync('which semgrep', { stdio: 'pipe' });
 558      } catch {
 559        console.log('ℹ️  Semgrep not installed, skipping\n');
 560        return { committed: false, message: 'Semgrep not installed' };
 561      }
 562  
 563      // Run semgrep scan
 564      console.log('Scanning for security issues...');
 565      try {
 566        execSync('npm run security:semgrep', {
 567          cwd: projectRoot,
 568          stdio: 'inherit',
 569        });
 570      } catch {
 571        // Semgrep may exit with error if issues found - that's expected
 572        console.log('⚠️  Security issues found in Semgrep scan');
 573      }
 574  
 575      // Check if semgrep report was generated
 576      const reportPath = join(projectRoot, '.security-reports/semgrep.json');
 577      if (!existsSync(reportPath)) {
 578        console.log('⚠️  Semgrep report not generated\n');
 579        return { committed: false, message: 'No report generated' };
 580      }
 581  
 582      // Parse report and flag critical issues for human review
 583      const reportContent = execSync(`cat ${reportPath}`, { encoding: 'utf-8' });
 584      const report = JSON.parse(reportContent);
 585  
 586      if (report.results && report.results.length > 0) {
 587        // Import human review queue
 588        const { addReviewItem } = await import('../src/utils/human-review-queue.js');
 589  
 590        // Flag high-severity issues for human review
 591        const highSeverity = report.results.filter(
 592          r => r.extra && (r.extra.severity === 'ERROR' || r.extra.severity === 'WARNING')
 593        );
 594  
 595        // Filter out common false positives
 596        const falsePositivePatterns = [
 597          // Path traversal with __dirname or static paths
 598          { check: /path-traversal/, file: /scripts\// },
 599          // Child process with hardcoded git commands in autofix-branch.js
 600          { check: /child-process/, file: /autofix-branch\.js/ },
 601          // Child process with hardcoded commands in security/maintenance scripts
 602          { check: /child-process/, file: /(security-scan|npm-logger|quality-check)\.js/ },
 603        ];
 604  
 605        const realIssues = highSeverity.filter(issue => {
 606          const checkId = issue.check_id || '';
 607          const filePath = issue.path || '';
 608  
 609          // Skip if matches any false positive pattern
 610          return !falsePositivePatterns.some(
 611            pattern => pattern.check.test(checkId) && pattern.file.test(filePath)
 612          );
 613        });
 614  
 615        for (const issue of realIssues.slice(0, 10)) {
 616          // Limit to 10 to avoid spam
 617          const priority = issue.extra.severity === 'ERROR' ? 'high' : 'medium';
 618          addReviewItem({
 619            file: issue.path || 'Multiple files',
 620            reason: `Semgrep: ${issue.check_id || 'Security issue'}\n\n${issue.extra?.message || 'No details'}`,
 621            type: 'security',
 622            priority,
 623          });
 624        }
 625  
 626        const filteredCount = highSeverity.length - realIssues.length;
 627        if (realIssues.length > 0) {
 628          console.log(
 629            `⚠️  ${realIssues.length} security issues flagged for human review (${filteredCount} false positives filtered)\n`
 630          );
 631        } else if (filteredCount > 0) {
 632          console.log(`✅ ${filteredCount} semgrep warnings filtered as false positives\n`);
 633        } else {
 634          console.log('✅ No security issues found\n');
 635        }
 636      }
 637  
 638      console.log('✅ Semgrep scan completed\n');
 639      return { committed: false, message: 'Report generated, issues flagged for review' };
 640    } catch (error) {
 641      console.log('⚠️  Semgrep scan failed\n');
 642      return { committed: false, message: error.message };
 643    }
 644  }
 645  
 646  /**
 647   * Task 11: Deep code analysis
 648   */
 649  async function deepAnalysisTask() {
 650    console.log('Running deep code analysis...');
 651    try {
 652      // Check if deep-code-analysis script exists
 653      const analysisScript = join(projectRoot, 'scripts/deep-code-analysis.js');
 654      if (!existsSync(analysisScript)) {
 655        console.log('ℹ️  Deep code analysis script not found, skipping\n');
 656        return { committed: false, message: 'Script not found' };
 657      }
 658  
 659      // Run analysis (generates report only, no auto-fixes)
 660      execSync('node scripts/deep-code-analysis.js', {
 661        cwd: projectRoot,
 662        stdio: 'inherit',
 663      });
 664  
 665      console.log('✅ Deep code analysis completed (report generated in .analysis-reports/)\n');
 666      return { committed: false, message: 'Report generated' };
 667    } catch (error) {
 668      console.log('⚠️  Deep code analysis completed with issues\n');
 669      return { committed: false, message: error.message };
 670    }
 671  }
 672  
 673  /**
 674   * Task 12: Log Error Auto-Fix
 675   */
 676  async function logErrorFixTask() {
 677    console.log('Analyzing logs for auto-fixable errors...');
 678    try {
 679      const logFixScript = join(projectRoot, 'scripts/log-error-autofix.js');
 680      if (!existsSync(logFixScript)) {
 681        console.log('ℹ️  Log error auto-fix script not found, skipping\n');
 682        return { committed: false, message: 'Script not found' };
 683      }
 684  
 685      // Run with default settings (last 2 days, skip retried errors)
 686      execSync('node scripts/log-error-autofix.js --days=2 --skip-retried', {
 687        cwd: projectRoot,
 688        stdio: 'inherit',
 689      });
 690  
 691      // Check if there are database changes
 692      const dbStatus = execSync('git status --porcelain db/sites.db', {
 693        cwd: projectRoot,
 694        encoding: 'utf-8',
 695      });
 696  
 697      if (!dbStatus.trim()) {
 698        console.log('✅ No auto-fixable errors found in logs\n');
 699        return { committed: false, message: 'No errors to fix' };
 700      }
 701  
 702      // Commit changes
 703      const result = commitAutofix('log-fixes', 'automatically fix errors from log analysis', {
 704        'Errors fixed': 'See .analysis-reports/log-autofix-*.md for details',
 705      });
 706  
 707      console.log(`✅ ${result.message}\n`);
 708      return result;
 709    } catch (error) {
 710      console.log('⚠️  Log error auto-fix completed with issues\n');
 711      return { committed: false, message: error.message };
 712    }
 713  }
 714  
 715  /**
 716   * Task 13: Database maintenance (ANALYZE + apply index recommendations)
 717   */
 718  async function databaseMaintenanceTask() {
 719    console.log('Running database maintenance...');
 720    try {
 721      const dbPath = process.env.DATABASE_PATH || join(projectRoot, 'db/sites.db');
 722      const db = createDatabaseConnection(dbPath);
 723  
 724      try {
 725        // Run ANALYZE to update query planner statistics
 726        console.log('Running ANALYZE to update query planner statistics...');
 727        db.pragma('analyze');
 728  
 729        // Get pending performance recommendations from human_review_queue
 730        const recommendations = db
 731          .prepare(
 732            `SELECT id, file, reason FROM human_review_queue
 733             WHERE type = 'performance' AND status = 'pending'
 734             ORDER BY CASE priority
 735               WHEN 'critical' THEN 1
 736               WHEN 'high' THEN 2
 737               WHEN 'medium' THEN 3
 738               WHEN 'low' THEN 4
 739             END, created_at ASC`
 740          )
 741          .all();
 742  
 743        if (recommendations.length === 0) {
 744          console.log('✅ No pending database performance recommendations\n');
 745          return { committed: false, message: 'No recommendations to apply' };
 746        }
 747  
 748        let appliedCount = 0;
 749        const appliedIndexes = [];
 750  
 751        // Apply index recommendations
 752        for (const rec of recommendations) {
 753          // Extract SQL from reason field (look for CREATE INDEX statements)
 754          const sqlMatch = rec.reason.match(/```sql\n(CREATE INDEX[^`]+)\n```/);
 755  
 756          if (sqlMatch) {
 757            const sql = sqlMatch[1].trim();
 758            try {
 759              console.log(`Applying: ${sql}`);
 760              db.exec(sql);
 761              appliedIndexes.push(sql);
 762  
 763              // Mark as reviewed
 764              db.prepare(
 765                `UPDATE human_review_queue
 766                 SET status = 'reviewed',
 767                     reviewed_at = datetime('now'),
 768                     reviewed_by = 'autofix',
 769                     notes = 'Automatically applied by database maintenance task'
 770                 WHERE id = ?`
 771              ).run(rec.id);
 772  
 773              appliedCount++;
 774            } catch (error) {
 775              console.log(
 776                `⚠️  Failed to apply index from recommendation ${rec.id}: ${error.message}`
 777              );
 778              // Don't fail the entire task, just skip this one
 779            }
 780          } else if (rec.reason.includes('ANALYZE')) {
 781            // ANALYZE recommendation - we already ran it above
 782            db.prepare(
 783              `UPDATE human_review_queue
 784               SET status = 'reviewed',
 785                   reviewed_at = datetime('now'),
 786                   reviewed_by = 'autofix',
 787                   notes = 'ANALYZE automatically run by database maintenance task'
 788               WHERE id = ?`
 789            ).run(rec.id);
 790            appliedCount++;
 791          }
 792        }
 793  
 794        if (appliedCount === 0) {
 795          console.log('✅ No actionable database recommendations to apply\n');
 796          return { committed: false, message: 'No actionable recommendations' };
 797        }
 798  
 799        console.log(`✅ Applied ${appliedCount} database performance improvements\n`);
 800        return {
 801          committed: false,
 802          message: `Applied ${appliedCount} recommendations (${appliedIndexes.length} indexes created)`,
 803          details: {
 804            applied: appliedCount,
 805            indexes: appliedIndexes,
 806          },
 807        };
 808      } finally {
 809        db.close();
 810      }
 811    } catch (error) {
 812      console.log('⚠️  Database maintenance failed\n');
 813      return { committed: false, message: error.message };
 814    }
 815  }
 816  
 817  /**
 818   * Task 13: Archive completed TODO.md tasks to CHANGELOG.md
 819   */
 820  async function archiveTodosTask() {
 821    console.log('Archiving completed TODO.md tasks...');
 822    try {
 823      const { readFileSync, writeFileSync, existsSync } = await import('fs');
 824      const todoPath = join(projectRoot, 'docs/TODO.md');
 825      const changelogPath = join(projectRoot, 'CHANGELOG.md');
 826  
 827      if (!existsSync(todoPath)) {
 828        console.log('ℹ️  TODO.md not found, skipping\n');
 829        return { committed: false, message: 'TODO.md not found' };
 830      }
 831  
 832      const todoContent = readFileSync(todoPath, 'utf-8');
 833      const lines = todoContent.split('\n');
 834  
 835      // Extract completed tasks (lines with [x] or ✅)
 836      const completedTasks = [];
 837      const remainingLines = [];
 838      let currentSection = '';
 839  
 840      for (const line of lines) {
 841        // Track current section header
 842        if (line.match(/^#{1,3}\s/)) {
 843          currentSection = line.replace(/^#{1,3}\s+/, '').replace(/✅\s*/, '');
 844        }
 845  
 846        // Check if line is a completed task
 847        if (line.match(/^-\s+\[x\]/i) || line.match(/^-\s+✅/) || line.trim().startsWith('✅')) {
 848          completedTasks.push({
 849            section: currentSection,
 850            task: line.trim(),
 851          });
 852        } else {
 853          remainingLines.push(line);
 854        }
 855      }
 856  
 857      if (completedTasks.length === 0) {
 858        console.log('✅ No completed tasks to archive\n');
 859  
 860        // Clear any TODO.md archiving recommendations from human review queue
 861        const dbPath = process.env.DATABASE_PATH || join(projectRoot, 'db/sites.db');
 862        const db = createDatabaseConnection(dbPath);
 863        try {
 864          db.prepare(
 865            `UPDATE human_review_queue
 866             SET status = 'reviewed',
 867                 reviewed_at = datetime('now'),
 868                 reviewed_by = 'autofix',
 869                 notes = 'No completed tasks found to archive'
 870             WHERE file = 'docs/TODO.md' AND status = 'pending'`
 871          ).run();
 872        } finally {
 873          db.close();
 874        }
 875  
 876        return { committed: false, message: 'No completed tasks' };
 877      }
 878  
 879      // Create or update CHANGELOG.md
 880      const timestamp = new Date().toISOString().split('T')[0];
 881      const changelogEntry = `## Completed Tasks - ${timestamp}\n\n${completedTasks.map(t => t.task).join('\n')}\n\n`;
 882  
 883      let changelogContent = '';
 884      if (existsSync(changelogPath)) {
 885        changelogContent = readFileSync(changelogPath, 'utf-8');
 886        changelogContent = changelogEntry + changelogContent;
 887      } else {
 888        changelogContent = `# Changelog\n\nCompleted tasks archived from TODO.md\n\n${changelogEntry}`;
 889      }
 890  
 891      // Write updated files
 892      writeFileSync(changelogPath, changelogContent);
 893      writeFileSync(todoPath, remainingLines.join('\n'));
 894  
 895      // Mark TODO.md archiving recommendations as reviewed
 896      const dbPath = process.env.DATABASE_PATH || join(projectRoot, 'db/sites.db');
 897      const db = createDatabaseConnection(dbPath);
 898      try {
 899        db.prepare(
 900          `UPDATE human_review_queue
 901           SET status = 'reviewed',
 902               reviewed_at = datetime('now'),
 903               reviewed_by = 'autofix',
 904               notes = 'Archived ${completedTasks.length} completed tasks to CHANGELOG.md'
 905           WHERE file = 'docs/TODO.md' AND status = 'pending'`
 906        ).run();
 907      } finally {
 908        db.close();
 909      }
 910  
 911      // Commit changes
 912      const result = commitAutofix(
 913        'docs',
 914        `archive ${completedTasks.length} completed tasks to CHANGELOG.md`,
 915        {
 916          'Tasks archived': completedTasks.length,
 917          'File updated': 'CHANGELOG.md',
 918        }
 919      );
 920  
 921      console.log(`✅ ${result.message}\n`);
 922      return result;
 923    } catch (error) {
 924      console.log('⚠️  TODO archiving failed\n');
 925      return { committed: false, message: error.message };
 926    }
 927  }
 928  
 929  /**
 930   * Main execution
 931   */
 932  async function main() {
 933    // Ensure we're in a git repo
 934    if (!isGitRepo()) {
 935      console.error('❌ Not a git repository');
 936      process.exit(1);
 937    }
 938  
 939    // Ensure we're on main
 940    console.log('📝 Ensuring we are on main...\n');
 941    const branchResult = ensureAutofixBranch();
 942    console.log(`✅ ${branchResult.message}\n`);
 943  
 944    // Run all tasks
 945    await runTask('Prettier Formatting', 'Automatically format code with Prettier', prettierTask);
 946  
 947    await runTask('ESLint Auto-Fix', 'Automatically fix linting issues', eslintTask);
 948  
 949    await runTask('Security Audit', 'Fix security vulnerabilities with npm audit', securityTask);
 950  
 951    await runTask('Dependency Updates', 'Update dependencies (patches and minors)', dependencyTask);
 952  
 953    await runTask('Sage AI Fixes', 'AI-powered code quality improvements', sageTask);
 954  
 955    await runTask(
 956      'Stale Documentation Update',
 957      'Update outdated documentation (AI-powered)',
 958      staleDocsTask
 959    );
 960  
 961    await runTask('Documentation Check', 'Verify documentation consistency', docsTask);
 962  
 963    await runTask(
 964      'Test Generation',
 965      'Generate unit tests for low-coverage files (AI-powered)',
 966      testGenerationTask
 967    );
 968  
 969    await runTask('Snyk Security Scan', 'Scan and fix vulnerabilities with Snyk', snykTask);
 970  
 971    await runTask('Semgrep Security Scan', 'Static security analysis with Semgrep', semgrepTask);
 972  
 973    await runTask(
 974      'Deep Code Analysis',
 975      'Comprehensive code health and technical debt analysis',
 976      deepAnalysisTask
 977    );
 978  
 979    await runTask(
 980      'Log Error Auto-Fix',
 981      'Parse logs and automatically fix site errors and code bugs',
 982      logErrorFixTask
 983    );
 984  
 985    await runTask(
 986      'Database Maintenance',
 987      'Run ANALYZE and apply recommended indexes automatically',
 988      databaseMaintenanceTask
 989    );
 990  
 991    await runTask(
 992      'Archive Completed TODOs',
 993      'Move completed tasks from TODO.md to CHANGELOG.md',
 994      archiveTodosTask
 995    );
 996  
 997    // Print summary
 998    overallStats.endTime = new Date().toISOString();
 999    const totalDuration = overallStats.tasks.reduce((sum, t) => sum + parseFloat(t.duration), 0);
1000  
1001    console.log(`\n${'═'.repeat(60)}`);
1002    console.log('\n📊 Unified Auto-Fix Summary\n');
1003    console.log(`${'═'.repeat(60)}\n`);
1004  
1005    console.log(`Total Tasks: ${overallStats.tasks.length}`);
1006    console.log(`Successful: ${overallStats.tasks.filter(t => t.success).length}`);
1007    console.log(`Failed: ${overallStats.tasks.filter(t => !t.success).length}`);
1008    console.log(`Commits Created: ${overallStats.commitsCreated}`);
1009    console.log(`Total Duration: ${totalDuration.toFixed(2)}s\n`);
1010  
1011    // Show task breakdown
1012    console.log('Task Breakdown:');
1013    overallStats.tasks.forEach(task => {
1014      const icon = task.success ? '✅' : '❌';
1015      const commit = task.committed ? '📝' : '  ';
1016      console.log(`  ${icon} ${commit} ${task.name} (${task.duration}s)`);
1017      if (task.error) {
1018        console.log(`       Error: ${task.error}`);
1019      }
1020    });
1021  
1022    // Show branch summary
1023    if (overallStats.commitsCreated > 0) {
1024      console.log(`\n${'─'.repeat(60)}`);
1025      console.log('\n📋 Recent Commits on main:\n');
1026      const summary = getAutofixSummary();
1027      if (summary.exists && summary.commits?.length > 0) {
1028        summary.commits.slice(0, 5).forEach(c => {
1029          console.log(`  ${c.hash} - ${c.subject} (${c.date})`);
1030        });
1031        console.log();
1032      }
1033    } else {
1034      console.log('\n✅ No changes needed - code is already in great shape!\n');
1035    }
1036  
1037    console.log(`${'═'.repeat(60)}\n`);
1038  
1039    // Exit with appropriate code
1040    const hasErrors = overallStats.errors.length > 0;
1041    const hasCommits = overallStats.commitsCreated > 0;
1042  
1043    if (hasErrors && !hasCommits) {
1044      console.error('❌ All tasks failed\n');
1045      process.exit(1);
1046    } else if (hasErrors && hasCommits) {
1047      console.log('⚠️  Some tasks failed, but fixes were applied\n');
1048      process.exit(0);
1049    } else {
1050      console.log('✅ All tasks completed successfully\n');
1051      process.exit(0);
1052    }
1053  }
1054  
1055  // Run
1056  main().catch(error => {
1057    console.error('❌ Fatal error:', error);
1058    process.exit(1);
1059  });