<?php
// ============================================================
// modules/statutory/pf.php – PF / ECR Management
// ============================================================
require_once __DIR__ . '/../../config/config.php';
require_once __DIR__ . '/../../includes/functions.php';
startSession(); requireLogin(); requireRole('superadmin','admin','hr','management');
$db   = Database::getInstance();
$page = 'PF / ECR';

$month = cleanInt($_GET['month'] ?? date('n'));
$year  = cleanInt($_GET['year']  ?? date('Y'));

// CSV Export of PF Contribution Register
if ($_GET['action'] ?? '' === 'export') {
    // FIX (07 Jul 2026): exports UAN/PF numbers with no org scoping at all.
    [$orgClause, $orgParams] = orgScopeClause($db, 'e.company_id');
    $pfData = $db->fetchAll("SELECT e.employee_id AS emp_code,
        CONCAT(e.first_name,' ',e.last_name) AS emp_name,
        e.uan_number, e.pf_number,
        COALESCE(pd.basic,0) AS pf_wages,
        COALESCE(pd.pf_employee,0) AS ee_pf,
        COALESCE(pd.pf_employer,0) AS er_pf,
        COALESCE(pd.pf_employee,0)+COALESCE(pd.pf_employer,0) AS total_pf,
        COALESCE(pd.esi_employee,0) AS ee_esi,
        COALESCE(pd.esi_employer,0) AS er_esi
        FROM employees e
        LEFT JOIN payroll_details pd ON pd.employee_id=e.id
        LEFT JOIN payroll_runs pr ON pr.id=pd.payroll_run_id AND pr.month=? AND pr.year=?
        WHERE e.status='active' AND e.pf_applicable=1 AND $orgClause
        ORDER BY e.employee_id", array_merge([$month,$year], $orgParams));
    $mnames = ['','January','February','March','April','May','June','July','August','September','October','November','December'];
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="pf_register_'.$mnames[$month].'_'.$year.'.csv"');
    header('Pragma: no-cache');
    $out = fopen('php://output','w');
    fputcsv($out,['Emp Code','Name','UAN Number','PF Number','PF Wages (Basic)','EE PF (12%)','ER PF (12%)','Total PF','EE ESI','ER ESI']);
    $totPf = $totEsi = 0;
    foreach ($pfData as $r) {
        fputcsv($out,[$r['emp_code'],$r['emp_name'],$r['uan_number']??'',$r['pf_number']??'',$r['pf_wages'],$r['ee_pf'],$r['er_pf'],$r['total_pf'],$r['ee_esi'],$r['er_esi']]);
        $totPf  += $r['total_pf'];
        $totEsi += $r['ee_esi'] + $r['er_esi'];
    }
    fputcsv($out,['','TOTAL','','','','','',number_format($totPf,2),'',number_format($totEsi,2)]);
    fclose($out); exit;
}

// Generate ECR
if ($_SERVER['REQUEST_METHOD'] === 'POST' && post('action') === 'generate_ecr') {
    verifyCsrf();
    $m = postInt('month'); $y = postInt('year');
    // FIX (07 Jul 2026): scope to current org — ECR is a statutory filing
    // file with UAN numbers, this used to include every organization's.
    [$orgClause, $orgParams] = orgScopeClause($db, 'e.company_id');
    $details = $db->fetchAll(
        "SELECT pd.*, e.uan_number, e.first_name, e.last_name, e.employee_id AS emp_code,
                e.pf_number, e.date_of_joining
           FROM payroll_details pd
           JOIN employees e ON e.id=pd.employee_id
           JOIN payroll_runs pr ON pr.id=pd.payroll_run_id
          WHERE pd.month=? AND pd.year=? AND pr.status IN ('posted','disbursed')
            AND e.pf_applicable=1 AND e.uan_number IS NOT NULL AND e.uan_number != '' AND $orgClause
          ORDER BY e.employee_id",
        array_merge([$m, $y], $orgParams)
    );
    if (!$details) { setFlash('error','No PF data found. Ensure payroll is posted.'); header('Location: pf.php'); exit; }

    // Generate ECR text format
    header('Content-Type: text/plain');
    header('Content-Disposition: attachment; filename="ECR_'.str_pad($m,2,'0',STR_PAD_LEFT).'_'.$y.'.txt"');

    $pfSetup = $db->fetchOne("SELECT * FROM pf_setup WHERE is_active=1 LIMIT 1");
    echo "#~#" . ($pfSetup['establishment_id']??'') . "#~#ECR#~#1.0#~#\n";
    foreach ($details as $d) {
        $epfWages = min($d['basic'] + ($d['da']??0), 15000);
        $epsWages = min($epfWages, 15000);
        $eeContrib = round($epfWages * 12 / 100);
        $erContrib = round($epsWages * 12 / 100);
        $eps       = round($epsWages * 8.33 / 100);
        $edli      = round($epfWages * 0.5 / 100);

        echo implode('#~#', [
            $d['uan_number'], $d['first_name'].' '.$d['last_name'],
            (int)$epfWages, (int)$epsWages, $eeContrib, $erContrib,
            $eps, $edli, 0, 0, 0, 0
        ]) . "\n";
    }
    exit;
}

// Fetch PF register data
// FIX (07 Jul 2026): scope to current org.
[$orgClause2, $orgParams2] = orgScopeClause($db, 'e.company_id');
$pfData = $db->fetchAll(
    "SELECT pd.*, e.employee_id AS emp_code, e.first_name, e.last_name,
            e.uan_number, e.pf_number, e.date_of_joining,
            d.name AS dept
       FROM payroll_details pd
       JOIN employees e ON e.id=pd.employee_id
  LEFT JOIN departments d ON d.id=e.department_id
       JOIN payroll_runs pr ON pr.id=pd.payroll_run_id
      WHERE pd.month=? AND pd.year=? AND e.pf_applicable=1 AND $orgClause2
      ORDER BY e.employee_id",
    array_merge([$month, $year], $orgParams2)
);

$pfSetup = $db->fetchOne("SELECT * FROM pf_setup WHERE is_active=1 LIMIT 1");

$totEE = array_sum(array_column($pfData, 'pf_employee'));
$totER = array_sum(array_column($pfData, 'pf_employer'));

$headerActions = '
<form method="POST" class="d-inline">
  '.csrfField().'
  <input type="hidden" name="action" value="generate_ecr">
  <input type="hidden" name="month" value="'.$month.'">
  <input type="hidden" name="year"  value="'.$year.'">
  <button class="btn btn-pc btn-sm"><i class="ti ti-download me-1"></i>Download ECR</button>
</form>';
include __DIR__ . '/../../includes/header.php';
?>

<!-- Setup Info -->
<div class="row g-3 mb-3">
  <div class="col-md-6">
    <div class="pc-card">
      <div class="pc-card-title">PF Establishment Details</div>
      <table class="table table-sm mb-0" style="font-size:12px">
        <?php foreach ([
          ['Establishment ID',$pfSetup['establishment_id']??'—'],
          ['Establishment Name',$pfSetup['establishment_name']??'—'],
          ['PF Employee Rate',$pfSetup['pf_employee_rate'].'%'],
          ['PF Employer Rate',$pfSetup['pf_employer_rate'].'%'],
          ['EPS Rate',$pfSetup['eps_rate'].'%'],
          ['EDLI Rate',$pfSetup['edli_rate'].'%'],
          ['Wage Ceiling',money($pfSetup['pf_wage_ceiling'])],
        ] as [$k,$v]): ?>
        <tr><th width="55%"><?= $k ?></th><td><?= clean($v) ?></td></tr>
        <?php endforeach; ?>
      </table>
    </div>
  </div>
  <div class="col-md-6">
    <div class="row g-2">
      <?php foreach ([
        ['PF Members', count($pfData), 'brand'],
        ['Employee Share', money($totEE), 'blue'],
        ['Employer Share', money($totER), 'green'],
        ['Total Contribution', money($totEE + $totER), 'amber'],
      ] as [$lbl,$val,$cls]): ?>
      <div class="col-6">
        <div class="pc-stat text-center">
          <div class="pc-stat-icon <?= $cls ?>" style="margin:0 auto 8px"><i class="ti ti-building-bank"></i></div>
          <div class="pc-stat-val" style="font-size:16px"><?= $val ?></div>
          <div class="pc-stat-label"><?= $lbl ?></div>
        </div>
      </div>
      <?php endforeach; ?>
    </div>
  </div>
</div>

<!-- Filters -->
<div class="pc-card mb-3">
  <form method="GET" class="pc-filters">
    <div><label class="pc-label">Month</label>
      <select name="month" class="form-select form-select-sm">
        <?php for ($m=1;$m<=12;$m++): ?>
        <option value="<?= $m ?>" <?= $m===$month?'selected':'' ?>><?= monthName($m) ?></option>
        <?php endfor; ?>
      </select></div>
    <div><label class="pc-label">Year</label>
      <select name="year" class="form-select form-select-sm">
        <?php for ($y=2024;$y<=date('Y')+1;$y++): ?>
        <option value="<?= $y ?>" <?= $y===$year?'selected':'' ?>><?= $y ?></option>
        <?php endfor; ?>
      </select></div>
    <div style="margin-top:auto"><button class="btn btn-pc btn-sm">View</button></div>
  </form>
</div>

<!-- PF Register -->
<div class="pc-card">
  <div class="d-flex justify-content-between align-items-center mb-3">
    <div class="fw-semibold">PF Contribution Register – <?= monthName($month) ?> <?= $year ?></div>
    <a href="?action=export&month=<?= $month ?>&year=<?= $year ?>" class="btn btn-sm btn-outline-success">
      <i class="ti ti-download me-1"></i>Export CSV</a>
  </div>
  <div class="pc-table-wrap">
    <table class="pc-table" style="font-size:11px">
      <thead>
        <tr><th>Employee</th><th>UAN</th><th>PF No.</th><th>Dept</th>
          <th>Basic+DA</th><th>EPF Wages</th><th>EE (12%)</th><th>ER (12%)</th>
          <th>EPS (8.33%)</th><th>Total</th></tr>
      </thead>
      <tbody>
      <?php
      $totBDA=0; $totEPF=0; $totEEPF=0; $totERPF=0; $totEPS=0;
      foreach ($pfData as $p):
        $bda = $p['basic'] + ($p['da']??0);
        $epf = min($bda, $pfSetup['pf_wage_ceiling']??15000);
        $ee  = round($epf * ($pfSetup['pf_employee_rate']??12) / 100, 2);
        $er  = round($epf * ($pfSetup['pf_employer_rate']??12) / 100, 2);
        $eps = round(min($epf,15000) * ($pfSetup['eps_rate']??8.33) / 100, 2);
        $totBDA+=$bda; $totEPF+=$epf; $totEEPF+=$ee; $totERPF+=$er; $totEPS+=$eps;
      ?>
      <tr>
        <td><div class="fw-semibold"><?= clean($p['first_name'].' '.$p['last_name']) ?></div>
          <div style="font-size:10px;color:var(--pc-text2)"><?= clean($p['emp_code']) ?></div></td>
        <td style="font-size:10px"><?= clean($p['uan_number']??'—') ?></td>
        <td style="font-size:10px"><?= clean($p['pf_number']??'—') ?></td>
        <td><?= clean($p['dept']??'') ?></td>
        <td><?= money($bda) ?></td>
        <td><?= money($epf) ?></td>
        <td style="color:var(--pc-brand)"><?= money($ee) ?></td>
        <td style="color:var(--pc-success-rgb,#16a34a)"><?= money($er) ?></td>
        <td><?= money($eps) ?></td>
        <td class="fw-semibold"><?= money($ee+$er) ?></td>
      </tr>
      <?php endforeach; ?>
      </tbody>
      <tfoot>
        <tr class="fw-bold" style="background:var(--pc-brand-light)">
          <td colspan="4">TOTAL</td>
          <td><?= money($totBDA) ?></td><td><?= money($totEPF) ?></td>
          <td><?= money($totEEPF) ?></td><td><?= money($totERPF) ?></td>
          <td><?= money($totEPS) ?></td><td><?= money($totEEPF+$totERPF) ?></td>
        </tr>
      </tfoot>
    </table>
  </div>
</div>
<?php include __DIR__ . '/../../includes/footer.php'; ?>