package graph

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

	"github.com/local/glpi-neural-brain/internal/model"
)

const researchTaskColumns = `id,dedupe_key,topic,reason,requested_by,status,priority,seed_node_ids_json,questions_json,queries_de_json,queries_en_json,attempts,max_attempts,evidence_count,article_created,article_title,article_path,outcome,last_error,metadata_json,created_at_ns,updated_at_ns,available_at_ns,lease_until_ns,started_at_ns,completed_at_ns`

func normalizeResearchTask(task model.ResearchTask) model.ResearchTask {
	now := time.Now().UTC()
	task.ID = strings.TrimSpace(task.ID)
	if task.ID == "" {
		task.ID = ID("research-task", task.DedupeKey, task.Topic, now.Format(time.RFC3339Nano))
	}
	task.DedupeKey = strings.TrimSpace(task.DedupeKey)
	task.Topic = strings.TrimSpace(task.Topic)
	task.Reason = strings.TrimSpace(task.Reason)
	task.RequestedBy = strings.TrimSpace(task.RequestedBy)
	if task.RequestedBy == "" {
		task.RequestedBy = "brain"
	}
	if task.Status == "" {
		task.Status = "queued"
	}
	if task.Priority < 0 {
		task.Priority = 0
	}
	if task.Priority > 1 {
		task.Priority = 1
	}
	if task.MaxAttempts < 1 {
		task.MaxAttempts = 3
	}
	if task.CreatedAt.IsZero() {
		task.CreatedAt = now
	}
	if task.UpdatedAt.IsZero() {
		task.UpdatedAt = now
	}
	if task.AvailableAt.IsZero() {
		task.AvailableAt = now
	}
	if task.Metadata == nil {
		task.Metadata = map[string]any{}
	}
	task.SeedNodeIDs = uniqueExact(task.SeedNodeIDs)
	task.Questions = uniqueExact(task.Questions)
	task.QueriesDE = uniqueExact(task.QueriesDE)
	task.QueriesEN = uniqueExact(task.QueriesEN)
	return task
}

func uniqueExact(values []string) []string {
	seen := map[string]struct{}{}
	out := make([]string, 0, len(values))
	for _, value := range values {
		value = strings.TrimSpace(value)
		if value == "" {
			continue
		}
		if _, ok := seen[value]; ok {
			continue
		}
		seen[value] = struct{}{}
		out = append(out, value)
	}
	return out
}

