# pivot_forecast

Sales forecasting API using Node.js/Express + PostgreSQL. Takes historical sales data and generates forecast baselines, with the ability to scale/adjust forecasts for different scenarios.

## Project Structure

```
pivot_forecast/
├── index.js                 # Express server (2 routes: /baseline, /scale)
├── package.json
├── .env                     # DB connection (host, port, user, password, database)
├── routes/
│   ├── baseline/
│   │   ├── baseline.sql     # SQL for baseline generation
│   │   ├── generate_route_sql.sh
│   │   └── req.json         # Example request
│   └── scale/
│       ├── scale.sql        # SQL for scaling
│       ├── generate_route_sql.sh
│       └── req.json
├── setup_sql/
│   ├── 01_schema.sql        # Create fc schema, tables, period data
│   ├── 02_target_info.sql   # Populate target_meta from dcard columns
│   └── 03_build_master_tables.sql
└── readme.md
```

## Database Schema (`fc` schema)

### Tables

| Table | Purpose |
|-------|---------|
| `fc.dcard` | Main sales data (copied from source at setup) |
| `fc.target_meta` | Column metadata for dcard |
| `fc.appcols` | App column definitions (value, cost, units, dates, status) |
| `fc.log` | Forecast change log (JSONB) |
| `fc.sql` | Stored SQL commands |
| `fc.perd` | Fiscal period/calendar (2008-2023) |

### target_meta Column Definitions

| Field | Description |
|-------|-------------|
| `schema` | Schema of target table |
| `tname` | Table name |
| `cname` | Column name |
| `opos` | Ordinal position |
| `func` | Functional entity (e.g., "cost", "scust") - creates master table |
| `fkey` | Primary key for functional entity |
| `fcol` | Associated field in master table if different |
| `dtype` | Data type (numeric, text, date) |
| `appcol` | App-facing column name (e.g., "customer", "item") |
| `pretty` | User-friendly display name |

**Example target_meta row:**
```sql
| fc      | live    | fb_cst_loc     | 91   | cost         |              | fb_cst_loc     | numeric |              |        |
```

### appcols Requirements

| Column | Type | Required | Default |
|--------|------|----------|---------|
| value | numeric | yes | - |
| cost | numeric | yes | 0 |
| units | numeric | yes | 0 |
| order_date | date | yes | - |
| ship_date | date | no | - |
| order_status | text | yes | CLOSED |
| version | text | yes | ACTUALS |
| iter | text | yes | ACTUALS |
| logid | bigint | yes | - |
| tag | text | yes | - |
| comment | text | yes | - |
| customer | text | no | - |
| item | text | no | - |

## Setup

### 1. Configure Environment
```bash
cp .env-sample .env
# Edit .env with your PostgreSQL credentials
```

### 2. Run Database Setup
```bash
psql -h $host -U $user -d $database -f setup_sql/01_schema.sql
psql -h $host -U $user -d $database -f setup_sql/02_target_info.sql
psql -h $host -U $user -d $database -f setup_sql/03_build_master_tables.sql
```

### 3. Populate target_meta
Edit `target_meta` with the column mappings for your data:

```sql
UPDATE fc.target_meta SET func = 'cost', fkey = '', appcol = 'cost' WHERE cname = 'fb_cst_loc';
UPDATE fc.target_meta SET func = 'scust', fkey = 'ship_cust', appcol = 'customer' WHERE cname = 'ship_cust';
-- etc.
```

### 4. Generate Route SQL
```bash
./routes/baseline/generate_route_sql.sh
./routes/scale/generate_route_sql.sh
```

### 5. Start Server
```bash
node index.js
# Server runs on port in .env (default 3000)
```

## API Routes

### GET /baseline
Generate forecast baseline - mirrors historical data for forecast periods.

**Request body:**
```json
{
  "app_forecast_name": "Q1_2024",
  "app_forecast_version": "v1",
  "app_scenario": {
    "drange": "[2024-01-01,2024-03-31)"
  }
}
```

**Example curl:**
```bash
curl -X GET http://localhost:3000/baseline \
  -H "Content-Type: application/json" \
  -d '{"app_forecast_name":"Q1_2024","app_forecast_version":"v1","app_scenario":{"drange":"[2024-01-01,2024-03-31)"}}'
```

### GET /scale
Scale a selected data slice by specified amounts.

**Request body:**
```json
{
  "app_forecast_name": "Q1_2024_scaled",
  "app_where": "part = 'XFRM500'",
  "app_scenario": {
    "scust": "Sanford and Son"
  }
}
```

**Example curl:**
```bash
curl -X GET http://localhost:3000/scale \
  -H "Content-Type: application/json" \
  -d '{"app_forecast_name":"Q1_2024_scaled","app_scenario":{"scust":"Sanford and Son"}}'
```

## Common Tasks

### Add a new forecast dimension
1. Add column mapping to `fc.target_meta`:
   ```sql
   INSERT INTO fc.target_meta (schema, tname, cname, opos, func, fkey, fcol, dtype, appcol)
   VALUES ('fc', 'dcard', 'new_column', 99, 'new_func', 'new_column', 'new_column', 'text', 'new_dim');
   ```
2. Run `./routes/baseline/generate_route_sql.sh`
3. Run `./routes/scale/generate_route_sql.sh`

### Adjust forecast for a specific customer
```bash
curl -X GET http://localhost:3000/scale \
  -H "Content-Type: application/json" \
  -d '{
    "app_forecast_name": "Adjusted_Forecast",
    "app_scenario": {
      "scust": ["Customer A", "Customer B"]
    }
  }'
```

### View forecast logs
```sql
SELECT * FROM fc.log ORDER BY id DESC LIMIT 10;
```

### Add new fiscal periods
Insert into `fc.perd` with columns: comp, fsyr, perds, perd, fspr, sdat, edat, drange, capr, ndays, ssyr, sspr

## Known Issues

- [ ] SQL stored in files instead of database table
- [ ] Null handling in func tables (orders without quotes)
- [ ] Multi-table target_meta support
- [ ] Currency adjustment handling (which currency?)

## Testing

No automated test suite. Manual testing via curl commands in `routes/*/req.json` examples.