# Estimate Need — Complete Documentation

## How it works

Estimate need predicts future product demand based on historical sales. It calculates a daily consumption rate from delivered sales over a lookback period, then inserts negative rows into product balance arrays to represent expected future demand.

### The key principle

Estimate need has its own dedicated query `order_row-sales-estimate-need` that fetches SUM of delivered amounts per product per date over 2+ years. This query uses SQL `GROUP BY product_id, actual_delivery_date` with `SUM(delivery_amount)` — the database returns one row per product-date combination instead of 44K individual delivery rows. Lua code buckets these rows by date range into `consumption`/`growth_consumption` per product. The query loads only when estimate need calculation actually starts — inside `if useEstimateNeed then` in `productNeedArray`. If `useEstimateNeed` is false, zero estimate need code runs. No query. No data. No processing.

### Why estimate need has its own grid, not regular sales rows

`addSourceArray` builds the main calculation arrays (product rows, production orders, sales, purchase). Estimate need data must not be added to these arrays. Estimate need has its own separate grid (`order_row-sales-estimate-need`) that only estimate need code touches.

Estimate need processes its own grid via `loadSourceRow` — reading only the dedicated `order_row-sales-estimate-need` query result, not regular sales rows. This separation ensures estimate need logic is self-contained in `estimate-need.lua`.

## Constants

| Constant | Default | Purpose |
|---|---|---|
| `estimate_need_last_days` | 78 | "last" method trailing window |
| `estimate_period_before_days` | 45 | "period" method: days before anchor |
| `estimate_period_after_days` | 77 | "period" method: days after anchor |
| `growth_compare_days` | 365 | years apart for growth comparison |
| `estimate_need_growth_rate` | 1.0 | manual extra multiplier (layoffs etc) |
| `estimate_need_calculation_method` | "last" | global method: "last" or "period" |
| `estimate_need_calculation_method_by_group` | {} | per product group method override |
| `estimate_need_growth_rate_by_group` | {} | per product group growth rate |
| `estimate_need_add_after_days` | 14 | minimum days before first estimate row |

## Execution flow

```text
productNeedArray():
  1. addSourceArray(param, info)                 → regular rows only (no estimate need code)
  2. sortArray, tree, etc.
  3. if useEstimateNeed then                     → this is where estimate need starts
     estimateNeed.add(...)                       → all logic in one function
     inside add:
        a. setDate                               → calculate date ranges on rec
         b. queryData (SUM by product+date)       → one query covering full extended range
         c. loadSourceRow                         → bucket rows by date into estimateNeedIdx
         d. calcNeedAndInsertRow                  → calc daily_need, set pr.estimate_need, insert rows
  4. calculateBalance                            → works with estimate rows already in place
  5. while needDownArray > 0:
     a. addLowerLevelNeed                        → inserts material rows (splits estimate rows)
     b. calculateBalance                         → correct balance with split estimate need
  6. addActionLimit                              → action limit rows only
  7. calculateBalance                            → final
```

## estimateNeed.add function

All estimate need logic lives in `estimate-need.lua`. Other files only call `estimateNeed.add`. No estimate need code exists in `nc-calc-server-calc.lua` or `addSourceArray`.

`estimateNeed.add` performs these steps in order:

1. Reads `estimate_need_last_days` from constants. If zero or less, returns immediately.
2. Calls `setDate` to calculate date ranges on the `rec` object (including period ranges if applicable).
3. Installs a dedicated aggregate grid with `product_id` as id_field.
4. Builds query parameters with organization_id and the full extended date range (covers growth period too).
5. Calls `queryData` with the SUM aggregate query — database returns one row per (product_id, actual_delivery_date) with summed `delivery_amount`.
6. Calls `loadSourceRow` to bucket rows by date comparison into `estimateNeedIdx`: "period" products accumulate `consumption`/`growth_consumption` based on period date ranges, "last" products accumulate everything into `consumption`. Prints error if product_id not found in `productIdx`.
7. Calls `calcNeedAndInsertRow` — one function that for each product in `calcProductIdx`:
   - Calculates `daily_need` and `growth_rate` from `estimateNeedIdx` values
   - Sets `pr.estimate_need` on the product
   - Inserts negative estimate rows into the product array
