#!/bin/bash

# MySQL Database Comparison Tool for Showprima
# Compare two MySQL database backups to see what changed

set -e

# 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
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
RED='\033[0;31m'
BLUE='\033[0;34m'
NC='\033[0m'

echo -e "${YELLOW}========================================${NC}"
echo -e "${YELLOW}MySQL Database Comparison Tool${NC}"
echo -e "${YELLOW}========================================${NC}"
echo ""

# Function to get table counts from current database
get_current_table_counts() {
    echo -e "${BLUE}Current Database Table Counts:${NC}"
    echo "  Events: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM events;" -sN 2>/dev/null)"
    echo "  Orders: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM orders;" -sN 2>/dev/null)"
    echo "  Seat Reservations: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM seat_reservations;" -sN 2>/dev/null)"
    echo "  Users: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM users;" -sN 2>/dev/null)"
    echo "  Seats: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM seats;" -sN 2>/dev/null)"
}

# Function to get table counts from backup
get_backup_table_counts() {
    local backup_path="$1"
    local label="$2"

    # Create temporary database
    local temp_db="showprima_compare_temp_$$"
    mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" -e "CREATE DATABASE IF NOT EXISTS $temp_db;" 2>/dev/null

    # Restore backup to temp database
    gunzip -c "$backup_path" | mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" 2>/dev/null

    echo -e "${BLUE}$label Table Counts:${NC}"
    echo "  Events: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM events;" -sN 2>/dev/null)"
    echo "  Orders: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM orders;" -sN 2>/dev/null)"
    echo "  Seat Reservations: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM seat_reservations;" -sN 2>/dev/null)"
    echo "  Users: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM users;" -sN 2>/dev/null)"
    echo "  Seats: $(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM seats;" -sN 2>/dev/null)"

    # Get counts for comparison
    BACKUP_EVENTS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM events;" -sN 2>/dev/null)
    BACKUP_ORDERS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM orders;" -sN 2>/dev/null)
    BACKUP_RESERVATIONS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM seat_reservations;" -sN 2>/dev/null)
    BACKUP_USERS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$temp_db" -e "SELECT COUNT(*) FROM users;" -sN 2>/dev/null)

    # Drop temporary database
    mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" -e "DROP DATABASE $temp_db;" 2>/dev/null
}

# Function to compare databases
compare_databases() {
    local backup_path="$1"

    echo ""
    echo -e "${YELLOW}Comparing databases...${NC}"
    echo ""

    # Get current counts
    CURRENT_EVENTS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM events;" -sN 2>/dev/null)
    CURRENT_ORDERS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM orders;" -sN 2>/dev/null)
    CURRENT_RESERVATIONS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM seat_reservations;" -sN 2>/dev/null)
    CURRENT_USERS=$(mysql -u"$DB_USERNAME" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_DATABASE" -e "SELECT COUNT(*) FROM users;" -sN 2>/dev/null)

    # Get backup counts (already set by get_backup_table_counts)
    local events_diff=$((CURRENT_EVENTS - BACKUP_EVENTS))
    local orders_diff=$((CURRENT_ORDERS - BACKUP_ORDERS))
    local reservations_diff=$((CURRENT_RESERVATIONS - BACKUP_RESERVATIONS))
    local users_diff=$((CURRENT_USERS - BACKUP_USERS))

    echo -e "${BLUE}Row Count Differences:${NC}"

    printf "  Events: %d → %d " "$BACKUP_EVENTS" "$CURRENT_EVENTS"
    if [ $events_diff -gt 0 ]; then
        echo -e "${GREEN}(+$events_diff)${NC}"
    elif [ $events_diff -lt 0 ]; then
        echo -e "${RED}($events_diff)${NC}"
    else
        echo "(no change)"
    fi

    printf "  Orders: %d → %d " "$BACKUP_ORDERS" "$CURRENT_ORDERS"
    if [ $orders_diff -gt 0 ]; then
        echo -e "${GREEN}(+$orders_diff)${NC}"
    elif [ $orders_diff -lt 0 ]; then
        echo -e "${RED}($orders_diff)${NC}"
    else
        echo "(no change)"
    fi

    printf "  Seat Reservations: %d → %d " "$BACKUP_RESERVATIONS" "$CURRENT_RESERVATIONS"
    if [ $reservations_diff -gt 0 ]; then
        echo -e "${GREEN}(+$reservations_diff)${NC}"
    elif [ $reservations_diff -lt 0 ]; then
        echo -e "${RED}($reservations_diff)${NC}"
    else
        echo "(no change)"
    fi

    printf "  Users: %d → %d " "$BACKUP_USERS" "$CURRENT_USERS"
    if [ $users_diff -gt 0 ]; then
        echo -e "${GREEN}(+$users_diff)${NC}"
    elif [ $users_diff -lt 0 ]; then
        echo -e "${RED}($users_diff)${NC}"
    else
        echo "(no change)"
    fi

    echo ""
}

# Main logic
if [ -z "$1" ]; then
    echo "Compare current database with a backup"
    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"

if [ ! -f "$BACKUP_PATH" ]; then
    echo -e "${RED}Error: Backup not found: $BACKUP_PATH${NC}"
    exit 1
fi

# Show metadata if available
METADATA_PATH="$BACKUP_DIR/$BACKUP_NAME.meta.json"
if [ -f "$METADATA_PATH" ]; then
    echo -e "${BLUE}Backup Metadata:${NC}"
    cat "$METADATA_PATH" | jq -r '
        "  Created: \(.backup_timestamp)",
        "  Git Commit: \(.git_commit[0:8])",
        "  Last Migration: \(.last_migration)"
    ' 2>/dev/null || echo "  Metadata file exists but could not be parsed"
    echo ""
fi

get_backup_table_counts "$BACKUP_PATH" "Backup ($BACKUP_NAME)"
echo ""
get_current_table_counts

compare_databases "$BACKUP_PATH"

echo ""
echo -e "${GREEN}Comparison complete${NC}"
echo ""