// server.js - Express Backend for WakaTraffic SMM Platform
// Using MySQL with XAMPP Server

const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const morgan = require('morgan');
const rateLimit = require('express-rate-limit');
const compression = require('compression');
const mysql = require('mysql2/promise');
const axios = require('axios');
require('dotenv').config();

const app = express();

// Import Email Service
const emailService = require('./services/EmailService');

// =====================
// MIDDLEWARE SETUP
// =====================

// Security middleware
app.use(helmet({
  contentSecurityPolicy: false, // Disable CSP for now, configure properly later
  crossOriginEmbedderPolicy: false
}));

// CORS configuration
const allowedOrigins = process.env.NODE_ENV === 'production'
  ? (process.env.ALLOWED_ORIGINS || '').split(',').filter(Boolean)
  : [
      process.env.FRONTEND_URL || 'http://localhost:3000',
      'http://localhost:5000',
      'http://localhost',
      'http://localhost:80',
      'http://127.0.0.1',
      'http://127.0.0.1:5000',
      'null' // Allow file:// protocol (direct HTML file opening)
    ];

app.use(cors({
  origin: function(origin, callback) {
    // Allow requests with no origin (like mobile apps, curl, or Postman)
    if (!origin) return callback(null, true);

    // Allow null origin (file:// protocol)
    if (origin === 'null') return callback(null, true);

    // Check if origin is in allowed list
    if (allowedOrigins.indexOf(origin) !== -1) {
      return callback(null, true);
    }

    // Reject other origins
    callback(new Error('Not allowed by CORS'));
  },
  credentials: true,
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH'],
  allowedHeaders: ['Content-Type', 'Authorization']
}));

// Body parsing
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true, limit: '10mb' }));

// Compression
app.use(compression());

// Logging
app.use(morgan('combined'));

// Rate limiting
const limiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 100 // limit each IP to 100 requests per windowMs
});
app.use('/api/', limiter);

// =====================
// SERVE DASHBOARD
// =====================
const path = require('path');

app.get('/dashboard', (req, res) => {
  res.sendFile(path.join(__dirname, 'dashboard.html'));
});

app.get('/dashboard.html', (req, res) => {
  res.sendFile(path.join(__dirname, 'dashboard.html'));
});

// User-facing site
app.get('/app', (req, res) => {
  res.sendFile(path.join(__dirname, 'wakatraffic-complete.html'));
});

// User Dashboard
app.get('/user-dashboard', (req, res) => {
  res.sendFile(path.join(__dirname, 'user-dashboard.html'));
});

// Also serve at direct filename path for convenience
app.get('/wakatraffic-complete.html', (req, res) => {
  res.sendFile(path.join(__dirname, 'wakatraffic-complete.html'));
});

// =====================
// STATIC FILES
// =====================
app.use(express.static(path.join(__dirname, 'public')));
app.use('/js', express.static(path.join(__dirname, 'js')));
app.use('/uploads', express.static(path.join(__dirname, 'uploads')));
app.use('/logo', express.static(path.join(__dirname, 'logo')));

// =====================
// SERVE HOME PAGE
// =====================
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

app.get('/home', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

// =====================
// SERVE SERVICES PAGE
// =====================
app.get('/services', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'services-page.html'));
});

// =====================
// DATABASE CONNECTION (MySQL)
// =====================

// Resolve the correct DB host for WSL2 environments.
// In WSL2, the Node.js process can't reach Windows services via 'localhost'.
// The Windows host is accessible via the nameserver IP in /etc/resolv.conf.
function getDbHost() {
  const configuredHost = process.env.DB_HOST || 'localhost';
  if (configuredHost !== 'localhost' && configuredHost !== '127.0.0.1') {
    return configuredHost;
  }
  try {
    const fs = require('fs');
    const resolv = fs.readFileSync('/etc/resolv.conf', 'utf8');
    if (resolv.includes('WSL')) {
      const match = resolv.match(/nameserver\s+([\d.]+)/);
      if (match && match[1]) {
        console.log(`🔧 WSL2 detected — using Windows host IP for MySQL: ${match[1]}`);
        return match[1];
      }
    }
  } catch (e) { /* not WSL or unreadable */ }
  return configuredHost;
}

const DB_HOST = getDbHost();

