backbone-catalog 0.7.0

//! Repository for Item 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 catalog write service's item SQL (4-layer rule: services orchestrate, repos hold
//! SQL).
//!
//! Tenant-agnostic (ADR-0029): no statement here names a tenancy column. Statements ride
//! whatever executor the caller passes — write verbs pass their org-scoped transaction;
//! request-path reads use the `*_scoped` helpers, which ride the composing host's
//! request-dedicated connection when one is bound and the plain pool otherwise.
//!
//! Thin newtype over `backbone_orm::GenericCrudRepository<Item, backbone_orm::SoftDelete>`.
//! All standard CRUD methods are available via `Deref`.

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

use crate::domain::entity::Item;

/// Table name for Item entities
pub const TABLE_NAME: &str = "catalog.items";

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

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

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

/// A scan resolved to a sellable identity: the item (always) plus the variant if the scanned code
/// matched a variant SKU/barcode rather than the base item. POS rings against `item_id`.
///
/// Returned by [`ItemRepository::find_by_scan_code`] and
/// [`crate::infrastructure::persistence::ItemVariantRepository::find_variant_by_scan_code`].
#[derive(Debug, Clone, serde::Serialize, sqlx::FromRow)]
pub struct ItemHit {
    pub item_id: Uuid,
    pub variant_id: Option<Uuid>,
    pub item_code: String,
    pub name: String,
    pub barcode: Option<String>,
    pub sku: Option<String>,
}

/// The exact row a validated item insert writes.
pub struct NewItemRow<'a> {
    pub id: Uuid,
    pub item_code: &'a str,
    pub name: &'a str,
    pub description: Option<&'a str>,
    pub barcode: Option<&'a str>,
    pub brand_id: Option<Uuid>,
    pub item_group_id: Uuid,
    pub default_uom_id: Uuid,
    pub item_type: &'a str,
    pub is_sales_item: bool,
    pub is_purchase_item: bool,
    pub is_stock_item: bool,
    pub hsn_code: Option<&'a str>,
    pub is_taxable: bool,
    pub weight_per_unit: Option<Decimal>,
    pub standard_cost: Option<Decimal>,
    pub tags: &'a serde_json::Value,
    pub data: &'a serde_json::Value,
}

/// Catalog item SQL. Lives here (not in the service) per the module's 4-layer rule.
impl ItemRepository {
    /// Resolve a scanned code (barcode OR item_code) to the base item. Tenant-agnostic: the
    /// statement runs plain on the caller's pool — under a composed host's org request scope
    /// it rides the request-dedicated scoped connection, so out-of-scope items simply are
    /// not found.
    pub async fn find_by_scan_code(
        &self,
        executor: impl sqlx::Executor<'_, Database = sqlx::Postgres>,
        code: &str,
    ) -> Result<Option<ItemHit>, sqlx::Error> {
        let hit = sqlx::query_as::<_, ItemHit>(
            r#"SELECT id AS item_id, NULL::uuid AS variant_id, item_code, name, barcode, NULL::text AS sku
               FROM catalog.items
               WHERE (barcode = $1 OR item_code = $1)
                 AND (metadata->>'deleted_at') IS NULL
               LIMIT 1"#,
        )
        .bind(code)
        .fetch_optional(executor)
        .await?;
        Ok(hit)
    }

    /// Scan-code lookup for the request path: rides the composing host's request-dedicated
    /// connection when one is bound (so the org fence applies), plain pool otherwise.
    /// Tenant-agnostic — the database fence owns isolation, this picks the lane.
    pub async fn find_by_scan_code_scoped(
        &self,
        pool: &PgPool,
        code: &str,
    ) -> Result<Option<ItemHit>, sqlx::Error> {
        let row = backbone_orm::org_scope::fetch_optional_row_scoped(
            pool,
            sqlx::query(
                r#"SELECT id AS item_id, NULL::uuid AS variant_id, item_code, name, barcode, NULL::text AS sku
                   FROM catalog.items
                   WHERE (barcode = $1 OR item_code = $1)
                     AND (metadata->>'deleted_at') IS NULL
                   LIMIT 1"#,
            )
            .bind(code),
        )
        .await?;
        Ok(row.map(|r| ItemHit {
            item_id: r.get("item_id"),
            variant_id: r.get("variant_id"),
            item_code: r.get("item_code"),
            name: r.get("name"),
            barcode: r.get("barcode"),
            sku: r.get("sku"),
        }))
    }

