#!/bin/bash

# MySQL Database Restore Script for Showprima
# Restores from mysqldump backups with verification and safety checks

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"

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

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

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

# Check if backup file is provided
if [ -z "$1" ]; then
    echo -e "${RED}Error: No backup file specified${NC}"
    echo ""
    echo "Usage: $0 <backup_filename>"
    echo ""
    echo -e "${BLUE}Available backups:${NC}"
    ls -lh "$BACKUP_DIR"/backup_*.sql.gz 2>/dev/null | awk '{print "  " $9 " (" $5 ", " $6 " " $7 ")"}' || echo "  No backups found"
    echo ""
    exit 1
fi

BACKUP_NAME="$1"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_NAME"
METADATA_PATH="$BACKUP_DIR/${BACKUP_NAME}.meta.json"

# Check if backup exists
if [ ! -f "$BACKUP_PATH" ]; then
    echo -e "${RED}Error: Backup file not found: $BACKUP_PATH${NC}"
    echo ""
    echo -e "${BLUE}Available backups:${NC}"
    ls -lh "$BACKUP_DIR"/backup_*.sql.gz 2>/dev/null | awk '{print "  " $9 " (" $5 ", " $6 " " $7 ")"}' || echo "  No backups found"
    echo ""
    exit 1
fi

# Display backup metadata if available
if [ -f "$METADATA_PATH" ]; then
    echo -e "${BLUE}Backup Metadata:${NC}"
    echo "----------------------------------------"
    cat "$METADATA_PATH" | jq -r '
        "Created: \(.backup_timestamp)",
        "Database: \(.database_name)",
        "Database Size: \(.database_size_mb)MB",
        "Backup Size: \(.backup_size)",
        "Git Commit: \(.git_commit[0:8])",
        "Git Branch: \(.git_branch)",
        "Last Migration: \(.last_migration)",
        "",
        "Table Counts:",
        "  Events: \(.table_counts.events)",
        "  Orders: \(.table_counts.orders)",
        "  Reservations: \(.table_counts.seat_reservations)",
        "  Users: \(.table_counts.users)"
    ' 2>/dev/null || echo "Metadata file exists but could not be parsed"
    echo "----------------------------------------"
    echo ""
fi

# Verify backup integrity
echo -e "${YELLOW}Verifying backup integrity...${NC}"
set +e  # Temporarily disable exit on error
INTEGRITY_RESULT=$(gunzip -t "$BACKUP_PATH" 2>&1)
INTEGRITY_EXIT=$?
set -e  # Re-enable exit on error

if [ $INTEGRITY_EXIT -ne 0 ]; then
    echo -e "${RED}✗ Backup integrity check failed - file may be corrupted${NC}"
    echo -e "${RED}Error: $INTEGRITY_RESULT${NC}"
    exit 1
fi
echo -e "${GREEN}✓ Backup integrity verified${NC}"
echo ""

# Create a safety backup of current database before restoring
SAFETY_TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
SAFETY_BACKUP="$BACKUP_DIR/pre_restore_safety_${SAFETY_TIMESTAMP}.sql.gz"

echo -e "${YELLOW}Creating safety backup of current database...${NC}"

mysqldump -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --add-drop-table \
    "$DB_DATABASE" 2>/dev/null | gzip > "$SAFETY_BACKUP"

# Create metadata for safety backup
cat > "${SAFETY_BACKUP}.meta.json" << EOF
{
  "backup_timestamp": "$(date -u +"%Y-%m-%dT%H:%M:%SZ")",
  "backup_type": "pre_restore_safety",
  "database_type": "mysql",
  "database_name": "$DB_DATABASE",
  "restoring_from": "$BACKUP_NAME",
  "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')"
}
EOF

echo -e "${GREEN}✓ Safety backup created: pre_restore_safety_${SAFETY_TIMESTAMP}.sql.gz${NC}"
echo ""

# Confirmation prompt
echo -e "${RED}WARNING: This will replace your current database!${NC}"
echo ""
echo "Current database: $DB_DATABASE @ $DB_HOST"
echo "Restore from: $BACKUP_NAME"
echo "Safety backup: pre_restore_safety_${SAFETY_TIMESTAMP}.sql.gz"
echo ""
read -p "Are you sure you want to proceed? (type 'yes' to continue): " CONFIRM

if [ "$CONFIRM" != "yes" ]; then
    echo -e "${YELLOW}Restore cancelled${NC}"
    exit 0
fi

echo ""
echo -e "${YELLOW}Restoring database...${NC}"

# Decompress and restore
gunzip -c "$BACKUP_PATH" | mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" 2>/dev/null

if [ $? -ne 0 ]; then
    echo -e "${RED}✗ Restore failed${NC}"
    echo -e "${YELLOW}Rolling back to safety backup...${NC}"
    gunzip -c "$SAFETY_BACKUP" | mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" 2>/dev/null
    exit 1
fi

echo -e "${GREEN}✓ Database restored successfully${NC}"

# Get restored database stats
EVENTS_COUNT=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM events;" -sN 2>/dev/null)
ORDERS_COUNT=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM orders;" -sN 2>/dev/null)
RESERVATIONS_COUNT=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM seat_reservations;" -sN 2>/dev/null)
USERS_COUNT=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM users;" -sN 2>/dev/null)

echo ""
echo -e "${GREEN}========================================${NC}"
echo -e "${GREEN}Restore Completed Successfully!${NC}"
echo -e "${GREEN}========================================${NC}"
echo ""
echo "Restored from: $BACKUP_NAME"
echo ""
echo "Current table counts:"
echo "  - Events: $EVENTS_COUNT"
echo "  - Orders: $ORDERS_COUNT"
echo "  - Seat Reservations: $RESERVATIONS_COUNT"
echo "  - Users: $USERS_COUNT"
echo ""
echo "Safety backup available at:"
echo "  $SAFETY_BACKUP"
echo ""
echo -e "${YELLOW}Important: Restart any running Laravel services to ensure they use the restored database.${NC}"
echo ""