<?php
// all_warehouse_chart.php

// Konfigurasi koneksi database
define('DB_HOST', 'localhost');
define('DB_USER', 'wiselie_nanda');
define('DB_PASS', 'Clemira123');
define('DB_NAME', 'wiselie_inventory');

// Inisialisasi variabel error
$error = '';
$inventory_data = [];
$warehouses = [
    'products' => 'Surabaya',
    'wh_bali' => 'Bali',
    'wh_jakarta' => 'Jakarta',
    'wh_bandung' => 'Bandung',
    'wh_makassar' => 'Makassar',
    'wh_semarang' => 'Semarang'
];

// Periode penjualan (default bulan ini)
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
if (isset($_GET['start_date'])) {
    $start_date = $_GET['start_date'];
}
if (isset($_GET['end_date'])) {
    $end_date = $_GET['end_date'];
}

// Hitung hari berjalan dalam periode
$start_datetime = new DateTime($start_date);
$end_datetime = new DateTime($end_date);
$days_passed = $start_datetime->diff($end_datetime)->days + 1;
$current_day = (new DateTime())->format('Y-m-d');
if ($current_day < $end_date) {
    $days_passed = $start_datetime->diff(new DateTime())->days + 1;
}

try {
    // Membuat koneksi database
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    
    // Cek koneksi
    if ($conn->connect_error) {
        throw new Exception("Koneksi database gagal: " . $conn->connect_error);
    }

    // Ambil semua part_number dan buy_price dari products dengan categorie_id = 7
    $products_query = "SELECT part_number, buy_price FROM products WHERE categorie_id = 7";
    $products_result = $conn->query($products_query);
    
    if ($products_result === false) {
        throw new Exception("Error mengambil data products: " . $conn->error);
    }

    $all_products = [];
    if ($products_result->num_rows > 0) {
        while ($row = $products_result->fetch_assoc()) {
            $all_products[] = [
                'part_number' => $row['part_number'],
                'buy_price' => $row['buy_price']
            ];
        }
    } else {
        $error = "Tidak ditemukan part_number dengan categorie_id = 7";
    }

    // Ambil data penjualan per lokasi
    $sales_data = [];
    $sales_query = "SELECT part_number, 
                    SUM(CASE WHEN loc = 'Jakarta' THEN quantity ELSE 0 END) as jakarta,
                    SUM(CASE WHEN loc = 'Makassar' THEN quantity ELSE 0 END) as makassar,
                    SUM(CASE WHEN loc = 'Bali' THEN quantity ELSE 0 END) as bali,
                    SUM(CASE WHEN loc = 'Semarang' THEN quantity ELSE 0 END) as semarang,
                    SUM(CASE WHEN loc = 'Bandung' THEN quantity ELSE 0 END) as bandung,
                    SUM(CASE WHEN loc = 'Sidoarjo' THEN quantity ELSE 0 END) as surabaya
                    FROM awb 
                    WHERE DATE(scan_in) BETWEEN ? AND ?
                    GROUP BY part_number";
    
    $stmt = $conn->prepare($sales_query);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $sales_result = $stmt->get_result();
    
    while ($row = $sales_result->fetch_assoc()) {
        $sales_data[$row['part_number']] = [
            'Jakarta' => $row['jakarta'],
            'Makassar' => $row['makassar'],
            'Bali' => $row['bali'],
            'Semarang' => $row['semarang'],
            'Bandung' => $row['bandung'],
            'Surabaya' => $row['surabaya']
        ];
    }

    // Siapkan data untuk setiap part_number di semua warehouse
    foreach ($all_products as $product) {
        $part_number = $product['part_number'];
        $buy_price = $product['buy_price'];
        
        $part_data = [
            'part_number' => $part_number,
            'buy_price' => $buy_price
        ];
        
        $total = 0;
        $total_sales = 0;
        
        foreach ($warehouses as $table => $location) {
            // Query quantity untuk part_number di setiap warehouse
            $query = "SELECT quantity FROM $table WHERE part_number = ?";
            $stmt = $conn->prepare($query);
            
            if ($stmt === false) {
                throw new Exception("Error prepare statement untuk $table: " . $conn->error);
            }
            
            $stmt->bind_param("s", $part_number);
            if (!$stmt->execute()) {
                throw new Exception("Error execute query untuk $table: " . $stmt->error);
            }
            
            $result = $stmt->get_result();
            if ($result === false) {
                throw new Exception("Error mendapatkan result untuk $table: " . $stmt->error);
            }
            
            $quantity = 0;
            if ($result->num_rows > 0) {
                $row = $result->fetch_assoc();
                $quantity = (int)$row['quantity'];
            }
            
            $part_data[$location] = $quantity; // Store quantity for each location
            $total += $quantity;
            
            // Tambahkan data penjualan jika ada
            $sales_qty = isset($sales_data[$part_number][$location]) ? $sales_data[$part_number][$location] : 0;
            $part_data[$location.'_sales'] = $sales_qty;
            $total_sales += $sales_qty;
        }
        
        $part_data['Total'] = $total;
        $part_data['Total_sales'] = $total_sales;
        $part_data['Amount'] = $total * $buy_price;
        $part_data['Sales_amount'] = $total_sales * $buy_price;
        
        // Hitung stock-to-sales ratio (hindari division by zero)
        $part_data['Stock_sales_ratio'] = ($total_sales > 0) ? round($total / $total_sales, 2) : 0;
        
        // Hitung daily sales rate dan inventory days
        $daily_sales_rate = ($days_passed > 0) ? $total_sales / $days_passed : 0;
        $part_data['Daily_sales_rate'] = round($daily_sales_rate, 2);
        $part_data['Inventory_days'] = ($daily_sales_rate > 0) ? round($total / $daily_sales_rate, 2) : 0;
        
        $inventory_data[] = $part_data;
    }

    // Filter out items with zero sales before sorting
    $items_with_sales = array_filter($inventory_data, function($item) {
        return $item['Total_sales'] > 0;
    });

    // Urutkan berdasarkan Inventory Days tertinggi (slow moving)
    usort($items_with_sales, function($a, $b) {
        return $b['Inventory_days'] <=> $a['Inventory_days'];
    });

    // Ambil top 10 slow moving items (only those with sales)
    $slow_moving_items = array_slice($items_with_sales, 0, 10);

    // Hitung total untuk setiap warehouse
    $warehouse_totals = [];
    $warehouse_amounts = [];
    $warehouse_sales = []; // Total sales quantity per warehouse
    $warehouse_sales_amounts = []; // Total sales amount per warehouse
    
    foreach ($warehouses as $table => $location) {
        $warehouse_totals[$location] = 0;
        $warehouse_amounts[$location] = 0;
        $warehouse_sales[$location] = 0; // Initialize for quantity
        $warehouse_sales_amounts[$location] = 0; // Initialize for amount
    }

    foreach ($inventory_data as $part_data) {
        foreach ($warehouses as $table => $location) {
            $warehouse_totals[$location] += $part_data[$location];
            $warehouse_amounts[$location] += $part_data[$location] * $part_data['buy_price'];
            $warehouse_sales[$location] += $part_data[$location.'_sales']; // Accumulate sales quantity
            $warehouse_sales_amounts[$location] += $part_data[$location.'_sales'] * $part_data['buy_price']; // Accumulate sales amount
        }
    }

    // Hitung stock-to-sales ratio per warehouse
    $warehouse_ratios = [];
    foreach ($warehouses as $table => $location) { // Corrected: iterate over $warehouses to get location
        $warehouse_ratios[$location] = ($warehouse_sales[$location] > 0) 
            ? round($warehouse_totals[$location] / $warehouse_sales[$location], 2) 
            : 0;
    }

    // Siapkan data untuk chart
    $chart_labels = array_values($warehouses);
    $chart_quantity_data = array_values($warehouse_totals);
    $chart_amount_data = array_values($warehouse_amounts);
    $chart_sales_data = array_values($warehouse_sales);
    $chart_sales_amount_data = array_values($warehouse_sales_amounts);
    $chart_ratio_data = array_values($warehouse_ratios);

} catch (Exception $e) {
    $error = $e->getMessage();
} finally {
    // Tutup koneksi database jika ada
    if (isset($conn)) {
        $conn->close();
    }
}
?>

