package taskboard

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

	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

func queueCollaboratorGrantAddedNotification(ctx context.Context, queryer queryExecer, collaboratorID string, now time.Time) error {
	result, err := queryer.Exec(ctx, `
UPDATE task_scope_participant_metadata
SET add_notification_state = 'queued',
    add_notification_queued_at = $2,
    updated_at = $2
WHERE id = $1
  AND add_notification_state = 'none'
`, collaboratorID, now)
	if err != nil {
		return fmt.Errorf("queue collaborator grant notification: %w", err)
	}
	if result.RowsAffected() != 1 {
		return fmt.Errorf("queue collaborator grant notification: collaborator grant %s was not ready for notification", collaboratorID)
	}
	return nil
}

func lockActiveTaskCollaboratorID(ctx context.Context, queryer rowQueryer, executionID string, memberID string, now time.Time) (string, error) {
	row := queryer.QueryRow(ctx, `
SELECT id
FROM task_scope_participant_metadata
WHERE owner_scope_execution_id = $1
  AND member_id = $2
  AND removed_at IS NULL
  AND left_at IS NULL
  AND (expires_at IS NULL OR expires_at > $3)
ORDER BY added_at DESC, id DESC
LIMIT 1
FOR UPDATE
`, executionID, memberID, now)
	var collaboratorID string
	if err := row.Scan(&collaboratorID); err != nil {
		if coredb.IsNotFound(err) {
			return "", nil
		}
		return "", fmt.Errorf("lock task collaborator: %w", err)
	}
	return collaboratorID, nil
}

func (txs TxStore) loadTaskCollaboratorsForScope(ctx context.Context, queryer coredb.QueryExecer, target taskCollaboratorTarget, includeInactive bool, now time.Time) ([]TaskCollaborator, error) {
	execution, err := txs.loadTaskScopeExecution(ctx, queryer, target)
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return []TaskCollaborator{}, nil
		}
		return nil, err
	}
	if !includeInactive {
		if err := cleanupExpiredTaskScopeParticipants(ctx, queryer, []taskScopeExecution{execution}, now); err != nil {
			return nil, err
		}
	}
	query := taskCollaboratorSelectQuery() + `
WHERE tc.owner_scope_execution_id = $1
	`
	args := []any{execution.ID}
	if !includeInactive {
		query += "  AND tc.removed_at IS NULL\n  AND tc.left_at IS NULL\n  AND (tc.expires_at IS NULL OR tc.expires_at > $" + fmt.Sprint(len(args)+1) + ")\n" + activeTaskCollaboratorMemberAvailabilitySQL()
		args = append(args, now)
	}
	query += "ORDER BY tc.added_at ASC, tc.id ASC\n"
	collaborators, err := scanTaskCollaboratorRows(ctx, queryer, query, now, args...)
	if err != nil {
		return nil, err
	}
	return collaborators, nil
}

func loadTaskCollaboratorByID(ctx context.Context, queryer coredb.QueryExecer, collaboratorID string, now time.Time) (TaskCollaborator, error) {
	query := taskCollaboratorSelectQuery() + `
WHERE tc.id = $1
`
	rows, err := scanTaskCollaboratorRows(ctx, queryer, query, now, collaboratorID)
	if err != nil {
		return TaskCollaborator{}, err
	}
	if len(rows) == 0 {
		return TaskCollaborator{}, ErrNotFound
	}
	return rows[0], nil
}

func activeTaskCollaboratorMemberAvailabilitySQL() string {
	return `  AND (
    collaborator_member.type <> 'service'
    OR EXISTS (
        SELECT 1
        FROM service_members active_collaborator_member
        JOIN service_installations active_collaborator_app
          ON active_collaborator_app.id = active_collaborator_member.service_installation_id
        WHERE active_collaborator_member.workspace_id = tse.workspace_id
          AND active_collaborator_member.member_id = tc.member_id
          AND active_collaborator_app.status = 'active'
    )
  )
`
}

