#!/bin/bash

# Remote Supabase Database Backup Script
# Backs up all tables and storage buckets to local filesystem
# Uses .env from /database/supabase folder for connection details

set -e  # Exit on any error

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

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(cd "${SCRIPT_DIR}/../.." && pwd)"
DATABASE_DIR="${PROJECT_ROOT}/database"
ENV_FILE="${DATABASE_DIR}/supabase/.env"
BACKUP_TIMESTAMP=$(date -u +"%Y%m%d_%H%M%S_UTC")
BACKUP_DIR="${DATABASE_DIR}/backup/${BACKUP_TIMESTAMP}"

# Logging functions (send to stderr to avoid polluting command substitutions)
log()   { printf "%b[%s] %s%b\n" "$GREEN"  "$(date '+%Y-%m-%d %H:%M:%S')" "$1" "$NC" >&2; }
error() { printf "%b[ERROR] %s%b\n"        "$RED"    "$1"                           "$NC" >&2; }
warn()  { printf "%b[WARNING] %s%b\n"      "$YELLOW" "$1"                           "$NC" >&2; }

# Check prerequisites
check_prerequisites() {
    log "Checking prerequisites..."
    
    # Check if .env file exists
    if [[ ! -f "$ENV_FILE" ]]; then
        error ".env file not found at: $ENV_FILE"
        exit 1
    fi
    
    # Check required commands
    local required_commands=("pg_dump" "psql" "curl" "jq")
    for cmd in "${required_commands[@]}"; do
        if ! command -v "$cmd" &> /dev/null; then
            error "Required command '$cmd' not found. Please install it first."
            exit 1
        fi
    done
    
    log "Prerequisites check passed ✓"
}

