#!/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
# ------------------------------------------------------------------
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)

IN_LIST="'$(printf "%s" "${DEVICE_IDS[*]}" | sed "s/ /','/g")'"

# ------------------------------------------------------------------
# MySQL aggregation
# ------------------------------------------------------------------
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"; 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 (Korean fixed)
# ------------------------------------------------------------------
PROMPT=$(cat <<EOF
너는 **베트남 달랏 지역을 총괄하는 농업 담당자용 AI 분석가**다.

⚠️ 중요 규칙:
- 모든 설명, 요약, 문장, 배열 값은 **반드시 한국어로만 작성**
- 영어, 베트남어, 혼합 언어 사용 금지
- JSON key 는 유지하되, value 는 전부 한국어

최근 ${DAYS}일간의 온도·습도 데이터를 기반으로
각 농가의 상태를 평가하고,
관리자가 바로 행동할 수 있도록 **판단 기준 중심**으로 정리하라.

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

아래 JSON 스키마로만 응답하라 (추가 텍스트 금지).
JSON 내부 모든 value 문자열은 한국어여야 한다.

{
  "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":"...","evidence":"..."}]
  },
  "decision_criteria":{
    "rules":[{"name":"...","applies_to":"딸기|와사비|공통","thresholds":"...","rationale":"..."}],
    "data_quality_policy":"..."
  }
}
EOF
)

# ------------------------------------------------------------------
# OpenAI call
# ------------------------------------------------------------------
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 "너는 한국어로만 응답하는 농업 AI 분석가다." \
    --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"
      exit 0
    fi
  fi
  sleep 2
done

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