<?php
// doctor_earnings_report.php - Doctor Earnings Report
include 'layouts/session.php';
include 'layouts/dbconn.php';

$doctor_id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-01');
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');

// Get doctor details
$doctor_sql = "SELECT d.*, u.full_name, u.email, u.phone 
               FROM doctors d 
               JOIN users u ON d.user_id = u.user_id 
               WHERE d.doctor_id = ?";
$stmt = $conn->prepare($doctor_sql);
$stmt->bind_param('i', $doctor_id);
$stmt->execute();
$doctor = $stmt->get_result()->fetch_assoc();

if (!$doctor) {
    $_SESSION['error_message'] = "Doctor not found";
    header("Location: list_doctors.php");
    exit();
}

// Get earnings summary
$summary_sql = "SELECT 
    COUNT(pa.allocation_id) as total_services,
    COUNT(DISTINCT pa.provider_id) as unique_providers,
    SUM(pa.total_service_cost) as total_service_value,
    SUM(pa.doctor_allocation) as gross_earnings,
    SUM(pa.tax_withheld) as total_tax,
    SUM(pa.other_deductions) as total_deductions,
    SUM(pa.net_doctor_payment) as net_earnings,
    SUM(CASE WHEN pa.doctor_payment_status = 'PAID' THEN pa.net_doctor_payment ELSE 0 END) as paid_amount,
    SUM(CASE WHEN pa.doctor_payment_status IN ('PENDING', 'PROCESSED') THEN pa.net_doctor_payment ELSE 0 END) as pending_amount
FROM payment_allocations pa
WHERE pa.doctor_id = ? 
AND pa.service_date BETWEEN ? AND ?
AND pa.allocation_status = 'APPROVED'";

$stmt = $conn->prepare($summary_sql);
$stmt->bind_param('iss', $doctor_id, $date_from, $date_to);
$stmt->execute();
$summary = $stmt->get_result()->fetch_assoc();

// Get allocations by provider
$by_provider_sql = "SELECT 
    esp.provider_name,
    esp.provider_code,
    COUNT(pa.allocation_id) as service_count,
    SUM(pa.total_service_cost) as total_value,
    SUM(pa.doctor_allocation) as gross_earnings,
    SUM(pa.net_doctor_payment) as net_earnings
FROM payment_allocations pa
JOIN external_service_providers esp ON pa.provider_id = esp.provider_id
WHERE pa.doctor_id = ? 
AND pa.service_date BETWEEN ? AND ?
AND pa.allocation_status = 'APPROVED'
GROUP BY pa.provider_id
ORDER BY gross_earnings DESC";

$stmt = $conn->prepare($by_provider_sql);
$stmt->bind_param('iss', $doctor_id, $date_from, $date_to);
$stmt->execute();
$by_provider = $stmt->get_result();

// Get all allocations
$allocations_sql = "SELECT pa.*, esp.provider_name, bp.payment_number
                    FROM payment_allocations pa
                    JOIN bulk_payments bp ON pa.bulk_payment_id = bp.bulk_payment_id
                    JOIN external_service_providers esp ON pa.provider_id = esp.provider_id
                    WHERE pa.doctor_id = ? 
                    AND pa.service_date BETWEEN ? AND ?
                    AND pa.allocation_status = 'APPROVED'
                    ORDER BY pa.service_date DESC";
$stmt = $conn->prepare($allocations_sql);
$stmt->bind_param('iss', $doctor_id, $date_from, $date_to);
$stmt->execute();
$allocations = $stmt->get_result();

// Get payment batches
$batches_sql = "SELECT dpb.*, COUNT(bai.allocation_id) as allocation_count
                FROM doctor_payment_batches dpb
                LEFT JOIN batch_allocation_items bai ON dpb.batch_id = bai.batch_id
                WHERE dpb.doctor_id = ?
                AND dpb.payment_period_start >= ?
                AND dpb.payment_period_end <= ?
                GROUP BY dpb.batch_id
                ORDER BY dpb.created_at DESC";