func taskCollaboratorSelectQuery() string {
	return `
SELECT
    tc.id,
    tse.workspace_id,
    tse.scope_type,
    tse.scope_id,
    tse.owner_member_id,
    tc.member_id,
    tc.role,
    tc.added_by_member_id,
    tc.added_at,
    tc.add_notification_state,
    tc.add_notification_queued_at,
    tc.notified_at,
    tc.expires_at,
    tc.left_at,
    tc.removed_at,
    tc.removed_by_member_id,
` + taskActorProjectionSelectSQL("collaborator_member", "collaborator_user", "collaborator_agent", "collaborator_service_member", "collaborator_service_definition") + `,
` + taskActorProjectionSelectSQL("added_member", "added_user", "added_agent", "added_service_member", "added_service_definition") + `,
` + taskActorProjectionSelectSQL("removed_member", "removed_user", "removed_agent", "removed_service_member", "removed_service_definition") + `,
    CASE
      WHEN collaborator_member.type <> 'service' THEN TRUE
      WHEN EXISTS (
          SELECT 1
          FROM service_members collaborator_service_member
          JOIN service_installations collaborator_app
            ON collaborator_app.id = collaborator_service_member.service_installation_id
          WHERE collaborator_service_member.workspace_id = tse.workspace_id
            AND collaborator_service_member.member_id = collaborator_member.id
            AND collaborator_app.status = 'active'
      ) THEN TRUE
      ELSE FALSE
    END AS collaborator_available
FROM task_scope_participant_metadata tc
JOIN task_scope_executions tse
  ON tse.id = tc.owner_scope_execution_id
JOIN members collaborator_member
  ON collaborator_member.id = tc.member_id
` + taskActorProjectionJoinsSQL("collaborator_member", "collaborator_user", "collaborator_agent", "collaborator_service_member", "collaborator_service_definition") + `
JOIN members added_member
  ON added_member.id = tc.added_by_member_id
` + taskActorProjectionJoinsSQL("added_member", "added_user", "added_agent", "added_service_member", "added_service_definition") + `
LEFT JOIN members removed_member
  ON removed_member.id = tc.removed_by_member_id
` + taskActorProjectionJoinsSQL("removed_member", "removed_user", "removed_agent", "removed_service_member", "removed_service_definition") + `
`
}

func scanTaskCollaboratorRows(ctx context.Context, queryer coredb.Queryer, query string, now time.Time, args ...any) ([]TaskCollaborator, error) {
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("query task collaborators: %w", err)
	}
	defer rows.Close()

	collaborators := make([]TaskCollaborator, 0)
	for rows.Next() {
		var row TaskCollaborator
		var workspaceID string
		var removedByMemberID sql.NullString
		var collaborator taskActorProjection
		var collaboratorAvailable bool
		var notificationState string
		var notificationQueuedAt *time.Time
		var addedByProjection taskActorProjection
		var removedByProjection taskActorProjection
		scanDest := []any{
			&row.ID,
			&workspaceID,
			&row.ScopeType,
			&row.ScopeID,
			&row.ExecutionOwnerMemberID,
			&row.MemberID,
			&row.Role,
			&row.AddedByMemberID,
			&row.AddedAt,
			&notificationState,
			&notificationQueuedAt,
			&row.NotifiedAt,
			&row.ExpiresAt,
			&row.LeftAt,
			&row.RemovedAt,
			&removedByMemberID,
		}
		scanDest = appendTaskActorProjectionDestinations(scanDest, &collaborator)
		scanDest = appendTaskActorProjectionDestinations(scanDest, &addedByProjection)
		scanDest = appendTaskActorProjectionDestinations(scanDest, &removedByProjection)
		scanDest = append(scanDest,
			&collaboratorAvailable,
		)
		if err := rows.Scan(scanDest...); err != nil {
			return nil, fmt.Errorf("scan task collaborator: %w", err)
		}
		_ = workspaceID
		row.RemovedByMemberID = trimTaskNullString(removedByMemberID)
		row.NotificationQueuedAt = notificationQueuedAt
		member, err := collaborator.actor()
		if err != nil {
			return nil, err
		}
		memberProfile, err := taskPrincipalProfileFromProjection("task collaborator member", collaborator, true)
		if err != nil {
			return nil, err
		}
		row.MemberProfile = memberProfile
		if derived := taskActorCompatFromPrincipalProfile(memberProfile, member); derived != nil {
			row.Member = *derived
		}
		addedBy, err := addedByProjection.actor()
		if err != nil {
			return nil, err
		}
		addedByProfile, err := taskPrincipalProfileFromProjection("task collaborator addedBy", addedByProjection, true)
		if err != nil {
			return nil, err
		}
		row.AddedByProfile = addedByProfile
		if derived := taskActorCompatFromPrincipalProfile(addedByProfile, addedBy); derived != nil {
			row.AddedBy = *derived
		}
		removedBy, err := removedByProjection.actor()
		if err != nil {
			return nil, err
		}
		removedByProfile, err := taskPrincipalProfileFromProjection("task collaborator removedBy", removedByProjection, false)
		if err != nil {
			return nil, err
		}
		row.RemovedByProfile = removedByProfile
		row.RemovedBy = taskActorCompatFromPrincipalProfile(removedByProfile, removedBy)
		row.Active = collaboratorAvailable && row.RemovedAt == nil && row.LeftAt == nil && (row.ExpiresAt == nil || row.ExpiresAt.After(now))
		row.NotificationState = taskCollaboratorNotificationStatus(notificationState, row.NotifiedAt)
		collaborators = append(collaborators, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate task collaborators: %w", err)
	}
	return collaborators, nil
}

