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

// --- Export to Excel functionality ---
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['export_excel'])) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="salary_data.xls"');
    
    // Build the SQL query for export with new calculated columns
    $sql = "SELECT 
                e.first_name, 
                e.last_name, 
                e.job_title, 
                d.name AS department, 
                s.pay_date, 
                s.basic_salary, 
                (s.tunjangan_jabatan + s.adjustment + s.tunjangan_kehadiran + s.tunjangan_transport + s.tunjangan_makan + s.work_visit + s.lembur) AS allowances,
                (s.bpjs_kesehatan + s.bpjs_ketenagakerjaan) AS deductions,
                s.net_salary
            FROM salaries s
            LEFT JOIN employees e ON s.employee_id = e.id
            LEFT JOIN departments d ON e.department_id = d.department_id";

    // Handle search and filter parameters for export
    $search_term = isset($_GET['search']) ? $db->escape($_GET['search']) : '';
    $department_id = isset($_GET['department_id']) ? (int)$_GET['department_id'] : 0;
    $min_salary = isset($_GET['min_salary']) ? (int)$_GET['min_salary'] : 0;
    $max_salary = isset($_GET['max_salary']) ? (int)$_GET['max_salary'] : 0;

    $where_clauses = [];
    if (!empty($search_term)) {
        $where_clauses[] = " (e.first_name LIKE '%{$search_term}%' OR e.last_name LIKE '%{$search_term}%' OR e.id LIKE '%{$search_term}%') ";
    }
    if ($department_id > 0) {
        $where_clauses[] = " e.department_id = {$department_id} ";
    }
    if ($min_salary > 0) {
        $where_clauses[] = " s.net_salary >= {$min_salary} ";
    }
    if ($max_salary > 0) {
        $where_clauses[] = " s.net_salary <= {$max_salary} ";
    }

    if (!empty($where_clauses)) {
        $sql .= " WHERE " . implode(' AND ', $where_clauses);
    }
    
    $result = $db->query($sql);

    // Generate table for export
    echo '<table border="1">';
    echo '<thead><tr><th>#</th><th>Nama Karyawan</th><th>Jabatan</th><th>Departemen</th><th>Tanggal Gaji</th><th>Gaji Pokok</th><th>Tunjangan</th><th>Potongan</th><th>Total Gaji Bersih</th></tr></thead>';
    echo '<tbody>';
    $i = 1;
    while ($row = $db->fetch_assoc($result)) {
        echo '<tr>';
        echo '<td>' . $i++ . '</td>';
        echo '<td>' . remove_junk($row['first_name']) . ' ' . remove_junk($row['last_name']) . '</td>';
        echo '<td>' . remove_junk($row['job_title']) . '</td>';
        echo '<td>' . remove_junk($row['department']) . '</td>';
        echo '<td>' . htmlspecialchars($row['pay_date']) . '</td>';
        echo '<td>' . number_format($row['basic_salary'], 0, ',', '.') . '</td>';
        echo '<td>' . number_format($row['allowances'], 0, ',', '.') . '</td>';
        echo '<td>' . number_format($row['deductions'], 0, ',', '.') . '</td>';
        echo '<td>' . number_format($row['net_salary'], 0, ',', '.') . '</td>';
        echo '</tr>';
    }
    echo '</tbody>';
    echo '</table>';

    exit;
}

// --- Dashboard Metrics ---
$total_salaries_count = find_by_sql("SELECT COUNT(*) AS total FROM salaries")[0]['total'];
$total_net_salary = find_by_sql("SELECT SUM(net_salary) AS total FROM salaries")[0]['total'];
$average_salary = $total_salaries_count > 0 ? $total_net_salary / $total_salaries_count : 0;
$highest_salary_row = find_by_sql("SELECT MAX(net_salary) AS max_salary FROM salaries");
$highest_salary = isset($highest_salary_row[0]['max_salary']) ? $highest_salary_row[0]['max_salary'] : 0;
$lowest_salary_row = find_by_sql("SELECT MIN(net_salary) AS min_salary FROM salaries");
$lowest_salary = isset($lowest_salary_row[0]['min_salary']) ? $lowest_salary_row[0]['min_salary'] : 0;

