#!/bin/bash
################################################################################
# Ferrero Content Scaling Automation - Deployment Script
#
# Description: Automated server deployment script that sets up the complete
#              Ferrero DAM automation system including PostgreSQL database,
#              Python environment, and all dependencies.
#
# Usage:
#   1. Clone repo to /opt/ferrero-opentext/
#   2. Ensure Box-config.json is at /opt/ferrero-opentext/Box-config.json
#   3. cd /opt/ferrero-opentext/Python-Version
#   4. ./deploy.sh
#
# Requirements:
#   - Docker and docker-compose installed
#   - Python 3.6+ available
#   - Box-config.json in parent directory
#   - .env file with credentials configured
#
# Version: 1.0
# Last Updated: November 5, 2025
################################################################################

set -e  # Exit on any error
set -u  # Exit on undefined variable

# Color codes for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

# Logging functions
log_info() {
    echo -e "${CYAN}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $1"
}

log_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

log_step() {
    echo -e "\n${BLUE}===================================================${NC}"
    echo -e "${BLUE}$1${NC}"
    echo -e "${BLUE}===================================================${NC}\n"
}

# Error handler
error_exit() {
    log_error "$1"
    log_error "Deployment failed! Please check the error above and try again."
    exit 1
}

################################################################################
# STEP 0: Prerequisites Check
################################################################################

log_step "Step 0: Checking Prerequisites"

# Check if running from correct directory
CURRENT_DIR=$(basename "$PWD")
if [ "$CURRENT_DIR" != "Python-Version" ]; then
    error_exit "This script must be run from the Python-Version directory"
fi

PARENT_DIR=$(dirname "$PWD")
log_info "Deployment directory: $PWD"
log_info "Parent directory: $PARENT_DIR"

# Verify we're in /opt/ferrero-opentext/Python-Version
EXPECTED_PATH="/opt/ferrero-opentext/Python-Version"
if [ "$PWD" != "$EXPECTED_PATH" ]; then
    log_warning "Not running from expected path: $EXPECTED_PATH"
    log_warning "Current path: $PWD"
    read -p "Continue anyway? (y/n) " -n 1 -r
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        error_exit "Deployment cancelled by user"
    fi
fi

# Check Docker is installed
if ! command -v docker &> /dev/null; then
    error_exit "Docker is not installed. Please install Docker first."
fi
log_success "Docker is installed: $(docker --version)"

# Check Docker is running
if ! docker info &> /dev/null; then
    error_exit "Docker is not running. Please start Docker first."
fi
log_success "Docker is running"

# Check docker-compose is available
if ! command -v docker-compose &> /dev/null && ! docker compose version &> /dev/null 2>&1; then
    error_exit "docker-compose is not installed. Please install docker-compose first."
fi

# Determine which docker compose command to use
if command -v docker-compose &> /dev/null; then
    DOCKER_COMPOSE="docker-compose"
    log_success "docker-compose is installed: $(docker-compose --version)"
else
    DOCKER_COMPOSE="docker compose"
    log_success "docker compose is installed: $(docker compose version)"
fi

# Check Python 3.6+ is available
PYTHON_CMD=""
if command -v python3 &> /dev/null; then
    PYTHON_VERSION=$(python3 --version 2>&1 | cut -d' ' -f2)
    PYTHON_MAJOR=$(echo "$PYTHON_VERSION" | cut -d'.' -f1)
    PYTHON_MINOR=$(echo "$PYTHON_VERSION" | cut -d'.' -f2)

    if [ "$PYTHON_MAJOR" -ge 3 ] && [ "$PYTHON_MINOR" -ge 6 ]; then
        PYTHON_CMD="python3"
        log_success "Python is installed: $PYTHON_VERSION"
    else
        error_exit "Python 3.6+ is required. Found: $PYTHON_VERSION"
    fi
else
    error_exit "Python 3 is not installed. Please install Python 3.6 or higher."
fi

# Check Box-config.json exists in parent directory
BOX_CONFIG_PATH="$PARENT_DIR/Box-config.json"
if [ ! -f "$BOX_CONFIG_PATH" ]; then
    error_exit "Box-config.json not found at: $BOX_CONFIG_PATH"
fi
log_success "Box-config.json found at: $BOX_CONFIG_PATH"

# Check .env file exists
if [ ! -f .env ]; then
    error_exit ".env file not found. Please create .env with your credentials before deploying."
fi
log_success ".env file exists"

