Coverage for app/flights/airframe_import.py: 100%

1"""Bulk import of a historical airframe logbook (CSV/Excel) for one aircraft. 

2 

3Reuses the Phase 28 pilot-logbook machinery wholesale — file parsing, header 

4auto-detection, subtotal-row skipping, value parsers — mapped onto 

5Flight fields. Counter continuity is validated with per-row warnings 

6(historical paper logs often carry small corrections), free-text pilot names 

7are written to Flight.pic_name (pic_user_id left NULL), and an optional "opening 

8counters" baseline supports importing from a cutover date forward. 

9""" 

10 

11from __future__ import annotations 

12 

13import json 

14from dataclasses import dataclass, field 

15from datetime import date, timedelta 

16from typing import Any 

17 

18from flask_babel import gettext as _ # pyright: ignore[reportMissingImports] 

19from pilots.logbook_import import ( # pyright: ignore[reportMissingImports] 

20 DedupIndex, 

21 ParsedFile, 

22 parse_date_value, 

23 parse_duration_value, 

24 parse_int_value, 

25 parse_time_value, 

26) 

27 

28AIRFRAME_TARGET_FIELDS: list[str] = [ 

29 "date", 

30 "crew_name", 

31 "departure_icao", 

32 "arrival_icao", 

33 "takeoff_time", 

34 "landing_time", 

35 "flight_time", 

36 "flight_counter_start", 

37 "flight_counter_end", 

38 "engine_counter_start", 

39 "engine_counter_end", 

40 "landing_count", 

41 "passenger_count", 

42 "nature_of_flight", 

43 "notes", 

44] 

45 

46# Normalised source column name → airframe target field. 

47_AIRFRAME_ALIASES: dict[str, str] = { 

48 "date": "date", 

49 "date dd/mm/yy": "date", 

50 "pilot": "crew_name", 

51 "pilot in command": "crew_name", 

52 "pic": "crew_name", 

53 "crew": "crew_name", 

54 "name": "crew_name", 

55 "from": "departure_icao", 

56 "departure": "departure_icao", 

57 "dep": "departure_icao", 

58 "to": "arrival_icao", 

59 "arrival": "arrival_icao", 

60 "arr": "arrival_icao", 

61 # Airframe-log block/flight times feed takeoff_time/landing_time 

62 # (flight_time_counter_*/flight_time — airframe-log-facing), never 

63 # departure_time/arrival_time (engine_time_counter_*/engine_time — 

64 # pilot-log-facing; see models.py's Flight docstring). The two pairs 

65 # are never defaulted from each other. 

66 "time": "takeoff_time", # first TIME → takeoff 

67 "time_2": "landing_time", # second TIME → landing 

68 "departure time": "takeoff_time", 

69 "off block": "takeoff_time", 

70 "off-block": "takeoff_time", 

71 "arrival time": "landing_time", 

72 "on block": "landing_time", 

73 "on-block": "landing_time", 

74 "flight time": "flight_time", 

75 "block time": "flight_time", 

76 "duration": "flight_time", 

77 "total time": "flight_time", 

78 "landings": "landing_count", 

79 "ldg": "landing_count", 

80 "ldgs": "landing_count", 

81 "landing count": "landing_count", 

82 "pax": "passenger_count", 

83 "passengers": "passenger_count", 

84 "nature": "nature_of_flight", 

85 "nature of flight": "nature_of_flight", 

86 "remarks": "notes", 

87 "remarks and endorsements": "notes", 

88 "notes": "notes", 

89 "hobbs start": "engine_counter_start", 

90 "hobbs end": "engine_counter_end", 

91 "hobbs": "engine_counter_end", 

92 "engine start": "engine_counter_start", 

93 "engine end": "engine_counter_end", 

94 "engine counter start": "engine_counter_start", 

95 "engine counter end": "engine_counter_end", 

96 "tach start": "engine_counter_start", 

97 "tach end": "engine_counter_end", 

98 "flight counter start": "flight_counter_start", 

99 "flight counter end": "flight_counter_end", 

100 "flight start": "flight_counter_start", 

101 "flight end": "flight_counter_end", 

102 "counter start": "flight_counter_start", 

103 "counter end": "flight_counter_end", 

104} 

105 

106# Target field → parser (None = keep trimmed string) 