// Create MySQL connection pool
const pool = mysql.createPool({
  host: DB_HOST,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'wakatraffic',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// Make pool available to routes
app.set('pool', pool);

// Initialize Payment Service Factory with database pool
const { getPaymentServiceFactory } = require('./services/payments');
getPaymentServiceFactory(pool);

// Test database connection, then ensure admin user exists
pool.getConnection()
  .then(async connection => {
    console.log('✅ MySQL database connected successfully');
    connection.release();

    // Auto-create admin user from .env if it doesn't exist
    const adminEmail = process.env.ADMIN_EMAIL;
    const adminPassword = process.env.ADMIN_PASSWORD;
    if (adminEmail && adminPassword) {
      try {
        const [rows] = await pool.execute('SELECT id FROM users WHERE email = ?', [adminEmail]);
        if (rows.length === 0) {
          const bcrypt = require('bcrypt');
          const hash = await bcrypt.hash(adminPassword, 12);
          const firstName = process.env.ADMIN_FIRSTNAME || 'Admin';
          const lastName = process.env.ADMIN_LASTNAME || 'User';
          const username = adminEmail.split('@')[0];
          await pool.execute(
            `INSERT INTO users (username, email, password_hash, first_name, last_name, balance, role, status, email_verified, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, 0, 'admin', 'active', 1, NOW(), NOW())`,
            [username, adminEmail, hash, firstName, lastName]
          );
          console.log(`✅ Admin user created: ${adminEmail}`);
        } else {
          console.log(`✅ Admin user exists: ${adminEmail}`);
        }
      } catch (e) {
        console.error('⚠️  Admin user setup error:', e.message);
      }
    }
  })
  .catch(error => {
    console.error('❌ MySQL database connection failed:', error.message);
    console.error('   Host:', DB_HOST, '| DB:', process.env.DB_NAME || 'wakatraffic');
    console.error('   → Make sure XAMPP MySQL is running');
  });

// Database helper function
async function query(sql, params) {
  const [results] = await pool.execute(sql, params);
  return results;
}

// Ensure email_otp_tokens table exists (idempotent — safe to call anytime)
async function ensureOtpTable() {
  await pool.execute(`
    CREATE TABLE IF NOT EXISTS email_otp_tokens (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      email VARCHAR(255) NOT NULL,
      otp_code VARCHAR(10) NOT NULL,
      expires_at DATETIME NOT NULL,
      used TINYINT(1) DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_user_id (user_id),
      INDEX idx_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);
}

// OTP helper — generates a 6-digit code, stores it (30-min expiry), returns it
async function generateAndStoreOTP(userId, email) {
  await ensureOtpTable(); // Guarantee table exists before every write

  const otp = String(Math.floor(100000 + Math.random() * 900000)); // 6 digits

  // Invalidate any previous unused OTPs for this user
  await query('UPDATE email_otp_tokens SET used = 1 WHERE user_id = ? AND used = 0', [userId]);

  // Use MySQL's DATE_ADD to ensure consistent timezone handling
  await query(
    `INSERT INTO email_otp_tokens (user_id, email, otp_code, expires_at, created_at)
     VALUES (?, ?, ?, DATE_ADD(NOW(), INTERVAL 30 MINUTE), NOW())`,
    [userId, email, otp]
  );

  console.log(`🔐 OTP Generated for ${email}: ${otp} (expires in 30 minutes)`);
  return otp;
}

// Generate a unique username from email, appending a number if the base is taken
async function generateUniqueUsername(email) {
  const base = email.split('@')[0].replace(/[^a-zA-Z0-9_]/g, '').toLowerCase() || 'user';
  const [rows] = await pool.execute('SELECT username FROM users WHERE username = ?', [base]);
  if (rows.length === 0) return base;
  // Base is taken — append random 4-digit suffix until unique
  let candidate;
  do {
    candidate = `${base}${Math.floor(1000 + Math.random() * 9000)}`;
    const [taken] = await pool.execute('SELECT username FROM users WHERE username = ?', [candidate]);
    if (taken.length === 0) return candidate;
  } while (true);
}

// Make pool available to routes
app.locals.pool = pool;

// =====================
// AUTHENTICATION MIDDLEWARE
// =====================

const jwt = require('jsonwebtoken');

const authenticateToken = (req, res, next) => {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: 'Access token required' });
  }

  jwt.verify(token, process.env.JWT_SECRET, (err, user) => {
    if (err) {
      return res.status(403).json({ error: 'Invalid or expired token' });
    }
    req.user = user;
    next();
  });
};

const authenticateAdmin = (req, res, next) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: 'Admin access required' });
  }
  next();
};

// =====================
// OWLET API SERVICE (PHASE 1 - WITH SMART FILTERING)
// =====================

const owletService = require('./services/OwletService');

// =====================
// IMPORT ADMIN ROUTES (PHASE 1-3)
// =====================

const adminSyncRoutes = require('./routes/adminSyncRoutes');
const adminUserRoutes = require('./routes/adminUserRoutes');
const adminImpersonationRoutes = require('./routes/adminImpersonationRoutes');
const { requireAdmin } = require('./middleware/authMiddleware');
const BreadcrumbService = require('./services/BreadcrumbService');

// Phase 4: Admin Dashboard
const DashboardService = require('./services/DashboardService');
const adminDashboardRoutes = require('./routes/adminDashboardRoutes');
const adminServiceRoutes = require('./routes/adminServiceRoutes');
const adminCategoryRoutes = require('./routes/adminCategoryRoutes');
const adminPaymentGatewayRoutes = require('./routes/adminPaymentGatewayRoutes');
const adminSettingsRoutes = require('./routes/adminSettingsRoutes');
const adminOrderRoutes = require('./routes/adminOrderRoutes');

// Payment System Routes
const depositRoutes = require('./routes/depositRoutes');
const webhookRoutes = require('./routes/webhookRoutes');
const adminPaymentRoutes = require('./routes/adminPaymentRoutes');

// =====================
// AUTHENTICATION ROUTES
// =====================

const bcrypt = require('bcrypt');

// Register
app.post('/api/auth/register', async (req, res) => {
  try {
    const { email, password, firstName, lastName, mobile } = req.body;

    // Check if user exists
    const existingUsers = await query(
      'SELECT id FROM users WHERE email = ?',
      [email]
    );

    if (existingUsers.length > 0) {
      return res.status(400).json({ error: 'Email already registered' });
    }

    // Validate required fields
    if (!email || !password || !firstName || !lastName) {
      return res.status(400).json({ error: 'First name, last name, email and password are required' });
    }

    // Hash password
    const passwordHash = await bcrypt.hash(password, 10);

    // Generate a unique username (avoids UNIQUE constraint errors)
    const username = await generateUniqueUsername(email);

    // Insert user (email_verified = 0 until OTP confirmed)
    const result = await query(
      `INSERT INTO users (username, email, password_hash, first_name, last_name, mobile_number, balance, role, status, email_verified)
       VALUES (?, ?, ?, ?, ?, ?, 0.00, 'user', 'active', 0)`,
      [username, email, passwordHash, firstName, lastName, mobile || null]
    );

    const userId = result.insertId;

    // Generate OTP and send via email
    const otp = await generateAndStoreOTP(userId, email);

    // Send OTP via email
    const emailResult = await emailService.sendOTP(email, otp, firstName);

    if (!emailResult.success) {
      console.error('Failed to send OTP email:', emailResult.error);
      // Don't block registration if email fails, but warn user
    }

    // In development, include OTP in response for easy testing
    const isDev = process.env.NODE_ENV !== 'production';

    res.status(201).json({
      message: 'Registration successful. Please check your email for the verification code.',
      needsEmailVerification: true,
      email: email,
      userId: userId,
      emailSent: emailResult.success,
      // Include OTP in development mode for on-screen display
      ...(isDev && { otp: otp, otpForDevelopment: true })
    });
  } catch (error) {
    console.error('Registration error:', error);
    const isDev = process.env.NODE_ENV !== 'production';
    res.status(500).json({ error: isDev ? `Registration failed: ${error.message}` : 'Registration failed' });
  }
});

// Login
app.post('/api/auth/login', async (req, res) => {
  try {
    const { email, password, isAdmin } = req.body;

    // Find user
    const users = await query(
      'SELECT * FROM users WHERE email = ?',
      [email]
    );

    if (users.length === 0) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    const user = users[0];

    // Verify password
    const validPassword = await bcrypt.compare(password, user.password_hash);
    if (!validPassword) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    // Check admin access if requested
    if (isAdmin && user.role !== 'admin') {
      return res.status(403).json({ error: 'Admin access denied' });
    }

    // If email not verified, require OTP before granting access (admins are pre-verified)
    if (!user.email_verified && user.role !== 'admin') {
      const otp = await generateAndStoreOTP(user.id, user.email);

      // Send OTP via email
      const emailResult = await emailService.sendOTP(user.email, otp, user.first_name);

      if (!emailResult.success) {
        console.error('Failed to send OTP email:', emailResult.error);
      }

      // In development, include OTP in response for easy testing
      const isDev = process.env.NODE_ENV !== 'production';

      return res.status(200).json({
        message: 'Email not verified. Please check your email for the verification code.',
        needsEmailVerification: true,
        email: user.email,
        userId: user.id,
        emailSent: emailResult.success,
        // Include OTP in development mode for on-screen display
        ...(isDev && { otp: otp, otpForDevelopment: true })
      });
    }

    // Update last login
    await query(
      'UPDATE users SET last_login_at = NOW(), last_login_ip = ? WHERE id = ?',
      [req.ip, user.id]
    );

    // Log login activity
    await query(
      `INSERT INTO login_activities (user_id, ip_address, user_agent, login_at)
       VALUES (?, ?, ?, NOW())`,
      [user.id, req.ip, req.headers['user-agent'] || '']
    );

    // Generate JWT token
    const token = jwt.sign(
      { userId: user.id, email: user.email, role: user.role || 'user' },
      process.env.JWT_SECRET,
      { expiresIn: process.env.JWT_EXPIRES_IN || '7d' }
    );

    res.json({
      message: 'Login successful',
      token,
      user: {
        id: user.id,
        email: user.email,
        firstName: user.first_name,
        lastName: user.last_name,
        balance: parseFloat(user.balance),
        role: user.role || 'user'
      }
    });
  } catch (error) {
    console.error('Login error:', error);
    res.status(500).json({ error: 'Login failed' });
  }
});

// Verify OTP
app.post('/api/auth/verify-otp', async (req, res) => {
  try {
    const { email, otp } = req.body;

    if (!email || !otp) {
      return res.status(400).json({ error: 'Email and OTP are required' });
    }

    // Ensure OTP is a string for comparison
    const otpString = String(otp).trim();

    console.log(`🔍 Verifying OTP for ${email}: ${otpString}`);

    // Find a valid, unused OTP for this email
    const tokens = await query(
      `SELECT t.*, u.id as uid, u.first_name, u.last_name, u.balance, u.role,
              t.expires_at, NOW() as current_time
       FROM email_otp_tokens t
       JOIN users u ON u.id = t.user_id
       WHERE t.email = ? AND t.otp_code = ? AND t.used = 0 AND t.expires_at > NOW()
       ORDER BY t.created_at DESC LIMIT 1`,
      [email, otpString]
    );

    if (tokens.length === 0) {
      // Debug: Check if OTP exists but is expired or used
      const allTokens = await query(
        `SELECT otp_code, used, expires_at, NOW() as current_time
         FROM email_otp_tokens
         WHERE email = ?
         ORDER BY created_at DESC LIMIT 3`,
        [email]
      );

      console.log(`❌ OTP verification failed for ${email}. Recent tokens:`, allTokens);

      return res.status(400).json({ error: 'Invalid or expired OTP. Please request a new one.' });
    }

    console.log(`✅ OTP verified successfully for ${email}`);

    const record = tokens[0];

    // Mark OTP as used
    await query('UPDATE email_otp_tokens SET used = 1 WHERE id = ?', [record.id]);

    // Mark user's email as verified
    await query(
      'UPDATE users SET email_verified = 1, email_verified_at = NOW(), updated_at = NOW() WHERE id = ?',
      [record.user_id]
    );

    // Send welcome email (non-blocking)
    emailService.sendWelcomeEmail(email, record.first_name).catch(err => {
      console.error('Failed to send welcome email:', err.message);
    });

    // Log login activity
    await query(
      `INSERT INTO login_activities (user_id, ip_address, user_agent, login_at)
       VALUES (?, ?, ?, NOW())`,
      [record.user_id, req.ip, req.headers['user-agent'] || '']
    );

    // Generate JWT token
    const token = jwt.sign(
      { userId: record.user_id, email: email, role: record.role || 'user' },
      process.env.JWT_SECRET,
      { expiresIn: process.env.JWT_EXPIRES_IN || '7d' }
    );

    res.json({
      message: 'Email verified successfully',
      token,
      user: {
        id: record.user_id,
        email: email,
        firstName: record.first_name,
        lastName: record.last_name,
        balance: parseFloat(record.balance),
        role: record.role || 'user'
      }
    });
  } catch (error) {
    console.error('OTP verify error:', error);
    res.status(500).json({ error: 'Verification failed' });
  }
});

// Resend OTP
app.post('/api/auth/resend-otp', async (req, res) => {
  try {
    const { email } = req.body;

    if (!email) {
      return res.status(400).json({ error: 'Email is required' });
    }

    const users = await query('SELECT id, email, email_verified FROM users WHERE email = ?', [email]);

    if (users.length === 0) {
      return res.status(404).json({ error: 'Email not found' });
    }

    const user = users[0];

    if (user.email_verified) {
      return res.status(400).json({ error: 'Email is already verified' });
    }

    const otp = await generateAndStoreOTP(user.id, email);

    // Send OTP via email
    const emailResult = await emailService.sendOTP(email, otp, user.first_name || 'User');

    if (!emailResult.success) {
      console.error('Failed to send OTP email:', emailResult.error);
      return res.status(500).json({
        error: 'Failed to send OTP email. Please try again later.',
        emailSent: false
      });
    }

    // In development, include OTP in response for easy testing
    const isDev = process.env.NODE_ENV !== 'production';

    res.json({
      message: 'A new verification code has been sent to your email.',
      emailSent: true,
      // Include OTP in development mode for on-screen display
      ...(isDev && { otp: otp, otpForDevelopment: true })
    });
  } catch (error) {
    console.error('Resend OTP error:', error);
    res.status(500).json({ error: 'Failed to resend OTP' });
  }
});

// Forgot Password
app.post('/api/auth/forgot-password', async (req, res) => {
  try {
    const { email } = req.body;

    const users = await query('SELECT id, email FROM users WHERE email = ?', [email]);

    if (users.length === 0) {
      return res.status(404).json({ error: 'Email not found' });
    }

    // TODO: Generate reset token and send email
    // For now, return success
    res.json({ message: 'Password reset link sent to your email' });
  } catch (error) {
    console.error('Forgot password error:', error);
    res.status(500).json({ error: 'Failed to process request' });
  }
});

// =====================
// USER ROUTES
// =====================

// Get user dashboard
app.get('/api/user/dashboard', authenticateToken, async (req, res) => {
  try {
    const userId = req.user.userId;

    // Get user data
    const users = await query('SELECT * FROM users WHERE id = ?', [userId]);
    const user = users[0];

    // Get statistics
    const ordersResult = await query(
      'SELECT COUNT(*) as count FROM orders WHERE user_id = ?',
      [userId]
    );
    const transactionsResult = await query(
      'SELECT COUNT(*) as count FROM transactions WHERE user_id = ?',
      [userId]
    );
    const depositsResult = await query(
      'SELECT COUNT(*) as count FROM deposits WHERE user_id = ?',
      [userId]
    );
    res.json({
      user: {
        id: user.id,
        email: user.email,
        name: `${user.first_name} ${user.last_name}`,
        balance: parseFloat(user.balance)
      },
      stats: {
        orders: ordersResult[0].count,
        transactions: transactionsResult[0].count,
        deposits: depositsResult[0].count
      }
    });
  } catch (error) {
    console.error('Dashboard error:', error);
    res.status(500).json({ error: 'Failed to load dashboard' });
  }
});

// Get user profile
app.get('/api/user/profile', authenticateToken, async (req, res) => {
  try {
    const users = await query(
      `SELECT id, email, first_name, last_name, mobile_number, address, city, state, country,
       balance, profile_photo, email_verified, mobile_verified FROM users WHERE id = ?`,
      [req.user.userId]
    );

    if (users.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    res.json({ user: users[0] });
  } catch (error) {
    console.error('Profile error:', error);
    res.status(500).json({ error: 'Failed to load profile' });
  }
});

// Update user profile
app.put('/api/user/profile', authenticateToken, async (req, res) => {
  try {
    const { firstName, lastName, mobile, address, city, state, country } = req.body;

    await query(
      `UPDATE users SET first_name = ?, last_name = ?, mobile_number = ?, 
       address = ?, city = ?, state = ?, country = ?, updated_at = NOW() WHERE id = ?`,
      [firstName, lastName, mobile, address, city, state, country, req.user.userId]
    );

    res.json({ message: 'Profile updated successfully' });
  } catch (error) {
    console.error('Profile update error:', error);
    res.status(500).json({ error: 'Failed to update profile' });
  }
});

// Change password
app.put('/api/user/password', authenticateToken, async (req, res) => {
  try {
    const { currentPassword, newPassword } = req.body;

    // Get current password hash
    const users = await query(
      'SELECT password_hash FROM users WHERE id = ?',
      [req.user.userId]
    );

    if (users.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    // Verify current password
    const validPassword = await bcrypt.compare(currentPassword, users[0].password_hash);
    if (!validPassword) {
      return res.status(401).json({ error: 'Current password is incorrect' });
    }

    // Hash new password
    const newPasswordHash = await bcrypt.hash(newPassword, 10);

    // Update password
    await query(
      'UPDATE users SET password_hash = ?, updated_at = NOW() WHERE id = ?',
      [newPasswordHash, req.user.userId]
    );

    res.json({ message: 'Password changed successfully' });
  } catch (error) {
    console.error('Password change error:', error);
    res.status(500).json({ error: 'Failed to change password' });
  }
});

// =====================
// SERVICES ROUTES
// =====================

// Get categories
app.get('/api/services/categories', async (req, res) => {
  try {
    const categories = await query(
      `SELECT c.*, COUNT(s.id) as service_count
       FROM categories c
       LEFT JOIN services s ON c.id = s.category_id AND s.status = 'active'
       WHERE c.status = 'active'
       GROUP BY c.id
       ORDER BY c.name ASC`
    );

    res.json({ categories });
  } catch (error) {
    console.error('Categories error:', error);
    res.status(500).json({ error: 'Failed to load categories' });
  }
});

// Owlet full catalog (must be before /:categoryId to avoid route conflict)
// Service catalog endpoint (branded as generic catalog for users)
// Returns ALL services without filtering (3,036+ services)
app.get('/api/services/catalog', async (req, res) => {
  try {
    // Get ALL services without filtering
    const services = await owletService.getServices();

    if (!services || services.length === 0) {
      return res.json({
        success: false,
        message: 'No services available from provider',
        total: 0,
        categories: [],
        services: {}
      });
    }

    const MARKUP_PERCENTAGE = 0.35; // 35% markup
    const grouped = {};

    for (const svc of services) {
      const cat = (svc.category || 'Other').replace(/\bowlet\b/gi, 'WakaTraffic').trim();
      if (!grouped[cat]) grouped[cat] = [];

      // Calculate price with 35% markup
      const originalRate = parseFloat(svc.rate || 0);
      const markedUpRate = originalRate * (1 + MARKUP_PERCENTAGE);

      grouped[cat].push({
        id: svc.service,
        name: (svc.name || '').replace(/\bowlet\b/gi, 'WakaTraffic').trim(),
        category: cat,
        rate: parseFloat(markedUpRate.toFixed(2)), // Apply 35% markup
        min: parseInt(svc.min || 1),
        max: parseInt(svc.max || 1000000),
        type: (svc.type || 'Default').replace(/\bowlet\b/gi, 'WakaTraffic').trim()
      });
    }

    console.log(`✅ Serving ${services.length} services across ${Object.keys(grouped).length} categories`);

    res.json({
      success: true,
      total: services.length,
      categories: Object.keys(grouped).sort(),
      services: grouped
    });
  } catch (error) {
    console.error('Catalog error:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// Get services by category
app.get('/api/services/:categoryId', async (req, res) => {
  try {
    const { categoryId } = req.params;

    const services = await query(
      `SELECT s.*, c.name as category_name, c.icon_class as category_icon 
       FROM services s 
       INNER JOIN categories c ON s.category_id = c.id 
       WHERE s.category_id = ? AND s.status = ?`,
      [categoryId, 'active']
    );

    res.json({ services });
  } catch (error) {
    console.error('Services error:', error);
    res.status(500).json({ error: 'Failed to load services' });
  }
});

// Get all services
app.get('/api/services', async (req, res) => {
  try {
    const services = await query(
      `SELECT s.*, c.name as category_name, c.icon_class as category_icon 
       FROM services s 
       INNER JOIN categories c ON s.category_id = c.id 
       WHERE s.status = ? 
       ORDER BY c.display_order, s.id`,
      ['active']
    );

    res.json({ services });
  } catch (error) {
    console.error('Services error:', error);
    res.status(500).json({ error: 'Failed to load services' });
  }
});

// =====================
// ORDER ROUTES
// =====================

// Create order (using stored procedure)
app.post('/api/orders/create', authenticateToken, async (req, res) => {
  try {
    const { serviceId, link, quantity } = req.body;
    const userId = req.user.userId;

    // Input validation
    if (!serviceId || !link || !quantity) {
      return res.status(400).json({ error: 'Missing required fields' });
    }

    if (quantity < 1) {
      return res.status(400).json({ error: 'Quantity must be at least 1' });
    }

    // Validate URL format
    try {
      new URL(link);
    } catch (error) {
      return res.status(400).json({ error: 'Invalid link URL' });
    }

    // Get service details with API provider info
    const services = await query(
      `SELECT s.*, ap.api_key, ap.name as provider_name, ap.status as provider_status
       FROM services s
       INNER JOIN api_providers ap ON s.api_provider_id = ap.id
       WHERE s.id = ? AND s.status = 'active' AND ap.status = 'active'`,
      [serviceId]
    );

    if (services.length === 0) {
      return res.status(404).json({ error: 'Service not found or unavailable' });
    }

    const service = services[0];

    // Validate quantity range
    if (quantity < service.min_quantity || quantity > service.max_quantity) {
      return res.status(400).json({
        error: `Quantity must be between ${service.min_quantity} and ${service.max_quantity}`
      });
    }

    // Calculate charge (price is per 1000)
    const charge = (quantity / 1000) * service.price;

    // Get user balance
    const users = await query(
      'SELECT balance FROM users WHERE id = ?',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    const user = users[0];

    // Check if user has sufficient balance
    if (user.balance < charge) {
      return res.status(400).json({
        error: 'Insufficient balance',
        required: charge,
        available: user.balance
      });
    }

    // Place order with Owlet API
    const owletResponse = await owletService.placeOrder(
      service.api_service_id,
      link,
      quantity
    );

    if (!owletResponse.success) {
      return res.status(400).json({
        error: 'Failed to place order with provider',
        details: owletResponse.error
      });
    }

    // Deduct balance from user first
    await query(
      'UPDATE users SET balance = balance - ? WHERE id = ?',
      [charge, userId]
    );

    // Get post-balance for transaction record
    const updatedUser = await query('SELECT balance FROM users WHERE id = ?', [userId]);
    const postBalance = parseFloat(updatedUser[0]?.balance ?? 0);

    // Create order record in database (using correct DB column names)
    const orderResult = await query(
      `INSERT INTO orders (user_id, service_id, api_provider_id, link_url, quantity, price, status, api_order_id, start_counter, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, 'processing', ?, ?, NOW(), NOW())`,
      [userId, serviceId, service.api_provider_id, link, quantity, charge, owletResponse.orderId, owletResponse.startCount || null]
    );

    const orderId = orderResult.insertId;

    // Record transaction (using correct DB column names)
    await query(
      `INSERT INTO transactions (user_id, type, amount, charge, post_balance, description, reference_id, created_at)
       VALUES (?, 'order', ?, 0, ?, ?, ?, NOW())`,
      [userId, charge, postBalance, `Order #${orderId} - ${service.name}`, String(orderId)]
    );

    res.status(201).json({
      message: 'Order created successfully',
      orderId: orderId,
      providerOrderId: owletResponse.orderId,
      charge: charge,
      quantity: quantity,
      status: 'processing'
    });
  } catch (error) {
    console.error('Order creation error:', error);
    res.status(500).json({ error: 'Failed to create order', details: error.message });
  }
});

