#!/bin/bash
# SEAP historical XLSX importer.
# Downloads an xlsx from data.gov.ro, converts to CSV via openpyxl,
# then hands it to import-seap-historical.py + the same TSV+psql flow.
#
# Usage: ./import-seap-xlsx.sh URL TYPE SOURCE [DELETE_FIRST]

set -euo pipefail

URL="$1"
TYPE="$2"
SOURCE="$3"
DELETE_FIRST="${4:-no}"

WORK=/tmp/seap-xlsx-$$
mkdir -p "$WORK"
trap "rm -rf $WORK" EXIT

XLSX="$WORK/data.xlsx"
CSV="$WORK/data.csv"
TSV="$WORK/data.tsv"
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"

echo "[xlsx-import] downloading: $URL"
curl -sk --max-time 600 -L "$URL" -o "$XLSX"
echo "[xlsx-import] downloaded: $(stat -c %s "$XLSX") bytes"

echo "[xlsx-import] xlsx → csv..."
python3 "$SCRIPT_DIR/xlsx-to-csv.py" "$XLSX" "$CSV"
echo "[xlsx-import] csv: $(stat -c %s "$CSV") bytes"

echo "[xlsx-import] normalizing CSV → TSV..."
python3 "$SCRIPT_DIR/import-seap-historical.py" "$CSV" "$TSV" "$TYPE" "$SOURCE"

echo "[xlsx-import] copying TSV to satra..."
scp -q "$TSV" "satra:/tmp/seap-historical.tsv"

DELETE_SQL=""
if [ "$DELETE_FIRST" = "yes" ]; then
  DELETE_SQL="DELETE FROM seap.announcements WHERE source = '$SOURCE';"
fi

ssh satra "/tmp/baseline.sh <<SQL
$DELETE_SQL
CREATE TEMP TABLE _stage_seap_hist (
  type text, ref_number text, authority_name text, authority_cui text,
  cpv_code text, cpv_name text, contract_type text, publication_date text,
  contract_date text, awarded_value text, supplier_name text, supplier_cui text,
  procedure_type text, legislation text, source text
);
\\COPY _stage_seap_hist FROM '/tmp/seap-historical.tsv' WITH (FORMAT text, DELIMITER E'\\t', HEADER true);
INSERT INTO seap.announcements (
  type, ref_number, authority_name, authority_cui, cpv_code, cpv_name,
  contract_type, publication_date, contract_date, awarded_value,
  supplier_name, supplier_cui, procedure_type, legislation, source
)
SELECT type, ref_number, authority_name, authority_cui, cpv_code, cpv_name,
       contract_type,
       NULLIF(publication_date, '')::timestamptz,
       NULLIF(contract_date, '')::date,
       NULLIF(awarded_value, '')::numeric,
       supplier_name, supplier_cui, procedure_type, legislation, source
FROM _stage_seap_hist
ON CONFLICT (type, ref_number) DO NOTHING;
SELECT '$SOURCE' AS source, COUNT(*) AS rows,
       MIN(publication_date)::date AS oldest,
       MAX(publication_date)::date AS newest,
       SUM(awarded_value)::bigint AS total_lei
FROM seap.announcements WHERE source = '$SOURCE';
SQL"

ssh satra "rm -f /tmp/seap-historical.tsv"
echo "[xlsx-import] done."