wordpress.js

Go to the documentation of this file.

 
/**
 * @file wordpress.js
 * @module routes/wordpress
 * @description WordPress site management and DigitalOcean App Platform deployment endpoints.
 * Handles automated WordPress provisioning with database, Spaces storage, and credential management.
 * Integrates with DigitalOcean API for app creation and project organization.
 * @requires express
 * @requires middleware/auth
 * @requires middleware/tenant
 * @requires services/db
 * @requires axios
 * @requires crypto
 * @author RMM-PSA Development Team
 * @copyright 2026 RMM-PSA Platform
 * @license Proprietary
 */
 
/**
 * @apiDefine WordPress WordPress
 * WordPress site deployment and management for DigitalOcean App Platform
 */
 
const express = require('express');
const router = express.Router();
const authenticateToken = require('../middleware/auth');
const { setTenantContext } = require('../middleware/tenant');
const pool = require('../db');
const axios = require('axios');
const crypto = require('crypto');
// const { Client } = require('ssh2');
// const fs = require('fs');
 
// Monitoring POST endpoint (no auth required - uses API key instead)
router.post('/monitoring', async (req, res) => {
    try {
        const monitorKey = req.headers['x-monitor-key'] || req.headers['x-monitor-api-key'];
        
        if (!monitorKey) {
            return res.status(401).json({ error: 'Missing monitor key' });
        }
 
        const { server_ip, server_hostname, timestamp, last_scan, sites } = req.body;
 
        if (!server_ip || !sites) {
            return res.status(400).json({ error: 'Missing required fields' });
        }
 
        // Store raw monitoring report
        await pool.query(`
            INSERT INTO wordpress_monitoring_reports (server_ip, server_hostname, report_data)
            VALUES ($1, $2, $3)
        `, [server_ip, server_hostname, JSON.stringify(req.body)]);
 
        console.log(`[WordPress Monitoring] Received report from ${server_ip} with ${sites.length} sites`);
 
        res.json({ 
            success: true,
            message: 'Monitoring data received',
            sites_updated: sites.length
        });
    } catch (error) {
        console.error('[WordPress Monitoring] Error processing data:', error);
        res.status(500).json({ error: 'Failed to process monitoring data' });
    }
});
 
// Apply authentication and tenant context to all other routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /api/wordpress/deployments/:id/progress Get deployment progress
 * @apiName GetWordPressDeploymentProgress
 * @apiGroup WordPress
 * @apiDescription Check the progress of a WordPress site deployment. Returns current status,
 * step, and any error messages. Used for real-time deployment monitoring in the UI.
 * @apiParam {number} id Deployment ID
 * @apiSuccess {object} progress Deployment progress object
 * @apiSuccess {number} progress.id Deployment ID
 * @apiSuccess {string} progress.site_name Site name being deployed
 * @apiSuccess {string} progress.status Status (pending, in_progress, success, error)
 * @apiSuccess {string} progress.step Current deployment step
 * @apiSuccess {string} progress.message Progress message
 * @apiSuccess {string} [progress.error] Error message if status=error
 * @apiSuccess {DateTime} progress.created_at Deployment start time
 * @apiSuccess {DateTime} progress.updated_at Last update time
 * @apiError {string} error="Deployment not found" (404) Deployment ID not found
 * @apiError {string} error="Failed to fetch deployment progress" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/wordpress/deployments/123/progress \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "progress": {
 *       "id": 123,
 *       "site_name": "example-site",
 *       "status": "in_progress",
 *       "step": "database",
 *       "message": "Creating database wp_example_site",
 *       "created_at": "2026-03-12T10:00:00.000Z",
 *       "updated_at": "2026-03-12T10:02:30.000Z"
 *     }
 *   }
 */
router.get('/deployments/:id/progress', async (req, res) => {
  const { id } = req.params;
  try {
    const result = await pool.query(
      `SELECT id, site_name, status, step, message, error, created_at, updated_at
       FROM wordpress_deployments WHERE id = $1`,
      [id]
    );
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Deployment not found' });
    }
    res.json({ progress: result.rows[0] });
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch deployment progress', details: error.message });
  }
});
 
// Helper function to generate secure password
/**
 *
 * @param length
 */
function generatePassword(length = 16) {
  const charset = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*';
  let password = '';
  const randomBytes = crypto.randomBytes(length);
  for (let i = 0; i < length; i++) {
    password += charset[randomBytes[i] % charset.length];
  }
  return password;
}
 
/**
 * Execute command on WordPress server via SSH
 * @param {string} serverIp - Server IP address
 * @param {string} command - Command to execute
 * @returns {Promise<string>} Command output
 */
/* TEMPORARILY DISABLED - Moving to API bridge approach for security
async function sshExec(serverIp, command) {
    return new Promise((resolve, reject) => {
        const conn = new Client();
        let output = '';
        
        const sshKeyPath = process.env.WORDPRESS_SSH_KEY_PATH || '/root/.ssh/wordpress_hosting_rsa';
        
        if (!fs.existsSync(sshKeyPath)) {
            return reject(new Error('SSH key not found'));
        }
        
        const privateKey = fs.readFileSync(sshKeyPath);
        
        conn.on('ready', () => {
            conn.exec(command, (err, stream) => {
                if (err) {
                    conn.end();
                    return reject(err);
                }
                
                stream.on('close', (code) => {
                    conn.end();
                    if (code !== 0) {
                        return reject(new Error(`Command exited with code ${code}`));
                    }
                    resolve(output);
                }).on('data', (data) => {
                    output += data.toString();
                }).stderr.on('data', (data) => {
                    console.error('[SSH] stderr:', data.toString());
                });
            });
        }).on('error', (err) => {
            reject(err);
        }).connect({
            host: serverIp,
            port: 22,
            username: 'root',
            privateKey: privateKey
        });
    });
}
*/
 
