"""Worker CLI for TipSharks ingestion and computation tasks."""

import asyncio
import json
import os
import subprocess
import sys
from calendar import monthrange
from datetime import date, timedelta

import click
from rich.console import Console
from rich.table import Table

from packages.core.common.logging import get_logger, setup_logging
from packages.core.common.settings import get_settings
from packages.core.common.utils import parse_date
from packages.core.storage.database import get_session
from packages.core.storage.ingestion import IngestionService
from packages.core.storage.models import Meeting, Race
from packages.core.storage.repositories import StarterRepository, normalize_race_data

# Setup logging
setup_logging()
logger = get_logger(__name__)
console = Console()


def _subtract_months(d: date, months: int) -> date:
    """Subtract *months* from *d*, clamping day to month length.

    Args:
        d: Starting date.
        months: Number of months to subtract (must be >= 0).

    Returns:
        Date *months* months before *d*.
    """
    target_month = d.month - 1 - months  # 0-indexed
    target_year = d.year + target_month // 12
    target_month = target_month % 12 + 1
    max_day = monthrange(target_year, target_month)[1]
    target_day = min(d.day, max_day)
    return date(target_year, target_month, target_day)


@click.group()
def cli():
    """TipSharks worker CLI.

    Commands for ingesting TAB racing data and computing ratings.
    """
    pass


@cli.command("normalize-races")
@click.option(
    "--from",
    "date_from",
    required=False,
    help="Start date (YYYY-MM-DD)",
)
@click.option(
    "--to",
    "date_to",
    required=False,
    help="End date (YYYY-MM-DD)",
)
@click.option(
    "--commit-every",
    default=200,
    show_default=True,
    help="Commit interval for database updates",
)
def normalize_races(date_from: str, date_to: str, commit_every: int) -> None:
    """Normalize race/starter JSON structure and backfill starters from raw JSON."""
    start_date = parse_date(date_from) if date_from else None
    end_date = parse_date(date_to) if date_to else None

    with get_session() as session:
        query = session.query(Race).join(Meeting)
        if start_date:
            query = query.filter(Meeting.meeting_date >= start_date)
        if end_date:
            query = query.filter(Meeting.meeting_date <= end_date)

        race_ids = [
            race_id
            for (race_id,) in query.with_entities(Race.id)
            .order_by(Meeting.meeting_date, Race.race_number)
            .all()
        ]
        total = len(race_ids)
        console.print(f"[bold]Normalizing {total} races[/bold]")

        updated_races = 0
        upserted_starters = 0

        for idx, race_id in enumerate(race_ids, 1):
            race = session.query(Race).filter(Race.id == race_id).one()
            normalized = normalize_race_data(race.raw_json or {})
            if normalized != race.raw_json:
                race.raw_json = normalized
                updated_races += 1

            starters = (normalized.get("raw_json") or {}).get("starters")
            if isinstance(starters, list):
                for starter in starters:
                    if not isinstance(starter, dict):
                        continue
                    placing = starter.get("placing")
                    StarterRepository.upsert(session, race.id, starter, placing=placing)
                    upserted_starters += 1

            if idx % commit_every == 0:
                session.commit()
                console.print(f"[dim]Processed {idx}/{total} races[/dim]")

        session.commit()

    console.print(
        f"[green]✓ Updated races: {updated_races}, upserted starters: {upserted_starters}[/green]"
    )