<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Inventory Dashboard - All Warehouse</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.0.0"></script>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-color: #f5f5f5;
            margin: 0;
            padding: 20px;
            color: #333;
        }
        .container {
            max-width: 1400px;
            margin: 0 auto;
        }
        .header {
            text-align: center;
            margin-bottom: 30px;
            padding: 20px;
            background: white;
            border-radius: 10px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #2c3e50;
            margin: 0;
        }
        .dashboard {
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 20px;
            margin-bottom: 20px;
        }
        .card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        .card-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 15px;
            padding-bottom: 10px;
            border-bottom: 1px solid #eee;
        }
        .card-title {
            font-size: 18px;
            font-weight: bold;
            color: #2c3e50;
            margin: 0;
        }
        .chart-container {
            position: relative;
            height: 300px;
            width: 100%;
        }
        .stats-container {
            display: grid;
            grid-template-columns: repeat(3, 1fr);
            gap: 20px;
            margin-bottom: 20px;
        }
        .stat-card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
            text-align: center;
        }
        .stat-value {
            font-size: 28px;
            font-weight: bold;
            color: #3498db;
            margin: 10px 0;
        }
        .stat-label {
            color: #7f8c8d;
            font-size: 14px;
        }
        .date-filter {
            background-color: #f8f9fa;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .date-filter label {
            margin-right: 10px;
            font-weight: bold;
        }
        .date-filter input {
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
            margin-right: 15px;
        }
        .date-filter button {
            padding: 8px 15px;
            background-color: #3498db;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        .error-message {
            background-color: #f8d7da;
            color: #721c24;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            border: 1px solid #f5c6cb;
        }
        .slow-moving-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        .slow-moving-table th, 
        .slow-moving-table td {
            padding: 10px;
            text-align: left;
            border-bottom: 1px solid #ddd;
            /* Remove white-space: nowrap here to allow wrapping on large screens if needed */
        }
        .slow-moving-table th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        .slow-moving-table tr:hover {
            background-color: #f5f5f5;
        }
        .high-days {
            color: #e74c3c;
            font-weight: bold;
        }
        .medium-days {
            color: #f39c12;
            font-weight: bold;
        }
        .low-days {
            color: #27ae60;
            font-weight: bold;
        }
        /* New style for sales summary */
        .sales-summary-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        .sales-summary-table th,
        .sales-summary-table td {
            padding: 10px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        .sales-summary-table th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        .sales-summary-table tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        .sales-summary-table tr:hover {
            background-color: #f5f5f5;
        }

        @media (max-width: 1200px) {
            .dashboard {
                grid-template-columns: 1fr;
            }
            .stats-container {
                grid-template-columns: repeat(2, 1fr);
            }
            
            /* Responsive table specific styles for slow-moving-table */
            .slow-moving-table {
                display: block;
                overflow-x: auto; /* Fallback for very wide content or if JS isn't preferred */
                width: 100%; /* Ensure it takes full width when block */
                -webkit-overflow-scrolling: touch; /* For smooth scrolling on iOS */
            }
            
            .slow-moving-table thead, .slow-moving-table tbody, .slow-moving-table th, .slow-moving-table td, .slow-moving-table tr {
                display: block; /* Make all table elements behave like block elements */
            }
            
            .slow-moving-table thead tr {
                position: absolute; /* Hide the original header row off-screen */
                top: -9999px;
                left: -9999px;
            }
            
            .slow-moving-table tr {
                border: 1px solid #ccc;
                margin-bottom: 10px; /* Space between "rows" */
                background-color: white; /* Ensure background is white for stacked cards */
                box-shadow: 0 0 5px rgba(0,0,0,0.05); /* Slight shadow for "card" effect */
            }
            
            .slow-moving-table td {
                border: none; /* Remove cell borders */
                border-bottom: 1px solid #eee; /* Add a separator between stacked items */
                position: relative;
                padding-left: 48%; /* Make space for the label */
                text-align: right; /* Align data to the right */
                min-height: 30px; /* Ensure a minimum height for cells */
                display: flex; /* Use flexbox for label and value alignment */
                align-items: center; /* Vertically center label and value */
                justify-content: flex-end; /* Align value to the right */
            }

            .slow-moving-table td:last-child {
                border-bottom: none; /* No border on the last item in the stacked row */
            }
            
            .slow-moving-table td:before {
                position: absolute;
                top: 0; /* Align to the top of the cell */
                left: 0;
                width: 45%; /* Width for the label */
                padding-right: 10px;
                white-space: nowrap;
                text-align: left;
                font-weight: bold;
                content: attr(data-label); /* Use data-label attribute for content */
                padding: 10px; /* Match padding of the cell */
                box-sizing: border-box; /* Include padding in width calculation */
            }

            /* Generate :before content for each column based on its data-label */
            <?php 
            // Define all possible column headers in order
            $column_headers = [
                'Part Number', 
                'Total Stock',
            ];
            foreach ($warehouses as $location_name) {
                $column_headers[] = 'Stock ' . $location_name; // Add "Stock [City]" headers
            }
            $column_headers = array_merge($column_headers, [
                'Total Sales', 
                'Daily Sales Rate', 
                'Inventory Days', 
                'Stock/Sales Ratio'
            ]);
            
            // Generate the content for the data-label attributes
            // This is actually set in the HTML now, not here.
            // This PHP block is mainly for demonstrating what the labels would be.
            ?>
        }
        @media (max-width: 768px) {
            .stats-container {
                grid-template-columns: 1fr;
            }
            .sales-summary-table {
                font-size: 14px;
            }
            /* Further adjust slow-moving table for very small screens if needed */
            .slow-moving-table td {
                padding-left: 50%; /* Adjust padding for very small screens */
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>Inventory Dashboard (Category ID: 7) - All Warehouses</h1>
            <div class="date-filter">
                <form method="GET" action="">
                    <label for="start_date">Start Date:</label>
                    <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
                    
                    <label for="end_date">End Date:</label>
                    <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
                    
                    <button type="submit">Filter</button>
                </form>
                <p style="margin-top: 10px; font-size: 14px; color: #7f8c8d;">
                    Period: <?= htmlspecialchars($start_date) ?> to <?= htmlspecialchars($end_date) ?> 
                    (<?= $days_passed ?> days passed)
                </p>
            </div>
        </div>
        
        <?php if (!empty($error)): ?>
            <div class="error-message">
                <strong>Error:</strong> <?= htmlspecialchars($error) ?>
            </div>
        <?php endif; ?>
        
        <?php if (empty($error) && empty($inventory_data)): ?>
            <div class="card">
                <p>No inventory data found for this category.</p>
            </div>
        <?php else: ?>
            <div class="stats-container">
                <div class="stat-card">
                    <div class="stat-label">Total Products</div>
                    <div class="stat-value"><?= count($inventory_data) ?></div>
                </div>
                <div class="stat-card">
                    <div class="stat-label">Total Stock</div>
                    <div class="stat-value"><?= number_format(array_sum($chart_quantity_data)) ?></div>
                </div>
                <div class="stat-card">
                    <div class="stat-label">Total Sales</div>
                    <div class="stat-value"><?= number_format(array_sum($chart_sales_data)) ?></div>
                </div>
                <div class="stat-card">
                    <div class="stat-label">Inventory Value</div>
                    <div class="stat-value">Rp<?= number_format(array_sum($chart_amount_data), 2) ?></div>
                </div>
                <div class="stat-card">
                    <div class="stat-label">Sales Value</div>
                    <div class="stat-value">Rp<?= number_format(array_sum($chart_sales_amount_data), 2) ?></div>
                </div>
                <div class="stat-card">
                    <div class="stat-label">Avg Stock/Sales Ratio</div>
                    <div class="stat-value">
                        <?php 
                            $total_ratio = 0;
                            $count = 0;
                            foreach ($warehouse_ratios as $ratio) {
                                if (is_numeric($ratio)) {
                                    $total_ratio += $ratio;
                                    $count++;
                                }
                            }
                            echo $count > 0 ? round($total_ratio / $count, 2) : 'N/A';
                        ?>
                    </div>
                </div>
            </div>
            
            <div class="dashboard">
                <div class="card">
                    <div class="card-header">
                        <h2 class="card-title">Stock vs Sales by Warehouse</h2>
                    </div>
                    <div class="chart-container">
                        <canvas id="stockSalesChart"></canvas>
                    </div>
                </div>
                
                <div class="card">
                    <div class="card-header">
                        <h2 class="card-title">Stock-to-Sales Ratio by Warehouse</h2>
                    </div>
                    <div class="chart-container">
                        <canvas id="ratioChart"></canvas>
                    </div>
                </div>
            </div>
            
            <div class="dashboard">
                <div class="card">
                    <div class="card-header">
                        <h2 class="card-title">Inventory Value vs Sales Value (Rp)</h2>
                    </div>
                    <div class="chart-container">
                        <canvas id="valueChart"></canvas>
                    </div>
                </div>
                
                <div class="card">
                    <div class="card-header">
                        <h2 class="card-title">Warehouse Performance</h2>
                    </div>
                    <div class="chart-container">
                        <canvas id="performanceChart"></canvas>
                    </div>
                </div>
            </div>
            
            ---
            <div class="card" style="margin-bottom: 20px;">
                <div class="card-header">
                    <h2 class="card-title">Detailed Sales Performance by City</h2>
                </div>
                <?php if (empty($warehouse_sales_amounts) && empty($warehouse_sales)): ?>
                    <p style="padding: 20px;">No sales data available for this period.</p>
                <?php else: ?>
                    <table class="sales-summary-table">
                        <thead>
                            <tr>
                                <th>City</th>
                                <th>Total Sales Quantity</th>
                                <th>Total Sales Value (Rp)</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php 
                            // Combine data for sorting, prioritize by sales amount
                            $combined_sales_data = [];
                            foreach ($warehouses as $location_key => $location_name) {
                                $combined_sales_data[$location_name] = [
                                    'quantity' => $warehouse_sales[$location_name] ?? 0,
                                    'amount' => $warehouse_sales_amounts[$location_name] ?? 0
                                ];
                            }

                            // Sort by total sales amount in descending order
                            uasort($combined_sales_data, function($a, $b) {
                                return $b['amount'] <=> $a['amount'];
                            });

                            foreach ($combined_sales_data as $location => $data): 
                            ?>
                                <tr>
                                    <td><?= htmlspecialchars($location) ?></td>
                                    <td><?= number_format($data['quantity']) ?></td>
                                    <td>Rp<?= number_format($data['amount'], 2) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                <?php endif; ?>
            </div>
            
            ---
            <div class="card">
                <div class="card-header">
                    <h2 class="card-title">Slow Moving Analysis (Top 10)</h2>
                </div>
                <?php if (empty($slow_moving_items)): ?>
                    <p style="padding: 20px;">No slow moving items found (all items have zero sales in this period).</p>
                <?php else: ?>
                    <table class="slow-moving-table">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>Total Stock</th>
                                <?php foreach ($warehouses as $location_key => $location_name): // Use location_name for display ?>
                                    <th>Stock <?= htmlspecialchars($location_name) ?></th>
                                <?php endforeach; ?>
                                <th>Total Sales</th>
                                <th>Daily Sales Rate</th>
                                <th>Inventory Days</th>
                                <th>Stock/Sales Ratio</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($slow_moving_items as $item): ?>
                                <tr>
                                    <td data-label="Part Number"><?= htmlspecialchars($item['part_number']) ?></td>
                                    <td data-label="Total Stock"><?= number_format($item['Total']) ?></td>
                                    <?php foreach ($warehouses as $location_key => $location_name): ?>
                                        <td data-label="Stock <?= htmlspecialchars($location_name) ?>"><?= number_format($item[$location_name]) ?></td>
                                    <?php endforeach; ?>
                                    <td data-label="Total Sales"><?= number_format($item['Total_sales']) ?></td>
                                    <td data-label="Daily Sales Rate"><?= number_format($item['Daily_sales_rate'], 2) ?></td>
                                    <td data-label="Inventory Days" class="<?php 
                                        if ($item['Inventory_days'] > 60) {
                                            echo 'high-days';
                                        } elseif ($item['Inventory_days'] > 30) {
                                            echo 'medium-days';
                                        } else {
                                            echo 'low-days';
                                        }
                                    ?>">
                                        <?= number_format($item['Inventory_days'], 2) ?>
                                    </td>
                                    <td data-label="Stock/Sales Ratio"><?= number_format($item['Stock_sales_ratio'], 2) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                    <p style="margin-top: 15px; font-size: 14px; color: #7f8c8d;">
                        <strong>Note:</strong> Inventory Days = Total Stock / Daily Sales Rate. 
                        <span class="high-days">Red (>60 days)</span> indicates slow moving items, 
                        <span class="medium-days">orange (30-60 days)</span> indicates medium moving items, 
                        <span class="low-days">green (<30 days)</span> indicates fast moving items.
                    </p>
                <?php endif; ?>
            </div>
        <?php endif; ?>
    </div>
    
    <script>
        // Stock vs Sales Chart
        const stockSalesCtx = document.getElementById('stockSalesChart').getContext('2d');
        const stockSalesChart = new Chart(stockSalesCtx, {
            type: 'bar',
            data: {
                labels: <?= json_encode($chart_labels) ?>,
                datasets: [{
                    label: 'Stock',
                    data: <?= json_encode($chart_quantity_data) ?>,
                    backgroundColor: 'rgba(52, 152, 219, 0.7)',
                    borderColor: 'rgba(52, 152, 219, 1)',
                    borderWidth: 1
                }, {
                    label: 'Sales',
                    data: <?= json_encode($chart_sales_data) ?>,
                    backgroundColor: 'rgba(46, 204, 113, 0.7)',
                    borderColor: 'rgba(46, 204, 113, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.dataset.label + ': ' + context.parsed.y.toLocaleString();
                            }
                        }
                    },
                    datalabels: {
                        display: false
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return value.toLocaleString();
                            }
                        }
                    }
                }
            },
            plugins: [ChartDataLabels]
        });
        
        // Stock-to-Sales Ratio Chart
        const ratioCtx = document.getElementById('ratioChart').getContext('2d');
        const ratioChart = new Chart(ratioCtx, {
            type: 'bar',
            data: {
                labels: <?= json_encode($chart_labels) ?>,
                datasets: [{
                    label: 'Stock-to-Sales Ratio',
                    data: <?= json_encode($chart_ratio_data) ?>,
                    backgroundColor: 'rgba(241, 196, 15, 0.7)',
                    borderColor: 'rgba(241, 196, 15, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.parsed.y + ':1';
                            }
                        }
                    },
                    datalabels: {
                        anchor: 'end',
                        align: 'top',
                        formatter: function(value) {
                            return value + ':1';
                        },
                        color: '#333',
                        font: {
                            weight: 'bold'
                        }
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return value + ':1';
                            }
                        }
                    }
                }
            },
            plugins: [ChartDataLabels]
        });
        
        // Value Chart
        const valueCtx = document.getElementById('valueChart').getContext('2d');
        const valueChart = new Chart(valueCtx, {
            type: 'bar',
            data: {
                labels: <?= json_encode($chart_labels) ?>,
                datasets: [{
                    label: 'Inventory Value',
                    data: <?= json_encode($chart_amount_data) ?>,
                    backgroundColor: 'rgba(52, 152, 219, 0.7)',
                    borderColor: 'rgba(52, 152, 219, 1)',
                    borderWidth: 1
                }, {
                    label: 'Sales Value',
                    data: <?= json_encode($chart_sales_amount_data) ?>,
                    backgroundColor: 'rgba(46, 204, 113, 0.7)',
                    borderColor: 'rgba(46, 204, 113, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.dataset.label + ': Rp' + context.parsed.y.toLocaleString();
                            }
                        }
                    },
                    datalabels: {
                        display: false
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'Rp' + value.toLocaleString();
                            }
                        }
                    }
                }
            },
            plugins: [ChartDataLabels]
        });
        
        // Performance Chart (Radar)
        const performanceCtx = document.getElementById('performanceChart').getContext('2d');
        const performanceChart = new Chart(performanceCtx, {
            type: 'radar',
            data: {
                labels: <?= json_encode($chart_labels) ?>,
                datasets: [{
                    label: 'Stock Quantity',
                    data: <?= json_encode($chart_quantity_data) ?>,
                    backgroundColor: 'rgba(52, 152, 219, 0.2)',
                    borderColor: 'rgba(52, 152, 219, 1)',
                    borderWidth: 1
                }, {
                    label: 'Sales Quantity',
                    data: <?= json_encode($chart_sales_data) ?>,
                    backgroundColor: 'rgba(46, 204, 113, 0.2)',
                    borderColor: 'rgba(46, 204, 113, 1)',
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                return context.dataset.label + ': ' + context.parsed.r.toLocaleString();
                            }
                        }
                    }
                },
                scales: {
                    r: {
                        angleLines: {
                            display: true
                        },
                        suggestedMin: 0,
                        ticks: {
                            callback: function(value) {
                                return value.toLocaleString();
                            }
                        }
                    }
                }
            }
        });
    </script>
