<?php
require_once __DIR__ . "/../../config/config.php";
require_once __DIR__ . "/../../includes/functions.php";
startSession(); requireLogin();
$db = Database::getInstance();
$page = "Custom Report";

$allFields = [
    "emp"   => ["label"=>"Employee Info", "fields"=>["employee_id"=>"Employee Code","first_name"=>"First Name","last_name"=>"Last Name","date_of_joining"=>"Joining Date","date_of_birth"=>"Date of Birth","gender"=>"Gender","mobile"=>"Mobile","employment_type"=>"Employment Type","status"=>"Status"]],
    "dept"  => ["label"=>"Department / Role", "fields"=>["d.name AS department"=>"Department","des.name AS designation"=>"Designation","g.name AS grade"=>"Grade","l.name AS location"=>"Location"]],
    "salary"=> ["label"=>"Salary", "fields"=>["es.gross_monthly"=>"Gross Monthly","es.ctc_annual"=>"Annual CTC","es.basic"=>"Basic"]],
];

$generate = false; $results = []; $selFields = $_POST["fields"] ?? [];

if ($_SERVER["REQUEST_METHOD"]==="POST" && isset($_POST["generate"])) {
    verifyCsrf();
    $generate = true;
    if ($selFields) {
        $select = ["e.id"];
        $joins = ["LEFT JOIN departments d ON d.id=e.department_id","LEFT JOIN designations des ON des.id=e.designation_id","LEFT JOIN grades g ON g.id=e.grade_id","LEFT JOIN locations l ON l.id=e.location_id","LEFT JOIN employee_salary es ON es.employee_id=e.id"];
        foreach ($selFields as $f) $select[] = (strpos($f,'.') !== false) ? $f : "e.$f";
        $dept   = postInt("filter_dept");
        $status = post("filter_status");
        // FIX (07 Jul 2026): scoped to current org (this used to report
        // across every organization's employees), and parameterized the
        // status filter (it was raw string concatenation into the SQL).
        [$orgClause, $params] = orgScopeClause($db, 'e.company_id');
        $where  = [$orgClause];
        if ($dept)    { $where[] = "e.department_id=?"; $params[] = $dept; }
        if ($status)  { $where[] = "e.status=?"; $params[] = $status; }
        $sql = "SELECT ".implode(",",$select)." FROM employees e ".implode(" ",$joins)." WHERE ".implode(" AND ",$where)." ORDER BY e.first_name";
        $results = $db->fetchAll($sql, $params);
    }
}

$depts = $db->fetchAll("SELECT id,name FROM departments WHERE is_active=1 ORDER BY name");

include __DIR__ . "/../../includes/header.php";
?>
<div class="row g-3">
  <div class="col-md-4">
    <div class="pc-card">
      <div class="pc-card-title"><i class="ti ti-adjustments me-2"></i>Report Builder</div>
      <form method="POST">
        <?=csrfField()?>
        <div class="pc-section-divider">Select Fields</div>
        <?php foreach($allFields as $grp=>$group): ?>
        <div class="mb-2"><div class="fw-semibold small mb-1" style="color:var(--pc-brand)"><?=$group["label"]?></div>
        <?php foreach($group["fields"] as $fk=>$fl): ?>
        <div class="form-check"><input type="checkbox" name="fields[]" value="<?=$fk?>" class="form-check-input" id="f_<?=md5($fk)?>" <?=in_array($fk,$selFields)?"checked":""?>><label class="form-check-label small" for="f_<?=md5($fk)?>"><?=$fl?></label></div>
        <?php endforeach; ?></div>
        <?php endforeach; ?>
        <div class="pc-section-divider">Filters</div>
        <div class="mb-2"><label class="pc-label">Department</label><select name="filter_dept" class="form-select form-select-sm"><option value="">All</option><?php foreach($depts as $d): ?><option value="<?=$d["id"]?>"><?=clean($d["name"])?></option><?php endforeach; ?></select></div>
        <div class="mb-3"><label class="pc-label">Status</label><select name="filter_status" class="form-select form-select-sm"><option value="">All</option><option value="active">Active</option><option value="inactive">Inactive</option></select></div>
        <button type="submit" name="generate" class="btn btn-pc btn-sm w-100"><i class="ti ti-report me-1"></i>Generate Report</button>
      </form>
    </div>
  </div>
  <div class="col-md-8">
    <div class="pc-card">
      <div class="pc-card-title"><i class="ti ti-table me-2"></i>Report Output <?=$generate?"(".count($results)." rows)":""?></div>
      <?php if($generate && $selFields && $results): ?>
      <div class="pc-table-wrap">
        <table class="pc-table">
          <thead><tr><?php foreach($selFields as $f):
            $label = "";
            foreach($allFields as $g) foreach($g["fields"] as $k=>$l) if($k===$f) $label=$l;
          ?><th><?=$label?:'—'?></th><?php endforeach; ?></tr></thead>
          <tbody>
          <?php foreach($results as $row): $vals=array_values($row); array_shift($vals); ?>
          <tr><?php foreach($vals as $v): ?><td><?=clean($v??"—")?></td><?php endforeach; ?></tr>
          <?php endforeach; ?>
          </tbody>
        </table>
      </div>
      <?php elseif($generate && !$selFields): ?><div class="alert alert-warning small">Please select at least one field.</div>
      <?php elseif($generate): ?><div class="text-muted text-center py-4">No data matching the selected filters.</div>
      <?php else: ?><div class="text-muted text-center py-4"><i class="ti ti-adjustments" style="font-size:32px;opacity:0.3"></i><br>Configure your report and click Generate</div><?php endif; ?>
    </div>
  </div>
</div>
<?php include __DIR__ . "/../../includes/footer.php"; ?>