package message

// taskCanonicalBoardChannelFenceSQL is the shared Task topology relation used
// by historical readable-row queries, sidecars, writes, and live visible-scope
// admission. The board ID is the only authority source; tasks.channel_id is
// retained solely as a denormalized equality fence. Callers provide aliases
// already bound to the same workspace and active scope state.
func taskCanonicalBoardChannelFenceSQL(taskAlias string, boardAlias string, channelAlias string) string {
	return taskAlias + ".board_id IS NOT NULL" +
		" AND " + boardAlias + ".id = " + taskAlias + ".board_id" +
		" AND " + channelAlias + ".id = " + boardAlias + ".channel_id" +
		" AND " + taskAlias + ".channel_id = " + boardAlias + ".channel_id" +
		" AND " + boardAlias + ".workspace_id = " + channelAlias + ".workspace_id"
}

// taskParentChannelOwnerActiveSQL is the shared archive/revoke fence for
// owner-private Task parent memberships. Channel archive currently closes the
// archiving owner's membership row rather than the Channel scope globally; a
// private Agent membership owned by that Human must therefore also require an
// active owner membership. Public membership rows have no owner and retain the
// ordinary Channel behavior.
func taskParentChannelOwnerActiveSQL(membershipAlias string, channelAlias string) string {
	return "(" + membershipAlias + ".owner_member_id IS NULL OR EXISTS (" +
		"SELECT 1 FROM scope_memberships task_parent_owner_membership " +
		"WHERE task_parent_owner_membership.scope_id = " + channelAlias + ".id " +
		"AND task_parent_owner_membership.member_id = " + membershipAlias + ".owner_member_id " +
		"AND task_parent_owner_membership.removed_at IS NULL " +
		"AND task_parent_owner_membership.archived_at IS NULL " +
		"AND task_parent_owner_membership.effective_to_seq IS NULL))"
}

// scopePrivateAuthorityGrantIDSQL and scopePrivateAuthorityGrantJoinsSQL define
// the shared read-authority candidate source for owner-private ScopeAPI read
// surfaces: history, search, read-check, source, and attachment checks. Agent
// The agent delivery inbox remains a current-event transport and intentionally
// does not include every no-wake readable history row.
var scopePrivateAuthorityGrantIDSQL = `COALESCE(
         CASE WHEN message_scope.type = 'task' AND sm.visibility = 'private' AND ` + taskHistoricalParentAuthoritySQL + ` THEN 'task-parent-authority' ELSE NULL END,
	         spg_scope.id,
	         spg_parent.id,
	         CASE WHEN tspm_scope.id IS NOT NULL THEN CONCAT('task_scope_participant:', tspm_scope.id) ELSE NULL END,
	         CASE WHEN tspm_parent.id IS NOT NULL THEN CONCAT('task_scope_participant:', tspm_parent.id) ELSE NULL END,
	         CASE
	           WHEN sibling_private.member_id IS NOT NULL
	            AND (
	              (
	                message_scope.type <> 'thread'
	                AND message_scope.type <> 'task'
	              )
	              OR (
	                message_scope.type = 'thread'
	                AND viewer_parent_private.member_id IS NOT NULL
	                AND sibling_parent_private.member_id IS NOT NULL
	              )
	            ) THEN CONCAT('sibling_private:', sibling_private.member_id)
	           ELSE NULL
	         END,
	         ''
	       )`

