sqldb+payments: add payment_duplicates for legacy duplicate payments

diff --git a/payments/db/sql_store.go b/payments/db/sql_store.go
index 7f8f9e6..2c8dde9 100644
--- a/payments/db/sql_store.go
+++ b/payments/db/sql_store.go
@@ -57,6 +57,8 @@ type SQLQueries interface {
 	FetchAllInflightAttempts(ctx context.Context, arg sqlc.FetchAllInflightAttemptsParams) ([]sqlc.PaymentHtlcAttempt, error)
 	FetchHopsForAttempts(ctx context.Context, htlcAttemptIndices []int64) ([]sqlc.FetchHopsForAttemptsRow, error)
 
+	FetchPaymentDuplicates(ctx context.Context, paymentID int64) ([]sqlc.PaymentDuplicate, error)
+
 	FetchPaymentLevelFirstHopCustomRecords(ctx context.Context, paymentIDs []int64) ([]sqlc.PaymentFirstHopCustomRecord, error)
 	FetchRouteLevelFirstHopCustomRecords(ctx context.Context, htlcAttemptIndices []int64) ([]sqlc.PaymentAttemptFirstHopCustomRecord, error)
 	FetchHopLevelCustomRecords(ctx context.Context, hopIDs []int64) ([]sqlc.PaymentHopCustomRecord, error)
@@ -106,6 +108,9 @@ type SQLQueries interface {
 	// failure reason would exist yet.
 	InsertPaymentMig(ctx context.Context, arg sqlc.InsertPaymentMigParams) (int64, error)
 
+	// InsertPaymentDuplicateMig inserts a duplicate payment record during
+	// migration.
+	InsertPaymentDuplicateMig(ctx context.Context, arg sqlc.InsertPaymentDuplicateMigParams) (int64, error)
 }
 
 // BatchedSQLQueries is a version of the SQLQueries that's capable
diff --git a/sqldb/sqlc/migrations/000011_payment_duplicates.down.sql b/sqldb/sqlc/migrations/000011_payment_duplicates.down.sql
new file mode 100644
index 0000000..39c4a31
--- /dev/null
+++ b/sqldb/sqlc/migrations/000011_payment_duplicates.down.sql
@@ -0,0 +1,2 @@
+DROP INDEX IF EXISTS idx_payment_duplicates_payment_id;
+DROP TABLE IF EXISTS payment_duplicates;
diff --git a/sqldb/sqlc/migrations/000011_payment_duplicates.up.sql b/sqldb/sqlc/migrations/000011_payment_duplicates.up.sql
new file mode 100644
index 0000000..8abaf3e
--- /dev/null
+++ b/sqldb/sqlc/migrations/000011_payment_duplicates.up.sql
@@ -0,0 +1,43 @@
+-- ─────────────────────────────────────────────
+-- Payment Duplicate Records Table
+-- ─────────────────────────────────────────────
+-- Stores duplicate payment records that were created in older versions
+-- of lnd. This table is intentionally minimal and is expected to be dropped
+-- in the future especially if no duplicates were migrated.
+-- ─────────────────────────────────────────────
+
+CREATE TABLE IF NOT EXISTS payment_duplicates (
+    -- Primary key for the duplicate record.
+    id INTEGER PRIMARY KEY,
+
+    -- Reference to the primary payment this duplicate belongs to.
+    payment_id BIGINT NOT NULL REFERENCES payments (id) ON DELETE CASCADE,
+
+    -- Amount of the duplicate payment in millisatoshis.
+    amount_msat BIGINT NOT NULL,
+
+    -- Timestamp when the duplicate payment was created.
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+    -- Failure reason for failed payments (if known).
+    fail_reason INTEGER,
+
+    -- Settlement payload for succeeded payments (if known).
+    settle_preimage BLOB,
+
+    -- Settlement time for succeeded payments (if known).
+    settle_time TIMESTAMP,
+
+    -- Ensure we record either a failure reason or settlement data.
+    -- During the migration if we encounter a duplicate payment that has no
+    -- failure reason or settlement data, we will mark it as failed. Duplicate
+    -- payments were a bug in older versions of LND, so we can be sure if a
+    -- duplicate payment has no failure reason or settlement data, the
+    -- corresponding HTLC has been failed.
+    CONSTRAINT chk_payment_duplicates_outcome
+    CHECK (fail_reason IS NOT NULL OR settle_preimage IS NOT NULL)
+);
+
+-- Index for efficient lookup by primary payment.
+CREATE INDEX IF NOT EXISTS idx_payment_duplicates_payment_id
+ON payment_duplicates(payment_id);
diff --git a/sqldb/sqlc/models.go b/sqldb/sqlc/models.go
index 0e04cd4..513f045 100644
--- a/sqldb/sqlc/models.go
+++ b/sqldb/sqlc/models.go
@@ -224,6 +224,16 @@ type PaymentAttemptFirstHopCustomRecord struct {
 	Value            []byte
 }
 
+type PaymentDuplicate struct {
+	ID             int64
+	PaymentID      int64
+	AmountMsat     int64
+	CreatedAt      time.Time
+	FailReason     sql.NullInt32
+	SettlePreimage []byte
+	SettleTime     sql.NullTime
+}
+
 type PaymentFirstHopCustomRecord struct {
 	ID        int64
 	PaymentID int64
diff --git a/sqldb/sqlc/payments.sql.go b/sqldb/sqlc/payments.sql.go
index fe02be4..5ac3d82 100644
--- a/sqldb/sqlc/payments.sql.go
+++ b/sqldb/sqlc/payments.sql.go
@@ -479,6 +479,53 @@ func (q *Queries) FetchPayment(ctx context.Context, paymentIdentifier []byte) (F
 	return i, err
 }
 
+const fetchPaymentDuplicates = `-- name: FetchPaymentDuplicates :many
+SELECT
+    id,
+    payment_id,
+    amount_msat,
+    created_at,
+    fail_reason,
+    settle_preimage,
+    settle_time
+FROM payment_duplicates
+WHERE payment_id = $1
+ORDER BY id ASC
+`
+
+// Fetch all duplicate payment records from the payment_duplicates table for
+// a given payment ID.
+func (q *Queries) FetchPaymentDuplicates(ctx context.Context, paymentID int64) ([]PaymentDuplicate, error) {
+	rows, err := q.db.QueryContext(ctx, fetchPaymentDuplicates, paymentID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []PaymentDuplicate
+	for rows.Next() {
+		var i PaymentDuplicate
+		if err := rows.Scan(
+			&i.ID,
+			&i.PaymentID,
+			&i.AmountMsat,
+			&i.CreatedAt,
+			&i.FailReason,
+			&i.SettlePreimage,
+			&i.SettleTime,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
 const fetchPaymentLevelFirstHopCustomRecords = `-- name: FetchPaymentLevelFirstHopCustomRecords :many
 SELECT
     l.id,
@@ -909,6 +956,51 @@ func (q *Queries) InsertPaymentAttemptFirstHopCustomRecord(ctx context.Context,
 	return err
 }
 
+const insertPaymentDuplicateMig = `-- name: InsertPaymentDuplicateMig :one
+INSERT INTO payment_duplicates (
+    payment_id,
+    amount_msat,
+    created_at,
+    fail_reason,
+    settle_preimage,
+    settle_time
+)
+VALUES (
+    $1,
+    $2,
+    $3,
+    $4,
+    $5,
+    $6
+)
+RETURNING id
+`
+
+type InsertPaymentDuplicateMigParams struct {
+	PaymentID      int64
+	AmountMsat     int64
+	CreatedAt      time.Time
+	FailReason     sql.NullInt32
+	SettlePreimage []byte
+	SettleTime     sql.NullTime
+}
+
+// Insert a duplicate payment record into the payment_duplicates table and
+// return its ID.
+func (q *Queries) InsertPaymentDuplicateMig(ctx context.Context, arg InsertPaymentDuplicateMigParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, insertPaymentDuplicateMig,
+		arg.PaymentID,
+		arg.AmountMsat,
+		arg.CreatedAt,
+		arg.FailReason,
+		arg.SettlePreimage,
+		arg.SettleTime,
+	)
+	var id int64
+	err := row.Scan(&id)
+	return id, err
+}
+
 const insertPaymentFirstHopCustomRecord = `-- name: InsertPaymentFirstHopCustomRecord :exec
 INSERT INTO payment_first_hop_custom_records (
     payment_id,
diff --git a/sqldb/sqlc/querier.go b/sqldb/sqlc/querier.go
index 02f2297..9472373 100644
--- a/sqldb/sqlc/querier.go
+++ b/sqldb/sqlc/querier.go
@@ -49,6 +49,9 @@ type Querier interface {
 	FetchHtlcAttemptResolutionsForPayments(ctx context.Context, paymentIds []int64) ([]FetchHtlcAttemptResolutionsForPaymentsRow, error)
 	FetchHtlcAttemptsForPayments(ctx context.Context, paymentIds []int64) ([]FetchHtlcAttemptsForPaymentsRow, error)
 	FetchPayment(ctx context.Context, paymentIdentifier []byte) (FetchPaymentRow, error)
+	// Fetch all duplicate payment records from the payment_duplicates table for
+	// a given payment ID.
+	FetchPaymentDuplicates(ctx context.Context, paymentID int64) ([]PaymentDuplicate, error)
 	FetchPaymentLevelFirstHopCustomRecords(ctx context.Context, paymentIds []int64) ([]PaymentFirstHopCustomRecord, error)
 	// Batch fetch payment and intent data for a set of payment IDs.
 	// Used to avoid fetching redundant payment data when processing multiple
@@ -177,6 +180,9 @@ type Querier interface {
 	// payment process.
 	InsertPayment(ctx context.Context, arg InsertPaymentParams) (int64, error)
 	InsertPaymentAttemptFirstHopCustomRecord(ctx context.Context, arg InsertPaymentAttemptFirstHopCustomRecordParams) error
+	// Insert a duplicate payment record into the payment_duplicates table and
+	// return its ID.
+	InsertPaymentDuplicateMig(ctx context.Context, arg InsertPaymentDuplicateMigParams) (int64, error)
 	InsertPaymentFirstHopCustomRecord(ctx context.Context, arg InsertPaymentFirstHopCustomRecordParams) error
 	InsertPaymentHopCustomRecord(ctx context.Context, arg InsertPaymentHopCustomRecordParams) error
 	// Insert a payment intent for a given payment and return its ID.
diff --git a/sqldb/sqlc/queries/payments.sql b/sqldb/sqlc/queries/payments.sql
index 46f84e4..1bb2c50 100644
--- a/sqldb/sqlc/queries/payments.sql
+++ b/sqldb/sqlc/queries/payments.sql
@@ -40,6 +40,21 @@ FROM payments p
 LEFT JOIN payment_intents i ON i.payment_id = p.id
 WHERE p.payment_identifier = $1;
 
+-- name: FetchPaymentDuplicates :many
+-- Fetch all duplicate payment records from the payment_duplicates table for
+-- a given payment ID.
+SELECT
+    id,
+    payment_id,
+    amount_msat,
+    created_at,
+    fail_reason,
+    settle_preimage,
+    settle_time
+FROM payment_duplicates
+WHERE payment_id = $1
+ORDER BY id ASC;
+
 -- name: CountPayments :one
 SELECT COUNT(*) FROM payments;
 
@@ -408,4 +423,25 @@ FROM payments p
 LEFT JOIN payment_htlc_attempts ha ON ha.payment_id = p.id
 WHERE p.id IN (sqlc.slice('payment_ids')/*SLICE:payment_ids*/)
 GROUP BY p.id, p.amount_msat, p.created_at, p.payment_identifier, p.fail_reason
-ORDER BY p.id ASC;
\ No newline at end of file
+ORDER BY p.id ASC;
+
+-- name: InsertPaymentDuplicateMig :one
+-- Insert a duplicate payment record into the payment_duplicates table and 
+-- return its ID.
+INSERT INTO payment_duplicates (
+    payment_id,
+    amount_msat,
+    created_at,
+    fail_reason,
+    settle_preimage,
+    settle_time
+)
+VALUES (
+    @payment_id,
+    @amount_msat,
+    @created_at,
+    @fail_reason,
+    @settle_preimage,
+    @settle_time
+)
+RETURNING id;