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

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

$startDate = isset($_GET['startDate']) ? $_GET['startDate'] : date('Y-m-01');
$endDate = isset($_GET['endDate']) ? $_GET['endDate'] : date('Y-m-d');
$transactionType = isset($_GET['transactionType']) ? $_GET['transactionType'] : '';
$paymentMode = isset($_GET['paymentMode']) ? $_GET['paymentMode'] : '';

try {
    // Initialize arrays for different transaction types
    $transactions = [];
    $summary = [
        'totalCashIn' => 0,
        'totalCashOut' => 0,
        'totalUpiIn' => 0,
        'totalUpiOut' => 0,
        'netCashFlow' => 0,
        'netUpiFlow' => 0,
        'netTotalFlow' => 0,
        'salesCash' => 0,
        'salesUpi' => 0,
        'customerPaymentsCash' => 0,
        'customerPaymentsUpi' => 0,
        'purchasesCash' => 0,
        'purchasesUpi' => 0,
        'supplierPaymentsCash' => 0,
        'supplierPaymentsUpi' => 0,
        'expensesCash' => 0,
        'expensesUpi' => 0,
        'totalTransactions' => 0
    ];

    // 1. SALES TRANSACTIONS (Cash In)
    $salesQuery = "
        SELECT 
            s.ID,
            s.InvoiceNumber,
            s.SaleDate AS TransactionDate,
            s.SalesType,
            c.CustomerName,
            c.CustomerID AS CustomerCode,
            s.FinalAmount,
            s.CashAmount,
            s.UpiAmount,
            s.CreditAmount,
            s.PaymentMode,
            s.PaymentStatus,
            'SALES' AS TransactionType,
            'IN' AS FlowType
        FROM tblsales s
        LEFT JOIN tblcustomers c ON s.CustomerID = c.ID
        WHERE s.SaleDate BETWEEN ? AND ?
        AND s.SalesType NOT LIKE '%-RETURN'
    ";
    
    if ($transactionType && $transactionType === 'SALES') {
        $salesQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'SALES') {
        $salesQuery .= " AND 1=0 ";
    }
    
    $salesQuery .= " ORDER BY s.SaleDate DESC, s.ID DESC";
    
    $salesStmt = mysqli_prepare($con, $salesQuery);
    mysqli_stmt_bind_param($salesStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($salesStmt);
    $salesResult = mysqli_stmt_get_result($salesStmt);
    
    while ($row = mysqli_fetch_assoc($salesResult)) {
        $cashAmount = floatval($row['CashAmount']);
        $upiAmount = floatval($row['UpiAmount']);
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => $row['InvoiceNumber'],
            'Party' => $row['CustomerName'] ?: 'Walk-in Customer',
            'PartyCode' => $row['CustomerCode'] ?: '-',
            'Description' => 'Sales - ' . $row['SalesType'],
            'CashIn' => $cashAmount,
            'CashOut' => 0,
            'UpiIn' => $upiAmount,
            'UpiOut' => 0,
            'TotalAmount' => $cashAmount + $upiAmount,
            'PaymentMode' => $row['PaymentMode'],
            'PaymentStatus' => $row['PaymentStatus']
        ];
        
        $summary['salesCash'] += $cashAmount;
        $summary['salesUpi'] += $upiAmount;
        $summary['totalCashIn'] += $cashAmount;
        $summary['totalUpiIn'] += $upiAmount;
    }

    // 2. SALES RETURNS (Cash Out)
    $salesReturnQuery = "
        SELECT 
            s.ID,
            s.InvoiceNumber,
            s.SaleDate AS TransactionDate,
            s.SalesType,
            c.CustomerName,
            c.CustomerID AS CustomerCode,
            s.FinalAmount,
            s.CashAmount,
            s.UpiAmount,
            s.CreditAmount,
            s.PaymentMode,
            s.PaymentStatus,
            'SALES-RETURN' AS TransactionType,
            'OUT' AS FlowType
        FROM tblsales s
        LEFT JOIN tblcustomers c ON s.CustomerID = c.ID
        WHERE s.SaleDate BETWEEN ? AND ?
        AND s.SalesType LIKE '%-RETURN'
    ";
    
    if ($transactionType && $transactionType === 'SALES-RETURN') {
        $salesReturnQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'SALES-RETURN') {
        $salesReturnQuery .= " AND 1=0 ";
    }
    
    $salesReturnQuery .= " ORDER BY s.SaleDate DESC, s.ID DESC";
    
    $salesReturnStmt = mysqli_prepare($con, $salesReturnQuery);
    mysqli_stmt_bind_param($salesReturnStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($salesReturnStmt);
    $salesReturnResult = mysqli_stmt_get_result($salesReturnStmt);
    
    while ($row = mysqli_fetch_assoc($salesReturnResult)) {
        $cashAmount = floatval($row['CashAmount']);
        $upiAmount = floatval($row['UpiAmount']);
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => $row['InvoiceNumber'],
            'Party' => $row['CustomerName'] ?: 'Walk-in Customer',
            'PartyCode' => $row['CustomerCode'] ?: '-',
            'Description' => 'Sales Return - ' . $row['SalesType'],
            'CashIn' => 0,
            'CashOut' => $cashAmount,
            'UpiIn' => 0,
            'UpiOut' => $upiAmount,
            'TotalAmount' => $cashAmount + $upiAmount,
            'PaymentMode' => $row['PaymentMode'],
            'PaymentStatus' => $row['PaymentStatus']
        ];
        
        $summary['totalCashOut'] += $cashAmount;
        $summary['totalUpiOut'] += $upiAmount;
    }

    // 3. CUSTOMER PAYMENTS (Cash In - Credit collection)
    $customerPaymentsQuery = "
        SELECT 
            ca.ID,
            ca.InvoiceNumber,
            ca.TransactionDate,
            ca.TransactionType,
            c.CustomerName,
            c.CustomerID AS CustomerCode,
            ca.Amount,
            ca.Description,
            ca.PaymentMode,
            ca.ReferenceNumber,
            'CUSTOMER-PAYMENT' AS TransactionType,
            'IN' AS FlowType
        FROM tblcustomer_accounts ca
        LEFT JOIN tblcustomers c ON ca.CustomerID = c.ID
        WHERE ca.TransactionDate BETWEEN ? AND ?
        AND ca.TransactionType = 'Credit'
        AND ca.InvoiceNumber LIKE 'PAY-%'
    ";
    
    if ($transactionType && $transactionType === 'CUSTOMER-PAYMENT') {
        $customerPaymentsQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'CUSTOMER-PAYMENT') {
        $customerPaymentsQuery .= " AND 1=0 ";
    }
    
    $customerPaymentsQuery .= " ORDER BY ca.TransactionDate DESC, ca.ID DESC";
    
    $custPayStmt = mysqli_prepare($con, $customerPaymentsQuery);
    mysqli_stmt_bind_param($custPayStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($custPayStmt);
    $custPayResult = mysqli_stmt_get_result($custPayStmt);
    
    while ($row = mysqli_fetch_assoc($custPayResult)) {
        $amount = floatval($row['Amount']);
        $isCash = stripos($row['PaymentMode'], 'Cash') !== false;
        $isUpi = stripos($row['PaymentMode'], 'UPI') !== false;
        
        $cashAmount = 0;
        $upiAmount = 0;
        
        if ($isCash && $isUpi) {
            // Split equally if both mentioned (or parse from PaymentMode if formatted)
            $cashAmount = $amount / 2;
            $upiAmount = $amount / 2;
        } elseif ($isCash) {
            $cashAmount = $amount;
        } elseif ($isUpi) {
            $upiAmount = $amount;
        } else {
            // Default to cash if not specified
            $cashAmount = $amount;
        }
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => $row['InvoiceNumber'],
            'Party' => $row['CustomerName'] ?: 'Unknown',
            'PartyCode' => $row['CustomerCode'] ?: '-',
            'Description' => $row['Description'] ?: 'Customer Payment',
            'CashIn' => $cashAmount,
            'CashOut' => 0,
            'UpiIn' => $upiAmount,
            'UpiOut' => 0,
            'TotalAmount' => $amount,
            'PaymentMode' => $row['PaymentMode'],
            'PaymentStatus' => 'Paid'
        ];
        
        $summary['customerPaymentsCash'] += $cashAmount;
        $summary['customerPaymentsUpi'] += $upiAmount;
        $summary['totalCashIn'] += $cashAmount;
        $summary['totalUpiIn'] += $upiAmount;
    }

    // 4. PURCHASES (Cash Out)
    $purchasesQuery = "
        SELECT 
            p.ID,
            p.InvoiceNumber,
            p.PurchaseDate AS TransactionDate,
            p.PurchaseType,
            s.SupplierName,
            s.SupplierID AS SupplierCode,
            p.FinalAmount,
            p.CashAmount,
            p.UpiAmount,
            p.CreditAmount,
            p.PaymentMode,
            p.PaymentStatus,
            'PURCHASE' AS TransactionType,
            'OUT' AS FlowType
        FROM tblpurchases p
        LEFT JOIN tblsuppliers s ON p.SupplierID = s.ID
        WHERE p.PurchaseDate BETWEEN ? AND ?
        AND p.PurchaseType NOT LIKE '%-RETURN'
    ";
    
    if ($transactionType && $transactionType === 'PURCHASE') {
        $purchasesQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'PURCHASE') {
        $purchasesQuery .= " AND 1=0 ";
    }
    
    $purchasesQuery .= " ORDER BY p.PurchaseDate DESC, p.ID DESC";
    
    $purchaseStmt = mysqli_prepare($con, $purchasesQuery);
    mysqli_stmt_bind_param($purchaseStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($purchaseStmt);
    $purchaseResult = mysqli_stmt_get_result($purchaseStmt);
    
    while ($row = mysqli_fetch_assoc($purchaseResult)) {
        $cashAmount = floatval($row['CashAmount']);
        $upiAmount = floatval($row['UpiAmount']);
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => $row['InvoiceNumber'],
            'Party' => $row['SupplierName'] ?: 'Unknown Supplier',
            'PartyCode' => $row['SupplierCode'] ?: '-',
            'Description' => 'Purchase - ' . $row['PurchaseType'],
            'CashIn' => 0,
            'CashOut' => $cashAmount,
            'UpiIn' => 0,
            'UpiOut' => $upiAmount,
            'TotalAmount' => $cashAmount + $upiAmount,
            'PaymentMode' => $row['PaymentMode'],
            'PaymentStatus' => $row['PaymentStatus']
        ];
        
        $summary['purchasesCash'] += $cashAmount;
        $summary['purchasesUpi'] += $upiAmount;
        $summary['totalCashOut'] += $cashAmount;
        $summary['totalUpiOut'] += $upiAmount;
    }

    // 5. PURCHASE RETURNS (Cash In)
    $purchaseReturnQuery = "
        SELECT 
            p.ID,
            p.InvoiceNumber,
            p.PurchaseDate AS TransactionDate,
            p.PurchaseType,
            s.SupplierName,
            s.SupplierID AS SupplierCode,
            p.FinalAmount,
            p.CashAmount,
            p.UpiAmount,
            p.CreditAmount,
            p.PaymentMode,
            p.PaymentStatus,
            'PURCHASE-RETURN' AS TransactionType,
            'IN' AS FlowType
        FROM tblpurchases p
        LEFT JOIN tblsuppliers s ON p.SupplierID = s.ID
        WHERE p.PurchaseDate BETWEEN ? AND ?
        AND p.PurchaseType LIKE '%-RETURN'
    ";
    
    if ($transactionType && $transactionType === 'PURCHASE-RETURN') {
        $purchaseReturnQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'PURCHASE-RETURN') {
        $purchaseReturnQuery .= " AND 1=0 ";
    }
    
    $purchaseReturnQuery .= " ORDER BY p.PurchaseDate DESC, p.ID DESC";
    
    $purchaseReturnStmt = mysqli_prepare($con, $purchaseReturnQuery);
    mysqli_stmt_bind_param($purchaseReturnStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($purchaseReturnStmt);
    $purchaseReturnResult = mysqli_stmt_get_result($purchaseReturnStmt);
    
    while ($row = mysqli_fetch_assoc($purchaseReturnResult)) {
        $cashAmount = floatval($row['CashAmount']);
        $upiAmount = floatval($row['UpiAmount']);
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => $row['InvoiceNumber'],
            'Party' => $row['SupplierName'] ?: 'Unknown Supplier',
            'PartyCode' => $row['SupplierCode'] ?: '-',
            'Description' => 'Purchase Return - ' . $row['PurchaseType'],
            'CashIn' => $cashAmount,
            'CashOut' => 0,
            'UpiIn' => $upiAmount,
            'UpiOut' => 0,
            'TotalAmount' => $cashAmount + $upiAmount,
            'PaymentMode' => $row['PaymentMode'],
            'PaymentStatus' => $row['PaymentStatus']
        ];
        
        $summary['totalCashIn'] += $cashAmount;
        $summary['totalUpiIn'] += $upiAmount;
    }

    // 6. SUPPLIER PAYMENTS (Cash Out)
    $supplierPaymentsQuery = "
        SELECT 
            sa.ID,
            sa.InvoiceNumber,
            sa.TransactionDate,
            sa.TransactionType,
            s.SupplierName,
            s.SupplierID AS SupplierCode,
            sa.Amount,
            sa.Description,
            sa.PaymentMode,
            sa.ReferenceNumber,
            'SUPPLIER-PAYMENT' AS TransactionType,
            'OUT' AS FlowType
        FROM tblsupplier_accounts sa
        LEFT JOIN tblsuppliers s ON sa.SupplierID = s.ID
        WHERE sa.TransactionDate BETWEEN ? AND ?
        AND sa.TransactionType = 'Debit'
        AND sa.InvoiceNumber LIKE 'PAY-%'
    ";
    
    if ($transactionType && $transactionType === 'SUPPLIER-PAYMENT') {
        $supplierPaymentsQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'SUPPLIER-PAYMENT') {
        $supplierPaymentsQuery .= " AND 1=0 ";
    }
    
    $supplierPaymentsQuery .= " ORDER BY sa.TransactionDate DESC, sa.ID DESC";
    
    $suppPayStmt = mysqli_prepare($con, $supplierPaymentsQuery);
    mysqli_stmt_bind_param($suppPayStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($suppPayStmt);
    $suppPayResult = mysqli_stmt_get_result($suppPayStmt);
    
    while ($row = mysqli_fetch_assoc($suppPayResult)) {
        $amount = floatval($row['Amount']);
        $isCash = stripos($row['PaymentMode'], 'Cash') !== false;
        $isUpi = stripos($row['PaymentMode'], 'UPI') !== false;
        
        $cashAmount = 0;
        $upiAmount = 0;
        
        if ($isCash && $isUpi) {
            $cashAmount = $amount / 2;
            $upiAmount = $amount / 2;
        } elseif ($isCash) {
            $cashAmount = $amount;
        } elseif ($isUpi) {
            $upiAmount = $amount;
        } else {
            $cashAmount = $amount;
        }
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => $row['InvoiceNumber'],
            'Party' => $row['SupplierName'] ?: 'Unknown',
            'PartyCode' => $row['SupplierCode'] ?: '-',
            'Description' => $row['Description'] ?: 'Supplier Payment',
            'CashIn' => 0,
            'CashOut' => $cashAmount,
            'UpiIn' => 0,
            'UpiOut' => $upiAmount,
            'TotalAmount' => $amount,
            'PaymentMode' => $row['PaymentMode'],
            'PaymentStatus' => 'Paid'
        ];
        
        $summary['supplierPaymentsCash'] += $cashAmount;
        $summary['supplierPaymentsUpi'] += $upiAmount;
        $summary['totalCashOut'] += $cashAmount;
        $summary['totalUpiOut'] += $upiAmount;
    }

    // 7. EXPENSES (Cash Out)
    $expensesQuery = "
        SELECT 
            e.ID,
            e.ExpenseDate AS TransactionDate,
            e.ExpenseCategory,
            e.ExpenseDescription,
            e.Amount,
            e.PaymentMode,
            e.Reference,
            'EXPENSE' AS TransactionType,
            'OUT' AS FlowType
        FROM tblexpenses e
        WHERE e.ExpenseDate BETWEEN ? AND ?
    ";
    
    if ($transactionType && $transactionType === 'EXPENSE') {
        $expensesQuery .= " AND 1=1 ";
    } elseif ($transactionType && $transactionType !== 'EXPENSE') {
        $expensesQuery .= " AND 1=0 ";
    }
    
    $expensesQuery .= " ORDER BY e.ExpenseDate DESC, e.ID DESC";
    
    $expenseStmt = mysqli_prepare($con, $expensesQuery);
    mysqli_stmt_bind_param($expenseStmt, 'ss', $startDate, $endDate);
    mysqli_stmt_execute($expenseStmt);
    $expenseResult = mysqli_stmt_get_result($expenseStmt);
    
    while ($row = mysqli_fetch_assoc($expenseResult)) {
        $amount = floatval($row['Amount']);
        $isCash = stripos($row['PaymentMode'], 'Cash') !== false || empty($row['PaymentMode']);
        $isUpi = stripos($row['PaymentMode'], 'UPI') !== false;
        
        $cashAmount = 0;
        $upiAmount = 0;
        
        if ($isCash && $isUpi) {
            $cashAmount = $amount / 2;
            $upiAmount = $amount / 2;
        } elseif ($isCash) {
            $cashAmount = $amount;
        } elseif ($isUpi) {
            $upiAmount = $amount;
        } else {
            $cashAmount = $amount;
        }
        
        if ($paymentMode === 'Cash' && $cashAmount <= 0) continue;
        if ($paymentMode === 'UPI' && $upiAmount <= 0) continue;
        
        $transactions[] = [
            'TransactionDate' => $row['TransactionDate'],
            'TransactionType' => $row['TransactionType'],
            'FlowType' => $row['FlowType'],
            'Reference' => 'EXP-' . $row['ID'],
            'Party' => $row['ExpenseCategory'],
            'PartyCode' => '-',
            'Description' => $row['ExpenseDescription'] ?: $row['ExpenseCategory'],
            'CashIn' => 0,
            'CashOut' => $cashAmount,
            'UpiIn' => 0,
            'UpiOut' => $upiAmount,
            'TotalAmount' => $amount,
            'PaymentMode' => $row['PaymentMode'] ?: 'Cash',
            'PaymentStatus' => 'Paid'
        ];
        
        $summary['expensesCash'] += $cashAmount;
        $summary['expensesUpi'] += $upiAmount;
        $summary['totalCashOut'] += $cashAmount;
        $summary['totalUpiOut'] += $upiAmount;
    }

    // Sort all transactions by date (newest first)
    usort($transactions, function($a, $b) {
        return strtotime($b['TransactionDate']) - strtotime($a['TransactionDate']);
    });

    // Calculate net flows
    $summary['netCashFlow'] = $summary['totalCashIn'] - $summary['totalCashOut'];
    $summary['netUpiFlow'] = $summary['totalUpiIn'] - $summary['totalUpiOut'];
    $summary['netTotalFlow'] = $summary['netCashFlow'] + $summary['netUpiFlow'];
    $summary['totalTransactions'] = count($transactions);

    echo json_encode([
        'success' => true,
        'transactions' => $transactions,
        'summary' => $summary,
        'startDate' => $startDate,
        'endDate' => $endDate
    ]);

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