<?php
// finance/my_pay.php (FULL FIXED - dynamic schema + privacy-safe + no is_active dependency)
declare(strict_types=1);

require_once __DIR__ . '/_layout.php';

finance_shell_start('my');
page_header('راتبي');

$userId = (int)($_SESSION['user_id'] ?? 0);

/* ===== Helpers ===== */
function my_table_cols(PDO $pdo, string $table): array {
  $st = $pdo->query("SHOW COLUMNS FROM `$table`");
  return $st->fetchAll(PDO::FETCH_COLUMN, 0) ?: [];
}
function my_first_existing(array $cols, array $candidates): ?string {
  foreach ($candidates as $c) {
    if (in_array($c, $cols, true)) return $c;
  }
  return null;
}
function my_emp_name_expr(PDO $pdo, string $alias='e'): string {
  $cols = my_table_cols($pdo, 'employees');

  $nameCol = my_first_existing($cols, ['full_name','name','emp_name','employee','arabic_name','first_name']);
  $lastCol = my_first_existing($cols, ['last_name','family_name']);

  if ($nameCol && $nameCol !== 'first_name') {
    return "NULLIF(TRIM($alias.`$nameCol`),'')";
  }
  if ($nameCol === 'first_name' && $lastCol) {
    return "NULLIF(TRIM(CONCAT(COALESCE($alias.`first_name`,''),' ',COALESCE($alias.`$lastCol`,''))),'')";
  }
  return "CONCAT('EMP#', $alias.id)";
}

/* ===== Detect schema ===== */
$empCols = my_table_cols($pdo, 'employees');
$salCols = my_table_cols($pdo, 'employee_salaries');
$bonCols = my_table_cols($pdo, 'employee_bonuses');
$dedCols = my_table_cols($pdo, 'employee_deductions');

$userLinkCol = my_first_existing($empCols, ['user_id','userId','uid']);
$empNameExpr = my_emp_name_expr($pdo, 'e');

if (!$userLinkCol) {
  echo "<div class='card'>لا يمكن ربط المستخدم بالموظف لأن عمود user_id غير موجود في جدول employees.</div>";
  finance_shell_end();
  exit;
}