107_AIRFRAME_PARSERS: dict[str, Any] = { 

108 "takeoff_time": parse_time_value, 

109 "landing_time": parse_time_value, 

110 "flight_time": parse_duration_value, 

111 "flight_counter_start": parse_duration_value, 

112 "flight_counter_end": parse_duration_value, 

113 "engine_counter_start": parse_duration_value, 

114 "engine_counter_end": parse_duration_value, 

115 "landing_count": parse_int_value, 

116 "passenger_count": parse_int_value, 

117} 

118 

119# How far apart a row's start counter may be from the previous row's end 

120# counter before a continuity warning is raised. 

121_COUNTER_TOLERANCE = 0.05 

122 

123 

124@dataclass 

125class AirframeImportResult: 

126 imported: int = 0 

127 subtotals: int = 0 

128 skipped: list[tuple[int, str]] = field(default_factory=list) 

129 # (row_num, reason) — rows that matched a flight already in this aircraft's log 

130 duplicates: list[tuple[int, str]] = field(default_factory=list) 

131 parse_warnings: list[tuple[int, str, str, str]] = field(default_factory=list) 

132 # (row_num, counter_label, previous_end, this_start) 

133 continuity_warnings: list[tuple[int, str, float, float]] = field( 

134 default_factory=list 

135 ) 

136 has_opening_counters: bool = False 

137 

138 

139def _num(v: Any) -> float | None: 

140 # Numeric columns come back as decimal.Decimal; freshly parsed values are 

141 # plain float — normalise both sides before building/comparing keys (see 

142 # the matching note in pilots/logbook_import.py for why this matters). 

143 return None if v is None else float(v) 

144 

145 

146def _dup_key(fields: dict[str, Any]) -> tuple[Any, ...]: 

147 """Coarse exact-duplicate key: date + route + duration + landings. 

148 Re-importing the same airframe logbook file (by mistake, or deliberately 

149 after appending new rows) must only add genuinely new flights, not 

150 double up everything already imported. Necessary but not sufficient — 

151 see _dup_detail and DedupIndex.""" 

152 return ( 

153 fields["date"], 

154 fields.get("departure_icao") or "ZZZZ", 

155 fields.get("arrival_icao") or "ZZZZ", 

156 _num(fields.get("flight_time")), 

157 fields.get("landing_count"), 

158 ) 

159 

160 

161def _dup_detail( 

162 takeoff: Any, landing: Any, flight_end: Any, engine_end: Any 

163) -> tuple[Any, ...]: 

164 """What tells apart two flights sharing a _dup_key — e.g. a day of 

165 identical 0.5 h training circuits: their times and counter readings. 

166 Only compared where both sides record them.""" 

167 return (takeoff, landing, _num(flight_end), _num(engine_end)) 

168 

169 

170def _fields_dup_detail(fields: dict[str, Any]) -> tuple[Any, ...]: 

171 return _dup_detail( 

172 fields.get("takeoff_time"), 

173 fields.get("landing_time"), 

174 fields.get("flight_counter_end"), 

175 fields.get("engine_counter_end"), 

176 ) 

177 

178 

179def _fetch_existing_dedup_index(aircraft_id: int) -> DedupIndex: 

180 from models import Flight, db # pyright: ignore[reportMissingImports] 

181 

182 index = DedupIndex() 

183 for row in db.session.query( 

184 Flight.date, 

185 Flight.departure_icao, 

186 Flight.arrival_icao, 

187 Flight.flight_time, 

188 Flight.landing_count, 

189 Flight.takeoff_time, 

190 Flight.landing_time, 

191 Flight.flight_time_counter_end, 

192 Flight.engine_time_counter_end, 

193 ).filter_by(aircraft_id=aircraft_id): 

194 index.add( 

195 (row[0], row[1], row[2], _num(row[3]), row[4]), 

196 _dup_detail(row[5], row[6], row[7], row[8]), 

197 ) 

198 return index 

199 

200 

201def propose_airframe_mapping( 

202 parsed: ParsedFile, saved: list[Any] 

203) -> tuple[dict[str, str], str]: 

204 """Return (mapping, match_type) — exact fingerprint reuse, else aliases.""" 

205 for m in saved: 

206 if m.source_fingerprint == parsed.fingerprint: 

207 stored = json.loads(m.column_mapping) 

208 mapping = { 

209 col: stored.get(col, "ignore") 

210 if stored.get(col) in AIRFRAME_TARGET_FIELDS 

211 else "ignore" 

212 for col in parsed.norm_cols 

213 } 

