cmsp/internal/store/video.go

package store

import (
	"fmt"
	"os"
	"strings"
)

const (
	VideoStatusPending    = "pending"
	VideoStatusDownloaded = "downloaded"
	VideoStatusFailed     = "failed"
	VideoStatusUploaded   = "uploaded"
)

// Video 对应 videos 表的一行。
type Video struct {
	ID         int64  `json:"id"`
	ProductID  string `json:"productId"`
	SourceItem string `json:"sourceItem"`
	SourceURL  string `json:"sourceUrl"`
	LocalPath  string `json:"localPath"`
	FileSize   int64  `json:"fileSize"`
	RemoteURL  string `json:"remoteUrl"`
	Status     string `json:"status"`
	LastError  string `json:"lastError"`
	CreatedAt  string `json:"createdAt"`
}

// ReplaceVideos 在一个事务内按商品全量替换视频记录。
func (s *Store) ReplaceVideos(productID string, items []Video, now string) error {
	tx, err := s.db.Begin()
	if err != nil {
		return fmt.Errorf("开启视频替换事务失败：%w", err)
	}
	defer tx.Rollback()
	if _, err := tx.Exec(`DELETE FROM videos WHERE product_id = ?`, productID); err != nil {
		return fmt.Errorf("清理商品旧视频失败：%w", err)
	}
	stmt, err := tx.Prepare(`INSERT INTO videos (
		product_id, source_item, source_url, local_path, file_size,
		remote_url, status, last_error, created_at
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return fmt.Errorf("准备写入视频语句失败：%w", err)
	}
	defer stmt.Close()
	for _, item := range items {
		createdAt := item.CreatedAt
		if createdAt == "" {
			createdAt = now
		}
		if _, err := stmt.Exec(productID, item.SourceItem, item.SourceURL,
			item.LocalPath, item.FileSize, item.RemoteURL, item.Status,
			item.LastError, createdAt); err != nil {
			return fmt.Errorf("写入商品视频失败：%w", err)
		}
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("提交视频替换事务失败：%w", err)
	}
	return nil
}

func (s *Store) ListVideos(productID string) ([]Video, error) {
	rows, err := s.db.Query(`SELECT id, product_id, source_item, source_url,
		local_path, file_size, remote_url, status, last_error, created_at
		FROM videos WHERE product_id = ? ORDER BY id`, productID)
	if err != nil {
		return nil, fmt.Errorf("查询商品视频失败：%w", err)
	}
	defer rows.Close()
	items := make([]Video, 0)
	for rows.Next() {
		var item Video
		if err := rows.Scan(&item.ID, &item.ProductID, &item.SourceItem,
			&item.SourceURL, &item.LocalPath, &item.FileSize, &item.RemoteURL,
			&item.Status, &item.LastError, &item.CreatedAt); err != nil {
			return nil, fmt.Errorf("读取商品视频行失败：%w", err)
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("遍历商品视频失败：%w", err)
	}
	return items, nil
}

// FirstUploadableVideo 返回第一个已下载且本地文件仍存在的记录。
func (s *Store) FirstUploadableVideo(productID string) (Video, bool, error) {
	items, err := s.ListVideos(productID)
	if err != nil {
		return Video{}, false, err
	}
	for _, item := range items {
		if item.Status != VideoStatusDownloaded || strings.TrimSpace(item.LocalPath) == "" {
			continue
		}
		info, err := os.Stat(item.LocalPath)
		if err != nil || info.IsDir() {
			continue
		}
		return item, true, nil
	}
	return Video{}, false, nil
}

// MarkVideoUploaded 写回成功上传后的素材地址和状态。
func (s *Store) MarkVideoUploaded(videoID int64, remoteURL string) error {
	if _, err := s.db.Exec(`UPDATE videos SET remote_url = ?, status = ?, last_error = '' WHERE id = ?`, remoteURL, VideoStatusUploaded, videoID); err != nil {
		return fmt.Errorf("更新视频上传状态失败：%w", err)
	}
	return nil
}

// UpsertUploadedVideo 按商品和本地路径补写手工放入目录的视频上传结果。
// 它绝不能使用 ReplaceVideos：同一商品可能还有其它视频来源记录。
func (s *Store) UpsertUploadedVideo(productID, localPath string, fileSize int64, remoteURL, now string) error {
	result, err := s.db.Exec(`UPDATE videos SET file_size = ?, remote_url = ?, status = ?, last_error = ''
		WHERE product_id = ? AND local_path = ?`, fileSize, remoteURL, VideoStatusUploaded, productID, localPath)
	if err != nil {
		return fmt.Errorf("更新已上传视频记录失败：%w", err)
	}
	changed, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("读取已上传视频更新数量失败：%w", err)
	}
	if changed > 0 {
		return nil
	}
	if _, err := s.db.Exec(`INSERT INTO videos (
		product_id, source_item, source_url, local_path, file_size, remote_url, status, last_error, created_at
	) VALUES (?, '', '', ?, ?, ?, ?, '', ?)`, productID, localPath, fileSize, remoteURL, VideoStatusUploaded, now); err != nil {
		return fmt.Errorf("补写已上传视频记录失败：%w", err)
	}
	return nil
}