#!/bin/bash
set -euo pipefail

# NS20 Multi-Farm AI Manager Report (Da Lat)
# - 여러 농가(딸기/와사비) 환경데이터를 묶어 "농업담당자용 판단기준" JSON 생성
# - 입력: MySQL(ns20_1) temp/humidity(20분) + GPS
# - 출력: reports/manager_reports/manager_YYYYmmdd_HHMM.json

BASE_DIR="$(cd "$(dirname "$0")" && pwd)"
REPORT_DIR="$BASE_DIR/reports/manager_reports"
RAW_DIR="$BASE_DIR/raw/manager_raw"
mkdir -p "$REPORT_DIR" "$RAW_DIR"

source "$BASE_DIR/openai_connect.inc"

DAYS="${1:-7}"
DT_FMT='%Y-%m-%d %H:%i:%s'
NOW_TS="$(date +"%Y%m%d_%H%M")"
OUT_FILE="manager_${NOW_TS}.json"
RAW_FILE="$RAW_DIR/openai_raw_manager_${NOW_TS}.json"

need(){ command -v "$1" >/dev/null 2>&1 || { echo "[ERROR] missing $1"; exit 1; }; }
need mysql; need jq; need curl

# device list: prefer openai_connect.inc mapping keys
DEVICE_IDS=()
if declare -p FARM_NAME >/dev/null 2>&1; then
  while IFS= read -r k; do DEVICE_IDS+=("$k"); done < <(printf "%s\n" "${!FARM_NAME[@]}" | sort)
