Database Integration for CPQ — Queryable Configuration History

-- Stored procedure to get product configuration options
CREATE PROCEDURE GetProductConfigurationOptions
    @ProductID INT,
    @CustomerSegment VARCHAR(50) = NULL
AS
BEGIN
    SELECT
        co.OptionID,
        co.OptionName,
        co.OptionType,
        co.DefaultValue,
        co.MinValue,
        co.MaxValue,
        co.PriceModifier,
        CASE
            WHEN cs.CustomerSegment IS NOT NULL
            THEN cs.SegmentPriceModifier
            ELSE co.PriceModifier
        END AS EffectivePriceModifier
    FROM ConfigurationOptions co
    LEFT JOIN CustomerSegmentPricing cs
        ON co.OptionID = cs.OptionID
        AND cs.CustomerSegment = @CustomerSegment
    WHERE co.ProductID = @ProductID
        AND co.IsActive = 1
        AND co.EffectiveDate <= GETDATE()
        AND (co.ExpiryDate IS NULL OR co.ExpiryDate > GETDATE())
    ORDER BY co.DisplayOrder;
END

-- PostgreSQL function for configuration analytics
CREATE OR REPLACE FUNCTION get_configuration_analytics(
    start_date DATE,
    end_date DATE
) RETURNS TABLE(
    product_id INTEGER,
    product_name VARCHAR,
    total_configurations BIGINT,
    avg_configuration_value NUMERIC,
    popular_options JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.id AS product_id,
        p.name AS product_name,
        COUNT(c.id) AS total_configurations,
        AVG(c.total_price) AS avg_configuration_value,
        jsonb_agg(
            jsonb_build_object(
                'option', co.option_name,
                'count', option_counts.option_count
            ) ORDER BY option_counts.option_count DESC
        ) AS popular_options
    FROM products p
    LEFT JOIN configurations c ON p.id = c.product_id
        AND c.created_at BETWEEN start_date AND end_date
    LEFT JOIN LATERAL (
        SELECT
            co.option_name,
            COUNT(*) AS option_count
        FROM configuration_options co
        JOIN jsonb_array_elements(c.selected_options) AS selected(option)
            ON (selected.option->>'id')::INTEGER = co.id
        GROUP BY co.option_name
        LIMIT 5
    ) option_counts ON true
    GROUP BY p.id, p.name;
END;
$$ LANGUAGE plpgsql;

// Node.js MongoDB integration for configuration storage
const { MongoClient } = require('mongodb');

class ConfigurationDatabase {
  constructor(connectionString) {
    this.client = new MongoClient(connectionString);
    this.db = this.client.db('ignitionary');
  }

  async saveConfiguration(configData) {
    const configurations = this.db.collection('configurations');

    const document = {
      productId: configData.productId,
      customerId: configData.customerId,
      sessionId: configData.sessionId,
      selectedOptions: configData.options,
      totalPrice: configData.totalPrice,
      metadata: {
        timeSpent: configData.timeSpent,
        revisions: configData.revisions,
        source: configData.source
      },
      createdAt: new Date(),
      status: 'draft'
    };

    const result = await configurations.insertOne(document);
    return result.insertedId;
  }

  async getConfigurationTrends(productId, days = 30) {
    const configurations = this.db.collection('configurations');
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - days);

    const pipeline = [
      {
        $match: {
          productId: productId,
          createdAt: { $gte: startDate }
        }
      },
      {
        $unwind: '$selectedOptions'
      },
      {
        $group: {
          _id: '$selectedOptions.optionId',
          optionName: { $first: '$selectedOptions.name' },
          count: { $sum: 1 },
          avgPrice: { $avg: '$selectedOptions.price' }
        }
      },
      {
        $sort: { count: -1 }
      },
      {
        $limit: 10
      }
    ];

    return await configurations.aggregate(pipeline).toArray();
  }
}

// Real-time price updates using database triggers
async function setupRealTimeSync() {
  // SQL Server Change Data Capture setup
  const sql = require('mssql');

  const config = {
    server: process.env.DB_SERVER,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    options: {
      encrypt: true,
      enableArithAbort: true
    }
  };

  const pool = await sql.connect(config);

  // Monitor price changes
  const request = pool.request();
  request.stream = true;

  request.query(`
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_ProductPricing(
      sys.fn_cdc_get_min_lsn('dbo_ProductPricing'),
      sys.fn_cdc_get_max_lsn(),
      'all'
    )
  `);

  request.on('row', async (row) => {
    // Update configurator cache with new pricing
    const productId = row.__$start_lsn;
    const operation = row.__$operation;

    if (operation === 2 || operation === 4) { // Insert or Update
      await updateConfiguratorPricing(productId, row);

      // Notify active configurator sessions
      await notifyActiveConfigurators(productId, {
        type: 'price_update',
        productId: productId,
        newPrice: row.Price
      });
    }
  });
}

async function updateConfiguratorPricing(productId, priceData) {
  // Update Redis cache or application state
  const redis = require('redis');
  const client = redis.createClient();

  await client.setex(
    `pricing:${productId}`,
    3600, // 1 hour cache
    JSON.stringify(priceData)
  );
}