<?php
session_start();
include_once('includes/config.php');
if (!isset($_SESSION['uid'])) {
    header('location:logout.php');
    exit();
}

$userId = $_SESSION['uid'];
$userType = $_SESSION['usertype'];

// Get customer ID from URL
$customerId = isset($_GET['customer_id']) ? (int)$_GET['customer_id'] : 0;

if ($customerId <= 0) {
    header('location:customer-management.php');
    exit();
}

// Get customer details
$customerQuery = "SELECT * FROM tblcustomers WHERE ID = $customerId AND IsActive = 1";
$customerResult = mysqli_query($con, $customerQuery);

if (!$customerResult || mysqli_num_rows($customerResult) == 0) {
    header('location:customer-management.php');
    exit();
}

$customer = mysqli_fetch_assoc($customerResult);

// Date filters
$dateFrom = isset($_GET['date_from']) ? mysqli_real_escape_string($con, $_GET['date_from']) : '';
$dateTo = isset($_GET['date_to']) ? mysqli_real_escape_string($con, $_GET['date_to']) : '';

$whereClause = "WHERE p.CustomerID = $customerId AND p.IsActive = 1";
if (!empty($dateFrom)) {
    $whereClause .= " AND p.TransactionDate >= '$dateFrom'";
}
if (!empty($dateTo)) {
    $whereClause .= " AND p.TransactionDate <= '$dateTo'";
}

// Get payment history
$paymentsQuery = "SELECT p.*, u1.FullName as CreatedByName 
                  FROM tblcustomerpayments p 
                  LEFT JOIN tblusers u1 ON p.CreatedBy = u1.ID 
                  $whereClause 
                  ORDER BY p.TransactionDate ASC, p.CreatedDate ASC";
$paymentsResult = mysqli_query($con, $paymentsQuery);

// Calculate balance summary
$balanceQuery = "SELECT 
    COALESCE(SUM(CASE WHEN TransactionType = 'Credit' AND Status = 'Completed' THEN Amount ELSE 0 END), 0) as TotalCredits,
    COALESCE(SUM(CASE WHEN TransactionType = 'Debit' AND Status = 'Completed' THEN Amount ELSE 0 END), 0) as TotalDebits,
    COALESCE(SUM(CASE WHEN TransactionType = 'Credit' AND Status = 'Pending' THEN Amount ELSE 0 END), 0) as PendingCredits,
    COALESCE(SUM(CASE WHEN TransactionType = 'Debit' AND Status = 'Pending' THEN Amount ELSE 0 END), 0) as PendingDebits,
    COUNT(*) as TotalTransactions,
    MIN(TransactionDate) as FirstTransaction,
    MAX(TransactionDate) as LastTransaction
    FROM tblcustomerpayments 
    WHERE CustomerID = $customerId AND IsActive = 1";
$balanceResult = mysqli_query($con, $balanceQuery);
$balanceData = mysqli_fetch_assoc($balanceResult);