func taskCollaboratorNotificationStatus(deliveryState string, notifiedAt *time.Time) string {
	if notifiedAt != nil {
		return taskCollaboratorNotificationStatusSent
	}
	switch strings.TrimSpace(deliveryState) {
	case "queued":
		return taskCollaboratorNotificationStatusPending
	case "delivered":
		return taskCollaboratorNotificationStatusSent
	case "failed":
		return taskCollaboratorNotificationStatusFailed
	default:
		return taskCollaboratorNotificationStatusNone
	}
}

func taskEffectiveCollaboratorTargets(anchor taskCollaboratorTaskAnchor) []taskCollaboratorTarget {
	targets := []taskCollaboratorTarget{{
		WorkspaceID:            anchor.WorkspaceID,
		ScopeType:              taskCollaboratorScopeTask,
		ScopeID:                anchor.TaskID,
		ExecutionOwnerMemberID: anchor.ExecutionOwnerMemberID,
		TaskID:                 anchor.TaskID,
		TaskScopeID:            anchor.TaskScopeID,
		ChannelID:              anchor.ChannelID,
		ThreadChannelID:        anchor.SourceThreadChannelID,
	}}
	if strings.TrimSpace(anchor.SourceThreadChannelID) != "" {
		targets = append(targets, taskCollaboratorTarget{
			WorkspaceID:            anchor.WorkspaceID,
			ScopeType:              taskCollaboratorScopeThread,
			ScopeID:                anchor.SourceThreadChannelID,
			ExecutionOwnerMemberID: anchor.ExecutionOwnerMemberID,
			ChannelID:              anchor.ChannelID,
			ThreadChannelID:        anchor.SourceThreadChannelID,
		})
	}
	targets = append(targets, taskCollaboratorTarget{
		WorkspaceID:            anchor.WorkspaceID,
		ScopeType:              taskCollaboratorScopeChannel,
		ScopeID:                anchor.ChannelID,
		ExecutionOwnerMemberID: anchor.ExecutionOwnerMemberID,
		ChannelID:              anchor.ChannelID,
	})
	return targets
}

