#!/usr/bin/env bash
set -euo pipefail

DB="${HOME}/.local/share/mindwtr/mindwtr.db"
[[ -f "$DB" ]] || { echo "Fehler: DB nicht gefunden: $DB" >&2; exit 1; }
command -v bc >/dev/null || { echo "bc fehlt" >&2; exit 1; }
command -v sqlite3 >/dev/null || { echo "sqlite3 fehlt" >&2; exit 1; }

RED='\033[0;31m'; GREEN='\033[0;32m'; YELLOW='\033[1;33m'
BLUE='\033[0;34m'; MAGENTA='\033[0;35m'; CYAN='\033[0;36m'
WHITE='\033[1;37m'; GRAY='\033[0;90m'; BOLD='\033[1m'; NC='\033[0m'

TMP="/tmp/mindwtr_stats_$$.tmp"
trap 'rm -f "$TMP"' EXIT

bar() {
  local p=${1:-0} w=${2:-30}
  [[ -z "$p" ]] && p=0; [[ -z "$w" ]] && w=30
  [[ ! "$p" =~ ^[0-9]+$ ]] && p=0; [[ ! "$w" =~ ^[0-9]+$ ]] && w=30
  local f=0 e=$w
  (( w > 0 && p > 0 )) && f=$((w * p / 100)) && e=$((w - f))
  local c=$GREEN; [[ $p -lt 30 ]] && c=$RED; [[ $p -ge 30 && $p -lt 70 ]] && c=$YELLOW
  if [[ $p -eq 0 ]]; then
    printf "${GRAY}%${w}s${NC}" '' | sed 's/ /░/g'
  else
    printf "${c}%${f}s${NC}${GRAY}%${e}s${NC}" '' '' | sed 's/ /█/g; s/ /░/g'
  fi
}

sparkline() {
  local -a v=("$@") max=0; for n in "${v[@]}"; do ((n>max)) && max=$n; done
  [[ $max -eq 0 ]] && max=1
  local chars=('▁' '▂' '▃' '▄' '▅' '▆' '▇' '█')
  for n in "${v[@]}"; do printf "%s" "${chars[n*7/max]}"; done
}

hr()  { printf "${GRAY}──────────────────────────────────────────────────────────────────────────────${NC}\n"; }
hr2() { printf "${GRAY}┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈${NC}\n"; }
hdr() { printf "\n${BOLD}${CYAN}%s${NC}\n" "$1"; hr; }

header() {
  clear
  printf "\n${BOLD}${WHITE}%s${NC}\n" "M I N D W T R   D A S H B O A R D"
  printf "${GRAY}%s${NC}\n\n" "$(date '+%A, %d. %B %Y')"
  hr
}

sqlite3 "$DB" -batch <<'SQL' > "$TMP"
.headers off
.mode list
.separator |
SELECT 'open' AS k, COUNT(*) AS v FROM tasks WHERE deletedAt IS NULL AND status!='done'
UNION ALL SELECT 'done', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='done'
UNION ALL SELECT 'total', COUNT(*) FROM tasks WHERE deletedAt IS NULL
UNION ALL SELECT 'inbox', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='inbox'
UNION ALL SELECT 'next', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='next'
UNION ALL SELECT 'someday', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='someday'
UNION ALL SELECT 'overdue', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='next' AND dueDate IS NOT NULL AND date(dueDate)<date('now')
UNION ALL SELECT 'due_today', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='next' AND dueDate IS NOT NULL AND date(dueDate)=date('now')
UNION ALL SELECT 'due_week', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='next' AND dueDate IS NOT NULL AND date(dueDate) BETWEEN date('now') AND date('now','+7 days')
UNION ALL SELECT 'due_next_week', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='next' AND dueDate IS NOT NULL AND date(dueDate) BETWEEN date('now','+1 day') AND date('now','+7 days')
UNION ALL SELECT 'active_projects', COUNT(*) FROM projects WHERE deletedAt IS NULL AND status='active'
UNION ALL SELECT 'list_tasks', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND taskMode='list'
UNION ALL SELECT 'single_tasks', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND taskMode='task'
UNION ALL SELECT 'done_week', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='done' AND date(completedAt)>=date('now','-7 days')
UNION ALL SELECT 'done_month', COUNT(*) FROM tasks WHERE deletedAt IS NULL AND status='done' AND date(completedAt)>=date('now','-30 days');