// Create order directly from catalog (for services page)
app.post('/api/orders/direct', authenticateToken, async (req, res) => {
  try {
    const { catalogServiceId, serviceName, serviceCategory, serviceRate, link, quantity } = req.body;
    const userId = req.user.userId;

    // Input validation
    if (!catalogServiceId || !link || !quantity) {
      return res.status(400).json({ error: 'Missing required fields' });
    }

    if (quantity < 1) {
      return res.status(400).json({ error: 'Quantity must be at least 1' });
    }

    // Validate URL format
    try {
      new URL(link);
    } catch (error) {
      return res.status(400).json({ error: 'Invalid link URL' });
    }

    // Calculate charge (rate is per 1000)
    const charge = (quantity / 1000) * serviceRate;

    // Get user balance
    const users = await query(
      'SELECT balance FROM users WHERE id = ?',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    const user = users[0];

    // Check if user has sufficient balance
    if (user.balance < charge) {
      return res.status(400).json({
        error: 'Insufficient balance',
        required: charge,
        available: user.balance
      });
    }

    // Place order with Owlet API using catalog service ID
    const owletResponse = await owletService.placeOrder(
      catalogServiceId,
      link,
      quantity
    );

    if (!owletResponse.success) {
      return res.status(400).json({
        error: 'Failed to place order with provider',
        details: owletResponse.error
      });
    }

    // Get the default API provider (should be Owlet)
    const providers = await query(
      `SELECT id FROM api_providers WHERE status = 'active' LIMIT 1`
    );

    const apiProviderId = providers.length > 0 ? providers[0].id : 1;

    // Check if service exists in local database, if not create it
    let serviceId;
    const existingServices = await query(
      'SELECT id FROM services WHERE api_service_id = ? AND api_provider_id = ?',
      [String(catalogServiceId), apiProviderId]
    );

    if (existingServices.length > 0) {
      serviceId = existingServices[0].id;
    } else {
      // Create service record in local database
      const serviceResult = await query(
        `INSERT INTO services (name, category_id, api_service_id, api_provider_id, price, min_quantity, max_quantity, status, created_at, updated_at)
         VALUES (?, 1, ?, ?, ?, 50, 100000, 'active', NOW(), NOW())`,
        [serviceName || 'Catalog Service', String(catalogServiceId), apiProviderId, serviceRate]
      );
      serviceId = serviceResult.insertId;
    }

    // Deduct balance from user
    await query(
      'UPDATE users SET balance = balance - ? WHERE id = ?',
      [charge, userId]
    );

    // Get post-balance
    const updatedUser = await query('SELECT balance FROM users WHERE id = ?', [userId]);
    const postBalance = parseFloat(updatedUser[0]?.balance ?? 0);

    // Create order record with service_id
    const orderResult = await query(
      `INSERT INTO orders (user_id, service_id, api_provider_id, link_url, quantity, price, status, api_order_id, start_counter, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, 'processing', ?, ?, NOW(), NOW())`,
      [userId, serviceId, apiProviderId, link, quantity, charge, owletResponse.orderId, owletResponse.startCount || null]
    );

    const orderId = orderResult.insertId;

    // Record transaction
    await query(
      `INSERT INTO transactions (user_id, type, amount, charge, post_balance, description, reference_id, created_at)
       VALUES (?, 'order', ?, 0, ?, ?, ?, NOW())`,
      [userId, charge, postBalance, `Order #${orderId} - ${serviceName}`, String(orderId)]
    );

    res.status(201).json({
      success: true,
      message: 'Order created successfully',
      orderId: orderId,
      providerOrderId: owletResponse.orderId,
      charge: charge,
      quantity: quantity,
      status: 'processing'
    });
  } catch (error) {
    console.error('Direct order creation error:', error);
    res.status(500).json({ error: 'Failed to create order', details: error.message });
  }
});

// Get user orders
app.get('/api/user/orders', authenticateToken, async (req, res) => {
  try {
    const { page = 1, limit = 20, status } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE o.user_id = ?';
    let params = [req.user.userId];

    if (status) {
      whereClause += ' AND o.status = ?';
      params.push(status);
    }

    const orders = await query(
      `SELECT o.*, s.name as service_name, c.name as category_name, c.icon_class as category_icon 
       FROM orders o 
       INNER JOIN services s ON o.service_id = s.id 
       INNER JOIN categories c ON s.category_id = c.id 
       ${whereClause} 
       ORDER BY o.created_at DESC 
       LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );

    // Get total count
    const countResult = await query(
      `SELECT COUNT(*) as total FROM orders o ${whereClause}`,
      params
    );

    res.json({
      orders,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: countResult[0].total,
        pages: Math.ceil(countResult[0].total / limit)
      }
    });
  } catch (error) {
    console.error('Orders error:', error);
    res.status(500).json({ error: 'Failed to load orders' });
  }
});

// Get order details
app.get('/api/orders/:orderId', authenticateToken, async (req, res) => {
  try {
    const { orderId } = req.params;

    const orders = await query(
      `SELECT o.*, s.name as service_name, s.description as service_description,
       c.name as category_name, c.icon_class as category_icon,
       u.email as user_email, u.first_name, u.last_name
       FROM orders o 
       INNER JOIN services s ON o.service_id = s.id 
       INNER JOIN categories c ON s.category_id = c.id 
       INNER JOIN users u ON o.user_id = u.id
       WHERE o.id = ? AND (o.user_id = ? OR ? = 'admin')`,
      [orderId, req.user.userId, req.user.role]
    );

    if (orders.length === 0) {
      return res.status(404).json({ error: 'Order not found' });
    }

    res.json({ order: orders[0] });
  } catch (error) {
    console.error('Order details error:', error);
    res.status(500).json({ error: 'Failed to load order details' });
  }
});

// Cancel order (user)
app.post('/api/orders/:orderId/cancel', authenticateToken, async (req, res) => {
  try {
    const { orderId } = req.params;
    const userId = req.user.userId;

    // Get order details
    const orders = await query(
      `SELECT o.*, s.price, ap.api_key
       FROM orders o
       INNER JOIN services s ON o.service_id = s.id
       INNER JOIN api_providers ap ON s.api_provider_id = ap.id
       WHERE o.id = ? AND o.user_id = ?`,
      [orderId, userId]
    );

    if (orders.length === 0) {
      return res.status(404).json({ error: 'Order not found' });
    }

    const order = orders[0];

    // Check if order can be cancelled (only if pending or processing)
    if (!['pending', 'processing', 'in_progress'].includes(order.status)) {
      return res.status(400).json({
        error: `Cannot cancel order with status: ${order.status}`
      });
    }

    // Try to refund with API provider
    let refundSuccess = false;
    if (order.provider_order_id) {
      const refundResult = await owletService.refundOrder(order.provider_order_id);
      refundSuccess = refundResult.success;
    }

    // Update order status
    await query(
      `UPDATE orders SET status = 'cancelled', updated_at = NOW() WHERE id = ?`,
      [orderId]
    );

    // Refund balance to user if order was charged
    if (order.charge > 0) {
      await query(
        'UPDATE users SET balance = balance + ? WHERE id = ?',
        [order.charge, userId]
      );

      // Record refund transaction
      await query(
        `INSERT INTO transactions (user_id, order_id, type, amount, status, description, created_at)
         VALUES (?, ?, 'credit', ?, 'completed', ?, NOW())`,
        [userId, orderId, order.charge, `Refund for cancelled order #${orderId}`]
      );
    }

    res.json({
      message: 'Order cancelled successfully',
      orderId: orderId,
      refundAmount: order.charge,
      refundStatus: refundSuccess ? 'completed' : 'pending'
    });
  } catch (error) {
    console.error('Order cancellation error:', error);
    res.status(500).json({ error: 'Failed to cancel order', details: error.message });
  }
});

