Coverage for app/pilots/routes.py: 100%
1271 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-17 15:29 +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 flights.crew_invites import ( # pyright: ignore[reportMissingImports]
36 pending_claims_to_review,
37 pending_invites_for_user,
38)
39from flights.crew_removal import ( # pyright: ignore[reportMissingImports]
40 remove_flight_for_user,
41)
42from flights.shared_flight import ( # pyright: ignore[reportMissingImports]
43 can_edit_shared,
44 notify_shared_changes,
45 pending_suggestions_to_review,
46 protect_other_pilots,
47 restore_other_pilots,
48 shared_snapshot,
49)
50from models import ( # pyright: ignore[reportMissingImports]
51 Aircraft,
52 CrewRole,
53 Document,
54 Flight,
55 FstdType,
56 GpsTrack,
57 LogbookEntryType,
58 LogbookImportBatch,
59 LogbookImportMapping,
60 PersonalMinimumsItem,
61 PersonalMinimumsRevision,
62 PersonalMinimumsSection,
63 PersonalMinimumsStatus,
64 PersonalMinimumsTag,
65 PilotProfile,
66 Reservation,
67 ReservationStatus,
68 TenantUser,
69 db,
70)
71from sqlalchemy import func # pyright: ignore[reportMissingImports]
72from utils import ( # pyright: ignore[reportMissingImports]
73 current_user_role,
74 login_required,
75 require_pilot_access,
76 tenant_pilot_names,
77 user_can_access_aircraft,
78)
79from werkzeug.utils import secure_filename # pyright: ignore[reportMissingImports]
81from pilots.form_parsing import ( # pyright: ignore[reportMissingImports]
82 _parse_date,
83 apply_pilot_fields,
84 parse_pilot_fields,
85)
86from pilots.logbook_import import ( # pyright: ignore[reportMissingImports]
87 TARGET_FIELDS,
88 ConflictRow,
89 _assign_pilot_identity,
90 _norm,
91 execute_import,
92 find_conflicting_rows,
93 link_entries_to_aircraft,
94 merge_into_existing,
95 parse_duration_value,
96 parse_file,
97 preview_rows,
98 propose_mapping,
99 split_auto_merges,
100 type_hints,
101)
102from pilots.personal_minimums import ( # pyright: ignore[reportMissingImports]
103 STARTERS,
104 get_active_revision,
105 recency_breaches,
106)
108log = logging.getLogger(__name__)
110pilots_bp = Blueprint("pilots", __name__)
113def _current_user_id() -> int:
114 return int(session["user_id"])
117def _openaip_key() -> str | None:
118 from models import AppSetting # pyright: ignore[reportMissingImports]
120 s = db.session.get(AppSetting, "openaip_api_key")
121 return s.value if s and s.value else None
124def _get_or_create_profile(user_id: int) -> PilotProfile:
125 profile: PilotProfile | None = PilotProfile.query.filter_by(user_id=user_id).first()
126 if not profile:
127 profile = PilotProfile(user_id=user_id)
128 db.session.add(profile)
129 db.session.flush()
130 return profile
133def _my_entries_query(uid: int): # type: ignore[no-untyped-def]
134 """Base query for `Flight` rows this pilot occupies (either identity
135 slot) — the "my logbook" query, used throughout this module."""
136 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
138 return Flight.query.filter(
139 or_(Flight.pic_user_id == uid, Flight.second_crew_user_id == uid)
140 )
143def _check_logbook_milestone(entry: Flight, uid: int) -> None:
144 """Set one-shot session flags when a logbook milestone is crossed."""
145 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
147 mine = or_(Flight.pic_user_id == uid, Flight.second_crew_user_id == uid)
148 total = Flight.query.filter(mine).count()
149 if total == 100:
150 session["logbook_milestone"] = "100flights"
151 flash(_("🎉 100th logbook entry — congratulations!"), "success")
152 return
154 night = float(entry.night_time or 0)
155 if night > 0:
156 prev_night = (
157 db.session.query(func.count(Flight.id))
158 .filter(mine, Flight.id != entry.id, Flight.night_time > 0)
159 .scalar()
160 or 0
161 )
162 if prev_night == 0:
163 session["logbook_milestone"] = "first_night"
164 flash(_("🌙 First night flight logged — well done!"), "success")
165 return
167 dep = (entry.departure_icao or "").strip().upper()
168 arr = (entry.arrival_icao or "").strip().upper()
169 if dep and arr and dep != arr:
170 prev_xc = (
171 db.session.query(func.count(Flight.id))
172 .filter(
173 mine,
174 Flight.id != entry.id,
175 Flight.departure_icao.isnot(None),
176 Flight.arrival_icao.isnot(None),
177 Flight.departure_icao != Flight.arrival_icao,
178 )
179 .scalar()
180 or 0
181 )
182 if prev_xc == 0:
183 session["logbook_milestone"] = "first_xc"
184 flash(
185 _("✈️ First cross-country flight logged — congratulations!"), "success"
186 )
189# ── Profile ───────────────────────────────────────────────────────────────────
192@pilots_bp.route("/pilot/profile", methods=["GET", "POST"])
193@login_required
194@require_pilot_access
195def profile() -> ResponseReturnValue:
196 uid = _current_user_id()
197 p = _get_or_create_profile(uid)
199 if request.method == "POST":
200 errors = []
202 p.license_number = request.form.get("license_number", "").strip() or None
204 medical_str = request.form.get("medical_expiry", "")
205 medical, err = _parse_date(medical_str, "Medical expiry")
206 if err:
207 errors.append(err)
208 else:
209 p.medical_expiry = medical
211 sep_str = request.form.get("sep_expiry", "")
212 sep, err = _parse_date(sep_str, "SEP expiry")
213 if err:
214 errors.append(err)
215 else:
216 p.sep_expiry = sep
218 solo_str = request.form.get("first_solo_date", "")
219 solo, err = _parse_date(solo_str, _("First solo date"))
220 if err:
221 errors.append(err)
222 else:
223 p.first_solo_date = solo
225 ppl_str = request.form.get("ppl_issue_date", "")
226 ppl, err = _parse_date(ppl_str, _("PPL issue date"))
227 if err:
228 errors.append(err)
229 else:
230 p.ppl_issue_date = ppl
232 if errors:
233 for e in errors:
234 flash(e, "danger")
235 return (
236 render_template(
237 "pilots/profile.html", profile=p, pilot_docs=[], currency=None
238 ),
239 422,
240 )
242 db.session.commit()
243 flash(_("Profile saved."), "success")
244 return redirect(url_for("pilots.profile"))
246 from pilots.currency import (
247 currency_summary as _currency_summary, # pyright: ignore[reportMissingImports]
248 )
250 pilot_entries = _my_entries_query(uid).all()
251 currency = _currency_summary(p, pilot_entries)
253 pilot_docs = (
254 Document.query.filter_by(pilot_user_id=uid)
255 .order_by(Document.uploaded_at.desc())
256 .all()
257 )
258 return render_template(
259 "pilots/profile.html", profile=p, pilot_docs=pilot_docs, currency=currency
260 )
263_VALID_PER_PAGE = (10, 20, 50, 100)
264_DEFAULT_PER_PAGE = 20
267# ── Personal minimums ─────────────────────────────────────────────────────────
270def _active_minimums_revision(uid: int) -> PersonalMinimumsRevision | None:
271 return get_active_revision(uid)
274def _draft_minimums_revision(uid: int) -> PersonalMinimumsRevision | None:
275 revision: PersonalMinimumsRevision | None = (
276 PersonalMinimumsRevision.query.filter_by(
277 user_id=uid, status=PersonalMinimumsStatus.DRAFT
278 ).first()
279 )
280 return revision
283def _get_own_revision_or_404(revision_id: int, uid: int) -> PersonalMinimumsRevision:
284 revision = db.session.get(PersonalMinimumsRevision, revision_id)
285 if revision is None or revision.user_id != uid:
286 abort(404)
287 return revision
290def _get_own_draft_section_or_404(section_id: int, uid: int) -> PersonalMinimumsSection:
291 section = db.session.get(PersonalMinimumsSection, section_id)
292 if (
293 section is None
294 or section.revision.user_id != uid
295 or section.revision.status != PersonalMinimumsStatus.DRAFT
296 ):
297 abort(404)
298 return section
301def _get_own_draft_item_or_404(item_id: int, uid: int) -> PersonalMinimumsItem:
302 item = db.session.get(PersonalMinimumsItem, item_id)
303 if (
304 item is None
305 or item.section.revision.user_id != uid
306 or item.section.revision.status != PersonalMinimumsStatus.DRAFT
307 ):
308 abort(404)
309 return item
312def _flew_or_reserved_today(uid: int) -> bool:
313 today = _date.today()
314 if _my_entries_query(uid).filter(Flight.date == today).first():
315 return True
316 reservation = Reservation.query.filter(
317 Reservation.pilot_user_id == uid,
318 Reservation.status.in_(
319 [ReservationStatus.PENDING, ReservationStatus.CONFIRMED]
320 ),
321 db.func.date(Reservation.start_dt) <= today,
322 db.func.date(Reservation.end_dt) >= today,
323 ).first()
324 return reservation is not None
327@pilots_bp.route("/pilot/minimums")
328@login_required
329@require_pilot_access
330def minimums_view() -> ResponseReturnValue:
331 uid = _current_user_id()
332 active = _active_minimums_revision(uid)
333 if active is not None:
334 return render_template(
335 "pilots/minimums_view.html", revision=active, is_own_draft=False
336 )
337 if _draft_minimums_revision(uid) is not None:
338 return redirect(url_for("pilots.minimums_edit"))
339 return render_template("pilots/minimums_start.html")
342@pilots_bp.route("/pilot/minimums/create", methods=["POST"])
343@login_required
344@require_pilot_access
345def minimums_create() -> ResponseReturnValue:
346 uid = _current_user_id()
347 if _draft_minimums_revision(uid) is not None:
348 flash(_("You already have a draft in progress."), "warning")
349 return redirect(url_for("pilots.minimums_edit"))
350 starter = request.form.get("starter", "blank")
351 if starter not in ("blank", "light", "full"):
352 starter = "blank"
353 next_number = (
354 db.session.query(db.func.max(PersonalMinimumsRevision.revision_number))
355 .filter_by(user_id=uid)
356 .scalar()
357 or 0
358 ) + 1
359 revision = PersonalMinimumsRevision(
360 user_id=uid,
361 revision_number=next_number,
362 status=PersonalMinimumsStatus.DRAFT,
363 )
364 db.session.add(revision)
365 db.session.flush()
366 if starter in STARTERS:
367 for s_order, (title, items) in enumerate(STARTERS[starter]):
368 section = PersonalMinimumsSection(
369 revision_id=revision.id, title=str(title), sort_order=s_order
370 )
371 db.session.add(section)
372 db.session.flush()
373 for i_order, (label, tag) in enumerate(items):
374 db.session.add(
375 PersonalMinimumsItem(
376 section_id=section.id,
377 label=str(label),
378 value="",
379 semantic_tag=tag,
380 sort_order=i_order,
381 )
382 )
383 db.session.commit()
384 flash(_("Draft created."), "success")
385 return redirect(url_for("pilots.minimums_edit"))
388@pilots_bp.route("/pilot/minimums/history")
389@login_required
390@require_pilot_access
391def minimums_history() -> ResponseReturnValue:
392 uid = _current_user_id()
393 revisions = (
394 PersonalMinimumsRevision.query.filter_by(user_id=uid)
395 .order_by(PersonalMinimumsRevision.revision_number.desc())
396 .all()
397 )
398 return render_template("pilots/minimums_history.html", revisions=revisions)
401@pilots_bp.route("/pilot/minimums/revision/<int:revision_id>")
402@login_required
403@require_pilot_access
404def minimums_revision_detail(revision_id: int) -> ResponseReturnValue:
405 uid = _current_user_id()
406 revision = _get_own_revision_or_404(revision_id, uid)
407 return render_template(
408 "pilots/minimums_view.html",
409 revision=revision,
410 is_own_draft=(revision.status == PersonalMinimumsStatus.DRAFT),
411 )
414@pilots_bp.route("/pilot/minimums/revise", methods=["POST"])
415@login_required
416@require_pilot_access
417def minimums_revise() -> ResponseReturnValue:
418 uid = _current_user_id()
419 active = _active_minimums_revision(uid)
420 if active is None:
421 flash(_("No active revision to revise yet."), "danger")
422 return redirect(url_for("pilots.minimums_view"))
423 if _draft_minimums_revision(uid) is not None:
424 flash(_("You already have a draft in progress."), "warning")
425 return redirect(url_for("pilots.minimums_edit"))
426 next_number = (
427 db.session.query(db.func.max(PersonalMinimumsRevision.revision_number))
428 .filter_by(user_id=uid)
429 .scalar()
430 or 0
431 ) + 1
432 draft = PersonalMinimumsRevision(
433 user_id=uid,
434 revision_number=next_number,
435 status=PersonalMinimumsStatus.DRAFT,
436 )
437 db.session.add(draft)
438 db.session.flush()
439 for section in active.sections: # type: ignore[attr-defined]
440 new_section = PersonalMinimumsSection(
441 revision_id=draft.id, title=section.title, sort_order=section.sort_order
442 )
443 db.session.add(new_section)
444 db.session.flush()
445 for item in section.items:
446 db.session.add(
447 PersonalMinimumsItem(
448 section_id=new_section.id,
449 label=item.label,
450 value=item.value,
451 semantic_tag=item.semantic_tag,
452 numeric_value=item.numeric_value,
453 sort_order=item.sort_order,
454 )
455 )
456 db.session.commit()
457 flash(_("Draft created from your active revision."), "success")
458 return redirect(url_for("pilots.minimums_edit"))
461@pilots_bp.route("/pilot/minimums/edit")
462@login_required
463@require_pilot_access
464def minimums_edit() -> ResponseReturnValue:
465 uid = _current_user_id()
466 draft = _draft_minimums_revision(uid)
467 if draft is None:
468 return redirect(url_for("pilots.minimums_view"))
469 return render_template(
470 "pilots/minimums_edit.html", revision=draft, tags=PersonalMinimumsTag
471 )
474def _validate_tag_and_numeric(
475 tag_raw: str, numeric_raw: str
476) -> tuple[str | None, float | None, str | None]:
477 """Return (tag_or_None, numeric_value_or_None, error_or_None)."""
478 tag = tag_raw or None
479 if tag and tag not in PersonalMinimumsTag.ALL:
480 return None, None, str(_("Unrecognized tag."))
481 if tag:
482 try:
483 numeric_value = float(numeric_raw)
484 if not math.isfinite(numeric_value):
485 raise ValueError
486 except ValueError:
487 return None, None, str(_("This tag requires a numeric value."))
488 return tag, numeric_value, None
489 return None, None, None
492@pilots_bp.route("/pilot/minimums/section/add", methods=["POST"])
493@login_required
494@require_pilot_access
495def minimums_section_add() -> ResponseReturnValue:
496 uid = _current_user_id()
497 draft = _draft_minimums_revision(uid)
498 if draft is None:
499 abort(404)
500 title = request.form.get("title", "").strip()
501 if not title:
502 flash(_("Section title is required."), "danger")
503 return redirect(url_for("pilots.minimums_edit"))
504 max_order = (
505 db.session.query(db.func.max(PersonalMinimumsSection.sort_order))
506 .filter_by(revision_id=draft.id)
507 .scalar()
508 )
509 next_order = 0 if max_order is None else max_order + 1
510 db.session.add(
511 PersonalMinimumsSection(
512 revision_id=draft.id, title=title, sort_order=next_order
513 )
514 )
515 db.session.commit()
516 flash(_("Section added."), "success")
517 return redirect(url_for("pilots.minimums_edit"))
520@pilots_bp.route("/pilot/minimums/section/<int:section_id>/edit", methods=["POST"])
521@login_required
522@require_pilot_access
523def minimums_section_edit(section_id: int) -> ResponseReturnValue:
524 uid = _current_user_id()
525 section = _get_own_draft_section_or_404(section_id, uid)
526 title = request.form.get("title", "").strip()
527 if not title:
528 flash(_("Section title is required."), "danger")
529 return redirect(url_for("pilots.minimums_edit"))
530 section.title = title
531 db.session.commit()
532 flash(_("Section updated."), "success")
533 return redirect(url_for("pilots.minimums_edit"))
536@pilots_bp.route("/pilot/minimums/section/<int:section_id>/delete", methods=["POST"])
537@login_required
538@require_pilot_access
539def minimums_section_delete(section_id: int) -> ResponseReturnValue:
540 uid = _current_user_id()
541 section = _get_own_draft_section_or_404(section_id, uid)
542 db.session.delete(section)
543 db.session.commit()
544 flash(_("Section removed."), "success")
545 return redirect(url_for("pilots.minimums_edit"))
548def _move_sort_order(
549 model: Any, obj: Any, scope_field: str, scope_value: int, direction: str
550) -> None:
551 """Swap obj.sort_order with its neighbour within the given scope."""
552 siblings = (
553 model.query.filter_by(**{scope_field: scope_value})
554 .order_by(model.sort_order)
555 .all()
556 )
557 idx = next(i for i, s in enumerate(siblings) if s.id == obj.id)
558 neighbour_idx = idx - 1 if direction == "up" else idx + 1
559 if neighbour_idx < 0 or neighbour_idx >= len(siblings):
560 return
561 neighbour = siblings[neighbour_idx]
562 obj.sort_order, neighbour.sort_order = neighbour.sort_order, obj.sort_order
565@pilots_bp.route("/pilot/minimums/section/<int:section_id>/move-up", methods=["POST"])
566@login_required
567@require_pilot_access
568def minimums_section_move_up(section_id: int) -> ResponseReturnValue:
569 uid = _current_user_id()
570 section = _get_own_draft_section_or_404(section_id, uid)
571 _move_sort_order(
572 PersonalMinimumsSection, section, "revision_id", section.revision_id, "up"
573 )
574 db.session.commit()
575 return redirect(url_for("pilots.minimums_edit"))
578@pilots_bp.route("/pilot/minimums/section/<int:section_id>/move-down", methods=["POST"])
579@login_required
580@require_pilot_access
581def minimums_section_move_down(section_id: int) -> ResponseReturnValue:
582 uid = _current_user_id()
583 section = _get_own_draft_section_or_404(section_id, uid)
584 _move_sort_order(
585 PersonalMinimumsSection, section, "revision_id", section.revision_id, "down"
586 )
587 db.session.commit()
588 return redirect(url_for("pilots.minimums_edit"))
591@pilots_bp.route("/pilot/minimums/item/add", methods=["POST"])
592@login_required
593@require_pilot_access
594def minimums_item_add() -> ResponseReturnValue:
595 uid = _current_user_id()
596 section_id = request.form.get("section_id", type=int)
597 section = _get_own_draft_section_or_404(section_id, uid) if section_id else None
598 if section is None:
599 abort(404)
600 label = request.form.get("label", "").strip()
601 value = request.form.get("value", "").strip()
602 tag_raw = request.form.get("tag", "").strip()
603 numeric_raw = request.form.get("numeric_value", "").strip()
604 if not label:
605 flash(_("Item label is required."), "danger")
606 return redirect(url_for("pilots.minimums_edit"))
607 tag, numeric_value, error = _validate_tag_and_numeric(tag_raw, numeric_raw)
608 if error:
609 flash(error, "danger")
610 return redirect(url_for("pilots.minimums_edit"))
611 max_order = (
612 db.session.query(db.func.max(PersonalMinimumsItem.sort_order))
613 .filter_by(section_id=section.id)
614 .scalar()
615 )
616 next_order = 0 if max_order is None else max_order + 1
617 db.session.add(
618 PersonalMinimumsItem(
619 section_id=section.id,
620 label=label,
621 value=value or None,
622 semantic_tag=tag,
623 numeric_value=numeric_value,
624 sort_order=next_order,
625 )
626 )
627 db.session.commit()
628 flash(_("Item added."), "success")
629 return redirect(url_for("pilots.minimums_edit"))
632@pilots_bp.route("/pilot/minimums/item/<int:item_id>/edit", methods=["POST"])
633@login_required
634@require_pilot_access
635def minimums_item_edit(item_id: int) -> ResponseReturnValue:
636 uid = _current_user_id()
637 item = _get_own_draft_item_or_404(item_id, uid)
638 label = request.form.get("label", "").strip()
639 value = request.form.get("value", "").strip()
640 tag_raw = request.form.get("tag", "").strip()
641 numeric_raw = request.form.get("numeric_value", "").strip()
642 if not label:
643 flash(_("Item label is required."), "danger")
644 return redirect(url_for("pilots.minimums_edit"))
645 tag, numeric_value, error = _validate_tag_and_numeric(tag_raw, numeric_raw)
646 if error:
647 flash(error, "danger")
648 return redirect(url_for("pilots.minimums_edit"))
649 item.label = label
650 item.value = value or None
651 item.semantic_tag = tag
652 item.numeric_value = numeric_value
653 db.session.commit()
654 flash(_("Item updated."), "success")
655 return redirect(url_for("pilots.minimums_edit"))
658@pilots_bp.route("/pilot/minimums/item/<int:item_id>/delete", methods=["POST"])
659@login_required
660@require_pilot_access
661def minimums_item_delete(item_id: int) -> ResponseReturnValue:
662 uid = _current_user_id()
663 item = _get_own_draft_item_or_404(item_id, uid)
664 db.session.delete(item)
665 db.session.commit()
666 flash(_("Item removed."), "success")
667 return redirect(url_for("pilots.minimums_edit"))
670@pilots_bp.route("/pilot/minimums/item/<int:item_id>/move-up", methods=["POST"])
671@login_required
672@require_pilot_access
673def minimums_item_move_up(item_id: int) -> ResponseReturnValue:
674 uid = _current_user_id()
675 item = _get_own_draft_item_or_404(item_id, uid)
676 _move_sort_order(PersonalMinimumsItem, item, "section_id", item.section_id, "up")
677 db.session.commit()
678 return redirect(url_for("pilots.minimums_edit"))
681@pilots_bp.route("/pilot/minimums/item/<int:item_id>/move-down", methods=["POST"])
682@login_required
683@require_pilot_access
684def minimums_item_move_down(item_id: int) -> ResponseReturnValue:
685 uid = _current_user_id()
686 item = _get_own_draft_item_or_404(item_id, uid)
687 _move_sort_order(PersonalMinimumsItem, item, "section_id", item.section_id, "down")
688 db.session.commit()
689 return redirect(url_for("pilots.minimums_edit"))
692@pilots_bp.route("/pilot/minimums/publish", methods=["GET", "POST"])
693@login_required
694@require_pilot_access
695def minimums_publish() -> ResponseReturnValue:
696 uid = _current_user_id()
697 draft = _draft_minimums_revision(uid)
698 if draft is None:
699 flash(_("No draft to publish."), "danger")
700 return redirect(url_for("pilots.minimums_view"))
701 if not draft.sections:
702 flash(_("Add at least one section before publishing."), "danger")
703 return redirect(url_for("pilots.minimums_edit"))
705 if request.method == "POST":
706 active = _active_minimums_revision(uid)
707 if active is not None:
708 active.status = PersonalMinimumsStatus.SUPERSEDED
709 totals = _compute_totals_sql(uid)
710 draft.status = PersonalMinimumsStatus.ACTIVE
711 draft.published_on = _date.today()
712 draft.experience_hours = totals["total_flight_time"]
713 db.session.commit()
714 flash(_("Personal minimums published."), "success")
715 return redirect(url_for("pilots.minimums_view"))
717 return render_template(
718 "pilots/minimums_publish_confirm.html",
719 revision=draft,
720 flew_or_reserved_today=_flew_or_reserved_today(uid),
721 )
724@pilots_bp.route("/pilot/minimums/delete-draft", methods=["POST"])
725@login_required
726@require_pilot_access
727def minimums_delete_draft() -> ResponseReturnValue:
728 uid = _current_user_id()
729 draft = _draft_minimums_revision(uid)
730 if draft is None:
731 abort(404)
732 db.session.delete(draft)
733 db.session.commit()
734 flash(_("Draft discarded."), "success")
735 return redirect(url_for("pilots.minimums_view"))
738@pilots_bp.route("/pilot/minimums/print")
739@login_required
740@require_pilot_access
741def minimums_print() -> ResponseReturnValue:
742 uid = _current_user_id()
743 active = _active_minimums_revision(uid)
744 if active is None:
745 flash(_("No active personal minimums to print yet."), "warning")
746 return redirect(url_for("pilots.minimums_view"))
747 return render_template("pilots/minimums_print.html", revision=active)
750# ── GPS tracks map ────────────────────────────────────────────────────────────
753@pilots_bp.route("/pilot/tracks")
754@login_required
755@require_pilot_access
756def pilot_tracks() -> ResponseReturnValue:
757 from flask import url_for as _url_for
759 uid = _current_user_id()
760 entries = (
761 _my_entries_query(uid)
762 .filter(Flight.gps_track_id.isnot(None))
763 .order_by(Flight.date.asc())
764 .all()
765 )
766 track_rows = [
767 {
768 "date": str(e.date),
769 "dep": e.departure_icao or "",
770 "arr": e.arrival_icao or "",
771 "time_str": f"{e.total_flight_time} h"
772 if e.total_flight_time is not None
773 else "",
774 "view_url": _url_for(
775 "aircraft.flight_detail",
776 aircraft_id=e.aircraft_id,
777 flight_id=e.id,
778 )
779 if e.aircraft_id
780 else _url_for("pilots.view_entry", entry_id=e.id),
781 "geojson": e.gps_track.geojson if e.gps_track else None,
782 }
783 for e in entries
784 ]
786 return render_template(
787 "pilots/flight_tracks.html",
788 track_rows=track_rows,
789 openaip_key=_openaip_key(),
790 )
793@pilots_bp.route("/pilot/tracks/animation.gif")
794@login_required
795@require_pilot_access
796def pilot_tracks_gif() -> ResponseReturnValue:
797 from flask import Response # pyright: ignore[reportMissingImports]
798 from utils import ( # pyright: ignore[reportMissingImports]
799 generate_tracks_gif,
800 sort_tracks_oldest_first,
801 )
803 uid = _current_user_id()
804 entries = _my_entries_query(uid).filter(Flight.gps_track_id.isnot(None)).all()
805 track_rows = sort_tracks_oldest_first(
806 [
807 {
808 "date": str(e.date),
809 "dep": e.departure_icao or "",
810 "arr": e.arrival_icao or "",
811 "geojson": e.gps_track.geojson if e.gps_track else None,
812 }
813 for e in entries
814 ]
815 )
816 portrait = request.args.get("orientation") == "portrait"
817 hires = request.args.get("quality") == "hires"
818 base_w, base_h = (480, 800) if portrait else (800, 480)
819 mul = 2 if hires else 1
820 canvas_w, canvas_h = base_w * mul, base_h * mul
821 gif_bytes = generate_tracks_gif(
822 track_rows,
823 _openaip_key=_openaip_key(),
824 canvas_w=canvas_w,
825 canvas_h=canvas_h,
826 high_res=hires,
827 )
828 orient_sfx = "-portrait" if portrait else ""
829 qual_sfx = "-hires" if hires else ""
830 suffix = orient_sfx + qual_sfx
831 return Response(
832 gif_bytes,
833 mimetype="image/gif",
834 headers={
835 "Content-Disposition": f'attachment; filename="my_tracks{suffix}.gif"'
836 },
837 )
840@pilots_bp.route("/pilot/stats")
841@login_required
842@require_pilot_access
843def pilot_stats() -> ResponseReturnValue:
844 from reports.flight_stats import ( # pyright: ignore[reportMissingImports]
845 compute_flight_stats,
846 )
848 uid = _current_user_id()
849 entries = _my_entries_query(uid).all()
850 stats = compute_flight_stats(entries)
851 return render_template("pilots/stats.html", stats=stats)
854# ── Logbook entry detail (read-only) ─────────────────────────────────────────
857@pilots_bp.route("/pilot/logbook/<int:entry_id>/view")
858@login_required
859@require_pilot_access
860def view_entry(entry_id: int) -> ResponseReturnValue:
861 uid = _current_user_id()
862 entry = db.session.get(Flight, entry_id)
863 if not entry or (entry.pic_user_id != uid and entry.second_crew_user_id != uid):
864 abort(404)
866 return render_template(
867 "pilots/entry_detail.html",
868 entry=entry,
869 openaip_key=_openaip_key(),
870 LogbookEntryType=LogbookEntryType,
871 )
874# ── Logbook list ──────────────────────────────────────────────────────────────
877@pilots_bp.route("/pilot/logbook")
878@login_required
879@require_pilot_access
880def logbook() -> ResponseReturnValue:
881 uid = _current_user_id()
882 order = request.args.get("order", "desc")
883 page = request.args.get("page", 1, type=int)
884 pp_raw = request.args.get("per_page", str(_DEFAULT_PER_PAGE))
885 show_all = pp_raw == "all"
886 per_page = (
887 None
888 if show_all
889 else (
890 int(pp_raw)
891 if pp_raw.isdigit() and int(pp_raw) in _VALID_PER_PAGE
892 else _DEFAULT_PER_PAGE
893 )
894 )
896 q = _my_entries_query(uid)
897 if order == "asc":
898 q = q.order_by(
899 Flight.date.asc(),
900 Flight.departure_time.asc().nullslast(),
901 Flight.id.asc(),
902 )
903 else:
904 q = q.order_by(
905 Flight.date.desc(),
906 Flight.departure_time.desc().nullslast(),
907 Flight.id.desc(),
908 )
910 if show_all:
911 entries = q.all()
912 pagination = None
913 else:
914 pagination = q.paginate(page=page, per_page=per_page, error_out=False)
915 entries = pagination.items
917 totals = _compute_totals_sql(uid)
918 logbook_milestone = session.pop("logbook_milestone", None)
920 active_minimums = _active_minimums_revision(uid)
921 minimums_breaches = (
922 recency_breaches(active_minimums, uid) if active_minimums else []
923 )
925 return render_template(
926 "pilots/logbook.html",
927 entries=entries,
928 pagination=pagination,
929 totals=totals,
930 order=order,
931 per_page=pp_raw,
932 valid_per_page=_VALID_PER_PAGE,
933 logbook_milestone=logbook_milestone,
934 LogbookEntryType=LogbookEntryType,
935 minimums_breaches=minimums_breaches,
936 crew_invites=pending_invites_for_user(uid),
937 crew_claims=pending_claims_to_review(uid),
938 flight_corrections=pending_suggestions_to_review(uid),
939 crew_roles=CrewRole,
940 )
943def _compute_totals_sql(pilot_user_id: int) -> dict[str, object]:
944 """Aggregate totals over ALL entries for the pilot via SQL queries.
946 Shared EASA figures (night/instrument time, landings, single_pilot_se/me,
947 multi_pilot, fstd_duration) are summed once per row regardless of which
948 slot this pilot occupies — they describe the flight, not the occupant.
949 function_pic only ever belongs to the pic_user_id slot's own hours, and
950 function_copilot/dual/instructor only to the second_crew_user_id slot's
951 (see Flight's docstring) — each summed with its own slot-scoped filter,
952 so a flight where this pilot was PIC doesn't accidentally credit them
953 with the other occupant's dual/instructor time, or vice versa.
954 """
955 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
957 mine = or_(
958 Flight.pic_user_id == pilot_user_id,
959 Flight.second_crew_user_id == pilot_user_id,
960 )
961 row = (
962 db.session.query(
963 func.sum(Flight.night_time),
964 func.sum(Flight.instrument_time),
965 func.sum(Flight.landings_day),
966 func.sum(Flight.landings_night),
967 func.sum(Flight.single_pilot_se),
968 func.sum(Flight.single_pilot_me),
969 func.sum(Flight.multi_pilot),
970 func.sum(Flight.fstd_duration),
971 )
972 .filter(mine)
973 .one()
974 )
975 fn_pic = (
976 db.session.query(func.sum(Flight.function_pic))
977 .filter(Flight.pic_user_id == pilot_user_id)
978 .scalar()
979 )
980 fn_second = (
981 db.session.query(
982 func.sum(Flight.function_copilot),
983 func.sum(Flight.function_dual),
984 func.sum(Flight.function_instructor),
985 )
986 .filter(Flight.second_crew_user_id == pilot_user_id)
987 .one()
988 )
990 sp_se = round(float(row[4] or 0), 1)
991 sp_me = round(float(row[5] or 0), 1)
992 multi = round(float(row[6] or 0), 1)
994 return {
995 "night_time": round(float(row[0] or 0), 1),
996 "instrument_time": round(float(row[1] or 0), 1),
997 "landings_day": int(row[2] or 0),
998 "landings_night": int(row[3] or 0),
999 "single_pilot_se": sp_se,
1000 "single_pilot_me": sp_me,
1001 "multi_pilot": multi,
1002 # FSTD/simulator sessions are excluded from flight-time totals — they
1003 # are not flight hours, only single_pilot_se/me and multi_pilot count.
1004 "total_flight_time": round(sp_se + sp_me + multi, 1),
1005 "function_pic": round(float(fn_pic or 0), 1),
1006 "function_copilot": round(float(fn_second[0] or 0), 1),
1007 "function_dual": round(float(fn_second[1] or 0), 1),
1008 "function_instructor": round(float(fn_second[2] or 0), 1),
1009 "fstd_duration": round(float(row[7] or 0), 1),
1010 }
1013# ── New entry ────────────────────────────────────────────────────────────────
1016@pilots_bp.route("/pilot/logbook/new", methods=["GET", "POST"])
1017@login_required
1018@require_pilot_access
1019def new_entry() -> ResponseReturnValue:
1020 uid = _current_user_id()
1022 if request.method == "POST":
1023 values, errors = parse_pilot_fields(request.form)
1024 if errors:
1025 for e in errors:
1026 flash(e, "danger")
1027 return render_template(
1028 "pilots/entry_form.html",
1029 entry=None,
1030 form=request.form,
1031 action="new",
1032 openaip_key=_openaip_key(),
1033 LogbookEntryType=LogbookEntryType,
1034 FstdType=FstdType,
1035 crew_name_suggestions=_crew_name_suggestions(uid),
1036 ), 422
1037 entry = Flight(pic_user_id=uid)
1038 apply_pilot_fields(entry, values)
1039 db.session.add(entry)
1040 db.session.flush()
1041 _apply_gps_to_pilot_entry(entry)
1042 db.session.commit()
1043 _check_logbook_milestone(entry, uid)
1044 flash(_("Logbook entry saved."), "success")
1045 return redirect(url_for("pilots.logbook"))
1047 return render_template(
1048 "pilots/entry_form.html",
1049 entry=None,
1050 form={},
1051 action="new",
1052 openaip_key=_openaip_key(),
1053 LogbookEntryType=LogbookEntryType,
1054 FstdType=FstdType,
1055 crew_name_suggestions=_crew_name_suggestions(uid),
1056 )
1059# ── Edit entry ────────────────────────────────────────────────────────────────
1062@pilots_bp.route("/pilot/logbook/<int:entry_id>/edit", methods=["GET", "POST"])
1063@login_required
1064@require_pilot_access
1065def edit_entry(entry_id: int) -> ResponseReturnValue:
1066 uid = _current_user_id()
1067 entry = db.session.get(Flight, entry_id)
1068 if not entry or (entry.pic_user_id != uid and entry.second_crew_user_id != uid):
1069 abort(404)
1071 if entry.aircraft_id:
1072 return redirect(url_for("flights.edit_flight", flight_id=entry.id))
1074 # Confirmed on someone else's flight: only their own part is editable
1075 # (flights/shared_flight.py).
1076 if not can_edit_shared(entry, uid, current_user_role()):
1077 return redirect(url_for("flights.crew_entry", flight_id=entry.id))
1079 if request.method == "POST":
1080 values, errors = parse_pilot_fields(request.form)
1081 if errors:
1082 for e in errors:
1083 flash(e, "danger")
1084 return render_template(
1085 "pilots/entry_form.html",
1086 entry=entry,
1087 form=request.form,
1088 action="edit",
1089 openaip_key=_openaip_key(),
1090 LogbookEntryType=LogbookEntryType,
1091 FstdType=FstdType,
1092 crew_name_suggestions=_crew_name_suggestions(uid),
1093 ), 422
1094 shared_before = shared_snapshot(entry)
1095 other_pilots = protect_other_pilots(entry, uid)
1096 apply_pilot_fields(entry, values)
1097 restore_other_pilots(entry, other_pilots)
1098 _apply_gps_to_pilot_entry(entry)
1099 db.session.commit()
1100 notify_shared_changes(entry, uid, shared_before)
1101 flash(_("Logbook entry updated."), "success")
1102 return redirect(url_for("pilots.logbook"))
1104 return render_template(
1105 "pilots/entry_form.html",
1106 entry=entry,
1107 form={},
1108 action="edit",
1109 openaip_key=_openaip_key(),
1110 LogbookEntryType=LogbookEntryType,
1111 FstdType=FstdType,
1112 crew_name_suggestions=_crew_name_suggestions(uid),
1113 )
1116# ── Delete entry ──────────────────────────────────────────────────────────────
1119def _delete_upload(filename: str | None) -> None:
1120 if not filename:
1121 return
1122 folder = current_app.config.get("UPLOAD_FOLDER", "/data/uploads")
1123 try:
1124 os.remove(os.path.join(folder, filename))
1125 except OSError:
1126 current_app.logger.debug(
1127 "Could not delete upload %s (already absent?)", filename
1128 )
1131@pilots_bp.route("/pilot/logbook/<int:entry_id>/delete", methods=["POST"])
1132@login_required
1133@require_pilot_access
1134def delete_entry(entry_id: int) -> ResponseReturnValue:
1135 uid = _current_user_id()
1136 entry = db.session.get(Flight, entry_id)
1137 if not entry or (entry.pic_user_id != uid and entry.second_crew_user_id != uid):
1138 abort(404)
1140 # Another pilot has this flight in their logbook too: only remove this
1141 # pilot's own link — never their colleague's hours (flights/crew_removal.py).
1142 if entry.other_linked_user_ids(uid):
1143 remove_flight_for_user(entry, uid)
1144 db.session.commit()
1145 flash(
1146 _(
1147 "Flight removed from your logbook. It stays in the other "
1148 "pilot's logbook."
1149 ),
1150 "success",
1151 )
1152 return redirect(url_for("pilots.logbook"))
1154 # Unified model: there's only one row now, so deleting it removes both
1155 # the pilot's own record and the airframe log entry at once (no more
1156 # separate "also delete the linked flight" choice). If it's a managed
1157 # aircraft's row, still gate on aircraft access — not just crew identity
1158 # — before deleting shared airframe data.
1159 if entry.aircraft_id and not user_can_access_aircraft(entry.aircraft_id):
1160 abort(403)
1162 _delete_upload(entry.flight_counter_photo)
1163 _delete_upload(entry.engine_counter_photo)
1164 _delete_upload(entry.flight_counter_photo_preflight)
1165 _delete_upload(entry.engine_counter_photo_preflight)
1166 db.session.delete(entry)
1167 db.session.commit()
1168 flash(_("Logbook entry deleted."), "success")
1169 return redirect(url_for("pilots.logbook"))
1172# ── GPS track helper ──────────────────────────────────────────────────────────
1175def _apply_gps_to_pilot_entry(entry: Flight) -> None:
1176 """Create or update the GpsTrack linked to a pilot logbook entry from form data."""
1177 f = request.form
1178 gps_geojson_raw = f.get("gps_geojson", "").strip()
1179 gps_filename = f.get("gps_filename", "").strip() or None
1180 if not gps_geojson_raw and not gps_filename:
1181 return
1183 geojson = None
1184 if gps_geojson_raw:
1185 with contextlib.suppress(
1186 ValueError
1187 ): # malformed hidden field — GPS track simply not applied
1188 geojson = json.loads(gps_geojson_raw)
1190 def _parse_dt(raw: str) -> "_datetime | None":
1191 try:
1192 return _datetime.fromisoformat(raw) if raw else None
1193 except ValueError:
1194 return None
1196 block_off = _parse_dt(f.get("gps_block_off_utc", "").strip())
1197 block_on = _parse_dt(f.get("gps_block_on_utc", "").strip())
1198 dep = f.get("departure_place", "").strip().upper()[:4] or None
1199 arr = f.get("arrival_place", "").strip().upper()[:4] or None
1201 if entry.gps_track_id:
1202 gt = db.session.get(GpsTrack, entry.gps_track_id)
1203 if gt:
1204 if geojson is not None:
1205 gt.geojson = geojson
1206 if gps_filename:
1207 gt.source_filename = gps_filename
1208 if block_off:
1209 gt.block_off_utc = block_off
1210 if block_on:
1211 gt.block_on_utc = block_on
1212 return
1214 gt = GpsTrack(
1215 source_filename=gps_filename,
1216 block_off_utc=block_off,
1217 block_on_utc=block_on,
1218 departure_icao=dep,
1219 arrival_icao=arr,
1220 geojson=geojson,
1221 )
1222 db.session.add(gt)
1223 db.session.flush()
1224 entry.gps_track_id = gt.id
1227# ── Logbook Import ────────────────────────────────────────────────────────────
1229_IMPORT_SESSION_KEY = "logbook_import"
1230_IMPORT_REVIEW_SESSION_KEY = "logbook_import_review"
1231_ALLOWED_IMPORT_EXTS = {".csv", ".xlsx", ".xls"}
1232_MAX_IMPORT_BYTES = 10 * 1024 * 1024 # 10 MB
1235def _import_tmp_dir() -> str:
1236 folder = current_app.config.get("UPLOAD_FOLDER", "/data/uploads")
1237 d = os.path.join(folder, "import_tmp")
1238 os.makedirs(d, exist_ok=True)
1239 return d
1242def _cleanup_previous_tmp(uid: int) -> None:
1243 """Delete any leftover temp import file for this user, including one
1244 left behind by an abandoned conflict-review (started a fresh upload
1245 instead of finishing it)."""
1246 meta = session.get(_IMPORT_SESSION_KEY)
1247 if meta and meta.get("uid") == uid:
1248 tmp = meta.get("tmp_path")
1249 if tmp and os.path.isfile(tmp):
1250 try:
1251 os.remove(tmp)
1252 except OSError as exc:
1253 current_app.logger.debug("cleanup tmp import file: %s", exc)
1254 session.pop(_IMPORT_SESSION_KEY, None)
1256 review_state = session.get(_IMPORT_REVIEW_SESSION_KEY)
1257 if review_state and review_state.get("uid") == uid:
1258 tmp = review_state.get("tmp_path")
1259 if tmp and os.path.isfile(tmp):
1260 try:
1261 os.remove(tmp)
1262 except OSError as exc:
1263 current_app.logger.debug("cleanup tmp review file: %s", exc)
1264 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1267@pilots_bp.route("/pilot/logbook/import", methods=["GET", "POST"])
1268@login_required
1269@require_pilot_access
1270def import_upload() -> ResponseReturnValue:
1271 uid = _current_user_id()
1273 if request.method == "GET":
1274 return render_template("pilots/import_upload.html")
1276 # ── POST: receive file, parse, present mapping page ───────────────────────
1277 uploaded = request.files.get("logbook_file")
1278 if not uploaded or not uploaded.filename:
1279 flash(_("Please select a file to upload."), "danger")
1280 return render_template("pilots/import_upload.html"), 422
1282 ext = os.path.splitext(uploaded.filename)[1].lower()
1283 if ext not in _ALLOWED_IMPORT_EXTS:
1284 flash(
1285 _("Unsupported format. Please upload a .csv or .xlsx file."),
1286 "danger",
1287 )
1288 return render_template("pilots/import_upload.html"), 422
1290 data = uploaded.read()
1291 if len(data) > _MAX_IMPORT_BYTES:
1292 flash(_("File too large (maximum 10 MB)."), "danger")
1293 return render_template("pilots/import_upload.html"), 422
1295 try:
1296 parsed = parse_file(data, uploaded.filename)
1297 except ValueError as exc:
1298 flash(str(exc), "danger")
1299 return render_template("pilots/import_upload.html"), 422
1301 # Save to a temp file so execute step can re-parse without re-upload
1302 _cleanup_previous_tmp(uid)
1303 safe_base = secure_filename(uploaded.filename) or "upload"
1304 tmp_name = f"import_{uid}_{uuid.uuid4().hex}_{safe_base}"
1305 tmp_path = os.path.join(_import_tmp_dir(), tmp_name)
1306 with open(tmp_path, "wb") as fh:
1307 fh.write(data)
1309 session[_IMPORT_SESSION_KEY] = {
1310 "uid": uid,
1311 "tmp_path": tmp_path,
1312 "original_filename": uploaded.filename,
1313 "norm_cols": parsed.norm_cols,
1314 "raw_cols": parsed.raw_cols,
1315 "fingerprint": parsed.fingerprint,
1316 }
1318 # Look up saved mappings for this pilot
1319 saved = LogbookImportMapping.query.filter_by(pilot_user_id=uid).all()
1320 proposal = propose_mapping(parsed, saved)
1322 preview = preview_rows(parsed, proposal.mapping, n=5)
1324 return render_template(
1325 "pilots/import_map.html",
1326 norm_cols=parsed.norm_cols,
1327 raw_cols=parsed.raw_cols,
1328 base_norm_cols=[_norm(r) for r in parsed.raw_cols],
1329 mapping=proposal.mapping,
1330 match_type=proposal.match_type,
1331 fuzzy_score=proposal.fuzzy_score,
1332 target_fields=TARGET_FIELDS,
1333 preview=preview,
1334 filename=uploaded.filename,
1335 type_hints=type_hints(parsed, proposal.mapping),
1336 )
1339@pilots_bp.route("/pilot/logbook/import/execute", methods=["POST"])
1340@login_required
1341@require_pilot_access
1342def import_execute() -> ResponseReturnValue:
1343 uid = _current_user_id()
1344 meta = session.get(_IMPORT_SESSION_KEY)
1346 if not meta or meta.get("uid") != uid:
1347 flash(_("Import session expired. Please upload the file again."), "warning")
1348 return redirect(url_for("pilots.import_upload"))
1350 tmp_path: str = meta["tmp_path"]
1351 original_filename: str = meta["original_filename"]
1352 norm_cols: list[str] = meta["norm_cols"]
1353 fingerprint: str = meta["fingerprint"]
1355 if not os.path.isfile(tmp_path):
1356 flash(_("Temporary file not found. Please upload the file again."), "warning")
1357 session.pop(_IMPORT_SESSION_KEY, None)
1358 return redirect(url_for("pilots.import_upload"))
1360 # Reconstruct mapping from form
1361 mapping: dict[str, str] = {}
1362 for col in norm_cols:
1363 val = request.form.get(f"mapping_{col}", "ignore").strip()
1364 mapping[col] = val if val in TARGET_FIELDS else "ignore"
1366 # Validate: at least 'date' must be mapped
1367 if "date" not in mapping.values():
1368 flash(_("You must map at least one column to 'Date'."), "danger")
1369 # Re-render the mapping page
1370 with open(tmp_path, "rb") as fh:
1371 data = fh.read()
1372 try:
1373 parsed = parse_file(data, original_filename)
1374 except ValueError:
1375 session.pop(_IMPORT_SESSION_KEY, None)
1376 return redirect(url_for("pilots.import_upload"))
1377 preview = preview_rows(parsed, mapping, n=5)
1378 return render_template(
1379 "pilots/import_map.html",
1380 norm_cols=parsed.norm_cols,
1381 raw_cols=parsed.raw_cols,
1382 base_norm_cols=[_norm(r) for r in parsed.raw_cols],
1383 mapping=mapping,
1384 match_type="alias",
1385 fuzzy_score=0.0,
1386 target_fields=TARGET_FIELDS,
1387 preview=preview,
1388 filename=original_filename,
1389 type_hints=type_hints(parsed, mapping),
1390 ), 422
1392 # Parse opening balance
1393 opening_balance: dict[str, float | None] = {}
1394 ob_fields = [
1395 "single_pilot_se",
1396 "single_pilot_me",
1397 "multi_pilot",
1398 "night_time",
1399 "instrument_time",
1400 "function_pic",
1401 "function_copilot",
1402 "function_dual",
1403 "function_instructor",
1404 ]
1405 for f_name in ob_fields:
1406 raw = request.form.get(f"ob_{f_name}", "").strip()
1407 opening_balance[f_name] = parse_duration_value(raw) if raw else None
1409 # Re-parse the temp file
1410 with open(tmp_path, "rb") as fh:
1411 data = fh.read()
1412 try:
1413 parsed = parse_file(data, original_filename)
1414 except ValueError as exc:
1415 flash(str(exc), "danger")
1416 session.pop(_IMPORT_SESSION_KEY, None)
1417 return redirect(url_for("pilots.import_upload"))
1419 # Create or reuse the mapping record
1420 saved_mappings = LogbookImportMapping.query.filter_by(pilot_user_id=uid).all()
1421 mapping_record: LogbookImportMapping | None = None
1422 for m in saved_mappings:
1423 if m.source_fingerprint == fingerprint:
1424 # Update the saved mapping with the user's potentially-refined choices
1425 m.column_mapping = json.dumps(mapping)
1426 mapping_record = m
1427 break
1428 if mapping_record is None:
1429 mapping_record = LogbookImportMapping(
1430 pilot_user_id=uid,
1431 source_fingerprint=fingerprint,
1432 column_mapping=json.dumps(mapping),
1433 source_columns=json.dumps(norm_cols),
1434 created_at=_datetime.now(UTC),
1435 )
1436 db.session.add(mapping_record)
1437 db.session.flush() # get mapping_record.id
1439 # Create the batch record (row counts filled in after execute)
1440 batch = LogbookImportBatch(
1441 pilot_user_id=uid,
1442 mapping_id=mapping_record.id,
1443 source_filename=original_filename,
1444 imported_at=_datetime.now(UTC),
1445 )
1446 db.session.add(batch)
1447 db.session.flush() # get batch.id
1449 resolved_opening_balance = (
1450 opening_balance if any(opening_balance.values()) else None
1451 )
1453 # Rows that look like they might be an edited version of an existing
1454 # entry (score >= _CANDIDATE_MIN_SCORE) need a human decision, not a
1455 # guess — carve them out of this pass and route them through the
1456 # interactive review step below instead of silently importing or
1457 # skipping them.
1458 all_conflicts = find_conflicting_rows(parsed, mapping, uid)
1459 # A row whose only match is the same flight logged from the airframe
1460 # side just completes it — merged straight away, no decision needed.
1461 auto_merges, conflicts = split_auto_merges(all_conflicts)
1462 for auto_conflict, existing_id in auto_merges:
1463 merge_into_existing(
1464 db.session.get(Flight, existing_id),
1465 auto_conflict.kwargs,
1466 uid,
1467 fill_only=True,
1468 )
1470 result = execute_import(
1471 parsed=parsed,
1472 mapping=mapping,
1473 pilot_user_id=uid,
1474 batch_id=batch.id,
1475 opening_balance=resolved_opening_balance,
1476 skip_row_nums={c.row_num for c in all_conflicts},
1477 )
1479 batch.row_count = result.imported
1480 batch.subtotal_count = result.subtotals
1481 batch.skipped_count = len(result.skipped)
1482 batch.has_opening_balance = result.has_opening_balance
1484 db.session.commit()
1486 if auto_merges:
1487 flash(
1488 ngettext(
1489 "One row matched a flight already in the aircraft log and was "
1490 "merged into it.",
1491 "%(n)d rows matched a flight already in the aircraft log and "
1492 "were merged into it.",
1493 len(auto_merges),
1494 n=len(auto_merges),
1495 ),
1496 "info",
1497 )
1499 if conflicts:
1500 # Defer the aircraft-link pass and tmp-file cleanup until every
1501 # conflict is resolved — _finalize_import_review does both, covering
1502 # entries added during review as well as the ones just committed.
1503 session[_IMPORT_REVIEW_SESSION_KEY] = {
1504 "uid": uid,
1505 "tmp_path": tmp_path,
1506 "original_filename": original_filename,
1507 "mapping": mapping,
1508 "batch_id": batch.id,
1509 "resolved": {},
1510 "auto_merged": [c.row_num for c, _eid in auto_merges],
1511 }
1512 session.pop(_IMPORT_SESSION_KEY, None)
1513 session.modified = True
1515 flash(
1516 _(
1517 "%(imported)d entries imported so far. %(n)d rows look like they "
1518 "might already be in your logbook with different data — please "
1519 "review them below.",
1520 imported=result.imported,
1521 n=len(conflicts),
1522 ),
1523 "info",
1524 )
1525 if result.duplicates:
1526 detail = "; ".join(
1527 reason if r == 0 else f"row {r}: {reason}"
1528 for r, reason in result.duplicates[:5]
1529 )
1530 if len(result.duplicates) > 5:
1531 detail += f" … and {len(result.duplicates) - 5} more"
1532 flash(
1533 _("Rows already in your logbook, skipped: %(detail)s", detail=detail),
1534 "info",
1535 )
1536 if result.skipped:
1537 detail = "; ".join(f"row {r}: {reason}" for r, reason in result.skipped[:5])
1538 if len(result.skipped) > 5:
1539 detail += f" … and {len(result.skipped) - 5} more"
1540 flash(_("Skipped rows: %(detail)s", detail=detail), "warning")
1542 return redirect(url_for("pilots.import_review"))
1544 # Promote newly imported standalone entries to managed aircraft, where
1545 # the registration matches one.
1546 new_entries = (
1547 Flight.query.filter(
1548 Flight.import_batch_id == batch.id,
1549 Flight.aircraft_id.is_(None),
1550 Flight.other_aircraft_registration.isnot(None),
1551 )
1552 .order_by(Flight.date.asc(), Flight.id.asc())
1553 .all()
1554 )
1555 ac_created = link_entries_to_aircraft(new_entries)
1556 if ac_created > 0:
1557 db.session.commit()
1559 # Clean up temp file and session
1560 try:
1561 os.remove(tmp_path)
1562 except OSError as exc:
1563 current_app.logger.debug("cleanup tmp import file: %s", exc)
1564 session.pop(_IMPORT_SESSION_KEY, None)
1566 # Duplicates are a normal, expected outcome — e.g. re-uploading a
1567 # spreadsheet after appending a few new flights — not an error, so these
1568 # messages stay in the "success"/"info" register rather than "warning".
1569 if result.imported == 0 and result.duplicates:
1570 flash(
1571 _(
1572 "All entries in this file were already in your logbook — "
1573 "nothing new was imported."
1574 ),
1575 "success",
1576 )
1577 else:
1578 from flights.routes import _skipped_rows_summary
1580 n_dup = len(result.duplicates)
1581 if result.duplicates:
1582 parts = [
1583 ngettext(
1584 "Import complete: one new entry imported.",
1585 "Import complete: %(n)d new entries imported.",
1586 result.imported,
1587 n=result.imported,
1588 ),
1589 ngettext(
1590 "One row was already in your logbook and was skipped.",
1591 "%(n)d rows were already in your logbook and were skipped.",
1592 n_dup,
1593 n=n_dup,
1594 ),
1595 ]
1596 elif result.imported:
1597 parts = [
1598 ngettext(
1599 "Import complete: one entry imported.",
1600 "Import complete: %(n)d entries imported.",
1601 result.imported,
1602 n=result.imported,
1603 )
1604 ]
1605 else:
1606 parts = [_("Import complete: no entries imported.")]
1607 flash(" ".join(parts + _skipped_rows_summary(result)), "success")
1608 if ac_created > 0:
1609 flash(
1610 ngettext(
1611 "%(n)d aircraft log entry was also created for your managed aircraft.",
1612 "%(n)d aircraft log entries were also created for your managed aircraft.",
1613 ac_created,
1614 n=ac_created,
1615 ),
1616 "info",
1617 )
1618 if result.duplicates:
1619 detail = "; ".join(
1620 reason if r == 0 else f"row {r}: {reason}"
1621 for r, reason in result.duplicates[:5]
1622 )
1623 if len(result.duplicates) > 5:
1624 detail += f" … and {len(result.duplicates) - 5} more"
1625 flash(
1626 _("Rows already in your logbook, skipped: %(detail)s", detail=detail),
1627 "info",
1628 )
1629 if result.skipped:
1630 detail = "; ".join(f"row {r}: {reason}" for r, reason in result.skipped[:5])
1631 if len(result.skipped) > 5:
1632 detail += f" … and {len(result.skipped) - 5} more"
1633 flash(_("Skipped rows: %(detail)s", detail=detail), "warning")
1635 if result.parse_warnings:
1636 n = len(result.parse_warnings)
1637 examples = "; ".join(
1638 f"row {r}, {target}: {raw}"
1639 for r, _col, target, raw in result.parse_warnings[:3]
1640 )
1641 if n > 3:
1642 examples += f" … +{n - 3}"
1643 flash(
1644 ngettext(
1645 "One cell value could not be parsed and was imported as blank: %(examples)s",
1646 "%(n)d cell values could not be parsed and were imported as blank: %(examples)s",
1647 n,
1648 n=n,
1649 examples=examples,
1650 ),
1651 "warning",
1652 )
1654 if result.total_mismatch_warnings:
1655 n = len(result.total_mismatch_warnings)
1656 examples = "; ".join(
1657 _(
1658 "row %(row)d (source %(src).1f h, computed %(comp).1f h)",
1659 row=r,
1660 src=src,
1661 comp=comp,
1662 )
1663 for r, src, comp in result.total_mismatch_warnings[:3]
1664 )
1665 if n > 3:
1666 examples += f" … +{n - 3}"
1667 flash(
1668 ngettext(
1669 "One row has a total flight time that doesn't match the sum of its components — please review: %(examples)s",
1670 "%(n)d rows have a total flight time that doesn't match the sum of their components — please review: %(examples)s",
1671 n,
1672 n=n,
1673 examples=examples,
1674 ),
1675 "warning",
1676 )
1678 return redirect(url_for("pilots.import_history"))
1681def _review_excluded_rows(state: dict[str, Any]) -> set[int]:
1682 """Rows the review step no longer offers: already resolved, or merged
1683 automatically when the import ran."""
1684 return {int(k) for k in state.get("resolved", {})} | set(
1685 state.get("auto_merged", [])
1686 )
1689def _finalize_import_review(state: dict[str, Any]) -> ResponseReturnValue:
1690 """Common tail once every conflict row from a review has a decision:
1691 aircraft-link pass (covers both the rows auto-imported before the review
1692 started and any resolved as 'new' during it), tmp-file/session cleanup,
1693 summary flash, redirect to history — the same shape as the no-conflicts
1694 tail of import_execute above."""
1695 batch_id: int = state["batch_id"]
1696 tmp_path: str = state["tmp_path"]
1698 new_entries = (
1699 Flight.query.filter(
1700 Flight.import_batch_id == batch_id,
1701 Flight.aircraft_id.is_(None),
1702 Flight.other_aircraft_registration.isnot(None),
1703 )
1704 .order_by(Flight.date.asc(), Flight.id.asc())
1705 .all()
1706 )
1707 ac_created = link_entries_to_aircraft(new_entries)
1708 if ac_created > 0:
1709 db.session.commit()
1711 try:
1712 os.remove(tmp_path)
1713 except OSError as exc:
1714 current_app.logger.debug("cleanup tmp review file: %s", exc)
1715 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1717 resolved: dict[str, str] = state.get("resolved", {})
1718 kept = sum(1 for d in resolved.values() if d == "keep")
1719 overwritten = sum(1 for d in resolved.values() if d.startswith("overwrite:"))
1720 added_new = sum(1 for d in resolved.values() if d == "new")
1722 flash(
1723 _(
1724 "Review complete: %(overwritten)d entries updated, %(new)d imported "
1725 "as new, %(kept)d left unchanged.",
1726 overwritten=overwritten,
1727 new=added_new,
1728 kept=kept,
1729 ),
1730 "success",
1731 )
1732 if ac_created > 0:
1733 flash(
1734 ngettext(
1735 "%(n)d aircraft log entry was also created for your managed aircraft.",
1736 "%(n)d aircraft log entries were also created for your managed aircraft.",
1737 ac_created,
1738 n=ac_created,
1739 ),
1740 "info",
1741 )
1743 return redirect(url_for("pilots.import_history"))
1746@pilots_bp.route("/pilot/logbook/import/review")
1747@login_required
1748@require_pilot_access
1749def import_review() -> ResponseReturnValue:
1750 uid = _current_user_id()
1751 state = session.get(_IMPORT_REVIEW_SESSION_KEY)
1752 if not state or state.get("uid") != uid:
1753 flash(_("Import session expired. Please upload the file again."), "warning")
1754 return redirect(url_for("pilots.import_upload"))
1756 tmp_path: str = state["tmp_path"]
1757 if not os.path.isfile(tmp_path):
1758 flash(_("Temporary file not found. Please upload the file again."), "warning")
1759 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1760 return redirect(url_for("pilots.import_upload"))
1762 mapping: dict[str, str] = state["mapping"]
1763 resolved: dict[str, str] = state.get("resolved", {})
1765 with open(tmp_path, "rb") as fh:
1766 data = fh.read()
1767 try:
1768 parsed = parse_file(data, state["original_filename"])
1769 except ValueError:
1770 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1771 return redirect(url_for("pilots.import_upload"))
1773 exclude_row_nums = _review_excluded_rows(state)
1774 conflicts = find_conflicting_rows(
1775 parsed, mapping, uid, exclude_row_nums=exclude_row_nums
1776 )
1778 if not conflicts:
1779 return _finalize_import_review(state)
1781 candidate_ids = {cid for c in conflicts for _score, cid in c.candidates}
1782 candidate_entries: dict[int, Flight] = (
1783 {e.id: e for e in Flight.query.filter(Flight.id.in_(candidate_ids))}
1784 if candidate_ids
1785 else {}
1786 )
1788 rows = [
1789 {
1790 "row_num": c.row_num,
1791 "kwargs": c.kwargs,
1792 "candidates": [
1793 {"id": cid, "score": score, "entry": candidate_entries.get(cid)}
1794 for score, cid in c.candidates
1795 ],
1796 }
1797 for c in conflicts
1798 ]
1800 return render_template(
1801 "pilots/import_review.html",
1802 rows=rows,
1803 resolved_count=len(resolved),
1804 total_count=len(resolved) + len(conflicts),
1805 )
1808@pilots_bp.route("/pilot/logbook/import/review/resolve", methods=["POST"])
1809@login_required
1810@require_pilot_access
1811def import_review_resolve() -> ResponseReturnValue:
1812 uid = _current_user_id()
1813 state = session.get(_IMPORT_REVIEW_SESSION_KEY)
1814 if not state or state.get("uid") != uid:
1815 flash(_("Import session expired. Please upload the file again."), "warning")
1816 return redirect(url_for("pilots.import_upload"))
1818 tmp_path: str = state["tmp_path"]
1819 mapping: dict[str, str] = state["mapping"]
1820 resolved: dict[str, str] = state.get("resolved", {})
1822 try:
1823 row_num = int(request.form.get("row_num", ""))
1824 except (ValueError, TypeError):
1825 flash(_("Invalid row number."), "danger")
1826 return redirect(url_for("pilots.import_review"))
1828 if str(row_num) in resolved:
1829 flash(_("This row has already been resolved."), "info")
1830 return redirect(url_for("pilots.import_review"))
1832 if not os.path.isfile(tmp_path):
1833 flash(_("Temporary file not found. Please upload the file again."), "warning")
1834 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1835 return redirect(url_for("pilots.import_upload"))
1837 with open(tmp_path, "rb") as fh:
1838 data = fh.read()
1839 try:
1840 parsed = parse_file(data, state["original_filename"])
1841 except ValueError:
1842 session.pop(_IMPORT_REVIEW_SESSION_KEY, None)
1843 return redirect(url_for("pilots.import_upload"))
1845 exclude_row_nums = _review_excluded_rows(state)
1846 conflicts = find_conflicting_rows(
1847 parsed, mapping, uid, exclude_row_nums=exclude_row_nums
1848 )
1849 conflict: ConflictRow | None = next(
1850 (c for c in conflicts if c.row_num == row_num), None
1851 )
1852 if conflict is None:
1853 flash(_("Invalid row number."), "danger")
1854 return redirect(url_for("pilots.import_review"))
1856 decision = request.form.get("decision", "")
1857 candidate_ids = {cid for _score, cid in conflict.candidates}
1859 if decision == "keep":
1860 pass # the freshly-parsed row is discarded; the existing entry is untouched
1861 elif decision.startswith("overwrite:"):
1862 try:
1863 existing_id = int(decision.split(":", 1)[1])
1864 except ValueError:
1865 existing_id = -1
1866 if existing_id not in candidate_ids:
1867 flash(_("Invalid selection."), "danger")
1868 return redirect(url_for("pilots.import_review"))
1869 # candidate_ids just came from find_conflicting_rows in this same
1870 # request, which already scopes candidates to rows linked to this
1871 # pilot OR unclaimed rows on this pilot's own tenant aircraft — no
1872 # need to re-check ownership here (and re-checking pic/second_crew
1873 # linkage would wrongly reject the unclaimed case, which is exactly
1874 # the row this decision is meant to claim).
1875 existing = db.session.get(Flight, existing_id)
1876 if existing is None:
1877 # candidate_ids just came from a live query in the same
1878 # request — only a concurrent delete from elsewhere reaches this.
1879 abort(404) # pragma: no cover
1880 # Replace with the row's values — see merge_into_existing for what's
1881 # deliberately left alone. id, import_batch_id, aircraft_id and gps_*
1882 # links are never touched, so this entry stays outside the *current*
1883 # batch's rollback (it wasn't created by it) and any existing
1884 # aircraft/GPS linkage survives. Crew identity is recomputed the same
1885 # way a fresh import would, replacing whatever slot *existing*
1886 # previously had (e.g. an unclaimed airframe-log row being claimed
1887 # here for the first time).
1888 merge_into_existing(existing, conflict.kwargs, uid, fill_only=False)
1889 db.session.commit()
1890 elif decision == "new":
1891 batch_id: int = state["batch_id"]
1892 new_kwargs = dict(conflict.kwargs)
1893 _assign_pilot_identity(new_kwargs, uid)
1894 new_kwargs["import_batch_id"] = batch_id
1895 new_kwargs["source"] = "import"
1896 db.session.add(Flight(**new_kwargs))
1897 batch = db.session.get(LogbookImportBatch, batch_id)
1898 if batch is not None:
1899 batch.row_count += 1
1900 else:
1901 current_app.logger.debug( # pragma: no cover — batch was just created
1902 "import_review_resolve: batch %s vanished before resolve", batch_id
1903 )
1904 db.session.commit()
1905 else:
1906 flash(_("Invalid decision."), "danger")
1907 return redirect(url_for("pilots.import_review"))
1909 resolved[str(row_num)] = decision
1910 state["resolved"] = resolved
1911 session[_IMPORT_REVIEW_SESSION_KEY] = state
1912 session.modified = True
1914 remaining = find_conflicting_rows(
1915 parsed, mapping, uid, exclude_row_nums=_review_excluded_rows(state)
1916 )
1917 if not remaining:
1918 return _finalize_import_review(state)
1920 return redirect(url_for("pilots.import_review"))
1923@pilots_bp.route("/pilot/logbook/import/history")
1924@login_required
1925@require_pilot_access
1926def import_history() -> ResponseReturnValue:
1927 uid = _current_user_id()
1928 batches = (
1929 LogbookImportBatch.query.filter_by(pilot_user_id=uid)
1930 .order_by(LogbookImportBatch.imported_at.desc())
1931 .all()
1932 )
1933 return render_template("pilots/import_history.html", batches=batches)
1936@pilots_bp.route("/pilot/logbook/import/<int:batch_id>/rollback", methods=["POST"])
1937@login_required
1938@require_pilot_access
1939def import_rollback(batch_id: int) -> ResponseReturnValue:
1940 uid = _current_user_id()
1941 batch = db.session.get(LogbookImportBatch, batch_id)
1942 if not batch or batch.pilot_user_id != uid:
1943 abort(404)
1945 # Delete all entries belonging to this batch. Unified-model note: if any
1946 # of them were later promoted to a managed aircraft (link_entries_to_
1947 # aircraft), that's the same row now — rolling back removes the airframe
1948 # log entry too, not just the pilot's personal copy (a behaviour change
1949 # from the old two-table design, where the promoted FlightEntry was a
1950 # separate row left untouched by a pilot-side rollback).
1951 # An entry another pilot has in their logbook too is kept for them — only
1952 # this pilot's link is removed (flights/crew_removal.py).
1953 kept = 0
1954 for entry in Flight.query.filter_by(import_batch_id=batch_id).all():
1955 if not remove_flight_for_user(entry, uid):
1956 entry.import_batch_id = None
1957 kept += 1
1958 db.session.delete(batch)
1959 db.session.commit()
1961 removed = max(batch.row_count - kept, 0)
1962 flash(
1963 ngettext(
1964 "Import deleted: one entry removed.",
1965 "Import deleted: all %(count)d entries removed.",
1966 removed,
1967 count=removed,
1968 ),
1969 "success",
1970 )
1971 if kept:
1972 flash(
1973 ngettext(
1974 "One flight stays in another pilot's logbook; it was only "
1975 "removed from yours.",
1976 "%(count)d flights stay in other pilots' logbooks; they were "
1977 "only removed from yours.",
1978 kept,
1979 count=kept,
1980 ),
1981 "info",
1982 )
1983 return redirect(url_for("pilots.import_history"))
1986# ── Pilot GPS import (airplane-agnostic batch upload) ────────────────────────
1988_GPS_ALLOWED_EXTS = {".gpx", ".kml", ".csv"}
1989_GPS_MAX_BYTES = 20 * 1024 * 1024
1990_BLOCK_TOLERANCE_PILOT = _td(minutes=15)
1993def _pilot_gps_tmp_dir() -> str:
1994 from aircraft.routes import _gps_tmp_dir
1996 return _gps_tmp_dir()
1999def _pilot_tenant_id(user_id: int) -> int | None:
2000 tu = TenantUser.query.filter_by(user_id=user_id).first()
2001 return tu.tenant_id if tu else None
2004def _crew_name_suggestions(user_id: int) -> list[str]:
2005 tenant_id = _pilot_tenant_id(user_id)
2006 return tenant_pilot_names(tenant_id) if tenant_id is not None else []
2009def _pilot_match_segment(
2010 user_id: int, block_off: "_datetime", block_on: "_datetime"
2011) -> list["Flight"]:
2012 """Find Flight records the pilot is associated with that overlap block times."""
2013 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
2015 tol = _BLOCK_TOLERANCE_PILOT
2017 return Flight.query.filter( # type: ignore[no-any-return]
2018 or_(Flight.pic_user_id == user_id, Flight.second_crew_user_id == user_id),
2019 Flight.block_off_utc.isnot(None),
2020 Flight.block_on_utc.isnot(None),
2021 Flight.block_off_utc < block_on + tol,
2022 Flight.block_on_utc > block_off - tol,
2023 ).all()
2026def _pilot_fuzzy_match_segment(
2027 user_id: int, seg: dict[str, Any], block_off: "_datetime", block_on: "_datetime"
2028) -> list["Flight"]:
2029 """Fallback when no exact GPS-block overlap exists: fuzzy-match on
2030 date/route/duration against the pilot's own flights (any aircraft, or
2031 standalone) that don't yet have real GPS block data — typically a row
2032 logged manually or imported from a personal-logbook CSV. Reuses the
2033 exact same near-match scorer the pilot-logbook-import review already
2034 uses (see score_gps_candidates's docstring)."""
2035 from aircraft.gps_import import score_gps_candidates
2036 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
2038 from pilots.logbook_import import (
2039 _CANDIDATE_MIN_SCORE,
2040 _score_candidate,
2041 )
2043 kwargs = {
2044 "departure_icao": seg.get("departure_icao"),
2045 "arrival_icao": seg.get("arrival_icao"),
2046 "departure_time": block_off.time(),
2047 "arrival_time": block_on.time(),
2048 # _score_candidate sums single_pilot_se/me + multi_pilot for the
2049 # duration comparison — scoring purposes only, the confirmed
2050 # entry's real classification is decided later by pilot_role.
2051 "single_pilot_se": seg.get("flight_time_rounded_h"),
2052 }
2053 # Only rows with no real GPS block data yet — a flight that already
2054 # has its own track is a different real flight, not the one the exact
2055 # overlap check above already ruled out.
2056 same_day = Flight.query.filter(
2057 or_(Flight.pic_user_id == user_id, Flight.second_crew_user_id == user_id),
2058 Flight.date == block_off.date(),
2059 Flight.block_off_utc.is_(None),
2060 ).all()
2061 return score_gps_candidates(
2062 kwargs, same_day, _score_candidate, _CANDIDATE_MIN_SCORE
2063 )
2066def _pilot_near_date_match_segment(
2067 user_id: int, seg: dict[str, Any], block_off: "_datetime", claimed: set[int]
2068) -> list["Flight"]:
2069 """Last resort when nothing matched on the GPS date: the pilot's flights
2070 on the same route logged a day or two off — a typo in a hand-entered
2071 logbook date (see aircraft.gps_import.rank_near_date_candidates)."""
2072 from aircraft.gps_import import near_date_range, rank_near_date_candidates
2073 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
2075 from pilots.logbook_import import (
2076 _CANDIDATE_MIN_SCORE,
2077 _score_candidate,
2078 )
2080 gps_date = block_off.date()
2081 first_day, last_day = near_date_range(gps_date)
2082 kwargs = {
2083 "departure_icao": seg.get("departure_icao"),
2084 "arrival_icao": seg.get("arrival_icao"),
2085 "departure_time": block_off.time(),
2086 "single_pilot_se": seg.get("flight_time_rounded_h"),
2087 }
2088 return rank_near_date_candidates(
2089 kwargs,
2090 gps_date,
2091 Flight.query.filter(
2092 or_(Flight.pic_user_id == user_id, Flight.second_crew_user_id == user_id),
2093 Flight.date.between(first_day, last_day),
2094 Flight.block_off_utc.is_(None),
2095 Flight.id.notin_(claimed),
2096 ).all(),
2097 _score_candidate,
2098 _CANDIDATE_MIN_SCORE,
2099 )
2102def _pilot_seg_match_dict(
2103 matches: list[Any], force_ambiguous: bool = False
2104) -> dict[str, Any]:
2105 """Summarise match results into fields stored on the segment dict.
2107 force_ambiguous marks a fuzzy (non-exact) match as always needing an
2108 explicit human choice, even with just one candidate — unlike an exact
2109 GPS-block overlap, a fuzzy match is a guess and must not auto-apply.
2110 """
2111 if not matches:
2112 return {
2113 "matched_flight_id": None,
2114 "matched_flight_str": None,
2115 "matched_has_existing_track": False,
2116 "matched_aircraft_id": None,
2117 "matched_aircraft_reg": None,
2118 "matched_ambiguous": False,
2119 "matched_candidates": [],
2120 }
2121 from aircraft.routes import _gps_candidate_dict
2123 candidates = [_gps_candidate_dict(fe) for fe in matches]
2124 primary = matches[0]
2125 return {
2126 "matched_flight_id": primary.id,
2127 "matched_flight_str": candidates[0]["str"],
2128 "matched_has_existing_track": primary.gps_track_id is not None,
2129 "matched_aircraft_id": primary.aircraft_id,
2130 "matched_aircraft_reg": candidates[0]["aircraft_reg"],
2131 "matched_ambiguous": len(matches) > 1 or force_ambiguous,
2132 "matched_candidates": candidates,
2133 }
2136@pilots_bp.route("/pilot/gps-import", methods=["GET", "POST"])
2137@login_required
2138@require_pilot_access
2139def pilot_gps_import_upload() -> ResponseReturnValue:
2140 uid = _current_user_id()
2141 tenant_id = _pilot_tenant_id(uid)
2142 tenant_aircraft = (
2143 Aircraft.query.filter_by(tenant_id=tenant_id)
2144 .order_by(Aircraft.registration)
2145 .all()
2146 if tenant_id
2147 else []
2148 )
2150 if request.method == "GET":
2151 return render_template(
2152 "pilots/gps_import_upload.html",
2153 tenant_aircraft=tenant_aircraft,
2154 )
2156 # ── POST: process uploaded files ──────────────────────────────────────────
2157 mode = request.form.get("mode", "agnostic") # "one_aircraft" | "agnostic"
2158 files = request.files.getlist("gps_files")
2160 if not files or all(f.filename == "" for f in files):
2161 flash(_("Please select at least one GPS log file."), "warning")
2162 return render_template(
2163 "pilots/gps_import_upload.html", tenant_aircraft=tenant_aircraft
2164 )
2166 from aircraft.gps_import import parse_gps_file
2167 from documents.routes import _safe_join # pyright: ignore[reportMissingImports]
2168 from services.gps_archive import ( # pyright: ignore[reportMissingImports]
2169 archive_gps_file,
2170 pilot_archive_parts,
2171 )
2173 tmp_dir = _pilot_gps_tmp_dir()
2174 archive_dir = _safe_join(
2175 current_app.config.get("UPLOAD_FOLDER", "/data/uploads"),
2176 *pilot_archive_parts(uid),
2177 )
2178 parsed_meta: list[dict[str, Any]] = []
2179 errors: list[str] = []
2180 skipped_empty = 0
2182 for f in files:
2183 if not f.filename:
2184 continue # pragma: no cover – Werkzeug never yields empty-filename FileStorage objects
2185 ext = os.path.splitext(f.filename.lower())[1]
2186 if ext not in _GPS_ALLOWED_EXTS:
2187 errors.append(
2188 _(
2189 "%(fn)s: unsupported file type (use .gpx, .kml, or .csv).",
2190 fn=f.filename,
2191 )
2192 )
2193 continue
2194 data = f.read(_GPS_MAX_BYTES + 1)
2195 if len(data) > _GPS_MAX_BYTES:
2196 errors.append(_("%(fn)s: file too large (20 MB limit).", fn=f.filename))
2197 continue
2198 # Kept whatever the parser makes of it — see services.gps_archive.
2199 archive_gps_file(data, f.filename, archive_dir)
2200 try:
2201 parsed = parse_gps_file(data, f.filename)
2202 except ValueError as exc:
2203 errors.append(_("%(fn)s: %(err)s", fn=f.filename, err=str(exc)))
2204 continue
2205 if parsed.classification == "empty":
2206 skipped_empty += 1
2207 continue
2208 uid_hex = uuid.uuid4().hex
2209 safe_name = f"{uid_hex}_{secure_filename(f.filename)}"
2210 tmp_path = os.path.join(tmp_dir, safe_name)
2211 with open(tmp_path, "wb") as fh:
2212 fh.write(data)
2213 parsed_meta.append(
2214 {
2215 "tmp_path": tmp_path,
2216 "original_filename": f.filename,
2217 "format": parsed.format,
2218 "classification": parsed.classification,
2219 "trkpt_count": len(parsed.trackpoints),
2220 "hint_dep": parsed.hint_departure_icao,
2221 "hint_arr": parsed.hint_arrival_icao,
2222 "device_id": getattr(parsed, "device_id", None),
2223 }
2224 )
2226 for e in errors:
2227 flash(e, "danger")
2228 if skipped_empty:
2229 flash(
2230 ngettext(
2231 "one file skipped — no movement detected.",
2232 "%(n)s files skipped — no movement detected.",
2233 skipped_empty,
2234 n=skipped_empty,
2235 ),
2236 "info",
2237 )
2238 if not parsed_meta:
2239 flash(_("No valid GPS files to import."), "warning")
2240 return render_template(
2241 "pilots/gps_import_upload.html", tenant_aircraft=tenant_aircraft
2242 )
2244 if mode == "one_aircraft":
2245 aircraft_id = request.form.get("aircraft_id", type=int)
2246 if not aircraft_id:
2247 flash(_("Please select an aircraft."), "warning")
2248 return render_template(
2249 "pilots/gps_import_upload.html", tenant_aircraft=tenant_aircraft
2250 )
2251 session["gps_import"] = {
2252 "user_id": session["user_id"],
2253 "aircraft_id": aircraft_id,
2254 "files": parsed_meta,
2255 "skipped_empty": skipped_empty,
2256 "other_aircraft": False,
2257 "other_ac_make_model": "",
2258 "other_ac_reg": "",
2259 }
2260 session.modified = True
2261 return redirect(url_for("aircraft.gps_import_review", aircraft_id=aircraft_id))
2263 # agnostic mode
2264 session["pilot_gps_import"] = {
2265 "user_id": session["user_id"],
2266 "files": parsed_meta,
2267 "skipped_empty": skipped_empty,
2268 }
2269 session.modified = True
2270 return redirect(url_for("pilots.pilot_gps_import_review"))
2273@pilots_bp.route("/pilot/gps-import/review", methods=["GET"])
2274@login_required
2275@require_pilot_access
2276def pilot_gps_import_review() -> ResponseReturnValue:
2277 uid = _current_user_id()
2278 state = session.get("pilot_gps_import")
2279 if not state:
2280 flash(_("Session expired — please upload your GPS files again."), "warning")
2281 return redirect(url_for("pilots.pilot_gps_import_upload"))
2283 from aircraft.gps_import import (
2284 detect_segments,
2285 logged_flight_windows,
2286 merge_and_sort,
2287 merge_segments_logged_as_one,
2288 parse_gps_file,
2289 )
2290 from aircraft.routes import (
2291 _gps_tmp_dir,
2292 _linked_pilot_entries,
2293 _segment_for_session,
2294 _segment_to_dict,
2295 )
2296 from sqlalchemy import or_ # pyright: ignore[reportMissingImports]
2298 file_metas = state["files"]
2299 all_parsed = []
2300 for meta in file_metas:
2301 try:
2302 with open(meta["tmp_path"], "rb") as fh:
2303 data = fh.read()
2304 parsed = parse_gps_file(data, meta["original_filename"])
2305 parsed.hint_departure_icao = meta.get("hint_dep")
2306 parsed.hint_arrival_icao = meta.get("hint_arr")
2307 all_parsed.append(parsed)
2308 except (OSError, ValueError):
2309 flash(
2310 _(
2311 "Could not read %(fn)s — please upload again.",
2312 fn=meta["original_filename"],
2313 ),
2314 "warning",
2315 )
2316 return redirect(url_for("pilots.pilot_gps_import_upload"))
2318 merged = merge_and_sort(all_parsed)
2319 hint_dep = next(
2320 (p.hint_departure_icao for p in all_parsed if p.hint_departure_icao), None
2321 )
2322 hint_arr = next(
2323 (p.hint_arrival_icao for p in all_parsed if p.hint_arrival_icao), None
2324 )
2325 segments = detect_segments(merged, hint_dep=hint_dep, hint_arr=hint_arr)
2326 seg_dates = {seg.block_off_utc.date() for seg in segments}
2327 segments = merge_segments_logged_as_one(
2328 segments,
2329 merged,
2330 logged_flight_windows(
2331 Flight.query.filter(
2332 or_(Flight.pic_user_id == uid, Flight.second_crew_user_id == uid),
2333 Flight.date.in_(seg_dates),
2334 ).all()
2335 ),
2336 )
2337 full_segs = [_segment_to_dict(seg, i) for i, seg in enumerate(segments)]
2339 for seg in full_segs:
2340 block_off = _datetime.fromisoformat(seg["block_off_utc"])
2341 block_on = _datetime.fromisoformat(seg["block_on_utc"])
2342 matches = _pilot_match_segment(uid, block_off, block_on)
2343 is_fuzzy = False
2344 if not matches:
2345 matches = _pilot_fuzzy_match_segment(uid, seg, block_off, block_on)
2346 is_fuzzy = bool(matches)
2347 seg.update(_pilot_seg_match_dict(matches, force_ambiguous=is_fuzzy))
2348 seg["matched_near_date"] = False
2349 if seg.get("matched_flight_id") and not seg.get("matched_ambiguous"):
2350 seg["linked_pilot_entries"] = _linked_pilot_entries(
2351 seg["matched_flight_id"], uid
2352 )
2353 else:
2354 seg["linked_pilot_entries"] = []
2356 # Nothing on the GPS date: offer a same-route flight logged a day or two
2357 # off (a date typo), never one another segment already matches.
2358 claimed = {s["matched_flight_id"] for s in full_segs if s["matched_flight_id"]}
2359 for seg in full_segs:
2360 if seg["matched_flight_id"] or seg["is_ground_only"]:
2361 continue
2362 block_off = _datetime.fromisoformat(seg["block_off_utc"])
2363 near = _pilot_near_date_match_segment(uid, seg, block_off, claimed)
2364 if near:
2365 seg.update(_pilot_seg_match_dict(near, force_ambiguous=True))
2366 seg["matched_near_date"] = True
2368 tmp_dir = _gps_tmp_dir()
2369 state["segments"] = [_segment_for_session(s, tmp_dir) for s in full_segs]
2370 session["pilot_gps_import"] = state
2371 session.modified = True
2373 tenant_id = _pilot_tenant_id(uid)
2374 tenant_aircraft = (
2375 Aircraft.query.filter_by(tenant_id=tenant_id)
2376 .order_by(Aircraft.registration)
2377 .all()
2378 if tenant_id
2379 else []
2380 )
2382 from models import (
2383 AppSetting, # pyright: ignore[reportMissingImports]
2384 )
2386 tile_setting = db.session.get(AppSetting, "openaip_api_key")
2387 openaip_key = tile_setting.value if tile_setting and tile_setting.value else None
2389 return render_template(
2390 "pilots/gps_import_review.html",
2391 segments=full_segs,
2392 skipped_empty=state.get("skipped_empty", 0),
2393 confirmed_segments=state.get("confirmed_segments", {}),
2394 tenant_aircraft=tenant_aircraft,
2395 openaip_key=openaip_key,
2396 )
2399@pilots_bp.route("/pilot/gps-import/confirm-one", methods=["POST"])
2400@login_required
2401@require_pilot_access
2402def pilot_gps_import_confirm_one() -> ResponseReturnValue:
2404 from aircraft.gps_import import apply_gps_date, segment_times
2405 from aircraft.routes import _gps_cleanup, _load_segment_geojson
2407 uid = _current_user_id()
2408 state = session.get("pilot_gps_import")
2409 if not state:
2410 flash(_("Session expired — please upload your GPS files again."), "warning")
2411 return redirect(url_for("pilots.pilot_gps_import_upload"))
2413 segments_data: list[dict[str, Any]] = state.get("segments", [])
2414 if not segments_data:
2415 flash(_("No segments to import."), "warning")
2416 return redirect(url_for("pilots.pilot_gps_import_upload"))
2418 try:
2419 seg_idx = int(request.form.get("seg_idx", ""))
2420 except (ValueError, TypeError):
2421 flash(_("Invalid segment index."), "danger")
2422 return redirect(url_for("pilots.pilot_gps_import_review"))
2424 if seg_idx < 0 or seg_idx >= len(segments_data):
2425 flash(_("Invalid segment index."), "danger")
2426 return redirect(url_for("pilots.pilot_gps_import_review"))
2428 confirmed = state.get("confirmed_segments", {})
2429 if str(seg_idx) in confirmed:
2430 flash(_("This segment has already been confirmed."), "info")
2431 return redirect(url_for("pilots.pilot_gps_import_review"))
2433 pilot_role = request.form.get("pilot_role", "pic")
2434 if pilot_role not in ("pic", "dual", "none"):
2435 pilot_role = "pic"
2437 # ── Skip ─────────────────────────────────────────────────────────────────
2438 if request.form.get("skip") == "1":
2439 confirmed[str(seg_idx)] = "skip"
2440 state["confirmed_segments"] = confirmed
2441 session["pilot_gps_import"] = state
2442 session.modified = True
2443 if len(confirmed) == len(segments_data):
2444 _gps_cleanup(state)
2445 session.pop("pilot_gps_import", None)
2446 imported = sum(1 for v in confirmed.values() if v != "skip")
2447 skipped_count = len(segments_data) - imported
2448 if imported > 0:
2449 flash(
2450 ngettext(
2451 "one flight imported successfully.",
2452 "%(n)s flights imported successfully.",
2453 imported,
2454 n=imported,
2455 ),
2456 "success",
2457 )
2458 flash(
2459 ngettext(
2460 "one segment skipped.",
2461 "%(n)s segments skipped.",
2462 skipped_count,
2463 n=skipped_count,
2464 ),
2465 "info",
2466 )
2467 if imported > 0 and pilot_role in ("pic", "dual"):
2468 return redirect(url_for("pilots.logbook"))
2469 return redirect(url_for("pilots.logbook"))
2470 flash(_("Segment skipped."), "info")
2471 return redirect(url_for("pilots.pilot_gps_import_review"))
2473 # ── Confirm ───────────────────────────────────────────────────────────────
2474 seg = segments_data[seg_idx]
2475 file_metas = state.get("files", [])
2476 block_off = _datetime.fromisoformat(seg["block_off_utc"])
2477 block_on = _datetime.fromisoformat(seg["block_on_utc"])
2479 dep_icao = (
2480 request.form.get("dep_icao") or seg.get("departure_icao") or ""
2481 ).strip().upper()[:4] or "????"
2482 arr_icao = (
2483 request.form.get("arr_icao") or seg.get("arrival_icao") or ""
2484 ).strip().upper()[:4] or "????"
2485 nature = (request.form.get("nature") or "").strip()[:100] or None
2486 remarks = (request.form.get("remarks") or "").strip() or None
2488 geojson = _load_segment_geojson(seg)
2489 source_filename = (
2490 file_metas[0]["original_filename"] if len(file_metas) == 1 else None
2491 )
2492 device_id = next(
2493 (m.get("device_id") for m in file_metas if m.get("device_id")), None
2494 )
2496 create_pilot_entry = pilot_role in ("pic", "dual")
2497 if seg.get("matched_ambiguous"):
2498 # A candidate picker was rendered — respect the human's explicit
2499 # choice (including "none of these", submitted as "") instead of
2500 # always falling back to the top-scored candidate.
2501 picked = request.form.get("matched_flight_id", "")
2502 matched_flight_id = int(picked) if picked.strip().isdigit() else None
2503 else:
2504 matched_flight_id = seg.get("matched_flight_id")
2505 entry: Flight | None = None
2506 gps_track: GpsTrack | None = None
2507 ac: Aircraft | None = None
2509 if matched_flight_id:
2510 # Link GPS track to the existing matched Flight
2511 existing = db.session.get(Flight, matched_flight_id)
2512 if existing:
2513 old_track_id = existing.gps_track_id
2514 gps_track = GpsTrack(
2515 source_filename=source_filename,
2516 device_id=device_id,
2517 block_off_utc=block_off,
2518 block_on_utc=block_on,
2519 departure_icao=dep_icao,
2520 arrival_icao=arr_icao,
2521 geojson=geojson,
2522 )
2523 db.session.add(gps_track)
2524 db.session.flush()
2525 existing.gps_track_id = gps_track.id
2526 existing.block_off_utc = block_off
2527 existing.block_on_utc = block_on
2528 apply_gps_date(existing, seg, block_off)
2529 if old_track_id and old_track_id != gps_track.id:
2530 # Re-confirming an already-linked match (the review page
2531 # warns this replaces the track) — drop the superseded
2532 # row instead of leaving it as a permanent orphan.
2533 old_track = db.session.get(GpsTrack, old_track_id)
2534 if old_track is not None:
2535 db.session.delete(old_track)
2536 # Unified model: this Flight row already covers both crew slots
2537 # (pic_user_id/second_crew_user_id), so setting gps_track_id
2538 # above already applies to whichever other pilot occupies the
2539 # other slot too — no separate per-pilot row left to update.
2540 db.session.flush()
2541 entry = existing
2542 else:
2543 matched_flight_id = None # stale — fall through to new entry
2545 if not matched_flight_id:
2546 resolution = request.form.get("resolution", "other_aircraft")
2548 if resolution == "managed_aircraft":
2549 aircraft_id = request.form.get("aircraft_id", type=int)
2550 if aircraft_id:
2551 tenant_id = _pilot_tenant_id(uid)
2552 ac = (
2553 Aircraft.query.filter_by(
2554 id=aircraft_id, tenant_id=tenant_id
2555 ).first()
2556 if tenant_id
2557 else None
2558 )
2560 if ac:
2561 # Create a new Flight for the managed aircraft
2562 gps_track = GpsTrack(
2563 source_filename=source_filename,
2564 device_id=device_id,
2565 block_off_utc=block_off,
2566 block_on_utc=block_on,
2567 departure_icao=dep_icao,
2568 arrival_icao=arr_icao,
2569 geojson=geojson,
2570 )
2571 db.session.add(gps_track)
2572 db.session.flush()
2573 ac_times = segment_times(
2574 seg, getattr(ac, "logbook_time_precision", "tenth_hour")
2575 )
2576 entry = Flight(
2577 aircraft_id=ac.id,
2578 date=block_off.date(),
2579 departure_icao=dep_icao,
2580 arrival_icao=arr_icao,
2581 departure_time=block_off.time().replace(tzinfo=None),
2582 arrival_time=block_on.time().replace(tzinfo=None),
2583 takeoff_time=ac_times.takeoff_time,
2584 landing_time=ac_times.landing_time,
2585 flight_time=ac_times.airborne_time,
2586 engine_time=ac_times.block_time,
2587 landing_count=seg.get("landing_count") or 0,
2588 nature_of_flight=nature,
2589 source="gps_import",
2590 block_off_utc=block_off,
2591 block_on_utc=block_on,
2592 gps_track_id=gps_track.id,
2593 )
2594 db.session.add(entry)
2595 db.session.flush()
2596 else:
2597 # Other / external aircraft — pilot-only, no Flight
2598 if geojson:
2599 gps_track = GpsTrack(
2600 source_filename=source_filename,
2601 device_id=device_id,
2602 block_off_utc=block_off,
2603 block_on_utc=block_on,
2604 departure_icao=dep_icao,
2605 arrival_icao=arr_icao,
2606 geojson=geojson,
2607 )
2608 db.session.add(gps_track)
2609 db.session.flush()
2610 create_pilot_entry = (
2611 True # always create logbook entry for external aircraft
2612 )
2614 if create_pilot_entry:
2615 from flights.routes import apply_pilot_identity
2617 times = segment_times(seg, "tenth_hour")
2619 if entry is None:
2620 # Other/external aircraft, no existing match — standalone row.
2621 other_reg = (request.form.get("other_reg") or "").strip().upper()
2622 other_mm = (request.form.get("other_make_model") or "").strip()
2623 entry = Flight(
2624 date=block_off.date(),
2625 other_aircraft_type=other_mm or None,
2626 other_aircraft_registration=other_reg or None,
2627 departure_icao=dep_icao,
2628 departure_time=block_off.time().replace(tzinfo=None),
2629 arrival_icao=arr_icao,
2630 arrival_time=block_on.time().replace(tzinfo=None),
2631 source="gps_import",
2632 gps_track_id=gps_track.id if gps_track else None,
2633 )
2634 db.session.add(entry)
2635 db.session.flush()
2637 # The pilot's figures come from the block time (see
2638 # flights.routes.pilot_time) — recorded as engine_time unless the
2639 # flight already has one (e.g. from its engine counters). A matched
2640 # flight's own airborne flight_time is never replaced by it.
2641 if entry.engine_time is None:
2642 entry.engine_time = times.block_time
2643 if entry.flight_time is None:
2644 entry.flight_time = times.airborne_time
2645 # GPS-derived landing count has no day/night split — treat them all
2646 # as day landings, same simplification the old standalone pilot
2647 # entry made.
2648 entry.landings_day = seg.get("landing_count") or 0
2649 if remarks:
2650 entry.notes = remarks
2651 entry_ac: Aircraft | None = (
2652 ac if ac is not None else entry.aircraft # type: ignore[assignment]
2653 )
2654 # apply_pilot_identity only knows "pic"/"dual" — an external-aircraft
2655 # segment always gets a personal entry recorded (create_pilot_entry
2656 # is forced True above) even when pilot_role is "none"; default that
2657 # case to "pic" rather than skipping identity resolution entirely.
2658 apply_pilot_identity(
2659 entry,
2660 entry_ac,
2661 uid,
2662 pilot_role if pilot_role != "none" else "pic",
2663 )
2665 db.session.commit()
2667 confirmed[str(seg_idx)] = entry.id if entry else 0
2668 state["confirmed_segments"] = confirmed
2669 session["pilot_gps_import"] = state
2670 session.modified = True
2672 all_handled = len(confirmed) == len(segments_data)
2673 if all_handled:
2674 _gps_cleanup(state)
2675 session.pop("pilot_gps_import", None)
2676 total = sum(1 for v in confirmed.values() if v != "skip")
2677 flash(
2678 ngettext(
2679 "one flight imported successfully.",
2680 "%(n)s flights imported successfully.",
2681 total,
2682 n=total,
2683 ),
2684 "success",
2685 )
2686 return redirect(url_for("pilots.logbook"))
2688 flash(_("Flight confirmed."), "success")
2689 return redirect(url_for("pilots.pilot_gps_import_review"))