#!/bin/bash

# ============================================================================
# Maternal Records Migration Script
# ============================================================================
# This script safely migrates data from the old maternal_records table
# to the new maternal_health_records + maternal_test_results system
# ============================================================================

echo "╔═══════════════════════════════════════════════════════════════╗"
echo "║     MATERNAL RECORDS MIGRATION TO NEW SYSTEM                  ║"
echo "╚═══════════════════════════════════════════════════════════════╝"
echo ""

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

# Function to print colored messages
print_success() {
    echo -e "${GREEN}✓ $1${NC}"
}

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

print_error() {
    echo -e "${RED}✗ $1${NC}"
}

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

# Check if we're in the right directory
if [ ! -f "artisan" ]; then
    print_error "Error: artisan file not found. Please run this script from your Laravel project root."
    exit 1
fi

print_info "Step 1: Checking database connection..."
php artisan db:show 2>&1 | head -n 5
if [ $? -ne 0 ]; then
    print_error "Database connection failed! Please check your .env configuration."
    exit 1
fi
print_success "Database connection OK"
echo ""

print_info "Step 2: Checking if old maternal_records table exists..."
RECORD_COUNT=$(php artisan tinker --execute="echo DB::table('maternal_records')->count();" 2>/dev/null || echo "0")
if [ "$RECORD_COUNT" == "0" ] || [ -z "$RECORD_COUNT" ]; then
    print_warning "No records found in maternal_records table (or table doesn't exist)"
    echo "   This might mean:"
    echo "   - The table is already empty"
    echo "   - Data was already migrated"
    echo "   - Table doesn't exist"
    echo ""
    read -p "Continue anyway? (y/n) " -n 1 -r
    echo ""
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        echo "Migration cancelled."
        exit 0
    fi
else
    print_success "Found $RECORD_COUNT records to migrate"
fi
echo ""

print_info "Step 3: Checking if new tables exist..."
NEW_TABLES_EXIST=1
php artisan tinker --execute="DB::table('maternal_health_records')->count();" > /dev/null 2>&1 || NEW_TABLES_EXIST=0
php artisan tinker --execute="DB::table('maternal_test_results')->count();" > /dev/null 2>&1 || NEW_TABLES_EXIST=0

if [ $NEW_TABLES_EXIST -eq 0 ]; then
    print_error "New tables (maternal_health_records or maternal_test_results) don't exist!"
    print_info "Running migrations to create new tables..."
    php artisan migrate
    if [ $? -ne 0 ]; then
        print_error "Migration failed! Please check the error above."
        exit 1
    fi
fi
print_success "New tables exist"
echo ""

print_info "Step 4: Creating database backup..."
BACKUP_FILE="backup_before_migration_$(date +%Y%m%d_%H%M%S).sql"
print_warning "Important: Creating backup at storage/backups/$BACKUP_FILE"

mkdir -p storage/backups

# Get database credentials from .env
DB_DATABASE=$(grep DB_DATABASE .env | cut -d '=' -f2)
DB_USERNAME=$(grep DB_USERNAME .env | cut -d '=' -f2)
DB_PASSWORD=$(grep DB_PASSWORD .env | cut -d '=' -f2)
DB_HOST=$(grep DB_HOST .env | cut -d '=' -f2)

if command -v mysqldump &> /dev/null; then
    mysqldump -h "$DB_HOST" -u "$DB_USERNAME" -p"$DB_PASSWORD" "$DB_DATABASE" maternal_records > "storage/backups/$BACKUP_FILE" 2>/dev/null
    if [ $? -eq 0 ]; then
        print_success "Backup created: storage/backups/$BACKUP_FILE"
    else
        print_warning "Could not create MySQL backup (this is optional)"
    fi
else
    print_warning "mysqldump not found. Skipping backup (this is optional)"
fi
echo ""

print_warning "═══════════════════════════════════════════════════════════"
print_warning "IMPORTANT: This will migrate data from old to new system!"
print_warning "═══════════════════════════════════════════════════════════"
echo ""
echo "What will happen:"
echo "  1. Read all records from maternal_records table"
echo "  2. Create new records in maternal_health_records table"
echo "  3. Create test results in maternal_test_results table"
echo "  4. Keep original maternal_records table intact (no deletion)"
echo ""
read -p "Do you want to proceed? (y/n) " -n 1 -r
echo ""
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
    echo "Migration cancelled."
    exit 0
fi
echo ""

print_info "Step 5: Running migration..."
php artisan migrate --path=database/migrations/2026_01_05_000000_migrate_maternal_records_to_new_system.php

if [ $? -eq 0 ]; then
    print_success "Migration completed!"
    echo ""
    
    print_info "Step 6: Verifying migration..."
    echo ""
    
    # Check new records count
    NEW_COUNT=$(php artisan tinker --execute="echo DB::table('maternal_health_records')->where('general_notes', 'like', 'Migrated from old maternal_records table%')->count();" 2>/dev/null || echo "0")
    TEST_COUNT=$(php artisan tinker --execute="echo DB::table('maternal_test_results')->count();" 2>/dev/null || echo "0")
    
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo "📊 MIGRATION SUMMARY"
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo "Old records (maternal_records):          $RECORD_COUNT"
    echo "New records (maternal_health_records):   $NEW_COUNT"
    echo "Test results created:                    $TEST_COUNT"
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo ""
    
    if [ "$NEW_COUNT" -eq "$RECORD_COUNT" ]; then
        print_success "All records migrated successfully! ✓"
    else
        print_warning "Record count mismatch! Please check the logs."
        echo "   Check: storage/logs/laravel.log for details"
    fi
    
    echo ""
    print_info "Next Steps:"
    echo "  1. ✓ Verify migrated data in your application"
    echo "  2. ✓ Test the new system thoroughly"
    echo "  3. ✓ Update code to use maternal_health_records"
    echo "  4. ✓ Once confirmed, you can drop old maternal_records table"
    echo ""
    print_success "Migration process completed!"
    
else
    print_error "Migration failed! Check the error above."
    print_info "Your database has been rolled back to the previous state."
    echo ""
    echo "Check the logs: storage/logs/laravel.log"
    exit 1
fi

echo ""
echo "╔═══════════════════════════════════════════════════════════════╗"
echo "║                    MIGRATION COMPLETE                         ║"
echo "╚═══════════════════════════════════════════════════════════════╝"