# POTE Quick Start Guide

## 🚀 Your System is Ready!

**Container IP**: Check with `ip addr show eth0 | grep "inet"`  
**Database**: PostgreSQL on port 5432  
**Username**: `poteuser`  
**Password**: `changeme123` (⚠️ change in production!)

---

## 📊 How to Use POTE

### Option 1: Command Line (SSH into container)

```bash
# SSH to your container
ssh root@YOUR_CONTAINER_IP

# Switch to poteapp user
su - poteapp

# Activate Python environment
cd pote && source venv/bin/activate

# Now you can run any POTE command!
```

### Option 2: Remote Database Access

Connect from any machine with PostgreSQL client:

```bash
psql -h YOUR_CONTAINER_IP -U poteuser -d pote
# Password: changeme123
```

### Option 3: Python Client (From Anywhere)

```python
from sqlalchemy import create_engine, text

# Connect remotely
engine = create_engine("postgresql://poteuser:changeme123@YOUR_CONTAINER_IP:5432/pote")

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM officials"))
    for row in result:
        print(row)
```

---

## 🎯 Common Tasks

### 1. Check System Status

```bash
su - poteapp
cd pote && source venv/bin/activate
~/status.sh  # Shows current database stats
```

### 2. Ingest Sample Data (Offline)

```bash
su - poteapp
cd pote && source venv/bin/activate
python scripts/ingest_from_fixtures.py
```

**Output**: Ingests 5 sample congressional trades (Nancy Pelosi, etc.)

### 3. Fetch Live Congressional Trades

```bash
su - poteapp
cd pote && source venv/bin/activate
python scripts/fetch_congressional_trades.py
```

**What it does**:
- Fetches latest trades from House Stock Watcher API
- Deduplicates against existing trades
- Shows summary of what was added

### 4. Enrich Securities (Add Company Info)

```bash
su - poteapp
cd pote && source venv/bin/activate
python scripts/enrich_securities.py
```

**What it does**:
- Fetches company names, sectors, industries from yfinance
- Updates securities table with real company data

### 5. Fetch Historical Prices

```bash
su - poteapp
cd pote && source venv/bin/activate
python scripts/fetch_sample_prices.py
```

**What it does**:
- Fetches historical price data for securities in database
- Stores daily OHLCV data for analysis

### 6. Run Database Queries

```bash
# Connect to database
psql -h localhost -U poteuser -d pote
```

**Useful queries**:

```sql
-- View all officials
SELECT name, chamber, party FROM officials;

-- View all trades
SELECT o.name, s.ticker, t.side, t.amount_min, t.transaction_date
FROM trades t
JOIN officials o ON t.official_id = o.id
JOIN securities s ON t.security_id = s.id
ORDER BY t.transaction_date DESC;

-- Top traders
SELECT o.name, COUNT(t.id) as trade_count
FROM officials o
LEFT JOIN trades t ON o.id = t.official_id
GROUP BY o.id, o.name
ORDER BY trade_count DESC;

-- Trades by ticker
SELECT s.ticker, s.name, COUNT(t.id) as trade_count
FROM securities s
LEFT JOIN trades t ON s.id = t.security_id
GROUP BY s.id, s.ticker, s.name
ORDER BY trade_count DESC;
```

---

## 📈 Example Workflows

### Workflow 1: Daily Update

```bash
su - poteapp
cd pote && source venv/bin/activate

# Fetch new trades
python scripts/fetch_congressional_trades.py

# Enrich any new securities
python scripts/enrich_securities.py

# Update prices
python scripts/fetch_sample_prices.py

# Check status
~/status.sh
```

### Workflow 2: Research Query

```python
# research.py - Save this in ~/pote/
from sqlalchemy import create_engine, text
from pote.config import settings

engine = create_engine(settings.DATABASE_URL)

# Find all NVDA trades
with engine.connect() as conn:
    result = conn.execute(text("""
        SELECT 
            o.name,
            o.party,
            t.side,
            t.amount_min,
            t.transaction_date,
            t.disclosure_date
        FROM trades t
        JOIN officials o ON t.official_id = o.id
        JOIN securities s ON t.security_id = s.id
        WHERE s.ticker = 'NVDA'
        ORDER BY t.transaction_date DESC
    """))
    
    for row in result:
        print(f"{row.name:20s} | {row.side:8s} | ${row.amount_min:,} | {row.transaction_date}")
```

