repository

type AdminAuditLogEntry struct {
	ID              uuid.UUID
	AdminUserID     uuid.UUID
	AdminEmail      *string
	AdminName       *string
	Action          string
	TargetUserID    *uuid.UUID
	TargetUserEmail *string
	TargetUserName  *string
	// Details is the JSON metadata payload for the action.
	Details   []byte
	CreatedAt time.Time
}

type AdminRepository interface {
	// GetStats returns the dashboard counters, evaluated relative to `now`
	// (month-to-date, trailing week, and lock expiry all derive from it).
	GetStats(ctx context.Context, now time.Time) (*AdminStats, error)

	// MigrationVersion returns the highest cleanly-applied schema migration
	// version, or 0 when none is recorded.
	MigrationVersion(ctx context.Context) (int, error)

	// CountAuditLog counts all audit log entries.
	CountAuditLog(ctx context.Context) (int, error)

	// ListAuditLog returns audit entries newest first.
	ListAuditLog(ctx context.Context, limit, offset int) ([]*AdminAuditLogEntry, error)

	// InsertAuditLog appends one action. Only ID, AdminUserID, Action,
	// TargetUserID, Details and CreatedAt are stored; the joined email/name
	// fields are read-side decoration.
	InsertAuditLog(ctx context.Context, entry *AdminAuditLogEntry) error

	// ListUsers returns one page of the user listing, newest first, plus the
	// total row count for the (optionally searched) set. A non-empty search
	// matches email or name case-insensitively as a substring.
	ListUsers(ctx context.Context, search string, limit, offset int) ([]*AdminUserRow, int, error)

	// UnlockUser resets the failed-login counter and clears any lockout.
	UnlockUser(ctx context.Context, userID uuid.UUID) error

	// DeleteExpiredRefreshTokens removes refresh tokens that are expired or
	// revoked, returning how many rows went.
	DeleteExpiredRefreshTokens(ctx context.Context, now time.Time) (int64, error)

	// DeleteExpiredPasswordResetTokens removes password-reset tokens that are
	// expired or already used, returning how many rows went.
	DeleteExpiredPasswordResetTokens(ctx context.Context, now time.Time) (int64, error)
}

type AdminStats struct {
	TotalUsers             int
	TotalFlights           int
	TotalSimulatorSessions int
	TotalPassengerFlights  int
	TotalAircraft          int
	TotalContacts          int
	TotalCredentials       int
	TotalImports           int
	FlightsThisMonth       int
	NewUsersThisWeek       int
	LockedAccounts         int
	DisabledAccounts       int
	// ActiveSessions counts live sessions across all users.
	ActiveSessions int
	// ImportsByFormat maps import_format value to import count. Never nil;
	// formats with no imports are absent.
	ImportsByFormat map[string]int
	// BackupDestinationsByProvider maps provider name to destination count.
	// Never nil.
	BackupDestinationsByProvider map[string]int
}

type AdminUserRow struct {
	ID                         uuid.UUID
	Email                      string
	Name                       string
	CreatedAt                  time.Time
	LastLoginAt                *time.Time
	EmailVerified              bool
	TwoFactorEnabled           bool
	Disabled                   bool
	FailedLoginAttempts        int
	LockedUntil                *time.Time
	VerificationReminderSentAt *time.Time
	EmailSuppressed            bool
	FlightCount                int
	AircraftCount              int
}

type AircraftRepository interface {
	Create(ctx context.Context, aircraft *models.Aircraft) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.Aircraft, error)
	// GetByUserID retrieves all aircraft for a user. A non-nil updatedSince
	// narrows the result to aircraft changed strictly after that instant.
	GetByUserID(ctx context.Context, userID uuid.UUID, updatedSince *time.Time) ([]*models.Aircraft, error)
	// GetPageByUserID retrieves one registration-ordered page of a user's
	// aircraft, bounded by LIMIT/OFFSET in SQL, and the total row count
	// matching the same filter. A non-nil updatedSince narrows both to
	// aircraft changed strictly after that instant.
	GetPageByUserID(ctx context.Context, userID uuid.UUID, updatedSince *time.Time, limit, offset int) ([]*models.Aircraft, int, error)
	Update(ctx context.Context, aircraft *models.Aircraft) error
	// UpdateWithFlightRename updates the aircraft and, in the same
	// transaction, repoints flights (and open flight sessions) logged under
	// oldRegistration to the aircraft's new registration. Returns the number
	// of flights updated.
	UpdateWithFlightRename(ctx context.Context, aircraft *models.Aircraft, oldRegistration string) (int, error)
	Delete(ctx context.Context, id uuid.UUID) error
	CountByUserID(ctx context.Context, userID uuid.UUID) (int, error)
	// GetStatsByUserID aggregates flight statistics per aircraft registration
	GetStatsByUserID(ctx context.Context, userID uuid.UUID) ([]*models.AircraftStats, error)
	// GetTypeStatsByUserID aggregates flight statistics per aircraft type
	GetTypeStatsByUserID(ctx context.Context, userID uuid.UUID) ([]*models.AircraftTypeStats, error)
	// GetRecencyRowsByUserID returns per-day landing counts for flights in
	// the preceding 90 days, newest first, for recency derivation
	GetRecencyRowsByUserID(ctx context.Context, userID uuid.UUID) ([]*models.AircraftRecencyRow, error)
}