const scopePrivateAuthorityGrantJoinsSQL = `
LEFT JOIN scope_private_grants spg_scope
  ON spg_scope.workspace_id = m.workspace_id
 AND spg_scope.scope_id = m.scope_id
 AND spg_scope.owner_member_id = sm.owner_member_id
 AND spg_scope.grantee_member_id = m.author_member_id
 AND m.created_at >= spg_scope.created_at
 AND (spg_scope.expires_at IS NULL OR m.created_at < spg_scope.expires_at)
 AND (spg_scope.revoked_at IS NULL OR m.created_at < spg_scope.revoked_at)
LEFT JOIN scope_private_grants spg_parent
  ON spg_parent.workspace_id = m.workspace_id
 AND message_scope.type = 'thread'
 AND spg_parent.scope_id = message_scope.parent_scope_id
 AND spg_parent.owner_member_id = sm.owner_member_id
 AND spg_parent.grantee_member_id = m.author_member_id
 AND m.created_at >= spg_parent.created_at
 AND (spg_parent.expires_at IS NULL OR m.created_at < spg_parent.expires_at)
 AND (spg_parent.revoked_at IS NULL OR m.created_at < spg_parent.revoked_at)
LEFT JOIN task_scope_executions tse_scope
  ON tse_scope.workspace_id = m.workspace_id
 AND tse_scope.owner_member_id = sm.owner_member_id
 AND tse_scope.scope_id = m.scope_id
 AND tse_scope.scope_type = message_scope.type
 AND message_scope.type IN ('channel', 'thread')
LEFT JOIN task_scope_participant_metadata tspm_scope
  ON tspm_scope.owner_scope_execution_id = tse_scope.id
 AND tspm_scope.member_id = m.author_member_id
 AND m.created_at >= tspm_scope.added_at
 AND (tspm_scope.expires_at IS NULL OR m.created_at < tspm_scope.expires_at)
 AND (tspm_scope.removed_at IS NULL OR m.created_at < tspm_scope.removed_at)
 AND (tspm_scope.left_at IS NULL OR m.created_at < tspm_scope.left_at)
LEFT JOIN task_scope_executions tse_parent
  ON tse_parent.workspace_id = m.workspace_id
 AND message_scope.type = 'thread'
 AND tse_parent.owner_member_id = sm.owner_member_id
 AND tse_parent.scope_type = 'channel'
 AND tse_parent.scope_id = message_scope.parent_scope_id
LEFT JOIN task_scope_participant_metadata tspm_parent
  ON tspm_parent.owner_scope_execution_id = tse_parent.id
 AND tspm_parent.member_id = m.author_member_id
 AND m.created_at >= tspm_parent.added_at
 AND (tspm_parent.expires_at IS NULL OR m.created_at < tspm_parent.expires_at)
 AND (tspm_parent.removed_at IS NULL OR m.created_at < tspm_parent.removed_at)
 AND (tspm_parent.left_at IS NULL OR m.created_at < tspm_parent.left_at)
LEFT JOIN agents sibling_agent
  ON sibling_agent.workspace_id = m.workspace_id
 AND sibling_agent.member_id = m.author_member_id
LEFT JOIN scope_memberships sibling_private
  ON message_scope.type <> 'task'
 AND sibling_private.scope_id = m.scope_id
 AND sibling_private.member_id = sibling_agent.member_id
 AND sibling_private.member_id <> sm.member_id
 AND sibling_private.owner_member_id = sm.owner_member_id
 AND sibling_private.visibility = 'private'
 AND sm.visibility = 'private'
 AND COALESCE(sm.owner_member_id, '') <> ''
 AND sibling_private.removed_at IS NULL
 AND sibling_private.archived_at IS NULL
 AND sibling_private.effective_to_seq IS NULL
 AND (
       message_scope.type = 'task'
       OR (
         m.seq >= sibling_private.effective_from_seq
         AND (sibling_private.effective_to_seq IS NULL OR m.seq <= sibling_private.effective_to_seq)
       )
     )
LEFT JOIN scope_memberships viewer_parent_private
  ON message_scope.type = 'thread'
 AND viewer_parent_private.scope_id = message_scope.parent_scope_id
 AND viewer_parent_private.member_id = sm.member_id
 AND viewer_parent_private.owner_member_id = sm.owner_member_id
 AND viewer_parent_private.visibility = 'private'
 AND viewer_parent_private.removed_at IS NULL
 AND viewer_parent_private.archived_at IS NULL
 AND viewer_parent_private.effective_to_seq IS NULL
LEFT JOIN scope_memberships sibling_parent_private
  ON message_scope.type = 'thread'
 AND sibling_parent_private.scope_id = message_scope.parent_scope_id
 AND sibling_parent_private.member_id = sibling_private.member_id
 AND sibling_parent_private.owner_member_id = sm.owner_member_id
 AND sibling_parent_private.visibility = 'private'
 AND sibling_parent_private.removed_at IS NULL
 AND sibling_parent_private.archived_at IS NULL
 AND sibling_parent_private.effective_to_seq IS NULL
`

