# Tikeasy 2-hour vCron: `lnc:rebuild_quarters_kpi --last-updates`

This document explains what the Tikeasy vCron does when it runs every 2 hours with:

```bash
php /var/www/html/tikeasy/artisan lnc:rebuild_quarters_kpi --last-updates
```

The implementation lives in `app/Console/Commands/RebuildAllQuarters.php` (Artisan command `lnc:rebuild_quarters_kpi`).

## High-level flow (what happens each run)

- **Job logging starts** (into `storage/logs/job_logs.log`) with the resolved options (`begin-date`, `last-updates`, `only-cagr`).
- **EDM batch import always runs first** (even if KPI rebuild later fails).
- **Night window tasks run only between 00:00 and 04:00 server time**:
  - Sync ECB FX rates into the DB.
  - Fill missing KPI EUR-converted fields (`*_EURO`) using those FX rates.
  - Failures here are caught and logged as a warning; the main KPI rebuild still continues.
- **Quarter KPI rebuild** runs for a *subset of companies* when `--last-updates` is used (last 24h), otherwise it can run for all companies.
- **Job logging ends** with status + duration + number of companies processed.

## Step 1 — EDM batch import (always)

The cron command directly executes `lnc:import-edm` logic by instantiating `App\Console\Commands\import_edm` and calling `handle()`.

### Input

- **Folder**: `storage/app/edm_batch/`
- **Files**: only `*.csv` files whose **filename (without `.csv`)** matches one of:
  - `edm_funds`
  - `edm_agents`
  - `edm_instruments`
  - `edm_deal_link`
  - `edm_deals`

Any other `*.csv` file is skipped.

### Processing

For each recognized CSV:

- The entire file is read into memory.
- Import is delegated to `App\Http\Controllers\API\EdmController::analyse_csv($table, $csvContent, true)`
  - The third argument is `true` meaning the importer will convert text from **Windows-1252 → UTF-8** for selected columns.
- Tables are imported using DB upserts (examples from the code):
  - `edm_funds`: upserts into `edm_investors` (key: `edm_id`)
  - `edm_agents`: upserts into `edm_investors`, persists agent metadata, and triggers updates to related Tikeasy companies (see `EdmController::update_edm_companies(...)`)
  - Other EDM table handlers exist in `EdmController` (e.g. deals, deal links); behavior depends on the handler implementation.

### Important behavior (destructive)

- **Each imported CSV is deleted** after processing (`unlink(...)`).
  - This makes the import “consume” the batch folder contents on every cron run.

### Logs

Each file import writes start/finish entries into:

- `storage/logs/job_logs.log` under message keys:
  - `lnc:import-edm started`
  - `lnc:import-edm finished`

## Step 2 — Night window tasks (only 00:00–04:00)

Between **00:00 inclusive** and **04:00 exclusive** server time, the cron additionally tries to keep FX rates and EUR KPI fields up to date.

These tasks are wrapped in a `try/catch`: if they fail, the cron logs a warning and continues to the KPI rebuild.

### 2.1 Sync ECB historical FX rates

Service: `App\Services\EcbExchangeRateService`

- Called method: `syncHistoricalRates()`
- Default date range (when called with no args): **last 7 days**
- Source: ECB data API
- DB target: `exchange_rates` table
  - Records are upserted on `(cur1, cur2, rate_date, source)`
  - Stored with `cur1 = 'EUR'` and `cur2 = <quote currency>`
  - An identity row for `EUR/EUR` with rate `1` is also generated per date

Logs (same file `storage/logs/job_logs.log`):

- `lnc:sync-ecb-exchange-rates started`
- `lnc:sync-ecb-exchange-rates finished`

### 2.2 Fill missing KPI amounts converted to EUR

Service: `App\Services\KpiEuroConversionService`

- Called method: `run(KpiEuroConversionService::MODE_FILL_MISSING)`
- Mode: **fill_missing**
  - Only writes EUR fields that are *missing* in the KPI JSON payload.
  - It does **not** remove/rebuild existing EUR values in this cron flow.

Scope of rows processed:

