# PRD: Reverse Scan — Country → Specific Airport(s)

**Status:** Ready for implementation
**Date:** 2026-03-01
**Target:** Web App (api_server.py + scan_processor.py + database + frontend)

---

## 1. Problem

All scans today are fixed-origin, variable-destination:
> *"From BDS — which German airports can I fly to directly?"*

The opposite question is equally common but unsupported:
> *"I want to fly to BRI or BDS — which German airport should I depart from?"*

This is the natural perspective of a traveller based in Germany looking for the cheapest access point to Puglia, not a traveller already in Puglia wondering where to go.

---

## 2. Goal

Let users create a scan where:
- **Origin** = all airports in a country (e.g. DE)
- **Destination** = one or more specific airports (e.g. BRI, BDS)

The results table for a reverse scan shows one row per **origin airport × destination** pair, so the user can directly compare which German airport is cheapest for each destination.

---

## 3. User Stories

- **As a user**, I want to search DE → BRI,BDS so I can see which German airport has the cheapest direct flight to Puglia.
- **As a user**, I want to schedule a reverse scan to run weekly.
- **As a user**, I want to mix both directions in my scan history.
- **As a user**, I want the destination country in forward scans to be a dropdown, not free text.

---

## 4. Scope

### In scope
- `scan_mode` field on scans and scheduled scans: `"forward"` (default) or `"reverse"`
- Reverse scan: origin = ISO country (dropdown), destinations = specific airport(s)
- Forward scan: destination country changed from text input to dropdown
- New `GET /api/v1/countries` endpoint (powers both dropdowns)
- `routes` table: add `origin_airport` column (nullable; populated for reverse scans)
- `flights` table: add `origin_airport` column (nullable; populated for reverse scans)
- ScanDetails routes table: show Origin column for reverse scans
- Scheduled scans: support `scan_mode`

### Out of scope
- Multi-country origin (e.g. DE + IT → BRI)
- Round-trip reverse scans

---

## 5. Data Model Changes

### 5.1 `scans` table

```sql
ALTER TABLE scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward'
    CHECK (scan_mode IN ('forward', 'reverse'));
```

Column semantics by mode:

| Column | Forward (existing) | Reverse (new) |
|--------|--------------------|---------------|
| `origin` | Single IATA (e.g. `BDS`) | ISO country code (e.g. `DE`) |
| `country` | ISO destination country OR comma-separated destination IATAs | Comma-separated destination IATAs (e.g. `BRI,BDS`) |
| `scan_mode` | `forward` | `reverse` |

All existing rows default to `forward`. No data migration needed.

### 5.2 `routes` table

```sql
ALTER TABLE routes ADD COLUMN origin_airport TEXT;
```

- **Forward scans**: `origin_airport` = NULL (origin is always the scan-level `origin` IATA, already known)
- **Reverse scans**: `origin_airport` = the specific origin IATA for this route (e.g. `BER`, `FRA`)

The unique constraint on routes changes from `(scan_id, destination)` to `(scan_id, COALESCE(origin_airport, ''), destination)`.

**Reverse scan routes example** for `DE → BRI, BDS`:

| origin_airport | destination | flight_count | min_price |
|----------------|-------------|--------------|-----------|
| BER | BRI | 14 | €15 |
| FMM | BRI | 8 | €17 |
| DUS | BRI | 6 | €22 |
| BER | BDS | 3 | €28 |
| MUC | BDS | 2 | €34 |

### 5.3 `flights` table

```sql
ALTER TABLE flights ADD COLUMN origin_airport TEXT;
```

- **Forward scans**: `origin_airport` = NULL (origin is the scan-level `origin` IATA)
- **Reverse scans**: `origin_airport` = the specific IATA the flight departs from

### 5.4 `scheduled_scans` table

```sql
ALTER TABLE scheduled_scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward'
    CHECK (scan_mode IN ('forward', 'reverse'));
```

Same column semantics as `scans`. When the scheduler fires a reverse scheduled scan, it creates a reverse scan child.

---

## 6. New API Endpoint

### `GET /api/v1/countries`

Returns the list of countries that have at least one airport in the database. Powers both the forward-mode destination country dropdown and the reverse-mode origin country dropdown.

**Response:**
```json
{
  "data": [
    { "code": "DE", "name": "Germany", "airport_count": 23 },
    { "code": "IT", "name": "Italy", "airport_count": 41 },
    ...
  ]
}
```

Sorted alphabetically by name. Derived from `data/airports_by_country.json` at startup (same source as airport resolution). Rate limit: 100/min.

Country names are resolved from a static ISO → name mapping in `api_server.py` (same `COUNTRY_NAME_TO_ISO` dict already in `airports.py`, inverted).

---

## 7. Backend Changes

### 7.1 `ScanRequest` model

Add `scan_mode: Literal['forward', 'reverse'] = 'forward'`.

Root validator enforces:

**Forward mode** (unchanged):
- `origin`: required, 3-char IATA
- `country` OR `destinations`: required

**Reverse mode**:
- `origin`: required, 2-char ISO country code
- `destinations`: required, 1–10 IATAs
- `country` must be absent or null

### 7.2 `scan_processor.py` — `process_scan()`

**Forward** (unchanged):
```
fixed origin IATA × [airports resolved from destination country / destination IATAs]
```

**Reverse** (new):
```
[airports resolved from origin country] × fixed destination IATA(s)
```

