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

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

function e($v){ return htmlspecialchars($v ?? '', ENT_QUOTES); }

/* GET USER COUNTRY (value stored on users.country could be:
   - a country id (numeric)
   - an ISO code like 'IN'
   - a country name like 'India'
*/
$user_id = (int)($_SESSION['user_id'] ?? 0);
$user_country = '';

$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();
    $u = $res->fetch_assoc();
    $user_country = trim((string)($u['country'] ?? ''));
    $stmt->close();
}

/* Resolve country_id from users.country in a tolerant way */
$country_id = 0;

if ($user_country !== '') {
    // 1) If numeric, assume it's already a country id
    if (ctype_digit($user_country)) {
        $country_id = (int)$user_country;
    } else {
        // 2) Try matching iso_code (if exists) - case-insensitive
        $q = $conn->prepare("SELECT id 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'];
            }
            $q->close();
        }
        // 3) If still not found, try matching by country_name (case-insensitive)
        if ($country_id === 0) {
            $q2 = $conn->prepare("SELECT id FROM countries WHERE LOWER(country_name)=LOWER(?) LIMIT 1");
            if ($q2) {
                $q2->bind_param("s", $user_country);
                $q2->execute();
                $r2 = $q2->get_result();
                if ($row2 = $r2->fetch_assoc()) {
                    $country_id = (int)$row2['id'];
                }
                $q2->close();
            }
        }
    }
}

/* GET GLOBAL STOCK LIMIT */
$limit = 0;
$r = $conn->query("SELECT stock_limit FROM stock_limits LIMIT 1");
if ($r && ($row = $r->fetch_assoc())) {
    $limit = (int)$row['stock_limit'];
}

/* find which stock column exists in products table (safe list) */
$possible_stock_cols = ['qty','stock','quantity','stock_qty','available_qty'];
$found_stock_col = null;

$cols = $conn->query("SHOW COLUMNS FROM products");
if ($cols) {
    $columns = [];
    while ($c = $cols->fetch_assoc()) {
        $columns[] = $c['Field'];
    }
    foreach ($possible_stock_cols as $cname) {
        if (in_array($cname, $columns)) {
            $found_stock_col = $cname;
            break;
        }
    }
}

/* If we couldn't detect a stock column, prepare a friendly message and do not run query */
$products = [];
$error_msg = '';

if ($country_id <= 0) {
    
} elseif ($found_stock_col === null) {
    $error_msg = "Products table does not contain a recognized stock column (tried: " . implode(', ', $possible_stock_cols) . "). ";
    $error_msg .= "Add a column named <code>qty</code> (INT) or update the code to use your column name.";
} else {
    // Build SQL safely: column name already validated from list
    $stock_col = $found_stock_col;
    $sql = "
        SELECT
            p.id,
            p.product_name,
            p.brand_name,
            p.$stock_col AS stock_value,
            c.country_name
        FROM products p
        LEFT JOIN countries c ON c.id = p.country_id
        WHERE p.country_id = ?
          AND p.$stock_col <= ?
        ORDER BY p.product_name ASC
    ";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("ii", $country_id, $limit);
        $stmt->execute();
        $res = $stmt->get_result();
        while ($row = $res->fetch_assoc()) {
            // normalize output to 'qty' key for template
            $row['qty'] = (int)$row['stock_value'];
            unset($row['stock_value']);
            $products[] = $row;
        }
        $stmt->close();
    } else {
        $error_msg = "Database prepare failed: " . $conn->error;
    }
}
?>
<!doctype html>
<html>
<head>

<meta charset="utf-8">
<title>Stock Alerts</title>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">

<style>

body{
font-family:Inter,Arial;
background:#eef2f7;
margin:0;
padding:90px 25px;
}

.container{
margin-left:260px;
}

.card{
background:#fff;
border-radius:14px;
padding:25px;
box-shadow:0 10px 25px rgba(0,0,0,0.05);
}

h2{
margin:0 0 20px;
font-size:24px;
color:#1e293b;
}

.table{
width:100%;
border-collapse:separate;
border-spacing:0 12px;
}

.table th{
background:#334155;
color:#fff;
padding:14px;
text-align:left;
font-size:13px;
}

.table td{
background:#f8fafc;
padding:14px;
font-size:14px;
}

.low{
color:#dc2626;
font-weight:600;
}

.notice{
background:#fff7ed;
border:1px solid #f5d6b3;
padding:10px 12px;
border-radius:8px;
color:#92400e;
margin-bottom:12px;
}

.small-muted { color:#6b7280; font-size:13px; }

</style>

</head>

<body>

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

<div class="container">

<div class="card">

<h2>Low Stock Alerts</h2>

<?php if ($error_msg): ?>
  <div class="notice">
    <?php echo $error_msg; ?>
    <div class="small-muted" style="margin-top:8px;">
      Quick fixes:
      <ul>
        <li>If <strong>users.country</strong> contains ISO codes (IN, US) add <code>iso_code</code> to <code>countries</code> and update values.</li>
        <li>To add qty column: <code>ALTER TABLE products ADD COLUMN qty INT NOT NULL DEFAULT 0;</code></li>
      </ul>
    </div>
  </div>
<?php endif; ?>

<table class="table">

<thead>

<tr>
<th>ID</th>
<th>Product</th>
<th>Brand</th>
<th>Country</th>
<th>Stock</th>
<th>Status</th>
</tr>

</thead>

<tbody>

<?php if (empty($products)): ?>

<tr>
<td colspan="6">No low stock alerts</td>
</tr>

<?php endif; ?>

<?php foreach($products as $p): ?>

<tr>

<td><?php echo (int)$p['id']; ?></td>

<td><?php echo e($p['product_name']); ?></td>

<td><?php echo e($p['brand_name']); ?></td>

<td><?php echo e($p['country_name']); ?></td>

<td><?php echo (int)$p['qty']; ?></td>

<td class="low">
<i class="fa fa-exclamation-triangle"></i>
Low Stock
</td>

</tr>

<?php endforeach; ?>

</tbody>

</table>

</div>

</div>

</body>
</html>