package app

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"
	"time"

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

const (
	AppNotificationTargetHumanDM = "human_dm"
	AppNotificationTargetScope   = "scope"

	AppNotificationContentMessage         = "message"
	AppNotificationContentInteractiveCard = "interactive_card"

	AppNotificationStatusActive    = "active"
	AppNotificationStatusResolved  = "resolved"
	AppNotificationStatusWithdrawn = "withdrawn"
	AppNotificationStatusExpired   = "expired"

	AppNotificationCardStateActive    = "active"
	AppNotificationCardStateResolved  = "resolved"
	AppNotificationCardStateWithdrawn = "withdrawn"
	AppNotificationCardStateExpired   = "expired"

	AppNotificationActionStateAccepted  = "accepted"
	AppNotificationActionStateDelivered = "delivered"
	AppNotificationActionStateAcked     = "acked"
	AppNotificationActionStateFailed    = "failed"

	appNotificationActionStateExpired   = "expired"
	appNotificationActionStateCancelled = "cancelled"
)

// ServiceGrantPolicyRecord is the structured, versioned policy attached to one
// active installation grant. Config is kept as deterministic JSON so the owning
// policy resolver can decode a versioned schema without coupling persistence to
// a single business-domain recipient model.
type ServiceGrantPolicyRecord struct {
	ID                    string
	WorkspaceID           string
	ServiceDefinitionID   string
	ServiceInstallationID string
	ServiceKind           string
	Scope                 string
	Config                json.RawMessage
	PolicyVersion         int64
	PolicyDigest          string
	GrantedByMemberID     string
	GrantedAt             time.Time
	ExpiresAt             *time.Time
	RevokedAt             *time.Time
	CreatedAt             time.Time
	UpdatedAt             time.Time
}

// AppNotificationRecord is the App-owned control record for one visible scope
// message. Optional Human-recipient and business-object facts remain pointers
// so callers retain the distinction between an absent snapshot and a zero
// business version.
type AppNotificationRecord struct {
	ID                      string
	WorkspaceID             string
	ServiceDefinitionID     string
	ServiceInstallationID   string
	ServiceMemberID         string
	ServiceTargetBindingID  string
	TargetKind              string
	TargetRef               string
	ScopeID                 string
	RecipientMemberID       *string
	RecipientPolicyVersion  *int64
	RecipientBasisKind      *string
	RecipientBasisRef       *string
	RecipientFactVersion    *int64
	MessageID               string
	ContentKind             string
	ClientNotificationID    string
	CreatePayloadDigest     string
	BusinessObjectNamespace *string
	BusinessObjectType      *string
	BusinessObjectID        *string
	BusinessObjectVersion   *int64
	BusinessObjectURL       *string
	Status                  string
	Revision                int64
	CausedByActorType       string
	CausedByActorID         string
	ExecutionActorType      string
	ExecutionActorID        string
	CorrelationID           string
	ExpiresAt               *time.Time
	ResolvedAt              *time.Time
	WithdrawnAt             *time.Time
	CreatedAt               time.Time
	UpdatedAt               time.Time
}

// AppNotificationMutationRecord is an idempotent optimistic-concurrency
// receipt for one notification mutation.
type AppNotificationMutationRecord struct {
	ID                     string
	WorkspaceID            string
	AppNotificationID      string
	MutationKind           string
	ClientMutationID       string
	ExpectedRevision       int64
	ResultRevision         int64
	PayloadDigest          string
	ResultState            json.RawMessage
	MessageEditRevision    int64
	ServiceEventDeliveryID *string
	CausedByActorType      string
	CausedByActorID        string
	ExecutionActorType     string
	ExecutionActorID       string
	CorrelationID          string
	CreatedAt              time.Time
	CompletedAt            *time.Time
}

// AppNotificationCardRecord is the current render/action sidecar for an
// interactive-card scope message.
type AppNotificationCardRecord struct {
	ScopeID           string
	MessageID         string
	AppNotificationID string
	SchemaVersion     string
	CardRevision      int64
	RenderPayloadJSON json.RawMessage
	ActionSchemaJSON  json.RawMessage
	State             string
	ExpiresAt         *time.Time
	CreatedAt         time.Time
	UpdatedAt         time.Time
}

// AppNotificationActionRecord is the durable Human action invocation and its
// reliable Service Event Delivery association.
type AppNotificationActionRecord struct {
	ID                     string
	WorkspaceID            string
	AppNotificationID      string
	MessageID              string
	CardRevision           int64
	ActionKey              string
	ActorMemberID          string
	ClientActionID         string
	RequestDigest          string
	InputDigest            string
	InputJSON              json.RawMessage
	ServiceEventDeliveryID string
	State                  string
	ResultCode             string
	ResultDigest           string
	CorrelationID          string
	AcceptedAt             time.Time
	CompletedAt            *time.Time
	CreatedAt              time.Time
	UpdatedAt              time.Time
}

// LoadActiveGrantPolicyInTx loads the newest unrevoked, unexpired grant policy.
// forUpdate is intended for a transaction-bound queryer when a caller will
// consume the policy snapshot in the same write transaction.
func (store *Store) LoadActiveGrantPolicyInTx(ctx context.Context, queryer coredb.QueryRower, installationID string, scope string, forUpdate bool) (ServiceGrantPolicyRecord, error) {
	if queryer == nil {
		return ServiceGrantPolicyRecord{}, fmt.Errorf("load active service grant policy: queryer is nil")
	}
	installationID = strings.TrimSpace(installationID)
	scope = strings.TrimSpace(scope)
	if installationID == "" || scope == "" {
		return ServiceGrantPolicyRecord{}, badRequest("installationId and scope are required")
	}
	query := `
SELECT
    id, workspace_id, service_definition_id, service_installation_id,
    service_kind, scope, config, policy_version, policy_digest,
    granted_by_member_id, granted_at, expires_at, revoked_at, created_at, updated_at
FROM service_grants
WHERE service_installation_id = $1
  AND scope = $2
  AND revoked_at IS NULL
  AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)
ORDER BY policy_version DESC, granted_at DESC, id DESC
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	policy, err := scanServiceGrantPolicy(queryer.QueryRow(ctx, query, installationID, scope))
	if err != nil {
		if coredb.IsNotFound(err) {
			return ServiceGrantPolicyRecord{}, ErrNotFound
		}
		return ServiceGrantPolicyRecord{}, fmt.Errorf("load active service grant policy: %w", err)
	}
	return policy, nil
}

func (store *Store) LoadAppNotification(ctx context.Context, workspaceID string, notificationID string) (AppNotificationRecord, error) {
	return store.LoadAppNotificationInTx(ctx, store.db, workspaceID, notificationID, false)
}

func (store *Store) LoadAppNotificationInTx(ctx context.Context, queryer coredb.QueryRower, workspaceID string, notificationID string, forUpdate bool) (AppNotificationRecord, error) {
	if queryer == nil {
		return AppNotificationRecord{}, fmt.Errorf("load App notification: queryer is nil")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	notificationID = strings.TrimSpace(notificationID)
	if workspaceID == "" || notificationID == "" {
		return AppNotificationRecord{}, badRequest("workspaceId and notificationId are required")
	}
	query := appNotificationSelectSQL + `
WHERE notification.workspace_id = $1
  AND notification.id = $2
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	notification, err := scanAppNotification(queryer.QueryRow(ctx, query, workspaceID, notificationID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationRecord{}, ErrNotFound
		}
		return AppNotificationRecord{}, fmt.Errorf("load App notification: %w", err)
	}
	return notification, nil
}

func (store *Store) FindAppNotificationByClientID(ctx context.Context, workspaceID string, installationID string, clientNotificationID string) (AppNotificationRecord, bool, error) {
	return store.FindAppNotificationByClientIDInTx(ctx, store.db, workspaceID, installationID, clientNotificationID, false)
}

