package persistence

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

	"github.com/nexuscrm/kernel/pkg/ports"
)

const (
	PermissionAuthoringCommandAccepted  = ports.PermissionAuthoringCommandAccepted
	PermissionAuthoringCommandCompleted = ports.PermissionAuthoringCommandCompleted
)

var (
	ErrPermissionAuthoringCommandConflict  = ports.ErrPermissionAuthoringCommandConflict
	ErrPermissionAuthoringCommandMissing   = ports.ErrPermissionAuthoringCommandMissing
	ErrPermissionAuthoringBindingConflict  = ports.ErrPermissionAuthoringBindingConflict
	ErrPermissionAuthoringAdmissionExpired = ports.ErrPermissionAuthoringAdmissionExpired
	permissionAuthoringRequestDigest       = regexp.MustCompile(`^sha256:[0-9a-f]{64}$`)
)

// PermissionAuthoringCommandRepository is the Permissions-owned durable
// command authority. Persistence owns transactionality and exact replay
// binding, but treats terminal result payloads as opaque semantic data.
// There are deliberately no per-family operation slots: partial family
// completion is not representable.
type PermissionAuthoringCommandRepository struct {
	db *sql.DB
}

var _ ports.PermissionAuthoringCommandStore = (*PermissionAuthoringCommandRepository)(nil)

func NewPermissionAuthoringCommandRepository(db *sql.DB) *PermissionAuthoringCommandRepository {
	return &PermissionAuthoringCommandRepository{db: db}
}

type PermissionAuthoringCommandAcceptance = ports.PermissionAuthoringCommandAcceptance
type PermissionAuthoringCommandRow = ports.PermissionAuthoringCommand

