#!/bin/bash

# MySQL Database Backup Script for Showprima
# Creates timestamped backups with metadata and verification

set -e  # Exit on error

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"
BACKUP_DIR="$PROJECT_ROOT/database/backups/mysql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_NAME="backup_${TIMESTAMP}.sql"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_NAME"
METADATA_PATH="$BACKUP_DIR/$BACKUP_NAME.meta.json"

# Load database credentials from .env
if [ -f "$PROJECT_ROOT/.env" ]; then
    DB_HOST=$(grep "^DB_HOST=" "$PROJECT_ROOT/.env" | cut -d '=' -f2 | tr -d '\r\n' | xargs)
    DB_DATABASE=$(grep "^DB_DATABASE=" "$PROJECT_ROOT/.env" | cut -d '=' -f2 | tr -d '\r\n' | xargs)
    DB_USERNAME=$(grep "^DB_USERNAME=" "$PROJECT_ROOT/.env" | cut -d '=' -f2 | tr -d '\r\n' | xargs)
    DB_PASSWORD=$(grep "^DB_PASSWORD=" "$PROJECT_ROOT/.env" | cut -d '=' -f2 | tr -d '\r\n' | xargs)
else
    echo "Error: .env file not found"
    exit 1
fi

# Create temporary MySQL config file for safe password handling
MYSQL_CONFIG=$(mktemp)
trap "rm -f $MYSQL_CONFIG" EXIT
cat > "$MYSQL_CONFIG" << EOF
[client]
host=$DB_HOST
user=$DB_USERNAME
password=$DB_PASSWORD

[mysql]
database=$DB_DATABASE
EOF
chmod 600 "$MYSQL_CONFIG"

# Colors for output
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
RED='\033[0;31m'
NC='\033[0m' # No Color

echo -e "${YELLOW}========================================${NC}"
echo -e "${YELLOW}Showprima MySQL Database Backup${NC}"
echo -e "${YELLOW}========================================${NC}"
echo ""

# Verify MySQL connection
if ! mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT 1;" > /dev/null 2>&1; then
    echo -e "${RED}Error: Cannot connect to MySQL database${NC}"
    echo "Database: $DB_DATABASE"
    echo "Host: $DB_HOST"
    echo "Username: $DB_USERNAME"
    exit 1
fi

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Get database size
DB_SIZE=$(mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size_mb FROM information_schema.tables WHERE table_schema = '$DB_DATABASE';" -sN 2>/dev/null)

# Get row counts for key tables
echo -e "${YELLOW}Collecting database metadata...${NC}"
EVENTS_COUNT=$(mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT COUNT(*) FROM events;" -sN 2>/dev/null)
ORDERS_COUNT=$(mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT COUNT(*) FROM orders;" -sN 2>/dev/null)
RESERVATIONS_COUNT=$(mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT COUNT(*) FROM seat_reservations;" -sN 2>/dev/null)
USERS_COUNT=$(mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT COUNT(*) FROM users;" -sN 2>/dev/null)

# Get last migration
LAST_MIGRATION=$(mysql --defaults-extra-file="$MYSQL_CONFIG" -e "SELECT migration FROM migrations ORDER BY id DESC LIMIT 1;" -sN 2>/dev/null || echo "unknown")

# Create MySQL dump
echo -e "${YELLOW}Creating backup...${NC}"
DUMP_ERROR=$(mktemp)
if ! mysqldump --defaults-extra-file="$MYSQL_CONFIG" \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --add-drop-table \
    --hex-blob \
    --no-tablespaces \
    "$DB_DATABASE" > "$BACKUP_PATH" 2>"$DUMP_ERROR"; then
    echo -e "${RED}✗ Backup failed${NC}"
    cat "$DUMP_ERROR"
    rm -f "$DUMP_ERROR"
    exit 1
fi
rm -f "$DUMP_ERROR"

# Compress backup
echo -e "${YELLOW}Compressing backup...${NC}"
gzip "$BACKUP_PATH"
BACKUP_PATH="${BACKUP_PATH}.gz"
BACKUP_NAME="${BACKUP_NAME}.gz"

# Get compressed size
BACKUP_SIZE=$(du -h "$BACKUP_PATH" | cut -f1)

# Verify backup can be decompressed
echo -e "${YELLOW}Verifying backup integrity...${NC}"
if gunzip -t "$BACKUP_PATH" 2>/dev/null; then
    echo -e "${GREEN}✓ Backup integrity verified${NC}"
else
    echo -e "${RED}✗ Backup integrity check failed${NC}"
    rm "$BACKUP_PATH"
    exit 1
fi

# Create metadata file
cat > "$METADATA_PATH" << EOF
{
  "backup_timestamp": "$(date -u +"%Y-%m-%dT%H:%M:%SZ")",
  "database_type": "mysql",
  "database_name": "$DB_DATABASE",
  "database_host": "$DB_HOST",
  "backup_path": "$BACKUP_PATH",
  "database_size_mb": "$DB_SIZE",
  "backup_size": "$BACKUP_SIZE",
  "compressed": true,
  "git_commit": "$(git rev-parse HEAD 2>/dev/null || echo 'not-a-git-repo')",
  "git_branch": "$(git branch --show-current 2>/dev/null || echo 'unknown')",
  "last_migration": "$LAST_MIGRATION",
  "table_counts": {
    "events": $EVENTS_COUNT,
    "orders": $ORDERS_COUNT,
    "seat_reservations": $RESERVATIONS_COUNT,
    "users": $USERS_COUNT
  },
  "php_version": "$(php -v | head -n 1)",
  "laravel_version": "$(cd "$PROJECT_ROOT" && php artisan --version 2>/dev/null || echo 'unknown')"
}
EOF

# Clean up old backups (keep last 10)
echo -e "${YELLOW}Cleaning up old backups (keeping last 10)...${NC}"
cd "$BACKUP_DIR"
ls -t backup_*.sql.gz 2>/dev/null | tail -n +11 | while read file; do
    echo "  Removing old backup: $file"
    rm -f "$file"
    rm -f "${file}.meta.json"
done

# Print summary
echo ""
echo -e "${GREEN}========================================${NC}"
echo -e "${GREEN}Backup Created Successfully!${NC}"
echo -e "${GREEN}========================================${NC}"
echo ""
echo "Backup file: $BACKUP_NAME"
echo "Location: $BACKUP_DIR"
echo "Database size: ${DB_SIZE}MB"
echo "Compressed size: $BACKUP_SIZE"
echo ""
echo "Table counts:"
echo "  - Events: $EVENTS_COUNT"
echo "  - Orders: $ORDERS_COUNT"
echo "  - Seat Reservations: $RESERVATIONS_COUNT"
echo "  - Users: $USERS_COUNT"
echo ""
echo "To restore this backup, run:"
echo "  ./scripts/restore-db-mysql.sh $BACKUP_NAME"
echo ""