#!/bin/bash

# MySQL Backup Verification Script for Showprima
# Verifies backup integrity with multiple verification levels
#
# Usage:
#   ./scripts/verify-mysql-backup.sh backup_20251105_120000.sql.gz
#   ./scripts/verify-mysql-backup.sh backup_20251105_120000.sql.gz --level=full
#   ./scripts/verify-mysql-backup.sh --latest
#   ./scripts/verify-mysql-backup.sh --all

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"

# Default verification level
VERIFICATION_LEVEL="basic"  # basic, sql, full

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

# Parse arguments
BACKUP_FILE=""
VERIFY_LATEST=false
VERIFY_ALL=false

while [[ $# -gt 0 ]]; do
    case $1 in
        --level=*)
            VERIFICATION_LEVEL="${1#*=}"
            shift
            ;;
        --latest)
            VERIFY_LATEST=true
            shift
            ;;
        --all)
            VERIFY_ALL=true
            shift
            ;;
        --help)
            echo "MySQL Backup Verification Script"
            echo ""
            echo "Usage:"
            echo "  $0 <backup_file> [options]"
            echo "  $0 --latest [options]"
            echo "  $0 --all [options]"
            echo ""
            echo "Options:"
            echo "  --level=<basic|sql|full>  Verification level (default: basic)"
            echo "  --latest                  Verify latest backup only"
            echo "  --all                     Verify all backups"
            echo "  --help                    Show this help message"
            echo ""
            echo "Verification Levels:"
            echo "  basic  - Compression integrity + file size check (~5 seconds)"
            echo "  sql    - Basic + SQL syntax validation (~30 seconds)"
            echo "  full   - SQL + test restore to temp database (~2-5 minutes)"
            exit 0
            ;;
        *)
            BACKUP_FILE="$1"
            shift
            ;;
    esac
done

# Function to print section headers
print_header() {
    echo ""
    echo -e "${BLUE}========================================${NC}"
    echo -e "${BLUE}$1${NC}"
    echo -e "${BLUE}========================================${NC}"
    echo ""
}

