//go:build integration

package controlplane

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

	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

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

	conn := openIntegrationConnection(t, cfg)
	if err := InitL0(ctx, conn); err != nil {
		_ = conn.Close()
		t.Fatalf("init-l0: %v", err)
	}
	_ = conn.Close()

	conn = openIntegrationConnection(t, 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)
	}

	sourceDir := writeRollupMaterializationPackSource(t)
	source := "local_path:" + sourceDir
	if err := InstallPackFromSource(ctx, r, source); err != nil {
		t.Fatalf("install source-backed pack: %v", err)
	}
	report, err := resolveInstalledSourceBackedPackForTest(ctx, conn.DB(), "rollup-materialization-smoke")
	if err != nil {
		t.Fatalf("resolve installed source-backed pack: %v", err)
	}

	if _, err := conn.DB().ExecContext(ctx, `
		DELETE f
		  FROM _System_Field f
		  JOIN _System_Object o ON o.Id = f.ObjectId
		 WHERE o.ApiName = 'Epic'
		   AND f.ApiName = 'TotalPoints'
	`); err != nil {
		t.Fatalf("delete drifted Epic.TotalPoints field row: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx, "ALTER TABLE `Epic` DROP COLUMN `TotalPoints`"); err != nil {
		t.Fatalf("drop drifted Epic.TotalPoints physical column: %v", err)
	}
	assertNoActiveFieldRow(t, conn.DB(), "Epic", "TotalPoints")
	assertColumnMissing(t, conn.DB(), "Epic", "TotalPoints")

	if err := materializeSourceBackedPackDerived(ctx, r, report, false); err != nil {
		t.Fatalf("materialize source-backed pack derived artifacts after rollup drift: %v", err)
	}

	if err := VerifyInstalledPackByName(ctx, conn.DB(), "rollup-materialization-smoke", true); err != nil {
		t.Fatalf("VerifyInstalledPackByName after materialize = %v, want repaired substrate", err)
	}
	assertPackOwnedFieldRow(t, conn.DB(), "Epic", "TotalPoints", "rollup-materialization-smoke")
	if got := physicalColumnType(t, conn.DB(), "Epic", "TotalPoints"); !strings.HasPrefix(strings.ToLower(got), "decimal") {
		t.Fatalf("Epic.TotalPoints physical type after materialize = %q, want decimal-derived rollup storage", got)
	}
	assertViewQueryable(t, conn.DB(), "v_epic", "RemainingPoints")
}

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

	conn := openIntegrationConnection(t, cfg)
	if err := InitL0(ctx, conn); err != nil {
		_ = conn.Close()
		t.Fatalf("init-l0: %v", err)
	}
	_ = conn.Close()

	conn = openIntegrationConnection(t, 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)
	}

	sourceDir := writeRollupMaterializationPackSource(t)
	source := "local_path:" + sourceDir
	if err := InstallPackFromSource(ctx, r, source); err != nil {
		t.Fatalf("install source-backed pack: %v", err)
	}

	if _, err := conn.DB().ExecContext(ctx, `
		DELETE f
		  FROM _System_Field f
		  JOIN _System_Object o ON o.Id = f.ObjectId
		 WHERE o.ApiName = 'Epic'
		   AND f.ApiName = 'TotalPoints'
	`); err != nil {
		t.Fatalf("delete drifted Epic.TotalPoints field row: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx, "ALTER TABLE `Epic` DROP COLUMN `TotalPoints`"); err != nil {
		t.Fatalf("drop drifted Epic.TotalPoints physical column: %v", err)
	}
	assertNoActiveFieldRow(t, conn.DB(), "Epic", "TotalPoints")
	assertColumnMissing(t, conn.DB(), "Epic", "TotalPoints")

	err = Verify(ctx, conn)
	if err == nil {
		t.Fatal("Verify after rollup substrate drift = nil, want installed-pack drift error")
	}
	if !strings.Contains(err.Error(), "rollup-materialization-smoke physical column Epic.TotalPoints is missing") {
		t.Fatalf("Verify error = %v, want missing physical-column drift", err)
	}
	if !strings.Contains(err.Error(), "rollup-materialization-smoke _System_Field row for Epic.TotalPoints is missing") {
		t.Fatalf("Verify error = %v, want missing _System_Field drift", err)
	}
	if !isRepairableInstalledPackApplyAllError(err) {
		t.Fatalf("Verify error = %v, want repairable installed-pack verify classification", err)
	}

	if err := ApplyAll(ctx, conn); err != nil {
		t.Fatalf("apply all after rollup substrate drift: %v", err)
	}

	checkConn := openIntegrationConnection(t, cfg)
	defer checkConn.Close()

	if err := Verify(ctx, checkConn); err != nil {
		t.Fatalf("Verify after ApplyAll rollup substrate repair = %v", err)
	}
	assertPackOwnedFieldRow(t, checkConn.DB(), "Epic", "TotalPoints", "rollup-materialization-smoke")
	if got := physicalColumnType(t, checkConn.DB(), "Epic", "TotalPoints"); !strings.HasPrefix(strings.ToLower(got), "decimal") {
		t.Fatalf("Epic.TotalPoints physical type after repair = %q, want decimal-derived rollup storage", got)
	}
	assertViewExists(t, checkConn.DB(), "v_epic")
	assertViewQueryable(t, checkConn.DB(), "v_epic", "RemainingPoints")
}