# Validate environment variables
validate_env() {
    log "Validating environment variables..."
    
    # Validate SUPABASE_URL format
    if [[ ! "$SUPABASE_URL" =~ ^https://[a-zA-Z0-9-]+\.supabase\.co$ ]]; then
        error "Invalid SUPABASE_URL format. Expected: https://PROJECT_ID.supabase.co"
        exit 1
    fi
    
    # Validate service role key format (JWT structure)
    if [[ ! "$SUPABASE_SERVICE_ROLE_KEY" =~ ^eyJ[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+$ ]]; then
        error "Invalid SUPABASE_SERVICE_ROLE_KEY format. Expected JWT token."
        exit 1
    fi
    
    # Validate database password exists and has reasonable length
    if [[ -z "$SUPABASE_DB_PASSWORD" || ${#SUPABASE_DB_PASSWORD} -lt 8 ]]; then
        error "SUPABASE_DB_PASSWORD missing or too short (minimum 8 characters)"
        exit 1
    fi
    
    log "Environment validation passed ✓"
}

# Load environment variables
load_env() {
    log "Loading environment variables from $ENV_FILE"
    
    # Source the .env file
    set -a  # automatically export all variables
    # shellcheck source=/dev/null
    source "$ENV_FILE"
    set +a
    
    # Validate required environment variables
    if [[ -z "$SUPABASE_URL" || -z "$SUPABASE_SERVICE_ROLE_KEY" ]]; then
        error "Missing required environment variables: SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY"
        exit 1
    fi
    
    # Validate environment variables
    validate_env
    
    # Extract database connection details from Supabase URL
    # Format: https://PROJECT_ID.supabase.co
    PROJECT_ID=$(echo "$SUPABASE_URL" | sed -E 's|https://([^.]+)\.supabase\.co.*|\1|')
    
    if [[ -z "$PROJECT_ID" ]]; then
        error "Could not extract project ID from SUPABASE_URL: $SUPABASE_URL"
        exit 1
    fi
    
    # Construct database connection string
    DB_HOST="db.${PROJECT_ID}.supabase.co"
    DB_PORT="5432"
    DB_NAME="postgres"
    DB_USER="postgres"
    DB_PASSWORD="${SUPABASE_DB_PASSWORD}"
    
    # Ensure SSL for libpq tools (psql/pg_dump)
    export PGSSLMODE=require
    
    log "Environment loaded: Project ID = $PROJECT_ID"
}

# Create backup directory structure
create_backup_structure() {
    log "Creating backup directory structure at: $BACKUP_DIR"
    
    mkdir -p "$BACKUP_DIR"
    mkdir -p "$BACKUP_DIR/tables"
    mkdir -p "$BACKUP_DIR/storage"
    mkdir -p "$BACKUP_DIR/metadata"
    
    log "Backup directory structure created ✓"
}

# Escape special characters in .pgpass format
escape_pgpass_field() {
    local input="$1"
    # Escape backslashes first, then colons
    echo "$input" | sed 's/\\/\\\\/g' | sed 's/:/\\:/g'
}

# Setup secure database connection
setup_pgpass() {
    log "Setting up secure database connection..."
    
    # Create .pgpass file for secure password handling with proper escaping
    local pgpass_file="$HOME/.pgpass_backup_$$"
    local escaped_password
    escaped_password=$(escape_pgpass_field "$DB_PASSWORD")
    
    echo "$(escape_pgpass_field "$DB_HOST"):$(escape_pgpass_field "$DB_PORT"):$(escape_pgpass_field "$DB_NAME"):$(escape_pgpass_field "$DB_USER"):$escaped_password" > "$pgpass_file"
    chmod 600 "$pgpass_file"
    export PGPASSFILE="$pgpass_file"
    
    # Store path for cleanup
    PGPASS_TEMP_FILE="$pgpass_file"
    
    log "Secure database connection setup ✓"
}

# Cleanup secure database connection
cleanup_pgpass() {
    if [[ -n "$PGPASS_TEMP_FILE" && -f "$PGPASS_TEMP_FILE" ]]; then
        rm -f "$PGPASS_TEMP_FILE"
        unset PGPASSFILE
    fi
}

# Backup database tables
backup_tables() {
    log "Starting database tables backup..."
    
    # Setup secure password handling
    setup_pgpass
    
    # Create backup state file for recovery
    echo "BACKUP_STARTED:$(date -u +"%Y-%m-%dT%H:%M:%SZ")" > "$BACKUP_DIR/backup.state"
    
    # Full database dump
    log "Creating full database dump..."
    echo "FULL_DUMP_STARTED:$(date)" >> "$BACKUP_DIR/backup.state"
    if pg_dump \
        --host="$DB_HOST" \
        --port="$DB_PORT" \
        --username="$DB_USER" \
        --dbname="$DB_NAME" \
        --verbose \
        --clean \
        --if-exists \
        --create \
        --format=custom \
        --file="$BACKUP_DIR/full_database_backup.dump"; then
        echo "FULL_DUMP_COMPLETED:$(date)" >> "$BACKUP_DIR/backup.state"
    else
        error "Full database dump failed"
        cleanup_pgpass
        exit 1
    fi
    
    # Also create a readable SQL dump
    log "Creating readable SQL dump..."
    echo "SQL_DUMP_STARTED:$(date)" >> "$BACKUP_DIR/backup.state"
    if pg_dump \
        --host="$DB_HOST" \
        --port="$DB_PORT" \
        --username="$DB_USER" \
        --dbname="$DB_NAME" \
        --verbose \
        --clean \
        --if-exists \
        --create \
        --format=plain \
        --file="$BACKUP_DIR/full_database_backup.sql"; then
        echo "SQL_DUMP_COMPLETED:$(date)" >> "$BACKUP_DIR/backup.state"
    else
        warn "SQL dump creation failed, continuing with individual tables"
    fi
    
    # Individual table backups
    log "Creating individual table backups..."
    
    # Get list of all tables from relevant schemas with error handling
    TABLES=$(psql \
        --host="$DB_HOST" \
        --port="$DB_PORT" \
        --username="$DB_USER" \
        --dbname="$DB_NAME" \
        --tuples-only \
        --no-align \
        --command="SELECT schemaname || '.' || tablename FROM pg_tables WHERE schemaname IN ('public', 'auth', 'storage', 'graphql_public', 'realtime') ORDER BY schemaname, tablename;")
    
    if [[ -z "$TABLES" ]]; then
        warn "No tables found in target schemas"
        cleanup_pgpass
        return 0
    fi
    
    for table in $TABLES; do
        if [[ -n "$table" && "$table" != " " ]]; then
            log "Backing up table: $table"
            echo "TABLE_STARTED:$table:$(date)" >> "$BACKUP_DIR/backup.state"
            
            # Parse schema and table name for proper quoting
            local schema="${table%%.*}"
            local name="${table#*.}"
            local quoted_table="\"${schema}\".\"${name}\""
            local safe_table_name="${schema}_${name}"
            
            # Binary dump with error handling (properly quote schema.table)
            if pg_dump \
                --host="$DB_HOST" \
                --port="$DB_PORT" \
                --username="$DB_USER" \
                --dbname="$DB_NAME" \
                --table="$quoted_table" \
                --data-only \
                --format=custom \
                --file="$BACKUP_DIR/tables/${safe_table_name}.dump"; then
                
                echo "TABLE_DUMP_COMPLETED:$table:$(date)" >> "$BACKUP_DIR/backup.state"
                
                # CSV export using \copy for better memory handling (properly quote schema.table)
                if psql \
                    --host="$DB_HOST" \
                    --port="$DB_PORT" \
                    --username="$DB_USER" \
                    --dbname="$DB_NAME" \
                    --command="\\copy $quoted_table TO '$BACKUP_DIR/tables/${safe_table_name}.csv' WITH CSV HEADER"; then
                    echo "TABLE_CSV_COMPLETED:$table:$(date)" >> "$BACKUP_DIR/backup.state"
                else
                    warn "Could not export $table to CSV (might be empty or have permission issues)"
                fi
            else
                warn "Failed to backup table: $table"
                echo "TABLE_FAILED:$table:$(date)" >> "$BACKUP_DIR/backup.state"
            fi
        fi
    done
    
    # Clean up secure connection
    cleanup_pgpass
    
    log "Database tables backup completed ✓"
}

# URL encode a string for safe API usage
url_encode() {
    local string="$1"
    # Use printf to handle special characters properly
    printf '%s\n' "$string" | sed '
        s/ /%20/g
        s/!/%21/g
        s/"/%22/g
        s/#/%23/g
        s/\$/%24/g
        s/&/%26/g
        s/'\''/%27/g
        s/(/%28/g
        s/)/%29/g
        s/\*/%2A/g
        s/+/%2B/g
        s/,/%2C/g
        s/\//%2F/g
        s/:/%3A/g
        s/;/%3B/g
        s/=/%3D/g
        s/?/%3F/g
        s/@/%40/g
        s/\[/%5B/g
        s/\\/%5C/g
        s/\]/%5D/g
        s/\^/%5E/g
        s/`/%60/g
        s/{/%7B/g
        s/|/%7C/g
        s/}/%7D/g
        s/~/%7E/g
    '
}

# Sanitize file path to prevent directory traversal
sanitize_path() {
    local input="$1"
    # Remove any path traversal attempts and leading slashes
    echo "$input" | sed 's/\.\.//g' | sed 's|^/||' | sed 's|//|/|g'
}

# Atomic download with retry
download_object_atomic() {
    local bucket="$1"
    local object="$2"
    local target_dir="$3"
    local attempts=3
    
    # Sanitize object path for filesystem
    local safe_object
    safe_object=$(sanitize_path "$object")
    
    # URL encode object path for API call
    local encoded_object
    encoded_object=$(url_encode "$object")
    
    # Create directory structure
    local object_dir
    object_dir=$(dirname "$safe_object")
    if [[ "$object_dir" != "." ]]; then
        mkdir -p "$target_dir/$object_dir"
    fi
    
    local temp_file="$target_dir/${safe_object}.tmp.$$"
    local final_file="$target_dir/$safe_object"
    
    for ((i=1; i<=attempts; i++)); do
        log "  Downloading: $object (attempt $i/$attempts)"
        
        if curl --silent --show-error --fail \
            -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
            -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
            "$SUPABASE_URL/storage/v1/object/$bucket/$encoded_object" \
            -o "$temp_file" && [[ -s "$temp_file" ]]; then
            
            mv "$temp_file" "$final_file"
            return 0
        else
            rm -f "$temp_file"
            if [[ $i -eq $attempts ]]; then
                warn "Failed to download $object after $attempts attempts"
                return 1
            fi
            sleep 2
        fi
    done
}

# Get list of storage buckets with error handling
get_storage_buckets() {
    log "Fetching storage bucket list..."
    
    local response
    response=$(curl --silent --show-error --fail \
        -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
        -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
        "$SUPABASE_URL/storage/v1/bucket")
    
    if [[ -n "$response" ]]; then
        # Check if response is valid JSON array and extract bucket names (not IDs)
        echo "$response" | jq -r 'if type=="array" then .[].name else empty end' || {
            warn "Invalid response format from bucket list API"
            return 1
        }
    else
        warn "Could not fetch bucket list - empty response"
        return 1
    fi
}

# List objects in a bucket with pagination support
list_bucket_objects() {
    local bucket="$1"
    local limit=1000
    local offset=0
    local all_objects=""
    
    while true; do
        log "  Fetching objects from $bucket (offset: $offset, limit: $limit)"
        
        local response
        response=$(curl --silent --show-error --fail \
            -X POST \
            -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
            -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
            -H "Content-Type: application/json" \
            -d "{\"prefix\": \"\", \"limit\": $limit, \"offset\": $offset}" \
            "$SUPABASE_URL/storage/v1/object/list/$bucket")
        
        if [[ -z "$response" ]]; then
            warn "Empty response from object list API for bucket: $bucket"
            break
        fi
        
        # Extract object names from response
        local objects
        objects=$(echo "$response" | jq -r 'if type=="array" then .[].name else empty end')
        
        if [[ -z "$objects" ]]; then
            # No more objects, we're done
            break
        fi
        
        # Add to our collection
        if [[ -z "$all_objects" ]]; then
            all_objects="$objects"
        else
            all_objects="$all_objects"$'\n'"$objects"
        fi
        
        # Check if we got fewer objects than requested (indicates last page)
        local object_count
        object_count=$(echo "$objects" | wc -l)
        if [[ $object_count -lt $limit ]]; then
            break
        fi
        
        # Move to next page
        offset=$((offset + limit))
    done
    
    if [[ -n "$all_objects" ]]; then
        echo "$all_objects"
    fi
}

# Backup storage buckets
backup_storage() {
    log "Starting storage buckets backup..."
    
    echo "STORAGE_STARTED:$(date)" >> "$BACKUP_DIR/backup.state"
    
    # Get bucket list (compatible with bash 3.2+)
    local buckets_output
    buckets_output=$(get_storage_buckets)
    
    if [[ -z "$buckets_output" ]]; then
        warn "No storage buckets found to backup"
        echo "STORAGE_NO_BUCKETS:$(date)" >> "$BACKUP_DIR/backup.state"
        return 0
    fi
    
    # Process each bucket line by line
    while IFS= read -r bucket; do
        if [[ -n "$bucket" ]]; then
            log "Backing up storage bucket: $bucket"
            echo "BUCKET_STARTED:$bucket:$(date)" >> "$BACKUP_DIR/backup.state"
            
            mkdir -p "$BACKUP_DIR/storage/$bucket"
            
            # Get list of objects in bucket with pagination
            local objects
            objects=$(list_bucket_objects "$bucket")
            
            if [[ -n "$objects" ]]; then
                local download_count=0
                local failed_count=0
                
                while IFS= read -r object; do
                    if [[ -n "$object" ]]; then
                        if download_object_atomic "$bucket" "$object" "$BACKUP_DIR/storage/$bucket"; then
                            ((download_count++))
                            echo "OBJECT_DOWNLOADED:$bucket:$object:$(date)" >> "$BACKUP_DIR/backup.state"
                        else
                            ((failed_count++))
                            echo "OBJECT_FAILED:$bucket:$object:$(date)" >> "$BACKUP_DIR/backup.state"
                        fi
                    fi
                done <<< "$objects"
                
                log "Bucket $bucket: $download_count downloaded, $failed_count failed"
                echo "BUCKET_COMPLETED:$bucket:$download_count:$failed_count:$(date)" >> "$BACKUP_DIR/backup.state"
            else
                warn "No objects found in bucket: $bucket"
                echo "BUCKET_EMPTY:$bucket:$(date)" >> "$BACKUP_DIR/backup.state"
            fi
        fi
    done <<< "$buckets_output"
    
    echo "STORAGE_COMPLETED:$(date)" >> "$BACKUP_DIR/backup.state"
    log "Storage buckets backup completed ✓"
}

# Create backup metadata
create_metadata() {
    log "Creating backup metadata..."
    
    cat > "$BACKUP_DIR/metadata/backup_info.json" << EOF
{
    "backup_date": "$(date -u +"%Y-%m-%dT%H:%M:%SZ")",
    "backup_version": "2.0",
    "project_id": "$PROJECT_ID",
    "supabase_url": "$SUPABASE_URL",
    "backup_directory": "$BACKUP_DIR",
    "script_version": "2.0.0",
    "environment": "production",
    "schemas_backed_up": ["public", "auth", "storage", "graphql_public", "realtime"],
    "api_improvements": {
        "storage_api": "Uses correct bucket names and POST for object listing",
        "pagination": "Handles large buckets with proper pagination",
        "url_encoding": "Properly encodes object paths with special characters",
        "error_handling": "Improved error visibility and recovery"
    }
}
EOF
    
    # Create README
    cat > "$BACKUP_DIR/README.md" << EOF
# Supabase Database Backup - $BACKUP_TIMESTAMP

This backup was created on $(date -u) from Supabase project: \`$PROJECT_ID\`

## Structure

- \`full_database_backup.dump\` - Complete database backup (binary format)
- \`full_database_backup.sql\` - Complete database backup (SQL format)
- \`tables/\` - Individual table backups from multiple schemas
  - \`*.dump\` - Binary format table dumps
  - \`*.csv\` - CSV exports for easy viewing
- \`storage/\` - Storage bucket backups organized by bucket name
- \`metadata/\` - Backup metadata and information
- \`backup.state\` - Recovery and progress tracking

## Schemas Included

This backup includes tables from:
- \`public\` - Application tables
- \`auth\` - Supabase authentication tables  
- \`storage\` - Supabase storage metadata
- \`graphql_public\` - GraphQL schema tables
- \`realtime\` - Realtime subscription tables

## Restore Commands

### Full Database Restore

Option 1 - Restore to new database (recommended):
\`\`\`bash
# Connect to maintenance DB so pg_restore can run CREATE DATABASE from the archive
pg_restore --clean --if-exists --create --dbname=postgres full_database_backup.dump
\`\`\`

Option 2 - Restore to existing database:
\`\`\`bash
createdb target_db
pg_restore --clean --if-exists --dbname=target_db full_database_backup.dump
\`\`\`

### Individual Table Restore (note schema qualification)
\`\`\`bash
pg_restore --clean --if-exists --dbname=target_db --table=schema.table_name tables/schema_table_name.dump
\`\`\`

### Connectivity Test
\`\`\`bash
# Test connection to Supabase (replace PROJECT_ID with your project ID)
psql "host=db.PROJECT_ID.supabase.co port=5432 dbname=postgres user=postgres sslmode=require" -c "select version();"
\`\`\`

## Storage API Improvements

This backup uses the correct Supabase Storage API:
- ✅ Uses bucket names (not IDs) for downloads
- ✅ POST requests for object listing with pagination support
- ✅ Proper URL encoding for objects with special characters
- ✅ Improved error handling and retry mechanisms

## Notes

- Storage files maintain their original directory structure
- CSV files are provided for easy data inspection
- All timestamps are in UTC
- Object paths with special characters are properly handled
- Large buckets are paginated automatically
EOF
    
    log "Backup metadata created ✓"
}

# Cleanup function for trap
cleanup_on_exit() {
    cleanup_pgpass
    log "Cleanup completed"
}

# Main execution
main() {
    # Set up cleanup trap
    trap cleanup_on_exit EXIT INT TERM
    
    log "Starting Supabase remote database backup..."
    log "Target backup directory: $BACKUP_DIR"
    
    check_prerequisites
    load_env
    create_backup_structure
    backup_tables
    backup_storage
    create_metadata
    
    # Final state
    echo "BACKUP_COMPLETED:$(date -u +"%Y-%m-%dT%H:%M:%SZ")" >> "$BACKUP_DIR/backup.state"
    
    log "✅ Backup completed successfully!"
    log "Backup location: $BACKUP_DIR"
    
    # Calculate and display backup size
    local backup_size
    backup_size=$(du -sh "$BACKUP_DIR" 2>/dev/null | cut -f1 || echo "unknown")
    log "Backup size: $backup_size"
    
    # Display summary
    if [[ -f "$BACKUP_DIR/backup.state" ]]; then
        local table_count
        local storage_count
        table_count=$(grep -c "TABLE_DUMP_COMPLETED" "$BACKUP_DIR/backup.state" 2>/dev/null || echo "0")
        storage_count=$(grep -c "OBJECT_DOWNLOADED" "$BACKUP_DIR/backup.state" 2>/dev/null || echo "0")
        log "Summary: $table_count tables backed up, $storage_count objects downloaded"
    fi
}

# Run main function
main "$@"