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

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

// Check permissions
/*$user_id = $_SESSION['user_id'];
$stmt = $conn->prepare("SELECT HasPermission(?, 'commissions_view') as has_permission");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$permission_result = $stmt->get_result()->fetch_assoc();

if (!$permission_result['has_permission']) {
    header('Location: index.php?error=no_permission');
    exit();
}*/

// Get filter parameters
$agent_filter = $_GET['agent'] ?? '';
$period_filter = $_GET['period'] ?? 'current_month';
$status_filter = $_GET['status'] ?? '';
$year_filter = $_GET['year'] ?? date('Y');

// Calculate date range based on period
$start_date = '';
$end_date = '';
switch ($period_filter) {
    case 'current_month':
        $start_date = date('Y-m-01');
        $end_date = date('Y-m-t');
        break;
    case 'last_month':
        $start_date = date('Y-m-01', strtotime('last month'));
        $end_date = date('Y-m-t', strtotime('last month'));
        break;
    case 'current_quarter':
        $quarter = ceil(date('n') / 3);
        $start_date = date('Y') . '-' . str_pad(($quarter - 1) * 3 + 1, 2, '0', STR_PAD_LEFT) . '-01';
        $end_date = date('Y-m-t', strtotime($start_date . ' +2 months'));
        break;
    case 'current_year':
        $start_date = $year_filter . '-01-01';
        $end_date = $year_filter . '-12-31';
        break;
    case 'custom':
        $start_date = $_GET['start_date'] ?? date('Y-m-01');
        $end_date = $_GET['end_date'] ?? date('Y-m-t');
        break;
}

// Build filter conditions
$where_conditions = ["u.agent_code IS NOT NULL"];
$params = [];
$param_types = "";

if (!empty($agent_filter)) {
    $where_conditions[] = "u.user_id = ?";
    $params[] = $agent_filter;
    $param_types .= "i";
}

if (!empty($status_filter)) {
    $where_conditions[] = "cc.status = ?";
    $params[] = $status_filter;
    $param_types .= "s";
}

// Get commission data
$commission_sql = "SELECT 
    u.user_id,
    u.agent_code,
    u.full_name,
    u.commission_rate,
    b.branch_name,
    
    -- Commission calculations
    COUNT(cc.commission_id) as commission_records,
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? THEN cc.commission_amount END) as period_commission,
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? AND cc.status = 'PAID' THEN cc.commission_amount END) as period_paid,
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? AND cc.status = 'APPROVED' THEN cc.commission_amount END) as period_approved,
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? AND cc.status = 'CALCULATED' THEN cc.commission_amount END) as period_pending,
    
    -- Premium and policy data
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? THEN cc.premium_amount END) as period_premium,
    COUNT(CASE WHEN cc.calculation_period BETWEEN ? AND ? THEN cc.policy_id END) as period_policies,
    
    -- Overall totals
    SUM(cc.commission_amount) as total_commission,
    SUM(CASE WHEN cc.status = 'PAID' THEN cc.commission_amount END) as total_paid,
    COUNT(DISTINCT cc.policy_id) as total_policies
    
FROM users u
LEFT JOIN branches b ON u.branch_id = b.branch_id
LEFT JOIN commission_calculations cc ON u.user_id = cc.agent_id
WHERE " . implode(" AND ", $where_conditions) . "
GROUP BY u.user_id, u.agent_code, u.full_name, u.commission_rate, b.branch_name
HAVING period_commission > 0 OR total_commission > 0
ORDER BY period_commission DESC";

// Bind parameters (we need date parameters multiple times)
$date_params = array_merge(
    [$start_date, $end_date], // period_commission
    [$start_date, $end_date], // period_paid
    [$start_date, $end_date], // period_approved
    [$start_date, $end_date], // period_pending
    [$start_date, $end_date], // period_premium
    [$start_date, $end_date]  // period_policies
);
$all_params = array_merge($date_params, $params);
$all_param_types = str_repeat("ss", 6) . $param_types;

