#!/bin/bash
#
# SxSQLyze (StackX SQL Analyzer)
# Version : 1.2
# Author  : Christophe Casalegno / Brain 0verride
#
# License : GPL-3.0 or later
#
# Copyright (c) 2025 Christophe Casalegno
#
# This program is free software: you can redistribute it and/or modify
#
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program.  If not, see <https://www.gnu.org/licenses/>
#
# The license is available on this server here:
# https://www.christophe-casalegno.com/licences/gpl-3.0.txt
#
# ---------------------------------------------------------------------------
#  Changelog 1.2
#   - each "case" now separated in functions
#   - echo replaced by print & cat << EOF
#   - posix fallback for possible usage outside Linux
# ---------------------------------------------------------------------------

set -euo pipefail
IFS=$'\n\t'

unit="gb"       # (MB|GB)
cmd=""          
arg=""          


# General functions

function print_usage()
{
  cat <<EOF
Usage: $0 [options]
Long options:
  --databases                     Displays the size of all databases
  --tables <db>                   Displays the size of each table in a database
  --top <N>                       Shows the top N largest tables (across all databases)
  --growth-hint <db>              Estimates growth rate (MB/day)
  --engine-stats <db>             Storage engine distribution
  --table-fragmentation <db>      Reclaimable space (data_free)
  --charset-collation <db>        Character sets / collations
  --index-usage <db>              Index size per table
  --analyze-all                   Runs all analyses on all databases
  --unit mb|gb                    Output unit (default: gb)
EOF
  exit 1
}

function convert_unit()
{
  local mb="$1"
  if [[ ${unit} == "mb" ]]; then
    printf "%.2f" "${mb}"
  else
    awk -v v="${mb}" 'BEGIN{printf "%.2f", v/1024}'
  fi
}

function label_unit()
{
  [[ ${unit} == "mb" ]] && printf "MB" || printf "GB"
}

function mysql_ping()
{
  mysql --batch --skip-column-names -e "SELECT 1" >/dev/null 2>&1 || {
    printf "✖️  Impossible de se connecter à MySQL (identifiants ?)\n" >&2
    exit 2
  }
}

function validate_identifier()
{
  [[ $1 =~ ^[A-Za-z0-9_]+$ ]] || {
    printf "Identifiant SQL invalide : %s\n" "$1" >&2
    exit 3
  }
}

# Analyser functions

function databases()
{
  mysql --batch --skip-column-names -e "\
    SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,2) AS mb\
    FROM information_schema.tables\
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')\
    GROUP BY table_schema;" |
  awk -F"\t" -v u="$(label_unit)" '{conv=$2;if(u=="GB") conv=sprintf("%.2f",$2/1024);printf "%s\t%s\n",$1,conv}' |
  awk -F"\t" -v u="$(label_unit)" 'BEGIN{printf "\n%-30s %10s\n","Database","Size ("u")";print "----------------------------------------------"}{total+=$2;printf "%-30s %10s %s\n",$1,$2,u}END{print "----------------------------------------------";printf "%-30s %10.2f %s\n","TOTAL",total,u}'
}

function tables()
{
  validate_identifier "$1"
  mysql --batch --skip-column-names -e "\
    SELECT table_name, ROUND((data_length+index_length)/1024/1024,2) AS mb\
    FROM information_schema.tables WHERE table_schema='$1' ORDER BY mb DESC;" |
  awk -F"\t" -v db="$1" -v u="$(label_unit)" '{conv=$2;if(u=="GB") conv=sprintf("%.2f",$2/1024);printf "%s\t%s\n",$1,conv}' |
  awk -F"\t" -v db="$1" -v u="$(label_unit)" 'BEGIN{printf "\n%-40s %10s\n","Table (in "db")","Size ("u")";print "--------------------------------------------------------------"}{total+=$2;printf "%-40s %10s %s\n",$1,$2,u}END{print "--------------------------------------------------------------";printf "%-40s %10.2f %s\n","TOTAL",total,u}'
}

function top_tables()
{
  local n="$1"
  [[ ${n} =~ ^[0-9]+$ ]] || { printf "--top attend un entier\n" >&2; exit 3; }
  mysql --batch --skip-column-names -e "\
    SELECT CONCAT(table_schema,'.',table_name), ROUND((data_length+index_length)/1024/1024,2) AS mb\
    FROM information_schema.tables\
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')\
    ORDER BY mb DESC LIMIT ${n};" |
  awk -F"\t" -v u="$(label_unit)" '{conv=$2;if(u=="GB") conv=sprintf("%.2f",$2/1024);printf "%s\t%s\n",$1,conv}' |
  awk -F"\t" -v u="$(label_unit)" 'BEGIN{printf "\n%-50s %10s\n","Table","Size ("u")";print "---------------------------------------------------------------------"}{printf "%-50s %10s %s\n",$1,$2,u}'
}

