Stu Leak
16fb407a3c
- Implement full web interface with Go html/template server - Add GX component library (buttons, dialogs, tables, forms, etc.) - Create scene/performer/studio/movie detail and listing pages - Add Adult Empire scraper for additional metadata sources - Implement movie support with database schema - Add import and sync services for data management - Include comprehensive API and frontend documentation - Add custom color scheme and responsive layout 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
439 lines
13 KiB
Go
439 lines
13 KiB
Go
package db
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"time"
|
|
|
|
"git.leaktechnologies.dev/stu/Goondex/internal/model"
|
|
)
|
|
|
|
// SceneStore handles CRUD operations for scenes
|
|
type SceneStore struct {
|
|
db *DB
|
|
}
|
|
|
|
// NewSceneStore creates a new scene store
|
|
func NewSceneStore(db *DB) *SceneStore {
|
|
return &SceneStore{db: db}
|
|
}
|
|
|
|
// Create inserts a new scene
|
|
func (s *SceneStore) Create(scene *model.Scene) error {
|
|
now := time.Now()
|
|
scene.CreatedAt = now
|
|
scene.UpdatedAt = now
|
|
|
|
result, err := s.db.conn.Exec(`
|
|
INSERT INTO scenes (title, code, date, studio_id, description, image_path, image_url, director, url, source, source_id, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`, scene.Title, scene.Code, scene.Date, scene.StudioID, scene.Description, scene.ImagePath, scene.ImageURL, scene.Director, scene.URL, scene.Source, scene.SourceID, scene.CreatedAt.Format(time.RFC3339), scene.UpdatedAt.Format(time.RFC3339))
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to create scene: %w", err)
|
|
}
|
|
|
|
id, err := result.LastInsertId()
|
|
if err != nil {
|
|
return fmt.Errorf("failed to get last insert id: %w", err)
|
|
}
|
|
|
|
scene.ID = id
|
|
return nil
|
|
}
|
|
|
|
// GetByID retrieves a scene by ID
|
|
func (s *SceneStore) GetByID(id int64) (*model.Scene, error) {
|
|
scene := &model.Scene{}
|
|
var createdAt, updatedAt string
|
|
|
|
err := s.db.conn.QueryRow(`
|
|
SELECT id, title, COALESCE(code, ''), COALESCE(date, ''), COALESCE(studio_id, 0), COALESCE(description, ''), COALESCE(image_path, ''), COALESCE(image_url, ''), COALESCE(director, ''), COALESCE(url, ''), COALESCE(source, ''), COALESCE(source_id, ''), created_at, updated_at
|
|
FROM scenes WHERE id = ?
|
|
`, id).Scan(&scene.ID, &scene.Title, &scene.Code, &scene.Date, &scene.StudioID, &scene.Description, &scene.ImagePath, &scene.ImageURL, &scene.Director, &scene.URL, &scene.Source, &scene.SourceID, &createdAt, &updatedAt)
|
|
|
|
if err == sql.ErrNoRows {
|
|
return nil, fmt.Errorf("scene not found")
|
|
}
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to get scene: %w", err)
|
|
}
|
|
|
|
scene.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
scene.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
return scene, nil
|
|
}
|
|
|
|
// Search searches for scenes by title or code
|
|
func (s *SceneStore) Search(query string) ([]model.Scene, error) {
|
|
rows, err := s.db.conn.Query(`
|
|
SELECT id, title, COALESCE(code, ''), COALESCE(date, ''), COALESCE(studio_id, 0), COALESCE(description, ''), COALESCE(image_path, ''), COALESCE(image_url, ''), COALESCE(director, ''), COALESCE(url, ''), COALESCE(source, ''), COALESCE(source_id, ''), created_at, updated_at
|
|
FROM scenes
|
|
WHERE title LIKE ? OR COALESCE(code, '') LIKE ?
|
|
ORDER BY date DESC, title
|
|
`, "%"+query+"%", "%"+query+"%")
|
|
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to search scenes: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var scenes []model.Scene
|
|
for rows.Next() {
|
|
var scene model.Scene
|
|
var createdAt, updatedAt string
|
|
|
|
err := rows.Scan(&scene.ID, &scene.Title, &scene.Code, &scene.Date, &scene.StudioID, &scene.Description, &scene.ImagePath, &scene.ImageURL, &scene.Director, &scene.URL, &scene.Source, &scene.SourceID, &createdAt, &updatedAt)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to scan scene: %w", err)
|
|
}
|
|
|
|
scene.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
scene.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
scenes = append(scenes, scene)
|
|
}
|
|
|
|
return scenes, nil
|
|
}
|
|
|
|
// Update updates an existing scene
|
|
func (s *SceneStore) Update(scene *model.Scene) error {
|
|
scene.UpdatedAt = time.Now()
|
|
|
|
result, err := s.db.conn.Exec(`
|
|
UPDATE scenes
|
|
SET title = ?, code = ?, date = ?, studio_id = ?, description = ?, image_path = ?, image_url = ?, director = ?, url = ?, source = ?, source_id = ?, updated_at = ?
|
|
WHERE id = ?
|
|
`, scene.Title, scene.Code, scene.Date, scene.StudioID, scene.Description, scene.ImagePath, scene.ImageURL, scene.Director, scene.URL, scene.Source, scene.SourceID, scene.UpdatedAt.Format(time.RFC3339), scene.ID)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to update scene: %w", err)
|
|
}
|
|
|
|
rows, err := result.RowsAffected()
|
|
if err != nil {
|
|
return fmt.Errorf("failed to get rows affected: %w", err)
|
|
}
|
|
|
|
if rows == 0 {
|
|
return fmt.Errorf("scene not found")
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// Delete deletes a scene by ID
|
|
func (s *SceneStore) Delete(id int64) error {
|
|
result, err := s.db.conn.Exec("DELETE FROM scenes WHERE id = ?", id)
|
|
if err != nil {
|
|
return fmt.Errorf("failed to delete scene: %w", err)
|
|
}
|
|
|
|
rows, err := result.RowsAffected()
|
|
if err != nil {
|
|
return fmt.Errorf("failed to get rows affected: %w", err)
|
|
}
|
|
|
|
if rows == 0 {
|
|
return fmt.Errorf("scene not found")
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// AddPerformer associates a performer with a scene
|
|
func (s *SceneStore) AddPerformer(sceneID, performerID int64) error {
|
|
_, err := s.db.conn.Exec(`
|
|
INSERT OR IGNORE INTO scene_performers (scene_id, performer_id)
|
|
VALUES (?, ?)
|
|
`, sceneID, performerID)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to add performer to scene: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// RemovePerformer removes a performer association from a scene
|
|
func (s *SceneStore) RemovePerformer(sceneID, performerID int64) error {
|
|
_, err := s.db.conn.Exec(`
|
|
DELETE FROM scene_performers
|
|
WHERE scene_id = ? AND performer_id = ?
|
|
`, sceneID, performerID)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to remove performer from scene: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// AddTag associates a tag with a scene
|
|
func (s *SceneStore) AddTag(sceneID, tagID int64) error {
|
|
return s.AddTagWithConfidence(sceneID, tagID, 1.0, "user", false)
|
|
}
|
|
|
|
// AddTagWithConfidence associates a tag with a scene with ML support
|
|
func (s *SceneStore) AddTagWithConfidence(sceneID, tagID int64, confidence float64, source string, verified bool) error {
|
|
verifiedInt := 0
|
|
if verified {
|
|
verifiedInt = 1
|
|
}
|
|
|
|
_, err := s.db.conn.Exec(`
|
|
INSERT OR REPLACE INTO scene_tags (scene_id, tag_id, confidence, source, verified, created_at)
|
|
VALUES (?, ?, ?, ?, ?, datetime('now'))
|
|
`, sceneID, tagID, confidence, source, verifiedInt)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to add tag to scene: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// VerifyTag marks a scene tag as human-verified
|
|
func (s *SceneStore) VerifyTag(sceneID, tagID int64) error {
|
|
_, err := s.db.conn.Exec(`
|
|
UPDATE scene_tags
|
|
SET verified = 1
|
|
WHERE scene_id = ? AND tag_id = ?
|
|
`, sceneID, tagID)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to verify tag: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// RemoveTag removes a tag association from a scene
|
|
func (s *SceneStore) RemoveTag(sceneID, tagID int64) error {
|
|
_, err := s.db.conn.Exec(`
|
|
DELETE FROM scene_tags
|
|
WHERE scene_id = ? AND tag_id = ?
|
|
`, sceneID, tagID)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to remove tag from scene: %w", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// GetPerformers retrieves all performers for a scene
|
|
func (s *SceneStore) GetPerformers(sceneID int64) ([]model.Performer, error) {
|
|
rows, err := s.db.conn.Query(`
|
|
SELECT p.id, p.name, COALESCE(p.aliases, ''), COALESCE(p.gender, ''),
|
|
COALESCE(p.birthday, ''), COALESCE(p.nationality, ''),
|
|
COALESCE(p.source, ''), COALESCE(p.source_id, ''),
|
|
p.created_at, p.updated_at
|
|
FROM performers p
|
|
INNER JOIN scene_performers sp ON p.id = sp.performer_id
|
|
WHERE sp.scene_id = ?
|
|
ORDER BY p.name
|
|
`, sceneID)
|
|
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to get performers: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var performers []model.Performer
|
|
for rows.Next() {
|
|
var p model.Performer
|
|
var createdAt, updatedAt string
|
|
|
|
err := rows.Scan(&p.ID, &p.Name, &p.Aliases, &p.Gender,
|
|
&p.Birthday, &p.Nationality, &p.Source, &p.SourceID,
|
|
&createdAt, &updatedAt)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to scan performer: %w", err)
|
|
}
|
|
|
|
p.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
p.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
performers = append(performers, p)
|
|
}
|
|
|
|
return performers, nil
|
|
}
|
|
|
|
// GetTags retrieves all tags for a scene
|
|
func (s *SceneStore) GetTags(sceneID int64) ([]model.Tag, error) {
|
|
rows, err := s.db.conn.Query(`
|
|
SELECT t.id, t.name, t.category_id, COALESCE(t.description, ''),
|
|
COALESCE(t.source, ''), COALESCE(t.source_id, ''),
|
|
t.created_at, t.updated_at
|
|
FROM tags t
|
|
INNER JOIN scene_tags st ON t.id = st.tag_id
|
|
WHERE st.scene_id = ?
|
|
ORDER BY t.name
|
|
`, sceneID)
|
|
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to get tags: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var tags []model.Tag
|
|
for rows.Next() {
|
|
var t model.Tag
|
|
var createdAt, updatedAt string
|
|
|
|
err := rows.Scan(&t.ID, &t.Name, &t.CategoryID, &t.Description,
|
|
&t.Source, &t.SourceID, &createdAt, &updatedAt)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to scan tag: %w", err)
|
|
}
|
|
|
|
t.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
t.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
tags = append(tags, t)
|
|
}
|
|
|
|
return tags, nil
|
|
}
|
|
|
|
// Upsert inserts or updates a scene based on source_id
|
|
func (s *SceneStore) Upsert(scene *model.Scene) error {
|
|
// Try to find existing scene by source_id
|
|
existing, err := s.GetBySourceID(scene.Source, scene.SourceID)
|
|
if err == nil && existing != nil {
|
|
// Update existing
|
|
scene.ID = existing.ID
|
|
return s.Update(scene)
|
|
}
|
|
// Create new
|
|
return s.Create(scene)
|
|
}
|
|
|
|
// GetBySourceID retrieves a scene by its source and source_id
|
|
func (s *SceneStore) GetBySourceID(source, sourceID string) (*model.Scene, error) {
|
|
var scene model.Scene
|
|
var createdAt, updatedAt string
|
|
|
|
err := s.db.conn.QueryRow(`
|
|
SELECT id, title, COALESCE(code, ''), COALESCE(date, ''), COALESCE(studio_id, 0),
|
|
COALESCE(description, ''), COALESCE(image_path, ''), COALESCE(image_url, ''),
|
|
COALESCE(director, ''), COALESCE(url, ''), COALESCE(source, ''), COALESCE(source_id, ''),
|
|
created_at, updated_at
|
|
FROM scenes
|
|
WHERE source = ? AND source_id = ?
|
|
`, source, sourceID).Scan(
|
|
&scene.ID, &scene.Title, &scene.Code, &scene.Date, &scene.StudioID,
|
|
&scene.Description, &scene.ImagePath, &scene.ImageURL,
|
|
&scene.Director, &scene.URL, &scene.Source, &scene.SourceID,
|
|
&createdAt, &updatedAt,
|
|
)
|
|
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to get scene: %w", err)
|
|
}
|
|
|
|
scene.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
scene.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
return &scene, nil
|
|
}
|
|
|
|
// GetByPerformer retrieves all scenes featuring a specific performer
|
|
func (s *SceneStore) GetByPerformer(performerID int64) ([]model.Scene, error) {
|
|
rows, err := s.db.conn.Query(`
|
|
SELECT DISTINCT s.id, s.title, COALESCE(s.code, ''), COALESCE(s.date, ''), COALESCE(s.studio_id, 0),
|
|
COALESCE(s.description, ''), COALESCE(s.image_path, ''), COALESCE(s.image_url, ''),
|
|
COALESCE(s.director, ''), COALESCE(s.url, ''), COALESCE(s.source, ''), COALESCE(s.source_id, ''),
|
|
s.created_at, s.updated_at
|
|
FROM scenes s
|
|
INNER JOIN scene_performers sp ON s.id = sp.scene_id
|
|
WHERE sp.performer_id = ?
|
|
ORDER BY s.date DESC, s.title ASC
|
|
`, performerID)
|
|
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to get scenes for performer: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var scenes []model.Scene
|
|
for rows.Next() {
|
|
var scene model.Scene
|
|
var createdAt, updatedAt string
|
|
|
|
err := rows.Scan(
|
|
&scene.ID, &scene.Title, &scene.Code, &scene.Date, &scene.StudioID,
|
|
&scene.Description, &scene.ImagePath, &scene.ImageURL,
|
|
&scene.Director, &scene.URL, &scene.Source, &scene.SourceID,
|
|
&createdAt, &updatedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to scan scene: %w", err)
|
|
}
|
|
|
|
scene.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
scene.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
scenes = append(scenes, scene)
|
|
}
|
|
|
|
return scenes, nil
|
|
}
|
|
|
|
// GetMovies retrieves all movies that contain this scene
|
|
func (s *SceneStore) GetMovies(sceneID int64) ([]model.Movie, error) {
|
|
rows, err := s.db.conn.Query(`
|
|
SELECT m.id, m.title, COALESCE(m.date, ''), COALESCE(m.studio_id, 0),
|
|
COALESCE(m.description, ''), COALESCE(m.director, ''), COALESCE(m.duration, 0),
|
|
COALESCE(m.image_path, ''), COALESCE(m.image_url, ''), COALESCE(m.back_image_url, ''),
|
|
COALESCE(m.url, ''), COALESCE(m.source, ''), COALESCE(m.source_id, ''),
|
|
m.created_at, m.updated_at, COALESCE(ms.scene_number, 0)
|
|
FROM movies m
|
|
INNER JOIN movie_scenes ms ON m.id = ms.movie_id
|
|
WHERE ms.scene_id = ?
|
|
ORDER BY m.date DESC, m.title ASC
|
|
`, sceneID)
|
|
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to get movies for scene: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var movies []model.Movie
|
|
for rows.Next() {
|
|
var m model.Movie
|
|
var studioID sql.NullInt64
|
|
var createdAt, updatedAt string
|
|
var sceneNumber int
|
|
|
|
err := rows.Scan(
|
|
&m.ID, &m.Title, &m.Date, &studioID,
|
|
&m.Description, &m.Director, &m.Duration,
|
|
&m.ImagePath, &m.ImageURL, &m.BackImageURL,
|
|
&m.URL, &m.Source, &m.SourceID,
|
|
&createdAt, &updatedAt, &sceneNumber,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to scan movie: %w", err)
|
|
}
|
|
|
|
if studioID.Valid && studioID.Int64 > 0 {
|
|
m.StudioID = &studioID.Int64
|
|
}
|
|
|
|
m.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
|
|
m.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)
|
|
|
|
movies = append(movies, m)
|
|
}
|
|
|
|
return movies, nil
|
|
}
|