import pool from '../db';

export interface MlbBullpenRoleAvailability {
  closerName: string | null;
  closerAvailable: boolean | null;
  closerStatus: 'available' | 'limited' | 'unavailable' | null;
  setupAvailableCount: number;
  setupLimitedCount: number;
  coreAvailableCount: number;
  coreLimitedCount: number;
  coreAvailabilityScore: number;
}

export interface MlbBullpenWorkload {
  teamShort: string;
  last1DayPitches: number;
  last3DayPitches: number;
  relieversUsedLast1Day: number;
  relieversUsedLast3Days: number;
  highLeverageAppearancesLast3Days: number;
  backToBackPitchers: number;
  fatigueScore: number;
  roleAvailability: MlbBullpenRoleAvailability;
}

type BullpenRoleRow = {
  pitcher_name: string;
  leverage_score: number;
  last1_pitches: number;
  last3_pitches: number;
  last2_day_appearances: number;
  last3_day_appearances: number;
};

function clamp(val: number, min: number, max: number): number {
  return Math.max(min, Math.min(max, val));
}

function normalize(val: number, lo: number, hi: number): number {
  return clamp((val - lo) / (hi - lo), 0, 1);
}

function classifyRoleAvailability(row: BullpenRoleRow | undefined): 'available' | 'limited' | 'unavailable' | null {
  if (!row) return null;
  const last1Pitches = Number(row.last1_pitches || 0);
  const last3Pitches = Number(row.last3_pitches || 0);
  const last2Apps = Number(row.last2_day_appearances || 0);
  const last3Apps = Number(row.last3_day_appearances || 0);

  if (
    last1Pitches >= 24
    || (last2Apps >= 2 && last3Pitches >= 35)
    || (last3Apps >= 3 && last3Pitches >= 45)
  ) {
    return 'unavailable';
  }

  if (
    last1Pitches >= 15
    || last3Pitches >= 30
    || last2Apps >= 2
    || last3Apps >= 3
  ) {
    return 'limited';
  }

  return 'available';
}

function buildRoleAvailability(rows: BullpenRoleRow[]): MlbBullpenRoleAvailability {
  const closer = rows[0];
  const setupRows = rows.slice(1, 3);
  const coreRows = rows.slice(0, 3);
  const statuses = coreRows.map(classifyRoleAvailability);
  const setupStatuses = setupRows.map(classifyRoleAvailability);
  const availableCount = statuses.filter((status) => status === 'available').length;
  const limitedCount = statuses.filter((status) => status === 'limited').length;
  const setupAvailableCount = setupStatuses.filter((status) => status === 'available').length;
  const setupLimitedCount = setupStatuses.filter((status) => status === 'limited').length;
  const closerStatus = classifyRoleAvailability(closer);
  const closerAvailable = closerStatus == null ? null : closerStatus !== 'unavailable';
  const roleScore = clamp(
    ((closerStatus === 'available' ? 1 : closerStatus === 'limited' ? 0.5 : 0) * 0.45)
      + (setupAvailableCount * 0.175)
      + (setupLimitedCount * 0.075)
      + (availableCount * 0.05),
    0,
    1,
  );

  return {
    closerName: closer?.pitcher_name || null,
    closerAvailable,
    closerStatus,
    setupAvailableCount,
    setupLimitedCount,
    coreAvailableCount: availableCount,
    coreLimitedCount: limitedCount,
    coreAvailabilityScore: Number(roleScore.toFixed(3)),
  };
}

