package postgres

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"gl/domain/ledger"
)

type SettlementRepository struct{ database Database }

type ReconciliationStats struct {
	Open, Detected, Resolved int64
}

type txQueryer interface {
	Query(context.Context, string, ...any) (Rows, error)
}

func NewSettlementRepository(database Database) *SettlementRepository {
	return &SettlementRepository{database: database}
}

func (r *SettlementRepository) Stats(ctx context.Context) (ledger.SettlementStats, error) {
	var stats ledger.SettlementStats
	err := r.database.QueryRow(ctx, `SELECT
	COUNT(*) FILTER (WHERE status='PENDING'),
	COUNT(*) FILTER (WHERE status='RETRYABLE'),
	COUNT(*) FILTER (WHERE status='MANUAL_REVIEW'),
	COALESCE(EXTRACT(EPOCH FROM (clock_timestamp() - MIN(created_at) FILTER (WHERE status IN ('PENDING','RETRYABLE'))))::bigint, 0)
FROM kuknos_settlements`).Scan(&stats.Pending, &stats.Retryable, &stats.ManualReview, &stats.OldestPendingSeconds)
	if err != nil {
		return ledger.SettlementStats{}, fmt.Errorf("settlement stats: %w", err)
	}
	return stats, nil
}

func (r *SettlementRepository) Enqueue(ctx context.Context, s ledger.Settlement) (ledger.Settlement, bool, error) {
	if err := s.Validate(); err != nil {
		return ledger.Settlement{}, false, err
	}
	var inserted string
	err := r.database.QueryRow(ctx, `INSERT INTO kuknos_settlements
	(id, source_service, source_transaction_id, idempotency_key, status, network, signed_transaction_xdr, available_at)
	VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT (idempotency_key) DO NOTHING RETURNING id`,
		s.ID, s.SourceService, s.SourceTxID, s.IdempotencyKey, s.Status, s.Network, s.SignedTransactionXDR, s.AvailableAt).Scan(&inserted)
	if err == nil {
		return s, false, nil
	}
	if err == pgx.ErrNoRows {
		var existingXDR string
		if err := r.database.QueryRow(ctx, `SELECT signed_transaction_xdr, id FROM kuknos_settlements WHERE idempotency_key=$1`, s.IdempotencyKey).Scan(&existingXDR, &inserted); err != nil {
			return ledger.Settlement{}, false, fmt.Errorf("resolve settlement idempotency: %w", err)
		}
		if existingXDR != s.SignedTransactionXDR {
			return ledger.Settlement{}, false, ledger.ErrIdempotencyConflict
		}
		existing, err := r.Get(ctx, inserted, "")
		return existing, true, err
	}
	return ledger.Settlement{}, false, fmt.Errorf("enqueue settlement: %w", err)
}

func (r *SettlementRepository) Get(ctx context.Context, id, idempotencyKey string) (ledger.Settlement, error) {
	var s ledger.Settlement
	err := r.database.QueryRow(ctx, `SELECT id, source_service, source_transaction_id, idempotency_key, status, network, transaction_hash, attempts, available_at, last_error, signed_transaction_xdr FROM kuknos_settlements WHERE id=NULLIF($1,'')::uuid OR idempotency_key=NULLIF($2,'')`, id, idempotencyKey).Scan(&s.ID, &s.SourceService, &s.SourceTxID, &s.IdempotencyKey, &s.Status, &s.Network, &s.TransactionHash, &s.Attempts, &s.AvailableAt, &s.LastError, &s.SignedTransactionXDR)
	if err != nil {
		return ledger.Settlement{}, fmt.Errorf("get settlement: %w", err)
	}
	return s, nil
}

