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

// Check if user is admin
if (!isset($_SESSION['user_id']) || $_SESSION['role_id'] !== 1) {
    header('Location: login.php');
    exit();
}

// Get date range from request, default to last 12 months
$endDate = date('Y-m-d');
$startDate = isset($_GET['startDate']) ? $_GET['startDate'] : date('Y-m-d', strtotime('-12 months'));
$endDate = isset($_GET['endDate']) ? $_GET['endDate'] : $endDate;

// Get summary metrics
$sql = "SELECT 
    COUNT(*) as total_claims,
    SUM(invoiced_amount) as total_amount,
    COUNT(DISTINCT member_no) as unique_members,
    COUNT(DISTINCT provider) as unique_providers,
    AVG(CASE WHEN vet_status = 1 THEN 1 ELSE 0 END) * 100 as approval_rate
FROM bills 
WHERE invoice_date BETWEEN ? AND ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $startDate, $endDate);
$stmt->execute();
$metrics = $stmt->get_result()->fetch_assoc();

// Get fraud metrics
$sql = "WITH monthly_metrics AS (
    SELECT 
        DATE_FORMAT(invoice_date, '%Y-%m') as month,
        COUNT(*) as total_claims,
        SUM(CASE WHEN invoiced_amount > (
            SELECT AVG(invoiced_amount) + 2*STDDEV(invoiced_amount) 
            FROM bills WHERE invoice_date BETWEEN ? AND ?
        ) THEN 1 ELSE 0 END) as high_value_outliers,
        COUNT(*) - COUNT(DISTINCT claim_no) as duplicate_claims
    FROM bills
    WHERE invoice_date BETWEEN ? AND ?
    GROUP BY DATE_FORMAT(invoice_date, '%Y-%m')
)
SELECT 
    month,
    high_value_outliers,
    duplicate_claims,
    ROUND(((high_value_outliers + duplicate_claims) / total_claims) * 100, 2) as fraud_risk_pct
FROM monthly_metrics
ORDER BY month";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ssss', $startDate, $endDate, $startDate, $endDate);
$stmt->execute();
$fraudMetrics = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Get provider utilization trends
$sql = "SELECT 
    p.provider_name,
    COUNT(*) as claim_count,
    SUM(b.invoiced_amount) as total_amount,
    AVG(b.invoiced_amount) as avg_amount,
    COUNT(DISTINCT b.member_no) as unique_patients,
    SUM(CASE WHEN b.vet_status = 2 THEN 1 ELSE 0 END) as rejected_claims
