#!/bin/bash

# Database Restore Script for Showprima
# Restores from timestamped backups with verification and safety checks

set -e  # Exit on error

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"
DB_PATH="$PROJECT_ROOT/database/database.sqlite"
BACKUP_DIR="$PROJECT_ROOT/database/backups"

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

echo -e "${YELLOW}========================================${NC}"
echo -e "${YELLOW}Showprima Database Restore${NC}"
echo -e "${YELLOW}========================================${NC}"
echo ""

# Check if backup file is provided
if [ -z "$1" ]; then
    echo -e "${RED}Error: No backup file specified${NC}"
    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"
METADATA_PATH="$BACKUP_DIR/$BACKUP_NAME.meta.json"

# Check if backup exists
if [ ! -f "$BACKUP_PATH" ]; then
    echo -e "${RED}Error: Backup file not found: $BACKUP_PATH${NC}"
    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

# Display backup metadata if available
if [ -f "$METADATA_PATH" ]; then
    echo -e "${BLUE}Backup Metadata:${NC}"
    echo "----------------------------------------"
    cat "$METADATA_PATH" | jq -r '
        "Created: \(.backup_timestamp)",
        "Database Size: \(.database_size)",
        "Git Commit: \(.git_commit[0:8])",
        "Git Branch: \(.git_branch)",
        "Last Migration: \(.last_migration)",
        "",
        "Table Counts:",
        "  Events: \(.table_counts.events)",
        "  Orders: \(.table_counts.orders)",
        "  Reservations: \(.table_counts.seat_reservations)",
        "  Users: \(.table_counts.users)"
    ' 2>/dev/null || echo "Metadata file exists but could not be parsed"
    echo "----------------------------------------"
    echo ""
fi

# Verify backup integrity
echo -e "${YELLOW}Verifying backup integrity...${NC}"
set +e  # Temporarily disable exit on error
INTEGRITY_RESULT=$(sqlite3 "$BACKUP_PATH" "PRAGMA integrity_check;" 2>&1)
INTEGRITY_EXIT=$?
set -e  # Re-enable exit on error

if [ $INTEGRITY_EXIT -ne 0 ]; then
    echo -e "${RED}✗ Backup integrity check failed - file may be corrupted${NC}"
    echo -e "${RED}Error: $INTEGRITY_RESULT${NC}"
    exit 1
fi
echo -e "${GREEN}✓ Backup integrity verified${NC}"
echo ""

# Create a safety backup of current database before restoring
SAFETY_TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
SAFETY_BACKUP="$BACKUP_DIR/pre_restore_safety_${SAFETY_TIMESTAMP}.sqlite"

if [ -f "$DB_PATH" ]; then
    echo -e "${YELLOW}Creating safety backup of current database...${NC}"
    cp "$DB_PATH" "$SAFETY_BACKUP"

    # Create metadata for safety backup
    cat > "${SAFETY_BACKUP}.meta.json" << EOF
{
  "backup_timestamp": "$(date -u +"%Y-%m-%dT%H:%M:%SZ")",
  "backup_type": "pre_restore_safety",
  "restoring_from": "$BACKUP_NAME",
  "git_commit": "$(git rev-parse HEAD 2>/dev/null || echo 'not-a-git-repo')",
  "git_branch": "$(git branch --show-current 2>/dev/null || echo 'unknown')"
}
EOF

    echo -e "${GREEN}✓ Safety backup created: pre_restore_safety_${SAFETY_TIMESTAMP}.sqlite${NC}"
    echo ""
fi

# Confirmation prompt
echo -e "${RED}WARNING: This will replace your current database!${NC}"
echo ""
echo "Current database: $DB_PATH"
echo "Restore from: $BACKUP_NAME"
echo "Safety backup: pre_restore_safety_${SAFETY_TIMESTAMP}.sqlite"
echo ""
read -p "Are you sure you want to proceed? (type 'yes' to continue): " CONFIRM

if [ "$CONFIRM" != "yes" ]; then
    echo -e "${YELLOW}Restore cancelled${NC}"
    exit 0
fi

echo ""
echo -e "${YELLOW}Restoring database...${NC}"

# Perform the restore
cp "$BACKUP_PATH" "$DB_PATH"

# Verify restored database
echo -e "${YELLOW}Verifying restored database...${NC}"
sqlite3 "$DB_PATH" "PRAGMA integrity_check;" > /dev/null

if [ $? -eq 0 ]; then
    echo -e "${GREEN}✓ Database restored successfully${NC}"
else
    echo -e "${RED}✗ Restored database integrity check failed${NC}"
    echo -e "${YELLOW}Rolling back to safety backup...${NC}"
    cp "$SAFETY_BACKUP" "$DB_PATH"
    exit 1
fi

# Get restored database stats
EVENTS_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM events;")
ORDERS_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM orders;")
RESERVATIONS_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM seat_reservations;")
USERS_COUNT=$(sqlite3 "$DB_PATH" "SELECT COUNT(*) FROM users;")

echo ""
echo -e "${GREEN}========================================${NC}"
echo -e "${GREEN}Restore Completed Successfully!${NC}"
echo -e "${GREEN}========================================${NC}"
echo ""
echo "Restored from: $BACKUP_NAME"
echo ""
echo "Current table counts:"
echo "  - Events: $EVENTS_COUNT"
echo "  - Orders: $ORDERS_COUNT"
echo "  - Seat Reservations: $RESERVATIONS_COUNT"
echo "  - Users: $USERS_COUNT"
echo ""
echo "Safety backup available at:"
echo "  $SAFETY_BACKUP"
echo ""
echo -e "${YELLOW}Important: Restart any running Laravel services to ensure they use the restored database.${NC}"
echo ""