# Trading Strategy Tester Development Plan

## Overview
This plan outlines an optimized development approach for implementing a trading strategy tester with entry point detection, database integration, and web interface.

## ⚠️ CRITICAL BUGS TO FIX (Found During Code Review)

### Bug 1: Missing `BacktestRequest` Pydantic Model
- **File:** `web/app.py` (lines 453, 486, 499)
- **Status:** BLOCKING — backtest endpoints are completely non-functional
- **Description:** The `BacktestRequest` model is referenced in three places but never defined. This causes an immediate `NameError` at import time.
- **Fix:** Add this model to `web/app.py`:
```python
class BacktestRequest(BaseModel):
    instrument: str = Field(default="BITCOIN", description="Instrument symbol")
    timeframe: str = Field(default="H1", description="Timeframe")
    candle_count: int = Field(default=100, ge=20, le=5000, description="Candles to backtest")
    initial_balance: float = Field(default=10000.0, gt=0)
    risk_percent: float = Field(default=1.0, ge=0.1, le=10)
    take_profit_multiplier: float = Field(default=3.0, ge=1, le=10)
    short_sma: int = Field(default=8, ge=3)
    long_sma: int = Field(default=21, ge=5)
    start_date: Optional[str] = Field(default=None, description="Start date (ISO 8601)")
    end_date: Optional[str] = Field(default=None, description="End date (ISO 8601)")
```

### Bug 2: Missing `calculate_position_size` Import
- **File:** `web/app.py` (line 673)
- **Status:** BLOCKING — causes NameError during backtest execution
- **Description:** `calculate_position_size` is called but not imported. Only `full_risk_analysis` and `calculate_atr` are imported from `risk_calculator`.
- **Fix:** Add to the import statement at line 30:
```python
from core.risk_calculator import RiskParameters, RiskResult, full_risk_analysis, calculate_atr, calculate_position_size
```

### Bug 3: Trade Log TP Column Shows Wrong Data
- **File:** `web/static/js/app.js` (line 513)
- **Status:** Data display bug
- **Description:** In `displayTradeLog`, the "TP" column displays `t.exit_price.toFixed(2)` instead of `t.take_profit?.toFixed(2)`. Both TP and SL columns show the exit price.
- **Fix:** Change line 513: `t.exit_price.toFixed(2)` → `t.take_profit?.toFixed(2) || '-'`

### Bug 4: CORS Configuration Violates Spec
- **File:** `web/app.py` (lines 54-59)
- **Status:** Potential browser compatibility issue
- **Description:** `allow_origins=["*"]` combined with `allow_credentials=True` violates CORS spec. Browsers reject credentialed requests with wildcard origin.
- **Fix:** Either set `allow_credentials=False` or specify explicit origins.

### Bug 5: Backtest Never Records Entry Time
- **File:** `web/app.py` (lines 579-588, 619-630)
- **Status:** Missing data in trade log
- **Description:** `TradeLogEntry` objects always have `entry_time=0`. Entry timestamp is never recorded when opening a position.
- **Fix:** Store `current.timestamp` as `entry_time` when opening a position.

### Bug 6: Overly Strict Candle Validation
- **File:** `data/preprocessing.py` (lines 45-51)
- **Status:** Data quality issue
- **Description:** Conditions like `high_price < open_price` reject candles where high equals open (e.g., doji candles or zero-shadow candles in low-volatility periods). Uses strict `<` and `>` instead of `<=` and `>=`.
- **Fix:** Change to allow `high >= max(open, close)` and `low <= min(open, close)`.

## Database Study and Analysis

### Step 0.1: Database Structure Investigation
Based on the provided database configuration:
```python
db_config = {
    'host': 'nlbotinterface.ru',
    'port': 3306,
    'database': 'bitcoin_tickers',
    'user': 'bitcoin',
    'password': 'g49020007'
}
```

**Expected Tables and Structure:**
- `candles_{INSTRUMENT}_{TF}`: Time-series data with columns: timestamp, Date, Time, Open, High, Low, Close, Volume
- `instruments`: Trading instruments with id, name, type

**Known Table Examples:**
- `BITCOIN_H1`, `BITCOIN_D1`, `BITCOIN_W1`, `BITCOIN_M15`, `BITCOIN_M5`

**Analysis Tasks:**
1. Document all tables and their schemas
2. Identify relationships between tables (foreign keys)
3. Analyze data volume and time range coverage
4. Identify indexing strategy for performance optimization
5. Document data frequency (1m, 5m, 1h candles)
6. Identify any existing computed fields or materialized views

### Step 0.2: Database Connection and Query Optimization
- Implement connection pooling using `pymysql` ✓ (already implemented)
- Develop optimized queries for candle data retrieval by time range ✓
- Create query patterns for:
  - Getting last N candles for an instrument ✓
  - Retrieving historical data with pagination
  - Aggregating data by different timeframes ✓
  - Joining candle data with trade/order information

## Phase 1: Core Architecture (Days 1-3)

