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

/* ================= EXPORT TO EXCEL (LOW STOCK ONLY) ================= */
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;

    /* ✅ SAME LIMIT AS UI */
    $settingKey = 'commercial_stock';

    $stmtL = $conn->prepare("SELECT limit_qty FROM stock_limit_settings WHERE setting_key=? LIMIT 1");
    $stmtL->bind_param("s", $settingKey);
    $stmtL->execute();
    $rowL = $stmtL->get_result()->fetch_assoc();

    // 🔥 fallback if null
    $limit = isset($rowL['limit_qty']) && $rowL['limit_qty'] > 0 
        ? (int)$rowL['limit_qty'] 
        : 50;

    /* ✅ ONLY LOW STOCK */
    $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.qty <= ?
        ORDER BY p.qty ASC, p.product_name ASC
    ");

    $stmt->bind_param("i", $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, ['ra', 'admin'])) {
    header('Location: ../login.php');
    exit;
}

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

function fmtDate($v) {
    if (empty($v)) return '-';
    $ts = strtotime($v);
    return $ts ? date('d-m-Y h:i A', $ts) : e($v);
}

/*
|--------------------------------------------------------------------------
| Create settings table automatically if it doesn't exist
|--------------------------------------------------------------------------
*/
$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 = '';

/* DELETE LIMIT */
if (isset($_GET['delete_limit'])) {
    $stmt = $conn->prepare("DELETE FROM stock_limit_settings WHERE setting_key = ?");
    $stmt->bind_param("s", $settingKey);
    $stmt->execute();

    header("Location: commercial-stock.php?msg=deleted");
    exit;
}

/* SAVE LIMIT */
if (isset($_POST['save_limit'])) {
    $limitQty = trim($_POST['limit_qty'] ?? '');

    if ($limitQty === '' || !ctype_digit($limitQty) || (int)$limitQty <= 0) {
        $err = "Please enter a valid stock limit greater than 0.";
    } else {
        $limitQty = (int)$limitQty;

        $stmt = $conn->prepare("SELECT id FROM stock_limit_settings WHERE setting_key = ? LIMIT 1");
        $stmt->bind_param("s", $settingKey);
        $stmt->execute();
        $existing = $stmt->get_result()->fetch_assoc();

        if ($existing) {
            echo "<script>
                Swal.fire({
                    icon: 'warning',
                    title: 'Limit Already Exists',
                    text: 'Delete existing limit first!'
                }).then(() => {
                    window.location = 'commercial-stock.php';
                });
            </script>";
            exit;
        }

        $createdBy = (int)$_SESSION['user_id'];
        $stmt = $conn->prepare("
            INSERT INTO stock_limit_settings (setting_key, limit_qty, created_by)
            VALUES (?, ?, ?)
        ");
        $stmt->bind_param("sii", $settingKey, $limitQty, $createdBy);
        $stmt->execute();

        header("Location: commercial-stock.php?msg=saved");
        exit;
    }
}

/* FETCH CURRENT LIMIT */
$limitRow = null;
$stmt = $conn->prepare("SELECT * FROM stock_limit_settings WHERE setting_key = ? LIMIT 1");
$stmt->bind_param("s", $settingKey);
$stmt->execute();
$limitRow = $stmt->get_result()->fetch_assoc();

$currentLimit = $limitRow ? (int)$limitRow['limit_qty'] : null;

/* LOW STOCK LIST WITH COUNTRY */
$lowStock = [];
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.gst_percent,
            p.status,
            p.created_at,
            p.country_id,
            COALESCE(c.country_name, '-') AS country_name
        FROM products p
        LEFT JOIN countries c ON c.id = p.country_id
        WHERE COALESCE(p.qty, 0) <= ?
        ORDER BY COALESCE(p.qty, 0) ASC, p.product_name ASC
    ");
    $stmt->bind_param("i", $currentLimit);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $lowStock[] = $row;
    }
}