@cli.command()
@click.option(
    "--from",
    "date_from",
    required=False,
    help="Start date (YYYY-MM-DD)",
)
@click.option(
    "--to",
    "date_to",
    required=False,
    help="End date (YYYY-MM-DD)",
)
@click.option(
    "--date",
    "single_date",
    required=False,
    help="Single date to ingest (YYYY-MM-DD)",
)
@click.option(
    "--category",
    "category",
    required=False,
    type=click.Choice(["T", "H", "G"], case_sensitive=False),
    help="Racing category: T (Thoroughbred), H (Harness), G (Greyhound)",
)
def ingest(date_from: str, date_to: str, single_date: str, category: str):
    """Ingest meetings and races from TAB API.

    Examples:
        ingest --from 2024-01-01 --to 2024-01-31
        ingest --date 2024-01-15
        ingest --date 2024-01-15 --category H
    """
    # Validate inputs
    if single_date:
        if date_from or date_to:
            console.print("[red]Error: Cannot use --date with --from/--to[/red]")
            sys.exit(1)
        start_date = parse_date(single_date)
        end_date = start_date
    elif date_from and date_to:
        start_date = parse_date(date_from)
        end_date = parse_date(date_to)
    else:
        console.print("[red]Error: Must specify either --date or --from/--to[/red]")
        sys.exit(1)

    settings = get_settings()
    effective_category = category.upper() if category else settings.tab.default_category

    console.print(
        f"\n[bold]Ingesting {effective_category} meetings from {start_date} to {end_date}[/bold]\n"
    )

    try:
        with get_session() as session:
            service = IngestionService(session)
            meetings, races, starters = asyncio.run(
                service.ingest_date_range(
                    start_date, end_date, category=effective_category
                )
            )

        # Display results
        table = Table(title="Ingestion Results")
        table.add_column("Entity", style="cyan")
        table.add_column("Count", style="green", justify="right")

        table.add_row("Meetings", str(meetings))
        table.add_row("Races", str(races))
        table.add_row("Starters", str(starters))
        table.add_row(
            "Errors",
            str(service.stats["errors"]),
            style="red" if service.stats["errors"] > 0 else "green",
        )

        console.print(table)

        if service.stats["errors"] > 0:
            console.print(
                f"\n[yellow]⚠ {service.stats['errors']} errors occurred during ingestion[/yellow]"
            )
        else:
            console.print("\n[green]✓ Ingestion completed successfully[/green]")

    except Exception as e:
        logger.error(f"Ingestion failed: {e}", exc_info=True)
        console.print(f"\n[red]✗ Ingestion failed: {e}[/red]")
        sys.exit(1)


@cli.command()
@click.option(
    "--from",
    "date_from",
    required=True,
    help="Start date (YYYY-MM-DD)",
)
@click.option(
    "--to",
    "date_to",
    required=True,
    help="End date (YYYY-MM-DD)",
)
@click.option(
    "--clear",
    is_flag=True,
    help="Clear existing ratings before recompute",
)
def recompute(date_from: str, date_to: str, clear: bool):
    """Recompute ratings from stored race results.

    This will deterministically recompute all ratings in the date range.

    Examples:
        recompute --from 2024-01-01 --to 2024-12-31
        recompute --from 2024-01-01 --to 2024-12-31 --clear
    """
    start_date = parse_date(date_from)
    end_date = parse_date(date_to)

    console.print(
        f"\n[bold]Recomputing ratings from {start_date} to {end_date}[/bold]\n"
    )

    if clear:
        console.print("[yellow]Clearing existing ratings...[/yellow]")

    try:
        # Import here to avoid circular dependency
        from packages.core.ratings.recompute import recompute_ratings

        with get_session() as session:
            snapshot_count = recompute_ratings(
                session, start_date, end_date, clear_existing=clear
            )

        console.print(
            f"\n[green]✓ Recompute complete: {snapshot_count} rating snapshots created[/green]"
        )

    except Exception as e:
        logger.error(f"Recompute failed: {e}", exc_info=True)
        console.print(f"\n[red]✗ Recompute failed: {e}[/red]")
        sys.exit(1)


