package app

import (
	"context"
	"fmt"
	"strings"
	"time"

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

const (
	serviceEventRetentionDeleteBatchDefault = 500
	serviceEventRetentionDeleteBatchMax     = 1000
)

// DeletePullRequestSubscriptionRateLimitEventsBefore deletes old pull-request
// subscription control-plane rate-limit log rows in bounded batches. The
// physical table is a canonical retention ledger owned by pull-request
// subscription lifecycle code.
func (store *Store) DeletePullRequestSubscriptionRateLimitEventsBefore(ctx context.Context, cutoff time.Time, limit int) (int64, error) {
	return deleteServiceEventRowsBefore(ctx, store.db, serviceEventRetentionDeleteQuery{
		SelectSQL: `
SELECT id
FROM service_action_rate_limit_events
WHERE created_at < $1
ORDER BY created_at ASC, id ASC
LIMIT $2
`,
		DeleteTable: "service_action_rate_limit_events",
		Cutoff:      cutoff,
		Limit:       limit,
		Label:       "pull request subscription rate limit events",
	})
}

// DeleteServiceEventWebhookDeliveriesBefore deletes old service-event webhook
// delivery logs in bounded batches while preserving recent dedupe/audit history.
func (store *Store) DeleteServiceEventWebhookDeliveriesBefore(ctx context.Context, cutoff time.Time, limit int) (int64, error) {
	return deleteServiceEventRowsBefore(ctx, store.db, serviceEventRetentionDeleteQuery{
		SelectSQL: `
SELECT id
FROM service_webhook_receipts
WHERE created_at < $1
ORDER BY created_at ASC, id ASC
LIMIT $2
`,
		DeleteTable: "service_webhook_receipts",
		Cutoff:      cutoff,
		Limit:       limit,
		Label:       "service event webhook deliveries",
	})
}

type serviceEventRetentionDeleteQuery struct {
	SelectSQL   string
	DeleteTable string
	Cutoff      time.Time
	Limit       int
	Label       string
}

func deleteServiceEventRowsBefore(ctx context.Context, db coredb.QueryExecer, query serviceEventRetentionDeleteQuery) (int64, error) {
	if db == nil {
		return 0, nil
	}
	limit := normalizeServiceEventRetentionDeleteLimit(query.Limit)
	rows, err := db.Query(ctx, query.SelectSQL, query.Cutoff.UTC(), limit)
	if err != nil {
		return 0, fmt.Errorf("select old %s: %w", query.Label, err)
	}
	defer rows.Close()

	ids := make([]string, 0, limit)
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return 0, fmt.Errorf("scan old %s: %w", query.Label, err)
		}
		if trimmed := strings.TrimSpace(id); trimmed != "" {
			ids = append(ids, trimmed)
		}
	}
	if err := rows.Err(); err != nil {
		return 0, fmt.Errorf("iterate old %s: %w", query.Label, err)
	}

	if len(ids) == 0 {
		return 0, nil
	}
	placeholders, args := serviceEventRetentionIDPlaceholders(ids)
	deleteSQL := fmt.Sprintf("DELETE FROM %s WHERE id IN (%s)", query.DeleteTable, placeholders)
	result, err := db.Exec(ctx, deleteSQL, args...)
	if err != nil {
		return 0, fmt.Errorf("delete old %s: %w", query.Label, err)
	}
	return result.RowsAffected(), nil
}

func normalizeServiceEventRetentionDeleteLimit(limit int) int {
	if limit <= 0 {
		return serviceEventRetentionDeleteBatchDefault
	}
	if limit > serviceEventRetentionDeleteBatchMax {
		return serviceEventRetentionDeleteBatchMax
	}
	return limit
}

func serviceEventRetentionIDPlaceholders(ids []string) (string, []any) {
	placeholders := make([]string, len(ids))
	args := make([]any, len(ids))
	for index, id := range ids {
		placeholders[index] = fmt.Sprintf("$%d", index+1)
		args[index] = id
	}
	return strings.Join(placeholders, ", "), args
}