tenants.js

Go to the documentation of this file.

/**
 * @file routes/tenants.js
 * @module routes/tenants
 * @description Multi-tenant management API for MSP platform. Handles tenant lifecycle, subdomain
 * provisioning, MeshCentral device group integration, DNS automation, user provisioning, and
 * comprehensive audit logging. Root MSP administrators manage all tenant operations.
 * 
 * **Multi-Tenant Architecture:**
 * - Root MSP tenant (is_msp=true) manages multiple customer tenants
 * - Each tenant has unique subdomain, isolated data, and user accounts
 * - Tenant-based data filtering enforced across entire platform
 * - Subdomain-based routing for white-label customer portals
 * 
 * **Tenant Lifecycle:**
 * 1. Create - Generate subdomain, provision DNS, create MeshCentral group
 * 2. Active - Full operational access to platform features
 * 3. Suspended - Temporary access restriction (billing issues, security)
 * 4. Inactive - Soft delete, data retained
 * 5. Delete - Permanent removal with cascade options
 * 
 * **Subdomain Provisioning:**
 * - Automatic Cloudflare DNS record creation (CNAME to base domain)
 * - Format validation (lowercase, alphanumeric + hyphens)
 * - Reserved subdomain blocking (admin, api, www, app, etc.)
 * - Immutable after creation (prevents broken links/users)
 * - Example: acmecorp.app.everydaytech.au → app.everydaytech.au
 * 
 * **MeshCentral Integration:**
 * - Automatic device group creation on tenant creation
 * - meshcentral_group_id stored in tenants table
 * - Device isolation per tenant for RMM security
 * - Group naming: "{tenant_name} Devices"
 * - Fallback: Group created on next agent sync if creation fails
 * 
 * **Audit Logging:**
 * - All tenant operations logged to tenant_audit_log table
 * - Create, update, delete, user creation, MSP status changes
 * - User attribution (who performed action)
 * - Detailed JSON payloads for change tracking
 * - Impersonation audit trail (separate endpoint)
 * 
 * **Security:**
 * - Most routes require Root MSP access (requireRoot middleware)
 * - Regular tenant users can only view their current tenant
 * - Prevent root/MSP tenant deletion
 * - Prevent multiple root tenant creation
 * - Force-delete option requires explicit query parameter
 * 
 * **Related Models:**
 * - tenants table (tenant_id, name, subdomain, status, is_msp, meshcentral_group_id)
 * - tenant_audit_log table (comprehensive action logging)
 * - tenant_settings table (per-tenant configuration)
 * - users, customers, tickets, invoices, etc. (all tenant-scoped)
 * @requires express
 * @requires ../services/db
 * @requires ../services/cloudflare
 * @requires ../middleware/auth
 * @requires ../middleware/tenant
 * @requires ../lib/meshcentral-api
 * @author IBG MSP Development Team
 * @date 2024-03-11
 * @since 1.0.0
 * @see {@link module:routes/users} for user management
 * @see {@link module:middleware/tenant} for tenant context filtering
 */
 
const { createSubdomain } = require('../services/cloudflare');
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const { requireRoot, withTenantContext, setTenantContext } = require('../middleware/tenant');
 
// Apply tenant context to all routes in this router
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /tenants/current Get Current Tenant
 * @apiName GetCurrentTenant
 * @apiGroup Tenants
 * @apiDescription Retrieves the authenticated user's tenant details. Available to all authenticated
 * users to view their own tenant information (company name, subdomain, status, etc.).
 * 
 * **Use Cases:**
 * - Display tenant branding in UI header
 * - Show current tenant context in multi-tenant dashboards
 * - Validate tenant status before operations
 * - Retrieve subdomain for URL construction
 * 
 * **Tenant Context:**
 * Uses req.user.tenantId from JWT token to lookup tenant. No special permissions required -
 * all authenticated users can view their own tenant.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiSuccess {number} tenant_id Unique tenant ID
 * @apiSuccess {string} name Tenant/company name
 * @apiSuccess {string} subdomain Unique subdomain identifier
 * @apiSuccess {string} status Tenant status (active, suspended, inactive)
 * @apiSuccess {boolean} is_msp Whether tenant is root MSP (true only for platform admin)
 * @apiSuccess {string} meshcentral_group_id MeshCentral device group ID
 * @apiSuccess {boolean} meshcentral_setup_complete Whether MeshCentral integration complete
 * @apiSuccess {string} created_at Tenant creation timestamp
 * @apiSuccess {string} updated_at Last update timestamp
 * @apiError {number} 404 Tenant not found (data integrity issue)
 * @apiError {number} 500 Failed to fetch tenant
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants/current"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "tenant_id": 5,
 *     "name": "Acme Corporation",
 *     "subdomain": "acmecorp",
 *     "status": "active",
 *     "is_msp": false,
 *     "meshcentral_group_id": "mesh://server/mesh1234567890abcdef",
 *     "meshcentral_setup_complete": true,
 *     "created_at": "2024-01-15T10:00:00.000Z",
 *     "updated_at": "2024-03-01T14:30:00.000Z"
 *   }
 * @since 1.0.0
 * @see {@link module:routes/tenants.getTenant} for detailed tenant stats
 */
router.get('/current', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM tenants WHERE tenant_id = $1',
      [req.user.tenantId]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Tenant not found' });
    }
 
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error fetching current tenant:', err);
    res.status(500).json({ error: 'Failed to fetch tenant' });
  }
});
 