214 return mapping, "exact" 

215 mapping = {} 

216 taken: set[str] = set() 

217 for idx, col in enumerate(parsed.norm_cols): 

218 target = _AIRFRAME_ALIASES.get(col, "ignore") 

219 if target in _PLACE_TO_TIME and _column_holds_times(parsed, idx): 

220 # "Dep"/"Arr" are as often the departure/arrival *times* as the 

221 # places — let the data decide. 

222 target = _PLACE_TO_TIME[target] 

223 if target in taken: 

224 # Never propose two columns for one field: the second would 

225 # silently overwrite the first on every row. 

226 target = "ignore" 

227 if target != "ignore": 

228 taken.add(target) 

229 mapping[col] = target 

230 return mapping, "alias" 

231 

232 

233_PLACE_TO_TIME = {"departure_icao": "takeoff_time", "arrival_icao": "landing_time"} 

234 

235 

236def _column_holds_times(parsed: ParsedFile, idx: int) -> bool: 

237 """True when most sampled non-blank cells of column *idx* parse as a 

238 time of day (an airfield code never does).""" 

239 sample = [ 

240 row[idx] 

241 for row in parsed.data_rows[:_HINT_SAMPLE_ROWS] 

242 if idx < len(row) and not _is_blank_cell(row[idx]) 

243 ] 

244 times = sum(1 for v in sample if parse_time_value(v) is not None) 

245 return bool(sample) and times * 2 > len(sample) 

246 

247 

248_HINT_SAMPLE_ROWS = 25 

249 

250_TYPE_NAMES: dict[str, str] = { 

251 "takeoff_time": "time", 

252 "landing_time": "time", 

253 "flight_time": "duration", 

254 "flight_counter_start": "counter value", 

255 "flight_counter_end": "counter value", 

256 "engine_counter_start": "counter value", 

257 "engine_counter_end": "counter value", 

258 "landing_count": "whole number", 

259 "passenger_count": "whole number", 

260} 

261 

262 

263def airframe_type_hints(parsed: ParsedFile, mapping: dict[str, str]) -> dict[str, str]: 

264 """{col: hint} where sample data fails to parse as the proposed type.""" 

265 col_index = {col: i for i, col in enumerate(parsed.norm_cols)} 

266 hints: dict[str, str] = {} 

267 for col, target in mapping.items(): 

268 parser = _AIRFRAME_PARSERS.get(target) 

269 idx = col_index.get(col) 

270 if parser is None or idx is None: 

271 continue 

272 sample = [ 

273 row[idx] 

274 for row in parsed.data_rows[:_HINT_SAMPLE_ROWS] 

275 if idx < len(row) and not _is_blank_cell(row[idx]) 

276 ] 

277 if not sample: 

278 continue 

279 failed = [v for v in sample if parser(v) is None] 

280 if failed: 

281 example = str(failed[0])[:30] 

282 hints[col] = ( 

283 f"Sample data doesn't look like a {_TYPE_NAMES[target]} " 

284 f"(e.g. {example!r})" 

285 ) 

286 return hints 

287 

288 

289def _clean_icao(raw: Any) -> str: 

290 """Normalise a place cell to the 4-char ICAO field; ZZZZ when unusable.""" 

291 val = str(raw).strip().upper() if raw is not None else "" 

292 if not val: 

293 return "ZZZZ" 

294 return val[:4] 

295 

296 

297def _is_subtotal(row: list[Any], date_idx: int | None) -> bool: 

298 from pilots.logbook_import import ( 

299 _is_subtotal_row, # pyright: ignore[reportMissingImports] 

300 ) 

301 

302 return _is_subtotal_row(row, date_idx) 

303 

304 

305# Paper airframe logbooks routinely leave a counter blank — e.g. flying a 

306# leg and back on one continuous engine run only has one landing counter 

307# reading in between, so the arrival counter of the first leg and the 

308# departure counter of the return leg were never written down. When such a 

309# gap is transcribed, it's marked with this literal placeholder rather than 

310# left as a truly blank cell (to distinguish "known blank" from "OCR missed 

311# it") — treated identically to a blank cell everywhere a cell is read: no 

312# value, no parse warning. 

313_BLANK_CELL_MARKER = "[empty]" 

314 

315 

