<?php
// payroll_reports.php
// Updated: Removed all division color code and badges. Division now displays as plain text.

session_start();
// Path to payslip viewer — change this if your payslip viewer is in a different location
$payslip_path = '/mnt/data/payslip.php';
if (!isset($_SESSION['username']) || !in_array($_SESSION['role'], ['admin','superadmin','manager'])) {
    header('Location: index.php'); exit;
}

include_once 'config.php';
if (!isset($conn) || !($conn instanceof mysqli)) { die('Database connection not configured.'); }

function h($s){ return htmlspecialchars($s, ENT_QUOTES, 'UTF-8'); }

// Inputs
$month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('n');
$year  = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');
$division = isset($_GET['division']) ? trim($_GET['division']) : '';
$employee = isset($_GET['employee']) ? trim($_GET['employee']) : '';
$show_overall = isset($_GET['overall']) && $_GET['overall'] === '1';
$month_str = sprintf('%04d-%02d', $year, $month);

// Pagination
$per_page = 50;
$page = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page - 1) * $per_page;
$export_csv = isset($_GET['export']) && $_GET['export'] === 'csv';

// Build where clauses and params
$where_parts = ['p.month = ?'];
$params = [$month_str];
$types = 's';
if ($division !== '') { $where_parts[] = '(p.division = ? OR u.division = ?)'; $params[] = $division; $params[] = $division; $types .= 'ss'; }
if ($employee !== '') {
    if (ctype_digit($employee)) { $where_parts[] = 'p.emp_id = ?'; $params[] = (int)$employee; $types .= 'i'; }
    else {
        $where_parts[] = '(u.first_name LIKE CONCAT("%", ?, "%") OR u.last_name LIKE CONCAT("%", ?, "%") OR u.username LIKE CONCAT("%", ?, "%") OR p.name LIKE CONCAT("%", ?, "%"))';
        $params[] = $employee; $params[] = $employee; $params[] = $employee; $params[] = $employee; $types .= 'ssss';
    }
}
$where_sql = implode(' AND ', $where_parts);

// Helper to bind params dynamically
function bind_params_stmt(mysqli_stmt $stmt, $types, $params) {
    if (count($params) === 0) return;
    $refs = [];
    $refs[] = &$types;
    foreach ($params as $k => $v) { $refs[] = &$params[$k]; }
    call_user_func_array([$stmt, 'bind_param'], $refs);
}

// Count total
$count_sql = "SELECT COUNT(*) FROM payroll p LEFT JOIN users u ON p.emp_id = u.id WHERE $where_sql";
$stmt = $conn->prepare($count_sql);
if ($stmt === false) { die('Prepare failed: '.$conn->error); }
bind_params_stmt($stmt, $types, $params);
$stmt->execute();
$stmt->bind_result($total_rows);
$stmt->fetch();
$stmt->close();

$total_pages = max(1, (int)ceil($total_rows / $per_page));

// Fetch rows (include users.division explicitly)
$select_sql = "SELECT p.*, u.username, u.first_name, u.last_name, u.division AS user_division, u.uan_no, u.esic_no, u.bank_name, u.account_number, u.ifsc_code
               FROM payroll p
               LEFT JOIN users u ON p.emp_id = u.id
               WHERE $where_sql
               ORDER BY p.emp_id ASC
               LIMIT ? OFFSET ?";
$params_page = $params;
$types_page = $types . 'ii';
$params_page[] = $per_page;
$params_page[] = $offset;
$stmt = $conn->prepare($select_sql);
if ($stmt === false) { die('Prepare failed: '.$conn->error); }
bind_params_stmt($stmt, $types_page, $params_page);
$stmt->execute();
$result = $stmt->get_result();

