# CSV Import Guide

This guide explains how to import your CSV files (OptionsFlow.csv, OptionsVolume.csv, OptionsOpenInterest.csv) into the database.

## Prerequisites

1. **Install dependencies:**
   ```bash
   cd backend
   npm install
   ```

2. **Ensure database connection is configured:**
   - Your `.env` file should have `DATABASE_URL` or Supabase credentials
   - See `SETUP_ENV.md` for configuration details

3. **Place CSV files in project root:**
   - `OptionsFlow.csv`
   - `OptionsVolume.csv`
   - `OptionsOpenInterest.csv`

## Import Process

### Option 1: Command Line (Recommended)

Run the import script:

```bash
cd backend
npm run import-csv
```

This will:
- ✅ Read all three CSV files
- ✅ Create tables if they don't exist
- ✅ Import data in batches (1000 records at a time)
- ✅ Show progress and summary

### Option 2: Programmatic Import

You can also import programmatically:

```javascript
import { importOptionsFlow, importOptionsVolume, importOptionsOpenInterest } from './scripts/importCSV.js';

// Import each file
await importOptionsFlow('./OptionsFlow.csv');
await importOptionsVolume('./OptionsVolume.csv');
await importOptionsOpenInterest('./OptionsOpenInterest.csv');
```

## What Gets Imported

### OptionsFlow.csv → `OptionsFlow_monthly` table

Columns imported:
- CreatedDate, CreatedTime
- Symbol, Type, Volume, Price
- Side, CallPut, Strike, Spot
- Premium, ExpirationDate
- Color, ImpliedVolatility, Dte, ER
- StockEtf, Sector, Uoa, Weekly, MktCap, OI

**Note:** This matches the table structure expected by your SQL queries.

### OptionsVolume.csv → `OptionsVolume` table

Columns imported:
- SYMBOL, VOL, C VOL, P VOL
- P/C, AVG VOL, RATIO
- COI, POI, DCOI, DPOI

### OptionsOpenInterest.csv → `OptionsOpenInterest` table

Columns imported:
- SYMBOL, EXP, C/P
- STRIKE, OI, DOI

## Troubleshooting

### Error: "Database connection failed"

1. Check your `.env` file has correct `DATABASE_URL`
2. Verify database is accessible (not paused/restricted)
3. See `TROUBLESHOOTING.md` for connection issues

### Error: "CSV file not found"

1. Ensure CSV files are in the project root directory (same level as `backend/` folder)
2. Check file names are exactly: `OptionsFlow.csv`, `OptionsVolume.csv`, `OptionsOpenInterest.csv`
3. Verify files are readable (not corrupted)

### Error: "Table already exists"

This is normal - the script uses `CREATE TABLE IF NOT EXISTS`, so existing tables won't cause errors.

### Error: "Invalid numeric value"

The script automatically cleans numeric values (removes commas, dollar signs, etc.). If you see this error:
1. Check the CSV file for corrupted data
2. Verify numeric columns contain valid numbers
3. Empty values are handled as NULL

### Import is slow

- The script imports in batches of 1000 records
- Large files may take several minutes
- Progress is shown during import

## Data Validation

After import, verify the data:

```sql
-- Check OptionsFlow_monthly
SELECT COUNT(*) FROM "OptionsFlow_monthly";
SELECT "Symbol", COUNT(*) 
FROM "OptionsFlow_monthly" 
GROUP BY "Symbol" 
ORDER BY COUNT(*) DESC 
LIMIT 10;

-- Check OptionsVolume
SELECT COUNT(*) FROM "OptionsVolume";
SELECT * FROM "OptionsVolume" LIMIT 10;

-- Check OptionsOpenInterest
SELECT COUNT(*) FROM "OptionsOpenInterest";
SELECT * FROM "OptionsOpenInterest" LIMIT 10;
```

## Next Steps

After importing:
1. ✅ Verify data counts match CSV file row counts
2. ✅ Test your API endpoints (e.g., `/api/options/flow`)
3. ✅ Check that queries work with the imported data

## Notes

- **Data is appended** - Running the import multiple times will add duplicate records
- **No deduplication** - If you need to avoid duplicates, truncate tables first:
  ```sql
  TRUNCATE TABLE "OptionsFlow_monthly";
  TRUNCATE TABLE "OptionsVolume";
  TRUNCATE TABLE "OptionsOpenInterest";
  ```
- **Large files** - Files with 10,000+ records may take 1-2 minutes to import
- **Memory usage** - The script processes data in batches to minimize memory usage