Coverage for app/models.py: 100%

1import enum

2import secrets

3from datetime import datetime, timezone

5from flask_sqlalchemy import SQLAlchemy # pyright: ignore[reportMissingImports]

7db = SQLAlchemy()

10class Role(str, enum.Enum):

11 ADMIN = "admin"

12 OWNER = "owner"

13 PILOT = "pilot" # Pilot/Renter: log flights + view own; no config/cost edits

14 MAINTENANCE = (

15 "maintenance" # Maintenance: view+update maintenance; no flights/aircraft edits

16 )

17 VIEWER = "viewer" # Read-only across tenant

18 STUDENT = "student" # Student pilot — requires instructor sign-off on solo entries

19 INSTRUCTOR = "instructor" # Flight instructor — can countersign student entries

22class OperatingModel(str, enum.Enum):

23 SOLE_PILOT = "sole_pilot"

24 SOLE_OPERATOR = "sole_operator"

25 SHARED_OWNERSHIP = "shared_ownership"

26 FLIGHT_CLUB = "flight_club"

27 FLIGHT_SCHOOL = "flight_school"

30class PermissionBit:

31 """Bitmask constants for UserAircraftAccess.permissions_mask."""

33 VIEW_AIRCRAFT = 0x01

34 EDIT_AIRCRAFT = 0x02

35 READ_MAINT_FULL = 0x04

36 READ_MAINT_LIMITED = 0x08

37 WRITE_MAINTENANCE = 0x10

38 EDIT_COMPONENTS = 0x20

39 WRITE_LOGBOOK = 0x40

40 RESERVE_AIRCRAFT = 0x80

41 ALL = 0xFF

43 # Default masks per role (used when no explicit per-aircraft row exists)

44 ROLE_DEFAULTS: "dict[str, int]" = {

45 "admin": ALL,

46 "owner": ALL,

47 "pilot": VIEW_AIRCRAFT | READ_MAINT_LIMITED | WRITE_LOGBOOK | RESERVE_AIRCRAFT,

48 "student": VIEW_AIRCRAFT | READ_MAINT_LIMITED,

49 "instructor": VIEW_AIRCRAFT

50 | READ_MAINT_FULL

51 | WRITE_LOGBOOK

52 | RESERVE_AIRCRAFT,

53 "maintenance": VIEW_AIRCRAFT

54 | EDIT_AIRCRAFT

55 | READ_MAINT_FULL

56 | WRITE_MAINTENANCE

57 | EDIT_COMPONENTS,

58 "viewer": VIEW_AIRCRAFT | READ_MAINT_FULL,

59 }

62class Tenant(db.Model):

63 __tablename__ = "tenants"

65 id = db.Column(db.Integer, primary_key=True)

66 name = db.Column(db.String(128), nullable=False)

67 slug = db.Column(db.String(64), nullable=True, unique=True)

68 is_active = db.Column(db.Boolean, nullable=False, default=True)

69 require_totp = db.Column(db.Boolean, nullable=False, default=False)

70 created_at = db.Column(

71 db.DateTime(timezone=True),

72 nullable=False,

73 default=lambda: datetime.now(timezone.utc),

74 )

76 users = db.relationship(

77 "TenantUser", back_populates="tenant", cascade="all, delete-orphan"

78 )

79 aircraft = db.relationship(

80 "Aircraft", back_populates="tenant", cascade="all, delete-orphan"

81 )

84class User(db.Model):

85 __tablename__ = "users"

87 id = db.Column(db.Integer, primary_key=True)

88 email = db.Column(db.String(255), unique=True, nullable=False)

89 password_hash = db.Column(db.String(255), nullable=False)

90 totp_secret = db.Column(db.String(64), nullable=True, default=None)

91 is_active = db.Column(db.Boolean, nullable=False, default=True)

92 name = db.Column(db.String(128), nullable=True)

93 language = db.Column(db.String(8), nullable=True, default="en")

94 theme = db.Column(db.String(8), nullable=True, default=None)

95 # Phase 23: capability flags — orthogonal to role; allow cross-role flows

96 is_pilot = db.Column(db.Boolean, nullable=False, default=False)

97 is_maintenance = db.Column(db.Boolean, nullable=False, default=False)

98 view_only = db.Column(db.Boolean, nullable=False, default=False)

99 # Phase 29: instance-level super admin — set on the very first user created

100 is_instance_admin = db.Column(db.Boolean, nullable=False, default=False)

101 created_at = db.Column(

102 db.DateTime(timezone=True),

103 nullable=False,

104 default=lambda: datetime.now(timezone.utc),

105 )

106

107 tenants = db.relationship(

108 "TenantUser", back_populates="user", cascade="all, delete-orphan"

109 )

110

111 @property

112 def display_name(self) -> str:

113 return (self.name or "").strip() or self.email.split("@")[0]

114

115

116class TenantUser(db.Model):

117 __tablename__ = "tenant_users"

118

119 user_id = db.Column(

120 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True

121 )

122 tenant_id = db.Column(

123 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True

124 )

125 role = db.Column(db.Enum(Role), nullable=False, default=Role.OWNER)

126

127 user = db.relationship("User", back_populates="tenants")

128 tenant = db.relationship("Tenant", back_populates="users")

129

130

131class UserAircraftAccess(db.Model):

132 """Grants a non-owner/admin user explicit access to a specific aircraft."""

133

134 __tablename__ = "user_aircraft_access"

135

136 user_id = db.Column(

137 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True

138 )

139 aircraft_id = db.Column(

140 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True

141 )

142 # Phase 23: optional override mask; when NULL the role's default mask applies

143 permissions_mask = db.Column(db.Integer, nullable=True)

144

145

146class UserAllAircraftAccess(db.Model):

147 """Grants a user access to every aircraft in a tenant (past and future).

148

149 Admin users bypass access checks entirely and never need this row.

150 For non-admin users, this row grants access to every aircraft in the

151 tenant using the supplied permissions_mask (or the role default when NULL).

152 """

153

154 __tablename__ = "user_all_aircraft_access"

155

156 user_id = db.Column(

157 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True

158 )

159 tenant_id = db.Column(

160 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), primary_key=True

161 )

162 permissions_mask = db.Column(db.Integer, nullable=True)

163

164

165class UserInvitation(db.Model):

166 """Time-limited invitation for a new user to join a tenant."""

167

168 __tablename__ = "user_invitations"

169

170 id = db.Column(db.Integer, primary_key=True)

171 token = db.Column(

172 db.String(64),

173 unique=True,

174 nullable=False,

175 default=lambda: secrets.token_urlsafe(32),

176 )

177 tenant_id = db.Column(

178 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False

179 )

180 invited_by_user_id = db.Column(

181 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True

182 )

183 email = db.Column(db.String(255), nullable=True)

184 display_name = db.Column(db.String(128), nullable=True)

185 role = db.Column(db.Enum(Role), nullable=False, default=Role.PILOT)

186 aircraft_ids = db.Column(db.JSON, nullable=True)

187 expires_at = db.Column(db.DateTime(timezone=True), nullable=False)

188 accepted_at = db.Column(db.DateTime(timezone=True), nullable=True)

189 created_at = db.Column(

190 db.DateTime(timezone=True),

191 nullable=False,

192 default=lambda: datetime.now(timezone.utc),

193 )

194

195 tenant = db.relationship("Tenant")

196 invited_by = db.relationship("User", foreign_keys=[invited_by_user_id])

197

198 @property

199 def is_expired(self) -> bool:

200 exp = self.expires_at

201 # SQLite returns naive datetimes; compare with naive UTC in that case

202 if exp.tzinfo is None:

203 return datetime.now(timezone.utc).replace(tzinfo=None) > exp

204 return datetime.now(timezone.utc) > exp

205

206 @property

207 def is_accepted(self) -> bool:

208 return self.accepted_at is not None

209

210

211# ── Phase 29: Password Reset Token ───────────────────────────────────────────

212

213

214class PasswordResetToken(db.Model):

215 """One-time password reset token generated by the instance admin for a tenant owner."""

216

217 __tablename__ = "password_reset_tokens"

218

219 id = db.Column(db.Integer, primary_key=True)

220 token = db.Column(

221 db.String(64),

222 unique=True,

223 nullable=False,

224 default=lambda: secrets.token_urlsafe(32),

225 )

226 user_id = db.Column(

227 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False

228 )

229 generated_by_user_id = db.Column(

230 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True

231 )

232 created_at = db.Column(

233 db.DateTime(timezone=True),

234 nullable=False,

235 default=lambda: datetime.now(timezone.utc),

236 )