$stmt = $conn->prepare($batches_sql);
$stmt->bind_param('iss', $doctor_id, $date_from, $date_to);
$stmt->execute();
$batches = $stmt->get_result();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Doctor Earnings')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/flatpickr/flatpickr.min.css" rel="stylesheet">
    
    <style>
        .page-header {
            background: linear-gradient(135deg, #3b82f6 0%, #2563eb 100%);
            padding: 2rem;
            border-radius: 12px;
            color: white;
            margin-bottom: 2rem;
        }
        
        @media print {
            .no-print { display: none; }
            .page-header { background: #3b82f6 !important; }
        }
    </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 Header -->
                    <div class="page-header no-print">
                        <div class="row align-items-center">
                            <div class="col">
                                <h3 class="mb-2"><i class="ri-user-heart-line me-2"></i>Doctor Earnings Report</h3>
                                <p class="mb-0 opacity-75"><?php echo htmlspecialchars($doctor['full_name']); ?></p>
                            </div>
                            <div class="col-auto">
                                <button onclick="window.print()" class="btn btn-light me-2">
                                    <i class="ri-printer-line me-1"></i>Print
                                </button>
                                <a href="list_doctors.php" class="btn btn-secondary">
                                    <i class="ri-arrow-left-line me-1"></i>Back
                                </a>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Doctor Information -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <div class="row">
                                <div class="col-md-6">
                                    <h4><?php echo htmlspecialchars($doctor['full_name']); ?></h4>
                                    <p class="mb-1"><strong>Specialization:</strong> <?php echo htmlspecialchars($doctor['specialization']); ?></p>
                                    <p class="mb-1"><strong>Medical License:</strong> <?php echo htmlspecialchars($doctor['medical_license']); ?></p>
                                    <?php if ($doctor['email']): ?>
                                    <p class="mb-1"><strong>Email:</strong> <?php echo htmlspecialchars($doctor['email']); ?></p>
                                    <?php endif; ?>
                                    <?php if ($doctor['phone']): ?>
                                    <p class="mb-1"><strong>Phone:</strong> <?php echo htmlspecialchars($doctor['phone']); ?></p>
                                    <?php endif; ?>
                                </div>
                                <div class="col-md-6 text-end">
                                    <h5>EARNINGS REPORT</h5>
                                    <p class="mb-1">
                                        <strong>Period:</strong> <?php echo date('d M Y', strtotime($date_from)); ?> to <?php echo date('d M Y', strtotime($date_to)); ?>
                                    </p>
                                    <p class="mb-1"><strong>Generated:</strong> <?php echo date('d M Y H:i'); ?></p>
                                    <p class="mb-1"><strong>Commission Rate:</strong> <?php echo number_format($doctor['commission_rate'], 2); ?>%</p>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Period Filter -->
                    <div class="card mb-4 no-print">
                        <div class="card-body">
                            <form method="GET" action="">
                                <input type="hidden" name="id" value="<?php echo $doctor_id; ?>">
                                <div class="row g-3">
                                    <div class="col-md-4">
                                        <label class="form-label">From Date</label>
                                        <input type="text" class="form-control datepicker" name="date_from" 
                                               value="<?php echo htmlspecialchars($date_from); ?>">
                                    </div>
                                    <div class="col-md-4">
                                        <label class="form-label">To Date</label>
                                        <input type="text" class="form-control datepicker" name="date_to" 
                                               value="<?php echo htmlspecialchars($date_to); ?>">
                                    </div>
                                    <div class="col-md-4 d-flex align-items-end">
                                        <button type="submit" class="btn btn-primary w-100">
                                            <i class="ri-search-line me-1"></i>Update Period
                                        </button>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    <!-- Earnings Summary -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <h5 class="card-title mb-4">Earnings Summary</h5>
                            <div class="row">
                                <div class="col-md-3">
                                    <div class="border-end">
                                        <h6 class="text-muted mb-2">Total Services</h6>
                                        <h4 class="mb-0"><?php echo number_format($summary['total_services']); ?></h4>
                                        <small class="text-muted"><?php echo $summary['unique_providers']; ?> providers</small>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="border-end">
                                        <h6 class="text-muted mb-2">Gross Earnings</h6>
                                        <h4 class="text-primary mb-0">KES <?php echo number_format($summary['gross_earnings'] ?? 0, 2); ?></h4>
                                        <small class="text-muted">Before deductions</small>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <div class="border-end">
                                        <h6 class="text-muted mb-2">Total Deductions</h6>
                                        <h4 class="text-danger mb-0">KES <?php echo number_format(($summary['total_tax'] + $summary['total_deductions']) ?? 0, 2); ?></h4>
                                        <small class="text-muted">Tax + Other</small>
                                    </div>
                                </div>
                                <div class="col-md-3">
                                    <h6 class="text-muted mb-2">Net Earnings</h6>
                                    <h4 class="text-success mb-0">KES <?php echo number_format($summary['net_earnings'] ?? 0, 2); ?></h4>
                                    <small class="text-muted">After deductions</small>
                                </div>
                            </div>
                            
                            <hr class="my-4">
                            
                            <div class="row">
                                <div class="col-md-6">
                                    <div class="d-flex justify-content-between mb-2">
                                        <span class="text-muted">Service Value:</span>
                                        <strong>KES <?php echo number_format($summary['total_service_value'] ?? 0, 2); ?></strong>
                                    </div>
                                    <div class="d-flex justify-content-between mb-2">
                                        <span class="text-muted">Your Share (Commission):</span>
                                        <strong class="text-primary">KES <?php echo number_format($summary['gross_earnings'] ?? 0, 2); ?></strong>
                                    </div>
                                    <div class="d-flex justify-content-between mb-2">
                                        <span class="text-muted">Less: Tax Withheld:</span>
                                        <strong class="text-danger">KES <?php echo number_format($summary['total_tax'] ?? 0, 2); ?></strong>
                                    </div>
                                    <div class="d-flex justify-content-between mb-2">
                                        <span class="text-muted">Less: Other Deductions:</span>
                                        <strong class="text-danger">KES <?php echo number_format($summary['total_deductions'] ?? 0, 2); ?></strong>
                                    </div>
                                    <div class="d-flex justify-content-between pt-2 border-top">
                                        <strong>Net Payable:</strong>
                                        <strong class="text-success fs-5">KES <?php echo number_format($summary['net_earnings'] ?? 0, 2); ?></strong>
                                    </div>
                                </div>
                                <div class="col-md-6">
                                    <div class="bg-light p-3 rounded">
                                        <h6 class="mb-3">Payment Status</h6>
                                        <div class="d-flex justify-content-between mb-2">
                                            <span class="text-success">Paid:</span>
                                            <strong class="text-success">KES <?php echo number_format($summary['paid_amount'] ?? 0, 2); ?></strong>
                                        </div>
                                        <div class="d-flex justify-content-between">
                                            <span class="text-warning">Pending:</span>
                                            <strong class="text-warning">KES <?php echo number_format($summary['pending_amount'] ?? 0, 2); ?></strong>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Earnings by Provider -->
                    <?php if ($by_provider->num_rows > 0): ?>
                    <div class="card mb-4">
                        <div class="card-body">
                            <h5 class="card-title mb-4">Earnings by Provider</h5>
                            <div class="table-responsive">
                                <table class="table table-bordered">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Provider</th>
                                            <th class="text-end">Services</th>
                                            <th class="text-end">Service Value</th>
                                            <th class="text-end">Gross Earnings</th>
                                            <th class="text-end">Net Earnings</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while ($prov = $by_provider->fetch_assoc()): ?>
                                        <tr>
                                            <td>
                                                <strong><?php echo htmlspecialchars($prov['provider_name']); ?></strong><br>
                                                <small class="text-muted"><?php echo htmlspecialchars($prov['provider_code']); ?></small>
                                            </td>
                                            <td class="text-end"><?php echo number_format($prov['service_count']); ?></td>
                                            <td class="text-end">KES <?php echo number_format($prov['total_value'], 2); ?></td>
                                            <td class="text-end text-primary">KES <?php echo number_format($prov['gross_earnings'], 2); ?></td>
                                            <td class="text-end text-success">KES <?php echo number_format($prov['net_earnings'], 2); ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                    <!-- Payment Batches -->
                    <?php if ($batches->num_rows > 0): ?>
                    <div class="card mb-4">
                        <div class="card-body">
                            <h5 class="card-title mb-4">Payment Batches</h5>
                            <div class="table-responsive">
                                <table class="table table-bordered">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Batch #</th>
                                            <th>Period</th>
                                            <th class="text-end">Allocations</th>
                                            <th class="text-end">Gross Amount</th>
                                            <th class="text-end">Net Payable</th>
                                            <th>Payment Date</th>
                                            <th>Status</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while ($batch = $batches->fetch_assoc()): ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($batch['batch_number']); ?></td>
                                            <td>
                                                <?php echo date('d M', strtotime($batch['payment_period_start'])); ?> - 
                                                <?php echo date('d M Y', strtotime($batch['payment_period_end'])); ?>
                                            </td>
                                            <td class="text-end"><?php echo number_format($batch['allocation_count']); ?></td>
                                            <td class="text-end">KES <?php echo number_format($batch['gross_amount'], 2); ?></td>
                                            <td class="text-end text-success">KES <?php echo number_format($batch['net_payable'], 2); ?></td>
                                            <td><?php echo $batch['payment_date'] ? date('d M Y', strtotime($batch['payment_date'])) : '-'; ?></td>
                                            <td>
                                                <span class="badge bg-<?php 
                                                    echo $batch['status'] === 'PAID' ? 'success' : 
                                                        ($batch['status'] === 'APPROVED' ? 'info' : 'warning'); 
                                                ?>">
                                                    <?php echo str_replace('_', ' ', $batch['status']); ?>
                                                </span>
                                            </td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                    <!-- Detailed Allocations -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <h5 class="card-title mb-4">Service Allocations Detail</h5>
                            <div class="table-responsive">
                                <table class="table table-bordered table-sm">
                                    <thead class="table-light">
                                        <tr>
                                            <th>Date</th>
                                            <th>Allocation #</th>
                                            <th>Provider</th>
                                            <th>Service</th>
                                            <th class="text-end">Service Cost</th>
                                            <th class="text-end">Your Share</th>
                                            <th class="text-end">Tax</th>
                                            <th class="text-end">Net</th>
                                            <th>Payment Status</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php if ($allocations->num_rows > 0): ?>
                                            <?php while ($alloc = $allocations->fetch_assoc()): ?>
                                            <tr>
                                                <td><?php echo date('d M Y', strtotime($alloc['service_date'])); ?></td>
                                                <td><small><?php echo htmlspecialchars($alloc['allocation_number']); ?></small></td>
                                                <td><small><?php echo htmlspecialchars($alloc['provider_name']); ?></small></td>
                                                <td><small><?php echo ucfirst(strtolower($alloc['service_category'])); ?></small></td>
                                                <td class="text-end"><?php echo number_format($alloc['total_service_cost'], 2); ?></td>
                                                <td class="text-end text-primary"><?php echo number_format($alloc['doctor_allocation'], 2); ?></td>
                                                <td class="text-end text-danger"><?php echo number_format($alloc['tax_withheld'], 2); ?></td>
                                                <td class="text-end text-success"><?php echo number_format($alloc['net_doctor_payment'], 2); ?></td>
                                                <td>
                                                    <span class="badge bg-<?php echo $alloc['doctor_payment_status'] === 'PAID' ? 'success' : 'warning'; ?>">
                                                        <?php echo str_replace('_', ' ', $alloc['doctor_payment_status']); ?>
                                                    </span>
                                                </td>
                                            </tr>
                                            <?php endwhile; ?>
                                        <?php else: ?>
                                            <tr>
                                                <td colspan="9" class="text-center text-muted">No allocations in this period</td>
                                            </tr>
                                        <?php endif; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    
                </div>
            </div>
            
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    
    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/libs/flatpickr/flatpickr.min.js"></script>
    
    <script>
        flatpickr('.datepicker', {
            dateFormat: 'Y-m-d',
            allowInput: true
        });
    </script>
</body>
</html>