# Database Schema Documentation

## Connection
```python
db_config = {
    'host': 'nlbotinterface.ru',
    'port': 3306,
    'database': 'bitcoin_tickers',
    'user': 'bitcoin',
    'password': 'g49020007'
}
```

## Tables Overview

### 1. instruments (lookup table)
| Column | Type | Constraints |
|--------|------|-------------|
| id | int(11) | PRIMARY KEY, AUTO_INCREMENT |
| name | varchar(50) | UNIQUE |
| type | varchar(20) | — |

**Rows: 15** — 2 crypto (BITCOIN, BITCOINC), 13 MOEX stocks

### 2. Candle tables (12 instruments × 3 timeframes = 36 tables)

All candle tables share identical structure:

| Column | Type | Constraints |
|--------|------|-------------|
| timestamp | bigint(20) | PRIMARY KEY (Unix epoch) |
| Date | varchar(10) / tinytext | Date string (YYYY.MM.DD) |
| Time | varchar(5) / tinytext | Time string (HH:MM, UTC+3 for MOEX) |
| Open | decimal(20,8) | — |
| High | decimal(20,8) | — |
| Low | decimal(20,8) | — |
| Close | decimal(20,8) | — |
| Volume | bigint(20) / decimal(20,8) | — |

**Naming convention:** `{INSTRUMENT}_{TF}` where TF ∈ {D1, H1, W1}

**Indexing:** Each table has a single BTREE index on `timestamp` (PRIMARY). No composite indexes exist.

### 3. analysis_results
| Column | Type | Constraints |
|--------|------|-------------|
| id | int(11) | PRIMARY KEY, AUTO_INCREMENT |
| table_name | varchar(50) | MUL index |
| instrument | varchar(50) | — |
| timeframe | varchar(10) | — |
| analysis_time | datetime | — |
| candle_last_datetime | datetime | — |
| candle_patterns | longtext | — |
| levels | longtext | — |
| pa_volume | longtext | — |
| trend | longtext | — |
| volatility | longtext | — |
| wave | longtext | — |
| trading_signal | longtext | — |
| pullback_analysis | longtext | — |
| created_at | timestamp | DEFAULT current_timestamp, ON UPDATE |
| atr_analysis | longtext | — |
| wave_range_analysis | longtext | — |

**Rows: 45** — pre-computed analysis results.

### 4. Missing tables (per plan, not present in DB)
- **trades** — no tables matching `trades*` pattern
- **orders** — no tables matching `orders*` pattern

## Data Volume Summary

### By Instrument

| Instrument | Type | D1 rows | H1 rows | W1 rows | D1 Range |
|-----------|------|---------|---------|---------|----------|
| BITCOIN | crypto | 471 | 10,822 | 68 | 2025-01 → 2026-05 |
| BITCOINC | crypto | 471 | 3,627 | 68 | 2025-01 → 2026-05 |
| EURUSD | forex | 2,657 | 16,172 | 1,238 | 2016-02 → 2026-05 |
| ASTR | moex | 745 | 11,561 | 135 | 2023-10 → 2025-12 |
| GAZP | moex | 3,086 | 19,147 | 619 | 2014-06 → 2026-05 |
| SBER | moex | 4,804 | 19,159 | 975 | 2007-07 → 2026-05 |
| LKOH | moex | 5,776 | 19,141 | 1,176 | 2003-08 → 2023-08 |
| MTSS | moex | 3,985 | 19,114 | 806 | 2010-10 → 2021-11 |
| NVTK | moex | 4,248 | 19,101 | 860 | 2009-10 → 2021-08 |
| PHOR | moex | 3,403 | 19,065 | 686 | 2013-02 → 2021-08 |
| PLZL | moex | 3,079 | 19,029 | 619 | 2014-06 → 2021-09 |
| ROSN | moex | 3,086 | 19,140 | 619 | 2014-06 → 2021-11 |
| SNGSP | moex | 3,086 | 19,122 | 619 | 2014-06 → 2021-11 |
| VTBR | moex | 3,401 | 19,079 | 686 | 2013-02 → 2021-10 |
| X5 | moex | 430 | 6,906 | 70 | 2025-01 → 2026-05 |

### Key observations
- **H1 (hourly)** tables are most comprehensive (~19K rows for MOEX stocks)
- **D1 (daily)** tables vary: EURUSD has 2,657 rows going back to 2016; MOEX stocks ~3K-5K rows
- **W1 (weekly)** tables are sparse (68-1,238 rows)
- BITCOIN and BITCOINC have recent data (2025-2026); LKOH data ends ~2023
- EURUSD has the deepest history (2002 for W1, 2016 for D1/H1)

## Data Type Notes
- `timestamp` is Unix epoch (bigint) — **not** a MySQL datetime
- `Date` and `Time` are stored as strings (varchar/tinytext), not DATE/TIME types
- Volume: MOEX tables use `bigint(20)`, crypto tables use `decimal(20,8)`

## Performance Notes
- All tables use InnoDB (implied by BTREE indexes)
- Single-column PRIMARY index on `timestamp` only
- No composite indexes (instrument_id + timestamp) since each instrument has its own table
- No foreign key constraints between tables
- `analysis_results.table_name` has a MUL (non-unique) index

## Query Patterns for Strategy

### Get last N candles (most recent first)
```sql
SELECT * FROM {table} ORDER BY timestamp DESC LIMIT ?;
```

### Get candles by time range (oldest first for processing)
```sql
SELECT * FROM {table}
WHERE timestamp >= ? AND timestamp <= ?
ORDER BY timestamp ASC;
```

### Get candles for last N hours
```sql
SELECT * FROM {table}
WHERE timestamp >= UNIX_TIMESTAMP(NOW() - INTERVAL ? HOUR)
ORDER BY timestamp ASC;
```

### List all available instruments
```sql
SELECT id, name, type FROM instruments ORDER BY name;
```

### List available timeframes for an instrument
```sql
-- Check which tables exist for a given instrument prefix
SHOW TABLES LIKE 'BITCOIN_%';
```

### Join with instruments metadata (example)
```sql
-- Not directly joinable since instruments table has no FKs to candle tables
-- Application-level join needed: query instruments first, then construct table name
```