#  ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
#  ┃ ██████ ██████ ██████       █      █      █      █      █ █▄  ▀███ █       ┃
#  ┃ ▄▄▄▄▄█ █▄▄▄▄▄ ▄▄▄▄▄█  ▀▀▀▀▀█▀▀▀▀▀ █ ▀▀▀▀▀█ ████████▌▐███ ███▄  ▀█ █ ▀▀▀▀▀ ┃
#  ┃ █▀▀▀▀▀ █▀▀▀▀▀ █▀██▀▀ ▄▄▄▄▄ █ ▄▄▄▄▄█ ▄▄▄▄▄█ ████████▌▐███ █████▄   █ ▄▄▄▄▄ ┃
#  ┃ █      ██████ █  ▀█▄       █ ██████      █      ███▌▐███ ███████▄ █       ┃
#  ┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
#  ┃ Copyright (c) 2017, the Perspective Authors.                              ┃
#  ┃ ╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌ ┃
#  ┃ This file is part of the Perspective library, distributed under the terms ┃
#  ┃ of the [Apache License 2.0](https://www.apache.org/licenses/LICENSE-2.0). ┃
#  ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛

import os
import tempfile
import urllib.request

import pytest
import duckdb

from perspective import Client
from perspective.virtual_servers.duckdb import DuckDBVirtualServer

_SUPERSTORE_LOCAL = os.path.join(
    os.path.dirname(__file__),
    "..",
    "..",
    "..",
    "node_modules",
    "superstore-arrow",
    "superstore.parquet",
)

_SUPERSTORE_URL = (
    "https://cdn.jsdelivr.net/npm/superstore-arrow@3.2.0/superstore.parquet"
)


def _get_superstore_parquet():
    if os.path.exists(_SUPERSTORE_LOCAL):
        return _SUPERSTORE_LOCAL
    path = os.path.join(tempfile.gettempdir(), "superstore.parquet")
    if not os.path.exists(path):
        urllib.request.urlretrieve(_SUPERSTORE_URL, path)
    return path


SUPERSTORE_PARQUET = _get_superstore_parquet()


@pytest.fixture(scope="module")
def client():
    db = duckdb.connect()
    db.execute("SET default_null_order=NULLS_FIRST_ON_ASC_LAST_ON_DESC")
    db.execute(
        f"CREATE TABLE superstore AS SELECT * FROM read_parquet('{SUPERSTORE_PARQUET}')"
    )

    server = DuckDBVirtualServer(db)

    def handle_request(msg):
        session.handle_request(msg)

    def handle_response(msg):
        c.handle_response(msg)

    session = server.new_session(handle_response)
    c = Client(handle_request)
    return c


class TestDuckDBClient:
    def test_get_hosted_table_names(self, client):
        tables = client.get_hosted_table_names()
        assert tables == ["memory.superstore"]


class TestDuckDBTable:
    def test_schema(self, client):
        table = client.open_table("memory.superstore")
        schema = table.schema()
        assert schema == {
            "Product Name": "string",
            "Ship Date": "date",
            "City": "string",
            "Row ID": "integer",
            "Customer Name": "string",
            "Quantity": "integer",
            "Discount": "float",
            "Sub-Category": "string",
            "Segment": "string",
            "Category": "string",
            "Order Date": "date",
            "Order ID": "string",
            "Sales": "float",
            "State": "string",
            "Postal Code": "float",
            "Country": "string",
            "Customer ID": "string",
            "Ship Mode": "string",
            "Region": "string",
            "Profit": "float",
            "Product ID": "string",
        }

    def test_columns(self, client):
        table = client.open_table("memory.superstore")
        columns = table.columns()
        assert columns == [
            "Row ID",
            "Order ID",
            "Order Date",
            "Ship Date",
            "Ship Mode",
            "Customer ID",
            "Customer Name",
            "Segment",
            "Country",
            "City",
            "State",
            "Postal Code",
            "Region",
            "Product ID",
            "Category",
            "Sub-Category",
            "Product Name",
            "Sales",
            "Quantity",
            "Discount",
            "Profit",
        ]

    def test_size(self, client):
        table = client.open_table("memory.superstore")
        size = table.size()
        assert size == 9994


