package sqlite

import (
	"context"
	"testing"
)

func TestProviderAccountsRepoCRUDAndFilters(t *testing.T) {
	t.Parallel()

	store := openTestDBWithFK(t)
	ctx := context.Background()
	hostID := createTestHost(t, store)
	packID := createTestPack(t, store)
	providerID, err := store.Providers().Create(ctx, Provider{
		PackID:      packID,
		ProviderID:  "deepseek-official",
		DisplayName: "DeepSeek Official",
		BaseURL:     "https://api.deepseek.com",
		Platform:    "openai",
	})
	if err != nil {
		t.Fatalf("Providers().Create() error = %v", err)
	}
	if _, err := store.LogicalGroups().Create(ctx, LogicalGroup{LogicalGroupID: "lg-1", DisplayName: "LG 1", Status: "active"}); err != nil {
		t.Fatalf("LogicalGroups().Create() error = %v", err)
	}
	if _, err := store.LogicalGroupRoutes().Create(ctx, LogicalGroupRoute{
		RouteID:        "route-1",
		LogicalGroupID: "lg-1",
		Name:           "Route 1",
		Status:         "active",
		Priority:       10,
		Weight:         100,
		ShadowGroupID:  "shadow-group-1",
		ShadowHostID:   "host-" + sanitizeTestName(t.Name()),
	}); err != nil {
		t.Fatalf("LogicalGroupRoutes().Create() error = %v", err)
	}

	accountRepo := store.ProviderAccounts()
	accountID, err := accountRepo.Create(ctx, ProviderAccount{
		HostID:          hostID,
		ProviderID:      providerID,
		RouteID:         "route-1",
		ShadowGroupID:   "shadow-group-1",
		HostAccountID:   "account-1",
		KeyFingerprint:  "sha256:abc",
		AccountName:     "deepseek-01",
		AccountStatus:   ProviderAccountStatusActive,
		LastProbeStatus: "passed",
		LastProbeAt:     "2026-05-29T00:00:00Z",
	})
	if err != nil {
		t.Fatalf("ProviderAccounts().Create() error = %v", err)
	}

	got, err := accountRepo.GetByID(ctx, accountID)
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByID() error = %v", err)
	}
	if got.HostAccountID != "account-1" || got.AccountStatus != ProviderAccountStatusActive {
		t.Fatalf("ProviderAccounts().GetByID() = %+v", got)
	}

	if _, err := accountRepo.Upsert(ctx, ProviderAccount{
		HostID:          hostID,
		ProviderID:      providerID,
		RouteID:         "route-1",
		ShadowGroupID:   "shadow-group-1",
		HostAccountID:   "account-1",
		KeyFingerprint:  "sha256:abc",
		AccountName:     "deepseek-01",
		AccountStatus:   ProviderAccountStatusBroken,
		LastProbeStatus: "failed",
		LastProbeAt:     "2026-05-29T01:00:00Z",
	}); err != nil {
		t.Fatalf("ProviderAccounts().Upsert() error = %v", err)
	}

	view, err := accountRepo.GetViewByID(ctx, accountID)
	if err != nil {
		t.Fatalf("ProviderAccounts().GetViewByID() error = %v", err)
	}
	if view.ProviderID != "deepseek-official" || view.LogicalGroupID != "lg-1" || view.AccountStatus != ProviderAccountStatusBroken {
		t.Fatalf("ProviderAccounts().GetViewByID() = %+v", view)
	}

	rows, err := accountRepo.List(ctx, ProviderAccountListFilter{
		HostID:         "host-" + sanitizeTestName(t.Name()),
		ProviderID:     "deepseek-official",
		LogicalGroupID: "lg-1",
		RouteID:        "route-1",
		ShadowGroupID:  "shadow-group-1",
		AccountStatus:  ProviderAccountStatusBroken,
		Query:          "deepseek",
	})
	if err != nil {
		t.Fatalf("ProviderAccounts().List() error = %v", err)
	}
	if len(rows) != 1 || rows[0].ID != accountID {
		t.Fatalf("ProviderAccounts().List() = %+v, want one row for account_id %d", rows, accountID)
	}
	if rows[0].LogicalGroupID != "lg-1" || rows[0].RouteName != "Route 1" || rows[0].ShadowHostID != "host-"+sanitizeTestName(t.Name()) {
		t.Fatalf("ProviderAccounts().List() relationship view = %+v", rows[0])
	}
	if rows[0].BindingState != ProviderAccountBindingStateAssigned || rows[0].BindingCandidateCount != 1 {
		t.Fatalf("ProviderAccounts().List() binding view = %+v", rows[0])
	}

	if err := accountRepo.UpdateStatusByID(ctx, accountID, ProviderAccountStatusDisabled, "manual_disable"); err != nil {
		t.Fatalf("ProviderAccounts().UpdateStatusByID() error = %v", err)
	}
	got, err = accountRepo.GetByID(ctx, accountID)
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByID() after status update error = %v", err)
	}
	if got.AccountStatus != ProviderAccountStatusDisabled || got.DisabledReason != "manual_disable" {
		t.Fatalf("ProviderAccounts().GetByID() after status update = %+v", got)
	}

	if _, err := store.LogicalGroupRoutes().Create(ctx, LogicalGroupRoute{
		RouteID:        "route-2",
		LogicalGroupID: "lg-1",
		Name:           "Route 2",
		Status:         "active",
		Priority:       20,
		Weight:         100,
		ShadowGroupID:  "shadow-group-1",
		ShadowHostID:   "host-" + sanitizeTestName(t.Name()),
	}); err != nil {
		t.Fatalf("LogicalGroupRoutes().Create(route-2) error = %v", err)
	}
	if err := accountRepo.UpdateBindingByID(ctx, accountID, "", "shadow-group-1"); err != nil {
		t.Fatalf("ProviderAccounts().UpdateBindingByID(clear) error = %v", err)
	}
	conflictRows, err := accountRepo.List(ctx, ProviderAccountListFilter{BindingState: ProviderAccountBindingStateConflict})
	if err != nil {
		t.Fatalf("ProviderAccounts().List(conflict) error = %v", err)
	}
	if len(conflictRows) != 1 || conflictRows[0].ID != accountID || conflictRows[0].BindingState != ProviderAccountBindingStateConflict {
		t.Fatalf("ProviderAccounts().List(conflict) = %+v", conflictRows)
	}
	if err := accountRepo.UpdateBindingByID(ctx, accountID, "route-2", "shadow-group-1"); err != nil {
		t.Fatalf("ProviderAccounts().UpdateBindingByID(assign) error = %v", err)
	}
	view, err = accountRepo.GetViewByID(ctx, accountID)
	if err != nil {
		t.Fatalf("ProviderAccounts().GetViewByID(after binding) error = %v", err)
	}
	if view.RouteID != "route-2" || view.BindingState != ProviderAccountBindingStateAssigned {
		t.Fatalf("ProviderAccounts().GetViewByID(after binding) = %+v", view)
	}
}

