# Before (v15.x)
df.set_index('date')

# After (v16.0.0+)
df.set_index('timestamp')

import gapless_crypto_clickhouse as gcch

status = gcch.check_setup()
print(f"Ready: {status['ready']}, Mode: {status['mode']}")

# If issues found, print actionable fixes
if not status["ready"]:
    for issue in status["issues"]:
        print(f"Issue: {issue['message']}")
        print(f"Fix: {issue['fix']}")

import gapless_crypto_clickhouse as gcch

# Auto-downloads from Binance CDN if not cached
df = gcch.query_ohlcv("BTCUSDT", "1h", "2024-01-01", "2024-01-31")
print(df.head())

from gapless_crypto_clickhouse import probe
print(probe.get_local_installation_guide())

import gapless_crypto_clickhouse as gcch

# Fetch recent data with date range (CCXT-compatible timeframe parameter)
df = gcch.download("BTCUSDT", timeframe="1h", start="2024-01-01", end="2024-06-30")

# Or with limit
df = gcch.fetch_data("ETHUSDT", timeframe="4h", limit=1000)

# Get available symbols and timeframes
symbols = gcch.get_supported_symbols()
timeframes = gcch.get_supported_timeframes()

# Fill gaps in existing data
results = gcch.fill_gaps("./data")

# Multi-symbol batch download (concurrent execution)
results = gcch.download_multiple(
    symbols=["BTCUSDT", "ETHUSDT", "BNBUSDT", "XRPUSDT", "SOLUSDT"],
    timeframe="1h",
    start_date="2024-01-01",
    end_date="2024-06-30",
    max_workers=5  # Configure concurrency
)
# Returns: dict[str, pd.DataFrame]
# Example: btc_df = results["BTCUSDT"]

from gapless_crypto_clickhouse import BinancePublicDataCollector, UniversalGapFiller

# Custom collection with full control
collector = BinancePublicDataCollector(
    symbol="SOLUSDT",
    start_date="2023-01-01",
    end_date="2023-12-31"
)

result = collector.collect_timeframe_data("1h")
df = result["dataframe"]

# Manual gap filling
gap_filler = UniversalGapFiller()
gaps = gap_filler.detect_all_gaps("BTCUSDT_1h_data.csv", "1h")

import gapless_crypto_clickhouse as gcch

# Fetch data
df = gcch.download("BTCUSDT", timeframe="1h", start="2024-01-01", end="2024-06-30")

# DataFrame columns (microstructure format)
print(df.columns.tolist())
# ['timestamp', 'open', 'high', 'low', 'close', 'volume',
#  'close_time', 'quote_asset_volume', 'number_of_trades',
#  'taker_buy_base_asset_volume', 'taker_buy_quote_asset_volume']

# Professional microstructure analysis
buy_pressure = df['taker_buy_base_asset_volume'].sum() / df['volume'].sum()
avg_trade_size = df['volume'].sum() / df['number_of_trades'].sum()
market_impact = df['quote_asset_volume'].std() / df['quote_asset_volume'].mean()

print(f"Taker buy pressure: {buy_pressure:.1%}")
print(f"Average trade size: {avg_trade_size:.4f} BTC")
print(f"Market impact volatility: {market_impact:.3f}")

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"Retrieved {len(df)} rows")

# 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
)

from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection

# Connect to ClickHouse (reads from .env or uses defaults)
with ClickHouseConnection() as conn:
    # Verify connection
    health = conn.health_check()
    print(f"ClickHouse connected: {health}")

    # Execute simple query
    result = conn.execute("SELECT count() FROM ohlcv")
    print(f"Total rows in database: {result[0][0]:,}")

from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection
from gapless_crypto_clickhouse.collectors.clickhouse_bulk_loader import ClickHouseBulkLoader