class TestDuckDBView:
    def test_num_rows(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Profit"])
        num_rows = view.num_rows()
        assert num_rows == 9994
        view.delete()

    def test_num_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Profit", "State"])
        num_columns = view.num_columns()
        assert num_columns == 3
        view.delete()

    def test_schema(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Profit", "State"])
        schema = view.schema()
        assert schema == {
            "Sales": "float",
            "Profit": "float",
            "State": "string",
        }
        view.delete()

    def test_to_json(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Quantity"])
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "Quantity": 2},
            {"Sales": 731.94, "Quantity": 3},
            {"Sales": 14.62, "Quantity": 2},
            {"Sales": 957.5775, "Quantity": 5},
            {"Sales": 22.368, "Quantity": 2},
        ]
        view.delete()

    def test_to_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Quantity"])
        columns = view.to_columns(start_row=0, end_row=5)
        assert columns == {
            "Sales": [261.96, 731.94, 14.62, 957.5775, 22.368],
            "Quantity": [2, 3, 2, 5, 2],
        }
        view.delete()

    def test_column_paths(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Profit", "State"])
        paths = view.column_paths()
        assert paths == ["Sales", "Profit", "State"]
        view.delete()


class TestDuckDBGroupBy:
    def test_single_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Region"],
            aggregates={"Sales": "sum"},
        )
        num_rows = view.num_rows()
        assert num_rows == 5
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 2297200.860299955},
            {"__ROW_PATH__": ["Central"], "Sales": 501239.8908000005},
            {"__ROW_PATH__": ["East"], "Sales": 678781.2399999979},
            {"__ROW_PATH__": ["South"], "Sales": 391721.9050000003},
            {"__ROW_PATH__": ["West"], "Sales": 725457.8245000006},
        ]
        view.delete()

    def test_multi_level_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Region", "Category"],
            aggregates={"Sales": "sum"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 2297200.860299955},
            {"__ROW_PATH__": ["Central"], "Sales": 501239.8908000005},
            {"__ROW_PATH__": ["Central", "Furniture"], "Sales": 163797.16380000004},
            {
                "__ROW_PATH__": ["Central", "Office Supplies"],
                "Sales": 167026.41500000027,
            },
            {"__ROW_PATH__": ["Central", "Technology"], "Sales": 170416.3119999999},
            {"__ROW_PATH__": ["East"], "Sales": 678781.2399999979},
            {"__ROW_PATH__": ["East", "Furniture"], "Sales": 208291.20400000009},
            {"__ROW_PATH__": ["East", "Office Supplies"], "Sales": 205516.0549999999},
            {"__ROW_PATH__": ["East", "Technology"], "Sales": 264973.9810000003},
            {"__ROW_PATH__": ["South"], "Sales": 391721.9050000003},
            {"__ROW_PATH__": ["South", "Furniture"], "Sales": 117298.6840000001},
            {"__ROW_PATH__": ["South", "Office Supplies"], "Sales": 125651.31299999992},
            {"__ROW_PATH__": ["South", "Technology"], "Sales": 148771.9079999999},
            {"__ROW_PATH__": ["West"], "Sales": 725457.8245000006},
            {"__ROW_PATH__": ["West", "Furniture"], "Sales": 252612.7435000003},
            {"__ROW_PATH__": ["West", "Office Supplies"], "Sales": 220853.24900000007},
            {"__ROW_PATH__": ["West", "Technology"], "Sales": 251991.83199999997},
        ]
        view.delete()

    def test_group_by_with_count_aggregate(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Region"],
            aggregates={"Sales": "count"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 9994},
            {"__ROW_PATH__": ["Central"], "Sales": 2323},
            {"__ROW_PATH__": ["East"], "Sales": 2848},
            {"__ROW_PATH__": ["South"], "Sales": 1620},
            {"__ROW_PATH__": ["West"], "Sales": 3203},
        ]
        view.delete()

    def test_group_by_with_avg_aggregate(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Category"],
            aggregates={"Sales": "avg"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 229.8580008304938},
            {"__ROW_PATH__": ["Furniture"], "Sales": 349.83488698727007},
            {"__ROW_PATH__": ["Office Supplies"], "Sales": 119.32410089611732},
            {"__ROW_PATH__": ["Technology"], "Sales": 452.70927612344155},
        ]
        view.delete()

    def test_group_by_with_min_aggregate(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Quantity"],
            group_by=["Region"],
            aggregates={"Quantity": "min"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Quantity": 1},
            {"__ROW_PATH__": ["Central"], "Quantity": 1},
            {"__ROW_PATH__": ["East"], "Quantity": 1},
            {"__ROW_PATH__": ["South"], "Quantity": 1},
            {"__ROW_PATH__": ["West"], "Quantity": 1},
        ]
        view.delete()

    def test_group_by_with_max_aggregate(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Quantity"],
            group_by=["Region"],
            aggregates={"Quantity": "max"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Quantity": 14},
            {"__ROW_PATH__": ["Central"], "Quantity": 14},
            {"__ROW_PATH__": ["East"], "Quantity": 14},
            {"__ROW_PATH__": ["South"], "Quantity": 14},
            {"__ROW_PATH__": ["West"], "Quantity": 14},
        ]
        view.delete()


class TestDuckDBSplitBy:
    def test_single_split_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            split_by=["Region"],
            group_by=["Category"],
            aggregates={"Sales": "sum"},
        )

        column_paths = view.column_paths()
        assert column_paths == [
            "Central_Sales",
            "East_Sales",
            "South_Sales",
            "West_Sales",
        ]

        json = view.to_json()
        assert json == [
            {
                "__ROW_PATH__": [],
                "Central|Sales": 501239.8908000005,
                "East|Sales": 678781.2399999979,
                "South|Sales": 391721.9050000003,
                "West|Sales": 725457.8245000006,
            },
            {
                "__ROW_PATH__": ["Furniture"],
                "Central|Sales": 163797.16380000004,
                "East|Sales": 208291.20400000009,
                "South|Sales": 117298.6840000001,
                "West|Sales": 252612.7435000003,
            },
            {
                "__ROW_PATH__": ["Office Supplies"],
                "Central|Sales": 167026.41500000027,
                "East|Sales": 205516.0549999999,
                "South|Sales": 125651.31299999992,
                "West|Sales": 220853.24900000007,
            },
            {
                "__ROW_PATH__": ["Technology"],
                "Central|Sales": 170416.3119999999,
                "East|Sales": 264973.9810000003,
                "South|Sales": 148771.9079999999,
                "West|Sales": 251991.83199999997,
            },
        ]
        view.delete()

    def test_split_by_without_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            split_by=["Category"],
        )
        paths = view.column_paths()
        assert any("Furniture" in c for c in paths)
        assert any("Office Supplies" in c for c in paths)
        assert any("Technology" in c for c in paths)
        view.delete()


