# Reports Alignment Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Make local daily reports and Excel exports use the same settled-bet accounting model, fix match payout aggregation, and automatically repair historical extraction statistics derived from incorrect financial totals.

**Architecture:** Keep the existing route structure in `mbetterclient/web_dashboard/routes.py`, but unify daily financial calculations around settled `BetDetailModel` records filtered by bet datetime. Add focused regression tests around the reporting routes and a one-time database migration that recomputes historical `ExtractionStatsModel` financial fields from authoritative bet detail data.

**Tech Stack:** Python, Flask, SQLAlchemy, openpyxl, existing migration framework, pytest/unittest-style test modules already present in repo

---

## File Map

- Modify: `mbetterclient/web_dashboard/routes.py`
  - Correct `match-reports` payout aggregation
  - Unify Excel daily summary payout calculation with the daily summary API
  - Improve Excel route error handling if needed during fix
- Modify: `mbetterclient/database/migrations.py`
  - Add one migration to recompute historical `ExtractionStatsModel` financial fields
  - Register the migration in `MIGRATIONS`
- Create: `test_reports_daily_alignment.py`
  - Regression tests for daily summary, match reports, and Excel export parity
- Create: `test_reports_stats_repair_migration.py`
  - Regression tests for historical repair migration behavior

### Task 1: Add failing tests for daily summary and match reports

**Files:**
- Create: `test_reports_daily_alignment.py`
- Modify: `mbetterclient/web_dashboard/routes.py`

- [ ] **Step 1: Write the failing test**

```python
import os
import sys
from datetime import datetime
from unittest.mock import Mock

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from mbetterclient.web_dashboard.routes import is_bet_detail_winning


class DummyDetail:
    def __init__(self, match_id, amount, win_amount, result, outcome='WIN1', bet_id='bet1'):
        self.match_id = match_id
        self.amount = amount
        self.win_amount = win_amount
        self.result = result
        self.outcome = outcome
        self.bet_id = bet_id


class DummyBet:
    def __init__(self, uuid, bet_datetime):
        self.uuid = uuid
        self.bet_datetime = bet_datetime


class DummyMatch:
    def __init__(self, match_id, match_number=1):
        self.id = match_id
        self.match_number = match_number
        self.fighter1_township = 'A'
        self.fighter2_township = 'B'
        self.venue_kampala_township = 'V'
        self.status = 'completed'
        self.winning_outcomes = ['WIN1']
        self.result = 'WIN1'


def test_match_report_payout_uses_loaded_match():
    detail = DummyDetail(match_id=10, amount=100.0, win_amount=250.0, result='win')
    match = DummyMatch(match_id=10)
    session = Mock()

    assert is_bet_detail_winning(detail, match, session) is True
```

- [ ] **Step 2: Run test to verify it fails**

Run: `python -m pytest test_reports_daily_alignment.py::test_match_report_payout_uses_loaded_match -v`
Expected: test file exists and route/test setup still incomplete for the broader parity behavior; this first test should establish the file and testing path.

- [ ] **Step 3: Expand the failing test to cover route-level match payout aggregation**

```python
from mbetterclient.web_dashboard import routes as routes_module


def test_match_reports_route_counts_winning_payout_after_loading_match():
    match = DummyMatch(match_id=10)
    detail = DummyDetail(match_id=10, amount=100.0, win_amount=250.0, result='win')
    bet = DummyBet(uuid='bet1', bet_datetime=datetime(2026, 5, 1, 12, 0, 0))

    class QueryStub:
        def __init__(self, rows):
            self.rows = rows
        def join(self, *args, **kwargs):
            return self
        def filter(self, *args, **kwargs):
            return self
        def all(self):
            return self.rows
        def first(self):
            return self.rows[0] if self.rows else None
        def order_by(self, *args, **kwargs):
            return self

    session = Mock()

    def query_side_effect(model):
        model_name = getattr(model, '__name__', str(model))
        if model_name == 'BetDetailModel':
            return QueryStub([detail])
        if model_name == 'MatchModel':
            return QueryStub([match])
        if model_name == 'BetModel':
            return QueryStub([bet])
        return QueryStub([])

    session.query.side_effect = query_side_effect

    assert is_bet_detail_winning(detail, match, session) is True
```

