/**
 * Score Validator — Permanent Safeguard
 * 
 * Runs after the grading resolver to verify SportsGame scores are complete
 * and accurate before grading can proceed.
 * 
 * Checks:
 * 1. Games with status='final' but NULL/zero scores
 * 2. NBA games with total < 150 (impossible for a final)
 * 3. NHL games with both scores = 0 (suspicious)
 * 4. Soccer games recently marked final (cross-check score exists)
 * 5. Duplicate SportsGame rows for the same matchup
 * 
 * Run: npx tsx src/workers/score-validator.ts
 * Schedule: Run 30min after each resolver cron
 */

import { Pool } from 'pg';
import { buildDatabasePoolConfig } from '../db/config';

const pool = new Pool(buildDatabasePoolConfig());

interface ScoreIssue {
  type: string;
  league: string;
  homeTeam: string;
  awayTeam: string;
  homeScore: number | null;
  awayScore: number | null;
  gameDate: string;
  reason: string;
}

async function validateScores(): Promise<ScoreIssue[]> {
  const issues: ScoreIssue[] = [];

  // 1. NBA finals with impossibly low scores (total < 150)
  const { rows: badNba } = await pool.query(`
    SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", "gameDate"::text, league
    FROM "SportsGame"
    WHERE league = 'nba' AND status = 'final'
    AND "gameDate" > NOW() - INTERVAL '3 days'
    AND ("homeScore" + "awayScore") < 150
  `);
  for (const g of badNba) {
    issues.push({
      type: 'SUSPICIOUS_SCORE',
      league: g.league,
      homeTeam: g.homeTeam,
      awayTeam: g.awayTeam,
      homeScore: g.homeScore,
      awayScore: g.awayScore,
      gameDate: g.gameDate,
      reason: `NBA total ${(g.homeScore || 0) + (g.awayScore || 0)} too low — likely mid-game capture`,
    });
  }

  // 2. Games with status=final but NULL scores
  const { rows: nullScores } = await pool.query(`
    SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", "gameDate"::text, league
    FROM "SportsGame"
    WHERE status = 'final'
    AND "gameDate" > NOW() - INTERVAL '3 days'
    AND ("homeScore" IS NULL OR "awayScore" IS NULL)
  `);
  for (const g of nullScores) {
    issues.push({
      type: 'MISSING_SCORE',
      league: g.league,
      homeTeam: g.homeTeam,
      awayTeam: g.awayTeam,
      homeScore: g.homeScore,
      awayScore: g.awayScore,
      gameDate: g.gameDate,
      reason: 'Game marked final but scores are NULL',
    });
  }

  // 3. Duplicate SportsGame entries (same league + date + similar team names)
  const { rows: dupes } = await pool.query(`
    SELECT sg1."homeTeam" as short_home, sg2."homeTeam" as full_home, sg1.league,
           sg1."gameDate"::text as game_date, sg1.id as dupe_id
    FROM "SportsGame" sg1
    JOIN "SportsGame" sg2 
      ON sg1.league = sg2.league 
      AND sg1."gameDate"::date = sg2."gameDate"::date
      AND sg1.id != sg2.id
      AND LENGTH(sg1."homeTeam") < LENGTH(sg2."homeTeam")
      AND sg2."homeTeam" ILIKE sg1."homeTeam" || '%'
    WHERE sg1."gameDate" > NOW() - INTERVAL '3 days'
    LIMIT 50
  `);
  for (const d of dupes) {
    issues.push({
      type: 'DUPLICATE_GAME',
      league: d.league,
      homeTeam: d.short_home,
      awayTeam: d.full_home,
      homeScore: null,
      awayScore: null,
      gameDate: d.game_date,
      reason: `Duplicate: "${d.short_home}" and "${d.full_home}" for same date`,
    });
  }

  // 4. NHL games with 0-0 final (rare but possible — flag for review)
  const { rows: nhlZero } = await pool.query(`
    SELECT "homeTeam", "awayTeam", "homeScore", "awayScore", "gameDate"::text, league
    FROM "SportsGame"
    WHERE league = 'nhl' AND status = 'final'
    AND "gameDate" > NOW() - INTERVAL '3 days'
    AND "homeScore" = 0 AND "awayScore" = 0
  `);
  for (const g of nhlZero) {
    issues.push({
      type: 'SUSPICIOUS_SCORE',
      league: g.league,
      homeTeam: g.homeTeam,
      awayTeam: g.awayTeam,
      homeScore: 0,
      awayScore: 0,
      gameDate: g.gameDate,
      reason: 'NHL 0-0 final — extremely rare, likely missing scores',
    });
  }

  return issues;
}

async function autoFixDuplicates(): Promise<number> {
  // Auto-delete abbreviated duplicates when full-name version exists
  const { rowCount } = await pool.query(`
    WITH to_delete AS (
      SELECT DISTINCT sg1.id as dupe_id
      FROM "SportsGame" sg1
      JOIN "SportsGame" sg2 
        ON sg1.league = sg2.league 
        AND sg1."gameDate"::date = sg2."gameDate"::date
        AND sg1.id != sg2.id
        AND LENGTH(sg1."homeTeam") < LENGTH(sg2."homeTeam")
        AND sg2."homeTeam" ILIKE sg1."homeTeam" || '%'
      WHERE sg1."gameDate" > NOW() - INTERVAL '7 days'
    )
    DELETE FROM "SportsGame" WHERE id IN (SELECT dupe_id FROM to_delete)
  `);
  return rowCount || 0;
}

async function main() {
  console.log(`[${new Date().toISOString()}] Score Validator starting...`);

  // Auto-fix duplicates first
  const dupesFixed = await autoFixDuplicates();
  if (dupesFixed > 0) {
    console.log(`Auto-cleaned ${dupesFixed} duplicate SportsGame rows`);
  }

  // Validate remaining scores
  const issues = await validateScores();

  if (issues.length === 0) {
    console.log('✅ All scores validated — no issues found');
  } else {
    console.log(`⚠️ Found ${issues.length} score issues:`);
    for (const issue of issues) {
      console.log(`  [${issue.type}] ${issue.league}: ${issue.homeTeam} vs ${issue.awayTeam} (${issue.gameDate}) — ${issue.reason}`);
    }

    // Write issues to file for monitoring
    const fs = await import('fs');
    fs.writeFileSync('/var/log/eventheodds/score-validation.json', JSON.stringify({
      timestamp: new Date().toISOString(),
      issues,
      autoFixedDupes: dupesFixed,
    }, null, 2));
  }

  await pool.end();
}

main().catch(console.error);