func (s *Store) EnqueueResearchTask(ctx context.Context, task model.ResearchTask, cooldown time.Duration) (model.ResearchTask, bool, error) {
	// Keep the dedupe lookup and insert atomic within this Brain process. The
	// previous SELECT-then-INSERT sequence allowed two concurrent scanners/API
	// callers to observe no existing task and enqueue the same SearXNG work
	// twice. A process-local mutex is sufficient because one Store owns the DB.
	s.researchTaskMu.Lock()
	defer s.researchTaskMu.Unlock()
	task = normalizeResearchTask(task)
	if task.Topic == "" {
		return model.ResearchTask{}, false, errors.New("research task topic is required")
	}
	if len(task.Questions) == 0 && len(task.QueriesDE) == 0 && len(task.QueriesEN) == 0 {
		task.Questions = []string{task.Topic}
	}
	if task.DedupeKey != "" && cooldown > 0 {
		cutoff := time.Now().UTC().Add(-cooldown).UnixNano()
		var existingID string
		err := s.db.QueryRowContext(ctx, `SELECT id FROM research_tasks WHERE dedupe_key=? AND updated_at_ns>=? AND status NOT IN ('cancelled','failed') ORDER BY updated_at_ns DESC LIMIT 1`, task.DedupeKey, cutoff).Scan(&existingID)
		if err == nil {
			existing, getErr := s.GetResearchTask(ctx, existingID)
			return existing, false, getErr
		}
		if err != nil && !errors.Is(err, sql.ErrNoRows) {
			return model.ResearchTask{}, false, err
		}
	}
	seedJSON, _ := json.Marshal(task.SeedNodeIDs)
	questionsJSON, _ := json.Marshal(task.Questions)
	queriesDEJSON, _ := json.Marshal(task.QueriesDE)
	queriesENJSON, _ := json.Marshal(task.QueriesEN)
	metadataJSON, _ := json.Marshal(task.Metadata)
	_, err := s.db.ExecContext(ctx, `INSERT INTO research_tasks(`+researchTaskColumns+`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
		task.ID, task.DedupeKey, task.Topic, task.Reason, task.RequestedBy, task.Status, task.Priority,
		string(seedJSON), string(questionsJSON), string(queriesDEJSON), string(queriesENJSON), task.Attempts, task.MaxAttempts,
		task.EvidenceCount, boolInt(task.ArticleCreated), task.ArticleTitle, task.ArticlePath, task.Outcome, task.LastError, string(metadataJSON),
		task.CreatedAt.UnixNano(), task.UpdatedAt.UnixNano(), task.AvailableAt.UnixNano(), timeNS(task.LeaseUntil), timeNS(task.StartedAt), timeNS(task.CompletedAt))
	if err != nil {
		return model.ResearchTask{}, false, fmt.Errorf("enqueue research task: %w", err)
	}
	return task, true, nil
}

func (s *Store) GetResearchTask(ctx context.Context, id string) (model.ResearchTask, error) {
	row := s.db.QueryRowContext(ctx, `SELECT `+researchTaskColumns+` FROM research_tasks WHERE id=?`, id)
	return scanResearchTask(row)
}

func (s *Store) ListResearchTasks(ctx context.Context, limit int, statuses ...string) ([]model.ResearchTask, error) {
	if limit < 1 || limit > 500 {
		limit = 100
	}
	query := `SELECT ` + researchTaskColumns + ` FROM research_tasks`
	args := []any{}
	if len(statuses) > 0 {
		placeholders := make([]string, 0, len(statuses))
		for _, status := range statuses {
			status = strings.TrimSpace(status)
			if status == "" {
				continue
			}
			placeholders = append(placeholders, "?")
			args = append(args, status)
		}
		if len(placeholders) > 0 {
			query += ` WHERE status IN (` + strings.Join(placeholders, ",") + `)`
		}
	}
	query += ` ORDER BY CASE status WHEN 'running' THEN 0 WHEN 'reserved' THEN 1 WHEN 'queued' THEN 2 WHEN 'deferred' THEN 3 ELSE 4 END, priority DESC, updated_at_ns DESC LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []model.ResearchTask{}
	for rows.Next() {
		task, err := scanResearchTask(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, task)
	}
	return out, rows.Err()
}

func (s *Store) LeaseNextResearchTask(ctx context.Context, minPriority float64, lease time.Duration) (model.ResearchTask, bool, error) {
	now := time.Now().UTC()
	if lease <= 0 {
		lease = 45 * time.Minute
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return model.ResearchTask{}, false, err
	}
	defer tx.Rollback()
	var id string
	err = tx.QueryRowContext(ctx, `SELECT id FROM research_tasks WHERE status IN ('queued','deferred') AND priority>=? AND available_at_ns<=? AND attempts<max_attempts ORDER BY priority DESC, created_at_ns ASC LIMIT 1`, minPriority, now.UnixNano()).Scan(&id)
	if errors.Is(err, sql.ErrNoRows) {
		return model.ResearchTask{}, false, nil
	}
	if err != nil {
		return model.ResearchTask{}, false, err
	}
	res, err := tx.ExecContext(ctx, `UPDATE research_tasks SET status='reserved', attempts=attempts+1, lease_until_ns=?, started_at_ns=CASE WHEN started_at_ns=0 THEN ? ELSE started_at_ns END, updated_at_ns=? WHERE id=? AND status IN ('queued','deferred')`, now.Add(lease).UnixNano(), now.UnixNano(), now.UnixNano(), id)
	if err != nil {
		return model.ResearchTask{}, false, err
	}
	rows, _ := res.RowsAffected()
	if rows != 1 {
		return model.ResearchTask{}, false, nil
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO research_task_attempts(task_id,attempt,status,message,metadata_json,created_at_ns) SELECT id,attempts,'reserved','Task wurde vom autonomen Worker reserviert','{}',? FROM research_tasks WHERE id=?`, now.UnixNano(), id); err != nil {
		return model.ResearchTask{}, false, err
	}
	if err := tx.Commit(); err != nil {
		return model.ResearchTask{}, false, err
	}
	task, err := s.GetResearchTask(ctx, id)
	return task, err == nil, err
}

func (s *Store) MarkResearchTaskRunning(ctx context.Context, id string) error {
	now := time.Now().UTC().UnixNano()
	result, err := s.db.ExecContext(ctx, `UPDATE research_tasks SET status='running',updated_at_ns=? WHERE id=? AND status='reserved'`, now, id)
	if err != nil {
		return err
	}
	rows, err := result.RowsAffected()
	if err != nil {
		return err
	}
	if rows != 1 {
		return fmt.Errorf("research task %q is no longer reserved", id)
	}
	return nil
}

func (s *Store) CompleteResearchTask(ctx context.Context, task model.ResearchTask) error {
	now := time.Now().UTC()
	task.Status = "completed"
	task.UpdatedAt = now
	task.CompletedAt = now
	metadataJSON, _ := json.Marshal(task.Metadata)
	_, err := s.db.ExecContext(ctx, `UPDATE research_tasks SET status='completed',evidence_count=?,article_created=?,article_title=?,article_path=?,outcome=?,last_error='',metadata_json=?,lease_until_ns=0,completed_at_ns=?,updated_at_ns=? WHERE id=?`, task.EvidenceCount, boolInt(task.ArticleCreated), task.ArticleTitle, task.ArticlePath, task.Outcome, string(metadataJSON), now.UnixNano(), now.UnixNano(), task.ID)
	if err == nil {
		_, _ = s.db.ExecContext(ctx, `INSERT INTO research_task_attempts(task_id,attempt,status,message,metadata_json,created_at_ns) SELECT id,attempts,'completed',?, ?, ? FROM research_tasks WHERE id=?`, task.Outcome, string(metadataJSON), now.UnixNano(), task.ID)
	}
	return err
}

func (s *Store) FailResearchTask(ctx context.Context, task model.ResearchTask, retryDelay time.Duration) error {
	now := time.Now().UTC()
	status := "failed"
	available := now
	if task.Attempts < task.MaxAttempts {
		status = "deferred"
		if retryDelay <= 0 {
			retryDelay = time.Duration(task.Attempts*task.Attempts) * 10 * time.Minute
		}
		available = now.Add(retryDelay)
	}
	_, err := s.db.ExecContext(ctx, `UPDATE research_tasks SET status=?,last_error=?,outcome=?,available_at_ns=?,lease_until_ns=0,updated_at_ns=? WHERE id=?`, status, task.LastError, task.Outcome, available.UnixNano(), now.UnixNano(), task.ID)
	if err == nil {
		_, _ = s.db.ExecContext(ctx, `INSERT INTO research_task_attempts(task_id,attempt,status,message,metadata_json,created_at_ns) SELECT id,attempts,?,?, '{}',? FROM research_tasks WHERE id=?`, status, task.LastError, now.UnixNano(), task.ID)
	}
	return err
}

func (s *Store) CancelResearchTask(ctx context.Context, id string) (bool, error) {
	now := time.Now().UTC().UnixNano()
	res, err := s.db.ExecContext(ctx, `UPDATE research_tasks SET status='cancelled',lease_until_ns=0,completed_at_ns=?,updated_at_ns=? WHERE id=? AND status IN ('queued','deferred','reserved')`, now, now, id)
	if err != nil {
		return false, err
	}
	rows, _ := res.RowsAffected()
	return rows == 1, nil
}

func (s *Store) ResetExpiredResearchTaskLeases(ctx context.Context) (int64, error) {
	now := time.Now().UTC().UnixNano()
	res, err := s.db.ExecContext(ctx, `UPDATE research_tasks SET status='deferred',available_at_ns=?,lease_until_ns=0,last_error='Worker-Lease ist abgelaufen; Aufgabe wurde erneut eingeplant',updated_at_ns=? WHERE status IN ('reserved','running') AND lease_until_ns>0 AND lease_until_ns<?`, now, now, now)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

func (s *Store) CountResearchTasksCompletedSince(ctx context.Context, since time.Time) (int, error) {
	var count int
	err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM research_tasks WHERE status='completed' AND completed_at_ns>=?`, since.UnixNano()).Scan(&count)
	return count, err
}

func (s *Store) ResearchTaskCounts(ctx context.Context) (map[string]int, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT status,COUNT(*) FROM research_tasks GROUP BY status`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]int{}
	for rows.Next() {
		var status string
		var count int
		if err := rows.Scan(&status, &count); err != nil {
			return nil, err
		}
		out[status] = count
	}
	return out, rows.Err()
}

type rowScanner interface {
	Scan(dest ...any) error
}

func scanResearchTask(row rowScanner) (model.ResearchTask, error) {
	var task model.ResearchTask
	var seedJSON, questionsJSON, queriesDEJSON, queriesENJSON, metadataJSON string
	var articleCreated int
	var created, updated, available, lease, started, completed int64
	err := row.Scan(&task.ID, &task.DedupeKey, &task.Topic, &task.Reason, &task.RequestedBy, &task.Status, &task.Priority,
		&seedJSON, &questionsJSON, &queriesDEJSON, &queriesENJSON, &task.Attempts, &task.MaxAttempts, &task.EvidenceCount,
		&articleCreated, &task.ArticleTitle, &task.ArticlePath, &task.Outcome, &task.LastError, &metadataJSON,
		&created, &updated, &available, &lease, &started, &completed)
	if err != nil {
		return model.ResearchTask{}, err
	}
	_ = json.Unmarshal([]byte(seedJSON), &task.SeedNodeIDs)
	_ = json.Unmarshal([]byte(questionsJSON), &task.Questions)
	_ = json.Unmarshal([]byte(queriesDEJSON), &task.QueriesDE)
	_ = json.Unmarshal([]byte(queriesENJSON), &task.QueriesEN)
	_ = json.Unmarshal([]byte(metadataJSON), &task.Metadata)
	if task.Metadata == nil {
		task.Metadata = map[string]any{}
	}
	task.ArticleCreated = articleCreated != 0
	task.CreatedAt = fromNS(created)
	task.UpdatedAt = fromNS(updated)
	task.AvailableAt = fromNS(available)
	task.LeaseUntil = fromNS(lease)
	task.StartedAt = fromNS(started)
	task.CompletedAt = fromNS(completed)
	return task, nil
}

func boolInt(value bool) int {
	if value {
		return 1
	}
	return 0
}

func timeNS(value time.Time) int64 {
	if value.IsZero() {
		return 0
	}
	return value.UTC().UnixNano()
}

func fromNS(value int64) time.Time {
	if value <= 0 {
		return time.Time{}
	}
	return time.Unix(0, value).UTC()
}

// MergeQueuedResearchTask folds newly discovered context into an already queued
// or deferred task. It deliberately does not mutate running/reserved work.
func (s *Store) MergeQueuedResearchTask(ctx context.Context, id string, incoming model.ResearchTask) (model.ResearchTask, bool, error) {
	s.researchTaskMu.Lock()
	defer s.researchTaskMu.Unlock()
	existing, err := s.GetResearchTask(ctx, strings.TrimSpace(id))
	if err != nil {
		return model.ResearchTask{}, false, err
	}
	if existing.Status != "queued" && existing.Status != "deferred" {
		return existing, false, nil
	}
	incoming = normalizeResearchTask(incoming)
	if incoming.Priority > existing.Priority {
		existing.Priority = incoming.Priority
	}
	existing.SeedNodeIDs = uniqueExact(append(existing.SeedNodeIDs, incoming.SeedNodeIDs...))
	existing.Questions = uniqueExact(append(existing.Questions, incoming.Questions...))
	existing.QueriesDE = uniqueExact(append(existing.QueriesDE, incoming.QueriesDE...))
	existing.QueriesEN = uniqueExact(append(existing.QueriesEN, incoming.QueriesEN...))
	if strings.TrimSpace(existing.Reason) == "" {
		existing.Reason = incoming.Reason
	}
	if existing.Metadata == nil {
		existing.Metadata = map[string]any{}
	}
	merged := 0
	if value, ok := existing.Metadata["semantic_merge_count"].(float64); ok {
		merged = int(value)
	} else if value, ok := existing.Metadata["semantic_merge_count"].(int); ok {
		merged = value
	}
	existing.Metadata["semantic_merge_count"] = merged + 1
	existing.Metadata["last_semantic_merge_topic"] = incoming.Topic
	now := time.Now().UTC()
	existing.UpdatedAt = now
	seedJSON, _ := json.Marshal(existing.SeedNodeIDs)
	questionsJSON, _ := json.Marshal(existing.Questions)
	queriesDEJSON, _ := json.Marshal(existing.QueriesDE)
	queriesENJSON, _ := json.Marshal(existing.QueriesEN)
	metadataJSON, _ := json.Marshal(existing.Metadata)
	res, err := s.db.ExecContext(ctx, `UPDATE research_tasks SET priority=?,seed_node_ids_json=?,questions_json=?,queries_de_json=?,queries_en_json=?,reason=?,metadata_json=?,updated_at_ns=? WHERE id=? AND status IN ('queued','deferred')`,
		existing.Priority, string(seedJSON), string(questionsJSON), string(queriesDEJSON), string(queriesENJSON), existing.Reason, string(metadataJSON), now.UnixNano(), existing.ID)
	if err != nil {
		return model.ResearchTask{}, false, err
	}
	rows, _ := res.RowsAffected()
	if rows != 1 {
		latest, getErr := s.GetResearchTask(ctx, existing.ID)
		return latest, false, getErr
	}
	return existing, true, nil
}

// ResearchTaskPersistentTotals returns restart-stable autonomous research
// counters. These values are the source of truth for status/readiness; the
// in-memory counters remain useful only as per-process telemetry.
func (s *Store) ResearchTaskPersistentTotals(ctx context.Context) (map[string]int, error) {
	var completed, failed, evidence, articles int
	err := s.db.QueryRowContext(ctx, `SELECT
		COALESCE(SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END),0),
		COALESCE(SUM(CASE WHEN status='failed' THEN 1 ELSE 0 END),0),
		COALESCE(SUM(CASE WHEN status='completed' THEN evidence_count ELSE 0 END),0),
		COALESCE(SUM(CASE WHEN status='completed' AND article_created=1 THEN 1 ELSE 0 END),0)
		FROM research_tasks`).Scan(&completed, &failed, &evidence, &articles)
	if err != nil {
		return nil, err
	}
	return map[string]int{"completed": completed, "failed": failed, "evidence": evidence, "articles": articles}, nil
}