Cleanup & Refactor DAL layer (remove redundant try/catch, fix ID heuristics, concurrency, guest assignments)

[CarlosCanet]

Summary

Refactor and clean the Data Access Layer (DAL) modules to remove noisy patterns, fix ID/day ambiguity bugs, address a race condition in image ordering, optimize inefficient queries, and update assignment logic to support guestName. This improves reliability, performance, and correctness without changing existing external APIs (unless noted for guest support).

Motivation

Current DAL code contains repetitive catch/rethrow blocks, ambiguous parameter handling between UUID vs numeric day values, a potential race condition when inserting ordered images, inefficient data fetching for single image retrieval, and outdated assumptions after adding guestName to assignments.

Scope

Target files (indicative, adjust if naming differs):

• dal-tea.ts
• dal-story-tea.ts
• dal-story-image.ts
• dal-day-assignment.ts
• Any other DAL modules using the same patterns (e.g. dal-day.ts for heuristic reference only)

Tasks / Checklist

1. Remove redundant try/catch blocks

   • Delete all DAL-layer try { ... } catch (err) { console.error(err); throw err; } blocks that simply rethrow.
   • Ensure calling Server Actions or route handlers handle errors appropriately.

2. Fix ID vs day number ambiguity in getTea and getStoryTea

   • Implement numeric detection heuristic (replicate logic from getDay): if the input can be parsed as a positive integer (and not a UUID format), treat it as dayNumber + year; otherwise treat as entity UUID.
   • Add inline validation for impossible numeric cases (e.g., outside 1–25 range) and throw descriptive error.
   • Add unit tests covering: numeric string ("1"), UUID, invalid numeric ("99"), and mixed input.

3. Fix race condition in addStoryImage

   • Replace separate read-then-create logic with a Prisma $transaction.
   • Inside transaction: use count (or max(order)) and then create with order = count (or max + 1).
   • Add test simulating concurrent inserts (can be approximate with Promise.all) to confirm unique sequential ordering.

4. Optimize getStoryImage

   • Replace full tea/story/images fetch with minimal lookup: fetch story ID by day/year, then fetch single image by storyTeaId + order.
   • Throw a precise error if story or image not found.
   • Add test ensuring only one image record is queried (spy/mocked Prisma client if feasible).

5. Update day assignment logic for guests (dal-day-assignment.ts)

   • Modify addDayAssignment to accept either userId OR guestName (one required) plus dayNumber and year.
   • Ensure constraints: a day can be assigned once per year; a user has at most one assignment per year; guestName should also respect uniqueness per year if intended.
   • Add functions:
     • getAssignmentByDay(dayNumber, year) – returns assignment regardless of user or guest.
     • getAssignmentsForYear(year) – optional for admin overview.
     • getGuestAssignments(guestName, year) – to retrieve guest-specific assignment.
   • Add validation that both userId and guestName are not provided simultaneously.
   • Update or add tests covering user assignment, guest assignment, conflict cases.

6. Preserve existing good logic

   • Do not alter working transaction logic in editTea (except to integrate new heuristic if needed).
   • Keep delete functions unchanged except for removing redundant try/catch blocks.

Non-Goals

• UI changes
• Modifying public API shapes beyond adding guest support to DAL function parameters
• Performance micro-optimizations outside the specified functions

Acceptance Criteria

• All specified DAL functions compile and tests pass.
• No remaining redundant catch/rethrow patterns in DAL files.
• Numeric vs UUID heuristic works consistently; documented in code comments for future readers.
• addStoryImage uses a transaction preventing duplicate order values under concurrent insertion attempts.
• getStoryImage performs only the two minimal queries (story ID + single image fetch).
• Guest assignment logic supports adding and querying guest entries without requiring an email.
• New/updated unit tests cover error cases and new logic paths.
• Existing working transaction logic (e.g., editTea) remains intact and green.

Testing Strategy

• Unit tests for heuristics (ID vs number) and assignment functions.
• Concurrency test (approximate) for addStoryImage using Promise.all.
• Query efficiency test for getStoryImage (mock prisma or inspect call count if feasible).

Risks & Mitigations

• Race condition fix may introduce unexpected locking behavior: mitigate by using simple count or max order with single transaction.
• Heuristic misclassification: mitigate via explicit validation and tests.
• Guest logic may require minor schema review: confirm existing fields (guestName) and uniqueness constraints.

Follow-Up (Optional / Future)

• Introduce pagination for listing assignments/images if volume grows.
• Add optimistic concurrency control if assignments become highly contentious.

---

Please link this issue in related commits (e.g., refactor: remove DAL try/catch (#ISSUE_NUMBER)).