contracts.js

Go to the documentation of this file.

/**
 * @file contracts.js
 * @brief Service Contract Management API Routes
 * @description Comprehensive contract and service agreement management for MSP/PSA operations.
 * Manages recurring service contracts with billing automation, line items, SLA tracking, and
 * customer service level agreements. Supports multiple billing intervals, auto-invoicing, and
 * resource limits (devices, contacts, products).
 * 
 * **Core Features:**
 * - Contract lifecycle management (active, inactive, deleted status)
 * - Flexible billing intervals (monthly, yearly, custom dates)
 * - Auto-invoice generation with configurable schedules
 * - Line items with recurring/one-time charges
 * - Resource limits (max devices, contacts, products)
 * - SLA hours configuration per contract
 * - Labor rate and currency settings
 * - Multi-tenant isolation
 * - Schema-aware dynamic column handling (backwards compatible)
 * 
 * **Billing Features:**
 * - Automatic invoice generation via background worker
 * - Next billing date calculation
 * - Custom billing day support
 * - Custom billing date arrays for irregular schedules
 * - Manual invoice generation endpoint
 * 
 * **Contract Line Items:**
 * - Product-linked or custom line items
 * - Quantity and unit price tracking
 * - Recurring vs one-time charges
 * - Sort order for display
 * - Graceful handling if table doesn't exist (migration safe)
 * 
 * **Database Schema:**
 * - contracts: Main contract records with billing config
 * - contract_line_items: Individual charges and products
 * - Foreign keys: customers, tenants
 * @module routes/contracts
 * @requires express
 * @requires services/db
 * @requires middleware/auth
 * @requires middleware/adminOnly
 * @requires middleware/tenant
 * @requires contractBillingWorker
 * @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);
 
// Utility: list existing columns for a table in current schema
/**
 * Get table columns from database schema
 * @param {object} executor - Database connection pool or client
 * @param {string} tableName - Name of the table to check
 * @returns {Promise<Set<string>>} Set of column names
 */
async function getTableColumns(executor = pool, tableName) {
  const res = await executor.query(
    `SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = $1`,
    [tableName]
  );
  return new Set(res.rows.map(r => r.column_name));
}
 
// Utility: check if contract_line_items table exists
/**
 * Check if contract_line_items table exists in schema
 * @param {object} executor - Database connection pool or client
 * @returns {Promise<boolean>} True if table exists
 */
async function contractLineItemsTableExists(executor = pool) {
  try {
    const res = await executor.query(
      `SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'contract_line_items')`
    );
    return res.rows[0]?.exists || false;
  } catch (_) {
    return false;
  }
}
 
 
 
/**
 * @api {get} /contracts List all contracts
 * @apiName ListContracts
 * @apiGroup Contracts
 * @apiDescription Retrieves paginated list of service contracts with comprehensive filtering.
 * Returns contracts with customer names, tenant names, and supports search, status filtering,
 * billing interval filtering, date ranges, and customer-specific filtering. Multi-tenant isolated.
 * 
 * **Query Parameters:**
 * - page (default: 1): Page number
 * - limit (default: 10): Results per page
 * - search: Search in title, description, or customer name
 * - status: Filter by status (active, inactive, deleted, or all)
 * - billing_interval: Filter by billing type (monthly, yearly, custom, or all)
 * - customer_id: Filter by specific customer
 * - date_from: Filter contracts starting after this date
 * - date_to: Filter contracts starting before this date
 * 
 * **Billing Intervals:**
 * - monthly: Billed every month on specified billing_day
 * - yearly: Billed annually on anniversary of start_date
 * - custom: Billed on dates specified in custom_billing_dates array
 * @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
 * @apiParam {string} [status] Status filter
 * @apiParam {string} [billing_interval] Billing interval filter
 * @apiParam {number} [customer_id] Customer ID filter
 * @apiParam {string} [date_from] Start date (ISO 8601)
 * @apiParam {string} [date_to] End date (ISO 8601)
 * @apiSuccess {Array} contracts Array of contract objects with customer/tenant names
 * @apiSuccess {number} total Total count of matching contracts
 * @apiExample {curl} List Active Monthly Contracts:
 *   curl -H "Authorization: Bearer eyJhbGc..." \\
 *        "https://api.everydaytech.au/contracts?status=active&billing_interval=monthly"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "contracts": [
 *       {
 *         "contract_id": 5,
 *         "title": "Managed Services Agreement",
 *         "customer_id": 42,
 *         "customer_name": "Acme Corp",
 *         "status": "active",
 *         "billing_interval": "monthly",
 *         "start_date": "2025-01-01",
 *         "next_billing_date": "2026-03-01",
 *         "auto_invoice_enabled": true,
 *         "sla_hours": 24,
 *         "labor_rate": 150.00,
 *         "currency": "AUD"
 *       }
 *     ],
 *     "total": 23
 *   }
 * @since 2.0.0
 * @see {@link module:routes/contracts.getContract} for single contract 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;
  const billingInterval = req.query.billing_interval;
  const dateFrom = req.query.date_from;
  const dateTo = req.query.date_to;
  const customerId = req.query.customer_id;
 
const ContractsModel = require('../models/Contracts');
// ...existing code...
 
/**
 * @api {get} /contracts/tenant/:tenantId Get contract for tenant
 * @apiName GetContractForTenant
 * @apiGroup Contracts
 * @apiDescription Retrieves the primary service contract associated with a specific tenant.
 * Used for tenant-level billing configuration and service agreement lookup. Returns 404
 * if no contract found for the tenant.
 * 
 * **Use Case:**
 * Primarily used for looking up the main service agreement for a sub-tenant in an
 * MSP hierarchy. Each tenant typically has one primary contract defining their
 * service level and billing terms.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} tenantId Tenant ID (URL parameter)
 * @apiSuccess {object} contract Contract object for the tenant
 * @apiError {number} 404 Contract not found for tenant
 * @apiError {number} 500 Server error
 * @apiExample {curl} Example Request:
 *   curl -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/contracts/tenant/5"
 * @since 2.0.0
 * @see {@link module:models/Contracts.getContractForTenant} for model implementation
 */
