package packs

import (
	"context"
	"database/sql"
	"regexp"
	"strings"
	"testing"
	"time"

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

func TestSourceBackedStandardFlowPackSweeperRetiresOnlyUndeclaredManagedRows(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectBegin()
	mock.ExpectQuery(regexp.QuoteMeta(`
SELECT fd.Id, fd.DeveloperName, fd.ActiveVersionId,
       active_version.Id, active_version.DefinitionId, active_version.Source,
       active_version.Status, COALESCE(active_version.IsDeleted, 0)
  FROM FlowDefinition fd
  LEFT JOIN Flow active_version ON active_version.Id = fd.ActiveVersionId
 WHERE COALESCE(fd.OwnerPack, '') = ?
   AND COALESCE(fd.IsDeleted, 0) = 0
   AND COALESCE(fd.IsCustomized, 0) = 0
 ORDER BY fd.DeveloperName, fd.Id FOR UPDATE`)).
		WithArgs("companion-pack").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "DeveloperName", "ActiveVersionId", "ActiveId", "DefinitionId", "Source", "Status", "IsDeleted"}).
			AddRow("flow-keep", "Current_Flow", "version-keep", "version-keep", "flow-keep", FlowSourceStandard, "Active", 0).
			AddRow("flow-retire", "Legacy_Flow", "version-retire", "version-retire", "flow-retire", FlowSourceStandard, "Active", 0))
	mock.ExpectExec(regexp.QuoteMeta(`
UPDATE FlowDefinition
   SET IsDeleted = 1, ActiveVersionId = NULL, LastModifiedDate = NOW()
 WHERE Id = ?
   AND COALESCE(OwnerPack, '') = ?
   AND COALESCE(IsDeleted, 0) = 0
   AND COALESCE(IsCustomized, 0) = 0
   AND (
       ActiveVersionId IS NULL
       OR EXISTS (
           SELECT 1
             FROM Flow
            WHERE Flow.Id = FlowDefinition.ActiveVersionId
              AND Flow.DefinitionId = FlowDefinition.Id
              AND COALESCE(Flow.IsDeleted, 0) = 0
              AND Flow.Status = 'Active'
              AND Flow.Source = ?
       )
   )`)).
		WithArgs("flow-retire", "companion-pack", FlowSourceStandard).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta(`UPDATE _System_TriggerSubscription SET IsActive = 0, LastModifiedDate = NOW() WHERE FlowDefinitionId = ?`)).
		WithArgs("flow-retire").
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	if err := applySourceBackedStandardFlowPackSweep(context.Background(), db, "companion-pack", []string{"current_flow"}); err != nil {
		t.Fatalf("applySourceBackedStandardFlowPackSweep: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func TestSourceBackedStandardFlowPackSweeperVerifyRejectsUndeclaredManagedRows(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)SELECT fd\\.Id, fd\\.DeveloperName.*FROM FlowDefinition").
		WithArgs("companion-pack").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "DeveloperName", "ActiveVersionId", "ActiveId", "DefinitionId", "Source", "Status", "IsDeleted"}).
			AddRow("flow-keep", "Current_Flow", "version-keep", "version-keep", "flow-keep", FlowSourceStandard, "Active", 0).
			AddRow("flow-retire", "Legacy_Flow", "version-retire", "version-retire", "flow-retire", FlowSourceStandard, "Active", 0))

	err = verifySourceBackedStandardFlowPackSweeper(context.Background(), db, "companion-pack", []string{"Current_Flow"})
	if err == nil || !strings.Contains(err.Error(), "undeclared standard_flow") || !strings.Contains(err.Error(), "Legacy_Flow") {
		t.Fatalf("verify error = %v, want Legacy_Flow undeclared rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func TestSourceBackedStandardFlowPackSweeperPreservesCustomizedRows(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)SELECT fd\\.Id, fd\\.DeveloperName.*FROM FlowDefinition").
		WithArgs("companion-pack").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "DeveloperName", "ActiveVersionId", "ActiveId", "DefinitionId", "Source", "Status", "IsDeleted"}))

	if err := verifySourceBackedStandardFlowPackSweeper(context.Background(), db, "companion-pack", nil); err != nil {
		t.Fatalf("customized rows are excluded by the ownership query: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func TestSourceBackedStandardFlowPackSweeperRejectsBrokenActivePointer(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)SELECT fd\\.Id, fd\\.DeveloperName.*FROM FlowDefinition").
		WithArgs("companion-pack").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "DeveloperName", "ActiveVersionId", "ActiveId", "DefinitionId", "Source", "Status", "IsDeleted"}).
			AddRow("flow-broken", "Broken_Flow", "missing-version", nil, nil, nil, nil, 0))

	err = verifySourceBackedStandardFlowPackSweeper(context.Background(), db, "companion-pack", nil)
	if err == nil || !strings.Contains(err.Error(), "missing active Flow missing-version") {
		t.Fatalf("verify error = %v, want broken active pointer rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func TestSourceBackedStandardFlowPackSweeperRejectsCrossDefinitionActivePointer(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock.New: %v", err)
	}
	defer db.Close()

	mock.ExpectQuery("(?s)SELECT fd\\.Id, fd\\.DeveloperName.*FROM FlowDefinition").
		WithArgs("companion-pack").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "DeveloperName", "ActiveVersionId", "ActiveId", "DefinitionId", "Source", "Status", "IsDeleted"}).
			AddRow("flow-broken", "Broken_Flow", "foreign-version", "foreign-version", "other-definition", FlowSourceStandard, "Active", 0))

	err = verifySourceBackedStandardFlowPackSweeper(context.Background(), db, "companion-pack", nil)
	if err == nil || !strings.Contains(err.Error(), "owned by definition \"other-definition\"") {
		t.Fatalf("verify error = %v, want cross-definition active pointer rejection", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet SQL expectations: %v", err)
	}
}