- `kpis.report_type = 'ACTUAL'`
- `kpis.report_period IN ('LTM', 'M')`

Fields written into `kpis.data` (JSON) when possible:

- For `LTM` rows:
  - `SALES_EURO` from `SALES`
  - `EBITDA_EURO` from `EBITDA`
  - `EBIT_EURO` from `EBIT`
- For `M` rows:
  - `EV_EURO` from `EV`
  - `RISKEV_EURO` from `RISKEV`

Currency resolution order:

1. `kpis.data['Currency']`
2. `kpis.currency`
3. `company.data['Currency']`

FX lookup behavior:

- Looks up ECB rates in `exchange_rates` for up to **7 days back** from the KPI `report_date` (to tolerate missing FX for weekends/holidays).
- Conversion used by code:
  - ECB stores “units of local currency per 1 EUR”
  - So conversion is: `EUR = local_amount / rate`

Logs:

- `lnc:convert-kpis-to-euro started`
- `lnc:convert-kpis-to-euro finished`

## Step 3 — Quarter KPI rebuild (main purpose of cron)

### 3.1 What `--last-updates` means

When the cron runs with `--last-updates`:

- It sets an internal begin date to: **now - 24 hours**
- It selects only companies that have at least one KPI updated since that begin date **excluding** already-quarter rows.

Selection condition (as implemented in SQL):

- A company is included if it has a KPI such that:
  - `kpis.updated_at::date >= beginDate`
  - `kpis.report_period <> 'QUARTER'`

Notes:

- The comparison uses `updated_at::date`, so it effectively works at **date granularity** (not exact timestamp).

### 3.2 What is recalculated for each selected company

Per company, the cron calls:

- `App\Services\CalcQuarterService::calc1CompanyQuarter($company, false, $onlyCagr)`

Key side effects (when `simulation=false`, which is the cron behavior):

- **Company “last report date” update**:
  - Finds the most recent KPI (ACTUAL, period M or YTD) that contains non-zero `SALES` or `EBITDA`.
  - Updates `companies.LastReportDate` if it changed.
- **CAGR calculation**:
  - Updates `companies.CAGR_*` fields and stores a text log in `companies.CAGR_LOG`.
  - Runs even when `--only-cagr` is used; if `--only-cagr` is set, the process stops after CAGR.
- **Fiscal year alignment on all KPI rows**:
  - Iterates the company KPIs and writes/updates `kpis.fiscal_year_end` based on the company FY end month.
- **Quarter KPI regeneration**:
  - Deletes all existing KPI rows where `report_period = 'QUARTER'` for that company.
  - Deletes “empty” KPI records where `data = '[]'`.
  - Rebuilds `QUARTER` rows by deriving quarter values from YTD rows:
    - For the fiscal first quarter: quarter = YTD
    - For subsequent quarters: quarter = YTD(current) − YTD(previous quarter)
  - Generates `QUARTER_ID` (e.g. `2026Q1`) and `QUARTER_DATE` (end-of-month of the calendar quarter).
- **Company cache updates**:
  - Stores some “last full year” indicators into `companies.cache` (including a computed leverage string).

### 3.3 How many companies it processes

- With `--last-updates`: only the filtered set as described above.
- Without any date options: it processes **all companies** (in chunks of 100).

### Output

- Console shows a progress bar and prints `Done !` at the end.
- `storage/logs/job_logs.log` receives:
  - `lnc:rebuild_quarters_kpi started`
  - `lnc:rebuild_quarters_kpi finished` (with status, duration, company count, and resolved begin date)

## Troubleshooting / where to look first

- **Main log file**: `storage/logs/job_logs.log`
  - Search for the messages listed above to see exactly which step failed.
- **EDM import “missing folder”**:
  - If `storage/app/edm_batch` does not exist, the import command prints an error and returns exit code `2`.
- **FX/EUR conversions**:
  - Only run in the night window (00:00–04:00).
  - If ECB is unreachable or returns unexpected data, you’ll see errors in `lnc:sync-ecb-exchange-rates`.
- **Long runtimes**:
  - Quarter rebuild is per-company and may be heavy; `--last-updates` limits work to recently changed companies.