# How Avg Need Works

## Module-level variables

```lua
-- declared (set in productNeed.calculate)
local avgNeedStartDate, avgNeedEndDate, avgNeedDays, defaultAvgNeedTime

-- always true
local addAvgNeed = true

-- avg need row type identifier
local fromAvgNeed = 5
```

## Initialization (productNeed.calculate)

```lua
avgNeedEndDate = dt.currentDate()                            -- NOW
avgNeedDays = param.rec.avg_need_days                         -- from settings, e.g. 90
avgNeedStartDate = dt.daysAdd(avgNeedEndDate, -avgNeedDays)   -- NOW - 90 days (lookback period)
defaultAvgNeedTime = dt.timeParse(param.rec.default_avg_need_time or "12:00:00")
currentDate = dt.currentDate() + defaultAvgNeedTime           -- NOW + noon time
currentDate = currentDate - timeZoneOffset(currentDate)
```

- `avgNeedStartDate` = lookback start (NOW - avgNeedDays) — used to COLLECT sales amounts
- `avgNeedEndDate` = lookback end (NOW) — end of collection window
- `avgNeedDays` = number of days in lookback period
- `currentDate` = calculation start date with time set to defaultAvgNeedTime

## Phase 1: Collect avg need amount (3 places)

Avg need amount = total sales in lookback period `[avgNeedStartDate, avgNeedEndDate)`.

### Place 1: addSourceArray → addOrderRow for sales

When adding sales order rows:

```lua
if from == fromSales and pr.production_type == productionTypeStock
   and date >= avgNeedStartDate and date < avgNeedEndDate then
    pr.avg_need = pr.avg_need or 0 + amount
end
```

Only stock products. Only sales orders with delivery date inside lookback period.

### Place 2: addSourceArray → addOrderRow for delivered sales

Same check for sales orders with delivery state (already delivered):

```lua
if from == fromSales and pr.production_type == productionTypeStock
   and date >= avgNeedStartDate and date < avgNeedEndDate then
    pr.avg_need = pr.avg_need or 0 + amount
end
```

### Place 3: addLowerLevelNeed

When adding lower level need rows (material rows from need down):

```lua
if pr.production_type == productionTypeStock then
    if fromItem.from == fromSales
       and need.date >= avgNeedStartDate and need.date < avgNeedEndDate then
        avgNeed = need.amount
        pr.avg_need = pr.avg_need + avgNeed
    end
end
```

Also stock products only. Also sales-originated need rows only.

### Also: product initialization in addSourceArray

```lua
pr.avg_need = 0  -- reset to 0 before accumulation
```

Every product starts with `avg_need = 0`. All three places above ADD to it.

## Phase 2: Add avg need rows (addAvgNeedActionLimit)

### When it runs

`addAvgNeedActionLimit(calcArray, info, fromNeed)` called with `fromNeed = fromAvgNeed`.

**Guard:**

```lua
if not info.lastLoop and fromNeed ~= fromActionLimit then
    return count, calcArray  -- EXIT EARLY
end
```

Only runs when `info.lastLoop == true`. During the while loop, it returns early and does NOTHING.

Called from `productNeedArray`:

| When | Condition |
|------|-----------|
| Before while loop | only if `criticalDateCalculation` — BUT returns early because `lastLoop` not set |
| During while loop | always called — BUT returns early because `lastLoop` not set |
| After while loop | `info.lastLoop = true` → **RUNS** |
| After while loop (again) | `fromActionLimit` → runs for action limit, not avg need |

**Result:** avg need rows are ONLY added on the LAST loop after the while loop ends.

### Per-product setup

When `useProductIdx = true`, at first row of product array:

```lua
pr = productIdx[to.product_id]
avgNeed = pr.avg_need / avgNeedDays        -- daily rate
avgNeedDate = daysAdd(currentDate, pr.json_data.unfinished_orders_in_weeks * 7)  -- forward start date
```