func (txs TxStore) loadActiveTaskCollaboratorsForTargets(ctx context.Context, queryer coredb.QueryExecer, targets []taskCollaboratorTarget, now time.Time, cleanupExpired bool) ([]TaskCollaborator, error) {
	executions := make([]taskScopeExecution, 0, len(targets))
	for _, target := range targets {
		if strings.TrimSpace(target.WorkspaceID) == "" || strings.TrimSpace(target.ScopeType) == "" || strings.TrimSpace(target.ScopeID) == "" || strings.TrimSpace(target.ExecutionOwnerMemberID) == "" {
			continue
		}
		execution, err := txs.loadTaskScopeExecution(ctx, queryer, target)
		if err != nil {
			if errors.Is(err, ErrNotFound) {
				continue
			}
			return nil, err
		}
		executions = append(executions, execution)
	}
	if len(executions) == 0 {
		return []TaskCollaborator{}, nil
	}
	if cleanupExpired {
		if err := cleanupExpiredTaskScopeParticipants(ctx, queryer, executions, now); err != nil {
			return nil, err
		}
	}

	args := make([]any, 0, len(executions)+1)
	clauses := make([]string, 0, len(executions))
	for _, execution := range executions {
		clauses = append(clauses, fmt.Sprintf("tc.owner_scope_execution_id = $%d", len(args)+1))
		args = append(args, execution.ID)
	}
	nowArg := len(args) + 1
	args = append(args, now)

	query := taskCollaboratorSelectQuery() + `
WHERE (` + strings.Join(clauses, " OR ") + fmt.Sprintf(`)
  AND tc.removed_at IS NULL
  AND tc.left_at IS NULL
  AND (tc.expires_at IS NULL OR tc.expires_at > $%d)
%sORDER BY tc.added_at ASC, tc.id ASC
`, nowArg, activeTaskCollaboratorMemberAvailabilitySQL())
	return scanTaskCollaboratorRows(ctx, queryer, query, now, args...)
}

func taskCollaboratorTargetsByScope(targets []taskCollaboratorTarget) map[string]taskCollaboratorTarget {
	byScope := make(map[string]taskCollaboratorTarget, len(targets))
	for _, target := range targets {
		byScope[taskCollaboratorScopeKey(target.ScopeType, target.ScopeID)] = target
	}
	return byScope
}

func taskCollaboratorScopeKey(scopeType string, scopeID string) string {
	return strings.TrimSpace(scopeType) + "\x00" + strings.TrimSpace(scopeID)
}

func loadTaskboardWorkspaceMemberIDForUser(ctx context.Context, queryer rowQueryer, workspaceID string, userID string) (string, error) {
	row := queryer.QueryRow(ctx, `
SELECT id
FROM members
WHERE workspace_id = $1
  AND user_id = $2
  AND type = 'human'
  AND status = 'active'
`, workspaceID, userID)
	var memberID string
	if err := row.Scan(&memberID); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrForbidden
		}
		return "", fmt.Errorf("load taskboard workspace member: %w", err)
	}
	return memberID, nil
}

func collaboratorScopeRank(scopeType string) int {
	switch scopeType {
	case taskCollaboratorScopeTask:
		return 0
	case taskCollaboratorScopeThread:
		return 1
	case taskCollaboratorScopeChannel:
		return 2
	default:
		return 3
	}
}

func loadTaskPrincipalProfileByMemberID(ctx context.Context, queryer rowQueryer, workspaceID string, memberID string) (*TaskPrincipalProfile, error) {
	row := queryer.QueryRow(ctx, `
SELECT
`+taskActorProjectionSelectSQL("m", "u", "a", "sm", "sd")+`
FROM members m
`+taskActorProjectionJoinsSQL("m", "u", "a", "sm", "sd")+`
WHERE m.workspace_id = $1 AND m.id = $2 AND m.status = 'active'
	`, workspaceID, memberID)
	var actor taskActorProjection
	if err := row.Scan(appendTaskActorProjectionDestinations(nil, &actor)...); err != nil {
		if coredb.IsNotFound(err) {
			return nil, ErrNotFound
		}
		return nil, fmt.Errorf("load task principal profile by member: %w", err)
	}
	return taskPrincipalProfileFromProjection("task member", actor, true)
}