/* COUNTS */
$totalProducts = 0;
$res = $conn->query("SELECT COUNT(*) AS total FROM products");
if ($res) {
    $totalProducts = (int)($res->fetch_assoc()['total'] ?? 0);
}
$totalLowStock = count($lowStock);

/* 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">
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>

    <style>
    :root{
        --sidebar:#ffffff;
        --sidebar-border:#e5ebf3;
        --sidebar-active:#edf2f8;
        --topbar:#ffffff;
        --content:#eef4fb;
        --card:#ffffff;
        --text:#1f2937;
        --muted:#6b7280;
        --primary:#2f5fbb;
        --primary-dark:#234b94;
        --danger:#ef4444;
        --success:#16a34a;
        --warning:#f59e0b;
        --border:#e6ebf2;
        --border-2:#d8e0ea;
        --shadow:0 10px 30px rgba(15, 23, 42, 0.08);
        --shadow-soft:0 6px 18px rgba(15, 23, 42, 0.06);
        --radius:18px;
        --radius-sm:14px;
        --table-head:#3d4b64;
        --table-head-text:#ffffff;
        --bg-line:#f4f7fb;
    }

    *{
        box-sizing:border-box;
    }

    html, body{
        width:100%;
        min-height:100%;
    }

    body{
        margin:0;
        font-family: Inter, Arial, Helvetica, sans-serif;
        background:var(--content);
        color:var(--text);
        overflow-x:hidden;
    }

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

    img{
        max-width:100%;
        display:block;
    }

    /* =========================
       TOP HEADER / NAVBAR
       ========================= */
    header,
    .top-header,
    .navbar{
        position:fixed;
        top:0;
        left:0;
        right:0;
        height:70px;
        z-index:1100;
        background:var(--topbar);
        border-bottom:1px solid var(--border);
        box-shadow:0 2px 10px rgba(15, 23, 42, 0.04);
    }

    /* =========================
       SIDEBAR
       ========================= */
    .sidebar{
        position:fixed;
        top:70px;
        left:0;
        width:250px;
        height:calc(100vh - 70px);
        background:var(--sidebar);
        color:var(--text);
        z-index:1000;
        border-right:1px solid var(--sidebar-border);
        box-shadow:4px 0 18px rgba(15, 23, 42, 0.04);
        overflow-y:auto;
    }

    .sidebar::-webkit-scrollbar{
        width:8px;
    }

    .sidebar::-webkit-scrollbar-thumb{
        background:#cfd8e3;
        border-radius:999px;
    }

    /* Make sidebar menu links look like the screenshot */
    .sidebar a,
    .sidebar li,
    .sidebar .menu-item{
        color:var(--text);
    }

    .sidebar .active,
    .sidebar .selected,
    .sidebar .menu-item.active{
        background:var(--sidebar-active);
        border-radius:14px;
        font-weight:700;
        color:#1f2937;
    }

    .sidebar .submenu,
    .sidebar .sub-menu{
        margin-top:6px;
        margin-left:14px;
        padding-left:12px;
        border-left:1px solid #e8edf4;
    }

    .sidebar .submenu a,
    .sidebar .sub-menu a{
        color:#667085;
        font-weight:600;
    }

    .sidebar .submenu a:hover,
    .sidebar .sub-menu a:hover{
        color:#111827;
    }

    /* =========================
       WRAPPER / CONTENT
       ========================= */
    .wrapper{
        margin-left:250px;
        margin-top:70px;
        min-height:calc(100vh - 70px);
        background:var(--content);
    }

    .content{
        padding:22px 18px 28px;
    }

    /* =========================
       PAGE TITLE
       ========================= */
    .page-title{
        background:transparent;
        border:none;
        box-shadow:none;
        margin:4px 0 16px;
        padding:0;
    }

    .page-title h1{
        margin:0;
        font-size:24px;
        font-weight:800;
        color:#0f172a;
        display:flex;
        align-items:center;
        gap:12px;
        line-height:1.2;
        letter-spacing:-0.2px;
    }

    .page-title h1::before{
        content:"\f0ae";
        font-family:"Font Awesome 6 Free";
        font-weight:900;
        width:36px;
        height:36px;
        display:inline-flex;
        align-items:center;
        justify-content:center;
        border-radius:12px;
        background:#eef4ff;
        color:#244c96;
        font-size:16px;
        box-shadow:inset 0 0 0 1px #d7e5ff;
        flex:0 0 36px;
    }

    .page-subtitle{
        margin-top:8px;
        font-size:14px;
        color:#6b7280;
        font-weight:600;
    }

    /* =========================
       ALERTS
       ========================= */
    .alert{
        padding:13px 14px;
        border-radius:14px;
        margin-bottom:16px;
        font-weight:700;
        box-shadow:var(--shadow-soft);
    }

    .alert.success{
        background:#ecfdf5;
        color:#166534;
        border:1px solid #bbf7d0;
    }

    .alert.error{
        background:#fef2f2;
        color:#991b1b;
        border:1px solid #fecaca;
    }

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

    .stat-card{
        position:relative;
        background:var(--card);
        border-radius:var(--radius);
        box-shadow:var(--shadow);
        padding:18px 18px 16px;
        border:1px solid #edf2f7;
        overflow:hidden;
        min-height:112px;
    }

    .stat-card::before{
        content:"";
        position:absolute;
        left:0;
        right:0;
        top:0;
        height:4px;
        background:#5e6c84;
    }

    .stat-card .label{
        color:#6b7280;
        font-size:12px;
        margin-bottom:8px;
        font-weight:800;
        text-transform:uppercase;
        letter-spacing:.5px;
    }

    .stat-card .value{
        font-size:32px;
        font-weight:800;
        color:#0f172a;
        line-height:1.05;
    }

    .stat-card .sub{
        margin-top:8px;
        color:var(--muted);
        font-size:13px;
        line-height:1.5;
        font-weight:600;
    }

    /* =========================
       MAIN GRID
       ========================= */
    .grid{
        display:grid;
        grid-template-columns:360px minmax(0, 1fr);
        gap:16px;
        align-items:start;
    }

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

    .card-head{
        padding:14px 16px;
        border-bottom:1px solid var(--border);
        display:flex;
        align-items:center;
        justify-content:space-between;
        gap:10px;
        color:#0f172a;
        font-weight:800;
        background:#ffffff;
    }

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

    .card-head h2 i{
        color:#111827;
        font-size:16px;
    }

    .card-body{
        padding:16px;
        color:#1e293b;
    }

    /* =========================
       FORM
       ========================= */
    .form-group{
        margin-bottom:14px;
    }

    .form-group label{
        display:block;
        margin-bottom:8px;
        font-size:13px;
        font-weight:800;
        color:#334155;
    }

    .form-control{
        width:100%;
        border:1px solid #d8e0ea;
        border-radius:12px;
        padding:12px 14px;
        font-size:15px;
        outline:none;
        background:#fff;
        color:#0f172a;
        transition:border-color .15s ease, box-shadow .15s ease, transform .15s ease;
    }

    .form-control:focus{
        border-color:var(--primary);
        box-shadow:0 0 0 4px rgba(47, 95, 187, 0.12);
    }

    .btn-row{
        display:flex;
        flex-wrap:wrap;
        gap:10px;
        margin-top:14px;
    }

    .btn{
        border:0;
        border-radius:12px;
        padding:11px 15px;
        font-size:14px;
        font-weight:700;
        cursor:pointer;
        display:inline-flex;
        align-items:center;
        gap:8px;
        transition:transform .15s ease, background .15s ease, box-shadow .15s ease;
        box-shadow:0 6px 14px rgba(15, 23, 42, 0.08);
    }

    .btn:hover{
        transform:translateY(-1px);
    }

    .btn-primary{
        background:var(--primary);
        color:#fff;
    }

    .btn-primary:hover{
        background:var(--primary-dark);
    }

    .btn-danger{
        background:var(--danger);
        color:#fff;
    }

    .btn-danger:hover{
        background:#dc2626;
    }

    /* =========================
       LIMIT BOX
       ========================= */
    .limit-box{
        background:#f8fafc;
        border:1px dashed #cbd5e1;
        border-radius:16px;
        padding:16px;
        margin-bottom:14px;
    }

    .limit-box .big{
        font-size:34px;
        font-weight:800;
        color:#0f172a;
        line-height:1;
        letter-spacing:-0.5px;
    }

    .limit-box .small{
        color:var(--muted);
        margin-top:6px;
        font-size:13px;
        font-weight:600;
    }

    .note{
        font-size:13px;
        color:var(--muted);
        margin-top:12px;
        line-height:1.6;
        font-weight:600;
    }

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

    table{
        width:100%;
        border-collapse:separate;
        border-spacing:0;
        min-width:1350px;
        table-layout:auto;
    }

    thead th{
        background:var(--table-head);
        color:var(--table-head-text);
        position:sticky;
        top:0;
        z-index:2;
        font-weight:800;
        font-size:13px;
        letter-spacing:.3px;
        text-transform:uppercase;
        border-bottom:1px solid rgba(255,255,255,0.08);
    }

    th, td{
        padding:13px 12px;
        border-bottom:1px solid #e8edf5;
        text-align:left;
        vertical-align:middle;
        font-size:14px;
        color:#1e293b;
        white-space:nowrap;
        background:#fff;
    }

    tbody tr:hover td{
        background:#fbfdff;
    }

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

    /* first column alignment */
    th:first-child,
    td:first-child{
        text-align:center;
        width:72px;
    }

    /* numeric columns */
    td:nth-child(6),
    td:nth-child(7),
    td:nth-child(8){
        text-align:center;
    }

    .badge{
        display:inline-flex;
        align-items:center;
        justify-content:center;
        min-width:52px;
        padding:6px 10px;
        border-radius:999px;
        font-size:12px;
        font-weight:800;
        white-space:nowrap;
        line-height:1;
        border:1px solid transparent;
    }

    .badge-low{
        background:#fff7ed;
        color:#9a3412;
        border-color:#fed7aa;
    }

    .badge-ok{
        background:#ecfdf5;
        color:#166534;
        border-color:#bbf7d0;
    }

    .badge-pending{
        background:#eef2f7;
        color:#475569;
        border-color:#e2e8f0;
    }

    .empty{
        padding:38px 18px;
        text-align:center;
        color:var(--muted);
        background:#fff;
    }

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

    /* =========================
       RESPONSIVE
       ========================= */
    @media (max-width: 1200px){
        .stats{
            grid-template-columns:repeat(2, minmax(0, 1fr));
        }

        .grid{
            grid-template-columns:1fr;
        }
    }

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

    /* ðŸ”¥ REMOVE SIDEBAR GAP */
    .wrapper{
        margin-left:0 !important;
    }

    .content{
        padding:14px 10px;
    }

    /* ðŸ”¥ GRID FIX */
    .grid{
        grid-template-columns:1fr;
    }

    /* ðŸ”¥ TABLE FIX */
    .table-wrap{
        overflow:visible;
    }

    table{
        min-width:100% !important;
    }

    thead{
        display:none;
    }

    tbody tr{
        display:block;
        background:#fff;
        margin-bottom:12px;
        border-radius:12px;
        padding:12px;
        box-shadow:0 4px 10px rgba(0,0,0,0.05);
    }

    tbody td{
        display:flex;
        justify-content:space-between;
        padding:8px 0;
        border:none;
        font-size:13px;
    }

    tbody td::before{
        content:attr(data-label);
        font-weight:600;
        color:#64748b;
        flex:0 0 45%;
    }

}
</style>
</head>
<body>
<?php if (file_exists('../header.php')) include '../header.php'; ?>
<?php
if ($role === 'admin') {
    include __DIR__ . '/../Admin/adminsidebar.php';
} else {
    include __DIR__ . '/rasidebar.php';
}
?>

