import pool from '../db';
import { countMlbPropFeedRows, fetchMlbPropCandidates } from './mlb-prop-markets';

export interface MlbAlertAsset {
  event_id: string;
  forecast_type: string;
  team_side: string | null;
  team_id: string | null;
  status: string;
  generated_at: string;
  starts_at: string | null;
  home_team: string | null;
  away_team: string | null;
  alerts: any[];
}

type AuditEventRow = {
  event_id: string;
  home_team: string;
  away_team: string;
  home_short: string;
  away_short: string;
  starts_at: string;
};

export interface MlbZeroCandidateIssue {
  event_id: string;
  starts_at: string;
  matchup: string;
  home_team: string;
  away_team: string;
  home_feed_rows: number;
  away_feed_rows: number;
  home_candidate_count: number;
  away_candidate_count: number;
  zero_candidate_teams: string[];
  issue_type: 'missing_feed' | 'partial_feed' | 'upstream_team_mismatch' | 'strict_filtering';
}

function classifyIssueType(params: {
  homeFeedRows: number;
  awayFeedRows: number;
  homeCandidateCount: number;
  awayCandidateCount: number;
}): 'missing_feed' | 'partial_feed' | 'upstream_team_mismatch' | 'strict_filtering' | 'healthy' {
  const missingHomeFeed = params.homeFeedRows === 0;
  const missingAwayFeed = params.awayFeedRows === 0;
  const missingHomeCandidates = params.homeCandidateCount === 0;
  const missingAwayCandidates = params.awayCandidateCount === 0;

  if (!missingHomeCandidates && !missingAwayCandidates) return 'healthy';
  if (missingHomeFeed && missingAwayFeed) return 'missing_feed';
  if (missingHomeFeed || missingAwayFeed) return 'partial_feed';
  if ((missingHomeCandidates && !missingAwayCandidates) || (!missingHomeCandidates && missingAwayCandidates)) {
    return 'upstream_team_mismatch';
  }
  return 'strict_filtering';
}

async function loadMlbEvents(dateET: string): Promise<AuditEventRow[]> {
  const { rows } = await pool.query(
    `SELECT event_id, home_team, away_team, home_short, away_short, starts_at
     FROM rm_events
     WHERE league = 'mlb'
       AND (starts_at AT TIME ZONE 'America/New_York')::date = $1::date
     ORDER BY starts_at ASC`,
    [dateET],
  ).catch(() => ({ rows: [] as AuditEventRow[] }));

  return rows as AuditEventRow[];
}

export async function getMlbAlertAssets(dateET: string): Promise<MlbAlertAsset[]> {
  const { rows } = await pool.query(
    `SELECT
       fp.event_id,
       fp.forecast_type,
       fp.team_side,
       fp.team_id,
       fp.status,
       fp.generated_at,
       e.starts_at,
       e.home_team,
       e.away_team,
       fp.vendor_inputs_summary->'mlb_alerts' AS alerts
     FROM rm_forecast_precomputed fp
     LEFT JOIN rm_events e ON e.event_id = fp.event_id
     WHERE fp.date_et = $1
       AND fp.league = 'mlb'
       AND COALESCE(jsonb_array_length(fp.vendor_inputs_summary->'mlb_alerts'), 0) > 0
     ORDER BY e.starts_at ASC NULLS LAST, fp.generated_at DESC`,
    [dateET],
  ).catch(() => ({ rows: [] as any[] }));

  return rows.map((row: any) => ({
    event_id: row.event_id,
    forecast_type: row.forecast_type,
    team_side: row.team_side,
    team_id: row.team_id,
    status: row.status,
    generated_at: row.generated_at,
    starts_at: row.starts_at,
    home_team: row.home_team,
    away_team: row.away_team,
    alerts: Array.isArray(row.alerts) ? row.alerts : [],
  }));
}

