package packs

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
)

// StandardActionPersistenceDriftError is a deterministic difference between
// the source-backed invocable-action declaration and its live persisted rows.
// Operational query/scan failures are deliberately not wrapped in this type,
// so ApplyAll can replay a stale persistence-semantic projection without
// treating database failures as repair authority.
type StandardActionPersistenceDriftError struct {
	Details []string
}

func (e *StandardActionPersistenceDriftError) Error() string {
	if e == nil || len(e.Details) == 0 {
		return "standard actions drift"
	}
	return fmt.Sprintf("standard actions drift:\n  - %s", strings.Join(e.Details, "\n  - "))
}

func IsStandardActionPersistenceDrift(err error) bool {
	var drift *StandardActionPersistenceDriftError
	return errors.As(err, &drift)
}

// VerifyStandardActionsForOwner checks every metadata surface written from
// standard_actions.json. The payload is included in the CRM app-pack hash, so
// verify must cover the parent row plus every satellite table written by
// InitializeStandardActionsForOwnerFromPayload. Admin-customized rows keep
// identity but skip editable payload comparison, matching the writer's
// IsCustomized contract.
func VerifyStandardActionsForOwnerFromPayload(db *sql.DB, ownerPack string, raw []byte) error {
	defs, err := ParseStandardActionDefs(raw)
	if err != nil {
		return err
	}
	if err := ValidateStandardActionDefs(defs); err != nil {
		return err
	}
	if len(defs) == 0 {
		return nil
	}
	expected := make(map[string]StandardActionDef, len(defs))
	var errs []string
	for _, def := range defs {
		expected[strings.ToLower(def.Name)] = def
		var label, flowDeveloperName string
		var description, category, requiredPermissions, targetObjects, rendererID, gotOwnerPack sql.NullString
		var visibilityCondition, previewFlowDeveloperName sql.NullString
		var headerFallbackEligible bool
		var isAtomic, sortOrder int
		var isCustomized, isActive, isDeleted bool
		err := db.QueryRow(fmt.Sprintf(`
	SELECT Label, Description, FlowDeveloperName, Category, RequiredPermissions, TargetObjects, RendererId,
	       IsAtomic, SortOrder, IsCustomized, IsActive, IsDeleted, OwnerPack, VisibilityCondition,
	       PreviewFlowDeveloperName, HeaderFallbackEligible
FROM %s
WHERE Name = ?
LIMIT 1`, tableStandardAction), def.Name).Scan(
			&label,
			&description,
			&flowDeveloperName,
			&category,
			&requiredPermissions,
			&targetObjects,
			&rendererID,
			&isAtomic,
			&sortOrder,
			&isCustomized,
			&isActive,
			&isDeleted,
			&gotOwnerPack,
			&visibilityCondition,
			&previewFlowDeveloperName,
			&headerFallbackEligible,
		)
		if err == sql.ErrNoRows {
			errs = append(errs, fmt.Sprintf("standard action %s is missing", def.Name))
			continue
		}
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("query standard action %q", def.Name), err)
		}
		if !isActive || isDeleted {
			errs = append(errs, fmt.Sprintf("standard action %s is inactive/deleted", def.Name))
			continue
		}
		if ownerPack != "" && gotOwnerPack.String != ownerPack {
			errs = append(errs, fmt.Sprintf("standard action %s OwnerPack = %q, want %q", def.Name, gotOwnerPack.String, ownerPack))
			continue
		}
		if strings.TrimSpace(rendererID.String) != strings.TrimSpace(def.RendererID) {
			errs = append(errs, fmt.Sprintf("standard action %s RendererId = %q, want %q", def.Name, rendererID.String, def.RendererID))
		}
		if !isCustomized {
			wantDescription := strings.TrimSpace(def.Description)
			if label != def.Label {
				errs = append(errs, fmt.Sprintf("standard action %s Label = %q, want %q", def.Name, label, def.Label))
			}
			if strings.TrimSpace(description.String) != wantDescription {
				errs = append(errs, fmt.Sprintf("standard action %s Description drift", def.Name))
			}
			if flowDeveloperName != def.FlowDeveloperName {
				errs = append(errs, fmt.Sprintf("standard action %s FlowDeveloperName = %q, want %q", def.Name, flowDeveloperName, def.FlowDeveloperName))
			}
			if strings.TrimSpace(category.String) != strings.TrimSpace(def.Category) {
				errs = append(errs, fmt.Sprintf("standard action %s Category = %q, want %q", def.Name, category.String, def.Category))
			}
			if canonicalJSONStringFromSlice(def.RequiredPermissions) != canonicalJSONString(requiredPermissions.String) {
				errs = append(errs, fmt.Sprintf("standard action %s RequiredPermissions drift", def.Name))
			}
			if canonicalJSONStringFromSlice(def.TargetObjects) != canonicalJSONString(targetObjects.String) {
				errs = append(errs, fmt.Sprintf("standard action %s TargetObjects drift", def.Name))
			}
			if (isAtomic != 0) != def.IsAtomic {
				errs = append(errs, fmt.Sprintf("standard action %s IsAtomic = %t, want %t", def.Name, isAtomic != 0, def.IsAtomic))
			}
			if sortOrder != def.SortOrder {
				errs = append(errs, fmt.Sprintf("standard action %s SortOrder = %d, want %d", def.Name, sortOrder, def.SortOrder))
			}
			wantVC := ""
			if def.VisibilityCondition != nil {
				wantVC = *def.VisibilityCondition
			}
			if strings.TrimSpace(visibilityCondition.String) != strings.TrimSpace(wantVC) {
				errs = append(errs, fmt.Sprintf("standard action %s VisibilityCondition drift", def.Name))
			}
			// PreviewFlowDeveloperName is optional. nil/empty in the pack means
			// "no preview producer"; the column is then NULL or empty string.
			// Both pack-side and live-side are normalized via TrimSpace so a
			// nil pointer and a NULL/empty column compare equal.
			wantPreviewFlow := ""
			if def.PreviewFlowDeveloperName != nil {
				wantPreviewFlow = *def.PreviewFlowDeveloperName
			}
			if strings.TrimSpace(previewFlowDeveloperName.String) != strings.TrimSpace(wantPreviewFlow) {
				errs = append(errs, fmt.Sprintf("standard action %s PreviewFlowDeveloperName = %q, want %q",
					def.Name, previewFlowDeveloperName.String, wantPreviewFlow))
			}
			if wantHeaderFallbackEligible := standardActionHeaderFallbackEligible(def); headerFallbackEligible != wantHeaderFallbackEligible {
				errs = append(errs, fmt.Sprintf("standard action %s HeaderFallbackEligible = %t, want %t",
					def.Name, headerFallbackEligible, wantHeaderFallbackEligible))
			}
		}
		actionID, err := loadPersistedStandardActionID(db, def.Name)
		if err != nil {
			return err
		}
		if err := verifyStandardActionSchemaRows(db, &errs, actionID, def); err != nil {
			return err
		}
		if err := verifyStandardActionFieldDefaultRows(db, &errs, actionID, def); err != nil {
			return err
		}
		if err := verifyStandardActionInputGuardRows(db, &errs, actionID, def); err != nil {
			return err
		}
		if err := verifyStandardActionInternalAccessScopeRows(db, &errs, actionID, def); err != nil {
			return err
		}
	}
	query := fmt.Sprintf(`
SELECT Name
FROM %s
WHERE IsActive = 1 AND IsDeleted = 0 AND (IsCustomized = 0 OR IsCustomized IS NULL)`, tableStandardAction)
	args := []interface{}{}
	if ownerPack != "" {
		query += " AND OwnerPack = ?"
		args = append(args, ownerPack)
	}
	rows, err := db.Query(query, args...)
	if err != nil {
		return apperrors.NewInternalError("query standard action pack rows", err)
	}
	defer rows.Close()
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return apperrors.NewInternalError("scan standard action pack row", err)
		}
		if _, ok := expected[strings.ToLower(name)]; !ok {
			errs = append(errs, fmt.Sprintf("standard action %s is active but not declared by standard_actions.json", name))
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}

	if len(errs) > 0 {
		return &StandardActionPersistenceDriftError{Details: append([]string(nil), errs...)}
	}
	return nil
}