# Check database/init.sql exists
if [ ! -f database/init.sql ]; then
    error_exit "database/init.sql not found. Database schema is required for deployment."
fi
log_success "database/init.sql found"

# Check docker-compose.yml exists
if [ ! -f docker-compose.yml ]; then
    error_exit "docker-compose.yml not found. Please ensure it's in the Python-Version directory."
fi
log_success "docker-compose.yml found"

################################################################################
# STEP 1: Docker Database Setup
################################################################################

log_step "Step 1: Setting up PostgreSQL Database with Docker"

# Check if container already exists
if docker ps -a --format '{{.Names}}' | grep -q "^ferrero-tracking-db$"; then
    log_warning "Database container 'ferrero-tracking-db' already exists"
    read -p "Remove existing container and recreate? (y/n) " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        log_info "Stopping and removing existing container..."
        $DOCKER_COMPOSE down -v
        log_success "Existing container removed"
    else
        log_info "Keeping existing container"
    fi
fi

# Start database container
log_info "Starting PostgreSQL container..."
$DOCKER_COMPOSE up -d

# Wait for database to be healthy
log_info "Waiting for database to be ready (this may take 10-30 seconds)..."
MAX_WAIT=60
WAIT_COUNT=0
while [ $WAIT_COUNT -lt $MAX_WAIT ]; do
    if docker exec ferrero-tracking-db pg_isready -U ferrero_user -d ferrero_tracking &> /dev/null; then
        log_success "Database is ready!"
        break
    fi
    sleep 2
    WAIT_COUNT=$((WAIT_COUNT + 2))
    echo -n "."
done
echo ""

if [ $WAIT_COUNT -ge $MAX_WAIT ]; then
    error_exit "Database failed to become ready within $MAX_WAIT seconds"
fi

# Verify database is accessible
log_info "Verifying database connection..."
if docker exec ferrero-tracking-db psql -U ferrero_user -d ferrero_tracking -c "SELECT 1;" &> /dev/null; then
    log_success "Database connection verified"
else
    error_exit "Failed to connect to database"
fi

################################################################################
# STEP 2: Database Schema Initialization
################################################################################

log_step "Step 2: Initializing Database Schema"