export async function getMlbZeroCandidateIssues(dateET: string): Promise<MlbZeroCandidateIssue[]> {
  const events = await loadMlbEvents(dateET);
  const rows: MlbZeroCandidateIssue[] = [];

  for (const event of events) {
    const homeFeedRows = await countMlbPropFeedRows({
      teamShort: event.home_short,
      teamName: event.home_team,
      opponentShort: event.away_short,
      startsAt: event.starts_at,
    });
    const awayFeedRows = await countMlbPropFeedRows({
      teamShort: event.away_short,
      teamName: event.away_team,
      opponentShort: event.home_short,
      startsAt: event.starts_at,
    });
    const homeCandidates = await fetchMlbPropCandidates({
      teamShort: event.home_short,
      teamName: event.home_team,
      opponentShort: event.away_short,
      startsAt: event.starts_at,
    });
    const awayCandidates = await fetchMlbPropCandidates({
      teamShort: event.away_short,
      teamName: event.away_team,
      opponentShort: event.home_short,
      startsAt: event.starts_at,
    });

    const issueType = classifyIssueType({
      homeFeedRows,
      awayFeedRows,
      homeCandidateCount: homeCandidates.length,
      awayCandidateCount: awayCandidates.length,
    });
    if (issueType === 'healthy') continue;

    rows.push({
      event_id: event.event_id,
      starts_at: event.starts_at,
      matchup: `${event.away_team} @ ${event.home_team}`,
      home_team: event.home_short,
      away_team: event.away_short,
      home_feed_rows: homeFeedRows,
      away_feed_rows: awayFeedRows,
      home_candidate_count: homeCandidates.length,
      away_candidate_count: awayCandidates.length,
      zero_candidate_teams: [
        ...(homeCandidates.length === 0 ? [event.home_short] : []),
        ...(awayCandidates.length === 0 ? [event.away_short] : []),
      ],
      issue_type: issueType,
    });
  }

  return rows;
}

export function summarizeMlbAlertAssets(alertAssets: MlbAlertAsset[]): {
  total_assets: number;
  critical_assets: number;
  warning_assets: number;
  by_code: Record<string, number>;
} {
  const byCode: Record<string, number> = {};
  let critical = 0;
  let warning = 0;

  for (const asset of alertAssets) {
    let hasCritical = false;
    let hasWarning = false;
    for (const alert of asset.alerts) {
      const code = String(alert?.code || 'unknown');
      byCode[code] = (byCode[code] || 0) + 1;
      if (String(alert?.severity || '').toLowerCase() === 'critical') hasCritical = true;
      if (String(alert?.severity || '').toLowerCase() === 'warning') hasWarning = true;
    }
    if (hasCritical) critical += 1;
    else if (hasWarning) warning += 1;
  }

  return {
    total_assets: alertAssets.length,
    critical_assets: critical,
    warning_assets: warning,
    by_code: byCode,
  };
}

export function summarizeMlbZeroCandidateIssues(issues: MlbZeroCandidateIssue[]): {
  total_games: number;
  by_issue_type: Record<string, number>;
} {
  const byIssueType: Record<string, number> = {};
  for (const issue of issues) {
    byIssueType[issue.issue_type] = (byIssueType[issue.issue_type] || 0) + 1;
  }

  return {
    total_games: issues.length,
    by_issue_type: byIssueType,
  };
}