# Ingest historical data from Binance public repository
with ClickHouseConnection() as conn:
    loader = ClickHouseBulkLoader(conn, instrument_type="spot")

    # Ingest single month (e.g., January 2024)
    rows_inserted = loader.ingest_month("BTCUSDT", "1h", year=2024, month=1)
    print(f"Inserted {rows_inserted:,} rows for BTCUSDT 1h (Jan 2024)")

    # Ingest date range (e.g., Q1 2024)
    total_rows = loader.ingest_date_range(
        symbol="ETHUSDT",
        timeframe="4h",
        start_date="2024-01-01",
        end_date="2024-03-31"
    )
    print(f"Inserted {total_rows:,} rows for ETHUSDT 4h (Q1 2024)")

from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection
from gapless_crypto_clickhouse.clickhouse_query import OHLCVQuery

with ClickHouseConnection() as conn:
    query = OHLCVQuery(conn)

    # Get latest data (last 10 bars)
    df = query.get_latest("BTCUSDT", "1h", limit=10)
    print(f"Latest 10 bars:\n{df[['timestamp', 'close']]}")

    # Get specific date range
    df = query.get_range(
        symbol="BTCUSDT",
        timeframe="1h",
        start_date="2024-01-01",
        end_date="2024-01-31",
        instrument_type="spot"
    )
    print(f"January 2024: {len(df):,} bars")

    # Multi-symbol comparison
    df = query.get_multi_symbol(
        symbols=["BTCUSDT", "ETHUSDT", "SOLUSDT"],
        timeframe="1d",
        start_date="2024-01-01",
        end_date="2024-12-31"
    )
    print(f"Multi-symbol dataset: {df.shape}")

# Ingest futures data (same format as spot)
with ClickHouseConnection() as conn:
    loader = ClickHouseBulkLoader(conn, instrument_type="futures-um")
    rows = loader.ingest_month("BTCUSDT", "1h", 2024, 1)
    print(f"Futures data: {rows:,} rows")

    # Query futures data (isolated from spot)
    query = OHLCVQuery(conn)
    df_spot = query.get_latest("BTCUSDT", "1h", instrument_type="spot", limit=10)
    df_futures = query.get_latest("BTCUSDT", "1h", instrument_type="futures-um", limit=10)

    print(f"Spot data: {len(df_spot)} bars")
    print(f"Futures data: {len(df_futures)} bars")

import gapless_crypto_clickhouse as gcch

# Process multiple symbols with simple loops
symbols = ["BTCUSDT", "ETHUSDT", "SOLUSDT", "ADAUSDT"]
timeframes = ["1h", "4h"]

for symbol in symbols:
    for timeframe in timeframes:
        df = gcch.fetch_data(symbol, timeframe, start="2023-01-01", end="2023-12-31")
        print(f"{symbol} {timeframe}: {len(df)} bars collected")

from gapless_crypto_clickhouse import BinancePublicDataCollector

# Initialize with custom settings
collector = BinancePublicDataCollector(
    start_date="2023-01-01",
    end_date="2023-12-31",
    output_dir="./crypto_data"
)

# Process multiple symbols with detailed control
symbols = ["BTCUSDT", "ETHUSDT", "SOLUSDT"]
for symbol in symbols:
    collector.symbol = symbol
    results = collector.collect_multiple_timeframes(["1m", "5m", "1h", "4h"])
    for timeframe, result in results.items():
        print(f"{symbol} {timeframe}: {result['stats']}")

import gapless_crypto_clickhouse as gcch

# Quick gap filling for entire directory
results = gcch.fill_gaps("./data")
print(f"Processed {results['files_processed']} files")
print(f"Filled {results['gaps_filled']}/{results['gaps_detected']} gaps")
print(f"Success rate: {results['success_rate']:.1f}%")

# Gap filling for specific symbols only
results = gcch.fill_gaps("./data", symbols=["BTCUSDT", "ETHUSDT"])

from gapless_crypto_clickhouse import UniversalGapFiller

