package packs

import (
	"context"
	"encoding/json"
	"fmt"
	"regexp"
	"strings"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/shared/pkg/models"
)

func TestApplyStandardListFieldsSkipsCustomizedRows(t *testing.T) {
	var listFieldsByObject map[string][]string
	if err := json.Unmarshal(standardListFieldsJSON, &listFieldsByObject); err != nil {
		t.Fatalf("parse standard list fields: %v", err)
	}

	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	if err != nil {
		t.Fatalf("sqlmock: %v", err)
	}
	defer db.Close()
	mock.MatchExpectationsInOrder(false)

	pattern := regexp.QuoteMeta(
		"UPDATE `_System_Object` SET `ListFields` = ? WHERE `ApiName` = ? AND (`ListFields` IS NULL OR `ListFields` = '' OR `ListFields` = '[]') AND (`IsCustomized` = 0 OR `IsCustomized` IS NULL)",
	)
	for objectApiName := range listFieldsByObject {
		mock.ExpectExec(pattern).
			WithArgs(sqlmock.AnyArg(), objectApiName).
			WillReturnResult(sqlmock.NewResult(0, 0))
	}

	applyStandardListFields(db)

	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectExec(`UPDATE _System_ValidationRule\s+SET IsCustomized = 1\s+WHERE \(IsCustomized = 0 OR IsCustomized IS NULL\)\s+AND LEFT\(Id, \?\) <> \?\s+AND Id NOT IN \(\?\)\s+AND NOT \(\(ObjectApiName = \? AND Name = \?\)\)`).
		WithArgs(len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix, "vrule_lead_post_convert_read_only", "Lead", "Lead_Post_Convert_Readonly").
		WillReturnResult(sqlmock.NewResult(0, 0))
	mock.ExpectExec(`INSERT INTO _System_ValidationRule`).
		WithArgs(
			"vrule_lead_post_convert_read_only",
			"Lead",
			"Lead_Post_Convert_Readonly",
			"Prevents edits to converted Lead records through generic validation metadata.",
			true,
			`PRIORVALUE(IsConverted) = TRUE`,
			"This lead has already been converted. No further changes are allowed.",
			nil,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(`UPDATE _System_ValidationRule\s+SET Active = 0, LastModifiedDate = CURRENT_TIMESTAMP\s+WHERE \(IsCustomized = 0 OR IsCustomized IS NULL\)\s+AND LEFT\(Id, \?\) = \?\s+AND Active = 1\s+AND Id NOT IN \(\?\)`).
		WithArgs(len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix, "vrule_lead_post_convert_read_only").
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := applyStandardValidationRules(context.Background(), db); err != nil {
		t.Fatalf("applyStandardValidationRules: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectExec(`UPDATE _System_ValidationRule\s+SET IsCustomized = 1\s+WHERE \(IsCustomized = 0 OR IsCustomized IS NULL\)\s+AND LEFT\(Id, \?\) <> \?\s+AND Id NOT IN \(\?\)\s+AND NOT \(\(ObjectApiName = \? AND Name = \?\)\)`).
		WithArgs(len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix, "vrule_lead_post_convert_read_only", "Lead", "Lead_Post_Convert_Readonly").
		WillReturnResult(sqlmock.NewResult(0, 3))
	mock.ExpectExec(`INSERT INTO _System_ValidationRule`).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(`UPDATE _System_ValidationRule\s+SET Active = 0, LastModifiedDate = CURRENT_TIMESTAMP\s+WHERE \(IsCustomized = 0 OR IsCustomized IS NULL\)\s+AND LEFT\(Id, \?\) = \?\s+AND Active = 1\s+AND Id NOT IN \(\?\)`).
		WithArgs(len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix, "vrule_lead_post_convert_read_only").
		WillReturnResult(sqlmock.NewResult(0, 1))

	if err := applyStandardValidationRules(context.Background(), db); err != nil {
		t.Fatalf("applyStandardValidationRules: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	mock.ExpectExec(`UPDATE _System_ValidationRule\s+SET IsCustomized = 1\s+WHERE \(IsCustomized = 0 OR IsCustomized IS NULL\)\s+AND LEFT\(Id, \?\) <> \?`).
		WithArgs(len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix).
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectExec(`UPDATE _System_ValidationRule\s+SET Active = 0, LastModifiedDate = CURRENT_TIMESTAMP\s+WHERE \(IsCustomized = 0 OR IsCustomized IS NULL\)\s+AND LEFT\(Id, \?\) = \?\s+AND Active = 1`).
		WithArgs(len(standardValidationRuleIDPrefix), standardValidationRuleIDPrefix).
		WillReturnResult(sqlmock.NewResult(0, 2))

	if err := applyStandardValidationRuleSeeds(context.Background(), db, nil); err != nil {
		t.Fatalf("applyStandardValidationRuleSeeds: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatalf("unmet expectations: %v", err)
	}
}

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

	err = applyStandardValidationRuleSeeds(context.Background(), db, []models.ValidationRule{{
		ID:            "admin_like_id",
		ObjectAPIName: "Lead",
		Name:          "Bad_Seed_ID",
		Active:        true,
		Condition:     "TRUE",
		ErrorMessage:  "bad seed",
	}})
	if err == nil || !strings.Contains(err.Error(), "must use vrule_ prefix") {
		t.Fatalf("expected prefix error, got %v", err)
	}
}

func validationRuleConvergentMatcher(expectedSQL, actualSQL string) error {
	if strings.Contains(expectedSQL, "INSERT INTO _System_ValidationRule") {
		duplicateStart := strings.LastIndex(strings.ToUpper(actualSQL), "ON DUPLICATE KEY UPDATE")
		if duplicateStart == -1 {
			return fmt.Errorf("standard validation rule seed must use upsert: %s", actualSQL)
		}
		duplicateClause := actualSQL[duplicateStart:]
		for _, field := range []string{
			"ObjectApiName",
			"Name",
			"Description",
			"Active",
			"Condition",
			"ErrorMessage",
			"ErrorDisplayField",
		} {
			pattern := fmt.Sprintf("(?is)`?%s`?\\s*=\\s*IF\\(\\s*IsCustomized\\s*=\\s*0\\s*,", regexp.QuoteMeta(field))
			if !regexp.MustCompile(pattern).MatchString(duplicateClause) {
				return fmt.Errorf("standard validation rule upsert must guard %s with IsCustomized=0, got: %s", field, duplicateClause)
			}
		}
		if regexp.MustCompile(`(?is)(^|,)\s*IsCustomized\s*=`).MatchString(duplicateClause) {
			return fmt.Errorf("standard validation rule upsert must not rewrite IsCustomized, got: %s", duplicateClause)
		}
	}
	return sqlmock.QueryMatcherRegexp.Match(expectedSQL, actualSQL)
}