<?php
/**
 * Manager Reports API
 * Handles access to reports and analytics
 */

session_start();
require 'config.php';

header('Content-Type: application/json');

// Release any expired bookings before processing requests
releaseExpiredBookings($conn);

// Check manager authentication
if (!isset($_SESSION['manager_id'])) {
    http_response_code(401);
    sendResponse(false, 'Unauthorized access', null);
}

$manager_id = $_SESSION['manager_id'];
$action = isset($_GET['action']) ? $_GET['action'] : '';

switch ($action) {
    case 'daily_summary':
        getDailySummary();
        break;
    case 'revenue_report':
        getRevenueReport();
        break;
    case 'table_utilization':
        getTableUtilization();
        break;
    case 'server_performance':
        getServerPerformance();
        break;
    case 'payment_methods':
        getPaymentMethodsReport();
        break;
    case 'peak_hours':
        getPeakHoursReport();
        break;
    case 'menu_sales':
        getMenuSalesReport();
        break;
    case 'customer_traffic':
        getCustomerTrafficReport();
        break;
    case 'cancellations':
        getCancellationsReport();
        break;
    case 'reservation_stats':
        getReservationStats();
        break;
    case 'export_report':
        exportReport();
        break;
    default:
        sendResponse(false, 'Invalid action', null);
}

// Daily summary report
function getDailySummary() {
    global $conn;
    
    $date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
    $shift = isset($_GET['shift']) ? $_GET['shift'] : '';
    
    $summary = [];
    
    // Total revenue
    $revenue_sql = "SELECT SUM(total) as total_revenue, COUNT(id) as total_bills
                    FROM billings 
                    WHERE DATE(completed_at) = '" . $conn->real_escape_string($date) . "'
                    AND status = 'completed'";
    if ($shift) {
        $hour = ($shift === 'day') ? 'HOUR(completed_at) >= 10 AND HOUR(completed_at) < 17' :
                ($shift === 'evening' ? 'HOUR(completed_at) >= 17 AND HOUR(completed_at) < 23' :
                 'HOUR(completed_at) >= 23 OR HOUR(completed_at) < 10');
        $revenue_sql .= " AND " . $hour;
    }
    
    $revenue_result = $conn->query($revenue_sql);
    $revenue_data = $revenue_result->fetch_assoc();
    $summary['total_revenue'] = $revenue_data['total_revenue'] ?? 0;
    $summary['total_bills'] = $revenue_data['total_bills'] ?? 0;
    
    // Tables served
    $tables_sql = "SELECT COUNT(DISTINCT table_id) as tables_served
                   FROM billings 
                   WHERE DATE(completed_at) = '" . $conn->real_escape_string($date) . "'
                   AND status = 'completed'";
    $tables_result = $conn->query($tables_sql);
    $tables_data = $tables_result->fetch_assoc();
    $summary['tables_served'] = $tables_data['tables_served'] ?? 0;
    
    // Average bill
    $summary['average_bill'] = $summary['total_bills'] > 0 ? 
                               $summary['total_revenue'] / $summary['total_bills'] : 0;
    
    // Total items sold
    $items_sql = "SELECT SUM(quantity) as total_items
                  FROM billing_items bi
                  INNER JOIN billings b ON bi.billing_id = b.id
                  WHERE DATE(b.completed_at) = '" . $conn->real_escape_string($date) . "'
                  AND b.status = 'completed'";
    $items_result = $conn->query($items_sql);
    $items_data = $items_result->fetch_assoc();
    $summary['total_items'] = $items_data['total_items'] ?? 0;
    
    // Payment methods breakdown
    $payment_sql = "SELECT payment_method, COUNT(id) as count, SUM(total) as amount
                    FROM billings 
                    WHERE DATE(completed_at) = '" . $conn->real_escape_string($date) . "'
                    AND status = 'completed'
                    GROUP BY payment_method";
    $payment_result = $conn->query($payment_sql);
    $summary['payment_breakdown'] = [];
    while ($row = $payment_result->fetch_assoc()) {
        $summary['payment_breakdown'][] = $row;
    }
    
    sendResponse(true, 'Daily summary retrieved', $summary);
}

