package store

import (
	"database/sql"

	"task-dashboard/internal/models"
)

// CreateProject inserts a new project and returns it.
func (s *Store) CreateProject(name, color string) (*models.Project, error) {
	id := newTaskID()
	if _, err := s.db.Exec(`INSERT INTO projects (id, name, color) VALUES (?, ?, ?)`, id, name, color); err != nil {
		return nil, err
	}
	return s.GetProjectByID(id)
}

// GetProjects returns all non-archived projects, alphabetically by name.
func (s *Store) GetProjects() ([]models.Project, error) {
	rows, err := s.db.Query(`
		SELECT id, name, color, created_at, archived FROM projects WHERE archived = 0 ORDER BY name ASC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var projects []models.Project
	for rows.Next() {
		var p models.Project
		if err := rows.Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived); err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}

// GetProjectByID returns a single project by id, or ErrNativeTaskNotFound.
func (s *Store) GetProjectByID(id string) (*models.Project, error) {
	var p models.Project
	err := s.db.QueryRow(`
		SELECT id, name, color, created_at, archived FROM projects WHERE id = ?
	`, id).Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived)
	if err == sql.ErrNoRows {
		return nil, ErrNativeTaskNotFound
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

// SetTaskProject sets or clears a task's project (projectID = "" clears
// it). Returns ErrNativeTaskNotFound if id doesn't match any task row --
// deliberately does NOT validate that projectID itself exists, mirroring
// this codebase's existing tolerance for a stale/empty foreign reference
// (e.g. RecurrenceSeriesID is never validated against another table either).
func (s *Store) SetTaskProject(id, projectID string) error {
	result, err := s.db.Exec(`
		UPDATE native_tasks SET project_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
	`, projectID, id)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}