# file_id, ocr, title, date, author, page_count, word_count, character_count

echo """
CREATE TABLE presse AS (
SELECT title
     , author
     , LPAD((REGEXP_EXTRACT(date, '1[0-9][0-9][0-9]') || '-01-01'), 10, '0')::DATE AS year
  FROM read_parquet([""" > $TMPDIR/presse.sql 

for i in $(seq 1 320); do
  echo "    'https://huggingface.co/datasets/PleIAs/French-PD-Newspapers/resolve/main/gallica_presse_$i.parquet'," >> $TMPDIR/presse.sql
done

echo """  ])
);

COPY presse TO '$TMPDIR/presse.parquet' (FORMAT 'parquet', COMPRESSION 'GZIP');
""" >> $TMPDIR/presse.sql

duckdb < $TMPDIR/presse.sql

# isatty
if [ -t 1 ]; then
  echo parquet file output at: $TMPDIR/presse.parquet
  echo "duckdb -csv :memory: \"SELECT * FROM '$TMPDIR/presse.parquet'\""
else
  cat $TMPDIR/presse.parquet
  rm $TMPDIR/presse.parquet
fi