237 expires_at = db.Column(db.DateTime(timezone=True), nullable=False)

238 used_at = db.Column(db.DateTime(timezone=True), nullable=True)

239

240 user = db.relationship("User", foreign_keys=[user_id])

241 generated_by = db.relationship("User", foreign_keys=[generated_by_user_id])

242

243 @property

244 def is_expired(self) -> bool:

245 exp = self.expires_at

246 if exp.tzinfo is None:

247 return datetime.now(timezone.utc).replace(tzinfo=None) > exp

248 return datetime.now(timezone.utc) > exp

249

250 @property

251 def is_used(self) -> bool:

252 return self.used_at is not None

253

254

255# ── Phase 26: Tenant Profile ─────────────────────────────────────────────────

256

257

258class TenantProfile(db.Model):

259 """Instance-level profile collected during the onboarding wizard."""

260

261 __tablename__ = "tenant_profiles"

262

263 id = db.Column(db.Integer, primary_key=True)

264 tenant_id = db.Column(

265 db.Integer,

266 db.ForeignKey("tenants.id", ondelete="CASCADE"),

267 nullable=False,

268 unique=True,

269 )

270 operating_model = db.Column(db.Enum(OperatingModel), nullable=True)

271 # 0 → logbook-only (no aircraft UI)

272 # 1 → single-aircraft (hides fleet-level widgets)

273 # N → show "Add aircraft" CTA until N aircraft exist

274 planned_aircraft_count = db.Column(db.Integer, nullable=True)

275 allows_rental = db.Column(db.Boolean, nullable=False, default=False)

276 club_name = db.Column(db.String(128), nullable=True)

277 school_name = db.Column(db.String(128), nullable=True)

278 organisation_name = db.Column(db.String(128), nullable=True)

279 setup_complete = db.Column(db.Boolean, nullable=False, default=False)

280 # Phase 34: optional email subject prefix, e.g. "[MyClub]"

281 email_subject_prefix = db.Column(db.String(64), nullable=True)

282

283 tenant = db.relationship("Tenant", backref=db.backref("profile", uselist=False))

284

285

286# ── Phase 1: Aircraft & Component Models ──────────────────────────────────────

287

288

289# Application-level component type constants.

290# Stored as plain strings in the DB so new types never require a migration.

291class ComponentType:

292 AIRFRAME = "airframe"

293 ENGINE = "engine"

294 PROPELLER = "propeller"

295 AVIONICS = "avionics"

296 OTHER = "other"

297

298 ALL = {AIRFRAME, ENGINE, PROPELLER, AVIONICS, OTHER}

299

300

301class Aircraft(db.Model):

302 __tablename__ = "aircraft"

303

304 id = db.Column(db.Integer, primary_key=True)

305 tenant_id = db.Column(

306 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False

307 )

308 registration = db.Column(db.String(16), nullable=False)

309 make = db.Column(db.String(64), nullable=False)

310 model = db.Column(db.String(64), nullable=False)

311 year = db.Column(db.Integer, nullable=True)

312 has_flight_counter = db.Column(db.Boolean, nullable=False, default=True)

313 flight_counter_offset = db.Column(db.Numeric(3, 1), nullable=False, default=0.3)

314 fuel_flow = db.Column(

315 db.Numeric(6, 2), nullable=True

316 ) # typical fuel consumption in L/h

317 fuel_type = db.Column(

318 db.String(8), nullable=False, default="avgas"

319 ) # "avgas" | "jet_a1"

320 insurance_expiry = db.Column(db.Date, nullable=True)

321 # Phase 30: GPS import time rounding preference

322 logbook_time_precision = db.Column(

323 db.String(16), nullable=False, default="tenth_hour"

324 ) # "tenth_hour" | "minute"

325 created_at = db.Column(

326 db.DateTime(timezone=True),

327 nullable=False,

328 default=lambda: datetime.now(timezone.utc),

329 )

330

331 tenant = db.relationship("Tenant", back_populates="aircraft")

332 components = db.relationship(

333 "Component", back_populates="aircraft", cascade="all, delete-orphan"

334 )

335 flights = db.relationship(

336 "FlightEntry", back_populates="aircraft", cascade="all, delete-orphan"

337 )

338 maintenance_triggers = db.relationship(

339 "MaintenanceTrigger", back_populates="aircraft", cascade="all, delete-orphan"

340 )

341 expenses = db.relationship(

342 "Expense", back_populates="aircraft", cascade="all, delete-orphan"

343 )

344 documents = db.relationship(

345 "Document",

346 back_populates="aircraft",

347 cascade="all, delete-orphan",

348 single_parent=True,

349 foreign_keys="Document.aircraft_id",

350 primaryjoin="Document.aircraft_id == Aircraft.id",

351 )

352 share_tokens = db.relationship(

353 "ShareToken",

354 back_populates="aircraft",

355 cascade="all, delete-orphan",

356 )

357 snags = db.relationship(

358 "Snag",

359 back_populates="aircraft",

360 cascade="all, delete-orphan",

361 )

362 wb_config = db.relationship(

363 "WeightBalanceConfig",

364 back_populates="aircraft",

365 cascade="all, delete-orphan",

366 uselist=False,

367 )

368 reservations = db.relationship(

369 "Reservation",

370 back_populates="aircraft",

371 cascade="all, delete-orphan",

372 )

373 booking_settings = db.relationship(

374 "AircraftBookingSettings",

375 back_populates="aircraft",

376 cascade="all, delete-orphan",

377 uselist=False,

378 )

379 photos = db.relationship(

380 "AircraftPhoto",

381 back_populates="aircraft",

382 cascade="all, delete-orphan",

383 order_by="AircraftPhoto.sort_order",

384 )

385 airworthiness_statuses = db.relationship(

386 "AirworthinessDocumentStatus",

387 back_populates="aircraft",

388 cascade="all, delete-orphan",

389 )

390 installed_stcs = db.relationship(

391 "InstalledSTC",

392 back_populates="aircraft",

393 cascade="all, delete-orphan",

394 )

395

396 @property

397 def cover_photo(self) -> "AircraftPhoto | None":

398 return self.photos[0] if self.photos else None

399

400 @property

401 def total_engine_hours(self):

402 """Current engine hours — the highest engine_time_counter_end across all flight entries."""

403 vals = [

404 float(f.engine_time_counter_end)

405 for f in self.flights

406 if f.engine_time_counter_end is not None

407 ]

408 return max(vals) if vals else None

409

410 @property

411 def total_flight_hours(self):

412 """Current flight hours — the highest flight_time_counter_end across all flight entries."""

413 vals = [

414 float(f.flight_time_counter_end)

415 for f in self.flights

416 if f.flight_time_counter_end is not None

417 ]

418 return max(vals) if vals else None

419

420 @property

421 def is_grounded(self) -> bool:

422 """True when any unresolved grounding snag exists, or insurance has expired."""

423 from datetime import date as _date

424

425 if self.insurance_expiry is not None and self.insurance_expiry < _date.today():

426 return True

427 return any(s.is_grounding and s.is_open for s in self.snags)

428

429 @property

430 def insurance_status(self) -> str:

431 """Return 'expired', 'expiring_soon' (≤30 days), or 'ok'."""

432 from datetime import date as _date

433

434 if self.insurance_expiry is None:

435 return "ok"

436 delta = (self.insurance_expiry - _date.today()).days

437 if delta < 0:

438 return "expired"

439 if delta <= 30:

440 return "expiring_soon"

441 return "ok"

442

443

444class AircraftPhoto(db.Model):

445 __tablename__ = "aircraft_photos"

446 __table_args__ = (db.Index("ix_aircraft_photos_aircraft_id", "aircraft_id"),)

447

448 id = db.Column(db.Integer, primary_key=True)

449 aircraft_id = db.Column(

450 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

451 )

452 filename = db.Column(db.String(512), nullable=False)

453 original_filename = db.Column(db.String(256), nullable=False)

454 sort_order = db.Column(db.Integer, nullable=False, default=1)

455 uploaded_at = db.Column(

456 db.DateTime(timezone=True),

457 nullable=False,

458 default=lambda: datetime.now(timezone.utc),

459 )

460 uploaded_by_user_id = db.Column(

461 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True

462 )

463

464 aircraft = db.relationship("Aircraft", back_populates="photos")

465

466

467class Component(db.Model):

468 """

469 A generic aircraft component (engine, propeller, avionics, …).

470

471 Common fields live as columns; type-specific attributes go in `extras` (JSON).

472 `removed_at = NULL` means the component is currently installed.

473 `position` disambiguates multiple components of the same type, e.g. "left" / "right"

474 for a twin-engine aircraft.

475 """

