#!/bin/bash

# Insert single test UNESCO petition signature
# Uses same database connection as backup script

set -e

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

# Configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(cd "${SCRIPT_DIR}/../.." && pwd)"
DATABASE_DIR="${PROJECT_ROOT}/database"
ENV_FILE="${DATABASE_DIR}/.env"

# Logging functions
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; }

# Load environment variables
load_env() {
    log "Loading environment from $ENV_FILE"
    
    if [[ ! -f "$ENV_FILE" ]]; then
        error ".env file not found at: $ENV_FILE"
        exit 1
    fi
    
    # shellcheck source=/dev/null
    source "$ENV_FILE"
    
    # Validate required variables
    if [[ -z "$SUPABASE_URL" || -z "$SUPABASE_DB_PASSWORD" ]]; then
        error "Missing required environment variables"
        exit 1
    fi
    
    # Extract project ID and construct database connection
    PROJECT_ID=$(echo "$SUPABASE_URL" | sed -E 's|https://([^.]+)\.supabase\.co.*|\1|')
    DB_HOST="db.${PROJECT_ID}.supabase.co"
    DB_PORT="5432"
    DB_NAME="postgres"
    DB_USER="postgres"
    DB_PASSWORD="${SUPABASE_DB_PASSWORD}"
    
    # Ensure SSL
    export PGSSLMODE=require
    
    log "Environment loaded: Project ID = $PROJECT_ID"
}

# Setup secure database connection
setup_pgpass() {
    log "Setting up secure database connection..."
    
    # Create .pgpass file with proper escaping
    local pgpass_file="$HOME/.pgpass_insert_$$"
    local escaped_password
    escaped_password=$(echo "$DB_PASSWORD" | sed 's/\\/\\\\/g' | sed 's/:/\\:/g')
    
    echo "${DB_HOST}:${DB_PORT}:${DB_NAME}:${DB_USER}:${escaped_password}" > "$pgpass_file"
    chmod 600 "$pgpass_file"
    export PGPASSFILE="$pgpass_file"
    
    # Store for cleanup
    PGPASS_TEMP_FILE="$pgpass_file"
    
    log "Database connection setup complete ✓"
}

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

# Insert single test signature
insert_test_signature() {
    log "Inserting single test signature with email=junwon@junwon.com and real_name=TEST"
    
    # Check connection first
    if ! psql --host="$DB_HOST" --port="$DB_PORT" --username="$DB_USER" --dbname="$DB_NAME" --command="SELECT 1;" >/dev/null; then
        error "Failed to connect to database"
        exit 1
    fi
    
    log "Database connection verified ✓"
    
    local current_time
    current_time=$(date '+%Y-%m-%d %H:%M:%S')
    log "[$current_time] Inserting test signature"
    
    # Insert single signature
    psql --host="$DB_HOST" --port="$DB_PORT" --username="$DB_USER" --dbname="$DB_NAME" --command="
        INSERT INTO unesco_petition_signatures (email, real_name, home_location, ip_address, user_agent) 
        VALUES (
            'junwon@junwon.com', 
            'TEST', 
            'Test Location ($(date))', 
            '127.0.0.1', 
            'Test Script User Agent'
        );
    " || {
        error "Failed to insert test signature"
        exit 1
    }
    
    log "✅ Test signature inserted successfully"
    
    # Show current count
    local count
    count=$(psql --host="$DB_HOST" --port="$DB_PORT" --username="$DB_USER" --dbname="$DB_NAME" --tuples-only --no-align --command="
        SELECT COUNT(*) FROM unesco_petition_signatures WHERE email = 'junwon@junwon.com' AND real_name = 'TEST';
    ")
    log "Total TEST signatures: $count"
}

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

# Main execution
main() {
    trap cleanup_on_exit EXIT INT TERM
    
    log "Starting UNESCO petition test signature insertion..."
    
    load_env
    setup_pgpass
    insert_test_signature
    
    log "✅ Test signature insertion completed successfully!"
}

# Run main function
main "$@"