View Javadoc
1   package fr.tiogars.chat.domains.task;
2   
3   import static org.junit.jupiter.api.Assertions.assertEquals;
4   import static org.junit.jupiter.api.Assertions.assertNotEquals;
5   import static org.junit.jupiter.api.Assertions.assertThrows;
6   import static org.junit.jupiter.api.Assertions.assertTrue;
7   import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.delete;
8   import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
9   import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.post;
10  import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.put;
11  import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
12  import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;
13  
14  import java.io.IOException;
15  import java.nio.charset.StandardCharsets;
16  import java.sql.Connection;
17  import java.sql.DriverManager;
18  import java.sql.SQLException;
19  import java.sql.Statement;
20  import java.time.Instant;
21  import java.util.List;
22  import java.util.Map;
23  
24  import com.jayway.jsonpath.JsonPath;
25  import org.apache.pdfbox.Loader;
26  import org.apache.pdfbox.pdmodel.PDDocument;
27  import org.apache.pdfbox.text.PDFTextStripper;
28  import org.junit.jupiter.api.AfterEach;
29  import org.junit.jupiter.api.BeforeEach;
30  import org.junit.jupiter.api.Tag;
31  import org.junit.jupiter.api.Test;
32  import org.springframework.beans.factory.annotation.Autowired;
33  import org.springframework.boot.test.context.SpringBootTest;
34  import org.springframework.boot.webmvc.test.autoconfigure.AutoConfigureMockMvc;
35  import org.springframework.core.io.ClassPathResource;
36  import org.springframework.dao.DataIntegrityViolationException;
37  import org.springframework.http.MediaType;
38  import org.springframework.jdbc.core.JdbcTemplate;
39  import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
40  import org.springframework.security.core.context.SecurityContextHolder;
41  import org.springframework.orm.ObjectOptimisticLockingFailureException;
42  import org.springframework.test.context.DynamicPropertyRegistry;
43  import org.springframework.test.context.DynamicPropertySource;
44  import org.springframework.test.web.servlet.MockMvc;
45  import org.testcontainers.junit.jupiter.Container;
46  import org.testcontainers.junit.jupiter.Testcontainers;
47  import org.testcontainers.postgresql.PostgreSQLContainer;
48  
49  import fr.tiogars.chat.domains.task.entities.TaskEntity;
50  import fr.tiogars.chat.domains.task.repositories.TaskRepository;
51  
52  @SpringBootTest
53  @AutoConfigureMockMvc(addFilters = false)
54  @Testcontainers
55  @Tag("Layer1")
56  class TaskAuditingL1Test {
57  
58      @Container
59      private static final PostgreSQLContainer POSTGRES = new PostgreSQLContainer("postgres:17-alpine")
60              .withInitScript("db/legacy-chats.sql");
61  
62      @Autowired
63      private MockMvc mvc;
64  
65      @Autowired
66      private JdbcTemplate jdbc;
67  
68      @Autowired
69      private TaskRepository repository;
70  
71      @DynamicPropertySource
72      static void databaseProperties(DynamicPropertyRegistry registry) throws SQLException, IOException {
73          try (Connection connection = DriverManager.getConnection(
74                  POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword());
75                  Statement statement = connection.createStatement()) {
76              statement.execute(migrationSql());
77              statement.execute(migrationSql());
78          }
79          registry.add("spring.datasource.url", POSTGRES::getJdbcUrl);
80          registry.add("spring.datasource.username", POSTGRES::getUsername);
81          registry.add("spring.datasource.password", POSTGRES::getPassword);
82          registry.add("spring.jpa.hibernate.ddl-auto", () -> "update");
83      }
84  
85      @BeforeEach
86      void authenticate() {
87          authenticateAs("creator");
88      }
89  
90      @AfterEach
91      void cleanUp() {
92          jdbc.update("DELETE FROM task_tags");
93          jdbc.update("DELETE FROM tasks WHERE code <> 'LEGACY'");
94          jdbc.update("DELETE FROM tags");
95          jdbc.update("UPDATE nomenclatures SET parent_id = NULL");
96          jdbc.update("DELETE FROM nomenclatures");
97          SecurityContextHolder.clearContext();
98      }
99  
100     @Test
101     void taskAttributesRoundtripThroughCrudSeedAndImportExport() throws Exception {
102         long tagId = createTaxonomy("/api/v1/tag", "{\"code\":\"TAG\",\"label\":\"Tag\"}");
103         long categoryId = createTaxonomy("/api/v1/nomenclature", "{\"code\":\"CAT\",\"label\":\"Category\"}");
104         String body = """
105                 {"code":"TASK","label":"Task","description":"Description","status":"IN_PROGRESS","priority":"URGENT",
106                  "dueDate":"2026-12-31","assignedTo":"alice","tagIds":[%d],"nomenclatureId":%d}
107                 """.formatted(tagId, categoryId);
108         String created = mvc.perform(post("/api/v1/task").contentType(MediaType.APPLICATION_JSON).content(body))
109                 .andExpect(status().isCreated()).andExpect(jsonPath("$.status").value("IN_PROGRESS"))
110                 .andExpect(jsonPath("$.priority").value("URGENT")).andExpect(jsonPath("$.dueDate").value("2026-12-31"))
111                 .andExpect(jsonPath("$.assignedTo").value("alice")).andExpect(jsonPath("$.tagIds[0]").value(tagId))
112                 .andExpect(jsonPath("$.nomenclatureId").value(categoryId)).andReturn().getResponse().getContentAsString();
113         Number id = JsonPath.read(created, "$.id");
114         mvc.perform(get("/api/v1/task/" + id)).andExpect(jsonPath("$.tagIds[0]").value(tagId));
115         mvc.perform(post("/api/v1/task/seed").contentType(MediaType.APPLICATION_JSON).content("[" + body + "]"))
116                 .andExpect(status().isCreated()).andExpect(jsonPath("$[0].priority").value("URGENT"));
117         String exported = mvc.perform(get("/api/v1/task/export.json")).andExpect(status().isOk())
118                 .andReturn().getResponse().getContentAsString();
119         String taskExport = new tools.jackson.databind.ObjectMapper().writeValueAsString(
120                 JsonPath.read(exported, "$[?(@.code == 'TASK')]"));
121         mvc.perform(post("/api/v1/task/import.json").contentType(MediaType.APPLICATION_JSON).content(taskExport))
122                 .andExpect(status().isCreated()).andExpect(jsonPath("$[?(@.code == 'TASK')].assignedTo").value("alice"));
123         String csv = mvc.perform(get("/api/v1/task/export.csv")).andExpect(status().isOk())
124                 .andReturn().getResponse().getContentAsString();
125         String taskCsv = csv.lines().filter(line -> line.startsWith("id,") || line.contains(",TASK,"))
126                 .collect(java.util.stream.Collectors.joining("\n")) + "\n";
127         mvc.perform(post("/api/v1/task/import.csv").contentType("text/csv").content(taskCsv))
128                 .andExpect(status().isCreated()).andExpect(jsonPath("$[?(@.code == 'TASK')].dueDate").value("2026-12-31"));
129         mvc.perform(put("/api/v1/task/" + id).contentType(MediaType.APPLICATION_JSON)
130                 .content("{\"label\":\"Reset\",\"description\":null}"))
131                 .andExpect(status().isOk()).andExpect(jsonPath("$.code").value("TASK"))
132                 .andExpect(jsonPath("$.status").value("TODO")).andExpect(jsonPath("$.priority").value("NORMAL"))
133                 .andExpect(jsonPath("$.tagIds").isEmpty());
134     }
135 
136     @Test
137     void taxonomyValidatesReferencesCyclesAndProtectedDeletion() throws Exception {
138         long tagId = createTaxonomy("/api/v1/tag", "{\"code\":\"TAG\",\"label\":\"Tag\"}");
139         long rootId = createTaxonomy("/api/v1/nomenclature", "{\"code\":\"ROOT\",\"label\":\"Root\"}");
140         long childId = createTaxonomy("/api/v1/nomenclature",
141                 "{\"code\":\"CHILD\",\"label\":\"Child\",\"parentId\":" + rootId + "}");
142         mvc.perform(put("/api/v1/nomenclature/" + rootId).contentType(MediaType.APPLICATION_JSON)
143                 .content("{\"label\":\"Root\",\"parentId\":" + childId + "}")).andExpect(status().isBadRequest());
144         mvc.perform(put("/api/v1/nomenclature/" + rootId).contentType(MediaType.APPLICATION_JSON)
145                 .content("{\"label\":\"Root\",\"parentId\":" + rootId + "}")).andExpect(status().isBadRequest());
146         mvc.perform(delete("/api/v1/nomenclature/" + rootId)).andExpect(status().isConflict());
147         mvc.perform(post("/api/v1/task").contentType(MediaType.APPLICATION_JSON)
148                 .content("{\"code\":\"BAD\",\"label\":\"Bad\",\"tagIds\":[999999]}")).andExpect(status().isBadRequest());
149         mvc.perform(post("/api/v1/task").contentType(MediaType.APPLICATION_JSON)
150                 .content("{\"code\":\"BAD\",\"label\":\"Bad\",\"nomenclatureId\":999999}")).andExpect(status().isBadRequest());
151         mvc.perform(post("/api/v1/nomenclature").contentType(MediaType.APPLICATION_JSON)
152                 .content("{\"code\":\"BAD\",\"label\":\"Bad\",\"parentId\":999999}")).andExpect(status().isBadRequest());
153         mvc.perform(post("/api/v1/task").contentType(MediaType.APPLICATION_JSON)
154                 .content("{\"code\":\"TASK\",\"label\":\"Task\",\"tagIds\":[" + tagId + "],\"nomenclatureId\":" + childId + "}"))
155                 .andExpect(status().isCreated());
156         mvc.perform(delete("/api/v1/tag/" + tagId)).andExpect(status().isConflict());
157         mvc.perform(delete("/api/v1/nomenclature/" + childId)).andExpect(status().isConflict());
158         mvc.perform(put("/api/v1/tag/" + tagId).contentType(MediaType.APPLICATION_JSON)
159                 .content("{\"label\":\"Updated\",\"description\":\"Reusable\"}"))
160                 .andExpect(status().isOk()).andExpect(jsonPath("$.code").value("TAG"))
161                 .andExpect(jsonPath("$.createdBy").value("creator"));
162     }
163 
164     private long createTaxonomy(String path, String body) throws Exception {
165         String response = mvc.perform(post(path).contentType(MediaType.APPLICATION_JSON).content(body))
166                 .andExpect(status().isCreated()).andReturn().getResponse().getContentAsString();
167         return ((Number) JsonPath.read(response, "$.id")).longValue();
168     }
169 
170     @Test
171     void migration_preservesLegacyRowsAndAllowsFreshDatabaseSetup() throws SQLException, IOException {
172         Map<String, Object> legacy = jdbc.queryForMap("SELECT * FROM tasks WHERE code = 'LEGACY'");
173         assertEquals("unknown", legacy.get("created_by"));
174         assertEquals("unknown", legacy.get("last_modified_by"));
175         assertEquals(Instant.parse("2026-08-01T00:00:00Z"),
176                 jdbc.queryForObject("SELECT created_date FROM tasks WHERE code = 'LEGACY'",
177                         java.sql.Timestamp.class).toInstant());
178 
179         try (Connection connection = DriverManager.getConnection(
180                 POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword());
181                 Statement statement = connection.createStatement()) {
182             statement.execute("CREATE SCHEMA fresh_database");
183             statement.execute("SET search_path TO fresh_database");
184             statement.execute(migrationSql());
185             try (var result = statement.executeQuery(
186                     "SELECT count(*) FROM pg_indexes WHERE schemaname = 'fresh_database' "
187                             + "AND indexname = 'uk_tasks_active_code'")) {
188                 assertTrue(result.next());
189                 assertEquals(1, result.getInt(1));
190             }
191             statement.execute("DROP SCHEMA fresh_database CASCADE");
192         }
193     }
194 
195     @Test
196     void migrationReplacesLegacyActiveCodeIndexWithTaskConstraintName() throws SQLException, IOException {
197         try (Connection connection = DriverManager.getConnection(
198                 POSTGRES.getJdbcUrl(), POSTGRES.getUsername(), POSTGRES.getPassword());
199                 Statement statement = connection.createStatement()) {
200             statement.execute("CREATE SCHEMA audited_legacy");
201             statement.execute("SET search_path TO audited_legacy");
202             statement.execute(migrationSql());
203             statement.execute("ALTER TABLE tasks RENAME TO domain_templates");
204             statement.execute("ALTER INDEX uk_tasks_active_code RENAME TO uk_domain_templates_active_code");
205             statement.execute(migrationSql());
206             statement.execute(migrationSql());
207             try (var result = statement.executeQuery(
208                     "SELECT indexname FROM pg_indexes WHERE schemaname = 'audited_legacy' "
209                             + "AND indexname IN ('uk_tasks_active_code', 'uk_domain_templates_active_code')")) {
210                 assertTrue(result.next());
211                 assertEquals("uk_tasks_active_code", result.getString(1));
212                 assertTrue(!result.next());
213             }
214             statement.execute("DROP SCHEMA audited_legacy CASCADE");
215         }
216     }
217 
218     @Test
219     void lifecycle_auditsActorsRetainsDeletedRowsAndReusesCodeWithNewId() throws Exception {
220         long originalId = create("CUSTOMER");
221         TaskEntity original = repository.findById(originalId).orElseThrow();
222         assertEquals("creator", original.getCreatedBy());
223         assertEquals("creator", original.getLastModifiedBy());
224         Instant createdDate = original.getCreatedDate();
225         assertEquals(createdDate, original.getLastModifiedDate());
226         assertEquals(0, createdDate.getNano() % 1000);
227 
228         authenticateAs("editor");
229         String response = mvc.perform(put("/api/v1/task/{id}", originalId)
230                 .contentType(MediaType.APPLICATION_JSON)
231                 .content("{\"label\":\"Updated\",\"description\":\"Edited\"}"))
232                 .andExpect(status().isOk()).andReturn().getResponse().getContentAsString();
233         TaskEntity updated = repository.findById(originalId).orElseThrow();
234         assertEquals("creator", updated.getCreatedBy());
235         assertEquals("editor", updated.getLastModifiedBy());
236         assertEquals(createdDate, updated.getCreatedDate());
237         assertTrue(updated.getLastModifiedDate().isAfter(createdDate));
238         assertEquals(updated.getLastModifiedDate(), Instant.parse(JsonPath.read(response, "$.lastModifiedDate")));
239 
240         authenticateAs("deleter");
241         mvc.perform(delete("/api/v1/task/{id}", originalId)).andExpect(status().isNoContent());
242         Map<String, Object> archived = jdbc.queryForMap("SELECT * FROM tasks WHERE id = ?", originalId);
243         assertEquals("CUSTOMER", archived.get("code"));
244         assertEquals("creator", archived.get("created_by"));
245         assertEquals("deleter", archived.get("deleted_by"));
246         assertEquals("deleter", archived.get("last_modified_by"));
247         assertTrue(archived.get("deleted_date") != null);
248         assertTrue(repository.findById(originalId).isEmpty());
249         assertTrue(repository.findByCode("CUSTOMER").isEmpty());
250         assertTrue(!repository.existsById(originalId));
251         mvc.perform(get("/api/v1/task/{id}", originalId)).andExpect(status().isNotFound());
252         mvc.perform(put("/api/v1/task/{id}", originalId)
253                 .contentType(MediaType.APPLICATION_JSON).content("{\"label\":\"Resurrect\"}"))
254                 .andExpect(status().isNotFound());
255         mvc.perform(delete("/api/v1/task/{id}", originalId)).andExpect(status().isNotFound());
256         String exported = mvc.perform(get("/api/v1/task/export.json"))
257                 .andExpect(status().isOk()).andReturn().getResponse().getContentAsString();
258         List<String> exportedCodes = JsonPath.read(exported, "$[*].code");
259         assertTrue(!exportedCodes.contains("CUSTOMER"));
260         mvc.perform(get("/api/v1/task/export.csv"))
261                 .andExpect(result -> assertTrue(!result.getResponse().getContentAsString().contains("CUSTOMER")));
262 
263         authenticateAs("replacementCreator");
264         long replacementId = create("CUSTOMER");
265         assertNotEquals(originalId, replacementId);
266         assertEquals(2, jdbc.queryForObject(
267                 "SELECT count(*) FROM tasks WHERE code = 'CUSTOMER'", Integer.class));
268         mvc.perform(get("/api/v1/task"))
269                 .andExpect(jsonPath("$[0].id").value(replacementId))
270                 .andExpect(jsonPath("$.length()").value(2));
271         mvc.perform(post("/api/v1/task").contentType(MediaType.APPLICATION_JSON)
272                 .content("{\"code\":\"CUSTOMER\",\"label\":\"Duplicate\"}")).andExpect(status().isConflict());
273         assertThrows(DataIntegrityViolationException.class,
274                 () -> repository.saveAndFlush(new TaskEntity("CUSTOMER", "Duplicate", null)));
275     }
276 
277     @Test
278     void pdfPrinting_excludesArchivedRowsAndOnlyPrintsSelectedActiveRecords() throws Exception {
279         long archivedId = create("ARCHIVED_PDF");
280         long selectedId = create("SELECTED_PDF");
281         create("UNSELECTED_PDF");
282         mvc.perform(delete("/api/v1/task/{id}", archivedId)).andExpect(status().isNoContent());
283 
284         mvc.perform(get("/api/v1/task/{id}/print.pdf", archivedId)).andExpect(status().isNotFound());
285         mvc.perform(post("/api/v1/task/print-selected.pdf")
286                 .contentType(MediaType.APPLICATION_JSON)
287                 .content("{\"ids\":[" + selectedId + "," + archivedId + "]}")).andExpect(status().isNotFound());
288 
289         byte[] selectedPdf = mvc.perform(post("/api/v1/task/print-selected.pdf")
290                 .contentType(MediaType.APPLICATION_JSON).content("{\"ids\":[" + selectedId + "]}"))
291                 .andExpect(status().isOk()).andReturn().getResponse().getContentAsByteArray();
292         try (PDDocument pdf = Loader.loadPDF(selectedPdf)) {
293             String text = new PDFTextStripper().getText(pdf);
294             assertTrue(text.contains("SELECTED_PDF"));
295             assertTrue(!text.contains("UNSELECTED_PDF"));
296             assertTrue(!text.contains("ARCHIVED_PDF"));
297             assertEquals(1, pdf.getNumberOfPages());
298         }
299 
300         byte[] allPdf = mvc.perform(get("/api/v1/task/print.pdf"))
301                 .andExpect(status().isOk()).andReturn().getResponse().getContentAsByteArray();
302         try (PDDocument pdf = Loader.loadPDF(allPdf)) {
303             String text = new PDFTextStripper().getText(pdf);
304             assertTrue(text.contains("SELECTED_PDF"));
305             assertTrue(text.contains("UNSELECTED_PDF"));
306             assertTrue(!text.contains("ARCHIVED_PDF"));
307         }
308     }
309 
310     @Test
311     void staleUpdate_cannotOverwriteAnArchivedRow() throws Exception {
312         long id = create("STALE");
313         TaskEntity stale = repository.findById(id).orElseThrow();
314         mvc.perform(delete("/api/v1/task/{id}", id)).andExpect(status().isNoContent());
315         stale.update("Stale update", null);
316 
317         assertThrows(ObjectOptimisticLockingFailureException.class, () -> repository.saveAndFlush(stale));
318         assertEquals("Original", jdbc.queryForObject(
319                 "SELECT label FROM tasks WHERE id = ?", String.class, id));
320     }
321 
322     @Test
323     void importsAndSeed_createNewRowsForArchivedCodesAndAuditUpserts() throws Exception {
324         long oldId = create("IMPORT");
325         mvc.perform(delete("/api/v1/task/{id}", oldId)).andExpect(status().isNoContent());
326         authenticateAs("importer");
327         mvc.perform(post("/api/v1/task/import.json").contentType(MediaType.APPLICATION_JSON)
328                 .content("[{\"code\":\"IMPORT\",\"label\":\"Imported\",\"createdBy\":\"spoofed\"}]"))
329                 .andExpect(status().isCreated());
330         TaskEntity imported = repository.findByCode("IMPORT").orElseThrow();
331         assertNotEquals(oldId, imported.getId());
332         assertEquals("importer", imported.getCreatedBy());
333 
334         authenticateAs("seeder");
335         mvc.perform(post("/api/v1/task/seed").contentType(MediaType.APPLICATION_JSON)
336                 .content("[{\"code\":\"IMPORT\",\"label\":\"Seeded\"}]")).andExpect(status().isCreated());
337         assertEquals("seeder", repository.findByCode("IMPORT").orElseThrow().getLastModifiedBy());
338         mvc.perform(delete("/api/v1/task/{id}", imported.getId())).andExpect(status().isNoContent());
339 
340         authenticateAs("csvImporter");
341         mvc.perform(post("/api/v1/task/import.csv").contentType("text/csv")
342                 .content("id,code,label,description,createdDate,lastModifiedDate\n"
343                         + "999,IMPORT,CSV import,,,\n")).andExpect(status().isCreated());
344         TaskEntity csvImported = repository.findByCode("IMPORT").orElseThrow();
345         assertNotEquals(imported.getId(), csvImported.getId());
346         assertEquals("csvImporter", csvImported.getCreatedBy());
347         assertEquals(3, jdbc.queryForObject(
348                 "SELECT count(*) FROM tasks WHERE code = 'IMPORT'", Integer.class));
349     }
350 
351     private long create(String code) throws Exception {
352         String response = mvc.perform(post("/api/v1/task").contentType(MediaType.APPLICATION_JSON)
353                 .content("{\"code\":\"" + code + "\",\"label\":\"Original\"}"))
354                 .andExpect(status().isCreated()).andReturn().getResponse().getContentAsString();
355         Number id = JsonPath.read(response, "$.id");
356         return id.longValue();
357     }
358 
359     private static void authenticateAs(String username) {
360         SecurityContextHolder.getContext().setAuthentication(
361                 UsernamePasswordAuthenticationToken.authenticated(username, null, List.of()));
362     }
363 
364     private static String migrationSql() throws IOException {
365         return new ClassPathResource("db/manual/task-domains.sql")
366                 .getContentAsString(StandardCharsets.UTF_8);
367     }
368 }