#!/usr/bin/env node

/**
 * Update WakaTraffic Service Prices
 * Fetches services from Owlet API and updates local database prices
 * with a profit markup (15-25% higher than Owlet)
 */

require('dotenv').config();
const mysql = require('mysql2/promise');
const axios = require('axios');

// Price markup configuration
const MARKUP_CONFIG = {
  // Services under NGN 100 - 25% markup
  lowPrice: { threshold: 100, markup: 1.25 },

  // Services NGN 100-1000 - 20% markup
  mediumPrice: { threshold: 1000, markup: 1.20 },

  // Services NGN 1000-5000 - 18% markup
  highPrice: { threshold: 5000, markup: 1.18 },

  // Services above NGN 5000 - 15% markup
  veryHighPrice: { threshold: Infinity, markup: 1.15 }
};

/**
 * Calculate markup based on price tier
 */
function calculateMarkupPrice(owletPrice) {
  const price = parseFloat(owletPrice);

  if (price < MARKUP_CONFIG.lowPrice.threshold) {
    return Math.ceil(price * MARKUP_CONFIG.lowPrice.markup);
  } else if (price < MARKUP_CONFIG.mediumPrice.threshold) {
    return Math.ceil(price * MARKUP_CONFIG.mediumPrice.markup);
  } else if (price < MARKUP_CONFIG.highPrice.threshold) {
    return Math.ceil(price * MARKUP_CONFIG.highPrice.markup);
  } else {
    return Math.ceil(price * MARKUP_CONFIG.veryHighPrice.markup);
  }
}

/**
 * Get Owlet service list
 */
async function getOwletServices() {
  try {
    const response = await axios.post(
      process.env.OWLET_API_URL,
      new URLSearchParams({
        key: process.env.OWLET_API_KEY,
        action: 'services'
      }),
      {
        headers: {
          'Content-Type': 'application/x-www-form-urlencoded'
        },
        timeout: 30000
      }
    );

    if (response.data && Array.isArray(response.data)) {
      // Filter out free and view services as per OwletService.js
      return response.data.filter(service => {
        const name = (service.name || '').toLowerCase();
        const category = (service.category || '').toLowerCase();

        // Skip free services
        if (name.includes('free') || name.includes('99.99%')) {
          return false;
        }

        // Skip view-only services (keeping only engagement services)
        if (name.includes('views') && !name.includes('story')) {
          return false;
        }

        return true;
      });
    }

    return [];
  } catch (error) {
    console.error('❌ Failed to fetch Owlet services:', error.message);
    throw error;
  }
}

/**
 * Connect to database
 */
async function connectDatabase() {
  try {
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME
    });

    console.log('✅ Database connected');
    return connection;
  } catch (error) {
    console.error('❌ Database connection failed:', error.message);
    throw error;
  }
}

/**
 * Update service prices in database
 */
async function updateServicePrices(connection, owletServices) {
  let updated = 0;
  let skipped = 0;
  let errors = 0;

  console.log(`\n📊 Processing ${owletServices.length} services...\n`);

  for (const owletService of owletServices) {
    try {
      const owletPrice = parseFloat(owletService.rate);
      const owletServiceId = owletService.service;

      // Skip if price is 0 or invalid
      if (!owletPrice || owletPrice <= 0) {
        skipped++;
        continue;
      }

      // Calculate new price with markup
      const newPrice = calculateMarkupPrice(owletPrice);
      const markup = ((newPrice - owletPrice) / owletPrice * 100).toFixed(1);

      // Find service in database by api_service_id
      const [services] = await connection.execute(
        'SELECT id, name, price, api_service_id FROM services WHERE api_service_id = ?',
        [String(owletServiceId)]
      );

      if (services.length > 0) {
        const service = services[0];
        const oldPrice = parseFloat(service.price);

        // Update price
        await connection.execute(
          'UPDATE services SET price = ?, updated_at = NOW() WHERE id = ?',
          [newPrice, service.id]
        );

        console.log(`✅ ${service.name}`);
        console.log(`   Owlet: ₦${owletPrice.toFixed(2)} → WakaTraffic: ₦${newPrice.toFixed(2)} (+${markup}%)`);
        console.log(`   Old: ₦${oldPrice.toFixed(2)} | New: ₦${newPrice.toFixed(2)}\n`);

        updated++;
      } else {
        // Service not in database yet
        skipped++;
      }
    } catch (error) {
      console.error(`❌ Error updating service ${owletService.service}:`, error.message);
      errors++;
    }
  }

  return { updated, skipped, errors };
}

