Coverage for app/models.py: 100%
1317 statements
« prev ^ index » next coverage.py v7.15.4, created at 2026-08-31 10:13 +0000
1import enum
2import secrets
3from datetime import UTC, date, datetime
4from typing import ClassVar
6from flask_sqlalchemy import SQLAlchemy # pyright: ignore[reportMissingImports]
7from sqlalchemy import text
9db = SQLAlchemy()
12def reset_schema(db: SQLAlchemy) -> None:
13 """Drop and recreate the demo database's public schema, then rebuild tables.
15 Demo mode never runs Alembic migrations (see docker-init-db.py) and its
16 Postgres volume persists across image updates. db.drop_all() only drops
17 tables declared in the *current* models — a renamed/removed model (e.g.
18 a past FlightEntry -> Flight rename) leaves the old physical table behind,
19 invisible to drop_all() but still enforcing its FK constraints, which can
20 block dropping tables that are still declared. Nuking the whole schema
21 sidesteps that: nothing survives for a stale constraint to reference.
23 On SQLite (dev/test), schemas don't work this way and each test already
24 starts from a fresh in-memory database, so just create_all().
25 """
26 if db.engine.dialect.name == "postgresql":
27 with db.engine.begin() as conn:
28 conn.execute(text("DROP SCHEMA public CASCADE"))
29 conn.execute(text("CREATE SCHEMA public"))
30 db.create_all()
33class Role(str, enum.Enum):
34 ADMIN = "admin"
35 OWNER = "owner"
36 PILOT = "pilot" # Pilot/Renter: log flights + view own; no config/cost edits
37 MAINTENANCE = (
38 "maintenance" # Maintenance: view+update maintenance; no flights/aircraft edits
39 )
40 VIEWER = "viewer" # Read-only across tenant
41 STUDENT = "student" # Student pilot — requires instructor sign-off on solo entries
42 INSTRUCTOR = "instructor" # Flight instructor — can countersign student entries
45class OperatingModel(str, enum.Enum):
46 SOLE_PILOT = "sole_pilot"
47 SOLE_OPERATOR = "sole_operator"
48 SHARED_OWNERSHIP = "shared_ownership"
49 FLIGHT_CLUB = "flight_club"
50 FLIGHT_SCHOOL = "flight_school"
53class PermissionBit:
54 """Bitmask constants for UserAircraftAccess.permissions_mask."""
56 VIEW_AIRCRAFT = 0x01
57 EDIT_AIRCRAFT = 0x02
58 READ_MAINT_FULL = 0x04
59 READ_MAINT_LIMITED = 0x08
60 WRITE_MAINTENANCE = 0x10
61 EDIT_COMPONENTS = 0x20
62 WRITE_LOGBOOK = 0x40
63 RESERVE_AIRCRAFT = 0x80
64 ALL = 0xFF
66 # Default masks per role (used when no explicit per-aircraft row exists)
67 ROLE_DEFAULTS: ClassVar[dict[str, int]] = {
68 "admin": ALL,
69 "owner": ALL,
70 "pilot": VIEW_AIRCRAFT | READ_MAINT_LIMITED | WRITE_LOGBOOK | RESERVE_AIRCRAFT,
71 "student": VIEW_AIRCRAFT | READ_MAINT_LIMITED,
72 "instructor": VIEW_AIRCRAFT
73 | READ_MAINT_FULL
74 | WRITE_LOGBOOK
75 | RESERVE_AIRCRAFT,
76 "maintenance": VIEW_AIRCRAFT
77 | EDIT_AIRCRAFT
78 | READ_MAINT_FULL
79 | WRITE_MAINTENANCE
80 | EDIT_COMPONENTS,
81 "viewer": VIEW_AIRCRAFT | READ_MAINT_FULL,
82 }
85class Tenant(db.Model):
86 __tablename__ = "tenants"
88 id = db.Column(db.Integer, primary_key=True)
89 name = db.Column(db.String(128), nullable=False)
90 slug = db.Column(db.String(64), nullable=True, unique=True)
91 is_active = db.Column(db.Boolean, nullable=False, default=True)
92 require_totp = db.Column(db.Boolean, nullable=False, default=False)
93 created_at = db.Column(
94 db.DateTime(timezone=True),
95 nullable=False,
96 default=lambda: datetime.now(UTC),
97 )
99 users = db.relationship(
100 "TenantUser", back_populates="tenant", cascade="all, delete-orphan"
101 )
102 aircraft = db.relationship(
103 "Aircraft", back_populates="tenant", cascade="all, delete-orphan"
104 )
107class User(db.Model):
108 __tablename__ = "users"
110 id = db.Column(db.Integer, primary_key=True)
111 email = db.Column(db.String(255), unique=True, nullable=False)
112 password_hash = db.Column(db.String(255), nullable=False)
113 totp_secret = db.Column(db.String(64), nullable=True, default=None)
114 is_active = db.Column(db.Boolean, nullable=False, default=True)
115 name = db.Column(db.String(128), nullable=True)
116 language = db.Column(db.String(8), nullable=True, default="en")
117 theme = db.Column(db.String(8), nullable=True, default=None)
118 # Phase 23: capability flags — orthogonal to role; allow cross-role flows
119 is_pilot = db.Column(db.Boolean, nullable=False, default=False)
120 is_maintenance = db.Column(db.Boolean, nullable=False, default=False)
121 view_only = db.Column(db.Boolean, nullable=False, default=False)
122 # Phase 29: instance-level super admin — set on the very first user created
123 is_instance_admin = db.Column(db.Boolean, nullable=False, default=False)
124 created_at = db.Column(
125 db.DateTime(timezone=True),
126 nullable=False,
127 default=lambda: datetime.now(UTC),
128 )
130 tenants = db.relationship(
131 "TenantUser", back_populates="user", cascade="all, delete-orphan"
132 )
134 @property
135 def display_name(self) -> str:
136 return (self.name or "").strip() or self.email.split("@")[0]
139class TenantUser(db.Model):
140 __tablename__ = "tenant_users"
142 user_id = db.Column(
143 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
144 )
145 tenant_id = db.Column(
146 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True
147 )
148 role = db.Column(db.Enum(Role), nullable=False, default=Role.OWNER)
150 user = db.relationship("User", back_populates="tenants")
151 tenant = db.relationship("Tenant", back_populates="users")
154class UserAircraftAccess(db.Model):
155 """Grants a non-owner/admin user explicit access to a specific aircraft."""
157 __tablename__ = "user_aircraft_access"
159 user_id = db.Column(
160 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
161 )
162 aircraft_id = db.Column(
163 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True
164 )
165 # Phase 23: optional override mask; when NULL the role's default mask applies
166 permissions_mask = db.Column(db.Integer, nullable=True)
169class UserAllAircraftAccess(db.Model):
170 """Grants a user access to every aircraft in a tenant (past and future).
172 Admin users bypass access checks entirely and never need this row.
173 For non-admin users, this row grants access to every aircraft in the
174 tenant using the supplied permissions_mask (or the role default when NULL).
175 """
177 __tablename__ = "user_all_aircraft_access"
179 user_id = db.Column(
180 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True
181 )
182 tenant_id = db.Column(
183 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True
184 )
185 permissions_mask = db.Column(db.Integer, nullable=True)
188class UserInvitation(db.Model):
189 """Time-limited invitation for a new user to join a tenant."""
191 __tablename__ = "user_invitations"
193 id = db.Column(db.Integer, primary_key=True)
194 token = db.Column(
195 db.String(64),
196 unique=True,
197 nullable=False,
198 default=lambda: secrets.token_urlsafe(32),
199 )
200 tenant_id = db.Column(
201 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
202 )
203 invited_by_user_id = db.Column(
204 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
205 )
206 email = db.Column(db.String(255), nullable=True)
207 display_name = db.Column(db.String(128), nullable=True)
208 role = db.Column(db.Enum(Role), nullable=False, default=Role.PILOT)
209 aircraft_ids = db.Column(db.JSON, nullable=True)
210 expires_at = db.Column(db.DateTime(timezone=True), nullable=False)
211 accepted_at = db.Column(db.DateTime(timezone=True), nullable=True)
212 created_at = db.Column(
213 db.DateTime(timezone=True),
214 nullable=False,
215 default=lambda: datetime.now(UTC),
216 )
218 tenant = db.relationship("Tenant")
219 invited_by = db.relationship("User", foreign_keys=[invited_by_user_id])
221 @property
222 def is_expired(self) -> bool:
223 exp = self.expires_at
224 # SQLite returns naive datetimes; compare with naive UTC in that case
225 if exp.tzinfo is None:
226 return datetime.now(UTC).replace(tzinfo=None) > exp
227 return datetime.now(UTC) > exp
229 @property
230 def is_accepted(self) -> bool:
231 return self.accepted_at is not None
234# ── Phase 29: Password Reset Token ───────────────────────────────────────────
237class PasswordResetToken(db.Model):
238 """One-time password reset token generated by the instance admin for a tenant owner."""
240 __tablename__ = "password_reset_tokens"
242 id = db.Column(db.Integer, primary_key=True)
243 token = db.Column(
244 db.String(64),
245 unique=True,
246 nullable=False,
247 default=lambda: secrets.token_urlsafe(32),
248 )
249 user_id = db.Column(
250 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
251 )
252 generated_by_user_id = db.Column(
253 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
254 )
255 created_at = db.Column(
256 db.DateTime(timezone=True),
257 nullable=False,
258 default=lambda: datetime.now(UTC),
259 )
260 expires_at = db.Column(db.DateTime(timezone=True), nullable=False)
261 used_at = db.Column(db.DateTime(timezone=True), nullable=True)
263 user = db.relationship("User", foreign_keys=[user_id])
264 generated_by = db.relationship("User", foreign_keys=[generated_by_user_id])
266 @property
267 def is_expired(self) -> bool:
268 exp = self.expires_at
269 if exp.tzinfo is None:
270 return datetime.now(UTC).replace(tzinfo=None) > exp
271 return datetime.now(UTC) > exp
273 @property
274 def is_used(self) -> bool:
275 return self.used_at is not None
278# ── Phase 26: Tenant Profile ─────────────────────────────────────────────────
281class TenantProfile(db.Model):
282 """Instance-level profile collected during the onboarding wizard."""
284 __tablename__ = "tenant_profiles"
286 id = db.Column(db.Integer, primary_key=True)
287 tenant_id = db.Column(
288 db.Integer,
289 db.ForeignKey("tenants.id", ondelete="CASCADE"),
290 nullable=False,
291 unique=True,
292 )
293 operating_model = db.Column(db.Enum(OperatingModel), nullable=True)
294 # 0 → logbook-only (no aircraft UI)
295 # 1 → single-aircraft (hides fleet-level widgets)
296 # N → show "Add aircraft" CTA until N aircraft exist
297 planned_aircraft_count = db.Column(db.Integer, nullable=True)
298 allows_rental = db.Column(db.Boolean, nullable=False, default=False)
299 club_name = db.Column(db.String(128), nullable=True)
300 school_name = db.Column(db.String(128), nullable=True)
301 organisation_name = db.Column(db.String(128), nullable=True)
302 setup_complete = db.Column(db.Boolean, nullable=False, default=False)
303 # Phase 34: optional email subject prefix, e.g. "[MyClub]"
304 email_subject_prefix = db.Column(db.String(64), nullable=True)
305 # Phase 37c: "off" | "warn" | "block" — enforcement level when a renter
306 # (non is_owner user) books an aircraft without a valid RenterAuthorization.
307 rental_authorization_policy = db.Column(
308 db.String(8), nullable=False, default="warn"
309 )
310 # Phase 37f: "warn" | "block" — enforcement level when creating/confirming a
311 # reservation on an aircraft with an open grounding snag. Owners always get
312 # warn-level at most (they may be booking the aircraft for the shop visit).
313 grounded_reservation_policy = db.Column(
314 db.String(8), nullable=False, default="warn"
315 )
316 # Phase 39c: days a co-owner capital balance may stay negative before the
317 # billing dashboard flags it. Only editable/read on a shared_ownership
318 # tenant — see config.update_profile's gating.
319 co_owner_overdue_days = db.Column(db.Integer, nullable=False, default=30)
321 # cascade="all, delete-orphan" on the backref: without it, deleting a
322 # Tenant object through the ORM (e.g. the demo-seed wipe cycle) tries to
323 # NULL this row's NOT NULL tenant_id instead of deleting it, since this
324 # is the only Tenant-side relationship that previously relied solely on
325 # the DB's ondelete=CASCADE rather than mirroring it at the ORM level
326 # (see Tenant.users/Tenant.aircraft above).
327 tenant = db.relationship(
328 "Tenant",
329 backref=db.backref("profile", uselist=False, cascade="all, delete-orphan"),
330 )
333# ── Phase 1: Aircraft & Component Models ──────────────────────────────────────
336# Application-level component type constants.
337# Stored as plain strings in the DB so new types never require a migration.
338class ComponentType:
339 AIRFRAME = "airframe"
340 ENGINE = "engine"
341 PROPELLER = "propeller"
342 AVIONICS = "avionics"
343 OTHER = "other"
345 ALL: ClassVar[set[str]] = {AIRFRAME, ENGINE, PROPELLER, AVIONICS, OTHER}
348class Aircraft(db.Model):
349 __tablename__ = "aircraft"
351 id = db.Column(db.Integer, primary_key=True)
352 tenant_id = db.Column(
353 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
354 )
355 registration = db.Column(db.String(16), nullable=False)
356 make = db.Column(db.String(64), nullable=False)
357 model = db.Column(db.String(64), nullable=False)
358 year = db.Column(db.Integer, nullable=True)
359 has_flight_counter = db.Column(db.Boolean, nullable=False, default=True)
360 flight_counter_offset = db.Column(db.Numeric(3, 1), nullable=False, default=0.3)
361 fuel_flow = db.Column(
362 db.Numeric(6, 2), nullable=True
363 ) # typical fuel consumption in L/h
364 fuel_type = db.Column(
365 db.String(8), nullable=False, default="avgas"
366 ) # "avgas" | "jet_a1"
367 # Oil consumption warning threshold in L/h; null = no warning on the
368 # cost dashboard.
369 oil_warning_lph = db.Column(db.Numeric(4, 2), nullable=True)
370 # Both insurance_expiry and arc_expiry are synced caches, kept in step with
371 # the currently-active (non-superseded) Document of the matching doc_type
372 # by app/documents/routes.py — not directly user-editable.
373 insurance_expiry = db.Column(db.Date, nullable=True)
374 arc_expiry = db.Column(db.Date, nullable=True)
375 # Phase 30: GPS import time rounding preference
376 logbook_time_precision = db.Column(
377 db.String(16), nullable=False, default="tenth_hour"
378 ) # "tenth_hour" | "minute"
379 # Phase 36: optional engine-overhaul reserve accrual rate, surfaced as a
380 # line item on the cost dashboard; null = not configured.
381 reserve_hourly_rate = db.Column(db.Numeric(8, 2), nullable=True)
382 # Phase 39: hourly rate charged to the flying co-owner, and the date from
383 # which co-owner billing considers expenses/flights. Both null until the
384 # owners form is first saved.
385 co_owner_hourly_rate = db.Column(db.Numeric(8, 2), nullable=True)
386 co_owner_billing_start = db.Column(db.Date, nullable=True)
387 # Phase 39g: reserve/overhaul fund contribution — at most one of the two
388 # may be set (mode exclusivity validated on the owners form, not here).
389 reserve_contribution_hourly = db.Column(db.Numeric(8, 2), nullable=True)
390 reserve_contribution_monthly = db.Column(db.Numeric(8, 2), nullable=True)
391 # Archived (sold/retired) aircraft keep their full history but are hidden
392 # from active-fleet views, reservations, and notification passes.
393 archived_at = db.Column(db.DateTime(timezone=True), nullable=True)
394 # Free-text "about this aircraft" blurb shown only on the public showcase
395 # page (ShareToken.access_level == "showcase") — never on the owner-facing
396 # aircraft pages or the operational summary/full share tiers.
397 showcase_blurb = db.Column(db.Text, nullable=True)
398 created_at = db.Column(
399 db.DateTime(timezone=True),
400 nullable=False,
401 default=lambda: datetime.now(UTC),
402 )
404 tenant = db.relationship("Tenant", back_populates="aircraft")
405 components = db.relationship(
406 "Component", back_populates="aircraft", cascade="all, delete-orphan"
407 )
408 flights = db.relationship(
409 "Flight", back_populates="aircraft", cascade="all, delete-orphan"
410 )
411 maintenance_triggers = db.relationship(
412 "MaintenanceTrigger", back_populates="aircraft", cascade="all, delete-orphan"
413 )
414 expenses = db.relationship(
415 "Expense", back_populates="aircraft", cascade="all, delete-orphan"
416 )
417 refuels = db.relationship(
418 "Refuel", back_populates="aircraft", cascade="all, delete-orphan"
419 )
420 fuel_tanks = db.relationship(
421 "AircraftFuelTank",
422 back_populates="aircraft",
423 cascade="all, delete-orphan",
424 order_by="AircraftFuelTank.sort_order",
425 )
426 documents = db.relationship(
427 "Document",
428 back_populates="aircraft",
429 cascade="all, delete-orphan",
430 single_parent=True,
431 foreign_keys="Document.aircraft_id",
432 primaryjoin="Document.aircraft_id == Aircraft.id",
433 )
434 share_tokens = db.relationship(
435 "ShareToken",
436 back_populates="aircraft",
437 cascade="all, delete-orphan",
438 )
439 snags = db.relationship(
440 "Snag",
441 back_populates="aircraft",
442 cascade="all, delete-orphan",
443 )
444 maintenance_downtimes = db.relationship(
445 "MaintenanceDowntime",
446 back_populates="aircraft",
447 cascade="all, delete-orphan",
448 )
449 wb_config = db.relationship(
450 "WeightBalanceConfig",
451 back_populates="aircraft",
452 cascade="all, delete-orphan",
453 uselist=False,
454 )
455 reservations = db.relationship(
456 "Reservation",
457 back_populates="aircraft",
458 cascade="all, delete-orphan",
459 )
460 booking_settings = db.relationship(
461 "AircraftBookingSettings",
462 back_populates="aircraft",
463 cascade="all, delete-orphan",
464 uselist=False,
465 )
466 amp_declaration = db.relationship(
467 "AmpDeclaration",
468 back_populates="aircraft",
469 cascade="all, delete-orphan",
470 uselist=False,
471 )
472 amp_revisions = db.relationship(
473 "AmpRevision",
474 back_populates="aircraft",
475 cascade="all, delete-orphan",
476 order_by="AmpRevision.revision_date.asc().nullslast(), AmpRevision.id.asc()",
477 )
478 photos = db.relationship(
479 "AircraftPhoto",
480 back_populates="aircraft",
481 cascade="all, delete-orphan",
482 order_by="AircraftPhoto.sort_order",
483 )
484 airworthiness_statuses = db.relationship(
485 "AirworthinessDocumentStatus",
486 back_populates="aircraft",
487 cascade="all, delete-orphan",
488 )
489 installed_stcs = db.relationship(
490 "InstalledSTC",
491 back_populates="aircraft",
492 cascade="all, delete-orphan",
493 )
494 owners = db.relationship(
495 "AircraftOwner",
496 back_populates="aircraft",
497 cascade="all, delete-orphan",
498 order_by="AircraftOwner.share_pct.desc()",
499 )
501 @property
502 def cover_photo(self) -> "AircraftPhoto | None":
503 return self.photos[0] if self.photos else None
505 @property
506 def total_engine_hours(self) -> "float | None":
507 """Current engine hours — the highest engine_time_counter_end across all flight entries."""
508 val = db.session.execute(
509 db.select(db.func.max(Flight.engine_time_counter_end)).where(
510 Flight.aircraft_id == self.id
511 )
512 ).scalar()
513 return float(val) if val is not None else None
515 @property
516 def total_flight_hours(self) -> "float | None":
517 """Current flight hours — the highest flight_time_counter_end across all flight entries."""
518 val = db.session.execute(
519 db.select(db.func.max(Flight.flight_time_counter_end)).where(
520 Flight.aircraft_id == self.id
521 )
522 ).scalar()
523 return float(val) if val is not None else None
525 @staticmethod
526 def engine_hours_by_id(aircraft_ids: "list[int]") -> "dict[int, float | None]":
527 """Current engine hours for a whole fleet in one aggregate query.
529 Returns an entry for every requested id (None when the aircraft has no
530 flight entries or no engine counter values)."""
531 totals: dict[int, float | None] = {aid: None for aid in aircraft_ids}
532 if not aircraft_ids:
533 return totals
534 rows = db.session.execute(
535 db.select(
536 Flight.aircraft_id,
537 db.func.max(Flight.engine_time_counter_end),
538 )
539 .where(Flight.aircraft_id.in_(aircraft_ids))
540 .group_by(Flight.aircraft_id)
541 ).all()
542 for aid, max_end in rows:
543 totals[aid] = float(max_end) if max_end is not None else None
544 return totals
546 @staticmethod
547 def flight_hours_by_id(aircraft_ids: "list[int]") -> "dict[int, float | None]":
548 """Current flight hours for a whole fleet in one aggregate query.
550 Mirrors ``engine_hours_by_id``, reading ``flight_time_counter_end``
551 instead — used for maintenance triggers whose ``hours_basis`` is
552 ``HoursBasis.FLIGHT``."""
553 totals: dict[int, float | None] = {aid: None for aid in aircraft_ids}
554 if not aircraft_ids:
555 return totals
556 rows = db.session.execute(
557 db.select(
558 Flight.aircraft_id,
559 db.func.max(Flight.flight_time_counter_end),
560 )
561 .where(Flight.aircraft_id.in_(aircraft_ids))
562 .group_by(Flight.aircraft_id)
563 ).all()
564 for aid, max_end in rows:
565 totals[aid] = float(max_end) if max_end is not None else None
566 return totals
568 @property
569 def total_landings(self) -> "int | None":
570 """Current cumulative landing count — sum of landing_count across all
571 flight entries (unlike the engine/flight hour counters, landings
572 aren't a running total already, so this sums rather than maxes)."""
573 val = db.session.execute(
574 db.select(db.func.sum(Flight.landing_count)).where(
575 Flight.aircraft_id == self.id
576 )
577 ).scalar()
578 return int(val) if val is not None else None
580 @staticmethod
581 def landings_by_id(aircraft_ids: "list[int]") -> "dict[int, int | None]":
582 """Current cumulative landing count for a whole fleet in one aggregate
583 query. Returns an entry for every requested id (None when the
584 aircraft has no flight entries with a recorded landing count)."""
585 totals: dict[int, int | None] = {aid: None for aid in aircraft_ids}
586 if not aircraft_ids:
587 return totals
588 rows = db.session.execute(
589 db.select(
590 Flight.aircraft_id,
591 db.func.sum(Flight.landing_count),
592 )
593 .where(Flight.aircraft_id.in_(aircraft_ids))
594 .group_by(Flight.aircraft_id)
595 ).all()
596 for aid, total in rows:
597 totals[aid] = int(total) if total is not None else None
598 return totals
600 @property
601 def is_archived(self) -> bool:
602 return self.archived_at is not None
604 @property
605 def is_grounded(self) -> bool:
606 """True when any unresolved grounding snag exists, or insurance/the
607 ARC has expired."""
608 from datetime import date as _date
610 today = _date.today()
611 if self.insurance_expiry is not None and self.insurance_expiry < today:
612 return True
613 if self.arc_expiry is not None and self.arc_expiry < today:
614 return True
615 return any(s.is_grounding and s.is_open for s in self.snags)
617 @staticmethod
618 def _expiry_status(expiry: date | None) -> str:
619 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok' for a given date."""
620 if expiry is None:
621 return "ok"
622 delta = (expiry - date.today()).days
623 if delta < 0:
624 return "expired"
625 if delta <= 30:
626 return "expiring_soon"
627 return "ok"
629 @property
630 def insurance_status(self) -> str:
631 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok'."""
632 return self._expiry_status(self.insurance_expiry)
634 @property
635 def arc_status(self) -> str:
636 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok'."""
637 return self._expiry_status(self.arc_expiry)
640class AircraftPhoto(db.Model):
641 __tablename__ = "aircraft_photos"
642 __table_args__ = (db.Index("ix_aircraft_photos_aircraft_id", "aircraft_id"),)
644 id = db.Column(db.Integer, primary_key=True)
645 aircraft_id = db.Column(
646 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
647 )
648 filename = db.Column(db.String(512), nullable=False)
649 original_filename = db.Column(db.String(256), nullable=False)
650 sort_order = db.Column(db.Integer, nullable=False, default=1)
651 uploaded_at = db.Column(
652 db.DateTime(timezone=True),
653 nullable=False,
654 default=lambda: datetime.now(UTC),
655 )
656 uploaded_by_user_id = db.Column(
657 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
658 )
660 aircraft = db.relationship("Aircraft", back_populates="photos")
663class Component(db.Model):
664 """
665 A generic aircraft component (engine, propeller, avionics, …).
667 Common fields live as columns; type-specific attributes go in `extras` (JSON).
668 `removed_at = NULL` means the component is currently installed.
669 `position` disambiguates multiple components of the same type, e.g. "left" / "right"
670 for a twin-engine aircraft.
671 """
673 __tablename__ = "components"
675 id = db.Column(db.Integer, primary_key=True)
676 aircraft_id = db.Column(
677 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
678 )
679 # Plain string, validated at application layer — no DB ENUM so new types
680 # never require a schema migration.
681 type = db.Column(db.String(32), nullable=False)
682 # Optional slot label: "left", "right", "1", "2", "center", …
683 position = db.Column(db.String(32), nullable=True)
685 make = db.Column(db.String(64), nullable=False)
686 model = db.Column(db.String(64), nullable=False)
687 serial_number = db.Column(db.String(64), nullable=True)
688 # Hours on this component when it was installed on this aircraft
689 time_at_install = db.Column(db.Numeric(8, 1), nullable=True)
691 installed_at = db.Column(db.Date, nullable=True)
692 removed_at = db.Column(db.Date, nullable=True) # NULL = currently installed
694 # Life limits: hours between overhauls (TBO) and/or a calendar life limit
695 # (e.g. 12-year rubber hoses). overhauled_at_hours records the component
696 # hours at the last overhaul — it resets the TBO reference point without
697 # touching history; overhauled_on is the matching date for the records.
698 tbo_hours = db.Column(db.Numeric(8, 1), nullable=True)
699 life_limit_date = db.Column(db.Date, nullable=True)
700 overhauled_at_hours = db.Column(db.Numeric(8, 1), nullable=True)
701 overhauled_on = db.Column(db.Date, nullable=True)
703 # Type-specific attributes (blade count, TBO, firmware version, …)
704 extras = db.Column(db.JSON, nullable=True)
706 created_at = db.Column(
707 db.DateTime(timezone=True),
708 nullable=False,
709 default=lambda: datetime.now(UTC),
710 )
712 aircraft = db.relationship("Aircraft", back_populates="components")
713 documents = db.relationship(
714 "Document",
715 back_populates="component",
716 cascade="all, delete-orphan",
717 )
718 easa_source_nodes = db.relationship(
719 "EASASourceNode",
720 back_populates="component",
721 cascade="all, delete-orphan",
722 )
723 airworthiness_documents = db.relationship(
724 "AirworthinessDocument",
725 back_populates="component",
726 cascade="all, delete-orphan",
727 foreign_keys="AirworthinessDocument.component_id",
728 )
729 # Phase 40: no cascade — deleting a component unscopes (SET NULL) rather
730 # than deletes its maintenance triggers, per MaintenanceTrigger.component_id.
731 maintenance_triggers = db.relationship(
732 "MaintenanceTrigger", back_populates="component"
733 )
736# ── Phase 3: Flight Logging ───────────────────────────────────────────────────
739class CrewRole:
740 PIC = "PIC"
741 IP = "IP"
742 SP = "SP"
743 COPILOT = "COPILOT"
744 STUDENT = "STUDENT"
745 ALL: ClassVar[list[str]] = [PIC, IP, SP, COPILOT, STUDENT]
746 LABELS: ClassVar[dict[str, str]] = {
747 PIC: "PIC",
748 IP: "Instructor",
749 SP: "Safety Pilot",
750 COPILOT: "Co-Pilot",
751 STUDENT: "Student",
752 }
755# ── GPS Track (standalone, linkable from Flight) ──────────────────────────────
758class GpsTrack(db.Model):
759 __tablename__ = "gps_tracks"
761 id = db.Column(db.Integer, primary_key=True)
762 source_filename = db.Column(db.String(256), nullable=True)
763 device_id = db.Column(db.String(64), nullable=True, index=True)
764 block_off_utc = db.Column(db.DateTime(timezone=True), nullable=True)
765 block_on_utc = db.Column(db.DateTime(timezone=True), nullable=True)
766 departure_icao = db.Column(db.String(4), nullable=True)
767 arrival_icao = db.Column(db.String(4), nullable=True)
768 geojson = db.Column(db.JSON, nullable=True)
769 created_at = db.Column(
770 db.DateTime(timezone=True),
771 nullable=False,
772 default=lambda: datetime.now(UTC),
773 )
774 # Render cache for the default (landscape, low-res) single-flight PNG/GIF —
775 # geojson never changes once saved, so no invalidation is ever needed.
776 cached_png = db.Column(db.LargeBinary, nullable=True)
777 cached_gif = db.Column(db.LargeBinary, nullable=True)
780class LogbookEntryType:
781 FLIGHT = "flight"
782 FSTD = "fstd" # synthetic training device / simulator session
783 ALL: ClassVar[set[str]] = {FLIGHT, FSTD}
786class FstdType:
787 FFS = "FFS"
788 FTD = "FTD"
789 FNPT = "FNPT"
790 BITD = "BITD"
791 AATD = "AATD"
792 ALL: ClassVar[list[str]] = [FFS, FTD, FNPT, BITD, AATD]
795class Flight(db.Model):
796 """Unified logbook record — one row per real-world flight (or FSTD
797 session), covering both the airframe-log ("aircraft side") and the
798 EASA FCL.050 pilot-log ("pilot side") views of it, replacing the old
799 FlightEntry + FlightCrew + PilotLogbookEntry three-table split.
801 Covers all three real-world cases: (1) a managed aircraft flown by a
802 pilot with no OpenHangar account (aircraft_id set, pic_user_id NULL,
803 pic_name free text); (2) a pilot with an account flying an aircraft
804 not managed here (aircraft_id NULL, other_aircraft_* free text,
805 pic_user_id set); (3) both managed (aircraft_id and pic_user_id both
806 set). At most one additional occupant is tracked via the
807 second_crew_* identity fields (mirrors the pre-refactor FlightCrew's
808 2-slot cap — every crew-creating code path already stopped at 2).
810 EASA figures (night/instrument/cross-country time, landings,
811 single_pilot_se/me, multi_pilot, the function_* breakdown, entry_type/
812 fstd_*) live once on the row, not duplicated per pilot slot — they
813 describe the flight/session itself (or, for function_*, are already
814 unambiguously tied to a slot by column name), not a specific occupant.
816 Two independent engine/flight duration tracks live side by side:
817 departure_time/arrival_time + engine_time_counter_*/engine_time (engine
818 start/end, pilot-log-facing) vs. takeoff_time/landing_time +
819 flight_time_counter_*/flight_time (flight start/end, airframe-log-
820 facing). Neither pair is derived from the other.
821 Letting PIC and a second crew member log different figures for the
822 same real flight was a bug in the old two-table design, not a
823 feature — this schema makes that impossible by construction.
824 """
826 __tablename__ = "flights"
828 id = db.Column(db.Integer, primary_key=True)
829 aircraft_id = db.Column(
830 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
831 )
832 # Free-text aircraft descriptor for flights on an aircraft not managed
833 # in this instance (aircraft_id NULL) — mirrors PilotLogbookEntry's old
834 # aircraft_type/_icao/registration. When aircraft_id is set, the type/
835 # registration are derived live from the Aircraft join instead.
836 other_aircraft_type = db.Column(db.String(64), nullable=True)
837 other_aircraft_type_icao = db.Column(db.String(16), nullable=True)
838 other_aircraft_registration = db.Column(db.String(16), nullable=True)
840 date = db.Column(db.Date, nullable=False)
841 # Widened from the old FlightEntry's strict 4-char ICAO-only String —
842 # a standalone entry (rental/FSTD) may log a free-text place instead.
843 departure_icao = db.Column(db.String(64), nullable=True)
844 arrival_icao = db.Column(db.String(64), nullable=True)
845 # Engine start/end (block-off/block-on — engine assumed on at block-off).
846 # Pairs with engine_time_counter_*/engine_time below and feeds the pilot
847 # logbook. Distinct from takeoff_time/landing_time (the flight/airborne
848 # segment, pairs with flight_time_counter_*/flight_time, feeds the
849 # airframe logbook) — the two pairs are never defaulted from each other.
850 departure_time = db.Column(db.Time, nullable=True)
851 arrival_time = db.Column(db.Time, nullable=True)
852 # Flight start/end (wheels-up/wheels-down) — see comment above.
853 takeoff_time = db.Column(db.Time, nullable=True)
854 landing_time = db.Column(db.Time, nullable=True)
855 flight_time = db.Column(db.Numeric(4, 1), nullable=True)
856 nature_of_flight = db.Column(db.String(100), nullable=True)
857 passenger_count = db.Column(db.Integer, nullable=True)
858 landing_count = db.Column(db.Integer, nullable=True)
859 flight_time_counter_start = db.Column(db.Numeric(8, 1), nullable=True)
860 flight_time_counter_end = db.Column(db.Numeric(8, 1), nullable=True)
861 notes = db.Column(db.Text, nullable=True)
862 engine_time_counter_start = db.Column(db.Numeric(8, 1), nullable=True)
863 engine_time_counter_end = db.Column(db.Numeric(8, 1), nullable=True)
864 # Raw engine-hours duration for this flight (engine counter end minus
865 # start, no offset) — distinct from flight_time, which approximates the
866 # airborne segment. Engine/propeller TBO tracking sums this, not
867 # flight_time (see services/component_limits.py).
868 engine_time = db.Column(db.Numeric(4, 1), nullable=True)
869 flight_counter_photo = db.Column(db.String(255), nullable=True)
870 engine_counter_photo = db.Column(db.String(255), nullable=True)
871 # Refueling before and after are independent events — a flight can have
872 # neither, either, or both (e.g. topped off before departure, then
873 # topped off again after landing).
874 fuel_added_before_qty = db.Column(db.Numeric(8, 2), nullable=True)
875 fuel_added_before_unit = db.Column(db.String(8), nullable=True)
876 fuel_added_after_qty = db.Column(db.Numeric(8, 2), nullable=True)
877 fuel_added_after_unit = db.Column(db.String(8), nullable=True)
878 fuel_remaining_qty = db.Column(db.Numeric(8, 2), nullable=True)
879 fuel_photo = db.Column(db.String(255), nullable=True)
880 # Same independence as the fuel_added_before/after pair above — oil can
881 # be topped off before departure, after landing, or both.
882 oil_added_before_l = db.Column(db.Numeric(4, 2), nullable=True)
883 oil_added_after_l = db.Column(db.Numeric(4, 2), nullable=True)
884 created_at = db.Column(
885 db.DateTime(timezone=True),
886 nullable=False,
887 default=lambda: datetime.now(UTC),
888 )
890 # ── EASA FCL.050 pilot-log figures (shared, once per row) ─────────────────
891 night_time = db.Column(db.Numeric(4, 1), nullable=True)
892 instrument_time = db.Column(db.Numeric(4, 1), nullable=True)
893 cross_country = db.Column(db.Numeric(4, 1), nullable=True)
894 landings_day = db.Column(db.Integer, nullable=True)
895 landings_night = db.Column(db.Integer, nullable=True)
896 single_pilot_se = db.Column(db.Numeric(4, 1), nullable=True)
897 single_pilot_me = db.Column(db.Numeric(4, 1), nullable=True)
898 multi_pilot = db.Column(db.Numeric(4, 1), nullable=True)
899 # Each already unambiguously tied to a slot by name: function_pic is
900 # always the pic_* slot's hours; whichever of the other three is
901 # non-null belongs to the second_crew_* slot, per second_crew_role.
902 function_pic = db.Column(db.Numeric(4, 1), nullable=True)
903 function_copilot = db.Column(db.Numeric(4, 1), nullable=True)
904 function_dual = db.Column(db.Numeric(4, 1), nullable=True)
905 function_instructor = db.Column(db.Numeric(4, 1), nullable=True)
907 # EASA AMC1 FCL.050 column 10 — FSTD/simulator sessions (LogbookEntryType constant)
908 entry_type = db.Column(
909 db.String(16),
910 nullable=False,
911 default=LogbookEntryType.FLIGHT,
912 server_default=LogbookEntryType.FLIGHT,
913 )
914 fstd_type = db.Column(db.String(16), nullable=True) # FstdType constant
915 fstd_duration = db.Column(db.Numeric(4, 1), nullable=True)
917 # ── Crew identity slots ────────────────────────────────────────────────────
918 pic_user_id = db.Column(
919 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
920 )
921 pic_name = db.Column(db.String(128), nullable=True)
922 second_crew_user_id = db.Column(
923 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
924 )
925 second_crew_name = db.Column(db.String(128), nullable=True)
926 second_crew_role = db.Column(db.String(16), nullable=True) # CrewRole constant
928 # Phase 30: GPS import
929 source = db.Column(db.String(32), nullable=True)
930 # "gps_import" | "import" | "logbook_import" | None — see docs/backlog.md
931 # "Step 2" for how this is used to flag rows needing the other side's data.
932 gps_import_batch_id = db.Column(
933 db.Integer,
934 db.ForeignKey("aircraft_gps_import_batches.id", ondelete="SET NULL"),
935 nullable=True,
936 )
937 # Bulk airframe logbook import (CSV/Excel)
938 airframe_import_batch_id = db.Column(
939 db.Integer,
940 db.ForeignKey("airframe_import_batches.id", ondelete="SET NULL"),
941 nullable=True,
942 )
943 airframe_import_batch = db.relationship(
944 "AirframeImportBatch", foreign_keys=[airframe_import_batch_id]
945 )
946 # Pilot logbook import (CSV/Excel) — independent of airframe_import_batch_id
947 # above; a row can carry both once it's been touched from both sides.
948 import_batch_id = db.Column(
949 db.Integer,
950 db.ForeignKey("logbook_import_batches.id", ondelete="SET NULL"),
951 nullable=True,
952 )
953 block_off_utc = db.Column(db.DateTime(timezone=True), nullable=True)
954 block_on_utc = db.Column(db.DateTime(timezone=True), nullable=True)
955 gps_track_id = db.Column(
956 db.Integer,
957 db.ForeignKey("gps_tracks.id", ondelete="SET NULL"),
958 nullable=True,
959 )
960 # Phase 37d: auto-linked when this flight falls inside a confirmed
961 # reservation's dispatch window for the same pilot.
962 reservation_id = db.Column(
963 db.Integer,
964 db.ForeignKey("reservations.id", ondelete="SET NULL"),
965 nullable=True,
966 )
968 aircraft = db.relationship("Aircraft", back_populates="flights")
969 reservation = db.relationship("Reservation", back_populates="flights")
970 gps_track = db.relationship("GpsTrack", foreign_keys=[gps_track_id])
971 gps_import_batch = db.relationship(
972 "AircraftGpsImportBatch", foreign_keys=[gps_import_batch_id]
973 )
974 import_batch = db.relationship("LogbookImportBatch", foreign_keys=[import_batch_id])
975 pic_user = db.relationship("User", foreign_keys=[pic_user_id])
976 second_crew_user = db.relationship("User", foreign_keys=[second_crew_user_id])
977 expenses = db.relationship("Expense", back_populates="flight_entry")
978 documents = db.relationship(
979 "Document",
980 back_populates="flight_entry",
981 cascade="all, delete-orphan",
982 )
984 __table_args__ = (
985 # Matches the standard airframe-log ordering (date DESC, id DESC per aircraft).
986 db.Index(
987 "ix_flights_aircraft_id_date_id",
988 aircraft_id,
989 date.desc(),
990 id.desc(),
991 ),
992 # Match the old per-pilot logbook ordering (date DESC, id DESC per
993 # pilot) for each identity slot — "my logbook" is
994 # WHERE pic_user_id = :uid OR second_crew_user_id = :uid.
995 db.Index(
996 "ix_flights_pic_user_id_date_id",
997 pic_user_id,
998 date.desc(),
999 id.desc(),
1000 ),
1001 db.Index(
1002 "ix_flights_second_crew_user_id_date_id",
1003 second_crew_user_id,
1004 date.desc(),
1005 id.desc(),
1006 ),
1007 )
1009 @property
1010 def total_flight_time(self):
1011 parts = [self.single_pilot_se, self.single_pilot_me, self.multi_pilot]
1012 vals = [float(p) for p in parts if p is not None]
1013 return round(sum(vals), 1) if vals else None
1015 # Aircraft descriptor, resolved live from the managed Aircraft when
1016 # aircraft_id is set (Aircraft.make/model rarely changes) rather than
1017 # denormalized onto this row — falls back to the free-text
1018 # other_aircraft_* columns for a standalone (aircraft_id NULL) row.
1019 @property
1020 def display_aircraft_type(self) -> str | None:
1021 if self.aircraft_id and self.aircraft:
1022 return f"{self.aircraft.make} {self.aircraft.model}".strip()
1023 return self.other_aircraft_type
1025 @property
1026 def display_aircraft_type_icao(self) -> str | None:
1027 if self.aircraft_id and self.aircraft:
1028 return getattr(self.aircraft, "aircraft_type_icao", None)
1029 return self.other_aircraft_type_icao
1031 @property
1032 def display_registration(self) -> str | None:
1033 if self.aircraft_id and self.aircraft:
1034 return self.aircraft.registration
1035 return self.other_aircraft_registration
1038# ── Pilot Profile ──────────────────────────────────────────────────────────────
1041class PilotProfile(db.Model):
1042 __tablename__ = "pilot_profiles"
1044 id = db.Column(db.Integer, primary_key=True)
1045 user_id = db.Column(
1046 db.Integer,
1047 db.ForeignKey("users.id", ondelete="CASCADE"),
1048 nullable=False,
1049 unique=True,
1050 )
1051 license_number = db.Column(db.String(64), nullable=True)
1052 medical_expiry = db.Column(db.Date, nullable=True)
1053 sep_expiry = db.Column(db.Date, nullable=True)
1054 first_solo_date = db.Column(db.Date, nullable=True)
1055 ppl_issue_date = db.Column(db.Date, nullable=True)
1057 user = db.relationship("User")
1060# ── Personal minimums ──────────────────────────────────────────────────────────
1063class PersonalMinimumsStatus:
1064 DRAFT = "draft"
1065 ACTIVE = "active"
1066 SUPERSEDED = "superseded"
1067 ALL: ClassVar[set[str]] = {DRAFT, ACTIVE, SUPERSEDED}
1070class PersonalMinimumsTag:
1071 """Small semantic vocabulary — binding an item to one of these lets the
1072 app compute a recency nudge for it (the two MAX_DAYS_* tags only; the
1073 other two are display-only in v1, see docs/backlog.md). Stored as a
1074 plain string so new tags never need a migration."""
1076 MAX_DAYS_SINCE_LAST_FLIGHT = "max_days_since_last_flight"
1077 MAX_DAYS_SINCE_INSTRUCTOR_FLIGHT = "max_days_since_instructor_flight"
1078 MANOEUVRES_PRACTICE_INTERVAL_MONTHS = "manoeuvres_practice_interval_months"
1079 MIN_FUEL_RESERVE_MINUTES = "min_fuel_reserve_minutes"
1081 ALL: ClassVar[set[str]] = {
1082 MAX_DAYS_SINCE_LAST_FLIGHT,
1083 MAX_DAYS_SINCE_INSTRUCTOR_FLIGHT,
1084 MANOEUVRES_PRACTICE_INTERVAL_MONTHS,
1085 MIN_FUEL_RESERVE_MINUTES,
1086 }
1087 # Tags with an automatic recency check in v1 (see backlog decisions log).
1088 HAS_RECENCY_CHECK: ClassVar[set[str]] = {
1089 MAX_DAYS_SINCE_LAST_FLIGHT,
1090 MAX_DAYS_SINCE_INSTRUCTOR_FLIGHT,
1091 }
1094class PersonalMinimumsRevision(db.Model):
1095 """A pilot's personal minimums document, versioned: draft (editable) ->
1096 active (published, immutable) -> superseded (immutable, kept for
1097 history). At most one draft and one active per pilot at a time
1098 (enforced in route logic, not a DB constraint — see pilots/routes.py)."""
1100 __tablename__ = "personal_minimums_revisions"
1101 __table_args__ = (
1102 db.UniqueConstraint(
1103 "user_id", "revision_number", name="uq_personal_minimums_revision"
1104 ),
1105 )
1107 id = db.Column(db.Integer, primary_key=True)
1108 user_id = db.Column(
1109 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1110 )
1111 revision_number = db.Column(db.Integer, nullable=False)
1112 status = db.Column(
1113 db.String(16), nullable=False, default=PersonalMinimumsStatus.DRAFT
1114 )
1115 published_on = db.Column(db.Date, nullable=True)
1116 experience_hours = db.Column(db.Numeric(6, 1), nullable=True)
1117 experience_note = db.Column(db.String(128), nullable=True)
1118 created_at = db.Column(
1119 db.DateTime(timezone=True),
1120 nullable=False,
1121 default=lambda: datetime.now(UTC),
1122 )
1123 updated_at = db.Column(
1124 db.DateTime(timezone=True),
1125 nullable=False,
1126 default=lambda: datetime.now(UTC),
1127 onupdate=lambda: datetime.now(UTC),
1128 )
1130 user = db.relationship("User")
1131 sections = db.relationship(
1132 "PersonalMinimumsSection",
1133 back_populates="revision",
1134 cascade="all, delete-orphan",
1135 order_by="PersonalMinimumsSection.sort_order",
1136 )
1139class PersonalMinimumsSection(db.Model):
1140 __tablename__ = "personal_minimums_sections"
1142 id = db.Column(db.Integer, primary_key=True)
1143 revision_id = db.Column(
1144 db.Integer,
1145 db.ForeignKey("personal_minimums_revisions.id", ondelete="CASCADE"),
1146 nullable=False,
1147 )
1148 title = db.Column(db.String(128), nullable=False)
1149 sort_order = db.Column(db.Integer, nullable=False, default=0)
1151 revision = db.relationship("PersonalMinimumsRevision", back_populates="sections")
1152 items = db.relationship(
1153 "PersonalMinimumsItem",
1154 back_populates="section",
1155 cascade="all, delete-orphan",
1156 order_by="PersonalMinimumsItem.sort_order",
1157 )
1160class PersonalMinimumsItem(db.Model):
1161 __tablename__ = "personal_minimums_items"
1163 id = db.Column(db.Integer, primary_key=True)
1164 section_id = db.Column(
1165 db.Integer,
1166 db.ForeignKey("personal_minimums_sections.id", ondelete="CASCADE"),
1167 nullable=False,
1168 )
1169 label = db.Column(db.String(128), nullable=False)
1170 value = db.Column(db.Text, nullable=True)
1171 semantic_tag = db.Column(
1172 db.String(64), nullable=True
1173 ) # PersonalMinimumsTag constant
1174 numeric_value = db.Column(db.Numeric(8, 2), nullable=True)
1175 sort_order = db.Column(db.Integer, nullable=False, default=0)
1177 section = db.relationship("PersonalMinimumsSection", back_populates="items")
1180# ── Phase 28: Pilot Logbook Import ───────────────────────────────────────────
1183class LogbookImportMapping(db.Model):
1184 __tablename__ = "logbook_import_mappings"
1186 id = db.Column(db.Integer, primary_key=True)
1187 pilot_user_id = db.Column(
1188 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1189 )
1190 source_fingerprint = db.Column(db.String(64), nullable=False, index=True)
1191 # JSON: {norm_col_key: target_field_or_"ignore"}
1192 column_mapping = db.Column(db.Text, nullable=False)
1193 # JSON list of norm_col_keys — stored for fuzzy matching future uploads
1194 source_columns = db.Column(db.Text, nullable=False)
1195 created_at = db.Column(db.DateTime(timezone=True), nullable=False)
1197 pilot = db.relationship("User")
1200class LogbookImportBatch(db.Model):
1201 __tablename__ = "logbook_import_batches"
1203 id = db.Column(db.Integer, primary_key=True)
1204 pilot_user_id = db.Column(
1205 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1206 )
1207 mapping_id = db.Column(
1208 db.Integer,
1209 db.ForeignKey("logbook_import_mappings.id", ondelete="SET NULL"),
1210 nullable=True,
1211 )
1212 source_filename = db.Column(db.String(256), nullable=False)
1213 imported_at = db.Column(db.DateTime(timezone=True), nullable=False)
1214 row_count = db.Column(db.Integer, nullable=False, default=0)
1215 subtotal_count = db.Column(db.Integer, nullable=False, default=0)
1216 skipped_count = db.Column(db.Integer, nullable=False, default=0)
1217 has_opening_balance = db.Column(db.Boolean, nullable=False, default=False)
1219 pilot = db.relationship("User")
1220 mapping = db.relationship("LogbookImportMapping")
1221 entries = db.relationship(
1222 "Flight",
1223 foreign_keys="Flight.import_batch_id",
1224 lazy="dynamic",
1225 overlaps="import_batch",
1226 )
1229class AirframeImportMapping(db.Model):
1230 """Fingerprint-keyed column mapping memory for airframe logbook imports
1231 (the aircraft-record twin of LogbookImportMapping)."""
1233 __tablename__ = "airframe_import_mappings"
1235 id = db.Column(db.Integer, primary_key=True)
1236 tenant_id = db.Column(
1237 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
1238 )
1239 source_fingerprint = db.Column(db.String(64), nullable=False, index=True)
1240 # JSON: {norm_col_key: target_field_or_"ignore"}
1241 column_mapping = db.Column(db.Text, nullable=False)
1242 # JSON list of norm_col_keys
1243 source_columns = db.Column(db.Text, nullable=False)
1244 created_at = db.Column(db.DateTime(timezone=True), nullable=False)
1247class AirframeImportBatch(db.Model):
1248 """One executed airframe logbook import for one aircraft, undoable as a unit."""
1250 __tablename__ = "airframe_import_batches"
1252 id = db.Column(db.Integer, primary_key=True)
1253 aircraft_id = db.Column(
1254 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1255 )
1256 mapping_id = db.Column(
1257 db.Integer,
1258 db.ForeignKey("airframe_import_mappings.id", ondelete="SET NULL"),
1259 nullable=True,
1260 )
1261 source_filename = db.Column(db.String(256), nullable=False)
1262 imported_at = db.Column(db.DateTime(timezone=True), nullable=False)
1263 row_count = db.Column(db.Integer, nullable=False, default=0)
1264 subtotal_count = db.Column(db.Integer, nullable=False, default=0)
1265 skipped_count = db.Column(db.Integer, nullable=False, default=0)
1266 warning_count = db.Column(db.Integer, nullable=False, default=0)
1267 has_opening_counters = db.Column(db.Boolean, nullable=False, default=False)
1268 # One-time digitization of pre-existing paper records: rows may contain
1269 # imprecision (OCR misreads, decimal/hours.minutes ambiguity) that must
1270 # still be imported as-is, and must not later block edits to that
1271 # imported flight. Regular/incremental catch-up imports leave this False
1272 # so they stay subject to the normal strict validation on edit.
1273 is_historical = db.Column(db.Boolean, nullable=False, default=False)
1275 aircraft = db.relationship("Aircraft")
1278# ── Phase 30: Aircraft GPS Log Import ────────────────────────────────────────
1281class AircraftGpsImportBatch(db.Model):
1282 """Metadata for one GPS-import session (1+ files → 1+ Flight records)."""
1284 __tablename__ = "aircraft_gps_import_batches"
1286 id = db.Column(db.Integer, primary_key=True)
1287 aircraft_id = db.Column(
1288 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1289 )
1290 pilot_user_id = db.Column(
1291 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
1292 )
1293 # JSON list of original filenames, e.g. ["log_260518_EBNM.csv", "track.gpx"]
1294 source_filenames = db.Column(db.JSON, nullable=False, default=list)
1295 imported_at = db.Column(
1296 db.DateTime(timezone=True),
1297 nullable=False,
1298 default=lambda: datetime.now(UTC),
1299 )
1300 format_detected = db.Column(
1301 db.String(16), nullable=False
1302 ) # "gpx"|"kml"|"garmin_csv"|"mixed"
1303 segments_found = db.Column(db.Integer, nullable=False, default=0)
1304 segments_imported = db.Column(db.Integer, nullable=False, default=0)
1305 # IDs of pre-existing Flight rows that received a GPS track (not created).
1306 linked_flight_entry_ids = db.Column(db.JSON, nullable=False, default=list)
1307 # Pilot role selected during import: 'pic' | 'dual' | 'none'
1308 pilot_role = db.Column(db.String(8), nullable=True)
1309 # Set when the import is for an aircraft not in this instance (Phase 31).
1310 other_aircraft_make_model = db.Column(db.String(128), nullable=True)
1311 other_aircraft_registration = db.Column(db.String(16), nullable=True)
1313 aircraft = db.relationship(
1314 "Aircraft",
1315 backref=db.backref("gps_import_batches", cascade="all, delete-orphan"),
1316 )
1317 pilot = db.relationship("User", foreign_keys=[pilot_user_id])
1318 # Merges the old FlightEntry.gps_import_batch_id and
1319 # PilotLogbookEntry.gps_batch_id (two separate FKs to this same table)
1320 # into one — a unified Flight row only ever needs one link back here.
1321 flight_entries = db.relationship(
1322 "Flight",
1323 foreign_keys="Flight.gps_import_batch_id",
1324 lazy="dynamic",
1325 overlaps="gps_import_batch",
1326 )
1329# ── Phase 4: Maintenance Tracking ────────────────────────────────────────────
1332class TriggerType:
1333 CALENDAR = "calendar" # due on a specific date
1334 HOURS = "hours" # due at a specific hobbs reading
1335 LANDINGS = "landings" # due at a specific cumulative landing count
1336 ALL: ClassVar[set[str]] = {CALENDAR, HOURS, LANDINGS}
1339class HoursBasis:
1340 """Which running total an HOURS-type MaintenanceTrigger's
1341 due_engine_hours/interval_hours are measured against — engine/propeller
1342 TBO-style items are naturally engine-hours based, while airframe life
1343 limits and other flight-hours-quoted items need the aircraft's flight
1344 (airborne) hours instead."""
1346 ENGINE = "engine"
1347 FLIGHT = "flight"
1348 ALL: ClassVar[set[str]] = {ENGINE, FLIGHT}
1349 LABELS: ClassVar[dict[str, str]] = {ENGINE: "Engine hours", FLIGHT: "Flight hours"}
1352class AmpCategory:
1353 """The 9 additional-maintenance-requirement categories from EASA Form
1354 AMP block 4 / Appendix B (AMC2 ML.A.302), used verbatim as
1355 ``MaintenanceTrigger.category`` values so that block 4's Yes/No table
1356 and Appendix B can be computed from the trigger set at export time
1357 rather than stored separately (Phase 40). ``ALL`` preserves the
1358 official form's own row order for rendering."""
1360 EQUIPMENT_AND_MODIFICATIONS = (
1361 "Maintenance due to specific equipment and modifications"
1362 )
1363 REPAIRS = "Maintenance due to repairs"
1364 LIFE_LIMITED_COMPONENTS = "Maintenance due to life-limited components"
1365 MANDATORY_CONTINUING_AIRWORTHINESS = (
1366 "Maintenance due to mandatory continuing airworthiness information "
1367 "(ALIs, CMRs, TCDS)"
1368 )
1369 TBO_RECOMMENDATIONS = "Maintenance recommendations (TBO via SB/SL, non-mandatory)"
1370 REPETITIVE_ADS = "Maintenance due to repetitive ADs"
1371 OPERATIONAL_AIRSPACE_DIRECTIVES = (
1372 "Maintenance due to specific operational/airspace directives/requirements"
1373 )
1374 TYPE_OF_OPERATION = "Maintenance due to type of operation or operational approvals"
1375 OTHER = "Other"
1377 ALL: ClassVar[list[str]] = [
1378 EQUIPMENT_AND_MODIFICATIONS,
1379 REPAIRS,
1380 LIFE_LIMITED_COMPONENTS,
1381 MANDATORY_CONTINUING_AIRWORTHINESS,
1382 TBO_RECOMMENDATIONS,
1383 REPETITIVE_ADS,
1384 OPERATIONAL_AIRSPACE_DIRECTIVES,
1385 TYPE_OF_OPERATION,
1386 OTHER,
1387 ]
1390class MaintenanceTrigger(db.Model):
1391 __tablename__ = "maintenance_triggers"
1393 id = db.Column(db.Integer, primary_key=True)
1394 aircraft_id = db.Column(
1395 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1396 )
1397 # Phase 40: optional component scoping (engine, propeller, …). NULL means
1398 # airframe-general. SET NULL on component deletion — removing a component
1399 # shouldn't delete its maintenance history, just unscope the trigger.
1400 component_id = db.Column(
1401 db.Integer, db.ForeignKey("components.id", ondelete="SET NULL"), nullable=True
1402 )
1403 name = db.Column(db.String(128), nullable=False)
1404 trigger_type = db.Column(db.String(16), nullable=False) # TriggerType constant
1406 # Calendar trigger fields
1407 due_date = db.Column(db.Date, nullable=True)
1408 interval_days = db.Column(db.Integer, nullable=True) # advance due_date on service
1409 # Days before due_date to flag 'due_soon'. NULL falls back to a flat
1410 # 30-day default (status()) — lets an admin override per trigger without
1411 # requiring every existing row to be re-saved.
1412 warn_days = db.Column(db.Integer, nullable=True)
1414 # Hours trigger fields — despite the column name, due_engine_hours is
1415 # measured against engine or flight hours depending on hours_basis.
1416 due_engine_hours = db.Column(db.Numeric(8, 1), nullable=True)
1417 interval_hours = db.Column(
1418 db.Numeric(8, 1), nullable=True
1419 ) # advance due_engine_hours on service
1420 hours_basis = db.Column(
1421 db.String(16),
1422 nullable=False,
1423 default=HoursBasis.ENGINE,
1424 server_default=HoursBasis.ENGINE,
1425 )
1426 # Hours before due_engine_hours to flag 'due_soon'. NULL falls back to
1427 # max(interval_hours * 10%, 5.0), or a flat 10.0 with no interval set.
1428 warn_hours = db.Column(db.Numeric(6, 1), nullable=True)
1430 # Landings trigger fields
1431 due_landings = db.Column(db.Integer, nullable=True)
1432 interval_landings = db.Column(
1433 db.Integer, nullable=True
1434 ) # advance due_landings on service
1435 # Landings before due_landings to flag 'due_soon'. NULL falls back to
1436 # max(interval_landings * 10%, 5), or a flat 10 with no interval set.
1437 warn_landings = db.Column(db.Integer, nullable=True)
1439 notes = db.Column(db.Text, nullable=True)
1441 # Phase 40: AMP import/export provenance and classification fields — all
1442 # nullable free text/flags, orthogonal to the calendar/hours/landings due
1443 # fields above. See docs/maintenance_import.md once written.
1444 category = db.Column(db.String(128), nullable=True) # AmpCategory value, or NULL
1445 is_alternative_to_ica = db.Column(
1446 db.Boolean, nullable=False, default=False, server_default=db.false()
1447 )
1448 alternative_task_notes = db.Column(db.Text, nullable=True)
1449 reference = db.Column(db.String(255), nullable=True) # AD/SB/manual doc reference
1450 action = db.Column(db.String(32), nullable=True) # e.g. INSPECTION/REPLACE/TBO/SLL
1451 part_number = db.Column(db.String(64), nullable=True)
1452 serial_number = db.Column(db.String(64), nullable=True)
1453 # Set on import for rows with an unresolved/unparseable interval, so they
1454 # read as "not yet scheduled" rather than silently evaluating as 'ok'.
1455 needs_review = db.Column(
1456 db.Boolean, nullable=False, default=False, server_default=db.false()
1457 )
1458 import_batch_id = db.Column(
1459 db.Integer,
1460 db.ForeignKey("maintenance_import_batches.id", ondelete="SET NULL"),
1461 nullable=True,
1462 )
1464 created_at = db.Column(
1465 db.DateTime(timezone=True),
1466 nullable=False,
1467 default=lambda: datetime.now(UTC),
1468 )
1470 aircraft = db.relationship("Aircraft", back_populates="maintenance_triggers")
1471 component = db.relationship("Component", back_populates="maintenance_triggers")
1472 import_batch = db.relationship("MaintenanceImportBatch", back_populates="triggers")
1473 records = db.relationship(
1474 "MaintenanceRecord",
1475 back_populates="trigger",
1476 cascade="all, delete-orphan",
1477 order_by="MaintenanceRecord.performed_at.desc()",
1478 )
1480 __table_args__ = (
1481 db.Index("ix_maintenance_triggers_aircraft_id", aircraft_id),
1482 db.Index("ix_maintenance_triggers_component_id", component_id),
1483 db.Index("ix_maintenance_triggers_import_batch_id", import_batch_id),
1484 )
1486 def status(
1487 self,
1488 current_engine_hours: "float | None" = None,
1489 current_landings: "int | None" = None,
1490 current_flight_hours: "float | None" = None,
1491 ) -> str:
1492 """Return 'overdue', 'due_soon', or 'ok'.
1494 Evaluates every populated field group (calendar ``due_date``, hours
1495 ``due_engine_hours``, landings ``due_landings``) independently and
1496 returns the worst result. This is what lets a trigger represent a
1497 "due at whichever comes first" combined interval (e.g. an AMP task
1498 quoted as "100FH / 12MO") simply by having more than one field group
1499 populated at once, with no separate ``trigger_type`` value or
1500 duplicate rows needed. A trigger with only one field group populated
1501 (the common case) behaves exactly as before this method started
1502 evaluating groups independently. For the hours group, ``hours_basis``
1503 picks which of ``current_engine_hours``/``current_flight_hours`` is
1504 compared against ``due_engine_hours``."""
1505 from datetime import date as _date
1507 statuses: list[str] = []
1509 if self.due_date is not None:
1510 delta = (self.due_date - _date.today()).days
1511 if delta < 0:
1512 statuses.append("overdue")
1513 else:
1514 warn_days = self.warn_days if self.warn_days is not None else 30
1515 if delta <= warn_days:
1516 statuses.append("due_soon")
1518 if self.due_engine_hours is not None:
1519 current_hobbs = (
1520 current_flight_hours
1521 if self.hours_basis == HoursBasis.FLIGHT
1522 else current_engine_hours
1523 )
1524 if current_hobbs is not None:
1525 remaining = float(self.due_engine_hours) - float(current_hobbs)
1526 if remaining <= 0:
1527 statuses.append("overdue")
1528 else:
1529 if self.warn_hours is not None:
1530 warn_h = float(self.warn_hours)
1531 else:
1532 warn_h = (
1533 max(float(self.interval_hours) * 0.1, 5.0)
1534 if self.interval_hours
1535 else 10.0
1536 )
1537 if remaining <= warn_h:
1538 statuses.append("due_soon")
1540 if self.due_landings is not None and current_landings is not None:
1541 remaining_l = self.due_landings - current_landings
1542 if remaining_l <= 0:
1543 statuses.append("overdue")
1544 else:
1545 if self.warn_landings is not None:
1546 warn_l = self.warn_landings
1547 else:
1548 warn_l = (
1549 max(int(self.interval_landings * 0.1), 5)
1550 if self.interval_landings
1551 else 10
1552 )
1553 if remaining_l <= warn_l:
1554 statuses.append("due_soon")
1556 if "overdue" in statuses:
1557 return "overdue"
1558 if "due_soon" in statuses:
1559 return "due_soon"
1560 return "ok"
1562 @property
1563 def last_record(self):
1564 return self.records[0] if self.records else None
1567# ── Phase 6: Demo Mode ────────────────────────────────────────────────────────
1570class DemoSlot(db.Model):
1571 """One isolated visitor slot in demo mode. Each slot is its own tenant+user pair."""
1573 __tablename__ = "demo_slots"
1575 id = db.Column(db.Integer, primary_key=True) # slot number 1..N
1576 display_id = db.Column(db.Integer, nullable=True) # random 1000-9999, shown in UI
1577 tenant_id = db.Column(
1578 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
1579 )
1580 user_id = db.Column(
1581 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
1582 )
1583 renter_user_id = db.Column(
1584 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1585 )
1586 maintenance_user_id = db.Column(
1587 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1588 )
1589 viewer_user_id = db.Column(
1590 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1591 )
1592 sole_pilot_user_id = db.Column(
1593 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1594 )
1595 sole_operator_user_id = db.Column(
1596 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1597 )
1598 # Phase 39h: shared-ownership sub-tenant (3 co-owner users on one shared
1599 # aircraft) — tracked by tenant id rather than a single user id, since
1600 # this sub-tenant has 3 users, not 1. SET NULL (not CASCADE): the demo
1601 # wipe cycle deletes this tenant explicitly (cascading its co-owners'
1602 # AircraftOwner/BillingAccount/etc. rows), it doesn't rely on the FK to
1603 # do it.
1604 shared_ownership_tenant_id = db.Column(
1605 db.Integer, db.ForeignKey("tenants.id", ondelete="SET NULL"), nullable=True
1606 )
1607 # One representative co-owner (of the 3 seeded on shared_ownership_tenant_id)
1608 # for the "Shared ownership" demo-role login button — mirrors the direct
1609 # *_user_id columns above rather than looking one up via the tenant at
1610 # login time.
1611 shared_ownership_user_id = db.Column(
1612 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
1613 )
1614 last_activity_at = db.Column(db.DateTime(timezone=True), nullable=True)
1617class MaintenanceRecord(db.Model):
1618 __tablename__ = "maintenance_records"
1620 id = db.Column(db.Integer, primary_key=True)
1621 trigger_id = db.Column(
1622 db.Integer,
1623 db.ForeignKey("maintenance_triggers.id", ondelete="CASCADE"),
1624 nullable=False,
1625 )
1626 performed_at = db.Column(db.Date, nullable=False)
1627 hobbs_at_service = db.Column(db.Numeric(8, 1), nullable=True)
1628 landings_at_service = db.Column(db.Integer, nullable=True)
1629 notes = db.Column(db.Text, nullable=True)
1630 created_at = db.Column(
1631 db.DateTime(timezone=True),
1632 nullable=False,
1633 default=lambda: datetime.now(UTC),
1634 )
1636 trigger = db.relationship("MaintenanceTrigger", back_populates="records")
1639# ── Phase 40: AMP Declaration Profile ────────────────────────────────────────
1642class AmpBasis:
1643 """EASA Form AMP block 2 — what the programme is based on."""
1645 DAH_ICA = "dah_ica"
1646 MIP = "mip"
1647 ALL: ClassVar[set[str]] = {DAH_ICA, MIP}
1650class AmpDeclarationType:
1651 """EASA Form AMP block 7 — declaration by the owner, or approval by a
1652 contracted CAMO/CAO."""
1654 OWNER = "owner"
1655 CAMO_CAO = "camo_cao"
1656 ALL: ClassVar[set[str]] = {OWNER, CAMO_CAO}
1659class AmpCertifyingPartyKind:
1660 """EASA Form AMP block 8 — who signs the certification statement."""
1662 OWNER_LESSEE_OPERATOR = "owner_lessee_operator"
1663 CAMO_CAO = "camo_cao"
1664 ALL: ClassVar[set[str]] = {OWNER_LESSEE_OPERATOR, CAMO_CAO}
1667class AmpDeclaration(db.Model):
1668 """One-to-one with Aircraft: the EASA Form AMP (AMC2 ML.A.302) fields
1669 that aren't derivable from Aircraft/Component/MaintenanceTrigger. Block
1670 4/5's Yes/No tables and Appendix B/C are computed from MaintenanceTrigger
1671 at export time, not stored here — see docs/maintenance_import.md."""
1673 __tablename__ = "amp_declarations"
1675 aircraft_id = db.Column(
1676 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True
1677 )
1679 # Block 1 — aircraft owner. Distinct from block 8's certifying party
1680 # below: real AMPs can have a contracted CAMO/CAO (block 8) acting on
1681 # behalf of a different aircraft owner (block 1) — e.g. a declaration
1682 # by the owner themselves has these match, but a CAMO/CAO approval
1683 # doesn't. Optional: when unset, export falls back to the certifying
1684 # party (the common case where they're the same person/entity).
1685 owner_name = db.Column(db.String(128), nullable=True)
1686 owner_address = db.Column(db.Text, nullable=True)
1688 # Block 2 — basis for the maintenance programme
1689 basis = db.Column(
1690 db.String(16),
1691 nullable=False,
1692 default=AmpBasis.DAH_ICA,
1693 server_default=AmpBasis.DAH_ICA,
1694 )
1695 mip_details = db.Column(db.Text, nullable=True) # Appendix A content, if basis=MIP
1697 # Block 3a-3c — DAH ICA reference per equipment (manufacturer/type/model
1698 # is already on Aircraft/Component; only the reference is stored here).
1699 # Balloon fields 3d-3g are out of scope — no balloon support elsewhere.
1700 dah_ica_airframe_ref = db.Column(db.String(255), nullable=True)
1701 dah_ica_engine_ref = db.Column(db.String(255), nullable=True)
1702 dah_ica_propeller_ref = db.Column(db.String(255), nullable=True)
1704 # Block 6 — pilot-owner maintenance (ML.A.803)
1705 pilot_owner_maintenance = db.Column(
1706 db.Boolean, nullable=False, default=False, server_default=db.false()
1707 )
1708 pilot_owner_name = db.Column(db.String(128), nullable=True)
1709 pilot_owner_licence_number = db.Column(db.String(64), nullable=True)
1711 # Block 7 — declaration/approval of the maintenance programme
1712 declaration_type = db.Column(
1713 db.String(16),
1714 nullable=False,
1715 default=AmpDeclarationType.OWNER,
1716 server_default=AmpDeclarationType.OWNER,
1717 )
1718 camo_cao_approval_reference = db.Column(db.String(128), nullable=True)
1720 # Block 8 — certification statement
1721 certifying_party_kind = db.Column(
1722 db.String(24),
1723 nullable=False,
1724 default=AmpCertifyingPartyKind.OWNER_LESSEE_OPERATOR,
1725 server_default=AmpCertifyingPartyKind.OWNER_LESSEE_OPERATOR,
1726 )
1727 certifying_party_name = db.Column(db.String(128), nullable=True)
1728 certifying_party_address = db.Column(db.Text, nullable=True)
1729 certifying_party_phone = db.Column(db.String(32), nullable=True)
1730 certifying_party_email = db.Column(db.String(128), nullable=True)
1732 # Appendix D — optional free text. Block 10 (revision history) is a
1733 # separate one-to-many AmpRevision table, not stored here — see below.
1734 appendix_d_notes = db.Column(db.Text, nullable=True)
1736 updated_at = db.Column(
1737 db.DateTime(timezone=True),
1738 nullable=False,
1739 default=lambda: datetime.now(UTC),
1740 onupdate=lambda: datetime.now(UTC),
1741 )
1743 aircraft = db.relationship("Aircraft", back_populates="amp_declaration")
1746class AmpRevision(db.Model):
1747 """Block 10 — Revision control & periodic reviews of the Aircraft
1748 Maintenance Programme. One-to-many with Aircraft (not AmpDeclaration)
1749 so revision history survives even if the declaration profile is ever
1750 deleted and recreated. Real shop-produced AMPs consistently carry
1751 multiple rows here (e.g. "R00 initial release", "R01 <what changed>",
1752 ...) — mirrors MaintenanceRecord's shape/relationship style."""
1754 __tablename__ = "amp_revisions"
1756 id = db.Column(db.Integer, primary_key=True)
1757 aircraft_id = db.Column(
1758 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1759 )
1760 revision_number = db.Column(db.String(16), nullable=False)
1761 revision_content = db.Column(db.Text, nullable=True)
1762 revision_date = db.Column(db.Date, nullable=True)
1763 # SHA-256 of the export HTML rendered at the moment this revision was
1764 # added — the "what did the AMP look like when this revision was
1765 # declared" snapshot. A later export whose freshly-rendered HTML hashes
1766 # to something else means the AMP's data has drifted since this
1767 # revision, so a download at that point is a draft, not this revision.
1768 content_hash = db.Column(db.String(64), nullable=True)
1769 # Path (relative to UPLOAD_FOLDER, mirrors Document.filename) of the
1770 # canonical PDF for this revision — set the first time a download
1771 # happens while content_hash still matches the live data, so this
1772 # revision's exact bytes stay re-downloadable even after later edits.
1773 # Never set at all if this revision was superseded before anyone ever
1774 # downloaded it while current — there is no way to reconstruct that.
1775 pdf_path = db.Column(db.String(512), nullable=True)
1776 created_at = db.Column(
1777 db.DateTime(timezone=True),
1778 nullable=False,
1779 default=lambda: datetime.now(UTC),
1780 )
1782 aircraft = db.relationship("Aircraft", back_populates="amp_revisions")
1784 __table_args__ = (db.Index("ix_amp_revisions_aircraft_id", "aircraft_id"),)
1787class MaintenanceImportBatch(db.Model):
1788 """One AMP task-list spreadsheet import — mirrors LogbookImportBatch
1789 (Phase 28): links every MaintenanceTrigger it created so the whole
1790 import can be reviewed or rolled back as a unit."""
1792 __tablename__ = "maintenance_import_batches"
1794 id = db.Column(db.Integer, primary_key=True)
1795 aircraft_id = db.Column(
1796 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1797 )
1798 source_filename = db.Column(db.String(256), nullable=False)
1799 imported_at = db.Column(
1800 db.DateTime(timezone=True),
1801 nullable=False,
1802 default=lambda: datetime.now(UTC),
1803 )
1804 row_count = db.Column(db.Integer, nullable=False, default=0)
1805 needs_review_count = db.Column(db.Integer, nullable=False, default=0)
1807 aircraft = db.relationship("Aircraft")
1808 triggers = db.relationship(
1809 "MaintenanceTrigger",
1810 back_populates="import_batch",
1811 foreign_keys="MaintenanceTrigger.import_batch_id",
1812 lazy="dynamic",
1813 )
1815 __table_args__ = (
1816 db.Index("ix_maintenance_import_batches_aircraft_id", aircraft_id),
1817 )
1820# ── Phase 8: Cost Tracking ────────────────────────────────────────────────────
1823class ExpenseType:
1824 FUEL = "fuel"
1825 PARTS = "parts"
1826 INSURANCE = "insurance"
1827 OTHER = "other"
1829 ALL: ClassVar[set[str]] = {FUEL, PARTS, INSURANCE, OTHER}
1830 LABELS: ClassVar[dict[str, str]] = {
1831 FUEL: "Fuel",
1832 PARTS: "Parts & Maintenance",
1833 INSURANCE: "Insurance",
1834 OTHER: "Other",
1835 }
1838class ExpenseCategory:
1839 """Phase 36: fixed costs (pro-rated by time) vs. operating costs (usage-based)."""
1841 FIXED = "fixed"
1842 OPERATING = "operating"
1844 ALL: ClassVar[set[str]] = {FIXED, OPERATING}
1845 LABELS: ClassVar[dict[str, str]] = {
1846 FIXED: "Fixed",
1847 OPERATING: "Operating",
1848 }
1849 # Default category per expense type; user may override on a per-entry basis.
1850 DEFAULTS: ClassVar[dict[str, str]] = {
1851 ExpenseType.FUEL: OPERATING,
1852 ExpenseType.PARTS: OPERATING,
1853 ExpenseType.INSURANCE: FIXED,
1854 ExpenseType.OTHER: OPERATING,
1855 }
1858class ExpenseRecurrence:
1859 """Recurring fixed costs: how often a template expense repeats."""
1861 MONTHLY = "monthly"
1862 QUARTERLY = "quarterly"
1863 YEARLY = "yearly"
1865 MONTHS: ClassVar[dict[str, int]] = {MONTHLY: 1, QUARTERLY: 3, YEARLY: 12}
1866 ALL: ClassVar[set[str]] = set(MONTHS)
1869class Expense(db.Model):
1870 __tablename__ = "expenses"
1872 id = db.Column(db.Integer, primary_key=True)
1873 aircraft_id = db.Column(
1874 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1875 )
1876 flight_entry_id = db.Column(
1877 db.Integer,
1878 db.ForeignKey("flights.id", ondelete="SET NULL"),
1879 nullable=True,
1880 )
1881 date = db.Column(db.Date, nullable=False)
1882 expense_type = db.Column(db.String(32), nullable=False, default=ExpenseType.OTHER)
1883 expense_category = db.Column(
1884 db.String(16), nullable=False, default=ExpenseCategory.OPERATING
1885 )
1886 description = db.Column(db.String(255), nullable=True)
1887 amount = db.Column(db.Numeric(10, 2), nullable=False)
1888 currency = db.Column(db.String(4), nullable=False, default="EUR")
1889 quantity = db.Column(db.Numeric(8, 2), nullable=True) # litres or gallons of fuel
1890 unit = db.Column(db.String(8), nullable=True) # L, gal
1891 # Phase 36: optional coverage span for fixed costs (e.g. an annual insurance
1892 # premium), used to pro-rate the amount across a report period shorter than
1893 # the coverage span. Left null, the expense counts in full on its `date`.
1894 coverage_start = db.Column(db.Date, nullable=True)
1895 coverage_end = db.Column(db.Date, nullable=True)
1896 # Recurring fixed costs: a template expense carries `recurrence`
1897 # (ExpenseRecurrence value, optionally bounded by recurrence_end); the
1898 # daily pass materialises ordinary Expense rows linked back through
1899 # recurring_template_id. recurrence_last_date is the materialiser's
1900 # cursor — deleting a generated row must not resurrect it next run.
1901 recurrence = db.Column(db.String(16), nullable=True)
1902 recurrence_end = db.Column(db.Date, nullable=True)
1903 recurrence_last_date = db.Column(db.Date, nullable=True)
1904 recurring_template_id = db.Column(
1905 db.Integer, db.ForeignKey("expenses.id", ondelete="SET NULL"), nullable=True
1906 )
1907 created_at = db.Column(
1908 db.DateTime(timezone=True),
1909 nullable=False,
1910 default=lambda: datetime.now(UTC),
1911 )
1912 # Phase 37e: who recorded this expense — used to attribute a renter's own
1913 # fuel-purchase expenses on a rental's linked flights for the fuel credit.
1914 created_by_id = db.Column(
1915 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
1916 )
1918 aircraft = db.relationship("Aircraft", back_populates="expenses")
1919 flight_entry = db.relationship("Flight", back_populates="expenses")
1920 created_by = db.relationship("User", foreign_keys=[created_by_id])
1921 receipts = db.relationship(
1922 "Document",
1923 back_populates="expense",
1924 cascade="all, delete-orphan",
1925 )
1926 recurring_template = db.relationship(
1927 "Expense",
1928 foreign_keys=[recurring_template_id],
1929 remote_side="Expense.id",
1930 uselist=False,
1931 )
1933 __table_args__ = (db.Index("ix_expenses_aircraft_id", aircraft_id),)
1936# ── Fuel: standalone refuel record (backlog) ─────────────────────────────────
1939class Refuel(db.Model):
1940 """A refuel not bracketed by a flight (e.g. topping off while at the
1941 airfield for maintenance). Mirrors Flight.fuel_added_*_qty/_unit but
1942 carries no flight linkage — findable from the aircraft page. Can be
1943 turned into a costed Expense the same way a flight's fuel-added figure
1944 can (see expenses._fuel_expense_prefill): only the date and aircraft
1945 matter for cost tracking, not whether the volume was logged here or
1946 on a Flight row."""
1948 __tablename__ = "refuels"
1950 id = db.Column(db.Integer, primary_key=True)
1951 aircraft_id = db.Column(
1952 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1953 )
1954 date = db.Column(db.Date, nullable=False)
1955 quantity = db.Column(db.Numeric(8, 2), nullable=False)
1956 unit = db.Column(db.String(8), nullable=False, default="L") # L, gal
1957 note = db.Column(db.String(255), nullable=True)
1958 created_at = db.Column(
1959 db.DateTime(timezone=True),
1960 nullable=False,
1961 default=lambda: datetime.now(UTC),
1962 )
1963 created_by_id = db.Column(
1964 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
1965 )
1967 aircraft = db.relationship("Aircraft", back_populates="refuels")
1968 created_by = db.relationship("User", foreign_keys=[created_by_id])
1970 __table_args__ = (db.Index("ix_refuels_aircraft_id", aircraft_id),)
1973# ── Fuel: per-tank capacity tracking (backlog) ───────────────────────────────
1976class AircraftFuelTank(db.Model):
1977 """One independently-tracked fuel tank on an aircraft (e.g. left/right
1978 wing, or a main + aux tank). An aircraft with a single combined tank
1979 still needs exactly one row here (name it e.g. "Main") — there is no
1980 separate scalar-capacity fallback."""
1982 __tablename__ = "aircraft_fuel_tanks"
1984 id = db.Column(db.Integer, primary_key=True)
1985 aircraft_id = db.Column(
1986 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
1987 )
1988 name = db.Column(db.String(64), nullable=False)
1989 capacity_liters = db.Column(db.Numeric(6, 1), nullable=False)
1990 sort_order = db.Column(db.Integer, nullable=False, default=0)
1992 aircraft = db.relationship("Aircraft", back_populates="fuel_tanks")
1994 __table_args__ = (db.Index("ix_aircraft_fuel_tanks_aircraft_id", aircraft_id),)
1997# ── Phase 9 / 27: Document & Photo Uploads ───────────────────────────────────
2000class DocType:
2001 LICENSE = "license"
2002 MEDICAL = "medical"
2003 INSURANCE_CERT = "insurance_certificate"
2004 ARC = "arc_certificate"
2007class DocCategory:
2008 """Broad document categories that map 1-to-1 to on-disk folder names.
2010 Used by the Syncthing-compatible canonical path layout:
2011 {tenant_slug}/{aircraft_reg}/{category}/{YYYY-MM-DD} - {title}.{ext}
2012 """
2014 MAINTENANCE = "maintenance"
2015 INSURANCE = "insurance"
2016 POH = "poh"
2017 AIRWORTHINESS = "airworthiness"
2018 LOGBOOK = "logbook"
2019 INVOICE = "invoice"
2020 OTHER = "other"
2021 UNCATEGORISED = "uncategorised"
2023 ALL: ClassVar[list[str]] = [
2024 MAINTENANCE,
2025 INSURANCE,
2026 POH,
2027 AIRWORTHINESS,
2028 LOGBOOK,
2029 INVOICE,
2030 OTHER,
2031 UNCATEGORISED,
2032 ]
2035class Document(db.Model):
2036 """
2037 A document or photo attached to an aircraft, component, flight entry, or
2038 pilot profile. aircraft_id or pilot_user_id must be set (not both).
2039 """
2041 __tablename__ = "documents"
2043 id = db.Column(db.Integer, primary_key=True)
2044 aircraft_id = db.Column(
2045 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
2046 )
2047 pilot_user_id = db.Column(
2048 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True
2049 )
2050 component_id = db.Column(
2051 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=True
2052 )
2053 flight_entry_id = db.Column(
2054 db.Integer,
2055 db.ForeignKey("flights.id", ondelete="CASCADE"),
2056 nullable=True,
2057 )
2058 # Receipt/invoice attached to an expense; such documents also carry
2059 # aircraft_id so access control and the aircraft document list apply.
2060 expense_id = db.Column(
2061 db.Integer, db.ForeignKey("expenses.id", ondelete="CASCADE"), nullable=True
2062 )
2063 # Signed rental agreement attached to a RenterAuthorization (Phase 37c).
2064 # Carries no aircraft_id — visible to the renter concerned and is_owner
2065 # users, not to the aircraft document list.
2066 renter_authorization_id = db.Column(
2067 db.Integer,
2068 db.ForeignKey("renter_authorizations.id", ondelete="CASCADE"),
2069 nullable=True,
2070 )
2071 filename = db.Column(
2072 db.String(512), nullable=False
2073 ) # stored path on disk (may include subdirectories)
2074 original_filename = db.Column(db.String(255), nullable=False) # as uploaded
2075 mime_type = db.Column(db.String(128), nullable=True)
2076 size_bytes = db.Column(db.Integer, nullable=True)
2077 title = db.Column(db.String(128), nullable=True) # optional display name
2078 doc_type = db.Column(db.String(32), nullable=True) # DocType constant
2079 category = db.Column(
2080 db.String(32), nullable=True
2081 ) # DocCategory value; drives on-disk folder
2082 # NULL means "in force as soon as uploaded" (the original behaviour,
2083 # before valid_from existed) -- only set it to model a document that
2084 # shouldn't become the active one until a future date, e.g. next
2085 # quarter's insurance cert uploaded ahead of time. See
2086 # documents/routes.py active_document_for().
2087 valid_from = db.Column(db.Date, nullable=True)
2088 valid_until = db.Column(db.Date, nullable=True)
2089 superseded_by_id = db.Column(
2090 db.Integer, db.ForeignKey("documents.id", ondelete="SET NULL"), nullable=True
2091 )
2092 is_sensitive = db.Column(db.Boolean, nullable=False, default=False)
2093 uploaded_at = db.Column(
2094 db.DateTime(timezone=True),
2095 nullable=False,
2096 default=lambda: datetime.now(UTC),
2097 )
2099 aircraft = db.relationship(
2100 "Aircraft",
2101 back_populates="documents",
2102 foreign_keys=[aircraft_id],
2103 )
2104 pilot_user = db.relationship("User", foreign_keys=[pilot_user_id])
2105 component = db.relationship("Component", back_populates="documents")
2106 flight_entry = db.relationship("Flight", back_populates="documents")
2107 expense = db.relationship("Expense", back_populates="receipts")
2108 renter_authorization = db.relationship(
2109 "RenterAuthorization", back_populates="agreement_documents"
2110 )
2111 superseded_by = db.relationship(
2112 "Document",
2113 foreign_keys=[superseded_by_id],
2114 remote_side="Document.id",
2115 uselist=False,
2116 )
2118 __table_args__ = (
2119 db.Index("ix_documents_aircraft_id", aircraft_id),
2120 db.Index("ix_documents_pilot_user_id", pilot_user_id),
2121 db.Index("ix_documents_component_id", component_id),
2122 db.Index("ix_documents_flight_entry_id", flight_entry_id),
2123 db.Index("ix_documents_expense_id", expense_id),
2124 )
2126 @property
2127 def owner_type(self) -> str:
2128 if self.pilot_user_id:
2129 return "pilot"
2130 if self.component_id:
2131 return "component"
2132 if self.flight_entry_id:
2133 return "entry"
2134 if self.expense_id:
2135 return "expense"
2136 return "aircraft"
2138 @property
2139 def is_image(self) -> bool:
2140 return bool(self.mime_type and self.mime_type.startswith("image/"))
2142 @property
2143 def is_pdf(self) -> bool:
2144 return self.mime_type == "application/pdf"
2146 @property
2147 def is_expiring_soon(self) -> bool:
2148 """True when valid_until is set and within 90 days from today."""
2149 from datetime import date as _date
2151 if self.valid_until is None:
2152 return False
2153 return (self.valid_until - _date.today()).days <= 90
2155 @property
2156 def is_upcoming(self) -> bool:
2157 """True when valid_from is set and still in the future -- this
2158 document is on file but not yet in force."""
2159 from datetime import date as _date
2161 if self.valid_from is None:
2162 return False
2163 return self.valid_from > _date.today()
2166# ── Syncthing reconcile queue ─────────────────────────────────────────────────
2169class PendingReconcile(db.Model):
2170 """Files found on disk (via Syncthing or manual copy) that are not yet
2171 tracked in the documents table. The reconcile screen lets owners review
2172 these files and import them as Document rows with a single click.
2174 filepath is relative to UPLOAD_FOLDER (e.g. 'my-hangar/OO-PNH/maintenance/
2175 2024-03-15 - Annual inspection.pdf'). The unique constraint prevents the
2176 same file from appearing twice in the queue.
2177 """
2179 __tablename__ = "pending_reconcile"
2181 id = db.Column(db.Integer, primary_key=True)
2182 tenant_id = db.Column(
2183 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
2184 )
2185 aircraft_id = db.Column(
2186 db.Integer, db.ForeignKey("aircraft.id", ondelete="SET NULL"), nullable=True
2187 )
2188 filepath = db.Column(db.String(512), nullable=False, unique=True)
2189 category = db.Column(db.String(32), nullable=True)
2190 title_hint = db.Column(db.String(255), nullable=True)
2191 date_hint = db.Column(db.Date, nullable=True)
2192 detected_at = db.Column(
2193 db.DateTime(timezone=True),
2194 nullable=False,
2195 default=lambda: datetime.now(UTC),
2196 )
2197 reconciled_at = db.Column(db.DateTime(timezone=True), nullable=True)
2198 ignored = db.Column(db.Boolean, nullable=False, default=False)
2200 tenant = db.relationship("Tenant")
2201 aircraft = db.relationship("Aircraft")
2204# ── Phase 10: Backup & Restore ────────────────────────────────────────────────
2207class BackupRecord(db.Model):
2208 __tablename__ = "backup_records"
2210 id = db.Column(db.Integer, primary_key=True)
2211 filename = db.Column(db.String(255), nullable=False)
2212 path = db.Column(db.String(512), nullable=False)
2213 size_bytes = db.Column(db.Integer, nullable=True)
2214 sha256 = db.Column(db.String(64), nullable=True)
2215 created_at = db.Column(
2216 db.DateTime(timezone=True),
2217 nullable=False,
2218 default=lambda: datetime.now(UTC),
2219 )
2220 status = db.Column(db.String(32), nullable=False, default="ok") # ok / failed
2221 app_version = db.Column(db.String(64), nullable=True)
2222 alembic_head = db.Column(db.String(64), nullable=True)
2225# ── Phase 11: Read-only Share Links ──────────────────────────────────────────
2228class ShareToken(db.Model):
2229 __tablename__ = "share_tokens"
2231 id = db.Column(db.Integer, primary_key=True)
2232 aircraft_id = db.Column(
2233 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2234 )
2235 token = db.Column(db.String(16), unique=True, nullable=False, index=True)
2236 access_level = db.Column(
2237 db.String(16), nullable=False, default="summary"
2238 ) # summary / full
2239 created_at = db.Column(
2240 db.DateTime(timezone=True),
2241 nullable=False,
2242 default=lambda: datetime.now(UTC),
2243 )
2244 revoked_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)
2246 aircraft = db.relationship("Aircraft", back_populates="share_tokens")
2248 @property
2249 def is_active(self) -> bool:
2250 return self.revoked_at is None
2253# ── Phase 12: Snag List ───────────────────────────────────────────────────────
2256class Snag(db.Model):
2257 __tablename__ = "snags"
2259 id = db.Column(db.Integer, primary_key=True)
2260 aircraft_id = db.Column(
2261 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2262 )
2263 title = db.Column(db.String(128), nullable=False)
2264 description = db.Column(db.Text, nullable=True)
2265 reporter = db.Column(db.String(128), nullable=True)
2266 is_grounding = db.Column(db.Boolean, nullable=False, default=False)
2267 reported_at = db.Column(
2268 db.DateTime(timezone=True),
2269 nullable=False,
2270 default=lambda: datetime.now(UTC),
2271 )
2272 resolved_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)
2273 resolution_note = db.Column(db.Text, nullable=True)
2275 aircraft = db.relationship("Aircraft", back_populates="snags")
2277 __table_args__ = (db.Index("ix_snags_aircraft_id", aircraft_id),)
2279 @property
2280 def is_open(self) -> bool:
2281 return self.resolved_at is None
2284# ── Phase 22: Reservations ───────────────────────────────────────────────────
2287class ReservationStatus(str, enum.Enum):
2288 PENDING = "pending"
2289 CONFIRMED = "confirmed"
2290 CANCELLED = "cancelled"
2293class Reservation(db.Model):
2294 __tablename__ = "reservations"
2296 id = db.Column(db.Integer, primary_key=True)
2297 aircraft_id = db.Column(
2298 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2299 )
2300 pilot_user_id = db.Column(
2301 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2302 )
2303 start_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2304 end_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2305 status = db.Column(
2306 db.Enum(ReservationStatus), nullable=False, default=ReservationStatus.PENDING
2307 )
2308 notes = db.Column(db.Text, nullable=True)
2309 hourly_rate = db.Column(db.Numeric(8, 2), nullable=True) # EUR/h snapshot
2310 estimated_cost = db.Column(db.Numeric(10, 2), nullable=True)
2311 created_at = db.Column(
2312 db.DateTime(timezone=True),
2313 nullable=False,
2314 default=lambda: datetime.now(UTC),
2315 )
2317 aircraft = db.relationship("Aircraft", back_populates="reservations")
2318 pilot = db.relationship("User", foreign_keys=[pilot_user_id])
2319 flights = db.relationship("Flight", back_populates="reservation")
2320 dispatch = db.relationship(
2321 "DispatchRecord", back_populates="reservation", uselist=False
2322 )
2323 rental_charge = db.relationship(
2324 "RentalCharge", back_populates="reservation", uselist=False
2325 )
2327 __table_args__ = (db.Index("ix_reservations_aircraft_id", aircraft_id),)
2329 @property
2330 def duration_hours(self) -> float:
2331 delta = self.end_dt - self.start_dt
2332 return round(delta.total_seconds() / 3600, 2)
2335class DispatchRecord(db.Model):
2336 """Phase 37d: check-out / check-in record for a rental reservation. One
2337 row per reservation (unique constraint) — created at check-out, updated
2338 at check-in."""
2340 __tablename__ = "dispatch_records"
2342 id = db.Column(db.Integer, primary_key=True)
2343 reservation_id = db.Column(
2344 db.Integer,
2345 db.ForeignKey("reservations.id", ondelete="CASCADE"),
2346 nullable=False,
2347 unique=True,
2348 )
2349 # Check-out
2350 out_at = db.Column(db.DateTime(timezone=True), nullable=True)
2351 out_by_id = db.Column(
2352 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2353 )
2354 out_engine_counter = db.Column(db.Numeric(8, 1), nullable=True)
2355 out_flight_counter = db.Column(db.Numeric(8, 1), nullable=True)
2356 out_fuel_state = db.Column(db.String(64), nullable=True)
2357 out_walkaround_ok = db.Column(db.Boolean, nullable=False, default=False)
2358 out_snags_acknowledged = db.Column(db.Boolean, nullable=False, default=False)
2359 # is_owner explicitly dispatched despite a grounding snag — auditability.
2360 out_grounded_override = db.Column(db.Boolean, nullable=False, default=False)
2361 # Check-in
2362 in_at = db.Column(db.DateTime(timezone=True), nullable=True)
2363 in_by_id = db.Column(
2364 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2365 )
2366 in_engine_counter = db.Column(db.Numeric(8, 1), nullable=True)
2367 in_flight_counter = db.Column(db.Numeric(8, 1), nullable=True)
2368 in_fuel_state = db.Column(db.String(64), nullable=True)
2369 in_notes = db.Column(db.Text, nullable=True)
2371 reservation = db.relationship("Reservation", back_populates="dispatch")
2372 out_by = db.relationship("User", foreign_keys=[out_by_id])
2373 in_by = db.relationship("User", foreign_keys=[in_by_id])
2375 @property
2376 def is_checked_out(self) -> bool:
2377 return self.out_at is not None
2379 @property
2380 def is_checked_in(self) -> bool:
2381 return self.in_at is not None
2384class RentalChargeStatus:
2385 DRAFT = "draft"
2386 FINAL = "final"
2388 ALL: ClassVar[set[str]] = {DRAFT, FINAL}
2391class RentalCharge(db.Model):
2392 """Phase 37e: rental charge for one reservation. Drafted automatically at
2393 check-in (see reservations.routes.checkin); the owner reviews and
2394 finalizes it, which posts one CHARGE to the billing ledger (Phase 37a).
2395 A finalized charge is immutable — corrections go through
2396 BillingService.reverse plus a new adjustment, never an edit."""
2398 __tablename__ = "rental_charges"
2400 id = db.Column(db.Integer, primary_key=True)
2401 reservation_id = db.Column(
2402 db.Integer,
2403 db.ForeignKey("reservations.id", ondelete="CASCADE"),
2404 nullable=False,
2405 unique=True,
2406 )
2407 renter_user_id = db.Column(
2408 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
2409 )
2410 status = db.Column(db.String(12), nullable=False, default=RentalChargeStatus.DRAFT)
2411 billable_hours = db.Column(db.Numeric(6, 1), nullable=False)
2412 hourly_rate = db.Column(db.Numeric(8, 2), nullable=False) # snapshot
2413 rate_type = db.Column(db.String(8), nullable=False) # snapshot
2414 fuel_credit = db.Column(
2415 db.Numeric(10, 2), nullable=False, default=0
2416 ) # positive, subtracted
2417 adjustment = db.Column(db.Numeric(10, 2), nullable=False, default=0) # signed
2418 adjustment_note = db.Column(db.String(255), nullable=True)
2419 # True when the drafting logic fell back to the non-preferred counter
2420 # (the rate_basis one was left blank at dispatch) — shown as a note on
2421 # the draft review, not a data-quality error.
2422 fallback_counter_used = db.Column(db.Boolean, nullable=False, default=False)
2423 total = db.Column(
2424 db.Numeric(10, 2), nullable=False
2425 ) # hours*rate - credit + adjustment
2426 finalized_at = db.Column(db.DateTime(timezone=True), nullable=True)
2427 finalized_by_id = db.Column(
2428 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2429 )
2430 created_at = db.Column(
2431 db.DateTime(timezone=True),
2432 nullable=False,
2433 default=lambda: datetime.now(UTC),
2434 )
2436 reservation = db.relationship("Reservation", back_populates="rental_charge")
2437 renter_user = db.relationship("User", foreign_keys=[renter_user_id])
2438 finalized_by = db.relationship("User", foreign_keys=[finalized_by_id])
2440 @property
2441 def is_final(self) -> bool:
2442 return self.status == RentalChargeStatus.FINAL
2445class MaintenanceDowntime(db.Model):
2446 """Phase 37f: owner-entered planned unavailability window (e.g. a shop
2447 appointment). Behaves like a confirmed reservation in conflict
2448 detection and is rendered on the booking calendar in a distinct style.
2449 Downtime is scheduling; a grounding snag is airworthiness — related but
2450 separate records."""
2452 __tablename__ = "maintenance_downtimes"
2454 id = db.Column(db.Integer, primary_key=True)
2455 aircraft_id = db.Column(
2456 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2457 )
2458 start_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2459 end_dt = db.Column(db.DateTime(timezone=True), nullable=False)
2460 reason = db.Column(db.String(255), nullable=True)
2461 created_by_id = db.Column(
2462 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2463 )
2464 created_at = db.Column(
2465 db.DateTime(timezone=True),
2466 nullable=False,
2467 default=lambda: datetime.now(UTC),
2468 )
2470 aircraft = db.relationship("Aircraft", back_populates="maintenance_downtimes")
2471 created_by = db.relationship("User", foreign_keys=[created_by_id])
2474class RateBasis:
2475 """Phase 37b: which counter delta a rental charge is billed against."""
2477 ENGINE_TIME = "engine_time"
2478 FLIGHT_TIME = "flight_time"
2480 ALL: ClassVar[set[str]] = {ENGINE_TIME, FLIGHT_TIME}
2481 LABELS: ClassVar[dict[str, str]] = {
2482 ENGINE_TIME: "Engine time",
2483 FLIGHT_TIME: "Flight time",
2484 }
2487class RateType:
2488 """Phase 37b: wet (fuel included) vs. dry (fuel billed separately)."""
2490 WET = "wet"
2491 DRY = "dry"
2493 ALL: ClassVar[set[str]] = {WET, DRY}
2494 LABELS: ClassVar[dict[str, str]] = {
2495 WET: "Wet",
2496 DRY: "Dry",
2497 }
2500class AircraftBookingSettings(db.Model):
2501 """Per-aircraft booking rules and hourly rate for cost estimation."""
2503 __tablename__ = "aircraft_booking_settings"
2505 aircraft_id = db.Column(
2506 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True
2507 )
2508 min_booking_hours = db.Column(db.Numeric(4, 1), nullable=True)
2509 max_booking_hours = db.Column(db.Numeric(4, 1), nullable=True)
2510 hourly_rate = db.Column(db.Numeric(8, 2), nullable=True) # EUR/h
2511 rate_basis = db.Column(db.String(16), nullable=False, default=RateBasis.ENGINE_TIME)
2512 rate_type = db.Column(db.String(8), nullable=False, default=RateType.WET)
2513 min_hours_per_day = db.Column(db.Numeric(4, 1), nullable=True)
2515 aircraft = db.relationship("Aircraft", back_populates="booking_settings")
2518class RenterAuthorization(db.Model):
2519 """Phase 37c: owner-verified rental qualification for one renter, scoped
2520 to one aircraft or the whole fleet (aircraft_id is NULL).
2522 These are owner-entered verification facts — deliberately NOT automatic
2523 reads of the renter's private PilotProfile.
2524 """
2526 __tablename__ = "renter_authorizations"
2527 __table_args__ = (
2528 db.Index("ix_renter_authorizations_tenant_id", "tenant_id"),
2529 db.Index("ix_renter_authorizations_renter_user_id", "renter_user_id"),
2530 )
2532 id = db.Column(db.Integer, primary_key=True)
2533 tenant_id = db.Column(
2534 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
2535 )
2536 renter_user_id = db.Column(
2537 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
2538 )
2539 aircraft_id = db.Column(
2540 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
2541 ) # NULL = whole fleet
2542 authorized_by_id = db.Column(
2543 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
2544 )
2545 granted_on = db.Column(db.Date, nullable=False)
2546 expires_on = db.Column(db.Date, nullable=True) # NULL = does not expire
2547 checkout_flight_on = db.Column(db.Date, nullable=True)
2548 licence_seen_on = db.Column(db.Date, nullable=True)
2549 medical_valid_until = db.Column(db.Date, nullable=True) # owner-entered
2550 notes = db.Column(db.Text, nullable=True)
2551 revoked_at = db.Column(db.DateTime(timezone=True), nullable=True)
2552 created_at = db.Column(
2553 db.DateTime(timezone=True),
2554 nullable=False,
2555 default=lambda: datetime.now(UTC),
2556 )
2558 tenant = db.relationship("Tenant")
2559 renter_user = db.relationship("User", foreign_keys=[renter_user_id])
2560 authorized_by = db.relationship("User", foreign_keys=[authorized_by_id])
2561 aircraft = db.relationship("Aircraft")
2562 agreement_documents = db.relationship(
2563 "Document", back_populates="renter_authorization"
2564 )
2566 @property
2567 def is_valid(self) -> bool:
2568 from datetime import date as _date
2570 if self.revoked_at is not None:
2571 return False
2572 today = _date.today()
2573 if self.expires_on is not None and self.expires_on < today:
2574 return False
2575 return not (
2576 self.medical_valid_until is not None and self.medical_valid_until < today
2577 )
2579 @staticmethod
2580 def valid_for(
2581 renter_user_id: int, aircraft_id: int
2582 ) -> "RenterAuthorization | None":
2583 """Return a valid authorization covering this renter+aircraft — a
2584 fleet-wide row (aircraft_id IS NULL) or a per-aircraft row — or None."""
2585 candidates = RenterAuthorization.query.filter(
2586 RenterAuthorization.renter_user_id == renter_user_id,
2587 RenterAuthorization.revoked_at.is_(None),
2588 db.or_(
2589 RenterAuthorization.aircraft_id.is_(None),
2590 RenterAuthorization.aircraft_id == aircraft_id,
2591 ),
2592 ).all()
2593 return next((c for c in candidates if c.is_valid), None)
2596# ── Phase 20: Mass & Balance ──────────────────────────────────────────────────
2598FUEL_DENSITY = {
2599 "avgas": 0.72, # Avgas 100LL
2600 "ul91": 0.72, # UL91 — unleaded avgas replacement
2601 "mogas": 0.74, # Automotive gasoline (Mogas)
2602 "jet_a1": 0.81, # Jet-A1 (kerosene)
2603} # kg/L
2604GAL_TO_L = 3.78541 # US gallons to litres
2607class WeightBalanceConfig(db.Model):
2608 __tablename__ = "wb_configs"
2610 id = db.Column(db.Integer, primary_key=True)
2611 aircraft_id = db.Column(
2612 db.Integer,
2613 db.ForeignKey("aircraft.id", ondelete="CASCADE"),
2614 nullable=False,
2615 unique=True,
2616 )
2617 empty_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg
2618 empty_cg_arm = db.Column(db.Numeric(7, 2), nullable=False) # m from datum
2619 max_takeoff_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg
2620 forward_cg_limit = db.Column(db.Numeric(7, 2), nullable=False) # m
2621 aft_cg_limit = db.Column(db.Numeric(7, 2), nullable=False) # m
2622 fuel_unit = db.Column(db.String(3), nullable=False, default="L") # "L" or "gal"
2623 # Optional non-rectangular envelope: list of [arm_m, weight_kg] pairs in polygon order.
2624 # When ≥ 3 points are present they override forward_cg_limit/aft_cg_limit/max_takeoff_weight
2625 # for the in-envelope check.
2626 envelope_points = db.Column(db.JSON, nullable=True)
2627 datum_note = db.Column(db.String(200), nullable=True)
2629 aircraft = db.relationship("Aircraft", back_populates="wb_config")
2630 stations = db.relationship(
2631 "WeightBalanceStation",
2632 back_populates="config",
2633 cascade="all, delete-orphan",
2634 order_by="WeightBalanceStation.position",
2635 )
2636 entries = db.relationship(
2637 "WeightBalanceEntry",
2638 back_populates="config",
2639 cascade="all, delete-orphan",
2640 )
2643class WeightBalanceStation(db.Model):
2644 __tablename__ = "wb_stations"
2646 id = db.Column(db.Integer, primary_key=True)
2647 config_id = db.Column(
2648 db.Integer, db.ForeignKey("wb_configs.id", ondelete="CASCADE"), nullable=False
2649 )
2650 label = db.Column(db.String(64), nullable=False)
2651 arm = db.Column(db.Numeric(7, 2), nullable=False) # m from datum
2652 max_weight = db.Column(db.Numeric(6, 2), nullable=True) # kg limit (non-fuel only)
2653 capacity = db.Column(db.Float, nullable=True) # L or gal (fuel stations)
2654 is_fuel = db.Column(db.Boolean, nullable=False, default=False)
2655 position = db.Column(db.Integer, nullable=False, default=0) # display order
2657 config = db.relationship("WeightBalanceConfig", back_populates="stations")
2660class WeightBalanceEntry(db.Model):
2661 __tablename__ = "wb_entries"
2663 id = db.Column(db.Integer, primary_key=True)
2664 config_id = db.Column(
2665 db.Integer, db.ForeignKey("wb_configs.id", ondelete="CASCADE"), nullable=False
2666 )
2667 date = db.Column(db.Date, nullable=False)
2668 label = db.Column(db.String(100), nullable=True)
2669 total_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg
2670 loaded_cg = db.Column(db.Numeric(7, 2), nullable=False) # mm
2671 is_in_envelope = db.Column(db.Boolean, nullable=False)
2672 # {station_id_str: value} — fuel stations store volume (L or gal), non-fuel store kg
2673 station_weights = db.Column(db.JSON, nullable=False, default=dict)
2674 created_at = db.Column(
2675 db.DateTime(timezone=True),
2676 nullable=False,
2677 default=lambda: datetime.now(UTC),
2678 )
2680 config = db.relationship("WeightBalanceConfig", back_populates="entries")
2683class AppSetting(db.Model):
2684 __tablename__ = "app_settings"
2686 key = db.Column(db.String(64), primary_key=True)
2687 value = db.Column(db.Text, nullable=True)
2690# ── Phase 33: Airworthiness Requirements Tracker ──────────────────────────────
2693class EASASourceNode(db.Model):
2694 """
2695 Maps a Component to one leaf node in the EASA Safety Publications Tool
2696 taxonomy tree (TC holder → type → model). One component may have multiple
2697 nodes (e.g. base TC plus an installed STC that also carries ADs).
2698 """
2700 __tablename__ = "easa_source_nodes"
2702 id = db.Column(db.Integer, primary_key=True)
2703 component_id = db.Column(
2704 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=False
2705 )
2706 tc_holder_node_id = db.Column(db.String(16), nullable=False)
2707 tc_holder_name = db.Column(db.String(128), nullable=False)
2708 type_node_id = db.Column(db.String(16), nullable=False)
2709 type_name = db.Column(db.String(128), nullable=False)
2710 model_node_id = db.Column(db.String(16), nullable=False)
2711 model_name = db.Column(db.String(128), nullable=False)
2712 last_synced_at = db.Column(db.DateTime(timezone=True), nullable=True)
2713 consecutive_errors = db.Column(db.Integer, nullable=False, default=0)
2715 component = db.relationship("Component", back_populates="easa_source_nodes")
2716 documents = db.relationship(
2717 "AirworthinessDocument",
2718 back_populates="source_node",
2719 cascade="all, delete-orphan",
2720 )
2722 @property
2723 def display_path(self) -> str:
2724 return f"{self.tc_holder_name} / {self.type_name} / {self.model_name}"
2727class AirworthinessDocType:
2728 AD = "ad"
2729 MANDATORY_SB = "mandatory_sb"
2730 SB = "sb"
2731 SIB = "sib"
2732 ARC = "arc"
2733 MANUAL = "manual"
2735 ALL = (AD, MANDATORY_SB, SB, SIB, ARC, MANUAL)
2736 SYNCED = (AD, SIB) # types populated by EASA sync
2737 LABELS: ClassVar[dict[str, str]] = {
2738 AD: "AD",
2739 MANDATORY_SB: "Mandatory SB",
2740 SB: "SB",
2741 SIB: "SIB",
2742 ARC: "ARC",
2743 MANUAL: "Manual",
2744 }
2747class AirworthinessDocStatus:
2748 PENDING_REVIEW = "pending_review"
2749 COMPLIED = "complied"
2750 NOT_APPLICABLE = "not_applicable"
2751 DEFERRED = "deferred"
2752 QUESTION = "question"
2754 ALL = (PENDING_REVIEW, COMPLIED, NOT_APPLICABLE, DEFERRED, QUESTION)
2757class AirworthinessDocument(db.Model):
2758 """
2759 One airworthiness-related document (AD, SB, SIB, ARC, …) applicable to a
2760 component. Synced documents reference a source_node; manually entered
2761 documents have source_node_id = NULL.
2762 """
2764 __tablename__ = "airworthiness_documents"
2766 id = db.Column(db.Integer, primary_key=True)
2767 doc_type = db.Column(db.String(16), nullable=False)
2768 reference = db.Column(db.String(64), nullable=False)
2769 title = db.Column(db.String(256), nullable=True)
2770 source_node_id = db.Column(
2771 db.Integer,
2772 db.ForeignKey("easa_source_nodes.id", ondelete="CASCADE"),
2773 nullable=True,
2774 )
2775 # For manual entries without a source node, store the component directly
2776 component_id = db.Column(
2777 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=True
2778 )
2779 doc_url = db.Column(db.String(512), nullable=True)
2780 # For ARC: date the certificate expires
2781 expiry_date = db.Column(db.Date, nullable=True)
2782 first_seen_at = db.Column(
2783 db.DateTime(timezone=True),
2784 nullable=False,
2785 default=lambda: datetime.now(UTC),
2786 )
2788 source_node = db.relationship("EASASourceNode", back_populates="documents")
2789 component = db.relationship("Component", back_populates="airworthiness_documents")
2790 statuses = db.relationship(
2791 "AirworthinessDocumentStatus",
2792 back_populates="document",
2793 cascade="all, delete-orphan",
2794 )
2796 @property
2797 def is_manual(self) -> bool:
2798 return self.source_node_id is None
2801class AirworthinessDocumentStatus(db.Model):
2802 """
2803 Compliance state of one AirworthinessDocument for one aircraft.
2804 Unique per (aircraft_id, document_id).
2805 """
2807 __tablename__ = "airworthiness_document_statuses"
2808 __table_args__ = (
2809 db.UniqueConstraint("aircraft_id", "document_id", name="uq_aw_status"),
2810 )
2812 id = db.Column(db.Integer, primary_key=True)
2813 aircraft_id = db.Column(
2814 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2815 )
2816 document_id = db.Column(
2817 db.Integer,
2818 db.ForeignKey("airworthiness_documents.id", ondelete="CASCADE"),
2819 nullable=False,
2820 )
2821 status = db.Column(
2822 db.String(24), nullable=False, default=AirworthinessDocStatus.PENDING_REVIEW
2823 )
2824 notes = db.Column(db.Text, nullable=True)
2825 compliance_date = db.Column(db.Date, nullable=True)
2826 next_review_date = db.Column(db.Date, nullable=True)
2827 updated_at = db.Column(
2828 db.DateTime(timezone=True),
2829 nullable=False,
2830 default=lambda: datetime.now(UTC),
2831 onupdate=lambda: datetime.now(UTC),
2832 )
2834 aircraft = db.relationship("Aircraft", back_populates="airworthiness_statuses")
2835 document = db.relationship("AirworthinessDocument", back_populates="statuses")
2838class InstalledSTC(db.Model):
2839 """
2840 Registry of Supplemental Type Certificates physically installed on an
2841 aircraft. No compliance workflow — presence/absence is the record.
2842 """
2844 __tablename__ = "installed_stcs"
2846 id = db.Column(db.Integer, primary_key=True)
2847 aircraft_id = db.Column(
2848 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
2849 )
2850 stc_number = db.Column(db.String(64), nullable=False)
2851 title = db.Column(db.String(256), nullable=True)
2852 tc_holder = db.Column(db.String(128), nullable=True)
2853 installation_date = db.Column(db.Date, nullable=True)
2854 notes = db.Column(db.Text, nullable=True)
2856 aircraft = db.relationship("Aircraft", back_populates="installed_stcs")
2859# ── Phase 34: Email Notifications ─────────────────────────────────────────────
2862class NotificationType:
2863 """String constants for all supported notification types.
2865 Stored as plain strings in the DB — no enum migration needed to add types.
2866 """
2868 GROUNDING_SNAG_OPENED = "grounding_snag_opened"
2869 SNAG_REPORTED = "snag_reported"
2870 RESERVATION_CONFIRMED = "reservation_confirmed"
2871 RESERVATION_CANCELLED = "reservation_cancelled"
2872 RESERVATION_REQUEST = "reservation_request"
2873 MAINTENANCE_DUE_SOON = "maintenance_due_soon"
2874 MAINTENANCE_OVERDUE = "maintenance_overdue"
2875 INSURANCE_EXPIRING = "insurance_expiring"
2876 ARC_EXPIRY = "arc_expiry"
2877 MEDICAL_EXPIRING = "medical_expiring"
2878 SEP_RATING_EXPIRING = "sep_rating_expiring"
2879 DOCUMENT_EXPIRING = "document_expiring"
2880 NEW_MEMBER_JOINED = "new_member_joined"
2881 AIRWORTHINESS_REVIEW_DUE = "airworthiness_review_due"
2882 EASA_SYNC_NEW_AD = "easa_sync_new_ad"
2883 RENTER_AUTHORIZATION_EXPIRY = "renter_authorization_expiry"
2884 RESERVATION_AIRCRAFT_GROUNDED = "reservation_aircraft_grounded"
2885 PERSONAL_MINIMUMS_RECENCY = "personal_minimums_recency"
2887 ALL: ClassVar[list[str]] = [
2888 GROUNDING_SNAG_OPENED,
2889 SNAG_REPORTED,
2890 RESERVATION_CONFIRMED,
2891 RESERVATION_CANCELLED,
2892 RESERVATION_REQUEST,
2893 MAINTENANCE_DUE_SOON,
2894 MAINTENANCE_OVERDUE,
2895 INSURANCE_EXPIRING,
2896 ARC_EXPIRY,
2897 MEDICAL_EXPIRING,
2898 SEP_RATING_EXPIRING,
2899 DOCUMENT_EXPIRING,
2900 NEW_MEMBER_JOINED,
2901 AIRWORTHINESS_REVIEW_DUE,
2902 EASA_SYNC_NEW_AD,
2903 RENTER_AUTHORIZATION_EXPIRY,
2904 RESERVATION_AIRCRAFT_GROUNDED,
2905 PERSONAL_MINIMUMS_RECENCY,
2906 ]
2908 # System defaults — coded constants; DB only stores per-user or per-tenant overrides
2909 SYSTEM_DEFAULTS: ClassVar[dict[str, dict]] = {
2910 GROUNDING_SNAG_OPENED: {"enabled": True, "threshold_days": None},
2911 SNAG_REPORTED: {"enabled": False, "threshold_days": None},
2912 RESERVATION_CONFIRMED: {"enabled": True, "threshold_days": None},
2913 RESERVATION_CANCELLED: {"enabled": True, "threshold_days": None},
2914 RESERVATION_REQUEST: {"enabled": True, "threshold_days": None},
2915 MAINTENANCE_DUE_SOON: {"enabled": True, "threshold_days": 30},
2916 MAINTENANCE_OVERDUE: {"enabled": True, "threshold_days": None},
2917 INSURANCE_EXPIRING: {"enabled": True, "threshold_days": 30},
2918 ARC_EXPIRY: {"enabled": True, "threshold_days": 60},
2919 MEDICAL_EXPIRING: {"enabled": True, "threshold_days": 60},
2920 SEP_RATING_EXPIRING: {"enabled": True, "threshold_days": 60},
2921 DOCUMENT_EXPIRING: {"enabled": True, "threshold_days": 30},
2922 NEW_MEMBER_JOINED: {"enabled": False, "threshold_days": None},
2923 AIRWORTHINESS_REVIEW_DUE: {"enabled": True, "threshold_days": 30},
2924 EASA_SYNC_NEW_AD: {"enabled": True, "threshold_days": None},
2925 RENTER_AUTHORIZATION_EXPIRY: {"enabled": True, "threshold_days": 30},
2926 RESERVATION_AIRCRAFT_GROUNDED: {"enabled": True, "threshold_days": None},
2927 PERSONAL_MINIMUMS_RECENCY: {"enabled": True, "threshold_days": None},
2928 }
2930 # Capability flags required — user sees this type in their prefs if they have >= 1
2931 # "is_owner" | "is_pilot" | "is_maint" match init.py context processor naming
2932 REQUIRED_CAPS: ClassVar[dict[str, list[str]]] = {
2933 GROUNDING_SNAG_OPENED: ["is_owner", "is_maint"],
2934 SNAG_REPORTED: ["is_owner"],
2935 RESERVATION_CONFIRMED: ["is_pilot"],
2936 RESERVATION_CANCELLED: ["is_pilot"],
2937 RESERVATION_REQUEST: ["is_owner"],
2938 MAINTENANCE_DUE_SOON: ["is_owner", "is_maint"],
2939 MAINTENANCE_OVERDUE: ["is_owner", "is_maint"],
2940 INSURANCE_EXPIRING: ["is_owner"],
2941 ARC_EXPIRY: ["is_owner"],
2942 MEDICAL_EXPIRING: ["is_pilot"],
2943 SEP_RATING_EXPIRING: ["is_pilot"],
2944 DOCUMENT_EXPIRING: ["is_owner", "is_maint"],
2945 NEW_MEMBER_JOINED: ["is_owner"],
2946 AIRWORTHINESS_REVIEW_DUE: ["is_owner", "is_maint"],
2947 EASA_SYNC_NEW_AD: ["is_owner", "is_maint"],
2948 RENTER_AUTHORIZATION_EXPIRY: ["is_owner"],
2949 # Any authenticated role that could hold a reservation.
2950 RESERVATION_AIRCRAFT_GROUNDED: ["is_owner", "is_pilot", "is_maint"],
2951 PERSONAL_MINIMUMS_RECENCY: ["is_pilot"],
2952 }
2954 # Types that have a configurable days-ahead threshold
2955 HAS_THRESHOLD: ClassVar[set[str]] = {
2956 MAINTENANCE_DUE_SOON,
2957 INSURANCE_EXPIRING,
2958 ARC_EXPIRY,
2959 MEDICAL_EXPIRING,
2960 SEP_RATING_EXPIRING,
2961 DOCUMENT_EXPIRING,
2962 AIRWORTHINESS_REVIEW_DUE,
2963 RENTER_AUTHORIZATION_EXPIRY,
2964 }
2967class NotificationPreference(db.Model):
2968 """Per-user notification preference override within a tenant (level 1 of 3)."""
2970 __tablename__ = "notification_preferences"
2971 __table_args__ = (
2972 db.UniqueConstraint(
2973 "user_id", "tenant_id", "notification_type", name="uq_notif_pref"
2974 ),
2975 )
2977 id = db.Column(db.Integer, primary_key=True)
2978 user_id = db.Column(
2979 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
2980 )
2981 tenant_id = db.Column(
2982 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
2983 )
2984 notification_type = db.Column(db.String(64), nullable=False)
2985 enabled = db.Column(db.Boolean, nullable=False)
2986 threshold_days = db.Column(db.Integer, nullable=True)
2987 updated_at = db.Column(
2988 db.DateTime(timezone=True),
2989 nullable=False,
2990 default=lambda: datetime.now(UTC),
2991 onupdate=lambda: datetime.now(UTC),
2992 )
2994 user = db.relationship("User")
2995 tenant = db.relationship("Tenant")
2998class TenantNotificationDefault(db.Model):
2999 """Per-tenant override of system notification defaults (level 2 of 3)."""
3001 __tablename__ = "tenant_notification_defaults"
3002 __table_args__ = (
3003 db.UniqueConstraint(
3004 "tenant_id", "notification_type", name="uq_tenant_notif_default"
3005 ),
3006 )
3008 id = db.Column(db.Integer, primary_key=True)
3009 tenant_id = db.Column(
3010 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3011 )
3012 notification_type = db.Column(db.String(64), nullable=False)
3013 enabled = db.Column(db.Boolean, nullable=False)
3014 threshold_days = db.Column(db.Integer, nullable=True)
3015 updated_at = db.Column(
3016 db.DateTime(timezone=True),
3017 nullable=False,
3018 default=lambda: datetime.now(UTC),
3019 onupdate=lambda: datetime.now(UTC),
3020 )
3022 tenant = db.relationship("Tenant")
3025class NotificationSendLog(db.Model):
3026 """Records that a given (user, notification instance) was emailed on a
3027 given calendar day -- makes run_daily_checks() idempotent within a day,
3028 so a server restart mid-day (or a second manual run) does not resend
3029 everything already sent earlier. subject_ref identifies the specific
3030 instance a notification is about (e.g. "aircraft:12", "document:34"),
3031 distinct from notification_type which identifies the kind of alert."""
3033 __tablename__ = "notification_send_log"
3034 __table_args__ = (
3035 db.UniqueConstraint(
3036 "user_id",
3037 "tenant_id",
3038 "notification_type",
3039 "subject_ref",
3040 "sent_date",
3041 name="uq_notif_send_log",
3042 ),
3043 )
3045 id = db.Column(db.Integer, primary_key=True)
3046 user_id = db.Column(
3047 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3048 )
3049 tenant_id = db.Column(
3050 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3051 )
3052 notification_type = db.Column(db.String(64), nullable=False)
3053 subject_ref = db.Column(db.String(128), nullable=False)
3054 sent_date = db.Column(db.Date, nullable=False)
3055 created_at = db.Column(
3056 db.DateTime(timezone=True),
3057 nullable=False,
3058 default=lambda: datetime.now(UTC),
3059 )
3061 user = db.relationship("User")
3062 tenant = db.relationship("Tenant")
3065class NotificationSnooze(db.Model):
3066 """Per-recipient, per-instance reminder snooze, keyed the same way as
3067 NotificationSendLog (notification_type + subject_ref). snoozed_value is
3068 NULL until the user follows the one-click email link and confirms;
3069 once set, it is compared against the live deadline value on every
3070 subsequent check -- a match suppresses the email, a mismatch means the
3071 underlying deadline changed (e.g. a renewed document was uploaded) and
3072 the snooze is treated as stale/cleared automatically."""
3074 __tablename__ = "notification_snoozes"
3075 __table_args__ = (
3076 db.UniqueConstraint(
3077 "user_id",
3078 "tenant_id",
3079 "notification_type",
3080 "subject_ref",
3081 name="uq_notif_snooze",
3082 ),
3083 )
3085 id = db.Column(db.Integer, primary_key=True)
3086 user_id = db.Column(
3087 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3088 )
3089 tenant_id = db.Column(
3090 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3091 )
3092 notification_type = db.Column(db.String(64), nullable=False)
3093 subject_ref = db.Column(db.String(128), nullable=False)
3094 token = db.Column(
3095 db.String(64),
3096 unique=True,
3097 nullable=False,
3098 default=lambda: secrets.token_urlsafe(32),
3099 )
3100 label = db.Column(db.String(256), nullable=False)
3101 current_value = db.Column(db.String(64), nullable=False)
3102 snoozed_value = db.Column(db.String(64), nullable=True)
3103 snoozed_at = db.Column(db.DateTime(timezone=True), nullable=True)
3104 created_at = db.Column(
3105 db.DateTime(timezone=True),
3106 nullable=False,
3107 default=lambda: datetime.now(UTC),
3108 )
3110 user = db.relationship("User")
3111 tenant = db.relationship("Tenant")
3113 @property
3114 def is_active(self) -> bool:
3115 return (
3116 self.snoozed_value is not None and self.snoozed_value == self.current_value
3117 )
3120class BillingAccountKind:
3121 """Shared billing core (Phases 37/39/41) — see docs/billing_service_design.md."""
3123 RENTER = "renter" # Phase 37 — scoped to the tenant (all aircraft)
3124 CO_OWNER = "co_owner" # Phase 39 — scoped to one aircraft
3125 MEMBER = "member" # Phase 41 — scoped to the tenant
3127 ALL: ClassVar[set[str]] = {RENTER, CO_OWNER, MEMBER}
3130class BillingAccount(db.Model):
3131 """One row per (tenant, user, scope). Created lazily by BillingService —
3132 there is no UI to create an account directly."""
3134 __tablename__ = "billing_accounts"
3135 __table_args__ = (
3136 # A plain UniqueConstraint on (tenant_id, user_id, kind, aircraft_id)
3137 # would NOT prevent duplicate renter/member accounts: aircraft_id is
3138 # NULL for those (tenant-scoped, not aircraft-scoped) kinds, and SQL
3139 # unique constraints treat NULL as distinct from every other NULL.
3140 # Two partial unique indexes close that gap: one for aircraft-scoped
3141 # (co_owner) rows, one for tenant-scoped (renter/member) rows.
3142 db.Index(
3143 "uq_billing_account_scope_aircraft",
3144 "tenant_id",
3145 "user_id",
3146 "kind",
3147 "aircraft_id",
3148 unique=True,
3149 sqlite_where=db.text("aircraft_id IS NOT NULL"),
3150 postgresql_where=db.text("aircraft_id IS NOT NULL"),
3151 ),
3152 db.Index(
3153 "uq_billing_account_scope_fleet",
3154 "tenant_id",
3155 "user_id",
3156 "kind",
3157 unique=True,
3158 sqlite_where=db.text("aircraft_id IS NULL"),
3159 postgresql_where=db.text("aircraft_id IS NULL"),
3160 ),
3161 db.Index("ix_billing_accounts_tenant_id", "tenant_id"),
3162 )
3164 id = db.Column(db.Integer, primary_key=True)
3165 tenant_id = db.Column(
3166 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False
3167 )
3168 user_id = db.Column(
3169 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3170 )
3171 aircraft_id = db.Column(
3172 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True
3173 ) # co_owner only
3174 kind = db.Column(db.String(16), nullable=False) # BillingAccountKind
3175 currency = db.Column(db.String(4), nullable=False, default="EUR")
3176 created_at = db.Column(
3177 db.DateTime(timezone=True),
3178 nullable=False,
3179 default=lambda: datetime.now(UTC),
3180 )
3182 tenant = db.relationship("Tenant")
3183 user = db.relationship("User", foreign_keys=[user_id])
3184 aircraft = db.relationship("Aircraft")
3185 entries = db.relationship(
3186 "LedgerEntry", back_populates="account", cascade="all, delete-orphan"
3187 )
3190class LedgerEntryType:
3191 """Append-only ledger entry types. No update/delete route may ever exist
3192 for LedgerEntry — corrections are posted as reversal entries."""
3194 CHARGE = "charge" # money the account holder owes (positive amount)
3195 PAYMENT = "payment" # money received from the holder (negative amount)
3196 CREDIT = "credit" # reduction of debt, e.g. fuel reimbursement (negative)
3197 ADJUSTMENT = "adjustment" # manual correction, either sign, requires note
3198 OPENING = "opening" # opening balance / co-owner buy-in
3200 ALL: ClassVar[set[str]] = {CHARGE, PAYMENT, CREDIT, ADJUSTMENT, OPENING}
3203class LedgerEntry(db.Model):
3204 """Append-only. Sign convention: positive amount = the holder owes more;
3205 negative = the holder owes less. balance = sum(amount)."""
3207 __tablename__ = "ledger_entries"
3208 __table_args__ = (db.Index("ix_ledger_entries_account_id", "account_id"),)
3210 id = db.Column(db.Integer, primary_key=True)
3211 account_id = db.Column(
3212 db.Integer,
3213 db.ForeignKey("billing_accounts.id", ondelete="CASCADE"),
3214 nullable=False,
3215 )
3216 entry_type = db.Column(db.String(16), nullable=False) # LedgerEntryType
3217 amount = db.Column(db.Numeric(10, 2), nullable=False) # signed
3218 description = db.Column(db.String(255), nullable=False)
3219 entry_date = db.Column(db.Date, nullable=False) # business date, not created_at
3220 # Link back to the domain object that produced the entry, for drill-down:
3221 source_type = db.Column(db.String(32), nullable=True) # e.g. "rental_charge"
3222 source_id = db.Column(db.Integer, nullable=True)
3223 # SET NULL, not RESTRICT: entries are never deleted in the app (append-only,
3224 # no delete route) so this only matters for admin-level DB surgery / test
3225 # cleanup — a self-referential RESTRICT here blocks a bulk DELETE FROM
3226 # ledger_entries entirely, since SQLite can't order same-table FK checks.
3227 reverses_id = db.Column(
3228 db.Integer,
3229 db.ForeignKey("ledger_entries.id", ondelete="SET NULL"),
3230 nullable=True,
3231 )
3232 created_by_id = db.Column(
3233 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
3234 )
3235 created_at = db.Column(
3236 db.DateTime(timezone=True),
3237 nullable=False,
3238 default=lambda: datetime.now(UTC),
3239 )
3241 account = db.relationship("BillingAccount", back_populates="entries")
3242 reverses = db.relationship(
3243 "LedgerEntry", foreign_keys=[reverses_id], remote_side="LedgerEntry.id"
3244 )
3245 created_by = db.relationship("User", foreign_keys=[created_by_id])
3248# ── Phase 39: Shared Ownership ────────────────────────────────────────────────
3251class AircraftOwner(db.Model):
3252 """A co-owner of one aircraft. share_pct values for one aircraft always
3253 sum to exactly 100.00 (enforced in the manage-owners route — rows are
3254 only ever written through that form, which replaces the full owner set
3255 for an aircraft atomically). Share percentage is financial only — each
3256 co-owner always has exactly one vote regardless of share size, so no
3257 voting-weight column exists."""
3259 __tablename__ = "aircraft_owners"
3260 __table_args__ = (
3261 db.UniqueConstraint("aircraft_id", "user_id", name="uq_aircraft_owner"),
3262 db.Index("ix_aircraft_owners_aircraft_id", "aircraft_id"),
3263 )
3265 id = db.Column(db.Integer, primary_key=True)
3266 aircraft_id = db.Column(
3267 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3268 )
3269 user_id = db.Column(
3270 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3271 )
3272 share_pct = db.Column(db.Numeric(5, 2), nullable=False) # 0.01 - 100.00
3273 buy_in_amount = db.Column(db.Numeric(10, 2), nullable=False, default=0)
3274 created_at = db.Column(
3275 db.DateTime(timezone=True),
3276 nullable=False,
3277 default=lambda: datetime.now(UTC),
3278 )
3280 aircraft = db.relationship("Aircraft", back_populates="owners")
3281 user = db.relationship("User")
3284class CoOwnerValuationSnapshot(db.Model):
3285 """Phase 39e: immutable point-in-time capital value per co-owner. No
3286 update or delete route may ever exist for this table — reproducible by
3287 construction, since the ledger it summarises is itself append-only."""
3289 __tablename__ = "co_owner_valuation_snapshots"
3290 __table_args__ = (db.Index("ix_covs_aircraft_id", "aircraft_id"),)
3292 id = db.Column(db.Integer, primary_key=True)
3293 aircraft_id = db.Column(
3294 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False
3295 )
3296 user_id = db.Column(
3297 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
3298 )
3299 valuation_date = db.Column(db.Date, nullable=False)
3300 share_pct = db.Column(db.Numeric(5, 2), nullable=False) # copied at snapshot time
3301 capital_balance = db.Column(
3302 db.Numeric(10, 2), nullable=False
3303 ) # -balance(account, as_of=valuation_date)
3304 note = db.Column(db.String(255), nullable=True)
3305 created_by_id = db.Column(
3306 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True
3307 )
3308 created_at = db.Column(
3309 db.DateTime(timezone=True),
3310 nullable=False,
3311 default=lambda: datetime.now(UTC),
3312 )
3314 aircraft = db.relationship("Aircraft")
3315 user = db.relationship("User", foreign_keys=[user_id])
3316 created_by = db.relationship("User", foreign_keys=[created_by_id])