316def _is_blank_cell(raw: Any) -> bool: 

317 if raw is None: 

318 return True 

319 s = str(raw).strip() 

320 return not s or s.casefold() == _BLANK_CELL_MARKER 

321 

322 

323def _row_get(row: list[Any], col_index: dict[str, int], col: str) -> Any: 

324 i = col_index.get(col) 

325 val = row[i] if i is not None and i < len(row) else None 

326 return None if _is_blank_cell(val) else val 

327 

328 

329def _parse_row_date( 

330 row: list[Any], mapping: dict[str, str], col_index: dict[str, int] 

331) -> date | None: 

332 for col, target in mapping.items(): 

333 if target == "date": 

334 return parse_date_value(_row_get(row, col_index, col)) 

335 return None 

336 

337 

338def _build_airframe_fields( 

339 row: list[Any], 

340 mapping: dict[str, str], 

341 col_index: dict[str, int], 

342 date_val: date, 

343) -> tuple[dict[str, Any], str | None, list[tuple[str, str, str]]]: 

344 """Build Flight field values (+ crew name) for one data row. 

345 

346 Returns (fields, crew_name, parse_warnings), where parse_warnings is a 

347 list of (col, target, raw_repr) for non-empty cells that couldn't be 

348 parsed as their target field's type. Shared by the normal import pass 

349 and the near-match conflict finder below, so the two can never compute a 

350 row's fields differently. 

351 """ 

352 fields: dict[str, Any] = {"date": date_val} 

353 crew_name: str | None = None 

354 parse_warnings: list[tuple[str, str, str]] = [] 

355 for col, target in mapping.items(): 

356 if target in ("ignore", "date"): 

357 continue 

358 raw = _row_get(row, col_index, col) 

359 if target == "crew_name": 

360 crew_name = str(raw).strip() if raw is not None else None 

361 crew_name = crew_name or None 

362 continue 

363 if target in ("departure_icao", "arrival_icao"): 

364 fields[target] = _clean_icao(raw) 

365 continue 

366 parser = _AIRFRAME_PARSERS.get(target) 

367 if parser is not None: 

368 parsed_val = parser(raw) 

369 if parsed_val is None and raw is not None and str(raw).strip(): 

370 parse_warnings.append((col, target, repr(str(raw)[:40]))) 

371 fields[target] = parsed_val 

372 else: # nature_of_flight, notes — free text 

373 val = str(raw).strip() if raw is not None else None 

374 fields[target] = val or None 

375 # The flight counters are the authoritative airborne figure, as on the 

376 # flight form (flights/form_parsing.py): a logged duration is often the 

377 # block time (departure to arrival) instead — on paper logs typically 

378 # by exactly the aircraft's block-to-airborne offset. 

379 ctr_start = fields.get("flight_counter_start") 

380 ctr_end = fields.get("flight_counter_end") 

381 if ctr_start is not None and ctr_end is not None and ctr_end >= ctr_start: 

382 fields["flight_time"] = round(ctr_end - ctr_start, 1) 

383 # Likewise the engine counters give the engine time — the log has no 

384 # engine start/stop times to take it from. 

385 eng_start = fields.get("engine_counter_start") 

386 eng_end = fields.get("engine_counter_end") 

387 if eng_start is not None and eng_end is not None and eng_end >= eng_start: 

388 fields["engine_time"] = round(eng_end - eng_start, 1) 

389 return fields, crew_name, parse_warnings 

390 

391 

392def _fields_to_flight_entry_kwargs(fields: dict[str, Any]) -> dict[str, Any]: 

393 """Map parsed row *fields* (mapping-target-field keys) to Flight 

394 constructor kwargs (model-column keys) — the two differ for the counter 

395 fields (flight_counter_end → flight_time_counter_end etc). Shared by the 

396 normal insert path and the review step's overwrite/new-entry handling. 

397 

398 Only ever touches the airframe-facing takeoff_time/landing_time pair — 

399 never departure_time/arrival_time, which is pilot-log-facing (fed by 

400 pilots/logbook_import.py instead) and must not be clobbered when this 

401 row is merged onto an existing placeholder created from that side.""" 

