#!/bin/bash
set -euo pipefail

# --- Config ---
MYSQLDUMP="/usr/bin/mysqldump"
DB="skyflyco_Lessons"
BACKUP_DIR="/home/skyflyco/public_html/Lesson/backups"
LOGFILE="/home/skyflyco/public_html/Lesson/logs/backup_log.txt"

# --- Lock to prevent overlapping runs ---
LOCKFILE="/tmp/mysql_backup.lock"
exec 9>"$LOCKFILE" || exit 1
flock -n 9 || exit 0

mkdir -p "$BACKUP_DIR"
mkdir -p "$(dirname "$LOGFILE")"

# --- Backup filename ---
FILENAME="backup_$(date +'%Y-%m-%d_%H-%M-%S').sql"
FULLPATH="$BACKUP_DIR/$FILENAME"

# --- Start logging ---
echo "----------------------------------------" >> "$LOGFILE"
echo "$(date '+%Y-%m-%d %H:%M:%S') - Starting backup" >> "$LOGFILE"

# --- Run mysqldump excluding certain tables ---
$MYSQLDUMP $DB \
  --ignore-table=$DB.table_row_snapshots_ \
  --ignore-table=$DB.table_row_snapshots__ \
  --ignore-table=$DB.table_row_snapshots \
  --ignore-table=$DB.audit_queue \
  --ignore-table=$DB.delete_log \
  --ignore-table=$DB.lessonss_can_delete \
  --ignore-table=$DB.lessonreports_can_delete \
  --ignore-table=$DB.table_row_counts \
  --ignore-table=$DB.notifications \
  | gzip > "$FULLPATH" 2>> "$LOGFILE"

# --- Check result ---
if [ $? -eq 0 ]; then
    echo "Backup OK: $FULLPATH" >> "$LOGFILE"
else
    echo "ERROR: Backup failed!" >> "$LOGFILE"
fi

# --- Delete backups older than 90 days ---
find "$BACKUP_DIR" -name "*.sql" -mtime +90 -delete