type AircraftTypeTrendRow struct {
	AircraftType string
	TotalFlights int
	TotalMinutes int
}

type AirportDirectionCount struct {
	ICAO      string
	Direction string
	Count     int
}

type AnalyticsAircraftRow struct {
	Label           string
	SubLabel        *string
	Flights         int
	TotalMinutes    int
	PicMinutes      int
	DualMinutes     int
	NightMinutes    int
	IfrMinutes      int
	Landings        int
	DistanceNM      float64
	FirstFlightDate *time.Time
	LastFlightDate  *time.Time
}

type AnalyticsAirportRow struct {
	ICAO       string
	Departures int
	Arrivals   int
	Flights    int
}

type AnalyticsApproachTypeRow struct {
	Type  string
	Count int
}

type AnalyticsBucket struct {
	Flights      int
	TotalMinutes int
}

type AnalyticsFlightRef struct {
	ID            uuid.UUID
	Date          time.Time
	AircraftReg   *string
	AircraftType  *string
	DepartureICAO *string
	ArrivalICAO   *string
	TotalMinutes  int
	DistanceNM    float64
}

type AnalyticsGroupRow struct {
	Label        string
	Flights      int
	TotalMinutes int
	PicMinutes   int
	DualMinutes  int
	Landings     int
}

type AnalyticsMonthRow struct {
	Month            string // "YYYY-MM"
	Flights          int
	TotalMinutes     int
	PicMinutes       int
	SicMinutes       int
	DualMinutes      int
	DualGivenMinutes int
	SoloMinutes      int
	NightMinutes     int
	IfrMinutes       int
	LandingsDay      int
	LandingsNight    int
	DistanceNM       float64
}

type AnalyticsPersonRow struct {
	Name string
	// Role is the role the person most flies as, by time.
	Role *string
	// Roles is every role the person has held, most time first.
	Roles []string
	// ContactID is the contact the person's crew entries link to, if any.
	ContactID      *string
	Flights        int
	TotalMinutes   int
	LastFlightDate *time.Time
}

type AnalyticsRouteRow struct {
	DepartureICAO string
	ArrivalICAO   string
	Flights       int
	TotalMinutes  int
	DistanceNM    float64
}

type AnalyticsTotals struct {
	TotalFlights               int
	TotalMinutes               int
	PicMinutes                 int
	SicMinutes                 int
	DualMinutes                int
	DualGivenMinutes           int
	PicusMinutes               int
	SpicMinutes                int
	ExaminerMinutes            int
	ReliefMinutes              int
	SoloMinutes                int
	NightMinutes               int
	IfrMinutes                 int
	ActualInstrumentMinutes    int
	SimulatedInstrumentMinutes int
	CrossCountryMinutes        int
	MultiPilotMinutes          int
	SimulatedFlightMinutes     int
	GroundTrainingMinutes      int
	LandingsDay                int
	LandingsNight              int
	TakeoffsDay                int
	TakeoffsNight              int
	Approaches                 int
	Holds                      int
	DistanceNM                 float64
	DistinctRegistrations      int
	DistinctTypes              int
	FirstFlightDate            *time.Time
	LastFlightDate             *time.Time
}

type AnalyticsYearRow struct {
	Year         int
	Flights      int
	TotalMinutes int
	PicMinutes   int
	DualMinutes  int
	NightMinutes int
	IfrMinutes   int
	Landings     int
	DistanceNM   float64
}

type AnnouncementRepository interface {
	// ListActive returns announcements that have not expired as of `now`,
	// newest first.
	ListActive(ctx context.Context, now time.Time) ([]*models.SystemAnnouncement, error)

	// Create stores a new announcement.
	Create(ctx context.Context, a *models.SystemAnnouncement) error

	// Delete removes an announcement. Returns ErrNotFound when no row had
	// this id.
	Delete(ctx context.Context, id uuid.UUID) error
}

type BackupDestinationRepository interface {
	Create(ctx context.Context, dest *models.BackupDestination) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.BackupDestination, error)
	GetByUserID(ctx context.Context, userID uuid.UUID) ([]*models.BackupDestination, error)
	Update(ctx context.Context, dest *models.BackupDestination) error
	Delete(ctx context.Context, id uuid.UUID) error
	// ListDueForRun returns enabled, active destinations whose schedule
	// indicates they are due to run at the supplied wall-clock time.
	ListDueForRun(ctx context.Context, now time.Time) ([]*models.BackupDestination, error)
}

type BackupRunRepository interface {
	Create(ctx context.Context, run *models.BackupRun) error
	Update(ctx context.Context, run *models.BackupRun) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.BackupRun, error)
	// GetByDestinationID returns the requested page of runs newest-first
	// along with the total row count.
	GetByDestinationID(ctx context.Context, destinationID uuid.UUID, limit, offset int) ([]*models.BackupRun, int, error)
	DeleteByDestinationID(ctx context.Context, destinationID uuid.UUID) error
}

