#!/bin/bash
# 匯入 Day3 自動蒐集的 evidence_E001/E002.auto.csv 到 MySQL，並轉正到正式 evidence 表
# 使用前提：
#   1. MySQL 已啟動，且 local_infile=1
#   2. 資料庫 mymall 已建立，帳號 root 無密碼（如果有密碼要自己加 -pYOURPASS）
#   3. 已經建好 schema.sql（含 evidence_staging）

set -euo pipefail
DB="mymall"
MYSQL="/opt/homebrew/opt/mysql@8.4/bin/mysql"
CSV_DIR="/Users/a18888/mywork/day3_auto_ingest/outputs"
echo "=== 匯入 E001 auto.csv 到 staging ==="
$MYSQL --local-infile=1 -uroot $DB <<EOF
LOAD DATA LOCAL INFILE '${CSV_DIR}/evidence_E001.auto.csv'
INTO TABLE evidence_staging
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(effect_id, food_name, endpoint, direction, grade, reference, title);
EOF

echo "=== 匯入 E002 auto.csv 到 staging ==="
$MYSQL --local-infile=1 -uroot $DB <<EOF
LOAD DATA LOCAL INFILE '${CSV_DIR}/evidence_E002.auto.csv'
INTO TABLE evidence_staging
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(effect_id, food_name, endpoint, direction, grade, reference, title);
EOF

echo "=== 確保 effects 有 E001/E002 ==="
$MYSQL -uroot $DB <<'EOF'
INSERT INTO effects(effect_id, effect_name, category) VALUES
('E001','烏髮（減少白髮/促黑色素）','皮膚毛髮'),
('E002','抗皺（細紋/彈性）','皮膚抗老')
ON DUPLICATE KEY UPDATE effect_name=VALUES(effect_name), category=VALUES(category);

echo "=== food ==="
$MYSQL -uroot $DB <<'EOF'
INSERT IGNORE INTO foods(name)
SELECT DISTINCT food_name FROM evidence_staging;

echo "=== 將 staging 轉正到正式 evidence ==="
$MYSQL -uroot "$DB" <<'EOF'
INSERT INTO evidence (
  effect_id, food_id, endpoint, direction, magnitude, grade,
  study_type, dosage, duration, population, reference, note
)
SELECT
  s.effect_id, f.food_id, s.endpoint, s.direction,
  NULL, s.grade,
  NULL, NULL, NULL, NULL,
  s.reference, s.title
FROM evidence_staging s
JOIN foods f ON f.name = s.food_name;
EOF

echo "=== 匯入完成，檢查結果 ==="
$MYSQL -uroot $DB -e "SELECT effect_id, COUNT(*) AS n_rows FROM evidence GROUP BY effect_id;"
$MYSQL -uroot $DB -e "SELECT f.name, COUNT(*) AS refs FROM foods f JOIN evidence e ON f.food_id=e.food_id GROUP BY f.name ORDER BY refs DESC;"

• 驗收

# 列出 effect 字典
curl "http://localhost:8080/api/effects"

# 取得抗皺（E002）的推薦（Day2 先回傳假資料）
curl "http://localhost:8080/api/recommendations?effectId=E002"

# 取得烏髮（E001）的推薦
curl "http://localhost:8080/api/recommendations?effectId=E001"