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

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Fetch user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT username, role_id, full_name, email FROM users WHERE user_id = $user_id";
$result_user_details = $conn->query($sql_user_details);

if ($result_user_details && $row_user_details = $result_user_details->fetch_assoc()) {
    $username = $row_user_details['username'];
    $user_role = $row_user_details['role_id'];
    $full_name = $row_user_details['full_name'];
    $email = $row_user_details['email'];
}

// Get total active corporates
$sql_corporates = "SELECT COUNT(*) as total_corporates FROM corporate WHERE cancelled = 0";
$result_corporates = $conn->query($sql_corporates);
$total_corporates = $result_corporates->fetch_assoc()['total_corporates'];

// Get total members
$sql_members = "SELECT COUNT(*) as total_members FROM member_info WHERE status = 1";
$result_members = $conn->query($sql_members);
$total_members = $result_members->fetch_assoc()['total_members'];

// Get claims statistics
$sql_claims = "SELECT 
    COUNT(*) as total_claims,
    SUM(invoiced_amount) as total_amount,
    SUM(CASE WHEN vet_status = 1 THEN 1 ELSE 0 END) as approved_claims,
    SUM(CASE WHEN paid = 1 THEN invoiced_amount ELSE 0 END) as paid_amount,
    SUM(CASE WHEN vet_status = 0 THEN 1 ELSE 0 END) as pending_claims,
    COUNT(DISTINCT member_no) as unique_members
FROM bills 
WHERE vet_status != 2"; // Exclude rejected claims
$result_claims = $conn->query($sql_claims);
$claims_data = $result_claims->fetch_assoc();

// Get monthly claims trends with status
$sql_monthly_claims = "SELECT 
    DATE_FORMAT(invoice_date, '%Y-%m') as month,
    COUNT(*) as claim_count,
    SUM(CASE WHEN vet_status != 2 THEN invoiced_amount ELSE 0 END) as total_amount,
    SUM(CASE WHEN vet_status = 1 THEN 1 ELSE 0 END) as approved_count,
    SUM(CASE WHEN paid = 1 THEN 1 ELSE 0 END) as paid_count,
    SUM(CASE WHEN vet_status = 0 THEN 1 ELSE 0 END) as pending_count
FROM bills
WHERE invoice_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(invoice_date, '%Y-%m')
ORDER BY month";
$result_monthly_claims = $conn->query($sql_monthly_claims);
$monthly_claims_data = [];
while ($row = $result_monthly_claims->fetch_assoc()) {
    $monthly_claims_data[] = $row;
}

// Get age distribution
$sql_age_groups = "SELECT 
    CASE 
        WHEN age <= 18 THEN '0-18'
        WHEN age <= 30 THEN '19-30'
        WHEN age <= 45 THEN '31-45'
        WHEN age <= 60 THEN '46-60'
        ELSE '60+'
    END as age_group,
    COUNT(*) as member_count
FROM member_info
WHERE status = 1
GROUP BY age_group
ORDER BY 
    CASE age_group
        WHEN '0-18' THEN 1
        WHEN '19-30' THEN 2
        WHEN '31-45' THEN 3
        WHEN '46-60' THEN 4
        ELSE 5
    END";
$result_age_groups = $conn->query($sql_age_groups);
$age_groups_data = [];
while ($row = $result_age_groups->fetch_assoc()) {
    $age_groups_data[] = $row;
}

// Get claims by benefit type with proper join and status check
$sql_benefit_claims = "SELECT 
    b.code,
    b.benefit as benefit_name,
    COUNT(DISTINCT CASE WHEN bl.vet_status != 2 THEN bl.claim_no END) as claim_count,
    SUM(CASE WHEN bl.vet_status != 2 THEN bl.invoiced_amount ELSE 0 END) as total_amount,
    COUNT(DISTINCT bl.member_no) as unique_members
FROM benefit b
LEFT JOIN bills bl ON b.code = bl.claim_nature
WHERE bl.vet_status != 2 OR bl.vet_status IS NULL
GROUP BY b.code, b.benefit
ORDER BY total_amount DESC";
$result_benefit_claims = $conn->query($sql_benefit_claims);
$benefit_claims_data = [];
while ($row = $result_benefit_claims->fetch_assoc()) {
    $benefit_claims_data[] = $row;
}