8. Records timing stats and returns `days, estimate_need_start_date`.

### Where called: productNeedArray

```text
if useEstimateNeed then
    estimateNeedDays, estimateNeedStartDate = estimateNeed.add(estimateNeedIdx, param, grid, calcLoadData, constant, productIdx, calcProductIdx, info, negativeEstimateNeedError, addWebError, err, currentDate, defaultEstimateNeedTime, fromEstimateNeed)
end
```

One function, one call. All estimate need code is inside `estimate-need.lua`.

## Date calculation: setDate (local in estimate-need.lua)

`setDate` writes seven date fields on the `rec` object that define the time windows for consumption calculation.

Steps it performs:

1. Sets `estimate_need_end_date` = today.
2. Sets `estimate_need_start_date` = today minus `estimate_need_last_days`.
3. If `estimate_need_last_days > 0`:
   - Calculates `period_anchor` = today minus `growth_compare_days` (default 365).
   - Sets `estimate_period_start_date` = anchor minus `estimate_period_before_days`.
   - Sets `estimate_period_end_date` = anchor plus `estimate_period_after_days`.
   - Sets `estimate_growth_start_date` = period_start minus `growth_compare_days`.
   - Sets `estimate_growth_end_date` = period_end minus `growth_compare_days`.
   - If `growth_start_date` is earlier than `estimate_need_start_date`, extends `estimate_need_start_date` to cover the growth period.

### Example: "last" method (default)

```text
constant.estimate_need_last_days = 78
constant.estimate_need_calculation_method = "last"

Today = 2026-06-15

rec.estimate_need_end_date   = 2026-06-15
rec.estimate_need_start_date = 2026-03-29  (78 days back)

window:       [2026-03-29 → 2026-06-15)
daily rate:   consumption / 78
```

### Example: "period" method

```text
constant.estimate_need_last_days = 78
constant.estimate_need_calculation_method = "period"
constant.growth_compare_days = 365
constant.estimate_period_before_days = 45
constant.estimate_period_after_days = 77

Today = 2026-06-15

rec.estimate_need_end_date   = 2026-06-15
rec.estimate_need_start_date = 2026-03-29  (78 days back)
anchor                       = 2025-06-15  (today - 365)
rec.estimate_period_start_date       = 2025-05-01  (anchor - 45)
rec.estimate_period_end_date         = 2025-08-31  (anchor + 77)
rec.estimate_growth_start_date  = 2024-05-01  (period_start - 365)
rec.estimate_growth_end_date    = 2024-08-31  (period_end - 365)

period:       [2025-05-01 → 2025-08-31)
growth:       [2024-05-01 → 2024-08-31)
period_days:  122

growth_rate = consumption / growth_consumption
daily rate = consumption / 122 * growth_rate * estimate_need_growth_rate
```

### Example: period growth extends need_start_date

```text
constant.estimate_need_last_days = 365 (1 year)
constant.growth_compare_days = 365
constant.estimate_period_before_days = 30
constant.estimate_period_after_days = 30

Today = 2026-06-15

rec.estimate_need_end_date   = 2026-06-15
rec.estimate_need_start_date = 2025-06-15  (365 days back)
period_start                 = 2025-05-16  (anchor - 30)
period_end                   = 2025-07-16  (anchor + 30)
growth_start                 = 2024-05-16  (period_start - 365)
growth_end                   = 2024-07-16  (period_end - 365)

growth_start (2024-05-16) < estimate_need_start_date (2025-06-15)?

No — growth is earlier. So estimate_need_start_date is pushed back:
rec.estimate_need_start_date = 2024-05-16

This ensures the query fetches enough rows to cover the growth period too.
```

## Calculation methods

### "last" method (default)

```text
trailing window = [today - estimate_need_last_days, today)
daily rate = consumption / estimate_need_last_days
growth rate = none (always 1.0)
```

Simple average over the full lookback period. One SUM query returns total delivery_amount per product for the entire window.

### "period" method

