<?php
/**
 * Enterprise HRM ERP System - Fortnight Stock Management
 * Excel-like Stock Management with Dynamic Table Creation
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$pageTitle = 'Fortnight Stock Management';
$currentModule = 'fortnight';

$siteId = getCurrentSiteId();
$userId = $_SESSION['user_id'];
$roleCode = strtolower($_SESSION['role_code'] ?? '');

// Get parameters
$selectedMonth = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$selectedYear = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');
$periodId = isset($_GET['period_id']) ? (int)$_GET['period_id'] : 0;

// Validate
if ($selectedMonth < 1 || $selectedMonth > 12) $selectedMonth = (int)date('m');
if ($selectedYear < 2020 || $selectedYear > 2100) $selectedYear = (int)date('Y');

$errors = [];
$success = false;
$period = null;
$inventoryItems = [];

// Handle save "To" name
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_to_name'])) {
    try {
        $db = getDB();
        $toName = sanitize($_POST['to_name'] ?? '');
        
        // Create table if not exists
        $db->exec("CREATE TABLE IF NOT EXISTS fortnight_to_names (
            id INT AUTO_INCREMENT PRIMARY KEY,
            site_id INT NOT NULL,
            to_name VARCHAR(255) DEFAULT '',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_site (site_id)
        )");
        
        // Insert or update
        $stmt = $db->prepare("INSERT INTO fortnight_to_names (site_id, to_name) VALUES (?, ?) 
                              ON DUPLICATE KEY UPDATE to_name = VALUES(to_name), updated_at = CURRENT_TIMESTAMP");
        $stmt->execute([$siteId, $toName]);
        
        echo json_encode(['success' => true, 'message' => 'Name saved successfully']);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        exit;
    }
}

// Load existing "To" name for this site
$toName = '';
try {
    $db = getDB();
    // Create table if not exists
    $db->exec("CREATE TABLE IF NOT EXISTS fortnight_to_names (
        id INT AUTO_INCREMENT PRIMARY KEY,
        site_id INT NOT NULL,
        to_name VARCHAR(255) DEFAULT '',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_site (site_id)
    )");
    
    $stmt = $db->prepare("SELECT to_name FROM fortnight_to_names WHERE site_id = ?");
    $stmt->execute([$siteId]);
    $toNameRow = $stmt->fetch();
    if ($toNameRow) {
        $toName = $toNameRow['to_name'];
    }
} catch (Exception $e) {
    // Table might not exist yet, ignore
}

// Handle save
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_fortnight'])) {
    try {
        $db = getDB();
        $db->beginTransaction();
        
        $fromDate = $_POST['from_date'] ?? '';
        $toDate = $_POST['to_date'] ?? '';
        $periodName = sanitize($_POST['period_name'] ?? '');
        $fortnightNo = sanitize($_POST['fortnight_no'] ?? '');
        $editPeriodId = isset($_POST['period_id']) ? (int)$_POST['period_id'] : 0;
        
        if (empty($fromDate) || empty($toDate)) {
            throw new Exception('From Date and To Date are required.');
        }
        
        $month = (int)date('m', strtotime($fromDate));
        $year = (int)date('Y', strtotime($fromDate));
        
        // Check if we're editing an existing period (period_id provided via hidden field)
        if ($editPeriodId > 0) {
            // Verify the period exists and belongs to this site
            $stmt = $db->prepare("SELECT period_id, is_locked FROM fortnight_periods WHERE period_id = ? AND site_id = ?");
            $stmt->execute([$editPeriodId, $siteId]);
            $existingPeriod = $stmt->fetch();
            
            if ($existingPeriod) {
                // Check if locked
                if ($existingPeriod['is_locked']) {
                    throw new Exception('This period is locked and cannot be modified.');
                }
                
                $periodId = $editPeriodId;
                
                // Update period details
                if (empty($periodName)) {
                    $periodName = 'Stock Entry - ' . date('d-M-Y', strtotime($fromDate)) . ' - ' . date('H:i:s');
                }
                $stmt = $db->prepare("UPDATE fortnight_periods SET period_name = ?, date = ?, period_date = ?, month = ?, year = ? WHERE period_id = ? AND site_id = ?");
                $stmt->execute([$periodName, $fromDate, $toDate, $month, $year, $periodId, $siteId]);
            } else {
                throw new Exception('Period not found or you do not have permission to edit it.');
            }
        } else {
            // Create new period
            if (empty($periodName)) {
                $periodName = 'Stock Entry - ' . date('d-M-Y', strtotime($fromDate)) . ' - ' . date('H:i:s');
            }
            
            $stmt = $db->prepare("
                INSERT INTO fortnight_periods (period_name, date, period_date, month, year, site_id, created_by)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ");
            $stmt->execute([$periodName, $fromDate, $toDate, $month, $year, $siteId, $userId]);
            $periodId = $db->lastInsertId();
        }
        
        // Delete existing inventory for this period
        $stmt = $db->prepare("DELETE FROM fortnight_inventory WHERE period_id = ?");
        $stmt->execute([$periodId]);
        
        // Insert inventory items - handle all row types (data, header, calculation)
        if (isset($_POST['inventory']) && is_array($_POST['inventory'])) {
            $displayOrder = 1;
            $srNo = 1;
            
            // CRITICAL: Sort inventory array by key to maintain DOM order
            $inventoryData = $_POST['inventory'];
            ksort($inventoryData, SORT_NUMERIC);
            
            foreach ($inventoryData as $rowIndex => $row) {
                $rowType = sanitize($row['row_type'] ?? 'data');
                
                // Handle HEADER rows
                if ($rowType === 'header') {
                    $headerText = sanitize($row['header_text'] ?? 'Material Description');
                    
                    $stmt = $db->prepare("
                        INSERT INTO fortnight_inventory (
                            period_id, row_type, display_order, header_text,
                            material_description, site_id, created_by
                        ) VALUES (?, ?, ?, ?, ?, ?, ?)
                    ");
                    $stmt->execute([
                        $periodId, 'header', $displayOrder++, $headerText,
                        '__HEADER__', $siteId, $userId
                    ]);
                    $savedCount++;
                }
                // Handle CALCULATION rows
                else if ($rowType === 'calculation') {
                    $headerText = sanitize($row['header_text'] ?? 'Total Amount');
                    $openingStockQty = (float)($row['opening_stock_qty'] ?? 0);
                    $openingStockValue = (float)($row['opening_stock_value'] ?? 0);
                    $purchaseQty = (float)($row['purchase_qty'] ?? 0);
                    $purchaseValue = (float)($row['purchase_value'] ?? 0);
                    $consumptionQty = (float)($row['consumption_qty'] ?? 0);
                    $consumptionValue = (float)($row['consumption_value'] ?? 0);
                    $closingStockQty = (float)($row['closing_stock_qty'] ?? 0);
                    $closingStockValue = (float)($row['closing_stock_value'] ?? 0);
                    
                    $stmt = $db->prepare("
                        INSERT INTO fortnight_inventory (
                            period_id, row_type, display_order, header_text, material_description,
                            opening_stock_qty, opening_stock_value, purchase_qty, purchase_value,
                            consumption_qty, consumption_value, closing_stock_qty, closing_stock_value,
                            site_id, created_by
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ");
                    $stmt->execute([
                        $periodId, 'calculation', $displayOrder++, $headerText, '__CALCULATION__',
                        $openingStockQty, $openingStockValue, $purchaseQty, $purchaseValue,
                        $consumptionQty, $consumptionValue, $closingStockQty, $closingStockValue,
                        $siteId, $userId
                    ]);
                    $savedCount++;
                }
                // Handle DATA rows (default)
                else {
                    // Skip empty data rows
                    if (empty($row['material_description'])) continue;
                    
                    $materialHsnCode = sanitize($row['material_hsn_code'] ?? '');
                    $materialDescription = sanitize($row['material_description'] ?? '');
                    $category = sanitize($row['category'] ?? '');
                    $netRate = (float)($row['net_rate'] ?? 0);
                    $openingStockQty = (float)($row['opening_stock_qty'] ?? 0);
                    $purchaseQty = (float)($row['purchase_qty'] ?? 0);
                    $consumptionQty = (float)($row['consumption_qty'] ?? 0);
                    $supplierName = sanitize($row['supplier_name'] ?? '');
                    $billChallanNo = sanitize($row['bill_challan_no'] ?? '');
                    
                    // Calculate values (Excel Formula equivalents)
                    $openingStockValue = round($openingStockQty * $netRate, 2);
                    $purchaseValue = round($purchaseQty * $netRate, 2);
                    $consumptionValue = round($consumptionQty * $netRate, 2);
                    $closingStockQty = max(0, round($openingStockQty + $purchaseQty - $consumptionQty, 3));
                    $closingStockValue = round($closingStockQty * $netRate, 2);
                    
                    $stmt = $db->prepare("
                        INSERT INTO fortnight_inventory (
                            period_id, row_type, display_order, sr_no, material_hsn_code, material_description, category,
                            net_rate, opening_stock_qty, opening_stock_value,
                            purchase_qty, purchase_value, consumption_qty, consumption_value,
                            closing_stock_qty, closing_stock_value, supplier_name, bill_challan_no,
                            site_id, created_by
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ");
                    $stmt->execute([
                        $periodId, 'data', $displayOrder++, $srNo++, $materialHsnCode, $materialDescription, $category,
                        $netRate, $openingStockQty, $openingStockValue,
                        $purchaseQty, $purchaseValue, $consumptionQty, $consumptionValue,
                        $closingStockQty, $closingStockValue, $supplierName, $billChallanNo,
                        $siteId, $userId
                    ]);
                    $savedCount++;
                }
            }
            
            // Log save count for debugging
            if ($savedCount > 0) {
                error_log("Fortnight saved: {$savedCount} items for period_id {$periodId}");
            } else {
                error_log("Fortnight save warning: No items saved for period_id {$periodId}. Check if inventory data is being submitted.");
            }
        } else {
            error_log("Fortnight save warning: No inventory data in POST for period_id {$periodId}");
        }
        
        $db->commit();
        $success = true;
        $_SESSION['success'] = 'Fortnight data saved successfully.';
        // Redirect with period_id, month, and year to maintain context
        header('Location: ' . $_SERVER['PHP_SELF'] . '?period_id=' . $periodId . '&month=' . $month . '&year=' . $year);
        exit;
        
    } catch (Exception $e) {
            $db->rollBack();
        $errors[] = $e->getMessage();
        error_log("Fortnight save error: " . $e->getMessage());
    }
}

// Load data
try {
    $db = getDB();
    
    // Get all periods for selected month/year
            $stmt = $db->prepare("
                SELECT * FROM fortnight_periods
                WHERE site_id = ? AND month = ? AND year = ?
        ORDER BY period_id DESC
    ");
        $stmt->execute([$siteId, $selectedMonth, $selectedYear]);
    $periods = $stmt->fetchAll();
    
    // Load period and inventory if period_id is set
    if ($periodId > 0) {
        $stmt = $db->prepare("SELECT * FROM fortnight_periods WHERE period_id = ? AND site_id = ?");
        $stmt->execute([$periodId, $siteId]);
        $period = $stmt->fetch();
        
        if ($period) {
            $stmt = $db->prepare("
                SELECT * FROM fortnight_inventory 
                WHERE period_id = ? 
                ORDER BY display_order ASC, inventory_id ASC
            ");
            $stmt->execute([$periodId]);
            $inventoryItems = $stmt->fetchAll();
        }
    }
    
    // Get item master for dropdowns
            $stmt = $db->prepare("
        SELECT DISTINCT material_hsn_code, material_name, category, rate 
        FROM item_master 
        WHERE (site_id = ? OR site_id = 0) AND is_active = 1
        ORDER BY material_name
    ");
    $stmt->execute([$siteId]);
    $itemMaster = $stmt->fetchAll();
    
    // Get supplier master for dropdowns
                $stmt = $db->prepare("
        SELECT supplier_name FROM supplier_master 
        WHERE (site_id = ? OR site_id = 0) AND is_active = 1
        ORDER BY supplier_name
    ");
    $stmt->execute([$siteId]);
    $suppliers = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
} catch (Exception $e) {
    error_log("Fortnight list error: " . $e->getMessage());
    $periods = [];
    $itemMaster = [];
    $suppliers = [];
}

// If no items, create empty array for new entry
if (empty($inventoryItems)) {
    $inventoryItems = [['sr_no' => 1]];
}

include __DIR__ . '/../../includes/header.php';

// Show success/error messages
if (isset($_SESSION['success'])) {
    echo '<div class="alert alert-success" style="background: #d4edda; color: #155724; padding: 12px; margin: 10px 0; border: 1px solid #c3e6cb; border-radius: 4px;">';
    echo htmlspecialchars($_SESSION['success']);
    echo '</div>';
    unset($_SESSION['success']);
}

if (!empty($errors)) {
    echo '<div class="alert alert-danger" style="background: #f8d7da; color: #721c24; padding: 12px; margin: 10px 0; border: 1px solid #f5c6cb; border-radius: 4px;">';
    foreach ($errors as $error) {
        echo htmlspecialchars($error) . '<br>';
    }
    echo '</div>';
}
?>

<style>
/* ========== FORTNIGHT STOCK - MODERN UI STYLES ========== */

