//go:build integration

package controlplane

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"testing"
	"time"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

type accountContactLastActivityCase struct {
	objectAPIName string
	tableName     string
	viewName      string
	fieldAPIName  string
}

// TestAccountAndContactLastActivityDatePhysicalColumnRepairAndViewRebuild is
// the NexusCRM-side regression for issue #1392: Account and Contact declared
// LastActivityDate in metadata, but formula-view rebuilds failed when the
// physical TiDB tables lacked the column.
func TestAccountAndContactLastActivityDatePhysicalColumnRepairAndViewRebuild(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}
	fixtureRoot := t.TempDir()
	packSource := "local_path:" + writeAccountContactLastActivityPackSource(t, fixtureRoot, "with-last-activity")
	if err := installAccountContactLastActivityPack(t, ctx, r, packSource); err != nil {
		t.Fatalf("install source-backed pack: %v", err)
	}
	assertNoPackGenerationAuthority(t, ctx, conn.DB(), "account-contact-last-activity-smoke")

	cases := accountContactLastActivityCases()
	proveAccountContactLastActivityViews(t, ctx, r, conn.DB(), cases)

	for _, tc := range cases {
		if _, err := conn.DB().ExecContext(ctx, "ALTER TABLE `"+tc.tableName+"` DROP COLUMN `LastActivityDate`"); err != nil {
			t.Fatalf("drop %s.LastActivityDate: %v", tc.objectAPIName, err)
		}
		assertColumnMissing(t, conn.DB(), tc.tableName, "LastActivityDate")
	}

	for _, tc := range cases {
		rebuildErr := r.SvcMgr.FormulaView().RebuildView(ctx, tc.objectAPIName)
		if rebuildErr == nil {
			t.Fatalf("RebuildView after dropping %s.LastActivityDate = nil, want error", tc.objectAPIName)
		}
		if !strings.Contains(strings.ToLower(rebuildErr.Error()), "lastactivitydate") {
			t.Fatalf("RebuildView error after dropping %s.LastActivityDate = %v, want error containing lastactivitydate", tc.objectAPIName, rebuildErr)
		}
	}

	if err := installAccountContactLastActivityPack(t, ctx, r, packSource); err != nil {
		t.Fatalf("install source-backed pack (repair): %v", err)
	}

	assertAccountContactLastActivityRepair(t, ctx, r, conn.DB(), cases)
}

// TestLocalPathPackCanReapplyChangedContentAtTheSamePath preserves the
// development-source contract: local_path identifies a mutable tree, so a
// later operator install from the same path must be able to converge changed
// content without pretending that the path is an immutable release revision.
func TestLocalPathPackCanReapplyChangedContentAtTheSamePath(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}

	fixtureRoot := t.TempDir()
	const sourceDir = "mutable-source"
	sourcePath := writeAccountContactLastActivityPackSourceWithoutLastActivityDate(
		t,
		fixtureRoot,
		sourceDir,
	)
	rawSource := "local_path:" + sourcePath
	if err := installAccountContactLastActivityPack(t, ctx, r, rawSource); err != nil {
		t.Fatalf("install initial local_path content: %v", err)
	}
	assertNoPackGenerationAuthority(t, ctx, conn.DB(), "account-contact-last-activity-smoke")
	for _, tc := range accountContactLastActivityCases() {
		assertColumnMissing(t, conn.DB(), tc.tableName, "LastActivityDate")
	}

	// Rewrite the same source tree with a legitimate payload change. The path
	// and pack identity remain unchanged; only current development content
	// advances.
	if got := writeAccountContactLastActivityPackSource(
		t,
		fixtureRoot,
		sourceDir,
	); got != sourcePath {
		t.Fatalf("rewritten source path = %q, want unchanged %q", got, sourcePath)
	}
	if err := installAccountContactLastActivityPack(t, ctx, r, rawSource); err != nil {
		t.Fatalf("reapply changed local_path content at same path: %v", err)
	}
	assertNoPackGenerationAuthority(t, ctx, conn.DB(), "account-contact-last-activity-smoke")
	for _, tc := range accountContactLastActivityCases() {
		if got := physicalColumnType(t, conn.DB(), tc.tableName, "LastActivityDate"); got != "date" {
			t.Fatalf("%s.LastActivityDate type after same-path reapply = %q, want date", tc.objectAPIName, got)
		}
	}
}

