test(account): cover missing picker business date

docs/tests/integration/eligible-intern-picker.md

@@ -0,0 +1,161 @@
+# Test Evidence: Eligible Intern picker query
+
+- **Test type:** Integration
+- **Requirement IDs:** ACC-014, ACC-019–ACC-021, AUTH-001, PRJ-017, TST-001–TST-010
+- **Scenario IDs:** AC-ACC-009, AC-ACC-010, AC-PRJ-010 (selection-eligibility support)
+- **Test class/method:** com.lab.labtimesheet.feature.account.service.EligibleInternOptionIntegrationTest#listsOnlyActiveInternsWithActiveInclusiveInternshipsInPickerOrder; #rejectsMissingBusinessDate
+- **Implementation commit:** e70159a81b6445825f6d5f912ecf3c4aa3c1aa85
+
+## Protected behavior
+
+Pending, locked, deactivated, non-Intern, not-started, completed, and date-expired records must not appear in the
+Account-owned Intern picker. An option is selectable only when both account and internship are ACTIVE and the
+explicit business date lies within the inclusive internship range. The returned numeric user ID is the internal
+submission identity, and options sort by display name then student code.
+The public query rejects a missing business date with the documented actionable message instead of issuing an
+ambiguous null-bound database query.
+
+## Test method
+
+The PostgreSQL 18.4 integration test persists valid account/profile combinations through the account feature's JPA
+entities and repositories. It uses SQL only as a test fixture for future lock, deactivation, and completion states
+whose production transitions are outside this change. It calls the public Account service query and compares the
+complete immutable DTO sequence, including both inclusive date boundaries and unique user IDs.
+Its separate null-date regression calls the same public service method and asserts the exact
+<code>IllegalArgumentException</code> message documented by that method.
+
+## Hand-derived expected result
+
+For business date 2026-08-14, profiles starting on that date and ending on that date remain eligible. The only
+expected options are Alpha / STU-100, Alpha / STU-200, and Zeta / STU-300, in that order. Every other seeded
+row fails at least one account role/state, internship state, or inclusive date condition.
+For a missing business date, the service must immediately throw
+<code>IllegalArgumentException("Business date is required")</code>.
+
+## RED
+
+**Command**
+
+~~~text
+export JAVA_HOME=/opt/homebrew/opt/openjdk@25
+export PATH="$JAVA_HOME/bin:$PATH"
+export DOCKER_HOST=unix:///Users/sechmachine/.orbstack/run/docker.sock
+./mvnw -Dtest=EligibleInternOptionIntegrationTest test
+~~~
+
+**Observed result**
+
+~~~text
+[ERROR] EligibleInternOptionIntegrationTest.java:[11,54] cannot find symbol
+  symbol:   class EligibleInternOption
+  location: package com.lab.labtimesheet.feature.account.model.dto
+BUILD FAILURE
+~~~
+
+## GREEN
+
+**Command**
+
+~~~text
+export JAVA_HOME=/opt/homebrew/opt/openjdk@25
+export PATH="$JAVA_HOME/bin:$PATH"
+export DOCKER_HOST=unix:///Users/sechmachine/.orbstack/run/docker.sock
+./mvnw -Dtest=EligibleInternOptionIntegrationTest test
+~~~
+
+**Observed result**
+
+~~~text
+PostgreSQL 18.4 Testcontainers started and Flyway applied V1 baseline.
+Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
+BUILD SUCCESS
+~~~
+
+## Review follow-up: missing business date
+
+The public guard was temporarily removed solely to prove the new regression fails for the intended reason, then
+restored exactly before the GREEN checks. The follow-up commit contains only the regression test and evidence.
+
+### RED
+
+**Command**
+
+~~~text
+export JAVA_HOME=/opt/homebrew/opt/openjdk@25
+export PATH="$JAVA_HOME/bin:$PATH"
+export DOCKER_HOST=unix:///Users/sechmachine/.orbstack/run/docker.sock
+./mvnw '-Dtest=EligibleInternOptionIntegrationTest#rejectsMissingBusinessDate' test
+~~~
+
+**Observed result**
+
+~~~text
+Tests run: 1, Failures: 1, Errors: 0, Skipped: 0
+java.lang.AssertionError: Expecting code to raise a throwable.
+BUILD FAILURE
+~~~
+
+### GREEN
+
+**Command**
+
+~~~text
+export JAVA_HOME=/opt/homebrew/opt/openjdk@25
+export PATH="$JAVA_HOME/bin:$PATH"
+export DOCKER_HOST=unix:///Users/sechmachine/.orbstack/run/docker.sock
+./mvnw '-Dtest=EligibleInternOptionIntegrationTest#rejectsMissingBusinessDate' test
+~~~
+
+**Observed result**
+
+~~~text
+PostgreSQL 18.4 Testcontainers started and Flyway applied V1 baseline.
+Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
+BUILD SUCCESS
+~~~
+
+## Affected suite
+
+**Command and result**
+
+~~~text
+export JAVA_HOME=/opt/homebrew/opt/openjdk@25
+export PATH="$JAVA_HOME/bin:$PATH"
+export DOCKER_HOST=unix:///Users/sechmachine/.orbstack/run/docker.sock
+./mvnw -Dtest=EligibleInternOptionIntegrationTest,AccountActivationIntegrationTest,BootstrapIntegrationTest,AccountWebIntegrationTest,AuthenticationWebIntegrationTest,BootstrapOnboardingWebIntegrationTest test
+
+Selected account reports: 13 tests, 0 failures, 0 errors, 0 skipped.
+
+./mvnw -Dtest=AccountActivationIntegrationTest test
+Tests run: 2, Failures: 0, Errors: 0, Skipped: 0
+BUILD SUCCESS
+
+./mvnw -Dtest=LayerStructureTest test
+Tests run: 1, Failures: 0, Errors: 0, Skipped: 0
+BUILD SUCCESS
+
+./mvnw test
+Tests run: 105, Failures: 0, Errors: 0, Skipped: 0
+BUILD SUCCESS
+~~~
+
+### Review follow-up affected account-service checks
+
+~~~text
+export JAVA_HOME=/opt/homebrew/opt/openjdk@25
+export PATH="$JAVA_HOME/bin:$PATH"
+export DOCKER_HOST=unix:///Users/sechmachine/.orbstack/run/docker.sock
+./mvnw '-Dtest=EligibleInternOptionIntegrationTest,AccountActivationIntegrationTest,BootstrapIntegrationTest' test
+
+EligibleInternOptionIntegrationTest: 2 tests, 0 failures, 0 errors, 0 skipped
+BootstrapIntegrationTest: 4 tests, 0 failures, 0 errors, 0 skipped
+AccountActivationIntegrationTest: 2 tests, 0 failures, 0 errors, 0 skipped
+Selected account-service reports: 8 tests, 0 failures, 0 errors, 0 skipped.
+BUILD SUCCESS
+~~~
+
+## External-test boundaries
+
+This query does not authorize Project membership itself; the consuming Project transaction must still recheck
+membership and ownership invariants. It does not test the later lifecycle mutation workflows that produce locked,
+deactivated, or completed rows.