</body>
</html>


<?php
// awb_data.php - Dashboard AWB
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Koneksi Database
$host = 'localhost';
$dbname = 'wiselie_inventory';
$username = 'wiselie_nanda';
$password = 'Clemira123';

// Mapping LOC yang diminta (NULL/KOSONG akan digabung ke SURABAYA)
$locMapping = [
    'surabaya' => 'SURABAYA',
    'jakarta' => 'JAKARTA',
    'bali' => 'BALI',
    'semarang' => 'SEMARANG',
    'bandung' => 'BANDUNG',
    'makassar' => 'MAKASSAR'
];

// Warna untuk chart
$chartColors = [
    'SURABAYA' => '#3B82F6',
    'JAKARTA' => '#EF4444',
    'BALI' => '#10B981',
    'SEMARANG' => '#8B5CF6',
    'BANDUNG' => '#F59E0B',
    'MAKASSAR' => '#EC4899',
    'LAINNYA' => '#9CA3AF'
];
?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <title>Dashboard AWB</title>
    <style>
        :root {
            --primary: #3B82F6;
            --secondary: #64748B;
            --success: #10B981;
            --danger: #EF4444;
            --warning: #F59E0B;
            --info: #0EA5E9;
        }
        body { 
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; 
            margin: 0; 
            padding: 20px; 
            background-color: #F8FAFC;
            color: #1E293B;
        }
        .dashboard-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 20px;
            padding-bottom: 15px;
            border-bottom: 1px solid #E2E8F0;
        }
        .dashboard-title {
            font-size: 24px;
            font-weight: 600;
            color: #1E293B;
        }
        .filter-container {
            background: white;
            padding: 15px;
            border-radius: 8px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            margin-bottom: 20px;
        }
        .filter-form {
            display: flex;
            gap: 15px;
            align-items: center;
        }
        .filter-group {
            display: flex;
            align-items: center;
            gap: 8px;
        }
        label {
            font-weight: 500;
            color: #475569;
        }
        input[type="month"] {
            padding: 8px 12px;
            border: 1px solid #E2E8F0;
            border-radius: 6px;
            background: white;
        }
        button {
            padding: 8px 16px;
            background-color: var(--primary);
            color: white;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-weight: 500;
            transition: background-color 0.2s;
        }
        button:hover {
            background-color: #2563EB;
        }
        .dashboard-grid {
            display: grid;
            grid-template-columns: repeat(12, 1fr);
            gap: 20px;
        }
        .card {
            background: white;
            border-radius: 8px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            padding: 20px;
        }
        .card-header {
            font-size: 16px;
            font-weight: 600;
            margin-bottom: 15px;
            color: #1E293B;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        .card-lg {
            grid-column: span 8;
        }
        .card-md {
            grid-column: span 6;
        }
        .card-sm {
            grid-column: span 4;
        }
        .card-xl {
            grid-column: span 12;
        }
        .chart-container {
            position: relative;
            height: 300px;
            width: 100%;
        }
        .chart-container-sm {
            position: relative;
            height: 250px;
            width: 100%;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }
        th, td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #E2E8F0;
        }
        th {
            background-color: #F8FAFC;
            font-weight: 600;
            color: #475569;
            position: sticky;
            top: 0;
        }
        tr:hover {
            background-color: #F8FAFC;
        }
        .summary-row {
            background-color: #F8FAFC;
            font-weight: 600;
        }
        .other-loc {
            color: #64748B;
        }
        .percentage {
            font-size: 12px;
            color: #64748B;
            display: block;
            margin-top: 3px;
        }
        .kpi-container {
            display: flex;
            justify-content: space-between;
            text-align: center;
        }
        .kpi-item {
            flex: 1;
            padding: 15px;
        }
        .kpi-value {
            font-size: 28px;
            font-weight: 700;
            margin: 10px 0;
            color: var(--primary);
        }
        .kpi-label {
            font-size: 14px;
            color: #64748B;
        }
        .tabs {
            display: flex;
            border-bottom: 1px solid #E2E8F0;
            margin-bottom: 15px;
        }
        .tab {
            padding: 8px 16px;
            cursor: pointer;
            border-bottom: 2px solid transparent;
            font-weight: 500;
        }
        .tab.active {
            border-bottom-color: var(--primary);
            color: var(--primary);
        }
        .tab-content {
            display: none;
        }
        .tab-content.active {
            display: block;
        }
        @media (max-width: 1200px) {
            .dashboard-grid {
                grid-template-columns: repeat(1, 1fr);
            }
            .card-lg, .card-md, .card-sm, .card-xl {
                grid-column: span 1;
            }
        }
        /* Table specific styles */
        .table-container {
            overflow-x: auto;
            margin-top: 15px;
        }
        .data-table {
            min-width: 100%;
            white-space: nowrap;
        }
        .data-table th {
            position: sticky;
            top: 0;
            background: #F8FAFC;
            z-index: 10;
        }
        .data-table td:first-child,
        .data-table th:first-child {
            position: sticky;
            left: 0;
            background: white;
            z-index: 20;
        }
        .data-table tr:hover td:first-child {
            background: #F8FAFC;
        }
        .data-table .summary-row td:first-child {
            background: #F8FAFC;
        }
    </style>
    <!-- Load Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <!-- Load Luxon untuk tanggal -->
    <script src="https://cdn.jsdelivr.net/npm/luxon@2.0.2"></script>
    <!-- Load Chart.js adapter -->
    <script src="https://cdn.jsdelivr.net/npm/chartjs-adapter-luxon@1.0.0"></script>