402 return { 

403 "date": fields["date"], 

404 "departure_icao": fields.get("departure_icao") or "ZZZZ", 

405 "arrival_icao": fields.get("arrival_icao") or "ZZZZ", 

406 "takeoff_time": fields.get("takeoff_time"), 

407 "landing_time": fields.get("landing_time"), 

408 "flight_time": fields.get("flight_time"), 

409 "flight_time_counter_start": fields.get("flight_counter_start"), 

410 "flight_time_counter_end": fields.get("flight_counter_end"), 

411 "engine_time_counter_start": fields.get("engine_counter_start"), 

412 "engine_time_counter_end": fields.get("engine_counter_end"), 

413 "engine_time": fields.get("engine_time"), 

414 "landing_count": fields.get("landing_count"), 

415 "passenger_count": fields.get("passenger_count"), 

416 "nature_of_flight": fields.get("nature_of_flight"), 

417 "notes": fields.get("notes"), 

418 } 

419 

420 

421def execute_airframe_import( 

422 parsed: ParsedFile, 

423 mapping: dict[str, str], 

424 aircraft: Any, 

425 batch_id: int, 

426 opening_counters: dict[str, float | None] | None = None, 

427 skip_row_nums: set[int] | None = None, 

428) -> AirframeImportResult: 

429 """Create Flight rows from *parsed* using *mapping*. 

430 

431 Rows are added to db.session but NOT committed — the caller commits after 

432 updating the batch record. Counter continuity is checked in date order 

433 against the previous imported row (and the opening counters, if given), 

434 producing warnings rather than errors. *skip_row_nums* lets the caller 

435 carve out rows it's handling separately — near-match conflicts routed to 

436 the interactive review step in app/flights/routes.py — so they're 

437 excluded entirely from this pass. 

438 """ 

439 from models import Flight, db # pyright: ignore[reportMissingImports] 

440 

441 result = AirframeImportResult() 

442 col_index = {col: i for i, col in enumerate(parsed.norm_cols)} 

443 date_idx = next( 

444 (col_index[c] for c, t in mapping.items() if t == "date" and c in col_index), 

445 None, 

446 ) 

447 skip_row_nums = skip_row_nums or set() 

448 

449 existing = _fetch_existing_dedup_index(aircraft.id) 

450 rows: list[tuple[int, dict[str, Any], str | None]] = [] 

451 for row_num, row in enumerate(parsed.data_rows, start=1): 

452 if row_num in skip_row_nums: 

453 continue 

454 if _is_subtotal(row, date_idx): 

455 result.subtotals += 1 

456 continue 

457 

458 date_val = _parse_row_date(row, mapping, col_index) 

459 if date_val is None: 

460 raw_date = ( 

461 row[date_idx] if date_idx is not None and date_idx < len(row) else None 

462 ) 

463 result.skipped.append((row_num, f"unparseable date: {raw_date!r}")) 

464 continue 

465 

466 fields, crew_name, parse_warnings = _build_airframe_fields( 

467 row, mapping, col_index, date_val 

468 ) 

469 for col, target, raw_repr in parse_warnings: 

470 result.parse_warnings.append((row_num, col, target, raw_repr)) 

471 

472 dup_key = _dup_key(fields) 

473 dup_detail = _fields_dup_detail(fields) 

474 if existing.contains(dup_key, dup_detail): 

475 result.duplicates.append( 

476 ( 

477 row_num, 

478 _( 

479 "matches a flight already in this aircraft's log " 

480 "(same date, route, duration and landings)" 

481 ), 

482 ) 

483 ) 

484 continue 

485 existing.add(dup_key, dup_detail) 

486 

487 rows.append((row_num, fields, crew_name)) 

488 

489 # Continuity checks run in chronological order (file order as tiebreaker). 

490 rows.sort(key=lambda item: (item[1]["date"], item[0])) 

491 prev_end: dict[str, float | None] = { 

492 "flight": (opening_counters or {}).get("flight"), 

493 "engine": (opening_counters or {}).get("engine"), 

494 } 

495 for row_num, fields, _crew in rows: 

496 for kind, start_key, end_key in ( 

497 ("flight", "flight_counter_start", "flight_counter_end"), 

498 ("engine", "engine_counter_start", "engine_counter_end"), 

499 ): 

500 start = fields.get(start_key) 

501 prev = prev_end[kind] 

502 if ( 

503 start is not None 

504 and prev is not None 

505 and abs(start - prev) > _COUNTER_TOLERANCE 

506 ): 

507 result.continuity_warnings.append((row_num, kind, prev, start)) 