476

477 __tablename__ = "components"

478

479 id = db.Column(db.Integer, primary_key=True)

480 aircraft_id = db.Column(

481 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

482 )

483 # Plain string, validated at application layer — no DB ENUM so new types

484 # never require a schema migration.

485 type = db.Column(db.String(32), nullable=False)

486 # Optional slot label: "left", "right", "1", "2", "center", …

487 position = db.Column(db.String(32), nullable=True)

488

489 make = db.Column(db.String(64), nullable=False)

490 model = db.Column(db.String(64), nullable=False)

491 serial_number = db.Column(db.String(64), nullable=True)

492 # Hours on this component when it was installed on this aircraft

493 time_at_install = db.Column(db.Numeric(8, 1), nullable=True)

494

495 installed_at = db.Column(db.Date, nullable=True)

496 removed_at = db.Column(db.Date, nullable=True) # NULL = currently installed

497

498 # Type-specific attributes (blade count, TBO, firmware version, …)

499 extras = db.Column(db.JSON, nullable=True)

500

501 created_at = db.Column(

502 db.DateTime(timezone=True),

503 nullable=False,

504 default=lambda: datetime.now(timezone.utc),

505 )

506

507 aircraft = db.relationship("Aircraft", back_populates="components")

508 documents = db.relationship(

509 "Document",

510 back_populates="component",

511 cascade="all, delete-orphan",

512 )

513 easa_source_nodes = db.relationship(

514 "EASASourceNode",

515 back_populates="component",

516 cascade="all, delete-orphan",

517 )

518 airworthiness_documents = db.relationship(

519 "AirworthinessDocument",

520 back_populates="component",

521 cascade="all, delete-orphan",

522 foreign_keys="AirworthinessDocument.component_id",

523 )

524

525

526# ── Phase 3: Flight Logging ───────────────────────────────────────────────────

527

528

529class CrewRole:

530 PIC = "PIC"

531 IP = "IP"

532 SP = "SP"

533 COPILOT = "COPILOT"

534 STUDENT = "STUDENT"

535 ALL = [PIC, IP, SP, COPILOT, STUDENT]

536 LABELS = {

537 PIC: "PIC",

538 IP: "Instructor",

539 SP: "Safety Pilot",

540 COPILOT: "Co-Pilot",

541 STUDENT: "Student",

542 }

543

544

545# ── Phase 31b: GPS Track (standalone, linkable from FlightEntry or PilotLogbookEntry) ──

546

547

548class GpsTrack(db.Model):

549 __tablename__ = "gps_tracks"

550

551 id = db.Column(db.Integer, primary_key=True)

552 source_filename = db.Column(db.String(256), nullable=True)

553 device_id = db.Column(db.String(64), nullable=True, index=True)

554 block_off_utc = db.Column(db.DateTime(timezone=True), nullable=True)

555 block_on_utc = db.Column(db.DateTime(timezone=True), nullable=True)

556 departure_icao = db.Column(db.String(4), nullable=True)

557 arrival_icao = db.Column(db.String(4), nullable=True)

558 geojson = db.Column(db.JSON, nullable=True)

559 created_at = db.Column(

560 db.DateTime(timezone=True),

561 nullable=False,

562 default=lambda: datetime.now(timezone.utc),

563 )

564

565

566class FlightEntry(db.Model):

567 __tablename__ = "flight_entries"

568

569 id = db.Column(db.Integer, primary_key=True)

570 aircraft_id = db.Column(

571 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

572 )

573 date = db.Column(db.Date, nullable=False)

574 departure_icao = db.Column(db.String(4), nullable=False)

575 arrival_icao = db.Column(db.String(4), nullable=False)

576 departure_time = db.Column(db.Time, nullable=True)

577 arrival_time = db.Column(db.Time, nullable=True)

578 flight_time = db.Column(db.Numeric(4, 1), nullable=True)

579 nature_of_flight = db.Column(db.String(100), nullable=True)

580 passenger_count = db.Column(db.Integer, nullable=True)

581 landing_count = db.Column(db.Integer, nullable=True)

582 flight_time_counter_start = db.Column(db.Numeric(8, 1), nullable=True)

583 flight_time_counter_end = db.Column(db.Numeric(8, 1), nullable=True)

584 notes = db.Column(db.Text, nullable=True)

585 engine_time_counter_start = db.Column(db.Numeric(8, 1), nullable=True)

586 engine_time_counter_end = db.Column(db.Numeric(8, 1), nullable=True)

587 flight_counter_photo = db.Column(db.String(255), nullable=True)

588 engine_counter_photo = db.Column(db.String(255), nullable=True)

589 fuel_event = db.Column(db.String(8), nullable=True) # 'before' | 'after' | None

590 fuel_added_qty = db.Column(db.Numeric(8, 2), nullable=True)

591 fuel_added_unit = db.Column(db.String(8), nullable=True)

592 fuel_remaining_qty = db.Column(db.Numeric(8, 2), nullable=True)

593 fuel_photo = db.Column(db.String(255), nullable=True)

594 created_at = db.Column(

595 db.DateTime(timezone=True),

596 nullable=False,

597 default=lambda: datetime.now(timezone.utc),

598 )

599 # Phase 30: GPS import

600 source = db.Column(db.String(32), nullable=True) # "gps_import" | None (manual)

601 gps_import_batch_id = db.Column(

602 db.Integer,

603 db.ForeignKey("aircraft_gps_import_batches.id", ondelete="SET NULL"),

604 nullable=True,

605 )

606 block_off_utc = db.Column(db.DateTime(timezone=True), nullable=True)

607 block_on_utc = db.Column(db.DateTime(timezone=True), nullable=True)

608 gps_track_id = db.Column(

609 db.Integer,

610 db.ForeignKey("gps_tracks.id", ondelete="SET NULL"),

611 nullable=True,

612 )

613

614 aircraft = db.relationship("Aircraft", back_populates="flights")

615 gps_track = db.relationship("GpsTrack", foreign_keys=[gps_track_id])

616 gps_import_batch = db.relationship(

617 "AircraftGpsImportBatch", foreign_keys=[gps_import_batch_id]

618 )

619 crew = db.relationship(

620 "FlightCrew",

621 back_populates="flight",

622 cascade="all, delete-orphan",

623 order_by="FlightCrew.sort_order",

624 )

625 expenses = db.relationship("Expense", back_populates="flight_entry")

626 documents = db.relationship(

627 "Document",

628 back_populates="flight_entry",

629 cascade="all, delete-orphan",

630 )

631

632

633class FlightCrew(db.Model):

634 __tablename__ = "flight_crew"

635

636 id = db.Column(db.Integer, primary_key=True)

637 flight_id = db.Column(

638 db.Integer,

639 db.ForeignKey("flight_entries.id", ondelete="CASCADE"),

640 nullable=False,

641 )

642 user_id = db.Column(

643 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True

644 )

645 name = db.Column(db.String(128), nullable=False)

646 role = db.Column(db.String(16), nullable=False)

647 sort_order = db.Column(db.Integer, nullable=False, default=0)

648

649 flight = db.relationship("FlightEntry", back_populates="crew")

650 user = db.relationship("User")

651

652

653# ── Phase 17: Pilot Profile & Manual Logbook ─────────────────────────────────

654

655

656class PilotProfile(db.Model):

657 __tablename__ = "pilot_profiles"

658

659 id = db.Column(db.Integer, primary_key=True)

660 user_id = db.Column(

661 db.Integer,

662 db.ForeignKey("users.id", ondelete="CASCADE"),

663 nullable=False,

664 unique=True,

665 )

666 license_number = db.Column(db.String(64), nullable=True)

667 medical_expiry = db.Column(db.Date, nullable=True)

668 sep_expiry = db.Column(db.Date, nullable=True)

669 first_solo_date = db.Column(db.Date, nullable=True)

670 ppl_issue_date = db.Column(db.Date, nullable=True)

671

672 user = db.relationship("User")

673

674

675class PilotLogbookEntry(db.Model):

676 __tablename__ = "pilot_logbook_entries"

677

678 id = db.Column(db.Integer, primary_key=True)

679 pilot_user_id = db.Column(

680 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False

681 )

682 flight_id = db.Column(

683 db.Integer,

684 db.ForeignKey("flight_entries.id", ondelete="SET NULL"),

685 nullable=True,

686 )

687 date = db.Column(db.Date, nullable=False)