func TestLocalPathPackRejectsUnexpectedCatalogAuthorityBeforeMutation(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}

	fixtureRoot := t.TempDir()
	const (
		packName  = "account-contact-last-activity-smoke"
		sourceDir = "polluted-mutable-source"
	)
	sourcePath := writeAccountContactLastActivityPackSourceWithoutLastActivityDate(
		t,
		fixtureRoot,
		sourceDir,
	)
	rawSource := "local_path:" + sourcePath
	if err := installAccountContactLastActivityPack(t, ctx, r, rawSource); err != nil {
		t.Fatalf("install initial local_path content: %v", err)
	}
	assertNoPackGenerationAuthority(t, ctx, conn.DB(), packName)

	var packInstallID string
	if err := conn.DB().QueryRowContext(
		ctx,
		`SELECT PackInstallId FROM _System_InstalledPack WHERE Name = ?`,
		packName,
	).Scan(&packInstallID); err != nil {
		t.Fatalf("read mutable source install id: %v", err)
	}
	now := time.Now().UTC()
	if _, err := conn.DB().ExecContext(ctx, `INSERT INTO _System_PackActivation (
Id, PackName, ActivePackInstallId, PreviousPackInstallId,
ActivationEpoch, ActivatedDate, CreatedDate, LastModifiedDate, IsDeleted
) VALUES (?, ?, ?, NULL, ?, ?, ?, ?, 0)`,
		packActivationID(packName),
		packName,
		packInstallID,
		firstPackActivationEpoch,
		now,
		now,
		now,
	); err != nil {
		t.Fatalf("seed forbidden local_path activation authority: %v", err)
	}

	writeAccountContactLastActivityPackSource(t, fixtureRoot, sourceDir)
	err = installAccountContactLastActivityPack(t, ctx, r, rawSource)
	if !errors.Is(err, ErrPackGenerationConflict) {
		t.Fatalf("reapply local_path with forbidden activation = %v, want generation conflict", err)
	}
	for _, tc := range accountContactLastActivityCases() {
		assertColumnMissing(t, conn.DB(), tc.tableName, "LastActivityDate")
	}
	var (
		status             string
		packInstallIDAfter string
	)
	if err := conn.DB().QueryRowContext(
		ctx,
		`SELECT Status, PackInstallId FROM _System_InstalledPack WHERE Name = ?`,
		packName,
	).Scan(&status, &packInstallIDAfter); err != nil {
		t.Fatalf("read mutable source after rejected reapply: %v", err)
	}
	if status != installedPackStatusActive || packInstallIDAfter != packInstallID {
		t.Fatalf(
			"mutable source changed after rejected authority = status %q install %q, want active/%q",
			status,
			packInstallIDAfter,
			packInstallID,
		)
	}
}

