<?php
/**
 * 客户统计分析模块
 * 
 * 包含与客户相关的数据分析功能
 */

require_once 'statistics_utils.php';

/**
 * 获取客户类型分布
 * 
 * @param mysqli $conn 数据库连接
 * @return mysqli_result 客户类型分布数据结果集
 */
function getCustomerTypeDistribution($conn) {
    $sql = "SELECT 
                ct.businessType, 
                COUNT(c.id) as customer_count
            FROM customer c
            JOIN clienttype ct ON c.cs_type = ct.id
            GROUP BY c.cs_type";
    
    return $conn->query($sql);
}

/**
 * 获取成交阶段分布
 * 
 * @param mysqli $conn 数据库连接
 * @return mysqli_result 成交阶段分布数据结果集
 */
function getDealStageDistribution($conn) {
    $sql = "SELECT 
                cs_deal,
                CASE 
                    WHEN cs_deal = 1 THEN '背景调查'
                    WHEN cs_deal = 2 THEN '明确需求'
                    WHEN cs_deal = 3 THEN '已成交'
                    ELSE '其他'
                END as stage_name,
                COUNT(id) as customer_count
            FROM customer
            GROUP BY cs_deal";
    
    return $conn->query($sql);
}

/**
 * 获取客户增长趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param int $months 获取多少个月的数据，默认12个月
 * @return mysqli_result 客户增长趋势数据结果集
 */