688 aircraft_type = db.Column(db.String(64), nullable=True)

689 aircraft_type_icao = db.Column(db.String(16), nullable=True)

690 aircraft_registration = db.Column(db.String(16), nullable=True)

691 departure_place = db.Column(db.String(64), nullable=True)

692 departure_time = db.Column(db.Time, nullable=True)

693 arrival_place = db.Column(db.String(64), nullable=True)

694 arrival_time = db.Column(db.Time, nullable=True)

695 pic_name = db.Column(db.String(128), nullable=True)

696 night_time = db.Column(db.Numeric(4, 1), nullable=True)

697 instrument_time = db.Column(db.Numeric(4, 1), nullable=True)

698 cross_country = db.Column(db.Numeric(4, 1), nullable=True)

699 landings_day = db.Column(db.Integer, nullable=True)

700 landings_night = db.Column(db.Integer, nullable=True)

701 single_pilot_se = db.Column(db.Numeric(4, 1), nullable=True)

702 single_pilot_me = db.Column(db.Numeric(4, 1), nullable=True)

703 multi_pilot = db.Column(db.Numeric(4, 1), nullable=True)

704 function_pic = db.Column(db.Numeric(4, 1), nullable=True)

705 function_copilot = db.Column(db.Numeric(4, 1), nullable=True)

706 function_dual = db.Column(db.Numeric(4, 1), nullable=True)

707 function_instructor = db.Column(db.Numeric(4, 1), nullable=True)

708 remarks = db.Column(db.Text, nullable=True)

709

710 source = db.Column(db.String(32), nullable=True) # "import" | "gps_import" | None

711 import_batch_id = db.Column(

712 db.Integer,

713 db.ForeignKey("logbook_import_batches.id", ondelete="SET NULL"),

714 nullable=True,

715 )

716 # Phase 30: GPS-derived pilot logbook entries link to an AircraftGpsImportBatch

717 gps_batch_id = db.Column(

718 db.Integer,

719 db.ForeignKey("aircraft_gps_import_batches.id", ondelete="SET NULL"),

720 nullable=True,

721 )

722 # Phase 31b: standalone GPS track linkable independently of aircraft log

723 gps_track_id = db.Column(

724 db.Integer,

725 db.ForeignKey("gps_tracks.id", ondelete="SET NULL"),

726 nullable=True,

727 )

728

729 pilot = db.relationship("User", foreign_keys=[pilot_user_id])

730 flight = db.relationship("FlightEntry")

731 import_batch = db.relationship("LogbookImportBatch", foreign_keys=[import_batch_id])

732 gps_batch = db.relationship("AircraftGpsImportBatch", foreign_keys=[gps_batch_id])

733 gps_track = db.relationship("GpsTrack", foreign_keys=[gps_track_id])

734

735 @property

736 def total_flight_time(self):

737 parts = [self.single_pilot_se, self.single_pilot_me, self.multi_pilot]

738 vals = [float(p) for p in parts if p is not None]

739 return round(sum(vals), 1) if vals else None

740

741

742# ── Phase 28: Pilot Logbook Import ───────────────────────────────────────────

743

744

745class LogbookImportMapping(db.Model):

746 __tablename__ = "logbook_import_mappings"

747

748 id = db.Column(db.Integer, primary_key=True)

749 pilot_user_id = db.Column(

750 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False

751 )

752 source_fingerprint = db.Column(db.String(64), nullable=False, index=True)

753 # JSON: {norm_col_key: target_field_or_"ignore"}

754 column_mapping = db.Column(db.Text, nullable=False)

755 # JSON list of norm_col_keys — stored for fuzzy matching future uploads

756 source_columns = db.Column(db.Text, nullable=False)

757 created_at = db.Column(db.DateTime(timezone=True), nullable=False)

758

759 pilot = db.relationship("User")

760

761

762class LogbookImportBatch(db.Model):

763 __tablename__ = "logbook_import_batches"

764

765 id = db.Column(db.Integer, primary_key=True)

766 pilot_user_id = db.Column(

767 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False

768 )

769 mapping_id = db.Column(

770 db.Integer,

771 db.ForeignKey("logbook_import_mappings.id", ondelete="SET NULL"),

772 nullable=True,

773 )

774 source_filename = db.Column(db.String(256), nullable=False)

775 imported_at = db.Column(db.DateTime(timezone=True), nullable=False)

776 row_count = db.Column(db.Integer, nullable=False, default=0)

777 subtotal_count = db.Column(db.Integer, nullable=False, default=0)

778 skipped_count = db.Column(db.Integer, nullable=False, default=0)

779 has_opening_balance = db.Column(db.Boolean, nullable=False, default=False)

780

781 pilot = db.relationship("User")

782 mapping = db.relationship("LogbookImportMapping")

783 entries = db.relationship(

784 "PilotLogbookEntry",

785 foreign_keys="PilotLogbookEntry.import_batch_id",

786 lazy="dynamic",

787 overlaps="import_batch",

788 )

789

790

791# ── Phase 30: Aircraft GPS Log Import ────────────────────────────────────────

792

793

794class AircraftGpsImportBatch(db.Model):

795 """Metadata for one GPS-import session (1+ files → 1+ FlightEntry records)."""

796

797 __tablename__ = "aircraft_gps_import_batches"

798

799 id = db.Column(db.Integer, primary_key=True)

800 aircraft_id = db.Column(

801 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

802 )

803 pilot_user_id = db.Column(

804 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True

805 )

806 # JSON list of original filenames, e.g. ["log_260518_EBNM.csv", "track.gpx"]

807 source_filenames = db.Column(db.JSON, nullable=False, default=list)

808 imported_at = db.Column(

809 db.DateTime(timezone=True),

810 nullable=False,

811 default=lambda: datetime.now(timezone.utc),

812 )

813 format_detected = db.Column(

814 db.String(16), nullable=False

815 ) # "gpx"|"kml"|"garmin_csv"|"mixed"

816 segments_found = db.Column(db.Integer, nullable=False, default=0)

817 segments_imported = db.Column(db.Integer, nullable=False, default=0)

818 # IDs of pre-existing FlightEntry rows that received a GPS track (not created).

819 linked_flight_entry_ids = db.Column(db.JSON, nullable=False, default=list)

820 # Pilot role selected during import: 'pic' | 'dual' | 'none'

821 pilot_role = db.Column(db.String(8), nullable=True)

822 # Set when the import is for an aircraft not in this instance (Phase 31).

823 other_aircraft_make_model = db.Column(db.String(128), nullable=True)

824 other_aircraft_registration = db.Column(db.String(16), nullable=True)

825

826 aircraft = db.relationship(

827 "Aircraft",

828 backref=db.backref("gps_import_batches", cascade="all, delete-orphan"),

829 )

830 pilot = db.relationship("User", foreign_keys=[pilot_user_id])

831 flight_entries = db.relationship(

832 "FlightEntry",

833 foreign_keys="FlightEntry.gps_import_batch_id",

834 lazy="dynamic",

835 overlaps="gps_import_batch",

836 )

837 pilot_logbook_entries = db.relationship(

838 "PilotLogbookEntry",

839 foreign_keys="PilotLogbookEntry.gps_batch_id",

840 lazy="dynamic",

841 overlaps="gps_batch",

842 )

843

844

845# ── Phase 4: Maintenance Tracking ────────────────────────────────────────────

846

847

848class TriggerType:

849 CALENDAR = "calendar" # due on a specific date

850 HOURS = "hours" # due at a specific hobbs reading

851 ALL = {CALENDAR, HOURS}

852

853

854class MaintenanceTrigger(db.Model):

855 __tablename__ = "maintenance_triggers"

856

857 id = db.Column(db.Integer, primary_key=True)

858 aircraft_id = db.Column(

859 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

860 )

861 name = db.Column(db.String(128), nullable=False)

862 trigger_type = db.Column(db.String(16), nullable=False) # TriggerType constant

863

864 # Calendar trigger fields

865 due_date = db.Column(db.Date, nullable=True)

866 interval_days = db.Column(db.Integer, nullable=True) # advance due_date on service

867

868 # Hours trigger fields

869 due_engine_hours = db.Column(db.Numeric(8, 1), nullable=True)

870 interval_hours = db.Column(

871 db.Numeric(8, 1), nullable=True

872 ) # advance due_engine_hours on service

873

874 notes = db.Column(db.Text, nullable=True)

875 created_at = db.Column(

876 db.DateTime(timezone=True),

877 nullable=False,

878 default=lambda: datetime.now(timezone.utc),

879 )

880

