package tasktimeline

import (
	"strconv"
	"strings"
	"time"
)

const (
	timelineSourceKindActivity = "activity"
	timelineSourceKindMessage  = "message"
	discussionAddedEvent       = "discussion_added"
)

// ReadCursor is the store-owned keyset boundary for the unified Task timeline.
// Seq is absent on legacy browser tokens.
type ReadCursor struct {
	Seq       int64
	CreatedAt time.Time
	ID        string
}

func (cursor ReadCursor) IsZero() bool {
	return cursor == (ReadCursor{})
}

// BuildReadPageQuery keeps the SQL projection owned beside Task timeline
// writes while allowing taskboard to hydrate the resulting domain rows.
func BuildReadPageQuery(taskID string, cursor ReadCursor, limit int, workspaceID string) (string, []any) {
	if cursor.IsZero() || cursor.Seq > 0 {
		return buildBoundedReadPageQuery(taskID, cursor, limit, workspaceID)
	}
	return buildLegacyReadPageQuery(taskID, cursor, limit, workspaceID)
}

func taskTargetSQL(taskID string, workspaceID string) (string, []any) {
	if strings.TrimSpace(workspaceID) == "" {
		return `
WITH task_target AS (
  SELECT id, task_scope_id
  FROM tasks
  WHERE id = $1
),
`, []any{taskID}
	}
	return `
WITH task_target AS (
  SELECT t.id, t.task_scope_id
  FROM tasks t
  JOIN task_boards task_board
    ON task_board.id = t.board_id
   AND task_board.workspace_id = $1
  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 = task_board.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
  WHERE t.id = $2
    AND t.channel_id = task_board.channel_id
),
`, []any{strings.TrimSpace(workspaceID), taskID}
}

func buildBoundedReadPageQuery(taskID string, cursor ReadCursor, limit int, workspaceID string) (string, []any) {
	taskTarget, args := taskTargetSQL(taskID, workspaceID)
	messageCursorPredicate := ""
	activityCursorPredicate := ""
	if cursor.Seq > 0 {
		seqPosition := len(args) + 1
		idPosition := len(args) + 2
		messageCursorPredicate = `
   AND (sm.seq < $` + strconv.Itoa(seqPosition) + ` OR (sm.seq = $` + strconv.Itoa(seqPosition) + ` AND sm.id < $` + strconv.Itoa(idPosition) + `))`
		activityCursorPredicate = `
   AND (ae.seq < $` + strconv.Itoa(seqPosition) + ` OR (ae.seq = $` + strconv.Itoa(seqPosition) + ` AND ae.id < $` + strconv.Itoa(idPosition) + `))`
		args = append(args, cursor.Seq, cursor.ID)
	}
	query := taskTarget + `message_candidates AS (
  SELECT sm.*
  FROM task_target t
  JOIN scope_messages sm
    ON sm.scope_id = t.task_scope_id
   AND sm.message_type = 'task_discussion'` + messageCursorPredicate + `
  ORDER BY sm.seq DESC, sm.id DESC
  LIMIT ` + strconv.Itoa(limit) + `
),
activity_candidates AS (
  SELECT ae.*
  FROM task_target t
  JOIN scope_activity_events ae
    ON ae.scope_id = t.task_scope_id
   AND ae.activity_ref_type = 'task_event'
   AND ae.suppressed_at IS NULL` + activityCursorPredicate + `
  ORDER BY ae.seq DESC, ae.id DESC
  LIMIT ` + strconv.Itoa(limit) + `
),
timeline AS (` + boundedActivitySourcesSQL() + `),
page_candidates AS (
  SELECT timeline.*
  FROM timeline
  ORDER BY timeline.seq DESC, timeline.id DESC
  LIMIT ` + strconv.Itoa(limit) + `
)
` + timelineProjectionSQL("page_candidates timeline") + `
ORDER BY timeline.seq DESC, timeline.id DESC
`
	return query, args
}

