<?php
// filename: ajax/get_reconciliation_details.php (Strict schema: exact tables/columns)
session_start(); // Required for resolved_by = $_SESSION['user_id']
include '../layouts/dbconn.php';

// Validate input
if (!isset($_GET['reconciliation_id']) || !filter_var($_GET['reconciliation_id'], FILTER_VALIDATE_INT) === false) {
    http_response_code(400);
    echo '<div class="alert alert-danger"><i class="ri-error-warning-line me-2"></i>Invalid reconciliation ID.</div>';
    exit();
}

$reconciliation_id = (int)$_GET['reconciliation_id'];

// Fetch reconciliation summary (reconciliations + users.full_name + insurance_providers.provider_name)
$summary_sql = "SELECT r.reconciliation_id, r.reconciliation_number, r.description, r.insurance_provider_id, r.file_path, 
                       r.file_type, r.records_uploaded, r.process_mode, r.reconciled_by, r.reconciliation_date, 
                       r.status, r.total_matches, r.total_discrepancies, r.notes, r.created_at, r.updated_at,
                       u.full_name AS reconciler_name, ip.provider_name AS insurance_provider_name
                FROM reconciliations r
                LEFT JOIN users u ON r.reconciled_by = u.user_id
                LEFT JOIN insurance_providers ip ON r.insurance_provider_id = ip.provider_id
                WHERE r.reconciliation_id = ?";
if (!$stmt = $conn->prepare($summary_sql)) {
    echo '<div class="alert alert-danger">Database prepare error for summary.</div>';
    exit();
}
$stmt->bind_param('i', $reconciliation_id);
if (!$stmt->execute()) {
    echo '<div class="alert alert-danger">Database execute error for summary.</div>';
    exit();
}
$recon = $stmt->get_result()->fetch_assoc();
$stmt->close();

if (!$recon) {
    echo '<div class="alert alert-warning"><i class="ri-search-eye-line me-2"></i>Reconciliation not found.</div>';
    exit();
}

// Fetch system records (invoices + insurance_claims on invoice_id + patients on patient_id; filter insurance_claims.provider_id, last 30 days)
$system_sql = "SELECT i.invoice_id, i.invoice_number, i.grand_total AS amount, i.invoice_date AS date, 
                      p.patient_id, p.patient_number, CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, '')) AS patient_name,
                      ic.claim_id, ic.claim_number, ic.status AS claim_status
               FROM invoices i
               INNER JOIN insurance_claims ic ON i.invoice_id = ic.invoice_id
               LEFT JOIN patients p ON i.patient_id = p.patient_id
               WHERE ic.provider_id = ? AND i.invoice_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
               ORDER BY i.invoice_date DESC LIMIT 100";
if (!$system_stmt = $conn->prepare($system_sql)) {
    echo '<div class="alert alert-danger">Database prepare error for system records.</div>';
    exit();
}
$system_stmt->bind_param('i', $recon['insurance_provider_id']);
if (!$system_stmt->execute()) {
    echo '<div class="alert alert-danger">Database execute error for system records.</div>';
    exit();
}
$system_result = $system_stmt->get_result();
$system_stmt->close();

// Fetch uploaded records (reconciliation_uploaded_records exact columns)
$uploaded_sql = "SELECT uploaded_id, claim_number, patient_number, amount, claim_date, status, insurance_policy_no, service_description 
                 FROM reconciliation_uploaded_records WHERE reconciliation_id = ? ORDER BY created_at DESC LIMIT 100";
if (!$uploaded_stmt = $conn->prepare($uploaded_sql)) {
    echo '<div class="alert alert-danger">Database prepare error for uploaded records.</div>';
    exit();
}
$uploaded_stmt->bind_param('i', $reconciliation_id);
if (!$uploaded_stmt->execute()) {
    echo '<div class="alert alert-danger">Database execute error for uploaded records.</div>';
    exit();
}
$uploaded_result = $uploaded_stmt->get_result();
$uploaded_stmt->close();