881 aircraft = db.relationship("Aircraft", back_populates="maintenance_triggers")

882 records = db.relationship(

883 "MaintenanceRecord",

884 back_populates="trigger",

885 cascade="all, delete-orphan",

886 order_by="MaintenanceRecord.performed_at.desc()",

887 )

888

889 def status(self, current_hobbs=None):

890 """Return 'overdue', 'due_soon', or 'ok'."""

891 from datetime import date as _date

892

893 if self.trigger_type == TriggerType.CALENDAR and self.due_date:

894 delta = (self.due_date - _date.today()).days

895 if delta < 0:

896 return "overdue"

897 if delta <= 30:

898 return "due_soon"

899 elif (

900 self.trigger_type == TriggerType.HOURS and self.due_engine_hours is not None

901 ):

902 if current_hobbs is None:

903 return "ok"

904 remaining = float(self.due_engine_hours) - float(current_hobbs)

905 if remaining <= 0:

906 return "overdue"

907 warn = float(self.interval_hours) * 0.1 if self.interval_hours else 10.0

908 if remaining <= max(warn, 5.0):

909 return "due_soon"

910 return "ok"

911

912 @property

913 def last_record(self):

914 return self.records[0] if self.records else None

915

916

917# ── Phase 6: Demo Mode ────────────────────────────────────────────────────────

918

919

920class DemoSlot(db.Model):

921 """One isolated visitor slot in demo mode. Each slot is its own tenant+user pair."""

922

923 __tablename__ = "demo_slots"

924

925 id = db.Column(db.Integer, primary_key=True) # slot number 1..N

926 display_id = db.Column(db.Integer, nullable=True) # random 1000-9999, shown in UI

927 tenant_id = db.Column(

928 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False

929 )

930 user_id = db.Column(

931 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False

932 )

933 renter_user_id = db.Column(

934 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True

935 )

936 maintenance_user_id = db.Column(

937 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True

938 )

939 viewer_user_id = db.Column(

940 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True

941 )

942 sole_pilot_user_id = db.Column(

943 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True

944 )

945 sole_operator_user_id = db.Column(

946 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True

947 )

948 last_activity_at = db.Column(db.DateTime(timezone=True), nullable=True)

949

950

951class MaintenanceRecord(db.Model):

952 __tablename__ = "maintenance_records"

953

954 id = db.Column(db.Integer, primary_key=True)

955 trigger_id = db.Column(

956 db.Integer,

957 db.ForeignKey("maintenance_triggers.id", ondelete="CASCADE"),

958 nullable=False,

959 )

960 performed_at = db.Column(db.Date, nullable=False)

961 hobbs_at_service = db.Column(db.Numeric(8, 1), nullable=True)

962 notes = db.Column(db.Text, nullable=True)

963 created_at = db.Column(

964 db.DateTime(timezone=True),

965 nullable=False,

966 default=lambda: datetime.now(timezone.utc),

967 )

968

969 trigger = db.relationship("MaintenanceTrigger", back_populates="records")

970

971

972# ── Phase 8: Cost Tracking ────────────────────────────────────────────────────

973

974

975class ExpenseType:

976 FUEL = "fuel"

977 PARTS = "parts"

978 INSURANCE = "insurance"

979 OTHER = "other"

980

981 ALL = {FUEL, PARTS, INSURANCE, OTHER}

982 LABELS = {

983 FUEL: "Fuel",

984 PARTS: "Parts & Maintenance",

985 INSURANCE: "Insurance",

986 OTHER: "Other",

987 }

988

989

990class Expense(db.Model):

991 __tablename__ = "expenses"

992

993 id = db.Column(db.Integer, primary_key=True)

994 aircraft_id = db.Column(

995 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

996 )

997 flight_entry_id = db.Column(

998 db.Integer,

999 db.ForeignKey("flight_entries.id", ondelete="SET NULL"),

1000 nullable=True,

1001 )

1002 date = db.Column(db.Date, nullable=False)

1003 expense_type = db.Column(db.String(32), nullable=False, default=ExpenseType.OTHER)

1004 description = db.Column(db.String(255), nullable=True)

1005 amount = db.Column(db.Numeric(10, 2), nullable=False)

1006 currency = db.Column(db.String(4), nullable=False, default="EUR")

1007 quantity = db.Column(db.Numeric(8, 2), nullable=True) # litres or gallons of fuel

1008 unit = db.Column(db.String(8), nullable=True) # L, gal

1009 created_at = db.Column(

1010 db.DateTime(timezone=True),

1011 nullable=False,

1012 default=lambda: datetime.now(timezone.utc),

1013 )

1014

1015 aircraft = db.relationship("Aircraft", back_populates="expenses")

1016 flight_entry = db.relationship("FlightEntry", back_populates="expenses")

1017

1018

1019# ── Phase 9 / 27: Document & Photo Uploads ───────────────────────────────────

1020

1021

1022class DocType:

1023 LICENSE = "license"

1024 MEDICAL = "medical"

1025 INSURANCE_CERT = "insurance_certificate"

1026

1027

1028class DocCategory:

1029 """Broad document categories that map 1-to-1 to on-disk folder names.

1030

1031 Used by the Syncthing-compatible canonical path layout:

1032 {tenant_slug}/{aircraft_reg}/{category}/{YYYY-MM-DD} - {title}.{ext}

1033 """

1034

1035 MAINTENANCE = "maintenance"

1036 INSURANCE = "insurance"

1037 POH = "poh"

1038 AIRWORTHINESS = "airworthiness"

1039 LOGBOOK = "logbook"

1040 INVOICE = "invoice"

1041 OTHER = "other"

1042 UNCATEGORISED = "uncategorised"

1043

1044 ALL = [

1045 MAINTENANCE,

1046 INSURANCE,

1047 POH,

1048 AIRWORTHINESS,

1049 LOGBOOK,

1050 INVOICE,

1051 OTHER,

1052 UNCATEGORISED,

1053 ]

1054

1055

1056class Document(db.Model):

1057 """

1058 A document or photo attached to an aircraft, component, flight entry, or

1059 pilot profile. aircraft_id or pilot_user_id must be set (not both).

1060 """

1061

1062 __tablename__ = "documents"

1063

1064 id = db.Column(db.Integer, primary_key=True)

1065 aircraft_id = db.Column(

1066 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=True

1067 )

1068 pilot_user_id = db.Column(

1069 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=True

1070 )

1071 component_id = db.Column(

1072 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=True

1073 )

1074 flight_entry_id = db.Column(

1075 db.Integer,

1076 db.ForeignKey("flight_entries.id", ondelete="CASCADE"),

1077 nullable=True,

1078 )

1079 filename = db.Column(

1080 db.String(512), nullable=False

1081 ) # stored path on disk (may include subdirectories)

1082 original_filename = db.Column(db.String(255), nullable=False) # as uploaded

1083 mime_type = db.Column(db.String(128), nullable=True)

1084 size_bytes = db.Column(db.Integer, nullable=True)

1085 title = db.Column(db.String(128), nullable=True) # optional display name

1086 doc_type = db.Column(db.String(32), nullable=True) # DocType constant

1087 category = db.Column(

1088 db.String(32), nullable=True

1089 ) # DocCategory value; drives on-disk folder

1090 valid_until = db.Column(db.Date, nullable=True)

1091 superseded_by_id = db.Column(

1092 db.Integer, db.ForeignKey("documents.id", ondelete="SET NULL"), nullable=True

1093 )

1094 is_sensitive = db.Column(db.Boolean, nullable=False, default=False)

1095 uploaded_at = db.Column(

1096 db.DateTime(timezone=True),

1097 nullable=False,

1098 default=lambda: datetime.now(timezone.utc),

1099 )

1100

1101 aircraft = db.relationship(

1102 "Aircraft",

1103 back_populates="documents",

1104 foreign_keys=[aircraft_id],

1105 )

1106 pilot_user = db.relationship("User", foreign_keys=[pilot_user_id])

1107 component = db.relationship("Component", back_populates="documents")

1108 flight_entry = db.relationship("FlightEntry", back_populates="documents")

1109 superseded_by = db.relationship(

1110 "Document",

1111 foreign_keys=[superseded_by_id],

1112 remote_side="Document.id",

1113 uselist=False,

1114 )

1115

1116 @property

1117 def owner_type(self) -> str:

1118 if self.pilot_user_id:

1119 return "pilot"

1120 if self.component_id:

1121 return "component"

1122 if self.flight_entry_id:

1123 return "entry"

1124 return "aircraft"

1125

1126 @property

1127 def is_image(self) -> bool:

