package schedule

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

// Definition is the canonical schedule definition record.
type Definition struct {
	ID                     string
	WorkspaceID            string
	OwnerMemberID          string
	PrincipalMemberID      string
	Name                   string
	Status                 string
	SourceKind             string
	SourceType             string
	ScheduledAt            *time.Time
	RelativeSeconds        *int64
	AnchorAt               *time.Time
	IntervalSeconds        *int64
	CronExpression         string
	Timezone               string
	TargetKind             string
	TargetRef              string
	DeliveryScopeID        string
	PermissionCheckScopeID string
	TargetSnapshot         []byte
	MessageTemplate        string
	NotificationMode       string
	NextOccurrenceAt       *time.Time
	LastRunAt              *time.Time
	LastSuccessAt          *time.Time
	LastFailureAt          *time.Time
	LastFailureCode        string
	LastFailureDetail      string
	PausedAt               *time.Time
	PausedReason           string
	DeletedAt              *time.Time
	IdempotencyKey         string
	CreatedByMemberID      string
	CreatedByActorType     string
	CreatedByActorID       string
	CreatedVia             string
	Metadata               []byte
	CreatedAt              time.Time
	UpdatedAt              time.Time
}

// DefinitionCreate describes a schedule definition creation request.
type DefinitionCreate struct {
	ID                     string
	WorkspaceID            string
	OwnerMemberID          string
	PrincipalMemberID      string
	Name                   string
	Status                 string
	SourceKind             string
	SourceType             string
	ScheduledAt            *time.Time
	RelativeSeconds        *int64
	AnchorAt               *time.Time
	IntervalSeconds        *int64
	CronExpression         string
	Timezone               string
	TargetKind             string
	TargetRef              string
	DeliveryScopeID        string
	PermissionCheckScopeID string
	TargetSnapshot         []byte
	MessageTemplate        string
	NotificationMode       string
	NextOccurrenceAt       *time.Time
	IdempotencyKey         string
	CreatedByMemberID      string
	CreatedByActorType     string
	CreatedByActorID       string
	CreatedVia             string
	Metadata               []byte
	Now                    time.Time
}

// DefinitionUpdate describes a full mutable schedule definition update.
type DefinitionUpdate struct {
	WorkspaceID            string
	ID                     string
	Name                   string
	Status                 string
	SourceKind             string
	SourceType             string
	ScheduledAt            *time.Time
	RelativeSeconds        *int64
	AnchorAt               *time.Time
	IntervalSeconds        *int64
	CronExpression         string
	Timezone               string
	TargetKind             string
	TargetRef              string
	DeliveryScopeID        string
	PermissionCheckScopeID string
	TargetSnapshot         []byte
	MessageTemplate        string
	NotificationMode       string
	NextOccurrenceAt       *time.Time
	Metadata               []byte
	Now                    time.Time
}

// DefinitionListQuery bounds schedule definition list queries.
type DefinitionListQuery struct {
	WorkspaceID         string
	OwnerMemberID       string
	PrincipalMemberID   string
	TargetKind          string
	TargetRef           string
	IncludeChildThreads bool
	Status              string
	Statuses            []string
	IncludeDeleted      bool
	Limit               int
	Offset              int
}

// DefinitionStatusUpdate describes a schedule definition status transition.
type DefinitionStatusUpdate struct {
	WorkspaceID string
	ID          string
	Status      string
	Reason      string
	Now         time.Time
}

// CreateDefinition creates a schedule definition, reusing an existing row for the same idempotency key.
func (store *Store) CreateDefinition(ctx context.Context, params DefinitionCreate) (Definition, bool, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return Definition{}, false, err
	}
	return createDefinition(ctx, execer, params)
}

// CreateDefinition creates a schedule definition in the transaction.
func (store *TxStore) CreateDefinition(ctx context.Context, params DefinitionCreate) (Definition, bool, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Definition{}, false, err
	}
	return createDefinition(ctx, execer, params)
}

// UpdateDefinition updates mutable schedule definition fields.
func (store *Store) UpdateDefinition(ctx context.Context, params DefinitionUpdate) (Definition, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return Definition{}, err
	}
	return updateDefinition(ctx, execer, params)
}

// UpdateDefinition updates mutable schedule definition fields in the transaction.
func (store *TxStore) UpdateDefinition(ctx context.Context, params DefinitionUpdate) (Definition, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Definition{}, err
	}
	return updateDefinition(ctx, execer, params)
}

