<?php
session_start();
include 'layouts/dbconn.php';
include 'layouts/session.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN', 'ACCOUNTANT'];
if (!in_array($_SESSION['role_code'], $allowed_roles)) {
    header('Location: index.php');
    exit();
}

$page_title = "Payment Reconciliation";

// Get unmatched payments
$unmatched_query = "SELECT p.*, i.invoice_number, pat.patient_number, u.full_name as patient_name
                    FROM payments p
                    LEFT JOIN invoices i ON p.invoice_id = i.invoice_id
                    LEFT JOIN patients pat ON p.patient_id = pat.patient_id
                    LEFT JOIN users u ON pat.user_id = u.user_id
                    WHERE p.status = 'PENDING'
                    ORDER BY p.payment_date DESC";
$unmatched_result = mysqli_query($conn, $unmatched_query);

// Get invoices with discrepancies (amount paid doesn't match expected)
$discrepancy_query = "SELECT i.*, 
                      (i.amount_paid - (SELECT COALESCE(SUM(amount), 0) FROM payments WHERE invoice_id = i.invoice_id AND status = 'COMPLETED')) as discrepancy,
                      pat.patient_number, u.full_name as patient_name
                      FROM invoices i
                      JOIN patients pat ON i.patient_id = pat.patient_id
                      JOIN users u ON pat.user_id = u.user_id
                      WHERE ABS(i.amount_paid - (SELECT COALESCE(SUM(amount), 0) FROM payments WHERE invoice_id = i.invoice_id AND status = 'COMPLETED')) > 0.01
                      AND i.status != 'CANCELLED'";
$discrepancy_result = mysqli_query($conn, $discrepancy_query);

// Summary stats
$stats_query = "SELECT 
                COUNT(CASE WHEN status = 'PENDING' THEN 1 END) as unmatched_count,
                SUM(CASE WHEN status = 'PENDING' THEN amount ELSE 0 END) as unmatched_amount
                FROM payments";
$stats_result = mysqli_query($conn, $stats_query);
$stats = mysqli_fetch_assoc($stats_result);

