package persistence

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/shared/pkg/constants"
)

type historyTrackingState struct {
	fieldID       string
	trackHistory  bool
	revision      int64
	customized    bool
	deleted       bool
	intervals     int
	open          int
	validOpen     int
	staleOpenKey  int
	invalidBounds int
	overlaps      int
	latestStart   sql.NullInt64
	latestEnd     sql.NullInt64
}

// ApplyHistoryTrackingTransition applies one existing-field TrackHistory
// transition through the optimistic CAS + interval-ledger protocol.
func (r *SchemaRepository) ApplyHistoryTrackingTransition(
	ctx context.Context,
	fieldID string,
	desired bool,
	tx interface{},
	preserveCustomized bool,
	markCustomized bool,
) (bool, error) {
	exec, ok := tx.(Executor)
	if !ok || exec == nil {
		return false, apperrors.NewInternalError("history tracking transition requires an active transaction executor", nil)
	}
	if _, rootDB := tx.(*sql.DB); rootDB {
		return false, apperrors.NewInternalError("history tracking transition rejects the root database executor", nil)
	}
	ctx = ports.EnsureHistorySerializationAttemptState(ctx)
	state, err := loadHistoryTrackingState(ctx, exec, fieldID)
	if err != nil {
		return false, err
	}
	if state.deleted {
		return false, apperrors.NewValidationError(constants.FieldSysField_TrackHistory, "cannot change history tracking for a deleted field")
	}
	if err := validateHistoryTrackingState(state); err != nil {
		return false, err
	}
	if preserveCustomized && state.customized {
		return false, nil
	}
	// A pre-upgrade true/no-interval row is not initialized by bootstrap or an
	// ordinary metadata write. Only an explicit transition owner (including
	// pack reapply/reconcile) reaches this legacy initialization branch and
	// establishes new forward-only sequence authority.
	legacyInitialization := state.trackHistory && state.intervals == 0 && desired
	legacyDisable := state.trackHistory && state.intervals == 0 && !desired
	if state.trackHistory == desired && !legacyInitialization {
		return false, nil
	}

	reserved, err := ReserveHistorySequences(ctx, tx, []string{fieldID})
	if err != nil {
		return false, err
	}
	boundary := reserved[fieldID]
	if !desired && state.latestStart.Valid && boundary <= state.latestStart.Int64 {
		return false, historyTrackingConflict("disable boundary does not follow the open interval start", nil)
	}
	if desired && state.latestEnd.Valid && boundary <= state.latestEnd.Int64 {
		return false, historyTrackingConflict("enable boundary does not follow the latest closed interval", nil)
	}
	eventAt, err := currentHistoryTrackingBoundary(ctx, exec)
	if err != nil {
		return false, err
	}

	customizedValue := state.customized
	if markCustomized {
		customizedValue = true
	}
	result, err := exec.ExecContext(ctx, `
		UPDATE _System_Field
		SET TrackHistory = ?,
		    HistoryTrackingRevision = HistoryTrackingRevision + 1,
		    IsCustomized = ?,
		    LastModifiedDate = ?
		WHERE Id = ?
		  AND IsDeleted = 0
		  AND TrackHistory = ?
		  AND HistoryTrackingRevision = ?
		  AND COALESCE(IsCustomized, 0) = ?`,
		desired, customizedValue, eventAt, fieldID,
		state.trackHistory, state.revision, state.customized,
	)
	if err != nil {
		if isOptimisticWriteConflict(err) {
			return false, historyTrackingConflict("history tracking CAS failed", err)
		}
		return false, apperrors.NewInternalError("execute history tracking CAS", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, apperrors.NewInternalError("read history tracking CAS result", err)
	}
	if affected != 1 {
		return false, historyTrackingConflict("history tracking state changed concurrently", nil)
	}
	ports.MarkHistoryTrackingAttempt(ctx)

	if desired {
		_, err = exec.ExecContext(ctx, `
			INSERT INTO _System_FieldHistoryTrackingInterval
				(Id, FieldId, OpenFieldId, StartedSequence, EndedSequence, StartedAt, EndedAt)
			VALUES (?, ?, ?, ?, NULL, ?, NULL)`,
			uuid.NewString(), fieldID, fieldID, boundary, eventAt,
		)
		if err != nil {
			if IsDuplicateKeyError(err) || isOptimisticWriteConflict(err) {
				return false, historyTrackingConflict("open history tracking interval", err)
			}
			return false, apperrors.NewInternalError("open history tracking interval", err)
		}
		return true, nil
	}
	// Legacy tracked rows have no interval authority to close. Disabling one is
	// a forward-only, fail-closed transition: the flag/revision and reserved
	// boundary commit atomically, without inventing a historical interval.
	if legacyDisable {
		return true, nil
	}

	result, err = exec.ExecContext(ctx, `
		UPDATE _System_FieldHistoryTrackingInterval
		SET OpenFieldId = NULL, EndedSequence = ?, EndedAt = ?
		WHERE OpenFieldId = ? AND EndedSequence IS NULL`, boundary, eventAt, fieldID)
	if err != nil {
		if isOptimisticWriteConflict(err) {
			return false, historyTrackingConflict("close history tracking interval", err)
		}
		return false, apperrors.NewInternalError("close history tracking interval", err)
	}
	affected, err = result.RowsAffected()
	if err != nil {
		return false, apperrors.NewInternalError("read history interval close result", err)
	}
	if affected != 1 {
		return false, historyTrackingConflict("open history interval changed concurrently", nil)
	}
	return true, nil
}

func loadHistoryTrackingState(ctx context.Context, exec Executor, fieldID string) (historyTrackingState, error) {
	state := historyTrackingState{fieldID: fieldID}
	var deleted, customized bool
	err := exec.QueryRowContext(ctx, `
		SELECT TrackHistory, HistoryTrackingRevision, IsCustomized, IsDeleted
		FROM _System_Field
		WHERE Id = ?`, fieldID).Scan(&state.trackHistory, &state.revision, &customized, &deleted)
	if errors.Is(err, sql.ErrNoRows) {
		return state, apperrors.NewNotFoundError("Field", fieldID)
	}
	if err != nil {
		return state, apperrors.NewInternalError("read history tracking state", err)
	}
	state.customized = customized
	state.deleted = deleted
	err = exec.QueryRowContext(ctx, `
		SELECT COUNT(*),
		       COALESCE(SUM(CASE WHEN EndedSequence IS NULL THEN 1 ELSE 0 END), 0),
		       COALESCE(SUM(CASE WHEN EndedSequence IS NULL AND OpenFieldId = FieldId THEN 1 ELSE 0 END), 0),
		       COALESCE(SUM(CASE WHEN EndedSequence IS NOT NULL AND OpenFieldId IS NOT NULL THEN 1 ELSE 0 END), 0),
		       COALESCE(SUM(CASE WHEN StartedSequence IS NULL OR (EndedSequence IS NOT NULL AND EndedSequence <= StartedSequence) THEN 1 ELSE 0 END), 0),
		       (SELECT COUNT(*)
		          FROM _System_FieldHistoryTrackingInterval a
		          JOIN _System_FieldHistoryTrackingInterval b
		            ON a.FieldId = b.FieldId AND a.Id < b.Id
		           AND a.StartedSequence < COALESCE(b.EndedSequence, 9223372036854775807)
		           AND b.StartedSequence < COALESCE(a.EndedSequence, 9223372036854775807)
		         WHERE a.FieldId = ?),
		       MAX(StartedSequence), MAX(EndedSequence)
		FROM _System_FieldHistoryTrackingInterval
		WHERE FieldId = ?`, fieldID, fieldID).Scan(
		&state.intervals, &state.open, &state.validOpen, &state.staleOpenKey,
		&state.invalidBounds, &state.overlaps, &state.latestStart, &state.latestEnd,
	)
	if err != nil {
		return state, apperrors.NewInternalError("read history tracking intervals", err)
	}
	return state, nil
}

func loadHistoryTrackingStates(ctx context.Context, exec Executor, fieldIDs []string) (map[string]historyTrackingState, error) {
	states, err := loadHistoryTrackingFieldStates(ctx, exec, fieldIDs)
	if err != nil {
		return nil, err
	}
	existingFieldIDs := make([]string, 0, len(states))
	seen := make(map[string]struct{}, len(states))
	for _, fieldID := range fieldIDs {
		if _, ok := states[fieldID]; !ok {
			continue
		}
		if _, ok := seen[fieldID]; ok {
			continue
		}
		seen[fieldID] = struct{}{}
		existingFieldIDs = append(existingFieldIDs, fieldID)
	}
	if err := loadHistoryTrackingIntervalSummaries(ctx, exec, existingFieldIDs, states); err != nil {
		return nil, err
	}
	return states, nil
}

func loadHistoryTrackingFieldStates(ctx context.Context, exec Executor, fieldIDs []string) (map[string]historyTrackingState, error) {
	states := make(map[string]historyTrackingState, len(fieldIDs))
	if len(fieldIDs) == 0 {
		return states, nil
	}
	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(fieldIDs)), ",")
	args := make([]interface{}, len(fieldIDs))
	for i, fieldID := range fieldIDs {
		args[i] = fieldID
	}
	rows, err := exec.QueryContext(ctx, fmt.Sprintf(`
		SELECT Id, TrackHistory, HistoryTrackingRevision, IsCustomized, IsDeleted
		FROM _System_Field
		WHERE Id IN (%s)`, placeholders), args...)
	if err != nil {
		return nil, apperrors.NewInternalError("read batch history tracking states", err)
	}
	defer rows.Close()
	for rows.Next() {
		state := historyTrackingState{}
		if err := rows.Scan(&state.fieldID, &state.trackHistory, &state.revision, &state.customized, &state.deleted); err != nil {
			return nil, apperrors.NewInternalError("scan batch history tracking state", err)
		}
		states[state.fieldID] = state
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate batch history tracking states", err)
	}
	return states, nil
}