- `avgNeed` (local) = daily rate = total accumulated sales / lookback days
- `avgNeedDate` (local) = where avg need rows START being placed = NOW + unfinished orders weeks

This `avgNeedDate` is NOT the same as module-level `avgNeedStartDate`. This one is FORWARD-looking.

### Row creation

For each row `to` in the product array:

```lua
if avgNeed > 0 and (to.date >= avgNeedDate or to.last_row and to.balance >= 0) then
    addRowCount = dayDifference(avgNeedDate, to.date)
    if addRowCount == 0 then addRowCount = 1 end
    date = daysAdd(removeTimePart(avgNeedDate) + defaultAvgNeedTime, addRowCount)
    date = date - timeZoneOffset(date)
    addRow(i, avgNeed)                   -- creates row with amount = -(avgNeed * addRowCount)
    avgNeedDate = daysAdd(date, 1)       -- advance past this row's date
end
```

Each avg need row:

- Covers range `[avgNeedDate, date]`
- `amount = -(dailyRate * dayCount)` — always negative
- `from = fromAvgNeed` (5)
- `date` = end of range, with time set to `defaultAvgNeedTime`
- After creation: `avgNeedDate` advances to `date + 1 day`

### Extra row on last row

```lua
if info.lastLoop and to.last_row == true and to.balance >= 0 then
    date = avgNeedDate
    addRowCount = floor(to.balance / avgNeed) + 1
    addRow(i + 1, avgNeed)
end
```

When balance is positive at end, adds extra avg need rows to drive balance negative.

### addRow creates this record

```lua
rec = {
    from_db = to.from_db,
    product_id = to.product_id,
    date = date,           -- end of range with defaultAvgNeedTime
    from = fromNeed,       -- fromAvgNeed = 5
    from_depth = depth,    -- "avg -"
    amount = -amount,      -- negative
    need_down = 0,
    balance = ...,         -- estimated, recalculated later by calculateBalance
    stock_unit = pr.stock_unit,
    from_row_text = l("%d: %.2f * %d days = %.2f", ...)
}
```

### After loop: sort

Sort order: `{"product_id", ">", "date", ">", "from", ">", ...}`.
Since `fromAvgNeed = 5 > fromMaterial = 4`, avg need rows sort AFTER material rows at same date.

## Phase 3: Check/fix avg need in output (checkCalculation)

```lua
if item.last_row then
    item.avg_need = l("%.2f", item.avg_need / avgNeedDays)  -- show daily rate
else
    item.avg_need = l("%.0f / %d = %.2f", item.avg_need, avgNeedDays, item.avg_need / avgNeedDays)
end
```

Converts numeric avg_need to display string.

## Execution flow summary

```text
1. init: pr.avg_need = 0 for all products                    (addSourceArray)
2. collect: add sales amounts to pr.avg_need                  (3 places, lookback period)
3. while loop: addLowerLevelNeed inserts material rows        (may also add to pr.avg_need)
4. while loop: addAvgNeedActionLimit RETURNS EARLY            (lastLoop not set)
5. after while: info.lastLoop = true
6. addAvgNeedActionLimit RUNS                                 (creates avg need rows)
7. addAvgNeedActionLimit for action limit                     (creates action limit rows)
8. calculateBalance                                           (recalculates all balances)
```

## Avg need row identification

- `from = fromAvgNeed` (value 5)
- `amount` is always negative
- `date` has time set to `defaultAvgNeedTime`
- Sorts after material rows at same date (from 5 > from 4)

## Range calculation for each avg need row

For a product with avg need rows in its array:

- First row: range = `[daysAdd(currentDate, pr.json_data.unfinished_orders_in_weeks * 7), row.date]`
- Subsequent rows: range = `[previousRow.date + 1 day, row.date]`
- Day count = `dayDifference(rangeStart, row.date)`
- Amount = `-(pr.avg_need / avgNeedDays * dayCount)`
