package storage

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"time"

	"github.com/thepeterstone/claudomator/internal/task"
)

// ErrTemplateNotFound is returned when a template ID does not exist.
var ErrTemplateNotFound = errors.New("template not found")

// Template is a reusable task configuration saved for repeated use.
type Template struct {
	ID          string            `json:"id"`
	Name        string            `json:"name"`
	Description string            `json:"description"`
	Claude      task.ClaudeConfig `json:"claude"`
	Timeout     string            `json:"timeout"`
	Priority    string            `json:"priority"`
	Tags        []string          `json:"tags"`
	CreatedAt   time.Time         `json:"created_at"`
	UpdatedAt   time.Time         `json:"updated_at"`
}

// CreateTemplate inserts a new template.
func (s *DB) CreateTemplate(tmpl *Template) error {
	configJSON, err := json.Marshal(tmpl.Claude)
	if err != nil {
		return fmt.Errorf("marshaling config: %w", err)
	}
	tagsJSON, err := json.Marshal(tmpl.Tags)
	if err != nil {
		return fmt.Errorf("marshaling tags: %w", err)
	}
	_, err = s.db.Exec(`
		INSERT INTO templates (id, name, description, config_json, timeout, priority, tags_json, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		tmpl.ID, tmpl.Name, tmpl.Description, string(configJSON),
		tmpl.Timeout, tmpl.Priority, string(tagsJSON),
		tmpl.CreatedAt.UTC(), tmpl.UpdatedAt.UTC(),
	)
	return err
}

// GetTemplate retrieves a template by ID, returning ErrTemplateNotFound if missing.
func (s *DB) GetTemplate(id string) (*Template, error) {
	row := s.db.QueryRow(`SELECT id, name, description, config_json, timeout, priority, tags_json, created_at, updated_at FROM templates WHERE id = ?`, id)
	return scanTemplate(row)
}

// ListTemplates returns all templates ordered by name.
func (s *DB) ListTemplates() ([]*Template, error) {
	rows, err := s.db.Query(`SELECT id, name, description, config_json, timeout, priority, tags_json, created_at, updated_at FROM templates ORDER BY name ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var templates []*Template
	for rows.Next() {
		tmpl, err := scanTemplate(rows)
		if err != nil {
			return nil, err
		}
		templates = append(templates, tmpl)
	}
	return templates, rows.Err()
}

// UpdateTemplate fully replaces a template's fields. Returns ErrTemplateNotFound if the ID is missing.
func (s *DB) UpdateTemplate(tmpl *Template) error {
	configJSON, err := json.Marshal(tmpl.Claude)
	if err != nil {
		return fmt.Errorf("marshaling config: %w", err)
	}
	tagsJSON, err := json.Marshal(tmpl.Tags)
	if err != nil {
		return fmt.Errorf("marshaling tags: %w", err)
	}
	result, err := s.db.Exec(`
		UPDATE templates SET name = ?, description = ?, config_json = ?, timeout = ?, priority = ?, tags_json = ?, updated_at = ?
		WHERE id = ?`,
		tmpl.Name, tmpl.Description, string(configJSON), tmpl.Timeout, tmpl.Priority, string(tagsJSON),
		tmpl.UpdatedAt.UTC(), tmpl.ID,
	)
	if err != nil {
		return err
	}
	n, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrTemplateNotFound
	}
	return nil
}

// DeleteTemplate removes a template by ID. Returns ErrTemplateNotFound if the ID is missing.
func (s *DB) DeleteTemplate(id string) error {
	result, err := s.db.Exec(`DELETE FROM templates WHERE id = ?`, id)
	if err != nil {
		return err
	}
	n, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrTemplateNotFound
	}
	return nil
}

func scanTemplate(row scanner) (*Template, error) {
	var (
		tmpl       Template
		configJSON string
		tagsJSON   string
	)
	err := row.Scan(&tmpl.ID, &tmpl.Name, &tmpl.Description, &configJSON,
		&tmpl.Timeout, &tmpl.Priority, &tagsJSON, &tmpl.CreatedAt, &tmpl.UpdatedAt)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrTemplateNotFound
		}
		return nil, err
	}
	if err := json.Unmarshal([]byte(configJSON), &tmpl.Claude); err != nil {
		return nil, fmt.Errorf("unmarshaling config: %w", err)
	}
	if err := json.Unmarshal([]byte(tagsJSON), &tmpl.Tags); err != nil {
		return nil, fmt.Errorf("unmarshaling tags: %w", err)
	}
	return &tmpl, nil
}