<?php
include 'config.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Age Analysis Report</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            background-color: #f5f5f5;
        }
        .container {
            max-width: 1200px;
            margin: 0 auto;
            background: white;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        .filters {
            background: #f8f9fa;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .filter-group {
            display: inline-block;
            margin-right: 20px;
        }
        label {
            font-weight: bold;
            margin-right: 5px;
        }
        select, input {
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        button {
            background: #007bff;
            color: white;
            padding: 8px 15px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        button:hover {
            background: #0056b3;
        }
        .charts-container {
            display: flex;
            flex-wrap: wrap;
            gap: 20px;
            margin: 20px 0;
        }
        .chart-box {
            flex: 1;
            min-width: 300px;
            background: white;
            padding: 15px;
            border-radius: 5px;
            box-shadow: 0 0 5px rgba(0,0,0,0.1);
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 10px;
            text-align: left;
        }
        th {
            background: #007bff;
            color: white;
        }
        tr:nth-child(even) {
            background: #f9f9f9;
        }
        .summary {
            background: #e7f3ff;
            padding: 15px;
            border-radius: 5px;
            margin: 15px 0;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Age Analysis Report</h1>
        
        <!-- Filter Section -->
        <div class="filters">
            <form method="GET" action="">
                <div class="filter-group">
                    <label for="sal_ex">Sales Executive:</label>
                    <select name="sal_ex" id="sal_ex">
                        <option value="">All Sales Executives</option>
                        <?php
                        $sal_ex_query = "SELECT DISTINCT SAL_EX FROM s_salma WHERE SAL_EX IS NOT NULL AND SAL_EX != '' ORDER BY SAL_EX";
                        $sal_ex_result = $conn->query($sal_ex_query);
                        while($row = $sal_ex_result->fetch_assoc()) {
                            $selected = ($_GET['sal_ex'] ?? '') == $row['SAL_EX'] ? 'selected' : '';
                            echo "<option value='{$row['SAL_EX']}' $selected>{$row['SAL_EX']}</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="filter-group">
                    <label for="cus_name">Customer Name:</label>
                    <select name="cus_name" id="cus_name">
                        <option value="">All Customers</option>
                        <?php
                        $cus_query = "SELECT DISTINCT CUS_NAME FROM s_salma WHERE CUS_NAME IS NOT NULL AND CUS_NAME != '' ORDER BY CUS_NAME";
                        $cus_result = $conn->query($cus_query);
                        while($row = $cus_result->fetch_assoc()) {
                            $selected = ($_GET['cus_name'] ?? '') == $row['CUS_NAME'] ? 'selected' : '';
                            echo "<option value='{$row['CUS_NAME']}' $selected>{$row['CUS_NAME']}</option>";
                        }
                        ?>
                    </select>
                </div>
                
                <div class="filter-group">
                    <label for="from_date">From Date:</label>
                    <input type="date" name="from_date" value="<?php echo $_GET['from_date'] ?? ''; ?>">
                </div>
                
                <div class="filter-group">
                    <label for="to_date">To Date:</label>
                    <input type="date" name="to_date" value="<?php echo $_GET['to_date'] ?? ''; ?>">
                </div>
                
                <button type="submit">Generate Report</button>
                <button type="button" onclick="exportToExcel()">Export to Excel</button>
            </form>
        </div>

        <?php
        // Build query with filters
        $sql = "SELECT id, REF_NO, SDATE, C_CODE, CUS_NAME, SAL_EX, 
                       GRAND_TOT, TOTPAY, (GRAND_TOT - TOTPAY) as OUTSTANDING,
                       DATEDIFF(CURDATE(), SDATE) as AGE_DAYS
                FROM s_salma 
                WHERE GRAND_TOT > TOTPAY";
        
        $conditions = [];
        $params = [];
        
        if (!empty($_GET['sal_ex'])) {
            $conditions[] = "SAL_EX = ?";
            $params[] = $_GET['sal_ex'];
        }
        
        if (!empty($_GET['cus_name'])) {
            $conditions[] = "CUS_NAME = ?";
            $params[] = $_GET['cus_name'];
        }
        
        if (!empty($_GET['from_date'])) {
            $conditions[] = "SDATE >= ?";
            $params[] = $_GET['from_date'];
        }
        
        if (!empty($_GET['to_date'])) {
            $conditions[] = "SDATE <= ?";
            $params[] = $_GET['to_date'];
        }
        
        if (!empty($conditions)) {
            $sql .= " AND " . implode(" AND ", $conditions);
        }
        
        $sql .= " ORDER BY SDATE DESC";
        
        // Prepare and execute statement
        $stmt = $conn->prepare($sql);
        if (!empty($params)) {
            $types = str_repeat('s', count($params));
            $stmt->bind_param($types, ...$params);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        
        $total_outstanding = 0;
        $records = [];
        $age_buckets = [
            '0-30' => 0,
            '31-60' => 0,
            '61-90' => 0,
            '91-180' => 0,
            '180+' => 0
        ];
        
        $sales_exec_data = [];
        $customer_data = [];
        
        while($row = $result->fetch_assoc()) {
            $records[] = $row;
            $total_outstanding += $row['OUTSTANDING'];
            
            // Categorize by age
            $age_days = $row['AGE_DAYS'];
            if ($age_days <= 30) {
                $age_buckets['0-30'] += $row['OUTSTANDING'];
            } elseif ($age_days <= 60) {
                $age_buckets['31-60'] += $row['OUTSTANDING'];
            } elseif ($age_days <= 90) {
                $age_buckets['61-90'] += $row['OUTSTANDING'];
            } elseif ($age_days <= 180) {
                $age_buckets['91-180'] += $row['OUTSTANDING'];
            } else {
                $age_buckets['180+'] += $row['OUTSTANDING'];
            }
            
            // Sales executive data
            $sal_ex = $row['SAL_EX'];
            if (!isset($sales_exec_data[$sal_ex])) {
                $sales_exec_data[$sal_ex] = 0;
            }
            $sales_exec_data[$sal_ex] += $row['OUTSTANDING'];
            
            // Customer data
            $cus_name = $row['CUS_NAME'];
            if (!isset($customer_data[$cus_name])) {
                $customer_data[$cus_name] = 0;
            }
            $customer_data[$cus_name] += $row['OUTSTANDING'];
        }
        ?>

        <!-- Summary Section -->
        <div class="summary">
            <h3>Report Summary</h3>
            <p><strong>Total Outstanding Amount:</strong> <?php echo number_format($total_outstanding, 2); ?></p>
            <p><strong>Total Invoices:</strong> <?php echo count($records); ?></p>
        </div>

        <!-- Charts Section -->
        <div class="charts-container">
            <div class="chart-box">
                <h3>Outstanding by Age Bucket</h3>
                <canvas id="ageChart" width="400" height="300"></canvas>
            </div>
            
            <div class="chart-box">
                <h3>Outstanding by Sales Executive</h3>
                <canvas id="salesExecChart" width="400" height="300"></canvas>
            </div>
            
            <div class="chart-box">
                <h3>Top Customers by Outstanding</h3>
                <canvas id="customerChart" width="400" height="300"></canvas>
            </div>
        </div>

        <!-- Data Table -->
        <h3>Detailed Report</h3>
        <table id="reportTable">
            <thead>
                <tr>
                    <th>Ref No</th>
                    <th>Date</th>
                    <th>Customer Code</th>
                    <th>Customer Name</th>
                    <th>Sales Executive</th>
                    <th>Grand Total</th>
                    <th>Total Paid</th>
                    <th>Outstanding</th>
                    <th>Age (Days)</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($records as $record): ?>
                <tr>
                    <td><?php echo htmlspecialchars($record['REF_NO']); ?></td>
                    <td><?php echo $record['SDATE']; ?></td>
                    <td><?php echo htmlspecialchars($record['C_CODE']); ?></td>
                    <td><?php echo htmlspecialchars($record['CUS_NAME']); ?></td>
                    <td><?php echo htmlspecialchars($record['SAL_EX']); ?></td>
                    <td style="text-align: right;"><?php echo number_format($record['GRAND_TOT'], 2); ?></td>
                    <td style="text-align: right;"><?php echo number_format($record['TOTPAY'], 2); ?></td>
                    <td style="text-align: right; font-weight: bold; color: #dc3545;">
                        <?php echo number_format($record['OUTSTANDING'], 2); ?>
                    </td>
                    <td style="text-align: center; 
                        <?php 
                        $age = $record['AGE_DAYS'];
                        if ($age > 180) echo 'background: #dc3545; color: white;';
                        elseif ($age > 90) echo 'background: #ffc107;';
                        elseif ($age > 60) echo 'background: #fd7e14;';
                        else echo 'background: #28a745; color: white;';
                        ?>">
                        <?php echo $age; ?>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <script>
        // Age Bucket Chart
        const ageCtx = document.getElementById('ageChart').getContext('2d');
        const ageChart = new Chart(ageCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode(array_keys($age_buckets)); ?>,
                datasets: [{
                    label: 'Outstanding Amount',
                    data: <?php echo json_encode(array_values($age_buckets)); ?>,
                    backgroundColor: [
                        '#28a745',
                        '#17a2b8',
                        '#ffc107',
                        '#fd7e14',
                        '#dc3545'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'Rs.' + value.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Sales Executive Chart
        const salesExecCtx = document.getElementById('salesExecChart').getContext('2d');
        const salesExecChart = new Chart(salesExecCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode(array_keys($sales_exec_data)); ?>,
                datasets: [{
                    data: <?php echo json_encode(array_values($sales_exec_data)); ?>,
                    backgroundColor: [
                        '#007bff', '#28a745', '#dc3545', '#ffc107', '#6f42c1',
                        '#e83e8c', '#fd7e14', '#20c997', '#6610f2', '#6c757d'
                    ]
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right',
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.label + ': Rs.' + context.raw.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Customer Chart (Top 10)
        const customerData = <?php echo json_encode($customer_data); ?>;
        const sortedCustomers = Object.entries(customerData)
            .sort((a, b) => b[1] - a[1])
            .slice(0, 10);
        
        const customerCtx = document.getElementById('customerChart').getContext('2d');
        const customerChart = new Chart(customerCtx, {
            type: 'doughnut',
            data: {
                labels: sortedCustomers.map(item => item[0]),
                datasets: [{
                    data: sortedCustomers.map(item => item[1]),
                    backgroundColor: [
                        '#007bff', '#28a745', '#dc3545', '#ffc107', '#6f42c1',
                        '#e83e8c', '#fd7e14', '#20c997', '#6610f2', '#6c757d'
                    ]
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.label + ': Rs' + context.raw.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Export to Excel function
        function exportToExcel() {
            let table = document.getElementById('reportTable');
            let html = table.outerHTML;
            let url = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(html);
            let downloadLink = document.createElement('a');
            downloadLink.href = url;
            downloadLink.download = 'Age_Analysis_Report.xls';
            document.body.appendChild(downloadLink);
            downloadLink.click();
            document.body.removeChild(downloadLink);
        }
    </script>
</body>
</html>
<?php
$conn->close();
?>