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

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

$startDate = isset($_GET['startDate']) && $_GET['startDate'] ? $_GET['startDate'] : date('Y-m-01');
$endDate = isset($_GET['endDate']) && $_GET['endDate'] ? $_GET['endDate'] : date('Y-m-t');
$supplierCode = isset($_GET['supplierCode']) ? trim($_GET['supplierCode']) : '';
$purchaseType = isset($_GET['purchaseType']) ? trim($_GET['purchaseType']) : '';
$itemCode = isset($_GET['itemCode']) ? trim($_GET['itemCode']) : '';
$categoryId = isset($_GET['categoryId']) ? trim($_GET['categoryId']) : '';

try {
    // Build the query for item-wise purchases with stock tracking
    $query = "SELECT 
        pd.ProductID,
        pd.ProductCode,
        pd.ProductName,
        i.CategoryID,
        cat.CategoryName,
        i.BrandID,
        brand.BrandName,
        unit.UnitName,
        
        -- Purchase data
        SUM(pd.Quantity) as TotalPurchased,
        AVG(pd.FinalRate) as AvgPurchaseRate,
        MIN(pd.FinalRate) as MinPurchaseRate,
        MAX(pd.FinalRate) as MaxPurchaseRate,
        SUM(pd.FinalTotal) as TotalPurchaseValue,
        COUNT(DISTINCT p.ID) as InvoiceCount,
        MIN(p.PurchaseDate) as FirstPurchaseDate,
        MAX(p.PurchaseDate) as LastPurchaseDate,
        
        -- Calculate sold quantity for the same period
        COALESCE((
            SELECT SUM(sd.Quantity)
            FROM tblsales_details sd
            INNER JOIN tblsales s ON sd.SaleID = s.ID
            WHERE sd.ProductCode = pd.ProductCode
            AND s.SaleDate BETWEEN ? AND ?
        ), 0) as TotalSold,
        
        -- Calculate average sales rate (after discount + tax)
        COALESCE((
            SELECT AVG(sd.FinalRate)
            FROM tblsales_details sd
            INNER JOIN tblsales s ON sd.SaleID = s.ID
            WHERE sd.ProductCode = pd.ProductCode
            AND s.SaleDate BETWEEN ? AND ?
        ), 0) as AvgSalesRate,
        
        -- Calculate total sold value
        COALESCE((
            SELECT SUM(sd.FinalTotal)
            FROM tblsales_details sd
            INNER JOIN tblsales s ON sd.SaleID = s.ID
            WHERE sd.ProductCode = pd.ProductCode
            AND s.SaleDate BETWEEN ? AND ?
        ), 0) as TotalSoldValue
        
    FROM tblpurchases_details pd
    INNER JOIN tblpurchases p ON pd.PurchaseID = p.ID
    LEFT JOIN tblitems i ON pd.ProductID = i.ID
    LEFT JOIN tblcategories cat ON i.CategoryID = cat.ID
    LEFT JOIN tblbrands brand ON i.BrandID = brand.ID
    LEFT JOIN tblunits unit ON i.UnitID = unit.ID
    LEFT JOIN tblsuppliers sup ON p.SupplierID = sup.ID
    WHERE p.PurchaseDate BETWEEN ? AND ?";
    
    // Prepare parameters - need multiple copies of dates for subqueries
    $params = [$startDate, $endDate, $startDate, $endDate, $startDate, $endDate, $startDate, $endDate];
    $types = "ssssssss";
    
    // Add supplier filter
    if ($supplierCode) {
        // Check if it's numeric (ID) or alphanumeric (Code)
        if (is_numeric($supplierCode)) {
            $query .= " AND sup.ID = ?";
            $params[] = intval($supplierCode);
            $types .= "i";
        } else {
            $query .= " AND sup.SupplierCode = ?";
            $params[] = $supplierCode;
            $types .= "s";
        }
    }
    
    // Add purchase type filter
    if ($purchaseType) {
        $query .= " AND p.PurchaseType = ?";
        $params[] = $purchaseType;
        $types .= "s";
    }
    
    // Add item code filter
    if ($itemCode) {
        $query .= " AND pd.ProductCode = ?";
        $params[] = $itemCode;
        $types .= "s";
    }
    
    // Add category filter
    if ($categoryId) {
        $query .= " AND i.CategoryID = ?";
        $params[] = $categoryId;
        $types .= "i";
    }
    
    $query .= " GROUP BY pd.ProductID, pd.ProductCode, pd.ProductName 
                ORDER BY TotalPurchaseValue DESC";
    
    $stmt = $con->prepare($query);
    if (!$stmt) {
        throw new Exception("Query preparation failed: " . $con->error);
    }
    
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $items = [];
    $summary = [
        'totalItems' => 0,
        'totalPurchased' => 0,
        'totalSold' => 0,
        'totalRemaining' => 0,
        'totalPurchaseValue' => 0,
        'totalSoldValue' => 0,
        'totalStockValue' => 0,
        'totalInvoices' => 0
    ];
    
    while ($row = $result->fetch_assoc()) {
        // Calculate remaining stock
        $remaining = $row['TotalPurchased'] - $row['TotalSold'];
        $stockValue = $remaining * $row['AvgPurchaseRate'];
        
        $row['StockRemaining'] = $remaining;
        $row['StockValue'] = $stockValue;
        
        $items[] = $row;
        
        $summary['totalItems']++;
        $summary['totalPurchased'] += $row['TotalPurchased'];
        $summary['totalSold'] += $row['TotalSold'];
        $summary['totalRemaining'] += $remaining;
        $summary['totalPurchaseValue'] += $row['TotalPurchaseValue'];
        $summary['totalSoldValue'] += $row['TotalSoldValue'];
        $summary['totalStockValue'] += $stockValue;
    }
    
    // Get unique invoice count
    $countQuery = "SELECT 
        COUNT(DISTINCT p.ID) as TotalInvoices
    FROM tblpurchases_details pd
    INNER JOIN tblpurchases p ON pd.PurchaseID = p.ID
    LEFT JOIN tblitems i ON pd.ProductID = i.ID
    LEFT JOIN tblsuppliers sup ON p.SupplierID = sup.ID
    WHERE p.PurchaseDate BETWEEN ? AND ?";
    
    $countParams = [$startDate, $endDate];
    $countTypes = "ss";
    
    if ($supplierCode) {
        // Check if it's numeric (ID) or alphanumeric (Code)
        if (is_numeric($supplierCode)) {
            $countQuery .= " AND sup.ID = ?";
            $countParams[] = intval($supplierCode);
            $countTypes .= "i";
        } else {
            $countQuery .= " AND sup.SupplierCode = ?";
            $countParams[] = $supplierCode;
            $countTypes .= "s";
        }
    }
    
    if ($purchaseType) {
        $countQuery .= " AND p.PurchaseType = ?";
        $countParams[] = $purchaseType;
        $countTypes .= "s";
    }
    
    if ($itemCode) {
        $countQuery .= " AND pd.ProductCode = ?";
        $countParams[] = $itemCode;
        $countTypes .= "s";
    }
    
    if ($categoryId) {
        $countQuery .= " AND i.CategoryID = ?";
        $countParams[] = $categoryId;
        $countTypes .= "i";
    }
    
    $countStmt = $con->prepare($countQuery);
    $countStmt->bind_param($countTypes, ...$countParams);
    $countStmt->execute();
    $countResult = $countStmt->get_result();
    $countRow = $countResult->fetch_assoc();
    
    $summary['totalInvoices'] = $countRow['TotalInvoices'];
    $countStmt->close();
    
    // Get supplier info if filtered
    $supplierInfo = null;
    if ($supplierCode) {
        if (is_numeric($supplierCode)) {
            $supStmt = $con->prepare("SELECT SupplierName, SupplierCode FROM tblsuppliers WHERE ID = ?");
            $supStmt->bind_param("i", $supplierCode);
        } else {
            $supStmt = $con->prepare("SELECT SupplierName, SupplierCode FROM tblsuppliers WHERE SupplierCode = ?");
            $supStmt->bind_param("s", $supplierCode);
        }
        $supStmt->execute();
        $supResult = $supStmt->get_result();
        if ($supResult->num_rows > 0) {
            $supplierInfo = $supResult->fetch_assoc();
        }
        $supStmt->close();
    }
    
    // Get purchase type description if filtered
    $purchaseTypeInfo = null;
    if ($purchaseType) {
        $purchaseTypeInfo = $purchaseType;
    }
    
    // Get category info if filtered
    $categoryInfo = null;
    if ($categoryId) {
        $catStmt = $con->prepare("SELECT CategoryName FROM tblcategories WHERE ID = ?");
        $catStmt->bind_param("i", $categoryId);
        $catStmt->execute();
        $catResult = $catStmt->get_result();
        if ($catResult->num_rows > 0) {
            $catRow = $catResult->fetch_assoc();
            $categoryInfo = $catRow['CategoryName'];
        }
        $catStmt->close();
    }
    
    // Get item info if filtered
    $itemInfo = null;
    if ($itemCode) {
        $itemStmt = $con->prepare("SELECT ItemName, ItemCode FROM tblitems WHERE ItemCode = ?");
        $itemStmt->bind_param("s", $itemCode);
        $itemStmt->execute();
        $itemResult = $itemStmt->get_result();
        if ($itemResult->num_rows > 0) {
            $itemInfo = $itemResult->fetch_assoc();
        }
        $itemStmt->close();
    }
    
    $stmt->close();
    
    echo json_encode([
        'success' => true,
        'items' => $items,
        'summary' => $summary,
        'startDate' => $startDate,
        'endDate' => $endDate,
        'supplierInfo' => $supplierInfo,
        'purchaseTypeInfo' => $purchaseTypeInfo,
        'categoryInfo' => $categoryInfo,
        'itemInfo' => $itemInfo
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>