// LoadDefinition loads one schedule definition by id.
func (store *Store) LoadDefinition(ctx context.Context, workspaceID string, definitionID string) (Definition, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return Definition{}, err
	}
	return loadDefinition(ctx, queryer, workspaceID, definitionID, false)
}

// LoadDefinitionForUpdate locks one schedule definition by id.
func (store *TxStore) LoadDefinitionForUpdate(ctx context.Context, workspaceID string, definitionID string) (Definition, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Definition{}, err
	}
	return loadDefinition(ctx, execer, workspaceID, definitionID, true)
}

// ListDefinitions lists schedule definitions.
func (store *Store) ListDefinitions(ctx context.Context, params DefinitionListQuery) ([]Definition, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listDefinitions(ctx, queryer, params, false)
}

// ListDefinitionsForUpdate lists and locks schedule definitions in a transaction.
func (store *TxStore) ListDefinitionsForUpdate(ctx context.Context, params DefinitionListQuery) ([]Definition, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return nil, err
	}
	return listDefinitions(ctx, execer, params, true)
}

// SetDefinitionStatus updates active/paused/blocked/completed state.
func (store *Store) SetDefinitionStatus(ctx context.Context, params DefinitionStatusUpdate) (Definition, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return Definition{}, err
	}
	return setDefinitionStatus(ctx, execer, params)
}

// SetDefinitionStatus updates active/paused/blocked/completed state in the transaction.
func (store *TxStore) SetDefinitionStatus(ctx context.Context, params DefinitionStatusUpdate) (Definition, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Definition{}, err
	}
	return setDefinitionStatus(ctx, execer, params)
}

// DeleteDefinition soft-deletes a schedule definition.
func (store *Store) DeleteDefinition(ctx context.Context, workspaceID string, definitionID string, now time.Time) (Definition, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return Definition{}, err
	}
	return deleteDefinition(ctx, execer, workspaceID, definitionID, now)
}

// DeleteDefinition soft-deletes a schedule definition in the transaction.
func (store *TxStore) DeleteDefinition(ctx context.Context, workspaceID string, definitionID string, now time.Time) (Definition, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Definition{}, err
	}
	return deleteDefinition(ctx, execer, workspaceID, definitionID, now)
}

