<?php
session_start();
if (!isset($_SESSION['admin'])) {
  header("Location: login_admin.php");
  exit;
}

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$successCount = 0;
$failCount = 0;

<?php
$host = "localhost";
$user = "odmr9632_userdb";
$pass = "@Sukabumi2018";
$db   = "odmr9632_order_db";

$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) {
  die("Koneksi gagal: " . $conn->connect_error);
}
?>
if ($conn->connect_error) {
  die("Koneksi database gagal: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
  $fileTmp = $_FILES['excel_file']['tmp_name'];
  $spreadsheet = IOFactory::load($fileTmp);
  $sheet = $spreadsheet->getActiveSheet();
  $rows = $sheet->toArray();

  foreach ($rows as $index => $row) {
    if ($index === 0) continue; // Lewati baris header
    $resi = $conn->real_escape_string($row[0]); // kolom A
    $status = $conn->real_escape_string($row[4]); // kolom E
    $nama = trim($conn->real_escape_string($row[12])); // kolom M
    $telepon = preg_replace('/\D/', '', $row[13]); // kolom N (angka saja)
    $teleponAkhir = substr($telepon, -4); // ambil 4 digit terakhir

    $query = "SELECT id FROM orders WHERE nama_customer LIKE '%$nama%' AND RIGHT(no_wa, 4) = '$teleponAkhir'";
    $result = $conn->query($query);
    if ($result && $result->num_rows > 0) {
      while ($data = $result->fetch_assoc()) {
        $id = $data['id'];
        $update = "UPDATE orders SET no_resi='$resi', status_paket='$status' WHERE id='$id'";
        if ($conn->query($update)) {
          $successCount++;
        } else {
          $failCount++;
        }
      }
    } else {
      $failCount++;
    }
  }
  echo "<script>alert('Proses selesai! Berhasil update: $successCount, Tidak ada Kecocokan: $failCount');</script>";
}
?>

<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Update Resi Otomatis</title>
  <style>
    body {
      font-family: Arial, sans-serif;
      background: #f5f5f5;
      display: flex;
      justify-content: center;
      align-items: center;
      height: 100vh;
    }
    .container {
      background: white;
      padding: 30px;
      border-radius: 10px;
      box-shadow: 0 0 15px rgba(0,0,0,0.1);
      width: 100%;
      max-width: 500px;
      text-align: center;
    }
    h2 {
      color: green;
      margin-bottom: 20px;
    }
    .upload-wrapper {
      display: flex;
      flex-direction: column;
      align-items: center;
      margin-bottom: 15px;
    }
    .file-label {
      font-weight: bold;
      margin-bottom: 10px;
    }
    .file-input-wrapper {
      display: flex;
      justify-content: center;
      align-items: center;
      gap: 10px;
    }
    .file-input {
      font-size: 14px;
    }
    .file-note {
      font-size: 12px;
      margin-top: 10px;
      color: gray;
    }
    .submit-btn, .back-btn {
      margin-top: 20px;
      padding: 10px 20px;
      font-size: 16px;
      border: none;
      border-radius: 6px;
      cursor: pointer;
    }
    .submit-btn {
      background: #4CAF50;
      color: white;
    }
    .submit-btn:hover {
      background-color: #45a049;
    }
    .back-btn {
      display: inline-block;
      background: gray;
      color: white;
      text-decoration: none;
    }
  </style>
</head>
<body>
  <div class="container">
    <h2>📦 Update Resi Otomatis</h2>
    <form method="POST" enctype="multipart/form-data">
      <div class="upload-wrapper">
        <div class="file-label">Pilih file Excel (.xlsx atau .xls)</div>
        <div class="file-input-wrapper">
          <input type="file" name="excel_file" id="excelFile" class="file-input" required>
        </div>
      </div>
      <button type="submit" class="submit-btn">Upload & Proses</button>
    </form>
    <a href="menu_admin.php" class="back-btn">⬅️ Kembali ke Menu Admin</a>
  </div>
</body>
</html>