class TestDuckDBFilter:
    def test_filter_with_equals(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Region"],
            filter=[["Region", "==", "West"]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 14.62, "Region": "West"},
            {"Sales": 48.86, "Region": "West"},
            {"Sales": 7.28, "Region": "West"},
            {"Sales": 907.152, "Region": "West"},
            {"Sales": 18.504, "Region": "West"},
        ]
        view.delete()

    def test_filter_with_not_equals(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Region"],
            filter=[["Region", "!=", "West"]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "Region": "South"},
            {"Sales": 731.94, "Region": "South"},
            {"Sales": 957.5775, "Region": "South"},
            {"Sales": 22.368, "Region": "South"},
            {"Sales": 15.552, "Region": "South"},
        ]
        view.delete()

    def test_filter_with_greater_than(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Quantity"],
            filter=[["Quantity", ">", 5]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 48.86, "Quantity": 7},
            {"Sales": 907.152, "Quantity": 6},
            {"Sales": 1706.184, "Quantity": 9},
            {"Sales": 665.88, "Quantity": 6},
            {"Sales": 19.46, "Quantity": 7},
        ]
        view.delete()

    def test_filter_with_less_than(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Quantity"],
            filter=[["Quantity", "<", 3]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "Quantity": 2},
            {"Sales": 14.62, "Quantity": 2},
            {"Sales": 22.368, "Quantity": 2},
            {"Sales": 55.5, "Quantity": 2},
            {"Sales": 8.56, "Quantity": 2},
        ]
        view.delete()

    def test_filter_with_greater_than_or_equal(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Quantity"],
            filter=[["Quantity", ">=", 10]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 40.096, "Quantity": 14},
            {"Sales": 43.12, "Quantity": 14},
            {"Sales": 384.45, "Quantity": 11},
            {"Sales": 3347.37, "Quantity": 13},
            {"Sales": 100.24, "Quantity": 10},
        ]
        view.delete()

    def test_filter_with_less_than_or_equal(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Quantity"],
            filter=[["Quantity", "<=", 2]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "Quantity": 2},
            {"Sales": 14.62, "Quantity": 2},
            {"Sales": 22.368, "Quantity": 2},
            {"Sales": 55.5, "Quantity": 2},
            {"Sales": 8.56, "Quantity": 2},
        ]
        view.delete()

    def test_filter_with_like(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "State"],
            filter=[["State", "LIKE", "Cal%"]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 14.62, "State": "California"},
            {"Sales": 48.86, "State": "California"},
            {"Sales": 7.28, "State": "California"},
            {"Sales": 907.152, "State": "California"},
            {"Sales": 18.504, "State": "California"},
        ]
        view.delete()

    def test_multiple_filters(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Region", "Quantity"],
            filter=[
                ["Region", "==", "West"],
                ["Quantity", ">", 3],
            ],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 48.86, "Region": "West", "Quantity": 7},
            {"Sales": 7.28, "Region": "West", "Quantity": 4},
            {"Sales": 907.152, "Region": "West", "Quantity": 6},
            {"Sales": 114.9, "Region": "West", "Quantity": 5},
            {"Sales": 1706.184, "Region": "West", "Quantity": 9},
        ]
        view.delete()

    def test_filter_with_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Category"],
            filter=[["Region", "==", "West"]],
            aggregates={"Sales": "sum"},
        )
        num_rows = view.num_rows()
        assert num_rows == 4
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 725457.8245000006},
            {"__ROW_PATH__": ["Furniture"], "Sales": 252612.7435000003},
            {"__ROW_PATH__": ["Office Supplies"], "Sales": 220853.24900000007},
            {"__ROW_PATH__": ["Technology"], "Sales": 251991.83199999997},
        ]
        view.delete()