1128 return bool(self.mime_type and self.mime_type.startswith("image/"))

1129

1130 @property

1131 def is_pdf(self) -> bool:

1132 return self.mime_type == "application/pdf"

1133

1134 @property

1135 def is_expiring_soon(self) -> bool:

1136 """True when valid_until is set and within 90 days from today."""

1137 from datetime import date as _date

1138

1139 if self.valid_until is None:

1140 return False

1141 return (self.valid_until - _date.today()).days <= 90

1142

1143

1144# ── Syncthing reconcile queue ─────────────────────────────────────────────────

1145

1146

1147class PendingReconcile(db.Model):

1148 """Files found on disk (via Syncthing or manual copy) that are not yet

1149 tracked in the documents table. The reconcile screen lets owners review

1150 these files and import them as Document rows with a single click.

1151

1152 filepath is relative to UPLOAD_FOLDER (e.g. 'my-hangar/OO-PNH/maintenance/

1153 2024-03-15 - Annual inspection.pdf'). The unique constraint prevents the

1154 same file from appearing twice in the queue.

1155 """

1156

1157 __tablename__ = "pending_reconcile"

1158

1159 id = db.Column(db.Integer, primary_key=True)

1160 tenant_id = db.Column(

1161 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False

1162 )

1163 aircraft_id = db.Column(

1164 db.Integer, db.ForeignKey("aircraft.id", ondelete="SET NULL"), nullable=True

1165 )

1166 filepath = db.Column(db.String(512), nullable=False, unique=True)

1167 category = db.Column(db.String(32), nullable=True)

1168 title_hint = db.Column(db.String(255), nullable=True)

1169 date_hint = db.Column(db.Date, nullable=True)

1170 detected_at = db.Column(

1171 db.DateTime(timezone=True),

1172 nullable=False,

1173 default=lambda: datetime.now(timezone.utc),

1174 )

1175 reconciled_at = db.Column(db.DateTime(timezone=True), nullable=True)

1176 ignored = db.Column(db.Boolean, nullable=False, default=False)

1177

1178 tenant = db.relationship("Tenant")

1179 aircraft = db.relationship("Aircraft")

1180

1181

1182# ── Phase 10: Backup & Restore ────────────────────────────────────────────────

1183

1184

1185class BackupRecord(db.Model):

1186 __tablename__ = "backup_records"

1187

1188 id = db.Column(db.Integer, primary_key=True)

1189 filename = db.Column(db.String(255), nullable=False)

1190 path = db.Column(db.String(512), nullable=False)

1191 size_bytes = db.Column(db.Integer, nullable=True)

1192 sha256 = db.Column(db.String(64), nullable=True)

1193 created_at = db.Column(

1194 db.DateTime(timezone=True),

1195 nullable=False,

1196 default=lambda: datetime.now(timezone.utc),

1197 )

1198 status = db.Column(db.String(32), nullable=False, default="ok") # ok / failed

1199 app_version = db.Column(db.String(64), nullable=True)

1200 alembic_head = db.Column(db.String(64), nullable=True)

1201

1202

1203# ── Phase 11: Read-only Share Links ──────────────────────────────────────────

1204

1205

1206class ShareToken(db.Model):

1207 __tablename__ = "share_tokens"

1208

1209 id = db.Column(db.Integer, primary_key=True)

1210 aircraft_id = db.Column(

1211 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

1212 )

1213 token = db.Column(db.String(16), unique=True, nullable=False, index=True)

1214 access_level = db.Column(

1215 db.String(16), nullable=False, default="summary"

1216 ) # summary / full

1217 created_at = db.Column(

1218 db.DateTime(timezone=True),

1219 nullable=False,

1220 default=lambda: datetime.now(timezone.utc),

1221 )

1222 revoked_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)

1223

1224 aircraft = db.relationship("Aircraft", back_populates="share_tokens")

1225

1226 @property

1227 def is_active(self) -> bool:

1228 return self.revoked_at is None

1229

1230

1231# ── Phase 12: Snag List ───────────────────────────────────────────────────────

1232

1233

1234class Snag(db.Model):

1235 __tablename__ = "snags"

1236

1237 id = db.Column(db.Integer, primary_key=True)

1238 aircraft_id = db.Column(

1239 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

1240 )

1241 title = db.Column(db.String(128), nullable=False)

1242 description = db.Column(db.Text, nullable=True)

1243 reporter = db.Column(db.String(128), nullable=True)

1244 is_grounding = db.Column(db.Boolean, nullable=False, default=False)

1245 reported_at = db.Column(

1246 db.DateTime(timezone=True),

1247 nullable=False,

1248 default=lambda: datetime.now(timezone.utc),

1249 )

1250 resolved_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)

1251 resolution_note = db.Column(db.Text, nullable=True)

1252

1253 aircraft = db.relationship("Aircraft", back_populates="snags")

1254

1255 @property

1256 def is_open(self) -> bool:

1257 return self.resolved_at is None

1258

1259

1260# ── Phase 22: Reservations ───────────────────────────────────────────────────

1261

1262

1263class ReservationStatus(str, enum.Enum):

1264 PENDING = "pending"

1265 CONFIRMED = "confirmed"

1266 CANCELLED = "cancelled"

1267

1268

1269class Reservation(db.Model):

1270 __tablename__ = "reservations"

1271

1272 id = db.Column(db.Integer, primary_key=True)

1273 aircraft_id = db.Column(

1274 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

1275 )

1276 pilot_user_id = db.Column(

1277 db.Integer, db.ForeignKey("users.id", ondelete="SET NULL"), nullable=True

1278 )

1279 start_dt = db.Column(db.DateTime(timezone=True), nullable=False)

1280 end_dt = db.Column(db.DateTime(timezone=True), nullable=False)

1281 status = db.Column(

1282 db.Enum(ReservationStatus), nullable=False, default=ReservationStatus.PENDING

1283 )

1284 notes = db.Column(db.Text, nullable=True)

1285 hourly_rate = db.Column(db.Numeric(8, 2), nullable=True) # EUR/h snapshot

1286 estimated_cost = db.Column(db.Numeric(10, 2), nullable=True)

1287 created_at = db.Column(

1288 db.DateTime(timezone=True),

1289 nullable=False,

1290 default=lambda: datetime.now(timezone.utc),

1291 )

1292

1293 aircraft = db.relationship("Aircraft", back_populates="reservations")

1294 pilot = db.relationship("User", foreign_keys=[pilot_user_id])

1295

1296 @property

1297 def duration_hours(self) -> float:

1298 delta = self.end_dt - self.start_dt

1299 return round(delta.total_seconds() / 3600, 2)

1300

1301

1302class AircraftBookingSettings(db.Model):

1303 """Per-aircraft booking rules and hourly rate for cost estimation."""

1304

1305 __tablename__ = "aircraft_booking_settings"

1306

1307 aircraft_id = db.Column(

1308 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), primary_key=True

1309 )

1310 min_booking_hours = db.Column(db.Numeric(4, 1), nullable=True)

1311 max_booking_hours = db.Column(db.Numeric(4, 1), nullable=True)

1312 hourly_rate = db.Column(db.Numeric(8, 2), nullable=True) # EUR/h

1313

1314 aircraft = db.relationship("Aircraft", back_populates="booking_settings")

1315

1316

1317# ── Phase 20: Mass & Balance ──────────────────────────────────────────────────

1318

1319FUEL_DENSITY = {

1320 "avgas": 0.72, # Avgas 100LL

1321 "ul91": 0.72, # UL91 — unleaded avgas replacement

1322 "mogas": 0.74, # Automotive gasoline (Mogas)

1323 "jet_a1": 0.81, # Jet-A1 (kerosene)

1324} # kg/L

1325GAL_TO_L = 3.78541 # US gallons to litres

1326

1327

1328class WeightBalanceConfig(db.Model):

1329 __tablename__ = "wb_configs"

1330

1331 id = db.Column(db.Integer, primary_key=True)

1332 aircraft_id = db.Column(

1333 db.Integer,

1334 db.ForeignKey("aircraft.id", ondelete="CASCADE"),

1335 nullable=False,

1336 unique=True,

1337 )

1338 empty_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg

1339 empty_cg_arm = db.Column(db.Numeric(7, 2), nullable=False) # m from datum

1340 max_takeoff_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg

1341 forward_cg_limit = db.Column(db.Numeric(7, 2), nullable=False) # m

1342 aft_cg_limit = db.Column(db.Numeric(7, 2), nullable=False) # m