/**
 * @api {get} /api/wordpress/sites List WordPress sites
 * @apiName ListWordPressSites
 * @apiGroup WordPress
 * @apiDescription Get all WordPress sites for the current tenant with optional customer details.
 * Sites are ordered by creation date (newest first).
 * @apiSuccess {Array} sites Array of WordPress site objects
 * @apiSuccess {UUID} sites.site_id Site ID
 * @apiSuccess {UUID} sites.tenant_id Tenant ID
 * @apiSuccess {UUID} [sites.customer_id] Customer ID
 * @apiSuccess {string} sites.name Site name (slug)
 * @apiSuccess {string} sites.app_id DigitalOcean App ID
 * @apiSuccess {string} sites.region Region (syd, nyc3, etc)
 * @apiSuccess {string} sites.status App status (deploying, active, error)
 * @apiSuccess {string} sites.url App URL
 * @apiSuccess {string} [sites.domain] Custom domain if configured
 * @apiSuccess {string} sites.db_name MySQL database name
 * @apiSuccess {string} sites.spaces_path Spaces storage folder
 * @apiSuccess {string} sites.wp_admin_user WordPress admin username
 * @apiSuccess {string} sites.wp_admin_password WordPress admin password
 * @apiSuccess {string} sites.wp_admin_email WordPress admin email
 * @apiSuccess {string} [sites.customer_name] Customer name (joined)
 * @apiSuccess {DateTime} sites.created_at Creation timestamp
 * @apiError {string} error="Missing tenant context" (400) No tenant in request
 * @apiError {string} error="Failed to fetch WordPress sites" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/wordpress/sites \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "sites": [
 *       {
 *         "site_id": "123e4567-e89b-12d3-a456-426614174000",
 *         "tenant_id": "456e7890-ab12-34cd-5678-901234567890",
 *         "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *         "name": "example-site",
 *         "app_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *         "region": "syd",
 *         "status": "active",
 *         "url": "https://wordpress-example-site-abc123.ondigitalocean.app",
 *         "domain": "www.example.com",
 *         "db_name": "wp_example_site",
 *         "spaces_path": "example-site",
 *         "wp_admin_user": "admin",
 *         "wp_admin_password": "SecurePass123!@#$%",
 *         "wp_admin_email": "admin@example-site.everydaytech.au",
 *         "customer_name": "Acme Corp",
 *         "created_at": "2026-03-12T08:00:00.000Z"
 *       }
 *     ]
 *   }
 */
router.get('/sites', async (req, res) => {
  try {
    // Validate tenant context
    if (!req.tenant || !req.tenant.id) {
      console.error('[WordPress] Missing tenant context:', { tenant: req.tenant, user: req.user?.email });
      return res.status(400).json({ 
        error: 'Missing tenant context. Please ensure you are logged in and have a valid tenant.' 
      });
    }
 
    const result = await pool.query(
      `SELECT 
        w.*,
        c.name as customer_name
       FROM wordpress_sites w
       LEFT JOIN customers c ON w.customer_id = c.customer_id
       WHERE w.tenant_id = $1
       ORDER BY w.created_at DESC`,
      [req.tenant.id]
    );
 
    res.json({ sites: result.rows });
  } catch (error) {
    console.error('[WordPress] Error fetching sites:', error);
    res.status(500).json({ error: 'Failed to fetch WordPress sites', details: error.message });
  }
});
 
/**
 * @api {post} /api/wordpress/create Create WordPress site
 * @apiName CreateWordPressSite
 * @apiGroup WordPress
 * @apiDescription Fully automated WordPress site provisioning on DigitalOcean App Platform.
 * Creates MySQL database, configures Spaces storage, deploys from GitHub template with PHP buildpack,
 * generates credentials, and assigns to WordPress project. Tracks progress via deployment ID.
 * @apiParam {string} siteName Site name slug (lowercase alphanumeric with hyphens)
 * @apiParam {number} [customerId] Customer ID to link site to
 * @apiParam {string} [domain] Custom domain (optional, PRIMARY domain for app)
 * @apiSuccess {boolean} success=true Site created successfully
 * @apiSuccess {string} message="WordPress site created successfully"
 * @apiSuccess {string} appId DigitalOcean App ID
 * @apiSuccess {string} url Default app URL (.ondigitalocean.app)
 * @apiSuccess {string} status App deployment status
 * @apiSuccess {number} deploymentId Deployment progress tracker ID
 * @apiSuccess {Object} credentials WordPress and database credentials
 * @apiSuccess {string} credentials.adminUser WordPress admin username (always 'admin')
 * @apiSuccess {string} credentials.adminPassword Generated WordPress admin password
 * @apiSuccess {string} credentials.adminEmail WordPress admin email
 * @apiSuccess {string} credentials.database MySQL database name
 * @apiSuccess {string} credentials.spacesPath Spaces storage folder
 * @apiError {string} error="Missing tenant context" (400) No tenant in request
 * @apiError {string} error="Missing required field: siteName" (400) siteName not provided
 * @apiError {string} error="Site name must contain only lowercase letters, numbers, and hyphens" (400) Invalid siteName format
 * @apiError {String} error="WordPress credentials not configured" (400) Global WordPress config missing
 * @apiError {String} error="DigitalOcean configuration not found" (404) Tenant DO config missing
 * @apiError {String} error (500) Deployment failed
 * 
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/wordpress/create \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{
 *       "siteName": "example-site",
 *       "customerId": 123,
 *       "domain": "www.example.com"
 *     }'
 * 
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "WordPress site created successfully",
 *     "appId": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *     "url": "https://wordpress-example-site-abc123.ondigitalocean.app",
 *     "status": "deploying",
 *     "deploymentId": 456,
 *     "credentials": {
 *       "adminUser": "admin",
 *       "adminPassword": "Xy9!kL2#mN5$pQ8@rT1%",
 *       "adminEmail": "admin@example-site.everydaytech.au",
 *       "database": "wp_example_site",
 *       "spacesPath": "example-site"
 *     }
 *   }
 */
