package store

import (
	"crypto/rand"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	"task-dashboard/internal/models"
)

// ErrNativeTaskNotFound is returned by CompleteNativeTask, UncompleteNativeTask,
// and RescheduleNativeTask when no row matches the given id -- previously these
// three silently reported success on a 0-row UPDATE (Exec's err is nil even when
// no rows match), so a stale or wrong id from a caller looked identical to a real
// completion: the HTTP response was 200, but nothing in the database changed.
var ErrNativeTaskNotFound = errors.New("native task not found")

// GetNativeTasks returns all non-completed native tasks.
func (s *Store) GetNativeTasks() ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at,
		       recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override
		FROM native_tasks
		WHERE completed = 0
		ORDER BY CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC, priority DESC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanNativeTasks(rows)
}

// GetNativeTasksByDateRange returns non-completed native tasks due within the given range.
// Overdue tasks (due before start) are deliberately excluded here -- BuildTimeline fetches
// those separately via GetOverdueNativeTasks so callers that only want "in range" can use this
// without double-counting against that separate fetch.
func (s *Store) GetNativeTasksByDateRange(start, end time.Time) ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at,
		       recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override
		FROM native_tasks
		WHERE completed = 0 AND due_date IS NOT NULL AND due_date >= ? AND due_date < ?
		ORDER BY due_date ASC, priority DESC
	`, start, end)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanNativeTasks(rows)
}

// GetOverdueNativeTasks returns non-completed native tasks whose due date is
// before the given time. BuildTimeline calls this alongside
// GetNativeTasksByDateRange, whose lower bound excludes anything due before
// the requested range's start -- without this, a task overdue from a
// previous day never gets fetched at all, so it never reaches
// ComputeDaySection to be marked IsOverdue.
func (s *Store) GetOverdueNativeTasks(before time.Time) ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at,
		       recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override
		FROM native_tasks
		WHERE completed = 0 AND due_date IS NOT NULL AND due_date < ?
		ORDER BY due_date ASC, priority DESC
	`, before)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanNativeTasks(rows)
}

// GetUndatedNativeTasks returns non-completed native tasks with no due date.
func (s *Store) GetUndatedNativeTasks() ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at,
		       recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override
		FROM native_tasks
		WHERE completed = 0 AND due_date IS NULL
		ORDER BY priority DESC, created_at ASC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanNativeTasks(rows)
}

