#!/bin/bash
# Database Analysis Script
# Analyzes all databases, their tables, sizes, and identifies unused tables

echo "=========================================="
echo "DATABASE ANALYSIS REPORT"
echo "Generated: $(date)"
echo "=========================================="
echo ""

# Database credentials (adjust as needed)
DB_USER="admin"
DB_PASS="3mdbpB2k%*@!Dcfd"

# Get list of databases (excluding system databases)
DATABASES=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" 2>/dev/null | grep -v -E "^(Database|information_schema|performance_schema|mysql|sys)$")

echo "=== DATABASE SUMMARY ==="
echo ""
mysql -u "$DB_USER" -p"$DB_PASS" -e "
SELECT 
    table_schema AS 'Database',
    COUNT(*) AS 'Tables',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'Data (MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'Index (MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
" 2>/dev/null

echo ""
echo "=== POTENTIALLY UNUSED DATABASES ==="
echo ""
for db in $(echo "$DATABASES" | grep -E "old|backup|temp|test"); do
    echo "Database: $db"
    table_count=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$db';" 2>/dev/null | tail -1)
    size=$(mysql -u "$DB_USER" -p"$DB_PASS" -e "
    SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
    FROM information_schema.tables
    WHERE table_schema = '$db';
    " 2>/dev/null | tail -1)
    echo "  Tables: $table_count"
    echo "  Size: ${size}MB"
    echo ""
done

echo ""
echo "=== TABLES WITH BACKUP/OLD NAMING PATTERNS ==="
echo ""
for db in $DATABASES; do
    backup_tables=$(mysql -u "$DB_USER" -p"$DB_PASS" "$db" -e "
    SELECT TABLE_NAME, 
           ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size (MB)',
           TABLE_ROWS
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = '$db'
    AND (
        TABLE_NAME LIKE '%backup%' 
        OR TABLE_NAME LIKE '%old%' 
        OR TABLE_NAME LIKE '%temp%'
        OR TABLE_NAME LIKE '%_bak'
        OR TABLE_NAME LIKE '%_old'
        OR TABLE_NAME LIKE '%_tmp'
    )
    ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
    " 2>/dev/null)
    
    if [ ! -z "$backup_tables" ] && [ $(echo "$backup_tables" | wc -l) -gt 1 ]; then
        echo "Database: $db"
        echo "$backup_tables" | tail -n +2
        echo ""
    fi
done

echo ""
echo "=== LARGEST TABLES (Top 20) ==="
echo ""
mysql -u "$DB_USER" -p"$DB_PASS" -e "
SELECT 
    CONCAT(table_schema, '.', table_name) AS 'Table',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
    table_rows AS 'Rows'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;
" 2>/dev/null

echo ""
echo "=== TABLES WITH ZERO ROWS ==="
echo ""
for db in $DATABASES; do
    empty_tables=$(mysql -u "$DB_USER" -p"$DB_PASS" "$db" -e "
    SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = '$db'
    AND TABLE_ROWS = 0
    AND TABLE_TYPE = 'BASE TABLE';
    " 2>/dev/null | tail -n +2)
    
    if [ ! -z "$empty_tables" ]; then
        echo "Database: $db"
        echo "$empty_tables" | sed 's/^/  /'
        echo ""
    fi
done

echo ""
echo "=========================================="
echo "Analysis Complete"
echo "=========================================="