/* Main Container */
.excel-container {
    background: linear-gradient(135deg, #f0f4f8 0%, #d9e2ec 100%);
    padding: 30px;
    font-family: 'Segoe UI', -apple-system, BlinkMacSystemFont, sans-serif;
    min-height: 100vh;
    border-radius: 16px;
}

/* Page Title */
.excel-container h2 {
    background: linear-gradient(135deg, #1e3c72 0%, #2a5298 100%);
    color: #fff;
    padding: 20px 25px;
    margin: -30px -30px 25px -30px;
    font-size: 22px;
    font-weight: 600;
    letter-spacing: 0.5px;
    display: flex;
    align-items: center;
    gap: 12px;
    box-shadow: 0 4px 15px rgba(30, 60, 114, 0.3);
    border-radius: 16px 16px 0 0;
}

.excel-container h2::before {
    content: '📦';
    font-size: 24px;
}

/* Period Selector Card */
.period-selector {
    background: linear-gradient(135deg, #ffffff 0%, #f8fafc 100%);
    padding: 20px 25px;
    margin-bottom: 25px;
    border: none;
    border-radius: 16px;
    box-shadow: 0 4px 20px rgba(0, 0, 0, 0.08);
    border-left: 5px solid #2a5298;
    display: flex;
    flex-wrap: wrap;
    align-items: center;
    gap: 20px;
}

.period-selector label {
    font-weight: 600;
    color: #64748b;
    font-size: 13px;
    text-transform: uppercase;
    letter-spacing: 0.5px;
}

.period-selector select, .period-selector input[type="number"] {
    padding: 10px 14px;
    border: 2px solid #e2e8f0;
    border-radius: 8px;
    font-size: 13px;
    background: #fff;
    transition: all 0.3s ease;
    min-width: 120px;
}

.period-selector select:focus, .period-selector input:focus {
    border-color: #2a5298;
    box-shadow: 0 0 0 3px rgba(42, 82, 152, 0.15);
    outline: none;
}

/* Excel Table - Modern Design */
.excel-table {
    width: 100%;
    border-collapse: separate;
    border-spacing: 0;
    font-size: 12px;
    background: #fff;
    font-family: 'Segoe UI', -apple-system, BlinkMacSystemFont, sans-serif;
    margin-bottom: 20px;
    border-radius: 12px;
    overflow: hidden;
    box-shadow: 0 8px 30px rgba(0, 0, 0, 0.12);
}

.excel-table thead {
    background: linear-gradient(135deg, #1e3c72 0%, #2a5298 100%);
    color: #fff;
}

.excel-table thead th {
    padding: 12px 8px;
    text-align: center !important;
    border: none;
    border-right: 1px solid rgba(255, 255, 255, 0.1);
    font-weight: 600;
    white-space: nowrap;
    font-size: 11px;
    width: auto;
    min-width: 100px;
    background: transparent !important;
    color: #fff !important;
    text-transform: uppercase;
    letter-spacing: 0.5px;
}

.excel-table thead th:last-child {
    border-right: none;
}

/* Ensure all header cells have uniform styling */
.excel-table thead th.col-rate,
.excel-table thead th.col-code,
.excel-table thead th.col-desc,
.excel-table thead th.col-qty,
.excel-table thead th.col-value,
.excel-table thead th.col-supplier,
.excel-table thead th.col-bill,
.excel-table thead th.col-sr {
    background: transparent !important;
    color: #fff !important;
}

.excel-table thead th.col-sr {
    min-width: 50px;
    width: 50px;
}

.excel-table thead th.col-code {
    min-width: 150px;
    width: 150px;
}

.excel-table thead th.col-desc {
    min-width: 300px;
    width: 300px;
}

.excel-table thead th.col-rate {
    min-width: 120px;
    width: 120px;
}

.excel-table thead th.col-qty {
    min-width: 120px;
    width: 120px;
}

.excel-table thead th.col-value {
    min-width: 140px;
    width: 140px;
}

.excel-table thead th.col-supplier {
    min-width: 180px;
    width: 180px;
}

.excel-table thead th.col-bill {
    min-width: 160px;
    width: 160px;
}

.excel-table thead th input[type="text"] {
    color: #fff;
    text-align: center;
    background: rgba(255, 255, 255, 0.1);
    border-radius: 6px;
    padding: 6px 8px;
}

.excel-table thead th input[type="text"]::placeholder {
    color: rgba(255, 255, 255, 0.7);
}

.excel-table thead th input[type="text"]:focus {
    background: rgba(255, 255, 255, 0.2);
    outline: 2px solid #60a5fa;
    outline-offset: -2px;
    border-radius: 6px;
}

.header-row-duplicate .header-desc-input {
    color: #fff !important;
    background: transparent !important;
}

.header-row-duplicate .header-desc-input:focus {
    background: rgba(255, 255, 255, 0.2) !important;
    outline: 2px solid #60a5fa;
    outline-offset: -2px;
    border-radius: 6px;
}

.excel-table tbody td {
    padding: 8px 6px;
    border-bottom: 1px solid #e2e8f0;
    border-right: 1px solid #f1f5f9;
    text-align: left;
    font-size: 12px;
    vertical-align: middle;
    transition: all 0.2s ease;
    height: auto;
}

.excel-table tbody td:last-child {
    border-right: none;
}

.excel-table tbody tr:nth-child(even) td {
    background: #f8fafc;
}

.excel-table tbody tr:nth-child(odd) td {
    background: #ffffff;
}

.excel-table tbody tr:hover td {
    background: linear-gradient(135deg, #dbeafe 0%, #bfdbfe 100%) !important;
}

.excel-table tbody tr:hover td.calculated {
    background: linear-gradient(135deg, #c7d2fe 0%, #a5b4fc 100%) !important;
}

/* Input Fields */
.excel-table tbody td input {
    width: 100%;
    min-width: 80px;
    border: 2px solid transparent;
    padding: 6px 8px;
    font-size: 12px;
    background: #f8fafc;
    font-family: 'Segoe UI', -apple-system, BlinkMacSystemFont, sans-serif;
    height: 32px;
    line-height: 20px;
    box-sizing: border-box;
    border-radius: 6px;
    transition: all 0.3s ease;
}

.excel-table tbody td input:hover {
    background: #f1f5f9;
    border-color: #cbd5e1;
}

.excel-table tbody td input:focus {
    background: #fffbeb;
    border-color: #2a5298;
    outline: none;
    box-shadow: 0 0 0 3px rgba(42, 82, 152, 0.15);
    transform: scale(1.02);
}

.excel-table tbody td input[type="number"]:invalid {
    background: #fee2e2;
    border-color: #fca5a5;
}

.excel-table .col-sr { width: 50px; text-align: center; }
.excel-table .col-code { width: 150px; }
.excel-table .col-desc { width: 300px; min-width: 300px; }
.excel-table .col-desc input[type="text"] {
    width: 100%;
    min-width: 250px;
    border: 2px solid transparent;
    padding: 6px 8px;
    font-size: 12px;
    font-family: 'Segoe UI', -apple-system, BlinkMacSystemFont, sans-serif;
    background: #f8fafc;
    height: 32px;
    line-height: 20px;
    box-sizing: border-box;
    border-radius: 6px;
}
.excel-table .col-desc input[type="text"]:focus {
    background: #fffbeb;
    border-color: #2a5298;
    outline: none;
    box-shadow: 0 0 0 3px rgba(42, 82, 152, 0.15);
}
.excel-table .col-rate { width: 120px; text-align: right; }
.excel-table .col-qty { width: 120px; text-align: right; }
.excel-table .col-value { width: 140px; text-align: right; }
.excel-table .col-supplier { width: 180px; }
.excel-table .col-bill { width: 160px; }

/* Input field backgrounds - only for data rows, not headers */
.input-yellow { background: #fef3c7 !important; border-color: #fbbf24 !important; }
.input-blue { background: #dbeafe !important; border-color: #60a5fa !important; }
.input-green { background: #dcfce7 !important; border-color: #86efac !important; }

/* Calculated Cells */
.calculated {
    background: linear-gradient(135deg, #dcfce7 0%, #bbf7d0 100%) !important;
    font-weight: 600;
    text-align: right !important;
    user-select: none;
    cursor: default;
    color: #166534;
}

.calculated:hover {
    background: linear-gradient(135deg, #c7d2fe 0%, #a5b4fc 100%) !important;
}

/* Header Row Duplicate */
.header-row-duplicate {
    background: linear-gradient(135deg, #1e3c72 0%, #2a5298 100%) !important;
    color: #fff !important;
}

.header-row-duplicate td {
    background: linear-gradient(135deg, #1e3c72 0%, #2a5298 100%) !important;
    color: #fff !important;
    font-weight: 600;
    text-align: center;
    padding: 12px 8px !important;
    border: none !important;
    border-right: 1px solid rgba(255, 255, 255, 0.1) !important;
    height: auto;
    text-transform: uppercase;
    letter-spacing: 0.5px;
    font-size: 11px;
}

/* Calculation Row */
.calculation-row {
    background: linear-gradient(135deg, #fbbf24 0%, #f59e0b 100%) !important;
}

.calculation-row td {
    background: linear-gradient(135deg, #fbbf24 0%, #f59e0b 100%) !important;
    color: #1e293b !important;
    font-weight: 700;
    border: none !important;
    border-right: 1px solid rgba(0, 0, 0, 0.1) !important;
    padding: 12px 8px !important;
    height: auto;
    font-size: 13px;
}

/* Action Buttons - Modern Design */
.btn-action {
    padding: 12px 24px;
    margin: 6px;
    border: none;
    border-radius: 10px;
    cursor: pointer;
    font-size: 14px;
    font-weight: 600;
    transition: all 0.3s ease;
    box-shadow: 0 4px 15px rgba(0, 0, 0, 0.1);
    text-transform: uppercase;
    letter-spacing: 0.5px;
    display: inline-flex;
    align-items: center;
    gap: 8px;
}

.btn-action:hover {
    transform: translateY(-3px);
    box-shadow: 0 8px 25px rgba(0, 0, 0, 0.2);
}

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

.btn-primary { 
    background: linear-gradient(135deg, #3b82f6 0%, #2563eb 100%); 
    color: #fff; 
}
.btn-primary:hover {
    box-shadow: 0 8px 25px rgba(59, 130, 246, 0.4);
}

.btn-success { 
    background: linear-gradient(135deg, #10b981 0%, #059669 100%); 
    color: #fff; 
}
.btn-success:hover {
    box-shadow: 0 8px 25px rgba(16, 185, 129, 0.4);
}

.btn-info { 
    background: linear-gradient(135deg, #8b5cf6 0%, #6d28d9 100%); 
    color: #fff; 
}
.btn-info:hover {
    box-shadow: 0 8px 25px rgba(139, 92, 246, 0.4);
}
.btn-danger { background: #dc3545; color: #fff; }

.btn-action:hover { opacity: 0.9; }

.action-buttons {
    margin: 20px 0;
    text-align: center;
}

.period-form {
    background: #f9f9f9;
    padding: 15px;
    margin-bottom: 20px;
    border: 1px solid #ddd;
    border-radius: 4px;
}

.period-form .form-group {
    margin: 10px 0;
    display: inline-block;
    margin-right: 15px;
}

.period-form label {
    display: block;
    margin-bottom: 5px;
    font-weight: bold;
}

.period-form input, .period-form select {
    padding: 5px;
    border: 1px solid #ddd;
    border-radius: 3px;
}
</style>

<div class="excel-container">
    <h2>Fortnight Stock Management</h2>
    
    <!-- Period Selector -->
    <div class="period-selector">
        <label>Select Month:</label>
        <select id="filter_month" onchange="filterByMonthYear()">
                <?php for ($m = 1; $m <= 12; $m++): ?>
            <option value="<?php echo $m; ?>" <?php echo $m == $selectedMonth ? 'selected' : ''; ?>>
                    <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                </option>
                <?php endfor; ?>
            </select>
        
        <label>Year:</label>
        <input type="number" id="filter_year" value="<?php echo $selectedYear; ?>" 
               min="2020" max="2100" onchange="filterByMonthYear()" style="width: 80px;">
        
        <button onclick="window.location.href='?month=<?php echo date('m'); ?>&year=<?php echo date('Y'); ?>'" 
                class="btn-action btn-info">Current Month</button>
        
        <a href="<?php echo BASE_URL; ?>/modules/fortnight/report.php" class="btn-action btn-info">View Report</a>
        
        <!-- To Name Field -->
        <div style="display: flex; align-items: center; gap: 8px; margin-left: 15px;">
            <label style="font-weight: 600; font-size: 13px; color: #64748b; text-transform: uppercase; letter-spacing: 0.5px;">📝 To</label>
            <div style="position: relative; display: inline-flex; align-items: center;">
                <input type="text" id="to_name_input" value="<?php echo htmlspecialchars($toName); ?>" 
                       placeholder="Enter name..." 
                       style="width: 140px; padding: 10px 35px 10px 14px; border: 2px solid #e2e8f0; border-radius: 8px; font-size: 13px; transition: all 0.3s ease;">
                <button type="button" onclick="saveToName()" title="Save Name" 
                        style="position: absolute; right: 5px; background: linear-gradient(135deg, #10b981 0%, #059669 100%); color: white; border: none; border-radius: 6px; padding: 5px 7px; cursor: pointer; font-size: 11px; line-height: 1; transition: all 0.3s; box-shadow: 0 2px 6px rgba(16, 185, 129, 0.3);">
                    ✓
                </button>
            </div>
        </div>
        </div>
        
    <!-- Period List - Enhanced -->
    <?php if (!empty($periods)): ?>
    <div style="margin-bottom: 25px; padding: 20px; background: linear-gradient(135deg, #ffffff 0%, #f8fafc 100%); border-radius: 16px; box-shadow: 0 4px 20px rgba(0, 0, 0, 0.08); border-left: 5px solid #8b5cf6;">
        <div style="display: flex; align-items: center; gap: 10px; margin-bottom: 15px;">
            <span style="font-size: 20px;">📋</span>
            <strong style="color: #1e293b; font-size: 16px; font-weight: 600;">Existing Periods</strong>
            <span style="background: linear-gradient(135deg, #8b5cf6 0%, #6d28d9 100%); color: white; padding: 4px 12px; border-radius: 20px; font-size: 12px; font-weight: 600;"><?php echo count($periods); ?></span>
        </div>
        <div style="display: flex; flex-wrap: wrap; gap: 10px;">
        <?php foreach ($periods as $p): ?>
        <a href="?period_id=<?php echo $p['period_id']; ?>&month=<?php echo $selectedMonth; ?>&year=<?php echo $selectedYear; ?>" 
           style="margin: 0; padding: 10px 20px; font-size: 13px; background: linear-gradient(135deg, #8b5cf6 0%, #6d28d9 100%); color: white; text-decoration: none; border-radius: 10px; font-weight: 500; transition: all 0.3s ease; box-shadow: 0 3px 10px rgba(139, 92, 246, 0.3); display: inline-flex; align-items: center; gap: 8px;">
            <span>📄</span>
            <?php echo htmlspecialchars($p['period_name'] ?: date('d M', strtotime($p['date'])) . ' - ' . date('d M', strtotime($p['period_date']))); ?>
        </a>
        <?php endforeach; ?>
        </div>
    </div>
    <?php endif; ?>
    
    <!-- Period Form -->
    <form method="POST" id="fortnightForm">
        <input type="hidden" name="save_fortnight" value="1">
        <?php if ($period && $periodId > 0): ?>
        <input type="hidden" name="period_id" value="<?php echo $periodId; ?>">
        <?php endif; ?>
        
        <div class="period-form">
            <div class="form-group">
                <label>From Date:</label>
                <input type="date" name="from_date" id="from_date" 
                       value="<?php echo $period ? htmlspecialchars($period['date']) : ''; ?>" required>
    </div>
            
            <div class="form-group">
                <label>To Date:</label>
                <input type="date" name="to_date" id="to_date" 
                       value="<?php echo $period ? htmlspecialchars($period['period_date']) : ''; ?>" required>
    </div>
            
            <div class="form-group">
                <label>Fortnight No / Period Name:</label>
                <input type="text" name="period_name" 
                       value="<?php echo $period ? htmlspecialchars($period['period_name']) : ''; ?>" 
                       placeholder="e.g., Fortnight 1, Jan 2026">
        </div>
        
            </div>
            
        <!-- Excel-like Table -->
        <div style="overflow-x: auto;">
            <table class="excel-table" id="inventoryTable">
                    <thead>
                        <tr>
                        <th class="col-sr" style="text-align: center !important;">SR NO.</th>
                        <th class="col-code" style="text-align: center !important;">Material HSN Code No</th>
                        <th class="col-desc" style="text-align: center !important;">
                            <div style="display: flex; align-items: center; gap: 5px; justify-content: center;">
                                <input type="text" 
                                       id="header_material_desc" 
                                       value="Material Description"
                                       style="width: 100%; border: none; padding: 4px; font-size: 11px; font-weight: bold; background: transparent; color: #fff; text-align: center;"
                                       placeholder="Material Description">
                                <button type="button" 
                                        onclick="saveHeaderMaterialDesc()" 
                                        title="Save Header Text"
                                        style="background: #28a745; color: #fff; border: none; padding: 4px 6px; border-radius: 3px; cursor: pointer; font-size: 10px; min-width: 24px; height: 24px; display: flex; align-items: center; justify-content: center;">
                                    <span style="font-size: 12px;">✓</span>
                                </button>
                            </div>
                            <span id="header-save-indicator" style="display: none; color: #90ee90; font-size: 9px; margin-top: 2px;">Saved</span>
                        </th>
                        <th class="col-rate" style="text-align: center !important;">Net Rate</th>
                        <th class="col-qty" style="text-align: center !important;">Opening Stock Qty</th>
                        <th class="col-value" style="text-align: center !important;">Opening Stock Value</th>
                        <th class="col-qty" style="text-align: center !important;">Purchase</th>
                        <th class="col-value" style="text-align: center !important;">Purchase Value</th>
                        <th class="col-qty" style="text-align: center !important;">Consumption</th>
                        <th class="col-value" style="text-align: center !important;">Consumption Value</th>
                        <th class="col-qty" style="text-align: center !important;">Closing Stock Qty</th>
                        <th class="col-value" style="text-align: center !important;">Closing Stock value</th>
                        <th class="col-supplier" style="text-align: center !important;">Supplier Name</th>
                        <th class="col-bill" style="text-align: center !important;">Bill/ Challan No</th>
                        <th style="width: 50px; text-align: center !important;">Action</th>
                        </tr>
                    </thead>
                <tbody id="inventoryBody">
                    <?php $rowIndex = 0; ?>
                    <?php foreach ($inventoryItems as $item): 
                        $rowType = $item['row_type'] ?? 'data';
                        
                        // HEADER ROW
                        if ($rowType === 'header'):
                    ?>
                    <tr class="header-row-duplicate" data-row-index="<?php echo $rowIndex; ?>">
                        <td class="col-sr" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">SR NO.
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][row_type]" value="header">
                        </td>
                        <td class="col-code" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Material HSN Code No</td>
                        <td class="col-desc" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">
                            <div style="display: flex; align-items: center; gap: 3px;">
                                <input type="text" 
                                       name="inventory[<?php echo $rowIndex; ?>][header_text]"
                                       class="header-desc-input" 
                                       value="<?php echo htmlspecialchars($item['header_text'] ?? 'Material Description'); ?>"
                                       style="width: 100%; border: none; padding: 2px; font-size: 11px; font-weight: bold; background: transparent; color: #fff; text-align: center;"
                                       placeholder="Material Description">
                                <button type="button" 
                                        onclick="saveHeaderDesc(this)" 
                                        title="Save"
                                        style="background: #28a745; color: #fff; border: none; padding: 2px 4px; border-radius: 2px; cursor: pointer; font-size: 9px; min-width: 20px; height: 20px;">
                                    ✓
                                </button>
                            </div>
                        </td>
                        <td class="col-rate" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Net Rate</td>
                        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Opening Stock Qty</td>
                        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Opening Stock Value</td>
                        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Purchase</td>
                        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Purchase Value</td>
                        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Consumption</td>
                        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Consumption Value</td>
                        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Closing Stock Qty</td>
                        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Closing Stock value</td>
                        <td class="col-supplier" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Supplier Name</td>
                        <td class="col-bill" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Bill/ Challan No</td>
                        <td style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">
                            <button type="button" class="btn-action btn-success" onclick="insertCalculationAfter(this)" title="Add Calculation Row" style="padding: 2px 5px; font-size: 10px; background: #ffa500; color: #000; margin-right: 2px;">∑</button>
                            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Header" style="padding: 2px 5px; font-size: 12px; background: #dc3545;">×</button>
                        </td>
                    </tr>
                    <?php 
                        // CALCULATION ROW
                        elseif ($rowType === 'calculation'):
                    ?>
                    <tr class="calculation-row" data-row-index="<?php echo $rowIndex; ?>">
                        <td colspan="3" style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;">
                            <strong><?php echo htmlspecialchars($item['header_text'] ?? 'Total Amount'); ?></strong>
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][row_type]" value="calculation">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][header_text]" value="<?php echo htmlspecialchars($item['header_text'] ?? 'Total Amount'); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][opening_stock_qty]" class="calc-opening-qty" value="<?php echo number_format($item['opening_stock_qty'] ?? 0, 3, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][opening_stock_value]" class="calc-opening-value" value="<?php echo number_format($item['opening_stock_value'] ?? 0, 2, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][purchase_qty]" class="calc-purchase-qty" value="<?php echo number_format($item['purchase_qty'] ?? 0, 3, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][purchase_value]" class="calc-purchase-value" value="<?php echo number_format($item['purchase_value'] ?? 0, 2, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][consumption_qty]" class="calc-consumption-qty" value="<?php echo number_format($item['consumption_qty'] ?? 0, 3, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][consumption_value]" class="calc-consumption-value" value="<?php echo number_format($item['consumption_value'] ?? 0, 2, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][closing_stock_qty]" class="calc-closing-qty" value="<?php echo number_format($item['closing_stock_qty'] ?? 0, 3, '.', ''); ?>">
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][closing_stock_value]" class="calc-closing-value" value="<?php echo number_format($item['closing_stock_value'] ?? 0, 2, '.', ''); ?>">
                        </td>
                        <td style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;"></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['opening_stock_qty'] ?? 0, 3); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['opening_stock_value'] ?? 0, 2); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['purchase_qty'] ?? 0, 3); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['purchase_value'] ?? 0, 2); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['consumption_qty'] ?? 0, 3); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['consumption_value'] ?? 0, 2); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['closing_stock_qty'] ?? 0, 3); ?></td>
                        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"><?php echo number_format($item['closing_stock_value'] ?? 0, 2); ?></td>
                        <td colspan="2" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"></td>
                        <td style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;">
                            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Calculation" style="padding: 2px 5px; font-size: 12px; background: #dc3545;">×</button>
                        </td>
                    </tr>
                    <?php 
                        // DATA ROW (default)
                        else:
                    ?>
                    <tr data-row-index="<?php echo $rowIndex; ?>">
                        <td class="col-sr">
                            <span class="sr-no"><?php echo $item['sr_no'] ?? ($rowIndex + 1); ?></span>
                            <input type="hidden" name="inventory[<?php echo $rowIndex; ?>][row_type]" value="data">
                        </td>
                        <td class="col-code">
                            <input type="text" name="inventory[<?php echo $rowIndex; ?>][material_hsn_code]" 
                                   value="<?php echo htmlspecialchars($item['material_hsn_code'] ?? ''); ?>" 
                                   class="material-code" list="hsnCodes">
                        </td>
                        <td class="col-desc">
                            <input type="text" 
                                   name="inventory[<?php echo $rowIndex; ?>][material_description]" 
                                   value="<?php echo htmlspecialchars($item['material_description'] ?? ''); ?>" 
                                   class="material-desc" 
                                   list="materialNames"
                                   onblur="autoSaveMaterialDesc(this, <?php echo $rowIndex; ?>)" 
                                   style="width: 100%; min-width: 200px; border: none; padding: 4px 6px; font-size: 11px; font-family: Arial, sans-serif; height: 28px; line-height: 20px; box-sizing: border-box;"
                                   required>
                            <span class="save-indicator" id="save-indicator-<?php echo $rowIndex; ?>" style="display: none; color: green; font-size: 9px;">✓ Saved</span>
                        </td>
                        <td class="col-rate">
                            <input type="number" step="0.01" name="inventory[<?php echo $rowIndex; ?>][net_rate]" 
                                   value="<?php echo number_format($item['net_rate'] ?? 0, 2, '.', ''); ?>" 
                                   class="text-right net-rate input-green" 
                                   onchange="calculateRow(<?php echo $rowIndex; ?>)" 
                                   oninput="calculateRow(<?php echo $rowIndex; ?>)">
                        </td>
                        <td class="col-qty">
                            <input type="number" step="0.001" name="inventory[<?php echo $rowIndex; ?>][opening_stock_qty]" 
                                   value="<?php echo number_format($item['opening_stock_qty'] ?? 0, 3, '.', ''); ?>" 
                                   class="text-right opening-qty input-yellow" 
                                   onchange="calculateRow(<?php echo $rowIndex; ?>)" 
                                   oninput="calculateRow(<?php echo $rowIndex; ?>)">
                        </td>
                        <td class="col-value calculated opening-value">
                            <?php echo number_format($item['opening_stock_value'] ?? 0, 2); ?>
                        </td>
                        <td class="col-qty">
                            <input type="number" step="0.001" name="inventory[<?php echo $rowIndex; ?>][purchase_qty]" 
                                   value="<?php echo number_format($item['purchase_qty'] ?? 0, 3, '.', ''); ?>" 
                                   class="text-right purchase-qty input-blue" 
                                   onchange="calculateRow(<?php echo $rowIndex; ?>)" 
                                   oninput="calculateRow(<?php echo $rowIndex; ?>)">
                        </td>
                        <td class="col-value calculated purchase-value">
                            <?php echo number_format($item['purchase_value'] ?? 0, 2); ?>
                        </td>
                        <td class="col-qty">
                            <input type="number" step="0.001" name="inventory[<?php echo $rowIndex; ?>][consumption_qty]" 
                                   value="<?php echo number_format($item['consumption_qty'] ?? 0, 3, '.', ''); ?>" 
                                   class="text-right consumption-qty input-green" 
                                   onchange="calculateRow(<?php echo $rowIndex; ?>)" 
                                   oninput="calculateRow(<?php echo $rowIndex; ?>)">
                        </td>
                        <td class="col-value calculated consumption-value">
                            <?php echo number_format($item['consumption_value'] ?? 0, 2); ?>
                        </td>
                        <td class="col-qty calculated closing-qty">
                            <?php echo number_format($item['closing_stock_qty'] ?? 0, 3); ?>
                        </td>
                        <td class="col-value calculated closing-value">
                            <?php echo number_format($item['closing_stock_value'] ?? 0, 2); ?>
                        </td>
                        <td class="col-supplier">
                            <input type="text" name="inventory[<?php echo $rowIndex; ?>][supplier_name]" 
                                   value="<?php echo htmlspecialchars($item['supplier_name'] ?? ''); ?>"
                                   class="supplier-name" list="suppliers">
                        </td>
                        <td class="col-bill">
                            <input type="text" name="inventory[<?php echo $rowIndex; ?>][bill_challan_no]" 
                                   value="<?php echo htmlspecialchars($item['bill_challan_no'] ?? ''); ?>">
                        </td>
                        <td>
                            <button type="button" class="btn-action btn-success" onclick="insertRowAfter(this)" title="Insert Row After" style="padding: 2px 5px; font-size: 12px; margin: 1px;">+</button>
                            <button type="button" class="btn-action btn-info" onclick="insertHeaderAfter(this)" title="Insert Header After" style="padding: 2px 5px; font-size: 12px; background: #ffa500; color: #000; margin: 1px;">H</button>
                            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Row" style="padding: 2px 5px; font-size: 12px; margin: 1px;">×</button>
                        </td>
                    </tr>
                    <?php endif; ?>
                    <?php $rowIndex++; ?>
                    <?php endforeach; ?>
                    </tbody>
                </table>
        </div>
        
        <!-- Data Lists for Dropdowns -->
        <datalist id="hsnCodes">
            <?php foreach ($itemMaster as $item): ?>
            <option value="<?php echo htmlspecialchars($item['material_hsn_code'] ?? ''); ?>">
        <?php endforeach; ?>
        </datalist>
        
        <datalist id="materialNames">
            <?php foreach ($itemMaster as $item): ?>
            <option value="<?php echo htmlspecialchars($item['material_name'] ?? ''); ?>" 
                    data-code="<?php echo htmlspecialchars($item['material_hsn_code'] ?? ''); ?>"
                    data-rate="<?php echo $item['rate']; ?>">
            <?php endforeach; ?>
        </datalist>
        
        <datalist id="suppliers">
            <?php foreach ($suppliers as $supplier): ?>
            <option value="<?php echo htmlspecialchars($supplier); ?>">
            <?php endforeach; ?>
        </datalist>
        
        <!-- Action Buttons -->
        <div class="action-buttons">
            <button type="button" class="btn-action btn-success" onclick="addRow()">+ Add Row</button>
            <button type="button" class="btn-action btn-info" onclick="addHeaderRow()" style="background: #ffa500; color: #000;">+ Add Header</button>
            <button type="button" class="btn-action btn-info" onclick="addCalculationRowAtEnd()" style="background: #ffa500; color: #000;">+ Add Calculation</button>
            <button type="submit" class="btn-action btn-primary">Save Data</button>
            <button type="button" class="btn-action btn-info" onclick="exportToExcel()">Export to Excel</button>
        </div>
    </form>
</div>

<script>
let rowCount = <?php echo $rowIndex; ?>;

function addRow() {
    const tbody = document.getElementById('inventoryBody');
    const newRow = document.createElement('tr');
    newRow.setAttribute('data-row-index', rowCount);
    newRow.innerHTML = `
        <td class="col-sr">
            <span class="sr-no">${rowCount + 1}</span>
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="data">
        </td>
        <td class="col-code">
            <input type="text" name="inventory[${rowCount}][material_hsn_code]" 
                   class="material-code" list="hsnCodes">
        </td>
        <td class="col-desc">
            <input type="text" 
                   name="inventory[${rowCount}][material_description]" 
                   class="material-desc" 
                   list="materialNames"
                   onblur="autoSaveMaterialDesc(this, ${rowCount})" 
                   style="width: 100%; border: none; padding: 1px 3px; font-size: 11px; font-family: Arial, sans-serif; height: 22px; line-height: 20px;"
                   required>
            <span class="save-indicator" id="save-indicator-${rowCount}" style="display: none; color: green; font-size: 9px;">✓ Saved</span>
        </td>
        <td class="col-rate">
            <input type="number" step="0.01" name="inventory[${rowCount}][net_rate]" 
                   value="0.00" class="text-right net-rate input-green" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][opening_stock_qty]" 
                   value="0.000" class="text-right opening-qty input-yellow" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated opening-value">0.00</td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][purchase_qty]" 
                   value="0.000" class="text-right purchase-qty input-blue" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated purchase-value">0.00</td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][consumption_qty]" 
                   value="0.000" class="text-right consumption-qty input-green" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated consumption-value">0.00</td>
        <td class="col-qty calculated closing-qty">0.000</td>
        <td class="col-value calculated closing-value">0.00</td>
        <td class="col-supplier">
            <input type="text" name="inventory[${rowCount}][supplier_name]" 
                   class="supplier-name" list="suppliers">
        </td>
        <td class="col-bill">
            <input type="text" name="inventory[${rowCount}][bill_challan_no]">
        </td>
        <td>
            <button type="button" class="btn-action btn-success" onclick="insertRowAfter(this)" title="Insert Row After" style="padding: 2px 5px; font-size: 12px;">+</button>
            <button type="button" class="btn-action btn-info" onclick="insertHeaderAfter(this)" title="Insert Header After" style="padding: 2px 5px; font-size: 12px; background: #ffa500; color: #000;">H</button>
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Row" style="padding: 2px 5px; font-size: 12px;">×</button>
        </td>
    `;
    tbody.appendChild(newRow);
    
    // Auto-fill rate when material is typed (for input with datalist)
    const materialInput = newRow.querySelector('.material-desc');
    if (materialInput) {
        materialInput.addEventListener('input', function() {
            const value = this.value.trim();
            if (value) {
                const option = document.querySelector(`#materialNames option[value="${value}"]`);
                if (option) {
                    const row = this.closest('tr');
                    if (row) {
                        if (row.querySelector('.material-code')) {
                            row.querySelector('.material-code').value = option.getAttribute('data-code') || '';
                        }
                        if (row.querySelector('.net-rate')) {
                            row.querySelector('.net-rate').value = option.getAttribute('data-rate') || '0.00';
                            const rowIndex = parseInt(row.getAttribute('data-row-index'));
                            if (!isNaN(rowIndex)) {
                                calculateRow(rowIndex);
                            }
                        }
                    }
                }
            }
        });
    }
    
    rowCount++;
    updateSrNos();
}

function insertHeaderAfter(btn) {
    const currentRow = btn.closest('tr');
    const tbody = document.getElementById('inventoryBody');
    
    // Get saved header text
    const savedHeaderText = localStorage.getItem('fortnight_header_material_desc') || 'Material Description';
    
    // Create header row (duplicate of table header)
    const headerRow = document.createElement('tr');
    headerRow.className = 'header-row-duplicate';
    headerRow.setAttribute('data-row-index', rowCount);
    headerRow.innerHTML = `
        <td class="col-sr" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">SR NO.
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="header">
        </td>
        <td class="col-code" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Material HSN Code No</td>
        <td class="col-desc" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">
            <div style="display: flex; align-items: center; gap: 3px;">
                <input type="text" 
                       name="inventory[${rowCount}][header_text]"
                       class="header-desc-input" 
                       value="${savedHeaderText}"
                       style="width: 100%; border: none; padding: 2px; font-size: 11px; font-weight: bold; background: transparent; color: #fff; text-align: center;"
                       placeholder="Material Description">
                <button type="button" 
                        onclick="saveHeaderDesc(this)" 
                        title="Save"
                        style="background: #28a745; color: #fff; border: none; padding: 2px 4px; border-radius: 2px; cursor: pointer; font-size: 9px; min-width: 20px; height: 20px;">
                    ✓
                </button>
    </div>
        </td>
        <td class="col-rate" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Net Rate</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Opening Stock Qty</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Opening Stock Value</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Purchase</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Purchase Value</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Consumption</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Consumption Value</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Closing Stock Qty</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Closing Stock value</td>
        <td class="col-supplier" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Supplier Name</td>
        <td class="col-bill" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Bill/ Challan No</td>
        <td style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">
            <button type="button" class="btn-action btn-success" onclick="insertRowAfterHeader(this)" title="Add Data Row After" style="padding: 2px 5px; font-size: 12px; background: #28a745; margin-right: 2px;">+</button>
            <button type="button" class="btn-action btn-success" onclick="insertCalculationAfter(this)" title="Add Calculation Row" style="padding: 2px 5px; font-size: 10px; background: #ffa500; color: #000; margin-right: 2px;">∑</button>
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Header" style="padding: 2px 5px; font-size: 12px; background: #dc3545;">×</button>
        </td>
    `;
    
    // Insert after current row
    currentRow.insertAdjacentElement('afterend', headerRow);
    rowCount++;
    updateSrNos();
}

function insertCalculationAfter(btn) {
    const headerRow = btn.closest('tr');
    const tbody = document.getElementById('inventoryBody');
    const allRows = Array.from(tbody.querySelectorAll('tr'));
    const headerIndex = allRows.indexOf(headerRow);
    
    // Find previous header or start
    let startIndex = 0;
    for (let i = headerIndex - 1; i >= 0; i--) {
        if (allRows[i].classList.contains('header-row-duplicate') || allRows[i].classList.contains('calculation-row')) {
            startIndex = i + 1;
            break;
        }
    }
    
    // Get all data rows between previous header and current header
    const dataRows = [];
    for (let i = startIndex; i < headerIndex; i++) {
        const row = allRows[i];
        if (!row.classList.contains('header-row-duplicate') && !row.classList.contains('calculation-row')) {
            dataRows.push(row);
        }
    }
    
    // Calculate totals
    let totalOpeningQty = 0, totalOpeningValue = 0;
    let totalPurchaseQty = 0, totalPurchaseValue = 0;
    let totalConsumptionQty = 0, totalConsumptionValue = 0;
    let totalClosingQty = 0, totalClosingValue = 0;
    
    // Excel Formula: SUM equivalent - Sum all values in range
    // Formula: =SUM(Range)
    dataRows.forEach(row => {
        // Parse with IFERROR equivalent (handle empty/null as 0)
        const openingQty = parseFloat(row.querySelector('.opening-qty')?.value || 0) || 0;
        const openingValueText = row.querySelector('.opening-value')?.textContent || '0';
        const openingValue = parseFloat(openingValueText.replace(/[^\d.-]/g, '')) || 0;
        
        const purchaseQty = parseFloat(row.querySelector('.purchase-qty')?.value || 0) || 0;
        const purchaseValueText = row.querySelector('.purchase-value')?.textContent || '0';
        const purchaseValue = parseFloat(purchaseValueText.replace(/[^\d.-]/g, '')) || 0;
        
        const consumptionQty = parseFloat(row.querySelector('.consumption-qty')?.value || 0) || 0;
        const consumptionValueText = row.querySelector('.consumption-value')?.textContent || '0';
        const consumptionValue = parseFloat(consumptionValueText.replace(/[^\d.-]/g, '')) || 0;
        
        const closingQtyText = row.querySelector('.closing-qty')?.textContent || '0';
        const closingQty = parseFloat(closingQtyText.replace(/[^\d.-]/g, '')) || 0;
        const closingValueText = row.querySelector('.closing-value')?.textContent || '0';
        const closingValue = parseFloat(closingValueText.replace(/[^\d.-]/g, '')) || 0;
        
        // Excel SUM formula equivalent
        totalOpeningQty += openingQty;
        totalOpeningValue += openingValue;
        totalPurchaseQty += purchaseQty;
        totalPurchaseValue += purchaseValue;
        totalConsumptionQty += consumptionQty;
        totalConsumptionValue += consumptionValue;
        totalClosingQty += closingQty;
        totalClosingValue += closingValue;
    });
    
    // Create calculation row
    const calcRow = document.createElement('tr');
    calcRow.className = 'calculation-row';
    calcRow.setAttribute('data-row-index', rowCount);
    calcRow.innerHTML = `
        <td colspan="3" style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;">
            <strong>Total Amount</strong>
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="calculation">
            <input type="hidden" name="inventory[${rowCount}][header_text]" value="Total Amount">
            <input type="hidden" name="inventory[${rowCount}][opening_stock_qty]" class="calc-opening-qty" value="${totalOpeningQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][opening_stock_value]" class="calc-opening-value" value="${totalOpeningValue.toFixed(2)}">
            <input type="hidden" name="inventory[${rowCount}][purchase_qty]" class="calc-purchase-qty" value="${totalPurchaseQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][purchase_value]" class="calc-purchase-value" value="${totalPurchaseValue.toFixed(2)}">
            <input type="hidden" name="inventory[${rowCount}][consumption_qty]" class="calc-consumption-qty" value="${totalConsumptionQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][consumption_value]" class="calc-consumption-value" value="${totalConsumptionValue.toFixed(2)}">
            <input type="hidden" name="inventory[${rowCount}][closing_stock_qty]" class="calc-closing-qty" value="${totalClosingQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][closing_stock_value]" class="calc-closing-value" value="${totalClosingValue.toFixed(2)}">
        </td>
        <td style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;"></td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalOpeningQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalOpeningValue.toFixed(2)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalPurchaseQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalPurchaseValue.toFixed(2)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalConsumptionQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalConsumptionValue.toFixed(2)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalClosingQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalClosingValue.toFixed(2)}</td>
        <td colspan="2" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"></td>
        <td style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;">
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Calculation" style="padding: 2px 5px; font-size: 12px; background: #dc3545;">×</button>
        </td>
    `;
    
    // Insert after header row
    headerRow.insertAdjacentElement('afterend', calcRow);
    rowCount++;
    updateSrNos();
}

function addHeaderRow() {
    const tbody = document.getElementById('inventoryBody');
    
    // Get saved header text
    const savedHeaderText = localStorage.getItem('fortnight_header_material_desc') || 'Material Description';
    
    // Create header row (duplicate of table header)
    const headerRow = document.createElement('tr');
    headerRow.className = 'header-row-duplicate';
    headerRow.setAttribute('data-row-index', rowCount);
    headerRow.innerHTML = `
        <td class="col-sr" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">SR NO.
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="header">
        </td>
        <td class="col-code" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Material HSN Code No</td>
        <td class="col-desc" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">
            <div style="display: flex; align-items: center; gap: 3px;">
                <input type="text" 
                       name="inventory[${rowCount}][header_text]"
                       class="header-desc-input" 
                       value="${savedHeaderText}"
                       style="width: 100%; border: none; padding: 2px; font-size: 11px; font-weight: bold; background: transparent; color: #fff; text-align: center;"
                       placeholder="Material Description">
                <button type="button" 
                        onclick="saveHeaderDesc(this)" 
                        title="Save"
                        style="background: #28a745; color: #fff; border: none; padding: 2px 4px; border-radius: 2px; cursor: pointer; font-size: 9px; min-width: 20px; height: 20px;">
                    ✓
                </button>
            </div>
        </td>
        <td class="col-rate" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Net Rate</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Opening Stock Qty</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Opening Stock Value</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Purchase</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Purchase Value</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Consumption</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Consumption Value</td>
        <td class="col-qty" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Closing Stock Qty</td>
        <td class="col-value" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Closing Stock value</td>
        <td class="col-supplier" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Supplier Name</td>
        <td class="col-bill" style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">Bill/ Challan No</td>
        <td style="background: #808080; color: #fff; font-weight: bold; text-align: center; padding: 0.8px 2px;">
            <button type="button" class="btn-action btn-success" onclick="insertCalculationAfter(this)" title="Add Calculation Row" style="padding: 2px 5px; font-size: 10px; background: #ffa500; color: #000; margin-right: 2px;">∑</button>
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Header" style="padding: 2px 5px; font-size: 12px; background: #dc3545;">×</button>
        </td>
    `;
    
    // Append to end of tbody
    tbody.appendChild(headerRow);
    rowCount++;
    updateSrNos();
}

function addCalculationRowAtEnd() {
    const tbody = document.getElementById('inventoryBody');
    const allRows = Array.from(tbody.querySelectorAll('tr'));
    
    // Find last header or start from beginning
    let startIndex = 0;
    for (let i = allRows.length - 1; i >= 0; i--) {
        if (allRows[i].classList.contains('header-row-duplicate') || allRows[i].classList.contains('calculation-row')) {
            startIndex = i + 1;
            break;
        }
    }
    
    // Get all data rows from startIndex to end
    const dataRows = [];
    for (let i = startIndex; i < allRows.length; i++) {
        const row = allRows[i];
        if (!row.classList.contains('header-row-duplicate') && !row.classList.contains('calculation-row')) {
            dataRows.push(row);
        }
    }
    
    // Calculate totals
    let totalOpeningQty = 0, totalOpeningValue = 0;
    let totalPurchaseQty = 0, totalPurchaseValue = 0;
    let totalConsumptionQty = 0, totalConsumptionValue = 0;
    let totalClosingQty = 0, totalClosingValue = 0;
    
    // Excel Formula: SUM equivalent - Sum all values in range
    // Formula: =SUM(Range)
    dataRows.forEach(row => {
        // Parse with IFERROR equivalent (handle empty/null as 0)
        const openingQty = parseFloat(row.querySelector('.opening-qty')?.value || 0) || 0;
        const openingValueText = row.querySelector('.opening-value')?.textContent || '0';
        const openingValue = parseFloat(openingValueText.replace(/[^\d.-]/g, '')) || 0;
        
        const purchaseQty = parseFloat(row.querySelector('.purchase-qty')?.value || 0) || 0;
        const purchaseValueText = row.querySelector('.purchase-value')?.textContent || '0';
        const purchaseValue = parseFloat(purchaseValueText.replace(/[^\d.-]/g, '')) || 0;
        
        const consumptionQty = parseFloat(row.querySelector('.consumption-qty')?.value || 0) || 0;
        const consumptionValueText = row.querySelector('.consumption-value')?.textContent || '0';
        const consumptionValue = parseFloat(consumptionValueText.replace(/[^\d.-]/g, '')) || 0;
        
        const closingQtyText = row.querySelector('.closing-qty')?.textContent || '0';
        const closingQty = parseFloat(closingQtyText.replace(/[^\d.-]/g, '')) || 0;
        const closingValueText = row.querySelector('.closing-value')?.textContent || '0';
        const closingValue = parseFloat(closingValueText.replace(/[^\d.-]/g, '')) || 0;
        
        // Excel SUM formula equivalent
        totalOpeningQty += openingQty;
        totalOpeningValue += openingValue;
        totalPurchaseQty += purchaseQty;
        totalPurchaseValue += purchaseValue;
        totalConsumptionQty += consumptionQty;
        totalConsumptionValue += consumptionValue;
        totalClosingQty += closingQty;
        totalClosingValue += closingValue;
    });
    
    // Create calculation row
    const calcRow = document.createElement('tr');
    calcRow.className = 'calculation-row';
    calcRow.setAttribute('data-row-index', rowCount);
    calcRow.innerHTML = `
        <td colspan="3" style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;">
            <strong>Total Amount</strong>
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="calculation">
            <input type="hidden" name="inventory[${rowCount}][header_text]" value="Total Amount">
            <input type="hidden" name="inventory[${rowCount}][opening_stock_qty]" class="calc-opening-qty" value="${totalOpeningQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][opening_stock_value]" class="calc-opening-value" value="${totalOpeningValue.toFixed(2)}">
            <input type="hidden" name="inventory[${rowCount}][purchase_qty]" class="calc-purchase-qty" value="${totalPurchaseQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][purchase_value]" class="calc-purchase-value" value="${totalPurchaseValue.toFixed(2)}">
            <input type="hidden" name="inventory[${rowCount}][consumption_qty]" class="calc-consumption-qty" value="${totalConsumptionQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][consumption_value]" class="calc-consumption-value" value="${totalConsumptionValue.toFixed(2)}">
            <input type="hidden" name="inventory[${rowCount}][closing_stock_qty]" class="calc-closing-qty" value="${totalClosingQty.toFixed(3)}">
            <input type="hidden" name="inventory[${rowCount}][closing_stock_value]" class="calc-closing-value" value="${totalClosingValue.toFixed(2)}">
        </td>
        <td style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;"></td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalOpeningQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalOpeningValue.toFixed(2)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalPurchaseQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalPurchaseValue.toFixed(2)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalConsumptionQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalConsumptionValue.toFixed(2)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalClosingQty.toFixed(3)}</td>
        <td class="text-right calculated" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;">${totalClosingValue.toFixed(2)}</td>
        <td colspan="2" style="background: #ffa500; color: #000; font-weight: bold; padding: 0.8px 2px; border: 1px solid #000;"></td>
        <td style="background: #ffa500; color: #000; font-weight: bold; text-align: center; padding: 0.8px 2px; border: 1px solid #000;">
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Calculation" style="padding: 2px 5px; font-size: 12px; background: #dc3545;">×</button>
        </td>
    `;
    
    // Append to end of tbody
    tbody.appendChild(calcRow);
    rowCount++;
    updateSrNos();
}

function insertRowAfter(btn) {
    const currentRow = btn.closest('tr');
    const tbody = document.getElementById('inventoryBody');
    const currentIndex = parseInt(currentRow.getAttribute('data-row-index'));
    
    const newRow = document.createElement('tr');
    newRow.setAttribute('data-row-index', rowCount);
    newRow.innerHTML = `
        <td class="col-sr">
            <span class="sr-no">${rowCount + 1}</span>
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="data">
        </td>
        <td class="col-code">
            <input type="text" name="inventory[${rowCount}][material_hsn_code]" 
                   class="material-code" list="hsnCodes">
        </td>
        <td class="col-desc">
            <input type="text" 
                   name="inventory[${rowCount}][material_description]" 
                   class="material-desc" 
                   list="materialNames"
                   onblur="autoSaveMaterialDesc(this, ${rowCount})" 
                   style="width: 100%; border: none; padding: 1px 3px; font-size: 11px; font-family: Arial, sans-serif; height: 22px; line-height: 20px;"
                   required>
            <span class="save-indicator" id="save-indicator-${rowCount}" style="display: none; color: green; font-size: 9px;">✓ Saved</span>
        </td>
        <td class="col-rate">
            <input type="number" step="0.01" name="inventory[${rowCount}][net_rate]" 
                   value="0.00" class="text-right net-rate input-green" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][opening_stock_qty]" 
                   value="0.000" class="text-right opening-qty input-yellow" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated opening-value">0.00</td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][purchase_qty]" 
                   value="0.000" class="text-right purchase-qty input-blue" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated purchase-value">0.00</td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][consumption_qty]" 
                   value="0.000" class="text-right consumption-qty input-green" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated consumption-value">0.00</td>
        <td class="col-qty calculated closing-qty">0.000</td>
        <td class="col-value calculated closing-value">0.00</td>
        <td class="col-supplier">
            <input type="text" name="inventory[${rowCount}][supplier_name]" 
                   class="supplier-name" list="suppliers">
        </td>
        <td class="col-bill">
            <input type="text" name="inventory[${rowCount}][bill_challan_no]">
        </td>
        <td>
            <button type="button" class="btn-action btn-success" onclick="insertRowAfter(this)" title="Insert Row After" style="padding: 2px 5px; font-size: 12px;">+</button>
            <button type="button" class="btn-action btn-info" onclick="insertHeaderAfter(this)" title="Insert Header After" style="padding: 2px 5px; font-size: 12px; background: #ffa500; color: #000;">H</button>
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Row" style="padding: 2px 5px; font-size: 12px;">×</button>
        </td>
    `;
    
    // Insert after current row
    currentRow.insertAdjacentElement('afterend', newRow);
    rowCount++;
    updateSrNos();
}

// Insert data row after a header row
function insertRowAfterHeader(btn) {
    const headerRow = btn.closest('tr');
    const tbody = document.getElementById('inventoryBody');
    
    const newRow = document.createElement('tr');
    newRow.setAttribute('data-row-index', rowCount);
    newRow.innerHTML = `
        <td class="col-sr">
            <span class="sr-no">${rowCount + 1}</span>
            <input type="hidden" name="inventory[${rowCount}][row_type]" value="data">
        </td>
        <td class="col-code">
            <input type="text" name="inventory[${rowCount}][material_hsn_code]" 
                   class="material-code" list="hsnCodes">
        </td>
        <td class="col-desc">
            <input type="text" 
                   name="inventory[${rowCount}][material_description]" 
                   class="material-desc" 
                   list="materialNames"
                   onblur="autoSaveMaterialDesc(this, ${rowCount})" 
                   style="width: 100%; border: none; padding: 1px 3px; font-size: 11px; font-family: Arial, sans-serif; height: 22px; line-height: 20px;"
                   required>
            <span class="save-indicator" id="save-indicator-${rowCount}" style="display: none; color: green; font-size: 9px;">✓ Saved</span>
        </td>
        <td class="col-rate">
            <input type="number" step="0.01" name="inventory[${rowCount}][net_rate]" 
                   value="0.00" class="text-right net-rate input-green" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][opening_stock_qty]" 
                   value="0.000" class="text-right opening-qty input-yellow" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated opening-value">0.00</td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][purchase_qty]" 
                   value="0.000" class="text-right purchase-qty input-blue" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated purchase-value">0.00</td>
        <td class="col-qty">
            <input type="number" step="0.001" name="inventory[${rowCount}][consumption_qty]" 
                   value="0.000" class="text-right consumption-qty input-green" 
                   onchange="calculateRow(${rowCount})" oninput="calculateRow(${rowCount})">
        </td>
        <td class="col-value calculated consumption-value">0.00</td>
        <td class="col-qty calculated closing-qty">0.000</td>
        <td class="col-value calculated closing-value">0.00</td>
        <td class="col-supplier">
            <input type="text" name="inventory[${rowCount}][supplier_name]" 
                   class="supplier-name" list="suppliers">
        </td>
        <td class="col-bill">
            <input type="text" name="inventory[${rowCount}][bill_challan_no]">
        </td>
        <td>
            <button type="button" class="btn-action btn-success" onclick="insertRowAfter(this)" title="Insert Row After" style="padding: 2px 5px; font-size: 12px;">+</button>
            <button type="button" class="btn-action btn-info" onclick="insertHeaderAfter(this)" title="Insert Header After" style="padding: 2px 5px; font-size: 12px; background: #ffa500; color: #000;">H</button>
            <button type="button" class="btn-action btn-danger" onclick="removeRow(this)" title="Remove Row" style="padding: 2px 5px; font-size: 12px;">×</button>
        </td>
    `;
    
    // Insert after header row
    headerRow.insertAdjacentElement('afterend', newRow);
    rowCount++;
    updateSrNos();
}

function removeRow(btn) {
    const row = btn.closest('tr');
    if (!row) return;
    
    const isCalcRow = row.classList.contains('calculation-row');
    const isHeaderRow = row.classList.contains('header-row-duplicate');
    const confirmMsg = isCalcRow 
        ? 'Are you sure you want to remove this calculation row?' 
        : isHeaderRow
        ? 'Are you sure you want to remove this header row?'
        : 'Are you sure you want to remove this row?';
    
    if (confirm(confirmMsg)) {
        // Add fade out effect
        row.style.transition = 'opacity 0.2s';
        row.style.opacity = '0';
        
        setTimeout(() => {
            row.remove();
            updateSrNos();
            // Recalculate all calculation rows after removal
            recalculateAllCalculationRows();
        }, 200);
    }
}

function updateSrNos() {
    const rows = document.querySelectorAll('#inventoryBody tr');
    let globalRowIndex = 0;  // Index for ALL rows (data, header, calculation) - maintains DOM order
    let dataRowSrNo = 1;     // Serial number only for data rows
    
    rows.forEach((row) => {
        // Update row index for ALL row types to maintain DOM order
        row.setAttribute('data-row-index', globalRowIndex);
        
        // Update all input names for this row
        const inputs = row.querySelectorAll('input, textarea');
        inputs.forEach(input => {
            const name = input.getAttribute('name');
            if (name) {
                const newName = name.replace(/inventory\[\d+\]/, `inventory[${globalRowIndex}]`);
                input.setAttribute('name', newName);
            }
        });
        
        // Handle data rows specifically
        if (!row.classList.contains('header-row-duplicate') && !row.classList.contains('calculation-row')) {
            // Update serial number display
            const srNo = row.querySelector('.sr-no');
            if (srNo) {
                srNo.textContent = dataRowSrNo;
            }
            
            // Update onchange/oninput for calculation inputs
            inputs.forEach(input => {
                if (input.tagName === 'INPUT') {
                    if (input.classList.contains('net-rate') || input.classList.contains('opening-qty') || 
                        input.classList.contains('purchase-qty') || input.classList.contains('consumption-qty')) {
                        input.setAttribute('onchange', `calculateRow(${globalRowIndex})`);
                        input.setAttribute('oninput', `calculateRow(${globalRowIndex})`);
                    }
                }
                // Update onblur for material description input
                if (input.classList.contains('material-desc')) {
                    input.setAttribute('onblur', `autoSaveMaterialDesc(this, ${globalRowIndex})`);
                }
            });
            
            // Update save indicator ID
            const indicator = row.querySelector('.save-indicator');
            if (indicator) {
                indicator.id = `save-indicator-${globalRowIndex}`;
            }
            
            dataRowSrNo++;
        }
        
        globalRowIndex++;
    });
    
    // Update global rowCount to next available index
    rowCount = globalRowIndex;
}

// Auto-save Material Description (on blur)
let saveTimeout = {};
function autoSaveMaterialDesc(input, rowIndex) {
    const materialDesc = input.value.trim();
    if (!materialDesc) return;
    
    // Save to localStorage as backup
    const periodId = <?php echo $periodId > 0 ? $periodId : 'null'; ?>;
    const key = `fortnight_material_${periodId}_${rowIndex}`;
    localStorage.setItem(key, materialDesc);
    
    // Show saved indicator
    const indicator = document.getElementById(`save-indicator-${rowIndex}`);
    if (indicator) {
        indicator.textContent = '✓ Saved';
        indicator.style.color = 'green';
        indicator.style.display = 'inline';
        setTimeout(() => {
            indicator.style.display = 'none';
        }, 2000);
    }
}

// Save Material Description on button click
function saveMaterialDesc(btn, rowIndex) {
    const row = btn.closest('tr');
    const input = row.querySelector('.material-desc');
    
    if (!input) return;
    
    const materialDesc = input.value.trim();
    if (!materialDesc) return;
    
    // Save to localStorage
    const periodId = <?php echo $periodId > 0 ? $periodId : 'null'; ?>;
    const key = `fortnight_material_${periodId}_${rowIndex}`;
    localStorage.setItem(key, materialDesc);
    
    // Show save confirmation
    const indicator = document.getElementById(`save-indicator-${rowIndex}`);
    if (indicator) {
        indicator.textContent = '✓ Saved';
        indicator.style.color = 'green';
        indicator.style.display = 'inline';
        setTimeout(() => {
            indicator.style.display = 'none';
        }, 2000);
    }
    
    // Visual feedback on button
    btn.style.background = '#28a745';
    btn.style.opacity = '0.8';
    setTimeout(() => {
        btn.style.opacity = '1';
    }, 500);
}

function calculateRow(rowIndex) {
    const row = document.querySelector(`tr[data-row-index="${rowIndex}"]`);
    if (!row) return;
    
    // Get input values with proper parsing and validation
    const netRateInput = row.querySelector('.net-rate');
    const openingQtyInput = row.querySelector('.opening-qty');
    const purchaseQtyInput = row.querySelector('.purchase-qty');
    const consumptionQtyInput = row.querySelector('.consumption-qty');
    
    if (!netRateInput || !openingQtyInput || !purchaseQtyInput || !consumptionQtyInput) return;
    
    // Parse values with proper handling of empty/null values (Excel equivalent: IFERROR(VALUE(), 0))
    const netRate = parseFloat(netRateInput.value) || 0;
    const openingQty = Math.max(0, parseFloat(openingQtyInput.value) || 0); // Prevent negative
    const purchaseQty = Math.max(0, parseFloat(purchaseQtyInput.value) || 0); // Prevent negative
    const consumptionQty = Math.max(0, parseFloat(consumptionQtyInput.value) || 0); // Prevent negative
    
    // Excel Formula: Opening Value = Opening Qty × Rate
    // Formula: =IFERROR(OpeningQty * Rate, 0)
    const openingValue = Math.round((openingQty * netRate) * 100) / 100; // Round to 2 decimals
    
    // Excel Formula: Purchase Value = Purchase Qty × Rate
    // Formula: =IFERROR(PurchaseQty * Rate, 0)
    const purchaseValue = Math.round((purchaseQty * netRate) * 100) / 100; // Round to 2 decimals
    
    // Excel Formula: Consumption Value = Consumption Qty × Rate
    // Formula: =IFERROR(ConsumptionQty * Rate, 0)
    const consumptionValue = Math.round((consumptionQty * netRate) * 100) / 100; // Round to 2 decimals
    
    // Excel Formula: Closing Qty = Opening Qty + Purchase Qty - Consumption Qty
    // Formula: =IFERROR(OpeningQty + PurchaseQty - ConsumptionQty, 0)
    // Prevent negative closing stock (Excel: =MAX(0, OpeningQty + PurchaseQty - ConsumptionQty))
    const closingQty = Math.max(0, Math.round((openingQty + purchaseQty - consumptionQty) * 1000) / 1000); // Round to 3 decimals
    
    // Excel Formula: Closing Value = Closing Qty × Rate
    // Formula: =IFERROR(ClosingQty * Rate, 0)
    const closingValue = Math.round((closingQty * netRate) * 100) / 100; // Round to 2 decimals
    
    // Update displayed values with null checks
    const openingValueEl = row.querySelector('.opening-value');
    const purchaseValueEl = row.querySelector('.purchase-value');
    const consumptionValueEl = row.querySelector('.consumption-value');
    const closingQtyEl = row.querySelector('.closing-qty');
    const closingValueEl = row.querySelector('.closing-value');
    
    if (openingValueEl) openingValueEl.textContent = openingValue.toFixed(2);
    if (purchaseValueEl) purchaseValueEl.textContent = purchaseValue.toFixed(2);
    if (consumptionValueEl) consumptionValueEl.textContent = consumptionValue.toFixed(2);
    if (closingQtyEl) closingQtyEl.textContent = closingQty.toFixed(3);
    if (closingValueEl) closingValueEl.textContent = closingValue.toFixed(2);
    
    // Visual warning if closing stock is negative (shouldn't happen due to Math.max, but for safety)
    if (closingQty < 0) {
        if (closingQtyEl) closingQtyEl.style.background = '#ffe6e6';
    } else {
        if (closingQtyEl) closingQtyEl.style.background = '';
    }
    
    // Recalculate all calculation rows
    recalculateAllCalculationRows();
}

// Recalculate all calculation rows
function recalculateAllCalculationRows() {
    const tbody = document.getElementById('inventoryBody');
    const allRows = Array.from(tbody.querySelectorAll('tr'));
    
    // Find all calculation rows
    allRows.forEach((calcRow, calcIndex) => {
        if (calcRow.classList.contains('calculation-row')) {
            // Find previous header or start
            let startIndex = 0;
            for (let i = calcIndex - 1; i >= 0; i--) {
                if (allRows[i].classList.contains('header-row-duplicate') || 
                    (allRows[i].classList.contains('calculation-row') && i !== calcIndex)) {
                    startIndex = i + 1;
                    break;
                }
            }
            
            // Get all data rows between previous header/calc and current calc
            const dataRows = [];
            for (let i = startIndex; i < calcIndex; i++) {
                const row = allRows[i];
                if (!row.classList.contains('header-row-duplicate') && !row.classList.contains('calculation-row')) {
                    dataRows.push(row);
                }
            }
            
            // Calculate totals
            let totalOpeningQty = 0, totalOpeningValue = 0;
            let totalPurchaseQty = 0, totalPurchaseValue = 0;
            let totalConsumptionQty = 0, totalConsumptionValue = 0;
            let totalClosingQty = 0, totalClosingValue = 0;
            
            // Excel Formula: SUMIF equivalent - Sum all values in range
            // Formula: =SUMIF(Range, Criteria, SumRange) or =SUM(Range)
            dataRows.forEach(row => {
                // Parse with IFERROR equivalent (handle empty/null as 0)
                const openingQty = parseFloat(row.querySelector('.opening-qty')?.value || 0) || 0;
                // Read calculated value from display (already rounded)
                const openingValueText = row.querySelector('.opening-value')?.textContent || '0';
                const openingValue = parseFloat(openingValueText.replace(/[^\d.-]/g, '')) || 0;
                
                const purchaseQty = parseFloat(row.querySelector('.purchase-qty')?.value || 0) || 0;
                const purchaseValueText = row.querySelector('.purchase-value')?.textContent || '0';
                const purchaseValue = parseFloat(purchaseValueText.replace(/[^\d.-]/g, '')) || 0;
                
                const consumptionQty = parseFloat(row.querySelector('.consumption-qty')?.value || 0) || 0;
                const consumptionValueText = row.querySelector('.consumption-value')?.textContent || '0';
                const consumptionValue = parseFloat(consumptionValueText.replace(/[^\d.-]/g, '')) || 0;
                
                const closingQtyText = row.querySelector('.closing-qty')?.textContent || '0';
                const closingQty = parseFloat(closingQtyText.replace(/[^\d.-]/g, '')) || 0;
                const closingValueText = row.querySelector('.closing-value')?.textContent || '0';
                const closingValue = parseFloat(closingValueText.replace(/[^\d.-]/g, '')) || 0;
                
                // Excel SUM formula equivalent
                totalOpeningQty += openingQty;
                totalOpeningValue += openingValue;
                totalPurchaseQty += purchaseQty;
                totalPurchaseValue += purchaseValue;
                totalConsumptionQty += consumptionQty;
                totalConsumptionValue += consumptionValue;
                totalClosingQty += closingQty;
                totalClosingValue += closingValue;
            });
            
            // Update calculation row display values
            const cells = calcRow.querySelectorAll('td');
            if (cells.length >= 12) {
                cells[4].textContent = totalOpeningQty.toFixed(3);  // Opening Qty
                cells[5].textContent = totalOpeningValue.toFixed(2); // Opening Value
                cells[6].textContent = totalPurchaseQty.toFixed(3);  // Purchase Qty
                cells[7].textContent = totalPurchaseValue.toFixed(2); // Purchase Value
                cells[8].textContent = totalConsumptionQty.toFixed(3); // Consumption Qty
                cells[9].textContent = totalConsumptionValue.toFixed(2); // Consumption Value
                cells[10].textContent = totalClosingQty.toFixed(3); // Closing Qty
                cells[11].textContent = totalClosingValue.toFixed(2); // Closing Value
            }
            
            // Also update hidden inputs for form submission
            const calcOpeningQty = calcRow.querySelector('.calc-opening-qty');
            const calcOpeningValue = calcRow.querySelector('.calc-opening-value');
            const calcPurchaseQty = calcRow.querySelector('.calc-purchase-qty');
            const calcPurchaseValue = calcRow.querySelector('.calc-purchase-value');
            const calcConsumptionQty = calcRow.querySelector('.calc-consumption-qty');
            const calcConsumptionValue = calcRow.querySelector('.calc-consumption-value');
            const calcClosingQty = calcRow.querySelector('.calc-closing-qty');
            const calcClosingValue = calcRow.querySelector('.calc-closing-value');
            
            if (calcOpeningQty) calcOpeningQty.value = totalOpeningQty.toFixed(3);
            if (calcOpeningValue) calcOpeningValue.value = totalOpeningValue.toFixed(2);
            if (calcPurchaseQty) calcPurchaseQty.value = totalPurchaseQty.toFixed(3);
            if (calcPurchaseValue) calcPurchaseValue.value = totalPurchaseValue.toFixed(2);
            if (calcConsumptionQty) calcConsumptionQty.value = totalConsumptionQty.toFixed(3);
            if (calcConsumptionValue) calcConsumptionValue.value = totalConsumptionValue.toFixed(2);
            if (calcClosingQty) calcClosingQty.value = totalClosingQty.toFixed(3);
            if (calcClosingValue) calcClosingValue.value = totalClosingValue.toFixed(2);
        }
    });
}

// Update hidden inputs in calculation rows before form submission
function updateCalculationRowHiddenInputs() {
    const tbody = document.getElementById('inventoryBody');
    const calcRows = tbody.querySelectorAll('.calculation-row');
    
    calcRows.forEach(calcRow => {
        const cells = calcRow.querySelectorAll('td');
        if (cells.length >= 12) {
            // Get values from display cells
            const openingQty = parseFloat(cells[4].textContent.replace(/[^\d.-]/g, '')) || 0;
            const openingValue = parseFloat(cells[5].textContent.replace(/[^\d.-]/g, '')) || 0;
            const purchaseQty = parseFloat(cells[6].textContent.replace(/[^\d.-]/g, '')) || 0;
            const purchaseValue = parseFloat(cells[7].textContent.replace(/[^\d.-]/g, '')) || 0;
            const consumptionQty = parseFloat(cells[8].textContent.replace(/[^\d.-]/g, '')) || 0;
            const consumptionValue = parseFloat(cells[9].textContent.replace(/[^\d.-]/g, '')) || 0;
            const closingQty = parseFloat(cells[10].textContent.replace(/[^\d.-]/g, '')) || 0;
            const closingValue = parseFloat(cells[11].textContent.replace(/[^\d.-]/g, '')) || 0;
            
            // Update hidden inputs
            const calcOpeningQty = calcRow.querySelector('.calc-opening-qty');
            const calcOpeningValue = calcRow.querySelector('.calc-opening-value');
            const calcPurchaseQty = calcRow.querySelector('.calc-purchase-qty');
            const calcPurchaseValue = calcRow.querySelector('.calc-purchase-value');
            const calcConsumptionQty = calcRow.querySelector('.calc-consumption-qty');
            const calcConsumptionValue = calcRow.querySelector('.calc-consumption-value');
            const calcClosingQty = calcRow.querySelector('.calc-closing-qty');
            const calcClosingValue = calcRow.querySelector('.calc-closing-value');
            
            if (calcOpeningQty) calcOpeningQty.value = openingQty.toFixed(3);
            if (calcOpeningValue) calcOpeningValue.value = openingValue.toFixed(2);
            if (calcPurchaseQty) calcPurchaseQty.value = purchaseQty.toFixed(3);
            if (calcPurchaseValue) calcPurchaseValue.value = purchaseValue.toFixed(2);
            if (calcConsumptionQty) calcConsumptionQty.value = consumptionQty.toFixed(3);
            if (calcConsumptionValue) calcConsumptionValue.value = consumptionValue.toFixed(2);
            if (calcClosingQty) calcClosingQty.value = closingQty.toFixed(3);
            if (calcClosingValue) calcClosingValue.value = closingValue.toFixed(2);
        }
    });
}

function filterByMonthYear() {
    const month = document.getElementById('filter_month').value;
    const year = document.getElementById('filter_year').value;
    window.location.href = '?month=' + month + '&year=' + year;
}

// Save "To" name
function saveToName() {
    const toName = document.getElementById('to_name_input').value;
    const btn = event.target;
    
    fetch(window.location.pathname, {
        method: 'POST',
        headers: {
            'Content-Type': 'application/x-www-form-urlencoded',
        },
        body: 'save_to_name=1&to_name=' + encodeURIComponent(toName)
    })
    .then(response => response.json())
    .then(data => {
        if (data.success) {
            // Show success
            const originalBg = btn.style.background;
            btn.innerHTML = '✓';
            btn.style.background = 'linear-gradient(135deg, #10b981 0%, #059669 100%)';
            setTimeout(() => {
                btn.innerHTML = '✓';
                btn.style.background = originalBg || 'linear-gradient(135deg, #10b981 0%, #059669 100%)';
            }, 1500);
        } else {
            alert('Error saving name: ' + data.message);
        }
    })
    .catch(error => {
        alert('Error saving name');
        console.error(error);
    });
}

function exportToExcel() {
    // Redirect to export page
    const month = document.getElementById('filter_month').value;
    const year = document.getElementById('filter_year').value;
    window.location.href = '<?php echo BASE_URL; ?>/modules/fortnight/export.php?month=' + month + '&year=' + year;
}

// Auto-fill rate when material is selected (for datalist)
document.addEventListener('DOMContentLoaded', function() {
    // Load saved material descriptions from localStorage
    const periodId = <?php echo $periodId > 0 ? $periodId : 'null'; ?>;
    if (periodId) {
        document.querySelectorAll('.material-desc').forEach((input, index) => {
            const key = `fortnight_material_${periodId}_${index}`;
            const saved = localStorage.getItem(key);
            if (saved && !input.value) {
                input.value = saved;
            }
        });
    }
    
    // CRITICAL: Update all row indexes on page load to ensure correct order
    updateSrNos();
    
    // Initialize calculations for all existing rows
    document.querySelectorAll('tr[data-row-index]').forEach(row => {
        const rowIndex = parseInt(row.getAttribute('data-row-index'));
        if (!isNaN(rowIndex)) {
            calculateRow(rowIndex);
        }
    });
    
    // Recalculate all calculation rows on page load
    recalculateAllCalculationRows();
    
    // CRITICAL: Add form submit handler to ensure correct row order before save
    const form = document.getElementById('fortnightForm');
    if (form) {
        form.addEventListener('submit', function(e) {
            // Update all row indexes to match DOM order before submitting
            updateSrNos();
            // Recalculate all calculation rows to ensure values are correct
            recalculateAllCalculationRows();
            // Update hidden inputs in calculation rows with current values
            updateCalculationRowHiddenInputs();
        });
    }
    
    // Auto-fill rate when material is typed (for input with datalist)
    document.querySelectorAll('.material-desc').forEach(input => {
        input.addEventListener('input', function() {
            const value = this.value.trim();
            if (value) {
                // Try to find matching option
                const option = document.querySelector(`#materialNames option[value="${value}"]`);
                if (option) {
                    const row = this.closest('tr');
                    if (row) {
                        if (row.querySelector('.material-code')) {
                            row.querySelector('.material-code').value = option.getAttribute('data-code') || '';
                        }
                        if (row.querySelector('.net-rate')) {
                            row.querySelector('.net-rate').value = option.getAttribute('data-rate') || '0.00';
                            const rowIndex = parseInt(row.getAttribute('data-row-index'));
                            if (!isNaN(rowIndex)) {
                                calculateRow(rowIndex);
                            }
                        }
                    }
                }
            }
        });
    });
    
    // Add validation for numeric inputs
    document.querySelectorAll('input[type="number"]').forEach(input => {
        input.addEventListener('blur', function() {
            if (this.value && (isNaN(this.value) || parseFloat(this.value) < 0)) {
                this.style.background = '#ffe6e6';
                setTimeout(() => {
                    if (this.value && !isNaN(this.value) && parseFloat(this.value) >= 0) {
                        this.style.background = '';
                    }
                }, 2000);
            } else {
                this.style.background = '';
            }
        });
    });
    
    // Load saved header material description
    const savedHeaderText = localStorage.getItem('fortnight_header_material_desc');
    if (savedHeaderText) {
        const headerInput = document.getElementById('header_material_desc');
        if (headerInput) {
            headerInput.value = savedHeaderText;
        }
    }
});

// Save main header Material Description (only once on button click)
function saveHeaderMaterialDesc() {
    const input = document.getElementById('header_material_desc');
    const indicator = document.getElementById('header-save-indicator');
    
    if (!input) return;
    
    const text = input.value.trim();
    
    // Save to localStorage (only for default template, not for individual headers)
    localStorage.setItem('fortnight_header_material_desc', text);
    
    // Show save indicator
    if (indicator) {
        indicator.style.display = 'block';
        indicator.textContent = '✓ Saved';
        indicator.style.color = '#90ee90';
        
        setTimeout(() => {
            indicator.style.display = 'none';
        }, 2000);
    }
}

// Save individual header row Material Description
function saveHeaderDesc(btn) {
    const headerRow = btn.closest('tr');
    const input = headerRow.querySelector('.header-desc-input');
    
    if (!input) return;
    
    const text = input.value.trim() || 'Material Description';
    
    // Save to this specific header row (text is already in input field)
    // Show save confirmation
    const originalBg = btn.style.background;
    const originalText = btn.textContent;
    btn.style.background = '#28a745';
    btn.textContent = '✓';
    btn.style.opacity = '0.8';
    
    setTimeout(() => {
        btn.style.background = originalBg;
        btn.textContent = originalText;
        btn.style.opacity = '1';
    }, 1500);
}
</script>

<?php include __DIR__ . '/../../includes/footer.php'; ?>