function getCustomerGrowthTrend($conn, $months = 12) {
    $sql = "SELECT 
                DATE_FORMAT(cs_addtime, '%Y-%m') as month,
                COUNT(id) as new_customers
            FROM customer
            WHERE cs_addtime >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
            GROUP BY DATE_FORMAT(cs_addtime, '%Y-%m')
            ORDER BY month";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $months);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取新老客户订单分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 新老客户订单分析数据
 */
function getNewVsReturningCustomerOrders($conn, $start_date, $end_date) {
    // 获取选定日期范围内的订单
    $sql = "SELECT
                o.customer_id,
                COUNT(o.id) as order_count,
                SUM(o.total_amount) as total_amount,
                MIN(o.order_date) as first_order_date,
                MAX(c.cs_addtime) as customer_addtime
            FROM orders o
            JOIN customer c ON o.customer_id = c.id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY o.customer_id";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $new_customers = 0;
    $returning_customers = 0;
    $new_customer_amount = 0;
    $returning_customer_amount = 0;
    
    while ($row = $result->fetch_assoc()) {
        // 查找之前是否有订单
        $prev_sql = "SELECT id FROM orders 
                     WHERE customer_id = ? 
                     AND order_date < ? 
                     LIMIT 1";
        
        $prev_stmt = $conn->prepare($prev_sql);
        $prev_stmt->bind_param("is", $row['customer_id'], $start_date);
        $prev_stmt->execute();
        $prev_result = $prev_stmt->get_result();
        
        if ($prev_result->num_rows > 0) {
            // 老客户
            $returning_customers++;
            $returning_customer_amount += $row['total_amount'];
        } else {
            // 新客户
            $new_customers++;
            $new_customer_amount += $row['total_amount'];
        }
    }
    
    return [
        'new_customers' => $new_customers,
        'returning_customers' => $returning_customers,
        'new_customer_amount' => $new_customer_amount,
        'returning_customer_amount' => $returning_customer_amount,
        'total_customers' => $new_customers + $returning_customers,
        'total_amount' => $new_customer_amount + $returning_customer_amount
    ];
}

/**
 * 渲染客户类型分布图
 * 
 * @param array $type_labels 类型标签
 * @param array $type_data 类型数据
 * @return void
 */
function renderCustomerTypeChart($type_labels, $type_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户类型分布</h2>
        </div>
        <canvas id="customerTypeChart"></canvas>
    </div>
    
    <script>
        // 客户类型分布图
        var customerTypeCtx = document.getElementById('customerTypeChart').getContext('2d');
        var customerTypeChart = new Chart(customerTypeCtx, {
            type: 'doughnut',
            data: {
                labels: <?php echo json_encode($type_labels); ?>,
                datasets: [{
                    data: <?php echo json_encode($type_data); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(153, 102, 255, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染成交阶段分布图
 * 
 * @param array $stage_labels 阶段标签
 * @param array $stage_data 阶段数据
 * @return void
 */
function renderDealStageChart($stage_labels, $stage_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">成交阶段分布</h2>
        </div>
        <canvas id="dealStageChart"></canvas>
    </div>
    
    <script>
        // 成交阶段分布图
        var dealStageCtx = document.getElementById('dealStageChart').getContext('2d');
        var dealStageChart = new Chart(dealStageCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($stage_labels); ?>,
                datasets: [{
                    label: '客户数量',
                    data: <?php echo json_encode($stage_data); ?>,
                    backgroundColor: [
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染客户增长趋势图
 * 
 * @param array $growth_labels 增长标签
 * @param array $growth_data 增长数据
 * @return void
 */
function renderCustomerGrowthChart($growth_labels, $growth_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户增长趋势</h2>
        </div>
        <canvas id="customerGrowthChart"></canvas>
    </div>
    
    <script>
        // 客户增长趋势图
        var customerGrowthCtx = document.getElementById('customerGrowthChart').getContext('2d');
        var customerGrowthChart = new Chart(customerGrowthCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($growth_labels); ?>,
                datasets: [{
                    label: '新增客户',
                    data: <?php echo json_encode($growth_data); ?>,
                    backgroundColor: 'rgba(75, 192, 192, 0.2)',
                    borderColor: 'rgba(75, 192, 192, 1)',
                    borderWidth: 2,
                    tension: 0.1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染新老客户分析图
 * 
 * @param array $new_vs_returning 新老客户数据
 * @return void
 */
function renderNewVsReturningCustomersChart($new_vs_returning) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">新老客户分析</h2>
        </div>
        <style>
            .pie-charts-container {
                display: flex;
                flex-direction: row;
                justify-content: space-between;
                margin-bottom: 20px;
            }
            .pie-chart-wrapper {
                flex: 0 0 48%;
                max-width: 48%;
            }
            .customer-stats-summary {
                margin-top: 20px;
                padding: 15px;
                background-color: #f9f9f9;
                border-radius: 5px;
            }
            .stats-row {
                display: flex;
                margin-bottom: 15px;
            }
            .stat-item {
                flex: 1;
                padding: 0 10px;
            }
            .stat-label {
                display: block;
                font-weight: bold;
                margin-bottom: 5px;
                color: #555;
            }
            .stat-value {
                font-size: 16px;
                color: #333;
            }
        </style>
        <div class="pie-charts-container">
            <div class="pie-chart-wrapper">
                <h3 style="text-align: center; margin-bottom: 15px;">客户数量分布</h3>
                <canvas id="newVsReturningCustomersChart"></canvas>
            </div>
            <div class="pie-chart-wrapper">
                <h3 style="text-align: center; margin-bottom: 15px;">销售额分布</h3>
                <canvas id="newVsReturningAmountChart"></canvas>
            </div>
        </div>
        <div class="customer-stats-summary">
            <div class="stats-row">
                <div class="stat-item">
                    <span class="stat-label">总客户数:</span>
                    <span class="stat-value"><?php echo number_format($new_vs_returning['total_customers']); ?></span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">新客户:</span>
                    <span class="stat-value"><?php echo number_format($new_vs_returning['new_customers']); ?> 
                    (<?php echo ($new_vs_returning['total_customers'] > 0) ? number_format(($new_vs_returning['new_customers'] / $new_vs_returning['total_customers']) * 100, 1) : '0'; ?>%)</span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">老客户:</span>
                    <span class="stat-value"><?php echo number_format($new_vs_returning['returning_customers']); ?> 
                    (<?php echo ($new_vs_returning['total_customers'] > 0) ? number_format(($new_vs_returning['returning_customers'] / $new_vs_returning['total_customers']) * 100, 1) : '0'; ?>%)</span>
                </div>
            </div>
            <div class="stats-row">
                <div class="stat-item">
                    <span class="stat-label">总销售额:</span>
                    <span class="stat-value">¥<?php echo number_format($new_vs_returning['total_amount'], 2); ?></span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">新客户销售额:</span>
                    <span class="stat-value">¥<?php echo number_format($new_vs_returning['new_customer_amount'], 2); ?> 
                    (<?php echo ($new_vs_returning['total_amount'] > 0) ? number_format(($new_vs_returning['new_customer_amount'] / $new_vs_returning['total_amount']) * 100, 1) : '0'; ?>%)</span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">老客户销售额:</span>
                    <span class="stat-value">¥<?php echo number_format($new_vs_returning['returning_customer_amount'], 2); ?> 
                    (<?php echo ($new_vs_returning['total_amount'] > 0) ? number_format(($new_vs_returning['returning_customer_amount'] / $new_vs_returning['total_amount']) * 100, 1) : '0'; ?>%)</span>
                </div>
            </div>
        </div>
    </div>
    
    <script>
        // 新老客户数量图
        var newVsReturningCtx = document.getElementById('newVsReturningCustomersChart').getContext('2d');
        var newVsReturningChart = new Chart(newVsReturningCtx, {
            type: 'pie',
            data: {
                labels: ['新客户', '老客户'],
                datasets: [{
                    data: [
                        <?php echo $new_vs_returning['new_customers']; ?>, 
                        <?php echo $new_vs_returning['returning_customers']; ?>
                    ],
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    }
                }
            }
        });
        
        // 新老客户销售额图
        var amountCtx = document.getElementById('newVsReturningAmountChart').getContext('2d');
        var amountChart = new Chart(amountCtx, {
            type: 'pie',
            data: {
                labels: ['新客户销售额', '老客户销售额'],
                datasets: [{
                    data: [
                        <?php echo $new_vs_returning['new_customer_amount']; ?>, 
                        <?php echo $new_vs_returning['returning_customer_amount']; ?>
                    ],
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 获取客户总数
 * 
 * @param mysqli $conn 数据库连接
 * @return int 客户总数
 */
function getTotalCustomers($conn) {
    $sql = "SELECT COUNT(id) as total FROM customer";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
    return $row['total'];
}

/**
 * 获取指定时间段内新增客户数
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return int 新增客户数
 */
function getNewCustomers($conn, $start_date, $end_date) {
    $sql = "SELECT COUNT(id) as new_count 
            FROM customer 
            WHERE cs_addtime BETWEEN ? AND ?";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    return $row['new_count'];
}

/**
 * 计算平均客户价值（客户平均订单金额）
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return float 平均客户价值
 */
function getAverageCustomerValue($conn, $start_date, $end_date) {
    $sql = "SELECT AVG(customer_value) as avg_value FROM (
                SELECT 
                    o.customer_id,
                    SUM(o.total_amount) as customer_value
                FROM orders o
                WHERE o.order_date BETWEEN ? AND ?
                GROUP BY o.customer_id
            ) as customer_values";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    return $row['avg_value'] ? $row['avg_value'] : 0;
}

/**
 * 计算客户留存率
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 客户留存率数据
 */
function getCustomerRetentionRate($conn, $start_date, $end_date) {
    // 获取之前时间段的客户
    $previous_start = date('Y-m-d', strtotime('-1 year', strtotime($start_date)));
    $previous_end = date('Y-m-d', strtotime('-1 day', strtotime($start_date)));
    
    // 之前时间段的客户ID
    $prev_sql = "SELECT DISTINCT customer_id 
                FROM orders 
                WHERE order_date BETWEEN ? AND ?";
    
    $prev_stmt = $conn->prepare($prev_sql);
    $prev_stmt->bind_param("ss", $previous_start, $previous_end);
    $prev_stmt->execute();
    $prev_result = $prev_stmt->get_result();
    
    $previous_customers = [];
    while ($row = $prev_result->fetch_assoc()) {
        $previous_customers[] = $row['customer_id'];
    }
    
    $previous_count = count($previous_customers);
    
    // 如果没有之前的客户，返回0
    if ($previous_count == 0) {
        return [
            'retained_count' => 0,
            'total_previous' => 0,
            'retention_rate' => 0
        ];
    }
    
    // 查询当前时间段内，之前客户中再次购买的客户数
    $current_sql = "SELECT COUNT(DISTINCT customer_id) as retained_count
                   FROM orders
                   WHERE order_date BETWEEN ? AND ?
                   AND customer_id IN (" . implode(',', $previous_customers) . ")";
    
    $current_stmt = $conn->prepare($current_sql);
    $current_stmt->bind_param("ss", $start_date, $end_date);
    $current_stmt->execute();
    $current_result = $current_stmt->get_result();
    $row = $current_result->fetch_assoc();
    
    $retained_count = $row['retained_count'];
    $retention_rate = ($retained_count / $previous_count) * 100;
    
    return [
        'retained_count' => $retained_count,
        'total_previous' => $previous_count,
        'retention_rate' => $retention_rate
    ];
}

/**
 * 计算下单转换率
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 下单转换率数据
 */
function getOrderConversionRate($conn, $start_date, $end_date) {
    // 获取指定时间段内总客户数
    $total_sql = "SELECT COUNT(DISTINCT id) as total_count FROM customer WHERE cs_addtime <= ?";
    $total_stmt = $conn->prepare($total_sql);
    $total_stmt->bind_param("s", $end_date);
    $total_stmt->execute();
    $total_result = $total_stmt->get_result();
    $total_row = $total_result->fetch_assoc();
    $total_customers = $total_row['total_count'];
    
    // 获取有订单的客户数
    $order_sql = "SELECT COUNT(DISTINCT customer_id) as order_count 
                 FROM orders 
                 WHERE order_date BETWEEN ? AND ?";
    $order_stmt = $conn->prepare($order_sql);
    $order_stmt->bind_param("ss", $start_date, $end_date);
    $order_stmt->execute();
    $order_result = $order_stmt->get_result();
    $order_row = $order_result->fetch_assoc();
    $customers_with_orders = $order_row['order_count'];
    
    // 计算转换率
    $conversion_rate = ($total_customers > 0) ? ($customers_with_orders / $total_customers) * 100 : 0;
    
    return [
        'total_customers' => $total_customers,
        'customers_with_orders' => $customers_with_orders,
        'conversion_rate' => $conversion_rate
    ];
}

/**
 * 渲染关键指标仪表板
 * 
 * @param array $kpi_data 关键指标数据
 * @return void
 */
function renderKeyMetricsCard($kpi_data) {
    ?>
    <div class="stats-card-container">
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>客户总数</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($kpi_data['total_customers']); ?></div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>新增客户</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($kpi_data['new_customers']); ?></div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>平均客户价值</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value">¥<?php echo number_format($kpi_data['avg_customer_value'], 2); ?></div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>客户留存率</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($kpi_data['retention_rate'], 1); ?>%</div>
                <div class="stats-card-subtitle"><?php echo number_format($kpi_data['retained_count']); ?> / <?php echo number_format($kpi_data['total_previous']); ?></div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>下单转换率</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($kpi_data['conversion_rate'], 1); ?>%</div>
                <div class="stats-card-subtitle"><?php echo number_format($kpi_data['customers_with_orders']); ?> / <?php echo number_format($kpi_data['total_customers']); ?></div>
            </div>
        </div>
    </div>
    <?php
}

/**
 * 获取客户价值分布数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 客户价值分布数据
 */
function getCustomerValueDistribution($conn, $start_date, $end_date) {
    $sql = "SELECT 
                value_segment,
                COUNT(customer_id) as customer_count,
                SUM(total_amount) as total_amount
            FROM (
                SELECT 
                    o.customer_id,
                    SUM(o.total_amount) as total_amount,
                    CASE 
                        WHEN SUM(o.total_amount) > 100000 THEN '高价值客户(>10万)'
                        WHEN SUM(o.total_amount) > 50000 THEN '中高价值客户(5-10万)'
                        WHEN SUM(o.total_amount) > 10000 THEN '中价值客户(1-5万)'
                        WHEN SUM(o.total_amount) > 5000 THEN '低价值客户(5千-1万)'
                        ELSE '微价值客户(<5千)'
                    END as value_segment
                FROM orders o
                WHERE o.order_date BETWEEN ? AND ?
                GROUP BY o.customer_id
            ) as customer_value
            GROUP BY value_segment
            ORDER BY 
                CASE value_segment
                    WHEN '高价值客户(>10万)' THEN 1
                    WHEN '中高价值客户(5-10万)' THEN 2
                    WHEN '中价值客户(1-5万)' THEN 3
                    WHEN '低价值客户(5千-1万)' THEN 4
                    ELSE 5
                END";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $value_segments = [];
    $customer_counts = [];
    $total_amounts = [];
    $total_customers = 0;
    
    while ($row = $result->fetch_assoc()) {
        $value_segments[] = $row['value_segment'];
        $customer_counts[] = $row['customer_count'];
        $total_amounts[] = $row['total_amount'];
        $total_customers += $row['customer_count'];
    }
    
    return [
        'segments' => $value_segments,
        'counts' => $customer_counts,
        'amounts' => $total_amounts,
        'total_customers' => $total_customers
    ];
}

/**
 * 获取客户活跃度分析数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $end_date 截止日期
 * @return array 客户活跃度分析数据
 */
function getCustomerActivityAnalysis($conn, $end_date) {
    $sql = "SELECT 
                activity_level,
                COUNT(*) as customer_count
            FROM (
                SELECT 
                    o.customer_id,
                    CASE 
                        WHEN DATEDIFF(?, MAX(o.order_date)) <= 30 THEN '活跃客户(30天内)'
                        WHEN DATEDIFF(?, MAX(o.order_date)) <= 90 THEN '一般活跃(90天内)'
                        WHEN DATEDIFF(?, MAX(o.order_date)) <= 180 THEN '低活跃(180天内)'
                        WHEN DATEDIFF(?, MAX(o.order_date)) <= 365 THEN '沉睡客户(1年内)'
                        ELSE '流失客户(超过1年)'
                    END as activity_level
                FROM orders o
                GROUP BY o.customer_id
            ) as customer_activity
            GROUP BY activity_level
            ORDER BY 
                CASE activity_level
                    WHEN '活跃客户(30天内)' THEN 1
                    WHEN '一般活跃(90天内)' THEN 2
                    WHEN '低活跃(180天内)' THEN 3
                    WHEN '沉睡客户(1年内)' THEN 4
                    ELSE 5
                END";
    
    $stmt = $conn->prepare($sql);
    $end_date_formatted = date('Y-m-d', strtotime($end_date));
    $stmt->bind_param("ssss", $end_date_formatted, $end_date_formatted, $end_date_formatted, $end_date_formatted);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $activity_levels = [];
    $customer_counts = [];
    
    while ($row = $result->fetch_assoc()) {
        $activity_levels[] = $row['activity_level'];
        $customer_counts[] = $row['customer_count'];
    }
    
    return [
        'levels' => $activity_levels,
        'counts' => $customer_counts
    ];
}

/**
 * 获取客户流失风险分析数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $end_date 截止日期
 * @return array 客户流失风险分析数据
 */
function getCustomerChurnRiskAnalysis($conn, $end_date) {
    $sql = "SELECT 
                risk_level,
                COUNT(*) as customer_count
            FROM (
                SELECT 
                    c.id,
                    CASE 
                        WHEN last_order_date IS NULL THEN '从未购买'
                        WHEN DATEDIFF(?, last_order_date) <= 90 THEN '低风险(90天内)'
                        WHEN DATEDIFF(?, last_order_date) <= 180 THEN '中风险(90-180天)'
                        WHEN DATEDIFF(?, last_order_date) <= 365 THEN '高风险(180-365天)'
                        ELSE '极高风险(超过1年)'
                    END as risk_level
                FROM customer c
                LEFT JOIN (
                    SELECT customer_id, MAX(order_date) as last_order_date
                    FROM orders
                    GROUP BY customer_id
                ) o ON c.id = o.customer_id
            ) as customer_risk
            GROUP BY risk_level
            ORDER BY 
                CASE risk_level
                    WHEN '低风险(90天内)' THEN 1
                    WHEN '中风险(90-180天)' THEN 2
                    WHEN '高风险(180-365天)' THEN 3
                    WHEN '极高风险(超过1年)' THEN 4
                    WHEN '从未购买' THEN 5
                END";
    
    $stmt = $conn->prepare($sql);
    $end_date_formatted = date('Y-m-d', strtotime($end_date));
    $stmt->bind_param("sss", $end_date_formatted, $end_date_formatted, $end_date_formatted);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $risk_levels = [];
    $customer_counts = [];
    
    while ($row = $result->fetch_assoc()) {
        $risk_levels[] = $row['risk_level'];
        $customer_counts[] = $row['customer_count'];
    }
    
    return [
        'levels' => $risk_levels,
        'counts' => $customer_counts
    ];
}

/**
 * 获取客户来源分析数据
 * 
 * @param mysqli $conn 数据库连接
 * @return array 客户来源分析数据
 */
function getCustomerSourceAnalysis($conn) {
    // 假设cs_from字段代表客户来源，需要根据实际情况调整SQL
    $sql = "SELECT 
                source,
                COUNT(*) as customer_count
            FROM (
                SELECT 
                    id,
                    CASE 
                        WHEN cs_from = 1 THEN '网站注册'
                        WHEN cs_from = 2 THEN '销售开发'
                        WHEN cs_from = 3 THEN '广告引流'
                        WHEN cs_from = 4 THEN '展会获取'
                        WHEN cs_from = 5 THEN '客户推荐'
                        ELSE '其他来源'
                    END as source
                FROM customer
            ) as customer_source
            GROUP BY source
            ORDER BY customer_count DESC";
    
    $result = $conn->query($sql);
    
    $sources = [];
    $counts = [];
    
    while ($row = $result->fetch_assoc()) {
        $sources[] = $row['source'];
        $counts[] = $row['customer_count'];
    }
    
    return [
        'sources' => $sources,
        'counts' => $counts
    ];
}

/**
 * 渲染客户价值分布图表
 * 
 * @param array $value_data 客户价值分布数据
 * @return void
 */
function renderCustomerValueCharts($value_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户价值分布</h2>
        </div>
        
        <div class="chart-row" style="display: flex; width: 100%; margin: 0 -10px;">
            <div class="chart-column" style="flex: 0 0 50%; max-width: 50%; padding: 0 10px; box-sizing: border-box;">
                <h3 style="text-align: center; margin-bottom: 15px;">客户价值分布(柱状图)</h3>
                <canvas id="customerValueBarChart"></canvas>
            </div>
            <div class="chart-column" style="flex: 0 0 50%; max-width: 50%; padding: 0 10px; box-sizing: border-box;">
                <h3 style="text-align: center; margin-bottom: 15px;">客户价值分布(饼图)</h3>
                <canvas id="customerValuePieChart"></canvas>
            </div>
        </div>
        
        <div class="customer-stats-summary">
            <div class="stats-row">
                <?php foreach ($value_data['segments'] as $index => $segment): ?>
                <div class="stat-item">
                    <span class="stat-label"><?php echo $segment; ?>:</span>
                    <span class="stat-value"><?php echo number_format($value_data['counts'][$index]); ?> 
                    (<?php echo ($value_data['total_customers'] > 0) ? 
                        number_format(($value_data['counts'][$index] / $value_data['total_customers']) * 100, 1) : '0'; ?>%)</span>
                    <span class="stat-sub-value">¥<?php echo number_format($value_data['amounts'][$index], 2); ?></span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
    
    <script>
        // 客户价值分布柱状图
        var valueBarCtx = document.getElementById('customerValueBarChart').getContext('2d');
        var valueBarChart = new Chart(valueBarCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($value_data['segments']); ?>,
                datasets: [{
                    label: '客户数量',
                    data: <?php echo json_encode($value_data['counts']); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(153, 102, 255, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                }
            }
        });
        
        // 客户价值分布饼图
        var valuePieCtx = document.getElementById('customerValuePieChart').getContext('2d');
        var valuePieChart = new Chart(valuePieCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($value_data['segments']); ?>,
                datasets: [{
                    data: <?php echo json_encode($value_data['counts']); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(153, 102, 255, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true
            }
        });
    </script>
    <?php
}

/**
 * 渲染客户活跃度分析图表
 * 
 * @param array $activity_data 客户活跃度数据
 * @return void
 */
function renderCustomerActivityChart($activity_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户活跃度分析</h2>
        </div>
        <canvas id="customerActivityChart"></canvas>
        
        <div class="customer-stats-summary">
            <div class="stats-row">
                <?php 
                $total_customers = array_sum($activity_data['counts']);
                foreach ($activity_data['levels'] as $index => $level): 
                ?>
                <div class="stat-item">
                    <span class="stat-label"><?php echo $level; ?>:</span>
                    <span class="stat-value"><?php echo number_format($activity_data['counts'][$index]); ?> 
                    (<?php echo ($total_customers > 0) ? 
                        number_format(($activity_data['counts'][$index] / $total_customers) * 100, 1) : '0'; ?>%)</span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
    
    <script>
        // 客户活跃度分析图
        var activityCtx = document.getElementById('customerActivityChart').getContext('2d');
        var activityChart = new Chart(activityCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($activity_data['levels']); ?>,
                datasets: [{
                    label: '客户数量',
                    data: <?php echo json_encode($activity_data['counts']); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(153, 102, 255, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染客户流失风险分析图表
 * 
 * @param array $risk_data 客户流失风险数据
 * @return void
 */
function renderCustomerChurnRiskChart($risk_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户流失风险分析</h2>
        </div>
        <canvas id="customerRiskChart"></canvas>
        
        <div class="customer-stats-summary">
            <div class="stats-row">
                <?php 
                $total_customers = array_sum($risk_data['counts']);
                foreach ($risk_data['levels'] as $index => $level): 
                ?>
                <div class="stat-item">
                    <span class="stat-label"><?php echo $level; ?>:</span>
                    <span class="stat-value"><?php echo number_format($risk_data['counts'][$index]); ?> 
                    (<?php echo ($total_customers > 0) ? 
                        number_format(($risk_data['counts'][$index] / $total_customers) * 100, 1) : '0'; ?>%)</span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
    
    <script>
        // 客户流失风险分析图
        var riskCtx = document.getElementById('customerRiskChart').getContext('2d');
        var riskChart = new Chart(riskCtx, {
            type: 'doughnut',
            data: {
                labels: <?php echo json_encode($risk_data['levels']); ?>,
                datasets: [{
                    data: <?php echo json_encode($risk_data['counts']); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(153, 102, 255, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染客户来源分析图表
 * 
 * @param array $source_data 客户来源数据
 * @return void
 */
function renderCustomerSourceChart($source_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户来源分析</h2>
        </div>
        <canvas id="customerSourceChart"></canvas>
        
        <div class="customer-stats-summary">
            <div class="stats-row">
                <?php 
                $total_customers = array_sum($source_data['counts']);
                foreach ($source_data['sources'] as $index => $source): 
                ?>
                <div class="stat-item">
                    <span class="stat-label"><?php echo $source; ?>:</span>
                    <span class="stat-value"><?php echo number_format($source_data['counts'][$index]); ?> 
                    (<?php echo ($total_customers > 0) ? 
                        number_format(($source_data['counts'][$index] / $total_customers) * 100, 1) : '0'; ?>%)</span>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
    
    <script>
        // 客户来源分析图
        var sourceCtx = document.getElementById('customerSourceChart').getContext('2d');
        var sourceChart = new Chart(sourceCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($source_data['sources']); ?>,
                datasets: [{
                    data: <?php echo json_encode($source_data['counts']); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(153, 102, 255, 0.7)',
                        'rgba(255, 159, 64, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 获取客户转化漏斗数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 客户转化漏斗数据
 */
function getCustomerConversionFunnel($conn, $start_date, $end_date) {
    // 获取总客户数（潜在客户）
    $total_sql = "SELECT COUNT(id) as total FROM customer";
    $total_result = $conn->query($total_sql);
    $total_row = $total_result->fetch_assoc();
    $total_customers = $total_row['total'];
    
    // 获取明确需求的客户数
    $needs_sql = "SELECT COUNT(id) as needs_count FROM customer WHERE cs_deal = 2";
    $needs_result = $conn->query($needs_sql);
    $needs_row = $needs_result->fetch_assoc();
    $needs_customers = $needs_row['needs_count'];
    
    // 获取已成交客户数
    $deal_sql = "SELECT COUNT(id) as deal_count FROM customer WHERE cs_deal = 3";
    $deal_result = $conn->query($deal_sql);
    $deal_row = $deal_result->fetch_assoc();
    $deal_customers = $deal_row['deal_count'];
    
    // 获取有订单的客户数
    $order_sql = "SELECT COUNT(DISTINCT customer_id) as order_count FROM orders WHERE order_date BETWEEN ? AND ?";
    $order_stmt = $conn->prepare($order_sql);
    $order_stmt->bind_param("ss", $start_date, $end_date);
    $order_stmt->execute();
    $order_result = $order_stmt->get_result();
    $order_row = $order_result->fetch_assoc();
    $order_customers = $order_row['order_count'];
    
    // 获取复购客户数（多次下单）
    $repeat_sql = "SELECT COUNT(customer_id) as repeat_count FROM (
                        SELECT customer_id, COUNT(id) as order_count 
                        FROM orders 
                        WHERE order_date BETWEEN ? AND ?
                        GROUP BY customer_id
                        HAVING order_count > 1
                    ) as repeat_customers";
    $repeat_stmt = $conn->prepare($repeat_sql);
    $repeat_stmt->bind_param("ss", $start_date, $end_date);
    $repeat_stmt->execute();
    $repeat_result = $repeat_stmt->get_result();
    $repeat_row = $repeat_result->fetch_assoc();
    $repeat_customers = $repeat_row['repeat_count'];
    
    return [
        'stages' => ['潜在客户', '明确需求', '已成交', '有效订单', '复购客户'],
        'counts' => [$total_customers, $needs_customers, $deal_customers, $order_customers, $repeat_customers]
    ];
}

/**
 * 渲染客户转化漏斗图表
 * 
 * @param array $funnel_data 客户转化漏斗数据
 * @return void
 */
function renderCustomerFunnelChart($funnel_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户转化漏斗</h2>
        </div>
        <canvas id="customerFunnelChart" style="max-height: 400px;"></canvas>
        
        <div class="customer-stats-summary">
            <div class="stats-row">
                <?php 
                foreach ($funnel_data['stages'] as $index => $stage): 
                    $current_count = $funnel_data['counts'][$index];
                    $prev_count = $index > 0 ? $funnel_data['counts'][$index-1] : $current_count;
                    $conversion_rate = $prev_count > 0 ? ($current_count / $prev_count) * 100 : 0;
                ?>
                <div class="stat-item">
                    <span class="stat-label"><?php echo $stage; ?>:</span>
                    <span class="stat-value"><?php echo number_format($current_count); ?></span>
                    <?php if ($index > 0): ?>
                        <span class="stat-conversion">
                            转化率: <?php echo number_format($conversion_rate, 1); ?>%
                        </span>
                    <?php endif; ?>
                </div>
                <?php endforeach; ?>
            </div>
        </div>
    </div>
    
    <script>
        // 客户转化漏斗图
        var funnelCtx = document.getElementById('customerFunnelChart').getContext('2d');
        var funnelChart = new Chart(funnelCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($funnel_data['stages']); ?>,
                datasets: [{
                    label: '客户数量',
                    data: <?php echo json_encode($funnel_data['counts']); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(153, 102, 255, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                indexAxis: 'y',
                responsive: true,
                scales: {
                    x: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                },
                plugins: {
                    tooltip: {
                        callbacks: {
                            afterLabel: function(context) {
                                var index = context.dataIndex;
                                if (index > 0) {
                                    var currentValue = context.parsed.x;
                                    var previousValue = context.dataset.data[index-1];
                                    var conversionRate = previousValue > 0 ? (currentValue / previousValue * 100).toFixed(1) : 0;
                                    return '转化率: ' + conversionRate + '%';
                                }
                                return '';
                            }
                        }
                    }
                }
            }
        });
    </script>
    <?php
}