import pytest

import analyst.db as dbmod
from analyst import QueryRejected, ReadOnlyDB


@pytest.fixture(scope="module")
def db():
    return ReadOnlyDB()


def test_select_returns_rows(db):
    result = db.query("SELECT status, COUNT(*) AS n FROM orders GROUP BY status ORDER BY status")
    assert result.columns == ["status", "n"]
    assert dict(result.rows) == {"CANCELLED": 46, "COMPLETED": 546, "PENDING": 67, "SHIPPED": 141}


@pytest.mark.parametrize("sql", [
    "DELETE FROM orders",
    "UPDATE products SET unit_price = 0",
    "DROP TABLE customers",
    "INSERT INTO returns VALUES (1, 1, '2024-01-01', 'x', 1)",
    "PRAGMA table_info(orders)",
    "ATTACH DATABASE 'evil.db' AS evil",
])
def test_non_select_statements_are_rejected(db, sql):
    with pytest.raises(QueryRejected):
        db.query(sql)


def test_multiple_statements_are_rejected(db):
    with pytest.raises(QueryRejected, match="one statement"):
        db.query("SELECT 1; DELETE FROM orders")


def test_write_disguised_as_with_is_blocked_by_the_authorizer(db):
    # starts with WITH, so it passes the prefix check, but the authorizer denies the DELETE
    with pytest.raises(QueryRejected):
        db.query("WITH x AS (SELECT 1) DELETE FROM orders WHERE order_id IN (SELECT * FROM x)")
    assert db.query("SELECT COUNT(*) FROM orders").rows[0][0] == 800        # nothing was deleted


def test_results_are_capped(db):
    result = db.query("SELECT order_id FROM orders")
    assert len(result.rows) == dbmod.MAX_ROWS and result.truncated
    assert "truncated" in result.as_text()


def test_runaway_queries_are_stopped(db, monkeypatch):
    monkeypatch.setattr(dbmod, "MAX_SECONDS", 0.2)
    with pytest.raises(QueryRejected, match="longer than"):
        db.query("WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM c) SELECT COUNT(*) FROM c")


def test_bad_sql_message_is_returned_not_raised_raw(db):
    with pytest.raises(QueryRejected, match="no such column"):
        db.query("SELECT nonexistent FROM orders")