// Fetch discrepancies for highlighting (reconciliation_discrepancies.uploaded_claim_number)
$disc_sql = "SELECT uploaded_claim_number FROM reconciliation_discrepancies WHERE reconciliation_id = ?";
if (!$disc_stmt = $conn->prepare($disc_sql)) {
    echo '<div class="alert alert-danger">Database prepare error for discrepancies.</div>';
    exit();
}
$disc_stmt->bind_param('i', $reconciliation_id);
if (!$disc_stmt->execute()) {
    echo '<div class="alert alert-danger">Database execute error for discrepancies.</div>';
    exit();
}
$disc_result = $disc_stmt->get_result();
$discrepancies = [];
while ($d = $disc_result->fetch_assoc()) {
    $discrepancies[] = $d['uploaded_claim_number'];
}
$disc_stmt->close();

// Fetch matches for highlighting (reconciliation_matches.uploaded_claim_number via join to reconciliation_uploaded_records.claim_number)
$match_sql = "SELECT rm.uploaded_claim_number FROM reconciliation_matches rm 
              INNER JOIN reconciliation_uploaded_records ur ON rm.uploaded_claim_number = ur.claim_number 
              WHERE rm.reconciliation_id = ?";
if (!$match_stmt = $conn->prepare($match_sql)) {
    echo '<div class="alert alert-danger">Database prepare error for matches.</div>';
    exit();
}
$match_stmt->bind_param('i', $reconciliation_id);
if (!$match_stmt->execute()) {
    echo '<div class="alert alert-danger">Database execute error for matches.</div>';
    exit();
}
$match_result = $match_stmt->get_result();
$matches = [];
while ($m = $match_result->fetch_assoc()) {
    $matches[] = $m['uploaded_claim_number'];
}
$match_stmt->close();

// Handle resolve action (reconciliation_discrepancies exact columns)
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'resolve' && isset($_POST['discrepancy_id'])) {
    $discrepancy_id = (int)$_POST['discrepancy_id'];
    $resolve_notes = trim($_POST['resolve_notes'] ?? '');
    $resolve_sql = "UPDATE reconciliation_discrepancies SET resolved = 1, resolved_by = ?, resolved_at = CURRENT_TIMESTAMP, 
                           notes = CONCAT(COALESCE(notes, ''), '\nResolved: ', ?) 
                    WHERE discrepancy_id = ? AND reconciliation_id = ?";
    if (!$resolve_stmt = $conn->prepare($resolve_sql)) {
        echo json_encode(['status' => 'error', 'message' => 'Database prepare error for resolve.']);
        exit();
    }
    $resolve_stmt->bind_param('isii', $_SESSION['user_id'], $resolve_notes, $discrepancy_id, $reconciliation_id);
    if ($resolve_stmt->execute()) {
        echo json_encode(['status' => 'success', 'message' => 'Discrepancy resolved successfully!']);
    } else {
        echo json_encode(['status' => 'error', 'message' => 'Failed to resolve discrepancy.']);
    }
    $resolve_stmt->close();
    exit();
}
?>

<!-- Summary Card -->
<div class="row mb-4">
    <div class="col-12">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h5 class="card-title"><i class="ri-file-check-line me-2"></i><?php echo htmlspecialchars($recon['reconciliation_number']); ?> - <?php echo htmlspecialchars($recon['description']); ?></h5>
                <p class="card-text">
                    <strong>Provider:</strong> <?php echo htmlspecialchars($recon['insurance_provider_name'] ?? 'N/A'); ?><br>
                    <strong>Reconciled By:</strong> <?php echo htmlspecialchars($recon['reconciler_name'] ?? 'N/A'); ?><br>
                    <strong>Date:</strong> <?php echo date('d M Y H:i', strtotime($recon['reconciliation_date'])); ?><br>
                    <strong>Mode:</strong> <?php echo ucfirst(strtolower($recon['process_mode'])); ?><br>
                    <strong>Status:</strong> 
                    <span class="badge bg-<?php echo $recon['status'] === 'COMPLETED' ? 'success' : ($recon['status'] === 'DISPUTED' ? 'danger' : 'warning'); ?>">
                        <?php echo $recon['status']; ?>
                    </span>
                </p>
                <div class="row text-center">
                    <div class="col-md-3">
                        <div class="p-3 bg-light rounded">
                            <h6 class="text-success"><i class="ri-check-double-line me-1"></i><?php echo $recon['total_matches']; ?></h6>
                            <small>Matches</small>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="p-3 bg-light rounded">
                            <h6 class="text-danger"><i class="ri-alert-line me-1"></i><?php echo $recon['total_discrepancies']; ?></h6>
                            <small>Discrepancies</small>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="p-3 bg-light rounded">
                            <h6 class="text-info"><i class="ri-database-line me-1"></i><?php echo $system_result->num_rows; ?></h6>
                            <small>System Records</small>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="p-3 bg-light rounded">
                            <h6 class="text-primary"><i class="ri-upload-line me-1"></i><?php echo $uploaded_result->num_rows; ?></h6>
                            <small>Uploaded Records</small>
                        </div>
                    </div>
                </div>
                <?php if (!empty($recon['notes'])): ?>
                <hr>
                <small class="text-muted"><strong>Notes:</strong> <?php echo nl2br(htmlspecialchars($recon['notes'])); ?></small>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<!-- Side-by-Side Tables for Comparison -->