<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="btn btn-primary">
        <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 Stock Limit</div>
                <div class="value">
                    <?php echo $currentLimit !== null ? e($currentLimit) : 'Not Set'; ?>
                </div>
                <div class="sub">One-time limit for low-stock tracking</div>
            </div>

            <div class="stat-card">
                <div class="label">Low Stock Items</div>
                <div class="value"><?php echo e($totalLowStock); ?></div>
                <div class="sub">Products with qty &lt;= current limit</div>
            </div>

            <div class="stat-card">
                <div class="label">Total Products</div>
                <div class="value"><?php echo e($totalProducts); ?></div>
                <div class="sub">From products table</div>
            </div>
        </div>

        <div class="grid">
            <div class="card">
                <div class="card-head">
                    <h2><i class="fa-solid fa-sliders"></i> Set Stock Limit</h2>
                </div>
                <div class="card-body">
                    <?php if ($limitRow): ?>
                        <div class="limit-box">
                            <div class="big"><?php echo e((int)$limitRow['limit_qty']); ?></div>
                            <div class="small">Current commercial stock limit</div>
                        </div>

                        <div class="btn-row">
                            <a href="commercial-stock.php?delete_limit=1"
                               class="btn btn-danger"
                               onclick="return confirm('Delete current stock limit? After delete you can add a new one.')">
                                <i class="fa-solid fa-trash"></i> Delete Limit
                            </a>
                        </div>

                        <div class="note">
                            A stock limit already exists. Delete it first if you want to set a new limit.
                        </div>
                    <?php else: ?>
                        <form method="post">
                            <div class="form-group">
                                <label for="limit_qty">Stock Limit</label>
                                <input
                                    type="number"
                                    min="1"
                                    name="limit_qty"
                                    id="limit_qty"
                                    class="form-control"
                                    placeholder="Enter stock limit"
                                    required
                                >
                            </div>

                            <div class="btn-row">
                                <button type="submit" name="save_limit" class="btn btn-primary">
                                    <i class="fa-solid fa-floppy-disk"></i> Save Limit
                                </button>
                            </div>
                        </form>

                        <div class="note">
                            Only one limit can be added. If someone tries to add another limit, a popup will appear.
                        </div>
                    <?php endif; ?>
                </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 ($currentLimit === null): ?>
                        <div class="empty">
                            <i class="fa-solid fa-box-open"></i>
                            Please set a stock limit first to show the low stock list.
                        </div>
                    <?php elseif (empty($lowStock)): ?>
                        <div class="empty">
                            <i class="fa-solid fa-circle-check"></i>
                            No products found below or equal to the current stock limit.
                        </div>
                    <?php else: ?>
                        <table>
                            <thead>
                                <tr>
                                    <th>SR</th>
                                    <th>Product Name</th>
                                    <th>Generic Name</th>
                                    <th>Brand Name</th>
                                    <th>Country</th>
                                    <th>Qty</th>
                                    <th>Units / Packet</th>
                                    <th>Price / Packet</th>
                                    
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $sr = 1;
                                foreach ($lowStock as $row):
                                    $qty = (int)($row['qty'] ?? 0);
                                    $status = strtolower(trim((string)($row['status'] ?? '')));
                                ?>
                                    <tr>
                                       <td 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="Country"><?php echo e($row['country_name']); ?></td>

<td data-label="Qty">
    <?php if ($qty <= $currentLimit): ?>
        <span class="badge badge-low"><?php echo e($qty); ?></span>
    <?php else: ?>
        <span class="badge badge-ok"><?php echo e($qty); ?></span>
    <?php endif; ?>
</td>

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

<td data-label="Price">
    <?php echo isset($row['price_per_packet']) ? number_format((float)$row['price_per_packet'],2) : '-'; ?>
</td>
                                 
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    <?php endif; ?>
                </div>
            </div>
        </div>
    </div>
    </div>
</div>
</body>
</html>