func writeRollupMaterializationPackSource(t *testing.T) string {
	t.Helper()

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

	manifest := `{
  "manifestFormatVersion": "1",
  "name": "rollup-materialization-smoke",
  "displayName": "Rollup Materialization Smoke",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {
    "min": "0.2.0",
    "maxExclusive": "0.3.0"
  },
  "dependencies": [],
  "components": [
    {
      "kind": "object_spec",
      "businessKey": "epic-story",
      "manageability": "locked",
      "path": "objects/epic_story.json"
    }
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write pack manifest: %v", err)
	}

	objectSpec := `[
  {
    "apiName": "Epic",
    "label": "Epic",
    "pluralLabel": "Epics",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Epic parent object for rollup substrate restoration coverage.",
    "fields": [
      {"apiName": "Name", "label": "Epic Name", "type": "string", "required": true, "isNameField": true},
      {"apiName": "CapacityPoints", "label": "Capacity Points", "type": "double", "required": true},
      {
        "apiName": "TotalPoints",
        "label": "Total Points",
        "type": "rollupsummary",
        "rollupConfig": {
          "summaryObject": "Story",
          "summaryField": "Points",
          "relationshipField": "EpicId",
          "calcType": "SUM"
        }
      },
      {
        "apiName": "RemainingPoints",
        "label": "Remaining Points",
        "type": "formula",
        "createable": false,
        "updateable": false,
        "formulaMode": "active",
        "formula": "CapacityPoints - TotalPoints",
        "returnType": "double"
      }
    ]
  },
  {
    "apiName": "Story",
    "label": "Story",
    "pluralLabel": "Stories",
    "tableType": "standard_object",
    "category": "jira",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "description": "Story child object for rollup substrate restoration coverage.",
    "fields": [
      {"apiName": "Name", "label": "Story Name", "type": "string", "required": true, "isNameField": true},
      {
        "apiName": "EpicId",
        "label": "Epic",
        "type": "reference",
        "required": true,
        "referenceTo": ["Epic"],
        "relationshipName": "Epic",
        "childRelationshipName": "Stories"
      },
      {"apiName": "Points", "label": "Points", "type": "double", "required": true}
    ]
  }
]`
	if err := os.WriteFile(filepath.Join(objectsDir, "epic_story.json"), []byte(objectSpec), 0o644); err != nil {
		t.Fatalf("write object spec: %v", err)
	}

	return root
}

func resolveInstalledSourceBackedPackForTest(ctx context.Context, db *sql.DB, packName string) (*packs.SourceBackedPack, error) {
	row, ok, err := getInstalledPackRowByName(ctx, db, packName)
	if err != nil {
		return nil, err
	}
	if !ok {
		return nil, sql.ErrNoRows
	}
	return sourceBackedPackFromInstalledRow(ctx, db, row)
}

func assertPackOwnedFieldRow(t *testing.T, db *sql.DB, objectAPIName string, fieldAPIName string, ownerPack string) {
	t.Helper()

	var ownerKind, gotOwnerPack string
	if err := db.QueryRow(`
		SELECT COALESCE(f.OwnerKind, ''), COALESCE(f.OwnerPack, '')
		  FROM _System_Field f
		  JOIN _System_Object o ON o.Id = f.ObjectId
		 WHERE o.ApiName = ?
		   AND f.ApiName = ?
		   AND COALESCE(o.IsDeleted, 0) = 0
		   AND COALESCE(f.IsDeleted, 0) = 0
		 LIMIT 1`,
		objectAPIName,
		fieldAPIName,
	).Scan(&ownerKind, &gotOwnerPack); err != nil {
		t.Fatalf("query field row %s.%s: %v", objectAPIName, fieldAPIName, err)
	}
	if ownerKind != "pack" {
		t.Fatalf("%s.%s OwnerKind = %q, want pack", objectAPIName, fieldAPIName, ownerKind)
	}
	if gotOwnerPack != ownerPack {
		t.Fatalf("%s.%s OwnerPack = %q, want %q", objectAPIName, fieldAPIName, gotOwnerPack, ownerPack)
	}
}

// TestApplyAllRepairsRollupSubstrateWhenFormulaViewNeverBuilt covers the scenario
// where a rollup field's substrate (physical column + _System_Field row) was never
// created, so the active-formula field that depends on it also has no formula VIEW.
// Before the fix for issue #1081, verifyPackObjectPhysicalSchemaWithContext would
// generate "physical column is missing" drift for the active-formula field
// (RemainingPoints) even though active-formula fields are VIEW-computed. Since
// RemainingPoints' _System_Field row existed, hasColumnMissingWithoutFieldRowMissing
// returned true → isRepairableInstalledPackApplyAllError returned false → repair was
// blocked → pack permanently stuck in failed state.
func TestApplyAllRepairsRollupSubstrateWhenFormulaViewNeverBuilt(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()
	setDatabaseEnvFromConfig(t, cfg)

	conn := openIntegrationConnection(t, cfg)
	if err := InitL0(ctx, conn); err != nil {
		_ = conn.Close()
		t.Fatalf("init-l0: %v", err)
	}
	_ = conn.Close()

	conn = openIntegrationConnection(t, 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)
	}

	sourceDir := writeRollupMaterializationPackSource(t)
	source := "local_path:" + sourceDir
	if err := InstallPackFromSource(ctx, r, source); err != nil {
		t.Fatalf("install source-backed pack: %v", err)
	}

	// Simulate the production scenario: drop the rollup substrate AND the formula
	// VIEW. The active-formula field (RemainingPoints) retains its _System_Field row
	// but has no VIEW backing. This is distinct from the existing test which only
	// drops the rollup substrate while the VIEW remains (invalid but still present in
	// information_schema.COLUMNS via its stale DDL definition).
	if _, err := conn.DB().ExecContext(ctx, `
		DELETE f
		  FROM _System_Field f
		  JOIN _System_Object o ON o.Id = f.ObjectId
		 WHERE o.ApiName = 'Epic'
		   AND f.ApiName = 'TotalPoints'
	`); err != nil {
		t.Fatalf("delete drifted Epic.TotalPoints field row: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx, "ALTER TABLE `Epic` DROP COLUMN `TotalPoints`"); err != nil {
		t.Fatalf("drop drifted Epic.TotalPoints physical column: %v", err)
	}
	if _, err := conn.DB().ExecContext(ctx, "DROP VIEW IF EXISTS `v_epic`"); err != nil {
		t.Fatalf("drop formula view v_epic: %v", err)
	}
	assertNoActiveFieldRow(t, conn.DB(), "Epic", "TotalPoints")
	assertColumnMissing(t, conn.DB(), "Epic", "TotalPoints")
	assertViewMissing(t, conn.DB(), "v_epic")

	verifyErr := Verify(ctx, conn)
	if verifyErr == nil {
		t.Fatal("Verify after rollup substrate drift + dropped view = nil, want drift error")
	}
	if !strings.Contains(verifyErr.Error(), "rollup-materialization-smoke physical column Epic.TotalPoints is missing") {
		t.Fatalf("Verify error = %v, want missing physical-column drift for TotalPoints", verifyErr)
	}
	// The key regression assertion: with the active-formula physical-column check
	// fixed, the drift must be classified as ApplyAll-repairable. Without the fix,
	// RemainingPoints generates "column missing without field row missing" and
	// isRepairableInstalledPackApplyAllError returns false.
	if !isRepairableInstalledPackApplyAllError(verifyErr) {
		t.Fatalf("Verify error is not ApplyAll-repairable (active-formula column check not skipped?): %v", verifyErr)
	}

	if err := ApplyAll(ctx, conn); err != nil {
		t.Fatalf("ApplyAll after rollup substrate drift + dropped view: %v", err)
	}

	checkConn := openIntegrationConnection(t, cfg)
	defer checkConn.Close()

	if err := Verify(ctx, checkConn); err != nil {
		t.Fatalf("Verify after ApplyAll rollup substrate repair = %v", err)
	}
	assertPackOwnedFieldRow(t, checkConn.DB(), "Epic", "TotalPoints", "rollup-materialization-smoke")
	if got := physicalColumnType(t, checkConn.DB(), "Epic", "TotalPoints"); !strings.HasPrefix(strings.ToLower(got), "decimal") {
		t.Fatalf("Epic.TotalPoints physical type after repair = %q, want decimal-derived rollup storage", got)
	}
	assertViewExists(t, checkConn.DB(), "v_epic")
	assertViewQueryable(t, checkConn.DB(), "v_epic", "RemainingPoints")
}

func assertViewMissing(t *testing.T, db *sql.DB, viewName string) {
	t.Helper()

	var count int
	if err := db.QueryRow(`
		SELECT COUNT(*)
		  FROM information_schema.VIEWS
		 WHERE TABLE_SCHEMA = DATABASE()
		   AND TABLE_NAME = ?`,
		viewName,
	).Scan(&count); err != nil {
		t.Fatalf("query view %s: %v", viewName, err)
	}
	if count != 0 {
		t.Fatalf("view %s count = %d, want 0 (expected missing)", viewName, count)
	}
}

func assertViewExists(t *testing.T, db *sql.DB, viewName string) {
	t.Helper()

	var count int
	if err := db.QueryRow(`
		SELECT COUNT(*)
		  FROM information_schema.VIEWS
		 WHERE TABLE_SCHEMA = DATABASE()
		   AND TABLE_NAME = ?`,
		viewName,
	).Scan(&count); err != nil {
		t.Fatalf("query view %s: %v", viewName, err)
	}
	if count != 1 {
		t.Fatalf("view %s count = %d, want 1", viewName, count)
	}
}

func assertViewQueryable(t *testing.T, db *sql.DB, viewName string, fieldName string) {
	t.Helper()

	query := "SELECT `" + fieldName + "` FROM `" + viewName + "` LIMIT 0"
	if _, err := db.Exec(query); err != nil {
		t.Fatalf("query view %s field %s: %v", viewName, fieldName, err)
	}
}

func assertNoActiveFieldRow(t *testing.T, db *sql.DB, objectAPIName string, fieldAPIName string) {
	t.Helper()

	var count int
	if err := db.QueryRow(`
		SELECT COUNT(*)
		  FROM _System_Field f
		  JOIN _System_Object o ON o.Id = f.ObjectId
		 WHERE o.ApiName = ?
		   AND f.ApiName = ?
		   AND COALESCE(o.IsDeleted, 0) = 0
		   AND COALESCE(f.IsDeleted, 0) = 0`,
		objectAPIName,
		fieldAPIName,
	).Scan(&count); err != nil {
		t.Fatalf("query field row %s.%s: %v", objectAPIName, fieldAPIName, err)
	}
	if count != 0 {
		t.Fatalf("field row %s.%s count = %d, want 0", objectAPIName, fieldAPIName, count)
	}
}

func assertColumnMissing(t *testing.T, db *sql.DB, tableName string, columnName string) {
	t.Helper()

	var count int
	if err := db.QueryRow(`
		SELECT COUNT(*)
		  FROM information_schema.COLUMNS
		 WHERE TABLE_SCHEMA = DATABASE()
		   AND TABLE_NAME = ?
		   AND COLUMN_NAME = ?`,
		tableName,
		columnName,
	).Scan(&count); err != nil {
		t.Fatalf("query column %s.%s: %v", tableName, columnName, err)
	}
	if count != 0 {
		t.Fatalf("column %s.%s count = %d, want 0", tableName, columnName, count)
	}
}