/**
 * @api {get} /tenants List All Tenants
 * @apiName GetTenants
 * @apiGroup Tenants
 * @apiDescription Retrieves complete list of all tenants with aggregated statistics. Root MSP
 * administrators only. Includes user counts, customer counts, and ticket counts for each tenant.
 * Used for MSP dashboard overview and tenant management.
 * 
 * **Statistics Included:**
 * - user_count: Total users in tenant
 * - customer_count: Total customers managed by tenant
 * - ticket_count: Total tickets created in tenant
 * 
 * **Ordering:**
 * Tenants sorted by creation date (newest first). Shows recent tenant additions at top.
 * 
 * **Access Control:**
 * Requires Root MSP authentication (requireRoot middleware). Regular tenant users receive
 * 403 Forbidden.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiSuccess {object[]} tenants Array of tenant objects with statistics
 * @apiSuccess {number} tenants.tenant_id Unique tenant ID
 * @apiSuccess {string} tenants.name Tenant/company name
 * @apiSuccess {string} tenants.subdomain Unique subdomain
 * @apiSuccess {string} tenants.status Tenant status (active, suspended, inactive)
 * @apiSuccess {boolean} tenants.is_msp Whether tenant is MSP
 * @apiSuccess {string} tenants.meshcentral_group_id MeshCentral device group ID
 * @apiSuccess {string} tenants.created_at Creation timestamp
 * @apiSuccess {number} tenants.user_count Total users in tenant
 * @apiSuccess {number} tenants.customer_count Total customers in tenant
 * @apiSuccess {number} tenants.ticket_count Total tickets in tenant
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {number} 500 Failed to fetch tenants
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "tenants": [
 *       {
 *         "tenant_id": 5,
 *         "name": "Acme Corporation",
 *         "subdomain": "acmecorp",
 *         "status": "active",
 *         "is_msp": false,
 *         "meshcentral_group_id": "mesh://server/mesh1234567890abcdef",
 *         "created_at": "2024-01-15T10:00:00.000Z",
 *         "user_count": "23",
 *         "customer_count": "145",
 *         "ticket_count": "892"
 *       },
 *       {
 *         "tenant_id": 1,
 *         "name": "Root MSP",
 *         "subdomain": "root.demo",
 *         "status": "active",
 *         "is_msp": true,
 *         "meshcentral_group_id": null,
 *         "created_at": "2023-11-01T00:00:00.000Z",
 *         "user_count": "5",
 *         "customer_count": "0",
 *         "ticket_count": "0"
 *       }
 *     ]
 *   }
 * @since 1.0.0
 * @see {@link module:routes/tenants.getTenant} for detailed single tenant info
 * @see {@link module:routes/tenants.createTenant} for creating tenants
 */
