package packs

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

	"github.com/nexuscrm/automation/autonumber"
	"github.com/pingcap-inc/nexuscrm-contract/pkg/autonumberseed"
	"github.com/pingcap-inc/nexuscrm-controlplane/internal/offlinecandidateauthority"
)

func newSourceBackedAutoNumberFormatComponent(packName string, component ManifestComponent, payload []byte) (PackComponent, error) {
	if strings.TrimSpace(packName) == "" {
		return nil, fmt.Errorf("source-backed pack: empty manifest name is forbidden for auto-number component")
	}
	formats, expected, err := sourceBackedAutoNumberFormatProjection(payload, componentPayloadLabel(component))
	if err != nil {
		return nil, err
	}
	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 liveSourceBackedAutoNumberFormatProjection(ctx, deps, packName, expected)
		},
		apply: func(_ context.Context, deps ApplyDeps) error {
			registerAutoNumberDeclaredSeedsFromFormats(formats)
			return ApplyAutoNumberFormatSeeds(deps.DB, packName, formats)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			registerAutoNumberDeclaredSeedsFromFormats(formats)
			return verifyPackComponentDefault(ctx, deps, &sourceBackedManagedComponent{
				packName:           packName,
				manifestComponent:  component,
				payload:            payload,
				expectedProjection: func() (any, error) { return expected, nil },
				liveProjection: func(ctx context.Context, deps VerifyDeps) (any, error) {
					return liveSourceBackedAutoNumberFormatProjection(ctx, deps, packName, expected)
				},
			})
		},
	}, nil
}

type sourceBackedAutoNumberFormatRow struct {
	ObjectName    string `json:"objectName"`
	FieldAPIName  string `json:"fieldApiName"`
	Format        string `json:"format"`
	Scope         string `json:"scope"`
	Start         int64  `json:"start"`
	TokenRefsJSON string `json:"tokenRefsJson,omitempty"`
}

func sourceBackedAutoNumberFormatProjection(payload []byte, source string) ([]AutoNumberFormatSeed, []sourceBackedAutoNumberFormatRow, error) {
	var formats []AutoNumberFormatSeed
	if err := json.Unmarshal(payload, &formats); err != nil {
		return nil, nil, fmt.Errorf("parse %s: %w", source, err)
	}
	out := make([]sourceBackedAutoNumberFormatRow, 0, len(formats))
	for _, seed := range formats {
		tokenRefs, err := canonicalAutoNumberTokenRefsForSeed(seed)
		if err != nil {
			return nil, nil, err
		}
		out = append(out, sourceBackedAutoNumberFormatRow{
			ObjectName:    seed.ObjectName,
			FieldAPIName:  seed.FieldApiName,
			Format:        seed.Format,
			Scope:         seed.Scope,
			Start:         seed.Start,
			TokenRefsJSON: tokenRefs,
		})
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].ObjectName != out[j].ObjectName {
			return out[i].ObjectName < out[j].ObjectName
		}
		return out[i].FieldAPIName < out[j].FieldAPIName
	})
	return formats, out, nil
}

func liveSourceBackedAutoNumberFormatProjection(ctx context.Context, deps VerifyDeps, packName string, expected []sourceBackedAutoNumberFormatRow) (any, error) {
	live := make([]sourceBackedAutoNumberFormatRow, 0, len(expected))
	for _, want := range expected {
		var got sourceBackedAutoNumberFormatRow
		var format, scope, tokenRefs sql.NullString
		var start sql.NullInt64
		err := deps.DB.QueryRowContext(ctx, `
SELECT o.ApiName, f.ApiName, f.auto_number_format, f.auto_number_scope, f.auto_number_start, f.auto_number_token_refs
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE o.ApiName = ? AND f.ApiName = ? AND COALESCE(f.OwnerPack, '') = ?
LIMIT 1`, want.ObjectName, want.FieldAPIName, packName).Scan(
			&got.ObjectName,
			&got.FieldAPIName,
			&format,
			&scope,
			&start,
			&tokenRefs,
		)
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("auto-number field %s.%s is missing", want.ObjectName, want.FieldAPIName)
		}
		if err != nil {
			return nil, fmt.Errorf("query auto-number field %s.%s: %w", want.ObjectName, want.FieldAPIName, err)
		}
		if format.Valid {
			got.Format = format.String
		}
		if scope.Valid {
			got.Scope = scope.String
		}
		if start.Valid {
			got.Start = start.Int64
		}
		if tokenRefs.Valid {
			canonicalTokenRefs, err := canonicalAutoNumberTokenRefsString(
				tokenRefs.String,
				got.ObjectName,
				got.FieldAPIName,
			)
			if err != nil {
				return nil, err
			}
			got.TokenRefsJSON = canonicalTokenRefs
		}
		live = append(live, got)
	}
	return live, nil
}