func verifyStandardActionSchemaRows(db *sql.DB, errs *[]string, actionID string, def StandardActionDef) error {
	expected := make(map[string]StandardActionSchemaDef)
	for _, row := range def.Inputs {
		expected["input\x00"+row.APIName] = row
	}
	for _, row := range def.Outputs {
		expected["output\x00"+row.APIName] = row
	}

	for key, want := range expected {
		parts := strings.Split(key, "\x00")
		direction := parts[0]
		var dataType string
		var referenceTo, label, uiSection, uiMode, targetObject, targetField, prefillSourceFieldAPIName, requiredPicklistTag, mappingSourceObject, mappingActionType, description sql.NullString
		var isCollection, isRequired, sortOrder int
		var isCustomized, isActive, isDeleted bool
		err := db.QueryRow(fmt.Sprintf(`
SELECT DataType, ReferenceTo, Label, UISection, UIMode, TargetObject, TargetFieldAPIName,
       PrefillSourceFieldAPIName, RequiredPicklistTag, MappingSourceObject, MappingActionType, IsCollection, IsRequired, Description,
       SortOrder, IsCustomized, IsActive, IsDeleted
FROM %s
WHERE StandardActionId = ? AND Direction = ? AND APIName = ?
LIMIT 1`, tableActionSchema), actionID, direction, want.APIName).Scan(
			&dataType, &referenceTo, &label, &uiSection, &uiMode, &targetObject, &targetField,
			&prefillSourceFieldAPIName, &requiredPicklistTag, &mappingSourceObject, &mappingActionType, &isCollection, &isRequired, &description,
			&sortOrder, &isCustomized, &isActive, &isDeleted,
		)
		if err == sql.ErrNoRows {
			*errs = append(*errs, fmt.Sprintf("standard action %s schema %s/%s is missing", def.Name, direction, want.APIName))
			continue
		}
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("query standard action %s schema %s/%s", def.Name, direction, want.APIName), err)
		}
		if !isActive || isDeleted {
			*errs = append(*errs, fmt.Sprintf("standard action %s schema %s/%s is inactive/deleted", def.Name, direction, want.APIName))
			continue
		}
		if isCustomized {
			continue
		}
		if dataType != want.DataType {
			*errs = append(*errs, fmt.Sprintf("standard action %s schema %s/%s DataType = %q, want %q", def.Name, direction, want.APIName, dataType, want.DataType))
		}
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s ReferenceTo", def.Name, direction, want.APIName), referenceTo, want.ReferenceTo)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s Label", def.Name, direction, want.APIName), label, want.Label)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s UISection", def.Name, direction, want.APIName), uiSection, want.UISection)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s UIMode", def.Name, direction, want.APIName), uiMode, want.UIMode)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s TargetObject", def.Name, direction, want.APIName), targetObject, want.TargetObject)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s TargetFieldAPIName", def.Name, direction, want.APIName), targetField, want.TargetFieldAPIName)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s PrefillSourceFieldAPIName", def.Name, direction, want.APIName), prefillSourceFieldAPIName, want.PrefillSourceFieldAPIName)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s RequiredPicklistTag", def.Name, direction, want.APIName), requiredPicklistTag, want.RequiredPicklistTag)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s MappingSourceObject", def.Name, direction, want.APIName), mappingSourceObject, want.MappingSourceObject)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s MappingActionType", def.Name, direction, want.APIName), mappingActionType, want.MappingActionType)
		compareNullString(errs, fmt.Sprintf("standard action %s schema %s/%s Description", def.Name, direction, want.APIName), description, want.Description)
		if (isCollection != 0) != want.IsCollection {
			*errs = append(*errs, fmt.Sprintf("standard action %s schema %s/%s IsCollection = %t, want %t", def.Name, direction, want.APIName, isCollection != 0, want.IsCollection))
		}
		if (isRequired != 0) != want.IsRequired {
			*errs = append(*errs, fmt.Sprintf("standard action %s schema %s/%s IsRequired = %t, want %t", def.Name, direction, want.APIName, isRequired != 0, want.IsRequired))
		}
		if sortOrder != want.SortOrder {
			*errs = append(*errs, fmt.Sprintf("standard action %s schema %s/%s SortOrder = %d, want %d", def.Name, direction, want.APIName, sortOrder, want.SortOrder))
		}
	}
	if err := verifyNoStaleStandardActionRows(db, errs, tableActionSchema, actionID, expected, true, func(rows *sql.Rows) (string, string, error) {
		var direction, apiName string
		if err := rows.Scan(&direction, &apiName); err != nil {
			return "", "", err
		}
		return direction + "\x00" + apiName, direction + "/" + apiName, nil
	}, "Direction, APIName", def.Name, "schema"); err != nil {
		return err
	}
	return nil
}