- [ ] **Step 4: Run test to verify it fails for the intended reason**

Run: `python -m pytest test_reports_daily_alignment.py -v`
Expected: FAIL once route-level assertions are added for current broken aggregation behavior.

- [ ] **Step 5: Commit**

```bash
git add test_reports_daily_alignment.py
git commit -m "test: add failing report alignment tests"
```

### Task 2: Add failing tests for Excel export parity

**Files:**
- Modify: `test_reports_daily_alignment.py`
- Modify: `mbetterclient/web_dashboard/routes.py`

- [ ] **Step 1: Write the failing Excel parity test**

```python
from io import BytesIO
from openpyxl import load_workbook


def test_excel_summary_uses_same_payout_basis_as_daily_summary():
    workbook = load_workbook(BytesIO(build_sample_excel_bytes()))
    sheet = workbook['Daily Summary']

    assert sheet['B4'].value == '100.00'
    assert sheet['B5'].value == '250.00'
    assert sheet['B6'].value == '-150.00'
```

- [ ] **Step 2: Run test to verify it fails**

Run: `python -m pytest test_reports_daily_alignment.py::test_excel_summary_uses_same_payout_basis_as_daily_summary -v`
Expected: FAIL because `build_sample_excel_bytes` does not exist yet or current export values are still based on `ExtractionStatsModel.total_redistributed`.

- [ ] **Step 3: Add a real route-level Excel response test scaffold**

```python
def test_excel_download_returns_xlsx_response(client):
    response = client.get('/api/reports/download-excel?date=2026-05-01')

    assert response.status_code == 200
    assert response.mimetype == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
```

- [ ] **Step 4: Run tests to verify route-level failures**

Run: `python -m pytest test_reports_daily_alignment.py -v`
Expected: FAIL due to missing fixture/client wiring or current Excel calculation mismatch.

- [ ] **Step 5: Commit**

```bash
git add test_reports_daily_alignment.py
git commit -m "test: add failing excel parity tests"
```

### Task 3: Fix match report aggregation and Excel summary calculations

**Files:**
- Modify: `mbetterclient/web_dashboard/routes.py`
- Test: `test_reports_daily_alignment.py`

- [ ] **Step 1: Write the minimal route fix for match payout aggregation**

```python
for detail in bet_details:
    match_id = detail.match_id
    if match_id not in match_stats:
        match_stats[match_id] = {
            'match_id': match_id,
            'bets_count': 0,
            'payin': 0.0,
            'payout': 0.0
        }

    match = session.query(MatchModel).filter_by(id=match_id).first()

    match_stats[match_id]['bets_count'] += 1
    match_stats[match_id]['payin'] += float(detail.amount)

    if is_bet_detail_winning(detail, match, session):
        match_stats[match_id]['payout'] += float(detail.win_amount or 0)
```

- [ ] **Step 2: Write the minimal Excel summary payout fix**

```python
bet_details_query = session.query(BetDetailModel).join(
    BetModel, BetDetailModel.bet_id == BetModel.uuid
).filter(
    BetModel.bet_datetime >= start_datetime,
    BetModel.bet_datetime <= end_datetime,
    BetDetailModel.result != 'cancelled'
)

bet_details = bet_details_query.all()

total_payout = 0.0
for detail in bet_details:
    match = session.query(MatchModel).filter_by(id=detail.match_id).first()
    if is_bet_detail_winning(detail, match, session):
        total_payout += float(detail.win_amount or 0)
```

- [ ] **Step 3: Run targeted tests to verify they pass**

Run: `python -m pytest test_reports_daily_alignment.py -v`
Expected: PASS for the route and parity tests added so far.

- [ ] **Step 4: Add a tiny refactor only if needed to keep duplication readable**

```python
# Optional only if tests are green and duplication is awkward:
# extract a small local helper in routes.py for summary payout calculation
# def _calculate_report_payout_from_bet_details(session, bet_details): ...
```