// Accept binds a principal-scoped CommandID to one immutable semantic effect.
// Acceptance and canonical binding have no business side effect by themselves,
// so an expired ACCEPTED row may be recycled. A completed mutation and its
// terminal result always share one later transaction.
func (r *PermissionAuthoringCommandRepository) Accept(
	ctx context.Context,
	input PermissionAuthoringCommandAcceptance,
) (PermissionAuthoringCommandRow, bool, error) {
	if r == nil || r.db == nil {
		return PermissionAuthoringCommandRow{}, false, errors.New("permission authoring command repository is unavailable")
	}
	if !hexDigestPattern.MatchString(input.ScopeDigest) ||
		!permissionAuthoringRequestDigest.MatchString(input.RequestDigest) ||
		input.RowID == "" || input.CommandID == "" || input.TargetKind == "" ||
		input.TargetID == "" || input.ExpectedAuthoringToken == "" ||
		input.SemanticMappingRevision == 0 || input.AdmissionTTL.Microseconds() <= 0 ||
		input.OutcomeRetention.Microseconds() <= 0 {
		return PermissionAuthoringCommandRow{}, false, errors.New("permission authoring command acceptance is incomplete")
	}
	exec, err := r.transactionExecutor(ctx)
	if err != nil {
		return PermissionAuthoringCommandRow{}, false, err
	}
	if _, err := exec.ExecContext(ctx, `DELETE FROM _System_PermissionAuthoringCommand
WHERE ScopeDigest = ? AND CommandId = ?
  AND ((State = 'ACCEPTED' AND AdmissionExpiresAt <= UTC_TIMESTAMP(6))
    OR (State = 'COMPLETED' AND OutcomeExpiresAt <= UTC_TIMESTAMP(6)))`,
		input.ScopeDigest, input.CommandID); err != nil {
		return PermissionAuthoringCommandRow{}, false, fmt.Errorf("recycle expired permission authoring command: %w", err)
	}
	result, err := exec.ExecContext(ctx, `INSERT IGNORE INTO _System_PermissionAuthoringCommand
(Id, ScopeDigest, CommandId, RequestDigest, TargetKind, TargetId, ExpectedAuthoringToken,
 SemanticMappingRevision, State, AdmissionExpiresAt, OutcomeRetentionMicros)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'ACCEPTED', TIMESTAMPADD(MICROSECOND, ?, UTC_TIMESTAMP(6)), ?)`,
		input.RowID, input.ScopeDigest, input.CommandID, input.RequestDigest,
		input.TargetKind, input.TargetID, input.ExpectedAuthoringToken, input.SemanticMappingRevision,
		input.AdmissionTTL.Microseconds(), input.OutcomeRetention.Microseconds())
	if err != nil {
		return PermissionAuthoringCommandRow{}, false, fmt.Errorf("accept permission authoring command: %w", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return PermissionAuthoringCommandRow{}, false, fmt.Errorf("accept permission authoring command rows affected: %w", err)
	}
	row, err := r.load(ctx, exec, input.ScopeDigest, input.CommandID, true, "")
	if err != nil {
		return PermissionAuthoringCommandRow{}, false, err
	}
	if row.RequestDigest != input.RequestDigest || row.TargetKind != input.TargetKind ||
		row.TargetID != input.TargetID || row.ExpectedAuthoringToken != input.ExpectedAuthoringToken {
		return row, true, ErrPermissionAuthoringCommandConflict
	}
	return row, affected == 0, nil
}

// LoadActive preserves nonterminal commands only through their database-owned
// recovery window. BindCanonicalPermissionSet replaces the short unbound
// admission deadline with the longer command retention window. Completed
// outcomes remain visible through that same retention duration.
func (r *PermissionAuthoringCommandRepository) LoadActive(
	ctx context.Context,
	scopeDigest string,
	commandID string,
) (PermissionAuthoringCommandRow, error) {
	if r == nil || r.db == nil {
		return PermissionAuthoringCommandRow{}, ErrPermissionAuthoringCommandMissing
	}
	return r.load(ctx, r.db, scopeDigest, commandID, false, ` AND (
  (State = 'ACCEPTED' AND AdmissionExpiresAt > UTC_TIMESTAMP(6))
  OR (State = 'COMPLETED' AND OutcomeExpiresAt > UTC_TIMESTAMP(6))
)`)
}

func (r *PermissionAuthoringCommandRepository) LockCommand(
	ctx context.Context,
	rowID string,
) (PermissionAuthoringCommandRow, error) {
	exec, err := r.transactionExecutor(ctx)
	if err != nil {
		return PermissionAuthoringCommandRow{}, err
	}
	row, err := scanPermissionAuthoringCommand(exec.QueryRowContext(ctx, `SELECT Id, ScopeDigest, CommandId,
       RequestDigest, TargetKind, TargetId, COALESCE(CanonicalPermissionSetId, ''), ExpectedAuthoringToken,
       COALESCE(SemanticMappingRevision, 0), State, COALESCE(ResultPayload, ''), AdmissionExpiresAt, OutcomeExpiresAt, OutcomeRetentionMicros
FROM _System_PermissionAuthoringCommand WHERE Id = ? FOR UPDATE`, rowID))
	if err != nil {
		return PermissionAuthoringCommandRow{}, err
	}
	if row.State == PermissionAuthoringCommandAccepted {
		// TiDB evaluates UTC_TIMESTAMP() at statement start, before a locking
		// read may wait. Check the database clock in a second statement only
		// after this transaction owns the row lock.
		var admissionActive bool
		if err := exec.QueryRowContext(ctx, `SELECT AdmissionExpiresAt > UTC_TIMESTAMP(6)
FROM _System_PermissionAuthoringCommand WHERE Id = ?`, rowID).Scan(&admissionActive); err != nil {
			if errors.Is(err, sql.ErrNoRows) {
				return PermissionAuthoringCommandRow{}, ErrPermissionAuthoringCommandMissing
			}
			return PermissionAuthoringCommandRow{}, fmt.Errorf("check locked permission authoring recovery deadline: %w", err)
		}
		if !admissionActive {
			return row, ErrPermissionAuthoringAdmissionExpired
		}
	}
	return row, nil
}

// BindCanonicalPermissionSet permanently records the first successful target
// resolution and replaces the short admission deadline with its bounded
// recovery window. The binding commits before business execution, so Profile
// rebinding cannot redirect a retried command.
func (r *PermissionAuthoringCommandRepository) BindCanonicalPermissionSet(
	ctx context.Context,
	rowID string,
	permissionSetID string,
) error {
	if rowID == "" || permissionSetID == "" {
		return ErrPermissionAuthoringBindingConflict
	}
	exec, err := r.transactionExecutor(ctx)
	if err != nil {
		return err
	}
	result, err := exec.ExecContext(ctx, `UPDATE _System_PermissionAuthoringCommand
SET CanonicalPermissionSetId = ?,
    AdmissionExpiresAt = TIMESTAMPADD(MICROSECOND, OutcomeRetentionMicros, UTC_TIMESTAMP(6)),
    LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE Id = ? AND State = 'ACCEPTED' AND CanonicalPermissionSetId IS NULL
  AND AdmissionExpiresAt > UTC_TIMESTAMP(6)`, permissionSetID, rowID)
	if err != nil {
		return fmt.Errorf("bind permission authoring command target: %w", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("bind permission authoring command target rows affected: %w", err)
	}
	if affected == 1 {
		return nil
	}
	row, err := r.LockCommand(ctx, rowID)
	if err != nil {
		return err
	}
	if row.State == PermissionAuthoringCommandAccepted && row.CanonicalPermissionSetID == permissionSetID {
		return nil
	}
	if row.State == PermissionAuthoringCommandAccepted && row.CanonicalPermissionSetID == "" {
		return ErrPermissionAuthoringAdmissionExpired
	}
	return ErrPermissionAuthoringBindingConflict
}

// TerminalOutcomeExpiry derives the replay deadline from the database clock
// while the command row is locked in its completing transaction. The returned
// value is used both in the opaque response payload and the durable row, so
// process/DB clock skew cannot split replay semantics.
func (r *PermissionAuthoringCommandRepository) TerminalOutcomeExpiry(
	ctx context.Context,
	rowID string,
) (time.Time, error) {
	if rowID == "" {
		return time.Time{}, errors.New("permission authoring terminal expiry requires a command row")
	}
	exec, err := r.transactionExecutor(ctx)
	if err != nil {
		return time.Time{}, err
	}
	var rawExpiry any
	err = exec.QueryRowContext(ctx, `SELECT TIMESTAMPADD(MICROSECOND, OutcomeRetentionMicros, UTC_TIMESTAMP(6))
FROM _System_PermissionAuthoringCommand
WHERE Id = ? AND State = 'ACCEPTED' FOR UPDATE`, rowID).Scan(&rawExpiry)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return time.Time{}, ErrPermissionAuthoringCommandMissing
		}
		return time.Time{}, fmt.Errorf("derive permission authoring terminal expiry: %w", err)
	}
	expiry, err := permissionAuthoringDatabaseUTC(rawExpiry)
	if err != nil {
		return time.Time{}, err
	}
	return expiry, nil
}

