<?php
/**
 * OTP helper (MySQLi)
 * - Sends email using PHPMailer via send_mail() from mailer.php
 * - Does NOT assume your otps table has a `used` column
 * - Supports: `used` OR `used_at` columns if they exist
 */

require_once __DIR__ . '/mailer.php'; // ensures send_mail() exists

/* Check if a column exists on a table (safe) */
function otp_has_column(mysqli $conn, string $table, string $column): bool {
  $db = $conn->query("SELECT DATABASE() db")->fetch_assoc()['db'] ?? '';
  if ($db === '') return false;

  $st = $conn->prepare("
    SELECT COUNT(*) c
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=?
    LIMIT 1
  ");
  $st->bind_param("sss", $db, $table, $column);
  $st->execute();
  $row = $st->get_result()->fetch_assoc();
  return (int)($row['c'] ?? 0) > 0;
}

if (!function_exists('otp_create')) {

function otp_create(
  mysqli $conn,
  int $userId,
  string $purpose,
  string $email,
  int $expiryMinutes = 5
): bool {

  $code = (string) random_int(100000, 999999);
  $expiresAt = date('Y-m-d H:i:s', time() + ($expiryMinutes * 60));

  // Detect columns once
  $hasUsed   = otp_has_column($conn, 'otps', 'used');
  $hasUsedAt = otp_has_column($conn, 'otps', 'used_at');

  // Build INSERT that matches your schema
  if ($hasUsed && $hasUsedAt) {
    $st = $conn->prepare("
      INSERT INTO otps (user_id, purpose, code, expires_at, used, used_at)
      VALUES (?, ?, ?, ?, 0, NULL)
    ");
    $st->bind_param("isss", $userId, $purpose, $code, $expiresAt);

  } elseif ($hasUsed) {
    $st = $conn->prepare("
      INSERT INTO otps (user_id, purpose, code, expires_at, used)
      VALUES (?, ?, ?, ?, 0)
    ");
    $st->bind_param("isss", $userId, $purpose, $code, $expiresAt);

  } elseif ($hasUsedAt) {
    $st = $conn->prepare("
      INSERT INTO otps (user_id, purpose, code, expires_at, used_at)
      VALUES (?, ?, ?, ?, NULL)
    ");
    $st->bind_param("isss", $userId, $purpose, $code, $expiresAt);

  } else {
    // minimal schema (most common)
    $st = $conn->prepare("
      INSERT INTO otps (user_id, purpose, code, expires_at)
      VALUES (?, ?, ?, ?)
    ");
    $st->bind_param("isss", $userId, $purpose, $code, $expiresAt);
  }

  if (!$st->execute()) {
    error_log('OTP DB INSERT FAILED: ' . $st->error);
    return false;
  }

  // Send OTP email via PHPMailer
  if (!function_exists('send_mail')) {
    error_log('OTP MAIL FAILED: send_mail() missing');
    return false;
  }

  $subject = 'Your One-Time Password (OTP)';
  $body = "
Hello,<br><br>
Your OTP code is:<br>
<h2 style='margin:8px 0'>{$code}</h2>
This code will expire in <b>{$expiryMinutes} minutes</b>.<br><br>
If you did not request this, contact support immediately.<br><br>
Regards,<br>
Astrabank Security Team
";

  try {
    $sent = send_mail($email, $subject, $body);
    if (!$sent) {
      error_log("OTP MAIL FAILED: send_mail returned false ({$email})");
      return false;
    }
  } catch (Throwable $e) {
    error_log('OTP MAIL EXCEPTION: ' . $e->getMessage());
    return false;
  }

  return true;
}

}

if (!function_exists('otp_verify')) {

function otp_verify(
  mysqli $conn,
  int $userId,
  string $purpose,
  string $code
): bool {

  $hasUsed   = otp_has_column($conn, 'otps', 'used');
  $hasUsedAt = otp_has_column($conn, 'otps', 'used_at');

  // Build WHERE based on schema
  $whereUsed = "";
  if ($hasUsed)   $whereUsed .= " AND used=0 ";
  if ($hasUsedAt) $whereUsed .= " AND used_at IS NULL ";

  $st = $conn->prepare("
    SELECT id
    FROM otps
    WHERE user_id=?
      AND purpose=?
      AND code=?
      {$whereUsed}
      AND expires_at > NOW()
    LIMIT 1
  ");
  $st->bind_param("iss", $userId, $purpose, $code);
  $st->execute();
  $row = $st->get_result()->fetch_assoc();

  if (!$row) return false;

  $otpId = (int)$row['id'];

  // Mark used based on schema
  if ($hasUsed && $hasUsedAt) {
    $up = $conn->prepare("UPDATE otps SET used=1, used_at=NOW() WHERE id=?");
    $up->bind_param("i", $otpId);
    $up->execute();

  } elseif ($hasUsed) {
    $up = $conn->prepare("UPDATE otps SET used=1 WHERE id=?");
    $up->bind_param("i", $otpId);
    $up->execute();

  } elseif ($hasUsedAt) {
    $up = $conn->prepare("UPDATE otps SET used_at=NOW() WHERE id=?");
    $up->bind_param("i", $otpId);
    $up->execute();

  } else {
    // If schema has no used markers, do nothing (still valid for expiry-based OTP)
  }

  return true;
}

}