- [ ] **Step 5: Re-run tests after refactor**

Run: `python -m pytest test_reports_daily_alignment.py -v`
Expected: PASS

- [ ] **Step 6: Commit**

```bash
git add mbetterclient/web_dashboard/routes.py test_reports_daily_alignment.py
git commit -m "fix: align daily reports and excel payout calculations"
```

### Task 4: Add failing tests for historical extraction stats repair

**Files:**
- Create: `test_reports_stats_repair_migration.py`
- Modify: `mbetterclient/database/migrations.py`

- [ ] **Step 1: Write the failing migration repair test**

```python
import os
import sys
from types import SimpleNamespace

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from mbetterclient.database.migrations import Migration_069_RepairExtractionStatsFinancials


class StatRow:
    def __init__(self):
        self.match_id = 10
        self.total_bets = 99
        self.total_amount_collected = 9999.0
        self.total_redistributed = 8888.0
        self.under_bets = 0
        self.under_amount = 0.0
        self.over_bets = 0
        self.over_amount = 0.0


class DetailRow:
    def __init__(self, outcome, amount, win_amount, result):
        self.outcome = outcome
        self.amount = amount
        self.win_amount = win_amount
        self.result = result


def test_repair_migration_recomputes_financial_fields_from_bet_details():
    migration = Migration_069_RepairExtractionStatsFinancials()
    stat = StatRow()
    details = [
        DetailRow('UNDER', 100.0, 0.0, 'lost'),
        DetailRow('WIN1', 50.0, 125.0, 'win'),
        DetailRow('OVER', 75.0, 0.0, 'cancelled'),
    ]

    repaired = migration._recompute_stat_row(stat, details)

    assert repaired.total_bets == 2
    assert repaired.total_amount_collected == 150.0
    assert repaired.total_redistributed == 125.0
    assert repaired.under_bets == 1
    assert repaired.under_amount == 100.0
    assert repaired.over_bets == 0
    assert repaired.over_amount == 0.0
```

- [ ] **Step 2: Run test to verify it fails**

Run: `python -m pytest test_reports_stats_repair_migration.py::test_repair_migration_recomputes_financial_fields_from_bet_details -v`
Expected: FAIL because migration/class/helper does not exist yet.

- [ ] **Step 3: Add a second failing test for cancelled-bet exclusion**

```python
def test_repair_migration_excludes_cancelled_bets_from_all_totals():
    migration = Migration_069_RepairExtractionStatsFinancials()
    stat = StatRow()
    details = [
        DetailRow('OVER', 40.0, 500.0, 'cancelled'),
    ]

    repaired = migration._recompute_stat_row(stat, details)

    assert repaired.total_bets == 0
    assert repaired.total_amount_collected == 0.0
    assert repaired.total_redistributed == 0.0
    assert repaired.over_bets == 0
    assert repaired.over_amount == 0.0
```

- [ ] **Step 4: Run tests to verify they fail correctly**

Run: `python -m pytest test_reports_stats_repair_migration.py -v`
Expected: FAIL because the repair migration has not been implemented.

- [ ] **Step 5: Commit**

```bash
git add test_reports_stats_repair_migration.py
git commit -m "test: add failing extraction stats repair migration tests"
```

### Task 5: Implement the historical repair migration

**Files:**
- Modify: `mbetterclient/database/migrations.py`
- Test: `test_reports_stats_repair_migration.py`

- [ ] **Step 1: Add the new migration class with a pure recompute helper**

```python
class Migration_069_RepairExtractionStatsFinancials(DatabaseMigration):
    """Repair historical extraction stats financial fields from authoritative bet details"""

    def __init__(self):
        super().__init__("069", "Repair extraction stats financial fields")

    def _recompute_stat_row(self, stat_row, detail_rows):
        active_details = [d for d in detail_rows if d.result != 'cancelled']
        stat_row.total_bets = len(active_details)
        stat_row.total_amount_collected = sum(float(d.amount or 0) for d in active_details)
        stat_row.total_redistributed = sum(float(d.win_amount or 0) for d in active_details if d.result in ('win', 'won'))
        stat_row.under_bets = sum(1 for d in active_details if d.outcome == 'UNDER')
        stat_row.under_amount = sum(float(d.amount or 0) for d in active_details if d.outcome == 'UNDER')
        stat_row.over_bets = sum(1 for d in active_details if d.outcome == 'OVER')
        stat_row.over_amount = sum(float(d.amount or 0) for d in active_details if d.outcome == 'OVER')
        return stat_row
```

