package controlplane

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

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
)

const migrationIdBackfillWebhookSecretKeyReadProtection = "20260614_backfill_webhook_secretkey_read_protection"

// BackfillWebhookSecretKeyReadProtection marks the legacy _System_Webhook.SecretKey
// metadata row blocked for generic reads. Current bootstrap metadata uses
// AuthConfig, but warm-upgraded orgs can still carry the old SecretKey field.
func BackfillWebhookSecretKeyReadProtection(ctx context.Context, db *sql.DB) (int64, error) {
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdBackfillWebhookSecretKeyReadProtection)
	if err != nil {
		return 0, err
	}
	if applied {
		log.Printf("   ⏭  BackfillWebhookSecretKeyReadProtection: already applied; skipping")
		return 0, nil
	}

	result, err := db.ExecContext(ctx, webhookSecretKeyReadProtectionUpdateSQL)
	if err != nil {
		return 0, fmt.Errorf("BackfillWebhookSecretKeyReadProtection: %w", err)
	}
	touched, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("BackfillWebhookSecretKeyReadProtection rows affected: %w", err)
	}

	remaining, err := webhookSecretKeyReadProtectionRemaining(ctx, db)
	if err != nil {
		return touched, err
	}
	if remaining > 0 {
		return touched, fmt.Errorf("BackfillWebhookSecretKeyReadProtection: %d legacy _System_Webhook.SecretKey row(s) remain without readProtectionPolicy=blocked", remaining)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx,
		db,
		migrationIdBackfillWebhookSecretKeyReadProtection,
		1,
		"Backfill readProtectionPolicy=blocked for legacy _System_Webhook.SecretKey field metadata before removing the generic sensitive-field fallback.",
	); err != nil {
		return touched, err
	}
	if touched > 0 {
		log.Printf("   🧼 BackfillWebhookSecretKeyReadProtection: protected %d legacy webhook SecretKey field row(s)", touched)
	}
	return touched, nil
}

func webhookSecretKeyReadProtectionRemaining(ctx context.Context, db *sql.DB) (int, error) {
	var remaining int
	if err := db.QueryRowContext(ctx, webhookSecretKeyReadProtectionRemainingSQL).Scan(&remaining); err != nil {
		return 0, fmt.Errorf("BackfillWebhookSecretKeyReadProtection closure check: %w", err)
	}
	return remaining, nil
}

const webhookSecretKeyReadProtectionUpdateSQL = `
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.ReadProtectionPolicy = 'blocked'
WHERE o.ApiName = '_System_Webhook'
  AND f.ApiName = 'SecretKey'
  AND COALESCE(o.IsDeleted, 0) = 0
  AND COALESCE(f.IsDeleted, 0) = 0
  AND (f.ReadProtectionPolicy IS NULL OR TRIM(f.ReadProtectionPolicy) = '')
`

const webhookSecretKeyReadProtectionRemainingSQL = `
SELECT COUNT(*)
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE o.ApiName = '_System_Webhook'
  AND f.ApiName = 'SecretKey'
  AND COALESCE(o.IsDeleted, 0) = 0
  AND COALESCE(f.IsDeleted, 0) = 0
  AND NOT LOWER(TRIM(COALESCE(f.ReadProtectionPolicy, ''))) = 'blocked'
`