package packs

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"
)

// webhookSecretPackBatch carries the set of webhook secret Names declared by a
// single pack in the current manifest. It is populated during source-backed
// pack parsing and passed to the sweeper component so the apply-side sweep and
// verify-side assertion share the same declared set.
type webhookSecretPackBatch struct {
	// declaredNames is the set of webhook secret Name values declared by the pack.
	declaredNames []string
}

// newWebhookSecretPackSweeperComponent returns a synthetic pack-level install
// step that garbage-collects _System_WebhookSecret rows owned by packName whose
// Name is no longer declared in the current manifest.
//
// Without this sweeper, a pack that drops a webhook_secret component between
// versions leaves a stale row live: ResolveWebhookSecretForVerification still
// returns it, meaning an old — potentially compromised — secret continues to
// verify inbound payloads indefinitely. The sweeper closes that gap by
// soft-deleting orphan definition rows and marking their value rows inactive.
//
// The synthetic is folded into InstallPlan() and Verify() exclusively via
// SourceBackedPack.lifecycleClosureComponents() — it has no manifest business key,
// no payload, and does not participate in payload-hash computation.
func newWebhookSecretPackSweeperComponent(packName string, batch *webhookSecretPackBatch) PackComponent {
	manifestComponent := ManifestComponent{
		Kind:          ComponentKindWebhookSecret,
		BusinessKey:   JoinComponentBusinessKey(packName, "@sweeper"),
		Manageability: ManageabilityLocked,
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: manifestComponent,
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applyWebhookSecretPackSweeper(ctx, deps.DB, batch, packName)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifyWebhookSecretPackSweeper(ctx, deps.DB, batch, packName)
		},
	}
}

// applyWebhookSecretPackSweeper soft-deletes _System_WebhookSecret rows owned
// by ownerPack whose Name is not in the declared set, and marks their
// corresponding _System_WebhookSecretValue rows inactive. Value rows are NOT
// hard-deleted — leaving them as inactive allows an accidental drop+restore
// cycle to be repaired without losing the encrypted bytes.
func applyWebhookSecretPackSweeper(ctx context.Context, db *sql.DB, batch *webhookSecretPackBatch, ownerPack string) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}

	orphans, err := queryOrphanWebhookSecretRows(ctx, db, batch, ownerPack)
	if err != nil {
		return err
	}
	if len(orphans) == 0 {
		return nil
	}

	for _, orphanID := range orphans {
		// Soft-delete the definition row.
		if _, err := db.ExecContext(ctx, `
UPDATE _System_WebhookSecret
SET IsDeleted = 1, LastModifiedDate = NOW()
WHERE Id = ? AND COALESCE(IsDeleted, 0) = 0`, orphanID); err != nil {
			return fmt.Errorf("soft-delete orphan webhook secret %s for pack %s: %w", orphanID, ownerPack, err)
		}
		// Deactivate all value rows for this definition (don't hard-delete).
		if _, err := db.ExecContext(ctx, `
UPDATE _System_WebhookSecretValue
SET IsActive = 0, LastModifiedDate = NOW()
WHERE WebhookSecretId = ? AND IsActive = 1`, orphanID); err != nil {
			return fmt.Errorf("deactivate orphan webhook secret values for def %s pack %s: %w", orphanID, ownerPack, err)
		}
	}
	return nil
}

// verifyWebhookSecretPackSweeper asserts that no live _System_WebhookSecret
// row exists for ownerPack whose Name is not in the declared set. It is the
// verify-side complement to applyWebhookSecretPackSweeper.
func verifyWebhookSecretPackSweeper(ctx context.Context, db *sql.DB, batch *webhookSecretPackBatch, ownerPack string) error {
	if db == nil {
		return fmt.Errorf("database is required")
	}

	orphans, err := queryOrphanWebhookSecretRows(ctx, db, batch, ownerPack)
	if err != nil {
		return err
	}
	if len(orphans) == 0 {
		return nil
	}
	sort.Strings(orphans)
	return fmt.Errorf("pack %s has %d undeclared webhook_secret row(s) still live: %s",
		ownerPack, len(orphans), strings.Join(orphans, ", "))
}

// queryOrphanWebhookSecretRows returns the Ids of _System_WebhookSecret rows
// owned by ownerPack that are live (IsDeleted=0) but whose Name is NOT in the
// declared set of the current manifest.
func queryOrphanWebhookSecretRows(ctx context.Context, db *sql.DB, batch *webhookSecretPackBatch, ownerPack string) ([]string, error) {
	// Keys are lowercased to match TiDB's case-insensitive (OwnerPack, Name) UNIQUE collation.
	declaredSet := make(map[string]struct{})
	if batch != nil {
		for _, name := range batch.declaredNames {
			declaredSet[strings.ToLower(strings.TrimSpace(name))] = struct{}{}
		}
	}

	rows, err := db.QueryContext(ctx, `
SELECT Id, Name
FROM _System_WebhookSecret
WHERE OwnerPack = ? AND COALESCE(IsDeleted, 0) = 0`, ownerPack)
	if err != nil {
		return nil, fmt.Errorf("query live webhook_secret rows for pack sweep %s: %w", ownerPack, err)
	}
	defer rows.Close()

	var orphanIDs []string
	for rows.Next() {
		var id, name string
		if err := rows.Scan(&id, &name); err != nil {
			return nil, fmt.Errorf("scan webhook_secret row for pack sweep %s: %w", ownerPack, err)
		}
		if _, ok := declaredSet[strings.ToLower(strings.TrimSpace(name))]; ok {
			continue
		}
		orphanIDs = append(orphanIDs, id)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate webhook_secret rows for pack sweep %s: %w", ownerPack, err)
	}
	return orphanIDs, nil
}