func TestSourceBackedPackInstallPlanIncludesStandardFlowSweeperWithoutDeclarations(t *testing.T) {
	source := &ResolvedPackSource{
		Kind: PackSourceKindLocalPath,
		URI:  "/tmp/companion-pack",
		Manifest: &PackManifest{
			ManifestFormatVersion: SupportedManifestFormatVersion,
			Name:                  "companion-pack",
			DisplayName:           "Companion Pack",
			Category:              AppPack,
			Version:               "0.1.0",
			PlatformCompatibility: PlatformCompatibility{Min: "0.1.0", MaxExclusive: "0.2.0"},
			Components: []ManifestComponent{
				testObjectSpecManifestComponent("issue", "Issue"),
			},
		},
	}
	pack, err := NewSourceBackedPack(source)
	if err != nil {
		t.Fatalf("NewSourceBackedPack: %v", err)
	}
	for _, component := range pack.Components() {
		if component.Kind() == ComponentKindStandardFlow {
			t.Fatalf("Components includes synthetic standard_flow %q", component.BusinessKey())
		}
	}

	plan, err := pack.InstallPlan()
	if err != nil {
		t.Fatalf("InstallPlan: %v", err)
	}
	var sweepers []PackComponent
	for _, component := range plan.ComponentsForPhase(InstallPlanPhaseAutomation) {
		if component.Kind() == ComponentKindStandardFlow {
			sweepers = append(sweepers, component)
		}
	}
	if len(sweepers) != 1 || sweepers[0].BusinessKey() != "_pack_sweep" {
		t.Fatalf("standard_flow sweepers = %#v, want one hidden pack sweeper", sweepers)
	}
	grouped, ok := sweepers[0].(componentApplyGroupProvider)
	if !ok || grouped.ApplyGroupKey() != string(ComponentKindStandardFlow)+"_pack_sweep" {
		t.Fatalf("standard_flow sweeper group = %v, want independent post-apply group", grouped)
	}
}