?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => $page_title)); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
    <link href="assets/libs/datatables.net-bs5/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
    
    <style>
        .page-header {
            background: linear-gradient(135deg, #8b5cf6 0%, #7c3aed 100%);
            padding: 2rem;
            border-radius: 12px;
            color: white;
            margin-bottom: 2rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
        }
        
        .stat-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            transition: transform 0.2s;
            margin-bottom: 1.5rem;
        }
        
        .chart-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            margin-bottom: 1.5rem;
        }
        
        .discrepancy-badge {
            background: #fee2e2;
            color: #991b1b;
            padding: 0.375rem 0.75rem;
            border-radius: 6px;
            font-weight: 600;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    
                    <!-- Page Title -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0"><?php echo $page_title; ?></h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="financial_dashboard.php">Financial</a></li>
                                        <li class="breadcrumb-item active">Reconciliation</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Page Header -->
                    <div class="page-header">
                        <div class="d-flex justify-content-between align-items-center">
                            <div>
                                <h4><i class="ri-file-list-check-line me-2"></i>Payment Reconciliation</h4>
                                <p class="mb-0">Match payments to invoices and resolve discrepancies</p>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Cards -->
                    <div class="row">
                        <div class="col-md-6">
                            <div class="stat-card">
                                <div class="d-flex align-items-center">
                                    <div class="flex-shrink-0">
                                        <div class="avatar-sm rounded-circle" style="background: linear-gradient(135deg, #f59e0b 0%, #d97706 100%);">
                                            <span class="avatar-title text-white fs-4">
                                                <i class="ri-time-line"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="flex-grow-1 ms-3">
                                        <p class="text-muted mb-1">Unmatched Payments</p>
                                        <h4 class="mb-0"><?php echo $stats['unmatched_count'] ?? 0; ?> <small class="text-muted fs-6">/ KES <?php echo number_format($stats['unmatched_amount'] ?? 0, 2); ?></small></h4>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-md-6">
                            <div class="stat-card">
                                <div class="d-flex align-items-center">
                                    <div class="flex-shrink-0">
                                        <div class="avatar-sm rounded-circle" style="background: linear-gradient(135deg, #dc2626 0%, #991b1b 100%);">
                                            <span class="avatar-title text-white fs-4">
                                                <i class="ri-alert-line"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="flex-grow-1 ms-3">
                                        <p class="text-muted mb-1">Invoices with Discrepancies</p>
                                        <h4 class="mb-0"><?php echo mysqli_num_rows($discrepancy_result); ?></h4>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Unmatched Payments -->
                    <div class="chart-card">
                        <h5 class="mb-3"><i class="ri-question-line me-2"></i>Unmatched Payments</h5>
                        <div class="table-responsive">
                            <table class="table table-hover" id="unmatchedTable">
                                <thead class="table-light">
                                    <tr>
                                        <th>Payment #</th>
                                        <th>Date</th>
                                        <th>Amount</th>
                                        <th>Method</th>
                                        <th>Reference</th>
                                        <th>Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php while ($payment = mysqli_fetch_assoc($unmatched_result)): ?>
                                    <tr>
                                        <td><strong><?php echo htmlspecialchars($payment['payment_number']); ?></strong></td>
                                        <td><?php echo date('d M Y', strtotime($payment['payment_date'])); ?></td>
                                        <td><strong class="text-success">KES <?php echo number_format($payment['amount'], 2); ?></strong></td>
                                        <td><span class="badge bg-info"><?php echo $payment['payment_method']; ?></span></td>
                                        <td><small><?php echo htmlspecialchars($payment['payment_reference'] ?? '-'); ?></small></td>
                                        <td>
                                            <button type="button" class="btn btn-sm btn-primary match-payment" 
                                                    data-payment-id="<?php echo $payment['payment_id']; ?>">
                                                <i class="ri-links-line me-1"></i> Match to Invoice
                                            </button>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>

                    <!-- Invoices with Discrepancies -->
                    <div class="chart-card">
                        <h5 class="mb-3"><i class="ri-alert-line me-2"></i>Invoices with Payment Discrepancies</h5>
                        <div class="table-responsive">
                            <table class="table table-hover" id="discrepancyTable">
                                <thead class="table-light">
                                    <tr>
                                        <th>Invoice #</th>
                                        <th>Patient</th>
                                        <th>Total Amount</th>
                                        <th>Recorded Paid</th>
                                        <th>Actual Payments</th>
                                        <th>Discrepancy</th>
                                        <th>Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php 
                                    mysqli_data_seek($discrepancy_result, 0);
                                    while ($invoice = mysqli_fetch_assoc($discrepancy_result)): 
                                        $actual_payments_query = "SELECT COALESCE(SUM(amount), 0) as actual FROM payments WHERE invoice_id = {$invoice['invoice_id']} AND status = 'COMPLETED'";
                                        $actual_result = mysqli_query($conn, $actual_payments_query);
                                        $actual_row = mysqli_fetch_assoc($actual_result);
                                        $actual_paid = $actual_row['actual'];
                                    ?>
                                    <tr>
                                        <td><strong><?php echo htmlspecialchars($invoice['invoice_number']); ?></strong></td>
                                        <td>
                                            <div><?php echo htmlspecialchars($invoice['patient_name']); ?></div>
                                            <small class="text-muted"><?php echo htmlspecialchars($invoice['patient_number']); ?></small>
                                        </td>
                                        <td>KES <?php echo number_format($invoice['total_amount'], 2); ?></td>
                                        <td>KES <?php echo number_format($invoice['amount_paid'], 2); ?></td>
                                        <td>KES <?php echo number_format($actual_paid, 2); ?></td>
                                        <td>
                                            <span class="discrepancy-badge">
                                                <?php echo ($invoice['discrepancy'] > 0 ? '+' : '') . number_format($invoice['discrepancy'], 2); ?> KES
                                            </span>
                                        </td>
                                        <td>
                                            <button type="button" class="btn btn-sm btn-warning resolve-discrepancy" 
                                                    data-invoice-id="<?php echo $invoice['invoice_id']; ?>">
                                                <i class="ri-tools-line me-1"></i> Resolve
                                            </button>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                    
                </div>
            </div>
        </div>

        <?php include 'layouts/footer.php'; ?>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
    <script src="assets/libs/datatables.net/js/jquery.dataTables.min.js"></script>
    <script src="assets/libs/datatables.net-bs5/js/dataTables.bootstrap5.min.js"></script>
    
    <script>
    $(document).ready(function() {
        $('#unmatchedTable, #discrepancyTable').DataTable({
            pageLength: 10
        });

        // Match payment to invoice
        $('.match-payment').on('click', function() {
            const paymentId = $(this).data('payment-id');
            
            Swal.fire({
                title: 'Match Payment to Invoice',
                text: 'Enter the invoice number to match this payment to:',
                input: 'text',
                inputPlaceholder: 'Invoice Number',
                showCancelButton: true,
                confirmButtonText: 'Match',
                preConfirm: (invoiceNumber) => {
                    if (!invoiceNumber) {
                        Swal.showValidationMessage('Please enter an invoice number');
                    }
                    return invoiceNumber;
                }
            }).then((result) => {
                if (result.isConfirmed) {
                    $.ajax({
                        url: 'ajax/match_payment_to_invoice.php',
                        method: 'POST',
                        data: { payment_id: paymentId, invoice_number: result.value },
                        dataType: 'json',
                        success: function(response) {
                            if (response.success) {
                                Swal.fire('Matched!', 'Payment matched successfully', 'success')
                                    .then(() => location.reload());
                            } else {
                                Swal.fire('Error', response.message, 'error');
                            }
                        }
                    });
                }
            });
        });

        // Resolve discrepancy
        $('.resolve-discrepancy').on('click', function() {
            const invoiceId = $(this).data('invoice-id');
            window.location.href = 'view_invoice.php?id=' + invoiceId + '&reconcile=1';
        });
    });
    </script>
</body>
</html>