func createDefinition(ctx context.Context, execer coredb.QueryExecer, params DefinitionCreate) (Definition, bool, error) {
	normalized, err := normalizeDefinitionCreate(params)
	if err != nil {
		return Definition{}, false, err
	}
	if normalized.IdempotencyKey != "" {
		if existing, found, err := loadDefinitionByIdempotencyKeyForUpdate(ctx, execer, normalized.WorkspaceID, normalized.IdempotencyKey); err != nil {
			return Definition{}, false, err
		} else if found {
			return existing, true, nil
		}
	}
	definitionID := trim(normalized.ID)
	if definitionID == "" {
		definitionID, err = identifiers.NewEntityID("sch")
		if err != nil {
			return Definition{}, false, fmt.Errorf("generate schedule definition id: %w", err)
		}
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO schedule_definitions (
    id, workspace_id, owner_member_id, principal_member_id,
    name, status, source_kind, source_type, scheduled_at, relative_seconds,
    anchor_at, interval_seconds, cron_expression, timezone,
    target_kind, target_ref, delivery_scope_id, permission_check_scope_id,
    target_snapshot, message_template, notification_mode, next_occurrence_at,
    idempotency_key, created_by_member_id, created_by_actor_type,
    created_by_actor_id, created_via, metadata, last_failure_detail,
    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, $29
)
`, definitionID, normalized.WorkspaceID, normalized.OwnerMemberID, normalized.PrincipalMemberID,
		normalized.Name, normalized.Status, normalized.SourceKind, normalized.SourceType, normalized.ScheduledAt, normalized.RelativeSeconds,
		normalized.AnchorAt, normalized.IntervalSeconds, nullableString(normalized.CronExpression), nullableString(normalized.Timezone),
		normalized.TargetKind, normalized.TargetRef, nullableString(normalized.DeliveryScopeID), nullableString(normalized.PermissionCheckScopeID),
		nullableBytes(normalized.TargetSnapshot), normalized.MessageTemplate, normalized.NotificationMode, normalized.NextOccurrenceAt,
		nullableString(normalized.IdempotencyKey), normalized.CreatedByMemberID, normalized.CreatedByActorType,
		normalized.CreatedByActorID, normalized.CreatedVia, nullableBytes(normalized.Metadata), normalized.Now); err != nil {
		if coredb.IsUniqueViolation(err) && normalized.IdempotencyKey != "" {
			if existing, found, loadErr := loadDefinitionByIdempotencyKeyForUpdate(ctx, execer, normalized.WorkspaceID, normalized.IdempotencyKey); loadErr != nil {
				return Definition{}, false, loadErr
			} else if found {
				return existing, true, nil
			}
		}
		return Definition{}, false, fmt.Errorf("insert schedule definition: %w", mapDefinitionWriteError(err))
	}
	definition, err := loadDefinition(ctx, execer, normalized.WorkspaceID, definitionID, false)
	return definition, false, err
}

func updateDefinition(ctx context.Context, execer coredb.QueryExecer, params DefinitionUpdate) (Definition, error) {
	params.WorkspaceID = trim(params.WorkspaceID)
	params.ID = trim(params.ID)
	if params.WorkspaceID == "" || params.ID == "" {
		return Definition{}, badRequest("schedule definition id and workspace are required")
	}
	existing, err := loadDefinition(ctx, execer, params.WorkspaceID, params.ID, false)
	if err != nil {
		return Definition{}, err
	}
	requestedStatus := trim(params.Status)
	if requestedStatus == "" {
		params.Status = existing.Status
	} else {
		status, err := normalizeStatus(requestedStatus)
		if err != nil {
			return Definition{}, err
		}
		if status != existing.Status {
			return Definition{}, badRequest("schedule definition status transitions must use status operations")
		}
		params.Status = status
	}
	normalized, err := normalizeDefinitionUpdate(params)
	if err != nil {
		return Definition{}, err
	}
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET name = $3,
    status = $4,
    source_kind = $5,
    source_type = $6,
    scheduled_at = $7,
    relative_seconds = $8,
    anchor_at = $9,
    interval_seconds = $10,
    cron_expression = $11,
    timezone = $12,
    target_kind = $13,
    target_ref = $14,
    delivery_scope_id = $15,
    permission_check_scope_id = $16,
    target_snapshot = $17,
    message_template = $18,
    notification_mode = $19,
    next_occurrence_at = $20,
    metadata = $21,
    updated_at = $22
WHERE workspace_id = $1
  AND id = $2
  AND deleted_at IS NULL
`, normalized.WorkspaceID, normalized.ID, normalized.Name, normalized.Status, normalized.SourceKind, normalized.SourceType,
		normalized.ScheduledAt, normalized.RelativeSeconds, normalized.AnchorAt, normalized.IntervalSeconds,
		nullableString(normalized.CronExpression), nullableString(normalized.Timezone), normalized.TargetKind, normalized.TargetRef,
		nullableString(normalized.DeliveryScopeID), nullableString(normalized.PermissionCheckScopeID), nullableBytes(normalized.TargetSnapshot), normalized.MessageTemplate,
		normalized.NotificationMode, normalized.NextOccurrenceAt, nullableBytes(normalized.Metadata), normalized.Now)
	if err != nil {
		return Definition{}, fmt.Errorf("update schedule definition: %w", mapDefinitionWriteError(err))
	}
	if result.RowsAffected() == 0 {
		return Definition{}, notFound("schedule definition not found")
	}
	return loadDefinition(ctx, execer, normalized.WorkspaceID, normalized.ID, false)
}