func TestSyncProviderAccountsFromImportBatchCreatesAndDeprecatesInventory(t *testing.T) {
	t.Parallel()

	store := openTestDBWithFK(t)
	ctx := context.Background()
	hostID := createTestHost(t, store)
	packID := createTestPack(t, store)
	providerID, err := store.Providers().Create(ctx, Provider{
		PackID:      packID,
		ProviderID:  "asxs-provider",
		DisplayName: "ASXS Provider",
		BaseURL:     "https://api.asxs.top/v1",
		Platform:    "openai",
	})
	if err != nil {
		t.Fatalf("Providers().Create() error = %v", err)
	}
	batch1, err := store.ImportBatches().Create(ctx, ImportBatch{
		HostID:       hostID,
		PackID:       packID,
		ProviderID:   providerID,
		Mode:         "strict",
		BatchStatus:  "succeeded",
		AccessStatus: "subscription_ready",
	})
	if err != nil {
		t.Fatalf("ImportBatches().Create(batch1) error = %v", err)
	}
	if _, err := store.ImportBatchItems().Create(ctx, ImportBatchItem{
		BatchID:          batch1,
		KeyFingerprint:   "sha256:key1",
		AccountStatus:    "passed",
		ProbeSummaryJSON: `{"account_id":"account-1","probe_status":"passed"}`,
	}); err != nil {
		t.Fatalf("ImportBatchItems().Create(batch1) error = %v", err)
	}
	for _, resource := range []ManagedResource{
		{BatchID: batch1, HostID: hostID, ResourceType: "group", HostResourceID: "group-1", ResourceName: "ASXS Group"},
		{BatchID: batch1, HostID: hostID, ResourceType: "account", HostResourceID: "account-1", ResourceName: "asxs-01"},
	} {
		if _, err := store.ManagedResources().Create(ctx, resource); err != nil {
			t.Fatalf("ManagedResources().Create(batch1/%s) error = %v", resource.ResourceType, err)
		}
	}
	if err := SyncProviderAccountsFromImportBatch(ctx, store, batch1); err != nil {
		t.Fatalf("SyncProviderAccountsFromImportBatch(batch1) error = %v", err)
	}

	account1, err := store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-1")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID(account-1) error = %v", err)
	}
	if account1.AccountStatus != ProviderAccountStatusActive || account1.ShadowGroupID != "group-1" {
		t.Fatalf("account-1 = %+v, want active shadow group-1", account1)
	}

	batch2, err := store.ImportBatches().Create(ctx, ImportBatch{
		HostID:       hostID,
		PackID:       packID,
		ProviderID:   providerID,
		Mode:         "strict",
		BatchStatus:  "succeeded",
		AccessStatus: "subscription_ready",
	})
	if err != nil {
		t.Fatalf("ImportBatches().Create(batch2) error = %v", err)
	}
	if _, err := store.ImportBatchItems().Create(ctx, ImportBatchItem{
		BatchID:          batch2,
		KeyFingerprint:   "sha256:key2",
		AccountStatus:    "failed",
		ProbeSummaryJSON: `{"account_id":"account-2","probe_status":"failed"}`,
	}); err != nil {
		t.Fatalf("ImportBatchItems().Create(batch2) error = %v", err)
	}
	for _, resource := range []ManagedResource{
		{BatchID: batch2, HostID: hostID, ResourceType: "group", HostResourceID: "group-2", ResourceName: "ASXS Group 2"},
		{BatchID: batch2, HostID: hostID, ResourceType: "account", HostResourceID: "account-2", ResourceName: "asxs-02"},
	} {
		if _, err := store.ManagedResources().Create(ctx, resource); err != nil {
			t.Fatalf("ManagedResources().Create(batch2/%s) error = %v", resource.ResourceType, err)
		}
	}
	if err := SyncProviderAccountsFromImportBatch(ctx, store, batch2); err != nil {
		t.Fatalf("SyncProviderAccountsFromImportBatch(batch2) error = %v", err)
	}

	account1, err = store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-1")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID(account-1 after batch2) error = %v", err)
	}
	if account1.AccountStatus != ProviderAccountStatusDeprecated || account1.DisabledReason != providerAccountDeprecatedMissingReason {
		t.Fatalf("account-1 after batch2 = %+v, want deprecated missing_from_latest_batch", account1)
	}

	account2, err := store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-2")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID(account-2) error = %v", err)
	}
	if account2.AccountStatus != ProviderAccountStatusBroken || account2.LastProbeStatus != "failed" {
		t.Fatalf("account-2 = %+v, want broken failed", account2)
	}
}

