# ECB Exchange Rates - Developer Guide

## Purpose
This module imports ECB FX rates into `exchange_rates` so the app can use a local, queryable history.

## Data model
Table: `exchange_rates`

- `id` (UUID, primary key)
- `cur1` (base currency, 3 chars)
- `cur2` (quote currency, 3 chars)
- `rate` (decimal 20,10)
- `rate_date` (date)
- `source` (default `ECB`)
- timestamps

Unique key:

- (`cur1`, `cur2`, `rate_date`, `source`)

## Main components

- Migration: `database/migrations/2026_03_24_120000_create_exchange_rates_table.php`
- Model: `app/Models/ExchangeRate.php`
- Service: `app/Services/EcbExchangeRateService.php`
- Command: `app/Console/Commands/SyncEcbExchangeRates.php`

## ECB API strategy
The service uses the ECB historical endpoint:

- `https://data-api.ecb.europa.eu/service/data/EXR/D..EUR.SP00.A`

Query params used:

- `startPeriod=YYYY-MM-DD`
- `endPeriod=YYYY-MM-DD`
- `format=csvdata`

Behavior:

- Default load window: last 7 days
- Without `--start`: `--years=X` means **X years back from today**
- With `--start`: `--years=X` means **X years forward from `--start`**, capped at today (unless `--end` is set)
- With `--start` and `--end`: explicit `[start, end]` range (`--years` is ignored)
- Long ranges are split into **multiple ECB HTTP calls** (`--chunk-years`, default **1 year per request**) to avoid timeouts and huge CSV payloads
- Base currency is EUR (`cur1 = EUR`)
- Adds synthetic `EUR/EUR = 1` rows for each imported date
- Fails the run if **no rows** are found for the selected period (to avoid silently “successful” empty syncs)

## Logging

- Job logs: `storage/logs/job_logs.log`
  - Start: `lnc:sync-ecb-exchange-rates started`
  - End: `lnc:sync-ecb-exchange-rates finished`
  - Failure: `lnc:sync-ecb-exchange-rates failed`

## TLS / HTTPS verification config

The service uses Laravel HTTP client options controlled by config:

- `services.ecb.verify` (default `true`)
- `services.ecb.ca_bundle` (path to CA bundle; when set, it overrides `verify`)

## Command usage

- Default (7 days):
  - `php artisan lnc:sync-ecb-exchange-rates`
- History from today (example 10 years back):
  - `php artisan lnc:sync-ecb-exchange-rates --years=10`
- **X years from a fixed start date** (recommended for 20–30 year backfills):
  - `php artisan lnc:sync-ecb-exchange-rates --start=2000-01-01 --years=30`
- Explicit date range:
  - `php artisan lnc:sync-ecb-exchange-rates --start=2015-01-01 --end=2020-12-31`
- Larger chunks per request (fewer HTTP calls; may time out on slow networks):
  - `php artisan lnc:sync-ecb-exchange-rates --start=2000-01-01 --years=30 --chunk-years=2`

Validation:

- `--years` must be `>= 1` when provided
- `--chunk-years` must be `>= 1`

## Scheduling with system cron (not Laravel scheduler)
Use OS cron to run the command, for example on weekdays at 16:30:

```bash
30 16 * * 1-5 cd /path/to/tikeasy && php artisan lnc:sync-ecb-exchange-rates >> storage/logs/cron.log 2>&1
```

If you need explicit timezone:

```bash
CRON_TZ=Europe/Paris
30 16 * * 1-5 cd /path/to/tikeasy && php artisan lnc:sync-ecb-exchange-rates >> storage/logs/cron.log 2>&1
```

## Conflict handling note
To avoid PostgreSQL conflict edge cases, writes are done with `updateOrInsert` row-by-row after payload de-duplication on:

- `cur1`, `cur2`, `rate_date`, `source`

This prevents duplicate-key collisions in a single bulk upsert payload.

## Troubleshooting

- **No rows imported**
  - Check requested date range and ECB publication day (business days).
- **HTTP error**
  - Verify outbound network access to `data-api.ecb.europa.eu`.
- **Unexpected CSV format**
  - ECB headers may have changed; inspect response and update parser mapping.