router.post('/create', async (req, res) => {
  const { siteName: siteNameRaw, customerId: customerIdRaw, domain: domainRaw } = req.body;
 
  // Ensure values are properly typed
  const siteName = siteNameRaw ? String(siteNameRaw).trim() : null;
  const domain = domainRaw ? String(domainRaw).trim() : null;
  const customerId = customerIdRaw ? parseInt(customerIdRaw, 10) : null;
 
  // Validate tenant context
  if (!req.tenant || !req.tenant.id) {
    console.error('[WordPress] Missing tenant context:', { tenant: req.tenant, user: req.user?.email });
    return res.status(400).json({ 
      error: 'Missing tenant context. Please ensure you are logged in and have a valid tenant.' 
    });
  }
 
  // Validation
  if (!siteName) {
    return res.status(400).json({ 
      error: 'Missing required field: siteName' 
    });
  }
 
  // Validate siteName format (lowercase, alphanumeric, hyphens only)
  if (!/^[a-z0-9-]+$/.test(siteName)) {
    return res.status(400).json({ 
      error: 'Site name must contain only lowercase letters, numbers, and hyphens' 
    });
  }
 
  // Validate customerId if provided
  if (customerId !== null && (isNaN(customerId) || customerId < 1)) {
    return res.status(400).json({ 
      error: 'Invalid customerId: must be a positive integer' 
    });
  }
 
  let deploymentId = null;
  try {
    // Insert deployment progress record
    const depResult = await pool.query(
      `INSERT INTO wordpress_deployments (site_name, status, step, message)
       VALUES ($1, 'pending', 'starting', 'Starting deployment') RETURNING id`,
      [siteName]
    );
    deploymentId = depResult.rows[0].id;
    const updateProgress = async (step, status, message, error = null) => {
      await pool.query(
        `UPDATE wordpress_deployments SET step=$1, status=$2, message=$3, error=$4, updated_at=NOW() WHERE id=$5`,
        [step, status, message, error, deploymentId]
      );
    };
    // Get WordPress credentials from global settings
    const settingsResult = await pool.query('SELECT config FROM app_settings ORDER BY id DESC LIMIT 1');
    
    if (settingsResult.rows.length === 0 || !settingsResult.rows[0].config || !settingsResult.rows[0].config.wordpress) {
      if (deploymentId) await updateProgress('settings', 'error', 'WordPress credentials not configured', 'Missing config');
      return res.status(400).json({ 
        error: 'WordPress credentials not configured. Please configure in Settings (Root Tenant only).' 
      });
    }
 
    const wordpressConfig = settingsResult.rows[0].config.wordpress;
    const dbHost = wordpressConfig.database_host;
    const dbPort = wordpressConfig.database_port;
    const dbUser = wordpressConfig.database_user;
    const dbPassword = wordpressConfig.database_password;
    const dbDefaultName = wordpressConfig.database_name;
    const dbSslMode = wordpressConfig.database_sslmode || 'REQUIRED';
    const mysqlClusterId = wordpressConfig.mysql_cluster_id;
    const spacesKey = wordpressConfig.spaces_access_key;
    const spacesSecret = wordpressConfig.spaces_secret_key;
 
    if (!dbHost || !dbPort || !dbUser || !dbPassword || !dbDefaultName || 
        !mysqlClusterId || !spacesKey || !spacesSecret) {
      if (deploymentId) await updateProgress('settings', 'error', 'Incomplete WordPress credentials in settings', 'Missing fields');
      return res.status(400).json({ 
        error: 'Incomplete WordPress credentials in settings. Please configure all fields in Settings (database_host, database_port, database_user, database_password, database_name, mysql_cluster_id, spaces_access_key, spaces_secret_key).' 
      });
    }
 
    // Get DigitalOcean API token
    const configResult = await pool.query(
      `SELECT api_token_encrypted FROM digitalocean_config WHERE tenant_id = $1 AND is_active = true`,
      [req.tenant.id]
    );
 
    if (configResult.rows.length === 0) {
      if (deploymentId) await updateProgress('settings', 'error', 'DigitalOcean configuration not found', 'No config');
      return res.status(404).json({ 
        error: 'DigitalOcean configuration not found. Please configure in Settings.' 
      });
    }
 
    const doToken = configResult.rows[0].api_token_encrypted;
 
    // Generate WordPress admin credentials
    const wpAdminUser = 'admin';
    const wpAdminPassword = generatePassword(20);
    const wpAdminEmail = `admin@${siteName}.everydaytech.au`;
    const dbName = `wp_${siteName.replace(/-/g, '_')}`;
    const spacesPath = siteName;
 
    if (deploymentId) await updateProgress('credentials', 'in_progress', 'Generated WordPress credentials');
 
    // Create MySQL database on DigitalOcean (using cluster ID from settings)
    try {
      if (deploymentId) await updateProgress('database', 'in_progress', `Creating database ${dbName}`);
      await axios.post(
        `https://api.digitalocean.com/v2/databases/${mysqlClusterId}/dbs`,
        { name: dbName },
        {
          headers: {
            'Authorization': `Bearer ${doToken}`,
            'Content-Type': 'application/json'
          }
        }
      );
      if (deploymentId) await updateProgress('database', 'success', `Database ${dbName} created successfully`);
    } catch (dbError) {
      if (dbError.response?.status === 422) {
        if (deploymentId) await updateProgress('database', 'success', `Database ${dbName} already exists, continuing...`);
      } else {
        if (deploymentId) await updateProgress('database', 'error', 'Failed to create MySQL database', dbError.response?.data?.message || dbError.message);
        throw new Error(`Failed to create MySQL database: ${dbError.response?.data?.message || dbError.message}`);
      }
    }
 
    // Note: Spaces folder will be auto-created by WordPress when it uploads first file
    // The BUCKET_SITE_PATH env var tells WordPress which folder to use
    if (deploymentId) await updateProgress('spaces', 'in_progress', 'Preparing Spaces bucket/folder');
 
    // Get WordPress project from DigitalOcean
    let projectId = null;
    try {
      const projectsResponse = await axios.get(
        'https://api.digitalocean.com/v2/projects',
        {
          headers: {
            'Authorization': `Bearer ${doToken}`,
            'Content-Type': 'application/json'
          }
        }
      );
      const projects = projectsResponse.data.projects || [];
      // Look for WordPress projects folder
      const wordpressProject = projects.find(p => 
        p.name.toLowerCase().includes('wordpress')
      );
      if (wordpressProject) {
        projectId = wordpressProject.id;
        if (deploymentId) await updateProgress('project', 'success', `Using project: ${wordpressProject.name}`);
      } else {
        if (deploymentId) await updateProgress('project', 'success', 'No WordPress project found, using default project');
      }
    } catch (error) {
      if (deploymentId) await updateProgress('project', 'error', 'Could not fetch projects', error.message);
      // Continue without project - app will be created in default project
    }
 
    // Create App Spec for WordPress using PHP buildpack (proven working method)
    if (deploymentId) await updateProgress('app', 'in_progress', 'Creating App Platform app');
    const appSpec = {
      name: `wordpress-${siteName}`,
      region: 'syd',
      services: [
        {
          name: 'wordpress',
          github: {
            branch: 'main',
            deploy_on_push: false,
            repo: 'Independent-Business-Group/wordpress-template'
          },
          build_command: 'chmod +x .do/deploy.sh && ./.do/deploy.sh',
          run_command: 'heroku-php-apache2',
          environment_slug: 'php',
          instance_count: 1,
          instance_size_slug: 'apps-s-1vcpu-0.5gb',
          http_port: 8080,
          health_check: {
            http_path: '/health.php',
            port: 8080,
            initial_delay_seconds: 60,
            period_seconds: 10,
            timeout_seconds: 3,
            success_threshold: 1,
            failure_threshold: 3
          },
          routes: [
            {
              path: '/'
            }
          ],
          envs: [
            {
              key: 'DB_NAME',
              scope: 'RUN_AND_BUILD_TIME',
              type: 'SECRET',
              value: dbName
            },
            {
              key: 'DB_USER',
              scope: 'RUN_AND_BUILD_TIME',
              type: 'SECRET',
              value: dbUser
            },
            {
              key: 'DB_PASSWORD',
              scope: 'RUN_AND_BUILD_TIME',
              type: 'SECRET',
              value: dbPassword
            },
            {
              key: 'DB_HOST',
              scope: 'RUN_AND_BUILD_TIME',
              type: 'SECRET',
              value: dbHost
            },
            {
              key: 'DB_PORT',
              scope: 'RUN_AND_BUILD_TIME',
              value: String(dbPort)
            },
            {
              key: 'TABLE_PREFIX',
              scope: 'RUN_AND_BUILD_TIME',
              value: 'wp_'
            },
            {
              key: 'SPACES_BUCKET',
              scope: 'RUN_AND_BUILD_TIME',
              value: 'everydaytech-wordpress'
            },
            {
              key: 'SPACES_FOLDER',
              scope: 'RUN_AND_BUILD_TIME',
              value: spacesPath
            },
            {
              key: 'SPACES_KEY',
              scope: 'RUN_AND_BUILD_TIME',
              type: 'SECRET',
              value: spacesKey
            },
            {
              key: 'SPACES_SECRET',
              scope: 'RUN_AND_BUILD_TIME',
              type: 'SECRET',
              value: spacesSecret
            }
          ]
        }
      ]
    };
 
    // Add custom domain if provided
    if (domain) {
      appSpec.domains = [
        {
          domain: domain,
          type: 'PRIMARY'
        }
      ];
    }
 
    console.log('[WordPress] Creating app with spec:', JSON.stringify(appSpec, null, 2));
 
    // Create app on DigitalOcean
    const doResponse = await axios.post(
      'https://api.digitalocean.com/v2/apps',
      { spec: appSpec },
      {
        headers: {
          'Authorization': `Bearer ${doToken}`,
          'Content-Type': 'application/json'
        }
      }
    );
 
    const app = doResponse.data.app;
    if (deploymentId) await updateProgress('app', 'success', `App created: ${app.id}`);
    console.log(`[WordPress] App created: ${app.id}`);
 
    // Assign app to WordPress project if we found one
    if (projectId) {
      try {
        await axios.post(
          `https://api.digitalocean.com/v2/projects/${projectId}/resources`,
          {
            resources: [`do:app:${app.id}`]
          },
          {
            headers: {
              'Authorization': `Bearer ${doToken}`,
              'Content-Type': 'application/json'
            }
          }
        );
        if (deploymentId) await updateProgress('project', 'success', `App assigned to project ${projectId}`);
        console.log(`[WordPress] App assigned to project ${projectId}`);
      } catch (error) {
        if (deploymentId) await updateProgress('project', 'error', 'Could not assign app to project', error.message);
        console.warn(`[WordPress] Could not assign app to project:`, error.message);
        // Continue even if assignment fails
      }
    }
 
    // Save to database
    await pool.query(
      `INSERT INTO wordpress_sites (
        tenant_id,
        customer_id,
        name,
        app_id,
        region,
        status,
        url,
        domain,
        db_name,
        spaces_path,
        wp_admin_user,
        wp_admin_password,
        wp_admin_email
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)`,
      [
        req.tenant.id,
        customerId || null,
        siteName,
        app.id,
        'syd',
        app.phase || 'deploying',
        app.default_ingress || null,
        domain || null,
        dbName,
        spacesPath,
        wpAdminUser,
        wpAdminPassword,
        wpAdminEmail
      ]
    );
    if (deploymentId) await updateProgress('completed', 'success', 'Deployment completed', null);
 
    res.json({
      success: true,
      message: 'WordPress site created successfully',
      appId: app.id,
      url: app.default_ingress,
      status: app.phase,
      deploymentId,
      credentials: {
        adminUser: wpAdminUser,
        adminPassword: wpAdminPassword,
        adminEmail: wpAdminEmail,
        database: dbName,
        spacesPath: spacesPath
      }
    });
  } catch (error) {
    console.error('[WordPress] Error creating site:', error);
    
    // More detailed error message
    if (error.response) {
      const doError = error.response.data;
      console.error('[WordPress] DO API Error:', JSON.stringify(doError, null, 2));
      return res.status(error.response.status).json({ 
        error: doError.message || 'Failed to create WordPress site on DigitalOcean',
        details: doError
      });
    }
    
    res.status(500).json({ 
      error: error.message || 'Failed to create WordPress site' 
    });
  }
});
 
