<?php include("header.php")?>
<?php include("./db.php")?>

<h3 class="p-2">All Sales</h3> 

<?php
$connection = new mysqli($dbhost,$dbusername,$dbpassword,$dbname); 
if($connection->connect_error){ die("Database connection error: ".$connection->connect_error); }

// Pagination settings
$limit = 50;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
if ($page < 1) $page = 1;
$offset = ($page - 1) * $limit;

// Filter values
$selectedProduct  = isset($_POST['product_name']) ? (int)$_POST['product_name'] : 0;
$selectedCategory = isset($_POST['category_name']) ? (int)$_POST['category_name'] : 0;
$fromDate = isset($_POST['from_date']) ? $_POST['from_date'] : '';
$toDate   = isset($_POST['to_date']) ? $_POST['to_date'] : '';

// Base WHERE clause
$where = "WHERE m.Order_Status='Confirmed'";

// Apply product filter
if ($selectedProduct > 0) {
    $where .= " AND p.ID = $selectedProduct";
}

// Apply category filter
if ($selectedCategory > 0) {
    $where .= " AND p.Category = $selectedCategory"; // Adjust column name if different
}

// Apply date filter
if ($fromDate && $toDate) {
    $where .= " AND m.Date BETWEEN '$fromDate 00:00:00' AND '$toDate 23:59:59'";
}

// Get totals for current page
$totalsSql = "
SELECT SUM(s.Quantity) AS total_quantity,
       SUM(s.Amount) AS total_amount
FROM invoice m
INNER JOIN orders s ON m.Invoice = s.Invoice
INNER JOIN product p ON s.Product = p.ID
$where
LIMIT $limit OFFSET $offset
";
$totalsResult = $connection->query($totalsSql);
$totals = $totalsResult->fetch_assoc();
$totalQuantity = $totals['total_quantity'] ?? 0;
$totalAmount = $totals['total_amount'] ?? 0;

// Total rows for pagination
$countSql = "SELECT COUNT(*) AS total 
             FROM invoice m
             INNER JOIN orders s ON m.Invoice = s.Invoice
             INNER JOIN product p ON s.Product = p.ID
             $where";
$countResult = $connection->query($countSql);
$totalRows = $countResult->fetch_assoc()['total'];
$totalPages = ceil($totalRows / $limit);

// Correct page if too high
if ($page > $totalPages) $page = $totalPages;

// Main table query
$sql = "
SELECT m.Invoice,
       s.ID AS orderid,
       m.Date,
       m.OrderType,
       p.Name AS Product_name,
       s.AmountPerPC AS Price,
       s.Quantity,
       s.Amount
FROM invoice m
INNER JOIN orders s ON m.Invoice = s.Invoice
INNER JOIN product p ON s.Product = p.ID
$where
LIMIT $limit OFFSET $offset
";
$result = $connection->query($sql);
?>

<div class="container-fluid">
  <div class="row">
    <!-- TOTAL QUANTITY -->
    <div class="col-lg-4">
      <div class="card shadow p-4 my-2">
        <h3 class="fw-bold" id="total_quantity"><?= $totalQuantity ?></h3>
        <p class="text-secondary p-0 m-0">Total Quantity</p>
      </div>
    </div>

    <!-- TOTAL AMOUNT -->
    <div class="col-lg-4">
      <div class="card shadow p-4 my-2">
        <h3 class="fw-bold" id="total_amount"><?= $totalAmount ?></h3>
        <p class="text-secondary p-0 m-0">Total Amount</p>
      </div>
    </div>
  </div>

  <!-- Filter Form -->
  <div class="row py-3">
    <div class="col-lg-6 col-md-6">
      <form method="POST" action="">
        <div class="row">
          <div class="col-lg-4 mb-2">
            <label class="form-label">From:</label>
            <input type="date" class="form-control" name="from_date" value="<?= htmlspecialchars($fromDate) ?>">
          </div>
          <div class="col-lg-4 mb-2">
            <label class="form-label">To:</label>
            <input type="date" class="form-control" name="to_date" value="<?= htmlspecialchars($toDate) ?>">
          </div>
          <div class="col-lg-4 mb-2">
            <button class="btn btn-primary" style="margin-top:33px;" type="submit">
              <i class="fa-solid fa-eye"></i> View
            </button>
          </div>
        </div>
      </form>
    </div>

    <div class="col-lg-6 col-md-6">
      <form method="POST" action="">
        <div class="row">
          <div class="col-lg-4 mb-2">
            <label class="form-label">Search Category:</label>
            <select class="form-control" name="category_name">
              <option value="0">All</option>
              <?php
              $get_cat="SELECT * FROM Category";
              $data=$connection->query($get_cat);
              while($cat=mysqli_fetch_array($data)){ ?>
                <option value="<?= $cat['ID'] ?>" <?= ($cat['ID'] == $selectedCategory ? 'selected' : '') ?>><?= $cat['Name'] ?></option>
              <?php } ?>
            </select>
          </div>

          <div class="col-lg-4 mb-2">
            <label class="form-label">Search Product:</label>
            <select class="form-control" name="product_name">
              <option value="0">All</option>
              <?php
              $get_pro="SELECT * FROM product";
              $data=$connection->query($get_pro);
              while($pro=mysqli_fetch_array($data)){ ?>
                <option value="<?= $pro['ID'] ?>" <?= ($pro['ID'] == $selectedProduct ? 'selected' : '') ?>><?= $pro['Name'] ?></option>
              <?php } ?>
            </select>
          </div>

          <div class="col-lg-4 mb-2">
            <button class="btn btn-primary" style="margin-top:33px;" type="submit">
              <i class="fa-solid fa-eye"></i> View
            </button>
          </div>
        </div>
      </form>
    </div>
  </div>

  <!-- Table -->
  <table class="table table-hover rounded-3">
    <thead class="table-secondary">
      <tr>
        <th>Order ID</th>
        <th>Date</th>
        <th>Order Type</th>
        <th>Product Name</th>
        <th>Price</th>
        <th>Quantity</th>
        <th>Amount</th>
      </tr>
    </thead>
    <tbody>
      <?php while($row = mysqli_fetch_assoc($result)){ ?>
        <tr class="table-light">
          <td><?= $row['orderid'] ?></td>
          <td><?= $row['Date'] ?></td>
          <td><?= $row['OrderType'] ?></td>
          <td><?= $row['Product_name'] ?></td>
          <td><?= $row['Price'] ?></td>
          <td><?= $row['Quantity'] ?></td>
          <td><?= $row['Amount'] ?></td>
        </tr>
      <?php } ?>
    </tbody>
  </table>

  <!-- Pagination -->
  <nav aria-label="Page navigation example">
    <ul class="pagination d-flex justify-content-center">
      <li class="page-item <?= ($page <= 1) ? 'disabled' : '' ?>">
        <a class="page-link" href="?page=<?= $page-1 ?>">Previous</a>
      </li>

      <?php for ($i = 1; $i <= $totalPages; $i++): ?>
        <li class="page-item <?= ($i == $page) ? 'active' : '' ?>">
          <a class="page-link" href="?page=<?= $i ?>"><?= $i ?></a>
        </li>
      <?php endfor; ?>

      <li class="page-item <?= ($page >= $totalPages) ? 'disabled' : '' ?>">
        <a class="page-link" href="?page=<?= $page+1 ?>">Next</a>
      </li>
    </ul>
  </nav>
</div>

<?php include("footer.php") ?>
