fcsc_ipi_backend/ipi-index-calculation-document.md

# IPI Index Calculation Document

## Purpose
This document explains what happens when `runCompleteCalculation(year, month)` is executed in `app/services/ipi_calculation_service.js`, including formulas, data flow, and output tables.

## Runtime Entry Flow (Actual Start Point)
Calculation does not start by directly calling `runCompleteCalculation` from `server.js`.
It starts through scheduler wiring:

1. `server.js` creates scheduler:
   - `const scheduler = new AutomatedSchedulerService();`
2. `server.js` starts jobs:
   - `scheduler.start();` (IPI calculation schedule)
   - `scheduler.startRemainderEmail();` (email reminders)
3. `start()` in `app/services/scheduler.service.js` registers cron:
   - `cron.schedule('0 2 * * *', ...)` => runs daily at 2:00 AM
4. Cron calls `runScheduledTask()`
5. `runScheduledTask()` flow:
   - fetch latest unprocessed quarter (`ipi_calculated = 0`)
   - check current date >= quarter `end_date`
   - run auto submission fill (`executeSurveyAutoSubmit`)
   - run IPI quarter calculation (`executeCalculateQuarter`)
   - set `ipi_calculated = 1`
6. `executeCalculateQuarter(year, quarter)` loops through quarter months and calls:
   - `runCompleteCalculation(year, month)` for each month

Manual trigger path also exists:
- `POST /api/admin/trigger-scheduler` -> `scheduler.manualTrigger()` -> `runScheduledTask()`

## Calculation Pipeline Entry
Function: `runCompleteCalculation(year, month)`

Execution order:
1. `aggregateMonthlyProduction(year, month)`
2. `calculateItemLevelIndices(year, month)`
3. `calculateISIC4DigitIndices(year, month)`
4. `calculateISIC3DigitIndices(year, month)`
5. `calculateISIC2DigitIndices(year, month)`
6. `calculateManufacturingIPI(year, month)`

If any step throws an error, the pipeline stops and the error is propagated.

---

## Data Prerequisite (Base Year)
Before running the monthly pipeline, base-year data must exist in `base_year_production` (typically by running `calculateBaseYearProduction(2022)`).

Base-year average production per product is calculated as:

`BaseAvg_i = (Jan_i + Feb_i + Mar_i + Apr_i + May_i + Jun_i + Jul_i + Aug_i + Sep_i + Oct_i + Nov_i + Dec_i) / 12`

Stored in:
- `base_year_production.avg_by_production`

Only approved submissions and active submission products are used.

---

## Step-by-Step Flow in `runCompleteCalculation`

## Step 1: Aggregate Monthly Production
Function: `aggregateMonthlyProduction(year, month)`

What it does:
- Maps the target month to quarter and period field:
  - Month 1 -> `Q1/current_quantity_period_one`
  - Month 2 -> `Q1/current_quantity_period_two`
  - ...
  - Month 12 -> `Q4/current_quantity_period_three`
- Sums product quantity across all approved submissions for that period.
- Writes one record per product to `monthly_production`.

Formula:

`MonthlyProduction_i(y,m) = SUM( submission_products.period_quantity )`

Output table:
- `monthly_production(product_id, year, month, production_quantity, ...)`

---

## Step 2: Calculate Item Level Indices
Function: `calculateItemLevelIndices(year, month)`

What it does:
- Joins current month production with base-year average production by product.
- Excludes products where base average is zero or null (`avg_by_production > 0`).
- Stores item-level relative and item index.

Formulas:

`Ri = CurrentProduction_i / BaseAvg_i`

`Ii = Ri * 100`

Expanded:

`ItemIndex_i(y,m) = (MonthlyProduction_i(y,m) / BaseAvg_i) * 100`

Output table:
- `item_level_indices(product_id, current_production, base_year_avg_production, production_relative, item_index, ...)`

Interpretation:
- `100` means equal to base-year monthly average.
- `>100` means above base-year average.
- `<100` means below base-year average.

---

## Step 3: Calculate ISIC 4-Digit Indices
Function: `calculateISIC4DigitIndices(year, month)`

What it does:
- Assigns each product to ISIC group using `LEFT(establishments.isic_code, 4)`.
- Uses item-level index values belonging to that 4-digit group.
- Stores count and group index.

Implemented formulas:

`total_weight_g = COUNT(DISTINCT product_id)`

`weighted_index_sum_g = SUM(ItemIndex_i)`

`ISIC4Index_g = AVG(ItemIndex_i)`

Note:
- In this implementation, "weight" is the number of distinct products (equal weight per product), not an external official weight table.

Output table:
- `isic_4digit_indices(isic_4digit_code, total_weight, weighted_index_sum, isic_4digit_index, ...)`

---

## Step 4: Calculate ISIC 3-Digit Indices
Function: `calculateISIC3DigitIndices(year, month)`

What it does:
- Rolls up 4-digit results to 3-digit groups via `LEFT(isic_4digit_code, 3)`.
- Uses weighted average based on `total_weight` from 4-digit level.

Formulas:

`W_h = SUM(total_weight_g)` for all 4-digit groups `g` under 3-digit group `h`

`WeightedSum_h = SUM(total_weight_g * ISIC4Index_g)`

`ISIC3Index_h = WeightedSum_h / W_h`

Output table:
- `isic_3digit_indices(isic_3digit_code, total_weight, weighted_index_sum, isic_3digit_index, ...)`

---

## Step 5: Calculate ISIC 2-Digit Indices
Function: `calculateISIC2DigitIndices(year, month)`

What it does:
- Rolls up 3-digit results to 2-digit groups via `LEFT(isic_3digit_code, 2)`.
- Uses weighted average based on `total_weight` from 3-digit level.

Formulas:

`W_k = SUM(total_weight_h)` for all 3-digit groups `h` under 2-digit group `k`

`WeightedSum_k = SUM(total_weight_h * ISIC3Index_h)`

`ISIC2Index_k = WeightedSum_k / W_k`

Output table:
- `isic_2digit_indices(isic_2digit_code, total_weight, weighted_index_sum, isic_2digit_index, ...)`

---

## Step 6: Calculate Manufacturing IPI (Headline)
Function: `calculateManufacturingIPI(year, month)`

What it does:
- Computes overall manufacturing index from all 2-digit ISIC groups.
- Then computes Month-on-Month (MoM) and Year-on-Year (YoY) percentage changes.
- Writes final result in `manufacturing_ipi`.

Headline formulas:

`TotalWeight = SUM(total_weight_k)`

`TotalWeightedSum = SUM(total_weight_k * ISIC2Index_k)`

`ManufacturingIPI(y,m) = TotalWeightedSum / TotalWeight`

Change formulas:

`MoM(%) = ((IPI(y,m) - IPI(prev_month)) / IPI(prev_month)) * 100`

`YoY(%) = ((IPI(y,m) - IPI(y-1,m)) / IPI(y-1,m)) * 100`

Reference date:
- First day of month: `YYYY-MM-01`

Output table:
- `manufacturing_ipi(year, month, manufacturing_index, mom_change, yoy_change, total_weight, weighted_index_sum, status, generated_on, ...)`

---

## Logging and Transactions
Each calculation step:
- Opens its own DB transaction.
- Creates a start log entry in `calculation_log`.
- Updates log as Completed/Failed with record count.
- Commits on success and rolls back on error.

This means:
- A step is atomic by itself.
- The full pipeline is not one global transaction across all six steps.

---

## End-to-End Formula Chain (Compact View)
1. `BaseAvg_i = Avg monthly production in base year (2022)`
2. `ItemIndex_i = (CurrentProduction_i / BaseAvg_i) * 100`
3. `ISIC4Index = Avg(ItemIndex_i within ISIC4)`
4. `ISIC3Index = Weighted avg(ISIC4Index by ISIC4 total_weight)`
5. `ISIC2Index = Weighted avg(ISIC3Index by ISIC3 total_weight)`
6. `ManufacturingIPI = Weighted avg(ISIC2Index by ISIC2 total_weight)`
7. `MoM`, `YoY` from prior stored headline indices.

---

## Client Explanation Script (Short)
- We first aggregate monthly production quantity by product from approved submissions.
- Each product is compared against its base-year monthly average (2022), giving an item index where 100 equals base-year average output.
- Product indices are grouped by ISIC 4-digit, then rolled up to ISIC 3-digit and 2-digit using weighted averages.
- The final manufacturing IPI is the weighted average of all ISIC 2-digit indices.
- We then compute MoM and YoY growth rates using previously stored headline values.