1343 fuel_unit = db.Column(db.String(3), nullable=False, default="L") # "L" or "gal"

1344 # Optional non-rectangular envelope: list of [arm_m, weight_kg] pairs in polygon order.

1345 # When ≥ 3 points are present they override forward_cg_limit/aft_cg_limit/max_takeoff_weight

1346 # for the in-envelope check.

1347 envelope_points = db.Column(db.JSON, nullable=True)

1348 datum_note = db.Column(db.String(200), nullable=True)

1349

1350 aircraft = db.relationship("Aircraft", back_populates="wb_config")

1351 stations = db.relationship(

1352 "WeightBalanceStation",

1353 back_populates="config",

1354 cascade="all, delete-orphan",

1355 order_by="WeightBalanceStation.position",

1356 )

1357 entries = db.relationship(

1358 "WeightBalanceEntry",

1359 back_populates="config",

1360 cascade="all, delete-orphan",

1361 )

1362

1363

1364class WeightBalanceStation(db.Model):

1365 __tablename__ = "wb_stations"

1366

1367 id = db.Column(db.Integer, primary_key=True)

1368 config_id = db.Column(

1369 db.Integer, db.ForeignKey("wb_configs.id", ondelete="CASCADE"), nullable=False

1370 )

1371 label = db.Column(db.String(64), nullable=False)

1372 arm = db.Column(db.Numeric(7, 2), nullable=False) # m from datum

1373 max_weight = db.Column(db.Numeric(6, 2), nullable=True) # kg limit (non-fuel only)

1374 capacity = db.Column(db.Float, nullable=True) # L or gal (fuel stations)

1375 is_fuel = db.Column(db.Boolean, nullable=False, default=False)

1376 position = db.Column(db.Integer, nullable=False, default=0) # display order

1377

1378 config = db.relationship("WeightBalanceConfig", back_populates="stations")

1379

1380

1381class WeightBalanceEntry(db.Model):

1382 __tablename__ = "wb_entries"

1383

1384 id = db.Column(db.Integer, primary_key=True)

1385 config_id = db.Column(

1386 db.Integer, db.ForeignKey("wb_configs.id", ondelete="CASCADE"), nullable=False

1387 )

1388 date = db.Column(db.Date, nullable=False)

1389 label = db.Column(db.String(100), nullable=True)

1390 total_weight = db.Column(db.Numeric(7, 2), nullable=False) # kg

1391 loaded_cg = db.Column(db.Numeric(7, 2), nullable=False) # mm

1392 is_in_envelope = db.Column(db.Boolean, nullable=False)

1393 # {station_id_str: value} — fuel stations store volume (L or gal), non-fuel store kg

1394 station_weights = db.Column(db.JSON, nullable=False, default=dict)

1395 created_at = db.Column(

1396 db.DateTime(timezone=True),

1397 nullable=False,

1398 default=lambda: datetime.now(timezone.utc),

1399 )

1400

1401 config = db.relationship("WeightBalanceConfig", back_populates="entries")

1402

1403

1404class AppSetting(db.Model):

1405 __tablename__ = "app_settings"

1406

1407 key = db.Column(db.String(64), primary_key=True)

1408 value = db.Column(db.Text, nullable=True)

1409

1410

1411# ── Phase 33: Airworthiness Requirements Tracker ──────────────────────────────

1412

1413

1414class EASASourceNode(db.Model):

1415 """

1416 Maps a Component to one leaf node in the EASA Safety Publications Tool

1417 taxonomy tree (TC holder → type → model). One component may have multiple

1418 nodes (e.g. base TC plus an installed STC that also carries ADs).

1419 """

1420

1421 __tablename__ = "easa_source_nodes"

1422

1423 id = db.Column(db.Integer, primary_key=True)

1424 component_id = db.Column(

1425 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=False

1426 )

1427 tc_holder_node_id = db.Column(db.String(16), nullable=False)

1428 tc_holder_name = db.Column(db.String(128), nullable=False)

1429 type_node_id = db.Column(db.String(16), nullable=False)

1430 type_name = db.Column(db.String(128), nullable=False)

1431 model_node_id = db.Column(db.String(16), nullable=False)

1432 model_name = db.Column(db.String(128), nullable=False)

1433 last_synced_at = db.Column(db.DateTime(timezone=True), nullable=True)

1434 consecutive_errors = db.Column(db.Integer, nullable=False, default=0)

1435

1436 component = db.relationship("Component", back_populates="easa_source_nodes")

1437 documents = db.relationship(

1438 "AirworthinessDocument",

1439 back_populates="source_node",

1440 cascade="all, delete-orphan",

1441 )

1442

1443 @property

1444 def display_path(self) -> str:

1445 return f"{self.tc_holder_name} / {self.type_name} / {self.model_name}"

1446

1447

1448class AirworthinessDocType:

1449 AD = "ad"

1450 MANDATORY_SB = "mandatory_sb"

1451 SB = "sb"

1452 SIB = "sib"

1453 ARC = "arc"

1454 MANUAL = "manual"

1455

1456 ALL = (AD, MANDATORY_SB, SB, SIB, ARC, MANUAL)

1457 SYNCED = (AD, SIB) # types populated by EASA sync

1458 LABELS = {

1459 AD: "AD",

1460 MANDATORY_SB: "Mandatory SB",

1461 SB: "SB",

1462 SIB: "SIB",

1463 ARC: "ARC",

1464 MANUAL: "Manual",

1465 }

1466

1467

1468class AirworthinessDocStatus:

1469 PENDING_REVIEW = "pending_review"

1470 COMPLIED = "complied"

1471 NOT_APPLICABLE = "not_applicable"

1472 DEFERRED = "deferred"

1473 QUESTION = "question"

1474

1475 ALL = (PENDING_REVIEW, COMPLIED, NOT_APPLICABLE, DEFERRED, QUESTION)

1476

1477

1478class AirworthinessDocument(db.Model):

1479 """

1480 One airworthiness-related document (AD, SB, SIB, ARC, …) applicable to a

1481 component. Synced documents reference a source_node; manually entered

1482 documents have source_node_id = NULL.

1483 """

1484

1485 __tablename__ = "airworthiness_documents"

1486

1487 id = db.Column(db.Integer, primary_key=True)

1488 doc_type = db.Column(db.String(16), nullable=False)

1489 reference = db.Column(db.String(64), nullable=False)

1490 title = db.Column(db.String(256), nullable=True)

1491 source_node_id = db.Column(

1492 db.Integer,

1493 db.ForeignKey("easa_source_nodes.id", ondelete="CASCADE"),

1494 nullable=True,

1495 )

1496 # For manual entries without a source node, store the component directly

1497 component_id = db.Column(

1498 db.Integer, db.ForeignKey("components.id", ondelete="CASCADE"), nullable=True

1499 )

1500 doc_url = db.Column(db.String(512), nullable=True)

1501 # For ARC: date the certificate expires

1502 expiry_date = db.Column(db.Date, nullable=True)

1503 first_seen_at = db.Column(

1504 db.DateTime(timezone=True),

1505 nullable=False,

1506 default=lambda: datetime.now(timezone.utc),

1507 )

1508

1509 source_node = db.relationship("EASASourceNode", back_populates="documents")

1510 component = db.relationship("Component", back_populates="airworthiness_documents")

1511 statuses = db.relationship(

1512 "AirworthinessDocumentStatus",

1513 back_populates="document",

1514 cascade="all, delete-orphan",

1515 )

1516

1517 @property

1518 def is_manual(self) -> bool:

1519 return self.source_node_id is None

1520

1521

1522class AirworthinessDocumentStatus(db.Model):

1523 """

1524 Compliance state of one AirworthinessDocument for one aircraft.

1525 Unique per (aircraft_id, document_id).

1526 """

1527

1528 __tablename__ = "airworthiness_document_statuses"

1529 __table_args__ = (

1530 db.UniqueConstraint("aircraft_id", "document_id", name="uq_aw_status"),

1531 )

1532

1533 id = db.Column(db.Integer, primary_key=True)

1534 aircraft_id = db.Column(

1535 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

1536 )

1537 document_id = db.Column(

1538 db.Integer,

1539 db.ForeignKey("airworthiness_documents.id", ondelete="CASCADE"),

1540 nullable=False,

1541 )

1542 status = db.Column(

1543 db.String(24), nullable=False, default=AirworthinessDocStatus.PENDING_REVIEW

1544 )

1545 notes = db.Column(db.Text, nullable=True)

1546 compliance_date = db.Column(db.Date, nullable=True)

1547 next_review_date = db.Column(db.Date, nullable=True)

