fix-product-pricing.js

Go to the documentation of this file.

#!/usr/bin/env node
/**
 * Fix Product Pricing from Contract Line Items
 * 
 * Updates product prices based on actual contract line item prices
 */
 
const { Pool } = require('pg');
 
const pool = new Pool({
  host: 'rmm-psa-db-do-user-28531160-0.i.db.ondigitalocean.com',
  port: 25060,
  database: 'defaultdb',
  user: 'doadmin',
  password: 'AVNS_J8RJAmsEwsHFG52_-F2',
  ssl: { rejectUnauthorized: false }
});
 
/**
 *
 */
async function fixProductPricing() {
  const tenantId = '00000000-0000-0000-0000-000000000001';
  
  console.log('╔══════════════════════════════════════════════════════════╗');
  console.log('║   Fixing Product Pricing from Contract Line Items       ║');
  console.log('╚══════════════════════════════════════════════════════════╝\n');
  
  // Get all products that have associated contract line items
  const result = await pool.query(`
    SELECT 
      p.product_id,
      p.name,
      p.unit_price as current_price,
      cli.unit_price as contract_price,
      cli.description,
      c.billing_interval
    FROM products p
    JOIN contract_line_items cli ON p.product_id = cli.product_id
    JOIN contracts c ON cli.contract_id = c.contract_id
    WHERE p.tenant_id = $1
    ORDER BY p.product_id, cli.unit_price DESC
  `, [tenantId]);
  
  console.log(`Found ${result.rows.length} product-contract associations\n`);
  
  // Group by product and calculate average/most common price
  const productPrices = {};
  
  for (const row of result.rows) {
    if (!productPrices[row.product_id]) {
      productPrices[row.product_id] = {
        name: row.name,
        current_price: parseFloat(row.current_price || 0),
        prices: []
      };
    }
    
    const price = parseFloat(row.contract_price || 0);
    if (price > 0) {
      productPrices[row.product_id].prices.push({
        price,
        interval: row.billing_interval
      });
    }
  }
  
  let updated = 0;
  
  for (const [productId, data] of Object.entries(productPrices)) {
    if (data.prices.length === 0) continue;
    
    // Convert all prices to monthly equivalent
    const monthlyPrices = data.prices.map(p => {
      let monthly = p.price;
      if (p.interval === 'yearly') monthly = p.price / 12;
      else if (p.interval === 'quarterly') monthly = p.price / 3;
      else if (p.interval === 'semi_annually') monthly = p.price / 6;
      return monthly;
    });
    
    // Use the most common price (or average if all different)
    const avgPrice = monthlyPrices.reduce((a, b) => a + b, 0) / monthlyPrices.length;
    const newPrice = Math.round(avgPrice * 100) / 100; // Round to 2 decimals
    
    if (newPrice !== data.current_price && newPrice > 0) {
      await pool.query(
        'UPDATE products SET unit_price = $1, price_retail = $1, price_ex_tax = $1 WHERE product_id = $2',
        [newPrice, productId]
      );
      
      console.log(`✓ Updated: ${data.name}`);
      console.log(`  Old price: $${data.current_price.toFixed(2)}/month`);
      console.log(`  New price: $${newPrice.toFixed(2)}/month`);
      console.log(`  Based on ${data.prices.length} contract(s)\n`);
      
      updated++;
    }
  }
  
  console.log(`\n✓ Updated ${updated} product prices\n`);
  
  // Show current product pricing
  const products = await pool.query(`
    SELECT 
      p.product_id,
      p.name,
      p.unit_price,
      COUNT(cli.line_item_id) as contract_count
    FROM products p
    LEFT JOIN contract_line_items cli ON p.product_id = cli.product_id
    WHERE p.tenant_id = $1
    GROUP BY p.product_id, p.name, p.unit_price
    HAVING COUNT(cli.line_item_id) > 0
    ORDER BY p.unit_price DESC
  `, [tenantId]);
  
  console.log('UPDATED PRODUCT CATALOG');
  console.log('=======================\n');
  
  for (const product of products.rows) {
    console.log(`${product.name.padEnd(40)} $${parseFloat(product.unit_price).toFixed(2)}/month (${product.contract_count} contracts)`);
  }
  
  await pool.end();
}
 
fixProductPricing().catch(console.error);