func TestActiveInstalledPackReconcileFlagsDriftWhenCurrentSourceOmitsLivePackField(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()

	conn := initializeControlPlaneMetadata(t, ctx, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}

	fixtureRoot := t.TempDir()
	installSource := "local_path:" + writeAccountContactLastActivityPackSource(t, fixtureRoot, "with-last-activity")
	if err := installAccountContactLastActivityPack(t, ctx, r, installSource); err != nil {
		t.Fatalf("install source-backed pack: %v", err)
	}

	cases := accountContactLastActivityCases()
	proveAccountContactLastActivityViews(t, ctx, r, conn.DB(), cases)

	for _, tc := range cases {
		if _, err := conn.DB().ExecContext(ctx, "ALTER TABLE `"+tc.tableName+"` DROP COLUMN `LastActivityDate`"); err != nil {
			t.Fatalf("drop %s.LastActivityDate: %v", tc.objectAPIName, err)
		}
		assertColumnMissing(t, conn.DB(), tc.tableName, "LastActivityDate")
	}

	omittedSource := writeAccountContactLastActivityPackSourceWithoutLastActivityDate(t, fixtureRoot, "without-last-activity")
	if _, err := os.Stat(omittedSource); err != nil {
		t.Fatalf("stat omitted-source fixture %q: %v", omittedSource, err)
	}
	omittedSourceCanonical, err := filepath.EvalSymlinks(omittedSource)
	if err != nil {
		t.Fatalf("eval symlinks for omitted-source fixture %q: %v", omittedSource, err)
	}
	if _, err := conn.DB().ExecContext(ctx, `
		UPDATE _System_InstalledPack
		   SET SourceURI = ?
		 WHERE Name = 'account-contact-last-activity-smoke'
	`, omittedSourceCanonical); err != nil {
		t.Fatalf("redirect installed pack source to omitted-field fixture: %v", err)
	}
	assertInstalledPackSourceURI(t, ctx, conn.DB(), "account-contact-last-activity-smoke", omittedSourceCanonical)

	if err := reconcileActiveInstalledPackSourceState(ctx, r, InstallPackOptions{
		allowInstalledPackSourceStateRepair: true,
	}); err != nil {
		t.Fatalf("reconcileActiveInstalledPackSourceState after source omission + dropped LastActivityDate columns: %v", err)
	}

	// Verify must fail: the source omits LastActivityDate, so the live field is
	// undeclared drift. The physical column is repaired (data safety), but the
	// pack/source divergence is surfaced to the operator.
	if err := Verify(ctx, conn); err == nil {
		t.Fatal("Verify after source-omitted reconcile = nil, want drift error for undeclared pack-owned field")
	} else if !strings.Contains(strings.ToLower(err.Error()), "extra live pack-owned field") {
		t.Fatalf("Verify after source-omitted reconcile = %v, want 'extra live pack-owned field' drift", err)
	}

	// Physical columns must still be repaired by reconcile (data safety).
	for _, tc := range cases {
		colType := physicalColumnType(t, conn.DB(), tc.tableName, "LastActivityDate")
		if colType == "" {
			t.Fatalf("%s.LastActivityDate still missing after reconcile repair", tc.objectAPIName)
		}
	}
}

func installAccountContactLastActivityPack(t *testing.T, ctx context.Context, r *Runner, source string) error {
	t.Helper()
	if err := ApplyRuntimeRequiredPacks(ctx, r); err != nil {
		return err
	}
	return InstallPackFromSource(ctx, r, source)
}

func writeAccountContactLastActivityPackSource(t *testing.T, fixtureRoot string, dirName string) string {
	t.Helper()
	return writeAccountContactLastActivityPackSourceWithLastActivityDate(t, fixtureRoot, dirName, true)
}

func writeAccountContactLastActivityPackSourceWithoutLastActivityDate(t *testing.T, fixtureRoot string, dirName string) string {
	t.Helper()
	return writeAccountContactLastActivityPackSourceWithLastActivityDate(t, fixtureRoot, dirName, false)
}