type CategoryStatRow struct {
	Category    string
	Flights     int
	PICMinutes  int
	DualMinutes int
}

type ClassStatRow struct {
	Class       string
	Flights     int
	Minutes     int
	PICMinutes  int
	DualMinutes int
	Landings    int
}

type ContactRepository interface {
	Create(ctx context.Context, contact *models.Contact) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.Contact, error)
	// GetByUserID retrieves all contacts for a user. A non-nil updatedSince
	// narrows the result to contacts changed strictly after that instant.
	GetByUserID(ctx context.Context, userID uuid.UUID, updatedSince *time.Time) ([]*models.Contact, error)
	GetByExactName(ctx context.Context, userID uuid.UUID, name string) (*models.Contact, error)
	// FindOrCreateByName returns the user's contact with this name — creating
	// it if absent — and reports whether it created one. Concurrent callers
	// converge on a single row.
	FindOrCreateByName(ctx context.Context, userID uuid.UUID, name string) (*models.Contact, bool, error)
	Search(ctx context.Context, userID uuid.UUID, query string, limit int) ([]*models.Contact, error)
	Update(ctx context.Context, contact *models.Contact) error
	// UpdateWithCrewRename updates the contact and carries a name change into
	// the crew rows of the user's unsigned flights, returning the number of
	// crew rows rewritten. Signed flights keep the name they were signed with.
	UpdateWithCrewRename(ctx context.Context, contact *models.Contact) (int, error)
	// RolesByContact maps each of the user's contacts to the distinct crew
	// roles it has been logged in, most-used first.
	RolesByContact(ctx context.Context, userID uuid.UUID) (map[uuid.UUID][]string, error)
	Delete(ctx context.Context, id uuid.UUID) error
}

type CredentialRepository interface {
	Create(ctx context.Context, credential *models.Credential) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.Credential, error)
	// GetByUserID retrieves all credentials for a user. A non-nil updatedSince
	// narrows the result to credentials changed strictly after that instant.
	GetByUserID(ctx context.Context, userID uuid.UUID, updatedSince *time.Time) ([]*models.Credential, error)
	Update(ctx context.Context, credential *models.Credential) error
	Delete(ctx context.Context, id uuid.UUID) error
}

type CustomCurrencyRuleRepository interface {
	Create(ctx context.Context, rule *models.CustomCurrencyRule) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.CustomCurrencyRule, error)
	GetByUserID(ctx context.Context, userID uuid.UUID) ([]*models.CustomCurrencyRule, error)
	GetByShareToken(ctx context.Context, token string) (*models.CustomCurrencyRule, error)
	Update(ctx context.Context, rule *models.CustomCurrencyRule) error
	Delete(ctx context.Context, id uuid.UUID) error
}

type DeletionRepository interface {
	// ListSince returns the user's deletions strictly after `since`, oldest
	// first, optionally narrowed to one entity type.
	ListSince(ctx context.Context, userID uuid.UUID, since time.Time, entity *models.DeletionEntityType, limit, offset int) ([]*models.Deletion, error)

	// CountSince counts the set ListSince pages over.
	CountSince(ctx context.Context, userID uuid.UUID, since time.Time, entity *models.DeletionEntityType) (int, error)

	// DeleteExpired removes tombstones older than `before`. Returns the
	// number deleted.
	DeleteExpired(ctx context.Context, before time.Time) (int64, error)
}

type DocumentFileRepository interface {
	// Create atomically inserts the image only if the subject is still below
	// maxPerSubject images, returning repository.ErrDocumentFileLimit
	// otherwise, or ErrNotFound if the document does not exist.
	Create(ctx context.Context, image *models.DocumentFile, maxPerSubject int) error

	// ListBySubject returns the images attached to one licence or credential,
	// oldest first, without their payload (DocumentFile.Data is nil).
	ListBySubject(ctx context.Context, userID uuid.UUID, subject models.DocumentSubjectType, subjectID uuid.UUID) ([]*models.DocumentFile, error)

	// GetWithData returns a single image including its bytes, scoped by
	// subjectID.
	GetWithData(ctx context.Context, userID uuid.UUID, subject models.DocumentSubjectType, subjectID, imageID uuid.UUID) (*models.DocumentFile, error)

	// Delete removes one image, scoped the same way as GetWithData.
	Delete(ctx context.Context, userID uuid.UUID, subject models.DocumentSubjectType, subjectID, imageID uuid.UUID) error

	// CountBySubject reports how many images the subject already carries.
	CountBySubject(ctx context.Context, userID uuid.UUID, subject models.DocumentSubjectType, subjectID uuid.UUID) (int, error)
}

