"""Tests for database connector and query patterns."""
import pytest
import os
import sys

sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

from data.db_connector import DBConfig, ConnectionPool, CandleQuery, InstrumentRegistry, DBConnector


@pytest.fixture
def db_config():
    """Provide test database config."""
    return DBConfig(
        host='nlbotinterface.ru',
        port=3306,
        database='bitcoin_tickers',
        user='bitcoin',
        password='g49020007',
        pool_size=2,
    )


@pytest.fixture
def connector(db_config):
    """Provide a DBConnector instance."""
    return DBConnector(db_config)


class TestDBConfig:
    def test_default_values(self):
        config = DBConfig(
            host='localhost',
            port=3306,
            database='test',
            user='root',
            password='pass',
        )
        assert config.host == 'localhost'
        assert config.port == 3306
        assert config.charset == 'utf8mb4'
        assert config.autocommit is True
        assert config.pool_size == 5

    def test_custom_values(self):
        config = DBConfig(
            host='remote',
            port=3307,
            database='prod',
            user='app',
            password='secret',
            charset='latin1',
            autocommit=False,
            pool_size=10,
        )
        assert config.host == 'remote'
        assert config.port == 3307
        assert config.charset == 'latin1'
        assert config.autocommit is False
        assert config.pool_size == 10


class TestConnectionPool:
    def test_pool_creation(self, db_config):
        pool = ConnectionPool(db_config)
        assert pool._size <= db_config.pool_size
        pool.close_all()

    def test_get_connection_is_generator(self, db_config):
        pool = ConnectionPool(db_config)
        with pool.get_connection() as conn:
            assert conn is not None
            assert hasattr(conn, 'cursor')
        pool.close_all()

    def test_close_all(self, db_config):
        pool = ConnectionPool(db_config)
        pool.close_all()
        assert pool._size == 0


class TestInstrumentRegistry:
    def test_load_instruments(self, connector):
        names = connector.instruments.get_names()
        assert isinstance(names, list)
        assert len(names) > 0
        # Known instruments from database
        assert 'BITCOIN' in names
        assert 'EURUSD' in names
        assert 'GAZP' in names

    def test_get_by_type(self, connector):
        crypto = connector.instruments.get_by_type('crypto')
        assert len(crypto) >= 2
        moex = connector.instruments.get_by_type('moex')
        assert len(moex) >= 10

    def test_get_timeframes(self, connector):
        timeframes = connector.instruments.get_timeframes('BITCOIN')
        assert isinstance(timeframes, list)
        assert 'D1' in timeframes or 'H1' in timeframes

    def test_get_all(self, connector):
        instruments = connector.instruments.get_all()
        assert len(instruments) == 15
        first = instruments[0]
        assert 'id' in first
        assert 'name' in first
        assert 'type' in first


class TestCandleQuery:
    def test_get_table_name(self, connector):
        table = connector.candles._get_table_name('BITCOIN', 'H1')
        assert table == 'BITCOIN_H1'

    def test_invalid_timeframe(self, connector):
        with pytest.raises(ValueError):
            connector.candles._get_table_name('BITCOIN', 'INVALID')

    def test_get_range_stats(self, connector):
        stats = connector.candles.get_range_stats('EURUSD', 'D1')
        assert stats is not None
        assert 'count' in stats
        assert stats['count'] > 0

    def test_get_last_n(self, connector):
        candles = connector.candles.get_last_n('EURUSD', 'D1', 3)
        assert len(candles) == 3
        assert 'timestamp' in candles[0]
        assert 'Open' in candles[0]
        assert 'Close' in candles[0]

    def test_get_by_range(self, connector):
        # Test with a known range for EURUSD_D1
        start = 1455235200  # 2016-02-12
        end = 1455494400    # 2016-02-15
        candles = connector.candles.get_by_range('EURUSD', 'D1', start, end)
        assert isinstance(candles, list)
        assert len(candles) >= 2

    def test_get_last_hours(self, connector):
        candles = connector.candles.get_last_hours('BITCOIN', 'H1', 48)
        assert isinstance(candles, list)
        if len(candles) > 0:
            assert 'timestamp' in candles[0]


class TestHealthCheck:
    def test_health_check(self, connector):
        assert connector.health_check() is True

    def test_close(self, connector):
        connector.close()
        # After close, should still be able to reconnect
        assert connector.health_check() is True