<?php
session_start();
require_once '../config.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit;
}

function e($v) {
    return htmlspecialchars((string)($v ?? ''), ENT_QUOTES, 'UTF-8');
}

function fmtDate($d) {
    if (!$d) return '-';
    $ts = strtotime($d);
    return $ts ? date('d M Y', $ts) : e($d);
}

$user_id = (int)($_SESSION['user_id'] ?? 0);
$user_country = '';
$country_id = 0;
$country_name = '';
$error_msg = '';

// Resolve user's country in a tolerant way (id / ISO code / name)
$stmt = $conn->prepare("SELECT country FROM users WHERE id=? LIMIT 1");
if ($stmt) {
    $stmt->bind_param('i', $user_id);
    $stmt->execute();
    $res = $stmt->get_result();
    if ($u = $res->fetch_assoc()) {
        $user_country = trim((string)($u['country'] ?? ''));
    }
    $stmt->close();
}

if ($user_country !== '') {
    if (ctype_digit($user_country)) {
        $country_id = (int)$user_country;
        $q = $conn->prepare("SELECT country_name FROM countries WHERE id=? LIMIT 1");
        if ($q) {
            $q->bind_param('i', $country_id);
            $q->execute();
            $r = $q->get_result();
            if ($row = $r->fetch_assoc()) {
                $country_name = (string)($row['country_name'] ?? '');
            }
            $q->close();
        }
    } else {
        $q = $conn->prepare("SELECT id, country_name FROM countries WHERE LOWER(iso_code)=LOWER(?) LIMIT 1");
        if ($q) {
            $q->bind_param('s', $user_country);
            $q->execute();
            $r = $q->get_result();
            if ($row = $r->fetch_assoc()) {
                $country_id = (int)$row['id'];
                $country_name = (string)($row['country_name'] ?? '');
            }
            $q->close();
        }

        if ($country_id <= 0) {
            $q = $conn->prepare("SELECT id, country_name FROM countries WHERE LOWER(country_name)=LOWER(?) LIMIT 1");
            if ($q) {
                $q->bind_param('s', $user_country);
                $q->execute();
                $r = $q->get_result();
                if ($row = $r->fetch_assoc()) {
                    $country_id = (int)$row['id'];
                    $country_name = (string)($row['country_name'] ?? '');
                }
                $q->close();
            }
        }
    }
}

// Search
$q = trim($_GET['q'] ?? '');
$like = '%' . $q . '%';

// Expiry window
$window_days = 30;

$alerts = [];
$total = 0;
$expiredCount = 0;
$soonCount = 0;

// Build query in safe variants
$baseSql = "
    SELECT
        p.id,
        p.product_name,
        p.brand_name,
        p.registration_number,
        p.registration_date,
        p.registration_exp_date,
        c.country_name,
        DATEDIFF(p.registration_exp_date, CURDATE()) AS days_left
    FROM products p
    LEFT JOIN countries c ON c.id = p.country_id
    WHERE p.status = 'approved'
      AND p.registration_exp_date IS NOT NULL
      AND p.registration_exp_date <= DATE_ADD(CURDATE(), INTERVAL {$window_days} DAY)
";

if ($country_id > 0 && $q !== '') {
    $sql = $baseSql . "
      AND p.country_id = ?
      AND (
            p.product_name LIKE ?
         OR p.brand_name LIKE ?
         OR p.registration_number LIKE ?
      )
      ORDER BY p.registration_exp_date ASC, p.product_name ASC
    ";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param('isss', $country_id, $like, $like, $like);
    }
} elseif ($country_id > 0) {
    $sql = $baseSql . "
      AND p.country_id = ?
      ORDER BY p.registration_exp_date ASC, p.product_name ASC
    ";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param('i', $country_id);
    }
} elseif ($q !== '') {
    $sql = $baseSql . "
      AND (
            p.product_name LIKE ?
         OR p.brand_name LIKE ?
         OR p.registration_number LIKE ?
      )
      ORDER BY p.registration_exp_date ASC, p.product_name ASC
    ";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param('sss', $like, $like, $like);
    }
} else {
    $sql = $baseSql . "
      ORDER BY p.registration_exp_date ASC, p.product_name ASC
    ";
    $stmt = $conn->prepare($sql);
}

if (!$stmt) {
    $error_msg = 'Database prepare failed: ' . $conn->error;
} else {
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $days_left = (int)($row['days_left'] ?? 0);
        if ($days_left < 0) {
            $expiredCount++;
        } else {
            $soonCount++;
        }
        $alerts[] = $row;
    }
    $stmt->close();
}

$total = count($alerts);

$country_notice = '';
if ($country_id <= 0) {
    $country_notice = 'Your country could not be detected, so alerts are shown for all approved products.';
} elseif ($country_name !== '') {
    $country_notice = 'Showing expiry alerts for ' . $country_name . '.';
}
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Expire Alerts</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
<style>
/* ===== ROOT ===== */
:root{
    --theme:#374155;
    --theme-dark:#2c3445;

    --bg:#eef2f7;
    --card:#ffffff;
    --text:#0f172a;
    --muted:#64748b;
    --border:#e2e8f0;

    --danger:#dc2626;
    --warn:#d97706;
    --ok:#16a34a;
}

