package serviceaccount

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

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

const defaultPlatformDeletionDeliveryMaxBackoff = 5 * time.Minute

// InsertPlatformDeletionDelivery persists a deletion snapshot in the transaction that
// removes its source account. No source identifier is validated through a
// lookup or foreign key because the delivery must outlive those rows.
func (store *TxStore) InsertPlatformDeletionDelivery(ctx context.Context, input PlatformDeletionDeliveryInsert) error {
	execer, err := txExecer(store)
	if err != nil {
		return err
	}
	input.ID = trim(input.ID)
	input.WorkspaceID = trim(input.WorkspaceID)
	input.PlatformServiceAccountID = trim(input.PlatformServiceAccountID)
	input.LocalServiceAccountID = trim(input.LocalServiceAccountID)
	input.LocalServiceAccountPrincipalID = trim(input.LocalServiceAccountPrincipalID)
	input.MessageScopeID = trim(input.MessageScopeID)
	if input.ID == "" || input.WorkspaceID == "" || input.PlatformServiceAccountID == "" ||
		input.LocalServiceAccountID == "" || input.LocalServiceAccountPrincipalID == "" || input.MessageScopeID == "" {
		return badRequest("platform service account deletion delivery identity is required")
	}
	now := utcOrNow(input.Now)
	if _, err := execer.Exec(ctx, `
INSERT INTO platform_service_account_deletion_outbox (
    id, workspace_id, platform_service_account_id, local_service_account_id,
    local_service_account_principal_id, message_scope_id, attempt_count,
    next_attempt_at, lease_token, lease_expires_at, last_error, created_at, updated_at
) VALUES (
    $1, $2, $3, $4,
    $5, $6, 0,
    $7, NULL, NULL, '', $7, $7
)
`, input.ID, input.WorkspaceID, input.PlatformServiceAccountID, input.LocalServiceAccountID,
		input.LocalServiceAccountPrincipalID, input.MessageScopeID, now); err != nil {
		return fmt.Errorf("insert platform service account deletion delivery: %w", mapAccountWriteError(err))
	}
	return nil
}

// ListDuePlatformDeletionDeliveries returns a stable id-ordered routing page. Leasing
// performs the authoritative due check, so a concurrent worker may safely race
// over the same candidate page.
func (store *Store) ListDuePlatformDeletionDeliveries(ctx context.Context, now time.Time, afterID string, limit int) ([]PlatformDeletionDeliveryCandidate, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	if limit <= 0 || limit > 500 {
		return nil, badRequest("limit must be between 1 and 500")
	}
	now = utcOrNow(now)
	rows, err := q.Query(ctx, `
SELECT id, workspace_id
FROM platform_service_account_deletion_outbox
WHERE id > $1
  AND next_attempt_at <= $2
  AND (lease_token IS NULL OR lease_expires_at <= $2)
ORDER BY id ASC
LIMIT $3
`, trim(afterID), now, limit)
	if err != nil {
		return nil, fmt.Errorf("list due platform service account deletion deliveries: %w", err)
	}
	defer rows.Close()
	candidates := make([]PlatformDeletionDeliveryCandidate, 0, limit)
	for rows.Next() {
		var candidate PlatformDeletionDeliveryCandidate
		if err := rows.Scan(&candidate.ID, &candidate.WorkspaceID); err != nil {
			return nil, fmt.Errorf("scan due platform service account deletion delivery: %w", err)
		}
		candidates = append(candidates, candidate)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate due platform service account deletion deliveries: %w", err)
	}
	return candidates, nil
}

// LeasePlatformDeletionDelivery atomically claims one due or expired delivery and
// returns its complete immutable deletion snapshot. Every successful lease is
// an attempt, including a lease recovered after a worker crash.
func (store *Store) LeasePlatformDeletionDelivery(ctx context.Context, id string, now time.Time, leaseTTL time.Duration) (PlatformDeletionDelivery, bool, error) {
	if err := store.Validate(); err != nil {
		return PlatformDeletionDelivery{}, false, err
	}
	id = trim(id)
	if id == "" {
		return PlatformDeletionDelivery{}, false, badRequest("platform service account deletion delivery id is required")
	}
	if leaseTTL <= 0 {
		return PlatformDeletionDelivery{}, false, badRequest("platform service account deletion delivery lease TTL must be positive")
	}
	now = utcOrNow(now)
	leaseToken, err := identifiers.NewEntityID("sadlease")
	if err != nil {
		return PlatformDeletionDelivery{}, false, fmt.Errorf("generate platform service account deletion delivery lease: %w", err)
	}
	leaseExpiresAt := now.Add(leaseTTL)
	var delivery PlatformDeletionDelivery
	var leased bool
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		result, err := tx.Exec(ctx, `
UPDATE platform_service_account_deletion_outbox
SET attempt_count = attempt_count + 1,
    next_attempt_at = $3,
    lease_token = $2,
    lease_expires_at = $3,
    last_error = '',
    updated_at = $4
WHERE id = $1
  AND next_attempt_at <= $4
  AND (lease_token IS NULL OR lease_expires_at <= $4)
`, id, leaseToken, leaseExpiresAt, now)
		if err != nil {
			return fmt.Errorf("lease platform service account deletion delivery: %w", err)
		}
		if result.RowsAffected() == 0 {
			return nil
		}
		delivery, err = loadPlatformDeletionDelivery(ctx, tx, id, leaseToken, "")
		if err != nil {
			return err
		}
		leased = true
		return nil
	})
	if err != nil {
		return PlatformDeletionDelivery{}, false, err
	}
	return delivery, leased, nil
}

