#!/bin/bash

#################################################################################
#                         Enjyn Skript Service                                  #
#                     MySQL Server Install Script                               #
#                     Compatible with Debian 8-12                               #
#                    Compatible with Ubuntu 20.04-24.04                         #
#################################################################################

# Color definitions
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
PURPLE='\033[0;35m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

# Variables
MYSQL_ROOT_PASS=""
MYSQL_VERSION=""
OS_VERSION=""
OS_NAME=""
LOG_FILE="/var/log/enjyn_mysql_install.log"
MYSQL_PACKAGE=""
MYSQL_SERVICE=""
REMOTE_ACCESS=false
CREATE_USER=false
NEW_USERNAME=""
NEW_USER_PASS=""
NEW_USER_HOST="%"
NEW_DATABASE=""
PORT=3306
BIND_ADDRESS="127.0.0.1"

# Functions
print_banner() {
    clear
    echo -e "${PURPLE}"
    echo "╔═══════════════════════════════════════════════════════════════════╗"
    echo "║                      Enjyn Skript Service                         ║"
    echo "║                  MySQL Server Installation                        ║"
    echo "║                      Version 1.0.0                                ║"
    echo "╚═══════════════════════════════════════════════════════════════════╝"
    echo -e "${NC}"
}

print_success() {
    echo -e "${GREEN}[✓] $1${NC}"
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] SUCCESS: $1" >> "$LOG_FILE"
}

print_error() {
    echo -e "${RED}[✗] $1${NC}"
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] ERROR: $1" >> "$LOG_FILE"
}

print_warning() {
    echo -e "${YELLOW}[!] $1${NC}"
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] WARNING: $1" >> "$LOG_FILE"
}

print_info() {
    echo -e "${CYAN}[i] $1${NC}"
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] INFO: $1" >> "$LOG_FILE"
}

check_root() {
    if [[ $EUID -ne 0 ]]; then
        print_error "This script must be run as root!"
        exit 1
    fi
}

detect_os() {
    if [[ -f /etc/os-release ]]; then
        . /etc/os-release
        OS_NAME=$ID
        OS_VERSION=$VERSION_ID
    else
        print_error "Cannot detect OS version!"
        exit 1
    fi
    
    case $OS_NAME in
        debian)
            case $OS_VERSION in
                8|9|10|11|12)
                    print_success "Detected: Debian $OS_VERSION"
                    ;;
                *)
                    print_error "Unsupported Debian version: $OS_VERSION"
                    exit 1
                    ;;
            esac
            ;;
        ubuntu)
            case $OS_VERSION in
                20.04|22.04|24.04)
                    print_success "Detected: Ubuntu $OS_VERSION"
                    ;;
                *)
                    print_error "Unsupported Ubuntu version: $OS_VERSION"
                    exit 1
                    ;;
            esac
            ;;
        *)
            print_error "Unsupported OS: $OS_NAME"
            exit 1
            ;;
    esac
}

determine_mysql_version() {
    # Determine which MySQL/MariaDB version to install
    if [[ "$OS_NAME" == "debian" ]]; then
        case $OS_VERSION in
            8|9) 
                MYSQL_PACKAGE="mysql-server mysql-client"
                MYSQL_SERVICE="mysql"
                MYSQL_VERSION="5.5"
                ;;
            10)
                MYSQL_PACKAGE="mariadb-server mariadb-client"
                MYSQL_SERVICE="mariadb"
                MYSQL_VERSION="10.3"
                ;;
            11)
                MYSQL_PACKAGE="mariadb-server mariadb-client"
                MYSQL_SERVICE="mariadb"
                MYSQL_VERSION="10.5"
                ;;
            12)
                MYSQL_PACKAGE="mariadb-server mariadb-client"
                MYSQL_SERVICE="mariadb"
                MYSQL_VERSION="10.11"
                ;;
        esac
    elif [[ "$OS_NAME" == "ubuntu" ]]; then
        case $OS_VERSION in
            20.04)
                MYSQL_PACKAGE="mysql-server mysql-client"
                MYSQL_SERVICE="mysql"
                MYSQL_VERSION="8.0"
                ;;
            22.04)
                MYSQL_PACKAGE="mysql-server mysql-client"
                MYSQL_SERVICE="mysql"
                MYSQL_VERSION="8.0"
                ;;
            24.04)
                MYSQL_PACKAGE="mysql-server mysql-client"
                MYSQL_SERVICE="mysql"
                MYSQL_VERSION="8.0"
                ;;
        esac
    fi
}

