import { Router, Request, Response } from 'express';
import pool from '../db';
import { getTeamAbbr } from '../lib/team-abbreviations';
import { gradeFromForecastData, selectBenchmarkForecast, sanitizeClosingSpreadForLeague } from '../services/benchmark-grading';

const router = Router();

/**
 * GET /api/archive/:eventId
 *
 * Returns the full traceability chain for a graded forecast:
 *   Graded Result -> Original Forecast -> Rainwire Post
 *
 * Public endpoint (no auth) — the archive is a trust/transparency feature.
 */
router.get('/:eventId', async (req: Request, res: Response) => {
  try {
    const { eventId } = req.params;

    // Fetch archive + blog post + accuracy in one query
    const { rows } = await pool.query(`
      SELECT
        af.id              AS archive_id,
        af.forecast_id,
        af.event_id,
        af.league,
        af.home_team,
        af.away_team,
        af.starts_at,
        af.winner_pick,
        af.probability_raw,
        af.probability_bucket,
        af.forecast_data,
        af.confidence_score,
        af.composite_confidence,
        af.odds_data,
        af.model_signals,
        af.outcome,
        af.actual_winner,
        af.actual_score,
        af.settled_at,
        af.blog_post_id,
        af.created_at       AS archived_at,
        -- Blog post fields
        bp.slug             AS blog_slug,
        bp.sport            AS blog_sport,
        bp.title            AS blog_title,
        bp.content          AS blog_content,
        bp.excerpt          AS blog_excerpt,
        bp.meta_description AS blog_meta,
        bp.tags             AS blog_tags,
        bp.home_team        AS blog_home_team,
        bp.away_team        AS blog_away_team,
        bp.game_date        AS blog_game_date,
        bp.published_at     AS blog_published_at,
        bp.views            AS blog_views,
        -- Accuracy/grading fields
        fa.id               AS accuracy_id,
        fa.predicted_winner,
        fa.actual_winner    AS graded_actual_winner,
        fa.predicted_spread,
        fa.actual_spread,
        fa.predicted_total,
        fa.actual_total,
        fa.accuracy_bucket,
        fa.home_score,
        fa.away_score,
        fa.original_forecast,
        fa.benchmark_forecast,
        fa.closing_market,
        fa.final_grade,
        fa.grading_policy,
        fa.benchmark_source,
        fa.resolved_at
      FROM rm_archived_forecasts af
      LEFT JOIN rm_blog_posts bp ON bp.id = af.blog_post_id AND bp.status = 'published'
      LEFT JOIN rm_forecast_accuracy_v2 fa ON fa.event_id = af.event_id
      WHERE af.event_id = $1
      ORDER BY af.created_at DESC
      LIMIT 1
    `, [eventId]);

    if (rows.length === 0) {
      return res.status(404).json({ error: 'Archive not found for this event' });
    }

    const r = rows[0];
    const fd = r.forecast_data || {};
    const odds = r.odds_data || {};

    // Compute display values
    const fs = fd.forecast_side || fd.winner_pick || r.winner_pick;
    let forecastLine: string | null = null;
    let closingLine: string | null = null;

    if (fs) {
      const isHome = fs === r.home_team;
      const side: 'home' | 'away' = isHome ? 'home' : 'away';
      const teamShort = getTeamAbbr(fs);

      const originalSpread = fd?.projected_lines?.spread?.[side] ?? null;
      const closingSpread = sanitizeClosingSpreadForLeague(
        r.league,
        odds?.spread?.[side]?.line ?? null,
      );
      const { value: benchmarkSpread } = selectBenchmarkForecast('spread', side, originalSpread, closingSpread);

      if (benchmarkSpread != null) {
        forecastLine = `${teamShort} ${benchmarkSpread > 0 ? '+' + benchmarkSpread : benchmarkSpread === 0 ? 'PK' : benchmarkSpread}`;
      }
      if (fd?.projected_lines?.moneyline?.[side] != null) {
        const ml = fd.projected_lines.moneyline[side];
        forecastLine = forecastLine
          ? `${forecastLine} / ML ${ml > 0 ? '+' + ml : ml}`
          : `${teamShort} ML ${ml > 0 ? '+' + ml : ml}`;
      }

      if (closingSpread != null) {
        const so = odds?.spread?.[side]?.odds;
        closingLine = `${teamShort} ${closingSpread > 0 ? '+' + closingSpread : closingSpread === 0 ? 'PK' : closingSpread}${so != null ? ` (${so > 0 ? '+' : ''}${so})` : ''}`;
      }
    }

    // Confidence tier
    const cs = parseFloat(r.confidence_score) || 0;
    const confidenceTier = cs >= 0.85 ? 'A+' : cs >= 0.70 ? 'A' : cs >= 0.55 ? 'B+' : cs > 0 ? 'B' : null;

    // Final score string
    let finalScore: string | null = null;
    if (r.home_score != null && r.away_score != null) {
      const hs = getTeamAbbr(r.home_team);
      const as_ = getTeamAbbr(r.away_team);
      finalScore = `${hs} ${r.home_score} - ${as_} ${r.away_score}`;
    } else if (r.actual_score) {
      finalScore = r.actual_score;
    }

    // Totals
    let totalForecast: string | null = null;
    let totalResult: string | null = null;
    if (fd?.projected_lines?.total != null) {
      totalForecast = `O/U ${fd.projected_lines.total}`;
    }
    if (r.actual_total != null) {
      totalResult = String(r.actual_total);
    } else if (r.home_score != null && r.away_score != null) {
      totalResult = String(r.home_score + r.away_score);
    }

    // Build Rainwire URL
    const rainwireUrl = r.blog_slug && r.blog_sport
      ? `/rain-wire/${r.blog_sport}/${r.blog_slug}`
      : null;

    const response: any = {
      // Traceability chain IDs
      archiveId: r.archive_id,
      forecastId: r.forecast_id,
      eventId: r.event_id,
      accuracyId: r.accuracy_id || null,
      blogPostId: r.blog_post_id || null,

      // Event context
      matchup: {
        league: r.league,
        homeTeam: r.home_team,
        awayTeam: r.away_team,
        homeShort: getTeamAbbr(r.home_team),
        awayShort: getTeamAbbr(r.away_team),
        startsAt: r.starts_at,
        eventDate: r.blog_game_date || r.starts_at,
      },

      // Original forecast (as published)
      forecast: {
        forecastSide: fs || null,
        forecastLine,
        confidenceTier,
        confidenceScore: r.confidence_score ? parseFloat(r.confidence_score) : null,
        compositeConfidence: r.composite_confidence ? parseFloat(r.composite_confidence) : null,
        probabilityRaw: r.probability_raw ? parseFloat(r.probability_raw) : null,
        probabilityBucket: r.probability_bucket,
        winnerPick: r.winner_pick,
        totalForecast,
        publishedAt: r.archived_at,
        // Snapshot data — preserved from publish time
        originalMarketSpread: r.original_forecast,
        modelSignals: r.model_signals,
      },

      // Grading / result
      result: {
        outcome: r.outcome,
        finalGrade: r.final_grade || null,
        finalScore,
        homeScore: r.home_score,
        awayScore: r.away_score,
        actualWinner: r.graded_actual_winner || r.actual_winner,
        closingLine,
        closingMarketValue: r.closing_market,
        benchmarkForecast: r.benchmark_forecast,
        benchmarkSource: r.benchmark_source,
        gradingPolicy: r.grading_policy,
        totalResult,
        resolvedAt: r.resolved_at,
        settledAt: r.settled_at,
        accuracyBucket: r.accuracy_bucket,
      },

      // Rainwire post
      rainwire: r.blog_slug ? {
        slug: r.blog_slug,
        url: rainwireUrl,
        title: r.blog_title,
        content: r.blog_content,
        excerpt: r.blog_excerpt,
        metaDescription: r.blog_meta,
        tags: r.blog_tags,
        publishedAt: r.blog_published_at,
        views: r.blog_views,
        gameDate: r.blog_game_date,
      } : null,

      // Chain integrity flags
      integrity: {
        hasArchive: true,
        hasBlogPost: !!r.blog_slug,
        hasAccuracy: !!r.accuracy_id,
        isSettled: r.outcome !== 'pending',
        isFullyLinked: !!(r.blog_slug && r.accuracy_id && r.outcome !== 'pending'),
      },
    };

    res.json(response);
  } catch (err: any) {
    console.error('Archive detail error:', err);
    res.status(500).json({ error: 'Failed to fetch archive detail' });
  }
});

