#!/bin/bash

# ==============================================================================
# Script Name:  systron-db-backup.sh
# Description:  Automated Nightly Backup & Offsite Sync with Slack/Discord Alerts
# Target:       Systron Cloud SSD VPS (Ubuntu, Debian, RHEL Flavors)
# ==============================================================================

# --- CONFIGURATION SETTINGS ---
DB_NAME="wp_portal"                 # Database name from your secure setup
BACKUP_DIR="/var/backups/mysql"     # Local directory to store temporary files
RETENTION_DAYS=7                     # Number of days to keep backups locally
DATE=$(date +"%Y-%m-%d_%H%M%S")     # Timestamp format
BACKUP_NAME="${DB_NAME}_backup_${DATE}.sql.gz"
SERVER_NAME=$(hostname)             # Dynamically pulls your server name

# --- OFFSITE RETENTION SETTINGS ---
OFFSITE_METHOD="sftp"               # Options: "sftp" or "s3"
SFTP_USER="backup_user"
SFTP_HOST="offsite-backup-server.com"
SFTP_PORT="22"
SFTP_REMOTE_DIR="/remote/backups/systron/"
S3_BUCKET="s3://your-secure-backup-bucket/database/"

# --- ALERT NOTIFICATION SETTINGS ---
# Set to "true" for the channel(s) you wish to activate
ALERT_DISCORD=true
ALERT_SLACK=false
ALERT_EMAIL=false

# Channel Webhook URL Endpoints / Destination Addresses
DISCORD_WEBHOOK="https://discord.com"
SLACK_WEBHOOK="https://slack.com"
NOTIFICATION_EMAIL="admin@yourportal.com"
# ------------------------------------------------------------------------------

# Ensure running as root or with elevated permissions
if [ "$EUID" -ne 0 ]; then
  echo "❌ Error: Please execute this backup utility using root or sudo privileges."
  exit 1
fi

# Ensure local backup directory exists
mkdir -p "$BACKUP_DIR"
chmod 700 "$BACKUP_DIR"

# ------------------------------------------------------------------------------
# 🔔 ALERTS / NOTIFICATION ENGINE
# ------------------------------------------------------------------------------
send_notification() {
    local STATUS=$1     # Parameter 1: "SUCCESS" or "FAILURE"
    local MESSAGE=$2    # Parameter 2: Custom message string
    local COLOR=32768   # Green for success (Discord decimal format)
    
    if [ "$STATUS" = "FAILURE" ]; then
        COLOR=16711680  # Red for failure
    fi

    # 1. Discord Notification Handler
    if [ "$ALERT_DISCORD" = true ]; then
        curl -H "Content-Type: application/json" -X POST -d '{
            "embeds": [{
                "title": "'"${STATUS}: Backup Engine Alert"'",
                "description": "'"${MESSAGE}"'",
                "color": '"${COLOR}"',
                "fields": [
                    {"name": "Host Server", "value": "'"${SERVER_NAME}"'", "inline": true},
                    {"name": "Target DB", "value": "'"${DB_NAME}"'", "inline": true}
                ],
                "footer": {"text": "Systron VPS Automation Pipeline Engine"}
            }]
        }' "$DISCORD_WEBHOOK" > /dev/null 2>&1
    fi

    # 2. Slack Notification Handler
    if [ "$ALERT_SLACK" = true ]; then
        local SLACK_COLOR="good"
        [ "$STATUS" = "FAILURE" ] && SLACK_COLOR="danger"
        
        curl -X POST --data-urlencode "payload={\"attachments\": [{\"fallback\": \"${STATUS}: ${MESSAGE}\", \"color\": \"${SLACK_COLOR}\", \"title\": \"${STATUS}: Backup Notification\", \"text\": \"${MESSAGE}\n*Server:* ${SERVER_NAME}\n*Database:* ${DB_NAME}\"}]}" "$SLACK_WEBHOOK" > /dev/null 2>&1
    fi

    # 3. Email Notification Handler
    if [ "$ALERT_EMAIL" = true ]; then
        echo -e "Subject: [${STATUS}] Database Backup Status - ${SERVER_NAME}\n\nHi Team,\n\nThe backup operation reported a status of: ${STATUS}.\n\nDetails:\n${MESSAGE}\n\n---\nSystron VPS System Automated Notification Engine" | sendmail "$NOTIFICATION_EMAIL"
    fi
}

# ------------------------------------------------------------------------------
# MAIN EXECUTION ROUTINE
# ------------------------------------------------------------------------------
echo "⏳ Starting database backup sequence for [${DB_NAME}] at $(date)"

# --- Step 1: Dump and Compress the 1GB+ Database ---
mysqldump --single-transaction --quick --routines --triggers "${DB_NAME}" | gzip -9 > "${BACKUP_DIR}/${BACKUP_NAME}"

if [ $? -eq 0 ]; then
    echo "✅ Step 1/3: Local database dump and compression complete. (${BACKUP_NAME})"
else
    ERROR_MSG="CRITICAL ERROR: Database dump routine failed on local disk generation step."
    echo "❌ ${ERROR_MSG}"
    send_notification "FAILURE" "${ERROR_MSG}"
    exit 1
fi

# --- Step 2: Push to Secure Offsite Storage Location ---
echo "🛫 Step 2/3: Dispatching compressed structural asset offsite via ${OFFSITE_METHOD^^}..."

if [ "$OFFSITE_METHOD" = "sftp" ]; then
    scp -P "${SFTP_PORT}" "${BACKUP_DIR}/${BACKUP_NAME}" "${SFTP_USER}@${SFTP_HOST}:${SFTP_REMOTE_DIR}"
    OFFSITE_STATUS=$?
elif [ "$OFFSITE_METHOD" = "s3" ]; then
    aws s3 cp "${BACKUP_DIR}/${BACKUP_NAME}" "${S3_BUCKET}${BACKUP_NAME}"
    OFFSITE_STATUS=$?
else
    OFFSITE_STATUS=9
fi

if [ $OFFSITE_STATUS -eq 0 ]; then
    echo "✅ Step 2/3: Offsite structural redundancy achieved successfully."
else
    ERROR_MSG="CRITICAL ERROR: Offsite file transmission failed via tracking status profile: ${OFFSITE_STATUS}."
    echo "❌ ${ERROR_MSG}"
    send_notification "FAILURE" "${ERROR_MSG}"
    exit 1
fi

# --- Step 3: Local Disk Housekeeping (Self-Cleaning Loop) ---
echo "🧹 Step 3/3: Running maintenance loops on local storage buffers..."
find "$BACKUP_DIR" -type f -name "${DB_NAME}_backup_*.sql.gz" -mtime +"${RETENTION_DAYS}" -exec rm {} \;

# If execution successfully navigates to this point without an exit code trigger, issue success ping
SUCCESS_MSG="Nightly backup successfully compiled, compressed, and transferred to offsite storage vaults safely. Compressed filename: ${BACKUP_NAME}"
echo "🏁 ${SUCCESS_MSG}"
send_notification "SUCCESS" "${SUCCESS_MSG}"