<?php
require_once __DIR__ . '/../../config/config.php';
require_once __DIR__ . '/../../includes/functions.php';
startSession(); requireLogin(); requireRole('superadmin','admin','hr','management');
$db   = Database::getInstance();
$page = 'HR Report';

[$eOrg, $eOrgP]   = orgScopeClause($db, 'e.company_id');
[$plainOrg, $plainOrgP] = orgScopeClause($db, 'company_id');

$type = get('type') ?: 'separation';

$reports = [
    'separation'      => 'Separation',
    'joined_list'     => 'Joined List',
    'pending_fnf'     => 'Pending F&F',
    'release'         => 'Release (Relieved)',
    'birthday'        => 'Birthday',
    'marriage_anniv'  => 'Marriage Anniversary',
    'age'             => 'Age Report',
    'emp_history'     => 'Employee History',
    'approval_matrix' => 'Approval Matrix',
    'master_count'    => 'Master Count',
    'payroll_count'   => 'Payroll Count',
    'join_resign_count' => 'Join & Resign Count',
    'birthday_details'=> 'Birthday Details',
    'emp_documents'   => 'Employee Documents',
    'approval_matrix_export' => 'Approval Matrix (Manager Export)',
    'transfer'        => 'Transfer',
];
if (!isset($reports[$type])) $type = 'separation';

$rows = [];
$cols = [];