class TestDuckDBSort:
    def test_sort_ascending(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Quantity"],
            sort=[["Sales", "asc"]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 0.444, "Quantity": 1},
            {"Sales": 0.556, "Quantity": 1},
            {"Sales": 0.836, "Quantity": 1},
            {"Sales": 0.852, "Quantity": 1},
            {"Sales": 0.876, "Quantity": 1},
        ]
        view.delete()

    def test_sort_descending(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Quantity"],
            sort=[["Sales", "desc"]],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 22638.48, "Quantity": 6},
            {"Sales": 17499.95, "Quantity": 5},
            {"Sales": 13999.96, "Quantity": 4},
            {"Sales": 11199.968, "Quantity": 4},
            {"Sales": 10499.97, "Quantity": 3},
        ]
        view.delete()

    def test_sort_with_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Region"],
            sort=[["Sales", "desc"]],
            aggregates={"Sales": "sum"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 2297200.860299955},
            {"__ROW_PATH__": ["West"], "Sales": 725457.8245000006},
            {"__ROW_PATH__": ["East"], "Sales": 678781.2399999979},
            {"__ROW_PATH__": ["Central"], "Sales": 501239.8908000005},
            {"__ROW_PATH__": ["South"], "Sales": 391721.9050000003},
        ]
        view.delete()

    def test_multi_column_sort(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Region", "Sales", "Quantity"],
            sort=[
                ["Region", "asc"],
                ["Sales", "desc"],
            ],
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Region": "Central", "Sales": 17499.95, "Quantity": 5},
            {"Region": "Central", "Sales": 9892.74, "Quantity": 13},
            {"Region": "Central", "Sales": 9449.95, "Quantity": 5},
            {"Region": "Central", "Sales": 8159.952, "Quantity": 8},
            {"Region": "Central", "Sales": 5443.96, "Quantity": 4},
        ]
        view.delete()


