<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();
require_once("connectioni.php");

// Assign the global mysqli connection to the $conn variable
$conn = $GLOBALS['dbinv'];

// =================================================================
// THE FIX: Automatically patch tables missing the COST & SELLING columns
// =================================================================
$check_trn = $conn->query("SHOW COLUMNS FROM s_trn LIKE 'SELLING'");
if ($check_trn && $check_trn->num_rows == 0) {
    // If SELLING is missing in s_trn, add COST and SELLING
    $conn->query("ALTER TABLE s_trn ADD COLUMN COST DECIMAL(12,2) DEFAULT 0");
    $conn->query("ALTER TABLE s_trn ADD COLUMN SELLING DECIMAL(12,2) DEFAULT 0");
}

$check_pur = $conn->query("SHOW COLUMNS FROM s_purtrn LIKE 'SELLING'");
if ($check_pur && $check_pur->num_rows == 0) {
    // If SELLING is missing in s_purtrn, add COST and SELLING
    $conn->query("ALTER TABLE s_purtrn ADD COLUMN COST DECIMAL(12,2) DEFAULT 0");
    $conn->query("ALTER TABLE s_purtrn ADD COLUMN SELLING DECIMAL(12,2) DEFAULT 0");
}
// =================================================================
// ---------------------------
// AJAX handlers
// ---------------------------
if (isset($_GET['action'])) {
    $action = $_GET['action'];
    
    // -----------------------------------------------------------------
    // 1. Get items for left grid (Contra FIFO with cumulative REC_QTY) - NO RUNNING VALUE COLUMN
    // -----------------------------------------------------------------
    if ($action === 'get_items') {
        $brandFilter = isset($_GET['brand_name']) ? trim($_GET['brand_name']) : '';
        $brand_esc = $conn->real_escape_string($brandFilter);
        
        // First, get all stock items that satisfy QTYINHAND > 0 and have purchase records
        $master_sql = "SELECT STK_NO, BRAND_NAME, DESCRIPT, QTYINHAND 
                       FROM s_mas 
                       WHERE QTYINHAND > 0";
        if ($brandFilter !== '') {
            $master_sql .= " AND BRAND_NAME LIKE '%$brand_esc%'";
        }
        $master_sql .= " ORDER BY STK_NO";
        $master_res = $conn->query($master_sql);
        
        $all_rows = array();
        $grand_totals = array(
            'total_stock_value' => 0,
            'over90_value' => 0,
            'sixty90_value' => 0
        );
        
        $items_meta = array(); // store meta per item for quick access
        
        while ($master = $master_res->fetch_assoc()) {
            $stk = $master['STK_NO'];
            $qty_in_hand = (float)$master['QTYINHAND'];
            
            // Get purchase batches ordered by date
            $purchase_sql = "SELECT SDATE, REFNO, REC_QTY, COST, SELLING, 
                                    DATEDIFF(CURDATE(), SDATE) AS days
                             FROM s_purtrn 
                             WHERE STK_NO = '" . $conn->real_escape_string($stk) . "'
                             ORDER BY SDATE DESC";
            $purchase_res = $conn->query($purchase_sql);
            
            $cumulative = 0;
            $item_rows = array();
            $item_total_rec_qty = 0;
            $item_total_running_value = 0;
            $item_over90_value = 0;
            $item_sixty90_value = 0;
            
            while ($p = $purchase_res->fetch_assoc()) {
                $rec_qty = (float)$p['REC_QTY'];
                $cost = (float)$p['COST'];
                $days = (int)$p['days'];
                $running_value = $rec_qty * $cost;
                
                // Contra FIFO condition: cumulative + rec_qty <= qty_in_hand
                if ($cumulative + $rec_qty <= $qty_in_hand) {
                    $allocated_qty = $rec_qty;
                    $cumulative += $rec_qty;
                } elseif ($cumulative < $qty_in_hand) {
                    $allocated_qty = $qty_in_hand - $cumulative;
                    $cumulative = $qty_in_hand;
                    $running_value = $allocated_qty * $cost;
                } else {
                    break; // no more stock coverage
                }
                
                if ($allocated_qty <= 0) continue;
                
                $p['REC_QTY'] = $allocated_qty;
                $p['running_value'] = $running_value;
                $p['STK_NO'] = $stk;
                $p['BRAND_NAME'] = $master['BRAND_NAME'];
                $p['DESCRIPT'] = $master['DESCRIPT'];
                $p['QTYINHAND'] = $qty_in_hand;
                $item_rows[] = $p;
                
                $item_total_rec_qty += $allocated_qty;
                $item_total_running_value += $running_value;
                
                // Ageing stock values
                if ($days > 90) {
                    $item_over90_value += $running_value;
                } elseif ($days > 60 && $days < 90) {
                    $item_sixty90_value += $running_value;
                }
            }
            
            // Calculate weighted average cost for the item
            $item_avg_cost = ($item_total_rec_qty > 0) ? ($item_total_running_value / $item_total_rec_qty) : 0;
            
            // Store meta
            $items_meta[$stk] = array(
                'over90_value' => $item_over90_value,
                'sixty90_value' => $item_sixty90_value,
                'total_stock_value' => $item_total_running_value,
                'avg_cost' => $item_avg_cost,
                'brand' => $master['BRAND_NAME'],
                'desc' => $master['DESCRIPT']
            );
            
            // Append rows with master fields (duplicate suppression will be done in JS)
            foreach ($item_rows as $row) {
                $row['item_total_rec_qty'] = $item_total_rec_qty;
                $row['item_total_running_value'] = $item_total_running_value;
                $row['item_avg_cost'] = $item_avg_cost;
                $row['item_over90_value'] = $item_over90_value;
                $row['item_sixty90_value'] = $item_sixty90_value;
                $all_rows[] = $row;
            }
            
            // Accumulate grand totals
            $grand_totals['total_stock_value'] += $item_total_running_value;
            $grand_totals['over90_value'] += $item_over90_value;
            $grand_totals['sixty90_value'] += $item_sixty90_value;
        }
        
        header('Content-Type: application/json');
        echo json_encode(array(
            'rows' => $all_rows,
            'grand_totals' => $grand_totals,
            'items_meta' => $items_meta
        ));
        exit;
    }
    
    // -----------------------------------------------------------------
    // 2. Get chart data (last 6 months sales, purchase, GP)
    // -----------------------------------------------------------------
    if ($action === 'get_chart_data') {
        $stk = $conn->real_escape_string($_GET['stk_no']);
        $last6 = date('Y-m-01', strtotime('-5 months'));
        
        $labels = array();
        $sales_data = array();
        $purchase_data = array();
        $gp_data = array();
        
        // Build last 6 months labels
        for ($i = 5; $i >= 0; $i--) {
            $labels[] = date('Y-m', strtotime("-$i months"));
        }
        
        // Sales and GP
        $sales_sql = "SELECT DATE_FORMAT(SDATE, '%Y-%m') as mon, 
                             SUM(TRU_QTY * -1) as sales_qty,
                             SUM((TRU_QTY * -1) * (SELLING - COST)) as gp
                      FROM s_trn
                      WHERE (LEDINDI='INV' OR LEDINDI='GRN') 
                        AND STK_NO='$stk'
                        AND SDATE >= '$last6'
                      GROUP BY mon
                      ORDER BY mon";
        $sales_res = $conn->query($sales_sql);
        
        // ADDED ERROR CHECK HERE
        if (!$sales_res) {
            header('Content-Type: application/json');
            echo json_encode(['error' => 'Sales query failed: ' . $conn->error]);
            exit;
        }

        $sales_map = array();
        $gp_map = array();
        while ($r = $sales_res->fetch_assoc()) {
            $sales_map[$r['mon']] = (float)$r['sales_qty'];
            $gp_map[$r['mon']] = (float)$r['gp'];
        }
        
        // Purchase returns
        $pur_sql = "SELECT DATE_FORMAT(SDATE, '%Y-%m') as mon, SUM(TRU_QTY) as pur_qty
                    FROM s_trn
                    WHERE LEDINDI='ARN' AND STK_NO='$stk' AND SDATE >= '$last6'
                    GROUP BY mon";
        $pur_res = $conn->query($pur_sql);
        
        // ADDED ERROR CHECK HERE (Line 242 fix)
        if (!$pur_res) {
            header('Content-Type: application/json');
            echo json_encode(['error' => 'Purchase query failed: ' . $conn->error]);
            exit;
        }

        $pur_map = array();
        while ($r = $pur_res->fetch_assoc()) {
            $pur_map[$r['mon']] = (float)$r['pur_qty'];
        }
        
        foreach ($labels as $mon) {
            $sales_data[] = isset($sales_map[$mon]) ? $sales_map[$mon] : 0;
            $gp_data[] = isset($gp_map[$mon]) ? $gp_map[$mon] : 0;
            $purchase_data[] = isset($pur_map[$mon]) ? $pur_map[$mon] : 0;
        }
        
        // Forecast next 3 months using average of last 3 months sales
        $last_three = array_slice($sales_data, -3);
        $avg_sales = (count($last_three) > 0) ? array_sum($last_three) / count($last_three) : 0;
        $forecast_labels = array();
        $forecast_values = array();
        $last_label = end($labels);
        $date = DateTime::createFromFormat('Y-m', $last_label);
        for ($i = 1; $i <= 3; $i++) {
            $date->modify('+1 month');
            $forecast_labels[] = $date->format('Y-m');
            $forecast_values[] = round($avg_sales, 2);
        }
        
        header('Content-Type: application/json');
        echo json_encode(array(
            'labels' => $labels,
            'sales' => $sales_data,
            'purchase' => $purchase_data,
            'gp' => $gp_data,
            'forecast_labels' => $forecast_labels,
            'forecast_values' => $forecast_values,
            'avg_sales' => round($avg_sales, 2)
        ));
        exit;
    }
    
    // -----------------------------------------------------------------
    // 3. Sales popup data (last N months) - with GP negative red coloring
    // -----------------------------------------------------------------
    if ($action === 'popup_sales') {
        $stk = $conn->real_escape_string($_GET['stk_no']);
        $months = isset($_GET['months']) ? (int)$_GET['months'] : 6;
        $cutoff = date('Y-m-d', strtotime("-$months months"));
        
        $sql = "SELECT SDATE, REFNO, DEPARTMENT, (TRU_QTY * -1) AS qty, COST, SELLING,
                       ((SELLING - COST) * (TRU_QTY * -1)) AS gp
                FROM s_trn
                WHERE (LEDINDI='INV' OR LEDINDI='GRN')
                  AND STK_NO='$stk'
                  AND SDATE >= '$cutoff'
                ORDER BY SDATE DESC";
        $res = $conn->query($sql);
        
        // ADDED ERROR CHECK HERE
        if (!$res) {
            header('Content-Type: application/json');
            echo json_encode(['error' => 'Sales popup query failed: ' . $conn->error]);
            exit;
        }

        $data = array();
        while ($r = $res->fetch_assoc()) {
            $data[] = $r;
        }
        header('Content-Type: application/json');
        echo json_encode($data);
        exit;
    }
    
    // -----------------------------------------------------------------
    // 4. Purchase popup data (last N months)
    // -----------------------------------------------------------------
    if ($action === 'popup_purchase') {
        $stk = $conn->real_escape_string($_GET['stk_no']);
        $months = isset($_GET['months']) ? (int)$_GET['months'] : 6;
        $cutoff = date('Y-m-d', strtotime("-$months months"));
        
        $sql = "SELECT SDATE, REFNO, REC_QTY, COST, SELLING, (REC_QTY * COST) AS subtot
                FROM s_purtrn
                WHERE STK_NO='$stk' AND SDATE >= '$cutoff'
                ORDER BY SDATE DESC";
        $res = $conn->query($sql);
        
        // ADDED ERROR CHECK HERE (This fixes the line 323 crash)
        if (!$res) {
            header('Content-Type: application/json');
            echo json_encode(['error' => 'Purchase popup query failed: ' . $conn->error]);
            exit;
        }

        $data = array();
        while ($r = $res->fetch_assoc()) {
            $data[] = $r;
        }
        header('Content-Type: application/json');
        echo json_encode($data);
        exit;
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes">
    <title>Contra FIFO Stock</title>
    <!-- Google Fonts + Font Awesome -->
    <link href="https://fonts.googleapis.com/css2?family=Inter:opsz,wght@14..32,300;14..32,400;14..32,500;14..32,600;14..32,700&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body {
            background: linear-gradient(145deg, #eef2f9 0%, #d9e2ef 100%);
            font-family: 'Inter', sans-serif;
            font-size: 12px;
            height: 100vh;
            overflow: hidden;
            color: #1a2c3e;
        }

        /* Glassmorphism container */
        .app-container {
            height: 100%;
            display: flex;
            flex-direction: column;
            padding: 16px 20px 20px 20px;
            backdrop-filter: blur(2px);
        }

        /* Top Bar with floating effect */
        .top-bar {
            background: rgba(255, 255, 255, 0.65);
            backdrop-filter: blur(12px);
            border-radius: 60px;
            padding: 6px 20px;
            margin-bottom: 20px;
            display: flex;
            justify-content: flex-end;
            gap: 16px;
            box-shadow: 0 8px 20px rgba(0, 0, 0, 0.05), 0 1px 2px rgba(0, 0, 0, 0.03);
            border: 1px solid rgba(255, 255, 255, 0.6);
        }

        .glass-btn {
            background: #4b5563;
            border: none;
            border-radius: 40px;
            padding: 8px 28px;
            font-weight: 600;
            font-size: 13px;
            color: white;
            cursor: pointer;
            transition: all 0.2s ease;
            backdrop-filter: blur(4px);
            display: inline-flex;
            align-items: center;
            gap: 8px;
            letter-spacing: 0.3px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }

        .glass-btn i {
            font-size: 14px;
        }

        .glass-btn:hover {
            background: #374151;
            transform: translateY(-2px);
            box-shadow: 0 10px 20px -5px rgba(0, 0, 0, 0.2);
        }

        /* Split Layout */
        .split-view {
            display: flex;
            flex: 1;
            gap: 20px;
            overflow: hidden;
            min-height: 0;
        }

        /* Left Panel: modern card with frosted glass - neutral */
        .left-panel {
            flex: 0 0 68%;
            background: rgba(255, 255, 255, 0.75);
            backdrop-filter: blur(10px);
            border-radius: 28px;
            box-shadow: 0 20px 35px -12px rgba(0, 0, 0, 0.12);
            display: flex;
            flex-direction: column;
            overflow: hidden;
            border: 1px solid rgba(255, 255, 255, 0.6);
            transition: all 0.2s;
        }

        .right-panel {
            flex: 1;
            background: rgba(255, 255, 255, 0.75);
            backdrop-filter: blur(10px);
            border-radius: 28px;
            box-shadow: 0 20px 35px -12px rgba(0, 0, 0, 0.12);
            display: flex;
            flex-direction: column;
            gap: 16px;
            padding: 18px 16px;
            overflow-y: auto;
            border: 1px solid rgba(255, 255, 255, 0.6);
        }

        /* Filter row - neutral */
        .filter-area {
            padding: 16px 20px 8px 20px;
            display: flex;
            align-items: center;
            gap: 14px;
            flex-wrap: wrap;
            border-bottom: 1px solid rgba(0, 0, 0, 0.05);
        }

        .filter-label {
            font-weight: 600;
            color: #2c3e50;
            background: rgba(0,0,0,0.04);
            padding: 4px 12px;
            border-radius: 40px;
            font-size: 12px;
        }

        #brand_name {
            flex: 1;
            padding: 9px 16px;
            border-radius: 60px;
            border: 1px solid rgba(72, 120, 160, 0.3);
            background: rgba(255,255,255,0.8);
            font-size: 12px;
            transition: 0.2s;
            outline: none;
        }

        #brand_name:focus {
            border-color: #6c757d;
            box-shadow: 0 0 0 3px rgba(108,117,125,0.2);
        }

        .btn-primary {
            background: #495057;
            border: none;
            border-radius: 60px;
            padding: 8px 22px;
            color: white;
            font-weight: 600;
            cursor: pointer;
            transition: 0.2s;
            display: flex;
            align-items: center;
            gap: 8px;
            font-size: 12px;
        }

        .btn-primary:hover {
            background: #343a40;
            transform: scale(0.98);
        }

        /* Grid Wrapper */
        .grid-wrapper {
            flex: 1;
            overflow-y: auto;
            margin: 0 12px 16px 12px;
            border-radius: 20px;
            background: rgba(255, 255, 255, 0.5);
            scrollbar-width: thin;
        }

        .item-table {
            width: 100%;
            border-collapse: separate;
            border-spacing: 0;
            font-size: 11.5px;
        }

        .item-table th {
            position: sticky;
            top: 0;
            background: #f0f2f5;
            padding: 12px 6px;
            font-weight: 700;
            color: #2c3e50;
            border-bottom: 1px solid #dee2e6;
            z-index: 10;
            backdrop-filter: blur(4px);
        }

        .item-table td {
            padding: 9px 6px;
            border-bottom: 1px solid rgba(0, 0, 0, 0.05);
            transition: background 0.15s;
        }

        .grid-row {
            cursor: pointer;
            transition: all 0.1s;
        }

        .grid-row:hover td {
            background: rgba(0, 0, 0, 0.05);
        }

        .selected-row td {
            background: #e9ecef !important;
            box-shadow: inset 0 0 0 1px #adb5bd;
        }

        .highlight-qty {
            background-color: #fff1d6;
            font-weight: 700;
        }
        .highlight-rec {
            background-color: #e0f0e8;
            font-weight: 700;
        }

        /* Ageing badges - neutralized blue tones */
        .days-green { background: #c6f7e2; color: #0b5e42; font-weight: 600; text-align: center; border-radius: 40px; }
        .days-yellow { background: #fff0c2; color: #8a6300; font-weight: 600; text-align: center; border-radius: 40px; }
        .days-indigo { background: #e9ecef; color: #495057; font-weight: 600; text-align: center; border-radius: 40px; }
        .days-red { background: #ffe0e0; color: #b12a2a; font-weight: 700; text-align: center; border-radius: 40px; animation: softPulse 1.2s infinite; }

        @keyframes softPulse {
            0% { background: #ffe0e0; }
            100% { background: #ffc6c6; }
        }

        .summary-row td {
            background: #f1f3f5;
            font-weight: 700;
            border-top: 1px solid #dee2e6;
        }

        .spacer-row td {
            padding: 6px;
            background: transparent;
        }

        /* Right cards */
        .info-card {
            background: rgba(255, 255, 255, 0.8);
            border-radius: 24px;
            padding: 14px 16px;
            box-shadow: 0 5px 12px rgba(0, 0, 0, 0.03);
            transition: all 0.2s;
            border: 1px solid rgba(255,255,255,0.7);
        }

        .stat-number {
            font-size: 26px;
            font-weight: 800;
            background: linear-gradient(135deg, #2c3e50, #4a627a);
            -webkit-background-clip: text;
            background-clip: text;
            color: transparent;
        }

        .forecast-badge {
            background: #eef3fc;
            border-radius: 40px;
            padding: 4px 14px;
            display: inline-flex;
            align-items: center;
            gap: 6px;
            font-size: 11px;
            font-weight: 500;
        }

        .btn-icon-sm {
            background: #e2edf7;
            border: none;
            border-radius: 60px;
            padding: 6px 12px;
            font-size: 11px;
            font-weight: 500;
            cursor: pointer;
            transition: 0.2s;
        }

        .btn-icon-sm:hover {
            background: #cde0ef;
        }

        /* Modal */
        .modal {
            position: fixed;
            top: 0; left: 0;
            width: 100%; height: 100%;
            background: rgba(0, 0, 0, 0.6);
            backdrop-filter: blur(6px);
            display: none;
            justify-content: center;
            align-items: center;
            z-index: 2000;
        }
        .modal-content {
            background: #fffef7;
            width: 70%;
            max-height: 75%;
            border-radius: 32px;
            overflow: hidden;
            box-shadow: 0 30px 40px rgba(0, 0, 0, 0.2);
            animation: fadeSlideUp 0.2s ease;
        }
        @keyframes fadeSlideUp {
            from { opacity: 0; transform: translateY(20px); }
            to { opacity: 1; transform: translateY(0); }
        }
        .modal-header {
            background: #f0f2f5;
            padding: 14px 20px;
            display: flex;
            justify-content: space-between;
        }
        .modal-body {
            overflow-y: auto;
            padding: 16px;
        }
        .modal-table {
            width: 100%;
            border-collapse: collapse;
            font-size: 11px;
        }
        .modal-table th, .modal-table td {
            border: 1px solid #d4e2f0;
            padding: 8px;
            text-align: left;
        }
        .negative-gp {
            color: red;
            font-weight: bold;
        }

        /* Custom scroll */
        ::-webkit-scrollbar {
            width: 6px;
            height: 6px;
        }
        ::-webkit-scrollbar-track {
            background: #e2e9f0;
            border-radius: 10px;
        }
        ::-webkit-scrollbar-thumb {
            background: #9bb7d0;
            border-radius: 10px;
        }

        #grandTotalDisplay {
            background: rgba(0, 0, 0, 0.05);
            margin: 6px 12px 12px 12px;
            padding: 10px 16px;
            border-radius: 60px;
            font-weight: 600;
            font-size: 12px;
        }
        canvas {
            max-height: 180px;
        }
        @media print {
            .top-bar, .right-panel, .filter-area, .btn-icon-sm, .modal { display: none; }
            .left-panel { width: 100%; flex: 1; }
            body { background: white; }
        }
    </style>
</head>
<body>
<div class="app-container">
    <div class="top-bar">
        <button class="glass-btn" id="printBtn"><i class="fas fa-print"></i> Print</button>
        <button class="glass-btn" id="exitBtn"><i class="fas fa-sign-out-alt"></i> Exit</button>
    </div>
    <div class="split-view">
        <!-- LEFT: Inventory Grid (Running Value column removed) -->
        <div class="left-panel">
            <div class="filter-area">
                <span class="filter-label"><i class="fas fa-tag"></i> Brand</span>
                <input type="text" id="brand_name" placeholder="Search brand..." autocomplete="off">
                <button id="refreshGrid" class="btn-primary"><i class="fas fa-sync-alt"></i> Refresh</button>
            </div>
            <div class="grid-wrapper">
                <table class="item-table" id="itemGrid">
                    <thead>
                        <tr><th>STK No</th><th>Brand</th><th>Description</th><th class="highlight-qty">Qty Hand</th><th>Date</th><th>Ref No</th><th class="highlight-rec">Rec Qty</th><th>Cost</th><th>Selling</th><th>Days</th></tr>
                    </thead>
                    <tbody id="gridBody"></tbody>
                </table>
            </div>
            <div id="grandTotalDisplay"></div>
        </div>

        <!-- RIGHT: Analytics with charts -->
        <div class="right-panel">
            <div class="info-card" style="display: flex; gap: 12px; flex-wrap: wrap; align-items: center;">
                <div style="flex:1"><i class="fas fa-chart-line"></i> <strong>Time Range</strong><br>
                    <input type="number" id="monthsRange" class="months-input" value="6" min="1" max="24" style="width:70px; border-radius:40px; border:1px solid #ccc; padding:5px;">
                </div>
                <button id="salesPopupBtn" class="btn-icon-sm"><i class="fas fa-chart-simple"></i> Sales Log</button>
                <button id="purchasePopupBtn" class="btn-icon-sm"><i class="fas fa-cart-flatbed"></i> Purchase Log</button>
            </div>
            <div class="info-card">
                <canvas id="trendChart" style="width:100%; height:190px;"></canvas>
            </div>
            <div class="info-card" style="display: flex; justify-content: space-between;">
                <div><i class="fas fa-dollar-sign"></i> <strong>Total GP (6M)</strong><br><span id="totalGP" class="stat-number">-</span></div>
                <div><i class="fas fa-chart-line"></i> <strong>Avg Monthly Sales</strong><br><span id="avgSales" class="stat-number">-</span></div>
            </div>
            <div class="info-card">
                <div><i class="fas fa-chart-line"></i> <strong>📈 3-Month Sales Forecast</strong></div>
                <div id="forecastDisplay" class="forecast-items" style="margin-top: 8px;"></div>
            </div>
            <div class="info-card" id="selectedInfo">
                <i class="fas fa-mouse-pointer"></i> Click any row to view analytics
            </div>
        </div>
    </div>
</div>

<!-- Modal -->
<div id="modalWindow" class="modal">
    <div class="modal-content">
        <div class="modal-header"><span id="modalTitle"><i class="fas fa-table"></i> Details</span><button class="glass-btn" style="padding:4px 15px;" id="closeModalBtn"><i class="fas fa-times"></i></button></div>
        <div class="modal-body"><table class="modal-table" id="modalTable"><thead id="modalHead"></thead><tbody id="modalBody"></tbody></table></div>
    </div>
</div>

<script>
let currentChart = null;
let currentStkNo = null;
let itemsGlobalMeta = {};

function loadGrid() {
    let brand = $('#brand_name').val();
    $('#gridBody').html('<tr><td colspan="10" style="text-align:center; padding:40px;"><i class="fas fa-spinner fa-pulse"></i> Loading contra-FIFO data...</td></tr>');
    $.ajax({
        url: window.location.href,
        method: 'GET',
        data: { action: 'get_items', brand_name: brand },
        dataType: 'json',
        success: function(resp) {
            let rows = resp.rows || [];
            let grand = resp.grand_totals;
            itemsGlobalMeta = resp.items_meta || {};
            let tbody = $('#gridBody');
            tbody.empty();
            if (!rows.length) {
                tbody.append('<tr><td colspan="10">✨ No stock records found with positive QTYINHAND</td></tr>');
                $('#grandTotalDisplay').html('');
                return;
            }
            
            let lastStk = '';
            let groupBuffer = [];
            let groupTotalRunning = 0, groupTotalQty = 0, groupOver90=0, groupSixty90=0;
            
            function flushGroup() {
                if (groupBuffer.length === 0) return;
                let avgCost = (groupTotalQty > 0) ? (groupTotalRunning / groupTotalQty) : 0;
                // Summary row with adjusted colspan (10 columns total)
                let summaryRow = `<tr class="summary-row">
                                    <td colspan="5" style="text-align:right;"><strong>📊 Item Summary</strong></td>
                                    <td><strong>${groupTotalQty.toFixed(2)}</strong></td>
                                    <td colspan="2"><strong>Total Val: Rs.${groupTotalRunning.toFixed(2)}</strong></td>
                                    <td><strong>Avg: Rs.${avgCost.toFixed(2)}</strong></td>
                                    <td></td>
                                </tr>`;
                tbody.append(summaryRow);
                tbody.append(`<tr style="background:#f8f9fa;"><td colspan="10"><i class="fas fa-hourglass-half"></i> 🔴 Over 90d: Rs ${groupOver90.toFixed(2)} &nbsp; 🟠 60-90d: Rs ${groupSixty90.toFixed(2)}</td></tr>`);
                tbody.append('<tr class="spacer-row"><td colspan="10"></td></tr>');
                groupTotalRunning = 0; groupTotalQty = 0; groupOver90 = 0; groupSixty90 = 0;
                groupBuffer = [];
            }
            
            $.each(rows, function(idx, row) {
                let isNewItem = (row.STK_NO !== lastStk);
                if (isNewItem && lastStk !== null) flushGroup();
                lastStk = row.STK_NO;
                groupBuffer.push(row);
                groupTotalRunning += parseFloat(row.running_value);
                groupTotalQty += parseFloat(row.REC_QTY);
                if (row.item_over90_value) groupOver90 = parseFloat(row.item_over90_value);
                if (row.item_sixty90_value) groupSixty90 = parseFloat(row.item_sixty90_value);
                
                let showMaster = (isNewItem);
                let stkCell = showMaster ? row.STK_NO : '';
                let brandCell = showMaster ? row.BRAND_NAME : '';
                let descCell = showMaster ? row.DESCRIPT : '';
                let qtyHandCell = showMaster ? parseFloat(row.QTYINHAND).toFixed(2) : '';
                
                let daysClass = 'days-green';
                let daysVal = parseInt(row.days);
                if (daysVal >= 90) daysClass = 'days-red';
                else if (daysVal >= 60) daysClass = 'days-indigo';
                else if (daysVal >= 30) daysClass = 'days-yellow';
                
                let tr = $('<tr class="grid-row" data-stk="'+escapeHtml(row.STK_NO)+'">');
                tr.append('<td>'+escapeHtml(stkCell)+'</td>');
                tr.append('<td>'+escapeHtml(brandCell)+'</td>');
                tr.append('<td>'+escapeHtml(descCell)+'</td>');
                tr.append('<td class="highlight-qty">'+qtyHandCell+'</td>');
                tr.append('<td>'+escapeHtml(row.SDATE)+'</td>');
                tr.append('<td>'+escapeHtml(row.REFNO)+'</td>');
                tr.append('<td class="highlight-rec">'+parseFloat(row.REC_QTY).toFixed(2)+'</td>');
                tr.append('<td>'+parseFloat(row.COST).toFixed(2)+'</td>');
                tr.append('<td>'+parseFloat(row.SELLING).toFixed(2)+'</td>');
                tr.append('<td class="'+daysClass+'">'+daysVal+' d</td>');
                
                tr.click(function() {
                    $('.grid-row').removeClass('selected-row');
                    tr.addClass('selected-row');
                    currentStkNo = row.STK_NO;
                    loadChart(currentStkNo);
                    let meta = itemsGlobalMeta[currentStkNo] || {};
                    $('#selectedInfo').html(`<i class="fas fa-cube"></i> <strong>${row.BRAND_NAME} (${row.STK_NO})</strong><br>
                        <small>📦 Stock Value: Rs ${(meta.total_stock_value || 0).toFixed(2)} | Avg Cost: Rs ${(meta.avg_cost || 0).toFixed(2)}</small>`);
                });
                tbody.append(tr);
            });
            flushGroup();
            
            if (grand) {
                $('#grandTotalDisplay').html(`<i class="fas fa-chart-pie"></i> <strong>Grand Totals:</strong> Inventory Value: Rs ${grand.total_stock_value.toFixed(2)} &nbsp;| 🔴 Over 90d: Rs ${grand.over90_value.toFixed(2)} &nbsp;| 🟠 60-90d: Rs ${grand.sixty90_value.toFixed(2)}`);
            }
            if (rows.length > 0 && !currentStkNo) $('.grid-row:first').click();
        },
        error: function() { $('#gridBody').html('<tr><td colspan="10">⚠️ Failed to load data</td></tr>'); }
    });
}

function escapeHtml(str) { if(!str) return ''; return String(str).replace(/[&<>]/g, function(m){if(m==='&') return '&amp;'; if(m==='<') return '&lt;'; if(m==='>') return '&gt;'; return m;}); }

function loadChart(stk) {
    $.ajax({
        url: window.location.href,
        method: 'GET',
        data: { action: 'get_chart_data', stk_no: stk },
        dataType: 'json',
        success: function(data) {
            // 1. ADDED THIS CHECK: Catch the PHP error before processing
            if (data.error) {
                console.error("Chart Data Error:", data.error);
                $('#forecastDisplay').html(`<span style="color:red;"><i class="fas fa-exclamation-triangle"></i> ${data.error}</span>`);
                $('#totalGP').text('Error');
                $('#avgSales').text('Error');
                if (currentChart) currentChart.destroy();
                return; // Stop running the rest of the function
            }

            // 2. Existing code continues safely...
            let totalGP = data.gp.reduce((a,b)=>a+b,0);
            $('#totalGP').text('Rs.'+totalGP.toFixed(2));
            $('#avgSales').text(data.avg_sales.toFixed(2));
            
            let fcHtml = '';
            for (let i=0; i<data.forecast_labels.length; i++) {
                fcHtml += `<span class="forecast-badge"><i class="far fa-calendar-alt"></i> ${data.forecast_labels[i]} : ${data.forecast_values[i]} u</span>`;
            }
            $('#forecastDisplay').html(fcHtml || 'No forecast data');
            
            if (currentChart) currentChart.destroy();
            const ctx = document.getElementById('trendChart').getContext('2d');
            currentChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: data.labels,
                    datasets: [
                        { label: 'Sales Qty', data: data.sales, borderColor: '#2c7fb8', backgroundColor: 'rgba(44,127,184,0.1)', fill: true, tension: 0.4, pointRadius: 3, pointBackgroundColor: '#2c7fb8' },
                        { label: 'Purchase Returns', data: data.purchase, borderColor: '#33a02c', backgroundColor: 'rgba(51,160,44,0.08)', fill: true, tension: 0.4, borderDash: [5,5] },
                        { label: 'Gross Profit ($)', data: data.gp, borderColor: '#d95f02', backgroundColor: 'rgba(217,95,2,0.05)', fill: true, tension: 0.4, yAxisID: 'y1' }
                    ]
                },
                options: {
                    responsive: true, maintainAspectRatio: true,
                    animation: { duration: 1000, easing: 'easeOutQuart' },
                    plugins: { legend: { position: 'top', labels: { font: { size: 9 } } }, tooltip: { mode: 'index' } },
                    scales: { y: { title: { display: true, text: 'Quantity', font: { size: 9 } } }, y1: { position: 'right', title: { text: 'GP ($)' } } }
                }
            });
        },
        error: function(xhr, status, error) {
            console.error("AJAX Request Failed:", error);
        }
    });
}

// Sales Popup with GP negative red coloring
function showSalesPopup() { 
    if (!currentStkNo) { alert('Select an item first'); return; } 
    let months = $('#monthsRange').val();
    $.ajax({ url: window.location.href, method: 'GET', data: { action: 'popup_sales', stk_no: currentStkNo, months: months }, dataType: 'json',
        success: function(data) { 
            $('#modalTitle').html(`<i class="fas fa-chart-line"></i> Sales (Last ${months} months)`);
            let thead = `<tr><th>Date</th><th>RefNo</th><th>Dept</th><th>Qty</th><th>Cost</th><th>Selling</th><th>GP</th></tr>`;
            let tbody = '', totQty=0, totGP=0;
            $.each(data, function(i, r) { 
                let qty = parseFloat(r.qty), gp = parseFloat(r.gp); 
                totQty += qty; totGP += gp;
                let gpClass = (gp < 0) ? 'class="negative-gp"' : '';
                tbody += `<tr>
                            <td>${r.SDATE}</td>
                            <td>${r.REFNO}</td>
                            <td>${r.DEPARTMENT||'-'}</td>
                            <td>${qty.toFixed(2)}</td>
                            <td>${parseFloat(r.COST).toFixed(2)}</td>
                            <td>${parseFloat(r.SELLING).toFixed(2)}</td>
                            <td ${gpClass}>${gp.toFixed(2)}</td>
                          </tr>`;
            });
            tbody += `<tr style="background:#eef2f9;"><td colspan="3"><strong>TOTAL</strong></td><td><strong>${totQty.toFixed(2)}</strong></td><td></td><td></td><td><strong>${totGP.toFixed(2)}</strong></td></tr>`;
            $('#modalHead').html(thead); $('#modalBody').html(tbody); $('#modalWindow').show();
        }
    });
}

function showPurchasePopup() { 
    if (!currentStkNo) { alert('Select an item first'); return; } 
    let months = $('#monthsRange').val();
    $.ajax({ url: window.location.href, method: 'GET', data: { action: 'popup_purchase', stk_no: currentStkNo, months: months }, dataType: 'json',
        success: function(data) { 
            $('#modalTitle').html(`<i class="fas fa-cart-plus"></i> Purchase Records (Last ${months} months)`);
            let thead = `<tr><th>Date</th><th>RefNo</th><th>Rec Qty</th><th>Cost</th><th>Selling</th><th>Subtotal</th></tr>`;
            let tbody = '', totQty=0, totSub=0;
            $.each(data, function(i, r) { 
                let qty = parseFloat(r.REC_QTY), sub = parseFloat(r.subtot); 
                totQty += qty; totSub += sub;
                tbody += `<tr>
                            <td>${r.SDATE}</td>
                            <td>${r.REFNO}</td>
                            <td>${qty.toFixed(2)}</td>
                            <td>${parseFloat(r.COST).toFixed(2)}</td>
                            <td>${parseFloat(r.SELLING).toFixed(2)}</td>
                            <td>${sub.toFixed(2)}</td>
                          </tr>`;
            });
            tbody += `<tr style="background:#eef2f9;"><td colspan="2"><strong>TOTAL</strong></td><td><strong>${totQty.toFixed(2)}</strong></td><td></td><td></td><td><strong>${totSub.toFixed(2)}</strong></td></tr>`;
            $('#modalHead').html(thead); $('#modalBody').html(tbody); $('#modalWindow').show();
        }
    });
}

$(document).ready(function() {
    loadGrid();
    $('#refreshGrid').click(function() { loadGrid(); });
    $('#brand_name').on('keypress', function(e) { if(e.which===13) loadGrid(); });
    $('#salesPopupBtn').click(showSalesPopup);
    $('#purchasePopupBtn').click(showPurchasePopup);
    $('#printBtn').click(() => window.print());
    $('#exitBtn').click(() => { if(confirm('Exit application?')) { window.close(); document.body.innerHTML='Closed'; } });
    $('#closeModalBtn').click(() => $('#modalWindow').hide());
    $(window).click(e => { if($(e.target).is('.modal')) $('#modalWindow').hide(); });
});
</script>
</body>
</html>