/* ===== RESET ===== */
*{
    box-sizing:border-box;
    margin:0;
    padding:0;
}

body{
    font-family:Inter, Arial, sans-serif;
    background:linear-gradient(180deg,#eef2f7,#e9eef6);
    color:var(--text);
}

/* ===== FIX HEADER SPACE ===== */
.container{
    margin-left:260px;
    padding:100px 24px 24px; /* ðŸ”¥ header fix */
}

/* ===== CARD ===== */
.page-card{
    background:var(--card);
    border-radius:18px;
    padding:20px;
    box-shadow:0 10px 30px rgba(0,0,0,0.06);
    border:1px solid rgba(148,163,184,.15);
}

/* ===== HEADER ===== */
.page-header{
    display:flex;
    justify-content:space-between;
    align-items:center;
    margin-bottom:18px;
    flex-wrap:wrap;
    gap:10px;
}

.title-wrap h2{
    font-size:22px;
    font-weight:800;
    display:flex;
    align-items:center;
    gap:8px;
    color:var(--theme);
}

.subtitle{
    font-size:13px;
    color:var(--muted);
    margin-top:4px;
}

/* ===== BADGE ===== */
.badge{
    background:var(--theme);
    color:#fff;
    padding:8px 14px;
    border-radius:999px;
    font-weight:700;
    display:flex;
    align-items:center;
    gap:6px;
    font-size:13px;
}

/* ===== TOOLBAR ===== */
.toolbar{
    display:flex;
    justify-content:space-between;
    gap:10px;
    flex-wrap:wrap;
    margin:14px 0;
}

/* ===== SEARCH ===== */
.search-box{
    display:flex;
    align-items:center;
    gap:8px;
    background:#fff;
    border:1px solid var(--border);
    border-radius:10px;
    padding:10px 12px;
    width:100%;
    max-width:450px;
}

.search-box input{
    border:none;
    outline:none;
    width:100%;
    font-size:13px;
}

.search-box button{
    border:none;
    background:none;
    font-weight:700;
    cursor:pointer;
}

/* ===== BUTTON ===== */
.btn-link{
    background:#fff;
    border:1px solid var(--border);
    padding:10px 14px;
    border-radius:10px;
    font-size:13px;
    font-weight:600;
    display:flex;
    align-items:center;
    gap:6px;
}

.btn-link:hover{
    background:#f1f5f9;
}

/* ===== STATS ===== */
.stats{
    display:grid;
    grid-template-columns:repeat(3,1fr);
    gap:12px;
    margin-bottom:16px;
}

.stat{
    background:#fff;
    border-radius:12px;
    padding:14px;
    border:1px solid var(--border);
}

.stat .label{
    font-size:11px;
    color:var(--muted);
    font-weight:600;
}

.stat .value{
    font-size:22px;
    font-weight:800;
    margin-top:4px;
}

/* COLORS */
.stat.expired .value{color:var(--danger);}
.stat.soon .value{color:var(--warn);}
.stat.total .value{color:var(--theme);}

/* ===== NOTICE ===== */
.notice{
    background:#f8fafc;
    border:1px solid var(--border);
    padding:10px 12px;
    border-radius:10px;
    color:var(--muted);
    margin-bottom:16px;
}

/* ===== TABLE ===== */
.table-wrap{
    width:100%;
    overflow-x:auto; /* ðŸ”¥ scroll fix */
}

table{
    width:100%;
    min-width:1000px; /* ðŸ”¥ force scroll */
    border-collapse:separate;
    border-spacing:0 8px;
}

/* HEADER */
th{
    background:var(--theme);
    color:#fff;
    padding:12px;
    font-size:12px;
    text-align:left;
}

th:first-child{border-radius:10px 0 0 10px;}
th:last-child{border-radius:0 10px 10px 0;}

/* BODY */
td{
    background:#f8fafc;
    padding:12px;
    font-size:13px;
    border-top:1px solid var(--border);
    border-bottom:1px solid var(--border);
}

td:first-child{
    border-left:1px solid var(--border);
    border-radius:10px 0 0 10px;
}

td:last-child{
    border-right:1px solid var(--border);
    border-radius:0 10px 10px 0;
}

/* HOVER */
tr:hover td{
    background:#e2e8f0;
}

/* PRODUCT */
.product{
    font-weight:700;
}

/* STATUS */
.badge-status{
    padding:6px 10px;
    border-radius:999px;
    font-size:11px;
    font-weight:700;
}

/* STATUS COLORS */
.badge-expired{
    background:rgba(220,38,38,.1);
    color:var(--danger);
}

.badge-soon{
    background:rgba(217,119,6,.1);
    color:var(--warn);
}

.badge-safe{
    background:rgba(22,163,74,.1);
    color:var(--ok);
}

/* DAYS */
.days-left{
    font-weight:700;
}

.days-left.expired{color:var(--danger);}
.days-left.soon{color:var(--warn);}
.days-left.safe{color:var(--ok);}

/* EMPTY */
.empty{
    text-align:center;
    padding:20px;
    color:var(--muted);
}

/* ERROR */
.error{
    background:#fef2f2;
    border:1px solid #fecaca;
    color:#991b1b;
    padding:10px;
    border-radius:10px;
}

/* ===== MOBILE ===== */
@media(max-width:900px){
    .container{
        margin-left:0;
        padding:90px 12px 12px;
    }

    .stats{
        grid-template-columns:1fr;
    }
}
</style>
</head>
<body>

<?php include 'agencysidebar.php'; ?>
<?php include '../header.php'; ?>

<div class="container">
    <div class="page-card">
        <div class="page-header">
            <div class="title-wrap">
                <h2><i class="fa-solid fa-triangle-exclamation"></i> Expire Alerts</h2>
                <div class="subtitle">Commercial Products with registration expiry within the next 30 days.</div>
            </div>
            <div class="badge"><i class="fa-solid fa-bell"></i> <?php echo (int)$total; ?> Alerts</div>
        </div>

        <div class="toolbar">
            <form method="get" style="width:100%;max-width:560px;">
                <div class="search-box">
                    <i class="fa-solid fa-magnifying-glass"></i>
                    <input type="text" id="liveSearch" placeholder="Search product, brand..." autocomplete="off">
                    <button type="submit" style="border:none;background:none;padding:0;cursor:pointer;color:#0f172a;font-weight:700;">Search</button>
                </div>
            </form>
            <a class="btn-link" href="expire-alert.php"><i class="fa-solid fa-rotate-right"></i> Reset</a>
        </div>

        <div class="stats">
            <div class="stat expired">
                <div class="label">Expired</div>
                <div class="value"><?php echo (int)$expiredCount; ?></div>
            </div>
            <div class="stat soon">
                <div class="label">Expiring Soon</div>
                <div class="value"><?php echo (int)$soonCount; ?></div>
            </div>
            <div class="stat total">
                <div class="label">Total Alerts</div>
                <div class="value"><?php echo (int)$total; ?></div>
            </div>
        </div>

        <?php if ($country_notice): ?>
            <div class="notice"><?php echo e($country_notice); ?></div>
        <?php endif; ?>

        <?php if ($error_msg): ?>
            <div class="error"><?php echo e($error_msg); ?></div>
        <?php endif; ?>

        <div class="table-wrap">
            <table>
                <thead>
                    <tr>
                        <th>No.</th>
                        <th>Product</th>
                        <th>Brand</th>
                        <th>Country</th>
                        <th>Registration No.</th>
                        <th>Registration Date</th>
                        <th>Expiry Date</th>
                        <th>Days Left</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($alerts)): ?>