@cli.command()
@click.option(
    "--urls",
    "urls_file",
    required=True,
    help="File containing HRNZ result URLs (one per line)",
)
@click.option(
    "--overwrite/--no-overwrite",
    default=False,
    show_default=True,
    help="Overwrite existing meetings/races/starters for matching HRNZ meeting IDs",
)
@click.option(
    "--from",
    "date_from",
    required=False,
    help="Filter: Start date (YYYY-MM-DD)",
)
@click.option(
    "--to",
    "date_to",
    required=False,
    help="Filter: End date (YYYY-MM-DD)",
)
def scrape_hrnz(urls_file: str, overwrite: bool, date_from: str, date_to: str):
    """Import historical data from HRNZ results archive.

    Scrapes HRNZ InfoHorse results pages and imports into database.

    WARNING: Web scraping should only be used if official API access is unavailable.
    Always check HRNZ's Terms of Service before scraping.

    Examples:
        scrape-hrnz --urls hrnz_urls.txt
        scrape-hrnz --urls hrnz_urls.txt --from 2024-01-01 --to 2024-12-31

    URL File Format:
        102402rs.htm
        102502rs.htm
        102602rs.htm
    """
    from datetime import date as date_type

    from packages.core.storage.repositories import (
        DriverRepository,
        HorseRepository,
        MeetingRepository,
        RaceRepository,
        StarterRepository,
        TrainerRepository,
    )
    from packages.hrnz_scraper import HRNZScraper
    from packages.hrnz_scraper.mapper import HRNZDataMapper

    # Parse date filters
    start_date = parse_date(date_from) if date_from else date_type(2000, 1, 1)
    end_date = parse_date(date_to) if date_to else date_type(2030, 12, 31)

    # Read URLs from file
    try:
        with open(urls_file) as f:
            urls = [line.strip() for line in f if line.strip()]
    except FileNotFoundError:
        console.print(f"[red]Error: File not found: {urls_file}[/red]")
        sys.exit(1)

    console.print(f"\n[bold]Scraping {len(urls)} HRNZ meetings[/bold]")
    console.print(f"Date filter: {start_date} to {end_date}\n")
    console.print(
        "[yellow]⚠ Using web scraper - please ensure compliance with HRNZ ToS[/yellow]\n"
    )

    stats = {
        "meetings": 0,
        "races": 0,
        "starters": 0,
        "horses": 0,
        "drivers": 0,
        "trainers": 0,
        "errors": 0,
    }

    try:

        async def scrape_and_import():
            async with HRNZScraper() as scraper:
                for idx, url in enumerate(urls, 1):
                    try:
                        console.print(f"[{idx}/{len(urls)}] Scraping {url}...")

                        # Scrape meeting
                        scraped = await scraper.get_meeting_results(url)

                        # Check date filter
                        meeting_date_str = scraped.get("date")
                        if meeting_date_str:
                            meeting_date = date_type.fromisoformat(meeting_date_str)
                            if not (start_date <= meeting_date <= end_date):
                                console.print(
                                    "  [dim]Skipped (outside date range)[/dim]"
                                )
                                continue

                        # Map to TipSharks format
                        mapper = HRNZDataMapper()
                        meeting = mapper.map_meeting(scraped)
                        entities = mapper.map_entities(scraped)

                        # Import to database
                        with get_session() as session:
                            if overwrite:
                                session.query(Meeting).filter(
                                    Meeting.id == meeting["meeting"]
                                ).delete(synchronize_session=False)
                                session.commit()

                            # Upsert meeting
                            MeetingRepository.upsert(session, meeting)
                            stats["meetings"] += 1

                            # Upsert entities (horses, drivers, trainers)
                            for horse in entities["horses"]:
                                HorseRepository.upsert(
                                    session,
                                    horse["id"],
                                    horse["name"],
                                    horse.get("raw_json"),
                                )
                                stats["horses"] += 1

                            for driver in entities["drivers"]:
                                DriverRepository.upsert(
                                    session, driver["name"], driver_id=driver.get("id")
                                )
                                stats["drivers"] += 1

                            for trainer in entities["trainers"]:
                                TrainerRepository.upsert(
                                    session,
                                    trainer["name"],
                                    trainer_id=trainer.get("id"),
                                )
                                stats["trainers"] += 1

                            # Upsert races and get race_id_map
                            races = mapper.map_races(scraped, meeting["meeting"])
                            race_id_map = {}
                            for race in races:
                                race_obj = RaceRepository.upsert(
                                    session, meeting["meeting"], race
                                )
                                race_id_map[race["race_number"]] = race_obj.id
                                stats["races"] += 1

                            # Upsert starters
                            starters = mapper.map_starters(scraped, race_id_map)
                            for starter in starters:
                                StarterRepository.upsert(
                                    session,
                                    starter["race_id"],
                                    starter,
                                    starter.get("placing"),
                                )
                                stats["starters"] += 1

                            session.commit()

                        console.print(
                            f"  [green]✓ Imported: {len(races)} races, "
                            f"{len(starters)} starters[/green]"
                        )

                    except Exception as e:
                        stats["errors"] += 1
                        logger.error(f"Failed to scrape {url}: {e}")
                        console.print(f"  [red]✗ Error: {e}[/red]")
                        continue

        asyncio.run(scrape_and_import())

        # Display results
        table = Table(title="HRNZ Scraping Results")
        table.add_column("Entity", style="cyan")
        table.add_column("Count", style="green", justify="right")

        table.add_row("Meetings", str(stats["meetings"]))
        table.add_row("Races", str(stats["races"]))
        table.add_row("Starters", str(stats["starters"]))
        table.add_row("Horses", str(stats["horses"]))
        table.add_row("Drivers", str(stats["drivers"]))
        table.add_row("Trainers", str(stats["trainers"]))
        table.add_row(
            "Errors",
            str(stats["errors"]),
            style="red" if stats["errors"] > 0 else "green",
        )

        console.print("\n")
        console.print(table)

        if stats["errors"] > 0:
            console.print(
                f"\n[yellow]⚠ {stats['errors']} errors occurred during scraping[/yellow]"
            )
            console.print(
                "[yellow]Tip: Run 'recompute' to compute ratings for imported data[/yellow]"
            )
        else:
            console.print("\n[green]✓ Scraping completed successfully[/green]")
            console.print(
                "[green]Tip: Run 'recompute' to compute ratings for imported data[/green]"
            )

    except Exception as e:
        logger.error(f"Scraping failed: {e}", exc_info=True)
        console.print(f"\n[red]✗ Scraping failed: {e}[/red]")
        sys.exit(1)


