// Helper function for database operations
def executeDbQuery(String query, String description = null) {
    echo "SQL query: ${query} ### ${description}"
    sh """mysql -u ${DEPLOY_DB_USER} --password=${DEPLOY_DB_PWD} -D ${DEPLOY_DB_NAME} -h ${DEPLOY_DB_HOST} -e "${query}" """
}

// Helper function for database operations
def executeDbMultiQuery(String queries, String description = null) {
    echo "SQL query: ${queries} ### ${description}"
    sh """mysql -u ${DEPLOY_DB_USER} --password=${DEPLOY_DB_PWD} -D ${DEPLOY_DB_NAME} -h ${DEPLOY_DB_HOST} << 'EOF'
${queries}
EOF"""
}

pipeline {
    agent any

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

    environment {
        // Node.js configuration
        PATH = "/var/lib/jenkins/.nvm/versions/node/v24.16.0/bin:$PATH"
        
        // Binary paths
        PHP = "/usr/bin/php8.3"
        GIT = "git"
        CURL = "curl"
        PHING = "/usr/bin/phing-3.1.0.phar"
        DOXYGEN = "/usr/bin/doxygen"
        COMPOSER_PHAR = "/usr/bin/composer.phar"
        
        // Modules to install
        MODULES = "bx_posts,bx_timeline,bx_contact,bx_developer,bx_antispam,bx_convos,bx_organizations,bx_notifications,bx_ether,bx_accounts,bx_albums,bx_facebook,bx_tricon,bx_oauth,bx_payment,bx_market,bx_groups,bx_intercom,bx_googlecon,bx_linkedin,bx_twitter,bx_mailchimp,bx_forum,bx_acl,bx_events,bx_files,bx_glossary,bx_googletagman,bx_photos,bx_polls,bx_profiler,bx_quoteofday,bx_smtp,bx_shopify,bx_snipcart,bx_se_migration,bx_spaces,bx_stripe_connect,bx_videos,bx_charts,bx_dolcon,bx_fontawesome,bx_channels,bx_attendant,bx_massmailer,bx_directory,bx_plyr,bx_ads,bx_lucid,bx_credits,bx_wiki,bx_tasks,bx_courses,bx_classes,bx_artificer,bx_donations,bx_feedback,bx_help_tours,bx_stream,bx_attendant,bx_messenger,bx_analytics,bx_reputation,bx_ru"
        
        // Test install parameters
        INSTALL_DB_NAME = "mainsite_virtual"
        INSTALL_DB_USER = "mainsite_una"
        INSTALL_DB_PWD = "*****"
        
        // Package parameters
        PACKAGE_PUBLISH_DIR = "/var/www/vhosts/mainsite/html/builds"
        PACKAGE_PUBLISH_LATEST_FILE = "${PACKAGE_PUBLISH_DIR}/latest-file.txt"
        PACKAGE_PUBLISH_LATEST_DATE = "${PACKAGE_PUBLISH_DIR}/latest-date.txt"
        PACKAGE_PUBLISH_LATEST_BUILD = "${PACKAGE_PUBLISH_DIR}/latest-build.txt"
        
        // Deploy parameters
        DEPLOY_PATH = "/var/www/vhosts/mainsite/html/test"
        DEPLOY_HTTP_HOST = "ci.una.io"
        DEPLOY_HTTP_PATH = "/test/"
        DEPLOY_DOC_ROOT = "/var/www/vhosts/mainsite/html"
        DEPLOY_DB_HOST = "127.0.0.1"
        DEPLOY_DB_NAME = "mainsite_una"
        DEPLOY_DB_USER = "mainsite_una"
        DEPLOY_DB_PWD = "*****"
        
        // Documentation parameters
        DOCS_OUTPUT = "/var/www/vhosts/mainsite/html/docs"
        
        // Environment flags
        UNA_HTTPS = "1"
        
        // Date stamp for package
        DSTAMP = sh(script: 'date +"%Y%m%d"', returnStdout: true).trim()
        ZIPFILE = "UNA-${DSTAMP}-${BUILD_NUMBER}.zip"
        
        // Demo users
        DEMO_USERS_PASS = "*****"
        DEMO_USERS_SALT = "*****"
        DEMO_S3_BUCKET = "una-ci"
        DEMO_S3_ENDPOINT = "us-east-1.linodeobjects.com"
        DEMO_S3_KEY = "*****"
        DEMO_S3_SECRET = "*****"
        DEMO_GOOGLE_MAPS_API_KEY = "*****"

        DEMO_MESSENGER_URL = "https://ci.una.io"

        DEMO_API_OAUTH_ID = "*****"
        DEMO_API_OAUTH_SECRET = "*****"
        DEMO_API_KEY = "*****"

        DEMO_SOCKETS_URL = "https://ci.una.io"
        DEMO_SOCKETS_APP_ID = "neo-ci"
        DEMO_SOCKETS_KEY = "neo-ci"
        DEMO_SOCKETS_SECRET = "*****"

        TEST_USER_ADMIN_PWD = "*****"
        TEST_USER_REGULAR_PWD = "*****"
        TEST_USER_TOM_PWD = "*****"
    }
    
    stages {
        stage('Prepare & worspace clean up') {
            steps {
                script {
                    echo "Cleaning workspace before proceeding"
                    // Clean up workspace
                    cleanWs(
                        cleanWhenAborted: true,
                        cleanWhenFailure: true,
                        cleanWhenNotBuilt: true,
                        cleanWhenSuccess: true,
                        cleanWhenUnstable: true,
                        deleteDirs: true,
                        disableDeferredWipeout: true
                    )
                    
                    echo "NodeJS version:"
                    sh 'node -v'
                    
                    echo "Starting UNA CI/CD Pipeline"
                    echo "Build Number: ${BUILD_NUMBER}"
                    echo "Package File: ${ZIPFILE}"
                }
            }
        }
    
        stage('Clone UNA repository') {
            steps {
                git branch: 'master', 
                    url: 'https://github.com/unacms/UNA/',
                    credentialsId: '0043feeb-9df7-4494-9b2b-d6c8a1b8ca08'
            }
        }
        
        stage('Clone additional UNA modules') {
            parallel {
                /*
                stage('Clone: Media Manager') {
                    steps {
                        dir('modules/boonex/media') {
                            git branch: 'master',
                                url: 'git@github.com:unacms/media-manager.git',
                                credentialsId: '0043feeb-9df7-4494-9b2b-d6c8a1b8ca08'
                        }
                    }
                }
                */
                stage('Clone: Messenger') {
                    steps {
                        dir('modules/boonex/messenger') {
                            git branch: 'master',
                                url: 'git@github.com:unacms/jot-client-una.git',
                                credentialsId: '628aae6d-1c6a-437f-aa13-6f74c0167dfc'
                        }
                    }
                }
            }
        }
        
        stage('Build messenger') {
            steps {
                script {
                    dir('modules/boonex/messenger') {
                        echo "Building messenger"
                        sh 'yarn && yarn build'
                    }
                }
            }
        }
        stage('Install dependencies') {
            steps {
                script {
                    echo "Cleaning some files"
                    sh '${PHP} ${PHING} clean_install'
                    
                    echo "Install PHP and JS dependences"
                    sh '${PHP} ${PHING} -verbose -Dphp=${PHP} -Dcomposer=${COMPOSER_PHAR} prepare'
                }
            }
        }
        stage('Test installation') {
            steps {
                script {
                    echo "Setting up permissions"
                    sh '${PHP} ${PHING} change_permissions'
                    
                    echo "Performing installation for testing"
                    sh '''
                        ${PHP} ${PHING} -verbose -debug \
                            -Dphp=${PHP} \
                            -Ddb_name=${INSTALL_DB_NAME} \
                            -Ddb_user=${INSTALL_DB_USER} \
                            -Ddb_password=${INSTALL_DB_PWD} \
                            -Dmodules="${MODULES}" \
                            install
                    '''
                }
            }
        }
        stage('Unit tests') {
            steps {
                script {
                    echo "Running PHP Unit Tests"
                    def status = sh(
                        script: "${PHP} plugins/bin/phpunit -c tests/phpunit.xml",
                        returnStatus: true
                    )
                    echo "PHPUnit exit code: ${status}"
                }
            }
            post {
                always {
                    // Publish test results if they exist
                    archiveArtifacts artifacts: 'logs/junit.xml', allowEmptyArchive: true
                    junit checksName: 'Unit-Tests', testResults: 'logs/junit.xml'
                }
            }
        }
        
        stage('Code Quality Analysis') {
            steps {
                script {
                    echo "Running PHP Copy Paste Detector"
                    sh '''
${PHP} -d memory_limit=512M /usr/bin/phpcpd.phar --min-lines 18 --log-pmd logs/phpcpd.xml \
  --exclude plugins \
  --exclude plugins_public \
  --exclude modules/boonex/xero/plugins \
  --exclude upgrade \
  --exclude cache \
  --exclude cache_public \
  --exclude storage \
  --exclude logs \
  --exclude tmp \
  --exclude samples \
  --exclude tests \
  --exclude modules/boonex/lucid/data \
  --exclude modules/boonex/decorous/data \
  --exclude modules/boonex/protean/data \
  --exclude modules/boonex/artificer/data \
  --exclude modules/boonex/membership_pricing \
  --exclude modules/boonex/se_migration \
  --exclude modules/boonex/dolphin_migration \
  --exclude modules/boonex/anon_follow \
  --exclude modules/boonex/plyr/updates \
  --exclude modules/boonex/classes/updates \
  --exclude modules/boonex/nexus/updates \
  --exclude modules/boonex/dolphin_migration/updates \
  --exclude modules/boonex/accounts/updates \
  --exclude modules/boonex/invites/updates \
  --exclude modules/boonex/files/updates \
  --exclude modules/boonex/intercom/updates \
  --exclude modules/boonex/oauth2/updates \
  --exclude modules/boonex/linkedin_connect/updates \
  --exclude modules/boonex/videos/updates \
  --exclude modules/boonex/market/updates \
  --exclude modules/boonex/russian/updates \
  --exclude modules/boonex/decorous/updates \
  --exclude modules/boonex/groups/updates \
  --exclude modules/boonex/lucid/updates \
  --exclude modules/boonex/api/updates \
  --exclude modules/boonex/fontawesome/updates \
  --exclude modules/boonex/spaces/updates \
  --exclude modules/boonex/elasticsearch/updates \
  --exclude modules/boonex/google_connect/updates \
  --exclude modules/boonex/froala/updates \
  --exclude modules/boonex/twitter_connect/updates \
  --exclude modules/boonex/payment/updates \
  --exclude modules/boonex/artificer/updates \
  --exclude modules/boonex/events/updates \
  --exclude modules/boonex/messenger/updates \
  --exclude modules/boonex/donations/updates \
  --exclude modules/boonex/google_tagmanager/updates \
  --exclude modules/boonex/contact/updates \
  --exclude modules/boonex/polls/updates \
  --exclude modules/boonex/developer/updates \
  --exclude modules/boonex/mailchimp/updates \
  --exclude modules/boonex/snipcart/updates \
  --exclude modules/boonex/chat_plus/updates \
  --exclude modules/boonex/persons/updates \
  --exclude modules/boonex/glossary/updates \
  --exclude modules/boonex/xero/updates \
  --exclude modules/boonex/charts/updates \
  --exclude modules/boonex/credits/updates \
  --exclude modules/boonex/acl/updates \
  --exclude modules/boonex/massmailer/updates \
  --exclude modules/boonex/ocean/updates \
  --exclude modules/boonex/markerio/updates \
  --exclude modules/boonex/english/updates \
  --exclude modules/boonex/profiler/updates \
  --exclude modules/boonex/shopify/updates \
  --exclude modules/boonex/timeline/updates \
  --exclude modules/boonex/forum/updates \
  --exclude modules/boonex/smtpmailer/updates \
  --exclude modules/boonex/albums/updates \
  --exclude modules/boonex/antispam/updates \
  --exclude modules/boonex/photos/updates \
  --exclude modules/boonex/courses/updates \
  --exclude modules/boonex/ads/updates \
  --exclude modules/boonex/posts/updates \
  --exclude modules/boonex/protean/updates \
  --exclude modules/boonex/wiki/updates \
  --exclude modules/boonex/facebook_connect/updates \
  --exclude modules/boonex/convos/updates \
  --exclude modules/boonex/dolphin_connect/updates \
  --exclude modules/boonex/tasks/updates \
  --exclude modules/boonex/help_tours/updates \
  --exclude modules/boonex/stripe_connect/updates \
  --exclude modules/boonex/stream/updates \
  --exclude modules/boonex/datafox/updates \
  --exclude modules/boonex/notifications/updates \
  --exclude modules/boonex/attendant/updates \
  --exclude modules/boonex/channels/updates \
  --exclude modules/boonex/analytics/updates \
  --exclude modules/boonex/una_connect/updates \
  --exclude modules/boonex/quoteofday/updates \
  --exclude modules/boonex/organizations/updates \
  --exclude modules/boonex/plyr/install/config.php \
  --exclude modules/boonex/classes/install/config.php \
  --exclude modules/boonex/nexus/install/config.php \
  --exclude modules/boonex/dolphin_migration/install/config.php \
  --exclude modules/boonex/accounts/install/config.php \
  --exclude modules/boonex/invites/install/config.php \
  --exclude modules/boonex/files/install/config.php \
  --exclude modules/boonex/intercom/install/config.php \
  --exclude modules/boonex/oauth2/install/config.php \
  --exclude modules/boonex/linkedin_connect/install/config.php \
  --exclude modules/boonex/videos/install/config.php \
  --exclude modules/boonex/market/install/config.php \
  --exclude modules/boonex/russian/install/config.php \
  --exclude modules/boonex/decorous/install/config.php \
  --exclude modules/boonex/groups/install/config.php \
  --exclude modules/boonex/lucid/install/config.php \
  --exclude modules/boonex/api/install/config.php \
  --exclude modules/boonex/fontawesome/install/config.php \
  --exclude modules/boonex/spaces/install/config.php \
  --exclude modules/boonex/elasticsearch/install/config.php \
  --exclude modules/boonex/google_connect/install/config.php \
  --exclude modules/boonex/froala/install/config.php \
  --exclude modules/boonex/twitter_connect/install/config.php \
  --exclude modules/boonex/payment/install/config.php \
  --exclude modules/boonex/artificer/install/config.php \
  --exclude modules/boonex/events/install/config.php \
  --exclude modules/boonex/messenger/install/config.php \
  --exclude modules/boonex/donations/install/config.php \
  --exclude modules/boonex/google_tagmanager/install/config.php \
  --exclude modules/boonex/contact/install/config.php \
  --exclude modules/boonex/polls/install/config.php \
  --exclude modules/boonex/developer/install/config.php \
  --exclude modules/boonex/mailchimp/install/config.php \
  --exclude modules/boonex/snipcart/install/config.php \
  --exclude modules/boonex/chat_plus/install/config.php \
  --exclude modules/boonex/persons/install/config.php \
  --exclude modules/boonex/glossary/install/config.php \
  --exclude modules/boonex/xero/install/config.php \
  --exclude modules/boonex/charts/install/config.php \
  --exclude modules/boonex/credits/install/config.php \
  --exclude modules/boonex/acl/install/config.php \
  --exclude modules/boonex/massmailer/install/config.php \
  --exclude modules/boonex/ocean/install/config.php \
  --exclude modules/boonex/markerio/install/config.php \
  --exclude modules/boonex/english/install/config.php \
  --exclude modules/boonex/profiler/install/config.php \
  --exclude modules/boonex/shopify/install/config.php \
  --exclude modules/boonex/timeline/install/config.php \
  --exclude modules/boonex/forum/install/config.php \
  --exclude modules/boonex/smtpmailer/install/config.php \
  --exclude modules/boonex/albums/install/config.php \
  --exclude modules/boonex/antispam/install/config.php \
  --exclude modules/boonex/photos/install/config.php \
  --exclude modules/boonex/courses/install/config.php \
  --exclude modules/boonex/ads/install/config.php \
  --exclude modules/boonex/posts/install/config.php \
  --exclude modules/boonex/protean/install/config.php \
  --exclude modules/boonex/wiki/install/config.php \
  --exclude modules/boonex/facebook_connect/install/config.php \
  --exclude modules/boonex/convos/install/config.php \
  --exclude modules/boonex/dolphin_connect/install/config.php \
  --exclude modules/boonex/tasks/install/config.php \
  --exclude modules/boonex/help_tours/install/config.php \
  --exclude modules/boonex/stripe_connect/install/config.php \
  --exclude modules/boonex/stream/install/config.php \
  --exclude modules/boonex/datafox/install/config.php \
  --exclude modules/boonex/notifications/install/config.php \
  --exclude modules/boonex/attendant/install/config.php \
  --exclude modules/boonex/channels/install/config.php \
  --exclude modules/boonex/analytics/install/config.php \
  --exclude modules/boonex/una_connect/install/config.php \
  --exclude modules/boonex/quoteofday/install/config.php \
  --exclude modules/boonex/organizations/install/config.php \
  . || true
                    '''
                    
                    echo "Checking log files:"
                    sh 'ls -la logs/'
                }
            }
            post {
                always {
                    // Archive code quality reports
                    // archiveArtifacts artifacts: 'logs/phpcpd.xml', allowEmptyArchive: true, followSymlinks: false
                    recordIssues(
                        sourceCodeRetention: 'LAST_BUILD',
                        tools: [cpd(pattern: 'logs/phpcpd.xml')]
                    )
                }
            }
        }

        stage('Install Composer production dependencies') {
            steps {
                script {
                    echo "Installing Composer production dependencies"
                    sh '''
                        ${COMPOSER_PHAR} install \
                            --no-dev \
                            --no-interaction \
                            --prefer-dist \
                            --optimize-autoloader
                    '''
                }
            }
        }

        stage('Package Build') {
            steps {
                script {
                    echo "Creating package: ${ZIPFILE}"
                    sh '${PHP} ${PHING} -Dpackage_file=${ZIPFILE} package'
                    sh 'cp "packages/${ZIPFILE}" ${PACKAGE_PUBLISH_DIR}'
                    
                    // Update latest build info
                    sh 'echo ${ZIPFILE} > ${PACKAGE_PUBLISH_LATEST_FILE}'
                    sh 'date +"%Y.%m.%d" > ${PACKAGE_PUBLISH_LATEST_DATE}'
                    sh 'echo ${BUILD_NUMBER} > ${PACKAGE_PUBLISH_LATEST_BUILD}'
                }
            }
            post {
                success {
                    archiveArtifacts artifacts: "packages/${ZIPFILE}", fingerprint: true
                }
            }
        }

        stage('Deployment') {
            steps {
                script {
                    echo "Cleaning up test site"
                    sh '"${DEPLOY_PATH}/../../cleanup_test_site.sh"'
                    
                    echo "Deploying to test environment"
                    sh '''
                        ${PHP} ${PHING} \
                            -Dphp=${PHP} \
                            -Ddeploy_path=${DEPLOY_PATH} \
                            -Dhttp_host=${DEPLOY_HTTP_HOST} \
                            -Dhttp_path=${DEPLOY_HTTP_PATH} \
                            -Ddoc_root=${DEPLOY_DOC_ROOT} \
                            -Ddb_name=${DEPLOY_DB_NAME} \
                            -Ddb_host=${DEPLOY_DB_HOST} \
                            -Ddb_user=${DEPLOY_DB_USER} \
                            -Ddb_password=${DEPLOY_DB_PWD} \
                            -Dmodules="${MODULES}" \
                            deploy
                    '''
                }
            }
        }
        
        stage('Database Configuration') {
            steps {
                script {
                    echo "Configuring database for UI tests"

                    // Enable Lucid icons
                    executeDbQuery(
                        "UPDATE sys_options SET value = 'sys_lucide' WHERE name = 'sys_iconset_default'",
                        "Enable Lucid icons"
                    )
                    
                    // Maps API key
                    executeDbQuery(
                        "UPDATE sys_options SET value = '${DEMO_GOOGLE_MAPS_API_KEY}' WHERE name = 'sys_maps_api_key'",
                        "Setting Google Maps API key"
                    )
                    
                    // Messenger server  
                    executeDbQuery(
                        "UPDATE sys_options SET value = '${DEMO_MESSENGER_URL}' WHERE name = 'bx_messenger_server_url'",
                        "Configuring messenger server URL"
                    )
                    
                    // SMTP mailer
                    executeDbQuery(
                        "UPDATE sys_options SET value = 'on' WHERE name = 'bx_smtp_on'",
                        "Enabling SMTP mailer"
                    )
                    executeDbQuery(
                        "UPDATE sys_options SET value = 'localhost' WHERE name = 'bx_smtp_host'",
                        "Configuring SMTP mailer host"
                    )
                    executeDbQuery(
                        "UPDATE sys_options SET value = '1025' WHERE name = 'bx_smtp_port'",
                        "Configuring SMTP mailer port"
                    )
                    
                    // Create test users
                    echo "Creating test users"
                    def users = ['agent', 'user', 'lily', 'eva', 'linda', 'emma', 'karen', 'ella', 'viky', 'mila', 'eric', 'luck', 'jack', 'rob', 'dave', 'tom', 'andy', 'ivan']
                    
                    users.each { user ->
                        executeDbMultiQuery("""
                            SET sql_mode = '';
                            INSERT INTO sys_accounts (name, email, email_confirmed, receive_updates, receive_news, password, salt, role, added) VALUES
                            ('${user}', '${user}@example.com', 1, 0, 0, SHA1(CONCAT(MD5('${DEMO_USERS_PASS}'), '${DEMO_USERS_SALT}')), '${DEMO_USERS_SALT}', 1, UNIX_TIMESTAMP());
                            SET @iAccountId = LAST_INSERT_ID();
                            INSERT INTO sys_profiles (account_id, type, content_id, status) VALUES
                            (@iAccountId, 'system', @iAccountId, 'active');
                        """, "Creating user: ${user}")
                    }
                    
                    // Storage configuration
                    echo "Configuring S3 storage"
                    executeDbQuery(
                        "UPDATE sys_options SET value = '${DEMO_S3_KEY}' WHERE name = 'sys_storage_s3_access_key'",
                        "Setting S3 access key"
                    )
                    executeDbQuery(
                        "UPDATE sys_options SET value = '${DEMO_S3_SECRET}' WHERE name = 'sys_storage_s3_secret_key'",
                        "Setting S3 secret key"
                    )
                    executeDbQuery(
                        "UPDATE sys_options SET value = '${DEMO_S3_BUCKET}' WHERE name = 'sys_storage_s3_bucket'",
                        "Setting S3 bucket name"
                    )
                    executeDbQuery(
                        "UPDATE sys_options SET value = '${DEMO_S3_ENDPOINT}' WHERE name = 'sys_storage_s3_endpoint'",
                        "Setting S3 endpoint"
                    )
                    executeDbQuery(
                        "UPDATE sys_objects_storage SET engine = 'S3'",
                        "Switching storage engine to S3"
                    )
                    
                    // SEO URI rewrites
                    executeDbQuery(
                        "INSERT INTO sys_seo_uri_rewrites SET uri_orig = 'view-persons-profile', uri_rewrite = 'u'",
                        "Adding person profile URL rewrite"
                    )
                    executeDbQuery(
                        "INSERT INTO sys_seo_uri_rewrites SET uri_orig = 'view-organization-profile', uri_rewrite = 'o'",
                        "Adding organization profile URL rewrite"
                    )
                    
                    // Enable subgroups
                    executeDbQuery(
                        "UPDATE sys_objects_privacy SET spaces = 'bx_events,bx_spaces,bx_groups' WHERE object = 'bx_groups_allow_view_to'",
                        "Enabling subgroups functionality"
                    )
                    
                    // Tmp fix for search via API
                    executeDbQuery(
                        "UPDATE sys_objects_search SET GlobalSearch = '0' WHERE ObjectName IN('sys_pages', 'bx_timeline')",
                        "Tmp fix for search via API"
                    )
                }
            }
        }
        
        stage('API Configuration') {
            parallel {
                stage('Old API Setup') {
                    steps {
                        script {
                            echo "Configuring old OAuth API"
                            executeDbMultiQuery("""
                                INSERT INTO bx_oauth_clients (title, client_id, client_secret, redirect_uri, grant_types, scope, parent_id, user_id) VALUES 
                                ('nextjs', '${DEMO_API_OAUTH_ID}', '${DEMO_API_OAUTH_SECRET}', 'http://app.una.io:3000/', NULL, 'service', 0, 1);
                                SET @iClientId = LAST_INSERT_ID();
                                INSERT INTO bx_oauth_allowed_origins (client_id, url) VALUES 
                                (@iClientId, 'https://ci.una.io:3000');
                            """, "Setting up OAuth client and allowed origins")
                        }
                    }
                }
                
                stage('New API Setup') {
                    steps {
                        script {
                            echo "Configuring new API system"
                            executeDbMultiQuery("""
                                INSERT INTO sys_api_keys (title, `key`, `order`) VALUES
                                ('NextJS', '${DEMO_API_KEY}', 1);
                                INSERT INTO sys_api_origins (url, `order`) VALUES
                                ('neo://app', 1),
                                ('http://localhost:3000', 2),
                                ('https://neo.vercel.app/', 4),
                                ('https://neo.so/', 5),
                                ('http://hihi.com', 6),
                                ('https://hihi.com', 7),
                                ('https://neo-ci.una.io', 8);
                                UPDATE sys_options SET value = 'on' WHERE name = 'sys_api_enable';
                                UPDATE sys_options SET value = '' WHERE name = 'sys_api_access_by_origin';
                                UPDATE sys_options SET value = 'on' WHERE name = 'sys_api_access_by_key';
                                UPDATE sys_options SET value = 'on' WHERE name = 'sys_session_auth';
                            """, "Setting up new API keys, origins and enabling API features")
                        }
                    }
                }
                
                stage('Sockets Server Setup') {
                    steps {
                        script {
                            echo "Configuring sockets server"
                            executeDbMultiQuery("""
                                UPDATE sys_options SET value = 'sys_sockets_soketi' WHERE name = 'sys_sockets_type';
                                UPDATE sys_options SET value = '${DEMO_SOCKETS_URL}' WHERE name = 'sys_sockets_url';
                                UPDATE sys_options SET value = '${DEMO_SOCKETS_APP_ID}' WHERE name = 'sys_sockets_app_id';
                                UPDATE sys_options SET value = '${DEMO_SOCKETS_KEY}' WHERE name = 'sys_sockets_key';
                                UPDATE sys_options SET value = '${DEMO_SOCKETS_SECRET}' WHERE name = 'sys_sockets_secret';
                            """, "Configuring Soketi sockets server settings")
                        }
                    }
                }
                
                stage('AI Agents Setup') {
                    steps {
                        script {
                            echo "Creating vector store file"
                            sh '''
                                mkdir -p -m 0777 "${DEPLOY_PATH}/storage/vector_stores/1"
                                cp /var/lib/jenkins/neuron.store ${DEPLOY_PATH}/storage/vector_stores/1/data.store
                                chmod 0666 "${DEPLOY_PATH}/storage/vector_stores/1/data.store"
                            '''
                            
                            echo "Configuring AI agents"
                            executeDbMultiQuery("""
                                                            
    
                                UPDATE `sys_agents_models` SET `active` = 1, `changed` = UNIX_TIMESTAMP() WHERE `type` = 'ollama' AND `model` = 'llama3.2:1b';
                                UPDATE `sys_agents_models` SET `active` = 1, `changed` = UNIX_TIMESTAMP() WHERE `type` = 'ollama-embeddings' AND `model` = 'all-minilm';
                                
                                SET @model_embedding = (SELECT `id` FROM `sys_agents_models` WHERE `type` = 'ollama-embeddings' AND `model` = 'all-minilm');
                                
                                UPDATE `sys_agents_vector_store` SET `active` = 1, `changed` = UNIX_TIMESTAMP(), `embedding_provider_id` = @model_embedding WHERE `type` = 'file';
                                
                                SET @vector_store = (SELECT `id` FROM `sys_agents_vector_store` WHERE `type` = 'file');
                                
                                INSERT INTO `sys_agents_vector_store_data` (`id`, `vector_store_id`, `type`, `name`, `size`, `metadata`, `settings`, `content`, `status`, `added`) VALUES
                                (NULL, @vector_store, 'custom', 'data_for_vector_store.md', 189, '', '{\"chunk_size\":512,\"delimeter\":\".\",\"overlap\":2}', 'Documentation is very important. UNACMS is modular CMS written in PHP and backed by MySQL database. UNA CMS features such modules as Posts, Discussions, Notifications, Timeline and others.\n', 'ready', UNIX_TIMESTAMP());

                                SET @model = (SELECT `id` FROM `sys_agents_models` WHERE `type` = 'ollama' AND `model` = 'llama3.2:1b');
                                SET @profile = (SELECT `p`.`id` FROM `sys_profiles` AS `p` INNER JOIN `sys_accounts` AS `a` ON(`p`.`account_id` = `a`.id) WHERE `email` = '');
                                
                                INSERT INTO `sys_agents_agents` (`id`, `name`, `title`, `description`, `icon`, `model_id`, `profile_id`, `acl_levels`, `prompt_system`, `prompt_steps`, `prompt_output`, `prompt_tools`, `tools`, `tools_max_run`, `limit_message`, `chat_history_context`, `vector_store_id`, `trigger`, `async`, `alert`, `scheduler_cron`, `webhook_key`, `webhook_sample`, `form_object`, `form_input`, `agent_sample`, `message_profile_id`, `added`, `active`) VALUES
                                (NULL, 'john-agent', 'John Agent', '', '', @model, @profile, 2147483647, 'You are real person. Your name is John. You can talk freely with anyone.', '', '', '', '', 0, '', 50000, @vector_store, 'message', 0, '', '', '', '', '', '', '', 0, UNIX_TIMESTAMP(), 1),
                                (NULL, 'profile-description-agent', 'Profile Description Agent', '', '', @model, @profile, 2147483646, 'Write a biography from first person. ', '', '', '', '', 0, '', 0, @vector_store, 'form-input', 0, '', '', '', '', 'bx_person', 'description', '', 0, UNIX_TIMESTAMP(), 1);


                            """, "Configuring local Ollama AI providers, embedder and agents")
                        }
                    }
                }
            }
        }
        
        stage('UI Tests') {
            steps {
                timeout(time: 6, unit: 'MINUTES') {
                    script {
                        echo "Creating cache directories"
                        sh '''
                            mkdir -p -m 0777 "${DEPLOY_PATH}/cache/CSS"
                            mkdir -p -m 0777 "${DEPLOY_PATH}/cache/HTML"
                            mkdir -p -m 0777 "${DEPLOY_PATH}/cache/URI"
                        '''
                        dir('una-ui-test3') {
                        
                            echo "Clone repository with tests"
                            git branch: 'main',
                                url: 'git@github.com:unacms/una-ui-test3.git',
                                credentialsId: '628aae6d-1c6a-437f-aa13-6f74c0167dfc'
                            
                            // Create environment file
                            sh '''
                                cat > .env.local << EOF
baseUrl=https://${DEPLOY_HTTP_HOST}${DEPLOY_HTTP_PATH}
userAdminPwd=${TEST_USER_ADMIN_PWD}
userRegularPwd=${TEST_USER_REGULAR_PWD}
userTomPwd=${TEST_USER_TOM_PWD}
EOF
                            '''
                        
                            echo "Install dependences for UI tests"
                            sh 'npm install'
                            sh 'npx playwright install'
                        
                            echo "Running Playwright UI Tests"
                            sh 'npm run test'
                        }
                    }
                }
            }
            post {
                always {
                    // Archive test results
                    archiveArtifacts artifacts: 'una-ui-test3/results/results.xml', allowEmptyArchive: true
                    junit checksName: 'UI-Tests', testResults: 'una-ui-test3/results/results.xml'
                }
            }
        }
        
        // end
    }
    
    post {
/*
        always {
            xunit checksName: 'UI-Tests', thresholds: [
                    failed(failureNewThreshold: '0', failureThreshold: '0', unstableNewThreshold: '0', unstableThreshold: '0')
                ], 
                tools: [
                    JUnit(excludesPattern: '', pattern: 'una-ui-test3/results/results.xml', stopProcessingIfError: true)
                ]
        }
*/
        success {
            echo 'UNA CI/CD Pipeline completed successfully!'
        }
        failure {
            echo 'UNA CI/CD Pipeline failed!'
            script {
                emailext(
                    subject: "Build Failed: ${env.JOB_NAME} #${env.BUILD_NUMBER}",
                    body: "Build failed!\nCheck console output at ${env.BUILD_URL}\n\nCommit author: ${env.CHANGE_AUTHOR ?: 'unknown'}",
                    to: "${env.CHANGE_AUTHOR_EMAIL}"
                )
                slackSend(
                    botUser: true, 
                    channel: '#unacms', 
                    color: '#ff4444', 
                    iconEmoji: '👮', 
                    message: "Build failed!\nCheck console output at ${env.BUILD_URL}\n\nCommit author: ${env.CHANGE_AUTHOR ?: 'unknown'}", 
                    notifyCommitters: true, 
                    tokenCredentialId: 'slack-jenkins-bot-token'
                )
            }
        }
        unstable {
            echo 'UNA CI/CD Pipeline completed with test failures!'
        }
    }
}