508 if fields.get(end_key) is not None: 

509 prev_end[kind] = fields[end_key] 

510 

511 earliest = rows[0][1]["date"] if rows else date.today() 

512 if opening_counters and any(v is not None for v in opening_counters.values()): 

513 # Baseline entry seeding the counters: zero-length deltas so hours 

514 # statistics are unaffected, dated before the first imported flight. 

515 baseline = Flight( 

516 aircraft_id=aircraft.id, 

517 airframe_import_batch_id=batch_id, 

518 source="import", 

519 date=earliest - timedelta(days=1), 

520 departure_icao="ZZZZ", 

521 arrival_icao="ZZZZ", 

522 flight_time_counter_start=opening_counters.get("flight"), 

523 flight_time_counter_end=opening_counters.get("flight"), 

524 engine_time_counter_start=opening_counters.get("engine"), 

525 engine_time_counter_end=opening_counters.get("engine"), 

526 notes="Opening counters (imported)", 

527 ) 

528 db.session.add(baseline) 

529 result.has_opening_counters = True 

530 

531 for _row_num, fields, crew_name in rows: 

532 fe = Flight( 

533 aircraft_id=aircraft.id, 

534 airframe_import_batch_id=batch_id, 

535 source="import", 

536 pic_name=crew_name, 

537 **_fields_to_flight_entry_kwargs(fields), 

538 ) 

539 db.session.add(fe) 

540 result.imported += 1 

541 

542 return result 

543 

544 

545# ── Near-match conflict detection (possible corrections) ─────────────────────── 

546 

547_CANDIDATE_MIN_SCORE = 3 

548_CANDIDATE_DURATION_TOLERANCE = 1.0 

549_CANDIDATE_COUNTER_TOLERANCE = 0.3 

550 

551 

552@dataclass 

553class AirframeConflictRow: 

554 """A parsed row that isn't an exact duplicate but scores highly enough 

555 against one or more existing Flight rows to plausibly be an edited 

556 version of one of them — needs a human decision, not a guess.""" 

557 

558 row_num: int 

559 fields: dict[str, Any] 

560 crew_name: str | None 

561 candidates: list[tuple[float, int]] # (score, existing_flight_id), best first 

562 

563 

564def _score_airframe_non_time_signals(fields: dict[str, Any], existing: Any) -> float: 

565 """The 5 of 7 _score_airframe_candidate signals that aren't a time of 

566 day: departure, arrival, duration, landings, flight counter reading. 

567 Factored out so aircraft/gps_import.py's fuzzy match can reuse them with 

568 its own time-matching logic (a GPS track's single recorded start/end 

569 instant needs a wider comparison than a same-source re-import does — 

570 see _score_airframe_candidate below) instead of duplicating this. 

571 """ 

572 score: float = 0.0 

573 

574 dep_new = (fields.get("departure_icao") or "ZZZZ").strip().upper() 

575 dep_old = (existing.departure_icao or "ZZZZ").strip().upper() 

576 if dep_new != "ZZZZ" and dep_old != "ZZZZ" and dep_new == dep_old: 

577 score += 1 

578 

579 arr_new = (fields.get("arrival_icao") or "ZZZZ").strip().upper() 

580 arr_old = (existing.arrival_icao or "ZZZZ").strip().upper() 

581 if arr_new != "ZZZZ" and arr_old != "ZZZZ" and arr_new == arr_old: 

582 score += 1 

583 

584 dur_new = _num(fields.get("flight_time")) 

585 dur_old = _num(existing.flight_time) 

586 if ( 

587 dur_new is not None 

588 and dur_old is not None 

589 and abs(dur_new - dur_old) <= _CANDIDATE_DURATION_TOLERANCE 

590 ): 

591 score += 1 

592 

593 land_new = fields.get("landing_count") 

594 land_old = existing.landing_count 

595 if land_new is not None and land_old is not None and land_new == land_old: 

596 score += 1 

597 

598 ctr_new = _num(fields.get("flight_counter_end")) 

599 ctr_old = _num(existing.flight_time_counter_end) 

600 if ( 

601 ctr_new is not None 

602 and ctr_old is not None 

603 and abs(ctr_new - ctr_old) <= _CANDIDATE_COUNTER_TOLERANCE 

604 ): 

605 score += 1 

606 

607 return score 

608 

609 