# Check if tables already exist
log_info "Checking for existing database schema..."
TABLES_EXIST=$(docker exec ferrero-tracking-db psql -U ferrero_user -d ferrero_tracking -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'master_assets';" | tr -d ' ')

if [ "$TABLES_EXIST" = "1" ]; then
    log_warning "Database tables already exist (master_assets table found)"
    log_info "Skipping schema initialization (init.sql runs automatically on first container start)"
else
    log_info "Database schema initialized (init.sql ran automatically during container creation)"
fi

# Verify all expected tables exist
log_info "Verifying database schema..."
EXPECTED_TABLES=("master_assets" "derivative_assets" "asset_events" "workflow_state")
for table in "${EXPECTED_TABLES[@]}"; do
    TABLE_EXISTS=$(docker exec ferrero-tracking-db psql -U ferrero_user -d ferrero_tracking -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name = '$table';" | tr -d ' ')
    if [ "$TABLE_EXISTS" = "1" ]; then
        log_success "Table '$table' exists"
    else
        error_exit "Table '$table' not found. Schema initialization may have failed."
    fi
done

# Show table counts
log_info "Database statistics:"
for table in "${EXPECTED_TABLES[@]}"; do
    COUNT=$(docker exec ferrero-tracking-db psql -U ferrero_user -d ferrero_tracking -t -c "SELECT COUNT(*) FROM $table;" | tr -d ' ')
    log_info "  - $table: $COUNT rows"
done

################################################################################
# STEP 3: Python Environment Setup
################################################################################

log_step "Step 3: Setting up Python Environment"

# Remove existing venv if present
if [ -d venv ]; then
    log_warning "Existing virtual environment found"
    log_info "Removing old virtual environment..."
    rm -rf venv
fi

# Create virtual environment
log_info "Creating virtual environment..."
$PYTHON_CMD -m venv venv
log_success "Virtual environment created"

# Activate virtual environment
log_info "Activating virtual environment..."
source venv/bin/activate

# Upgrade pip to appropriate version (pip 21+ requires Python 3.6+, but 20.3.4 is safer for 3.6)
log_info "Upgrading pip..."
if [ "$PYTHON_MINOR" -eq 6 ]; then
    pip install --upgrade "pip<21.0" --quiet
    log_success "Pip upgraded (Python 3.6 compatible version)"
else
    pip install --upgrade pip --quiet
    log_success "Pip upgraded to latest version"
fi

# Install dependencies
log_info "Installing Python dependencies from requirements.txt..."
log_info "This may take a few minutes..."
pip install -r requirements.txt --quiet

# Verify key packages installed
log_info "Verifying key packages..."
REQUIRED_PACKAGES=("requests" "psycopg2" "boxsdk" "PyYAML" "cryptography" "flask")
for package in "${REQUIRED_PACKAGES[@]}"; do
    if pip show "$package" &> /dev/null; then
        VERSION=$(pip show "$package" | grep "Version:" | cut -d' ' -f2)
        log_success "  - $package ($VERSION)"
    else
        log_warning "  - $package not found"
    fi
done

################################################################################
# STEP 4: Directory Structure
################################################################################

log_step "Step 4: Creating Directory Structure"

# Create required directories
log_info "Creating logs directory..."
mkdir -p logs/backup
chmod 755 logs

log_info "Creating temp directory..."
mkdir -p temp/downloads
chmod 755 temp

log_info "Creating config directory (if needed)..."
mkdir -p config/environments
mkdir -p config/certificates

log_success "Directory structure created"

################################################################################
# STEP 5: File Permissions
################################################################################

log_step "Step 5: Setting File Permissions"

# Set permissions on .env
log_info "Securing .env file..."
chmod 600 .env
log_success ".env permissions set to 600"

# Set permissions on Box-config.json
log_info "Securing Box-config.json..."
chmod 600 "$BOX_CONFIG_PATH"
log_success "Box-config.json permissions set to 600"

# Set permissions on certificates if they exist
if [ -d config/certificates ] && [ "$(ls -A config/certificates/*.pfx 2>/dev/null)" ]; then
    log_info "Securing certificate files..."
    chmod 600 config/certificates/*.pfx
    log_success "Certificate files secured"
else
    log_info "No certificate files found (optional - only needed for mTLS)"
fi

# Make scripts executable
log_info "Making scripts executable..."
chmod +x scripts/*.py 2>/dev/null || true
log_success "Scripts are executable"

################################################################################
# STEP 6: Connection Testing
################################################################################

log_step "Step 6: Testing Connections"

log_info "Running connection tests..."
log_info "This will test DAM OAuth2, Box SDK, and PostgreSQL connections..."
echo ""

# Run test_connection.py
if $PYTHON_CMD scripts/test_connection.py; then
    echo ""
    log_success "All connection tests passed!"
else
    echo ""
    log_error "Connection tests failed!"
    log_error "Please check your .env configuration and ensure all credentials are correct."
    log_warning "You can manually test connections later with: python scripts/test_connection.py"
    read -p "Continue deployment anyway? (y/n) " -n 1 -r
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        error_exit "Deployment cancelled due to connection test failures"
    fi
fi

################################################################################
# STEP 7: Deployment Summary
################################################################################

log_step "Deployment Complete!"

echo -e "${GREEN}╔════════════════════════════════════════════════════════════════╗${NC}"
echo -e "${GREEN}║                                                                ║${NC}"
echo -e "${GREEN}║         ✅  FERRERO AUTOMATION DEPLOYMENT SUCCESSFUL!  ✅      ║${NC}"
echo -e "${GREEN}║                                                                ║${NC}"
echo -e "${GREEN}╚════════════════════════════════════════════════════════════════╝${NC}"
echo ""

log_info "Deployment Summary:"
echo ""
echo -e "  ${GREEN}✓${NC} PostgreSQL database running (port 5437)"
echo -e "  ${GREEN}✓${NC} Database schema initialized (4 tables)"
echo -e "  ${GREEN}✓${NC} Python environment configured"
echo -e "  ${GREEN}✓${NC} Dependencies installed"
echo -e "  ${GREEN}✓${NC} File permissions set"
echo -e "  ${GREEN}✓${NC} Connection tests passed"
echo ""

log_step "Next Steps"

echo -e "${YELLOW}1. Configure Cron Jobs${NC}"
echo ""
echo "   Copy and paste these commands to set up automated workflows:"
echo ""
echo -e "${CYAN}   crontab -e${NC}"
echo ""
echo "   Then add these lines:"
echo ""
echo "   # Ferrero Automation - Run every 5 minutes"
echo "   */5 * * * * cd /opt/ferrero-opentext/Python-Version && venv/bin/python scripts/a1_to_a2_download.py >> logs/cron_a1_a2.log 2>&1"
echo "   */5 * * * * cd /opt/ferrero-opentext/Python-Version && venv/bin/python scripts/a5_to_a6_download.py >> logs/cron_a5_a6.log 2>&1"
echo "   */5 * * * * cd /opt/ferrero-opentext/Python-Version && venv/bin/python scripts/b1_to_b2_download.py >> logs/cron_b1_b2.log 2>&1"
echo "   */5 * * * * cd /opt/ferrero-opentext/Python-Version && venv/bin/python scripts/a2_to_a3_upload_polling.py >> logs/cron_a2_a3.log 2>&1"
echo "   0 19 * * * cd /opt/ferrero-opentext/Python-Version && venv/bin/python scripts/daily_report.py >> logs/daily_report.log 2>&1"
echo ""

echo -e "${YELLOW}2. Test Workflows Manually${NC}"
echo ""
echo "   Activate the virtual environment and test each workflow:"
echo ""
echo -e "   ${CYAN}source venv/bin/activate${NC}"
echo -e "   ${CYAN}python scripts/a1_to_a2_download.py${NC}"
echo -e "   ${CYAN}python scripts/a5_to_a6_download.py${NC}"
echo -e "   ${CYAN}python scripts/b1_to_b2_download.py${NC}"
echo -e "   ${CYAN}python scripts/a2_to_a3_upload_polling.py${NC}"
echo -e "   ${CYAN}python scripts/daily_report.py${NC}"
echo ""

echo -e "${YELLOW}3. Monitor Logs${NC}"
echo ""
echo "   Watch workflow execution in real-time:"
echo ""
echo -e "   ${CYAN}tail -f logs/a1_to_a2.log${NC}"
echo -e "   ${CYAN}tail -f logs/a5_to_a6.log${NC}"
echo -e "   ${CYAN}tail -f logs/cron_a1_a2.log${NC}"
echo ""

echo -e "${YELLOW}4. Database Management${NC}"
echo ""
echo "   Access the database:"
echo ""
echo -e "   ${CYAN}docker exec -it ferrero-tracking-db psql -U ferrero_user -d ferrero_tracking${NC}"
echo ""
echo "   Or from outside container:"
echo ""
echo -e "   ${CYAN}PGPASSWORD=ferrero_pass_2025 psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking${NC}"
echo ""

echo -e "${YELLOW}5. Docker Management${NC}"
echo ""
echo "   View database logs:"
echo -e "   ${CYAN}docker logs ferrero-tracking-db${NC}"
echo ""
echo "   Stop database:"
echo -e "   ${CYAN}$DOCKER_COMPOSE stop${NC}"
echo ""
echo "   Start database:"
echo -e "   ${CYAN}$DOCKER_COMPOSE start${NC}"
echo ""
echo "   Restart database:"
echo -e "   ${CYAN}$DOCKER_COMPOSE restart${NC}"
echo ""

log_step "Important Security Notes"

echo -e "${RED}⚠️  BEFORE PRODUCTION:${NC}"
echo ""
echo "  1. Change the database password from 'ferrero_pass_2025'"
echo "  2. Update the password in .env (DB_PASSWORD)"
echo "  3. Update docker-compose.yml with new password"
echo "  4. Recreate the container: $DOCKER_COMPOSE down && $DOCKER_COMPOSE up -d"
echo "  5. Verify all credentials in .env are production values"
echo "  6. Set up database backups"
echo "  7. Configure log rotation/monitoring"
echo ""

log_step "Useful Commands"

echo "  Test connections:"
echo -e "    ${CYAN}source venv/bin/activate && python scripts/test_connection.py${NC}"
echo ""
echo "  Check database status:"
echo -e "    ${CYAN}docker ps | grep ferrero${NC}"
echo ""
echo "  View recent assets:"
echo -e "    ${CYAN}docker exec ferrero-tracking-db psql -U ferrero_user -d ferrero_tracking -c \"SELECT tracking_id, original_filename, created_at FROM master_assets ORDER BY created_at DESC LIMIT 10;\"${NC}"
echo ""
echo "  Check workflow logs for errors:"
echo -e "    ${CYAN}grep -i error logs/*.log${NC}"
echo ""

echo ""
echo -e "${GREEN}════════════════════════════════════════════════════════════════${NC}"
echo -e "${GREEN}Deployment completed successfully! 🚀${NC}"
echo -e "${GREEN}════════════════════════════════════════════════════════════════${NC}"
echo ""