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

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

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

    $sr = 1;

    /* ✅ GET USER 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();

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

    /* ✅ GET COUNTRY ID */
    $countryId = null;
    if ($agencyCountryName !== '') {
        $stmt = $conn->prepare("SELECT id FROM countries WHERE LOWER(country_name)=LOWER(?) LIMIT 1");
        $stmt->bind_param("s", $agencyCountryName);
        $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;

    /* ✅ FETCH SAME DATA AS TABLE */
    $stmt = $conn->prepare("
        SELECT
            p.product_name,
            p.generic_name,
            p.brand_name,
            COALESCE(c.country_name,'-') as country_name,
            p.qty,
            p.units_per_packet,
            p.price_per_packet
        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, ['sales_manager'])) {
    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{
        --bg:#eef3f9;
        --card:#ffffff;
        --text:#0f172a;
        --muted:#64748b;
        --border:#dfe7f1;
        --shadow:0 16px 35px rgba(15, 23, 42, 0.08);
        --shadow-soft:0 10px 24px rgba(15, 23, 42, 0.06);
        --radius:22px;

        --primary:#374155;
        --primary-2:#273142;
        --primary-soft:#eef2f7;
        --success:#16a34a;
        --warning:#d97706;
        --danger:#dc2626;

        --low-bg:#fef3c7;
        --low-text:#92400e;
        --ok-bg:#dcfce7;
        --ok-text:#166534;
    }

    *{ box-sizing:border-box; }

    body{
        margin:0;
        font-family: Inter, Arial, Helvetica, sans-serif;
        background:
            radial-gradient(circle at top left, rgba(37,99,235,.08), transparent 30%),
            radial-gradient(circle at bottom right, rgba(14,165,233,.06), transparent 28%),
            var(--bg);
        color:var(--text);
    }

    a{ text-decoration:none; color:inherit; }

    .wrapper{
        margin-left:250px;
        margin-top:20px;
        min-height:calc(100vh - 70px);
    }

    .content{
        padding:18px;
        max-width:1600px;
    }

    .page-title{
        background: transparent;
        border: none;
        box-shadow: none;
        padding: 10px 0;
        margin-bottom: 10px;
    }

    .page-title .title-row{
        display:flex;
        align-items:center;
        justify-content:space-between;
        gap:16px;
        flex-wrap:wrap;
    }

    .page-title h1{
        margin:0;
        font-size:22px;
        color:#0b1b3a;
        font-weight:700;
        letter-spacing:.2px;
        display:flex;
        align-items:center;
        gap:12px;
    }

    .page-title h1 i{
        color:#374155;
        background:#eef2f7;
        width:36px;
        height:36px;
        font-size:14px;
        border-radius:14px;
        display:inline-flex;
        align-items:center;
        justify-content:center;
        box-shadow:inset 0 0 0 1px rgba(37,99,235,.08);
    }

    .page-title p{
        margin:8px 0 0;
        color:var(--muted);
        font-size:14px;
        line-height:1.5;
    }

    .stats{
        display:grid;
        grid-template-columns:repeat(3, minmax(0, 1fr));
        gap:12px;
        margin-bottom:14px;
    }

    .stat-card{
        background:linear-gradient(180deg, #ffffff 0%, #fbfdff 100%);
        border:1px solid var(--border);
        border-radius:18px;
        box-shadow:var(--shadow-soft);
        padding:12px;
        position:relative;
        overflow:hidden;
        min-height:90px;
    }

    .stat-card::before{
        content:'';
        position:absolute;
        inset:0 auto auto 0;
        width:100%;
        height:4px;
        background:linear-gradient(90deg, #374155, #6b7280);
    }

    .stat-card .icon{
        width:38px;
        height:38px;
        border-radius:12px;
        display:flex;
        align-items:center;
        justify-content:center;
        margin-bottom:8px;
        background:var(--primary-soft);
        color:var(--primary);
        font-size:15px;
    }

    .stat-card .label{
        color:var(--muted);
        font-size:10px;
        margin-bottom:4px;
        font-weight:800;
        text-transform:uppercase;
        letter-spacing:.04em;
    }

    .stat-card .value{
        font-size:18px;
        font-weight:900;
        color:#0f172a;
        line-height:1.1;
        word-break:break-word;
    }

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

    .card{
        background:var(--card);
        border:1px solid var(--border);
        border-radius:var(--radius);
        box-shadow:var(--shadow);
        overflow:hidden;
    }

    .card-head{
        padding:12px 16px;
        border-bottom:1px solid var(--border);
        display:flex;
        align-items:center;
        justify-content:space-between;
        gap:10px;
        background:linear-gradient(180deg, #ffffff, #f8fbff);
    }

    .card-head h2{
        margin:0;
        font-size:16px;
        color:#0f172a;
        font-weight:900;
        display:flex;
        align-items:center;
        gap:10px;
    }

    .card-head h2 i{
        color:#000;
    }

    .table-wrap{
        overflow-x:auto;
    }

    table{
        width:100%;
        border-collapse:separate;
        border-spacing:0;
        table-layout:fixed;
    }

    colgroup col:nth-child(1){ width:70px; }
    colgroup col:nth-child(2){ width:30%; }
    colgroup col:nth-child(3){ width:30%; }
    colgroup col:nth-child(4){ width:24%; }
    colgroup col:nth-child(5){ width:140px; }
    colgroup col:nth-child(6){ width:160px; }

    thead th{
        position:sticky;
        top:0;
        z-index:1;
        background:#374155;
        color:#fff;
        font-size:13px;
        text-transform:uppercase;
        letter-spacing:.05em;
        font-weight:900;
        padding:10px;
        border-bottom:1px solid #e2e8f0;
    }

    tbody td{
        padding:10px;
        border-bottom:1px solid #edf2f7;
        font-size:13px;
        color:#374155;
        vertical-align:middle;
        white-space:normal;
        word-break:break-word;
    }

    tbody tr:nth-child(even){
        background:#fcfdff;
    }

    tbody tr:hover{
        background:#f1f5f9;
    }

    th.center, td.center{ text-align:center; }
    th.right, td.right{ text-align:right; }
    th.left, td.left{ text-align:left; }

    td.sr{
        text-align:center;
        font-weight:800;
        color:#374155;
    }

    .badge{
        display:inline-flex;
        align-items:center;
        justify-content:center;
        min-width:54px;
        padding:5px 10px;
        border-radius:999px;
        font-size:11px;
        font-weight:800;
        line-height:1;
        box-shadow:inset 0 0 0 1px rgba(0,0,0,.03);
    }

    .badge-low{ color:#000; }
    .badge-ok{ color:var(--ok-text); }

    .empty{
        padding:48px 18px;
        text-align:center;
        color:var(--muted);
    }

    .empty i{
        font-size:44px;
        margin-bottom:12px;
        display:block;
        color:#94a3b8;
    }

    .alert{
        padding:13px 14px;
        border-radius:14px;
        margin-bottom:16px;
        font-weight:700;
        border:1px solid transparent;
    }

    .alert.success{
        background:#ecfdf5;
        color:#166534;
        border-color:#bbf7d0;
    }

    .alert.error{
        background:#fef2f2;
        color:#991b1b;
        border-color:#fecaca;
    }

  /* ================= TABLET ================= */
@media (max-width: 1100px){
    .stats{
        grid-template-columns: 1fr;
    }

    .wrapper{
        margin-left: 0;
    }
}


/* ================= MEDIUM DEVICES ================= */
@media (max-width: 992px){

    .wrapper{
        margin-left: 0 !important;
    }

    .content{
        padding: 14px;
    }

    .stats{
        grid-template-columns: 1fr;
        gap: 12px;
    }

    .stat-card{
        padding: 14px;
    }
}


/* ================= MOBILE (MAIN FIX) ================= */
@media (max-width: 768px){

    html, body{
        overflow-x: hidden;
    }

    .content{
        padding: 12px;
    }

    /* TITLE */
    .page-title h1{
        font-size: 18px;
    }

    .page-title p{
        font-size: 12px;
    }

    .page-title h1 i{
        width: 34px;
        height: 34px;
        font-size: 12px;
    }

    /* CARD */
    .card{
        border-radius: 14px;
    }

    .card-head{
        padding: 10px 12px;
    }

    .card-head h2{
        font-size: 14px;
    }

    /* ❌ REMOVE TABLE SCROLL */
    .table-wrap{
        overflow: visible;
    }

    table, thead, tbody, tr, td{
        display: block;
        width: 100%;
    }

    /* HIDE HEADER */
    thead{
        display: none;
    }

    /* ROW = CARD */
    tbody tr{
        background: #ffffff;
        margin-bottom: 12px;
        padding: 12px;
        border-radius: 16px;
        box-shadow: 0 6px 18px rgba(0,0,0,0.06);
        border: 1px solid #e5e7eb;
        transition: 0.2s;
    }

    tbody tr:hover{
        transform: translateY(-2px);
    }

    /* CELL STYLE */
    tbody td{
        display: flex;
        justify-content: space-between;
        align-items: center;
        padding: 6px 0;
        font-size: 13px;
        border: none;
    }

    /* LABEL LEFT SIDE */
    tbody td::before{
        content: attr(data-label);
        font-weight: 700;
        color: #64748b;
        font-size: 11px;
        text-transform: uppercase;
    }

    /* SR FIX */
    td.sr{
        justify-content: center;
        font-weight: 900;
        font-size: 14px;
        border-bottom: 1px dashed #e5e7eb;
        margin-bottom: 6px;
    }

    /* BADGE */
    .badge{
        font-size: 11px;
        padding: 5px 10px;
    }

    /* EMPTY */
    .empty{
        padding: 30px 10px;
        font-size: 13px;
    }
}


/* ================= EXTRA SMALL ================= */
@media (max-width: 480px){

    .content{
        padding: 10px;
    }

    .page-title h1{
        font-size: 16px;
    }

    .stat-card .value{
        font-size: 15px;
    }

    .stat-card .label{
        font-size: 9px;
    }

    .stat-card .sub{
        font-size: 10px;
    }
}
.export-btn{
    display:inline-flex;
    align-items:center;
    gap:8px;
    background:linear-gradient(135deg,#3b82f6,#2563eb);
    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:hover{
    transform:translateY(-2px);
    box-shadow:0 10px 20px rgba(37,99,235,0.4);
}
</style>
</head>
<body>

<?php
$header = __DIR__ . '/../header.php';
if (file_exists($header)) {
    include $header;
}
?>
<?php
$salesManagerSidebar = __DIR__ . '/salesmanagersidebar.php';

if (file_exists($salesManagerSidebar)) {
    include $salesManagerSidebar;
}
?>

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

    <div>
        <h1><i class="fa-solid fa-boxes-stacked"></i> Commercial Stock</h1>
        <p>Low stock products are shown only for the agency country and approved items.</p>
    </div>

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

</div>
</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="icon"><i class="fa-solid fa-layer-group"></i></div>
        <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="icon"><i class="fa-solid fa-earth-asia"></i></div>
        <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="icon"><i class="fa-solid fa-triangle-exclamation"></i></div>
        <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"></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" data-label="SR"><?php echo $sr++; ?></td>

    <td data-label="Product">
        <?php echo e($row['product_name']); ?>
    </td>

    <td data-label="Generic">
        <?php echo e($row['generic_name']); ?>
    </td>

    <td data-label="Brand">
        <?php echo e($row['brand_name']); ?>
    </td>

    <td data-label="Packet Qty" 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 data-label="Units">
        <?php echo e($row['units_per_packet']); ?>
    </td>
</tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

</body>
</html>