SELECT 'vel_'||date(completedAt) AS k, COUNT(*) AS v FROM tasks
WHERE deletedAt IS NULL AND status='done' AND completedAt IS NOT NULL
AND date(completedAt)>=date('now','-21 days')
GROUP BY date(completedAt) ORDER BY date(completedAt);

SELECT p.id, p.title, p.dueDate, COUNT(t.id), SUM(CASE WHEN t.status='done' THEN 1 ELSE 0 END)
FROM projects p LEFT JOIN tasks t ON t.projectId=p.id
WHERE p.deletedAt IS NULL AND p.status='active'
GROUP BY p.id, p.title, p.dueDate ORDER BY p.dueDate IS NULL, p.dueDate;

SELECT date(dueDate), COUNT(*), GROUP_CONCAT(title,' | ') FROM tasks
WHERE deletedAt IS NULL AND status='next' AND dueDate IS NOT NULL
AND date(dueDate) BETWEEN date('now') AND date('now','+30 days')
GROUP BY date(dueDate) ORDER BY date(dueDate);

SELECT t.title, COALESCE(p.title,'(kein Projekt)'), t.dueDate FROM tasks t
LEFT JOIN projects p ON t.projectId=p.id
WHERE t.deletedAt IS NULL AND t.status='next' AND t.dueDate IS NOT NULL AND date(t.dueDate)<date('now')
ORDER BY t.dueDate;

SELECT taskMode, COUNT(*), SUM(CASE WHEN status='done' THEN 1 ELSE 0 END), SUM(CASE WHEN status='next' THEN 1 ELSE 0 END), SUM(CASE WHEN status='someday' THEN 1 ELSE 0 END)
FROM tasks WHERE deletedAt IS NULL GROUP BY taskMode;
SQL

open=0; done_tasks=0; total=0; inbox=0; next_tasks=0; someday=0
overdue=0; due_today=0; due_week=0; due_next_week=0
active_projects=0; list_tasks=0; single_tasks=0
done_week=0; done_month=0

mapfile -t stats < <(grep -E '^(open|done|total|inbox|next|someday|overdue|due_today|due_week|due_next_week|active_projects|list_tasks|single_tasks|done_week|done_month)\|' "$TMP")
for line in "${stats[@]}"; do
  IFS='|' read -r k v <<< "$line"
  case "$k" in
    open) open=$v;; done) done_tasks=$v;; total) total=$v;;
    inbox) inbox=$v;; next) next_tasks=$v;; someday) someday=$v;;
    overdue) overdue=$v;; due_today) due_today=$v;; due_week) due_week=$v;;
    due_next_week) due_next_week=$v;;
    active_projects) active_projects=$v;;
    list_tasks) list_tasks=$v;; single_tasks) single_tasks=$v;;
    done_week) done_week=$v;; done_month) done_month=$v;;
  esac
done