610def _score_airframe_candidate( 

611 fields: dict[str, Any], existing: Any, offset_hours: float 

612) -> float: 

613 """Score how likely *existing* (a Flight row) is the same real-world 

614 flight as *fields* (a freshly parsed row), across 7 points: departure, 

615 arrival, takeoff time, landing time, duration, landings, flight 

616 counter reading. A point only counts toward the score if both sides 

617 have meaningful data for it — "ZZZZ" (unmapped ICAO) and missing values 

618 are neutral, never a mismatch. The two time points can be fractional — 

619 see services.time_band_matching. 

620 """ 

621 from services.time_band_matching import ( # pyright: ignore[reportMissingImports] 

622 offset_ring_step_minutes, 

623 taxi_in_score, 

624 taxi_out_score, 

625 time_band_score, 

626 ) 

627 

628 score = _score_airframe_non_time_signals(fields, existing) 

629 

630 # Both same-pair (existing.takeoff_time/landing_time set — likely a 

631 # previous airframe import) and cross-pair (existing only has 

632 # departure_time/arrival_time — a pilot-import placeholder) use the same 

633 # ring width: 1/3 of this aircraft's flight_counter_offset. Pilots log 

634 # to a fixed precision (0.1h/6min at the 0.3h default) — a flat few- 

635 # minutes tolerance would put an ordinary rounding difference in the 

636 # wrong ring, so the same offset-derived step is used for both instead 

637 # of a separate hardcoded constant. Cross-pair instead checks the time 

638 # falls within the taxi window the offset allows — see 

639 # services.time_band_matching.taxi_out_score. 

640 offset_step = offset_ring_step_minutes(offset_hours) 

641 

642 takeoff_new = fields.get("takeoff_time") 

643 if existing.takeoff_time is not None: 

644 score += time_band_score(takeoff_new, (existing.takeoff_time,), offset_step) 

645 else: 

646 score += taxi_out_score(existing.departure_time, takeoff_new, offset_hours) 

647 

648 landing_new = fields.get("landing_time") 

649 if existing.landing_time is not None: 

650 score += time_band_score(landing_new, (existing.landing_time,), offset_step) 

651 else: 

652 score += taxi_in_score(landing_new, existing.arrival_time, offset_hours) 

653 

654 return score 

655 

656 

657def find_conflicting_airframe_rows( 

658 parsed: ParsedFile, 

659 mapping: dict[str, str], 

660 aircraft_id: int, 

661 exclude_row_nums: set[int] | None = None, 

662) -> list[AirframeConflictRow]: 

663 """Find rows that aren't an exact duplicate but plausibly match an 

664 existing Flight row closely enough (score >= _CANDIDATE_MIN_SCORE) to 

665 need a human decision: keep the existing entry, overwrite it with the 

666 new data, or import as a genuinely separate new flight. 

667 

668 Skips subtotal rows, rows with an unparseable date, and exact duplicates 

669 (same as execute_airframe_import's own dedup), plus any row_num in 

670 *exclude_row_nums* — typically rows already resolved in an earlier pass 

671 of this same review. 

672 """ 

673 from models import Aircraft, Flight, db # pyright: ignore[reportMissingImports] 

674 

675 exclude_row_nums = exclude_row_nums or set() 

676 col_index = {col: i for i, col in enumerate(parsed.norm_cols)} 

677 date_idx = next( 

678 (col_index[c] for c, t in mapping.items() if t == "date" and c in col_index), 

679 None, 

680 ) 

681 aircraft = db.session.get(Aircraft, aircraft_id) 

682 offset_hours = float(aircraft.flight_counter_offset) if aircraft else 0.0 

683 existing = _fetch_existing_dedup_index(aircraft_id) 

684 conflicts: list[AirframeConflictRow] = [] 

685 

686 for row_num, row in enumerate(parsed.data_rows, start=1): 

687 if row_num in exclude_row_nums: 

688 continue 

689 if _is_subtotal(row, date_idx): 

690 continue 

691 date_val = _parse_row_date(row, mapping, col_index) 

692 if date_val is None: 

693 continue 

694 

695 fields, crew_name, _parse_warnings = _build_airframe_fields( 

696 row, mapping, col_index, date_val 

697 ) 

698 if existing.contains(_dup_key(fields), _fields_dup_detail(fields)): 

699 continue # exact duplicate — execute_airframe_import's own dedup handles this 

700 