```text
anchor = today - growth_compare_days
period = [anchor - estimate_period_before_days, anchor + estimate_period_after_days)
growth = same period shifted -growth_compare_days (1 year earlier)

consumption = SUM of rows where actual_delivery_date falls in period range
growth_consumption = SUM of rows where actual_delivery_date falls in growth range
growth_rate = consumption / growth_consumption  (auto-calculated)
daily_need = consumption / (estimate_period_before_days + estimate_period_after_days)
```

Compares current period to same period 1 year earlier. Growth rate captures year-over-year trend. Both values come from a single query — `loadSourceRow` buckets each row into `consumption` or `growth_consumption` by comparing `actual_delivery_date` against the period/growth date ranges.

Why two windows? The estimate_period measures LAST YEAR's consumption in a date range. The estimate_growth measures the SAME date range TWO YEARS AGO. The ratio (period / growth) = year-over-year growth rate. That growth rate predicts next year's demand from this year's consumption.

### Method by product group

```json
{
    "estimate_need_calculation_method_by_group": {
        "ice-cream": "period",
        "metal": "last"
    }
}
```

Each product group can use a different method. Products not in the table use the global method from `estimate_need_calculation_method`.

### Example: mixed methods in same calculation

```text
constant.estimate_need_calculation_method = "last"
constant.estimate_need_calculation_method_by_group = {
    "seasonal": "period",
    "metal": "last"
}

Product A (group "seasonal"):
  uses "period" method → loadSourceRow buckets by period/growth date ranges
  growth_rate auto-calculated from year-over-year comparison

Product B (group "metal"):
  uses "last" method → loadSourceRow accumulates everything into consumption
  growth_rate always 1.0

Product C (group "plastic"):
  not in by_group table → uses global "last"

Product D (no group):
  uses global "last"

All products processed in a single query + single loadSourceRow pass.
```

### Query strategy: one query, Lua bucketing

One query covers the full extended date range `[estimate_need_start_date, estimate_need_end_date)` (extended to include the growth period if any product uses "period" method). The database returns one row per `(product_id, actual_delivery_date)` with `SUM(delivery_amount)`.

`loadSourceRow` iterates these rows and buckets each into the correct field:

- **"last" products**: every row accumulates into `consumption`, regardless of date
- **"period" products**: rows whose `actual_delivery_date` falls in `[estimate_period_start_date, estimate_period_end_date]` accumulate into `consumption`, rows in `[estimate_growth_start_date, estimate_growth_end_date]` accumulate into `growth_consumption`

No method detection needed before the query. No grid save/restore. No extra queries.

## Data loading: loadSourceRow (local in estimate-need.lua)

`loadSourceRow` reads the pre-summed query results (one row per product_id + actual_delivery_date) from the grid and populates `estimateNeedIdx`. For each row, it:

1. Looks up the product in `productIdx` by `product_id`.
2. Prints error if product not found — never silently skips.
3. Determines the calculation method from `pr.product_group`.
4. Creates or updates an entry in `estimateNeedIdx[product_id]`.
5. Buckets the row's `delivery_amount` by comparing `actual_delivery_date` to date ranges:
   - **"last" method**: accumulates into `consumption` (all dates).
   - **"period" method**: if date falls in `[estimate_period_start_date, estimate_period_end_date]` → `consumption`. If date falls in `[estimate_growth_start_date, estimate_growth_end_date]` → `growth_consumption`.

### Example: "last" method data loading

```text
estimate_need_last_days = 730, method = "last"
window = [2024-04-14, 2026-04-14)

SUM query returns 5 rows (grouped by product_id + actual_delivery_date):
  product_id = "A"  actual_delivery_date = 2024-07-15  delivery_amount = 150
  product_id = "A"  actual_delivery_date = 2025-03-10  delivery_amount = 300
  product_id = "B"  actual_delivery_date = 2025-08-22  delivery_amount = 200
  product_id = "C"  actual_delivery_date = 2025-12-01  delivery_amount = 0  (removed by remove_empty_aggregate_row)
  product_id = "C"  actual_delivery_date = 2026-01-15  delivery_amount = 0  (removed by remove_empty_aggregate_row)

loadSourceRow buckets all rows into consumption (last method):
  estimateNeedIdx["A"] = {consumption = 450}
  estimateNeedIdx["B"] = {consumption = 200}

sumConsumption:
  estimateNeedIdx["A"].daily_need = 450 / 730 = 0.616
  estimateNeedIdx["B"].daily_need = 200 / 730 = 0.274
```

