backbone-banking 0.7.1

//! Repository for BankTransaction entities
//!
//! Originally generated by metaphor-schema; now **user-owned** — this exact path is declared under
//! `user_owned` in `metaphor.codegen.yaml`, so the generator skips it wholesale. The custom methods
//! below hold the hand-written BankTransaction SQL: the statement lines, the clearing engine's
//! line+account reads, the allocation watermark, and the reconciliation close-gate's exception count
//! (4-layer rule: services orchestrate, repos hold SQL).
//!
//! Thin newtype over `backbone_orm::GenericCrudRepository<BankTransaction, backbone_orm::SoftDelete>`.
//! All standard CRUD methods are available via `Deref`.

use anyhow::Result;
use rust_decimal::Decimal;
use sqlx::{PgPool, Row};
use uuid::Uuid;

use backbone_orm::org_scope;

use crate::domain::entity::{BankTransaction, TxnStatus};

/// Table name for BankTransaction entities
pub const TABLE_NAME: &str = "banking.bank_transactions";

/// Repository for BankTransaction entities.
///
/// All standard CRUD, soft-delete, pagination, and bulk methods are
/// provided automatically via `Deref` to `backbone_orm::GenericCrudRepository`.
pub struct BankTransactionRepository(
    backbone_orm::GenericCrudRepository<BankTransaction, backbone_orm::SoftDelete>,
);

impl std::ops::Deref for BankTransactionRepository {
    type Target = backbone_orm::GenericCrudRepository<BankTransaction, backbone_orm::SoftDelete>;
    fn deref(&self) -> &Self::Target {
        &self.0
    }
}

impl BankTransactionRepository {
    /// Create a new repository instance.
    pub fn new(pool: PgPool) -> Self {
        Self(backbone_orm::GenericCrudRepository::new(pool, TABLE_NAME))
    }
}

/// The exact row a statement line writes.
///
/// `currency`/`status`/`allocated_amount` are not fields — the INSERT hard-codes `'IDR'`,
/// `'unreconciled'` and `0` (a freshly imported line has cleared nothing). The amounts carry the
/// SERVER-rounded values (2dp half-up).
pub struct NewBankTransactionRow<'a> {
    pub id: Uuid,
    pub bank_account_id: Uuid,
    pub import_id: Uuid,
    pub txn_date: chrono::NaiveDate,
    pub description: Option<&'a str>,
    pub reference_no: Option<&'a str>,
    pub deposit: Decimal,
    pub withdrawal: Decimal,
}

/// The fields the match proposal selects a candidate against.
pub struct MatchBasisRow {
    pub deposit: Decimal,
    pub withdrawal: Decimal,
    pub reference_no: Option<String>,
}

/// A statement line's identity + match signals, as reconcile-preset candidate ordering reads it.
pub struct CandidateLineBasisRow {
    pub bank_account_id: Uuid,
    pub deposit: Decimal,
    pub withdrawal: Decimal,
    pub txn_date: chrono::NaiveDate,
    pub reference_no: Option<String>,
}

/// A statement line joined to its bank account's GL + clearing accounts — everything the clearing
/// engine needs to build the bank-side leg.
pub struct ClearingLineRow {
    pub deposit: Decimal,
    pub withdrawal: Decimal,
    /// How much of this line has already been cleared — the line-level bound.
    pub allocated_amount: Decimal,
    pub currency: String,
    /// The GL account the bank balance lives in.
    pub gl_account_id: Uuid,
    /// The account bank clearing nets to zero against.
    pub clearing_account_id: Uuid,
}

/// A statement line joined to its bank account's GL account — the bank-charge path's narrower read
/// (a charge never touches the clearing account).
pub struct ChargeLineRow {
    pub deposit: Decimal,
    pub withdrawal: Decimal,
    pub allocated_amount: Decimal,
    pub currency: String,
    pub gl_account_id: Uuid,
}