router.get('/tenant/:tenantId', authenticateToken, async (req, res) => {
  try {
    const contract = await ContractsModel.getContractForTenant(req.params.tenantId);
    if (!contract) {
      return res.status(404).json({ error: 'Contract not found for tenant' });
    }
    res.json(contract);
  } catch (err) {
    console.error('Error fetching contract for tenant:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /contracts/_schema Get contracts schema info
 * @apiName GetContractsSchema
 * @apiGroup Contracts
 * @apiDescription Debug endpoint that exposes current database schema information for the
 * contracts and contract_line_items tables. Returns list of columns and table existence
 * status. Used for development, debugging, and migration verification.
 * 
 * **Development Only:**
 * This is a helper endpoint for developers to verify schema state and column existence.
 * Should be restricted in production environments.
 * @apiSuccess {Array} contracts_columns List of column names in contracts table
 * @apiSuccess {boolean} has_contract_line_items Whether contract_line_items table exists
 * @apiError {number} 500 Schema query failed
 * @apiExample {curl} Example Request:
 *   curl "https://api.everydaytech.au/contracts/_schema"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "contracts_columns": [
 *       "contract_id", "customer_id", "title", "status",
 *       "billing_interval", "auto_invoice_enabled", "sla_hours"
 *     ],
 *     "has_contract_line_items": true
 *   }
 * @since 2.0.0
 */
router.get('/_schema', async (req, res) => {
  try {
    const cols = await getTableColumns(pool, 'contracts');
    const hasLineItems = await contractLineItemsTableExists(pool);
    res.json({ contracts_columns: Array.from(cols), has_contract_line_items: hasLineItems });
  } catch (e) {
    res.status(500).json({ error: 'schema query failed', details: e.message });
  }
});
 
 
 
  // Debug: log request context
  console.log(`[API][contracts] GET /contracts | Tenant:`, req.tenant, '| Query:', req.query);
 
  try {
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'c');
    let query = `
      SELECT c.*, cust.name as customer_name, t.name as tenant_name
      FROM contracts c 
      LEFT JOIN customers cust ON c.customer_id = cust.customer_id
      LEFT JOIN tenants t ON c.tenant_id = t.tenant_id
    `;
    let countQuery = 'SELECT COUNT(*) FROM contracts c LEFT JOIN customers cust ON c.customer_id = cust.customer_id';
    const where = [];
    const params = [...tenantParams];
    let paramIndex = nextParamIndex;
 
    // Tenant filter
    if (tenantClause) where.push(tenantClause);
 
    // Search
    if (search) {
      params.push(`%${search}%`);
      where.push(`(c.title ILIKE $${paramIndex} OR cust.name ILIKE $${paramIndex} OR c.description ILIKE $${paramIndex})`);
      paramIndex++;
    }
 
    // Customer filter
    if (customerId) {
      params.push(customerId);
      where.push(`c.customer_id = $${paramIndex}`);
      paramIndex++;
    }
 
    // Status filter
    if (status && status !== 'all') {
      params.push(status);
      where.push(`c.status = $${params.length}`);
    }
 
    // Billing interval filter
    if (billingInterval && billingInterval !== 'all') {
      params.push(billingInterval);
      where.push(`c.billing_interval = $${params.length}`);
    }
 
    // Date range filter (start_date)
    if (dateFrom) {
      params.push(dateFrom);
      where.push(`c.start_date >= $${params.length}`);
    }
    if (dateTo) {
      params.push(dateTo);
      where.push(`c.start_date <= $${params.length}`);
    }
 
    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 $${params.length - 1} OFFSET $${params.length}`;
 
    // Debug: log final query and params
    console.log(`[API][contracts] Executing query:`, query, '| Params:', params);
    console.log(`[API][contracts] Executing countQuery:`, countQuery, '| Params:', params.slice(0, params.length - 2));
 
    const [totalRes, result] = await Promise.all([
      pool.query(countQuery, params.slice(0, params.length - 2)),
      pool.query(query, params)
    ]);
 
    res.json({ contracts: result.rows, total: parseInt(totalRes.rows[0].count, 10) });
  } catch (err) {
    console.error('[API][contracts] Error fetching contracts:', err, '| Tenant:', req.tenant, '| Query:', req.query);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /contracts/:id Get contract details
 * @apiName GetContract
 * @apiGroup Contracts
 * @apiDescription Retrieves complete contract information including all line items.
 * Returns contract details with customer name, tenant name, and all associated
 * line items ordered by sort_order. Gracefully handles missing line items table.
 * 
 * **Included Data:**
 * - Full contract details (billing config, dates, limits)
 * - Customer name and tenant name (via LEFT JOIN)
 * - All line items with product links, pricing, quantities
 * - SLA hours (defaults to 24 if null)
 * 
 * **Line Items:**
 * Each line item includes:
 * - product_id: Linked product (if applicable)
 * - description: Line item description
 * - quantity: Number of units
 * - unit_price: Price per unit
 * - recurring: Whether charge repeats each billing cycle
 * - sort_order: Display order
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} id Contract ID (URL parameter)
 * @apiSuccess {object} contract Complete contract object
 * @apiSuccess {Array} contract.line_items Array of line item objects
 * @apiError {number} 404 Contract not found or access denied
 * @apiError {number} 500 Server error
 * @apiExample {curl} Example Request:
 *   curl -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/contracts/5"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "contract_id": 5,
 *     "title": "Managed Services",
 *     "customer_id": 42,
 *     "customer_name": "Acme Corp",
 *     "status": "active",
 *     "billing_interval": "monthly",
 *     "billing_day": 1,
 *     "auto_invoice_enabled": true,
 *     "next_billing_date": "2026-04-01",
 *     "sla_hours": 24,
 *     "labor_rate": 150.00,
 *     "currency": "AUD",
 *     "line_items": [
 *       {
 *         "line_item_id": 10,
 *         "product_id": 5,
 *         "description": "Managed Desktop Support",
 *         "quantity": 25,
 *         "unit_price": 50.00,
 *         "recurring": true,
 *         "sort_order": 1
 *       }
 *     ]
 *   }
 * @since 2.0.0
 * @see {@link module:routes/contracts.updateContract} for updating contracts
 */
router.get('/:id', authenticateToken, async (req, res) => {
  const id = req.params.id;
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
    let query = `
      SELECT c.*, cust.name as customer_name, t.name as tenant_name 
      FROM contracts c 
      LEFT JOIN customers cust ON c.customer_id = cust.customer_id
      LEFT JOIN tenants t ON c.tenant_id = t.tenant_id
      WHERE c.contract_id = $1
    `;
    const params = [id];
    
    if (tenantClause) {
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      query += ` AND ${adjustedClause}`;
      params.push(...tenantParams);
    }
    
    const result = await pool.query(query, params);
    
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Contract not found' });
    }
    
    const contract = result.rows[0];
    
    // Fetch line items - handle gracefully if table doesn't exist
    try {
      const lineItemsRes = await pool.query(
        'SELECT * FROM contract_line_items WHERE contract_id = $1 ORDER BY sort_order, line_item_id',
        [id]
      );
      contract.line_items = lineItemsRes.rows;
    } catch (lineItemErr) {
      console.warn('contract_line_items table not found, skipping line items');
      contract.line_items = [];
    }
    
    res.json(contract);
  } catch (err) {
    console.error('Error fetching contract:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {post} /contracts Create new contract
 * @apiName CreateContract
 * @apiGroup Contracts
 * @apiDescription Creates a new service contract with optional line items. Automatically
 * calculates next billing date based on billing interval, sets tenant context, and
 * performs transaction-safe creation with line items. Uses schema-aware column insertion
 * for backwards compatibility with different database versions.
 * 
 * **Billing Configuration:**
 * - **Monthly:** Bills on specified billing_day each month (or last day if > days in month)
 * - **Yearly:** Bills on anniversary of start_date each year
 * - **Custom:** Bills on dates specified in custom_billing_dates JSON array
 * 
 * **Auto-Invoice:**
 * When auto_invoice_enabled=true, system automatically generates invoices on
 * next_billing_date via background worker (contractBillingWorker).
 * 
 * **Line Items:**
 * Array of charges to include in invoices:
 * - product_id: Optional link to products table
 * - description: Line item label
 * - quantity: Number of units
 * - unit_price: Price per unit
 * - recurring: true for every invoice, false for one-time
 * - sort_order: Display order
 * 
 * **Transaction Safety:**
 * Contract and all line items created in single database transaction.
 * Rolls back entirely if any part fails.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} customer_id Customer ID (required)
 * @apiParam {string} title Contract title (required)
 * @apiParam {string} start_date Start date ISO 8601 (required)
 * @apiParam {string} [description] Contract description
 * @apiParam {string} [status=active] Contract status (active, inactive, deleted)
 * @apiParam {string} [end_date] Contract end date (null for ongoing)
 * @apiParam {string} [billing_interval=monthly] Billing frequency (monthly, yearly, custom)
 * @apiParam {number} [billing_day] Day of month for monthly billing (1-31)
 * @apiParam {string} [custom_billing_dates] JSON array of custom billing dates
 * @apiParam {boolean} [auto_invoice_enabled=false] Enable automatic invoicing
 * @apiParam {number} [max_devices=0] Maximum allowed devices (0=unlimited)
 * @apiParam {number} [max_contacts=0] Maximum contacts allowed
 * @apiParam {number} [max_products=0] Maximum products allowed
 * @apiParam {number} [labor_rate=0] Hourly labor rate
 * @apiParam {string} [currency=AUD] Currency code
 * @apiParam {string} [notes] Internal notes
 * @apiParam {Number} [sla_hours=24] SLA response time in hours
 * @apiParam {Array} [line_items=[]] Array of line item objects
 * 
 * @apiSuccess {Object} contract Complete newly created contract with line items
 * @apiSuccess {Number} contract.contract_id Auto-generated contract ID
 * 
 * @apiError {Number} 400 Missing required fields
 * @apiError {Number} 403 No tenant context
 * @apiError {Number} 500 Failed to create contract
 * 
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "customer_id": 42,
 *          "title": "Managed Services Agreement",
 *          "description": "Full IT support and monitoring",
 *          "start_date": "2026-03-01",
 *          "billing_interval": "monthly",
 *          "billing_day": 1,
 *          "auto_invoice_enabled": true,
 *          "max_devices": 50,
 *          "labor_rate": 150.00,
 *          "sla_hours": 4,
 *          "line_items": [
 *            {
 *              "product_id": 5,
 *              "description": "Per-device monitoring",
 *              "quantity": 25,
 *              "unit_price": 50.00,
 *              "recurring": true,
 *              "sort_order": 1
 *            }
 *          ]
 *        }' \
 *        "https://api.everydaytech.au/contracts"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "contract": {
 *       "contract_id": 5,
 *       "customer_id": 42,
 *       "title": "Managed Services Agreement",
 *       "status": "active",
 *       "next_billing_date": "2026-04-01",
 *       "line_items": [{ ... }],
 *       "created_at": "2026-03-11T10:00:00Z"
 *     }
 *   }
 * 
 * @since 2.0.0
 * @see {@link module:routes/contracts.updateContract} for updating contracts
 * @see {@link module:workers/contractBillingWorker} for auto-invoice generation
 */
router.post('/', authenticateToken, async (req, res) => {
  const {
    customer_id,
    title,
    description,
    status = 'active',
    start_date,
    end_date,
    billing_interval = 'monthly',
    billing_day,
    custom_billing_dates,
    auto_invoice_enabled = false,
    max_devices = 0,
    max_contacts = 0,
    max_products = 0,
    labor_rate = 0,
    currency = 'AUD',
    notes,
    sla_hours = 24,
    line_items = []
  } = req.body;
 
  if (!customer_id || !title || !start_date) {
    return res.status(400).json({ error: 'Customer, title, and start date are required' });
  }
 
  if (!req.tenant?.id) {
    console.error('POST /contracts - No tenant context found');
    return res.status(403).json({ error: 'No tenant context' });
  }
 
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
    // Calculate next billing date
    let nextBillingDate = null;
    if (auto_invoice_enabled) {
      if (billing_interval === 'monthly') {
        nextBillingDate = start_date;
      } else if (billing_interval === 'yearly') {
        nextBillingDate = start_date;
      } else if (billing_interval === 'custom' && custom_billing_dates) {
        const dates = JSON.parse(custom_billing_dates);
        if (dates && dates.length > 0) {
          nextBillingDate = dates[0];
        }
      }
    }
 
    // Insert contract using only columns that exist in current schema
    const cols = await getTableColumns(client, 'contracts');
    const valueMap = {
      customer_id,
      title,
      description,
      status,
      start_date,
      end_date: end_date || null,
      billing_interval,
      // legacy column alias
      invoice_interval: billing_interval,
      billing_day,
      custom_billing_dates,
      auto_invoice_enabled,
      // legacy column alias
      auto_invoice: auto_invoice_enabled,
      next_billing_date: nextBillingDate,
      max_devices,
      max_contacts,
      max_products,
      labor_rate,
      currency,
      notes,
      sla_hours,
      tenant_id: req.tenant.id
    };
    const insertCols = Object.keys(valueMap).filter(k => cols.has(k));
    if (!insertCols.includes('customer_id') || !insertCols.includes('title') || !insertCols.includes('start_date') || !insertCols.includes('tenant_id')) {
      throw new Error('Contracts table schema missing required columns (customer_id, title, start_date, tenant_id)');
    }
    const paramsArr = insertCols.map(k => valueMap[k]);
    const placeholders = insertCols.map((_, i) => `$${i + 1}`);
    const insertSql = `INSERT INTO contracts (${insertCols.join(', ')}) VALUES (${placeholders.join(', ')}) RETURNING *`;
    const contractRes = await client.query(insertSql, paramsArr);
    const contract = contractRes.rows[0];
    if (contract && contract.sla_hours == null) contract.sla_hours = 24;
 
    // Insert line items (if table exists)
    const hasLineItems = await contractLineItemsTableExists(client);
    if (hasLineItems && Array.isArray(line_items) && line_items.length) {
      for (const item of line_items) {
        await client.query(
          `INSERT INTO contract_line_items (
            contract_id, product_id, description, quantity, unit_price, recurring, sort_order
          ) VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [
            contract.contract_id,
            item.product_id || null,
            item.description,
            item.quantity,
            item.unit_price,
            item.recurring !== false,
            item.sort_order || 0
          ]
        );
      }
    } else if (!hasLineItems) {
      console.warn('contract_line_items table not found, skipping insert of line items');
    }
 
    await client.query('COMMIT');
 
    // Fetch the complete contract with line items (if table exists)
    try {
      if (await contractLineItemsTableExists()) {
        const lineItemsRes = await pool.query(
          'SELECT * FROM contract_line_items WHERE contract_id = $1 ORDER BY sort_order',
          [contract.contract_id]
        );
        contract.line_items = lineItemsRes.rows;
      } else {
        console.warn('contract_line_items table not found, skipping fetch of line items');
        contract.line_items = [];
      }
    } catch (e) {
      console.warn('contract_line_items table not found, skipping fetch of line items');
      contract.line_items = [];
    }
 
    res.status(201).json({ contract });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error creating contract:', err);
    res.status(500).json({ error: 'Failed to create contract', details: err.message });
  } finally {
    client.release();
  }
});
 
