package unread

import (
	"fmt"
	"strings"

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

const snapshotSelectColumns = `
SELECT
  snap.workspace_id,
  snap.member_id,
  snap.scope_id,
  snap.message_unread_count,
  snap.activity_unread_count,
  snap.manual_unread,
  snap.last_message_read_seq,
  snap.last_activity_read_seq,
  snap.effective_from_seq,
  snap.latest_message_accounted_seq,
  snap.latest_activity_accounted_seq,
  snap.notification_state,
  snap.membership_visibility,
  snap.scope_message_generation,
  snap.scope_activity_generation,
  snap.membership_generation,
  snap.notification_generation,
  snap.manual_unread_generation,
  snap.rewind_generation,
  snap.updated_at,
  snap.task_snapshot_format_version,
  snap.task_snapshot_json`

func buildSnapshotPageQuery(request SnapshotPageRequest) (string, []any, error) {
	request.ScopeIDs = compactStrings(request.ScopeIDs)

	if len(request.ScopeIDs) > 0 {
		requestedScopesCTE, scopeArgs, _, err := coredb.BuildStringValuesCTE("requested_scopes", "scope_id", request.ScopeIDs, 4)
		if err != nil {
			return "", nil, err
		}
		query := fmt.Sprintf(`
WITH %s
%s
FROM requested_scopes rs
JOIN member_scope_unread_runtime_snapshots snap
  ON snap.workspace_id = $1
 AND snap.member_id = $2
 AND snap.scope_id = rs.scope_id
JOIN scopes s
  ON s.workspace_id = snap.workspace_id
 AND s.id = snap.scope_id
 AND s.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
ORDER BY snap.scope_id
LIMIT $3
`, requestedScopesCTE, snapshotSelectColumns)
		args := []any{request.WorkspaceID, request.MemberID, request.Limit}
		args = append(args, scopeArgs...)
		return query, args, nil
	}

	return snapshotSelectColumns + `
FROM member_scope_unread_runtime_snapshots snap
JOIN scopes s
  ON s.workspace_id = snap.workspace_id
 AND s.id = snap.scope_id
 AND s.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
WHERE snap.workspace_id = $1
  AND snap.member_id = $2
  AND snap.scope_id > $3
ORDER BY snap.scope_id
LIMIT $4
`, []any{request.WorkspaceID, request.MemberID, request.AfterScopeID, request.Limit}, nil
}

func buildMetadataPageQuery(request MetadataPageRequest) (string, []any, error) {
	request.ScopeIDs = compactStrings(request.ScopeIDs)
	const selectColumns = `
SELECT
  s.workspace_id,
  sm.member_id,
  s.id AS scope_id,
  s.type AS scope_type,
  s.latest_seq,
  s.latest_activity_seq,
  s.unread_rewind_generation,
  sm.last_read_seq,
  sm.last_activity_global_seq,
  sm.effective_from_seq,
  sm.unread_generation,
  sns.state AS notification_state,
  COALESCE(sns.unread_generation, 0) AS notification_generation,
  sm.visibility,
  cms.manual_unread_at IS NOT NULL AS manual_unread,
  COALESCE(cms.unread_generation, 0) AS manual_unread_generation`
	const joins = `
JOIN scope_memberships sm
  ON sm.scope_id = s.id
 AND sm.member_id = $2
 AND sm.removed_at IS NULL
 AND sm.archived_at IS NULL
 AND sm.effective_to_seq IS NULL
LEFT JOIN scope_notification_settings sns
  ON sns.scope_id = s.id
 AND sns.member_id = $2
LEFT JOIN conversation_member_states cms
  ON cms.scope_id = s.id
 AND cms.member_id = $2`

	if len(request.ScopeIDs) > 0 {
		requestedScopesCTE, scopeArgs, _, err := coredb.BuildStringValuesCTE("requested_scopes", "scope_id", request.ScopeIDs, 4)
		if err != nil {
			return "", nil, err
		}
		query := fmt.Sprintf(`
WITH %s
%s
FROM requested_scopes rs
JOIN scopes s
  ON s.workspace_id = $1
 AND s.id = rs.scope_id
 AND s.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
%s
ORDER BY s.id
LIMIT $3
`, requestedScopesCTE, selectColumns, joins)
		args := []any{request.WorkspaceID, request.MemberID, request.Limit}
		args = append(args, scopeArgs...)
		return query, args, nil
	}

	return selectColumns + `
FROM scopes s
` + joins + `
WHERE s.workspace_id = $1
  AND s.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
  AND s.id > $3
ORDER BY s.id
LIMIT $4
`, []any{request.WorkspaceID, request.MemberID, request.AfterScopeID, request.Limit}, nil
}

func buildActivityTailPageQuery() string {
	return `
SELECT
  a.workspace_id,
  a.scope_id,
  s.type,
  a.seq,
  COALESCE(a.actor_member_id, '')
FROM scope_activity_events a
JOIN scopes s
  ON s.id = a.scope_id
 AND s.workspace_id = a.workspace_id
WHERE a.workspace_id = $1
  AND a.scope_id = $2
  AND a.seq > $3
  AND a.suppressed_at IS NULL
  AND s.type IN ('channel', 'dm', 'thread', 'service_account')
  AND a.activity_ref_type <> 'reaction'
  AND a.activity_type <> 'reaction_added'
ORDER BY a.seq
LIMIT $4
`
}

func compactStrings(values []string) []string {
	if len(values) == 0 {
		return nil
	}
	seen := make(map[string]struct{}, len(values))
	compacted := 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{}{}
		compacted = append(compacted, value)
	}
	return compacted
}