@cli.command()
@click.option(
    "--from",
    "date_from",
    required=True,
    help="Start date (YYYY-MM-DD)",
)
@click.option(
    "--to",
    "date_to",
    required=True,
    help="End date (YYYY-MM-DD)",
)
@click.option(
    "--race-type",
    "race_day_type",
    type=click.Choice(["OfficialRaces", "TrialRaces", "WorkoutRaces"]),
    default="OfficialRaces",
    show_default=True,
    help="Race day type filter from the results enquiry",
)
@click.option(
    "--club",
    "club_no",
    default="",
    show_default=True,
    help="Optional club number filter (leave blank for all clubs)",
)
@click.option(
    "--overwrite/--no-overwrite",
    default=False,
    show_default=True,
    help="Overwrite existing meetings/races/starters for matching HRNZ meeting IDs",
)
def scrape_hrnz_enquiry(
    date_from: str, date_to: str, race_day_type: str, club_no: str, overwrite: bool
):
    """Scrape HRNZ historical results via the Results Enquiry page."""
    from datetime import date as date_type

    from packages.core.storage.repositories import (
        DriverRepository,
        HorseRepository,
        MeetingRepository,
        RaceRepository,
        StarterRepository,
        TrainerRepository,
    )
    from packages.hrnz_scraper import HRNZHistoricalResultsScraper
    from packages.hrnz_scraper.mapper import HRNZDataMapper

    start_date = parse_date(date_from)
    end_date = parse_date(date_to)

    console.print(
        f"\n[bold]Scraping HRNZ results from {start_date} to {end_date}[/bold]\n"
    )
    console.print(
        "[yellow]⚠ Using web scraper - please ensure compliance with HRNZ ToS[/yellow]\n"
    )

    stats = {
        "meetings": 0,
        "races": 0,
        "starters": 0,
        "horses": 0,
        "drivers": 0,
        "trainers": 0,
        "errors": 0,
    }

    try:

        async def scrape_and_import():
            async with HRNZHistoricalResultsScraper() as scraper:
                meetings = [
                    meeting
                    async for meeting in scraper.iter_meetings(
                        start_date,
                        end_date,
                        race_day_type=race_day_type,
                        club_no=club_no,
                    )
                ]
                for idx, meeting_meta in enumerate(meetings, 1):
                    try:
                        url = meeting_meta["results_url"]
                        console.print(f"[{idx}/{len(meetings)}] Scraping {url}...")

                        scraped = await scraper.get_meeting_results(url, meeting_meta)

                        if not scraped.get("date") and meeting_meta.get("meeting_date"):
                            scraped["date"] = meeting_meta["meeting_date"].isoformat()

                        if not scraped.get("races"):
                            console.print("  [dim]Skipped (no races found)[/dim]")
                            continue

                        meeting_date_str = scraped.get("date")
                        if meeting_date_str:
                            meeting_date = date_type.fromisoformat(meeting_date_str)
                            if not (start_date <= meeting_date <= end_date):
                                console.print(
                                    "  [dim]Skipped (outside date range)[/dim]"
                                )
                                continue

                        mapper = HRNZDataMapper()
                        meeting = mapper.map_meeting(scraped)
                        entities = mapper.map_entities(scraped)

                        with get_session() as session:
                            if overwrite:
                                session.query(Meeting).filter(
                                    Meeting.id == meeting["meeting"]
                                ).delete(synchronize_session=False)
                                session.commit()

                            MeetingRepository.upsert(session, meeting)
                            stats["meetings"] += 1

                            for horse in entities["horses"]:
                                HorseRepository.upsert(
                                    session,
                                    horse["id"],
                                    horse["name"],
                                    horse.get("raw_json"),
                                )
                                stats["horses"] += 1

                            for driver in entities["drivers"]:
                                DriverRepository.upsert(
                                    session, driver["name"], driver_id=driver.get("id")
                                )
                                stats["drivers"] += 1

                            for trainer in entities["trainers"]:
                                TrainerRepository.upsert(
                                    session,
                                    trainer["name"],
                                    trainer_id=trainer.get("id"),
                                )
                                stats["trainers"] += 1

                            races = mapper.map_races(scraped, meeting["meeting"])
                            race_id_map = {}
                            for race in races:
                                race_obj = RaceRepository.upsert(
                                    session, meeting["meeting"], race
                                )
                                race_id_map[race["race_number"]] = race_obj.id
                                stats["races"] += 1

                            starters = mapper.map_starters(scraped, race_id_map)
                            for starter in starters:
                                StarterRepository.upsert(
                                    session,
                                    starter["race_id"],
                                    starter,
                                    starter.get("placing"),
                                )
                                stats["starters"] += 1

                            session.commit()

                        console.print(
                            f"  [green]✓ Imported: {len(races)} races, "
                            f"{len(starters)} starters[/green]"
                        )
                    except Exception as e:
                        stats["errors"] += 1
                        logger.error(
                            f"Failed to scrape {meeting_meta.get('results_url')}: {e}"
                        )
                        console.print(f"  [red]✗ Error: {e}[/red]")
                        continue

        asyncio.run(scrape_and_import())

        table = Table(title="HRNZ Results Enquiry Scraping Results")
        table.add_column("Entity", style="cyan")
        table.add_column("Count", style="green", justify="right")

        table.add_row("Meetings", str(stats["meetings"]))
        table.add_row("Races", str(stats["races"]))
        table.add_row("Starters", str(stats["starters"]))
        table.add_row("Horses", str(stats["horses"]))
        table.add_row("Drivers", str(stats["drivers"]))
        table.add_row("Trainers", str(stats["trainers"]))
        table.add_row(
            "Errors",
            str(stats["errors"]),
            style="red" if stats["errors"] > 0 else "green",
        )

        console.print("\n")
        console.print(table)

        if stats["errors"] > 0:
            console.print(
                f"\n[yellow]⚠ {stats['errors']} errors occurred during scraping[/yellow]"
            )
            console.print(
                "[yellow]Tip: Run 'recompute' to compute ratings for imported data[/yellow]"
            )
        else:
            console.print("\n[green]✓ Scraping completed successfully[/green]")
            console.print(
                "[green]Tip: Run 'recompute' to compute ratings for imported data[/green]"
            )

    except Exception as e:
        logger.error(f"Scraping failed: {e}", exc_info=True)
        console.print(f"\n[red]✗ Scraping failed: {e}[/red]")
        sys.exit(1)


