backbone_inventory/infrastructure/persistence/
valuation_overlay_repository.rs

1//! Valuation-overlay repository (hand-authored, user-owned) — the SQL behind the posting
2//! posture and the landed-cost document family.
3//!
4//! Not schema-derived: this file exists for the same reason `gl_voucher_repository.rs` does —
5//! the reads and writes here cut across the generated per-entity shapes. The settings row is a
6//! one-per-org-unit posture read (not a CRUD list), the landed-cost draft write mints header +
7//! lines as one unit, and the allocation worksheet is a TRANSIENT recompute surface
8//! (delete + recreate, ordered) that must never be written row-by-row by clients.
9//!
10//! Per the module's 4-layer rule the services orchestrate and this file holds the SQL. Every
11//! method takes the CALLER'S connection (or runs `*_scoped` on the pool). Tenancy is
12//! composition-installed (ADR-0029): the composing service's org fence bounds every read and
13//! write, so no statement keys on tenancy.
14
15use rust_decimal::Decimal;
16use sqlx::{PgPool, Row};
17use uuid::Uuid;
18
19/// The valuation posture, as the settings row stores it. An ABSENT row means the
20/// runtime defaults (`average` / `perpetual` / anglo off) — exactly the pre-overlay behavior.
21#[derive(Debug, Clone)]
22pub struct PostureRow {
23    /// `average` | `fifo` | `standard` (only `average` has a costing engine today).
24    pub cost_method: String,
25    /// `perpetual` | `periodic`.
26    pub valuation_policy: String,
27    pub anglo_saxon_accounting: bool,
28    pub stock_interim_delivered_account_id: Option<Uuid>,
29}
30
31/// A landed-cost document header, as the validate/cancel verbs read it.
32#[derive(Debug, Clone)]
33pub struct LcHeaderRow {
34    pub id: Uuid,
35    pub lc_number: String,
36    pub branch_id: Option<Uuid>,
37    pub target_receipt_id: Uuid,
38    pub currency: String,
39    pub posting_date: chrono::NaiveDate,
40    /// `draft` | `done` | `cancel`.
41    pub state: String,
42    /// `not_applicable` | `pending` | `posted` | `failed`.
43    pub posting_state: String,
44    /// The recorded GL settlement ids (a `posted` document's repost short-circuit).
45    pub journal_id: Option<Uuid>,
46    pub accounting_post_id: Option<Uuid>,
47}
48
49/// One landed-cost charge line.
50#[derive(Debug, Clone)]
51pub struct LcLineRow {
52    pub id: Uuid,
53    pub name: String,
54    pub account_id: Uuid,
55    /// `quantity` | `value` | `weight`.
56    pub split_method: String,
57    pub amount: Decimal,
58}
59
60/// The target receipt of a landed cost, as its validation reads it.
61#[derive(Debug, Clone)]
62pub struct LcTargetReceiptRow {
63    pub receipt_number: String,
64    pub warehouse_id: Uuid,
65    /// `draft` | `submitted` | `cancelled`.
66    pub status: String,
67    /// The receipt header's inventory account — the fallback of the landed-cost debit leg's
68    /// account-resolution chain (location override first, this second).
69    pub inventory_account_id: Uuid,
70}
71
72/// One transient allocation-worksheet row, as the recompute writes it and tests read it back.
73#[derive(Debug, Clone)]
74pub struct WorksheetRow {
75    pub move_line_id: Uuid,
76    pub cost_line_id: Uuid,
77    /// This cost line's rounded allocation onto the target line.
78    pub share: Decimal,
79    /// CUMULATIVE landed cost allocated onto this target line across ALL cost lines.
80    pub additional_landed_cost: Decimal,
81    /// The target line's still-on-hand quantity at validate time (read-only FIFO attribution).
82    pub remaining_qty: Decimal,
83}
84
85/// The draft header a `create_landed_cost` mints.
86pub struct NewLandedCostRow<'a> {
87    pub id: Uuid,
88    pub lc_number: &'a str,
89    pub branch_id: Option<Uuid>,
90    pub target_receipt_id: Uuid,
91    pub currency: &'a str,
92    pub posting_date: chrono::NaiveDate,
93    pub notes: Option<&'a str>,
94}
95
96/// One cost line of a draft landed cost.
97pub struct NewLcLineRow<'a> {
98    pub id: Uuid,
99    pub lc_id: Uuid,
100    pub name: &'a str,
101    pub account_id: Uuid,
102    pub split_method: &'a str,
103    pub amount: Decimal,
104}
105
106/// One worksheet row insert. Rows are written ordered by `move_line_id` (the caller sorts) so
107/// the last row is a deterministic rounding-diff recipient.
108pub struct NewWorksheetRow {
109    pub lc_id: Uuid,
110    pub move_line_id: Uuid,
111    pub cost_line_id: Uuid,
112    pub share: Decimal,
113    pub additional_landed_cost: Decimal,
114    pub remaining_qty: Decimal,
115}
116
117/// Hand-owned SQL for the valuation overlay. Stateless (methods take the caller's connection or
118/// run scoped on the pool), mirroring [`super::GlVoucherRepository`].
119pub struct ValuationOverlayRepository;
120
121impl Default for ValuationOverlayRepository {
122    fn default() -> Self { Self::new() }
123}
124
125impl ValuationOverlayRepository {
126    pub fn new() -> Self { Self }
127
128    // ---- posting posture ------------------------------------------------------
129
130    /// Read the caller's org-unit valuation settings row. `None` when the org unit has no row
131    /// — the caller applies the runtime defaults (average / perpetual / anglo off), which
132    /// makes rolling the module out a no-op for tenants that never configure it.
133    pub async fn fetch_posture(
134        &self,
135        pool: &PgPool,
136    ) -> Result<Option<PostureRow>, sqlx::Error> {
137        let row = backbone_orm::org_scope::fetch_optional_row_scoped(
138            pool,
139            sqlx::query(
140                r#"SELECT cost_method::text AS cost_method, valuation_policy::text AS valuation_policy,
141                          anglo_saxon_accounting, stock_interim_delivered_account_id
142                   FROM inventory.inventory_company_settings
143                   WHERE (metadata->>'deleted_at') IS NULL"#,
144            ),
145        )
146        .await?;
147        Ok(row.map(Self::map_posture_row))
148    }
149
150    /// The same posture read on the CALLER'S connection — the variant the move engine uses
151    /// inside its open movement transaction (the ambient org scope is already bound there, so
152    /// the fenced read is correct without opening a second transaction).
153    pub async fn fetch_posture_on(
154        &self,
155        conn: &mut sqlx::PgConnection,
156    ) -> Result<Option<PostureRow>, sqlx::Error> {
157        let row = sqlx::query(
158            r#"SELECT cost_method::text AS cost_method, valuation_policy::text AS valuation_policy,
159                      anglo_saxon_accounting, stock_interim_delivered_account_id
160               FROM inventory.inventory_company_settings
161               WHERE (metadata->>'deleted_at') IS NULL"#,
162        )
163        .fetch_optional(&mut *conn)
164        .await?;
165        Ok(row.map(Self::map_posture_row))
166    }
167
168    fn map_posture_row(r: sqlx::postgres::PgRow) -> PostureRow {
169        PostureRow {
170            cost_method: r.get("cost_method"),
171            valuation_policy: r.get("valuation_policy"),
172            anglo_saxon_accounting: r.get("anglo_saxon_accounting"),
173            stock_interim_delivered_account_id: r.get("stock_interim_delivered_account_id"),
174        }
175    }
176
177    /// A location's valuation-account override, when set. `None` = no override (the caller
178    /// falls through to the door-header account).
179    ///
180    /// The read runs org-scoped: locations are shared masters under the composition's
181    /// root-shared fence, so a scoped read resolves the override for tenant-specific and
182    /// shared locations alike.
183    pub async fn fetch_location_valuation_override(
184        &self,
185        pool: &PgPool,
186        location_id: Uuid,
187    ) -> Result<Option<Uuid>, sqlx::Error> {
188        let row = backbone_orm::org_scope::fetch_optional_row_scoped(
189            pool,
190            sqlx::query(
191                r#"SELECT valuation_account_id FROM inventory.locations
192                   WHERE id=$1 AND (metadata->>'deleted_at') IS NULL"#,
193            )
194            .bind(location_id),
195        )
196        .await?;
197        Ok(row.and_then(|r| r.get("valuation_account_id")))
198    }
199
200    /// The per-unit shipping weight of one stock item (0 = unweighted; the weight basis of a
201    /// landed-cost split — an all-zero weight basis is the loud zero-denominator case).
202    pub async fn fetch_item_weight(
203        &self,
204        conn: &mut sqlx::PgConnection,
205        item_id: Uuid,
206    ) -> Result<Decimal, sqlx::Error> {
207        let row: Option<Decimal> = sqlx::query_scalar(
208            r#"SELECT weight_per_unit FROM inventory.stock_items
209               WHERE item_id=$1 AND (metadata->>'deleted_at') IS NULL"#,
210        )
211        .bind(item_id)
212        .fetch_optional(conn)
213        .await?;
214        Ok(row.unwrap_or(Decimal::ZERO))
215    }
216
217    // ---- landed-cost document -------------------------------------------------
218
219    /// Read one landed-cost header.
220    pub async fn fetch_lc_header(
221        &self,
222        conn: &mut sqlx::PgConnection,
223        lc_id: Uuid,
224    ) -> Result<Option<LcHeaderRow>, sqlx::Error> {
225        let row = sqlx::query(
226            r#"SELECT id, lc_number, branch_id, target_receipt_id, currency,
227                      posting_date, state::text AS state, posting_state::text AS posting_state,
228                      journal_id, accounting_post_id
229               FROM inventory.landed_costs
230               WHERE id=$1 AND (metadata->>'deleted_at') IS NULL"#,
231        )
232        .bind(lc_id)
233        .fetch_optional(&mut *conn)
234        .await?;
235        Ok(row.map(|r| LcHeaderRow {
236            id: r.get("id"),
237            lc_number: r.get("lc_number"),
238            branch_id: r.get("branch_id"),
239            target_receipt_id: r.get("target_receipt_id"),
240            currency: r.get("currency"),
241            posting_date: r.get("posting_date"),
242            state: r.get("state"),
243            posting_state: r.get("posting_state"),
244            journal_id: r.get("journal_id"),
245            accounting_post_id: r.get("accounting_post_id"),
246        }))
247    }
248
249    /// Read the landed cost's charge lines.
250    pub async fn fetch_lc_lines(
251        &self,
252        conn: &mut sqlx::PgConnection,
253        lc_id: Uuid,
254    ) -> Result<Vec<LcLineRow>, sqlx::Error> {
255        let rows = sqlx::query(
256            r#"SELECT id, name, account_id, split_method::text AS split_method, amount
257               FROM inventory.landed_cost_lines
258               WHERE lc_id=$1 AND (metadata->>'deleted_at') IS NULL
259               ORDER BY (metadata->>'created_at'), id"#,
260        )
261        .bind(lc_id)
262        .fetch_all(&mut *conn)
263        .await?;
264        Ok(rows
265            .into_iter()
266            .map(|r| LcLineRow {
267                id: r.get("id"),
268                name: r.get("name"),
269                account_id: r.get("account_id"),
270                split_method: r.get("split_method"),
271                amount: r.get("amount"),
272            })
273            .collect())
274    }
275
276    /// Insert a draft landed-cost header. `posting_state` starts `not_applicable`: a draft has
277    /// no GL leg; validation arms `pending` in the same transaction that mints the revaluation
278    /// SLE rows (the move-engine arming pattern).
279    pub async fn insert_landed_cost_draft(
280        &self,
281        conn: &mut sqlx::PgConnection,
282        lc: &NewLandedCostRow<'_>,
283    ) -> Result<(), sqlx::Error> {
284        sqlx::query(
285            r#"INSERT INTO inventory.landed_costs
286                 (id, lc_number, branch_id, target_receipt_id, currency,
287                  posting_date, state, amount_total, posting_state, notes)
288               VALUES ($1,$2,$3,$4,$5,$6,'draft',$7,'not_applicable',$8)"#,
289        )
290        .bind(lc.id)
291        .bind(lc.lc_number)
292        .bind(lc.branch_id)
293        .bind(lc.target_receipt_id)
294        .bind(lc.currency)
295        .bind(lc.posting_date)
296        .bind(Decimal::ZERO)
297        .bind(lc.notes)
298        .execute(&mut *conn)
299        .await?;
300        Ok(())
301    }
302
303    /// Insert one cost line of a draft landed cost.
304    pub async fn insert_lc_line(
305        &self,
306        conn: &mut sqlx::PgConnection,
307        l: &NewLcLineRow<'_>,
308    ) -> Result<(), sqlx::Error> {
309        sqlx::query(
310            r#"INSERT INTO inventory.landed_cost_lines
311                 (id, lc_id, name, account_id, split_method, amount)
312               VALUES ($1,$2,$3,$4,$5::landed_cost_split_method,$6)"#,
313        )
314        .bind(l.id)
315        .bind(l.lc_id)
316        .bind(l.name)
317        .bind(l.account_id)
318        .bind(l.split_method)
319        .bind(l.amount)
320        .execute(&mut *conn)
321        .await?;
322        Ok(())
323    }
324
325    /// The target receipt of a landed cost, as its validation reads it: the receipt number the
326    /// door stamped as `origin` on every line move, the header inventory account (the fallback
327    /// of the debit leg's account-resolution chain), and the status (a landed cost only ever
328    /// targets a receipt whose moves are DONE; the service derives that from the moves
329    /// themselves). Takes the caller's connection.
330    pub async fn fetch_lc_target_receipt(
331        &self,
332        conn: &mut sqlx::PgConnection,
333        receipt_id: Uuid,
334    ) -> Result<Option<LcTargetReceiptRow>, sqlx::Error> {
335        let row = sqlx::query(
336            r#"SELECT receipt_number, warehouse_id, status::text AS st, inventory_account_id
337               FROM inventory.purchase_receipts
338               WHERE id=$1 AND (metadata->>'deleted_at') IS NULL"#,
339        )
340        .bind(receipt_id)
341        .fetch_optional(&mut *conn)
342        .await?;
343        Ok(row.map(|r| LcTargetReceiptRow {
344            receipt_number: r.get("receipt_number"),
345            warehouse_id: r.get("warehouse_id"),
346            status: r.get("st"),
347            inventory_account_id: r.get("inventory_account_id"),
348        }))
349    }
350
351    /// Flip a draft landed cost to `done` and arm its GL leg `pending` — one transaction
352    /// together with the revaluation SLE rows the engine verb minted before this call inside
353    /// the caller's unit of work. `amount_total` is the document's Σ cost amounts.
354    pub async fn mark_lc_validated(
355        &self,
356        conn: &mut sqlx::PgConnection,
357        lc_id: Uuid,
358        amount_total: Decimal,
359    ) -> Result<(), sqlx::Error> {
360        sqlx::query(
361            r#"UPDATE inventory.landed_costs
362               SET state='done', posting_state='pending', amount_total=$2
363               WHERE id=$1 AND state='draft'"#,
364        )
365        .bind(lc_id)
366        .bind(amount_total)
367        .execute(&mut *conn)
368        .await?;
369        Ok(())
370    }
371
372    /// Cancel a DRAFT landed cost (a `done` one can never cancel — the reversal pattern is a
373    /// negative-amount landed cost). A draft posted no GL, so its posting state retires to
374    /// `not_applicable`.
375    pub async fn mark_lc_cancelled(
376        &self,
377        conn: &mut sqlx::PgConnection,
378        lc_id: Uuid,
379    ) -> Result<(), sqlx::Error> {
380        sqlx::query(
381            r#"UPDATE inventory.landed_costs
382               SET state='cancel', posting_state='not_applicable'
383               WHERE id=$1 AND state='draft'"#,
384        )
385        .bind(lc_id)
386        .execute(&mut *conn)
387        .await?;
388        Ok(())
389    }
390
391    // ---- the transient allocation worksheet -----------------------------------
392
393    /// Delete the landed cost's worksheet rows. The worksheet is a RECOMPUTE SURFACE, not a
394    /// ledger: every (re)validation deletes and recreates it wholesale. The only accounting
395    /// identity is the posted journal entry.
396    pub async fn delete_worksheet(
397        &self,
398        conn: &mut sqlx::PgConnection,
399        lc_id: Uuid,
400    ) -> Result<(), sqlx::Error> {
401        sqlx::query("DELETE FROM inventory.landed_cost_adjustment_lines WHERE lc_id=$1")
402            .bind(lc_id)
403            .execute(&mut *conn)
404            .await?;
405        Ok(())
406    }
407
408    /// Insert one worksheet row (the caller writes them ordered by `move_line_id` so the
409    /// last-line-eats-the-rounding-diff recipient is deterministic).
410    pub async fn insert_worksheet_row(
411        &self,
412        conn: &mut sqlx::PgConnection,
413        w: &NewWorksheetRow,
414    ) -> Result<(), sqlx::Error> {
415        sqlx::query(
416            r#"INSERT INTO inventory.landed_cost_adjustment_lines
417                 (id, lc_id, move_line_id, cost_line_id, share,
418                  additional_landed_cost, remaining_qty)
419               VALUES ($1,$2,$3,$4,$5,$6,$7)"#,
420        )
421        .bind(Uuid::new_v4())
422        .bind(w.lc_id)
423        .bind(w.move_line_id)
424        .bind(w.cost_line_id)
425        .bind(w.share)
426        .bind(w.additional_landed_cost)
427        .bind(w.remaining_qty)
428        .execute(&mut *conn)
429        .await?;
430        Ok(())
431    }
432
433    /// Read the landed cost's worksheet back (tests + GL repost reconstruction).
434    pub async fn fetch_worksheet(
435        &self,
436        conn: &mut sqlx::PgConnection,
437        lc_id: Uuid,
438    ) -> Result<Vec<WorksheetRow>, sqlx::Error> {
439        let rows = sqlx::query(
440            r#"SELECT move_line_id, cost_line_id, share, additional_landed_cost, remaining_qty
441               FROM inventory.landed_cost_adjustment_lines
442               WHERE lc_id=$1
443               ORDER BY move_line_id, id"#,
444        )
445        .bind(lc_id)
446        .fetch_all(&mut *conn)
447        .await?;
448        Ok(rows
449            .into_iter()
450            .map(|r| WorksheetRow {
451                move_line_id: r.get("move_line_id"),
452                cost_line_id: r.get("cost_line_id"),
453                share: r.get("share"),
454                additional_landed_cost: r.get("additional_landed_cost"),
455                remaining_qty: r.get("remaining_qty"),
456            })
457            .collect())
458    }
459}
backbone_inventory/infrastructure/persistence/valuation_overlay_repository.rs

backbone_inventory/infrastructure/persistence/
valuation_overlay_repository.rs