<tr>
    <td colspan="9">
        <div class="empty">
            <i class="fa-regular fa-circle-check"></i>
            No expiry alerts found.
        </div>
    </td>
</tr>
<?php else: ?>

<?php $i = 1; ?>
<?php foreach ($alerts as $row): 
    $days_left = (int)($row['days_left'] ?? 0);
    $isExpired = $days_left < 0;
    $isSoon = !$isExpired && $days_left <= 7;

    $statusClass = $isExpired ? 'badge-expired' : ($isSoon ? 'badge-soon' : 'badge-safe');
    $daysClass = $isExpired ? 'expired' : ($isSoon ? 'soon' : 'safe');
    $statusText = $isExpired ? 'Expired' : ($isSoon ? 'Expiring Soon' : 'Active');
?>
<tr>
    <td><?php echo $i++; ?></td>
    <td><div class="product"><?php echo e($row['product_name']); ?></div></td>
    <td><?php echo e($row['brand_name']); ?></td>
    <td><?php echo e($row['country_name']); ?></td>
    <td><?php echo e($row['registration_number']); ?></td>
    <td><?php echo fmtDate($row['registration_date']); ?></td>
    <td><?php echo fmtDate($row['registration_exp_date']); ?></td>
    <td>
        <span class="days-left <?php echo $daysClass; ?>">
            <?php echo $isExpired ? abs($days_left).' days ago' : $days_left.' days'; ?>
        </span>
    </td>
    <td>
        <span class="badge-status <?php echo $statusClass; ?>">
            <i class="fa-solid fa-circle"></i>
            <?php echo $statusText; ?>
        </span>
    </td>
</tr>
<?php endforeach; ?>

<?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
<script>
document.getElementById("liveSearch").addEventListener("keyup", function() {
    let value = this.value.toLowerCase();
    let rows = document.querySelectorAll("tbody tr");

    rows.forEach(row => {
        let text = row.innerText.toLowerCase();
        row.style.display = text.includes(value) ? "" : "none";
    });
});
</script>
</body>
</html>