#!/bin/bash

# Database Comparison Tool for Showprima
# Compare two 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"
CURRENT_DB="$PROJECT_ROOT/database/database.sqlite"

# 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}Database Comparison Tool${NC}"
echo -e "${YELLOW}========================================${NC}"
echo ""

# Function to get table counts
get_table_counts() {
    local db_path="$1"
    local label="$2"

    echo -e "${BLUE}$label Table Counts:${NC}"
    echo "  Events: $(sqlite3 "$db_path" "SELECT COUNT(*) FROM events;")"
    echo "  Orders: $(sqlite3 "$db_path" "SELECT COUNT(*) FROM orders;")"
    echo "  Seat Reservations: $(sqlite3 "$db_path" "SELECT COUNT(*) FROM seat_reservations;")"
    echo "  Users: $(sqlite3 "$db_path" "SELECT COUNT(*) FROM users;")"
    echo "  Seats: $(sqlite3 "$db_path" "SELECT COUNT(*) FROM seats;")"
}

# Function to compare two databases
compare_databases() {
    local db1="$1"
    local db2="$2"

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

    # Get counts from both databases
    local events1=$(sqlite3 "$db1" "SELECT COUNT(*) FROM events;")
    local events2=$(sqlite3 "$db2" "SELECT COUNT(*) FROM events;")
    local events_diff=$((events2 - events1))

    local orders1=$(sqlite3 "$db1" "SELECT COUNT(*) FROM orders;")
    local orders2=$(sqlite3 "$db2" "SELECT COUNT(*) FROM orders;")
    local orders_diff=$((orders2 - orders1))

    local reservations1=$(sqlite3 "$db1" "SELECT COUNT(*) FROM seat_reservations;")
    local reservations2=$(sqlite3 "$db2" "SELECT COUNT(*) FROM seat_reservations;")
    local reservations_diff=$((reservations2 - reservations1))

    local users1=$(sqlite3 "$db1" "SELECT COUNT(*) FROM users;")
    local users2=$(sqlite3 "$db2" "SELECT COUNT(*) FROM users;")
    local users_diff=$((users2 - users1))

    echo -e "${BLUE}Row Count Differences:${NC}"
    printf "  Events: %d → %d " "$events1" "$events2"
    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 " "$orders1" "$orders2"
    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 " "$reservations1" "$reservations2"
    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 " "$users1" "$users2"
    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 ""

    # Schema comparison
    echo -e "${BLUE}Schema Comparison:${NC}"

    local schema1=$(mktemp)
    local schema2=$(mktemp)

    sqlite3 "$db1" ".schema" > "$schema1"
    sqlite3 "$db2" ".schema" > "$schema2"

    if diff -q "$schema1" "$schema2" > /dev/null; then
        echo -e "  ${GREEN}✓ Schemas are identical${NC}"
    else
        echo -e "  ${YELLOW}⚠ Schema differences detected${NC}"
        echo ""
        echo "  Run this command to see detailed schema diff:"
        echo "  diff <(sqlite3 '$db1' '.schema') <(sqlite3 '$db2' '.schema')"
    fi

    rm "$schema1" "$schema2"
}

# 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_*.sqlite 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

if [ ! -f "$CURRENT_DB" ]; then
    echo -e "${RED}Error: Current database not found: $CURRENT_DB${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_table_counts "$BACKUP_PATH" "Backup ($BACKUP_NAME)"
echo ""
get_table_counts "$CURRENT_DB" "Current Database"

compare_databases "$BACKUP_PATH" "$CURRENT_DB"

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