// ListAppNotificationsByBusinessObject returns a bounded stable set owned by a
// registered built-in service. Owning-domain projectors use it to converge
// every card that represents the same versioned object.
func (store *Store) ListAppNotificationsByBusinessObject(
	ctx context.Context,
	workspaceID string,
	identityID string,
	namespace string,
	objectType string,
	objectID string,
	limit int,
) ([]AppNotificationRecord, error) {
	principal, err := store.AppNotificationBuiltinPrincipal(ctx, workspaceID, identityID)
	if err != nil {
		return nil, err
	}
	namespace = strings.TrimSpace(namespace)
	objectType = strings.TrimSpace(objectType)
	objectID = strings.TrimSpace(objectID)
	if namespace == "" || objectType == "" || objectID == "" {
		return nil, badRequest("App notification business object identity is required")
	}
	if limit <= 0 || limit > 500 {
		return nil, badRequest("App notification business object list limit must be between 1 and 500")
	}
	rows, err := store.db.Query(ctx, appNotificationSelectSQL+`
WHERE notification.workspace_id = $1
  AND notification.service_installation_id = $2
  AND notification.business_object_namespace = $3
  AND notification.business_object_type = $4
  AND notification.business_object_id = $5
ORDER BY notification.created_at ASC, notification.id ASC
LIMIT $6
`, principal.WorkspaceID, principal.ServiceInstallationID, namespace, objectType, objectID, limit)
	if err != nil {
		return nil, fmt.Errorf("list App notifications by business object: %w", err)
	}
	defer rows.Close()
	records := make([]AppNotificationRecord, 0)
	for rows.Next() {
		record, err := scanAppNotification(rows)
		if err != nil {
			return nil, fmt.Errorf("scan App notification by business object: %w", err)
		}
		if err := principal.authorizeNotificationRecord(record); err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate App notifications by business object: %w", err)
	}
	return records, nil
}

// ListActiveAppNotificationCardsByBusinessObject returns only the active card
// projections for one recipient-basis kind. Unlike
// ListAppNotificationsByBusinessObject, this read is not diluted by terminal
// notification history, so a bounded owner-domain fanout can still reach every
// current projection after a long-lived business object accumulates many
// historical generations.
func (store *Store) ListActiveAppNotificationCardsByBusinessObject(
	ctx context.Context,
	workspaceID string,
	identityID string,
	namespace string,
	objectType string,
	objectID string,
	recipientBasisKind string,
	limit int,
) ([]AppNotificationRecord, error) {
	principal, err := store.AppNotificationBuiltinPrincipal(ctx, workspaceID, identityID)
	if err != nil {
		return nil, err
	}
	namespace = strings.TrimSpace(namespace)
	objectType = strings.TrimSpace(objectType)
	objectID = strings.TrimSpace(objectID)
	recipientBasisKind = strings.TrimSpace(recipientBasisKind)
	if namespace == "" || objectType == "" || objectID == "" || recipientBasisKind == "" {
		return nil, badRequest("App notification business object and recipient basis are required")
	}
	if limit <= 0 || limit > 500 {
		return nil, badRequest("Active App notification card list limit must be between 1 and 500")
	}
	rows, err := store.db.Query(ctx, appNotificationSelectSQL+`
WHERE notification.workspace_id = $1
  AND notification.service_installation_id = $2
  AND notification.business_object_namespace = $3
  AND notification.business_object_type = $4
  AND notification.business_object_id = $5
  AND notification.recipient_basis_kind = $6
  AND notification.content_kind = 'interactive_card'
  AND notification.status = 'active'
ORDER BY notification.created_at ASC, notification.id ASC
LIMIT $7
`, principal.WorkspaceID, principal.ServiceInstallationID, namespace, objectType, objectID, recipientBasisKind, limit)
	if err != nil {
		return nil, fmt.Errorf("list active App notification cards by business object: %w", err)
	}
	defer rows.Close()
	records := make([]AppNotificationRecord, 0)
	for rows.Next() {
		record, err := scanAppNotification(rows)
		if err != nil {
			return nil, fmt.Errorf("scan active App notification card by business object: %w", err)
		}
		if err := principal.authorizeNotificationRecord(record); err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active App notification cards by business object: %w", err)
	}
	return records, nil
}

