const express = require('express');
const router = express.Router();
const { protect, adminOnly } = require('../middleware/authMiddleware');
const db = require('../config/database');

// Dashboard istatistikleri
router.get('/stats', protect, adminOnly, async (req, res) => {
  try {
    // Toplam Turlar (normal + umre)
    const [toursCount] = await db.query(
      'SELECT COUNT(*) as count FROM tours WHERE status = "active"'
    );
    const [umreToursCount] = await db.query(
      'SELECT COUNT(*) as count FROM umre_tours WHERE status = "active"'
    );
    const totalTours = toursCount[0].count + umreToursCount[0].count;

    // Toplam Kategoriler (normal + umre)
    const [categoriesCount] = await db.query(
      'SELECT COUNT(*) as count FROM categories WHERE status = "active"'
    );
    const [umreCategoriesCount] = await db.query(
      'SELECT COUNT(*) as count FROM umre_categories WHERE status = "active"'
    );
    const totalCategories = categoriesCount[0].count + umreCategoriesCount[0].count;

    // Toplam Alt Kategoriler (normal + umre)
    const [subCategoriesCount] = await db.query(
      'SELECT COUNT(*) as count FROM sub_categories WHERE status = "active"'
    );
    const [umreSubCategoriesCount] = await db.query(
      'SELECT COUNT(*) as count FROM umre_sub_categories WHERE status = "active"'
    );
    const totalSubCategories = subCategoriesCount[0].count + umreSubCategoriesCount[0].count;

    // Toplam Rezervasyonlar (orders tablosundan)
    const [reservationsCount] = await db.query(
      'SELECT COUNT(*) as count FROM orders'
    );

    // Toplam Blog Yazıları
    const [blogCount] = await db.query(
      'SELECT COUNT(*) as count FROM pages WHERE type = "blog" AND status = "published"'
    );

    // Ödeme durumlarına göre rezervasyonlar
    const [paymentStats] = await db.query(`
      SELECT 
        payment_status,
        COUNT(*) as count,
        SUM(total_amount) as total
      FROM orders
      GROUP BY payment_status
    `);

    // Rezervasyon durumlarına göre
    const [bookingStats] = await db.query(`
      SELECT 
        booking_status,
        COUNT(*) as count
      FROM orders
      GROUP BY booking_status
    `);

    // Son 7 günlük rezervasyonlar
    const [recentReservations] = await db.query(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as count
      FROM orders
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `);

    // En çok rezervasyon alan turlar
    const [topTours] = await db.query(`
      SELECT 
        t.title,
        COUNT(o.id) as reservation_count
      FROM orders o
      LEFT JOIN tours t ON o.tour_id = t.id
      WHERE t.title IS NOT NULL
      GROUP BY o.tour_id, t.title
      ORDER BY reservation_count DESC
      LIMIT 5
    `);

    // Son 30 günlük unique ziyaretçi sayıları (günlük)
    const [visitorStats] = await db.query(`
      SELECT
        DATE(created_at) as date,
        COUNT(DISTINCT ip_address) as unique_visitors
      FROM visitor_logs
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `);

    // Toplam unique ziyaretçi (son 30 gün)
    const [totalVisitors] = await db.query(`
      SELECT COUNT(DISTINCT ip_address) as total
      FROM visitor_logs
      WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    `);

    // Bugünkü ziyaretçi
    const [todayVisitors] = await db.query(`
      SELECT COUNT(DISTINCT ip_address) as total
      FROM visitor_logs
      WHERE DATE(created_at) = CURDATE()
    `);

    res.json({
      stats: {
        totalTours,
        totalCategories,
        totalSubCategories,
        totalReservations: reservationsCount[0].count,
        totalBlogs: blogCount[0].count
      },
      paymentStats,
      bookingStats,
      recentReservations,
      topTours,
      visitorStats: {
        daily: visitorStats,
        total30Days: totalVisitors[0]?.total || 0,
        today: todayVisitors[0]?.total || 0
      }
    });
  } catch (error) {
    console.error('Dashboard stats error:', error);
    res.status(500).json({ message: 'Sunucu hatası' });
  }
});

module.exports = router;