func permissionAuthoringDatabaseUTC(value any) (time.Time, error) {
	switch typed := value.(type) {
	case time.Time:
		return time.Date(
			typed.Year(), typed.Month(), typed.Day(), typed.Hour(), typed.Minute(), typed.Second(), typed.Nanosecond(), time.UTC,
		), nil
	case []byte:
		return permissionAuthoringParseDatabaseUTC(string(typed))
	case string:
		return permissionAuthoringParseDatabaseUTC(typed)
	default:
		return time.Time{}, fmt.Errorf("derive permission authoring terminal expiry: unsupported database time %T", value)
	}
}

func permissionAuthoringParseDatabaseUTC(value string) (time.Time, error) {
	for _, layout := range []string{"2006-01-02 15:04:05.999999", "2006-01-02 15:04:05"} {
		if parsed, err := time.ParseInLocation(layout, value, time.UTC); err == nil {
			return parsed, nil
		}
	}
	return time.Time{}, fmt.Errorf("derive permission authoring terminal expiry: invalid database time %q", value)
}

func (r *PermissionAuthoringCommandRepository) CompleteBound(
	ctx context.Context,
	rowID string,
	resultPayload []byte,
	outcomeExpiresAt time.Time,
) error {
	if rowID == "" || len(resultPayload) == 0 || outcomeExpiresAt.IsZero() {
		return errors.New("permission authoring terminal result is incomplete")
	}
	exec, err := r.transactionExecutor(ctx)
	if err != nil {
		return err
	}
	result, err := exec.ExecContext(ctx, `UPDATE _System_PermissionAuthoringCommand
SET State = 'COMPLETED', ResultPayload = ?, OutcomeExpiresAt = ?, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE Id = ? AND State = 'ACCEPTED' AND CanonicalPermissionSetId IS NOT NULL`,
		resultPayload, outcomeExpiresAt.UTC(), rowID)
	if err != nil {
		return fmt.Errorf("complete permission authoring command: %w", err)
	}
	return requireOneAffected(result, "complete permission authoring command")
}