### Step 1.1: Project Structure Setup
```
├── config/
│   ├── app_config.json     # Application settings ✓
│   ├── db_config.json       # Database configuration ✓
│   └── locales.py           # i18n translation engine (RU/EN) ✓
├── core/                    # Core logic ✓
│   ├── trend_analysis.py    # Trend detection algorithms
│   ├── entry_detection.py   # Entry point detection logic
│   ├── risk_calculator.py   # Stop loss/take profit calculation
│   └── candle_patterns.py   # Candle pattern recognition
├── data/                    # Data layer ✓
│   ├── db_connector.py      # Database connection and queries
│   ├── cache_manager.py     # Data caching for performance
│   └── preprocessing.py     # Data normalization and cleaning
├── models/                  # ML models (optional, require PyTorch) ✓
│   ├── trend_predictor.py   # Lightweight neural network for trend prediction
│   └── pattern_recognizer.py # Pattern recognition model
├── web/                     # Web interface ✓
│   ├── app.py               # FastAPI application
│   ├── templates/           # HTML templates
│   │   └── index.html       # Single-page application
│   └── static/              # CSS, JS, lightweight charts
│       ├── js/app.js        # Frontend application logic
│       └── css/style.css    # Dark theme stylesheet
├── tests/                   # Unit and integration tests ✓
│   ├── test_preprocessing.py
│   └── test_core_logic.py
└── DEVELOPMENT_PLAN.md
```

### Step 1.2: Core Strategy Logic Implementation
- ✅ Trend detection using last 24 candles (SMA crossover + RSI + slope)
- ✅ Candle pattern recognition for 10 patterns
- ✅ Entry point calculation (buy/sell signals with Fibonacci support)
- ✅ Risk management calculations (stop loss, take profit, position sizing, ATR)
- ✅ In-memory caching layer with TTL and LRU eviction

### Step 1.3: Additional Issues Found

**Issue: Broad Exception Handling in ML Imports**
- **File:** `web/app.py` (lines 35-39)
- **Description:** `except (ImportError, Exception)` catches all exceptions, masking unrelated errors. Should be narrowed to `ImportError`.

**Issue: No Database Error Handling in API Endpoints**
- **File:** `web/app.py` (multiple endpoints)
- **Description:** DB queries in endpoints have no try/except. Connection failures cause unhandled 500 errors.

**Issue: Cache Manager Has No Redis Backend**
- **File:** `data/cache_manager.py`
- **Description:** Implementation is in-memory only. Cache is lost on restart. The plan mentions Redis but it's not implemented.

**Issue: Frontend JS `val()` Function Bug**
- **File:** `web/static/js/app.js` (line 108)
- **Description:** `function val(id) { return $('id').value; }` uses string literal `'id'` instead of the variable `id`. Should be `return $(id).value;`.

**Issue: SQL Injection Risk in Table Name Construction**
- **File:** `data/db_connector.py` (lines 219-224)
- **Description:** Table names constructed via string formatting from user input. While a whitelist exists, instrument names are not sanitized.

**Issue: No Integration/API Tests**
- **Description:** Only unit tests exist. No tests for actual API endpoints, database integration, or full backtest pipeline.

## Phase 2: Data Layer (Days 4-6) — LARGELY COMPLETE

### Step 2.1: Database Schema Documentation and Query Patterns ✓
**Key Queries Implemented:**
```sql
-- Get last N candles (DESC)
SELECT * FROM {table} ORDER BY timestamp DESC LIMIT ?;

-- Get candle range (ASC)
SELECT * FROM {table} WHERE timestamp >= ? AND timestamp <= ? ORDER BY timestamp ASC;

-- Last N hours
SELECT * FROM {table} WHERE timestamp >= ? ORDER BY timestamp ASC;

-- Range stats
SELECT MIN(timestamp), MAX(timestamp), COUNT(*), MIN(Low), MAX(High) FROM {table};

-- Aggregation/resampling
SELECT FLOOR(timestamp/?) * ? as period_start, MIN(Open), MAX(High), MIN(Low), MAX(Close), SUM(Volume)
FROM {table} WHERE timestamp >= ? AND timestamp <= ?
GROUP BY period_start ORDER BY period_start ASC;
```

### Step 2.2: Database Integration ✓
- ✅ Connection pool implementation (`ConnectionPool` class)
- ✅ In-memory cache (`CacheManager` class)
- ✅ Query optimization
- ✅ Data preprocessing pipeline

### Step 2.3: Lightweight Neural Network Models ✓ (optional)
- ✅ `TrendPredictor` — CNN with ~10k parameters for trend classification
- ✅ `PatternRecognizer` — Hybrid rule-based + ML pattern detection

## Phase 3: Web Interface — COMPLETE