@cli.command()
def refresh_club_codes():
    """Refresh HRNZ club codes by scraping the HRNZ results pages.

    Fetches the latest list of club codes from HRNZ, compares them with
    the hardcoded ``HRNZ_ALL_CLUB_CODES`` list, and reports any
    differences (new, missing, or unmatched codes).
    """
    from packages.hrnz_scraper.club_refresh import (
        generate_diff_report,
        refresh_club_codes,
    )

    console.print("\n[bold cyan]Refreshing HRNZ club codes...[/bold cyan]\n")

    try:
        result = refresh_club_codes()
        report = generate_diff_report(result)

        console.print(report)

        new_codes = result.get("new", [])
        missing_codes = result.get("missing", [])

        if result.get("error"):
            console.print(f"\n[yellow]⚠ {result['error']}[/yellow]")

        if new_codes:
            console.print(
                "\n[yellow]⚠ New club codes detected. "
                "Update HRNZ_ALL_CLUB_CODES in packages/core/common/settings.py "
                "to include these codes.[/yellow]"
            )
            console.print(f"\nAdd to settings.py:\n  {new_codes!r}")

        if missing_codes and not new_codes:
            console.print(
                "\n[green]✓ All hardcoded club codes were found online.[/green]"
            )
        elif not new_codes and not missing_codes:
            console.print(
                "\n[green]✓ Hardcoded club codes are up to date "
                f"(all {len(result.get('fetched', []))} codes confirmed).[/green]"
            )

    except Exception as e:
        logger.error(f"Club code refresh failed: {e}", exc_info=True)
        console.print(f"\n[red]✗ Club code refresh failed: {e}[/red]")
        sys.exit(1)


