/**
 * DVP (Defense vs Position) Service
 *
 * Direct DB query to sportsdb DefenseVsPosition table for matchup context.
 * Supports all leagues: NBA, NCAAB, NHL, NFL, MLB, EPL, La Liga, Bundesliga,
 * Serie A, Ligue 1, Champions League, WNBA, UFC.
 */

import pool from '../db';
import { getTeamAbbr } from '../lib/team-abbreviations';

export interface DvpRank {
  position: string;  // G, F, C, D, ALL, OF, IF, etc.
  stat: string;      // points, rebounds, shots, etc.
  rank: number;
  tier: string;      // EASY (1-10), AVG (11-20), TOUGH (21-30)
  value: number;     // avg allowed
}

export interface TeamDvp {
  team: string;
  ranks: DvpRank[];
}

export interface DvpPositionData {
  stat: string;
  rank: number;
  tier: string;
  avgAllowed: number;
  leagueAvg: number;
  gamesCount: number;
}

export interface DvpTeamProfile {
  team: string;
  positions: Record<string, DvpPositionData[]>;
}

export interface DvpInsightResult {
  title: string;
  league: string;
  matchup: string;
  home: DvpTeamProfile | null;
  away: DvpTeamProfile | null;
  keyTakeaways: string[];
}

// Human-readable stat labels per sport
const STAT_LABELS: Record<string, string> = {
  points: 'PTS', rebounds: 'REB', assists: 'AST', steals: 'STL', blocks: 'BLK',
  threePointersMade: '3PM', threes: '3PM',
  goals: 'G', shots: 'SOG', shots_onTarget: 'SOT', tackles: 'TKL',
  clearances: 'CLR', fouls: 'FLS',
  mlb_hits: 'H', mlb_home_runs: 'HR', mlb_rbi: 'RBI', mlb_total_bases: 'TB',
  mlb_strikeouts: 'K', mlb_runs: 'R', mlb_stolen_bases: 'SB', mlb_walks: 'BB',
  mlb_earned_runs: 'ER', mlb_pitcher_strikeouts: 'K',
  passing_yards: 'Pass YDS', passing_tds: 'Pass TD', rushing_yards: 'Rush YDS',
  receiving_yards: 'Rec YDS', receptions: 'REC',
  total_strikes: 'Strikes', knockdowns: 'KD', sig_strike_pct: 'Sig%',
  takedowns: 'TD', submission_attempts: 'Sub Att',
};

// Position group labels per league
const POS_LABELS: Record<string, Record<string, string>> = {
  nba: { G: 'Guards', F: 'Forwards', C: 'Centers' },
  ncaab: { G: 'Guards', F: 'Forwards', C: 'Centers' },
  wnba: { G: 'Guards', F: 'Forwards', C: 'Centers' },
  nhl: { F: 'Forwards', D: 'Defensemen', G: 'Goalies' },
  nfl: { WR: 'Wide Receivers', LB: 'Linebackers', CB: 'Cornerbacks', DE: 'Defensive Ends' },
  mlb: { OF: 'Outfielders', IF: 'Infielders', C_DH: 'Catcher/DH', P: 'Pitchers', PR: 'Pinch Runners' },
};

function getTier(rank: number): string {
  if (rank <= 10) return 'EASY';
  if (rank <= 20) return 'AVG';
  return 'TOUGH';
}

function getStatLabel(stat: string): string {
  return STAT_LABELS[stat] || stat;
}

function getPosLabel(league: string, pos: string): string {
  return POS_LABELS[league]?.[pos] || pos;
}

/**
 * Get DVP ranks for both teams in a matchup.
 * Each team's DVP represents how they DEFEND — low rank = bad defense = easy matchup for opponents.
 * Now supports ALL leagues with DVP data.
 */
export async function getDvpForMatchup(
  homeTeam: string,
  awayTeam: string,
  league: string
): Promise<{ home: TeamDvp | null; away: TeamDvp | null }> {
  try {
    // Resolve abbreviations — DVP table stores 3-letter codes (STL, SJS, etc.)
    const homeAbbr = getTeamAbbr(homeTeam);
    const awayAbbr = getTeamAbbr(awayTeam);

    // Try abbreviation match first (most reliable)
    const abbrResult = await pool.query(
      `SELECT team, "positionGroup" as position, "statKey" as stat, rank, "avgAllowed" as value
       FROM "DefenseVsPosition"
       WHERE league = $3 AND (team = $1 OR team = $2)
       ORDER BY team, "positionGroup", "statKey"`,
      [homeAbbr !== '???' ? homeAbbr : homeTeam, awayAbbr !== '???' ? awayAbbr : awayTeam, league]
    ).catch(() => ({ rows: [] }));

    if (abbrResult.rows.length > 0) {
      return parseDvpRows(abbrResult.rows, homeAbbr !== '???' ? homeAbbr : homeTeam, awayAbbr !== '???' ? awayAbbr : awayTeam);
    }

    // Fallback: fuzzy match on full names
    const result = await pool.query(
      `SELECT team, "positionGroup" as position, "statKey" as stat, rank, "avgAllowed" as value
       FROM "DefenseVsPosition"
       WHERE league = $3 AND (team ILIKE $1 OR team ILIKE $2)
       ORDER BY team, "positionGroup", "statKey"`,
      [`%${homeTeam}%`, `%${awayTeam}%`, league]
    ).catch(() => ({ rows: [] }));

    if (result.rows.length > 0) {
      return parseDvpRows(result.rows, homeTeam, awayTeam);
    }

    return { home: null, away: null };
  } catch (err) {
    console.error('DVP lookup error:', err);
    return { home: null, away: null };
  }
}