// GetNativeTaskByID returns a single native task by id, or ErrNativeTaskNotFound.
func (s *Store) GetNativeTaskByID(id string) (*models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_name, due_date, priority, completed, labels, created_at,
		       recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override
		FROM native_tasks
		WHERE id = ?
	`, id)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	tasks, err := scanNativeTasks(rows)
	if err != nil {
		return nil, err
	}
	if len(tasks) == 0 {
		return nil, ErrNativeTaskNotFound
	}
	return &tasks[0], nil
}

// CreateNativeTask inserts a new native task.
func (s *Store) CreateNativeTask(task models.Task) error {
	labelsJSON, _ := json.Marshal(task.Labels)
	_, err := s.db.Exec(`
		INSERT INTO native_tasks (id, content, description, project_name, due_date, priority, labels, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
	`, task.ID, task.Content, task.Description, task.ProjectName, task.DueDate, task.Priority, string(labelsJSON))
	return err
}

// UpdateNativeTask updates a native task's content and description.
func (s *Store) UpdateNativeTask(id, content, description string) error {
	_, err := s.db.Exec(`
		UPDATE native_tasks SET content = ?, description = ?, updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`, content, description, id)
	return err
}

// UpdateNativeTaskDescription updates only a native task's description, leaving content untouched.
func (s *Store) UpdateNativeTaskDescription(id, description string) error {
	_, err := s.db.Exec(`
		UPDATE native_tasks SET description = ?, updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`, description, id)
	return err
}

// CompleteNativeTask marks a task as completed. If it's the latest
// occurrence of a recurring series (no newer row exists yet), it also
// creates the next iteration. Returns ErrNativeTaskNotFound if id doesn't
// match any row.
func (s *Store) CompleteNativeTask(id string) error {
	task, err := s.GetNativeTaskByID(id)
	if err != nil {
		return err
	}

	result, err := s.db.Exec(`
		UPDATE native_tasks SET completed = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?
	`, id)
	if err != nil {
		return err
	}
	if err := checkRowsAffected(result); err != nil {
		return err
	}

	if task.RecurrenceSeriesID == "" {
		return nil
	}
	isLatest, err := s.isLatestInSeries(*task)
	if err != nil {
		return err
	}
	if !isLatest {
		return nil
	}
	return s.CreateNextIteration(*task)
}

// isLatestInSeries reports whether task is the row with the latest
// due_date in its recurrence series (i.e., no newer iteration has been
// created yet). Ties on due_date are broken by created_at: the
// more-recently-created row wins, so CreateNextIteration's freshly-inserted
// row always displaces the row it was generated from, never the reverse.
func (s *Store) isLatestInSeries(task models.Task) (bool, error) {
	var exists bool
	err := s.db.QueryRow(`
		SELECT EXISTS (
			SELECT 1 FROM native_tasks
			WHERE recurrence_series_id = ?
			  AND (due_date > ? OR (due_date = ? AND created_at > ?))
		)
	`, task.RecurrenceSeriesID, task.DueDate, task.DueDate, task.CreatedAt).Scan(&exists)
	if err != nil {
		return false, err
	}
	return !exists, nil
}

// RescheduleNativeTask sets a new due date on a task. Returns
// ErrNativeTaskNotFound if id doesn't match any row.
func (s *Store) RescheduleNativeTask(id string, dueDate time.Time) error {
	result, err := s.db.Exec(`
		UPDATE native_tasks SET due_date = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
	`, dueDate, id)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// UncompleteNativeTask marks a task as not completed. Returns
// ErrNativeTaskNotFound if id doesn't match any row.
func (s *Store) UncompleteNativeTask(id string) error {
	result, err := s.db.Exec(`
		UPDATE native_tasks SET completed = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?
	`, id)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// CreateNextIteration copies old's content, description, project_name,
// priority, labels, and recurrence fields onto a brand-new row (new id,
// same recurrence_series_id, completed=false, next_occurrence_override=""),
// with due_date set to old.NextOccurrenceOverride if present, else
// ComputeNextOccurrence(old.DueDate, ...). old itself is left untouched.
func (s *Store) CreateNextIteration(old models.Task) error {
	var nextDue *time.Time
	switch {
	case old.NextOccurrenceOverride != nil:
		nextDue = old.NextOccurrenceOverride
	case old.DueDate != nil:
		computed := models.ComputeNextOccurrence(*old.DueDate, old.RecurrenceFreq, old.RecurrenceInterval, old.RecurrenceWeekdays)
		nextDue = &computed
	}

	labelsJSON, _ := json.Marshal(old.Labels)
	_, err := s.db.Exec(`
		INSERT INTO native_tasks (
			id, content, description, project_name, due_date, priority, labels,
			recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override,
			created_at, updated_at
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
	`, newTaskID(), old.Content, old.Description, old.ProjectName, nextDue, old.Priority, string(labelsJSON),
		old.RecurrenceFreq, old.RecurrenceInterval, formatWeekdays(old.RecurrenceWeekdays), old.RecurrenceSeriesID)
	return err
}

// newTaskID generates a random hex id for a new native_tasks row -- the
// same format as handlers.newID(), duplicated here since store must not
// import handlers.
func newTaskID() string {
	b := make([]byte, 12)
	_, _ = rand.Read(b)
	return fmt.Sprintf("%x", b)
}

// SetTaskRecurrence sets or clears a task's recurrence pattern. freq == ""
// clears the pattern (recurrence_series_id is left untouched so history
// stays linkable -- a cleared task just stops generating new iterations).
// Setting a freq for the first time (existing recurrence_series_id is
// empty) generates a new series id. Returns ErrNativeTaskNotFound if id
// doesn't match any row.
func (s *Store) SetTaskRecurrence(id, freq string, interval int, weekdays []int) error {
	task, err := s.GetNativeTaskByID(id)
	if err != nil {
		return err
	}

	seriesID := task.RecurrenceSeriesID
	if freq != "" && seriesID == "" {
		seriesID = newTaskID()
	}

	result, err := s.db.Exec(`
		UPDATE native_tasks
		SET recurrence_freq = ?, recurrence_interval = ?, recurrence_weekdays = ?, recurrence_series_id = ?, updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`, freq, interval, formatWeekdays(weekdays), seriesID, id)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// SetNextOccurrenceOverride sets a one-shot override for a task's next
// occurrence, consumed (read, but not explicitly cleared -- the override
// column simply isn't copied onto the new row) the next time
// CreateNextIteration runs for its series. Returns ErrNativeTaskNotFound if
// id doesn't match any row.
func (s *Store) SetNextOccurrenceOverride(id string, date time.Time) error {
	result, err := s.db.Exec(`
		UPDATE native_tasks SET next_occurrence_override = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
	`, date.Format("2006-01-02"), id)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// checkRowsAffected returns ErrNativeTaskNotFound if the update matched no
// rows -- mirrors the RowsAffected() check already used in sqlite.go's
// ApproveAgentSession/DenyAgentSession for the same "silent 0-row update"
// class of bug.
func checkRowsAffected(result sql.Result) error {
	affected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if affected == 0 {
		return ErrNativeTaskNotFound
	}
	return nil
}

func scanNativeTasks(rows interface {
	Next() bool
	Scan(...interface{}) error
	Err() error
}) ([]models.Task, error) {
	var tasks []models.Task
	for rows.Next() {
		var t models.Task
		var labelsJSON string
		var dueDateStr *string
		var weekdaysStr string
		var nextOverrideStr string
		if err := rows.Scan(
			&t.ID, &t.Content, &t.Description, &t.ProjectName, &dueDateStr, &t.Priority, &t.Completed, &labelsJSON, &t.CreatedAt,
			&t.RecurrenceFreq, &t.RecurrenceInterval, &weekdaysStr, &t.RecurrenceSeriesID, &nextOverrideStr,
		); err != nil {
			return nil, err
		}
		if dueDateStr != nil {
			if parsed, err := time.Parse(time.RFC3339, *dueDateStr); err == nil {
				t.DueDate = &parsed
			} else if parsed, err := time.Parse("2006-01-02 15:04:05", *dueDateStr); err == nil {
				t.DueDate = &parsed
			} else if parsed, err := time.Parse("2006-01-02", *dueDateStr); err == nil {
				t.DueDate = &parsed
			}
		}
		if err := json.Unmarshal([]byte(labelsJSON), &t.Labels); err != nil {
			t.Labels = nil
		}
		t.RecurrenceWeekdays = parseWeekdays(weekdaysStr)
		if nextOverrideStr != "" {
			if parsed, err := time.Parse("2006-01-02", nextOverrideStr); err == nil {
				t.NextOccurrenceOverride = &parsed
			}
		}
		tasks = append(tasks, t)
	}
	return tasks, rows.Err()
}

// parseWeekdays parses a comma-separated list of 0-6 ints (e.g. "1,3,5"),
// returning nil for an empty string.
func parseWeekdays(s string) []int {
	if s == "" {
		return nil
	}
	parts := strings.Split(s, ",")
	weekdays := make([]int, 0, len(parts))
	for _, p := range parts {
		if n, err := strconv.Atoi(strings.TrimSpace(p)); err == nil {
			weekdays = append(weekdays, n)
		}
	}
	return weekdays
}

// formatWeekdays is the inverse of parseWeekdays.
func formatWeekdays(weekdays []int) string {
	if len(weekdays) == 0 {
		return ""
	}
	strs := make([]string, len(weekdays))
	for i, d := range weekdays {
		strs[i] = strconv.Itoa(d)
	}
	return strings.Join(strs, ",")
}