export async function storeMlbZeroCandidateIssues(dateET: string, issues: MlbZeroCandidateIssue[]): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS rm_mlb_operational_issues (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      date_et DATE NOT NULL,
      event_id VARCHAR(255) NOT NULL,
      starts_at TIMESTAMPTZ,
      matchup VARCHAR(255) NOT NULL,
      home_team VARCHAR(20) NOT NULL,
      away_team VARCHAR(20) NOT NULL,
      home_feed_rows INTEGER NOT NULL DEFAULT 0,
      away_feed_rows INTEGER NOT NULL DEFAULT 0,
      home_candidate_count INTEGER NOT NULL DEFAULT 0,
      away_candidate_count INTEGER NOT NULL DEFAULT 0,
      zero_candidate_teams JSONB NOT NULL DEFAULT '[]'::jsonb,
      issue_type VARCHAR(50) NOT NULL,
      captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      metadata JSONB,
      UNIQUE (date_et, event_id, issue_type)
    )
  `);

  await pool.query(`DELETE FROM rm_mlb_operational_issues WHERE date_et = $1::date`, [dateET]);

  for (const issue of issues) {
    await pool.query(
      `INSERT INTO rm_mlb_operational_issues
         (date_et, event_id, starts_at, matchup, home_team, away_team, home_feed_rows, away_feed_rows,
          home_candidate_count, away_candidate_count, zero_candidate_teams, issue_type, metadata)
       VALUES ($1::date, $2, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11::jsonb, $12, $13::jsonb)`,
      [
        dateET,
        issue.event_id,
        issue.starts_at,
        issue.matchup,
        issue.home_team,
        issue.away_team,
        issue.home_feed_rows,
        issue.away_feed_rows,
        issue.home_candidate_count,
        issue.away_candidate_count,
        JSON.stringify(issue.zero_candidate_teams),
        issue.issue_type,
        JSON.stringify({
          zero_candidate_teams: issue.zero_candidate_teams,
        }),
      ],
    );
  }
}

export async function getStoredMlbZeroCandidateIssues(dateET: string): Promise<MlbZeroCandidateIssue[]> {
  const { rows } = await pool.query(
    `SELECT event_id, starts_at, matchup, home_team, away_team,
            home_feed_rows, away_feed_rows, home_candidate_count, away_candidate_count,
            zero_candidate_teams, issue_type
     FROM rm_mlb_operational_issues
     WHERE date_et = $1::date
     ORDER BY starts_at ASC, matchup ASC`,
    [dateET],
  ).catch(() => ({ rows: [] as any[] }));

  return rows.map((row: any) => ({
    event_id: row.event_id,
    starts_at: row.starts_at,
    matchup: row.matchup,
    home_team: row.home_team,
    away_team: row.away_team,
    home_feed_rows: Number(row.home_feed_rows || 0),
    away_feed_rows: Number(row.away_feed_rows || 0),
    home_candidate_count: Number(row.home_candidate_count || 0),
    away_candidate_count: Number(row.away_candidate_count || 0),
    zero_candidate_teams: Array.isArray(row.zero_candidate_teams) ? row.zero_candidate_teams : [],
    issue_type: row.issue_type,
  }));
}

export async function getMlbIssueHistory(days: number): Promise<{
  daily: Array<{ date_et: string; issue_type: string; count: number }>;
  recurring_matchups: Array<{ matchup: string; issue_type: string; appearances: number; latest_date_et: string }>;
}> {
  const safeDays = Math.max(1, Math.min(days, 30));

  const daily = await pool.query(
    `SELECT date_et::text AS date_et, issue_type, COUNT(*)::int AS count
     FROM rm_mlb_operational_issues
     WHERE date_et >= (CURRENT_DATE - ($1::int - 1))
     GROUP BY date_et, issue_type
     ORDER BY date_et DESC, issue_type ASC`,
    [safeDays],
  ).then(res => res.rows).catch(() => []);

  const recurring = await pool.query(
    `SELECT matchup, issue_type, COUNT(*)::int AS appearances, MAX(date_et)::text AS latest_date_et
     FROM rm_mlb_operational_issues
     WHERE date_et >= (CURRENT_DATE - ($1::int - 1))
     GROUP BY matchup, issue_type
     ORDER BY appearances DESC, latest_date_et DESC, matchup ASC
     LIMIT 25`,
    [safeDays],
  ).then(res => res.rows).catch(() => []);

  return {
    daily: daily.map((row: any) => ({
      date_et: row.date_et,
      issue_type: row.issue_type,
      count: Number(row.count || 0),
    })),
    recurring_matchups: recurring.map((row: any) => ({
      matchup: row.matchup,
      issue_type: row.issue_type,
      appearances: Number(row.appearances || 0),
      latest_date_et: row.latest_date_et,
    })),
  };
}