// assets/js/inventory.js

let productsData = [];
let categoriesData = [];
let unitsData = [];
let allMovementsData = [];
let appSettings = {};

document.addEventListener('DOMContentLoaded', async () => {
    const user = await checkAuth();
    if (!user) return;

    appSettings = await getAppSettings() || { company_currency_code: 'USD' };
    applyCurrencySymbols();

    loadInventoryData();

    // Form handlers
    document.getElementById('productForm').addEventListener('submit', saveProduct);
    document.getElementById('categoryForm').addEventListener('submit', saveCategory);
    document.getElementById('unitForm').addEventListener('submit', saveUnit);
    document.getElementById('adjustmentForm').addEventListener('submit', applyAdjustment);
    document.getElementById('importForm').addEventListener('submit', importProducts);

    // Search & Filter
    document.getElementById('inventorySearch').addEventListener('input', filterCatalog);
    document.getElementById('filterCategory').addEventListener('change', filterCatalog);



    // Initialize Select2 specifically for Bootstrap 5
    $('.select2-init').each(function () {
        const $this = $(this);
        const parentModal = $this.closest('.modal');
        $this.select2({
            dropdownParent: parentModal.length ? parentModal : $(document.body),
            width: '100%',
            theme: 'bootstrap-5'
        });
    });

    $('#filterMovementProduct').on('change', filterMovements);

    // Handle re-initialization when modals are shown to ensure dropdownParent is correct
    // (In case modals were moved or recreated, though here they are static)
    $('.modal').on('shown.bs.modal', function () {
        $(this).find('.select2-init').each(function () {
            $(this).select2({
                dropdownParent: $(this).closest('.modal'),
                width: '100%',
                theme: 'bootstrap-5'
            });
        });
    });

    // Thousand Separator for prices
    document.querySelectorAll('.thousand-separator').forEach(input => {
        input.addEventListener('input', function (e) {
            let value = this.value.replace(/,/g, '');
            if (!isNaN(value) && value !== '') {
                // Keep cursor position logic could be added but let's keep it simple
                this.value = parseFloat(value).toLocaleString('en-US', { minimumFractionDigits: 0, maximumFractionDigits: 0 });
            }
        });
    });

    // Listen for changes from other tabs
    syncChannel.onmessage = (event) => {
        if (event.data.type === 'INVENTORY_CHANGED') {
            loadInventoryData();
        }
    };

    // Product selection in Adjustment Modal
    $('#adj_product_select_field').on('change', function () {
        const id = $(this).val();
        if (!id) return;

        const p = productsData.find(x => x.id == id);
        if (p) {
            document.getElementById('adj_product_id').value = p.id;
            document.getElementById('adj_current_stock').value = `${p.stock_quantity} ${p.unit_short_name || 'pcs'}`;

            // Handle Batch Fields visibility
            const batchFields = document.getElementById('adj_batch_fields');
            if (p.track_batches == 1) {
                batchFields.classList.remove('d-none');
                document.getElementById('adj_batch_number').required = true;
                document.getElementById('adj_expiry_date').required = true;
            } else {
                batchFields.classList.add('d-none');
                document.getElementById('adj_batch_number').required = false;
                document.getElementById('adj_expiry_date').required = false;
            }
        }
    });
});

function applyCurrencySymbols() {
    const symbols = document.querySelectorAll('.currency-symbol');
    symbols.forEach(s => s.innerText = appSettings.company_currency_code || 'USD');
}

async function loadInventoryData() {
    // Stage 1: Fast Data (Settings & Current User)
    fetch('api/settings.php?action=get').then(r => r.json()).then(data => {
        if (data.success) {
            appSettings = data.data;
            applyCurrencySymbols();
        }
    }).catch(e => console.error("Settings error:", e));

    // Stage 2: Core Analysis Data (Stats & Categories)
    fetch('api/products.php?action=stats').then(r => r.json()).then(data => {
        if (data.success) renderStats(data.data);
    }).catch(e => console.error("Stats error:", e));

    fetch('api/products.php?action=list_categories').then(r => r.json()).then(data => {
        if (data.success) {
            categoriesData = data.data;
            renderCategories();
            populateCategoryFilter();
        }
    }).catch(e => console.error("Categories error:", e));

    fetch('api/products.php?action=list_units').then(r => r.json()).then(data => {
        if (data.success) {
            unitsData = data.data;
            renderUnits();
            populateUnitSelect();
        }
    }).catch(e => console.error("Units error:", e));


    // Stage 3: Inventory Catalog (Main Table)
    fetch('api/products.php?action=list').then(r => r.json()).then(data => {
        if (data.success) {
            productsData = data.data;
            renderProducts(productsData);
            populateAdjustmentSelect();
            filterCatalog(); // Re-apply current search if any
            renderInventoryReports(); // Base on current products
        }
    }).catch(e => console.error("Main Catalog error:", e));


    // Stage 4: Heavy Logs (May take time on large DBs)
    fetch('api/inventory.php?action=movements').then(r => r.json()).then(data => {
        if (data.success) {
            allMovementsData = data.data;
            populateMovementProductFilter();
            renderMovements(allMovementsData);
        }
    }).catch(e => console.error("Movements Log error:", e));
}

