#!/usr/bin/bash

# MySQL Database Setup Script for VegeRevenge Game
# This script creates the database, user, and tables required for the project

echo "Starting MySQL database setup for VegeRevenge..."

# Database configuration
DB_NAME="vegerevenge"
DB_USER="vegerevenge_user"
DB_PASS="vegerevenge_password"

# Check if MySQL is running
if ! systemctl is-active --quiet mysql; then
    echo "Error: MySQL service is not running. Please start MySQL first."
    exit 1
fi

# Try connecting to MySQL with different methods
echo "Trying to connect to MySQL..."

# First try without password (auth_socket)
if mysql -u root -e "SELECT 1;" &>/dev/null; then
    echo "Connected using auth_socket authentication"
    MYSQL_CMD="mysql -u root"
else
    # If that fails, try with password
    read -s -p "Enter MySQL root password: " MYSQL_ROOT_PASS
    echo
    if mysql -u root -p"$MYSQL_ROOT_PASS" -e "SELECT 1;" &>/dev/null; then
        echo "Connected using password authentication"
        MYSQL_CMD="mysql -u root -p$MYSQL_ROOT_PASS"
    else
        echo "Error: Could not connect to MySQL. Please check your credentials or try running with sudo."
        echo "Alternative: Run 'sudo mysql -u root' to connect manually"
        exit 1
    fi
fi

# Create database and user
$MYSQL_CMD << EOF
-- Create database
CREATE DATABASE IF NOT EXISTS $DB_NAME;

-- Create user and grant privileges
CREATE USER IF NOT EXISTS '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASS';
GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'localhost';
FLUSH PRIVILEGES;

-- Use the database
USE $DB_NAME;

-- Create leaderboard table
CREATE TABLE IF NOT EXISTS leaderboard(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    score INT NOT NULL,
    name VARCHAR(25) NOT NULL
);

-- Add indexes for better performance
CREATE INDEX idx_leaderboard_score ON leaderboard(score);
CREATE INDEX idx_leaderboard_name ON leaderboard(name);

EOF

if [ $? -eq 0 ]; then
    echo "Database setup completed successfully!"
    echo
    echo "Database Details:"
    echo "  Database Name: $DB_NAME"
    echo "  Username: $DB_USER"
    echo "  Password: $DB_PASS"
    echo "  Host: localhost"
    echo
    echo "IMPORTANT: Update your database connection file with these credentials:"
    echo "  File: mysqlconnection.php"
    echo "  Replace the connection parameters with:"
    echo "    Server: localhost"
    echo "    User: $DB_USER"
    echo "    Password: $DB_PASS"
    echo "    Database: $DB_NAME"
else
    echo "Error: Database setup failed. Please check your MySQL root password and try again."
    exit 1
fi