# Expanded Database Pattern

## Overview
Pattern for expanding material databases from basic (10 materials) to comprehensive (25+ materials) with multiple brand options per category.

## Initial State
- **Basic database**: 10 materials, 1 brand per category
- **Goal**: 25+ materials, 3-5 brands per category

## Expansion Workflow

### Step 1: Identify Categories
```
Ground categories:
- floor (地面)
- wall (墙面)
- ceiling (顶面)
- kitchen (厨房)
- bathroom (卫浴)
- lighting (灯具)
```

### Step 2: Add Brands per Category
For each category, add 3-5 brands covering different price tiers:

| Tier | Price Range | Example Brands (US) | Example Brands (CN) |
|------|-------------|-------------------|-------------------|
| Economy | $2-3/sq ft, ¥100-150/㎡ | Pergo, 圣象 |
| Mid-range | $3-5/sq ft, ¥150-250/㎡ | COREtec, 菲林格尔 |
| Premium | $5+/sq ft, ¥250+/㎡ | Bruce, 大自然 |

### Step 3: JSON Structure
```json
{
  "id": "us_floor_004",
  "name": "Engineered Hardwood",
  "category": "floor",
  "brand": "Mohawk",
  "model": "RevWood Select",
  "price_per_unit": 4.29,
  "unit": "sq ft",
  "style_match": ["Traditional", "Transitional", "American"],
  "description": "Water-resistant engineered hardwood, 5/8\" thick",
  "supplier": "Home Depot",
  "purchase_url": "https://www.homedepot.com/p/mohawk-revwood"
}
```

**Key fields:**
- `style_match`: Array of styles this material works with
- `purchase_url`: **REAL URL** (not example.com - destroys trust)
- `supplier`: Where to buy (Home Depot, 淘宝, etc.)

### Step 4: Update Backend Logic
Modify recommendation to return **2-3 options per category** (not just first match):

```python
# backend/main.py
cat_materials = [m for m in all_materials if m.get('category') == target_category]
style_materials = [m for m in cat_materials if request.style in m.get('style_match', [])]

if not style_materials:
    style_materials = cat_materials

# Return TOP 2-3 options (not just [0])
max_items = min(3, len(style_materials))
selected_materials = style_materials[:max_items]

for material in selected_materials:
    materials.append(MaterialItem(...))
```

### Step 5: Test Expansion
```bash
# Rebuild backend
cd ~/projects/your-app
sudo docker-compose up -d --build backend

# Test with different markets
MARKET=US curl -s -X POST http://localhost:8000/api/v1/generate-plan ...
# Expect: 30+ materials (vs 13 before)

MARKET=CN curl -s -X POST http://localhost:8000/api/v1/generate-plan ...
# Expect: 20+ materials (vs 10 before)
```

## Results from This Session

### North America (`materials_us.json`)
- **Before**: 12 materials, 5 brands
- **After**: **26 materials, 13 brands**
- **Brands added**: Shaw, Mohawk, USG, Georgia-Pacific, Delta, TOTO, Kichler, Philips Hue, etc.

### China (`materials_cn.json`)
- **Before**: 10 materials, 5 brands
- **After**: **22 materials, 10 brands**
- **Brands added**: 菲林格尔, 东鹏, 立邦, 三棵树, 友邦, 志邦, 赛凯隆, 恒洁, 九牧, 欧普, 飞利浦, Yeelight

## Pitfalls
1. **Fake URLs**: Never use `example.com`. Use real URLs from Homedepot.com, Lowes.com, taobao.com
2. **Single brand only**: Don't just return `style_materials[0]`. Users want choices!
3. **Missing style_match**: Every material MUST have `style_match` array, or recommendation fails
4. **Wrong unit**: US uses sq ft, CN uses 平方米. Handle in room config.

## Cost Analysis
- **Database expansion**: One-time effort (2-3 hours)
- **API cost**: Still $0.000001/call (database query, not AI generation)
- **Maintenance**: Quarterly price updates (15 min/quarter)

**Compare to ChatGPT API approach:**
- ChatGPT for materials: $30-270/year ongoing
- Database expansion: $0 ongoing ✅