customers.js

Go to the documentation of this file.

/**
 * @file customers.js
 * @brief Customer Management API Routes
 * @description Comprehensive customer relationship management (CRM) system for MSP/PSA
 * operations. Manages customer records, contacts, and relationships to tickets, invoices,
 * contracts, domains, Office 365 subscriptions, and RMM agents. Multi-tenant isolated with
 * full CRUD operations and hierarchical contact management.
 * 
 * **Core Features:**
 * - Customer CRUD operations (create, read, update, delete)
 * - Multi-tenant isolation (MSP and sub-tenant level)
 * - Customer contact management (primary/secondary contacts)
 * - Status tracking (active/inactive)
 * - Advanced search and filtering
 * - Comprehensive relationship data (tickets, invoices, contracts, etc.)
 * - Redis event publishing for integrations
 * - Safe deletion with referential integrity checks
 * 
 * **Related Entities:**
 * - Tickets: Support tickets for customer
 * - Invoices: Billing records
 * - Contracts: Service agreements
 * - Agents: RMM agents deployed at customer sites
 * - Domains: Registered domain names
 * - Office 365 Subscriptions: Microsoft 365 licenses
 * - Hosting Apps: Deployed web applications
 * 
 * **Database Schema:**
 * - customers: Main customer records
 * - customer_contacts: Contact persons (with primary flag)
 * - Foreign keys to: tickets, invoices, contracts, domains, etc.
 * @module routes/customers
 * @requires express
 * @requires services/db
 * @requires middleware/auth
 * @requires middleware/adminOnly
 * @requires middleware/tenant
 * @requires utils/redis
 * @author RMM-PSA Platform
 * @date 2026-02-10
 * @since 2.0.0
 */
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const requireAdmin = require('../middleware/adminOnly');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /customers List all customers
 * @apiName ListCustomers
 * @apiGroup Customers
 * @apiDescription Retrieves paginated list of customers with advanced filtering capabilities.
 * Supports search by name, status filtering, and date range queries. Results include
 * tenant name for multi-tenant visibility. Tenant-isolated automatically.
 * 
 * **Query Parameters:**
 * - page (default: 1): Page number for pagination
 * - limit (default: 10): Results per page
 * - search: Search in customer name (case-insensitive)
 * - status: Filter by status (active, inactive, or omit for all)
 * - date_from: Filter customers created after this date (ISO 8601)
 * - date_to: Filter customers created before this date (end of day included)
 * 
 * **Performance:**
 * - Indexed queries for fast pagination
 * - LEFT JOIN for tenant name lookup
 * - Separate COUNT query for total
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} [page=1] Page number
 * @apiParam {number} [limit=10] Results per page
 * @apiParam {string} [search] Search query (customer name)
 * @apiParam {string} [status] Status filter (active or inactive)
 * @apiParam {string} [date_from] Start date (ISO 8601)
 * @apiParam {string} [date_to] End date (ISO 8601)
 * @apiSuccess {Array} customers Array of customer objects with tenant names
 * @apiSuccess {number} total Total count of matching customers
 * @apiExample {curl} List Active Customers:
 *   curl -H "Authorization: Bearer eyJhbGc..." \\
 *        "https://api.everydaytech.au/customers?status=active&page=1&limit=20"
 * @apiExample {curl} Search Customers:
 *   curl -H "Authorization: Bearer eyJhbGc..." \\
 *        "https://api.everydaytech.au/customers?search=acme"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "customers": [
 *       {
 *         "customer_id": 42,
 *         "name": "Acme Corporation",
 *         "contact_name": "John Smith",
 *         "email": "john@example.com",
 *         "phone": "+1-555-0123",
 *         "status": "active",
 *         "tenant_name": "Main MSP",
 *         "created_at": "2025-08-15T10:30:00Z"
 *       }
 *     ],
 *     "total": 156
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.getCustomer} for single customer details
 */
