package store

import (
	"database/sql"
	"time"

	"task-dashboard/internal/models"
)

// SaveGoogleTasks replaces all cached Google Tasks
func (s *Store) SaveGoogleTasks(tasks []models.GoogleTask) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.Exec(`DELETE FROM google_tasks`); err != nil {
		return err
	}

	stmt, err := tx.Prepare(`
		INSERT INTO google_tasks (id, title, notes, status, completed, due_date, updated_at, list_id, url)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
	`)
	if err != nil {
		return err
	}
	defer func() { _ = stmt.Close() }()

	for _, t := range tasks {
		_, err = stmt.Exec(t.ID, t.Title, t.Notes, t.Status, t.Completed, t.DueDate, t.UpdatedAt, t.ListID, t.URL)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// parseGoogleTaskTime parses a TEXT-column timestamp written by mattn/go-sqlite3's
// default time.Time binding. The google_tasks.due_date/updated_at columns are declared
// TEXT (not DATETIME), so the driver never auto-converts them back to time.Time on read.
func parseGoogleTaskTime(s string) (time.Time, bool) {
	for _, layout := range []string{
		"2006-01-02 15:04:05.999999999-07:00",
		time.RFC3339Nano,
		time.RFC3339,
		"2006-01-02 15:04:05",
		"2006-01-02",
	} {
		if parsed, err := time.Parse(layout, s); err == nil {
			return parsed, true
		}
	}
	return time.Time{}, false
}

func scanGoogleTasks(rows *sql.Rows) ([]models.GoogleTask, error) {
	var tasks []models.GoogleTask
	for rows.Next() {
		var t models.GoogleTask
		var dueDate, updatedAt sql.NullString

		err := rows.Scan(&t.ID, &t.Title, &t.Notes, &t.Status, &t.Completed, &dueDate, &updatedAt, &t.ListID, &t.URL)
		if err != nil {
			return nil, err
		}

		if dueDate.Valid {
			if parsed, ok := parseGoogleTaskTime(dueDate.String); ok {
				t.DueDate = &parsed
			}
		}
		if updatedAt.Valid {
			if parsed, ok := parseGoogleTaskTime(updatedAt.String); ok {
				t.UpdatedAt = parsed
			}
		}

		tasks = append(tasks, t)
	}

	return tasks, rows.Err()
}

// GetGoogleTasks retrieves all cached Google Tasks
func (s *Store) GetGoogleTasks() ([]models.GoogleTask, error) {
	rows, err := s.db.Query(`
		SELECT id, title, notes, status, completed, due_date, updated_at, list_id, url
		FROM google_tasks
		ORDER BY completed ASC, CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	return scanGoogleTasks(rows)
}

// GetGoogleTasksByDateRange retrieves cached Google Tasks due before end, including overdue
// tasks (due before start) so they keep appearing until completed.
func (s *Store) GetGoogleTasksByDateRange(start, end time.Time) ([]models.GoogleTask, error) {
	rows, err := s.db.Query(`
		SELECT id, title, notes, status, completed, due_date, updated_at, list_id, url
		FROM google_tasks
		WHERE due_date IS NULL OR due_date < ?
		ORDER BY completed ASC, CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC
	`, end)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	return scanGoogleTasks(rows)
}