FROM bills b
JOIN providers p ON b.provider = p.provider_id
WHERE b.invoice_date BETWEEN ? AND ?
GROUP BY p.provider_id, p.provider_name
ORDER BY claim_count DESC
LIMIT 10";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $startDate, $endDate);
$stmt->execute();
$providerTrends = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Get disease patterns
$sql = "SELECT 
    claim_nature,
    COUNT(*) as frequency,
    AVG(invoiced_amount) as avg_cost,
    COUNT(DISTINCT member_no) as affected_members,
    SUM(CASE WHEN vet_status = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100 as approval_rate
FROM bills
WHERE invoice_date BETWEEN ? AND ?
GROUP BY claim_nature
ORDER BY frequency DESC
LIMIT 10";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $startDate, $endDate);
$stmt->execute();
$diseasePatterns = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

$conn->close();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Claims Analytics')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet" type="text/css" />
    <style>
        .metric-card {
            transition: all 0.3s ease;
        }
        .metric-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
        }
        .table-hover tbody tr:hover {
            background-color: rgba(0,0,0,0.02);
        }
        .risk-high { background-color: #ffebee; }
        .risk-medium { background-color: #fff3e0; }
        .risk-low { background-color: #f1f8e9; }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Claims', 'title' => 'Claims Analytics Dashboard')); ?>

                    <!-- Date Filter -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <form method="GET" class="row gy-2 gx-3 align-items-center">
                                        <div class="col-auto">
                                            <label class="form-label">Start Date</label>
                                            <input type="date" class="form-control" name="startDate" 
                                                   value="<?php echo $startDate; ?>">
                                        </div>
                                        <div class="col-auto">
                                            <label class="form-label">End Date</label>
                                            <input type="date" class="form-control" name="endDate" 
                                                   value="<?php echo $endDate; ?>">
                                        </div>
                                        <div class="col-auto">
                                            <label class="form-label">&nbsp;</label>
                                            <button type="submit" class="btn btn-primary d-block">
                                                Update Analytics
                                            </button>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Metrics -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card metric-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Total Claims</h6>
                                            <h2 class="mb-0"><?php echo number_format($metrics['total_claims']); ?></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-primary-subtle text-primary rounded fs-3">
                                                    <i class="bx bx-file"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card metric-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Total Amount</h6>
                                            <h2 class="mb-0"><?php echo number_format($metrics['total_amount'], 2); ?></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-success-subtle text-success rounded fs-3">
                                                    <i class="bx bx-money"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card metric-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Unique Members</h6>
                                            <h2 class="mb-0"><?php echo number_format($metrics['unique_members']); ?></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-info-subtle text-info rounded fs-3">
                                                    <i class="bx bx-user-circle"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6 mb-4">
                            <div class="card metric-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Approval Rate</h6>
                                            <h2 class="mb-0"><?php echo number_format($metrics['approval_rate'], 1); ?>%</h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm">
                                                <span class="avatar-title bg-warning-subtle text-warning rounded fs-3">
                                                    <i class="bx bx-check-circle"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Fraud Detection -->
                    <div class="row">
                        <div class="col-xl-6 mb-4">
                            <div class="card h-100">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Fraud Detection Patterns</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover table-nowrap mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Month</th>
                                                    <th>High Value Outliers</th>
                                                    <th>Duplicate Claims</th>
                                                    <th>Risk Level</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($fraudMetrics as $metric): 
                                                    $riskClass = $metric['fraud_risk_pct'] > 10 ? 'risk-high' : 
                                                                ($metric['fraud_risk_pct'] > 5 ? 'risk-medium' : 'risk-low');
                                                ?>
                                                <tr class="<?php echo $riskClass; ?>">
                                                    <td><?php echo $metric['month']; ?></td>
                                                    <td><?php echo $metric['high_value_outliers']; ?></td>
                                                    <td><?php echo $metric['duplicate_claims']; ?></td>
                                                    <td><?php echo $metric['fraud_risk_pct']; ?>%</td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <!-- Provider Utilization -->
                        <div class="col-xl-6 mb-4">
                            <div class="card h-100">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Provider Utilization Trends</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover table-nowrap mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Provider</th>
                                                    <th>Claims</th>
                                                    <th>Amount</th>
                                                    <th>Rejection Rate</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($providerTrends as $trend): 
                                                    $rejectionRate = ($trend['rejected_claims'] / $trend['claim_count']) * 100;
                                                    $riskClass = $rejectionRate > 20 ? 'risk-high' : 
                                                                ($rejectionRate > 10 ? 'risk-medium' : 'risk-low');
                                                ?>
                                                <tr class="<?php echo $riskClass; ?>">
                                                    <td><?php echo $trend['provider_name']; ?></td>
                                                    <td><?php echo number_format($trend['claim_count']); ?></td>
                                                    <td><?php echo number_format($trend['total_amount'], 2); ?></td>
                                                    <td><?php echo number_format($rejectionRate, 1); ?>%</td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Disease Patterns -->
                    <div class="row">
                        <div class="col-12 mb-4">
                            <div class="card">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Disease Pattern Recognition</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover table-nowrap mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Disease/Condition</th>
                                                    <th>Frequency</th>
                                                    <th>Average Cost</th>
                                                    <th>Members Affected</th>
                                                    <th>Approval Rate</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($diseasePatterns as $pattern): ?>
                                                <tr>
                                                    <td><?php echo htmlspecialchars($pattern['claim_nature']); ?></td>
                                                    <td><?php echo number_format($pattern['frequency']); ?></td>
                                                    <td><?php echo number_format($pattern['avg_cost'], 2); ?></td>
                                                    <td><?php echo number_format($pattern['affected_members']); ?></td>
                                                    <td>
                                                        <div class="progress" style="height: 20px;">
                                                            <div class="progress-bar bg-success" role="progressbar" 
                                                                 style="width: <?php echo $pattern['approval_rate']; ?>%">
                                                                <?php echo number_format($pattern['approval_rate'], 1); ?>%
                                                            </div>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Export Buttons -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="text-end">
                                <a href="export_claims_analytics.php?startDate=<?php echo $startDate; ?>&endDate=<?php echo $endDate; ?>" 
                                   class="btn btn-success">
                                    <i class="ri-file-excel-2-line align-middle me-1"></i> Export to Excel
                                </a>
                                <a href="export_claims_analytics.php?format=pdf&startDate=<?php echo $startDate; ?>&endDate=<?php echo $endDate; ?>" 
                                   class="btn btn-danger ms-2">
                                    <i class="ri-file-pdf-line align-middle me-1"></i> Export to PDF
                                </a>
                            </div>
                        </div>
                    </div>

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

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
<script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            // Add hover effect to metric cards
            const metricCards = document.querySelectorAll('.metric-card');
            metricCards.forEach(card => {
                card.addEventListener('mouseenter', function() {
                    this.style.transform = 'translateY(-5px)';
                });
                card.addEventListener('mouseleave', function() {
                    this.style.transform = 'translateY(0)';
                });
            });

            // Initialize date range validation
            const startDate = document.querySelector('input[name="startDate"]');
            const endDate = document.querySelector('input[name="endDate"]');

            if (startDate && endDate) {
                endDate.addEventListener('change', function() {
                    if (startDate.value && this.value < startDate.value) {
                        Swal.fire({
                            icon: 'error',
                            title: 'Invalid Date Range',
                            text: 'End date cannot be earlier than start date'
                        });
                        this.value = '';
                    }
                });

                startDate.addEventListener('change', function() {
                    if (endDate.value && this.value > endDate.value) {
                        endDate.value = '';
                    }
                });
            }

            // Add drill-down functionality for high-risk items
            const riskRows = document.querySelectorAll('.risk-high');
            riskRows.forEach(row => {
                row.style.cursor = 'pointer';
                row.addEventListener('click', function() {
                    const data = {
                        month: row.cells[0].textContent,
                        outliers: row.cells[1].textContent,
                        duplicates: row.cells[2].textContent,
                        risk: row.cells[3].textContent
                    };

                    Swal.fire({
                        title: 'Risk Details',
                        html: `
                            <div class="table-responsive">
                                <table class="table table-bordered">
                                    <tr>
                                        <th>Period</th>
                                        <td>${data.month}</td>
                                    </tr>
                                    <tr>
                                        <th>High Value Outliers</th>
                                        <td>${data.outliers}</td>
                                    </tr>
                                    <tr>
                                        <th>Duplicate Claims</th>
                                        <td>${data.duplicates}</td>
                                    </tr>
                                    <tr>
                                        <th>Risk Level</th>
                                        <td>${data.risk}</td>
                                    </tr>
                                </table>
                            </div>
                        `,
                        confirmButtonText: 'Close'
                    });
                });
            });

            // Add export confirmation
            const exportButtons = document.querySelectorAll('a[href*="export_claims_analytics.php"]');
            exportButtons.forEach(button => {
                button.addEventListener('click', function(e) {
                    e.preventDefault();
                    const format = this.href.includes('format=pdf') ? 'PDF' : 'Excel';
                    
                    Swal.fire({
                        title: `Export to ${format}?`,
                        text: 'This may take a few moments depending on the date range',
                        icon: 'question',
                        showCancelButton: true,
                        confirmButtonText: `Export to ${format}`,
                        cancelButtonText: 'Cancel'
                    }).then((result) => {
                        if (result.isConfirmed) {
                            window.location.href = this.href;
                        }
                    });
                });
            });
        });
    </script>
</body>
</html>