router.get('/', requireRoot, async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        t.*,
        COUNT(DISTINCT u.user_id) as user_count,
        COUNT(DISTINCT c.customer_id) as customer_count,
        COUNT(DISTINCT tk.ticket_id) as ticket_count
      FROM tenants t
      LEFT JOIN users u ON t.tenant_id = u.tenant_id
      LEFT JOIN customers c ON t.tenant_id = c.tenant_id
      LEFT JOIN tickets tk ON t.tenant_id = tk.tenant_id
      GROUP BY t.tenant_id
      ORDER BY t.created_at DESC
    `);
 
    res.json({ tenants: result.rows });
  } catch (err) {
    console.error('Error fetching tenants:', err);
    res.status(500).json({ error: 'Failed to fetch tenants' });
  }
});
 
/**
 * @api {get} /tenants/:id Get Tenant Details
 * @apiName GetTenant
 * @apiGroup Tenants
 * @apiDescription Retrieves detailed information for a specific tenant with comprehensive statistics.
 * Root MSP administrators only. Provides all tenant details plus counts of users, customers,
 * tickets, invoices, contracts, and agents.
 * 
 * **Statistics Provided:**
 * - user_count: Total users in tenant
 * - customer_count: Total customers managed
 * - ticket_count: Total support tickets
 * - invoice_count: Total invoices issued
 * - contract_count: Total service contracts
 * - agent_count: Total RMM agents deployed
 * 
 * **Use Cases:**
 * - Tenant detail page in MSP admin dashboard
 * - Tenant health monitoring
 * - Capacity planning and resource allocation
 * - Billing verification
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiSuccess {object} tenant Tenant object with all details
 * @apiSuccess {number} tenant.tenant_id Unique tenant ID
 * @apiSuccess {string} tenant.name Tenant/company name
 * @apiSuccess {string} tenant.subdomain Unique subdomain
 * @apiSuccess {string} tenant.status Tenant status (active, suspended, inactive)
 * @apiSuccess {boolean} tenant.is_msp Whether tenant is MSP
 * @apiSuccess {string} tenant.meshcentral_group_id MeshCentral device group ID
 * @apiSuccess {boolean} tenant.meshcentral_setup_complete MeshCentral integration status
 * @apiSuccess {string} tenant.created_at Creation timestamp
 * @apiSuccess {string} tenant.updated_at Last update timestamp
 * @apiSuccess {Object} stats Aggregated statistics
 * @apiSuccess {string} stats.user_count Total users
 * @apiSuccess {string} stats.customer_count Total customers
 * @apiSuccess {string} stats.ticket_count Total tickets
 * @apiSuccess {string} stats.invoice_count Total invoices
 * @apiSuccess {string} stats.contract_count Total contracts
 * @apiSuccess {String} stats.agent_count Total RMM agents
 * 
 * @apiError {Number} 403 Forbidden (not root MSP admin)
 * @apiError {Number} 404 Tenant not found
 * @apiError {Number} 500 Failed to fetch tenant
 * 
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants/5"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "tenant": {
 *       "tenant_id": 5,
 *       "name": "Acme Corporation",
 *       "subdomain": "acmecorp",
 *       "status": "active",
 *       "is_msp": false,
 *       "meshcentral_group_id": "mesh://server/mesh1234567890abcdef",
 *       "meshcentral_setup_complete": true,
 *       "created_at": "2024-01-15T10:00:00.000Z",
 *       "updated_at": "2024-03-01T14:30:00.000Z"
 *     },
 *     "stats": {
 *       "user_count": "23",
 *       "customer_count": "145",
 *       "ticket_count": "892",
 *       "invoice_count": "1247",
 *       "contract_count": "89",
 *       "agent_count": "432"
 *     }
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/tenants.getTenants} for list of all tenants
 * @see {@link module:routes/tenants.updateTenant} for updating tenant
 */
router.get('/:id', requireRoot, async (req, res) => {
  const { id } = req.params;
 
  try {
    const tenantQuery = await pool.query(
      'SELECT * FROM tenants WHERE tenant_id = $1',
      [id]
    );
 
    if (tenantQuery.rows.length === 0) {
      return res.status(404).json({ error: 'Tenant not found' });
    }
 
    const tenant = tenantQuery.rows[0];
 
    // Get stats for this tenant
    const statsQuery = await pool.query(`
      SELECT 
        (SELECT COUNT(*) FROM users WHERE tenant_id = $1) as user_count,
        (SELECT COUNT(*) FROM customers WHERE tenant_id = $1) as customer_count,
        (SELECT COUNT(*) FROM tickets WHERE tenant_id = $1) as ticket_count,
        (SELECT COUNT(*) FROM invoices WHERE tenant_id = $1) as invoice_count,
        (SELECT COUNT(*) FROM contracts WHERE tenant_id = $1) as contract_count,
        (SELECT COUNT(*) FROM agents WHERE tenant_id = $1) as agent_count
    `, [id]);
 
    res.json({
      tenant,
      stats: statsQuery.rows[0]
    });
  } catch (err) {
    console.error('Error fetching tenant:', err);
    res.status(500).json({ error: 'Failed to fetch tenant' });
  }
});
 
/**
 * @api {post} /tenants Create Tenant
 * @apiName CreateTenant
 * @apiGroup Tenants
 * @apiDescription Creates a new tenant with automatic subdomain provisioning, DNS record creation,
 * MeshCentral device group setup, and audit logging. Root MSP administrators only. Comprehensive
 * tenant onboarding in single operation.
 * 
 * **Creation Process:**
 * 1. Validate name and subdomain (format, uniqueness, reserved words)
 * 2. Create tenant record with 'active' status
 * 3. Initialize tenant_settings with company_name
 * 4. Create MeshCentral device group for RMM
 * 5. Provision Cloudflare DNS CNAME record (subdomain → base domain)
 * 6. Log creation to tenant_audit_log
 * 7. Return complete tenant object with subdomain_url
 * 
 * **Subdomain Validation:**
 * - Format: lowercase letters, numbers, hyphens only (regex: ^[a-z0-9][a-z0-9-]*[a-z0-9]$)
 * - Reserved: admin, api, www, app, mail, support, help
 * - Uniqueness: Must not exist in tenants table
 * - Special case: 'root' subdomain forced to 'root.demo', only one root tenant allowed
 * 
 * **MeshCentral Integration:**
 * - Device group name: "{tenant_name} Devices"
 * - Group ID stored in meshcentral_group_id column
 * - Failure non-blocking (marked for retry on next sync)
 * - Provides device isolation for RMM multi-tenancy
 * 
 * **DNS Provisioning:**
 * - Cloudflare API creates CNAME: {subdomain}.app.everydaytech.au → app.everydaytech.au
 * - 2 retry attempts for resilience
 * - Failure logged but doesn't block tenant creation
 * - subdomain_url returned if DNS successful, null otherwise
 * 
 * **Audit Trail:**
 * All tenant creations logged with user attribution and details JSON.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiHeader {string} Content-Type application/json
 * @apiParam {string} name Tenant/company name (required)
 * @apiParam {string} subdomain Unique subdomain identifier (required, lowercase alphanumeric+hyphens)
 * @apiSuccess {object} tenant Created tenant object
 * @apiSuccess {number} tenant.tenant_id Generated tenant ID
 * @apiSuccess {string} tenant.name Tenant name
 * @apiSuccess {string} tenant.subdomain Subdomain (may be modified for root tenant)
 * @apiSuccess {string} tenant.status Always 'active' for new tenants
 * @apiSuccess {boolean} tenant.is_msp Always false (unless creating root tenant)
 * @apiSuccess {string} tenant.meshcentral_group_id MeshCentral mesh group ID (if successful)
 * @apiSuccess {boolean} tenant.meshcentral_setup_complete MeshCentral integration status
 * @apiSuccess {string} subdomain_url Full URL (https://{subdomain}.app.everydaytech.au) or null
 * @apiError {number} 400 Name and subdomain are required
 * @apiError {number} 400 Invalid subdomain format
 * @apiError {number} 400 This subdomain is reserved
 * @apiError {Number} 403 Forbidden (not root MSP admin)
 * @apiError {Number} 409 Root tenant already exists
 * @apiError {Number} 409 Subdomain already exists
 * @apiError {Number} 500 Failed to create tenant
 * 
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Acme Corporation",
 *          "subdomain": "acmecorp"
 *        }' \
 *        "https://api.everydaytech.au/tenants"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "tenant": {
 *       "tenant_id": 5,
 *       "name": "Acme Corporation",
 *       "subdomain": "acmecorp",
 *       "status": "active",
 *       "is_msp": false,
 *       "meshcentral_group_id": "mesh://server/mesh1234567890abcdef",
 *       "meshcentral_setup_complete": true,
 *       "created_at": "2024-03-11T16:45:00.000Z",
 *       "updated_at": "2024-03-11T16:45:00.000Z"
 *     },
 *     "subdomain_url": "https://acmecorp.app.everydaytech.au"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/tenants.updateTenant} for updating tenants
 * @see {@link module:routes/tenants.createTenantUser} for creating tenant users
 * @see {@link module:services/cloudflare} for DNS provisioning
 */
router.post('/', requireRoot, async (req, res) => {
  const { name, subdomain } = req.body;
 
  if (!name || !subdomain) {
    return res.status(400).json({ error: 'Name and subdomain are required' });
  }
 
  // Check if creating root tenant
  const isRoot = name?.toLowerCase() === 'root' || subdomain?.toLowerCase() === 'root';
  
  // Prevent multiple root tenants
  if (isRoot) {
    const rootCheck = await pool.query(
      `SELECT tenant_id FROM tenants WHERE LOWER(name) = 'root' OR LOWER(subdomain) = 'root.demo'`
    );
    if (rootCheck.rows.length > 0) {
      return res.status(409).json({ error: 'Root tenant already exists' });
    }
  }
 
  // Validate subdomain format (lowercase, alphanumeric + hyphens)
  const subdomainRegex = /^[a-z0-9][a-z0-9-]*[a-z0-9]$/;
  if (!subdomainRegex.test(subdomain)) {
    return res.status(400).json({ 
      error: 'Invalid subdomain format. Use lowercase letters, numbers, and hyphens only.' 
    });
  }
 
  // Reserved subdomains
  const reserved = ['admin', 'api', 'www', 'app', 'mail', 'support', 'help'];
  if (reserved.includes(subdomain)) {
    return res.status(400).json({ 
      error: 'This subdomain is reserved' 
    });
  }
 
  try {
    // If creating root tenant, force subdomain to 'root.demo'
    const finalSubdomain = isRoot ? 'root.demo' : subdomain;
    const result = await pool.query(
      `INSERT INTO tenants (name, subdomain, status, is_msp)
       VALUES ($1, $2, 'active', false)
       RETURNING *`,
      [name, finalSubdomain]
    );
 
    const newTenantId = result.rows[0].tenant_id;
 
    // Initialize tenant settings with tenant name as company name
    try {
      await pool.query(
        `INSERT INTO tenant_settings (tenant_id, setting_key, setting_value, description)
         VALUES ($1, 'company_name', $2, 'Company name for this tenant')
         ON CONFLICT (tenant_id, setting_key) DO NOTHING`,
        [newTenantId, name]
      );
    } catch (settingsErr) {
      console.error('Warning: Failed to initialize tenant settings:', settingsErr);
      // Don't fail the whole operation if settings initialization fails
    }
 
    // Automatically create MeshCentral device group
    try {
      const MeshCentralAPI = require('../lib/meshcentral-api');
      const meshAPI = new MeshCentralAPI({
        url: process.env.MESHCENTRAL_URL || 'https://rmm-psa-meshcentral-aq48h.ondigitalocean.app',
        username: process.env.MESHCENTRAL_ADMIN_USER || 'admin',
        password: process.env.MESHCENTRAL_ADMIN_PASS || 'admin'
      });
      
      await meshAPI.login();
      const meshName = `${name} Devices`;
      const meshResult = await meshAPI.createMesh(
        meshName, 
        `Device group for ${name} (tenant:${newTenantId})`
      );
      
      if (meshResult && meshResult.meshId) {
        // Update tenant with mesh group ID
        await pool.query(
          'UPDATE tenants SET meshcentral_group_id = $1, meshcentral_setup_complete = true WHERE tenant_id = $2',
          [meshResult.meshId, newTenantId]
        );
        console.log(`✅ Created MeshCentral mesh group ${meshResult.meshId} for tenant ${name}`);
      }
    } catch (meshErr) {
      console.error('⚠️ Failed to create MeshCentral mesh group:', meshErr.message);
      // Don't fail tenant creation if mesh creation fails - will be created on next sync
    }
 
    // Log the action
    await pool.query(
      `INSERT INTO tenant_audit_log (user_id, tenant_id, action, resource_type, details)
       VALUES ($1, $2, 'create', 'tenant', $3)`,
      [
        req.user.userId,
        newTenantId,
        JSON.stringify({ name, subdomain })
      ]
    );
 
    // Create subdomain in Cloudflare
    const target = process.env.BASE_APP_DOMAIN || 'app.everydayoffice.au';
    let dnsRecord = null;
    let dnsError = null;
    // DNS provisioning with retry
    for (let attempt = 1; attempt <= 2; attempt++) {
      try {
        dnsRecord = await createSubdomain(finalSubdomain, target);
        console.log(`Tenant domain ready: ${dnsRecord}`);
        break;
      } catch (cfErr) {
        dnsError = cfErr;
        console.error(`⚠️ Failed to provision DNS (attempt ${attempt}):`, cfErr.message);
        if (attempt === 2) {
          // Optionally: notify admin or mark tenant for DNS retry
        }
      }
    }
 
    // Fetch the updated tenant record with mesh group ID
    const updatedTenant = await pool.query(
      'SELECT * FROM tenants WHERE tenant_id = $1',
      [newTenantId]
    );
 
    res.status(201).json({
      tenant: updatedTenant.rows[0],
      subdomain_url: dnsRecord ? `https://${dnsRecord}` : null
    });
  } catch (err) {
    console.error('Error creating tenant:', err);
    
    if (err.code === '23505') { // Unique violation
      return res.status(409).json({ error: 'Subdomain already exists' });
    }
    
    res.status(500).json({ error: 'Failed to create tenant' });
  }
});
 
