#!/bin/bash

set -x  # Enable command tracing for debugging

echo "🚀 Starting CSV export process..."

# Input Params
ENVIRONMENT=${1:-development}
EXPORT_PATH=$2
CHILD_PROCESS_SLUG=$3

# Input Validation
if [ -z "$ENVIRONMENT" ] || [ -z "$EXPORT_PATH" ] || [ -z "$CHILD_PROCESS_SLUG" ]; then
  echo "❌ Missing required arguments. Usage:"
  echo "   bash export_excel.sh <env> <file_path> <child_process_slug>"
  exit 1
fi

# Environment File
ENV_FILE=".env.${ENVIRONMENT}"
echo "📂 Environment file: $ENV_FILE"
if [ ! -f "$ENV_FILE" ]; then
  echo "❌ Environment file '$ENV_FILE' not found!"
  exit 1
fi

echo "✅ Using environment file: $ENV_FILE"

# Normalize line endings
sed -i 's/\r$//' "$ENV_FILE"

# Load environment vars safely (handle 'key = value' or 'key= 'value' ')
TEMP_ENV_FILE="/tmp/tmp_env_$RANDOM.env"
grep -v '^#' "$ENV_FILE" | sed -E "s/^[[:space:]]*([A-Za-z0-9_]+)[[:space:]]*=[[:space:]]*'?([^']*)'?[[:space:]]*$/\1=\"\2\"/" > "$TEMP_ENV_FILE"

set -a
source "$TEMP_ENV_FILE"
set +a
rm -f "$TEMP_ENV_FILE"

# Validate DB environment
echo "✅ Loaded Environment Variables: DATABASE_HOST=$DATABASE_HOST, DATABASE_USER=$DATABASE_USER, DATABASE_NAME=$DATABASE_NAME"
if [ -z "$DATABASE_HOST" ] || [ -z "$DATABASE_PORT" ] || [ -z "$DATABASE_USER" ] || [ -z "$DATABASE_PASSWORD" ] || [ -z "$DATABASE_NAME" ]; then
  echo "❌ One or more database environment variables are missing."
  exit 1
fi

# Compose full DATABASE_URL
DATABASE_URL="postgresql://$DATABASE_USER:$DATABASE_PASSWORD@$DATABASE_HOST:$DATABASE_PORT/$DATABASE_NAME"
echo "📌 DATABASE_URL: $DATABASE_URL"

# Create export folder
mkdir -p "$EXPORT_PATH"

# Verify export path permissions
if [ ! -w "$EXPORT_PATH" ]; then
  echo "❌ Export path '$EXPORT_PATH' is not writable."
  exit 1
fi

# Unique file name
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
UUID=$(date +%s%N | md5sum | head -c 12)
FILENAME="${UUID}_${TIMESTAMP}.csv"
FULL_PATH="$EXPORT_PATH/$FILENAME"

# Export data using COPY
echo "📡 Exporting data using \COPY..."

PGPASSWORD=$DATABASE_PASSWORD psql -U "$DATABASE_USER" -d "$DATABASE_NAME" -h "$DATABASE_HOST" -p "$DATABASE_PORT" -v ON_ERROR_STOP=1 -c "\COPY (SELECT code,name,email,user_roles FROM users_export) TO '$FULL_PATH' CSV HEADER" 2>&1 | tee psql_output.log

if [ $? -ne 0 ] || [ ! -f "$FULL_PATH" ]; then
  echo "❌ Failed to export data using \COPY."
  exit 1
fi

echo "✅ CSV file saved to: $FULL_PATH"

# Update in database
echo "📝 Updating child_processes.downloadables in DB..."

PGPASSWORD=$DATABASE_PASSWORD psql -h "$DATABASE_HOST" -U "$DATABASE_USER" -d "$DATABASE_NAME" -p "$DATABASE_PORT" <<EOF
UPDATE child_processes
SET downloadables = '$FULL_PATH'
WHERE slug = '$CHILD_PROCESS_SLUG';

INSERT INTO child_process_logs (
    child_process_id,
    downloadables,
    process_status,
    output,
    initiated_by,
    created_at
)
SELECT
    id,
    '$FULL_PATH',
    'success',  
    'success',  
    1,          
    NOW()       
FROM
    child_processes
WHERE
    slug = '$CHILD_PROCESS_SLUG';
EOF

echo "✅ CSV file saved to: $FULL_PATH"
echo "<link>$FULL_PATH<link>"

if [ $? -eq 0 ]; then
  echo "✅ child_processes.downloadables updated for slug: $CHILD_PROCESS_SLUG"
else
  echo "❌ Failed to update child_processes table."
fi