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