$commission_stmt = $conn->prepare($commission_sql);
if (!empty($all_params)) {
    $commission_stmt->bind_param($all_param_types, ...$all_params);
}
$commission_stmt->execute();
$commission_result = $commission_stmt->get_result();

// Get detailed commission records for the selected period
$details_sql = "SELECT 
    cc.*,
    u.full_name,
    u.agent_code,
    p.policy_number,
    c.first_name,
    c.last_name,
    ip.product_name,
    pp.payment_date
FROM commission_calculations cc
INNER JOIN users u ON cc.agent_id = u.user_id
LEFT JOIN policies p ON cc.policy_id = p.policy_id
LEFT JOIN customers c ON p.customer_id = c.customer_id
LEFT JOIN insurance_products ip ON p.product_id = ip.product_id
LEFT JOIN premium_payments pp ON cc.payment_id = pp.payment_id
WHERE cc.calculation_period BETWEEN ? AND ?";

if (!empty($agent_filter)) {
    $details_sql .= " AND cc.agent_id = ?";
}

if (!empty($status_filter)) {
    $details_sql .= " AND cc.status = ?";
}

$details_sql .= " ORDER BY cc.calculation_period DESC, cc.created_at DESC LIMIT 100";

$details_stmt = $conn->prepare($details_sql);
$details_params = [$start_date, $end_date];
$details_param_types = "ss";

if (!empty($agent_filter)) {
    $details_params[] = $agent_filter;
    $details_param_types .= "i";
}

if (!empty($status_filter)) {
    $details_params[] = $status_filter;
    $details_param_types .= "s";
}

$details_stmt->bind_param($details_param_types, ...$details_params);
$details_stmt->execute();
$details_result = $details_stmt->get_result();

// Get summary statistics
$summary_sql = "SELECT 
    COUNT(DISTINCT cc.agent_id) as total_agents,
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? THEN cc.commission_amount END) as total_period_commission,
    SUM(CASE WHEN cc.calculation_period BETWEEN ? AND ? AND cc.status = 'PAID' THEN cc.commission_amount END) as total_period_paid,
    COUNT(CASE WHEN cc.calculation_period BETWEEN ? AND ? THEN cc.commission_id END) as total_period_records,
    AVG(CASE WHEN cc.calculation_period BETWEEN ? AND ? THEN cc.commission_amount END) as avg_commission
FROM commission_calculations cc
INNER JOIN users u ON cc.agent_id = u.user_id
WHERE u.agent_code IS NOT NULL";

$summary_stmt = $conn->prepare($summary_sql);
$summary_stmt->bind_param("ssssssss", $start_date, $end_date, $start_date, $end_date, 
                         $start_date, $end_date, $start_date, $end_date);
$summary_stmt->execute();
$summary = $summary_stmt->get_result()->fetch_assoc();

// Get monthly commission trends (last 12 months)
$trends_sql = "SELECT 
    DATE_FORMAT(cc.calculation_period, '%Y-%m') as month,
    SUM(cc.commission_amount) as total_commission,
    SUM(CASE WHEN cc.status = 'PAID' THEN cc.commission_amount END) as paid_commission,
    COUNT(DISTINCT cc.agent_id) as active_agents,
    COUNT(cc.commission_id) as commission_records
FROM commission_calculations cc
INNER JOIN users u ON cc.agent_id = u.user_id
WHERE cc.calculation_period >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    AND u.agent_code IS NOT NULL
GROUP BY DATE_FORMAT(cc.calculation_period, '%Y-%m')
ORDER BY month";

$trends_result = $conn->query($trends_sql);
$trends_data = [];
while ($row = $trends_result->fetch_assoc()) {
    $trends_data[] = $row;
}