// --- Find employees with no salary records ---
$employees_without_salary_query = "
    SELECT e.id, e.first_name, e.last_name, e.job_title, d.name AS department
    FROM employees e
    LEFT JOIN salaries s ON e.id = s.employee_id
    LEFT JOIN departments d ON e.department_id = d.department_id
    WHERE s.employee_id IS NULL
    ORDER BY e.first_name ASC
";
$employees_without_salary = find_by_sql($employees_without_salary_query);


// --- Pagination, search, and filter settings ---
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 25;
$department_id = isset($_GET['department_id']) ? (int)$_GET['department_id'] : 0;
$search_term = isset($_GET['search']) ? $db->escape($_GET['search']) : '';
$min_salary = isset($_GET['min_salary']) ? (int)$_GET['min_salary'] : 0;
$max_salary = isset($_GET['max_salary']) ? (int)$_GET['max_salary'] : 0;

// Build the SQL query for counting total rows
$sql = "SELECT COUNT(*) AS total FROM salaries s
        LEFT JOIN employees e ON s.employee_id = e.id";

$where_clauses = [];
if (!empty($search_term)) {
    $where_clauses[] = " (e.first_name LIKE '%{$search_term}%' OR e.last_name LIKE '%{$search_term}%' OR e.id LIKE '%{$search_term}%') ";
}
if ($department_id > 0) {
    $where_clauses[] = " e.department_id = {$department_id} ";
}
if ($min_salary > 0) {
    $where_clauses[] = " s.net_salary >= {$min_salary} ";
}
if ($max_salary > 0) {
    $where_clauses[] = " s.net_salary <= {$max_salary} ";
}

if (!empty($where_clauses)) {
    $sql .= " WHERE " . implode(' AND ', $where_clauses);
}

$result = $db->query($sql);
$total_salaries_filtered = $db->fetch_assoc($result)['total'];
$total_pages = ceil($total_salaries_filtered / $per_page);
$start = ($page - 1) * $per_page;

// Build the main SQL query for displaying data with new calculated columns
$sql = "SELECT 
            s.*, 
            e.first_name, 
            e.last_name, 
            e.job_title, 
            d.name AS department,
            (s.tunjangan_jabatan + s.adjustment + s.tunjangan_kehadiran + s.tunjangan_transport + s.tunjangan_makan + s.work_visit + s.lembur) AS allowances,
            (s.bpjs_kesehatan + s.bpjs_ketenagakerjaan) AS deductions
        FROM salaries s
        LEFT JOIN employees e ON s.employee_id = e.id
        LEFT JOIN departments d ON e.department_id = d.department_id";

if (!empty($where_clauses)) {
    $sql .= " WHERE " . implode(' AND ', $where_clauses);
}

$sql .= " ORDER BY s.pay_date DESC LIMIT {$per_page} OFFSET {$start}";
$salaries = find_by_sql($sql);
$departments = find_all('departments');
?>

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

<style>
    .dashboard-panel {
        display: flex;
        justify-content: space-between;
        flex-wrap: wrap;
    }
    .dashboard-panel .col-md-3 {
        flex: 1 1 23%;
    }
    .panel-heading i {
        margin-right: 5px;
    }
    .btn-group .btn {
        margin-right: 2px;
    }
</style>

<div class="row">
    <div class="col-md-12">
        <?php echo display_msg($msg); ?>
    </div>
</div>