/* ===== Get current employee ===== */
$st = $pdo->prepare("
  SELECT e.id, COALESCE($empNameExpr, CONCAT('EMP#', e.id)) AS emp_name
  FROM employees e
  WHERE e.`$userLinkCol` = ?
  LIMIT 1
");
$st->execute([$userId]);
$emp = $st->fetch();

if (!$emp) {
  echo "<div class='card'>لم يتم ربط حسابك بملف موظف بعد.</div>";
  finance_shell_end();
  exit;
}

$employeeId = (int)$emp['id'];
$empName    = (string)$emp['emp_name'];

/* ===== Current month ===== */
$month = (string)($_GET['m'] ?? date('Y-m'));
if (!preg_match('/^\d{4}-\d{2}$/', $month)) $month = date('Y-m');
$from = $month . '-01';
$to   = date('Y-m-t', strtotime($from));

/* ===== Latest salary row only ===== */
$effSelect = in_array('effective_from', $salCols, true) ? "s.effective_from" : "NULL AS effective_from";
$curSelect = in_array('currency', $salCols, true) ? "COALESCE(s.currency,'AED') AS currency" : "'AED' AS currency";

$st = $pdo->prepare("
  SELECT
    COALESCE(s.basic_salary,0) AS basic_salary,
    COALESCE(s.housing_allowance,0) AS housing_allowance,
    COALESCE(s.transport_allowance,0) AS transport_allowance,
    COALESCE(s.other_allowance,0) AS other_allowance,
    $curSelect,
    $effSelect
  FROM employee_salaries s
  WHERE s.employee_id = ?
  ORDER BY s.id DESC
  LIMIT 1
");
$st->execute([$employeeId]);
$s = $st->fetch() ?: [
  'basic_salary' => 0,
  'housing_allowance' => 0,
  'transport_allowance' => 0,
  'other_allowance' => 0,
  'currency' => 'AED',
  'effective_from' => null,
];

$basic = (float)$s['basic_salary'];
$allow = (float)$s['housing_allowance'] + (float)$s['transport_allowance'] + (float)$s['other_allowance'];
$cur   = (string)($s['currency'] ?? 'AED');

/* ===== Bonuses for selected month ===== */
$hasBonMonth = in_array('payroll_month', $bonCols, true);
$bonDateCol  = in_array('bonus_date', $bonCols, true) ? 'bonus_date' : (in_array('created_at', $bonCols, true) ? 'created_at' : null);

if ($hasBonMonth) {
  $st = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_bonuses WHERE employee_id=? AND payroll_month=?");
  $st->execute([$employeeId, $month]);
  $bon = (float)$st->fetchColumn();
} elseif ($bonDateCol) {
  $st = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_bonuses WHERE employee_id=? AND DATE(`$bonDateCol`) BETWEEN ? AND ?");
  $st->execute([$employeeId, $from, $to]);
  $bon = (float)$st->fetchColumn();
} else {
  $bon = 0.0;
}

/* ===== Deductions for selected month ===== */
$hasDedMonth = in_array('payroll_month', $dedCols, true);
$dedDateCol  = in_array('deduction_date', $dedCols, true) ? 'deduction_date' : (in_array('created_at', $dedCols, true) ? 'created_at' : null);

if ($hasDedMonth) {
  $st = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_deductions WHERE employee_id=? AND payroll_month=?");
  $st->execute([$employeeId, $month]);
  $ded = (float)$st->fetchColumn();
} elseif ($dedDateCol) {
  $st = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_deductions WHERE employee_id=? AND DATE(`$dedDateCol`) BETWEEN ? AND ?");
  $st->execute([$employeeId, $from, $to]);
  $ded = (float)$st->fetchColumn();
} else {
  $ded = 0.0;
}

$net = $basic + $allow + $bon - $ded;

/* ===== Recent bonuses ===== */
$bonusDateSelect = in_array('bonus_date', $bonCols, true)
  ? "b.bonus_date"
  : (in_array('created_at', $bonCols, true) ? "DATE(b.created_at) AS bonus_date" : "NULL AS bonus_date");

$st = $pdo->prepare("
  SELECT $bonusDateSelect, b.amount, b.reason
  FROM employee_bonuses b
  WHERE b.employee_id=?
  ORDER BY b.id DESC
  LIMIT 8
");
$st->execute([$employeeId]);
$lastBon = $st->fetchAll() ?: [];

/* ===== Recent deductions ===== */
$deductionDateSelect = in_array('deduction_date', $dedCols, true)
  ? "d.deduction_date"
  : (in_array('created_at', $dedCols, true) ? "DATE(d.created_at) AS deduction_date" : "NULL AS deduction_date");

$st = $pdo->prepare("
  SELECT $deductionDateSelect, d.amount, d.reason
  FROM employee_deductions d
  WHERE d.employee_id=?
  ORDER BY d.id DESC
  LIMIT 8
");
$st->execute([$employeeId]);
$lastDed = $st->fetchAll() ?: [];
?>

<style>
.table{width:100%;border-collapse:separate;border-spacing:0;overflow:hidden;border-radius:16px}
.table th,.table td{padding:10px 12px;border-bottom:1px solid #eef2f7;text-align:right;font-size:13px}
.table thead th{background:#f8fafc;color:#0f172a;font-weight:700}
.table tr:last-child td{border-bottom:none}
</style>

<div class="card" style="margin-bottom:12px">
  <form method="get" class="row">
    <select class="select" name="m">
      <?php for($i=0;$i<12;$i++):
        $m = date('Y-m', strtotime(date('Y-m-01') . " -$i months"));
        $sel = ($m === $month) ? 'selected' : '';
      ?>
        <option <?= $sel ?> value="<?= h($m) ?>"><?= h($m) ?></option>
      <?php endfor; ?>
    </select>
    <button class="btn" type="submit">عرض</button>
  </form>
</div>

<div class="grid">
  <div class="card kpi">
    <div class="k">الموظف</div>
    <div class="v"><?= h($empName) ?></div>
    <div class="s">عرض خاص بك فقط</div>
  </div>

  <div class="card kpi">
    <div class="k">الراتب الأساسي</div>
    <div class="v"><?= number_format($basic,2) ?> <?= h($cur) ?></div>
    <div class="s">سريان: <?= h((string)($s['effective_from'] ?? '')) ?></div>
  </div>

  <div class="card kpi">
    <div class="k">البدلات</div>
    <div class="v"><?= number_format($allow,2) ?> <?= h($cur) ?></div>
    <div class="s">سكن + مواصلات + أخرى</div>
  </div>

  <div class="card kpi">
    <div class="k">مكافآت الشهر</div>
    <div class="v"><?= number_format($bon,2) ?> <?= h($cur) ?></div>
    <div class="s"><?= h($month) ?></div>
  </div>

  <div class="card kpi">
    <div class="k">خصومات الشهر</div>
    <div class="v"><?= number_format($ded,2) ?> <?= h($cur) ?></div>
    <div class="s"><?= h($month) ?></div>
  </div>

  <div class="card" style="grid-column:span 12">
    <h3 style="margin:0 0 10px 0;color:#fff">الصافي التقديري للشهر</h3>
    <div style="font-size:26px;color:var(--teal);font-weight:700"><?= number_format($net,2) ?> <?= h($cur) ?></div>
  </div>

  <div class="card" style="grid-column:span 6">
    <h3 style="margin:0 0 10px 0;color:#fff">آخر المكافآت</h3>
    <table class="table">
      <thead>
        <tr><th>التاريخ</th><th>المبلغ</th><th>السبب</th></tr>
      </thead>
      <tbody>
        <?php if(!$lastBon): ?>
          <tr><td colspan="3">لا توجد</td></tr>
        <?php else: foreach($lastBon as $r): ?>
          <tr>
            <td><?= h((string)($r['bonus_date'] ?? '')) ?></td>
            <td><?= number_format((float)$r['amount'],2) ?> <?= h($cur) ?></td>
            <td><?= h((string)($r['reason'] ?? '')) ?></td>
          </tr>
        <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>

  <div class="card" style="grid-column:span 6">
    <h3 style="margin:0 0 10px 0;color:#fff">آخر الخصومات</h3>
    <table class="table">
      <thead>
        <tr><th>التاريخ</th><th>المبلغ</th><th>السبب</th></tr>
      </thead>
      <tbody>
        <?php if(!$lastDed): ?>
          <tr><td colspan="3">لا توجد</td></tr>
        <?php else: foreach($lastDed as $r): ?>
          <tr>
            <td><?= h((string)($r['deduction_date'] ?? '')) ?></td>
            <td><?= number_format((float)$r['amount'],2) ?> <?= h($cur) ?></td>
            <td><?= h((string)($r['reason'] ?? '')) ?></td>
          </tr>
        <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>
</div>

<?php finance_shell_end(); ?>