multica/server/pkg/db/generated/dingtalk.sql.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.31.1
// source: dingtalk.sql

package db

import (
	"context"

	"github.com/jackc/pgx/v5/pgtype"
)

const countInactiveDingTalkGroupPresencesByWorkspace = `-- name: CountInactiveDingTalkGroupPresencesByWorkspace :many
SELECT presence.installation_id, installation.agent_id, count(*)::bigint AS group_count
FROM dingtalk_group_presence presence
JOIN channel_installation installation ON installation.id = presence.installation_id
WHERE installation.workspace_id = $1
  AND installation.channel_type = 'dingtalk'
  AND installation.status = 'active'
  AND (presence.last_active_at < $2 OR presence.last_active_at IS NULL)
  AND (NOT $3::boolean OR installation.agent_id = $4)
GROUP BY presence.installation_id, installation.agent_id
`

type CountInactiveDingTalkGroupPresencesByWorkspaceParams struct {
	WorkspaceID   pgtype.UUID        `json:"workspace_id"`
	ActiveSince   pgtype.Timestamptz `json:"active_since"`
	FilterByAgent bool               `json:"filter_by_agent"`
	AgentID       pgtype.UUID        `json:"agent_id"`
}

type CountInactiveDingTalkGroupPresencesByWorkspaceRow struct {
	InstallationID pgtype.UUID `json:"installation_id"`
	AgentID        pgtype.UUID `json:"agent_id"`
	GroupCount     int64       `json:"group_count"`
}

