apps/backend/src/modules/costing/read-models/costing-read-model.service.tsTypeScript
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '../../../infrastructure/database/database.service';
import {
assertCostingId,
assertCostingScope,
assertCostingText,
} from '../application/costing-input-validation';
@Injectable()
export class CostingReadModelService {
constructor(private readonly database: DatabaseService) {}
async overview(input: { organizationId: string; locationId: string }) {
assertCostingScope(input, 'costing.read_input_invalid');
const summary = await this.database.query<any>(
`select
count(*) filter(where quality_state='stale')::int as stale_count,
count(*) filter(where quality_state='insufficient_data')::int as insufficient_count,
min(gross_margin_percent)::text as lowest_margin_percent,
max(food_cost_percent)::text as highest_food_cost_percent
from product_margin_projections
where organization_id=$1 and location_id=$2`,
[input.organizationId,input.locationId],
);
const alerts = await this.database.query<any>(
`select ca.*,p.name as product_name
from cost_alerts ca
left join products p on p.id=ca.entity_id and ca.entity_type='product'
where ca.organization_id=$1 and ca.location_id=$2 and ca.status='open'
order by case severity when 'critical' then 0 when 'warning' then 1 else 2 end,
calculated_at desc
limit 20`,
[input.organizationId,input.locationId],
);
const products = await this.database.query<any>(
`select
pmp.product_id,p.name as product_name,pmp.channel,
pmp.effective_price_minor,pmp.recipe_cost_minor::text,
pmp.gross_margin_minor::text,pmp.gross_margin_percent::text,
pmp.food_cost_percent::text,pmp.currency_code,pmp.quality_state,
pmp.calculated_at
from product_margin_projections pmp
join products p on p.id=pmp.product_id
where pmp.organization_id=$1 and pmp.location_id=$2
order by pmp.food_cost_percent desc nulls last
limit 50`,
[input.organizationId,input.locationId],
);
return { summary: summary[0] ?? {}, alerts, products };
}
async recipeCost(input: {
organizationId: string;
locationId: string;
recipeId: string;
}) {
assertCostingScope(input, 'costing.read_input_invalid');
assertCostingId(input.recipeId, 'costing.read_input_invalid');
const rows = await this.database.query<any>(
`select rcp.*,rv.version_number
from recipe_cost_projections rcp
join recipe_versions rv on rv.id=rcp.recipe_version_id
where rcp.organization_id=$1
and rcp.location_id=$2
and rcp.recipe_id=$3
order by rv.version_number desc
limit 1`,
[input.organizationId,input.locationId,input.recipeId],
);
return rows[0] ?? null;
}
async recipeHistory(input: {
organizationId: string;
locationId: string;
recipeId: string;
}) {
assertCostingScope(input, 'costing.read_input_invalid');
assertCostingId(input.recipeId, 'costing.read_input_invalid');
return this.database.query<any>(
`select *
from cost_projection_history
where organization_id=$1
and location_id=$2
and entity_type='recipe'
and entity_id=$3
order by recorded_at desc
limit 100`,
[input.organizationId,input.locationId,input.recipeId],
);
}
async productMargin(input: {
organizationId: string;
locationId: string;
productId: string;
channel: string;
}) {
assertCostingScope(input, 'costing.read_input_invalid');
assertCostingId(input.productId, 'costing.read_input_invalid');
assertCostingText(input.channel, 80, 'costing.read_input_invalid');
const rows = await this.database.query<any>(
`select *
from product_margin_projections
where organization_id=$1
and location_id=$2
and product_id=$3
and channel=$4`,
[input.organizationId,input.locationId,input.productId,input.channel],
);
return rows[0] ?? null;
}
async alerts(input: { organizationId: string; locationId: string }) {
assertCostingScope(input, 'costing.read_input_invalid');
return this.database.query<any>(
`select *
from cost_alerts
where organization_id=$1 and location_id=$2 and status='open'
order by calculated_at desc`,
[input.organizationId,input.locationId],
);
}
async supplierImpact(input: {
organizationId: string;
locationId: string;
supplierId: string;
}) {
assertCostingScope(input, 'costing.read_input_invalid');
assertCostingId(input.supplierId, 'costing.read_input_invalid');
return this.database.query<any>(
`select
scip.*,
si.name as stock_item_name,
s.name as supplier_name,
(select cc.currency_code
from costing_configurations cc
where cc.organization_id=scip.organization_id
and cc.is_active=true
and (cc.location_id=scip.location_id or cc.location_id is null)
order by case when cc.location_id=scip.location_id then 0 else 1 end
limit 1) as currency_code
from supplier_cost_impact_projections scip
join stock_items si on si.id=scip.stock_item_id and si.organization_id=scip.organization_id
join suppliers s on s.id=scip.supplier_id and s.organization_id=scip.organization_id
where scip.organization_id=$1
and scip.location_id=$2
and scip.supplier_id=$3
order by abs(coalesce(scip.average_margin_change_percent,0)) desc`,
[input.organizationId,input.locationId,input.supplierId],
);
}
async modifierProfitability(input: {
organizationId: string;
locationId: string;
modifierId: string;
}) {
assertCostingScope(input, 'costing.read_input_invalid');
assertCostingId(input.modifierId, 'costing.read_input_invalid');
const rows = await this.database.query<any>(
`select *
from modifier_cost_projections
where organization_id=$1 and location_id=$2 and modifier_id=$3`,
[input.organizationId,input.locationId,input.modifierId],
);
return rows[0] ?? null;
}
}