func loadHistoryTrackingIntervalSummaries(ctx context.Context, exec Executor, fieldIDs []string, states map[string]historyTrackingState) error {
	if len(fieldIDs) == 0 {
		return nil
	}
	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(fieldIDs)), ",")
	args := make([]interface{}, 0, len(fieldIDs)*2)
	for _, fieldID := range fieldIDs {
		args = append(args, fieldID)
	}
	for _, fieldID := range fieldIDs {
		args = append(args, fieldID)
	}
	rows, err := exec.QueryContext(ctx, fmt.Sprintf(`
		SELECT intervals.FieldId,
		       COUNT(*),
		       COALESCE(SUM(CASE WHEN intervals.EndedSequence IS NULL THEN 1 ELSE 0 END), 0),
		       COALESCE(SUM(CASE WHEN intervals.EndedSequence IS NULL AND intervals.OpenFieldId = intervals.FieldId THEN 1 ELSE 0 END), 0),
		       COALESCE(SUM(CASE WHEN intervals.EndedSequence IS NOT NULL AND intervals.OpenFieldId IS NOT NULL THEN 1 ELSE 0 END), 0),
		       COALESCE(SUM(CASE WHEN intervals.StartedSequence IS NULL OR (intervals.EndedSequence IS NOT NULL AND intervals.EndedSequence <= intervals.StartedSequence) THEN 1 ELSE 0 END), 0),
		       COALESCE(overlaps.OverlapCount, 0),
		       MAX(intervals.StartedSequence), MAX(intervals.EndedSequence)
		FROM _System_FieldHistoryTrackingInterval intervals
		LEFT JOIN (
			SELECT a.FieldId, COUNT(*) AS OverlapCount
			FROM _System_FieldHistoryTrackingInterval a
			JOIN _System_FieldHistoryTrackingInterval b
			  ON a.FieldId = b.FieldId AND a.Id < b.Id
			 AND a.StartedSequence < COALESCE(b.EndedSequence, 9223372036854775807)
			 AND b.StartedSequence < COALESCE(a.EndedSequence, 9223372036854775807)
			WHERE a.FieldId IN (%s)
			GROUP BY a.FieldId
		) overlaps ON overlaps.FieldId = intervals.FieldId
		WHERE intervals.FieldId IN (%s)
		GROUP BY intervals.FieldId, overlaps.OverlapCount`, placeholders, placeholders), args...)
	if err != nil {
		return apperrors.NewInternalError("read batch history tracking intervals", err)
	}
	defer rows.Close()
	for rows.Next() {
		state := historyTrackingState{}
		if err := rows.Scan(
			&state.fieldID, &state.intervals, &state.open, &state.validOpen,
			&state.staleOpenKey, &state.invalidBounds, &state.overlaps,
			&state.latestStart, &state.latestEnd,
		); err != nil {
			return apperrors.NewInternalError("scan batch history tracking intervals", err)
		}
		current, ok := states[state.fieldID]
		if !ok {
			return apperrors.NewInternalError("batch history tracking interval references an unknown field", nil)
		}
		state.trackHistory = current.trackHistory
		state.revision = current.revision
		state.customized = current.customized
		state.deleted = current.deleted
		states[state.fieldID] = state
	}
	if err := rows.Err(); err != nil {
		return apperrors.NewInternalError("iterate batch history tracking intervals", err)
	}
	return nil
}

