1  #!/usr/bin/env node
  2  
  3  /**
  4   * Generate Daily Progress Report
  5   * Collects git, database, and system data from last 24 hours and generates PDF report
  6   */
  7  
  8  import { exec } from 'child_process';
  9  import { promisify } from 'util';
 10  import { join, dirname } from 'path';
 11  import { fileURLToPath } from 'url';
 12  import { readFileSync, existsSync, readdirSync } from 'fs';
 13  import { getAll, getOne, closePool } from '../src/utils/db.js';
 14  import dotenv from 'dotenv';
 15  import { generateDailyReport } from '../src/reports/daily-progress-html-generator.js';
 16  import Logger from '../src/utils/logger.js';
 17  
 18  dotenv.config();
 19  
 20  const execAsync = promisify(exec);
 21  const logger = new Logger('DailyReportCLI');
 22  
 23  const __filename = fileURLToPath(import.meta.url);
 24  const __dirname = dirname(__filename);
 25  const projectRoot = join(__dirname, '..');
 26  
 27  // All MMO workspace repos (relative to ~/code/)
 28  const MMO_REPOS = [
 29    { name: '333Method', path: join(projectRoot) },
 30    { name: '2Step', path: join(projectRoot, '..', '2Step') },
 31    { name: 'distributed-infra', path: join(projectRoot, '..', 'distributed-infra') },
 32    { name: 'mmo-platform', path: join(projectRoot, '..', 'mmo-platform') },
 33  ];
 34  
 35  /**
 36   * Get git activity from last 24 hours across all MMO workspace repos
 37   */
 38  async function getGitActivity() {
 39    const allCommits = [];
 40    let filesChanged = 0;
 41    let linesAdded = 0;
 42    let linesRemoved = 0;
 43  
 44    for (const repo of MMO_REPOS) {
 45      if (!existsSync(join(repo.path, '.git'))) continue;
 46      try {
 47        const { stdout: commitsRaw } = await execAsync(
 48          'git log --since="24 hours ago" --pretty=format:"%h|%s|%ar"',
 49          { cwd: repo.path }
 50        );
 51  
 52        const repoCommits = commitsRaw
 53          .split('\n')
 54          .filter(line => line.trim())
 55          .map(line => {
 56            const [hash, message, timeAgo] = line.split('|');
 57            return { hash, message, timeAgo, repo: repo.name };
 58          });
 59        allCommits.push(...repoCommits);
 60  
 61        try {
 62          const { stdout: diffStat } = await execAsync('git diff --stat HEAD@{24.hours.ago}..HEAD', {
 63            cwd: repo.path,
 64            timeout: 20000,
 65          });
 66          const match = diffStat.match(
 67            /(\d+) files? changed(?:, (\d+) insertions?\(\+\))?(?:, (\d+) deletions?\(-\))?/
 68          );
 69          if (match) {
 70            filesChanged += parseInt(match[1]) || 0;
 71            linesAdded += parseInt(match[2]) || 0;
 72            linesRemoved += parseInt(match[3]) || 0;
 73          }
 74        } catch (_error) {
 75          // no commits in window — normal
 76        }
 77      } catch (error) {
 78        logger.warn(`Could not fetch git activity for ${repo.name}:`, error.message);
 79      }
 80    }
 81  
 82    return {
 83      commits: allCommits,
 84      filesChanged,
 85      linesAdded,
 86      linesRemoved,
 87    };
 88  }
 89  
 90  /**
 91   * Get database changes from last 24 hours, including full status/outreach trees
 92   */
 93  async function getDatabaseChanges() {
 94    try {
 95      // Total sites
 96      const totalRow = await getOne('SELECT COUNT(*) as total FROM sites');
 97      const total = totalRow?.total ?? 0;
 98  
 99      // New sites (created in last 24h) — use site_status log
100      const newSitesRow = await getOne(
101        `SELECT COUNT(DISTINCT site_id) as newSites FROM site_status
102           WHERE status = 'found' AND created_at > NOW() - INTERVAL '1 day'`
103      );
104      const newSites = newSitesRow?.newSites ?? 0;
105  
106      // Lightweight status tree — uses indexed queries only (no full-table scans)
107      const statusTotals = await getAll('SELECT status, COUNT(*) AS total FROM sites GROUP BY status');
108      // Use site_status transition log for deltas
109      const statusD24 = await getAll(
110        `SELECT status, COUNT(*) AS delta FROM site_status
111         WHERE created_at > NOW() - INTERVAL '24 hours' GROUP BY status`
112      );
113      const statusD1 = await getAll(
114        `SELECT status, COUNT(*) AS delta FROM site_status
115         WHERE created_at > NOW() - INTERVAL '1 hour' GROUP BY status`
116      );
117      const d24map = Object.fromEntries(statusD24.map(r => [r.status, r.delta]));
118      const d1map = Object.fromEntries(statusD1.map(r => [r.status, r.delta]));
119      const totalMap = Object.fromEntries(statusTotals.map(r => [r.status, r.total]));
120      // Same order as npm run status (SITE_STATUS_ORDER in error-categories.js)
121      const SITE_STATUS_ORDER = [
122        'found',
123        'ignored',
124        'assets_captured',
125        'prog_scored',
126        'semantic_scored',
127        'vision_scored',
128        'enriched',
129        'proposals_drafted',
130        'outreach_partial',
131        'outreach_sent',
132        'high_score',
133        'failing',
134      ];
135      const seenStatuses = new Set();
136      const statusTree = [];
137      for (const status of SITE_STATUS_ORDER) {
138        if (totalMap[status] !== undefined) {
139          seenStatuses.add(status);
140          statusTree.push({
141            status,
142            total: totalMap[status],
143            delta_24h: d24map[status] || 0,
144            delta_1h: d1map[status] || 0,
145            children: null,
146          });
147        }
148      }
149      for (const r of statusTotals) {
150        if (!seenStatuses.has(r.status)) {
151          statusTree.push({
152            status: r.status,
153            total: r.total,
154            delta_24h: d24map[r.status] || 0,
155            delta_1h: d1map[r.status] || 0,
156            children: null,
157          });
158        }
159      }
160  
161      // Cross-tab: outreach contact_method × status
162      const outreachCrossRows = await getAll(
163        `SELECT contact_method, COALESCE(delivery_status, approval_status) AS status, COUNT(*) AS total
164         FROM messages WHERE direction = 'outbound'
165         GROUP BY contact_method, status`
166      );
167      const outreachSentTodayRow = await getOne(
168        `SELECT COUNT(*) AS n FROM messages
169         WHERE direction = 'outbound'
170           AND delivery_status IN ('sent','delivered')
171           AND updated_at > NOW() - INTERVAL '24 hours'`
172      );
173      const outreachSentToday = outreachSentTodayRow?.n || 0;
174  
175      // Cross-tab: inbound messages intent × sentiment
176      const convCrossRows = await getAll(
177        `SELECT COALESCE(sentiment,'unknown') AS sentiment,
178                COALESCE(intent,'unknown') AS intent,
179                COUNT(*) AS total
180         FROM messages
181         WHERE direction = 'inbound'
182         GROUP BY sentiment, intent`
183      );
184      const convChannelRows = await getAll(
185        `SELECT contact_method AS channel, COUNT(*) AS total,
186                0 AS replied
187         FROM messages WHERE direction='inbound'
188         GROUP BY contact_method ORDER BY total DESC`
189      );
190  
191      // Compute actable counts (same logic as getActionableQueues in status.js)
192      const actableMap = {};
193      try {
194        const blockedCountries = (process.env.OUTREACH_BLOCKED_COUNTRIES || '')
195          .split(',')
196          .map(s => s.trim().toUpperCase())
197          .filter(Boolean);
198        const skipMethods = (process.env.OUTREACH_SKIP_METHODS || '')
199          .split(',')
200          .map(s => s.trim().toLowerCase())
201          .filter(Boolean);
202        const englishOnlyMarkets = (process.env.ENGLISH_ONLY_MARKETS || '')
203          .split(',')
204          .map(s => s.trim().toUpperCase())
205          .filter(Boolean);
206        const lowScoreCutoff = parseFloat(process.env.LOW_SCORE_CUTOFF || '82');
207        const eligibleChannels = ['email', 'sms'].filter(ch => !skipMethods.includes(ch));
208        const blockedSet = new Set(blockedCountries);
209        const templatesDir = join(projectRoot, 'data', 'templates');
210        const templateCountries = new Set(
211          existsSync(templatesDir)
212            ? readdirSync(templatesDir)
213                .filter(cc => existsSync(join(templatesDir, cc, 'email.json')))
214                .map(cc => cc.toUpperCase())
215            : []
216        );
217        const eligibleCodes = [...templateCountries].filter(cc => !blockedSet.has(cc));
218        const englishActiveCodes = englishOnlyMarkets.filter(cc => !blockedSet.has(cc));
219  
220        // scoring stages
221        if (englishActiveCodes.length > 0) {
222          const ccP = englishActiveCodes.map((_, i) => `$${i + 2}`).join(',');
223          for (const status of ['semantic_scored', 'vision_scored', 'prog_scored']) {
224            const row = await getOne(
225              `SELECT COUNT(*) as n FROM sites WHERE status = $1 AND UPPER(country_code) IN (${ccP})`,
226              [status, ...englishActiveCodes]
227            );
228            actableMap[status] = row?.n || 0;
229          }
230        }
231        // enriched intermediate
232        if (englishActiveCodes.length > 0) {
233          const ccP = englishActiveCodes.map((_, i) => `$${i + 3}`).join(',');
234          for (const status of ['enriched_regex', 'enriched_llm']) {
235            const row = await getOne(
236              `SELECT COUNT(*) as n FROM sites WHERE status = $1 AND (score IS NULL OR score < $2) AND UPPER(country_code) IN (${ccP})`,
237              [status, lowScoreCutoff, ...englishActiveCodes]
238            );
239            actableMap[status] = row?.n || 0;
240          }
241        }
242        // enriched (gate 3)
243        if (eligibleCodes.length > 0) {
244          const ccP = eligibleCodes.map((_, i) => `$${i + 2}`).join(',');
245          const row = await getOne(
246            `SELECT COUNT(*) as n FROM sites WHERE status = 'enriched' AND (score IS NULL OR score < $1) AND UPPER(country_code) IN (${ccP})`,
247            [lowScoreCutoff, ...eligibleCodes]
248          );
249          actableMap['enriched'] = row?.n || 0;
250        }
251        // proposals_drafted (gate 2)
252        if (eligibleChannels.length > 0 && eligibleCodes.length > 0) {
253          const chP = eligibleChannels.map((_, i) => `$${i + 1}`).join(',');
254          const ccP = eligibleCodes.map((_, i) => `$${eligibleChannels.length + i + 1}`).join(',');
255          const row = await getOne(
256            `SELECT COUNT(DISTINCT m.site_id) as n FROM messages m JOIN sites s ON m.site_id=s.id
257             WHERE s.status = 'proposals_drafted'
258               AND m.direction='outbound' AND m.message_type='outreach'
259               AND m.reworded_at IS NULL AND m.sent_at IS NULL
260               AND m.delivery_status IS NULL
261               AND m.contact_method IN (${chP})
262               AND UPPER(s.country_code) IN (${ccP})`,
263            [...eligibleChannels, ...eligibleCodes]
264          );
265          actableMap['proposals_drafted'] = row?.n || 0;
266        }
267      } catch (_e) {
268        logger.warn('Could not compute actable counts:', _e.message);
269      }
270  
271      // Attach actable to statusTree rows
272      for (const row of statusTree) {
273        row.actable = actableMap[row.status] ?? null;
274      }
275  
276      // Quick lookup for summary generation
277      const statusCounts = statusTree.reduce((acc, row) => {
278        acc[row.status] = row.total;
279        return acc;
280      }, {});
281  
282      return {
283        totalSites: total,
284        newSites,
285        statusTree,
286        outreachCrossRows,
287        outreachSentToday,
288        convCrossRows,
289        convChannelRows,
290        // Fields used by generateSummary()
291        assetsCaptured: statusCounts.assets_captured || 0,
292        failing: statusCounts.failing || 0,
293        found: statusCounts.found || 0,
294        ignore: statusCounts.ignore || 0,
295      };
296    } catch (error) {
297      logger.warn('Could not fetch database changes:', error.message);
298      return null;
299    }
300  }
301  
302  /**
303   * Get code quality metrics
304   */
305  async function getCodeQualityMetrics() {
306    try {
307      // Check for new TODOs
308      let newTodos = 0;
309  
310      try {
311        // Count commits mentioning TODO/FIXME (fast, no full diff needed)
312        const { stdout: todosRaw } = await execAsync(
313          'git log --since="24 hours ago" --oneline --grep="TODO\\|FIXME" | wc -l',
314          { cwd: projectRoot, shell: '/bin/bash', timeout: 10000 }
315        );
316        newTodos = parseInt(todosRaw.trim()) || 0;
317      } catch (_error) {
318        newTodos = 0;
319      }
320  
321      // Read most recent test results (if available)
322      // For now, return placeholder values
323      return {
324        coverage: 82, // Last known coverage
325        coverageChange: undefined, // Would need historical tracking
326        failingTests: 0,
327        newTodos,
328      };
329    } catch (_error) {
330      logger.warn('Could not fetch code quality metrics:', _error.message);
331      return {
332        coverage: null,
333        coverageChange: null,
334        failingTests: null,
335        newTodos: 0,
336      };
337    }
338  }
339  
340  /**
341   * Get system health (recent errors from logs)
342   */
343  function getSystemHealth() {
344    try {
345      const today = new Date().toISOString().split('T')[0]; // YYYY-MM-DD
346      const logFiles = [
347        `logs/pipeline-${today}.log`,
348        `logs/outreach-${today}.log`,
349        `logs/inbound-${today}.log`,
350      ];
351  
352      const errors = [];
353  
354      for (const logFile of logFiles) {
355        const logPath = join(projectRoot, logFile);
356  
357        // eslint-disable-next-line security/detect-non-literal-fs-filename -- Dynamic log file path
358        if (existsSync(logPath)) {
359          try {
360            const content = readFileSync(logPath, 'utf-8');
361            const lines = content.split('\n');
362  
363            // Extract error lines (simple grep-like approach)
364            const errorLines = lines
365              .filter(line => line.includes('[ERROR]') && !line.includes('test')) // Exclude test errors
366              .slice(-10); // Last 10 errors
367  
368            errorLines.forEach(line => {
369              // Extract error message (simplified)
370              const match = line.match(/\[ERROR\]\s+(.+?)(?:\n|$)/);
371              if (match) {
372                errors.push({ message: match[1].substring(0, 100) }); // Truncate long messages
373              }
374            });
375          } catch (error) {
376            logger.warn(`Could not read log file ${logFile}`);
377          }
378        }
379      }
380  
381      // Deduplicate similar errors
382      const uniqueErrors = [];
383      const seen = new Set();
384  
385      errors.forEach(error => {
386        const key = error.message.substring(0, 50); // Use first 50 chars as key
387        if (!seen.has(key)) {
388          seen.add(key);
389          uniqueErrors.push(error);
390        }
391      });
392  
393      return {
394        errors: uniqueErrors,
395      };
396    } catch (_error) {
397      logger.warn('Could not fetch system health:', _error.message);
398      return {
399        errors: [],
400      };
401    }
402  }
403  
404  /**
405   * Generate executive summary from collected data
406   */
407  function generateSummary(gitActivity, dbChanges, codeQuality, systemHealth) {
408    const shipped = [];
409    const nextSteps = [];
410  
411    // What shipped
412    if (gitActivity.commits.length > 0) {
413      const commitCount = gitActivity.commits.length;
414  
415      // Summarize commits by type
416      const features = gitActivity.commits.filter(c => c.message.startsWith('feat')).length;
417      const fixes = gitActivity.commits.filter(c => c.message.startsWith('fix')).length;
418      const tests = gitActivity.commits.filter(c => c.message.includes('test')).length;
419  
420      if (features > 0) {
421        shipped.push(`${features} new feature${features > 1 ? 's' : ''} implemented`);
422      }
423  
424      if (fixes > 0) {
425        shipped.push(`${fixes} bug fix${fixes > 1 ? 'es' : ''} deployed`);
426      }
427  
428      if (tests > 0) {
429        shipped.push(`${tests} test improvement${tests > 1 ? 's' : ''} added`);
430      }
431  
432      if (codeQuality?.coverageChange > 0) {
433        shipped.push(`Test coverage increased by ${codeQuality.coverageChange}%`);
434      }
435  
436      if (gitActivity.filesChanged > 0) {
437        shipped.push(
438          `${gitActivity.filesChanged} files modified (+${gitActivity.linesAdded.toLocaleString()}, -${gitActivity.linesRemoved.toLocaleString()} lines)`
439        );
440      }
441  
442      if (shipped.length === 0 && commitCount > 0) {
443        shipped.push(`${commitCount} commit${commitCount > 1 ? 's' : ''} merged`);
444      }
445    }
446  
447    if (dbChanges?.newSites > 0) {
448      shipped.push(
449        `Pipeline processed ${dbChanges.newSites.toLocaleString()} new sites (${dbChanges.assetsCaptured} captured)`
450      );
451    }
452  
453    if (shipped.length === 0) {
454      shipped.push('Maintenance and monitoring (no major changes)');
455    }
456  
457    // Next steps
458    if (systemHealth?.errors?.length > 0) {
459      nextSteps.push(
460        `Investigate ${systemHealth.errors.length} recent error${systemHealth.errors.length > 1 ? 's' : ''} in logs`
461      );
462    }
463  
464    if (codeQuality?.coverage < 80) {
465      nextSteps.push(`Improve test coverage to 80%+ target (currently ${codeQuality.coverage}%)`);
466    }
467  
468    if (dbChanges?.failing > 0) {
469      nextSteps.push(`Review ${dbChanges.failing} failing sites for retry/debugging`);
470    }
471  
472    if (nextSteps.length === 0) {
473      nextSteps.push('Continue with planned roadmap items');
474      nextSteps.push('Monitor system performance and error rates');
475    }
476  
477    return {
478      shipped,
479      nextSteps,
480    };
481  }
482  
483  /**
484   * Main execution
485   */
486  async function main() {
487    try {
488      logger.info('Collecting data for daily progress report...');
489  
490      // Gather all data
491      const gitActivity = await getGitActivity();
492      const dbChanges = await getDatabaseChanges();
493      const codeQuality = await getCodeQualityMetrics();
494      const systemHealth = getSystemHealth();
495  
496      // Generate summary
497      const summary = generateSummary(gitActivity, dbChanges, codeQuality, systemHealth);
498  
499      logger.info('Generating HTML report...');
500  
501      // Generate HTML report
502      const filepath = await generateDailyReport({
503        summary,
504        gitActivity,
505        dbChanges,
506        codeQuality,
507        systemHealth,
508      });
509  
510      console.log('\nDaily Progress Report Generated\n');
511      console.log(`Location: ${filepath}\n`);
512      console.log('Summary:');
513      console.log(`  ${gitActivity.commits.length} commits in last 24 hours`);
514      console.log(`  ${dbChanges?.newSites || 0} new sites processed`);
515      console.log(`  ${systemHealth.errors.length} errors logged\n`);
516      console.log(`Open in browser: file://${filepath}\n`);
517  
518      process.exit(0);
519    } catch (error) {
520      logger.error('Failed to generate daily report:', error);
521      process.exit(1);
522    } finally {
523      await closePool();
524    }
525  }
526  
527  main();