<?php
session_start();
ini_set('display_errors', 1);
error_reporting(E_ALL);

require_once '../../../dripend/includes/db.php';

$message = '';

// ==============================================================================
// 1. FETCH DICTIONARIES STRICTLY FROM DATABASE
// ==============================================================================
$suppliers = $conn->query("SELECT id, supplier_name FROM suppliers WHERE is_active = 1 ORDER BY supplier_name ASC")->fetch_all(MYSQLI_ASSOC);
$stores = $conn->query("SELECT id, location_name FROM inventory_locations WHERE location_type = 'Store' AND is_active = 1 ORDER BY location_name ASC")->fetch_all(MYSQLI_ASSOC);
$categories = $conn->query("SELECT id, category_name FROM item_categories WHERE is_active = 1 ORDER BY id ASC")->fetch_all(MYSQLI_ASSOC);

// Fetch Master Catalog
$catalog_items = $conn->query("
    SELECT sc.id, sc.service_name, sc.unit_of_pricing, sc.category_id 
    FROM service_catalog sc
    WHERE sc.is_active = 1 AND sc.service_type = 'Drug' 
    ORDER BY sc.service_name ASC
")->fetch_all(MYSQLI_ASSOC);

// Fetch Brands Dictionary
$brands = $conn->query("
    SELECT id, service_id, brand_name 
    FROM item_brands 
    WHERE is_active = 1 
    ORDER BY brand_name ASC
")->fetch_all(MYSQLI_ASSOC);

// ==============================================================================
// 2. PROCESS PURCHASE ORDER SUBMISSION
// ==============================================================================
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $supplier_id = (int)$_POST['supplier_id'];
    $location_id = (int)$_POST['location_id']; 
    $order_date = $_POST['order_date'];
    $expected_delivery = !empty($_POST['expected_delivery']) ? $_POST['expected_delivery'] : null;
    $remarks = trim($_POST['remarks'] ?? '');
    
    $service_ids = $_POST['service_id'] ?? [];
    $brand_names = $_POST['brand_name'] ?? [];
    $quantities = $_POST['quantity'] ?? [];
    $unit_costs = $_POST['unit_cost'] ?? [];

    if ($supplier_id > 0 && $location_id > 0 && !empty($service_ids)) {
        $conn->begin_transaction();
        try {
            $res = $conn->query("SELECT MAX(id) as max_id FROM purchase_orders");
            $max_id = $res->fetch_assoc()['max_id'] ?? 0;
            $po_number = "PO-" . date('Y') . "-" . str_pad($max_id + 1, 4, '0', STR_PAD_LEFT);

            $grand_total = 0;
            foreach ($service_ids as $index => $item_id) {
                if($item_id > 0) {
                    $grand_total += ((int)$quantities[$index] * (float)$unit_costs[$index]);
                }
            }

            // Insert Header
            $stmtHeader = $conn->prepare("
                INSERT INTO purchase_orders (po_number, supplier_id, location_id, order_date, expected_delivery, remarks, total_estimated_cost, status) 
                VALUES (?, ?, ?, ?, ?, ?, ?, 'Pending')
            ");
            $stmtHeader->bind_param("siisssd", $po_number, $supplier_id, $location_id, $order_date, $expected_delivery, $remarks, $grand_total);
            $stmtHeader->execute();
            $po_id = $conn->insert_id;
            $stmtHeader->close();

            // Insert Line Items
            $stmtLine = $conn->prepare("
                INSERT INTO purchase_order_items (po_id, service_id, brand_name, quantity_ordered, expected_unit_cost, total_line_cost) 
                VALUES (?, ?, ?, ?, ?, ?)
            ");
            foreach ($service_ids as $index => $service_id) {
                $qty = (int)$quantities[$index];
                $cost = (float)$unit_costs[$index];
                
                // Pure Database Logic: If brand is empty from DB dropdown, it inserts real SQL NULL
                $brand = !empty($brand_names[$index]) ? trim($brand_names[$index]) : null;
                $line_total = $qty * $cost;
                
                if ($service_id > 0 && $qty > 0) {
                    $stmtLine->bind_param("iisidd", $po_id, $service_id, $brand, $qty, $cost, $line_total);
                    $stmtLine->execute();
                }
            }
            $stmtLine->close();

            $conn->commit();
            $message = "<div class='alert alert-success'><i class='fa-solid fa-file-contract'></i> <strong>Purchase Order Created!</strong> $po_number has been saved and is pending approval.</div>";
        } catch (Exception $e) {
            $conn->rollback();
            $message = "<div class='alert alert-danger'><i class='fa-solid fa-triangle-exclamation'></i> Database Error: " . $e->getMessage() . "</div>";
        }
    } else {
        $message = "<div class='alert alert-danger'><i class='fa-solid fa-circle-exclamation'></i> Please select a Supplier, a Destination Store, and add at least one valid item.</div>";
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Create Purchase Order | Procurement</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
<style>
    :root { --sidebar-width: 240px; --e-blue: #1e3a8a; --bg: #f8fafc; --border: #e2e8f0; --text: #334155; }
    body { font-family: "Segoe UI", system-ui, sans-serif; background: var(--bg); margin: 0; padding: 0; color: var(--text); }
    
    .page-wrapper { margin-left: var(--sidebar-width); padding: 30px; min-height: 100vh; box-sizing: border-box; width: calc(100% - var(--sidebar-width)); }
    
    .header-bar { display: flex; justify-content: space-between; align-items: flex-end; margin-bottom: 25px; padding-bottom: 15px; border-bottom: 2px solid var(--border); }
    .header-bar h2 { color: #0f172a; margin: 0; font-size: 24px; font-weight: 800; display:flex; align-items:center; gap:10px; }

    .alert { padding: 15px 20px; border-radius: 8px; margin-bottom: 20px; font-weight: 600; display:flex; align-items:center; gap:10px; border-left: 5px solid; }
    .alert-success { background: #dcfce7; color: #166534; border-color: #22c55e; }
    .alert-danger { background: #fee2e2; color: #991b1b; border-color: #ef4444; }

    .section-box { background: #fff; border: 1px solid var(--border); border-radius: 12px; margin-bottom: 25px; box-shadow: 0 4px 10px rgba(0,0,0,0.02); overflow: hidden; }
    .section-header { padding: 15px 20px; font-size: 14px; font-weight: 800; text-transform: uppercase; color: #fff; display: flex; align-items: center; gap: 10px; background: #0f172a; }
    .section-body { padding: 25px; }

    .form-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; }
    .form-group { display: flex; flex-direction: column; gap: 6px; }
    .form-group label { font-size: 12px; font-weight: 700; color: #64748b; text-transform: uppercase; }
    .form-control { padding: 12px; border: 1px solid #cbd5e1; border-radius: 6px; font-size: 14px; outline: none; transition:0.2s; background:#f8fafc; box-sizing: border-box; width: 100%;}
    .form-control:focus { border-color: var(--e-blue); box-shadow: 0 0 0 3px #eff6ff; background:#fff;}
    
    .table-responsive { width: 100%; overflow-x: auto; display: block; border-top: 1px solid var(--border); }
    .cart-table { width: 100%; border-collapse: collapse; min-width: 1000px; }
    .cart-table th { background: #f1f5f9; color: #475569; padding: 12px; font-size: 12px; text-transform: uppercase; border-bottom: 2px solid var(--border); text-align: left; }
    .cart-table td { padding: 10px; border-bottom: 1px solid var(--border); vertical-align: middle; }
    
    .category-select { min-width: 150px; }
    .item-select { min-width: 250px; }
    .brand-select { min-width: 160px; }
    .qty-input { min-width: 80px; }
    .cost-input { min-width: 100px; }
    .line-total { min-width: 120px; }

    .btn { padding: 12px 25px; border-radius: 8px; font-weight: 800; cursor: pointer; font-size: 14px; transition: 0.2s; border: none; text-decoration:none; display:inline-flex; align-items:center; gap:8px; }
    .btn-secondary { background: #e2e8f0; color: #475569; }
    .btn-secondary:hover { background: #cbd5e1; }
    .btn-primary { background: #0f172a; color: #fff; box-shadow: 0 4px 6px rgba(15, 23, 42, 0.2); }
    .btn-primary:hover { background: #1e293b; transform: translateY(-1px); }
    .btn-add-row { background: #e0f2fe; color: #0284c7; border: 1px solid #bae6fd; padding: 8px 15px; font-size:13px; }
    .btn-add-row:hover { background: #bae6fd; }
    .btn-remove { background: #fee2e2; color: #dc2626; border: none; width:34px; height:34px; border-radius:6px; cursor:pointer; display:flex; justify-content:center; align-items:center;}
    .btn-remove:hover { background: #fca5a5; }

    .footer-flex { display: flex; flex-wrap: wrap; justify-content: space-between; align-items: flex-start; gap: 20px; padding: 25px; }
    .remarks-container { flex: 1; min-width: 300px; }
    .grand-total-box { font-size: 24px; font-weight: 900; color: #16a34a; text-align: right; min-width: 250px; }
    .remarks-box { resize: vertical; min-height: 80px; }
</style>
</head>
<body>

<?php include('../../../dripend/statpages/sidebar.php'); ?>

<div class="page-wrapper">
    <div style="margin-bottom:15px;">
        <a href="manage_products.php" style="color:#64748b; text-decoration:none; font-weight:700; font-size:13px;"><i class="fa-solid fa-arrow-left"></i> Back to Inventory</a>
    </div>

    <div class="header-bar">
        <h2><i class="fa-solid fa-file-contract" style="color:#0f172a;"></i> Create Purchase Order</h2>
    </div>

    <?= $message ?>

    <form method="post" id="poForm">
        <div class="section-box">
            <div class="section-header"><i class="fa-solid fa-truck"></i> Logistics & Timeline</div>
            <div class="section-body">
                <div class="form-grid">
                    <div class="form-group">
                        <label style="color:#0284c7;">1. Destination Store</label>
                        <select name="location_id" class="form-control" style="border-color:#0284c7; font-weight:bold; color:#0284c7;" required>
                            <option value="">-- Select Delivery Location --</option>
                            <?php foreach($stores as $store): ?>
                                <option value="<?= $store['id'] ?>"><?= htmlspecialchars($store['location_name']) ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <label>2. Select Supplier</label>
                        <select name="supplier_id" class="form-control" required>
                            <option value="">-- Choose Supplier --</option>
                            <?php foreach($suppliers as $sup): ?>
                                <option value="<?= $sup['id'] ?>"><?= htmlspecialchars($sup['supplier_name']) ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <label>3. Order Date</label>
                        <input type="date" name="order_date" class="form-control" value="<?= date('Y-m-d') ?>" required>
                    </div>
                    <div class="form-group">
                        <label>4. Expected Delivery</label>
                        <input type="date" name="expected_delivery" class="form-control">
                    </div>
                </div>
            </div>
        </div>

        <div class="section-box">
            <div class="section-header" style="background:#0284c7;">
                <span style="flex-grow:1;"><i class="fa-solid fa-cart-shopping"></i> Order Items</span>
                <button type="button" class="btn btn-add-row" onclick="addPORow()"><i class="fa-solid fa-plus"></i> Add Line Item</button>
            </div>
            
            <div class="table-responsive">
                <table class="cart-table" id="poTable">
                    <thead>
                        <tr>
                            <th style="padding-left:20px;">Category</th>
                            <th>Generic Item (Catalog)</th>
                            <th style="color:#0284c7;">Select Brand</th>
                            <th>Quantity</th>
                            <th>Unit Cost (GHS)</th>
                            <th>Line Total</th>
                            <th style="text-align:center;">Del</th>
                        </tr>
                    </thead>
                    <tbody id="cartBody">
                        <tr>
                            <td style="padding-left:20px;">
                                <select class="form-control category-select" onchange="filterItems(this.closest('tr'))">
                                    <option value="">-- All --</option>
                                    <?php foreach($categories as $cat): ?>
                                        <option value="<?= $cat['id'] ?>"><?= htmlspecialchars($cat['category_name']) ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </td>
                            <td>
                                <select name="service_id[]" class="form-control item-select" required onchange="filterBrands(this.closest('tr')); calculateTotals();">
                                    <option value="">-- Select Item --</option>
                                </select>
                            </td>
                            <td>
                                <select name="brand_name[]" class="form-control brand-select" style="border-color:#bae6fd;">
                                    <option value="">-- Select Brand --</option>
                                </select>
                            </td>
                            <td>
                                <input type="number" name="quantity[]" class="form-control qty-input" min="1" value="1" required oninput="calculateTotals()">
                            </td>
                            <td>
                                <input type="number" name="unit_cost[]" class="form-control cost-input" step="0.01" min="0" placeholder="0.00" required oninput="calculateTotals()">
                            </td>
                            <td>
                                <input type="text" class="form-control line-total" value="0.00" readonly style="background:#f1f5f9; font-weight:bold; color:#0f172a; border-color:transparent;">
                            </td>
                            <td style="text-align:center;">
                                <button type="button" class="btn-remove" onclick="removeRow(this)"><i class="fa-solid fa-trash"></i></button>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </div>
                
            <div class="footer-flex">
                <div class="form-group remarks-container">
                    <label><i class="fa-solid fa-clipboard-list"></i> Procurement Remarks / Instructions to Supplier</label>
                    <textarea name="remarks" class="form-control remarks-box" placeholder="e.g. Urgent delivery needed. Ensure expiry date is at least 18 months from delivery."></textarea>
                </div>
                <div class="grand-total-box">
                    Total Order Estimate:<br>
                    <span style="color:#0f172a;">GHS <span id="grandTotalDisplay">0.00</span></span>
                </div>
            </div>
        </div>

        <div style="display:flex; justify-content:flex-end; gap:15px; padding-bottom:30px;">
            <a href="manage_products.php" class="btn btn-secondary">Cancel Order</a>
            <button type="submit" class="btn btn-primary"><i class="fa-solid fa-paper-plane"></i> Submit Purchase Order</button>
        </div>
    </form>
</div>

<template id="rowTemplate">
    <tr>
        <td style="padding-left:20px;">
            <select class="form-control category-select" onchange="filterItems(this.closest('tr'))">
                <option value="">-- All --</option>
                <?php foreach($categories as $cat): ?>
                    <option value="<?= $cat['id'] ?>"><?= htmlspecialchars($cat['category_name']) ?></option>
                <?php endforeach; ?>
            </select>
        </td>
        <td>
            <select name="service_id[]" class="form-control item-select" required onchange="filterBrands(this.closest('tr')); calculateTotals();">
                <option value="">-- Select Item --</option>
            </select>
        </td>
        <td>
            <select name="brand_name[]" class="form-control brand-select" style="border-color:#bae6fd;">
                <option value="">-- Select Brand --</option>
            </select>
        </td>
        <td><input type="number" name="quantity[]" class="form-control qty-input" min="1" value="1" required oninput="calculateTotals()"></td>
        <td><input type="number" name="unit_cost[]" class="form-control cost-input" step="0.01" min="0" placeholder="0.00" required oninput="calculateTotals()"></td>
        <td><input type="text" class="form-control line-total" value="0.00" readonly style="background:#f1f5f9; font-weight:bold; color:#0f172a; border-color:transparent;"></td>
        <td style="text-align:center;">
            <button type="button" class="btn-remove" onclick="removeRow(this)"><i class="fa-solid fa-trash"></i></button>
        </td>
    </tr>
</template>

<script>
// Load data from PHP into JS Arrays (100% Database Driven)
const masterCatalog = <?= json_encode($catalog_items) ?>;
const masterBrands = <?= json_encode($brands) ?>;

// 1. Filter the Generic Items based on Category
function filterItems(row) {
    const catId = row.querySelector('.category-select').value;
    const itemSelect = row.querySelector('.item-select');
    
    itemSelect.innerHTML = '<option value="">-- Select Item --</option>';
    
    let filteredItems = masterCatalog;
    if (catId !== "") {
        filteredItems = masterCatalog.filter(item => item.category_id == catId);
    }
    
    filteredItems.forEach(item => {
        let opt = document.createElement('option');
        opt.value = item.id;
        opt.textContent = `${item.service_name} (Base: ${item.unit_of_pricing})`;
        itemSelect.appendChild(opt);
    });
    
    // Reset the brand dropdown since item changed
    filterBrands(row);
}

// 2. Filter the Brands based on the Generic Item selected (No Hardcoded Fallbacks)
function filterBrands(row) {
    const serviceId = row.querySelector('.item-select').value;
    const brandSelect = row.querySelector('.brand-select');
    
    brandSelect.innerHTML = '<option value="">-- Select Brand --</option>';
    
    if (serviceId && serviceId !== "") {
        const filteredBrands = masterBrands.filter(brand => brand.service_id == serviceId);
        
        if (filteredBrands.length > 0) {
            filteredBrands.forEach(brand => {
                let opt = document.createElement('option');
                opt.value = brand.brand_name; // Value sent to DB
                opt.textContent = brand.brand_name; // Text displayed to user
                brandSelect.appendChild(opt);
            });
        } else {
            // If the DB explicitly has NO brands for this item, display generic notification
            let opt = document.createElement('option');
            opt.value = ""; 
            opt.textContent = "-- No DB Brands (Generic) --";
            brandSelect.appendChild(opt);
        }
    }
}

function addPORow() {
    const template = document.getElementById('rowTemplate');
    const tbody = document.getElementById('cartBody');
    const clone = template.content.cloneNode(true);
    tbody.appendChild(clone);
    
    const newRow = tbody.lastElementChild;
    filterItems(newRow); // Initialize item dropdown for the new row
}

function removeRow(button) {
    const row = button.closest('tr');
    const tbody = document.getElementById('cartBody');
    if (tbody.children.length > 1) {
        row.remove();
        calculateTotals();
    } else {
        alert("You must have at least one item in the order.");
    }
}

function calculateTotals() {
    const rows = document.querySelectorAll('#cartBody tr');
    let grandTotal = 0;

    rows.forEach(row => {
        const qty = parseFloat(row.querySelector('.qty-input').value) || 0;
        const cost = parseFloat(row.querySelector('.cost-input').value) || 0;
        const lineTotal = qty * cost;
        
        row.querySelector('.line-total').value = lineTotal.toFixed(2);
        grandTotal += lineTotal;
    });

    document.getElementById('grandTotalDisplay').innerText = grandTotal.toLocaleString('en-US', {minimumFractionDigits: 2, maximumFractionDigits: 2});
}

// Initialize the first row on page load
document.addEventListener("DOMContentLoaded", function() {
    const firstRow = document.querySelector('#cartBody tr');
    filterItems(firstRow);
});
</script>

</body>
</html>