type EmailDeliveryRepository interface {
	// RecordEvent appends one attempt to the delivery log.
	RecordEvent(ctx context.Context, event *models.EmailDeliveryEvent) error

	// ListEvents returns the most recent events, newest first, optionally
	// narrowed to one recipient address.
	ListEvents(ctx context.Context, recipient string, limit int) ([]*models.EmailDeliveryEvent, error)

	// IsSuppressed reports whether the address is on the suppression list.
	IsSuppressed(ctx context.Context, email string) (bool, error)

	// Suppress adds or updates a suppression, bumping the bounce count when the
	// address is already listed.
	Suppress(ctx context.Context, email, reason string, smtpCode *int, detail string) error

	// Unsuppress removes an address from the suppression list. Returns
	// ErrNotFound when the address was not listed.
	Unsuppress(ctx context.Context, email string) error

	// ListSuppressions returns suppressed addresses, most recently bounced first.
	ListSuppressions(ctx context.Context, limit int) ([]*models.EmailSuppression, error)

	// CountSuppressions reports the size of the suppression list.
	CountSuppressions(ctx context.Context) (int, error)

	// UserIDForEmail resolves an address to an account. Returns ErrNotFound
	// when no account has that address.
	UserIDForEmail(ctx context.Context, email string) (uuid.UUID, error)
}

type EmailVerificationTokenRepository interface {
	Create(ctx context.Context, token *models.EmailVerificationToken) error
	GetByTokenHash(ctx context.Context, tokenHash string) (*models.EmailVerificationToken, error)
	MarkAsUsed(ctx context.Context, tokenHash string) error
	DeleteForUser(ctx context.Context, userID uuid.UUID) error
	DeleteExpired(ctx context.Context) error
}

type FlightBaselineRepository interface {
	// Get returns the baseline for the given user, or repository.ErrNotFound.
	Get(ctx context.Context, userID uuid.UUID) (*models.FlightBaseline, error)

	// Upsert inserts or updates the baseline for a user. The user_id field on
	// the model must be set; created_at / updated_at are populated by the DB.
	Upsert(ctx context.Context, baseline *models.FlightBaseline) error

	// Delete removes the baseline for the given user. Returns ErrNotFound when
	// no baseline existed.
	Delete(ctx context.Context, userID uuid.UUID) error
}

type FlightCrewRepository interface {
	SetCrewMembers(ctx context.Context, flightID uuid.UUID, members []models.FlightCrewMember) error
	GetByFlightID(ctx context.Context, flightID uuid.UUID) ([]models.FlightCrewMember, error)
	GetByFlightIDs(ctx context.Context, flightIDs []uuid.UUID) (map[uuid.UUID][]models.FlightCrewMember, error)
	DeleteByFlightID(ctx context.Context, flightID uuid.UUID) error
}

type FlightImportRepository interface {
	// Create stores one import record (created_at is set by the database).
	Create(ctx context.Context, rec *models.FlightImport) error

	// CountByUserID counts a user's import records.
	CountByUserID(ctx context.Context, userID uuid.UUID) (int, error)

	// ListByUserID returns a user's import records newest first.
	ListByUserID(ctx context.Context, userID uuid.UUID, limit, offset int) ([]*models.FlightImport, error)

	// GetByIDForUser returns one import record scoped to its owner, or
	// ErrNotFound.
	GetByIDForUser(ctx context.Context, id, userID uuid.UUID) (*models.FlightImport, error)

	// DeleteByUserID removes all of a user's import records.
	DeleteByUserID(ctx context.Context, userID uuid.UUID) error
}

type FlightQueryOptions struct {
	StartDate     *time.Time
	EndDate       *time.Time
	AircraftReg   *string
	DepartureICAO *string
	ArrivalICAO   *string
	IsPIC         *bool
	IsDual        *bool
	Search        *string
	// UpdatedSince restricts the result to flights whose updated_at is
	// strictly after the given instant, compared at full timestamp precision.
	UpdatedSince *time.Time
	// Query is a parsed advanced search query (see internal/flightsearch),
	// ANDed with the other filters.
	Query     *flightsearch.Query
	Page      int
	PageSize  int
	SortBy    string // "date", "totalTime", "createdAt"
	SortOrder string // "asc", "desc"

	// Logbook filtering: when FilterByRegistrations is true, only flights whose
	// aircraft_reg matches one of AircraftRegistrations (case-insensitive) are
	// returned. Registrations should be supplied upper-cased. An empty slice with
	// FilterByRegistrations=true matches no flights. Applied at the SQL level.
	FilterByRegistrations bool
	AircraftRegistrations []string
}

type FlightRepository interface {
	// Create creates a new flight
	Create(ctx context.Context, flight *models.Flight) error

	// GetByID retrieves a flight by its ID
	GetByID(ctx context.Context, id uuid.UUID) (*models.Flight, error)

	// GetByUserID retrieves flights for a user with optional filters
	GetByUserID(ctx context.Context, userID uuid.UUID, opts *FlightQueryOptions) ([]*models.Flight, error)

	// Update updates a flight
	Update(ctx context.Context, flight *models.Flight) error

	// Delete deletes a flight
	Delete(ctx context.Context, id uuid.UUID) error

	// DeleteAllByUserID deletes all flights for a user, returns count deleted
	DeleteAllByUserID(ctx context.Context, userID uuid.UUID) (int64, error)

	// CountByUserID counts flights for a user with optional filters
	CountByUserID(ctx context.Context, userID uuid.UUID, opts *FlightQueryOptions) (int, error)

	// GetStatsByUserID returns aggregated flight statistics for a user
	GetStatsByUserID(ctx context.Context, userID uuid.UUID, startDate, endDate *time.Time) (*models.FlightStatistics, error)

	// GetCurrencyData returns landing counts and flight count for a user within a date range
	GetCurrencyData(ctx context.Context, userID uuid.UUID, since time.Time) (*models.CurrencyData, error)

	// SetSignatureLock sets (or, passing nil, clears) the flight's signature
	// lock pointer. Locked iff signatureID is non-nil.
	SetSignatureLock(ctx context.Context, flightID uuid.UUID, signatureID *uuid.UUID) error
}

