Coverage for app/models.py: 100%
1461 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-17 15:29 +0000
1import enum
2import secrets
3from datetime import UTC, date, datetime
4from typing import Any, ClassVar
6from flask_sqlalchemy import SQLAlchemy # pyright: ignore[reportMissingImports]
7from sqlalchemy import text
9db = SQLAlchemy()
12def N_(s: str) -> str:
13 """Marks a string for pybabel extraction without translating it here —
14 this model layer has no i18n dependency by design (see
15 MaintenanceTrigger.service_basis), so the actual gettext() lookup
16 happens later, at the template/route layer that renders a *LABELS
17 dict value. A no-op at runtime; its literal-string argument is what
18 lets pybabel discover these msgids despite them only being selected
19 dynamically (by dict key) rather than passed straight to gettext()."""
20 return s
23def reset_schema(db: SQLAlchemy) -> None:
24 """Drop and recreate the demo database's public schema, then rebuild tables.
26 Demo mode never runs Alembic migrations (see docker-init-db.py) and its
27 Postgres volume persists across image updates. db.drop_all() only drops
28 tables declared in the *current* models — a renamed/removed model (e.g.
29 a past FlightEntry -> Flight rename) leaves the old physical table behind,
30 invisible to drop_all() but still enforcing its FK constraints, which can
31 block dropping tables that are still declared. Nuking the whole schema
32 sidesteps that: nothing survives for a stale constraint to reference.
34 On SQLite (dev/test), schemas don't work this way and each test already
35 starts from a fresh in-memory database, so just create_all().
36 """
37 if db.engine.dialect.name == "postgresql":
38 with db.engine.begin() as conn:
39 conn.execute(text("DROP SCHEMA public CASCADE"))
40 conn.execute(text("CREATE SCHEMA public"))
41 db.create_all()
44class Role(str, enum.Enum):
45 ADMIN = "admin"
46 OWNER = "owner"
47 PILOT = "pilot" # Pilot/Renter: log flights + view own; no config/cost edits
48 MAINTENANCE = (
49 "maintenance" # Maintenance: view+update maintenance; no flights/aircraft edits
50 )
51 VIEWER = "viewer" # Read-only across tenant
52 STUDENT = "student" # Student pilot — requires instructor sign-off on solo entries
53 INSTRUCTOR = "instructor" # Flight instructor — can countersign student entries
56class OperatingModel(str, enum.Enum):
57 SOLE_PILOT = "sole_pilot"
58 SOLE_OPERATOR = "sole_operator"
59 SHARED_OWNERSHIP = "shared_ownership"
60 FLIGHT_CLUB = "flight_club"
61 FLIGHT_SCHOOL = "flight_school"
64class PermissionBit:
65 """Bitmask constants for UserAircraftAccess.permissions_mask."""
67 VIEW_AIRCRAFT = 0x01
68 EDIT_AIRCRAFT = 0x02
69 READ_MAINT_FULL = 0x04
70 READ_MAINT_LIMITED = 0x08
71 WRITE_MAINTENANCE = 0x10
72 EDIT_COMPONENTS = 0x20
73 WRITE_LOGBOOK = 0x40
74 RESERVE_AIRCRAFT = 0x80
75 ALL = 0xFF
77 # Default masks per role (used when no explicit per-aircraft row exists)
78 ROLE_DEFAULTS: ClassVar[dict[str, int]] = {
79 "admin": ALL,
80 "owner": ALL,
81 "pilot": VIEW_AIRCRAFT | READ_MAINT_LIMITED | WRITE_LOGBOOK | RESERVE_AIRCRAFT,
82 "student": VIEW_AIRCRAFT | READ_MAINT_LIMITED,
83 "instructor": VIEW_AIRCRAFT
84 | READ_MAINT_FULL
85 | WRITE_LOGBOOK
86 | RESERVE_AIRCRAFT,
87 "maintenance": VIEW_AIRCRAFT
88 | EDIT_AIRCRAFT
89 | READ_MAINT_FULL
90 | WRITE_MAINTENANCE
91 | EDIT_COMPONENTS,
92 "viewer": VIEW_AIRCRAFT | READ_MAINT_FULL,
93 }
96class Tenant(db.Model):
97 __tablename__ = "tenants"
99 id = db.Column(db.Integer, primary_key=True)
100 name = db.Column(db.String(128), nullable=False)
101 slug = db.Column(db.String(64), nullable=True, unique=True)
102 is_active = db.Column(db.Boolean, nullable=False, default=True)
103 require_totp = db.Column(db.Boolean, nullable=False, default=False)
104 created_at = db.Column(
105 db.DateTime(timezone=True),
106 nullable=False,
107 default=lambda: datetime.now(UTC),
108 )
110 users = db.relationship(
111 "TenantUser", back_populates="tenant", cascade="all, delete-orphan"
112 )
113 aircraft = db.relationship(
114 "Aircraft", back_populates="tenant", cascade="all, delete-orphan"
115 )
118class User(db.Model):
119 __tablename__ = "users"
121 id = db.Column(db.Integer, primary_key=True)
122 email = db.Column(db.String(255), unique=True, nullable=False)
123 password_hash = db.Column(db.String(255), nullable=False)
124 totp_secret = db.Column(db.String(64), nullable=True, default=None)
125 is_active = db.Column(db.Boolean, nullable=False, default=True)
126 name = db.Column(db.String(128), nullable=True)
127 language = db.Column(db.String(8), nullable=True, default="en")
128 theme = db.Column(db.String(8), nullable=True, default=None)
129 # Phase 23: capability flags — orthogonal to role; allow cross-role flows
130 is_pilot = db.Column(db.Boolean, nullable=False, default=False)
131 is_maintenance = db.Column(db.Boolean, nullable=False, default=False)
132 view_only = db.Column(db.Boolean, nullable=False, default=False)
133 # Phase 29: instance-level super admin — set on the very first user created
134 is_instance_admin = db.Column(db.Boolean, nullable=False, default=False)
135 created_at = db.Column(
136 db.DateTime(timezone=True),
137 nullable=False,
138 default=lambda: datetime.now(UTC),
139 )
141 tenants = db.relationship(
142 "TenantUser", back_populates="user", cascade="all, delete-orphan"
143 )
145 @property
146 def display_name(self) -> str:
147 return (self.name or "").strip() or self.email.split("@")[0]
150class TenantUser(db.Model):
151 __tablename__ = "tenant_users"
153 user_id = db.Column(
154 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
155 )
156 tenant_id = db.Column(
157 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True
158 )
159 role = db.Column(db.Enum(Role), nullable=False, default=Role.OWNER)
161 user = db.relationship("User", back_populates="tenants")
162 tenant = db.relationship("Tenant", back_populates="users")
165class UserAircraftAccess(db.Model):
166 """Grants a non-owner/admin user explicit access to a specific aircraft."""
168 __tablename__ = "user_aircraft_access"
170 user_id = db.Column(
171 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
172 )
173 aircraft_id = db.Column(
174 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True
175 )
176 # Phase 23: optional override mask; when NULL the role's default mask applies
177 permissions_mask = db.Column(db.Integer, nullable=True)
180class UserAllAircraftAccess(db.Model):
181 """Grants a user access to every aircraft in a tenant (past and future).
183 Admin users bypass access checks entirely and never need this row.
184 For non-admin users, this row grants access to every aircraft in the
185 tenant using the supplied permissions_mask (or the role default when NULL).
186 """
188 __tablename__ = "user_all_aircraft_access"
190 user_id = db.Column(
191 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
192 )
193 tenant_id = db.Column(
194 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True
195 )
196 permissions_mask = db.Column(db.Integer, nullable=True)
199class UserInvitation(db.Model):
200 """Time-limited invitation for a new user to join a tenant."""
202 __tablename__ = "user_invitations"
204 id = db.Column(db.Integer, primary_key=True)
205 token = db.Column(
206 db.String(64),
207 unique=True,
208 nullable=False,
209 default=lambda: secrets.token_urlsafe(32),
210 )
211 tenant_id = db.Column(
212 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
213 )
214 invited_by_user_id = db.Column(
215 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
216 )
217 email = db.Column(db.String(255), nullable=True)
218 display_name = db.Column(db.String(128), nullable=True)
219 role = db.Column(db.Enum(Role), nullable=False, default=Role.PILOT)
220 aircraft_ids = db.Column(db.JSON, nullable=True)
221 expires_at = db.Column(db.DateTime(timezone=True), nullable=False)
222 accepted_at = db.Column(db.DateTime(timezone=True), nullable=True)
223 created_at = db.Column(
224 db.DateTime(timezone=True),
225 nullable=False,
226 default=lambda: datetime.now(UTC),
227 )
229 tenant = db.relationship("Tenant")
230 invited_by = db.relationship("User", foreign_keys=[invited_by_user_id])
232 @property
233 def is_expired(self) -> bool:
234 exp = self.expires_at
235 # SQLite returns naive datetimes; compare with naive UTC in that case
236 if exp.tzinfo is None:
237 return datetime.now(UTC).replace(tzinfo=None) > exp
238 return datetime.now(UTC) > exp
240 @property
241 def is_accepted(self) -> bool:
242 return self.accepted_at is not None
245# ── Phase 29: Password Reset Token ───────────────────────────────────────────
248class PasswordResetToken(db.Model):
249 """One-time password reset token generated by the instance admin for a tenant owner."""
251 __tablename__ = "password_reset_tokens"
253 id = db.Column(db.Integer, primary_key=True)
254 token = db.Column(
255 db.String(64),
256 unique=True,
257 nullable=False,
258 default=lambda: secrets.token_urlsafe(32),
259 )
260 user_id = db.Column(
261 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
262 )
263 generated_by_user_id = db.Column(
264 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
265 )
266 created_at = db.Column(
267 db.DateTime(timezone=True),
268 nullable=False,
269 default=lambda: datetime.now(UTC),
270 )
271 expires_at = db.Column(db.DateTime(timezone=True), nullable=False)
272 used_at = db.Column(db.DateTime(timezone=True), nullable=True)
274 user = db.relationship("User", foreign_keys=[user_id])
275 generated_by = db.relationship("User", foreign_keys=[generated_by_user_id])
277 @property
278 def is_expired(self) -> bool:
279 exp = self.expires_at
280 if exp.tzinfo is None:
281 return datetime.now(UTC).replace(tzinfo=None) > exp
282 return datetime.now(UTC) > exp
284 @property
285 def is_used(self) -> bool:
286 return self.used_at is not None
289# ── Phase 26: Tenant Profile ─────────────────────────────────────────────────
292class TenantProfile(db.Model):
293 """Instance-level profile collected during the onboarding wizard."""
295 __tablename__ = "tenant_profiles"
297 id = db.Column(db.Integer, primary_key=True)
298 tenant_id = db.Column(
299 db.Integer,
300 db.ForeignKey("tenants.id", ondelete="CASCADE"),
301 nullable=False,
302 unique=True,
303 )
304 operating_model = db.Column(db.Enum(OperatingModel), nullable=True)
305 # 0 → logbook-only (no aircraft UI)
306 # 1 → single-aircraft (hides fleet-level widgets)
307 # N → show "Add aircraft" CTA until N aircraft exist
308 planned_aircraft_count = db.Column(db.Integer, nullable=True)
309 allows_rental = db.Column(db.Boolean, nullable=False, default=False)
310 club_name = db.Column(db.String(128), nullable=True)
311 school_name = db.Column(db.String(128), nullable=True)
312 organisation_name = db.Column(db.String(128), nullable=True)
313 setup_complete = db.Column(db.Boolean, nullable=False, default=False)
314 # Phase 34: optional email subject prefix, e.g. "[MyClub]"
315 email_subject_prefix = db.Column(db.String(64), nullable=True)
316 # Phase 37c: "off" | "warn" | "block" — enforcement level when a renter
317 # (non is_owner user) books an aircraft without a valid RenterAuthorization.
318 rental_authorization_policy = db.Column(
319 db.String(8), nullable=False, default="warn"
320 )
321 # Phase 37f: "warn" | "block" — enforcement level when creating/confirming a
322 # reservation on an aircraft with an open grounding snag. Owners always get
323 # warn-level at most (they may be booking the aircraft for the shop visit).
324 grounded_reservation_policy = db.Column(
325 db.String(8), nullable=False, default="warn"
326 )
327 # Phase 39c: days a co-owner capital balance may stay negative before the
328 # billing dashboard flags it. Only editable/read on a shared_ownership
329 # tenant — see config.update_profile's gating.
330 co_owner_overdue_days = db.Column(db.Integer, nullable=False, default=30)
332 # cascade="all, delete-orphan" on the backref: without it, deleting a
333 # Tenant object through the ORM (e.g. the demo-seed wipe cycle) tries to
334 # NULL this row's NOT NULL tenant_id instead of deleting it, since this
335 # is the only Tenant-side relationship that previously relied solely on
336 # the DB's ondelete=CASCADE rather than mirroring it at the ORM level
337 # (see Tenant.users/Tenant.aircraft above).
338 tenant = db.relationship(
339 "Tenant",
340 backref=db.backref("profile", uselist=False, cascade="all, delete-orphan"),
341 )
344# ── Phase 1: Aircraft & Component Models ──────────────────────────────────────
347# Application-level component type constants.
348# Stored as plain strings in the DB so new types never require a migration.
349class ComponentType:
350 AIRFRAME = "airframe"
351 ENGINE = "engine"
352 PROPELLER = "propeller"
353 AVIONICS = "avionics"
354 OTHER = "other"
356 ALL: ClassVar[set[str]] = {AIRFRAME, ENGINE, PROPELLER, AVIONICS, OTHER}
359class Aircraft(db.Model):
360 __tablename__ = "aircraft"
362 id = db.Column(db.Integer, primary_key=True)
363 tenant_id = db.Column(
364 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
365 )
366 registration = db.Column(db.String(16), nullable=False)
367 make = db.Column(db.String(64), nullable=False)
368 model = db.Column(db.String(64), nullable=False)
369 year = db.Column(db.Integer, nullable=True)
370 has_flight_counter = db.Column(db.Boolean, nullable=False, default=True)
371 flight_counter_offset = db.Column(db.Numeric(3, 1), nullable=False, default=0.3)
372 fuel_flow = db.Column(
373 db.Numeric(6, 2), nullable=True
374 ) # typical fuel consumption in L/h
375 fuel_type = db.Column(
376 db.String(8), nullable=False, default="avgas"
377 ) # "avgas" | "jet_a1"
378 # Oil consumption warning threshold in L/h; null = no warning on the
379 # cost dashboard.
380 oil_warning_lph = db.Column(db.Numeric(4, 2), nullable=True)
381 # Both insurance_expiry and arc_expiry are synced caches, kept in step with
382 # the currently-active (non-superseded) Document of the matching doc_type
383 # by app/documents/routes.py — not directly user-editable.
384 insurance_expiry = db.Column(db.Date, nullable=True)
385 arc_expiry = db.Column(db.Date, nullable=True)
386 # Phase 30: GPS import time rounding preference
387 logbook_time_precision = db.Column(
388 db.String(16), nullable=False, default="tenth_hour"
389 ) # "tenth_hour" | "minute"
390 # Phase 36: optional engine-overhaul reserve accrual rate, surfaced as a
391 # line item on the cost dashboard; null = not configured.
392 reserve_hourly_rate = db.Column(db.Numeric(8, 2), nullable=True)
393 # Phase 39: hourly rate charged to the flying co-owner, and the date from
394 # which co-owner billing considers expenses/flights. Both null until the
395 # owners form is first saved.
396 co_owner_hourly_rate = db.Column(db.Numeric(8, 2), nullable=True)
397 co_owner_billing_start = db.Column(db.Date, nullable=True)
398 # Phase 39g: reserve/overhaul fund contribution — at most one of the two
399 # may be set (mode exclusivity validated on the owners form, not here).
400 reserve_contribution_hourly = db.Column(db.Numeric(8, 2), nullable=True)
401 reserve_contribution_monthly = db.Column(db.Numeric(8, 2), nullable=True)
402 # Archived (sold/retired) aircraft keep their full history but are hidden
403 # from active-fleet views, reservations, and notification passes.
404 archived_at = db.Column(db.DateTime(timezone=True), nullable=True)
405 # Free-text "about this aircraft" blurb shown only on the public showcase
406 # page (ShareToken.access_level == "showcase") — never on the owner-facing
407 # aircraft pages or the operational summary/full share tiers.
408 showcase_blurb = db.Column(db.Text, nullable=True)
409 created_at = db.Column(
410 db.DateTime(timezone=True),
411 nullable=False,
412 default=lambda: datetime.now(UTC),
413 )
415 tenant = db.relationship("Tenant", back_populates="aircraft")
416 components = db.relationship(
417 "Component", back_populates="aircraft", cascade="all, delete-orphan"
418 )
419 flights = db.relationship(
420 "Flight", back_populates="aircraft", cascade="all, delete-orphan"
421 )
422 maintenance_triggers = db.relationship(
423 "MaintenanceTrigger", back_populates="aircraft", cascade="all, delete-orphan"
424 )
425 expenses = db.relationship(
426 "Expense", back_populates="aircraft", cascade="all, delete-orphan"
427 )
428 refuels = db.relationship(
429 "Refuel", back_populates="aircraft", cascade="all, delete-orphan"
430 )
431 fuel_tanks = db.relationship(
432 "AircraftFuelTank",
433 back_populates="aircraft",
434 cascade="all, delete-orphan",
435 order_by="AircraftFuelTank.sort_order",
436 )
437 documents = db.relationship(
438 "Document",
439 back_populates="aircraft",
440 cascade="all, delete-orphan",
441 single_parent=True,
442 foreign_keys="Document.aircraft_id",
443 primaryjoin="Document.aircraft_id == Aircraft.id",
444 )
445 share_tokens = db.relationship(
446 "ShareToken",
447 back_populates="aircraft",
448 cascade="all, delete-orphan",
449 )
450 snags = db.relationship(
451 "Snag",
452 back_populates="aircraft",
453 cascade="all, delete-orphan",
454 )
455 maintenance_downtimes = db.relationship(
456 "MaintenanceDowntime",
457 back_populates="aircraft",
458 cascade="all, delete-orphan",
459 )
460 wb_config = db.relationship(
461 "WeightBalanceConfig",
462 back_populates="aircraft",
463 cascade="all, delete-orphan",
464 uselist=False,
465 )
466 reservations = db.relationship(
467 "Reservation",
468 back_populates="aircraft",
469 cascade="all, delete-orphan",
470 )
471 booking_settings = db.relationship(
472 "AircraftBookingSettings",
473 back_populates="aircraft",
474 cascade="all, delete-orphan",
475 uselist=False,
476 )
477 amp_declaration = db.relationship(
478 "AmpDeclaration",
479 back_populates="aircraft",
480 cascade="all, delete-orphan",
481 uselist=False,
482 )
483 amp_revisions = db.relationship(
484 "AmpRevision",
485 back_populates="aircraft",
486 cascade="all, delete-orphan",
487 order_by="AmpRevision.revision_date.asc().nullslast(), AmpRevision.id.asc()",
488 )
489 photos = db.relationship(
490 "AircraftPhoto",
491 back_populates="aircraft",
492 cascade="all, delete-orphan",
493 order_by="AircraftPhoto.sort_order",
494 )
495 airworthiness_statuses = db.relationship(
496 "AirworthinessDocumentStatus",
497 back_populates="aircraft",
498 cascade="all, delete-orphan",
499 )
500 installed_stcs = db.relationship(
501 "InstalledSTC",
502 back_populates="aircraft",
503 cascade="all, delete-orphan",
504 )
505 ad_sb_items = db.relationship(
506 "AdSbItem",
507 back_populates="aircraft",
508 cascade="all, delete-orphan",
509 order_by="AdSbItem.reference",
510 )
511 avionics_units = db.relationship(
512 "AvionicsUnit",
513 back_populates="aircraft",
514 cascade="all, delete-orphan",
515 order_by="AvionicsUnit.role",
516 )
517 equipment_wishlist_items = db.relationship(
518 "EquipmentWishlistItem",
519 back_populates="aircraft",
520 cascade="all, delete-orphan",
521 order_by="EquipmentWishlistItem.created_at",
522 )
523 owners = db.relationship(
524 "AircraftOwner",
525 back_populates="aircraft",
526 cascade="all, delete-orphan",
527 order_by="AircraftOwner.share_pct.desc()",
528 )
530 @property
531 def cover_photo(self) -> "AircraftPhoto | None":
532 return self.photos[0] if self.photos else None
534 @property
535 def total_engine_hours(self) -> "float | None":
536 """Current engine hours — the highest engine_time_counter_end across all flight entries."""
537 val = db.session.execute(
538 db.select(db.func.max(Flight.engine_time_counter_end)).where(
539 Flight.aircraft_id == self.id
540 )
541 ).scalar()
542 return float(val) if val is not None else None
544 @property
545 def total_flight_hours(self) -> "float | None":
546 """Current flight hours — the highest flight_time_counter_end across all flight entries."""
547 val = db.session.execute(
548 db.select(db.func.max(Flight.flight_time_counter_end)).where(
549 Flight.aircraft_id == self.id
550 )
551 ).scalar()
552 return float(val) if val is not None else None
554 @staticmethod
555 def engine_hours_by_id(aircraft_ids: "list[int]") -> "dict[int, float | None]":
556 """Current engine hours for a whole fleet in one aggregate query.
558 Returns an entry for every requested id (None when the aircraft has no
559 flight entries or no engine counter values)."""
560 totals: dict[int, float | None] = {aid: None for aid in aircraft_ids}
561 if not aircraft_ids:
562 return totals
563 rows = db.session.execute(
564 db.select(
565 Flight.aircraft_id,
566 db.func.max(Flight.engine_time_counter_end),
567 )
568 .where(Flight.aircraft_id.in_(aircraft_ids))
569 .group_by(Flight.aircraft_id)
570 ).all()
571 for aid, max_end in rows:
572 totals[aid] = float(max_end) if max_end is not None else None
573 return totals
575 @staticmethod
576 def flight_hours_by_id(aircraft_ids: "list[int]") -> "dict[int, float | None]":
577 """Current flight hours for a whole fleet in one aggregate query.
579 Mirrors ``engine_hours_by_id``, reading ``flight_time_counter_end``
580 instead — used for maintenance triggers whose ``hours_basis`` is
581 ``HoursBasis.FLIGHT``."""
582 totals: dict[int, float | None] = {aid: None for aid in aircraft_ids}
583 if not aircraft_ids:
584 return totals
585 rows = db.session.execute(
586 db.select(
587 Flight.aircraft_id,
588 db.func.max(Flight.flight_time_counter_end),
589 )
590 .where(Flight.aircraft_id.in_(aircraft_ids))
591 .group_by(Flight.aircraft_id)
592 ).all()
593 for aid, max_end in rows:
594 totals[aid] = float(max_end) if max_end is not None else None
595 return totals
597 @property
598 def total_landings(self) -> "int | None":
599 """Current cumulative landing count — sum of landing_count across all
600 flight entries (unlike the engine/flight hour counters, landings
601 aren't a running total already, so this sums rather than maxes)."""
602 val = db.session.execute(
603 db.select(db.func.sum(Flight.landing_count)).where(
604 Flight.aircraft_id == self.id
605 )
606 ).scalar()
607 return int(val) if val is not None else None
609 @staticmethod
610 def landings_by_id(aircraft_ids: "list[int]") -> "dict[int, int | None]":
611 """Current cumulative landing count for a whole fleet in one aggregate
612 query. Returns an entry for every requested id (None when the
613 aircraft has no flight entries with a recorded landing count)."""
614 totals: dict[int, int | None] = {aid: None for aid in aircraft_ids}
615 if not aircraft_ids:
616 return totals
617 rows = db.session.execute(
618 db.select(
619 Flight.aircraft_id,
620 db.func.sum(Flight.landing_count),
621 )
622 .where(Flight.aircraft_id.in_(aircraft_ids))
623 .group_by(Flight.aircraft_id)
624 ).all()
625 for aid, total in rows:
626 totals[aid] = int(total) if total is not None else None
627 return totals
629 @property
630 def is_archived(self) -> bool:
631 return self.archived_at is not None
633 @property
634 def is_grounded(self) -> bool:
635 """True when any unresolved grounding snag exists, or insurance/the
636 ARC has expired."""
637 from datetime import date as _date
639 today = _date.today()
640 if self.insurance_expiry is not None and self.insurance_expiry < today:
641 return True
642 if self.arc_expiry is not None and self.arc_expiry < today:
643 return True
644 return any(s.is_grounding and s.is_open for s in self.snags)
646 @staticmethod
647 def _expiry_status(expiry: date | None) -> str:
648 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok' for a given date."""
649 if expiry is None:
650 return "ok"
651 delta = (expiry - date.today()).days
652 if delta < 0:
653 return "expired"
654 if delta <= 30:
655 return "expiring_soon"
656 return "ok"
658 @property
659 def insurance_status(self) -> str:
660 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok'."""
661 return self._expiry_status(self.insurance_expiry)
663 @property
664 def arc_status(self) -> str:
665 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok'."""
666 return self._expiry_status(self.arc_expiry)
669class AircraftPhoto(db.Model):
670 __tablename__ = "aircraft_photos"
671 __table_args__ = (db.Index("ix_aircraft_photos_aircraft_id", "aircraft_id"),)
673 id = db.Column(db.Integer, primary_key=True)
674 aircraft_id = db.Column(
675 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
676 )
677 filename = db.Column(db.String(512), nullable=False)
678 original_filename = db.Column(db.String(256), nullable=False)
679 sort_order = db.Column(db.Integer, nullable=False, default=1)
680 uploaded_at = db.Column(
681 db.DateTime(timezone=True),
682 nullable=False,
683 default=lambda: datetime.now(UTC),
684 )
685 uploaded_by_user_id = db.Column(
686 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
687 )
689 aircraft = db.relationship("Aircraft", back_populates="photos")
692class Component(db.Model):
693 """
694 A generic aircraft component (engine, propeller, avionics, …).
696 Common fields live as columns; type-specific attributes go in `extras` (JSON).
697 `removed_at = NULL` means the component is currently installed.
698 `position` disambiguates multiple components of the same type, e.g. "left" / "right"
699 for a twin-engine aircraft.
700 """
702 __tablename__ = "components"
704 id = db.Column(db.Integer, primary_key=True)
705 aircraft_id = db.Column(
706 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
707 )
708 # Plain string, validated at application layer — no DB ENUM so new types
709 # never require a schema migration.
710 type = db.Column(db.String(32), nullable=False)
711 # Optional slot label: "left", "right", "1", "2", "center", …
712 position = db.Column(db.String(32), nullable=True)
714 make = db.Column(db.String(64), nullable=False)
715 model = db.Column(db.String(64), nullable=False)
716 serial_number = db.Column(db.String(64), nullable=True)
717 # Hours on this component when it was installed on this aircraft
718 time_at_install = db.Column(db.Numeric(8, 1), nullable=True)
720 installed_at = db.Column(db.Date, nullable=True)
721 removed_at = db.Column(db.Date, nullable=True) # NULL = currently installed
723 # Life limits: hours between overhauls (TBO) and/or a calendar life limit
724 # (e.g. 12-year rubber hoses). overhauled_at_hours records the component
725 # hours at the last overhaul — it resets the TBO reference point without
726 # touching history; overhauled_on is the matching date for the records.
727 tbo_hours = db.Column(db.Numeric(8, 1), nullable=True)
728 life_limit_date = db.Column(db.Date, nullable=True)
729 overhauled_at_hours = db.Column(db.Numeric(8, 1), nullable=True)
730 overhauled_on = db.Column(db.Date, nullable=True)
732 # Type-specific attributes (blade count, TBO, firmware version, …)
733 extras = db.Column(db.JSON, nullable=True)
735 created_at = db.Column(
736 db.DateTime(timezone=True),
737 nullable=False,
738 default=lambda: datetime.now(UTC),
739 )
741 aircraft = db.relationship("Aircraft", back_populates="components")
742 documents = db.relationship(
743 "Document",
744 back_populates="component",
745 cascade="all, delete-orphan",
746 )
747 easa_source_nodes = db.relationship(
748 "EASASourceNode",
749 back_populates="component",
750 cascade="all, delete-orphan",
751 )
752 airworthiness_documents = db.relationship(
753 "AirworthinessDocument",
754 back_populates="component",
755 cascade="all, delete-orphan",
756 foreign_keys="AirworthinessDocument.component_id",
757 )
758 # Phase 40: no cascade — deleting a component unscopes (SET NULL) rather
759 # than deletes its maintenance triggers, per MaintenanceTrigger.component_id.
760 maintenance_triggers = db.relationship(
761 "MaintenanceTrigger", back_populates="component"
762 )
765# ── Phase 3: Flight Logging ───────────────────────────────────────────────────
768class CrewRole:
769 PIC = "PIC"
770 IP = "IP"
771 SP = "SP"
772 COPILOT = "COPILOT"
773 STUDENT = "STUDENT"
774 ALL: ClassVar[list[str]] = [PIC, IP, SP, COPILOT, STUDENT]
775 # N_()-marked: rendered through gettext() at the template layer, not here.
776 LABELS: ClassVar[dict[str, str]] = {
777 PIC: N_("PIC"),
778 IP: N_("Instructor"),
779 SP: N_("Safety Pilot"),
780 COPILOT: N_("Co-Pilot"),
781 STUDENT: N_("Student"),
782 }
785# ── GPS Track (standalone, linkable from Flight) ──────────────────────────────
788class GpsTrack(db.Model):
789 __tablename__ = "gps_tracks"
791 id = db.Column(db.Integer, primary_key=True)
792 source_filename = db.Column(db.String(256), nullable=True)
793 device_id = db.Column(db.String(64), nullable=True, index=True)
794 block_off_utc = db.Column(db.DateTime(timezone=True), nullable=True)
795 block_on_utc = db.Column(db.DateTime(timezone=True), nullable=True)
796 departure_icao = db.Column(db.String(4), nullable=True)
797 arrival_icao = db.Column(db.String(4), nullable=True)
798 geojson = db.Column(db.JSON, nullable=True)
799 created_at = db.Column(
800 db.DateTime(timezone=True),
801 nullable=False,
802 default=lambda: datetime.now(UTC),
803 )
804 # Render cache for the default (landscape, low-res) single-flight PNG/GIF —
805 # geojson never changes once saved, so no invalidation is ever needed.
806 cached_png = db.Column(db.LargeBinary, nullable=True)
807 cached_gif = db.Column(db.LargeBinary, nullable=True)
810class LogbookEntryType:
811 FLIGHT = "flight"
812 FSTD = "fstd" # synthetic training device / simulator session
813 ALL: ClassVar[set[str]] = {FLIGHT, FSTD}
816class FstdType:
817 FFS = "FFS"
818 FTD = "FTD"
819 FNPT = "FNPT"
820 BITD = "BITD"
821 AATD = "AATD"
822 ALL: ClassVar[list[str]] = [FFS, FTD, FNPT, BITD, AATD]
825class Flight(db.Model):
826 """Unified logbook record — one row per real-world flight (or FSTD
827 session), covering both the airframe-log ("aircraft side") and the
828 EASA FCL.050 pilot-log ("pilot side") views of it, replacing the old
829 FlightEntry + FlightCrew + PilotLogbookEntry three-table split.
831 Covers all three real-world cases: (1) a managed aircraft flown by a
832 pilot with no OpenHangar account (aircraft_id set, pic_user_id NULL,
833 pic_name free text); (2) a pilot with an account flying an aircraft
834 not managed here (aircraft_id NULL, other_aircraft_* free text,
835 pic_user_id set); (3) both managed (aircraft_id and pic_user_id both
836 set). At most one additional occupant is tracked via the
837 second_crew_* identity fields (mirrors the pre-refactor FlightCrew's
838 2-slot cap — every crew-creating code path already stopped at 2).
840 EASA figures (night/instrument/cross-country time, landings,
841 single_pilot_se/me, multi_pilot, the function_* breakdown, entry_type/
842 fstd_*) live once on the row, not duplicated per pilot slot — they
843 describe the flight/session itself (or, for function_*, are already
844 unambiguously tied to a slot by column name), not a specific occupant.
846 Two independent engine/flight duration tracks live side by side:
847 departure_time/arrival_time + engine_time_counter_*/engine_time (engine
848 start/end, pilot-log-facing) vs. takeoff_time/landing_time +
849 flight_time_counter_*/flight_time (flight start/end, airframe-log-
850 facing). Neither pair is derived from the other.
851 Letting PIC and a second crew member log different figures for the
852 same real flight was a bug in the old two-table design, not a
853 feature — this schema makes that impossible by construction.
854 """
856 __tablename__ = "flights"
858 id = db.Column(db.Integer, primary_key=True)
859 aircraft_id = db.Column(
860 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
861 )
862 # Free-text aircraft descriptor for flights on an aircraft not managed
863 # in this instance (aircraft_id NULL) — mirrors PilotLogbookEntry's old
864 # aircraft_type/_icao/registration. When aircraft_id is set, the type/
865 # registration are derived live from the Aircraft join instead.
866 other_aircraft_type = db.Column(db.String(64), nullable=True)
867 other_aircraft_type_icao = db.Column(db.String(16), nullable=True)
868 other_aircraft_registration = db.Column(db.String(16), nullable=True)
870 date = db.Column(db.Date, nullable=False)
871 # Widened from the old FlightEntry's strict 4-char ICAO-only String —
872 # a standalone entry (rental/FSTD) may log a free-text place instead.
873 departure_icao = db.Column(db.String(64), nullable=True)
874 arrival_icao = db.Column(db.String(64), nullable=True)
875 # Engine start/end (block-off/block-on — engine assumed on at block-off).
876 # Pairs with engine_time_counter_*/engine_time below and feeds the pilot
877 # logbook. Distinct from takeoff_time/landing_time (the flight/airborne
878 # segment, pairs with flight_time_counter_*/flight_time, feeds the
879 # airframe logbook) — the two pairs are never defaulted from each other.
880 departure_time = db.Column(db.Time, nullable=True)
881 arrival_time = db.Column(db.Time, nullable=True)
882 # Flight start/end (wheels-up/wheels-down) — see comment above.
883 takeoff_time = db.Column(db.Time, nullable=True)
884 landing_time = db.Column(db.Time, nullable=True)
885 flight_time = db.Column(db.Numeric(4, 1), nullable=True)
886 nature_of_flight = db.Column(db.String(100), nullable=True)
887 passenger_count = db.Column(db.Integer, nullable=True)
888 landing_count = db.Column(db.Integer, nullable=True)
889 flight_time_counter_start = db.Column(db.Numeric(8, 1), nullable=True)
890 flight_time_counter_end = db.Column(db.Numeric(8, 1), nullable=True)
891 notes = db.Column(db.Text, nullable=True)
892 engine_time_counter_start = db.Column(db.Numeric(8, 1), nullable=True)
893 engine_time_counter_end = db.Column(db.Numeric(8, 1), nullable=True)
894 # Raw engine-hours duration for this flight (engine counter end minus
895 # start, no offset) — distinct from flight_time, which approximates the
896 # airborne segment. Engine/propeller TBO tracking sums this, not
897 # flight_time (see services/component_limits.py).
898 engine_time = db.Column(db.Numeric(4, 1), nullable=True)
899 flight_counter_photo = db.Column(db.String(255), nullable=True)
900 engine_counter_photo = db.Column(db.String(255), nullable=True)
901 # Pre-flight counterparts of the two columns above — taken before
902 # departure (e.g. at block-off) rather than after shutdown. Independent
903 # of the post-flight photos: a flight can have neither, either, or both.
904 flight_counter_photo_preflight = db.Column(db.String(255), nullable=True)
905 engine_counter_photo_preflight = db.Column(db.String(255), nullable=True)
906 # Refueling before and after are independent events — a flight can have
907 # neither, either, or both (e.g. topped off before departure, then
908 # topped off again after landing).
909 fuel_added_before_qty = db.Column(db.Numeric(8, 2), nullable=True)
910 fuel_added_before_unit = db.Column(db.String(8), nullable=True)
911 fuel_added_after_qty = db.Column(db.Numeric(8, 2), nullable=True)
912 fuel_added_after_unit = db.Column(db.String(8), nullable=True)
913 fuel_remaining_qty = db.Column(db.Numeric(8, 2), nullable=True)
914 fuel_photo = db.Column(db.String(255), nullable=True)
915 # Same independence as the fuel_added_before/after pair above — oil can
916 # be topped off before departure, after landing, or both.
917 oil_added_before_l = db.Column(db.Numeric(4, 2), nullable=True)
918 oil_added_after_l = db.Column(db.Numeric(4, 2), nullable=True)
919 created_at = db.Column(
920 db.DateTime(timezone=True),
921 nullable=False,
922 default=lambda: datetime.now(UTC),
923 )
925 # ── EASA FCL.050 pilot-log figures (shared, once per row) ─────────────────
926 night_time = db.Column(db.Numeric(4, 1), nullable=True)
927 instrument_time = db.Column(db.Numeric(4, 1), nullable=True)
928 cross_country = db.Column(db.Numeric(4, 1), nullable=True)
929 landings_day = db.Column(db.Integer, nullable=True)
930 landings_night = db.Column(db.Integer, nullable=True)
931 single_pilot_se = db.Column(db.Numeric(4, 1), nullable=True)
932 single_pilot_me = db.Column(db.Numeric(4, 1), nullable=True)
933 multi_pilot = db.Column(db.Numeric(4, 1), nullable=True)
934 # Each already unambiguously tied to a slot by name: function_pic is
935 # always the pic_* slot's hours; whichever of the other three is
936 # non-null belongs to the second_crew_* slot, per second_crew_role.
937 function_pic = db.Column(db.Numeric(4, 1), nullable=True)
938 function_copilot = db.Column(db.Numeric(4, 1), nullable=True)
939 function_dual = db.Column(db.Numeric(4, 1), nullable=True)
940 function_instructor = db.Column(db.Numeric(4, 1), nullable=True)
942 # EASA AMC1 FCL.050 column 10 — FSTD/simulator sessions (LogbookEntryType constant)
943 entry_type = db.Column(
944 db.String(16),
945 nullable=False,
946 default=LogbookEntryType.FLIGHT,
947 server_default=LogbookEntryType.FLIGHT,
948 )
949 fstd_type = db.Column(db.String(16), nullable=True) # FstdType constant
950 fstd_duration = db.Column(db.Numeric(4, 1), nullable=True)
952 # ── Crew identity slots ────────────────────────────────────────────────────
953 pic_user_id = db.Column(
954 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
955 )
956 pic_name = db.Column(db.String(128), nullable=True)
957 second_crew_user_id = db.Column(
958 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
959 )
960 second_crew_name = db.Column(db.String(128), nullable=True)
961 second_crew_role = db.Column(db.String(16), nullable=True) # CrewRole constant
962 # Personal remark per slot — only ever edited by the pilot in that slot,
963 # unlike the shared `notes` (see flights/shared_flight.py).
964 pic_remarks = db.Column(db.Text, nullable=True)
965 second_crew_remarks = db.Column(db.Text, nullable=True)
966 # Who logged the flight. While they're still linked to a slot, only they
967 # (plus tenant owners/admins, for managed aircraft) edit the shared
968 # fields; the other linked pilot suggests corrections instead. NULL for
969 # rows created before this was tracked → every linked pilot may edit.
970 created_by_user_id = db.Column(
971 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
972 )
974 # Phase 30: GPS import
975 source = db.Column(db.String(32), nullable=True)
976 # "gps_import" | "import" | "logbook_import" | None — see docs/backlog.md
977 # "Step 2" for how this is used to flag rows needing the other side's data.
978 gps_import_batch_id = db.Column(
979 db.Integer,
980 db.ForeignKey("aircraft_gps_import_batches.id", ondelete="SET NULL"),
981 nullable=True,
982 )
983 # Bulk airframe logbook import (CSV/Excel)
984 airframe_import_batch_id = db.Column(
985 db.Integer,
986 db.ForeignKey("airframe_import_batches.id", ondelete="SET NULL"),
987 nullable=True,
988 )
989 airframe_import_batch = db.relationship(
990 "AirframeImportBatch", foreign_keys=[airframe_import_batch_id]
991 )
992 # Pilot logbook import (CSV/Excel) — independent of airframe_import_batch_id
993 # above; a row can carry both once it's been touched from both sides.
994 import_batch_id = db.Column(
995 db.Integer,
996 db.ForeignKey("logbook_import_batches.id", ondelete="SET NULL"),
997 nullable=True,
998 )
999 block_off_utc = db.Column(db.DateTime(timezone=True), nullable=True)
1000 block_on_utc = db.Column(db.DateTime(timezone=True), nullable=True)
1001 gps_track_id = db.Column(
1002 db.Integer,
1003 db.ForeignKey("gps_tracks.id", ondelete="SET NULL"),
1004 nullable=True,
1005 )
1006 # Phase 37d: auto-linked when this flight falls inside a confirmed
1007 # reservation's dispatch window for the same pilot.
1008 reservation_id = db.Column(
1009 db.Integer,
1010 db.ForeignKey("reservations.id", ondelete="SET NULL"),
1011 nullable=True,
1012 )
1014 aircraft = db.relationship("Aircraft", back_populates="flights")
1015 reservation = db.relationship("Reservation", back_populates="flights")
1016 gps_track = db.relationship("GpsTrack", foreign_keys=[gps_track_id])
1017 gps_import_batch = db.relationship(
1018 "AircraftGpsImportBatch", foreign_keys=[gps_import_batch_id]
1019 )
1020 import_batch = db.relationship("LogbookImportBatch", foreign_keys=[import_batch_id])
1021 pic_user = db.relationship("User", foreign_keys=[pic_user_id])
1022 second_crew_user = db.relationship("User", foreign_keys=[second_crew_user_id])
1023 expenses = db.relationship("Expense", back_populates="flight_entry")
1024 documents = db.relationship(
1025 "Document",
1026 back_populates="flight_entry",
1027 cascade="all, delete-orphan",
1028 )
1030 __table_args__ = (
1031 # Matches the standard airframe-log ordering (date DESC, id DESC per aircraft).
1032 db.Index(
1033 "ix_flights_aircraft_id_date_id",
1034 aircraft_id,
1035 date.desc(),
1036 id.desc(),
1037 ),
1038 # Match the old per-pilot logbook ordering (date DESC, id DESC per
1039 # pilot) for each identity slot — "my logbook" is
1040 # WHERE pic_user_id = :uid OR second_crew_user_id = :uid.
1041 db.Index(
1042 "ix_flights_pic_user_id_date_id",
1043 pic_user_id,
1044 date.desc(),
1045 id.desc(),
1046 ),
1047 db.Index(
1048 "ix_flights_second_crew_user_id_date_id",
1049 second_crew_user_id,
1050 date.desc(),
1051 id.desc(),
1052 ),
1053 )
1055 @property
1056 def total_flight_time(self):
1057 parts = [self.single_pilot_se, self.single_pilot_me, self.multi_pilot]
1058 vals = [float(p) for p in parts if p is not None]
1059 return round(sum(vals), 1) if vals else None
1061 # Aircraft descriptor, resolved live from the managed Aircraft when
1062 # aircraft_id is set (Aircraft.make/model rarely changes) rather than
1063 # denormalized onto this row — falls back to the free-text
1064 # other_aircraft_* columns for a standalone (aircraft_id NULL) row.
1065 @property
1066 def display_aircraft_type(self) -> str | None:
1067 if self.aircraft_id and self.aircraft:
1068 return f"{self.aircraft.make} {self.aircraft.model}".strip()
1069 return self.other_aircraft_type
1071 @property
1072 def display_aircraft_type_icao(self) -> str | None:
1073 if self.aircraft_id and self.aircraft:
1074 return getattr(self.aircraft, "aircraft_type_icao", None)
1075 return self.other_aircraft_type_icao
1077 @property
1078 def display_registration(self) -> str | None:
1079 if self.aircraft_id and self.aircraft:
1080 return self.aircraft.registration
1081 return self.other_aircraft_registration
1083 def slot_for(self, user_id: int | None) -> str | None:
1084 """The crew slot ("pic" / "second") *user_id* is linked to, if any."""
1085 if user_id is None:
1086 return None
1087 if self.pic_user_id == user_id:
1088 return "pic"
1089 if self.second_crew_user_id == user_id:
1090 return "second"
1091 return None
1093 def visible_function_fields(self, user_id: int | None) -> set[str]:
1094 """function_* columns to show *user_id* in their logbook: on a flight
1095 shared with another linked pilot, only their own slot's hours."""
1096 if self.other_linked_user_ids(user_id):
1097 if self.slot_for(user_id) == "pic":
1098 return {"function_pic"}
1099 if self.slot_for(user_id) == "second":
1100 return {"function_copilot", "function_dual", "function_instructor"}
1101 return {
1102 "function_pic",
1103 "function_copilot",
1104 "function_dual",
1105 "function_instructor",
1106 }
1108 def personal_remark_for(self, user_id: int | None) -> str | None:
1109 slot = self.slot_for(user_id)
1110 if slot == "pic":
1111 remark: str | None = self.pic_remarks
1112 return remark
1113 if slot == "second":
1114 remark = self.second_crew_remarks
1115 return remark
1116 return None
1118 def other_linked_user_ids(self, user_id: int | None) -> set[int]:
1119 """Accounts linked to either crew slot, other than *user_id* — the
1120 pilots whose logbooks a delete by *user_id* must not touch (see
1121 flights/crew_removal.py)."""
1122 return {
1123 uid
1124 for uid in (self.pic_user_id, self.second_crew_user_id)
1125 if uid is not None and uid != user_id
1126 }
1129# ── Crew invites (ask another pilot to confirm their slot on a flight) ────────
1132class CrewSlot:
1133 """Which of a Flight row's two identity slots an invite targets."""
1135 PIC = "pic" # pic_user_id / pic_name
1136 SECOND = "second" # second_crew_user_id / second_crew_name / second_crew_role
1137 ALL: ClassVar[list[str]] = [PIC, SECOND]
1140class CrewInviteStatus:
1141 PENDING = "pending"
1142 ACCEPTED = "accepted"
1143 DECLINED = "declined"
1144 # The inviter changed/removed the name, or the slot got filled another way.
1145 CANCELLED = "cancelled"
1146 ALL: ClassVar[list[str]] = [PENDING, ACCEPTED, DECLINED, CANCELLED]
1149class CrewInviteKind:
1150 # The logger named a pilot; that pilot confirms or declines.
1151 INVITE = "invite"
1152 # A pilot found the flight already logged (duplicate warning) and asks to
1153 # be added; the pilot who owns the flight's shared fields approves.
1154 CLAIM = "claim"
1155 ALL: ClassVar[list[str]] = [INVITE, CLAIM]
1158class FlightCrewInvite(db.Model):
1159 """A request to link ``invited_user_id`` into one of a flight's crew slots.
1161 ``kind`` says who answers it: for an invite (a pilot named another pilot
1162 of the same tenant) the invited pilot confirms or declines; for a claim (a
1163 pilot asks to be added to a flight someone else logged) the pilot who owns
1164 the flight's shared fields approves or declines. Either way, on acceptance
1165 the user id is written into the slot and the flight appears in that
1166 pilot's logbook.
1168 The slot's user id is deliberately *not* set while the request is
1169 pending, so an unconfirmed flight never counts towards that pilot's totals
1170 or currency.
1171 """
1173 __tablename__ = "flight_crew_invites"
1175 id = db.Column(db.Integer, primary_key=True)
1176 flight_id = db.Column(
1177 db.Integer,
1178 db.ForeignKey("flights.id", ondelete="CASCADE"),
1179 nullable=False,
1180 index=True,
1181 )
1182 slot = db.Column(db.String(16), nullable=False) # CrewSlot constant
1183 invited_user_id = db.Column(
1184 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1185 )
1186 invited_by_user_id = db.Column(
1187 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
1188 )
1189 status = db.Column(
1190 db.String(16),
1191 nullable=False,
1192 default=CrewInviteStatus.PENDING,
1193 server_default=CrewInviteStatus.PENDING,
1194 )
1195 kind = db.Column(
1196 db.String(16),
1197 nullable=False,
1198 default=CrewInviteKind.INVITE,
1199 server_default=CrewInviteKind.INVITE,
1200 )
1201 # Claims only: the second-crew role the claiming pilot says they flew as,
1202 # applied on approval when the flight doesn't name one yet.
1203 requested_role = db.Column(db.String(16), nullable=True) # CrewRole constant
1204 created_at = db.Column(
1205 db.DateTime(timezone=True),
1206 nullable=False,
1207 default=lambda: datetime.now(UTC),
1208 )
1209 responded_at = db.Column(db.DateTime(timezone=True), nullable=True)
1211 flight = db.relationship(
1212 "Flight",
1213 backref=db.backref(
1214 "crew_invites", cascade="all, delete-orphan", passive_deletes=True
1215 ),
1216 )
1217 invited_user = db.relationship("User", foreign_keys=[invited_user_id])
1218 invited_by = db.relationship("User", foreign_keys=[invited_by_user_id])
1220 __table_args__ = (
1221 db.Index(
1222 "ix_flight_crew_invites_invited_user_id_status",
1223 invited_user_id,
1224 status,
1225 ),
1226 )
1229class CorrectionStatus:
1230 PENDING = "pending"
1231 ACCEPTED = "accepted"
1232 REJECTED = "rejected"
1233 ALL: ClassVar[list[str]] = [PENDING, ACCEPTED, REJECTED]
1236class FlightCorrectionSuggestion(db.Model):
1237 """A linked pilot who may not edit a shared flight's common fields
1238 proposes new values; the pilot who logged it accepts (applied for both)
1239 or rejects. ``changes`` maps field name → [old, new] canonical strings
1240 (flights/shared_flight.py)."""
1242 __tablename__ = "flight_correction_suggestions"
1244 id = db.Column(db.Integer, primary_key=True)
1245 flight_id = db.Column(
1246 db.Integer,
1247 db.ForeignKey("flights.id", ondelete="CASCADE"),
1248 nullable=False,
1249 index=True,
1250 )
1251 suggested_by_user_id = db.Column(
1252 db.Integer,
1253 db.ForeignKey("users.id", ondelete="CASCADE"),
1254 nullable=False,
1255 index=True,
1256 )
1257 changes = db.Column(db.JSON, nullable=False)
1258 status = db.Column(
1259 db.String(16),
1260 nullable=False,
1261 default=CorrectionStatus.PENDING,
1262 server_default=CorrectionStatus.PENDING,
1263 )
1264 created_at = db.Column(
1265 db.DateTime(timezone=True),
1266 nullable=False,
1267 default=lambda: datetime.now(UTC),
1268 )
1269 responded_at = db.Column(db.DateTime(timezone=True), nullable=True)
1271 flight = db.relationship(
1272 "Flight",
1273 backref=db.backref(
1274 "correction_suggestions", cascade="all, delete-orphan", passive_deletes=True
1275 ),
1276 )
1277 suggested_by = db.relationship("User", foreign_keys=[suggested_by_user_id])
1280# ── Pilot Profile ──────────────────────────────────────────────────────────────
1283class PilotProfile(db.Model):
1284 __tablename__ = "pilot_profiles"
1286 id = db.Column(db.Integer, primary_key=True)
1287 user_id = db.Column(
1288 db.Integer,
1289 db.ForeignKey("users.id", ondelete="CASCADE"),
1290 nullable=False,
1291 unique=True,
1292 )
1293 license_number = db.Column(db.String(64), nullable=True)
1294 medical_expiry = db.Column(db.Date, nullable=True)
1295 sep_expiry = db.Column(db.Date, nullable=True)
1296 first_solo_date = db.Column(db.Date, nullable=True)
1297 ppl_issue_date = db.Column(db.Date, nullable=True)
1299 user = db.relationship("User")
1302# ── Personal minimums ──────────────────────────────────────────────────────────
1305class PersonalMinimumsStatus:
1306 DRAFT = "draft"
1307 ACTIVE = "active"
1308 SUPERSEDED = "superseded"
1309 ALL: ClassVar[set[str]] = {DRAFT, ACTIVE, SUPERSEDED}
1312class PersonalMinimumsTag:
1313 """Small semantic vocabulary — binding an item to one of these lets the
1314 app compute a recency nudge for it (the two MAX_DAYS_* tags only; the
1315 other two are display-only in v1, see docs/backlog.md). Stored as a
1316 plain string so new tags never need a migration."""
1318 MAX_DAYS_SINCE_LAST_FLIGHT = "max_days_since_last_flight"
1319 MAX_DAYS_SINCE_INSTRUCTOR_FLIGHT = "max_days_since_instructor_flight"
1320 MANOEUVRES_PRACTICE_INTERVAL_MONTHS = "manoeuvres_practice_interval_months"
1321 MIN_FUEL_RESERVE_MINUTES = "min_fuel_reserve_minutes"
1323 ALL: ClassVar[set[str]] = {
1324 MAX_DAYS_SINCE_LAST_FLIGHT,
1325 MAX_DAYS_SINCE_INSTRUCTOR_FLIGHT,
1326 MANOEUVRES_PRACTICE_INTERVAL_MONTHS,
1327 MIN_FUEL_RESERVE_MINUTES,
1328 }
1329 # Tags with an automatic recency check in v1 (see backlog decisions log).
1330 HAS_RECENCY_CHECK: ClassVar[set[str]] = {
1331 MAX_DAYS_SINCE_LAST_FLIGHT,
1332 MAX_DAYS_SINCE_INSTRUCTOR_FLIGHT,
1333 }
1336class PersonalMinimumsRevision(db.Model):
1337 """A pilot's personal minimums document, versioned: draft (editable) ->
1338 active (published, immutable) -> superseded (immutable, kept for
1339 history). At most one draft and one active per pilot at a time
1340 (enforced in route logic, not a DB constraint — see pilots/routes.py)."""
1342 __tablename__ = "personal_minimums_revisions"
1343 __table_args__ = (
1344 db.UniqueConstraint(
1345 "user_id", "revision_number", name="uq_personal_minimums_revision"
1346 ),
1347 )
1349 id = db.Column(db.Integer, primary_key=True)
1350 user_id = db.Column(
1351 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1352 )
1353 revision_number = db.Column(db.Integer, nullable=False)
1354 status = db.Column(
1355 db.String(16), nullable=False, default=PersonalMinimumsStatus.DRAFT
1356 )
1357 published_on = db.Column(db.Date, nullable=True)
1358 experience_hours = db.Column(db.Numeric(6, 1), nullable=True)
1359 experience_note = db.Column(db.String(128), nullable=True)
1360 created_at = db.Column(
1361 db.DateTime(timezone=True),
1362 nullable=False,
1363 default=lambda: datetime.now(UTC),
1364 )
1365 updated_at = db.Column(
1366 db.DateTime(timezone=True),
1367 nullable=False,
1368 default=lambda: datetime.now(UTC),
1369 onupdate=lambda: datetime.now(UTC),
1370 )
1372 user = db.relationship("User")
1373 sections = db.relationship(
1374 "PersonalMinimumsSection",
1375 back_populates="revision",
1376 cascade="all, delete-orphan",
1377 order_by="PersonalMinimumsSection.sort_order",
1378 )
1381class PersonalMinimumsSection(db.Model):
1382 __tablename__ = "personal_minimums_sections"
1384 id = db.Column(db.Integer, primary_key=True)
1385 revision_id = db.Column(
1386 db.Integer,
1387 db.ForeignKey("personal_minimums_revisions.id", ondelete="CASCADE"),
1388 nullable=False,
1389 )
1390 title = db.Column(db.String(128), nullable=False)
1391 sort_order = db.Column(db.Integer, nullable=False, default=0)
1393 revision = db.relationship("PersonalMinimumsRevision", back_populates="sections")
1394 items = db.relationship(
1395 "PersonalMinimumsItem",
1396 back_populates="section",
1397 cascade="all, delete-orphan",
1398 order_by="PersonalMinimumsItem.sort_order",
1399 )
1402class PersonalMinimumsItem(db.Model):
1403 __tablename__ = "personal_minimums_items"
1405 id = db.Column(db.Integer, primary_key=True)
1406 section_id = db.Column(
1407 db.Integer,
1408 db.ForeignKey("personal_minimums_sections.id", ondelete="CASCADE"),
1409 nullable=False,
1410 )
1411 label = db.Column(db.String(128), nullable=False)
1412 value = db.Column(db.Text, nullable=True)
1413 semantic_tag = db.Column(
1414 db.String(64), nullable=True
1415 ) # PersonalMinimumsTag constant
1416 numeric_value = db.Column(db.Numeric(8, 2), nullable=True)
1417 sort_order = db.Column(db.Integer, nullable=False, default=0)
1419 section = db.relationship("PersonalMinimumsSection", back_populates="items")
1422# ── Phase 28: Pilot Logbook Import ───────────────────────────────────────────
1425class LogbookImportMapping(db.Model):
1426 __tablename__ = "logbook_import_mappings"
1428 id = db.Column(db.Integer, primary_key=True)
1429 pilot_user_id = db.Column(
1430 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1431 )
1432 source_fingerprint = db.Column(db.String(64), nullable=False, index=True)
1433 # JSON: {norm_col_key: target_field_or_"ignore"}
1434 column_mapping = db.Column(db.Text, nullable=False)
1435 # JSON list of norm_col_keys — stored for fuzzy matching future uploads
1436 source_columns = db.Column(db.Text, nullable=False)
1437 created_at = db.Column(db.DateTime(timezone=True), nullable=False)
1439 pilot = db.relationship("User")
1442class LogbookImportBatch(db.Model):
1443 __tablename__ = "logbook_import_batches"
1445 id = db.Column(db.Integer, primary_key=True)
1446 pilot_user_id = db.Column(
1447 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1448 )
1449 mapping_id = db.Column(
1450 db.Integer,
1451 db.ForeignKey("logbook_import_mappings.id", ondelete="SET NULL"),
1452 nullable=True,
1453 )
1454 source_filename = db.Column(db.String(256), nullable=False)
1455 imported_at = db.Column(db.DateTime(timezone=True), nullable=False)
1456 row_count = db.Column(db.Integer, nullable=False, default=0)
1457 subtotal_count = db.Column(db.Integer, nullable=False, default=0)
1458 skipped_count = db.Column(db.Integer, nullable=False, default=0)
1459 has_opening_balance = db.Column(db.Boolean, nullable=False, default=False)
1461 pilot = db.relationship("User")
1462 mapping = db.relationship("LogbookImportMapping")
1463 entries = db.relationship(
1464 "Flight",
1465 foreign_keys="Flight.import_batch_id",
1466 lazy="dynamic",
1467 overlaps="import_batch",
1468 )
1471class AirframeImportMapping(db.Model):
1472 """Fingerprint-keyed column mapping memory for airframe logbook imports
1473 (the aircraft-record twin of LogbookImportMapping)."""
1475 __tablename__ = "airframe_import_mappings"
1477 id = db.Column(db.Integer, primary_key=True)
1478 tenant_id = db.Column(
1479 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
1480 )
1481 source_fingerprint = db.Column(db.String(64), nullable=False, index=True)
1482 # JSON: {norm_col_key: target_field_or_"ignore"}
1483 column_mapping = db.Column(db.Text, nullable=False)
1484 # JSON list of norm_col_keys
1485 source_columns = db.Column(db.Text, nullable=False)
1486 created_at = db.Column(db.DateTime(timezone=True), nullable=False)
1489class AirframeImportBatch(db.Model):
1490 """One executed airframe logbook import for one aircraft, undoable as a unit."""
1492 __tablename__ = "airframe_import_batches"
1494 id = db.Column(db.Integer, primary_key=True)
1495 aircraft_id = db.Column(
1496 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1497 )
1498 mapping_id = db.Column(
1499 db.Integer,
1500 db.ForeignKey("airframe_import_mappings.id", ondelete="SET NULL"),
1501 nullable=True,
1502 )
1503 source_filename = db.Column(db.String(256), nullable=False)
1504 imported_at = db.Column(db.DateTime(timezone=True), nullable=False)
1505 row_count = db.Column(db.Integer, nullable=False, default=0)
1506 subtotal_count = db.Column(db.Integer, nullable=False, default=0)
1507 skipped_count = db.Column(db.Integer, nullable=False, default=0)
1508 warning_count = db.Column(db.Integer, nullable=False, default=0)
1509 has_opening_counters = db.Column(db.Boolean, nullable=False, default=False)
1510 # One-time digitization of pre-existing paper records: rows may contain
1511 # imprecision (OCR misreads, decimal/hours.minutes ambiguity) that must
1512 # still be imported as-is, and must not later block edits to that
1513 # imported flight. Regular/incremental catch-up imports leave this False
1514 # so they stay subject to the normal strict validation on edit.
1515 is_historical = db.Column(db.Boolean, nullable=False, default=False)
1517 aircraft = db.relationship("Aircraft")
1520# ── Phase 30: Aircraft GPS Log Import ────────────────────────────────────────
1523class AircraftGpsImportBatch(db.Model):
1524 """Metadata for one GPS-import session (1+ files → 1+ Flight records)."""
1526 __tablename__ = "aircraft_gps_import_batches"
1528 id = db.Column(db.Integer, primary_key=True)
1529 aircraft_id = db.Column(
1530 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1531 )
1532 pilot_user_id = db.Column(
1533 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
1534 )
1535 # JSON list of original filenames, e.g. ["log_260518_EBNM.csv", "track.gpx"]
1536 source_filenames = db.Column(db.JSON, nullable=False, default=list)
1537 imported_at = db.Column(
1538 db.DateTime(timezone=True),
1539 nullable=False,
1540 default=lambda: datetime.now(UTC),
1541 )
1542 format_detected = db.Column(
1543 db.String(16), nullable=False
1544 ) # "gpx"|"kml"|"garmin_csv"|"mixed"
1545 segments_found = db.Column(db.Integer, nullable=False, default=0)
1546 segments_imported = db.Column(db.Integer, nullable=False, default=0)
1547 # IDs of pre-existing Flight rows that received a GPS track (not created).
1548 linked_flight_entry_ids = db.Column(db.JSON, nullable=False, default=list)
1549 # Pilot role selected during import: 'pic' | 'dual' | 'none'
1550 pilot_role = db.Column(db.String(8), nullable=True)
1551 # Set when the import is for an aircraft not in this instance (Phase 31).
1552 other_aircraft_make_model = db.Column(db.String(128), nullable=True)
1553 other_aircraft_registration = db.Column(db.String(16), nullable=True)
1555 aircraft = db.relationship(
1556 "Aircraft",
1557 backref=db.backref("gps_import_batches", cascade="all, delete-orphan"),
1558 )
1559 pilot = db.relationship("User", foreign_keys=[pilot_user_id])
1560 # Merges the old FlightEntry.gps_import_batch_id and
1561 # PilotLogbookEntry.gps_batch_id (two separate FKs to this same table)
1562 # into one — a unified Flight row only ever needs one link back here.
1563 flight_entries = db.relationship(
1564 "Flight",
1565 foreign_keys="Flight.gps_import_batch_id",
1566 lazy="dynamic",
1567 overlaps="gps_import_batch",
1568 )
1571# ── Phase 4: Maintenance Tracking ────────────────────────────────────────────
1574class TriggerType:
1575 CALENDAR = "calendar" # due on a specific date
1576 HOURS = "hours" # due at a specific hobbs reading
1577 LANDINGS = "landings" # due at a specific cumulative landing count
1578 ALL: ClassVar[set[str]] = {CALENDAR, HOURS, LANDINGS}
1581class HoursBasis:
1582 """Which running total an HOURS-type MaintenanceTrigger's
1583 due_engine_hours/interval_hours are measured against — engine/propeller
1584 TBO-style items are naturally engine-hours based, while airframe life
1585 limits and other flight-hours-quoted items need the aircraft's flight
1586 (airborne) hours instead."""
1588 ENGINE = "engine"
1589 FLIGHT = "flight"
1590 ALL: ClassVar[set[str]] = {ENGINE, FLIGHT}
1591 LABELS: ClassVar[dict[str, str]] = {ENGINE: "Engine hours", FLIGHT: "Flight hours"}
1594class AmpCategory:
1595 """The 9 additional-maintenance-requirement categories from EASA Form
1596 AMP block 4 / Appendix B (AMC2 ML.A.302), used verbatim as
1597 ``MaintenanceTrigger.category`` values so that block 4's Yes/No table
1598 and Appendix B can be computed from the trigger set at export time
1599 rather than stored separately (Phase 40). ``ALL`` preserves the
1600 official form's own row order for rendering."""
1602 EQUIPMENT_AND_MODIFICATIONS = (
1603 "Maintenance due to specific equipment and modifications"
1604 )
1605 REPAIRS = "Maintenance due to repairs"
1606 LIFE_LIMITED_COMPONENTS = "Maintenance due to life-limited components"
1607 MANDATORY_CONTINUING_AIRWORTHINESS = (
1608 "Maintenance due to mandatory continuing airworthiness information "
1609 "(ALIs, CMRs, TCDS)"
1610 )
1611 TBO_RECOMMENDATIONS = "Maintenance recommendations (TBO via SB/SL, non-mandatory)"
1612 REPETITIVE_ADS = "Maintenance due to repetitive ADs"
1613 OPERATIONAL_AIRSPACE_DIRECTIVES = (
1614 "Maintenance due to specific operational/airspace directives/requirements"
1615 )
1616 TYPE_OF_OPERATION = "Maintenance due to type of operation or operational approvals"
1617 OTHER = "Other"
1619 ALL: ClassVar[list[str]] = [
1620 EQUIPMENT_AND_MODIFICATIONS,
1621 REPAIRS,
1622 LIFE_LIMITED_COMPONENTS,
1623 MANDATORY_CONTINUING_AIRWORTHINESS,
1624 TBO_RECOMMENDATIONS,
1625 REPETITIVE_ADS,
1626 OPERATIONAL_AIRSPACE_DIRECTIVES,
1627 TYPE_OF_OPERATION,
1628 OTHER,
1629 ]
1632class MaintenanceTrigger(db.Model):
1633 __tablename__ = "maintenance_triggers"
1635 id = db.Column(db.Integer, primary_key=True)
1636 aircraft_id = db.Column(
1637 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1638 )
1639 # Phase 40: optional component scoping (engine, propeller, …). NULL means
1640 # airframe-general. SET NULL on component deletion — removing a component
1641 # shouldn't delete its maintenance history, just unscope the trigger.
1642 component_id = db.Column(
1643 db.Integer, db.ForeignKey("components.id", ondelete="SET NULL"), nullable=True
1644 )
1645 name = db.Column(db.String(128), nullable=False)
1646 trigger_type = db.Column(db.String(16), nullable=False) # TriggerType constant
1648 # Calendar trigger fields
1649 due_date = db.Column(db.Date, nullable=True)
1650 interval_days = db.Column(db.Integer, nullable=True) # advance due_date on service
1651 # Days before due_date to flag 'due_soon'. NULL falls back to a flat
1652 # 30-day default (status()) — lets an admin override per trigger without
1653 # requiring every existing row to be re-saved.
1654 warn_days = db.Column(db.Integer, nullable=True)
1656 # Hours trigger fields — despite the column name, due_engine_hours is
1657 # measured against engine or flight hours depending on hours_basis.
1658 due_engine_hours = db.Column(db.Numeric(8, 1), nullable=True)
1659 interval_hours = db.Column(
1660 db.Numeric(8, 1), nullable=True
1661 ) # advance due_engine_hours on service
1662 hours_basis = db.Column(
1663 db.String(16),
1664 nullable=False,
1665 default=HoursBasis.ENGINE,
1666 server_default=HoursBasis.ENGINE,
1667 )
1668 # Hours before due_engine_hours to flag 'due_soon'. NULL falls back to
1669 # max(interval_hours * 10%, 5.0), or a flat 10.0 with no interval set.
1670 warn_hours = db.Column(db.Numeric(6, 1), nullable=True)
1672 # Landings trigger fields
1673 due_landings = db.Column(db.Integer, nullable=True)
1674 interval_landings = db.Column(
1675 db.Integer, nullable=True
1676 ) # advance due_landings on service
1677 # Landings before due_landings to flag 'due_soon'. NULL falls back to
1678 # max(interval_landings * 10%, 5), or a flat 10 with no interval set.
1679 warn_landings = db.Column(db.Integer, nullable=True)
1681 notes = db.Column(db.Text, nullable=True)
1683 # Phase 40: AMP import/export provenance and classification fields — all
1684 # nullable free text/flags, orthogonal to the calendar/hours/landings due
1685 # fields above. See docs/maintenance_import.md once written.
1686 category = db.Column(db.String(128), nullable=True) # AmpCategory value, or NULL
1687 is_alternative_to_ica = db.Column(
1688 db.Boolean, nullable=False, default=False, server_default=db.false()
1689 )
1690 alternative_task_notes = db.Column(db.Text, nullable=True)
1691 reference = db.Column(db.String(255), nullable=True) # AD/SB/manual doc reference
1692 action = db.Column(db.String(32), nullable=True) # e.g. INSPECTION/REPLACE/TBO/SLL
1693 part_number = db.Column(db.String(64), nullable=True)
1694 serial_number = db.Column(db.String(64), nullable=True)
1695 # Set on import for rows with an unresolved/unparseable interval, so they
1696 # read as "not yet scheduled" rather than silently evaluating as 'ok'.
1697 needs_review = db.Column(
1698 db.Boolean, nullable=False, default=False, server_default=db.false()
1699 )
1700 import_batch_id = db.Column(
1701 db.Integer,
1702 db.ForeignKey("maintenance_import_batches.id", ondelete="SET NULL"),
1703 nullable=True,
1704 )
1706 created_at = db.Column(
1707 db.DateTime(timezone=True),
1708 nullable=False,
1709 default=lambda: datetime.now(UTC),
1710 )
1712 aircraft = db.relationship("Aircraft", back_populates="maintenance_triggers")
1713 component = db.relationship("Component", back_populates="maintenance_triggers")
1714 import_batch = db.relationship("MaintenanceImportBatch", back_populates="triggers")
1715 records = db.relationship(
1716 "MaintenanceRecord",
1717 back_populates="trigger",
1718 cascade="all, delete-orphan",
1719 order_by="MaintenanceRecord.performed_at.desc()",
1720 )
1722 __table_args__ = (
1723 db.Index("ix_maintenance_triggers_aircraft_id", aircraft_id),
1724 db.Index("ix_maintenance_triggers_component_id", component_id),
1725 db.Index("ix_maintenance_triggers_import_batch_id", import_batch_id),
1726 )
1728 def status(
1729 self,
1730 current_engine_hours: "float | None" = None,
1731 current_landings: "int | None" = None,
1732 current_flight_hours: "float | None" = None,
1733 ) -> str:
1734 """Return 'overdue', 'due_soon', or 'ok'.
1736 Evaluates every populated field group (calendar ``due_date``, hours
1737 ``due_engine_hours``, landings ``due_landings``) independently and
1738 returns the worst result. This is what lets a trigger represent a
1739 "due at whichever comes first" combined interval (e.g. an AMP task
1740 quoted as "100FH / 12MO") simply by having more than one field group
1741 populated at once, with no separate ``trigger_type`` value or
1742 duplicate rows needed. A trigger with only one field group populated
1743 (the common case) behaves exactly as before this method started
1744 evaluating groups independently. For the hours group, ``hours_basis``
1745 picks which of ``current_engine_hours``/``current_flight_hours`` is
1746 compared against ``due_engine_hours``."""
1747 from datetime import date as _date
1749 statuses: list[str] = []
1751 if self.due_date is not None:
1752 delta = (self.due_date - _date.today()).days
1753 if delta < 0:
1754 statuses.append("overdue")
1755 else:
1756 warn_days = self.warn_days if self.warn_days is not None else 30
1757 if delta <= warn_days:
1758 statuses.append("due_soon")
1760 if self.due_engine_hours is not None:
1761 current_hobbs = (
1762 current_flight_hours
1763 if self.hours_basis == HoursBasis.FLIGHT
1764 else current_engine_hours
1765 )
1766 if current_hobbs is not None:
1767 remaining = float(self.due_engine_hours) - float(current_hobbs)
1768 if remaining <= 0:
1769 statuses.append("overdue")
1770 else:
1771 if self.warn_hours is not None:
1772 warn_h = float(self.warn_hours)
1773 else:
1774 warn_h = (
1775 max(float(self.interval_hours) * 0.1, 5.0)
1776 if self.interval_hours
1777 else 10.0
1778 )
1779 if remaining <= warn_h:
1780 statuses.append("due_soon")
1782 if self.due_landings is not None and current_landings is not None:
1783 remaining_l = self.due_landings - current_landings
1784 if remaining_l <= 0:
1785 statuses.append("overdue")
1786 else:
1787 if self.warn_landings is not None:
1788 warn_l = self.warn_landings
1789 else:
1790 warn_l = (
1791 max(int(self.interval_landings * 0.1), 5)
1792 if self.interval_landings
1793 else 10
1794 )
1795 if remaining_l <= warn_l:
1796 statuses.append("due_soon")
1798 if "overdue" in statuses:
1799 return "overdue"
1800 if "due_soon" in statuses:
1801 return "due_soon"
1802 return "ok"
1804 @property
1805 def last_record(self):
1806 return self.records[0] if self.records else None
1808 @property
1809 def service_basis(self) -> "dict[str, Any] | None":
1810 """Raw data behind "how was this due value computed": the last
1811 service's date (+ reading, if logged) and this trigger's configured
1812 interval(s) — so a template can show the basis for a due figure
1813 without drilling into service history. Returns plain values (not a
1814 pre-formatted string) so the template can apply unit words via
1815 gettext — this model layer has no i18n dependency by design.
1816 None when there's no service history yet (nothing to summarize: an
1817 initial/imported due value isn't derived from a last-done reading +
1818 interval)."""
1819 lr = self.last_record
1820 if lr is None:
1821 return None
1822 return {
1823 "date": lr.performed_at,
1824 "hobbs": float(lr.hobbs_at_service)
1825 if lr.hobbs_at_service is not None
1826 else None,
1827 "landings": lr.landings_at_service,
1828 "interval_days": self.interval_days,
1829 "interval_hours": float(self.interval_hours)
1830 if self.interval_hours
1831 else None,
1832 "interval_landings": self.interval_landings,
1833 }
1836# ── Phase 6: Demo Mode ────────────────────────────────────────────────────────
1839class DemoSlot(db.Model):
1840 """One isolated visitor slot in demo mode. Each slot is its own tenant+user pair."""
1842 __tablename__ = "demo_slots"
1844 id = db.Column(db.Integer, primary_key=True) # slot number 1..N
1845 display_id = db.Column(db.Integer, nullable=True) # random 1000-9999, shown in UI
1846 tenant_id = db.Column(
1847 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
1848 )
1849 user_id = db.Column(
1850 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1851 )
1852 renter_user_id = db.Column(
1853 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1854 )
1855 maintenance_user_id = db.Column(
1856 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1857 )
1858 viewer_user_id = db.Column(
1859 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1860 )
1861 sole_pilot_user_id = db.Column(
1862 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1863 )
1864 sole_operator_user_id = db.Column(
1865 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1866 )
1867 # Phase 39h: shared-ownership sub-tenant (3 co-owner users on one shared
1868 # aircraft) — tracked by tenant id rather than a single user id, since
1869 # this sub-tenant has 3 users, not 1. SET NULL (not CASCADE): the demo
1870 # wipe cycle deletes this tenant explicitly (cascading its co-owners'
1871 # AircraftOwner/BillingAccount/etc. rows), it doesn't rely on the FK to
1872 # do it.
1873 shared_ownership_tenant_id = db.Column(
1874 db.Integer, db.ForeignKey("tenants.id", ondelete="SET NULL"), nullable=True
1875 )
1876 # One representative co-owner (of the 3 seeded on shared_ownership_tenant_id)
1877 # for the "Shared ownership" demo-role login button — mirrors the direct
1878 # *_user_id columns above rather than looking one up via the tenant at
1879 # login time.
1880 shared_ownership_user_id = db.Column(
1881 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1882 )
1883 last_activity_at = db.Column(db.DateTime(timezone=True), nullable=True)
1886class MaintenanceRecord(db.Model):
1887 __tablename__ = "maintenance_records"
1889 id = db.Column(db.Integer, primary_key=True)
1890 trigger_id = db.Column(
1891 db.Integer,
1892 db.ForeignKey("maintenance_triggers.id", ondelete="CASCADE"),
1893 nullable=False,
1894 )
1895 performed_at = db.Column(db.Date, nullable=False)
1896 hobbs_at_service = db.Column(db.Numeric(8, 1), nullable=True)
1897 landings_at_service = db.Column(db.Integer, nullable=True)
1898 notes = db.Column(db.Text, nullable=True)
1899 created_at = db.Column(
1900 db.DateTime(timezone=True),
1901 nullable=False,
1902 default=lambda: datetime.now(UTC),
1903 )
1905 trigger = db.relationship("MaintenanceTrigger", back_populates="records")
1908# ── Phase 40: AMP Declaration Profile ────────────────────────────────────────
1911class AmpBasis:
1912 """EASA Form AMP block 2 — what the programme is based on."""
1914 DAH_ICA = "dah_ica"
1915 MIP = "mip"
1916 ALL: ClassVar[set[str]] = {DAH_ICA, MIP}
1919class AmpDeclarationType:
1920 """EASA Form AMP block 7 — declaration by the owner, or approval by a
1921 contracted CAMO/CAO."""
1923 OWNER = "owner"
1924 CAMO_CAO = "camo_cao"
1925 ALL: ClassVar[set[str]] = {OWNER, CAMO_CAO}
1928class AmpCertifyingPartyKind:
1929 """EASA Form AMP block 8 — who signs the certification statement."""
1931 OWNER_LESSEE_OPERATOR = "owner_lessee_operator"
1932 CAMO_CAO = "camo_cao"
1933 ALL: ClassVar[set[str]] = {OWNER_LESSEE_OPERATOR, CAMO_CAO}
1936class AmpDeclaration(db.Model):
1937 """One-to-one with Aircraft: the EASA Form AMP (AMC2 ML.A.302) fields
1938 that aren't derivable from Aircraft/Component/MaintenanceTrigger. Block
1939 4/5's Yes/No tables and Appendix B/C are computed from MaintenanceTrigger
1940 at export time, not stored here — see docs/maintenance_import.md."""
1942 __tablename__ = "amp_declarations"
1944 aircraft_id = db.Column(
1945 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True
1946 )
1948 # Block 1 — aircraft owner. Distinct from block 8's certifying party
1949 # below: real AMPs can have a contracted CAMO/CAO (block 8) acting on
1950 # behalf of a different aircraft owner (block 1) — e.g. a declaration
1951 # by the owner themselves has these match, but a CAMO/CAO approval
1952 # doesn't. Optional: when unset, export falls back to the certifying
1953 # party (the common case where they're the same person/entity).
1954 owner_name = db.Column(db.String(128), nullable=True)
1955 owner_address = db.Column(db.Text, nullable=True)
1957 # Block 2 — basis for the maintenance programme
1958 basis = db.Column(
1959 db.String(16),
1960 nullable=False,
1961 default=AmpBasis.DAH_ICA,
1962 server_default=AmpBasis.DAH_ICA,
1963 )
1964 mip_details = db.Column(db.Text, nullable=True) # Appendix A content, if basis=MIP
1966 # Block 3a-3c — DAH ICA reference per equipment (manufacturer/type/model
1967 # is already on Aircraft/Component; only the reference is stored here).
1968 # Balloon fields 3d-3g are out of scope — no balloon support elsewhere.
1969 dah_ica_airframe_ref = db.Column(db.String(255), nullable=True)
1970 dah_ica_engine_ref = db.Column(db.String(255), nullable=True)
1971 dah_ica_propeller_ref = db.Column(db.String(255), nullable=True)
1973 # Block 6 — pilot-owner maintenance (ML.A.803)
1974 pilot_owner_maintenance = db.Column(
1975 db.Boolean, nullable=False, default=False, server_default=db.false()
1976 )
1977 pilot_owner_name = db.Column(db.String(128), nullable=True)
1978 pilot_owner_licence_number = db.Column(db.String(64), nullable=True)
1980 # Block 7 — declaration/approval of the maintenance programme
1981 declaration_type = db.Column(
1982 db.String(16),
1983 nullable=False,
1984 default=AmpDeclarationType.OWNER,
1985 server_default=AmpDeclarationType.OWNER,
1986 )
1987 camo_cao_approval_reference = db.Column(db.String(128), nullable=True)
1989 # Block 8 — certification statement
1990 certifying_party_kind = db.Column(
1991 db.String(24),
1992 nullable=False,
1993 default=AmpCertifyingPartyKind.OWNER_LESSEE_OPERATOR,
1994 server_default=AmpCertifyingPartyKind.OWNER_LESSEE_OPERATOR,
1995 )
1996 certifying_party_name = db.Column(db.String(128), nullable=True)
1997 certifying_party_address = db.Column(db.Text, nullable=True)
1998 certifying_party_phone = db.Column(db.String(32), nullable=True)
1999 certifying_party_email = db.Column(db.String(128), nullable=True)
2001 # Appendix D — optional free text. Block 10 (revision history) is a
2002 # separate one-to-many AmpRevision table, not stored here — see below.
2003 appendix_d_notes = db.Column(db.Text, nullable=True)
2005 updated_at = db.Column(
2006 db.DateTime(timezone=True),
2007 nullable=False,
2008 default=lambda: datetime.now(UTC),
2009 onupdate=lambda: datetime.now(UTC),
2010 )
2012 aircraft = db.relationship("Aircraft", back_populates="amp_declaration")
2015class AmpRevision(db.Model):
2016 """Block 10 — Revision control & periodic reviews of the Aircraft
2017 Maintenance Programme. One-to-many with Aircraft (not AmpDeclaration)
2018 so revision history survives even if the declaration profile is ever
2019 deleted and recreated. Real shop-produced AMPs consistently carry
2020 multiple rows here (e.g. "R00 initial release", "R01 <what changed>",
2021 ...) — mirrors MaintenanceRecord's shape/relationship style."""
2023 __tablename__ = "amp_revisions"
2025 id = db.Column(db.Integer, primary_key=True)
2026 aircraft_id = db.Column(
2027 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2028 )
2029 revision_number = db.Column(db.String(16), nullable=False)
2030 revision_content = db.Column(db.Text, nullable=True)
2031 revision_date = db.Column(db.Date, nullable=True)
2032 # SHA-256 of the export HTML rendered at the moment this revision was
2033 # added — the "what did the AMP look like when this revision was
2034 # declared" snapshot. A later export whose freshly-rendered HTML hashes
2035 # to something else means the AMP's data has drifted since this
2036 # revision, so a download at that point is a draft, not this revision.
2037 content_hash = db.Column(db.String(64), nullable=True)
2038 # Path (relative to UPLOAD_FOLDER, mirrors Document.filename) of the
2039 # canonical PDF for this revision — set the first time a download
2040 # happens while content_hash still matches the live data, so this
2041 # revision's exact bytes stay re-downloadable even after later edits.
2042 # Never set at all if this revision was superseded before anyone ever
2043 # downloaded it while current — there is no way to reconstruct that.
2044 pdf_path = db.Column(db.String(512), nullable=True)
2045 created_at = db.Column(
2046 db.DateTime(timezone=True),
2047 nullable=False,
2048 default=lambda: datetime.now(UTC),
2049 )
2051 aircraft = db.relationship("Aircraft", back_populates="amp_revisions")
2053 __table_args__ = (db.Index("ix_amp_revisions_aircraft_id", "aircraft_id"),)
2056class MaintenanceImportBatch(db.Model):
2057 """One AMP task-list spreadsheet import — mirrors LogbookImportBatch
2058 (Phase 28): links every MaintenanceTrigger it created so the whole
2059 import can be reviewed or rolled back as a unit."""
2061 __tablename__ = "maintenance_import_batches"
2063 id = db.Column(db.Integer, primary_key=True)
2064 aircraft_id = db.Column(
2065 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2066 )
2067 source_filename = db.Column(db.String(256), nullable=False)
2068 imported_at = db.Column(
2069 db.DateTime(timezone=True),
2070 nullable=False,
2071 default=lambda: datetime.now(UTC),
2072 )
2073 row_count = db.Column(db.Integer, nullable=False, default=0)
2074 needs_review_count = db.Column(db.Integer, nullable=False, default=0)
2076 aircraft = db.relationship("Aircraft")
2077 triggers = db.relationship(
2078 "MaintenanceTrigger",
2079 back_populates="import_batch",
2080 foreign_keys="MaintenanceTrigger.import_batch_id",
2081 lazy="dynamic",
2082 )
2084 __table_args__ = (
2085 db.Index("ix_maintenance_import_batches_aircraft_id", aircraft_id),
2086 )
2089# ── Phase 8: Cost Tracking ────────────────────────────────────────────────────
2092class ExpenseType:
2093 FUEL = "fuel"
2094 PARTS = "parts"
2095 INSURANCE = "insurance"
2096 OTHER = "other"
2098 ALL: ClassVar[set[str]] = {FUEL, PARTS, INSURANCE, OTHER}
2099 # N_()-marked: rendered through gettext() at the template/route layer, not here.
2100 LABELS: ClassVar[dict[str, str]] = {
2101 FUEL: N_("Fuel"),
2102 PARTS: N_("Parts & Maintenance"),
2103 INSURANCE: N_("Insurance"),
2104 OTHER: N_("Other"),
2105 }
2108class ExpenseCategory:
2109 """Phase 36: fixed costs (pro-rated by time) vs. operating costs (usage-based)."""
2111 FIXED = "fixed"
2112 OPERATING = "operating"
2114 ALL: ClassVar[set[str]] = {FIXED, OPERATING}
2115 LABELS: ClassVar[dict[str, str]] = {
2116 FIXED: "Fixed",
2117 OPERATING: "Operating",
2118 }
2119 # Default category per expense type; user may override on a per-entry basis.
2120 DEFAULTS: ClassVar[dict[str, str]] = {
2121 ExpenseType.FUEL: OPERATING,
2122 ExpenseType.PARTS: OPERATING,
2123 ExpenseType.INSURANCE: FIXED,
2124 ExpenseType.OTHER: OPERATING,
2125 }
2128class ExpenseRecurrence:
2129 """Recurring fixed costs: how often a template expense repeats."""
2131 MONTHLY = "monthly"
2132 QUARTERLY = "quarterly"
2133 YEARLY = "yearly"
2135 MONTHS: ClassVar[dict[str, int]] = {MONTHLY: 1, QUARTERLY: 3, YEARLY: 12}
2136 ALL: ClassVar[set[str]] = set(MONTHS)
2139class Expense(db.Model):
2140 __tablename__ = "expenses"
2142 id = db.Column(db.Integer, primary_key=True)
2143 aircraft_id = db.Column(
2144 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2145 )
2146 flight_entry_id = db.Column(
2147 db.Integer,
2148 db.ForeignKey("flights.id", ondelete="SET NULL"),
2149 nullable=True,
2150 )
2151 date = db.Column(db.Date, nullable=False)
2152 expense_type = db.Column(db.String(32), nullable=False, default=ExpenseType.OTHER)
2153 expense_category = db.Column(
2154 db.String(16), nullable=False, default=ExpenseCategory.OPERATING
2155 )
2156 description = db.Column(db.String(255), nullable=True)
2157 amount = db.Column(db.Numeric(10, 2), nullable=False)
2158 currency = db.Column(db.String(4), nullable=False, default="EUR")
2159 quantity = db.Column(db.Numeric(8, 2), nullable=True) # litres or gallons of fuel
2160 unit = db.Column(db.String(8), nullable=True) # L, gal
2161 # Phase 36: optional coverage span for fixed costs (e.g. an annual insurance
2162 # premium), used to pro-rate the amount across a report period shorter than
2163 # the coverage span. Left null, the expense counts in full on its `date`.
2164 coverage_start = db.Column(db.Date, nullable=True)
2165 coverage_end = db.Column(db.Date, nullable=True)
2166 # Recurring fixed costs: a template expense carries `recurrence`
2167 # (ExpenseRecurrence value, optionally bounded by recurrence_end); the
2168 # daily pass materialises ordinary Expense rows linked back through
2169 # recurring_template_id. recurrence_last_date is the materialiser's
2170 # cursor — deleting a generated row must not resurrect it next run.
2171 recurrence = db.Column(db.String(16), nullable=True)
2172 recurrence_end = db.Column(db.Date, nullable=True)
2173 recurrence_last_date = db.Column(db.Date, nullable=True)
2174 recurring_template_id = db.Column(
2175 db.Integer, db.ForeignKey("expenses.id", ondelete="SET NULL"), nullable=True
2176 )
2177 created_at = db.Column(
2178 db.DateTime(timezone=True),
2179 nullable=False,
2180 default=lambda: datetime.now(UTC),
2181 )
2182 # Phase 37e: who recorded this expense — used to attribute a renter's own
2183 # fuel-purchase expenses on a rental's linked flights for the fuel credit.
2184 created_by_id = db.Column(
2185 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2186 )
2188 aircraft = db.relationship("Aircraft", back_populates="expenses")
2189 flight_entry = db.relationship("Flight", back_populates="expenses")
2190 created_by = db.relationship("User", foreign_keys=[created_by_id])
2191 receipts = db.relationship(
2192 "Document",
2193 back_populates="expense",
2194 cascade="all, delete-orphan",
2195 )
2196 recurring_template = db.relationship(
2197 "Expense",
2198 foreign_keys=[recurring_template_id],
2199 remote_side="Expense.id",
2200 uselist=False,
2201 )
2203 __table_args__ = (db.Index("ix_expenses_aircraft_id", aircraft_id),)
2206# ── Fuel: standalone refuel record (backlog) ─────────────────────────────────
2209class Refuel(db.Model):
2210 """A refuel not bracketed by a flight (e.g. topping off while at the
2211 airfield for maintenance). Mirrors Flight.fuel_added_*_qty/_unit but
2212 carries no flight linkage — findable from the aircraft page. Can be
2213 turned into a costed Expense the same way a flight's fuel-added figure
2214 can (see expenses._fuel_expense_prefill): only the date and aircraft
2215 matter for cost tracking, not whether the volume was logged here or
2216 on a Flight row."""
2218 __tablename__ = "refuels"
2220 id = db.Column(db.Integer, primary_key=True)
2221 aircraft_id = db.Column(
2222 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2223 )
2224 date = db.Column(db.Date, nullable=False)
2225 quantity = db.Column(db.Numeric(8, 2), nullable=False)
2226 unit = db.Column(db.String(8), nullable=False, default="L") # L, gal
2227 note = db.Column(db.String(255), nullable=True)
2228 created_at = db.Column(
2229 db.DateTime(timezone=True),
2230 nullable=False,
2231 default=lambda: datetime.now(UTC),
2232 )
2233 created_by_id = db.Column(
2234 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2235 )
2237 aircraft = db.relationship("Aircraft", back_populates="refuels")
2238 created_by = db.relationship("User", foreign_keys=[created_by_id])
2240 __table_args__ = (db.Index("ix_refuels_aircraft_id", aircraft_id),)
2243# ── Fuel: per-tank capacity tracking (backlog) ───────────────────────────────
2246class AircraftFuelTank(db.Model):
2247 """One independently-tracked fuel tank on an aircraft (e.g. left/right
2248 wing, or a main + aux tank). An aircraft with a single combined tank
2249 still needs exactly one row here (name it e.g. "Main") — there is no
2250 separate scalar-capacity fallback."""
2252 __tablename__ = "aircraft_fuel_tanks"
2254 id = db.Column(db.Integer, primary_key=True)
2255 aircraft_id = db.Column(
2256 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2257 )
2258 name = db.Column(db.String(64), nullable=False)
2259 capacity_liters = db.Column(db.Numeric(6, 1), nullable=False)
2260 sort_order = db.Column(db.Integer, nullable=False, default=0)
2262 aircraft = db.relationship("Aircraft", back_populates="fuel_tanks")
2264 __table_args__ = (db.Index("ix_aircraft_fuel_tanks_aircraft_id", aircraft_id),)
2267# ── Phase 9 / 27: Document & Photo Uploads ───────────────────────────────────
2270class DocType:
2271 LICENSE = "license"
2272 MEDICAL = "medical"
2273 INSURANCE_CERT = "insurance_certificate"
2274 ARC = "arc_certificate"
2277class DocCategory:
2278 """Broad document categories that map 1-to-1 to on-disk folder names.
2280 Used by the Syncthing-compatible canonical path layout:
2281 {tenant_slug}/{aircraft_reg}/{category}/{YYYY-MM-DD} - {title}.{ext}
2282 """
2284 MAINTENANCE = "maintenance"
2285 INSURANCE = "insurance"
2286 POH = "poh"
2287 AIRWORTHINESS = "airworthiness"
2288 LOGBOOK = "logbook"
2289 INVOICE = "invoice"
2290 OTHER = "other"
2291 UNCATEGORISED = "uncategorised"
2293 ALL: ClassVar[list[str]] = [
2294 MAINTENANCE,
2295 INSURANCE,
2296 POH,
2297 AIRWORTHINESS,
2298 LOGBOOK,
2299 INVOICE,
2300 OTHER,
2301 UNCATEGORISED,
2302 ]
2305class Document(db.Model):
2306 """
2307 A document or photo attached to an aircraft, component, flight entry, or
2308 pilot profile. aircraft_id or pilot_user_id must be set (not both).
2309 """
2311 __tablename__ = "documents"
2313 id = db.Column(db.Integer, primary_key=True)
2314 aircraft_id = db.Column(
2315 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
2316 )
2317 pilot_user_id = db.Column(
2318 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
2319 )
2320 component_id = db.Column(
2321 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=True
2322 )
2323 flight_entry_id = db.Column(
2324 db.Integer,
2325 db.ForeignKey("flights.id", ondelete="CASCADE"),
2326 nullable=True,
2327 )
2328 # Receipt/invoice attached to an expense; such documents also carry
2329 # aircraft_id so access control and the aircraft document list apply.
2330 expense_id = db.Column(
2331 db.Integer, db.ForeignKey("expenses.id", ondelete="CASCADE"), nullable=True
2332 )
2333 # Signed rental agreement attached to a RenterAuthorization (Phase 37c).
2334 # Carries no aircraft_id — visible to the renter concerned and is_owner
2335 # users, not to the aircraft document list.
2336 renter_authorization_id = db.Column(
2337 db.Integer,
2338 db.ForeignKey("renter_authorizations.id", ondelete="CASCADE"),
2339 nullable=True,
2340 )
2341 filename = db.Column(
2342 db.String(512), nullable=False
2343 ) # stored path on disk (may include subdirectories)
2344 original_filename = db.Column(db.String(255), nullable=False) # as uploaded
2345 mime_type = db.Column(db.String(128), nullable=True)
2346 size_bytes = db.Column(db.Integer, nullable=True)
2347 title = db.Column(db.String(128), nullable=True) # optional display name
2348 doc_type = db.Column(db.String(32), nullable=True) # DocType constant
2349 category = db.Column(
2350 db.String(32), nullable=True
2351 ) # DocCategory value; drives on-disk folder
2352 # NULL means "in force as soon as uploaded" (the original behaviour,
2353 # before valid_from existed) -- only set it to model a document that
2354 # shouldn't become the active one until a future date, e.g. next
2355 # quarter's insurance cert uploaded ahead of time. See
2356 # documents/routes.py active_document_for().
2357 valid_from = db.Column(db.Date, nullable=True)
2358 valid_until = db.Column(db.Date, nullable=True)
2359 superseded_by_id = db.Column(
2360 db.Integer, db.ForeignKey("documents.id", ondelete="SET NULL"), nullable=True
2361 )
2362 is_sensitive = db.Column(db.Boolean, nullable=False, default=False)
2363 uploaded_at = db.Column(
2364 db.DateTime(timezone=True),
2365 nullable=False,
2366 default=lambda: datetime.now(UTC),
2367 )
2369 aircraft = db.relationship(
2370 "Aircraft",
2371 back_populates="documents",
2372 foreign_keys=[aircraft_id],
2373 )
2374 pilot_user = db.relationship("User", foreign_keys=[pilot_user_id])
2375 component = db.relationship("Component", back_populates="documents")
2376 flight_entry = db.relationship("Flight", back_populates="documents")
2377 expense = db.relationship("Expense", back_populates="receipts")
2378 renter_authorization = db.relationship(
2379 "RenterAuthorization", back_populates="agreement_documents"
2380 )
2381 superseded_by = db.relationship(
2382 "Document",
2383 foreign_keys=[superseded_by_id],
2384 remote_side="Document.id",
2385 uselist=False,
2386 )
2388 __table_args__ = (
2389 db.Index("ix_documents_aircraft_id", aircraft_id),
2390 db.Index("ix_documents_pilot_user_id", pilot_user_id),
2391 db.Index("ix_documents_component_id", component_id),
2392 db.Index("ix_documents_flight_entry_id", flight_entry_id),
2393 db.Index("ix_documents_expense_id", expense_id),
2394 )
2396 @property
2397 def owner_type(self) -> str:
2398 if self.pilot_user_id:
2399 return "pilot"
2400 if self.component_id:
2401 return "component"
2402 if self.flight_entry_id:
2403 return "entry"
2404 if self.expense_id:
2405 return "expense"
2406 return "aircraft"
2408 @property
2409 def is_image(self) -> bool:
2410 return bool(self.mime_type and self.mime_type.startswith("image/"))
2412 @property
2413 def is_pdf(self) -> bool:
2414 return self.mime_type == "application/pdf"
2416 @property
2417 def is_expiring_soon(self) -> bool:
2418 """True when valid_until is set and within 90 days from today."""
2419 from datetime import date as _date
2421 if self.valid_until is None:
2422 return False
2423 return (self.valid_until - _date.today()).days <= 90
2425 @property
2426 def is_upcoming(self) -> bool:
2427 """True when valid_from is set and still in the future -- this
2428 document is on file but not yet in force."""
2429 from datetime import date as _date
2431 if self.valid_from is None:
2432 return False
2433 return self.valid_from > _date.today()
2436# ── Syncthing reconcile queue ─────────────────────────────────────────────────
2439class PendingReconcile(db.Model):
2440 """Files found on disk (via Syncthing or manual copy) that are not yet
2441 tracked in the documents table. The reconcile screen lets owners review
2442 these files and import them as Document rows with a single click.
2444 filepath is relative to UPLOAD_FOLDER (e.g. 'my-hangar/OO-PNH/maintenance/
2445 2024-03-15 - Annual inspection.pdf'). The unique constraint prevents the
2446 same file from appearing twice in the queue.
2447 """
2449 __tablename__ = "pending_reconcile"
2451 id = db.Column(db.Integer, primary_key=True)
2452 tenant_id = db.Column(
2453 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
2454 )
2455 aircraft_id = db.Column(
2456 db.Integer, db.ForeignKey("aircraft.id", ondelete="SET NULL"), nullable=True
2457 )
2458 filepath = db.Column(db.String(512), nullable=False, unique=True)
2459 category = db.Column(db.String(32), nullable=True)
2460 title_hint = db.Column(db.String(255), nullable=True)
2461 date_hint = db.Column(db.Date, nullable=True)
2462 detected_at = db.Column(
2463 db.DateTime(timezone=True),
2464 nullable=False,
2465 default=lambda: datetime.now(UTC),
2466 )
2467 reconciled_at = db.Column(db.DateTime(timezone=True), nullable=True)
2468 ignored = db.Column(db.Boolean, nullable=False, default=False)
2470 tenant = db.relationship("Tenant")
2471 aircraft = db.relationship("Aircraft")
2474# ── Phase 10: Backup & Restore ────────────────────────────────────────────────
2477class BackupRecord(db.Model):
2478 __tablename__ = "backup_records"
2480 id = db.Column(db.Integer, primary_key=True)
2481 filename = db.Column(db.String(255), nullable=False)
2482 path = db.Column(db.String(512), nullable=False)
2483 size_bytes = db.Column(db.Integer, nullable=True)
2484 sha256 = db.Column(db.String(64), nullable=True)
2485 created_at = db.Column(
2486 db.DateTime(timezone=True),
2487 nullable=False,
2488 default=lambda: datetime.now(UTC),
2489 )
2490 status = db.Column(db.String(32), nullable=False, default="ok") # ok / failed
2491 app_version = db.Column(db.String(64), nullable=True)
2492 alembic_head = db.Column(db.String(64), nullable=True)
2495# ── Phase 11: Read-only Share Links ──────────────────────────────────────────
2498class ShareToken(db.Model):
2499 __tablename__ = "share_tokens"
2501 id = db.Column(db.Integer, primary_key=True)
2502 aircraft_id = db.Column(
2503 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2504 )
2505 token = db.Column(db.String(16), unique=True, nullable=False, index=True)
2506 access_level = db.Column(
2507 db.String(16), nullable=False, default="summary"
2508 ) # summary / full
2509 created_at = db.Column(
2510 db.DateTime(timezone=True),
2511 nullable=False,
2512 default=lambda: datetime.now(UTC),
2513 )
2514 revoked_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)
2516 aircraft = db.relationship("Aircraft", back_populates="share_tokens")
2518 @property
2519 def is_active(self) -> bool:
2520 return self.revoked_at is None
2523# ── Phase 12: Snag List ───────────────────────────────────────────────────────
2526class Snag(db.Model):
2527 __tablename__ = "snags"
2529 id = db.Column(db.Integer, primary_key=True)
2530 aircraft_id = db.Column(
2531 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2532 )
2533 title = db.Column(db.String(128), nullable=False)
2534 description = db.Column(db.Text, nullable=True)
2535 reporter = db.Column(db.String(128), nullable=True)
2536 is_grounding = db.Column(db.Boolean, nullable=False, default=False)
2537 reported_at = db.Column(
2538 db.DateTime(timezone=True),
2539 nullable=False,
2540 default=lambda: datetime.now(UTC),
2541 )
2542 resolved_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)
2543 resolution_note = db.Column(db.Text, nullable=True)
2545 aircraft = db.relationship("Aircraft", back_populates="snags")
2547 __table_args__ = (db.Index("ix_snags_aircraft_id", aircraft_id),)
2549 @property
2550 def is_open(self) -> bool:
2551 return self.resolved_at is None
2554# ── Phase 22: Reservations ───────────────────────────────────────────────────
2557class ReservationStatus(str, enum.Enum):
2558 PENDING = "pending"
2559 CONFIRMED = "confirmed"
2560 CANCELLED = "cancelled"
2563class Reservation(db.Model):
2564 __tablename__ = "reservations"
2566 id = db.Column(db.Integer, primary_key=True)
2567 aircraft_id = db.Column(
2568 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2569 )
2570 pilot_user_id = db.Column(
2571 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2572 )
2573 start_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2574 end_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2575 status = db.Column(
2576 db.Enum(ReservationStatus), nullable=False, default=ReservationStatus.PENDING
2577 )
2578 notes = db.Column(db.Text, nullable=True)
2579 hourly_rate = db.Column(db.Numeric(8, 2), nullable=True) # EUR/h snapshot
2580 estimated_cost = db.Column(db.Numeric(10, 2), nullable=True)
2581 created_at = db.Column(
2582 db.DateTime(timezone=True),
2583 nullable=False,
2584 default=lambda: datetime.now(UTC),
2585 )
2587 aircraft = db.relationship("Aircraft", back_populates="reservations")
2588 pilot = db.relationship("User", foreign_keys=[pilot_user_id])
2589 flights = db.relationship("Flight", back_populates="reservation")
2590 dispatch = db.relationship(
2591 "DispatchRecord", back_populates="reservation", uselist=False
2592 )
2593 rental_charge = db.relationship(
2594 "RentalCharge", back_populates="reservation", uselist=False
2595 )
2597 __table_args__ = (db.Index("ix_reservations_aircraft_id", aircraft_id),)
2599 @property
2600 def duration_hours(self) -> float:
2601 delta = self.end_dt - self.start_dt
2602 return round(delta.total_seconds() / 3600, 2)
2605class DispatchRecord(db.Model):
2606 """Phase 37d: check-out / check-in record for a rental reservation. One
2607 row per reservation (unique constraint) — created at check-out, updated
2608 at check-in."""
2610 __tablename__ = "dispatch_records"
2612 id = db.Column(db.Integer, primary_key=True)
2613 reservation_id = db.Column(
2614 db.Integer,
2615 db.ForeignKey("reservations.id", ondelete="CASCADE"),
2616 nullable=False,
2617 unique=True,
2618 )
2619 # Check-out
2620 out_at = db.Column(db.DateTime(timezone=True), nullable=True)
2621 out_by_id = db.Column(
2622 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2623 )
2624 out_engine_counter = db.Column(db.Numeric(8, 1), nullable=True)
2625 out_flight_counter = db.Column(db.Numeric(8, 1), nullable=True)
2626 out_fuel_state = db.Column(db.String(64), nullable=True)
2627 out_walkaround_ok = db.Column(db.Boolean, nullable=False, default=False)
2628 out_snags_acknowledged = db.Column(db.Boolean, nullable=False, default=False)
2629 # is_owner explicitly dispatched despite a grounding snag — auditability.
2630 out_grounded_override = db.Column(db.Boolean, nullable=False, default=False)
2631 # Check-in
2632 in_at = db.Column(db.DateTime(timezone=True), nullable=True)
2633 in_by_id = db.Column(
2634 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2635 )
2636 in_engine_counter = db.Column(db.Numeric(8, 1), nullable=True)
2637 in_flight_counter = db.Column(db.Numeric(8, 1), nullable=True)
2638 in_fuel_state = db.Column(db.String(64), nullable=True)
2639 in_notes = db.Column(db.Text, nullable=True)
2641 reservation = db.relationship("Reservation", back_populates="dispatch")
2642 out_by = db.relationship("User", foreign_keys=[out_by_id])
2643 in_by = db.relationship("User", foreign_keys=[in_by_id])
2645 @property
2646 def is_checked_out(self) -> bool:
2647 return self.out_at is not None
2649 @property
2650 def is_checked_in(self) -> bool:
2651 return self.in_at is not None
2654class RentalChargeStatus:
2655 DRAFT = "draft"
2656 FINAL = "final"
2658 ALL: ClassVar[set[str]] = {DRAFT, FINAL}
2661class RentalCharge(db.Model):
2662 """Phase 37e: rental charge for one reservation. Drafted automatically at
2663 check-in (see reservations.routes.checkin); the owner reviews and
2664 finalizes it, which posts one CHARGE to the billing ledger (Phase 37a).
2665 A finalized charge is immutable — corrections go through
2666 BillingService.reverse plus a new adjustment, never an edit."""
2668 __tablename__ = "rental_charges"
2670 id = db.Column(db.Integer, primary_key=True)
2671 reservation_id = db.Column(
2672 db.Integer,
2673 db.ForeignKey("reservations.id", ondelete="CASCADE"),
2674 nullable=False,
2675 unique=True,
2676 )
2677 renter_user_id = db.Column(
2678 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
2679 )
2680 status = db.Column(db.String(12), nullable=False, default=RentalChargeStatus.DRAFT)
2681 billable_hours = db.Column(db.Numeric(6, 1), nullable=False)
2682 hourly_rate = db.Column(db.Numeric(8, 2), nullable=False) # snapshot
2683 rate_type = db.Column(db.String(8), nullable=False) # snapshot
2684 fuel_credit = db.Column(
2685 db.Numeric(10, 2), nullable=False, default=0
2686 ) # positive, subtracted
2687 adjustment = db.Column(db.Numeric(10, 2), nullable=False, default=0) # signed
2688 adjustment_note = db.Column(db.String(255), nullable=True)
2689 # True when the drafting logic fell back to the non-preferred counter
2690 # (the rate_basis one was left blank at dispatch) — shown as a note on
2691 # the draft review, not a data-quality error.
2692 fallback_counter_used = db.Column(db.Boolean, nullable=False, default=False)
2693 total = db.Column(
2694 db.Numeric(10, 2), nullable=False
2695 ) # hours*rate - credit + adjustment
2696 finalized_at = db.Column(db.DateTime(timezone=True), nullable=True)
2697 finalized_by_id = db.Column(
2698 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2699 )
2700 created_at = db.Column(
2701 db.DateTime(timezone=True),
2702 nullable=False,
2703 default=lambda: datetime.now(UTC),
2704 )
2706 reservation = db.relationship("Reservation", back_populates="rental_charge")
2707 renter_user = db.relationship("User", foreign_keys=[renter_user_id])
2708 finalized_by = db.relationship("User", foreign_keys=[finalized_by_id])
2710 @property
2711 def is_final(self) -> bool:
2712 return self.status == RentalChargeStatus.FINAL
2715class MaintenanceDowntime(db.Model):
2716 """Phase 37f: owner-entered planned unavailability window (e.g. a shop
2717 appointment). Behaves like a confirmed reservation in conflict
2718 detection and is rendered on the booking calendar in a distinct style.
2719 Downtime is scheduling; a grounding snag is airworthiness — related but
2720 separate records."""
2722 __tablename__ = "maintenance_downtimes"
2724 id = db.Column(db.Integer, primary_key=True)
2725 aircraft_id = db.Column(
2726 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2727 )
2728 start_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2729 end_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2730 reason = db.Column(db.String(255), nullable=True)
2731 created_by_id = db.Column(
2732 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2733 )
2734 created_at = db.Column(
2735 db.DateTime(timezone=True),
2736 nullable=False,
2737 default=lambda: datetime.now(UTC),
2738 )
2740 aircraft = db.relationship("Aircraft", back_populates="maintenance_downtimes")
2741 created_by = db.relationship("User", foreign_keys=[created_by_id])
2744class RateBasis:
2745 """Phase 37b: which counter delta a rental charge is billed against."""
2747 ENGINE_TIME = "engine_time"
2748 FLIGHT_TIME = "flight_time"
2750 ALL: ClassVar[set[str]] = {ENGINE_TIME, FLIGHT_TIME}
2751 LABELS: ClassVar[dict[str, str]] = {
2752 ENGINE_TIME: "Engine time",
2753 FLIGHT_TIME: "Flight time",
2754 }
2757class RateType:
2758 """Phase 37b: wet (fuel included) vs. dry (fuel billed separately)."""
2760 WET = "wet"
2761 DRY = "dry"
2763 ALL: ClassVar[set[str]] = {WET, DRY}
2764 LABELS: ClassVar[dict[str, str]] = {
2765 WET: "Wet",
2766 DRY: "Dry",
2767 }
2770class AircraftBookingSettings(db.Model):
2771 """Per-aircraft booking rules and hourly rate for cost estimation."""
2773 __tablename__ = "aircraft_booking_settings"
2775 aircraft_id = db.Column(
2776 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True
2777 )
2778 min_booking_hours = db.Column(db.Numeric(4, 1), nullable=True)
2779 max_booking_hours = db.Column(db.Numeric(4, 1), nullable=True)
2780 hourly_rate = db.Column(db.Numeric(8, 2), nullable=True) # EUR/h
2781 rate_basis = db.Column(db.String(16), nullable=False, default=RateBasis.ENGINE_TIME)
2782 rate_type = db.Column(db.String(8), nullable=False, default=RateType.WET)
2783 min_hours_per_day = db.Column(db.Numeric(4, 1), nullable=True)
2785 aircraft = db.relationship("Aircraft", back_populates="booking_settings")
2788class RenterAuthorization(db.Model):
2789 """Phase 37c: owner-verified rental qualification for one renter, scoped
2790 to one aircraft or the whole fleet (aircraft_id is NULL).
2792 These are owner-entered verification facts — deliberately NOT automatic
2793 reads of the renter's private PilotProfile.
2794 """
2796 __tablename__ = "renter_authorizations"
2797 __table_args__ = (
2798 db.Index("ix_renter_authorizations_tenant_id", "tenant_id"),
2799 db.Index("ix_renter_authorizations_renter_user_id", "renter_user_id"),
2800 )
2802 id = db.Column(db.Integer, primary_key=True)
2803 tenant_id = db.Column(
2804 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
2805 )
2806 renter_user_id = db.Column(
2807 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
2808 )
2809 aircraft_id = db.Column(
2810 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
2811 ) # NULL = whole fleet
2812 authorized_by_id = db.Column(
2813 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2814 )
2815 granted_on = db.Column(db.Date, nullable=False)
2816 expires_on = db.Column(db.Date, nullable=True) # NULL = does not expire
2817 checkout_flight_on = db.Column(db.Date, nullable=True)
2818 licence_seen_on = db.Column(db.Date, nullable=True)
2819 medical_valid_until = db.Column(db.Date, nullable=True) # owner-entered
2820 notes = db.Column(db.Text, nullable=True)
2821 revoked_at = db.Column(db.DateTime(timezone=True), nullable=True)
2822 created_at = db.Column(
2823 db.DateTime(timezone=True),
2824 nullable=False,
2825 default=lambda: datetime.now(UTC),
2826 )
2828 tenant = db.relationship("Tenant")
2829 renter_user = db.relationship("User", foreign_keys=[renter_user_id])
2830 authorized_by = db.relationship("User", foreign_keys=[authorized_by_id])
2831 aircraft = db.relationship("Aircraft")
2832 agreement_documents = db.relationship(
2833 "Document", back_populates="renter_authorization"
2834 )
2836 @property
2837 def is_valid(self) -> bool:
2838 from datetime import date as _date
2840 if self.revoked_at is not None:
2841 return False
2842 today = _date.today()
2843 if self.expires_on is not None and self.expires_on < today:
2844 return False
2845 return not (
2846 self.medical_valid_until is not None and self.medical_valid_until < today
2847 )
2849 @staticmethod
2850 def valid_for(
2851 renter_user_id: int, aircraft_id: int
2852 ) -> "RenterAuthorization | None":
2853 """Return a valid authorization covering this renter+aircraft — a
2854 fleet-wide row (aircraft_id IS NULL) or a per-aircraft row — or None."""
2855 candidates = RenterAuthorization.query.filter(
2856 RenterAuthorization.renter_user_id == renter_user_id,
2857 RenterAuthorization.revoked_at.is_(None),
2858 db.or_(
2859 RenterAuthorization.aircraft_id.is_(None),
2860 RenterAuthorization.aircraft_id == aircraft_id,
2861 ),
2862 ).all()
2863 return next((c for c in candidates if c.is_valid), None)
2866# ── Phase 20: Mass & Balance ──────────────────────────────────────────────────
2868FUEL_DENSITY = {
2869 "avgas": 0.72, # Avgas 100LL
2870 "ul91": 0.72, # UL91 — unleaded avgas replacement
2871 "mogas": 0.74, # Automotive gasoline (Mogas)
2872 "jet_a1": 0.81, # Jet-A1 (kerosene)
2873} # kg/L
2874GAL_TO_L = 3.78541 # US gallons to litres
2877class WeightBalanceConfig(db.Model):
2878 __tablename__ = "wb_configs"
2880 id = db.Column(db.Integer, primary_key=True)
2881 aircraft_id = db.Column(
2882 db.Integer,
2883 db.ForeignKey("aircraft.id", ondelete="CASCADE"),
2884 nullable=False,
2885 unique=True,
2886 )
2887 empty_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg
2888 empty_cg_arm = db.Column(db.Numeric(7, 2), nullable=False) # m from datum
2889 max_takeoff_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg
2890 forward_cg_limit = db.Column(db.Numeric(7, 2), nullable=False) # m
2891 aft_cg_limit = db.Column(db.Numeric(7, 2), nullable=False) # m
2892 fuel_unit = db.Column(db.String(3), nullable=False, default="L") # "L" or "gal"
2893 # Optional non-rectangular envelope: list of [arm_m, weight_kg] pairs in polygon order.
2894 # When ≥ 3 points are present they override forward_cg_limit/aft_cg_limit/max_takeoff_weight
2895 # for the in-envelope check.
2896 envelope_points = db.Column(db.JSON, nullable=True)
2897 datum_note = db.Column(db.String(200), nullable=True)
2899 aircraft = db.relationship("Aircraft", back_populates="wb_config")
2900 stations = db.relationship(
2901 "WeightBalanceStation",
2902 back_populates="config",
2903 cascade="all, delete-orphan",
2904 order_by="WeightBalanceStation.position",
2905 )
2906 entries = db.relationship(
2907 "WeightBalanceEntry",
2908 back_populates="config",
2909 cascade="all, delete-orphan",
2910 )
2913class WeightBalanceStation(db.Model):
2914 __tablename__ = "wb_stations"
2916 id = db.Column(db.Integer, primary_key=True)
2917 config_id = db.Column(
2918 db.Integer, db.ForeignKey("wb_configs.id", ondelete="CASCADE"), nullable=False
2919 )
2920 label = db.Column(db.String(64), nullable=False)
2921 arm = db.Column(db.Numeric(7, 2), nullable=False) # m from datum
2922 max_weight = db.Column(db.Numeric(6, 2), nullable=True) # kg limit (non-fuel only)
2923 capacity = db.Column(db.Float, nullable=True) # L or gal (fuel stations)
2924 is_fuel = db.Column(db.Boolean, nullable=False, default=False)
2925 position = db.Column(db.Integer, nullable=False, default=0) # display order
2927 config = db.relationship("WeightBalanceConfig", back_populates="stations")
2930class WeightBalanceEntry(db.Model):
2931 __tablename__ = "wb_entries"
2933 id = db.Column(db.Integer, primary_key=True)
2934 config_id = db.Column(
2935 db.Integer, db.ForeignKey("wb_configs.id", ondelete="CASCADE"), nullable=False
2936 )
2937 date = db.Column(db.Date, nullable=False)
2938 label = db.Column(db.String(100), nullable=True)
2939 total_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg
2940 loaded_cg = db.Column(db.Numeric(7, 2), nullable=False) # mm
2941 is_in_envelope = db.Column(db.Boolean, nullable=False)
2942 # {station_id_str: value} — fuel stations store volume (L or gal), non-fuel store kg
2943 station_weights = db.Column(db.JSON, nullable=False, default=dict)
2944 created_at = db.Column(
2945 db.DateTime(timezone=True),
2946 nullable=False,
2947 default=lambda: datetime.now(UTC),
2948 )
2950 config = db.relationship("WeightBalanceConfig", back_populates="entries")
2953class AppSetting(db.Model):
2954 __tablename__ = "app_settings"
2956 key = db.Column(db.String(64), primary_key=True)
2957 value = db.Column(db.Text, nullable=True)
2960# ── Phase 33: Airworthiness Requirements Tracker ──────────────────────────────
2963class EASASourceNode(db.Model):
2964 """
2965 Maps a Component to one leaf node in the EASA Safety Publications Tool
2966 taxonomy tree (TC holder → type → model). One component may have multiple
2967 nodes (e.g. base TC plus an installed STC that also carries ADs).
2968 """
2970 __tablename__ = "easa_source_nodes"
2972 id = db.Column(db.Integer, primary_key=True)
2973 component_id = db.Column(
2974 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=False
2975 )
2976 tc_holder_node_id = db.Column(db.String(16), nullable=False)
2977 tc_holder_name = db.Column(db.String(128), nullable=False)
2978 type_node_id = db.Column(db.String(16), nullable=False)
2979 type_name = db.Column(db.String(128), nullable=False)
2980 model_node_id = db.Column(db.String(16), nullable=False)
2981 model_name = db.Column(db.String(128), nullable=False)
2982 last_synced_at = db.Column(db.DateTime(timezone=True), nullable=True)
2983 consecutive_errors = db.Column(db.Integer, nullable=False, default=0)
2985 component = db.relationship("Component", back_populates="easa_source_nodes")
2986 documents = db.relationship(
2987 "AirworthinessDocument",
2988 back_populates="source_node",
2989 cascade="all, delete-orphan",
2990 )
2992 @property
2993 def display_path(self) -> str:
2994 return f"{self.tc_holder_name} / {self.type_name} / {self.model_name}"
2997class AirworthinessDocType:
2998 AD = "ad"
2999 MANDATORY_SB = "mandatory_sb"
3000 SB = "sb"
3001 SIB = "sib"
3002 ARC = "arc"
3003 MANUAL = "manual"
3005 ALL = (AD, MANDATORY_SB, SB, SIB, ARC, MANUAL)
3006 SYNCED = (AD, SIB) # types populated by EASA sync
3007 # N_()-marked: rendered through gettext() at the template layer, not here.
3008 LABELS: ClassVar[dict[str, str]] = {
3009 AD: N_("AD"),
3010 MANDATORY_SB: N_("Mandatory SB"),
3011 SB: N_("SB"),
3012 SIB: N_("SIB"),
3013 ARC: N_("ARC"),
3014 MANUAL: N_("Manual"),
3015 }
3018class AirworthinessDocStatus:
3019 PENDING_REVIEW = "pending_review"
3020 COMPLIED = "complied"
3021 NOT_APPLICABLE = "not_applicable"
3022 DEFERRED = "deferred"
3023 QUESTION = "question"
3025 ALL = (PENDING_REVIEW, COMPLIED, NOT_APPLICABLE, DEFERRED, QUESTION)
3028class AirworthinessDocument(db.Model):
3029 """
3030 One airworthiness-related document (AD, SB, SIB, ARC, …) applicable to a
3031 component. Synced documents reference a source_node; manually entered
3032 documents have source_node_id = NULL.
3033 """
3035 __tablename__ = "airworthiness_documents"
3037 id = db.Column(db.Integer, primary_key=True)
3038 doc_type = db.Column(db.String(16), nullable=False)
3039 reference = db.Column(db.String(64), nullable=False)
3040 title = db.Column(db.String(256), nullable=True)
3041 source_node_id = db.Column(
3042 db.Integer,
3043 db.ForeignKey("easa_source_nodes.id", ondelete="CASCADE"),
3044 nullable=True,
3045 )
3046 # For manual entries without a source node, store the component directly
3047 component_id = db.Column(
3048 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=True
3049 )
3050 doc_url = db.Column(db.String(512), nullable=True)
3051 # For ARC: date the certificate expires
3052 expiry_date = db.Column(db.Date, nullable=True)
3053 first_seen_at = db.Column(
3054 db.DateTime(timezone=True),
3055 nullable=False,
3056 default=lambda: datetime.now(UTC),
3057 )
3059 source_node = db.relationship("EASASourceNode", back_populates="documents")
3060 component = db.relationship("Component", back_populates="airworthiness_documents")
3061 statuses = db.relationship(
3062 "AirworthinessDocumentStatus",
3063 back_populates="document",
3064 cascade="all, delete-orphan",
3065 )
3067 @property
3068 def is_manual(self) -> bool:
3069 return self.source_node_id is None
3072class AirworthinessDocumentStatus(db.Model):
3073 """
3074 Compliance state of one AirworthinessDocument for one aircraft.
3075 Unique per (aircraft_id, document_id).
3076 """
3078 __tablename__ = "airworthiness_document_statuses"
3079 __table_args__ = (
3080 db.UniqueConstraint("aircraft_id", "document_id", name="uq_aw_status"),
3081 )
3083 id = db.Column(db.Integer, primary_key=True)
3084 aircraft_id = db.Column(
3085 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3086 )
3087 document_id = db.Column(
3088 db.Integer,
3089 db.ForeignKey("airworthiness_documents.id", ondelete="CASCADE"),
3090 nullable=False,
3091 )
3092 status = db.Column(
3093 db.String(24), nullable=False, default=AirworthinessDocStatus.PENDING_REVIEW
3094 )
3095 notes = db.Column(db.Text, nullable=True)
3096 compliance_date = db.Column(db.Date, nullable=True)
3097 next_review_date = db.Column(db.Date, nullable=True)
3098 updated_at = db.Column(
3099 db.DateTime(timezone=True),
3100 nullable=False,
3101 default=lambda: datetime.now(UTC),
3102 onupdate=lambda: datetime.now(UTC),
3103 )
3105 aircraft = db.relationship("Aircraft", back_populates="airworthiness_statuses")
3106 document = db.relationship("AirworthinessDocument", back_populates="statuses")
3109class InstalledSTC(db.Model):
3110 """
3111 Registry of Supplemental Type Certificates physically installed on an
3112 aircraft. No compliance workflow — presence/absence is the record.
3113 """
3115 __tablename__ = "installed_stcs"
3117 id = db.Column(db.Integer, primary_key=True)
3118 aircraft_id = db.Column(
3119 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3120 )
3121 stc_number = db.Column(db.String(64), nullable=False)
3122 title = db.Column(db.String(256), nullable=True)
3123 tc_holder = db.Column(db.String(128), nullable=True)
3124 installation_date = db.Column(db.Date, nullable=True)
3125 notes = db.Column(db.Text, nullable=True)
3127 aircraft = db.relationship("Aircraft", back_populates="installed_stcs")
3130class AdSbStatus:
3131 """Status tags for an AD/SB compliance-board item (backlog: AD/SB
3132 compliance board — an owner's working summary, not the authoritative
3133 FAA/EASA record)."""
3135 RECURRING = "recurring"
3136 CONDITIONAL = "conditional"
3137 VERIFY_PART_NUMBER = "verify_part_number"
3138 CLOSED = "closed"
3139 NOT_APPLICABLE = "not_applicable"
3140 ALL: ClassVar[set[str]] = {
3141 RECURRING,
3142 CONDITIONAL,
3143 VERIFY_PART_NUMBER,
3144 CLOSED,
3145 NOT_APPLICABLE,
3146 }
3147 LABELS: ClassVar[dict[str, str]] = {
3148 RECURRING: "Recurring",
3149 CONDITIONAL: "Conditional",
3150 VERIFY_PART_NUMBER: "Verify part number",
3151 CLOSED: "Closed",
3152 NOT_APPLICABLE: "N/A",
3153 }
3154 # Statuses that still need attention at the next annual — drives both
3155 # the board's "open" filter and the print/checklist item list.
3156 OPEN: ClassVar[set[str]] = {RECURRING, CONDITIONAL, VERIFY_PART_NUMBER}
3159class AdSbItem(db.Model):
3160 """An owner's working AD/SB (Airworthiness Directive / Service
3161 Bulletin) compliance-tracking entry for one aircraft. Explicitly not
3162 the authoritative FAA/EASA record — see the disclaimer shown on the
3163 board itself."""
3165 __tablename__ = "ad_sb_items"
3167 id = db.Column(db.Integer, primary_key=True)
3168 aircraft_id = db.Column(
3169 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3170 )
3171 reference = db.Column(db.String(64), nullable=False) # e.g. "AD 2023-0048"
3172 title = db.Column(db.String(256), nullable=True)
3173 status = db.Column(
3174 db.String(32),
3175 nullable=False,
3176 default=AdSbStatus.CONDITIONAL,
3177 server_default=AdSbStatus.CONDITIONAL,
3178 )
3179 notes = db.Column(db.Text, nullable=True)
3180 created_at = db.Column(
3181 db.DateTime(timezone=True),
3182 nullable=False,
3183 default=lambda: datetime.now(UTC),
3184 )
3186 aircraft = db.relationship("Aircraft", back_populates="ad_sb_items")
3188 __table_args__ = (db.Index("ix_ad_sb_items_aircraft_id", aircraft_id),)
3191class EquipmentStatus:
3192 """Per-unit status for an avionics/equipment inventory item (backlog:
3193 avionics inventory with per-unit status and an upgrade wish list)."""
3195 SERVICEABLE = "serviceable"
3196 OPEN_SQUAWK = "open_squawk"
3197 PLACARDED_INOPERATIVE = "placarded_inoperative"
3198 ALL: ClassVar[set[str]] = {SERVICEABLE, OPEN_SQUAWK, PLACARDED_INOPERATIVE}
3199 LABELS: ClassVar[dict[str, str]] = {
3200 SERVICEABLE: "Serviceable",
3201 OPEN_SQUAWK: "Open squawk",
3202 PLACARDED_INOPERATIVE: "Placarded inoperative",
3203 }
3206class AvionicsUnit(db.Model):
3207 """An installed avionics/equipment unit tracked separately from the
3208 generic Component/maintenance-trigger model — this is a simple
3209 inventory entry (role, status, certification notes), not a TBO/life-
3210 limit-tracked part."""
3212 __tablename__ = "avionics_units"
3214 id = db.Column(db.Integer, primary_key=True)
3215 aircraft_id = db.Column(
3216 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3217 )
3218 role = db.Column(db.String(64), nullable=False) # e.g. "COM1", "Transponder"
3219 make = db.Column(db.String(64), nullable=True)
3220 model = db.Column(db.String(64), nullable=True)
3221 serial_number = db.Column(db.String(64), nullable=True)
3222 status = db.Column(
3223 db.String(32),
3224 nullable=False,
3225 default=EquipmentStatus.SERVICEABLE,
3226 server_default=EquipmentStatus.SERVICEABLE,
3227 )
3228 # Certification history and STC/AFMS approvals — free text rather than a
3229 # separate sub-model, matching this table's own "simple inventory" scope.
3230 certification_notes = db.Column(db.Text, nullable=True)
3231 notes = db.Column(db.Text, nullable=True)
3232 created_at = db.Column(
3233 db.DateTime(timezone=True),
3234 nullable=False,
3235 default=lambda: datetime.now(UTC),
3236 )
3238 aircraft = db.relationship("Aircraft", back_populates="avionics_units")
3240 __table_args__ = (db.Index("ix_avionics_units_aircraft_id", aircraft_id),)
3243class EquipmentWishlistItem(db.Model):
3244 """A planned equipment upgrade an owner is considering — separate from
3245 what's actually installed (AvionicsUnit)."""
3247 __tablename__ = "equipment_wishlist_items"
3249 id = db.Column(db.Integer, primary_key=True)
3250 aircraft_id = db.Column(
3251 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3252 )
3253 title = db.Column(db.String(256), nullable=False)
3254 rough_cost = db.Column(db.Numeric(10, 2), nullable=True)
3255 # What installing it would require — downtime, STC, panel space, etc.
3256 requirements = db.Column(db.Text, nullable=True)
3257 notes = db.Column(db.Text, nullable=True)
3258 created_at = db.Column(
3259 db.DateTime(timezone=True),
3260 nullable=False,
3261 default=lambda: datetime.now(UTC),
3262 )
3264 aircraft = db.relationship("Aircraft", back_populates="equipment_wishlist_items")
3266 __table_args__ = (db.Index("ix_equipment_wishlist_items_aircraft_id", aircraft_id),)
3269# ── Phase 34: Email Notifications ─────────────────────────────────────────────
3272class NotificationType:
3273 """String constants for all supported notification types.
3275 Stored as plain strings in the DB — no enum migration needed to add types.
3276 """
3278 GROUNDING_SNAG_OPENED = "grounding_snag_opened"
3279 SNAG_REPORTED = "snag_reported"
3280 RESERVATION_CONFIRMED = "reservation_confirmed"
3281 RESERVATION_CANCELLED = "reservation_cancelled"
3282 RESERVATION_REQUEST = "reservation_request"
3283 MAINTENANCE_DUE_SOON = "maintenance_due_soon"
3284 MAINTENANCE_OVERDUE = "maintenance_overdue"
3285 INSURANCE_EXPIRING = "insurance_expiring"
3286 ARC_EXPIRY = "arc_expiry"
3287 MEDICAL_EXPIRING = "medical_expiring"
3288 SEP_RATING_EXPIRING = "sep_rating_expiring"
3289 DOCUMENT_EXPIRING = "document_expiring"
3290 NEW_MEMBER_JOINED = "new_member_joined"
3291 AIRWORTHINESS_REVIEW_DUE = "airworthiness_review_due"
3292 EASA_SYNC_NEW_AD = "easa_sync_new_ad"
3293 RENTER_AUTHORIZATION_EXPIRY = "renter_authorization_expiry"
3294 RESERVATION_AIRCRAFT_GROUNDED = "reservation_aircraft_grounded"
3295 PERSONAL_MINIMUMS_RECENCY = "personal_minimums_recency"
3296 CREW_INVITE = "crew_invite"
3297 CREW_INVITE_ANSWERED = "crew_invite_answered"
3298 SHARED_FLIGHT_CHANGED = "shared_flight_changed"
3299 FLIGHT_CORRECTION = "flight_correction"
3300 CREW_CLAIM = "crew_claim"
3302 ALL: ClassVar[list[str]] = [
3303 GROUNDING_SNAG_OPENED,
3304 SNAG_REPORTED,
3305 RESERVATION_CONFIRMED,
3306 RESERVATION_CANCELLED,
3307 RESERVATION_REQUEST,
3308 MAINTENANCE_DUE_SOON,
3309 MAINTENANCE_OVERDUE,
3310 INSURANCE_EXPIRING,
3311 ARC_EXPIRY,
3312 MEDICAL_EXPIRING,
3313 SEP_RATING_EXPIRING,
3314 DOCUMENT_EXPIRING,
3315 NEW_MEMBER_JOINED,
3316 AIRWORTHINESS_REVIEW_DUE,
3317 EASA_SYNC_NEW_AD,
3318 RENTER_AUTHORIZATION_EXPIRY,
3319 RESERVATION_AIRCRAFT_GROUNDED,
3320 PERSONAL_MINIMUMS_RECENCY,
3321 CREW_INVITE,
3322 CREW_INVITE_ANSWERED,
3323 SHARED_FLIGHT_CHANGED,
3324 FLIGHT_CORRECTION,
3325 CREW_CLAIM,
3326 ]
3328 # System defaults — coded constants; DB only stores per-user or per-tenant overrides
3329 SYSTEM_DEFAULTS: ClassVar[dict[str, dict]] = {
3330 GROUNDING_SNAG_OPENED: {"enabled": True, "threshold_days": None},
3331 SNAG_REPORTED: {"enabled": False, "threshold_days": None},
3332 RESERVATION_CONFIRMED: {"enabled": True, "threshold_days": None},
3333 RESERVATION_CANCELLED: {"enabled": True, "threshold_days": None},
3334 RESERVATION_REQUEST: {"enabled": True, "threshold_days": None},
3335 MAINTENANCE_DUE_SOON: {"enabled": True, "threshold_days": 30},
3336 MAINTENANCE_OVERDUE: {"enabled": True, "threshold_days": None},
3337 INSURANCE_EXPIRING: {"enabled": True, "threshold_days": 30},
3338 ARC_EXPIRY: {"enabled": True, "threshold_days": 60},
3339 MEDICAL_EXPIRING: {"enabled": True, "threshold_days": 60},
3340 SEP_RATING_EXPIRING: {"enabled": True, "threshold_days": 60},
3341 DOCUMENT_EXPIRING: {"enabled": True, "threshold_days": 30},
3342 NEW_MEMBER_JOINED: {"enabled": False, "threshold_days": None},
3343 AIRWORTHINESS_REVIEW_DUE: {"enabled": True, "threshold_days": 30},
3344 EASA_SYNC_NEW_AD: {"enabled": True, "threshold_days": None},
3345 RENTER_AUTHORIZATION_EXPIRY: {"enabled": True, "threshold_days": 30},
3346 RESERVATION_AIRCRAFT_GROUNDED: {"enabled": True, "threshold_days": None},
3347 PERSONAL_MINIMUMS_RECENCY: {"enabled": True, "threshold_days": None},
3348 CREW_INVITE: {"enabled": True, "threshold_days": None},
3349 CREW_INVITE_ANSWERED: {"enabled": True, "threshold_days": None},
3350 SHARED_FLIGHT_CHANGED: {"enabled": True, "threshold_days": None},
3351 FLIGHT_CORRECTION: {"enabled": True, "threshold_days": None},
3352 CREW_CLAIM: {"enabled": True, "threshold_days": None},
3353 }
3355 # Capability flags required — user sees this type in their prefs if they have >= 1
3356 # "is_owner" | "is_pilot" | "is_maint" match init.py context processor naming
3357 REQUIRED_CAPS: ClassVar[dict[str, list[str]]] = {
3358 GROUNDING_SNAG_OPENED: ["is_owner", "is_maint"],
3359 SNAG_REPORTED: ["is_owner"],
3360 RESERVATION_CONFIRMED: ["is_pilot"],
3361 RESERVATION_CANCELLED: ["is_pilot"],
3362 RESERVATION_REQUEST: ["is_owner"],
3363 MAINTENANCE_DUE_SOON: ["is_owner", "is_maint"],
3364 MAINTENANCE_OVERDUE: ["is_owner", "is_maint"],
3365 INSURANCE_EXPIRING: ["is_owner"],
3366 ARC_EXPIRY: ["is_owner"],
3367 MEDICAL_EXPIRING: ["is_pilot"],
3368 SEP_RATING_EXPIRING: ["is_pilot"],
3369 DOCUMENT_EXPIRING: ["is_owner", "is_maint"],
3370 NEW_MEMBER_JOINED: ["is_owner"],
3371 AIRWORTHINESS_REVIEW_DUE: ["is_owner", "is_maint"],
3372 EASA_SYNC_NEW_AD: ["is_owner", "is_maint"],
3373 RENTER_AUTHORIZATION_EXPIRY: ["is_owner"],
3374 # Any authenticated role that could hold a reservation.
3375 RESERVATION_AIRCRAFT_GROUNDED: ["is_owner", "is_pilot", "is_maint"],
3376 PERSONAL_MINIMUMS_RECENCY: ["is_pilot"],
3377 CREW_INVITE: ["is_pilot"],
3378 CREW_INVITE_ANSWERED: ["is_pilot"],
3379 SHARED_FLIGHT_CHANGED: ["is_pilot"],
3380 FLIGHT_CORRECTION: ["is_pilot"],
3381 CREW_CLAIM: ["is_pilot"],
3382 }
3384 # Types that have a configurable days-ahead threshold
3385 HAS_THRESHOLD: ClassVar[set[str]] = {
3386 MAINTENANCE_DUE_SOON,
3387 INSURANCE_EXPIRING,
3388 ARC_EXPIRY,
3389 MEDICAL_EXPIRING,
3390 SEP_RATING_EXPIRING,
3391 DOCUMENT_EXPIRING,
3392 AIRWORTHINESS_REVIEW_DUE,
3393 RENTER_AUTHORIZATION_EXPIRY,
3394 }
3397class NotificationPreference(db.Model):
3398 """Per-user notification preference override within a tenant (level 1 of 3)."""
3400 __tablename__ = "notification_preferences"
3401 __table_args__ = (
3402 db.UniqueConstraint(
3403 "user_id", "tenant_id", "notification_type", name="uq_notif_pref"
3404 ),
3405 )
3407 id = db.Column(db.Integer, primary_key=True)
3408 user_id = db.Column(
3409 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3410 )
3411 tenant_id = db.Column(
3412 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3413 )
3414 notification_type = db.Column(db.String(64), nullable=False)
3415 enabled = db.Column(db.Boolean, nullable=False)
3416 threshold_days = db.Column(db.Integer, nullable=True)
3417 updated_at = db.Column(
3418 db.DateTime(timezone=True),
3419 nullable=False,
3420 default=lambda: datetime.now(UTC),
3421 onupdate=lambda: datetime.now(UTC),
3422 )
3424 user = db.relationship("User")
3425 tenant = db.relationship("Tenant")
3428class TenantNotificationDefault(db.Model):
3429 """Per-tenant override of system notification defaults (level 2 of 3)."""
3431 __tablename__ = "tenant_notification_defaults"
3432 __table_args__ = (
3433 db.UniqueConstraint(
3434 "tenant_id", "notification_type", name="uq_tenant_notif_default"
3435 ),
3436 )
3438 id = db.Column(db.Integer, primary_key=True)
3439 tenant_id = db.Column(
3440 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3441 )
3442 notification_type = db.Column(db.String(64), nullable=False)
3443 enabled = db.Column(db.Boolean, nullable=False)
3444 threshold_days = db.Column(db.Integer, nullable=True)
3445 updated_at = db.Column(
3446 db.DateTime(timezone=True),
3447 nullable=False,
3448 default=lambda: datetime.now(UTC),
3449 onupdate=lambda: datetime.now(UTC),
3450 )
3452 tenant = db.relationship("Tenant")
3455class NotificationSendLog(db.Model):
3456 """Records that a given (user, notification instance) was emailed on a
3457 given calendar day -- makes run_daily_checks() idempotent within a day,
3458 so a server restart mid-day (or a second manual run) does not resend
3459 everything already sent earlier. subject_ref identifies the specific
3460 instance a notification is about (e.g. "aircraft:12", "document:34"),
3461 distinct from notification_type which identifies the kind of alert."""
3463 __tablename__ = "notification_send_log"
3464 __table_args__ = (
3465 db.UniqueConstraint(
3466 "user_id",
3467 "tenant_id",
3468 "notification_type",
3469 "subject_ref",
3470 "sent_date",
3471 name="uq_notif_send_log",
3472 ),
3473 )
3475 id = db.Column(db.Integer, primary_key=True)
3476 user_id = db.Column(
3477 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3478 )
3479 tenant_id = db.Column(
3480 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3481 )
3482 notification_type = db.Column(db.String(64), nullable=False)
3483 subject_ref = db.Column(db.String(128), nullable=False)
3484 sent_date = db.Column(db.Date, nullable=False)
3485 created_at = db.Column(
3486 db.DateTime(timezone=True),
3487 nullable=False,
3488 default=lambda: datetime.now(UTC),
3489 )
3491 user = db.relationship("User")
3492 tenant = db.relationship("Tenant")
3495class NotificationSnooze(db.Model):
3496 """Per-recipient, per-instance reminder snooze, keyed the same way as
3497 NotificationSendLog (notification_type + subject_ref). snoozed_value is
3498 NULL until the user follows the one-click email link and confirms;
3499 once set, it is compared against the live deadline value on every
3500 subsequent check -- a match suppresses the email, a mismatch means the
3501 underlying deadline changed (e.g. a renewed document was uploaded) and
3502 the snooze is treated as stale/cleared automatically."""
3504 __tablename__ = "notification_snoozes"
3505 __table_args__ = (
3506 db.UniqueConstraint(
3507 "user_id",
3508 "tenant_id",
3509 "notification_type",
3510 "subject_ref",
3511 name="uq_notif_snooze",
3512 ),
3513 )
3515 id = db.Column(db.Integer, primary_key=True)
3516 user_id = db.Column(
3517 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3518 )
3519 tenant_id = db.Column(
3520 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3521 )
3522 notification_type = db.Column(db.String(64), nullable=False)
3523 subject_ref = db.Column(db.String(128), nullable=False)
3524 token = db.Column(
3525 db.String(64),
3526 unique=True,
3527 nullable=False,
3528 default=lambda: secrets.token_urlsafe(32),
3529 )
3530 label = db.Column(db.String(256), nullable=False)
3531 current_value = db.Column(db.String(64), nullable=False)
3532 snoozed_value = db.Column(db.String(64), nullable=True)
3533 snoozed_at = db.Column(db.DateTime(timezone=True), nullable=True)
3534 created_at = db.Column(
3535 db.DateTime(timezone=True),
3536 nullable=False,
3537 default=lambda: datetime.now(UTC),
3538 )
3540 user = db.relationship("User")
3541 tenant = db.relationship("Tenant")
3543 @property
3544 def is_active(self) -> bool:
3545 return (
3546 self.snoozed_value is not None and self.snoozed_value == self.current_value
3547 )
3550class BillingAccountKind:
3551 """Shared billing core (Phases 37/39/41) — see docs/billing_service_design.md."""
3553 RENTER = "renter" # Phase 37 — scoped to the tenant (all aircraft)
3554 CO_OWNER = "co_owner" # Phase 39 — scoped to one aircraft
3555 MEMBER = "member" # Phase 41 — scoped to the tenant
3557 ALL: ClassVar[set[str]] = {RENTER, CO_OWNER, MEMBER}
3560class BillingAccount(db.Model):
3561 """One row per (tenant, user, scope). Created lazily by BillingService —
3562 there is no UI to create an account directly."""
3564 __tablename__ = "billing_accounts"
3565 __table_args__ = (
3566 # A plain UniqueConstraint on (tenant_id, user_id, kind, aircraft_id)
3567 # would NOT prevent duplicate renter/member accounts: aircraft_id is
3568 # NULL for those (tenant-scoped, not aircraft-scoped) kinds, and SQL
3569 # unique constraints treat NULL as distinct from every other NULL.
3570 # Two partial unique indexes close that gap: one for aircraft-scoped
3571 # (co_owner) rows, one for tenant-scoped (renter/member) rows.
3572 db.Index(
3573 "uq_billing_account_scope_aircraft",
3574 "tenant_id",
3575 "user_id",
3576 "kind",
3577 "aircraft_id",
3578 unique=True,
3579 sqlite_where=db.text("aircraft_id IS NOT NULL"),
3580 postgresql_where=db.text("aircraft_id IS NOT NULL"),
3581 ),
3582 db.Index(
3583 "uq_billing_account_scope_fleet",
3584 "tenant_id",
3585 "user_id",
3586 "kind",
3587 unique=True,
3588 sqlite_where=db.text("aircraft_id IS NULL"),
3589 postgresql_where=db.text("aircraft_id IS NULL"),
3590 ),
3591 db.Index("ix_billing_accounts_tenant_id", "tenant_id"),
3592 )
3594 id = db.Column(db.Integer, primary_key=True)
3595 tenant_id = db.Column(
3596 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3597 )
3598 user_id = db.Column(
3599 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3600 )
3601 aircraft_id = db.Column(
3602 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
3603 ) # co_owner only
3604 kind = db.Column(db.String(16), nullable=False) # BillingAccountKind
3605 currency = db.Column(db.String(4), nullable=False, default="EUR")
3606 created_at = db.Column(
3607 db.DateTime(timezone=True),
3608 nullable=False,
3609 default=lambda: datetime.now(UTC),
3610 )
3612 tenant = db.relationship("Tenant")
3613 user = db.relationship("User", foreign_keys=[user_id])
3614 aircraft = db.relationship("Aircraft")
3615 entries = db.relationship(
3616 "LedgerEntry", back_populates="account", cascade="all, delete-orphan"
3617 )
3620class LedgerEntryType:
3621 """Append-only ledger entry types. No update/delete route may ever exist
3622 for LedgerEntry — corrections are posted as reversal entries."""
3624 CHARGE = "charge" # money the account holder owes (positive amount)
3625 PAYMENT = "payment" # money received from the holder (negative amount)
3626 CREDIT = "credit" # reduction of debt, e.g. fuel reimbursement (negative)
3627 ADJUSTMENT = "adjustment" # manual correction, either sign, requires note
3628 OPENING = "opening" # opening balance / co-owner buy-in
3630 ALL: ClassVar[set[str]] = {CHARGE, PAYMENT, CREDIT, ADJUSTMENT, OPENING}
3633class LedgerEntry(db.Model):
3634 """Append-only. Sign convention: positive amount = the holder owes more;
3635 negative = the holder owes less. balance = sum(amount)."""
3637 __tablename__ = "ledger_entries"
3638 __table_args__ = (db.Index("ix_ledger_entries_account_id", "account_id"),)
3640 id = db.Column(db.Integer, primary_key=True)
3641 account_id = db.Column(
3642 db.Integer,
3643 db.ForeignKey("billing_accounts.id", ondelete="CASCADE"),
3644 nullable=False,
3645 )
3646 entry_type = db.Column(db.String(16), nullable=False) # LedgerEntryType
3647 amount = db.Column(db.Numeric(10, 2), nullable=False) # signed
3648 description = db.Column(db.String(255), nullable=False)
3649 entry_date = db.Column(db.Date, nullable=False) # business date, not created_at
3650 # Link back to the domain object that produced the entry, for drill-down:
3651 source_type = db.Column(db.String(32), nullable=True) # e.g. "rental_charge"
3652 source_id = db.Column(db.Integer, nullable=True)
3653 # SET NULL, not RESTRICT: entries are never deleted in the app (append-only,
3654 # no delete route) so this only matters for admin-level DB surgery / test
3655 # cleanup — a self-referential RESTRICT here blocks a bulk DELETE FROM
3656 # ledger_entries entirely, since SQLite can't order same-table FK checks.
3657 reverses_id = db.Column(
3658 db.Integer,
3659 db.ForeignKey("ledger_entries.id", ondelete="SET NULL"),
3660 nullable=True,
3661 )
3662 created_by_id = db.Column(
3663 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
3664 )
3665 created_at = db.Column(
3666 db.DateTime(timezone=True),
3667 nullable=False,
3668 default=lambda: datetime.now(UTC),
3669 )
3671 account = db.relationship("BillingAccount", back_populates="entries")
3672 reverses = db.relationship(
3673 "LedgerEntry", foreign_keys=[reverses_id], remote_side="LedgerEntry.id"
3674 )
3675 created_by = db.relationship("User", foreign_keys=[created_by_id])
3678# ── Phase 39: Shared Ownership ────────────────────────────────────────────────
3681class AircraftOwner(db.Model):
3682 """A co-owner of one aircraft. share_pct values for one aircraft always
3683 sum to exactly 100.00 (enforced in the manage-owners route — rows are
3684 only ever written through that form, which replaces the full owner set
3685 for an aircraft atomically). Share percentage is financial only — each
3686 co-owner always has exactly one vote regardless of share size, so no
3687 voting-weight column exists."""
3689 __tablename__ = "aircraft_owners"
3690 __table_args__ = (
3691 db.UniqueConstraint("aircraft_id", "user_id", name="uq_aircraft_owner"),
3692 db.Index("ix_aircraft_owners_aircraft_id", "aircraft_id"),
3693 )
3695 id = db.Column(db.Integer, primary_key=True)
3696 aircraft_id = db.Column(
3697 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3698 )
3699 user_id = db.Column(
3700 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3701 )
3702 share_pct = db.Column(db.Numeric(5, 2), nullable=False) # 0.01 - 100.00
3703 buy_in_amount = db.Column(db.Numeric(10, 2), nullable=False, default=0)
3704 created_at = db.Column(
3705 db.DateTime(timezone=True),
3706 nullable=False,
3707 default=lambda: datetime.now(UTC),
3708 )
3710 aircraft = db.relationship("Aircraft", back_populates="owners")
3711 user = db.relationship("User")
3714class CoOwnerValuationSnapshot(db.Model):
3715 """Phase 39e: immutable point-in-time capital value per co-owner. No
3716 update or delete route may ever exist for this table — reproducible by
3717 construction, since the ledger it summarises is itself append-only."""
3719 __tablename__ = "co_owner_valuation_snapshots"
3720 __table_args__ = (db.Index("ix_covs_aircraft_id", "aircraft_id"),)
3722 id = db.Column(db.Integer, primary_key=True)
3723 aircraft_id = db.Column(
3724 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3725 )
3726 user_id = db.Column(
3727 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3728 )
3729 valuation_date = db.Column(db.Date, nullable=False)
3730 share_pct = db.Column(db.Numeric(5, 2), nullable=False) # copied at snapshot time
3731 capital_balance = db.Column(
3732 db.Numeric(10, 2), nullable=False
3733 ) # -balance(account, as_of=valuation_date)
3734 note = db.Column(db.String(255), nullable=True)
3735 created_by_id = db.Column(
3736 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
3737 )
3738 created_at = db.Column(
3739 db.DateTime(timezone=True),
3740 nullable=False,
3741 default=lambda: datetime.now(UTC),
3742 )
3744 aircraft = db.relationship("Aircraft")
3745 user = db.relationship("User", foreign_keys=[user_id])
3746 created_by = db.relationship("User", foreign_keys=[created_by_id])