func verifyStandardActionFieldDefaultRows(db *sql.DB, errs *[]string, actionID string, def StandardActionDef) error {
	expected := make(map[string]StandardActionDefaultDef, len(def.FieldDefaults))
	for _, row := range def.FieldDefaults {
		expected[row.TargetObject+"\x00"+row.FieldAPIName+"\x00"+fmt.Sprint(row.Priority)] = row
	}
	for _, want := range expected {
		var valueFormula string
		var condition sql.NullString
		var isCustomized, isActive, isDeleted bool
		err := db.QueryRow(fmt.Sprintf(`
SELECT ValueFormula, `+"`Condition`"+`, IsCustomized, IsActive, IsDeleted
FROM %s
WHERE StandardActionId = ? AND TargetObject = ? AND FieldAPIName = ? AND Priority = ?
LIMIT 1`, tableActionFieldDefault), actionID, want.TargetObject, want.FieldAPIName, want.Priority).Scan(
			&valueFormula, &condition, &isCustomized, &isActive, &isDeleted,
		)
		if err == sql.ErrNoRows {
			*errs = append(*errs, fmt.Sprintf("standard action %s field default %s.%s/%d is missing", def.Name, want.TargetObject, want.FieldAPIName, want.Priority))
			continue
		}
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("query standard action %s field default %s.%s/%d", def.Name, want.TargetObject, want.FieldAPIName, want.Priority), err)
		}
		if !isActive || isDeleted {
			*errs = append(*errs, fmt.Sprintf("standard action %s field default %s.%s/%d is inactive/deleted", def.Name, want.TargetObject, want.FieldAPIName, want.Priority))
			continue
		}
		if isCustomized {
			continue
		}
		if valueFormula != want.ValueFormula {
			*errs = append(*errs, fmt.Sprintf("standard action %s field default %s.%s/%d ValueFormula drift", def.Name, want.TargetObject, want.FieldAPIName, want.Priority))
		}
		compareNullString(errs, fmt.Sprintf("standard action %s field default %s.%s/%d Condition", def.Name, want.TargetObject, want.FieldAPIName, want.Priority), condition, want.Condition)
	}
	if err := verifyNoStaleStandardActionRows(db, errs, tableActionFieldDefault, actionID, expected, true, func(rows *sql.Rows) (string, string, error) {
		var targetObject, fieldAPIName string
		var priority int
		if err := rows.Scan(&targetObject, &fieldAPIName, &priority); err != nil {
			return "", "", err
		}
		return targetObject + "\x00" + fieldAPIName + "\x00" + fmt.Sprint(priority), targetObject + "." + fieldAPIName + "/" + fmt.Sprint(priority), nil
	}, "TargetObject, FieldAPIName, Priority", def.Name, "field default"); err != nil {
		return err
	}
	return nil
}