const scopeMembershipCoversMessageSQL = `(m.seq >= sm.effective_from_seq AND (sm.effective_to_seq IS NULL OR m.seq <= sm.effective_to_seq))`

// Task messages use their durable created_at authority timestamp rather than
// the task-local seq, which is unrelated to the parent Channel seq. The
// authority interval is populated by the membership writer at join/rejoin and
// is backfilled from created_at/removed_at for historical rows. This is the
// only comparable frontier used for Task parent membership reads; no current
// wall clock is consulted for historical message decisions.
func taskParentMembershipCoversAtSQL(membershipAlias string, eventAtSQL string) string {
	return "(" + eventAtSQL + " >= COALESCE(" + membershipAlias + ".authority_from_at, " + membershipAlias + ".created_at)" +
		" AND (COALESCE(" + membershipAlias + ".authority_to_at, " + membershipAlias + ".removed_at) IS NULL" +
		" OR " + eventAtSQL + " < COALESCE(" + membershipAlias + ".authority_to_at, " + membershipAlias + ".removed_at)))"
}

var scopeReadableMembershipCoversMessageSQL = `(
      (
        message_scope.type = 'task'
        AND ` + taskHistoricalParentAuthoritySQL + `
      )
      OR (
        message_scope.type <> 'task'
        AND ` + scopeMembershipCoversMessageSQL + `
      )
)`

// scopeReadableMembershipJoinSQL is the canonical membership source for
// message read-row authority. It covers direct scope membership rows plus the
// existing product behavior that active public parent-channel members may
// actively read reply-thread rows without being delivery/wake recipients.
//
// Task scopes use the authoritative Task->board Channel authority: a task-scoped
// message is readable when the viewer holds active membership on the task's
// board channel. The board channel is derived from tasks.board_id ->
// task_boards (never the denormalized tasks.channel_id alone), and the join
// fail-closes on a tasks.channel_id/task_boards.channel_id mismatch, matching
// the visible-scope authorizer and access.ResolveActiveTaskBoardChannel. Direct
// Task scope membership is preserved only for watch/notification/unread/
// read-cursor and participant projection state, never as a read-authorization
// fact, so a retained Task membership with no Channel membership grants
// nothing.
//
// Queries using this helper must bind scope_messages as m and scopes as
// message_scope before the join. Parent-channel readability intentionally lives
// here, not in sidecar-specific fallback code, so history/search/read-check and
// message-owned sidecars share one readable-row authority.
func scopeReadableMembershipJoinSQL(viewerMemberSQL string) string {
	return `
LEFT JOIN tasks task_read_source
  ON message_scope.type = 'task'
 AND task_read_source.task_scope_id = message_scope.id
LEFT JOIN task_boards task_read_board
  ON task_read_board.id = task_read_source.board_id
 AND task_read_board.workspace_id = message_scope.workspace_id
LEFT JOIN scopes source_scope
  ON source_scope.id = task_read_board.channel_id
 AND source_scope.workspace_id = message_scope.workspace_id
 AND source_scope.type = 'channel'
 AND source_scope.status = 'active'
 AND source_scope.archived_at IS NULL
 AND ` + taskCanonicalBoardChannelFenceSQL("task_read_source", "task_read_board", "source_scope") + `
JOIN scope_memberships sm
  ON sm.member_id = ` + viewerMemberSQL + `
 AND sm.removed_at IS NULL
 AND sm.visibility IN ('public', 'private')
 AND (
       (
           message_scope.type <> 'task'
           AND sm.scope_id = m.scope_id
           AND (
                 (
                     sm.visibility = 'private'
                     AND (sm.effective_to_seq IS NULL OR m.seq <= sm.effective_to_seq)
                 )
                 OR (
                     (
                         message_scope.type = 'service_account'
                         AND sm.visibility = 'public'
                         AND sm.archived_at IS NULL
                         AND sm.effective_to_seq IS NULL
                     )
                     OR (
                         m.seq >= sm.effective_from_seq
                         AND (sm.effective_to_seq IS NULL OR m.seq <= sm.effective_to_seq)
                     )
                 )
               )
       )
       OR (
           message_scope.type = 'task'
           AND source_scope.id IS NOT NULL
           AND sm.scope_id = source_scope.id
       )
       OR (
           message_scope.type = 'thread'
           AND sm.scope_id = message_scope.parent_scope_id
           AND sm.visibility = 'public'
           AND sm.archived_at IS NULL
           AND sm.effective_to_seq IS NULL
       )
     )
`
}

