# Local PostgreSQL Database Setup

This guide will help you set up a local PostgreSQL database for development when you can't use the remote Supabase database.

## Step 1: Install PostgreSQL

### Windows

1. **Download PostgreSQL:**
   - Go to https://www.postgresql.org/download/windows/
   - Download the installer from EnterpriseDB
   - Run the installer

2. **During installation:**
   - Remember the password you set for the `postgres` user
   - Default port is `5432` (keep this)
   - Install pgAdmin if you want a GUI (optional)

3. **Verify installation:**
   ```bash
   psql --version
   ```

### macOS

```bash
# Using Homebrew
brew install postgresql@15
brew services start postgresql@15

# Or download from: https://www.postgresql.org/download/macosx/
```

### Linux (Ubuntu/Debian)

```bash
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql
```

## Step 2: Configure Local Database

1. **Create `.env` file in `backend/` directory:**

```env
# Local PostgreSQL Configuration
LOCAL_DB_HOST=localhost
LOCAL_DB_PORT=5432
LOCAL_DB_USER=postgres
LOCAL_DB_PASSWORD=your_postgres_password
LOCAL_DB_NAME=institutional_trader

# Use local database (set this to use local instead of remote)
USE_LOCAL_DB=true

# Server Configuration
PORT=3010
NODE_ENV=development

# CORS Configuration
CORS_ORIGIN=http://localhost:5173
```

**Important:** Replace `your_postgres_password` with the password you set during PostgreSQL installation.

## Step 3: Set Up Database and Tables

Run the setup script:

```bash
cd backend
npm run setup-local-db
```

This will:
- ✅ Create the database `institutional_trader`
- ✅ Create all necessary tables
- ✅ Create indexes for performance
- ✅ Test the connection

## Step 4: Import CSV Files

Once the database is set up, import your CSV files:

```bash
npm run import-csv
```

This will import:
- `OptionsFlow.csv` → `OptionsFlow_monthly` table
- `OptionsVolume.csv` → `OptionsVolume` table
- `OptionsOpenInterest.csv` → `OptionsOpenInterest` table

## Step 5: Update Database Connection

The system will automatically use the local database when `USE_LOCAL_DB=true` is set in your `.env` file.

## Troubleshooting

### Error: "Cannot connect to PostgreSQL server"

1. **Check if PostgreSQL is running:**
   ```bash
   # Windows
   # Check Services (services.msc) for "postgresql" service
   
   # macOS/Linux
   sudo systemctl status postgresql
   # or
   brew services list
   ```

2. **Start PostgreSQL if not running:**
   ```bash
   # Windows: Start from Services
   # macOS
   brew services start postgresql@15
   # Linux
   sudo systemctl start postgresql
   ```

3. **Verify connection:**
   ```bash
   psql -U postgres -h localhost
   ```

### Error: "password authentication failed"

1. Check your `.env` file has the correct password
2. Try resetting the postgres password:
   ```bash
   # Windows: Use pgAdmin or reset via Services
   # macOS/Linux
   sudo -u postgres psql
   ALTER USER postgres PASSWORD 'newpassword';
   ```

### Error: "database does not exist"

Run the setup script again:
```bash
npm run setup-local-db
```

### Error: "permission denied"

On Linux/macOS, you might need to:
```bash
sudo -u postgres createdb institutional_trader
```

Or create a new user:
```bash
sudo -u postgres createuser -s your_username
```

## Using pgAdmin (Optional GUI)

1. Open pgAdmin (installed with PostgreSQL)
2. Connect to local server (password: your postgres password)
3. You can view/edit data, run queries, etc.

## Verify Setup

After setup, verify everything works:

```bash
# Test database connection
cd backend
npm run setup-local-db

# Import CSV files
npm run import-csv

# Start server
npm run dev
```

You should see:
```
✅ Database connection successful (PostgreSQL direct)
```

## Switching Between Local and Remote

To switch between local and remote databases:

**Use Local Database:**
```env
USE_LOCAL_DB=true
LOCAL_DB_HOST=localhost
LOCAL_DB_PORT=5432
LOCAL_DB_USER=postgres
LOCAL_DB_PASSWORD=your_password
LOCAL_DB_NAME=institutional_trader
```

**Use Remote Database:**
```env
USE_LOCAL_DB=false
DATABASE_URL=postgresql://postgres:password@host:5432/database
# OR
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_KEY=your_key
```

## Next Steps

1. ✅ PostgreSQL installed and running
2. ✅ Database created and tables set up
3. ✅ CSV files imported
4. ✅ Server running with local database

You're ready to develop locally! 🎉