@cli.command()
def info():
    """Display configuration information."""
    settings = get_settings()

    table = Table(title="TipSharks Configuration")
    table.add_column("Setting", style="cyan")
    table.add_column("Value", style="yellow")

    # TAB API settings
    table.add_row("TAB Base URL", settings.tab.base_url)
    table.add_row("TAB Default Category", settings.tab.default_category)
    table.add_row("TAB Default Country", settings.tab.default_country)
    table.add_row("TAB Mock Mode", "✓" if settings.tab.mock_mode else "✗")

    # Database
    table.add_row(
        "Database URL",
        (
            settings.database.url.split("@")[-1]
            if "@" in settings.database.url
            else settings.database.url
        ),
    )
    table.add_row("Log Level", settings.logging.level)

    # Rating settings
    table.add_row("Elo Scale (C)", str(settings.rating.elo_scale_c))
    table.add_row("Elo K", str(settings.rating.elo_k_base))
    table.add_row("Driver Weight (α)", str(settings.rating.driver_weight_alpha))
    table.add_row("Trainer Weight (β)", str(settings.rating.trainer_weight_beta))
    table.add_row("Enable Driver", "✓" if settings.rating.enable_driver else "✗")
    table.add_row("Enable Trainer", "✓" if settings.rating.enable_trainer else "✗")
    table.add_row(
        "Enable Adjustments", "✓" if settings.rating.enable_adjustments else "✗"
    )

    console.print(table)


@cli.command()
@click.option(
    "--from",
    "date_from",
    required=True,
    help="Start date (YYYY-MM-DD)",
)
@click.option(
    "--to",
    "date_to",
    required=True,
    help="End date (YYYY-MM-DD)",
)
@click.option(
    "--out",
    "output_file",
    required=False,
    help="Output JSON file path (optional)",
)
def data_quality(date_from, date_to, output_file):
    """Generate data quality report for date range.

    Validates data completeness, accuracy, and identifies issues.
    """
    import json

    from packages.core.common.data_quality import (
        DataQualityValidator,
        check_data_freshness,
    )

    console.print("[bold cyan]Generating Data Quality Report...[/bold cyan]")

    # Parse dates
    start_date = parse_date(date_from)
    end_date = parse_date(date_to)

    console.print(f"Date range: {start_date} to {end_date}")

    with get_session() as session:
        validator = DataQualityValidator(session)

        # Generate report
        report = validator.generate_report(start_date, end_date)

        # Check data freshness
        freshness_issue = check_data_freshness(session)
        if freshness_issue:
            report.issues.append(freshness_issue)

    # Display summary
    summary_table = Table(title="Data Quality Summary")
    summary_table.add_column("Metric", style="cyan")
    summary_table.add_column("Value", style="yellow")

    summary_table.add_row("Total Meetings", str(report.total_meetings))
    summary_table.add_row("Total Races", str(report.total_races))
    summary_table.add_row("Total Starters", str(report.total_starters))
    summary_table.add_row("Errors", f"[red]{report.error_count}[/red]")
    summary_table.add_row("Warnings", f"[yellow]{report.warning_count}[/yellow]")
    summary_table.add_row(
        "Info", str(sum(1 for i in report.issues if i.severity == "info"))
    )

    console.print(summary_table)

    # Display metrics
    if report.metrics:
        metrics_table = Table(title="Data Metrics")
        metrics_table.add_column("Metric", style="cyan")
        metrics_table.add_column("Value", style="yellow")

        for key, value in report.metrics.items():
            formatted_key = key.replace("_", " ").title()
            metrics_table.add_row(formatted_key, str(value))

        console.print(metrics_table)

    # Display issues by category
    if report.issues:
        console.print("\n[bold]Issues by Category:[/bold]")

        for category in ["error", "warning", "info"]:
            category_issues = [i for i in report.issues if i.severity == category]
            if category_issues:
                issues_table = Table(title=f"{category.upper()} Issues")
                issues_table.add_column("Category", style="cyan")
                issues_table.add_column("Message", style="yellow")
                issues_table.add_column("Race ID", style="magenta")

                for issue in category_issues[:20]:  # Show first 20
                    issues_table.add_row(
                        issue.category,
                        issue.message,
                        str(issue.race_id) if issue.race_id else "-",
                    )

                console.print(issues_table)

                if len(category_issues) > 20:
                    console.print(
                        f"[dim]... and {len(category_issues) - 20} more {category} issues[/dim]"
                    )

    # Save to file if requested
    if output_file:
        report_data = {
            "start_date": report.start_date.isoformat(),
            "end_date": report.end_date.isoformat(),
            "generated_at": report.generated_at.isoformat(),
            "summary": {
                "total_meetings": report.total_meetings,
                "total_races": report.total_races,
                "total_starters": report.total_starters,
                "error_count": report.error_count,
                "warning_count": report.warning_count,
            },
            "metrics": report.metrics,
            "issues": [
                {
                    "severity": issue.severity,
                    "category": issue.category,
                    "message": issue.message,
                    "race_id": issue.race_id,
                    "meeting_id": issue.meeting_id,
                    "starter_id": issue.starter_id,
                    "details": issue.details,
                }
                for issue in report.issues
            ],
        }

        with open(output_file, "w") as f:
            json.dump(report_data, f, indent=2)

        console.print(f"\n[green]Report saved to {output_file}[/green]")

    # Exit with error code if errors found
    if report.has_errors:
        console.print("\n[bold red]⚠ Data quality check FAILED[/bold red]")
        sys.exit(1)
    else:
        console.print("\n[bold green]✓ Data quality check PASSED[/bold green]")
        sys.exit(0)


