Coverage for app/pilots/routes.py: 100%
1202 statements
« prev ^ index » next coverage.py v7.15.4, created at 2026-08-31 10:13 +0000
1import contextlib
2import json
3import logging
4import math
5import os
6import uuid
7from datetime import (
8 UTC,
9)
10from datetime import (
11 date as _date,
12)
13from datetime import (
14 datetime as _datetime,
15)
16from datetime import (
17 timedelta as _td,
18)
19from typing import Any
21from flask import ( # pyright: ignore[reportMissingImports]
22 Blueprint,
23 abort,
24 current_app,
25 flash,
26 redirect,
27 render_template,
28 request,
29 session,
30 url_for,
31)
32from flask.typing import ResponseReturnValue # pyright: ignore[reportMissingImports]
33from flask_babel import gettext as _ # pyright: ignore[reportMissingImports]
34from flask_babel import ngettext
35from models import ( # pyright: ignore[reportMissingImports]
36 Aircraft,
37 Document,
38 Flight,
39 FstdType,
40 GpsTrack,
41 LogbookEntryType,
42 LogbookImportBatch,
43 LogbookImportMapping,
44 PersonalMinimumsItem,
45 PersonalMinimumsRevision,
46 PersonalMinimumsSection,
47 PersonalMinimumsStatus,
48 PersonalMinimumsTag,
49 PilotProfile,
50 Reservation,
51 ReservationStatus,
52 TenantUser,
53 db,
54)
55from sqlalchemy import func # pyright: ignore[reportMissingImports]
56from utils import ( # pyright: ignore[reportMissingImports]
57 login_required,
58 require_pilot_access,
59 user_can_access_aircraft,
60)
61from werkzeug.utils import secure_filename # pyright: ignore[reportMissingImports]
63from pilots.form_parsing import ( # pyright: ignore[reportMissingImports]
64 _parse_date,
65 apply_pilot_fields,
66 parse_pilot_fields,
67)
68from pilots.logbook_import import ( # pyright: ignore[reportMissingImports]
69 TARGET_FIELDS,
70 ConflictRow,
71 _assign_pilot_identity,
72 _norm,
73 execute_import,
74 find_conflicting_rows,
75 link_entries_to_aircraft,
76 parse_duration_value,
77 parse_file,
78 preview_rows,
79 propose_mapping,
80 type_hints,
81)
82from pilots.personal_minimums import ( # pyright: ignore[reportMissingImports]
83 STARTERS,
84 get_active_revision,
85 recency_breaches,
86)
88log = logging.getLogger(__name__)
90pilots_bp = Blueprint("pilots", __name__)
93def _current_user_id() -> int:
94 return int(session["user_id"])
97def _openaip_key() -> str | None:
98 from models import AppSetting # pyright: ignore[reportMissingImports]
100 s = db.session.get(AppSetting, "openaip_api_key")
101 return s.value if s and s.value else None
104def _get_or_create_profile(user_id: int) -> PilotProfile:
105 profile: PilotProfile | None = PilotProfile.query.filter_by(user_id=user_id).first()
106 if not profile:
107 profile = PilotProfile(user_id=user_id)
108 db.session.add(profile)
109 db.session.flush()
110 return profile
113def _my_entries_query(uid: int): # type: ignore[no-untyped-def]
114 """Base query for `Flight` rows this pilot occupies (either identity
115 slot) — the "my logbook" query, used throughout this module."""
116 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
118 return Flight.query.filter(
119 or_(Flight.pic_user_id == uid, Flight.second_crew_user_id == uid)
120 )
123def _check_logbook_milestone(entry: Flight, uid: int) -> None:
124 """Set one-shot session flags when a logbook milestone is crossed."""
125 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
127 mine = or_(Flight.pic_user_id == uid, Flight.second_crew_user_id == uid)
128 total = Flight.query.filter(mine).count()
129 if total == 100:
130 session["logbook_milestone"] = "100flights"
131 flash(_("🎉 100th logbook entry — congratulations!"), "success")
132 return
134 night = float(entry.night_time or 0)
135 if night > 0:
136 prev_night = (
137 db.session.query(func.count(Flight.id))
138 .filter(mine, Flight.id != entry.id, Flight.night_time > 0)
139 .scalar()
140 or 0
141 )
142 if prev_night == 0:
143 session["logbook_milestone"] = "first_night"
144 flash(_("🌙 First night flight logged — well done!"), "success")
145 return
147 dep = (entry.departure_icao or "").strip().upper()
148 arr = (entry.arrival_icao or "").strip().upper()
149 if dep and arr and dep != arr:
150 prev_xc = (
151 db.session.query(func.count(Flight.id))
152 .filter(
153 mine,
154 Flight.id != entry.id,
155 Flight.departure_icao.isnot(None),
156 Flight.arrival_icao.isnot(None),
157 Flight.departure_icao != Flight.arrival_icao,
158 )
159 .scalar()
160 or 0
161 )
162 if prev_xc == 0:
163 session["logbook_milestone"] = "first_xc"
164 flash(
165 _("✈️ First cross-country flight logged — congratulations!"), "success"
166 )
169# ── Profile ───────────────────────────────────────────────────────────────────
172@pilots_bp.route("/pilot/profile", methods=["GET", "POST"])
173@login_required
174@require_pilot_access
175def profile() -> ResponseReturnValue:
176 uid = _current_user_id()
177 p = _get_or_create_profile(uid)
179 if request.method == "POST":
180 errors = []
182 p.license_number = request.form.get("license_number", "").strip() or None
184 medical_str = request.form.get("medical_expiry", "")
185 medical, err = _parse_date(medical_str, "Medical expiry")
186 if err:
187 errors.append(err)
188 else:
189 p.medical_expiry = medical
191 sep_str = request.form.get("sep_expiry", "")
192 sep, err = _parse_date(sep_str, "SEP expiry")
193 if err:
194 errors.append(err)
195 else:
196 p.sep_expiry = sep
198 solo_str = request.form.get("first_solo_date", "")
199 solo, err = _parse_date(solo_str, _("First solo date"))
200 if err:
201 errors.append(err)
202 else:
203 p.first_solo_date = solo
205 ppl_str = request.form.get("ppl_issue_date", "")
206 ppl, err = _parse_date(ppl_str, _("PPL issue date"))
207 if err:
208 errors.append(err)
209 else:
210 p.ppl_issue_date = ppl
212 if errors:
213 for e in errors:
214 flash(e, "danger")
215 return (
216 render_template(
217 "pilots/profile.html", profile=p, pilot_docs=[], currency=None
218 ),
219 422,
220 )
222 db.session.commit()
223 flash(_("Profile saved."), "success")
224 return redirect(url_for("pilots.profile"))
226 from pilots.currency import (
227 currency_summary as _currency_summary, # pyright: ignore[reportMissingImports]
228 )
230 pilot_entries = _my_entries_query(uid).all()
231 currency = _currency_summary(p, pilot_entries)
233 pilot_docs = (
234 Document.query.filter_by(pilot_user_id=uid)
235 .order_by(Document.uploaded_at.desc())
236 .all()
237 )
238 return render_template(
239 "pilots/profile.html", profile=p, pilot_docs=pilot_docs, currency=currency
240 )
243_VALID_PER_PAGE = (10, 20, 50, 100)
244_DEFAULT_PER_PAGE = 20
247# ── Personal minimums ─────────────────────────────────────────────────────────
250def _active_minimums_revision(uid: int) -> PersonalMinimumsRevision | None:
251 return get_active_revision(uid)
254def _draft_minimums_revision(uid: int) -> PersonalMinimumsRevision | None:
255 revision: PersonalMinimumsRevision | None = (
256 PersonalMinimumsRevision.query.filter_by(
257 user_id=uid, status=PersonalMinimumsStatus.DRAFT
258 ).first()
259 )
260 return revision
263def _get_own_revision_or_404(revision_id: int, uid: int) -> PersonalMinimumsRevision:
264 revision = db.session.get(PersonalMinimumsRevision, revision_id)
265 if revision is None or revision.user_id != uid:
266 abort(404)
267 return revision
270def _get_own_draft_section_or_404(section_id: int, uid: int) -> PersonalMinimumsSection:
271 section = db.session.get(PersonalMinimumsSection, section_id)
272 if (
273 section is None
274 or section.revision.user_id != uid
275 or section.revision.status != PersonalMinimumsStatus.DRAFT
276 ):
277 abort(404)
278 return section
281def _get_own_draft_item_or_404(item_id: int, uid: int) -> PersonalMinimumsItem:
282 item = db.session.get(PersonalMinimumsItem, item_id)
283 if (
284 item is None
285 or item.section.revision.user_id != uid
286 or item.section.revision.status != PersonalMinimumsStatus.DRAFT
287 ):
288 abort(404)
289 return item
292def _flew_or_reserved_today(uid: int) -> bool:
293 today = _date.today()
294 if _my_entries_query(uid).filter(Flight.date == today).first():
295 return True
296 reservation = Reservation.query.filter(
297 Reservation.pilot_user_id == uid,
298 Reservation.status.in_(
299 [ReservationStatus.PENDING, ReservationStatus.CONFIRMED]
300 ),
301 db.func.date(Reservation.start_dt) <= today,
302 db.func.date(Reservation.end_dt) >= today,
303 ).first()
304 return reservation is not None
307@pilots_bp.route("/pilot/minimums")
308@login_required
309@require_pilot_access
310def minimums_view() -> ResponseReturnValue:
311 uid = _current_user_id()
312 active = _active_minimums_revision(uid)
313 if active is not None:
314 return render_template(
315 "pilots/minimums_view.html", revision=active, is_own_draft=False
316 )
317 if _draft_minimums_revision(uid) is not None:
318 return redirect(url_for("pilots.minimums_edit"))
319 return render_template("pilots/minimums_start.html")
322@pilots_bp.route("/pilot/minimums/create", methods=["POST"])
323@login_required
324@require_pilot_access
325def minimums_create() -> ResponseReturnValue:
326 uid = _current_user_id()
327 if _draft_minimums_revision(uid) is not None:
328 flash(_("You already have a draft in progress."), "warning")
329 return redirect(url_for("pilots.minimums_edit"))
330 starter = request.form.get("starter", "blank")
331 if starter not in ("blank", "light", "full"):
332 starter = "blank"
333 next_number = (
334 db.session.query(db.func.max(PersonalMinimumsRevision.revision_number))
335 .filter_by(user_id=uid)
336 .scalar()
337 or 0
338 ) + 1
339 revision = PersonalMinimumsRevision(
340 user_id=uid,
341 revision_number=next_number,
342 status=PersonalMinimumsStatus.DRAFT,
343 )
344 db.session.add(revision)
345 db.session.flush()
346 if starter in STARTERS:
347 for s_order, (title, items) in enumerate(STARTERS[starter]):
348 section = PersonalMinimumsSection(
349 revision_id=revision.id, title=str(title), sort_order=s_order
350 )
351 db.session.add(section)
352 db.session.flush()
353 for i_order, (label, tag) in enumerate(items):
354 db.session.add(
355 PersonalMinimumsItem(
356 section_id=section.id,
357 label=str(label),
358 value="",
359 semantic_tag=tag,
360 sort_order=i_order,
361 )
362 )
363 db.session.commit()
364 flash(_("Draft created."), "success")
365 return redirect(url_for("pilots.minimums_edit"))
368@pilots_bp.route("/pilot/minimums/history")
369@login_required
370@require_pilot_access
371def minimums_history() -> ResponseReturnValue:
372 uid = _current_user_id()
373 revisions = (
374 PersonalMinimumsRevision.query.filter_by(user_id=uid)
375 .order_by(PersonalMinimumsRevision.revision_number.desc())
376 .all()
377 )
378 return render_template("pilots/minimums_history.html", revisions=revisions)
381@pilots_bp.route("/pilot/minimums/revision/<int:revision_id>")
382@login_required
383@require_pilot_access
384def minimums_revision_detail(revision_id: int) -> ResponseReturnValue:
385 uid = _current_user_id()
386 revision = _get_own_revision_or_404(revision_id, uid)
387 return render_template(
388 "pilots/minimums_view.html",
389 revision=revision,
390 is_own_draft=(revision.status == PersonalMinimumsStatus.DRAFT),
391 )
394@pilots_bp.route("/pilot/minimums/revise", methods=["POST"])
395@login_required
396@require_pilot_access
397def minimums_revise() -> ResponseReturnValue:
398 uid = _current_user_id()
399 active = _active_minimums_revision(uid)
400 if active is None:
401 flash(_("No active revision to revise yet."), "danger")
402 return redirect(url_for("pilots.minimums_view"))
403 if _draft_minimums_revision(uid) is not None:
404 flash(_("You already have a draft in progress."), "warning")
405 return redirect(url_for("pilots.minimums_edit"))
406 next_number = (
407 db.session.query(db.func.max(PersonalMinimumsRevision.revision_number))
408 .filter_by(user_id=uid)
409 .scalar()
410 or 0
411 ) + 1
412 draft = PersonalMinimumsRevision(
413 user_id=uid,
414 revision_number=next_number,
415 status=PersonalMinimumsStatus.DRAFT,
416 )
417 db.session.add(draft)
418 db.session.flush()
419 for section in active.sections: # type: ignore[attr-defined]
420 new_section = PersonalMinimumsSection(
421 revision_id=draft.id, title=section.title, sort_order=section.sort_order
422 )
423 db.session.add(new_section)
424 db.session.flush()
425 for item in section.items:
426 db.session.add(
427 PersonalMinimumsItem(
428 section_id=new_section.id,
429 label=item.label,
430 value=item.value,
431 semantic_tag=item.semantic_tag,
432 numeric_value=item.numeric_value,
433 sort_order=item.sort_order,
434 )
435 )
436 db.session.commit()
437 flash(_("Draft created from your active revision."), "success")
438 return redirect(url_for("pilots.minimums_edit"))
441@pilots_bp.route("/pilot/minimums/edit")
442@login_required
443@require_pilot_access
444def minimums_edit() -> ResponseReturnValue:
445 uid = _current_user_id()
446 draft = _draft_minimums_revision(uid)
447 if draft is None:
448 return redirect(url_for("pilots.minimums_view"))
449 return render_template(
450 "pilots/minimums_edit.html", revision=draft, tags=PersonalMinimumsTag
451 )
454def _validate_tag_and_numeric(
455 tag_raw: str, numeric_raw: str
456) -> tuple[str | None, float | None, str | None]:
457 """Return (tag_or_None, numeric_value_or_None, error_or_None)."""
458 tag = tag_raw or None
459 if tag and tag not in PersonalMinimumsTag.ALL:
460 return None, None, str(_("Unrecognized tag."))
461 if tag:
462 try:
463 numeric_value = float(numeric_raw)
464 if not math.isfinite(numeric_value):
465 raise ValueError
466 except ValueError:
467 return None, None, str(_("This tag requires a numeric value."))
468 return tag, numeric_value, None
469 return None, None, None
472@pilots_bp.route("/pilot/minimums/section/add", methods=["POST"])
473@login_required
474@require_pilot_access
475def minimums_section_add() -> ResponseReturnValue:
476 uid = _current_user_id()
477 draft = _draft_minimums_revision(uid)
478 if draft is None:
479 abort(404)
480 title = request.form.get("title", "").strip()
481 if not title:
482 flash(_("Section title is required."), "danger")
483 return redirect(url_for("pilots.minimums_edit"))
484 max_order = (
485 db.session.query(db.func.max(PersonalMinimumsSection.sort_order))
486 .filter_by(revision_id=draft.id)
487 .scalar()
488 )
489 next_order = 0 if max_order is None else max_order + 1
490 db.session.add(
491 PersonalMinimumsSection(
492 revision_id=draft.id, title=title, sort_order=next_order
493 )
494 )
495 db.session.commit()
496 flash(_("Section added."), "success")
497 return redirect(url_for("pilots.minimums_edit"))
500@pilots_bp.route("/pilot/minimums/section/<int:section_id>/edit", methods=["POST"])
501@login_required
502@require_pilot_access
503def minimums_section_edit(section_id: int) -> ResponseReturnValue:
504 uid = _current_user_id()
505 section = _get_own_draft_section_or_404(section_id, uid)
506 title = request.form.get("title", "").strip()
507 if not title:
508 flash(_("Section title is required."), "danger")
509 return redirect(url_for("pilots.minimums_edit"))
510 section.title = title
511 db.session.commit()
512 flash(_("Section updated."), "success")
513 return redirect(url_for("pilots.minimums_edit"))
516@pilots_bp.route("/pilot/minimums/section/<int:section_id>/delete", methods=["POST"])
517@login_required
518@require_pilot_access
519def minimums_section_delete(section_id: int) -> ResponseReturnValue:
520 uid = _current_user_id()
521 section = _get_own_draft_section_or_404(section_id, uid)
522 db.session.delete(section)
523 db.session.commit()
524 flash(_("Section removed."), "success")
525 return redirect(url_for("pilots.minimums_edit"))
528def _move_sort_order(
529 model: Any, obj: Any, scope_field: str, scope_value: int, direction: str
530) -> None:
531 """Swap obj.sort_order with its neighbour within the given scope."""
532 siblings = (
533 model.query.filter_by(**{scope_field: scope_value})
534 .order_by(model.sort_order)
535 .all()
536 )
537 idx = next(i for i, s in enumerate(siblings) if s.id == obj.id)
538 neighbour_idx = idx - 1 if direction == "up" else idx + 1
539 if neighbour_idx < 0 or neighbour_idx >= len(siblings):
540 return
541 neighbour = siblings[neighbour_idx]
542 obj.sort_order, neighbour.sort_order = neighbour.sort_order, obj.sort_order
545@pilots_bp.route("/pilot/minimums/section/<int:section_id>/move-up", methods=["POST"])
546@login_required
547@require_pilot_access
548def minimums_section_move_up(section_id: int) -> ResponseReturnValue:
549 uid = _current_user_id()
550 section = _get_own_draft_section_or_404(section_id, uid)
551 _move_sort_order(
552 PersonalMinimumsSection, section, "revision_id", section.revision_id, "up"
553 )
554 db.session.commit()
555 return redirect(url_for("pilots.minimums_edit"))
558@pilots_bp.route("/pilot/minimums/section/<int:section_id>/move-down", methods=["POST"])
559@login_required
560@require_pilot_access
561def minimums_section_move_down(section_id: int) -> ResponseReturnValue:
562 uid = _current_user_id()
563 section = _get_own_draft_section_or_404(section_id, uid)
564 _move_sort_order(
565 PersonalMinimumsSection, section, "revision_id", section.revision_id, "down"
566 )
567 db.session.commit()
568 return redirect(url_for("pilots.minimums_edit"))
571@pilots_bp.route("/pilot/minimums/item/add", methods=["POST"])
572@login_required
573@require_pilot_access
574def minimums_item_add() -> ResponseReturnValue:
575 uid = _current_user_id()
576 section_id = request.form.get("section_id", type=int)
577 section = _get_own_draft_section_or_404(section_id, uid) if section_id else None
578 if section is None:
579 abort(404)
580 label = request.form.get("label", "").strip()
581 value = request.form.get("value", "").strip()
582 tag_raw = request.form.get("tag", "").strip()
583 numeric_raw = request.form.get("numeric_value", "").strip()
584 if not label:
585 flash(_("Item label is required."), "danger")
586 return redirect(url_for("pilots.minimums_edit"))
587 tag, numeric_value, error = _validate_tag_and_numeric(tag_raw, numeric_raw)
588 if error:
589 flash(error, "danger")
590 return redirect(url_for("pilots.minimums_edit"))
591 max_order = (
592 db.session.query(db.func.max(PersonalMinimumsItem.sort_order))
593 .filter_by(section_id=section.id)
594 .scalar()
595 )
596 next_order = 0 if max_order is None else max_order + 1
597 db.session.add(
598 PersonalMinimumsItem(
599 section_id=section.id,
600 label=label,
601 value=value or None,
602 semantic_tag=tag,
603 numeric_value=numeric_value,
604 sort_order=next_order,
605 )
606 )
607 db.session.commit()
608 flash(_("Item added."), "success")
609 return redirect(url_for("pilots.minimums_edit"))
612@pilots_bp.route("/pilot/minimums/item/<int:item_id>/edit", methods=["POST"])
613@login_required
614@require_pilot_access
615def minimums_item_edit(item_id: int) -> ResponseReturnValue:
616 uid = _current_user_id()
617 item = _get_own_draft_item_or_404(item_id, uid)
618 label = request.form.get("label", "").strip()
619 value = request.form.get("value", "").strip()
620 tag_raw = request.form.get("tag", "").strip()
621 numeric_raw = request.form.get("numeric_value", "").strip()
622 if not label:
623 flash(_("Item label is required."), "danger")
624 return redirect(url_for("pilots.minimums_edit"))
625 tag, numeric_value, error = _validate_tag_and_numeric(tag_raw, numeric_raw)
626 if error:
627 flash(error, "danger")
628 return redirect(url_for("pilots.minimums_edit"))
629 item.label = label
630 item.value = value or None
631 item.semantic_tag = tag
632 item.numeric_value = numeric_value
633 db.session.commit()
634 flash(_("Item updated."), "success")
635 return redirect(url_for("pilots.minimums_edit"))
638@pilots_bp.route("/pilot/minimums/item/<int:item_id>/delete", methods=["POST"])
639@login_required
640@require_pilot_access
641def minimums_item_delete(item_id: int) -> ResponseReturnValue:
642 uid = _current_user_id()
643 item = _get_own_draft_item_or_404(item_id, uid)
644 db.session.delete(item)
645 db.session.commit()
646 flash(_("Item removed."), "success")
647 return redirect(url_for("pilots.minimums_edit"))
650@pilots_bp.route("/pilot/minimums/item/<int:item_id>/move-up", methods=["POST"])
651@login_required
652@require_pilot_access
653def minimums_item_move_up(item_id: int) -> ResponseReturnValue:
654 uid = _current_user_id()
655 item = _get_own_draft_item_or_404(item_id, uid)
656 _move_sort_order(PersonalMinimumsItem, item, "section_id", item.section_id, "up")
657 db.session.commit()
658 return redirect(url_for("pilots.minimums_edit"))
661@pilots_bp.route("/pilot/minimums/item/<int:item_id>/move-down", methods=["POST"])
662@login_required
663@require_pilot_access
664def minimums_item_move_down(item_id: int) -> ResponseReturnValue:
665 uid = _current_user_id()
666 item = _get_own_draft_item_or_404(item_id, uid)
667 _move_sort_order(PersonalMinimumsItem, item, "section_id", item.section_id, "down")
668 db.session.commit()
669 return redirect(url_for("pilots.minimums_edit"))
672@pilots_bp.route("/pilot/minimums/publish", methods=["GET", "POST"])
673@login_required
674@require_pilot_access
675def minimums_publish() -> ResponseReturnValue:
676 uid = _current_user_id()
677 draft = _draft_minimums_revision(uid)
678 if draft is None:
679 flash(_("No draft to publish."), "danger")
680 return redirect(url_for("pilots.minimums_view"))
681 if not draft.sections:
682 flash(_("Add at least one section before publishing."), "danger")
683 return redirect(url_for("pilots.minimums_edit"))
685 if request.method == "POST":
686 active = _active_minimums_revision(uid)
687 if active is not None:
688 active.status = PersonalMinimumsStatus.SUPERSEDED
689 totals = _compute_totals_sql(uid)
690 draft.status = PersonalMinimumsStatus.ACTIVE
691 draft.published_on = _date.today()
692 draft.experience_hours = totals["total_flight_time"]
693 db.session.commit()
694 flash(_("Personal minimums published."), "success")
695 return redirect(url_for("pilots.minimums_view"))
697 return render_template(
698 "pilots/minimums_publish_confirm.html",
699 revision=draft,
700 flew_or_reserved_today=_flew_or_reserved_today(uid),
701 )
704@pilots_bp.route("/pilot/minimums/delete-draft", methods=["POST"])
705@login_required
706@require_pilot_access
707def minimums_delete_draft() -> ResponseReturnValue:
708 uid = _current_user_id()
709 draft = _draft_minimums_revision(uid)
710 if draft is None:
711 abort(404)
712 db.session.delete(draft)
713 db.session.commit()
714 flash(_("Draft discarded."), "success")
715 return redirect(url_for("pilots.minimums_view"))
718@pilots_bp.route("/pilot/minimums/print")
719@login_required
720@require_pilot_access
721def minimums_print() -> ResponseReturnValue:
722 uid = _current_user_id()
723 active = _active_minimums_revision(uid)
724 if active is None:
725 flash(_("No active personal minimums to print yet."), "warning")
726 return redirect(url_for("pilots.minimums_view"))
727 return render_template("pilots/minimums_print.html", revision=active)
730# ── GPS tracks map ────────────────────────────────────────────────────────────
733@pilots_bp.route("/pilot/tracks")
734@login_required
735@require_pilot_access
736def pilot_tracks() -> ResponseReturnValue:
737 from flask import url_for as _url_for
739 uid = _current_user_id()
740 entries = (
741 _my_entries_query(uid)
742 .filter(Flight.gps_track_id.isnot(None))
743 .order_by(Flight.date.asc())
744 .all()
745 )
746 track_rows = [
747 {
748 "date": str(e.date),
749 "dep": e.departure_icao or "",
750 "arr": e.arrival_icao or "",
751 "time_str": f"{e.total_flight_time} h"
752 if e.total_flight_time is not None
753 else "",
754 "view_url": _url_for(
755 "aircraft.flight_detail",
756 aircraft_id=e.aircraft_id,
757 flight_id=e.id,
758 )
759 if e.aircraft_id
760 else _url_for("pilots.view_entry", entry_id=e.id),
761 "geojson": e.gps_track.geojson if e.gps_track else None,
762 }
763 for e in entries
764 ]
766 return render_template(
767 "pilots/flight_tracks.html",
768 track_rows=track_rows,
769 openaip_key=_openaip_key(),
770 )
773@pilots_bp.route("/pilot/tracks/animation.gif")
774@login_required
775@require_pilot_access
776def pilot_tracks_gif() -> ResponseReturnValue:
777 from flask import Response # pyright: ignore[reportMissingImports]
778 from utils import ( # pyright: ignore[reportMissingImports]
779 generate_tracks_gif,
780 sort_tracks_oldest_first,
781 )
783 uid = _current_user_id()
784 entries = _my_entries_query(uid).filter(Flight.gps_track_id.isnot(None)).all()
785 track_rows = sort_tracks_oldest_first(
786 [
787 {
788 "date": str(e.date),
789 "dep": e.departure_icao or "",
790 "arr": e.arrival_icao or "",
791 "geojson": e.gps_track.geojson if e.gps_track else None,
792 }
793 for e in entries
794 ]
795 )
796 portrait = request.args.get("orientation") == "portrait"
797 hires = request.args.get("quality") == "hires"
798 base_w, base_h = (480, 800) if portrait else (800, 480)
799 mul = 2 if hires else 1
800 canvas_w, canvas_h = base_w * mul, base_h * mul
801 gif_bytes = generate_tracks_gif(
802 track_rows,
803 _openaip_key=_openaip_key(),
804 canvas_w=canvas_w,
805 canvas_h=canvas_h,
806 high_res=hires,
807 )
808 orient_sfx = "-portrait" if portrait else ""
809 qual_sfx = "-hires" if hires else ""
810 suffix = orient_sfx + qual_sfx
811 return Response(
812 gif_bytes,
813 mimetype="image/gif",
814 headers={
815 "Content-Disposition": f'attachment; filename="my_tracks{suffix}.gif"'
816 },
817 )
820# ── Logbook entry detail (read-only) ─────────────────────────────────────────
823@pilots_bp.route("/pilot/logbook/<int:entry_id>/view")
824@login_required
825@require_pilot_access
826def view_entry(entry_id: int) -> ResponseReturnValue:
827 uid = _current_user_id()
828 entry = db.session.get(Flight, entry_id)
829 if not entry or (entry.pic_user_id != uid and entry.second_crew_user_id != uid):
830 abort(404)
832 return render_template(
833 "pilots/entry_detail.html",
834 entry=entry,
835 openaip_key=_openaip_key(),
836 LogbookEntryType=LogbookEntryType,
837 )
840# ── Logbook list ──────────────────────────────────────────────────────────────
843@pilots_bp.route("/pilot/logbook")
844@login_required
845@require_pilot_access
846def logbook() -> ResponseReturnValue:
847 uid = _current_user_id()
848 order = request.args.get("order", "desc")
849 page = request.args.get("page", 1, type=int)
850 pp_raw = request.args.get("per_page", str(_DEFAULT_PER_PAGE))
851 show_all = pp_raw == "all"
852 per_page = (
853 None
854 if show_all
855 else (
856 int(pp_raw)
857 if pp_raw.isdigit() and int(pp_raw) in _VALID_PER_PAGE
858 else _DEFAULT_PER_PAGE
859 )
860 )
862 q = _my_entries_query(uid)
863 if order == "asc":
864 q = q.order_by(
865 Flight.date.asc(),
866 Flight.departure_time.asc().nullslast(),
867 Flight.id.asc(),
868 )
869 else:
870 q = q.order_by(
871 Flight.date.desc(),
872 Flight.departure_time.desc().nullslast(),
873 Flight.id.desc(),
874 )
876 if show_all:
877 entries = q.all()
878 pagination = None
879 else:
880 pagination = q.paginate(page=page, per_page=per_page, error_out=False)
881 entries = pagination.items
883 totals = _compute_totals_sql(uid)
884 logbook_milestone = session.pop("logbook_milestone", None)
886 active_minimums = _active_minimums_revision(uid)
887 minimums_breaches = (
888 recency_breaches(active_minimums, uid) if active_minimums else []
889 )
891 return render_template(
892 "pilots/logbook.html",
893 entries=entries,
894 pagination=pagination,
895 totals=totals,
896 order=order,
897 per_page=pp_raw,
898 valid_per_page=_VALID_PER_PAGE,
899 logbook_milestone=logbook_milestone,
900 LogbookEntryType=LogbookEntryType,
901 minimums_breaches=minimums_breaches,
902 )
905def _compute_totals_sql(pilot_user_id: int) -> dict[str, object]:
906 """Aggregate totals over ALL entries for the pilot via SQL queries.
908 Shared EASA figures (night/instrument time, landings, single_pilot_se/me,
909 multi_pilot, fstd_duration) are summed once per row regardless of which
910 slot this pilot occupies — they describe the flight, not the occupant.
911 function_pic only ever belongs to the pic_user_id slot's own hours, and
912 function_copilot/dual/instructor only to the second_crew_user_id slot's
913 (see Flight's docstring) — each summed with its own slot-scoped filter,
914 so a flight where this pilot was PIC doesn't accidentally credit them
915 with the other occupant's dual/instructor time, or vice versa.
916 """
917 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
919 mine = or_(
920 Flight.pic_user_id == pilot_user_id,
921 Flight.second_crew_user_id == pilot_user_id,
922 )
923 row = (
924 db.session.query(
925 func.sum(Flight.night_time),
926 func.sum(Flight.instrument_time),
927 func.sum(Flight.landings_day),
928 func.sum(Flight.landings_night),
929 func.sum(Flight.single_pilot_se),
930 func.sum(Flight.single_pilot_me),
931 func.sum(Flight.multi_pilot),
932 func.sum(Flight.fstd_duration),
933 )
934 .filter(mine)
935 .one()
936 )
937 fn_pic = (
938 db.session.query(func.sum(Flight.function_pic))
939 .filter(Flight.pic_user_id == pilot_user_id)
940 .scalar()
941 )
942 fn_second = (
943 db.session.query(
944 func.sum(Flight.function_copilot),
945 func.sum(Flight.function_dual),
946 func.sum(Flight.function_instructor),
947 )
948 .filter(Flight.second_crew_user_id == pilot_user_id)
949 .one()
950 )
952 sp_se = round(float(row[4] or 0), 1)
953 sp_me = round(float(row[5] or 0), 1)
954 multi = round(float(row[6] or 0), 1)
956 return {
957 "night_time": round(float(row[0] or 0), 1),
958 "instrument_time": round(float(row[1] or 0), 1),
959 "landings_day": int(row[2] or 0),
960 "landings_night": int(row[3] or 0),
961 "single_pilot_se": sp_se,
962 "single_pilot_me": sp_me,
963 "multi_pilot": multi,
964 # FSTD/simulator sessions are excluded from flight-time totals — they
965 # are not flight hours, only single_pilot_se/me and multi_pilot count.
966 "total_flight_time": round(sp_se + sp_me + multi, 1),
967 "function_pic": round(float(fn_pic or 0), 1),
968 "function_copilot": round(float(fn_second[0] or 0), 1),
969 "function_dual": round(float(fn_second[1] or 0), 1),
970 "function_instructor": round(float(fn_second[2] or 0), 1),
971 "fstd_duration": round(float(row[7] or 0), 1),
972 }
975# ── New entry ────────────────────────────────────────────────────────────────
978@pilots_bp.route("/pilot/logbook/new", methods=["GET", "POST"])
979@login_required
980@require_pilot_access
981def new_entry() -> ResponseReturnValue:
982 uid = _current_user_id()
984 if request.method == "POST":
985 values, errors = parse_pilot_fields(request.form)
986 if errors:
987 for e in errors:
988 flash(e, "danger")
989 return render_template(
990 "pilots/entry_form.html",
991 entry=None,
992 form=request.form,
993 action="new",
994 openaip_key=_openaip_key(),
995 LogbookEntryType=LogbookEntryType,
996 FstdType=FstdType,
997 ), 422
998 entry = Flight(pic_user_id=uid)
999 apply_pilot_fields(entry, values)
1000 db.session.add(entry)
1001 db.session.flush()
1002 _apply_gps_to_pilot_entry(entry)
1003 db.session.commit()
1004 _check_logbook_milestone(entry, uid)
1005 flash(_("Logbook entry saved."), "success")
1006 return redirect(url_for("pilots.logbook"))
1008 return render_template(
1009 "pilots/entry_form.html",
1010 entry=None,
1011 form={},
1012 action="new",
1013 openaip_key=_openaip_key(),
1014 LogbookEntryType=LogbookEntryType,
1015 FstdType=FstdType,
1016 )
1019# ── Edit entry ────────────────────────────────────────────────────────────────
1022@pilots_bp.route("/pilot/logbook/<int:entry_id>/edit", methods=["GET", "POST"])
1023@login_required
1024@require_pilot_access
1025def edit_entry(entry_id: int) -> ResponseReturnValue:
1026 uid = _current_user_id()
1027 entry = db.session.get(Flight, entry_id)
1028 if not entry or (entry.pic_user_id != uid and entry.second_crew_user_id != uid):
1029 abort(404)
1031 if entry.aircraft_id:
1032 return redirect(url_for("flights.edit_flight", flight_id=entry.id))
1034 if request.method == "POST":
1035 values, errors = parse_pilot_fields(request.form)
1036 if errors:
1037 for e in errors:
1038 flash(e, "danger")
1039 return render_template(
1040 "pilots/entry_form.html",
1041 entry=entry,
1042 form=request.form,
1043 action="edit",
1044 openaip_key=_openaip_key(),
1045 LogbookEntryType=LogbookEntryType,
1046 FstdType=FstdType,
1047 ), 422
1048 apply_pilot_fields(entry, values)
1049 _apply_gps_to_pilot_entry(entry)
1050 db.session.commit()
1051 flash(_("Logbook entry updated."), "success")
1052 return redirect(url_for("pilots.logbook"))
1054 return render_template(
1055 "pilots/entry_form.html",
1056 entry=entry,
1057 form={},
1058 action="edit",
1059 openaip_key=_openaip_key(),
1060 LogbookEntryType=LogbookEntryType,
1061 FstdType=FstdType,
1062 )
1065# ── Delete entry ──────────────────────────────────────────────────────────────
1068def _delete_upload(filename: str | None) -> None:
1069 if not filename:
1070 return
1071 folder = current_app.config.get("UPLOAD_FOLDER", "/data/uploads")
1072 try:
1073 os.remove(os.path.join(folder, filename))
1074 except OSError:
1075 current_app.logger.debug(
1076 "Could not delete upload %s (already absent?)", filename
1077 )
1080@pilots_bp.route("/pilot/logbook/<int:entry_id>/delete", methods=["POST"])
1081@login_required
1082@require_pilot_access
1083def delete_entry(entry_id: int) -> ResponseReturnValue:
1084 uid = _current_user_id()
1085 entry = db.session.get(Flight, entry_id)
1086 if not entry or (entry.pic_user_id != uid and entry.second_crew_user_id != uid):
1087 abort(404)
1089 # Unified model: there's only one row now, so deleting it removes both
1090 # the pilot's own record and the airframe log entry at once (no more
1091 # separate "also delete the linked flight" choice). If it's a managed
1092 # aircraft's row, still gate on aircraft access — not just crew identity
1093 # — before deleting shared airframe data.
1094 if entry.aircraft_id and not user_can_access_aircraft(entry.aircraft_id):
1095 abort(403)
1097 _delete_upload(entry.flight_counter_photo)
1098 _delete_upload(entry.engine_counter_photo)
1099 db.session.delete(entry)
1100 db.session.commit()
1101 flash(_("Logbook entry deleted."), "success")
1102 return redirect(url_for("pilots.logbook"))
1105# ── GPS track helper ──────────────────────────────────────────────────────────
1108def _apply_gps_to_pilot_entry(entry: Flight) -> None:
1109 """Create or update the GpsTrack linked to a pilot logbook entry from form data."""
1110 f = request.form
1111 gps_geojson_raw = f.get("gps_geojson", "").strip()
1112 gps_filename = f.get("gps_filename", "").strip() or None
1113 if not gps_geojson_raw and not gps_filename:
1114 return
1116 geojson = None
1117 if gps_geojson_raw:
1118 with contextlib.suppress(
1119 ValueError
1120 ): # malformed hidden field — GPS track simply not applied
1121 geojson = json.loads(gps_geojson_raw)
1123 def _parse_dt(raw: str) -> "_datetime | None":
1124 try:
1125 return _datetime.fromisoformat(raw) if raw else None
1126 except ValueError:
1127 return None
1129 block_off = _parse_dt(f.get("gps_block_off_utc", "").strip())
1130 block_on = _parse_dt(f.get("gps_block_on_utc", "").strip())
1131 dep = f.get("departure_place", "").strip().upper()[:4] or None
1132 arr = f.get("arrival_place", "").strip().upper()[:4] or None
1134 if entry.gps_track_id:
1135 gt = db.session.get(GpsTrack, entry.gps_track_id)
1136 if gt:
1137 if geojson is not None:
1138 gt.geojson = geojson
1139 if gps_filename:
1140 gt.source_filename = gps_filename
1141 if block_off:
1142 gt.block_off_utc = block_off
1143 if block_on:
1144 gt.block_on_utc = block_on
1145 return
1147 gt = GpsTrack(
1148 source_filename=gps_filename,
1149 block_off_utc=block_off,
1150 block_on_utc=block_on,
1151 departure_icao=dep,
1152 arrival_icao=arr,
1153 geojson=geojson,
1154 )
1155 db.session.add(gt)
1156 db.session.flush()
1157 entry.gps_track_id = gt.id
1160# ── Logbook Import ────────────────────────────────────────────────────────────
1162_IMPORT_SESSION_KEY = "logbook_import"
1163_IMPORT_REVIEW_SESSION_KEY = "logbook_import_review"
1164_ALLOWED_IMPORT_EXTS = {".csv", ".xlsx", ".xls"}
1165_MAX_IMPORT_BYTES = 10 * 1024 * 1024 # 10 MB
1168def _import_tmp_dir() -> str:
1169 folder = current_app.config.get("UPLOAD_FOLDER", "/data/uploads")
1170 d = os.path.join(folder, "import_tmp")
1171 os.makedirs(d, exist_ok=True)
1172 return d
1175def _cleanup_previous_tmp(uid: int) -> None:
1176 """Delete any leftover temp import file for this user, including one
1177 left behind by an abandoned conflict-review (started a fresh upload
1178 instead of finishing it)."""
1179 meta = session.get(_IMPORT_SESSION_KEY)
1180 if meta and meta.get("uid") == uid:
1181 tmp = meta.get("tmp_path")
1182 if tmp and os.path.isfile(tmp):
1183 try:
1184 os.remove(tmp)
1185 except OSError as exc:
1186 current_app.logger.debug("cleanup tmp import file: %s", exc)
1187 session.pop(_IMPORT_SESSION_KEY, None)
1189 review_state = session.get(_IMPORT_REVIEW_SESSION_KEY)
1190 if review_state and review_state.get("uid") == uid:
1191 tmp = review_state.get("tmp_path")
1192 if tmp and os.path.isfile(tmp):
1193 try:
1194 os.remove(tmp)
1195 except OSError as exc:
1196 current_app.logger.debug("cleanup tmp review file: %s", exc)
1197 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1200@pilots_bp.route("/pilot/logbook/import", methods=["GET", "POST"])
1201@login_required
1202@require_pilot_access
1203def import_upload() -> ResponseReturnValue:
1204 uid = _current_user_id()
1206 if request.method == "GET":
1207 return render_template("pilots/import_upload.html")
1209 # ── POST: receive file, parse, present mapping page ───────────────────────
1210 uploaded = request.files.get("logbook_file")
1211 if not uploaded or not uploaded.filename:
1212 flash(_("Please select a file to upload."), "danger")
1213 return render_template("pilots/import_upload.html"), 422
1215 ext = os.path.splitext(uploaded.filename)[1].lower()
1216 if ext not in _ALLOWED_IMPORT_EXTS:
1217 flash(
1218 _("Unsupported format. Please upload a .csv or .xlsx file."),
1219 "danger",
1220 )
1221 return render_template("pilots/import_upload.html"), 422
1223 data = uploaded.read()
1224 if len(data) > _MAX_IMPORT_BYTES:
1225 flash(_("File too large (maximum 10 MB)."), "danger")
1226 return render_template("pilots/import_upload.html"), 422
1228 try:
1229 parsed = parse_file(data, uploaded.filename)
1230 except ValueError as exc:
1231 flash(str(exc), "danger")
1232 return render_template("pilots/import_upload.html"), 422
1234 # Save to a temp file so execute step can re-parse without re-upload
1235 _cleanup_previous_tmp(uid)
1236 safe_base = secure_filename(uploaded.filename) or "upload"
1237 tmp_name = f"import_{uid}_{uuid.uuid4().hex}_{safe_base}"
1238 tmp_path = os.path.join(_import_tmp_dir(), tmp_name)
1239 with open(tmp_path, "wb") as fh:
1240 fh.write(data)
1242 session[_IMPORT_SESSION_KEY] = {
1243 "uid": uid,
1244 "tmp_path": tmp_path,
1245 "original_filename": uploaded.filename,
1246 "norm_cols": parsed.norm_cols,
1247 "raw_cols": parsed.raw_cols,
1248 "fingerprint": parsed.fingerprint,
1249 }
1251 # Look up saved mappings for this pilot
1252 saved = LogbookImportMapping.query.filter_by(pilot_user_id=uid).all()
1253 proposal = propose_mapping(parsed, saved)
1255 preview = preview_rows(parsed, proposal.mapping, n=5)
1257 return render_template(
1258 "pilots/import_map.html",
1259 norm_cols=parsed.norm_cols,
1260 raw_cols=parsed.raw_cols,
1261 base_norm_cols=[_norm(r) for r in parsed.raw_cols],
1262 mapping=proposal.mapping,
1263 match_type=proposal.match_type,
1264 fuzzy_score=proposal.fuzzy_score,
1265 target_fields=TARGET_FIELDS,
1266 preview=preview,
1267 filename=uploaded.filename,
1268 type_hints=type_hints(parsed, proposal.mapping),
1269 )
1272@pilots_bp.route("/pilot/logbook/import/execute", methods=["POST"])
1273@login_required
1274@require_pilot_access
1275def import_execute() -> ResponseReturnValue:
1276 uid = _current_user_id()
1277 meta = session.get(_IMPORT_SESSION_KEY)
1279 if not meta or meta.get("uid") != uid:
1280 flash(_("Import session expired. Please upload the file again."), "warning")
1281 return redirect(url_for("pilots.import_upload"))
1283 tmp_path: str = meta["tmp_path"]
1284 original_filename: str = meta["original_filename"]
1285 norm_cols: list[str] = meta["norm_cols"]
1286 fingerprint: str = meta["fingerprint"]
1288 if not os.path.isfile(tmp_path):
1289 flash(_("Temporary file not found. Please upload the file again."), "warning")
1290 session.pop(_IMPORT_SESSION_KEY, None)
1291 return redirect(url_for("pilots.import_upload"))
1293 # Reconstruct mapping from form
1294 mapping: dict[str, str] = {}
1295 for col in norm_cols:
1296 val = request.form.get(f"mapping_{col}", "ignore").strip()
1297 mapping[col] = val if val in TARGET_FIELDS else "ignore"
1299 # Validate: at least 'date' must be mapped
1300 if "date" not in mapping.values():
1301 flash(_("You must map at least one column to 'Date'."), "danger")
1302 # Re-render the mapping page
1303 with open(tmp_path, "rb") as fh:
1304 data = fh.read()
1305 try:
1306 parsed = parse_file(data, original_filename)
1307 except ValueError:
1308 session.pop(_IMPORT_SESSION_KEY, None)
1309 return redirect(url_for("pilots.import_upload"))
1310 preview = preview_rows(parsed, mapping, n=5)
1311 return render_template(
1312 "pilots/import_map.html",
1313 norm_cols=parsed.norm_cols,
1314 raw_cols=parsed.raw_cols,
1315 base_norm_cols=[_norm(r) for r in parsed.raw_cols],
1316 mapping=mapping,
1317 match_type="alias",
1318 fuzzy_score=0.0,
1319 target_fields=TARGET_FIELDS,
1320 preview=preview,
1321 filename=original_filename,
1322 type_hints=type_hints(parsed, mapping),
1323 ), 422
1325 # Parse opening balance
1326 opening_balance: dict[str, float | None] = {}
1327 ob_fields = [
1328 "single_pilot_se",
1329 "single_pilot_me",
1330 "multi_pilot",
1331 "night_time",
1332 "instrument_time",
1333 "function_pic",
1334 "function_copilot",
1335 "function_dual",
1336 "function_instructor",
1337 ]
1338 for f_name in ob_fields:
1339 raw = request.form.get(f"ob_{f_name}", "").strip()
1340 opening_balance[f_name] = parse_duration_value(raw) if raw else None
1342 # Re-parse the temp file
1343 with open(tmp_path, "rb") as fh:
1344 data = fh.read()
1345 try:
1346 parsed = parse_file(data, original_filename)
1347 except ValueError as exc:
1348 flash(str(exc), "danger")
1349 session.pop(_IMPORT_SESSION_KEY, None)
1350 return redirect(url_for("pilots.import_upload"))
1352 # Create or reuse the mapping record
1353 saved_mappings = LogbookImportMapping.query.filter_by(pilot_user_id=uid).all()
1354 mapping_record: LogbookImportMapping | None = None
1355 for m in saved_mappings:
1356 if m.source_fingerprint == fingerprint:
1357 # Update the saved mapping with the user's potentially-refined choices
1358 m.column_mapping = json.dumps(mapping)
1359 mapping_record = m
1360 break
1361 if mapping_record is None:
1362 mapping_record = LogbookImportMapping(
1363 pilot_user_id=uid,
1364 source_fingerprint=fingerprint,
1365 column_mapping=json.dumps(mapping),
1366 source_columns=json.dumps(norm_cols),
1367 created_at=_datetime.now(UTC),
1368 )
1369 db.session.add(mapping_record)
1370 db.session.flush() # get mapping_record.id
1372 # Create the batch record (row counts filled in after execute)
1373 batch = LogbookImportBatch(
1374 pilot_user_id=uid,
1375 mapping_id=mapping_record.id,
1376 source_filename=original_filename,
1377 imported_at=_datetime.now(UTC),
1378 )
1379 db.session.add(batch)
1380 db.session.flush() # get batch.id
1382 resolved_opening_balance = (
1383 opening_balance if any(opening_balance.values()) else None
1384 )
1386 # Rows that look like they might be an edited version of an existing
1387 # entry (score >= _CANDIDATE_MIN_SCORE) need a human decision, not a
1388 # guess — carve them out of this pass and route them through the
1389 # interactive review step below instead of silently importing or
1390 # skipping them.
1391 conflicts = find_conflicting_rows(parsed, mapping, uid)
1393 result = execute_import(
1394 parsed=parsed,
1395 mapping=mapping,
1396 pilot_user_id=uid,
1397 batch_id=batch.id,
1398 opening_balance=resolved_opening_balance,
1399 skip_row_nums={c.row_num for c in conflicts},
1400 )
1402 batch.row_count = result.imported
1403 batch.subtotal_count = result.subtotals
1404 batch.skipped_count = len(result.skipped)
1405 batch.has_opening_balance = result.has_opening_balance
1407 db.session.commit()
1409 if conflicts:
1410 # Defer the aircraft-link pass and tmp-file cleanup until every
1411 # conflict is resolved — _finalize_import_review does both, covering
1412 # entries added during review as well as the ones just committed.
1413 session[_IMPORT_REVIEW_SESSION_KEY] = {
1414 "uid": uid,
1415 "tmp_path": tmp_path,
1416 "original_filename": original_filename,
1417 "mapping": mapping,
1418 "batch_id": batch.id,
1419 "resolved": {},
1420 }
1421 session.pop(_IMPORT_SESSION_KEY, None)
1422 session.modified = True
1424 flash(
1425 _(
1426 "%(imported)d entries imported so far. %(n)d rows look like they "
1427 "might already be in your logbook with different data — please "
1428 "review them below.",
1429 imported=result.imported,
1430 n=len(conflicts),
1431 ),
1432 "info",
1433 )
1434 if result.duplicates:
1435 detail = "; ".join(
1436 reason if r == 0 else f"row {r}: {reason}"
1437 for r, reason in result.duplicates[:5]
1438 )
1439 if len(result.duplicates) > 5:
1440 detail += f" … and {len(result.duplicates) - 5} more"
1441 flash(
1442 _("Rows already in your logbook, skipped: %(detail)s", detail=detail),
1443 "info",
1444 )
1445 if result.skipped:
1446 detail = "; ".join(f"row {r}: {reason}" for r, reason in result.skipped[:5])
1447 if len(result.skipped) > 5:
1448 detail += f" … and {len(result.skipped) - 5} more"
1449 flash(_("Skipped rows: %(detail)s", detail=detail), "warning")
1451 return redirect(url_for("pilots.import_review"))
1453 # Promote newly imported standalone entries to managed aircraft, where
1454 # the registration matches one.
1455 new_entries = (
1456 Flight.query.filter(
1457 Flight.import_batch_id == batch.id,
1458 Flight.aircraft_id.is_(None),
1459 Flight.other_aircraft_registration.isnot(None),
1460 )
1461 .order_by(Flight.date.asc(), Flight.id.asc())
1462 .all()
1463 )
1464 ac_created = link_entries_to_aircraft(new_entries)
1465 if ac_created > 0:
1466 db.session.commit()
1468 # Clean up temp file and session
1469 try:
1470 os.remove(tmp_path)
1471 except OSError as exc:
1472 current_app.logger.debug("cleanup tmp import file: %s", exc)
1473 session.pop(_IMPORT_SESSION_KEY, None)
1475 # Duplicates are a normal, expected outcome — e.g. re-uploading a
1476 # spreadsheet after appending a few new flights — not an error, so these
1477 # messages stay in the "success"/"info" register rather than "warning".
1478 if result.imported == 0 and result.duplicates:
1479 flash(
1480 _(
1481 "All entries in this file were already in your logbook — "
1482 "nothing new was imported."
1483 ),
1484 "success",
1485 )
1486 elif result.duplicates:
1487 flash(
1488 _(
1489 "Import complete: %(imported)d new entries imported, %(duplicates)d "
1490 "rows were already in your logbook and were skipped, %(subtotals)d "
1491 "subtotal rows skipped, %(skipped)d rows could not be parsed.",
1492 imported=result.imported,
1493 duplicates=len(result.duplicates),
1494 subtotals=result.subtotals,
1495 skipped=len(result.skipped),
1496 ),
1497 "success",
1498 )
1499 else:
1500 flash(
1501 _(
1502 "Import complete: %(imported)d entries imported, %(subtotals)d "
1503 "subtotal rows skipped, %(skipped)d rows could not be parsed.",
1504 imported=result.imported,
1505 subtotals=result.subtotals,
1506 skipped=len(result.skipped),
1507 ),
1508 "success",
1509 )
1510 if ac_created > 0:
1511 flash(
1512 ngettext(
1513 "%(n)d aircraft log entry was also created for your managed aircraft.",
1514 "%(n)d aircraft log entries were also created for your managed aircraft.",
1515 ac_created,
1516 n=ac_created,
1517 ),
1518 "info",
1519 )
1520 if result.duplicates:
1521 detail = "; ".join(
1522 reason if r == 0 else f"row {r}: {reason}"
1523 for r, reason in result.duplicates[:5]
1524 )
1525 if len(result.duplicates) > 5:
1526 detail += f" … and {len(result.duplicates) - 5} more"
1527 flash(
1528 _("Rows already in your logbook, skipped: %(detail)s", detail=detail),
1529 "info",
1530 )
1531 if result.skipped:
1532 detail = "; ".join(f"row {r}: {reason}" for r, reason in result.skipped[:5])
1533 if len(result.skipped) > 5:
1534 detail += f" … and {len(result.skipped) - 5} more"
1535 flash(_("Skipped rows: %(detail)s", detail=detail), "warning")
1537 if result.parse_warnings:
1538 n = len(result.parse_warnings)
1539 examples = "; ".join(
1540 f"row {r}, {target}: {raw}"
1541 for r, _col, target, raw in result.parse_warnings[:3]
1542 )
1543 if n > 3:
1544 examples += f" … +{n - 3}"
1545 flash(
1546 ngettext(
1547 "One cell value could not be parsed and was imported as blank: %(examples)s",
1548 "%(n)d cell values could not be parsed and were imported as blank: %(examples)s",
1549 n,
1550 n=n,
1551 examples=examples,
1552 ),
1553 "warning",
1554 )
1556 if result.total_mismatch_warnings:
1557 n = len(result.total_mismatch_warnings)
1558 examples = "; ".join(
1559 _(
1560 "row %(row)d (source %(src).1f h, computed %(comp).1f h)",
1561 row=r,
1562 src=src,
1563 comp=comp,
1564 )
1565 for r, src, comp in result.total_mismatch_warnings[:3]
1566 )
1567 if n > 3:
1568 examples += f" … +{n - 3}"
1569 flash(
1570 ngettext(
1571 "One row has a total flight time that doesn't match the sum of its components — please review: %(examples)s",
1572 "%(n)d rows have a total flight time that doesn't match the sum of their components — please review: %(examples)s",
1573 n,
1574 n=n,
1575 examples=examples,
1576 ),
1577 "warning",
1578 )
1580 return redirect(url_for("pilots.import_history"))
1583def _finalize_import_review(state: dict[str, Any]) -> ResponseReturnValue:
1584 """Common tail once every conflict row from a review has a decision:
1585 aircraft-link pass (covers both the rows auto-imported before the review
1586 started and any resolved as 'new' during it), tmp-file/session cleanup,
1587 summary flash, redirect to history — the same shape as the no-conflicts
1588 tail of import_execute above."""
1589 batch_id: int = state["batch_id"]
1590 tmp_path: str = state["tmp_path"]
1592 new_entries = (
1593 Flight.query.filter(
1594 Flight.import_batch_id == batch_id,
1595 Flight.aircraft_id.is_(None),
1596 Flight.other_aircraft_registration.isnot(None),
1597 )
1598 .order_by(Flight.date.asc(), Flight.id.asc())
1599 .all()
1600 )
1601 ac_created = link_entries_to_aircraft(new_entries)
1602 if ac_created > 0:
1603 db.session.commit()
1605 try:
1606 os.remove(tmp_path)
1607 except OSError as exc:
1608 current_app.logger.debug("cleanup tmp review file: %s", exc)
1609 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1611 resolved: dict[str, str] = state.get("resolved", {})
1612 kept = sum(1 for d in resolved.values() if d == "keep")
1613 overwritten = sum(1 for d in resolved.values() if d.startswith("overwrite:"))
1614 added_new = sum(1 for d in resolved.values() if d == "new")
1616 flash(
1617 _(
1618 "Review complete: %(overwritten)d entries updated, %(new)d imported "
1619 "as new, %(kept)d left unchanged.",
1620 overwritten=overwritten,
1621 new=added_new,
1622 kept=kept,
1623 ),
1624 "success",
1625 )
1626 if ac_created > 0:
1627 flash(
1628 ngettext(
1629 "%(n)d aircraft log entry was also created for your managed aircraft.",
1630 "%(n)d aircraft log entries were also created for your managed aircraft.",
1631 ac_created,
1632 n=ac_created,
1633 ),
1634 "info",
1635 )
1637 return redirect(url_for("pilots.import_history"))
1640@pilots_bp.route("/pilot/logbook/import/review")
1641@login_required
1642@require_pilot_access
1643def import_review() -> ResponseReturnValue:
1644 uid = _current_user_id()
1645 state = session.get(_IMPORT_REVIEW_SESSION_KEY)
1646 if not state or state.get("uid") != uid:
1647 flash(_("Import session expired. Please upload the file again."), "warning")
1648 return redirect(url_for("pilots.import_upload"))
1650 tmp_path: str = state["tmp_path"]
1651 if not os.path.isfile(tmp_path):
1652 flash(_("Temporary file not found. Please upload the file again."), "warning")
1653 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1654 return redirect(url_for("pilots.import_upload"))
1656 mapping: dict[str, str] = state["mapping"]
1657 resolved: dict[str, str] = state.get("resolved", {})
1659 with open(tmp_path, "rb") as fh:
1660 data = fh.read()
1661 try:
1662 parsed = parse_file(data, state["original_filename"])
1663 except ValueError:
1664 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1665 return redirect(url_for("pilots.import_upload"))
1667 exclude_row_nums = {int(k) for k in resolved}
1668 conflicts = find_conflicting_rows(
1669 parsed, mapping, uid, exclude_row_nums=exclude_row_nums
1670 )
1672 if not conflicts:
1673 return _finalize_import_review(state)
1675 candidate_ids = {cid for c in conflicts for _score, cid in c.candidates}
1676 candidate_entries: dict[int, Flight] = (
1677 {e.id: e for e in Flight.query.filter(Flight.id.in_(candidate_ids))}
1678 if candidate_ids
1679 else {}
1680 )
1682 rows = [
1683 {
1684 "row_num": c.row_num,
1685 "kwargs": c.kwargs,
1686 "candidates": [
1687 {"id": cid, "score": score, "entry": candidate_entries.get(cid)}
1688 for score, cid in c.candidates
1689 ],
1690 }
1691 for c in conflicts
1692 ]
1694 return render_template(
1695 "pilots/import_review.html",
1696 rows=rows,
1697 resolved_count=len(resolved),
1698 total_count=len(resolved) + len(conflicts),
1699 )
1702@pilots_bp.route("/pilot/logbook/import/review/resolve", methods=["POST"])
1703@login_required
1704@require_pilot_access
1705def import_review_resolve() -> ResponseReturnValue:
1706 uid = _current_user_id()
1707 state = session.get(_IMPORT_REVIEW_SESSION_KEY)
1708 if not state or state.get("uid") != uid:
1709 flash(_("Import session expired. Please upload the file again."), "warning")
1710 return redirect(url_for("pilots.import_upload"))
1712 tmp_path: str = state["tmp_path"]
1713 mapping: dict[str, str] = state["mapping"]
1714 resolved: dict[str, str] = state.get("resolved", {})
1716 try:
1717 row_num = int(request.form.get("row_num", ""))
1718 except (ValueError, TypeError):
1719 flash(_("Invalid row number."), "danger")
1720 return redirect(url_for("pilots.import_review"))
1722 if str(row_num) in resolved:
1723 flash(_("This row has already been resolved."), "info")
1724 return redirect(url_for("pilots.import_review"))
1726 if not os.path.isfile(tmp_path):
1727 flash(_("Temporary file not found. Please upload the file again."), "warning")
1728 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1729 return redirect(url_for("pilots.import_upload"))
1731 with open(tmp_path, "rb") as fh:
1732 data = fh.read()
1733 try:
1734 parsed = parse_file(data, state["original_filename"])
1735 except ValueError:
1736 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1737 return redirect(url_for("pilots.import_upload"))
1739 exclude_row_nums = {int(k) for k in resolved}
1740 conflicts = find_conflicting_rows(
1741 parsed, mapping, uid, exclude_row_nums=exclude_row_nums
1742 )
1743 conflict: ConflictRow | None = next(
1744 (c for c in conflicts if c.row_num == row_num), None
1745 )
1746 if conflict is None:
1747 flash(_("Invalid row number."), "danger")
1748 return redirect(url_for("pilots.import_review"))
1750 decision = request.form.get("decision", "")
1751 candidate_ids = {cid for _score, cid in conflict.candidates}
1753 if decision == "keep":
1754 pass # the freshly-parsed row is discarded; the existing entry is untouched
1755 elif decision.startswith("overwrite:"):
1756 try:
1757 existing_id = int(decision.split(":", 1)[1])
1758 except ValueError:
1759 existing_id = -1
1760 if existing_id not in candidate_ids:
1761 flash(_("Invalid selection."), "danger")
1762 return redirect(url_for("pilots.import_review"))
1763 # candidate_ids just came from find_conflicting_rows in this same
1764 # request, which already scopes candidates to rows linked to this
1765 # pilot OR unclaimed rows on this pilot's own tenant aircraft — no
1766 # need to re-check ownership here (and re-checking pic/second_crew
1767 # linkage would wrongly reject the unclaimed case, which is exactly
1768 # the row this decision is meant to claim).
1769 existing = db.session.get(Flight, existing_id)
1770 if existing is None:
1771 # candidate_ids just came from a live query in the same
1772 # request — only a concurrent delete from elsewhere reaches this.
1773 abort(404) # pragma: no cover
1774 # Full replace of every field this row provides — id, import_batch_id,
1775 # aircraft_id and gps_* links are deliberately left untouched, so this
1776 # entry stays outside the *current* batch's rollback (it wasn't
1777 # created by it) and any existing aircraft/GPS linkage survives.
1778 for field, value in conflict.kwargs.items():
1779 setattr(existing, field, value)
1780 # Crew identity isn't part of conflict.kwargs (the caller's
1781 # responsibility, per _build_entry_kwargs's docstring) — recompute
1782 # it the same way a fresh import would, replacing whatever slot
1783 # *existing* previously had (e.g. an unclaimed airframe-log row
1784 # being claimed here for the first time).
1785 ident_kwargs: dict[str, Any] = dict(conflict.kwargs)
1786 _assign_pilot_identity(ident_kwargs, uid)
1787 existing.pic_user_id = ident_kwargs.get("pic_user_id")
1788 existing.second_crew_user_id = ident_kwargs.get("second_crew_user_id")
1789 existing.second_crew_role = ident_kwargs.get("second_crew_role")
1790 db.session.commit()
1791 elif decision == "new":
1792 batch_id: int = state["batch_id"]
1793 new_kwargs = dict(conflict.kwargs)
1794 _assign_pilot_identity(new_kwargs, uid)
1795 new_kwargs["import_batch_id"] = batch_id
1796 new_kwargs["source"] = "import"
1797 db.session.add(Flight(**new_kwargs))
1798 batch = db.session.get(LogbookImportBatch, batch_id)
1799 if batch is not None:
1800 batch.row_count += 1
1801 else:
1802 current_app.logger.debug( # pragma: no cover — batch was just created
1803 "import_review_resolve: batch %s vanished before resolve", batch_id
1804 )
1805 db.session.commit()
1806 else:
1807 flash(_("Invalid decision."), "danger")
1808 return redirect(url_for("pilots.import_review"))
1810 resolved[str(row_num)] = decision
1811 state["resolved"] = resolved
1812 session[_IMPORT_REVIEW_SESSION_KEY] = state
1813 session.modified = True
1815 remaining = find_conflicting_rows(
1816 parsed, mapping, uid, exclude_row_nums={int(k) for k in resolved}
1817 )
1818 if not remaining:
1819 return _finalize_import_review(state)
1821 return redirect(url_for("pilots.import_review"))
1824@pilots_bp.route("/pilot/logbook/import/history")
1825@login_required
1826@require_pilot_access
1827def import_history() -> ResponseReturnValue:
1828 uid = _current_user_id()
1829 batches = (
1830 LogbookImportBatch.query.filter_by(pilot_user_id=uid)
1831 .order_by(LogbookImportBatch.imported_at.desc())
1832 .all()
1833 )
1834 return render_template("pilots/import_history.html", batches=batches)
1837@pilots_bp.route("/pilot/logbook/import/<int:batch_id>/rollback", methods=["POST"])
1838@login_required
1839@require_pilot_access
1840def import_rollback(batch_id: int) -> ResponseReturnValue:
1841 uid = _current_user_id()
1842 batch = db.session.get(LogbookImportBatch, batch_id)
1843 if not batch or batch.pilot_user_id != uid:
1844 abort(404)
1846 # Delete all entries belonging to this batch. Unified-model note: if any
1847 # of them were later promoted to a managed aircraft (link_entries_to_
1848 # aircraft), that's the same row now — rolling back removes the airframe
1849 # log entry too, not just the pilot's personal copy (a behaviour change
1850 # from the old two-table design, where the promoted FlightEntry was a
1851 # separate row left untouched by a pilot-side rollback).
1852 Flight.query.filter_by(import_batch_id=batch_id).delete()
1853 db.session.delete(batch)
1854 db.session.commit()
1856 flash(
1857 ngettext(
1858 "Import deleted: one entry removed.",
1859 "Import deleted: all %(count)d entries removed.",
1860 batch.row_count,
1861 count=batch.row_count,
1862 ),
1863 "success",
1864 )
1865 return redirect(url_for("pilots.import_history"))
1868# ── Pilot GPS import (airplane-agnostic batch upload) ────────────────────────
1870_GPS_ALLOWED_EXTS = {".gpx", ".kml", ".csv"}
1871_GPS_MAX_BYTES = 20 * 1024 * 1024
1872_BLOCK_TOLERANCE_PILOT = _td(minutes=15)
1875def _pilot_gps_tmp_dir() -> str:
1876 from aircraft.routes import _gps_tmp_dir
1878 return _gps_tmp_dir()
1881def _pilot_tenant_id(user_id: int) -> int | None:
1882 tu = TenantUser.query.filter_by(user_id=user_id).first()
1883 return tu.tenant_id if tu else None
1886def _pilot_match_segment(
1887 user_id: int, block_off: "_datetime", block_on: "_datetime"
1888) -> list["Flight"]:
1889 """Find Flight records the pilot is associated with that overlap block times."""
1890 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
1892 tol = _BLOCK_TOLERANCE_PILOT
1894 return Flight.query.filter( # type: ignore[no-any-return]
1895 or_(Flight.pic_user_id == user_id, Flight.second_crew_user_id == user_id),
1896 Flight.block_off_utc.isnot(None),
1897 Flight.block_on_utc.isnot(None),
1898 Flight.block_off_utc < block_on + tol,
1899 Flight.block_on_utc > block_off - tol,
1900 ).all()
1903def _pilot_fuzzy_match_segment(
1904 user_id: int, seg: dict[str, Any], block_off: "_datetime", block_on: "_datetime"
1905) -> list["Flight"]:
1906 """Fallback when no exact GPS-block overlap exists: fuzzy-match on
1907 date/route/duration against the pilot's own flights (any aircraft, or
1908 standalone) that don't yet have real GPS block data — typically a row
1909 logged manually or imported from a personal-logbook CSV. Reuses the
1910 exact same near-match scorer the pilot-logbook-import review already
1911 uses (see score_gps_candidates's docstring)."""
1912 from aircraft.gps_import import score_gps_candidates
1913 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
1915 from pilots.logbook_import import (
1916 _CANDIDATE_MIN_SCORE,
1917 _score_candidate,
1918 )
1920 kwargs = {
1921 "departure_icao": seg.get("departure_icao"),
1922 "arrival_icao": seg.get("arrival_icao"),
1923 "departure_time": block_off.time(),
1924 "arrival_time": block_on.time(),
1925 # _score_candidate sums single_pilot_se/me + multi_pilot for the
1926 # duration comparison — scoring purposes only, the confirmed
1927 # entry's real classification is decided later by pilot_role.
1928 "single_pilot_se": seg.get("flight_time_rounded_h"),
1929 }
1930 # Only rows with no real GPS block data yet — a flight that already
1931 # has its own track is a different real flight, not the one the exact
1932 # overlap check above already ruled out.
1933 same_day = Flight.query.filter(
1934 or_(Flight.pic_user_id == user_id, Flight.second_crew_user_id == user_id),
1935 Flight.date == block_off.date(),
1936 Flight.block_off_utc.is_(None),
1937 ).all()
1938 return score_gps_candidates(
1939 kwargs, same_day, _score_candidate, _CANDIDATE_MIN_SCORE
1940 )
1943def _pilot_seg_match_dict(
1944 matches: list[Any], force_ambiguous: bool = False
1945) -> dict[str, Any]:
1946 """Summarise match results into fields stored on the segment dict.
1948 force_ambiguous marks a fuzzy (non-exact) match as always needing an
1949 explicit human choice, even with just one candidate — unlike an exact
1950 GPS-block overlap, a fuzzy match is a guess and must not auto-apply.
1951 """
1952 if not matches:
1953 return {
1954 "matched_flight_id": None,
1955 "matched_flight_str": None,
1956 "matched_has_existing_track": False,
1957 "matched_aircraft_id": None,
1958 "matched_aircraft_reg": None,
1959 "matched_ambiguous": False,
1960 "matched_candidates": [],
1961 }
1962 from aircraft.routes import _gps_candidate_dict
1964 candidates = [_gps_candidate_dict(fe) for fe in matches]
1965 primary = matches[0]
1966 return {
1967 "matched_flight_id": primary.id,
1968 "matched_flight_str": candidates[0]["str"],
1969 "matched_has_existing_track": primary.gps_track_id is not None,
1970 "matched_aircraft_id": primary.aircraft_id,
1971 "matched_aircraft_reg": candidates[0]["aircraft_reg"],
1972 "matched_ambiguous": len(matches) > 1 or force_ambiguous,
1973 "matched_candidates": candidates,
1974 }
1977@pilots_bp.route("/pilot/gps-import", methods=["GET", "POST"])
1978@login_required
1979@require_pilot_access
1980def pilot_gps_import_upload() -> ResponseReturnValue:
1981 uid = _current_user_id()
1982 tenant_id = _pilot_tenant_id(uid)
1983 tenant_aircraft = (
1984 Aircraft.query.filter_by(tenant_id=tenant_id)
1985 .order_by(Aircraft.registration)
1986 .all()
1987 if tenant_id
1988 else []
1989 )
1991 if request.method == "GET":
1992 return render_template(
1993 "pilots/gps_import_upload.html",
1994 tenant_aircraft=tenant_aircraft,
1995 )
1997 # ── POST: process uploaded files ──────────────────────────────────────────
1998 mode = request.form.get("mode", "agnostic") # "one_aircraft" | "agnostic"
1999 files = request.files.getlist("gps_files")
2001 if not files or all(f.filename == "" for f in files):
2002 flash(_("Please select at least one GPS log file."), "warning")
2003 return render_template(
2004 "pilots/gps_import_upload.html", tenant_aircraft=tenant_aircraft
2005 )
2007 from aircraft.gps_import import parse_gps_file
2009 tmp_dir = _pilot_gps_tmp_dir()
2010 parsed_meta: list[dict[str, Any]] = []
2011 errors: list[str] = []
2012 skipped_empty = 0
2014 for f in files:
2015 if not f.filename:
2016 continue # pragma: no cover – Werkzeug never yields empty-filename FileStorage objects
2017 ext = os.path.splitext(f.filename.lower())[1]
2018 if ext not in _GPS_ALLOWED_EXTS:
2019 errors.append(
2020 _(
2021 "%(fn)s: unsupported file type (use .gpx, .kml, or .csv).",
2022 fn=f.filename,
2023 )
2024 )
2025 continue
2026 data = f.read(_GPS_MAX_BYTES + 1)
2027 if len(data) > _GPS_MAX_BYTES:
2028 errors.append(_("%(fn)s: file too large (20 MB limit).", fn=f.filename))
2029 continue
2030 try:
2031 parsed = parse_gps_file(data, f.filename)
2032 except ValueError as exc:
2033 errors.append(_("%(fn)s: %(err)s", fn=f.filename, err=str(exc)))
2034 continue
2035 if parsed.classification == "empty":
2036 skipped_empty += 1
2037 continue
2038 uid_hex = uuid.uuid4().hex
2039 safe_name = f"{uid_hex}_{secure_filename(f.filename)}"
2040 tmp_path = os.path.join(tmp_dir, safe_name)
2041 with open(tmp_path, "wb") as fh:
2042 fh.write(data)
2043 parsed_meta.append(
2044 {
2045 "tmp_path": tmp_path,
2046 "original_filename": f.filename,
2047 "format": parsed.format,
2048 "classification": parsed.classification,
2049 "trkpt_count": len(parsed.trackpoints),
2050 "hint_dep": parsed.hint_departure_icao,
2051 "hint_arr": parsed.hint_arrival_icao,
2052 "device_id": getattr(parsed, "device_id", None),
2053 }
2054 )
2056 for e in errors:
2057 flash(e, "danger")
2058 if skipped_empty:
2059 flash(
2060 ngettext(
2061 "%(n)s file skipped — no movement detected.",
2062 "%(n)s files skipped — no movement detected.",
2063 skipped_empty,
2064 n=skipped_empty,
2065 ),
2066 "info",
2067 )
2068 if not parsed_meta:
2069 flash(_("No valid GPS files to import."), "warning")
2070 return render_template(
2071 "pilots/gps_import_upload.html", tenant_aircraft=tenant_aircraft
2072 )
2074 if mode == "one_aircraft":
2075 aircraft_id = request.form.get("aircraft_id", type=int)
2076 if not aircraft_id:
2077 flash(_("Please select an aircraft."), "warning")
2078 return render_template(
2079 "pilots/gps_import_upload.html", tenant_aircraft=tenant_aircraft
2080 )
2081 session["gps_import"] = {
2082 "user_id": session["user_id"],
2083 "aircraft_id": aircraft_id,
2084 "files": parsed_meta,
2085 "skipped_empty": skipped_empty,
2086 "other_aircraft": False,
2087 "other_ac_make_model": "",
2088 "other_ac_reg": "",
2089 }
2090 session.modified = True
2091 return redirect(url_for("aircraft.gps_import_review", aircraft_id=aircraft_id))
2093 # agnostic mode
2094 session["pilot_gps_import"] = {
2095 "user_id": session["user_id"],
2096 "files": parsed_meta,
2097 "skipped_empty": skipped_empty,
2098 }
2099 session.modified = True
2100 return redirect(url_for("pilots.pilot_gps_import_review"))
2103@pilots_bp.route("/pilot/gps-import/review", methods=["GET"])
2104@login_required
2105@require_pilot_access
2106def pilot_gps_import_review() -> ResponseReturnValue:
2107 uid = _current_user_id()
2108 state = session.get("pilot_gps_import")
2109 if not state:
2110 flash(_("Session expired — please upload your GPS files again."), "warning")
2111 return redirect(url_for("pilots.pilot_gps_import_upload"))
2113 from aircraft.gps_import import (
2114 detect_segments,
2115 merge_and_sort,
2116 parse_gps_file,
2117 )
2118 from aircraft.routes import (
2119 _gps_tmp_dir,
2120 _linked_pilot_entries,
2121 _segment_for_session,
2122 _segment_to_dict,
2123 )
2125 file_metas = state["files"]
2126 all_parsed = []
2127 for meta in file_metas:
2128 try:
2129 with open(meta["tmp_path"], "rb") as fh:
2130 data = fh.read()
2131 parsed = parse_gps_file(data, meta["original_filename"])
2132 parsed.hint_departure_icao = meta.get("hint_dep")
2133 parsed.hint_arrival_icao = meta.get("hint_arr")
2134 all_parsed.append(parsed)
2135 except (OSError, ValueError):
2136 flash(
2137 _(
2138 "Could not read %(fn)s — please upload again.",
2139 fn=meta["original_filename"],
2140 ),
2141 "warning",
2142 )
2143 return redirect(url_for("pilots.pilot_gps_import_upload"))
2145 merged = merge_and_sort(all_parsed)
2146 hint_dep = next(
2147 (p.hint_departure_icao for p in all_parsed if p.hint_departure_icao), None
2148 )
2149 hint_arr = next(
2150 (p.hint_arrival_icao for p in all_parsed if p.hint_arrival_icao), None
2151 )
2152 segments = detect_segments(merged, hint_dep=hint_dep, hint_arr=hint_arr)
2153 full_segs = [_segment_to_dict(seg, i) for i, seg in enumerate(segments)]
2155 for seg in full_segs:
2156 block_off = _datetime.fromisoformat(seg["block_off_utc"])
2157 block_on = _datetime.fromisoformat(seg["block_on_utc"])
2158 matches = _pilot_match_segment(uid, block_off, block_on)
2159 is_fuzzy = False
2160 if not matches:
2161 matches = _pilot_fuzzy_match_segment(uid, seg, block_off, block_on)
2162 is_fuzzy = bool(matches)
2163 seg.update(_pilot_seg_match_dict(matches, force_ambiguous=is_fuzzy))
2164 if seg.get("matched_flight_id") and not seg.get("matched_ambiguous"):
2165 seg["linked_pilot_entries"] = _linked_pilot_entries(
2166 seg["matched_flight_id"], uid
2167 )
2168 else:
2169 seg["linked_pilot_entries"] = []
2171 tmp_dir = _gps_tmp_dir()
2172 state["segments"] = [_segment_for_session(s, tmp_dir) for s in full_segs]
2173 session["pilot_gps_import"] = state
2174 session.modified = True
2176 tenant_id = _pilot_tenant_id(uid)
2177 tenant_aircraft = (
2178 Aircraft.query.filter_by(tenant_id=tenant_id)
2179 .order_by(Aircraft.registration)
2180 .all()
2181 if tenant_id
2182 else []
2183 )
2185 from models import (
2186 AppSetting, # pyright: ignore[reportMissingImports]
2187 )
2189 tile_setting = db.session.get(AppSetting, "openaip_api_key")
2190 openaip_key = tile_setting.value if tile_setting and tile_setting.value else None
2192 return render_template(
2193 "pilots/gps_import_review.html",
2194 segments=full_segs,
2195 skipped_empty=state.get("skipped_empty", 0),
2196 confirmed_segments=state.get("confirmed_segments", {}),
2197 tenant_aircraft=tenant_aircraft,
2198 openaip_key=openaip_key,
2199 )
2202@pilots_bp.route("/pilot/gps-import/confirm-one", methods=["POST"])
2203@login_required
2204@require_pilot_access
2205def pilot_gps_import_confirm_one() -> ResponseReturnValue:
2206 import decimal as _dec
2208 from aircraft.gps_import import round_flight_time
2209 from aircraft.routes import _gps_cleanup, _load_segment_geojson
2211 uid = _current_user_id()
2212 state = session.get("pilot_gps_import")
2213 if not state:
2214 flash(_("Session expired — please upload your GPS files again."), "warning")
2215 return redirect(url_for("pilots.pilot_gps_import_upload"))
2217 segments_data: list[dict[str, Any]] = state.get("segments", [])
2218 if not segments_data:
2219 flash(_("No segments to import."), "warning")
2220 return redirect(url_for("pilots.pilot_gps_import_upload"))
2222 try:
2223 seg_idx = int(request.form.get("seg_idx", ""))
2224 except (ValueError, TypeError):
2225 flash(_("Invalid segment index."), "danger")
2226 return redirect(url_for("pilots.pilot_gps_import_review"))
2228 if seg_idx < 0 or seg_idx >= len(segments_data):
2229 flash(_("Invalid segment index."), "danger")
2230 return redirect(url_for("pilots.pilot_gps_import_review"))
2232 confirmed = state.get("confirmed_segments", {})
2233 if str(seg_idx) in confirmed:
2234 flash(_("This segment has already been confirmed."), "info")
2235 return redirect(url_for("pilots.pilot_gps_import_review"))
2237 pilot_role = request.form.get("pilot_role", "pic")
2238 if pilot_role not in ("pic", "dual", "none"):
2239 pilot_role = "pic"
2241 # ── Skip ─────────────────────────────────────────────────────────────────
2242 if request.form.get("skip") == "1":
2243 confirmed[str(seg_idx)] = "skip"
2244 state["confirmed_segments"] = confirmed
2245 session["pilot_gps_import"] = state
2246 session.modified = True
2247 if len(confirmed) == len(segments_data):
2248 _gps_cleanup(state)
2249 session.pop("pilot_gps_import", None)
2250 imported = sum(1 for v in confirmed.values() if v != "skip")
2251 skipped_count = len(segments_data) - imported
2252 if imported > 0:
2253 flash(
2254 ngettext(
2255 "%(n)s flight imported successfully.",
2256 "%(n)s flights imported successfully.",
2257 imported,
2258 n=imported,
2259 ),
2260 "success",
2261 )
2262 flash(
2263 ngettext(
2264 "%(n)s segment skipped.",
2265 "%(n)s segments skipped.",
2266 skipped_count,
2267 n=skipped_count,
2268 ),
2269 "info",
2270 )
2271 if imported > 0 and pilot_role in ("pic", "dual"):
2272 return redirect(url_for("pilots.logbook"))
2273 return redirect(url_for("pilots.logbook"))
2274 flash(_("Segment skipped."), "info")
2275 return redirect(url_for("pilots.pilot_gps_import_review"))
2277 # ── Confirm ───────────────────────────────────────────────────────────────
2278 seg = segments_data[seg_idx]
2279 file_metas = state.get("files", [])
2280 block_off = _datetime.fromisoformat(seg["block_off_utc"])
2281 block_on = _datetime.fromisoformat(seg["block_on_utc"])
2283 dep_icao = (
2284 request.form.get("dep_icao") or seg.get("departure_icao") or ""
2285 ).strip().upper()[:4] or "????"
2286 arr_icao = (
2287 request.form.get("arr_icao") or seg.get("arrival_icao") or ""
2288 ).strip().upper()[:4] or "????"
2289 nature = (request.form.get("nature") or "").strip()[:100] or None
2290 remarks = (request.form.get("remarks") or "").strip() or None
2292 geojson = _load_segment_geojson(seg)
2293 source_filename = (
2294 file_metas[0]["original_filename"] if len(file_metas) == 1 else None
2295 )
2296 device_id = next(
2297 (m.get("device_id") for m in file_metas if m.get("device_id")), None
2298 )
2300 create_pilot_entry = pilot_role in ("pic", "dual")
2301 if seg.get("matched_ambiguous"):
2302 # A candidate picker was rendered — respect the human's explicit
2303 # choice (including "none of these", submitted as "") instead of
2304 # always falling back to the top-scored candidate.
2305 picked = request.form.get("matched_flight_id", "")
2306 matched_flight_id = int(picked) if picked.strip().isdigit() else None
2307 else:
2308 matched_flight_id = seg.get("matched_flight_id")
2309 entry: Flight | None = None
2310 gps_track: GpsTrack | None = None
2311 ac: Aircraft | None = None
2313 if matched_flight_id:
2314 # Link GPS track to the existing matched Flight
2315 existing = db.session.get(Flight, matched_flight_id)
2316 if existing:
2317 old_track_id = existing.gps_track_id
2318 gps_track = GpsTrack(
2319 source_filename=source_filename,
2320 device_id=device_id,
2321 block_off_utc=block_off,
2322 block_on_utc=block_on,
2323 departure_icao=dep_icao,
2324 arrival_icao=arr_icao,
2325 geojson=geojson,
2326 )
2327 db.session.add(gps_track)
2328 db.session.flush()
2329 existing.gps_track_id = gps_track.id
2330 existing.block_off_utc = block_off
2331 existing.block_on_utc = block_on
2332 if old_track_id and old_track_id != gps_track.id:
2333 # Re-confirming an already-linked match (the review page
2334 # warns this replaces the track) — drop the superseded
2335 # row instead of leaving it as a permanent orphan.
2336 old_track = db.session.get(GpsTrack, old_track_id)
2337 if old_track is not None:
2338 db.session.delete(old_track)
2339 # Unified model: this Flight row already covers both crew slots
2340 # (pic_user_id/second_crew_user_id), so setting gps_track_id
2341 # above already applies to whichever other pilot occupies the
2342 # other slot too — no separate per-pilot row left to update.
2343 db.session.flush()
2344 entry = existing
2345 else:
2346 matched_flight_id = None # stale — fall through to new entry
2348 if not matched_flight_id:
2349 resolution = request.form.get("resolution", "other_aircraft")
2351 if resolution == "managed_aircraft":
2352 aircraft_id = request.form.get("aircraft_id", type=int)
2353 if aircraft_id:
2354 tenant_id = _pilot_tenant_id(uid)
2355 ac = (
2356 Aircraft.query.filter_by(
2357 id=aircraft_id, tenant_id=tenant_id
2358 ).first()
2359 if tenant_id
2360 else None
2361 )
2363 if ac:
2364 # Create a new Flight for the managed aircraft
2365 gps_track = GpsTrack(
2366 source_filename=source_filename,
2367 device_id=device_id,
2368 block_off_utc=block_off,
2369 block_on_utc=block_on,
2370 departure_icao=dep_icao,
2371 arrival_icao=arr_icao,
2372 geojson=geojson,
2373 )
2374 db.session.add(gps_track)
2375 db.session.flush()
2376 flight_time_h = round_flight_time(
2377 seg.get("flight_time_raw_h", 0),
2378 getattr(ac, "logbook_time_precision", "tenth_hour"),
2379 )
2380 entry = Flight(
2381 aircraft_id=ac.id,
2382 date=block_off.date(),
2383 departure_icao=dep_icao,
2384 arrival_icao=arr_icao,
2385 departure_time=block_off.time().replace(tzinfo=None),
2386 arrival_time=block_on.time().replace(tzinfo=None),
2387 flight_time=_dec.Decimal(str(flight_time_h)),
2388 landing_count=seg.get("landing_count") or 0,
2389 nature_of_flight=nature,
2390 source="gps_import",
2391 block_off_utc=block_off,
2392 block_on_utc=block_on,
2393 gps_track_id=gps_track.id,
2394 )
2395 db.session.add(entry)
2396 db.session.flush()
2397 else:
2398 # Other / external aircraft — pilot-only, no Flight
2399 if geojson:
2400 gps_track = GpsTrack(
2401 source_filename=source_filename,
2402 device_id=device_id,
2403 block_off_utc=block_off,
2404 block_on_utc=block_on,
2405 departure_icao=dep_icao,
2406 arrival_icao=arr_icao,
2407 geojson=geojson,
2408 )
2409 db.session.add(gps_track)
2410 db.session.flush()
2411 create_pilot_entry = (
2412 True # always create logbook entry for external aircraft
2413 )
2415 if create_pilot_entry:
2416 from flights.routes import apply_pilot_identity
2418 flight_time_h = round_flight_time(seg.get("flight_time_raw_h", 0), "tenth_hour")
2420 if entry is None:
2421 # Other/external aircraft, no existing match — standalone row.
2422 other_reg = (request.form.get("other_reg") or "").strip().upper()
2423 other_mm = (request.form.get("other_make_model") or "").strip()
2424 entry = Flight(
2425 date=block_off.date(),
2426 other_aircraft_type=other_mm or None,
2427 other_aircraft_registration=other_reg or None,
2428 departure_icao=dep_icao,
2429 departure_time=block_off.time().replace(tzinfo=None),
2430 arrival_icao=arr_icao,
2431 arrival_time=block_on.time().replace(tzinfo=None),
2432 source="gps_import",
2433 gps_track_id=gps_track.id if gps_track else None,
2434 )
2435 db.session.add(entry)
2436 db.session.flush()
2438 entry.flight_time = _dec.Decimal(str(flight_time_h))
2439 # GPS-derived landing count has no day/night split — treat them all
2440 # as day landings, same simplification the old standalone pilot
2441 # entry made.
2442 entry.landings_day = seg.get("landing_count") or 0
2443 if remarks:
2444 entry.notes = remarks
2445 entry_ac: Aircraft | None = (
2446 ac if ac is not None else entry.aircraft # type: ignore[assignment]
2447 )
2448 # apply_pilot_identity only knows "pic"/"dual" — an external-aircraft
2449 # segment always gets a personal entry recorded (create_pilot_entry
2450 # is forced True above) even when pilot_role is "none"; default that
2451 # case to "pic" rather than skipping identity resolution entirely.
2452 apply_pilot_identity(
2453 entry,
2454 entry_ac,
2455 uid,
2456 pilot_role if pilot_role != "none" else "pic",
2457 )
2459 db.session.commit()
2461 confirmed[str(seg_idx)] = entry.id if entry else 0
2462 state["confirmed_segments"] = confirmed
2463 session["pilot_gps_import"] = state
2464 session.modified = True
2466 all_handled = len(confirmed) == len(segments_data)
2467 if all_handled:
2468 _gps_cleanup(state)
2469 session.pop("pilot_gps_import", None)
2470 total = sum(1 for v in confirmed.values() if v != "skip")
2471 flash(
2472 ngettext(
2473 "%(n)s flight imported successfully.",
2474 "%(n)s flights imported successfully.",
2475 total,
2476 n=total,
2477 ),
2478 "success",
2479 )
2480 return redirect(url_for("pilots.logbook"))
2482 flash(_("Flight confirmed."), "success")
2483 return redirect(url_for("pilots.pilot_gps_import_review"))