/**
 * @api {put} /tenants/:id Update Tenant
 * @apiName UpdateTenant
 * @apiGroup Tenants
 * @apiDescription Updates tenant name and/or status. Root MSP administrators only. Subdomain
 * cannot be changed after creation to prevent breaking existing users and links. All updates
 * logged to audit trail.
 * 
 * **Updateable Fields:**
 * - name: Tenant/company name (shown in UI, reports)
 * - status: Operational status (active, suspended, inactive)
 * 
 * **Status Values:**
 * - active: Full access to platform features
 * - suspended: Limited access (e.g., billing issues, policy violation)
 * - inactive: Soft delete, data retained but no access
 * 
 * **Immutable Fields:**
 * - subdomain: Cannot be changed after creation (would break links, user accounts)
 * - tenant_id: System-generated identifier
 * - is_msp: Changed via dedicated /msp endpoint
 * - meshcentral_group_id: System-managed
 * 
 * **Audit Logging:**
 * All updates logged to tenant_audit_log with user attribution and JSON details.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiParam {string} [name] New tenant/company name (optional)
 * @apiParam {string} [status] New status: active, suspended, or inactive (optional)
 * @apiSuccess {number} tenant_id Tenant ID
 * @apiSuccess {string} name Updated tenant name
 * @apiSuccess {string} subdomain Subdomain (unchanged)
 * @apiSuccess {string} status Updated status
 * @apiSuccess {string} updated_at Last update timestamp (set to NOW())
 * @apiError {number} 400 Status must be one of: active, suspended, inactive
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {number} 404 Tenant not found
 * @apiError {number} 500 Failed to update tenant
 * @apiExample {curl} Example Request (Update Status):
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"status": "suspended"}' \
 *        "https://api.everydaytech.au/tenants/5"
 * @apiExample {curl} Example Request (Update Name and Status):
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Acme Corporation (EMEA)",
 *          "status": "active"
 *        }' \
 *        "https://api.everydaytech.au/tenants/5"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "tenant_id": 5,
 *     "name": "Acme Corporation (EMEA)",
 *     "subdomain": "acmecorp",
 *     "status": "active",
 *     "is_msp": false,
 *     "updated_at": "2024-03-11T17:00:00.000Z"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/tenants.createTenant} for creating tenants
 * @see {@link module:routes/tenants.updateMSPStatus} for changing MSP status
 */
