package persistence

import (
	"strings"
	"testing"

	"github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/constants"
)

func TestPhysicalForeignKeyDefinitionsOmitsLogicalOnlyRelationships(t *testing.T) {
	definitions := []schema.ForeignKeyDefinition{
		{Column: "PhysicalParentId", References: "PhysicalParent(Id)"},
		{Column: "CatalogParentId", References: "CatalogParent(Id)", LogicalOnly: true},
	}

	physical := physicalForeignKeyDefinitions(definitions)
	if len(physical) != 1 {
		t.Fatalf("physical foreign keys = %+v, want one", physical)
	}
	if physical[0].Column != "PhysicalParentId" || physical[0].References != "PhysicalParent(Id)" {
		t.Fatalf("physical foreign key = %+v, want the physical relationship", physical[0])
	}
	if len(definitions) != 2 || !definitions[1].LogicalOnly {
		t.Fatalf("source definitions were mutated: %+v", definitions)
	}
}

func TestSqlTypeAllowsDefaultClauseExported(t *testing.T) {
	cases := []struct {
		sqlType string
		allow   bool
	}{
		{"VARCHAR(255)", true},
		{"INT", true},
		{"DECIMAL(18,4)", true},
		{"DATETIME", true},
		{"TINYINT(1)", true},
		// MySQL/TiDB rejects DEFAULT on these (Error 1101).
		{"JSON", false},
		{"json", false},
		{"TEXT", false},
		{"LONGTEXT", false},
		{"MEDIUMTEXT", false},
		{"TINYTEXT", false},
		{"BLOB", false},
		{"LONGBLOB", false},
		{"MEDIUMBLOB", false},
		{"TINYBLOB", false},
	}
	for _, tc := range cases {
		t.Run(tc.sqlType, func(t *testing.T) {
			got := SqlTypeAllowsDefaultClause(tc.sqlType)
			if got != tc.allow {
				t.Fatalf("SqlTypeAllowsDefaultClause(%q) = %v, want %v", tc.sqlType, got, tc.allow)
			}
		})
	}
}

func TestResolveSQLTypeMultiSelectPicklistAlias(t *testing.T) {
	got, err := ResolveSQLType("MultiselectPicklist")
	if err != nil {
		t.Fatalf("ResolveSQLType(MultiselectPicklist) = error %v, want nil", err)
	}
	if got != SQLTypeJSON {
		t.Fatalf("ResolveSQLType(MultiselectPicklist) = %q, want %q", got, SQLTypeJSON)
	}
}

func TestLogicalTextStorageMaxBytesMatchesLogicalSQLMapping(t *testing.T) {
	tests := []struct {
		fieldType constants.SchemaFieldType
		want      int64
	}{
		{constants.FieldTypeString, 255},
		{constants.FieldTypeEmail, 255},
		{constants.FieldTypeTextarea, 65535},
		{constants.FieldTypeRichtextarea, 1<<32 - 1},
	}
	for _, test := range tests {
		got, ok := LogicalTextStorageMaxBytes(test.fieldType)
		if !ok || got != test.want {
			t.Fatalf("LogicalTextStorageMaxBytes(%q) = %d, %t; want %d, true", test.fieldType, got, ok, test.want)
		}
	}
	if _, ok := LogicalTextStorageMaxBytes(constants.FieldTypeBoolean); ok {
		t.Fatal("boolean unexpectedly has text storage capacity")
	}
}

func TestResolveSQLTypeBinarySystemColumns(t *testing.T) {
	tests := []struct {
		fieldType string
		want      string
		wantError string
	}{
		{fieldType: "VARBINARY(1024)", want: "VARBINARY(1024)"},
		{fieldType: "binary(32)", want: "BINARY(32)"},
		{fieldType: "mediumblob", want: "MEDIUMBLOB"},
		{fieldType: "VARBINARY(0)", wantError: "must be 1..65535"},
		{fieldType: "BINARY(256)", wantError: "must be 1..255"},
		{fieldType: "VARBINARY(32) DROP TABLE x", wantError: "invalid VARBINARY syntax"},
		{fieldType: "MEDIUMBLOB NOT NULL", wantError: "unknown or unsupported"},
		{fieldType: "LONGBLOB", wantError: "unknown or unsupported"},
	}
	for _, tt := range tests {
		t.Run(tt.fieldType, func(t *testing.T) {
			got, err := ResolveSQLType(tt.fieldType)
			if tt.wantError != "" {
				if err == nil || !strings.Contains(err.Error(), tt.wantError) {
					t.Fatalf("ResolveSQLType(%q) error = %v, want %q", tt.fieldType, err, tt.wantError)
				}
				return
			}
			if err != nil || got != tt.want {
				t.Fatalf("ResolveSQLType(%q) = %q, %v; want %q", tt.fieldType, got, err, tt.want)
			}
		})
	}
}