gap_filler = UniversalGapFiller()

# Manual gap detection and analysis
gaps = gap_filler.detect_all_gaps("BTCUSDT_1h.csv", "1h")
print(f"Found {len(gaps)} gaps")

for gap in gaps:
    duration_hours = gap['duration'].total_seconds() / 3600
    print(f"Gap: {gap['start_time']} → {gap['end_time']} ({duration_hours:.1f}h)")

# Fill specific gaps
result = gap_filler.process_file("BTCUSDT_1h.csv", "1h")

from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection
from gapless_crypto_clickhouse.collectors.clickhouse_bulk_loader import ClickHouseBulkLoader

# Multi-symbol bulk ingestion for backtesting datasets
symbols = ["BTCUSDT", "ETHUSDT", "SOLUSDT", "ADAUSDT", "DOGEUSDT"]
timeframes = ["1h", "4h", "1d"]

with ClickHouseConnection() as conn:
    loader = ClickHouseBulkLoader(conn, instrument_type="spot")

    for symbol in symbols:
        for timeframe in timeframes:
            # Ingest Q1 2024 data
            rows = loader.ingest_date_range(
                symbol=symbol,
                timeframe=timeframe,
                start_date="2024-01-01",
                end_date="2024-03-31"
            )
            print(f"{symbol} {timeframe}: {rows:,} rows ingested")

# Zero-gap guarantee: Re-running this script won't create duplicates

from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection
from gapless_crypto_clickhouse.clickhouse_query import OHLCVQuery

with ClickHouseConnection() as conn:
    query = OHLCVQuery(conn)

    # Get synchronized data for all symbols (same time range)
    df = query.get_multi_symbol(
        symbols=["BTCUSDT", "ETHUSDT", "SOLUSDT"],
        timeframe="1h",
        start_date="2024-01-01",
        end_date="2024-01-31"
    )

    # Analyze cross-asset correlations
    pivot = df.pivot_table(index="timestamp", columns="symbol", values="close")
    correlation = pivot.corr()
    print(f"Correlation matrix:\n{correlation}")

    # Relative strength analysis
    for symbol in ["BTCUSDT", "ETHUSDT", "SOLUSDT"]:
        symbol_data = df[df["symbol"] == symbol]
        returns = symbol_data["close"].pct_change().sum()
        print(f"{symbol} total return: {returns:.2%}")

from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection

with ClickHouseConnection() as conn:
    # Custom SQL for advanced analytics (ClickHouse functions)
    query = """
    SELECT
        symbol,
        timeframe,
        toStartOfDay(timestamp) AS day,
        avg(close) AS avg_price,
        stddevPop(close) AS volatility,
        sum(volume) AS total_volume,
        count() AS bar_count
    FROM ohlcv FINAL
    WHERE symbol IN ('BTCUSDT', 'ETHUSDT')
      AND timeframe = '1h'
      AND timestamp >= '2024-01-01'
      AND timestamp < '2024-02-01'
    GROUP BY symbol, timeframe, day
    ORDER BY day ASC, symbol ASC
    """

    result = conn.execute(query)

    # Process results
    for row in result:
        symbol, timeframe, day, avg_price, volatility, volume, bars = row
        print(f"{day} {symbol}: avg=${avg_price:.2f}, vol={volatility:.2f}, volume={volume:,.0f}")

import gapless_crypto_clickhouse as gcch
from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection
from gapless_crypto_clickhouse.clickhouse_query import OHLCVQuery
from gapless_crypto_clickhouse.collectors.clickhouse_bulk_loader import ClickHouseBulkLoader

# Step 1: Collect to CSV files
df = gcch.download("BTCUSDT", timeframe="1h", start="2024-01-01", end="2024-03-31")
print(f"Downloaded {len(df):,} bars to CSV")