// CompleteUnboundRejection freezes a MISSING or DENIED disposition discovered
// before any canonical target could be bound. It cannot complete a command
// that has acquired a canonical PermissionSet: bound outcomes use
// CompleteBound so mutation and terminalization share that aggregate lock.
func (r *PermissionAuthoringCommandRepository) CompleteUnboundRejection(
	ctx context.Context,
	rowID string,
	resultPayload []byte,
	outcomeExpiresAt time.Time,
) error {
	if rowID == "" || len(resultPayload) == 0 || outcomeExpiresAt.IsZero() {
		return errors.New("permission authoring terminal rejection is incomplete")
	}
	exec, err := r.transactionExecutor(ctx)
	if err != nil {
		return err
	}
	result, err := exec.ExecContext(ctx, `UPDATE _System_PermissionAuthoringCommand
SET State = 'COMPLETED', ResultPayload = ?, OutcomeExpiresAt = ?, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE Id = ? AND State = 'ACCEPTED' AND CanonicalPermissionSetId IS NULL`,
		resultPayload, outcomeExpiresAt.UTC(), rowID)
	if err != nil {
		return fmt.Errorf("complete unbound permission authoring rejection: %w", err)
	}
	return requireOneAffected(result, "complete unbound permission authoring rejection")
}

func (r *PermissionAuthoringCommandRepository) load(
	ctx context.Context,
	exec Executor,
	scopeDigest string,
	commandID string,
	forUpdate bool,
	predicate string,
) (PermissionAuthoringCommandRow, error) {
	if !hexDigestPattern.MatchString(scopeDigest) || commandID == "" {
		return PermissionAuthoringCommandRow{}, ErrPermissionAuthoringCommandMissing
	}
	query := `SELECT Id, ScopeDigest, CommandId, RequestDigest, TargetKind, TargetId,
       COALESCE(CanonicalPermissionSetId, ''), ExpectedAuthoringToken,
       COALESCE(SemanticMappingRevision, 0), State, COALESCE(ResultPayload, ''), AdmissionExpiresAt, OutcomeExpiresAt, OutcomeRetentionMicros
FROM _System_PermissionAuthoringCommand WHERE ScopeDigest = ? AND CommandId = ?` + predicate
	if forUpdate {
		query += " FOR UPDATE"
	}
	return scanPermissionAuthoringCommand(exec.QueryRowContext(ctx, query, scopeDigest, commandID))
}

func (r *PermissionAuthoringCommandRepository) transactionExecutor(ctx context.Context) (Executor, error) {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx, nil
	}
	return nil, errors.New("permission authoring command operation requires an active transaction")
}

type permissionAuthoringCommandScanner interface {
	Scan(...any) error
}

func scanPermissionAuthoringCommand(scanner permissionAuthoringCommandScanner) (PermissionAuthoringCommandRow, error) {
	var row PermissionAuthoringCommandRow
	var outcomeExpiresAt sql.NullTime
	if err := scanner.Scan(
		&row.RowID, &row.ScopeDigest, &row.CommandID, &row.RequestDigest,
		&row.TargetKind, &row.TargetID, &row.CanonicalPermissionSetID,
		&row.ExpectedAuthoringToken, &row.SemanticMappingRevision, &row.State, &row.ResultPayload,
		&row.AdmissionExpiresAt, &outcomeExpiresAt, &row.OutcomeRetentionMicros,
	); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return PermissionAuthoringCommandRow{}, ErrPermissionAuthoringCommandMissing
		}
		return PermissionAuthoringCommandRow{}, fmt.Errorf("scan permission authoring command: %w", err)
	}
	if outcomeExpiresAt.Valid {
		row.OutcomeExpiresAt = outcomeExpiresAt.Time
	}
	return row, nil
}