# gapless-crypto-clickhouse v6.0.0

ClickHouse-based cryptocurrency data collection with zero-gap guarantee and Apache Arrow optimization.

## Quick Start

```python
from gapless_crypto_clickhouse import query_ohlcv

# Query with auto-ingestion (downloads data if missing)
df = query_ohlcv("BTCUSDT", "1h", "2024-01-01", "2024-01-31")
print(f"Rows: {len(df)}")  # 744 rows (31 days * 24 hours)
```

## Core API

### query_ohlcv() - Unified Query with Auto-Ingestion (NEW in v6.0.0)

**Signature**:
```python
query_ohlcv(
    symbol: str | List[str],
    timeframe: str,
    start_date: str,
    end_date: str,
    instrument_type: Literal["spot", "futures-um"] = "spot",
    auto_ingest: bool = True,
    fill_gaps: bool = True,
    clickhouse_config: Optional[ClickHouseConfig] = None,
) -> pd.DataFrame
```

**Parameters**:
- `symbol`: Trading pair (e.g., "BTCUSDT") or list of symbols
- `timeframe`: Timeframe string (e.g., "1h", "4h", "1d")
- `start_date`: Start date in "YYYY-MM-DD" format
- `end_date`: End date in "YYYY-MM-DD" format
- `instrument_type`: "spot" (default) or "futures-um"
- `auto_ingest`: Auto-download missing data (default: True)
- `fill_gaps`: Detect and fill gaps (default: True)

**Performance**:
- First query (auto-ingest): 30-60s (download + ingest + query)
- Cached query: 0.1-2s (3x faster with Arrow)
- Memory: 75% less vs previous version (Arrow zero-copy)

**Examples**:
```python
# Basic query
df = query_ohlcv("BTCUSDT", "1h", "2024-01-01", "2024-01-31")

# Multi-symbol query
df = query_ohlcv(
    ["BTCUSDT", "ETHUSDT", "SOLUSDT"],
    "1h",
    "2024-01-01",
    "2024-01-31"
)

# Futures data
df = query_ohlcv(
    "BTCUSDT",
    "1h",
    "2024-01-01",
    "2024-01-31",
    instrument_type="futures-um"
)

# Query without auto-ingestion (faster, raises if data missing)
df = query_ohlcv(
    "BTCUSDT",
    "1h",
    "2024-01-01",
    "2024-01-31",
    auto_ingest=False
)
```

**Workflow**:
1. Check if data exists in ClickHouse
2. If missing and auto_ingest=True: download from Binance + ingest
3. Query ClickHouse with FINAL keyword (deduplication)
4. If fill_gaps=True: detect and fill gaps
5. Return DataFrame (Arrow-optimized internally)

### fetch_data() - File-Based Workflow (Legacy)

**Signature**:
```python
fetch_data(
    symbol: str,
    timeframe: str,
    start: Optional[str] = None,
    end: Optional[str] = None,
    limit: Optional[int] = None,
    instrument_type: Literal["spot", "futures-um"] = "spot",
) -> pd.DataFrame
```

**Note**: Use `query_ohlcv()` for database-based workflows with auto-ingestion. `fetch_data()` is for file-based workflows (CSV/Parquet).

## Data Coverage

**Symbols**: 715 validated perpetual symbols (spot + futures aligned)
- Examples: BTCUSDT, ETHUSDT, BNBUSDT, SOLUSDT, XRPUSDT
- Source: binance-futures-availability package (95%+ SLA)

**Timeframes**: 16 timeframes (13 standard: 1s-1d + 3 exotic: 3d, 1w, 1mo)
- Ultra-high frequency: 1s, 1m, 3m, 5m
- Intraday: 15m, 30m, 1h, 2h, 4h
- Daily: 6h, 8h, 12h, 1d
- Exotic: 3d, 1w, 1mo

**Instrument Types**:
- spot: USDT-quoted spot pairs
- futures-um: USDT-margined perpetual futures

**Data Format**: 11-column microstructure format
- OHLCV: open, high, low, close, volume
- Timestamps: timestamp (bar open), close_time (bar close)
- Microstructure: quote_asset_volume, number_of_trades, taker_buy_base_asset_volume, taker_buy_quote_asset_volume
- Futures-specific: funding_rate (NULL for spot)

## Performance

**Arrow Optimization (v6.0.0)**:
- Query speedup: 3x faster DataFrame creation
- Memory reduction: 75% less memory (zero-copy)
- Driver: clickhouse-connect with Apache Arrow

**Ingestion**:
- Bulk loader: >100K rows/sec
- Download: 22x faster than REST API (CloudFront CDN)

**Zero-Gap Guarantee**:
- Deterministic versioning + ReplacingMergeTree deduplication
- Query with FINAL keyword for deduplicated results

## Deployment Modes

This package supports TWO deployment modes (ADR-0044):

### Cloud Mode (Production)
- Connect to ClickHouse Cloud on AWS
- Requires: CLICKHOUSE_HOST, CLICKHOUSE_PASSWORD environment variables
- Best for: Multi-user access, production workloads, managed infrastructure