/**
 * @api {get} /api/wordpress/site/:appId Get WordPress site details
 * @apiName GetWordPressSite
 * @apiGroup WordPress
 * @apiDescription Get detailed information about a specific WordPress site by App ID.
 * Optionally fetches live status from DigitalOcean API and updates database cache.
 * @apiParam {string} appId DigitalOcean App ID
 * @apiSuccess {object} site WordPress site object
 * @apiSuccess {UUID} site.site_id Site ID
 * @apiSuccess {UUID} site.tenant_id Tenant ID
 * @apiSuccess {UUID} [site.customer_id] Customer ID
 * @apiSuccess {string} site.name Site name
 * @apiSuccess {string} site.app_id DigitalOcean App ID
 * @apiSuccess {string} site.status Current status (deploying, active, error)
 * @apiSuccess {string} site.url App URL
 * @apiSuccess {string} [site.domain] Custom domain
 * @apiSuccess {string} site.db_name MySQL database name
 * @apiSuccess {string} site.spaces_path Spaces storage folder
 * @apiSuccess {string} site.wp_admin_user WordPress admin username
 * @apiSuccess {string} site.wp_admin_password WordPress admin password
 * @apiSuccess {string} site.wp_admin_email WordPress admin email
 * @apiSuccess {string} [site.customer_name] Customer name (joined)
 * @apiError {string} error="Missing tenant context" (400) No tenant in request
 * @apiError {string} error="WordPress site not found" (404) Site not found for tenant
 * @apiError {string} error="Failed to fetch WordPress site" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/wordpress/site/a1b2c3d4-e5f6-7890-abcd-ef1234567890 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * 
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "site": {
 *       "site_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "tenant_id": "456e7890-ab12-34cd-5678-901234567890",
 *       "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *       "name": "example-site",
 *       "app_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *       "region": "syd",
 *       "status": "active",
 *       "url": "https://wordpress-example-site-abc123.ondigitalocean.app",
 *       "domain": "www.example.com",
 *       "db_name": "wp_example_site",
 *       "spaces_path": "example-site",
 *       "wp_admin_user": "admin",
 *       "wp_admin_password": "SecurePass123!@#$%",
 *       "wp_admin_email": "admin@example-site.everydaytech.au",
 *       "customer_name": "Acme Corp"
 *     }
 *   }
 */