701 same_day = Flight.query.filter_by(aircraft_id=aircraft_id, date=date_val).all() 

702 scored = [ 

703 (score, existing.id) 

704 for existing in same_day 

705 if (score := _score_airframe_candidate(fields, existing, offset_hours)) 

706 >= _CANDIDATE_MIN_SCORE 

707 ] 

708 if scored: 

709 scored.sort(key=lambda t: -t[0]) 

710 conflicts.append( 

711 AirframeConflictRow( 

712 row_num=row_num, 

713 fields=fields, 

714 crew_name=crew_name, 

715 candidates=scored, 

716 ) 

717 ) 

718 

719 return conflicts 

720 

721 

722# ── Merging a row into an existing flight ──────────────────────────────────── 

723 

724# Fields a merge never takes from the parsed row: the date and route are 

725# what matched it to the existing flight in the first place. 

726_MERGE_SKIP_FIELDS = frozenset({"date", "departure_icao", "arrival_icao"}) 

727 

728 

729def merge_airframe_into_existing( 

730 existing: Any, fields: dict[str, Any], crew_name: str | None, *, fill_only: bool 

731) -> None: 

732 """Apply a parsed airframe-log row onto *existing* (a Flight), as the 

733 same real-world flight. Caller must commit. 

734 

735 *fill_only* (an automatic merge with a flight logged from the pilot 

736 side) only fills fields that are still empty. Otherwise (the review 

737 step's "overwrite") the row's values replace the existing ones. Either 

738 way an empty or unmapped cell never clears data already on the flight 

739 — e.g. the engine counters, or the pilot's departure/arrival times and 

740 remarks, which an airframe log row typically doesn't carry. 

741 """ 

742 for field_name, value in _fields_to_flight_entry_kwargs(fields).items(): 

743 if value is None or field_name in _MERGE_SKIP_FIELDS: 

744 continue 

745 current = getattr(existing, field_name) 

746 if field_name == "notes" and current and current != value: 

747 if fill_only and value not in current: 

748 existing.notes = f"{current}\n{value}" 

749 elif not fill_only: 

750 existing.notes = value 

751 continue 

752 if not fill_only or current is None: 

753 setattr(existing, field_name, value) 

754 if crew_name and not existing.pic_name: 

755 existing.pic_name = crew_name 

756 

757 

758def _is_pilot_only(existing: Any) -> bool: 

759 """A flight logged only from the pilot side (e.g. promoted from a 

760 personal logbook import): nothing airframe-facing recorded yet.""" 

761 return ( 

762 existing.takeoff_time is None 

763 and existing.landing_time is None 

764 and existing.flight_time is None 

765 and existing.flight_time_counter_start is None 

766 and existing.flight_time_counter_end is None 

767 ) 

768 

769 

770# Combined score of the two cross-pair time checks (takeoff vs block-off, 

771# landing vs block-on, each 0–1) needed to merge automatically: both inside 

772# their offset band, or one inside and the other within the first ring. 

773_AUTO_MERGE_MIN_TIME_SCORE = 1.75 

774 

775 

776def _is_strong_cross_match( 

777 fields: dict[str, Any], existing: Any, offset_hours: float 

778) -> bool: 

779 """Same known route, and both takeoff/landing times within the taxi 

780 window (the aircraft's block-to-airborne offset) of the pilot-side 

781 departure/arrival times — strong enough to merge without asking.""" 

782 from services.time_band_matching import ( # pyright: ignore[reportMissingImports] 

783 taxi_in_score, 

784 taxi_out_score, 

785 ) 

786 

787 dep = (fields.get("departure_icao") or "ZZZZ").upper() 

788 arr = (fields.get("arrival_icao") or "ZZZZ").upper() 

789 if "ZZZZ" in (dep, arr): 

790 return False 

791 if (dep, arr) != ( 

792 (existing.departure_icao or "").upper(), 

793 (existing.arrival_icao or "").upper(), 

794 ): 

795 return False 

796 score = taxi_out_score( 

797 existing.departure_time, fields.get("takeoff_time"), offset_hours 

798 ) + taxi_in_score(fields.get("landing_time"), existing.arrival_time, offset_hours) 

799 return score >= _AUTO_MERGE_MIN_TIME_SCORE 

800 

801 

802def split_airframe_auto_merges( 

803 conflicts: list[AirframeConflictRow], aircraft: Any