func TestSyncProviderAccountsFromImportBatchPreservesManualDisabledStatus(t *testing.T) {
	t.Parallel()

	store := openTestDBWithFK(t)
	ctx := context.Background()
	hostID := createTestHost(t, store)
	packID := createTestPack(t, store)
	providerID, err := store.Providers().Create(ctx, Provider{
		PackID:      packID,
		ProviderID:  "asxs-provider",
		DisplayName: "ASXS Provider",
		BaseURL:     "https://api.asxs.top/v1",
		Platform:    "openai",
	})
	if err != nil {
		t.Fatalf("Providers().Create() error = %v", err)
	}
	batchID, err := store.ImportBatches().Create(ctx, ImportBatch{
		HostID:       hostID,
		PackID:       packID,
		ProviderID:   providerID,
		Mode:         "strict",
		BatchStatus:  "succeeded",
		AccessStatus: "subscription_ready",
	})
	if err != nil {
		t.Fatalf("ImportBatches().Create() error = %v", err)
	}
	if _, err := store.ImportBatchItems().Create(ctx, ImportBatchItem{
		BatchID:          batchID,
		KeyFingerprint:   "sha256:key1",
		AccountStatus:    "passed",
		ProbeSummaryJSON: `{"account_id":"account-1","probe_status":"passed"}`,
	}); err != nil {
		t.Fatalf("ImportBatchItems().Create() error = %v", err)
	}
	for _, resource := range []ManagedResource{
		{BatchID: batchID, HostID: hostID, ResourceType: "group", HostResourceID: "group-1", ResourceName: "ASXS Group"},
		{BatchID: batchID, HostID: hostID, ResourceType: "account", HostResourceID: "account-1", ResourceName: "asxs-01"},
	} {
		if _, err := store.ManagedResources().Create(ctx, resource); err != nil {
			t.Fatalf("ManagedResources().Create(%s) error = %v", resource.ResourceType, err)
		}
	}
	if err := SyncProviderAccountsFromImportBatch(ctx, store, batchID); err != nil {
		t.Fatalf("SyncProviderAccountsFromImportBatch() error = %v", err)
	}

	account, err := store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-1")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID() error = %v", err)
	}
	if err := store.ProviderAccounts().UpdateStatusByID(ctx, account.ID, ProviderAccountStatusDisabled, "manual_disable"); err != nil {
		t.Fatalf("ProviderAccounts().UpdateStatusByID() error = %v", err)
	}

	if err := SyncProviderAccountsFromImportBatch(ctx, store, batchID); err != nil {
		t.Fatalf("SyncProviderAccountsFromImportBatch(second) error = %v", err)
	}
	account, err = store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-1")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID(second) error = %v", err)
	}
	if account.AccountStatus != ProviderAccountStatusDisabled || account.DisabledReason != "manual_disable" {
		t.Fatalf("account after resync = %+v, want disabled manual_disable preserved", account)
	}
}

