<?php
require_once __DIR__ . '/../../config/config.php';
require_once __DIR__ . '/../../includes/functions.php';
startSession(); requireLogin();
$db     = Database::getInstance();
$page   = 'Statutory Reports';
$month  = intval($_GET['month'] ?? date('n'));
$year   = intval($_GET['year']  ?? date('Y'));
$type   = $_GET['type'] ?? 'pf';
$format = $_GET['format'] ?? 'html';
$months = ['','January','February','March','April','May','June','July','August','September','October','November','December'];

// ── Fetch data ────────────────────────────────────────────────
// FIX (07 Jul 2026): none of these 4 reports were scoped to the current
// org — they exposed UAN/PF/PAN numbers across every organization.
[$orgClause, $orgParams] = orgScopeClause($db, 'e.company_id');
$data = []; $cols = []; $title = '';
if ($type === 'pf') {
    $title = 'PF Register';
    $data  = $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
    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.first_name", array_merge([$month,$year], $orgParams));
} elseif ($type === 'esi') {
    $title = 'ESI Register';
    $data  = $db->fetchAll("SELECT e.employee_id AS emp_code,
        CONCAT(e.first_name,' ',e.last_name) AS emp_name,
        e.esic_number,
        COALESCE(pd.gross_salary,0) AS gross_wages,
        COALESCE(pd.esi_employee,0) AS ee_esi,
        COALESCE(pd.esi_employer,0) AS er_esi,
        COALESCE(pd.esi_employee,0)+COALESCE(pd.esi_employer,0) AS total_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.esi_applicable=1 AND $orgClause
    ORDER BY e.first_name", array_merge([$month,$year], $orgParams));
} elseif ($type === 'pt') {
    $title = 'Professional Tax';
    $data  = $db->fetchAll("SELECT e.employee_id AS emp_code,
        CONCAT(e.first_name,' ',e.last_name) AS emp_name,
        e.state_of_work,
        COALESCE(pd.gross_salary,0) AS gross_salary,
        COALESCE(pd.professional_tax,0) AS pt_amount
    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.pt_applicable=1 AND $orgClause
    ORDER BY e.first_name", array_merge([$month,$year], $orgParams));
} elseif ($type === 'tds') {
    $title = 'TDS Report';
    $data  = $db->fetchAll("SELECT e.employee_id AS emp_code,
        CONCAT(e.first_name,' ',e.last_name) AS emp_name,
        e.pan_number, e.tax_regime,
        COALESCE(tc.gross_income,0) AS gross_income,
        COALESCE(tc.net_taxable_income,0) AS taxable_income,
        COALESCE(tc.tds_this_month,0) AS tds_month,
        COALESCE(tc.tds_deducted_ytd,0) AS tds_ytd
    FROM employees e
    LEFT JOIN tds_calculations tc ON tc.employee_id=e.id AND tc.month=? AND tc.year=?
    WHERE e.status='active' AND $orgClause
    ORDER BY e.first_name", array_merge([$month,$year], $orgParams));
}

// ── CSV Export ────────────────────────────────────────────────
if ($format === 'csv') {
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="'.$type.'_'.$months[$month].'_'.$year.'.csv"');
    header('Pragma: no-cache');
    $out = fopen('php://output','w');
    if ($type === 'pf') {
        fputcsv($out,['Emp Code','Name','UAN','PF Number','PF Wages','EE PF (12%)','ER PF (12%)','Total PF']);
        foreach ($data 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']]);
    } elseif ($type === 'esi') {
        fputcsv($out,['Emp Code','Name','ESIC Number','Gross Wages','EE ESI (0.75%)','ER ESI (3.25%)','Total ESI']);
        foreach ($data as $r) fputcsv($out,[$r['emp_code'],$r['emp_name'],$r['esic_number']??'',$r['gross_wages'],$r['ee_esi'],$r['er_esi'],$r['total_esi']]);
    } elseif ($type === 'pt') {
        fputcsv($out,['Emp Code','Name','State','Gross Salary','PT Amount']);
        foreach ($data as $r) fputcsv($out,[$r['emp_code'],$r['emp_name'],$r['state_of_work']??'',$r['gross_salary'],$r['pt_amount']]);
    } elseif ($type === 'tds') {
        fputcsv($out,['Emp Code','Name','PAN','Regime','Gross Income','Taxable Income','TDS This Month','TDS YTD']);
        foreach ($data as $r) fputcsv($out,[$r['emp_code'],$r['emp_name'],$r['pan_number']??'',$r['tax_regime']??'',$r['gross_income'],$r['taxable_income'],$r['tds_month'],$r['tds_ytd']]);
    }
    fclose($out); exit;
}

include __DIR__ . '/../../includes/header.php';
?>
<div class="pc-card mb-3">
  <form method="GET" class="d-flex gap-2 flex-wrap align-items-end">
    <div><label class="pc-label">Report Type</label>
      <select name="type" class="form-select form-select-sm">
        <?php foreach (['pf'=>'PF Register','esi'=>'ESI Register','pt'=>'Professional Tax','tds'=>'TDS Report'] as $v=>$l): ?>
        <option value="<?=$v?>" <?=$type===$v?'selected':''?>><?=$l?></option>
        <?php endforeach; ?>
      </select></div>
    <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?>" <?=$month==$m?'selected':''?>><?=$months[$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=date('Y');$y>=date('Y')-3;$y--): ?><option value="<?=$y?>" <?=$year==$y?'selected':''?>><?=$y?></option><?php endfor; ?>
      </select></div>
    <button class="btn btn-pc btn-sm"><i class="ti ti-search me-1"></i>Generate</button>
    <?php if ($data): ?>
    <a href="?type=<?=$type?>&month=<?=$month?>&year=<?=$year?>&format=csv" class="btn btn-sm btn-outline-success"><i class="ti ti-download me-1"></i>Export CSV</a>
    <button type="button" onclick="window.print()" class="btn btn-sm btn-outline-secondary"><i class="ti ti-printer me-1"></i>Print</button>
    <?php endif; ?>
  </form>
</div>

<div class="pc-card">
  <div class="pc-card-title"><?=$title?> — <?=$months[$month]?> <?=$year?>
    <?php if($data): ?><span style="font-weight:400;font-size:12px;color:var(--pc-text2)"><?=count($data)?> employees</span><?php endif; ?>
  </div>
  <div class="pc-table-wrap">
    <?php if ($type === 'pf'): ?>
    <table class="pc-table">
      <thead><tr><th>Emp Code</th><th>Name</th><th>UAN</th><th>PF No</th><th>PF Wages</th><th>EE PF (12%)</th><th>ER PF (12%)</th><th>Total</th></tr></thead>
      <tbody>
      <?php $totEePf=$totErPf=0; foreach($data as $r): $totEePf+=$r['ee_pf']; $totErPf+=$r['er_pf']; ?>
      <tr><td><?=clean($r['emp_code'])?></td><td class="fw-semibold"><?=clean($r['emp_name'])?></td>
          <td><?=clean($r['uan_number']??'—')?></td><td><?=clean($r['pf_number']??'—')?></td>
          <td>₹<?=number_format($r['pf_wages'],2)?></td>
          <td>₹<?=number_format($r['ee_pf'],2)?></td><td>₹<?=number_format($r['er_pf'],2)?></td>
          <td class="fw-bold">₹<?=number_format($r['total_pf'],2)?></td></tr>
      <?php endforeach; if(!$data): ?><tr><td colspan="8" class="text-center text-muted py-3">No PF data. Process payroll first.</td></tr><?php endif; ?>
      <?php if($data): ?><tr style="background:var(--pc-brand-light);font-weight:700"><td colspan="5">TOTAL</td><td>₹<?=number_format($totEePf,2)?></td><td>₹<?=number_format($totErPf,2)?></td><td>₹<?=number_format($totEePf+$totErPf,2)?></td></tr><?php endif; ?>
      </tbody>
    </table>

    <?php elseif ($type === 'esi'): ?>
    <table class="pc-table">
      <thead><tr><th>Emp Code</th><th>Name</th><th>ESIC No</th><th>Gross Wages</th><th>EE ESI (0.75%)</th><th>ER ESI (3.25%)</th><th>Total</th></tr></thead>
      <tbody>
      <?php $totEe=$totEr=0; foreach($data as $r): $totEe+=$r['ee_esi']; $totEr+=$r['er_esi']; ?>
      <tr><td><?=clean($r['emp_code'])?></td><td class="fw-semibold"><?=clean($r['emp_name'])?></td>
          <td><?=clean($r['esic_number']??'—')?></td><td>₹<?=number_format($r['gross_wages'],2)?></td>
          <td>₹<?=number_format($r['ee_esi'],2)?></td><td>₹<?=number_format($r['er_esi'],2)?></td>
          <td class="fw-bold">₹<?=number_format($r['total_esi'],2)?></td></tr>
      <?php endforeach; if(!$data): ?><tr><td colspan="7" class="text-center text-muted py-3">No ESI data. Process payroll first.</td></tr><?php endif; ?>
      <?php if($data): ?><tr style="background:var(--pc-brand-light);font-weight:700"><td colspan="4">TOTAL</td><td>₹<?=number_format($totEe,2)?></td><td>₹<?=number_format($totEr,2)?></td><td>₹<?=number_format($totEe+$totEr,2)?></td></tr><?php endif; ?>
      </tbody>
    </table>

    <?php elseif ($type === 'pt'): ?>
    <table class="pc-table">
      <thead><tr><th>Emp Code</th><th>Name</th><th>State</th><th>Gross Salary</th><th>PT Amount</th></tr></thead>
      <tbody>
      <?php $totPt=0; foreach($data as $r): $totPt+=$r['pt_amount']; ?>
      <tr><td><?=clean($r['emp_code'])?></td><td class="fw-semibold"><?=clean($r['emp_name'])?></td>
          <td><?=clean($r['state_of_work']??'—')?></td><td>₹<?=number_format($r['gross_salary'],2)?></td>
          <td class="fw-bold">₹<?=number_format($r['pt_amount'],2)?></td></tr>
      <?php endforeach; if(!$data): ?><tr><td colspan="5" class="text-center text-muted py-3">No PT data.</td></tr><?php endif; ?>
      <?php if($data): ?><tr style="background:var(--pc-brand-light);font-weight:700"><td colspan="4">TOTAL</td><td>₹<?=number_format($totPt,2)?></td></tr><?php endif; ?>
      </tbody>
    </table>

    <?php elseif ($type === 'tds'): ?>
    <table class="pc-table">
      <thead><tr><th>Emp Code</th><th>Name</th><th>PAN</th><th>Regime</th><th>Gross Income</th><th>Taxable Income</th><th>TDS Month</th><th>TDS YTD</th></tr></thead>
      <tbody>
      <?php $totTds=0; foreach($data as $r): $totTds+=$r['tds_month']; ?>
      <tr><td><?=clean($r['emp_code'])?></td><td class="fw-semibold"><?=clean($r['emp_name'])?></td>
          <td><code><?=clean($r['pan_number']??'—')?></code></td>
          <td><?=ucfirst($r['tax_regime']??'new')?></td>
          <td>₹<?=number_format($r['gross_income'],2)?></td>
          <td>₹<?=number_format($r['taxable_income'],2)?></td>
          <td class="fw-bold">₹<?=number_format($r['tds_month'],2)?></td>
          <td>₹<?=number_format($r['tds_ytd'],2)?></td></tr>
      <?php endforeach; if(!$data): ?><tr><td colspan="8" class="text-center text-muted py-3">No TDS data. Process payroll first.</td></tr><?php endif; ?>
      <?php if($data): ?><tr style="background:var(--pc-brand-light);font-weight:700"><td colspan="6">TOTAL TDS</td><td>₹<?=number_format($totTds,2)?></td><td></td></tr><?php endif; ?>
      </tbody>
    </table>
    <?php endif; ?>
  </div>
</div>
<style>@media print{.pc-topbar,.pc-sidebar,.pc-page-header,.pc-card:first-child{display:none!important}}</style>
<?php include __DIR__ . '/../../includes/footer.php'; ?>