#!/bin/bash
# extract_jobrunr_schema.sh
# Script to locate JobRunr JARs in Gradle cache and generate database schema SQL files.
# Usage: ./extract_jobrunr_schema.sh [databaseType] [tablePrefix]
# databaseType: postgres, mysql, mariadb, oracle, sqlserver, h2, db2, sqlite, cockroach (default: postgres)
# tablePrefix: Optional prefix for all tables (default: "jobrunr.")

set -e  # Exit immediately if a command exits with a non-zero status

# Get the script's directory and project root
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(cd "${SCRIPT_DIR}/.." && pwd)"

# Default values
DB_TYPE=${1:-postgres}
TABLE_PREFIX=${2:-"jobrunr."}
JOBRUNR_VERSION=$(grep -oP "val jobrunrVersion = \"\K[^\"]+" "${SCRIPT_DIR}/build.gradle.kts" 2>/dev/null || echo "7.5.1")
SLF4J_VERSION=$(grep -oP "implementation\(\"org.slf4j:slf4j-api:\K[^\"]+" "${SCRIPT_DIR}/build.gradle.kts" 2>/dev/null || echo "2.0.17")

# Output directory for SQL scripts
OUTPUT_DIR="${PROJECT_ROOT}/database/initdb"

echo "Script directory: $SCRIPT_DIR"
echo "Project root: $PROJECT_ROOT"
echo "Looking for JobRunr version: $JOBRUNR_VERSION"
echo "Looking for SLF4J version: $SLF4J_VERSION"
echo "Output directory: $OUTPUT_DIR"

# Ensure output directory exists
if [ ! -d "$OUTPUT_DIR" ]; then
    echo "Creating output directory: $OUTPUT_DIR"
    mkdir -p "$OUTPUT_DIR"
    if [ ! -d "$OUTPUT_DIR" ]; then
        echo "Error: Failed to create output directory: $OUTPUT_DIR"
        exit 1
    fi
else
    echo "Output directory exists: $OUTPUT_DIR"
fi

# Find the JAR files
JOBRUNR_JAR=$(find ~/.gradle -name "jobrunr-${JOBRUNR_VERSION}.jar" | head -1)
if [ -z "$JOBRUNR_JAR" ]; then
    echo "Could not find jobrunr-${JOBRUNR_VERSION}.jar, looking for any version..."
    JOBRUNR_JAR=$(find ~/.gradle -name "jobrunr-*.jar" | head -1)
    if [ -z "$JOBRUNR_JAR" ]; then
        echo "Error: Could not find JobRunr JAR in Gradle cache."
        exit 1
    fi
fi
echo "Found JobRunr JAR: $JOBRUNR_JAR"

SLF4J_JAR=$(find ~/.gradle -name "slf4j-api-${SLF4J_VERSION}.jar" | head -1)
if [ -z "$SLF4J_JAR" ]; then
    echo "Could not find slf4j-api-${SLF4J_VERSION}.jar, looking for any version..."
    SLF4J_JAR=$(find ~/.gradle -name "slf4j-api-*.jar" | head -1)
    if [ -z "$SLF4J_JAR" ]; then
        echo "Error: Could not find SLF4J JAR in Gradle cache."
        exit 1
    fi
fi
echo "Found SLF4J JAR: $SLF4J_JAR"

# Output directory was already checked at the beginning of the script

# Create a temporary directory for SQL generation
TEMP_DIR=$(mktemp -d)
echo "Created temporary directory: $TEMP_DIR"
cd "$TEMP_DIR"

echo ""
echo "Generating SQL scripts for database type: $DB_TYPE"
echo "Using table prefix: $TABLE_PREFIX"
echo "Output directory: $OUTPUT_DIR"

# Set up classpath with proper separator based on OS
if [[ "$OSTYPE" == "cygwin" || "$OSTYPE" == "msys" || "$OSTYPE" == "win32" ]]; then
    # Windows
    CLASSPATH="${JOBRUNR_JAR};${SLF4J_JAR}"
else
    # Unix-like
    CLASSPATH="${JOBRUNR_JAR}:${SLF4J_JAR}"
fi

echo "Using classpath: $CLASSPATH"

# Try to list available classes to help troubleshoot
echo "Listing classes in JobRunr JAR related to database migration:"
if ! jar -tf "$JOBRUNR_JAR" | grep -i "DatabaseSql"; then
    echo "No classes matching 'DatabaseSql' found, trying broader search:"
    jar -tf "$JOBRUNR_JAR" | grep -i "sql" | grep -i "database" || echo "No database-related classes found"
    echo "Checking package structure:"
    jar -tf "$JOBRUNR_JAR" | grep -i "org/jobrunr" | grep -i "storage" | grep -i "sql" | head -10
fi

