# Calc Server Data Fix Documentation

## Overview

The calc server loads data from multiple sources (local DB, external DBs like 4D, Odoo) and fixes/resolves data inconsistencies before calculations.

## Current Data Fix Logic

### workNumberProduct Lookup Table

Built from `product` table to map `work_number_id` → `product_id`.

**Source:** `product` table (`record_type = ""`)

**Logic:**

1. For each product, if `work_number_id` is nil → set to `product_id`
2. If `work_number_id` is not empty → add to lookup
3. Skip products with empty `work_number_id`

**Used by:**

- `setProductId()` function to resolve `work_number_id` → `product_id` in:
  - `production_order`
  - `product_work-estimated`
  - `product_material-estimated`
  - `product_work-actual`
  - `product_material-actual`
  - `product_work-schedule`

### Current Error Handling

When `workNumberProduct[workNum]` is nil:

1. **Product exists but has empty work_number_id:**
   - Print warning with product details
   - Fix: set `product.work_number_id = workNum`

2. **Product not found:**
   - Print warning with production order details
   - Add to `missingProduct` array
   - Create virtual product record with error flag

3. **allowMissingProduct = true:**
   - Silently skip (used for estimated data)

---

## Problem: Products Without work_number_id

**Scenario:** Product is no longer manufactured, bought instead.

**In 4D:**

- `product` table: `work_number_id` is empty (null)
- `work` table: has complete `product_id` ↔ `work_number_id` mapping

**In Capacic local DB:**

- Both stored in `product` table with different `record_type`
- `record_type = ""`: product records (work_number_id can be empty)
- `record_type = "work"`: work records (always has mapping)

**Current Issue:**

- `workNumberProduct` built only from `record_type = ""`
- Products with empty `work_number_id` are skipped
- Lookup fails when trying to resolve these products

---

## Solution: Load product-work Table

### Files to Create/Modify

- [x] Create query: `nc-backend/plugin/nc-calc-server/query/product-work.json`
- [x] Add constant: `load_product_work_schema` to `nc-plugin/plugin/nc-calc-server/constant.json`
- [x] Modify: `nc-plugin/plugin/nc-calc-server/nc-calc-server-calc.lua` - load query conditionally
- [x] Modify: `nc-plugin/plugin/nc-calc-server/nc-calc-server-fix-data.lua` - use product-work data

### product-work Query

Loads product records with `record_type = "work"`.

```json
{
    "name": "plugin/nc-calc-server/query/product-work.json",
    "table": [
        {
            "table": "product",
            "record_type": ["work"]
        }
    ],
    "parameter": {
        "show_sql": false
    },
    "query": [["pr.product_id", "<>", "null"]]
}
```

### Constant Setting

```json
"load_product_work_schema": { "4d": true }
```

Only schemas with `true` will load product-work data.

---

## Data Integrity Checks

### Check 1: 1:1 Link Integrity (Both Directions)

When adding from product-work to `workNumberProduct`:

- [x] **Direction 1:** `workNumberProduct[workNum]` already exists with different `product_id`
  - **Warning:** "product work has different product id 'X' for work number id 'Y', existing 'Z'"
  - **Action:** Do NOT overwrite, keep existing mapping
  - **Reason:** Existing mapping from product table takes precedence

- [x] **Direction 2:** `productWorkNumber[productId]` already exists with different `work_number_id`
  - **Warning:** "product work has different work number id 'X' for product id 'Y', existing 'Z'"
  - **Action:** Do NOT overwrite, keep existing mapping
  - **Reason:** Same product should not map to different work numbers

### Check 2: Missing Product but Work Exists

When `workNumberProduct[workNum]` is nil but work record exists:

- [x] **Case:** Work record has `product_id` but product not in product table
  - Handled by existing virtual product creation in fix-data.lua
  - **Action:** Create virtual product record

### Check 3: Work Record Missing product_id

- [x] **Case:** Work record exists but `product_id` is empty
  - **Warning:** "product work missing product id, work number id 'X'"
  - **Action:** Skip this work record, do not add to lookup

### Check 4: Work Record Missing work_number_id

- [x] **Case:** Work record exists but `work_number_id` is empty
  - **Warning:** "product work missing work number id, product id 'X'"
  - **Action:** Skip this work record, do not add to lookup

### Check 5: Product-work Not Loaded

- [x] **Case:** Schema not in `load_product_work_schema` or value is false
  - **Action:** Skip silently, use existing product-only lookup
  - **Reason:** Some systems (Odoo) may not have separate work table

### Check 6: Fix Product Record

- [x] **Case:** Product record has empty `work_number_id` but product-work has mapping
  - **Warning:** "product has empty work_number_id, fixed to 'X', product_id 'Y', name 'Z'"
  - **Action:** Set `product.work_number_id = work.work_number_id`
  - **Reason:** Ensure product record is complete for future lookups

---

## Implementation Order

1. [x] Create query file `product-work.json`
2. [x] Add `load_product_work_schema` to constant.json
3. [x] Modify `nc-calc-server-calc.lua` to load query conditionally
4. [x] Modify `nc-calc-server-fix-data.lua` to:
   - [x] Check if product-work data exists
   - [x] Add Check 1: 1:1 link integrity (both directions)
   - [x] Add Check 2: Missing product → create virtual (existing code)
   - [x] Add Check 3: Work missing product_id → warn and skip
   - [x] Add Check 4: Work missing work_number_id → warn and skip
   - [x] Add Check 6: Fix product record if empty work_number_id
5. [ ] Test with 4D schema
6. [ ] Verify virtual products appear correctly
7. [ ] Verify no errors in console

---

## Data Flow

```text
product table (record_type="")
    ↓
workNumberProduct[work_number_id] = product_id
    ↓
product-work table (record_type="work")
    ↓
Check 1: 1:1 integrity (both directions)
Check 3: product_id exists
Check 4: work_number_id exists
    ↓
workNumberProduct[work_number_id] = product_id (add missing)
productWorkNumber[product_id] = work_number_id (track reverse)
    ↓
Check 6: Fix product.work_number_id if empty
    ↓
setProductId() uses complete lookup
    ↓
Check 2: If still missing → create virtual product
```