func TestSyncProviderAccountsFromImportBatchInfersRouteFromShadowBinding(t *testing.T) {
	t.Parallel()

	store := openTestDBWithFK(t)
	ctx := context.Background()
	hostID := createTestHost(t, store)
	hostRow, err := store.Hosts().GetByID(ctx, hostID)
	if err != nil {
		t.Fatalf("Hosts().GetByID() error = %v", err)
	}
	packID := createTestPack(t, store)
	providerID, err := store.Providers().Create(ctx, Provider{
		PackID:      packID,
		ProviderID:  "asxs-provider",
		DisplayName: "ASXS Provider",
		BaseURL:     "https://api.asxs.top/v1",
		Platform:    "openai",
	})
	if err != nil {
		t.Fatalf("Providers().Create() error = %v", err)
	}
	if _, err := store.LogicalGroups().Create(ctx, LogicalGroup{
		LogicalGroupID: "gpt-shared",
		DisplayName:    "GPT Shared",
		Status:         "active",
	}); err != nil {
		t.Fatalf("LogicalGroups().Create() error = %v", err)
	}
	if _, err := store.LogicalGroupRoutes().Create(ctx, LogicalGroupRoute{
		RouteID:        "route-shadow-1",
		LogicalGroupID: "gpt-shared",
		Name:           "Shadow Route",
		Status:         "active",
		Priority:       10,
		Weight:         100,
		ShadowGroupID:  "group-1",
		ShadowHostID:   hostRow.HostID,
	}); err != nil {
		t.Fatalf("LogicalGroupRoutes().Create() error = %v", err)
	}
	batchID, err := store.ImportBatches().Create(ctx, ImportBatch{
		HostID:       hostID,
		PackID:       packID,
		ProviderID:   providerID,
		Mode:         "strict",
		BatchStatus:  "succeeded",
		AccessStatus: "subscription_ready",
	})
	if err != nil {
		t.Fatalf("ImportBatches().Create() error = %v", err)
	}
	if _, err := store.ImportBatchItems().Create(ctx, ImportBatchItem{
		BatchID:          batchID,
		KeyFingerprint:   "sha256:key1",
		AccountStatus:    "passed",
		ProbeSummaryJSON: `{"account_id":"account-1","probe_status":"passed"}`,
	}); err != nil {
		t.Fatalf("ImportBatchItems().Create() error = %v", err)
	}
	for _, resource := range []ManagedResource{
		{BatchID: batchID, HostID: hostID, ResourceType: "group", HostResourceID: "group-1", ResourceName: "ASXS Group"},
		{BatchID: batchID, HostID: hostID, ResourceType: "account", HostResourceID: "account-1", ResourceName: "asxs-01"},
	} {
		if _, err := store.ManagedResources().Create(ctx, resource); err != nil {
			t.Fatalf("ManagedResources().Create(%s) error = %v", resource.ResourceType, err)
		}
	}
	if err := SyncProviderAccountsFromImportBatch(ctx, store, batchID); err != nil {
		t.Fatalf("SyncProviderAccountsFromImportBatch() error = %v", err)
	}

	account, err := store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-1")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID() error = %v", err)
	}
	if account.RouteID != "route-shadow-1" || account.ShadowGroupID != "group-1" {
		t.Fatalf("provider account route binding = %+v, want route-shadow-1/group-1", account)
	}
	view, err := store.ProviderAccounts().GetViewByID(ctx, account.ID)
	if err != nil {
		t.Fatalf("ProviderAccounts().GetViewByID() error = %v", err)
	}
	if view.LogicalGroupID != "gpt-shared" || view.RouteName != "Shadow Route" || view.ShadowHostID != hostRow.HostID {
		t.Fatalf("provider account view route binding = %+v", view)
	}
}

