<?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');
$customerId = isset($_GET['customerId']) ? trim($_GET['customerId']) : '';
$salesType = isset($_GET['salesType']) ? trim($_GET['salesType']) : '';
$itemCode = isset($_GET['itemCode']) ? trim($_GET['itemCode']) : '';
$categoryId = isset($_GET['categoryId']) ? trim($_GET['categoryId']) : '';

try {
    // Build the query for item-wise sales
    $query = "SELECT 
        sd.ProductID,
        sd.ProductCode,
        sd.ProductName,
        i.CategoryID,
        cat.CategoryName,
        i.BrandID,
        brand.BrandName,
        unit.UnitName,
        SUM(sd.Quantity) as TotalQuantity,
        AVG(sd.Rate) as AvgRate,
        MIN(sd.Rate) as MinRate,
        MAX(sd.Rate) as MaxRate,
        SUM(sd.Amount) as TotalAmount,
        SUM(sd.DiscountAmount) as TotalDiscount,
        SUM(sd.FinalTotal) as TotalSales,
        SUM(sd.ProfitAmount) as TotalProfit,
        AVG(sd.PurchaseRate) as AvgPurchaseRate,
        COUNT(DISTINCT s.ID) as BillCount,
        COUNT(DISTINCT s.CustomerID) as CustomerCount,
        MIN(s.SaleDate) as FirstSaleDate,
        MAX(s.SaleDate) as LastSaleDate
    FROM tblsales_details sd
    INNER JOIN tblsales s ON sd.SaleID = s.ID
    LEFT JOIN tblitems i ON sd.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 tblcustomers c ON s.CustomerID = c.ID
    WHERE s.SaleDate BETWEEN ? AND ?";
    
    $params = [$startDate, $endDate];
    $types = "ss";
    
    // Add customer filter
    if ($customerId) {
        $query .= " AND c.CustomerCode = ?";
        $params[] = $customerId;
        $types .= "s";
    }
    
    // Add sales type filter
    if ($salesType) {
        $query .= " AND s.SalesType = ?";
        $params[] = $salesType;
        $types .= "s";
    }
    
    // Add item code filter
    if ($itemCode) {
        $query .= " AND sd.ProductCode = ?";
        $params[] = $itemCode;
        $types .= "s";
    }
    
    // Add category filter
    if ($categoryId) {
        $query .= " AND i.CategoryID = ?";
        $params[] = $categoryId;
        $types .= "i";
    }
    
    $query .= " GROUP BY sd.ProductID, sd.ProductCode, sd.ProductName 
                ORDER BY TotalSales 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,
        'totalQuantity' => 0,
        'totalAmount' => 0,
        'totalDiscount' => 0,
        'totalSales' => 0,
        'totalProfit' => 0,
        'totalBills' => 0,
        'totalCustomers' => 0,
        'avgProfitMargin' => 0
    ];
    
    $uniqueBills = [];
    $uniqueCustomers = [];
    
    while ($row = $result->fetch_assoc()) {
        $items[] = $row;
        
        $summary['totalItems']++;
        $summary['totalQuantity'] += $row['TotalQuantity'];
        $summary['totalAmount'] += $row['TotalAmount'];
        $summary['totalDiscount'] += $row['TotalDiscount'];
        $summary['totalSales'] += $row['TotalSales'];
        $summary['totalProfit'] += $row['TotalProfit'];
    }
    
    // Get unique bill and customer counts
    $countQuery = "SELECT 
        COUNT(DISTINCT s.ID) as TotalBills,
        COUNT(DISTINCT s.CustomerID) as TotalCustomers
    FROM tblsales_details sd
    INNER JOIN tblsales s ON sd.SaleID = s.ID
    LEFT JOIN tblitems i ON sd.ProductID = i.ID
    LEFT JOIN tblcustomers c ON s.CustomerID = c.ID
    WHERE s.SaleDate BETWEEN ? AND ?";
    
    $countParams = [$startDate, $endDate];
    $countTypes = "ss";
    
    if ($customerId) {
        $countQuery .= " AND c.CustomerCode = ?";
        $countParams[] = $customerId;
        $countTypes .= "s";
    }
    
    if ($salesType) {
        $countQuery .= " AND s.SalesType = ?";
        $countParams[] = $salesType;
        $countTypes .= "s";
    }
    
    if ($itemCode) {
        $countQuery .= " AND sd.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['totalBills'] = $countRow['TotalBills'];
    $summary['totalCustomers'] = $countRow['TotalCustomers'];
    
    // Calculate average profit margin
    if ($summary['totalSales'] > 0) {
        $summary['avgProfitMargin'] = ($summary['totalProfit'] / $summary['totalSales']) * 100;
    }
    
    $countStmt->close();
    
    // Get customer info if filtered
    $customerInfo = null;
    if ($customerId) {
        $custStmt = $con->prepare("SELECT CustomerName, CustomerCode, City FROM tblcustomers WHERE CustomerCode = ?");
        $custStmt->bind_param("s", $customerId);
        $custStmt->execute();
        $custResult = $custStmt->get_result();
        if ($custResult->num_rows > 0) {
            $customerInfo = $custResult->fetch_assoc();
        }
        $custStmt->close();
    }
    
    // Get sales type description if filtered
    $salesTypeInfo = null;
    if ($salesType) {
        $typeStmt = $con->prepare("SELECT Description FROM tblsales_type_config WHERE SalesType = ?");
        $typeStmt->bind_param("s", $salesType);
        $typeStmt->execute();
        $typeResult = $typeStmt->get_result();
        if ($typeResult->num_rows > 0) {
            $typeRow = $typeResult->fetch_assoc();
            $salesTypeInfo = $typeRow['Description'];
        }
        $typeStmt->close();
    }
    
    // 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,
        'customerInfo' => $customerInfo,
        'salesTypeInfo' => $salesTypeInfo,
        'categoryInfo' => $categoryInfo,
        'itemInfo' => $itemInfo
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>