func loadDefinition(ctx context.Context, queryer coredb.QueryRower, workspaceID string, definitionID string, forUpdate bool) (Definition, error) {
	trimmedWorkspaceID := trim(workspaceID)
	trimmedID := trim(definitionID)
	if trimmedWorkspaceID == "" || trimmedID == "" {
		return Definition{}, badRequest("workspaceId and scheduleDefinitionId are required")
	}
	query := definitionSelectSQL() + `
WHERE workspace_id = $1
  AND id = $2
  AND deleted_at IS NULL
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	definition, err := scanDefinition(queryer.QueryRow(ctx, query, trimmedWorkspaceID, trimmedID))
	if err != nil {
		if coredb.IsNotFound(err) {
			return Definition{}, notFound("schedule definition not found")
		}
		return Definition{}, fmt.Errorf("load schedule definition: %w", err)
	}
	return definition, nil
}

func loadDefinitionByIdempotencyKeyForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, idempotencyKey string) (Definition, bool, error) {
	row := queryer.QueryRow(ctx, definitionSelectSQL()+`
WHERE workspace_id = $1
  AND idempotency_key = $2
  AND deleted_at IS NULL
FOR UPDATE
`, trim(workspaceID), trim(idempotencyKey))
	definition, err := scanDefinition(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Definition{}, false, nil
		}
		return Definition{}, false, fmt.Errorf("load schedule definition by idempotency key: %w", err)
	}
	return definition, true, nil
}

func listDefinitions(ctx context.Context, queryer coredb.Queryer, params DefinitionListQuery, forUpdate bool) ([]Definition, error) {
	workspaceID := trim(params.WorkspaceID)
	if workspaceID == "" {
		return nil, badRequest("workspaceId is required")
	}
	conditions := []string{"workspace_id = $1"}
	if !params.IncludeDeleted {
		conditions = append(conditions, "deleted_at IS NULL")
	}
	args := []any{workspaceID}
	if trim(params.OwnerMemberID) != "" {
		args = append(args, trim(params.OwnerMemberID))
		conditions = append(conditions, fmt.Sprintf("owner_member_id = $%d", len(args)))
	}
	if trim(params.PrincipalMemberID) != "" {
		args = append(args, trim(params.PrincipalMemberID))
		conditions = append(conditions, fmt.Sprintf("principal_member_id = $%d", len(args)))
	}
	targetKind := trim(params.TargetKind)
	targetRef := trim(params.TargetRef)
	if params.IncludeChildThreads && (targetKind == "" || targetRef == "") {
		return nil, badRequest("includeChildThreads requires targetKind and targetRef")
	}
	if targetKind != "" || targetRef != "" {
		if targetKind == "" || targetRef == "" {
			return nil, badRequest("targetKind and targetRef must be provided together")
		}
		args = append(args, targetKind, targetRef)
		kindPlaceholder := len(args) - 1
		refPlaceholder := len(args)
		targetCondition := fmt.Sprintf("(target_kind = $%d AND target_ref = $%d)", kindPlaceholder, refPlaceholder)
		if params.IncludeChildThreads {
			targetCondition = fmt.Sprintf(`(%s OR (
  target_kind = '%s'
  AND EXISTS (
    SELECT 1
    FROM scopes child_scope
    WHERE child_scope.workspace_id = schedule_definitions.workspace_id
      AND child_scope.id = schedule_definitions.target_ref
      AND child_scope.type = 'thread'
      AND child_scope.parent_scope_id = $%d
      AND child_scope.status = 'active'
      AND child_scope.archived_at IS NULL
  )
))`, targetCondition, TargetKindReplyThread, refPlaceholder)
		}
		conditions = append(conditions, targetCondition)
	}
	if trim(params.Status) != "" {
		args = append(args, trim(params.Status))
		conditions = append(conditions, fmt.Sprintf("status = $%d", len(args)))
	} else if len(params.Statuses) > 0 {
		conditions = append(conditions, stringInCondition("status", params.Statuses, &args))
	}
	query := definitionSelectSQL() + `
WHERE ` + strings.Join(conditions, " AND ") + `
ORDER BY created_at DESC, id ASC
`
	if params.Limit > 0 {
		args = append(args, params.Limit)
		query += fmt.Sprintf("LIMIT $%d\n", len(args))
	}
	if params.Offset > 0 {
		args = append(args, params.Offset)
		query += fmt.Sprintf("OFFSET $%d\n", len(args))
	}
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list schedule definitions: %w", err)
	}
	defer rows.Close()
	definitions := make([]Definition, 0)
	for rows.Next() {
		definition, err := scanDefinition(rows)
		if err != nil {
			return nil, fmt.Errorf("scan schedule definition: %w", err)
		}
		definitions = append(definitions, definition)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate schedule definitions: %w", err)
	}
	return definitions, nil
}

func setDefinitionStatus(ctx context.Context, execer coredb.QueryExecer, params DefinitionStatusUpdate) (Definition, error) {
	workspaceID := trim(params.WorkspaceID)
	definitionID := trim(params.ID)
	status, err := normalizeStatus(params.Status)
	if err != nil {
		return Definition{}, err
	}
	if workspaceID == "" || definitionID == "" {
		return Definition{}, badRequest("workspaceId and scheduleDefinitionId are required")
	}
	now := utcOrNow(params.Now)
	var pausedAt any
	pausedReason := ""
	if status == DefinitionStatusPaused {
		pausedAt = now
		pausedReason = trim(params.Reason)
	}
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET status = $3,
    paused_at = $4,
    paused_reason = $5,
    updated_at = $6
WHERE workspace_id = $1
  AND id = $2
  AND deleted_at IS NULL
`, workspaceID, definitionID, status, pausedAt, pausedReason, now)
	if err != nil {
		return Definition{}, fmt.Errorf("update schedule definition status: %w", err)
	}
	if result.RowsAffected() == 0 {
		return Definition{}, notFound("schedule definition not found")
	}
	return loadDefinition(ctx, execer, workspaceID, definitionID, true)
}