func (q *Queries) CountInactiveDingTalkGroupPresencesByWorkspace(ctx context.Context, arg CountInactiveDingTalkGroupPresencesByWorkspaceParams) ([]CountInactiveDingTalkGroupPresencesByWorkspaceRow, error) {
	rows, err := q.db.Query(ctx, countInactiveDingTalkGroupPresencesByWorkspace,
		arg.WorkspaceID,
		arg.ActiveSince,
		arg.FilterByAgent,
		arg.AgentID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []CountInactiveDingTalkGroupPresencesByWorkspaceRow{}
	for rows.Next() {
		var i CountInactiveDingTalkGroupPresencesByWorkspaceRow
		if err := rows.Scan(&i.InstallationID, &i.AgentID, &i.GroupCount); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const deleteDingTalkInstallationForReplacement = `-- name: DeleteDingTalkInstallationForReplacement :one
WITH retired AS (
    DELETE FROM channel_installation ci
    WHERE ci.id = $1
      AND ci.workspace_id = $2
      AND ci.agent_id = $3
      AND ci.channel_type = 'dingtalk'
    RETURNING ci.id
),
cleared_group_presence AS (
    DELETE FROM dingtalk_group_presence
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_bot_identity AS (
    DELETE FROM dingtalk_bot_identity
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_group_routes AS (
    DELETE FROM dingtalk_group_route
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_task_deliveries AS (
    DELETE FROM channel_task_delivery
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_outbound_messages AS (
    DELETE FROM channel_outbound_message
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_chat_sessions AS (
    DELETE FROM channel_chat_session_binding
    WHERE installation_id IN (SELECT id FROM retired)
    RETURNING chat_session_id
),
cleared_outbound_cards AS (
    DELETE FROM channel_outbound_card_message
    WHERE chat_session_id IN (SELECT chat_session_id FROM cleared_chat_sessions)
),
cleared_binding_tokens AS (
    DELETE FROM channel_binding_token
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_user_bindings AS (
    DELETE FROM channel_user_binding
    WHERE installation_id IN (SELECT id FROM retired)
),
cleared_inbound_dedup AS (
    DELETE FROM channel_inbound_message_dedup
    WHERE installation_id IN (SELECT id FROM retired)
),
detached_audit AS (
    UPDATE channel_inbound_audit SET installation_id = NULL
    WHERE installation_id IN (SELECT id FROM retired)
),
detached_media_intents AS (
    UPDATE channel_media_pending_object SET installation_id = NULL
    WHERE installation_id IN (SELECT id FROM retired)
)
SELECT retired.id FROM retired
`

type DeleteDingTalkInstallationForReplacementParams struct {
	InstallationID pgtype.UUID `json:"installation_id"`
	WorkspaceID    pgtype.UUID `json:"workspace_id"`
	AgentID        pgtype.UUID `json:"agent_id"`
}

// Retires an installation when the same agent is connected with a DIFFERENT
// AppKey. A senderStaffId is scoped to one DingTalk organization, so none of the
// old installation's identity, token, session, dedup, or outbound state may
// cross into the new robot. The caller inserts a fresh installation in the same
// transaction, giving the replacement a new installation_id that also fences
// late writes and replies from the old connection.
//
// Chat sessions themselves remain as history, but their channel bindings and
// outbound cards are removed. Audit and media-intent rows remain useful for
// diagnostics / reconciliation, so their installation references are detached.
func (q *Queries) DeleteDingTalkInstallationForReplacement(ctx context.Context, arg DeleteDingTalkInstallationForReplacementParams) (pgtype.UUID, error) {
	row := q.db.QueryRow(ctx, deleteDingTalkInstallationForReplacement, arg.InstallationID, arg.WorkspaceID, arg.AgentID)
	var id pgtype.UUID
	err := row.Scan(&id)
	return id, err
}

const forgetDingTalkGroupPresence = `-- name: ForgetDingTalkGroupPresence :one
DELETE FROM dingtalk_group_presence presence
USING channel_installation installation
WHERE presence.installation_id = installation.id
  AND presence.workspace_id = $1
  AND presence.installation_id = $2
  AND presence.conversation_id = $3
  AND installation.workspace_id = $1
  AND installation.channel_type = 'dingtalk'
RETURNING presence.installation_id
`

type ForgetDingTalkGroupPresenceParams struct {
	WorkspaceID    pgtype.UUID `json:"workspace_id"`
	InstallationID pgtype.UUID `json:"installation_id"`
	ConversationID string      `json:"conversation_id"`
}

func (q *Queries) ForgetDingTalkGroupPresence(ctx context.Context, arg ForgetDingTalkGroupPresenceParams) (pgtype.UUID, error) {
	row := q.db.QueryRow(ctx, forgetDingTalkGroupPresence, arg.WorkspaceID, arg.InstallationID, arg.ConversationID)
	var installation_id pgtype.UUID
	err := row.Scan(&installation_id)
	return installation_id, err
}

const getDingTalkInstallationOwnerForUpdate = `-- name: GetDingTalkInstallationOwnerForUpdate :one
SELECT id, COALESCE(config ->> 'app_id', '')::text AS app_id
FROM channel_installation
WHERE workspace_id = $1
  AND agent_id = $2
  AND channel_type = 'dingtalk'
FOR UPDATE
`

type GetDingTalkInstallationOwnerForUpdateParams struct {
	WorkspaceID pgtype.UUID `json:"workspace_id"`
	AgentID     pgtype.UUID `json:"agent_id"`
}

type GetDingTalkInstallationOwnerForUpdateRow struct {
	ID    pgtype.UUID `json:"id"`
	AppID string      `json:"app_id"`
}

// Reads the current robot identity after LockDingTalkInstallationOwner has
// serialized the logical owner slot. app_id is non-null for every DingTalk
// installation; COALESCE treats malformed legacy config as a different robot,
// which safely replaces it instead of preserving unknown identity state.
func (q *Queries) GetDingTalkInstallationOwnerForUpdate(ctx context.Context, arg GetDingTalkInstallationOwnerForUpdateParams) (GetDingTalkInstallationOwnerForUpdateRow, error) {
	row := q.db.QueryRow(ctx, getDingTalkInstallationOwnerForUpdate, arg.WorkspaceID, arg.AgentID)
	var i GetDingTalkInstallationOwnerForUpdateRow
	err := row.Scan(&i.ID, &i.AppID)
	return i, err
}

const listDingTalkBotIdentitiesByWorkspace = `-- name: ListDingTalkBotIdentitiesByWorkspace :many
SELECT
    installation.id AS installation_id,
    installation.agent_id,
    identity.bot_name,
    identity.bot_identity_issue
FROM channel_installation installation
JOIN dingtalk_bot_identity identity ON identity.installation_id = installation.id
WHERE installation.workspace_id = $1
  AND installation.channel_type = 'dingtalk'
  AND installation.status = 'active'
  AND (NOT $2::boolean OR installation.agent_id = $3)
`

type ListDingTalkBotIdentitiesByWorkspaceParams struct {
	WorkspaceID   pgtype.UUID `json:"workspace_id"`
	FilterByAgent bool        `json:"filter_by_agent"`
	AgentID       pgtype.UUID `json:"agent_id"`
}

type ListDingTalkBotIdentitiesByWorkspaceRow struct {
	InstallationID   pgtype.UUID `json:"installation_id"`
	AgentID          pgtype.UUID `json:"agent_id"`
	BotName          string      `json:"bot_name"`
	BotIdentityIssue string      `json:"bot_identity_issue"`
}

// Identity is installation-owned, so forgetting or collapsing groups cannot
// change the connection label.
func (q *Queries) ListDingTalkBotIdentitiesByWorkspace(ctx context.Context, arg ListDingTalkBotIdentitiesByWorkspaceParams) ([]ListDingTalkBotIdentitiesByWorkspaceRow, error) {
	rows, err := q.db.Query(ctx, listDingTalkBotIdentitiesByWorkspace, arg.WorkspaceID, arg.FilterByAgent, arg.AgentID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []ListDingTalkBotIdentitiesByWorkspaceRow{}
	for rows.Next() {
		var i ListDingTalkBotIdentitiesByWorkspaceRow
		if err := rows.Scan(
			&i.InstallationID,
			&i.AgentID,
			&i.BotName,
			&i.BotIdentityIssue,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listDingTalkGroupPresencesByWorkspace = `-- name: ListDingTalkGroupPresencesByWorkspace :many
SELECT
    presence.conversation_id,
    presence.conversation_title,
    presence.installation_id,
    installation.agent_id,
    COALESCE(identity.bot_name, presence.bot_name)::text AS bot_name,
    COALESCE(identity.bot_identity_issue, presence.bot_identity_issue)::text AS bot_identity_issue,
    presence.last_active_at,
    presence.mention_count
FROM dingtalk_group_presence presence
JOIN channel_installation installation ON installation.id = presence.installation_id
LEFT JOIN dingtalk_bot_identity identity ON identity.installation_id = presence.installation_id
WHERE installation.workspace_id = $1
  AND installation.channel_type = 'dingtalk'
  AND installation.status = 'active'
  AND (NOT $2::boolean OR installation.agent_id = $3)
  AND (NOT $4::boolean OR installation.id = $5)
  AND (
      (NOT $6::boolean AND presence.last_active_at >= $7)
      OR ($6::boolean AND (presence.last_active_at < $7 OR presence.last_active_at IS NULL))
  )
ORDER BY presence.last_active_at DESC NULLS LAST, presence.conversation_title ASC,
         presence.conversation_id ASC, installation.installed_at ASC, installation.id ASC
LIMIT NULLIF($9::integer, 0)
OFFSET $8::integer
`

type ListDingTalkGroupPresencesByWorkspaceParams struct {
	WorkspaceID          pgtype.UUID        `json:"workspace_id"`
	FilterByAgent        bool               `json:"filter_by_agent"`
	AgentID              pgtype.UUID        `json:"agent_id"`
	FilterByInstallation bool               `json:"filter_by_installation"`
	FilterInstallationID pgtype.UUID        `json:"filter_installation_id"`
	IncludeInactive      bool               `json:"include_inactive"`
	ActiveSince          pgtype.Timestamptz `json:"active_since"`
	PageOffset           int32              `json:"page_offset"`
	PageLimit            int32              `json:"page_limit"`
}

type ListDingTalkGroupPresencesByWorkspaceRow struct {
	ConversationID    string             `json:"conversation_id"`
	ConversationTitle string             `json:"conversation_title"`
	InstallationID    pgtype.UUID        `json:"installation_id"`
	AgentID           pgtype.UUID        `json:"agent_id"`
	BotName           string             `json:"bot_name"`
	BotIdentityIssue  string             `json:"bot_identity_issue"`
	LastActiveAt      pgtype.Timestamptz `json:"last_active_at"`
	MentionCount      int64              `json:"mention_count"`
}

// Active rows are the default inventory. Inactive rows are loaded only when a
// client expands one installation's historical section.
func (q *Queries) ListDingTalkGroupPresencesByWorkspace(ctx context.Context, arg ListDingTalkGroupPresencesByWorkspaceParams) ([]ListDingTalkGroupPresencesByWorkspaceRow, error) {
	rows, err := q.db.Query(ctx, listDingTalkGroupPresencesByWorkspace,
		arg.WorkspaceID,
		arg.FilterByAgent,
		arg.AgentID,
		arg.FilterByInstallation,
		arg.FilterInstallationID,
		arg.IncludeInactive,
		arg.ActiveSince,
		arg.PageOffset,
		arg.PageLimit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []ListDingTalkGroupPresencesByWorkspaceRow{}
	for rows.Next() {
		var i ListDingTalkGroupPresencesByWorkspaceRow
		if err := rows.Scan(
			&i.ConversationID,
			&i.ConversationTitle,
			&i.InstallationID,
			&i.AgentID,
			&i.BotName,
			&i.BotIdentityIssue,
			&i.LastActiveAt,
			&i.MentionCount,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const listDingTalkUserBindingsForMember = `-- name: ListDingTalkUserBindingsForMember :many

SELECT installation_id, channel_user_id
FROM channel_user_binding
WHERE workspace_id = $1
  AND multica_user_id = $2
  AND channel_type = 'dingtalk'
ORDER BY bound_at DESC, id ASC
`

type ListDingTalkUserBindingsForMemberParams struct {
	WorkspaceID   pgtype.UUID `json:"workspace_id"`
	MulticaUserID pgtype.UUID `json:"multica_user_id"`
}

type ListDingTalkUserBindingsForMemberRow struct {
	InstallationID pgtype.UUID `json:"installation_id"`
	ChannelUserID  string      `json:"channel_user_id"`
}

// DingTalk-specific installation identity operations. The underlying channel_*
// tables are shared, but these replacement semantics belong to DingTalk's BYO
// AppKey model and deliberately stay out of the shared channel query surface.
// Returns only the requesting Multica member's DingTalk identities. The
// installation list is member-visible, so returning every member's staff id
// here would expose staff ID values more broadly than necessary.
func (q *Queries) ListDingTalkUserBindingsForMember(ctx context.Context, arg ListDingTalkUserBindingsForMemberParams) ([]ListDingTalkUserBindingsForMemberRow, error) {
	rows, err := q.db.Query(ctx, listDingTalkUserBindingsForMember, arg.WorkspaceID, arg.MulticaUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []ListDingTalkUserBindingsForMemberRow{}
	for rows.Next() {
		var i ListDingTalkUserBindingsForMemberRow
		if err := rows.Scan(&i.InstallationID, &i.ChannelUserID); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

const lockDingTalkInstallationOwner = `-- name: LockDingTalkInstallationOwner :exec
SELECT pg_advisory_xact_lock(
    hashtextextended(
        ($1::uuid)::text || ':' ||
        ($2::uuid)::text || ':dingtalk',
        0
    )
)
`

type LockDingTalkInstallationOwnerParams struct {
	WorkspaceID pgtype.UUID `json:"workspace_id"`
	AgentID     pgtype.UUID `json:"agent_id"`
}

// Serializes install / replacement decisions for one logical
// (workspace, agent, channel) slot. A different-AppKey replacement deletes the
// old row and inserts a fresh installation id; the advisory lock closes the
// gap where two concurrent replacements could otherwise miss each other's new
// row and update it in place, carrying identity state across robot boundaries.
func (q *Queries) LockDingTalkInstallationOwner(ctx context.Context, arg LockDingTalkInstallationOwnerParams) error {
	_, err := q.db.Exec(ctx, lockDingTalkInstallationOwner, arg.WorkspaceID, arg.AgentID)
	return err
}

const recordDingTalkGroupActivity = `-- name: RecordDingTalkGroupActivity :one
UPDATE dingtalk_group_presence
SET last_active_at = now(), mention_count = mention_count + 1, updated_at = now()
WHERE installation_id = $1
  AND conversation_id = $2
RETURNING installation_id
`

type RecordDingTalkGroupActivityParams struct {
	InstallationID pgtype.UUID `json:"installation_id"`
	ConversationID string      `json:"conversation_id"`
}

// Advances activity only after the deduplicated inbound message is durable.
// Presence observation runs first, so a forgotten group is recreated before
// this update executes.
func (q *Queries) RecordDingTalkGroupActivity(ctx context.Context, arg RecordDingTalkGroupActivityParams) (pgtype.UUID, error) {
	row := q.db.QueryRow(ctx, recordDingTalkGroupActivity, arg.InstallationID, arg.ConversationID)
	var installation_id pgtype.UUID
	err := row.Scan(&installation_id)
	return installation_id, err
}

const recordDingTalkGroupPresence = `-- name: RecordDingTalkGroupPresence :one
WITH target AS MATERIALIZED (
    SELECT channel_installation.id, channel_installation.workspace_id
    FROM channel_installation
    WHERE channel_installation.id = $1
      AND channel_installation.workspace_id = $2
      AND channel_installation.channel_type = 'dingtalk'
      AND channel_installation.status = 'active'
    FOR KEY SHARE
), current_identity AS (
    SELECT identity.installation_id, identity.bot_name, identity.bot_identity_issue
    FROM target
    JOIN dingtalk_bot_identity identity ON identity.installation_id = target.id
), observed AS (
    INSERT INTO dingtalk_group_presence (
        workspace_id, installation_id, conversation_id, conversation_title,
        bot_name, bot_identity_issue
    )
    SELECT
        target.workspace_id,
        target.id,
        $3::text,
        $4::text,
        current_identity.bot_name,
        current_identity.bot_identity_issue
    FROM target
    JOIN current_identity ON current_identity.installation_id = target.id
    ON CONFLICT (installation_id, conversation_id) DO UPDATE SET
        conversation_title = CASE
            WHEN EXCLUDED.conversation_title <> '' THEN EXCLUDED.conversation_title
            ELSE dingtalk_group_presence.conversation_title
        END,
        bot_name = EXCLUDED.bot_name,
        bot_identity_issue = EXCLUDED.bot_identity_issue,
        updated_at = now()
    WHERE (
        dingtalk_group_presence.conversation_title IS DISTINCT FROM EXCLUDED.conversation_title
        AND EXCLUDED.conversation_title <> ''
    )
       OR dingtalk_group_presence.bot_name IS DISTINCT FROM EXCLUDED.bot_name
       OR dingtalk_group_presence.bot_identity_issue IS DISTINCT FROM EXCLUDED.bot_identity_issue
    RETURNING installation_id
), synced_identity AS (
    UPDATE dingtalk_group_presence presence
    SET
        bot_name = current_identity.bot_name,
        bot_identity_issue = current_identity.bot_identity_issue,
        updated_at = now()
    FROM current_identity
    WHERE presence.installation_id = current_identity.installation_id
      AND presence.conversation_id <> $3::text
      AND (
          presence.bot_name IS DISTINCT FROM current_identity.bot_name
          OR presence.bot_identity_issue IS DISTINCT FROM current_identity.bot_identity_issue
      )
)
SELECT target.id FROM target
`

type RecordDingTalkGroupPresenceParams struct {
	InstallationID    pgtype.UUID `json:"installation_id"`
	WorkspaceID       pgtype.UUID `json:"workspace_id"`
	ConversationID    string      `json:"conversation_id"`
	ConversationTitle string      `json:"conversation_title"`
}

// Records an observed bot/group pair without carrying routing state. The
// installation identity is copied only for compatibility with the preceding
// draft; readers use dingtalk_bot_identity as the authority.
func (q *Queries) RecordDingTalkGroupPresence(ctx context.Context, arg RecordDingTalkGroupPresenceParams) (pgtype.UUID, error) {
	row := q.db.QueryRow(ctx, recordDingTalkGroupPresence,
		arg.InstallationID,
		arg.WorkspaceID,
		arg.ConversationID,
		arg.ConversationTitle,
	)
	var id pgtype.UUID
	err := row.Scan(&id)
	return id, err
}

const upsertDingTalkBotIdentity = `-- name: UpsertDingTalkBotIdentity :one
WITH target AS MATERIALIZED (
    SELECT channel_installation.id, channel_installation.workspace_id
    FROM channel_installation
    WHERE channel_installation.id = $1
      AND channel_installation.workspace_id = $2
      AND channel_installation.channel_type = 'dingtalk'
      AND channel_installation.status = 'active'
    FOR UPDATE
), prior_identity AS (
    SELECT
        COALESCE(identity.bot_name, '')::text AS bot_name,
        COALESCE(identity.bot_identity_issue, '')::text AS bot_identity_issue
    FROM target
    LEFT JOIN dingtalk_bot_identity identity ON identity.installation_id = target.id
), upserted_identity AS (
    INSERT INTO dingtalk_bot_identity (
        workspace_id, installation_id, bot_name, bot_identity_issue
    )
    SELECT
        target.workspace_id,
        target.id,
        COALESCE(NULLIF($3::text, ''), prior_identity.bot_name),
        CASE
            WHEN $4::text <> '' THEN $4::text
            WHEN $3::text <> '' THEN ''
            ELSE prior_identity.bot_identity_issue
        END
    FROM target, prior_identity
    ON CONFLICT (installation_id) DO UPDATE SET
        workspace_id = EXCLUDED.workspace_id,
        bot_name = EXCLUDED.bot_name,
        bot_identity_issue = EXCLUDED.bot_identity_issue,
        updated_at = now()
    WHERE dingtalk_bot_identity.workspace_id IS DISTINCT FROM EXCLUDED.workspace_id
       OR dingtalk_bot_identity.bot_name IS DISTINCT FROM EXCLUDED.bot_name
       OR dingtalk_bot_identity.bot_identity_issue IS DISTINCT FROM EXCLUDED.bot_identity_issue
    RETURNING installation_id
)
SELECT target.id FROM target
`

type UpsertDingTalkBotIdentityParams struct {
	InstallationID   pgtype.UUID `json:"installation_id"`
	WorkspaceID      pgtype.UUID `json:"workspace_id"`
	BotName          string      `json:"bot_name"`
	BotIdentityIssue string      `json:"bot_identity_issue"`
}

// Bot identity and the optional permission issue belong to the installation,
// not to any one group. Keep this as a separate command so the mixed-version
// compatibility trigger can observe the completed command state when the
// group-presence command runs next.
func (q *Queries) UpsertDingTalkBotIdentity(ctx context.Context, arg UpsertDingTalkBotIdentityParams) (pgtype.UUID, error) {
	row := q.db.QueryRow(ctx, upsertDingTalkBotIdentity,
		arg.InstallationID,
		arg.WorkspaceID,
		arg.BotName,
		arg.BotIdentityIssue,
	)
	var id pgtype.UUID
	err := row.Scan(&id)
	return id, err
}