<?php
include '../config/koneksi.php';

$newOrdersQuery = mysqli_query($conn, "SELECT COUNT(*) AS total 
FROM orders 
WHERE status NOT IN ('Selesai', 'Dikirim')");
$newOrders = mysqli_fetch_assoc($newOrdersQuery)['total'];

$productsQuery = mysqli_query($conn, "SELECT COUNT(*) AS total FROM products WHERE stok > 0");
$productsAvailable = mysqli_fetch_assoc($productsQuery)['total'];

$activeMembershipQuery = mysqli_query($conn, "SELECT COUNT(*) AS total FROM member_subscriptions WHERE status_langganan = 'aktif'");
$activeMembership = mysqli_fetch_assoc($activeMembershipQuery)['total'];

$soldProductsQuery = mysqli_query($conn, "SELECT SUM(jumlah) AS total FROM order_items");
$soldProducts = mysqli_fetch_assoc($soldProductsQuery)['total'] ?? 0;
?>

<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/jquery.dataTables.min.css">

<div class="content-wrapper">

  <section class="content-header">
    <div class="container-fluid">
      <div class="row mb-2">
        <div class="col-sm-6">
          <h1>Dashboard</h1>
        </div>
      </div>
    </div>
  </section>

  <section class="content">
    <div class="container-fluid">

  <div class="row">
    <div class="col-lg-3 col-6">
      <div class="small-box bg-info">
        <div class="inner">
          <h3><?= $newOrders ?></h3>
          <p>New Orders</p>
        </div>
        <div class="icon">
          <i class="fas fa-shopping-cart"></i>
        </div>
        <a href="../order/order.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
      </div>
    </div>

    <div class="col-lg-3 col-6">
      <div class="small-box bg-success">
        <div class="inner">
          <h3><?= $productsAvailable ?></h3>
          <p>Products Available</p>
        </div>
        <div class="icon">
          <i class="fas fa-th"></i>
        </div>
        <a href="../produk/produk.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
      </div>
    </div>

    <div class="col-lg-3 col-6">
      <div class="small-box bg-warning">
        <div class="inner">
          <h3><?= $activeMembership ?></h3>
          <p>Active Membership</p>
        </div>
        <div class="icon">
          <i class="fas fa-user-plus"></i>
        </div>
        <a href="../member/member.php" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
      </div>
    </div>

    <div class="col-lg-3 col-6">
      <div class="small-box bg-danger">
        <div class="inner">
          <h3><?= $soldProducts ?></h3>
          <p>Sold Products</p>
        </div>
        <div class="icon">
          <i class="fas fa-chart-pie"></i>
        </div>
        <a href="#" class="small-box-footer">More info <i class="fas fa-arrow-circle-right"></i></a>
      </div>
    </div>
  </div>


      <div class="row mt-4">
        <div class="col-12">
          <div class="card">
            <div class="card-header">
              <h3 class="card-title">Data Member Subscription</h3>
            </div>
            <div class="card-body">
              <table id="tabelMembership" class="table table-bordered table-striped">
                <thead>
                  <tr>
                    <th>No</th>
                    <th>Nama Member</th>
                    <th>Paket Membership</th>
                    <th>Tanggal Berakhir</th>
                    <th>Status</th>
                  </tr>
                </thead>
                <tbody>
                  <?php
                  include '../config/koneksi.php';
                  $no = 1;

                  $query = "SELECT ms.*, m.nama AS nama_member, mp.package_name 
                            FROM member_subscriptions ms
                            JOIN members m ON ms.member_id = m.member_id
                            JOIN membership_packages mp ON ms.package_id = mp.package_id
                            ORDER BY ms.tanggal_berakhir DESC";


                  $result = mysqli_query($conn, $query);
                  while ($row = mysqli_fetch_assoc($result)) {
                      $status = ucfirst($row['status_langganan']);
                      $tanggal = date('d-m-Y', strtotime($row['tanggal_berakhir']));
                      $badge = ($status == 'Aktif') ? 'success' : 'danger';
                  ?>
                  <tr>
                    <td><?= $no++ ?></td>
                    <td><?= htmlspecialchars($row['nama_member']) ?></td>
                    <td><?= htmlspecialchars($row['package_name']) ?></td>
                    <td><?= $tanggal ?></td>
                    <td><span class="badge badge-<?= $badge ?>"><?= $status ?></span></td>
                  </tr>
                  <?php } ?>
                </tbody>
              </table>
            </div>
          </div>
        </div>
      </div>

    </div>
  </section>
</div>

<script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function () {
    $('#tabelMembership').DataTable({
        ordering: false,
        paging: false,
        info: false,
        language: {
            search: "Cari Member:",
            zeroRecords: "Data tidak ditemukan",
        }
    });
});
</script>
