package app

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/store/message"
	"github.com/tidbcloud/AgentTeam/internal/store/taskcompat"
)

var channelAgentTaskSummariesSQL = `
WITH
visible_agents AS (
    SELECT
      a.id AS agent_id,
      a.member_id,
      MIN(CASE WHEN sm.visibility = 'public' THEN 0 ELSE 1 END) AS visibility_order,
      MIN(sm.created_at) AS membership_created_at
    FROM scope_memberships sm
    JOIN members agent_member
      ON agent_member.workspace_id = $1
     AND agent_member.id = sm.member_id
     AND agent_member.type = 'agent'
     AND agent_member.status = 'active'
    JOIN agents a
      ON a.workspace_id = $1
     AND a.member_id = agent_member.id
    WHERE a.status <> 'archived'
      AND sm.scope_id = $2
      AND sm.visibility IN ('public', 'private')
      AND sm.removed_at IS NULL
      AND sm.archived_at IS NULL
      AND sm.effective_to_seq IS NULL
    GROUP BY a.id, a.member_id
),
current_channel_candidates AS (
    SELECT va.agent_id, t.id AS task_id, tb.channel_id, t.task_number, t.title, t.status,
           'active_target' AS role, 0 AS role_priority,
           t.last_activity_at, t.updated_at, t.created_at
    FROM visible_agents va
    JOIN task_active_targets tat
      ON tat.workspace_id = $1
     AND tat.agent_id = va.agent_id
    JOIN tasks t
      ON t.id = tat.task_id
     AND t.status IN ('todo', 'in_progress', 'in_review')
    JOIN task_boards tb
      ON tb.id = t.board_id
     AND tb.workspace_id = $1
     AND tb.channel_id = $2
    JOIN scopes task_scope
      ON task_scope.id = t.task_scope_id
     AND task_scope.workspace_id = $1
     AND task_scope.type = 'task'
     AND task_scope.status = 'active'
     AND task_scope.archived_at IS NULL
    JOIN scopes task_channel
      ON task_channel.id = tb.channel_id
     AND task_channel.workspace_id = $1
     AND task_channel.type = 'channel'
     AND task_channel.status = 'active'
     AND task_channel.archived_at IS NULL
     AND t.channel_id = tb.channel_id
    UNION ALL
    SELECT va.agent_id, t.id, tb.channel_id, t.task_number, t.title, t.status,
           'assignee' AS role, 1 AS role_priority,
           t.last_activity_at, t.updated_at, t.created_at
    FROM visible_agents va
    JOIN tasks t
      ON t.status IN ('todo', 'in_progress', 'in_review')
     AND ` + taskcompat.EffectiveAssigneeRefSQL("t") + ` = va.member_id
    JOIN task_boards tb
      ON tb.id = t.board_id
     AND tb.workspace_id = $1
     AND tb.channel_id = $2
    JOIN scopes task_scope
      ON task_scope.id = t.task_scope_id
     AND task_scope.workspace_id = $1
     AND task_scope.type = 'task'
     AND task_scope.status = 'active'
     AND task_scope.archived_at IS NULL
    JOIN scopes task_channel
      ON task_channel.id = tb.channel_id
     AND task_channel.workspace_id = $1
     AND task_channel.type = 'channel'
     AND task_channel.status = 'active'
     AND task_channel.archived_at IS NULL
     AND t.channel_id = tb.channel_id
),
current_channel_counts AS (
    SELECT agent_id, COUNT(DISTINCT task_id) AS task_count
    FROM current_channel_candidates
    GROUP BY agent_id
),
ranked_current_channel AS (
    SELECT agent_id, task_id, channel_id, task_number, title, status, role, last_activity_at,
           ROW_NUMBER() OVER (
             PARTITION BY agent_id
             ORDER BY role_priority ASC, COALESCE(last_activity_at, updated_at, created_at) DESC, task_number ASC, task_id ASC
           ) AS summary_rank
    FROM current_channel_candidates
),
busy_elsewhere_agents AS (
    SELECT DISTINCT va.agent_id
    FROM visible_agents va
    JOIN tasks t
      ON t.status IN ('todo', 'in_progress', 'in_review')
     AND ` + taskcompat.EffectiveAssigneeRefSQL("t") + ` = va.member_id
    JOIN task_boards tb
      ON tb.id = t.board_id
     AND tb.workspace_id = $1
     AND tb.channel_id <> $2
    JOIN scopes task_scope
      ON task_scope.id = t.task_scope_id
     AND task_scope.workspace_id = $1
     AND task_scope.type = 'task'
     AND task_scope.status = 'active'
     AND task_scope.archived_at IS NULL
    JOIN scopes task_channel
      ON task_channel.id = tb.channel_id
     AND task_channel.workspace_id = $1
     AND task_channel.type = 'channel'
     AND task_channel.status = 'active'
     AND task_channel.archived_at IS NULL
     AND t.channel_id = tb.channel_id
    UNION
    SELECT DISTINCT va.agent_id
    FROM visible_agents va
    JOIN task_active_targets tat
      ON tat.workspace_id = $1
     AND tat.agent_id = va.agent_id
    JOIN tasks t
      ON t.id = tat.task_id
     AND t.status IN ('todo', 'in_progress', 'in_review')
    JOIN task_boards tb
      ON tb.id = t.board_id
     AND tb.workspace_id = $1
     AND tb.channel_id <> $2
    JOIN scopes task_scope
      ON task_scope.id = t.task_scope_id
     AND task_scope.workspace_id = $1
     AND task_scope.type = 'task'
     AND task_scope.status = 'active'
     AND task_scope.archived_at IS NULL
    JOIN scopes task_channel
      ON task_channel.id = tb.channel_id
     AND task_channel.workspace_id = $1
     AND task_channel.type = 'channel'
     AND task_channel.status = 'active'
     AND task_channel.archived_at IS NULL
     AND t.channel_id = tb.channel_id
)
SELECT va.agent_id,
       CASE
         WHEN ranked.task_id IS NOT NULL THEN 'working_in_channel'
         WHEN busy.agent_id IS NOT NULL THEN 'busy_elsewhere'
         ELSE 'idle'
       END AS state,
       COALESCE(ranked.task_id, '') AS task_id,
       COALESCE(ranked.channel_id, '') AS task_channel_id,
       COALESCE(ranked.task_number, 0) AS task_number,
       COALESCE(ranked.title, '') AS title,
       COALESCE(ranked.status, '') AS status,
       COALESCE(ranked.role, '') AS role,
       ranked.last_activity_at,
       GREATEST(COALESCE(counts.task_count, 0) - 1, 0) AS more_count
FROM visible_agents va
LEFT JOIN ranked_current_channel ranked
  ON ranked.agent_id = va.agent_id
 AND ranked.summary_rank = 1
LEFT JOIN current_channel_counts counts
  ON counts.agent_id = va.agent_id
LEFT JOIN busy_elsewhere_agents busy
  ON busy.agent_id = va.agent_id
ORDER BY va.visibility_order ASC, va.membership_created_at ASC, va.agent_id ASC
`

