import sqlite3

import pytest
from sqlalchemy import create_engine

import sqlmem.engine as eng_mod
from sqlmem import CachingEngine
from sqlmem.cache import CacheManager


def index_names(conn, table=None):
    sql = "SELECT name FROM sqlite_master WHERE type = 'index'"
    return {r[0] for r in conn.execute(sql).fetchall()}


# --- cache level ------------------------------------------------------------


@pytest.fixture
def source_conn():
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE big (id TEXT, val TEXT)")
    conn.executemany(
        "INSERT INTO big VALUES (?, ?)", [(str(i), f"v{i}") for i in range(100)]
    )
    conn.commit()
    yield conn
    conn.close()


@pytest.fixture
def cache(tmp_path):
    c = CacheManager(db_path=tmp_path / "cache.db", backup_interval=9999)
    yield c
    c.close()


def test_index_created_on_load(cache, source_conn):
    cache.add_index("big", ["val"])
    cache.load_table("big", ["id", "val"], source_conn)
    assert "sqlmem_idx_big_val" in index_names(cache.connection)


def test_index_used_by_query_planner(cache, source_conn):
    cache.add_index("big", ["val"])
    cache.load_table("big", ["id", "val"], source_conn)
    plan = cache.connection.execute(
        "EXPLAIN QUERY PLAN SELECT id FROM big WHERE val = 'v50'"
    ).fetchall()
    assert any("sqlmem_idx_big_val" in str(row) for row in plan)


def test_index_skipped_when_columns_not_cached(cache, source_conn):
    cache.add_index("big", ["missing_col"])
    cache.load_table("big", ["id", "val"], source_conn)  # must not raise
    assert "sqlmem_idx_big_missing_col" not in index_names(cache.connection)


def test_index_recreated_on_reload(cache, source_conn):
    cache.add_index("big", ["val"])
    cache.load_table("big", ["id", "val"], source_conn)
    cache.load_table("big", ["id", "val"], source_conn)  # reload (staging swap)
    assert "sqlmem_idx_big_val" in index_names(cache.connection)


# --- engine level -----------------------------------------------------------


@pytest.fixture
def source_engine(tmp_path):
    db_path = tmp_path / "source.db"
    conn = sqlite3.connect(db_path)
    conn.execute("CREATE TABLE products (id TEXT, name TEXT, price TEXT)")
    conn.executemany(
        "INSERT INTO products VALUES (?, ?, ?)",
        [(str(i), f"n{i}", f"{i}.00") for i in range(20)],
    )
    conn.commit()
    conn.close()
    engine = create_engine(f"sqlite:///{db_path}")
    yield engine
    engine.dispose()


@pytest.fixture
def patched_cache(tmp_path, monkeypatch):
    monkeypatch.setattr(eng_mod, "CACHE_DB_PATH", tmp_path / "cache.db")
    monkeypatch.setattr(eng_mod, "BACKUP_INTERVAL_SECONDS", 9999)


def test_index_column_auto_loaded_even_if_not_selected(source_engine, patched_cache):
    engine = CachingEngine(source_engine, indexes={"products": ["name"]})
    engine.execute("SELECT id FROM products")  # does not select 'name'
    cols = {
        r[1]
        for r in engine._cache.connection.execute("PRAGMA table_info(products)").fetchall()
    }
    assert "name" in cols  # pulled in so the index can be built
    assert "sqlmem_idx_products_name" in index_names(engine._cache.connection)
    engine.close()


def test_composite_index(source_engine, patched_cache):
    engine = CachingEngine(source_engine, indexes={"products": [["name", "price"]]})
    engine.execute("SELECT id FROM products")
    assert "sqlmem_idx_products_name_price" in index_names(engine._cache.connection)
    engine.close()


def test_index_survives_invalidate_and_reload(source_engine, patched_cache):
    engine = CachingEngine(source_engine, indexes={"products": ["name"]})
    engine.execute("SELECT id, name FROM products")
    engine.invalidate("products")
    engine.execute("SELECT id, name FROM products")
    assert "sqlmem_idx_products_name" in index_names(engine._cache.connection)
    engine.close()