// Get recent claims with status
$sql_recent_claims = "SELECT 
    b.claim_no,
    b.invoice_no,
    b.invoice_date,
    b.invoiced_amount,
    b.member_no,
    b.vet_status,
    b.paid,
    m.surname,
    m.first_name,
    c.corporate
FROM bills b
LEFT JOIN member_info m ON b.member_no = m.member_no
LEFT JOIN corporate c ON b.corp_id = c.corp_id
WHERE b.vet_status != 2
ORDER BY b.invoice_date DESC
LIMIT 5";
$result_recent_claims = $conn->query($sql_recent_claims);

// Get corporate statistics with proper status check
$sql_corporate_stats = "SELECT 
    c.corporate,
    COUNT(DISTINCT m.member_no) as member_count,
    COUNT(DISTINCT CASE WHEN b.vet_status != 2 THEN b.claim_no END) as claims_count,
    SUM(CASE WHEN b.vet_status != 2 THEN b.invoiced_amount ELSE 0 END) as total_claims_amount,
    c.corp_id
FROM corporate c
LEFT JOIN member_info m ON c.corp_id = m.corp_id AND m.status = 1
LEFT JOIN bills b ON c.corp_id = b.corp_id AND b.vet_status != 2
WHERE c.cancelled = 0
GROUP BY c.corp_id, c.corporate
ORDER BY total_claims_amount DESC
LIMIT 5";
$result_corporate_stats = $conn->query($sql_corporate_stats);



