Files

T

DeepnaPooja dc4b1934e1 Wire store scheme eligibility and update docs

2026-08-21 14:14:14 +05:30

17 KiB

Raw Permalink Blame History

17 — Promotions & Trade Scheme Management

Status: Done — migrations applied to the database Depends On: 01 — Item Catalog (Done), 14 — Pricing & Supplier Master Data (Done), 02 — Stock Ledger (Done)

Goal

Covers mainPlan.md §11 in full — an entirely new subsystem. applyPricingAndDiscounts (§1) and checkStoreSchemeEligibility (§4) both read from it.

Background / Context

No scheme/promotion entity existed before this step. Every reference to "eligible discount" elsewhere in this doc set (order pricing, field invoices, agent discount limits) had no scheme data source — it only enforced the flat per-role discount ceiling from 08_sales_agent_controls.md.

For an enterprise distributor, free-goods/bundle promotions ("buy 10 get 1 free", "buy A + B get C free") are operationally different from discount schemes because they move physical stock at zero revenue, not just reduce a price. That distinction shaped the data model below: BuyXGetY and FreeBundle rules are structured columns/child rows (with their own CRUD), not a JSON blob, so a free-goods rule can be validated (item exists, positive qty, stock available) the same way any other order line is. SlabDiscount/FlatPercent stay JSON in RuleConfig since they're pure percentage-off rules with no inventory or fulfilment side effects.

Data Model Changes

Scheme

Scheme
├── Id (Guid)                  — PK, from Template
├── Name (string)
├── RuleType (enum)            — BuyXGetY, SlabDiscount, FlatPercent, FreeBundle
├── RuleConfig (string?)       — JSON, only used by SlabDiscount/FlatPercent (see below); null
│                                 for BuyXGetY/FreeBundle
├── FK_TriggerItemId (Guid?)   — BuyXGetY only: the item the store must buy
├── TriggerQty (int?)          — BuyXGetY only: qty of TriggerItem required to earn one payout
├── FK_FreeItemId (Guid?)      — shared payout for BuyXGetY and FreeBundle: item given free
├── FreeQty (decimal?)         — shared payout: qty of FreeItem given free
├── FK_RegionId (Guid?)        — nullable, null = all regions
├── EligibleSegment (string?)  — Store.Segment match (Gold/Silver/Key Account/...); null = all segments
├── DateRangeFrom (DateTime)
├── DateRangeTo (DateTime)
├── AgentLimitExempt (bool)    — if true, this scheme's discount stacks on top of the per-role
│                                 discount ceiling (08_sales_agent_controls.md) unconditionally
├── BaselineExpectedSales (decimal?) — manual baseline used by getSchemeROI (see Open Questions)
├── IsActive, CreatedAt        — from Template

RuleConfig JSON shape (SlabDiscount/FlatPercent only):

FlatPercent — { "discountPercent": 10 }
SlabDiscount — { "tiers": [{ "minOrderAmount": 5000, "discountPercent": 5 }, ...] }, the highest tier whose minOrderAmount the order total meets or exceeds wins.

SchemeBundleComponent

SchemeBundleComponent
├── Id (Guid)                  — PK
├── FK_SchemeId (Guid)         — required, cascade delete with parent Scheme
├── FK_ItemId (Guid)           — required
├── Qty (int)                  — required qty of this item for the FreeBundle rule to trigger

Unique constraint on (SchemeId, ItemId). Only meaningful when Scheme.RuleType = FreeBundle — the order must contain every component in at least its listed Qty before the scheme's FK_FreeItemId/FreeQty payout is granted.

SchemeRedemption

SchemeRedemption
├── Id (Guid)                  — PK
├── FK_SchemeId (Guid)         — required
├── FK_OrderId (Guid?)         — nullable (see FieldInvoiceId below)
├── FieldInvoiceId (Guid?)     — placeholder for the future field-invoice module; no FieldInvoice
│                                 entity exists yet, so this is an unconstrained Guid for now
├── DiscountAmount (decimal?)  — set for SlabDiscount/FlatPercent redemptions
├── FK_FreeItemId (Guid?)      — set for BuyXGetY/FreeBundle redemptions
├── FreeQty (decimal?)
├── FreeItemCostValue (decimal?) — free item's Item.CostPrice snapshotted at redemption time, so
│                                   getSchemeROI/trackSchemeSpend stay accurate after CostPrice
│                                   later changes
├── RedeemedAt (DateTime)

