<?php
require_once 'db.php';
requireAdmin();


function formatJobNo($jobNo)
{
    if (preg_match('/JOB-(\d{8})(\d{6})/', $jobNo, $matches)) {
        $dateRaw = $matches[1];
        $time    = $matches[2];

        $dateObj = DateTime::createFromFormat('Ymd', $dateRaw);
        $dateFormatted = $dateObj ? $dateObj->format('d/m/Y') : $dateRaw;

        return $dateFormatted . ' (' . $time . ')';
    }

    return $jobNo;
}
/*
|--------------------------------------------------------------------------
| Filter (Year / Month)
|--------------------------------------------------------------------------
*/
$year = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');
$monthFrom = isset($_GET['month_from']) ? (int)$_GET['month_from'] : (int)date('n');
$monthTo   = isset($_GET['month_to']) ? (int)$_GET['month_to'] : (int)date('n');

if ($monthFrom < 1 || $monthFrom > 12) {
    $monthFrom = (int)date('n');
}

if ($monthTo < 1 || $monthTo > 12) {
    $monthTo = (int)date('n');
}

if ($monthFrom > $monthTo) {
    [$monthFrom, $monthTo] = [$monthTo, $monthFrom];
}

/*
|--------------------------------------------------------------------------
| Total Payment Summary
|--------------------------------------------------------------------------
*/
$totalPaymentStmt = $pdo->prepare("
    SELECT COALESCE(SUM(job_split.share_amount), 0) AS total_payment
    FROM (
        SELECT
            j.id AS job_id,
            jsa.staff_id,
            ROUND(
                j.staff_fee / NULLIF((
                    SELECT COUNT(*)
                    FROM job_staff_assignments jsa2
                    WHERE jsa2.job_id = j.id
                ), 0),
            0) AS share_amount
        FROM jobs j
        INNER JOIN job_staff_assignments jsa ON j.id = jsa.job_id
        WHERE j.status = 'closed'
          AND j.closed_at IS NOT NULL
          AND YEAR(j.closed_at) = :year
            AND MONTH(j.closed_at) BETWEEN :month_from AND :month_to
    ) AS job_split
");
$totalPaymentStmt->execute([
    ':year' => $year,
    ':month_from' => $monthFrom,
    ':month_to' => $monthTo
]);
$totalPayment = $totalPaymentStmt->fetch()['total_payment'] ?? 0;

/*
|--------------------------------------------------------------------------
| Staff Summary
|--------------------------------------------------------------------------
*/
$staffSummaryStmt = $pdo->prepare("
    SELECT
        s.id,
        s.full_name,
        s.username,
        s.status,
        COUNT(CASE WHEN j.status = 'closed' THEN 1 END) AS total_closed_jobs,
        COALESCE(SUM(
            CASE
                WHEN j.status = 'closed' THEN
                    ROUND(
                        j.staff_fee / NULLIF((
                            SELECT COUNT(*)
                            FROM job_staff_assignments jsa2
                            WHERE jsa2.job_id = j.id
                        ), 0),
                    0)
                ELSE 0
            END
        ), 0) AS total_earned
    FROM staff s
    LEFT JOIN job_staff_assignments jsa ON s.id = jsa.staff_id
    LEFT JOIN jobs j 
        ON j.id = jsa.job_id
        AND j.status = 'closed'
        AND j.closed_at IS NOT NULL
        AND YEAR(j.closed_at) = :year
        AND MONTH(j.closed_at) BETWEEN :month_from AND :month_to
    GROUP BY s.id, s.full_name, s.username, s.status
    ORDER BY total_earned DESC, s.full_name ASC
");
$staffSummaryStmt->execute([
    ':year' => $year,
    ':month_from' => $monthFrom,
    ':month_to' => $monthTo
]);
$staffSummaries = $staffSummaryStmt->fetchAll();

/*
|--------------------------------------------------------------------------
| Staff Jobs
|--------------------------------------------------------------------------
*/
$staffJobsStmt = $pdo->prepare("
    SELECT
        s.id AS staff_id,
        s.full_name,
        j.id AS job_id,
        j.job_no,
        j.issue_title,
        j.status AS job_status,
        j.staff_fee,
        j.closed_at,
        COALESCE(m.customer_name, c.customer_name) AS customer_name,
        m.unique_machine_no,
        ROUND(
            j.staff_fee / NULLIF((
                SELECT COUNT(*)
                FROM job_staff_assignments jsa2
                WHERE jsa2.job_id = j.id
            ), 0),
        0) AS share_amount
    FROM staff s
    LEFT JOIN job_staff_assignments jsa ON s.id = jsa.staff_id
    LEFT JOIN jobs j ON j.id = jsa.job_id
    LEFT JOIN customers c ON j.customer_id = c.id
    LEFT JOIN machines m ON j.machine_id = m.id
    WHERE j.id IS NOT NULL
      AND j.status = 'closed'
      AND j.closed_at IS NOT NULL
      AND YEAR(j.closed_at) = :year
      AND MONTH(j.closed_at) BETWEEN :month_from AND :month_to
    ORDER BY s.full_name ASC, j.id DESC
");
$staffJobsStmt->execute([
    ':year' => $year,
    ':month_from' => $monthFrom,
    ':month_to' => $monthTo
]);
$staffJobsRaw = $staffJobsStmt->fetchAll();

/*
|--------------------------------------------------------------------------
| Map Jobs
|--------------------------------------------------------------------------
*/
$staffJobsMap = [];
foreach ($staffJobsRaw as $row) {
    $staffJobsMap[$row['staff_id']][] = $row;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Staff Payment - Service Portal</title>
     <link rel="stylesheet" href="css/style.css?v=1">

    <style>
        * {
            box-sizing: border-box;
        }

        body {
            margin: 0;
            font-family: Arial, sans-serif;
            background: #f3f6fb;
            color: #111827;
        }

        .page-topbar {
            margin: 20px 0;
            padding: 0 16px;
        }

        .page-back-btn {
            display: inline-block;
            background: #2563eb;
            color: #fff;
            padding: 8px 14px;
            border-radius: 8px;
            text-decoration: none;
            font-weight: bold;
        }

        .page-back-btn:hover {
            background: #1d4ed8;
        }

        .payment-container {
            max-width: 1300px;
            margin: 24px auto;
            padding: 0 16px;
        }

        .payment-page-title {
            margin: 0 0 18px;
            font-size: 28px;
            font-weight: 700;
        }

        .payment-nav-links {
            display: flex;
            gap: 12px;
            flex-wrap: wrap;
            margin-bottom: 22px;
        }

        .payment-nav-links a {
            background: #fff;
            border: 1px solid #dbe3ee;
            padding: 10px 14px;
            border-radius: 10px;
            text-decoration: none;
            color: #1f2937;
            font-weight: bold;
        }

        .payment-nav-links a:hover,
        .payment-nav-links a.active {
            background: #eff6ff;
            border-color: #93c5fd;
        }

        .payment-card {
            background: #fff;
            border-radius: 16px;
            padding: 20px;
            box-shadow: 0 10px 25px rgba(15, 23, 42, 0.05);
            margin-bottom: 20px;
        }

        .payment-summary-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(240px, 1fr));
            gap: 16px;
        }

        .payment-summary-box {
            background: #f8fafc;
            border-radius: 14px;
            padding: 18px;
        }

        .payment-summary-label {
            color: #6b7280;
            font-size: 14px;
            margin-bottom: 8px;
        }

        .payment-summary-value {
            font-size: 28px;
            font-weight: bold;
        }

        .payment-section-title {
            margin-top: 0;
            margin-bottom: 16px;
            font-size: 20px;
        }

        .payment-table-wrap {
            width: 100%;
            overflow-x: auto;
            -webkit-overflow-scrolling: touch;
        }

        .payment-table {
            width: 100%;
            border-collapse: collapse;
            min-width: 900px;
        }

        .payment-table th,
        .payment-table td {
            text-align: left;
            padding: 12px 10px;
            border-bottom: 1px solid #e5e7eb;
            font-size: 14px;
            vertical-align: top;
        }

        .payment-table th {
            background: #f8fafc;
        }

        .payment-muted {
            color: #6b7280;
        }

        .payment-staff-block {
            background: #fff;
            border-radius: 16px;
            padding: 20px;
            box-shadow: 0 10px 25px rgba(15, 23, 42, 0.05);
            margin-bottom: 20px;
        }

        .payment-staff-header {
            display: flex;
            justify-content: space-between;
            align-items: flex-start;
            gap: 12px;
            flex-wrap: wrap;
            margin-bottom: 14px;
        }

        .payment-staff-name {
            font-size: 20px;
            font-weight: bold;
            margin-bottom: 4px;
        }

        .payment-staff-meta {
            color: #6b7280;
            font-size: 14px;
        }

        .payment-earned-badge {
            background: #dcfce7;
            color: #166534;
            padding: 10px 14px;
            border-radius: 999px;
            font-weight: bold;
            white-space: nowrap;
        }

        .payment-status {
            display: inline-block;
            padding: 6px 10px;
            border-radius: 999px;
            font-size: 12px;
            font-weight: bold;
        }

        .payment-status-closed {
            background: #dcfce7;
            color: #166534;
        }

        .payment-status-assigned {
            background: #dbeafe;
            color: #1d4ed8;
        }

        .payment-status-in-progress {
            background: #fef3c7;
            color: #92400e;
        }

        .payment-status-open {
            background: #e5e7eb;
            color: #374151;
        }

        .payment-status-inactive {
            background: #fee2e2;
            color: #991b1b;
        }

        .payment-status-active {
            background: #dcfce7;
            color: #166534;
        }

        .payment-grand-total-row {
            background: #f8fafc;
            font-weight: bold;
        }

        .payment-export-toolbar {
    margin-bottom: 16px;
}

.payment-export-form {
    display: flex;
    align-items: center;
    gap: 10px;
    flex-wrap: wrap;
    background: #f8fafc;
    border: 1px solid #dbe3ee;
    border-radius: 12px;
    padding: 12px;
}

.filter-row {
    display: flex;
    align-items: center;
    gap: 10px;
}

.filter-group {
    display: flex;
    align-items: center;
    gap: 8px;
}

.filter-group label {
    font-size: 14px;
    font-weight: 600;
    color: #374151;
    white-space: nowrap;
}

.filter-group input[type="number"],
.filter-group select {
    padding: 8px 10px;
    border: 1px solid #cbd5e1;
    border-radius: 8px;
    background: #fff;
    min-width: 100px;


    text-align: center;
    text-align-last: center; /* important */

}

.btn-filter {
    background: #2563eb;
    color: white;
    border: none;
    padding: 10px 14px;
    border-radius: 8px;
    font-weight: bold;
    cursor: pointer;
}

.btn-export-excel {
    background: #059669;
    color: white;
    border: none;
    padding: 10px 14px;
    border-radius: 8px;
    font-weight: bold;
    cursor: pointer;
}

.btn-export-excel:hover {
    background: #047857;
}
/* Desktop / tablet: show all controls on one line */
.payment-export-form {
    display: flex;
    align-items: center;
    gap: 10px;
    flex-wrap: wrap;
}

.filter-row {
    display: contents;
}
.filter-group input[type="number"],
.filter-group select,
.btn-filter,
.btn-export-excel {
    height: 42px;
    font-size: 16px;
    font-weight: 500;
    
}
.payment-export-form {
    align-items: center;
}
.filter-group {
    align-items: center;
}

/* iPhone only */
@media (max-width: 480px) {
    .payment-export-form {
        display: flex;
        flex-direction: column;
        align-items: flex-start;
        gap: 12px;
    }

    .filter-row-1 {
        width: 100%;
    }

    .filter-row-1 .filter-group {
        width: 100%;
        justify-content: flex-start;
    }

    .filter-row-2 {
        width: 100%;
        display: grid;
        grid-template-columns: 1fr 1fr auto;
        gap: 8px;
        align-items: end;
    }

    .filter-row-2 .filter-group {
        display: flex;
        flex-direction: column;
        align-items: flex-start;
        gap: 6px;
        width: 100%;
    }

    .filter-row-2 .filter-group select {
        width: 100%;
        min-width: 0;
    }

    .filter-row-2 label {
        font-size: 12px;
    }

    .btn-filter {
        height: 42px;
        white-space: nowrap;
    }

    .filter-row-3 {
        width: 100%;
    }

    .filter-row-3 .btn-export-excel {
        width: 100%;
    }
}

@media (max-width: 700px) {
    .payment-container {
        padding: 0 10px;
    }

    .payment-card,
    .payment-staff-block {
        padding: 14px;
    }

    .payment-summary-value {
        font-size: 22px;
    }

    .payment-table th,
    .payment-table td {
        font-size: 13px;
        padding: 10px 8px;
    }

    .payment-page-title {
        font-size: 24px;
    }
}
    
    </style>
</head>
<body>

    <?php include 'header.php'; ?>

    <div class="page-topbar">
        <a href="admin-dashboard.php" class="page-back-btn">← Dashboard</a>
    </div>

    <div class="payment-container">
        <h1 class="payment-page-title">Staff Payment</h1>

        <div class="payment-nav-links">
            <a href="machines.php">Machines</a>
            <a href="add_job.php">Open Job</a>
            <a href="manage_staff.php">Staff</a>
            <a href="staff_payments.php" class="active">Staff Payment</a>
        </div>

        <div class="payment-card">
            <h2 class="payment-section-title">Payment Summary</h2>
            <div class="payment-summary-grid">
                <div class="payment-summary-box">
                    <div class="payment-summary-label">Total Payment to All Staff</div>
                    <div class="payment-summary-value"><?= number_format($totalPayment, 0) ?> LAK</div>
                </div>

                <div class="payment-summary-box">
                    <div class="payment-summary-label">Total Staff Count</div>
                    <div class="payment-summary-value"><?= count($staffSummaries) ?></div>
                </div>
            </div>
        </div>

        <div class="payment-card">
            <div class="payment-export-toolbar">
                <form method="GET" class="payment-export-form">

                    <!-- Line 1 -->
                    <div class="filter-row filter-row-1">
                        <div class="filter-group">
                            <label for="year">Year</label>
                            <input
                                type="number"
                                id="year"
                                name="year"
                                min="2020"
                                max="2100"
                                value="<?= esc($year) ?>"
                            >
                        </div>
                    </div>

                    <!-- Line 2 -->
                    <div class="filter-row filter-row-2">
                        <div class="filter-group">
                            <label for="month_from">From</label>
                            <select id="month_from" name="month_from">
                                <?php for ($m = 1; $m <= 12; $m++): ?>
                                    <option value="<?= $m ?>" <?= $m == $monthFrom ? 'selected' : '' ?>>
                                        <?= $m ?>
                                    </option>
                                <?php endfor; ?>
                            </select>
                        </div>

                        <div class="filter-group">
                            <label for="month_to">To</label>
                            <select id="month_to" name="month_to">
                                <?php for ($m = 1; $m <= 12; $m++): ?>
                                    <option value="<?= $m ?>" <?= $m == $monthTo ? 'selected' : '' ?>>
                                        <?= $m ?>
                                    </option>
                                <?php endfor; ?>
                            </select>
                        </div>

                        <button type="submit" formaction="staff_payments.php" class="btn-filter">
                            Filter
                        </button>
                    </div>

                    <!-- Line 3 -->
                    <div class="filter-row filter-row-3">
                        <button type="submit" formaction="export_staff_payments_excel.php" class="btn-export-excel">
                            Export Excel
                        </button>
                    </div>

                </form>
            </div>


            <h2 class="payment-section-title">Staff Payment Summary Table</h2>

            <div class="payment-table-wrap">
                <table class="payment-table">
                    <thead>
                        <tr>
                            <th>Staff Name</th>
                            <th>Username</th>
                            <th>Status</th>
                            <th>Closed Jobs</th>
                            <th>Total Earned</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if ($staffSummaries): ?>
                            <?php foreach ($staffSummaries as $staff): ?>
                                <tr>
                                    <td><?= esc($staff['full_name']) ?></td>
                                    <td><?= esc($staff['username']) ?></td>
                                    <td>
                                        <span class="payment-status <?= $staff['status'] === 'active' ? 'payment-status-active' : 'payment-status-inactive' ?>">
                                            <?= esc($staff['status']) ?>
                                        </span>
                                    </td>
                                    <td><?= esc($staff['total_closed_jobs']) ?></td>
                                    <td><strong><?= number_format($staff['total_earned'], 0) ?> LAK</strong></td>
                                </tr>
                            <?php endforeach; ?>

                            <tr class="payment-grand-total-row">
                                <td colspan="4" style="text-align:right;">Grand Total Payment</td>
                                <td><?= number_format($totalPayment, 0) ?> LAK</td>
                            </tr>
                        <?php else: ?>
                            <tr>
                                <td colspan="5" class="payment-muted">No staff records found.</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <?php foreach ($staffSummaries as $staff): ?>
            <div class="payment-staff-block">
                <div class="payment-staff-header">
                    <div>
                        <div class="payment-staff-name"><?= esc($staff['full_name']) ?></div>
                        <div class="payment-staff-meta">
                            Username: <?= esc($staff['username']) ?> |
                            Closed Jobs: <?= esc($staff['total_closed_jobs']) ?> |
                            Status:
                            <span class="payment-status <?= $staff['status'] === 'active' ? 'payment-status-active' : 'payment-status-inactive' ?>">
                                <?= esc($staff['status']) ?>
                            </span>
                        </div>
                    </div>

                    <div class="payment-earned-badge">
                        Total Earned: <?= number_format($staff['total_earned'], 0) ?> LAK
                    </div>
                </div>

                <div class="payment-table-wrap">
                    <table class="payment-table">
                        <thead>
                            <tr>
                                <th>Job No</th>
                                <th>Customer</th>
                                <th>Machine</th>
                                <th>Issue</th>
                                <th>Job Status</th>
                                <th>Job Staff Fee</th>
                                <th>This Staff Share</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($staffJobsMap[$staff['id']])): ?>
                                <?php foreach ($staffJobsMap[$staff['id']] as $job): ?>
                                    <?php
                                        $jobStatusClass = 'payment-status-open';

                                        if ($job['job_status'] === 'closed') {
                                            $jobStatusClass = 'payment-status-closed';
                                        } elseif ($job['job_status'] === 'assigned') {
                                            $jobStatusClass = 'payment-status-assigned';
                                        } elseif ($job['job_status'] === 'in_progress') {
                                            $jobStatusClass = 'payment-status-in-progress';
                                        } elseif ($job['job_status'] === 'open') {
                                            $jobStatusClass = 'payment-status-open';
                                        }
                                    ?>
                                    <tr>
                                        <td><?= esc(formatJobNo($job['job_no'])) ?></td>
                                        <td><?= esc($job['customer_name'] ?: '-') ?></td>
                                        <td><?= esc($job['unique_machine_no']) ?></td>
                                        <td><?= esc($job['issue_title']) ?></td>
                                        <td>
                                            <span class="payment-status <?= $jobStatusClass ?>">
                                                <?= esc(ucwords(str_replace('_', ' ', $job['job_status']))) ?>
                                            </span>
                                        </td>
                                        <td><?= number_format($job['staff_fee'], 0) ?> LAK</td>
                                        <td>
                                            <?php if ($job['job_status'] === 'closed'): ?>
                                                <strong><?= number_format($job['share_amount'], 0) ?> LAK</strong>
                                            <?php else: ?>
                                                <span class="payment-muted">Not earned yet</span>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="7" class="payment-muted">No assigned jobs.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        <?php endforeach; ?>
    </div>

</body>
</html>