// =====================
// DEPOSIT ROUTES
// =====================
// Paystack integration removed - New payment methods to be implemented

// Get user deposits
app.get('/api/user/deposits', authenticateToken, async (req, res) => {
  try {
    const deposits = await query(
      `SELECT * FROM deposits WHERE user_id = ? ORDER BY created_at DESC LIMIT 50`,
      [req.user.userId]
    );

    res.json({ deposits });
  } catch (error) {
    console.error('Deposits error:', error);
    res.status(500).json({ error: 'Failed to load deposits' });
  }
});

// Get user transactions
app.get('/api/user/transactions', authenticateToken, async (req, res) => {
  try {
    const { page = 1, limit = 20 } = req.query;
    const offset = (page - 1) * limit;

    const transactions = await query(
      `SELECT * FROM transactions WHERE user_id = ? 
       ORDER BY created_at DESC LIMIT ? OFFSET ?`,
      [req.user.userId, parseInt(limit), offset]
    );

    const countResult = await query(
      'SELECT COUNT(*) as total FROM transactions WHERE user_id = ?',
      [req.user.userId]
    );

    res.json({
      transactions,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: countResult[0].total,
        pages: Math.ceil(countResult[0].total / limit)
      }
    });
  } catch (error) {
    console.error('Transactions error:', error);
    res.status(500).json({ error: 'Failed to load transactions' });
  }
});

// =====================
// ADMIN ROUTES (DEPRECATED - USE PHASE 4 ROUTES BELOW)
// =====================