router.get('/', async (req, res) => {
  const page = parseInt(req.query.page, 10) || 1;
  const limit = parseInt(req.query.limit, 10) || 10;
  const offset = (page - 1) * limit;
  const search = req.query.search;
  const status = req.query.status; // 'active' | 'inactive'
  const dateFrom = req.query.date_from; // created_at >= date
  const dateTo = req.query.date_to;     // created_at <= date end of day
 
  try {
    let query = 'SELECT c.*, t.name AS tenant_name FROM customers c LEFT JOIN tenants t ON c.tenant_id = t.tenant_id';
    let countQuery = 'SELECT COUNT(*) FROM customers c';
    const where = [];
    const params = [];
 
    // Tenant filter
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'c');
    if (tenantClause) {
      where.push(tenantClause);
      params.push(...tenantParams);
    }
    let paramIndex = nextParamIndex;
 
    // Search
    if (search) {
      params.push(`%${search}%`);
      where.push(`(c.name ILIKE $${paramIndex} OR c.contact_name ILIKE $${paramIndex} OR c.email ILIKE $${paramIndex} OR c.phone ILIKE $${paramIndex} OR c.city ILIKE $${paramIndex})`);
      paramIndex++;
    }
 
    // Status filter
    if (status && (status === 'active' || status === 'inactive')) {
      params.push(status);
      where.push(`c.status = $${paramIndex}`);
      paramIndex++;
    }
 
    // Date range filter (created_at)
    if (dateFrom) {
      params.push(dateFrom);
      where.push(`c.created_at >= $${paramIndex}`);
      paramIndex++;
    }
    if (dateTo) {
      // include entire day for date_to by adding 1 day and using < next day
      params.push(dateTo);
      where.push(`c.created_at < ($${paramIndex}::date + INTERVAL '1 day')`);
      paramIndex++;
    }
 
    if (where.length) {
      const clause = ' WHERE ' + where.join(' AND ');
      query += clause;
      countQuery += clause;
    }
 
    params.push(limit, offset);
    query += ` ORDER BY c.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
 
    const [totalRes, result] = await Promise.all([
      pool.query(countQuery, params.slice(0, params.length - 2)),
      pool.query(query, params)
    ]);
 
    res.json({ customers: result.rows, total: parseInt(totalRes.rows[0].count, 10) });
  } catch (err) {
    console.error('Error fetching customers:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /customers/:id Get customer details
 * @apiName GetCustomer
 * @apiGroup Customers
 * @apiDescription Retrieves complete customer information including all related entities.
 * Returns customer details along with tickets, invoices, contracts, agents, contacts,
 * domains, Office 365 subscriptions, and hosting applications. Provides comprehensive
 * 360-degree view of customer relationship. Gracefully handles missing related tables.
 * 
 * **Included Related Data:**
 * - **Tickets:** All support tickets for customer (ordered by created_at DESC)
 * - **Invoices:** All billing invoices (ordered by issued_date DESC)
 * - **Agents:** RMM agents deployed at customer sites
 * - **Contacts:** Customer contact persons (ordered by is_primary DESC)
 * - **Contracts:** Service agreements and SLAs (ordered by created_at DESC)
 * - **Domains:** Registered domains associated with customer
 * - **Office365 Subscriptions:** Microsoft 365 licenses and subscriptions
 * - **Hosting Apps:** Deployed web applications/websites
 * 
 * **Multi-Tenant Isolation:**
 * All related queries respect tenant boundaries - only returns data accessible
 * to the authenticated tenant.
 * 
 * **Error Handling:**
 * If any related table doesn't exist or query fails, returns empty array for
 * that relation rather than failing entire request.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} id Customer ID (URL parameter)
 * @apiSuccess {object} customer Complete customer object with tenant_name
 * @apiSuccess {Array} tickets Related tickets
 * @apiSuccess {Array} invoices Related invoices
 * @apiSuccess {Array} agents Related RMM agents
 * @apiSuccess {Array} contacts Customer contacts
 * @apiSuccess {Array} contracts Service contracts
 * @apiSuccess {Array} domains Registered domains
 * @apiSuccess {Array} office365_subscriptions Microsoft 365 subscriptions
 * @apiSuccess {Array} hosting_apps Hosted web applications
 * @apiError {number} 404 Customer not found or access denied
 * @apiError {number} 500 Server error
 * @apiExample {curl} Example Request:
 *   curl -H "Authorization: Bearer eyJhbGc..." \\
 *        "https://api.everydaytech.au/customers/42"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "customer": {
 *       "customer_id": 42,
 *       "name": "Acme Corporation",
 *       "email": "john@acme.com",
 *       "phone": "+1-555-0123",
 *       "status": "active",
 *       "tenant_name": "Main MSP",
 *       "created_at": "2025-08-15T10:30:00Z"
 *     },
 *     "tickets": [
 *       { "ticket_id": 125, "title": "Email issues", "status": "open" }
 *     ],
 *     "invoices": [
 *       { "invoice_id": 890, "amount": 299.99, "status": "paid" }
 *     ],
 *     "agents": [
 *       { "agent_id": "abc123", "name": "DC01", "status": "online" }
 *     ],
 *     "contacts": [
 *       { "contact_id": 1, "name": "John Smith", "is_primary": true }
 *     ],
 *     "contracts": [
 *       { "contract_id": 5, "name": "Managed Services", "status": "active" }
 *     ],
 *     "domains": [
 *       { "domain_id": 78, "domain_name": "acme.com", "expires_on": "2027-01-15" }
 *     ],
 *     "office365_subscriptions": [],
 *     "hosting_apps": []
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.updateCustomer} for updating customer
 * @see {@link module:routes/tickets.listTickets} for ticket details
 * @see {@link module:routes/contracts.listContracts} for contract details
 */
router.get('/:id', async (req, res) => {
  const id = req.params.id;
  try {
    // Build query with tenant filtering
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
    let query = 'SELECT c.*, t.name AS tenant_name FROM customers c LEFT JOIN tenants t ON c.tenant_id = t.tenant_id WHERE c.customer_id = $1';
    const params = [id];
    
    if (tenantClause) {
      // Adjust tenant clause parameter indexes to start at $2 since $1 is customer_id
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (match, num) => `$${parseInt(num, 10) + 1}`);
      query += ` AND ${adjustedClause}`;
      params.push(...tenantParams);
    }
    
    const custRes = await pool.query(query, params);
    if (custRes.rows.length === 0) return res.status(404).json({ error: 'Customer not found' });
 
    const customer = custRes.rows[0];
 
    // Fetch related data with tenant filtering - handle missing tables gracefully
    const ticketFilter = getTenantFilter(req, 'tk');
    const invoiceFilter = getTenantFilter(req, 'inv');
    const agentFilter = getTenantFilter(req, 'ag');
    const contractFilter = getTenantFilter(req, 'con');
 
    const fetchRelatedData = async () => {
      const results = {
        tickets: [],
        invoices: [],
        agents: [],
        contacts: [],
        contracts: [],
        domains: [],
        office365_subscriptions: [],
        hosting_apps: []
      };
 
      try {
        let ticketQuery = 'SELECT tk.* FROM tickets tk WHERE tk.customer_id = $1';
        const ticketParams = [id];
        if (ticketFilter.clause) {
          const adjustedClause = ticketFilter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          ticketQuery += ` AND ${adjustedClause}`;
          ticketParams.push(...ticketFilter.params);
        }
        ticketQuery += ' ORDER BY tk.created_at DESC';
        const ticketsRes = await pool.query(ticketQuery, ticketParams);
        results.tickets = ticketsRes.rows;
      } catch (err) {
        console.warn('Error fetching tickets:', err.message);
      }
 
      try {
        let invoiceQuery = 'SELECT inv.* FROM invoices inv WHERE inv.customer_id = $1';
        const invoiceParams = [id];
        if (invoiceFilter.clause) {
          const adjustedClause = invoiceFilter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          invoiceQuery += ` AND ${adjustedClause}`;
          invoiceParams.push(...invoiceFilter.params);
        }
        invoiceQuery += ' ORDER BY inv.issued_date DESC';
        const invoicesRes = await pool.query(invoiceQuery, invoiceParams);
        results.invoices = invoicesRes.rows;
      } catch (err) {
        console.warn('Error fetching invoices:', err.message);
      }
 
      try {
        let agentQuery = 'SELECT ag.* FROM agents ag WHERE ag.customer_id = $1';
        const agentParams = [id];
        if (agentFilter.clause) {
          const adjustedClause = agentFilter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          agentQuery += ` AND ${adjustedClause}`;
          agentParams.push(...agentFilter.params);
        }
        agentQuery += ' ORDER BY ag.name';
        const agentsRes = await pool.query(agentQuery, agentParams);
        results.agents = agentsRes.rows;
      } catch (err) {
        console.warn('Error fetching agents:', err.message);
      }
 
      try {
        const contactsRes = await pool.query('SELECT * FROM customer_contacts WHERE customer_id = $1 ORDER BY is_primary DESC, name ASC', [id]);
        results.contacts = contactsRes.rows;
      } catch (err) {
        console.warn('Error fetching contacts:', err.message);
      }
 
      try {
        let contractQuery = 'SELECT con.* FROM contracts con WHERE con.customer_id = $1';
        const contractParams = [id];
        if (contractFilter.clause) {
          const adjustedClause = contractFilter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          contractQuery += ` AND ${adjustedClause}`;
          contractParams.push(...contractFilter.params);
        }
        contractQuery += ' ORDER BY con.created_at DESC';
        const contractsRes = await pool.query(contractQuery, contractParams);
        results.contracts = contractsRes.rows;
      } catch (err) {
        console.warn('Error fetching contracts:', err.message);
      }
 
      // Fetch domains
      try {
        const domainsFilter = getTenantFilter(req, 'd');
        let domainQuery = 'SELECT d.* FROM domains d WHERE d.customer_id = $1';
        const domainParams = [id];
        if (domainsFilter.clause) {
          const adjustedClause = domainsFilter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          domainQuery += ` AND ${adjustedClause}`;
          domainParams.push(...domainsFilter.params);
        }
        domainQuery += ' ORDER BY d.domain_name';
        const domainsRes = await pool.query(domainQuery, domainParams);
        results.domains = domainsRes.rows;
      } catch (err) {
        console.warn('Error fetching domains:', err.message);
      }
 
      // Fetch Office 365 subscriptions
      try {
        const o365Filter = getTenantFilter(req, 'o');
        let o365Query = 'SELECT o.* FROM office365_subscriptions o WHERE o.customer_id = $1';
        const o365Params = [id];
        if (o365Filter.clause) {
          const adjustedClause = o365Filter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          o365Query += ` AND ${adjustedClause}`;
          o365Params.push(...o365Filter.params);
        }
        o365Query += ' ORDER BY o.created_at DESC';
        const o365Res = await pool.query(o365Query, o365Params);
        results.office365_subscriptions = o365Res.rows;
      } catch (err) {
        console.warn('Error fetching office365 subscriptions:', err.message);
      }
 
      // Fetch hosting apps
      try {
        const hostingFilter = getTenantFilter(req, 'h');
        let hostingQuery = 'SELECT h.* FROM hosting_apps h WHERE h.customer_id = $1';
        const hostingParams = [id];
        if (hostingFilter.clause) {
          const adjustedClause = hostingFilter.clause.replace(/\$(\d+)/g, (match, num) => {
            return `$${parseInt(num) + 1}`;
          });
          hostingQuery += ` AND ${adjustedClause}`;
          hostingParams.push(...hostingFilter.params);
        }
        hostingQuery += ' ORDER BY h.created_at DESC';
        const hostingRes = await pool.query(hostingQuery, hostingParams);
        results.hosting_apps = hostingRes.rows;
      } catch (err) {
        console.warn('Error fetching hosting apps:', err.message);
      }
 
      return results;
    };
 
    const relatedData = await fetchRelatedData();
 
    res.json({
      customer,
      ...relatedData
    });
  } catch (err) {
    console.error('Error fetching customer details:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {post} /customers Create new customer
 * @apiName CreateCustomer
 * @apiGroup Customers
 * @apiDescription Creates a new customer record in the system. Automatically sets tenant
 * context from JWT, generates timestamps, and publishes customer.created event to Redis
 * for downstream integrations (billing, CRM sync, reporting, etc.).
 * 
 * **Default Values:**
 * - status: 'active' (if not provided)
 * - tenant_id: Extracted from JWT token automatically
 * - created_at: Current timestamp
 * - updated_at: Current timestamp
 * 
 * **Event Publishing:**
 * Publishes 'customer.created' event to Redis events channel with customer data for:
 * - Billing system integration
 * - CRM synchronization
 * - Analytics and reporting
 * - Webhook notifications
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {string} name Customer name/company name (required)
 * @apiParam {string} [contact_name] Primary contact person name
 * @apiParam {string} [email] Primary email address
 * @apiParam {string} [phone] Primary phone number
 * @apiParam {string} [address] Street address
 * @apiParam {string} [city] City
 * @apiParam {string} [state] State/province
 * @apiParam {string} [postal_code] ZIP/postal code
 * @apiParam {string} [country] Country
 * @apiParam {string} [notes] Internal notes about customer
 * @apiParam {string} [status=active] Customer status (active or inactive)
 * @apiSuccess {Object} customer Complete newly created customer object
 * @apiSuccess {number} customer.customer_id Auto-generated customer ID
 * @apiError {number} 400 Missing required field (name)
 * @apiError {number} 403 No tenant context (authentication issue)
 * @apiError {number} 500 Failed to create customer
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Acme Corporation",
 *          "contact_name": "John Smith",
 *          "email": "john@acme.com",
 *          "phone": "+1-555-0123",
 *          "address": "123 Business Blvd",
 *          "city": "Sydney",
 *          "state": "NSW",
 *          "postal_code": "2000",
 *          "country": "Australia",
 *          "status": "active"
 *        }' \
 *        "https://api.everydaytech.au/customers"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "customer_id": 42,
 *     "name": "Acme Corporation",
 *     "contact_name": "John Smith",
 *     "email": "john@acme.com",
 *     "phone": "+1-555-0123",
 *     "status": "active",
 *     "tenant_id": 1,
 *     "created_at": "2026-02-10T14:30:00Z",
 *     "updated_at": "2026-02-10T14:30:00Z"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.updateCustomer} for updating customers
 * @see {@link module:utils/redis} for event publishing
 */
router.post('/', async (req, res) => {
  const { name, contact_name = null, email = null, phone = null, address = null, city = null, state = null, postal_code = null, country = null, notes = null, status = 'active' } = req.body;
  
  console.log('POST /customers - Request body:', req.body);
  console.log('POST /customers - Tenant context:', req.tenant);
  
  if (!name) {
    return res.status(400).json({ error: 'Customer name is required' });
  }
 
  if (!req.tenant?.id) {
    console.error('POST /customers - No tenant context found');
    return res.status(403).json({ error: 'No tenant context' });
  }
 
  try {
    console.log('POST /customers - Attempting to insert customer with tenant_id:', req.tenant.id);
    const result = await pool.query(
      `INSERT INTO customers (name, contact_name, email, phone, address, city, state, postal_code, country, notes, status, tenant_id)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING *`,
      [name, contact_name, email, phone, address, city, state, postal_code, country, notes, status, req.tenant.id]
    );
    const customer = result.rows[0];
    console.log('POST /customers - Customer created successfully:', customer);
 
    // Publish customer.created event to Redis
    try {
      const redis = require('../utils/redis');
      const event = {
        type: 'customer.created',
        tenant_id: customer.tenant_id,
        customer_id: customer.customer_id,
        data: customer
      };
      redis.publish('events', JSON.stringify(event));
    } catch (err) {
      console.error('[Redis] Failed to publish customer.created event:', err);
    }
 
    res.status(201).json(customer);
  } catch (err) {
    console.error('Error creating customer:', err);
    res.status(500).json({ error: 'Failed to create customer', details: err.message });
  }
});
 
/**
 * @api {put} /customers/:id Update customer
 * @apiName UpdateCustomer
 * @apiGroup Customers
 * @apiDescription Updates an existing customer's details. Sets updated_at timestamp
 * automatically. All fields should be provided in request body - include existing
 * values for fields that should not change. Tenant isolation enforced automatically.
 * 
 * **Updatable Fields:**
 * - name: Customer/company name
 * - contact_name: Primary contact person
 * - email, phone: Contact information
 * - address, city, state, postal_code, country: Physical address
 * - notes: Internal notes
 * - status: active or inactive
 * 
 * **Status Changes:**
 * Setting status to 'inactive' soft-disables customer without deleting data.
 * Can be reactivated later by setting status back to 'active'.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Customer ID (URL parameter)
 * @apiParam {string} name Updated customer name
 * @apiParam {string} contact_name Updated primary contact
 * @apiParam {string} email Updated email
 * @apiParam {string} phone Updated phone
 * @apiParam {string} address Updated street address
 * @apiParam {string} city Updated city
 * @apiParam {string} state Updated state
 * @apiParam {string} postal_code Updated postal code
 * @apiParam {string} country Updated country
 * @apiParam {string} notes Updated notes
 * @apiParam {string} status Updated status (active or inactive)
 * @apiSuccess {Object} customer Updated customer object with new updated_at timestamp
 * @apiError {number} 404 Customer not found or access denied
 * @apiError {number} 500 Failed to update customer
 * @apiExample {curl} Example Request:
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Acme Corporation Pty Ltd",
 *          "contact_name": "Jane Smith",
 *          "email": "jane@acme.com",
 *          "phone": "+1-555-9999",
 *          "address": "456 New Address",
 *          "city": "Sydney",
 *          "state": "NSW",
 *          "postal_code": "2001",
 *          "country": "Australia",
 *          "notes": "Updated primary contact",
 *          "status": "active"
 *        }' \
 *        "https://api.everydaytech.au/customers/42"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "customer_id": 42,
 *     "name": "Acme Corporation Pty Ltd",
 *     "contact_name": "Jane Smith",
 *     "updated_at": "2026-02-10T16:45:00Z",
 *     ...
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.getCustomer} for retrieving current customer state
 */
router.put('/:id', async (req, res) => {
  const customerId = req.params.id;
  const { name, contact_name, email, phone, address, city, state, postal_code, country, notes, status } = req.body;
 
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
    
    // Build the update query with tenant filtering
    const updateParams = [name, contact_name, email, phone, address, city, state, postal_code, country, notes, status];
    
    // Add customer ID
    const customerIdParamIndex = 12;
    updateParams.push(customerId);
    
    // Build WHERE clause
    let whereClause = `customer_id = $${customerIdParamIndex}`;
    
    if (tenantClause) {
      // Add tenant params and adjust clause
      updateParams.push(...tenantParams);
      const tenantParamStart = 13;
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (match, num) => {
        return `$${tenantParamStart + parseInt(num) - 1}`;
      });
      whereClause += ` AND ${adjustedClause}`;
    }
    
    const result = await pool.query(
      `UPDATE customers c SET name=$1, contact_name=$2, email=$3, phone=$4, address=$5, city=$6,
         state=$7, postal_code=$8, country=$9, notes=$10, status=$11, updated_at=NOW()
       WHERE ${whereClause} RETURNING *`,
      updateParams
    );
    
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Customer not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating customer:', err);
    res.status(500).json({ error: 'Failed to update customer', details: err.message });
  }
});
 
// Customer Contacts CRUD
 
/**
 * @api {post} /customers/:id/contacts Create customer contact
 * @apiName CreateCustomerContact
 * @apiGroup Customer Contacts
 * @apiDescription Creates a new contact person for a customer. Supports primary contact
 * designation - if is_primary is true, automatically unsets primary flag on other contacts
 * for this customer to ensure only one primary contact exists.
 * 
 * **Contact Hierarchy:**
 * - Primary contact: Main point of contact (only one per customer)
 * - Secondary contacts: Additional contacts (unlimited)
 * 
 * **Primary Contact Handling:**
 * When is_primary=true, automatically sets is_primary=false on all other contacts
 * for the customer before creating the new primary contact.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Customer ID (URL parameter)
 * @apiParam {string} name Contact person name (required)
 * @apiParam {string} [role] Job title or role (e.g., "IT Manager")
 * @apiParam {string} [email] Contact email address
 * @apiParam {string} [phone] Contact phone number
 * @apiParam {boolean} [is_primary=false] Whether this is the primary contact
 * @apiParam {string} [notes] Internal notes about contact
 * @apiSuccess {Object} contact Newly created contact object
 * @apiSuccess {number} contact.contact_id Auto-generated contact ID
 * @apiError {number} 400 Missing required field (name)
 * @apiError {number} 500 Failed to create contact
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Jane Smith",
 *          "role": "IT Manager",
 *          "email": "jane@acme.com",
 *          "phone": "+1-555-5678",
 *          "is_primary": true,
 *          "notes": "Technical escalation contact"
 *        }' \
 *        "https://api.everydaytech.au/customers/42/contacts"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "contact_id": 15,
 *     "customer_id": 42,
 *     "name": "Jane Smith",
 *     "role": "IT Manager",
 *     "email": "jane@acme.com",
 *     "phone": "+1-555-5678",
 *     "is_primary": true,
 *     "tenant_id": 1,
 *     "created_at": "2026-02-10T15:00:00Z"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.updateContact} for updating contacts
 * @see {@link module:routes/customers.getCustomer} to view all contacts
 */
router.post('/:id/contacts', async (req, res) => {
  const customerId = req.params.id;
  const { name, role = null, email = null, phone = null, is_primary = false, notes = null } = req.body;
 
  console.log('POST /customers/:id/contacts called');
  console.log('Customer ID:', customerId);
  console.log('Request body:', req.body);
 
  if (!name) {
    console.log('Error: Contact name is required');
    return res.status(400).json({ error: 'Contact name is required' });
  }
 
  try {
    // If setting as primary, unset other primaries for this customer
    if (is_primary) {
      await pool.query(
        'UPDATE customer_contacts SET is_primary = false WHERE customer_id = $1',
        [customerId]
      );
    }
 
    const result = await pool.query(
      `INSERT INTO customer_contacts (customer_id, name, role, email, phone, is_primary, notes, tenant_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
      [customerId, name, role, email, phone, is_primary, notes, req.tenant?.id]
    );
    console.log('Contact created successfully:', result.rows[0]);
    console.log('Tenant context in contacts route:', req.tenant);
 
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('Error creating customer contact:', err);
    res.status(500).json({ error: 'Failed to create contact', details: err.message });
  }
});
 