### Example: "period" method data loading

```text
estimate_need_last_days = 730, method = "period"
period  = [2025-05-01, 2025-08-31]
growth  = [2024-05-01, 2024-08-31]

Single SUM query covers [2024-05-01, 2026-04-14), returns 4 rows:
  product_id = "A"  actual_delivery_date = 2024-06-15  delivery_amount = 190  → growth_consumption (in growth range)
  product_id = "A"  actual_delivery_date = 2025-01-20  delivery_amount = 110  → ignored (not in period or growth)
  product_id = "A"  actual_delivery_date = 2025-06-10  delivery_amount = 120  → consumption (in period range)
  product_id = "A"  actual_delivery_date = 2025-07-22  delivery_amount = 80   → consumption (in period range)

loadSourceRow buckets by date:
  estimateNeedIdx["A"] = {
      consumption = 200,         (120 + 80, dates in [2025-05-01, 2025-08-31])
      growth_consumption = 190,  (date 2024-06-15 in [2024-05-01, 2024-08-31])
  }

sumConsumption:
  growth_rate = 200 / 190 = 1.053
  daily_need = 200 / 122 = 1.639
```

## calcNeedAndInsertRow (local in estimate-need.lua)

One function does all three per product in a single loop over `calcProductIdx`:

1. Calculates `daily_need` and `growth_rate` from `estimateNeedIdx` values:
   - **"last" method**: `daily_need = consumption / estimateNeedDays`
   - **"period" method**: `growth_rate = consumption / growth_consumption`, `daily_need = consumption / periodDays`
2. Sets `pr.estimate_need = consumption` on the product.
3. If `estimate_need ~= 0`, calculates `dailyRate = pr.estimate_need / estimateNeedDays * autoGrowthRate * extraRate` and inserts negative estimate rows into the product array.

### Example: zero growth consumption

```text
Product A, period method:
  consumption = 500
  growth_consumption = 0  (no sales in growth period)

  growth_rate = 1.0 (default, because growth_consumption is 0)
  daily_need = 500 / periodDays

  When growth_consumption is 0, growth rate stays at default 1.0.
  Division by zero is avoided.
```

### Example: declining demand (growth rate < 1.0)

```text
Product A, period method:
  consumption = 800 (current period)
  growth_consumption = 1000 (growth period, 1 year ago)

  growth_rate = 800 / 1000 = 0.80

  Demand dropped 20%. The daily rate is multiplied by 0.80,
  so future estimate need rows will be smaller than the raw
  period average would suggest.
```

## Growth rates

Two layers of growth rate multiplication in `calcNeedAndInsertRow`:

```text
dailyRate = (pr.estimate_need / estimateNeedDays) * autoGrowthRate * extraRate
```

1. **autoGrowthRate**: From `estimateNeedIdx[productId].growth_rate`. Period method: `consumption / growth_consumption`. Last method: always 1.0.
2. **extraRate**: From `estimateNeed.growthRate(constant, pr)` → `constant.estimate_need_growth_rate_by_group[product_group]` or `constant.estimate_need_growth_rate` or 1.0.

### Example: growth rate by product group

```json
{
    "estimate_need_growth_rate": 1.1,
    "estimate_need_growth_rate_by_group": {
        "ice-cream": 1.25,
        "metal": 0.90
    }
}
```

```text
Product A (group "ice-cream", period method):
  consumption = 1000, growth_consumption = 800
  autoGrowthRate = 1000/800 = 1.25
  extraRate = 1.25 (from group)
  total rate = 1.25 * 1.25 = 1.5625
  dailyRate = (1000 / 122) * 1.5625 = 12.807

Product B (group "metal", last method):
  autoGrowthRate = 1.0
  extraRate = 0.90 (from group)
  total rate = 1.0 * 0.90 = 0.90
  dailyRate = (consumption / 730) * 0.90

Product C (group "plastic", last method):
  autoGrowthRate = 1.0
  extraRate = 1.1 (global, no group override)
  total rate = 1.0 * 1.1 = 1.1
  dailyRate = (consumption / 730) * 1.1

Product D (no group, last method):
  autoGrowthRate = 1.0
  extraRate = 1.1 (global default)
  total rate = 1.1
```