// Admin dashboard (DEPRECATED - use /api/admin/dashboard from adminDashboardRoutes.js)
/* DEPRECATED - Commented out in favor of Phase 4 dashboard routes
app.get('/api/admin/dashboard-deprecated', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    // Get statistics using aggregation queries
    const totalUsersResult = await query('SELECT COUNT(*) as count FROM users');
    const activeUsersResult = await query('SELECT COUNT(*) as count FROM users WHERE status = ?', ['active']);
    const totalOrdersResult = await query('SELECT COUNT(*) as count FROM orders');
    const completeOrdersResult = await query('SELECT COUNT(*) as count FROM orders WHERE status = ?', ['complete']);
    const totalServicesResult = await query('SELECT COUNT(*) as count FROM services WHERE status = ?', ['active']);
    const depositsResult = await query('SELECT SUM(amount) as total FROM deposits WHERE status = ?', ['approved']);
    const pendingDepositsResult = await query('SELECT COUNT(*) as count FROM deposits WHERE status = ?', ['pending']);
    const rejectedDepositsResult = await query('SELECT COUNT(*) as count FROM deposits WHERE status = ?', ['rejected']);

    // Get API provider statistics
    const providersResult = await query('SELECT COUNT(*) as count FROM api_providers');
    const activeProvidersResult = await query('SELECT COUNT(*) as count FROM api_providers WHERE status = ?', ['active']);
    const providerServicesResult = await query(
      `SELECT ap.id, ap.name, COUNT(s.id) as service_count
       FROM api_providers ap
       LEFT JOIN services s ON ap.id = s.api_provider_id
       GROUP BY ap.id, ap.name`
    );

    // Get recent orders for chart
    const recentOrders = await query(
      `SELECT DATE(created_at) as date, COUNT(*) as count, SUM(charge) as revenue
       FROM orders
       WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
       GROUP BY DATE(created_at)
       ORDER BY date DESC`
    );

    // Get recent deposits for chart
    const recentDeposits = await query(
      `SELECT DATE(created_at) as date, COUNT(*) as count, SUM(amount) as amount
       FROM deposits
       WHERE status = 'approved' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
       GROUP BY DATE(created_at)
       ORDER BY date DESC`
    );

    // Get order status breakdown
    const orderStatusResult = await query(
      `SELECT status, COUNT(*) as count FROM orders GROUP BY status`
    );

    res.json({
      stats: {
        totalUsers: totalUsersResult[0].count,
        activeUsers: activeUsersResult[0].count,
        totalOrders: totalOrdersResult[0].count,
        completeOrders: completeOrdersResult[0].count,
        totalServices: totalServicesResult[0].count,
        totalDeposited: depositsResult[0].total || 0,
        pendingDeposits: pendingDepositsResult[0].count,
        rejectedDeposits: rejectedDepositsResult[0].count,
        totalProviders: providersResult[0].count,
        activeProviders: activeProvidersResult[0].count
      },
      providers: providerServicesResult,
      orderStatus: orderStatusResult,
      charts: {
        orders: recentOrders,
        deposits: recentDeposits
      }
    });
  } catch (error) {
    console.error('Admin dashboard error:', error);
    res.status(500).json({ error: 'Failed to load dashboard' });
  }
});
*/

// Get all users (admin)
app.get('/api/admin/users', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { page = 1, limit = 20, search = '', status } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE 1=1';
    let params = [];

    if (search) {
      whereClause += ' AND (email LIKE ? OR first_name LIKE ? OR last_name LIKE ?)';
      const searchParam = `%${search}%`;
      params.push(searchParam, searchParam, searchParam);
    }

    if (status) {
      whereClause += ' AND status = ?';
      params.push(status);
    }

    const users = await query(
      `SELECT id, username, email, first_name, last_name, balance, status, email_verified, role,
       created_at, last_login_at, last_login_ip
       FROM users
       ${whereClause} 
       ORDER BY created_at DESC 
       LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );

    const countResult = await query(
      `SELECT COUNT(*) as total FROM users ${whereClause}`,
      params
    );

    res.json({
      users,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: countResult[0].total,
        pages: Math.ceil(countResult[0].total / limit)
      }
    });
  } catch (error) {
    console.error('Admin users error:', error);
    res.status(500).json({ error: 'Failed to load users' });
  }
});

// Get user details (admin)
app.get('/api/admin/users/:userId', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { userId } = req.params;

    const users = await query('SELECT * FROM users WHERE id = ?', [userId]);

    if (users.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    // Get user statistics
    const ordersCount = await query('SELECT COUNT(*) as count FROM orders WHERE user_id = ?', [userId]);
    const depositsSum = await query('SELECT SUM(amount) as total FROM deposits WHERE user_id = ? AND status = ?', [userId, 'approved']);
    const transactionsCount = await query('SELECT COUNT(*) as count FROM transactions WHERE user_id = ?', [userId]);

    res.json({
      user: users[0],
      stats: {
        totalOrders: ordersCount[0].count,
        totalDeposited: depositsSum[0].total || 0,
        totalTransactions: transactionsCount[0].count
      }
    });
  } catch (error) {
    console.error('Admin user details error:', error);
    res.status(500).json({ error: 'Failed to load user details' });
  }
});

// Update user balance (admin)
app.post('/api/admin/users/:userId/balance', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { userId } = req.params;
    const { amount, type, description } = req.body; // type: 'add' or 'subtract'

    if (!amount || !type) {
      return res.status(400).json({ error: 'Amount and type are required' });
    }

    // Get current balance
    const users = await query('SELECT balance FROM users WHERE id = ?', [userId]);
    
    if (users.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    const currentBalance = parseFloat(users[0].balance);
    const changeAmount = parseFloat(amount);
    const newBalance = type === 'add' ? currentBalance + changeAmount : currentBalance - changeAmount;

    if (newBalance < 0) {
      return res.status(400).json({ error: 'Insufficient balance' });
    }

    // Update balance
    await query('UPDATE users SET balance = ? WHERE id = ?', [newBalance, userId]);

    // Create transaction record
    await query(
      `INSERT INTO transactions (user_id, type, amount, post_balance, description) 
       VALUES (?, ?, ?, ?, ?)`,
      [userId, type === 'add' ? 'bonus' : 'withdrawal', type === 'add' ? changeAmount : -changeAmount, newBalance, description || 'Balance adjustment by admin']
    );

    res.json({ message: 'Balance updated successfully', newBalance });
  } catch (error) {
    console.error('Balance update error:', error);
    res.status(500).json({ error: 'Failed to update balance' });
  }
});

// Ban/Unban user (admin)
app.put('/api/admin/users/:userId/status', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { userId } = req.params;
    const { status } = req.body; // 'active', 'banned', 'suspended'

    if (!['active', 'banned', 'suspended'].includes(status)) {
      return res.status(400).json({ error: 'Invalid status' });
    }

    await query('UPDATE users SET status = ? WHERE id = ?', [status, userId]);

    res.json({ message: 'User status updated successfully' });
  } catch (error) {
    console.error('User status update error:', error);
    res.status(500).json({ error: 'Failed to update user status' });
  }
});

// =====================
// ADMIN IMPERSONATION
// =====================

// Start impersonating a user
app.post('/api/admin/users/:userId/impersonate', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { userId } = req.params;
    const adminId = req.user.userId;

    // Verify the target user exists and is not admin/suspended
    const results = await query('SELECT id, first_name, last_name, email, status, role FROM users WHERE id = ?', [userId]);

    if (results.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    const targetUser = results[0];

    // Prevent impersonating other admins
    if (targetUser.role === 'admin') {
      return res.status(403).json({ error: 'Cannot impersonate admin users' });
    }

    // Prevent impersonating suspended/banned users
    if (['suspended', 'banned'].includes(targetUser.status)) {
      return res.status(403).json({ error: 'Cannot impersonate inactive users' });
    }

    // Create impersonation token
    const impersonationToken = jwt.sign(
      {
        userId: targetUser.id,
        email: targetUser.email,
        role: targetUser.role,
        impersonatingAdmin: adminId
      },
      process.env.JWT_SECRET,
      { expiresIn: process.env.JWT_EXPIRES_IN || '7d' }
    );

    // Log the impersonation
    console.log(`Admin ${adminId} is impersonating user ${userId}`);

    res.json({
      message: 'Impersonation started',
      token: impersonationToken,
      user: {
        userId: targetUser.id,
        email: targetUser.email,
        name: `${targetUser.first_name} ${targetUser.last_name}`
      }
    });
  } catch (error) {
    console.error('Impersonation error:', error);
    res.status(500).json({ error: 'Failed to start impersonation' });
  }
});

// Stop impersonating (return to admin account)
app.post('/api/admin/impersonate/stop', authenticateToken, async (req, res) => {
  try {
    // Check if this is an impersonation session
    if (!req.user.impersonatingAdmin) {
      return res.status(400).json({ error: 'Not in impersonation mode' });
    }

    const adminId = req.user.impersonatingAdmin;

    // Create new token as admin
    const adminToken = jwt.sign(
      {
        userId: adminId,
        role: 'admin'
      },
      process.env.JWT_SECRET,
      { expiresIn: process.env.JWT_EXPIRES_IN || '7d' }
    );

    console.log(`Admin ${adminId} ended impersonation of user ${req.user.userId}`);

    res.json({
      success: true,
      message: 'Impersonation stopped',
      token: adminToken
    });
  } catch (error) {
    console.error('Stop impersonation error:', error);
    res.status(500).json({ error: 'Failed to stop impersonation' });
  }
});

// Get all orders (admin)
app.get('/api/admin/orders', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { page = 1, limit = 20, status, userId } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE 1=1';
    let params = [];

    if (status) {
      whereClause += ' AND o.status = ?';
      params.push(status);
    }

    if (userId) {
      whereClause += ' AND o.user_id = ?';
      params.push(userId);
    }

    const orders = await query(
      `SELECT o.*, s.name as service_name, c.name as category_name, 
       u.email as user_email, u.first_name, u.last_name 
       FROM orders o 
       INNER JOIN services s ON o.service_id = s.id 
       INNER JOIN categories c ON s.category_id = c.id 
       INNER JOIN users u ON o.user_id = u.id 
       ${whereClause} 
       ORDER BY o.created_at DESC 
       LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );

    const countResult = await query(
      `SELECT COUNT(*) as total FROM orders o ${whereClause}`,
      params
    );

    res.json({
      orders,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: countResult[0].total,
        pages: Math.ceil(countResult[0].total / limit)
      }
    });
  } catch (error) {
    console.error('Admin orders error:', error);
    res.status(500).json({ error: 'Failed to load orders' });
  }
});