function parseDvpRows(
  rows: any[],
  homeTeam: string,
  awayTeam: string
): { home: TeamDvp | null; away: TeamDvp | null } {
  const homeRows = rows.filter(r =>
    r.team.toUpperCase().includes(homeTeam.toUpperCase()) ||
    r.team.toUpperCase() === homeTeam.toUpperCase()
  );
  const awayRows = rows.filter(r =>
    r.team.toUpperCase().includes(awayTeam.toUpperCase()) ||
    r.team.toUpperCase() === awayTeam.toUpperCase()
  );

  const buildTeamDvp = (teamRows: any[], teamName: string): TeamDvp | null => {
    if (teamRows.length === 0) return null;
    return {
      team: teamName,
      ranks: teamRows.map(r => ({
        position: r.position,
        stat: r.stat,
        rank: r.rank,
        tier: getTier(r.rank),
        value: parseFloat(r.value),
      })),
    };
  };

  return {
    home: buildTeamDvp(homeRows, homeTeam),
    away: buildTeamDvp(awayRows, awayTeam),
  };
}

/**
 * Format DVP data for API response (compact form for frontend).
 */
export function formatDvpForResponse(dvp: { home: TeamDvp | null; away: TeamDvp | null }): any {
  const formatTeam = (t: TeamDvp | null) => {
    if (!t) return null;
    const byPos: Record<string, Array<{ stat: string; rank: number; tier: string }>> = {};
    for (const r of t.ranks) {
      if (!byPos[r.position]) byPos[r.position] = [];
      byPos[r.position].push({ stat: r.stat, rank: r.rank, tier: r.tier });
    }
    return Object.keys(byPos).length > 0 ? byPos : null;
  };

  return {
    home: formatTeam(dvp.home),
    away: formatTeam(dvp.away),
  };
}

/**
 * Check if a league has DVP data in the database.
 */
export async function hasDvpData(league: string): Promise<boolean> {
  try {
    const { rows } = await pool.query(
      'SELECT 1 FROM "DefenseVsPosition" WHERE league = $1 LIMIT 1',
      [league]
    );
    return rows.length > 0;
  } catch {
    return false;
  }
}

/**
 * Generate a full DVP insight for a matchup — pure data, no LLM needed.
 * Queries DefenseVsPosition for both teams, formats into structured profiles,
 * and auto-generates key takeaways.
 */
export async function generateDvpInsight(
  homeTeam: string,
  awayTeam: string,
  league: string,
  homeShort?: string,
  awayShort?: string
): Promise<DvpInsightResult> {
  // DVP table typically stores short team names (CHA, POR, MIA, etc.)
  // Try short names first (exact match), then ILIKE on full names
  let dvpRows: any[] = [];

  // 1. Try short names (most reliable)
  if (homeShort && awayShort) {
    const shortResult = await pool.query(
      `SELECT team, "positionGroup", "statKey", rank, "avgAllowed", "leagueAvg", "gamesCount"
       FROM "DefenseVsPosition"
       WHERE league = $1 AND (team = $2 OR team = $3)
       ORDER BY team, "positionGroup", "statKey"`,
      [league, homeShort, awayShort]
    ).catch(() => ({ rows: [] }));
    dvpRows = shortResult.rows;
  }

  // 2. Fallback: ILIKE on full names
  if (dvpRows.length === 0) {
    const { rows } = await pool.query(
      `SELECT team, "positionGroup", "statKey", rank, "avgAllowed", "leagueAvg", "gamesCount"
       FROM "DefenseVsPosition"
       WHERE league = $1 AND (team ILIKE $2 OR team ILIKE $3)
       ORDER BY team, "positionGroup", "statKey"`,
      [league, `%${homeTeam}%`, `%${awayTeam}%`]
    ).catch(() => ({ rows: [] }));
    dvpRows = rows;
  }

  // 3. Last resort: exact full names
  if (dvpRows.length === 0) {
    const exact = await pool.query(
      `SELECT team, "positionGroup", "statKey", rank, "avgAllowed", "leagueAvg", "gamesCount"
       FROM "DefenseVsPosition"
       WHERE league = $1 AND (team = $2 OR team = $3)
       ORDER BY team, "positionGroup", "statKey"`,
      [league, homeTeam, awayTeam]
    ).catch(() => ({ rows: [] }));
    dvpRows = exact.rows;
  }

  const buildProfile = (teamName: string, teamShort?: string): DvpTeamProfile | null => {
    const teamRows = dvpRows.filter(r => {
      const t = r.team.toUpperCase();
      if (teamShort && t === teamShort.toUpperCase()) return true;
      return t.includes(teamName.toUpperCase()) || t === teamName.toUpperCase();
    });
    if (teamRows.length === 0) return null;

    const positions: Record<string, DvpPositionData[]> = {};
    for (const r of teamRows) {
      const pos = r.positionGroup;
      if (!positions[pos]) positions[pos] = [];
      positions[pos].push({
        stat: r.statKey,
        rank: r.rank,
        tier: getTier(r.rank),
        avgAllowed: parseFloat(r.avgAllowed),
        leagueAvg: parseFloat(r.leagueAvg),
        gamesCount: r.gamesCount,
      });
    }

    return { team: teamName, positions };
  };

  const homeProfile = buildProfile(homeTeam, homeShort);
  const awayProfile = buildProfile(awayTeam, awayShort);

  // Auto-generate key takeaways
  const takeaways = generateTakeaways(homeProfile, awayProfile, league);

  return {
    title: 'Defense vs Position Analysis',
    league,
    matchup: `${awayTeam} @ ${homeTeam}`,
    home: homeProfile,
    away: awayProfile,
    keyTakeaways: takeaways,
  };
}