/**
 * Display pricing statistics
 */
async function displayPricingStats(connection) {
  console.log('\n📊 Pricing Statistics:\n');
  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');

  const [stats] = await connection.execute(`
    SELECT
      COUNT(*) as total_services,
      MIN(price) as min_price,
      MAX(price) as max_price,
      AVG(price) as avg_price,
      SUM(CASE WHEN price < 100 THEN 1 ELSE 0 END) as under_100,
      SUM(CASE WHEN price >= 100 AND price < 1000 THEN 1 ELSE 0 END) as between_100_1000,
      SUM(CASE WHEN price >= 1000 AND price < 5000 THEN 1 ELSE 0 END) as between_1000_5000,
      SUM(CASE WHEN price >= 5000 THEN 1 ELSE 0 END) as above_5000
    FROM services
    WHERE status = 'active'
  `);

  const stat = stats[0];

  console.log(`Total Active Services: ${stat.total_services}`);
  console.log(`Price Range: ₦${parseFloat(stat.min_price).toFixed(2)} - ₦${parseFloat(stat.max_price).toFixed(2)}`);
  console.log(`Average Price: ₦${parseFloat(stat.avg_price).toFixed(2)}`);
  console.log('');
  console.log('Price Distribution:');
  console.log(`  Under ₦100:           ${stat.under_100} services (25% markup)`);
  console.log(`  ₦100 - ₦1,000:        ${stat.between_100_1000} services (20% markup)`);
  console.log(`  ₦1,000 - ₦5,000:      ${stat.between_1000_5000} services (18% markup)`);
  console.log(`  Above ₦5,000:         ${stat.above_5000} services (15% markup)`);
  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
}

/**
 * Show example price comparisons
 */
async function showExampleComparisons(connection) {
  console.log('\n💰 Sample Price Comparisons (WakaTraffic vs Owlet):\n');
  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');

  // Example prices from different tiers
  const examples = [
    { owletPrice: 8, name: 'TikTok Views' },
    { owletPrice: 57, name: 'TikTok Likes' },
    { owletPrice: 315, name: 'Instagram Likes (30D)' },
    { owletPrice: 2789, name: 'Instagram Followers' },
    { owletPrice: 10939, name: 'YouTube Subscribers' }
  ];

  examples.forEach(example => {
    const wakaPrice = calculateMarkupPrice(example.owletPrice);
    const markup = ((wakaPrice - example.owletPrice) / example.owletPrice * 100).toFixed(1);
    const profit = wakaPrice - example.owletPrice;

    console.log(`${example.name}:`);
    console.log(`  Owlet:      ₦${example.owletPrice.toFixed(2)}`);
    console.log(`  WakaTraffic: ₦${wakaPrice.toFixed(2)} (+${markup}%)`);
    console.log(`  Profit/1000: ₦${profit.toFixed(2)}\n`);
  });

  console.log('━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━');
}

/**
 * Main execution
 */
async function main() {
  console.log('\n╔════════════════════════════════════════════════════════╗');
  console.log('║   WakaTraffic Price Update Tool                       ║');
  console.log('║   Syncing with Owlet API + Profit Markup              ║');
  console.log('╚════════════════════════════════════════════════════════╝\n');

  let connection;

  try {
    // Show example comparisons first
    await showExampleComparisons(null);

    // Connect to database
    connection = await connectDatabase();

    // Fetch Owlet services
    console.log('\n🔄 Fetching services from Owlet API...');
    const owletServices = await getOwletServices();
    console.log(`✅ Fetched ${owletServices.length} services from Owlet\n`);

    // Update prices
    console.log('💰 Updating service prices with markup...\n');
    const { updated, skipped, errors } = await updateServicePrices(connection, owletServices);

    // Display results
    console.log('\n╔════════════════════════════════════════════════════════╗');
    console.log('║   Update Summary                                       ║');
    console.log('╚════════════════════════════════════════════════════════╝\n');
    console.log(`✅ Updated: ${updated} services`);
    console.log(`⏭️  Skipped: ${skipped} services (not in database or invalid price)`);
    console.log(`❌ Errors:  ${errors} services\n`);

    // Display statistics
    await displayPricingStats(connection);

    console.log('\n✅ Price update completed successfully!\n');

  } catch (error) {
    console.error('\n❌ Fatal error:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 Database connection closed\n');
    }
  }
}

// Run the script
main();