const scopePrivateViewerMemberSQL = `EXISTS (
            SELECT 1
            FROM members viewer_member
            WHERE viewer_member.workspace_id = m.workspace_id
              AND viewer_member.id = sm.member_id
              AND viewer_member.type = 'agent'
          )`

// scopeReadableMessageReasonSQL mirrors scopeReadableMessagePredicateSQL and is
// selected by every production read-row surface that returns visibility refs.
// The Go layer may map/paginate these rows, but it must not re-compute read
// authority with a second public/private visibility implementation.
var scopeReadableMessageReasonSQL = `CASE
        WHEN sm.visibility = 'public' THEN '` + ScopeVisibilityReasonMembership + `'
        WHEN sm.visibility = 'private'
         AND ` + scopePrivateViewerMemberSQL + `
         AND ` + scopeReadableMembershipCoversMessageSQL + `
         AND m.author_member_id = sm.member_id THEN '` + ScopeVisibilityReasonPrivateOutput + `'
        WHEN sm.visibility = 'private'
         AND ` + scopePrivateViewerMemberSQL + `
         AND ` + scopeReadableMembershipCoversMessageSQL + `
         AND COALESCE(sm.owner_member_id, '') <> ''
         AND m.author_member_id = sm.owner_member_id THEN '` + ScopeVisibilityReasonAuthorInterval + `'
        WHEN sm.visibility = 'private'
         AND ` + scopePrivateViewerMemberSQL + `
         AND (message_scope.type <> 'task' OR ` + taskHistoricalParentAuthoritySQL + `)
         AND ` + scopePrivateAuthorityGrantIDSQL + ` <> '' THEN '` + ScopeVisibilityReasonPrivateGrant + `'
        ELSE ''
      END`

// scopeReadableMessagePredicateSQL is the shared database-side readable-row
// predicate for ScopeAPI history/search/read-check and message-derived
// resource surfaces. Receive does not use this predicate: daemon delivery is
// driven by persisted fanout payloads, while ordinary read surfaces read
// currently readable rows.
var scopeReadableMessagePredicateSQL = `(
      message_scope.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
      AND (
      sm.visibility = 'public'
      OR (
          sm.visibility = 'private'
          AND ` + scopePrivateViewerMemberSQL + `
          AND (
              (
                  message_scope.type = 'task'
                  AND ` + taskHistoricalParentAuthoritySQL + `
              )
              OR (
                  message_scope.type <> 'task'
                  AND (
                      (
                          ` + scopeReadableMembershipCoversMessageSQL + `
                          AND (
                              m.author_member_id = sm.member_id
                              OR (
                                  COALESCE(sm.owner_member_id, '') <> ''
                                  AND m.author_member_id = sm.owner_member_id
                              )
                          )
                      )
                      OR ` + scopePrivateAuthorityGrantIDSQL + ` <> ''
                  )
              )
          )
      ))
  )
  AND ` + MessageNotRewoundSQL("m")