# Function to verify a single backup
verify_backup() {
    local backup_file="$1"
    local backup_path="$BACKUP_DIR/$backup_file"
    local metadata_path="$BACKUP_DIR/${backup_file%.gz}.meta.json"

    print_header "Verifying: $backup_file"
    echo "Verification Level: $VERIFICATION_LEVEL"
    echo ""

    # Check if backup file exists
    if [ ! -f "$backup_path" ]; then
        echo -e "${RED}✗ Backup file not found: $backup_path${NC}"
        return 1
    fi

    # Check if metadata file exists
    if [ ! -f "$metadata_path" ]; then
        echo -e "${YELLOW}⚠ Metadata file not found (non-critical)${NC}"
    fi

    # Level 1: Basic verification
    echo -e "${YELLOW}[Level 1] Basic Verification${NC}"

    # Check file size
    BACKUP_SIZE=$(stat -f%z "$backup_path" 2>/dev/null || stat -c%s "$backup_path" 2>/dev/null)
    BACKUP_SIZE_KB=$((BACKUP_SIZE / 1024))

    if [ "$BACKUP_SIZE" -lt 1024 ]; then
        echo -e "${RED}✗ Backup file too small (${BACKUP_SIZE_KB}KB) - likely corrupt${NC}"
        return 1
    fi
    echo -e "${GREEN}✓ File size check passed (${BACKUP_SIZE_KB}KB)${NC}"

    # Check compression integrity
    if gunzip -t "$backup_path" 2>/dev/null; then
        echo -e "${GREEN}✓ Compression integrity verified${NC}"
    else
        echo -e "${RED}✗ Compression integrity check failed${NC}"
        return 1
    fi

    # Check metadata consistency (if exists)
    if [ -f "$metadata_path" ]; then
        if command -v jq > /dev/null 2>&1; then
            BACKUP_TIMESTAMP=$(jq -r '.backup_timestamp' "$metadata_path" 2>/dev/null || echo "unknown")
            DB_SIZE_MB=$(jq -r '.database_size_mb' "$metadata_path" 2>/dev/null || echo "unknown")
            echo -e "${GREEN}✓ Metadata file valid${NC}"
            echo "  - Timestamp: $BACKUP_TIMESTAMP"
            echo "  - Database Size: ${DB_SIZE_MB}MB"
        else
            echo -e "${YELLOW}⚠ jq not installed, skipping metadata validation${NC}"
        fi
    fi

    if [ "$VERIFICATION_LEVEL" = "basic" ]; then
        echo ""
        echo -e "${GREEN}✅ Basic verification passed${NC}"
        return 0
    fi

    # Level 2: SQL Syntax Verification
    echo ""
    echo -e "${YELLOW}[Level 2] SQL Syntax Verification${NC}"

    # Decompress to temp file for analysis
    TEMP_SQL=$(mktemp)
    trap "rm -f $TEMP_SQL" EXIT

    gunzip -c "$backup_path" > "$TEMP_SQL"

    # Check for SQL dump header
    if head -n 20 "$TEMP_SQL" | grep -q "MySQL dump"; then
        echo -e "${GREEN}✓ Valid MySQL dump format${NC}"
    else
        echo -e "${RED}✗ Not a valid MySQL dump${NC}"
        return 1
    fi

    # Check for required tables
    REQUIRED_TABLES=("events" "orders" "seat_reservations" "users" "migrations")
    MISSING_TABLES=()

    for table in "${REQUIRED_TABLES[@]}"; do
        if grep -q "CREATE TABLE.*\`$table\`" "$TEMP_SQL"; then
            echo -e "${GREEN}✓ Table '$table' found${NC}"
        else
            echo -e "${RED}✗ Table '$table' missing${NC}"
            MISSING_TABLES+=("$table")
        fi
    done

    if [ ${#MISSING_TABLES[@]} -gt 0 ]; then
        echo -e "${RED}✗ Missing tables: ${MISSING_TABLES[*]}${NC}"
        return 1
    fi

    # Check for SQL errors or warnings
    if grep -i "error\|warning" "$TEMP_SQL" | head -5; then
        echo -e "${YELLOW}⚠ Found SQL errors/warnings in dump (check above)${NC}"
    fi

    if [ "$VERIFICATION_LEVEL" = "sql" ]; then
        echo ""
        echo -e "${GREEN}✅ SQL syntax verification passed${NC}"
        return 0
    fi

    # Level 3: Full verification (test restore)
    echo ""
    echo -e "${YELLOW}[Level 3] Test Restore Verification${NC}"
    echo -e "${YELLOW}⚠ This will create a temporary database and restore backup${NC}"

    # 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_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 -e "${RED}✗ .env file not found${NC}"
        return 1
    fi

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

    # Create temporary database
    TEMP_DB="showprima_backup_verify_$(date +%s)"
    echo "Creating temporary database: $TEMP_DB"

    if ! mysql --defaults-extra-file="$MYSQL_CONFIG" -e "CREATE DATABASE $TEMP_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" 2>/dev/null; then
        echo -e "${RED}✗ Failed to create temporary database${NC}"
        return 1
    fi

    # Restore backup to temp database
    echo "Restoring backup to temporary database..."
    if gunzip -c "$backup_path" | mysql --defaults-extra-file="$MYSQL_CONFIG" "$TEMP_DB" 2>/dev/null; then
        echo -e "${GREEN}✓ Backup restored successfully${NC}"
    else
        echo -e "${RED}✗ Failed to restore backup${NC}"
        mysql --defaults-extra-file="$MYSQL_CONFIG" -e "DROP DATABASE IF EXISTS $TEMP_DB;" 2>/dev/null
        return 1
    fi

    # Verify table counts match metadata
    if [ -f "$metadata_path" ] && command -v jq > /dev/null 2>&1; then
        echo "Verifying table counts against metadata..."

        EXPECTED_EVENTS=$(jq -r '.table_counts.events // 0' "$metadata_path")
        EXPECTED_ORDERS=$(jq -r '.table_counts.orders // 0' "$metadata_path")
        EXPECTED_RESERVATIONS=$(jq -r '.table_counts.seat_reservations // 0' "$metadata_path")

        ACTUAL_EVENTS=$(mysql --defaults-extra-file="$MYSQL_CONFIG" "$TEMP_DB" -sN -e "SELECT COUNT(*) FROM events;" 2>/dev/null || echo "0")
        ACTUAL_ORDERS=$(mysql --defaults-extra-file="$MYSQL_CONFIG" "$TEMP_DB" -sN -e "SELECT COUNT(*) FROM orders;" 2>/dev/null || echo "0")
        ACTUAL_RESERVATIONS=$(mysql --defaults-extra-file="$MYSQL_CONFIG" "$TEMP_DB" -sN -e "SELECT COUNT(*) FROM seat_reservations;" 2>/dev/null || echo "0")

        echo "  Events: expected $EXPECTED_EVENTS, got $ACTUAL_EVENTS"
        echo "  Orders: expected $EXPECTED_ORDERS, got $ACTUAL_ORDERS"
        echo "  Reservations: expected $EXPECTED_RESERVATIONS, got $ACTUAL_RESERVATIONS"

        if [ "$EXPECTED_EVENTS" = "$ACTUAL_EVENTS" ] && \
           [ "$EXPECTED_ORDERS" = "$ACTUAL_ORDERS" ] && \
           [ "$EXPECTED_RESERVATIONS" = "$ACTUAL_RESERVATIONS" ]; then
            echo -e "${GREEN}✓ Table counts match metadata${NC}"
        else
            echo -e "${YELLOW}⚠ Table counts don't match metadata (data may have changed since backup)${NC}"
        fi
    fi

    # Clean up temporary database
    echo "Cleaning up temporary database..."
    mysql --defaults-extra-file="$MYSQL_CONFIG" -e "DROP DATABASE IF EXISTS $TEMP_DB;" 2>/dev/null
    echo -e "${GREEN}✓ Temporary database removed${NC}"

    echo ""
    echo -e "${GREEN}✅ Full verification passed${NC}"
    return 0
}

# Main execution
print_header "MySQL Backup Verification"

# Determine which backups to verify
if [ "$VERIFY_ALL" = true ]; then
    # Verify all backups
    BACKUPS=$(ls -t "$BACKUP_DIR"/backup_*.sql.gz 2>/dev/null || echo "")

    if [ -z "$BACKUPS" ]; then
        echo -e "${RED}No backups found in $BACKUP_DIR${NC}"
        exit 1
    fi

    TOTAL=0
    PASSED=0
    FAILED=0

    for backup in $BACKUPS; do
        backup_name=$(basename "$backup")
        TOTAL=$((TOTAL + 1))

        if verify_backup "$backup_name"; then
            PASSED=$((PASSED + 1))
        else
            FAILED=$((FAILED + 1))
        fi
    done

    echo ""
    print_header "Verification Summary"
    echo "Total backups: $TOTAL"
    echo -e "${GREEN}Passed: $PASSED${NC}"
    echo -e "${RED}Failed: $FAILED${NC}"

    exit $([ "$FAILED" -eq 0 ] && echo 0 || echo 1)

elif [ "$VERIFY_LATEST" = true ]; then
    # Verify latest backup only
    LATEST_BACKUP=$(ls -t "$BACKUP_DIR"/backup_*.sql.gz 2>/dev/null | head -1)

    if [ -z "$LATEST_BACKUP" ]; then
        echo -e "${RED}No backups found in $BACKUP_DIR${NC}"
        exit 1
    fi

    verify_backup "$(basename "$LATEST_BACKUP")"

elif [ -n "$BACKUP_FILE" ]; then
    # Verify specific backup
    verify_backup "$BACKUP_FILE"

else
    # No arguments provided
    echo -e "${RED}Error: No backup file specified${NC}"
    echo ""
    echo "Usage:"
    echo "  $0 <backup_file> [--level=<basic|sql|full>]"
    echo "  $0 --latest [--level=<basic|sql|full>]"
    echo "  $0 --all [--level=<basic|sql|full>]"
    echo "  $0 --help"
    exit 1
fi