func deleteDefinition(ctx context.Context, execer coredb.QueryExecer, workspaceID string, definitionID string, now time.Time) (Definition, error) {
	trimmedWorkspaceID := trim(workspaceID)
	trimmedID := trim(definitionID)
	if trimmedWorkspaceID == "" || trimmedID == "" {
		return Definition{}, badRequest("workspaceId and scheduleDefinitionId are required")
	}
	definition, err := loadDefinition(ctx, execer, trimmedWorkspaceID, trimmedID, true)
	if err != nil {
		return Definition{}, err
	}
	deletedAt := utcOrNow(now)
	result, err := execer.Exec(ctx, `
UPDATE schedule_definitions
SET deleted_at = $3,
    updated_at = $3
WHERE workspace_id = $1
  AND id = $2
  AND deleted_at IS NULL
`, trimmedWorkspaceID, trimmedID, deletedAt)
	if err != nil {
		return Definition{}, fmt.Errorf("delete schedule definition: %w", err)
	}
	if result.RowsAffected() == 0 {
		return Definition{}, notFound("schedule definition not found")
	}
	definition.DeletedAt = &deletedAt
	definition.UpdatedAt = deletedAt
	return definition, nil
}

func normalizeDefinitionCreate(params DefinitionCreate) (DefinitionCreate, error) {
	params.WorkspaceID = trim(params.WorkspaceID)
	params.OwnerMemberID = trim(params.OwnerMemberID)
	params.PrincipalMemberID = trim(params.PrincipalMemberID)
	params.Name = trim(params.Name)
	params.SourceKind = normalizeSourceKind(params.SourceKind)
	params.SourceType = trim(params.SourceType)
	params.CronExpression = trim(params.CronExpression)
	params.Timezone = trim(params.Timezone)
	params.TargetKind = trim(params.TargetKind)
	params.TargetRef = trim(params.TargetRef)
	params.DeliveryScopeID = trim(params.DeliveryScopeID)
	params.PermissionCheckScopeID = trim(params.PermissionCheckScopeID)
	params.MessageTemplate = trim(params.MessageTemplate)
	params.NotificationMode = normalizeNotificationMode(params.NotificationMode)
	params.IdempotencyKey = trim(params.IdempotencyKey)
	params.CreatedByMemberID = trim(params.CreatedByMemberID)
	params.CreatedByActorType = trim(params.CreatedByActorType)
	params.CreatedByActorID = trim(params.CreatedByActorID)
	params.CreatedVia = trim(params.CreatedVia)
	params.Now = utcOrNow(params.Now)
	status, err := normalizeStatus(params.Status)
	if err != nil {
		return DefinitionCreate{}, err
	}
	params.Status = status
	if params.WorkspaceID == "" || params.OwnerMemberID == "" || params.PrincipalMemberID == "" || params.Name == "" || params.TargetKind == "" || params.TargetRef == "" || params.CreatedByMemberID == "" || params.CreatedByActorType == "" || params.CreatedByActorID == "" || params.CreatedVia == "" {
		return DefinitionCreate{}, badRequest("schedule definition workspace, owner, principal, name, target, and creator are required")
	}
	if err := validateSource(params.SourceKind, params.SourceType, params.ScheduledAt, params.RelativeSeconds, params.AnchorAt, params.IntervalSeconds, params.CronExpression, params.Timezone); err != nil {
		return DefinitionCreate{}, err
	}
	if err := validateTargetKind(params.TargetKind); err != nil {
		return DefinitionCreate{}, err
	}
	if err := validateTargetFields(params.TargetKind, params.DeliveryScopeID, params.PermissionCheckScopeID, params.MessageTemplate); err != nil {
		return DefinitionCreate{}, err
	}
	params = canonicalizeCreateTimeSource(params)
	nextOccurrenceAt, err := initialOccurrenceForDefinition(Definition{
		ID:              params.ID,
		SourceKind:      params.SourceKind,
		SourceType:      params.SourceType,
		ScheduledAt:     params.ScheduledAt,
		RelativeSeconds: params.RelativeSeconds,
		AnchorAt:        params.AnchorAt,
		IntervalSeconds: params.IntervalSeconds,
		CronExpression:  params.CronExpression,
		Timezone:        params.Timezone,
	}, params.Now)
	if err != nil {
		return DefinitionCreate{}, badRequest(err.Error())
	}
	if params.NextOccurrenceAt == nil || (params.Status == DefinitionStatusActive && isOneShotSource(params.SourceType)) {
		params.NextOccurrenceAt = nextOccurrenceAt
	}
	return params, nil
}