/**
 * @api {put} /customers/:id/contacts/:contactId Update customer contact
 * @apiName UpdateCustomerContact
 * @apiGroup Customer Contacts
 * @apiDescription Updates an existing customer contact's details. If is_primary is set
 * to true, automatically unsets primary flag on other contacts for the same customer.
 * Sets updated_at timestamp automatically.
 * 
 * **Primary Contact Toggle:**
 * When changing is_primary from false to true, the system ensures only one primary
 * contact exists by setting is_primary=false on all other contacts for this customer
 * (excluding the contact being updated).
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Customer ID (URL parameter)
 * @apiParam {number} contactId Contact ID to update (URL parameter)
 * @apiParam {string} name Updated contact name
 * @apiParam {string} role Updated role/title
 * @apiParam {string} email Updated email
 * @apiParam {string} phone Updated phone
 * @apiParam {boolean} is_primary Updated primary status
 * @apiParam {string} notes Updated notes
 * @apiSuccess {Object} contact Updated contact object with new updated_at timestamp
 * @apiError {number} 404 Contact not found
 * @apiError {number} 500 Failed to update contact
 * @apiExample {curl} Example Request:
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Jane Smith",
 *          "role": "Director of IT",
 *          "email": "jane.smith@acme.com",
 *          "phone": "+1-555-9999",
 *          "is_primary": true,
 *          "notes": "Promoted to Director"
 *        }' \
 *        "https://api.everydaytech.au/customers/42/contacts/15"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "contact_id": 15,
 *     "customer_id": 42,
 *     "name": "Jane Smith",
 *     "role": "Director of IT",
 *     "is_primary": true,
 *     "updated_at": "2026-02-10T16:30:00Z",
 *     ...
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.createContact} for creating contacts
 */