<div class="row dashboard-panel">
    <div class="col-md-3">
        <div class="panel panel-info">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-user"></i> Total Gaji Tercatat
            </div>
            <div class="panel-body text-center">
                <h2><?php echo number_format($total_salaries_count); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="panel panel-success">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-usd"></i> Rata-rata Gaji
            </div>
            <div class="panel-body text-center">
                <h2>Rp<?php echo number_format($average_salary, 0, ',', '.'); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="panel panel-danger">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-arrow-down"></i> Gaji Terendah
            </div>
            <div class="panel-body text-center">
                <h2>Rp<?php echo number_format($lowest_salary, 0, ',', '.'); ?></h2>
            </div>
        </div>
    </div>
    <div class="col-md-3">
        <div class="panel panel-primary">
            <div class="panel-heading">
                <i class="glyphicon glyphicon-arrow-up"></i> Gaji Tertinggi
            </div>
            <div class="panel-body text-center">
                <h2>Rp<?php echo number_format($highest_salary, 0, ',', '.'); ?></h2>
            </div>
        </div>
    </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-exclamation-sign"></span>
                    <span>Daftar Karyawan Belum Ada Gaji</span>
                </strong>
            </div>
            <div class="panel-body">
                <?php if (!empty($employees_without_salary)): ?>
                <div class="table-responsive">
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th class="text-center" style="width: 50px;">#</th>
                                <th>Nama Karyawan</th>
                                <th class="text-center">Departemen</th>
                                <th class="text-center">Jabatan</th>
                                <th class="text-center" style="width: 100px;">Aksi</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $i = 1; ?>
                            <?php foreach ($employees_without_salary as $employee) : ?>
                                <tr>
                                    <td class="text-center"><?php echo (int)$i++; ?></td>
                                    <td><?php echo remove_junk($employee['first_name']) . ' ' . remove_junk($employee['last_name']); ?></td>
                                    <td class="text-center"><?php echo remove_junk($employee['department']); ?></td>
                                    <td class="text-center"><?php echo remove_junk($employee['job_title']); ?></td>
                                    <td class="text-center">
                                        <div class="btn-group">
                                            <a href="add_salary.php?employee_id=<?php echo (int)$employee['id'];?>" class="btn btn-xs btn-primary" title="Tambah Gaji" data-toggle="tooltip">
                                                <i class="glyphicon glyphicon-plus"></i> Tambah
                                            </a>
                                        </div>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <?php else: ?>
                    <div class="alert alert-success text-center">
                        <i class="glyphicon glyphicon-ok-sign"></i> Semua karyawan sudah memiliki data gaji.
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </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-th"></span>
                    <span>Daftar Gaji Karyawan</span>
                </strong>
                <a href="add_salary.php" class="btn btn-info pull-right">Tambah Gaji</a>
            </div>
            <div class="panel-body">
                <form action="" method="GET" class="form-horizontal mb-4">
                    <div class="row">
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="search">Cari Nama / ID</label>
                                <input type="text" name="search" id="search" class="form-control" placeholder="Cari..." value="<?php echo htmlspecialchars($search_term); ?>">
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="department_id">Departemen</label>
                                <select name="department_id" id="department_id" class="form-control">
                                    <option value="0" <?php if ($department_id == 0) echo 'selected'; ?>>Semua Departemen</option>
                                    <?php foreach ($departments as $dept): ?>
                                        <option value="<?php echo (int)$dept['department_id']; ?>" <?php if ($department_id == $dept['department_id']) echo 'selected'; ?>>
                                            <?php echo htmlspecialchars($dept['name']); ?>
                                        </option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-2">
                            <div class="form-group">
                                <label for="min_salary">Min Gaji</label>
                                <input type="number" name="min_salary" id="min_salary" class="form-control" placeholder="Min Gaji" value="<?php echo htmlspecialchars($min_salary); ?>">
                            </div>
                        </div>
                        <div class="col-md-2">
                            <div class="form-group">
                                <label for="max_salary">Max Gaji</label>
                                <input type="number" name="max_salary" id="max_salary" class="form-control" placeholder="Max Gaji" value="<?php echo htmlspecialchars($max_salary); ?>">
                            </div>
                        </div>
                        <div class="col-md-2">
                            <div class="form-group">
                                <label for="per_page">Per Halaman</label>
                                <select name="per_page" id="per_page" class="form-control">
                                    <option value="25" <?php if ($per_page == 25) echo 'selected'; ?>>25</option>
                                    <option value="50" <?php if ($per_page == 50) echo 'selected'; ?>>50</option>
                                    <option value="100" <?php if ($per_page == 100) echo 'selected'; ?>>100</option>
                                </select>
                            </div>
                        </div>
                    </div>
                    <div class="form-group clearfix text-right">
                        <button type="submit" class="btn btn-primary">Filter</button>
                        <a href="gaji.php" class="btn btn-default">Reset</a>
                        <a href="?export_excel=1&search=<?php echo urlencode($search_term); ?>&department_id=<?php echo $department_id; ?>&min_salary=<?php echo $min_salary; ?>&max_salary=<?php echo $max_salary; ?>" class="btn btn-success">Export Excel</a>
                    </div>
                </form>

                <div class="table-responsive">
                    <table class="table table-bordered table-striped">
                        <thead>
                            <tr>
                                <th class="text-center" style="width: 50px;">#</th>
                                <th>Nama Karyawan</th>
                                <th class="text-center">Departemen</th>
                                <th class="text-center">Jabatan</th>
                                <th class="text-center">Tanggal Gaji</th>
                                <th class="text-center">Gaji Pokok</th>
                                <th class="text-center">Tunjangan</th>
                                <th class="text-center">Potongan</th>
                                <th class="text-center">Gaji Bersih</th>
                                <th class="text-center" style="width: 100px;">Aksi</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!empty($salaries)): ?>
                                <?php $i = $start + 1; ?>
                                <?php foreach ($salaries as $salary) : ?>
                                    <tr>
                                        <td class="text-center"><?php echo (int)$i++; ?></td>
                                        <td><?php echo remove_junk($salary['first_name']) . ' ' . remove_junk($salary['last_name']); ?></td>
                                        <td class="text-center"><?php echo remove_junk($salary['department']); ?></td>
                                        <td class="text-center"><?php echo remove_junk($salary['job_title']); ?></td>
                                        <td class="text-center"><?php echo htmlspecialchars($salary['pay_date']); ?></td>
                                        <td class="text-right">Rp<?php echo number_format($salary['basic_salary'], 0, ',', '.'); ?></td>
                                        <td class="text-right">Rp<?php echo number_format($salary['allowances'], 0, ',', '.'); ?></td>
                                        <td class="text-right">Rp<?php echo number_format($salary['deductions'], 0, ',', '.'); ?></td>
                                        <td class="text-right"><strong>Rp<?php echo number_format($salary['net_salary'], 0, ',', '.'); ?></strong></td>
                                        <td class="text-center">
                                            <div class="btn-group">
                                                <a href="edit_salary.php?id=<?php echo (int)$salary['id'];?>" class="btn btn-xs btn-warning" title="Edit" data-toggle="tooltip">
                                                    <i class="glyphicon glyphicon-pencil"></i>
                                                </a>
                                                <a href="delete_salary.php?id=<?php echo (int)$salary['id'];?>" class="btn btn-xs btn-danger" title="Hapus" data-toggle="tooltip" onclick="return confirm('Apakah Anda yakin ingin menghapus data gaji ini?');">
                                                    <i class="glyphicon glyphicon-remove"></i>
                                                </a>
                                                <!-- Tombol cetak slip gaji -->
                                                <a href="slip_gaji.php?id=<?php echo (int)$salary['id']; ?>" class="btn btn-xs btn-info" title="Cetak Slip Gaji" data-toggle="tooltip" target="_blank">
                                                    <i class="glyphicon glyphicon-print"></i>
                                                </a>
                                            </div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="10" class="text-center">Tidak ada data gaji ditemukan.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>

                <?php if ($total_pages > 1): ?>
                    <nav aria-label="Page navigation">
                        <ul class="pagination pull-right">
                            <li class="page-item <?php if ($page <= 1) echo 'disabled'; ?>">
                                <a class="page-link" href="?page=<?php echo $page - 1; ?>&per_page=<?php echo $per_page; ?>&department_id=<?php echo $department_id; ?>&search=<?php echo urlencode($search_term); ?>&min_salary=<?php echo $min_salary; ?>&max_salary=<?php echo $max_salary; ?>">Prev</a>
                            </li>
                            <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                                <li class="page-item <?php if ($i == $page) echo 'active'; ?>">
                                    <a class="page-link" href="?page=<?php echo $i; ?>&per_page=<?php echo $per_page; ?>&department_id=<?php echo $department_id; ?>&search=<?php echo urlencode($search_term); ?>&min_salary=<?php echo $min_salary; ?>&max_salary=<?php echo $max_salary; ?>"><?php echo $i; ?></a>
                                </li>
                            <?php endfor; ?>
                            <li class="page-item <?php if ($page >= $total_pages) echo 'disabled'; ?>">
                                <a class="page-link" href="?page=<?php echo $page + 1; ?>&per_page=<?php echo $per_page; ?>&department_id=<?php echo $department_id; ?>&search=<?php echo urlencode($search_term); ?>&min_salary=<?php echo $min_salary; ?>&max_salary=<?php echo $max_salary; ?>">Next</a>
                            </li>
                        </ul>
                    </nav>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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