import pool from '../db';

export interface RmTeamLogo {
  id: string;
  league: string;
  team_abbr: string;
  team_name: string | null;
  file_path: string | null;
  file_exists: boolean;
  checksum_sha256: string | null;
  source: string;
  license: string | null;
  source_url: string | null;
  resolution_status: string;
  admin_override: boolean;
  created_at: string;
  updated_at: string;
}

export async function upsertLogoRecord(
  league: string,
  teamAbbr: string,
  data: Partial<Pick<RmTeamLogo, 'team_name' | 'file_path' | 'file_exists' | 'checksum_sha256' | 'source' | 'license' | 'source_url' | 'resolution_status' | 'admin_override'>>
): Promise<RmTeamLogo> {
  const { rows } = await pool.query(
    `INSERT INTO rm_team_logos (league, team_abbr, team_name, file_path, file_exists, checksum_sha256, source, license, source_url, resolution_status, admin_override)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
     ON CONFLICT (league, team_abbr) DO UPDATE SET
       team_name = COALESCE($3, rm_team_logos.team_name),
       file_path = COALESCE($4, rm_team_logos.file_path),
       file_exists = COALESCE($5, rm_team_logos.file_exists),
       checksum_sha256 = COALESCE($6, rm_team_logos.checksum_sha256),
       source = COALESCE($7, rm_team_logos.source),
       license = COALESCE($8, rm_team_logos.license),
       source_url = COALESCE($9, rm_team_logos.source_url),
       resolution_status = COALESCE($10, rm_team_logos.resolution_status),
       admin_override = COALESCE($11, rm_team_logos.admin_override),
       updated_at = NOW()
     RETURNING *`,
    [
      league,
      teamAbbr,
      data.team_name ?? null,
      data.file_path ?? null,
      data.file_exists ?? false,
      data.checksum_sha256 ?? null,
      data.source ?? 'local_cache',
      data.license ?? null,
      data.source_url ?? null,
      data.resolution_status ?? 'pending',
      data.admin_override ?? false,
    ]
  );
  return rows[0];
}

export async function getLogoRecord(league: string, teamAbbr: string): Promise<RmTeamLogo | null> {
  const { rows } = await pool.query(
    'SELECT * FROM rm_team_logos WHERE league = $1 AND team_abbr = $2',
    [league, teamAbbr]
  );
  return rows[0] || null;
}

export async function getUnresolvedTeams(): Promise<RmTeamLogo[]> {
  const { rows } = await pool.query(
    `SELECT * FROM rm_team_logos WHERE resolution_status IN ('pending', 'failed') AND admin_override = FALSE ORDER BY league, team_abbr`
  );
  return rows;
}

export async function getLogoStats(): Promise<Array<{ league: string; total: number; resolved: number; pending: number; failed: number }>> {
  const { rows } = await pool.query(
    `SELECT league,
            COUNT(*) as total,
            COUNT(*) FILTER (WHERE resolution_status = 'resolved') as resolved,
            COUNT(*) FILTER (WHERE resolution_status = 'pending') as pending,
            COUNT(*) FILTER (WHERE resolution_status = 'failed') as failed
     FROM rm_team_logos
     GROUP BY league
     ORDER BY league`
  );
  return rows;
}

export async function getAllLogos(): Promise<RmTeamLogo[]> {
  const { rows } = await pool.query('SELECT * FROM rm_team_logos ORDER BY league, team_abbr');
  return rows;
}

export async function getLogoCoverage(): Promise<Array<{
  league: string; total: number; resolved: number; fallback: number; failed: number;
}>> {
  const { rows } = await pool.query(
    `SELECT league,
            COUNT(*)::int as total,
            COUNT(*) FILTER (WHERE resolution_status = 'resolved')::int as resolved,
            COUNT(*) FILTER (WHERE resolution_status = 'fallback')::int as fallback,
            COUNT(*) FILTER (WHERE resolution_status IN ('pending', 'failed'))::int as failed
     FROM rm_team_logos
     GROUP BY league
     ORDER BY league`
  );
  return rows;
}

export async function getRecentLogos(limit: number = 20): Promise<RmTeamLogo[]> {
  const { rows } = await pool.query(
    'SELECT * FROM rm_team_logos ORDER BY updated_at DESC LIMIT $1',
    [limit]
  );
  return rows;
}

export async function getNeedsReviewLogos(): Promise<RmTeamLogo[]> {
  const { rows } = await pool.query(
    `SELECT * FROM rm_team_logos
     WHERE resolution_status IN ('fallback', 'failed', 'pending')
       AND admin_override = FALSE
     ORDER BY league, team_abbr`
  );
  return rows;
}