func buildLegacyReadPageQuery(taskID string, cursor ReadCursor, limit int, workspaceID string) (string, []any) {
	taskTarget, args := taskTargetSQL(taskID, workspaceID)
	query := taskTarget + `timeline AS (` + legacyActivitySourcesSQL() + `)
` + timelineProjectionSQL("timeline")
	if !cursor.IsZero() {
		idPosition := len(args) + 1
		createdAtPosition := len(args) + 2
		query += `
LEFT JOIN timeline cursor_timeline
  ON cursor_timeline.id = $` + strconv.Itoa(idPosition) + `
WHERE ((cursor_timeline.id IS NOT NULL AND timeline.seq < cursor_timeline.seq)
   OR (cursor_timeline.id IS NULL AND (timeline.created_at < $` + strconv.Itoa(createdAtPosition) + ` OR (timeline.created_at = $` + strconv.Itoa(createdAtPosition) + ` AND timeline.id < $` + strconv.Itoa(idPosition) + `))))
`
		args = append(args, cursor.ID, cursor.CreatedAt)
	}
	query += "ORDER BY timeline.seq DESC, timeline.id DESC\n"
	query += "LIMIT " + strconv.Itoa(limit) + "\n"
	return query, args
}

func timelineProjectionSQL(source string) string {
	return `SELECT
    timeline.id,
    timeline.seq,
    timeline.source_kind,
    timeline.source_message_id,
    timeline.source_activity_id,
    timeline.actor_ref,
    actor_member.type,
    actor_member.id,
    actor_user.id,
    actor_user.display_name,
    actor_user.avatar_url,
    actor_member.display_name,
    actor_user.name,
    actor_agent.id,
    actor_agent.name,
    actor_agent.avatar_url,
    actor_service_member.service_installation_id,
    actor_service_member.service_kind,
    actor_service_member.handle,
    actor_service_member.display_name,
    actor_service_member.avatar_url,
    actor_service_definition.id,
    actor_service_definition.slug,
    actor_service_definition.name,
    actor_service_definition.avatar_url,
    timeline.event_type,
    timeline.event_payload,
    timeline.created_at
FROM ` + source + `
LEFT JOIN members actor_member
  ON actor_member.id = NULLIF(timeline.actor_ref, '')
LEFT JOIN users actor_user
  ON actor_member.type = 'human'
 AND actor_user.id = actor_member.user_id
LEFT JOIN agents actor_agent
  ON actor_member.type = 'agent'
 AND actor_agent.member_id = actor_member.id
LEFT JOIN service_members actor_service_member
  ON actor_member.type = 'service'
 AND actor_service_member.workspace_id = actor_member.workspace_id
 AND actor_service_member.member_id = actor_member.id
LEFT JOIN service_definitions actor_service_definition
  ON actor_service_definition.id = actor_service_member.service_definition_id
 AND actor_service_definition.service_kind = actor_service_member.service_kind`
}

func legacyActivitySourcesSQL() string {
	return `
SELECT
    COALESCE(message_activity.id, sm.id) AS id,
    COALESCE(message_activity.seq, sm.seq) AS seq,
    '` + timelineSourceKindMessage + `' AS source_kind,
    sm.id AS source_message_id,
    COALESCE(message_activity.id, '') AS source_activity_id,
    COALESCE(sm.author_member_id, '') AS actor_ref,
    '` + discussionAddedEvent + `' AS event_type,
    sm.id AS event_payload,
    sm.created_at AS created_at
FROM task_target t
JOIN scope_messages sm
  ON sm.scope_id = t.task_scope_id
 AND sm.message_type = 'task_discussion'
LEFT JOIN scope_activity_events message_activity
  ON message_activity.scope_id = sm.scope_id
 AND message_activity.activity_ref_type = 'task_event'
 AND message_activity.suppressed_at IS NULL
 AND message_activity.activity_type = 'task.` + discussionAddedEvent + `'
 AND (message_activity.source_message_id = sm.id OR ` + activityEventPayloadSQL("message_activity") + ` = sm.id)
UNION ALL
` + typedActivitySourceSQL("task_target t\n  JOIN scope_activity_events ae\n    ON ae.scope_id = t.task_scope_id\n   AND ae.activity_ref_type = 'task_event'\n   AND ae.suppressed_at IS NULL", "t.task_scope_id")
}