type FlightSessionRepository interface {
	// Create creates a new flight session. Returns ErrDuplicate when the
	// user already has an open session.
	Create(ctx context.Context, session *models.FlightSession) error

	// GetOpenByUserID returns the user's open session, or ErrNotFound.
	GetOpenByUserID(ctx context.Context, userID uuid.UUID) (*models.FlightSession, error)

	// Update persists mutable session fields (timestamps, aircraft, route,
	// status, flight_id). Returns ErrNotFound when the session does not exist.
	Update(ctx context.Context, session *models.FlightSession) error
}

type FlightSignatureRepository interface {
	// Create creates a new flight signature row (live 'completed' or
	// deferred 'pending'). Returns ErrDuplicate if a pending row already
	// exists for the flight.
	Create(ctx context.Context, sig *models.FlightSignature) error

	// GetByID retrieves a signature by ID.
	GetByID(ctx context.Context, id uuid.UUID) (*models.FlightSignature, error)

	// GetByIDs batch-loads signatures, keyed by signature ID. IDs with no
	// row are absent from the map.
	GetByIDs(ctx context.Context, ids []uuid.UUID) (map[uuid.UUID]*models.FlightSignature, error)

	// GetByTokenHash retrieves a signature by its hashed token. Returns
	// ErrNotFound if no row has this hash, regardless of status.
	GetByTokenHash(ctx context.Context, tokenHash string) (*models.FlightSignature, error)

	// GetPendingByFlightID returns the flight's current pending request, or
	// ErrNotFound if none.
	GetPendingByFlightID(ctx context.Context, flightID uuid.UUID) (*models.FlightSignature, error)

	// ListByFlightID returns the full signature history for a flight, newest
	// first.
	// CountEmailsSentSince returns how many signature-request emails the user
	// has triggered since the given instant.
	CountEmailsSentSince(ctx context.Context, userID uuid.UUID, since time.Time) (int, error)
	ListByFlightID(ctx context.Context, flightID uuid.UUID) ([]*models.FlightSignature, error)

	// Update persists all mutable fields of a signature row.
	Update(ctx context.Context, sig *models.FlightSignature) error

	// ExpirePendingPastDue flips any 'pending' row whose token_expires_at
	// has passed to 'expired' and returns the number of rows affected.
	ExpirePendingPastDue(ctx context.Context) (int64, error)
}

type IdempotencyRepository interface {
	// Claim atomically takes ownership of (rec.UserID, rec.Key) for the
	// current request.
	//
	// It returns (nil, nil) when the claim succeeded, meaning the caller must
	// run the request and then either Complete or Release the record. It
	// returns the live record when the key is already held — either still
	// in progress, or completed and awaiting replay — and the caller must not
	// execute the request.
	//
	// A record that has expired, or whose in-progress claim was taken before
	// staleBefore, is taken over rather than reported as a conflict.
	Claim(ctx context.Context, rec *models.IdempotencyRecord, staleBefore time.Time) (*models.IdempotencyRecord, error)

	// Complete finalizes a claimed record with the captured response. It is a
	// no-op when the claim identified by rec.CreatedAt is no longer held.
	Complete(ctx context.Context, rec *models.IdempotencyRecord) error

	// Release drops a claim so the key can be used again. Like Complete it
	// only acts on a claim still owned by claimedAt.
	Release(ctx context.Context, userID uuid.UUID, key string, claimedAt time.Time) error

	// DeleteExpired removes records that expired at or before `before`.
	// Returns the number deleted.
	DeleteExpired(ctx context.Context, before time.Time) (int64, error)
}

type LicenseRepository interface {
	// Create creates a new license
	Create(ctx context.Context, license *models.License) error

	// GetByID retrieves a license by its ID
	GetByID(ctx context.Context, id uuid.UUID) (*models.License, error)

	// GetByUserID retrieves all licenses for a user. A non-nil updatedSince
	// narrows the result to licences changed strictly after that instant.
	GetByUserID(ctx context.Context, userID uuid.UUID, updatedSince *time.Time) ([]*models.License, error)

	// Update updates a license
	Update(ctx context.Context, license *models.License) error

	// Delete deletes a license
	Delete(ctx context.Context, id uuid.UUID) error
}