router.get('/site/:appId', async (req, res) => {
  try {
    const { appId } = req.params;
 
    // Validate tenant context
    if (!req.tenant || !req.tenant.id) {
      console.error('[WordPress] Missing tenant context:', { tenant: req.tenant, user: req.user?.email });
      return res.status(400).json({ 
        error: 'Missing tenant context. Please ensure you are logged in and have a valid tenant.' 
      });
    }
 
    // Get from database
    const result = await pool.query(
      `SELECT 
        w.*,
        c.name as customer_name
       FROM wordpress_sites w
       LEFT JOIN customers c ON w.customer_id = c.customer_id
       WHERE w.app_id = $1 AND w.tenant_id = $2`,
      [appId, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'WordPress site not found' });
    }
 
    // Optionally fetch live status from DigitalOcean
    const configResult = await pool.query(
      `SELECT api_token_encrypted FROM digitalocean_config WHERE tenant_id = $1 AND is_active = true`,
      [req.tenant.id]
    );
 
    if (configResult.rows.length > 0) {
      const doToken = configResult.rows[0].api_token_encrypted;
      
      try {
        const doResponse = await axios.get(
          `https://api.digitalocean.com/v2/apps/${appId}`,
          {
            headers: {
              'Authorization': `Bearer ${doToken}`
            }
          }
        );
 
        const app = doResponse.data.app;
 
        // Update database with latest info
        await pool.query(
          `UPDATE wordpress_sites 
           SET status = $1, url = $2, updated_at = CURRENT_TIMESTAMP
           WHERE app_id = $3`,
          [app.phase, app.default_ingress, appId]
        );
 
        result.rows[0].status = app.phase;
        result.rows[0].url = app.default_ingress;
      } catch (doError) {
        console.error('[WordPress] Error fetching app from DO:', doError.message);
        // Continue with database data if DO API fails
      }
    }
 
    res.json({ site: result.rows[0] });
  } catch (error) {
    console.error('[WordPress] Error fetching site:', error);
    res.status(500).json({ error: 'Failed to fetch WordPress site' });
  }
});
 
