pipeline {
    agent any

    environment {
        DOCKER_IMAGE = 'postgres:18-alpine'
        COMPOSE_FILE = 'docker-compose.yml'
        BACKUP_RETENTION = '7'
        // Note: PostgreSQL credentials are handled via withCredentials in deployment stage
    }

    options {
        buildDiscarder(logRotator(numToKeepStr: '10'))
        timeout(time: 20, unit: 'MINUTES')
        timestamps()
    }

    triggers {
        // Poll SCM every hour for changes
        pollSCM('H * * * *')
        // Deploy every night at 2 AM
        cron('H 2 * * *')
    }

    stages {

        stage('Checkout') {
            steps {
                script {
                    // Clean workspace
                    cleanWs()

                    // Checkout code
                    checkout scm

                    echo "✅ Code checked out successfully"
                    echo "📂 Working directory: ${pwd()}"
                    echo "🌿 Git branch: ${env.BRANCH_NAME}"
                    echo "🏷️  Git commit: ${env.GIT_COMMIT}"
                }
            }
        }

        stage('Validate Configuration') {
            steps {
                script {
                    echo "🔍 Validating configuration files..."

                    // Check if required files exist
                    def requiredFiles = [
                        'docker-compose.yml',
                        'postgresql/conf/postgresql.conf',
                        'postgresql/conf/pg_hba.conf',
                        'postgresql/init/01-init-database.sh',
                        '.env.example'
                    ]

                    requiredFiles.each { file ->
                        if (!fileExists(file)) {
                            error "❌ Required file missing: ${file}"
                        } else {
                            echo "✅ Found: ${file}"
                        }
                    }

                    // Validate docker-compose syntax
                    sh """
                        echo "🐳 Validating Docker Compose files..."
                        docker-compose -f ${env.COMPOSE_FILE} config --quiet
                    """

                    // Validate PostgreSQL configuration
                    sh """
                        echo "🗄️  Validating PostgreSQL configuration..."
                        # Basic syntax check for postgresql.conf
                        grep -E '^(shared_buffers|effective_cache_size|work_mem)' postgresql/conf/postgresql.conf
                    """

                    // Check Jenkins credentials configuration
                    sh """
                        echo "🔐 Checking Jenkins credentials configuration..."
                        echo "ℹ️  Note: Make sure you have configured these Jenkins credentials:"
                        echo "   - postgres-password (Secret Text): Your PostgreSQL password"
                        echo "   - postgres-db (String, optional): Database name (default: postgres)"
                        echo "   - postgres-user (String, optional): Database user (default: postgres)"
                        echo "📝 Configure in: Jenkins → Manage Jenkins → Manage Credentials → (global)"
                    """
                }
            }
        }

        stage('System Health Check') {
            steps {
                script {
                    echo "🔍 Checking system resources..."

                    // Check Docker availability
                    sh """
                        if ! docker info >/dev/null 2>&1; then
                            echo "❌ Docker is not running"
                            exit 1
                        fi
                        echo "✅ Docker is running"

                        # Check system resources
                        echo "💾 Memory information:"
                        free -h

                        echo "💿 Disk space:"
                        df -h .

                        echo "🖥️  CPU information:"
                        nproc
                    """
                }
            }
        }

        stage('Deploy PostgreSQL') {
            when {
                anyOf {
                    branch 'main'
                    branch 'master'
                    buildingTag()
                    changeset "**/*.yml"
                    changeset "**/*.yaml"
                    changeset "**/conf/**"
                    changeset "**/init/**"
                    expression {
                        return env.BRANCH_NAME == null || env.BRANCH_NAME == 'main' || env.BRANCH_NAME == 'master'
                    }
                }
            }
            steps {
                script {
                    echo "🚀 Deploying PostgreSQL..."

                    try {
                        // Set script permissions
                        sh 'chmod +x postgresql/scripts/*.sh'

                        // Deploy with Jenkins credentials
                        withCredentials([
                            string(credentialsId: 'postgres-password', variable: 'POSTGRES_PASSWORD'),
                            string(credentialsId: 'postgres-db', variable: 'POSTGRES_DB'),
                            string(credentialsId: 'postgres-user', variable: 'POSTGRES_USER')
                        ]) {
                            sh '''
                                echo "🔧 Using Jenkins credentials for deployment..."
                                export POSTGRES_DB="${POSTGRES_DB:-postgres}"
                                export POSTGRES_USER="${POSTGRES_USER:-postgres}"
                                export POSTGRES_PASSWORD="${POSTGRES_PASSWORD}"
                                export PROJECT_NAME="${PROJECT_NAME:-Generic PostgreSQL}"
                                export PROJECT_SCHEMA="${PROJECT_SCHEMA:-none}"
                                export JENKINS_WORKSPACE="/home/geeplo/services/jenkins_home/workspace/postgres-deploy/postgresql"

                                echo "📋 Deployment configuration:"
                                echo "   Database: ${POSTGRES_DB}"
                                echo "   User: ${POSTGRES_USER}"
                                echo "   Project: ${PROJECT_NAME}"
                                echo "   Schema: ${PROJECT_SCHEMA}"
                                echo "   Workspace: ${JENKINS_WORKSPACE}"
                                echo "   Password: [REDACTED]"

                                docker-compose -f docker-compose.yml down

                                # Create temporary docker-compose file with substituted variables
                                sed 's/\\${POSTGRES_DB}/'"${POSTGRES_DB}"'/g' docker-compose.yml > docker-compose.tmp.yml
                                sed -i 's/\\${POSTGRES_USER}/'"${POSTGRES_USER}"'/g' docker-compose.tmp.yml
                                sed -i 's/\\${POSTGRES_PASSWORD}/'"${POSTGRES_PASSWORD}"'/g' docker-compose.tmp.yml
                                sed -i 's/\\${PROJECT_NAME}/'"${PROJECT_NAME}"'/g' docker-compose.tmp.yml
                                sed -i 's/\\${PROJECT_SCHEMA}/'"${PROJECT_SCHEMA}"'/g' docker-compose.tmp.yml
                                sed -i 's|\\${JENKINS_WORKSPACE:-./postgresql}|'"${JENKINS_WORKSPACE}"'|g' docker-compose.tmp.yml

                                # Start postgres service only (backup depends on it being healthy)
                                docker-compose -f docker-compose.tmp.yml up -d postgres

                                echo "⏳ Waiting for PostgreSQL to be ready..."
                                # Wait for postgres to be healthy
                                for i in {1..60}; do
                                    if docker exec postgres-postgres pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB} > /dev/null 2>&1; then
                                        echo "✅ PostgreSQL is ready!"
                                        break
                                    fi
                                    echo "⏳ Waiting for PostgreSQL (attempt $i/60)..."
                                    sleep 2
                                done

                                # Verify postgres is actually ready
                                if ! docker exec postgres-postgres pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}; then
                                    echo "❌ PostgreSQL failed to start within timeout"
                                    exit 1
                                fi

                                # Now start backup service (depends on healthy postgres)
                                docker-compose -f docker-compose.tmp.yml up -d postgres-backup

                                rm docker-compose.tmp.yml
                            '''
                        }

                        echo "✅ PostgreSQL deployed successfully"

                    } catch (Exception e) {
                        echo "❌ Deployment failed: ${e.getMessage()}"

                        // Show logs for debugging
                        sh 'docker logs postgres-postgres'
                        throw e
                    }
                }
            }
        }

        stage('Verify Deployment') {
            steps {
                script {
                    echo "🔍 Verifying deployment..."

                    // Use same credentials for verification
                    withCredentials([
                        string(credentialsId: 'postgres-password', variable: 'POSTGRES_PASSWORD'),
                        string(credentialsId: 'postgres-db', variable: 'POSTGRES_DB'),
                        string(credentialsId: 'postgres-user', variable: 'POSTGRES_USER')
                    ]) {
                        sh '''
                            export POSTGRES_DB="${POSTGRES_DB:-postgres}"
                            export POSTGRES_USER="${POSTGRES_USER:-postgres}"
                            export PGPASSWORD="${POSTGRES_PASSWORD}"

                            echo "🐳 Container status:"
                            docker ps | grep postgres || echo "❌ No postgres containers running"

                            echo "🔗 Testing database connection..."
                            docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" postgres-postgres psql -h localhost -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "SELECT 1 as connection_test;"

                            echo "📊 Database statistics:"
                            docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" postgres-postgres psql -h localhost -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "
                                SELECT
                                    'total schemas' as check,
                                    COUNT(*)::text as result
                                FROM information_schema.schemata
                                WHERE schema_name NOT IN ('information_schema', 'pg_catalog');
                            "

                            echo "💾 Testing backup system..."
                            docker exec postgres-backup /scripts/backup_check.sh || echo "⚠️  Backup check not available (container might be starting)"
                        '''
                    }

                    echo "✅ Deployment verification completed"
                }
            }
        }

        stage('Performance Test') {
            steps {
                script {
                    echo "⚡ Running performance tests..."

                    withCredentials([
                        string(credentialsId: 'postgres-password', variable: 'POSTGRES_PASSWORD'),
                        string(credentialsId: 'postgres-db', variable: 'POSTGRES_DB'),
                        string(credentialsId: 'postgres-user', variable: 'POSTGRES_USER')
                    ]) {
                        sh '''
                            export POSTGRES_DB="${POSTGRES_DB:-postgres}"
                            export POSTGRES_USER="${POSTGRES_USER:-postgres}"
                            export PGPASSWORD="${POSTGRES_PASSWORD}"

                            echo "📈 Running basic performance queries..."
                            docker exec -e PGPASSWORD="${POSTGRES_PASSWORD}" postgres-postgres psql -h localhost -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "
                                -- Test basic query performance
                                EXPLAIN ANALYZE SELECT 1 as test_query;

                                -- Test system catalog performance
                                EXPLAIN ANALYZE SELECT COUNT(*) FROM information_schema.tables;

                                -- Test connection info
                                SELECT database_name, process_count FROM (
                                    SELECT datname as database_name, COUNT(*) as process_count
                                    FROM pg_stat_activity
                                    GROUP BY datname
                                ) as activity_stats;
                            " || echo "⚠️  Performance test queries failed (might be empty database)"
                        '''
                    }

                    echo "✅ Performance tests completed"
                }
            }
        }

        stage('Create Backup') {
            steps {
                script {
                    def timestamp = new Date().format('yyyyMMdd_HHmmss')
                    def backupFile = "jenkins_backup_${timestamp}.sql"

                    echo "💾 Creating deployment backup: ${backupFile}"

                    withCredentials([
                        string(credentialsId: 'postgres-password', variable: 'POSTGRES_PASSWORD'),
                        string(credentialsId: 'postgres-db', variable: 'POSTGRES_DB'),
                        string(credentialsId: 'postgres-user', variable: 'POSTGRES_USER')
                    ]) {
                        sh """
                            export POSTGRES_DB="\${POSTGRES_DB:-postgres}"
                            export POSTGRES_USER="\${POSTGRES_USER:-postgres}"
                            export PGPASSWORD="\${POSTGRES_PASSWORD}"

                            # Create backup directory
                            mkdir -p jenkins_backups

                            # Create backup
                            docker exec -e PGPASSWORD="\${POSTGRES_PASSWORD}" postgres-postgres pg_dump \
                                -h localhost \
                                -U \${POSTGRES_USER} \
                                -d \${POSTGRES_DB} \
                                --format=custom \
                                --compress=9 \
                                --file="/tmp/${backupFile}"

                            # Copy backup from container
                            docker cp postgres-postgres:/tmp/${backupFile} jenkins_backups/

                            # Create checksum
                            cd jenkins_backups
                            sha256sum ${backupFile} > ${backupFile}.sha256

                            echo "✅ Backup created: ${backupFile}"
                            ls -lh ${backupFile}
                        """
                    }

                    // Archive backup artifacts
                    archiveArtifacts artifacts: 'jenkins_backups/*.sql,jenkins_backups/*.sha256',
                                 fingerprint: true,
                                 allowEmptyArchive: true
                }
            }
        }
    }

    post {
        always {
            script {
                echo "📋 Pipeline completed with status: ${currentBuild.currentResult}"

                // Generate pipeline summary
                def summary = """
                    🎯 PostgreSQL Deployment Pipeline
                    📅 Build: ${env.BUILD_NUMBER}
                    🌿 Branch: ${env.BRANCH_NAME}
                    🏷️  Commit: ${env.GIT_COMMIT?.take(8)}
                    ⏱️  Duration: ${currentBuild.durationString}
                    ✅ Status: ${currentBuild.currentResult}
                """

                echo summary

                // Clean up old artifacts (keep last 5)
                sh 'find jenkins_backups -name "*.sql" -type f | sort -r | tail -n +6 | xargs rm -f || true'
            }
        }

        success {
            script {
                echo "🎉 PostgreSQL deployment successful!"

                // Send success notification (customize as needed)
                // emailext (
                //     subject: "✅ DataShield PostgreSQL deployed successfully",
                //     body: "PostgreSQL has been deployed successfully on ${env.NODE_NAME}.\\n\\nBuild: ${env.BUILD_URL}",
                //     to: "${env.CHANGE_AUTHOR_EMAIL}"
                // )
            }
        }

        failure {
            script {
                echo "❌ PostgreSQL deployment failed!"

                // Show container logs for debugging
                sh '''
                    echo "🐳 Container logs:"
                    docker logs postgres-postgres 2>&1 | tail -n 50 || echo "Could not get container logs"

                    echo "📋 System status:"
                    docker ps -a | grep postgres || echo "No postgres containers found"
                '''

                // Send failure notification (customize as needed)
                // emailext (
                //     subject: "❌ DataShield PostgreSQL deployment failed",
                //     body: "PostgreSQL deployment failed on ${env.NODE_NAME}.\\n\\nBuild: ${env.BUILD_URL}\\n\\nPlease check the logs for details.",
                //     to: "${env.CHANGE_AUTHOR_EMAIL}"
                // )
            }
        }

        unstable {
            script {
                echo "⚠️  PostgreSQL deployment completed with warnings!"
            }
        }

        cleanup {
            script {
                echo "🧹 Cleaning up workspace..."

                // Remove temporary files
                sh 'rm -f *.tmp || true'

                // Only tear down containers for non-main branches (explicit check)
                // Keep containers running on main/master for production and automated backups
                if (env.BRANCH_NAME && env.BRANCH_NAME != 'main' && env.BRANCH_NAME != 'master') {
                    echo "🗑️  Tearing down development containers for branch: ${env.BRANCH_NAME}"
                    sh 'docker-compose down || true'
                } else {
                    echo "✅ Keeping containers running for automated backups (main branch or production)"
                }
            }
        }
    }
}