function renderProducts(products) {
    if ($.fn.DataTable.isDataTable('#productsTable')) {
        $('#productsTable').DataTable().destroy();
    }

    const tbody = document.getElementById('productsBody');
    tbody.innerHTML = '';

    products.forEach(p => {
        const stockStatus = p.stock_quantity <= 0 ? 'bg-danger' : (p.stock_quantity <= p.min_stock_level ? 'bg-warning' : 'bg-success');
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td>
                <div class="fw-bold text-dark">${p.name}</div>
                <div class="small text-muted">${p.unit_name || 'pcs'}</div>
            </td>
            <td>
                <div class="badge bg-light text-dark border fw-normal">${p.sku || 'N/A'}</div>
                <div class="small text-muted mt-1">${p.barcode || ''}</div>
            </td>
            <td><span class="badge bg-secondary opacity-75">${p.category_name || 'Uncategorized'}</span> <span class="d-none">${p.category_id || 0}</span></td>
            <td class="text-end fw-bold">${formatCurrency(p.cost_price)}</td>
            <td class="text-end fw-bold text-primary">${formatCurrency(p.selling_price)}</td>
            <td class="text-center">
                <span class="badge ${stockStatus} rounded-pill px-3">${p.stock_quantity}</span>
            </td>
            <td class="text-end fw-bold text-dark">
                ${formatCurrency(p.stock_quantity * p.cost_price)}
            </td>
            <td class="text-end pe-4">
                <div class="btn-group btn-group-sm">
                    ${p.track_batches == 1 ? `
                    <button class="btn btn-outline-info" onclick="viewBatches(${p.id})" title="View Batches/Expiry">
                        <i class="fa-solid fa-calendar-days"></i>
                    </button>` : ''}
                    <button class="btn btn-outline-warning" onclick="adjustProductStock(${p.id})" title="Adjust Stock">
                        <i class="fa-solid fa-sliders"></i>
                    </button>
                    <button class="btn btn-outline-primary" onclick="editProduct(${p.id})" title="Modify Product Data">
                        <i class="fa-solid fa-pen-to-square"></i>
                    </button>
                    <button class="btn btn-outline-danger" onclick="deleteProduct(${p.id})" title="Decommission SKU">
                        <i class="fa-solid fa-trash"></i>
                    </button>
                </div>
            </td>
        `;
        tbody.appendChild(tr);
    });

    $('#productsTable').DataTable({
        dom: 'rt<"row mt-3"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
        pageLength: 10,
        ordering: true,
        stateSave: true, // Maintain page and search state after redraw
        order: [], // Use server-side order (ID DESC) by default
        language: { emptyTable: "No products in catalog." },
        footerCallback: function (row, data, start, end, display) {
            const api = this.api();
            const symbol = appSettings.company_currency_code || 'USD';
            
            // Remove formatting to get integer data for summation
            const intVal = function (i) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '').replace(/[^-0-9\.]/g, '') * 1 :
                    typeof i === 'number' ?
                        i : 0;
            };

            // Total over all pages
            const total = api
                .column(6)
                .data()
                .reduce(function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0);

            // Total over this page
            const pageTotal = api
                .column(6, { page: 'current' })
                .data()
                .reduce(function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0);

            // Update footer
            $(api.column(6).footer()).html(
                `${symbol} ${new Intl.NumberFormat('en-US', { minimumFractionDigits: 0 }).format(pageTotal)}`
            );
        }
    });
}

function renderStats(stats) {
    document.getElementById('stat_total_items').innerText = stats.total_items;
    document.getElementById('stat_stock_value').innerText = formatCurrency(stats.stock_value);
    document.getElementById('stat_low_stock').innerText = stats.low_stock;
    document.getElementById('stat_expired').innerText = stats.expired_items || 0;
    document.getElementById('stat_categories').innerText = stats.total_categories;
}

function renderMovements(movements) {
    if ($.fn.DataTable.isDataTable('#movementsTable')) {
        $('#movementsTable').DataTable().destroy();
    }

    const tbody = document.getElementById('movementsBody');
    tbody.innerHTML = '';

    movements.forEach(m => {
        let typeBadge = 'bg-secondary';
        if (m.type === 'Purchase') typeBadge = 'bg-success';
        else if (m.type === 'Sale') typeBadge = 'bg-primary';
        else if (['Damage', 'Loss', 'Out'].includes(m.type)) typeBadge = 'bg-danger';
        else if (m.type === 'In') typeBadge = 'bg-info';

        const date = new Date(m.date).toLocaleString();
        const tr = document.createElement('tr');
        tr.innerHTML = `
            <td class="small text-muted">${date}</td>
            <td><span class="badge ${typeBadge}">${m.type}</span></td>
            <td class="fw-bold">${m.product_name}</td>
            <td class="small text-truncate" style="max-width: 200px;">${m.identifier || '-'}</td>
            <td class="fw-bold ${m.qty < 0 ? 'text-danger' : 'text-success'}">${m.qty > 0 ? '+' : ''}${m.qty}</td>
            <td class="small text-muted">${m.username}</td>
        `;
        tbody.appendChild(tr);
    });

    $('#movementsTable').DataTable({
        dom: 'rt<"row mt-3"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
        pageLength: 10,
        ordering: true,
        stateSave: true,
        order: [[0, 'desc']],
        language: { emptyTable: "No movements logged." }
    });
}

function renderCategories() {
    const list = document.getElementById('categoryList');
    list.innerHTML = '';

    categoriesData.forEach(c => {
        const item = document.createElement('div');
        item.className = 'list-group-item d-flex justify-content-between align-items-center py-2';
        item.innerHTML = `
            <span class="fw-bold px-2">${c.name}</span>
            <div class="btn-group btn-group-sm">
                <button class="btn btn-link text-primary p-0 me-2" onclick="editCategory(${c.id})">
                    <i class="fa-solid fa-edit"></i>
                </button>
                <button class="btn btn-link text-danger p-0" onclick="deleteCategory(${c.id})">
                    <i class="fa-solid fa-times-circle"></i>
                </button>
            </div>
        `;
        list.appendChild(item);
    });
}

function renderUnits() {
    const list = document.getElementById('unitList');
    list.innerHTML = '';

    unitsData.forEach(u => {
        const item = document.createElement('div');
        item.className = 'list-group-item d-flex justify-content-between align-items-center py-2';
        item.innerHTML = `
            <span class="fw-bold px-2">${u.name} <small class="text-muted">(${u.short_name})</small></span>
            <div class="btn-group btn-group-sm">
                <button class="btn btn-link text-primary p-0 me-2" onclick="editUnit(${u.id})">
                    <i class="fa-solid fa-edit"></i>
                </button>
                <button class="btn btn-link text-danger p-0" onclick="deleteUnit(${u.id})">
                    <i class="fa-solid fa-times-circle"></i>
                </button>
            </div>
        `;
        list.appendChild(item);
    });
}

function populateCategoryFilter() {
    const filter = document.getElementById('filterCategory');
    const select = document.getElementById('product_category');

    filter.innerHTML = '<option value="">All Categories</option>';
    let options = '<option value="">UNCATEGORIZED</option>';

    categoriesData.forEach(c => {
        filter.innerHTML += `<option value="${c.id}">${c.name}</option>`;
        options += `<option value="${c.id}">${c.name}</option>`;
    });
    select.innerHTML = options;
}

function populateUnitSelect() {
    const select = document.getElementById('product_unit');
    let options = '';
    unitsData.forEach(u => {
        options += `<option value="${u.id}">${u.name} (${u.short_name})</option>`;
    });
    select.innerHTML = options;
}

function populateAdjustmentSelect() {
    const adjSelect = document.getElementById('adj_product_select_field');
    if (!adjSelect) return;

    const options = '<option value="">-- Choose Product --</option>' + productsData.map(p => `<option value="${p.id}">${p.name} (${p.sku || 'No SKU'})</option>`).join('');
    adjSelect.innerHTML = options;
}

function populateMovementProductFilter() {
    const filter = document.getElementById('filterMovementProduct');
    if (!filter) return;

    const currentVal = $(filter).val();
    const productNames = [...new Set(allMovementsData.map(m => m.product_name))].sort();

    let options = '<option value="">All Products</option>';
    productNames.forEach(name => {
        options += `<option value="${name}">${name}</option>`;
    });

    filter.innerHTML = options;
    if (productNames.includes(currentVal)) {
        $(filter).val(currentVal);
    }
    $(filter).trigger('change.select2');
}

function filterCatalog() {
    const searchInput = document.getElementById('inventorySearch');
    const filterCat = document.getElementById('filterCategory');
    if (!searchInput || !filterCat) return;

    const search = searchInput.value;
    const catId = filterCat.value;
    
    const table = $('#productsTable').DataTable();
    table.search(search);
    
    if (catId) {
        table.column(2).search(catId);
    } else {
        table.column(2).search('');
    }
    table.draw();
}

function filterMovements() {
    const productName = document.getElementById('filterMovementProduct').value;
    const table = $('#movementsTable').DataTable();
    table.column(2).search(productName).draw();
}

function exportCatalog(format) {
    window.location.href = `api/export_reports.php?report=inventory&format=${format}`;
}

function exportMovements(format) {
    window.location.href = `api/export_reports.php?report=inventory-movements&format=${format}`;
}

function getPreviewSKU() {
    let template = appSettings.sku_template || 'PROD-{MMYYYY}-00000';
    let nextNum = parseInt(appSettings.sku_next_number || 1);

    let now = new Date();
    let mm = String(now.getMonth() + 1).padStart(2, '0');
    let yyyy = String(now.getFullYear());
    let yy = yyyy.slice(-2);

    // 1. Mask Date placeholders
    let sku = template.replace('{MMYYYY}', '[MMYYYY]')
        .replace('{MM}', '[MM]')
        .replace('{YYYY}', '[YYYY]')
        .replace('{YY}', '[YY]');

    // 2. Handle Serial Zeros
    sku = sku.replace(/\{?(0+)\}?/, (match, zeros) => {
        return String(nextNum).padStart(zeros.length, '0');
    });

    // 3. Unmask and replace with real dates
    sku = sku.replace('[MMYYYY]', mm + yyyy)
        .replace('[MM]', mm)
        .replace('[YYYY]', yyyy)
        .replace('[YY]', yy);

    return sku;
}

// Global scope functions for buttons
window.resetProductForm = () => {
    document.getElementById('productForm').reset();
    document.getElementById('product_id').value = '';

    // Set Preview SKU
    document.getElementById('product_sku').value = getPreviewSKU();
    document.getElementById('product_sku').disabled = true;
    document.getElementById('product_sku').classList.add('bg-light');

    document.getElementById('product_track_batches').checked = false;
    $('.select2-init').val('').trigger('change');
};

window.editProduct = (id) => {
    const p = productsData.find(x => x.id == id);
    if (!p) return;

    document.getElementById('product_id').value = p.id;
    document.getElementById('product_name').value = p.name;
    document.getElementById('product_barcode').value = p.barcode || '';

    // Set Existing SKU
    document.getElementById('product_sku').value = p.sku || '';
    document.getElementById('product_sku').disabled = true;
    document.getElementById('product_sku').classList.add('bg-light');

    $('#product_category').val(p.category_id || '').trigger('change');
    $('#product_unit').val(p.unit_id || '').trigger('change');

    document.getElementById('product_cost').value = parseFloat(p.cost_price).toLocaleString('en-US', { minimumFractionDigits: 0 });
    document.getElementById('product_selling').value = parseFloat(p.selling_price).toLocaleString('en-US', { minimumFractionDigits: 0 });
    document.getElementById('product_min_stock').value = p.min_stock_level || 5;
    document.getElementById('product_track_batches').checked = p.track_batches == 1;

    const modal = bootstrap.Modal.getOrCreateInstance(document.getElementById('productModal'));
    modal.show();
};


window.openQuickAdjustment = () => {
    const form = document.getElementById('adjustmentForm');
    form.reset();
    
    document.getElementById('adj_product_id').value = '';
    document.getElementById('adj_current_stock').value = '0';
    
    // UI states
    document.getElementById('adj_product_selection_container').classList.remove('d-none');
    document.getElementById('adj_product_name_container').classList.add('d-none');
    document.getElementById('adj_batch_fields').classList.add('d-none');
    
    $('#adj_product_select_field').val('').trigger('change.select2');
    
    const modal = bootstrap.Modal.getOrCreateInstance(document.getElementById('adjustmentModal'));
    modal.show();
};

window.adjustProductStock = (id) => {
    const p = productsData.find(x => x.id == id);
    if (!p) return;

    const form = document.getElementById('adjustmentForm');
    form.reset();
    
    document.getElementById('adj_product_id').value = p.id;
    document.getElementById('adj_product_name').value = p.name;
    document.getElementById('adj_current_stock').value = `${p.stock_quantity} ${p.unit_short_name || 'pcs'}`;
    
    // UI states
    document.getElementById('adj_product_selection_container').classList.add('d-none');
    document.getElementById('adj_product_name_container').classList.remove('d-none');

    // Handle Batch Fields visibility
    const batchFields = document.getElementById('adj_batch_fields');
    if (p.track_batches == 1) {
        batchFields.classList.remove('d-none');
        document.getElementById('adj_batch_number').required = true;
        document.getElementById('adj_expiry_date').required = true;
    } else {
        batchFields.classList.add('d-none');
        document.getElementById('adj_batch_number').required = false;
        document.getElementById('adj_expiry_date').required = false;
    }

    const modal = bootstrap.Modal.getOrCreateInstance(document.getElementById('adjustmentModal'));
    modal.show();
};




async function saveProduct(e) {
    e.preventDefault();
    const id = document.getElementById('product_id').value;
    const action = id ? 'update' : 'create';


    const fd = new FormData(e.target);
    fd.set('track_batches', document.getElementById('product_track_batches').checked ? '1' : '0');


    const btn = e.submitter || e.target.querySelector('button[type="submit"]');
    const originalBtnHtml = btn.innerHTML;
    btn.disabled = true;
    btn.innerHTML = '<span class="spinner-border spinner-border-sm me-2"></span> Saving...';

    try {
        const res = await fetch(`api/products.php?action=${action}`, { method: 'POST', body: fd });
        const data = await res.json();
        if (data.success) {
            bootstrap.Modal.getInstance(document.getElementById('productModal')).hide();
            showToast(data.message);

            // If it's a new product, clear search so it's visible at the top
            if (action === 'create') {
                document.getElementById('inventorySearch').value = '';
                document.getElementById('filterCategory').value = '';
            }
            
            // Refresh data immediately
            loadInventoryData();
            notifyInventoryChange();

            // Refresh settings in background to get the latest SKU sequence if needed
            getAppSettings().then(newSettings => {
                if (newSettings) appSettings = newSettings;
            });
        } else {
            Swal.fire('Error', data.message, 'error');
        }
    } catch (err) {
        Swal.fire('Error', 'Communication failure', 'error');
    } finally {
        btn.disabled = false;
        btn.innerHTML = originalBtnHtml;
    }
}

async function saveCategory(e) {
    e.preventDefault();
    const btn = e.submitter || e.target.querySelector('button[type="submit"]');
    const originalText = btn.innerHTML;
    btn.disabled = true;
    btn.innerHTML = '<span class="spinner-border spinner-border-sm"></span>';

    const id = document.getElementById('category_id').value;
    const action = id ? 'update_category' : 'create_category';
    const fd = new FormData(e.target);
    try {
        const res = await fetch(`api/products.php?action=${action}`, { method: 'POST', body: fd });
        const data = await res.json();
        if (data.success) {
            e.target.reset();
            document.getElementById('category_id').value = '';
            loadInventoryData();
            notifyInventoryChange();
            showToast(data.message);
        } else {
            Swal.fire('Error', data.message, 'error');
        }
    } catch (e) {
        Swal.fire('Error', 'Action failed', 'error');
    } finally {
        btn.disabled = false;
        btn.innerHTML = originalText;
    }
}

window.editCategory = (id) => {
    const c = categoriesData.find(x => x.id == id);
    if (!c) return;
    document.getElementById('category_id').value = c.id;
    document.getElementById('category_name').value = c.name;
};

async function deleteCategory(id) {
    const { isConfirmed } = await Swal.fire({ title: 'Remove Category?', showCancelButton: true });
    if (isConfirmed) {
        const fd = new FormData();
        fd.append('id', id);
        await fetch('api/products.php?action=delete_category', { method: 'POST', body: fd });
        loadInventoryData();
    }
}

async function saveUnit(e) {
    e.preventDefault();
    const btn = e.submitter || e.target.querySelector('button[type="submit"]');
    const originalText = btn.innerHTML;
    btn.disabled = true;
    btn.innerHTML = '<span class="spinner-border spinner-border-sm"></span>';

    const id = document.getElementById('unit_id').value;
    const action = id ? 'update_unit' : 'create_unit';
    const fd = new FormData(e.target);
    try {
        const res = await fetch(`api/products.php?action=${action}`, { method: 'POST', body: fd });
        const data = await res.json();
        if (data.success) {
            e.target.reset();
            document.getElementById('unit_id').value = '';
            loadInventoryData();
            notifyInventoryChange();
            showToast(data.message);
        } else {
            Swal.fire('Error', data.message, 'error');
        }
    } catch (e) {
        Swal.fire('Error', 'Action failed', 'error');
    } finally {
        btn.disabled = false;
        btn.innerHTML = originalText;
    }
}

window.editUnit = (id) => {
    const u = unitsData.find(x => x.id == id);
    if (!u) return;
    document.getElementById('unit_id').value = u.id;
    document.getElementById('unit_name').value = u.name;
    document.getElementById('unit_short_name').value = u.short_name;
};

async function deleteUnit(id) {
    const { isConfirmed } = await Swal.fire({ title: 'Remove Unit?', showCancelButton: true });
    if (isConfirmed) {
        const fd = new FormData();
        fd.append('id', id);
        await fetch('api/products.php?action=delete_unit', { method: 'POST', body: fd });
        loadInventoryData();
    }
}

async function deleteProduct(id) {
    const { isConfirmed } = await Swal.fire({
        title: 'Decommission SKU?',
        text: "This product will be removed from catalog!",
        icon: 'warning',
        showCancelButton: true,
        confirmButtonColor: '#d33',
        confirmButtonText: 'Yes, remove it'
    });

    if (isConfirmed) {
        const fd = new FormData();
        fd.append('id', id);
        const res = await fetch('api/products.php?action=delete', { method: 'POST', body: fd });
        const data = await res.json();
        if (data.success) {
            showToast(data.message);
            loadInventoryData();
        }
    }
}

window.viewBatches = async (id) => {
    const p = productsData.find(x => x.id == id);
    if (!p) return;

    document.getElementById('batch-product-info').innerHTML = `
        <div class="d-flex justify-content-between align-items-center">
            <div>
                <h6 class="fw-bold mb-0">${p.name}</h6>
                <div class="small text-muted">SKU: ${p.sku} | Total Stock: ${p.stock_quantity}</div>
            </div>
            <span class="badge bg-primary rounded-pill">Tracked by Batch</span>
        </div>
    `;

    const body = document.getElementById('batch-info-body');
    body.innerHTML = '<tr><td colspan="6" class="text-center py-4"><span class="spinner-border spinner-border-sm me-2"></span>Loading batches...</td></tr>';

    const modal = bootstrap.Modal.getOrCreateInstance(document.getElementById('batchInfoModal'));
    modal.show();

    try {
        const res = await fetch(`api/products.php?action=list_batches&product_id=${id}`);
        const data = await res.json();

        if (data.success) {
            body.innerHTML = '';
            if (data.data.length === 0) {
                body.innerHTML = '<tr><td colspan="6" class="text-center py-4 text-muted">No batch information found for this product.</td></tr>';
            } else {
                data.data.forEach(b => {
                    const expiryDate = new Date(b.expiry_date);
                    const today = new Date();
                    const diffDays = Math.ceil((expiryDate - today) / (1000 * 60 * 60 * 24));
                    
                    let statusBadge = '';
                    if (diffDays < 0) statusBadge = '<span class="badge bg-danger">EXPIRED</span>';
                    else if (diffDays <= 30) statusBadge = '<span class="badge bg-warning text-dark">EXPIRING SOON</span>';
                    else statusBadge = '<span class="badge bg-success">HEALTHY</span>';

                    body.innerHTML += `
                        <tr>
                            <td class="ps-3 fw-bold">${b.batch_number}</td>
                            <td>${b.expiry_date}</td>
                            <td class="text-end">${formatCurrency(b.cost_price)}</td>
                            <td class="text-end text-primary fw-bold">${formatCurrency(b.selling_price)}</td>
                            <td class="text-center fw-black">${b.stock_quantity}</td>
                            <td>${statusBadge}</td>
                        </tr>
                    `;
                });
            }
        } else {
            body.innerHTML = `<tr><td colspan="6" class="text-center py-4 text-danger">${data.message}</td></tr>`;
        }
    } catch (err) {
        body.innerHTML = '<tr><td colspan="6" class="text-center py-4 text-danger">Failed to load batches.</td></tr>';
    }
};



async function applyAdjustment(e) {
    e.preventDefault();
    const fd = new FormData(e.target);

    const btn = e.submitter || e.target.querySelector('button[type="submit"]');
    const originalBtnHtml = btn.innerHTML;
    btn.disabled = true;
    btn.innerHTML = '<span class="spinner-border spinner-border-sm"></span>';

    try {
        const res = await fetch('api/inventory.php?action=adjust_stock', { method: 'POST', body: fd });
        const data = await res.json();
        if (data.success) {
            bootstrap.Modal.getInstance(document.getElementById('adjustmentModal')).hide();
            e.target.reset();
            showToast(data.message);
            loadInventoryData();
            notifyInventoryChange();
        } else {
            Swal.fire('Error', data.message, 'error');
        }
    } catch (err) {
        Swal.fire('Error', 'Update failed', 'error');
    } finally {
        btn.disabled = false;
        btn.innerHTML = originalBtnHtml;
    }
}

function formatCurrency(num) {
    const code = appSettings.company_currency_code || 'USD';
    const formatted = new Intl.NumberFormat('en-US', { minimumFractionDigits: 0, maximumFractionDigits: 0 }).format(num);
    return `${code} ${formatted}`;
}

async function importProducts(e) {
    e.preventDefault();
    const btn = document.getElementById('btnImport');
    const resultDiv = document.getElementById('importResult');
    const fd = new FormData(e.target);

    btn.disabled = true;
    btn.innerHTML = '<span class="spinner-border spinner-border-sm me-1"></span> Processing...';

    try {
        const res = await fetch('api/products.php?action=import', { method: 'POST', body: fd });
        const data = await res.json();

        if (data.success) {
            resultDiv.classList.remove('d-none');
            document.getElementById('import-total').innerText = data.details.total;
            document.getElementById('import-success').innerText = data.details.success;
            document.getElementById('import-failed').innerText = data.details.failed;

            showToast(data.message);
            loadInventoryData();
            notifyInventoryChange();
        } else {
            Swal.fire('Error', data.message, 'error');
        }
    } catch (err) {
        Swal.fire('Error', 'Import failed: Server communication error', 'error');
    } finally {
        btn.disabled = false;
        btn.innerHTML = '<i class="fa-solid fa-cloud-arrow-up me-1"></i> Start Import';
    }
}

function renderInventoryReports() {
    const valuationBody = document.getElementById('report-valuation-body');
    const valuationFoot = document.getElementById('report-valuation-foot');
    const lowStockList = document.getElementById('report-low-stock-list');
    const noLowStockMsg = document.getElementById('no-low-stock-msg');

    if (!valuationBody) return;

    // 1. Calculate Valuation by Category
    const catAnalysis = {};
    let totalItems = 0;
    let totalQty = 0;
    let totalValue = 0;

    productsData.forEach(p => {
        const catName = p.category_name || 'Uncategorized';
        if (!catAnalysis[catName]) {
            catAnalysis[catName] = { count: 0, qty: 0, value: 0 };
        }
        catAnalysis[catName].count++;
        catAnalysis[catName].qty += parseFloat(p.stock_quantity || 0);
        catAnalysis[catName].value += (parseFloat(p.stock_quantity || 0) * parseFloat(p.cost_price || 0));
        
        totalItems++;
        totalQty += parseFloat(p.stock_quantity || 0);
        totalValue += (parseFloat(p.stock_quantity || 0) * parseFloat(p.cost_price || 0));
    });

    valuationBody.innerHTML = '';
    Object.keys(catAnalysis).sort().forEach(cat => {
        const data = catAnalysis[cat];
        valuationBody.innerHTML += `
            <tr onclick="showCategoryValuationDetails('${cat.replace(/'/g, "\\'")}')" style="cursor: pointer;">
                <td class="ps-4 fw-bold text-primary">${cat}</td>
                <td class="text-center text-muted">${data.count} SKUs</td>
                <td class="text-end fw-bold">${data.qty.toLocaleString()}</td>
                <td class="text-end pe-4 text-primary fw-black">${formatCurrency(data.value)}</td>
            </tr>
        `;
    });

    if (valuationFoot) {
        valuationFoot.innerHTML = `
            <td class="ps-4">GRAND TOTAL</td>
            <td class="text-center">${totalItems} SKUs</td>
            <td class="text-end">${totalQty.toLocaleString()}</td>
            <td class="text-end pe-4 text-primary">${formatCurrency(totalValue)}</td>
        `;
    }

    // 2. Critical Reorder List (Low Stock)
    const lowStockItems = productsData.filter(p => p.stock_quantity <= p.min_stock_level).sort((a,b) => a.stock_quantity - b.stock_quantity);
    
    if (lowStockList) {
        lowStockList.innerHTML = '';
        if (lowStockItems.length > 0) {
            if (noLowStockMsg) noLowStockMsg.classList.add('d-none');
            lowStockItems.slice(0, 15).forEach(p => {
                const urgency = p.stock_quantity <= 0 ? 'bg-danger' : 'bg-warning text-dark';
                lowStockList.innerHTML += `
                    <div class="list-group-item d-flex justify-content-between align-items-center py-3 border-0 border-bottom">
                        <div>
                            <div class="fw-bold text-dark">${p.name}</div>
                            <div class="small text-muted">SKU: ${p.sku} | Threshold: ${p.min_stock_level}</div>
                        </div>
                        <div class="text-end">
                            <span class="badge ${urgency} rounded-pill px-3">${p.stock_quantity} Left</span>
                            <div class="small mt-1"><a href="javascript:void(0)" onclick="adjustProductStock(${p.id})" class="text-decoration-none">Order More</a></div>
                        </div>
                    </div>
                `;
            });
        } else {
            if (noLowStockMsg) noLowStockMsg.classList.remove('d-none');
        }
    }
}

window.showCategoryValuationDetails = (catName) => {
    // Find the category object to get ID
    const sampleProduct = productsData.find(p => (p.category_name || 'Uncategorized') === catName);
    const catId = sampleProduct ? sampleProduct.category_id : '';
    document.getElementById('current_valuation_category_id').value = catId;

    const list = productsData.filter(p => (p.category_name || 'Uncategorized') === catName);
    const tbody = document.getElementById('valuation-detail-body');
    const title = document.getElementById('valuationDetailTitle');
    const totalSpan = document.getElementById('valuation-detail-total');
    
    if (!tbody || !title) return;

    title.innerText = `${catName} - Valuation Breakdown`;
    tbody.innerHTML = '';
    let totalValue = 0;

    list.sort((a,b) => (b.stock_quantity * b.cost_price) - (a.stock_quantity * a.cost_price)).forEach(p => {
        const value = p.stock_quantity * p.cost_price;
        totalValue += value;
        tbody.innerHTML += `
            <tr>
                <td class="ps-4">
                    <div class="fw-bold">${p.name}</div>
                    <div class="small text-muted">${p.unit_name || 'pcs'}</div>
                </td>
                <td class="text-center"><span class="badge bg-light text-dark border">${p.sku}</span></td>
                <td class="text-end">${formatCurrency(p.cost_price)}</td>
                <td class="text-end fw-bold">${p.stock_quantity}</td>
                <td class="text-end pe-4 fw-black text-primary">${formatCurrency(value)}</td>
            </tr>
        `;
    });

    if (totalSpan) totalSpan.innerText = formatCurrency(totalValue);
    bootstrap.Modal.getOrCreateInstance(document.getElementById('categoryValuationModal')).show();
};

window.generateReorderPDF = () => {
    const previewUrl = `api/export_inventory_pdf.php?type=reorder&t=${Date.now()}`;
    const downloadUrl = `${previewUrl}&download=1`;
    
    const iframe = document.getElementById('pdf-preview-iframe');
    const openBtn = document.getElementById('btnOpenNewTabLink');
    const dlLink = document.getElementById('btnDownloadPDFLink');
    
    if (iframe) iframe.src = previewUrl;
    if (openBtn) openBtn.href = previewUrl;
    if (dlLink) dlLink.href = downloadUrl;

    bootstrap.Modal.getOrCreateInstance(document.getElementById('pdfPreviewModal')).show();
};

document.getElementById('btnPrintCategoryValuation')?.addEventListener('click', () => {
    const catId = document.getElementById('current_valuation_category_id').value;
    const previewUrl = `api/export_inventory_pdf.php?type=valuation&category_id=${catId}&t=${Date.now()}`;
    const downloadUrl = `${previewUrl}&download=1`;
    
    const iframe = document.getElementById('pdf-preview-iframe');
    const openBtn = document.getElementById('btnOpenNewTabLink');
    const dlLink = document.getElementById('btnDownloadPDFLink');
    
    if (iframe) iframe.src = previewUrl;
    if (openBtn) openBtn.href = previewUrl;
    if (dlLink) dlLink.href = downloadUrl;

    bootstrap.Modal.getOrCreateInstance(document.getElementById('pdfPreviewModal')).show();
});