/**
 * @api {get} /api/wordpress/monitoring/latest Get latest monitoring data
 * @apiName GetLatestMonitoring
 * @apiGroup WordPress
 * @apiDescription Get the most recent monitoring report from all WordPress servers
 * @apiSuccess {object[]} reports Array of monitoring reports
 */
router.get('/monitoring/latest', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT 
                report_id,
                server_ip,
                server_hostname,
                report_data,
                received_at
            FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            ORDER BY received_at DESC
            LIMIT 10
        `);
 
        const reports = result.rows.map(row => ({
            ...row.report_data,
            report_id: row.report_id,
            received_at: row.received_at
        }));
 
        res.json({ reports });
    } catch (error) {
        console.error('[WordPress Monitoring] Error fetching reports:', error);
        res.status(500).json({ error: 'Failed to fetch monitoring data' });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/assign Assign site to customer
 * @apiName AssignWordPressSiteToCustomer
 * @apiGroup WordPress
 * @apiDescription Assign a WordPress site to a specific customer and add notes
 * @apiParam {string} domain WordPress site domain
 * @apiBody {number} customerId Customer ID to assign to
 * @apiBody {string} [notes] Optional notes about the site
 * @apiSuccess {boolean} success Operation success
 * @apiSuccess {string} message Success message
 */
router.post('/sites/:domain/assign', async (req, res) => {
    try {
        const { domain } = req.params;
        const { customerId, notes } = req.body;
        const tenantId = req.user.tenantId;
 
        // Get or create the WordPress site record
        const siteResult = await pool.query(`
            INSERT INTO wordpress_sites (tenant_id, domain, notes, updated_at)
            VALUES ($1, $2, $3, CURRENT_TIMESTAMP)
            ON CONFLICT (tenant_id, domain) DO UPDATE SET
                notes = $3,
                updated_at = CURRENT_TIMESTAMP
            RETURNING site_id
        `, [tenantId, domain, notes || null]);
 
        const siteId = siteResult.rows[0].site_id;
 
        // If customer_id provided, create or update customer association
        if (customerId) {
            // Update customer assignment (assuming we add a customer_id column)
            await pool.query(`
                UPDATE wordpress_sites
                SET notes = $1,
                    updated_at = CURRENT_TIMESTAMP
                WHERE site_id = $2
            `, [notes || null, siteId]);
 
            // You may want to create a separate wordpress_site_customers table for many-to-many
            // For now, we'll store it in notes or add a customer_id column
        }
 
        res.json({
            success: true,
            message: 'Site assignment updated successfully'
        });
    } catch (error) {
        console.error('[WordPress] Error assigning site:', error);
        res.status(500).json({ error: 'Failed to update site assignment' });
    }
});
 
/**
 * @api {get} /api/wordpress/sites/:domain Get site details
 * @apiName GetWordPressSiteDetails
 * @apiGroup WordPress
 * @apiDescription Get detailed information about a WordPress site including plugins, themes, and security issues
 * @apiParam {string} domain Site domain
 * @apiSuccess {object} site Site details with plugins, themes, security data
 */
router.get('/sites/:domain', async (req, res) => {
    try {
        const { domain } = req.params;
        
        // Get latest monitoring data for this site
        const result = await pool.query(`
            SELECT 
                report_id,
                server_ip,
                server_hostname,
                report_data,
                received_at
            FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            ORDER BY received_at DESC
            LIMIT 1
        `);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'No recent monitoring data found' });
        }
 
        const report = result.rows[0];
        const reportData = report.report_data;
        
        // Find the specific site
        const site = reportData.sites?.find(s => s.domain === domain);
        
        if (!site) {
            return res.status(404).json({ error: 'Site not found in monitoring data' });
        }
 
        // Get security scan data
        const securityResult = await pool.query(`
            SELECT 
                scan_id,
                malware_issues,
                php_in_uploads,
                suspicious_files,
                modified_core_files,
                scan_output,
                quarantined_files,
                scan_date
            FROM wordpress_security_scans
            WHERE site_id IN (
                SELECT site_id FROM wordpress_sites WHERE domain = $1
            )
            ORDER BY scan_date DESC
            LIMIT 1
        `, [domain]);
 
        // Combine base data
        const siteDetails = {
            ...site,
            server_ip: report.server_ip,
            server_hostname: report.server_hostname,
            last_updated: report.received_at,
            last_scan: reportData.last_scan,
            security: securityResult.rows.length > 0 ? securityResult.rows[0] : null,
            plugins: [],
            themes: [],
            core: null,
            admin_users: []
        };
 
        // Fetch detailed information via WordPress API Bridge
        try {
            const wordpressApiKey = process.env.WORDPRESS_API_KEY;
            const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
            
            if (!wordpressApiKey) {
                console.warn('[WordPress] WORDPRESS_API_KEY not configured, skipping detailed fetch');
            } else {
                console.log(`[WordPress] Fetching detailed info for ${domain} from API bridge`);
                
                const apiUrl = `http://${report.server_ip}:${wordpressApiPort}/api/site/${domain}/details`;
                const apiResponse = await axios.get(apiUrl, {
                    headers: {
                        'X-API-Key': wordpressApiKey
                    },
                    timeout: 15000
                });
 
                const details = apiResponse.data;
                siteDetails.plugins = details.plugins || [];
                siteDetails.themes = details.themes || [];
                siteDetails.core = details.core || null;
                siteDetails.admin_users = details.admin_users || [];
                siteDetails.site_url = details.site_url || `https://${domain}`;
                siteDetails.database = details.database || null;
            }
        } catch (apiError) {
            console.error('[WordPress] API fetch error:', apiError.message);
            // Continue with basic data even if API fetch fails
            siteDetails.api_error = apiError.message;
        }
 
        res.json({ site: siteDetails });
    } catch (error) {
        console.error('[WordPress] Error fetching site details:', error);
        res.status(500).json({ error: 'Failed to fetch site details' });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/login Generate auto-login URL
 * @apiName GenerateWordPressAutoLogin
 * @apiGroup WordPress
 * @apiDescription Generate a one-time auto-login URL for WordPress admin access
 * @apiParam {string} domain Site domain
 * @apiSuccess {string} loginUrl One-time login URL
 */
router.post('/sites/:domain/login', async (req, res) => {
    try {
        const { domain } = req.params;
        
        // Get site info from latest monitoring data
        const result = await pool.query(`
            SELECT 
                report_data
            FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            ORDER BY received_at DESC
            LIMIT 1
        `);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site not found' });
        }
 
        const reportData = result.rows[0].report_data;
        const site = reportData.sites?.find(s => s.domain === domain);
        
        if (!site) {
            return res.status(404).json({ error: 'Site not found in monitoring data' });
        }
 
        // Generate one-time login token (expires in 5 minutes)
        const loginToken = crypto.randomBytes(32).toString('hex');
        const expiresAt = new Date(Date.now() + 5 * 60 * 1000); // 5 minutes
 
        // Store the token
        await pool.query(`
            INSERT INTO wordpress_sites (tenant_id, domain, sso_token, sso_token_expires, updated_at)
            VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
            ON CONFLICT (tenant_id, domain) DO UPDATE SET
                sso_token = $3,
                sso_token_expires = $4,
                updated_at = CURRENT_TIMESTAMP
        `, [req.user.tenantId, domain, loginToken, expiresAt]);
 
        // Return the login URL (will be handled by a separate auto-login endpoint)
        const loginUrl = `https://${domain}/wp-admin/?auto_login=${loginToken}`;
 
        res.json({
            success: true,
            loginUrl,
            expiresAt,
            message: 'Auto-login URL generated (expires in 5 minutes)'
        });
    } catch (error) {
        console.error('[WordPress] Error generating login URL:', error);
        res.status(500).json({ error: 'Failed to generate login URL' });
    }
});
 
