package packs

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"sort"
	"strings"

	"github.com/nexuscrm/shared/pkg/models"
)

func newSourceBackedValidationRuleComponent(packName string, component ManifestComponent, payload []byte) (PackComponent, []ValidationRuleBusinessKey, error) {
	expected, seeds, err := sourceBackedValidationRuleProjection(payload, componentPayloadLabel(component), packName)
	if err != nil {
		return nil, nil, err
	}
	keys := make([]ValidationRuleBusinessKey, 0, len(seeds))
	for _, seed := range seeds {
		rule := normalizeValidationRuleSeed(seed)
		if rule.ObjectAPIName != "" && rule.Name != "" {
			keys = append(keys, ValidationRuleBusinessKey{ObjectAPIName: rule.ObjectAPIName, Name: rule.Name})
		}
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: component,
		payload:           payload,
		expectedProjection: func() (any, error) {
			return expected, nil
		},
		liveProjection: func(ctx context.Context, deps VerifyDeps) (any, error) {
			return liveSourceBackedValidationRuleProjection(ctx, deps, expected, packName, componentPayloadLabel(component))
		},
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applyStandardValidationRuleSeeds(ctx, deps.DB, seeds, packName)
		},
	}, keys, nil
}

type sourceBackedValidationRuleRow struct {
	ObjectAPIName     string                                  `json:"objectApiName"`
	Name              string                                  `json:"name"`
	Description       *string                                 `json:"description,omitempty"`
	Active            bool                                    `json:"active"`
	Condition         string                                  `json:"condition"`
	ErrorMessage      string                                  `json:"errorMessage"`
	ErrorDisplayField *string                                 `json:"errorDisplayField,omitempty"`
	RecordSetBindings []models.ValidationRuleRecordSetBinding `json:"recordSetBindings,omitempty"`
	OwnerPack         string                                  `json:"ownerPack"`
}

func sourceBackedValidationRuleProjection(payload []byte, source string, ownerPack string) ([]sourceBackedValidationRuleRow, []models.ValidationRule, error) {
	var data standardValidationRulesData
	if err := json.Unmarshal(payload, &data); err != nil {
		return nil, nil, fmt.Errorf("parse %s: %w", source, err)
	}
	out := make([]sourceBackedValidationRuleRow, 0, len(data.ValidationRules))
	seeds := make([]models.ValidationRule, 0, len(data.ValidationRules))
	for _, seed := range data.ValidationRules {
		rule := normalizeValidationRuleSeed(seed)
		seeds = append(seeds, rule)
		out = append(out, sourceBackedValidationRuleRow{
			ObjectAPIName:     rule.ObjectAPIName,
			Name:              rule.Name,
			Description:       rule.Description,
			Active:            rule.Active,
			Condition:         rule.Condition,
			ErrorMessage:      rule.ErrorMessage,
			ErrorDisplayField: rule.ErrorDisplayField,
			RecordSetBindings: rule.RecordSetBindings,
			OwnerPack:         ownerPack,
		})
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].ObjectAPIName != out[j].ObjectAPIName {
			return out[i].ObjectAPIName < out[j].ObjectAPIName
		}
		return out[i].Name < out[j].Name
	})
	return out, seeds, nil
}

func liveSourceBackedValidationRuleProjection(ctx context.Context, deps VerifyDeps, expected []sourceBackedValidationRuleRow, ownerPack string, source string) (any, error) {
	expectedKeys := make(map[string]struct{}, len(expected))
	live := make([]sourceBackedValidationRuleRow, 0, len(expected))
	for _, want := range expected {
		key := strings.ToLower(want.ObjectAPIName) + "\x00" + strings.ToLower(want.Name)
		expectedKeys[key] = struct{}{}

		var got sourceBackedValidationRuleRow
		var description, errorDisplayField, recordSetBindings, gotOwner sql.NullString
		var isCustomized bool
		err := deps.DB.QueryRowContext(ctx, `
SELECT ObjectApiName, Name, Description, Active, `+"`Condition`"+`, ErrorMessage, ErrorDisplayField, RecordSetBindings, IsCustomized, OwnerPack
FROM _System_ValidationRule
WHERE ObjectApiName = ? AND Name = ?
LIMIT 1`, want.ObjectAPIName, want.Name).Scan(
			&got.ObjectAPIName,
			&got.Name,
			&description,
			&got.Active,
			&got.Condition,
			&got.ErrorMessage,
			&errorDisplayField,
			&recordSetBindings,
			&isCustomized,
			&gotOwner,
		)
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("validation rule %s/%s is missing", want.ObjectAPIName, want.Name)
		}
		if err != nil {
			return nil, fmt.Errorf("query validation rule %s/%s: %w", want.ObjectAPIName, want.Name, err)
		}
		got.Description = sourceBackedNullStringPtr(description)
		got.ErrorDisplayField = sourceBackedNullStringPtr(errorDisplayField)
		if recordSetBindings.Valid && strings.TrimSpace(recordSetBindings.String) != "" {
			if err := json.Unmarshal([]byte(recordSetBindings.String), &got.RecordSetBindings); err != nil {
				return nil, fmt.Errorf("parse live validation rule %s/%s recordSetBindings: %w", want.ObjectAPIName, want.Name, err)
			}
		}
		got.OwnerPack = gotOwner.String
		if got.OwnerPack != ownerPack {
			return nil, fmt.Errorf("validation rule %s/%s OwnerPack = %q, want %q", want.ObjectAPIName, want.Name, got.OwnerPack, ownerPack)
		}
		if isCustomized {
			live = append(live, want)
		} else {
			live = append(live, got)
		}
	}

	rows, err := deps.DB.QueryContext(ctx, `
SELECT ObjectApiName, Name
FROM _System_ValidationRule
WHERE OwnerPack = ? AND Active = 1 AND (IsCustomized = 0 OR IsCustomized IS NULL)
ORDER BY ObjectApiName, Name`, ownerPack)
	if err != nil {
		return nil, fmt.Errorf("query %s-owned validation rules: %w", ownerPack, err)
	}
	defer rows.Close()
	for rows.Next() {
		var objectAPIName, name string
		if err := rows.Scan(&objectAPIName, &name); err != nil {
			return nil, fmt.Errorf("scan validation rule: %w", err)
		}
		key := strings.ToLower(objectAPIName) + "\x00" + strings.ToLower(name)
		if _, ok := expectedKeys[key]; !ok {
			return nil, fmt.Errorf("validation rule %s/%s is active but not declared by %s", objectAPIName, name, source)
		}
	}
	return live, rows.Err()
}