/**
 * Compliance Service
 * Tracks all automated actions and generates daily reports for admin
 */

import { PrismaClient } from '@prisma/client';
import { EmailService as TransactionalEmailService } from './email';

const prisma = new PrismaClient();

export type ComplianceCategory = 'EMAIL' | 'CONTENT' | 'AGENT' | 'SYSTEM' | 'USER' | 'INGESTION';
export type ComplianceStatus = 'SUCCESS' | 'FAILED' | 'BLOCKED';

export class ComplianceService {
  /**
   * Log a compliance event. Wrapped in try/catch to never break calling code.
   */
  static async log(
    category: ComplianceCategory,
    action: string,
    source: string,
    details?: Record<string, any>,
    status: ComplianceStatus = 'SUCCESS'
  ): Promise<void> {
    try {
      await prisma.complianceLog.create({
        data: {
          category,
          action,
          source,
          details: details ? JSON.stringify(details) : null,
          status,
        },
      });
    } catch (error) {
      // Never let compliance logging break the calling service
      console.error('[ComplianceService] Failed to log:', error);
    }
  }

  /**
   * Get all logs for a given day, aggregated by category
   */
  static async getDailyReport(date?: Date): Promise<DailyReport> {
    const targetDate = date || new Date();
    const startOfDay = new Date(targetDate);
    startOfDay.setHours(0, 0, 0, 0);
    const endOfDay = new Date(targetDate);
    endOfDay.setHours(23, 59, 59, 999);

    const logs = await prisma.complianceLog.findMany({
      where: {
        createdAt: { gte: startOfDay, lte: endOfDay },
      },
      orderBy: { createdAt: 'asc' },
    });

    // Aggregate by category
    const emailLogs = logs.filter(l => l.category === 'EMAIL');
    const contentLogs = logs.filter(l => l.category === 'CONTENT');
    const agentLogs = logs.filter(l => l.category === 'AGENT');
    const systemLogs = logs.filter(l => l.category === 'SYSTEM');
    const userLogs = logs.filter(l => l.category === 'USER');
    const ingestionLogs = logs.filter(l => l.category === 'INGESTION');

    // Email summary
    const emailSummary = this.aggregateByAction(emailLogs);

    // Content summary
    const contentSummary = this.aggregateByAction(contentLogs);

    // Agent summary
    const agentSummary = this.aggregateBySource(agentLogs);

    // System summary
    const systemSummary = this.aggregateByAction(systemLogs);

    // User summary
    const userSummary = this.aggregateByAction(userLogs);

    // Ingestion summary (cron jobs via cron-wrapper.sh)
    const ingestionSummary = this.aggregateIngestionLogs(ingestionLogs);

    // Gather live platform stats (replaces cron-seo-status-email.sh)
    const platformStats = await this.getPlatformStats();

    // Gather sports database record counts
    const sportsDbStats = await this.getSportsDbStats();

    // Scan log files for recent errors
    const logErrors = await this.scanLogErrors(startOfDay);

    return {
      date: startOfDay.toISOString().split('T')[0],
      totalEvents: logs.length,
      successCount: logs.filter(l => l.status === 'SUCCESS').length,
      failedCount: logs.filter(l => l.status === 'FAILED').length,
      blockedCount: logs.filter(l => l.status === 'BLOCKED').length,
      email: emailSummary,
      content: contentSummary,
      agent: agentSummary,
      system: systemSummary,
      user: userSummary,
      ingestion: ingestionSummary,
      platform: platformStats,
      sportsDb: sportsDbStats,
      logErrors,
    };
  }

  /**
   * Gather live platform stats from the database (replaces cron-seo-status-email.sh)
   */
  private static async getPlatformStats(): Promise<PlatformStats> {
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    const stats: PlatformStats = {
      gamePages: { total: 0, today: 0, published: 0 },
      seoPages: { total: 0 },
      alerts: { active: 0, sentToday: 0 },
      newsletterSubscribers: 0,
      workers: { online: 0, total: 0, details: '' },
    };

    try {
      stats.gamePages.total = await prisma.gamePage.count().catch(() => 0);
      stats.gamePages.today = await prisma.gamePage.count({ where: { createdAt: { gte: today } } }).catch(() => 0);
      stats.gamePages.published = await prisma.gamePage.count({ where: { status: 'PUBLISHED' } }).catch(() => 0);
    } catch {}

    try {
      stats.seoPages.total = await prisma.sEOPage.count().catch(() => 0);
    } catch {}

    try {
      stats.alerts.active = await prisma.oddsAlert.count({ where: { isActive: true } }).catch(() => 0);
      stats.alerts.sentToday = await prisma.alertHistory.count({ where: { sentAt: { gte: today } } }).catch(() => 0);
    } catch {}

    try {
      stats.newsletterSubscribers = await prisma.newsletterSubscriber.count({ where: { isConfirmed: true } }).catch(() => 0);
    } catch {}

    // PM2 worker status
    try {
      const { execSync } = require('child_process');
      const pm2Json = execSync('pm2 jlist 2>/dev/null', { encoding: 'utf8', timeout: 5000 });
      const pm2List = JSON.parse(pm2Json);
      const seoWorkers = pm2List.filter((w: any) => w.name?.startsWith('seo-') || w.name === 'compliance-worker');
      stats.workers.total = seoWorkers.length;
      stats.workers.online = seoWorkers.filter((w: any) => w.pm2_env?.status === 'online').length;
      stats.workers.details = seoWorkers.map((w: any) => `${w.name}: ${w.pm2_env?.status || 'unknown'}`).join(', ');
    } catch {}

    return stats;
  }

