person-home/internal/db/projects.go

package db

import (
	"database/sql"
	"encoding/json"
	"time"

	"person-home/internal/models"
)

func ListProjects(db *sql.DB, tag string, featuredOnly bool, limit int) ([]models.Project, error) {
	query := "SELECT id, title, description, url, image, tags, sort_order, featured, created_at, updated_at FROM projects WHERE 1=1"
	var args []any

	if featuredOnly {
		query += " AND featured = 1"
	}

	query += " ORDER BY sort_order ASC, created_at DESC"

	if limit > 0 {
		query += " LIMIT ?"
		args = append(args, limit)
	}

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var all []models.Project
	for rows.Next() {
		var p models.Project
		var tagsJSON string
		if err := rows.Scan(&p.ID, &p.Title, &p.Description, &p.URL, &p.Image, &tagsJSON, &p.SortOrder, &p.Featured, &p.CreatedAt, &p.UpdatedAt); err != nil {
			return nil, err
		}
		json.Unmarshal([]byte(tagsJSON), &p.Tags)
		if tag != "" {
			found := false
			for _, t := range p.Tags {
				if t == tag {
					found = true
					break
				}
			}
			if !found {
				continue
			}
		}
		all = append(all, p)
	}
	return all, rows.Err()
}

func GetProject(db *sql.DB, id int64) (*models.Project, error) {
	var p models.Project
	var tagsJSON string
	err := db.QueryRow(
		"SELECT id, title, description, url, image, tags, sort_order, featured, created_at, updated_at FROM projects WHERE id = ?", id,
	).Scan(&p.ID, &p.Title, &p.Description, &p.URL, &p.Image, &tagsJSON, &p.SortOrder, &p.Featured, &p.CreatedAt, &p.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	json.Unmarshal([]byte(tagsJSON), &p.Tags)
	return &p, nil
}

func CreateProject(db *sql.DB, req *models.CreateProjectRequest) (*models.Project, error) {
	tagsJSON, _ := json.Marshal(req.Tags)
	now := time.Now().UTC().Format(time.RFC3339)

	p := &models.Project{
		Title:       req.Title,
		Description: req.Description,
		URL:         req.URL,
		Image:       req.Image,
		Tags:        req.Tags,
		SortOrder:   req.SortOrder,
		Featured:    req.Featured,
		CreatedAt:   now,
		UpdatedAt:   now,
	}

	result, err := db.Exec(
		"INSERT INTO projects (title, description, url, image, tags, sort_order, featured, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
		p.Title, p.Description, p.URL, p.Image, string(tagsJSON), p.SortOrder, boolToInt(p.Featured), p.CreatedAt, p.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	id, _ := result.LastInsertId()
	p.ID = id
	return p, nil
}

func UpdateProject(db *sql.DB, id int64, req *models.UpdateProjectRequest) (*models.Project, error) {
	existing, err := GetProject(db, id)
	if err != nil {
		return nil, err
	}
	if existing == nil {
		return nil, nil
	}

	if req.Title != nil {
		existing.Title = *req.Title
	}
	if req.Description != nil {
		existing.Description = *req.Description
	}
	if req.URL != nil {
		existing.URL = *req.URL
	}
	if req.Image != nil {
		existing.Image = *req.Image
	}
	if req.Tags != nil {
		existing.Tags = req.Tags
	}
	if req.SortOrder != nil {
		existing.SortOrder = *req.SortOrder
	}
	if req.Featured != nil {
		existing.Featured = *req.Featured
	}

	existing.UpdatedAt = time.Now().UTC().Format(time.RFC3339)
	tagsJSON, _ := json.Marshal(existing.Tags)

	_, err = db.Exec(
		"UPDATE projects SET title=?, description=?, url=?, image=?, tags=?, sort_order=?, featured=?, updated_at=? WHERE id=?",
		existing.Title, existing.Description, existing.URL, existing.Image, string(tagsJSON), existing.SortOrder, boolToInt(existing.Featured), existing.UpdatedAt, id,
	)
	if err != nil {
		return nil, err
	}
	return existing, nil
}

func DeleteProject(db *sql.DB, id int64) error {
	result, err := db.Exec("DELETE FROM projects WHERE id = ?", id)
	if err != nil {
		return err
	}
	n, _ := result.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

func AllTags(db *sql.DB) ([]string, error) {
	rows, err := db.Query("SELECT DISTINCT tags FROM projects")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	tagSet := make(map[string]struct{})
	for rows.Next() {
		var tagsJSON string
		if err := rows.Scan(&tagsJSON); err != nil {
			return nil, err
		}
		var tags []string
		json.Unmarshal([]byte(tagsJSON), &tags)
		for _, t := range tags {
			tagSet[t] = struct{}{}
		}
	}
	var result []string
	for t := range tagSet {
		result = append(result, t)
	}
	return result, rows.Err()
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}