<?php
// payment_allocation_reports.php - Payment Allocation Reports
include 'layouts/session.php';
include 'layouts/dbconn.php';

$report_type = isset($_GET['report_type']) ? $_GET['report_type'] : 'summary';
$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');

// Summary Report
if ($report_type === 'summary') {
    $summary_sql = "SELECT 
        COUNT(DISTINCT bp.bulk_payment_id) as total_payments,
        SUM(bp.total_amount) as total_received,
        COUNT(pa.allocation_id) as total_allocations,
        SUM(pa.total_service_cost) as total_allocated,
        SUM(pa.doctor_allocation) as total_doctor_earnings,
        SUM(pa.facility_allocation) as total_facility_earnings,
        SUM(pa.tax_withheld) as total_tax,
        COUNT(DISTINCT pa.doctor_id) as active_doctors,
        COUNT(DISTINCT pa.provider_id) as active_providers
    FROM bulk_payments bp
    LEFT JOIN payment_allocations pa ON bp.bulk_payment_id = pa.bulk_payment_id 
        AND pa.allocation_status NOT IN ('CANCELLED', 'REJECTED')
    WHERE bp.payment_date BETWEEN ? AND ?";
    
    $stmt = $conn->prepare($summary_sql);
    $stmt->bind_param('ss', $date_from, $date_to);
    $stmt->execute();
    $summary = $stmt->get_result()->fetch_assoc();
}

// By Provider Report
elseif ($report_type === 'by_provider') {
    $provider_sql = "SELECT 
        esp.provider_name,
        esp.provider_code,
        COUNT(DISTINCT bp.bulk_payment_id) as payment_count,
        SUM(bp.total_amount) as total_paid,
        COUNT(pa.allocation_id) as allocation_count,
        SUM(pa.total_service_cost) as services_value,
        SUM(pa.doctor_allocation) as doctor_share,
        SUM(pa.facility_allocation) as facility_share
    FROM external_service_providers esp
    LEFT JOIN bulk_payments bp ON esp.provider_id = bp.provider_id 
        AND bp.payment_date BETWEEN ? AND ?
    LEFT JOIN payment_allocations pa ON bp.bulk_payment_id = pa.bulk_payment_id
        AND pa.allocation_status NOT IN ('CANCELLED', 'REJECTED')
    WHERE esp.is_active = 1
    GROUP BY esp.provider_id
    ORDER BY total_paid DESC";
    
    $stmt = $conn->prepare($provider_sql);
    $stmt->bind_param('ss', $date_from, $date_to);
    $stmt->execute();
    $providers = $stmt->get_result();
}

