weekly-repricing.js
1 #!/usr/bin/env node 2 3 /** 4 * Weekly Repricing Cron Job 5 * 6 * Fetches exchange rates from Fixer.io and updates country prices weekly. 7 * 8 * Pricing Formula: 9 * 1. Base: $300 USD 10 * 2. PPP Adjustment: Base × (Country PPP / US PPP) 11 * 3. FX Conversion: PPP-adjusted USD × Exchange Rate 12 * 4. Cultural Rounding: Apply culturally-appropriate rounding 13 * - Western markets (US, CA, AU, NZ, UK): Charm pricing (.97/.99) 14 * - German/Nordic (DE, NO, SE, DK, CH, AT): Round numbers 15 * - East Asia (CN, JP, KR): Lucky 8, avoid 4 and 7 16 * - India: Prefer endings in 1 (new beginnings) 17 * - Latin America (MX): Round numbers (stability) 18 * 19 * Example (Australia): 20 * - Base: $300 21 * - PPP: $300 × (64,674 / 80,412) = $241 22 * - FX: $241 × 1.52 = 366.87 AUD 23 * - Cultural Round: 337 AUD ✅ (charm pricing + lucky 7) 24 * 25 * Note: Countries with price_overridden=1 are skipped to preserve manual overrides. 26 * 27 * Schedule: Weekly (Sunday 2am) 28 * Crontab: 0 2 * * 0 cd /path/to/333Method && node src/cron/weekly-repricing.js 29 */ 30 31 import { getAll, run } from './../utils/db.js'; 32 import Logger from '../utils/logger.js'; 33 import '../utils/load-env.js'; 34 35 const logger = new Logger('WeeklyRepricing'); 36 const { FIXER_API_KEY } = process.env; 37 const BASE_USD_PRICE = 300.0; // $300 USD base price 38 const US_PPP = 80412; // US PPP GDP per capita (baseline) 39 40 /** 41 * Fetch exchange rates from Fixer.io 42 * @returns {Promise<Object>} Exchange rates (USD to other currencies) 43 */ 44 async function fetchExchangeRates() { 45 if (!FIXER_API_KEY) { 46 throw new Error('FIXER_API_KEY not set. Sign up at https://fixer.io/ and add to .env'); 47 } 48 49 const currencies = [ 50 'EUR', 51 'GBP', 52 'AUD', 53 'CAD', 54 'JPY', 55 'CNY', 56 'INR', 57 'KRW', 58 'MXN', 59 'IDR', 60 'CHF', 61 'PLN', 62 'SEK', 63 'NOK', 64 'DKK', 65 'SGD', 66 'NZD', 67 ]; 68 69 logger.info(`Fetching exchange rates from Fixer.io...`); 70 71 // Free tier uses data.fixer.io and only supports EUR as base 72 const response = await fetch( 73 `http://data.fixer.io/api/latest?access_key=${FIXER_API_KEY}&symbols=${currencies.join(',')},USD` 74 ); 75 76 if (!response.ok) { 77 throw new Error(`Fixer.io API error: HTTP ${response.status}`); 78 } 79 80 const data = await response.json(); 81 82 if (!data.success) { 83 throw new Error(`Fixer.io API error: ${data.error?.info || 'Unknown error'}`); 84 } 85 86 // Validate data freshness — Fixer.io free tier may return stale data 87 if (data.date) { 88 const rateDate = new Date(data.date); 89 const today = new Date(); 90 const diffDays = (today - rateDate) / (1000 * 60 * 60 * 24); 91 if (diffDays > 2) { 92 throw new Error( 93 `Fixer.io returned stale data (${data.date}, ${Math.round(diffDays)} days old). ` + 94 `Skipping repricing to avoid applying incorrect exchange rates.` 95 ); 96 } 97 } 98 99 // Free tier returns EUR-based rates, convert to USD-based 100 const eurToUsd = data.rates.USD; 101 const usdBasedRates = {}; 102 103 // Convert all EUR-based rates to USD-based rates 104 // Formula: USD -> Currency = (EUR -> Currency) / (EUR -> USD) 105 for (const currency of currencies) { 106 if (data.rates[currency]) { 107 usdBasedRates[currency] = data.rates[currency] / eurToUsd; 108 } 109 } 110 111 logger.success( 112 `Fetched rates for ${Object.keys(usdBasedRates).length} currencies (converted to USD base)` 113 ); 114 return usdBasedRates; 115 } 116 117 /** 118 * Apply PPP adjustment to base price 119 * @param {number} baseUsd - Base USD price 120 * @param {number} countryPPP - Country's PPP GDP per capita 121 * @param {number} usPPP - US PPP GDP per capita (baseline) 122 * @returns {number} PPP-adjusted USD price 123 */ 124 function applyPPPAdjustment(baseUsd, countryPPP, usPPP = US_PPP) { 125 return baseUsd * (countryPPP / usPPP); 126 } 127 128 /** 129 * Round price to nearest number ending in 7 130 * @param {number} price - Raw price 131 * @returns {number} Rounded price ending in 7 132 * @deprecated Use applyCulturalRounding() instead for culturally-appropriate pricing 133 */ 134 function roundToNearest7(price) { 135 // Get base 10 (e.g., 366.87 → 360) 136 const base10 = Math.floor(price / 10) * 10; 137 138 // Calculate options 139 const option7 = base10 + 7; 140 const nextOption7 = base10 + 17; 141 142 // Pick closest 143 const diffTo7 = Math.abs(price - option7); 144 const diffToNext7 = Math.abs(price - nextOption7); 145 146 return diffTo7 < diffToNext7 ? option7 : nextOption7; 147 } 148 149 /** 150 * Apply culturally-appropriate price rounding based on regional preferences 151 */ 152 function applyCulturalRounding(price, countryCode) { 153 // Western markets: Charm pricing (endings in 7 or 9) 154 const charmPricingMarkets = ['US', 'CA', 'AU', 'NZ', 'UK', 'FR', 'IT', 'ES', 'IE']; 155 if (charmPricingMarkets.includes(countryCode)) { 156 const base10 = Math.floor(price / 10) * 10; 157 const option7 = base10 + 7; 158 const option9 = base10 + 9; 159 const nextOption7 = base10 + 17; 160 161 const diffTo7 = Math.abs(price - option7); 162 const diffTo9 = Math.abs(price - option9); 163 const diffToNext7 = Math.abs(price - nextOption7); 164 165 if (diffTo7 <= diffTo9 && diffTo7 <= diffToNext7) return option7; 166 if (diffTo9 < diffToNext7) return option9; 167 return nextOption7; 168 } 169 170 // German/Nordic/Swiss/Austrian: Round numbers (efficiency, clarity) 171 const roundNumberMarkets = ['DE', 'NO', 'SE', 'DK', 'CH', 'AT']; 172 if (roundNumberMarkets.includes(countryCode)) { 173 if (price < 100) { 174 return Math.round(price / 10) * 10; 175 } else if (price < 1000) { 176 return Math.round(price / 50) * 50; 177 } else { 178 return Math.round(price / 100) * 100; 179 } 180 } 181 182 // East Asia (China, Japan, Korea): Lucky 8, avoid 4 and 7 183 if (countryCode === 'CN') { 184 if (price < 1000) { 185 const base100 = Math.floor(price / 100) * 100; 186 const option88 = base100 + 88; 187 const nextOption88 = base100 + 188; 188 const diffTo88 = Math.abs(price - option88); 189 const diffToNext88 = Math.abs(price - nextOption88); 190 191 if (diffTo88 < 20) return option88; 192 if (diffToNext88 < 20) return nextOption88; 193 194 const base10 = Math.floor(price / 10) * 10; 195 return base10 + 8; 196 } else { 197 const base10 = Math.floor(price / 10) * 10; 198 return base10 + 8; 199 } 200 } 201 202 if (countryCode === 'JP') { 203 if (price < 10000) { 204 return Math.round(price / 100) * 100; 205 } else { 206 return Math.round(price / 1000) * 1000; 207 } 208 } 209 210 if (countryCode === 'KR') { 211 const roundedThousand = Math.round(price / 1000) * 1000; 212 const asString = roundedThousand.toString(); 213 214 if (asString.includes('4') || asString.includes('7')) { 215 const nextThousand = roundedThousand + 1000; 216 return nextThousand; 217 } 218 219 return roundedThousand; 220 } 221 222 // India: Prefer endings in 1 (new beginnings, auspicious) 223 if (countryCode === 'IN') { 224 if (price < 1000) { 225 const base10 = Math.floor(price / 10) * 10; 226 return base10 + 1; 227 } else { 228 const base100 = Math.floor(price / 100) * 100; 229 return base100 + 1; 230 } 231 } 232 233 // Latin America (Mexico): Round numbers for stability 234 if (countryCode === 'MX') { 235 if (price < 1000) { 236 return Math.round(price / 100) * 100; 237 } else { 238 return Math.round(price / 500) * 500; 239 } 240 } 241 242 // Indonesia: Round to nearest 1000 for simplicity (large numbers) 243 if (countryCode === 'ID') { 244 return Math.round(price / 1000) * 1000; 245 } 246 247 // Poland: Charm pricing acceptable, prefer 7 (lucky), avoid 13 248 if (countryCode === 'PL') { 249 const base10 = Math.floor(price / 10) * 10; 250 const option7 = base10 + 7; 251 const nextOption7 = base10 + 17; 252 253 const hasThirteen = num => num.toString().includes('13'); 254 255 if (!hasThirteen(option7)) return option7; 256 if (!hasThirteen(nextOption7)) return nextOption7; 257 258 return base10 + 9; 259 } 260 261 // Default: Use charm pricing with 7 (safest universal approach) 262 return roundToNearest7(price); 263 } 264 265 /** 266 * Update country pricing with fresh exchange rates 267 * @param {Object} rates - Exchange rates from Fixer.io 268 */ 269 async function updateCountryPricing(rates) { 270 logger.info('Updating country pricing...'); 271 272 // Get all active countries (skip manually overridden prices) 273 const countries = await getAll(` 274 SELECT country_code, currency_code, ppp_gdp_per_capita, variant_multiplier, price_overridden 275 FROM countries 276 WHERE is_active = true 277 `); 278 279 let updated = 0; 280 let skipped = 0; 281 let errors = 0; 282 283 for (const country of countries) { 284 try { 285 // Skip countries with manual price overrides (preserve cultural optimizations) 286 if (country.price_overridden) { 287 logger.info( 288 `${country.country_code}: Skipping (price manually overridden for cultural optimization)` 289 ); 290 skipped++; 291 continue; 292 } 293 294 // Step 1: PPP adjustment 295 const pppAdjusted = applyPPPAdjustment(BASE_USD_PRICE, country.ppp_gdp_per_capita); 296 297 // Step 2: Apply variant multiplier (for split testing) 298 const variantAdjusted = pppAdjusted * country.variant_multiplier; 299 300 // Step 3: Convert to local currency 301 const rate = country.currency_code === 'USD' ? 1.0 : rates[country.currency_code]; 302 303 if (!rate) { 304 logger.warn( 305 `No exchange rate for ${country.currency_code}, skipping ${country.country_code}` 306 ); 307 errors++; 308 continue; 309 } 310 311 const localPrice = variantAdjusted * rate; 312 313 // Step 4: Apply culturally-appropriate rounding 314 const rounded = applyCulturalRounding(localPrice, country.country_code); 315 316 await run( 317 `UPDATE countries 318 SET 319 price_usd_base = $1, 320 price_usd_ppp = $2, 321 price_local = $3, 322 price_local_formatted = $4, 323 exchange_rate = $5, 324 price_last_updated = CURRENT_TIMESTAMP 325 WHERE country_code = $6`, 326 [ 327 Math.round(BASE_USD_PRICE * 100), // base USD in cents 328 Math.round(pppAdjusted * 100), // PPP-adjusted USD in cents 329 Math.round(rounded * 100), // Local in cents 330 rounded.toString(), // Formatted for display 331 rate, 332 country.country_code, 333 ] 334 ); 335 336 logger.success( 337 `${country.country_code}: ${rounded} ${country.currency_code} (PPP: $${pppAdjusted.toFixed(2)}, Rate: ${rate.toFixed(4)})` 338 ); 339 updated++; 340 } catch (error) { 341 logger.error(`Error updating ${country.country_code}:`, error); 342 errors++; 343 } 344 } 345 346 logger.info(`Updated ${updated} countries, skipped ${skipped} overridden, ${errors} errors`); 347 } 348 349 // ── PayPal subscription plan sync ────────────────────────────────────────── 350 351 // 2Step subscription tier multipliers relative to 333Method base price 352 const TWOSTEP_TIERS = { 353 starter: { multiplier: 0.41, label: 'Starter (4/mo)' }, 354 growth: { multiplier: 0.74, label: 'Growth (8/mo)' }, 355 scale: { multiplier: 1.04, label: 'Scale (12/mo)' }, 356 }; 357 358 // PayPal product IDs (created once, never change) 359 const PAYPAL_PRODUCTS = { 360 live: 'PROD-3B763174TA939220P', 361 sandbox: 'PROD-3V559137HL182344V', 362 }; 363 364 /** 365 * Get a PayPal OAuth2 access token. 366 */ 367 async function getPayPalToken(sandbox = false) { 368 const base = sandbox ? 'https://api-m.sandbox.paypal.com' : 'https://api-m.paypal.com'; 369 const clientId = sandbox ? process.env.PAYPAL_SANDBOX_CLIENT_ID : process.env.PAYPAL_CLIENT_ID; 370 const secret = sandbox ? process.env.PAYPAL_SANDBOX_CLIENT_SECRET : process.env.PAYPAL_CLIENT_SECRET; 371 372 if (!clientId || !secret) { 373 throw new Error(`PayPal ${sandbox ? 'sandbox' : 'live'} credentials not set`); 374 } 375 376 const res = await fetch(`${base}/v1/oauth2/token`, { 377 method: 'POST', 378 headers: { 379 'Authorization': `Basic ${Buffer.from(`${clientId}:${secret}`).toString('base64')}`, 380 'Content-Type': 'application/x-www-form-urlencoded', 381 }, 382 body: 'grant_type=client_credentials', 383 }); 384 385 const data = await res.json(); 386 if (!data.access_token) throw new Error('PayPal auth failed'); 387 return { token: data.access_token, base }; 388 } 389 390 /** 391 * List all existing plans for our product to avoid duplicates. 392 */ 393 async function listPayPalPlans(token, base, productId) { 394 const plans = []; 395 let page = 1; 396 let hasMore = true; 397 398 while (hasMore) { 399 const res = await fetch( 400 `${base}/v1/billing/plans?product_id=${productId}&page_size=20&page=${page}`, 401 { headers: { 'Authorization': `Bearer ${token}` } } 402 ); 403 const data = await res.json(); 404 if (data.plans) plans.push(...data.plans); 405 hasMore = data.plans?.length === 20; 406 page++; 407 } 408 409 return plans; 410 } 411 412 /** 413 * Update pricing on an existing PayPal plan. 414 */ 415 async function updatePlanPricing(token, base, planId, price, currency) { 416 const res = await fetch(`${base}/v1/billing/plans/${planId}/update-pricing-schemes`, { 417 method: 'POST', 418 headers: { 419 'Authorization': `Bearer ${token}`, 420 'Content-Type': 'application/json', 421 }, 422 body: JSON.stringify({ 423 pricing_schemes: [{ 424 billing_cycle_sequence: 1, 425 pricing_scheme: { 426 fixed_price: { value: String(price), currency_code: currency }, 427 }, 428 }], 429 }), 430 }); 431 432 if (!res.ok) { 433 const err = await res.text(); 434 throw new Error(`Update plan ${planId} failed ${res.status}: ${err}`); 435 } 436 } 437 438 /** 439 * Create a new PayPal subscription plan. 440 */ 441 async function createPayPalPlan(token, base, productId, name, price, currency) { 442 const res = await fetch(`${base}/v1/billing/plans`, { 443 method: 'POST', 444 headers: { 445 'Authorization': `Bearer ${token}`, 446 'Content-Type': 'application/json', 447 }, 448 body: JSON.stringify({ 449 product_id: productId, 450 name, 451 billing_cycles: [{ 452 frequency: { interval_unit: 'MONTH', interval_count: 1 }, 453 tenure_type: 'REGULAR', 454 sequence: 1, 455 total_cycles: 0, 456 pricing_scheme: { fixed_price: { value: String(price), currency_code: currency } }, 457 }], 458 payment_preferences: { 459 auto_bill_outstanding: true, 460 setup_fee_failure_action: 'CONTINUE', 461 payment_failure_threshold: 3, 462 }, 463 }), 464 }); 465 466 const data = await res.json(); 467 if (!data.id) throw new Error(`Create plan failed: ${JSON.stringify(data)}`); 468 return data.id; 469 } 470 471 /** 472 * Sync 2Step subscription plan pricing to PayPal. 473 */ 474 async function sync2StepPayPalPlans(rates) { 475 logger.info('Syncing 2Step subscription plans to PayPal...'); 476 477 // Get active countries with their repriced local values 478 const countries = await getAll(` 479 SELECT country_code, currency_code, price_local, ppp_gdp_per_capita 480 FROM countries WHERE is_active = true 481 `); 482 483 // Only sync countries we have currency support for in PayPal 484 const paypalCurrencies = new Set([ 485 'AUD', 'USD', 'GBP', 'CAD', 'NZD', 'EUR', 'CHF', 'SEK', 'NOK', 'DKK', 486 'PLN', 'SGD', 'JPY', 'CNY', 'INR', 'KRW', 'MXN', 487 ]); 488 489 for (const env of ['sandbox', 'live']) { 490 const sandbox = env === 'sandbox'; 491 let token, base; 492 try { 493 ({ token, base } = await getPayPalToken(sandbox)); 494 } catch (e) { 495 logger.warn(`Skipping PayPal ${env}: ${e.message}`); 496 continue; 497 } 498 499 const productId = sandbox ? PAYPAL_PRODUCTS.sandbox : PAYPAL_PRODUCTS.live; 500 const existingPlans = await listPayPalPlans(token, base, productId); 501 502 // Index existing plans by name for matching 503 const plansByName = {}; 504 for (const p of existingPlans) { 505 plansByName[p.name] = p; 506 } 507 508 let updated = 0; 509 let created = 0; 510 511 for (const country of countries) { 512 if (!paypalCurrencies.has(country.currency_code)) continue; 513 const cc = country.country_code; 514 const cur = country.currency_code; 515 516 // Calculate 333Method base price in local currency (from DB, already repriced) 517 const baseLocal = country.price_local / 100; // stored in cents 518 519 for (const [tier, config] of Object.entries(TWOSTEP_TIERS)) { 520 const rawPrice = baseLocal * config.multiplier; 521 const price = applyCulturalRounding(rawPrice, cc); 522 const planName = `Video Reviews — ${config.label} ${cc}`; 523 524 const existing = plansByName[planName]; 525 526 if (existing) { 527 try { 528 await updatePlanPricing(token, base, existing.id, price, cur); 529 logger.info(` ${env} ${planName}: ${price} ${cur} (updated)`); 530 updated++; 531 } catch (e) { 532 logger.warn(` ${env} ${planName}: update failed — ${e.message}`); 533 } 534 } else { 535 try { 536 const planId = await createPayPalPlan(token, base, productId, planName, price, cur); 537 logger.info(` ${env} ${planName}: ${price} ${cur} (created ${planId})`); 538 created++; 539 } catch (e) { 540 logger.warn(` ${env} ${planName}: create failed — ${e.message}`); 541 } 542 } 543 } 544 } 545 546 logger.success(`PayPal ${env}: ${updated} plans updated, ${created} plans created`); 547 } 548 } 549 550 /** 551 * Main function 552 */ 553 async function main() { 554 logger.info('Starting weekly repricing...'); 555 556 // Fetch exchange rates 557 const rates = await fetchExchangeRates(); 558 559 // Update country pricing 560 await updateCountryPricing(rates); 561 562 // Export updated pricing to CF Worker for the brand website 563 try { 564 const { exportPricing } = await import('../api/pricing-export.js'); 565 await exportPricing(); 566 } catch (exportError) { 567 logger.warn(`Pricing export to CF Worker failed: ${exportError.message}`); 568 } 569 570 // Sync 2Step subscription plan pricing to PayPal 571 try { 572 await sync2StepPayPalPlans(rates); 573 } catch (paypalError) { 574 logger.warn(`PayPal plan sync failed: ${paypalError.message}`); 575 } 576 577 logger.success('Weekly repricing completed successfully'); 578 579 // Show summary 580 const summary = await getAll(` 581 SELECT 582 pricing_tier, 583 COUNT(*) as count, 584 MIN(price_local / 100.0) as min_price, 585 MAX(price_local / 100.0) as max_price, 586 AVG(price_local / 100.0) as avg_price 587 FROM countries 588 WHERE is_active = true 589 GROUP BY pricing_tier 590 ORDER BY avg_price DESC 591 `); 592 593 logger.info('Pricing Summary by Tier:'); 594 summary.forEach(tier => { 595 logger.info( 596 ` ${tier.pricing_tier}: ${tier.count} countries, ${Number(tier.min_price).toFixed(0)}-${Number(tier.max_price).toFixed(0)} avg ${Number(tier.avg_price).toFixed(0)}` 597 ); 598 }); 599 600 return 0; 601 } 602 603 // Run if called directly 604 if (import.meta.url === `file://${process.argv[1]}`) { 605 main() 606 .then(code => process.exit(code)) 607 .catch(error => { 608 console.error('Fatal error:', error); 609 process.exit(1); 610 }); 611 } 612 613 export { 614 fetchExchangeRates, 615 updateCountryPricing, 616 applyPPPAdjustment, 617 roundToNearest7, 618 applyCulturalRounding, 619 };