package scheduler

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log/slog"
	"time"

	"github.com/google/uuid"
	_ "github.com/mattn/go-sqlite3"
	"github.com/nugget/thane-ai-agent/internal/platform/database"
)

// Store handles task and execution persistence.
type Store struct {
	db *sql.DB
}

// NewStore creates a scheduler store backed by db. The caller owns
// db's lifecycle; NewStore only applies the schema.
func NewStore(db *sql.DB, logger *slog.Logger) (*Store, error) {
	if err := database.Migrate(db, schema, logger); err != nil {
		return nil, err
	}
	return &Store{db: db}, nil
}

// NewID generates a new UUIDv7.
func NewID() string {
	id, err := uuid.NewV7()
	if err != nil {
		// Fallback to v4 if v7 fails
		return uuid.New().String()
	}
	return id.String()
}

// CreateTask persists a new task.
func (s *Store) CreateTask(t *Task) error {
	if t.ID == "" {
		t.ID = NewID()
	}
	if t.CreatedAt.IsZero() {
		t.CreatedAt = time.Now()
	}
	t.UpdatedAt = time.Now()

	scheduleJSON, err := json.Marshal(t.Schedule)
	if err != nil {
		return fmt.Errorf("marshal schedule: %w", err)
	}

	payloadJSON, err := json.Marshal(t.Payload)
	if err != nil {
		return fmt.Errorf("marshal payload: %w", err)
	}

	enabled := 0
	if t.Enabled {
		enabled = 1
	}

	_, err = s.db.Exec(`
		INSERT INTO tasks (id, name, schedule_json, payload_json, enabled, created_at, created_by, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
	`, t.ID, t.Name, string(scheduleJSON), string(payloadJSON), enabled,
		t.CreatedAt.Format(time.RFC3339Nano), t.CreatedBy, t.UpdatedAt.Format(time.RFC3339Nano))

	return err
}

// GetTask retrieves a task by ID.
func (s *Store) GetTask(id string) (*Task, error) {
	row := s.db.QueryRow(`
		SELECT id, name, schedule_json, payload_json, enabled, created_at, created_by, updated_at
		FROM tasks WHERE id = ?
	`, id)

	return s.scanTask(row)
}

// GetTaskByName retrieves a task by its human-readable name.
// Returns nil, nil when no task with the given name exists.
// If multiple tasks share the same name (which should not happen),
// returns an error to surface the data integrity problem.
func (s *Store) GetTaskByName(name string) (*Task, error) {
	rows, err := s.db.Query(`
		SELECT id, name, schedule_json, payload_json, enabled, created_at, created_by, updated_at
		FROM tasks WHERE name = ? ORDER BY updated_at DESC
	`, name)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	if !rows.Next() {
		return nil, rows.Err()
	}

	t, err := s.scanTaskRow(rows)
	if err != nil {
		return nil, err
	}

	if rows.Next() {
		return nil, fmt.Errorf("multiple tasks found with name %q", name)
	}

	return t, rows.Err()
}

// ListTasks returns all tasks, optionally filtered by enabled status.
func (s *Store) ListTasks(enabledOnly bool) ([]*Task, error) {
	query := `SELECT id, name, schedule_json, payload_json, enabled, created_at, created_by, updated_at FROM tasks`
	if enabledOnly {
		query += ` WHERE enabled = 1`
	}
	query += ` ORDER BY created_at DESC`

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

	var tasks []*Task
	for rows.Next() {
		t, err := s.scanTaskRow(rows)
		if err != nil {
			return nil, err
		}
		tasks = append(tasks, t)
	}

	return tasks, rows.Err()
}

// UpdateTask updates an existing task.
func (s *Store) UpdateTask(t *Task) error {
	t.UpdatedAt = time.Now()

	scheduleJSON, err := json.Marshal(t.Schedule)
	if err != nil {
		return fmt.Errorf("marshal schedule: %w", err)
	}

	payloadJSON, err := json.Marshal(t.Payload)
	if err != nil {
		return fmt.Errorf("marshal payload: %w", err)
	}

	enabled := 0
	if t.Enabled {
		enabled = 1
	}

	_, err = s.db.Exec(`
		UPDATE tasks SET name = ?, schedule_json = ?, payload_json = ?, enabled = ?, updated_at = ?
		WHERE id = ?
	`, t.Name, string(scheduleJSON), string(payloadJSON), enabled,
		t.UpdatedAt.Format(time.RFC3339Nano), t.ID)

	return err
}