// Update order status (admin)
app.put('/api/admin/orders/:orderId/status', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { orderId } = req.params;
    const { status, statusDescription } = req.body;

    const validStatuses = ['pending', 'processing', 'complete', 'cancelled', 'refunded', 'partial'];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({ error: 'Invalid status' });
    }

    await query(
      'UPDATE orders SET status = ?, status_description = ?, completed_at = ? WHERE id = ?',
      [status, statusDescription, status === 'complete' ? new Date() : null, orderId]
    );

    res.json({ message: 'Order status updated successfully' });
  } catch (error) {
    console.error('Order status update error:', error);
    res.status(500).json({ error: 'Failed to update order status' });
  }
});

// Refund order (admin)
app.post('/api/admin/orders/:orderId/refund', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { orderId } = req.params;
    const { reason } = req.body;

    // Get order details
    const orders = await query(
      `SELECT o.*, ap.api_key
       FROM orders o
       INNER JOIN services s ON o.service_id = s.id
       INNER JOIN api_providers ap ON s.api_provider_id = ap.id
       WHERE o.id = ?`,
      [orderId]
    );

    if (orders.length === 0) {
      return res.status(404).json({ error: 'Order not found' });
    }

    const order = orders[0];

    // Check if order is already refunded
    if (order.status === 'refunded') {
      return res.status(400).json({ error: 'Order already refunded' });
    }

    // Try to refund with API provider
    let providerRefundSuccess = false;
    if (order.provider_order_id) {
      const refundResult = await owletService.refundOrder(order.provider_order_id);
      providerRefundSuccess = refundResult.success;
    }

    // Update order status
    await query(
      `UPDATE orders SET status = 'refunded', status_description = ? WHERE id = ?`,
      [reason || 'Refunded by admin', orderId]
    );

    // Refund balance to user if not already refunded
    if (order.charge > 0 && !['refunded', 'cancelled'].includes(order.status)) {
      await query(
        'UPDATE users SET balance = balance + ? WHERE id = ?',
        [order.charge, order.user_id]
      );

      // Record refund transaction
      await query(
        `INSERT INTO transactions (user_id, order_id, type, amount, status, description, created_at)
         VALUES (?, ?, 'credit', ?, 'completed', ?, NOW())`,
        [order.user_id, orderId, order.charge, `Admin refund for order #${orderId}: ${reason || 'No reason provided'}`]
      );
    }

    res.json({
      message: 'Order refunded successfully',
      orderId: orderId,
      refundAmount: order.charge,
      providerRefundStatus: providerRefundSuccess ? 'completed' : 'pending'
    });
  } catch (error) {
    console.error('Order refund error:', error);
    res.status(500).json({ error: 'Failed to refund order', details: error.message });
  }
});

// Get all deposits (admin)
app.get('/api/admin/deposits', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { page = 1, limit = 20, status } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE 1=1';
    let params = [];

    if (status) {
      whereClause += ' AND d.status = ?';
      params.push(status);
    }

    const deposits = await query(
      `SELECT d.*, u.email as user_email, u.first_name, u.last_name 
       FROM deposits d 
       INNER JOIN users u ON d.user_id = u.id 
       ${whereClause} 
       ORDER BY d.created_at DESC 
       LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );

    const countResult = await query(
      `SELECT COUNT(*) as total FROM deposits d ${whereClause}`,
      params
    );

    res.json({
      deposits,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: countResult[0].total,
        pages: Math.ceil(countResult[0].total / limit)
      }
    });
  } catch (error) {
    console.error('Admin deposits error:', error);
    res.status(500).json({ error: 'Failed to load deposits' });
  }
});

// Approve deposit (admin)
app.put('/api/admin/deposits/:depositId/approve', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { depositId } = req.params;
    const { adminNote } = req.body;

    const deposits = await query('SELECT * FROM deposits WHERE id = ? AND status = ?', [depositId, 'pending']);
    if (!deposits.length) return res.status(400).json({ error: 'Deposit not found or already processed' });
    const deposit = deposits[0];
    await query('UPDATE deposits SET status = ?, approved_at = NOW(), admin_note = ? WHERE id = ?', ['approved', adminNote || 'Approved by admin', depositId]);
    await query('UPDATE users SET balance = balance + ? WHERE id = ?', [deposit.amount, deposit.user_id]);
    const updatedUser = await query('SELECT balance FROM users WHERE id = ?', [deposit.user_id]);
    const postBalance = parseFloat(updatedUser[0]?.balance ?? 0);
    await query(
      `INSERT INTO transactions (user_id, type, amount, charge, post_balance, description, reference_id, created_at)
       VALUES (?, 'deposit', ?, 0, ?, ?, ?, NOW())`,
      [deposit.user_id, deposit.amount, postBalance, `Admin approved deposit ₦${deposit.amount}`, String(deposit.id)]
    );
    res.json({ message: 'Deposit approved successfully' });
  } catch (error) {
    console.error('Deposit approval error:', error);
    res.status(500).json({ error: 'Failed to approve deposit' });
  }
});

// Reject deposit (admin)
app.put('/api/admin/deposits/:depositId/reject', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { depositId } = req.params;
    const { adminNote } = req.body;

    await query(
      'UPDATE deposits SET status = ?, admin_note = ? WHERE id = ?',
      ['rejected', adminNote || 'Rejected by admin', depositId]
    );

    res.json({ message: 'Deposit rejected successfully' });
  } catch (error) {
    console.error('Deposit rejection error:', error);
    res.status(500).json({ error: 'Failed to reject deposit' });
  }
});

// Admin: All transactions report
app.get('/api/admin/transactions', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { page = 1, limit = 20, type, search } = req.query;
    const offset = (page - 1) * limit;
    let where = 'WHERE 1=1';
    let params = [];
    if (type && type !== 'any') { where += ' AND t.type = ?'; params.push(type); }
    if (search) {
      where += ' AND (u.email LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ? OR CAST(t.id AS CHAR) LIKE ?)';
      const s = `%${search}%`; params.push(s, s, s, s);
    }
    const transactions = await query(
      `SELECT t.*, u.email as user_email, u.first_name, u.last_name
       FROM transactions t INNER JOIN users u ON t.user_id = u.id
       ${where} ORDER BY t.created_at DESC LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );
    const countResult = await query(
      `SELECT COUNT(*) as total FROM transactions t INNER JOIN users u ON t.user_id = u.id ${where}`,
      params
    );
    res.json({ transactions, pagination: { page: parseInt(page), limit: parseInt(limit), total: countResult[0].total, pages: Math.ceil(countResult[0].total / limit) } });
  } catch (error) {
    console.error('Admin transactions error:', error);
    res.status(500).json({ error: 'Failed to load transactions' });
  }
});

// Admin: Login activities report
app.get('/api/admin/login-activities', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { page = 1, limit = 20, search } = req.query;
    const offset = (page - 1) * limit;
    let where = 'WHERE 1=1';
    let params = [];
    if (search) {
      where += ' AND (u.email LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ?)';
      const s = `%${search}%`; params.push(s, s, s);
    }
    const activities = await query(
      `SELECT la.*, u.email as user_email, u.first_name, u.last_name
       FROM login_activities la INNER JOIN users u ON la.user_id = u.id
       ${where} ORDER BY la.login_at DESC LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );
    const countResult = await query(
      `SELECT COUNT(*) as total FROM login_activities la INNER JOIN users u ON la.user_id = u.id ${where}`,
      params
    );
    res.json({ activities, pagination: { page: parseInt(page), limit: parseInt(limit), total: countResult[0].total, pages: Math.ceil(countResult[0].total / limit) } });
  } catch (error) {
    console.error('Admin login activities error:', error);
    res.status(500).json({ error: 'Failed to load login activities' });
  }
});

// Admin: Deposit summary stats
app.get('/api/admin/deposits/stats', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const stats = await query(`
      SELECT
        SUM(CASE WHEN status = 'approved' THEN amount ELSE 0 END) as approved_total,
        SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) as pending_total,
        SUM(CASE WHEN status = 'rejected' THEN amount ELSE 0 END) as rejected_total,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count
      FROM deposits`
    );
    res.json({ stats: stats[0] });
  } catch (error) {
    res.status(500).json({ error: 'Failed to load deposit stats' });
  }
});

// Get all services (admin)
app.get('/api/admin/services', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { search = '', category = '', page = 1, limit = 50 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const params = [];

    let baseQuery = `
      SELECT s.id, s.name, s.description, s.price, s.min_quantity, s.max_quantity,
             s.status, s.api_service_id, s.created_at,
             COALESCE(c.name, 'Uncategorised') as category_name,
             COALESCE(ap.name, '—') as provider_name,
             s.category_id
      FROM services s
      LEFT JOIN categories c ON s.category_id = c.id
      LEFT JOIN api_providers ap ON s.api_provider_id = ap.id
      WHERE 1=1`;

    if (search) {
      baseQuery += ` AND (s.name LIKE ? OR c.name LIKE ?)`;
      params.push(`%${search}%`, `%${search}%`);
    }
    if (category) {
      baseQuery += ` AND s.category_id = ?`;
      params.push(category);
    }

    const [countRow] = await query(
      `SELECT COUNT(*) as total FROM services s LEFT JOIN categories c ON s.category_id = c.id WHERE 1=1` +
      (search ? ` AND (s.name LIKE ? OR c.name LIKE ?)` : '') +
      (category ? ` AND s.category_id = ?` : ''),
      params
    );
    const total = countRow?.total || 0;

    const services = await query(
      baseQuery + ` ORDER BY c.name, s.id LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );

    res.json({ services, total, page: parseInt(page), limit: parseInt(limit), pages: Math.ceil(total / parseInt(limit)) });
  } catch (error) {
    console.error('Admin services error:', error);
    res.status(500).json({ error: 'Failed to load services', details: error.message });
  }
});