vel_data=(); vel_labels=()
while IFS='|' read -r k v; do
  [[ $k == vel_* ]] && vel_data+=("$v") && vel_labels+=("$(date -d "${k#vel_}" '+%d.%m.' 2>/dev/null || echo "${k#vel_}")")
done < <(grep '^vel_' "$TMP")

projects=()
while IFS='|' read -r pid ptitle pdue ptotal pdone; do
  [[ -n $pid ]] && projects+=("$pid|$ptitle|$pdue|$ptotal|$pdone")
done < <(grep -E '^[a-f0-9-]{36}\|' "$TMP")

due_timeline=()
while IFS='|' read -r date count tasks; do
  [[ -n $date ]] && due_timeline+=("$date|$count|$tasks")
done < <(grep -E '^[0-9]{4}-[0-9]{2}-[0-9]{2}\|' "$TMP")

overdue_tasks=()
while IFS='|' read -r title project due; do
  [[ -n $title ]] && overdue_tasks+=("$title|$project|$due")
done < <(grep -E '^[^|]*\|[^|]*\|[0-9]{4}-[0-9]{2}-[0-9]{2}$' "$TMP")

mode_data=()
while IFS='|' read -r mode mtotal mdone_tasks mnext msomeday; do
  [[ -n $mode ]] && mode_data+=("$mode|$mtotal|$mdone_tasks|$mnext|$msomeday")
done < <(grep -E '^(task|list)\|' "$TMP")

header

hdr "ÜBERSICHT"
pct_total=0; [[ $total -gt 0 ]] && pct_total=$((done_tasks*100/total))
pct_week=0;  [[ $((done_week+open)) -gt 0 ]] && pct_week=$((done_week*100/(done_week+open)))
pct_month=0; [[ $((done_month+open)) -gt 0 ]] && pct_month=$((done_month*100/(done_month+open)))

b1=$(bar $pct_total 25)
b2=$(bar $pct_week 25)
b3=$(bar $pct_month 25)
printf "  %s\n" "$(printf "  ${BOLD}%-18s${NC} ${WHITE}%3d%%${NC} %s ${GRAY}(%d/%d)${NC}" "Gesamt" "$pct_total" "$b1" "$done_tasks" "$total")"
printf "  %s\n" "$(printf "  ${BOLD}%-18s${NC} ${WHITE}%3d%%${NC} %s ${GRAY}(%d/%d)${NC}" "Diese Woche" "$pct_week" "$b2" "$done_week" "$((done_week+open))")"
printf "  %s\n" "$(printf "  ${BOLD}%-18s${NC} ${WHITE}%3d%%${NC} %s ${GRAY}(%d/%d)${NC}" "Diesen Monat" "$pct_month" "$b3" "$done_month" "$((done_month+open))")"
printf "\n"
printf "  %s\n" "$(printf "  ${BOLD}Offen:${NC} ${WHITE}%d${NC}   ${YELLOW}Inbox:${NC} %d   ${BLUE}Next:${NC} %d   ${GRAY}Someday:${NC} %d" "$open" "$inbox" "$next_tasks" "$someday")"
printf "  %s\n" "$(printf "  ${RED}Überfällig:${NC} %d   ${YELLOW}Heute:${NC} %d   ${BLUE}Diese Woche:${NC} %d   ${CYAN}Nächste Woche:${NC} %d" "$overdue" "$due_today" "$due_week" "$due_next_week")"
printf "  %s\n" "$(printf "  ${GREEN}Aktive Projekte:${NC} %d   ${CYAN}Listen:${NC} %d   ${BLUE}Einzel:${NC} %d" "$active_projects" "$list_tasks" "$single_tasks")"

hdr "PROJEKT-GESUNDHEIT"
printf "  %s\n" "$(printf "  ${BOLD}%-30s %6s %s${NC}" "Projekt" "Fertig" "Fortschritt")"
hr2
for p in "${projects[@]}"; do
  IFS='|' read -r id title due ptotal pdone <<< "$p"
  pct=0; [[ $ptotal -gt 0 ]] && pct=$((pdone*100/ptotal))
  due_str=""
  if [[ -n "$due" && "$due" != "" ]]; then
    days=$(( ($(date -d "$due" +%s) - $(date +%s)) / 86400 ))
    if [[ $days -lt 0 ]]; then due_str="${RED}⚠ ${days#-}d überfällig${NC}"
    elif [[ $days -eq 0 ]]; then due_str="${YELLOW}⏰ heute${NC}"
    elif [[ $days -le 3 ]]; then due_str="${YELLOW}📅 in ${days}d${NC}"
    elif [[ $days -le 7 ]]; then due_str="${BLUE}📅 in ${days}d${NC}"
    else due_str="${GREEN}📅 in ${days}d${NC}"; fi
  fi
  bp=$(bar $pct 18)
  printf "  %s\n" "$(printf "  ${BOLD}%-30.30s${NC} ${WHITE}%3d%%${NC} %s  ${GRAY}(%d/%d)${NC} %s" "$title" "$pct" "$bp" "$pdone" "$ptotal" "$due_str")"
done
[[ ${#projects[@]} -eq 0 ]] && printf "  ${GRAY}(keine aktiven Projekte)${NC}\n"

hdr "ÜBERFÄLLIGE AUFGABEN"
if [[ ${#overdue_tasks[@]} -eq 0 ]]; then
  printf "  ${GREEN}✓ Keine überfälligen Aufgaben${NC}\n"
else
  printf "  ${RED}⚠ ${#overdue_tasks[@]} überfällige Aufgaben:${NC}\n"
  for t in "${overdue_tasks[@]}"; do
    IFS='|' read -r title project due <<< "$t"
    days=$(( ($(date +%s) - $(date -d "$due" +%s)) / 86400 ))
    printf "  %s\n" "$(printf "  ${RED}•${NC} ${WHITE}%-42.42s${NC} ${GRAY}[%s]${NC} ${RED}(%d Tage)${NC}" "$title" "$project" "$days")"
  done
fi

hdr "AUFGABEN NACH MODUS"
for m in "${mode_data[@]}"; do
  IFS='|' read -r mode mtotal mdone_tasks mnext msomeday <<< "$m"
  [[ $mtotal -eq 0 ]] && continue
  pct=0; [[ $mtotal -gt 0 ]] && pct=$((mdone_tasks*100/mtotal))
  label="${mode^}"; [[ $mode == "list" ]] && label="Liste"
  bp=$(bar $pct 22)
  printf "  %s\n" "$(printf "  ${BOLD}%-12s${NC} ${WHITE}%3d%%${NC} %s ${GRAY}(%d/%d)${NC}" "$label:" "$pct" "$bp" "$mdone_tasks" "$mtotal")"
  printf "  %s\n" "$(printf "    ${BLUE}Next:${NC} %-4d  ${GRAY}Someday:${NC} %-4d" "$mnext" "$msomeday")"
done

hdr "NÄCHSTE 30 TAGE – FÄLLIGKEITEN"
printf "  %s\n" "$(printf "  ${BOLD}%-12s %4s %s${NC}" "Datum" "Anz" "Aufgaben")"
hr2
today_ts=$(date +%s)
for dt in "${due_timeline[@]}"; do
  IFS='|' read -r due_date count tasks <<< "$dt"
  due_ts=$(date -d "$due_date" +%s 2>/dev/null || echo 0)
  day_name=$(date -d "$due_date" '+%a %d.%m.' 2>/dev/null || echo "$due_date")
  if [[ $due_ts -lt $today_ts ]]; then color=$RED; marker="⚠"
  elif [[ $due_ts -eq $today_ts ]]; then color=$YELLOW; marker="●"
  elif [[ $due_ts -lt $((today_ts + 259200)) ]]; then color=$YELLOW; marker="◐"
  else color=$BLUE; marker="○"; fi
  first_task=$(echo "$tasks" | cut -d'|' -f1 | sed 's/^ *//; s/ *$//')
  [[ ${#first_task} -gt 50 ]] && first_task="${first_task:0:47}..."
  printf "  %s\n" "$(printf "  ${color}%s %-12s${NC} ${WHITE}%3d${NC} ${GRAY}%s${NC}" "$marker" "$day_name" "$count" "$first_task")"
  if [[ "$tasks" == *"|"* ]]; then
    echo "$tasks" | cut -d'|' -f2- | tr '|' '\n' | while read -r t; do
      t=$(echo "$t" | sed 's/^ *//; s/ *$//')
      [[ ${#t} -gt 50 ]] && t="${t:0:47}..."
      printf "  %s\n" "$(printf "  ${GRAY}    └─ %s${NC}" "$t")"
    done
  fi
done
[[ ${#due_timeline[@]} -eq 0 ]] && printf "  ${GREEN}✓ Keine anstehenden Fälligkeiten${NC}\n"

hdr "VELOCITY – LETZTE 21 TAGE"
if [[ ${#vel_data[@]} -eq 0 ]]; then
  printf "  ${GRAY}Keine Velocity-Daten in den letzten 21 Tagen${NC}\n"
else
  for i in "${!vel_data[@]}"; do
    count=${vel_data[i]}; label=${vel_labels[i]}
    bar_len=$((count * 3)); [[ $bar_len -gt 40 ]] && bar_len=40
    bar_str=$(printf "${GREEN}%${bar_len}s${NC}" "" | sed 's/ /█/g')
    printf "  %s\n" "$(printf "  ${GRAY}%s${NC} ${WHITE}%2d${NC} %s" "$label" "$count" "$bar_str")"
  done
  total_week1=0; total_week2=0; total_week3=0
  for i in "${!vel_data[@]}"; do
    day_offset=$(( ${#vel_data[@]} - 1 - i ))
    if [[ $day_offset -lt 7 ]]; then total_week1=$((total_week1 + vel_data[i]))
    elif [[ $day_offset -lt 14 ]]; then total_week2=$((total_week2 + vel_data[i]))
    else total_week3=$((total_week3 + vel_data[i])); fi
  done
  printf "\n"
  printf "  %s\n" "$(printf "  ${BOLD}Wochen-Velocity:${NC}  ${CYAN}W1:${WHITE}%2d${NC}  ${CYAN}W2:${WHITE}%2d${NC}  ${CYAN}W3:${WHITE}%2d${NC}" "$total_week1" "$total_week2" "$total_week3")"
  trend="→"; [[ $total_week1 -gt $total_week2 ]] && trend="↑"; [[ $total_week1 -lt $total_week2 ]] && trend="↓"
  avg=$(echo "scale=1; ($total_week1+$total_week2+$total_week3)/21" | bc -l 2>/dev/null || echo "?")
  [[ "$avg" == .* ]] && avg="0$avg"
  printf "  %s\n" "$(printf "  ${BOLD}Trend:${NC} ${WHITE}%s${NC} (%s/Tag Ø)" "$trend" "$avg")"
fi

hdr "KALENDERANSICHT – NÄCHSTE 4 WOCHEN"
weeks=4
for w in $(seq 0 $((weeks-1))); do
  week_start=$(date -d "$(date +%F) +$((w*7)) days -$(($(date -d "$(date +%F) +$((w*7)) days" +%u)-1)) days" +%F)
  week_label=$(date -d "$week_start" '+%V. KW (%d.%m.)')
  printf "\n  ${BOLD}${CYAN}▶ %s${NC}\n" "$week_label"
  for d in $(seq 0 6); do
    day=$(date -d "$week_start +$d days" +%F)
    day_name=$(date -d "$day" '+%a %d.')
    day_ts=$(date -d "$day" +%s)
    tasks_today=""; count=0
    for dt in "${due_timeline[@]}"; do
      IFS='|' read -r due_date c t <<< "$dt"
      [[ "$due_date" == "$day" ]] && tasks_today="$t" && count=$c && break
    done
    if [[ $day_ts -lt $today_ts ]]; then color=$RED; marker="✗"
    elif [[ $day_ts -eq $today_ts ]]; then color=$YELLOW; marker="●"
    elif [[ $count -gt 0 ]]; then color=$BLUE; marker="◆"
    else color=$GRAY; marker=" "; fi
    if [[ $count -gt 0 ]]; then
      first=$(echo "$tasks_today" | cut -d'|' -f1 | sed 's/^ *//; s/ *$//')
      [[ ${#first} -gt 48 ]] && first="${first:0:45}..."
      printf "  %s\n" "$(printf "  ${color}%s${NC} ${BOLD}%s${NC} ${WHITE}(%d)${NC} ${GRAY}%s${NC}" "$marker" "$day_name" "$count" "$first")"
    else
      printf "  %s\n" "$(printf "  ${GRAY}%s %s${NC}" "$marker" "$day_name")"
    fi
  done
done

hdr "EMPFEHLUNGEN"
recs=()
[[ $inbox -gt 5 ]] && recs+=("${YELLOW}📥 Inbox verarbeiten ($inbox Items)${NC}")
[[ $overdue -gt 0 ]] && recs+=("${RED}⚠ $overdue überfällige Aufgaben dringend prüfen${NC}")
[[ $due_today -gt 0 ]] && recs+=("${YELLOW}⏰ $due_today Aufgaben sind heute fällig${NC}")
[[ $due_week -gt 10 ]] && recs+=("${BLUE}📅 Diese Woche $due_week fällige Aufgaben – planen${NC}")
[[ $someday -gt 20 ]] && recs+=("${GRAY}💭 $someday 'Irgendwann'-Aufgaben – aufräumen?${NC}")
[[ $active_projects -gt 5 ]] && recs+=("${MAGENTA}📦 $active_projects aktive Projekte – Fokus setzen${NC}")
[[ ${#vel_data[@]} -gt 0 && $total_week1 -eq 0 ]] && recs+=("${RED}📉 Keine Aufgaben diese Woche erledigt${NC}")
for p in "${projects[@]}"; do
  IFS='|' read -r id title due ptotal pdone <<< "$p"
  pct=0; [[ $ptotal -gt 0 ]] && pct=$((pdone*100/ptotal))
  [[ $ptotal -gt 3 && $pct -eq 0 ]] && recs+=("${RED}🛑 Projekt '$title': 0%% Fortschritt ($ptotal Tasks)${NC}") && break
done
if [[ ${#recs[@]} -eq 0 ]]; then
  printf "  ${GREEN}✓ Alles im grünen Bereich!${NC}\n"
else
  for r in "${recs[@]}"; do printf "  $r\n"; done
fi

hr
printf "${GRAY}Datenbank: %s${NC}\n" "$DB"
printf "${GRAY}Aktualisiert: %s${NC}\n" "$(date '+%H:%M:%S')"