#!/bin/bash

# Usage: curl https://tools-scr.apps360.id/backup-db/all-mysql-db.sh | bash -s -- <DB_USER> <DB_PASS> <BUCKET_NAME> <CURRENT_INSTANCE_NAME> <BACKUP_DIR> <RETENTION>
# Usually Args Value:
# -------------------
# DB_USER: backup
# BUCKET_NAME: dev-app-archives|dev-databases|prod-app-archives|prod-databases
# CURRENT_INSTANCE_NAME: <CURRENT_INSTANCE_NAME>
# BACKUP_DIR: /home/bigio/backup/tmp/mysql
# RETENTION: 2|7

# Prerequisites:
# apt-get install s3cmd
# apt-get install zip

# s3cmd --configure

# run using crontab
# 0 0 * * * /path/to/your/backup_script.sh

# MySQL Database Credentials
DB_USER="$1"
DB_PASS="$2"

# Vultr Object Storage Configuration
BUCKET_NAME="$3"
CURRENT_INSTANCE_NAME="$4"

# Directory to store backups temporarily
BACKUP_DIR="$5"

# Extension
EXTENSION="mysql"

# Retention period in seconds (1 week)
RETENTION_PERIOD=$6

# Remove backup directory if exists
rm -rf "$BACKUP_DIR"

# Create backup directory if not exists
mkdir -p "$BACKUP_DIR"

# Get current time in seconds since epoch
current_time=$(date +%s)

# Get current date and time
current_datetime=$(date +"%Y-%m-%d_%H-%M-%S")

# Get list of all databases
databases=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")

# Loop through each database and backup
for db in $databases; do
    echo "Backing up database: $db"
    mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction --databases "$db" > "$BACKUP_DIR/${db}_${current_datetime}.${EXTENSION}"

    # Make a backup file zip
    echo "Zipping: ${db}_${current_datetime}.${EXTENSION}"
    zip "$BACKUP_DIR/${db}_${current_datetime}.${EXTENSION}.zip" -j "$BACKUP_DIR/${db}_${current_datetime}.${EXTENSION}"

    # Remove zipped
    echo "Removing ${db}_${current_datetime}.${EXTENSION}"
    rm "$BACKUP_DIR/${db}_${current_datetime}.${EXTENSION}"

    # Uploading to s3
    file="$BACKUP_DIR/${db}_${current_datetime}.${EXTENSION}.zip"
    echo "Uploading $(basename "$file")"
    s3cmd put "$file" "s3://$BUCKET_NAME/${db}/$CURRENT_INSTANCE_NAME/$(basename "$file")"

    # Remove uploaded zip file
    echo "Removing $file"
    rm "$file"
done


for db in $databases; do

    s3cmd ls s3://$BUCKET_NAME/$db/$CURRENT_INSTANCE_NAME/ | grep " DIR " -v | while read -r line;
      do
        createDate=`echo $line|awk {'print $1" "$2'}`

        createDate=$(date -d "$createDate" +%s)  # GNU date format
        olderThan=$(date -d "$RETENTION_PERIOD days ago" +%s)

        if [[ $createDate -lt $olderThan ]]
          then
            fileName=`echo $line|awk {'print $4'}`
            if [[ $fileName != "" ]]
              then
                printf 'Deleting "%s"\n' $fileName
                s3cmd del "$fileName"
            fi
        fi
      done;

done

echo "Finished!"