src/main/java/com/lab/labtimesheet/feature/account/model/dto/EligibleInternOption.java

@@ -0,0 +1,21 @@
+package com.lab.labtimesheet.feature.account.model.dto;
+
+import java.time.LocalDate;
+
+/**
+ * Immutable non-secret selection data for an eligible Intern.
+ * The numeric user ID is the internal form submission identity; displayed fields are not authorization identifiers.
+ *
+ * @param userId persistent account identifier submitted by a consuming form
+ * @param displayName user-facing Intern name
+ * @param studentCode university student code shown to distinguish Interns
+ * @param internshipStart inclusive internship eligibility start date
+ * @param internshipEnd inclusive internship eligibility end date
+ */
+public record EligibleInternOption(
+        long userId,
+        String displayName,
+        String studentCode,
+        LocalDate internshipStart,
+        LocalDate internshipEnd) {
+}

src/main/java/com/lab/labtimesheet/feature/account/repository/InternProfileRepository.java

@@ -1,8 +1,12 @@
 package com.lab.labtimesheet.feature.account.repository;
 
 import java.time.LocalDate;
+import java.util.List;
 
+import com.lab.labtimesheet.feature.account.model.AccountStatus;
+import com.lab.labtimesheet.feature.account.model.GlobalRole;
 import com.lab.labtimesheet.feature.account.model.InternshipStatus;