$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Dashboard')); ?>
    <!-- apexcharts css -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.css">
    <?php include 'layouts/head-css.php'; ?>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Welcome Message -->
                    <div class="row">
                        <div class="col-12">
                            <div class="d-flex align-items-lg-center flex-lg-row flex-column">
                                <div class="flex-grow-1">
                                    <h4 class="fs-16 mb-1">
                                        <?php echo date('H') < 12 ? 'Good Morning' : (date('H') < 17 ? 'Good Afternoon' : 'Good Evening'); ?>, 
                                        <?php echo $full_name; ?>!
                                    </h4>
                                    <p class="text-muted mb-0">Here's what's happening with your insurance dashboard today.</p>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Key Metrics Cards -->
                    <div class="row mt-4">
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Total Corporates</p>
                                        </div>
                                    </div>
                                    <div class="d-flex align-items-end justify-content-between mt-4">
                                        <div>
                                            <h4 class="fs-22 fw-semibold ff-secondary mb-4">
                                                <span class="counter-value" data-target="<?php echo $total_corporates; ?>">0</span>
                                            </h4>
                                            <a href="all_corporates.php" class="text-decoration-underline">View Corporates</a>
                                        </div>
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-primary-subtle rounded fs-3">
                                                <i class="bx bx-building-house text-primary"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Total Members</p>
                                        </div>
                                    </div>
                                    <div class="d-flex align-items-end justify-content-between mt-4">
                                        <div>
                                            <h4 class="fs-22 fw-semibold ff-secondary mb-4">
                                                <span class="counter-value" data-target="<?php echo $total_members; ?>">0</span>
                                            </h4>
                                            <a href="all_members.php" class="text-decoration-underline">View Members</a>
                                        </div>
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-success-subtle rounded fs-3">
                                                <i class="bx bx-user-circle text-success"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Claims Processing</p>
                                        </div>
                                    </div>
                                    <div class="d-flex align-items-end justify-content-between mt-4">
                                        <div>
                                            <h4 class="fs-22 fw-semibold ff-secondary mb-4">
                                                <span class="counter-value" data-target="<?php echo $claims_data['total_claims']; ?>">0</span>
                                            </h4>
                                            <p class="mb-0 text-muted">
                                                <span class="badge bg-success-subtle text-success">
                                                    <?php echo $claims_data['approved_claims']; ?> Approved
                                                </span>
                                                <span class="badge bg-warning-subtle text-warning ms-1">
                                                    <?php echo $claims_data['pending_claims']; ?> Pending
                                                </span>
                                            </p>
                                        </div>
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-info-subtle rounded fs-3">
                                                <i class="bx bx-file text-info"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Claims Amount</p>
                                        </div>
                                    </div>
                                    <div class="d-flex align-items-end justify-content-between mt-4">
                                        <div>
                                            <h4 class="fs-22 fw-semibold ff-secondary mb-4">
                                                Ksh <span class="counter-value" data-target="<?php echo $claims_data['total_amount']; ?>">0</span>
                                            </h4>
                                            <p class="mb-0 text-muted">
                                                <span class="text-success">
                                                    Ksh <?php echo number_format($claims_data['paid_amount']); ?> Paid
                                                </span>
                                            </p>
                                        </div>
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-warning-subtle rounded fs-3">
                                                <i class="bx bx-money text-warning"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Claims Analytics -->
                    <div class="row">
                        <div class="col-xl-8">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Claims Analytics</h4>
                                </div>
                                <div class="card-body">
                                    <div id="claims-analytics" class="apex-charts" dir="ltr"></div>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-4">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Members Age Distribution</h4>
                                </div>
                                <div class="card-body">
                                    <div id="age-distribution" class="apex-charts" dir="ltr"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Recent Claims -->
                    <div class="row">
                        <div class="col-xl-8">
                            <div class="card">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Recent Claims</h4>
                                    <div class="flex-shrink-0">
                                        <a href="all_claims.php" class="btn btn-soft-primary btn-sm">View All Claims</a>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-borderless table-hover table-centered align-middle table-nowrap mb-0">
                                            <thead class="text-muted table-light">
                                                <tr>
                                                    <th scope="col">Claim No</th>
                                                    <th scope="col">Member</th>
                                                    <th scope="col">Corporate</th>
                                                    <th scope="col">Date</th>
                                                    <th scope="col">Amount</th>
                                                    <th scope="col">Status</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while($row = $result_recent_claims->fetch_assoc()): ?>
                                                <tr>
                                                    <td>
                                                        <a href="view_claim.php?id=<?php echo $row['claim_no']; ?>" class="fw-medium link-primary">
                                                            <?php echo $row['claim_no']; ?>
                                                        </a>
                                                    </td>
                                                    <td><?php echo $row['surname'] . ' ' . $row['first_name']; ?></td>
                                                    <td><?php echo $row['corporate']; ?></td>
                                                    <td><?php echo date('d M Y', strtotime($row['invoice_date'])); ?></td>
                                                    <td>Ksh <?php echo number_format($row['invoiced_amount'], 2); ?></td>
                                                    <td>
                                                        <?php if($row['paid']): ?>
                                                            <span class="badge bg-success">Paid</span>
                                                        <?php elseif($row['vet_status']): ?>
                                                            <span class="badge bg-danger">Approved</span>
                                                        <?php else: ?>
                                                            <span class="badge bg-warning">Pending</span>
                                                        <?php endif; ?>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <div class="card">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Benefit Distribution</h4>
                                </div>
                                <div class="card-body">
                                    <div id="benefit-distribution" class="apex-charts" dir="ltr"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Corporate Performance -->
                    <div class="row">
                        <div class="col-xl-12">
                            <div class="card">
                                <div class="card-header align-items-center d-flex">
                                    <h4 class="card-title mb-0 flex-grow-1">Corporate Performance</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover table-centered align-middle table-nowrap mb-0">
                                            <thead class="text-muted table-light">
                                                <tr>
                                                    <th scope="col">Corporate</th>
                                                    <th scope="col">Members</th>
                                                    <th scope="col">Claims</th>
                                                    <th scope="col">Total Amount</th>
                                                    <th scope="col">Claims Per Member</th>
                                                    <th scope="col">Amount Per Member</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while($row = $result_corporate_stats->fetch_assoc()): 
                                                    $claims_per_member = $row['member_count'] > 0 ? round($row['claims_count'] / $row['member_count'], 2) : 0;
                                                    $amount_per_member = $row['member_count'] > 0 ? round($row['total_claims_amount'] / $row['member_count'], 2) : 0;
                                                ?>
                                                <tr>
                                                    <td>
                                                        <a href="view_corporate.php?id=<?php echo $row['corp_id']; ?>" class="fw-medium link-primary">
                                                            <?php echo $row['corporate']; ?>
                                                        </a>
                                                    </td>
                                                    <td><?php echo number_format($row['member_count']); ?></td>
                                                    <td><?php echo number_format($row['claims_count']); ?></td>
                                                    <td>Ksh <?php echo number_format($row['total_claims_amount'], 2); ?></td>
                                                    <td><?php echo $claims_per_member; ?></td>
                                                    <td>Ksh <?php echo number_format($amount_per_member, 2); ?></td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script>
