<?php
session_start();
require_once '../config.php';
/* ================= EXPORT TO EXCEL (AGENCY LOW STOCK) ================= */
if (isset($_GET['export']) && $_GET['export'] == 1) {

    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=agency_low_stock.xls");

    echo "SR\tProduct Name\tGeneric Name\tBrand Name\tCountry\tQty\tUnits/Packet\tPrice/Packet\n";

    $sr = 1;

    /* ✅ GET AGENCY COUNTRY */
    $userId = (int)$_SESSION['user_id'];

    $stmt = $conn->prepare("SELECT country FROM users WHERE id=? LIMIT 1");
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $user = $stmt->get_result()->fetch_assoc();

    $countryName = trim($user['country'] ?? '');

    $countryId = null;
    if ($countryName !== '') {
        $stmt = $conn->prepare("SELECT id FROM countries WHERE LOWER(country_name)=LOWER(?) LIMIT 1");
        $stmt->bind_param("s", $countryName);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $countryId = $row['id'] ?? null;
    }

    /* ✅ GET LIMIT */
    $stmt = $conn->prepare("SELECT limit_qty FROM stock_limit_settings WHERE setting_key='commercial_stock' LIMIT 1");
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();

    $limit = isset($row['limit_qty']) && $row['limit_qty'] > 0 
        ? (int)$row['limit_qty'] 
        : 50;

    /* ✅ FINAL QUERY (SAME AS TABLE) */
    $stmt = $conn->prepare("
        SELECT
            p.product_name,
            p.generic_name,
            p.brand_name,
            p.qty,
            p.units_per_packet,
            p.price_per_packet,
            COALESCE(c.country_name,'-') as country_name
        FROM products p
        LEFT JOIN countries c ON c.id = p.country_id
        WHERE p.country_id = ?
          AND p.qty <= ?
          AND p.status = 'approved'
        ORDER BY p.qty ASC, p.product_name ASC
    ");

    $stmt->bind_param("ii", $countryId, $limit);
    $stmt->execute();

    $res = $stmt->get_result();

    while ($row = $res->fetch_assoc()) {
        echo $sr++ . "\t" .
             ($row['product_name'] ?? '-') . "\t" .
             ($row['generic_name'] ?? '-') . "\t" .
             ($row['brand_name'] ?? '-') . "\t" .
             ($row['country_name'] ?? '-') . "\t" .
             ($row['qty'] ?? 0) . "\t" .
             ($row['units_per_packet'] ?? '-') . "\t" .
             ($row['price_per_packet'] ?? '-') . "\n";
    }

    exit;
}

$role = strtolower($_SESSION['role'] ?? '');

if (!isset($_SESSION['user_id']) || !in_array($role, ['agency', 'admin', 'ra'])) {
    header('Location: ../login.php');
    exit;
}

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

/* =========================================================
   GET AGENCY COUNTRY NAME + COUNTRY ID
========================================================= */
$userId = (int)$_SESSION['user_id'];

$agencyCountryName = '';
$agencyCountryId = null;

$stmt = $conn->prepare("SELECT country FROM users WHERE id = ? LIMIT 1");
if ($stmt) {
    $stmt->bind_param("i", $userId);
    $stmt->execute();
    $res = $stmt->get_result();
    $userRow = $res->fetch_assoc();
    $agencyCountryName = trim($userRow['country'] ?? '');
}

if ($agencyCountryName !== '') {
    $stmt = $conn->prepare("
        SELECT id
        FROM countries
        WHERE LOWER(TRIM(country_name)) = LOWER(TRIM(?))
        LIMIT 1
    ");
    if ($stmt) {
        $stmt->bind_param("s", $agencyCountryName);
        $stmt->execute();
        $res = $stmt->get_result();
        if ($row = $res->fetch_assoc()) {
            $agencyCountryId = (int)$row['id'];
        }
    }
}

/* =========================================================
   STOCK LIMIT TABLE
========================================================= */
$conn->query("
    CREATE TABLE IF NOT EXISTS stock_limit_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(50) NOT NULL UNIQUE,
        limit_qty INT NOT NULL,
        created_by INT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

$settingKey = 'commercial_stock';
$msg = '';
$err = '';

/* =========================================================
   FETCH CURRENT COMMERCIAL STOCK LIMIT
========================================================= */
$currentLimit = null;
$stmt = $conn->prepare("SELECT limit_qty FROM stock_limit_settings WHERE setting_key = ? LIMIT 1");
if ($stmt) {
    $stmt->bind_param("s", $settingKey);
    $stmt->execute();
    $res = $stmt->get_result();
    if ($limitRow = $res->fetch_assoc()) {
        $currentLimit = (int)$limitRow['limit_qty'];
    }
}

/* =========================================================
   FETCH PRODUCTS
   - only this agency country
   - only approved
   - only qty <= currentLimit (low stock)
========================================================= */
$products = [];
$totalLowStock = 0;

if ($agencyCountryId !== null) {
    if ($currentLimit !== null) {
        $stmt = $conn->prepare("
            SELECT
                p.id,
                p.product_name,
                p.generic_name,
                p.brand_name,
                p.qty,
                p.units_per_packet,
                p.price_per_packet,
                p.country_id,
                COALESCE(c.country_name, '-') AS country_name
            FROM products p
            LEFT JOIN countries c ON c.id = p.country_id
            WHERE p.country_id = ?
              AND p.qty <= ?
              AND p.status = 'approved'
            ORDER BY COALESCE(p.qty, 0) ASC, p.product_name ASC
        ");
        if ($stmt) {
            $stmt->bind_param("ii", $agencyCountryId, $currentLimit);
            $stmt->execute();
            $res = $stmt->get_result();
            while ($row = $res->fetch_assoc()) {
                $products[] = $row;
            }
        }
    } else {
        $stmt = $conn->prepare("
            SELECT
                p.id,
                p.product_name,
                p.generic_name,
                p.brand_name,
                p.qty,
                p.units_per_packet,
                p.price_per_packet,
                p.country_id,
                COALESCE(c.country_name, '-') AS country_name
            FROM products p
            LEFT JOIN countries c ON c.id = p.country_id
            WHERE p.country_id = ?
              AND p.status = 'approved'
            ORDER BY COALESCE(p.qty, 0) ASC, p.product_name ASC
        ");
        if ($stmt) {
            $stmt->bind_param("i", $agencyCountryId);
            $stmt->execute();
            $res = $stmt->get_result();
            while ($row = $res->fetch_assoc()) {
                $products[] = $row;
            }
        }
    }

    $totalLowStock = count($products);
}

/* =========================================================
   MESSAGES
========================================================= */
if (isset($_GET['msg']) && $_GET['msg'] === 'saved') {
    $msg = "Stock limit saved successfully.";
}
if (isset($_GET['msg']) && $_GET['msg'] === 'deleted') {
    $msg = "Stock limit deleted successfully.";
}

$current = basename($_SERVER['PHP_SELF']);
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Commercial Stock</title>

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

<style>
:root{
    --sidebar:#ffffff;
    --sidebar-border:#e5e7eb;
    --sidebar-active:#f3f4f6;
    --topbar:#ffffff;
    --content:#f5f7fb;
    --card:#ffffff;
    --text:#111827;
    --muted:#6b7280;

    /* ðŸ”¥ GREY THEME */
    --primary:#374151;
    --primary-dark:#1f2937;

    --danger:#ef4444;
    --success:#16a34a;

    --border:#e5e7eb;
    --shadow:0 10px 25px rgba(0,0,0,0.06);
    --radius:18px;

    --table-head:#374151;
    --table-head-text:#ffffff;
}

/* ===== BODY ===== */
body{
    margin:0;
    font-family: Inter, Arial, sans-serif;
    background:var(--content);
    color:var(--text);
}

/* ===== SIDEBAR ===== */
.sidebar{
    position:fixed;
    top:70px;
    left:0;
    width:250px;
    height:calc(100vh - 70px);
    background:#ffffff;
    border-right:1px solid var(--sidebar-border);
}

/* ===== HEADER ===== */
header,
.top-header,
.navbar{
    position:fixed;
    top:0;
    left:0;
    right:0;
    height:70px;
    background:var(--topbar);
    border-bottom:1px solid var(--border);
    z-index:1000;
}

/* ===== WRAPPER ===== */
.wrapper{
    margin-left:250px;
    margin-top:70px;
}

.content{
    padding:22px;
}

/* ===== TITLE ===== */
.page-title{
    margin-bottom:18px;
}

.page-title h1{
    font-size:24px;
    font-weight:800;
    display:flex;
    align-items:center;
    gap:12px;
}

/* ðŸ”¥ GREY ICON */
.page-title h1::before{
    content:"\f0ae";
    font-family:"Font Awesome 6 Free";
    font-weight:900;
    background:#f3f4f6;
    color:#374151;
    padding:10px;
    border-radius:12px;
}

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

.stat-card{
    background:var(--card);
    border-radius:var(--radius);
    padding:18px;
    box-shadow:var(--shadow);
    position:relative;
    border:1px solid #f1f5f9;
}

/* ðŸ”¥ GREY TOP LINE */
.stat-card::before{
    content:"";
    position:absolute;
    top:0;
    left:0;
    width:100%;
    height:4px;
    background:#9ca3af;
}

.stat-card .label{
    font-size:12px;
    font-weight:800;
    color:var(--muted);
    text-transform:uppercase;
}

.stat-card .value{
    font-size:32px;
    font-weight:800;
    margin-top:6px;
}

.stat-card .sub{
    font-size:13px;
    color:var(--muted);
    margin-top:6px;
}

/* ===== CARD ===== */
.card{
    background:var(--card);
    border-radius:var(--radius);
    box-shadow:var(--shadow);
    overflow:hidden;
}

.card-head{
    padding:16px 18px;
    border-bottom:1px solid var(--border);
    font-weight:800;
    display:flex;
    align-items:center;
    gap:10px;
}

/* ðŸ”¥ ICON GREY */
.card-head i{
    color:#374151;
}

.card-body{
    padding:18px;
}

/* ===== TABLE ===== */
.table-wrap{
    overflow:auto;
}

table{
    width:100%;
    border-collapse:collapse;
}

/* ðŸ”¥ DARK GREY HEADER */
th{
    background:#374151;
    color:#fff;
    font-size:13px;
    padding:12px;
    text-transform:uppercase;
}

td{
    padding:12px;
    border-bottom:1px solid var(--border);
}

tr:hover td{
    background:#f3f4f6;
}

/* ===== BADGE ===== */
.badge{
    padding:6px 10px;
    border-radius:20px;
    font-size:12px;
    font-weight:700;
}

/* ðŸ”¥ LOW = SOFT RED */
.badge-low{
    background:#fee2e2;
    color:#b91c1c;
}

/* ðŸ”¥ OK = GREEN */
.badge-ok{
    background:#dcfce7;
    color:#166534;
}

/* ===== ALERT ===== */
.alert{
    padding:12px;
    border-radius:10px;
    margin-bottom:15px;
    font-weight:600;
}

.alert.success{
    background:#dcfce7;
    color:#166534;
}

.alert.error{
    background:#fee2e2;
    color:#991b1b;
}

/* ===== EMPTY ===== */
.empty{
    padding:35px;
    text-align:center;
    color:var(--muted);
}
.export-btn{
    display:inline-flex;
    align-items:center;
    gap:8px;
    background:linear-gradient(135deg,#3b82f6,#2563eb); /* 🔵 blue */
    color:#fff;
    padding:10px 18px;
    border-radius:12px;
    font-weight:600;
    text-decoration:none;
    font-size:14px;
    transition:all 0.3s ease;
    box-shadow:0 6px 15px rgba(37,99,235,0.3);
}

.export-btn i{
    font-size:15px;
}

.export-btn:hover{
    transform:translateY(-2px);
    box-shadow:0 10px 20px rgba(37,99,235,0.4);
    background:linear-gradient(135deg,#2563eb,#1d4ed8);
}

/* ===== RESPONSIVE ===== */
@media(max-width:900px){
    .stats{ grid-template-columns:1fr; }
    .wrapper{ margin-left:0; }
}
</style>
</head>
<body>

<?php if (file_exists('../header.php')) include '../header.php'; ?>

<?php
$agencySidebar = __DIR__ . '/agencysidebar.php';
$raSidebar = __DIR__ . '/rasidebar.php';
$adminSidebar = __DIR__ . '/../Admin/adminsidebar.php';

if ($role === 'admin' && file_exists($adminSidebar)) {
    include $adminSidebar;
} elseif ($role === 'ra' && file_exists($raSidebar)) {
    include $raSidebar;
} elseif (file_exists($agencySidebar)) {
    include $agencySidebar;
}
?>

<div class="wrapper">
    <div class="content">
<div class="page-title" style="display:flex;justify-content:space-between;align-items:center;">

    <h1>Commercial Stock</h1>

<a href="?export=1" class="export-btn">
    <i class="fa fa-file-excel"></i> Export Excel
</a>

</div>

        <?php if ($msg !== ''): ?>
            <div class="alert success"><?php echo e($msg); ?></div>
        <?php endif; ?>

        <?php if ($err !== ''): ?>
            <div class="alert error"><?php echo e($err); ?></div>
        <?php endif; ?>

        <div class="stats">
            <div class="stat-card">
                <div class="label">Current Commercial Stock Limit</div>
                <div class="value">
                    <?php echo $currentLimit !== null ? e($currentLimit) : 'Not Set'; ?>
                </div>
                <div class="sub">Set by RA/Admin</div>
            </div>

            <div class="stat-card">
                <div class="label">Agency Country</div>
                <div class="value"><?php echo e($agencyCountryName ?: 'Not Found'); ?></div>
                <div class="sub">Products are matched only to this country</div>
            </div>

            <div class="stat-card">
                <div class="label">Low Stock Products</div>
                <div class="value"><?php echo e($totalLowStock); ?></div>
                <div class="sub">Items with qty less than or equal to the current limit</div>
            </div>
        </div>

        <div class="card">
            <div class="card-head">
                <h2><i class="fa-solid fa-triangle-exclamation" style="color:#f59e0b;"></i> Low Stock Products</h2>
            </div>

            <div class="table-wrap">
                <?php if ($agencyCountryId === null): ?>
                    <div class="empty">
                        <i class="fa-solid fa-map-location-dot"></i>
                        Agency country not found.
                    </div>
                <?php elseif (empty($products)): ?>
                    <div class="empty">
                        <i class="fa-solid fa-box-open"></i>
                        No approved low stock products found for <?php echo e($agencyCountryName); ?>.
                    </div>
                <?php else: ?>
                    <table>
                        <colgroup>
                            <col>
                            <col>
                            <col>
                            <col>
                            <col>
                            <col>
                        </colgroup>
                        <thead>
                            <tr>
                                <th class="center">SR</th>
                                <th class="left">Product Name</th>
                                <th class="left">Generic Name</th>
                                <th class="left">Brand Name</th>
                                <th class="center">Packet Qty</th>
                                <th class="center">Units / Packet</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $sr = 1; foreach ($products as $row): ?>
                                <tr>
                                    <td class="sr"><?php echo $sr++; ?></td>
                                    <td class="left"><?php echo e($row['product_name']); ?></td>
                                    <td class="left"><?php echo e($row['generic_name']); ?></td>
                                    <td class="left"><?php echo e($row['brand_name']); ?></td>
                                    <td class="center">
                                        <?php if ($currentLimit !== null && (int)$row['qty'] <= $currentLimit): ?>
                                            <span class="badge badge-low"><?php echo e($row['qty']); ?></span>
                                        <?php else: ?>
                                            <span class="badge badge-ok"><?php echo e($row['qty']); ?></span>
                                        <?php endif; ?>
                                    </td>
                                    <td class="center"><?php echo e($row['units_per_packet']); ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

</body>
</html>