`DATA_STRUCTURE.md`

246 lines
# Japanese Handbag Arbitrage - Data Structure

## Spreadsheet Export Fields

### Main Listing Data
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **id** | INTEGER | Unique listing ID | 1234 |
| **external_id** | TEXT | Original site's product ID | "kmh_987654" |
| **source** | TEXT | Japanese marketplace source | "komehyo", "yahoo_auctions_jp", "mercari_jp" |
| **brand** | TEXT | Handbag brand | "Gucci", "Louis Vuitton", "Chanel" |
| **title** | TEXT | Full product title | "Gucci GG Marmont Matelasse Shoulder Bag Black" |
| **model** | TEXT | Specific model name | "GG Marmont" |
| **listing_type** | TEXT | Type of sale | "auction", "buy", "both" |

### Pricing Data
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **price_jpy** | REAL | Japanese price in Yen | 89000 |
| **price_usd** | REAL | Converted USD price | 593.33 |
| **currency** | TEXT | Original currency | "JPY" |
| **avg_us_price** | REAL | Average US market price | 1200.00 |
| **min_us_price** | REAL | Minimum US market price | 950.00 |
| **max_us_price** | REAL | Maximum US market price | 1500.00 |

### Deal Analysis
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **deal_percentage** | REAL | Discount vs US market | 50.6 |
| **is_deal** | INTEGER | Deal flag (1=yes, 0=no) | 1 |
| **confidence_score** | REAL | AI confidence (0-1) | 0.85 |
| **potential_profit** | REAL | Estimated profit (USD) | 606.67 |

### Product Details
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **condition** | TEXT | Item condition | "Excellent", "Good", "Used", "Like New" |
| **description** | TEXT | Product description | Full text description |
| **image_url** | TEXT | Full size image URL | "https://..." |
| **thumbnail_url** | TEXT | Thumbnail image URL | "https://..." |
| **product_url** | TEXT | Link to original listing | "https://komehyo.jp/..." |

### Auction Details (if applicable)
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **auction_end_date** | TEXT | When auction ends | "2025-11-10 15:30:00" |
| **location** | TEXT | Item location | "Tokyo, Japan" |
| **seller_name** | TEXT | Seller username | "komehyo_official" |

### AI Analysis Notes
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **ai_notes** | TEXT | AI reasoning for price | "Similar Gucci Marmont bags on The RealReal sell for $1,100-$1,400. Excellent condition justifies higher end of range." |

### US Market Comparisons
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **us_source** | TEXT | US comparison site | "ebay", "therealreal", "vestiaire", "fashionphile" |
| **us_price_usd** | REAL | Comparable US listing price | 1195.00 |
| **us_url** | TEXT | Link to US comparable | "https://..." |
| **us_condition** | TEXT | US listing condition | "Excellent" |

### Metadata
| Field | Type | Description | Example |
|-------|------|-------------|---------|
| **crawled_at** | TEXT | When item was found | "2025-11-08 14:30:00" |
| **analysis_date** | TEXT | When AI analyzed it | "2025-11-08 15:00:00" |
| **is_active** | INTEGER | Still available (1=yes) | 1 |

---

## Sample Data Rows

### Example 1: High-Value Deal
```
id: 1
brand: Gucci
title: Gucci GG Marmont Matelasse Shoulder Bag Black Leather
source: komehyo
listing_type: buy
price_jpy: 89000
price_usd: 593.33
avg_us_price: 1200.00
deal_percentage: 50.6%
is_deal: 1
condition: Excellent
confidence_score: 0.85
potential_profit: $606.67
ai_notes: Similar bags on The RealReal: $1,100-$1,400. This is authentic and excellent condition.
product_url: https://komehyo.jp/product/12345
```

### Example 2: Auction Item
```
id: 2
brand: Louis Vuitton
title: Louis Vuitton Neverfull MM Monogram Tote Bag
source: yahoo_auctions_jp
listing_type: auction
price_jpy: 45000
price_usd: 300.00
avg_us_price: 800.00
deal_percentage: 62.5%
is_deal: 1
condition: Good
auction_end_date: 2025-11-09 18:00:00
confidence_score: 0.78
potential_profit: $500.00
ai_notes: Neverfull MM typically sells for $700-900 on Fashionphile. Monitor auction closely.
```

### Example 3: Hermes Premium
```
id: 3
brand: Hermes
title: Hermes Birkin 30 Black Togo Leather Gold Hardware
source: komehyo
listing_type: buy
price_jpy: 1850000
price_usd: 12333.33
avg_us_price: 18000.00
deal_percentage: 31.5%
is_deal: 1
condition: Excellent
confidence_score: 0.92
potential_profit: $5,666.67
ai_notes: Birkin 30 Black Togo with GHW is highly sought. US market: $16K-$20K depending on year.
```

---

## Export Formats Available

### CSV Export
- Standard comma-separated values
- All fields included
- UTF-8 encoding
- Compatible with Excel, Google Sheets

### Excel Export (Future)
- Multiple sheets: Deals, All Listings, Statistics
- Formatted columns
- Conditional formatting for deal percentages

### JSON Export
- Full data structure with nested US comparisons
- API-ready format
- Includes metadata

---

## Filtering Options

### Available Filters
1. **Brand**: Filter by specific brand (partial match)
2. **Price Range**: Min/Max USD price
3. **Deal Percentage**: Minimum discount required
4. **Listing Type**: Auction only, Buy only, or Both
5. **Deals Only**: Show only items flagged as deals (20%+ off by default)
6. **Source**: Filter by Japanese marketplace
7. **Condition**: Filter by item condition

### Sorting Options
1. **Best Deal**: Highest deal percentage first
2. **Price (Low to High)**: Cheapest items first
3. **Price (High to Low)**: Most expensive items first
4. **Most Recent**: Latest crawled items first
5. **Type**: Group by auction vs buy now

---

## Deal Detection Criteria

An item is flagged as a **DEAL** when:
- Deal percentage ≥ 20% (configurable in .env)
- AI confidence score ≥ 0.6
- At least 2 US market comparisons found
- Authentic brand verification passed

### Deal Tiers
- 🔥 **Good Deal**: 20-30% below market
- 🔥🔥 **Great Deal**: 30-50% below market
- 🔥🔥🔥 **Exceptional Deal**: 50%+ below market

---

## API Endpoints for Data Access

### Get Listings
```
GET /api/listings?brand=gucci&minDeal=30&sort=deal&limit=100
```

### Get Best Deals
```
GET /api/deals?minDeal=40
```

### Export to CSV
```
GET /api/export/csv?dealOnly=true&minDeal=30
```

### Get Statistics
```
GET /api/stats
```

### Get Single Item
```
GET /api/listing/:id
```

---

## Sample SQL Queries

### Top 10 Deals
```sql
SELECT brand, title, price_usd, avg_us_price, deal_percentage
FROM listings l
JOIN deal_analysis d ON l.id = d.listing_id
WHERE is_deal = 1
ORDER BY deal_percentage DESC
LIMIT 10;
```

### Deals by Brand
```sql
SELECT brand, COUNT(*) as deal_count, AVG(deal_percentage) as avg_discount
FROM listings l
JOIN deal_analysis d ON l.id = d.listing_id
WHERE is_deal = 1
GROUP BY brand
ORDER BY deal_count DESC;
```

### Auction vs Buy Now Analysis
```sql
SELECT listing_type, COUNT(*) as count, AVG(deal_percentage) as avg_deal
FROM listings l
JOIN deal_analysis d ON l.id = d.listing_id
WHERE is_deal = 1
GROUP BY listing_type;
```