<?php
include 'db.php';
include 'auth_check.php';

$msg = "";

// Fetch items with available quantity (by purchase_stock.id)
$item_query = "
SELECT 
    p.id AS purchase_stock_id,
    p.item_name,
    p.particular_name,
    p.specification,
    p.model_number,
    COALESCE(SUM(p.qty),0) AS purchased_qty,
    COALESCE(SUM(f.total_fault),0) AS fault_qty,
    COALESCE(SUM(o.total_out),0) AS outward_qty,
    (COALESCE(SUM(p.qty),0) - COALESCE(SUM(f.total_fault),0) - COALESCE(SUM(o.total_out),0)) AS available_qty
FROM purchase_stock p
LEFT JOIN (
    SELECT purchase_stock_id, SUM(qty) AS total_fault
    FROM fault_stock
    GROUP BY purchase_stock_id
) f ON f.purchase_stock_id = p.id
LEFT JOIN (
    SELECT purchase_stock_id, SUM(qty) AS total_out
    FROM outward_stock
    GROUP BY purchase_stock_id
) o ON o.purchase_stock_id = p.id
GROUP BY p.id, p.item_name, p.particular_name, p.specification, p.model_number
HAVING available_qty > 0
ORDER BY p.item_name ASC

";

$items_result = $conn->query($item_query);

// Handle Add Outward Entry
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['add_outward'])) {
    $outward_date = $_POST['outward_date'];
    $given_to = trim($_POST['given_to']);
    $purchase_stock_id = intval($_POST['purchase_stock_id']);
    $qty = intval($_POST['qty']);
    $remarks = trim($_POST['remarks']);

    // Get available quantity for this purchase_stock_id
    $check_qty_sql = "
        SELECT 
            (p.qty 
             - COALESCE((SELECT SUM(o.qty) 
                         FROM outward_stock o 
                         WHERE o.purchase_stock_id = p.id), 0)
             - COALESCE((SELECT SUM(f.qty) 
                         FROM fault_stock f 
                         WHERE f.purchase_stock_id = p.id), 0)
            ) AS available_qty,
            p.item_name, p.particular_name, p.specification, p.model_number
        FROM purchase_stock p
        WHERE p.id = $purchase_stock_id
        LIMIT 1
    ";
    $check_qty_result = $conn->query($check_qty_sql);
    $check_qty = $check_qty_result ? $check_qty_result->fetch_assoc() : null;

    if (!$check_qty) {
        $msg = "Selected item not found in stock!";
    } elseif ($qty > $check_qty['available_qty']) {
        $msg = "Requested quantity exceeds available stock!";
    } else {
        // Generate OS number
        $prefix = "OS";
        $last = $conn->query("SELECT os_number FROM outward_stock ORDER BY id DESC LIMIT 1");
        $lastNumber = ($last && $last->num_rows > 0) ? intval(substr($last->fetch_assoc()['os_number'], 2)) : 0;
        $os_number = $prefix . str_pad($lastNumber + 1, 6, '0', STR_PAD_LEFT);

        $item = $conn->real_escape_string($check_qty['item_name']);
        $particular = $conn->real_escape_string($check_qty['particular_name']);
        $spec = $conn->real_escape_string($check_qty['specification']);
        $model = $conn->real_escape_string($check_qty['model_number']);
        $remarks_safe = $conn->real_escape_string($remarks);

        $sql = "INSERT INTO outward_stock 
                (outward_date, given_to, purchase_stock_id, item_name, particular_name, specification, model_number, qty, remarks, os_number)
                VALUES 
                ('$outward_date','$given_to',$purchase_stock_id,'$item','$particular','$spec','$model',$qty,'$remarks_safe','$os_number')";

        if ($conn->query($sql)) {
            header("Location: outward_view.php");
            exit;
        } else {
            $msg = "Error: " . $conn->error;
        }
    }
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Outward Stock</title>
    <link rel="icon" type="image/png" href="assets/logo/logo_icon.png">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body { display: flex; flex-direction: column; min-height: 100vh; }
        .flex-grow-1 { flex: 1; }
        footer { background: #f8f9fa; padding: 10px 0; }
    </style>
</head>
<body class="bg-light">

<?php include 'navbar.php'; ?>

<div class="flex-grow-1">
    <div class="container mt-2">
        <h2 class="mb-4 text-center text-success">Outward Stock Entry</h2>

        <?php if($msg != "") { ?>
            <div class="alert alert-info text-center"><?= $msg ?></div>
        <?php } ?>

        <!-- Add Outward Form -->
        <form method="POST" class="row g-2 shadow p-2 bg-white rounded mb-3">
            <input type="hidden" name="add_outward" value="1">
            <div class="col-md-4">
                <label class="form-label">Outward Date</label>
                <input type="date" class="form-control" name="outward_date" required>
            </div>
            <div class="col-md-4">
                <label class="form-label">Given To</label>
                <input type="text" class="form-control" name="given_to" required>
            </div>
            <div class="col-md-4">
                <label class="form-label">Item</label>
                <select name="purchase_stock_id" class="form-select" required onchange="updateQtyLimit(this)">
                    <option value="">-- Select Item --</option>
                    <?php
                    if ($items_result && $items_result->num_rows > 0) {
                        while($row = $items_result->fetch_assoc()){
                            echo "<option value='{$row['purchase_stock_id']}' data-available='{$row['available_qty']}'>".
                                $row['item_name']." | ".$row['particular_name']." | ".$row['specification']." | ".$row['model_number']." (Available: ".$row['available_qty'].")".
                                "</option>";
                        }
                    }
                    ?>
                </select>
            </div>

            <div class="col-md-4">
                <label class="form-label">Quantity</label>
                <input type="number" class="form-control" id="qtyInput" name="qty" min="1" required>
                <small class="text-muted" id="availableInfo"></small>
            </div>
            <div class="col-md-4">
                <label class="form-label">Remarks</label>
                <input type="text" class="form-control" name="remarks">
            </div>
            <div class="col-12 text-center">
                <button type="submit" class="btn btn-success">Submit</button>
            </div>
        </form>
    </div>
</div>

<?php
if (session_status() == PHP_SESSION_NONE) session_start();
include 'footer.php';
?>

<script>
function updateQtyLimit(sel){
    const selected = sel.options[sel.selectedIndex];
    const available = selected.getAttribute('data-available');
    const qtyInput = document.getElementById('qtyInput');
    const info = document.getElementById('availableInfo');

    if(available){
        qtyInput.max = available;
        qtyInput.value = (available > 0 ? 1 : 0);
        info.innerText = "Available: " + available;
    } else {
        qtyInput.max = 0;
        qtyInput.value = 0;
        info.innerText = "";
    }
}
</script>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