func loadActiveTaskCollaboratorProfile(ctx context.Context, queryer rowQueryer, workspaceID string, memberID string) (*TaskPrincipalProfile, error) {
	row := queryer.QueryRow(ctx, `
SELECT
`+taskActorProjectionSelectSQL("m", "u", "a", "sm", "sd")+`
FROM members m
`+taskActorProjectionJoinsSQL("m", "u", "a", "sm", "sd")+`
WHERE m.workspace_id = $1
  AND m.id = $2
  AND m.status = 'active'
  AND (
      m.type = 'human'
      OR (
          m.type = 'service'
          AND EXISTS (
              SELECT 1
              FROM service_members active_collaborator_member
              JOIN service_installations active_collaborator_app
                ON active_collaborator_app.id = active_collaborator_member.service_installation_id
              WHERE active_collaborator_member.workspace_id = m.workspace_id
                AND active_collaborator_member.member_id = m.id
                AND active_collaborator_app.status = 'active'
          )
      )
  )
	`, workspaceID, memberID)
	var actor taskActorProjection
	if err := row.Scan(appendTaskActorProjectionDestinations(nil, &actor)...); err != nil {
		if coredb.IsNotFound(err) {
			return nil, ErrNotFound
		}
		return nil, fmt.Errorf("load active task collaborator principal profile: %w", err)
	}
	return taskPrincipalProfileFromProjection("active task collaborator", actor, true)
}

type taskCollaboratorScopeReadabilityRequest struct {
	RelationID    string
	ScopeType     string
	ScopeID       string
	MemberID      string
	TaskChannelID string
}

func taskCollaboratorScopeReadabilityRequests(rows []TaskCollaborator, targetsByScope map[string]taskCollaboratorTarget) []taskCollaboratorScopeReadabilityRequest {
	if len(rows) == 0 {
		return nil
	}
	requests := make([]taskCollaboratorScopeReadabilityRequest, 0, len(rows))
	for _, row := range rows {
		target, ok := targetsByScope[taskCollaboratorScopeKey(row.ScopeType, row.ScopeID)]
		if !ok {
			continue
		}
		request := taskCollaboratorScopeReadabilityRequest{
			RelationID:    strings.TrimSpace(row.ID),
			ScopeType:     strings.TrimSpace(row.ScopeType),
			ScopeID:       strings.TrimSpace(row.ScopeID),
			MemberID:      strings.TrimSpace(row.MemberID),
			TaskChannelID: strings.TrimSpace(target.ChannelID),
		}
		if request.RelationID == "" || request.ScopeType == "" || request.ScopeID == "" || request.MemberID == "" {
			continue
		}
		requests = append(requests, request)
	}
	return requests
}

func loadReadableTaskCollaboratorScopeRows(ctx context.Context, queryer coredb.Queryer, workspaceID string, requests []taskCollaboratorScopeReadabilityRequest) (map[string]struct{}, error) {
	normalizedRequests := normalizeTaskCollaboratorScopeReadabilityRequests(requests)
	readable := make(map[string]struct{}, len(normalizedRequests))
	if len(normalizedRequests) == 0 {
		return readable, nil
	}
	requestsCTE, requestArgs, err := buildTaskCollaboratorScopeReadabilityRequestsCTE("requested_readability", normalizedRequests, 2)
	if err != nil {
		return nil, err
	}
	query := fmt.Sprintf(`
WITH %s
SELECT DISTINCT requested_readability.relation_id
FROM requested_readability
LEFT JOIN scopes source_scope
  ON source_scope.workspace_id = $1
 AND source_scope.id = requested_readability.scope_id
 AND source_scope.status = 'active'
LEFT JOIN members collaborator_member
  ON collaborator_member.workspace_id = $1
 AND collaborator_member.id = requested_readability.member_id
 AND collaborator_member.status = 'active'
LEFT JOIN service_members collaborator_service_member
  ON collaborator_member.type = 'service'
 AND collaborator_service_member.workspace_id = $1
 AND collaborator_service_member.member_id = collaborator_member.id
LEFT JOIN service_installations collaborator_app
  ON collaborator_app.id = collaborator_service_member.service_installation_id
 AND collaborator_app.status = 'active'
LEFT JOIN scope_memberships sm
  ON sm.member_id = requested_readability.member_id
 AND sm.visibility = 'public'
 AND sm.removed_at IS NULL
 AND (
      (requested_readability.scope_type = 'task' AND sm.scope_id = requested_readability.task_channel_id)
      OR (requested_readability.scope_type = 'channel' AND source_scope.type = 'channel' AND sm.scope_id = source_scope.id)
      OR (requested_readability.scope_type = 'thread' AND source_scope.type = 'thread' AND (sm.scope_id = source_scope.id OR sm.scope_id = source_scope.parent_scope_id))
 )
WHERE (requested_readability.scope_type = 'task' OR source_scope.id IS NOT NULL)
  AND (sm.member_id IS NOT NULL OR collaborator_app.id IS NOT NULL)
`, requestsCTE)
	args := append([]any{strings.TrimSpace(workspaceID)}, requestArgs...)
	if err := scanTaskCollaboratorReadableRelationIDs(ctx, queryer, query, args, readable, "scope"); err != nil {
		return nil, err
	}
	return readable, nil
}

