#!/usr/bin/env bash
# onx-db-slow-queries-explain — Verilen SELECT sorgusunu EXPLAIN ile cozumle
# + heuristik suggestion'lar ureti (index missing, full table scan vd.).
#
# Input:  {"query":"SELECT * FROM ..."}
# Output: {"query":"...","explain":[{...}],"suggestions":["..."]}

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "${SCRIPT_DIR}/_lib/common.sh"

require_root
require_cmd mysql
onx_json_input

QUERY="$(onx_json_field query '')"
[[ -z "$QUERY" ]] && onx_die 1 "query zorunlu"

# Guvenlik: sadece SELECT EXPLAIN'le (UPDATE/DELETE/DROP risk)
# Onbasin'da SELECT veya WITH (CTE) olmali
TRIMMED_LOWER="$(echo "$QUERY" | sed -E 's/^[[:space:]]+//' | head -c 20 | tr 'A-Z' 'a-z')"
case "$TRIMMED_LOWER" in
    select*|with*) : ;;
    *) onx_die 1 "Sadece SELECT/WITH sorgulari EXPLAIN edilebilir" ;;
esac

# Query uzunluk limiti
QUERY_LEN="${#QUERY}"
[[ "$QUERY_LEN" -gt 8192 ]] && onx_die 1 "Query cok uzun (max 8192 byte)"

onx_log "db-slow-queries-explain: query_len=${QUERY_LEN}"

# EXPLAIN FORMAT=JSON ile yapilandirilmis sonuc al (MariaDB 10.0.5+)
# JSON output parser-friendly. Fallback: tabular EXPLAIN.
EXPLAIN_OUTPUT="$(printf 'EXPLAIN FORMAT=JSON %s' "$QUERY" | mysql_exec_root_stdin "" 2>&1 || echo "")"

# Tabular fallback (basit parse icin)
EXPLAIN_TAB="$(printf 'EXPLAIN %s' "$QUERY" | mysql_exec_root_stdin "" 2>&1 || echo "")"

# EXPLAIN sonucu bos ya da error mi?
if [[ -z "$EXPLAIN_TAB" ]] || echo "$EXPLAIN_TAB" | grep -qi "^error"; then
    ERR_MSG="$(echo "$EXPLAIN_TAB" | tr -d '\n' | head -c 200)"
    json_ok "{\"query\":$(printf '%s' "$QUERY" | jq -Rs '.'),\"explain\":[],\"suggestions\":[\"EXPLAIN basarisiz: ${ERR_MSG}\"]}"
    exit 0
fi

# Tabular EXPLAIN'i JSON array'e cevir
# Sutunlar: id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN_JSON="["
sep=""
FULL_SCAN=0
NO_INDEX=0
TEMP_TABLE=0
FILESORT=0
TOTAL_ROWS=0

while IFS=$'\t' read -r id sel_type table type possible_keys key key_len ref rows extra; do
    # Header satirini atla
    [[ "$id" == "id" ]] && continue
    [[ -z "$id" ]] && continue
    rows_int="${rows:-0}"
    [[ ! "$rows_int" =~ ^[0-9]+$ ]] && rows_int=0
    TOTAL_ROWS=$((TOTAL_ROWS + rows_int))

    [[ "${type:-}" == "ALL" ]] && FULL_SCAN=1
    [[ -z "${key:-}" || "${key:-}" == "NULL" ]] && NO_INDEX=1
    echo "$extra" | grep -qi "temporary" && TEMP_TABLE=1
    echo "$extra" | grep -qi "filesort" && FILESORT=1

    EXPLAIN_JSON+="${sep}{\"id\":$(printf '%s' "${id:-null}" | jq -Rs '. | tonumber? // .'),\"select_type\":$(printf '%s' "${sel_type:-}" | jq -Rs '.'),\"table\":$(printf '%s' "${table:-}" | jq -Rs '.'),\"type\":$(printf '%s' "${type:-}" | jq -Rs '.'),\"possible_keys\":$(printf '%s' "${possible_keys:-}" | jq -Rs '.'),\"key\":$(printf '%s' "${key:-}" | jq -Rs '.'),\"key_len\":$(printf '%s' "${key_len:-}" | jq -Rs '.'),\"ref\":$(printf '%s' "${ref:-}" | jq -Rs '.'),\"rows\":${rows_int},\"extra\":$(printf '%s' "${extra:-}" | jq -Rs '.')}"
    sep=","
done <<< "$EXPLAIN_TAB"
EXPLAIN_JSON+="]"

# Suggestion'lar
SUGGESTIONS_JSON="["
sug_sep=""

if [[ "$FULL_SCAN" -eq 1 ]]; then
    SUGGESTIONS_JSON+="${sug_sep}\"⚠ Full table scan (type=ALL) — sorgu WHERE/JOIN icin index olusturmayi dusunun\""
    sug_sep=","
fi
if [[ "$NO_INDEX" -eq 1 ]]; then
    SUGGESTIONS_JSON+="${sug_sep}\"⚠ Index kullanilmiyor (key=NULL) — WHERE veya ORDER BY kolonlarinda index gerekli\""
    sug_sep=","
fi
if [[ "$TEMP_TABLE" -eq 1 ]]; then
    SUGGESTIONS_JSON+="${sug_sep}\"⚠ Using temporary — disk-on-memory tmp table; GROUP BY/ORDER BY kolonlarinda index dusunun\""
    sug_sep=","
fi
if [[ "$FILESORT" -eq 1 ]]; then
    SUGGESTIONS_JSON+="${sug_sep}\"⚠ Using filesort — sorted index eksik; ORDER BY kolonu icin index olusturun\""
    sug_sep=","
fi
if [[ "$TOTAL_ROWS" -gt 100000 ]]; then
    SUGGESTIONS_JSON+="${sug_sep}\"⚠ Tahmini ${TOTAL_ROWS} satir taranacak — selectivity dusuk, WHERE filtre kolonuna index ekleyin\""
    sug_sep=","
fi
if [[ "$FULL_SCAN" -eq 0 && "$NO_INDEX" -eq 0 && "$TEMP_TABLE" -eq 0 && "$FILESORT" -eq 0 ]]; then
    SUGGESTIONS_JSON+="${sug_sep}\"✓ EXPLAIN temiz — onemli bir issue tespit edilmedi\""
fi
SUGGESTIONS_JSON+="]"

json_ok "{\"query\":$(printf '%s' "$QUERY" | jq -Rs '.'),\"explain\":${EXPLAIN_JSON},\"suggestions\":${SUGGESTIONS_JSON}}"
