#!/bin/bash
#
# composite_live MySQL backup script
# - Daily backup, retains last 7 daily backups (No email sent daily)
# - Monthly backup on the 1st of every month, retains last 3 monthly backups
# - Emails HTML Download Link ONLY on the 1st of every month
# - .sql dump zipped with password protection
#
# Setup:
#   chmod 700 composite_daily_backup2.sh      # contains credentials, restrict access
#   crontab -e
#   0 2 * * * /web/html/bots/composite_daily_backup2.sh >> /var/log/db_backup.log 2>&1

set -uo pipefail

# ---------------- Config ----------------
DB_NAME="composite_live"
DB_USER="root"
DB_PASS='Outrigth@321#'

# Web Directory & Domain Configuration
DOMAIN="suitecrm7.compositeautomation.com"
BACKUP_ROOT="/web/html/composite_live/backups"
DAILY_DIR="$BACKUP_ROOT/daily"
MONTHLY_DIR="$BACKUP_ROOT/monthly"

ZIP_PASS='outr-compo'

DAILY_RETENTION=7     # Keep last 7 daily backups
MONTHLY_RETENTION=3   # Keep last 3 monthly backups

# Email & Chat Config
RECIPIENT_EMAIL="john@compositeautomation.com"
GCHAT_URL="https://chat.googleapis.com/v1/spaces/AAAABni8qm0/messages?key=AIzaSyDdI0hCZtE6vySjMm-WEfRq3CPzqKqqsHI&token=mGqTbW9-AUl7g6O5DfKof0Pu6XEjHqya0uDpOevM_yE"

# ---------------- SMTP Config ----------------
SMTP_HOST="smtp.gmail.com"
SMTP_PORT="587"                     # 587 (STARTTLS)
SMTP_USER="notification.outright@gmail.com"
SMTP_APP_PASS="bgbucbidtodufemg"
SMTP_FROM_NAME="Backup Notifier"

# ---------------- Helper Functions ----------------
send_html_email() {
    local to="$1"
    local subject="$2"
    local body="$3"

    if [ -z "$to" ] || [ -z "$subject" ]; then
        echo "send_html_email: 'to' and 'subject' are required" >&2
        return 1
    fi

    local msg_file
    msg_file="$(mktemp)"

    {
        echo "From: ${SMTP_FROM_NAME} <${SMTP_USER}>"
        echo "To: ${to}"
        echo "Subject: ${subject}"
        echo "MIME-Version: 1.0"
        echo "Content-Type: text/html; charset=UTF-8"
        echo
        echo "$body"
    } > "$msg_file"

    curl -s --ssl-reqd \
        --url "smtp://${SMTP_HOST}:${SMTP_PORT}" \
        --mail-from "$SMTP_USER" \
        --mail-rcpt "$to" \
        --user "${SMTP_USER}:${SMTP_APP_PASS}" \
        --upload-file "$msg_file"
    local status=$?

    rm -f "$msg_file"

    if [ $status -ne 0 ]; then
        echo "send_html_email: failed to send to ${to} (curl exit ${status})" >&2
        return 1
    fi
    return 0
}

notify() {
    local text="$1"
    curl -s -X POST -H "Content-Type: application/json" \
        -d "{\"text\": \"${text}\"}" \
        "$GCHAT_URL" > /dev/null 2>&1
}

cleanup() {
    rm -f "$TMP_SQL" "$TMP_ZIP" "$BACKUP_ROOT/mysqldump_err.log" "$BACKUP_ROOT/zip_err.log"
}
trap cleanup EXIT

# ---------------- Setup ----------------
mkdir -p "$DAILY_DIR" "$MONTHLY_DIR"

DATE="$(date +%Y-%m-%d)"
DAY_OF_MONTH="$(date +%d)"
TIMESTAMP="$(date +%Y%m%d_%H%M%S)"

SQL_FILE="${DB_NAME}_${TIMESTAMP}.sql"
ZIP_FILE="${DB_NAME}_${TIMESTAMP}.zip"
TMP_SQL="$BACKUP_ROOT/$SQL_FILE"
TMP_ZIP="$BACKUP_ROOT/$ZIP_FILE"

