# /// script
# requires-python = ">=3.12,<3.14"
# dependencies = ["duckdb==1.4.3"]
# ///
import hashlib
import json
from pathlib import Path

import duckdb

folder = Path(__file__).resolve().parent
fixture = json.loads((folder / 'queries.json').read_text())
file = folder / 'weather.parquet'
assert hashlib.sha256(file.read_bytes()).hexdigest() == fixture['file']['sha256']
con = duckdb.connect()
con.read_parquet(str(file)).create_view('data')
con.execute("CREATE VIEW stations AS SELECT * FROM (VALUES ('North','Northern site'),('South','Southern site')) AS lookup(station,description)")
for query in fixture['queries']:
    rows = [list(row) for row in con.execute('SELECT COLUMNS(*)::VARCHAR FROM (' + query['sql'] + ')').fetchall()]
    assert rows == query['rows'], query['id']
    print(query['label'], rows)
    print(con.execute('EXPLAIN ' + query['sql']).fetchone()[1])
    print(con.execute('EXPLAIN ANALYZE ' + query['sql']).fetchone()[1])