office365.js

Go to the documentation of this file.

/**
 * @file office365.js
 * @module Office365Routes
 * @description Office 365 / Pax8 integration API routes. Manages Microsoft 365 subscription lifecycle through Pax8 partner API, including configuration management, customer synchronization, product catalog access, and subscription CRUD operations. Multi-tenant aware with full isolation.
 * @see {@link ../services/pax8Service} for Pax8 API integration
 * @see {@link ../services/db} for database connection
 * @see {@link ../middleware/auth} for authentication middleware
 * @see {@link ../middleware/tenant} for tenant context utilities
 * @apiDefine Office365Group Office365
 * @apiGroup Office365
 * @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 500) ServerError Internal server error or Pax8 API error.
 */
 
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
const Pax8Service = require('../services/pax8Service');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
// ========================================
// Configuration Management
// ========================================
 
/**
 * @api {get} /office365/config Get Pax8 configuration
 * @apiName GetPax8Config
 * @apiGroup Office365
 * @apiDescription Retrieve Pax8 API configuration for the current tenant (without exposing secrets). Returns configuration status and metadata.
 * @apiSuccess {boolean} configured Whether Pax8 is configured for this tenant.
 * @apiSuccess {object} [config] Configuration object (if configured).
 * @apiSuccess {string} config.config_id Configuration ID.
 * @apiSuccess {string} config.tenant_id Tenant ID.
 * @apiSuccess {string} config.client_id Pax8 client ID.
 * @apiSuccess {string} config.pax8_company_id Pax8 company ID.
 * @apiSuccess {boolean} config.is_active Whether configuration is active.
 * @apiSuccess {Date} config.last_sync_at Last synchronization timestamp.
 * @apiSuccess {string} config.last_sync_status Last sync status (success/failed).
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/config
 */