func TestSourceBackedStandardFlowPackSweeperTiDBLifecycle(t *testing.T) {
	ctx := context.Background()
	db, cleanup := openFlowRuntimeMaintenanceTestDB(t)
	defer cleanup()

	now := time.Now().Format(time.RFC3339)
	definitions := []struct {
		id, developerName, versionID, source string
		customized                           bool
	}{
		{id: "flowdef-current", developerName: "Current_Flow", versionID: "flowver-current", source: FlowSourceStandard},
		{id: "flowdef-legacy", developerName: "Legacy_Flow", versionID: "flowver-legacy", source: FlowSourceStandard},
		{id: "flowdef-customized", developerName: "Customized_Legacy_Flow", versionID: "flowver-customized", source: FlowSourceStandard, customized: true},
		{id: "flowdef-custom-active", developerName: "Custom_Active_Legacy_Flow", versionID: "flowver-custom-active", source: FlowSourceCustom},
	}
	for _, definition := range definitions {
		if _, err := db.ExecContext(ctx, `INSERT INTO FlowDefinition
            (Id, DeveloperName, MasterLabel, Description, ProcessType, TriggerType, TriggerObjectOrEvent,
             ActiveVersionId, LatestVersionId, IsDeleted, IsCustomized, OwnerPack, CreatedDate, LastModifiedDate)
            VALUES (?, ?, ?, '', 'AutoLaunchedFlow', '', '', ?, ?, 0, ?, 'companion-pack', ?, ?)`,
			definition.id, definition.developerName, definition.developerName, definition.versionID, definition.versionID, definition.customized, now, now); err != nil {
			t.Fatalf("seed FlowDefinition %s: %v", definition.developerName, err)
		}
		metadata := `{"label":"` + definition.developerName + `","processType":"AutoLaunchedFlow"}`
		if _, err := db.ExecContext(ctx, `INSERT INTO Flow
            (Id, DefinitionId, MasterLabel, VersionNumber, Status, Description, ProcessType, Metadata, Source, IsDeleted, CreatedDate, LastModifiedDate)
			VALUES (?, ?, ?, 1, 'Active', '', 'AutoLaunchedFlow', ?, ?, 0, ?, ?)`,
			definition.versionID, definition.id, definition.developerName, metadata, definition.source, now, now); err != nil {
			t.Fatalf("seed Flow %s: %v", definition.developerName, err)
		}
		if _, err := db.ExecContext(ctx, `INSERT INTO _System_TriggerSubscription
            (Id, KeyPrefix, EventType, RecordTriggerType, FlowDefinitionId, FlowVersionId, IsActive)
            VALUES (?, 'a00', 'Create', 'AfterSave', ?, ?, 1)`,
			"subscription-"+definition.id, definition.id, definition.versionID); err != nil {
			t.Fatalf("seed TriggerSubscription %s: %v", definition.developerName, err)
		}
	}

	if err := applySourceBackedStandardFlowPackSweep(ctx, db, "companion-pack", []string{"Current_Flow"}); err != nil {
		t.Fatalf("applySourceBackedStandardFlowPackSweep: %v", err)
	}
	if err := applySourceBackedStandardFlowPackSweep(ctx, db, "companion-pack", []string{"Current_Flow"}); err != nil {
		t.Fatalf("idempotent applySourceBackedStandardFlowPackSweep: %v", err)
	}
	if err := verifySourceBackedStandardFlowPackSweeper(ctx, db, "companion-pack", []string{"Current_Flow"}); err != nil {
		t.Fatalf("verifySourceBackedStandardFlowPackSweeper: %v", err)
	}

	assertStandardFlowPackSweepState(t, ctx, db, "flowdef-current", false, false, "flowver-current", true)
	assertStandardFlowPackSweepState(t, ctx, db, "flowdef-legacy", true, false, "", false)
	assertStandardFlowPackSweepState(t, ctx, db, "flowdef-customized", false, true, "flowver-customized", true)
	assertStandardFlowPackSweepState(t, ctx, db, "flowdef-custom-active", false, false, "flowver-custom-active", true)

	legacyMetadata := []byte(`{"label":"Legacy_Flow","processType":"AutoLaunchedFlow"}`)
	if err := upsertStandardFlow(db, standardFlowSeed{
		DeveloperName: "Legacy_Flow",
		Label:         "Legacy_Flow",
		ProcessType:   "AutoLaunchedFlow",
		MetadataRaw:   legacyMetadata,
	}, time.Now().Format(time.RFC3339), "companion-pack"); err != nil {
		t.Fatalf("redeclare retired standard flow: %v", err)
	}
	assertStandardFlowPackSweepState(t, ctx, db, "flowdef-legacy", false, false, "flowver-legacy", false)
}

func assertStandardFlowPackSweepState(t *testing.T, ctx context.Context, db interface {
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}, definitionID string, wantDeleted, wantCustomized bool, wantActiveVersion string, wantSubscriptionActive bool) {
	t.Helper()
	var deleted, customized bool
	var activeVersion sql.NullString
	if err := db.QueryRowContext(ctx, `SELECT COALESCE(IsDeleted, 0), COALESCE(IsCustomized, 0), ActiveVersionId FROM FlowDefinition WHERE Id = ?`, definitionID).
		Scan(&deleted, &customized, &activeVersion); err != nil {
		t.Fatalf("read FlowDefinition %s: %v", definitionID, err)
	}
	if deleted != wantDeleted || customized != wantCustomized || strings.TrimSpace(activeVersion.String) != wantActiveVersion {
		t.Fatalf("FlowDefinition %s state = deleted:%t customized:%t active:%q, want deleted:%t customized:%t active:%q",
			definitionID, deleted, customized, activeVersion.String, wantDeleted, wantCustomized, wantActiveVersion)
	}
	var subscriptionActive bool
	if err := db.QueryRowContext(ctx, `SELECT IsActive FROM _System_TriggerSubscription WHERE FlowDefinitionId = ?`, definitionID).Scan(&subscriptionActive); err != nil {
		t.Fatalf("read TriggerSubscription %s: %v", definitionID, err)
	}
	if subscriptionActive != wantSubscriptionActive {
		t.Fatalf("TriggerSubscription %s active = %t, want %t", definitionID, subscriptionActive, wantSubscriptionActive)
	}
}