/// Hand-written BankTransaction SQL. Lives here (not in the write service) per the module's 4-layer
/// rule.
impl BankTransactionRepository {
    /// Insert one statement line as `unreconciled`.
    ///
    /// Takes the CALLER'S connection so it commits with its import header. The caller has already
    /// relayed the ambient org scope onto it (`org_scope::bind_org_scope_on`) — don't re-bind here
    /// (ADR-0029).
    pub async fn insert_transaction(
        &self,
        conn: &mut sqlx::PgConnection,
        t: &NewBankTransactionRow<'_>,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            r#"INSERT INTO banking.bank_transactions
                (id, bank_account_id, import_id, txn_date, description, reference_no,
                 deposit, withdrawal, currency, status, allocated_amount)
               VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'IDR','unreconciled'::txn_status,0)"#,
        )
        .bind(t.id)
        .bind(t.bank_account_id)
        .bind(t.import_id)
        .bind(t.txn_date)
        .bind(t.description)
        .bind(t.reference_no)
        .bind(t.deposit)
        .bind(t.withdrawal)
        .execute(conn)
        .await?;
        Ok(())
    }

    /// Read a line's amounts + reference for match proposal. `Ok(None)` = not found.
    ///
    /// ID-only: identified by the line id alone — no tenant argument. This rides the
    /// request-dedicated connection carrying the composing service's org scope, so the
    /// decorator-installed row-level fence decides visibility and another unit's line is simply
    /// not found (ADR-0029).
    pub async fn fetch_match_basis(
        &self,
        pool: &PgPool,
        bank_transaction_id: Uuid,
    ) -> Result<Option<MatchBasisRow>, sqlx::Error> {
        let row = org_scope::fetch_optional_row_scoped(
            pool,
            sqlx::query(
                "SELECT deposit, withdrawal, reference_no FROM banking.bank_transactions WHERE id=$1 AND (metadata->>'deleted_at') IS NULL",
            )
            .bind(bank_transaction_id),
        )
        .await?;
        Ok(row.map(|r| MatchBasisRow {
            deposit: r.get("deposit"),
            withdrawal: r.get("withdrawal"),
            reference_no: r.get("reference_no"),
        }))
    }

    /// Read the line side of reconcile-preset candidate ordering: identity + the match signals
    /// (reference, amount, date). `Ok(None)` = not found. ID-only + scoped read — see
    /// [`Self::fetch_match_basis`].
    pub async fn fetch_candidate_basis(
        &self,
        pool: &PgPool,
        bank_transaction_id: Uuid,
    ) -> Result<Option<crate::infrastructure::persistence::CandidateLineBasisRow>, sqlx::Error>
    {
        let row = org_scope::fetch_optional_row_scoped(
            pool,
            sqlx::query(
                "SELECT bank_account_id, deposit, withdrawal, txn_date, reference_no FROM banking.bank_transactions WHERE id=$1 AND (metadata->>'deleted_at') IS NULL",
            )
            .bind(bank_transaction_id),
        )
        .await?;
        Ok(row.map(
            |r| crate::infrastructure::persistence::CandidateLineBasisRow {
                bank_account_id: r.get("bank_account_id"),
                deposit: r.get("deposit"),
                withdrawal: r.get("withdrawal"),
                txn_date: r.get("txn_date"),
                reference_no: r.get("reference_no"),
            },
        ))
    }

    /// Read a line joined to its account's GL + clearing accounts. `Ok(None)` = not found. ID-only +
    /// scoped read — see [`Self::fetch_match_basis`]. The caller relays the same ambient scope onto
    /// its clearing transaction.
    pub async fn fetch_clearing_line(
        &self,
        pool: &PgPool,
        bank_transaction_id: Uuid,
    ) -> Result<Option<ClearingLineRow>, sqlx::Error> {
        let row = org_scope::fetch_optional_row_scoped(
            pool,
            sqlx::query(
                r#"SELECT t.bank_account_id, t.deposit, t.withdrawal, t.allocated_amount, t.currency,
                          a.gl_account_id, a.clearing_account_id
                   FROM banking.bank_transactions t
                   JOIN banking.bank_accounts a ON a.id = t.bank_account_id
                   WHERE t.id=$1 AND (t.metadata->>'deleted_at') IS NULL"#,
            )
            .bind(bank_transaction_id),
        )
        .await?;
        Ok(row.map(|r| ClearingLineRow {
            deposit: r.get("deposit"),
            withdrawal: r.get("withdrawal"),
            allocated_amount: r.get("allocated_amount"),
            currency: r.get("currency"),
            gl_account_id: r.get("gl_account_id"),
            clearing_account_id: r.get("clearing_account_id"),
        }))
    }

    /// Read a line joined to its account's GL account, for the bank-charge path. `Ok(None)` = not
    /// found. ID-only + scoped read — see [`Self::fetch_match_basis`].
    pub async fn fetch_charge_line(
        &self,
        pool: &PgPool,
        bank_transaction_id: Uuid,
    ) -> Result<Option<ChargeLineRow>, sqlx::Error> {
        let row = org_scope::fetch_optional_row_scoped(
            pool,
            sqlx::query(
                r#"SELECT t.deposit, t.withdrawal, t.allocated_amount, t.currency, a.gl_account_id
                   FROM banking.bank_transactions t JOIN banking.bank_accounts a ON a.id=t.bank_account_id
                   WHERE t.id=$1 AND (t.metadata->>'deleted_at') IS NULL"#,
            )
            .bind(bank_transaction_id),
        )
        .await?;
        Ok(row.map(|r| ChargeLineRow {
            deposit: r.get("deposit"),
            withdrawal: r.get("withdrawal"),
            allocated_amount: r.get("allocated_amount"),
            currency: r.get("currency"),
            gl_account_id: r.get("gl_account_id"),
        }))
    }

    /// Advance a line's allocation watermark + status after a clearance.
    ///
    /// `status` is the typed [`TxnStatus`] enum (ADR-0010 defense-in-depth): binding the enum — not a
    /// `&str` cast at the DB — means an invalid status is a compile-time error at the caller, not a
    /// runtime DB error after the watermark has already moved. Of the four `TxnStatus` variants, only
    /// `Reconciled` and `PartlyReconciled` are valid post-clearance states (`Unreconciled` is the
    /// import default and is advanced away from, never back to; `Ignored` is set by the ignore path,
    /// not the allocation path) — the caller is expected to pick one of those two.
    ///
    /// Takes the CALLER'S connection so it commits with the clearance that justifies it — the
    /// watermark and the clearance row must never disagree. The caller has already relayed the
    /// ambient org scope onto it (`org_scope::bind_org_scope_on`) — don't re-bind here.
    pub async fn set_allocation(
        &self,
        conn: &mut sqlx::PgConnection,
        bank_transaction_id: Uuid,
        allocated_amount: Decimal,
        status: TxnStatus,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE banking.bank_transactions SET allocated_amount=$2, status=$3 WHERE id=$1",
        )
        .bind(bank_transaction_id)
        .bind(allocated_amount)
        .bind(status)
        .execute(conn)
        .await?;
        Ok(())
    }

    /// Count the open lines (`unreconciled`/`partly_reconciled`) for an account in a period — the
    /// reconciliation close-gate's exception count.
    ///
    /// This count IS the assertion behind "the bank agrees with our books", so its scope is
    /// LOAD-BEARING: it rides the request-dedicated connection carrying the composing service's org
    /// scope, so a decorated deployment's row-level fence decides which lines are counted — an
    /// unscoped count under a live fence would read 0 rows and wrongly close a session that still
    /// has open lines in another unit's books. An undecorated deployment is unfenced by design
    /// (ADR-0029) and counts the account's lines plain.
    pub async fn count_open_in_period(
        &self,
        pool: &PgPool,
        bank_account_id: Uuid,
        from_date: chrono::NaiveDate,
        to_date: chrono::NaiveDate,
    ) -> Result<i64, sqlx::Error> {
        let row = org_scope::fetch_optional_row_scoped(
            pool,
            sqlx::query(
                r#"SELECT count(*) AS open_lines FROM banking.bank_transactions
                   WHERE bank_account_id=$1 AND txn_date BETWEEN $2 AND $3
                     AND status IN ('unreconciled'::txn_status,'partly_reconciled'::txn_status)
                     AND (metadata->>'deleted_at') IS NULL"#,
            )
            .bind(bank_account_id)
            .bind(from_date)
            .bind(to_date),
        )
        .await?;
        row.map(|r| r.get::<i64, _>("open_lines"))
            .ok_or(sqlx::Error::RowNotFound)
    }
}

backbone_core::impl_crud_repository!(BankTransactionRepository, BankTransaction, soft_delete);