# The class name might have changed in different versions
# Try known class names/patterns for generating SQL schemas
echo ""
echo "Attempting to generate SQL schema with DatabaseSqlMigrationFileProvider..."
if java -cp "$CLASSPATH" org.jobrunr.storage.sql.common.DatabaseSqlMigrationFileProvider "$DB_TYPE" "$TABLE_PREFIX" 2>/dev/null; then
    echo "SQL generation successful!"
else
    echo "First attempt failed, trying alternative class name..."
    # Try to find the right class by listing relevant classes and attempting each
    POTENTIAL_CLASSES=$(jar -tf "$JOBRUNR_JAR" | grep -i "org/jobrunr" | grep -i "storage" | grep -i "sql" | grep -i "migration" || echo "")
    
    if [ -n "$POTENTIAL_CLASSES" ]; then
        echo "Found potential migration-related classes:"
        echo "$POTENTIAL_CLASSES"
        
        # Try each potential class
        for CLASS_PATH in $POTENTIAL_CLASSES; do
            CLASS_NAME=$(echo "$CLASS_PATH" | tr '/' '.' | sed 's/\.class$//')
            echo "Trying class: $CLASS_NAME"
            if java -cp "$CLASSPATH" "$CLASS_NAME" "$DB_TYPE" "$TABLE_PREFIX" 2>/dev/null; then
                echo "Success with class: $CLASS_NAME"
                break
            fi
        done
    else
        echo "Could not find migration classes. Trying direct SQL dump class..."
        # Last resort - look at the docs for the right command
        if [ -f "$JOBRUNR_JAR" ]; then
            java -cp "$CLASSPATH" org.jobrunr.storage.sql.DatabaseCreator --dump "$DB_TYPE" "$TABLE_PREFIX" 2>/dev/null || \
            java -cp "$CLASSPATH" org.jobrunr.storage.sql.DatabaseCreator "$DB_TYPE" --dump 2>/dev/null || \
            echo "Failed to generate SQL schema with various attempts"
        fi
    fi
fi

# Check if any SQL files were generated
SQL_FILES=$(find . -name "*.sql")
if [ -z "$SQL_FILES" ]; then
    echo "No SQL files were generated. Something went wrong."
    echo "Manual intervention may be required."
    
    # Create a basic template that can be filled in manually
    OUTPUT_FILE="${OUTPUT_DIR}/02-jobrunr-schema.sql"
    echo "Will write template to: ${OUTPUT_FILE}"
    cat > "$OUTPUT_FILE" << EOF
-- JobRunr schema for PostgreSQL
-- Generated as a template by extract_jobrunr_schema.sh
-- Using JobRunr version $JOBRUNR_VERSION
-- NOTE: This is a placeholder. You need to manually add the schema.

-- Create schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS jobrunr;

-- TODO: Add JobRunr tables here
-- The script failed to auto-generate them. 
-- Please refer to JobRunr documentation to create the required tables.
-- You may need to run a JobRunr instance once with table creation enabled
-- and copy the schema from the database.
EOF
    
    echo "Created a template file at $OUTPUT_FILE"
    echo "Please fill it in manually with the required schema."
    
    cd - > /dev/null
    rm -rf "$TEMP_DIR"
    exit 1
fi

# Create the output SQL file with proper numbering
OUTPUT_FILE="${OUTPUT_DIR}/02-jobrunr-schema.sql"
echo "Will write output to: ${OUTPUT_FILE}"

# Add header to the SQL file
cat > "$OUTPUT_FILE" << EOF
-- JobRunr schema for PostgreSQL
-- Generated automatically by extract_jobrunr_schema.sh
-- Using JobRunr version $JOBRUNR_VERSION

-- Create schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS jobrunr;

EOF

# Find and append all SQL files in order
for f in $(find . -name "*.sql" | sort -V); do
    echo "-- Including migration file: $(basename "$f")" >> "$OUTPUT_FILE"
    cat "$f" >> "$OUTPUT_FILE"
    echo "" >> "$OUTPUT_FILE"
    echo "" >> "$OUTPUT_FILE"
done

echo ""
echo "SQL script consolidated to: $OUTPUT_FILE"

# Provide usage information for JobRunr configuration
echo ""
echo "===== JobRunr Configuration Information ====="
echo "When using these SQL scripts, configure JobRunr to skip table creation:"
echo ""
echo "JobRunr.configure()"
echo "    .useStorageProvider(new PostgresStorageProvider(dataSource, DatabaseOptions.SKIP_CREATE))"
echo "    .useBackgroundJobServer()"
echo "    .initialize();"

# Clean up temporary directory
cd - > /dev/null
rm -rf "$TEMP_DIR"

echo ""
echo "Temporary files cleaned up. Process complete."