func verifyStandardActionInputGuardRows(db *sql.DB, errs *[]string, actionID string, def StandardActionDef) error {
	expected := make(map[string]StandardActionGuardDef, len(def.InputGuards))
	for _, row := range def.InputGuards {
		guardKey := actionInputGuardKey(row)
		expected[actionInputGuardIdentityKey(row.Condition, guardKey)] = row
	}
	for key, want := range expected {
		parts := strings.Split(key, "\x00")
		guardKey := parts[1]
		var requiredPermission, requiredObject, requiredOperation, errorMessage, gatedInput sql.NullString
		var sortOrder int
		var isCustomized, isActive, isDeleted bool
		err := db.QueryRow(fmt.Sprintf(`
SELECT RequiredPermission, RequiredObject, RequiredOperation, ErrorMessage, GatedInput,
       SortOrder, IsCustomized, IsActive, IsDeleted
FROM %s
WHERE StandardActionId = ? AND `+"`Condition`"+` = ? AND GuardKey = ?
LIMIT 1`, tableActionInputGuard), actionID, want.Condition, guardKey).Scan(
			&requiredPermission, &requiredObject, &requiredOperation, &errorMessage, &gatedInput,
			&sortOrder, &isCustomized, &isActive, &isDeleted,
		)
		if err == sql.ErrNoRows {
			*errs = append(*errs, fmt.Sprintf("standard action %s input guard %s/%s is missing", def.Name, want.Condition, guardKey))
			continue
		}
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("query standard action %s input guard %s/%s", def.Name, want.Condition, guardKey), err)
		}
		if !isActive || isDeleted {
			*errs = append(*errs, fmt.Sprintf("standard action %s input guard %s/%s is inactive/deleted", def.Name, want.Condition, guardKey))
			continue
		}
		if isCustomized {
			continue
		}
		compareNullString(errs, fmt.Sprintf("standard action %s input guard %s/%s RequiredPermission", def.Name, want.Condition, guardKey), requiredPermission, want.RequiredPermission)
		compareNullString(errs, fmt.Sprintf("standard action %s input guard %s/%s RequiredObject", def.Name, want.Condition, guardKey), requiredObject, want.RequiredObject)
		compareNullString(errs, fmt.Sprintf("standard action %s input guard %s/%s RequiredOperation", def.Name, want.Condition, guardKey), requiredOperation, want.RequiredOperation)
		compareNullString(errs, fmt.Sprintf("standard action %s input guard %s/%s ErrorMessage", def.Name, want.Condition, guardKey), errorMessage, want.ErrorMessage)
		compareNullString(errs, fmt.Sprintf("standard action %s input guard %s/%s GatedInput", def.Name, want.Condition, guardKey), gatedInput, want.GatedInput)
		if sortOrder != want.SortOrder {
			*errs = append(*errs, fmt.Sprintf("standard action %s input guard %s/%s SortOrder = %d, want %d", def.Name, want.Condition, guardKey, sortOrder, want.SortOrder))
		}
	}
	if err := verifyNoStaleStandardActionRows(db, errs, tableActionInputGuard, actionID, expected, true, func(rows *sql.Rows) (string, string, error) {
		var condition, guardKey string
		if err := rows.Scan(&condition, &guardKey); err != nil {
			return "", "", err
		}
		return actionInputGuardIdentityKey(condition, guardKey), condition + "/" + guardKey, nil
	}, "`Condition`, GuardKey", def.Name, "input guard"); err != nil {
		return err
	}
	return nil
}

