const express = require('express');
const router = express.Router();
const db = require('../config/database');
const { auth, isAdmin } = require('../middleware/auth');

// Apply authentication to all admin routes
router.use(auth);
router.use(isAdmin);

// Dashboard Stats
router.get('/stats', async (req, res) => {
  try {
    const today = new Date().toISOString().split('T')[0];
    
    // Today's bookings
    const todayBookings = await db.get(
      `SELECT COUNT(*) as count FROM bookings WHERE DATE(pickup_date) = ?`,
      [today]
    );
    
    // Yesterday's bookings for comparison
    const yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);
    const yesterdayBookings = await db.get(
      `SELECT COUNT(*) as count FROM bookings WHERE DATE(pickup_date) = ?`,
      [yesterday.toISOString().split('T')[0]]
    );
    
    // Pending quotes (quotes older than 1 hour, not confirmed)
    const pendingQuotes = await db.get(
      `SELECT COUNT(*) as count FROM quotes WHERE status = 'pending' AND created_at > datetime('now', '-24 hours')`
    );
    
    // Today's revenue
    const todayRevenue = await db.get(
      `SELECT SUM(total_price) as revenue FROM bookings 
       WHERE DATE(pickup_date) = ? AND status != 'cancelled'`,
      [today]
    );
    
    // Active drivers
    const activeDrivers = await db.get(
      `SELECT COUNT(*) as count FROM users WHERE role = 'driver' AND is_verified = 1`
    );
    
    // This week's bookings
    const weekStart = new Date();
    weekStart.setDate(weekStart.getDate() - weekStart.getDay());
    const weekBookings = await db.all(
      `SELECT 
        COUNT(*) as total,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
        SUM(CASE WHEN status != 'cancelled' THEN total_price ELSE 0 END) as revenue
       FROM bookings 
       WHERE DATE(pickup_date) >= ?`,
      [weekStart.toISOString().split('T')[0]]
    );
    
    res.json({
      todayBookings: todayBookings.count || 0,
      yesterdayBookings: yesterdayBookings.count || 0,
      pendingQuotes: pendingQuotes.count || 0,
      todayRevenue: todayRevenue.revenue || 0,
      activeDrivers: activeDrivers.count || 0,
      weekStats: weekBookings[0] || {
        total: 0,
        completed: 0,
        cancelled: 0,
        revenue: 0
      }
    });
  } catch (error) {
    console.error('Error fetching stats:', error);
    res.status(500).json({ error: 'Failed to fetch dashboard stats' });
  }
});

// Recent Bookings
router.get('/recent-bookings', async (req, res) => {
  try {
    const bookings = await db.all(
      `SELECT 
        b.id,
        b.booking_reference,
        b.pickup_location,
        b.dropoff_location,
        b.pickup_date,
        b.pickup_time,
        b.total_price,
        b.status,
        COALESCE(u.first_name || ' ' || u.last_name, b.guest_name) as customer_name,
        COALESCE(u.email, b.guest_email) as customer_email
       FROM bookings b
       LEFT JOIN users u ON b.user_id = u.id
       ORDER BY b.created_at DESC
       LIMIT 10`
    );
    
    res.json(bookings);
  } catch (error) {
    console.error('Error fetching recent bookings:', error);
    res.status(500).json({ error: 'Failed to fetch recent bookings' });
  }
});

// All Bookings (with pagination)
router.get('/bookings', async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;
    const status = req.query.status || 'all';
    
    let whereClause = '';
    let params = [];
    
    if (status !== 'all') {
      whereClause = 'WHERE b.status = ?';
      params.push(status);
    }
    
    const bookings = await db.all(
      `SELECT 
        b.*,
        COALESCE(u.first_name || ' ' || u.last_name, b.guest_name) as customer_name,
        COALESCE(u.email, b.guest_email) as customer_email,
        COALESCE(u.phone, b.guest_phone) as customer_phone,
        v.name as vehicle_name,
        d.first_name || ' ' || d.last_name as driver_name
       FROM bookings b
       LEFT JOIN users u ON b.user_id = u.id
       LEFT JOIN vehicles v ON b.vehicle_id = v.id
       LEFT JOIN users d ON b.driver_id = d.id
       ${whereClause}
       ORDER BY b.created_at DESC
       LIMIT ? OFFSET ?`,
      [...params, limit, offset]
    );
    
    const total = await db.get(
      `SELECT COUNT(*) as count FROM bookings b ${whereClause}`,
      params
    );
    
    res.json({
      bookings,
      pagination: {
        page,
        limit,
        total: total.count,
        pages: Math.ceil(total.count / limit)
      }
    });
  } catch (error) {
    console.error('Error fetching bookings:', error);
    res.status(500).json({ error: 'Failed to fetch bookings' });
  }
});