func (store *Store) FindAppNotificationByClientIDInTx(ctx context.Context, queryer coredb.QueryRower, workspaceID string, installationID string, clientNotificationID string, forUpdate bool) (AppNotificationRecord, bool, error) {
	if queryer == nil {
		return AppNotificationRecord{}, false, fmt.Errorf("find App notification by client id: queryer is nil")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	installationID = strings.TrimSpace(installationID)
	clientNotificationID = strings.TrimSpace(clientNotificationID)
	if workspaceID == "" || installationID == "" || clientNotificationID == "" {
		return AppNotificationRecord{}, false, badRequest("workspaceId, installationId, and clientNotificationId are required")
	}
	query := appNotificationSelectSQL + `
WHERE notification.workspace_id = $1
  AND notification.service_installation_id = $2
  AND notification.client_notification_id = $3
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	notification, err := scanAppNotification(queryer.QueryRow(ctx, query, workspaceID, installationID, clientNotificationID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationRecord{}, false, nil
		}
		return AppNotificationRecord{}, false, fmt.Errorf("find App notification by client id: %w", err)
	}
	return notification, true, nil
}

// InsertAppNotificationInTx validates the cross-table workspace, author,
// message, scope, installation, member, and binding ownership facts before
// inserting the control row. Unique violations are returned unchanged so the
// application service can reload the canonical idempotency row and compare its
// create payload digest.
func (store *Store) InsertAppNotificationInTx(ctx context.Context, tx coredb.Tx, record AppNotificationRecord) error {
	if tx == nil {
		return fmt.Errorf("insert App notification: transaction is nil")
	}
	record = normalizeAppNotification(record)
	if err := validateAppNotificationRecord(record); err != nil {
		return err
	}
	if err := validateAppNotificationOwnershipInTx(ctx, tx, record); err != nil {
		return err
	}
	_, err := tx.Exec(ctx, `
INSERT INTO service_notifications (
    id, workspace_id, service_definition_id, service_installation_id,
    service_member_id, service_target_binding_id, target_kind, target_ref,
    scope_id, recipient_member_id, recipient_policy_version, recipient_basis_kind,
    recipient_basis_ref, recipient_fact_version, message_id, content_kind,
    client_notification_id, create_payload_digest, business_object_namespace,
    business_object_type, business_object_id, business_object_version, business_object_url,
    status, revision, caused_by_actor_type, caused_by_actor_id,
    execution_actor_type, execution_actor_id, correlation_id,
    expires_at, expiry_attempts, expiry_next_attempt_at, expiry_last_error,
    resolved_at, withdrawn_at, created_at, updated_at
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, $8,
    $9, $10, $11, $12,
    $13, $14, $15, $16,
    $17, $18, $19,
    $20, $21, $22, $23,
    $24, $25, $26, $27,
    $28, $29, $30,
    $31, 0, $31, '',
    $32, $33, $34, $35
)
`, record.ID, record.WorkspaceID, record.ServiceDefinitionID, record.ServiceInstallationID,
		record.ServiceMemberID, record.ServiceTargetBindingID, record.TargetKind, record.TargetRef,
		record.ScopeID, nullableString(record.RecipientMemberID), nullableInt64(record.RecipientPolicyVersion), nullableString(record.RecipientBasisKind),
		nullableString(record.RecipientBasisRef), nullableInt64(record.RecipientFactVersion), record.MessageID, record.ContentKind,
		record.ClientNotificationID, record.CreatePayloadDigest, nullableString(record.BusinessObjectNamespace),
		nullableString(record.BusinessObjectType), nullableString(record.BusinessObjectID), nullableInt64(record.BusinessObjectVersion), nullableString(record.BusinessObjectURL),
		record.Status, record.Revision, record.CausedByActorType, record.CausedByActorID,
		record.ExecutionActorType, record.ExecutionActorID, record.CorrelationID,
		nullableTime(record.ExpiresAt), nullableTime(record.ResolvedAt), nullableTime(record.WithdrawnAt), record.CreatedAt, record.UpdatedAt)
	if err != nil {
		return fmt.Errorf("insert App notification: %w", err)
	}
	return nil
}

// UpdateAppNotificationInTx advances mutable notification projection fields
// under an expected revision and optional expected lifecycle state fence.
func (store *Store) UpdateAppNotificationInTx(ctx context.Context, tx coredb.Tx, next AppNotificationRecord, expectedRevision int64, expectedStatus string) (bool, error) {
	if tx == nil {
		return false, fmt.Errorf("update App notification: transaction is nil")
	}
	next = normalizeAppNotification(next)
	if next.ID == "" || next.WorkspaceID == "" || next.Revision != expectedRevision+1 || next.Revision <= 1 {
		return false, badRequest("notification id, workspace id, and next sequential revision are required")
	}
	if !validAppNotificationStatus(next.Status) {
		return false, badRequest("invalid App notification status")
	}
	query := `
UPDATE service_notifications
SET business_object_version = $1,
    business_object_url = $2,
    status = $3,
    revision = $4,
    caused_by_actor_type = $5,
    caused_by_actor_id = $6,
    execution_actor_type = $7,
    execution_actor_id = $8,
    correlation_id = $9,
    expires_at = $10,
    expiry_attempts = CASE WHEN $3 = 'active' THEN 0 ELSE expiry_attempts END,
    expiry_next_attempt_at = CASE WHEN $3 = 'active' THEN $10 ELSE expiry_next_attempt_at END,
    expiry_last_error = CASE WHEN $3 = 'active' THEN '' ELSE expiry_last_error END,
    resolved_at = $11,
    withdrawn_at = $12,
    updated_at = $13
WHERE workspace_id = $14
  AND id = $15
  AND revision = $16
`
	args := []any{
		nullableInt64(next.BusinessObjectVersion), nullableString(next.BusinessObjectURL), next.Status, next.Revision,
		next.CausedByActorType, next.CausedByActorID, next.ExecutionActorType, next.ExecutionActorID,
		next.CorrelationID, nullableTime(next.ExpiresAt), nullableTime(next.ResolvedAt), nullableTime(next.WithdrawnAt), next.UpdatedAt,
		next.WorkspaceID, next.ID, expectedRevision,
	}
	if expectedStatus = strings.TrimSpace(expectedStatus); expectedStatus != "" {
		query += fmt.Sprintf("  AND status = $%d\n", len(args)+1)
		args = append(args, expectedStatus)
	}
	result, err := tx.Exec(ctx, query, args...)
	if err != nil {
		return false, fmt.Errorf("update App notification: %w", err)
	}
	return result.RowsAffected() == 1, nil
}

func (store *Store) InsertAppNotificationMutationInTx(ctx context.Context, tx coredb.Tx, record AppNotificationMutationRecord) error {
	if tx == nil {
		return fmt.Errorf("insert App notification mutation: transaction is nil")
	}
	record = normalizeAppNotificationMutation(record)
	if err := validateAppNotificationMutationRecord(record); err != nil {
		return err
	}
	var exists int
	if err := tx.QueryRow(ctx, `
SELECT 1
FROM service_notifications
WHERE workspace_id = $1
  AND id = $2
  AND revision = $3
LIMIT 1
`, record.WorkspaceID, record.AppNotificationID, record.ResultRevision).Scan(&exists); err != nil {
		if coredb.IsNotFound(err) {
			return badRequest("App notification mutation result revision is not current")
		}
		return fmt.Errorf("validate App notification mutation: %w", err)
	}
	_, err := tx.Exec(ctx, `
INSERT INTO service_notification_mutations (
    id, workspace_id, service_notification_id, mutation_kind, client_mutation_id,
    expected_revision, result_revision, payload_digest, result_state,
    message_edit_revision, service_event_delivery_id,
    caused_by_actor_type, caused_by_actor_id, execution_actor_type, execution_actor_id,
    correlation_id, created_at, completed_at
) VALUES (
    $1, $2, $3, $4, $5,
    $6, $7, $8, $9,
    $10, $11,
    $12, $13, $14, $15,
    $16, $17, $18
)
`, record.ID, record.WorkspaceID, record.AppNotificationID, record.MutationKind, record.ClientMutationID,
		record.ExpectedRevision, record.ResultRevision, record.PayloadDigest, []byte(record.ResultState),
		record.MessageEditRevision, nullableString(record.ServiceEventDeliveryID),
		record.CausedByActorType, record.CausedByActorID, record.ExecutionActorType, record.ExecutionActorID,
		record.CorrelationID, record.CreatedAt, nullableTime(record.CompletedAt))
	if err != nil {
		return fmt.Errorf("insert App notification mutation: %w", err)
	}
	return nil
}

func (store *Store) FindAppNotificationMutationInTx(ctx context.Context, queryer coredb.QueryRower, workspaceID string, notificationID string, clientMutationID string, forUpdate bool) (AppNotificationMutationRecord, bool, error) {
	if queryer == nil {
		return AppNotificationMutationRecord{}, false, fmt.Errorf("find App notification mutation: queryer is nil")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	notificationID = strings.TrimSpace(notificationID)
	clientMutationID = strings.TrimSpace(clientMutationID)
	if workspaceID == "" || notificationID == "" || clientMutationID == "" {
		return AppNotificationMutationRecord{}, false, badRequest("workspaceId, notificationId, and clientMutationId are required")
	}
	query := appNotificationMutationSelectSQL + `
WHERE workspace_id = $1
  AND service_notification_id = $2
  AND client_mutation_id = $3
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	record, err := scanAppNotificationMutation(queryer.QueryRow(ctx, query, workspaceID, notificationID, clientMutationID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationMutationRecord{}, false, nil
		}
		return AppNotificationMutationRecord{}, false, fmt.Errorf("find App notification mutation: %w", err)
	}
	return record, true, nil
}

func (store *Store) CompleteAppNotificationMutationInTx(ctx context.Context, tx coredb.Tx, mutationID string, payloadDigest string, resultRevision int64, messageEditRevision int64, resultState json.RawMessage, serviceEventDeliveryID *string, completedAt time.Time) (bool, error) {
	if tx == nil {
		return false, fmt.Errorf("complete App notification mutation: transaction is nil")
	}
	mutationID = strings.TrimSpace(mutationID)
	payloadDigest = strings.TrimSpace(payloadDigest)
	if mutationID == "" || payloadDigest == "" || resultRevision <= 0 || messageEditRevision < 0 || !validJSON(resultState) || completedAt.IsZero() {
		return false, badRequest("valid mutation completion fields are required")
	}
	result, err := tx.Exec(ctx, `
UPDATE service_notification_mutations
SET result_revision = $1,
    result_state = $2,
    message_edit_revision = $3,
    service_event_delivery_id = $4,
    completed_at = $5
WHERE id = $6
  AND payload_digest = $7
  AND completed_at IS NULL
  AND result_revision <= $1
`, resultRevision, []byte(resultState), messageEditRevision, nullableString(serviceEventDeliveryID), completedAt.UTC(), mutationID, payloadDigest)
	if err != nil {
		return false, fmt.Errorf("complete App notification mutation: %w", err)
	}
	return result.RowsAffected() == 1, nil
}

func (store *Store) InsertAppNotificationCardInTx(ctx context.Context, tx coredb.Tx, record AppNotificationCardRecord) error {
	if tx == nil {
		return fmt.Errorf("insert App notification card: transaction is nil")
	}
	record = normalizeAppNotificationCard(record)
	if err := validateAppNotificationCardRecord(record); err != nil {
		return err
	}
	var exists int
	if err := tx.QueryRow(ctx, `
SELECT 1
FROM service_notifications notification
JOIN scope_messages message
  ON message.id = notification.message_id
 AND message.scope_id = notification.scope_id
WHERE notification.id = $1
  AND notification.message_id = $2
  AND notification.scope_id = $3
  AND notification.content_kind = 'interactive_card'
  AND message.message_type = 'app_interactive_card'
LIMIT 1
`, record.AppNotificationID, record.MessageID, record.ScopeID).Scan(&exists); err != nil {
		if coredb.IsNotFound(err) {
			return badRequest("App notification card does not match an interactive notification message")
		}
		return fmt.Errorf("validate App notification card: %w", err)
	}
	_, err := tx.Exec(ctx, `
INSERT INTO service_notification_cards (
    scope_id, message_id, service_notification_id, schema_version, card_revision,
    render_payload_json, action_schema_json, state, expires_at, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
`, record.ScopeID, record.MessageID, record.AppNotificationID, record.SchemaVersion, record.CardRevision,
		[]byte(record.RenderPayloadJSON), []byte(record.ActionSchemaJSON), record.State,
		nullableTime(record.ExpiresAt), record.CreatedAt, record.UpdatedAt)
	if err != nil {
		return fmt.Errorf("insert App notification card: %w", err)
	}
	return nil
}

func (store *Store) LoadAppNotificationCardInTx(ctx context.Context, queryer coredb.QueryRower, messageID string, forUpdate bool) (AppNotificationCardRecord, error) {
	if queryer == nil {
		return AppNotificationCardRecord{}, fmt.Errorf("load App notification card: queryer is nil")
	}
	messageID = strings.TrimSpace(messageID)
	if messageID == "" {
		return AppNotificationCardRecord{}, badRequest("messageId is required")
	}
	query := appNotificationCardSelectSQL + "\nWHERE message_id = $1\nLIMIT 1\n"
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	record, err := scanAppNotificationCard(queryer.QueryRow(ctx, query, messageID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationCardRecord{}, ErrNotFound
		}
		return AppNotificationCardRecord{}, fmt.Errorf("load App notification card: %w", err)
	}
	return record, nil
}

// LoadAppNotificationCardsByMessageIDs batch-loads the interactive-card
// sidecars for the supplied message IDs, returning a map keyed by message_id.
// It is the batch equivalent of LoadAppNotificationCardInTx and exists so the
// message-list read decoration pass can resolve every card on a page with a
// single query instead of an N+1 per-card fetch. Missing message IDs simply
// have no entry in the returned map. An empty input returns an empty map
// without touching the database.
func (store *Store) LoadAppNotificationCardsByMessageIDs(ctx context.Context, messageIDs []string) (map[string]AppNotificationCardRecord, error) {
	normalizedMessageIDs := normalizeNonEmptyStrings(messageIDs)
	if len(normalizedMessageIDs) == 0 {
		return map[string]AppNotificationCardRecord{}, nil
	}
	requestedMessageIDsCTE, requestedMessageIDArgs, _, err := coredb.BuildStringValuesCTE("requested_message_ids", "message_id", normalizedMessageIDs, 1)
	if err != nil {
		return nil, fmt.Errorf("build requested message ids: %w", err)
	}
	query := fmt.Sprintf(`
WITH %s
%s
WHERE EXISTS (
    SELECT 1
    FROM requested_message_ids
    WHERE requested_message_ids.message_id = service_notification_cards.message_id
)
`, requestedMessageIDsCTE, appNotificationCardSelectSQL)
	rows, err := store.db.Query(ctx, query, requestedMessageIDArgs...)
	if err != nil {
		return nil, fmt.Errorf("load App notification cards by message ids: %w", err)
	}
	defer rows.Close()
	cards := make(map[string]AppNotificationCardRecord, len(normalizedMessageIDs))
	for rows.Next() {
		record, err := scanAppNotificationCard(rows)
		if err != nil {
			return nil, err
		}
		messageID := strings.TrimSpace(record.MessageID)
		if messageID == "" {
			continue
		}
		if _, exists := cards[messageID]; exists {
			continue
		}
		cards[messageID] = record
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate App notification cards: %w", err)
	}
	return cards, nil
}

func (store *Store) LoadAppNotificationCardByNotificationInTx(ctx context.Context, queryer coredb.QueryRower, notificationID string, forUpdate bool) (AppNotificationCardRecord, error) {
	if queryer == nil {
		return AppNotificationCardRecord{}, fmt.Errorf("load App notification card by notification: queryer is nil")
	}
	notificationID = strings.TrimSpace(notificationID)
	if notificationID == "" {
		return AppNotificationCardRecord{}, badRequest("notificationId is required")
	}
	query := appNotificationCardSelectSQL + "\nWHERE service_notification_id = $1\nLIMIT 1\n"
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	record, err := scanAppNotificationCard(queryer.QueryRow(ctx, query, notificationID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationCardRecord{}, ErrNotFound
		}
		return AppNotificationCardRecord{}, fmt.Errorf("load App notification card by notification: %w", err)
	}
	return record, nil
}

// UpdateAppNotificationCardInTx requires both the expected revision and current
// state. This prevents a late runtime callback from reopening a resolved,
// withdrawn, or expired action set.
func (store *Store) UpdateAppNotificationCardInTx(ctx context.Context, tx coredb.Tx, next AppNotificationCardRecord, expectedRevision int64, expectedState string) (bool, error) {
	if tx == nil {
		return false, fmt.Errorf("update App notification card: transaction is nil")
	}
	next = normalizeAppNotificationCard(next)
	expectedState = strings.TrimSpace(expectedState)
	if err := validateAppNotificationCardRecord(next); err != nil {
		return false, err
	}
	if expectedRevision <= 0 || next.CardRevision != expectedRevision+1 || expectedState == "" {
		return false, badRequest("expected card revision/state and next sequential revision are required")
	}
	result, err := tx.Exec(ctx, `
UPDATE service_notification_cards
SET schema_version = $1,
    card_revision = $2,
    render_payload_json = $3,
    action_schema_json = $4,
    state = $5,
    expires_at = $6,
    updated_at = $7
WHERE message_id = $8
  AND service_notification_id = $9
  AND card_revision = $10
  AND state = $11
`, next.SchemaVersion, next.CardRevision, []byte(next.RenderPayloadJSON), []byte(next.ActionSchemaJSON),
		next.State, nullableTime(next.ExpiresAt), next.UpdatedAt,
		next.MessageID, next.AppNotificationID, expectedRevision, expectedState)
	if err != nil {
		return false, fmt.Errorf("update App notification card: %w", err)
	}
	return result.RowsAffected() == 1, nil
}

func (store *Store) InsertAppNotificationActionInTx(ctx context.Context, tx coredb.Tx, record AppNotificationActionRecord) error {
	if tx == nil {
		return fmt.Errorf("insert App notification action: transaction is nil")
	}
	record = normalizeAppNotificationAction(record)
	if err := validateAppNotificationActionRecord(record); err != nil {
		return err
	}
	var exists int
	if err := tx.QueryRow(ctx, `
SELECT 1
FROM service_notifications notification
JOIN service_notification_cards card
  ON card.service_notification_id = notification.id
 AND card.message_id = notification.message_id
JOIN members actor
  ON actor.id = $1
 AND actor.workspace_id = notification.workspace_id
 AND actor.type = 'human'
 AND actor.status = 'active'
JOIN service_event_deliveries delivery
  ON delivery.id = $2
 AND delivery.workspace_id = notification.workspace_id
 AND delivery.service_installation_id = notification.service_installation_id
 AND delivery.service_member_id = notification.service_member_id
 AND delivery.external_resource_type = 'app_notification'
 AND delivery.external_resource_key = notification.id
WHERE notification.workspace_id = $3
  AND notification.id = $4
  AND notification.message_id = $5
  AND notification.status = 'active'
  AND card.card_revision = $6
  AND card.state = 'active'
LIMIT 1
`, record.ActorMemberID, record.ServiceEventDeliveryID, record.WorkspaceID, record.AppNotificationID, record.MessageID, record.CardRevision).Scan(&exists); err != nil {
		if coredb.IsNotFound(err) {
			return badRequest("App notification action ownership, card revision, actor, or delivery does not match")
		}
		return fmt.Errorf("validate App notification action: %w", err)
	}
	_, err := tx.Exec(ctx, `
INSERT INTO service_notification_actions (
    id, workspace_id, service_notification_id, message_id,
    card_revision, action_key, actor_member_id, client_action_id,
    request_digest, input_digest, input_json, service_event_delivery_id,
    state, result_code, result_digest, correlation_id,
    accepted_at, completed_at, created_at, updated_at
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, $8,
    $9, $10, $11, $12,
    $13, $14, $15, $16,
    $17, $18, $19, $20
)
`, record.ID, record.WorkspaceID, record.AppNotificationID, record.MessageID,
		record.CardRevision, record.ActionKey, record.ActorMemberID, record.ClientActionID,
		record.RequestDigest, record.InputDigest, []byte(record.InputJSON), record.ServiceEventDeliveryID,
		record.State, record.ResultCode, record.ResultDigest, record.CorrelationID,
		record.AcceptedAt, nullableTime(record.CompletedAt), record.CreatedAt, record.UpdatedAt)
	if err != nil {
		return fmt.Errorf("insert App notification action: %w", err)
	}
	return nil
}

func (store *Store) FindAppNotificationActionInTx(ctx context.Context, queryer coredb.QueryRower, messageID string, cardRevision int64, actorMemberID string, clientActionID string, forUpdate bool) (AppNotificationActionRecord, bool, error) {
	if queryer == nil {
		return AppNotificationActionRecord{}, false, fmt.Errorf("find App notification action: queryer is nil")
	}
	messageID = strings.TrimSpace(messageID)
	actorMemberID = strings.TrimSpace(actorMemberID)
	clientActionID = strings.TrimSpace(clientActionID)
	if messageID == "" || cardRevision <= 0 || actorMemberID == "" || clientActionID == "" {
		return AppNotificationActionRecord{}, false, badRequest("messageId, cardRevision, actorMemberId, and clientActionId are required")
	}
	query := appNotificationActionSelectSQL + `
WHERE message_id = $1
  AND card_revision = $2
  AND actor_member_id = $3
  AND client_action_id = $4
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	record, err := scanAppNotificationAction(queryer.QueryRow(ctx, query, messageID, cardRevision, actorMemberID, clientActionID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationActionRecord{}, false, nil
		}
		return AppNotificationActionRecord{}, false, fmt.Errorf("find App notification action: %w", err)
	}
	return record, true, nil
}

func (store *Store) LoadAppNotificationActionInTx(ctx context.Context, queryer coredb.QueryRower, workspaceID string, actionID string, forUpdate bool) (AppNotificationActionRecord, error) {
	if queryer == nil {
		return AppNotificationActionRecord{}, fmt.Errorf("load App notification action: queryer is nil")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	actionID = strings.TrimSpace(actionID)
	if workspaceID == "" || actionID == "" {
		return AppNotificationActionRecord{}, badRequest("workspaceId and actionId are required")
	}
	query := appNotificationActionSelectSQL + "\nWHERE workspace_id = $1 AND id = $2\nLIMIT 1\n"
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	record, err := scanAppNotificationAction(queryer.QueryRow(ctx, query, workspaceID, actionID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return AppNotificationActionRecord{}, ErrNotFound
		}
		return AppNotificationActionRecord{}, fmt.Errorf("load App notification action: %w", err)
	}
	return record, nil
}

func (store *Store) CompleteAppNotificationActionInTx(ctx context.Context, tx coredb.Tx, workspaceID string, actionID string, expectedState string, nextState string, resultCode string, resultDigest string, completedAt time.Time, updatedAt time.Time) (bool, error) {
	if tx == nil {
		return false, fmt.Errorf("complete App notification action: transaction is nil")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	actionID = strings.TrimSpace(actionID)
	expectedState = strings.TrimSpace(expectedState)
	nextState = strings.TrimSpace(nextState)
	resultCode = strings.TrimSpace(resultCode)
	resultDigest = strings.TrimSpace(resultDigest)
	if workspaceID == "" || actionID == "" || expectedState == "" || !validAppNotificationActionState(nextState) || completedAt.IsZero() || updatedAt.IsZero() {
		return false, badRequest("valid action completion fields are required")
	}
	result, err := tx.Exec(ctx, `
UPDATE service_notification_actions
SET state = $1,
    result_code = $2,
    result_digest = $3,
    completed_at = $4,
    updated_at = $5
WHERE workspace_id = $6
  AND id = $7
  AND state = $8
  AND completed_at IS NULL
`, nextState, resultCode, resultDigest, completedAt.UTC(), updatedAt.UTC(), workspaceID, actionID, expectedState)
	if err != nil {
		return false, fmt.Errorf("complete App notification action: %w", err)
	}
	return result.RowsAffected() == 1, nil
}

// MarkAppNotificationActionDeliveredInTx advances an accepted invocation to
// the non-terminal delivered state when a transport begins processing it.
func (store *Store) MarkAppNotificationActionDeliveredInTx(ctx context.Context, tx coredb.Tx, workspaceID string, actionID string, updatedAt time.Time) (bool, error) {
	if tx == nil {
		return false, fmt.Errorf("mark App notification action delivered: transaction is nil")
	}
	workspaceID = strings.TrimSpace(workspaceID)
	actionID = strings.TrimSpace(actionID)
	if workspaceID == "" || actionID == "" || updatedAt.IsZero() {
		return false, badRequest("workspace, action, and delivered time are required")
	}
	result, err := tx.Exec(ctx, `
UPDATE service_notification_actions
SET state = $1,
    updated_at = $2
WHERE workspace_id = $3
  AND id = $4
  AND state = $5
  AND completed_at IS NULL
`, AppNotificationActionStateDelivered, updatedAt.UTC(), workspaceID, actionID, AppNotificationActionStateAccepted)
	if err != nil {
		return false, fmt.Errorf("mark App notification action delivered: %w", err)
	}
	return result.RowsAffected() == 1, nil
}

const appNotificationSelectSQL = `
SELECT
    notification.id, notification.workspace_id,
    notification.service_definition_id, notification.service_installation_id,
    notification.service_member_id, notification.service_target_binding_id,
    notification.target_kind, notification.target_ref, notification.scope_id,
    notification.recipient_member_id, notification.recipient_policy_version,
    notification.recipient_basis_kind, notification.recipient_basis_ref,
    notification.recipient_fact_version, notification.message_id, notification.content_kind,
    notification.client_notification_id, notification.create_payload_digest,
    notification.business_object_namespace, notification.business_object_type,
    notification.business_object_id, notification.business_object_version,
    notification.business_object_url, notification.status, notification.revision,
    notification.caused_by_actor_type, notification.caused_by_actor_id,
    notification.execution_actor_type, notification.execution_actor_id,
    notification.correlation_id, notification.expires_at, notification.resolved_at,
    notification.withdrawn_at, notification.created_at, notification.updated_at
FROM service_notifications notification
`

const appNotificationMutationSelectSQL = `
SELECT
    id, workspace_id, service_notification_id, mutation_kind, client_mutation_id,
    expected_revision, result_revision, payload_digest, result_state,
    message_edit_revision, service_event_delivery_id,
    caused_by_actor_type, caused_by_actor_id, execution_actor_type, execution_actor_id,
    correlation_id, created_at, completed_at
FROM service_notification_mutations
`

const appNotificationCardSelectSQL = `
SELECT
    scope_id, message_id, service_notification_id, schema_version, card_revision,
    render_payload_json, action_schema_json, state, expires_at, created_at, updated_at
FROM service_notification_cards
`

const appNotificationActionSelectSQL = `
SELECT
    id, workspace_id, service_notification_id, message_id,
    card_revision, action_key, actor_member_id, client_action_id,
    request_digest, input_digest, input_json, service_event_delivery_id,
    state, result_code, result_digest, correlation_id,
    accepted_at, completed_at, created_at, updated_at
FROM service_notification_actions
`

func validateAppNotificationOwnershipInTx(ctx context.Context, tx coredb.QueryRower, record AppNotificationRecord) error {
	var exists int
	if err := tx.QueryRow(ctx, `
SELECT 1
FROM service_definitions definition
JOIN service_installations installation
  ON installation.id = $1
 AND installation.workspace_id = $2
 AND installation.service_definition_id = definition.id
 AND installation.status = 'active'
JOIN service_members service_member
  ON service_member.id = $3
 AND service_member.workspace_id = installation.workspace_id
 AND service_member.service_definition_id = definition.id
 AND service_member.service_installation_id = installation.id
 AND service_member.status = 'active'
JOIN service_target_bindings binding
  ON binding.id = $4
 AND binding.workspace_id = installation.workspace_id
 AND binding.service_definition_id = definition.id
 AND binding.service_installation_id = installation.id
 AND binding.service_member_id = service_member.id
 AND binding.delivery_scope_id = $5
 AND binding.status = 'active'
JOIN scopes scope
  ON scope.id = $5
 AND scope.workspace_id = installation.workspace_id
 AND scope.status = 'active'
JOIN scope_messages message
  ON message.id = $6
 AND message.workspace_id = installation.workspace_id
 AND message.scope_id = scope.id
 AND message.author_type = 'service'
 AND message.author_member_id = service_member.member_id
WHERE definition.id = $7
  AND definition.status = 'active'
  AND (
    $8 = 'scope'
    OR EXISTS (
      SELECT 1
      FROM members recipient
      WHERE recipient.id = $9
        AND recipient.workspace_id = installation.workspace_id
        AND recipient.type = 'human'
        AND recipient.status = 'active'
    )
  )
LIMIT 1
`, record.ServiceInstallationID, record.WorkspaceID, record.ServiceMemberID,
		record.ServiceTargetBindingID, record.ScopeID, record.MessageID,
		record.ServiceDefinitionID, record.TargetKind, nullableString(record.RecipientMemberID)).Scan(&exists); err != nil {
		if coredb.IsNotFound(err) {
			return badRequest("App notification ownership facts do not match the message target")
		}
		return fmt.Errorf("validate App notification ownership: %w", err)
	}
	return nil
}

func scanServiceGrantPolicy(row coredb.Row) (ServiceGrantPolicyRecord, error) {
	var record ServiceGrantPolicyRecord
	var config []byte
	var expiresAt, revokedAt sql.NullTime
	if err := row.Scan(
		&record.ID, &record.WorkspaceID, &record.ServiceDefinitionID, &record.ServiceInstallationID,
		&record.ServiceKind, &record.Scope, &config, &record.PolicyVersion, &record.PolicyDigest,
		&record.GrantedByMemberID, &record.GrantedAt, &expiresAt, &revokedAt, &record.CreatedAt, &record.UpdatedAt,
	); err != nil {
		return ServiceGrantPolicyRecord{}, err
	}
	record = normalizeServiceGrantPolicy(record)
	record.Config = cloneRawJSON(config)
	record.ExpiresAt = timePtr(expiresAt)
	record.RevokedAt = timePtr(revokedAt)
	return record, nil
}

func scanAppNotification(row coredb.Row) (AppNotificationRecord, error) {
	var record AppNotificationRecord
	var recipientMemberID, recipientBasisKind, recipientBasisRef sql.NullString
	var recipientPolicyVersion, recipientFactVersion sql.NullInt64
	var businessNamespace, businessType, businessID, businessURL sql.NullString
	var businessVersion sql.NullInt64
	var expiresAt, resolvedAt, withdrawnAt sql.NullTime
	if err := row.Scan(
		&record.ID, &record.WorkspaceID, &record.ServiceDefinitionID, &record.ServiceInstallationID,
		&record.ServiceMemberID, &record.ServiceTargetBindingID, &record.TargetKind, &record.TargetRef,
		&record.ScopeID, &recipientMemberID, &recipientPolicyVersion, &recipientBasisKind,
		&recipientBasisRef, &recipientFactVersion, &record.MessageID, &record.ContentKind,
		&record.ClientNotificationID, &record.CreatePayloadDigest,
		&businessNamespace, &businessType, &businessID, &businessVersion, &businessURL,
		&record.Status, &record.Revision, &record.CausedByActorType, &record.CausedByActorID,
		&record.ExecutionActorType, &record.ExecutionActorID, &record.CorrelationID,
		&expiresAt, &resolvedAt, &withdrawnAt, &record.CreatedAt, &record.UpdatedAt,
	); err != nil {
		return AppNotificationRecord{}, err
	}
	record.RecipientMemberID = stringPtr(recipientMemberID)
	record.RecipientPolicyVersion = int64Ptr(recipientPolicyVersion)
	record.RecipientBasisKind = stringPtr(recipientBasisKind)
	record.RecipientBasisRef = stringPtr(recipientBasisRef)
	record.RecipientFactVersion = int64Ptr(recipientFactVersion)
	record.BusinessObjectNamespace = stringPtr(businessNamespace)
	record.BusinessObjectType = stringPtr(businessType)
	record.BusinessObjectID = stringPtr(businessID)
	record.BusinessObjectVersion = int64Ptr(businessVersion)
	record.BusinessObjectURL = stringPtr(businessURL)
	record.ExpiresAt = timePtr(expiresAt)
	record.ResolvedAt = timePtr(resolvedAt)
	record.WithdrawnAt = timePtr(withdrawnAt)
	return normalizeAppNotification(record), nil
}

func scanAppNotificationMutation(row coredb.Row) (AppNotificationMutationRecord, error) {
	var record AppNotificationMutationRecord
	var resultState []byte
	var deliveryID sql.NullString
	var completedAt sql.NullTime
	if err := row.Scan(
		&record.ID, &record.WorkspaceID, &record.AppNotificationID, &record.MutationKind, &record.ClientMutationID,
		&record.ExpectedRevision, &record.ResultRevision, &record.PayloadDigest, &resultState,
		&record.MessageEditRevision, &deliveryID,
		&record.CausedByActorType, &record.CausedByActorID, &record.ExecutionActorType, &record.ExecutionActorID,
		&record.CorrelationID, &record.CreatedAt, &completedAt,
	); err != nil {
		return AppNotificationMutationRecord{}, err
	}
	record.ResultState = cloneRawJSON(resultState)
	record.ServiceEventDeliveryID = stringPtr(deliveryID)
	record.CompletedAt = timePtr(completedAt)
	return normalizeAppNotificationMutation(record), nil
}

func scanAppNotificationCard(row coredb.Row) (AppNotificationCardRecord, error) {
	var record AppNotificationCardRecord
	var renderPayload, actionSchema []byte
	var expiresAt sql.NullTime
	if err := row.Scan(
		&record.ScopeID, &record.MessageID, &record.AppNotificationID, &record.SchemaVersion, &record.CardRevision,
		&renderPayload, &actionSchema, &record.State, &expiresAt, &record.CreatedAt, &record.UpdatedAt,
	); err != nil {
		return AppNotificationCardRecord{}, err
	}
	record.RenderPayloadJSON = cloneRawJSON(renderPayload)
	record.ActionSchemaJSON = cloneRawJSON(actionSchema)
	record.ExpiresAt = timePtr(expiresAt)
	return normalizeAppNotificationCard(record), nil
}

func scanAppNotificationAction(row coredb.Row) (AppNotificationActionRecord, error) {
	var record AppNotificationActionRecord
	var input []byte
	var completedAt sql.NullTime
	if err := row.Scan(
		&record.ID, &record.WorkspaceID, &record.AppNotificationID, &record.MessageID,
		&record.CardRevision, &record.ActionKey, &record.ActorMemberID, &record.ClientActionID,
		&record.RequestDigest, &record.InputDigest, &input, &record.ServiceEventDeliveryID,
		&record.State, &record.ResultCode, &record.ResultDigest, &record.CorrelationID,
		&record.AcceptedAt, &completedAt, &record.CreatedAt, &record.UpdatedAt,
	); err != nil {
		return AppNotificationActionRecord{}, err
	}
	record.InputJSON = cloneRawJSON(input)
	record.CompletedAt = timePtr(completedAt)
	return normalizeAppNotificationAction(record), nil
}

func normalizeServiceGrantPolicy(record ServiceGrantPolicyRecord) ServiceGrantPolicyRecord {
	record.ID = strings.TrimSpace(record.ID)
	record.WorkspaceID = strings.TrimSpace(record.WorkspaceID)
	record.ServiceDefinitionID = strings.TrimSpace(record.ServiceDefinitionID)
	record.ServiceInstallationID = strings.TrimSpace(record.ServiceInstallationID)
	record.ServiceKind = strings.TrimSpace(record.ServiceKind)
	record.Scope = strings.TrimSpace(record.Scope)
	record.PolicyDigest = strings.TrimSpace(record.PolicyDigest)
	record.GrantedByMemberID = strings.TrimSpace(record.GrantedByMemberID)
	return record
}

func normalizeAppNotification(record AppNotificationRecord) AppNotificationRecord {
	record.ID = strings.TrimSpace(record.ID)
	record.WorkspaceID = strings.TrimSpace(record.WorkspaceID)
	record.ServiceDefinitionID = strings.TrimSpace(record.ServiceDefinitionID)
	record.ServiceInstallationID = strings.TrimSpace(record.ServiceInstallationID)
	record.ServiceMemberID = strings.TrimSpace(record.ServiceMemberID)
	record.ServiceTargetBindingID = strings.TrimSpace(record.ServiceTargetBindingID)
	record.TargetKind = strings.TrimSpace(record.TargetKind)
	record.TargetRef = strings.TrimSpace(record.TargetRef)
	record.ScopeID = strings.TrimSpace(record.ScopeID)
	record.MessageID = strings.TrimSpace(record.MessageID)
	record.ContentKind = strings.TrimSpace(record.ContentKind)
	record.ClientNotificationID = strings.TrimSpace(record.ClientNotificationID)
	record.CreatePayloadDigest = strings.TrimSpace(record.CreatePayloadDigest)
	record.Status = strings.TrimSpace(record.Status)
	record.CausedByActorType = strings.TrimSpace(record.CausedByActorType)
	record.CausedByActorID = strings.TrimSpace(record.CausedByActorID)
	record.ExecutionActorType = strings.TrimSpace(record.ExecutionActorType)
	record.ExecutionActorID = strings.TrimSpace(record.ExecutionActorID)
	record.CorrelationID = strings.TrimSpace(record.CorrelationID)
	record.RecipientMemberID = trimStringPointer(record.RecipientMemberID)
	record.RecipientBasisKind = trimStringPointer(record.RecipientBasisKind)
	record.RecipientBasisRef = trimStringPointer(record.RecipientBasisRef)
	record.BusinessObjectNamespace = trimStringPointer(record.BusinessObjectNamespace)
	record.BusinessObjectType = trimStringPointer(record.BusinessObjectType)
	record.BusinessObjectID = trimStringPointer(record.BusinessObjectID)
	record.BusinessObjectURL = trimStringPointer(record.BusinessObjectURL)
	return record
}

func normalizeAppNotificationMutation(record AppNotificationMutationRecord) AppNotificationMutationRecord {
	record.ID = strings.TrimSpace(record.ID)
	record.WorkspaceID = strings.TrimSpace(record.WorkspaceID)
	record.AppNotificationID = strings.TrimSpace(record.AppNotificationID)
	record.MutationKind = strings.TrimSpace(record.MutationKind)
	record.ClientMutationID = strings.TrimSpace(record.ClientMutationID)
	record.PayloadDigest = strings.TrimSpace(record.PayloadDigest)
	record.ServiceEventDeliveryID = trimStringPointer(record.ServiceEventDeliveryID)
	record.CausedByActorType = strings.TrimSpace(record.CausedByActorType)
	record.CausedByActorID = strings.TrimSpace(record.CausedByActorID)
	record.ExecutionActorType = strings.TrimSpace(record.ExecutionActorType)
	record.ExecutionActorID = strings.TrimSpace(record.ExecutionActorID)
	record.CorrelationID = strings.TrimSpace(record.CorrelationID)
	return record
}

func normalizeAppNotificationCard(record AppNotificationCardRecord) AppNotificationCardRecord {
	record.ScopeID = strings.TrimSpace(record.ScopeID)
	record.MessageID = strings.TrimSpace(record.MessageID)
	record.AppNotificationID = strings.TrimSpace(record.AppNotificationID)
	record.SchemaVersion = strings.TrimSpace(record.SchemaVersion)
	record.State = strings.TrimSpace(record.State)
	return record
}

func normalizeAppNotificationAction(record AppNotificationActionRecord) AppNotificationActionRecord {
	record.ID = strings.TrimSpace(record.ID)
	record.WorkspaceID = strings.TrimSpace(record.WorkspaceID)
	record.AppNotificationID = strings.TrimSpace(record.AppNotificationID)
	record.MessageID = strings.TrimSpace(record.MessageID)
	record.ActionKey = strings.TrimSpace(record.ActionKey)
	record.ActorMemberID = strings.TrimSpace(record.ActorMemberID)
	record.ClientActionID = strings.TrimSpace(record.ClientActionID)
	record.RequestDigest = strings.TrimSpace(record.RequestDigest)
	record.InputDigest = strings.TrimSpace(record.InputDigest)
	record.ServiceEventDeliveryID = strings.TrimSpace(record.ServiceEventDeliveryID)
	record.State = strings.TrimSpace(record.State)
	record.ResultCode = strings.TrimSpace(record.ResultCode)
	record.ResultDigest = strings.TrimSpace(record.ResultDigest)
	record.CorrelationID = strings.TrimSpace(record.CorrelationID)
	return record
}

func validateAppNotificationRecord(record AppNotificationRecord) error {
	if record.ID == "" || record.WorkspaceID == "" || record.ServiceDefinitionID == "" || record.ServiceInstallationID == "" ||
		record.ServiceMemberID == "" || record.ServiceTargetBindingID == "" || record.TargetRef == "" || record.ScopeID == "" ||
		record.MessageID == "" || record.ClientNotificationID == "" || record.CreatePayloadDigest == "" ||
		record.CausedByActorType == "" || record.CausedByActorID == "" || record.ExecutionActorType == "" ||
		record.ExecutionActorID == "" || record.CorrelationID == "" || record.CreatedAt.IsZero() || record.UpdatedAt.IsZero() {
		return badRequest("complete App notification identity, actor, digest, correlation, and timestamps are required")
	}
	if record.Revision <= 0 || !validAppNotificationStatus(record.Status) {
		return badRequest("valid App notification revision and status are required")
	}
	if record.ContentKind != AppNotificationContentMessage && record.ContentKind != AppNotificationContentInteractiveCard {
		return badRequest("invalid App notification content kind")
	}
	switch record.TargetKind {
	case AppNotificationTargetHumanDM:
		if emptyStringPointer(record.RecipientMemberID) || record.RecipientPolicyVersion == nil || *record.RecipientPolicyVersion <= 0 ||
			emptyStringPointer(record.RecipientBasisKind) || emptyStringPointer(record.RecipientBasisRef) ||
			record.RecipientFactVersion == nil || *record.RecipientFactVersion <= 0 {
			return badRequest("Human DM notification requires a complete recipient policy and fact snapshot")
		}
	case AppNotificationTargetScope:
		if record.RecipientMemberID != nil || record.RecipientPolicyVersion != nil || record.RecipientBasisKind != nil || record.RecipientBasisRef != nil || record.RecipientFactVersion != nil {
			return badRequest("scope notification cannot contain Human DM recipient facts")
		}
	default:
		return badRequest("invalid App notification target kind")
	}
	businessParts := []bool{
		!emptyStringPointer(record.BusinessObjectNamespace),
		!emptyStringPointer(record.BusinessObjectType),
		!emptyStringPointer(record.BusinessObjectID),
		record.BusinessObjectVersion != nil && *record.BusinessObjectVersion > 0,
	}
	hasBusiness := false
	for _, present := range businessParts {
		hasBusiness = hasBusiness || present
	}
	if hasBusiness {
		for _, present := range businessParts {
			if !present {
				return badRequest("business object namespace, type, id, and positive version must be supplied together")
			}
		}
	} else if record.BusinessObjectURL != nil {
		return badRequest("business object URL requires a business object")
	}
	return validateAppNotificationLifecycle(record.Status, record.ResolvedAt, record.WithdrawnAt)
}

func validateAppNotificationMutationRecord(record AppNotificationMutationRecord) error {
	if record.ID == "" || record.WorkspaceID == "" || record.AppNotificationID == "" || record.ClientMutationID == "" ||
		record.PayloadDigest == "" || record.CausedByActorType == "" || record.CausedByActorID == "" ||
		record.ExecutionActorType == "" || record.ExecutionActorID == "" || record.CorrelationID == "" ||
		record.CreatedAt.IsZero() || !validJSON(record.ResultState) {
		return badRequest("complete App notification mutation identity, result, actor, and timestamps are required")
	}
	if record.ExpectedRevision <= 0 || record.ResultRevision <= record.ExpectedRevision || record.MessageEditRevision < 0 {
		return badRequest("App notification mutation revisions are invalid")
	}
	switch record.MutationKind {
	case "update", "resolve", "withdraw", "expire":
		return nil
	default:
		return badRequest("invalid App notification mutation kind")
	}
}

func validateAppNotificationCardRecord(record AppNotificationCardRecord) error {
	if record.ScopeID == "" || record.MessageID == "" || record.AppNotificationID == "" || record.SchemaVersion == "" ||
		record.CardRevision <= 0 || !validJSON(record.RenderPayloadJSON) || !validJSON(record.ActionSchemaJSON) ||
		record.CreatedAt.IsZero() || record.UpdatedAt.IsZero() {
		return badRequest("complete App notification card identity, JSON, revision, and timestamps are required")
	}
	switch record.State {
	case AppNotificationCardStateActive, AppNotificationCardStateResolved, AppNotificationCardStateWithdrawn, AppNotificationCardStateExpired:
		return nil
	default:
		return badRequest("invalid App notification card state")
	}
}

func validateAppNotificationActionRecord(record AppNotificationActionRecord) error {
	if record.ID == "" || record.WorkspaceID == "" || record.AppNotificationID == "" || record.MessageID == "" ||
		record.CardRevision <= 0 || record.ActionKey == "" || record.ActorMemberID == "" || record.ClientActionID == "" ||
		record.RequestDigest == "" || record.InputDigest == "" || !validJSON(record.InputJSON) ||
		record.ServiceEventDeliveryID == "" || record.CorrelationID == "" || record.AcceptedAt.IsZero() ||
		record.CreatedAt.IsZero() || record.UpdatedAt.IsZero() {
		return badRequest("complete App notification action identity, digests, input, delivery, and timestamps are required")
	}
	if len(record.ClientActionID) > 128 {
		return badRequest("App notification client action id is too long")
	}
	if !validAppNotificationActionState(record.State) {
		return badRequest("invalid App notification action state")
	}
	return nil
}

func validateAppNotificationLifecycle(status string, resolvedAt *time.Time, withdrawnAt *time.Time) error {
	switch status {
	case AppNotificationStatusActive:
		if resolvedAt != nil || withdrawnAt != nil {
			return badRequest("active App notification cannot have terminal timestamps")
		}
	case AppNotificationStatusResolved:
		if resolvedAt == nil || withdrawnAt != nil {
			return badRequest("resolved App notification requires resolvedAt only")
		}
	case AppNotificationStatusWithdrawn:
		if withdrawnAt == nil {
			return badRequest("withdrawn App notification requires withdrawnAt")
		}
	case AppNotificationStatusExpired:
		if withdrawnAt != nil {
			return badRequest("expired App notification cannot have withdrawnAt")
		}
	default:
		return badRequest("invalid App notification status")
	}
	return nil
}

func validAppNotificationStatus(status string) bool {
	switch status {
	case AppNotificationStatusActive, AppNotificationStatusResolved, AppNotificationStatusWithdrawn, AppNotificationStatusExpired:
		return true
	default:
		return false
	}
}

func validAppNotificationActionState(state string) bool {
	switch state {
	case AppNotificationActionStateAccepted, AppNotificationActionStateDelivered, AppNotificationActionStateAcked,
		AppNotificationActionStateFailed, appNotificationActionStateExpired, appNotificationActionStateCancelled:
		return true
	default:
		return false
	}
}

func validJSON(value json.RawMessage) bool {
	return len(value) > 0 && json.Valid(value)
}

func cloneRawJSON(value []byte) json.RawMessage {
	if value == nil {
		return nil
	}
	return append(json.RawMessage(nil), value...)
}

func nullableString(value *string) any {
	if value == nil {
		return nil
	}
	return *value
}

func nullableInt64(value *int64) any {
	if value == nil {
		return nil
	}
	return *value
}

func nullableTime(value *time.Time) any {
	if value == nil {
		return nil
	}
	return value.UTC()
}

func stringPtr(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	result := strings.TrimSpace(value.String)
	return &result
}

func int64Ptr(value sql.NullInt64) *int64 {
	if !value.Valid {
		return nil
	}
	result := value.Int64
	return &result
}

func timePtr(value sql.NullTime) *time.Time {
	if !value.Valid {
		return nil
	}
	result := value.Time.UTC()
	return &result
}

func trimStringPointer(value *string) *string {
	if value == nil {
		return nil
	}
	result := strings.TrimSpace(*value)
	return &result
}

func emptyStringPointer(value *string) bool {
	return value == nil || strings.TrimSpace(*value) == ""
}