+import com.lab.labtimesheet.feature.account.model.dto.EligibleInternOption;
 import com.lab.labtimesheet.feature.account.model.entity.InternProfile;
 import jakarta.persistence.LockModeType;
 import org.springframework.data.jpa.repository.JpaRepository;
@@ -19,6 +23,34 @@ public interface InternProfileRepository extends JpaRepository<InternProfile, Lo
     boolean existsByUserIdAndInternshipStatusAndInternshipStartDateLessThanEqualAndInternshipEndDateGreaterThanEqual(
             Long userId, InternshipStatus status, LocalDate latestStartDate, LocalDate earliestEndDate);
 
+    /**
+     * Projects account-owned non-secret selection data for Interns eligible on one inclusive business date.
+     * Results are ordered by display name, student code, then user ID for deterministic form rendering.
+     *
+     * @param globalRole required immutable Intern role
+     * @param accountStatus required active account state
+     * @param internshipStatus required active internship state
+     * @param businessDate date that must fall within the inclusive internship range
+     * @return eligible Intern selection projections without duplicate profile rows
+     */
+    @Query("""
+            select new com.lab.labtimesheet.feature.account.model.dto.EligibleInternOption(
+                u.id, u.displayName, p.studentCode, p.internshipStartDate, p.internshipEndDate)
+            from InternProfile p
+            join AppUser u on u.id = p.userId
+            where u.globalRole = :globalRole
+              and u.accountStatus = :accountStatus
+              and p.internshipStatus = :internshipStatus
+              and p.internshipStartDate <= :businessDate
+              and p.internshipEndDate >= :businessDate
+            order by u.displayName asc, p.studentCode asc, u.id asc
+            """)
+    List<EligibleInternOption> findEligibleInternOptions(
+            @Param("globalRole") GlobalRole globalRole,
+            @Param("accountStatus") AccountStatus accountStatus,
+            @Param("internshipStatus") InternshipStatus internshipStatus,
+            @Param("businessDate") LocalDate businessDate);
+
     /** Counts Intern profiles in a lifecycle state. */
     long countByInternshipStatus(InternshipStatus status);
 

src/main/java/com/lab/labtimesheet/feature/account/service/AccountService.java

@@ -8,6 +8,7 @@ import java.time.Clock;
 import java.time.Duration;
 import java.time.LocalDate;
 import java.util.Base64;
+import java.util.List;
 
 import com.lab.labtimesheet.feature.account.model.AccountStatus;
 import com.lab.labtimesheet.feature.account.model.GlobalRole;
@@ -17,6 +18,7 @@ import com.lab.labtimesheet.feature.account.model.dto.AccountCreation;
 import com.lab.labtimesheet.feature.account.model.dto.AccountIdentity;
 import com.lab.labtimesheet.feature.account.model.dto.AccountSummary;
 import com.lab.labtimesheet.feature.account.model.dto.CreateAccountCommand;
+import com.lab.labtimesheet.feature.account.model.dto.EligibleInternOption;
 import com.lab.labtimesheet.feature.account.model.entity.AppUser;
 import com.lab.labtimesheet.feature.account.model.entity.InternProfile;
 import com.lab.labtimesheet.feature.account.model.entity.UserActionToken;
@@ -238,6 +240,24 @@ public class AccountService {
                 .isPresent();
     }
 