function growth_hint()
{
  validate_identifier "$1"
  mysql --batch --raw --skip-column-names -e "\
    SELECT table_name,\
           DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s'),\
           IFNULL(DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s'),'N/A'),\
           ROUND((data_length+index_length)/1024/1024,2) AS mb,\
           ROUND(((data_length+index_length)/1024/1024)/GREATEST(TIMESTAMPDIFF(MINUTE,create_time,NOW())/1440,1),4) AS mb_per_day\
    FROM information_schema.tables WHERE table_schema='$1' AND create_time IS NOT NULL\
    ORDER BY mb_per_day DESC;" |
  awk -F"\t" -v u="$(label_unit)" '{size=$4;growth=$5;if(u=="GB"){size=sprintf("%.2f",$4/1024);growth=sprintf("%.4f",$5/1024)};printf "%s\t%s\t%s\t%s\t%s\n",$1,$2,$3,size,growth}' |
  awk -F"\t" -v db="$1" -v u="$(label_unit)" 'BEGIN{printf "\n%-40s %-19s %-19s %12s %15s\n","Table (in "db")","Created","Last Updated","Size ("u")","Est. "u"/day";print "---------------------------------------------------------------------------------------------------------------"}{printf "%-40s %-19s %-19s %12s %15s\n",$1,$2,$3,$4,$5}'
}

function engine_stats()
{
  validate_identifier "$1"
  mysql --batch --skip-column-names -e "\
    SELECT engine, COUNT(*) FROM information_schema.tables\
    WHERE table_schema='$1' GROUP BY engine;"
}

function table_fragmentation()
{
  validate_identifier "$1"
  mysql --batch --skip-column-names -e "\
    SELECT table_name, ROUND(data_free/1024/1024,2) AS mb_free\
    FROM information_schema.tables WHERE table_schema='$1' AND data_free>0\
    ORDER BY mb_free DESC;"
}

function charset_collation()
{
  validate_identifier "$1"
  mysql --batch --skip-column-names -e "\
    SELECT table_name, table_collation FROM information_schema.tables\
    WHERE table_schema='$1';"
}

function index_usage()
{
  validate_identifier "$1"
  mysql --batch --skip-column-names -e "\
    SELECT table_name, ROUND(index_length/1024/1024,2) AS mb_idx\
    FROM information_schema.tables WHERE table_schema='$1' ORDER BY mb_idx DESC;"
}

function analyze_all()
{
  databases
  local db
  for db in $(mysql --batch --skip-column-names -e "SHOW DATABASES" | grep -Ev '^(mysql|information_schema|performance_schema|sys)$'); do
    tables "${db}"
    engine_stats "${db}"
    table_fragmentation "${db}"
    charset_collation "${db}"
    index_usage "${db}"
  done
}

# Options parsing

mysql_ping

if getopt --test >/dev/null 2>&1; then
  PARSED=$(getopt -o '' \
    --long databases,tables:,top:,growth-hint:,engine-stats:,table-fragmentation:,charset-collation:,index-usage:,analyze-all,unit: -- "$@") || print_usage
  eval set -- "${PARSED}"
  while true; do
    case "$1" in
      --databases)               cmd="databases"; shift ;;
      --tables)                  cmd="tables";              arg="$2"; shift 2 ;;
      --top)                     cmd="top_tables";          arg="$2"; shift 2 ;;
      --growth-hint)             cmd="growth_hint";         arg="$2"; shift 2 ;;
      --engine-stats)            cmd="engine_stats";        arg="$2"; shift 2 ;;
      --table-fragmentation)     cmd="table_fragmentation"; arg="$2"; shift 2 ;;
      --charset-collation)       cmd="charset_collation";   arg="$2"; shift 2 ;;
      --index-usage)             cmd="index_usage";         arg="$2"; shift 2 ;;
      --analyze-all)             cmd="analyze_all"; shift ;;
      --unit)                    unit="$2"; shift 2 ;;
      --) shift; break ;;
      *) print_usage ;;
    esac
  done
else

  # Fallback POSIX simple if one day... 
  while [[ $# -gt 0 ]]; do
    case "$1" in
      --databases)               cmd="databases"; shift ;;
      --tables)                  cmd="tables";              arg="$2"; shift 2 ;;
      --top)                     cmd="top_tables";          arg="$2"; shift 2 ;;
      --growth-hint)             cmd="growth_hint";         arg="$2"; shift 2 ;;
      --engine-stats)            cmd="engine_stats";        arg="$2"; shift 2 ;;
      --table-fragmentation)     cmd="table_fragmentation"; arg="$2"; shift 2 ;;
      --charset-collation)       cmd="charset_collation";   arg="$2"; shift 2 ;;
      --index-usage)             cmd="index_usage";         arg="$2"; shift 2 ;;
      --analyze-all)             cmd="analyze_all"; shift ;;
      --unit)                    unit="$2"; shift 2 ;;
      *) print_usage ;;
    esac
  done
fi

# Dispatch commands

if [[ -z ${cmd} ]]; then
  print_usage
fi

if [[ -n ${arg} ]]; then
  "${cmd}" "${arg}"
else
  "${cmd}"
fi