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, budget_tracked 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, &p.BudgetTracked); 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, budget_tracked FROM projects WHERE id = ?
	`, id).Scan(&p.ID, &p.Name, &p.Color, &p.CreatedAt, &p.Archived, &p.BudgetTracked)
	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)
}

// SetProjectBudgetTracked marks a project as opted in (or out) of budget
// tracking. Returns ErrNativeTaskNotFound if id doesn't match any row.
func (s *Store) SetProjectBudgetTracked(id string, tracked bool) error {
	result, err := s.db.Exec(`UPDATE projects SET budget_tracked = ? WHERE id = ?`, tracked, id)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// GetBudgetTrackedProjectIDs returns the set of project IDs opted into budget tracking.
func (s *Store) GetBudgetTrackedProjectIDs() (map[string]bool, error) {
	rows, err := s.db.Query(`SELECT id FROM projects WHERE budget_tracked = 1`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	ids := make(map[string]bool)
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids[id] = true
	}
	return ids, rows.Err()
}