hosting.js

Go to the documentation of this file.

/**
 * @file hosting.js
 * @module HostingRoutes
 * @description DigitalOcean hosting management API routes. Manages App Platform applications, Droplets (VMs), and managed databases through DigitalOcean API integration. Supports configuration management, resource synchronization, deployment operations, and multi-tenant isolation.
 * @see {@link ../services/digitalOceanService} for DigitalOcean API integration
 * @see {@link ../db} for database connection
 * @see {@link ../middleware/auth} for authentication middleware
 * @see {@link ../middleware/tenant} for tenant context utilities
 * @apiDefine HostingGroup Hosting
 * @apiGroup Hosting
 * @apiHeader {string} Authorization Bearer token required.
 * @apiHeader {string} X-Tenant-ID Tenant context header required.
 * @apiError (Error 401) Unauthorized Missing or invalid token.
 * @apiError (Error 403) Forbidden Tenant context missing or invalid.
 * @apiError (Error 404) NotFound Configuration or resource not found.
 * @apiError (Error 500) ServerError Internal server error or DigitalOcean API error.
 */
 
const express = require('express');
const router = express.Router();
const authenticateToken = require('../middleware/auth');
const { setTenantContext } = require('../middleware/tenant');
const DigitalOceanService = require('../services/digitalOceanService');
const pool = require('../db');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
// ===========================
// Configuration Management
// ===========================
 
/**
 * @api {get} /hosting/config Get DigitalOcean configuration
 * @apiName GetHostingConfig
 * @apiGroup Hosting
 * @apiDescription Retrieve DigitalOcean API configuration for the current tenant (without exposing API token).
 * @apiSuccess {object} config Configuration object.
 * @apiSuccess {string} config.config_id Configuration ID.
 * @apiSuccess {string} config.tenant_id Tenant ID.
 * @apiSuccess {string} config.team_id DigitalOcean team ID.
 * @apiSuccess {boolean} config.is_active Whether configuration is active.
 * @apiSuccess {Date} config.last_sync_at Last synchronization timestamp.
 * @apiError (Error 404) NotFound Configuration not found.
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/hosting/config
 */