switch ($type) {

    case 'separation':
        $cols = ['Emp Code','Name','Department','Last Working Date','Reason','Status'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    s.last_working_date, lr.reason AS reason, s.status
               FROM separations s
               JOIN employees e ON e.id=s.employee_id
          LEFT JOIN departments d ON d.id=e.department_id
          LEFT JOIN leaving_reasons lr ON lr.id=s.leaving_reason_id
              WHERE $eOrg ORDER BY s.last_working_date DESC", $eOrgP
        );
        break;

    case 'joined_list':
        $cols = ['Emp Code','Name','Department','Designation','Date of Joining','Employment Type'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    des.name AS designation, e.date_of_joining, e.employment_type
               FROM employees e
          LEFT JOIN departments d ON d.id=e.department_id
          LEFT JOIN designations des ON des.id=e.designation_id
              WHERE $eOrg ORDER BY e.date_of_joining DESC LIMIT 200", $eOrgP
        );
        break;

    case 'pending_fnf':
        $cols = ['Emp Code','Name','Department','Last Working Date','Status'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    s.last_working_date, s.status
               FROM separations s
               JOIN employees e ON e.id=s.employee_id
          LEFT JOIN departments d ON d.id=e.department_id
              WHERE s.status != 'completed' AND $eOrg ORDER BY s.last_working_date", $eOrgP
        );
        break;

    case 'release':
        $cols = ['Emp Code','Name','Department','Last Working Date','Cleared Date'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    s.last_working_date, s.cleared_date
               FROM separations s
               JOIN employees e ON e.id=s.employee_id
          LEFT JOIN departments d ON d.id=e.department_id
              WHERE s.status='completed' AND $eOrg ORDER BY s.cleared_date DESC", $eOrgP
        );
        break;

    case 'birthday':
    case 'birthday_details':
        $cols = $type === 'birthday_details'
            ? ['Emp Code','Name','Department','Designation','Date of Birth','Turning Age','This Year\'s Date']
            : ['Emp Code','Name','Department','Date of Birth'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    des.name AS designation, e.date_of_birth,
                    TIMESTAMPDIFF(YEAR,e.date_of_birth,CURDATE())+1 AS turning_age,
                    DATE_FORMAT(e.date_of_birth, CONCAT(YEAR(CURDATE()),'-%m-%d')) AS this_year_date
               FROM employees e
          LEFT JOIN departments d ON d.id=e.department_id
          LEFT JOIN designations des ON des.id=e.designation_id
              WHERE e.status='active' AND e.date_of_birth IS NOT NULL AND $eOrg
              ORDER BY MONTH(e.date_of_birth), DAY(e.date_of_birth)", $eOrgP
        );
        break;

    case 'marriage_anniv':
        $cols = ['Emp Code','Name','Department','Anniversary Date'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    e.marriage_anniversary_date
               FROM employees e
          LEFT JOIN departments d ON d.id=e.department_id
              WHERE e.status='active' AND e.marriage_anniversary_date IS NOT NULL AND $eOrg
              ORDER BY MONTH(e.marriage_anniversary_date), DAY(e.marriage_anniversary_date)", $eOrgP
        );
        break;

    case 'age':
        $cols = ['Emp Code','Name','Department','Date of Birth','Age'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept,
                    e.date_of_birth, TIMESTAMPDIFF(YEAR,e.date_of_birth,CURDATE()) AS age
               FROM employees e
          LEFT JOIN departments d ON d.id=e.department_id
              WHERE e.status='active' AND e.date_of_birth IS NOT NULL AND $eOrg
              ORDER BY age DESC", $eOrgP
        );
        break;

    case 'emp_history':
        $cols = ['Date','Employee','Action','Module','Changed By'];
        $rows = $db->fetchAll(
            "SELECT al.created_at, CONCAT(e.first_name,' ',e.last_name) AS name, al.action, al.module, u.username AS changed_by
               FROM audit_log al
               JOIN employees e ON e.id = al.record_id AND al.module='employees'
          LEFT JOIN users u ON u.id = al.user_id
              WHERE $eOrg
              ORDER BY al.created_at DESC LIMIT 200", $eOrgP
        );
        break;

    case 'approval_matrix':
    case 'approval_matrix_export':
        $cols = ['Emp Code','Employee','Department','Designation','Reporting Manager','Manager Designation'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, d.name AS dept, des.name AS designation,
                    CONCAT(m.first_name,' ',m.last_name) AS manager_name, mdes.name AS manager_designation
               FROM employees e
          LEFT JOIN departments d ON d.id=e.department_id
          LEFT JOIN designations des ON des.id=e.designation_id
          LEFT JOIN employees m ON m.id=e.reporting_manager_id
          LEFT JOIN designations mdes ON mdes.id=m.designation_id
              WHERE e.status='active' AND $eOrg ORDER BY e.first_name", $eOrgP
        );
        break;

    case 'master_count':
        $cols = ['Master', 'Active Records'];
        $masters = [
            'Departments'  => "SELECT COUNT(*) FROM departments WHERE is_active=1",
            'Designations' => "SELECT COUNT(*) FROM designations WHERE is_active=1",
            'Grades'       => "SELECT COUNT(*) FROM grades WHERE is_active=1",
            'Categories'   => "SELECT COUNT(*) FROM categories WHERE is_active=1",
            'Locations'    => "SELECT COUNT(*) FROM locations WHERE is_active=1",
            'Shifts'       => "SELECT COUNT(*) FROM shifts WHERE is_active=1",
            'Leave Types'  => "SELECT COUNT(*) FROM leave_types WHERE is_active=1",
        ];
        foreach ($masters as $name => $sql) {
            $rows[] = ['master' => $name, 'count' => (int)$db->fetchValue($sql)];
        }
        break;

    case 'payroll_count':
        $cols = ['Month','Year','Status','Employees','Total Gross','Total Net'];
        $rows = $db->fetchAll(
            "SELECT month, year, status, total_employees, total_gross, total_net
               FROM payroll_runs WHERE $plainOrg ORDER BY year DESC, month DESC LIMIT 24", $plainOrgP
        );
        break;

    case 'join_resign_count':
        $cols = ['Month', 'Joined', 'Resigned'];
        for ($i = 11; $i >= 0; $i--) {
            $m = (new DateTime())->modify("-$i months");
            $joined = (int)$db->fetchValue(
                "SELECT COUNT(*) FROM employees WHERE MONTH(date_of_joining)=? AND YEAR(date_of_joining)=? AND $plainOrg",
                array_merge([(int)$m->format('n'), (int)$m->format('Y')], $plainOrgP)
            );
            $resigned = (int)$db->fetchValue(
                "SELECT COUNT(*) FROM separations s JOIN employees e ON e.id=s.employee_id
                  WHERE MONTH(s.last_working_date)=? AND YEAR(s.last_working_date)=? AND $eOrg",
                array_merge([(int)$m->format('n'), (int)$m->format('Y')], $eOrgP)
            );
            $rows[] = ['month' => $m->format('M Y'), 'joined' => $joined, 'resigned' => $resigned];
        }
        break;

    case 'emp_documents':
        $cols = ['Emp Code','Name','Document Type','Document Name','Uploaded On'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name,
                    dt.name AS doc_type, ed.document_name, ed.uploaded_at
               FROM employee_documents ed
               JOIN employees e ON e.id=ed.employee_id
          LEFT JOIN document_types dt ON dt.id=ed.document_type_id
              WHERE $eOrg ORDER BY ed.uploaded_at DESC LIMIT 300", $eOrgP
        );
        break;

    case 'transfer':
        $cols = ['Emp Code','Name','Transfer Date','From Dept','To Dept','From Designation','To Designation','Status'];
        $rows = $db->fetchAll(
            "SELECT e.employee_id, CONCAT(e.first_name,' ',e.last_name) AS name, t.transfer_date,
                    fd.name AS from_dept, td.name AS to_dept, fdes.name AS from_desig, tdes.name AS to_desig, t.status
               FROM employee_transfers t
               JOIN employees e ON e.id=t.employee_id
          LEFT JOIN departments fd ON fd.id=t.from_department_id
          LEFT JOIN departments td ON td.id=t.to_department_id
          LEFT JOIN designations fdes ON fdes.id=t.from_designation_id
          LEFT JOIN designations tdes ON tdes.id=t.to_designation_id
              WHERE $eOrg ORDER BY t.transfer_date DESC", $eOrgP
        );
        break;
}