router.put('/:id/contacts/:contactId', async (req, res) => {
  const customerId = req.params.id;
  const contactId = req.params.contactId;
  const { name, role, email, phone, is_primary, notes } = req.body;
 
  try {
    // If setting as primary, unset other primaries for this customer
    if (is_primary) {
      await pool.query(
        'UPDATE customer_contacts SET is_primary = false WHERE customer_id = $1 AND contact_id != $2',
        [customerId, contactId]
      );
    }
 
    const result = await pool.query(
      `UPDATE customer_contacts 
       SET name=$1, role=$2, email=$3, phone=$4, is_primary=$5, notes=$6, updated_at=NOW()
       WHERE contact_id=$7 AND customer_id=$8 RETURNING *`,
      [name, role, email, phone, is_primary, notes, contactId, customerId]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Contact not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating customer contact:', err);
    res.status(500).json({ error: 'Failed to update contact', details: err.message });
  }
});
 
/**
 * @api {delete} /customers/:id/contacts/:contactId Delete customer contact
 * @apiName DeleteCustomerContact
 * @apiGroup Customer Contacts
 * @apiDescription Permanently deletes a customer contact. This action cannot be undone.
 * Use with caution. Consider updating rather than deleting if contact information changes.
 * 
 * **Deletion Safety:**
 * - Validates contact belongs to specified customer
 * - Returns deleted contact object for audit trail
 * - No cascading effects on customer or other records
 * 
 * **Best Practice:**
 * If simply changing primary contact, use the update endpoint instead of delete+create.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} id Customer ID (URL parameter)
 * @apiParam {number} contactId Contact ID to delete (URL parameter)
 * @apiSuccess {string} message Success confirmation message
 * @apiSuccess {object} contact The deleted contact object (for audit trail)
 * @apiError {number} 404 Contact not found or doesn't belong to customer
 * @apiError {number} 500 Failed to delete contact
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/customers/42/contacts/15"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Contact deleted successfully",
 *     "contact": {
 *       "contact_id": 15,
 *       "customer_id": 42,
 *       "name": "Former Employee",
 *       "email": "old@acme.com"
 *     }
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.updateContact} for updating instead of deleting
 */
router.delete('/:id/contacts/:contactId', async (req, res) => {
  const customerId = req.params.id;
  const contactId = req.params.contactId;
 
  try {
    const result = await pool.query(
      'DELETE FROM customer_contacts WHERE contact_id = $1 AND customer_id = $2 RETURNING *',
      [contactId, customerId]
    );
 
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Contact not found' });
    }
    res.json({ message: 'Contact deleted successfully', contact: result.rows[0] });
  } catch (err) {
    console.error('Error deleting customer contact:', err);
    res.status(500).json({ error: 'Failed to delete contact', details: err.message });
  }
});
 