func normalizeDefinitionUpdate(params DefinitionUpdate) (DefinitionUpdate, error) {
	params.WorkspaceID = trim(params.WorkspaceID)
	params.ID = trim(params.ID)
	params.Name = trim(params.Name)
	params.SourceKind = normalizeSourceKind(params.SourceKind)
	params.SourceType = trim(params.SourceType)
	params.CronExpression = trim(params.CronExpression)
	params.Timezone = trim(params.Timezone)
	params.TargetKind = trim(params.TargetKind)
	params.TargetRef = trim(params.TargetRef)
	params.DeliveryScopeID = trim(params.DeliveryScopeID)
	params.PermissionCheckScopeID = trim(params.PermissionCheckScopeID)
	params.MessageTemplate = trim(params.MessageTemplate)
	params.NotificationMode = normalizeNotificationMode(params.NotificationMode)
	params.Now = utcOrNow(params.Now)
	status, err := normalizeStatus(params.Status)
	if err != nil {
		return DefinitionUpdate{}, err
	}
	params.Status = status
	if params.WorkspaceID == "" || params.ID == "" || params.Name == "" || params.TargetKind == "" || params.TargetRef == "" {
		return DefinitionUpdate{}, badRequest("schedule definition id, workspace, name, and target are required")
	}
	if err := validateSource(params.SourceKind, params.SourceType, params.ScheduledAt, params.RelativeSeconds, params.AnchorAt, params.IntervalSeconds, params.CronExpression, params.Timezone); err != nil {
		return DefinitionUpdate{}, err
	}
	if err := validateTargetKind(params.TargetKind); err != nil {
		return DefinitionUpdate{}, err
	}
	if err := validateTargetFields(params.TargetKind, params.DeliveryScopeID, params.PermissionCheckScopeID, params.MessageTemplate); err != nil {
		return DefinitionUpdate{}, err
	}
	params = canonicalizeUpdateTimeSource(params)
	nextOccurrenceAt, err := initialOccurrenceForDefinition(Definition{
		ID:              params.ID,
		SourceKind:      params.SourceKind,
		SourceType:      params.SourceType,
		ScheduledAt:     params.ScheduledAt,
		RelativeSeconds: params.RelativeSeconds,
		AnchorAt:        params.AnchorAt,
		IntervalSeconds: params.IntervalSeconds,
		CronExpression:  params.CronExpression,
		Timezone:        params.Timezone,
	}, params.Now)
	if err != nil {
		return DefinitionUpdate{}, badRequest(err.Error())
	}
	if params.NextOccurrenceAt == nil || (params.Status == DefinitionStatusActive && isOneShotSource(params.SourceType)) {
		params.NextOccurrenceAt = nextOccurrenceAt
	}
	return params, nil
}