## Row insertion detail (inside calcNeedAndInsertRow)

For each product with `estimate_need > 0`, calculates daily rate and inserts rows into the product array:

```text
dailyRate = pr.estimate_need / estimateNeedDays * autoGrowthRate * extraRate
estimateNeedDate = daysAdd(currentDate, max(addAfterDays, unfinishedOrdersWeeks * 7))
```

Walks forward through product rows. When `productRow.date >= estimateNeedDate`, inserts an estimate need row before it. Each row covers `[estimateNeedDate, estimateDate]` with `amount = -(dailyRate * dayCount)`.

### Example: row insertion into product array

```text
Product A: estimate_need = 450, estimateNeedDays = 730
dailyRate = 0.616
addAfterDays = 14
unfinished_orders_in_weeks = 0

estimateNeedDate = Day 14 (currentDate + 14)

Product A rows after addSourceArray:
  Day 0   product    +100   (balance row)
  Day 5   sales      -20
  Day 12  sales      -30
  Day 20  sales      -10

Walking forward from row 2:
  Day 5 (i=2): Day 5 < Day 14 → skip
  Day 12 (i=3): Day 12 < Day 14 → skip
  Day 20 (i=4): Day 20 >= Day 14 → INSERT
    dayCount = 6 (Day 14 → Day 20)
    estimateDate = Day 20
    amount = -(0.616 * 6) = -3.70
    insert at position 4
    estimateNeedDate = Day 21

Result:
  Day 0   product    +100
  Day 5   sales      -20
  Day 12  sales      -30
  Day 20  estimate   -3.70  ← NEW (covers Day 14-20)
  Day 20  sales      -10
```

### Example: multiple estimate rows

```text
Product A: dailyRate = 1.0
estimateNeedDate = Day 3

Product A rows:
  Day 0   product    +100
  Day 5   sales      -20
  Day 12  sales      -30
  Day 20  sales      -10

Walk:
  Day 5 >= Day 3 → INSERT estimate row: -2 (2 days: Day 3→5)
    estimateNeedDate = Day 6
  Day 12 >= Day 6 → INSERT estimate row: -6 (6 days: Day 6→12)
    estimateNeedDate = Day 13
  Day 20 >= Day 13 → INSERT estimate row: -7 (7 days: Day 13→20)
    estimateNeedDate = Day 21

Result:
  Day 0   product    +100
  Day 5   estimate   -2     ← Day 3→5
  Day 5   sales      -20
  Day 12  estimate   -6     ← Day 6→12
  Day 12  sales      -30
  Day 20  estimate   -7     ← Day 13→20
  Day 20  sales      -10

Balance trace:
  Day 0:  bal = 100
  Day 5:  bal = 100 - 2 - 20 = 78
  Day 12: bal = 78 - 6 - 30 = 42
  Day 20: bal = 42 - 7 - 10 = 25
```

### Example: unfinished_orders_in_weeks shifts start date

```text
Product A: dailyRate = 1.0
addAfterDays = 14
unfinished_orders_in_weeks = 3

estimateNeedDate = max(14, 3 * 7) = Day 21

Product A rows:
  Day 0   product    +100
  Day 5   sales      -20
  Day 12  sales      -30
  Day 25  sales      -10

Walk:
  Day 5 < Day 21 → skip
  Day 12 < Day 21 → skip
  Day 25 >= Day 21 → INSERT estimate row: -4 (4 days: Day 21→25)

Result:
  Day 0   product    +100
  Day 5   sales      -20
  Day 12  sales      -30
  Day 25  estimate   -4     ← Day 21→25
  Day 25  sales      -10
```

### Example: negative daily rate error

```text
Product A: estimate_need = -50 (negative consumption)
estimateNeedDays = 730
dailyRate = -50 / 730 = -0.068

dailyRate < 0 → error: "average need -50.00 is negative, product id 'Product A'"
No estimate rows inserted. Error added to web errors.
```

### Example: zero estimate_need → no rows

```text
Product A: estimate_need = 0 (no sales in lookback period)
dailyRate = 0 / 730 = 0

dailyRate == 0 → skip (the elseif dailyRate > 0 check)
No estimate rows inserted. Product has no predicted demand.
```

