<?php
include '../config.php';
include '../auth.php';
checkRole('manager');

date_default_timezone_set('Asia/Colombo');

// Ensure tables exist
$conn->query("CREATE TABLE IF NOT EXISTS sales_officers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
$conn->query("CREATE TABLE IF NOT EXISTS sales_commissions (id INT AUTO_INCREMENT PRIMARY KEY, sale_id INT NOT NULL, sales_officer_id INT NOT NULL, commission_amount DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE, FOREIGN KEY (sales_officer_id) REFERENCES sales_officers(id) ON DELETE CASCADE)");

$month = $_GET['month'] ?? date('Y-m');
$officer_filter = isset($_GET['officer_id']) ? intval($_GET['officer_id']) : '';

$start_date = $month . '-01';
$end_date = date('Y-m-t', strtotime($start_date));

$officers_result = $conn->query("SELECT id, name FROM sales_officers WHERE is_active = 1 ORDER BY name");
$officers = [];
if ($officers_result) {
    while ($o = $officers_result->fetch_assoc()) {
        $officers[] = $o;
    }
}

// Summary query
$summary_sql = "SELECT so.id, so.name, SUM(sc.commission_amount) as total_commission, COUNT(sc.id) as sale_count
        FROM sales_commissions sc
        JOIN sales_officers so ON sc.sales_officer_id = so.id
        WHERE DATE(sc.created_at) BETWEEN ? AND ?";
$summary_params = [$start_date, $end_date];
$summary_types = 'ss';
if ($officer_filter) {
    $summary_sql .= " AND so.id = ?";
    $summary_params[] = $officer_filter;
    $summary_types .= 'i';
}
$summary_sql .= " GROUP BY so.id, so.name ORDER BY so.name";

$summary_stmt = $conn->prepare($summary_sql);
if (!$summary_stmt) {
    die("Prepare error: " . $conn->error);
}
$summary_stmt->bind_param($summary_types, ...$summary_params);
$summary_stmt->execute();
$summary = $summary_stmt->get_result();

// Debug: Count total commissions
$debug_stmt = $conn->prepare("SELECT COUNT(*) as total FROM sales_commissions WHERE DATE(created_at) BETWEEN ? AND ?");
$debug_stmt->bind_param('ss', $start_date, $end_date);
$debug_stmt->execute();
$debug_count = $debug_stmt->get_result()->fetch_assoc()['total'];

// Detailed list
$detail_sql = "SELECT sc.*, so.name as officer_name, s.sale_date, s.total_amount
         FROM sales_commissions sc
         JOIN sales_officers so ON sc.sales_officer_id = so.id
         JOIN sales s ON sc.sale_id = s.id
         WHERE DATE(sc.created_at) BETWEEN ? AND ?";
$detail_params = [$start_date, $end_date];
$detail_types = 'ss';
if ($officer_filter) {
    $detail_sql .= " AND so.id = ?";
    $detail_params[] = $officer_filter;
    $detail_types .= 'i';
}
$detail_sql .= " ORDER BY sc.created_at DESC";
$detail_stmt = $conn->prepare($detail_sql);
if (!$detail_stmt) {
    die("Prepare error: " . $conn->error);
}
$detail_stmt->bind_param($detail_types, ...$detail_params);
$detail_stmt->execute();
$details = $detail_stmt->get_result();
?>
<!DOCTYPE html>
<html>
<head>
    <title>Sales Commissions</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
</head>
<body class="bg-light">
<div class="container py-4">
    <div class="d-flex justify-content-between align-items-center mb-3">
        <h3><i class="fas fa-coins"></i> Sales Commissions</h3>
        <div>
            <a href="../dashboard.php" class="btn btn-secondary btn-sm">Dashboard</a>
        </div>
    </div>

    <div class="card mb-3">
        <div class="card-body">
            <form class="row g-2">
                <div class="col-md-3">
                    <label class="form-label">Month</label>
                    <input type="month" name="month" class="form-control" value="<?= htmlspecialchars($month) ?>">
                </div>
                <div class="col-md-3">
                    <label class="form-label">Sales Officer</label>
                    <select name="officer_id" class="form-select">
                        <option value="">All</option>
                        <?php foreach ($officers as $o): ?>
                            <option value="<?= $o['id'] ?>" <?= $officer_filter == $o['id'] ? 'selected' : '' ?>><?= htmlspecialchars($o['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2 d-flex align-items-end">
                    <button class="btn btn-primary w-100" type="submit"><i class="fas fa-filter"></i> Filter</button>
                </div>
            </form>
        </div>
    </div>

    <?php if ($debug_count == 0): ?>
    <div class="alert alert-warning">
        <i class="fas fa-info-circle"></i> <strong>No commissions recorded yet for this period.</strong>
        <br><small>Sales with selected sales officers will appear here automatically.</small>
    </div>
    <?php endif; ?>

    <div class="card mb-3">
        <div class="card-header">Monthly Summary</div>
        <div class="card-body p-0">
            <table class="table mb-0">
                <thead class="table-light">
                    <tr>
                        <th>Sales Officer</th>
                        <th>Sales Count</th>
                        <th>Total Commission (LKR)</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($summary && $summary->num_rows > 0): ?>
                        <?php while ($row = $summary->fetch_assoc()): ?>
                            <tr>
                                <td><?= htmlspecialchars($row['name']) ?></td>
                                <td><?= $row['sale_count'] ?></td>
                                <td>LKR <?= number_format($row['total_commission'], 2) ?></td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr><td colspan="3" class="text-center text-muted p-3">No commissions for this period.</td></tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <div class="card">
        <div class="card-header">Detailed Transactions</div>
        <div class="card-body p-0">
            <table class="table mb-0 table-striped">
                <thead class="table-light">
                    <tr>
                        <th>Date</th>
                        <th>Officer</th>
                        <th>Sale ID</th>
                        <th>Commission (LKR)</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($details && $details->num_rows > 0): ?>
                        <?php while ($d = $details->fetch_assoc()): ?>
                            <tr>
                                <td><?= date('M d, Y h:i A', strtotime($d['created_at'])) ?></td>
                                <td><?= htmlspecialchars($d['officer_name']) ?></td>
                                <td><?= $d['sale_id'] ?></td>
                                <td>LKR <?= number_format($d['commission_amount'], 2) ?></td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr><td colspan="4" class="text-center text-muted p-3">No records.</td></tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>
