1  #!/usr/bin/env node
  2  
  3  /**
  4   * Purchases CLI
  5   *
  6   * Manage Audit&Fix report purchases.
  7   *
  8   * Usage:
  9   *   node src/cli/purchases.js list [status]
 10   *   node src/cli/purchases.js refund <id> [reason]
 11   *   node src/cli/purchases.js retry <id>
 12   *   node src/cli/purchases.js stats
 13   */
 14  
 15  import '../utils/load-env.js';
 16  import { run, getOne, getAll } from '../utils/db.js';
 17  import Logger from '../utils/logger.js';
 18  
 19  const logger = new Logger('PurchasesCLI');
 20  
 21  async function listPurchases(status) {
 22    let query =
 23      'SELECT id, email, landing_page_url, amount, currency, status, report_grade, created_at, delivered_at FROM purchases';
 24    const params = [];
 25  
 26    if (status) {
 27      query += ' WHERE status = $1';
 28      params.push(status);
 29    }
 30  
 31    query += ' ORDER BY created_at DESC LIMIT 50';
 32  
 33    const purchases = await getAll(query, params.length ? params : undefined);
 34  
 35    if (purchases.length === 0) {
 36      console.log('No purchases found.');
 37      return;
 38    }
 39  
 40    console.table(
 41      purchases.map(p => ({
 42        ID: p.id,
 43        Email: p.email,
 44        URL: p.landing_page_url.substring(0, 40),
 45        Amount: `${p.currency} ${(p.amount / 100).toFixed(2)}`,
 46        Status: p.status,
 47        Grade: p.report_grade || '-',
 48        Created: p.created_at?.toISOString?.()?.substring(0, 16) ?? String(p.created_at ?? '').substring(0, 16),
 49        Delivered: p.delivered_at
 50          ? (p.delivered_at?.toISOString?.()?.substring(0, 16) ?? String(p.delivered_at).substring(0, 16))
 51          : '-',
 52      }))
 53    );
 54  }
 55  
 56  async function refundPurchase(id, reason) {
 57    const purchase = await getOne('SELECT * FROM purchases WHERE id = $1', [id]);
 58    if (!purchase) {
 59      logger.error(`Purchase #${id} not found`);
 60      return;
 61    }
 62  
 63    if (purchase.status === 'refunded') {
 64      logger.warn(`Purchase #${id} is already refunded`);
 65      return;
 66    }
 67  
 68    if (!purchase.paypal_capture_id) {
 69      logger.error(`Purchase #${id} has no PayPal capture ID`);
 70      return;
 71    }
 72  
 73    // Import and call PayPal refund
 74    const { refundPayment } = await import('../payment/paypal.js');
 75    const result = await refundPayment(purchase.paypal_capture_id, reason);
 76  
 77    // Update purchase status
 78    await run(
 79      `UPDATE purchases
 80       SET status = 'refunded', refunded_at = NOW(), refund_reason = $1, updated_at = NOW()
 81       WHERE id = $2`,
 82      [reason || 'Refunded via CLI', id]
 83    );
 84  
 85    logger.success(`Purchase #${id} refunded: ${result.refund_id}`);
 86  }
 87  
 88  async function retryPurchase(id) {
 89    const purchase = await getOne('SELECT * FROM purchases WHERE id = $1', [id]);
 90    if (!purchase) {
 91      logger.error(`Purchase #${id} not found`);
 92      return;
 93    }
 94  
 95    if (!['failed', 'processing'].includes(purchase.status)) {
 96      logger.warn(
 97        `Purchase #${id} status is '${purchase.status}', expected 'failed' or 'processing'`
 98      );
 99      return;
100    }
101  
102    await run(
103      `UPDATE purchases
104       SET status = 'paid', error_message = NULL, retry_count = 0, updated_at = NOW()
105       WHERE id = $1`,
106      [id]
107    );
108  
109    logger.success(`Purchase #${id} re-queued for processing`);
110  }
111  
112  async function showStats() {
113    // Check if table exists
114    const tableExists = await getOne(
115      "SELECT tablename FROM pg_tables WHERE schemaname = current_schema() AND tablename = 'purchases'"
116    );
117  
118    if (!tableExists) {
119      console.log('Purchases table does not exist yet. Run the migration first.');
120      return;
121    }
122  
123    const statusCounts = await getAll(
124      'SELECT status, COUNT(*) as count FROM purchases GROUP BY status ORDER BY count DESC'
125    );
126  
127    const totalRevenue = await getOne(
128      `SELECT
129         COUNT(*) as total_orders,
130         COALESCE(SUM(amount_usd), 0) as total_usd,
131         COALESCE(AVG(amount_usd), 0) as avg_usd
132       FROM purchases WHERE status NOT IN ('refunded', 'failed')`
133    );
134  
135    const revenueByCountry = await getAll(
136      `SELECT country_code, COUNT(*) as orders, SUM(amount_usd) as revenue_usd
137       FROM purchases WHERE status NOT IN ('refunded', 'failed')
138       GROUP BY country_code ORDER BY revenue_usd DESC LIMIT 10`
139    );
140  
141    const avgDelivery = await getOne(
142      `SELECT AVG(EXTRACT(EPOCH FROM (delivered_at - created_at)) / 3600) as avg_hours
143       FROM purchases WHERE delivered_at IS NOT NULL`
144    );
145  
146    console.log('\n=== Purchase Statistics ===\n');
147  
148    console.log('By Status:');
149    statusCounts.forEach(s => console.log(`  ${s.status}: ${s.count}`));
150  
151    console.log(
152      `\nRevenue: $${(Number(totalRevenue.total_usd) / 100).toFixed(2)} USD (${totalRevenue.total_orders} orders)`
153    );
154    console.log(`Average: $${(Number(totalRevenue.avg_usd) / 100).toFixed(2)} USD`);
155  
156    if (avgDelivery?.avg_hours) {
157      console.log(`Avg Delivery: ${Number(avgDelivery.avg_hours).toFixed(1)} hours`);
158    }
159  
160    if (revenueByCountry.length > 0) {
161      console.log('\nTop Countries:');
162      revenueByCountry.forEach(c =>
163        console.log(
164          `  ${c.country_code || 'Unknown'}: ${c.orders} orders, $${(Number(c.revenue_usd) / 100).toFixed(2)}`
165        )
166      );
167    }
168  
169    console.log('');
170  }
171  
172  async function main() {
173    const command = process.argv[2];
174  
175    try {
176      switch (command) {
177        case 'list':
178          await listPurchases(process.argv[3]);
179          break;
180        case 'refund': {
181          const id = parseInt(process.argv[3]);
182          const reason = process.argv.slice(4).join(' ') || undefined;
183          if (!id) {
184            console.error('Usage: node src/cli/purchases.js refund <id> [reason]');
185            process.exit(1);
186          }
187          await refundPurchase(id, reason);
188          break;
189        }
190        case 'retry': {
191          const retryId = parseInt(process.argv[3]);
192          if (!retryId) {
193            console.error('Usage: node src/cli/purchases.js retry <id>');
194            process.exit(1);
195          }
196          await retryPurchase(retryId);
197          break;
198        }
199        case 'stats':
200          await showStats();
201          break;
202        default:
203          console.log('Purchases CLI');
204          console.log('');
205          console.log('Usage:');
206          console.log('  list [status]     Show purchases (optional: filter by status)');
207          console.log('  refund <id> [reason]  Trigger PayPal refund');
208          console.log('  retry <id>        Re-queue failed purchase');
209          console.log('  stats             Revenue statistics');
210          console.log('');
211          console.log('Statuses: paid, processing, report_generated, delivered, failed, refunded');
212          process.exit(command ? 1 : 0);
213      }
214    } catch (error) {
215      logger.error(error.message);
216      process.exit(1);
217    }
218  }
219  
220  main();