/**
 * @api {delete} /customers/:id Delete customer
 * @apiName DeleteCustomer
 * @apiGroup Customers
 * @apiDescription Permanently deletes a customer and associated contacts. Admin-only operation
 * with referential integrity checks to prevent deletion of customers with active tickets or
 * invoices. Use with extreme caution as this action cannot be undone.
 * 
 * **Admin Only:**
 * Requires admin role via requireAdmin middleware. Regular users cannot delete customers.
 * 
 * **Referential Integrity Protection:**
 * Prevents deletion if customer has:
 * - Any tickets (open or closed)
 * - Any invoices (paid or unpaid)
 * 
 * Returns error with counts if deletion blocked. Recommend setting status to 'inactive' instead.
 * 
 * **Cascade Deletion:**
 * If deletion proceeds, automatically deletes:
 * - All customer contacts (customer_contacts table)
 * - The customer record itself
 * 
 * **Best Practice:**
 * Instead of deleting customers with business history, set status='inactive' using the
 * update endpoint. This preserves historical data and audit trails while hiding customer
 * from active lists.
 * @apiHeader {string} Authorization Bearer JWT token with admin permissions
 * @apiParam {number} id Customer ID to delete (URL parameter)
 * @apiSuccess {string} message Success confirmation message
 * @apiSuccess {object} deleted_customer The deleted customer object
 * @apiError {number} 403 Forbidden - Admin role required
 * @apiError {number} 404 Customer not found or access denied
 * @apiError {number} 400 Cannot delete customer with existing tickets/invoices
 * @apiError {number} 500 Failed to delete customer
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/customers/999"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Customer deleted successfully",
 *     "deleted_customer": {
 *       "customer_id": 999,
 *       "name": "Test Customer Ltd",
 *       "status": "inactive"
 *     }
 *   }
 * @apiExample {json} Error Response (Has Related Records):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Cannot delete customer with existing tickets or invoices. Set status to inactive instead.",
 *     "details": {
 *       "ticket_count": 5,
 *       "invoice_count": 12
 *     }
 *   }
 * @apiExample {json} Error Response (Not Admin):
 *   HTTP/1.1 403 Forbidden
 *   {
 *     "error": "Admin access required"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/customers.updateCustomer} for setting status to inactive (preferred)
 */