$rows = [];
$gross_page = $ded_page = $net_page = 0.0;
while ($r = $result->fetch_assoc()) {
    $emp_id_val = $r['emp_id'];
    $first = trim($r['first_name'] ?? '');
    $last = trim($r['last_name'] ?? '');
    $name_from_users = trim(($first . ' ' . $last));
    $display_name = $name_from_users !== '' ? $name_from_users : trim($r['name'] ?? $r['username'] ?? $emp_id_val);
    $division_val = $r['user_division'] ?: ($r['division'] ?? '');

    $gross = isset($r['gross_salary']) ? floatval($r['gross_salary']) : floatval($r['salary'] ?? 0);
    $deductions = 0.0;
    if (isset($r['total']) && floatval($r['total']) > 0) { $deductions = floatval($r['total']); }
    else { $deductions = floatval($r['epf'] ?? 0) + floatval($r['esi'] ?? 0) + floatval($r['tds'] ?? 0) + floatval($r['advance'] ?? 0); }
    $net = isset($r['grand_total']) ? floatval($r['grand_total']) : (floatval($r['total_wages'] ?? 0) - $deductions);

    $r['_emp_id'] = $emp_id_val;
    $r['_display_name'] = $display_name;
    $r['_division'] = $division_val;
    $r['_gross'] = $gross;
    $r['_deductions'] = $deductions;
    $r['_net'] = $net;

    $gross_page += $gross; $ded_page += $deductions; $net_page += $net;
    $rows[] = $r;
}
$stmt->close();

// Overall totals if requested
$gross_total = $ded_total = $net_total = 0.0;
if ($show_overall) {
    $tot_sql = "SELECT
                    COALESCE(SUM(COALESCE(p.gross_salary, p.salary, 0)),0) AS gross_sum,
                    COALESCE(SUM(
                        CASE WHEN COALESCE(p.total,0) > 0 THEN p.total ELSE COALESCE(p.epf,0)+COALESCE(p.esi,0)+COALESCE(p.tds,0)+COALESCE(p.advance,0) END
                    ),0) AS ded_sum,
                    COALESCE(SUM(COALESCE(p.grand_total, (COALESCE(p.total_wages,0) - (
                        CASE WHEN COALESCE(p.total,0) > 0 THEN p.total ELSE COALESCE(p.epf,0)+COALESCE(p.esi,0)+COALESCE(p.tds,0)+COALESCE(p.advance,0) END
                    )) ,0)),0) AS net_sum
                FROM payroll p
                LEFT JOIN users u ON p.emp_id = u.id
                WHERE $where_sql";
    $stmt = $conn->prepare($tot_sql);
    if ($stmt === false) { die('Prepare failed: '.$conn->error); }
    bind_params_stmt($stmt, $types, $params);
    $stmt->execute();
    $stmt->bind_result($gross_total, $ded_total, $net_total);
    $stmt->fetch();
    $stmt->close();
}

// CSV export
if ($export_csv) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="payroll_' . $year . '_' . sprintf('%02d', $month) . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Emp ID','NAME','Division','Gross','Deductions','Net','Month','Created At','UAN','ESIC','Bank','A/C','IFSC']);
    foreach ($rows as $row) {
        fputcsv($out, [
            $row['_emp_id'] ?? '',
            $row['_display_name'] ?? '',
            $row['_division'] ?? '',
            number_format($row['_gross'],2,'.',''),
            number_format($row['_deductions'],2,'.',''),
            number_format($row['_net'],2,'.',''),
            $row['month'] ?? '',
            $row['created_at'] ?? '',
            $row['uan_no'] ?? '',
            $row['esic_no'] ?? '',
            $row['bank_name'] ?? '',
            $row['account_number'] ?? '',
            $row['ifsc_code'] ?? ''
        ]);
    }
    fclose($out);
    exit;
}

// Divisions: fetch primarily from users table, then payroll as fallback. Exclude 'general'
$divisions = [];
$dv = $conn->query("SELECT DISTINCT division FROM users WHERE division IS NOT NULL AND division != '' ORDER BY division");
if ($dv) { while ($d = $dv->fetch_assoc()) { $div = trim($d['division']); if ($div !== '' && !in_array(strtolower($div), array_map('strtolower', $divisions))) $divisions[] = $div; } }
$dv2 = $conn->query("SELECT DISTINCT division FROM payroll WHERE division IS NOT NULL AND division != '' ORDER BY division");
if ($dv2) { while ($d = $dv2->fetch_assoc()) { $div = trim($d['division']); if ($div !== '' && !in_array(strtolower($div), array_map('strtolower', $divisions))) $divisions[] = $div; } }
$divisions = array_filter($divisions, function($v){ return strtolower(trim($v)) !== 'general'; });
sort($divisions);

// Division colors removed
$division_colors = [];

