package packs

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
)

func TestDetectStalePackOwnedFieldsFailsClosedOnAbsentNonSystemField(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	defs := []domainschema.TableDefinition{{
		TableName: "Issue",
		Columns: []domainschema.ColumnDefinition{
			{Name: "Subject"},
			{Name: "OwnerId"},
		},
	}}

	mock.ExpectQuery("SELECT o.`ApiName`, f.`ApiName`, COALESCE\\(f.`OwnerKind`, ''\\), COALESCE\\(f.`OwnerPack`, ''\\), COALESCE\\(f.`IsCustomized`, 0\\), f.`MaxLength` FROM `_System_Field`").
		WillReturnRows(sqlmock.NewRows([]string{"APIName", "ApiName", "OwnerKind", "OwnerPack", "IsCustomized", "MaxLength"}).
			AddRow("Issue", "OwnerId", "pack", "jira-like-sample", 0, nil).
			AddRow("Issue", "LegacyField__c", "pack", "jira-like-sample", 0, nil))

	err = detectStalePackOwnedFields(db, "jira-like-sample", defs)
	if err == nil {
		t.Fatal("detectStalePackOwnedFields = nil, want destructive-migration error")
	}
	if !strings.Contains(err.Error(), "Issue.LegacyField__c requires explicit destructive migration") {
		t.Fatalf("detectStalePackOwnedFields error = %v, want explicit destructive migration message", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}

func TestVerifyPackObjectPhysicalSchemaRejectsPackOwnedFieldDrift(t *testing.T) {
	tests := []struct {
		name string
		// columns is the pack-declared physical schema for Issue.
		columns []domainschema.ColumnDefinition
		// fieldRow is the live _System_Field row (ApiName, ApiName, OwnerKind,
		// OwnerPack, IsCustomized, MaxLength); physicalColumnRow is the live
		// INFORMATION_SCHEMA.COLUMNS row.
		fieldRow          []driver.Value
		physicalColumnRow []driver.Value
		wantErr           string
	}{
		{
			name:              "ExtraLivePackOwnedField",
			fieldRow:          []driver.Value{"Issue", "LegacyField__c", "pack", "jira-like-sample", 0, nil},
			physicalColumnRow: []driver.Value{"Issue", "LegacyField__c", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}},
			wantErr:           "extra live pack-owned field",
		},
		{
			name: "PackOwnedMaxLengthDrift",
			columns: []domainschema.ColumnDefinition{{
				Name:     "Subject",
				Type:     "VARCHAR(255)",
				Nullable: true,
			}},
			fieldRow:          []driver.Value{"Issue", "Subject", "pack", "jira-like-sample", 0, 12},
			physicalColumnRow: []driver.Value{"Issue", "Subject", "varchar(255)", "YES", sql.NullString{}, sql.NullString{}},
			wantErr:           "MaxLength",
		},
	}
	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			if err != nil {
				t.Fatalf("sqlmock: %v", err)
			}
			defer db.Close()

			expectVerifyPackObjectPhysicalSchemaBaseQueries(mock, tc.fieldRow, tc.physicalColumnRow)

			err = verifyPackObjectPhysicalSchema(db, "jira-like-sample", []domainschema.TableDefinition{{
				TableName: "Issue",
				TableType: "standard_object",
				Category:  "jira",
				Columns:   tc.columns,
			}}, ManageabilityLocked)
			if err == nil {
				t.Fatalf("verifyPackObjectPhysicalSchema = nil, want drift error containing %q", tc.wantErr)
			}
			if !strings.Contains(err.Error(), tc.wantErr) {
				t.Fatalf("verifyPackObjectPhysicalSchema error = %v, want %q", err, tc.wantErr)
			}
			if err := mock.ExpectationsWereMet(); err != nil {
				t.Fatalf("sql expectations: %v", err)
			}
		})
	}
}

func TestVerifyPackObjectPhysicalSchemaUsesAuthoredTextareaMaxLength(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()

	expectVerifyPackObjectPhysicalSchemaBaseQueries(mock, []driver.Value{
		"Issue", "Description__c", "pack", "jira-like-sample", 0, 255,
	}, []driver.Value{
		"Issue", "Description__c", "text", "YES", nil, nil,
	})

	defs := []domainschema.TableDefinition{{
		TableName: "Issue",
		TableType: "standard_object",
		Category:  "jira",
		Columns: []domainschema.ColumnDefinition{{
			Name:        "Description__c",
			Type:        "TEXT",
			LogicalType: "textarea",
			Nullable:    true,
		}},
	}}
	authoredFields := newObjectSpecAuthoredFieldSet([]ObjectSpec{{
		APIName:   "Issue",
		TableType: "standard_object",
		Fields: []FieldSpec{{
			APIName: "Description__c",
			Type:    "textarea",
			Length:  255,
		}},
	}})

	err = verifyPackObjectPhysicalSchemaWithContextAndAuthoredFields(
		context.Background(), db, "jira-like-sample", defs, ManageabilityLocked, authoredFields,
	)
	if err != nil {
		t.Fatalf("verifyPackObjectPhysicalSchemaWithContextAndAuthoredFields: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("sql expectations: %v", err)
	}
}