func (r *SettlementRepository) ClaimDue(ctx context.Context, workerID string, limit int, now time.Time) ([]ledger.Settlement, error) {
	tx, err := r.database.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	if _, err := tx.Exec(ctx, `UPDATE kuknos_settlements SET locked_at=NULL, locked_by='', updated_at=clock_timestamp() WHERE locked_at < clock_timestamp() - interval '10 minutes'`); err != nil {
		return nil, fmt.Errorf("release stale settlement locks: %w", err)
	}
	queryer, ok := tx.(txQueryer)
	if !ok {
		return nil, fmt.Errorf("settlement transaction does not support row queries")
	}
	rows, err := queryer.Query(ctx, `WITH due AS (
SELECT id FROM kuknos_settlements
WHERE status IN ('PENDING','RETRYABLE') AND available_at <= $1 AND locked_by=''
ORDER BY available_at, created_at FOR UPDATE SKIP LOCKED LIMIT $2
)
UPDATE kuknos_settlements s SET locked_at=clock_timestamp(), locked_by=$3, updated_at=clock_timestamp()
FROM due WHERE s.id=due.id
RETURNING s.id, s.source_service, s.source_transaction_id, s.idempotency_key, s.status, s.network, s.transaction_hash, s.attempts, s.available_at, s.last_error, s.signed_transaction_xdr`, now, limit, workerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var result []ledger.Settlement
	for rows.Next() {
		var s ledger.Settlement
		if err := rows.Scan(&s.ID, &s.SourceService, &s.SourceTxID, &s.IdempotencyKey, &s.Status, &s.Network, &s.TransactionHash, &s.Attempts, &s.AvailableAt, &s.LastError, &s.SignedTransactionXDR); err != nil {
			return nil, err
		}
		result = append(result, s)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	return result, nil
}

func (r *SettlementRepository) Save(ctx context.Context, s ledger.Settlement, workerID string) error {
	_, err := r.database.Exec(ctx, `UPDATE kuknos_settlements SET status=$2, transaction_hash=$3, attempts=$4, available_at=$5, last_error=$6, locked_at=NULL, locked_by='', submitted_at=CASE WHEN $2 IN ('SUBMITTED','CONFIRMED') THEN COALESCE(submitted_at, clock_timestamp()) ELSE submitted_at END, confirmed_at=CASE WHEN $2='CONFIRMED' THEN COALESCE(confirmed_at, clock_timestamp()) ELSE confirmed_at END, manual_review_at=CASE WHEN $2='MANUAL_REVIEW' THEN clock_timestamp() ELSE manual_review_at END, updated_at=clock_timestamp() WHERE id=$1 AND locked_by=$7`, s.ID, s.Status, s.TransactionHash, s.Attempts, s.AvailableAt, s.LastError, workerID)
	return err
}

func (r *SettlementRepository) RetryByOperator(ctx context.Context, id, actorID, reason string, now time.Time) (ledger.Settlement, error) {
	tx, err := r.database.Begin(ctx)
	if err != nil {
		return ledger.Settlement{}, fmt.Errorf("begin operator retry: %w", err)
	}
	defer tx.Rollback(ctx)
	var previous ledger.SettlementStatus
	if err := tx.QueryRow(ctx, `SELECT status FROM kuknos_settlements WHERE id=$1 FOR UPDATE`, id).Scan(&previous); err != nil {
		return ledger.Settlement{}, fmt.Errorf("lock settlement for operator retry: %w", err)
	}
	if previous == ledger.SettlementConfirmed {
		return ledger.Settlement{}, fmt.Errorf("confirmed settlement cannot be retried")
	}
	if _, err := tx.Exec(ctx, `UPDATE kuknos_settlements SET status='RETRYABLE', available_at=$2, locked_at=NULL, locked_by='', last_error='', manual_review_at=NULL, updated_at=clock_timestamp() WHERE id=$1`, id, now); err != nil {
		return ledger.Settlement{}, fmt.Errorf("retry settlement: %w", err)
	}
	if _, err := tx.Exec(ctx, `INSERT INTO kuknos_settlement_operator_audits (settlement_id, actor_id, action, reason, previous_status, resulting_status) VALUES ($1,$2,'RETRY',$3,$4,'RETRYABLE')`, id, actorID, reason, previous); err != nil {
		return ledger.Settlement{}, fmt.Errorf("audit operator retry: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		return ledger.Settlement{}, fmt.Errorf("commit operator retry: %w", err)
	}
	return r.Get(ctx, id, "")
}

// ReconcileConfirmationEvidence durably records confirmed Kuknos settlements
// whose latest GL lifecycle evidence is missing or has a different hash.
func (r *SettlementRepository) ReconcileConfirmationEvidence(ctx context.Context, grace time.Duration) (ReconciliationStats, error) {
	if grace <= 0 {
		grace = 10 * time.Minute
	}
	tx, err := r.database.Begin(ctx)
	if err != nil {
		return ReconciliationStats{}, err
	}
	defer tx.Rollback(ctx)
	var detected int64
	err = tx.QueryRow(ctx, `WITH inconsistent AS (
 SELECT s.id,
   CASE WHEN e.id IS NULL OR e.blockchain_transaction_hash='' THEN 'MISSING_LEDGER_CONFIRMATION' ELSE 'TRANSACTION_HASH_MISMATCH' END issue_type,
   s.transaction_hash expected_hash, COALESCE(e.blockchain_transaction_hash,'') actual_hash
 FROM kuknos_settlements s
 LEFT JOIN LATERAL (
   SELECT id, blockchain_transaction_hash FROM transaction_events
   WHERE source_service=s.source_service AND source_transaction_id=s.source_transaction_id
   ORDER BY event_version DESC, recorded_at DESC LIMIT 1
 ) e ON true
 WHERE s.status='CONFIRMED' AND s.confirmed_at < clock_timestamp() - $1::interval
   AND (e.id IS NULL OR e.blockchain_transaction_hash='' OR e.blockchain_transaction_hash<>s.transaction_hash)
), upserted AS (
 INSERT INTO kuknos_reconciliation_issues (settlement_id, issue_type, expected_hash, actual_hash)
 SELECT id, issue_type, expected_hash, actual_hash FROM inconsistent
 ON CONFLICT (settlement_id) DO UPDATE SET issue_type=EXCLUDED.issue_type,
 expected_hash=EXCLUDED.expected_hash, actual_hash=EXCLUDED.actual_hash,
 status='OPEN', last_detected_at=clock_timestamp(), resolved_at=NULL
 WHERE kuknos_reconciliation_issues.status<>'OPEN'
    OR kuknos_reconciliation_issues.issue_type<>EXCLUDED.issue_type
    OR kuknos_reconciliation_issues.expected_hash<>EXCLUDED.expected_hash
    OR kuknos_reconciliation_issues.actual_hash<>EXCLUDED.actual_hash
 RETURNING 1
) SELECT count(*) FROM upserted`, fmt.Sprintf("%f seconds", grace.Seconds())).Scan(&detected)
	if err != nil {
		return ReconciliationStats{}, fmt.Errorf("detect settlement reconciliation issues: %w", err)
	}
	result, err := tx.Exec(ctx, `UPDATE kuknos_reconciliation_issues i SET status='RESOLVED', resolved_at=clock_timestamp()
 WHERE status='OPEN' AND EXISTS (
   SELECT 1 FROM kuknos_settlements s JOIN transaction_events e
   ON e.source_service=s.source_service AND e.source_transaction_id=s.source_transaction_id
   WHERE s.id=i.settlement_id AND s.status='CONFIRMED'
   AND e.blockchain_transaction_hash=s.transaction_hash AND e.blockchain_transaction_hash<>''
 )`)
	if err != nil {
		return ReconciliationStats{}, fmt.Errorf("resolve settlement reconciliation issues: %w", err)
	}
	var open int64
	if err := tx.QueryRow(ctx, `SELECT count(*) FROM kuknos_reconciliation_issues WHERE status='OPEN'`).Scan(&open); err != nil {
		return ReconciliationStats{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return ReconciliationStats{}, err
	}
	return ReconciliationStats{Open: open, Detected: detected, Resolved: result.RowsAffected()}, nil
}