// Get agents for filter
$agents_sql = "SELECT user_id, full_name, agent_code FROM users WHERE agent_code IS NOT NULL AND status = 'ACTIVE' ORDER BY full_name";
$agents_result = $conn->query($agents_sql);

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Agent Commissions - Ultrasure Insurance Platform')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <style>
        .commission-card {
            border: none;
            border-radius: 15px;
            box-shadow: 0 5px 15px rgba(0,0,0,0.08);
            transition: all 0.3s ease;
        }
        .commission-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0,0,0,0.15);
        }
        .commission-header {
            background: linear-gradient(135deg, #28a745 0%, #20c997 100%);
            color: white;
            border-radius: 15px 15px 0 0;
            padding: 25px;
            text-align: center;
        }
        .metric-card {
            background: linear-gradient(135deg, #f8f9fa 0%, #e9ecef 100%);
            border-radius: 15px;
            padding: 20px;
            text-align: center;
            margin-bottom: 20px;
        }
        .metric-value {
            font-size: 2rem;
            font-weight: 700;
            color: #28a745;
            margin-bottom: 5px;
        }
        .metric-label {
            font-size: 0.9rem;
            color: #6c757d;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }
        .status-badge {
            font-size: 0.75rem;
            padding: 0.35rem 0.65rem;
            border-radius: 20px;
            font-weight: 600;
        }
        .status-calculated { background: #fff3cd; color: #856404; }
        .status-approved { background: #d1ecf1; color: #0c5460; }
        .status-paid { background: #d4edda; color: #155724; }
        .status-reversed { background: #f8d7da; color: #721c24; }
        .commission-amount {
            font-size: 1.1rem;
            font-weight: 700;
        }
        .commission-rate {
            background: linear-gradient(135deg, #ffd700 0%, #ffed4a 100%);
            color: #92400e;
            padding: 3px 8px;
            border-radius: 10px;
            font-size: 0.8rem;
            font-weight: 600;
        }
        .progress-commission {
            height: 8px;
            border-radius: 4px;
        }
        .chart-container {
            background: white;
            border-radius: 15px;
            padding: 20px;
            box-shadow: 0 5px 15px rgba(0,0,0,0.08);
        }
        .filter-card {
            background: white;
            border-radius: 15px;
            padding: 20px;
            margin-bottom: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
        }
    </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="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Agent Commissions</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                                        <li class="breadcrumb-item"><a href="list_agents.php">Agents</a></li>
                                        <li class="breadcrumb-item active">Commissions</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Commission Header -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="commission-card">
                                <div class="commission-header">
                                    <h3 class="mb-2">💰 Commission Management</h3>
                                    <p class="mb-0 opacity-75">
                                        Track, manage and pay agent commissions efficiently
                                    </p>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Statistics -->
                    <div class="row mb-4">
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card">
                                <div class="metric-value">KSH <?php echo number_format($summary['total_period_commission'] ?? 0, 2); ?></div>
                                <div class="metric-label">Total Commission</div>
                                <small class="text-muted">Selected Period</small>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card">
                                <div class="metric-value">KSH <?php echo number_format($summary['total_period_paid'] ?? 0, 2); ?></div>
                                <div class="metric-label">Amount Paid</div>
                                <small class="text-success">
                                    <?php 
                                    $paid_percentage = $summary['total_period_commission'] > 0 ? 
                                        round(($summary['total_period_paid'] / $summary['total_period_commission']) * 100, 1) : 0;
                                    echo $paid_percentage; ?>% of total
                                </small>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card">
                                <div class="metric-value"><?php echo number_format($summary['total_agents'] ?? 0); ?></div>
                                <div class="metric-label">Active Agents</div>
                                <small class="text-muted">With Commissions</small>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card">
                                <div class="metric-value">KSH <?php echo number_format($summary['avg_commission'] ?? 0, 2); ?></div>
                                <div class="metric-label">Average Commission</div>
                                <small class="text-muted">Per Record</small>
                            </div>
                        </div>
                    </div>

                    <!-- Filters -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="filter-card">
                                <form method="GET" class="row g-3">
                                    <div class="col-md-3">
                                        <label class="form-label">Agent</label>
                                        <select class="form-select" name="agent">
                                            <option value="">All Agents</option>
                                            <?php while($agent = $agents_result->fetch_assoc()): ?>
                                                <option value="<?php echo $agent['user_id']; ?>" 
                                                        <?php echo $agent_filter == $agent['user_id'] ? 'selected' : ''; ?>>
                                                    <?php echo htmlspecialchars($agent['full_name']); ?> 
                                                    (<?php echo htmlspecialchars($agent['agent_code']); ?>)
                                                </option>
                                            <?php endwhile; ?>
                                        </select>
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">Period</label>
                                        <select class="form-select" name="period" id="periodSelect">
                                            <option value="current_month" <?php echo $period_filter == 'current_month' ? 'selected' : ''; ?>>Current Month</option>
                                            <option value="last_month" <?php echo $period_filter == 'last_month' ? 'selected' : ''; ?>>Last Month</option>
                                            <option value="current_quarter" <?php echo $period_filter == 'current_quarter' ? 'selected' : ''; ?>>Current Quarter</option>
                                            <option value="current_year" <?php echo $period_filter == 'current_year' ? 'selected' : ''; ?>>Current Year</option>
                                            <option value="custom" <?php echo $period_filter == 'custom' ? 'selected' : ''; ?>>Custom Range</option>
                                        </select>
                                    </div>
                                    <div class="col-md-2" id="customDateRange" style="display: <?php echo $period_filter == 'custom' ? 'block' : 'none'; ?>;">
                                        <label class="form-label">Start Date</label>
                                        <input type="date" class="form-control" name="start_date" 
                                               value="<?php echo htmlspecialchars($_GET['start_date'] ?? ''); ?>">
                                    </div>
                                    <div class="col-md-2" id="customDateRange2" style="display: <?php echo $period_filter == 'custom' ? 'block' : 'none'; ?>;">
                                        <label class="form-label">End Date</label>
                                        <input type="date" class="form-control" name="end_date" 
                                               value="<?php echo htmlspecialchars($_GET['end_date'] ?? ''); ?>">
                                    </div>
                                    <div class="col-md-2">
                                        <label class="form-label">Status</label>
                                        <select class="form-select" name="status">
                                            <option value="">All Status</option>
                                            <option value="CALCULATED" <?php echo $status_filter == 'CALCULATED' ? 'selected' : ''; ?>>Calculated</option>
                                            <option value="APPROVED" <?php echo $status_filter == 'APPROVED' ? 'selected' : ''; ?>>Approved</option>
                                            <option value="PAID" <?php echo $status_filter == 'PAID' ? 'selected' : ''; ?>>Paid</option>
                                            <option value="REVERSED" <?php echo $status_filter == 'REVERSED' ? 'selected' : ''; ?>>Reversed</option>
                                        </select>
                                    </div>
                                    <div class="col-md-1 d-flex align-items-end">
                                        <button type="submit" class="btn btn-primary w-100">
                                            <i class="ri-search-line"></i>
                                        </button>
                                    </div>
                                </form>
                            </div>
                        </div>
                    </div>

                    <!-- Commission Trends Chart -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="chart-container">
                                <h5 class="mb-3">
                                    <i class="ri-line-chart-line me-2"></i>Commission Trends (Last 12 Months)
                                </h5>
                                <div id="commissionTrends" style="height: 350px;"></div>
                            </div>
                        </div>
                    </div>

                    <!-- Agent Commission Summary -->
                    <div class="row mb-4">
                        <div class="col-12">
                            <div class="commission-card">
                                <div class="card-header d-flex justify-content-between align-items-center">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-user-star-line me-2"></i>Agent Commission Summary
                                    </h5>
                                    <div class="dropdown">
                                        <button class="btn btn-soft-primary dropdown-toggle" type="button" data-bs-toggle="dropdown">
                                            <i class="ri-download-line me-1"></i> Export
                                        </button>
                                        <ul class="dropdown-menu">
                                            <li><a class="dropdown-item" href="#" onclick="exportTable('csv')">CSV</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="exportTable('excel')">Excel</a></li>
                                            <li><a class="dropdown-item" href="#" onclick="exportTable('pdf')">PDF</a></li>
                                        </ul>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="commissionSummaryTable" class="table table-hover align-middle">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Agent</th>
                                                    <th>Branch</th>
                                                    <th>Commission Rate</th>
                                                    <th>Period Commission</th>
                                                    <th>Amount Paid</th>
                                                    <th>Pending</th>
                                                    <th>Policies</th>
                                                    <th>Premium Generated</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while($agent = $commission_result->fetch_assoc()): ?>
                                                <tr>
                                                    <td>
                                                        <div class="d-flex align-items-center">
                                                            <div class="avatar-sm bg-success text-white rounded-circle me-3 d-flex align-items-center justify-content-center">
                                                                <?php echo strtoupper(substr($agent['full_name'], 0, 1)); ?>
                                                            </div>
                                                            <div>
                                                                <h6 class="mb-1">
                                                                    <a href="view_agent.php?id=<?php echo $agent['user_id']; ?>" 
                                                                       class="text-decoration-none">
                                                                        <?php echo htmlspecialchars($agent['full_name']); ?>
                                                                    </a>
                                                                </h6>
                                                                <small class="text-muted">
                                                                    Code: <?php echo htmlspecialchars($agent['agent_code']); ?>
                                                                </small>
                                                            </div>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-soft-info text-info">
                                                            <?php echo htmlspecialchars($agent['branch_name'] ?: 'Head Office'); ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <span class="commission-rate">
                                                            <?php echo $agent['commission_rate']; ?>%
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <div class="commission-amount text-success">
                                                            KSH <?php echo number_format($agent['period_commission'] ?? 0, 2); ?>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <div class="commission-amount text-primary">
                                                            KSH <?php echo number_format($agent['period_paid'] ?? 0, 2); ?>
                                                        </div>
                                                        <?php if($agent['period_commission'] > 0): ?>
                                                            <div class="progress progress-commission mt-1">
                                                                <div class="progress-bar bg-success" 
                                                                     style="width: <?php echo round(($agent['period_paid'] / $agent['period_commission']) * 100, 1); ?>%"></div>
                                                            </div>
                                                            <small class="text-muted">
                                                                <?php echo round(($agent['period_paid'] / $agent['period_commission']) * 100, 1); ?>% paid
                                                            </small>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <div class="commission-amount text-warning">
                                                            KSH <?php echo number_format(($agent['period_approved'] ?? 0) + ($agent['period_pending'] ?? 0), 2); ?>
                                                        </div>
                                                        <small class="text-muted">
                                                            Approved: KSH <?php echo number_format($agent['period_approved'] ?? 0, 2); ?>
                                                        </small>
                                                    </td>
                                                    <td>
                                                        <div>
                                                            <strong><?php echo number_format($agent['period_policies'] ?? 0); ?></strong>
                                                            <small class="text-muted d-block">
                                                                Total: <?php echo number_format($agent['total_policies'] ?? 0); ?>
                                                            </small>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <div class="text-info">
                                                            KSH <?php echo number_format($agent['period_premium'] ?? 0, 2); ?>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <div class="d-flex gap-1">
                                                            <a href="agent_commission_details.php?id=<?php echo $agent['user_id']; ?>&period=<?php echo $period_filter; ?>" 
                                                               class="btn btn-sm btn-soft-primary" 
                                                               data-bs-toggle="tooltip" title="View Details">
                                                                <i class="ri-eye-line"></i>
                                                            </a>
                                                            <button type="button" class="btn btn-sm btn-soft-success" 
                                                                    data-bs-toggle="tooltip" title="Process Payment"
                                                                    onclick="processPayment(<?php echo $agent['user_id']; ?>)">
                                                                <i class="ri-money-dollar-circle-line"></i>
                                                            </button>
                                                            <button type="button" class="btn btn-sm btn-soft-info" 
                                                                    data-bs-toggle="tooltip" title="Generate Statement"
                                                                    onclick="generateStatement(<?php echo $agent['user_id']; ?>)">
                                                                <i class="ri-file-text-line"></i>
                                                            </button>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Detailed Commission Records -->
                    <div class="row">
                        <div class="col-12">
                            <div class="commission-card">
                                <div class="card-header">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-list-check me-2"></i>Detailed Commission Records
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="commissionDetailsTable" class="table table-sm table-hover">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>Date</th>
                                                    <th>Agent</th>
                                                    <th>Policy</th>
                                                    <th>Customer</th>
                                                    <th>Product</th>
                                                    <th>Premium</th>
                                                    <th>Rate</th>
                                                    <th>Commission</th>
                                                    <th>Status</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while($record = $details_result->fetch_assoc()): ?>
                                                <tr>
                                                    <td>
                                                        <?php echo date('M j, Y', strtotime($record['calculation_period'])); ?>
                                                    </td>
                                                    <td>
                                                        <div>
                                                            <small class="fw-medium"><?php echo htmlspecialchars($record['full_name']); ?></small>
                                                            <div class="text-muted small"><?php echo htmlspecialchars($record['agent_code']); ?></div>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <a href="policies/view_policy.php?number=<?php echo $record['policy_number']; ?>" 
                                                           class="text-decoration-none small">
                                                            <?php echo htmlspecialchars($record['policy_number']); ?>
                                                        </a>
                                                    </td>
                                                    <td>
                                                        <small><?php echo htmlspecialchars($record['first_name'] . ' ' . $record['last_name']); ?></small>
                                                    </td>
                                                    <td>
                                                        <small><?php echo htmlspecialchars($record['product_name']); ?></small>
                                                    </td>
                                                    <td>
                                                        <small class="fw-medium">KSH <?php echo number_format($record['premium_amount'], 2); ?></small>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-soft-warning text-warning"><?php echo $record['commission_rate']; ?>%</span>
                                                    </td>
                                                    <td>
                                                        <small class="fw-bold text-success">KSH <?php echo number_format($record['commission_amount'], 2); ?></small>
                                                    </td>
                                                    <td>
                                                        <span class="status-badge status-<?php echo strtolower($record['status']); ?>">
                                                            <?php echo ucfirst(strtolower($record['status'])); ?>
                                                        </span>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

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

    <!-- Process Payment Modal -->
    <div class="modal fade" id="paymentModal" tabindex="-1">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Process Commission Payment</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                </div>
                <form id="paymentForm">
                    <div class="modal-body">
                        <input type="hidden" id="paymentAgentId" name="agent_id">
                        <div class="row">
                            <div class="col-md-6 mb-3">
                                <label class="form-label">Payment Period</label>
                                <div class="d-flex gap-2">
                                    <input type="date" class="form-control" name="period_start" required>
                                    <input type="date" class="form-control" name="period_end" required>
                                </div>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">Total Commission Amount</label>
                                <input type="number" class="form-control" name="total_commission" step="0.01" readonly>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">Tax Deduction (%)</label>
                                <input type="number" class="form-control" name="tax_percentage" value="5" step="0.01" min="0" max="30">
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">Net Amount</label>
                                <input type="number" class="form-control" name="net_amount" step="0.01" readonly>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">Payment Method</label>
                                <select class="form-select" name="payment_method" required>
                                    <option value="">Select Method</option>
                                    <option value="BANK_TRANSFER">Bank Transfer</option>
                                    <option value="MOBILE_MONEY">Mobile Money</option>
                                    <option value="CHEQUE">Cheque</option>
                                </select>
                            </div>
                            <div class="col-md-6 mb-3">
                                <label class="form-label">Payment Reference</label>
                                <input type="text" class="form-control" name="payment_reference" placeholder="Enter reference number">
                            </div>
                            <div class="col-12 mb-3">
                                <label class="form-label">Notes (Optional)</label>
                                <textarea class="form-control" name="notes" rows="3" placeholder="Additional notes about this payment"></textarea>
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-success">Process Payment</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>

    <script>
    $(document).ready(function() {
        // Initialize DataTables
        $('#commissionSummaryTable').DataTable({
            responsive: true,
            pageLength: 25,
            order: [[3, 'desc']], // Order by period commission descending
            columnDefs: [
                { orderable: false, targets: [8] } // Actions column
            ]
        });

        $('#commissionDetailsTable').DataTable({
            responsive: true,
            pageLength: 50,
            order: [[0, 'desc']], // Order by date descending
            columnDefs: [
                { orderable: false, targets: [] }
            ]
        });

        // Period change handler
        $('#periodSelect').change(function() {
            const customFields = ['#customDateRange', '#customDateRange2'];
            if ($(this).val() === 'custom') {
                customFields.forEach(field => $(field).show());
            } else {
                customFields.forEach(field => $(field).hide());
            }
        });

        // Initialize tooltips
        var tooltipTriggerList = [].slice.call(document.querySelectorAll('[data-bs-toggle="tooltip"]'));
        var tooltipList = tooltipTriggerList.map(function (tooltipTriggerEl) {
            return new bootstrap.Tooltip(tooltipTriggerEl);
        });

        // Render commission trends chart
        renderCommissionChart();

        // Payment form calculations
        $('input[name="tax_percentage"]').on('input', function() {
            calculateNetAmount();
        });

        // Payment form submission
        $('#paymentForm').on('submit', function(e) {
            e.preventDefault();
            
            $.ajax({
                url: 'ajax/process_commission_payment.php',
                method: 'POST',
                data: $(this).serialize(),
                dataType: 'json',
                success: function(response) {
                    if (response.success) {
                        $('#paymentModal').modal('hide');
                        Swal.fire({
                            icon: 'success',
                            title: 'Payment Processed',
                            text: 'Commission payment has been processed successfully.',
                            timer: 2000,
                            showConfirmButton: false
                        }).then(() => {
                            location.reload();
                        });
                    } else {
                        Swal.fire({
                            icon: 'error',
                            title: 'Error',
                            text: response.message || 'Failed to process payment.'
                        });
                    }
                },
                error: function() {
                    Swal.fire({
                        icon: 'error',
                        title: 'Error',
                        text: 'An error occurred while processing the payment.'
                    });
                }
            });
        });
    });

    function renderCommissionChart() {
        const trendsData = <?php echo json_encode($trends_data); ?>;
        
        const options = {
            series: [{
                name: 'Total Commission (KSH)',
                type: 'column',
                data: trendsData.map(item => parseFloat(item.total_commission) || 0)
            }, {
                name: 'Paid Commission (KSH)',
                type: 'column',
                data: trendsData.map(item => parseFloat(item.paid_commission) || 0)
            }, {
                name: 'Active Agents',
                type: 'line',
                data: trendsData.map(item => parseInt(item.active_agents) || 0)
            }],
            chart: {
                height: 350,
                type: 'line',
                toolbar: {
                    show: true
                }
            },
            stroke: {
                width: [0, 0, 4]
            },
            plotOptions: {
                bar: {
                    columnWidth: '50%'
                }
            },
            colors: ['#28a745', '#20c997', '#667eea'],
            dataLabels: {
                enabled: true,
                enabledOnSeries: [2]
            },
            labels: trendsData.map(item => {
                const date = new Date(item.month + '-01');
                return date.toLocaleDateString('en-US', { month: 'short', year: 'numeric' });
            }),
            xaxis: {
                type: 'category'
            },
            yaxis: [{
                title: {
                    text: 'Commission Amount (KSH)',
                },
                labels: {
                    formatter: function (val) {
                        return 'KSH ' + (val / 1000).toFixed(0) + 'K';
                    }
                }
            }, {
                opposite: true,
                title: {
                    text: 'Number of Active Agents'
                }
            }],
            tooltip: {
                shared: true,
                intersect: false,
                y: [{
                    formatter: function (y) {
                        if(typeof y !== "undefined") {
                            return "KSH " + y.toLocaleString();
                        }
                        return y;
                    }
                }, {
                    formatter: function (y) {
                        if(typeof y !== "undefined") {
                            return "KSH " + y.toLocaleString();
                        }
                        return y;
                    }
                }, {
                    formatter: function (y) {
                        if(typeof y !== "undefined") {
                            return y + " agents";
                        }
                        return y;
                    }
                }]
            }
        };

        const chart = new ApexCharts(document.querySelector("#commissionTrends"), options);
        chart.render();
    }

    // Export functions
    function exportTable(format) {
        Swal.fire({
            icon: 'info',
            title: 'Export Commission Data',
            text: `${format.toUpperCase()} export functionality would be implemented here.`
        });
    }

    // Process payment function
    function processPayment(agentId) {
        // Set default period to current month
        const now = new Date();
        const startDate = new Date(now.getFullYear(), now.getMonth(), 1);
        const endDate = new Date(now.getFullYear(), now.getMonth() + 1, 0);
        
        $('input[name="period_start"]').val(startDate.toISOString().split('T')[0]);
        $('input[name="period_end"]').val(endDate.toISOString().split('T')[0]);
        
        $('#paymentAgentId').val(agentId);
        
        // Load commission amount for the period
        loadCommissionAmount(agentId);
        
        $('#paymentModal').modal('show');
    }

    function loadCommissionAmount(agentId) {
        const startDate = $('input[name="period_start"]').val();
        const endDate = $('input[name="period_end"]').val();
        
        if (startDate && endDate) {
            $.ajax({
                url: 'ajax/get_commission_amount.php',
                method: 'POST',
                data: {
                    agent_id: agentId,
                    start_date: startDate,
                    end_date: endDate
                },
                dataType: 'json',
                success: function(response) {
                    if (response.success) {
                        $('input[name="total_commission"]').val(response.total_commission);
                        calculateNetAmount();
                    }
                }
            });
        }
    }

    function calculateNetAmount() {
        const totalCommission = parseFloat($('input[name="total_commission"]').val()) || 0;
        const taxPercentage = parseFloat($('input[name="tax_percentage"]').val()) || 0;
        const taxAmount = (totalCommission * taxPercentage) / 100;
        const netAmount = totalCommission - taxAmount;
        
        $('input[name="net_amount"]').val(netAmount.toFixed(2));
    }

    // Generate statement function
    function generateStatement(agentId) {
        Swal.fire({
            title: 'Generate Commission Statement',
            text: 'Select the period for the commission statement',
            icon: 'question',
            showCancelButton: true,
            confirmButtonText: 'Generate',
            preConfirm: () => {
                // This would open a new window/tab with the statement
                window.open(`commission_statement.php?agent_id=${agentId}&period=<?php echo $period_filter; ?>`, '_blank');
            }
        });
    }

    // Period change handlers for payment modal
    $('input[name="period_start"], input[name="period_end"]').on('change', function() {
        const agentId = $('#paymentAgentId').val();
        if (agentId) {
            loadCommissionAmount(agentId);
        }
    });
    </script>

    <!-- App js -->
    <script src="assets/js/app.js"></script>
</body>
</html>