package memory

import (
	"database/sql"
	"fmt"
	"strings"
)

// Store owns task CRUD against SQLite.
type Store struct {
	db *sql.DB
}

// NewStore wraps an opened *sql.DB (already migrated).
func NewStore(db *sql.DB) *Store {
	return &Store{db: db}
}

// Close closes the underlying database.
func (s *Store) Close() error {
	if s == nil || s.db == nil {
		return nil
	}
	return s.db.Close()
}

// AddTask inserts a new open task and returns its id.
func (s *Store) AddTask(title string) (int64, error) {
	title = strings.TrimSpace(title)
	if title == "" {
		return 0, fmt.Errorf("task title required")
	}
	res, err := s.db.Exec(`INSERT INTO tasks (title, done) VALUES (?, 0)`, title)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

// Task is a persisted todo row.
type Task struct {
	ID        int64  `json:"id"`
	Title     string `json:"title"`
	Done      bool   `json:"done"`
	CreatedAt string `json:"created_at"`
}

// Memory is a persisted memory row.
type Memory struct {
	ID        int64  `json:"id"`
	Content   string `json:"content"`
	CreatedAt string `json:"created_at"`
}

// ListOpenTasks returns tasks where done = 0, newest first.
func (s *Store) ListOpenTasks() ([]Task, error) {
	rows, err := s.db.Query(`SELECT id, title, done, created_at FROM tasks WHERE done = 0 ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Task
	for rows.Next() {
		var t Task
		var done int
		if err := rows.Scan(&t.ID, &t.Title, &done, &t.CreatedAt); err != nil {
			return nil, err
		}
		t.Done = done != 0
		out = append(out, t)
	}
	return out, rows.Err()
}

// CompleteTask marks a task done by id.
func (s *Store) CompleteTask(id int64) error {
	res, err := s.db.Exec(`UPDATE tasks SET done = 1 WHERE id = ? AND done = 0`, id)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no open task with id %d", id)
	}
	return nil
}

// AddMemory inserts one memory and returns its id.
func (s *Store) AddMemory(content string) (int64, error) {
	content = strings.TrimSpace(content)
	if content == "" {
		return 0, fmt.Errorf("memory content required")
	}
	res, err := s.db.Exec(`INSERT INTO memories (content) VALUES (?)`, content)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

// ListMemories returns newest memories first.
func (s *Store) ListMemories(limit int) ([]Memory, error) {
	if limit <= 0 {
		limit = 20
	}
	if limit > 200 {
		limit = 200
	}
	rows, err := s.db.Query(`SELECT id, content, created_at FROM memories ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Memory
	for rows.Next() {
		var m Memory
		if err := rows.Scan(&m.ID, &m.Content, &m.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, m)
	}
	return out, rows.Err()
}

// SearchMemories performs a simple LIKE match over memory content.
func (s *Store) SearchMemories(query string, limit int) ([]Memory, error) {
	q := strings.TrimSpace(query)
	if q == "" {
		return nil, fmt.Errorf("query required")
	}
	if limit <= 0 {
		limit = 20
	}
	if limit > 200 {
		limit = 200
	}
	rows, err := s.db.Query(
		`SELECT id, content, created_at FROM memories WHERE content LIKE ? ORDER BY id DESC LIMIT ?`,
		"%"+q+"%", limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Memory
	for rows.Next() {
		var m Memory
		if err := rows.Scan(&m.ID, &m.Content, &m.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, m)
	}
	return out, rows.Err()
}

// DeleteMemory removes one memory by id.
func (s *Store) DeleteMemory(id int64) error {
	res, err := s.db.Exec(`DELETE FROM memories WHERE id = ?`, id)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no memory with id %d", id)
	}
	return nil
}

// MemoryStats returns count and total content size in characters.
func (s *Store) MemoryStats() (count int64, totalChars int64, err error) {
	row := s.db.QueryRow(`SELECT COUNT(*), COALESCE(SUM(LENGTH(content)), 0) FROM memories`)
	if err := row.Scan(&count, &totalChars); err != nil {
		return 0, 0, err
	}
	return count, totalChars, nil
}

// ClearMemories removes all long-term memory entries.
func (s *Store) ClearMemories() (int64, error) {
	res, err := s.db.Exec(`DELETE FROM memories`)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// CompactMemories deduplicates long-term memories by normalized content.
// It keeps the newest entry for each distinct content and deletes older duplicates.
func (s *Store) CompactMemories() (int64, error) {
	rows, err := s.db.Query(`SELECT id, content FROM memories ORDER BY id DESC`)
	if err != nil {
		return 0, err
	}
	defer rows.Close()
	seen := map[string]struct{}{}
	var deleteIDs []int64
	for rows.Next() {
		var id int64
		var content string
		if err := rows.Scan(&id, &content); err != nil {
			return 0, err
		}
		key := strings.ToLower(strings.TrimSpace(content))
		if key == "" {
			deleteIDs = append(deleteIDs, id)
			continue
		}
		if _, ok := seen[key]; ok {
			deleteIDs = append(deleteIDs, id)
			continue
		}
		seen[key] = struct{}{}
	}
	if err := rows.Err(); err != nil {
		return 0, err
	}
	var removed int64
	for _, id := range deleteIDs {
		res, err := s.db.Exec(`DELETE FROM memories WHERE id = ?`, id)
		if err != nil {
			return removed, err
		}
		n, _ := res.RowsAffected()
		removed += n
	}
	return removed, nil
}