include __DIR__ . '/../../includes/header.php';
?>
<div class="row g-3">
  <div class="col-md-3">
    <div class="pc-card p-2">
      <div class="pc-card-title">HR Reports</div>
      <div class="list-group list-group-flush">
        <?php foreach ($reports as $key => $label): ?>
        <a href="?type=<?= $key ?>" class="list-group-item list-group-item-action small <?= $type===$key?'active':'' ?>"><?= clean($label) ?></a>
        <?php endforeach; ?>
      </div>
      <div class="alert alert-warning small mt-2 mb-0 py-2">
        <i class="ti ti-info-circle me-1"></i><b>Acknowledge Report</b> needs a policy/payslip acknowledgement-capture feature that doesn't exist yet — flag if you want that built as its own feature first.
      </div>
    </div>
  </div>
  <div class="col-md-9">
    <div class="pc-card">
      <div class="pc-card-title d-flex justify-content-between align-items-center">
        <span><?= clean($reports[$type]) ?> (<?= count($rows) ?>)</span>
        <a href="#" onclick="window.print();return false;" class="small"><i class="ti ti-printer me-1"></i>Print</a>
      </div>
      <div class="pc-table-wrap">
        <table class="pc-table">
          <thead><tr><?php foreach ($cols as $c): ?><th><?= clean($c) ?></th><?php endforeach; ?></tr></thead>
          <tbody>
          <?php if ($type === 'master_count'): ?>
            <?php foreach ($rows as $r): ?><tr><td><?= clean($r['master']) ?></td><td><?= $r['count'] ?></td></tr><?php endforeach; ?>
          <?php elseif ($type === 'join_resign_count'): ?>
            <?php foreach ($rows as $r): ?><tr><td><?= clean($r['month']) ?></td><td><?= $r['joined'] ?></td><td><?= $r['resigned'] ?></td></tr><?php endforeach; ?>
          <?php elseif ($type === 'payroll_count'): ?>
            <?php foreach ($rows as $r): ?>
            <tr>
              <td><?= monthName((int)$r['month']) ?></td><td><?= $r['year'] ?></td><td><?= statusBadge($r['status']) ?></td>
              <td><?= $r['total_employees'] ?></td><td>₹<?= number_format($r['total_gross'],2) ?></td><td>₹<?= number_format($r['total_net'],2) ?></td>
            </tr>
            <?php endforeach; ?>
          <?php else: ?>
            <?php foreach ($rows as $r): ?>
            <tr><?php foreach ($r as $k => $v): if (is_numeric($k)) continue; ?><td><?= clean(is_null($v) ? '—' : $v) ?></td><?php endforeach; ?></tr>
            <?php endforeach; ?>
          <?php endif; ?>
          <?php if (!$rows): ?><tr><td colspan="10" class="text-center text-muted py-4">No records found</td></tr><?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>
<?php include __DIR__ . '/../../includes/footer.php'; ?>