  /**
   * Aggregate ingestion (cron job) logs into per-job summaries
   */
  private static aggregateIngestionLogs(logs: any[]): IngestionJobSummary[] {
    const byJob: Record<string, { runs: number; success: number; failed: number; totalDuration: number; lastOutput: string }> = {};

    for (const log of logs) {
      let details: any = {};
      try { details = JSON.parse(log.details || '{}'); } catch {}

      const jobName = details.job || log.source || 'unknown';
      if (!byJob[jobName]) {
        byJob[jobName] = { runs: 0, success: 0, failed: 0, totalDuration: 0, lastOutput: '' };
      }
      byJob[jobName].runs++;
      if (log.status === 'SUCCESS') byJob[jobName].success++;
      if (log.status === 'FAILED') byJob[jobName].failed++;
      byJob[jobName].totalDuration += details.durationSec || 0;
      if (details.output) byJob[jobName].lastOutput = details.output;
    }

    return Object.entries(byJob).map(([name, data]) => ({
      name,
      runs: data.runs,
      success: data.success,
      failed: data.failed,
      avgDuration: data.runs > 0 ? Math.round(data.totalDuration / data.runs) : 0,
      lastOutput: data.lastOutput.slice(0, 200),
    }));
  }

  /**
   * Query the sports PostgreSQL database for record counts
   */
  private static async getSportsDbStats(): Promise<SportsDbStats> {
    const stats: SportsDbStats = {
      tables: [],
      todayInserts: [],
      gamesByLeague: [],
      propsByLeague: [],
      oddsByBookmaker: [],
      oddsSnapshots: { total: 0, today: 0, leagues: [] },
      unifiedOdds: {
        bookmakerOdds: { total: 0, today: 0, byLeague: [] },
        oddsHistory: { total: 0, today: 0 },
        ingestionRuns: [],
        bookmakers: [],
      },
    };

    try {
      const { execSync } = require('child_process');
      const connStr = process.env.SPORTS_DATABASE_URL;
      if (!connStr) return stats;

      const url = new URL(connStr);
      const psqlEnv = `PGPASSWORD='${url.password}'`;
      const dbName = url.pathname.slice(1).split('?')[0];
      const psqlConn = `-h ${url.hostname} -p ${url.port || 5432} -U ${url.username} -d ${dbName}`;

      // Row counts for key tables — use pg_stat_user_tables estimates (instant) instead of COUNT(*)
      // which times out on large tables like PlayerPropLine (94.6M rows).
      // Estimates are accurate after VACUUM ANALYZE.
      const tables = ['SportsGame', 'Player', 'PlayerGameMetric', 'PlayerPropLine', 'PlayerInjury', 'ExternalFeedRecord', 'GameOdds', 'OddsSnapshot', 'TeamGameMetric', 'GameWeather', 'BookmakerOdds', 'OddsHistoryV2', 'OddsIngestion'];
      const tableList = tables.map(t => `'${t}'`).join(',');
      const countQuery = `SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE relname IN (${tableList}) ORDER BY relname`;
      const countResult = execSync(
        `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${countQuery}"`,
        { encoding: 'utf8', timeout: 10000, shell: '/bin/bash' }
      ).trim();

      for (const line of countResult.split('\n')) {
        const [table, count] = line.split('|');
        if (table && count) {
          stats.tables.push({ table: table.trim(), count: parseInt(count.trim()) || 0 });
        }
      }

      // Records inserted/updated today - query each table individually to handle missing columns
      const todayTables = [
        { name: 'SportsGame', col: 'createdAt' },
        { name: 'PlayerGameMetric', col: 'createdAt' },
        { name: 'PlayerPropLine', col: 'createdAt' },
        { name: 'ExternalFeedRecord', col: 'createdAt' },
        { name: 'GameOdds', col: 'fetchedAt' },
        { name: 'OddsSnapshot', col: 'createdAt' },
        { name: 'PlayerInjury', col: 'updatedAt' },
        { name: 'BookmakerOdds', col: 'createdAt' },
        { name: 'OddsHistoryV2', col: 'createdAt' },
        { name: 'OddsIngestion', col: 'createdAt' },
      ];

      let todayResult = '';
      for (const t of todayTables) {
        try {
          const q = `SELECT '${t.name}' as tbl, COUNT(*)::bigint as cnt FROM \\"${t.name}\\" WHERE \\"${t.col}\\" >= CURRENT_DATE`;
          const r = execSync(
            `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${q}"`,
            { encoding: 'utf8', timeout: 60000, shell: '/bin/bash' }
          ).trim();
          if (r) todayResult += r + '\n';
        } catch {
          // Column may not exist for this table, skip it
        }
      }

      for (const line of todayResult.trim().split('\n')) {
        if (!line) continue;
        const [table, count] = line.split('|');
        if (table && count) {
          const cnt = parseInt(count.trim()) || 0;
          if (cnt > 0) stats.todayInserts.push({ table: table.trim(), count: cnt });
        }
      }

      // Games by league with odds breakdown
      try {
        const gamesQuery = `
          SELECT league,
                 COUNT(*)::bigint as total,
                 COUNT(CASE WHEN \\"createdAt\\" >= CURRENT_DATE THEN 1 END)::bigint as today,
                 COUNT(CASE WHEN \\"moneylineHome\\" IS NOT NULL THEN 1 END)::bigint as with_odds
          FROM \\"SportsGame\\"
          GROUP BY league
          ORDER BY total DESC
          LIMIT 15
        `;
        const gamesResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${gamesQuery}"`,
          { encoding: 'utf8', timeout: 15000, shell: '/bin/bash' }
        ).trim();
        for (const line of gamesResult.split('\n')) {
          if (!line) continue;
          const [league, total, today, withOdds] = line.split('|');
          if (league) {
            stats.gamesByLeague.push({
              league: league.trim().toUpperCase(),
              total: parseInt(total) || 0,
              today: parseInt(today) || 0,
              withOdds: parseInt(withOdds) || 0,
            });
          }
        }
      } catch {}

      // Props by league — split into two fast queries instead of one massive GROUP BY on 94.6M rows:
      // 1) Total per league: COUNT only today's rows (fast, indexed) + use pg_stat for overall total
      // 2) Today's inserts: filtered COUNT on createdAt >= CURRENT_DATE
      try {
        // Get overall total from pg_stat (instant)
        const pplTotalQuery = `SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'PlayerPropLine'`;
        const pplTotalResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -c "${pplTotalQuery}"`,
          { encoding: 'utf8', timeout: 5000, shell: '/bin/bash' }
        ).trim();
        const pplTotal = parseInt(pplTotalResult) || 0;

        // Get today's props by league (only scans today's rows, much faster)
        const propsTodayQuery = `
          SELECT league, COUNT(*)::bigint as today
          FROM \\"PlayerPropLine\\"
          WHERE \\"createdAt\\" >= CURRENT_DATE
          GROUP BY league
          ORDER BY today DESC
          LIMIT 10
        `;
        const propsTodayResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${propsTodayQuery}"`,
          { encoding: 'utf8', timeout: 60000, shell: '/bin/bash' }
        ).trim();

        // Build league breakdown from today's data + note overall total
        let todaySum = 0;
        const leagueToday: Array<{ league: string; today: number }> = [];
        for (const line of propsTodayResult.split('\n')) {
          if (!line) continue;
          const [league, today] = line.split('|');
          if (league) {
            const cnt = parseInt(today) || 0;
            todaySum += cnt;
            leagueToday.push({ league: league.trim().toUpperCase(), today: cnt });
          }
        }

        // Use the total from pg_stat, distribute proportionally or just show today's leagues
        for (const lt of leagueToday) {
          stats.propsByLeague.push({
            league: lt.league,
            total: pplTotal, // Overall table total (approximate)
            today: lt.today,
          });
        }

        // If no today's data, still show overall total
        if (leagueToday.length === 0) {
          stats.propsByLeague.push({
            league: 'ALL',
            total: pplTotal,
            today: 0,
          });
        }
      } catch {}