type MonthlyTrendRow struct {
	Month         string // "YYYY-MM"
	TotalFlights  int
	TotalMinutes  int
	PICMinutes    int
	DualMinutes   int
	NightMinutes  int
	IFRMinutes    int
	LandingsDay   int
	LandingsNight int
}

type NotificationRepository interface {
	GetPreferences(ctx context.Context, userID uuid.UUID) (*models.NotificationPreferences, error)
	UpsertPreferences(ctx context.Context, prefs *models.NotificationPreferences) error
	LogNotification(ctx context.Context, log *models.NotificationLog) error
	HasBeenSent(ctx context.Context, userID uuid.UUID, notificationType string, referenceID uuid.UUID, daysBeforeExpiry int, expiryReferenceDate *time.Time) (bool, error)
	GetAllUsersWithPreferences(ctx context.Context) ([]*models.NotificationPreferences, error)
	GetNotificationHistory(ctx context.Context, userID uuid.UUID, limit, offset int) ([]*models.NotificationLog, int, error)
}

type OIDCIdentityRepository interface {
	// GetBySubject returns the identity for an (issuer, subject) pair, or
	// ErrNotFound.
	GetBySubject(ctx context.Context, issuer, subject string) (*models.OIDCIdentity, error)

	// GetByUserID returns the identity linked to a local user, or ErrNotFound.
	GetByUserID(ctx context.Context, userID uuid.UUID) (*models.OIDCIdentity, error)

	// Create links an external identity to an existing user.
	Create(ctx context.Context, identity *models.OIDCIdentity) error

	// TouchLogin records a successful login and refreshes the cached email.
	TouchLogin(ctx context.Context, id uuid.UUID, email string, at time.Time) error

	// CreateLoginState stores the nonce and PKCE verifier for one pending
	// authorization request, keyed by the SHA-256 of the state parameter.
	CreateLoginState(ctx context.Context, state *models.OIDCLoginState) error

	// ConsumeLoginState atomically deletes and returns an unexpired login
	// state. Returns ErrNotFound for expired, already-consumed and forged
	// state values alike.
	ConsumeLoginState(ctx context.Context, stateHash []byte) (*models.OIDCLoginState, error)

	// CreateHandoffCode stores a single-use code the SPA can exchange for a
	// token pair, keyed by the SHA-256 of the code.
	CreateHandoffCode(ctx context.Context, code *models.OIDCHandoffCode) error

	// ConsumeHandoffCode atomically deletes and returns an unexpired handoff
	// code, or ErrNotFound.
	ConsumeHandoffCode(ctx context.Context, codeHash []byte) (*models.OIDCHandoffCode, error)

	// DeleteExpired removes expired login states and handoff codes. Returns
	// the number of rows deleted across both tables.
	DeleteExpired(ctx context.Context) (int64, error)
}

type PasswordResetTokenRepository interface {
	// Create creates a new password reset token
	Create(ctx context.Context, token *models.PasswordResetToken) error

	// GetByTokenHash retrieves a password reset token by its hash
	GetByTokenHash(ctx context.Context, tokenHash string) (*models.PasswordResetToken, error)

	// MarkAsUsed marks a password reset token as used
	MarkAsUsed(ctx context.Context, tokenHash string) error

	// DeleteExpired deletes expired password reset tokens
	DeleteExpired(ctx context.Context) error

	// DeleteForUser deletes all password reset tokens for a user
	DeleteForUser(ctx context.Context, userID uuid.UUID) error
}

type RefreshTokenRepository interface {
	// Create creates a new refresh token
	Create(ctx context.Context, token *models.RefreshToken) error

	// GetByTokenHash retrieves a refresh token by its hash
	GetByTokenHash(ctx context.Context, tokenHash string) (*models.RefreshToken, error)

	// RevokeByTokenHash revokes a refresh token outright, with no reuse grace.
	RevokeByTokenHash(ctx context.Context, tokenHash string) error

	// MarkRotated revokes a refresh token as superseded by a refresh, making
	// it eligible for the reuse grace window.
	MarkRotated(ctx context.Context, tokenHash string) error

	// RevokeAllForUser revokes all refresh tokens for a user
	RevokeAllForUser(ctx context.Context, userID uuid.UUID) error

	// DeleteForUser deletes all refresh tokens for a user
	DeleteForUser(ctx context.Context, userID uuid.UUID) error

	// DeleteExpired deletes expired refresh tokens
	DeleteExpired(ctx context.Context) error

	// TouchSession stamps last_used_at on every token in a session.
	TouchSession(ctx context.Context, sessionID uuid.UUID, at time.Time) error

	// ListSessions returns the user's live sessions, most recently used first.
	// A session is live when it holds at least one unrevoked, unexpired token.
	ListSessions(ctx context.Context, userID uuid.UUID) ([]*models.Session, error)

	// RevokeSession revokes every token in one session. It returns
	// ErrNotFound when the session does not exist or belongs to another user.
	RevokeSession(ctx context.Context, userID, sessionID uuid.UUID) error

	// RevokeSessionsExcept revokes every session the user holds apart from
	// keep, and returns how many sessions were revoked.
	RevokeSessionsExcept(ctx context.Context, userID, keep uuid.UUID) (int64, error)

	// EvictOldestSessions revokes the least recently used live sessions until
	// at most keep remain, and returns how many were evicted.
	EvictOldestSessions(ctx context.Context, userID uuid.UUID, keep int) (int64, error)

	// CountActiveSessions counts live sessions across all users.
	CountActiveSessions(ctx context.Context) (int64, error)

	// AccessTokenState reports the account's disabled flag and whether the
	// session still holds a live refresh token, in one round trip. It returns
	// ErrNotFound when no such user exists.
	AccessTokenState(ctx context.Context, userID, sessionID uuid.UUID) (disabled bool, live bool, err error)
}