func canonicalizeCreateTimeSource(params DefinitionCreate) DefinitionCreate {
	switch params.SourceType {
	case SourceTypeOneShotAbsolute:
		params.RelativeSeconds = nil
		params.AnchorAt = nil
		params.IntervalSeconds = nil
		params.CronExpression = ""
		params.Timezone = ""
	case SourceTypeOneShotRelative:
		if params.RelativeSeconds != nil {
			scheduledAt := params.Now.Add(time.Duration(*params.RelativeSeconds) * time.Second).UTC()
			params.ScheduledAt = &scheduledAt
		}
		params.AnchorAt = nil
		params.IntervalSeconds = nil
		params.CronExpression = ""
		params.Timezone = ""
	case SourceTypeInterval:
		params.ScheduledAt = nil
		params.RelativeSeconds = nil
		params.CronExpression = ""
		params.Timezone = ""
	case SourceTypeCron:
		params.ScheduledAt = nil
		params.RelativeSeconds = nil
		params.AnchorAt = nil
		params.IntervalSeconds = nil
	}
	return params
}

func canonicalizeUpdateTimeSource(params DefinitionUpdate) DefinitionUpdate {
	switch params.SourceType {
	case SourceTypeOneShotAbsolute:
		params.RelativeSeconds = nil
		params.AnchorAt = nil
		params.IntervalSeconds = nil
		params.CronExpression = ""
		params.Timezone = ""
	case SourceTypeOneShotRelative:
		if params.RelativeSeconds != nil {
			scheduledAt := params.Now.Add(time.Duration(*params.RelativeSeconds) * time.Second).UTC()
			params.ScheduledAt = &scheduledAt
		}
		params.AnchorAt = nil
		params.IntervalSeconds = nil
		params.CronExpression = ""
		params.Timezone = ""
	case SourceTypeInterval:
		params.ScheduledAt = nil
		params.RelativeSeconds = nil
		params.CronExpression = ""
		params.Timezone = ""
	case SourceTypeCron:
		params.ScheduledAt = nil
		params.RelativeSeconds = nil
		params.AnchorAt = nil
		params.IntervalSeconds = nil
	}
	return params
}

func normalizeStatus(value string) (string, error) {
	status := trim(value)
	if status == "" {
		return DefinitionStatusActive, nil
	}
	switch status {
	case DefinitionStatusActive, DefinitionStatusPaused, DefinitionStatusBlocked, DefinitionStatusCompleted:
		return status, nil
	default:
		return "", badRequest("invalid schedule definition status")
	}
}

func normalizeSourceKind(value string) string {
	kind := trim(value)
	if kind == "" {
		return SourceKindTime
	}
	return kind
}

func normalizeNotificationMode(value string) string {
	mode := trim(value)
	if mode == "" {
		return NotificationModeNone
	}
	return mode
}

func validateSource(sourceKind string, sourceType string, scheduledAt *time.Time, relativeSeconds *int64, anchorAt *time.Time, intervalSeconds *int64, cronExpression string, timezone string) error {
	if sourceKind != SourceKindTime {
		return badRequest("schedule definitions require time source kind")
	}
	switch sourceType {
	case SourceTypeOneShotAbsolute:
		if scheduledAt == nil {
			return badRequest("one-shot absolute schedule requires scheduledAt")
		}
	case SourceTypeOneShotRelative:
		if relativeSeconds == nil || *relativeSeconds <= 0 {
			return badRequest("one-shot relative schedule requires positive relativeSeconds")
		}
	case SourceTypeInterval:
		if anchorAt == nil || intervalSeconds == nil || *intervalSeconds <= 0 {
			return badRequest("interval schedule requires anchorAt and positive intervalSeconds")
		}
	case SourceTypeCron:
		if cronExpression == "" || timezone == "" {
			return badRequest("cron schedule requires cronExpression and timezone")
		}
	default:
		return badRequest("invalid schedule source type")
	}
	return nil
}

func validateTargetKind(targetKind string) error {
	switch targetKind {
	case TargetKindChannel, TargetKindReplyThread, TargetKindDM, TargetKindTask:
		return nil
	default:
		return badRequest("invalid schedule target kind")
	}
}

func validateTargetFields(targetKind string, deliveryScopeID string, permissionCheckScopeID string, messageTemplate string) error {
	if trim(deliveryScopeID) == "" || trim(permissionCheckScopeID) == "" || trim(messageTemplate) == "" {
		return badRequest("schedule definition message targets require delivery scope, permission scope, and message template")
	}
	return nil
}