# Step 2: Ingest CSV to ClickHouse for analysis
with ClickHouseConnection() as conn:
    loader = ClickHouseBulkLoader(conn)
    loader.ingest_from_dataframe(df, symbol="BTCUSDT", timeframe="1h")

    # Step 3: Run advanced queries
    query = OHLCVQuery(conn)
    gaps = query.detect_gaps("BTCUSDT", "1h", "2024-01-01", "2024-03-31")
    print(f"Gap detection: {len(gaps)} gaps found")

# Create a test script: test_clickhouse.py
from gapless_crypto_clickhouse.clickhouse import ClickHouseConnection
from gapless_crypto_clickhouse.collectors.clickhouse_bulk_loader import ClickHouseBulkLoader

with ClickHouseConnection() as conn:
    # Health check
    print(f"ClickHouse connected: {conn.health_check()}")

    # Test ingestion (small dataset)
    loader = ClickHouseBulkLoader(conn, instrument_type="spot")
    rows = loader.ingest_month("BTCUSDT", "1d", year=2024, month=1)
    print(f"Test ingestion: {rows} rows")

# Run test with Doppler
# doppler run -- uv run python test_clickhouse.py

collector = BinancePublicDataCollector(
    symbol="BTCUSDT",           # USDT spot pair
    start_date="2023-01-01",    # Start date (YYYY-MM-DD)
    end_date="2023-12-31",      # End date (YYYY-MM-DD)
    output_dir="./crypto_data"  # Output directory (optional)
)

result = collector.collect_timeframe_data("1h")
df = result["dataframe"]              # pandas DataFrame with OHLCV + microstructure
filepath = result["filepath"]         # Path to saved CSV file
stats = result["stats"]               # Collection statistics

# Access microstructure data
total_trades = df["number_of_trades"].sum()
taker_buy_ratio = df["taker_buy_base_asset_volume"].sum() / df["volume"].sum()

results = collector.collect_multiple_timeframes(["1h", "4h"])
for timeframe, result in results.items():
    df = result["dataframe"]
    print(f"{timeframe}: {len(df):,} bars")

gap_filler = UniversalGapFiller()
gaps = gap_filler.detect_all_gaps("BTCUSDT_1h_data.csv", "1h")
print(f"Found {len(gaps)} gaps to fill")

# Fill first detected gap
success = gap_filler.fill_gap(gaps[0], "BTCUSDT_1h_data.csv", "1h")
print(f"Gap filled successfully: {success}")

result = gap_filler.process_file("BTCUSDT_1h_data.csv", "1h")
print(f"Filled {result['gaps_filled']}/{result['gaps_detected']} gaps")

from pathlib import Path
atomic_ops = AtomicCSVOperations(Path("data.csv"))
backup_path = atomic_ops.create_backup()

success = atomic_ops.write_dataframe_atomic(df)
if not success:
    atomic_ops.rollback_from_backup()

from datetime import datetime
merger = SafeCSVMerger(Path("eth_data.csv"))
success = merger.merge_gap_data_safe(
    gap_data,                    # DataFrame with gap data
    datetime(2024, 1, 1, 12),   # Gap start time
    datetime(2024, 1, 1, 15)    # Gap end time
)

try:
    collector = BinancePublicDataCollector(symbol="INVALIDPAIR")
    result = collector.collect_timeframe_data("1h")
except ValueError as e:
    print(f"Invalid symbol format: {e}")
except ConnectionError as e:
    print(f"Network error: {e}")
except FileNotFoundError as e:
    print(f"Output directory error: {e}")

from typing import Dict, List, Optional, Any
from pathlib import Path
import pandas as pd

def collect_timeframe_data(self, trading_timeframe: str) -> Dict[str, Any]:
    # Returns dict with 'dataframe', 'filepath', and 'stats' keys
    pass

def collect_multiple_timeframes(
    self,
    timeframes: Optional[List[str]] = None
) -> Dict[str, Dict[str, Any]]:
    # Returns nested dict by timeframe
    pass