# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Overview

Data Intelligence Report Generator for ERP ROA (Oracle Database). Generates Excel and PDF business intelligence reports with sales analytics, margin analysis, stock tracking, financial indicators, and alerts.

## Commands

```bash
# Virtual Environment setup
python -m venv .venv
source .venv/bin/activate  # Linux/WSL
pip install -r requirements.txt

# Run report (default: last 12 months)
python main.py

# Custom period
python main.py --months 6

# Docker alternative
docker-compose run --rm report-generator
```

## Oracle Connection

| Environment | ORACLE_HOST value |
|-------------|-------------------|
| Windows native | `127.0.0.1` |
| WSL | Windows IP (`cat /etc/resolv.conf \| grep nameserver`) |
| Docker | `host.docker.internal` |

## Architecture

```
main.py                    # Entry point, orchestrates everything
├── config.py              # .env loader, thresholds (RECOMMENDATION_THRESHOLDS)
├── queries.py             # SQL queries in QUERIES dict with metadata
├── recommendations.py     # RecommendationsEngine - auto-generates alerts
└── report_generator.py    # Excel/PDF generators
```

**Data flow**:
1. `main.py` executes queries via `OracleConnection` context manager
2. Results stored in `results` dict (query_name → DataFrame)
3. Consolidation logic merges related DataFrames (e.g., KPIs + YoY)
4. `ExcelReportGenerator` creates consolidated sheets + detail sheets
5. `PDFReportGenerator` creates consolidated pages + charts

**Report structure** (after consolidation):
- **Excel**: 4 consolidated sheets (Vedere Ansamblu, Indicatori Venituri, Clienti si Risc, Tablou Financiar) + detail sheets
- **PDF**: Consolidated pages with multiple sections + charts + detail tables

## Key Code Locations

| What | Where |
|------|-------|
| SQL queries | `queries.py` - constants like `SUMAR_EXECUTIV`, `CONCENTRARE_RISC_YOY` |
| Query registry | `queries.py:QUERIES` dict |
| Sheet order | `main.py:sheet_order` list (~line 242) |
| Consolidated sheets | `main.py` after "GENERARE SHEET-URI CONSOLIDATE" (~line 567) |
| Legends | `main.py:legends` dict (~line 303) |
| Alert thresholds | `config.py:RECOMMENDATION_THRESHOLDS` |
| Consolidated sheet method | `report_generator.py:ExcelReportGenerator.add_consolidated_sheet()` |
| Consolidated page method | `report_generator.py:PDFReportGenerator.add_consolidated_page()` |

## Adding New Reports

1. Add SQL constant in `queries.py` (e.g., `NEW_QUERY = """SELECT..."""`)
2. Add to `QUERIES` dict: `'new_query': {'sql': NEW_QUERY, 'params': {'months': 12}, 'title': '...', 'description': '...'}`
3. Add `'new_query'` to `sheet_order` in `main.py`
4. Add legend in `legends` dict if needed
5. For PDF: add rendering in PDF section of `generate_reports()`

## Adding Consolidated Views

To add data to consolidated sheets, modify the `sections` list in `add_consolidated_sheet()` calls:
```python
excel_gen.add_consolidated_sheet(
    name='Sheet Name',
    sections=[
        {'title': 'Section', 'df': results.get('query_name'), 'legend': legends.get('query_name')}
    ]
)
```

## Oracle Schema Conventions

- `sters = 0` excludes deleted records
- `tip NOT IN (7, 8, 9, 24)` excludes returns/credit notes
- Account `341`, `345` = own production; `301` = raw materials
- Required views: `fact_vfacturi2`, `fact_vfacturi_detalii`, `vnom_articole`, `vnom_parteneri`, `vstoc`, `vrul`

## YoY Query Pattern

When creating Year-over-Year comparison queries:
1. Use CTEs for current period (`ADD_MONTHS(TRUNC(SYSDATE), -12)` to `SYSDATE`)
2. Use CTEs for previous period (`ADD_MONTHS(TRUNC(SYSDATE), -24)` to `ADD_MONTHS(TRUNC(SYSDATE), -12)`)
3. Handle empty previous data with `NVL()` fallback to 0
4. Add `TREND` column with values like `'CRESTERE'`, `'SCADERE'`, `'STABIL'`, `'FARA DATE YOY'`

## Conditional Formatting Colors

| Status | Excel Fill | Meaning |
|--------|------------|---------|
| OK/Good | `#4ECDC4` (teal) | CRESTERE, IMBUNATATIRE, DIVERSIFICARE |
| Warning | `#FFE66D` (yellow) | ATENTIE |
| Alert | `#FF6B6B` (red) | ALERTA, SCADERE, DETERIORARE, CONCENTRARE |