# same thing in pure duckdb / SQL

import duckdb as ddb

con = ddb.connect('test4.db', config=dict(max_memory='500MB'))  # Create a DuckDB connection
con.install_extension("spatial")
con.load_extension("spatial")

con.sql(f"""
CREATE OR REPLACE VIEW fires AS (
    SELECT CAST(fires.ALARM_DATE AS DATE) AS alarm_date, CAST(fires.CONT_DATE AS DATE) AS control_date, fires.geometry as boundary
    FROM 'https://minio.carlboettiger.info/public-fire/calfire-2023.parquet' AS fires
    WHERE fires.YEAR_ > 2018
)
""")


con.sql(f"""
CREATE OR REPLACE VIEW nps AS (
    SELECT geometry, MAKE_DATE(2020, 1, 1) AS "start_date", MAKE_DATE(2023, 12, 31) AS "end_date"
    FROM (
      SELECT ST_TRANSFORM(nps.SHAPE, 'EPSG:3857', 'EPSG:4326', TRUE) AS "geometry"
      FROM st_read("/vsicurl/https://minio.carlboettiger.info/public-biodiversity/NPS.gdb") AS nps
      WHERE nps.PARKNAME = 'Joshua Tree'
    )
)
""")

x = con.sql(f"""
SELECT * 
FROM fires 
INNER JOIN nps ON (
  ST_WITHIN(fires.boundary, nps.geometry)
  AND fires.alarm_date BETWEEN nps.start_date AND nps.end_date
  )
""")


x.df()