type historyTrackingNoOpClaim struct {
	fieldID    string
	tracked    bool
	revision   int64
	customized bool
}

func claimHistoryTrackingNoOps(ctx context.Context, exec Executor, claims []historyTrackingNoOpClaim) error {
	if len(claims) == 0 {
		return nil
	}
	conditions := make([]string, 0, len(claims))
	args := make([]interface{}, 0, len(claims)*4)
	for _, claim := range claims {
		conditions = append(conditions, "(Id = ? AND TrackHistory = ? AND HistoryTrackingRevision = ? AND COALESCE(IsCustomized, 0) = ? AND IsDeleted = 0)")
		args = append(args, claim.fieldID, claim.tracked, claim.revision, claim.customized)
	}
	// Make the claim a real row write in both transaction modes. The generic
	// metadata upsert later replaces this temporary timestamp with its normal
	// LastModifiedDate value before the transaction commits.
	result, err := exec.ExecContext(ctx, fmt.Sprintf(`
		UPDATE _System_Field
		SET LastModifiedDate = TIMESTAMPADD(SECOND, 1, LastModifiedDate)
		WHERE %s`, strings.Join(conditions, " OR ")), args...)
	if err != nil {
		if isOptimisticWriteConflict(err) {
			return historyTrackingConflict("history tracking no-op claim failed", err)
		}
		return apperrors.NewInternalError("execute history tracking no-op claim", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return apperrors.NewInternalError("read history tracking no-op claim result", err)
	}
	if affected != int64(len(claims)) {
		return historyTrackingConflict("history tracking state changed before no-op claim", nil)
	}
	ports.MarkHistoryTrackingAttempt(ctx)
	return nil
}

func validateHistoryTrackingState(state historyTrackingState) error {
	if state.open != state.validOpen || state.open > 1 || state.staleOpenKey != 0 || state.invalidBounds != 0 || state.overlaps != 0 {
		return historyTrackingConflict("history tracking interval ledger is inconsistent", nil)
	}
	if state.trackHistory {
		if state.intervals > 0 && state.open != 1 {
			return historyTrackingConflict("tracked field has no open interval", nil)
		}
		return nil
	}
	if state.open != 0 {
		return historyTrackingConflict("untracked field has an open interval", nil)
	}
	return nil
}

func currentHistoryTrackingBoundary(ctx context.Context, exec Executor) (time.Time, error) {
	var boundary time.Time
	if err := exec.QueryRowContext(ctx, "SELECT UTC_TIMESTAMP(6)").Scan(&boundary); err != nil {
		return time.Time{}, apperrors.NewInternalError("read history tracking boundary", err)
	}
	return boundary.UTC(), nil
}

func historyTrackingConflict(message string, cause error) error {
	if cause == nil {
		return fmt.Errorf("%w: %s", ports.ErrHistoryTrackingConflict, message)
	}
	return fmt.Errorf("%w: %s: %v", ports.ErrHistoryTrackingConflict, message, cause)
}