func newSourceBackedAutoNumberCounterComponent(packName string, component ManifestComponent, payload []byte) (PackComponent, error) {
	if strings.TrimSpace(packName) == "" {
		return nil, fmt.Errorf("source-backed pack: empty manifest name is forbidden for auto-number component")
	}
	formats, _, err := sourceBackedAutoNumberFormatProjection(payload, componentPayloadLabel(component))
	if err != nil {
		return nil, err
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: component,
		payload:           payload,
		apply: func(ctx context.Context, deps ApplyDeps) error {
			registerAutoNumberDeclaredSeedsFromFormats(formats)
			seeds := sourceBackedBootstrapSeedsFromAutoNumberFormats(formats)
			scopeMap := autonumberseed.BuildScopeSourceMapFromSeeds(seeds)
			if err := autoNumberSeeder.SeedAutoNumberCounters(ctx, deps.DB, seeds, scopeMap); err != nil {
				return fmt.Errorf("seed autonumber counters: %w", err)
			}
			if offlinecandidateauthority.StoppedSourceSetApplyOnly(ctx, packName) {
				return nil
			}
			return autoNumberSeeder.VerifyAutoNumberInvariants(ctx, deps.DB)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			registerAutoNumberDeclaredSeedsFromFormats(formats)
			if deps.DeepChecks {
				return autoNumberSeeder.VerifyAutoNumberInvariants(ctx, deps.DB)
			}
			return autoNumberSeeder.VerifyAutoNumberMetadataInvariants(ctx, deps.DB)
		},
	}, nil
}

func canonicalAutoNumberTokenRefsForSeed(seed AutoNumberFormatSeed) (string, error) {
	return canonicalAutoNumberTokenRefsString(seed.TokenRefsJSON, seed.ObjectName, seed.FieldApiName)
}

func canonicalAutoNumberTokenRefsString(raw, objectName, fieldApiName string) (string, error) {
	if strings.TrimSpace(raw) == "" {
		return "", nil
	}
	var generic any
	if err := json.Unmarshal([]byte(raw), &generic); err != nil {
		return "", fmt.Errorf("parse TokenRefsJSON for %s.%s: %w", objectName, fieldApiName, err)
	}
	canonical, err := autonumber.CanonicalizeTokenRefs(generic)
	if err != nil {
		return "", fmt.Errorf("canonicalize TokenRefsJSON for %s.%s: %w", objectName, fieldApiName, err)
	}
	return string(canonical), nil
}

func registerAutoNumberDeclaredSeedsFromFormats(formats []AutoNumberFormatSeed) {
	keys := make([]autonumberseed.AutoNumberDeclaredSeedKey, 0, len(formats))
	for _, seed := range formats {
		keys = append(keys, autonumberseed.AutoNumberDeclaredSeedKey{
			ObjectName:   seed.ObjectName,
			FieldApiName: seed.FieldApiName,
		})
	}
	autoNumberSeeder.RegisterAutoNumberDeclaredSeeds(keys)
}

func sourceBackedBootstrapSeedsFromAutoNumberFormats(formats []AutoNumberFormatSeed) []autonumberseed.AutoNumberFormatSeed {
	out := make([]autonumberseed.AutoNumberFormatSeed, 0, len(formats))
	for _, seed := range formats {
		out = append(out, autonumberseed.AutoNumberFormatSeed{
			ObjectName:   seed.ObjectName,
			FieldApiName: seed.FieldApiName,
			Format:       seed.Format,
			Scope:        seed.Scope,
			Start:        seed.Start,
			LegacySource: seed.LegacySource,
		})
	}
	return out
}