      // Odds by bookmaker (from GameOddsAltLine)
      try {
        const bookmakerQuery = `
          SELECT COALESCE(bookmaker, source, 'unknown') as bk, COUNT(*)::bigint as cnt
          FROM \\"GameOddsAltLine\\"
          WHERE \\"fetchedAt\\" >= CURRENT_DATE - INTERVAL '7 days'
          GROUP BY COALESCE(bookmaker, source, 'unknown')
          ORDER BY cnt DESC
          LIMIT 10
        `;
        const bookmakerResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${bookmakerQuery}"`,
          { encoding: 'utf8', timeout: 10000, shell: '/bin/bash' }
        ).trim();
        for (const line of bookmakerResult.split('\n')) {
          if (!line) continue;
          const [bookmaker, count] = line.split('|');
          if (bookmaker) {
            stats.oddsByBookmaker.push({
              bookmaker: bookmaker.trim(),
              count: parseInt(count) || 0,
            });
          }
        }
      } catch {}

      // Odds snapshots summary — use pg_stat for total, filtered COUNT for today
      try {
        const snapshotTotalQuery = `SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'OddsSnapshot'`;
        const snapshotTodayQuery = `SELECT COUNT(*)::bigint FROM \\"OddsSnapshot\\" WHERE \\"createdAt\\" >= CURRENT_DATE`;
        const snapshotQuery = `SELECT (${snapshotTotalQuery})::bigint as total, (${snapshotTodayQuery})::bigint as today`;
        const snapshotResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${snapshotQuery}"`,
          { encoding: 'utf8', timeout: 30000, shell: '/bin/bash' }
        ).trim();
        const [total, today] = snapshotResult.split('|');
        stats.oddsSnapshots.total = parseInt(total) || 0;
        stats.oddsSnapshots.today = parseInt(today) || 0;

        // Get leagues with recent snapshots
        const leaguesQuery = `
          SELECT DISTINCT league FROM \\"OddsSnapshot\\"
          WHERE \\"createdAt\\" >= CURRENT_DATE
          ORDER BY league LIMIT 10
        `;
        const leaguesResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -c "${leaguesQuery}"`,
          { encoding: 'utf8', timeout: 5000, shell: '/bin/bash' }
        ).trim();
        stats.oddsSnapshots.leagues = leaguesResult.split('\n').filter(Boolean).map(l => l.trim().toUpperCase());
      } catch {}

      // === Unified Odds Schema (v2) ===

      // BookmakerOdds totals — use pg_stat for total, filtered COUNT for today
      try {
        const bmStatQuery = `SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'BookmakerOdds'`;
        const bmTodayQuery = `SELECT COUNT(*)::bigint FROM \\"BookmakerOdds\\" WHERE \\"createdAt\\" >= CURRENT_DATE`;
        const bmOddsQuery = `SELECT (${bmStatQuery})::bigint as total, (${bmTodayQuery})::bigint as today`;
        const bmOddsResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${bmOddsQuery}"`,
          { encoding: 'utf8', timeout: 15000, shell: '/bin/bash' }
        ).trim();
        const [total, today] = bmOddsResult.split('|');
        stats.unifiedOdds.bookmakerOdds.total = parseInt(total) || 0;
        stats.unifiedOdds.bookmakerOdds.today = parseInt(today) || 0;

        // BookmakerOdds by league
        const bmLeagueQuery = `
          SELECT league, COUNT(*)::bigint as cnt
          FROM \\"BookmakerOdds\\"
          GROUP BY league ORDER BY cnt DESC LIMIT 10
        `;
        const bmLeagueResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${bmLeagueQuery}"`,
          { encoding: 'utf8', timeout: 15000, shell: '/bin/bash' }
        ).trim();
        for (const line of bmLeagueResult.split('\n')) {
          if (!line) continue;
          const [league, count] = line.split('|');
          if (league) {
            stats.unifiedOdds.bookmakerOdds.byLeague.push({
              league: league.trim().toUpperCase(),
              count: parseInt(count) || 0,
            });
          }
        }
      } catch {}

      // OddsHistoryV2 totals — use pg_stat for total (4.6M rows), filtered COUNT for today
      try {
        const histStatQuery = `SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'OddsHistoryV2'`;
        const histTodayQuery = `SELECT COUNT(*)::bigint FROM \\"OddsHistoryV2\\" WHERE \\"createdAt\\" >= CURRENT_DATE`;
        const historyQuery = `SELECT (${histStatQuery})::bigint as total, (${histTodayQuery})::bigint as today`;
        const historyResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${historyQuery}"`,
          { encoding: 'utf8', timeout: 30000, shell: '/bin/bash' }
        ).trim();
        const [total, today] = historyResult.split('|');
        stats.unifiedOdds.oddsHistory.total = parseInt(total) || 0;
        stats.unifiedOdds.oddsHistory.today = parseInt(today) || 0;
      } catch {}

      // Recent OddsIngestion runs
      try {
        const ingestionQuery = `
          SELECT source, league, status, \\"oddsCreated\\"::bigint, \\"startedAt\\"
          FROM \\"OddsIngestion\\"
          WHERE \\"startedAt\\" >= CURRENT_DATE - INTERVAL '1 day'
          ORDER BY \\"startedAt\\" DESC
          LIMIT 10
        `;
        const ingestionResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${ingestionQuery}"`,
          { encoding: 'utf8', timeout: 10000, shell: '/bin/bash' }
        ).trim();
        for (const line of ingestionResult.split('\n')) {
          if (!line) continue;
          const [source, league, status, oddsCreated, startedAt] = line.split('|');
          if (source) {
            stats.unifiedOdds.ingestionRuns.push({
              source: source.trim(),
              league: league?.trim()?.toUpperCase() || '',
              status: status?.trim() || 'unknown',
              oddsCreated: parseInt(oddsCreated) || 0,
              startedAt: startedAt?.trim() || '',
            });
          }
        }
      } catch {}

      // BookmakerMeta list
      try {
        const bookmakerQuery = `
          SELECT slug, name, \\"isSharp\\"::text, region
          FROM \\"BookmakerMeta\\"
          WHERE \\"isActive\\" = true
          ORDER BY \\"isSharp\\" DESC, slug
          LIMIT 20
        `;
        const bookmakerResult = execSync(
          `${psqlEnv} psql ${psqlConn} -t -A -F'|' -c "${bookmakerQuery}"`,
          { encoding: 'utf8', timeout: 5000, shell: '/bin/bash' }
        ).trim();
        for (const line of bookmakerResult.split('\n')) {
          if (!line) continue;
          const [slug, name, isSharp, region] = line.split('|');
          if (slug) {
            stats.unifiedOdds.bookmakers.push({
              slug: slug.trim(),
              name: name?.trim() || slug.trim(),
              isSharp: isSharp?.trim() === 'true',
              region: region?.trim() || 'US',
            });
          }
        }
      } catch {}

    } catch (error) {
      console.error('[ComplianceService] Failed to query sports DB stats:', error);
    }

    return stats;
  }

  /**
   * Scan log files for errors from the given day
   */
  private static async scanLogErrors(sinceDate: Date): Promise<LogError[]> {
    const errors: LogError[] = [];
    const dateStr = sinceDate.toISOString().split('T')[0];

    try {
      const { execSync } = require('child_process');
      const fs = require('fs');

      const logFiles: string[] = [];

      // /var/log/eventheodds/*.log
      const logDir = '/var/log/eventheodds';
      if (fs.existsSync(logDir)) {
        const files = fs.readdirSync(logDir).filter((f: string) => f.endsWith('.log'));
        logFiles.push(...files.map((f: string) => `${logDir}/${f}`));
      }

      // /var/log/eventheodds-*.log
      try {
        const topLevel = execSync('ls /var/log/eventheodds-*.log 2>/dev/null', { encoding: 'utf8', timeout: 3000 }).trim().split('\n');
        logFiles.push(...topLevel.filter(Boolean));
      } catch {}

      for (const file of logFiles) {
        try {
          // Match real errors, not JSON fields like "errors":0
          // Look for: Error:, FAIL, Exception, Traceback, "success":false, error messages
          const result = execSync(
            `grep "${dateStr}" "${file}" 2>/dev/null | grep -iE '(\\bError:|\\bFAIL\\b|\\bException\\b|\\bTraceback\\b|"success":\\s*false|ENOENT|ETIMEDOUT|ECONNREFUSED|exit code [1-9]|Cannot find|permission denied)' | grep -v '"errors":0' | tail -5`,
            { encoding: 'utf8', timeout: 3000 }
          ).trim();

          if (result) {
            for (const line of result.split('\n').filter(Boolean)) {
              errors.push({ file: file.replace(/.*\//, ''), line: line.slice(0, 300) });
            }
          }
        } catch {
          // grep exit 1 = no matches, that's fine
        }
      }
    } catch (error) {
      console.error('[ComplianceService] Failed to scan log errors:', error);
    }

    return errors.slice(0, 50);
  }

  private static aggregateByAction(logs: any[]): Record<string, { total: number; success: number; failed: number; blocked: number }> {
    const result: Record<string, { total: number; success: number; failed: number; blocked: number }> = {};
    for (const log of logs) {
      if (!result[log.action]) {
        result[log.action] = { total: 0, success: 0, failed: 0, blocked: 0 };
      }
      result[log.action].total++;
      if (log.status === 'SUCCESS') result[log.action].success++;
      if (log.status === 'FAILED') result[log.action].failed++;
      if (log.status === 'BLOCKED') result[log.action].blocked++;
    }
    return result;
  }

  private static aggregateBySource(logs: any[]): Record<string, { total: number; success: number; failed: number; blocked: number; actions: Record<string, number> }> {
    const result: Record<string, { total: number; success: number; failed: number; blocked: number; actions: Record<string, number> }> = {};
    for (const log of logs) {
      if (!result[log.source]) {
        result[log.source] = { total: 0, success: 0, failed: 0, blocked: 0, actions: {} };
      }
      result[log.source].total++;
      if (log.status === 'SUCCESS') result[log.source].success++;
      if (log.status === 'FAILED') result[log.source].failed++;
      if (log.status === 'BLOCKED') result[log.source].blocked++;
      result[log.source].actions[log.action] = (result[log.source].actions[log.action] || 0) + 1;
    }
    return result;
  }

  /**
   * Generate HTML report for email
   */
  static generateReportHTML(report: DailyReport): string {
    const sectionHTML = (title: string, data: Record<string, any>) => {
      const rows = Object.entries(data)
        .map(([key, val]: [string, any]) => {
          const success = val.success ?? 0;
          const failed = val.failed ?? 0;
          const blocked = val.blocked ?? 0;
          const total = val.total ?? 0;
          return `<tr>
            <td style="padding:6px 12px;border-bottom:1px solid #eee;">${key}</td>
            <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;">${total}</td>
            <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;color:#28a745;">${success}</td>
            <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;color:#dc3545;">${failed}</td>
            <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;color:#ffc107;">${blocked}</td>
          </tr>`;
        })
        .join('');

      if (!rows) return '';

      return `
        <h2 style="color:#333;margin-top:24px;border-bottom:2px solid #667eea;padding-bottom:6px;">${title}</h2>
        <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
          <thead>
            <tr style="background:#f8f9fa;">
              <th style="padding:8px 12px;text-align:left;">Action / Source</th>
              <th style="padding:8px 12px;text-align:center;">Total</th>
              <th style="padding:8px 12px;text-align:center;">Success</th>
              <th style="padding:8px 12px;text-align:center;">Failed</th>
              <th style="padding:8px 12px;text-align:center;">Blocked</th>
            </tr>
          </thead>
          <tbody>${rows}</tbody>
        </table>`;
    };

    return `<!DOCTYPE html>
<html>
<head><meta charset="utf-8"><title>Daily Compliance Report</title></head>
<body style="font-family:Arial,sans-serif;line-height:1.6;color:#333;max-width:700px;margin:0 auto;padding:20px;">
  <div style="background:linear-gradient(135deg,#667eea 0%,#764ba2 100%);color:white;padding:24px;border-radius:8px 8px 0 0;text-align:center;">
    <h1 style="margin:0;">Daily Compliance Report</h1>
    <p style="margin:8px 0 0;opacity:0.9;">${report.date} | EventheOdds.ai</p>
  </div>
  <div style="background:white;padding:24px;border:1px solid #ddd;border-top:none;border-radius:0 0 8px 8px;">
    <div style="display:flex;gap:16px;margin-bottom:24px;flex-wrap:wrap;">
      <div style="flex:1;min-width:120px;background:#f8f9fa;padding:16px;border-radius:8px;text-align:center;">
        <div style="font-size:28px;font-weight:bold;color:#667eea;">${report.totalEvents}</div>
        <div style="color:#666;font-size:13px;">Total Events</div>
      </div>
      <div style="flex:1;min-width:120px;background:#d4edda;padding:16px;border-radius:8px;text-align:center;">
        <div style="font-size:28px;font-weight:bold;color:#28a745;">${report.successCount}</div>
        <div style="color:#666;font-size:13px;">Success</div>
      </div>
      <div style="flex:1;min-width:120px;background:#f8d7da;padding:16px;border-radius:8px;text-align:center;">
        <div style="font-size:28px;font-weight:bold;color:#dc3545;">${report.failedCount}</div>
        <div style="color:#666;font-size:13px;">Failed</div>
      </div>
      <div style="flex:1;min-width:120px;background:#fff3cd;padding:16px;border-radius:8px;text-align:center;">
        <div style="font-size:28px;font-weight:bold;color:#ffc107;">${report.blockedCount}</div>
        <div style="color:#666;font-size:13px;">Blocked</div>
      </div>
    </div>

    <h2 style="color:#333;margin-top:24px;border-bottom:2px solid #667eea;padding-bottom:6px;">Platform Overview</h2>
    <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
      <tbody>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Game Pages (Today)</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;font-weight:bold;">${report.platform.gamePages.today}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Game Pages (Total)</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.gamePages.total}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Published Pages</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.gamePages.published}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">SEO Guide Pages</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.seoPages.total}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Active Odds Alerts</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.alerts.active}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Alerts Sent Today</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.alerts.sentToday}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Newsletter Subscribers</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.newsletterSubscribers}</td></tr>
        <tr><td style="padding:6px 12px;border-bottom:1px solid #eee;">Workers Online</td><td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:right;">${report.platform.workers.online} / ${report.platform.workers.total}</td></tr>
        ${report.platform.workers.details ? `<tr><td colspan="2" style="padding:6px 12px;border-bottom:1px solid #eee;font-size:12px;color:#666;">${report.platform.workers.details}</td></tr>` : ''}
      </tbody>
    </table>

    ${this.renderIngestionSection(report.ingestion)}
    ${this.renderSportsDbSection(report.sportsDb)}
    ${this.renderLogErrorsSection(report.logErrors)}

    ${sectionHTML('Email Activity', report.email)}
    ${sectionHTML('Content & Posts', report.content)}
    ${sectionHTML('Agent Activity', report.agent)}
    ${sectionHTML('System Events', report.system)}
    ${sectionHTML('User Activity', report.user)}

    ${report.totalEvents === 0 ? '<p style="text-align:center;color:#666;padding:24px;">No compliance events recorded for this date.</p>' : ''}
  </div>
  <div style="text-align:center;margin-top:16px;color:#999;font-size:12px;">
    <p>This is an automated compliance report from EventheOdds.ai</p>
    <p>Generated at ${new Date().toISOString()}</p>
  </div>
</body>
</html>`;
  }

  private static renderIngestionSection(jobs: IngestionJobSummary[]): string {
    if (!jobs.length) return '';

    const rows = jobs.map(j => {
      const statusColor = j.failed > 0 ? '#dc3545' : '#28a745';
      return `<tr>
        <td style="padding:6px 12px;border-bottom:1px solid #eee;">${j.name}</td>
        <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;">${j.runs}</td>
        <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;color:#28a745;">${j.success}</td>
        <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;color:${statusColor};">${j.failed}</td>
        <td style="padding:6px 12px;border-bottom:1px solid #eee;text-align:center;">${j.avgDuration}s</td>
      </tr>`;
    }).join('');

    return `
      <h2 style="color:#333;margin-top:24px;border-bottom:2px solid #667eea;padding-bottom:6px;">Data Ingestion (Cron Jobs)</h2>
      <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
        <thead>
          <tr style="background:#f8f9fa;">
            <th style="padding:8px 12px;text-align:left;">Job</th>
            <th style="padding:8px 12px;text-align:center;">Runs</th>
            <th style="padding:8px 12px;text-align:center;">OK</th>
            <th style="padding:8px 12px;text-align:center;">Failed</th>
            <th style="padding:8px 12px;text-align:center;">Avg Duration</th>
          </tr>
        </thead>
        <tbody>${rows}</tbody>
      </table>`;
  }

  private static renderSportsDbSection(stats: SportsDbStats): string {
    if (!stats.tables.length) return '';

    const totalRows = stats.tables.map(t => `<tr>
      <td style="padding:4px 12px;border-bottom:1px solid #eee;">${t.table}</td>
      <td style="padding:4px 12px;border-bottom:1px solid #eee;text-align:right;">${t.count.toLocaleString()}</td>
    </tr>`).join('');

    const todayRows = stats.todayInserts.length > 0
      ? stats.todayInserts.map(t => `<tr>
          <td style="padding:4px 12px;border-bottom:1px solid #eee;">${t.table}</td>
          <td style="padding:4px 12px;border-bottom:1px solid #eee;text-align:right;color:#28a745;font-weight:bold;">+${t.count.toLocaleString()}</td>
        </tr>`).join('')
      : '<tr><td colspan="2" style="padding:8px 12px;color:#999;">No new records today</td></tr>';

    // Games by league table
    const gamesByLeagueRows = stats.gamesByLeague.length > 0
      ? stats.gamesByLeague.map(g => `<tr>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;font-weight:500;">${g.league}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;">${g.total.toLocaleString()}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;color:#28a745;">${g.today > 0 ? '+' + g.today : '-'}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;color:#667eea;">${g.withOdds.toLocaleString()}</td>
        </tr>`).join('')
      : '';

    // Props by league table
    const propsByLeagueRows = stats.propsByLeague.length > 0
      ? stats.propsByLeague.map(p => `<tr>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;font-weight:500;">${p.league}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;">${p.total.toLocaleString()}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;color:#28a745;">${p.today > 0 ? '+' + p.today : '-'}</td>
        </tr>`).join('')
      : '';

    // Bookmaker breakdown
    const bookmakerRows = stats.oddsByBookmaker.length > 0
      ? stats.oddsByBookmaker.map(b => `<tr>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;">${b.bookmaker}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;">${b.count.toLocaleString()}</td>
        </tr>`).join('')
      : '';

    return `
      <h2 style="color:#333;margin-top:24px;border-bottom:2px solid #667eea;padding-bottom:6px;">Sports Database</h2>

      <!-- Summary Cards -->
      <div style="display:flex;gap:12px;margin-bottom:16px;flex-wrap:wrap;">
        <div style="flex:1;min-width:100px;background:#e3f2fd;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#1976d2;">${stats.oddsSnapshots.today.toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">Odds Snapshots Today</div>
        </div>
        <div style="flex:1;min-width:100px;background:#e8f5e9;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#388e3c;">${stats.todayInserts.reduce((sum, t) => sum + t.count, 0).toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">Total Records Today</div>
        </div>
        <div style="flex:1;min-width:100px;background:#fff3e0;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#f57c00;">${stats.gamesByLeague.reduce((sum, g) => sum + g.today, 0).toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">Games Added Today</div>
        </div>
        <div style="flex:1;min-width:100px;background:#fce4ec;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#c2185b;">${stats.propsByLeague.reduce((sum, p) => sum + p.today, 0).toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">Props Added Today</div>
        </div>
      </div>

      ${stats.oddsSnapshots.leagues.length > 0 ? `
      <div style="background:#f5f5f5;padding:8px 12px;border-radius:4px;margin-bottom:16px;font-size:12px;">
        <strong>Leagues with odds snapshots today:</strong> ${stats.oddsSnapshots.leagues.join(', ')}
      </div>
      ` : ''}

      <!-- Games by League -->
      ${gamesByLeagueRows ? `
      <h3 style="color:#555;font-size:14px;margin:16px 0 8px;">Games by League</h3>
      <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
        <thead>
          <tr style="background:#f8f9fa;">
            <th style="padding:6px 8px;text-align:left;font-size:12px;">League</th>
            <th style="padding:6px 8px;text-align:right;font-size:12px;">Total</th>
            <th style="padding:6px 8px;text-align:right;font-size:12px;">Today</th>
            <th style="padding:6px 8px;text-align:right;font-size:12px;">With Odds</th>
          </tr>
        </thead>
        <tbody>${gamesByLeagueRows}</tbody>
      </table>
      ` : ''}

      <!-- Props by League -->
      ${propsByLeagueRows ? `
      <h3 style="color:#555;font-size:14px;margin:16px 0 8px;">Player Props by League</h3>
      <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
        <thead>
          <tr style="background:#f8f9fa;">
            <th style="padding:6px 8px;text-align:left;font-size:12px;">League</th>
            <th style="padding:6px 8px;text-align:right;font-size:12px;">Total</th>
            <th style="padding:6px 8px;text-align:right;font-size:12px;">Today</th>
          </tr>
        </thead>
        <tbody>${propsByLeagueRows}</tbody>
      </table>
      ` : ''}

      <!-- Bookmaker Breakdown -->
      ${bookmakerRows ? `
      <h3 style="color:#555;font-size:14px;margin:16px 0 8px;">Odds by Bookmaker (Last 7 Days)</h3>
      <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
        <thead>
          <tr style="background:#f8f9fa;">
            <th style="padding:6px 8px;text-align:left;font-size:12px;">Bookmaker/Source</th>
            <th style="padding:6px 8px;text-align:right;font-size:12px;">Records</th>
          </tr>
        </thead>
        <tbody>${bookmakerRows}</tbody>
      </table>
      ` : ''}

      <div style="display:flex;gap:16px;flex-wrap:wrap;">
        <div style="flex:1;min-width:280px;">
          <h3 style="color:#555;font-size:14px;margin-bottom:8px;">Total Records (All Tables)</h3>
          <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
            <tbody>${totalRows}</tbody>
          </table>
        </div>
        <div style="flex:1;min-width:280px;">
          <h3 style="color:#555;font-size:14px;margin-bottom:8px;">Ingested Today (All Tables)</h3>
          <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
            <tbody>${todayRows}</tbody>
          </table>
        </div>
      </div>

      ${this.renderUnifiedOddsSection(stats.unifiedOdds)}`;
  }

  private static renderUnifiedOddsSection(unifiedOdds: SportsDbStats['unifiedOdds']): string {
    if (!unifiedOdds || unifiedOdds.bookmakerOdds.total === 0) return '';

    // BookmakerOdds by league rows
    const byLeagueRows = unifiedOdds.bookmakerOdds.byLeague.length > 0
      ? unifiedOdds.bookmakerOdds.byLeague.map(l => `<tr>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;font-weight:500;">${l.league}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;">${l.count.toLocaleString()}</td>
        </tr>`).join('')
      : '';

    // Recent ingestion runs
    const ingestionRows = unifiedOdds.ingestionRuns.length > 0
      ? unifiedOdds.ingestionRuns.map(r => `<tr>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;font-size:11px;">${r.source}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;font-size:11px;">${r.league}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:center;font-size:11px;color:${r.status === 'completed' ? '#28a745' : r.status === 'failed' ? '#dc3545' : '#ffc107'};">${r.status}</td>
          <td style="padding:4px 8px;border-bottom:1px solid #eee;text-align:right;font-size:11px;">${r.oddsCreated.toLocaleString()}</td>
        </tr>`).join('')
      : '<tr><td colspan="4" style="padding:8px;color:#999;">No recent ingestion runs</td></tr>';

    // Bookmakers list (sharp books highlighted)
    const sharpBooks = unifiedOdds.bookmakers.filter(b => b.isSharp);
    const regularBooks = unifiedOdds.bookmakers.filter(b => !b.isSharp);
    const bookmakerList = [
      ...sharpBooks.map(b => `<span style="background:#fff3cd;padding:2px 6px;border-radius:3px;margin:2px;font-size:11px;display:inline-block;">${b.name} ⭐</span>`),
      ...regularBooks.slice(0, 8).map(b => `<span style="background:#f8f9fa;padding:2px 6px;border-radius:3px;margin:2px;font-size:11px;display:inline-block;">${b.name}</span>`),
    ].join('');

    return `
      <h3 style="color:#667eea;font-size:14px;margin:24px 0 12px;border-top:1px solid #e0e0e0;padding-top:16px;">Unified Odds Schema (v2)</h3>

      <!-- Summary Cards -->
      <div style="display:flex;gap:12px;margin-bottom:16px;flex-wrap:wrap;">
        <div style="flex:1;min-width:100px;background:#ede7f6;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#7b1fa2;">${unifiedOdds.bookmakerOdds.total.toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">BookmakerOdds Total</div>
        </div>
        <div style="flex:1;min-width:100px;background:#e8f5e9;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#388e3c;">+${unifiedOdds.bookmakerOdds.today.toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">Odds Today</div>
        </div>
        <div style="flex:1;min-width:100px;background:#e3f2fd;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#1976d2;">${unifiedOdds.oddsHistory.total.toLocaleString()}</div>
          <div style="color:#666;font-size:11px;">Line Snapshots</div>
        </div>
        <div style="flex:1;min-width:100px;background:#fff8e1;padding:12px;border-radius:6px;text-align:center;">
          <div style="font-size:20px;font-weight:bold;color:#ff8f00;">${unifiedOdds.bookmakers.length}</div>
          <div style="color:#666;font-size:11px;">Active Bookmakers</div>
        </div>
      </div>

      <!-- Tracked Bookmakers -->
      ${bookmakerList ? `
      <div style="background:#f5f5f5;padding:8px 12px;border-radius:4px;margin-bottom:16px;">
        <strong style="font-size:12px;">Tracked Bookmakers:</strong><br/>
        ${bookmakerList}
        ${regularBooks.length > 8 ? `<span style="color:#999;font-size:11px;">+${regularBooks.length - 8} more</span>` : ''}
      </div>
      ` : ''}

      <div style="display:flex;gap:16px;flex-wrap:wrap;">
        <!-- BookmakerOdds by League -->
        ${byLeagueRows ? `
        <div style="flex:1;min-width:200px;">
          <h4 style="color:#555;font-size:13px;margin-bottom:8px;">BookmakerOdds by League</h4>
          <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
            <thead>
              <tr style="background:#f8f9fa;">
                <th style="padding:4px 8px;text-align:left;font-size:11px;">League</th>
                <th style="padding:4px 8px;text-align:right;font-size:11px;">Records</th>
              </tr>
            </thead>
            <tbody>${byLeagueRows}</tbody>
          </table>
        </div>
        ` : ''}

        <!-- Recent Ingestion Runs -->
        <div style="flex:1;min-width:300px;">
          <h4 style="color:#555;font-size:13px;margin-bottom:8px;">Recent Ingestion Runs</h4>
          <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
            <thead>
              <tr style="background:#f8f9fa;">
                <th style="padding:4px 8px;text-align:left;font-size:11px;">Source</th>
                <th style="padding:4px 8px;text-align:left;font-size:11px;">League</th>
                <th style="padding:4px 8px;text-align:center;font-size:11px;">Status</th>
                <th style="padding:4px 8px;text-align:right;font-size:11px;">Odds</th>
              </tr>
            </thead>
            <tbody>${ingestionRows}</tbody>
          </table>
        </div>
      </div>`;
  }

  private static renderLogErrorsSection(errors: LogError[]): string {
    if (!errors.length) {
      return `
        <h2 style="color:#333;margin-top:24px;border-bottom:2px solid #667eea;padding-bottom:6px;">Sync/Ingest Errors</h2>
        <p style="color:#28a745;padding:8px 0;">No errors found in log files for this period.</p>`;
    }

    const rows = errors.map(e => `<tr>
      <td style="padding:4px 8px;border-bottom:1px solid #eee;font-size:12px;color:#666;white-space:nowrap;vertical-align:top;">${e.file}</td>
      <td style="padding:4px 8px;border-bottom:1px solid #eee;font-size:12px;font-family:monospace;word-break:break-all;">${e.line.replace(/</g, '&lt;')}</td>
    </tr>`).join('');

    return `
      <h2 style="color:#dc3545;margin-top:24px;border-bottom:2px solid #dc3545;padding-bottom:6px;">Sync/Ingest Errors (${errors.length})</h2>
      <table style="width:100%;border-collapse:collapse;margin-bottom:16px;">
        <thead>
          <tr style="background:#f8d7da;">
            <th style="padding:6px 8px;text-align:left;font-size:12px;">Log File</th>
            <th style="padding:6px 8px;text-align:left;font-size:12px;">Error</th>
          </tr>
        </thead>
        <tbody>${rows}</tbody>
      </table>`;
  }

  /**
   * Generate and send the daily compliance report
   */
  static async sendDailyReport(date?: Date): Promise<boolean> {
    try {
      const report = await this.getDailyReport(date);
      const html = this.generateReportHTML(report);

      // Initialize email service if needed
      TransactionalEmailService.initialize();

      // Use nodemailer directly to send to admin (bypassing template system)
      const nodemailer = require('nodemailer');
      const host = process.env.SMTP_HOST || 'localhost';
      const port = parseInt(process.env.SMTP_PORT || '25');

      const transportConfig: any = {
        host,
        port,
        secure: port === 465,
        tls: { rejectUnauthorized: false },
      };

      if (process.env.SMTP_USER && process.env.SMTP_PASS) {
        transportConfig.auth = {
          user: process.env.SMTP_USER,
          pass: process.env.SMTP_PASS,
        };
      }

      const transporter = nodemailer.createTransport(transportConfig);

      await transporter.sendMail({
        from: `"EventheOdds Compliance" <${process.env.EMAIL_FROM || 'noreply@eventheodds.ai'}>`,
        to: 'admin@eventheodds.ai',
        subject: `[Compliance] Daily Report - ${report.date} | ${report.totalEvents} events (${report.failedCount} failed)`,
        html,
        text: `Daily Compliance Report for ${report.date}\n\nTotal Events: ${report.totalEvents}\nSuccess: ${report.successCount}\nFailed: ${report.failedCount}\nBlocked: ${report.blockedCount}`,
      });

      console.log(`[ComplianceService] Daily report sent for ${report.date}`);

      // Log that we sent the report
      await this.log('SYSTEM', 'COMPLIANCE_REPORT_SENT', 'ComplianceService', {
        date: report.date,
        totalEvents: report.totalEvents,
      });

      return true;
    } catch (error) {
      console.error('[ComplianceService] Failed to send daily report:', error);
      return false;
    }
  }

  /**
   * Get logs with filtering for the admin API
   */
  static async getLogs(params: {
    category?: string;
    days?: number;
    limit?: number;
    offset?: number;
  }) {
    const { category, days = 7, limit = 100, offset = 0 } = params;

    const since = new Date();
    since.setDate(since.getDate() - days);

    const where: any = { createdAt: { gte: since } };
    if (category) where.category = category;

    const [logs, total] = await Promise.all([
      prisma.complianceLog.findMany({
        where,
        orderBy: { createdAt: 'desc' },
        take: limit,
        skip: offset,
      }),
      prisma.complianceLog.count({ where }),
    ]);

    return { logs, total };
  }
}

interface IngestionJobSummary {
  name: string;
  runs: number;
  success: number;
  failed: number;
  avgDuration: number;
  lastOutput: string;
}

interface SportsDbStats {
  tables: Array<{ table: string; count: number }>;
  todayInserts: Array<{ table: string; count: number }>;
  // Detailed breakdowns
  gamesByLeague: Array<{ league: string; total: number; today: number; withOdds: number }>;
  propsByLeague: Array<{ league: string; total: number; today: number }>;
  oddsByBookmaker: Array<{ bookmaker: string; count: number }>;
  oddsSnapshots: { total: number; today: number; leagues: string[] };
  // Unified odds schema (v2)
  unifiedOdds: {
    bookmakerOdds: { total: number; today: number; byLeague: Array<{ league: string; count: number }> };
    oddsHistory: { total: number; today: number };
    ingestionRuns: Array<{ source: string; league: string; status: string; oddsCreated: number; startedAt: string }>;
    bookmakers: Array<{ slug: string; name: string; isSharp: boolean; region: string }>;
  };
}

interface LogError {
  file: string;
  line: string;
}

interface PlatformStats {
  gamePages: { total: number; today: number; published: number };
  seoPages: { total: number };
  alerts: { active: number; sentToday: number };
  newsletterSubscribers: number;
  workers: { online: number; total: number; details: string };
}

interface DailyReport {
  date: string;
  totalEvents: number;
  successCount: number;
  failedCount: number;
  blockedCount: number;
  email: Record<string, { total: number; success: number; failed: number; blocked: number }>;
  content: Record<string, { total: number; success: number; failed: number; blocked: number }>;
  agent: Record<string, any>;
  system: Record<string, { total: number; success: number; failed: number; blocked: number }>;
  user: Record<string, { total: number; success: number; failed: number; blocked: number }>;
  ingestion: IngestionJobSummary[];
  platform: PlatformStats;
  sportsDb: SportsDbStats;
  logErrors: LogError[];
}