<div class="row">
    <div class="col-md-6">
        <div class="card h-100">
            <div class="card-header">
                <h6 class="mb-0"><i class="ri-database-line me-2 text-primary"></i>System Records (Invoices/Claims)</h6>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-sm mb-0" id="systemTable">
                        <thead class="table-light">
                            <tr>
                                <th>Invoice #</th>
                                <th>Claim #</th>
                                <th>Patient</th>
                                <th>Amount (KES)</th>
                                <th>Date</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $system_result->data_seek(0); while ($sys = $system_result->fetch_assoc()): ?>
                            <tr>
                                <td><strong><?php echo htmlspecialchars($sys['invoice_number'] ?? 'N/A'); ?></strong></td>
                                <td><?php echo htmlspecialchars($sys['claim_number'] ?? 'N/A'); ?></td>
                                <td><?php echo htmlspecialchars($sys['patient_name'] ?? $sys['patient_number'] ?? 'N/A'); ?></td>
                                <td class="text-end"><strong><?php echo number_format($sys['amount'], 2); ?></strong></td>
                                <td><?php echo date('d M Y', strtotime($sys['date'])); ?></td>
                                <td><span class="badge bg-secondary"><?php echo htmlspecialchars($sys['claim_status'] ?? 'N/A'); ?></span></td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
    
    <div class="col-md-6">
        <div class="card h-100">
            <div class="card-header">
                <h6 class="mb-0"><i class="ri-file-line me-2 text-info"></i>Uploaded CSV Records</h6>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-sm mb-0" id="uploadedTable">
                        <thead class="table-light">
                            <tr>
                                <th>Claim #</th>
                                <th>Patient #</th>
                                <th>Amount (KES)</th>
                                <th>Date</th>
                                <th>Status</th>
                                <th>Policy</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $uploaded_result->data_seek(0); while ($upl = $uploaded_result->fetch_assoc()): ?>
                            <tr class="<?php 
                                $claim_num = $upl['claim_number'];
                                if (in_array($claim_num, $discrepancies)) {
                                    echo 'table-danger';
                                } elseif (in_array($claim_num, $matches)) {
                                    echo 'table-success';
                                } else {
                                    echo 'table-secondary';
                                }
                            ?>">
                                <td><strong><?php echo htmlspecialchars($upl['claim_number']); ?></strong></td>
                                <td><?php echo htmlspecialchars($upl['patient_number'] ?? 'N/A'); ?></td>
                                <td class="text-end"><strong><?php echo number_format($upl['amount'], 2); ?></strong></td>
                                <td><?php echo $upl['claim_date'] ? date('d M Y', strtotime($upl['claim_date'])) : 'N/A'; ?></td>
                                <td><span class="badge bg-secondary"><?php echo htmlspecialchars($upl['status'] ?? 'N/A'); ?></span></td>
                                <td><?php echo htmlspecialchars($upl['insurance_policy_no'] ?? 'N/A'); ?></td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Discrepancies Table -->