type ReportsRepository interface {
	// RouteCounts returns every flown departure→arrival pair with counts,
	// most-flown first, across all time.
	RouteCounts(ctx context.Context, userID uuid.UUID) ([]*RouteCount, error)

	// AirportCounts returns per-airport leg counts split by direction,
	// across all time.
	AirportCounts(ctx context.Context, userID uuid.UUID) ([]*AirportDirectionCount, error)

	// MonthlyTrends returns the legacy per-month trend rows, oldest first.
	MonthlyTrends(ctx context.Context, userID uuid.UUID, months int) ([]*MonthlyTrendRow, error)

	// AircraftTypeTrends returns the legacy per-type rollup, most minutes first.
	AircraftTypeTrends(ctx context.Context, userID uuid.UUID, months int) ([]*AircraftTypeTrendRow, error)

	// StatsByClass returns per-class rollups, most minutes first.
	StatsByClass(ctx context.Context, userID uuid.UUID, months int) ([]*ClassStatRow, error)

	// StatsByCategory returns the overlapping capability rollups.
	StatsByCategory(ctx context.Context, userID uuid.UUID, months int) ([]*CategoryStatRow, error)

	// Totals returns the timeframe-wide aggregate.
	Totals(ctx context.Context, userID uuid.UUID, months int) (*AnalyticsTotals, error)

	// Monthly returns the analytics month series, oldest first.
	Monthly(ctx context.Context, userID uuid.UUID, months int) ([]*AnalyticsMonthRow, error)

	// Yearly returns the analytics year series, oldest first.
	Yearly(ctx context.Context, userID uuid.UUID, months int) ([]*AnalyticsYearRow, error)

	// ByAircraft groups by aircraft type, or by registration when
	// byRegistration is true, top `limit` rows by block time.
	ByAircraft(ctx context.Context, userID uuid.UUID, months, limit int, byRegistration bool) ([]*AnalyticsAircraftRow, error)

	// ByClass groups by aircraft class.
	ByClass(ctx context.Context, userID uuid.UUID, months int) ([]*AnalyticsGroupRow, error)

	// ByCategory returns the overlapping capability rollups.
	ByCategory(ctx context.Context, userID uuid.UUID, months int) ([]*AnalyticsGroupRow, error)

	// ByAirport returns the top `limit` airports by distinct flights.
	ByAirport(ctx context.Context, userID uuid.UUID, months, limit int) ([]*AnalyticsAirportRow, error)

	// ByRoute returns the top `limit` routes by flight count.
	ByRoute(ctx context.Context, userID uuid.UUID, months, limit int) ([]*AnalyticsRouteRow, error)

	// ByInstructor returns the top `limit` instructors by dual time received.
	ByInstructor(ctx context.Context, userID uuid.UUID, months, limit int) ([]*AnalyticsPersonRow, error)

	// ByCrew returns the top `limit` crew members by block time — one row
	// per person. Entries linked to the same contact are one person; unlinked
	// entries fold by trimmed, case-insensitive name.
	ByCrew(ctx context.Context, userID uuid.UUID, months, limit int) ([]*AnalyticsPersonRow, error)

	// ApproachTypes counts structured approach entries by type, most first.
	ApproachTypes(ctx context.Context, userID uuid.UUID, months int) ([]*AnalyticsApproachTypeRow, error)

	// ApproachesCountTotal sums the flights' approaches_count column, which
	// also covers legacy flights that carry only a count.
	ApproachesCountTotal(ctx context.Context, userID uuid.UUID, months int) (int, error)

	// DayOfWeekBuckets keys by ISO weekday (1=Mon..7=Sun).
	DayOfWeekBuckets(ctx context.Context, userID uuid.UUID, months int) (map[int]AnalyticsBucket, error)

	// MonthOfYearBuckets keys by month number (1..12).
	MonthOfYearBuckets(ctx context.Context, userID uuid.UUID, months int) (map[int]AnalyticsBucket, error)

	// HourOfDayBuckets keys by the hour of the off-block (or departure) time
	// (0..23); flights with neither timestamp are excluded.
	HourOfDayBuckets(ctx context.Context, userID uuid.UUID, months int) (map[int]AnalyticsBucket, error)

	// DurationBuckets keys by duration bucket index
	// (0: <30m, 1: 30-60m, 2: 1-2h, 3: 2-3h, 4: 3-5h, 5: >5h).
	DurationBuckets(ctx context.Context, userID uuid.UUID, months int) (map[int]AnalyticsBucket, error)

	// LongestFlight returns the flight with the most block time in the
	// timeframe, or nil when there are none.
	LongestFlight(ctx context.Context, userID uuid.UUID, months int) (*AnalyticsFlightRef, error)

	// FarthestFlight returns the flight with the greatest recorded distance,
	// or nil when there are none.
	FarthestFlight(ctx context.Context, userID uuid.UUID, months int) (*AnalyticsFlightRef, error)

	// BusiestDay returns the date with the most flights and its count, or
	// (nil, 0) when the timeframe holds no flights.
	BusiestDay(ctx context.Context, userID uuid.UUID, months int) (*time.Time, int, error)

	// LastFlightDate returns the date of the most recent flight across the
	// whole logbook (deliberately ignoring the timeframe), or nil.
	LastFlightDate(ctx context.Context, userID uuid.UUID) (*time.Time, error)

	// CarriedForwardMinutes sums block time of flights dated before the
	// timeframe's first month. Zero when months <= 0 (nothing precedes an
	// all-time window).
	CarriedForwardMinutes(ctx context.Context, userID uuid.UUID, months int) (int, error)
}