func boundedActivitySourcesSQL() string {
	return `
SELECT
    discussion_activity.id AS id,
    discussion_activity.seq AS seq,
    '` + timelineSourceKindMessage + `' AS source_kind,
    sm.id AS source_message_id,
    discussion_activity.id AS source_activity_id,
    COALESCE(sm.author_member_id, '') AS actor_ref,
    '` + discussionAddedEvent + `' AS event_type,
    sm.id AS event_payload,
    sm.created_at AS created_at
FROM activity_candidates discussion_activity
JOIN scope_messages sm
  ON sm.scope_id = discussion_activity.scope_id
 AND sm.id = discussion_activity.source_message_id
 AND sm.message_type = 'task_discussion'
WHERE discussion_activity.activity_type = 'task.` + discussionAddedEvent + `'
UNION ALL
SELECT
    sm.id AS id,
    sm.seq AS seq,
    '` + timelineSourceKindMessage + `' AS source_kind,
    sm.id AS source_message_id,
    '' AS source_activity_id,
    COALESCE(sm.author_member_id, '') AS actor_ref,
    '` + discussionAddedEvent + `' AS event_type,
    sm.id AS event_payload,
    sm.created_at AS created_at
FROM message_candidates sm
WHERE NOT EXISTS (
  SELECT 1
  FROM scope_activity_events canonical_discussion_activity
  WHERE canonical_discussion_activity.scope_id = sm.scope_id
    AND canonical_discussion_activity.source_message_id = sm.id
    AND canonical_discussion_activity.activity_ref_type = 'task_event'
    AND canonical_discussion_activity.activity_type = 'task.` + discussionAddedEvent + `'
    AND canonical_discussion_activity.suppressed_at IS NULL
  LIMIT 1
)
UNION ALL
` + typedActivitySourceSQL("activity_candidates ae", "ae.scope_id")
}

func typedActivitySourceSQL(from string, taskScopeID string) string {
	return `SELECT
    typed.id,
    typed.seq,
    '` + timelineSourceKindActivity + `' AS source_kind,
    typed.source_message_id AS source_message_id,
    typed.id AS source_activity_id,
    typed.actor_ref,
    typed.event_type,
    typed.event_payload,
    typed.created_at
FROM (
  SELECT
      ` + taskScopeID + ` AS task_scope_id,
      ae.id,
      ae.seq,
      COALESCE(ae.source_message_id, '') AS source_message_id,
      ` + activityActorRefSQL("ae") + ` AS actor_ref,
      ` + activityEventTypeSQL("ae") + ` AS event_type,
      ` + activityEventPayloadSQL("ae") + ` AS event_payload,
      ae.created_at
  FROM ` + from + `
) typed
WHERE NOT (
  typed.event_type = '` + discussionAddedEvent + `'
  AND EXISTS (
    SELECT 1
    FROM scope_messages discussion_message
    WHERE discussion_message.scope_id = typed.task_scope_id
      AND (discussion_message.id = typed.source_message_id OR discussion_message.id = typed.event_payload)
      AND discussion_message.message_type = 'task_discussion'
    LIMIT 1
  )
)`
}

func activityActorRefSQL(alias string) string {
	return "COALESCE(JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(" + alias + ".payload) THEN " + alias + ".payload ELSE '{}' END, '$.taskEvent.actorRef')), COALESCE(" + alias + ".actor_member_id, ''))"
}

func activityEventTypeSQL(alias string) string {
	return "COALESCE(JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(" + alias + ".payload) THEN " + alias + ".payload ELSE '{}' END, '$.taskEvent.type')), CASE WHEN " + alias + ".activity_type LIKE 'task.%' THEN SUBSTRING(" + alias + ".activity_type, 6) ELSE " + alias + ".activity_type END)"
}

func activityEventPayloadSQL(alias string) string {
	return "COALESCE(JSON_UNQUOTE(JSON_EXTRACT(CASE WHEN JSON_VALID(" + alias + ".payload) THEN " + alias + ".payload ELSE '{}' END, '$.taskEvent.payload')), '')"
}