<?php
$page_title = 'Laporan Gaji Karyawan';
require_once('includes/load.php');
page_require_level(4);

function find_all_employees($search_query = '') {
    global $db;
    $sql = "SELECT id, first_name, last_name FROM employees";
    if (!empty($search_query)) {
        $search = $db->escape($search_query);
        $sql .= " WHERE first_name LIKE '%{$search}%' OR last_name LIKE '%{$search}%'";
    }
    $sql .= " ORDER BY first_name ASC, last_name ASC";
    return find_by_sql($sql);
}

$month = isset($_GET['month']) ? $_GET['month'] : date('m');
$year = isset($_GET['year']) ? $_GET['year'] : date('Y');
$search_query = isset($_GET['search_query']) ? $_GET['search_query'] : '';

$employees = find_all_employees($search_query);

$start_date = date("Y-m-01", strtotime("{$year}-{$month}-01"));
$end_date = date("Y-m-t", strtotime("{$year}-{$month}-01"));

$gaji_karyawan = [];

foreach ($employees as $employee) {
    $employee_id = $employee['id'];

    $sql_basic_salary = "SELECT basic_salary FROM salaries WHERE employee_id = '{$db->escape($employee_id)}' ORDER BY pay_date DESC LIMIT 1";
    $salary_info = find_by_sql($sql_basic_salary);
    $basic_salary = $salary_info ? (float)$salary_info[0]['basic_salary'] : 0.00;

    $sql_absensi = "SELECT status, COUNT(*) as count FROM absensi WHERE employee_id = '{$db->escape($employee_id)}' AND tanggal BETWEEN '{$start_date}' AND '{$end_date}' GROUP BY status";
    $absensi_counts = find_by_sql($sql_absensi);

    $hadir_count = 0;
    $alpha_count = 0;
    $izin_count = 0;
    $sakit_count = 0;
    $cuti_count = 0;
    foreach ($absensi_counts as $absensi) {
        if ($absensi['status'] == 'Hadir') $hadir_count = (int)$absensi['count'];
        if ($absensi['status'] == 'Alpha') $alpha_count = (int)$absensi['count'];
        if ($absensi['status'] == 'Izin') $izin_count = (int)$absensi['count'];
        if ($absensi['status'] == 'Sakit') $sakit_count = (int)$absensi['count'];
        if ($absensi['status'] == 'Cuti') $cuti_count = (int)$absensi['count'];
    }

    $fixed_deduction = 100000.00;
    $daily_rate = ($basic_salary > 0) ? $basic_salary / 22 : 0;
    $deductions_absensi = $alpha_count * $daily_rate;
    $total_deductions = $deductions_absensi + $fixed_deduction;
    $net_salary = $basic_salary - $total_deductions;

    if ($hadir_count == 0) {
        $total_deductions = 0.00;
        $net_salary = 0.00;
    }

    $gaji_karyawan[] = [
        'id' => $employee['id'],
        'name' => remove_junk($employee['first_name'] . ' ' . $employee['last_name']),
        'basic_salary' => $basic_salary,
        'hadir_count' => $hadir_count,
        'alpha_count' => $alpha_count,
        'izin_count' => $izin_count,
        'sakit_count' => $sakit_count,
        'cuti_count' => $cuti_count,
        'total_deductions' => $total_deductions,
        'net_salary' => $net_salary,
    ];
}
?>

<?php include_once('layouts/header.php'); ?>

<form action="export_gaji.php" method="POST" style="display: inline-block;">
                    <input type="hidden" name="salaries_data_export" value='<?php echo json_encode($gaji_karyawan); ?>'>
                    <input type="hidden" name="month" value="<?php echo htmlspecialchars($month); ?>">
                    <input type="hidden" name="year" value="<?php echo htmlspecialchars($year); ?>">
                    <button type="submit" class="btn btn-primary">Export ke Excel</button>
                </form>
<div class="row">
    <div class="col-md-12">
        <?php if (!empty($msg)) echo display_msg($msg); ?>
    </div>
</div>
<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <strong>
                    <span class="glyphicon glyphicon-usd"></span>
                    <span>Laporan Gaji Karyawan</span>
                </strong>
            </div>
            <div class="panel-body">
                <form action="" method="GET" class="form-inline mb-4">
                    <div class="form-group">
                        <label for="month">Bulan:</label>
                        <select name="month" id="month" class="form-control">
                            <?php for ($m = 1; $m <= 12; $m++): ?>
                                <option value="<?php echo sprintf('%02d', $m); ?>" <?php echo $month == sprintf('%02d', $m) ? 'selected' : ''; ?>>
                                    <?php echo date('F', mktime(0, 0, 0, $m, 10)); ?>
                                </option>
                            <?php endfor; ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <label for="year">Tahun:</label>
                        <select name="year" id="year" class="form-control">
                            <?php for ($y = date('Y'); $y >= date('Y') - 5; $y--): ?>
                                <option value="<?php echo $y; ?>" <?php echo $year == $y ? 'selected' : ''; ?>>
                                    <?php echo $y; ?>
                                </option>
                            <?php endfor; ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <label for="search_query">Cari Karyawan:</label>
                        <input type="text" name="search_query" id="search_query" class="form-control" placeholder="Nama karyawan..." value="<?php echo htmlspecialchars($search_query); ?>">
                    </div>
                    <button type="submit" class="btn btn-primary">Filter & Cari</button>
                </form>

                <div class="table-responsive">
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th class="text-center">#</th>
                                <th>Nama Karyawan</th>
                                <th class="text-center">Gaji Pokok</th>
                                <th class="text-center">Hadir</th>
                                <th class="text-center">Alpha</th>
                                <th class="text-center">Izin</th>
                                <th class="text-center">Sakit</th>
                                <th class="text-center">Cuti</th>
                                <th class="text-center">Total Potongan</th>
                                <th class="text-center">Gaji Bersih</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($gaji_karyawan)): ?>
                                <?php $i = 1; ?>
                                <?php foreach ($gaji_karyawan as $gaji): ?>
                                    <tr>
                                        <td class="text-center"><?php echo $i++; ?></td>
                                        <td><?php echo htmlspecialchars($gaji['name']); ?></td>
                                        <td class="text-right"><?php echo 'Rp ' . number_format($gaji['basic_salary'], 2, ',', '.'); ?></td>
                                        <td class="text-center"><?php echo (int)$gaji['hadir_count']; ?></td>
                                        <td class="text-center"><?php echo (int)$gaji['alpha_count']; ?></td>
                                        <td class="text-center"><?php echo (int)$gaji['izin_count']; ?></td>
                                        <td class="text-center"><?php echo (int)$gaji['sakit_count']; ?></td>
                                        <td class="text-center"><?php echo (int)$gaji['cuti_count']; ?></td>
                                        <td class="text-right"><?php echo 'Rp ' . number_format($gaji['total_deductions'], 2, ',', '.'); ?></td>
                                        <td class="text-right"><?php echo 'Rp ' . number_format($gaji['net_salary'], 2, ',', '.'); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="10" class="text-center">Tidak ada data gaji untuk ditampilkan.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>

            </div>
        </div>
    </div>
</div>
<?php include_once('layouts/footer.php'); ?>