</head>
<body>
    <div class="dashboard-header">
        <div class="dashboard-title">Dashboard AWB</div>
        <div>Periode: <?php echo date('d M Y'); ?></div>
    </div>
    
    <?php
    try {
        $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Set default bulan (3 bulan terakhir)
        $currentMonth = date('Y-m');
        $defaultStartMonth = date('Y-m', strtotime('-2 months'));
        $startMonth = isset($_GET['start_month']) ? $_GET['start_month'] : $defaultStartMonth;
        $endMonth = isset($_GET['end_month']) ? $_GET['end_month'] : $currentMonth;
        
        // Validasi range bulan
        if (strtotime($startMonth) > strtotime($endMonth)) {
            $temp = $startMonth;
            $startMonth = $endMonth;
            $endMonth = $temp;
        }
        
        // Form filter
        echo '<div class="filter-container">
                <form method="GET" class="filter-form">
                    <div class="filter-group">
                        <label>Dari Bulan:</label>
                        <input type="month" name="start_month" value="'.htmlspecialchars($startMonth).'" min="2020-01">
                    </div>
                    <div class="filter-group">
                        <label>Sampai Bulan:</label>
                        <input type="month" name="end_month" value="'.htmlspecialchars($endMonth).'" max="'.date('Y-m').'">
                    </div>
                    <button type="submit">Terapkan Filter</button>
                </form>
              </div>';
        
        // Generate semua bulan dalam range
        $months = [];
        $current = $startMonth;
        while (strtotime($current) <= strtotime($endMonth)) {
            $months[] = $current;
            $current = date('Y-m', strtotime('+1 month', strtotime($current)));
        }
        
        // Query data utama (NULL/KOSONG digabung ke SURABAYA)
        $stmt = $pdo->prepare("
            SELECT 
                CASE
                    WHEN loc IS NULL OR loc = '' THEN 'SURABAYA'  -- NULL/KOSONG digabung ke SURABAYA
                    ".implode(" ", array_map(function($key, $value) {
                        return "WHEN LOWER(loc) LIKE '%".strtolower($key)."%' THEN '".$value."'";
                    }, array_keys($locMapping), array_values($locMapping)))."
                    ELSE 'LAINNYA'
                END AS grouped_loc,
                DATE_FORMAT(scan_in, '%Y-%m') AS bulan,
                COUNT(DISTINCT resi) AS total_resi
            FROM awb_data
            WHERE 
                resi IS NOT NULL 
                AND resi != ''
                AND DATE(scan_in) BETWEEN :start_date AND :end_date
            GROUP BY grouped_loc, DATE_FORMAT(scan_in, '%Y-%m')
            ORDER BY 
                CASE grouped_loc
                    WHEN 'SURABAYA' THEN 0
                    ELSE 1
                END,
                grouped_loc, 
                bulan
        ");
        
        $stmt->execute([
            ':start_date' => $startMonth.'-01',
            ':end_date' => date('Y-m-t', strtotime($endMonth.'-01'))
        ]);
        
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Organize data
        $organizedData = [];
        $allLocGroups = array_merge(array_values($locMapping), ['LAINNYA']);
        
        foreach ($allLocGroups as $loc) {
            $organizedData[$loc] = [];
            foreach ($months as $month) {
                $organizedData[$loc][$month] = 0;
            }
        }
        
        foreach ($data as $row) {
            if (isset($organizedData[$row['grouped_loc']][$row['bulan']])) {
                $organizedData[$row['grouped_loc']][$row['bulan']] = $row['total_resi'];
            }
        }
        
        // Hitung KPI
        $totalResi = 0;
        $totalLoc = 0;
        $monthlyTotals = [];
        
        foreach ($months as $month) {
            $monthlyTotals[$month] = 0;
        }
        
        foreach ($organizedData as $loc => $monthData) {
            foreach ($monthData as $month => $count) {
                $monthlyTotals[$month] += $count;
                $totalResi += $count;
            }
            if (array_sum($monthData) > 0) $totalLoc++;
        }
        
        // Hitung persentase kontribusi
        $percentageData = [];
        foreach ($organizedData as $loc => $monthData) {
            $percentageData[$loc] = [];
            foreach ($monthData as $month => $count) {
                $percentageData[$loc][$month] = ($monthlyTotals[$month] > 0) ? round(($count / $monthlyTotals[$month]) * 100, 1) : 0;
            }
        }
        
        // Mulai dashboard
        echo '<div class="dashboard-grid">';
        
        // Card KPI
        echo '<div class="card card-md">
                <div class="card-header">Key Performance Indicators</div>
                <div class="kpi-container">
                    <div class="kpi-item">
                        <div class="kpi-value">'.number_format($totalResi).'</div>
                        <div class="kpi-label">Total RESI</div>
                    </div>
                    <div class="kpi-item">
                        <div class="kpi-value">'.$totalLoc.'</div>
                        <div class="kpi-label">Total LOC</div>
                    </div>
                    <div class="kpi-item">
                        <div class="kpi-value">'.count($months).'</div>
                        <div class="kpi-label">Bulan</div>
                    </div>
                </div>
              </div>';
        
        // Card Trend Bulanan
        echo '<div class="card card-lg">
                <div class="card-header">Trend RESI per Bulan</div>
                <div class="chart-container">
                    <canvas id="trendChart"></canvas>
                </div>
              </div>';
        
        // Card Distribusi LOC
        echo '<div class="card card-sm">
                <div class="card-header">Distribusi resi </div>
                <div class="chart-container">
                    <canvas id="distributionChart"></canvas>
                </div>
              </div>';
        
        // Card Breakdown per Kota per Bulan
        echo '<div class="card card-xl">
                <div class="card-header">Breakdown per Kota per Bulan</div>
                <div class="tabs">
                    <div class="tab active" data-tab="bar">Bar Chart</div>
                    <div class="tab" data-tab="line">Line Chart</div>
                </div>
                <div class="tab-content active" id="tab-bar">
                    <div class="chart-container">
                        <canvas id="breakdownBarChart"></canvas>
                    </div>
                </div>
                <div class="tab-content" id="tab-line">
                    <div class="chart-container">
                        <canvas id="breakdownLineChart"></canvas>
                    </div>
                </div>
              </div>';
        
        // Card Tabel Data
        echo '<div class="card card-xl">
                <div class="card-header">Detail Data Resi per kota </div>
                <div class="table-container">
                    <table class="data-table">
                        <thead>
                            <tr>
                                <th>LOC</th>';
        
        foreach ($months as $month) {
            echo '<th>'.date('M Y', strtotime($month.'-01')).'</th>';
        }
        
        echo '              <th>Total</th>
                            </tr>
                        </thead>
                        <tbody>';
        
        foreach ($organizedData as $loc => $monthData) {
            if (array_sum($monthData) == 0) continue;
            
            $rowClass = ($loc === 'LAINNYA') ? 'other-loc' : '';
            echo '<tr class="'.$rowClass.'">
                    <td>'.htmlspecialchars($loc).'</td>';
            
            $locTotal = 0;
            foreach ($months as $month) {
                $count = $monthData[$month];
                $percentage = $percentageData[$loc][$month];
                echo '<td>'.number_format($count).'<span class="percentage">'.$percentage.'%</span></td>';
                $locTotal += $count;
            }
            
            $totalPercentage = ($totalResi > 0) ? round(($locTotal / $totalResi) * 100, 1) : 0;
            echo '<td class="summary-row">'.number_format($locTotal).'<span class="percentage">'.$totalPercentage.'%</span></td>
                </tr>';
        }
        
        echo '<tr class="summary-row">
                <td>Total</td>';
        
        foreach ($months as $month) {
            echo '<td>'.number_format($monthlyTotals[$month]).'</td>';
        }
        
        echo '      <td>'.number_format($totalResi).'</td>
                </tr>
            </tbody>
        </table>
    </div>