// Create service (admin)
app.post('/api/admin/services', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { categoryId, name, description, price, minQuantity, maxQuantity, apiProviderId, apiServiceId } = req.body;

    const result = await query(
      `INSERT INTO services (category_id, name, description, price, min_quantity, max_quantity, api_provider_id, api_service_id, status) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active')`,
      [categoryId, name, description, price, minQuantity, maxQuantity, apiProviderId, apiServiceId]
    );

    res.status(201).json({
      message: 'Service created successfully',
      serviceId: result.insertId
    });
  } catch (error) {
    console.error('Service creation error:', error);
    res.status(500).json({ error: 'Failed to create service' });
  }
});

// Update service (admin)
app.put('/api/admin/services/:serviceId', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { serviceId } = req.params;
    const { name, description, price, minQuantity, maxQuantity, status } = req.body;

    await query(
      `UPDATE services SET name = ?, description = ?, price = ?, min_quantity = ?, max_quantity = ?, status = ? 
       WHERE id = ?`,
      [name, description, price, minQuantity, maxQuantity, status, serviceId]
    );

    res.json({ message: 'Service updated successfully' });
  } catch (error) {
    console.error('Service update error:', error);
    res.status(500).json({ error: 'Failed to update service' });
  }
});

// Delete service (admin)
app.delete('/api/admin/services/:serviceId', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { serviceId } = req.params;

    await query('DELETE FROM services WHERE id = ?', [serviceId]);

    res.json({ message: 'Service deleted successfully' });
  } catch (error) {
    console.error('Service deletion error:', error);
    res.status(500).json({ error: 'Failed to delete service' });
  }
});

// =====================
// API PROVIDER MANAGEMENT (ADMIN)
// =====================

// Get all API providers (admin)
app.get('/api/admin/api-providers', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const providers = await query(
      'SELECT * FROM api_providers ORDER BY created_at DESC'
    );

    res.json({ providers });
  } catch (error) {
    console.error('API providers error:', error);
    res.status(500).json({ error: 'Failed to load API providers' });
  }
});

// Get single API provider (admin)
app.get('/api/admin/api-providers/:providerId', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { providerId } = req.params;

    const providers = await query(
      'SELECT * FROM api_providers WHERE id = ?',
      [providerId]
    );

    if (providers.length === 0) {
      return res.status(404).json({ error: 'API provider not found' });
    }

    res.json({ provider: providers[0] });
  } catch (error) {
    console.error('API provider error:', error);
    res.status(500).json({ error: 'Failed to load API provider' });
  }
});

// Create API provider (admin)
app.post('/api/admin/api-providers', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { name, description, apiUrl, apiKey, status } = req.body;

    if (!name || !apiUrl || !apiKey) {
      return res.status(400).json({ error: 'Name, API URL, and API Key are required' });
    }

    const result = await query(
      `INSERT INTO api_providers (name, description, api_url, api_key, status, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, NOW(), NOW())`,
      [name, description || null, apiUrl, apiKey, status || 'active']
    );

    res.status(201).json({
      message: 'API provider created successfully',
      providerId: result.insertId
    });
  } catch (error) {
    console.error('API provider creation error:', error);
    res.status(500).json({ error: 'Failed to create API provider' });
  }
});

// Update API provider (admin)
app.put('/api/admin/api-providers/:providerId', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { providerId } = req.params;
    const { name, description, apiUrl, apiKey, status } = req.body;

    await query(
      `UPDATE api_providers SET name = ?, description = ?, api_url = ?, api_key = ?, status = ?, updated_at = NOW()
       WHERE id = ?`,
      [name, description, apiUrl, apiKey, status, providerId]
    );

    res.json({ message: 'API provider updated successfully' });
  } catch (error) {
    console.error('API provider update error:', error);
    res.status(500).json({ error: 'Failed to update API provider' });
  }
});

// Delete API provider (admin)
app.delete('/api/admin/api-providers/:providerId', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { providerId } = req.params;

    await query('DELETE FROM api_providers WHERE id = ?', [providerId]);

    res.json({ message: 'API provider deleted successfully' });
  } catch (error) {
    console.error('API provider deletion error:', error);
    res.status(500).json({ error: 'Failed to delete API provider' });
  }
});

// Test API provider connection (admin)
app.post('/api/admin/api-providers/:providerId/test-connection', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { providerId } = req.params;

    const providers = await query(
      'SELECT * FROM api_providers WHERE id = ?',
      [providerId]
    );

    if (providers.length === 0) {
      return res.status(404).json({ error: 'API provider not found' });
    }

    const provider = providers[0];

    // For Owlet API, test the connection
    const balanceResult = await owletService.getBalance();

    res.json({
      message: 'Connection test completed',
      provider: provider.name,
      result: balanceResult
    });
  } catch (error) {
    console.error('API provider connection test error:', error);
    res.status(500).json({ error: 'Connection test failed' });
  }
});

// Get API provider balance (admin)
app.get('/api/admin/api-providers/:providerId/balance', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { providerId } = req.params;

    const providers = await query(
      'SELECT * FROM api_providers WHERE id = ?',
      [providerId]
    );

    if (providers.length === 0) {
      return res.status(404).json({ error: 'API provider not found' });
    }

    const provider = providers[0];

    // For Owlet API, get the balance
    const balanceResult = await owletService.getBalance();

    res.json({
      provider: provider.name,
      balance: balanceResult
    });
  } catch (error) {
    console.error('API provider balance error:', error);
    res.status(500).json({ error: 'Failed to get balance' });
  }
});

// Sync services from API provider (admin)
app.post('/api/admin/api-providers/:providerId/sync-services', authenticateToken, authenticateAdmin, async (req, res) => {
  try {
    const { providerId } = req.params;

    const providers = await query(
      'SELECT * FROM api_providers WHERE id = ?',
      [providerId]
    );

    if (providers.length === 0) {
      return res.status(404).json({ error: 'API provider not found' });
    }

    const provider = providers[0];

    // Get services from Owlet API
    const servicesResult = await owletService.getServices();

    if (!servicesResult || servicesResult.length === 0) {
      return res.status(400).json({ error: 'No services returned from provider' });
    }

    let inserted = 0;
    let updated = 0;

    // Insert or update services
    for (const service of servicesResult) {
      const apiServiceId = service.service || service.id;
      const categoryId = 1; // Default to Instagram - you can map categories based on service name

      try {
        // Check if service exists
        const existing = await query(
          'SELECT id FROM services WHERE api_service_id = ? AND api_provider_id = ?',
          [apiServiceId, providerId]
        );

        if (existing.length > 0) {
          // Update existing service
          await query(
            `UPDATE services SET name = ?, price = ?, min_quantity = ?, max_quantity = ?, description = ?, updated_at = NOW()
             WHERE api_service_id = ? AND api_provider_id = ?`,
            [service.name, service.rate, service.min, service.max, service.category || service.name, apiServiceId, providerId]
          );
          updated++;
        } else {
          // Insert new service
          await query(
            `INSERT INTO services (category_id, api_provider_id, api_service_id, name, description, price, min_quantity, max_quantity, status, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active', NOW(), NOW())`,
            [categoryId, providerId, apiServiceId, service.name, service.category || service.name, service.rate, service.min, service.max]
          );
          inserted++;
        }
      } catch (error) {
        console.error('Failed to sync service:', error);
      }
    }

    res.json({
      message: 'Services synced successfully',
      inserted,
      updated,
      total: servicesResult.length
    });
  } catch (error) {
    console.error('Service sync error:', error);
    res.status(500).json({ error: 'Failed to sync services' });
  }
});