Steps for reverse:
1. Read `scan.origin` as ISO country code → resolve airport list via `airports.py`
2. Read `scan.country` as comma-separated destination IATAs
3. Build pairs: `[(orig, dest) for orig in origin_airports for dest in destination_iatas]`
4. For each pair: call `search_multiple_routes(orig, dest)` as usual
5. Save route with `origin_airport=orig`, `destination=dest`
6. Save each flight with `origin_airport=orig`

`total_routes` = `len(origin_airports) × len(destination_iatas)`.

### 7.3 `GET /scans/{id}/routes` response

Add `origin_airport: str | null` to the `Route` response model. Frontend uses this to show/hide the Origin column.

### 7.4 `GET /scans/{id}/flights` response

Add `origin_airport: str | null` to the `Flight` response model.

### 7.5 Scheduled scans

`ScheduleRequest` and `Schedule` models gain `scan_mode`. The scheduler's `_fire_due_scans()` function passes `scan_mode` when inserting child scans into the `scans` table.

---

## 8. Frontend Changes

### 8.1 New `countriesApi` client (`api.ts`)

```typescript
export interface Country { code: string; name: string; airport_count: number; }

export const countriesApi = {
  list: () => api.get<{ data: Country[] }>('/countries'),
};
```

### 8.2 New `CountrySelect` component

A standard `<select>` dropdown, populated from `GET /api/v1/countries` on mount. Shows `"Germany (DE)"` style labels, emits the ISO code. Reused in two places:
- Forward scan form: destination country field (replaces current text input)
- Reverse scan form: origin country field

### 8.3 Scan creation form (`/scans` page)

Add a **Direction toggle** at the top of the form (same segmented button style as "By Country / By Airports"):

```
[ → Forward ]  [ ← Reverse ]
```

**Forward mode** (current layout, destination country now uses `CountrySelect`):
```
Origin airport:       [AirportSearch]
Destination:          [By Country ▼] [By Airports]
  → Country:          [CountrySelect dropdown]
  → Airports:         [comma-sep IATA input]
```

**Reverse mode** (new):
```
Origin country:       [CountrySelect dropdown]
Destination airports: [AirportSearch / comma-sep IATA input]
```

### 8.4 ScanDetails — routes table

For reverse scans (`scan_mode === 'reverse'`), prepend an **Origin** column to the routes table:

| Origin | Destination | Flights | Airlines | Min | Avg | Max |
|--------|-------------|---------|----------|-----|-----|-----|
| BER Berlin | BRI Bari | 14 | Ryanair | €15 | €22 | €45 |
| FMM Memmingen | BRI Bari | 8 | Ryanair | €17 | €25 | €50 |

The existing click-to-expand flights sub-table still works — shows individual flight dates/times/prices for that specific origin→destination pair.

For forward scans: routes table unchanged (no Origin column).

### 8.5 ScanDetails — header

```
Forward: BDS → DE
Reverse: DE → BRI, BDS
```

### 8.6 `CreateScanRequest` type update (`api.ts`)

```typescript
export interface CreateScanRequest {
  scan_mode?: 'forward' | 'reverse';
  origin: string;           // IATA (forward) or ISO country code (reverse)
  country?: string;         // forward only
  destinations?: string[];  // forward (by airports) or reverse
  window_months?: number;
  seat_class?: 'economy' | 'premium' | 'business' | 'first';
  adults?: number;
}
```

---

## 9. API Contract

### `POST /api/v1/scans`

**Forward — by country (existing, unchanged):**
```json
{ "origin": "BDS", "country": "DE", "window_months": 6, "seat_class": "economy", "adults": 1 }
```

**Forward — by airports (existing, unchanged):**
```json
{ "origin": "BDS", "destinations": ["FRA", "MUC"], "window_months": 6, "seat_class": "economy", "adults": 1 }
```

**Reverse (new):**
```json
{ "scan_mode": "reverse", "origin": "DE", "destinations": ["BRI", "BDS"], "window_months": 6, "seat_class": "economy", "adults": 1 }
```

---

## 10. Migration (`init_db.py`)

Four `ALTER TABLE … ADD COLUMN` statements, each guarded by `try/except OperationalError`:

```python
migrations = [
    "ALTER TABLE scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward' CHECK (scan_mode IN ('forward', 'reverse'))",
    "ALTER TABLE routes ADD COLUMN origin_airport TEXT",
    "ALTER TABLE flights ADD COLUMN origin_airport TEXT",
    "ALTER TABLE scheduled_scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward' CHECK (scan_mode IN ('forward', 'reverse'))",
]
for sql in migrations:
    try:
        conn.execute(sql)
    except sqlite3.OperationalError:
        pass  # column already exists
conn.commit()
```

---

## 11. Acceptance Criteria

1. `GET /api/v1/countries` returns a sorted list of countries with airport counts.
2. Forward scan destination country field is a dropdown populated from that endpoint.
3. A reverse scan `DE → BRI, BDS` can be created via the form and the API.
4. The processor iterates all German airports × [BRI, BDS], storing `origin_airport` on each route and flight row.
5. ScanDetails for a reverse scan shows the Origin column in the routes table and `DE → BRI, BDS` in the header.
6. Scheduled scans accept `scan_mode`; the scheduler passes it through to child scans.
7. All existing forward scans continue to work — no regressions.
8. All four DB columns default correctly after migration with no data loss.