/**
 * @api {put} /contracts/:id Update contract
 * @apiName UpdateContract
 * @apiGroup Contracts
 * @apiDescription Updates an existing contract and replaces all line items. Recalculates
 * next billing date based on updated billing configuration. Uses transaction-safe update
 * with automatic rollback on failure. Schema-aware for backwards compatibility.
 * 
 * **Update Behavior:**
 * - All contract fields can be updated
 * - Existing line items are DELETED and replaced with provided line_items array
 * - Next billing date recalculated if auto_invoice_enabled or billing config changes
 * - Updated timestamp set automatically
 * 
 * **Next Billing Date Logic:**
 * - Uses last_billing_date if available, otherwise falls back to start_date
 * - Monthly: Advances by 1 month, respects billing_day
 * - Yearly: Advances by 1 year
 * - Custom: Uses next future date from custom_billing_dates array
 * 
 * **Transaction Safety:**
 * Contract update and line item replacement in single transaction. Rolls back if any step fails.
 * Uses SAVEPOINT for fallback to dynamic column detection if fixed-column update fails.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Contract ID (URL parameter)
 * @apiParam {number} customer_id Updated customer ID
 * @apiParam {string} title Updated title
 * @apiParam {string} description Updated description
 * @apiParam {string} status Updated status
 * @apiParam {string} start_date Updated start date
 * @apiParam {string} end_date Updated end date
 * @apiParam {string} billing_interval Updated billing interval
 * @apiParam {number} billing_day Updated billing day
 * @apiParam {string} custom_billing_dates Updated custom dates JSON
 * @apiParam {boolean} auto_invoice_enabled Updated auto-invoice flag
 * @apiParam {number} max_devices Updated device limit
 * @apiParam {number} max_contacts Updated contact limit
 * @apiParam {number} max_products Updated product limit
 * @apiParam {number} labor_rate Updated hourly rate
 * @apiParam {string} currency Updated currency code
 * @apiParam {String} notes Updated notes
 * @apiParam {Number} sla_hours Updated SLA hours
 * @apiParam {Array} [line_items=[]] New line items (replaces all existing)
 * 
 * @apiSuccess {Object} contract Updated contract object with new line items
 * 
 * @apiError {Number} 404 Contract not found or access denied
 * @apiError {Number} 500 Failed to update contract
 * 
 * @apiExample {curl} Example Request:
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "customer_id": 42,
 *          "title": "Updated Managed Services",
 *          "status": "active",
 *          "start_date": "2026-03-01",
 *          "billing_interval": "monthly",
 *          "billing_day": 15,
 *          "auto_invoice_enabled": true,
 *          "sla_hours": 2,
 *          "line_items": [
 *            {
 *              "description": "Premium support",
 *              "quantity": 30,
 *              "unit_price": 75.00,
 *              "recurring": true,
 *              "sort_order": 1
 *            }
 *          ]
 *        }' \
 *        "https://api.everydaytech.au/contracts/5"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "contract": {
 *       "contract_id": 5,
 *       "title": "Updated Managed Services",
 *       "next_billing_date": "2026-04-15",
 *       "line_items": [{ ... }],
 *       "updated_at": "2026-03-11T10:30:00Z"
 *     }
 *   }
 * 
 * @since 2.0.0
 * @see {@link module:routes/contracts.getContract} for retrieving current state
 */
