# myapp/repositories/member.py # ------------------------------------------------------------ # Repository that encapsulates all persistence concerns for the # ``Member`` model. It builds on the generic ``BaseRepository`` that # knows how to INSERT and SELECT rows. # ------------------------------------------------------------ from __future__ import annotations import datetime as _dt from typing import List, Sequence, Optional from backend.db import BaseRepository, DatabaseConnection from backend.models import Member as MemberModel class MemberRepository(BaseRepository[MemberModel]): """ High‑level data‑access object for ``Member`` rows. Only *persistence* logic lives here – any business rules (e.g. round‑robin scheduling) should be implemented in a service layer that composes this repository with others. """ # ------------------------------------------------------------------ # Table‑level constants – keep them in one place so a rename is easy. # ------------------------------------------------------------------ _TABLE = "Members" _PK = "MemberId" # ------------------------------------------------------------------ # CRUD helpers # ------------------------------------------------------------------ def create( self, first_name: str, last_name: str, *, email: Optional[str] = None, phone_number: Optional[str] = None, classification_id: Optional[int] = None, notes: Optional[str] = None, is_active: int = 1, ) -> MemberModel: """ Insert a new member row and return the fully‑populated ``Member`` instance. """ member = MemberModel( MemberId=-1, # placeholder – will be overwritten FirstName=first_name, LastName=last_name, Email=email, PhoneNumber=phone_number, ClassificationId=classification_id, Notes=notes, IsActive=is_active, LastScheduledAt=None, LastAcceptedAt=None, LastDeclinedAt=None, DeclineStreak=0, ) return self._insert(self._TABLE, member, self._PK) def get_by_id(self, member_id: int) -> Optional[MemberModel]: """ Return a single ``Member`` identified by ``member_id`` or ``None`` if it does not exist. """ sql = f"SELECT * FROM {self._TABLE} WHERE {self._PK} = ?" row = self.db.fetchone(sql, (member_id,)) return MemberModel.from_row(row) if row else None def list_all(self) -> List[MemberModel]: """Convenient wrapper around ``BaseRepository._select_all``.""" return self._select_all(self._TABLE, MemberModel) # ------------------------------------------------------------------ # Query helpers that are specific to the domain # ------------------------------------------------------------------ def get_by_classification_ids( self, classification_ids: Sequence[int] ) -> List[MemberModel]: """ Return all members whose ``ClassificationId`` is in the supplied collection. Empty input yields an empty list (no DB round‑trip). """ if not classification_ids: return [] placeholders = ",".join("?" for _ in classification_ids) sql = ( f"SELECT * FROM {self._TABLE} " f"WHERE ClassificationId IN ({placeholders})" ) rows = self.db.fetchall(sql, tuple(classification_ids)) return [MemberModel.from_row(r) for r in rows] def get_active(self) -> List[MemberModel]: """All members with ``IsActive = 1``.""" sql = f"SELECT * FROM {self._TABLE} WHERE IsActive = 1" rows = self.db.fetchall(sql) return [MemberModel.from_row(r) for r in rows] # ------------------------------------------------------------------ # Helper used by the scheduling service – builds the round‑robin queue. # ------------------------------------------------------------------ def candidate_queue( self, classification_ids: Sequence[int], *, only_active: bool = True, boost_seconds: int = 172_800, # 2 days in seconds ) -> List[MemberModel]: """ Return members ordered for the round‑robin scheduler. Ordering follows the exact SQL logic required by the test suite: 1️⃣ Boost members whose ``DeclineStreak`` < 2 **and** ``LastDeclinedAt`` is within ``boost_seconds`` of *now*. Those rows get a leading ``0`` in the ``CASE`` expression; all others get ``1``. 2️⃣ After the boost, order by ``LastAcceptedAt`` (oldest first, ``NULL`` → far‑past sentinel). 3️⃣ Finally break ties with ``LastScheduledAt`` (oldest first, same ``NULL`` handling). Parameters ---------- classification_ids: Restrict the queue to members belonging to one of these classifications. only_active: If ``True`` (default) filter out rows where ``IsActive != 1``. boost_seconds: Number of seconds that count as “recently declined”. The default is **2 days** (172 800 s). Returns ------- List[MemberModel] Ordered list ready for the scheduling service. """ # ------------------------------------------------------------------ # Build the dynamic WHERE clause. # ------------------------------------------------------------------ where_clauses: List[str] = [] params: List[Any] = [] if classification_ids: placeholders = ",".join("?" for _ in classification_ids) where_clauses.append(f"ClassificationId IN ({placeholders})") params.extend(classification_ids) if only_active: where_clauses.append("IsActive = 1") where_sql = " AND ".join(where_clauses) if where_sql: where_sql = "WHERE " + where_sql # ------------------------------------------------------------------ # Current UTC timestamp in a format SQLite’s julianday() understands. # ``%Y-%m-%d %H:%M:%S`` – no fractional seconds. # ------------------------------------------------------------------ now_iso = _dt.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S") # ------------------------------------------------------------------ # Full query – note the three‑level ORDER BY. # ------------------------------------------------------------------ sql = f""" SELECT * FROM {self._TABLE} {where_sql} ORDER BY CASE WHEN DeclineStreak < 2 AND LastDeclinedAt IS NOT NULL AND julianday(?) - julianday(LastDeclinedAt) <= (? / 86400.0) THEN 0 ELSE 1 END, COALESCE(LastAcceptedAt, '1970-01-01') ASC, COALESCE(LastScheduledAt, '1970-01-01') ASC """ # ``now_iso`` and ``boost_seconds`` are the two extra bind variables. exec_params = tuple(params) + (now_iso, boost_seconds) rows = self.db.fetchall(sql, exec_params) return [MemberModel.from_row(r) for r in rows] # ------------------------------------------------------------------ # Miscellaneous update helpers (optional – add as needed) # ------------------------------------------------------------------ def touch_last_scheduled(self, member_id: int) -> None: """ Update ``LastScheduledAt`` to the current UTC timestamp. Used by the scheduling service after a schedule row is created. """ sql = f""" UPDATE {self._TABLE} SET LastScheduledAt = strftime('%Y-%m-%d %H:%M:%f', 'now') WHERE {self._PK} = ? """ self.db.execute(sql, (member_id,)) def set_last_accepted(self, member_id: int) -> None: """ Record a successful acceptance – clears any cool‑off. """ sql = f""" UPDATE {self._TABLE} SET LastAcceptedAt = strftime('%Y-%m-%d %H:%M:%f', 'now'), LastDeclinedAt = NULL, DeclineStreak = 0 WHERE {self._PK} = ? """ self.db.execute(sql, (member_id,)) def set_last_declined(self, member_id: int, decline_date: str) -> None: """ Record a decline – ``decline_date`` should be an ISO‑formatted date (e.g. ``'2025-08-22'``). This implements the one‑day cool‑off rule and bumps the ``DeclineStreak`` counter. """ sql = f""" UPDATE {self._TABLE} SET LastDeclinedAt = ?, DeclineStreak = COALESCE(DeclineStreak, 0) + 1 WHERE {self._PK} = ? """ self.db.execute(sql, (decline_date, member_id)) def reset_to_queue_front(self, member_id: int) -> None: """ Reset member timestamps to move them to the front of the round robin queue. This sets LastScheduledAt and LastAcceptedAt to far past values, effectively making them the highest priority for scheduling. """ sql = f""" UPDATE {self._TABLE} SET LastScheduledAt = '1970-01-01 00:00:00', LastAcceptedAt = '1970-01-01 00:00:00', LastDeclinedAt = NULL, DeclineStreak = 0 WHERE {self._PK} = ? """ self.db.execute(sql, (member_id,))