func verifyStandardActionInternalAccessScopeRows(db *sql.DB, errs *[]string, actionID string, def StandardActionDef) error {
	expected := make(map[string]StandardActionInternalAccessScopeDef, len(def.InternalAccessScopes))
	for _, row := range def.InternalAccessScopes {
		expected[row.ObjectAPIName+"\x00"+row.Operation] = row
	}
	for _, want := range expected {
		var reason sql.NullString
		var isActive, isDeleted bool
		err := db.QueryRow(fmt.Sprintf(`
SELECT Reason, IsActive, IsDeleted
FROM %s
WHERE StandardActionId = ? AND ObjectApiName = ? AND Operation = ?
LIMIT 1`, tableStandardActionInternalAccessScope), actionID, want.ObjectAPIName, want.Operation).Scan(
			&reason, &isActive, &isDeleted,
		)
		if err == sql.ErrNoRows {
			*errs = append(*errs, fmt.Sprintf("standard action %s internal access scope %s/%s is missing", def.Name, want.ObjectAPIName, want.Operation))
			continue
		}
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("query standard action %s internal access scope %s/%s", def.Name, want.ObjectAPIName, want.Operation), err)
		}
		if !isActive || isDeleted {
			*errs = append(*errs, fmt.Sprintf("standard action %s internal access scope %s/%s is inactive/deleted", def.Name, want.ObjectAPIName, want.Operation))
			continue
		}
		compareNullString(errs, fmt.Sprintf("standard action %s internal access scope %s/%s Reason", def.Name, want.ObjectAPIName, want.Operation), reason, want.Reason)
	}
	if err := verifyNoStaleStandardActionRows(db, errs, tableStandardActionInternalAccessScope, actionID, expected, false, func(rows *sql.Rows) (string, string, error) {
		var objectAPIName, operation string
		if err := rows.Scan(&objectAPIName, &operation); err != nil {
			return "", "", err
		}
		return objectAPIName + "\x00" + operation, objectAPIName + "/" + operation, nil
	}, "ObjectApiName, Operation", def.Name, "internal access scope"); err != nil {
		return err
	}
	return nil
}

