pangolin/pay/internal/store/store.go

// Package store persists pay orders + orphan payments in SQLite (pure-Go
// modernc driver, no CGO — same choice as the control plane).
//
// Model: single fixed receiving address + a unique amount per order. Orders are
// matched by (amount == expect_amount) and (payment block time > order created),
// so a payment can never be misattributed to a later order that happens to share
// the same address.
package store

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

	_ "modernc.org/sqlite"
)

type Status string

const (
	StatusPending Status = "pending"
	StatusPaid    Status = "paid"
	StatusExpired Status = "expired"
)

// Order is one payment request. Amounts are micro-USDT (1e-6), matching the raw
// integer value of a TRC20 USDT transfer (USDT has 6 decimals). ExpectAmount is
// the *unique* amount (base price + a small unique tail).
type Order struct {
	OrderNo      string
	UserRef      string
	SKU          string
	ExpectAmount int64
	Address      string
	Status       Status
	TxID         string
	CreatedAt    time.Time
	ExpiresAt    time.Time
}

var ErrNotFound = errors.New("store: order not found")

type Store struct{ db *sql.DB }

func Open(dsn string) (*Store, error) {
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1) // SQLite: serialize writers
	s := &Store{db: db}
	if err := s.migrate(); err != nil {
		_ = db.Close()
		return nil, err
	}
	return s, nil
}

func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS pay_orders(
  order_no TEXT PRIMARY KEY,
  user_ref TEXT NOT NULL,
  sku TEXT NOT NULL,
  expect_amount INTEGER NOT NULL,
  address TEXT NOT NULL,
  status TEXT NOT NULL,
  tx_id TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
)`,
		`CREATE INDEX IF NOT EXISTS idx_orders_status ON pay_orders(status)`,
		`CREATE INDEX IF NOT EXISTS idx_orders_amount_created ON pay_orders(expect_amount, created_at)`,
		`CREATE INDEX IF NOT EXISTS idx_orders_user_status ON pay_orders(user_ref, status)`,
		`CREATE TABLE IF NOT EXISTS orphan_payments(
  tx_id TEXT PRIMARY KEY,
  address TEXT NOT NULL,
  value INTEGER NOT NULL,
  block_ts INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  handled INTEGER NOT NULL DEFAULT 0
)`,
	}
	for _, q := range stmts {
		if _, err := s.db.Exec(q); err != nil {
			return err
		}
	}
	return nil
}

func (s *Store) CreateOrder(ctx context.Context, o *Order) error {
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO pay_orders(order_no,user_ref,sku,expect_amount,address,status,created_at,expires_at)
		 VALUES(?,?,?,?,?,?,?,?)`,
		o.OrderNo, o.UserRef, o.SKU, o.ExpectAmount, o.Address, o.Status, o.CreatedAt.Unix(), o.ExpiresAt.Unix())
	return err
}

const cols = `order_no,user_ref,sku,expect_amount,address,status,tx_id,created_at,expires_at`

func scanOrder(sc interface{ Scan(...any) error }) (*Order, error) {
	o := &Order{}
	var created, expires int64
	if err := sc.Scan(&o.OrderNo, &o.UserRef, &o.SKU, &o.ExpectAmount, &o.Address, &o.Status, &o.TxID, &created, &expires); err != nil {
		return nil, err
	}
	o.CreatedAt = time.Unix(created, 0)
	o.ExpiresAt = time.Unix(expires, 0)
	return o, nil
}

func (s *Store) GetOrder(ctx context.Context, orderNo string) (*Order, error) {
	row := s.db.QueryRowContext(ctx, `SELECT `+cols+` FROM pay_orders WHERE order_no=?`, orderNo)
	o, err := scanOrder(row)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return o, err
}

func (s *Store) ListPending(ctx context.Context) ([]*Order, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT `+cols+` FROM pay_orders WHERE status=?`, StatusPending)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	var out []*Order
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, o)
	}
	return out, rows.Err()
}

// ActiveOrderByUser returns the user's pending order, or (nil, ErrNotFound) if
// none — used to enforce "one active order per user".
func (s *Store) ActiveOrderByUser(ctx context.Context, userRef string) (*Order, error) {
	row := s.db.QueryRowContext(ctx, `SELECT `+cols+` FROM pay_orders WHERE user_ref=? AND status=? LIMIT 1`, userRef, StatusPending)
	o, err := scanOrder(row)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	return o, err
}

// AmountRecentlyUsed reports whether any order with this expect_amount was
// created at/after sinceUnix — used to keep the unique amount collision-free
// within the late-payment window (so a stale payment can't match a new order).
func (s *Store) AmountRecentlyUsed(ctx context.Context, amount, sinceUnix int64) (bool, error) {
	var n int
	err := s.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM pay_orders WHERE expect_amount=? AND created_at>=?`, amount, sinceUnix).Scan(&n)
	return n > 0, err
}

// MarkPaid transitions pending->paid idempotently (only affects a still-pending
// row). Returns true if this call flipped it.
func (s *Store) MarkPaid(ctx context.Context, orderNo, txID string) (bool, error) {
	res, err := s.db.ExecContext(ctx,
		`UPDATE pay_orders SET status=?, tx_id=? WHERE order_no=? AND status=?`,
		StatusPaid, txID, orderNo, StatusPending)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}

// MarkExpired flips pending->expired for orders past their deadline.
func (s *Store) MarkExpired(ctx context.Context, now time.Time) (int64, error) {
	res, err := s.db.ExecContext(ctx,
		`UPDATE pay_orders SET status=? WHERE status=? AND expires_at < ?`,
		StatusExpired, StatusPending, now.Unix())
	if err != nil {
		return 0, err
	}
	n, _ := res.RowsAffected()
	return n, nil
}

// TxHandled reports whether a tx id has already been consumed — either matched
// to an order (pay_orders.tx_id) or recorded as an orphan. Guards idempotency.
func (s *Store) TxHandled(ctx context.Context, txID string) (bool, error) {
	var n int
	err := s.db.QueryRowContext(ctx,
		`SELECT (SELECT COUNT(*) FROM pay_orders WHERE tx_id=?) + (SELECT COUNT(*) FROM orphan_payments WHERE tx_id=?)`,
		txID, txID).Scan(&n)
	return n > 0, err
}

// RecordOrphan stores a payment that matched no active order (wrong amount / late
// after the address was reused). Idempotent on tx_id. Needs manual reconciliation.
func (s *Store) RecordOrphan(ctx context.Context, txID, address string, value, blockTs int64, now time.Time) error {
	_, err := s.db.ExecContext(ctx,
		`INSERT OR IGNORE INTO orphan_payments(tx_id,address,value,block_ts,created_at) VALUES(?,?,?,?,?)`,
		txID, address, value, blockTs, now.Unix())
	return err
}