router.get('/config', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT config_id, tenant_id, client_id, pax8_company_id, is_active, 
              last_sync_at, last_sync_status, sync_error, created_at, updated_at
       FROM pax8_config 
       WHERE tenant_id = $1`,
      [req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.json({ configured: false });
    }
 
    res.json({ configured: true, config: result.rows[0] });
  } catch (err) {
    console.error('Error fetching Pax8 config:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {put} /office365/config Update Pax8 configuration
 * @apiName UpdatePax8Config
 * @apiGroup Office365
 * @apiDescription Update or create Pax8 API configuration for the current tenant. Stores client credentials for Pax8 partner API integration.
 * @apiParam {string} client_id Pax8 OAuth client ID.
 * @apiParam {string} client_secret Pax8 OAuth client secret (will be encrypted).
 * @apiParam {string} [pax8_company_id] Pax8 company ID.
 * @apiSuccess {string} message Success confirmation.
 * @apiSuccess {object} config Saved configuration object.
 * @apiError (Error 400) BadRequest client_id and client_secret are required.
 * @apiExample {curl} Example usage:
 *     curl -X PUT -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" -d '{"client_id":"abc","client_secret":"secret","pax8_company_id":"123"}' https://api.example.com/office365/config
 */
router.put('/config', async (req, res) => {
  const { client_id, client_secret, pax8_company_id } = req.body;
 
  if (!client_id || !client_secret) {
    return res.status(400).json({ error: 'client_id and client_secret are required' });
  }
 
  try {
    // TODO: Encrypt client_secret before storing
    const encrypted_secret = client_secret; // Implement proper encryption
 
    // Upsert config
    const result = await pool.query(
      `INSERT INTO pax8_config (tenant_id, client_id, client_secret_encrypted, pax8_company_id, updated_at)
       VALUES ($1, $2, $3, $4, NOW())
       ON CONFLICT (tenant_id) 
       DO UPDATE SET 
         client_id = $2,
         client_secret_encrypted = $3,
         pax8_company_id = $4,
         updated_at = NOW()
       RETURNING config_id, tenant_id, client_id, pax8_company_id, is_active, created_at, updated_at`,
      [req.tenant.id, client_id, encrypted_secret, pax8_company_id]
    );
 
    res.json({ message: 'Configuration saved successfully', config: result.rows[0] });
  } catch (err) {
    console.error('Error saving Pax8 config:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {post} /office365/config/test Test Pax8 API connection
 * @apiName TestPax8Connection
 * @apiGroup Office365
 * @apiDescription Test the Pax8 API connection with current tenant's configuration. Updates sync status in database.
 * @apiSuccess {boolean} success Connection test result.
 * @apiSuccess {string} message Status message.
 * @apiError (Error 400) BadRequest Connection test failed.
 * @apiError (Error 500) ServerError API error or configuration missing.
 * @apiExample {curl} Example usage:
 *     curl -X POST -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/config/test
 */
router.post('/config/test', async (req, res) => {
  try {
    const pax8 = new Pax8Service(req.tenant.id);
    const success = await pax8.testConnection();
    
    if (success) {
      await pax8.updateSyncStatus('success');
      res.json({ success: true, message: 'Connection successful' });
    } else {
      await pax8.updateSyncStatus('failed', 'Connection test failed');
      res.status(400).json({ success: false, error: 'Connection test failed' });
    }
  } catch (err) {
    console.error('Error testing Pax8 connection:', err);
    await new Pax8Service(req.tenant.id).updateSyncStatus('failed', err.message);
    res.status(500).json({ success: false, error: err.message });
  }
});
 
// ========================================
// Customer Sync
// ========================================
 
/**
 * @api {post} /office365/customers/:customerId/sync Sync customer to Pax8
 * @apiName SyncCustomerToPax8
 * @apiGroup Office365
 * @apiDescription Sync a specific customer to Pax8 (create new company or update existing). Creates mapping record in database for future synchronization. If customer already synced, updates from Pax8.
 * @apiParam {number} customerId Customer ID (URL parameter).
 * @apiSuccess {string} message Success confirmation.
 * @apiSuccess {object} mapping Pax8 customer mapping object.
 * @apiSuccess {string} mapping.pax8_company_id Pax8 company ID.
 * @apiSuccess {string} mapping.pax8_company_name Pax8 company name.
 * @apiSuccess {string} mapping.sync_status Sync status (synced/failed).
 * @apiError (Error 404) NotFound Customer not found.
 * @apiError (Error 500) ServerError Pax8 API error or sync failed.
 * @apiExample {curl} Example usage:
 *     curl -X POST -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/customers/123/sync
 */
router.post('/customers/:customerId/sync', async (req, res) => {
  const customerId = parseInt(req.params.customerId);
 
  try {
    const pax8 = new Pax8Service(req.tenant.id);
 
    // Get local customer
    const customerResult = await pool.query(
      'SELECT * FROM customers WHERE customer_id = $1 AND tenant_id = $2',
      [customerId, req.tenant.id]
    );
 
    if (customerResult.rows.length === 0) {
      return res.status(404).json({ error: 'Customer not found' });
    }
 
    const customer = customerResult.rows[0];
 
    // Check if already synced
    const mappingResult = await pool.query(
      'SELECT * FROM pax8_customers WHERE customer_id = $1 AND tenant_id = $2',
      [customerId, req.tenant.id]
    );
 
    if (mappingResult.rows.length > 0) {
      // Already synced
      const mapping = mappingResult.rows[0];
      const pax8Customer = await pax8.getCompany(mapping.pax8_company_id);
      
      await pool.query(
        'UPDATE pax8_customers SET pax8_company_name = $1, last_synced_at = NOW(), sync_status = $2 WHERE id = $3',
        [pax8Customer.name, 'synced', mapping.id]
      );
 
      return res.json({ 
        message: 'Customer already synced, updated from Pax8', 
        mapping: { ...mapping, pax8_company_name: pax8Customer.name }
      });
    }
 
    // Create in Pax8
    const pax8CustomerData = {
      name: customer.name,
      address: {
        street: customer.address || '',
        city: customer.city || '',
        state: customer.state || '',
        postalCode: customer.postal_code || '',
        country: customer.country || 'AU'
      },
      contact: {
        name: customer.contact_name || customer.name,
        email: customer.email || '',
        phone: customer.phone || ''
      }
    };
 
    const pax8Customer = await pax8.createCompany(pax8CustomerData);
 
    // Save mapping
    const insertResult = await pool.query(
      `INSERT INTO pax8_customers (tenant_id, customer_id, pax8_company_id, pax8_company_name, last_synced_at, sync_status)
       VALUES ($1, $2, $3, $4, NOW(), 'synced')
       RETURNING *`,
      [req.tenant.id, customerId, pax8Customer.id, pax8Customer.name]
    );
 
    res.json({ 
      message: 'Customer synced successfully', 
      mapping: insertResult.rows[0]
    });
  } catch (err) {
    console.error('Error syncing customer:', err);
    
    // Update sync status to failed
    await pool.query(
      `UPDATE pax8_customers 
       SET sync_status = 'failed', sync_error = $1 
       WHERE customer_id = $2 AND tenant_id = $3`,
      [err.message, customerId, req.tenant.id]
    );
    
    res.status(500).json({ error: 'Failed to sync customer', details: err.message });
  }
});
 
/**
 * @api {get} /office365/customers/:customerId/sync-status Get customer sync status
 * @apiName GetCustomerSyncStatus
 * @apiGroup Office365
 * @apiDescription Check if a customer is synced to Pax8 and retrieve sync status details.
 * @apiParam {number} customerId Customer ID (URL parameter).
 * @apiSuccess {boolean} synced Whether customer is synced.
 * @apiSuccess {object} [mapping] Sync mapping object (if synced).
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/customers/123/sync-status
 */
router.get('/customers/:customerId/sync-status', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM pax8_customers WHERE customer_id = $1 AND tenant_id = $2',
      [req.params.customerId, req.tenant.id]
    );
 
    if (result.rows.length === 0) {
      return res.json({ synced: false });
    }
 
    res.json({ synced: true, mapping: result.rows[0] });
  } catch (err) {
    console.error('Error checking sync status:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
// ========================================
// Products & SKUs
// ========================================
 
/**
 * @api {get} /office365/products List Microsoft 365 products from Pax8
 * @apiName ListPax8Products
 * @apiGroup Office365
 * @apiDescription Retrieve available Microsoft 365 products from Pax8 catalog. Supports filtering by vendor and other query parameters.
 * @apiParam {string} [vendor=Microsoft] Filter by vendor name.
 * @apiSuccess {object[]} products Array of product objects from Pax8.
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/products?vendor=Microsoft
 */
router.get('/products', async (req, res) => {
  try {
    const pax8 = new Pax8Service(req.tenant.id);
    const filters = {
      vendor: req.query.vendor || 'Microsoft',
      ...req.query
    };
 
    const products = await pax8.listProducts(filters);
 
    res.json({ products: products.content || products });
  } catch (err) {
    console.error('Error fetching products:', err);
    res.status(500).json({ error: 'Failed to fetch products', details: err.message });
  }
});
 
/**
 * @api {get} /office365/products/:productId Get product details
 * @apiName GetPax8Product
 * @apiGroup Office365
 * @apiDescription Retrieve detailed information about a specific product from Pax8 catalog by product ID.
 * @apiParam {string} productId Pax8 product ID (URL parameter).
 * @apiSuccess {object} product Product details object from Pax8.
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/products/prod-123
 */
router.get('/products/:productId', async (req, res) => {
  try {
    const pax8 = new Pax8Service(req.tenant.id);
    const product = await pax8.getProduct(req.params.productId);
 
    res.json({ product });
  } catch (err) {
    console.error('Error fetching product:', err);
    res.status(500).json({ error: 'Failed to fetch product', details: err.message });
  }
});
 
/**
 * @api {post} /office365/products/sync Sync products from Pax8 to local database
 * @apiName SyncPax8Products
 * @apiGroup Office365
 * @apiDescription Fetch Microsoft 365 products from Pax8 and sync to local database for caching and offline access. Updates existing products or inserts new ones.
 * @apiParam {string} [vendor=Microsoft] Filter by vendor name (query param).
 * @apiParam {boolean} [force=false] Force re-sync even if recently synced (query param).
 * @apiSuccess {string} message Success confirmation.
 * @apiSuccess {number} synced Count of newly synced products.
 * @apiSuccess {number} updated Count of updated products.
 * @apiSuccess {number} skipped Count of skipped products.
 * @apiExample {curl} Example usage:
 *     curl -X POST -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/products/sync?vendor=Microsoft&force=true
 */
router.post('/products/sync', async (req, res) => {
  try {
    const pax8 = new Pax8Service(req.tenant.id);
    const vendor = req.query.vendor || 'Microsoft';
    const force = req.query.force === 'true';
 
    console.log(`[Office365] Starting product sync for vendor: ${vendor}`);
 
    // Fetch products from Pax8
    const productsResponse = await pax8.listProducts({ vendor });
    const products = productsResponse.content || productsResponse || [];
 
    console.log(`[Office365] Found ${products.length} products from Pax8`);
 
    let syncedCount = 0;
    let updatedCount = 0;
    let skippedCount = 0;
 
    for (const pax8Product of products) {
      try {
        // Check if product already exists
        const existingProduct = await pool.query(
          'SELECT product_id, last_synced_at FROM products WHERE pax8_product_id = $1',
          [pax8Product.id]
        );
 
        // Skip if recently synced (within last hour) unless force=true
        if (existingProduct.rows.length > 0 && !force) {
          const lastSynced = existingProduct.rows[0].last_synced_at;
          if (lastSynced && (Date.now() - new Date(lastSynced).getTime()) < 3600000) {
            skippedCount++;
            continue;
          }
        }
 
        const productData = {
          name: pax8Product.name || pax8Product.productName,
          description: pax8Product.description || pax8Product.productDescription || '',
          supplier: vendor,
          is_service: true, // Office 365 is a service
          divisible: true, // Can be sold per user
          is_stock: false, // Digital product
          price_retail: pax8Product.pricing?.retail || 0,
          price_ex_tax: pax8Product.pricing?.cost || 0,
          pax8_product_id: pax8Product.id,
          pax8_vendor: vendor,
          pax8_category: pax8Product.category || 'Software',
          pax8_subcategory: pax8Product.subcategory || '',
          pax8_sku: pax8Product.sku || pax8Product.vendorSku || '',
          billing_term: pax8Product.billingTerm || 'monthly',
          is_auto_renew: pax8Product.autoRenew !== false,
          pax8_metadata: JSON.stringify(pax8Product),
          is_active: pax8Product.status === 'Active' || pax8Product.available !== false,
          last_synced_at: new Date()
        };
 
        if (existingProduct.rows.length > 0) {
          // Update existing product
          await pool.query(
            `UPDATE products SET 
               name = $1, description = $2, supplier = $3, 
               price_retail = $4, price_ex_tax = $5, 
               pax8_vendor = $6, pax8_category = $7, pax8_subcategory = $8,
               pax8_sku = $9, billing_term = $10, is_auto_renew = $11,
               pax8_metadata = $12, is_active = $13, last_synced_at = $14
             WHERE pax8_product_id = $15`,
            [
              productData.name, productData.description, productData.supplier,
              productData.price_retail, productData.price_ex_tax,
              productData.pax8_vendor, productData.pax8_category, productData.pax8_subcategory,
              productData.pax8_sku, productData.billing_term, productData.is_auto_renew,
              productData.pax8_metadata, productData.is_active, productData.last_synced_at,
              pax8Product.id
            ]
          );
          updatedCount++;
        } else {
          // Insert new product
          await pool.query(
            `INSERT INTO products (
               name, description, supplier, is_service, divisible, is_stock,
               price_retail, price_ex_tax, pax8_product_id, pax8_vendor,
               pax8_category, pax8_subcategory, pax8_sku, billing_term,
               is_auto_renew, pax8_metadata, is_active, last_synced_at
             ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)`,
            [
              productData.name, productData.description, productData.supplier,
              productData.is_service, productData.divisible, productData.is_stock,
              productData.price_retail, productData.price_ex_tax, productData.pax8_product_id,
              productData.pax8_vendor, productData.pax8_category, productData.pax8_subcategory,
              productData.pax8_sku, productData.billing_term, productData.is_auto_renew,
              productData.pax8_metadata, productData.is_active, productData.last_synced_at
            ]
          );
          syncedCount++;
        }
      } catch (productErr) {
        console.error(`[Office365] Error syncing product ${pax8Product.id}:`, productErr);
        // Continue with next product
      }
    }
 
    console.log(`[Office365] Sync complete: ${syncedCount} new, ${updatedCount} updated, ${skippedCount} skipped`);
 
    res.json({
      success: true,
      message: 'Products synced successfully',
      stats: {
        total: products.length,
        synced: syncedCount,
        updated: updatedCount,
        skipped: skippedCount
      }
    });
  } catch (err) {
    console.error('Error syncing products:', err);
    res.status(500).json({ error: 'Failed to sync products', details: err.message });
  }
});
 
// ========================================
// Subscriptions
// ========================================
 
/**
 * @api {get} /office365/subscriptions List Office 365 subscriptions
 * @apiName ListOffice365Subscriptions
 * @apiGroup Office365
 * @apiDescription Retrieve all Office 365 subscriptions for the current tenant with customer and contract details.
 * @apiSuccess {object[]} subscriptions Array of subscription objects.
 * @apiSuccess {number} subscriptions.subscription_id Subscription ID.
 * @apiSuccess {string} subscriptions.pax8_subscription_id Pax8 subscription ID.
 * @apiSuccess {number} subscriptions.customer_id Customer ID.
 * @apiSuccess {string} subscriptions.customer_name Customer name.
 * @apiSuccess {string} subscriptions.sku_name Product/SKU name.
 * @apiSuccess {number} subscriptions.quantity License quantity.
 * @apiSuccess {string} subscriptions.status Subscription status.
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/subscriptions
 */
router.get('/subscriptions', async (req, res) => {
  try {
    const { clause, params } = getTenantFilter(req, 's');
    let query = `
      SELECT s.*, c.name as customer_name, co.contract_name
      FROM office365_subscriptions s 
      LEFT JOIN customers c ON s.customer_id = c.customer_id
      LEFT JOIN contracts co ON s.contract_id = co.contract_id
    `;
    
    if (clause) {
      query += ` WHERE ${clause}`;
    }
    
    query += ' ORDER BY s.created_at DESC';
 
    const result = await pool.query(query, params);
    res.json({ subscriptions: result.rows });
  } catch (err) {
    console.error('Error fetching subscriptions:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /office365/subscriptions/:id Get subscription details
 * @apiName GetOffice365Subscription
 * @apiGroup Office365
 * @apiDescription Retrieve detailed subscription information including license assignments.
 * @apiParam {number} id Subscription ID (URL parameter).
 * @apiSuccess {object} subscription Subscription details.
 * @apiSuccess {object[]} assignments License assignments for this subscription.
 * @apiError (Error 404) NotFound Subscription not found.
 * @apiExample {curl} Example usage:
 *     curl -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/subscriptions/123
 */
router.get('/subscriptions/:id', async (req, res) => {
  try {
    const subResult = await pool.query(
      `SELECT s.*, c.name as customer_name, co.contract_name
       FROM office365_subscriptions s 
       LEFT JOIN customers c ON s.customer_id = c.customer_id
       LEFT JOIN contracts co ON s.contract_id = co.contract_id
       WHERE s.subscription_id = $1 AND s.tenant_id = $2`,
      [req.params.id, req.tenant.id]
    );
 
    if (subResult.rows.length === 0) {
      return res.status(404).json({ error: 'Subscription not found' });
    }
 
    const subscription = subResult.rows[0];
 
    // Get license assignments
    const assignmentsResult = await pool.query(
      'SELECT * FROM office365_license_assignments WHERE subscription_id = $1 ORDER BY assigned_at DESC',
      [req.params.id]
    );
 
    res.json({ 
      subscription,
      assignments: assignmentsResult.rows
    });
  } catch (err) {
    console.error('Error fetching subscription:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {post} /office365/subscriptions Create new subscription
 * @apiName CreateOffice365Subscription
 * @apiGroup Office365
 * @apiDescription Purchase Microsoft 365 licenses via Pax8 and create subscription record. Customer must be synced to Pax8 first.
 * @apiParam {number} customer_id Customer ID.
 * @apiParam {string} product_id Pax8 product ID.
 * @apiParam {number} quantity License quantity.
 * @apiParam {string} [sku] Product SKU.
 * @apiParam {string} [billing_cycle=monthly] Billing cycle (monthly/yearly).
 * @apiParam {number} [contract_id] Associated contract ID.
 * @apiSuccess {string} message Success confirmation.
 * @apiSuccess {object} subscription Created subscription object.
 * @apiError (Error 400) BadRequest Required fields missing or customer not synced.
 * @apiError (Error 500) ServerError Pax8 API error or database error.
 * @apiExample {curl} Example usage:
 *     curl -X POST -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" -d '{"customer_id":123,"product_id":"prod-abc","quantity":10}' https://api.example.com/office365/subscriptions
 */
router.post('/subscriptions', async (req, res) => {
  const { customer_id, product_id, sku, quantity, billing_cycle, contract_id } = req.body;
 
  if (!customer_id || !product_id || !quantity) {
    return res.status(400).json({ error: 'customer_id, product_id, and quantity are required' });
  }
 
  try {
    // Get Pax8 customer mapping
    const mappingResult = await pool.query(
      'SELECT * FROM pax8_customers WHERE customer_id = $1 AND tenant_id = $2',
      [customer_id, req.tenant.id]
    );
 
    if (mappingResult.rows.length === 0) {
      return res.status(400).json({ 
        error: 'Customer not synced with Pax8', 
        message: 'Please sync the customer with Pax8 before creating subscriptions' 
      });
    }
 
    const pax8Customer = mappingResult.rows[0];
 
    // Create subscription in Pax8
    const pax8 = new Pax8Service(req.tenant.id);
    const pax8Subscription = await pax8.createSubscription({
      companyId: pax8Customer.pax8_company_id,
      productId: product_id,
      quantity: quantity,
      billingTerm: billing_cycle || 'monthly'
    });
 
    // Save to database
    const insertResult = await pool.query(
      `INSERT INTO office365_subscriptions 
       (tenant_id, customer_id, contract_id, pax8_subscription_id, pax8_company_id,
        product_id, sku, sku_name, quantity, unit_price, total_price, currency,
        status, billing_cycle, start_date, renewal_date, metadata)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
       RETURNING *`,
      [
        req.tenant.id,
        customer_id,
        contract_id,
        pax8Subscription.id,
        pax8Customer.pax8_company_id,
        product_id,
        sku || pax8Subscription.sku,
        pax8Subscription.productName || pax8Subscription.name,
        quantity,
        pax8Subscription.unitPrice || pax8Subscription.price,
        pax8Subscription.totalPrice || (pax8Subscription.price * quantity),
        'AUD',
        'active',
        billing_cycle || 'monthly',
        pax8Subscription.startDate || new Date(),
        pax8Subscription.renewalDate,
        JSON.stringify(pax8Subscription)
      ]
    );
 
    // Log history
    await pool.query(
      `INSERT INTO office365_subscription_history 
       (subscription_id, tenant_id, action, new_quantity, new_status, changed_by, contract_id)
       VALUES ($1, $2, 'created', $3, 'active', $4, $5)`,
      [insertResult.rows[0].subscription_id, req.tenant.id, quantity, req.user.id, contract_id]
    );
 
    res.json({ 
      message: 'Subscription created successfully', 
      subscription: insertResult.rows[0]
    });
  } catch (err) {
    console.error('Error creating subscription:', err);
    res.status(500).json({ error: 'Failed to create subscription', details: err.message });
  }
});
 
/**
 * @api {patch} /office365/subscriptions/:id Update subscription quantity
 * @apiName UpdateOffice365Subscription
 * @apiGroup Office365
 * @apiDescription Update subscription license quantity. Changes are synced to Pax8 and logged in subscription history.
 * @apiParam {number} id Subscription ID (URL parameter).
 * @apiParam {number} quantity New license quantity.
 * @apiSuccess {string} message Success confirmation.
 * @apiSuccess {object} subscription Updated subscription object.
 * @apiError (Error 400) BadRequest Quantity is required.
 * @apiError (Error 404) NotFound Subscription not found.
 * @apiError (Error 500) ServerError Pax8 API error or database error.
 * @apiExample {curl} Example usage:
 *     curl -X PATCH -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" -d '{"quantity":15}' https://api.example.com/office365/subscriptions/123
 */
router.patch('/subscriptions/:id', async (req, res) => {
  const { quantity } = req.body;
 
  if (!quantity || quantity < 1) {
    return res.status(400).json({ error: 'Valid quantity is required' });
  }
 
  try {
    // Get subscription
    const subResult = await pool.query(
      'SELECT * FROM office365_subscriptions WHERE subscription_id = $1 AND tenant_id = $2',
      [req.params.id, req.tenant.id]
    );
 
    if (subResult.rows.length === 0) {
      return res.status(404).json({ error: 'Subscription not found' });
    }
 
    const subscription = subResult.rows[0];
 
    // Update in Pax8
    const pax8 = new Pax8Service(req.tenant.id);
    const pax8Subscription = await pax8.updateSubscription(subscription.pax8_subscription_id, {
      quantity: quantity
    });
 
    // Update database
    const updateResult = await pool.query(
      `UPDATE office365_subscriptions 
       SET quantity = $1, total_price = $2, updated_at = NOW()
       WHERE subscription_id = $3
       RETURNING *`,
      [quantity, pax8Subscription.totalPrice || (quantity * subscription.unit_price), req.params.id]
    );
 
    // Log history
    await pool.query(
      `INSERT INTO office365_subscription_history 
       (subscription_id, tenant_id, action, previous_quantity, new_quantity, changed_by)
       VALUES ($1, $2, 'quantity_changed', $3, $4, $5)`,
      [req.params.id, req.tenant.id, subscription.quantity, quantity, req.user.id]
    );
 
    res.json({ 
      message: 'Subscription updated successfully', 
      subscription: updateResult.rows[0]
    });
  } catch (err) {
    console.error('Error updating subscription:', err);
    res.status(500).json({ error: 'Failed to update subscription', details: err.message });
  }
});
 
/**
 * @api {delete} /office365/subscriptions/:id Cancel subscription
 * @apiName CancelOffice365Subscription
 * @apiGroup Office365
 * @apiDescription Cancel a Microsoft 365 subscription in Pax8 and update status to 'cancelled'. Changes are logged in subscription history.
 * @apiParam {number} id Subscription ID (URL parameter).
 * @apiSuccess {string} message Success confirmation.
 * @apiError (Error 404) NotFound Subscription not found.
 * @apiError (Error 500) ServerError Pax8 API error or database error.
 * @apiExample {curl} Example usage:
 *     curl -X DELETE -H "Authorization: Bearer <token>" -H "X-Tenant-ID: <id>" https://api.example.com/office365/subscriptions/123
 */
router.delete('/subscriptions/:id', async (req, res) => {
  try {
    // Get subscription
    const subResult = await pool.query(
      'SELECT * FROM office365_subscriptions WHERE subscription_id = $1 AND tenant_id = $2',
      [req.params.id, req.tenant.id]
    );
 
    if (subResult.rows.length === 0) {
      return res.status(404).json({ error: 'Subscription not found' });
    }
 
    const subscription = subResult.rows[0];
 
    // Cancel in Pax8
    const pax8 = new Pax8Service(req.tenant.id);
    await pax8.cancelSubscription(subscription.pax8_subscription_id);
 
    // Update database
    await pool.query(
      `UPDATE office365_subscriptions 
       SET status = 'cancelled', end_date = NOW(), updated_at = NOW()
       WHERE subscription_id = $1`,
      [req.params.id]
    );
 
    // Log history
    await pool.query(
      `INSERT INTO office365_subscription_history 
       (subscription_id, tenant_id, action, previous_status, new_status, changed_by)
       VALUES ($1, $2, 'cancelled', $3, 'cancelled', $4)`,
      [req.params.id, req.tenant.id, subscription.status, req.user.id]
    );
 
    res.json({ message: 'Subscription cancelled successfully' });
  } catch (err) {
    console.error('Error cancelling subscription:', err);
    res.status(500).json({ error: 'Failed to cancel subscription', details: err.message });
  }
});
 
module.exports = router;