/**
 * @api {get} /api/wordpress/sites/:domain/users Get site users
 * @apiName GetWordPressSiteUsers
 * @apiGroup WordPress
 * @apiDescription Get all users and their roles for a WordPress site
 * @apiParam {string} domain Site domain
 * @apiSuccess {array} users List of users with roles
 */
router.get('/sites/:domain/users', async (req, res) => {
    try {
        const { domain } = req.params;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/users`;
 
        const apiResponse = await axios.get(apiUrl, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 30000
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Users fetch error:', error.message);
        res.status(500).json({ error: 'Failed to fetch users', message: error.message });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/plugin/:plugin/update Update plugin
 * @apiName UpdateWordPressPlugin
 * @apiGroup WordPress
 * @apiDescription Update a specific plugin on a WordPress site
 * @apiParam {string} domain Site domain
 * @apiParam {string} plugin Plugin slug
 * @apiSuccess {boolean} success Operation success
 */
router.post('/sites/:domain/plugin/:plugin/update', async (req, res) => {
    try {
        const { domain, plugin } = req.params;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/plugin/${plugin}/update`;
 
        const apiResponse = await axios.post(apiUrl, {}, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 30000
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Plugin update error:', error.message);
        res.status(500).json({ error: 'Failed to update plugin', message: error.message });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/theme/:theme/update Update theme
 * @apiName UpdateWordPressTheme
 * @apiGroup WordPress
 * @apiDescription Update a specific theme on a WordPress site
 * @apiParam {string} domain Site domain
 * @apiParam {string} theme Theme slug
 * @apiSuccess {boolean} success Operation success
 */
router.post('/sites/:domain/theme/:theme/update', async (req, res) => {
    try {
        const { domain, theme } = req.params;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/theme/${theme}/update`;
 
        const apiResponse = await axios.post(apiUrl, {}, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 30000
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Theme update error:', error.message);
        res.status(500).json({ error: 'Failed to update theme', message: error.message });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/core/update Update WordPress core
 * @apiName UpdateWordPressCore
 * @apiGroup WordPress
 * @apiDescription Update WordPress core on a site
 * @apiParam {string} domain Site domain
 * @apiBody {string} [version] Target version (optional)
 * @apiSuccess {boolean} success Operation success
 */
router.post('/sites/:domain/core/update', async (req, res) => {
    try {
        const { domain } = req.params;
        const { version } = req.body;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/core/update`;
 
        const apiResponse = await axios.post(apiUrl, { version }, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 300000 // 5 minutes for core updates
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Core update error:', error.message);
        res.status(500).json({ error: 'Failed to update WordPress core', message: error.message });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/clean Clean malware
 * @apiName CleanWordPressSiteThreats
 * @apiGroup WordPress
 * @apiDescription Remove malware and threats from a WordPress site
 * @apiParam {string} domain Site domain
 * @apiSuccess {boolean} success Operation success
 */
router.post('/sites/:domain/clean', async (req, res) => {
    try {
        const { domain } = req.params;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/clean`;
 
        const apiResponse = await axios.post(apiUrl, {}, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 60000
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Cleanup error:', error.message);
        res.status(500).json({ error: 'Failed to clean threats', message: error.message });
    }
});
 
/**
 * @api {get} /api/wordpress/sites/:domain/details Get detailed site information
 * @apiName GetWordPressSiteDetails
 * @apiGroup WordPress
 * @apiDescription Get detailed information about WordPress site including plugins, themes, and core version
 * @apiParam {string} domain Site domain
 * @apiSuccess {object} details Site details with plugins, themes, core info
 */
router.get('/sites/:domain/details', async (req, res) => {
    try {
        const { domain } = req.params;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/details`;
 
        const apiResponse = await axios.get(apiUrl, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 30000
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Site details error:', error.message);
        res.status(500).json({ error: 'Failed to fetch site details', message: error.message });
    }
});
 
/**
 * @api {post} /api/wordpress/sites/:domain/autologin Generate auto-login link
 * @apiName GenerateAutoLogin
 * @apiGroup WordPress
 * @apiDescription Generate a magic login link for WordPress admin access
 * @apiParam {string} domain Site domain
 * @apiBody {string} [username] Optional username (defaults to first admin)
 * @apiSuccess {string} login_url Magic login URL
 */
router.post('/sites/:domain/autologin', async (req, res) => {
    try {
        const { domain } = req.params;
        const { username } = req.body;
        const wordpressApiKey = process.env.WORDPRESS_API_KEY;
        const wordpressApiPort = process.env.WORDPRESS_API_PORT || '3100';
 
        if (!wordpressApiKey) {
            return res.status(503).json({ error: 'WordPress API not configured' });
        }
 
        // Get server IP from monitoring data
        const result = await pool.query(`
            SELECT server_ip FROM wordpress_monitoring_reports
            WHERE received_at > NOW() - INTERVAL '24 hours'
            AND report_data->'sites' @> jsonb_build_array(jsonb_build_object('domain', $1::text))
            ORDER BY received_at DESC LIMIT 1
        `, [domain]);
 
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Site server not found' });
        }
 
        const serverIp = result.rows[0].server_ip;
        const apiUrl = `http://${serverIp}:${wordpressApiPort}/api/site/${domain}/autologin`;
 
        const apiResponse = await axios.post(apiUrl, { username }, {
            headers: { 'X-API-Key': wordpressApiKey },
            timeout: 30000
        });
 
        res.json(apiResponse.data);
    } catch (error) {
        console.error('[WordPress] Auto-login error:', error.message);
        res.status(500).json({ error: 'Failed to generate auto-login link', message: error.message });
    }
});
 
module.exports = router;