$currentBalance = $balanceData['TotalCredits'] - $balanceData['TotalDebits'];
$pendingBalance = $balanceData['PendingCredits'] - $balanceData['PendingDebits'];
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Customer Ledger - <?php echo htmlspecialchars($customer['CustomerName']); ?> | Mariyappa Nadar Ricemill</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link href="vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
    <link href="css/sb-admin-2.min.css" rel="stylesheet">
    <style>
        .balance-positive { color: #1cc88a; font-weight: bold; }
        .balance-negative { color: #e74a3b; font-weight: bold; }
        .balance-zero { color: #6c757d; font-weight: bold; }
        .amount-credit { color: #1cc88a; font-weight: bold; }
        .amount-debit { color: #e74a3b; font-weight: bold; }
        .running-balance { font-weight: bold; }
        .customer-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 10px;
            margin-bottom: 20px;
        }
        .ledger-table {
            font-size: 0.9rem;
        }
        .ledger-table th {
            background-color: #f8f9fc;
            border-top: none;
        }
        .transaction-row {
            transition: background-color 0.2s;
        }
        .transaction-row:hover {
            background-color: #f8f9fc;
        }
        .voucher-preview {
            max-width: 40px;
            max-height: 30px;
            cursor: pointer;
            border-radius: 3px;
        }
        @media print {
            .no-print { display: none !important; }
            .card { border: none !important; box-shadow: none !important; }
            body { font-size: 12px; }
        }
    </style>
</head>
<body id="page-top">
    <div id="wrapper">
        <?php include_once('includes/sidebar.php');?>
        <div id="content-wrapper" class="d-flex flex-column">
            <div id="content">
                <?php include_once('includes/topbar.php');?>
                
                <!-- Begin Page Content -->
                <div class="container-fluid">
                    
                    <!-- Page Heading -->
                    <div class="d-sm-flex align-items-center justify-content-between mb-4 no-print">
                        <h1 class="h3 mb-0 text-gray-800">
                            <i class="fas fa-file-invoice-dollar text-primary mr-2"></i>
                            Customer Ledger
                        </h1>
                        <div>
                            <a href="customer-payments.php?customer=<?php echo $customerId; ?>" class="btn btn-info mr-2">
                                <i class="fas fa-plus mr-2"></i>Add Payment
                            </a>
                            <button onclick="window.print()" class="btn btn-secondary mr-2">
                                <i class="fas fa-print mr-2"></i>Print
                            </button>
                            <a href="customer-management.php" class="btn btn-primary">
                                <i class="fas fa-arrow-left mr-2"></i>Back to Customers
                            </a>
                        </div>
                    </div>

                    <!-- Customer Information Header -->
                    <div class="customer-header p-4 mb-4">
                        <div class="row align-items-center">
                            <div class="col-md-8">
                                <h2 class="mb-1"><?php echo htmlspecialchars($customer['CustomerName']); ?></h2>
                                <div class="row">
                                    <div class="col-md-6">
                                        <p class="mb-1"><i class="fas fa-phone mr-2"></i><?php echo $customer['Mobile']; ?></p>
                                        <p class="mb-1"><i class="fas fa-map-marker-alt mr-2"></i><?php echo htmlspecialchars($customer['Area']); ?></p>
                                    </div>
                                    <div class="col-md-6">
                                        <p class="mb-1"><i class="fas fa-tag mr-2"></i><?php echo $customer['CustomerType']; ?> Customer</p>
                                        <p class="mb-0"><i class="fas fa-calendar mr-2"></i>Customer since <?php echo date('M Y', strtotime($customer['CreatedDate'])); ?></p>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-4 text-md-right">
                                <div class="bg-white bg-opacity-20 p-3 rounded">
                                    <h6 class="mb-1">Current Balance</h6>
                                    <h3 class="mb-0 <?php echo $currentBalance > 0 ? 'text-light' : ($currentBalance < 0 ? 'text-warning' : 'text-light'); ?>">
                                        ₹<?php echo number_format($currentBalance, 2); ?>
                                    </h3>
                                    <?php if ($pendingBalance != 0): ?>
                                    <small>Pending: ₹<?php echo number_format($pendingBalance, 2); ?></small>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Cards -->
                    <div class="row mb-4">
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-success shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-success text-uppercase mb-1">
                                                Total Credits</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">
                                                ₹<?php echo number_format($balanceData['TotalCredits'], 2); ?>
                                            </div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-arrow-up fa-2x text-gray-300"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-danger shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-danger text-uppercase mb-1">
                                                Total Debits</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">
                                                ₹<?php echo number_format($balanceData['TotalDebits'], 2); ?>
                                            </div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-arrow-down fa-2x text-gray-300"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-primary shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-primary text-uppercase mb-1">
                                                Total Transactions</div>
                                            <div class="h5 mb-0 font-weight-bold text-gray-800">
                                                <?php echo number_format($balanceData['TotalTransactions']); ?>
                                            </div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-receipt fa-2x text-gray-300"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card border-left-info shadow h-100 py-2">
                                <div class="card-body">
                                    <div class="row no-gutters align-items-center">
                                        <div class="col mr-2">
                                            <div class="text-xs font-weight-bold text-info text-uppercase mb-1">
                                                Current Balance</div>
                                            <div class="h5 mb-0 font-weight-bold <?php echo $currentBalance > 0 ? 'balance-positive' : ($currentBalance < 0 ? 'balance-negative' : 'balance-zero'); ?>">
                                                ₹<?php echo number_format($currentBalance, 2); ?>
                                            </div>
                                        </div>
                                        <div class="col-auto">
                                            <i class="fas fa-balance-scale fa-2x text-gray-300"></i>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Date Filter -->
                    <div class="card shadow mb-3 no-print">
                        <div class="card-body py-2">
                            <form method="GET" class="row align-items-end">
                                <input type="hidden" name="customer_id" value="<?php echo $customerId; ?>">
                                <div class="col-md-3 mb-2">
                                    <label for="date_from" class="form-label">From Date</label>
                                    <input type="date" class="form-control form-control-sm" name="date_from" 
                                           id="date_from" value="<?php echo $dateFrom; ?>">
                                </div>
                                <div class="col-md-3 mb-2">
                                    <label for="date_to" class="form-label">To Date</label>
                                    <input type="date" class="form-control form-control-sm" name="date_to" 
                                           id="date_to" value="<?php echo $dateTo; ?>">
                                </div>
                                <div class="col-md-6 mb-2">
                                    <div class="btn-group" role="group">
                                        <button type="submit" class="btn btn-primary btn-sm">
                                            <i class="fas fa-filter mr-1"></i>Filter
                                        </button>
                                        <a href="customer-ledger.php?customer_id=<?php echo $customerId; ?>" class="btn btn-secondary btn-sm">
                                            <i class="fas fa-times mr-1"></i>Clear Filter
                                        </a>
                                        <button type="button" class="btn btn-success btn-sm" onclick="exportLedger()">
                                            <i class="fas fa-download mr-1"></i>Export
                                        </button>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>

                    <!-- Transaction Ledger -->
                    <div class="card shadow mb-4">
                        <div class="card-header py-3">
                            <h6 class="m-0 font-weight-bold text-primary">
                                <i class="fas fa-list mr-2"></i>Transaction Ledger
                                <?php if (!empty($dateFrom) || !empty($dateTo)): ?>
                                <span class="text-muted">
                                    (<?php echo !empty($dateFrom) ? date('M j, Y', strtotime($dateFrom)) : 'Beginning'; ?> - 
                                     <?php echo !empty($dateTo) ? date('M j, Y', strtotime($dateTo)) : 'Present'; ?>)
                                </span>
                                <?php endif; ?>
                            </h6>
                        </div>
                        <div class="card-body p-0">
                            <div class="table-responsive">
                                <table class="table table-bordered table-striped ledger-table mb-0" width="100%" cellspacing="0">
                                    <thead>
                                        <tr>
                                            <th width="10%">Date</th>
                                            <th width="25%">Description</th>
                                            <th width="10%">Method</th>
                                            <th width="10%">Reference</th>
                                            <th width="8%">Status</th>
                                            <th width="12%">Credit</th>
                                            <th width="12%">Debit</th>
                                            <th width="13%">Balance</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php 
                                        $runningBalance = 0;
                                        $hasTransactions = false;
                                        
                                        // If filtering by date, calculate opening balance
                                        if (!empty($dateFrom)) {
                                            $openingQuery = "SELECT 
                                                COALESCE(SUM(CASE WHEN TransactionType = 'Credit' AND Status = 'Completed' THEN Amount ELSE 0 END), 0) -
                                                COALESCE(SUM(CASE WHEN TransactionType = 'Debit' AND Status = 'Completed' THEN Amount ELSE 0 END), 0) as OpeningBalance
                                                FROM tblcustomerpayments 
                                                WHERE CustomerID = $customerId AND IsActive = 1 AND TransactionDate < '$dateFrom'";
                                            $openingResult = mysqli_query($con, $openingQuery);
                                            $openingBalance = mysqli_fetch_assoc($openingResult)['OpeningBalance'];
                                            $runningBalance = $openingBalance;
                                            
                                            if ($openingBalance != 0) {
                                                $hasTransactions = true;
                                        ?>
                                        <tr class="table-info">
                                            <td><?php echo date('M j, Y', strtotime($dateFrom)); ?></td>
                                            <td><strong>Opening Balance</strong></td>
                                            <td>-</td>
                                            <td>-</td>
                                            <td>-</td>
                                            <td>-</td>
                                            <td>-</td>
                                            <td class="running-balance <?php echo $openingBalance > 0 ? 'balance-positive' : ($openingBalance < 0 ? 'balance-negative' : 'balance-zero'); ?>">
                                                ₹<?php echo number_format($openingBalance, 2); ?>
                                            </td>
                                        </tr>
                                        <?php 
                                            }
                                        }
                                        
                                        while($payment = mysqli_fetch_assoc($paymentsResult)): 
                                            $hasTransactions = true;
                                            if ($payment['Status'] == 'Completed') {
                                                if ($payment['TransactionType'] == 'Credit') {
                                                    $runningBalance += $payment['Amount'];
                                                } else {
                                                    $runningBalance -= $payment['Amount'];
                                                }
                                            }
                                        ?>
                                        <tr class="transaction-row">
                                            <td>
                                                <strong><?php echo date('M j, Y', strtotime($payment['TransactionDate'])); ?></strong>
                                                <br><small class="text-muted"><?php echo date('h:i A', strtotime($payment['CreatedDate'])); ?></small>
                                            </td>
                                            <td>
                                                <?php echo htmlspecialchars($payment['Description']); ?>
                                                <br><small class="text-muted">by <?php echo htmlspecialchars($payment['CreatedByName']); ?></small>
                                                <?php if (!empty($payment['VoucherImage'])): ?>
                                                <br><img src="uploads/vouchers/<?php echo $payment['VoucherImage']; ?>" 
                                                         class="voucher-preview" alt="Voucher"
                                                         onclick="showVoucherModal('uploads/vouchers/<?php echo $payment['VoucherImage']; ?>')">
                                                <?php endif; ?>
                                            </td>
                                            <td>
                                                <span class="badge badge-secondary badge-sm">
                                                    <?php echo $payment['PaymentMethod']; ?>
                                                </span>
                                            </td>
                                            <td>
                                                <?php if (!empty($payment['ReferenceNumber'])): ?>
                                                    <code class="text-sm"><?php echo htmlspecialchars($payment['ReferenceNumber']); ?></code>
                                                <?php else: ?>
                                                    <span class="text-muted">-</span>
                                                <?php endif; ?>
                                            </td>
                                            <td>
                                                <span class="badge badge-<?php 
                                                    echo $payment['Status'] == 'Completed' ? 'success' : 
                                                        ($payment['Status'] == 'Pending' ? 'warning' : 'danger'); 
                                                ?> badge-sm">
                                                    <?php echo $payment['Status']; ?>
                                                </span>
                                            </td>
                                            <td>
                                                <?php if ($payment['TransactionType'] == 'Credit'): ?>
                                                    <span class="amount-credit">₹<?php echo number_format($payment['Amount'], 2); ?></span>
                                                <?php else: ?>
                                                    <span class="text-muted">-</span>
                                                <?php endif; ?>
                                            </td>
                                            <td>
                                                <?php if ($payment['TransactionType'] == 'Debit'): ?>
                                                    <span class="amount-debit">₹<?php echo number_format($payment['Amount'], 2); ?></span>
                                                <?php else: ?>
                                                    <span class="text-muted">-</span>
                                                <?php endif; ?>
                                            </td>
                                            <td class="running-balance <?php 
                                                if ($payment['Status'] == 'Completed') {
                                                    echo $runningBalance > 0 ? 'balance-positive' : ($runningBalance < 0 ? 'balance-negative' : 'balance-zero');
                                                } else {
                                                    echo 'text-muted';
                                                }
                                            ?>">
                                                <?php if ($payment['Status'] == 'Completed'): ?>
                                                    ₹<?php echo number_format($runningBalance, 2); ?>
                                                <?php else: ?>
                                                    Pending
                                                <?php endif; ?>
                                            </td>
                                        </tr>
                                        <?php endwhile; ?>
                                        
                                        <?php if (!$hasTransactions): ?>
                                        <tr>
                                            <td colspan="8" class="text-center text-muted py-4">
                                                <i class="fas fa-inbox fa-3x mb-3 text-gray-300"></i>
                                                <br>No transactions found for this customer
                                            </td>
                                        </tr>
                                        <?php endif; ?>
                                    </tbody>
                                    
                                    <?php if ($hasTransactions): ?>
                                    <tfoot class="thead-light">
                                        <tr>
                                            <th colspan="5" class="text-right">Current Balance:</th>
                                            <th class="amount-credit">₹<?php echo number_format($balanceData['TotalCredits'], 2); ?></th>
                                            <th class="amount-debit">₹<?php echo number_format($balanceData['TotalDebits'], 2); ?></th>
                                            <th class="running-balance <?php echo $currentBalance > 0 ? 'balance-positive' : ($currentBalance < 0 ? 'balance-negative' : 'balance-zero'); ?>">
                                                ₹<?php echo number_format($currentBalance, 2); ?>
                                            </th>
                                        </tr>
                                    </tfoot>
                                    <?php endif; ?>
                                </table>
                            </div>
                        </div>
                    </div>

                </div>
                <!-- /.container-fluid -->
            </div>
            <?php include_once('includes/footer.php');?>
        </div>
    </div>

    <!-- Voucher Modal -->
    <div class="modal fade" id="voucherModal" tabindex="-1" role="dialog" aria-labelledby="voucherModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-lg" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="voucherModalLabel">
                        <i class="fas fa-file-image mr-2"></i>Payment Voucher
                    </h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                    </button>
                </div>
                <div class="modal-body text-center">
                    <img id="voucherImage" src="" class="img-fluid" style="max-height: 500px;" alt="Voucher">
                </div>
            </div>
        </div>
    </div>

    <?php include_once('includes/footer2.php');?>

    <!-- Bootstrap core JavaScript-->
    <script src="vendor/jquery/jquery.min.js"></script>
    <script src="vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <script src="vendor/jquery-easing/jquery.easing.min.js"></script>
    <script src="js/sb-admin-2.min.js"></script>
    <script src="js/quick-filters.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>

    <script>
        function showVoucherModal(imagePath) {
            document.getElementById('voucherImage').src = imagePath;
            $('#voucherModal').modal('show');
        }

        function exportLedger() {
            // Get table data
            const table = document.querySelector('.ledger-table');
            const ws = XLSX.utils.table_to_sheet(table);
            
            // Create workbook
            const wb = XLSX.utils.book_new();
            XLSX.utils.book_append_sheet(wb, ws, 'Customer_Ledger');
            
            // Save file
            const customerName = '<?php echo preg_replace('/[^a-zA-Z0-9]/', '_', $customer['CustomerName']); ?>';
            const fileName = `Customer_Ledger_${customerName}_${new Date().toISOString().slice(0, 10)}.xlsx`;
            XLSX.writeFile(wb, fileName);
        }

        // Print specific styling
        window.addEventListener('beforeprint', function() {
            document.title = 'Customer Ledger - <?php echo htmlspecialchars($customer['CustomerName']); ?>';
        });
    </script>
</body>
</html>