?><!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Payroll Reports - <?php echo h($month_str); ?></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<style>@media print{.no-print{display:none;}}.table-fixed{table-layout:fixed;word-wrap:break-word;}.sort-ind{cursor:pointer;}
.name-link{font-weight:600}
</style>
</head>
<body class="p-3">
<div class="container-fluid">
  <div class="row mb-3">
    <div class="col-12 d-flex justify-content-between align-items-center">
      <h3 class="m-0">Payroll Reports <small class="text-muted"><?php echo h($month_str); ?></small></h3>
      <div class="btn-group no-print">
        <a class="btn btn-outline-primary" href="?<?php echo h(http_build_query(array_merge($_GET, ['export'=>'csv']))); ?>">Export CSV</a>
        <button class="btn btn-outline-success" onclick="window.print()">Print</button>
      </div>
    </div>
  </div>

  <div class="row">
    <div class="col-lg-3 col-md-4 mb-3">
      <div class="card">
        <div class="card-body">
          <h5 class="card-title">Filters</h5>
          <form method="get">
            <div class="mb-2"><label class="form-label small">Month</label>
              <select name="month" class="form-select form-select-sm">
                <?php for($m=1;$m<=12;$m++): ?><option value="<?php echo $m; ?>" <?php if($m==$month) echo 'selected'; ?>><?php echo date('F', mktime(0,0,0,$m,1)); ?></option><?php endfor; ?>
              </select></div>
            <div class="mb-2"><label class="form-label small">Year</label>
              <select name="year" class="form-select form-select-sm"><?php for($y=date('Y')-3;$y<=date('Y')+1;$y++): ?><option value="<?php echo $y; ?>" <?php if($y==$year) echo 'selected'; ?>><?php echo $y; ?></option><?php endfor; ?></select></div>
            <div class="mb-2"><label class="form-label small">Division</label>
              <select name="division" class="form-select form-select-sm"><option value="">All</option><?php foreach($divisions as $d): ?><option value="<?php echo h($d); ?>" <?php if($d==$division) echo 'selected'; ?>><?php echo h($d); ?></option><?php endforeach; ?></select></div>
            <div class="mb-2"><label class="form-label small">Employee</label>
              <input type="text" name="employee" value="<?php echo h($employee); ?>" class="form-control form-control-sm" placeholder="emp id, username or name"></div>
            <div class="form-check form-switch mb-2"><input class="form-check-input" type="checkbox" id="overall" name="overall" value="1" <?php if($show_overall) echo 'checked'; ?>><label class="form-check-label small" for="overall">Show overall totals</label></div>
            <div class="d-grid"><button class="btn btn-primary btn-sm">Apply</button></div>
          </form>

          <hr>
          <h6 class="small">Divisions</h6>
          <div class="div-legend" id="divisionLegend">
            <?php foreach($divisions as $dn): ?>
              <span class="badge-division" data-division="<?php echo h($dn); ?>" style="cursor:pointer"><?php echo h($dn); ?></span>
            <?php endforeach; ?>
          </div>
        </div>
      </div>