func writeAccountContactLastActivityPackSourceWithLastActivityDate(t *testing.T, fixtureRoot string, dirName string, includeLastActivityDate bool) string {
	t.Helper()

	root := filepath.Join(fixtureRoot, dirName)
	objectsDir := filepath.Join(root, "objects")
	if err := os.MkdirAll(objectsDir, 0o755); err != nil {
		t.Fatalf("mkdir objects dir: %v", err)
	}

	manifest := `{
  "manifestFormatVersion": "1",
  "name": "account-contact-last-activity-smoke",
  "displayName": "Account Contact Last Activity Smoke",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {
    "min": "0.2.0",
    "maxExclusive": "0.3.0"
  },
  "dependencies": [],
  "components": [
    {
      "kind": "object_spec",
      "businessKey": "account-contact-last-activity",
      "manageability": "locked",
      "path": "objects/account_contact.json"
    }
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write pack manifest: %v", err)
	}

	accountLastActivityField := ""
	contactLastActivityField := ""
	if includeLastActivityDate {
		accountLastActivityField = `,
      {"apiName": "LastActivityDate", "label": "Last Activity Date", "type": "date", "createable": false, "updateable": false}`
		contactLastActivityField = `,
      {"apiName": "LastActivityDate", "label": "Last Activity Date", "type": "date", "createable": false, "updateable": false}`
	}

	objectSpec := fmt.Sprintf(`[
  {
    "apiName": "Account",
    "label": "Account",
    "pluralLabel": "Accounts",
    "tableType": "standard_object",
    "category": "crm",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Account smoke object for issue #1392.",
    "fields": [
      {"apiName": "Name", "label": "Account Name", "type": "string", "required": true, "isNameField": true}%s
    ]
  },
  {
    "apiName": "Contact",
    "label": "Contact",
    "pluralLabel": "Contacts",
    "tableType": "standard_object",
    "category": "crm",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Contact smoke object for issue #1392.",
    "fields": [
      {"apiName": "LastName", "label": "Last Name", "type": "string", "required": true, "isNameField": true}%s
    ]
  }
]`, accountLastActivityField, contactLastActivityField)
	if err := os.WriteFile(filepath.Join(objectsDir, "account_contact.json"), []byte(objectSpec), 0o644); err != nil {
		t.Fatalf("write object spec: %v", err)
	}

	return root
}

func assertInstalledPackSourceURI(t *testing.T, ctx context.Context, db *sql.DB, packName string, want string) {
	t.Helper()

	var got string
	if err := db.QueryRowContext(ctx, `
		SELECT COALESCE(SourceURI, '')
		  FROM _System_InstalledPack
		 WHERE Name = ?
		 LIMIT 1
	`, packName).Scan(&got); err != nil {
		t.Fatalf("query installed pack %s source URI: %v", packName, err)
	}
	if got != want {
		t.Fatalf("installed pack %s SourceURI = %q, want %q", packName, got, want)
	}
}

func accountContactLastActivityCases() []accountContactLastActivityCase {
	return []accountContactLastActivityCase{
		{
			objectAPIName: "Account",
			tableName:     "Account",
			viewName:      "v_account",
			fieldAPIName:  "Issue1392RecentActivity__c",
		},
		{
			objectAPIName: "Contact",
			tableName:     "Contact",
			viewName:      "v_contact",
			fieldAPIName:  "Issue1392RecentTouch__c",
		},
	}
}

func proveAccountContactLastActivityViews(t *testing.T, ctx context.Context, r *Runner, db *sql.DB, cases []accountContactLastActivityCase) {
	t.Helper()

	for _, tc := range cases {
		colType := physicalColumnType(t, db, tc.tableName, "LastActivityDate")
		if colType == "" {
			t.Fatalf("%s.LastActivityDate physical column missing after install-pack", tc.objectAPIName)
		}
		if colType != "date" {
			t.Fatalf("%s.LastActivityDate physical type = %q, want date", tc.objectAPIName, colType)
		}

		formula := "TODAY() - LastActivityDate <= 14"
		mode := "active"
		returnType := constants.FieldTypeBoolean
		if err := r.SvcMgr.Metadata().CreateField(ctx, tc.objectAPIName, &models.FieldMetadata{
			APIName:     tc.fieldAPIName,
			Label:       "Issue 1392 activity proof",
			Type:        constants.FieldTypeFormula,
			Createable:  false,
			Updateable:  false,
			Formula:     &formula,
			FormulaMode: &mode,
			ReturnType:  &returnType,
		}); err != nil {
			t.Fatalf("create %s.%s: %v", tc.objectAPIName, tc.fieldAPIName, err)
		}

		if _, err := r.SvcMgr.FormulaView().PreviewDDL(ctx, tc.objectAPIName); err != nil {
			t.Fatalf("PreviewDDL before column drop for %s: %v", tc.objectAPIName, err)
		}
		if err := r.SvcMgr.FormulaView().RebuildView(ctx, tc.objectAPIName); err != nil {
			t.Fatalf("RebuildView before column drop for %s: %v", tc.objectAPIName, err)
		}
		assertViewQueryable(t, db, tc.viewName, tc.fieldAPIName)
	}
}

func assertAccountContactLastActivityRepair(t *testing.T, ctx context.Context, r *Runner, db *sql.DB, cases []accountContactLastActivityCase) {
	t.Helper()

	for _, tc := range cases {
		colType := physicalColumnType(t, db, tc.tableName, "LastActivityDate")
		if colType == "" {
			t.Fatalf("%s.LastActivityDate still missing after repair", tc.objectAPIName)
		}
		if colType != "date" {
			t.Fatalf("%s.LastActivityDate type after repair = %q, want date", tc.objectAPIName, colType)
		}
		if err := r.SvcMgr.FormulaView().RebuildView(ctx, tc.objectAPIName); err != nil {
			t.Fatalf("RebuildView after repair for %s: %v", tc.objectAPIName, err)
		}
		assertViewQueryable(t, db, tc.viewName, tc.fieldAPIName)
	}
}