<div class="row mt-4">
    <div class="col-12">
        <h6 class="mb-3"><i class="ri-error-warning-line me-2 text-danger"></i>Discrepancies (<?php echo $recon['total_discrepancies']; ?>)</h6>
        <?php 
        $disc_full_sql = "SELECT rd.discrepancy_id, rd.uploaded_claim_number, rd.discrepancy_type, rd.expected_amount, 
                                 rd.actual_amount, rd.notes, rd.resolved, rd.resolved_by, rd.resolved_at
                          FROM reconciliation_discrepancies rd 
                          WHERE rd.reconciliation_id = ? ORDER BY rd.discrepancy_id DESC";
        if (!$disc_full_stmt = $conn->prepare($disc_full_sql)) {
            echo '<div class="alert alert-danger">Database prepare error for full discrepancies.</div>';
            exit();
        }
        $disc_full_stmt->bind_param('i', $reconciliation_id);
        if (!$disc_full_stmt->execute()) {
            echo '<div class="alert alert-danger">Database execute error for full discrepancies.</div>';
            exit();
        }
        $disc_full_result = $disc_full_stmt->get_result();
        $disc_full_stmt->close();
        if ($disc_full_result->num_rows > 0): 
        ?>
        <div class="table-responsive">
            <table class="table table-sm table-hover" id="discrepanciesTable">
                <thead class="table-light">
                    <tr>
                        <th>Uploaded Claim #</th>
                        <th>Type</th>
                        <th>Expected (KES)</th>
                        <th>Actual (KES)</th>
                        <th>Notes</th>
                        <th>Status</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php while ($disc = $disc_full_result->fetch_assoc()): ?>
                    <tr class="<?php echo $disc['resolved'] ? 'table-success' : 'table-warning'; ?>">
                        <td><strong><?php echo htmlspecialchars($disc['uploaded_claim_number'] ?? 'N/A'); ?></strong></td>
                        <td><span class="badge bg-warning"><?php echo htmlspecialchars($disc['discrepancy_type']); ?></span></td>
                        <td class="text-end"><?php echo number_format($disc['expected_amount'] ?? 0, 2); ?></td>
                        <td class="text-end text-danger"><?php echo number_format($disc['actual_amount'] ?? 0, 2); ?></td>
                        <td><?php echo nl2br(htmlspecialchars($disc['notes'] ?? '')); ?></td>
                        <td><span class="badge <?php echo $disc['resolved'] ? 'bg-success' : 'bg-warning'; ?>"><?php echo $disc['resolved'] ? 'Resolved' : 'Pending'; ?></span></td>
                        <td>
                            <?php if (!$disc['resolved']): ?>
                            <button class="btn btn-sm btn-outline-success" onclick="resolveDiscrepancy(<?php echo $disc['discrepancy_id']; ?>)">
                                <i class="ri-check-line"></i> Resolve
                            </button>
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
        <?php else: ?>
        <div class="alert alert-success"><i class="ri-check-line me-1"></i>No discrepancies found! All records matched perfectly.</div>
        <?php endif; ?>
    </div>
</div>

<!-- DataTables CDN for search/sort/paging -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.7/css/dataTables.bootstrap5.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.7/js/dataTables.bootstrap5.min.js"></script>

<script>
$(document).ready(function() {
    $('#systemTable, #uploadedTable, #discrepanciesTable').DataTable({
        paging: true,
        searching: true,
        ordering: true,
        info: true,
        lengthChange: false,
        pageLength: 25,
        responsive: true,
        language: { search: "Filter records:" }
    });
});

function resolveDiscrepancy(discrepancyId) {
    const notes = prompt('Resolution notes (optional, e.g., "Amount adjusted in system"):', '');
    if (notes === null) return;
    
    Swal.fire({
        title: 'Resolving...',
        text: 'Updating discrepancy.',
        allowOutsideClick: false,
        didOpen: () => Swal.showLoading()
    });
    
    $.post('', {  // POST to self
        reconciliation_id: <?php echo $reconciliation_id; ?>,
        discrepancy_id: discrepancyId,
        action: 'resolve',
        resolve_notes: notes
    }).done(function(response) {
        if (response.status === 'success') {
            Swal.fire('Success!', response.message, 'success').then(() => location.reload());
        } else {
            Swal.fire('Error!', response.message, 'error');
        }
    }).fail(function() {
        Swal.fire('Error!', 'Network issue. Please try again.', 'error');
    });
}
</script>