Run it:
```bash
python research.py
```

### Workflow 3: Export to CSV

```python
# export_trades.py
import pandas as pd
from sqlalchemy import create_engine
from pote.config import settings

engine = create_engine(settings.DATABASE_URL)

# Export all trades to CSV
query = """
    SELECT 
        o.name as official_name,
        o.party,
        o.chamber,
        s.ticker,
        s.name as company_name,
        t.side,
        t.amount_min,
        t.amount_max,
        t.transaction_date,
        t.disclosure_date
    FROM trades t
    JOIN officials o ON t.official_id = o.id
    JOIN securities s ON t.security_id = s.id
    ORDER BY t.transaction_date DESC
"""

df = pd.read_sql(query, engine)
df.to_csv('trades_export.csv', index=False)
print(f"Exported {len(df)} trades to trades_export.csv")
```

---

## 🔧 Maintenance

### Update POTE Code

```bash
su - poteapp
cd pote
git pull
source venv/bin/activate
pip install -e .
```

### Backup Database

```bash
# Create backup
su - poteapp
pg_dump -h localhost -U poteuser pote > ~/backups/pote_$(date +%Y%m%d).sql

# Restore backup
psql -h localhost -U poteuser -d pote < ~/backups/pote_20250115.sql
```

### View Logs

```bash
# PostgreSQL logs
tail -f /var/log/postgresql/postgresql-15-main.log

# Application logs (if you create them)
tail -f ~/logs/pote.log
```

### Change Database Password

```bash
# As root
su - postgres
psql << EOF
ALTER USER poteuser WITH PASSWORD 'your_new_secure_password';
EOF

# Update .env
su - poteapp
nano ~/pote/.env
# Change DATABASE_URL password
```

---

## 🌐 Access Methods Summary

| Method | From Where | Command |
|--------|-----------|---------|
| **SSH + CLI** | Any network client | `ssh root@IP`, then `su - poteapp` |
| **psql** | Any network client | `psql -h IP -U poteuser -d pote` |
| **Python** | Any machine | `sqlalchemy.create_engine("postgresql://...")` |
| **Web UI** | Coming in Phase 3! | `http://IP:8000` (FastAPI + dashboard) |

---

## 📚 What Data Do You Have?

Right now (Phase 1 complete):
- ✅ **Congressional trading data** (from House Stock Watcher)
- ✅ **Security information** (tickers, names, sectors)
- ✅ **Historical prices** (OHLCV data from yfinance)
- ✅ **Official profiles** (name, party, chamber, state)

Coming next (Phase 2):
- 📊 **Abnormal return calculations**
- 🤖 **Behavioral clustering**
- 🚨 **Research signals** (follow_research, avoid_risk, watch)

---

## 🎓 Learning SQL for POTE

### Count Records
```sql
SELECT COUNT(*) FROM officials;
SELECT COUNT(*) FROM trades;
SELECT COUNT(*) FROM securities;
```

### Filter by Party
```sql
SELECT name, party FROM officials WHERE party = 'Democrat';
```

### Join Tables
```sql
SELECT o.name, s.ticker, t.side
FROM trades t
JOIN officials o ON t.official_id = o.id
JOIN securities s ON t.security_id = s.id
LIMIT 10;
```

### Aggregate Stats
```sql
SELECT 
    o.party,
    COUNT(t.id) as trade_count,
    AVG(t.amount_min) as avg_amount
FROM trades t
JOIN officials o ON t.official_id = o.id
GROUP BY o.party;
```

---

## ❓ Troubleshooting

### Can't connect remotely?
```bash
# Check PostgreSQL is listening
ss -tlnp | grep 5432
# Should show: 0.0.0.0:5432

# Check firewall (if enabled)
ufw status
```

### Database connection fails?
```bash
# Test locally first
psql -h localhost -U poteuser -d pote

# Check credentials in .env
cat ~/pote/.env
```

### Python import errors?
```bash
# Reinstall dependencies
su - poteapp
cd pote && source venv/bin/activate
pip install -e .
```

---

## 🚀 Next Steps

1. **Populate with real data**: Run `fetch_congressional_trades.py` regularly
2. **Set up cron job** for automatic daily updates
3. **Build analytics** (Phase 2) - abnormal returns, signals
4. **Create dashboard** (Phase 3) - web interface for exploration

Ready to build Phase 2 analytics? Just ask! 📈