/**
 * GET /api/archive/validate/mismatches
 *
 * Admin endpoint — returns records with broken or missing traceability links.
 */
router.get('/validate/mismatches', async (_req: Request, res: Response) => {
  try {
    // Archives without blog posts
    const noBlog = await pool.query(`
      SELECT af.id, af.event_id, af.league, af.home_team, af.away_team,
             af.starts_at, af.outcome, af.created_at
      FROM rm_archived_forecasts af
      WHERE af.blog_post_id IS NULL
        AND af.created_at >= NOW() - INTERVAL '90 days'
      ORDER BY af.created_at DESC
    `);

    // Archives without accuracy records
    const noAccuracy = await pool.query(`
      SELECT af.id, af.event_id, af.league, af.home_team, af.away_team,
             af.starts_at, af.outcome, af.created_at
      FROM rm_archived_forecasts af
      WHERE NOT EXISTS (
        SELECT 1 FROM rm_forecast_accuracy_v2 fa WHERE fa.event_id = af.event_id
      )
      AND af.outcome != 'pending'
      AND af.starts_at < NOW() - INTERVAL '4 hours'
      AND af.created_at >= NOW() - INTERVAL '90 days'
      ORDER BY af.created_at DESC
    `);

    // Blog posts without archive link
    const orphanBlogs = await pool.query(`
      SELECT bp.id, bp.slug, bp.sport, bp.title, bp.game_date, bp.published_at
      FROM rm_blog_posts bp
      WHERE bp.archived_forecast_id IS NULL
        AND bp.status = 'published'
        AND bp.published_at >= NOW() - INTERVAL '90 days'
      ORDER BY bp.published_at DESC
    `);

    // Settled archives still showing as pending
    const stalePending = await pool.query(`
      SELECT af.id, af.event_id, af.league, af.home_team, af.away_team,
             af.starts_at, af.created_at
      FROM rm_archived_forecasts af
      WHERE af.outcome = 'pending'
        AND af.starts_at < NOW() - INTERVAL '24 hours'
        AND af.created_at >= NOW() - INTERVAL '90 days'
      ORDER BY af.starts_at DESC
    `);

    res.json({
      missingBlogPost: noBlog.rows,
      missingAccuracy: noAccuracy.rows,
      orphanBlogPosts: orphanBlogs.rows,
      stalePending: stalePending.rows,
      summary: {
        missingBlogPost: noBlog.rows.length,
        missingAccuracy: noAccuracy.rows.length,
        orphanBlogPosts: orphanBlogs.rows.length,
        stalePending: stalePending.rows.length,
      },
    });
  } catch (err: any) {
    console.error('Archive validation error:', err);
    res.status(500).json({ error: 'Failed to validate archive integrity' });
  }
});

export default router;