At least one of DiscountAmount or FreeQty is set per redemption; Scheme/Order restrict-delete (a scheme or order with redemptions can't be hard-deleted, and soft-delete leaves history intact).

Item (extended)

Item
├── ...
├── CostPrice (decimal?)       — new: cost/purchase price, used to value free-goods redemptions

Migrations

Two migrations:

AddPromotionsSchemes (applied) — creates Schemes, SchemeRedemptions; adds Item.CostPrice.
AddSchemeFreeGoodsRuleAndBundleComponents (applied) — makes Scheme.RuleConfig nullable, adds Scheme.FK_TriggerItemId/TriggerQty/FK_FreeItemId/FreeQty, creates SchemeBundleComponents.

(Split into two because the free-goods columns were promoted from a JSON blob to structured columns/child table after the first migration had already been applied to the dev database.)

API Endpoints

Function reference	Endpoint	Notes
`createScheme(schemeRules, eligibility, dateRange)`	`POST /api/schemes`	`RuleConfig` required only for SlabDiscount/FlatPercent; `TriggerItemId`/`TriggerQty`/`FreeItemId`/`FreeQty`/`BundleComponents` can be supplied inline to fully create a BuyXGetY/FreeBundle scheme in one call
—	`GET /api/schemes`	Filterable by region
—	`GET /api/schemes/{id}`	Includes trigger/free item, bundle components
—	`PUT /api/schemes/{id}`	Updates name/eligibility/date range/`RuleConfig`
—	`DELETE /api/schemes/{id}`	Soft delete
—	`PUT /api/schemes/{id}/free-goods-rule`	Sets the BuyXGetY trigger + shared free-goods payout (also the payout for FreeBundle)
—	`DELETE /api/schemes/{id}/free-goods-rule`	Clears it
—	`PUT /api/schemes/{id}/bundle-components/{itemId}`	Upserts one FreeBundle required-item row
—	`DELETE /api/schemes/{id}/bundle-components/{itemId}`	Removes one FreeBundle required-item row
`evaluateSchemeEligibility(storeId, orderId)`	`GET /api/schemes/eligible?storeId=...&orderId=...`	Returns eligibility for every in-scope scheme, not just one
`applySchemeDiscount(orderId, schemeId)`	`POST /api/orders/{id}/apply-scheme`	Adds a zero-price order line for free-goods payouts
`trackSchemeSpend(schemeId)`	`GET /api/schemes/{id}/spend`	Discount spend + free-goods cost spend
`getSchemeROI(schemeId)`	`GET /api/schemes/{id}/roi`	Uses `BaselineExpectedSales`

Request / Response Shapes

POST `/api/schemes` (BuyXGetY — created fully populated in one call)

{
  "name": "Soap Buy10Get1",
  "ruleType": "BuyXGetY",
  "ruleConfig": null,
  "regionId": null,
  "eligibleSegment": "Gold",
  "dateRangeFrom": "2026-08-01T00:00:00",
  "dateRangeTo": "2026-08-31T23:59:59",
  "agentLimitExempt": false,
  "baselineExpectedSales": 50000,
  "triggerItemId": "<soapItemId>",
  "triggerQty": 10,
  "freeItemId": "<soapItemId>",
  "freeQty": 1
}

triggerItemId/triggerQty/freeItemId/freeQty are optional on create — omit them for a plain SlabDiscount/FlatPercent scheme, or omit and set them later via PUT .../free-goods-rule if you want to create the shell first and configure the payout afterwards.

POST `/api/schemes` (FreeBundle — created fully populated in one call)

{
  "name": "Soap+Shampoo -> Free Toothbrush",
  "ruleType": "FreeBundle",
  "ruleConfig": null,
  "regionId": null,
  "eligibleSegment": null,
  "dateRangeFrom": "2026-08-01T00:00:00",
  "dateRangeTo": "2026-08-31T23:59:59",
  "agentLimitExempt": false,
  "baselineExpectedSales": null,
  "freeItemId": "<toothbrushId>",
  "freeQty": 1,
  "bundleComponents": [ { "itemId": "<soapId>", "qty": 1 }, { "itemId": "<shampooId>", "qty": 1 } ]
}

triggerItemId/triggerQty are ignored for FreeBundle — the required items come from bundleComponents instead.

PUT `/api/schemes/{id}/free-goods-rule`

{ "triggerItemId": "<soapItemId>", "triggerQty": 10, "freeItemId": "<soapItemId>", "freeQty": 1 }

Same shape as the inline create fields — use this to add or change the free-goods payout on a scheme that was created without one.

PUT `/api/schemes/{id}/bundle-components/{itemId}` (FreeBundle)

{ "qty": 1 }

Use this to add/edit one bundle component after creation.

POST `/api/schemes` (SlabDiscount)

{
  "name": "Volume Discount",
  "ruleType": "SlabDiscount",
  "ruleConfig": "{\"tiers\":[{\"minOrderAmount\":5000,\"discountPercent\":5},{\"minOrderAmount\":10000,\"discountPercent\":8}]}",
  "regionId": null,
  "eligibleSegment": null,
  "dateRangeFrom": "2026-08-01T00:00:00",
  "dateRangeTo": "2026-08-31T23:59:59",
  "agentLimitExempt": false,
  "baselineExpectedSales": null
}

POST `/api/orders/{id}/apply-scheme`

{ "schemeId": "..." }

Business Rules

A scheme discount stacks with, but does not bypass, the per-role discount ceiling in 08_sales_agent_controls.md unless AgentLimitExempt = true. (Enforcement against the actual ceiling is deferred — 08_sales_agent_controls.md's discount ceiling isn't implemented in code yet; this is recorded as the target behavior.)
evaluateSchemeEligibility matches a scheme to a store/order only if: the scheme's RegionId is null or matches the store's region; EligibleSegment is null or matches Store.Segment; and the order's date falls within [DateRangeFrom, DateRangeTo].
BuyXGetY eligibility: the order must contain at least TriggerQty of TriggerItem; the payout scales with how many multiples of TriggerQty were bought (FreeQty × floor(boughtQty / TriggerQty)).
FreeBundle eligibility: the order must contain every SchemeBundleComponent in at least its listed Qty; payout is the flat FreeQty of FreeItem (no multiplier).
applySchemeDiscount adds a zero-price OrderItem line for the free-goods payout (UnitPrice = 0, Quantity = FreeQty), rather than only recording a discount amount — the free line still needs to be picked, delivered, and stock-tracked like any other sold line.
A free-goods redemption is blocked if the free item's available StockBalance (summed across all locations) is less than the payout FreeQty — same out-of-stock guard as a normal sold line.
A scheme can only be redeemed once per order (HasRedemptionAsync check in applySchemeDiscount).
SchemeRedemption.FreeItemCostValue snapshots Item.CostPrice at redemption time, so trackSchemeSpend/getSchemeROI stay accurate even after the item's cost price later changes.
Free goods are typically still a taxable "deemed supply" at zero net value under GST/VAT-style regimes — not yet wired into a tax calculation, since no tax subsystem exists in this codebase yet. Flagged for whoever builds tax/invoicing.
trackSchemeSpend = sum(DiscountAmount) + sum(FreeItemCostValue × FreeQty) across all redemptions. getSchemeROI = (actualSales − BaselineExpectedSales) / totalSpend, where actualSales is the sum of Order.TotalAmount for every distinct order that redeemed the scheme.

Open Questions

What counts as "incremental sales" for getSchemeROI — resolved for v1: a manual BaselineExpectedSales entered at scheme creation, not a trailing-period average. Revisit if historical order data volume later makes an automatic trailing average preferable.
Per-role discount ceiling stacking (AgentLimitExempt) is unenforced pending 08_sales_agent_controls.md having an actual ceiling implementation to stack against.
Deemed-supply tax treatment for free goods — deferred until a tax/invoicing subsystem exists.

Implementation Notes

File structure created / touched

Features/
  Scheme/
    SchemeController.cs     (also hosts SchemeOrderController for POST /api/orders/{id}/apply-scheme)
    SchemeDTOs.cs
    SchemeService.cs         (EvaluateRule — the core eligibility/payout calculator; ApplySchemeAsync
                               — the free-goods order-line + stock-check + redemption write path)
    ISchemeRepository.cs
    SchemeRepository.cs

Models/
  Scheme.cs                  (new — includes SchemeRuleType enum)
  SchemeBundleComponent.cs   (new)
  SchemeRedemption.cs        (new)
  Item.cs                    (extended — CostPrice)

Data/AppDBContext.cs
  add Schemes, SchemeRedemptions, SchemeBundleComponents DbSets; relationships (Scheme→TriggerItem/
  FreeItem restrict-delete, SchemeBundleComponent→Scheme cascade-delete, →Item restrict-delete,
  SchemeRedemption→Scheme/Order/FreeItem restrict-delete); unique index on
  SchemeBundleComponent(FK_SchemeId, FK_ItemId)

Migrations/
  AddPromotionsSchemes                                  (applied)
  AddSchemeFreeGoodsRuleAndBundleComponents              (applied)

Program.cs
  register ISchemeRepository/SchemeRepository, SchemeService

Eligibility + payout logic (pseudo)

EvaluateRule(scheme, orderItems):
  switch scheme.RuleType:
    BuyXGetY:
      boughtQty = sum(orderItems where ItemId = scheme.TriggerItemId).Quantity
      if boughtQty < scheme.TriggerQty: not eligible
      multiplier = boughtQty / scheme.TriggerQty  // integer division
      → eligible, FreeItemId = scheme.FreeItemId, FreeQty = scheme.FreeQty * multiplier

    FreeBundle:
      missing = scheme.BundleComponents where sum(orderItems qty for that item) < component.Qty
      if missing.any(): not eligible
      → eligible, FreeItemId = scheme.FreeItemId, FreeQty = scheme.FreeQty

    FlatPercent / SlabDiscount:
      parse RuleConfig JSON, compute DiscountAmount off orderItems total

applySchemeDiscount flow (pseudo)

ApplySchemeAsync(orderId, schemeId):
  reject if scheme already redeemed against this order
  eligibility = EvaluateRule(scheme, order's items)
  reject if not eligible

  if eligibility has a free-goods payout:
    check StockBalance across all locations >= FreeQty, else reject
    add OrderItem { ItemId = FreeItemId, Quantity = FreeQty, UnitPrice = 0 }
    redemption.FreeItemCostValue = freeItem.CostPrice ?? 0

  save SchemeRedemption { DiscountAmount, FreeItemId, FreeQty, FreeItemCostValue }

Worked Example

Item Soap-500ml has CostPrice = 12. Store is in the "Gold" segment.

Create the scheme (one call, fully populated):

POST /api/schemes
{ "name": "Soap Buy10Get1", "ruleType": "BuyXGetY", "ruleConfig": null,
  "regionId": null, "eligibleSegment": "Gold",
  "dateRangeFrom": "2026-08-01", "dateRangeTo": "2026-08-31",
  "agentLimitExempt": false, "baselineExpectedSales": 50000,
  "triggerItemId": "<soapId>", "triggerQty": 10, "freeItemId": "<soapId>", "freeQty": 1 }
→ Scheme { Id, ... }

Store places an order for 20 units of Soap-500ml → Order { Id, TotalAmount }.

Check eligibility:

GET /api/schemes/eligible?storeId=...&orderId=...
→ [{ "schemeId": "...", "eligible": true, "freeItemId": "<soapId>", "freeQty": 2 }]

20 ÷ 10 = 2 multiples → 2 free units.

Apply it:

POST /api/orders/{orderId}/apply-scheme
{ "schemeId": "..." }

Adds an OrderItem line (Soap-500ml, qty 2, UnitPrice = 0) and a SchemeRedemption with FreeQty = 2, FreeItemCostValue = 12.

Spend/ROI:

GET /api/schemes/{id}/spend
→ { "totalDiscountSpend": 0, "totalFreeGoodsCostSpend": 24, "totalSpend": 24, "redemptionCount": 1 }

GET /api/schemes/{id}/roi
→ { "baselineExpectedSales": 50000, "actualSales": 62000, "incrementalSales": 12000,
    "totalSpend": 24, "roi": 500.0 }

FreeBundle variant ("buy Soap + Shampoo, get a free Toothbrush") — also one call:

POST /api/schemes
{ "name": "Soap+Shampoo -> Free Toothbrush", "ruleType": "FreeBundle", "ruleConfig": null,
  "regionId": null, "eligibleSegment": null,
  "dateRangeFrom": "2026-08-01", "dateRangeTo": "2026-08-31",
  "agentLimitExempt": false, "baselineExpectedSales": null,
  "freeItemId": "<toothbrushId>", "freeQty": 1,
  "bundleComponents": [ { "itemId": "<soapId>", "qty": 1 }, { "itemId": "<shampooId>", "qty": 1 } ] }

Eligibility now requires both Soap ≥1 and Shampoo ≥1 in the order; payout is 1 free toothbrush, flat (no multiplier). Components/payout can still be edited afterwards via PUT .../bundle-components/{itemId} and PUT .../free-goods-rule.

17 KiB Raw Permalink Blame History Unescape Escape