// By Doctor Report
elseif ($report_type === 'by_doctor') {
    $doctor_sql = "SELECT 
        u.full_name as doctor_name,
        d.specialization,
        COUNT(pa.allocation_id) as service_count,
        SUM(pa.doctor_allocation) as gross_earnings,
        SUM(pa.tax_withheld) as tax_deducted,
        SUM(pa.other_deductions) as other_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 doctors d
    JOIN users u ON d.user_id = u.user_id
    LEFT JOIN payment_allocations pa ON d.doctor_id = pa.doctor_id
        AND pa.service_date BETWEEN ? AND ?
        AND pa.allocation_status = 'APPROVED'
    WHERE d.is_active = 1
    GROUP BY d.doctor_id
    HAVING service_count > 0
    ORDER BY net_earnings DESC";
    
    $stmt = $conn->prepare($doctor_sql);
    $stmt->bind_param('ss', $date_from, $date_to);
    $stmt->execute();
    $doctors = $stmt->get_result();
}
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Payment Reports')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/flatpickr/flatpickr.min.css" rel="stylesheet">
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    
                    <div class="page-header" style="background: linear-gradient(135deg, #8b5cf6 0%, #6366f1 100%); padding: 2rem; border-radius: 12px; color: white; margin-bottom: 2rem;">
                        <h3 class="text-white mb-2"><i class="ri-bar-chart-line me-2"></i>Payment Allocation Reports</h3>
                        <p class="text-white opacity-75 mb-0">Comprehensive payment and allocation analytics</p>
                    </div>
                    
                    <!-- Report Selection and Filters -->
                    <div class="card mb-4">
                        <div class="card-body">
                            <form method="GET" action="">
                                <div class="row g-3">
                                    <div class="col-md-4">
                                        <label class="form-label">Report Type</label>
                                        <select class="form-select" name="report_type">
                                            <option value="summary" <?php echo $report_type === 'summary' ? 'selected' : ''; ?>>Summary Overview</option>
                                            <option value="by_provider" <?php echo $report_type === 'by_provider' ? 'selected' : ''; ?>>By Provider</option>
                                            <option value="by_doctor" <?php echo $report_type === 'by_doctor' ? 'selected' : ''; ?>>By Doctor</option>
                                        </select>
                                    </div>
                                    <div class="col-md-3">
                                        <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-3">
                                        <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-2 d-flex align-items-end">
                                        <button type="submit" class="btn btn-primary w-100">
                                            <i class="ri-search-line me-1"></i>Generate
                                        </button>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>
                    
                    <!-- Summary Report -->
                    <?php if ($report_type === 'summary'): ?>
                    <div class="row">
                        <div class="col-md-3">
                            <div class="card">
                                <div class="card-body">
                                    <h6 class="text-muted mb-2">Total Payments Received</h6>
                                    <h3 class="mb-0"><?php echo number_format($summary['total_payments']); ?></h3>
                                    <small class="text-success">KES <?php echo number_format($summary['total_received'] ?? 0, 0); ?></small>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="card">
                                <div class="card-body">
                                    <h6 class="text-muted mb-2">Total Allocations</h6>
                                    <h3 class="mb-0"><?php echo number_format($summary['total_allocations']); ?></h3>
                                    <small class="text-primary">KES <?php echo number_format($summary['total_allocated'] ?? 0, 0); ?></small>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="card">
                                <div class="card-body">
                                    <h6 class="text-muted mb-2">Doctor Earnings</h6>
                                    <h3 class="mb-0 text-primary">KES <?php echo number_format($summary['total_doctor_earnings'] ?? 0, 0); ?></h3>
                                    <small class="text-muted"><?php echo $summary['active_doctors']; ?> doctors</small>
                                </div>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="card">
                                <div class="card-body">
                                    <h6 class="text-muted mb-2">Facility Earnings</h6>
                                    <h3 class="mb-0 text-success">KES <?php echo number_format($summary['total_facility_earnings'] ?? 0, 0); ?></h3>
                                    <small class="text-muted">Prodigy share</small>
                                </div>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                    <!-- By Provider Report -->
                    <?php if ($report_type === 'by_provider' && $providers->num_rows > 0): ?>
                    <div class="card">
                        <div class="card-body">
                            <h5 class="card-title mb-4">Payments by Provider</h5>
                            <div class="table-responsive">
                                <table class="table table-hover">
                                    <thead>
                                        <tr>
                                            <th>Provider</th>
                                            <th class="text-end">Payments</th>
                                            <th class="text-end">Total Paid</th>
                                            <th class="text-end">Allocations</th>
                                            <th class="text-end">Services Value</th>
                                            <th class="text-end">Doctor Share</th>
                                            <th class="text-end">Facility Share</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while ($prov = $providers->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['payment_count']); ?></td>
                                            <td class="text-end">KES <?php echo number_format($prov['total_paid'] ?? 0, 2); ?></td>
                                            <td class="text-end"><?php echo number_format($prov['allocation_count']); ?></td>
                                            <td class="text-end">KES <?php echo number_format($prov['services_value'] ?? 0, 2); ?></td>
                                            <td class="text-end text-primary">KES <?php echo number_format($prov['doctor_share'] ?? 0, 2); ?></td>
                                            <td class="text-end text-success">KES <?php echo number_format($prov['facility_share'] ?? 0, 2); ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                    <!-- By Doctor Report -->
                    <?php if ($report_type === 'by_doctor' && $doctors->num_rows > 0): ?>
                    <div class="card">
                        <div class="card-body">
                            <h5 class="card-title mb-4">Earnings by Doctor</h5>
                            <div class="table-responsive">
                                <table class="table table-hover">
                                    <thead>
                                        <tr>
                                            <th>Doctor</th>
                                            <th class="text-end">Services</th>
                                            <th class="text-end">Gross Earnings</th>
                                            <th class="text-end">Tax</th>
                                            <th class="text-end">Deductions</th>
                                            <th class="text-end">Net Earnings</th>
                                            <th class="text-end">Paid</th>
                                            <th class="text-end">Pending</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while ($doc = $doctors->fetch_assoc()): ?>
                                        <tr>
                                            <td>
                                                <strong><?php echo htmlspecialchars($doc['doctor_name']); ?></strong>
                                                <?php if ($doc['specialization']): ?>
                                                    <br><small class="text-muted"><?php echo htmlspecialchars($doc['specialization']); ?></small>
                                                <?php endif; ?>
                                            </td>
                                            <td class="text-end"><?php echo number_format($doc['service_count']); ?></td>
                                            <td class="text-end">KES <?php echo number_format($doc['gross_earnings'] ?? 0, 2); ?></td>
                                            <td class="text-end text-danger">KES <?php echo number_format($doc['tax_deducted'] ?? 0, 2); ?></td>
                                            <td class="text-end text-danger">KES <?php echo number_format($doc['other_deductions'] ?? 0, 2); ?></td>
                                            <td class="text-end"><strong>KES <?php echo number_format($doc['net_earnings'] ?? 0, 2); ?></strong></td>
                                            <td class="text-end text-success">KES <?php echo number_format($doc['paid_amount'] ?? 0, 2); ?></td>
                                            <td class="text-end text-warning">KES <?php echo number_format($doc['pending_amount'] ?? 0, 2); ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>
                    
                </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' });
    </script>
</body>
</html>