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

/* ================= EXPORT TO EXCEL (SAMPLE LOW STOCK) ================= */
if (isset($_GET['export']) && $_GET['export'] == 1) {

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

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

    $sr = 1;

    /* ✅ GET SAMPLE LIMIT */
    $settingKey = 'sample_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();

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

    /* ✅ SAMPLE PRODUCTS QUERY */
    $stmt = $conn->prepare("
        SELECT
            product_name,
            generic_name,
            brand_name,
            country,
            packet_qty,
            units_packet,
            price_packet
        FROM sample_products
        WHERE packet_qty <= ?
        ORDER BY packet_qty ASC, 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'] ?? '-') . "\t" .
             ($row['packet_qty'] ?? 0) . "\t" .
             ($row['units_packet'] ?? '-') . "\t" .
             ($row['price_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);
}

/* ================= 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
");

/* ðŸ”¥ CHANGE KEY */
$settingKey = 'sample_stock';

$msg = '';
$err = '';

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

    header("Location: sample-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 = 'sample-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: sample-stock.php?msg=saved");
        exit;
    }
}

/* FETCH CURRENT LIMIT */
$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 (SAMPLE PRODUCTS) ================= */
$lowStock = [];

if ($currentLimit !== null) {
    $stmt = $conn->prepare("
        SELECT 
            id,
            product_name,
            generic_name,
            brand_name,
            country,
            packet_qty,
            units_packet,
            price_packet,
            gst,
            created_at
        FROM sample_products
        WHERE COALESCE(packet_qty, 0) <= ?
        ORDER BY packet_qty ASC, 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 sample_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>Sample 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{
    --bg:#eef3f9;
    --card:#ffffff;
    --text:#0f172a;
    --muted:#64748b;
    --border:#e2e8f0;
    --shadow:0 10px 28px rgba(15, 23, 42, 0.08);
    --radius:16px;

    --primary:#374155;
    --danger:#ef4444;

    --low-bg:#fef3c7;
    --low-text:#92400e;

    --ok-bg:#dcfce7;
    --ok-text:#166534;
}

/* RESET */
*{ box-sizing:border-box; }

body{
    margin:0;
    font-family: Inter, Arial, sans-serif;
    background:var(--bg);
    color:var(--text);
}

/* HEADER FIX */
header, .top-header, .navbar{
    position:fixed;
    top:0;
    left:250px;
    right:0;
    height:70px;
    z-index:1000;
}

/* SIDEBAR */
.sidebar{
    position:fixed;
    top:70px;
    left:0;
    width:250px;
    height:calc(100vh - 70px);
}

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

/* CONTENT */
.content{
    padding:24px;
}

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

.page-title h1{
    font-size:22px;
    font-weight:800;
    margin:0;
}

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

.stat-card{
    position:relative;
    background:var(--card);
    border-radius:var(--radius);
    padding:16px;
    box-shadow:var(--shadow);
    overflow:hidden;
}

/* TOP LINE */
.stat-card::before{
    content:"";
    position:absolute;
    top:0;
    left:0;
    width:100%;
    height:4px;
    background:#374155; /* YOUR COLOR */
}
.stat-card .label{
    font-size:12px;
    color:var(--muted);
    font-weight:700;
}

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

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

/* GRID FIX (IMPORTANT) */
.grid{
    display:grid;
    grid-template-columns:320px 1fr;
    gap:16px;
    align-items:start;
}

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

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

.card-body{
    padding:16px;
}

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

.limit-box .big{
    font-size:24px;
    font-weight:900;
}

.limit-box .small{
    font-size:13px;
    color:var(--muted);
}

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

.form-group label{
    font-size:13px;
    font-weight:700;
    margin-bottom:6px;
    display:block;
}

.form-control{
    width:100%;
    padding:10px;
    border:1px solid #d1d5db;
    border-radius:10px;
}

/* BUTTON */
.btn{
    padding:10px 14px;
    border-radius:10px;
    border:none;
    font-weight:700;
    cursor:pointer;
    font-size:13px;
}

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

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

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

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

thead th{
    background:#374155;
    color:#fff;
    padding:10px;
    font-size:13px;
    text-transform:uppercase;
    position:sticky;
    top:0;
}

tbody td{
    padding:10px;
    font-size:13px;
    border-bottom:1px solid #edf2f7;
}

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

tbody tr:hover{
    background:#eef4ff;
}

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

.badge-low{
    background:var(--low-bg);
    color:var(--low-text);
}

.badge-ok{
    background:var(--ok-bg);
    color:var(--ok-text);
}

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

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

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

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

/* RESPONSIVE */
@media(max-width:1100px){
    .grid{ grid-template-columns:1fr; }
    .stats{ grid-template-columns:1fr; }
    .wrapper{ margin-left:0; }
}

@media(max-width:768px){
    .content{ padding:12px; }
}
</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">
            <div style="display:flex;justify-content:space-between;align-items:center;">     <h1>Sample Stock</h1>      <a href="?export=1" class="btn btn-primary">         <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="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">Sample 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 Sample 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 Sample stock limit</div>
                        </div>

                       <form method="post"
      onsubmit="return confirm('Delete current stock limit? After delete you can add a new one.');"
      style="display:inline;">
    <button type="submit" name="delete_limit" class="btn btn-danger">
        <i class="fa-solid fa-trash"></i> Delete Limit
    </button>
</form>

                        <div class="note">
                           <br> 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>Packet Qty</th>
            <th>Units / Packet</th>
            <th>Price / Packet</th>
        </tr>
    </thead>

    <tbody>
        <?php
        $sr = 1;
        foreach ($lowStock as $row):
            $qty = (int)($row['packet_qty'] ?? 0);
        ?>
            <tr>
                <td><?php echo $sr++; ?></td>

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

                <td><?php echo e($row['generic_name'] ?? '-'); ?></td>

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

                <td><?php echo e($row['country'] ?? '-'); ?></td>

                <td>
                    <?php if ($currentLimit !== null && $qty <= $currentLimit): ?>
                        <span class="badge badge-low"><?php echo $qty; ?></span>
                    <?php else: ?>
                        <span class="badge badge-ok"><?php echo $qty; ?></span>
                    <?php endif; ?>
                </td>

                <td><?php echo e($row['units_packet'] ?? '-'); ?></td>

                <td>
                    <?php
                    echo isset($row['price_packet']) && $row['price_packet'] !== null
                        ? number_format((float)$row['price_packet'], 2)
                        : '-';
                    ?>
                </td>

                    <?php endforeach; ?>
    </tbody>
</table>
                    <?php endif; ?>
                </div>
            </div>
        </div>
    </div>
</div>

</body>
</html>