gather_info() {
    echo ""
    print_info "MySQL Configuration Options"
    echo ""
    
    # Root password
    while true; do
        echo -n "Enter MySQL root password (auto-generate if empty): "
        read -s -r MYSQL_ROOT_PASS
        echo ""
        
        if [[ -z "$MYSQL_ROOT_PASS" ]]; then
            MYSQL_ROOT_PASS=$(openssl rand -base64 16)
            print_info "Generated password: $MYSQL_ROOT_PASS"
            break
        elif [[ ${#MYSQL_ROOT_PASS} -ge 8 ]]; then
            break
        else
            print_error "Password must be at least 8 characters long"
        fi
    done
    
    # Remote access
    echo ""
    echo -n "Enable remote access? [y/N]: "
    read -r response
    
    if [[ "$response" =~ ^[Yy]$ ]]; then
        REMOTE_ACCESS=true
        BIND_ADDRESS="0.0.0.0"
        
        echo -n "Custom port (default 3306): "
        read -r input
        [[ -n "$input" ]] && PORT="$input"
    fi
    
    # Create additional user
    echo ""
    echo -n "Create additional MySQL user? [y/N]: "
    read -r response
    
    if [[ "$response" =~ ^[Yy]$ ]]; then
        CREATE_USER=true
        
        echo -n "Username: "
        read -r NEW_USERNAME
        
        while true; do
            echo -n "Password for $NEW_USERNAME: "
            read -s -r NEW_USER_PASS
            echo ""
            
            if [[ ${#NEW_USER_PASS} -ge 8 ]]; then
                break
            else
                print_error "Password must be at least 8 characters long"
            fi
        done
        
        echo -n "Host access (% for any host, localhost for local only) [%]: "
        read -r input
        [[ -n "$input" ]] && NEW_USER_HOST="$input"
        
        echo -n "Create database for this user? Database name (leave empty to skip): "
        read -r NEW_DATABASE
    fi
}

update_system() {
    print_info "Updating system packages..."
    
    export DEBIAN_FRONTEND=noninteractive
    
    if apt-get update -y >> "$LOG_FILE" 2>&1; then
        print_success "System updated successfully"
    else
        print_error "Failed to update system"
        exit 1
    fi
    
    # Install required tools
    apt-get install -y software-properties-common wget curl openssl >> "$LOG_FILE" 2>&1
}

install_mysql() {
    determine_mysql_version
    print_info "Installing MySQL/MariaDB $MYSQL_VERSION..."
    
    # Pre-configure to avoid prompts
    export DEBIAN_FRONTEND=noninteractive
    
    # Install MySQL/MariaDB
    if apt-get install -y $MYSQL_PACKAGE >> "$LOG_FILE" 2>&1; then
        systemctl enable $MYSQL_SERVICE >> "$LOG_FILE" 2>&1
        systemctl start $MYSQL_SERVICE >> "$LOG_FILE" 2>&1
        
        if systemctl is-active --quiet $MYSQL_SERVICE; then
            print_success "MySQL/MariaDB installed and started successfully"
        else
            print_error "MySQL/MariaDB installed but failed to start"
            exit 1
        fi
    else
        print_error "Failed to install MySQL/MariaDB"
        exit 1
    fi
}

secure_mysql() {
    print_info "Securing MySQL installation..."
    
    # Different approach for MySQL 5.7+ and MariaDB
    if [[ "$MYSQL_SERVICE" == "mysql" ]] && [[ "$OS_NAME" == "ubuntu" ]]; then
        # MySQL 5.7+ on Ubuntu
        mysql --user=root <<EOF >> "$LOG_FILE" 2>&1
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '$MYSQL_ROOT_PASS';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
EOF
    else
        # MariaDB and older MySQL
        mysql --user=root <<EOF >> "$LOG_FILE" 2>&1
UPDATE mysql.user SET Password=PASSWORD('$MYSQL_ROOT_PASS') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
EOF
    fi
    
    if [[ $? -eq 0 ]]; then
        print_success "MySQL secured successfully"
    else
        print_error "Failed to secure MySQL"
        exit 1
    fi
}

configure_mysql() {
    print_info "Configuring MySQL..."
    
    # Find MySQL config file
    if [[ -f /etc/mysql/mysql.conf.d/mysqld.cnf ]]; then
        MYSQL_CONFIG="/etc/mysql/mysql.conf.d/mysqld.cnf"
    elif [[ -f /etc/mysql/mariadb.conf.d/50-server.cnf ]]; then
        MYSQL_CONFIG="/etc/mysql/mariadb.conf.d/50-server.cnf"
    elif [[ -f /etc/mysql/my.cnf ]]; then
        MYSQL_CONFIG="/etc/mysql/my.cnf"
    else
        print_warning "Could not find MySQL config file"
        return
    fi
    
    # Backup original config
    cp $MYSQL_CONFIG $MYSQL_CONFIG.bak
    
    # Update bind-address if remote access enabled
    if [[ "$REMOTE_ACCESS" == true ]]; then
        sed -i "s/^bind-address.*/bind-address = $BIND_ADDRESS/" $MYSQL_CONFIG
        sed -i "s/^#bind-address.*/bind-address = $BIND_ADDRESS/" $MYSQL_CONFIG
        
        # Update port if changed
        if [[ "$PORT" != "3306" ]]; then
            sed -i "s/^port.*/port = $PORT/" $MYSQL_CONFIG
            sed -i "s/^#port.*/port = $PORT/" $MYSQL_CONFIG
        fi
    fi
    
    # Add performance tuning
    if ! grep -q "# Enjyn Performance Tuning" $MYSQL_CONFIG; then
        cat >> $MYSQL_CONFIG <<EOF

# Enjyn Performance Tuning
max_connections = 200
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 1M
tmp_table_size = 32M
max_heap_table_size = 32M
EOF
    fi
    
    # Restart MySQL
    systemctl restart $MYSQL_SERVICE >> "$LOG_FILE" 2>&1
    print_success "MySQL configured successfully"
}

create_user_and_database() {
    if [[ "$CREATE_USER" == false ]]; then
        return
    fi
    
    print_info "Creating MySQL user and database..."
    
    # Create user
    mysql -u root -p"$MYSQL_ROOT_PASS" <<EOF >> "$LOG_FILE" 2>&1
CREATE USER '$NEW_USERNAME'@'$NEW_USER_HOST' IDENTIFIED BY '$NEW_USER_PASS';
EOF
    
    if [[ $? -eq 0 ]]; then
        print_success "User $NEW_USERNAME created successfully"
    else
        print_error "Failed to create user"
        return
    fi
    
    # Create database if requested
    if [[ -n "$NEW_DATABASE" ]]; then
        mysql -u root -p"$MYSQL_ROOT_PASS" <<EOF >> "$LOG_FILE" 2>&1
CREATE DATABASE IF NOT EXISTS $NEW_DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON $NEW_DATABASE.* TO '$NEW_USERNAME'@'$NEW_USER_HOST';
FLUSH PRIVILEGES;
EOF
        
        if [[ $? -eq 0 ]]; then
            print_success "Database $NEW_DATABASE created and granted to $NEW_USERNAME"
        else
            print_error "Failed to create database"
        fi
    else
        # Grant some basic privileges
        mysql -u root -p"$MYSQL_ROOT_PASS" <<EOF >> "$LOG_FILE" 2>&1
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON *.* TO '$NEW_USERNAME'@'$NEW_USER_HOST';
FLUSH PRIVILEGES;
EOF
    fi
}

configure_firewall() {
    print_info "Configuring firewall..."
    
    if command -v ufw >/dev/null 2>&1; then
        if [[ "$REMOTE_ACCESS" == true ]]; then
            ufw allow $PORT/tcp >> "$LOG_FILE" 2>&1
            print_success "Firewall configured for port $PORT"
        else
            print_info "Remote access disabled, firewall not modified"
        fi
    else
        print_warning "UFW not installed, please manually configure firewall"
    fi
}

create_backup_script() {
    print_info "Creating backup script..."
    
    mkdir -p /opt/enjyn/mysql-backup
    
    cat > /opt/enjyn/mysql-backup/backup.sh <<'EOF'
#!/bin/bash
# Enjyn MySQL Backup Script

BACKUP_DIR="/opt/enjyn/mysql-backup/backups"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# Get MySQL credentials
MYSQL_USER="root"
MYSQL_PASS="$1"

if [ -z "$MYSQL_PASS" ]; then
    echo "Usage: $0 <mysql_root_password>"
    exit 1
fi

# Backup all databases
echo "Starting MySQL backup..."
mysqldump -u $MYSQL_USER -p$MYSQL_PASS --all-databases --single-transaction --quick --lock-tables=false > $BACKUP_DIR/all_databases_$DATE.sql

if [ $? -eq 0 ]; then
    echo "Backup completed: $BACKUP_DIR/all_databases_$DATE.sql"
    
    # Compress backup
    gzip $BACKUP_DIR/all_databases_$DATE.sql
    echo "Backup compressed: $BACKUP_DIR/all_databases_$DATE.sql.gz"
    
    # Remove old backups
    find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "Old backups removed (older than $RETENTION_DAYS days)"
else
    echo "Backup failed!"
    exit 1
fi
EOF
    
    chmod +x /opt/enjyn/mysql-backup/backup.sh
    print_success "Backup script created at /opt/enjyn/mysql-backup/backup.sh"
}

show_summary() {
    SERVER_IP=$(hostname -I | awk '{print $1}')
    
    echo ""
    echo -e "${PURPLE}════════════════════════════════════════════════════════════════════${NC}"
    echo -e "${GREEN}         MySQL Server Installation Completed!${NC}"
    echo -e "${PURPLE}════════════════════════════════════════════════════════════════════${NC}"
    echo ""
    echo -e "${CYAN}Server Information:${NC}"
    echo -e "  • MySQL Version: $MYSQL_VERSION"
    echo -e "  • Service Name: $MYSQL_SERVICE"
    echo -e "  • Port: $PORT"
    echo -e "  • Bind Address: $BIND_ADDRESS"
    echo ""
    echo -e "${YELLOW}Credentials:${NC}"
    echo -e "  • Root User: root"
    echo -e "  • Root Password: ${RED}$MYSQL_ROOT_PASS${NC}"
    
    if [[ "$CREATE_USER" == true ]]; then
        echo -e "  • Additional User: $NEW_USERNAME"
        echo -e "  • User Password: ${RED}$NEW_USER_PASS${NC}"
        echo -e "  • User Host: $NEW_USER_HOST"
        [[ -n "$NEW_DATABASE" ]] && echo -e "  • Database: $NEW_DATABASE"
    fi
    
    echo ""
    echo -e "${GREEN}Connection Examples:${NC}"
    echo -e "  • Local: ${CYAN}mysql -u root -p'$MYSQL_ROOT_PASS'${NC}"
    
    if [[ "$REMOTE_ACCESS" == true ]]; then
        echo -e "  • Remote: ${CYAN}mysql -h $SERVER_IP -P $PORT -u root -p'$MYSQL_ROOT_PASS'${NC}"
    fi
    
    echo ""
    echo -e "${GREEN}Service Commands:${NC}"
    echo -e "  • Start: ${CYAN}systemctl start $MYSQL_SERVICE${NC}"
    echo -e "  • Stop: ${CYAN}systemctl stop $MYSQL_SERVICE${NC}"
    echo -e "  • Restart: ${CYAN}systemctl restart $MYSQL_SERVICE${NC}"
    echo -e "  • Status: ${CYAN}systemctl status $MYSQL_SERVICE${NC}"
    echo ""
    echo -e "${GREEN}Backup:${NC}"
    echo -e "  • Manual backup: ${CYAN}/opt/enjyn/mysql-backup/backup.sh '$MYSQL_ROOT_PASS'${NC}"
    echo -e "  • Add to crontab for daily backups:"
    echo -e "    ${CYAN}0 2 * * * /opt/enjyn/mysql-backup/backup.sh '$MYSQL_ROOT_PASS'${NC}"
    echo ""
    
    # Save credentials
    cat > /root/mysql_credentials.txt <<EOF
Enjyn Skript Service - MySQL Installation
=========================================
Date: $(date)
MySQL Version: $MYSQL_VERSION
Server IP: $SERVER_IP
Port: $PORT
Root Password: $MYSQL_ROOT_PASS
EOF

    if [[ "$CREATE_USER" == true ]]; then
        cat >> /root/mysql_credentials.txt <<EOF
Additional User: $NEW_USERNAME
User Password: $NEW_USER_PASS
User Host: $NEW_USER_HOST
Database: $NEW_DATABASE
EOF
    fi

    cat >> /root/mysql_credentials.txt <<EOF
=========================================
EOF
    
    chmod 600 /root/mysql_credentials.txt
    
    echo -e "${RED}Security Notes:${NC}"
    echo -e "  • Credentials saved to: /root/mysql_credentials.txt"
    echo -e "  • Installation log: $LOG_FILE"
    echo -e "  • Remember to backup your databases regularly"
    echo -e "  • Configure SSL for remote connections"
    echo ""
    echo -e "${PURPLE}════════════════════════════════════════════════════════════════════${NC}"
    echo -e "${GREEN}        Thank you for using Enjyn Skript Service!${NC}"
    echo -e "${PURPLE}════════════════════════════════════════════════════════════════════${NC}"
}

# Main execution
main() {
    print_banner
    
    # Initialize log file
    echo "=== Enjyn MySQL Installation Started at $(date) ===" > "$LOG_FILE"
    
    check_root
    detect_os
    
    echo ""
    print_warning "This script will install MySQL/MariaDB Server"
    echo -n "Do you want to continue? [y/N]: "
    read -r response
    
    if [[ ! "$response" =~ ^[Yy]$ ]]; then
        print_info "Installation cancelled"
        exit 0
    fi
    
    gather_info
    
    echo ""
    print_info "Starting installation..."
    echo ""
    
    update_system
    install_mysql
    secure_mysql
    configure_mysql
    create_user_and_database
    configure_firewall
    create_backup_script
    show_summary
    
    echo "=== Installation Completed at $(date) ===" >> "$LOG_FILE"
}

# Error handling
set -e
trap 'print_error "An error occurred during installation. Check $LOG_FILE for details."' ERR

# Run main function
main