// Update Booking Status
router.patch('/bookings/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status, driver_id } = req.body;
    
    const updates = ['status = ?'];
    const params = [status];
    
    if (driver_id) {
      updates.push('driver_id = ?', 'assigned_at = CURRENT_TIMESTAMP');
      params.push(driver_id);
    }
    
    params.push(id);
    
    await db.run(
      `UPDATE bookings SET ${updates.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
      params
    );
    
    const updatedBooking = await db.get('SELECT * FROM bookings WHERE id = ?', [id]);
    res.json(updatedBooking);
  } catch (error) {
    console.error('Error updating booking:', error);
    res.status(500).json({ error: 'Failed to update booking' });
  }
});

// Get All Drivers
router.get('/drivers', async (req, res) => {
  try {
    const drivers = await db.all(
      `SELECT 
        id,
        first_name,
        last_name,
        email,
        phone,
        is_verified,
        created_at
       FROM users
       WHERE role = 'driver'
       ORDER BY first_name, last_name`
    );
    
    res.json(drivers);
  } catch (error) {
    console.error('Error fetching drivers:', error);
    res.status(500).json({ error: 'Failed to fetch drivers' });
  }
});

// Add Driver
router.post('/drivers', async (req, res) => {
  try {
    const { first_name, last_name, email, phone, password } = req.body;
    const bcrypt = require('bcryptjs');
    const hashedPassword = await bcrypt.hash(password, 10);
    
    const result = await db.run(
      `INSERT INTO users (first_name, last_name, email, phone, password, role, is_verified)
       VALUES (?, ?, ?, ?, ?, 'driver', 1)`,
      [first_name, last_name, email, phone, hashedPassword]
    );
    
    const newDriver = await db.get('SELECT * FROM users WHERE id = ?', [result.lastID]);
    res.status(201).json(newDriver);
  } catch (error) {
    console.error('Error adding driver:', error);
    res.status(500).json({ error: 'Failed to add driver' });
  }
});

// Get Pricing
router.get('/pricing', async (req, res) => {
  try {
    const vehicles = await db.all('SELECT * FROM vehicles ORDER BY capacity');
    const extras = await db.all('SELECT * FROM extras WHERE is_active = 1');
    const config = await db.all('SELECT * FROM pricing_config');
    
    res.json({ vehicles, extras, config });
  } catch (error) {
    console.error('Error fetching pricing:', error);
    res.status(500).json({ error: 'Failed to fetch pricing' });
  }
});

// Update Vehicle Pricing
router.patch('/vehicles/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { price_per_mile, minimum_fare, is_active } = req.body;
    
    await db.run(
      `UPDATE vehicles 
       SET price_per_mile = ?, minimum_fare = ?, is_active = ?, updated_at = CURRENT_TIMESTAMP
       WHERE id = ?`,
      [price_per_mile, minimum_fare, is_active ? 1 : 0, id]
    );
    
    const updatedVehicle = await db.get('SELECT * FROM vehicles WHERE id = ?', [id]);
    res.json(updatedVehicle);
  } catch (error) {
    console.error('Error updating vehicle:', error);
    res.status(500).json({ error: 'Failed to update vehicle' });
  }
});

// Update Extra
router.patch('/extras/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { price, is_active } = req.body;
    
    await db.run(
      `UPDATE extras 
       SET price = ?, is_active = ?, updated_at = CURRENT_TIMESTAMP
       WHERE id = ?`,
      [price, is_active ? 1 : 0, id]
    );
    
    const updatedExtra = await db.get('SELECT * FROM extras WHERE id = ?', [id]);
    res.json(updatedExtra);
  } catch (error) {
    console.error('Error updating extra:', error);
    res.status(500).json({ error: 'Failed to update extra' });
  }
});

// Get Messages/Contact Form Submissions
router.get('/messages', async (req, res) => {
  try {
    const messages = await db.all(
      `SELECT * FROM contact_messages ORDER BY created_at DESC LIMIT 50`
    );
    
    res.json(messages);
  } catch (error) {
    console.error('Error fetching messages:', error);
    res.status(500).json({ error: 'Failed to fetch messages' });
  }
});

// Mark Message as Read
router.patch('/messages/:id/read', async (req, res) => {
  try {
    const { id } = req.params;
    
    await db.run(
      `UPDATE contact_messages SET is_read = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
      [id]
    );
    
    const updatedMessage = await db.get('SELECT * FROM contact_messages WHERE id = ?', [id]);
    res.json(updatedMessage);
  } catch (error) {
    console.error('Error updating message:', error);
    res.status(500).json({ error: 'Failed to update message' });
  }
});

module.exports = router;