// Revenue report
function getRevenueReport() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    $sql = "SELECT DATE(completed_at) as date, 
            SUM(total) as revenue,
            COUNT(id) as bills,
            COUNT(DISTINCT table_id) as tables,
            AVG(total) as avg_bill
            FROM billings 
            WHERE DATE(completed_at) BETWEEN '" . $conn->real_escape_string($start_date) . "' 
                   AND '" . $conn->real_escape_string($end_date) . "'
            AND status = 'completed'
            GROUP BY DATE(completed_at)
            ORDER BY date DESC";
    
    $result = $conn->query($sql);
    
    $report = [];
    $total_revenue = 0;
    while ($row = $result->fetch_assoc()) {
        $report[] = $row;
        $total_revenue += $row['revenue'];
    }
    
    sendResponse(true, 'Revenue report retrieved', [
        'daily_data' => $report,
        'total_revenue' => $total_revenue,
        'period' => [
            'start' => $start_date,
            'end' => $end_date
        ]
    ]);
}

// Table utilization report
function getTableUtilization() {
    global $conn;
    
    $date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
    
    $sql = "SELECT 
            rt.id, rt.table_number, rt.base_capacity,
            COUNT(ta.id) as times_used,
            SUM(CASE WHEN ta.status = 'completed' THEN 1 ELSE 0 END) as completed_usage,
            SUM(CASE WHEN ta.status = 'assigned' THEN 1 ELSE 0 END) as current_usage
            FROM restaurant_tables rt
            LEFT JOIN table_assignments ta ON rt.id = ta.table_id 
                     AND DATE(ta.assigned_at) = '" . $conn->real_escape_string($date) . "'
            GROUP BY rt.id
            ORDER BY rt.floor_level, rt.section, rt.table_number";
    
    $result = $conn->query($sql);
    
    $utilization = [];
    while ($row = $result->fetch_assoc()) {
        $utilization[] = $row;
    }
    
    sendResponse(true, 'Table utilization retrieved', $utilization);
}

// Server performance report
function getServerPerformance() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    // Get all servers with their distinct assigned tables
    $sql = "SELECT 
            s.id,
            s.name,
            s.employee_id,
            s.rating as avg_rating,
            COUNT(DISTINCT ta.table_id) as tables_served,
            (SELECT COALESCE(SUM(b.total), 0)
             FROM billings b
             WHERE b.table_id IN (
                SELECT DISTINCT ta2.table_id 
                FROM table_assignments ta2 
                WHERE ta2.server_id = s.id
             )
             AND DATE(b.completed_at) BETWEEN '" . $conn->real_escape_string($start_date) . "' 
             AND '" . $conn->real_escape_string($end_date) . "'
             AND b.status = 'completed'
            ) as total_revenue_generated
            FROM servers s
            LEFT JOIN table_assignments ta ON s.id = ta.server_id
            GROUP BY s.id, s.name, s.employee_id, s.rating
            ORDER BY total_revenue_generated DESC";
    
    $result = $conn->query($sql);
    
    if (!$result) {
        sendResponse(false, 'Query error: ' . $conn->error, null);
        return;
    }
    
    $performance = [];
    while ($row = $result->fetch_assoc()) {
        $performance[] = $row;
    }
    
    sendResponse(true, 'Server performance retrieved', $performance);
}

// Payment methods report
function getPaymentMethodsReport() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    $sql = "SELECT 
            payment_method,
            COUNT(id) as transaction_count,
            SUM(total) as total_amount,
            AVG(total) as avg_transaction,
            SUM(CASE WHEN payment_status = 'completed' THEN 1 ELSE 0 END) as completed,
            SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) as pending
            FROM billings 
            WHERE DATE(created_at) BETWEEN '" . $conn->real_escape_string($start_date) . "'
                   AND '" . $conn->real_escape_string($end_date) . "'
            GROUP BY payment_method
            ORDER BY total_amount DESC";
    
    $result = $conn->query($sql);
    
    $payment_report = [];
    while ($row = $result->fetch_assoc()) {
        $payment_report[] = $row;
    }
    
    sendResponse(true, 'Payment methods report retrieved', $payment_report);
}

// Peak hours report
function getPeakHoursReport() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    $sql = "SELECT 
            HOUR(b.created_at) as hour,
            COUNT(b.id) as bills,
            SUM(b.total) as revenue,
            COUNT(DISTINCT b.table_id) as tables_used
            FROM billings b
            WHERE DATE(b.created_at) BETWEEN '" . $conn->real_escape_string($start_date) . "'
                   AND '" . $conn->real_escape_string($end_date) . "'
            GROUP BY HOUR(b.created_at)
            ORDER BY hour";
    
    $result = $conn->query($sql);
    
    $peak_hours = [];
    while ($row = $result->fetch_assoc()) {
        $peak_hours[] = $row;
    }
    
    sendResponse(true, 'Peak hours report retrieved', $peak_hours);
}

