note-manager/repository/note_repository.go

package repository

import (
	"fmt"
	"os"
	"path/filepath"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"note-manager/model"
)

// NoteRepository 笔记数据访问层
type NoteRepository struct {
	db *gorm.DB
}

// NewNoteRepository 创建数据仓库实例，初始化数据库
func NewNoteRepository(dbPath string) (*NoteRepository, error) {
	// 确保数据库目录存在
	dir := filepath.Dir(dbPath)
	if err := os.MkdirAll(dir, 0755); err != nil {
		return nil, fmt.Errorf("创建数据库目录失败: %w", err)
	}

	db, err := gorm.Open(sqlite.Open(dbPath), &gorm.Config{})
	if err != nil {
		return nil, fmt.Errorf("连接数据库失败: %w", err)
	}

	// 自动迁移表结构
	if err := db.AutoMigrate(&model.Note{}); err != nil {
		return nil, fmt.Errorf("数据库迁移失败: %w", err)
	}

	return &NoteRepository{db: db}, nil
}

// Create 创建笔记
func (r *NoteRepository) Create(note *model.Note) error {
	return r.db.Select("Title", "Content", "Category", "Tags", "Password", "IsPinned", "IsFavorite", "IsPublic", "ParentID", "IsFolder", "SortOrder").Create(note).Error
}

// GetByID 根据 ID 获取笔记
func (r *NoteRepository) GetByID(id uint) (*model.Note, error) {
	var note model.Note
	err := r.db.First(&note, id).Error
	if err != nil {
		return nil, err
	}
	return &note, nil
}

// Update 更新笔记
func (r *NoteRepository) Update(note *model.Note) error {
	return r.db.Save(note).Error
}

// Delete 删除笔记
func (r *NoteRepository) Delete(id uint) error {
	return r.db.Delete(&model.Note{}, id).Error
}

// ListQuery 列表查询参数
type ListQuery struct {
	Page     int
	PageSize int
	Category string
	Tag      string
	Pinned   *bool
	Favorite *bool
	ParentID *uint // 父目录 ID，nil 表示所有
}

// List 获取笔记列表（分页）
func (r *NoteRepository) List(q ListQuery) ([]model.NoteListItem, int64, error) {
	var items []model.NoteListItem
	var total int64

	query := r.db.Model(&model.Note{})

	if q.Category != "" {
		query = query.Where("category = ?", q.Category)
	}
	if q.Tag != "" {
		query = query.Where("tags LIKE ?", fmt.Sprintf("%%\"%s\"%%", q.Tag))
	}
	if q.Pinned != nil {
		query = query.Where("is_pinned = ?", *q.Pinned)
	}
	if q.Favorite != nil {
		query = query.Where("is_favorite = ?", *q.Favorite)
	}
	if q.ParentID != nil {
		query = query.Where("parent_id = ?", *q.ParentID)
	}

	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	offset := (q.Page - 1) * q.PageSize
	err := query.Select("id, title, category, tags, is_pinned, is_favorite, parent_id, is_folder, sort_order, created_at, updated_at").
		Order("is_folder DESC, sort_order ASC, updated_at DESC").
		Offset(offset).
		Limit(q.PageSize).
		Find(&items).Error

	return items, total, err
}

// GetAllTree 获取所有笔记的树形结构
func (r *NoteRepository) GetAllTree() ([]model.NoteListItem, error) {
	var items []model.NoteListItem
	err := r.db.Model(&model.Note{}).
		Select("id, title, category, tags, CASE WHEN password != '' THEN 1 ELSE 0 END as has_password, is_pinned, is_favorite, is_public, parent_id, is_folder, sort_order, created_at, updated_at").
		Order("is_folder DESC, sort_order ASC, title ASC").
		Find(&items).Error
	return items, err
}

// GetPublicTree 获取公开可见的树形结构（显示所有目录和笔记，访问时再验证密码）
func (r *NoteRepository) GetPublicTree() ([]model.NoteListItem, error) {
	var items []model.NoteListItem
	err := r.db.Model(&model.Note{}).
		Select("id, title, category, tags, CASE WHEN password != '' THEN 1 ELSE 0 END as has_password, is_pinned, is_favorite, is_public, parent_id, is_folder, sort_order, created_at, updated_at").
		Order("is_folder DESC, sort_order ASC, title ASC").
		Find(&items).Error
	return items, err
}

// GetByParentID 获取指定父目录下的所有项目
func (r *NoteRepository) GetByParentID(parentID uint) ([]model.NoteListItem, error) {
	var items []model.NoteListItem
	err := r.db.Model(&model.Note{}).
		Where("parent_id = ?", parentID).
		Select("id, title, category, tags, is_pinned, is_favorite, is_public, parent_id, is_folder, sort_order, created_at, updated_at").
		Order("is_folder DESC, sort_order ASC, title ASC").
		Find(&items).Error
	return items, err
}

// GetChildrenCount 获取子项数量
func (r *NoteRepository) GetChildrenCount(parentID uint) (int64, error) {
	var count int64
	err := r.db.Model(&model.Note{}).Where("parent_id = ?", parentID).Count(&count).Error
	return count, err
}

// DeleteWithChildren 删除目录及其下所有内容
func (r *NoteRepository) DeleteWithChildren(id uint) error {
	// 先删除所有子项
	if err := r.db.Where("parent_id = ?", id).Delete(&model.Note{}).Error; err != nil {
		return err
	}
	// 再删除自己
	return r.db.Delete(&model.Note{}, id).Error
}

// Search 搜索笔记（按标题和内容）
func (r *NoteRepository) Search(keyword string, page, pageSize int) ([]model.NoteListItem, int64, error) {
	var items []model.NoteListItem
	var total int64

	like := "%" + keyword + "%"
	query := r.db.Model(&model.Note{}).Where("(title LIKE ? OR content LIKE ?) AND is_folder = ?", like, like, false)

	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	offset := (page - 1) * pageSize
	err := query.Select("id, title, category, tags, is_pinned, is_favorite, parent_id, is_folder, sort_order, created_at, updated_at").
		Order("is_pinned DESC, updated_at DESC").
		Offset(offset).
		Limit(pageSize).
		Find(&items).Error

	return items, total, err
}

// GetCategories 获取所有分类
func (r *NoteRepository) GetCategories() ([]string, error) {
	var categories []string
	err := r.db.Model(&model.Note{}).
		Distinct("category").
		Where("category != '' AND is_folder = ?", false).
		Pluck("category", &categories).Error
	return categories, err
}

// GetTags 获取所有标签
func (r *NoteRepository) GetTags() ([]string, error) {
	var tagsJSON []string
	err := r.db.Model(&model.Note{}).
		Where("tags != '' AND tags IS NOT NULL AND is_folder = ?", false).
		Pluck("tags", &tagsJSON).Error
	if err != nil {
		return nil, err
	}

	// 去重
	seen := make(map[string]bool)
	var result []string
	for _, t := range tagsJSON {
		if !seen[t] {
			seen[t] = true
			result = append(result, t)
		}
	}
	return result, nil
}