apps/backend/src/modules/inventory/application/stock-projection-diagnostic.service.tsTypeScript
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '../../../infrastructure/database/database.service';
@Injectable()
export class StockProjectionDiagnosticService {
constructor(private readonly database: DatabaseService) {}
async check(input: {
organizationId: string;
locationId: string;
}) {
const mismatches = await this.database.query<any>(
`with ledger as (
select
stock_item_id,
storage_location_id,
normalized_unit_code,
sum(normalized_quantity_delta)::numeric(24,8) as ledger_quantity
from stock_movements
where organization_id=$1 and location_id=$2
group by stock_item_id,storage_location_id,normalized_unit_code
),
projection as (
select
stock_item_id,
storage_location_id,
normalized_unit_code,
quantity_normalized::numeric(24,8) as projection_quantity
from stock_balance_projection
where organization_id=$1 and location_id=$2
)
select
coalesce(l.stock_item_id,p.stock_item_id) as stock_item_id,
coalesce(l.storage_location_id,p.storage_location_id) as storage_location_id,
coalesce(l.normalized_unit_code,p.normalized_unit_code) as unit_code,
coalesce(l.ledger_quantity,0)::text as ledger_quantity,
coalesce(p.projection_quantity,0)::text as projection_quantity
from ledger l
full outer join projection p
on p.stock_item_id=l.stock_item_id
and p.storage_location_id=l.storage_location_id
and p.normalized_unit_code=l.normalized_unit_code
where coalesce(l.ledger_quantity,0) <> coalesce(p.projection_quantity,0)`,
[input.organizationId, input.locationId],
);
await this.database.query(
`insert into stock_projection_diagnostics(
organization_id,location_id,mismatch_count,details
) values($1,$2,$3,$4::jsonb)`,
[
input.organizationId,
input.locationId,
mismatches.length,
JSON.stringify(mismatches),
],
);
return {
healthy: mismatches.length === 0,
mismatchCount: mismatches.length,
mismatches,
};
}
}