#!/bin/bash

# Database Backup Script for Showprima
# Auto-detects database type and creates timestamped backups
# Supports both SQLite (local dev) and MySQL (production/dev server)

set -e  # Exit on error

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

# Load database helper to detect connection type
if [ -f "$SCRIPT_DIR/_db-helper.sh" ]; then
    source "$SCRIPT_DIR/_db-helper.sh"
fi

# Detect database type from .env
if [ -f "$PROJECT_ROOT/.env" ]; then
    DB_CONNECTION=$(grep "^DB_CONNECTION=" "$PROJECT_ROOT/.env" | cut -d '=' -f2 | tr -d '"' || echo "sqlite")
else
    DB_CONNECTION="sqlite"
fi

# Route to appropriate backup script
if [ "$DB_CONNECTION" = "mysql" ]; then
    # Use MySQL backup script
    if [ -f "$SCRIPT_DIR/backup-db-mysql.sh" ]; then
        exec "$SCRIPT_DIR/backup-db-mysql.sh" "$@"
    else
        echo "Error: MySQL backup script not found"
        exit 1
    fi
fi

# Continue with SQLite backup (for local development)
DB_PATH="$PROJECT_ROOT/database/database.sqlite"
BACKUP_DIR="$PROJECT_ROOT/database/backups"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_NAME="backup_${TIMESTAMP}.sqlite"
BACKUP_PATH="$BACKUP_DIR/$BACKUP_NAME"
METADATA_PATH="$BACKUP_DIR/$BACKUP_NAME.meta.json"

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

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

# Check if database exists
if [ ! -f "$DB_PATH" ]; then
    echo -e "${RED}Error: Database not found at $DB_PATH${NC}"
    exit 1
fi

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Get database size
DB_SIZE=$(du -h "$DB_PATH" | cut -f1)

# Get row counts for key tables
echo -e "${YELLOW}Collecting database metadata...${NC}"
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;")

# Get last migration
LAST_MIGRATION=$(sqlite3 "$DB_PATH" "SELECT migration FROM migrations ORDER BY id DESC LIMIT 1;" 2>/dev/null || echo "unknown")

# Copy database file
echo -e "${YELLOW}Creating backup...${NC}"
cp "$DB_PATH" "$BACKUP_PATH"

# Verify backup integrity
echo -e "${YELLOW}Verifying backup integrity...${NC}"
sqlite3 "$BACKUP_PATH" "PRAGMA integrity_check;" > /dev/null

if [ $? -eq 0 ]; then
    echo -e "${GREEN}✓ Backup integrity verified${NC}"
else
    echo -e "${RED}✗ Backup integrity check failed${NC}"
    rm "$BACKUP_PATH"
    exit 1
fi

# Create metadata file
cat > "$METADATA_PATH" << EOF
{
  "backup_timestamp": "$(date -u +"%Y-%m-%dT%H:%M:%SZ")",
  "original_db_path": "$DB_PATH",
  "backup_path": "$BACKUP_PATH",
  "database_size": "$DB_SIZE",
  "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')",
  "last_migration": "$LAST_MIGRATION",
  "table_counts": {
    "events": $EVENTS_COUNT,
    "orders": $ORDERS_COUNT,
    "seat_reservations": $RESERVATIONS_COUNT,
    "users": $USERS_COUNT
  },
  "php_version": "$(php -v | head -n 1)",
  "laravel_version": "$(cd "$PROJECT_ROOT" && php artisan --version 2>/dev/null || echo 'unknown')"
}
EOF

# Clean up old backups (keep last 10)
echo -e "${YELLOW}Cleaning up old backups (keeping last 10)...${NC}"
cd "$BACKUP_DIR"
ls -t backup_*.sqlite 2>/dev/null | tail -n +11 | while read file; do
    echo "  Removing old backup: $file"
    rm -f "$file"
    rm -f "${file}.meta.json"
done

# Print summary
echo ""
echo -e "${GREEN}========================================${NC}"
echo -e "${GREEN}Backup Created Successfully!${NC}"
echo -e "${GREEN}========================================${NC}"
echo ""
echo "Backup file: $BACKUP_NAME"
echo "Location: $BACKUP_DIR"
echo "Database size: $DB_SIZE"
echo ""
echo "Table counts:"
echo "  - Events: $EVENTS_COUNT"
echo "  - Orders: $ORDERS_COUNT"
echo "  - Seat Reservations: $RESERVATIONS_COUNT"
echo "  - Users: $USERS_COUNT"
echo ""
echo "To restore this backup, run:"
echo "  ./scripts/restore-db.sh $BACKUP_NAME"
echo ""