class TestDuckDBExpressions:
    def test_simple_expression(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "doublesales"],
            expressions={"doublesales": '"Sales" * 2'},
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "doublesales": 523.92},
            {"Sales": 731.94, "doublesales": 1463.88},
            {"Sales": 14.62, "doublesales": 29.24},
            {"Sales": 957.5775, "doublesales": 1915.155},
            {"Sales": 22.368, "doublesales": 44.736},
        ]
        view.delete()

    def test_expression_with_multiple_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Profit", "margin"],
            expressions={"margin": '"Profit" / "Sales"'},
        )
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "Profit": 41.9136, "margin": 0.16000000000000003},
            {"Sales": 731.94, "Profit": 219.582, "margin": 0.3},
            {"Sales": 14.62, "Profit": 6.8714, "margin": 0.47000000000000003},
            {"Sales": 957.5775, "Profit": -383.031, "margin": -0.4},
            {"Sales": 22.368, "Profit": 2.5164, "margin": 0.1125},
        ]
        view.delete()

    def test_expression_with_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["total"],
            group_by=["Region"],
            expressions={"total": '"Sales" + "Profit"'},
            aggregates={"total": "sum"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "total": 2583597.882000014},
            {"__ROW_PATH__": ["Central"], "total": 540946.2532999996},
            {"__ROW_PATH__": ["East"], "total": 770304.0199999991},
            {"__ROW_PATH__": ["South"], "total": 438471.33530000027},
            {"__ROW_PATH__": ["West"], "total": 833876.2733999988},
        ]
        view.delete()