type RouteCount struct {
	DepartureICAO string
	ArrivalICAO   string
	FlightCount   int
}

type UserContentRepository interface {
	DeleteAllContent(ctx context.Context, userID uuid.UUID) error
}

type UserRepository interface {
	// Create creates a new user
	Create(ctx context.Context, user *models.User) error

	// GetByID retrieves a user by ID
	GetByID(ctx context.Context, id uuid.UUID) (*models.User, error)

	// GetByEmail retrieves a user by email
	GetByEmail(ctx context.Context, email string) (*models.User, error)

	// Update updates a user
	Update(ctx context.Context, user *models.User) error

	// Delete deletes a user
	Delete(ctx context.Context, id uuid.UUID) error

	// IncrementFailedLoginAttempts increments the failed login counter
	IncrementFailedLoginAttempts(ctx context.Context, id uuid.UUID) error

	// ResetFailedLoginAttempts resets the failed login counter to 0
	ResetFailedLoginAttempts(ctx context.Context, id uuid.UUID) error

	// LockAccount locks the account until the given time
	LockAccount(ctx context.Context, id uuid.UUID, until time.Time) error

	// UpdateLastLogin stamps the last successful login.
	UpdateLastLogin(ctx context.Context, id uuid.UUID, at time.Time) error

	// MarkEmailVerified flips the email_verified flag to true.
	// ConsumeRecoveryCode atomically removes a recovery code hash, returning
	// true only for the caller that actually removed it.
	ConsumeRecoveryCode(ctx context.Context, id uuid.UUID, codeHash string) (bool, error)
	MarkEmailVerified(ctx context.Context, id uuid.UUID) error

	// ListUnverifiedForReminder returns accounts that are still unverified,
	// were created before `createdBefore`, and have not yet been sent the
	// follow-up verification reminder. Accounts that have ever logged in and
	// accounts linked to an OIDC identity are excluded.
	ListUnverifiedForReminder(ctx context.Context, createdBefore time.Time, limit int) ([]*models.User, error)

	// MarkVerificationReminderSent stamps the reminder time.
	MarkVerificationReminderSent(ctx context.Context, id uuid.UUID, at time.Time) error

	// DeleteUnverifiedRemindedBefore deletes still-unverified accounts whose
	// reminder was sent before the given instant, and reports how many went.
	// It applies the same never-logged-in and non-OIDC guards as the listing.
	DeleteUnverifiedRemindedBefore(ctx context.Context, remindedBefore time.Time) (int64, error)
}

type WebAuthnCredentialRepository interface {
	Create(ctx context.Context, credential *models.WebAuthnCredential) error
	GetByID(ctx context.Context, id uuid.UUID) (*models.WebAuthnCredential, error)
	GetByCredentialID(ctx context.Context, credentialID []byte) (*models.WebAuthnCredential, error)
	GetByUserID(ctx context.Context, userID uuid.UUID) ([]*models.WebAuthnCredential, error)
	UpdateSignCount(ctx context.Context, id uuid.UUID, signCount uint32, lastUsedAt time.Time) error
	Delete(ctx context.Context, id uuid.UUID, userID uuid.UUID) error
}

type WebAuthnSessionRepository interface {
	// Create stores ceremony state keyed by the SHA-256 of the handle.
	Create(ctx context.Context, session *models.WebAuthnSession) error

	// Consume atomically deletes and returns the session if it exists, matches
	// the ceremony, and has not expired. Returns ErrNotFound otherwise.
	Consume(ctx context.Context, idHash []byte, ceremony string) (*models.WebAuthnSession, error)

	// DeleteOldestForUser keeps the newest `keep` sessions belonging to userID
	// and deletes the rest. Returns the number deleted.
	DeleteOldestForUser(ctx context.Context, userID uuid.UUID, keep int) (int64, error)

	// DeleteExpired removes expired rows. Returns the number deleted.
	DeleteExpired(ctx context.Context) (int64, error)
}