+    /**
+     * Lists non-secret Intern selection options eligible on an explicit business date.
+     * The result requires active account and internship states plus inclusive internship dates, but it does not
+     * authorize a consuming Project operation; that operation must recheck its own ownership and membership rules.
+     *
+     * @param businessDate server-derived business date to evaluate inclusively
+     * @return deterministic options ordered by display name, student code, then account ID
+     * @throws IllegalArgumentException when {@code businessDate} is {@code null}
+     */
+    @Transactional(readOnly = true)
+    public List<EligibleInternOption> eligibleInternOptions(LocalDate businessDate) {
+        if (businessDate == null) {
+            throw new IllegalArgumentException("Business date is required");
+        }
+        return internProfiles.findEligibleInternOptions(
+                GlobalRole.INTERN, AccountStatus.ACTIVE, InternshipStatus.ACTIVE, businessDate);
+    }
+
     /**
      * Resolves the cross-feature identity of a currently eligible Intern.
      *

src/test/java/com/lab/labtimesheet/feature/account/service/EligibleInternOptionIntegrationTest.java

@@ -0,0 +1,165 @@
+package com.lab.labtimesheet.feature.account.service;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.assertThatThrownBy;
+
+import java.sql.Timestamp;
+import java.time.Instant;
+import java.time.LocalDate;
+import java.util.List;
+
+import com.lab.labtimesheet.config.TestcontainersConfiguration;
+import com.lab.labtimesheet.feature.account.model.GlobalRole;
+import com.lab.labtimesheet.feature.account.model.dto.EligibleInternOption;
+import com.lab.labtimesheet.feature.account.model.entity.AppUser;
+import com.lab.labtimesheet.feature.account.model.entity.InternProfile;
+import com.lab.labtimesheet.feature.account.repository.AppUserRepository;
+import com.lab.labtimesheet.feature.account.repository.InternProfileRepository;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.boot.test.context.SpringBootTest;
+import org.springframework.context.annotation.Import;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.test.annotation.DirtiesContext;
+import org.springframework.test.context.ActiveProfiles;
+
+@Import(TestcontainersConfiguration.class)
+@SpringBootTest
+@ActiveProfiles("test")
+@DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD)
+class EligibleInternOptionIntegrationTest {
+    private static final Instant NOW = Instant.parse("2026-08-14T00:00:00Z");
+    private static final LocalDate BUSINESS_DATE = LocalDate.of(2026, 8, 14);
+
+    @Autowired
+    private AccountService accounts;
+
+    @Autowired
+    private AppUserRepository users;
+
+    @Autowired
+    private InternProfileRepository internProfiles;
+
+    @Autowired
+    private JdbcTemplate jdbc;
+
+    private AppUser admin;
+    private int userSequence;
+
+    @BeforeEach
+    void setUp() {
+        admin = users.saveAndFlush(AppUser.bootstrapAdmin(
+                "picker-admin@example.com", "Picker Admin", "encoded-password", NOW));
+    }
+
+    @Test
+    void listsOnlyActiveInternsWithActiveInclusiveInternshipsInPickerOrder() {
+        long lowerBoundary = activeIntern("Alpha", "STU-100", BUSINESS_DATE, BUSINESS_DATE.plusDays(10));
+        long upperBoundary = activeIntern("Alpha", "STU-200", BUSINESS_DATE.minusDays(10), BUSINESS_DATE);
+        long laterName = activeIntern("Zeta", "STU-300", BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1));
+
+        pendingInternWithActiveProfile("Ignored Pending", "STU-400");
+        long locked = activeIntern("Ignored Locked", "STU-500", BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1));
+        lock(locked);
+        long deactivated = activeIntern(
+                "Ignored Deactivated", "STU-600", BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1));
+        deactivate(deactivated);
+        activeMentorWithActiveProfile("Ignored Mentor", "STU-700");
+        activeInternWithNotStartedProfile("Ignored Not Started", "STU-800");
+        activeIntern("Ignored Ended", "STU-900", BUSINESS_DATE.minusDays(10), BUSINESS_DATE.minusDays(1));
+        long completed = activeIntern(
+                "Ignored Completed", "STU-1000", BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1));
+        completeInternship(completed);
+
+        List<EligibleInternOption> options = accounts.eligibleInternOptions(BUSINESS_DATE);
+
+        assertThat(options).containsExactly(
+                new EligibleInternOption(
+                        lowerBoundary, "Alpha", "STU-100", BUSINESS_DATE, BUSINESS_DATE.plusDays(10)),
+                new EligibleInternOption(
+                        upperBoundary, "Alpha", "STU-200", BUSINESS_DATE.minusDays(10), BUSINESS_DATE),
+                new EligibleInternOption(
+                        laterName, "Zeta", "STU-300", BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1)));
+        assertThat(options).extracting(EligibleInternOption::userId).doesNotHaveDuplicates();
+    }
+
+    @Test
+    void rejectsMissingBusinessDate() {
+        assertThatThrownBy(() -> accounts.eligibleInternOptions(null))
+                .isInstanceOf(IllegalArgumentException.class)
+                .hasMessage("Business date is required");
+    }
+
+    private long activeIntern(String displayName, String studentCode, LocalDate startDate, LocalDate endDate) {
+        long userId = activeUser(GlobalRole.INTERN, displayName);
+        activeProfile(userId, studentCode, startDate, endDate);
+        return userId;
+    }
+
+    private void pendingInternWithActiveProfile(String displayName, String studentCode) {
+        long userId = pendingUser(GlobalRole.INTERN, displayName);
+        activeProfile(userId, studentCode, BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1));
+    }
+
+    private void activeMentorWithActiveProfile(String displayName, String studentCode) {
+        long userId = activeUser(GlobalRole.MENTOR, displayName);
+        activeProfile(userId, studentCode, BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1));
+    }
+
+    private void activeInternWithNotStartedProfile(String displayName, String studentCode) {
+        long userId = activeUser(GlobalRole.INTERN, displayName);
+        internProfiles.saveAndFlush(InternProfile.notStarted(
+                userId, studentCode, BUSINESS_DATE.minusDays(1), BUSINESS_DATE.plusDays(1), NOW));
+    }
+
+    private long activeUser(GlobalRole role, String displayName) {
+        AppUser user = AppUser.pending(nextEmail(), displayName, role, admin, NOW);
+        user.activate("encoded-password", NOW);
+        return users.saveAndFlush(user).getId();
+    }
+
+    private long pendingUser(GlobalRole role, String displayName) {
+        return users.saveAndFlush(AppUser.pending(nextEmail(), displayName, role, admin, NOW)).getId();
+    }
+
+    private void activeProfile(long userId, String studentCode, LocalDate startDate, LocalDate endDate) {
+        InternProfile profile = InternProfile.notStarted(userId, studentCode, startDate, endDate, NOW);
+        profile.activate(NOW);
+        internProfiles.saveAndFlush(profile);
+    }
+
+    private void lock(long userId) {
+        assertThat(jdbc.update(
+                """
+                update app_users
+                set account_status = 'LOCKED', locked_at = ?, updated_at = ?
+                where id = ?
+                """,
+                Timestamp.from(NOW), Timestamp.from(NOW), userId)).isOne();
+    }
+
+    private void deactivate(long userId) {
+        assertThat(jdbc.update(
+                """
+                update app_users
+                set account_status = 'DEACTIVATED', deactivated_at = ?, updated_at = ?
+                where id = ?
+                """,
+                Timestamp.from(NOW), Timestamp.from(NOW), userId)).isOne();
+    }
+
+    private void completeInternship(long userId) {
+        assertThat(jdbc.update(
+                """
+                update intern_profiles
+                set internship_status = 'COMPLETED', completed_at = ?, updated_at = ?
+                where user_id = ?
+                """,
+                Timestamp.from(NOW), Timestamp.from(NOW), userId)).isOne();
+    }
+
+    private String nextEmail() {
+        return "picker-" + ++userSequence + "@example.com";
+    }
+}