breez_sdk_core/persist/
transactions.rs

1use std::collections::{HashMap, HashSet};
2use std::str::FromStr;
3
4use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, Type, ValueRef};
5use rusqlite::Row;
6use rusqlite::{named_params, params, OptionalExtension};
7use sdk_common::prelude::*;
8use serde_json::{Map, Value};
9
10use super::db::SqliteStorage;
11use super::error::{PersistError, PersistResult};
12use crate::models::*;
13
14const METADATA_MAX_LEN: usize = 1000;
15
16#[cfg_attr(test, mockall::automock)]
17pub(crate) trait PaymentStorage: Send + Sync {
18    fn get_completed_payment_by_hash(&self, hash: &str) -> PersistResult<Option<Payment>>;
19    fn get_open_channel_bolt11_by_hash(&self, hash: &str) -> PersistResult<Option<String>>;
20}
21
22impl PaymentStorage for SqliteStorage {
23    /// Looks up a completed payment by hash.
24    ///
25    /// To include pending or failed payments in the lookup as well, use [Self::get_payment_by_hash]
26    fn get_completed_payment_by_hash(&self, hash: &str) -> PersistResult<Option<Payment>> {
27        let res = self
28            .get_payment_by_hash(hash)?
29            .filter(|p| p.status == PaymentStatus::Complete);
30        Ok(res)
31    }
32
33    /// Look up a modified open channel bolt11 by hash.
34    fn get_open_channel_bolt11_by_hash(&self, hash: &str) -> PersistResult<Option<String>> {
35        Ok(self
36            .get_connection()?
37            .query_row(
38                "
39          SELECT o.open_channel_bolt11           
40          FROM sync.open_channel_payment_info o        
41          WHERE
42           payment_hash = ?1",
43                [hash],
44                |row| row.get(0),
45            )
46            .optional()?)
47    }
48}
49
50impl SqliteStorage {
51    /// Inserts payments into the payments table. These can be pending, completed and failed payments. Before
52    /// persisting, it automatically deletes previously pending payments
53    ///
54    /// Note that, if a payment has details of type [LnPaymentDetails] which contain a [SuccessActionProcessed],
55    /// then the [LnPaymentDetails] will NOT be persisted. In that case, the [SuccessActionProcessed]
56    /// can be inserted separately via [SqliteStorage::insert_payment_external_info].
57    pub fn insert_or_update_payments(
58        &self,
59        transactions: &[Payment],
60        is_pseudo: bool,
61    ) -> PersistResult<()> {
62        let con = self.get_connection()?;
63        let mut prep_statement = con.prepare(
64            "
65         INSERT OR REPLACE INTO payments (
66           id,
67           payment_type,                 
68           payment_time,                                  
69           amount_msat, 
70           fee_msat,                 
71           status,
72           description,
73           details,
74           is_pseudo
75        )
76         VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9)
77        ",
78        )?;
79
80        for ln_tx in transactions {
81            _ = prep_statement.execute((
82                &ln_tx.id,
83                &ln_tx.payment_type.to_string(),
84                &ln_tx.payment_time,
85                &ln_tx.amount_msat,
86                &ln_tx.fee_msat,
87                &ln_tx.status,
88                &ln_tx.description,
89                &ln_tx.details,
90                &is_pseudo,
91            ))?;
92        }
93        Ok(())
94    }
95
96    pub fn delete_pseudo_payments(&self) -> PersistResult<()> {
97        let con = self.get_connection()?;
98        let mut stmt = con.prepare("DELETE FROM payments where is_pseudo=1")?;
99        let res = stmt.execute([])?;
100        if res > 0 {
101            debug!("deleted {} pseudo-payments", res);
102        }
103
104        Ok(())
105    }
106
107    /// Inserts metadata associated with this payment
108    pub fn insert_payment_external_info(
109        &self,
110        payment_hash: &str,
111        payment_external_info: PaymentExternalInfo,
112    ) -> PersistResult<()> {
113        let con = self.get_connection()?;
114        let mut prep_statement = con.prepare(
115            "
116         INSERT OR REPLACE INTO sync.payments_external_info (
117           payment_id,
118           lnurl_success_action,
119           lnurl_pay_domain,
120           lnurl_pay_comment,
121           lnurl_metadata,
122           ln_address,
123           lnurl_withdraw_endpoint,
124           attempted_amount_msat,
125           attempted_error
126         )
127         VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9)
128        ",
129        )?;
130
131        _ = prep_statement.execute((
132            payment_hash,
133            serde_json::to_string(&payment_external_info.lnurl_pay_success_action)?,
134            payment_external_info.lnurl_pay_domain,
135            payment_external_info.lnurl_pay_comment,
136            payment_external_info.lnurl_metadata,
137            payment_external_info.ln_address,
138            payment_external_info.lnurl_withdraw_endpoint,
139            payment_external_info.attempted_amount_msat,
140            payment_external_info.attempted_error,
141        ))?;
142
143        Ok(())
144    }
145
146    /// Updates the metadata object associated to a payment
147    pub fn set_payment_external_metadata(
148        &self,
149        payment_hash: String,
150        new_metadata: String,
151    ) -> PersistResult<()> {
152        ensure_sdk!(
153            new_metadata.len() <= METADATA_MAX_LEN,
154            PersistError::Generic(format!(
155                "Max metadata size ({METADATA_MAX_LEN} characters) has been exceeded"
156            ))
157        );
158
159        let _ = serde_json::from_str::<Map<String, Value>>(&new_metadata)?;
160
161        // Check if the payment exists
162        let payment_exists = self
163            .get_connection()?
164            .prepare("SELECT 1 FROM payments WHERE id = ?1;")?
165            .exists(params![payment_hash])?;
166
167        if !payment_exists {
168            return Err(PersistError::generic("Payment not found"));
169        }
170
171        self.get_connection()?.execute(
172            "
173             INSERT OR REPLACE INTO sync.payments_metadata(
174                payment_id,
175                metadata,
176                updated_at
177             )
178             VALUES (
179                ?1,
180                json(?2),
181                CURRENT_TIMESTAMP
182             );",
183            params![payment_hash, new_metadata],
184        )?;
185
186        Ok(())
187    }
188
189    /// Updates attempted error data associated with this payment
190    pub fn update_payment_attempted_error(
191        &self,
192        payment_hash: &str,
193        attempted_error: Option<String>,
194    ) -> PersistResult<()> {
195        self.get_connection()?.execute(
196            "UPDATE sync.payments_external_info SET attempted_error=:attempted_error WHERE payment_id=:payment_id",
197            named_params! {
198             ":payment_id": payment_hash,
199             ":attempted_error": attempted_error,
200            },
201        )?;
202
203        Ok(())
204    }
205
206    /// Inserts payer amount for invoices that require opening a channel.
207    pub fn insert_open_channel_payment_info(
208        &self,
209        payment_hash: &str,
210        payer_amount_msat: u64,
211        open_channel_bolt11: &str,
212    ) -> PersistResult<()> {
213        let con = self.get_connection()?;
214        let mut prep_statement = con.prepare(
215            "
216        INSERT OR IGNORE INTO sync.open_channel_payment_info (
217          payment_hash,
218          payer_amount_msat,
219          open_channel_bolt11
220        )
221        VALUES (?1,?2,?3)
222       ",
223        )?;
224
225        _ = prep_statement.execute((payment_hash, payer_amount_msat, open_channel_bolt11))?;
226
227        Ok(())
228    }
229
230    /// Constructs [Payment] by joining data in the `payment` and `payments_external_info` tables
231    ///
232    /// This queries all payments. To query a single payment, see [Self::get_payment_by_hash]
233    /// or [Self::get_completed_payment_by_hash]
234    pub fn list_payments(&self, req: ListPaymentsRequest) -> PersistResult<Vec<Payment>> {
235        let where_clause = filter_to_where_clause(
236            req.filters,
237            &req.metadata_filters,
238            req.from_timestamp,
239            req.to_timestamp,
240            req.include_failures,
241        );
242        let offset = req.offset.unwrap_or(0u32);
243        let limit = req.limit.unwrap_or(u32::MAX);
244        let con = self.get_connection()?;
245        let query = self.select_payments_query(where_clause.as_str(), offset, limit)?;
246        let mut stmt = con.prepare(query.as_str())?;
247
248        let mut params: HashMap<String, String> = HashMap::new();
249
250        if let Some(metadata_filters) = &req.metadata_filters {
251            metadata_filters.iter().enumerate().for_each(
252                |(
253                    i,
254                    MetadataFilter {
255                        json_path,
256                        json_value,
257                    },
258                )| {
259                    params.insert(format!(":json_path_{i}"), format!("$.{json_path}"));
260                    params.insert(format!(":json_value_{i}"), json_value.clone());
261                },
262            )
263        }
264
265        let vec: Vec<Payment> = stmt
266            .query_map(
267                params
268                    .iter()
269                    .map(|(k, v)| (k.as_str(), v as &dyn ToSql))
270                    .collect::<Vec<(&str, &dyn ToSql)>>()
271                    .as_slice(),
272                |row| self.sql_row_to_payment(row),
273            )?
274            .map(|i| i.unwrap())
275            .collect();
276        Ok(vec)
277    }
278
279    pub fn select_payments_query(
280        &self,
281        where_clause: &str,
282        offset: u32,
283        limit: u32,
284    ) -> PersistResult<String> {
285        let swap_fields = self.select_swap_fields("swaps_");
286        let swap_query = self.select_swap_query("true", "swaps_");
287        let rev_swap_fields = self.select_reverse_swap_fields("revswaps_");
288        let rev_swap_query = self.select_reverse_swap_query("true", "revswaps_");
289        let query = format!(
290            "
291          SELECT 
292           p.id,
293           p.payment_type,
294           p.payment_time,
295           p.amount_msat,
296           p.fee_msat,
297           p.status,
298           p.description,
299           p.details,
300           e.lnurl_success_action,
301           e.lnurl_metadata,
302           e.ln_address,
303           e.lnurl_withdraw_endpoint,
304           e.attempted_amount_msat,
305           e.attempted_error,
306           o.payer_amount_msat,
307           o.open_channel_bolt11,
308           m.metadata,
309           e.lnurl_pay_domain,
310           e.lnurl_pay_comment,
311           {swap_fields},
312           {rev_swap_fields}
313          FROM payments p
314          LEFT JOIN sync.payments_external_info e
315          ON
316           p.id = e.payment_id
317          LEFT JOIN sync.payments_metadata m
318          ON
319            p.id = m.payment_id
320          LEFT JOIN sync.open_channel_payment_info o
321           ON
322            p.id = o.payment_hash
323          LEFT JOIN ({swap_query}) as swaps
324           ON
325            p.id = hex(swaps_payment_hash) COLLATE NOCASE
326          LEFT JOIN ({rev_swap_query}) as revswaps
327           ON
328            json_extract(p.details, '$.payment_preimage') = hex(revswaps_preimage) COLLATE NOCASE
329          {where_clause}
330          ORDER BY payment_time DESC
331          LIMIT {limit}
332          OFFSET {offset}
333        "
334        );
335
336        Ok(query)
337    }
338
339    /// This queries a single payment by hash, which may be pending, completed or failed.
340    ///
341    /// To lookup a completed payment by hash, use [Self::get_completed_payment_by_hash]
342    ///
343    /// To query all payments, see [Self::list_payments]
344    pub(crate) fn get_payment_by_hash(&self, hash: &str) -> PersistResult<Option<Payment>> {
345        let query = self.select_payments_query("where id = ?1", 0, 1)?;
346        Ok(self
347            .get_connection()?
348            .query_row(query.as_str(), [hash], |row| self.sql_row_to_payment(row))
349            .optional()?)
350    }
351
352    /// Look up a modified open channel bolt11 by hash.
353    #[allow(unused)]
354    pub(crate) fn get_open_channel_bolt11_by_hash(
355        &self,
356        hash: &str,
357    ) -> PersistResult<Option<String>> {
358        Ok(self
359            .get_connection()?
360            .query_row(
361                "
362          SELECT
363           o.open_channel_bolt11           
364          FROM sync.open_channel_payment_info o        
365          WHERE
366           payment_hash = ?1",
367                [hash],
368                |row| row.get(0),
369            )
370            .optional()?)
371    }
372
373    fn sql_row_to_payment(&self, row: &Row) -> PersistResult<Payment, rusqlite::Error> {
374        let payment_type_str: String = row.get(1)?;
375        let amount_msat = row.get(3)?;
376        let status: PaymentStatus = row.get(5)?;
377        let attempted_amount_msat: Option<u64> = row.get(12)?;
378        let mut payment = Payment {
379            id: row.get(0)?,
380            payment_type: PaymentType::from_str(payment_type_str.as_str()).unwrap(),
381            payment_time: row.get(2)?,
382            amount_msat: match status {
383                PaymentStatus::Complete => amount_msat,
384                _ => attempted_amount_msat.unwrap_or(amount_msat),
385            },
386            fee_msat: row.get(4)?,
387            status,
388            description: row.get(6)?,
389            details: row.get(7)?,
390            error: row.get(13)?,
391            metadata: row.get(16)?,
392        };
393
394        if let PaymentDetails::Ln { ref mut data } = payment.details {
395            let lnurl_success_action_str: Option<String> = row.get(8)?;
396            data.lnurl_success_action = match lnurl_success_action_str {
397                None => None,
398                Some(s) => serde_json::from_str(&s).map_err(|e| {
399                    rusqlite::Error::FromSqlConversionFailure(8, Type::Text, Box::new(e))
400                })?,
401            };
402
403            data.lnurl_pay_domain = row.get(17)?;
404            data.lnurl_pay_comment = row.get(18)?;
405            data.lnurl_metadata = row.get(9)?;
406            data.ln_address = row.get(10)?;
407            data.lnurl_withdraw_endpoint = row.get(11)?;
408            data.swap_info = self.sql_row_to_swap(row, "swaps_").ok();
409            if let Ok(fr) = self.sql_row_to_reverse_swap(row, "revswaps_") {
410                data.reverse_swap_info = Some(fr.get_reverse_swap_info_using_cached_values());
411            }
412        }
413
414        // In case we have a record of the open channel fee, let's use it.
415        let payer_amount_msat: Option<u64> = row.get(14)?;
416        if let Some(payer_amount) = payer_amount_msat {
417            payment.fee_msat = payer_amount - amount_msat;
418        }
419
420        // Add the payer invoice if it exists, in case of a received payment
421        if let Some(open_channel_bolt11) = row.get(15)? {
422            if let PaymentDetails::Ln { data } = &mut payment.details {
423                data.open_channel_bolt11 = Some(open_channel_bolt11);
424            }
425        }
426
427        Ok(payment)
428    }
429}
430
431fn filter_to_where_clause(
432    type_filters: Option<Vec<PaymentTypeFilter>>,
433    metadata_filters: &Option<Vec<MetadataFilter>>,
434    from_timestamp: Option<i64>,
435    to_timestamp: Option<i64>,
436    include_failures: Option<bool>,
437) -> String {
438    let mut where_clause: Vec<String> = Vec::new();
439    let with_failures = include_failures.unwrap_or(false);
440
441    if let Some(t) = from_timestamp {
442        where_clause.push(format!("payment_time >= {t}"));
443    };
444    if let Some(t) = to_timestamp {
445        where_clause.push(format!("payment_time <= {t}"));
446    };
447    if !with_failures {
448        where_clause.push(format!("status != {}", PaymentStatus::Failed as i64));
449    };
450
451    if let Some(filters) = type_filters {
452        if !filters.is_empty() {
453            let mut type_filter_clause: HashSet<PaymentType> = HashSet::new();
454            for type_filter in filters {
455                match type_filter {
456                    PaymentTypeFilter::Sent => {
457                        type_filter_clause.insert(PaymentType::Sent);
458                    }
459                    PaymentTypeFilter::Received => {
460                        type_filter_clause.insert(PaymentType::Received);
461                    }
462                    PaymentTypeFilter::ClosedChannel => {
463                        type_filter_clause.insert(PaymentType::ClosedChannel);
464                    }
465                }
466            }
467
468            where_clause.push(format!(
469                "payment_type in ({})",
470                type_filter_clause
471                    .iter()
472                    .map(|t| format!("'{t}'"))
473                    .collect::<Vec<_>>()
474                    .join(", ")
475            ));
476        }
477    }
478
479    if let Some(filters) = metadata_filters {
480        filters.iter().enumerate().for_each(|(i, _)| {
481            where_clause.push(format!("metadata->:json_path_{i} = :json_value_{i}"));
482        });
483    }
484
485    let mut where_clause_str = String::new();
486    if !where_clause.is_empty() {
487        where_clause_str = String::from("where ");
488        where_clause_str.push_str(where_clause.join(" and ").as_str());
489    }
490    where_clause_str
491}
492
493impl FromSql for PaymentDetails {
494    fn column_result(value: rusqlite::types::ValueRef<'_>) -> rusqlite::types::FromSqlResult<Self> {
495        serde_json::from_str(value.as_str()?).map_err(|_| FromSqlError::InvalidType)
496    }
497}
498
499impl ToSql for PaymentDetails {
500    fn to_sql(&self) -> rusqlite::Result<rusqlite::types::ToSqlOutput<'_>> {
501        Ok(ToSqlOutput::from(
502            serde_json::to_string(&self).map_err(|_| FromSqlError::InvalidType)?,
503        ))
504    }
505}
506
507impl FromSql for PaymentStatus {
508    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
509        match value {
510            ValueRef::Integer(i) => match i as u8 {
511                0 => Ok(PaymentStatus::Pending),
512                1 => Ok(PaymentStatus::Complete),
513                2 => Ok(PaymentStatus::Failed),
514                _ => Err(FromSqlError::OutOfRange(i)),
515            },
516            _ => Err(FromSqlError::InvalidType),
517        }
518    }
519}
520
521impl ToSql for PaymentStatus {
522    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
523        Ok(rusqlite::types::ToSqlOutput::from(*self as i64))
524    }
525}
526
527#[cfg(test)]
528mod test {
529    use crate::{
530        persist::{db::SqliteStorage, error::PersistResult, swap::SwapStorage},
531        FullReverseSwapInfo, ListPaymentsRequest, MetadataFilter, OpeningFeeParams,
532        PaymentExternalInfo, PaymentStatus, PaymentType, PaymentTypeFilter, ReverseSwapInfo,
533        ReverseSwapInfoCached, ReverseSwapStatus, SwapInfo, SwapStatus,
534    };
535
536    #[test]
537    fn test_ln_transactions() -> PersistResult<(), Box<dyn std::error::Error>> {
538        use sdk_common::prelude::*;
539
540        use crate::models::{LnPaymentDetails, Payment, PaymentDetails};
541        use crate::persist::test_utils;
542
543        let lnurl_metadata = "{'key': 'sample-metadata-val'}";
544        let test_ln_address = "test@ln.adddress.com";
545        let test_lnurl_pay_domain = "example.com";
546        let test_lnurl_pay_comment = "Thank you Satoshi!";
547        let sa = SuccessActionProcessed::Message {
548            data: MessageSuccessActionData {
549                message: "test message".into(),
550            },
551        };
552
553        let payment_hash_with_lnurl_success_action = "123";
554        let payment_hash_with_lnurl_withdraw = "124";
555        let payment_hash_with_swap_info: Vec<u8> = vec![234, 12, 53, 124];
556        let payment_hash_with_lnurl_domain = "126";
557        let payment_hash_with_rev_swap_info: Vec<u8> = vec![8, 7, 6, 5, 4, 3, 2, 1];
558        let lnurl_withdraw_url = "https://test.lnurl.withdraw.link";
559        let swap_info = SwapInfo {
560            bitcoin_address: "123".to_string(),
561            created_at: 1234567,
562            lock_height: 7654321,
563            payment_hash: payment_hash_with_swap_info.clone(),
564            preimage: vec![1, 2, 3],
565            private_key: vec![3, 2, 1],
566            public_key: vec![1, 3, 2],
567            swapper_public_key: vec![2, 1, 3],
568            script: vec![2, 3, 1],
569            bolt11: Some("swap_bolt11".into()),
570            paid_msat: 50_000,
571            confirmed_sats: 50,
572            unconfirmed_sats: 0,
573            total_incoming_txs: 1,
574            status: SwapStatus::Refundable,
575            refund_tx_ids: vec![],
576            unconfirmed_tx_ids: vec![],
577            confirmed_tx_ids: vec![],
578            min_allowed_deposit: 5_000,
579            max_allowed_deposit: 1_000_000,
580            max_swapper_payable: 2_000_000,
581            last_redeem_error: None,
582            channel_opening_fees: Some(OpeningFeeParams {
583                min_msat: 5_000_000,
584                proportional: 50,
585                valid_until: "date".to_string(),
586                max_idle_time: 12345,
587                max_client_to_self_delay: 234,
588                promise: "promise".to_string(),
589            }),
590            confirmed_at: Some(555),
591        };
592        let rev_swap_preimage = vec![4, 4, 4, 4];
593        let full_ref_swap_info = FullReverseSwapInfo {
594            id: "rev_swap_id".to_string(),
595            created_at_block_height: 0,
596            preimage: rev_swap_preimage.clone(),
597            private_key: vec![],
598            claim_pubkey: "claim_pubkey".to_string(),
599            timeout_block_height: 600_000,
600            invoice: "645".to_string(),
601            redeem_script: "redeem_script".to_string(),
602            onchain_amount_sat: 250,
603            sat_per_vbyte: Some(50),
604            receive_amount_sat: None,
605            cache: ReverseSwapInfoCached {
606                status: ReverseSwapStatus::CompletedConfirmed,
607                lockup_txid: Some("lockup_txid".to_string()),
608                claim_txid: Some("claim_txid".to_string()),
609            },
610        };
611        let rev_swap_info = ReverseSwapInfo {
612            id: "rev_swap_id".to_string(),
613            claim_pubkey: "claim_pubkey".to_string(),
614            lockup_txid: Some("lockup_txid".to_string()),
615            claim_txid: Some("claim_txid".to_string()),
616            onchain_amount_sat: 250,
617            status: ReverseSwapStatus::CompletedConfirmed,
618        };
619        let txs = [
620            Payment {
621                id: payment_hash_with_lnurl_success_action.to_string(),
622                payment_type: PaymentType::Sent,
623                payment_time: 1001,
624                amount_msat: 100,
625                fee_msat: 20,
626                status: PaymentStatus::Complete,
627                error: None,
628                description: None,
629                details: PaymentDetails::Ln {
630                    data: LnPaymentDetails {
631                        payment_hash: payment_hash_with_lnurl_success_action.to_string(),
632                        label: "label".to_string(),
633                        destination_pubkey: "pubey".to_string(),
634                        payment_preimage: "1111".to_string(),
635                        keysend: true,
636                        bolt11: "bolt11".to_string(),
637                        lnurl_success_action: Some(sa.clone()),
638                        lnurl_pay_domain: None,
639                        lnurl_pay_comment: None,
640                        lnurl_metadata: Some(lnurl_metadata.to_string()),
641                        ln_address: Some(test_ln_address.to_string()),
642                        lnurl_withdraw_endpoint: None,
643                        swap_info: None,
644                        reverse_swap_info: None,
645                        pending_expiration_block: None,
646                        open_channel_bolt11: None,
647                    },
648                },
649                metadata: None,
650            },
651            Payment {
652                id: payment_hash_with_lnurl_withdraw.to_string(),
653                payment_type: PaymentType::Received,
654                payment_time: 1000,
655                amount_msat: 100,
656                fee_msat: 20,
657                status: PaymentStatus::Complete,
658                error: None,
659                description: Some("desc".to_string()),
660                details: PaymentDetails::Ln {
661                    data: LnPaymentDetails {
662                        payment_hash: payment_hash_with_lnurl_withdraw.to_string(),
663                        label: "label".to_string(),
664                        destination_pubkey: "pubey".to_string(),
665                        payment_preimage: "2222".to_string(),
666                        keysend: true,
667                        bolt11: "bolt11".to_string(),
668                        lnurl_success_action: None,
669                        lnurl_pay_domain: None,
670                        lnurl_pay_comment: None,
671                        lnurl_metadata: None,
672                        ln_address: None,
673                        lnurl_withdraw_endpoint: Some(lnurl_withdraw_url.to_string()),
674                        swap_info: None,
675                        reverse_swap_info: None,
676                        pending_expiration_block: None,
677                        open_channel_bolt11: None,
678                    },
679                },
680                metadata: None,
681            },
682            Payment {
683                id: hex::encode(payment_hash_with_swap_info.clone()),
684                payment_type: PaymentType::Received,
685                payment_time: 999,
686                amount_msat: 50_000,
687                fee_msat: 20,
688                status: PaymentStatus::Complete,
689                error: None,
690                description: Some("desc".to_string()),
691                details: PaymentDetails::Ln {
692                    data: LnPaymentDetails {
693                        payment_hash: hex::encode(payment_hash_with_swap_info),
694                        label: "label".to_string(),
695                        destination_pubkey: "pubkey".to_string(),
696                        payment_preimage: "3333".to_string(),
697                        keysend: false,
698                        bolt11: "swap_bolt11".to_string(),
699                        lnurl_success_action: None,
700                        lnurl_pay_domain: None,
701                        lnurl_pay_comment: None,
702                        lnurl_metadata: None,
703                        ln_address: None,
704                        lnurl_withdraw_endpoint: None,
705                        swap_info: Some(swap_info.clone()),
706                        reverse_swap_info: None,
707                        pending_expiration_block: None,
708                        open_channel_bolt11: None,
709                    },
710                },
711                metadata: None,
712            },
713            Payment {
714                id: hex::encode(payment_hash_with_rev_swap_info.clone()),
715                payment_type: PaymentType::Sent,
716                payment_time: 998,
717                amount_msat: 100_000,
718                fee_msat: 200,
719                status: PaymentStatus::Complete,
720                error: None,
721                description: Some("desc".to_string()),
722                details: PaymentDetails::Ln {
723                    data: LnPaymentDetails {
724                        payment_hash: hex::encode(payment_hash_with_rev_swap_info),
725                        label: "label".to_string(),
726                        destination_pubkey: "pubkey".to_string(),
727                        payment_preimage: hex::encode(rev_swap_preimage),
728                        keysend: false,
729                        bolt11: "swap_bolt11".to_string(),
730                        lnurl_success_action: None,
731                        lnurl_metadata: None,
732                        lnurl_pay_domain: None,
733                        lnurl_pay_comment: None,
734                        ln_address: None,
735                        lnurl_withdraw_endpoint: None,
736                        swap_info: None,
737                        reverse_swap_info: Some(rev_swap_info.clone()),
738                        pending_expiration_block: None,
739                        open_channel_bolt11: None,
740                    },
741                },
742                metadata: None,
743            },
744            Payment {
745                id: payment_hash_with_lnurl_domain.to_string(),
746                payment_type: PaymentType::Sent,
747                payment_time: 998,
748                amount_msat: 100,
749                fee_msat: 20,
750                status: PaymentStatus::Complete,
751                error: None,
752                description: None,
753                details: PaymentDetails::Ln {
754                    data: LnPaymentDetails {
755                        payment_hash: payment_hash_with_lnurl_domain.to_string(),
756                        label: "label".to_string(),
757                        destination_pubkey: "pubey".to_string(),
758                        payment_preimage: "payment_preimage".to_string(),
759                        keysend: true,
760                        bolt11: "bolt11".to_string(),
761                        lnurl_success_action: None,
762                        lnurl_pay_domain: Some(test_lnurl_pay_domain.to_string()),
763                        lnurl_pay_comment: Some(test_lnurl_pay_comment.to_string()),
764                        lnurl_metadata: Some(lnurl_metadata.to_string()),
765                        ln_address: None,
766                        lnurl_withdraw_endpoint: None,
767                        swap_info: None,
768                        reverse_swap_info: None,
769                        pending_expiration_block: None,
770                        open_channel_bolt11: None,
771                    },
772                },
773                metadata: None,
774            },
775        ];
776        let failed_txs = [Payment {
777            id: "125".to_string(),
778            payment_type: PaymentType::Sent,
779            payment_time: 2000,
780            amount_msat: 1000,
781            fee_msat: 0,
782            status: PaymentStatus::Failed,
783            error: None,
784            description: Some("desc".to_string()),
785            details: PaymentDetails::Ln {
786                data: LnPaymentDetails {
787                    payment_hash: "125".to_string(),
788                    label: "label".to_string(),
789                    destination_pubkey: "pubey".to_string(),
790                    payment_preimage: "4444".to_string(),
791                    keysend: true,
792                    bolt11: "bolt11".to_string(),
793                    lnurl_success_action: None,
794                    lnurl_pay_domain: None,
795                    lnurl_pay_comment: None,
796                    lnurl_metadata: None,
797                    ln_address: None,
798                    lnurl_withdraw_endpoint: None,
799                    swap_info: None,
800                    reverse_swap_info: None,
801                    pending_expiration_block: None,
802                    open_channel_bolt11: None,
803                },
804            },
805            metadata: None,
806        }];
807        let storage = SqliteStorage::new(test_utils::create_test_sql_dir());
808        storage.init()?;
809        storage.insert_or_update_payments(&txs, false)?;
810        storage.insert_or_update_payments(&failed_txs, false)?;
811        storage.insert_payment_external_info(
812            payment_hash_with_lnurl_success_action,
813            PaymentExternalInfo {
814                lnurl_pay_success_action: Some(sa.clone()),
815                lnurl_pay_domain: None,
816                lnurl_pay_comment: None,
817                lnurl_metadata: Some(lnurl_metadata.to_string()),
818                ln_address: Some(test_ln_address.to_string()),
819                lnurl_withdraw_endpoint: None,
820                attempted_amount_msat: None,
821                attempted_error: None,
822            },
823        )?;
824        storage.insert_payment_external_info(
825            payment_hash_with_lnurl_withdraw,
826            PaymentExternalInfo {
827                lnurl_pay_success_action: None,
828                lnurl_pay_domain: None,
829                lnurl_pay_comment: None,
830                lnurl_metadata: None,
831                ln_address: None,
832                lnurl_withdraw_endpoint: Some(lnurl_withdraw_url.to_string()),
833                attempted_amount_msat: None,
834                attempted_error: None,
835            },
836        )?;
837        storage.insert_swap(&swap_info)?;
838        storage.update_swap_bolt11(
839            swap_info.bitcoin_address.clone(),
840            swap_info.bolt11.clone().unwrap(),
841        )?;
842        storage.insert_payment_external_info(
843            payment_hash_with_lnurl_domain,
844            PaymentExternalInfo {
845                lnurl_pay_success_action: None,
846                lnurl_pay_domain: Some(test_lnurl_pay_domain.to_string()),
847                lnurl_pay_comment: Some(test_lnurl_pay_comment.to_string()),
848                lnurl_metadata: Some(lnurl_metadata.to_string()),
849                ln_address: None,
850                lnurl_withdraw_endpoint: None,
851                attempted_amount_msat: None,
852                attempted_error: None,
853            },
854        )?;
855        storage.insert_reverse_swap(&full_ref_swap_info)?;
856        storage
857            .update_reverse_swap_status("rev_swap_id", &ReverseSwapStatus::CompletedConfirmed)?;
858        storage.update_reverse_swap_lockup_txid("rev_swap_id", Some("lockup_txid".to_string()))?;
859        storage.update_reverse_swap_claim_txid("rev_swap_id", Some("claim_txid".to_string()))?;
860
861        // retrieve all
862        let retrieve_txs = storage.list_payments(ListPaymentsRequest::default())?;
863        assert_eq!(retrieve_txs.len(), 5);
864        assert_eq!(retrieve_txs, txs);
865
866        //test only sent
867        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
868            filters: Some(vec![
869                PaymentTypeFilter::Sent,
870                PaymentTypeFilter::ClosedChannel,
871            ]),
872            ..Default::default()
873        })?;
874        assert_eq!(retrieve_txs.len(), 3);
875        assert_eq!(retrieve_txs[0], txs[0]);
876        assert_eq!(retrieve_txs[1], txs[3]);
877        assert!(
878            matches!( &retrieve_txs[0].details, PaymentDetails::Ln {data: LnPaymentDetails {lnurl_success_action, ..}} if lnurl_success_action == &Some(sa))
879        );
880        assert!(
881            matches!( &retrieve_txs[0].details, PaymentDetails::Ln {data: LnPaymentDetails {lnurl_pay_domain, ln_address, ..}} if lnurl_pay_domain.is_none() && ln_address == &Some(test_ln_address.to_string()))
882        );
883        assert!(
884            matches!( &retrieve_txs[2].details, PaymentDetails::Ln {data: LnPaymentDetails {lnurl_pay_domain, ln_address, ..}} if lnurl_pay_domain == &Some(test_lnurl_pay_domain.to_string()) && ln_address.is_none())
885        );
886        assert!(
887            matches!( &retrieve_txs[1].details, PaymentDetails::Ln {data: LnPaymentDetails {reverse_swap_info: rev_swap, ..}} if rev_swap == &Some(rev_swap_info))
888        );
889
890        //test only received
891        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
892            filters: Some(vec![PaymentTypeFilter::Received]),
893            ..Default::default()
894        })?;
895        assert_eq!(retrieve_txs.len(), 2);
896        assert_eq!(retrieve_txs[0], txs[1]);
897        assert_eq!(retrieve_txs[1], txs[2]);
898        assert!(
899            matches!( &retrieve_txs[1].details, PaymentDetails::Ln {data: LnPaymentDetails {swap_info: swap, ..}} if swap == &Some(swap_info))
900        );
901
902        storage.insert_or_update_payments(&txs, false)?;
903        let retrieve_txs = storage.list_payments(ListPaymentsRequest::default())?;
904        assert_eq!(retrieve_txs.len(), 5);
905        assert_eq!(retrieve_txs, txs);
906
907        storage.insert_open_channel_payment_info("123", 150, "")?;
908        let retrieve_txs = storage.list_payments(ListPaymentsRequest::default())?;
909        assert_eq!(retrieve_txs[0].fee_msat, 50);
910
911        // test all with failures
912        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
913            include_failures: Some(true),
914            ..Default::default()
915        })?;
916        assert_eq!(retrieve_txs.len(), 6);
917
918        // test sent with failures
919        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
920            filters: Some(vec![
921                PaymentTypeFilter::Sent,
922                PaymentTypeFilter::ClosedChannel,
923            ]),
924            include_failures: Some(true),
925            ..Default::default()
926        })?;
927        assert_eq!(retrieve_txs.len(), 4);
928
929        // test limit
930        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
931            include_failures: Some(false),
932            limit: Some(1),
933            ..Default::default()
934        })?;
935        assert_eq!(retrieve_txs.len(), 1);
936
937        // test offset
938        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
939            include_failures: Some(false),
940            offset: Some(1),
941            limit: Some(1),
942            ..Default::default()
943        })?;
944        assert_eq!(retrieve_txs.len(), 1);
945        assert_eq!(retrieve_txs[0].id, payment_hash_with_lnurl_withdraw);
946
947        // test json metadata validation
948        assert!(storage
949            .set_payment_external_metadata(
950                payment_hash_with_lnurl_withdraw.to_string(),
951                r#"{ "malformed: true }"#.to_string()
952            )
953            .is_err());
954
955        // test metadata set and filter
956        let test_json = r#"{"supportsBoolean":true,"supportsInt":10,"supportsString":"supports string","supportsNested":{"value":[1,2]}}"#;
957        let test_json_filters = Some(vec![
958            MetadataFilter {
959                json_path: "supportsBoolean".to_string(),
960                json_value: "true".to_string(),
961            },
962            MetadataFilter {
963                json_path: "supportsInt".to_string(),
964                json_value: "10".to_string(),
965            },
966            MetadataFilter {
967                json_path: "supportsString".to_string(),
968                json_value: r#""supports string""#.to_string(),
969            },
970            MetadataFilter {
971                json_path: "supportsNested.value".to_string(),
972                json_value: "[1,2]".to_string(),
973            },
974        ]);
975
976        storage.set_payment_external_metadata(
977            payment_hash_with_lnurl_withdraw.to_string(),
978            test_json.to_string(),
979        )?;
980
981        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
982            metadata_filters: test_json_filters,
983            ..Default::default()
984        })?;
985        assert_eq!(retrieve_txs.len(), 1);
986        assert_eq!(retrieve_txs[0].id, payment_hash_with_lnurl_withdraw);
987        assert_eq!(retrieve_txs[0].metadata, Some(test_json.to_string()),);
988
989        // test open_channel_bolt11
990        storage.insert_open_channel_payment_info(
991            payment_hash_with_lnurl_withdraw,
992            150,
993            "original_invoice",
994        )?;
995
996        let open_channel_bolt11 = storage
997            .get_open_channel_bolt11_by_hash(payment_hash_with_lnurl_withdraw)?
998            .unwrap();
999        assert_eq!(open_channel_bolt11, "original_invoice");
1000
1001        let open_channel_bolt11 = storage.get_open_channel_bolt11_by_hash("non existing hash")?;
1002        assert_eq!(open_channel_bolt11, None);
1003
1004        let retrieve_txs = storage.list_payments(ListPaymentsRequest {
1005            filters: Some(vec![PaymentTypeFilter::Received]),
1006            ..Default::default()
1007        })?;
1008
1009        let filtered_txs: Vec<&Payment> = retrieve_txs
1010            .iter()
1011            .filter(|p| {
1012                if let PaymentDetails::Ln { data } = &p.details {
1013                    return data.open_channel_bolt11 == Some("original_invoice".to_string());
1014                }
1015                false
1016            })
1017            .collect();
1018
1019        assert_eq!(filtered_txs.len(), 1);
1020        assert_eq!(filtered_txs[0].id, payment_hash_with_lnurl_withdraw);
1021        assert!(matches!(filtered_txs[0].details, PaymentDetails::Ln { .. }));
1022        if let PaymentDetails::Ln { data } = &filtered_txs[0].details {
1023            assert_eq!(
1024                data.open_channel_bolt11,
1025                Some("original_invoice".to_string())
1026            );
1027        }
1028
1029        Ok(())
1030    }
1031}