func (store *Store) ChannelAgentTaskSummariesForUser(ctx context.Context, userID string, workspaceID string, channelID string) ([]message.ChannelAgentTaskSummary, error) {
	channelAccess, err := store.loadAuthorizedHumanChannelAccess(ctx, store.db, userID, workspaceID, channelID, channelReadAction)
	if err != nil {
		return nil, err
	}
	if channelAccess.Channel.Type != "channel" {
		return nil, badRequest("thread channels do not have direct members")
	}

	return store.listChannelAgentTaskSummaries(ctx, workspaceID, channelID, time.Now().UTC())
}

func (store *Store) listChannelAgentTaskSummaries(ctx context.Context, workspaceID string, channelID string, updatedAt time.Time) ([]message.ChannelAgentTaskSummary, error) {
	rows, err := store.db.Query(ctx, channelAgentTaskSummariesSQL, strings.TrimSpace(workspaceID), strings.TrimSpace(channelID))
	if err != nil {
		return nil, fmt.Errorf("list channel agent task summaries: %w", err)
	}
	defer rows.Close()

	summaries := make([]message.ChannelAgentTaskSummary, 0)
	for rows.Next() {
		var summary message.ChannelAgentTaskSummary
		var taskID string
		var taskChannelID string
		var taskNumber int64
		var taskTitle string
		var taskStatus string
		var taskRole string
		var lastActivityAt sql.NullTime
		if err := rows.Scan(
			&summary.AgentID,
			&summary.State,
			&taskID,
			&taskChannelID,
			&taskNumber,
			&taskTitle,
			&taskStatus,
			&taskRole,
			&lastActivityAt,
			&summary.MoreCount,
		); err != nil {
			return nil, fmt.Errorf("scan channel agent task summary: %w", err)
		}
		summary.UpdatedAt = updatedAt
		if summary.State == message.ChannelAgentTaskSummaryStateWorkingInChannel && strings.TrimSpace(taskID) != "" {
			task := &message.ChannelAgentCurrentTaskSummary{
				TaskID:     strings.TrimSpace(taskID),
				ChannelID:  strings.TrimSpace(taskChannelID),
				TaskNumber: taskNumber,
				Title:      strings.TrimSpace(taskTitle),
				Status:     strings.TrimSpace(taskStatus),
				Role:       strings.TrimSpace(taskRole),
			}
			if task.ChannelID != "" && task.TaskNumber > 0 {
				task.Route = fmt.Sprintf("/channels/%s/tasks/%d", task.ChannelID, task.TaskNumber)
			}
			if lastActivityAt.Valid {
				task.LastActivityAt = &lastActivityAt.Time
			}
			summary.CurrentTask = task
		} else {
			summary.MoreCount = 0
		}
		summaries = append(summaries, summary)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate channel agent task summaries: %w", err)
	}
	return summaries, nil
}