class TestDuckDBViewport:
    def test_start_row_and_end_row(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Profit"])
        json = view.to_json(start_row=10, end_row=15)
        assert json == [
            {"Sales": 1706.184, "Profit": 85.3092},
            {"Sales": 911.424, "Profit": 68.3568},
            {"Sales": 15.552, "Profit": 5.4432},
            {"Sales": 407.976, "Profit": 132.5922},
            {"Sales": 68.81, "Profit": -123.858},
        ]
        view.delete()

    def test_start_col_and_end_col(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales", "Profit", "Quantity", "Discount"],
        )
        json = view.to_json(start_row=0, end_row=5, start_col=1, end_col=3)
        assert json == [
            {"Profit": 41.9136, "Quantity": 2},
            {"Profit": 219.582, "Quantity": 3},
            {"Profit": 6.8714, "Quantity": 2},
            {"Profit": -383.031, "Quantity": 5},
            {"Profit": 2.5164, "Quantity": 2},
        ]
        view.delete()


class TestDuckDBDataTypes:
    def test_integer_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Quantity"])
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Quantity": 2},
            {"Quantity": 3},
            {"Quantity": 2},
            {"Quantity": 5},
            {"Quantity": 2},
        ]
        view.delete()

    def test_float_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales", "Profit"])
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Sales": 261.96, "Profit": 41.9136},
            {"Sales": 731.94, "Profit": 219.582},
            {"Sales": 14.62, "Profit": 6.8714},
            {"Sales": 957.5775, "Profit": -383.031},
            {"Sales": 22.368, "Profit": 2.5164},
        ]
        view.delete()

    def test_string_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Region", "State", "City"])
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Region": "South", "State": "Kentucky", "City": "Henderson"},
            {"Region": "South", "State": "Kentucky", "City": "Henderson"},
            {"Region": "West", "State": "California", "City": "Los Angeles"},
            {"Region": "South", "State": "Florida", "City": "Fort Lauderdale"},
            {"Region": "South", "State": "Florida", "City": "Fort Lauderdale"},
        ]
        view.delete()

    def test_date_columns(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Order Date"])
        json = view.to_json(start_row=0, end_row=5)
        assert json == [
            {"Order Date": 1478563200000},
            {"Order Date": 1478563200000},
            {"Order Date": 1465689600000},
            {"Order Date": 1444521600000},
            {"Order Date": 1444521600000},
        ]
        view.delete()


class TestDuckDBCombinedOperations:
    def test_group_by_filter_sort(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Category"],
            filter=[["Region", "==", "West"]],
            sort=[["Sales", "desc"]],
            aggregates={"Sales": "sum"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "Sales": 725457.8245000006},
            {"__ROW_PATH__": ["Furniture"], "Sales": 252612.7435000003},
            {"__ROW_PATH__": ["Technology"], "Sales": 251991.83199999997},
            {"__ROW_PATH__": ["Office Supplies"], "Sales": 220853.24900000007},
        ]
        view.delete()

    def test_split_by_group_by_filter(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Category"],
            split_by=["Region"],
            filter=[["Quantity", ">", 3]],
            aggregates={"Sales": "sum"},
        )

        paths = view.column_paths()
        assert paths == [
            "Central_Sales",
            "East_Sales",
            "South_Sales",
            "West_Sales",
        ]

        num_rows = view.num_rows()
        assert num_rows == 4

        json = view.to_json()
        assert json == [
            {
                "__ROW_PATH__": [],
                "Central|Sales": 332883.0567999998,
                "East|Sales": 455143.735,
                "South|Sales": 274208.7699999999,
                "West|Sales": 470561.28350000136,
            },
            {
                "__ROW_PATH__": ["Furniture"],
                "Central|Sales": 111457.73279999988,
                "East|Sales": 140376.95899999997,
                "South|Sales": 80859.618,
                "West|Sales": 165219.5734999998,
            },
            {
                "__ROW_PATH__": ["Office Supplies"],
                "Central|Sales": 103937.78599999992,
                "East|Sales": 135823.893,
                "South|Sales": 84393.3579999999,
                "West|Sales": 140206.93099999975,
            },
            {
                "__ROW_PATH__": ["Technology"],
                "Central|Sales": 117487.53800000002,
                "East|Sales": 178942.883,
                "South|Sales": 108955.79400000005,
                "West|Sales": 165134.77900000007,
            },
        ]
        view.delete()

    def test_split_by_only(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            split_by=["Region"],
            filter=[["Quantity", ">", 3]],
        )

        paths = view.column_paths()
        assert paths == [
            "Central_Sales",
            "East_Sales",
            "South_Sales",
            "West_Sales",
        ]

        num_rows = view.num_rows()
        assert num_rows == 4284

        json = view.to_json(start_row=0, end_row=1)
        assert json == [
            {
                "Central|Sales": None,
                "East|Sales": None,
                "South|Sales": 957.5775,
                "West|Sales": None,
            },
        ]
        view.delete()

    def test_expressions_group_by_sort(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["profitmargin"],
            group_by=["Region"],
            expressions={"profitmargin": '"Profit" / "Sales" * 100'},
            sort=[["profitmargin", "desc"]],
            aggregates={"profitmargin": "avg"},
        )
        json = view.to_json()
        assert json == [
            {"__ROW_PATH__": [], "profitmargin": 12.031392972104467},
            {"__ROW_PATH__": ["West"], "profitmargin": 21.948661793784012},
            {"__ROW_PATH__": ["East"], "profitmargin": 16.722695960406636},
            {"__ROW_PATH__": ["South"], "profitmargin": 16.35190329218107},
            {"__ROW_PATH__": ["Central"], "profitmargin": -10.407293926323575},
        ]
        view.delete()


class TestDuckDBMinMax:
    def test_min_max_integer(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Quantity"])
        min_val, max_val = view.get_min_max("Quantity")
        assert min_val == 1
        assert max_val == 14
        view.delete()

    def test_min_max_float(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Sales"])
        min_val, max_val = view.get_min_max("Sales")
        assert min_val == 0.444
        assert max_val == 22638.48
        view.delete()

    def test_min_max_string(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(columns=["Category"])
        min_val, max_val = view.get_min_max("Category")
        assert min_val == "Furniture"
        assert max_val == "Technology"
        view.delete()

    def test_min_max_with_group_by(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Sales"],
            group_by=["Region"],
            aggregates={"Sales": "sum"},
        )
        min_val, max_val = view.get_min_max("Sales")
        assert min_val > 0
        assert max_val > 0
        assert max_val >= min_val
        view.delete()

    def test_min_max_with_filter(self, client):
        table = client.open_table("memory.superstore")
        view = table.view(
            columns=["Quantity"],
            filter=[["Quantity", ">", 10]],
        )
        min_val, max_val = view.get_min_max("Quantity")
        assert min_val >= 11
        assert max_val == 14
        view.delete()
