3 files changed, 98 insertions, 2 deletions

diff --git a/pkg/database/repository/media.go b/pkg/database/repository/media.go
index 2e94ff3..6ab4ee6 100644
--- a/pkg/database/repository/media.go
+++ b/pkg/database/repository/media.go
@@ -34,6 +34,10 @@ type (
 		GPSLongitude    *float64
 	}
 
+	MediaThumbnail struct {
+		Path string
+	}
+
 	Pagination struct {
 		Page int
 		Size int
@@ -52,10 +56,15 @@ type (
 		List(context.Context, *Pagination) ([]*Media, error)
 		Get(context.Context, string) (*Media, error)
 		GetPath(context.Context, string) (string, error)
+		GetThumbnailPath(context.Context, string) (string, error)
 
-		GetEmptyEXIF(context.Context, *Pagination) ([]*Media, error)
+		ListEmptyEXIF(context.Context, *Pagination) ([]*Media, error)
 		GetEXIF(context.Context, uint) (*MediaEXIF, error)
 		CreateEXIF(context.Context, uint, *MediaEXIF) error
+
+		ListEmptyThumbnail(context.Context, *Pagination) ([]*Media, error)
+		GetThumbnail(context.Context, uint) (*MediaThumbnail, error)
+		CreateThumbnail(context.Context, uint, *MediaThumbnail) error
 	}
 )
 
diff --git a/pkg/database/sql/media.go b/pkg/database/sql/media.go
index 27f8cf0..b8203f3 100644
--- a/pkg/database/sql/media.go
+++ b/pkg/database/sql/media.go
@@ -41,6 +41,13 @@ type (
 		GPSLongitude    *float64
 	}
 
+	MediaThumbnail struct {
+		gorm.Model
+		Path    string
+		MediaID uint
+		Media   Media
+	}
+
 	MediaRepository struct {
 		db *gorm.DB
 	}
@@ -79,6 +86,12 @@ func (m *MediaEXIF) ToModel() *repository.MediaEXIF {
 	}
 }
 
+func (m *MediaThumbnail) ToModel() *repository.MediaThumbnail {
+	return &repository.MediaThumbnail{
+		Path: m.Path,
+	}
+}
+
 func NewMediaRepository(db *gorm.DB) *MediaRepository {
 	return &MediaRepository{
 		db: db,
@@ -173,6 +186,24 @@ func (self *MediaRepository) GetPath(ctx context.Context, pathHash string) (stri
 	return path, nil
 }
 
+func (self *MediaRepository) GetThumbnailPath(ctx context.Context, pathHash string) (string, error) {
+	var path string
+	result := self.db.
+		WithContext(ctx).
+		Model(&Media{}).
+		Select("media_thumbnails.path").
+		Joins("left join media_thumbnails on media.id = media_thumbnails.media_id").
+		Where("media.path_hash = ?", pathHash).
+		Limit(1).
+		Find(&path)
+
+	if result.Error != nil {
+		return "", result.Error
+	}
+
+	return path, nil
+}
+
 func (m *MediaRepository) GetEXIF(ctx context.Context, mediaID uint) (*repository.MediaEXIF, error) {
 	exif := &MediaEXIF{}
 	result := m.db.
@@ -220,7 +251,7 @@ func (s *MediaRepository) CreateEXIF(ctx context.Context, id uint, info *reposit
 	return nil
 }
 
-func (r *MediaRepository) GetEmptyEXIF(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
+func (r *MediaRepository) ListEmptyEXIF(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
 	medias := make([]*Media, 0)
 	result := r.db.
 		WithContext(ctx).
@@ -242,3 +273,58 @@ func (r *MediaRepository) GetEmptyEXIF(ctx context.Context, pagination *reposito
 
 	return m, nil
 }
+
+func (r *MediaRepository) ListEmptyThumbnail(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
+	medias := make([]*Media, 0)
+	result := r.db.
+		WithContext(ctx).
+		Model(&Media{}).
+		Joins("left join media_thumbnails on media.id = media_thumbnails.media_id").
+		Where("media_thumbnails.media_id IS NULL").
+		Offset(pagination.Page * pagination.Size).
+		Limit(pagination.Size).
+		Order("media.created_at DESC").
+		Find(&medias)
+
+	if result.Error != nil {
+		return nil, result.Error
+	}
+
+	m := list.Map(medias, func(s *Media) *repository.Media {
+		return s.ToModel()
+	})
+
+	return m, nil
+}
+
+func (m *MediaRepository) GetThumbnail(ctx context.Context, mediaID uint) (*repository.MediaThumbnail, error) {
+	thumbnail := &MediaThumbnail{}
+	result := m.db.
+		WithContext(ctx).
+		Model(&Media{}).
+		Where("media_id = ?", mediaID).
+		Limit(1).
+		Take(m)
+
+	if result.Error != nil {
+		return nil, result.Error
+	}
+
+	return thumbnail.ToModel(), nil
+}
+
+func (m *MediaRepository) CreateThumbnail(ctx context.Context, mediaID uint, thumbnail *repository.MediaThumbnail) error {
+	media := &MediaThumbnail{
+		MediaID: mediaID,
+		Path:    thumbnail.Path,
+	}
+
+	result := m.db.
+		WithContext(ctx).
+		Create(media)
+	if result.Error != nil {
+		return result.Error
+	}
+
+	return nil
+}
diff --git a/pkg/database/sql/migration.go b/pkg/database/sql/migration.go
index 019eb91..076bf69 100644
--- a/pkg/database/sql/migration.go
+++ b/pkg/database/sql/migration.go
@@ -8,6 +8,7 @@ func Migrate(db *gorm.DB) error {
 		&Settings{},
 		&Media{},
 		&MediaEXIF{},
+		&MediaThumbnail{},
 	} {
 		if err := db.AutoMigrate(m); err != nil {
 			return err