-- Build calcofi_ctd.db: DuckDB VIEWS that denormalize the long CTD tables
-- (ctd_thin, ctd_measurement) by joining to ctd_cast for time/lat/lon, and
-- expose ERDDAP-canonical columns (epoch-seconds `time`, double lat/lon/depth).
-- The .db stores only view definitions (no data copied). Paths are absolute and
-- identity-mounted into the bench ERDDAP container, so they resolve identically
-- where this .db is built (host) and where ERDDAP reads it (container).
--
-- NB: connection tunables (memory_limit/threads/temp_directory/read_only) are
-- NOT persisted in the .db — ERDDAP passes them as JDBC <connectionProperty>.

CREATE OR REPLACE VIEW ctd_thin_erddap AS
SELECT
  t.ctd_cast_uuid,
  c.datetime_start_utc AS time,   -- real TIMESTAMP: ERDDAP EDVTimeStamp reads rs.getTimestamp()
  c.latitude::DOUBLE                  AS latitude,
  c.longitude::DOUBLE                 AS longitude,
  t.depth_m::DOUBLE                   AS depth,
  c.cruise_key,
  c.site_key,
  c.line,
  c.sta,
  t.measurement_type,
  t.measurement_value::DOUBLE         AS measurement_value,
  t.measurement_qual,
  t.cast_dir,
  t.retained_reason,
  t.ctd_thin_uuid
FROM read_parquet('/share/data/erddap-duckdb/datasets/ctd_thin/**/*.parquet', hive_partitioning = true) t
JOIN read_parquet('/share/data/erddap-duckdb/datasets/ctd_cast.parquet') c USING (ctd_cast_uuid);

CREATE OR REPLACE VIEW ctd_measurement_erddap AS
SELECT
  m.ctd_cast_uuid,
  c.datetime_start_utc AS time,   -- real TIMESTAMP: ERDDAP EDVTimeStamp reads rs.getTimestamp()
  c.latitude::DOUBLE                  AS latitude,
  c.longitude::DOUBLE                 AS longitude,
  m.depth_m::DOUBLE                   AS depth,
  c.cruise_key,
  c.site_key,
  c.line,
  c.sta,
  m.measurement_type,
  m.measurement_value::DOUBLE         AS measurement_value,
  m.measurement_qual,
  m.ctd_measurement_uuid
FROM read_parquet('/share/data/erddap-duckdb/datasets/ctd_measurement/**/*.parquet', hive_partitioning = true) m
JOIN read_parquet('/share/data/erddap-duckdb/datasets/ctd_cast.parquet') c USING (ctd_cast_uuid);
