backbone_pos/infrastructure/persistence/
pos_invoice_repository.rs

1//! Repository for PosInvoice entities
2//!
3//! Originally generated by metaphor-schema; now **user-owned** — this exact path is declared under
4//! `user_owned` in `metaphor.codegen.yaml`, so the generator skips it wholesale. The custom methods
5//! below hold the hand-written counter-ticket SQL (4-layer rule: services orchestrate, repos hold SQL).
6//!
7//! Thin newtype over `backbone_orm::GenericCrudRepository<PosInvoice, backbone_orm::SoftDelete>`.
8//! All standard CRUD methods are available via `Deref`.
9//!
10//! Tenancy is composition-installed (ADR-0029): pool reads ride the ambient org scope's
11//! request-dedicated connection (fail-narrow, never leaky — a row the scope cannot see is simply
12//! absent); connection-taking methods run on the CALLER'S open, scope-relayed transaction.
13
14use anyhow::Result;
15use rust_decimal::Decimal;
16use sqlx::{PgPool, Row};
17use uuid::Uuid;
18
19use backbone_orm::org_scope;
20// The row read twins ride the org-scope module (no scope invented); the scalar twins
21// still live only in the legacy `company_scope` module — what this repository needs
22// from both is the same connection discipline, the request-dedicated connection when
23// the composing service bound one, plain pool otherwise (ADR-0029).
24use backbone_orm::company_scope::{fetch_one_scalar_scoped, fetch_optional_scalar_scoped};
25use backbone_orm::org_scope::fetch_one_row_scoped;
26
27use crate::domain::entity::PosInvoice;
28
29/// Table name for PosInvoice entities
30pub const TABLE_NAME: &str = "pos.pos_invoices";
31
32/// Repository for PosInvoice entities.
33///
34/// All standard CRUD, soft-delete, pagination, and bulk methods are
35/// provided automatically via `Deref` to `backbone_orm::GenericCrudRepository`.
36pub struct PosInvoiceRepository(
37    backbone_orm::GenericCrudRepository<PosInvoice, backbone_orm::SoftDelete>,
38);
39
40impl std::ops::Deref for PosInvoiceRepository {
41    type Target = backbone_orm::GenericCrudRepository<PosInvoice, backbone_orm::SoftDelete>;
42    fn deref(&self) -> &Self::Target { &self.0 }
43}
44
45impl PosInvoiceRepository {
46    /// Create a new repository instance.
47    pub fn new(pool: PgPool) -> Self {
48        Self(backbone_orm::GenericCrudRepository::new(pool, TABLE_NAME))
49    }
50}
51
52/// The exact row a rung ticket writes. Mirrors the raw column shape rather than the `PosInvoice`
53/// entity: the money fields are already computed by the service, and `paid_total`/`change_due`/
54/// `is_return`/`status` are fixed by the SQL (`0,0,false,'draft'`). `client_uuid` carries the offline
55/// sync identity when the ticket is being replayed from a client (server-originated rings pass None).
56/// `pos_table_id` seats the ticket at a dining table (restaurant lanes); counter sales pass None.
57pub struct NewDraftInvoiceRow<'a> {
58    pub id: Uuid,
59    pub client_uuid: Option<Uuid>,
60    pub pos_profile_id: Uuid,
61    pub opening_entry_id: Uuid,
62    pub branch_id: Option<Uuid>,
63    pub customer_id: Option<Uuid>,
64    pub pos_table_id: Option<Uuid>,
65    pub receipt_number: &'a str,
66    pub posting_at: chrono::NaiveDateTime,
67    pub net_total: Decimal,
68    pub tax_total: Decimal,
69    pub grand_total: Decimal,
70    pub rounding_adjustment: Decimal,
71    pub rounded_total: Decimal,
72}
73
74/// A ticket looked up by its offline-sync identity — everything `sync_from_ui` needs to decide
75/// create-vs-update-vs-replay: identity anchors (session, partner), the refund lineage, and the
76/// persisted money (returned as the replay's totals when the ticket is already finalized).
77pub struct SyncLookupRow {
78    pub id: Uuid,
79    pub status: String,
80    pub pos_profile_id: Uuid,
81    pub opening_entry_id: Uuid,
82    pub branch_id: Option<Uuid>,
83    pub customer_id: Option<Uuid>,
84    /// The table the draft is currently seated at (for transfer validation on replay).
85    pub pos_table_id: Option<Uuid>,
86    pub is_return: bool,
87    /// For a refund ticket: the server id of the parent it was recorded against.
88    pub return_against: Option<Uuid>,
89    pub net_total: Decimal,
90    pub tax_total: Decimal,
91    pub grand_total: Decimal,
92    pub rounding_adjustment: Decimal,
93    pub rounded_total: Decimal,
94    pub paid_total: Decimal,
95    pub change_due: Decimal,
96}
97
98/// The exact row a return ticket writes. Mirrors the raw column shape: the SQL fixes
99/// `rounding_adjustment` to 0, binds `rounded_total` to both the rounded and paid totals ($12), fixes
100/// `change_due` to 0, and fixes `is_return`/`status` to `true`/`'returned'`.
101pub struct NewReturnInvoiceRow {
102    pub id: Uuid,
103    pub pos_profile_id: Uuid,
104    pub opening_entry_id: Uuid,
105    pub branch_id: Option<Uuid>,
106    pub customer_id: Option<Uuid>,
107    pub receipt_number: String,
108    pub posting_at: chrono::DateTime<chrono::Utc>,
109    pub net_total: Decimal,
110    pub tax_total: Decimal,
111    pub grand_total: Decimal,
112    pub rounded_total: Decimal,
113    pub billing_invoice_id: Uuid,
114    pub return_against: Uuid,
115}
116
117/// A ticket header as `add_tender` reads it: the draft gate and the target total.
118pub struct TenderHeaderRow {
119    pub status: String,
120    pub rounded_total: Decimal,
121}
122
123/// A ticket's already-recognised state, as the recognise short-circuit reads it.
124pub struct PaidStateRow {
125    pub status: String,
126    pub billing_invoice_id: Option<Uuid>,
127    pub payment_entry_id: Option<Uuid>,
128}
129
130/// Everything recognition needs about a draft ticket + its register, in one read: the money to settle,
131/// the accounts to post to, and the stock dimensions to relieve.
132pub struct RecognitionRow {
133    pub customer_id: Option<Uuid>,
134    pub branch_id: Option<Uuid>,
135    pub posting_at: chrono::DateTime<chrono::Utc>,
136    pub rounded_total: Decimal,
137    pub grand_total: Decimal,
138    pub tax_total: Decimal,
139    pub paid_total: Decimal,
140    pub billing_invoice_id: Option<Uuid>,
141    pub receivable_account_id: Option<Uuid>,
142    pub income_account_id: Option<Uuid>,
143    pub cash_account_id: Option<Uuid>,
144    pub tax_account_id: Option<Uuid>,
145    pub tax_rate: Decimal,
146    pub warehouse_id: Option<Uuid>,
147    pub cogs_account_id: Option<Uuid>,
148    pub inventory_account_id: Option<Uuid>,
149    pub currency: String,
150    pub default_customer_id: Option<Uuid>,
151}
152
153/// The original ticket as the returns path reads it — the source of every field the return ticket
154/// copies, plus the tender/invoice refs to reverse.
155pub struct ReturnSourceRow {
156    pub pos_profile_id: Uuid,
157    pub opening_entry_id: Uuid,
158    pub branch_id: Option<Uuid>,
159    pub customer_id: Option<Uuid>,
160    pub billing_invoice_id: Option<Uuid>,
161    pub payment_entry_id: Option<Uuid>,
162    pub rounded_total: Decimal,
163    pub net_total: Decimal,
164    pub tax_total: Decimal,
165    pub grand_total: Decimal,
166    pub posting_at: chrono::DateTime<chrono::Utc>,
167    pub status: String,
168}
169
170/// A ticket header as the printed receipt reads it (register name/currency joined in). `tax_rate` is
171/// the EFFECTIVE rate derived from the ticket's own totals — the register's flat `tax_rate` column is
172/// retired from the compute path, so the slip reports what the document-grade compute actually
173/// produced. `is_invoiced` derives from the billing link (never a stored state column).
174pub struct ReceiptHeaderRow {
175    pub receipt_number: String,
176    pub posting_at: chrono::DateTime<chrono::Utc>,
177    pub net_total: Decimal,
178    pub tax_total: Decimal,
179    pub rounding_adjustment: Decimal,
180    pub rounded_total: Decimal,
181    pub paid_total: Decimal,
182    pub change_due: Decimal,
183    pub status: String,
184    pub register_name: String,
185    pub currency: String,
186    pub tax_rate: Decimal,
187    pub is_invoiced: bool,
188}
189
190/// Hand-written counter-ticket SQL. Lives here (not in the write service) per the module's 4-layer
191/// rule: services orchestrate and own the unit of work, repositories hold the SQL.
192impl PosInvoiceRepository {
193    /// Insert a rung ticket in `draft`.
194    ///
195    /// Takes the CALLER'S connection so the header and its lines commit as one unit. The caller has
196    /// already relayed the ambient org scope onto it — don't re-bind here.
197    ///
198    /// Returns the raw `sqlx::Error` deliberately: the caller inspects it for a unique violation to turn
199    /// a re-used receipt number (or a re-used sync `client_uuid`) into a domain error.
200    pub async fn insert_draft(
201        &self,
202        conn: &mut sqlx::PgConnection,
203        i: &NewDraftInvoiceRow<'_>,
204    ) -> Result<(), sqlx::Error> {
205        sqlx::query(
206            r#"INSERT INTO pos.pos_invoices
207                (id, client_uuid, pos_profile_id, opening_entry_id, branch_id, customer_id, pos_table_id, receipt_number,
208                 posting_at, net_total, tax_total, grand_total, rounding_adjustment, rounded_total,
209                 paid_total, change_due, is_return, status)
210               VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,0,0,false,'draft'::pos_invoice_status)"#,
211        )
212        .bind(i.id).bind(i.client_uuid).bind(i.pos_profile_id).bind(i.opening_entry_id).bind(i.branch_id)
213        .bind(i.customer_id).bind(i.pos_table_id).bind(i.receipt_number).bind(i.posting_at).bind(i.net_total).bind(i.tax_total)
214        .bind(i.grand_total).bind(i.rounding_adjustment).bind(i.rounded_total)
215        .execute(conn)
216        .await?;
217        Ok(())
218    }
219
220    /// Find the live ticket carrying an offline client uuid. The uuid only namespaces inside the
221    /// caller's scope: the ambient org scope's fence is the whole guard — another unit's ticket
222    /// carrying the same uuid is simply not found.
223    pub async fn find_by_client_uuid(
224        &self,
225        pool: &PgPool,
226        client_uuid: Uuid,
227    ) -> Result<Option<SyncLookupRow>, sqlx::Error> {
228        let row = org_scope::fetch_optional_row_scoped(
229            pool,
230            sqlx::query(
231                r#"SELECT id, status::text AS st, pos_profile_id, opening_entry_id, branch_id, customer_id,
232                          pos_table_id, is_return, return_against, net_total, tax_total, grand_total, rounding_adjustment,
233                          rounded_total, paid_total, change_due
234                   FROM pos.pos_invoices WHERE client_uuid=$1 AND (metadata->>'deleted_at') IS NULL"#,
235            )
236            .bind(client_uuid),
237        )
238        .await?;
239        Ok(row.map(|r| SyncLookupRow {
240            id: r.get("id"), status: r.get("st"), pos_profile_id: r.get("pos_profile_id"),
241            opening_entry_id: r.get("opening_entry_id"), branch_id: r.get("branch_id"),
242            customer_id: r.get("customer_id"), pos_table_id: r.get("pos_table_id"), is_return: r.get("is_return"),
243            return_against: r.get("return_against"), net_total: r.get("net_total"),
244            tax_total: r.get("tax_total"), grand_total: r.get("grand_total"),
245            rounding_adjustment: r.get("rounding_adjustment"), rounded_total: r.get("rounded_total"),
246            paid_total: r.get("paid_total"), change_due: r.get("change_due"),
247        }))
248    }
249
250    /// Rewrite a DRAFT ticket's header for an offline replay: re-point it at the (possibly rescued)
251    /// session, re-seat it at the replay's table (a changed `pos_table_id` is a table TRANSFER), and
252    /// replace its server-derived money + partner with the replay's re-derived values.
253    /// State-guarded on `draft` — a finalized ticket is never rewritten (the replay short-circuits
254    /// before this). Takes the CALLER'S connection; the caller has already relayed the ambient scope.
255    /// Returns the raw `sqlx::Error` so the caller can map the one-draft-per-table partial unique.
256    pub async fn update_draft_from_sync(
257        &self,
258        conn: &mut sqlx::PgConnection,
259        pos_invoice_id: Uuid,
260        opening_entry_id: Uuid,
261        branch_id: Option<Uuid>,
262        customer_id: Option<Uuid>,
263        pos_table_id: Option<Uuid>,
264        net_total: Decimal,
265        tax_total: Decimal,
266        grand_total: Decimal,
267        rounding_adjustment: Decimal,
268        rounded_total: Decimal,
269        paid_total: Decimal,
270        change_due: Decimal,
271    ) -> Result<(), sqlx::Error> {
272        sqlx::query(
273            r#"UPDATE pos.pos_invoices SET
274                 opening_entry_id=$2, branch_id=$3, customer_id=$4, pos_table_id=$5,
275                 net_total=$6, tax_total=$7, grand_total=$8, rounding_adjustment=$9,
276                 rounded_total=$10, paid_total=$11, change_due=$12
277               WHERE id=$1 AND status='draft'::pos_invoice_status"#,
278        )
279        .bind(pos_invoice_id).bind(opening_entry_id).bind(branch_id).bind(customer_id).bind(pos_table_id)
280        .bind(net_total).bind(tax_total).bind(grand_total).bind(rounding_adjustment)
281        .bind(rounded_total).bind(paid_total).bind(change_due)
282        .execute(conn)
283        .await?;
284        Ok(())
285    }
286
287    /// The draft ticket currently seated at a dining table — the friendly half of the
288    /// one-draft-per-table guard (the DB partial unique `idx_pos_invoices_pos_table_id` is the
289    /// backstop; this read turns the collision into a typed refusal naming the occupying ticket).
290    /// `exclude_invoice_id` skips the ticket being re-seated (a replay that keeps its own table is
291    /// not a collision). `Ok(None)` = the table is free. Takes the CALLER'S scope-relayed
292    /// connection — the fence is the whole guard.
293    pub async fn find_draft_on_table(
294        &self,
295        conn: &mut sqlx::PgConnection,
296        pos_table_id: Uuid,
297        exclude_invoice_id: Option<Uuid>,
298    ) -> Result<Option<Uuid>, sqlx::Error> {
299        let row = sqlx::query(
300            r#"SELECT id FROM pos.pos_invoices
301               WHERE pos_table_id=$1 AND status='draft'::pos_invoice_status
302                 AND (metadata->>'deleted_at') IS NULL AND ($2::uuid IS NULL OR id <> $2)
303               LIMIT 1"#,
304        )
305        .bind(pos_table_id)
306        .bind(exclude_invoice_id)
307        .fetch_optional(conn)
308        .await?;
309        Ok(row.map(|r| r.get("id")))
310    }
311
312    /// The pool twin of [`Self::find_draft_on_table`] for the duplicate-mapping enrichment: after a
313    /// failed insert the caller's transaction is ABORTED, so the occupying ticket must be read on a
314    /// fresh connection. Rides the ambient org scope (ADR-0029) — under a composed fence the read
315    /// stays inside the acting unit, and a fence-blind read would enrich the 409 with a nil id.
316    pub async fn find_draft_on_table_scoped(
317        &self,
318        pool: &PgPool,
319        pos_table_id: Uuid,
320        exclude_invoice_id: Option<Uuid>,
321    ) -> Result<Option<Uuid>, sqlx::Error> {
322        let row = org_scope::fetch_optional_row_scoped(
323            pool,
324            sqlx::query(
325                r#"SELECT id FROM pos.pos_invoices
326                   WHERE pos_table_id=$1 AND status='draft'::pos_invoice_status
327                     AND (metadata->>'deleted_at') IS NULL AND ($2::uuid IS NULL OR id <> $2)
328                   LIMIT 1"#,
329            )
330            .bind(pos_table_id)
331            .bind(exclude_invoice_id),
332        )
333        .await?;
334        Ok(row.map(|r| r.get("id")))
335    }
336
337    /// Soft-delete a DRAFT ticket's lines so an offline replay can rewrite the basket (full-snapshot
338    /// semantics: the replay carries the complete ticket). Soft-delete keeps the audit trail and frees
339    /// the partial-unique on `client_uuid`. Takes the CALLER'S connection.
340    pub async fn soft_delete_lines_for_ticket(
341        &self,
342        conn: &mut sqlx::PgConnection,
343        pos_invoice_id: Uuid,
344        now: chrono::DateTime<chrono::Utc>,
345    ) -> Result<(), sqlx::Error> {
346        sqlx::query(
347            r#"UPDATE pos.pos_invoice_items SET metadata = jsonb_set(metadata, '{deleted_at}', to_jsonb($2::timestamptz))
348               WHERE pos_invoice_id=$1 AND (metadata->>'deleted_at') IS NULL"#,
349        )
350        .bind(pos_invoice_id).bind(now)
351        .execute(conn)
352        .await?;
353        Ok(())
354    }
355
356    /// Count a session's DRAFT tickets — the close guard. ID-only: rides the ambient org scope.
357    pub async fn count_draft_orders_for_session(
358        &self,
359        pool: &PgPool,
360        opening_entry_id: Uuid,
361    ) -> Result<i64, sqlx::Error> {
362        fetch_one_scalar_scoped(
363            pool,
364            sqlx::query_scalar(
365                "SELECT COUNT(*) FROM pos.pos_invoices WHERE opening_entry_id=$1 AND status='draft'::pos_invoice_status AND is_return=false AND (metadata->>'deleted_at') IS NULL",
366            )
367            .bind(opening_entry_id),
368        )
369        .await
370    }
371
372    /// Count a session's half-recognised tickets — billing linked but the draft→paid flip never
373    /// landed (a settle failed after the invoice was raised). The close guard refuses on these: they
374    /// need recognition retried, not skipped. ID-only scope as above.
375    pub async fn count_linked_unposted_for_session(
376        &self,
377        pool: &PgPool,
378        opening_entry_id: Uuid,
379    ) -> Result<i64, sqlx::Error> {
380        fetch_one_scalar_scoped(
381            pool,
382            sqlx::query_scalar(
383                "SELECT COUNT(*) FROM pos.pos_invoices WHERE opening_entry_id=$1 AND status='draft'::pos_invoice_status AND billing_invoice_id IS NOT NULL AND (metadata->>'deleted_at') IS NULL",
384            )
385            .bind(opening_entry_id),
386        )
387        .await
388    }
389
390    /// Read the header `add_tender` gates on.
391    ///
392    /// ID-only: rides the ambient org scope's request-dedicated connection, so a ticket the scope
393    /// cannot see simply is not found. The caller runs this on its own scope-relayed transaction
394    /// (`fetch_tender_header_on` shape inlined: same statement, caller's connection).
395    pub async fn fetch_tender_header(
396        &self,
397        conn: &mut sqlx::PgConnection,
398        pos_invoice_id: Uuid,
399    ) -> Result<Option<TenderHeaderRow>, sqlx::Error> {
400        let row = sqlx::query(
401            "SELECT status::text AS st, rounded_total FROM pos.pos_invoices WHERE id=$1 AND (metadata->>'deleted_at') IS NULL",
402        )
403        .bind(pos_invoice_id)
404        .fetch_optional(conn)
405        .await?;
406        Ok(row.map(|r| TenderHeaderRow {
407            status: r.get("st"), rounded_total: r.get("rounded_total"),
408        }))
409    }
410
411    /// Write back a ticket's recomputed tender totals. Takes the CALLER'S connection so this and the
412    /// tender insert commit as one unit; the caller has already relayed the ambient scope on it.
413    pub async fn update_tender_totals(
414        &self,
415        conn: &mut sqlx::PgConnection,
416        pos_invoice_id: Uuid,
417        paid_total: Decimal,
418        change_due: Decimal,
419    ) -> Result<(), sqlx::Error> {
420        sqlx::query("UPDATE pos.pos_invoices SET paid_total=$2, change_due=$3 WHERE id=$1")
421            .bind(pos_invoice_id).bind(paid_total).bind(change_due)
422            .execute(conn)
423            .await?;
424        Ok(())
425    }
426
427    /// Read a ticket's recognised state, for the recognise short-circuit. Same ID-only scope contract
428    /// as [`Self::fetch_tender_header`].
429    pub async fn fetch_paid_state(
430        &self,
431        pool: &PgPool,
432        pos_invoice_id: Uuid,
433    ) -> Result<Option<PaidStateRow>, sqlx::Error> {
434        let row = org_scope::fetch_optional_row_scoped(
435            pool,
436            sqlx::query(
437                "SELECT status::text AS st, billing_invoice_id, payment_entry_id FROM pos.pos_invoices WHERE id=$1 AND (metadata->>'deleted_at') IS NULL",
438            )
439            .bind(pos_invoice_id),
440        )
441        .await?;
442        Ok(row.map(|r| PaidStateRow {
443            status: r.get("st"), billing_invoice_id: r.get("billing_invoice_id"),
444            payment_entry_id: r.get("payment_entry_id"),
445        }))
446    }
447
448    /// Read a DRAFT ticket joined to its register — everything recognition needs to drive billing,
449    /// payment, and the optional stock issue. `Ok(None)` = absent, already recognised, or outside the
450    /// caller's scope.
451    ///
452    /// ID-only: rides the ambient org scope. Under HTTP the request-dedicated connection carries the
453    /// scope. When driven by an EVENT (the recognition sink subscribing to `PosTenderCompleted`), the
454    /// caller MUST wrap this in the composing service's org request scope — bound from the event's
455    /// `company_id` twin (the acting unit) — otherwise this read fails closed.
456    pub async fn fetch_for_recognition(
457        &self,
458        pool: &PgPool,
459        pos_invoice_id: Uuid,
460    ) -> Result<Option<RecognitionRow>, sqlx::Error> {
461        let row = org_scope::fetch_optional_row_scoped(
462            pool,
463            sqlx::query(
464                r#"SELECT i.customer_id, i.pos_profile_id, i.branch_id, i.posting_at, i.rounded_total, i.grand_total,
465                          i.tax_total, i.paid_total, i.billing_invoice_id,
466                          p.receivable_account_id, p.income_account_id, p.cash_account_id, p.tax_account_id,
467                          ROUND(COALESCE(i.tax_total / NULLIF(i.net_total, 0), 0), 6) AS tax_rate,
468                          p.warehouse_id, p.cogs_account_id, p.inventory_account_id,
469                          p.currency, p.default_customer_id
470                   FROM pos.pos_invoices i JOIN pos.pos_profiles p ON p.id = i.pos_profile_id
471                   WHERE i.id=$1 AND i.status='draft'::pos_invoice_status AND (i.metadata->>'deleted_at') IS NULL"#,
472            )
473            .bind(pos_invoice_id),
474        )
475        .await?;
476        Ok(row.map(|r| RecognitionRow {
477            customer_id: r.get("customer_id"), branch_id: r.get("branch_id"),
478            posting_at: r.get("posting_at"), rounded_total: r.get("rounded_total"), grand_total: r.get("grand_total"),
479            tax_total: r.get("tax_total"), paid_total: r.get("paid_total"),
480            billing_invoice_id: r.get("billing_invoice_id"),
481            receivable_account_id: r.get("receivable_account_id"), income_account_id: r.get("income_account_id"),
482            cash_account_id: r.get("cash_account_id"), tax_account_id: r.get("tax_account_id"),
483            tax_rate: r.get("tax_rate"), warehouse_id: r.get("warehouse_id"), cogs_account_id: r.get("cogs_account_id"),
484            inventory_account_id: r.get("inventory_account_id"), currency: r.get("currency"),
485            default_customer_id: r.get("default_customer_id"),
486        }))
487    }
488
489    /// Persist the raised Sales Invoice onto the STILL-DRAFT ticket, before settle.
490    ///
491    /// This is billing's at-most-once gate (council 2026-07-05): writing the link the instant
492    /// `raise_and_post` returns makes a retry after a settle failure (declined card / closed period)
493    /// REUSE the invoice instead of raising a second one — otherwise the counter's ordinary
494    /// decline-then-retry doubles the revenue journal. State-guarded on `draft`. Rides the ambient
495    /// org scope.
496    pub async fn link_billing_invoice(
497        &self,
498        pool: &PgPool,
499        pos_invoice_id: Uuid,
500        billing_invoice_id: Uuid,
501    ) -> Result<(), sqlx::Error> {
502        org_scope::execute_scoped(
503            pool,
504            sqlx::query("UPDATE pos.pos_invoices SET billing_invoice_id=$2 WHERE id=$1 AND status='draft'::pos_invoice_status")
505                .bind(pos_invoice_id).bind(billing_invoice_id),
506        )
507        .await?;
508        Ok(())
509    }
510
511    /// Flip a ticket draft→paid, persisting both downstream refs. Returns the rows affected: the caller
512    /// gates the `PosInvoicePaid` event and the stock issue on this being 1, so each happens exactly
513    /// once even if recognise is retried.
514    ///
515    /// Persisting `payment_entry_id` in the same flip is what lets a later return refund the tender from
516    /// the ticket (no cross-schema lookup) and gives a crash-retry a durable skip-gate (ADR-001
517    /// `settle`-idempotency). Rides the ambient org scope.
518    pub async fn mark_paid(
519        &self,
520        pool: &PgPool,
521        pos_invoice_id: Uuid,
522        billing_invoice_id: Uuid,
523        payment_entry_id: Uuid,
524    ) -> Result<u64, sqlx::Error> {
525        let res = org_scope::execute_scoped(
526            pool,
527            sqlx::query(
528                "UPDATE pos.pos_invoices SET status='paid'::pos_invoice_status, billing_invoice_id=$2, payment_entry_id=$3 WHERE id=$1 AND status='draft'::pos_invoice_status",
529            )
530            .bind(pos_invoice_id).bind(billing_invoice_id).bind(payment_entry_id),
531        )
532        .await?;
533        Ok(res.rows_affected())
534    }
535
536    /// Read the original ticket the returns path reverses and copies. Same ID-only scope contract as
537    /// [`Self::fetch_tender_header`].
538    pub async fn fetch_return_source(
539        &self,
540        pool: &PgPool,
541        pos_invoice_id: Uuid,
542    ) -> Result<Option<ReturnSourceRow>, sqlx::Error> {
543        let row = org_scope::fetch_optional_row_scoped(
544            pool,
545            sqlx::query(
546                r#"SELECT pos_profile_id, opening_entry_id, branch_id, customer_id, billing_invoice_id,
547                          payment_entry_id, rounded_total, net_total, tax_total, grand_total, posting_at, status::text AS st
548                   FROM pos.pos_invoices WHERE id=$1 AND (metadata->>'deleted_at') IS NULL"#,
549            )
550            .bind(pos_invoice_id),
551        )
552        .await?;
553        Ok(row.map(|r| ReturnSourceRow {
554            pos_profile_id: r.get("pos_profile_id"),
555            opening_entry_id: r.get("opening_entry_id"), branch_id: r.get("branch_id"),
556            customer_id: r.get("customer_id"), billing_invoice_id: r.get("billing_invoice_id"),
557            payment_entry_id: r.get("payment_entry_id"), rounded_total: r.get("rounded_total"),
558            net_total: r.get("net_total"), tax_total: r.get("tax_total"), grand_total: r.get("grand_total"),
559            posting_at: r.get("posting_at"), status: r.get("st"),
560        }))
561    }
562
563    /// Flip the original ticket paid→returned. Returns the rows affected: the caller gates the return
564    /// ticket and the `PosInvoiceReturned` event on this being 1, so a repeat return records at most
565    /// once. Rides the ambient org scope.
566    pub async fn mark_returned(
567        &self,
568        pool: &PgPool,
569        pos_invoice_id: Uuid,
570    ) -> Result<u64, sqlx::Error> {
571        let res = org_scope::execute_scoped(
572            pool,
573            sqlx::query("UPDATE pos.pos_invoices SET status='returned'::pos_invoice_status WHERE id=$1 AND status='paid'::pos_invoice_status")
574                .bind(pos_invoice_id),
575        )
576        .await?;
577        Ok(res.rows_affected())
578    }
579
580    /// Insert the mirror return ticket, linked to the original via `return_against`. Rides the ambient
581    /// org scope.
582    pub async fn insert_return_ticket(
583        &self,
584        pool: &PgPool,
585        r: &NewReturnInvoiceRow,
586    ) -> Result<(), sqlx::Error> {
587        org_scope::execute_scoped(
588            pool,
589            sqlx::query(
590                r#"INSERT INTO pos.pos_invoices
591                    (id, pos_profile_id, opening_entry_id, branch_id, customer_id, receipt_number,
592                     posting_at, net_total, tax_total, grand_total, rounding_adjustment, rounded_total,
593                     paid_total, change_due, billing_invoice_id, is_return, return_against, status)
594                   VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,0,$11,$11,0,$12,true,$13,'returned'::pos_invoice_status)"#,
595            )
596            .bind(r.id).bind(r.pos_profile_id).bind(r.opening_entry_id)
597            .bind(r.branch_id).bind(r.customer_id).bind(&r.receipt_number)
598            .bind(r.posting_at).bind(r.net_total).bind(r.tax_total).bind(r.grand_total).bind(r.rounded_total)
599            .bind(r.billing_invoice_id).bind(r.return_against),
600        )
601        .await?;
602        Ok(())
603    }
604
605    /// Find the return ticket already recorded against an original — the idempotent-replay path when the
606    /// paid→returned flip found nothing to do. Rides the ambient org scope.
607    pub async fn find_return_ticket(
608        &self,
609        pool: &PgPool,
610        original_pos_invoice_id: Uuid,
611    ) -> Result<Option<Uuid>, sqlx::Error> {
612        fetch_optional_scalar_scoped(
613            pool,
614            sqlx::query_scalar(
615                "SELECT id FROM pos.pos_invoices WHERE return_against=$1 AND is_return=true AND (metadata->>'deleted_at') IS NULL LIMIT 1",
616            )
617            .bind(original_pos_invoice_id),
618        )
619        .await
620    }
621
622    /// Sum the cash handed back as change across a session's recognised tickets — it reduces the drawer.
623    /// ID-only: rides the ambient org scope.
624    pub async fn sum_change_due_for_session(
625        &self,
626        pool: &PgPool,
627        opening_entry_id: Uuid,
628    ) -> Result<Decimal, sqlx::Error> {
629        fetch_one_scalar_scoped(
630            pool,
631            sqlx::query_scalar(
632                "SELECT COALESCE(SUM(change_due),0) FROM pos.pos_invoices WHERE opening_entry_id=$1 AND status='paid'::pos_invoice_status AND (metadata->>'deleted_at') IS NULL",
633            )
634            .bind(opening_entry_id),
635        )
636        .await
637    }
638
639    /// A session's recognised turnover + paid-ticket count, for the X/Z report. ID-only: rides the
640    /// ambient org scope.
641    pub async fn session_totals(
642        &self,
643        pool: &PgPool,
644        opening_entry_id: Uuid,
645    ) -> Result<(Decimal, i64), sqlx::Error> {
646        let row = fetch_one_row_scoped(
647            pool,
648            sqlx::query(
649                "SELECT COALESCE(SUM(rounded_total),0), COUNT(*) FROM pos.pos_invoices WHERE opening_entry_id=$1 AND status='paid'::pos_invoice_status AND (metadata->>'deleted_at') IS NULL",
650            )
651            .bind(opening_entry_id),
652        )
653        .await?;
654        Ok((row.get(0), row.get(1)))
655    }
656
657    /// Read a ticket header joined to its register for the printed receipt. `Ok(None)` = no such
658    /// ticket in the caller's scope. Rides the ambient org scope — the fence is the whole guard.
659    pub async fn fetch_receipt_header(
660        &self,
661        pool: &PgPool,
662        pos_invoice_id: Uuid,
663    ) -> Result<Option<ReceiptHeaderRow>, sqlx::Error> {
664        let row = org_scope::fetch_optional_row_scoped(
665            pool,
666            sqlx::query(
667                r#"SELECT i.receipt_number, i.posting_at, i.net_total, i.tax_total, i.rounding_adjustment,
668                          i.rounded_total, i.paid_total, i.change_due, i.status::text AS status,
669                          p.name AS register_name, p.currency,
670                          ROUND(COALESCE(i.tax_total / NULLIF(i.net_total, 0), 0), 6) AS tax_rate,
671                          (i.billing_invoice_id IS NOT NULL) AS is_invoiced
672                   FROM pos.pos_invoices i JOIN pos.pos_profiles p ON p.id = i.pos_profile_id
673                   WHERE i.id=$1 AND (i.metadata->>'deleted_at') IS NULL"#,
674            )
675            .bind(pos_invoice_id),
676        )
677        .await?;
678        Ok(row.map(|r| ReceiptHeaderRow {
679            receipt_number: r.get("receipt_number"), posting_at: r.get("posting_at"),
680            net_total: r.get("net_total"), tax_total: r.get("tax_total"),
681            rounding_adjustment: r.get("rounding_adjustment"), rounded_total: r.get("rounded_total"),
682            paid_total: r.get("paid_total"), change_due: r.get("change_due"), status: r.get("status"),
683            register_name: r.get("register_name"), currency: r.get("currency"), tax_rate: r.get("tax_rate"),
684            is_invoiced: r.get("is_invoiced"),
685        }))
686    }
687}
688
689backbone_core::impl_crud_repository!(PosInvoiceRepository, PosInvoice, soft_delete);
backbone_pos/infrastructure/persistence/pos_invoice_repository.rs

backbone_pos/infrastructure/persistence/
pos_invoice_repository.rs