fi
[ ${#DEVICE_IDS[@]} -gt 0 ] || DEVICE_IDS=(ns20_002 ns20_003 ns20_004 ns20_005)

# MySQL: farm stats (varchar datetime/temp/humidity safe casting)
IN_LIST="'$(printf "%s" "${DEVICE_IDS[*]}" | sed "s/ /','/g")'"
SQL=$(cat <<SQL
WITH base AS (
  SELECT
    deviceid,
    STR_TO_DATE(datetime,'$DT_FMT') AS dt,
    CAST(NULLIF(temp,'') AS DECIMAL(10,2)) AS t,
    CAST(NULLIF(humidity,'') AS DECIMAL(10,2)) AS h,
    lat,lng,alt
  FROM ns20_1
  WHERE deviceid IN ($IN_LIST)
    AND STR_TO_DATE(datetime,'$DT_FMT') >= (NOW() - INTERVAL ${DAYS} DAY)
), latest AS (
  SELECT b.* FROM base b
  JOIN (SELECT deviceid, MAX(dt) maxdt FROM base GROUP BY deviceid) m
    ON b.deviceid=m.deviceid AND b.dt=m.maxdt
), agg AS (
  SELECT
    deviceid,
    COUNT(*) n,
    AVG(t) t_avg, MIN(t) t_min, MAX(t) t_max, STDDEV_SAMP(t) t_std,
    AVG(h) h_avg, MIN(h) h_min, MAX(h) h_max, STDDEV_SAMP(h) h_std
  FROM base GROUP BY deviceid
), trend AS (
  SELECT
    deviceid,
    AVG(CASE WHEN dt >= (NOW()-INTERVAL 24 HOUR) THEN t END) t_24,
    AVG(CASE WHEN dt <  (NOW()-INTERVAL 24 HOUR) AND dt >= (NOW()-INTERVAL 48 HOUR) THEN t END) t_p24,
    AVG(CASE WHEN dt >= (NOW()-INTERVAL 24 HOUR) THEN h END) h_24,
    AVG(CASE WHEN dt <  (NOW()-INTERVAL 24 HOUR) AND dt >= (NOW()-INTERVAL 48 HOUR) THEN h END) h_p24
  FROM base GROUP BY deviceid
)
SELECT
  a.deviceid,
  IFNULL(l.dt,'') last_dt,
  IFNULL(l.lat,'') lat,
  IFNULL(l.lng,'') lng,
  IFNULL(l.alt,'') alt,
  a.n,
  ROUND((1-(a.n/(${DAYS}*24*3)))*100,1) miss_pct,
  ROUND(a.t_avg,1),ROUND(a.t_min,1),ROUND(a.t_max,1),ROUND(IFNULL(a.t_std,0),1),
  ROUND(a.h_avg,1),ROUND(a.h_min,1),ROUND(a.h_max,1),ROUND(IFNULL(a.h_std,0),1),
  ROUND(IFNULL(tr.t_24,0),1),ROUND(IFNULL(tr.t_p24,0),1),
  ROUND(IFNULL(tr.h_24,0),1),ROUND(IFNULL(tr.h_p24,0),1)
FROM agg a
LEFT JOIN latest l ON a.deviceid=l.deviceid
LEFT JOIN trend tr ON a.deviceid=tr.deviceid
ORDER BY a.deviceid;
SQL
)

ROWS=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" "$MYSQL_DB" -N -B -e "$SQL")
[ -n "$ROWS" ] || { echo "[ERROR] no rows. check datetime format or device ids"; exit 1; }

LINES=()
while IFS=$'\t' read -r deviceid last_dt lat lng alt n miss_pct \
  t_avg t_min t_max t_std h_avg h_min h_max h_std t24 tp24 h24 hp24; do
  farm="${FARM_NAME[$deviceid]:-$deviceid}"
  crop="${CROP_TYPE[$deviceid]:-unknown}"
  t_delta=$(awk -v a="$t24" -v b="$tp24" 'BEGIN{printf "%.1f", (a-b)}')
  h_delta=$(awk -v a="$h24" -v b="$hp24" 'BEGIN{printf "%.1f", (a-b)}')
  LINES+=("- ${deviceid} | ${farm} | ${crop} | last=${last_dt} | gps=(${lat},${lng},alt:${alt}) | samples=${n} | missing%=${miss_pct} | T(avg/min/max/std)=${t_avg}/${t_min}/${t_max}/${t_std} | H(avg/min/max/std)=${h_avg}/${h_min}/${h_max}/${h_std} | 24hΔ(T/H)=${t_delta}C/${h_delta}%")
done <<< "$ROWS"

PROMPT=$(cat <<EOF
너는 "베트남 달랏시 농업담당자"에게 여러 농가를 한눈에 보고하는 AI다.
최근 ${DAYS}일의 온도/습도 데이터로 각 농가의 현황(적정/주의/위험),
우선순위(오늘 어디를 먼저 점검할지), 24~48h 리스크를 예측하고,
무엇보다 "판단기준(Decision Criteria)"을 명시하라.

[농가 요약]
$(printf "%s\n" "${LINES[@]}")

아래 JSON 스키마로만 응답(추가 텍스트 금지):
{
  "generated_at":"YYYY-MM-DD HH:MM:SS",
  "location":"Vietnam - Da Lat",
  "period_days":${DAYS},
  "portfolio_overview":{
    "overall_status":"good|watch|critical",
    "key_alerts":[{"deviceid":"...","severity":"watch|critical","title":"...","evidence":"수치근거","recommended_action":"..."}],
    "today_priorities":[{"rank":1,"deviceid":"...","why":"...","first_actions":["...","..."]}]
  },
  "farms":[
    {
      "deviceid":"...",
      "farm_name":"...",
      "crop_type":"딸기|와사비",
      "data_quality":{"missing_pct":0.0,"risk_note":"..."},
      "current_env_judgement":{"status":"적정|주의|위험","summary":"...","evidence":["...","..."]},
      "diagnosis_hypotheses":[{"hypothesis":"...","why":"...","checklist":["...","..."]}],
      "next_24_48h_outlook":{"risk_level":"low|medium|high","what_to_watch":["..."],"preventive_actions":["..."]}
    }
  ],
  "cross_farm_comparison":{
    "anomalies":[{"deviceid":"...","type":"temp_variability|humidity_variability|data_gap|trend_shift","evidence":"..."}]
  },
  "decision_criteria":{
    "rules":[{"name":"...","applies_to":"딸기|와사비|공통","thresholds":"...","rationale":"..."}],
    "data_quality_policy":"..."
  }
}
EOF
)

MAX_RETRY=3
for RETRY in $(seq 1 $MAX_RETRY); do
  echo "[INFO] OpenAI request attempt ${RETRY}/${MAX_RETRY}"

  REQUEST_JSON=$(jq -n \
    --arg model "$OPENAI_MODEL" \
    --arg system "You are an agricultural portfolio analyst for Da Lat, Vietnam." \
    --arg user "$PROMPT" \
    '{model:$model, messages:[{role:"system",content:$system},{role:"user",content:$user}], temperature:0.2}'
  )

  curl -s https://api.openai.com/v1/chat/completions \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer $OPENAI_API_KEY" \
    -d "$REQUEST_JSON" > "$RAW_FILE" || true

  if [ -s "$RAW_FILE" ] && ! jq -e '.error' "$RAW_FILE" >/dev/null 2>&1; then
    jq -r '.choices[0].message.content' "$RAW_FILE" | jq '.' > "$REPORT_DIR/$OUT_FILE" 2>/dev/null || true
    if [ -s "$REPORT_DIR/$OUT_FILE" ]; then
      echo "[DONE] manager report: $REPORT_DIR/$OUT_FILE"
      echo "[INFO] raw: $RAW_FILE"
      exit 0
    fi
  else
    echo "[WARN] OpenAI API error or empty response"
    [ -s "$RAW_FILE" ] && jq '.error.message' "$RAW_FILE" || true
  fi

  sleep 2
done

echo "[ERROR] OpenAI failed after ${MAX_RETRY} attempts"
exit 1