<?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
// Accept manager_id from session or from GET parameter (for client-side authentication)
$manager_id = null;

if (isset($_SESSION['manager_id'])) {
    $manager_id = $_SESSION['manager_id'];
} elseif (isset($_GET['manager_id'])) {
    $manager_id = intval($_GET['manager_id']);
    // Validate the manager exists
    $validateRes = $conn->query("SELECT id FROM managers WHERE id = " . $manager_id . " LIMIT 1");
    if (!$validateRes || $validateRes->num_rows === 0) {
        http_response_code(401);
        sendResponse(false, 'Unauthorized access', null);
        exit;
    }
} else {
    http_response_code(401);
    sendResponse(false, 'Unauthorized access', null);
    exit;
}
$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');

    $safe_start_date = $conn->real_escape_string($start_date);
    $safe_end_date = $conn->real_escape_string($end_date);

    $has_dine_in_order_id = false;
    $has_assignment_id = false;
    $has_source_assignment_id = false;

    $check = $conn->query("SHOW COLUMNS FROM billings LIKE 'dine_in_order_id'");
    if ($check && $check->num_rows > 0) {
        $has_dine_in_order_id = true;
    }

    $check = $conn->query("SHOW COLUMNS FROM billings LIKE 'assignment_id'");
    if ($check && $check->num_rows > 0) {
        $has_assignment_id = true;
    }

    $check = $conn->query("SHOW COLUMNS FROM billings LIKE 'source_assignment_id'");
    if ($check && $check->num_rows > 0) {
        $has_source_assignment_id = true;
    }

    if ($has_dine_in_order_id) {
        // Correct attribution: revenue belongs to the server who owns the dine-in order.
        $sql = "SELECT
                s.id,
                s.name,
                s.employee_id,
                s.rating AS avg_rating,
                COALESCE(sp.tables_served, 0) AS tables_served,
                COALESCE(sp.total_revenue_generated, 0) AS total_revenue_generated
                FROM servers s
                LEFT JOIN (
                    SELECT
                        o.server_id,
                        COUNT(DISTINCT COALESCE(b.table_id, o.table_id)) AS tables_served,
                        COALESCE(SUM(b.total), 0) AS total_revenue_generated
                    FROM billings b
                    INNER JOIN dine_in_orders o ON b.dine_in_order_id = o.id
                    WHERE DATE(b.completed_at) BETWEEN '" . $safe_start_date . "' AND '" . $safe_end_date . "'
                      AND b.status = 'completed'
                    GROUP BY o.server_id
                ) sp ON sp.server_id = s.id
                ORDER BY total_revenue_generated DESC, s.name ASC";
    } else {
        // Fallback for older schema: map billings to assignments when possible.
        $assignment_match = [];
        if ($has_assignment_id) {
            $assignment_match[] = "b.assignment_id = ta.id";
        }
        if ($has_source_assignment_id) {
            $assignment_match[] = "b.source_assignment_id = ta.id";
        }

        if (!empty($assignment_match)) {
            $join_condition = implode(' OR ', $assignment_match);
            $sql = "SELECT
                    s.id,
                    s.name,
                    s.employee_id,
                    s.rating AS avg_rating,
                    COALESCE(sp.tables_served, 0) AS tables_served,
                    COALESCE(sp.total_revenue_generated, 0) AS total_revenue_generated
                    FROM servers s
                    LEFT JOIN (
                        SELECT
                            ta.server_id,
                            COUNT(DISTINCT b.table_id) AS tables_served,
                            COALESCE(SUM(b.total), 0) AS total_revenue_generated
                        FROM table_assignments ta
                        INNER JOIN billings b ON (" . $join_condition . ")
                        WHERE DATE(b.completed_at) BETWEEN '" . $safe_start_date . "' AND '" . $safe_end_date . "'
                          AND b.status = 'completed'
                        GROUP BY ta.server_id
                    ) sp ON sp.server_id = s.id
                    ORDER BY total_revenue_generated DESC, s.name ASC";
        } else {
            // No reliable link from billing to server in this schema version.
            $sql = "SELECT
                    s.id,
                    s.name,
                    s.employee_id,
                    s.rating AS avg_rating,
                    0 AS tables_served,
                    0 AS total_revenue_generated
                    FROM servers s
                    ORDER BY s.name ASC";
        }
    }
    
    $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);
}
?>