router.put('/:id', requireRoot, async (req, res) => {
  const { id } = req.params;
  const { name, status } = req.body;
 
  // Prevent changing subdomain after creation (would break existing users/links)
  const allowedStatuses = ['active', 'suspended', 'inactive'];
  if (status && !allowedStatuses.includes(status)) {
    return res.status(400).json({ 
      error: `Status must be one of: ${allowedStatuses.join(', ')}` 
    });
  }
 
  try {
    const result = await pool.query(
      `UPDATE tenants 
       SET name = COALESCE($1, name),
           status = COALESCE($2, status),
           updated_at = NOW()
       WHERE tenant_id = $3
       RETURNING *`,
      [name, status, id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Tenant not found' });
    }
 
    // Log the action
    await pool.query(
      `INSERT INTO tenant_audit_log (user_id, tenant_id, action, resource_type, details)
       VALUES ($1, $2, 'update', 'tenant', $3)`,
      [
        req.user.userId,
        id,
        JSON.stringify({ name, status })
      ]
    );
 
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating tenant:', err);
    res.status(500).json({ error: 'Failed to update tenant' });
  }
});
 
/**
 * @api {get} /tenants/:id/audit Get Tenant Audit Log
 * @apiName GetTenantAudit
 * @apiGroup Tenants
 * @apiDescription Retrieves paginated audit log for a specific tenant. Shows all actions performed
 * on tenant (create, update, delete, user creation, MSP status changes) with user attribution
 * and detailed change tracking. Root MSP administrators only.
 * 
 * **Logged Actions:**
 * - create: Tenant creation
 * - update: Name/status changes
 * - delete: Tenant deletion
 * - create (user): User added to tenant
 * - update_msp_status: MSP flag toggled
 * 
 * **Audit Fields:**
 * - action: Operation type
 * - resource_type: 'tenant' or 'user'
 * - resource_id: ID of affected resource (user_id for user operations)
 * - details: JSON payload with change details
 * - user_name, user_email: Who performed action (from users table join)
 * - created_at: When action occurred
 * 
 * **Pagination:**
 * - Default: 50 records per page
 * - Sorted by created_at DESC (newest first)
 * - Total count provided for pagination UI
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiParam {number} [limit=50] Records per page (max: system-defined)
 * @apiParam {number} [page=1] Page number (1-indexed)
 * @apiSuccess {object[]} logs Array of audit log entries
 * @apiSuccess {number} logs.id Audit log entry ID
 * @apiSuccess {number} logs.user_id User who performed action
 * @apiSuccess {number} logs.tenant_id Tenant ID
 * @apiSuccess {string} logs.action Action type
 * @apiSuccess {string} logs.resource_type Resource type (tenant, user)
 * @apiSuccess {number} logs.resource_id Resource ID (if applicable)
 * @apiSuccess {Object} logs.details JSON details of action/changes
 * @apiSuccess {string} logs.created_at Timestamp when action occurred
 * @apiSuccess {string} logs.user_name Name of user who performed action
 * @apiSuccess {string} logs.user_email Email of user who performed action
 * @apiSuccess {number} total Total count of audit records for this tenant
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {Number} 500 Failed to fetch audit log
 * 
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants/5/audit?limit=50&page=1"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "logs": [
 *       {
 *         "id": 142,
 *         "user_id": 1,
 *         "tenant_id": 5,
 *         "action": "update",
 *         "resource_type": "tenant",
 *         "resource_id": null,
 *         "details": {"name": "Acme Corporation (EMEA)", "status": "active"},
 *         "created_at": "2024-03-11T17:00:00.000Z",
 *         "user_name": "Admin User",
 *         "user_email": "admin@everydaytech.au"
 *       },
 *       {
 *         "id": 89,
 *         "user_id": 1,
 *         "tenant_id": 5,
 *         "action": "create",
 *         "resource_type": "user",
 *         "resource_id": 23,
 *         "details": {"username": "jdoe", "email": "jdoe@acme.com", "role": "admin"},
 *         "created_at": "2024-02-15T14:30:00.000Z",
 *         "user_name": "Admin User",
 *         "user_email": "admin@everydaytech.au"
 *       }
 *     ],
 *     "total": 47
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/tenants.getImpersonationAudit} for impersonation audit trail
 */
router.get('/:id/audit', requireRoot, async (req, res) => {
  const { id } = req.params;
  const limit = parseInt(req.query.limit) || 50;
  const page = parseInt(req.query.page) || 1;
  const offset = (page - 1) * limit;
 
  try {
    const result = await pool.query(
      `SELECT 
         a.*,
         u.name as user_name,
         u.email as user_email
       FROM tenant_audit_log a
       LEFT JOIN users u ON a.user_id = u.user_id
       WHERE a.tenant_id = $1
       ORDER BY a.created_at DESC
       LIMIT $2 OFFSET $3`,
      [id, limit, offset]
    );
 
    const countResult = await pool.query(
      'SELECT COUNT(*) FROM tenant_audit_log WHERE tenant_id = $1',
      [id]
    );
 
    res.json({
      logs: result.rows,
      total: parseInt(countResult.rows[0].count)
    });
  } catch (err) {
    console.error('Error fetching audit log:', err);
    res.status(500).json({ error: 'Failed to fetch audit log' });
  }
});
 
/**
 * @api {post} /tenants/:id/users Create Tenant User
 * @apiName CreateTenantUser
 * @apiGroup Tenants
 * @apiDescription Creates a new user account within a specific tenant. Root MSP administrators only.
 * Allows MSP to provision user accounts for customer tenants. Password hashed with bcrypt (10 rounds).
 * User creation logged to tenant audit trail.
 * 
 * **User Roles:**
 * - owner: Full administrative access (billing, user management, all features)
 * - admin: Administrative access (user management, most features)
 * - agent: Standard technician access (tickets, customers, limited admin)
 * - read: Read-only access (reporting, viewing only)
 * 
 * **Password Security:**
 * - Bcrypt hashing with 10-round salt
 * - Plain text password never stored
 * - User can change password via /users/me/password endpoint
 * 
 * **Validation:**
 * - Username, email, password required
 * - Email must be unique across all tenants
 * - Username must be unique within tenant
 * - Role validated against allowed roles
 * 
 * **Audit Logging:**
 * User creation logged to tenant_audit_log with username, email, role in JSON details.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiParam {string} username Username (required, unique within tenant)
 * @apiParam {string} email Email address (required, unique globally)
 * @apiParam {string} password Plain text password (required, will be hashed)
 * @apiParam {string} [role=agent] User role: owner, admin, agent, or read (default: agent)
 * @apiSuccess {number} user_id Generated user ID
 * @apiSuccess {number} tenant_id Tenant ID
 * @apiSuccess {string} username Username
 * @apiSuccess {string} email Email address
 * @apiParam {string} role Assigned role
 * @apiSuccess {string} created_at User creation timestamp
 * @apiError {number} 400 Username, email, and password are required
 * @apiError {number} 400 Role must be one of: owner, admin, agent, read
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {Number} 404 Tenant not found
 * @apiError {Number} 409 Username or email already exists
 * @apiError {Number} 500 Failed to create user
 * 
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "username": "jdoe",
 *          "email": "jdoe@acme.com",
 *          "password": "SecurePass123!",
 *          "role": "admin"
 *        }' \
 *        "https://api.everydaytech.au/tenants/5/users"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "user_id": 23,
 *     "tenant_id": 5,
 *     "username": "jdoe",
 *     "email": "jdoe@acme.com",
 *     "role": "admin",
 *     "created_at": "2024-03-11T17:15:00.000Z"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/users} for user management API
 * @see {@link module:routes/tenants.getTenant} for tenant details with user count
 */
router.post('/:id/users', requireRoot, async (req, res) => {
  const { id } = req.params;
  const { username, email, password, role } = req.body;
 
  if (!username || !email || !password) {
    return res.status(400).json({ error: 'Username, email, and password are required' });
  }
 
  const allowedRoles = ['owner', 'admin', 'agent', 'read'];
  const userRole = role || 'agent';
  
  if (!allowedRoles.includes(userRole)) {
    return res.status(400).json({ 
      error: `Role must be one of: ${allowedRoles.join(', ')}` 
    });
  }
 
  try {
    // Verify tenant exists
    const tenantCheck = await pool.query(
      'SELECT tenant_id FROM tenants WHERE tenant_id = $1',
      [id]
    );
 
    if (tenantCheck.rows.length === 0) {
      return res.status(404).json({ error: 'Tenant not found' });
    }
 
    const bcrypt = require('bcrypt');
    const hashedPassword = await bcrypt.hash(password, 10);
 
    const result = await pool.query(
      `INSERT INTO users (tenant_id, username, email, password, role)
       VALUES ($1, $2, $3, $4, $5)
       RETURNING user_id, tenant_id, username, email, role, created_at`,
      [id, username, email, hashedPassword, userRole]
    );
 
    // Log the action
    await pool.query(
      `INSERT INTO tenant_audit_log (user_id, tenant_id, action, resource_type, resource_id, details)
       VALUES ($1, $2, 'create', 'user', $3, $4)`,
      [
        req.user.userId,
        id,
        result.rows[0].user_id,
        JSON.stringify({ username, email, role: userRole })
      ]
    );
 
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('Error creating user:', err);
    
    if (err.code === '23505') { // Unique violation
      return res.status(409).json({ error: 'Username or email already exists' });
    }
    
    res.status(500).json({ error: 'Failed to create user' });
  }
});
 
/**
 * @api {put} /tenants/:id/msp Update MSP Status
 * @apiName UpdateMSPStatus
 * @apiGroup Tenants
 * @apiDescription Toggles MSP (Managed Service Provider) status for a tenant. Root MSP administrators
 * only. MSP flag grants elevated privileges including multi-tenant visibility and impersonation.
 * Use with extreme caution - MSP tenants have platform-wide access.
 * 
 * **MSP Privileges:**
 * - View/manage all tenants' data (customers, tickets, invoices, etc.)
 * - Impersonate users in other tenants
 * - Create and delete tenants
 * - Platform-wide reporting and analytics
 * - Root-level administrative functions
 * 
 * **Security Warning:**
 * Only grant MSP status to trusted platform administrators. MSP users bypass tenant isolation
 * and can access all data across the platform.
 * 
 * **Audit Logging:**
 * MSP status changes logged to tenant_audit_log with details JSON showing new is_msp value.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiParam {boolean} is_msp New MSP status (required)
 * @apiSuccess {number} tenant_id Tenant ID
 * @apiSuccess {string} name Tenant name
 * @apiSuccess {boolean} is_msp Updated MSP status
 * @apiSuccess {string} updated_at Last update timestamp
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {number} 404 Tenant not found
 * @apiError {number} 500 Failed to update MSP status
 * @apiExample {curl} Example Request (Grant MSP):
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"is_msp": true}' \
 *        "https://api.everydaytech.au/tenants/5/msp"
 * @apiExample {curl} Example Request (Revoke MSP):
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"is_msp": false}' \
 *        "https://api.everydaytech.au/tenants/5/msp"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "tenant_id": 5,
 *     "name": "Acme Corporation",
 *     "subdomain": "acmecorp",
 *     "is_msp": true,
 *     "updated_at": "2024-03-11T17:30:00.000Z"
 *   }
 * @since 1.0.0
 * @see {@link module:routes/auth.impersonate} for MSP tenant impersonation
 * @see {@link module:routes/tenants.updateTenant} for general tenant updates
 */
router.put('/:id/msp', requireRoot, async (req, res) => {
  const { id } = req.params;
  const { is_msp } = req.body;
 
  if (typeof is_msp !== 'boolean') {
    return res.status(400).json({ error: 'is_msp must be a boolean value' });
  }
 
  try {
    const result = await pool.query(
      `UPDATE tenants 
       SET is_msp = $1, updated_at = NOW()
       WHERE tenant_id = $2
       RETURNING *`,
      [is_msp, id]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Tenant not found' });
    }
 
    // Log the action
    await pool.query(
      `INSERT INTO tenant_audit_log (user_id, tenant_id, action, resource_type, details)
       VALUES ($1, $2, 'update_msp_status', 'tenant', $3)`,
      [
        req.user.userId,
        id,
        JSON.stringify({ is_msp })
      ]
    );
 
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating MSP status:', err);
    res.status(500).json({ error: 'Failed to update MSP status' });
  }
});
 
/**
 * @api {delete} /tenants/:id Delete Tenant
 * @apiName DeleteTenant
 * @apiGroup Tenants
 * @apiDescription Permanently deletes a tenant. Root MSP administrators only. Prevents deletion of
 * MSP tenants. By default, requires tenant to have no related data (users, customers, tickets, etc.).
 * Use ?force=true query parameter to cascade delete all related data.
 * 
 * **Deletion Safety:**
 * - Cannot delete root/MSP tenants (prevents platform lockout)
 * - Default: Blocks deletion if any related data exists
 * - Force mode: Cascade deletes all related data in proper order
 * - Always deletes audit logs and notifications (non-critical data)
 * 
 * **Related Data Check:**
 * System counts records in:
 * - users, customers, tickets, invoices, contracts
 * - agents (RMM), domains, purchase_orders
 * 
 * If ANY count > 0 and force != 'true', deletion blocked with detailed error showing counts.
 * 
 * **Force Delete Cascade Order:**
 * 1. Tickets and attachments/comments
 * 2. Invoices and line items
 * 3. Purchase orders and lines
 * 4. Contracts and line items
 * 5. Domains and DNS records
 * 6. Agents and customers
 * 7. Products and quotes
 * 8. Users (last, due to foreign key dependencies)
 * 9. Tenant record
 * 
 * **Transaction Safety:**
 * All operations in database transaction. Rollback on any error prevents partial deletions.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiParam {boolean} [force=false] Query parameter: ?force=true to cascade delete all data
 * @apiSuccess {string} message Success message
 * @apiSuccess {object} tenant Deleted tenant object
 * @apiSuccess {boolean} cascade_deleted Whether force delete was used
 * @apiError {number} 400 Cannot delete root/MSP tenant
 * @apiError {number} 400 Cannot delete tenant with related data (without force)
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {number} 404 Tenant not found
 * @apiError {number} 500 Failed to delete tenant
 * @apiExample {curl} Example Request (Safe Delete):
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants/5"
 * @apiExample {curl} Example Request (Force Delete):
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants/5?force=true"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Tenant deleted successfully",
 *     "tenant": {
 *       "tenant_id": 5,
 *       "name": "Acme Corporation",
 *       "subdomain": "acmecorp"
 *     },
 *     "cascade_deleted": true
 *   }
 * @apiExample {json} Error Response (Has Data):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Cannot delete tenant with related data",
 *     "details": {
 *       "user_count": "23",
 *       "customer_count": "145",
 *       "ticket_count": "892",
 *       "invoice_count": "1247",
 *       "agent_count": "432",
 *       "contract_count": "89",
 *       "domain_count": "15",
 *       "purchase_order_count": "34"
 *     },
 *     "message": "Please delete all users, customers, tickets, invoices, contracts, agents, domains, and purchase orders first. Or use ?force=true to cascade delete all data."
 *   }
 * @apiExample {json} Error Response (MSP Tenant):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Cannot delete root/MSP tenant",
 *     "message": "The MSP tenant cannot be deleted as it manages the system"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/tenants.updateTenant} for suspending tenants (soft delete alternative)
 */
router.delete('/:id', requireRoot, async (req, res) => {
  const { id } = req.params;
  const { force } = req.query; // Allow ?force=true to delete with related data
 
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
    // Check if tenant exists
    const checkQuery = await client.query(
      'SELECT is_msp, name FROM tenants WHERE tenant_id = $1',
      [id]
    );
 
    if (checkQuery.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Tenant not found' });
    }
 
    const tenant = checkQuery.rows[0];
 
    // Prevent deleting root/MSP tenant
    if (tenant.is_msp) {
      await client.query('ROLLBACK');
      return res.status(400).json({ 
        error: 'Cannot delete root/MSP tenant',
        message: 'The MSP tenant cannot be deleted as it manages the system'
      });
    }
 
    // Always delete audit log and notifications (they're just logging/notification data)
    await client.query('DELETE FROM tenant_audit_log WHERE tenant_id = $1', [id]);
    await client.query('DELETE FROM notifications WHERE tenant_id = $1', [id]);
 
    // Check for related data
    const relatedCheck = await client.query(`
      SELECT 
        (SELECT COUNT(*) FROM users WHERE tenant_id = $1) as user_count,
        (SELECT COUNT(*) FROM customers WHERE tenant_id = $1) as customer_count,
        (SELECT COUNT(*) FROM tickets WHERE tenant_id = $1) as ticket_count,
        (SELECT COUNT(*) FROM invoices WHERE tenant_id = $1) as invoice_count,
        (SELECT COUNT(*) FROM agents WHERE tenant_id = $1) as agent_count,
        (SELECT COUNT(*) FROM contracts WHERE tenant_id = $1) as contract_count,
        (SELECT COUNT(*) FROM domains WHERE tenant_id = $1) as domain_count,
        (SELECT COUNT(*) FROM purchase_orders WHERE tenant_id = $1) as purchase_order_count
    `, [id]);
 
    const counts = relatedCheck.rows[0];
    const hasRelatedData = Object.values(counts).some(count => parseInt(count) > 0);
 
    if (hasRelatedData && force !== 'true') {
      await client.query('ROLLBACK');
      return res.status(400).json({ 
        error: 'Cannot delete tenant with related data',
        details: counts,
        message: 'Please delete all users, customers, tickets, invoices, contracts, agents, domains, and purchase orders first. Or use ?force=true to cascade delete all data.'
      });
    }
 
    // If force=true, cascade delete all related data
    if (force === 'true') {
      console.log(`[Tenant Delete] Force deleting tenant ${id} with all related data...`);
      
      // Delete in order to respect foreign keys
      // Tickets and related
      await client.query('DELETE FROM ticket_comments WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM ticket_attachments WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM tickets WHERE tenant_id = $1', [id]);
      
      // Invoices and related
      await client.query('DELETE FROM invoice_items WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM invoices WHERE tenant_id = $1', [id]);
      
      // Purchase orders
      await client.query('DELETE FROM purchase_order_lines WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM purchase_orders WHERE tenant_id = $1', [id]);
      
      // Contracts
      await client.query('DELETE FROM contract_items WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM contracts WHERE tenant_id = $1', [id]);
      
      // Domains
      await client.query('DELETE FROM dns_records WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM domains WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM domain_registration_requests WHERE tenant_id = $1', [id]);
      
      // Agents and customers (before products/quotes since they may reference products)
      await client.query('DELETE FROM agents WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM customers WHERE tenant_id = $1', [id]);
      
      // Products and quotes (after customers)
      await client.query('DELETE FROM quote_items WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM quotes WHERE tenant_id = $1', [id]);
      await client.query('DELETE FROM products WHERE tenant_id = $1', [id]);
      
      // Users last
      await client.query('DELETE FROM users WHERE tenant_id = $1', [id]);
      
      console.log(`[Tenant Delete] Deleted all related data for tenant ${id}`);
    }
 
    // Delete tenant
    const result = await client.query(
      'DELETE FROM tenants WHERE tenant_id = $1 RETURNING *',
      [id]
    );
 
    await client.query('COMMIT');
 
    res.json({ 
      message: 'Tenant deleted successfully', 
      tenant: result.rows[0],
      cascade_deleted: force === 'true'
    });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error deleting tenant:', err);
    res.status(500).json({ 
      error: 'Failed to delete tenant', 
      details: err.message,
      constraint: err.constraint || null
    });
  } finally {
    client.release();
  }
});
 