    /// `EXISTS` probe for a live row (replaces the prior string-built `exists_in` helper in
    /// the write service). Tenant-agnostic: scoped by the caller's connection, never by a
    /// column here.
    pub async fn exists_id(
        &self,
        executor: impl sqlx::Executor<'_, Database = sqlx::Postgres>,
        id: Uuid,
    ) -> Result<bool, sqlx::Error> {
        let found: Option<Uuid> = sqlx::query_scalar(
            "SELECT id FROM catalog.items \
             WHERE id = $1 AND (metadata->>'deleted_at') IS NULL",
        )
        .bind(id)
        .fetch_optional(executor)
        .await?;
        Ok(found.is_some())
    }

    /// Insert a validated item row. Unique-constraint errors propagate as `sqlx::Error` so
    /// the service can disambiguate barcode vs item_code duplicates.
    pub async fn insert_item(
        &self,
        executor: impl sqlx::Executor<'_, Database = sqlx::Postgres>,
        r: &NewItemRow<'_>,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            r#"INSERT INTO catalog.items
                (id, item_code, name, description, barcode, brand_id, item_group_id,
                 default_uom_id, item_type, is_sales_item, is_purchase_item, is_stock_item,
                 hsn_code, is_taxable, weight_per_unit, standard_cost, tags, data, status)
               VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9::item_type,$10,$11,$12,$13,$14,$15,$16,$17,$18,'active'::catalog_status)"#,
        )
        .bind(r.id)
        .bind(r.item_code)
        .bind(r.name)
        .bind(r.description)
        .bind(r.barcode)
        .bind(r.brand_id)
        .bind(r.item_group_id)
        .bind(r.default_uom_id)
        .bind(r.item_type)
        .bind(r.is_sales_item)
        .bind(r.is_purchase_item)
        .bind(r.is_stock_item)
        .bind(r.hsn_code)
        .bind(r.is_taxable)
        .bind(r.weight_per_unit)
        .bind(r.standard_cost)
        .bind(r.tags)
        .bind(r.data)
        .execute(executor)
        .await?;
        Ok(())
    }

    /// Flip `has_variants = TRUE` on the item (in-tx; called from the variant-create tx after the
    /// variant row is inserted).
    pub async fn set_has_variants_true(
        &self,
        conn: &mut PgConnection,
        item_id: Uuid,
    ) -> Result<(), sqlx::Error> {
        sqlx::query("UPDATE catalog.items SET has_variants = TRUE WHERE id = $1")
            .bind(item_id)
            .execute(conn)
            .await?;
        Ok(())
    }

    /// Read an item's current lifecycle status (in-tx). Used by the validated
    /// status-transition path to enforce the CatalogStatus state machine.
    pub async fn find_status(
        &self,
        conn: &mut PgConnection,
        item_id: Uuid,
    ) -> Result<Option<crate::domain::entity::CatalogStatus>, sqlx::Error> {
        Ok(sqlx::query_scalar("SELECT status FROM catalog.items WHERE id = $1")
            .bind(item_id)
            .fetch_optional(conn)
            .await?)
    }

    /// Set an item's lifecycle status (in-tx).
    pub async fn set_status(
        &self,
        conn: &mut PgConnection,
        item_id: Uuid,
        status: crate::domain::entity::CatalogStatus,
    ) -> Result<(), sqlx::Error> {
        sqlx::query("UPDATE catalog.items SET status = $1 WHERE id = $2")
            .bind(status)
            .bind(item_id)
            .execute(conn)
            .await?;
        Ok(())
    }

    /// Flip `has_variants = FALSE` on the item (in-tx; called from the variant-delete tx when no
    /// live variants remain).
    pub async fn set_has_variants_false(
        &self,
        conn: &mut PgConnection,
        item_id: Uuid,
    ) -> Result<(), sqlx::Error> {
        sqlx::query("UPDATE catalog.items SET has_variants = FALSE WHERE id=$1")
            .bind(item_id)
            .execute(conn)
            .await?;
        Ok(())
    }
}

backbone_core::impl_crud_repository!(ItemRepository, Item, soft_delete);