// Direct Owlet order - auto-creates DB service if missing
app.post('/api/orders/owlet-direct', authenticateToken, async (req, res) => {
  try {
    const { owletServiceId, serviceName, serviceCategory, serviceRate, link, quantity } = req.body;
    const userId = req.user.userId;

    if (!owletServiceId || !link || !quantity) {
      return res.status(400).json({ error: 'Missing required fields' });
    }

    const qty = parseInt(quantity);
    const rate = parseFloat(serviceRate || 0);
    const charge = parseFloat(((qty / 1000) * rate).toFixed(6));

    // Check user balance
    const users = await query('SELECT balance FROM users WHERE id = ?', [userId]);
    if (!users.length) return res.status(404).json({ error: 'User not found' });
    if (parseFloat(users[0].balance) < charge) {
      return res.status(400).json({ error: 'Insufficient balance', required: charge, available: parseFloat(users[0].balance) });
    }

    // Get provider ID (needed for both service creation and order)
    const providers = await query('SELECT id FROM api_providers WHERE status = ? LIMIT 1', ['active']);
    const providerId = providers.length ? providers[0].id : 1;

    // Find or create service in DB
    let serviceId;
    const existing = await query('SELECT id FROM services WHERE api_service_id = ? LIMIT 1', [String(owletServiceId)]);
    if (existing.length > 0) {
      serviceId = existing[0].id;
    } else {
      const catName = (serviceCategory || '').toLowerCase();
      let categoryId = 1;
      const cats = await query('SELECT id, name FROM categories WHERE status = ?', ['active']);
      for (const cat of cats) {
        if (catName.includes(cat.name.toLowerCase())) { categoryId = cat.id; break; }
      }
      const ins = await query(
        `INSERT INTO services (category_id, api_provider_id, api_service_id, name, description, price, min_quantity, max_quantity, status, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active', NOW(), NOW())`,
        [categoryId, providerId, String(owletServiceId), serviceName, serviceCategory, rate, 1, 10000000]
      );
      serviceId = ins.insertId;
    }

    // Place order with Owlet
    const owletResult = await owletService.placeOrder(owletServiceId, link, qty);
    if (!owletResult.success) {
      return res.status(400).json({ error: 'Provider error: ' + owletResult.error });
    }

    // Deduct balance first
    await query('UPDATE users SET balance = balance - ? WHERE id = ?', [charge, userId]);

    // Get post-balance for transaction record
    const updatedUser = await query('SELECT balance FROM users WHERE id = ?', [userId]);
    const postBalance = parseFloat(updatedUser[0]?.balance ?? 0);

    // Record order (using correct DB column names)
    const orderResult = await query(
      `INSERT INTO orders (user_id, service_id, api_provider_id, link_url, quantity, price, status, api_order_id, start_counter, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, 'processing', ?, ?, NOW(), NOW())`,
      [userId, serviceId, providerId, link, qty, charge, owletResult.orderId, owletResult.startCount || null]
    );

    // Record transaction (using correct DB column names)
    await query(
      `INSERT INTO transactions (user_id, type, amount, charge, post_balance, description, reference_id, created_at)
       VALUES (?, 'order', ?, 0, ?, ?, ?, NOW())`,
      [userId, charge, postBalance, `Order #${orderResult.insertId} - ${serviceName}`, String(orderResult.insertId)]
    );

    const dbOrderId = orderResult.insertId;

    // Immediately poll Owlet for initial status (non-blocking — don't delay response)
    setImmediate(async () => {
      try {
        if (!owletResult.orderId) return;
        const statusResult = await owletService.getOrderStatus(owletResult.orderId);
        if (statusResult.success && statusResult.status) {
          let dbStatus = statusResult.status;
          if (dbStatus === 'completed') dbStatus = 'complete';
          if (['complete', 'failed', 'cancelled', 'in_progress', 'processing', 'partial'].includes(dbStatus)) {
            await query(
              `UPDATE orders SET status = ?, remains = ?, updated_at = NOW() WHERE id = ?`,
              [dbStatus, statusResult.remains || null, dbOrderId]
            );
          }
        }
      } catch (e) {
        console.error(`Post-order status check failed for order ${dbOrderId}:`, e.message);
      }
    });

    res.status(201).json({
      success: true,
      message: 'Order placed successfully',
      orderId: dbOrderId,
      providerOrderId: owletResult.orderId,
      charge,
      status: 'processing'
    });
  } catch (error) {
    console.error('Direct Owlet order error:', error);
    res.status(500).json({ error: 'Failed to place order', details: error.message });
  }
});

// =====================
// ADMIN ROUTES (PHASE 1-4)
// =====================

// Phase 1: Owlet API Sync
app.use('/api/admin', adminSyncRoutes);

// Phase 3: User Management
app.use('/api/admin', requireAdmin, adminUserRoutes);

// Phase 3: Impersonation
app.use('/api/admin', adminImpersonationRoutes);

// Phase 4: Admin Dashboard & Management
app.use('/api/admin', requireAdmin, adminDashboardRoutes);
app.use('/api/admin', requireAdmin, adminServiceRoutes);
app.use('/api/admin', requireAdmin, adminCategoryRoutes);
app.use('/api/admin', requireAdmin, adminPaymentGatewayRoutes);
app.use('/api/admin', requireAdmin, adminSettingsRoutes);
app.use('/api/admin', requireAdmin, adminOrderRoutes);

// Payment System Routes
app.use('/api/deposits', depositRoutes);
app.use('/api/webhooks', webhookRoutes);
app.use('/api/admin', requireAdmin, adminPaymentRoutes);

// =====================
// CRON JOB
// =====================

app.get('/api/cron', async (req, res) => {
  try {
    // Verify cron secret
    const cronSecret = req.headers['x-cron-secret'] || req.query.secret;
    if (cronSecret !== process.env.CRON_SECRET) {
      return res.status(401).json({ error: 'Unauthorized' });
    }

    const startTime = Date.now();

    // Get processing orders with provider info
    const orders = await query(
      `SELECT o.*, ap.api_key, ap.name as provider_name
       FROM orders o
       INNER JOIN services s ON o.service_id = s.id
       INNER JOIN api_providers ap ON s.api_provider_id = ap.id
       WHERE o.status IN ('pending', 'processing', 'in_progress')
       AND o.api_order_id IS NOT NULL
       AND ap.status = 'active'
       LIMIT 100`
    );

    let processed = 0;
    let updated = 0;
    let completed = 0;
    let errors = [];

    for (const order of orders) {
      try {
        // Check order status from Owlet API
        const statusResult = await owletService.getOrderStatus(order.api_order_id);

        if (statusResult.success) {
          const newStatus = statusResult.status || order.status;

          // Map Owlet status to our status
          let dbStatus = newStatus;
          if (newStatus === 'completed') dbStatus = 'complete';
          if (newStatus === 'failed') dbStatus = 'failed';
          if (newStatus === 'cancelled') dbStatus = 'cancelled';

          // Update order if status changed
          if (dbStatus !== order.status) {
            await query(
              `UPDATE orders
               SET status = ?, remains = ?, updated_at = NOW()
               WHERE id = ?`,
              [dbStatus, statusResult.remains || null, order.id]
            );
            updated++;

            if (dbStatus === 'complete') {
              completed++;
            }
          }
          processed++;
        } else {
          errors.push({
            orderId: order.id,
            providerOrderId: order.provider_order_id,
            error: statusResult.error
          });
        }
      } catch (error) {
        console.error(`Order ${order.id} status check failed:`, error);
        errors.push({
          orderId: order.id,
          error: error.message
        });
      }
    }

    const duration = Math.round((Date.now() - startTime) / 1000);

    // Log cron execution
    await query(
      `INSERT INTO cron_logs (execution_time, duration_seconds, status, actions_performed, errors)
       VALUES (NOW(), ?, 'success', ?, ?)`,
      [duration, JSON.stringify({
        processed,
        updated,
        completed,
        total: orders.length
      }), errors.length > 0 ? JSON.stringify(errors) : null]
    );

    res.json({
      message: 'Cron job completed',
      processed,
      updated,
      completed,
      total: orders.length,
      duration: `${duration}s`,
      errors: errors.length
    });
  } catch (error) {
    console.error('Cron error:', error);
    res.status(500).json({ error: 'Cron job failed' });
  }
});

// =====================
// PUBLIC ROUTES
// =====================

// Subscribe to newsletter
app.post('/api/subscribe', async (req, res) => {
  try {
    const { email } = req.body;

    if (!email) {
      return res.status(400).json({ error: 'Email is required' });
    }

    // Check if already subscribed
    const existing = await query(
      'SELECT id FROM email_subscribers WHERE email = ?',
      [email]
    );

    if (existing.length > 0) {
      return res.status(400).json({ error: 'Email already subscribed' });
    }

    await query(
      'INSERT INTO email_subscribers (email, status) VALUES (?, ?)',
      [email, 'active']
    );

    res.json({ message: 'Subscribed successfully' });
  } catch (error) {
    console.error('Subscribe error:', error);
    res.status(500).json({ error: 'Subscription failed' });
  }
});

// Contact form
app.post('/api/contact', async (req, res) => {
  try {
    const { name, email, subject, message } = req.body;

    if (!name || !email || !message) {
      return res.status(400).json({ error: 'All fields are required' });
    }

    // TODO: Send email notification
    // TODO: Save to database if needed

    res.json({ message: 'Message sent successfully. We will get back to you soon!' });
  } catch (error) {
    console.error('Contact error:', error);
    res.status(500).json({ error: 'Failed to send message' });
  }
});

// Get site settings
app.get('/api/settings', async (req, res) => {
  try {
    const settings = await query('SELECT setting_key, setting_value FROM site_settings');
    
    const settingsObj = {};
    settings.forEach(setting => {
      settingsObj[setting.setting_key] = setting.setting_value;
    });

    res.json({ settings: settingsObj });
  } catch (error) {
    console.error('Settings error:', error);
    res.status(500).json({ error: 'Failed to load settings' });
  }
});

// =====================
// ERROR HANDLING
// =====================

// 404 handler
app.use((req, res) => {
  res.status(404).json({ error: 'Endpoint not found' });
});

// Global error handler
app.use((err, req, res, next) => {
  console.error(err.stack);
  res.status(500).json({
    error: 'Internal server error',
    message: process.env.NODE_ENV === 'development' ? err.message : undefined
  });
});

// =====================
// START SERVER
// =====================

const PORT = process.env.PORT || 5000;

app.listen(PORT, () => {
  console.log(`\n🚀 WakaTraffic Server Started Successfully!`);
  console.log(`═══════════════════════════════════════════`);
  console.log(`📝 Environment: ${process.env.NODE_ENV || 'development'}`);
  console.log(`🌐 Server: http://localhost:${PORT}`);
  console.log(`🔌 API Base: http://localhost:${PORT}/api`);
  console.log(`🗄️  Database: MySQL (${process.env.DB_NAME || 'wakatraffic'})`);
  console.log(`═══════════════════════════════════════════\n`);
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  console.log('SIGTERM signal received: closing server');
  await pool.end();
  process.exit(0);
});

process.on('SIGINT', async () => {
  console.log('SIGINT signal received: closing server');
  await pool.end();
  process.exit(0);
});

// Export for testing
module.exports = app;