/**
 * @api {get} /tenants/:id/impersonation-audit Get Impersonation Audit Log
 * @apiName GetImpersonationAudit
 * @apiGroup Tenants
 * @apiDescription Retrieves impersonation audit log for a specific tenant. Shows all times MSP
 * administrators impersonated users within this tenant. Root MSP administrators only.
 * **Currently not implemented** - returns placeholder response.
 * 
 * **Future Implementation:**
 * Will log:
 * - Who performed impersonation (admin user_id, email)
 * - Which user was impersonated (impersonated_user_id)
 * - Which tenant was accessed (impersonated_tenant_id)
 * - Start timestamp (when impersonation began)
 * - End timestamp (when exited impersonation)
 * - Actions performed during impersonation (optional detailed logging)
 * 
 * **Use Cases:**
 * - Security auditing of MSP administrator actions
 * - Compliance reporting (who accessed customer data)
 * - Incident investigation (trace actions to specific admin)
 * - Customer transparency (show support session history)
 * 
 * **Pagination:**
 * When implemented, will support limit/offset pagination similar to tenant audit log.
 * @apiHeader {string} Authorization Bearer JWT token (root MSP admin required)
 * @apiParam {number} id Tenant ID (in URL path)
 * @apiParam {number} [limit=50] Records per page (when implemented)
 * @apiParam {number} [offset=0] Offset for pagination (when implemented)
 * @apiSuccess {object[]} logs Empty array (placeholder)
 * @apiSuccess {string} message "Impersonation audit log not yet implemented"
 * @apiError {number} 403 Forbidden (not root MSP admin)
 * @apiError {number} 500 Failed to fetch impersonation audit log
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/tenants/5/impersonation-audit?limit=50&offset=0"
 * @apiExample {json} Current Response (Not Implemented):
 *   HTTP/1.1 200 OK
 *   {
 *     "logs": [],
 *     "message": "Impersonation audit log not yet implemented"
 *   }
 * @apiExample {json} Future Response (When Implemented):
 *   HTTP/1.1 200 OK
 *   {
 *     "logs": [
 *       {
 *         "id": 42,
 *         "admin_user_id": 1,
 *         "admin_email": "admin@everydaytech.au",
 *         "impersonated_user_id": 23,
 *         "impersonated_email": "jdoe@acme.com",
 *         "impersonated_tenant_id": 5,
 *         "start_timestamp": "2024-03-11T14:30:00.000Z",
 *         "end_timestamp": "2024-03-11T14:45:00.000Z",
 *         "duration_minutes": 15
 *       }
 *     ]
 *   }
 * @since 1.0.0 (endpoint exists, feature not implemented)
 * @see {@link module:routes/auth.impersonate} for impersonation feature
 * @see {@link module:routes/tenants.getTenantAudit} for general tenant audit log
 */
router.get('/:id/impersonation-audit', requireRoot, async (req, res) => {
  const { id } = req.params;
  const limit = parseInt(req.query.limit) || 50;
  const offset = parseInt(req.query.offset) || 0;
 
  try {
    // TODO: Implement when impersonation_audit table is created
    res.json({ logs: [], message: 'Impersonation audit log not yet implemented' });
    
    // const result = await pool.query(
    //   `SELECT * FROM impersonation_audit
    //    WHERE impersonated_tenant_id = $1
    //    ORDER BY timestamp DESC
    //    LIMIT $2 OFFSET $3`,
    //   [id, limit, offset]
    // );
    // res.json({ logs: result.rows });
  } catch (err) {
    console.error('Error fetching impersonation audit log:', err);
    res.status(500).json({ error: 'Failed to fetch impersonation audit log' });
  }
});
 
module.exports = router;