### Local Mode (Development)
- Install ClickHouse locally via Homebrew or apt
- No credentials required
- Best for: Backtesting, development, offline work, trying the package

**Installation (Local Mode)**:

macOS:
```bash
brew install clickhouse
clickhouse server --daemon
```

Linux (EC2/Ubuntu):
```bash
curl https://clickhouse.com/ | sh
./clickhouse server --daemon
```

**Mode Selection**:
```bash
# Explicit local mode
export GCCH_MODE=local

# Explicit cloud mode
export GCCH_MODE=cloud

# Auto-detect (default): localhost → local, remote → cloud
export GCCH_MODE=auto
```

**Introspection**:
```python
from gapless_crypto_clickhouse import probe

# Check available modes
modes = probe.get_deployment_modes()

# Check current mode
current = probe.get_current_mode()  # "local" or "cloud"

# Check if local ClickHouse is installed
status = probe.check_local_clickhouse()
```

## Configuration

### Cloud Mode (GCCH_MODE=cloud)
```bash
export CLICKHOUSE_HOST=your-instance.clickhouse.cloud
export CLICKHOUSE_HTTP_PORT=8443      # Cloud HTTPS port
export CLICKHOUSE_PASSWORD=your-password
export CLICKHOUSE_SECURE=true         # TLS enabled
```

### Local Mode (GCCH_MODE=local)
```bash
export CLICKHOUSE_HOST=localhost      # Default: localhost
export CLICKHOUSE_HTTP_PORT=8123      # Default: 8123 (HTTP)
export CLICKHOUSE_DATABASE=default    # Default: default
export CLICKHOUSE_USER=default        # Default: default
export CLICKHOUSE_PASSWORD=           # Default: empty
```

**Custom Configuration**:
```python
from gapless_crypto_clickhouse.clickhouse import ClickHouseConfig

config = ClickHouseConfig(
    host="clickhouse.example.com",
    http_port=8123,
    database="crypto",
    user="admin",
    password="secret"
)

df = query_ohlcv(
    "BTCUSDT",
    "1h",
    "2024-01-01",
    "2024-01-31",
    clickhouse_config=config
)
```

## AI Agent Introspection

```python
from gapless_crypto_clickhouse import probe

# Get all capabilities
caps = probe.get_capabilities()

# Get supported symbols
symbols = probe.get_supported_symbols()  # 715 symbols

# Get supported timeframes
timeframes = probe.get_supported_timeframes()  # 16 timeframes

# Get performance info
perf = probe.get_performance_info()
```

## Migration from v5.0.0

**Breaking Changes**:
- Protocol change: Native TCP (port 9000) → HTTP (port 8123)
- Driver change: clickhouse-driver → clickhouse-connect
- Exception types: ClickHouseError → Exception

**New Features**:
- query_ohlcv() with lazy auto-ingestion
- Apache Arrow optimization (3x faster queries, 75% less memory)
- AI discoverability (probe module)

**Migration Steps**:
1. Update port: 9000 → 8123 in CLICKHOUSE_PORT or use CLICKHOUSE_HTTP_PORT
2. Update exceptions: catch Exception instead of ClickHouseError
3. Use query_ohlcv() for unified query API with auto-ingestion

## Common Patterns

### Backtesting
```python
# Load historical data for backtesting
df = query_ohlcv("BTCUSDT", "1h", "2023-01-01", "2023-12-31")

# Calculate indicators
df['sma_20'] = df['close'].rolling(20).mean()
df['returns'] = df['close'].pct_change()

# Backtest strategy
df['signal'] = (df['close'] > df['sma_20']).astype(int)
df['strategy_returns'] = df['returns'] * df['signal'].shift(1)
```

### Multi-Symbol Analysis
```python
# Compare multiple symbols
symbols = ["BTCUSDT", "ETHUSDT", "BNBUSDT", "SOLUSDT"]
df = query_ohlcv(symbols, "1d", "2024-01-01", "2024-12-31")

# Calculate correlation matrix
pivot = df.pivot(index='timestamp', columns='symbol', values='close')
corr = pivot.pct_change().corr()
```

### Real-Time Updates
```python
# Get latest data
from datetime import datetime, timedelta

end = datetime.now()
start = end - timedelta(days=7)

df = query_ohlcv(
    "BTCUSDT",
    "1h",
    start.strftime("%Y-%m-%d"),
    end.strftime("%Y-%m-%d"),
    auto_ingest=True  # Automatically download latest data
)

print(f"Latest price: ${df.iloc[-1]['close']:.2f}")
```

## Error Handling

```python
from gapless_crypto_clickhouse import query_ohlcv

try:
    df = query_ohlcv("BTCUSDT", "1h", "2024-01-01", "2024-01-31")
except ValueError as e:
    print(f"Invalid parameters: {e}")
except Exception as e:
    print(f"Query failed: {e}")
```

## Links

- GitHub: https://github.com/terrylica/gapless-crypto-clickhouse
- PyPI: https://pypi.org/project/gapless-crypto-clickhouse/
- Documentation: See README.md