// MarkPlatformDeletionDeliveryDelivered acknowledges a fenced lease by physically
// deleting its outbox row. There is intentionally no delivered-history state.
func (store *Store) MarkPlatformDeletionDeliveryDelivered(ctx context.Context, id string, leaseToken string, now time.Time) error {
	execer, err := execer(store)
	if err != nil {
		return err
	}
	id = trim(id)
	leaseToken = trim(leaseToken)
	if id == "" || leaseToken == "" {
		return badRequest("platform service account deletion delivery id and lease token are required")
	}
	_ = utcOrNow(now)
	result, err := execer.Exec(ctx, `
DELETE FROM platform_service_account_deletion_outbox
WHERE id = $1 AND lease_token = $2
`, id, leaseToken)
	if err != nil {
		return fmt.Errorf("delete delivered platform service account deletion delivery: %w", err)
	}
	if result.RowsAffected() == 0 {
		return conflict("platform service account deletion delivery lease changed")
	}
	return nil
}

// MarkPlatformDeletionDeliveryFailed releases a fenced lease and schedules an
// unbounded retry with quadratic delay capped by maxBackoff. Durable runtime
// invalidation has no terminal exhausted state: it remains pending until it is
// delivered or its workspace is deleted.
func (store *Store) MarkPlatformDeletionDeliveryFailed(ctx context.Context, id string, leaseToken string, cause error, now time.Time, maxBackoff time.Duration) error {
	if err := store.Validate(); err != nil {
		return err
	}
	id = trim(id)
	leaseToken = trim(leaseToken)
	if id == "" || leaseToken == "" {
		return badRequest("platform service account deletion delivery id and lease token are required")
	}
	now = utcOrNow(now)
	if maxBackoff <= 0 {
		maxBackoff = defaultPlatformDeletionDeliveryMaxBackoff
	}
	lastError := "platform service account deletion delivery failed"
	if cause != nil && trim(cause.Error()) != "" {
		lastError = trim(cause.Error())
	}
	if len(lastError) > 4000 {
		lastError = lastError[:4000]
	}
	return coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		delivery, err := loadPlatformDeletionDelivery(ctx, tx, id, leaseToken, "FOR UPDATE")
		if err != nil {
			if coredb.IsNotFound(err) {
				return conflict("platform service account deletion delivery lease changed")
			}
			return err
		}
		nextAttemptAt := now.Add(platformDeletionDeliveryRetryDelay(delivery.AttemptCount, maxBackoff))
		result, err := tx.Exec(ctx, `
UPDATE platform_service_account_deletion_outbox
SET next_attempt_at = $3,
    lease_token = NULL,
    lease_expires_at = NULL,
    last_error = $4,
    updated_at = $5
WHERE id = $1 AND lease_token = $2
`, id, leaseToken, nextAttemptAt, lastError, now)
		if err != nil {
			return fmt.Errorf("record platform service account deletion delivery failure: %w", err)
		}
		if result.RowsAffected() == 0 {
			return conflict("platform service account deletion delivery lease changed")
		}
		return nil
	})
}

func platformDeletionDeliveryRetryDelay(attemptCount int64, maxBackoff time.Duration) time.Duration {
	if maxBackoff <= 0 {
		maxBackoff = defaultPlatformDeletionDeliveryMaxBackoff
	}
	maxSeconds := int64(maxBackoff / time.Second)
	if maxSeconds <= 0 {
		return maxBackoff
	}
	if attemptCount <= 0 {
		attemptCount = 1
	}
	delaySeconds := maxSeconds
	if attemptCount <= maxSeconds/attemptCount {
		squared := attemptCount * attemptCount
		if squared < delaySeconds {
			delaySeconds = squared
		}
	}
	return time.Duration(delaySeconds) * time.Second
}

func loadPlatformDeletionDelivery(ctx context.Context, q coredb.QueryRower, id string, leaseToken string, suffix string) (PlatformDeletionDelivery, error) {
	delivery, err := scanPlatformDeletionDelivery(q.QueryRow(ctx, `
SELECT id, workspace_id, platform_service_account_id, local_service_account_id,
       local_service_account_principal_id, message_scope_id, attempt_count,
       next_attempt_at, COALESCE(lease_token, ''), lease_expires_at,
       last_error, created_at, updated_at
FROM platform_service_account_deletion_outbox
WHERE id = $1 AND lease_token = $2
`+suffix, trim(id), trim(leaseToken)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return PlatformDeletionDelivery{}, sql.ErrNoRows
		}
		return PlatformDeletionDelivery{}, fmt.Errorf("load platform service account deletion delivery: %w", err)
	}
	return delivery, nil
}

func scanPlatformDeletionDelivery(scanner interface{ Scan(...any) error }) (PlatformDeletionDelivery, error) {
	var delivery PlatformDeletionDelivery
	var leaseExpiresAt sql.NullTime
	if err := scanner.Scan(
		&delivery.ID,
		&delivery.WorkspaceID,
		&delivery.PlatformServiceAccountID,
		&delivery.LocalServiceAccountID,
		&delivery.LocalServiceAccountPrincipalID,
		&delivery.MessageScopeID,
		&delivery.AttemptCount,
		&delivery.NextAttemptAt,
		&delivery.LeaseToken,
		&leaseExpiresAt,
		&delivery.LastError,
		&delivery.CreatedAt,
		&delivery.UpdatedAt,
	); err != nil {
		return PlatformDeletionDelivery{}, err
	}
	if leaseExpiresAt.Valid {
		value := leaseExpiresAt.Time
		delivery.LeaseExpiresAt = &value
	}
	return delivery, nil
}