func normalizeTaskCollaboratorScopeReadabilityRequests(requests []taskCollaboratorScopeReadabilityRequest) []taskCollaboratorScopeReadabilityRequest {
	if len(requests) == 0 {
		return nil
	}
	normalized := make([]taskCollaboratorScopeReadabilityRequest, 0, len(requests))
	seen := make(map[string]struct{}, len(requests))
	for _, request := range requests {
		candidate := taskCollaboratorScopeReadabilityRequest{
			RelationID:    strings.TrimSpace(request.RelationID),
			ScopeType:     strings.TrimSpace(request.ScopeType),
			ScopeID:       strings.TrimSpace(request.ScopeID),
			MemberID:      strings.TrimSpace(request.MemberID),
			TaskChannelID: strings.TrimSpace(request.TaskChannelID),
		}
		if candidate.RelationID == "" || candidate.ScopeType == "" || candidate.ScopeID == "" || candidate.MemberID == "" {
			continue
		}
		key := strings.Join([]string{candidate.RelationID, candidate.ScopeType, candidate.ScopeID, candidate.MemberID, candidate.TaskChannelID}, "\x00")
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		normalized = append(normalized, candidate)
	}
	return normalized
}

func scanTaskCollaboratorReadableRelationIDs(ctx context.Context, queryer coredb.Queryer, query string, args []any, readable map[string]struct{}, label string) error {
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("load %s task collaborator readable relation ids: %w", label, err)
	}
	defer rows.Close()

	for rows.Next() {
		var relationID string
		if err := rows.Scan(&relationID); err != nil {
			return fmt.Errorf("scan %s task collaborator readable relation id: %w", label, err)
		}
		if trimmed := strings.TrimSpace(relationID); trimmed != "" {
			readable[trimmed] = struct{}{}
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate %s task collaborator readable relation ids: %w", label, err)
	}
	return nil
}

func buildTaskCollaboratorScopeReadabilityRequestsCTE(name string, requests []taskCollaboratorScopeReadabilityRequest, startParam int) (string, []any, error) {
	trimmedName := strings.TrimSpace(name)
	if trimmedName == "" {
		return "", nil, fmt.Errorf("cte name is required")
	}
	if len(requests) == 0 {
		return "", nil, fmt.Errorf("task collaborator scope readability requests are empty")
	}
	var builder strings.Builder
	builder.WriteString(trimmedName)
	builder.WriteString(" AS (\n")
	args := make([]any, 0, len(requests)*5)
	param := startParam
	for index, request := range requests {
		if index > 0 {
			builder.WriteString("\n    UNION ALL\n")
		}
		builder.WriteString(fmt.Sprintf("    SELECT $%d AS relation_id, $%d AS scope_type, $%d AS scope_id, $%d AS member_id, $%d AS task_channel_id", param, param+1, param+2, param+3, param+4))
		args = append(args, request.RelationID, request.ScopeType, request.ScopeID, request.MemberID, request.TaskChannelID)
		param += 5
	}
	builder.WriteString("\n)")
	return builder.String(), args, nil
}