1  /**
  2   * Country Pricing Database Utilities
  3   *
  4   * Provides functions to query country-specific pricing and configuration
  5   * from the countries table in the database.
  6   *
  7   * Replaces hardcoded config with database-driven approach for easier
  8   * price updates and analytics.
  9   */
 10  
 11  import { run, getOne, getAll } from './db.js';
 12  import Logger from './logger.js';
 13  
 14  const logger = new Logger('CountryPricing');
 15  
 16  /**
 17   * Parse a JSON field that may be a string (SQLite) or already-parsed object (PG jsonb).
 18   * @param {*} val
 19   * @returns {Array|Object}
 20   */
 21  function parseJsonField(val) {
 22    if (!val) return [];
 23    if (typeof val === 'object') return val; // already parsed (PG jsonb)
 24    try { return JSON.parse(val); } catch { return []; }
 25  }
 26  
 27  /**
 28   * Get country information including pricing
 29   * @param {string} countryCode - ISO country code (e.g., 'US', 'AU')
 30   * @returns {Promise<Object|null>} Country configuration or null if not found
 31   */
 32  export async function getCountry(countryCode) {
 33    try {
 34      const country = await getOne(
 35        `SELECT * FROM countries
 36         WHERE country_code = $1 AND is_active = true`,
 37        [countryCode.toUpperCase()]
 38      );
 39  
 40      if (!country) {
 41        logger.warn(`Country not found: ${countryCode}`);
 42        return null;
 43      }
 44      return {
 45        ...country,
 46        priceUsd: country.price_usd / 100, // Convert cents to dollars
 47        commonCities: parseJsonField(country.common_cities),
 48        companyTypes: parseJsonField(country.company_types),
 49        companyKeywords: parseJsonField(country.company_keywords),
 50        keyPageNames: parseJsonField(country.key_page_names),
 51      };
 52    } catch (error) {
 53      logger.error(`Error fetching country ${countryCode}:`, error);
 54      return null;
 55    }
 56  }
 57  
 58  /**
 59   * Get pricing for a country
 60   * @param {string} countryCode - ISO country code
 61   * @returns {Promise<Object|null>} Pricing information
 62   */
 63  export async function getPrice(countryCode) {
 64    try {
 65      const result = await getOne(
 66        `SELECT
 67          country_code,
 68          country_name,
 69          price_usd_base,
 70          price_usd_ppp,
 71          price_local,
 72          price_local_formatted,
 73          currency_code,
 74          currency_symbol,
 75          exchange_rate,
 76          pricing_tier,
 77          pricing_variant,
 78          price_last_updated,
 79          market_notes,
 80          is_price_sensitive,
 81          price_overridden,
 82          override_reason
 83        FROM countries
 84        WHERE country_code = $1 AND is_active = true`,
 85        [countryCode.toUpperCase()]
 86      );
 87  
 88      if (!result) {
 89        return null;
 90      }
 91  
 92      return {
 93        countryCode: result.country_code,
 94        countryName: result.country_name,
 95        priceUsd: result.price_usd_ppp / 100, // PPP-adjusted USD
 96        priceLocal: result.price_local / 100, // Local currency
 97        priceFormatted: result.price_local_formatted, // Display format (e.g., "347")
 98        priceCents: result.price_usd_ppp, // For backward compatibility
 99        priceLocalCents: result.price_local, // Local currency in cents
100        currency: result.currency_code,
101        currencySymbol: result.currency_symbol,
102        formattedPrice: `${result.currency_symbol}${result.price_local_formatted}`,
103        tier: result.pricing_tier,
104        variant: result.pricing_variant,
105        exchangeRate: result.exchange_rate,
106        lastUpdated: result.price_last_updated,
107        notes: result.market_notes,
108        isPriceSensitive: Boolean(result.is_price_sensitive),
109        overridden: Boolean(result.price_overridden),
110        overrideReason: result.override_reason,
111      };
112    } catch (error) {
113      logger.error(`Error fetching price for ${countryCode}:`, error);
114      return null;
115    }
116  }
117  
118  /**
119   * Get all active countries
120   * @param {Object} options - Query options
121   * @param {string} options.orderBy - Order by field (default: 'price_usd DESC')
122   * @param {number} options.limit - Limit results (optional)
123   * @returns {Promise<Array<Object>>} Array of countries
124   */
125  export async function getAllCountries(options = {}) {
126    const { orderBy = 'price_usd DESC', limit } = options;
127  
128    try {
129      let sql = `
130        SELECT * FROM countries
131        WHERE is_active = true
132        ORDER BY ${orderBy}
133      `;
134  
135      if (limit) {
136        sql += ` LIMIT ${parseInt(limit, 10)}`;
137      }
138  
139      const countries = await getAll(sql);
140  
141      return countries.map(c => ({
142        ...c,
143        priceUsd: c.price_usd / 100,
144        commonCities: parseJsonField(c.common_cities),
145        companyTypes: parseJsonField(c.company_types),
146        companyKeywords: parseJsonField(c.company_keywords),
147        keyPageNames: parseJsonField(c.key_page_names),
148      }));
149    } catch (error) {
150      logger.error('Error fetching all countries:', error);
151      return [];
152    }
153  }
154  
155  /**
156   * Get countries by pricing tier
157   * @param {string} tier - Pricing tier (e.g., 'Premium', 'Standard')
158   * @returns {Promise<Array<Object>>} Array of countries in that tier
159   */
160  export async function getCountriesByTier(tier) {
161    try {
162      const countries = await getAll(
163        `SELECT * FROM countries
164         WHERE pricing_tier = $1 AND is_active = true
165         ORDER BY price_usd DESC`,
166        [tier]
167      );
168  
169      return countries.map(c => ({
170        ...c,
171        priceUsd: c.price_usd / 100,
172      }));
173    } catch (error) {
174      logger.error(`Error fetching countries for tier ${tier}:`, error);
175      return [];
176    }
177  }
178  
179  /**
180   * Get pricing summary (statistics across all countries)
181   * @returns {Promise<Object>} Pricing statistics
182   */
183  export async function getPricingSummary() {
184    try {
185      const stats = await getOne(
186        `SELECT
187          COUNT(*) as total_countries,
188          MIN(price_usd) as min_price_cents,
189          MAX(price_usd) as max_price_cents,
190          AVG(price_usd) as avg_price_cents,
191          COUNT(DISTINCT pricing_tier) as tier_count,
192          SUM(CASE WHEN requires_gdpr_check = true THEN 1 ELSE 0 END) as gdpr_countries,
193          SUM(CASE WHEN is_price_sensitive = true THEN 1 ELSE 0 END) as price_sensitive_countries
194        FROM countries
195        WHERE is_active = true`
196      );
197  
198      const tierBreakdown = await getAll(
199        `SELECT
200          pricing_tier,
201          COUNT(*) as count,
202          AVG(price_usd) as avg_price
203        FROM countries
204        WHERE is_active = true
205        GROUP BY pricing_tier
206        ORDER BY avg_price DESC`
207      );
208  
209      return {
210        totalCountries: stats.total_countries,
211        minPrice: stats.min_price_cents / 100,
212        maxPrice: stats.max_price_cents / 100,
213        avgPrice: Math.round(stats.avg_price_cents) / 100,
214        tierCount: stats.tier_count,
215        gdprCountries: stats.gdpr_countries,
216        priceSensitiveCountries: stats.price_sensitive_countries,
217        tierBreakdown: tierBreakdown.map(t => ({
218          tier: t.pricing_tier,
219          count: t.count,
220          avgPrice: Math.round(t.avg_price) / 100,
221        })),
222      };
223    } catch (error) {
224      logger.error('Error fetching pricing summary:', error);
225      return null;
226    }
227  }
228  
229  /**
230   * Override price for a country
231   * @param {string} countryCode - ISO country code
232   * @param {number} newPriceUsd - New price in USD (dollars, not cents)
233   * @param {string} reason - Reason for override
234   * @returns {Promise<boolean>} Success status
235   */
236  export async function overridePrice(countryCode, newPriceUsd, reason) {
237    try {
238      const result = await run(
239        `UPDATE countries
240         SET
241           price_usd = $1,
242           price_overridden = true,
243           override_reason = $2,
244           override_date = CURRENT_TIMESTAMP,
245           updated_at = CURRENT_TIMESTAMP
246         WHERE country_code = $3 AND is_active = true`,
247        [Math.round(newPriceUsd * 100), reason, countryCode.toUpperCase()]
248      );
249  
250      if (result.changes === 0) {
251        logger.warn(`No country found with code: ${countryCode}`);
252        return false;
253      }
254  
255      logger.success(`Price overridden for ${countryCode}: $${newPriceUsd} USD (reason: ${reason})`);
256      return true;
257    } catch (error) {
258      logger.error(`Error overriding price for ${countryCode}:`, error);
259      return false;
260    }
261  }
262  
263  /**
264   * Get Accept-Language header for a country
265   * @param {string} countryCode - ISO country code
266   * @returns {Promise<string|null>} Accept-Language header value
267   */
268  export async function getAcceptLanguage(countryCode) {
269    const country = await getCountry(countryCode);
270    return country?.accept_language || null;
271  }
272  
273  /**
274   * Get Google domain for a country
275   * @param {string} countryCode - ISO country code
276   * @returns {Promise<string|null>} Google domain (e.g., 'google.com.au')
277   */
278  export async function getGoogleDomain(countryCode) {
279    const country = await getCountry(countryCode);
280    return country?.google_domain || null;
281  }
282  
283  /**
284   * Check if country requires GDPR compliance
285   * @param {string} countryCode - ISO country code
286   * @returns {Promise<boolean>} True if GDPR required
287   */
288  export async function requiresGDPR(countryCode) {
289    const country = await getCountry(countryCode);
290    return Boolean(country?.requires_gdpr_check);
291  }
292  
293  // CLI functionality for testing
294  if (import.meta.url === `file://${process.argv[1]}`) {
295    const command = process.argv[2];
296    const arg = process.argv[3];
297  
298    const exit = (code = 0) => process.exit(code);
299  
300    if (command === 'get' && arg) {
301      getCountry(arg).then(country => { console.log(JSON.stringify(country, null, 2)); exit(); });
302    } else if (command === 'price' && arg) {
303      getPrice(arg).then(price => { console.log(JSON.stringify(price, null, 2)); exit(); });
304    } else if (command === 'all') {
305      getAllCountries().then(countries => { console.log(JSON.stringify(countries, null, 2)); exit(); });
306    } else if (command === 'tier' && arg) {
307      getCountriesByTier(arg).then(countries => { console.log(JSON.stringify(countries, null, 2)); exit(); });
308    } else if (command === 'summary') {
309      getPricingSummary().then(summary => { console.log(JSON.stringify(summary, null, 2)); exit(); });
310    } else if (command === 'override' && process.argv.length >= 6) {
311      const code = process.argv[3];
312      const price = parseFloat(process.argv[4]);
313      const reason = process.argv.slice(5).join(' ');
314      overridePrice(code, price, reason).then(success => {
315        exit(success ? 0 : 1);
316      }).catch(err => {
317        console.error(err.message);
318        exit(1);
319      });
320    } else {
321      console.log('Country Pricing Utilities\n');
322      console.log('Usage:');
323      console.log('  node src/utils/country-pricing.js get <country_code>');
324      console.log('  node src/utils/country-pricing.js price <country_code>');
325      console.log('  node src/utils/country-pricing.js all');
326      console.log('  node src/utils/country-pricing.js tier <tier_name>');
327      console.log('  node src/utils/country-pricing.js summary');
328      console.log('  node src/utils/country-pricing.js override <code> <price> <reason>\n');
329      console.log('Examples:');
330      console.log('  node src/utils/country-pricing.js get AU');
331      console.log('  node src/utils/country-pricing.js price US');
332      console.log('  node src/utils/country-pricing.js tier Premium');
333      console.log('  node src/utils/country-pricing.js summary');
334      console.log(
335        '  node src/utils/country-pricing.js override AU 349 Market research shows higher willingness to pay\n'
336      );
337    }
338  }
339  
340  export default {
341    getCountry,
342    getPrice,
343    getAllCountries,
344    getCountriesByTier,
345    getPricingSummary,
346    overridePrice,
347    getAcceptLanguage,
348    getGoogleDomain,
349    requiresGDPR,
350  };