// DeleteTask removes a task and its executions.
func (s *Store) DeleteTask(id string) error {
	_, err := s.db.Exec(`DELETE FROM tasks WHERE id = ?`, id)
	return err
}

// CreateExecution records a new execution.
func (s *Store) CreateExecution(e *Execution) error {
	if e.ID == "" {
		e.ID = NewID()
	}

	var startedAt, completedAt *string
	if e.StartedAt != nil {
		s := e.StartedAt.Format(time.RFC3339Nano)
		startedAt = &s
	}
	if e.CompletedAt != nil {
		s := e.CompletedAt.Format(time.RFC3339Nano)
		completedAt = &s
	}

	_, err := s.db.Exec(`
		INSERT INTO executions (id, task_id, scheduled_at, started_at, completed_at, status, result)
		VALUES (?, ?, ?, ?, ?, ?, ?)
	`, e.ID, e.TaskID, e.ScheduledAt.Format(time.RFC3339Nano), startedAt, completedAt, e.Status, e.Result)

	return err
}

// UpdateExecution updates an execution record.
func (s *Store) UpdateExecution(e *Execution) error {
	var startedAt, completedAt *string
	if e.StartedAt != nil {
		s := e.StartedAt.Format(time.RFC3339Nano)
		startedAt = &s
	}
	if e.CompletedAt != nil {
		s := e.CompletedAt.Format(time.RFC3339Nano)
		completedAt = &s
	}

	_, err := s.db.Exec(`
		UPDATE executions SET started_at = ?, completed_at = ?, status = ?, result = ?
		WHERE id = ?
	`, startedAt, completedAt, e.Status, e.Result, e.ID)

	return err
}

// GetExecution retrieves an execution by ID.
func (s *Store) GetExecution(id string) (*Execution, error) {
	row := s.db.QueryRow(`
		SELECT id, task_id, scheduled_at, started_at, completed_at, status, result
		FROM executions WHERE id = ?
	`, id)

	return s.scanExecution(row)
}

