show-products.js

Go to the documentation of this file.

#!/usr/bin/env node
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 showProducts() {
  const tenantId = '00000000-0000-0000-0000-000000000001';
  
  const result = await pool.query(`
    SELECT 
      p.product_id,
      p.name,
      p.description,
      p.unit_price,
      COUNT(cli.line_item_id) as active_contracts,
      SUM(cli.unit_price) as total_contract_value
    FROM products p
    LEFT JOIN contract_line_items cli ON p.product_id = cli.product_id
    LEFT JOIN contracts c ON cli.contract_id = c.contract_id AND c.status = 'active'
    WHERE p.tenant_id = $1
    GROUP BY p.product_id, p.name, p.description, p.unit_price
    ORDER BY p.unit_price DESC, active_contracts DESC
  `, [tenantId]);
  
  console.log('\n╔══════════════════════════════════════════════════════════╗');
  console.log('║   Product Catalog with Corrected Pricing                ║');
  console.log('╚══════════════════════════════════════════════════════════╝\n');
  
  let totalProductsWithPricing = 0;
  let totalProductsInUse = 0;
  
  for (const product of result.rows) {
    const price = parseFloat(product.unit_price || 0);
    const contracts = parseInt(product.active_contracts || 0);
    
    if (contracts > 0) {
      console.log(`${product.name}`);
      console.log(`  Unit Price: $${price.toFixed(2)}/month`);
      console.log(`  Active Contracts: ${contracts}`);
      console.log(`  Total Contract Value: $${parseFloat(product.total_contract_value || 0).toFixed(2)}`);
      console.log('');
      totalProductsInUse++;
    }
    
    if (price > 0) totalProductsWithPricing++;
  }
  
  console.log(`Summary: ${totalProductsInUse} products in active use, ${totalProductsWithPricing} products with pricing\n`);
  
  await pool.end();
}
 
showProducts().catch(console.error);