# Anvil Product Tables Implementation

## Overview

Anvil-branded cloud products with automated pricing sync from source providers (Linode, Vultr).

**Key Features:**
- USD retail pricing only (simplified schema)
- Automatic pricing sync: wholesale × 1.21 = retail
- Source mapping via `source_instance_id` and `source_region_id`

## Database Schema

### anvil_regions
Maps Anvil regions to source provider regions.

**Columns:**
- `id`, `name` (unique), `display_name`, `country_code`
- `source_provider` (linode, vultr)
- `source_region_code`, `source_region_id` (FK to `regions.id`)
- `active`, `created_at`, `updated_at`

**Region Mappings:**
| Anvil Region | Source Provider | Source Region |
|-------------|-----------------|---------------|
| anvil-tyo1 | Linode | ap-northeast |
| anvil-tyo2 | Linode | jp-tyo-3 |
| anvil-tyo3 | Vultr | nrt |
| anvil-osa1 | Linode | jp-osa |
| anvil-osa2 | Vultr | itm |
| anvil-sel1 | Vultr | icn |

### anvil_instances
Anvil product specifications.

**Columns:**
- `id`, `name` (unique), `display_name`, `category` (vm, gpu, g8, vpu)
- `vcpus`, `memory_gb`, `disk_gb`
- `transfer_tb`, `network_gbps`
- `gpu_model`, `gpu_vram_gb` (nullable, for GPU instances)
- `active`, `created_at`, `updated_at`

### anvil_pricing
Retail pricing in USD. Auto-synced from source provider pricing.

**Columns:**
- `id`, `anvil_instance_id` (FK), `anvil_region_id` (FK)
- `hourly_price`, `monthly_price` (retail USD)
- `source_instance_id` (FK to `instance_types.id`)
- `active`, `created_at`, `updated_at`
- UNIQUE constraint on (anvil_instance_id, anvil_region_id)

### anvil_transfer_pricing
Data transfer pricing per region.

**Columns:**
- `id`, `anvil_region_id` (FK)
- `price_per_gb` (USD)
- `included_tb` (reference only)
- `active`, `created_at`, `updated_at`
- UNIQUE constraint on anvil_region_id

## Pricing Sync Flow

Provider sync automatically updates Anvil pricing via `syncAnvilPricing()`.

### Flow Diagram
```
Provider API → pricing table (wholesale USD) → anvil_pricing (retail USD)
                    ↓
              syncProvider()
                    ↓
              syncAnvilPricing()
                    ↓
        retail = wholesale × 1.21
```

### Retail Markup Calculation
```typescript
// src/constants.ts
export const USD_RETAIL_DEFAULTS = {
  MARGIN_MULTIPLIER: 1.1,   // 10% margin
  VAT_MULTIPLIER: 1.1,      // 10% VAT
  TOTAL_MULTIPLIER: 1.21,   // Combined
};

// Hourly: rounded to 4 decimal places
calculateRetailHourly(0.0075) // → 0.0091

// Monthly: rounded to nearest $1
calculateRetailMonthly(5)     // → 6
```

### Source Mapping
`anvil_pricing` links to source data via:
- `source_instance_id` → `instance_types.id`
- `anvil_region_id` → `anvil_regions.source_region_id` → `regions.id`

When sync runs, it:
1. Finds anvil_pricing records with `source_instance_id` set
2. Looks up wholesale price from `pricing` table using source_instance_id + source_region_id
3. Calculates retail price (×1.21)
4. Updates anvil_pricing with new retail prices

## API Authentication

API key stored in Vault:
```
Path: secret/data/cloud-instances-api
Key:  api_key
```

Set wrangler secret:
```bash
wrangler secret put API_KEY
```

## Database Relationships

```
providers (1) ──< instance_types (M) ──< pricing (M)
                         ↑                    ↑
              source_instance_id      source_region_id
                         ↓                    ↓
anvil_instances (1) ──< anvil_pricing (M) >── anvil_regions (1)
```

## Deployment Status

✅ Migration 004 applied to remote database
✅ 6 Anvil regions configured
✅ Source instance mappings for tyo1, tyo2, tyo3, osa1, osa2, sel1
✅ Auto-sync working: retail = wholesale × 1.21

## Usage Examples

### Trigger Sync
```bash
curl -X POST "https://cloud-instances-api.kappa-d8e.workers.dev/sync" \
  -H "X-API-Key: YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"providers": ["linode", "vultr"]}'
```

### Query Anvil Pricing
```typescript
const repos = new RepositoryFactory(env.DB, env);

// Get pricing for a region
const pricing = await repos.anvilPricing.findByRegion(regionId);

// Get pricing for an instance
const instancePricing = await repos.anvilPricing.findByInstance(instanceId);
```

### Add Source Mapping
```sql
-- Map anvil_pricing to source instance
UPDATE anvil_pricing
SET source_instance_id = (
  SELECT id FROM instance_types
  WHERE instance_id = 'vc2-1c-1gb' AND provider_id = 2
)
WHERE anvil_instance_id = 1 AND anvil_region_id = 3;
```

## Files

### Repositories
- `/src/repositories/anvil-regions.ts`
- `/src/repositories/anvil-instances.ts`
- `/src/repositories/anvil-pricing.ts`
- `/src/repositories/anvil-transfer-pricing.ts`

### Sync Service
- `/src/services/sync.ts` - Contains `syncAnvilPricing()` method

### Constants
- `/src/constants.ts` - `USD_RETAIL_DEFAULTS`, `calculateRetailHourly()`, `calculateRetailMonthly()`