// ListExecutions returns executions for a task.
func (s *Store) ListExecutions(taskID string, limit int) ([]*Execution, error) {
	if limit <= 0 {
		limit = 100
	}

	rows, err := s.db.Query(`
		SELECT id, task_id, scheduled_at, started_at, completed_at, status, result
		FROM executions WHERE task_id = ?
		ORDER BY scheduled_at DESC LIMIT ?
	`, taskID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var execs []*Execution
	for rows.Next() {
		e, err := s.scanExecutionRow(rows)
		if err != nil {
			return nil, err
		}
		execs = append(execs, e)
	}

	return execs, rows.Err()
}

// GetPendingExecutions returns executions that need to run.
func (s *Store) GetPendingExecutions() ([]*Execution, error) {
	rows, err := s.db.Query(`
		SELECT id, task_id, scheduled_at, started_at, completed_at, status, result
		FROM executions WHERE status = ?
		ORDER BY scheduled_at ASC
	`, StatusPending)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var execs []*Execution
	for rows.Next() {
		e, err := s.scanExecutionRow(rows)
		if err != nil {
			return nil, err
		}
		execs = append(execs, e)
	}

	return execs, rows.Err()
}

// Helper scan functions

func (s *Store) scanTask(row *sql.Row) (*Task, error) {
	var t Task
	var scheduleJSON, payloadJSON string
	var enabled int
	var createdAt, updatedAt string

	err := row.Scan(&t.ID, &t.Name, &scheduleJSON, &payloadJSON, &enabled, &createdAt, &t.CreatedBy, &updatedAt)
	if err != nil {
		return nil, err
	}

	if err := json.Unmarshal([]byte(scheduleJSON), &t.Schedule); err != nil {
		return nil, fmt.Errorf("unmarshal schedule: %w", err)
	}
	if err := json.Unmarshal([]byte(payloadJSON), &t.Payload); err != nil {
		return nil, fmt.Errorf("unmarshal payload: %w", err)
	}

	t.Enabled = enabled == 1
	if t.CreatedAt, err = database.ParseTimestamp(createdAt); err != nil {
		return nil, fmt.Errorf("parse created_at: %w", err)
	}
	if t.UpdatedAt, err = database.ParseTimestamp(updatedAt); err != nil {
		return nil, fmt.Errorf("parse updated_at: %w", err)
	}

	return &t, nil
}

func (s *Store) scanTaskRow(rows *sql.Rows) (*Task, error) {
	var t Task
	var scheduleJSON, payloadJSON string
	var enabled int
	var createdAt, updatedAt string

	err := rows.Scan(&t.ID, &t.Name, &scheduleJSON, &payloadJSON, &enabled, &createdAt, &t.CreatedBy, &updatedAt)
	if err != nil {
		return nil, err
	}

	if err := json.Unmarshal([]byte(scheduleJSON), &t.Schedule); err != nil {
		return nil, fmt.Errorf("unmarshal schedule: %w", err)
	}
	if err := json.Unmarshal([]byte(payloadJSON), &t.Payload); err != nil {
		return nil, fmt.Errorf("unmarshal payload: %w", err)
	}

	t.Enabled = enabled == 1
	if t.CreatedAt, err = database.ParseTimestamp(createdAt); err != nil {
		return nil, fmt.Errorf("parse created_at: %w", err)
	}
	if t.UpdatedAt, err = database.ParseTimestamp(updatedAt); err != nil {
		return nil, fmt.Errorf("parse updated_at: %w", err)
	}

	return &t, nil
}

func (s *Store) scanExecution(row *sql.Row) (*Execution, error) {
	var e Execution
	var scheduledAt string
	var startedAt, completedAt, result sql.NullString

	err := row.Scan(&e.ID, &e.TaskID, &scheduledAt, &startedAt, &completedAt, &e.Status, &result)
	if err != nil {
		return nil, err
	}

	if e.ScheduledAt, err = database.ParseTimestamp(scheduledAt); err != nil {
		return nil, fmt.Errorf("parse scheduled_at: %w", err)
	}
	if startedAt.Valid {
		ts, tsErr := database.ParseTimestamp(startedAt.String)
		if tsErr != nil {
			return nil, fmt.Errorf("parse started_at: %w", tsErr)
		}
		e.StartedAt = &ts
	}
	if completedAt.Valid {
		ts, tsErr := database.ParseTimestamp(completedAt.String)
		if tsErr != nil {
			return nil, fmt.Errorf("parse completed_at: %w", tsErr)
		}
		e.CompletedAt = &ts
	}
	if result.Valid {
		e.Result = result.String
	}

	return &e, nil
}

func (s *Store) scanExecutionRow(rows *sql.Rows) (*Execution, error) {
	var e Execution
	var scheduledAt string
	var startedAt, completedAt, result sql.NullString

	err := rows.Scan(&e.ID, &e.TaskID, &scheduledAt, &startedAt, &completedAt, &e.Status, &result)
	if err != nil {
		return nil, err
	}

	if e.ScheduledAt, err = database.ParseTimestamp(scheduledAt); err != nil {
		return nil, fmt.Errorf("parse scheduled_at: %w", err)
	}
	if startedAt.Valid {
		ts, tsErr := database.ParseTimestamp(startedAt.String)
		if tsErr != nil {
			return nil, fmt.Errorf("parse started_at: %w", tsErr)
		}
		e.StartedAt = &ts
	}
	if completedAt.Valid {
		ts, tsErr := database.ParseTimestamp(completedAt.String)
		if tsErr != nil {
			return nil, fmt.Errorf("parse completed_at: %w", tsErr)
		}
		e.CompletedAt = &ts
	}
	if result.Valid {
		e.Result = result.String
	}

	return &e, nil
}