</div>';
        
        echo '</div>'; // Tutup dashboard-grid
        
        // Siapkan data untuk chart
        $chartLabels = array_map(function($m) { return date('M Y', strtotime($m.'-01')); }, $months);
        
        // Data untuk trend chart (stacked bar)
        $trendDatasets = [];
        foreach ($organizedData as $loc => $data) {
            if (array_sum($data) > 0) {
                $trendDatasets[] = [
                    'label' => $loc,
                    'data' => array_values($data),
                    'backgroundColor' => $chartColors[$loc] ?? '#'.substr(md5($loc), 0, 6),
                    'borderColor' => $chartColors[$loc] ?? '#'.substr(md5($loc), 0, 6),
                    'borderWidth' => 1
                ];
            }
        }
        
        // Data untuk distribution chart (pie/doughnut)
        $distributionData = [];
        $distributionLabels = [];
        $distributionColors = [];
        
        foreach ($organizedData as $loc => $data) {
            $total = array_sum($data);
            if ($total > 0) {
                $distributionLabels[] = $loc;
                $distributionData[] = $total;
                $distributionColors[] = $chartColors[$loc] ?? '#'.substr(md5($loc), 0, 6);
            }
        }
        
        // Data untuk breakdown chart (bar dan line)
        $breakdownDatasets = [];
        foreach ($organizedData as $loc => $data) {
            if (array_sum($data) > 0) {
                $breakdownDatasets[] = [
                    'label' => $loc,
                    'data' => array_values($data),
                    'backgroundColor' => $chartColors[$loc] ?? '#'.substr(md5($loc), 0, 6),
                    'borderColor' => $chartColors[$loc] ?? '#'.substr(md5($loc), 0, 6),
                    'borderWidth' => 1,
                    'fill' => false
                ];
            }
        }
        
        // Script untuk chart
        echo '<script>
                document.addEventListener("DOMContentLoaded", function() {
                    // Trend Chart (Stacked Bar)
                    const trendCtx = document.getElementById("trendChart").getContext("2d");
                    const trendChart = new Chart(trendCtx, {
                        type: "bar",
                        data: {
                            labels: '.json_encode($chartLabels).',
                            datasets: '.json_encode($trendDatasets).'
                        },
                        options: {
                            responsive: true,
                            maintainAspectRatio: false,
                            scales: {
                                x: {
                                    stacked: true,
                                    grid: {
                                        display: false
                                    }
                                },
                                y: {
                                    stacked: true,
                                    beginAtZero: true,
                                    ticks: {
                                        callback: function(value) {
                                            return value.toLocaleString();
                                        }
                                    }
                                }
                            },
                            plugins: {
                                legend: {
                                    position: "top",
                                },
                                tooltip: {
                                    callbacks: {
                                        afterBody: function(context) {
                                            const dataset = context[0].dataset;
                                            const total = dataset.data.reduce((a, b) => a + b, 0);
                                            return "Total: " + total.toLocaleString();
                                        }
                                    }
                                }
                            }
                        }
                    });
                    
                    // Distribution Chart (Doughnut)
                    const distCtx = document.getElementById("distributionChart").getContext("2d");
                    const distChart = new Chart(distCtx, {
                        type: "doughnut",
                        data: {
                            labels: '.json_encode($distributionLabels).',
                            datasets: [{
                                data: '.json_encode($distributionData).',
                                backgroundColor: '.json_encode($distributionColors).',
                                borderWidth: 1
                            }]
                        },
                        options: {
                            responsive: true,
                            maintainAspectRatio: false,
                            plugins: {
                                legend: {
                                    position: "right",
                                },
                                tooltip: {
                                    callbacks: {
                                        label: function(context) {
                                            const label = context.label || "";
                                            const value = context.raw || 0;
                                            const total = context.dataset.data.reduce((a, b) => a + b, 0);
                                            const percentage = Math.round((value / total) * 100);
                                            return `${label}: ${value.toLocaleString()} (${percentage}%)`;
                                        }
                                    }
                                }
                            }
                        }
                    });
                    
                    // Breakdown Bar Chart
                    const breakdownBarCtx = document.getElementById("breakdownBarChart").getContext("2d");
                    const breakdownBarChart = new Chart(breakdownBarCtx, {
                        type: "bar",
                        data: {
                            labels: '.json_encode($chartLabels).',
                            datasets: '.json_encode($breakdownDatasets).'
                        },
                        options: {
                            responsive: true,
                            maintainAspectRatio: false,
                            scales: {
                                x: {
                                    grid: {
                                        display: false
                                    }
                                },
                                y: {
                                    beginAtZero: true,
                                    ticks: {
                                        callback: function(value) {
                                            return value.toLocaleString();
                                        }
                                    }
                                }
                            },
                            plugins: {
                                legend: {
                                    position: "top",
                                },
                                tooltip: {
                                    callbacks: {
                                        label: function(context) {
                                            const label = context.dataset.label || "";
                                            const value = context.raw || 0;
                                            const total = context.chart.data.datasets
                                                .map(dataset => dataset.data[context.dataIndex])
                                                .reduce((a, b) => a + b, 0);
                                            const percentage = Math.round((value / total) * 100);
                                            return `${label}: ${value.toLocaleString()} (${percentage}%)`;
                                        }
                                    }
                                }
                            }
                        }
                    });
                    
                    // Breakdown Line Chart
                    const breakdownLineCtx = document.getElementById("breakdownLineChart").getContext("2d");
                    const breakdownLineChart = new Chart(breakdownLineCtx, {
                        type: "line",
                        data: {
                            labels: '.json_encode($chartLabels).',
                            datasets: '.json_encode($breakdownDatasets).'
                        },
                        options: {
                            responsive: true,
                            maintainAspectRatio: false,
                            scales: {
                                x: {
                                    grid: {
                                        display: false
                                    }
                                },
                                y: {
                                    beginAtZero: true,
                                    ticks: {
                                        callback: function(value) {
                                            return value.toLocaleString();
                                        }
                                    }
                                }
                            },
                            plugins: {
                                legend: {
                                    position: "top",
                                },
                                tooltip: {
                                    callbacks: {
                                        label: function(context) {
                                            const label = context.dataset.label || "";
                                            const value = context.raw || 0;
                                            const total = context.chart.data.datasets
                                                .map(dataset => dataset.data[context.dataIndex])
                                                .reduce((a, b) => a + b, 0);
                                            const percentage = Math.round((value / total) * 100);
                                            return `${label}: ${value.toLocaleString()} (${percentage}%)`;
                                        }
                                    }
                                }
                            }
                        }
                    });
                    
                    // Tab functionality
                    const tabs = document.querySelectorAll(".tab");
                    tabs.forEach(tab => {
                        tab.addEventListener("click", function() {
                            // Remove active class from all tabs and contents
                            document.querySelectorAll(".tab").forEach(t => t.classList.remove("active"));
                            document.querySelectorAll(".tab-content").forEach(c => c.classList.remove("active"));
                            
                            // Add active class to clicked tab and corresponding content
                            this.classList.add("active");
                            const tabId = this.getAttribute("data-tab");
                            document.getElementById(`tab-${tabId}`).classList.add("active");
                        });
                    });
                });
              </script>';
        
    } catch (PDOException $e) {
        echo '<div class="card">
                <div style="color:red; padding: 20px;">Error: '.$e->getMessage().'</div>
              </div>';
        if (isset($stmt)) {
            echo '<div class="card">
                    <div style="padding: 20px;">Query: '.htmlspecialchars($stmt->queryString).'</div>
                  </div>';
        }
    }
    ?>
</body>
</html>