router.delete('/:id', requireAdmin, async (req, res) => {
  const customerId = req.params.id;
  
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
    
    // First check if customer exists and user has access
    let checkQuery = 'SELECT customer_id, name FROM customers c WHERE c.customer_id = $1';
    const checkParams = [customerId];
    
    if (tenantClause) {
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      checkQuery += ` AND ${adjustedClause}`;
      checkParams.push(...tenantParams);
    }
    
    const checkResult = await pool.query(checkQuery, checkParams);
    
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ error: 'Customer not found' });
    }
    
    const customer = checkResult.rows[0];
    
    // Delete associated customer contacts first (due to foreign key constraints)
    try {
      await pool.query('DELETE FROM customer_contacts WHERE customer_id = $1', [customerId]);
    } catch (contactErr) {
      console.log('No customer contacts to delete or table does not exist');
    }
    
    // Check for related tickets or invoices that would prevent deletion
    const relatedCheck = await pool.query(`
      SELECT 
        (SELECT COUNT(*) FROM tickets WHERE customer_id = $1) as ticket_count,
        (SELECT COUNT(*) FROM invoices WHERE customer_id = $1) as invoice_count
    `, [customerId]);
    
    const { ticket_count, invoice_count } = relatedCheck.rows[0];
    
    if (parseInt(ticket_count) > 0 || parseInt(invoice_count) > 0) {
      return res.status(400).json({ 
        error: 'Cannot delete customer with existing tickets or invoices. Set status to inactive instead.',
        details: { ticket_count: parseInt(ticket_count), invoice_count: parseInt(invoice_count) }
      });
    }
    
    // Delete the customer
    const result = await pool.query(
      'DELETE FROM customers WHERE customer_id = $1 RETURNING *',
      [customerId]
    );
    
    console.log(`[Customers] Customer deleted: ID ${customerId}, Name: "${customer.name}", User: ${req.user?.user_id}, Tenant: ${req.tenant?.id}`);
    
    res.json({ 
      message: 'Customer deleted successfully', 
      deleted_customer: result.rows[0] 
    });
  } catch (err) {
    console.error('Error deleting customer:', err);
    res.status(500).json({ error: 'Failed to delete customer', details: err.message });
  }
});
 
module.exports = router;