func definitionSelectSQL() string {
	return `
SELECT id, workspace_id, owner_member_id, principal_member_id,
       name, status, source_kind, source_type, scheduled_at, relative_seconds,
       anchor_at, interval_seconds, COALESCE(cron_expression, ''), COALESCE(timezone, ''),
       target_kind, target_ref, COALESCE(delivery_scope_id, ''), COALESCE(permission_check_scope_id, ''),
       target_snapshot, COALESCE(message_template, ''), notification_mode, next_occurrence_at,
       last_run_at, last_success_at, last_failure_at, last_failure_code,
       last_failure_detail, paused_at, paused_reason, deleted_at,
       COALESCE(idempotency_key, ''), created_by_member_id, created_by_actor_type,
       created_by_actor_id, created_via, metadata, created_at, updated_at
FROM schedule_definitions`
}

func scanDefinition(row coredb.Row) (Definition, error) {
	var definition Definition
	var scheduledAt, anchorAt, nextOccurrenceAt, lastRunAt, lastSuccessAt, lastFailureAt, pausedAt, deletedAt sql.NullTime
	var relativeSeconds, intervalSeconds sql.NullInt64
	var targetSnapshot, metadata []byte
	if err := row.Scan(
		&definition.ID,
		&definition.WorkspaceID,
		&definition.OwnerMemberID,
		&definition.PrincipalMemberID,
		&definition.Name,
		&definition.Status,
		&definition.SourceKind,
		&definition.SourceType,
		&scheduledAt,
		&relativeSeconds,
		&anchorAt,
		&intervalSeconds,
		&definition.CronExpression,
		&definition.Timezone,
		&definition.TargetKind,
		&definition.TargetRef,
		&definition.DeliveryScopeID,
		&definition.PermissionCheckScopeID,
		&targetSnapshot,
		&definition.MessageTemplate,
		&definition.NotificationMode,
		&nextOccurrenceAt,
		&lastRunAt,
		&lastSuccessAt,
		&lastFailureAt,
		&definition.LastFailureCode,
		&definition.LastFailureDetail,
		&pausedAt,
		&definition.PausedReason,
		&deletedAt,
		&definition.IdempotencyKey,
		&definition.CreatedByMemberID,
		&definition.CreatedByActorType,
		&definition.CreatedByActorID,
		&definition.CreatedVia,
		&metadata,
		&definition.CreatedAt,
		&definition.UpdatedAt,
	); err != nil {
		return Definition{}, err
	}
	definition.ID = trim(definition.ID)
	definition.WorkspaceID = trim(definition.WorkspaceID)
	definition.OwnerMemberID = trim(definition.OwnerMemberID)
	definition.PrincipalMemberID = trim(definition.PrincipalMemberID)
	definition.SourceKind = trim(definition.SourceKind)
	definition.SourceType = trim(definition.SourceType)
	definition.TargetKind = trim(definition.TargetKind)
	definition.TargetRef = trim(definition.TargetRef)
	definition.Status = trim(definition.Status)
	definition.ScheduledAt = ptrFromNullTime(scheduledAt)
	definition.RelativeSeconds = ptrFromNullInt64(relativeSeconds)
	definition.AnchorAt = ptrFromNullTime(anchorAt)
	definition.IntervalSeconds = ptrFromNullInt64(intervalSeconds)
	definition.NextOccurrenceAt = ptrFromNullTime(nextOccurrenceAt)
	definition.LastRunAt = ptrFromNullTime(lastRunAt)
	definition.LastSuccessAt = ptrFromNullTime(lastSuccessAt)
	definition.LastFailureAt = ptrFromNullTime(lastFailureAt)
	definition.PausedAt = ptrFromNullTime(pausedAt)
	definition.DeletedAt = ptrFromNullTime(deletedAt)
	definition.TargetSnapshot = targetSnapshot
	definition.Metadata = metadata
	return definition, nil
}

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

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

func stringInCondition(column string, values []string, args *[]any) string {
	placeholders := make([]string, 0, len(values))
	seen := make(map[string]struct{}, len(values))
	for _, value := range values {
		trimmed := trim(value)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		*args = append(*args, trimmed)
		placeholders = append(placeholders, fmt.Sprintf("$%d", len(*args)))
	}
	if len(placeholders) == 0 {
		return "1 = 0"
	}
	return fmt.Sprintf("%s IN (%s)", column, strings.Join(placeholders, ", "))
}

func mapDefinitionWriteError(err error) error {
	if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
		switch constraint {
		case "schedule_definitions_idempotency_key":
			return conflict("Schedule definition idempotency key already exists.")
		}
	}
	if err != nil {
		return err
	}
	return storeerr.ErrConflict
}