import sqlite3

import pytest

from sqlmem.cache import CacheManager
from sqlmem.executor import QueryExecutor
from sqlmem.parser import parse
from sqlmem.registry import ColumnRegistry
from sqlmem.stats import StatsCollector


@pytest.fixture
def source_conn():
    conn = sqlite3.connect(":memory:")
    conn.executescript(
        """
        CREATE TABLE users (id TEXT, name TEXT, status TEXT);
        INSERT INTO users VALUES ('1', 'alice', 'active'), ('2', 'bob', 'inactive');
        CREATE TABLE orders (id TEXT, user_id TEXT, total TEXT, title TEXT);
        INSERT INTO orders VALUES ('10', '1', '99', 'first'), ('11', '2', '5', 'second');
        """
    )
    conn.commit()
    yield conn
    conn.close()


@pytest.fixture
def executor(tmp_path, source_conn):
    cache = CacheManager(db_path=tmp_path / "cache.db", backup_interval=9999)
    registry = ColumnRegistry(cache.connection)
    stats = StatsCollector()
    ex = QueryExecutor(cache, registry, source_conn, stats)
    yield ex
    cache.close()


def run(executor, sql, params=None):
    return executor.execute(parse(sql, params))


# --- R1: parameters ---------------------------------------------------------


def test_param_filters_in_memory(executor):
    rows = run(executor, "SELECT id, name FROM users WHERE id = ?", ("1",))
    assert rows == [{"id": "1", "name": "alice"}]


def test_param_no_match(executor):
    rows = run(executor, "SELECT name FROM users WHERE id = ?", ("999",))
    assert rows == []


def test_named_params(executor):
    rows = run(executor, "SELECT name FROM users WHERE id = :id", {"id": "2"})
    assert rows == [{"name": "bob"}]


# --- cache hit / miss / refetch --------------------------------------------


def test_cache_hit_does_not_refetch(executor):
    run(executor, "SELECT name FROM users")
    run(executor, "SELECT name FROM users")
    assert executor._stats.hits == 1
    assert executor._stats.misses == 1


def test_new_column_triggers_refetch(executor):
    run(executor, "SELECT name FROM users")
    run(executor, "SELECT name, status FROM users")
    assert executor._stats.misses == 1
    assert executor._stats.refetches == 1


# --- R2: JOINs --------------------------------------------------------------


def test_join_across_two_tables(executor):
    rows = run(
        executor,
        "SELECT u.name, o.title FROM users u "
        "JOIN orders o ON o.user_id = u.id WHERE u.id = ?",
        ("1",),
    )
    assert rows == [{"name": "alice", "title": "first"}]


def test_join_caches_each_table_independently(executor):
    run(
        executor,
        "SELECT u.name, o.title FROM users u JOIN orders o ON o.user_id = u.id",
    )
    # two distinct tables loaded → two misses
    assert executor._stats.misses == 2
    assert executor._cache.is_table_cached("users")
    assert executor._cache.is_table_cached("orders")


# --- R3: SELECT * -----------------------------------------------------------


def test_select_star_returns_all_columns(executor):
    rows = run(executor, "SELECT * FROM users WHERE id = ?", ("1",))
    assert rows == [{"id": "1", "name": "alice", "status": "active"}]


def test_select_star_marks_table_full_and_hits(executor):
    run(executor, "SELECT * FROM users")
    run(executor, "SELECT * FROM users")
    assert executor._cache.is_table_full("users")
    assert executor._stats.misses == 1
    assert executor._stats.hits == 1


def test_column_query_after_star_is_a_hit(executor):
    run(executor, "SELECT * FROM users")
    run(executor, "SELECT name FROM users")
    # full table already cached → specific column is a hit, no refetch
    assert executor._stats.refetches == 0
    assert executor._stats.hits == 1