{"multiple":false,"replacement":"
      <div class="card mt-3">
        <div class="card-body">
          <h6 class="card-subtitle mb-2 text-muted">Summary</h6>
          <p class="mb-1">Records: <strong><?php echo number_format($total_rows); ?></strong></p>
          <p class="mb-1">Page Gross: <strong><?php echo number_format($gross_page,2); ?></strong></p>
          <p class="mb-1">Page Net: <strong><?php echo number_format($net_page,2); ?></strong></p>
        </div>
      </div>
    </div>

    <div class="col-lg-9 col-md-8">
      <div class="card">
        <div class="card-body">
          <div class="table-responsive">
            <table class="table table-bordered table-sm table-fixed" id="payrollTable">
              <thead class="table-light"><tr>
                <th class="sort-ind" data-sort="int">Emp ID</th>
                <th class="sort-ind" data-sort="string">NAME</th>
                <th class="sort-ind" data-sort="string">Division</th>
                <th class="sort-ind" data-sort="float">Gross</th>
                <th class="sort-ind" data-sort="float">Deductions</th>
                <th class="sort-ind" data-sort="float">Net</th>
                <th>UAN</th>
                <th>ESIC</th>
                <th>Bank / A/C</th>
                <th>IFSC</th>
                <th>Month</th>
                <th>Created At</th>
              </tr></thead>
              <tbody>
                <?php if(count($rows)===0): ?>
                  <tr><td colspan="12" class="text-center">No records found.</td></tr>
                <?php else: foreach($rows as $r):
                  $divname = $r['_division'];
                  // Payslip URL (local file path) — may need mapping in production
                  $payslip_url = $payslip_path . '?emp_id=' . urlencode($r['_emp_id']) . '&month=' . urlencode($r['month']);
                ?>
                  <tr data-division="<?php echo h($divname); ?>">
                    <td><?php echo h($r['_emp_id']); ?></td>
                    <td><a class="name-link" href="<?php echo h($payslip_url); ?>" target="_blank"><?php echo h($r['_display_name']); ?></a></td>
                    <td><?php echo h($divname); ?></td>
                    <td><?php echo number_format($r['_gross'],2); ?></td>
                    <td><?php echo number_format($r['_deductions'],2); ?></td>
                    <td><?php echo number_format($r['_net'],2); ?></td>
                    <td><?php echo h($r['uan_no'] ?? ''); ?></td>
                    <td><?php echo h($r['esic_no'] ?? ''); ?></td>
                    <td><?php echo h(($r['bank_name'] ?? '') . ' / ' . ($r['account_number'] ?? '')); ?></td>
                    <td><?php echo h($r['ifsc_code'] ?? ''); ?></td>
                    <td><?php echo h($r['month'] ?? ''); ?></td>
                    <td><?php echo h($r['created_at'] ?? ''); ?></td>
                  </tr>
                <?php endforeach; endif; ?>
              </tbody>
              <tfoot class="table-light">
                <tr>
                  <th colspan="3">Page Totals</th>
                  <th><?php echo number_format($gross_page,2); ?></th>
                  <th><?php echo number_format($ded_page,2); ?></th>
                  <th><?php echo number_format($net_page,2); ?></th>
                  <th colspan="6"></th>
                </tr>
                <?php if($show_overall): ?>
                <tr>
                  <th colspan="3">Overall Totals</th>
                  <th><?php echo number_format($gross_total,2); ?></th>
                  <th><?php echo number_format($ded_total,2); ?></th>
                  <th><?php echo number_format($net_total,2); ?></th>
                  <th colspan="6"></th>
                </tr>
                <?php endif; ?>
              </tfoot>
            </table>
          </div>

          <!-- Pagination -->
          <nav class="mt-3 no-print"><ul class="pagination">
            <?php for($p=1;$p<=$total_pages;$p++): ?><li class="page-item <?php if($p==$page) echo 'active'; ?>"><a class="page-link" href="?<?php echo h(http_build_query(array_merge($_GET,['page'=>$p]))); ?>"><?php echo $p; ?></a></li><?php endfor; ?>
          </ul></nav>
        </div>
      </div>
    </div>
  </div>
</div>

<script>
// Division badge click to filter table rows client-side
document.querySelectorAll('.div-legend .badge-division').forEach(function(b){
  b.addEventListener('click', function(){
    var div = this.getAttribute('data-division');
    // set the division select and submit the form
    var sel = document.querySelector('select[name="division"]');
    if(sel){ sel.value = div; sel.form.submit(); }
  });
});

// Simple client-side sorting
(function(){
  const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;
  const comparer = function(idx, type, asc) { return function(a,b){
    let v1 = getCellValue(a, idx).trim();
    let v2 = getCellValue(b, idx).trim();
    if(type==='int' || type==='float'){
      v1 = v1 === '' ? 0 : Number(v1.replace(/,/g,''));
      v2 = v2 === '' ? 0 : Number(v2.replace(/,/g,''));
    }
    if(v1 === v2) return 0;
    return ((v1 > v2) ? 1 : -1) * (asc?1:-1);
  }};
  document.querySelectorAll('#payrollTable thead th.sort-ind').forEach(function(th,idx){
    th.addEventListener('click', function(){
      const table = th.closest('table');
      const tbody = table.querySelector('tbody');
      const arr = Array.from(tbody.querySelectorAll('tr'));
      const type = th.getAttribute('data-sort') || 'string';
      const asc = !th.classList.contains('asc');
      arr.sort(comparer(idx, type, asc));
      arr.forEach(tr => tbody.appendChild(tr));
      table.querySelectorAll('thead th').forEach(x=>x.classList.remove('asc','desc'));
      th.classList.add(asc?'asc':'desc');
    });
  });
})();
</script>
</body>
</html>