router.put('/:id', authenticateToken, async (req, res) => {
  const id = req.params.id;
  const {
    customer_id,
    title,
    description,
    status,
    start_date,
    end_date,
    billing_interval,
    billing_day,
    custom_billing_dates,
    auto_invoice_enabled,
    max_devices,
    max_contacts,
    max_products,
    labor_rate,
    currency,
    notes,
    sla_hours,
    line_items = []
  } = req.body;
 
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
    // Calculate next billing date if needed (robust against missing last_billing_date column)
    let nextBillingDate = null;
    if (auto_invoice_enabled) {
      let baseDate = start_date;
      try {
        const cols = await getTableColumns(client, 'contracts');
        if (cols.has('last_billing_date')) {
          const lastBilling = await client.query(
            'SELECT last_billing_date FROM contracts WHERE contract_id = $1',
            [id]
          );
          if (lastBilling.rows[0]?.last_billing_date) {
            baseDate = lastBilling.rows[0].last_billing_date;
          }
        }
      } catch (_) {
        // ignore, fallback to start_date
      }
 
      if (billing_interval === 'monthly') {
        const date = new Date(baseDate);
        date.setMonth(date.getMonth() + 1);
        if (billing_day) {
          date.setDate(Math.min(billing_day, new Date(date.getFullYear(), date.getMonth() + 1, 0).getDate()));
        }
        nextBillingDate = date.toISOString().split('T')[0];
      } else if (billing_interval === 'yearly') {
        const date = new Date(baseDate);
        date.setFullYear(date.getFullYear() + 1);
        nextBillingDate = date.toISOString().split('T')[0];
      } else if (billing_interval === 'custom' && custom_billing_dates) {
        const dates = JSON.parse(custom_billing_dates);
        const today = new Date().toISOString().split('T')[0];
        const futureDates = dates.filter(d => d > today).sort();
        if (futureDates.length > 0) {
          nextBillingDate = futureDates[0];
        }
      }
    }
 
    // Update contract
  let contractRes;
  await client.query('SAVEPOINT sp_contract_update');
  try {
      // Get tenant filter for security
      const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
      
      // Build WHERE clause with tenant filtering
      let whereClause = 'contract_id=$19';
      const updateParams = [
        customer_id, title, description, status, start_date, end_date,
        billing_interval, billing_day, custom_billing_dates, auto_invoice_enabled,
        nextBillingDate, max_devices, max_contacts, max_products,
        labor_rate, currency, notes, sla_hours, id
      ];
      
      if (tenantClause) {
        updateParams.push(...tenantParams);
        const adjustedClause = tenantClause.replace(/\$(\d+)/g, (match, num) => {
          return `$${19 + parseInt(num)}`;
        });
        whereClause += ` AND ${adjustedClause}`;
      }
      
      // Debug logging for contract update
      console.log(`[Contracts] Updating contract ${id}:`, {
        requestTenantId: req.tenant?.id,
        isMsp: req.tenant?.isMsp,
        statusChange: status,
        tenantClause,
        whereClause,
        hasCustomerId: !!customer_id,
        paramCount: updateParams.length
      });
      
      console.log('[Contracts] Update SQL:', `UPDATE contracts c SET customer_id=$1, title=$2, description=$3, status=$4, start_date=$5, end_date=$6, billing_interval=$7, billing_day=$8, custom_billing_dates=$9, auto_invoice_enabled=$10, next_billing_date=$11, max_devices=$12, max_contacts=$13, max_products=$14, labor_rate=$15, currency=$16, notes=$17, sla_hours=$18, updated_at=CURRENT_TIMESTAMP WHERE ${whereClause} RETURNING *`);
      console.log('[Contracts] Update params:', updateParams);
      
      contractRes = await client.query(
        `UPDATE contracts c SET 
          customer_id=$1, title=$2, description=$3, status=$4, start_date=$5, end_date=$6,
          billing_interval=$7, billing_day=$8, custom_billing_dates=$9, auto_invoice_enabled=$10,
          next_billing_date=$11, max_devices=$12, max_contacts=$13, max_products=$14,
          labor_rate=$15, currency=$16, notes=$17, sla_hours=$18, updated_at=CURRENT_TIMESTAMP
         WHERE ${whereClause} RETURNING *`,
        updateParams
      );
    } catch (e) {
      // Fallback: dynamically update only existing columns in contracts table
      await client.query('ROLLBACK TO SAVEPOINT sp_contract_update');
      const cols = await getTableColumns(client, 'contracts');
      const valueMap = {
        customer_id,
        title,
        description,
        status,
        start_date,
        end_date,
        billing_interval,
        // legacy column alias
        invoice_interval: billing_interval,
        billing_day,
        custom_billing_dates,
        auto_invoice_enabled,
        // legacy column alias
        auto_invoice: auto_invoice_enabled,
        next_billing_date: nextBillingDate,
        max_devices,
        max_contacts,
        max_products,
        labor_rate,
        currency,
        notes,
        sla_hours
      };
      const setCols = Object.keys(valueMap).filter(k => cols.has(k));
      if (setCols.length === 0) {
        throw new Error('No updatable columns exist in contracts table');
      }
 
      // Build dynamic SET clause
      const setParts = setCols.map((k, i) => `${k}=$${i + 1}`);
      const updateParams = setCols.map(k => valueMap[k]);
 
      // WHERE with tenant filter
      const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
      let whereClause = `contract_id=$${setCols.length + 1}`;
      updateParams.push(id);
      if (tenantClause) {
        const adjustedClause = tenantClause.replace(/\$(\d+)/g, (match, num) => `$${setCols.length + 1 + parseInt(num)}`);
        whereClause += ` AND ${adjustedClause}`;
        updateParams.push(...tenantParams);
      }
 
      const sql = `UPDATE contracts c SET ${setParts.join(', ')}, updated_at=CURRENT_TIMESTAMP WHERE ${whereClause} RETURNING *`;
      contractRes = await client.query(sql, updateParams);
    }
 
    if (contractRes.rows.length === 0) {
      // Contract not found - could be wrong tenant or doesn't exist
      await client.query('ROLLBACK');
      
      // Debug: check if contract exists at all
      const existsCheck = await client.query(
        'SELECT contract_id, tenant_id, status FROM contracts WHERE contract_id = $1',
        [id]
      );
      
      if (existsCheck.rows.length === 0) {
        console.error(`[Contracts] Contract ${id} does not exist in database`);
        return res.status(404).json({ 
          error: 'Contract not found',
          details: 'No contract with this ID exists'
        });
      }
      
      // Contract exists but tenant filter excluded it
      const actualTenant = existsCheck.rows[0].tenant_id;
      const requestTenant = req.tenant?.id;
      console.error(`[Contracts] Contract ${id} exists but tenant mismatch:`, {
        contractTenantId: actualTenant,
        requestTenantId: requestTenant,
        isMsp: req.tenant?.isMsp,
        contractStatus: existsCheck.rows[0].status
      });
      
      return res.status(404).json({ 
        error: 'Contract not found',
        details: 'Contract not accessible by your tenant'
      });
    }
 
    const contract = contractRes.rows[0];
 
    // Delete existing line items and insert new ones (if table exists)
    const hasLineItems = await contractLineItemsTableExists(client);
    if (hasLineItems) {
      await client.query('DELETE FROM contract_line_items WHERE contract_id = $1', [id]);
      for (const item of line_items) {
        await client.query(
          `INSERT INTO contract_line_items (
            contract_id, product_id, description, quantity, unit_price, recurring, sort_order
          ) VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [
            id,
            item.product_id || null,
            item.description,
            item.quantity,
            item.unit_price,
            item.recurring !== false,
            item.sort_order || 0
          ]
        );
      }
    } else {
      console.warn('contract_line_items table not found, skipping update of line items');
    }
 
    await client.query('COMMIT');
 
    // Fetch updated line items (if table exists)
    try {
      if (await contractLineItemsTableExists()) {
        const lineItemsRes = await pool.query(
          'SELECT * FROM contract_line_items WHERE contract_id = $1 ORDER BY sort_order',
          [id]
        );
        contract.line_items = lineItemsRes.rows;
      } else {
        console.warn('contract_line_items table not found, skipping fetch of line items');
        contract.line_items = [];
      }
    } catch (e) {
      console.warn('contract_line_items table not found, skipping fetch of line items');
      contract.line_items = [];
    }
  if (contract && contract.sla_hours == null) contract.sla_hours = 24;
 
    res.json({ contract });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('[Contracts] Error updating contract:', err);
    console.error('[Contracts] Error stack:', err.stack);
    console.error('[Contracts] Contract ID:', id);
    console.error('[Contracts] Request body:', JSON.stringify(req.body, null, 2));
    res.status(500).json({ error: 'Failed to update contract', details: err.message, errorCode: err.code });
  } finally {
    client.release();
  }
});
 
/**
 * @api {delete} /contracts/:id Delete contract
 * @apiName DeleteContract
 * @apiGroup Contracts
 * @apiDescription Permanently deletes a contract. Tenant isolation enforced - users can only
 * delete contracts belonging to their tenant. Associated line items are cascade deleted
 * by database foreign key constraints.
 * 
 * **Deletion vs Status:**
 * This endpoint performs HARD delete (removes from database). For soft delete that
 * preserves history, use the admin-only DELETE endpoint that sets status='deleted'.
 * 
 * **Warning:**
 * This action cannot be undone. Consider setting status='inactive' or 'deleted' via
 * the update endpoint instead to preserve historical data.
 * @apiHeader {string} Authorization Bearer JWT token with tenant context
 * @apiParam {number} id Contract ID to delete (URL parameter)
 * @apiSuccess {string} message Success confirmation message
 * @apiError {number} 404 Contract not found or access denied
 * @apiError {number} 500 Delete failed
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/contracts/999"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Contract deleted successfully"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/contracts.updateContract} for soft delete via status field
 */
router.delete('/:id', authenticateToken, async (req, res) => {
  const id = req.params.id;
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'c');
    let query = 'DELETE FROM contracts c WHERE c.contract_id = $1';
    const params = [id];
    
    if (tenantClause) {
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      query += ` AND ${adjustedClause}`;
      params.push(...tenantParams);
    }
    
    query += ' RETURNING *';
    
    const result = await pool.query(query, params);
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Contract not found' });
    }
    res.json({ message: 'Contract deleted successfully' });
  } catch (err) {
    console.error('Error deleting contract:', err);
    res.status(500).json({ error: 'Delete failed', details: err.message });
  }
});
 
/**
 * @api {post} /contracts/:id/generate-invoice Generate invoice manually
 * @apiName GenerateContractInvoice
 * @apiGroup Contracts
 * @apiDescription Manually triggers invoice generation for a contract. Adds job to billing
 * queue and waits for completion (30 second timeout). Generates invoice with all recurring
 * line items, updates next_billing_date, and returns invoice details.
 * 
 * **Use Cases:**
 * - Manual billing outside regular schedule
 * - One-time additional invoice for contract
 * - Testing invoice generation
 * - Handling missed automatic billing
 * 
 * **Invoice Generation:**
 * - Creates invoice with current date
 * - Includes all recurring line items from contract
 * - Calculates total from line item quantities and prices
 * - Updates contract.next_billing_date based on billing_interval
 * - Links invoice to customer and contract
 * 
 * **Queue Processing:**
 * Job added to contractBillingWorker queue with priority=1 (high priority).
 * Endpoint waits up to 30 seconds for job completion before returning.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} id Contract ID (URL parameter)
 * @apiSuccess {boolean} success Always true when successful
 * @apiSuccess {string} message Success confirmation
 * @apiSuccess {number} invoiceId Generated invoice ID
 * @apiSuccess {string} invoiceNumber Invoice number (e.g., "INV-2026-001")
 * @apiSuccess {number} amount Total invoice amount
 * @apiSuccess {string} nextBillingDate Updated next billing date for contract
 * @apiError {boolean} success false when generation fails
 * @apiError {string} message Failure message
 * @apiError {string} reason Specific reason for failure
 * @apiError {number} 500 Server error or queue failure
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/contracts/5/generate-invoice"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "Invoice generated successfully",
 *     "invoiceId": 890,
 *     "invoiceNumber": "INV-2026-052",
 *     "amount": 1250.00,
 *     "nextBillingDate": "2026-04-01"
 *   }
 * @apiExample {json} Error Response (Not Due):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "success": false,
 *     "message": "Failed to generate invoice",
 *     "reason": "Invoice not due yet. Next billing date is 2026-04-15."
 *   }
 * @since 2.0.0
 * @see {@link module:workers/contractBillingWorker} for queue implementation
 * @see {@link module:routes/invoices} for invoice management
 */
router.post('/:id/generate-invoice', async (req, res) => {
  try {
    const { id } = req.params;
    
    // Import billing queue
    const { billingQueue } = require('../contractBillingWorker');
    
    // Add job to queue
    const job = await billingQueue.add(
      'generate-invoice',
      { contractId: parseInt(id) },
      { priority: 1 }
    );
    
    // Wait for job to complete (with timeout)
    const result = await job.waitUntilFinished(
      billingQueue.events,
      30000 // 30 second timeout
    );
    
    if (result.success) {
      res.json({
        success: true,
        message: 'Invoice generated successfully',
        invoiceId: result.invoiceId,
        invoiceNumber: result.invoiceNumber,
        amount: result.amount,
        nextBillingDate: result.nextBillingDate
      });
    } else {
      res.status(400).json({
        success: false,
        message: 'Failed to generate invoice',
        reason: result.reason
      });
    }
  } catch (err) {
    console.error('Error generating invoice:', err);
    res.status(500).json({ error: 'Failed to generate invoice', details: err.message });
  }
});
 
/**
 * @api {delete} /contracts/:id Soft-delete contract (Admin)
 * @apiName SoftDeleteContract
 * @apiGroup Contracts
 * @apiDescription Soft-deletes a contract by setting status to 'deleted'. Admin-only operation.
 * Preserves all contract data and history while marking it as deleted. Contract remains in
 * database for reporting and audit purposes but is hidden from active lists when filtering
 * by status != 'deleted'.
 * 
 * **Admin Only:**
 * Requires admin role via requireAdmin middleware. Regular users should use the other
 * DELETE endpoint for hard delete (if permitted by tenant access controls).
 * 
 * **Soft Delete Benefits:**
 * - Preserves historical billing data
 * - Maintains referential integrity with invoices
 * - Allows audit trail and reporting
 * - Can be "undeleted" by setting status back to 'active'
 * 
 * **Recommended Approach:**
 * Soft delete is preferred over hard delete for contracts with billing history or
 * generated invoices. Use this endpoint instead of the hard delete for production data.
 * @apiHeader {string} Authorization Bearer JWT token with admin permissions
 * @apiParam {number} id Contract ID to soft-delete (URL parameter)
 * @apiSuccess {string} message Success confirmation message
 * @apiError {number} 403 Forbidden - Admin role required
 * @apiError {number} 404 Contract not found or access denied
 * @apiError {number} 500 Failed to delete contract
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \\
 *        -H "Authorization: Bearer eyJhbGc..." \\
 *        "https://api.everydaytech.au/contracts/5"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Contract deleted successfully"
 *   }
 * @apiExample {json} Error Response (Not Admin):
 *   HTTP/1.1 403 Forbidden
 *   {
 *     "error": "Admin access required"
 *   }
 * @since 2.0.0
 * @see {@link module:routes/contracts.updateContract} for reactivating by setting status='active'
 */
router.delete('/:id', requireAdmin, async (req, res) => {
  const { id } = req.params;
  
  try {
    // Check if contract exists and belongs to tenant
    const checkResult = await pool.query(
      'SELECT contract_id FROM contracts WHERE contract_id = $1 AND tenant_id = $2',
      [id, req.tenant.id]
    );
 
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ error: 'Contract not found or access denied' });
    }
 
    // Soft delete: set status to 'deleted'
    await pool.query(
      `UPDATE contracts SET status = 'deleted', updated_at = NOW() WHERE contract_id = $1`,
      [id]
    );
 
    res.json({ message: 'Contract deleted successfully' });
  } catch (err) {
    console.error('Error deleting contract:', err);
    res.status(500).json({ error: 'Failed to delete contract', details: err.message });
  }
});
 
module.exports = router;