-- Migrated from DuckDB test style: test boolean aggregation
-- Test BOOL_AND and BOOL_OR functions
-- Test with boolean values
CREATE TABLE bool_test(b BOOLEAN, g INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO bool_test VALUES
    (true, 1, 1000), (true, 1, 2000), (true, 1, 3000),
    (false, 2, 4000), (true, 2, 5000), (false, 2, 6000),
    (NULL, 3, 7000), (true, 3, 8000);

Affected Rows: 8

-- Test BOOL_AND (all values must be true) and BOOL_OR (any value can be true)
 -- Should be true
SELECT bool_and(b) FROM bool_test WHERE g = 1;

+-----------------------+
| bool_and(bool_test.b) |
+-----------------------+
| true                  |
+-----------------------+

-- Should be false
SELECT bool_and(b) FROM bool_test WHERE g = 2;

+-----------------------+
| bool_and(bool_test.b) |
+-----------------------+
| false                 |
+-----------------------+

 -- Should be true (NULL ignored)
SELECT bool_and(b) FROM bool_test WHERE g = 3;

+-----------------------+
| bool_and(bool_test.b) |
+-----------------------+
| true                  |
+-----------------------+

 -- Should be true
SELECT bool_or(b) FROM bool_test WHERE g = 1;

+----------------------+
| bool_or(bool_test.b) |
+----------------------+
| true                 |
+----------------------+

-- Should be true
SELECT bool_or(b) FROM bool_test WHERE g = 2;

+----------------------+
| bool_or(bool_test.b) |
+----------------------+
| true                 |
+----------------------+

-- Should be true
SELECT bool_or(b) FROM bool_test WHERE g = 3;

+----------------------+
| bool_or(bool_test.b) |
+----------------------+
| true                 |
+----------------------+

-- Test with GROUP BY
SELECT g, bool_and(b), bool_or(b) FROM bool_test GROUP BY g ORDER BY g;

+---+-----------------------+----------------------+
| g | bool_and(bool_test.b) | bool_or(bool_test.b) |
+---+-----------------------+----------------------+
| 1 | true                  | true                 |
| 2 | false                 | true                 |
| 3 | true                  | true                 |
+---+-----------------------+----------------------+

-- Test all true values
CREATE TABLE all_true(b BOOLEAN, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO all_true VALUES (true, 1000), (true, 2000), (true, 3000);

Affected Rows: 3

SELECT bool_and(b), bool_or(b) FROM all_true;

+----------------------+---------------------+
| bool_and(all_true.b) | bool_or(all_true.b) |
+----------------------+---------------------+
| true                 | true                |
+----------------------+---------------------+

-- Test all false values
CREATE TABLE all_false(b BOOLEAN, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO all_false VALUES (false, 1000), (false, 2000), (false, 3000);

Affected Rows: 3

SELECT bool_and(b), bool_or(b) FROM all_false;

+-----------------------+----------------------+
| bool_and(all_false.b) | bool_or(all_false.b) |
+-----------------------+----------------------+
| false                 | false                |
+-----------------------+----------------------+

-- Test all NULL values
CREATE TABLE all_null(b BOOLEAN, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO all_null VALUES (NULL, 1000), (NULL, 2000), (NULL, 3000);

Affected Rows: 3

SELECT bool_and(b), bool_or(b) FROM all_null;

+----------------------+---------------------+
| bool_and(all_null.b) | bool_or(all_null.b) |
+----------------------+---------------------+
|                      |                     |
+----------------------+---------------------+

-- Test empty result
SELECT bool_and(b), bool_or(b) FROM bool_test WHERE g > 100;

+-----------------------+----------------------+
| bool_and(bool_test.b) | bool_or(bool_test.b) |
+-----------------------+----------------------+
|                       |                      |
+-----------------------+----------------------+

-- Test with integer expressions (converted to boolean)
CREATE TABLE int_test(i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO int_test VALUES (0, 1000), (1, 2000), (2, 3000), (NULL, 4000);

Affected Rows: 4

SELECT bool_and(i > 0), bool_or(i > 1) FROM int_test;

+---------------------------------+--------------------------------+
| bool_and(int_test.i > Int64(0)) | bool_or(int_test.i > Int64(1)) |
+---------------------------------+--------------------------------+
| false                           | true                           |
+---------------------------------+--------------------------------+

-- cleanup
DROP TABLE bool_test;

Affected Rows: 0

DROP TABLE all_true;

Affected Rows: 0

DROP TABLE all_false;

Affected Rows: 0

DROP TABLE all_null;

Affected Rows: 0

DROP TABLE int_test;

Affected Rows: 0