## Why splitting must happen on every loop

### Simulation: Work1 (stock, daily estimate rate = 1)

Work1 has materials: Work2 (also a work with its own materials), Material3 (purchased).

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
```

Estimate need row created: range [Day 5 — Day 11], amount -7 (7 days × 1/day).

Now a sales order arrives at Day 8:

```text
Day 8  sales      -5    bal ?
```

### Without splitting (single estimate need row at Day 11)

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
Day 8  sales      -5    bal 3     ← estimate need for Day 5-11 not applied yet!
Day 11 estimate need   -7    bal -4   ← all estimate need hits at once
                                    need_down = 4, date = Day 11
```

Material demand: Work2 and Material3 needed by Day 11.

### With splitting (estimate need split at Day 8)

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
Day 5-8  estimate need -3    bal 5    ← estimate need for 3 days applied before sales
Day 8  sales      -5    bal 0
Day 8-11 estimate need -3    bal -3   ← remaining estimate need
                                    need_down = 3, date = Day 11
```

Same need_down date, different amount (3 vs 4). But what if the sales is bigger?

### Same scenario, sales -7 at Day 8

**Without splitting:**

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
Day 8  sales      -7    bal 1    ← still positive! estimate need not applied
Day 11 estimate need   -7    bal -6   ← need_down = 6, date = Day 11
```

**With splitting:**

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
Day 5-8  estimate need -3    bal 5
Day 8  sales      -7    bal -2   ← negative! need_down = 2, date = Day 8!
Day 8-11 estimate need -3    bal -5   ← more need_down propagates
```

### Splitting changes need_down date

Without split: need_down at Day 11.
With split: need_down at Day 8 — three days earlier.

This matters because Work1's need_down creates material rows for Work2 and Material3. If need_down date is Day 8 vs Day 11, the entire BOM tree schedules differently.

Work2 (also a work) gets its material row at Day 8 instead of Day 11. Work2's own balance calculation runs with this earlier date. Work2 may go negative earlier. Work2's materials get scheduled earlier. The cascade continues down the BOM tree.

### What if material row lands at Day 6 (inside first estimate need range)?

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
```

Material row from upper level need_down arrives at Day 6:

**Without splitting:**

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
Day 6  material   -3    bal 5
Day 11 estimate need   -7    bal -2   ← need_down = 2, date = Day 11
```

**With splitting:**

```text
Day 1  product    +10   bal 10
Day 4  sales      -2    bal 8
Day 5-6  estimate need -2    bal 6    ← 2 days of estimate need before material
Day 6  material   -3    bal 3
Day 6-11 estimate need -5    bal -2   ← need_down = 2, date = Day 11
```

Same final need_down amount, but different intermediate balances. In both cases Day 11 need_down = 2. The difference appears when more events happen between Day 6 and Day 11.

### Key insight: split changes when balance goes negative

The critical question is: does the balance go negative at a different date because of splitting?

If yes → need_down date changes → material scheduling changes → BOM cascade.
If no → same need_down, splitting is cosmetic for that product.

Since material rows are inserted during the while loop (each loop is a BOM level deeper), and those material rows can land inside estimate need date ranges, splitting must happen after each `addLowerLevelNeed` call.

### Can we skip splitting and do it at the end only? No

If we skip splitting during the while loop:

1. Balance calculations show wrong intermediate values
2. Wrong need_down dates and amounts
3. Wrong material demand propagated to lower BOM levels
4. Lower levels calculate wrong need_down for their own materials
5. Entire BOM tree has wrong scheduling

Splitting at the end only fixes the top level display but not the intermediate calculations that drive the BOM explosion.

### Split implementation in insertToProductIdxArray (product-need.lua)

When `addLowerLevelNeed` inserts a material row via `insertToProductIdxArray`, after insertion, scans backward from position to find any estimate need row whose range contains the new row's date:

```text
Before: estimateRow [Day 5 — Day 11] amount -7
Material row lands at Day 8
After:  estimateRow [Day 5 — Day 8]  amount -3
        newRow [Day 9 — Day 11] amount -3
