package automation

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

	"github.com/nexuscrm/persistence"
)

// approvalSubmitLineageStore persists the per-causal-chain approval submit
// dedup fact: "chain C submitted process P for record R". One fact, one owner
// (the submit transaction), one read path (the SubmitForApproval probe). The
// row is inserted INSIDE the submit's pessimistic transaction, so it is
// atomic with the approval state — a rolled-back submit leaves no fact, a
// committed one is visible to every later dispatch in the same chain
// regardless of outbox topology (sibling echo rows included, which no
// per-row context/envelope snapshot can guarantee).
type approvalSubmitLineageStore interface {
	// RecordSubmitted inserts the (chain, record, process) fact, returning
	// ErrApprovalSubmitLineageDuplicate when this chain already recorded it —
	// the unique key doubles as the last-line concurrency guard if two
	// dispatches of one chain ever race.
	RecordSubmitted(ctx context.Context, chainID, targetObjectID, processDefinitionID string) error
	SubmittedProcessIDs(ctx context.Context, chainID, targetObjectID string) ([]string, error)
	// PruneOlderThan opportunistically deletes aged rows (bounded batch) so
	// the table is self-maintaining without a dedicated worker.
	PruneOlderThan(ctx context.Context, cutoff time.Time, limit int) error
}

// ErrApprovalSubmitLineageDuplicate reports that the current causal chain has
// already recorded a submit for this (record, process).
var ErrApprovalSubmitLineageDuplicate = errors.New("approval submit already recorded for this chain, record, and process")

// SQLApprovalSubmitLineageStore stores submit lineage facts in
// _System_ApprovalSubmitLineage. Like SQLApprovalActionReplayStore it uses the
// caller's transaction when one is present on the context — that is what
// makes the fact atomic with the submit runtime rows.
type SQLApprovalSubmitLineageStore struct {
	db          *sql.DB
	txExtractor replayTxExtractor
}

func NewSQLApprovalSubmitLineageStore(db *sql.DB, txExtractor replayTxExtractor) *SQLApprovalSubmitLineageStore {
	return &SQLApprovalSubmitLineageStore{db: db, txExtractor: txExtractor}
}

func (s *SQLApprovalSubmitLineageStore) executor(ctx context.Context) replayExecutor {
	if s != nil && s.txExtractor != nil {
		if tx, ok := s.txExtractor.ExtractTx(ctx).(*sql.Tx); ok && tx != nil {
			return tx
		}
	}
	if s == nil {
		return nil
	}
	return s.db
}

func (s *SQLApprovalSubmitLineageStore) RecordSubmitted(ctx context.Context, chainID, targetObjectID, processDefinitionID string) error {
	if s == nil || s.db == nil {
		return errors.New("approval submit lineage store is not configured")
	}
	now := time.Now().UTC()
	_, err := s.executor(ctx).ExecContext(ctx,
		`INSERT INTO _System_ApprovalSubmitLineage (ChainId, TargetObjectId, ProcessDefinitionId, CreatedDate, LastModifiedDate)
		 VALUES (?, ?, ?, ?, ?)`,
		chainID, targetObjectID, processDefinitionID, now, now)
	if err != nil {
		if persistence.IsDuplicateKeyError(err) {
			return ErrApprovalSubmitLineageDuplicate
		}
		return fmt.Errorf("record approval submit lineage: %w", err)
	}
	return nil
}

func (s *SQLApprovalSubmitLineageStore) SubmittedProcessIDs(ctx context.Context, chainID, targetObjectID string) ([]string, error) {
	if s == nil || s.db == nil {
		return nil, errors.New("approval submit lineage store is not configured")
	}
	rows, err := s.executor(ctx).QueryContext(ctx,
		`SELECT ProcessDefinitionId FROM _System_ApprovalSubmitLineage WHERE ChainId = ? AND TargetObjectId = ?`,
		chainID, targetObjectID)
	if err != nil {
		return nil, fmt.Errorf("query approval submit lineage: %w", err)
	}
	defer func() { _ = rows.Close() }()
	var out []string
	for rows.Next() {
		var processDefinitionID string
		if err := rows.Scan(&processDefinitionID); err != nil {
			return nil, fmt.Errorf("scan approval submit lineage row: %w", err)
		}
		out = append(out, processDefinitionID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate approval submit lineage rows: %w", err)
	}
	return out, nil
}

// MemoryApprovalSubmitLineageStore is an in-memory approvalSubmitLineageStore
// for unit tests that construct ApprovalService without a database. It is NOT
// transactional: a rolled-back submit keeps its fact, which no production
// path relies on — integration tests cover the transactional semantics.
type MemoryApprovalSubmitLineageStore struct {
	mu    sync.Mutex
	facts map[string][]string // chainID+"\x00"+targetObjectID → processDefinitionIDs
}

func NewMemoryApprovalSubmitLineageStore() *MemoryApprovalSubmitLineageStore {
	return &MemoryApprovalSubmitLineageStore{facts: map[string][]string{}}
}

func (s *MemoryApprovalSubmitLineageStore) key(chainID, targetObjectID string) string {
	return chainID + "\x00" + targetObjectID
}

func (s *MemoryApprovalSubmitLineageStore) RecordSubmitted(_ context.Context, chainID, targetObjectID, processDefinitionID string) error {
	s.mu.Lock()
	defer s.mu.Unlock()
	k := s.key(chainID, targetObjectID)
	for _, existing := range s.facts[k] {
		if existing == processDefinitionID {
			return ErrApprovalSubmitLineageDuplicate
		}
	}
	s.facts[k] = append(s.facts[k], processDefinitionID)
	return nil
}

func (s *MemoryApprovalSubmitLineageStore) SubmittedProcessIDs(_ context.Context, chainID, targetObjectID string) ([]string, error) {
	s.mu.Lock()
	defer s.mu.Unlock()
	return append([]string(nil), s.facts[s.key(chainID, targetObjectID)]...), nil
}

func (s *MemoryApprovalSubmitLineageStore) PruneOlderThan(context.Context, time.Time, int) error {
	return nil
}

func (s *SQLApprovalSubmitLineageStore) PruneOlderThan(ctx context.Context, cutoff time.Time, limit int) error {
	if s == nil || s.db == nil {
		return errors.New("approval submit lineage store is not configured")
	}
	if limit <= 0 {
		limit = 200
	}
	// Runs on the base DB, never the caller's tx: pruning must not couple an
	// unrelated submit's fate to housekeeping.
	_, err := s.db.ExecContext(ctx,
		fmt.Sprintf(`DELETE FROM _System_ApprovalSubmitLineage WHERE CreatedDate < ? LIMIT %d`, limit),
		cutoff)
	if err != nil {
		return fmt.Errorf("prune approval submit lineage: %w", err)
	}
	return nil
}