3 files changed, 139 insertions, 6 deletions

diff --git a/pkg/database/repository/media.go b/pkg/database/repository/media.go
index 6f5b39b..d6addbf 100644
--- a/pkg/database/repository/media.go
+++ b/pkg/database/repository/media.go
@@ -34,6 +34,10 @@ type (
 		GPSLongitude    *float64
 	}
 
+	Album struct {
+		ID uint
+	}
+
 	MediaThumbnail struct {
 		Path string
 	}
@@ -51,6 +55,17 @@ type (
 		MIMEType string
 	}
 
+	CreateAlbum struct {
+		ParentID *uint
+		Name     string
+		Path     string
+	}
+
+	CreateAlbumFile struct {
+		MediaID uint
+		AlbumID uint
+	}
+
 	MediaRepository interface {
 		Create(context.Context, *CreateMedia) error
 		Exists(context.Context, string) (bool, error)
@@ -66,6 +81,12 @@ type (
 		ListEmptyThumbnail(context.Context, *Pagination) ([]*Media, error)
 		GetThumbnail(context.Context, uint) (*MediaThumbnail, error)
 		CreateThumbnail(context.Context, uint, *MediaThumbnail) error
+
+		ListEmptyAlbums(context.Context, *Pagination) ([]*Media, error)
+		ExistsAlbumByAbsolutePath(context.Context, string) (bool, error)
+		GetAlbumByAbsolutePath(context.Context, string) (*Album, error)
+		CreateAlbum(context.Context, *CreateAlbum) (*Album, error)
+		CreateAlbumFile(context.Context, *CreateAlbumFile) error
 	}
 )
 
diff --git a/pkg/database/sql/media.go b/pkg/database/sql/media.go
index e5ba517..59e39ee 100644
--- a/pkg/database/sql/media.go
+++ b/pkg/database/sql/media.go
@@ -48,6 +48,22 @@ type (
 		Media   Media
 	}
 
+	MediaAlbum struct {
+		gorm.Model
+		ParentID *uint
+		Parent   *MediaAlbum
+		Name     string
+		Path     string
+	}
+
+	MediaAlbumFile struct {
+		gorm.Model
+		MediaID uint
+		Media   Media
+		AlbumID uint
+		Album   MediaAlbum
+	}
+
 	MediaRepository struct {
 		db *gorm.DB
 	}
@@ -55,13 +71,13 @@ type (
 
 var _ repository.MediaRepository = &MediaRepository{}
 
-func (self *Media) ToModel() *repository.Media {
+func (m *Media) ToModel() *repository.Media {
 	return &repository.Media{
-		ID:       self.ID,
-		Path:     self.Path,
-		PathHash: self.PathHash,
-		Name:     self.Name,
-		MIMEType: self.MIMEType,
+		ID:       m.ID,
+		Path:     m.Path,
+		PathHash: m.PathHash,
+		Name:     m.Name,
+		MIMEType: m.MIMEType,
 	}
 }
 
@@ -86,6 +102,12 @@ func (m *MediaEXIF) ToModel() *repository.MediaEXIF {
 	}
 }
 
+func (a *MediaAlbum) ToModel() *repository.Album {
+	return &repository.Album{
+		ID: a.ID,
+	}
+}
+
 func (m *MediaThumbnail) ToModel() *repository.MediaThumbnail {
 	return &repository.MediaThumbnail{
 		Path: m.Path,
@@ -329,3 +351,91 @@ func (m *MediaRepository) CreateThumbnail(ctx context.Context, mediaID uint, thu
 
 	return nil
 }
+
+func (r *MediaRepository) ListEmptyAlbums(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
+	medias := make([]*Media, 0)
+	result := r.db.
+		WithContext(ctx).
+		Model(&Media{}).
+		Joins("left join media_album_files on media.id = media_album_files.media_id").
+		Where("media_album_files.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) ExistsAlbumByAbsolutePath(ctx context.Context, path string) (bool, error) {
+	var exists bool
+	result := m.db.
+		WithContext(ctx).
+		Model(&MediaAlbum{}).
+		Select("count(id) > 0").
+		Where("path = ?", path).
+		Find(&exists)
+
+	if result.Error != nil {
+		return false, result.Error
+	}
+
+	return exists, nil
+}
+
+func (r *MediaRepository) GetAlbumByAbsolutePath(ctx context.Context, path string) (*repository.Album, error) {
+	m := &MediaAlbum{}
+	result := r.db.
+		WithContext(ctx).
+		Model(&MediaAlbum{}).
+		Where("path = ?", path).
+		Limit(1).
+		Take(m)
+
+	if result.Error != nil {
+		return nil, result.Error
+	}
+
+	return m.ToModel(), nil
+}
+
+func (m *MediaRepository) CreateAlbum(ctx context.Context, createAlbum *repository.CreateAlbum) (*repository.Album, error) {
+	album := &MediaAlbum{
+		ParentID: createAlbum.ParentID,
+		Name:     createAlbum.Name,
+		Path:     createAlbum.Path,
+	}
+
+	result := m.db.
+		WithContext(ctx).
+		Create(album)
+	if result.Error != nil {
+		return nil, result.Error
+	}
+
+	return album.ToModel(), nil
+}
+
+func (m *MediaRepository) CreateAlbumFile(ctx context.Context, createAlbumFile *repository.CreateAlbumFile) error {
+	albumFile := &MediaAlbumFile{
+		MediaID: createAlbumFile.MediaID,
+		AlbumID: createAlbumFile.AlbumID,
+	}
+
+	result := m.db.
+		WithContext(ctx).
+		Create(albumFile)
+	if result.Error != nil {
+		return result.Error
+	}
+
+	return nil
+}
diff --git a/pkg/database/sql/migration.go b/pkg/database/sql/migration.go
index 076bf69..73e4297 100644
--- a/pkg/database/sql/migration.go
+++ b/pkg/database/sql/migration.go
@@ -9,6 +9,8 @@ func Migrate(db *gorm.DB) error {
 		&Media{},
 		&MediaEXIF{},
 		&MediaThumbnail{},
+		&MediaAlbum{},
+		&MediaAlbumFile{},
 	} {
 		if err := db.AutoMigrate(m); err != nil {
 			return err