export async function getMlbBullpenWorkload(teamShort: string, startsAt: string): Promise<MlbBullpenWorkload | null> {
  const { rows } = await pool.query(
    `WITH recent AS (
       SELECT *
       FROM rm_mlb_bullpen_usage
       WHERE team_short = $1
         AND game_date < DATE($2::timestamptz)
         AND game_date >= DATE($2::timestamptz) - INTERVAL '3 days'
     ),
     day1 AS (
       SELECT * FROM recent WHERE game_date = DATE($2::timestamptz) - INTERVAL '1 day'
     ),
     pitcher_days AS (
       SELECT pitcher_id, COUNT(DISTINCT game_date) AS games_count
       FROM recent
       GROUP BY pitcher_id
     )
     SELECT
       COALESCE((SELECT SUM(pitches) FROM day1), 0) AS last1_pitches,
       COALESCE((SELECT SUM(pitches) FROM recent), 0) AS last3_pitches,
       COALESCE((SELECT COUNT(DISTINCT pitcher_id) FROM day1), 0) AS relievers_used_last1,
       COALESCE((SELECT COUNT(DISTINCT pitcher_id) FROM recent), 0) AS relievers_used_last3,
       COALESCE((SELECT SUM(CASE WHEN holds > 0 OR saves > 0 OR blown_saves > 0 THEN 1 ELSE 0 END) FROM recent), 0) AS high_leverage_appearances,
       COALESCE((SELECT COUNT(*) FROM pitcher_days WHERE games_count >= 2), 0) AS back_to_back_pitchers`,
    [teamShort.toUpperCase(), startsAt],
  ).catch(() => ({ rows: [] as any[] }));

  if (!rows[0]) return null;

  const roleRows = await pool.query(
    `WITH recent14 AS (
       SELECT *
       FROM rm_mlb_bullpen_usage
       WHERE team_short = $1
         AND game_date < DATE($2::timestamptz)
         AND game_date >= DATE($2::timestamptz) - INTERVAL '14 days'
     )
     SELECT
       pitcher_name,
       SUM((COALESCE(saves, 0) * 3) + (COALESCE(holds, 0) * 2) + COALESCE(blown_saves, 0) + (CASE WHEN COALESCE(inherited_runners, 0) > 0 THEN 0.5 ELSE 0 END)) AS leverage_score,
       COALESCE(SUM(CASE WHEN game_date = DATE($2::timestamptz) - INTERVAL '1 day' THEN pitches ELSE 0 END), 0) AS last1_pitches,
       COALESCE(SUM(CASE WHEN game_date >= DATE($2::timestamptz) - INTERVAL '3 days' THEN pitches ELSE 0 END), 0) AS last3_pitches,
       COUNT(DISTINCT CASE WHEN game_date >= DATE($2::timestamptz) - INTERVAL '2 days' THEN game_date END) AS last2_day_appearances,
       COUNT(DISTINCT CASE WHEN game_date >= DATE($2::timestamptz) - INTERVAL '3 days' THEN game_date END) AS last3_day_appearances
     FROM recent14
     GROUP BY pitcher_name, pitcher_id
     ORDER BY leverage_score DESC, last3_day_appearances DESC, last3_pitches DESC, pitcher_name ASC
     LIMIT 3`,
    [teamShort.toUpperCase(), startsAt],
  ).catch(() => ({ rows: [] as BullpenRoleRow[] }));

  const row = rows[0];
  const fatigueScore = clamp(
    normalize(Number(row.last1_pitches || 0), 20, 70) * 0.35 +
    normalize(Number(row.last3_pitches || 0), 60, 180) * 0.35 +
    normalize(Number(row.high_leverage_appearances || 0), 1, 6) * 0.15 +
    normalize(Number(row.back_to_back_pitchers || 0), 1, 5) * 0.15,
    0,
    1
  );

  return {
    teamShort: teamShort.toUpperCase(),
    last1DayPitches: Number(row.last1_pitches || 0),
    last3DayPitches: Number(row.last3_pitches || 0),
    relieversUsedLast1Day: Number(row.relievers_used_last1 || 0),
    relieversUsedLast3Days: Number(row.relievers_used_last3 || 0),
    highLeverageAppearancesLast3Days: Number(row.high_leverage_appearances || 0),
    backToBackPitchers: Number(row.back_to_back_pitchers || 0),
    fatigueScore,
    roleAvailability: buildRoleAvailability(roleRows.rows as BullpenRoleRow[]),
  };
}