@cli.command()
@click.option(
    "--months",
    default=12,
    type=click.IntRange(1, 24),
    show_default=True,
    help="Number of months to backfill (max 24)",
)
@click.option(
    "--category",
    type=click.Choice(["T", "H", "G"], case_sensitive=False),
    help="Racing category: T (Thoroughbred), H (Harness), G (Greyhound)",
)
@click.option(
    "--source",
    type=click.Choice(["tab", "ingest"], case_sensitive=False),
    default="tab",
    show_default=True,
    help="Data source: tab (TAB API) or ingest (tab-api-ingest service)",
)
@click.option(
    "--clear",
    is_flag=True,
    help="Clear existing ratings before recompute",
)
@click.option(
    "--learn-adjustments",
    is_flag=True,
    help="Learn barrier/handicap adjustments during recompute",
)
@click.option(
    "--skip-eval",
    is_flag=True,
    help="Skip accuracy evaluation after recompute",
)
def backfill(months, category, source, clear, learn_adjustments, skip_eval):
    """Backfill historical data and recompute ratings.

    Ingests data week by week from (today - months) to today,
    then recomputes all ratings and evaluates prediction accuracy.

    Examples:

        backfill --months 12 --category H --source tab

        backfill --months 6 --category T --clear --learn-adjustments

        backfill --months 3 --source ingest --skip-eval
    """
    # Calculate date range
    today = date.today()
    start_date = _subtract_months(today, months)

    settings = get_settings()
    effective_category = category.upper() if category else settings.tab.default_category

    console.print(
        f"\n[bold]Backfilling {effective_category} data "
        f"from {start_date} to {today}[/bold]"
    )
    console.print(
        f"Source: {source}, Months: {months}, "
        f"Clear: {clear}, Learn adjustments: {learn_adjustments}\n"
    )

    # Track totals
    totals: dict[str, int] = {
        "meetings": 0,
        "races": 0,
        "starters": 0,
        "errors": 0,
    }
    failed_weeks: list[tuple[date, date, str]] = []

    # ── Week-by-week ingestion ───────────────────────────────────
    current = start_date
    week_count = 0
    while current <= today:
        week_end = min(current + timedelta(days=6), today)
        week_count += 1
        console.print(
            f"[bold cyan]Week {week_count}: {current} to {week_end}[/bold cyan]"
        )

        try:
            with get_session() as session:
                service = IngestionService(session, source=source)
                meetings, races, starters = asyncio.run(
                    service.ingest_date_range(
                        current, week_end, category=effective_category
                    )
                )
                totals["meetings"] += meetings
                totals["races"] += races
                totals["starters"] += starters
                totals["errors"] += service.stats["errors"]

            console.print(
                f"  [green]✓ {meetings} meetings, "
                f"{races} races, {starters} starters[/green]"
            )
        except Exception as e:
            logger.error(f"Week {current} to {week_end} failed: {e}", exc_info=True)
            console.print(f"  [red]✗ Week failed: {e}[/red]")
            failed_weeks.append((current, week_end, str(e)))
            totals["errors"] += 1

        current = week_end + timedelta(days=1)

    # ── Ingestion summary ────────────────────────────────────────
    console.print("\n[bold]Ingestion complete:[/bold]")
    console.print(f"  Weeks processed: {week_count}")
    console.print(f"  Total meetings: {totals['meetings']}")
    console.print(f"  Total races: {totals['races']}")
    console.print(f"  Total starters: {totals['starters']}")

    if failed_weeks:
        console.print(f"\n[yellow]⚠ {len(failed_weeks)} week(s) had errors:[/yellow]")
        for fw_start, fw_end, fw_err in failed_weeks:
            console.print(f"  [yellow]{fw_start} to {fw_end}: {fw_err}[/yellow]")

    # ── Recompute ratings ────────────────────────────────────────
    snapshot_count = 0
    if totals["meetings"] == 0:
        console.print("\n[yellow]No data ingested — skipping recompute.[/yellow]")
    else:
        console.print(
            f"\n[bold]Recomputing ratings from " f"{start_date} to {today}...[/bold]"
        )
        try:
            from packages.core.ratings.recompute import recompute_ratings

            with get_session() as session:
                snapshot_count = recompute_ratings(
                    session,
                    start_date,
                    today,
                    clear_existing=clear,
                    learn_adjustments=learn_adjustments,
                )
            console.print(f"[green]✓ {snapshot_count} rating snapshots created[/green]")
        except Exception as e:
            logger.error(f"Recompute failed: {e}", exc_info=True)
            console.print(f"\n[red]✗ Recompute failed: {e}[/red]")
            sys.exit(1)

    # ── Evaluation ───────────────────────────────────────────────
    brier: float | None = None
    winner_acc: float | None = None
    if not skip_eval and totals["meetings"] > 0:
        console.print("\n[bold]Evaluating prediction accuracy...[/bold]")
        try:
            script_path = os.path.join(
                os.path.dirname(os.path.abspath(__file__)),
                "..",
                "..",
                "..",
                "scripts",
                "evaluate_accuracy.py",
            )
            eval_result = subprocess.run(
                [
                    sys.executable,
                    script_path,
                    "--from",
                    start_date.isoformat(),
                    "--to",
                    today.isoformat(),
                    "--json",
                ],
                capture_output=True,
                text=True,
                check=True,
                timeout=600,
            )
            data = json.loads(eval_result.stdout)
            if "all" in data and data["all"]:
                brier = data["all"]["brier"]
                winner_acc = data["all"]["winner_acc"]
                console.print(f"  [green]Winner accuracy: {winner_acc:.4f}[/green]")
                console.print(f"  [green]Brier score: {brier:.4f}[/green]")
            else:
                console.print("  [yellow]Evaluation returned no results[/yellow]")
        except Exception as e:
            logger.error(f"Evaluation failed: {e}", exc_info=True)
            console.print(f"  [yellow]⚠ Evaluation skipped due to error: {e}[/yellow]")

    # ── Final summary table ──────────────────────────────────────
    table = Table(title="Backfill Summary")
    table.add_column("Metric", style="cyan")
    table.add_column("Value", style="green", justify="right")

    table.add_row("Weeks processed", str(week_count))
    table.add_row("Meetings ingested", str(totals["meetings"]))
    table.add_row("Races ingested", str(totals["races"]))
    table.add_row("Starters ingested", str(totals["starters"]))
    table.add_row("Rating snapshots", str(snapshot_count))
    if brier is not None:
        table.add_row("Brier score", f"{brier:.4f}")
    if winner_acc is not None:
        table.add_row("Winner accuracy", f"{winner_acc:.4f}")
    table.add_row(
        "Errors",
        str(totals["errors"]),
        style="red" if totals["errors"] > 0 else "green",
    )
    if failed_weeks:
        table.add_row("Failed weeks", str(len(failed_weeks)), style="red")

    console.print("\n")
    console.print(table)

    if totals["errors"] > 0:
        console.print(
            f"\n[yellow]⚠ Completed with {totals['errors']} error(s)[/yellow]"
        )
    else:
        console.print("\n[green]✓ Backfill completed successfully[/green]")


if __name__ == "__main__":
    cli()