1548 updated_at = db.Column(

1549 db.DateTime(timezone=True),

1550 nullable=False,

1551 default=lambda: datetime.now(timezone.utc),

1552 onupdate=lambda: datetime.now(timezone.utc),

1553 )

1554

1555 aircraft = db.relationship("Aircraft", back_populates="airworthiness_statuses")

1556 document = db.relationship("AirworthinessDocument", back_populates="statuses")

1557

1558

1559class InstalledSTC(db.Model):

1560 """

1561 Registry of Supplemental Type Certificates physically installed on an

1562 aircraft. No compliance workflow — presence/absence is the record.

1563 """

1564

1565 __tablename__ = "installed_stcs"

1566

1567 id = db.Column(db.Integer, primary_key=True)

1568 aircraft_id = db.Column(

1569 db.Integer, db.ForeignKey("aircraft.id", ondelete="CASCADE"), nullable=False

1570 )

1571 stc_number = db.Column(db.String(64), nullable=False)

1572 title = db.Column(db.String(256), nullable=True)

1573 tc_holder = db.Column(db.String(128), nullable=True)

1574 installation_date = db.Column(db.Date, nullable=True)

1575 notes = db.Column(db.Text, nullable=True)

1576

1577 aircraft = db.relationship("Aircraft", back_populates="installed_stcs")

1578

1579

1580# ── Phase 34: Email Notifications ─────────────────────────────────────────────

1581

1582

1583class NotificationType:

1584 """String constants for all supported notification types.

1585

1586 Stored as plain strings in the DB — no enum migration needed to add types.

1587 """

1588

1589 GROUNDING_SNAG_OPENED = "grounding_snag_opened"

1590 SNAG_REPORTED = "snag_reported"

1591 RESERVATION_CONFIRMED = "reservation_confirmed"

1592 RESERVATION_CANCELLED = "reservation_cancelled"

1593 RESERVATION_REQUEST = "reservation_request"

1594 MAINTENANCE_DUE_SOON = "maintenance_due_soon"

1595 MAINTENANCE_OVERDUE = "maintenance_overdue"

1596 INSURANCE_EXPIRING = "insurance_expiring"

1597 MEDICAL_EXPIRING = "medical_expiring"

1598 SEP_RATING_EXPIRING = "sep_rating_expiring"

1599 DOCUMENT_EXPIRING = "document_expiring"

1600 NEW_MEMBER_JOINED = "new_member_joined"

1601 AIRWORTHINESS_REVIEW_DUE = "airworthiness_review_due"

1602 EASA_SYNC_NEW_AD = "easa_sync_new_ad"

1603

1604 ALL: list[str] = [

1605 GROUNDING_SNAG_OPENED,

1606 SNAG_REPORTED,

1607 RESERVATION_CONFIRMED,

1608 RESERVATION_CANCELLED,

1609 RESERVATION_REQUEST,

1610 MAINTENANCE_DUE_SOON,

1611 MAINTENANCE_OVERDUE,

1612 INSURANCE_EXPIRING,

1613 MEDICAL_EXPIRING,

1614 SEP_RATING_EXPIRING,

1615 DOCUMENT_EXPIRING,

1616 NEW_MEMBER_JOINED,

1617 AIRWORTHINESS_REVIEW_DUE,

1618 EASA_SYNC_NEW_AD,

1619 ]

1620

1621 # System defaults — coded constants; DB only stores per-user or per-tenant overrides

1622 SYSTEM_DEFAULTS: dict[str, dict] = {

1623 GROUNDING_SNAG_OPENED: {"enabled": True, "threshold_days": None},

1624 SNAG_REPORTED: {"enabled": False, "threshold_days": None},

1625 RESERVATION_CONFIRMED: {"enabled": True, "threshold_days": None},

1626 RESERVATION_CANCELLED: {"enabled": True, "threshold_days": None},

1627 RESERVATION_REQUEST: {"enabled": True, "threshold_days": None},

1628 MAINTENANCE_DUE_SOON: {"enabled": True, "threshold_days": 30},

1629 MAINTENANCE_OVERDUE: {"enabled": True, "threshold_days": None},

1630 INSURANCE_EXPIRING: {"enabled": True, "threshold_days": 30},

1631 MEDICAL_EXPIRING: {"enabled": True, "threshold_days": 60},

1632 SEP_RATING_EXPIRING: {"enabled": True, "threshold_days": 60},

1633 DOCUMENT_EXPIRING: {"enabled": True, "threshold_days": 30},

1634 NEW_MEMBER_JOINED: {"enabled": False, "threshold_days": None},

1635 AIRWORTHINESS_REVIEW_DUE: {"enabled": True, "threshold_days": 30},

1636 EASA_SYNC_NEW_AD: {"enabled": True, "threshold_days": None},

1637 }

1638

1639 # Capability flags required — user sees this type in their prefs if they have >= 1

1640 # "is_owner" | "is_pilot" | "is_maint" match init.py context processor naming

1641 REQUIRED_CAPS: dict[str, list[str]] = {

1642 GROUNDING_SNAG_OPENED: ["is_owner", "is_maint"],

1643 SNAG_REPORTED: ["is_owner"],

1644 RESERVATION_CONFIRMED: ["is_pilot"],

1645 RESERVATION_CANCELLED: ["is_pilot"],

1646 RESERVATION_REQUEST: ["is_owner"],

1647 MAINTENANCE_DUE_SOON: ["is_owner", "is_maint"],

1648 MAINTENANCE_OVERDUE: ["is_owner", "is_maint"],

1649 INSURANCE_EXPIRING: ["is_owner"],

1650 MEDICAL_EXPIRING: ["is_pilot"],

1651 SEP_RATING_EXPIRING: ["is_pilot"],

1652 DOCUMENT_EXPIRING: ["is_owner", "is_maint"],

1653 NEW_MEMBER_JOINED: ["is_owner"],

1654 AIRWORTHINESS_REVIEW_DUE: ["is_owner", "is_maint"],

1655 EASA_SYNC_NEW_AD: ["is_owner", "is_maint"],

1656 }

1657

1658 # Types that have a configurable days-ahead threshold

1659 HAS_THRESHOLD: set[str] = {

1660 MAINTENANCE_DUE_SOON,

1661 INSURANCE_EXPIRING,

1662 MEDICAL_EXPIRING,

1663 SEP_RATING_EXPIRING,

1664 DOCUMENT_EXPIRING,

1665 AIRWORTHINESS_REVIEW_DUE,

1666 }

1667

1668

1669class NotificationPreference(db.Model):

1670 """Per-user notification preference override within a tenant (level 1 of 3)."""

1671

1672 __tablename__ = "notification_preferences"

1673 __table_args__ = (

1674 db.UniqueConstraint(

1675 "user_id", "tenant_id", "notification_type", name="uq_notif_pref"

1676 ),

1677 )

1678

1679 id = db.Column(db.Integer, primary_key=True)

1680 user_id = db.Column(

1681 db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False

1682 )

1683 tenant_id = db.Column(

1684 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False

1685 )

1686 notification_type = db.Column(db.String(64), nullable=False)

1687 enabled = db.Column(db.Boolean, nullable=False)

1688 threshold_days = db.Column(db.Integer, nullable=True)

1689 updated_at = db.Column(

1690 db.DateTime(timezone=True),

1691 nullable=False,

1692 default=lambda: datetime.now(timezone.utc),

1693 onupdate=lambda: datetime.now(timezone.utc),

1694 )

1695

1696 user = db.relationship("User")

1697 tenant = db.relationship("Tenant")

1698

1699

1700class TenantNotificationDefault(db.Model):

1701 """Per-tenant override of system notification defaults (level 2 of 3)."""

1702

1703 __tablename__ = "tenant_notification_defaults"

1704 __table_args__ = (

1705 db.UniqueConstraint(

1706 "tenant_id", "notification_type", name="uq_tenant_notif_default"

1707 ),

1708 )

1709

1710 id = db.Column(db.Integer, primary_key=True)

1711 tenant_id = db.Column(

1712 db.Integer, db.ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False

1713 )

1714 notification_type = db.Column(db.String(64), nullable=False)

1715 enabled = db.Column(db.Boolean, nullable=False)

1716 threshold_days = db.Column(db.Integer, nullable=True)

1717 updated_at = db.Column(

1718 db.DateTime(timezone=True),

1719 nullable=False,

1720 default=lambda: datetime.now(timezone.utc),

1721 onupdate=lambda: datetime.now(timezone.utc),

1722 )

1723

1724 tenant = db.relationship("Tenant")