### Step 3.1: Backend API ✓
| Endpoint | Method | Purpose |
|----------|--------|---------|
| `/` | GET | Main HTML page |
| `/health` | GET | Health check |
| `/api/locale` | GET/POST | Locale management |
| `/api/instruments` | GET | List instruments |
| `/api/candles` | GET | Get raw candle data |
| `/api/trend` | POST | Analyze trend |
| `/api/entry-signal` | POST | Detect entry signal |
| `/api/risk-analysis` | POST | Full risk analysis |
| `/api/backtest` | GET/POST | Run backtest |
| `/api/ml/trend` | GET | ML trend prediction |
| `/api/ml/patterns` | GET | ML pattern recognition |
| `/api/support-resistance` | POST | S/R levels |
| `/api/stats/{instrument}` | GET | Instrument statistics |
| `/api/cache/stats` | GET | Cache statistics |
| `/api/cache/clear` | POST | Clear cache |

### Step 3.2: Frontend Components ✓
- ✅ Instrument selector dropdown
- ✅ Timeframe selector (D1, H1, W1, M15, M5)
- ✅ Charting component (Lightweight Charts v4.1)
- ✅ Strategy parameter inputs
- ✅ Backtest execution (quick and date-range)
- ✅ Results display (stats grid, trade log, equity curve)
- ✅ Performance metrics (win rate, Sharpe, drawdown, P&L)

## Phase 4: Internationalization — COMPLETE

### Step 4.1: Russian Language Support ✓
- ✅ Full EN/RU translation dictionaries in `config/locales.py`
- ✅ Language switcher in UI
- ✅ Runtime locale persistence in `app_config.json`
- ✅ Translation API endpoints

## Phase 5: Testing — PARTIALLY COMPLETE

### Step 5.1: Unit Tests ✓
- ✅ 74 tests passing (41 core logic, 13 preprocessing, 20 additional)
- ✅ Trend detection logic
- ✅ Entry point calculation
- ✅ Risk management formulas
- ✅ Candle pattern recognition
- ✅ Data preprocessing

### Step 5.2: Integration Tests ❌ NOT YET IMPLEMENTED
- ❌ Full backtesting pipeline integration
- ❌ Web API endpoint tests
- ❌ Data flow from DB to chart
- ❌ Database connector tests (requires live DB)
- ❌ Cache manager tests
- ❌ ML model tests (requires PyTorch)

## Phase 6: Deployment

### Step 6.1: Containerization
```dockerfile
FROM python:3.9-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install -r requirements.txt
COPY . .
EXPOSE 8000
CMD ["uvicorn", "web.app:app", "--host", "0.0.0.0", "--port", "8000"]
```

### Step 6.2: Database Backup and Recovery
- Implement automated backup procedures
- Document restore process for database
- Test backup/restore with sample data
- Establish data retention policies

### Step 6.3: Deployment Options
- Docker Compose for local development
- Kubernetes for production
- Cloud deployment (AWS/GCP/Azure)

## Database Performance Optimization

### Database-Specific Optimizations:
1. **Query Caching Layer:**
   - Implement Redis cache for frequent queries
   - Cache strategies by instrument/timeframe
   - Invalidate cache on new data arrival

2. **Batch Processing:**
   - Retrieve multiple timeframes in single query
   - Batch candle data for multiple instruments
   - Implement pagination for large result sets

3. **Indexing Strategy:**
   - Ensure indexes on timestamp and instrument_id
   - Composite indexes for common query patterns
   - Analyze query execution plans

4. **Data Partitioning:**
   - Consider time-based partitioning for candles table
   - Archive old data to improve query performance
   - Implement data retention policies

## Optimization Strategy

1. **Neural Network Efficiency**:
   - Use small convolutional networks (10k-50k parameters)
   - Quantize models to 8-bit integers where possible
   - Implement model caching for repeated calculations

2. **Agent-Based Architecture**:
   - DataFetchingAgent: Handles DB queries and caching
   - StrategyAnalysisAgent: Executes trading logic
   - VisualizationAgent: Generates charts and reports
   - MonitoringAgent: Tracks backtest performance

3. **Performance Optimization**:
   - Implement data batching for multiple instruments
   - Use vectorized operations for candle calculations
   - Cache frequent query results
   - Lazy-load chart data as user scrolls

## Risk Management Implementation

The strategy specifies:
- Stop loss below low of second-to-last candle (uptrend buy)
- Stop loss beyond high of second-to-last candle (downtrend sell)
- Take profit = 3 × stop loss distance

This is implemented in `risk_calculator.py` with comprehensive validation.

## Files Reference
| File | Lines | Purpose |
|------|-------|---------|
| `config/locales.py` | 1-491 | i18n translation engine |
| `web/app.py` | 1-826 | FastAPI application + backtest engine |
| `core/entry_detection.py` | 1-300 | Entry signal detection |
| `core/risk_calculator.py` | 1-266 | Risk management |
| `core/trend_analysis.py` | 1-270 | Trend detection |
| `core/candle_patterns.py` | 1-409 | Pattern recognition |
| `models/trend_predictor.py` | 1-201 | ML trend prediction |
| `models/pattern_recognizer.py` | 1-242 | ML pattern recognition |
| `data/db_connector.py` | 1-313 | Database connection |
| `data/preprocessing.py` | 1-218 | Data normalization |
| `data/cache_manager.py` | 1-201 | Caching layer |