document.addEventListener('DOMContentLoaded', function() {
    // Charts code
    var options = {
        series: [{
            name: 'Amount (KSH)',
            type: 'column',
            data: <?php echo json_encode(array_column($monthly_claims_data, 'total_amount')); ?>
        }, {
            name: 'Claims Count',
            type: 'line',
            data: <?php echo json_encode(array_column($monthly_claims_data, 'claim_count')); ?>
        }],
        chart: {
            height: 350,
            type: 'line',
            toolbar: {
                show: false
            }
        },
        stroke: {
            curve: 'smooth',
            width: [0, 3]
        },
        plotOptions: {
            bar: {
                columnWidth: '50%'
            }
        },
        fill: {
            opacity: [0.85, 1]
        },
        labels: <?php echo json_encode(array_column($monthly_claims_data, 'month')); ?>,
        markers: {
            size: 4
        },
        xaxis: {
            type: 'category'
        },
        yaxis: [{
            title: {
                text: 'Amount (KSH)',
            },
        }, {
            opposite: true,
            title: {
                text: 'Number of Claims'
            }
        }],
        tooltip: {
            shared: true,
            intersect: false,
            y: [{
                formatter: function (y) {
                    if(typeof y !== "undefined") {
                        return "KSH " + y.toFixed(2);
                    }
                    return y;
                }
            }, {
                formatter: function (y) {
                    if(typeof y !== "undefined") {
                        return y.toFixed(0) + " claims";
                    }
                    return y;
                }
            }]
        }
    };

    try {
        var claimsChart = new ApexCharts(document.querySelector("#claims-analytics"), options);
        claimsChart.render();
    } catch(e) {
        console.error("Error rendering claims chart:", e);
    }

    // Age Distribution Chart
    var ageOptions = {
        series: <?php echo json_encode(array_column($age_groups_data, 'member_count')); ?>,
        chart: {
            type: 'donut',
            height: 350
        },
        labels: <?php echo json_encode(array_column($age_groups_data, 'age_group')); ?>,
        plotOptions: {
            pie: {
                donut: {
                    size: '70%'
                }
            }
        },
        legend: {
            position: 'bottom'
        },
        colors: ['#3577f1', '#0ab39c', '#f7b84b', '#f06548', '#299cdb']
    };

    try {
        var ageChart = new ApexCharts(document.querySelector("#age-distribution"), ageOptions);
        ageChart.render();
    } catch(e) {
        console.error("Error rendering age chart:", e);
    }

    // Benefit Distribution Chart
    var benefitOptions = {
        series: [{
            name: 'Claims Amount',
            data: <?php echo json_encode(array_column($benefit_claims_data, 'total_amount')); ?>
        }],
        chart: {
            type: 'bar',
            height: 350
        },
        plotOptions: {
            bar: {
                horizontal: false,
                columnWidth: '55%',
                endingShape: 'rounded'
            }
        },
        dataLabels: {
            enabled: false
        },
        xaxis: {
            categories: <?php echo json_encode(array_column($benefit_claims_data, 'benefit_name')); ?>,
        },
        yaxis: {
            title: {
                text: 'Amount (KSH)'
            }
        },
        fill: {
            opacity: 1
        },
        tooltip: {
            y: {
                formatter: function(value) {
                    return "KSH " + value.toFixed(2);
                }
            }
        }
    };

    try {
        var benefitChart = new ApexCharts(document.querySelector("#benefit-distribution"), benefitOptions);
        benefitChart.render();
    } catch(e) {
        console.error("Error rendering benefit chart:", e);
    }
});
</script>

    <!-- App js -->
    <script src="assets/js/app.js"></script>
    <!-- apexcharts js -->
<script src="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.min.js"></script>
</body>
</html>