<?php
declare(strict_types=1);

/**
 * modules/payroll/_helpers.php
 * Shared helpers for Payroll (staff, advances, salaries).
 */

require_once dirname(__DIR__, 2) . '/config/functions.php';

require_role(['MD','Accounts','Admin']); // protect all importers

/** Quick table existence check */
function pr_table_exists(string $t): bool {
  try { db()->query("SELECT 1 FROM `{$t}` LIMIT 1"); return true; }
  catch (Throwable $e) { return false; }
}

/** Best-effort: ensure all payroll tables exist (idempotent) */
function pr_ensure_tables(): void {
  $pdo = db();

  // staff_members
  if (!pr_table_exists('staff_members')) {
    $pdo->exec("
      CREATE TABLE IF NOT EXISTS staff_members (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        company_id INT UNSIGNED NOT NULL,
        full_name VARCHAR(255) NOT NULL,
        email VARCHAR(255) DEFAULT NULL,
        phone VARCHAR(50)  DEFAULT NULL,
        address TEXT DEFAULT NULL,
        job_title VARCHAR(128) DEFAULT NULL,
        pay_cycle ENUM('monthly','weekly') NOT NULL DEFAULT 'monthly',
        salary_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        salary_day TINYINT UNSIGNED DEFAULT 25,
        salary_weekday TINYINT UNSIGNED DEFAULT 5,
        start_date DATE DEFAULT NULL,
        end_date DATE DEFAULT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        bank_details TEXT DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        KEY idx_company_active (company_id, is_active),
        KEY idx_company_name (company_id, full_name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
  }

  // salary_advances
  if (!pr_table_exists('salary_advances')) {
    $pdo->exec("
      CREATE TABLE IF NOT EXISTS salary_advances (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        company_id INT UNSIGNED NOT NULL,
        staff_id INT UNSIGNED NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        paid_at DATETIME NOT NULL,
        method VARCHAR(64)  DEFAULT NULL,
        reference VARCHAR(128) DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        period_type ENUM('monthly','weekly') NOT NULL,
        period_label VARCHAR(16) NOT NULL,
        period_start DATE NOT NULL,
        period_end DATE NOT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_company_staff (company_id, staff_id),
        KEY idx_company_period (company_id, period_type, period_label),
        CONSTRAINT fk_adv_staff FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
  }

  // salaries
  if (!pr_table_exists('salaries')) {
    $pdo->exec("
      CREATE TABLE IF NOT EXISTS salaries (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        company_id INT UNSIGNED NOT NULL,
        staff_id INT UNSIGNED NOT NULL,
        period_type ENUM('monthly','weekly') NOT NULL,
        period_label VARCHAR(16) NOT NULL,
        period_start DATE NOT NULL,
        period_end DATE NOT NULL,
        gross_amount DECIMAL(10,2) NOT NULL,
        advances_offset DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        net_paid DECIMAL(10,2) NOT NULL,
        paid_at DATETIME NOT NULL,
        method VARCHAR(64)  DEFAULT NULL,
        reference VARCHAR(128) DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_company_staff_period (company_id, staff_id, period_type, period_label),
        CONSTRAINT fk_sal_staff FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
  }
}

/** Parse JSON or key:value bank text (similar to partners/drivers) */
function pr_parse_bank(?string $raw): array {
  $out = [
    'bank_name'=>'','account_name'=>'','account_number'=>'','sort_code'=>'',
    'iban'=>'','swift'=>'','notes'=>'','raw'=>''
  ];
  if (!$raw) return $out;
  $s = trim($raw);

  $decoded = json_decode($s, true);
  if (is_array($decoded)) {
    foreach (array_keys($out) as $k) {
      if (isset($decoded[$k]) && is_string($decoded[$k])) $out[$k] = trim($decoded[$k]);
    }
    return $out;
  }
  $lines = preg_split('/\r\n|\r|\n/', $s);
  foreach ($lines as $line) {
    $line = trim($line); if ($line==='') continue;
    if (preg_match('/^(bank\s*name)\s*[:\-]\s*(.+)$/i', $line, $m)) { $out['bank_name']=trim($m[2]); continue; }
    if (preg_match('/^(account\s*name|account\s*holder)\s*[:\-]\s*(.+)$/i', $line, $m)) { $out['account_name']=trim($m[2]); continue; }
    if (preg_match('/^(account\s*number|acc(?:ount)?\.?|acc)\s*[:\-]\s*(.+)$/i', $line, $m)) { $out['account_number']=trim($m[2]); continue; }
    if (preg_match('/^(sort\s*code|sort)\s*[:\-]\s*([0-9\-\s]+)$/i', $line, $m)) { $out['sort_code']=trim($m[2]); continue; }
    if (preg_match('/^(iban)\s*[:\-]\s*(.+)$/i', $line, $m)) { $out['iban']=trim($m[2]); continue; }
    if (preg_match('/^(swift|bic)\s*[:\-]\s*(.+)$/i', $line, $m)) { $out['swift']=trim($m[2]); continue; }
    $out['raw'] .= ($out['raw'] ? "\n" : "") . $line;
  }
  return $out;
}

/** Clamp day to month length (1..28/29/30/31) */
function pr_clamp_day_for_month(int $year, int $month, int $day): int {
  $max = (int)cal_days_in_month(CAL_GREGORIAN, $month, $year);
  if ($day < 1) $day = 1;
  if ($day > $max) $day = $max;
  return $day;
}

/** Compute monthly period (label, start, end) for a given date */
function pr_month_period(DateTimeImmutable $dt): array {
  $y = (int)$dt->format('Y');
  $m = (int)$dt->format('m');
  $start = new DateTimeImmutable(sprintf('%04d-%02d-01', $y, $m));
  $end   = $start->modify('last day of this month');
  return [
    'type'  => 'monthly',
    'label' => $start->format('Y-m'),
    'start' => $start->format('Y-m-d'),
    'end'   => $end->format('Y-m-d'),
  ];
}

/** Compute ISO week period (Mon..Sun) and label YYYY-Www for a given date */
function pr_week_period(DateTimeImmutable $dt): array {
  $isoWeek = (int)$dt->format('W');
  $isoYear = (int)$dt->format('o'); // ISO year
  // Monday of ISO week:
  $monday  = new DateTimeImmutable($isoYear . "W" . str_pad((string)$isoWeek, 2, '0', STR_PAD_LEFT));
  $sunday  = $monday->modify('+6 days');
  return [
    'type'  => 'weekly',
    'label' => sprintf('%04d-W%02d', $isoYear, $isoWeek),
    'start' => $monday->format('Y-m-d'),
    'end'   => $sunday->format('Y-m-d'),
  ];
}

/**
 * Return payroll period for a staff & date based on staff.pay_cycle
 * @param array $staff row from staff_members
 * @param string|null $when 'Y-m-d H:i:s' (defaults now)
 * @return array {type,label,start,end, paid_day_date?}
 */
function pr_staff_period_for_date(array $staff, ?string $when = null): array {
  $dt = $when ? new DateTimeImmutable($when) : new DateTimeImmutable('now');
  if (($staff['pay_cycle'] ?? 'monthly') === 'weekly') {
    return pr_week_period($dt);
  }
  return pr_month_period($dt);
}

/** Sum advances for a given staff + period */
function pr_sum_advances(int $companyId, int $staffId, string $periodType, string $periodLabel): float {
  try {
    $q = db()->prepare("
      SELECT COALESCE(SUM(amount),0)
        FROM salary_advances
       WHERE company_id = :c AND staff_id = :s
         AND period_type = :t AND period_label = :l
    ");
    $q->execute([':c'=>$companyId, ':s'=>$staffId, ':t'=>$periodType, ':l'=>$periodLabel]);
    return (float)$q->fetchColumn();
  } catch (Throwable $e) { return 0.0; }
}

/** Insert a transactions row (accounting) */
function pr_tx_insert(int $companyId, string $dateYmd, string $accountCode, float $amount, ?string $reference = null, ?string $notes = null): void {
  if (!pr_table_exists('transactions')) throw new RuntimeException('transactions table missing.');
  $ins = db()->prepare("
    INSERT INTO transactions
      (company_id, date, account_code, segment, type, amount, booking_id, rental_id, invoice_id, reference, milestone, notes, attachment_url, created_at)
    VALUES
      (:cid, :dt, :acc, NULL, 'expense', :amt, NULL, NULL, NULL, :ref, NULL, :notes, NULL, NOW())
  ");
  $ins->execute([
    ':cid'=>$companyId, ':dt'=>$dateYmd, ':acc'=>$accountCode,
    ':amt'=>$amount, ':ref'=>$reference, ':notes'=>$notes ?: null
  ]);
}

/** Compute next pay date (pretty) for list view */
function pr_next_paydate_label(array $s): string {
  $today = new DateTimeImmutable('today');
  if (($s['pay_cycle'] ?? 'monthly') === 'weekly') {
    $wd = (int)($s['salary_weekday'] ?? 5); // 1..7
    // find next weekday >= today
    $dowToday = (int)$today->format('N'); // 1..7
    $delta = $wd - $dowToday; if ($delta < 0) $delta += 7;
    $next = $today->modify("+{$delta} day");
    return $next->format('D, d M Y');
  }
  $day = (int)($s['salary_day'] ?? 25);
  $y = (int)$today->format('Y'); $m = (int)$today->format('m');
  $clamped = pr_clamp_day_for_month($y, $m, $day);
  $candidate = new DateTimeImmutable(sprintf('%04d-%02d-%02d', $y, $m, $clamped));
  if ($candidate < $today) {
    // next month
    $nm = (int)$today->modify('first day of next month')->format('m');
    $ny = (int)$today->modify('first day of next month')->format('Y');
    $clamped = pr_clamp_day_for_month($ny, $nm, $day);
    $candidate = new DateTimeImmutable(sprintf('%04d-%02d-%02d', $ny, $nm, $clamped));
  }
  return $candidate->format('D, d M Y');
}