func verifyNoStaleStandardActionRows[T any](
	db *sql.DB,
	errs *[]string,
	tableName string,
	actionID string,
	expected map[string]T,
	hasIsCustomized bool,
	scanKey func(*sql.Rows) (string, string, error),
	selectColumns string,
	actionName string,
	label string,
) error {
	customizedPredicate := ""
	if hasIsCustomized {
		customizedPredicate = " AND (IsCustomized = 0 OR IsCustomized IS NULL)"
	}
	rows, err := db.Query(fmt.Sprintf(`
SELECT %s
FROM %s
WHERE StandardActionId = ? AND IsActive = 1 AND IsDeleted = 0%s`, selectColumns, tableName, customizedPredicate), actionID)
	if err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("query standard action %s stale %s rows", actionName, label), err)
	}
	defer rows.Close()
	for rows.Next() {
		key, display, err := scanKey(rows)
		if err != nil {
			return apperrors.NewInternalError(fmt.Sprintf("scan standard action %s stale %s row", actionName, label), err)
		}
		if _, ok := expected[key]; !ok {
			*errs = append(*errs, fmt.Sprintf("standard action %s %s %s is active but not declared by standard_actions.json", actionName, label, display))
		}
	}
	if err := rows.Err(); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("iterate standard action %s stale %s rows", actionName, label), err)
	}
	return nil
}

func compareNullString(errs *[]string, label string, got sql.NullString, want string) {
	gotValue := ""
	if got.Valid {
		gotValue = strings.TrimSpace(got.String)
	}
	wantValue := strings.TrimSpace(want)
	if gotValue != wantValue {
		*errs = append(*errs, fmt.Sprintf("%s = %q, want %q", label, gotValue, wantValue))
	}
}

func canonicalJSONStringFromSlice(values []string) string {
	if len(values) == 0 {
		return "[]"
	}
	raw, _ := json.Marshal(values)
	return canonicalJSONString(string(raw))
}

func canonicalJSONString(raw string) string {
	if strings.TrimSpace(raw) == "" {
		return "[]"
	}
	var value any
	if err := json.Unmarshal([]byte(raw), &value); err != nil {
		return strings.TrimSpace(raw)
	}
	out, _ := json.Marshal(value)
	return string(out)
}