#!/bin/bash

# Database Migration Script for Tilbudgivern
# Migrates database from s.alw.dk to local instance
# Author: GitHub Copilot
# Date: $(date)

set -e

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

# Configuration
REMOTE_HOST="s.alw.dk"
REMOTE_PORT="55555"
REMOTE_USER="alex"
REMOTE_DB_NAME="tilbudgivern"
REMOTE_DB_USER="tilbuduser"
REMOTE_DB_PASS="tilbudpass123"

LOCAL_DB_NAME="tilbudgivern"
LOCAL_DB_USER="tilbuduser"
LOCAL_DB_PASS="tilbudpass123"

BACKUP_DIR="/tmp/tilbudgivern_migration"
BACKUP_FILE="tilbudgivern_backup_$(date +%Y%m%d_%H%M%S).sql"

error() {
    echo -e "${RED}❌ ERROR: $1${NC}"
    exit 1
}

success() {
    echo -e "${GREEN}✅ $1${NC}"
}

warning() {
    echo -e "${YELLOW}⚠️  $1${NC}"
}

info() {
    echo -e "${BLUE}ℹ️  $1${NC}"
}

# Create backup directory
info "Step 1: Creating backup directory..."
mkdir -p "$BACKUP_DIR"
success "Backup directory created at $BACKUP_DIR"

# Test local database connection
info "Step 2: Testing local database connection..."
if mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" -e "USE $LOCAL_DB_NAME; SELECT 'Local DB OK' as status;" > /dev/null 2>&1; then
    success "Local database connection OK"
else
    error "Local database connection failed. Please check credentials."
fi

# Test SSH connection
info "Step 3: Testing SSH connection to remote server..."
if ssh -p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "echo 'SSH OK'" > /dev/null 2>&1; then
    success "SSH connection to remote server OK"
else
    error "SSH connection failed. Please check SSH credentials and connectivity."
fi

# Export database from remote server
info "Step 4: Exporting database from remote server..."
ssh -p "$REMOTE_PORT" "$REMOTE_USER@$REMOTE_HOST" "
    sudo mysqldump '$REMOTE_DB_NAME' \
    --single-transaction \
    --routines \
    --triggers \
    --hex-blob \
    --default-character-set=utf8mb4 \
    --add-drop-table \
    --complete-insert
" > "$BACKUP_DIR/$BACKUP_FILE" 2>/dev/null

if [ -f "$BACKUP_DIR/$BACKUP_FILE" ] && [ -s "$BACKUP_DIR/$BACKUP_FILE" ]; then
    BACKUP_SIZE=$(du -h "$BACKUP_DIR/$BACKUP_FILE" | cut -f1)
    success "Database exported successfully ($BACKUP_SIZE)"
else
    error "Database export failed or file is empty"
fi

# Show backup file info
info "Backup file details:"
echo "  Location: $BACKUP_DIR/$BACKUP_FILE"
echo "  Size: $(du -h "$BACKUP_DIR/$BACKUP_FILE" | cut -f1)"
echo "  Lines: $(wc -l < "$BACKUP_DIR/$BACKUP_FILE")"

# Ask for confirmation before importing
echo ""
warning "About to import database to local MySQL instance."
warning "This will REPLACE all existing data in the local '$LOCAL_DB_NAME' database!"
read -p "Are you sure you want to continue? (yes/no): " confirm

if [ "$confirm" != "yes" ]; then
    info "Migration cancelled by user"
    exit 0
fi

# Create local backup first
info "Step 5: Creating local database backup before migration..."
LOCAL_BACKUP_FILE="local_backup_before_migration_$(date +%Y%m%d_%H%M%S).sql"
mysqldump -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" "$LOCAL_DB_NAME" > "$BACKUP_DIR/$LOCAL_BACKUP_FILE" 2>/dev/null || warning "Local backup failed (database might be empty)"

# Import database to local server
info "Step 6: Importing database to local server..."
mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" "$LOCAL_DB_NAME" < "$BACKUP_DIR/$BACKUP_FILE"

if [ $? -eq 0 ]; then
    success "Database imported successfully!"
else
    error "Database import failed"
fi

# Verify import
info "Step 7: Verifying database import..."
TABLE_COUNT=$(mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" -e "USE $LOCAL_DB_NAME; SHOW TABLES;" 2>/dev/null | wc -l)
if [ "$TABLE_COUNT" -gt 1 ]; then
    success "Database verification OK - Found $(($TABLE_COUNT - 1)) tables"
    
    # Show some basic stats
    echo ""
    info "Database import summary:"
    mysql -u "$LOCAL_DB_USER" -p"$LOCAL_DB_PASS" -e "
        USE $LOCAL_DB_NAME;
        SELECT 'customer_projects' as table_name, COUNT(*) as record_count FROM customer_projects
        UNION ALL
        SELECT 'project_quotes', COUNT(*) FROM project_quotes
        UNION ALL  
        SELECT 'project_materials', COUNT(*) FROM project_materials
        UNION ALL
        SELECT 'bygma_prisbog', COUNT(*) FROM bygma_prisbog;
    " 2>/dev/null || info "Some tables might not exist yet"
else
    error "Database verification failed - No tables found"
fi

# Clean up (optional)
read -p "Do you want to keep the backup files? (yes/no): " keep_backup
if [ "$keep_backup" != "yes" ]; then
    rm -f "$BACKUP_DIR/$BACKUP_FILE"
    rm -f "$BACKUP_DIR/$LOCAL_BACKUP_FILE" 2>/dev/null
    info "Backup files cleaned up"
else
    info "Backup files kept in: $BACKUP_DIR"
fi

echo ""
success "Database migration completed successfully!"
info "Local database '$LOCAL_DB_NAME' now contains data from remote server"