```

Scan is backward from insertion point, breaks on first estimate need row found. O(K) where K = distance to nearest estimate need row.

### Performance

Old code scanned entire product array on every insertion. O(N × M).

New approach: estimate need rows exist upfront. When a material row is inserted, check only nearby estimate need rows. O(N × K) where K = number of estimate need rows (typically 10-20, constant).

## Estimate need row identification

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

## Range calculation for each estimate need row

For a product with estimate need rows in its array:

- First row: range = `[daysAdd(currentDate, max(addAfterDays, unfinishedOrdersWeeks * 7)), row.date]`
- Subsequent rows: range = `[previousRow.date + 1 day, row.date]`
- Day count = `dayDifference(rangeStart, row.date)`
- Amount = `-(dailyRate * dayCount)`

## Module-level variables

- `fromEstimateNeed = 5` — identifies estimate need rows in the product array

## Check/fix estimate need in output (checkCalculation)

Converts numeric estimate_need to a daily rate display string:

- For the last row: shows the daily rate formatted to 2 decimal places.
- For other rows: shows `"total / days = daily_rate"` format.

## Full end-to-end example

```text
Constants:
  estimate_need_last_days = 365
  estimate_need_calculation_method = "last"
  estimate_need_growth_rate = 1.1
  estimate_need_add_after_days = 14

Today = 2026-04-14

Product SCREW-M5 (stock):
  SUM query for [2025-04-14, 2026-04-14) grouped by product_id + actual_delivery_date:
    delivery_amount = 1000 (sum of all deliveries across all dates)
  daily_need = 1000 / 365 = 2.740
  pr.estimate_need = 1000

  unfinished_orders_in_weeks = 0
  addAfterDays = 14
  estimateNeedDate = Day 14

  Product rows after addSourceArray:
    Day 0   product    +500
    Day 10  sales      -100
    Day 25  sales      -200
    Day 40  sales      -150

  dailyRate = 1000/365 * 1.0 * 1.1 = 3.014

  addEstimateRow:
    Day 10 < Day 14 → skip
    Day 25 >= Day 14 → INSERT -33.15 (11 days: Day 14→25)
      estimateNeedDate = Day 26
    Day 40 >= Day 26 → INSERT -42.19 (14 days: Day 26→40)
      estimateNeedDate = Day 41

  Final rows:
    Day 0   product    +500
    Day 10  sales      -100     bal = 400
    Day 25  estimate   -33.15   bal = 366.85
    Day 25  sales      -200     bal = 166.85
    Day 40  estimate   -42.19   bal = 124.66
    Day 40  sales      -150     bal = -25.34  ← need_down!

  need_down = 25.34, date = Day 40

  Without estimate need, balance at Day 40 would be:
    500 - 100 - 200 - 150 = 50 (still positive, no need_down)

  With estimate need, the predicted future consumption causes
  need_down 25 days earlier than it would appear from actual orders alone.
```

## SUM query optimization

### Before (44K rows)

Single query loaded ALL individual delivery rows. `loadSourceRow` iterated every row. `addDeliveredRow` bucketed each row by date range into `source_row`/`base_source_row` arrays. `sumConsumption` iterated those arrays to sum amounts. Three separate queries ran when any product used "period" method.

### After (one query, pre-summed by product+date)

Query uses `aggregate: { "sum": ["ordr.delivery_amount"] }`, sql GROUP BY is auto generated from aggregate. With `field: ["ordr.product_id", "ordr.delivery_state", "ordr.delivery_amount", "ordr.waiting_for_delivery", "ordr.actual_delivery_date"]`. Database returns one row per (product_id, actual_delivery_date) with the total delivery_amount. One query covers the full extended date range (including growth period). `loadSourceRow` buckets these rows by comparing `actual_delivery_date` to period/growth date ranges. No extra queries for period method.

### Performance improvement

- One query instead of up to three
- Database returns pre-summed rows (one per product+date) instead of individual delivery rows
- No `methodFilter` — all methods handled in a single `loadSourceRow` pass
- No `hasPeriodMethod` detection — period date ranges are always computed and used for bucketing
- No grid save/restore — dedicated grid installed once
- `calcNeedAndInsertRow` does sum + set + insert in one loop instead of three separate functions