// Menu sales report
function getMenuSalesReport() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    $limit = isset($_GET['limit']) ? intval($_GET['limit']) : 20;
    
    $sql = "SELECT 
            bi.item_name,
            SUM(bi.quantity) as total_sold,
            SUM(bi.total_price) as total_revenue,
            AVG(bi.unit_price) as avg_price,
            COUNT(DISTINCT bi.billing_id) as times_ordered
            FROM billing_items bi
            INNER JOIN billings b ON bi.billing_id = b.id
            WHERE DATE(b.created_at) BETWEEN '" . $conn->real_escape_string($start_date) . "'
                   AND '" . $conn->real_escape_string($end_date) . "'
            AND b.status = 'completed'
            GROUP BY bi.item_name
            ORDER BY total_sold DESC
            LIMIT " . $limit;
    
    $result = $conn->query($sql);
    
    $menu_sales = [];
    while ($row = $result->fetch_assoc()) {
        $menu_sales[] = $row;
    }
    
    sendResponse(true, 'Menu sales report retrieved', $menu_sales);
}

// Customer traffic report
function getCustomerTrafficReport() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    $sql = "SELECT 
            DATE(b.created_at) as date,
            COUNT(DISTINCT b.table_id) as unique_tables,
            COUNT(b.id) as total_bills,
            SUM(1) as customer_count
            FROM billings b
            WHERE DATE(b.created_at) BETWEEN '" . $conn->real_escape_string($start_date) . "'
                   AND '" . $conn->real_escape_string($end_date) . "'
            GROUP BY DATE(b.created_at)
            ORDER BY date DESC";
    
    $result = $conn->query($sql);
    
    $customer_traffic = [];
    while ($row = $result->fetch_assoc()) {
        $customer_traffic[] = $row;
    }
    
    sendResponse(true, 'Customer traffic report retrieved', $customer_traffic);
}

// Cancellations and no-shows report
function getCancellationsReport() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    $sql = "SELECT 
            DATE(created_at) as date,
            COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled,
            COUNT(CASE WHEN status = 'no_show' THEN 1 END) as no_show,
            COUNT(id) as total_bookings
            FROM bookings 
            WHERE DATE(created_at) BETWEEN '" . $conn->real_escape_string($start_date) . "'
                   AND '" . $conn->real_escape_string($end_date) . "'
            GROUP BY DATE(created_at)
            ORDER BY date DESC";
    
    $result = $conn->query($sql);
    
    $cancellations = [];
    while ($row = $result->fetch_assoc()) {
        $cancellations[] = $row;
    }
    
    sendResponse(true, 'Cancellations report retrieved', $cancellations);
}

// Reservation statistics
function getReservationStats() {
    global $conn;
    
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    
    $stats = [];
    
    // Total bookings
    $bookings_sql = "SELECT COUNT(id) as total FROM bookings
                     WHERE booking_date BETWEEN '" . $conn->real_escape_string($start_date) . "'
                     AND '" . $conn->real_escape_string($end_date) . "'";
    $stats['total_bookings'] = $conn->query($bookings_sql)->fetch_assoc()['total'];
    
    // By status
    $status_sql = "SELECT status, COUNT(id) as count FROM bookings
                   WHERE booking_date BETWEEN '" . $conn->real_escape_string($start_date) . "'
                   AND '" . $conn->real_escape_string($end_date) . "'
                   GROUP BY status";
    $stats['by_status'] = [];
    $status_result = $conn->query($status_sql);
    while ($row = $status_result->fetch_assoc()) {
        $stats['by_status'][] = $row;
    }
    
    // Average party size
    $party_sql = "SELECT AVG(guests) as avg_party_size FROM bookings
                  WHERE booking_date BETWEEN '" . $conn->real_escape_string($start_date) . "'
                  AND '" . $conn->real_escape_string($end_date) . "'";
    $stats['avg_party_size'] = $conn->query($party_sql)->fetch_assoc()['avg_party_size'] ?? 0;
    
    sendResponse(true, 'Reservation statistics retrieved', $stats);
}

// Export report (prepare data for CSV/PDF export)
function exportReport() {
    global $conn;
    
    $report_type = isset($_GET['type']) ? $_GET['type'] : 'daily';
    $date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
    
    $data = [];
    
    if ($report_type === 'daily') {
        // Get daily data
        $sql = "SELECT b.billing_number, rt.table_number, b.subtotal, b.tax, b.discount, b.total, 
                b.payment_method, b.created_at
                FROM billings b
                INNER JOIN restaurant_tables rt ON b.table_id = rt.id
                WHERE DATE(b.created_at) = '" . $conn->real_escape_string($date) . "'
                ORDER BY b.created_at";
        
        $result = $conn->query($sql);
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
    }
    
    sendResponse(true, 'Report data exported', $data);
}
?>