func TestPlanAddColumnStatementsTwoPhaseForJSONNotNullWithDefault(t *testing.T) {
	stmts := PlanAddColumnStatements("Lead", "Notes", "TEXT", false, "''", "")
	if len(stmts) != 3 {
		t.Fatalf("expected 3-statement two-phase plan for TEXT NOT NULL with default, got %d: %v", len(stmts), stmts)
	}
	if !strings.Contains(stmts[0], "ADD COLUMN `Notes` TEXT") || strings.Contains(stmts[0], "NOT NULL") || strings.Contains(stmts[0], "DEFAULT") {
		t.Fatalf("phase 1 must add nullable without DEFAULT/NOT NULL, got: %q", stmts[0])
	}
	if !strings.Contains(stmts[1], "UPDATE `Lead`") || !strings.Contains(stmts[1], "WHERE `Notes` IS NULL") {
		t.Fatalf("phase 2 must backfill from metadata default, got: %q", stmts[1])
	}
	if !strings.Contains(stmts[2], "MODIFY COLUMN `Notes` TEXT NOT NULL") {
		t.Fatalf("phase 3 must tighten to NOT NULL, got: %q", stmts[2])
	}
}

func TestPlanAddColumnStatementsSinglePhaseForVarcharNotNullWithDefault(t *testing.T) {
	// VARCHAR carries DEFAULT physically, so the safe path is single-statement
	// ADD COLUMN with DEFAULT, plus a defensive backfill UPDATE for any rows
	// that may have been left NULL by an interrupted prior run.
	stmts := PlanAddColumnStatements("Account", "Status", "VARCHAR(64)", false, "'pending'", "")
	if len(stmts) != 2 {
		t.Fatalf("expected 2-statement single-phase plan (ADD + backfill) for VARCHAR NOT NULL with default, got %d: %v", len(stmts), stmts)
	}
	if !strings.Contains(stmts[0], "ADD COLUMN `Status` VARCHAR(64) NOT NULL DEFAULT 'pending'") {
		t.Fatalf("phase 1 must inline NOT NULL + DEFAULT for VARCHAR, got: %q", stmts[0])
	}
	if !strings.Contains(stmts[1], "UPDATE `Account` SET `Status` = 'pending' WHERE `Status` IS NULL") {
		t.Fatalf("phase 2 must defensively backfill, got: %q", stmts[1])
	}
}

func TestPlanAddColumnStatementsSinglePhaseForNullableJSON(t *testing.T) {
	stmts := PlanAddColumnStatements("Lead", "Tags", "JSON", true, "'[]'", "")
	if len(stmts) != 1 {
		t.Fatalf("expected single-statement plan for nullable JSON, got %d: %v", len(stmts), stmts)
	}
	if strings.Contains(stmts[0], "DEFAULT") || strings.Contains(stmts[0], "NOT NULL") {
		t.Fatalf("nullable JSON ADD must drop DEFAULT clause and not add NOT NULL, got: %q", stmts[0])
	}
}

func TestIsAddColumnTwoPhaseDecisionMatrix(t *testing.T) {
	cases := []struct {
		name        string
		nullable    bool
		metaDefault string
		sqlType     string
		want        bool
	}{
		{"json_notnull_with_default", false, "'[]'", "JSON", true},
		{"text_notnull_with_default", false, "'x'", "TEXT", true},
		{"longtext_notnull_with_default", false, "'x'", "LONGTEXT", true},
		{"blob_notnull_with_default", false, "''", "BLOB", true},
		{"json_nullable_with_default", true, "'[]'", "JSON", false},
		{"json_notnull_no_default", false, "", "JSON", false},
		{"varchar_notnull_with_default", false, "'pending'", "VARCHAR(64)", false},
		{"int_notnull_with_default", false, "0", "INT", false},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := IsAddColumnTwoPhase(tc.nullable, tc.metaDefault, tc.sqlType)
			if got != tc.want {
				t.Fatalf("IsAddColumnTwoPhase(nullable=%v, default=%q, type=%q) = %v, want %v",
					tc.nullable, tc.metaDefault, tc.sqlType, got, tc.want)
			}
		})
	}
}

func TestBuildAddColumnInlineDDLIncludesCollation(t *testing.T) {
	got := BuildAddColumnInlineDDL("KeyPrefix", "CHAR(3)", false, "", "utf8mb4_bin")
	if !strings.Contains(got, "COLLATE utf8mb4_bin") {
		t.Fatalf("BuildAddColumnInlineDDL() = %q, want COLLATE utf8mb4_bin", got)
	}
}

func TestBuildIndexDDLSupportsCompositePrimaryKey(t *testing.T) {
	repo := NewSchemaRepository(nil, nil)

	got := repo.buildIndexDDL("_System_ConnectorSchedulerLease", schema.IndexDefinition{
		Columns: []string{"InstalledPackId", "Generation"},
		Primary: true,
	})

	const want = "PRIMARY KEY (`InstalledPackId`, `Generation`)"
	if got != want {
		t.Fatalf("buildIndexDDL() = %q, want %q", got, want)
	}
}