router.get('/config', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT config_id, tenant_id, team_id, is_active, last_sync_at, created_at, updated_at
       FROM digitalocean_config
       WHERE tenant_id = $1`,
      [req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'DigitalOcean configuration not found' });
    }
 
    res.json({ config: result.rows[0] });
  } catch (error) {
    console.error('[Hosting] Error fetching config:', error);
    res.status(500).json({ error: 'Failed to fetch configuration' });
  }
});
 
/**
 * @api {put} /hosting/config Update DigitalOcean configuration
 * @apiName UpdateHostingConfig
 * @apiGroup Hosting
 * @apiDescription Update or create DigitalOcean API configuration for the current tenant. Stores API token and team ID for DigitalOcean resource management.
 * @apiParam {string} api_token DigitalOcean API token.
 * @apiParam {string} [team_id] DigitalOcean team ID (optional).
 * @apiSuccess {string} message Success confirmation.
 * @apiSuccess {object} config Saved configuration object.
 * @apiError (Error 400) BadRequest api_token is required.
 * @apiExample {curl} Example usage:
 *     curl -X PUT -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" -d '{"api_token":"dop_v1_abc123","team_id":"team-123"}' https://api.example.com/hosting/config
 */
router.put('/config', async (req, res) => {
  const { api_token, team_id } = req.body;
 
  if (!api_token) {
    return res.status(400).json({ error: 'API token is required' });
  }
 
  try {
    // TODO: Encrypt api_token before storing
    const encrypted_token = api_token; // Implement encryption
 
    const result = await pool.query(
      `INSERT INTO digitalocean_config (tenant_id, api_token_encrypted, team_id, is_active)
       VALUES ($1, $2, $3, true)
       ON CONFLICT (tenant_id)
       DO UPDATE SET
         api_token_encrypted = EXCLUDED.api_token_encrypted,
         team_id = EXCLUDED.team_id,
         is_active = true,
         updated_at = CURRENT_TIMESTAMP
       RETURNING config_id, tenant_id, team_id, is_active, created_at, updated_at`,
      [req.tenant.id, encrypted_token, team_id || null]
    );
 
    res.json({
      message: 'DigitalOcean configuration saved successfully',
      config: result.rows[0]
    });
  } catch (error) {
    console.error('[Hosting] Error saving config:', error);
    res.status(500).json({ error: 'Failed to save configuration' });
  }
});
 
/**
 * @api {post} /hosting/config/test Test DigitalOcean API connection
 * @apiName TestHostingConnection
 * @apiGroup Hosting
 * @apiDescription Test the DigitalOcean API connection with current tenant's configuration. Verifies API token validity and connectivity.
 * @apiSuccess {boolean} success Connection test result.
 * @apiSuccess {string} [message] Status message.
 * @apiError (Error 500) ServerError Connection test failed or configuration missing.
 * @apiExample {curl} Example usage:
 *     curl -X POST -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/hosting/config/test
 */
router.post('/config/test', async (req, res) => {
  try {
    const doService = new DigitalOceanService(req.tenant.id);
    const result = await doService.testConnection();
    res.json(result);
  } catch (error) {
    console.error('[Hosting] Connection test failed:', error);
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});
 
/**
 * @api {get} /hosting/projects List all DigitalOcean projects
 * @apiName ListProjects
 * @apiGroup Hosting
 * @apiDescription Retrieve all projects from DigitalOcean for the current tenant. Projects organize and group DigitalOcean resources.
 * @apiSuccess {object[]} projects Array of project objects from DigitalOcean.
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/hosting/projects
 */
router.get('/projects', async (req, res) => {
  try {
    const doService = new DigitalOceanService(req.tenant.id);
    const projects = await doService.listProjects();
    res.json({ projects });
  } catch (error) {
    console.error('[Hosting] Error fetching projects:', error);
    res.status(500).json({ error: error.message });
  }
});
 
// ===========================
// Apps Management
// ===========================
 
/**
 * @api {get} /hosting/apps List all hosting apps for tenant
 * @apiName ListHostingApps
 * @apiGroup Hosting
 * @apiDescription Retrieve all App Platform applications for the current tenant with customer and contract details. Root tenant sees all apps; other tenants see only assigned apps. Excludes deleted apps.
 * @apiSuccess {object[]} apps Array of hosting app objects.
 * @apiSuccess {string} apps.app_id App ID.
 * @apiSuccess {string} apps.app_name App name.
 * @apiSuccess {string} apps.do_app_id DigitalOcean app ID.
 * @apiSuccess {string} apps.status App status (active, building, error, etc.).
 * @apiSuccess {string} apps.customer_name Customer name.
 * @apiSuccess {string} apps.contract_name Contract title.
 * @apiSuccess {Date} lastSync Last synchronization timestamp.
 * @apiSuccess {string} ttl Cache TTL ("5 minutes").
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/hosting/apps
 */
router.get('/apps', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    // Get last sync time
    const syncResult = await pool.query(
      `SELECT last_sync_at FROM digitalocean_config WHERE tenant_id = $1`,
      [req.tenant.id]
    );
    const lastSync = syncResult.rows[0]?.last_sync_at;
    
    // Root tenant sees all apps, other tenants only see their assigned apps
    const isRootTenant = req.tenant.id === '00000000-0000-0000-0000-000000000001';
    const result = await pool.query(
      `SELECT 
        ha.*,
        c.name as customer_name,
        ct.title as contract_name
       FROM hosting_apps ha
       LEFT JOIN customers c ON ha.customer_id = c.customer_id
       LEFT JOIN contracts ct ON ha.contract_id = ct.contract_id
       WHERE ha.status != 'deleted' ${isRootTenant ? '' : 'AND (ha.tenant_id = $1 OR ha.assigned_tenant_id = $1)'}
       ORDER BY ha.created_at DESC`,
      isRootTenant ? [] : [req.tenant.id]
    );
 
    res.json({ 
      apps: result.rows, 
      lastSync,
      ttl: '5 minutes' 
    });
  } catch (error) {
    console.error('[Hosting] Error fetching apps:', error);
    // Return empty array if table doesn't exist yet
    if (error.code === '42P01') { // undefined_table
      return res.json({ apps: [] });
    }
    res.status(500).json({ error: 'Failed to fetch apps' });
  }
});
 
/**
 * @api {get} /api/hosting/apps/:id Get app details
 * @apiName GetHostingApp
 * @apiGroup Hosting
 * @apiDescription Get detailed information about a specific DigitalOcean app, including
 * optional WordPress site details if applicable. Requires tenant context.
 * @apiParam {UUID} id App ID (internal database ID)
 * @apiSuccess {object} app App details object
 * @apiSuccess {UUID} app.app_id Internal app ID
 * @apiSuccess {string} app.do_app_id DigitalOcean app ID
 * @apiSuccess {string} app.app_name App name
 * @apiSuccess {string} app.app_type App type (nodejs, static, wordpress)
 * @apiSuccess {string} app.status App status (active, pending, deleted)
 * @apiSuccess {string} app.region Region slug
 * @apiSuccess {string} app.live_url Live URL
 * @apiSuccess {string} app.customer_name Customer name (joined)
 * @apiSuccess {string} app.contract_name Contract name (joined)
 * @apiSuccess {Object} [app.wordpress] WordPress-specific details (if applicable)
 * @apiSuccess {UUID} app.wordpress.site_id WordPress site ID
 * @apiSuccess {string} app.wordpress.db_name Database name
 * @apiSuccess {string} app.wordpress.wp_admin_user WordPress admin username
 * @apiError {string} error="App not found" (404) App not found for tenant
 * @apiError {string} error="Failed to fetch app" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/apps/123e4567-e89b-12d3-a456-426614174000 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response (with WordPress details):
 *   HTTP/1.1 200 OK
 *   {
 *     "app": {
 *       "app_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "do_app_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *       "app_name": "wordpress-example-site",
 *       "app_type": "wordpress",
 *       "status": "active",
 *       "region": "nyc3",
 *       "live_url": "https://wordpress-example-site-abc123.ondigitalocean.app",
 *       "customer_name": "Acme Corp",
 *       "contract_name": "Managed Hosting Plan",
 *       "wordpress": {
 *         "site_id": "456e7890-ab12-34cd-5678-901234567890",
 *         "db_name": "wordpress_db",
 *         "spaces_path": "wp-content/uploads",
 *         "wp_admin_user": "admin",
 *         "wp_admin_email": "admin@example.com"
 *       }
 *     }
 *   }
 */
router.get('/apps/:id', async (req, res) => {
  const { id } = req.params;
 
  try {
    const result = await pool.query(
      `SELECT 
        ha.*,
        c.name as customer_name,
        ct.title as contract_name
       FROM hosting_apps ha
       LEFT JOIN customers c ON ha.customer_id = c.customer_id
       LEFT JOIN contracts ct ON ha.contract_id = ct.contract_id
       WHERE ha.app_id = $1 AND (ha.tenant_id = $2 OR ha.assigned_tenant_id = $2)`,
      [id, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'App not found' });
    }
 
    const app = result.rows[0];
 
    // If this is a WordPress app, fetch WordPress-specific details
    if (app.app_name && app.app_name.startsWith('wordpress-')) {
      try {
        const wpResult = await pool.query(
          `SELECT 
            site_id,
            db_name,
            spaces_path,
            wp_admin_user,
            wp_admin_password,
            wp_admin_email
           FROM wordpress_sites
           WHERE app_id = $1 AND tenant_id = $2`,
          [app.do_app_id, req.tenant.id]
        );
 
        if (wpResult.rows.length > 0) {
          app.wordpress = wpResult.rows[0];
        }
      } catch (wpError) {
        console.error('[Hosting] Error fetching WordPress details:', wpError);
        // Continue without WordPress details if query fails
      }
    }
 
    res.json({ app });
  } catch (error) {
    console.error('[Hosting] Error fetching app:', error);
    res.status(500).json({ error: 'Failed to fetch app' });
  }
});
 
/**
 * @api {put} /api/hosting/apps/:id Update app assignments
 * @apiName UpdateHostingApp
 * @apiGroup Hosting
 * @apiDescription Update customer, contract, and tenant assignments for a hosting app.
 * Used for organizing apps in multi-tenant environment.
 * @apiParam {UUID} id App ID (internal database ID)
 * @apiParam {UUID} [customer_id] Customer ID to assign app to
 * @apiParam {UUID} [contract_id] Contract ID to link app with
 * @apiParam {UUID} [assigned_tenant_id] Tenant ID to assign app to
 * @apiSuccess {boolean} success=true Operation successful
 * @apiSuccess {string} message="App updated successfully"
 * @apiSuccess {object} app Updated app object
 * @apiError {string} error="App not found" (404) App not found for tenant
 * @apiError {string} error="Failed to update app" (500) Database update failed
 * @apiExample {curl} Example:
 *   curl -X PUT http://localhost:3000/api/hosting/apps/123e4567-e89b-12d3-a456-426614174000 \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{
 *       "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *       "contract_id": "abc12345-de67-89ab-cdef-0123456789ab"
 *     }'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "App updated successfully",
 *     "app": {
 *       "app_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *       "contract_id": "abc12345-de67-89ab-cdef-0123456789ab",
 *       "assigned_tenant_id": null,
 *       "updated_at": "2026-03-12T10:30:00.000Z"
 *     }
 *   }
 */
router.put('/apps/:id', async (req, res) => {
  const { id } = req.params;
  const { customer_id, contract_id, assigned_tenant_id } = req.body;
 
  try {
    // Update the app
    const result = await pool.query(
      `UPDATE hosting_apps 
       SET customer_id = $1, 
           contract_id = $2, 
           assigned_tenant_id = $3,
           updated_at = CURRENT_TIMESTAMP
       WHERE app_id = $4 AND tenant_id = $5
       RETURNING *`,
      [customer_id || null, contract_id || null, assigned_tenant_id || null, id, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'App not found' });
    }
 
    res.json({ 
      success: true, 
      message: 'App updated successfully', 
      app: result.rows[0] 
    });
  } catch (error) {
    console.error('[Hosting] Error updating app:', error);
    res.status(500).json({ error: 'Failed to update app' });
  }
});
 
/**
 * @api {post} /api/hosting/sync Sync DigitalOcean resources
 * @apiName SyncHostingResources
 * @apiGroup Hosting
 * @apiDescription Synchronize all DigitalOcean resources (apps, databases, droplets) to local
 * database with current metrics. Called manually via UI button or automatically by Redis worker
 * every 30 minutes. Marks resources as deleted if removed from DigitalOcean.
 * @apiSuccess {string} message="DigitalOcean resources synced successfully"
 * @apiSuccess {object} summary Resource count summary from DigitalOcean API
 * @apiSuccess {number} summary.apps Total apps in DO
 * @apiSuccess {number} summary.databases Total databases in DO
 * @apiSuccess {number} summary.droplets Total droplets in DO
 * @apiSuccess {object} syncStats Statistics of synced resources
 * @apiSuccess {number} syncStats.apps Apps synced to database
 * @apiSuccess {number} syncStats.databases Databases synced to database
 * @apiSuccess {number} syncStats.droplets Droplets synced to database
 * @apiSuccess {number} syncStats.metrics Metric API calls made
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error="DigitalOcean API token not configured" (400) DO_API_TOKEN missing
 * @apiError {string} error (500) Sync operation failed
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/hosting/sync \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "DigitalOcean resources synced successfully",
 *     "summary": {
 *       "apps": 15,
 *       "databases": 3,
 *       "droplets": 8
 *     },
 *     "syncStats": {
 *       "apps": 15,
 *       "databases": 3,
 *       "droplets": 8,
 *       "metrics": 11
 *     }
 *   }
 */
router.post('/sync', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    const doService = new DigitalOceanService(req.tenant.id);
    const resources = await doService.getAllResources();
 
    let syncStats = {
      apps: 0,
      databases: 0,
      droplets: 0,
      metrics: 0
    };
 
    // =====================
    // Sync Apps
    // =====================
    const activeAppIds = resources.apps.map(app => app.id);
 
    for (const app of resources.apps) {
      await pool.query(
        `INSERT INTO hosting_apps (
          tenant_id, do_app_id, app_name, app_type, status, region,
          live_url, default_domain, custom_domains, metadata, 
          created_at, updated_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        ON CONFLICT (do_app_id)
        DO UPDATE SET
          app_name = EXCLUDED.app_name,
          status = EXCLUDED.status,
          region = EXCLUDED.region,
          live_url = EXCLUDED.live_url,
          default_domain = EXCLUDED.default_domain,
          custom_domains = EXCLUDED.custom_domains,
          metadata = EXCLUDED.metadata,
          updated_at = CURRENT_TIMESTAMP`,
        [
          req.tenant.id,
          app.id,
          app.spec?.name || 'Unknown App',
          app.spec?.services?.[0]?.source_dir ? 'nodejs' : 'static',
          app.live_url_base ? 'active' : 'pending',
          app.region,
          app.live_url,
          app.default_ingress,
          JSON.stringify(app.spec?.domains || []),
          JSON.stringify(app)
        ]
      );
      syncStats.apps++;
    }
 
    // Mark apps as deleted if they no longer exist in DO
    if (activeAppIds.length > 0) {
      await pool.query(
        `UPDATE hosting_apps 
         SET status = 'deleted', updated_at = CURRENT_TIMESTAMP
         WHERE tenant_id = $1 
         AND do_app_id NOT IN (${activeAppIds.map((_, i) => `$${i + 2}`).join(', ')})
         AND status != 'deleted'`,
        [req.tenant.id, ...activeAppIds]
      );
    }
 
    // =====================
    // Sync Databases + Metrics
    // =====================
    const databases = resources.databases || [];
    const activeDatabaseIds = databases.map(db => db.id);
 
    for (const db of databases) {
      // Fetch metrics for this database
      let metrics = null;
      try {
        metrics = await doService.getDatabaseMetrics(db.id);
        syncStats.metrics++;
      } catch (err) {
        console.error(`[Hosting] Failed to fetch metrics for database ${db.id}:`, err.message);
      }
 
      await pool.query(
        `INSERT INTO hosting_databases (
          tenant_id, do_database_id, database_name, engine, version, status,
          region, size, num_nodes, connection_host, connection_port,
          connection_database, connection_user, tags,
          cpu_count, memory_mb, disk_gb, metrics_last_updated,
          metadata, created_at, updated_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        ON CONFLICT (do_database_id)
        DO UPDATE SET
          database_name = EXCLUDED.database_name,
          status = EXCLUDED.status,
          version = EXCLUDED.version,
          size = EXCLUDED.size,
          num_nodes = EXCLUDED.num_nodes,
          connection_host = EXCLUDED.connection_host,
          connection_port = EXCLUDED.connection_port,
          cpu_count = EXCLUDED.cpu_count,
          memory_mb = EXCLUDED.memory_mb,
          disk_gb = EXCLUDED.disk_gb,
          metrics_last_updated = EXCLUDED.metrics_last_updated,
          metadata = EXCLUDED.metadata,
          updated_at = CURRENT_TIMESTAMP`,
        [
          req.tenant.id,
          db.id,
          db.name,
          db.engine,
          db.version,
          db.status,
          db.region,
          db.size,
          db.num_nodes || 1,
          db.connection?.host || null,
          db.connection?.port || null,
          db.connection?.database || null,
          db.connection?.user || null,
          db.tags || [],
          metrics?.cpu_count || null,
          metrics?.memory_mb || null,
          metrics?.disk_gb || null,
          metrics ? new Date() : null,
          JSON.stringify(db)
        ]
      );
      
      // Store metrics history for trends
      if (metrics) {
        await pool.query(
          `INSERT INTO hosting_metrics_history (
            resource_type, resource_id, tenant_id, cpu_value, memory_value, disk_value
          ) VALUES ($1, $2, $3, $4, $5, $6)`,
          ['database', db.id, req.tenant.id, metrics.cpu_count, metrics.memory_mb, metrics.disk_gb]
        );
      }
      
      syncStats.databases++;
    }
 
    // Mark databases as deleted if they no longer exist
    if (activeDatabaseIds.length > 0) {
      await pool.query(
        `UPDATE hosting_databases 
         SET status = 'deleted', updated_at = CURRENT_TIMESTAMP
         WHERE tenant_id = $1 
         AND do_database_id NOT IN (${activeDatabaseIds.map((_, i) => `$${i + 2}`).join(', ')})
         AND status != 'deleted'`,
        [req.tenant.id, ...activeDatabaseIds]
      );
    }
 
    // =====================
    // Sync Droplets + Metrics
    // =====================
    const droplets = resources.droplets || [];
    const activeDropletIds = droplets.map(d => d.id.toString());
 
    for (const droplet of droplets) {
      // Fetch CPU metrics for this droplet (last hour average)
      let cpuMetrics = null;
      try {
        cpuMetrics = await doService.getDropletMetrics(droplet.id, 'cpu');
        syncStats.metrics++;
        
        // Calculate average CPU from last hour
        let avgCpu = null;
        if (cpuMetrics?.data?.result?.[0]?.values) {
          const values = cpuMetrics.data.result[0].values;
          const sum = values.reduce((acc, [, value]) => acc + parseFloat(value), 0);
          avgCpu = values.length > 0 ? (sum / values.length).toFixed(2) : null;
        }
 
        await pool.query(
          `INSERT INTO hosting_droplets (
            tenant_id, do_droplet_id, droplet_name, status, region, size,
            ip_address, ipv6_address, vcpus, memory, disk, image, tags,
            cpu_usage_percent, metrics_last_updated,
            metadata, created_at, updated_at
          )
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
          ON CONFLICT (do_droplet_id)
          DO UPDATE SET
            droplet_name = EXCLUDED.droplet_name,
            status = EXCLUDED.status,
            size = EXCLUDED.size,
            ip_address = EXCLUDED.ip_address,
            vcpus = EXCLUDED.vcpus,
            memory = EXCLUDED.memory,
            disk = EXCLUDED.disk,
            cpu_usage_percent = EXCLUDED.cpu_usage_percent,
            metrics_last_updated = EXCLUDED.metrics_last_updated,
            metadata = EXCLUDED.metadata,
            updated_at = CURRENT_TIMESTAMP`,
          [
            req.tenant.id,
            droplet.id.toString(),
            droplet.name,
            droplet.status,
            droplet.region.slug,
            droplet.size_slug,
            droplet.networks?.v4?.[0]?.ip_address || null,
            droplet.networks?.v6?.[0]?.ip_address || null,
            droplet.vcpus,
            droplet.memory,
            droplet.disk,
            droplet.image?.slug || droplet.image?.name || null,
            droplet.tags || [],
            avgCpu,
            new Date(),
            JSON.stringify(droplet)
          ]
        );
        
        // Store metrics history
        if (avgCpu) {
          await pool.query(
            `INSERT INTO hosting_metrics_history (
              resource_type, resource_id, tenant_id, cpu_value
            ) VALUES ($1, $2, $3, $4)`,
            ['droplet', droplet.id.toString(), req.tenant.id, avgCpu]
          );
        }
        
      } catch (err) {
        console.error(`[Hosting] Failed to fetch metrics for droplet ${droplet.id}:`, err.message);
        
        // Still save droplet without metrics
        await pool.query(
          `INSERT INTO hosting_droplets (
            tenant_id, do_droplet_id, droplet_name, status, region, size,
            ip_address, ipv6_address, vcpus, memory, disk, image, tags,
            metadata, created_at, updated_at
          )
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
          ON CONFLICT (do_droplet_id)
          DO UPDATE SET
            droplet_name = EXCLUDED.droplet_name,
            status = EXCLUDED.status,
            metadata = EXCLUDED.metadata,
            updated_at = CURRENT_TIMESTAMP`,
          [
            req.tenant.id,
            droplet.id.toString(),
            droplet.name,
            droplet.status,
            droplet.region.slug,
            droplet.size_slug,
            droplet.networks?.v4?.[0]?.ip_address || null,
            droplet.networks?.v6?.[0]?.ip_address || null,
            droplet.vcpus,
            droplet.memory,
            droplet.disk,
            droplet.image?.slug || droplet.image?.name || null,
            droplet.tags || [],
            JSON.stringify(droplet)
          ]
        );
      }
      
      syncStats.droplets++;
    }
 
    // Mark droplets as deleted if they no longer exist
    if (activeDropletIds.length > 0) {
      await pool.query(
        `UPDATE hosting_droplets 
         SET status = 'deleted', updated_at = CURRENT_TIMESTAMP
         WHERE tenant_id = $1 
         AND do_droplet_id NOT IN (${activeDropletIds.map((_, i) => `$${i + 2}`).join(', ')})
         AND status != 'deleted'`,
        [req.tenant.id, ...activeDropletIds]
      );
    }
 
    // Update last sync time
    await pool.query(
      `INSERT INTO digitalocean_config (tenant_id, last_sync_at)
       VALUES ($1, CURRENT_TIMESTAMP)
       ON CONFLICT (tenant_id)
       DO UPDATE SET last_sync_at = CURRENT_TIMESTAMP`,
      [req.tenant.id]
    );
 
    res.json({
      message: 'DigitalOcean resources synced successfully',
      summary: resources.summary,
      syncStats
    });
  } catch (error) {
    console.error('[Hosting] Error syncing resources:', error);
    // Check if it's a missing config error
    if (error.message && error.message.includes('configuration not found')) {
      return res.status(400).json({ 
        error: 'DigitalOcean API token not configured. Please add DO_API_TOKEN to backend environment variables.' 
      });
    }
    res.status(500).json({ error: error.message || 'Failed to sync resources' });
  }
});
 
// ===========================
// Live DO API Queries
// ===========================
 
/**
 * @api {get} /api/hosting/do/apps Get live DO apps
 * @apiName GetLiveDigitalOceanApps
 * @apiGroup Hosting
 * @apiDescription Query DigitalOcean API directly for current app list (live, no cache).
 * Use this endpoint sparingly - prefer cached GET /api/hosting/apps endpoint.
 * @apiSuccess {Array} apps Array of app objects from DigitalOcean API
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) DigitalOcean API request failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/do/apps \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "apps": [
 *       {
 *         "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *         "spec": {
 *           "name": "wordpress-example",
 *           "region": "nyc3"
 *         },
 *         "live_url": "https://wordpress-example-abc123.ondigitalocean.app",
 *         "created_at": "2026-01-15T08:00:00Z"
 *       }
 *     ]
 *   }
 */
router.get('/do/apps', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    const doService = new DigitalOceanService(req.tenant.id);
    const apps = await doService.listApps();
    res.json({ apps });
  } catch (error) {
    console.error('[Hosting] Error fetching DO apps:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {get} /api/hosting/droplets List droplets
 * @apiName ListHostingDroplets
 * @apiGroup Hosting
 * @apiDescription Get all DigitalOcean Droplets (VMs) from cached database. Root tenant sees
 * all droplets, other tenants only see their own. Synced every 5 minutes via background worker.
 * @apiSuccess {Array} droplets Array of droplet objects with customer details
 * @apiSuccess {DateTime} lastSync Last successful sync timestamp
 * @apiSuccess {string} ttl="5 minutes" Cache TTL
 * @apiSuccess {UUID} droplets.droplet_id Internal droplet ID
 * @apiSuccess {string} droplets.do_droplet_id DigitalOcean droplet ID
 * @apiSuccess {string} droplets.droplet_name Droplet name
 * @apiSuccess {string} droplets.status Status (active, off, new, deleted)
 * @apiSuccess {string} droplets.region Region slug
 * @apiSuccess {string} droplets.size Size slug (s-1vcpu-1gb, etc)
 * @apiSuccess {string} droplets.ip_address Primary IPv4 address
 * @apiSuccess {number} droplets.vcpus Number of vCPUs
 * @apiSuccess {number} droplets.memory RAM in MB
 * @apiSuccess {number} droplets.disk Disk size in GB
 * @apiSuccess {number} [droplets.cpu_usage_percent] Average CPU usage from last hour
 * @apiSuccess {string} droplets.customer_name Customer name (joined)
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error="Failed to fetch droplets" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/droplets \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "droplets": [
 *       {
 *         "droplet_id": "123e4567-e89b-12d3-a456-426614174000",
 *         "do_droplet_id": "123456789",
 *         "droplet_name": "web-server-01",
 *         "status": "active",
 *         "region": "nyc3",
 *         "size": "s-2vcpu-4gb",
 *         "ip_address": "192.0.2.100",
 *         "vcpus": 2,
 *         "memory": 4096,
 *         "disk": 80,
 *         "cpu_usage_percent": 35.5,
 *         "customer_name": "Acme Corp"
 *       }
 *     ],
 *     "lastSync": "2026-03-12T10:25:00.000Z",
 *     "ttl": "5 minutes"
 *   }
 */
router.get('/droplets', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    // Get last sync time
    const syncResult = await pool.query(
      `SELECT last_sync_at FROM digitalocean_config WHERE tenant_id = $1`,
      [req.tenant.id]
    );
    const lastSync = syncResult.rows[0]?.last_sync_at;
    
    // Root tenant sees all droplets, other tenants only see their own
    const isRootTenant = req.tenant.id === '00000000-0000-0000-0000-000000000001';
    const result = await pool.query(
      `SELECT 
        hd.*,
        c.name as customer_name
       FROM hosting_droplets hd
       LEFT JOIN customers c ON hd.customer_id = c.customer_id
       ${isRootTenant ? '' : 'WHERE hd.tenant_id = $1'}
       ORDER BY hd.created_at DESC`,
      isRootTenant ? [] : [req.tenant.id]
    );
 
    res.json({ 
      droplets: result.rows,
      lastSync,
      ttl: '5 minutes'
    });
  } catch (error) {
    console.error('[Hosting] Error fetching droplets:', error);
    if (error.code === '42P01') {
      return res.json({ droplets: [], lastSync: null, ttl: '5 minutes' });
    }
    res.status(500).json({ error: 'Failed to fetch droplets' });
  }
});
 
/**
 * @api {get} /api/hosting/databases List databases
 * @apiName ListHostingDatabases
 * @apiGroup Hosting
 * @apiDescription Get all DigitalOcean managed databases from cached database. Root tenant
 * sees all databases, other tenants only see their own. Synced every 5 minutes.
 * @apiSuccess {Array} databases Array of database objects with customer details
 * @apiSuccess {DateTime} lastSync Last successful sync timestamp
 * @apiSuccess {string} ttl="5 minutes" Cache TTL
 * @apiSuccess {UUID} databases.database_id Internal database ID
 * @apiSuccess {string} databases.do_database_id DigitalOcean database cluster ID
 * @apiSuccess {string} databases.database_name Database cluster name
 * @apiSuccess {string} databases.engine Engine type (pg, mysql, redis, mongodb)
 * @apiSuccess {string} databases.version Engine version
 * @apiSuccess {string} databases.status Status (online, creating, deleted)
 * @apiSuccess {string} databases.region Region slug
 * @apiSuccess {string} databases.size Size slug (db-s-1vcpu-1gb, etc)
 * @apiSuccess {number} databases.num_nodes Number of nodes in cluster
 * @apiSuccess {string} databases.connection_host Connection hostname
 * @apiSuccess {number} databases.connection_port Connection port
 * @apiSuccess {number} [databases.cpu_count] CPU count from metrics
 * @apiSuccess {number} [databases.memory_mb] Memory in MB from metrics
 * @apiSuccess {number} [databases.disk_gb] Disk usage in GB from metrics
 * @apiSuccess {string} databases.customer_name Customer name (joined)
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error="Failed to fetch databases" (500) Database query failed
 * 
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/databases \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * 
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "databases": [
 *       {
 *         "database_id": "123e4567-e89b-12d3-a456-426614174000",
 *         "do_database_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *         "database_name": "production-pg",
 *         "engine": "pg",
 *         "version": "15",
 *         "status": "online",
 *         "region": "nyc3",
 *         "size": "db-s-2vcpu-4gb",
 *         "num_nodes": 1,
 *         "connection_host": "production-pg-do-user-123-0.b.db.ondigitalocean.com",
 *         "connection_port": 25060,
 *         "cpu_count": 2,
 *         "memory_mb": 3845,
 *         "disk_gb": 12.5,
 *         "customer_name": "Acme Corp"
 *       }
 *     ],
 *     "lastSync": "2026-03-12T10:25:00.000Z",
 *     "ttl": "5 minutes"
 *   }
 */
router.get('/databases', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    // Get last sync time
    const syncResult = await pool.query(
      `SELECT last_sync_at FROM digitalocean_config WHERE tenant_id = $1`,
      [req.tenant.id]
    );
    const lastSync = syncResult.rows[0]?.last_sync_at;
    
    // Root tenant sees all databases, other tenants only see their own
    const isRootTenant = req.tenant.id === '00000000-0000-0000-0000-000000000001';
    const result = await pool.query(
      `SELECT 
        hdb.*,
        c.name as customer_name
       FROM hosting_databases hdb
       LEFT JOIN customers c ON hdb.customer_id = c.customer_id
       ${isRootTenant ? '' : 'WHERE hdb.tenant_id = $1'}
       ORDER BY hdb.created_at DESC`,
      isRootTenant ? [] : [req.tenant.id]
    );
 
    res.json({ 
      databases: result.rows,
      lastSync,
      ttl: '5 minutes'
    });
  } catch (error) {
    console.error('[Hosting] Error fetching databases:', error);
    if (error.code === '42P01') {
      return res.json({ databases: [], lastSync: null, ttl: '5 minutes' });
    }
    res.status(500).json({ error: 'Failed to fetch databases' });
  }
});
 
/**
 * @api {get} /api/hosting/databases/:id Get database details
 * @apiName GetHostingDatabase
 * @apiGroup Hosting
 * @apiDescription Get detailed information about a specific DigitalOcean managed database
 * cluster including customer and metrics.
 * @apiParam {UUID} id Database ID (internal database ID)
 * @apiSuccess {object} database Database details object
 * @apiSuccess {UUID} database.database_id Internal database ID
 * @apiSuccess {string} database.do_database_id DigitalOcean database cluster ID
 * @apiSuccess {string} database.database_name Database cluster name
 * @apiSuccess {string} database.engine Engine type (pg, mysql, redis, mongodb)
 * @apiSuccess {string} database.version Engine version
 * @apiSuccess {string} database.status Status
 * @apiSuccess {string} database.connection_host Connection hostname
 * @apiSuccess {number} database.connection_port Connection port
 * @apiSuccess {string} database.connection_user Connection username
 * @apiSuccess {number} [database.cpu_count] CPU count from metrics
 * @apiSuccess {number} [database.memory_mb] Memory in MB from metrics
 * @apiSuccess {number} [database.disk_gb] Disk usage in GB from metrics
 * @apiSuccess {string} database.customer_name Customer name (joined)
 * @apiError {string} error="Database not found" (404) Database not found for tenant
 * @apiError {string} error="Failed to fetch database" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/databases/123e4567-e89b-12d3-a456-426614174000 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "database": {
 *       "database_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "do_database_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *       "database_name": "production-pg",
 *       "engine": "pg",
 *       "version": "15",
 *       "status": "online",
 *       "connection_host": "production-pg-do-user-123-0.b.db.ondigitalocean.com",
 *       "connection_port": 25060,
 *       "connection_user": "doadmin",
 *       "cpu_count": 2,
 *       "memory_mb": 3845,
 *       "disk_gb": 12.5,
 *       "customer_name": "Acme Corp"
 *     }
 *   }
 */
router.get('/databases/:id', async (req, res) => {
  const { id } = req.params;
 
  try {
    const result = await pool.query(
      `SELECT 
        hdb.*,
        c.name as customer_name
       FROM hosting_databases hdb
       LEFT JOIN customers c ON hdb.customer_id = c.customer_id
       WHERE hdb.database_id = $1 AND hdb.tenant_id = $2`,
      [id, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Database not found' });
    }
 
    const database = result.rows[0];
    res.json({ database });
  } catch (error) {
    console.error('[Hosting] Error fetching database:', error);
    res.status(500).json({ error: 'Failed to fetch database' });
  }
});
 
/**
 * @api {put} /api/hosting/databases/:id Update database assignments
 * @apiName UpdateHostingDatabase
 * @apiGroup Hosting
 * @apiDescription Update customer, contract, and tenant assignments for a managed database.
 * Used for organizing databases in multi-tenant environment.
 * @apiParam {UUID} id Database ID (internal database ID)
 * @apiParam {UUID} [customer_id] Customer ID to assign database to
 * @apiParam {UUID} [contract_id] Contract ID to link database with
 * @apiParam {UUID} [assigned_tenant_id] Tenant ID to assign database to
 * @apiSuccess {boolean} success=true Operation successful
 * @apiSuccess {string} message="Database updated successfully"
 * @apiSuccess {object} database Updated database object
 * @apiError {string} error="Database not found" (404) Database not found for tenant
 * @apiError {string} error="Failed to update database" (500) Database update failed
 * @apiExample {curl} Example:
 *   curl -X PUT http://localhost:3000/api/hosting/databases/123e4567-e89b-12d3-a456-426614174000 \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{
 *       "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *       "contract_id": "abc12345-de67-89ab-cdef-0123456789ab"
 *     }'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "Database updated successfully",
 *     "database": {
 *       "database_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *       "contract_id": "abc12345-de67-89ab-cdef-0123456789ab",
 *       "updated_at": "2026-03-12T10:30:00.000Z"
 *     }
 *   }
 */
router.put('/databases/:id', async (req, res) => {
  const { id } = req.params;
  const { customer_id, contract_id, assigned_tenant_id } = req.body;
 
  try {
    // Update the database
    const result = await pool.query(
      `UPDATE hosting_databases 
       SET customer_id = $1, 
           contract_id = $2, 
           assigned_tenant_id = $3,
           updated_at = CURRENT_TIMESTAMP
       WHERE database_id = $4 AND tenant_id = $5
       RETURNING *`,
      [customer_id || null, contract_id || null, assigned_tenant_id || null, id, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Database not found' });
    }
 
    res.json({ 
      success: true, 
      message: 'Database updated successfully', 
      database: result.rows[0] 
    });
  } catch (error) {
    console.error('[Hosting] Error updating database:', error);
    res.status(500).json({ error: 'Failed to update database' });
  }
});
 
/**
 * @api {get} /api/hosting/droplets/:id Get droplet details
 * @apiName GetHostingDroplet
 * @apiGroup Hosting
 * @apiDescription Get detailed information about a specific DigitalOcean Droplet (VM)
 * including customer assignment.
 * @apiParam {UUID} id Droplet ID (internal database ID)
 * @apiSuccess {object} droplet Droplet details object
 * @apiSuccess {UUID} droplet.droplet_id Internal droplet ID
 * @apiSuccess {string} droplet.do_droplet_id DigitalOcean droplet ID
 * @apiSuccess {string} droplet.droplet_name Droplet name
 * @apiSuccess {string} droplet.status Status (active, off, new)
 * @apiSuccess {string} droplet.region Region slug
 * @apiSuccess {string} droplet.size Size slug
 * @apiSuccess {string} droplet.ip_address Primary IPv4 address
 * @apiSuccess {string} [droplet.ipv6_address] IPv6 address
 * @apiSuccess {number} droplet.vcpus Number of vCPUs
 * @apiSuccess {number} droplet.memory RAM in MB
 * @apiSuccess {number} droplet.disk Disk size in GB
 * @apiSuccess {string} droplet.image Image slug or name
 * @apiSuccess {number} [droplet.cpu_usage_percent] Average CPU usage
 * @apiSuccess {string} droplet.customer_name Customer name (joined)
 * @apiError {string} error="Droplet not found" (404) Droplet not found for tenant
 * @apiError {string} error="Failed to fetch droplet" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/droplets/123e4567-e89b-12d3-a456-426614174000 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * 
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "droplet": {
 *       "droplet_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "do_droplet_id": "123456789",
 *       "droplet_name": "web-server-01",
 *       "status": "active",
 *       "region": "nyc3",
 *       "size": "s-2vcpu-4gb",
 *       "ip_address": "192.0.2.100",
 *       "vcpus": 2,
 *       "memory": 4096,
 *       "disk": 80,
 *       "image": "ubuntu-22-04-x64",
 *       "cpu_usage_percent": 35.5,
 *       "customer_name": "Acme Corp"
 *     }
 *   }
 */
router.get('/droplets/:id', async (req, res) => {
  const { id } = req.params;
 
  try {
    const result = await pool.query(
      `SELECT 
        hd.*,
        c.name as customer_name
       FROM hosting_droplets hd
       LEFT JOIN customers c ON hd.customer_id = c.customer_id
       WHERE hd.droplet_id = $1 AND hd.tenant_id = $2`,
      [id, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Droplet not found' });
    }
 
    const droplet = result.rows[0];
    res.json({ droplet });
  } catch (error) {
    console.error('[Hosting] Error fetching droplet:', error);
    res.status(500).json({ error: 'Failed to fetch droplet' });
  }
});
 
/**
 * @api {put} /api/hosting/droplets/:id Update droplet assignments
 * @apiName UpdateHostingDroplet
 * @apiGroup Hosting
 * @apiDescription Update customer and tenant assignments for a Droplet (VM).
 * Used for organizing droplets in multi-tenant environment.
 * @apiParam {UUID} id Droplet ID (internal database ID)
 * @apiParam {UUID} [customer_id] Customer ID to assign droplet to
 * @apiParam {UUID} [assigned_tenant_id] Tenant ID to assign droplet to
 * @apiSuccess {boolean} success=true Operation successful
 * @apiSuccess {string} message="Droplet updated successfully"
 * @apiSuccess {object} droplet Updated droplet object
 * @apiError {string} error="Droplet not found" (404) Droplet not found for tenant
 * @apiError {string} error="Failed to update droplet" (500) Database update failed
 * @apiExample {curl} Example:
 *   curl -X PUT http://localhost:3000/api/hosting/droplets/123e4567-e89b-12d3-a456-426614174000 \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{"customer_id": "789e0123-ab45-67cd-89ef-012345678901"}'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "Droplet updated successfully",
 *     "droplet": {
 *       "droplet_id": "123e4567-e89b-12d3-a456-426614174000",
 *       "customer_id": "789e0123-ab45-67cd-89ef-012345678901",
 *       "updated_at": "2026-03-12T10:30:00.000Z"
 *     }
 *   }
 */
router.put('/droplets/:id', async (req, res) => {
  const { id } = req.params;
  const { customer_id, assigned_tenant_id } = req.body;
 
  try {
    // Update the droplet
    const result = await pool.query(
      `UPDATE hosting_droplets 
       SET customer_id = $1, 
           assigned_tenant_id = $2,
           updated_at = CURRENT_TIMESTAMP
       WHERE droplet_id = $3 AND tenant_id = $4
       RETURNING *`,
      [customer_id || null, assigned_tenant_id || null, id, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Droplet not found' });
    }
 
    res.json({ 
      success: true, 
      message: 'Droplet updated successfully', 
      droplet: result.rows[0] 
    });
  } catch (error) {
    console.error('[Hosting] Error updating droplet:', error);
    res.status(500).json({ error: 'Failed to update droplet' });
  }
});
 
/**
 * @api {get} /api/hosting/do/droplets Get live DO droplets
 * @apiName GetLiveDigitalOceanDroplets
 * @apiGroup Hosting
 * @apiDescription Query DigitalOcean API directly for current droplet list (live, no cache).
 * Use sparingly - prefer cached GET /api/hosting/droplets endpoint.
 * @apiSuccess {Array} droplets Array of droplet objects from DigitalOcean API
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) DigitalOcean API request failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/do/droplets \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "droplets": [
 *       {
 *         "id": 123456789,
 *         "name": "web-server-01",
 *         "status": "active",
 *         "region": {"slug": "nyc3"},
 *         "size_slug": "s-2vcpu-4gb",
 *         "vcpus": 2,
 *         "memory": 4096,
 *         "disk": 80,
 *         "networks": {
 *           "v4": [{"ip_address": "192.0.2.100"}]
 *         }
 *       }
 *     ]
 *   }
 */
router.get('/do/droplets', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    const doService = new DigitalOceanService(req.tenant.id);
    const droplets = await doService.listDroplets();
    res.json({ droplets });
  } catch (error) {
    console.error('[Hosting] Error fetching DO droplets:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {get} /api/hosting/do/databases Get live DO databases
 * @apiName GetLiveDigitalOceanDatabases
 * @apiGroup Hosting
 * @apiDescription Query DigitalOcean API directly for current database cluster list
 * (live, no cache). Use sparingly - prefer cached GET /api/hosting/databases endpoint.
 * @apiSuccess {Array} databases Array of database cluster objects from DigitalOcean API
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) DigitalOcean API request failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/do/databases \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "databases": [
 *       {
 *         "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
 *         "name": "production-pg",
 *         "engine": "pg",
 *         "version": "15",
 *         "status": "online",
 *         "region": "nyc3",
 *         "size": "db-s-2vcpu-4gb",
 *         "num_nodes": 1,
 *         "connection": {
 *           "host": "production-pg-do-user-123-0.b.db.ondigitalocean.com",
 *           "port": 25060
 *         }
 *       }
 *     ]
 *   }
 */
router.get('/do/databases', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    const doService = new DigitalOceanService(req.tenant.id);
    const databases = await doService.listDatabases();
    res.json({ databases });
  } catch (error) {
    console.error('[Hosting] Error fetching DO databases:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {get} /api/hosting/do/resources Get all live DO resources
 * @apiName GetLiveDigitalOceanResources
 * @apiGroup Hosting
 * @apiDescription Query DigitalOcean API directly for all resources (apps, databases, droplets)
 * with summary counts. Live query, no caching. Use for initial sync or troubleshooting.
 * @apiSuccess {Array} apps Array of app objects
 * @apiSuccess {Array} databases Array of database cluster objects
 * @apiSuccess {Array} droplets Array of droplet objects
 * @apiSuccess {object} summary Resource count summary
 * @apiSuccess {number} summary.apps Total apps
 * @apiSuccess {number} summary.databases Total databases
 * @apiSuccess {number} summary.droplets Total droplets
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) DigitalOcean API request failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/do/resources \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "apps": [...],
 *     "databases": [...],
 *     "droplets": [...],
 *     "summary": {
 *       "apps": 15,
 *       "databases": 3,
 *       "droplets": 8
 *     }
 *   }
 */
router.get('/do/resources', async (req, res) => {
  try {    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }    const doService = new DigitalOceanService(req.tenant.id);
    const resources = await doService.getAllResources();
    res.json(resources);
  } catch (error) {
    console.error('[Hosting] Error fetching DO resources:', error);
    res.status(500).json({ error: error.message });
  }
});
 
// ===========================
// App Operations
// ===========================
 
/**
 * @api {post} /api/hosting/apps/:id/deploy Trigger app deployment
 * @apiName DeployHostingApp
 * @apiGroup Hosting
 * @apiDescription Trigger a new deployment for a DigitalOcean app. Optionally force rebuild
 * from source. Records deployment in database for history tracking.
 * @apiParam {UUID} id App ID (internal database ID)
 * @apiParam {boolean} [force_rebuild=false] Force complete rebuild from source
 * @apiSuccess {string} message="Deployment triggered successfully"
 * @apiSuccess {object} deployment Deployment object from DigitalOcean API
 * @apiSuccess {string} deployment.id Deployment ID
 * @apiSuccess {string} deployment.phase Deployment phase (PENDING_BUILD, BUILDING, DEPLOYING, ACTIVE)
 * @apiSuccess {DateTime} deployment.created_at Deployment start time
 * @apiError {string} error="App not found" (404) App not found for tenant
 * @apiError {string} error (500) Deployment trigger failed
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/hosting/apps/123e4567-e89b-12d3-a456-426614174000/deploy \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{"force_rebuild": true}'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Deployment triggered successfully",
 *     "deployment": {
 *       "id": "deploy-abc123",
 *       "phase": "PENDING_BUILD",
 *       "created_at": "2026-03-12T10:30:00Z",
 *       "progress": {
 *         "steps": []
 *       }
 *     }
 *   }
 */
router.post('/apps/:id/deploy', async (req, res) => {
  const { id } = req.params;
  const { force_rebuild } = req.body;
 
  try {
    // Get app's DO ID
    const appResult = await pool.query(
      'SELECT do_app_id FROM hosting_apps WHERE app_id = $1 AND tenant_id = $2',
      [id, req.tenant.id]
    );
 
    if (appResult.rows.length === 0) {
      return res.status(404).json({ error: 'App not found' });
    }
 
    const doService = new DigitalOceanService(req.tenant.id);
    const deployment = await doService.createDeployment(
      appResult.rows[0].do_app_id,
      force_rebuild || false
    );
 
    // Record deployment in database
    await pool.query(
      `INSERT INTO hosting_deployments (
        app_id, do_deployment_id, status, started_at
      ) VALUES ($1, $2, $3, CURRENT_TIMESTAMP)`,
      [id, deployment.id, deployment.phase]
    );
 
    res.json({
      message: 'Deployment triggered successfully',
      deployment
    });
  } catch (error) {
    console.error('[Hosting] Error triggering deployment:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {get} /api/hosting/apps/:id/deployments Get app deployment history
 * @apiName GetAppDeployments
 * @apiGroup Hosting
 * @apiDescription Get deployment history for an app (last 50 deployments).
 * @apiParam {UUID} id App ID (internal database ID)
 * @apiSuccess {Array} deployments Array of deployment objects
 * @apiSuccess {UUID} deployments.deployment_id Internal deployment ID
 * @apiSuccess {string} deployments.do_deployment_id DigitalOcean deployment ID
 * @apiSuccess {string} deployments.status Deployment status/phase
 * @apiSuccess {DateTime} deployments.started_at Deployment start time
 * @apiSuccess {DateTime} [deployments.completed_at] Deployment completion time
 * @apiError {string} error="Failed to fetch deployments" (500) Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/apps/123e4567-e89b-12d3-a456-426614174000/deployments \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "deployments": [
 *       {
 *         "deployment_id": "123e4567-e89b-12d3-a456-426614174000",
 *         "do_deployment_id": "deploy-abc123",
 *         "status": "ACTIVE",
 *         "started_at": "2026-03-12T10:30:00.000Z",
 *         "completed_at": "2026-03-12T10:35:00.000Z"
 *       },
 *       {
 *         "deployment_id": "456e7890-ab12-34cd-5678-901234567890",
 *         "do_deployment_id": "deploy-def456",
 *         "status": "ACTIVE",
 *         "started_at": "2026-03-11T15:20:00.000Z",
 *         "completed_at": "2026-03-11T15:24:00.000Z"
 *       }
 *     ]
 *   }
 */
router.get('/apps/:id/deployments', async (req, res) => {
  const { id } = req.params;
 
  try {
    const result = await pool.query(
      `SELECT * FROM hosting_deployments
       WHERE app_id = $1
       ORDER BY started_at DESC
       LIMIT 50`,
      [id]
    );
 
    res.json({ deployments: result.rows });
  } catch (error) {
    console.error('[Hosting] Error fetching deployments:', error);
    res.status(500).json({ error: 'Failed to fetch deployments' });
  }
});
 
/**
 * @api {post} /api/hosting/apps/rename-all Rename all apps with prefixes
 * @apiName RenameAllApps
 * @apiGroup Hosting
 * @apiDescription Batch rename all DigitalOcean apps with appropriate prefixes (wordpress- or
 * nodejs-) and assign them to respective projects. WordPress apps detected by GitHub repo.
 * Also syncs renamed apps to database. Use for initial organization or cleanup.
 * @apiSuccess {boolean} success=true Operation successful
 * @apiSuccess {string} message Summary message
 * @apiSuccess {object} results Operation results
 * @apiSuccess {number} results.total Total apps processed
 * @apiSuccess {Array} results.renamed Apps that were renamed
 * @apiSuccess {Array} results.skipped Apps skipped (already have prefix)
 * @apiSuccess {Array} results.organized Apps assigned to projects
 * @apiSuccess {Array} results.errors Errors encountered
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) Batch operation failed
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/hosting/apps/rename-all \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "Renamed 5 apps, organized 15 into projects",
 *     "results": {
 *       "total": 15,
 *       "renamed": [
 *         {
 *           "appId": "a1b2c3d4",
 *           "oldName": "example-site",
 *           "newName": "wordpress-example-site",
 *           "type": "wordpress"
 *         }
 *       ],
 *       "skipped": [
 *         {"appId": "b2c3d4e5", "name": "nodejs-api", "reason": "Already has prefix"}
 *       ],
 *       "organized": [
 *         {"appId": "a1b2c3d4", "name": "wordpress-example", "project": "WordPress"}
 *       ],
 *       "errors": []
 *     }
 *   }
 */
router.post('/apps/rename-all', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
 
    const doService = new DigitalOceanService(req.tenant.id);
    const apps = await doService.listApps();
 
    // Get WordPress and Node.js projects
    let wordpressProjectId = null;
    let nodejsProjectId = null;
    
    try {
      const projects = await doService.listProjects();
      
      const wordpressProject = projects.find(p => 
        p.name.toLowerCase().includes('wordpress')
      );
      const nodejsProject = projects.find(p => 
        p.name.toLowerCase().includes('nodejs') || p.name.toLowerCase().includes('node.js')
      );
      
      if (wordpressProject) {
        wordpressProjectId = wordpressProject.id;
        console.log(`[Hosting] Using WordPress project: ${wordpressProject.name}`);
      }
      if (nodejsProject) {
        nodejsProjectId = nodejsProject.id;
        console.log(`[Hosting] Using Node.js project: ${nodejsProject.name}`);
      }
    } catch (error) {
      console.warn('[Hosting] Could not fetch projects:', error.message);
    }
 
    const results = {
      total: apps.length,
      renamed: [],
      skipped: [],
      organized: [],
      errors: []
    };
 
    for (const app of apps) {
      try {
        const currentName = app.spec?.name || 'unknown';
        
        // Detect app type based on GitHub repo
        const githubRepo = app.spec?.services?.[0]?.github?.repo || '';
        const isWordPress = githubRepo.includes('wordpress-egg') || githubRepo.includes('wordpress');
        const projectId = isWordPress ? wordpressProjectId : nodejsProjectId;
        
        // Check if needs renaming
        const needsRename = !currentName.startsWith('wordpress-') && !currentName.startsWith('nodejs-');
        
        if (needsRename) {
          // Determine new name
          const prefix = isWordPress ? 'wordpress-' : 'nodejs-';
          const newName = `${prefix}${currentName}`;
 
          // Update app spec with new name
          const updatedSpec = {
            ...app.spec,
            name: newName
          };
 
          // Update app in DigitalOcean
          await doService.updateApp(app.id, updatedSpec);
 
          results.renamed.push({
            appId: app.id,
            oldName: currentName,
            newName: newName,
            type: isWordPress ? 'wordpress' : 'nodejs'
          });
 
          console.log(`[Hosting] Renamed app ${app.id}: ${currentName} -> ${newName}`);
        } else {
          results.skipped.push({
            appId: app.id,
            name: currentName,
            reason: 'Already has prefix'
          });
        }
 
        // Assign app to appropriate project
        if (projectId) {
          try {
            await doService.apiRequest('POST', `/projects/${projectId}/resources`, {
              resources: [`do:app:${app.id}`]
            });
            
            results.organized.push({
              appId: app.id,
              name: currentName,
              project: isWordPress ? 'WordPress' : 'Node.js'
            });
            
            console.log(`[Hosting] Assigned app ${app.id} to ${isWordPress ? 'WordPress' : 'Node.js'} project`);
          } catch (error) {
            console.warn(`[Hosting] Could not assign app ${app.id} to project:`, error.message);
          }
        }
      } catch (error) {
        console.error(`[Hosting] Error processing app ${app.id}:`, error);
        results.errors.push({
          appId: app.id,
          name: app.spec?.name || 'unknown',
          error: error.message
        });
      }
    }
 
    // Trigger a sync to update database
    if (results.renamed.length > 0) {
      try {
        await doService.getAllResources().then(async (resources) => {
          for (const app of resources.apps) {
            await pool.query(
              `INSERT INTO hosting_apps (
                tenant_id, do_app_id, app_name, app_type, status, region,
                live_url, default_domain, custom_domains, metadata, 
                created_at, updated_at
              )
              VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
              ON CONFLICT (do_app_id)
              DO UPDATE SET
                app_name = EXCLUDED.app_name,
                status = EXCLUDED.status,
                updated_at = CURRENT_TIMESTAMP`,
              [
                req.tenant.id,
                app.id,
                app.spec?.name || 'Unknown App',
                app.spec?.services?.[0]?.github?.repo?.includes('wordpress') ? 'wordpress' : 'nodejs',
                app.live_url_base ? 'active' : 'pending',
                app.region,
                app.live_url,
                app.default_ingress,
                JSON.stringify(app.spec?.domains || []),
                JSON.stringify(app)
              ]
            );
          }
        });
        console.log('[Hosting] Database synced after rename-all');
      } catch (err) {
        console.error('[Hosting] Error syncing after rename:', err);
      }
    }
 
    res.json({
      success: true,
      message: `Renamed ${results.renamed.length} apps, organized ${results.organized.length} into projects`,
      results
    });
  } catch (error) {
    console.error('[Hosting] Error in rename-all:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {post} /api/hosting/apps/organize Organize apps into projects
 * @apiName OrganizeApps
 * @apiGroup Hosting
 * @apiDescription Assign all apps to their appropriate DigitalOcean projects without renaming.
 * WordPress apps assigned to WordPress project, Node.js apps to Node.js project.
 * Requires projects to exist in DigitalOcean.
 * @apiSuccess {boolean} success=true Operation successful
 * @apiSuccess {string} message Summary message
 * @apiSuccess {object} results Operation results
 * @apiSuccess {number} results.total Total apps processed
 * @apiSuccess {Array} results.organized Apps successfully organized
 * @apiSuccess {Array} results.skipped Apps skipped (no matching project)
 * @apiSuccess {Array} results.errors Errors encountered
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error="No WordPress or Node.js projects found" (404) Required projects missing
 * @apiError {string} error (500) Batch operation failed
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/hosting/apps/organize \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "Organized 15 apps into projects",
 *     "results": {
 *       "total": 15,
 *       "organized": [
 *         {"appId": "a1b2c3d4", "name": "wordpress-example", "project": "WordPress"},
 *         {"appId": "b2c3d4e5", "name": "nodejs-api", "project": "Node.js"}
 *       ],
 *       "skipped": [],
 *       "errors": []
 *     }
 *   }
 */
router.post('/apps/organize', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
 
    const doService = new DigitalOceanService(req.tenant.id);
    const apps = await doService.listApps();
 
    // Get WordPress and Node.js projects
    let wordpressProjectId = null;
    let nodejsProjectId = null;
    
    try {
      const projects = await doService.listProjects();
      
      const wordpressProject = projects.find(p => 
        p.name.toLowerCase().includes('wordpress')
      );
      const nodejsProject = projects.find(p => 
        p.name.toLowerCase().includes('nodejs') || p.name.toLowerCase().includes('node.js')
      );
      
      if (wordpressProject) {
        wordpressProjectId = wordpressProject.id;
        console.log(`[Hosting] Using WordPress project: ${wordpressProject.name}`);
      } else {
        console.warn('[Hosting] No WordPress project found');
      }
      
      if (nodejsProject) {
        nodejsProjectId = nodejsProject.id;
        console.log(`[Hosting] Using Node.js project: ${nodejsProject.name}`);
      } else {
        console.warn('[Hosting] No Node.js project found');
      }
      
      if (!wordpressProjectId && !nodejsProjectId) {
        return res.status(404).json({ 
          error: 'No WordPress or Node.js projects found in DigitalOcean' 
        });
      }
    } catch (error) {
      console.error('[Hosting] Error fetching projects:', error);
      return res.status(500).json({ error: 'Could not fetch DigitalOcean projects' });
    }
 
    const results = {
      total: apps.length,
      organized: [],
      skipped: [],
      errors: []
    };
 
    for (const app of apps) {
      try {
        const appName = app.spec?.name || 'unknown';
        
        // Detect app type based on name or GitHub repo
        const githubRepo = app.spec?.services?.[0]?.github?.repo || '';
        const isWordPress = appName.includes('wordpress') || 
                           githubRepo.includes('wordpress-egg') || 
                           githubRepo.includes('wordpress');
        
        const projectId = isWordPress ? wordpressProjectId : nodejsProjectId;
        const projectType = isWordPress ? 'WordPress' : 'Node.js';
        
        if (!projectId) {
          results.skipped.push({
            appId: app.id,
            name: appName,
            reason: `No ${projectType} project found`
          });
          continue;
        }
 
        // Assign app to appropriate project
        await doService.apiRequest('POST', `/projects/${projectId}/resources`, {
          resources: [`do:app:${app.id}`]
        });
        
        results.organized.push({
          appId: app.id,
          name: appName,
          project: projectType
        });
        
        console.log(`[Hosting] Assigned app ${app.id} (${appName}) to ${projectType} project`);
        
      } catch (error) {
        console.error(`[Hosting] Error organizing app ${app.id}:`, error);
        results.errors.push({
          appId: app.id,
          name: app.spec?.name || 'unknown',
          error: error.message
        });
      }
    }
 
    res.json({
      success: true,
      message: `Organized ${results.organized.length} apps into projects`,
      results
    });
  } catch (error) {
    console.error('[Hosting] Error in organize:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * GET /api/hosting/apps
 * Get all apps from database (cached, synced every 5 minutes)
 * Excludes WordPress apps
 */
router.get('/apps', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    // Get last sync time
    const syncResult = await pool.query(
      `SELECT last_sync_at FROM digitalocean_config WHERE tenant_id = $1`,
      [req.tenant.id]
    );
    const lastSync = syncResult.rows[0]?.last_sync_at;
    
    // Root tenant sees all apps, other tenants only see their assigned apps
    // Exclude WordPress apps (identified by app_name containing 'wordpress')
    const isRootTenant = req.tenant.id === '00000000-0000-0000-0000-000000000001';
    const result = await pool.query(
      `SELECT 
        ha.*,
        c.name as customer_name
       FROM hosting_apps ha
       LEFT JOIN customers c ON ha.customer_id = c.customer_id
       WHERE ha.app_name NOT ILIKE '%wordpress%'
       ${isRootTenant ? '' : 'AND (ha.tenant_id = $1 OR ha.assigned_tenant_id = $1)'}
       ORDER BY ha.created_at DESC`,
      isRootTenant ? [] : [req.tenant.id]
    );
 
    res.json({ 
      apps: result.rows,
      lastSync,
      ttl: '5 minutes'
    });
  } catch (error) {
    console.error('[Hosting] Error fetching apps:', error);
    if (error.code === '42P01') {
      return res.json({ apps: [], lastSync: null, ttl: '5 minutes' });
    }
    res.status(500).json({ error: 'Failed to fetch apps' });
  }
});
 
/**
 * @api {get} /api/hosting/database/:id/metrics Get database metrics
 * @apiName GetDatabaseMetrics
 * @apiGroup Hosting
 * @apiDescription Fetch current metrics for a managed database cluster from DigitalOcean API.
 * Returns CPU count, memory usage, and disk usage.
 * @apiParam {string} id DigitalOcean database cluster ID (do_database_id)
 * @apiSuccess {object} metrics Database metrics object
 * @apiSuccess {number} metrics.cpu_count Number of CPUs
 * @apiSuccess {number} metrics.memory_mb Memory usage in MB
 * @apiSuccess {number} metrics.disk_gb Disk usage in GB
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) Metrics fetch failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/database/a1b2c3d4-e5f6-7890-abcd-ef1234567890/metrics \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "metrics": {
 *       "cpu_count": 2,
 *       "memory_mb": 3845,
 *       "disk_gb": 12.5
 *     }
 *   }
 */
router.get('/database/:id/metrics', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    const { id } = req.params;
    const doService = new DigitalOceanService(req.tenant.id);
    const metrics = await doService.getDatabaseMetrics(id);
    
    res.json({ metrics });
  } catch (error) {
    console.error('[Hosting] Error fetching database metrics:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {get} /api/hosting/droplet/:id/metrics Get droplet metrics
 * @apiName GetDropletMetrics
 * @apiGroup Hosting
 * @apiDescription Fetch time-series metrics for a Droplet from DigitalOcean Monitoring API.
 * Supports CPU, memory, disk, and network metrics with custom time ranges.
 * @apiParam {string} id DigitalOcean droplet ID (numeric)
 * @apiParam {string} [metric=cpu] Metric type (cpu, memory_available, disk_read, disk_write, public_inbound, public_outbound)
 * @apiParam {number} [start] Unix timestamp for start of time range
 * @apiParam {number} [end] Unix timestamp for end of time range
 * @apiSuccess {object} metrics Prometheus-style metrics object
 * @apiSuccess {string} metrics.status="success"
 * @apiSuccess {object} metrics.data Metrics data
 * @apiSuccess {Array} metrics.data.result Array of metric result objects
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) Metrics fetch failed
 * @apiExample {curl} Example:
 *   curl -X GET "http://localhost:3000/api/hosting/droplet/123456789/metrics?metric=cpu&start=1678886400&end=1678972800" \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "metrics": {
 *       "status": "success",
 *       "data": {
 *         "resultType": "matrix",
 *         "result": [
 *           {
 *             "metric": {"host_id": "123456789"},
 *             "values": [
 *               [1678886400, "35.5"],
 *               [1678890000, "42.3"]
 *             ]
 *           }
 *         ]
 *       }
 *     }
 *   }
 */
router.get('/droplet/:id/metrics', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    const { id } = req.params;
    const { metric = 'cpu', start, end } = req.query;
    
    const doService = new DigitalOceanService(req.tenant.id);
    const metrics = await doService.getDropletMetrics(
      parseInt(id), 
      metric, 
      start ? parseInt(start) : null,
      end ? parseInt(end) : null
    );
    
    res.json({ metrics });
  } catch (error) {
    console.error('[Hosting] Error fetching droplet metrics:', error);
    res.status(500).json({ error: error.message });
  }
});
 
/**
 * @api {get} /api/hosting/app/:id/metrics Get app metrics
 * @apiName GetAppMetrics
 * @apiGroup Hosting
 * @apiDescription Fetch metrics for a DigitalOcean App Platform app. Returns CPU, memory,
 * and bandwidth metrics for the app's components.
 * @apiParam {string} id DigitalOcean app ID (do_app_id)
 * @apiSuccess {object} metrics App metrics object
 * @apiSuccess {Array} metrics.components Array of component metrics
 * @apiSuccess {string} metrics.components.name Component name
 * @apiSuccess {object} metrics.components.metrics Metrics for component
 * @apiError {string} error="Tenant context not available" (400) No tenant in request
 * @apiError {string} error (500) Metrics fetch failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/hosting/app/a1b2c3d4-e5f6-7890-abcd-ef1234567890/metrics \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "metrics": {
 *       "components": [
 *         {
 *           "name": "web",
 *           "metrics": {
 *             "cpu_percentage": 25.5,
 *             "memory_percentage": 60.2,
 *             "restart_count": 0
 *           }
 *         }
 *       ]
 *     }
 *   }
 */
router.get('/app/:id/metrics', async (req, res) => {
  try {
    if (!req.tenant || !req.tenant.id) {
      return res.status(400).json({ error: 'Tenant context not available' });
    }
    
    const { id } = req.params;
    const doService = new DigitalOceanService(req.tenant.id);
    const metrics = await doService.getAppMetrics(id);
    
    res.json({ metrics });
  } catch (error) {
    console.error('[Hosting] Error fetching app metrics:', error);
    res.status(500).json({ error: error.message });
  }
});
 
module.exports = router;