thcathy · Nov 4, 2026 · Nov 3, 2026 · Nov 3, 2026 · Nov 3, 2026 · Nov 4, 2026
diff --git a/src/main/java/com/esl/dao/PhoneticQuestionDAO.java b/src/main/java/com/esl/dao/PhoneticQuestionDAO.java
@@ -33,6 +33,19 @@ public PhoneticQuestion getPhoneticQuestionByWord(String word) {
 		}
 	}
 
+	public long countByWord(String word) {
+		return em.createQuery("select count(q) from PhoneticQuestion q where q.word = :word", Long.class)
+				.setParameter("word", word)
+				.getSingleResult();
+	}
+
+	public void insertIfAbsent(PhoneticQuestion question) {
+		if (countByWord(question.getWord()) > 0) {
+			return;
+		}
+		persist(question);
+	}
+
 	@Deprecated
 	public List<PhoneticQuestion> getNotEnrichedQuestions() {
 		return em.createQuery(GET_NOT_ENRICHED_QUESTIONS).setMaxResults(300).getResultList();

diff --git a/src/main/java/com/esl/model/PhoneticQuestion.java b/src/main/java/com/esl/model/PhoneticQuestion.java
@@ -12,7 +12,7 @@
 import static jakarta.persistence.GenerationType.IDENTITY;
 
 @Entity
-@Table(name = "phonetic_question")
+@Table(name = "phonetic_question", uniqueConstraints = @UniqueConstraint(name = "UK_PHONETIC_QUESTION_WORD", columnNames = "WORD"))
 public class PhoneticQuestion implements Serializable {
 	private static Logger logger = LoggerFactory.getLogger(PhoneticQuestion.class);
 

diff --git a/src/main/java/com/esl/service/PhoneticQuestionService.java b/src/main/java/com/esl/service/PhoneticQuestionService.java
@@ -9,6 +9,7 @@
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.dao.DataIntegrityViolationException;
 import org.springframework.stereotype.Service;
 import org.springframework.web.client.RestTemplate;
 
@@ -41,9 +42,24 @@ public PhoneticQuestion buildQuestionByWebAPI(String word) {
         question.setPicsFullPaths(new String[] { "" });
 
         fillQuestionByDictionaryResult(question, webService.queryDictionary(word).join());
+        persistEnrichedQuestion(question);
         return question;
     }
 
+    private void persistEnrichedQuestion(PhoneticQuestion question) {
+        if (!question.enriched()) {
+            return;
+        }
+        // Rank 0 sits below every VocabDifficulty range, so random practice never selects write-backs.
+        question.setRank(0);
+        try {
+            phoneticQuestionDAO.insertIfAbsent(question);
+        } catch (DataIntegrityViolationException e) {
+            log.info("Skipped phonetic_question insert for '{}': {}",
+                    question.getWord(), e.getMostSpecificCause().getMessage());
+        }
+    }
+
     private void fillQuestionByDictionaryResult(PhoneticQuestion question, Optional<DictionaryResult> r) {
         if (r.isEmpty()) {
             question.setIPAUnavailable(true);

diff --git a/src/test/java/com/esl/service/PhoneticQuestionServiceTest.java b/src/test/java/com/esl/service/PhoneticQuestionServiceTest.java
@@ -1,78 +1,102 @@
 package com.esl.service;
 
+import com.esl.dao.PhoneticQuestionDAO;
 import com.esl.entity.rest.DictionaryResult;
+import com.esl.model.PhoneticQuestion;
 import com.esl.service.rest.WebParserRestService;
+import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.MethodSource;
+import org.mockito.ArgumentCaptor;
 import org.springframework.web.client.RestTemplate;
 
 import java.util.Optional;
 import java.util.concurrent.CompletableFuture;
+import java.util.stream.Stream;
 
 import static org.junit.jupiter.api.Assertions.*;
 import static org.mockito.Mockito.*;
 import static org.springframework.test.util.ReflectionTestUtils.setField;
 
 class PhoneticQuestionServiceTest {
 
-    @Test
-    void buildQuestionByWebAPI_whenDictionaryResolvesToDifferentWord_shouldSkipPronunciationUrl() {
-        // Given
-        var webService = mock(WebParserRestService.class);
-        var restTemplate = mock(RestTemplate.class);
-
-        // Query is "learning" but dictionary resolves to base form "learn"
-        var dictionaryResult = new DictionaryResult(
-                "learn",
-                "https://example.com/learn.mp3",
-                "en-US",
-                "lɜːn",
-                "definition"
-        );
-        when(webService.queryDictionary("learning"))
-                .thenReturn(CompletableFuture.completedFuture(Optional.of(dictionaryResult)));
+    private WebParserRestService webService;
+    private PhoneticQuestionDAO phoneticQuestionDAO;
+    private PhoneticQuestionService service;
 
-        var service = new PhoneticQuestionService();
+    @BeforeEach
+    void setUp() {
+        webService = mock(WebParserRestService.class);
+        phoneticQuestionDAO = mock(PhoneticQuestionDAO.class);
+        service = new PhoneticQuestionService();
         setField(service, "webService", webService);
-        setField(service, "restTemplate", restTemplate);
+        setField(service, "phoneticQuestionDAO", phoneticQuestionDAO);
+        setField(service, "restTemplate", mock(RestTemplate.class));
+    }
+
+    @Test
+    void buildQuestionByWebAPI_whenDictionaryResolvesToDifferentWord_shouldSkipPronunciationUrlAndNotPersist() {
+        when(webService.queryDictionary("learning"))
+                .thenReturn(CompletableFuture.completedFuture(Optional.of(
+                        new DictionaryResult("learn", "https://example.com/learn.mp3", "en-US", "lɜːn", "definition"))));
 
-        // When
         var question = service.buildQuestionByWebAPI("learning");
 
-        // Then
         assertEquals("learning", question.getWord());
         assertTrue(question.isIPAUnavailable(), "Should mark IPA unavailable when dictionary word mismatches request");
         assertNull(question.getPronouncedLink(), "Should skip pronunciationUrl so client falls back to TTS");
+        assertArrayEquals(new String[] { "" }, question.getPicsFullPaths());
         verify(webService, never()).searchGoogleImage(anyString(), anyInt());
+        verify(phoneticQuestionDAO, never()).insertIfAbsent(any());
     }
 
     @Test
-    void buildQuestionByWebAPI_whenDictionaryMatchesWord_shouldUsePronunciationUrl() {
-        // Given
-        var webService = mock(WebParserRestService.class);
-        var restTemplate = mock(RestTemplate.class);
-
-        var dictionaryResult = new DictionaryResult(
-                "learning",
-                "https://example.com/learning.mp3",
-                "en-US",
-                "ˈlɜːnɪŋ",
-                "definition"
-        );
-        when(webService.queryDictionary("learning"))
-                .thenReturn(CompletableFuture.completedFuture(Optional.of(dictionaryResult)));
-
-        var service = new PhoneticQuestionService();
-        setField(service, "webService", webService);
-        setField(service, "restTemplate", restTemplate);
+    void buildQuestionByWebAPI_whenDictionaryMatchesWord_shouldPersistSlimRowWithRankZero() {
+        when(webService.queryDictionary("Learning"))
+                .thenReturn(CompletableFuture.completedFuture(Optional.of(
+                        new DictionaryResult("learning", "https://example.com/learning.mp3", "en-US", "ˈlɜːnɪŋ", "definition"))));
 
-        // When
-        var question = service.buildQuestionByWebAPI("learning");
+        var question = service.buildQuestionByWebAPI("Learning");
 
-        // Then
-        assertEquals("learning", question.getWord());
-        assertFalse(question.isIPAUnavailable(), "Should keep IPA available when dictionary provides IPA for exact match");
+        assertEquals("Learning", question.getWord());
+        assertFalse(question.isIPAUnavailable());
         assertEquals("ˈlɜːnɪŋ", question.getIPA());
         assertEquals("https://example.com/learning.mp3", question.getPronouncedLink());
+        assertEquals(0, question.getRank());
+        assertArrayEquals(new String[] { "" }, question.getPicsFullPaths());
+
+        var captor = ArgumentCaptor.forClass(PhoneticQuestion.class);
+        verify(phoneticQuestionDAO).insertIfAbsent(captor.capture());
+        var saved = captor.getValue();
+        assertEquals("Learning", saved.getWord());
+        assertEquals("ˈlɜːnɪŋ", saved.getIPA());
+        assertEquals("https://example.com/learning.mp3", saved.getPronouncedLink());
+        assertEquals(0, saved.getRank());
+        assertNull(saved.getPicFileName());
+        assertNull(saved.getPronouncedLinkBackup());
+        assertTrue(saved.getGrades().isEmpty());
     }
-}
 
+    @ParameterizedTest
+    @MethodSource("incompleteDictionaryResults")
+    void buildQuestionByWebAPI_whenNotEnriched_shouldNotPersist(Optional<DictionaryResult> result) {
+        when(webService.queryDictionary("partial")).thenReturn(CompletableFuture.completedFuture(result));
+
+        var question = service.buildQuestionByWebAPI("partial");
+
+        assertFalse(question.enriched());
+        assertArrayEquals(new String[] { "" }, question.getPicsFullPaths());
+        verify(phoneticQuestionDAO, never()).insertIfAbsent(any());
+    }
+
+    static Stream<Optional<DictionaryResult>> incompleteDictionaryResults() {
+        return Stream.of(
+                Optional.empty(),
+                Optional.of(new DictionaryResult("partial", null, "en-US", "ˈpɑːʃəl", "definition")),
+                Optional.of(new DictionaryResult("partial", "   ", "en-US", "ˈpɑːʃəl", "definition")),
+                Optional.of(new DictionaryResult("partial", "https://example.com/partial.mp3", "en-US", null, "definition")),
+                Optional.of(new DictionaryResult("partial", "https://example.com/partial.mp3", "en-US", "  ", "definition"))
+        );
+    }
+}