Refactor database interactions: replace direct database calls with DB variable and implement batch creation for images and producers

10 files changed, 182 insertions, 63 deletions

diff --git a/entities/meta.go b/entities/meta.go
index deb1e08..57ebae7 100644
--- a/entities/meta.go
+++ b/entities/meta.go
@@ -69,11 +69,11 @@ type ExternalURL struct {
 
 type SimpleTitle struct {
 	gorm.Model
-	Type  string `json:"type,omitempty"`
-	Title string `json:"title,omitempty"`
+	Type  string `gorm:"uniqueIndex:idx_simple_title" json:"type,omitempty"`
+	Title string `gorm:"uniqueIndex:idx_simple_title" json:"title,omitempty"`
 }
 
 type SimpleImage struct {
 	gorm.Model
-	ImageURL string `json:"image_url,omitempty"`
+	ImageURL string `gorm:"uniqueIndex" json:"image_url,omitempty"`
 }
diff --git a/repositories/anime.go b/repositories/anime.go
index 04d3ab7..b6fe0b8 100644
--- a/repositories/anime.go
+++ b/repositories/anime.go
@@ -3,7 +3,6 @@ package repositories
 import (
 	"errors"
 	"fmt"
-	"metachan/database"
 	"metachan/entities"
 	"metachan/enums"
 	"metachan/utils/logger"
@@ -21,7 +20,7 @@ func GetAnime[T idType](maptype enums.MappingType, id T) (entities.Anime, error)
 		return entities.Anime{}, errors.New("anime not found")
 	}
 
-	result := database.DB.
+	result := DB.
 		Preload("Mapping").
 		Preload("Title").
 		Preload("Images").
@@ -75,12 +74,12 @@ func CreateOrUpdateAnime(anime *entities.Anime) error {
 	}
 
 	var existingAnime entities.Anime
-	result := database.DB.Where("mal_id = ?", anime.MALID).First(&existingAnime)
+	result := DB.Where("mal_id = ?", anime.MALID).First(&existingAnime)
 	if result.Error == nil {
 		anime.ID = existingAnime.ID
 	}
 
-	result = database.DB.Session(&gorm.Session{FullSaveAssociations: true}).Clauses(clause.OnConflict{
+	result = DB.Session(&gorm.Session{FullSaveAssociations: true}).Clauses(clause.OnConflict{
 		UpdateAll: true,
 	}).Save(anime)
 
@@ -97,11 +96,11 @@ func SaveEpisodeSkipTimes(episodeID string, skipTimes []entities.EpisodeSkipTime
 		return nil
 	}
 
-	database.DB.Where("episode_id = ?", episodeID).Delete(&entities.EpisodeSkipTime{})
+	DB.Where("episode_id = ?", episodeID).Delete(&entities.EpisodeSkipTime{})
 
 	for i := range skipTimes {
 		skipTimes[i].EpisodeID = episodeID
-		if err := database.DB.Create(&skipTimes[i]).Error; err != nil {
+		if err := DB.Create(&skipTimes[i]).Error; err != nil {
 			return fmt.Errorf("failed to save skip time: %w", err)
 		}
 	}
@@ -112,7 +111,7 @@ func SaveEpisodeSkipTimes(episodeID string, skipTimes []entities.EpisodeSkipTime
 func GetAiringAnime() ([]entities.Anime, error) {
 	var anime []entities.Anime
 
-	result := database.DB.
+	result := DB.
 		Where("airing = ?", true).
 		Preload("NextAiring").
 		Preload("Schedule").
diff --git a/repositories/genre.go b/repositories/genre.go
index ed27db4..0d95f66 100644
--- a/repositories/genre.go
+++ b/repositories/genre.go
@@ -2,7 +2,6 @@ package repositories
 
 import (
 	"errors"
-	"metachan/database"
 	"metachan/entities"
 	"metachan/utils/logger"
 
@@ -10,7 +9,7 @@ import (
 )
 
 func CreateOrUpdateGenre(genre *entities.Genre) error {
-	result := database.DB.Clauses(clause.OnConflict{
+	result := DB.Clauses(clause.OnConflict{
 		Columns:   []clause.Column{{Name: "genre_id"}},
 		DoUpdates: clause.AssignmentColumns([]string{"name", "url", "count"}),
 	}).Create(genre)
diff --git a/repositories/mapping.go b/repositories/mapping.go
index bbcda8d..145bb43 100644
--- a/repositories/mapping.go
+++ b/repositories/mapping.go
@@ -3,7 +3,6 @@ package repositories
 import (
 	"errors"
 	"fmt"
-	"metachan/database"
 	"metachan/entities"
 	"metachan/enums"
 	"metachan/utils/logger"
@@ -14,7 +13,7 @@ import (
 func GetAnimeMapping[T idType](maptype enums.MappingType, id T) (entities.Mapping, error) {
 	var mapping entities.Mapping
 
-	result := database.DB.Where(fmt.Sprintf("%s = ?", maptype), id).First(&mapping)
+	result := DB.Where(fmt.Sprintf("%s = ?", maptype), id).First(&mapping)
 
 	if result.Error != nil {
 		logger.Errorf("Mapping", "Failed to get mapping for %s with ID %v: %v", maptype, id, result.Error)
@@ -25,7 +24,7 @@ func GetAnimeMapping[T idType](maptype enums.MappingType, id T) (entities.Mappin
 }
 
 func CreateOrUpdateMapping(mapping *entities.Mapping) error {
-	result := database.DB.Clauses(clause.OnConflict{
+	result := DB.Clauses(clause.OnConflict{
 		Columns: []clause.Column{{Name: "mal"}},
 		DoUpdates: clause.AssignmentColumns([]string{
 			"ani_db", "anilist", "anime_countdown", "anime_planet",
@@ -45,7 +44,7 @@ func CreateOrUpdateMapping(mapping *entities.Mapping) error {
 func GetAllMappings() ([]entities.Mapping, error) {
 	var mappings []entities.Mapping
 
-	result := database.DB.Find(&mappings)
+	result := DB.Find(&mappings)
 	if result.Error != nil {
 		logger.Errorf("Mapping", "Failed to fetch all mappings: %v", result.Error)
 		return nil, errors.New("failed to fetch mappings")
diff --git a/repositories/meta.go b/repositories/meta.go
index 3ab4543..34487a4 100644
--- a/repositories/meta.go
+++ b/repositories/meta.go
@@ -2,7 +2,6 @@ package repositories
 
 import (
 	"errors"
-	"metachan/database"
 	"metachan/entities"
 	"metachan/utils/logger"
 
@@ -10,7 +9,7 @@ import (
 )
 
 func CreateOrUpdateSimpleImage(image *entities.SimpleImage) (uint, error) {
-	result := database.DB.Clauses(clause.OnConflict{
+	result := DB.Clauses(clause.OnConflict{
 		Columns:   []clause.Column{{Name: "image_url"}},
 		DoUpdates: clause.AssignmentColumns([]string{"image_url"}),
 	}).Create(image)
@@ -23,8 +22,44 @@ func CreateOrUpdateSimpleImage(image *entities.SimpleImage) (uint, error) {
 	return image.ID, nil
 }
 
+func BatchCreateSimpleImages(images []entities.SimpleImage) error {
+	if len(images) == 0 {
+		return nil
+	}
+
+	result := DB.Clauses(clause.OnConflict{
+		Columns:   []clause.Column{{Name: "image_url"}},
+		DoNothing: true,
+	}).CreateInBatches(&images, 100)
+
+	if result.Error != nil {
+		logger.Errorf("Meta", "Failed to batch create images: %v", result.Error)
+		return errors.New("failed to batch create images")
+	}
+
+	return nil
+}
+
+func BatchCreateSimpleTitles(titles []entities.SimpleTitle) error {
+	if len(titles) == 0 {
+		return nil
+	}
+
+	result := DB.Clauses(clause.OnConflict{
+		Columns:   []clause.Column{{Name: "type"}, {Name: "title"}},
+		DoNothing: true,
+	}).CreateInBatches(&titles, 100)
+
+	if result.Error != nil {
+		logger.Errorf("Meta", "Failed to batch create titles: %v", result.Error)
+		return errors.New("failed to batch create titles")
+	}
+
+	return nil
+}
+
 func CreateOrUpdateSimpleTitle(title *entities.SimpleTitle) (uint, error) {
-	result := database.DB.Clauses(clause.OnConflict{
+	result := DB.Clauses(clause.OnConflict{
 		Columns:   []clause.Column{{Name: "type"}, {Name: "title"}},
 		DoUpdates: clause.AssignmentColumns([]string{"type", "title"}),
 	}).Create(title)
@@ -38,7 +73,7 @@ func CreateOrUpdateSimpleTitle(title *entities.SimpleTitle) (uint, error) {
 }
 
 func CreateOrUpdateExternalURL(url *entities.ExternalURL) (uint, error) {
-	result := database.DB.Clauses(clause.OnConflict{
+	result := DB.Clauses(clause.OnConflict{
 		Columns:   []clause.Column{{Name: "name"}, {Name: "url"}},
 		DoUpdates: clause.AssignmentColumns([]string{"name", "url"}),
 	}).Create(url)
diff --git a/repositories/producer.go b/repositories/producer.go
index dade0d1..946d873 100644
--- a/repositories/producer.go
+++ b/repositories/producer.go
@@ -2,15 +2,15 @@ package repositories
 
 import (
 	"errors"
-	"metachan/database"
 	"metachan/entities"
 	"metachan/utils/logger"
 
+	"gorm.io/gorm"
 	"gorm.io/gorm/clause"
 )
 
 func CreateOrUpdateProducer(producer *entities.Producer) error {
-	result := database.DB.Clauses(clause.OnConflict{
+	result := DB.Clauses(clause.OnConflict{
 		Columns: []clause.Column{{Name: "mal_id"}},
 		DoUpdates: clause.AssignmentColumns([]string{
 			"url", "favorites", "count", "established", "about", "image_id",
@@ -24,3 +24,23 @@ func CreateOrUpdateProducer(producer *entities.Producer) error {
 
 	return nil
 }
+
+func BatchCreateProducers(producers []entities.Producer) error {
+	if len(producers) == 0 {
+		return nil
+	}
+
+	result := DB.Session(&gorm.Session{FullSaveAssociations: true}).Clauses(clause.OnConflict{
+		Columns: []clause.Column{{Name: "mal_id"}},
+		DoUpdates: clause.AssignmentColumns([]string{
+			"url", "favorites", "count", "established", "about", "image_id",
+		}),
+	}).CreateInBatches(&producers, 100)
+
+	if result.Error != nil {
+		logger.Errorf("Producer", "Failed to batch create producers: %v", result.Error)
+		return errors.New("failed to batch create producers")
+	}
+
+	return nil
+}
diff --git a/repositories/tasks.go b/repositories/tasks.go
index 6a52e7a..097186f 100644
--- a/repositories/tasks.go
+++ b/repositories/tasks.go
@@ -2,7 +2,6 @@ package repositories
 
 import (
 	"errors"
-	"metachan/database"
 	"metachan/entities"
 	"metachan/utils/logger"
 )
@@ -10,7 +9,7 @@ import (
 func GetTaskStatus(taskName string) (entities.TaskStatus, error) {
 	var taskStatus entities.TaskStatus
 
-	result := database.DB.Where("task_name = ?", taskName).First(&taskStatus)
+	result := DB.Where("task_name = ?", taskName).First(&taskStatus)
 
 	if result.Error != nil {
 		return entities.TaskStatus{}, errors.New("task status not found")
@@ -20,7 +19,7 @@ func GetTaskStatus(taskName string) (entities.TaskStatus, error) {
 }
 
 func SetTaskStatus(task *entities.TaskStatus) error {
-	result := database.DB.Save(task)
+	result := DB.Save(task)
 
 	if result.Error != nil {
 		logger.Errorf("Task", "Failed to set task status for %s: %v", task.TaskName, result.Error)
@@ -34,7 +33,7 @@ func SetTaskStatus(task *entities.TaskStatus) error {
 func GetLatestTaskLog(taskName string) (*entities.TaskLog, error) {
 	var taskLog entities.TaskLog
 
-	result := database.DB.Where("task_name = ?", taskName).Order("executed_at desc").First(&taskLog)
+	result := DB.Where("task_name = ?", taskName).Order("executed_at desc").First(&taskLog)
 	if result.Error != nil {
 		return nil, result.Error
 	}
@@ -43,7 +42,7 @@ func GetLatestTaskLog(taskName string) (*entities.TaskLog, error) {
 }
 
 func CreateTaskLog(taskLog *entities.TaskLog) error {
-	result := database.DB.Create(taskLog)
+	result := DB.Create(taskLog)
 	if result.Error != nil {
 		logger.Errorf("Task", "Failed to create task log: %v", result.Error)
 		return errors.New("failed to create task log")
diff --git a/repositories/types.go b/repositories/types.go
index ccc04f2..dcce328 100644
--- a/repositories/types.go
+++ b/repositories/types.go
@@ -1,5 +1,13 @@
 package repositories
 
+import (
+	"metachan/database"
+
+	"gorm.io/gorm"
+)
+
+var DB *gorm.DB = database.DB
+
 type idType interface {
 	~int | ~string
 }
diff --git a/tasks/producersync.task.go b/tasks/producersync.task.go
index 59864ce..6949e70 100644
--- a/tasks/producersync.task.go
+++ b/tasks/producersync.task.go
@@ -21,58 +21,110 @@ func ProducerSync() error {
 	logger.Infof("ProducerSync", "Fetched %d producers from MAL", total)
 
 	startTime := time.Now()
+	const batchSize = 10
+	totalProcessed := 0
 
-	for i, producerData := range response.Data {
-		producerDetail, err := jikan.GetProducerByID(producerData.MALID)
-		if err != nil {
-			logger.Warnf("ProducerSync", "Failed to fetch details for producer %d: %v", producerData.MALID, err)
-			continue
+	for batchStart := 0; batchStart < total; batchStart += batchSize {
+		batchEnd := min(batchStart+batchSize, total)
+
+		batchData := response.Data[batchStart:batchEnd]
+
+		type producerWithImage struct {
+			producer entities.Producer
+			imageURL string
 		}
 
-		var imageID *uint
-		if producerDetail.Data.Images.JPG.ImageURL != "" {
-			image := entities.SimpleImage{
-				ImageURL: producerDetail.Data.Images.JPG.ImageURL,
+		producersData := make([]producerWithImage, 0, len(batchData))
+		imageMap := make(map[string]struct{})
+
+		for i, producerData := range batchData {
+			producerDetail, err := jikan.GetProducerByID(producerData.MALID)
+			if err != nil {
+				logger.Warnf("ProducerSync", "Failed to fetch details for producer %d: %v", producerData.MALID, err)
+				continue
 			}
-			id, err := repositories.CreateOrUpdateSimpleImage(&image)
-			if err == nil {
-				imageID = &id
+
+			producer := entities.Producer{
+				MALID:       producerDetail.Data.MALID,
+				URL:         producerDetail.Data.URL,
+				Favorites:   producerDetail.Data.Favorites,
+				Count:       producerDetail.Data.Count,
+				Established: producerDetail.Data.Established,
+				About:       producerDetail.Data.About,
+			}
+
+			for _, title := range producerDetail.Data.Titles {
+				producer.Titles = append(producer.Titles, entities.SimpleTitle{
+					Type:  title.Type,
+					Title: title.Title,
+				})
+			}
+
+			for _, ext := range producerDetail.Data.External {
+				producer.ExternalURLs = append(producer.ExternalURLs, entities.ExternalURL{
+					Name: ext.Name,
+					URL:  ext.URL,
+				})
+			}
+
+			imageURL := producerDetail.Data.Images.JPG.ImageURL
+			if imageURL != "" {
+				imageMap[imageURL] = struct{}{}
+			}
+
+			producersData = append(producersData, producerWithImage{
+				producer: producer,
+				imageURL: imageURL,
+			})
+
+			if (batchStart+i+1)%10 == 0 || (batchStart+i+1) == total {
+				progress, eta := calculateProgress(batchStart+i+1, total, startTime)
+				logger.Infof("ProducerSync", "Fetched: %d/%d (%.1f%%) | ETA: %v", batchStart+i+1, total, progress, eta)
 			}
 		}
 
-		producer := entities.Producer{
-			MALID:       producerDetail.Data.MALID,
-			URL:         producerDetail.Data.URL,
-			Favorites:   producerDetail.Data.Favorites,
-			Count:       producerDetail.Data.Count,
-			Established: producerDetail.Data.Established,
-			About:       producerDetail.Data.About,
-			ImageID:     imageID,
+		if len(imageMap) > 0 {
+			images := make([]entities.SimpleImage, 0, len(imageMap))
+			for url := range imageMap {
+				images = append(images, entities.SimpleImage{ImageURL: url})
+			}
+			if err := repositories.BatchCreateSimpleImages(images); err != nil {
+				logger.Errorf("ProducerSync", "Failed to batch insert images: %v", err)
+				return err
+			}
 		}
 
-		for _, title := range producerDetail.Data.Titles {
-			producer.Titles = append(producer.Titles, entities.SimpleTitle{
-				Type:  title.Type,
-				Title: title.Title,
-			})
+		var dbImages []entities.SimpleImage
+		if err := repositories.DB.Select("id, image_url").Find(&dbImages).Error; err != nil {
+			logger.Errorf("ProducerSync", "Failed to query images: %v", err)
+			return err
 		}
 
-		for _, ext := range producerDetail.Data.External {
-			producer.ExternalURLs = append(producer.ExternalURLs, entities.ExternalURL{
-				Name: ext.Name,
-				URL:  ext.URL,
-			})
+		imageIDMap := make(map[string]uint)
+		for _, img := range dbImages {
+			imageIDMap[img.ImageURL] = img.ID
 		}
 
-		if err := repositories.CreateOrUpdateProducer(&producer); err != nil {
-			logger.Warnf("ProducerSync", "Failed to sync producer %d: %v", producerData.MALID, err)
-			continue
+		producers := make([]entities.Producer, 0, len(producersData))
+		for _, pd := range producersData {
+			if pd.imageURL != "" {
+				if id, exists := imageIDMap[pd.imageURL]; exists {
+					pd.producer.ImageID = &id
+				}
+			}
+			producers = append(producers, pd.producer)
 		}
 
-		progress, eta := calculateProgress(i+1, total, startTime)
-		logger.Infof("ProducerSync", "Progress: %d/%d (%.1f%%) | ETA: %v", i+1, total, progress, eta)
+		if len(producers) > 0 {
+			if err := repositories.BatchCreateProducers(producers); err != nil {
+				logger.Errorf("ProducerSync", "Failed to batch insert producers: %v", err)
+				return err
+			}
+			totalProcessed += len(producers)
+			logger.Infof("ProducerSync", "Committed batch: %d producers (Total: %d/%d)", len(producers), totalProcessed, total)
+		}
 	}
 
-	logger.Successf("ProducerSync", "Producer sync completed. Total: %d producers", total)
+	logger.Successf("ProducerSync", "Producer sync completed. Total: %d producers", totalProcessed)
 	return nil
 }
diff --git a/utils/api/jikan/jikan.go b/utils/api/jikan/jikan.go
index 87c7cfd..244253d 100644
--- a/utils/api/jikan/jikan.go
+++ b/utils/api/jikan/jikan.go
@@ -108,7 +108,15 @@ func (c *client) makeRequest(ctx context.Context, url string) ([]byte, error) {
 			}
 
 			return bytes, nil
+		case http.StatusNotFound:
+			logger.Warnf("JikanClient", "Resource not found: %s", url)
+			return nil, errors.New("resource not found")
 		default:
+			if response.StatusCode >= 400 && response.StatusCode < 500 {
+				logger.Warnf("JikanClient", "Client error %d for %s", response.StatusCode, url)
+				return nil, fmt.Errorf("client error: status %d", response.StatusCode)
+			}
+
 			retries++
 			backoffDuration := c.getBackOffDuration(retries)