package packs

import (
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
)

// TestSatelliteOrphanCascadeQueryScoping locks the write-side contract that
// mirrors controlplane.VerifyActionTableConsistency: the three customizable
// satellites preserve admin-customized (IsCustomized=1) orphans by scoping the
// cascade to IsCustomized=0, while the L1-only internal-access-scope table is
// swept in full. A regression either way would diverge the reconcile cascade
// from the verify gate and re-open the orphan/false-fail gap.
func TestSatelliteOrphanCascadeQueryScoping(t *testing.T) {
	if len(standardActionSatelliteTables) != 4 {
		t.Fatalf("expected 4 standard-action satellite tables, got %d", len(standardActionSatelliteTables))
	}

	customizedScoped := 0
	for _, sat := range standardActionSatelliteTables {
		q := satelliteOrphanCascadeQuery(sat.name, sat.customizable)

		hasCustomized := strings.Contains(q, "COALESCE(IsCustomized, 0) = 0")
		switch {
		case sat.customizable && !hasCustomized:
			t.Errorf("%s: customizable satellite must scope cascade to IsCustomized=0 to preserve admin orphans", sat.name)
		case !sat.customizable && hasCustomized:
			t.Errorf("%s: L1-only satellite must not scope cascade by IsCustomized", sat.name)
		}
		if hasCustomized {
			customizedScoped++
		}

		if !strings.Contains(q, "NOT EXISTS (SELECT 1 FROM `"+tableStandardAction+"`") {
			t.Errorf("%s: cascade must gate on a live _System_StandardAction parent", sat.name)
		}
		if !strings.HasPrefix(q, "UPDATE `"+sat.name+"` SET IsActive = 0, IsDeleted = 1") {
			t.Errorf("%s: cascade must soft-delete (IsActive=0, IsDeleted=1), got %q", sat.name, q)
		}
	}

	if customizedScoped != 3 {
		t.Fatalf("expected exactly 3 customizable satellites scoped by IsCustomized, got %d", customizedScoped)
	}

	// The L1-only table is the access-scope table; assert by name so a future
	// L1Only flip on a customizable table is caught here too.
	for _, sat := range standardActionSatelliteTables {
		if sat.name == tableStandardActionInternalAccessScope && sat.customizable {
			t.Fatalf("%s is L1-only and must not be marked customizable", sat.name)
		}
	}
}

// TestSatelliteOrphanCascadeKeysOnDeletionNotVisibility locks that the cascade
// treats parent presence (IsDeleted), not visibility (IsActive), as the
// liveness signal. _System_StandardAction.IsActive=0 is an admin
// disable-without-delete toggle preserved across pack updates, so a
// disabled-but-present parent must keep its satellites. Regression guard for
// the data-loss bug where disabling a standard action soft-deleted its schema /
// field-default / guard / access-scope metadata.
func TestSatelliteOrphanCascadeKeysOnDeletionNotVisibility(t *testing.T) {
	for _, sat := range standardActionSatelliteTables {
		q := satelliteOrphanCascadeQuery(sat.name, sat.customizable)
		idx := strings.Index(q, "NOT EXISTS")
		if idx < 0 {
			t.Fatalf("%s: cascade query missing parent NOT EXISTS clause: %q", sat.name, q)
		}
		parentClause := q[idx:]
		if !strings.Contains(parentClause, "a.IsDeleted") {
			t.Errorf("%s: cascade must gate parent on IsDeleted, got %q", sat.name, parentClause)
		}
		if strings.Contains(parentClause, "a.IsActive") {
			t.Errorf("%s: cascade must NOT gate parent on IsActive (admin disable != delete), got %q", sat.name, parentClause)
		}
	}
}

// TestCascadeDeactivateOrphanedStandardActionSatellitesRunsEveryTable verifies
// the exec path issues one soft-delete UPDATE per satellite table, bound to the
// shared `now` arg, and sums RowsAffected across them.
func TestCascadeDeactivateOrphanedStandardActionSatellitesRunsEveryTable(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	const now = "2026-05-25 00:00:00"
	for _, sat := range standardActionSatelliteTables {
		mock.ExpectExec(regexp.QuoteMeta("UPDATE `" + sat.name + "`")).
			WithArgs(now).
			WillReturnResult(sqlmock.NewResult(0, 1))
	}

	got, err := cascadeDeactivateOrphanedStandardActionSatellites(db, now)
	if err != nil {
		t.Fatalf("cascadeDeactivateOrphanedStandardActionSatellites() error = %v", err)
	}
	if want := len(standardActionSatelliteTables); got != want {
		t.Fatalf("rows deactivated = %d, want %d (one per satellite table)", got, want)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}