- [ ] **Step 2: Add the migration `up` method using existing models**

```python
    def up(self, db_manager) -> bool:
        try:
            from .models import ExtractionStatsModel, BetDetailModel
            session = db_manager.get_session()
            try:
                stats_rows = session.query(ExtractionStatsModel).all()
                for stat in stats_rows:
                    detail_rows = session.query(BetDetailModel).filter_by(match_id=stat.match_id).all()
                    self._recompute_stat_row(stat, detail_rows)
                session.commit()
                logger.info(f"Repaired {len(stats_rows)} extraction stats rows")
                return True
            except Exception:
                session.rollback()
                raise
            finally:
                session.close()
        except Exception as e:
            logger.error(f"Failed to repair extraction stats financial fields: {e}")
            return False
```

- [ ] **Step 3: Register the migration in `MIGRATIONS`**

```python
    Migration_068_ResetRedistributionBalance(),
    Migration_069_RepairExtractionStatsFinancials(),
]
```

- [ ] **Step 4: Run the migration tests to verify they pass**

Run: `python -m pytest test_reports_stats_repair_migration.py -v`
Expected: PASS

- [ ] **Step 5: Run the report tests to catch regressions**

Run: `python -m pytest test_reports_daily_alignment.py test_reports_stats_repair_migration.py -v`
Expected: PASS

- [ ] **Step 6: Commit**

```bash
git add mbetterclient/database/migrations.py test_reports_stats_repair_migration.py
git commit -m "fix: repair historical extraction stats financial fields"
```

### Task 6: Verify behavior end-to-end

**Files:**
- Modify: `mbetterclient/web_dashboard/routes.py`
- Modify: `mbetterclient/database/migrations.py`
- Test: `test_reports_daily_alignment.py`
- Test: `test_reports_stats_repair_migration.py`

- [ ] **Step 1: Run focused tests for reporting behavior**

Run: `python -m pytest test_reports_daily_alignment.py -v`
Expected: PASS

- [ ] **Step 2: Run focused tests for migration repair behavior**

Run: `python -m pytest test_reports_stats_repair_migration.py -v`
Expected: PASS

- [ ] **Step 3: Run any existing nearby report sync tests for regression coverage**

Run: `python -m pytest test_reports_sync.py test_reports_sync_fix.py test_reports_sync_db.py -v`
Expected: PASS or identify unrelated pre-existing failures

- [ ] **Step 4: Inspect git diff for only intended files**

Run: `git diff -- mbetterclient/web_dashboard/routes.py mbetterclient/database/migrations.py test_reports_daily_alignment.py test_reports_stats_repair_migration.py`
Expected: Diff shows only report alignment, Excel correction, and migration repair changes

- [ ] **Step 5: Commit final verification pass**

```bash
git add mbetterclient/web_dashboard/routes.py mbetterclient/database/migrations.py test_reports_daily_alignment.py test_reports_stats_repair_migration.py
git commit -m "test: verify report alignment and repair changes"
```

## Self-Review

- Spec coverage:
  - Daily summary consistency: Tasks 1-3
  - Excel correction and verification: Tasks 2-3
  - Match reports payout fix: Task 3
  - Historical extraction stats repair: Tasks 4-5
  - Remote log upload explanation: design-only in spec, no implementation tasks by scope
- Placeholder scan:
  - Removed TODO/TBD placeholders
  - Each implementation step includes concrete code or command
- Type consistency:
  - Migration name consistently `Migration_069_RepairExtractionStatsFinancials`
  - Report repair logic consistently uses `BetDetailModel.win_amount` with winning results `('win', 'won')`