# ---------------- Dump ----------------
export MYSQL_PWD="$DB_PASS"
mysqldump --single-transaction --quick -u"$DB_USER" "$DB_NAME" > "$TMP_SQL" 2>"$BACKUP_ROOT/mysqldump_err.log"
DUMP_STATUS=$?
unset MYSQL_PWD

if [ $DUMP_STATUS -ne 0 ]; then
    ERR="$(cat "$BACKUP_ROOT/mysqldump_err.log")"
    notify "❌ *${DB_NAME}* backup FAILED on ${DATE} — mysqldump error: ${ERR}"
    send_html_email "$RECIPIENT_EMAIL" "❌ Database Backup FAILED: ${DB_NAME}" "<p>The database backup failed on ${DATE}.</p><p><b>Error details:</b><br>${ERR}</p>"
    exit 1
fi

# ---------------- Zip (password protected) ----------------
zip -P "$ZIP_PASS" -j "$TMP_ZIP" "$TMP_SQL" > "$BACKUP_ROOT/zip_err.log" 2>&1
ZIP_STATUS=$?

if [ $ZIP_STATUS -ne 0 ]; then
    ERR="$(cat "$BACKUP_ROOT/zip_err.log")"
    notify "❌ *${DB_NAME}* backup FAILED on ${DATE} — zip error: ${ERR}"
    send_html_email "$RECIPIENT_EMAIL" "❌ Database Backup FAILED: ${DB_NAME}" "<p>The backup failed during zip compression on ${DATE}.</p><p><b>Error details:</b><br>${ERR}</p>"
    exit 1
fi

# ---------------- Daily backup ----------------
mv "$TMP_ZIP" "$DAILY_DIR/$ZIP_FILE"

# Keep only last 7 daily backups (delete older than 7)
cd "$DAILY_DIR" || exit 1
ls -1t ${DB_NAME}_*.zip 2>/dev/null | tail -n +$((DAILY_RETENTION + 1)) | xargs -r rm -f

# ---------------- Monthly backup (1st of month) ----------------
if [ "$DAY_OF_MONTH" == "01" ]; then
    # Copy file to monthly folder
    cp "$DAILY_DIR/$ZIP_FILE" "$MONTHLY_DIR/$ZIP_FILE"

    # Keep only last 3 monthly backups (delete older than 3)
    cd "$MONTHLY_DIR" || exit 1
    ls -1t ${DB_NAME}_*.zip 2>/dev/null | tail -n +$((MONTHLY_RETENTION + 1)) | xargs -r rm -f

    # Build Monthly Download Link & Send Email
    DOWNLOAD_URL="https://${DOMAIN}/backups/monthly/${ZIP_FILE}"
    FILE_SIZE=$(du -h "$MONTHLY_DIR/$ZIP_FILE" | cut -f1)

    HTML_BODY="<!DOCTYPE html>
<html>
<body style=\"font-family: Arial, sans-serif; color: #333;\">
    <p>Hello,</p>
    <p>The <b>Monthly Database Backup</b> for <b>${DB_NAME}</b> has been generated successfully.</p>

    <p><b>Backup Details:</b><br>
    ------------------------------------------<br>
    <b>Database Name:</b> ${DB_NAME}<br>
    <b>Date:</b> ${DATE}<br>
    <b>File Size:</b> ${FILE_SIZE}</p>

    <p style=\"margin-top: 20px; margin-bottom: 20px;\">
        <a href=\"${DOWNLOAD_URL}\" style=\"background-color: #007bff; color: white; padding: 10px 18px; text-decoration: none; border-radius: 4px; font-weight: bold; display: inline-block;\">Download Monthly Backup</a>
    </p>

    <p style=\"color: #666; font-size: 13px;\"><i>Note: This monthly download link will remain active for 3 months.</i></p>
</body>
</html>"

    # Send Monthly Email
    send_html_email "$RECIPIENT_EMAIL" "✅ Monthly Database Backup Link: ${DB_NAME} (${DATE})" "$HTML_BODY"
    notify "✅ *${DB_NAME}* MONTHLY backup created and emailed successfully on ${DATE} — ${ZIP_FILE}"
else
    # Send daily notification only to Google Chat
    notify "✅ *${DB_NAME}* daily backup successful on ${DATE} — ${ZIP_FILE}"
fi

exit 0