func TestSyncProviderAccountsFromImportBatchLeavesRouteEmptyOnAmbiguousShadowBinding(t *testing.T) {
	t.Parallel()

	store := openTestDBWithFK(t)
	ctx := context.Background()
	hostID := createTestHost(t, store)
	hostRow, err := store.Hosts().GetByID(ctx, hostID)
	if err != nil {
		t.Fatalf("Hosts().GetByID() error = %v", err)
	}
	packID := createTestPack(t, store)
	providerID, err := store.Providers().Create(ctx, Provider{
		PackID:      packID,
		ProviderID:  "asxs-provider",
		DisplayName: "ASXS Provider",
		BaseURL:     "https://api.asxs.top/v1",
		Platform:    "openai",
	})
	if err != nil {
		t.Fatalf("Providers().Create() error = %v", err)
	}
	for _, groupID := range []string{"gpt-shared-a", "gpt-shared-b"} {
		if _, err := store.LogicalGroups().Create(ctx, LogicalGroup{
			LogicalGroupID: groupID,
			DisplayName:    groupID,
			Status:         "active",
		}); err != nil {
			t.Fatalf("LogicalGroups().Create(%s) error = %v", groupID, err)
		}
	}
	for _, routeID := range []string{"route-a", "route-b"} {
		logicalGroupID := "gpt-shared-a"
		if routeID == "route-b" {
			logicalGroupID = "gpt-shared-b"
		}
		if _, err := store.LogicalGroupRoutes().Create(ctx, LogicalGroupRoute{
			RouteID:        routeID,
			LogicalGroupID: logicalGroupID,
			Name:           routeID,
			Status:         "active",
			Priority:       10,
			Weight:         100,
			ShadowGroupID:  "group-1",
			ShadowHostID:   hostRow.HostID,
		}); err != nil {
			t.Fatalf("LogicalGroupRoutes().Create(%s) error = %v", routeID, err)
		}
	}
	batchID, err := store.ImportBatches().Create(ctx, ImportBatch{
		HostID:       hostID,
		PackID:       packID,
		ProviderID:   providerID,
		Mode:         "strict",
		BatchStatus:  "succeeded",
		AccessStatus: "subscription_ready",
	})
	if err != nil {
		t.Fatalf("ImportBatches().Create() error = %v", err)
	}
	if _, err := store.ImportBatchItems().Create(ctx, ImportBatchItem{
		BatchID:          batchID,
		KeyFingerprint:   "sha256:key1",
		AccountStatus:    "passed",
		ProbeSummaryJSON: `{"account_id":"account-1","probe_status":"passed"}`,
	}); err != nil {
		t.Fatalf("ImportBatchItems().Create() error = %v", err)
	}
	for _, resource := range []ManagedResource{
		{BatchID: batchID, HostID: hostID, ResourceType: "group", HostResourceID: "group-1", ResourceName: "ASXS Group"},
		{BatchID: batchID, HostID: hostID, ResourceType: "account", HostResourceID: "account-1", ResourceName: "asxs-01"},
	} {
		if _, err := store.ManagedResources().Create(ctx, resource); err != nil {
			t.Fatalf("ManagedResources().Create(%s) error = %v", resource.ResourceType, err)
		}
	}

	if err := SyncProviderAccountsFromImportBatch(ctx, store, batchID); err != nil {
		t.Fatalf("SyncProviderAccountsFromImportBatch() error = %v", err)
	}
	account, err := store.ProviderAccounts().GetByHostIDAndAccountID(ctx, hostID, "account-1")
	if err != nil {
		t.Fatalf("ProviderAccounts().GetByHostIDAndAccountID() error = %v", err)
	}
	if account.RouteID != "" {
		t.Fatalf("provider account route binding = %+v, want empty route on ambiguous shadow binding", account)
	}
}
