<?php
session_start();
header('Content-Type: application/json');
include_once('../../includes/config.php');

if (!isset($_SESSION['uid'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit();
}

try {
    // Get filter parameters
    $categoryId = isset($_GET['categoryId']) ? trim($_GET['categoryId']) : '';
    $brandId = isset($_GET['brandId']) ? trim($_GET['brandId']) : '';
    $locationId = isset($_GET['locationId']) ? trim($_GET['locationId']) : '';
    $stockStatus = isset($_GET['stockStatus']) ? trim($_GET['stockStatus']) : ''; // all, in-stock, low-stock, out-of-stock, negative
    $itemCode = isset($_GET['itemCode']) ? trim($_GET['itemCode']) : '';

    // Build WHERE conditions
    $conditions = ["i.Status = 'Active'"];
    
    if (!empty($categoryId)) {
        $conditions[] = "i.CategoryID = " . intval($categoryId);
    }
    
    if (!empty($brandId)) {
        $conditions[] = "i.BrandID = " . intval($brandId);
    }
    
    if (!empty($locationId)) {
        $conditions[] = "i.LocationID = " . intval($locationId);
    }
    
    if (!empty($itemCode)) {
        $itemCodeEsc = mysqli_real_escape_string($con, $itemCode);
        $conditions[] = "i.ItemCode = '$itemCodeEsc'";
    }
    
    // Stock status filters
    if ($stockStatus === 'out-of-stock') {
        $conditions[] = "i.CurrentStock <= 0";
    } elseif ($stockStatus === 'low-stock') {
        $conditions[] = "i.CurrentStock > 0 AND i.CurrentStock <= i.AlertQuantity";
    } elseif ($stockStatus === 'in-stock') {
        $conditions[] = "i.CurrentStock > i.AlertQuantity";
    } elseif ($stockStatus === 'negative') {
        $conditions[] = "i.CurrentStock < 0";
    }
    
    $whereClause = implode(' AND ', $conditions);

    // Main query to get items with stock information
    $query = "
        SELECT 
            i.ID,
            i.ItemCode,
            i.ItemName,
            i.Barcode,
            i.HSN,
            c.CategoryName,
            b.BrandName,
            u.UnitName,
            l.LocationName,
            r.RackName,
            s.SupplierName,
            i.OpeningStock,
            i.CurrentStock,
            i.AlertQuantity,
            i.PurchasePrice,
            i.SalesPrice,
            i.ShopRate,
            i.WholesaleRate,
            i.MRP,
            i.ProfitMarginPercent,
            i.LastUpdated,
            
            -- Calculate stock value
            (i.CurrentStock * i.PurchasePrice) AS StockValueAtCost,
            (i.CurrentStock * i.SalesPrice) AS StockValueAtSelling,
            
            -- Potential profit
            ((i.CurrentStock * i.SalesPrice) - (i.CurrentStock * i.PurchasePrice)) AS PotentialProfit,
            
            -- Stock status
            CASE 
                WHEN i.CurrentStock < 0 THEN 'Negative'
                WHEN i.CurrentStock = 0 THEN 'Out of Stock'
                WHEN i.CurrentStock <= i.AlertQuantity THEN 'Low Stock'
                ELSE 'In Stock'
            END AS StockStatus
            
        FROM tblitems i
        LEFT JOIN tblcategories c ON i.CategoryID = c.ID
        LEFT JOIN tblbrands b ON i.BrandID = b.ID
        LEFT JOIN tblunits u ON i.UnitID = u.ID
        LEFT JOIN tbllocations l ON i.LocationID = l.ID
        LEFT JOIN tblracks r ON i.RackID = r.ID
        LEFT JOIN tblsuppliers s ON i.SupplierID = s.ID
        WHERE $whereClause
        ORDER BY i.ItemName ASC
    ";

    $result = mysqli_query($con, $query);
    
    if (!$result) {
        throw new Exception('Database query failed: ' . mysqli_error($con));
    }

    $items = [];
    $summary = [
        'totalItems' => 0,
        'totalOpeningStock' => 0,
        'totalCurrentStock' => 0,
        'totalStockValueAtCost' => 0,
        'totalStockValueAtSelling' => 0,
        'totalPotentialProfit' => 0,
        'outOfStockItems' => 0,
        'lowStockItems' => 0,
        'negativeStockItems' => 0,
        'inStockItems' => 0
    ];

    while ($row = mysqli_fetch_assoc($result)) {
        $items[] = [
            'ID' => $row['ID'],
            'ItemCode' => $row['ItemCode'],
            'ItemName' => $row['ItemName'],
            'Barcode' => $row['Barcode'],
            'HSN' => $row['HSN'],
            'CategoryName' => $row['CategoryName'],
            'BrandName' => $row['BrandName'],
            'UnitName' => $row['UnitName'],
            'LocationName' => $row['LocationName'],
            'RackName' => $row['RackName'],
            'SupplierName' => $row['SupplierName'],
            'OpeningStock' => floatval($row['OpeningStock']),
            'CurrentStock' => floatval($row['CurrentStock']),
            'AlertQuantity' => floatval($row['AlertQuantity']),
            'PurchasePrice' => floatval($row['PurchasePrice']),
            'SalesPrice' => floatval($row['SalesPrice']),
            'ShopRate' => floatval($row['ShopRate']),
            'WholesaleRate' => floatval($row['WholesaleRate']),
            'MRP' => floatval($row['MRP']),
            'ProfitMarginPercent' => floatval($row['ProfitMarginPercent']),
            'StockValueAtCost' => floatval($row['StockValueAtCost']),
            'StockValueAtSelling' => floatval($row['StockValueAtSelling']),
            'PotentialProfit' => floatval($row['PotentialProfit']),
            'StockStatus' => $row['StockStatus'],
            'LastUpdated' => $row['LastUpdated']
        ];

        // Update summary
        $summary['totalItems']++;
        $summary['totalOpeningStock'] += floatval($row['OpeningStock']);
        $summary['totalCurrentStock'] += floatval($row['CurrentStock']);
        $summary['totalStockValueAtCost'] += floatval($row['StockValueAtCost']);
        $summary['totalStockValueAtSelling'] += floatval($row['StockValueAtSelling']);
        $summary['totalPotentialProfit'] += floatval($row['PotentialProfit']);

        // Count stock status
        switch ($row['StockStatus']) {
            case 'Negative':
                $summary['negativeStockItems']++;
                break;
            case 'Out of Stock':
                $summary['outOfStockItems']++;
                break;
            case 'Low Stock':
                $summary['lowStockItems']++;
                break;
            case 'In Stock':
                $summary['inStockItems']++;
                break;
        }
    }

    // Get filter info for display
    $categoryInfo = null;
    $brandInfo = null;
    $locationInfo = null;
    $itemInfo = null;

    if (!empty($categoryId)) {
        $catQuery = "SELECT CategoryName FROM tblcategories WHERE ID = " . intval($categoryId);
        $catResult = mysqli_query($con, $catQuery);
        if ($catResult && $catRow = mysqli_fetch_assoc($catResult)) {
            $categoryInfo = $catRow['CategoryName'];
        }
    }

    if (!empty($brandId)) {
        $brandQuery = "SELECT BrandName FROM tblbrands WHERE ID = " . intval($brandId);
        $brandResult = mysqli_query($con, $brandQuery);
        if ($brandResult && $brandRow = mysqli_fetch_assoc($brandResult)) {
            $brandInfo = $brandRow['BrandName'];
        }
    }

    if (!empty($locationId)) {
        $locQuery = "SELECT LocationName FROM tbllocations WHERE ID = " . intval($locationId);
        $locResult = mysqli_query($con, $locQuery);
        if ($locResult && $locRow = mysqli_fetch_assoc($locResult)) {
            $locationInfo = $locRow['LocationName'];
        }
    }

    if (!empty($itemCode)) {
        $itemCodeEsc = mysqli_real_escape_string($con, $itemCode);
        $itemQuery = "SELECT ItemName, ItemCode FROM tblitems WHERE ItemCode = '$itemCodeEsc'";
        $itemResult = mysqli_query($con, $itemQuery);
        if ($itemResult && $itemRow = mysqli_fetch_assoc($itemResult)) {
            $itemInfo = [
                'ItemName' => $itemRow['ItemName'],
                'ItemCode' => $itemRow['ItemCode']
            ];
        }
    }

    echo json_encode([
        'success' => true,
        'items' => $items,
        'summary' => $summary,
        'categoryInfo' => $categoryInfo,
        'brandInfo' => $brandInfo,
        'locationInfo' => $locationInfo,
        'itemInfo' => $itemInfo,
        'stockStatus' => $stockStatus
    ]);

} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
