package persistence

import (
	"context"
	"regexp"
	"testing"

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

// TestIsManagedSystemFieldGenericNoObjectBranch proves that IsManagedSystemField
// uses only field metadata properties (type, nullable, createable, updateable)
// — no object-name or field-name hardcoding.
func TestIsManagedSystemFieldGenericNoObjectBranch(t *testing.T) {
	activeMode := "active"
	formulaExpr := "TODAY()"
	returnType := models.FieldType(constants.FieldTypeDate)

	cases := []struct {
		name  string
		field models.FieldMetadata
		want  bool
	}{
		{
			name: "pack_owned_date_readonly_nullable",
			field: models.FieldMetadata{
				APIName:    "LastActivityDate",
				Type:       constants.FieldTypeDate,
				Createable: false,
				Updateable: false,
				Required:   false,
			},
			want: true,
		},
		{
			name: "pack_owned_date_readonly_required",
			field: models.FieldMetadata{
				APIName:    "SomeDate",
				Type:       constants.FieldTypeDate,
				Createable: false,
				Updateable: false,
				Required:   true,
			},
			want: false, // required managed fields are NOT nullable — not managed
		},
		{
			name: "pack_owned_date_createable",
			field: models.FieldMetadata{
				APIName:    "CustomDate",
				Type:       constants.FieldTypeDate,
				Createable: true,
				Updateable: true,
				Required:   false,
			},
			want: false, // createable fields are not managed
		},
		{
			name: "pack_owned_string_readonly",
			field: models.FieldMetadata{
				APIName:    "SomeString",
				Type:       constants.FieldTypeString,
				Createable: false,
				Updateable: false,
				Required:   false,
			},
			want: true, // any non-createable, non-updateable, nullable field is managed
		},
		{
			name: "admin_owned_structural_match",
			field: models.FieldMetadata{
				APIName:    "OwnerField",
				Type:       constants.FieldTypeString,
				Createable: false,
				Updateable: false,
				Required:   false,
			},
			want: true, // structural properties match; ownership is checked separately
		},
		{
			name: "formula_view_only_not_managed",
			field: models.FieldMetadata{
				APIName:     "FormulaField",
				Type:        constants.FieldTypeFormula,
				FormulaMode: &activeMode,
				Formula:     &formulaExpr,
				ReturnType:  &returnType,
				Createable:  false,
				Updateable:  false,
				Required:    false,
			},
			want: false, // formula-backed view-only fields are not physical-column managed
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := IsManagedSystemField(tc.field)
			if got != tc.want {
				t.Fatalf("IsManagedSystemField(%+v) = %v, want %v", tc.field, got, tc.want)
			}
		})
	}
}

// TestIsManagedSystemColumnGenericNoObjectBranch proves the ColumnDefinition
// variant is also generic — no object/field name hardcoding.
func TestIsManagedSystemColumnGenericNoObjectBranch(t *testing.T) {
	cases := []struct {
		name string
		col  schema.ColumnDefinition
		want bool
	}{
		{
			name: "readonly_nullable_date",
			col: schema.ColumnDefinition{
				Name:        "LastActivityDate",
				Type:        "DATE",
				LogicalType: "date",
				Nullable:    true,
			},
			want: true,
		},
		{
			name: "readonly_notnullable_date",
			col: schema.ColumnDefinition{
				Name:        "SomeRequiredDate",
				Type:        "DATE",
				LogicalType: "date",
				Nullable:    false,
			},
			want: false,
		},
		{
			name: "formula_view_only_not_managed",
			col: schema.ColumnDefinition{
				Name:               "FormulaField",
				Type:               "DATE",
				LogicalType:        "formula",
				Formula:            "TODAY()",
				FormulaMode:        "active",
				FormulaStorageMode: "",
				Nullable:           true,
			},
			want: false,
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got := IsManagedSystemColumn(tc.col)
			if got != tc.want {
				t.Fatalf("IsManagedSystemColumn(%+v) = %v, want %v", tc.col, got, tc.want)
			}
		})
	}
}

// buildManagedQueryRegex matches the exact SQL emitted by
// BuildManagedFieldColumnDefinition for _System_Field + _System_Object.
var buildManagedQueryRegex = regexp.QuoteMeta(
	"SELECT f.`Type`, f.`Label`, COALESCE(f.`Required`, 0), COALESCE(f.`IsUnique`, 0), COALESCE(f.`ReferenceTo`, ''), COALESCE(f.`RelationshipName`, ''), COALESCE(f.`FormulaMode`, ''), COALESCE(f.`Formula`, ''), COALESCE(f.`FormulaStorageMode`, ''), COALESCE(f.`ReturnType`, ''), COALESCE(f.`ProjectionConfig`, ''), CASE WHEN COALESCE(f.`Required`, 0) = 0 THEN 1 ELSE 0 END FROM `_System_Field` f JOIN `_System_Object` o ON o.`Id` = f.`ObjectId` WHERE COALESCE(o.`IsDeleted`, 0) = 0 AND COALESCE(f.`IsDeleted`, 0) = 0 AND o.`ApiName` = ? AND f.`ApiName` = ?",
)

func TestBuildManagedFieldColumnDefinitionRejectsNilDB(t *testing.T) {
	_, err := BuildManagedFieldColumnDefinition(context.Background(), nil, "Account", "LastActivityDate")
	if err == nil {
		t.Fatal("BuildManagedFieldColumnDefinition(nil db) = nil error, want unavailable error")
	}
}