/**
 * Auto-generate 3-5 key takeaway bullets from DVP profiles.
 * Highlights EASY targets, TOUGH matchups, and home/away edges.
 */
function generateTakeaways(
  home: DvpTeamProfile | null,
  away: DvpTeamProfile | null,
  league: string
): string[] {
  const takeaways: string[] = [];
  const allEntries: { team: string; pos: string; stat: string; rank: number; avg: number; lgAvg: number; tier: string }[] = [];

  for (const profile of [home, away]) {
    if (!profile) continue;
    for (const [pos, stats] of Object.entries(profile.positions)) {
      for (const s of stats) {
        allEntries.push({
          team: profile.team,
          pos,
          stat: s.stat,
          rank: s.rank,
          avg: s.avgAllowed,
          lgAvg: s.leagueAvg,
          tier: s.tier,
        });
      }
    }
  }

  if (allEntries.length === 0) {
    takeaways.push('Limited DVP data available for this matchup.');
    return takeaways;
  }

  // Find biggest EASY targets (rank 1-5)
  const easyTargets = allEntries
    .filter(e => e.rank <= 5)
    .sort((a, b) => a.rank - b.rank);

  for (const e of easyTargets.slice(0, 2)) {
    const diff = e.avg - e.lgAvg;
    const diffStr = diff > 0 ? `+${diff.toFixed(1)}` : diff.toFixed(1);
    takeaways.push(
      `${e.team} allows the #${e.rank} most ${getStatLabel(e.stat)} to ${getPosLabel(league, e.pos)} (${e.avg.toFixed(1)} vs ${e.lgAvg.toFixed(1)} avg, ${diffStr}) — EASY target`
    );
  }

  // Find TOUGH matchups (rank 26+)
  const toughTargets = allEntries
    .filter(e => e.rank >= 26)
    .sort((a, b) => b.rank - a.rank);

  for (const e of toughTargets.slice(0, 2)) {
    takeaways.push(
      `${e.team} ranks #${e.rank} against ${getPosLabel(league, e.pos)} ${getStatLabel(e.stat)} — tough matchup`
    );
  }

  // Compare home vs away if both profiles exist
  if (home && away) {
    const homeEasy = allEntries.filter(e => e.team === home.team && e.tier === 'EASY').length;
    const awayEasy = allEntries.filter(e => e.team === away.team && e.tier === 'EASY').length;
    if (homeEasy > awayEasy + 2) {
      takeaways.push(`Edge: ${home.team} defense has ${homeEasy} EASY exploits vs ${awayEasy} for ${away.team} — opponents facing ${home.team} have more favorable matchups`);
    } else if (awayEasy > homeEasy + 2) {
      takeaways.push(`Edge: ${away.team} defense has ${awayEasy} EASY exploits vs ${homeEasy} for ${home.team} — opponents facing ${away.team} have more favorable matchups`);
    }
  }

  // Fallback if we haven't generated enough
  if (takeaways.length < 2) {
    const sorted = allEntries.sort((a, b) => a.rank - b.rank);
    const weakest = sorted[0];
    if (weakest) {
      takeaways.push(
        `Weakest spot: ${weakest.team} ${getPosLabel(league, weakest.pos)} ${getStatLabel(weakest.stat)} (Rank #${weakest.rank})`
      );
    }
    const strongest = sorted[sorted.length - 1];
    if (strongest && strongest !== weakest) {
      takeaways.push(
        `Strongest: ${strongest.team} ${getPosLabel(league, strongest.pos)} ${getStatLabel(strongest.stat)} (Rank #${strongest.rank})`
      );
    }
  }

  return takeaways.slice(0, 5);
}