<?php
include __DIR__ . "/../../global.php";

class product_ajax extends object_class
{

  public $productClass;
  public $webClass;
  public $rating;
  public $tw;

  public function __construct()
  {
    parent::__construct('3');

    $this->functions->require_once_custom('webProduct_functions');
    $this->productClass = new webProduct_functions();
    $this->functions->require_once_custom('twilio_whatsapp');
    $this->tw = new TwilioWhatsApp();
    $this->webClass = $GLOBALS['webClass'];
  }

  public function addNewUser()
  {
    if (isset($_POST["password"], $_POST["email"])) {
      $name = trim($_POST["name"] ?? '');
      $email = strip_tags(strtolower(trim($_POST["email"] ?? '')));
      $phone = trim($_POST["phone"] ?? '');
      $pass = trim($_POST["password"] ?? '');
      $conPass = trim($_POST["con-password"] ?? '');
      $type = $_POST["type"];
      $country = trim($_POST["signUp"]["country"] ?? '');
      $photo = empty($_FILES['image']['name']) ? false : true;

      $extraEmails = $_POST["extra_emails"] ?? [];
      $extraPhones = $_POST["extra_phones"] ?? [];

      if (empty($name) || empty($email) || empty($phone) || empty($pass) || empty($conPass) || empty($type) || empty($country)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        echo json_encode(["status" => "error", "message" => "Invalid email format."]);
        return;
      }

      if (!preg_match('/^\+\d{10,15}$/', $phone)) {
        echo json_encode(["status" => "error", "message" => "Invalid phone number."]);
        return;
      }

      if ($pass !== $conPass) {
        echo json_encode(["status" => "error", "message" => "Passwords do not match."]);
        return;
      }

      $checkUserSQL = "SELECT * FROM `accounts_user` WHERE `acc_email` = ?";
      $this->dbF->getRow($checkUserSQL, [$email]);

      if ($this->dbF->rowCount > 0) {
        echo json_encode(["status" => "error", "message" => "Email already exists."]);
        return;
      }

      $encodedPass = $this->functions->encode($pass);

      if ($photo) {
        $returnImage = $this->functions->uploadSingleImage($_FILES["image"], 'photos');
      } else {
        $returnImage = '';
      }

      $sqlInsert = "INSERT INTO `accounts_user` (`acc_name`, `acc_email`, `acc_pass`, `acc_type`, `status`, `acc_image`, `acc_phone`, `acc_created`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
      $lastId = $this->dbF->setRow($sqlInsert, [$name, $email, $encodedPass, $type, 1, $returnImage, $phone, date('Y-m-d H:i:s')]);

      $setting = empty($_POST['signUp']) ? [] : $_POST['signUp'];

      $sql = "INSERT INTO `accounts_user_detail` (`id_user`, `setting_name`, `setting_val`) VALUES ";
      $arry = [];

      foreach ($setting as $key => $value) {
        $key = trim(strip_tags($key));
        $sql .= "('$lastId',?,?) ,";
        $arry[] = $key;
        $arry[] = $value;
      }

      $sql = trim($sql, ",");
      $this->dbF->setRow($sql, $arry, false);

      if (is_array($extraEmails) && !empty($extraEmails)) {
        foreach ($extraEmails as $extraEmail) {
          $extraEmail = trim($extraEmail);
          if (filter_var($extraEmail, FILTER_VALIDATE_EMAIL)) {
            $checkEmailSql = "SELECT `id` FROM `user_emails` WHERE `email` = ?";
            $this->dbF->getRow($checkEmailSql, [$extraEmail]);
            if ($this->dbF->rowCount == 0) {
              $sqlEmail = "INSERT INTO `user_emails` (`user_id`, `email`) VALUES (?, ?)";
              $this->dbF->setRow($sqlEmail, [$lastId, $extraEmail]);
            }
          }
        }
      }

      if (is_array($extraPhones) && !empty($extraPhones)) {
        foreach ($extraPhones as $extraPhone) {
          $extraPhone = trim($extraPhone);
          if (preg_match('/^\+\d{10,15}$/', $extraPhone)) {
            $checkPhoneSql = "SELECT id FROM user_phones WHERE phone = ?";
            $this->dbF->getRow($checkPhoneSql, [$extraPhone]);
            if ($this->dbF->rowCount == 0) {
              $sqlPhone = "INSERT INTO `user_phones` (`user_id`, `phone`) VALUES (?, ?)";
              $this->dbF->setRow($sqlPhone, [$lastId, $extraPhone]);
            }
          }
        }
      }

      $redirect = match ($type) {
        "3" => "leads",
        "4" => "clients",
        default => "users",
      };

      if ($lastId > 0) {
        if ($type != "3") {
          $link = WEB_URL . '/login';
          $subject = "🎉 Welcome to Interactive Media - Your Account is Ready!";
          $logoUrl = WEB_URL . "/webImages/logo1.png";
          $message = "
        <html>
        <head>
          <style>
            body {
              font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
              background-color: #f4f4f4;
              padding: 20px;
              color: #333;
            }
            .email-container {
              max-width: 600px;
              margin: auto;
              background: #ffffff;
              padding: 30px;
              border-radius: 10px;
              box-shadow: 0 0 10px rgba(0,0,0,0.05);
            }
            .header {
              text-align: center;
              margin-bottom: 30px;
            }
            .header img {
              max-height: 60px;
            }
            h2 {
              color: #8cc63f;
            }
            .credentials {
              background-color: #f9f9f9;
              border: 1px solid #ddd;
              padding: 15px;
              border-radius: 5px;
            }
            .login-button {
              display: inline-block;
              margin-top: 20px;
              padding: 12px 20px;
              background-color: #8cc63f;
              color: #ffffff !important;
              text-decoration: none;
              border-radius: 5px;
              font-weight: bold;
            }
            .footer {
              margin-top: 40px;
              font-size: 12px;
              color: #999;
              text-align: center;
            }
          </style>
        </head>
        <body>
          <div class='email-container'>
            <div class='header'>
              <img src='{$logoUrl}' alt='Interactive Media Logo'>
            </div>
            <h2>Welcome to Interactive Media!</h2>
            <p>Dear {$name},</p>
            <p>Thank you for registering with <strong>Interactive Media</strong>. We're thrilled to have you on board!</p>

            <p>Here are your login credentials:</p>
            <div class='credentials'>
              <p><strong>Email:</strong> {$email}</p>
              <p><strong>Password:</strong> {$conPass}</p>
            </div>

            <p>Click below to log into your account and get started:</p>
            <p><a href='{$link}' class='login-button'>Login to Your Account</a></p>

            <p><small>We recommend changing your password after your first login to ensure account security.</small></p>

            <p>Warm regards,<br>
            <strong>Interactive Media Team</strong></p>

            <div class='footer'>
              &copy; " . date('Y') . " Interactive Media. All rights reserved.
            </div>
          </div>
        </body>
        </html>";
          // if ($this->functions->send_mail($email, $subject, $message)) {
          echo json_encode(["status" => "success", "message" => "Account created successfully! Redirecting...", "redirect" => $redirect]);
          $desc = "New user added: Name = {$name}, Email = {$email}, Type = {$type}, ID = {$lastId}";
          $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
          // }
        } else {
          echo json_encode(["status" => "success", "message" => "Account created successfully! Redirecting...", "redirect" => $redirect]);
          $desc = "New user added: Name = {$name}, Email = {$email}, Type = {$type}, ID = {$lastId}";
          $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
        }
      }
    }
  }
  

  public function editUser()
  {
    if (isset($_POST["userId"])) {

      $name = trim($_POST["name"] ?? '');
      $phone = trim($_POST["phone"] ?? '');
      $type = trim($_POST["type"] ?? '');
      $photo = empty($_FILES['image']['name']) ? false : true;

      $oldImg = empty($_POST['oldImg']) ? "" : $_POST['oldImg'];
      $imgName = $oldImg;

      $editId = $_POST["userId"];

      $extraEmails = $_POST["extra_emails"] ?? [];
      $extraPhones = $_POST["extra_phones"] ?? [];

      if (empty($name) || empty($phone)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      if (!preg_match('/^\+\d{10,15}$/', $phone)) {
        echo json_encode(["status" => "error", "message" => "Invalid phone number."]);
        return;
      }

      $checkUserSQL = "SELECT * FROM `accounts_user` WHERE `acc_id` = ?";
      $this->dbF->getRow($checkUserSQL, [$editId]);

      if ($this->dbF->rowCount > 0) {
        if ($photo) {
          $this->functions->deleteOldSingleImage($oldImg);
          $imgName = $this->functions->uploadSingleImage($_FILES["image"], 'photos');
          if ($imgName == false) {
            echo json_encode(["status" => "error", "message" => "Image File Error."]);
            return;
          }
        }
      }


      $sqlUpdate = "UPDATE `accounts_user` SET `acc_name` = ?, `acc_type` = ?, `acc_image` = ?, `acc_phone` = ? WHERE `acc_id` = ?";
      $this->dbF->setRow($sqlUpdate, [$name, $type, $imgName, $phone, $editId]);

      $lastId = $editId;
      $setting = empty($_POST['signUp']) ? [] : $_POST['signUp'];


      foreach ($setting as $key => $value) {
        $updateSql = "UPDATE `accounts_user_detail` SET `setting_val` = ? WHERE `id_user` = ? AND `setting_name` = ?";
        $updateRes = $this->dbF->setRow($updateSql, [$value, $lastId, $key]);

        if (!$updateRes) {
          $insertSql = "INSERT INTO `accounts_user_detail` (`id_user`, `setting_name`, `setting_val`) VALUES (?, ?, ?)";
          $this->dbF->setRow($insertSql, [$lastId, $key, $value]);
        }
      }

      $this->dbF->setRow("DELETE FROM user_emails WHERE user_id = '$lastId'");
      $this->dbF->setRow("DELETE FROM user_phones WHERE user_id = '$lastId'");

      if (is_array($extraEmails) && !empty($extraEmails)) {
        foreach ($extraEmails as $extraEmail) {
          $extraEmail = trim($extraEmail);
          if (filter_var($extraEmail, FILTER_VALIDATE_EMAIL)) {
            $sqlEmail = "INSERT INTO `user_emails` (`user_id`, `email`) VALUES (?, ?)";
            $this->dbF->setRow($sqlEmail, [$lastId, $extraEmail]);
          }
        }
      }

      if (is_array($extraPhones) && !empty($extraPhones)) {
        foreach ($extraPhones as $extraPhone) {
          $extraPhone = trim($extraPhone);
          if (preg_match('/^\+\d{10,15}$/', $extraPhone)) {
            $sqlPhone = "INSERT INTO `user_phones` (`user_id`, `phone`) VALUES (?, ?)";
            $this->dbF->setRow($sqlPhone, [$lastId, $extraPhone]);
          }
        }
      }

      $redirect = match ($type) {
        "3" => "leads",
        "4" => "clients",
        "5" => "index",
        default => "users",
      };

      $desc = "User updated: ID = {$editId}, Name = {$name}, Type = {$type}, Phone = {$phone}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      echo json_encode(["status" => "success", "message" => "User updated successfully! Redirecting...", "redirect" => $redirect]);
    }
  }

  public function addNewStaff()
  {
    if (isset($_POST["userId"]) && !empty($_POST["userId"])) {
      $userId = trim($_POST["userId"] ?? '');
      $designations = $_POST["designation"] ?? [];
      $checkInTime = trim($_POST["checkintime"] ?? '');
      $checkOutTime = trim($_POST["checkouttime"] ?? '');
      $salary = trim($_POST["salary"] ?? '');

      if (empty($userId) || empty($designations) || empty($checkInTime) || empty($checkOutTime)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      $checkStaffSQL = "SELECT * FROM `staffs` WHERE `userId` = ?";
      $this->dbF->getRow($checkStaffSQL, [$userId]);

      if ($this->dbF->rowCount > 0) {
        echo json_encode(["status" => "error", "message" => "Staff already exists."]);
        return;
      }

      $sql = "INSERT INTO `staffs` (`userId`, `checkintime`, `checkouttime`, `salary`) VALUES (?, ?, ?, ?)";
      $staffId = $this->dbF->setRow($sql, [$userId, $checkInTime, $checkOutTime, $salary]);

      foreach ($designations as $designationId) {
        $checkSQL = "SELECT * FROM `staff_designations` WHERE `staff_id` = ? AND `designation_id` = ?";
        $this->dbF->getRow($checkSQL, [$staffId, $designationId]);
        if ($this->dbF->rowCount == 0) {
          $insertSQL = "INSERT INTO `staff_designations` (`staff_id`, `designation_id`) VALUES (?, ?)";
          $this->dbF->setRow($insertSQL, [$staffId, $designationId]);
        }
      }
      echo json_encode(["status" => "success", "message" => "Staff added successfully."]);
      $desc = "New staff added: ";
      $staffName = $this->functions->webUserName($userId, 'acc_name');
      $desc .= "Name = {$staffName}, ID = {$userId}, Check-In = {$checkInTime}, Check-Out = {$checkOutTime}, Salary = {$salary}";

      if (!empty($designations)) {
        $designationNames = [];
        foreach ($designations as $designationId) {
          $designationSql = "SELECT `designation` FROM `designations` WHERE `id` = ?";
          $designationRow = $this->dbF->getRow($designationSql, [$designationId]);
          if ($this->dbF->rowCount > 0) {
            $designationNames[] = $designationRow['name'];
          }
        }
        if (!empty($designationNames)) {
          $desc .= ", Designations = [" . implode(", ", $designationNames) . "]";
        }
      }

      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editStaff()
  {
    if (isset($_POST["staffId"])) {
      $userId = trim($_POST["userId"] ?? '');
      $designations = $_POST["designation"] ?? [];
      $checkInTime = trim($_POST["checkintime"] ?? '');
      $checkOutTime = trim($_POST["checkouttime"] ?? '');
      $salary = trim($_POST["salary"] ?? '');

      if (empty($userId) || empty($designations) || empty($checkInTime) || empty($checkOutTime)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      $editId = $_POST["staffId"];

      $updateSql = "UPDATE `staffs` SET `userId` = ?, `checkintime` = ?, `checkouttime` = ?, `salary` = ? WHERE `id` = ?";
      $this->dbF->setRow($updateSql, [$userId, $checkInTime, $checkOutTime, $salary, $editId]);

      $deleteSql = "DELETE FROM `staff_designations` WHERE `staff_id` = '$editId'";
      $this->dbF->setRow($deleteSql);

      $insertSql = "INSERT INTO staff_designations (staff_id, designation_id) VALUES ";
      $insertParams = [];
      foreach ($designations as $desigId) {
        $insertSql .= "(?, ?),";
        $insertParams[] = $editId;
        $insertParams[] = $desigId;
      }
      $insertSql = rtrim($insertSql, ',');

      if (!empty($insertParams)) {
        $this->dbF->setRow($insertSql, $insertParams);
      }

      echo json_encode(["status" => "success", "message" => "Staff updated successfully! Redirecting...", "redirect" => "staffs"]);

      $desc = "Staff updated: ";
      $staffName = $this->functions->webUserName($userId, 'acc_name');
      $desc .= "Name = {$staffName}, ID = {$userId}, Check-In = {$checkInTime}, Check-Out = {$checkOutTime}, Salary = {$salary}";

      if (!empty($designations)) {
        $designationNames = [];
        foreach ($designations as $designationId) {
          $designationSql = "SELECT `designation` FROM `designations` WHERE `id` = ?";
          $designationRow = $this->dbF->getRow($designationSql, [$designationId]);
          if ($this->dbF->rowCount > 0) {
            $designationNames[] = $designationRow['name'];
          }
        }
        if (!empty($designationNames)) {
          $desc .= ", Designations = [" . implode(", ", $designationNames) . "]";
        }
      }

      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteStaff()
  {
    if (isset($_POST["deleteStaffId"])) {
      $staffId = $_POST["deleteStaffId"];

      $checkSql = "SELECT * FROM `staffs` WHERE `id` = ?";
      $staff = $this->dbF->getRow($checkSql, [$staffId]);

      if ($this->dbF->rowCount > 0) {
        try {
          $userId = $staff['userId'];
          $staffName = $this->functions->webUserName($userId, 'acc_name');
          $checkIn = $staff['checkintime'];
          $checkOut = $staff['checkouttime'];
          $salary = $staff['salary'];

          $designationsSql = "SELECT d.designation FROM staff_designations sd JOIN designations d ON sd.designation_id = d.id WHERE sd.staff_id = ?";
          $designationRows = $this->dbF->getRows($designationsSql, [$staffId]);
          $designationNames = array_column($designationRows, 'name');
          $designationText = !empty($designationNames) ? implode(', ', $designationNames) : 'None';

          $this->db->beginTransaction();

          $this->dbF->setRow("DELETE FROM staff_designations WHERE staff_id = '$staffId'");
          $this->dbF->setRow("DELETE FROM staffs WHERE id = '$staffId'");

          $this->db->commit();

          echo "1";

          $desc = "Staff deleted: Name = {$staffName}, ID = {$staffId}, Check-In = {$checkIn}, Check-Out = {$checkOut}, Salary = {$salary}, Designations = [{$designationText}]";
          $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
        } catch (Exception $e) {
          $this->db->rollBack();
          echo "0";
        }
      } else {
        echo "0";
      }
    } else {
      echo "0";
    }
  }

  public function addNewDesignation()
  {
    if (isset($_POST["designation"])) {
      $designation = trim($_POST["designation"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($designation)) {
        echo json_encode(["status" => "error", "message" => "Designation is required."]);
        return;
      }

      $sql = "INSERT INTO `designations` (`designation`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$designation, $publish]);

      echo json_encode(["status" => "success", "message" => "Designation added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Designation added: Name = {$designation}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editDesignation()
  {
    if (isset($_POST["designationId"])) {
      $designation = trim($_POST["designation"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($designation)) {
        echo json_encode(["status" => "error", "message" => "Designation is required."]);
        return;
      }
      $editId = $_POST["designationId"];

      $sql = "UPDATE `designations` SET `designation` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$designation, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Designation updated successfully! Redirecting...", "redirect" => "designations"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Designation updated: ID = {$editId}, Name = {$designation}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteDesignation()
  {
    if (isset($_POST["deleteDesignationId"])) {
      $designationId = $_POST["deleteDesignationId"];

      $sqlGet = "SELECT `designation` FROM `designations` WHERE `id` = ?";
      $designationData = $this->dbF->getRow($sqlGet, [$designationId]);
      $designationName = $designationData['designation'] ?? 'Unknown';

      $sql = "DELETE FROM `designations` WHERE `id` = '$designationId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Designation deleted: ID = {$designationId}, Name = {$designationName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function addNewProjectStatus()
  {
    if (isset($_POST["projectstatus"])) {
      $status = trim($_POST["projectstatus"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($status)) {
        echo json_encode(["status" => "error", "message" => "Status Title is required."]);
        return;
      }

      $sql = "INSERT INTO `project_statuses` (`name`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$status, $publish]);

      echo json_encode(["status" => "success", "message" => "Status added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Project Status added: Name = {$status}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editProjectStatus()
  {
    if (isset($_POST["projectStatusId"])) {
      $status = trim($_POST["projectstatus"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($status)) {
        echo json_encode(["status" => "error", "message" => "Status Title is required."]);
        return;
      }

      $editId = $_POST["projectStatusId"];

      $sql = "UPDATE `project_statuses` SET `name` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$status, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Status updated successfully! Redirecting...", "redirect" => "projectstatus"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Project Status updated: ID = {$editId}, Name = {$status}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteProjectStatus()
  {
    if (isset($_POST["deleteProjectStatusId"])) {
      $projectStatusId = $_POST["deleteProjectStatusId"];

      $sqlGet = "SELECT `name` FROM `project_statuses` WHERE `id` = ?";
      $statusData = $this->dbF->getRow($sqlGet, [$projectStatusId]);
      $statusName = $statusData['name'] ?? 'Unknown';

      $sql = "DELETE FROM `project_statuses` WHERE `id` = '$projectStatusId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Project Status deleted: ID = {$projectStatusId}, Name = {$statusName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function addNewProjectType()
  {
    if (isset($_POST["projecttype"])) {
      $type = trim($_POST["projecttype"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($type)) {
        echo json_encode(["status" => "error", "message" => "Type Title is required."]);
        return;
      }

      $sql = "INSERT INTO `project_types` (`name`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$type, $publish]);

      echo json_encode(["status" => "success", "message" => "Type added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Project Type added: Name = {$type}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editProjectType()
  {
    if (isset($_POST["projectTypeId"])) {
      $type = trim($_POST["projecttype"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      $editId = $_POST["projectTypeId"];

      if (empty($type)) {
        echo json_encode(["status" => "error", "message" => "Type Title is required."]);
        return;
      }

      $sql = "UPDATE `project_types` SET `name` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$type, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Project Type updated successfully! Redirecting...", "redirect" => "projecttype"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Project Type updated: ID = {$editId}, Name = {$type}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteProjectType()
  {
    if (isset($_POST["deleteProjectTypeId"])) {
      $projectTypeId = $_POST["deleteProjectTypeId"];

      $sqlGet = "SELECT `name` FROM `project_types` WHERE `id` = ?";
      $typeData = $this->dbF->getRow($sqlGet, [$projectTypeId]);
      $typeName = $typeData['name'] ?? 'Unknown';

      $sql = "DELETE FROM `project_types` WHERE `id` = '$projectTypeId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;

        $desc = "Project Type deleted: ID = {$projectTypeId}, Name = {$typeName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function addNewProjectCategory()
  {
    if (isset($_POST["projectcategories"])) {
      $category = trim($_POST["projectcategories"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($category)) {
        echo json_encode(["status" => "error", "message" => "Category Title is required."]);
        return;
      }

      $sql = "INSERT INTO `project_categories` (`category`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$category, $publish]);

      echo json_encode(["status" => "success", "message" => "Category added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Project Category added: Name = {$category}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editProjectCategory()
  {
    if (isset($_POST["projectCategoryId"])) {
      $category = trim($_POST["projectcategories"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($category)) {
        echo json_encode(["status" => "error", "message" => "Category Title is required."]);
        return;
      }
      $editId = $_POST["projectCategoryId"];

      $sql = "UPDATE `project_categories` SET `category` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$category, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Category updated successfully! Redirecting...", "redirect" => "projectcategories"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Project Category updated: ID = {$editId}, Name = {$category}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteProjectCategory()
  {
    if (isset($_POST["deleteProjectCategoryId"])) {
      $projectCategoryId = $_POST["deleteProjectCategoryId"];

      $sqlGet = "SELECT `category` FROM `project_categories` WHERE `id` = ?";
      $catData = $this->dbF->getRow($sqlGet, [$projectCategoryId]);
      $catName = $catData['category'] ?? 'Unknown';

      $sql = "DELETE FROM `project_categories` WHERE `id` = '$projectCategoryId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Project Category deleted: ID = {$projectCategoryId}, Name = {$catName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function checkInCheckOut()
  {
    if (isset($_POST["action"]) && !empty($_POST["action"])) {
      $action = $_POST['action'] ?? '';
      $emp_id = $_POST['emp_id'] ?? '';
      $date = $_POST['date'] ?? date('Y-m-d');
      $time = new DateTime();
      $talkId = $this->getEmployeeTalkId($emp_id);
      if ($action == 'checkin') {
        $sqlCheck = "SELECT 1 FROM daily_reports WHERE emp_id = ? AND date = ?";
        $this->dbF->getRow($sqlCheck, [$emp_id, $date]);

        if ($this->dbF->rowCount > 0) {
          return;
        }

        $time->modify('-10 minutes');
        $timeNow = $time->format('H:i:s');

        $sql = "INSERT INTO daily_reports (`emp_id`, `date`, `in_time`, `status`) VALUES (?, ?, ?, ?)";
        $this->dbF->setRow($sql, [$emp_id, $date, $timeNow, 'Present']);

        $this->sendDataToRemote($action, $talkId, $timeNow, $date);
        echo 'checked-in';
      }

      if ($action == 'checkout') {
        $time->modify('+5 minutes');
        $timeNow = $time->format('H:i:s');
        $msg = $_POST['msg'] ?? '';

        $sqlCheck = "SELECT * FROM `daily_reports` WHERE `emp_id` = ? AND `date` = ?";
        $this->dbF->getRow($sqlCheck, [$emp_id, $date]);
        if ($this->dbF->rowCount > 0) {
          $sql = "UPDATE daily_reports SET out_time = ?, msg = ? WHERE emp_id = ? AND `date` = ?";
          $this->dbF->setRow($sql, [$timeNow, $msg, $emp_id, $date]);
        } else {
          $sql = "INSERT INTO daily_reports (`emp_id`, `date`, `in_time`, `out_time`, `status`) VALUES (?, ?, ?, ?, ?)";
          $this->dbF->setRow($sql, [$emp_id, $date, '14:00:00', $timeNow, 'Present']);
        }

        echo 'checked-out';

        $this->sendDataToRemote($action, $talkId, $timeNow, $date);
      }
    }
  }

  private function getEmployeeTalkId($userId)
  {
    $sql = "SELECT talk_id FROM `accounts_user` WHERE `acc_id` = ?";
    $userData = $this->dbF->getRow($sql, [$userId]);

    if ($this->dbF->rowCount > 0) {
      return $userData["talk_id"];
    }
    return null;
  }

  private function sendDataToRemote($action, $emp_id, $timeNow, $date)
  {
    $url = "https://talk.imediahostings.com/datafromcrm.php";
    $postData = [
      'action' => $action,
      'emp_id' => $emp_id,
      'timeNow' => $timeNow,
      'date' => $date
    ];

    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($postData));
    curl_setopt($ch, CURLOPT_POST, true);

    $response = curl_exec($ch);
    curl_close($ch);
  }

  public function getStaffReport()
  {
    if (isset($_POST["userId"]) && isset($_POST["fromdate"]) && isset($_POST["todate"])) {
      $userId = $_POST["userId"];
      $fromDate = $_POST["fromdate"];
      $toDate = $_POST["todate"];

      if ($userId === "All") {
        $staffList = $this->productClass->getStaffArray();
        $allReports = [];

        foreach ($staffList as $staff) {
          $_POST["userId"] = $staff['acc_id'];
          ob_start();
          $this->getStaffReport();
          $result = json_decode(ob_get_clean(), true);
          $allReports[] = $result;
        }

        echo json_encode(['allStaffReports' => $allReports]);
        return;
      }

      $report = [];
      $absents = 0;
      $lates = 0;
      $presents = 0;
      $totalWorkMinutes = 0;
      $missingOutTime = 0;
      $totalWorkingHours = 0;
      $totalWorkingDays = 0;

      $sql = "SELECT * FROM daily_reports WHERE `emp_id` = ? AND `date` BETWEEN ? AND ?";
      $attendanceData = $this->dbF->getRows($sql, [$userId, $fromDate, $toDate]);
      $empName = $this->functions->webUserName($userId, 'acc_name');
      $records = [];
      foreach ($attendanceData as $row) {
        $records[$row['date']] = $row;
      }

      $period = new DatePeriod(
        new DateTime($fromDate),
        new DateInterval('P1D'),
        (new DateTime($toDate))->modify('+1 day')
      );

      foreach ($period as $date) {
        $currDate = $date->format("Y-m-d");
        $dayName = $date->format("D");
        $inTime = $outTime = $status = '';
        $message = '';
        $workMinutes = 0;
        if ($dayName === 'Sun') {
          continue;
        }
        if ($dayName === 'Sat' && $this->isSaturdayOff($currDate)) {
          continue;
        }
        if ($dayName === 'Sat') {
          $totalWorkingHours += 6;
        } else {
          $totalWorkingHours += 9;
        }
        $totalWorkingDays++;
        if (isset($records[$currDate])) {
          $inTime = $records[$currDate]['in_time'];
          $outTime = $records[$currDate]['out_time'];
          $message = $records[$currDate]['msg'] ?? '';
          if (empty($outTime)) {
            $status = "**Out Time Missing!! | Half Day Leave";
            $missingOutTime++;
          } else {
            $workMinutes = (strtotime($currDate . ' ' . $outTime) - strtotime($currDate . ' ' . $inTime)) / 60;
            $workMinutes = round($workMinutes);
            $totalWorkMinutes += $workMinutes;

            $hours = floor($workMinutes / 60);
            $minutes = $workMinutes % 60;
            $formattedDuration = sprintf('%02d:%02d', $hours, $minutes);

            $targetMinutes = ($dayName === 'Sat') ? 360 : 540;
            $diff = $workMinutes - $targetMinutes;

            if ($diff < 0) {
              $status = sprintf('%s hrs. | %02d minute(s) Less time', $formattedDuration, abs($diff));
            } elseif ($diff > 0) {
              $status = sprintf('%s hrs. | %02d minute(s) Extra time', $formattedDuration, $diff);
            } else {
              $status = sprintf('%s hrs.', $formattedDuration);
            }

            $presents++;

            $inTimeCheck = strtotime($inTime);
            $expectedInTime = strtotime('11:00:00');
            if ($inTimeCheck > $expectedInTime) {
              $lates++;
            }
          }
        } else {
          $status = "**ABSENT!";
          $absents++;
        }

        $inTime = $inTime ? date('h:i A', strtotime($inTime)) : '';
        $outTime = $outTime ? date('h:i A', strtotime($outTime)) : '';

        $report[] = [
          'date' => $currDate,
          'day' => $dayName,
          'inTime' => $inTime,
          'outTime' => $outTime,
          'status' => $status,
          'minutes' => $workMinutes,
          'message' => $message,
        ];
      }

      $totalHours = floor($totalWorkMinutes / 60);
      $remainingMinutes = $totalWorkMinutes % 60;

      echo json_encode([
        'report' => $report,
        'summary' => [
          'absents' => $absents,
          'lates' => $lates,
          'presents' => $presents,
          'workHours' => "{$totalHours}:{$remainingMinutes}",
          'name' => $empName,
          'missingOutTime' => $missingOutTime,
          'totalWorkingHours' => $totalWorkingHours,
          'totalWorkingDays' => $totalWorkingDays
        ]
      ]);
    }
  }

  private function isSaturdayOff($date)
  {
    $sql = "SELECT COUNT(*) AS `cnt` FROM `saturday_off` WHERE `date` = ?";
    $res = $this->dbF->getRow($sql, [$date]);
    return $res['cnt'] > 0;
  }
  public function getStaffSalary()
  {
    if (isset($_POST["userId"])) {
      $userId = $_POST["userId"];

      $sql = "SELECT `salary` FROM `staffs` WHERE `userId` = ?";
      $salaryData = $this->dbF->getRow($sql, [$userId]);

      if ($this->dbF->rowCount > 0) {
        echo $salaryData["salary"];
      } else {
        echo 0;
      }
    }
  }

  public function addNewTicket()
  {
    if (isset($_POST["clientId"]) && isset($_POST["status"]) && isset($_POST["priority"])) {
      $clientId = $_POST["clientId"];
      $projectId = $_POST["projectId"];
      $status = $_POST["status"];
      $priority = $_POST["priority"];
      $category = $_POST["category"];
      $deadline = $_POST["deadline"];
      $assignto = trim($_POST["assignto"] ?? 0);
      $complaint = trim($_POST["complaint"] ?? '');
      $description = trim($_POST["description"] ?? '');

      if (empty($clientId) || empty($projectId) || empty($status) || empty($priority) || empty($category) || empty($deadline)) {
        echo json_encode(["status" => "error", "message" => "Please Fill/Select all required fields."]);
        return;
      }

      $sql = "INSERT INTO `tickets` (`client_id`, `project_id`, `status`, `priority`, `category`, `deadline`, `assigned_to`, `complaint`, `description`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
      $ticketId = $this->dbF->setRow($sql, [$clientId, $projectId, $status, $priority, $category, $deadline, $assignto, $complaint, $description]);

      $shortComplaint = substr($complaint, 0, 50) . (strlen($complaint) > 50 ? '...' : '');
      $assigneeName = $assignto ? $this->functions->webUserName($assignto, 'acc_name') : 'Unassigned';
      $desc = "Ticket added: Category - $category, Priority - $priority, Assigned To - $assigneeName, Complaint - \"$shortComplaint\"";

      if (isset($assignto) && !empty($assignto)) {
        $ticketLink = "ticketedit?id=$ticketId";
        $notifMsg = "You have been assigned a new ticket: $shortComplaint";
        $this->addNotification($assignto, $notifMsg, $ticketLink);

        $employeeData = $this->productClass->getUserById($assignto);
        $to = $employeeData["acc_email"];
        $subject = "New Ticket - $category - $deadline";
        if ($this->functions->send_mail($to, $subject, $description)) {
          echo json_encode(["status" => "success", "message" => "Ticket added successfully. Email has been sent to $employeeData[acc_name]"]);
          $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
        }
      } else {
        echo json_encode(["status" => "success", "message" => "Ticket added successfully."]);
        $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      }
    }
  }

  public function editTicket()
  {
    if (isset($_POST["ticketId"])) {
      $ticketId = $_POST["ticketId"];

      $status = $_POST["status"];
      $priority = $_POST["priority"];
      $category = $_POST["category"];
      $deadline = $_POST["deadline"];
      $assignto = trim($_POST["assignto"] ?? 0);
      $complaint = trim($_POST["complaint"] ?? '');
      $description = trim($_POST["description"] ?? '');

      $old = $this->dbF->getRow("SELECT status FROM tickets WHERE id=?", [$ticketId]);

      $sql = "UPDATE `tickets` SET `status` = ?, `priority` = ?, `category` = ?, `deadline` = ?, `assigned_to` = ?, `complaint` = ?, `description` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$status, $priority, $category, $deadline, $assignto, $complaint, $description, $ticketId]);

      $shortComplaint = substr($complaint, 0, 50) . (strlen($complaint) > 50 ? '...' : '');
      $assigneeName = $assignto ? $this->functions->webUserName($assignto, 'acc_name') : 'Unassigned';
      $desc = "Ticket updated: ID - $ticketId, Status - $status, Priority - $priority, Category - $category, Assigned To - $assigneeName, Complaint - \"$shortComplaint\"";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);

      if ($status == "Open") {
        $redirect = "opentickets";
        $title = "View/Edit Open Ticket";
      } else {
        $redirect = "closedtickets";
        $title = "View/Edit Closed Ticket";
      }

      echo json_encode(["status" => "success", "message" => "Ticket updated successfully! Redirecting...", "redirect" => $redirect, "title" => $title]);
    }
  }

  public function addNewService()
  {
    if (isset($_POST["service"])) {
      $service = trim($_POST["service"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($service)) {
        echo json_encode(["status" => "error", "message" => "Service title is required."]);
        return;
      }

      $sql = "INSERT INTO `services` (`name`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$service, $publish]);

      echo json_encode(["status" => "success", "message" => "Service added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Inquiry Service added: Name = {$service}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editService()
  {
    if (isset($_POST["serviceId"])) {
      $service = trim($_POST["service"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($service)) {
        echo json_encode(["status" => "error", "message" => "Service title is required."]);
        return;
      }
      $editId = $_POST["serviceId"];

      $sql = "UPDATE `services` SET `name` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$service, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Service updated successfully! Redirecting...", "redirect" => "services"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Inquiry Service updated: ID = {$editId}, Name = {$service}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteService()
  {
    if (isset($_POST["deleteServiceId"])) {
      $serviceId = $_POST["deleteServiceId"];

      $sqlGet = "SELECT `name` FROM `services` WHERE `id` = ?";
      $serviceData = $this->dbF->getRow($sqlGet, [$serviceId]);
      $serviceName = $serviceData['name'] ?? 'Unknown';

      $sql = "DELETE FROM `services` WHERE `id` = '$serviceId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Inquiry Service deleted: ID = {$serviceId}, Name = {$serviceName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function addNewInquiry()
  {
    if (isset($_POST["name"]) && isset($_POST["company"]) && isset($_POST["email"])) {
      $name = trim($_POST["name"] ?? '');
      $company = trim($_POST["company"] ?? '');
      $email = trim($_POST["email"] ?? '');
      $phone = trim($_POST["phone"] ?? '');
      $message = trim($_POST["message"] ?? '');
      $services = $_POST["services"] ?? [];

      $requiredFields = ['name' => $name, 'email' => $email, 'phone' => $phone, 'services' => $services];
      foreach ($requiredFields as $field => $value) {
        if (empty($value)) {
          echo json_encode(["status" => "error", "message" => "Please fill all required fields."]);
          return;
        }
      }

      if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        echo json_encode(["status" => "error", "message" => "Invalid email format."]);
        return;
      }

      if (!preg_match('/^\+\d{10,15}$/', $phone)) {
        echo json_encode(["status" => "error", "message" => "Invalid phone number."]);
        return;
      }

      $servicesStr = implode(", ", $services);
      $message = htmlspecialchars($message, ENT_QUOTES, 'UTF-8');

      $webUserType = $this->webClass->webUserType();
      $userId = ($webUserType === 4) ? $this->webClass->webUserId() : 0;

      $sql = "INSERT INTO `inquiries` (`user_id`, `name`, `company_name`, `email`, `phone`, `services`, `message`) VALUES (?, ?, ?, ?, ?, ?, ?)";
      $res = $this->dbF->setRow($sql, [$userId, $name, $company, $email, $phone, $servicesStr, $message]);

      if (!$res) {
        echo json_encode(["status" => "error", "message" => "Failed to add inquiry."]);
        return;
      }

      $shortMsg = substr($message, 0, 50) . (strlen($message) > 50 ? '...' : '');
      $desc = "Inquiry added: Name - $name, Email - $email, Phone - $phone, Services - [$servicesStr], Message - \"$shortMsg\"";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);

      echo json_encode(["status" => "success", "message" => "Inquiry added successfully."]);
    }
  }

  public function addNewDailyExpense()
  {
    if (isset($_POST["title"]) && isset($_POST["paidto"]) && isset($_POST["amount"])) {
      $title = trim($_POST["title"] ?? '');
      $categoryId = trim($_POST["category"] ?? '');
      $paidto = trim($_POST["paidto"] ?? '');
      $amount = trim($_POST["amount"] ?? '');
      $date = trim($_POST["date"] ?? '');

      if (empty($title) || empty($categoryId) || empty($paidto) || empty($amount) || empty($date)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      $sql = "INSERT INTO `daily_expenses` (`title`, `category`, `paid_to`, `amount`, `date`) VALUES (?, ?, ?, ?, ?)";
      $res = $this->dbF->setRow($sql, [$title, $categoryId, $paidto, $amount, $date]);

      if (!$res) {
        echo json_encode(["status" => "error", "message" => "Failed to add expense."]);
        return;
      }

      $categoryName = $this->dbF->getRow("SELECT `category` FROM `expense_categories` WHERE `id` = ?", [$categoryId]);
      $categoryText = $categoryName ? $categoryName['category'] : "ID: $categoryId";

      $desc = "Daily Expense Added: Title - $title, Category - $categoryText, Paid To - $paidto, Amount - $amount, Date - $date";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);

      echo json_encode(["status" => "success", "message" => "Expense added successfully."]);
    }
  }

  public function editExpense()
  {
    if (isset($_POST["expenseId"])) {
      $title = trim($_POST["title"] ?? '');
      $categoryId = trim($_POST["category"] ?? '');
      $paidto = trim($_POST["paidto"] ?? '');
      $amount = trim($_POST["amount"] ?? '');
      $date = trim($_POST["date"] ?? '');

      $editId = $_POST["expenseId"];

      if (empty($title) || empty($categoryId) || empty($paidto) || empty($amount) || empty($date)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      $sql = "UPDATE `daily_expenses` SET `title` = ?, `category` = ?, `paid_to` = ?, `amount` = ?, `date` = ? WHERE `id` = ?";
      $res = $this->dbF->setRow($sql, [$title, $categoryId, $paidto, $amount, $date, $editId]);

      if (!$res) {
        echo json_encode(["status" => "error", "message" => "Failed to update expense."]);
        return;
      }

      $categoryName = $this->dbF->getRow("SELECT `category` FROM `expense_categories` WHERE `id` = ?", [$categoryId]);
      $categoryText = $categoryName ? $categoryName['category'] : "ID: $categoryId";

      $desc = "Daily Expense Updated: ID - $editId, Title - $title, Category - $categoryText, Paid To - $paidto, Amount - $amount, Date - $date";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);

      echo json_encode(["status" => "success", "message" => "Expense updated successfully! Redirecting...", "redirect" => "expense"]);
    }
  }

  public function deleteDailyExpense()
  {
    if (isset($_POST["deleteExpenseId"])) {
      $expenseId = $_POST["deleteExpenseId"];

      $sqlGet = "SELECT `title` FROM `daily_expenses` WHERE `id` = ?";
      $expenseData = $this->dbF->getRow($sqlGet, [$expenseId]);
      $expenseTitle = $expenseData['title'] ?? 'Unknown';

      $sql = "DELETE FROM `daily_expenses` WHERE `id` = '$expenseId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Daily Expense deleted: ID = {$expenseId}, Title = {$expenseTitle}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function addNote()
  {
    if (isset($_POST["target_id"]) && isset($_POST["note_text"]) && isset($_POST["target_type"])) {
      $targetType = trim($_POST['target_type'] ?? '');
      $targetId = trim($_POST['target_id'] ?? '');
      $noteText = trim($_POST['note_text'] ?? '');
      $noteTextEmail = trim($_POST['note_text_email'] ?? '');
      $userId = $this->webClass->webUserId();

      if ($targetType && $targetId && $noteText && $userId) {
        $sql = "INSERT INTO `notes` (`target_type`, `target_id`, `note_text`, `created_by`) VALUES (?, ?, ?, ?)";
        $this->dbF->setRow($sql, [$targetType, $targetId, $noteText, $userId]);

        if (!empty($noteTextEmail)) {
          $to = $this->functions->webUserName($targetId, 'acc_email');
          $this->functions->send_mail($to, "From Interactive Media", $noteTextEmail);
        }

        $desc = "Note added: Target Type - $targetType, Target ID - $targetId, Note - " . substr($noteText, 0, 50) . (strlen($noteText) > 50 ? "..." : "");
        $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);

        echo json_encode(['status' => "success", "message" => "Note added successfully!"]);
      } else {
        echo json_encode(["status" => "error", "message" => "Missing or invalid data"]);
      }
    }
  }

  public function getNotes()
  {
    $targetType = $_POST['target_type'];
    $targetId = $_POST['target_id'];

    $sql = "SELECT note_text, created_by, created_at FROM notes WHERE target_type = ? AND target_id = ? ORDER BY created_at DESC";
    $data = $this->dbF->getRows($sql, [$targetType, $targetId]);

    if ($targetType == "client" || $targetType == "lead") {
      $userData = $this->productClass->getUserById($targetId);
      $userDetail = $this->productClass->getUserDetailsById($targetId);
      $address = $this->webClass->webUserInfoArray($userDetail, 'country');

      $sqlClientTickets = "SELECT * FROM `tickets` WHERE `client_id` = ? ORDER BY `id` ASC";
      $tickets = $this->dbF->getRows($sqlClientTickets, [$targetId]);
      $ticketDataRowCount = $this->dbF->rowCount;

      echo "<div class='row'>";
      echo "<div class='col-4'>";

      echo "<div class='d-flex bg-grd-success justify-content-between align-items-center p-2'>";
      echo "<h4>Client Details</h4>";
      echo "<a href='clientedit?id=$targetId' class='btn btn-outline-dark d-flex'><i class='material-icons-outlined'>edit_note</i></a>";
      echo "</div>";

      echo "<div class='d-flex justify-content-between align-items-center'>";
      echo "<h6>Client Name</h6>";
      echo "<p>$userData[acc_name]</p>";
      echo "</div>";

      echo "<div class='d-flex justify-content-between align-items-center'>";
      echo "<h6>Address</h6>";
      echo "<p>$address</p>";
      echo "</div>";

      echo "<div class='d-flex justify-content-between align-items-center'>";
      echo "<h6>Phone</h6>";
      echo "<p>$userData[acc_phone]</p>";
      echo "</div>";

      echo "<div class='d-flex justify-content-between align-items-center'>";
      echo "<h6>Email</h6>";
      echo "<p>$userData[acc_email]</p>";
      echo "</div>";

      echo "<div class='d-flex justify-content-between align-items-center'>";
      echo "<h6>Date of Sign up</h6>";
      echo "<p>$userData[acc_created]</p>";
      echo "</div>";

      echo "</div>";

      echo "<div class='col-4'>";
      echo "<div class='bg-grd-success p-2'>";
      echo "<h4>Tickets</h4>";
      echo "</div>";

      if ($ticketDataRowCount > 0) {
        foreach ($tickets as $ticket) {
          $priorityBadge = '';
          if ($ticket["priority"] == "Normal") {
            $priorityBadge = "<span class='badge bg-success'>$ticket[priority]</span>";
          } elseif ($ticket["priority"] == "Urgent") {
            $priorityBadge = "<span class='badge bg-danger'>$ticket[priority]</span>";
          }

          echo "<div class='d-flex justify-content-between align-items-center p-2'>";
          echo "<p class='text-primary border-right'>$ticket[id]</p>
                                  $priorityBadge
                </div>
                <div class='bg-secondary p-2'>
                    <h5>Deadline: $ticket[deadline]</h5>
                    <p>$ticket[complaint]</p>
                </div>";
        }
      }

      echo "</div>";

      echo "<div class='col-4'>";
      echo "<ul class='nav nav-tabs nav-success' role='tablist'>";

      echo "<li class='nav-item' role='presentation'>";
      echo "<a class='nav-link active' data-bs-toggle='tab' href='#notes' role='tab' aria-selected='true'>";
      echo "<div class='tab-title'>Notes</div>";
      echo "</a>";
      echo "</li>";

      echo "<li class='nav-item' role='presentation'>";
      echo "<a class='nav-link' data-bs-toggle='tab' href='#tasks' role='tab' aria-selected='false'>";
      echo "<div class='tab-title'>Tasks</div>";
      echo "</a>";
      echo "</li>";

      echo "</ul>";

      echo "<div class='tab-content py-3'>";
      echo "<div class='tab-pane fade show active' id='notes' role='tabpanel'>";
      echo "<div class='p-2'>";

      echo "<div class='text-end my-2'>";
      echo "<button class='btn btn-sm btn-success' onclick=\"addNote(this, 'client');\">Add Note</button>";
      echo "</div>";

      if (empty($data)) {
        echo "<div class='text-muted'>No notes available.</div>";
      } else {
        echo "<ul class='list-group'>";
        foreach ($data as $note) {
          $name = $this->functions->webUserName($note['created_by'], 'acc_name');
          $text = htmlentities($note['note_text']);
          $date = date('d-M-Y h:i A', strtotime($note['created_at']));
          echo "<li class='list-group-item'>
                                <p class='mb-1'>$text</p>
                                <div class='text-end'>
                                  <small class='text-muted'>By: $name on $date</small>
                                </div>
                            </li>";
        }
        echo "</ul>";
      }
      echo "</div>";
      echo "</div>";

      echo "<div class='tab-pane fade' id='tasks' role='tabpanel'>";
      echo "<div class='py-2'>";

      echo "<div class='text-end my-2'>";
      echo "<a class='btn btn-sm btn-success' href='addevent'>Add Events/Tasks/Reminders</a>";
      echo "</div>";

      echo "<div class='bg-grd-success p-2'>Events</div>";
      echo "<div class='border-2 border text-center mb-3'>";
      $sql = "SELECT * FROM `events` WHERE `category` = ? AND `client_id` = ?";
      $events = $this->dbF->getRows($sql, ['Event', $targetId]);
      if ($this->dbF->rowCount > 0) {
        foreach ($events as $event) {
        }
      } else {
        echo "<div class='text-muted'>No Event.</div>";
      }
      echo "</div>";

      echo "<div class='bg-grd-success p-2'>Tasks</div>";
      echo "<div class='border-2 border text-center mb-3'>";
      $sql = "SELECT * FROM `events` WHERE `category` = ? AND `client_id` = ?";
      $tasks = $this->dbF->getRows($sql, ['Task', $targetId]);
      if ($this->dbF->rowCount > 0) {
        foreach ($tasks as $task) {
        }
      } else {
        echo "<div class='text-muted'>No Task.</div>";
      }
      echo "</div>";

      echo "<div class='bg-grd-success p-2'>Reminders</div>";
      echo "<div class='border-2 border text-center mb-3'>";
      $sql = "SELECT * FROM `events` WHERE `category` = ? AND `client_id` = ?";
      $reminders = $this->dbF->getRows($sql, ['Reminder', $targetId]);
      if ($this->dbF->rowCount > 0) {
        foreach ($reminders as $reminder) {
        }
      } else {
        echo "<div class='text-muted'>No Reminder.</div>";
      }
      echo "</div>";

      echo "</div>";
      echo "</div>";
      echo "</div>";
      echo "</div>";
      echo "</div>";
    } else {
      if (empty($data)) {
        echo "<div class='text-muted'>No notes available.</div>";
      } else {
        echo "<ul class='list-group'>";
        foreach ($data as $note) {
          $name = $this->functions->webUserName($note['created_by'], 'acc_name');
          $text = htmlentities($note['note_text']);
          $date = date('d-M-Y h:i A', strtotime($note['created_at']));
          echo "<li class='list-group-item'>
                      <p class='mb-1'>$text</p>
                      <div class='text-end'>
                        <small class='text-muted'>By: $name on $date</small>
                      </div>
                  </li>";
        }
        echo "</ul>";
      }
    }
  }

  public function addNewTicketCategory()
  {
    if (isset($_POST["ticketcategory"])) {
      $category = trim($_POST["ticketcategory"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($category)) {
        echo json_encode(["status" => "error", "message" => "Category Title is required."]);
        return;
      }

      $sql = "INSERT INTO `ticket_categories` (`category`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$category, $publish]);

      echo json_encode(["status" => "success", "message" => "Category added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Ticket Category added: Name = {$category}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editTicketCategory()
  {
    if (isset($_POST["ticketCategoryId"])) {
      $category = trim($_POST["ticketcategory"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($category)) {
        echo json_encode(["status" => "error", "message" => "Category Title is required."]);
        return;
      }
      $editId = $_POST["ticketCategoryId"];

      $sql = "UPDATE `ticket_categories` SET `category` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$category, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Category updated successfully! Redirecting...", "redirect" => "ticketcategories"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Ticket Cateogry updated: ID = {$editId}, Name = {$category}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteTicketCategory()
  {
    if (isset($_POST["deleteTicketCategoryId"])) {
      $ticketCategoryId = $_POST["deleteTicketCategoryId"];

      $sqlGet = "SELECT `category` FROM `ticket_categories` WHERE `id` = ?";
      $catData = $this->dbF->getRow($sqlGet, [$ticketCategoryId]);
      $catName = $catData['category'] ?? 'Unknown';

      $sql = "DELETE FROM `ticket_categories` WHERE `id` = '$ticketCategoryId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Ticket Category deleted: ID = {$ticketCategoryId}, Name = {$catName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function forgotPassword()
  {
    if (isset($_POST["email"])) {
      $email = trim($_POST["email"]);

      $sql = "SELECT * FROM `accounts_user` WHERE `acc_email` = ?";
      $this->dbF->getRow($sql, [$email]);

      if ($this->dbF->rowCount > 0) {
        $resetCode = rand(1000, 9999);
        $expiryTime = date("Y-m-d H:i:s", strtotime("+15 minutes"));

        $sqlCode = "UPDATE `accounts_user` SET `acc_code` = ?, `acc_code_expiry` = ? WHERE `acc_email` = ?";
        $this->dbF->setRow($sqlCode, [$resetCode, $expiryTime, $email]);

        $subject = "Password Reset Code";
        $body = "<p>Your password reset code is <strong>$resetCode</strong>. It expires in 15 minutes.</p>";
        $sent = $this->functions->send_mail($email, $subject, $body);

        $encodedEmail = $this->functions->encode($email);

        if ($sent) {
          echo json_encode(["status" => "success", "message" => "Reset code sent! Check your email! Redirecting...", "redirect" => "verify_code?email=$encodedEmail"]);
        } else {
          echo json_encode(["status" => "error", "message" => "Failed to send email. Try again."]);
        }
      } else {
        echo json_encode(["status" => "error", "message" => "Email not found."]);
      }
    }
  }

  public function verifyCode()
  {
    if (isset($_POST["verificationCode"]) && isset($_POST["email"])) {
      $userVerificationCode = $_POST["verificationCode"];
      $email = $_POST["email"];

      if (empty($userVerificationCode)) {
        echo json_encode(["status" => "error", "message" => "Verification code is required."]);
        return;
      }

      $sql = "SELECT `acc_code_expiry` FROM `accounts_user` WHERE `acc_code` = ? AND `acc_email` = ?";
      $data = $this->dbF->getRow($sql, [$userVerificationCode, $email]);

      if ($this->dbF->rowCount == 0) {
        echo json_encode(["status" => "error", "message" => "Incorrect code."]);
        return;
      }

      $currentTime = date("Y-m-d H:i:s");

      if ($currentTime > $data["acc_code_expiry"]) {
        echo json_encode(["status" => "error", "message" => "The code has expired. Please request a new one."]);
        return;
      }

      $encodedEmail = $this->functions->encode($email);
      echo json_encode(["status" => "success", "message" => "Code verified successfully! Redirecting...", "redirect" => "reset_password?email=$encodedEmail"]);
    }
  }

  public function changePassword()
  {
    if (isset($_POST["newPassword"]) && isset($_POST["conPassword"]) && isset($_POST["email"])) {
      $newPassword = trim($_POST["newPassword"]);
      $conPassword = trim($_POST["conPassword"]);
      $email = trim($_POST["email"]);

      if (empty($newPassword) || empty($conPassword)) {
        echo json_encode(["status" => "error", "message" => "Both password fields are required."]);
        return;
      }

      if ($newPassword !== $conPassword) {
        echo json_encode(["status" => "error", "message" => "Passwords do not match."]);
        return;
      }

      $sql = "SELECT * FROM `accounts_user` WHERE `acc_email` = ?";
      $this->dbF->getRow($sql, [$email]);

      if ($this->dbF->rowCount == 0) {
        echo json_encode(["status" => "error", "message" => "Invalid email address."]);
        return;
      }

      $encodedPass = $this->functions->encode($newPassword);

      $sqlUpdate = "UPDATE `accounts_user` SET `acc_pass` = ?, `acc_code` = NULL, `acc_code_expiry` = NULL WHERE `acc_email` = ?";
      $this->dbF->setRow($sqlUpdate, [$encodedPass, $email]);

      if ($this->dbF->rowCount > 0) {
        echo json_encode(["status" => "success", "message" => "Password updated successfully! Redirecting...", "redirect" => "login"]);
      } else {
        echo json_encode(["status" => "error", "message" => "Failed to update password. Try again."]);
      }
    }
  }

  public function updatePassword()
  {
    if (isset($_POST["newpassword"]) && isset($_POST["conpassword"]) && isset($_POST["curpassword"])) {
      $curpassword = trim($_POST["curpassword"]);
      $newpassword = trim($_POST["newpassword"]);
      $conpassword = trim($_POST["conpassword"]);
      $email = trim($_POST["email"]);

      if (empty($curpassword) || empty($newpassword) || empty($conpassword)) {
        echo json_encode(["status" => "error", "message" => "All fields are required."]);
        return;
      }

      if ($newpassword !== $conpassword) {
        echo json_encode(["status" => "error", "message" => "Passwords do not match."]);
        return;
      }

      $sql = "SELECT * FROM `accounts_user` WHERE `acc_email` = ?";
      $userData = $this->dbF->getRow($sql, [$email]);

      $currentPass = $userData["acc_pass"];
      $curpassword = $this->functions->encode($curpassword);

      if ($currentPass !== $curpassword) {
        echo json_encode(["status" => "error", "message" => "You entered wrong current password."]);
        return;
      }

      $newpasswordEncoded = $this->functions->encode($newpassword);

      $sqlUpdate = "UPDATE `accounts_user` SET `acc_pass` = ? WHERE `acc_email` = ?";
      $this->dbF->setRow($sqlUpdate, [$newpasswordEncoded, $email]);

      if ($this->dbF->rowCount > 0) {
        echo json_encode(["status" => "success", "message" => "Password updated successfully! Redirecting..."]);
      } else {
        echo json_encode(["status" => "error", "message" => "Failed to update password. Try again."]);
      }
    }
  }

  public function fetchChatUsers()
  {
    $sqlUsers = "SELECT au.* FROM staffs s JOIN accounts_user au ON au.acc_id = s.userId WHERE au.acc_id <> ? ORDER BY au.last_seen DESC";
    $chatUsers = $this->dbF->getRows($sqlUsers, [$this->webClass->webUserId()]);

    echo json_encode($chatUsers);
  }

  public function fetchMessages()
  {
    $sender = $_POST['sender'];
    $receiver = $_POST['receiver'];

    $sql = "SELECT cm.*, au.acc_name AS receiverName FROM chat_messages cm JOIN accounts_user au ON au.acc_id = cm.sender_id WHERE (cm.sender_id = ? AND cm.receiver_id = ?) OR (cm.sender_id = ? AND cm.receiver_id = ?) ORDER BY cm.created_at ASC";
    $data = $this->dbF->getRows($sql, [$sender, $receiver, $receiver, $sender]);
    echo json_encode($data);
  }

  public function sendMessage()
  {
    $sender = $_POST['sender'];
    $receiver = $_POST['receiver'];
    $message = $_POST['message'];

    $sql = "INSERT INTO `chat_messages` (`sender_id`, `receiver_id`, `message`) VALUES (?, ?, ?)";
    $this->dbF->setRow($sql, [$sender, $receiver, $message]);

    $sqlUpdate = "UPDATE `accounts_user` SET `last_message` = ? WHERE acc_id = ?";
    $this->dbF->setRow($sqlUpdate, [$message, $sender]);

    $sqlUpdate2 = "UPDATE `accounts_user` SET `last_message` = ? WHERE acc_id = ?";
    $this->dbF->setRow($sqlUpdate2, [$message, $receiver]);

    echo json_encode(['status' => 'success']);
  }

  public function fetchChatUserDetail()
  {
    $userId = $_POST["userId"];

    $sqlUsers = "SELECT * FROM `accounts_user` WHERE `acc_id` = ?";
    $chatUserDetail = $this->dbF->getRow($sqlUsers, [$userId]);

    echo json_encode($chatUserDetail);
  }

  public function sendFileMessage()
  {
    $sender = $_POST['sender'];
    $receiver = $_POST['receiver'];
    if (!empty($_FILES['file']['name'])) {
      $returnFile = $this->functions->uploadSingleFile($_FILES["file"], "chat");
      $fileName = $_FILES['file']['name'];
    } else {
      $returnFile = '';
      $fileName = '';
    }

    $message = "<a href='" . WEB_URL . "/images/" . $returnFile . "' target='_blank'><span class='material-icons-outlined'>file_present</span> " . htmlspecialchars($fileName) . "</a>";
    $sql = "INSERT INTO `chat_messages` (`sender_id`, `receiver_id`, `message`) VALUES (?, ?, ?)";
    $this->dbF->setRow($sql, [$sender, $receiver, $message]);

    echo json_encode(['status' => 'success']);
  }

  public function addNewExpenseCategory()
  {
    if (isset($_POST["expensecategory"])) {
      $category = trim($_POST["expensecategory"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($category)) {
        echo json_encode(["status" => "error", "message" => "Category Title is required."]);
        return;
      }

      $sql = "INSERT INTO `expense_categories` (`category`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$category, $publish]);

      echo json_encode(["status" => "success", "message" => "Category added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Expense Category added: Name = {$category}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editExpenseCategory()
  {
    if (isset($_POST["expenseCategoryId"])) {
      $category = trim($_POST["expensecategory"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($category)) {
        echo json_encode(["status" => "error", "message" => "Category Title is required."]);
        return;
      }
      $editId = $_POST["expenseCategoryId"];

      $sql = "UPDATE `expense_categories` SET `category` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$category, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Category updated successfully! Redirecting...", "redirect" => "expensecategories"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Expense Cateogry updated: ID = {$editId}, Name = {$category}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteExpenseCategory()
  {
    if (isset($_POST["deleteExpenseCategoryId"])) {
      $expenseCategoryId = $_POST["deleteExpenseCategoryId"];

      $sqlGet = "SELECT `category` FROM `expense_categories` WHERE `id` = ?";
      $catData = $this->dbF->getRow($sqlGet, [$expenseCategoryId]);
      $catName = $catData['category'] ?? 'Unknown';

      $sql = "DELETE FROM `expense_categories` WHERE `id` = '$expenseCategoryId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Expense Category deleted: ID = {$expenseCategoryId}, Name = {$catName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function addNewInvoiceStatus()
  {
    if (isset($_POST["invoicestatus"])) {
      $status = trim($_POST["invoicestatus"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($status)) {
        echo json_encode(["status" => "error", "message" => "Status Title is required."]);
        return;
      }

      $sql = "INSERT INTO `invoice_statuses` (`name`, `publish`) VALUES (?, ?)";
      $this->dbF->setRow($sql, [$status, $publish]);

      echo json_encode(["status" => "success", "message" => "Status added successfully."]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Invoice Status added: Name = {$status}, Status = {$publishText}";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editInvoiceStatus()
  {
    if (isset($_POST["invoiceStatusId"])) {
      $status = trim($_POST["invoicestatus"] ?? '');
      $publish = isset($_POST["publish"]) ? 1 : 0;

      if (empty($status)) {
        echo json_encode(["status" => "error", "message" => "Status Title is required."]);
        return;
      }
      $editId = $_POST["invoiceStatusId"];

      $sql = "UPDATE `invoice_statuses` SET `name` = ?, `publish` = ? WHERE `id` = ?";
      $this->dbF->setRow($sql, [$status, $publish, $editId]);

      echo json_encode(["status" => "success", "message" => "Status updated successfully! Redirecting...", "redirect" => "invoicestatuses"]);

      $publishText = $publish ? "Published" : "Unpublished";
      $desc = "Invoice Status updated: ID = {$editId}, Name = {$status}, Status = {$publishText}";
      $this->functions->setlog("Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteInvoiceStatus()
  {
    if (isset($_POST["deleteInvoiceStatusId"])) {
      $invoiceStatusId = $_POST["deleteInvoiceStatusId"];

      $sqlGet = "SELECT `name` FROM `invoice_statuses` WHERE `id` = ?";
      $statusData = $this->dbF->getRow($sqlGet, [$invoiceStatusId]);
      $statusName = $statusData['name'] ?? 'Unknown';

      $sql = "DELETE FROM `invoice_statuses` WHERE `id` = '$invoiceStatusId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Invoice Status deleted: ID = {$invoiceStatusId}, Name = {$statusName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function createInvoice()
  {
    $clientId = $_POST['invclientId'];
    $company = $_POST['invcompany'] ?? '';
    $invdate = $_POST['invdate'];
    $duedate = $_POST['invduedate'];
    $expirydate = $_POST['invexpirydate'];
    $invfor = $_POST['invfor'];
    $billed = $_POST['invbilledamount'];
    $invtype = $_POST['invtype'];
    $totalBilled = $_POST['invtotalbillamount'];
    $projTotal = $_POST['invprototalamount'];
    $rcvdAmount = $_POST['invrcvdamount'];
    $status = $_POST['invstatus'];

    $invTaxAmount = $_POST["invtaxamount"] ?? 0;
    $payMode = $_POST['invpaymode'] ?? '';
    $bankPost = $_POST['invbankpost'] ?? '';
    $desc = $_POST['invdesc'] ?? '';

    $vendorFields = [
      'vendor_name' => $_POST['vendorname'] ?? '',
      'vendor_email' => $_POST['vendoremail'] ?? '',
      'vendor_phone' => $_POST['vendorphone'] ?? '',
      'vendor_domain' => $_POST['vendordomain'] ?? '',
      'vendor_amount' => $_POST['vendoramount'] ?? '',
      'vendor_paid_date' => $_POST['vendorpaiddate'] ?? '',
      'vendor_rg_date' => $_POST['vendorrgdate'] ?? '',
    ];

    if (empty($clientId) || empty($invdate) || empty($duedate) || empty($invfor) || empty($billed) || empty($invtype) || empty($totalBilled) || empty($status) || empty($expirydate)) {
      echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
      return;
    }

    $format = $_POST["invoiceformat"];

    if ($format === "Imedia") {
      $today = date('Y-m-d');
      $day = date('d', strtotime($today));
      $year = date('y', strtotime($today));
      $monthAbbr = strtoupper(substr(date('F'), 0, 2));

      $sql = "SELECT COUNT(*) AS total FROM invoices WHERE created_at = ?";
      $data = $this->dbF->getRow($sql, [$today]);

      $invoiceCount = (int) ($data["total"] ?? 0) + 1;
      $invoiceCountPadded = str_pad($invoiceCount, 2, '0', STR_PAD_LEFT);

      $invNo = "{$monthAbbr}{$year}-{$day}{$invoiceCountPadded}";
    } else {
      $invNo = $_POST['invnumber'] ?? '';
    }


    $sql = "INSERT INTO `invoices` (`invoice_number`, `invoice_company`, `client_company`, `client_id`, `invoice_date`, `due_date`, `expiry_date`, `invoice_for`, `description`, `total_amount`, `billed_amount`, `invoice_type`, `total_billed_amount`, `tax_amount`, `received_amount`, `status`, `payment_mode`, `bank_post`, `created_at`) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    $invId = $this->dbF->setRow($sql, [$invNo, $format, $company, $clientId, $invdate, $duedate, $expirydate, $invfor, $desc, $projTotal, $billed, $invtype, $totalBilled, $invTaxAmount, $rcvdAmount, $status, $payMode, $bankPost, $today]);

    foreach ($vendorFields as $name => $value) {
      if (!empty($value)) {
        $sql = "INSERT INTO `invoice_vendor_details` (`invoice_id`, `detail_name`, `detail_value`) VALUES (?, ?, ?)";
        $this->dbF->setRow($sql, [$invId, $name, $value]);
      }
    }

    $doneCreate = "Invoice Created, Invoice No : " . $invNo . " , Company Name : " . $company . " , Description : " . $desc . " , Status : " . $status;
    $this->functions->setlog("Invoice Created", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $doneCreate);

    $recurrence = $_POST['invrecurrence'] ?? '';
    $recurrDur = $_POST['invrecurrenceduration'] ?? '';
    $expDur = $_POST['invexpduration'] ?? 0;

    $invpdf = empty($_FILES['invpdf']['name']) ? false : true;

    if ($recurrence != '' && $recurrence != 'no' && !empty($recurrDur) && $recurrDur != '') {
      $duedate = date('Y-m-d', strtotime("+ $recurrDur month", strtotime("$duedate")));
      $expirydate = date('Y-m-d', strtotime("+ $expDur month", strtotime("$expirydate")));
      $status = 3; // Pending
      $invNo = '';
      $invdate = '';
      $rcvdAmount = '';
      $payMode = '';
      $invtype = '';

      $sql2 = "INSERT INTO `invoices` (`invoice_number`, `invoice_company`, `client_company`, `client_id`, `invoice_date`, `due_date`, `expiry_date`, `invoice_for`, `description`, `total_amount`, `billed_amount`, `invoice_type`, `total_billed_amount`, `tax_amount`, `received_amount`, `status`, `payment_mode`, `bank_post`, `created_at`) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      $this->dbF->setRow($sql2, [$invNo, $format, $company, $clientId, $invdate, $duedate, $expirydate, $invfor, $desc, $projTotal, $billed, $invtype, $totalBilled, $invTaxAmount, $rcvdAmount, $status, $payMode, $bankPost, $today]);

      $doneCreateRecur = "Invoice Created as Recurrence , Invoice No : " . $invNo . " , Company Name : " . $company . " , Description : " . $desc . " , Status : " . $status;
      $this->functions->setlog("Recurrence Invoice Created", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $doneCreateRecur);
    }

    if ($invpdf) {
      $returnPDF = $this->functions->uploadSingleFile($_FILES["invpdf"], 'invoicepdf');
    } else {
      $returnPDF = '';
    }

    $docsArray = [];
    $invDocs = '';

    if (!empty($_FILES['invdocs']['name'])) {
      foreach ($_FILES['invdocs']['name'] as $key => $value) {
        if (!empty($value)) {
          $fileData = [
            'name' => $_FILES['invdocs']['name'][$key],
            'full_path' => $_FILES['invdocs']['full_path'][$key],
            'type' => $_FILES['invdocs']['type'][$key],
            'tmp_name' => $_FILES['invdocs']['tmp_name'][$key],
            'error' => $_FILES['invdocs']['error'][$key],
            'size' => $_FILES['invdocs']['size'][$key],
          ];

          $docsArray[] = $this->functions->uploadSingleFile($fileData, 'invoicedocs');
        }
      }
      if (!empty($docsArray)) {
        $invDocs = json_encode($docsArray);
      }
    }

    $sqlCheck = "SELECT * FROM `invoice_docs` WHERE `invoice_id` = ?";
    $this->dbF->getRow($sqlCheck, [$invId]);

    if ($this->dbF->rowCount > 0) {
      $sqlUpdate = "UPDATE `invoice_docs` SET `pdf_file` = ?, `docs` = ? WHERE `invoice_id` = ?";
      $this->dbF->setRow($sqlUpdate, [$returnPDF, $invDocs, $invId]);
    } else {
      if (!empty($invDocs) || !empty($returnPDF)) {
        $sqlUpdate = "INSERT INTO `invoice_docs` (`invoice_id`, `pdf_file`, `docs`) VALUES (?, ?, ?)";
        $this->dbF->setRow($sqlUpdate, [$invId, $returnPDF, $invDocs]);
      }
    }

    echo json_encode(["status" => "success", "message" => "Invoice created successfully! Redirecting...", "redirect" => "invoiceview"]);
  }
  public function editInvoice()
  {
    if (isset($_POST["invId"])) {
      $invId = $_POST["invId"];
      $invTaxAmount = $_POST["invtaxamount"];
      $invNo = $_POST['invnumber'] ?? '';
      $clientId = $_POST['invclientId'];
      $company = $_POST['invcompany'];
      $invdate = $_POST['invdate'];
      $duedate = $_POST['invduedate'];
      $expirydate = $_POST['invexpirydate'];
      $invfor = $_POST['invfor'];
      $billed = $_POST['invbilledamount'];
      $invtype = $_POST['invtype'];
      $totalBilled = $_POST['invtotalbillamount'];
      $projTotal = $_POST['invprototalamount'];
      $rcvdAmount = $_POST['invrcvdamount'];
      $status = $_POST['invstatus'];
      $payMode = $_POST['invpaymode'] ?? '';
      $bankPost = $_POST['invbankpost'] ?? '';
      $desc = $_POST['invdesc'] ?? '';

      $sqlUpdate = "UPDATE `invoices` SET `invoice_number` = ?, `company_name` = ?, `client_id` = ?, `invoice_date` = ?, `due_date` = ?, `expiry_date` = ?, `invoice_for` = ?, `description` = ?, `total_amount` = ?, `billed_amount` = ?, `invoice_type` = ?, `total_billed_amount` = ?, `received_amount` = ?, `status` = ?, `payment_mode` = ?, `bank_post` = ?, `tax_amount` = ? WHERE `id` = ?";
      $this->dbF->setRow($sqlUpdate, [$invNo, $company, $clientId, $invdate, $duedate, $expirydate, $invfor, $desc, $projTotal, $billed, $invtype, $totalBilled, $rcvdAmount, $status, $payMode, $bankPost, $invTaxAmount, $invId]);

      $doneUpdate = "Invoice Updated, Invoice No : $invNo , Company Name : $company , Description : $desc , Status : $status";
      $this->functions->setlog("Invoice Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $doneUpdate);

      $recurrence = $_POST['invrecurrence'] ?? '';
      $recurrDur = $_POST['invrecurrenceduration'] ?? '';
      $expDur = $_POST['invexpduration'] ?? 0;

      if ($recurrence != '' && $recurrence != 'no' && !empty($recurrDur) && $recurrDur != '') {
        $duedate = date('Y-m-d', strtotime("+ $recurrDur month", strtotime("$duedate")));
        $expirydate = date('Y-m-d', strtotime("+ $expDur month", strtotime("$expirydate")));
        $status = 3; // Pending
        $invNo = '';
        $invdate = '';
        $rcvdAmount = '';
        $payMode = '';
        $invtype = '';

        $sql = "INSERT INTO `invoices` (`invoice_number`, `company_name`, `client_id`, `invoice_date`, `due_date`, `expiry_date`, `invoice_for`, `description`, `total_amount`, `billed_amount`, `invoice_type`, `total_billed_amount`, `tax_amount`, `received_amount`, `status`, `payment_mode`, `bank_post`) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $this->dbF->setRow($sql, [$invNo, $company, $clientId, $invdate, $duedate, $expirydate, $invfor, $desc, $projTotal, $billed, $invtype, $totalBilled, $invTaxAmount, $rcvdAmount, $status, $payMode, $bankPost]);

        $doneCreateRecur = "Invoice Created as Recurrence , Invoice No : $invNo , Company Name : $company , Description : $desc , Status : $status";
        $this->functions->setlog("Recurrence Invoice Created", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $doneCreateRecur);
      }

      $invpdf = empty($_FILES['invpdf']['name']) ? false : true;
      $oldPDF = empty($_POST['invpdfold']) ? "" : $_POST['invpdfold'];
      $returnPDF = $oldPDF;

      if ($invpdf) {
        $this->functions->deleteOldSingleImage($oldPDF);
        $returnPDF = $this->functions->uploadSingleFile($_FILES["invpdf"], 'invoicepdf');
      }

      $sqlCheck = "SELECT * FROM `invoice_docs` WHERE `invoice_id` = ?";
      $row = $this->dbF->getRow($sqlCheck, [$invId]);

      $docsRowCount = $this->dbF->rowCount;

      $oldDocsArray = !empty($row) ? json_decode($row['docs'], true) : [];
      $docsArray = [];

      if (!empty($_FILES['invdocs']['name'][0])) {
        foreach ($oldDocsArray as $key => $value) {
          $this->functions->deleteOldSingleImage($value);
        }

        foreach ($_FILES['invdocs']['name'] as $key => $value) {
          $fileData = [
            'name' => $_FILES['invdocs']['name'][$key],
            'full_path' => $_FILES['invdocs']['full_path'][$key],
            'type' => $_FILES['invdocs']['type'][$key],
            'tmp_name' => $_FILES['invdocs']['tmp_name'][$key],
            'error' => $_FILES['invdocs']['error'][$key],
            'size' => $_FILES['invdocs']['size'][$key],
          ];

          $docsArray[] = $this->functions->uploadSingleFile($fileData, 'invoicedocs');
        }
      } else {
        $docsArray = $oldDocsArray;
      }

      $invDocs = json_encode($docsArray);

      if ($docsRowCount > 0) {
        $sqlUpdate = "UPDATE `invoice_docs` SET `pdf_file` = ?, `docs` = ? WHERE `invoice_id` = ?";
        $this->dbF->setRow($sqlUpdate, [$returnPDF, $invDocs, $invId]);
      } else {
        $sqlUpdate = "INSERT INTO `invoice_docs` (`invoice_id`, `pdf_file`, `docs`) VALUES (?, ?, ?)";
        $this->dbF->setRow($sqlUpdate, [$invId, $returnPDF, $invDocs]);
      }

      echo json_encode(["status" => "success", "message" => "Invoice updated successfully! Redirecting...", "redirect" => "invoiceview"]);
    }
  }

  public function addNewProject()
  {
    if (isset($_POST["projTitle"]) && isset($_POST["projClientId"]) && isset($_POST["projCompany"])) {

      $requiredFields = [
        "projTitle",
        "projClientId",
        "projCompany",
        "projCountry",
        "projDomain",
        "projTypeId",
        "projCategoryId",
        "projStatusId",
        "projSalesExecId",
        "projClientCoordinatorId",
        "projManagerId",
        "projPropDate",
        "projStartDate",
        "projDesignStartDate",
        "projDevelopmentStartDate",
        "projDevelopmentEndDate",
        "projdesigner",
        "projdeveloper",
        "projworker"
      ];

      foreach ($requiredFields as $field) {
        if (!isset($_POST[$field]) || (is_array($_POST[$field]) ? empty($_POST[$field]) : trim($_POST[$field]) === '')) {
          echo json_encode([
            "status" => "error",
            "message" => "Field '$field' is required and cannot be empty."
          ]);
          return;
        }
      }

      $projTitle = $_POST["projTitle"];
      $projClientId = $_POST["projClientId"];
      $projCompany = $_POST["projCompany"];
      $projCountry = $_POST["projCountry"];
      $projDomain = $_POST["projDomain"];
      $projTypeId = $_POST["projTypeId"];
      $projCategoryId = $_POST["projCategoryId"];
      $projStatusId = $_POST["projStatusId"];
      $projDesc = $_POST["projDesc"];
      $projSalesExecId = $_POST["projSalesExecId"];
      $projClientCoordinatorId = $_POST["projClientCoordinatorId"];
      $projManagerId = $_POST["projManagerId"];
      $projPropDate = $_POST["projPropDate"];
      $projStartDate = $_POST["projStartDate"];
      $projDesignStartDate = $_POST["projDesignStartDate"];
      $projDevelopmentStartDate = $_POST["projDevelopmentStartDate"];
      $projDevelopmentEndDate = $_POST["projDevelopmentEndDate"];

      $projdesigner = $_POST["projdesigner"];
      $projdeveloper = $_POST["projdeveloper"];
      $projworker = $_POST["projworker"];

      $projLogo = empty($_FILES["projLogo"]) ? false : true;

      $returnLogo = $projLogo ? $this->functions->uploadSingleImage($_FILES["projLogo"], 'projectlogo') : '';

      $sql = "INSERT INTO `projects` (`project_title`, `company_name`, `client_id`, `country`, `domain`, `type_id`, `category_id`, `status_id`, `description`, `sales_executive_id`, `client_coordinator_id`, `manager_id`, `proposal_date`, `start_date`, `design_start_date`, `development_start_date`, `development_end_date`, `logo`) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      $lastId = $this->dbF->setRow($sql, [$projTitle, $projCompany, $projClientId, $projCountry, $projDomain, $projTypeId, $projCategoryId, $projStatusId, $projDesc, $projSalesExecId, $projClientCoordinatorId, $projManagerId, $projPropDate, $projStartDate, $projDesignStartDate, $projDevelopmentStartDate, $projDevelopmentEndDate, $returnLogo]);

      foreach ($projdesigner as $userId) {
        $this->dbF->setRow("INSERT INTO `project_assignments` (`project_id`, `user_id`, `role`) VALUES (?, ?, ?)", [$lastId, $userId, 'Designer']);
      }
      foreach ($projdeveloper as $userId) {
        $this->dbF->setRow("INSERT INTO `project_assignments` (`project_id`, `user_id`, `role`) VALUES (?, ?, ?)", [$lastId, $userId, 'Developer']);
      }
      foreach ($projworker as $userId) {
        $this->dbF->setRow("INSERT INTO `project_assignments` (`project_id`, `user_id`, `role`) VALUES (?, ?, ?)", [$lastId, $userId, 'Worker']);
      }

      $userId = $this->webClass->webUserId();
      $desc = "New project added: $projTitle (Project ID: $lastId) by User ID: $userId";
      $this->functions->setlog("Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      echo json_encode(["status" => "success", "message" => "Project added successfully! Redirecting...", "redirect" => "projects"]);
    }
  }

  public function updateProjectSort()
  {
    $json = file_get_contents("php://input");
    $data = json_decode($json, true);

    if (isset($data['order']) && is_array($data['order'])) {
      $order = $data['order'];

      foreach ($order as $sortIndex => $projectId) {
        // You can use prepared statements or your DB helper function here
        $sql = "UPDATE `projects` SET `sort` = ? WHERE `id` = ?";
        $this->dbF->setRow($sql, [$sortIndex, $projectId]);
      }

      echo json_encode(["status" => "success", "message" => "Sorted successfully"]);
    } else {
      echo json_encode(["status" => "error", "message" => "Error while sorting"]);
    }
  }

  public function getClientCompanies()
  {
    if (isset($_POST["clientId"])) {
      $clientId = $_POST["clientId"];

      $sql = "SELECT DISTINCT p.company_name FROM projects p JOIN accounts_user au ON p.client_id = au.acc_id WHERE p.client_id = ?";
      $clientCompanies = $this->dbF->getRows($sql, [$clientId]);

      echo json_encode($clientCompanies);
    }
  }

  public function getClientProjects()
  {
    if (isset($_POST["clientId"])) {
      $clientId = $_POST["clientId"];

      $sql = "SELECT p.id, p.project_title FROM projects p JOIN accounts_user au ON p.client_id = au.acc_id WHERE p.client_id = ?";
      $clientProjects = $this->dbF->getRows($sql, [$clientId]);

      echo json_encode($clientProjects);
    }
  }

  public function createChatGroup()
  {
    $groupName = $_POST['groupName'];
    $members = $_POST['groupMembers'];

    $creatorId = $this->webClass->webUserId();

    $groupId = $this->dbF->setRow("INSERT INTO `chat_groups` (`group_name`, `created_by`) VALUES (?, ?)", [$groupName, $creatorId]);

    foreach ($members as $memberId) {
      $this->dbF->setRow("INSERT INTO `chat_group_members` (`group_id`, `user_id`) VALUES (?, ?)", [$groupId, $memberId]);
    }

    $this->dbF->setRow("INSERT INTO `chat_group_members` (`group_id`, `user_id`) VALUES (?, ?)", [$groupId, $creatorId]);

    echo json_encode(["status" => "success", "group_id" => $groupId]);
  }

  public function fetchGroupMessages()
  {
    $groupId = $_POST["group_id"];
    $sql = "SELECT m.*, au.acc_name as senderName FROM chat_group_messages m JOIN accounts_user au ON m.sender_id = au.acc_id WHERE m.group_id = ? ORDER BY m.created_at ASC";
    $messages = $this->dbF->getRows($sql, [$groupId]);

    echo json_encode($messages);
  }

  public function sendGroupMessage()
  {
    $sender = $_POST["sender"];
    $groupId = $_POST["group_id"];
    $message = $_POST["message"];

    $sql = "INSERT INTO `chat_group_messages` (`group_id`, `sender_id`, `message`) VALUES (?, ?, ?)";
    $this->dbF->setRow($sql, [$groupId, $sender, $message]);

    echo json_encode(["status" => "success"]);
  }

  public function addNewEvent()
  {
    if (isset($_POST["eventTitle"])) {
      $eventTitle = $_POST["eventTitle"];
      $eventCategory = $_POST["eventCategory"];
      $eventDueDate = $_POST["eventDueDate"];
      $eventClientId = $_POST["eventClientId"];
      $eventAssignTo = $_POST["eventAssignTo"];
      $eventDetails = $_POST["eventDetails"];
      $userId = $this->webClass->webUserId();

      if (empty($eventTitle) || empty($eventCategory) || empty($eventDueDate) || empty($eventClientId) || empty($eventAssignTo) || empty($eventDetails)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      if ($eventCategory == "Reminder") {
        $sql = "INSERT INTO `events` (`title`, `user_id`, `client_id`, `assign_to`, `due_date`, `category`, `detail`) VALUES (?, ?, ?, ?, ?, ?, ?)";
        $this->dbF->setRow($sql, [$eventTitle, $userId, $eventClientId, $eventAssignTo, $eventDueDate, $eventCategory, $eventDetails]);
      } else {
        $sql = "INSERT INTO `events` (`title`, `user_id`, `client_id`, `assign_to`, `due_date`, `status`, `category`, `detail`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $this->dbF->setRow($sql, [$eventTitle, $userId, $eventClientId, $eventAssignTo, $eventDueDate, 'Pending', $eventCategory, $eventDetails]);
      }

      echo json_encode(["status" => "success", "message" => "Event added successfully! Redirecting...", "redirect" => "calendar"]);

      $desc = "New event added: Title = {$eventTitle}, Category = {$eventCategory}, Due Date = {$eventDueDate}, Client ID = {$eventClientId}, Assigned To = {$eventAssignTo}";
      $this->functions->setlog("New Event Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function editEvent()
  {
    if (isset($_POST["eventId"])) {
      $eventTitle = $_POST["eventTitle"];
      $eventCategory = $_POST["eventCategory"];
      $eventDueDate = $_POST["eventDueDate"];
      $eventClientId = $_POST["eventClientId"];
      $eventAssignTo = $_POST["eventAssignTo"];
      $eventDetails = $_POST["eventDetails"];
      $eventStatus = $_POST["eventStatus"];
      $userId = $this->webClass->webUserId();

      $eventId = $_POST["eventId"];

      if (empty($eventTitle) || empty($eventCategory) || empty($eventDueDate) || empty($eventClientId) || empty($eventAssignTo) || empty($eventDetails)) {
        echo json_encode(["status" => "error", "message" => "Fill all required fields."]);
        return;
      }

      if ($eventCategory == "Reminder") {
        $sql = "UPDATE `events` SET `title` = ?, `user_id` = ?, `client_id` = ?, `assign_to` = ?, `due_date` = ?, `category` = ?, `detail` = ? WHERE `id` = ?";
        $this->dbF->setRow($sql, [$eventTitle, $userId, $eventClientId, $eventAssignTo, $eventDueDate, $eventCategory, $eventDetails, $eventId]);
      } else {
        $sql = "UPDATE `events` SET `title` = ?, `user_id` = ?, `client_id` = ?, `assign_to` = ?, `due_date` = ?, `status` = ?, `category` = ?, `detail` = ? WHERE `id` = ?";
        $this->dbF->setRow($sql, [$eventTitle, $userId, $eventClientId, $eventAssignTo, $eventDueDate, $eventStatus, $eventCategory, $eventDetails, $eventId]);
      }

      echo json_encode(["status" => "success", "message" => "Event updated successfully! Redirecting..."]);

      $desc = "event updated: Title = {$eventTitle}, Category = {$eventCategory}, Due Date = {$eventDueDate}, Client ID = {$eventClientId}, Assigned To = {$eventAssignTo}";
      $this->functions->setlog("Event Updated", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
    }
  }

  public function deleteEvent()
  {
    if (isset($_POST["deleteEventId"])) {
      $eventId = $_POST["deleteEventId"];

      $sqlGet = "SELECT `title` FROM `events` WHERE `id` = ?";
      $eventData = $this->dbF->getRow($sqlGet, [$eventId]);
      $eventName = $eventData['title'] ?? 'Unknown';

      $sql = "DELETE FROM `events` WHERE `id` = '$eventId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Event deleted: ID = {$eventId}, Name = {$eventName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function saturdaysOffAdd()
  {
    if (isset($_POST["date"])) {
      foreach ($_POST['date'] as $saturdayDate) {
        $sql = "INSERT INTO saturday_off (`date`) VALUES (?)";
        $this->dbF->setRow($sql, [$saturdayDate]);

        $desc = "New saturday date added: Date = {$saturdayDate}";
        $this->functions->setlog("New Saturday Date Added", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      }

      echo json_encode(["status" => "success", "message" => "Saturdays added successfully! Redirecting..."]);
    }
  }

  public function deleteSaturday()
  {
    if (isset($_POST["deleteSaturdayId"])) {
      $dateId = $_POST["deleteSaturdayId"];

      $sqlGet = "SELECT `saturday_off` FROM `events` WHERE `id` = ?";
      $dateData = $this->dbF->getRow($sqlGet, [$dateId]);
      $date = $dateData['date'] ?? 'Unknown';

      $sql = "DELETE FROM `saturday_off` WHERE `id` = '$dateId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Saturday Date deleted: ID = {$dateId}, Date = {$date}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function getUserReport()
  {
    if (isset($_POST["userId"]) && isset($_POST["fromdate"]) && isset($_POST["todate"])) {
      $userId = $_POST["userId"];
      $fromDate = $_POST["fromdate"];
      $toDate = $_POST["todate"];

      $sql = "SELECT * FROM activity_log WHERE ref_id = ? AND DATE(log_time) BETWEEN ? AND ? ORDER BY log_time DESC";
      $data = $this->dbF->getRows($sql, [$userId, $fromDate, $toDate]);

      if (!$data) {
        echo "<div class='alert alert-warning'>No activity found for this user during this date range.</div>";
        return;
      }

      echo "<table class='table table-bordered'>
        <thead>
            <tr>
                <th>#</th>
                <th>Timestamp</th>
                <th>Details</th>
            </tr>
        </thead>
        <tbody>";

      foreach ($data as $index => $row) {
        echo "<tr>
            <td>" . ($index + 1) . "</td>
            <td>{$row['log_time']}</td>
            <td>{$row['log_desc']}</td>
          </tr>";
      }

      echo "</tbody></table>";
    }
  }

  public function getNotifications()
  {
    $userId = $this->webClass->webUserId();
    $sql = "SELECT * FROM `notifications` WHERE `user_id` = ? AND `is_read` = 0 ORDER BY `created_at` DESC LIMIT 10";

    $data = $this->dbF->getRows($sql, [$userId]);
    echo json_encode($data);
  }

  public function addNotification($userId, $message, $link = '#')
  {
    $sql = "INSERT INTO `notifications` (`user_id`, `message`, `link`) VALUES (?, ?, ?)";
    $this->dbF->setRow($sql, [$userId, $message, $link]);
  }

  public function markNotificationRead()
  {
    if (isset($_POST["id"])) {
      $id = $_POST['id'];
      $sql = "UPDATE `notifications` SET `is_read` = 1 WHERE `id` = ?";
      $this->dbF->setRow($sql, [$id]);
    }
  }

  public function searchAll()
  {
    $search = trim($_POST['search'] ?? '');
    if ($search === '')
      exit;

    $userId = $this->webClass->webUserId();
    $userRole = $this->webClass->webUserType();

    $results = [];

    // ================== CLIENTS ==================
    if ($userRole === 1 || $userRole === 5) {
      $sql = "SELECT acc_id, acc_name FROM accounts_user WHERE acc_type = '4' AND acc_name LIKE ?";
      $rows = $this->dbF->getRows($sql, ["%$search%"]);
      if ($rows) {
        $results['Clients'] = array_map(fn($r) => [
          'title' => $r['acc_name'],
          'link' => "clientedit?id={$r['acc_id']}"
        ], $rows);
      }
    }

    // ================== TICKETS ==================
    $sql = "SELECT id, complaint FROM tickets WHERE complaint LIKE ?";
    $params = ["%$search%"];
    if ($userRole === 5) {
      $sql .= " AND assigned_to = ?";
      $params[] = $userId;
    } elseif ($userRole === 4) {
      $sql .= " AND client_id = ?";
      $params[] = $userId;
    }
    $rows = $this->dbF->getRows($sql, $params);
    if ($rows) {
      $results['Tickets'] = array_map(fn($r) => [
        'title' => $r['complaint'],
        'link' => "ticketedit?id={$r['id']}"
      ], $rows);
    }

    // ================== PROJECTS ==================
    $sql = "SELECT id, project_title FROM projects WHERE project_title LIKE ?";
    $params = ["%$search%"];
    if ($userRole === 5) {
      $sql .= " AND (sales_executive_id = ? OR client_coordinator_id = ? OR manager_id = ?)";
      $params[] = $userId;
      $params[] = $userId;
      $params[] = $userId;
    } elseif ($userRole === 4) {
      $sql .= " AND client_id = ?";
      $params[] = $userId;
    }
    $rows = $this->dbF->getRows($sql, $params);
    if ($rows) {
      $results['Projects'] = array_map(fn($r) => [
        'title' => $r['project_title'],
        'link' => "projectedit?id={$r['id']}"
      ], $rows);
    }

    // ================== EVENTS ==================
    $sql = "SELECT id, title FROM events WHERE title LIKE ?";
    $params = ["%$search%"];
    if ($userRole === 5) {
      $sql .= " AND (assign_to = ? OR user_id = ?)";
      $params[] = $userId;
      $params[] = $userId;
    } elseif ($userRole === 4) {
      $sql .= " AND client_id = ?";
      $params[] = $userId;
    }
    $rows = $this->dbF->getRows($sql, $params);
    if ($rows) {
      $results['Events'] = array_map(fn($r) => [
        'title' => $r['title'],
        'link' => "eventedit?id={$r['id']}"
      ], $rows);
    }

    // ================== INVOICES ==================
    $sql = "SELECT id, invoice_number FROM invoices WHERE invoice_number LIKE ?";
    $params = ["%$search%"];
    if ($userRole === 1) {
      $sql .= " AND created_at IS NOT NULL";
    } elseif ($userRole === 4) {
      $sql .= " AND client_id = ?";
      $params[] = $userId;
    }
    $rows = $this->dbF->getRows($sql, $params);
    if ($rows) {
      $results['Invoices'] = array_map(fn($r) => [
        'title' => "Invoice #{$r['invoice_number']}",
        'link' => "invoiceedit?id={$r['id']}"
      ], $rows);
    }

    // ================== NOTES ==================
    $sql = "SELECT id, note_text, target_type, target_id FROM notes WHERE note_text LIKE ?";
    $params = ["%$search%"];
    if ($userRole === 5) {
      $sql .= " AND created_by = ?";
      $params[] = $userId;
    } elseif ($userRole === 4) {
      $sql .= " AND target_type = 'client' AND target_id = ?";
      $params[] = $userId;
    }
    $rows = $this->dbF->getRows($sql, $params);
    if ($rows) {
      $results['Notes'] = array_map(fn($r) => [
        'title' => substr($r['note_text'], 0, 50) . '...',
        'link' => "note-view?id={$r['id']}"
      ], $rows);
    }

    // ================== ACTIVITY LOG (admin only) ==================
    if ($userRole === 1) {
      $sql = "SELECT log_id, log_title FROM activity_log WHERE log_title LIKE ? OR log_desc LIKE ? LIMIT 10";
      $rows = $this->dbF->getRows($sql, ["%$search%", "%$search%"]);
      if ($rows) {
        $results['Activity Logs'] = array_map(fn($r) => [
          'title' => $r['log_title'],
          'link' => "activity-log-view?id={$r['log_id']}"
        ], $rows);
      }
    }

    // ================== OUTPUT RESULTS ==================
    if (empty($results)) {
      echo "<p class='text-muted px-3'>No results found.</p>";
      exit;
    }

    foreach ($results as $category => $items) {
      echo "<p class='search-title'>$category</p>";
      echo '<div class="search-list d-flex flex-column gap-2">';
      foreach ($items as $item) {
        $title = htmlspecialchars($item['title']);
        echo <<<HTML
            <a class="search-list-item d-flex align-items-center gap-3" href="{$item['link']}">
                <div class="">
                  <h5 class="mb-0 search-list-title ">$title</h5>
                </div>
            </a>
HTML;
      }
      echo '<div>';
      echo '<hr>';
    }
  }

  public function getChartsData()
  {
    $sql = "SELECT `status`, COUNT(*) AS count FROM `accounts_user` GROUP BY `status`";
    $statusData = $this->dbF->getRows($sql);

    $sql = "SELECT `acc_type`, COUNT(*) AS count FROM `accounts_user` WHERE acc_type != '1' GROUP BY `acc_type`";
    $typeData = $this->dbF->getRows($sql);

    $sql = "SELECT d.designation, COUNT(*) AS staff_count FROM staff_designations sd JOIN designations d ON sd.designation_id = d.id GROUP BY d.designation";
    $staffData = $this->dbF->getRows($sql);

    $sql = "SELECT DATE(date) as expense_date, SUM(amount) as total_expense FROM daily_expenses GROUP BY DATE(date) ORDER BY DATE(date)";
    $expenseData = $this->dbF->getRows($sql);

    $sql = "SELECT status, COUNT(*) AS count FROM invoices GROUP BY status";
    $invoiceStatusData = $this->dbF->getRows($sql);

    $sql = "SELECT ps.name AS status, COUNT(p.id) AS total FROM projects p JOIN project_statuses ps ON p.status_id = ps.id GROUP BY p.status_id";
    $projectStatusData = $this->dbF->getRows($sql);

    $sql = "SELECT pt.name AS type, COUNT(p.id) AS total FROM projects p JOIN project_types pt ON p.type_id = pt.id GROUP BY p.type_id";
    $projectTypesData = $this->dbF->getRows($sql);

    $sql = "SELECT au.acc_name AS team_member, COUNT(pa.project_id) AS total_projects FROM project_assignments pa JOIN accounts_user au ON pa.user_id = au.acc_id GROUP BY pa.user_id";
    $memberProjectsData = $this->dbF->getRows($sql);

    $sql = "SELECT status, COUNT(*) AS count FROM tickets GROUP BY status";
    $ticketStatusData = $this->dbF->getRows($sql);

    $sql = "SELECT c.category, COUNT(*) AS count FROM tickets t JOIN ticket_categories c ON t.category = c.id GROUP BY t.category";
    $ticketsByCategoryData = $this->dbF->getRows($sql);

    $sql = "SELECT DATE(timestamp) as created_date, COUNT(*) as total FROM tickets GROUP BY DATE(timestamp) ORDER BY created_date";
    $ticketsCreatedOverTime = $this->dbF->getRows($sql);

    $sql = "SELECT due_date, COUNT(*) as total_events FROM events WHERE due_date >= CURDATE() GROUP BY due_date ORDER BY due_date ASC";
    $upcomingEventsOvertime = $this->dbF->getRows($sql);

    $sql = "SELECT category, COUNT(*) as total FROM events GROUP BY category";
    $eventsByCategory = $this->dbF->getRows($sql);

    echo json_encode([
      'statusData' => $statusData,
      'typeData' => $typeData,
      'staffData' => $staffData,
      'expenseData' => $expenseData,
      'invoiceStatusData' => $invoiceStatusData,
      'projectStatusData' => $projectStatusData,
      'projectTypesData' => $projectTypesData,
      'memberProjectsData' => $memberProjectsData,
      'ticketStatusData' => $ticketStatusData,
      'ticketsByCategoryData' => $ticketsByCategoryData,
      'ticketsCreatedOverTime' => $ticketsCreatedOverTime,
      'upcomingEventsOvertime' => $upcomingEventsOvertime,
      'eventsByCategoryData' => $eventsByCategory
    ]);
  }

  public function sendWhatsAppMessage()
  {
    if (!isset($_POST["to"], $_POST["body"])) {
      echo json_encode(["status" => "error", "message" => "Missing fields"]);
      return;
    }

    try {
      $to = preg_replace('/\s+/', '', $_POST['to']);
      $body = trim($_POST['body'] ?? '');
      $media = trim($_POST['media'] ?? '');

      if (!preg_match('/^\+\d{8,15}$/', $to)) {
        echo json_encode(["status" => "error", "message" => "Recipient must be E.164 e.g. +923001234567"]);
        return;
      }
      if ($body === '' && $media === '') {
        echo json_encode(["status" => "error", "message" => "Provide text or media URL"]);
        return;
      }
      if ($media !== '' && !filter_var($media, FILTER_VALIDATE_URL)) {
        echo json_encode(["status" => "error", "message" => "Media URL is invalid"]);
        return;
      }

      if ($this->waIsOptedOut($to)) {
        echo json_encode(["status" => "error", "message" => "This contact has opted out."]);
        return;
      }

      if (!$this->waWithin24h($to)) {
        echo json_encode([
          "status" => "error",
          "message" => "Free-form messages are outside the 24-hour window. Please use an approved Utility template (via rules) or wait for the user to message again."
        ]);
        return;
      }

      $qid = $this->dbF->setRow("INSERT INTO wa_queue (to_e164, rendered_body, media_url, status) 
      VALUES (?, ?, ?, 'queued')", [$to, $body, ($media ?: null)]);

      $resp = $this->tw->send($to, $body, $media ?: null);
      $sid = $resp['sid'] ?? null;

      if ($qid) {
        $this->dbF->setRow("UPDATE wa_queue SET twilio_sid = ?, status = 'sent' WHERE id = ?", [$sid, $qid]);
      }

      $acc = $this->dbF->getRow("SELECT account_id FROM whatsapp_contacts WHERE phone_e164 = ? LIMIT 1", [$to]);

      $accId = $acc['account_id'] ?? null;
      $this->dbF->setRow("INSERT INTO whatsapp_messages (account_id, direction, wa_from, wa_to, body, media_url, status, twilio_sid) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [$accId, 'outbound', TWILIO_WHATSAPP_FROM, "whatsapp:$to", $body, ($media ?: null), 'sent', $sid]);

      if ($qid) {
        $this->dbF->setRow("INSERT INTO wa_audit (queue_id, event, payload) VALUES (?, 'sent_immediate', ?)", [$qid, json_encode($resp)]);
      }

      echo json_encode(["status" => "success", "message" => "Message sent"]);
    } catch (Exception $e) {
      if (!empty($qid)) {
        $this->dbF->setRow("UPDATE wa_queue SET status = 'failed' WHERE id = ?", [$qid]);
        $this->dbF->setRow("INSERT INTO wa_audit (queue_id, event, payload) VALUES (?, 'send_error', ?)", [$qid, json_encode(['error' => $e->getMessage()])]);
      }
      echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
  }

  public function saveWaTemplate()
  {
    if (isset($_POST["name"], $_POST["body"])) {
      $id = isset($_POST['id']) ? (int) $_POST['id'] : 0;
      $name = trim($_POST['name'] ?? '');
      $body = trim($_POST['body'] ?? '');

      if ($id > 0) {
        $this->dbF->setRow("UPDATE `wa_templates` SET `name` = ?, `body` = ? WHERE `id` = ?", [$name, $body, $id]);
      } else {
        $this->dbF->setRow("INSERT INTO `wa_templates` (`name`, `body`) VALUES(?, ?)", [$name, $body]);
      }
      echo json_encode(["status" => "success", "message" => "Template saved successfully"]);
    }
  }

  public function deleteWaTemplate()
  {
    if (isset($_POST["deleteTplId"])) {
      $tplId = $_POST["deleteTplId"];

      $sqlGet = "SELECT `name` FROM `wa_templates` WHERE `id` = ?";
      $tplData = $this->dbF->getRow($sqlGet, [$tplId]);
      $tplName = $tplData['name'] ?? 'Unknown';

      $sql = "DELETE FROM `wa_templates` WHERE `id` = '$tplId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Whatsapp Template deleted: ID = {$tplId}, Name = {$tplName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function saveWaRule()
  {
    if (isset($_POST['name'], $_POST['template_id'])) {
      $id = isset($_POST['id']) ? (int) $_POST['id'] : 0;
      $name = trim($_POST['name'] ?? '');
      $priority = (int) ($_POST['priority'] ?? 100);
      $active = (int) ($_POST['active'] ?? 1);
      $match_type = trim($_POST['match_type'] ?? 'keyword');
      $match_value = trim($_POST['match_value'] ?? '');
      $template_id = (int) ($_POST['template_id'] ?? 0);
      $delay = (int) ($_POST['schedule_delay_minutes'] ?? 0);
      $stop_after_match = (int) ($_POST['stop_after_match'] ?? 1);

      if ($name === '' || !$template_id) {
        echo json_encode(["status" => "error", 'message' => 'Name & Template required']);
        return;
      }
      if (!in_array($match_type, ['keyword', 'first_message', 'always'], true)) {
        echo json_encode(["status" => "error", 'message' => 'Invalid match_type']);
        return;
      }

      $params = [$name, $priority, $active, $match_type, $match_value, $template_id, $delay, $stop_after_match];

      if ($id > 0) {
        $this->dbF->setRow("UPDATE wa_rules SET name = ?, priority = ?, active = ?, match_type = ?, match_value = ?, template_id = ?, schedule_delay_minutes = ?, stop_after_match = ? WHERE id = ?", array_merge($params, [$id]));
      } else {
        $this->dbF->setRow("INSERT INTO wa_rules (name, priority, active, match_type, match_value, template_id, schedule_delay_minutes, stop_after_match) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", $params);
      }

      echo json_encode(["status" => "success", "message" => "Rule saved successfully"]);
    }
  }

  public function deleteWaRule()
  {
    if (isset($_POST["deleteRuleId"])) {
      $ruleId = $_POST["deleteRuleId"];

      $sqlGet = "SELECT `name` FROM `wa_rules` WHERE `id` = ?";
      $ruleData = $this->dbF->getRow($sqlGet, [$ruleId]);
      $ruleName = $ruleData['name'] ?? 'Unknown';

      $sql = "DELETE FROM `wa_rules` WHERE `id` = '$ruleId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Whatsapp Template deleted: ID = {$ruleId}, Name = {$ruleName}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }

  public function waList()
  {
    $q = trim($_POST['q'] ?? '');

    $sql = "SELECT
    c.id AS contact_id,
    c.display_name,
    c.phone_e164,
    (
      SELECT m.body
      FROM whatsapp_messages m
      WHERE (m.wa_from = CONCAT('whatsapp:', c.phone_e164)
            OR m.wa_to   = CONCAT('whatsapp:', c.phone_e164))
      ORDER BY m.id DESC
      LIMIT 1
    ) AS last_body,
    (
      SELECT m.created_at
      FROM whatsapp_messages m
      WHERE (m.wa_from = CONCAT('whatsapp:', c.phone_e164)
            OR m.wa_to   = CONCAT('whatsapp:', c.phone_e164))
      ORDER BY m.id DESC
      LIMIT 1
    ) AS last_at
  FROM whatsapp_contacts c
  " . ($q !== '' ? "WHERE (c.display_name LIKE ? OR c.phone_e164 LIKE ?)" : "") . "
  ORDER BY (last_at IS NULL), last_at DESC, c.id DESC
  LIMIT 100";

    $params = ($q !== '' ? ["%$q%", "%$q%"] : []);
    $rows = $this->dbF->getRows($sql, $params);


    if (!$rows) {
      echo '<div class="p-3 muted">No conversations yet.</div>';
      return;
    }

    foreach ($rows as $r) {
      $n = $r['display_name'] ?: '(unknown)';
      $p = $r['phone_e164'];
      $lb = $r['last_body'] ?: '';
      echo '<li class="list-group-item px-2 py-2 bg-transparent cursor-pointer wa-item" data-phone="' . htmlspecialchars($p) . '" data-name="' . htmlspecialchars($n) . '">
            <div class="fw-bold">' . htmlspecialchars($n) . '</div>
            <div class="text-muted">' . htmlspecialchars($p) . '</div>
            <div class="">' . htmlspecialchars($lb) . '</div>
          </li>';
    }
  }

  public function waLoadThread()
  {
    $phone = trim($_POST['phone'] ?? '');
    if ($phone === '') {
      echo '<div class="muted">No phone provided.</div>';
      return;
    }
    $sql = "SELECT id, direction, wa_from, wa_to, body, media_url, status, created_at FROM whatsapp_messages WHERE 
    wa_from = CONCAT('whatsapp:', ?) OR wa_to = CONCAT('whatsapp:', ?) ORDER BY id ASC LIMIT 200";
    $rows = $this->dbF->getRows($sql, [$phone, $phone]);

    if (!$rows) {
      echo '<div class="muted">No messages yet.</div>';
      return;
    }

    foreach ($rows as $m) {
      $isOut = ($m['direction'] === 'outbound');
      $cls = $isOut ? 'wa-bubble wa-out' : 'wa-bubble wa-in';
      $body = trim((string) ($m['body'] ?? ''));
      $media = trim((string) ($m['media_url'] ?? ''));
      $time = date('d M Y, h:i A', strtotime($m['created_at']));
      echo '<div class="' . $cls . '">';
      if ($body !== '')
        echo nl2br(htmlspecialchars($body)) . '<br>';
      if ($media !== '')
        echo '<a href="' . htmlspecialchars($media) . '" target="_blank">Media</a><br>';
      echo '<div class="muted">' . $time . ' · ' . $m['status'] . '</div>';
      echo '</div>';
    }
  }

  public function waSendFromThread()
  {
    if (!isset($_POST['to'])) {
      echo json_encode(['status' => 'error', 'message' => 'Recipient missing']);
      return;
    }
    $to = preg_replace('/\s+/', '', $_POST['to']);
    $body = trim($_POST['body'] ?? '');
    $media = trim($_POST['media'] ?? '');

    if (!preg_match('/^\+\d{8,15}$/', $to)) {
      echo json_encode(["status" => "error", "message" => "Recipient must be E.164 e.g. +923001234567"]);
      return;
    }
    if ($body === '' && $media === '') {
      echo json_encode(["status" => "error", "message" => "Provide text or media URL"]);
      return;
    }
    if ($media !== '' && !filter_var($media, FILTER_VALIDATE_URL)) {
      echo json_encode(["status" => "error", "message" => "Media URL is invalid"]);
      return;
    }

    if ($this->waIsOptedOut($to)) {
      echo json_encode(["status" => "error", "message" => "This contact has opted out."]);
      return;
    }
    if (!$this->waWithin24h($to)) {
      echo json_encode([
        "status" => "error",
        "message" => "Free-form messages are outside the 24-hour window. Please use an approved Utility template or wait for a new inbound."
      ]);
      return;
    }

    try {
      $qid = $this->dbF->setRow("INSERT INTO wa_queue (to_e164, rendered_body, media_url, status) 
      VALUES (?, ?, ?, 'queued')", [$to, $body, ($media ?: null)]);
      $resp = $this->tw->send($to, $body, $media ?: null);
      $sid = $resp['sid'] ?? null;

      if ($qid)
        $this->dbF->setRow("UPDATE wa_queue SET twilio_sid = ?, status = 'sent' WHERE id = ?", [$sid, $qid]);

      $acc = $this->dbF->getRow("SELECT account_id FROM whatsapp_contacts WHERE phone_e164 = ? LIMIT 1", [$to]);
      $accId = $acc['account_id'] ?? null;

      $this->dbF->setRow(
        "INSERT INTO whatsapp_messages (account_id, direction, wa_from, wa_to, body, media_url, status, twilio_sid) 
      VALUES (?, 'outbound', ?, ?, ?, ?, 'sent', ?)",
        [$accId, TWILIO_WHATSAPP_FROM, "whatsapp:$to", $body, ($media ?: null), $sid]
      );

      if ($qid) {
        $this->dbF->setRow("INSERT INTO wa_audit (queue_id, event, payload) VALUES (?, 'sent_immediate', ?)", [$qid, json_encode($resp)]);
      }
      echo json_encode(['status' => 'success', 'message' => 'Message sent']);
    } catch (Exception $e) {
      if (!empty($qid)) {
        $this->dbF->setRow("UPDATE wa_queue SET status='failed' WHERE id=?", [$qid]);
        $this->dbF->setRow("INSERT INTO wa_audit (queue_id, event, payload) VALUES (?, 'send_error', ?)", [$qid, json_encode(['error' => $e->getMessage()])]);
      }
      echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
    }
  }

  private function waWithin24h(string $phoneE164): bool
  {
    $row = $this->dbF->getRow("SELECT MAX(created_at) AS last_in FROM whatsapp_messages 
    WHERE direction='inbound' AND (wa_from=CONCAT('whatsapp:', ?) OR wa_to=CONCAT('whatsapp:', ?))", [$phoneE164, $phoneE164]);
    if (empty($row['last_in']))
      return false;
    return (time() - strtotime($row['last_in'])) <= 24 * 3600;
  }

  private function waIsOptedOut(string $phoneE164): bool
  {
    $c = $this->dbF->getRow("SELECT opt_out FROM whatsapp_contacts WHERE phone_e164=? LIMIT 1", [$phoneE164]);
    return $c && (int) $c['opt_out'] === 1;
  }

  public function waBulkEnqueue()
  {
    if (isset($_POST['body']) || isset($_POST['media'])) {
      if (isset($_POST['recipients'])) {
        $body = trim($_POST['body'] ?? '');
        $media = trim($_POST['media'] ?? '');
        $rawSchedule = trim($_POST['schedule_at'] ?? '');
        $scheduleAt = null;

        if ($body === '' && $media === '') {
          echo json_encode(["status" => "error", "message" => "Provide text or media URL"]);
          return;
        }
        if ($media !== '' && !filter_var($media, FILTER_VALIDATE_URL)) {
          echo json_encode(["status" => "error", "message" => "Media URL is invalid"]);
          return;
        }
        if ($rawSchedule !== '') {
          $ts = strtotime(str_replace('T', ' ', $rawSchedule));
          if ($ts === false) {
            echo json_encode(["status" => "error", "message" => "Invalid schedule datetime"]);
            return;
          }
          $scheduleAt = date('Y-m-d H:i:s', $ts);
        }

        $targets = [];
        $recipientsParam = $_POST['recipients'] ?? [];
        if (is_array($recipientsParam) && count($recipientsParam) > 0) {
          $phones = [];
          foreach ($recipientsParam as $r) {
            $n = preg_replace('/\s+/', '', (string) $r);
            if ($n !== '')
              $phones[] = $n;
          }
          $phones = array_values(array_unique($phones));

          if (!empty($phones)) {
            $in = implode(',', array_fill(0, count($phones), '?'));
            $rows = $this->dbF->getRows("SELECT phone_e164, COALESCE(NULLIF(display_name,''), phone_e164) AS display_name FROM whatsapp_contacts WHERE phone_e164 IN ($in)", $phones);
            $found = [];
            foreach ($rows as $row) {
              $targets[$row['phone_e164']] = $row['display_name'] ?: $row['phone_e164'];
              $found[$row['phone_e164']] = true;
            }
            // Any unmatched phones fall back to their phone as name
            foreach ($phones as $p) {
              if (empty($found[$p]))
                $targets[$p] = $p;
            }
          }
        }

        if (!empty($_FILES['csvfile']) && is_uploaded_file($_FILES['csvfile']['tmp_name'])) {
          $csvPath = $_FILES['csvfile']['tmp_name'];
          $fh = fopen($csvPath, 'r');
          if ($fh !== false) {
            $first = fgets($fh);
            if ($first === false) {
            } else {
              $delim = (substr_count($first, ';') > substr_count($first, ',')) ? ';' : ',';
              fseek($fh, 0);

              $header = fgetcsv($fh, 0, $delim);
              $hasHeader = false;
              $map = ['phone' => -1, 'name' => -1];

              if (is_array($header)) {
                $lower = array_map(function ($h) {
                  return strtolower(trim((string) $h));
                }, $header);
                $phoneKeys = ['phone', 'number', 'msisdn', 'whatsapp', 'mobile', 'contact', 'to'];
                foreach ($phoneKeys as $k) {
                  $idx = array_search($k, $lower, true);
                  if ($idx !== false) {
                    $map['phone'] = $idx;
                    break;
                  }
                }
                $nameIdx = array_search('name', $lower, true);
                if ($nameIdx !== false)
                  $map['name'] = $nameIdx;

                if ($map['phone'] >= 0)
                  $hasHeader = true;
              }

              if (!$hasHeader) {
                $dataRow = $header;
                if (is_array($dataRow) && count($dataRow) > 0) {
                  $phone = preg_replace('/\s+/', '', (string) ($dataRow[0] ?? ''));
                  $name = trim((string) ($dataRow[1] ?? ''));
                  if ($phone !== '')
                    $targets[$phone] = ($name !== '' ? $name : $phone);
                }
              }

              while (($row = fgetcsv($fh, 0, $delim)) !== false) {
                if (!is_array($row) || count($row) === 0)
                  continue;
                if ($hasHeader) {
                  $phone = ($map['phone'] >= 0) ? preg_replace('/\s+/', '', (string) ($row[$map['phone']] ?? '')) : '';
                  $name = ($map['name'] >= 0) ? trim((string) ($row[$map['name']] ?? '')) : '';
                } else {
                  $phone = preg_replace('/\s+/', '', (string) ($row[0] ?? ''));
                  $name = trim((string) ($row[1] ?? ''));
                }
                if ($phone === '')
                  continue;
                $targets[$phone] = ($name !== '' ? $name : ($targets[$phone] ?? $phone));
                if (count($targets) >= 10000)
                  break;
              }
            }
            fclose($fh);
          }
        }

        if (empty($targets)) {
          echo json_encode(["status" => "error", "message" => "No recipients found (select from list and/or upload CSV)."]);
          return;
        }

        $ok = 0;
        $skipped = [];
        foreach ($targets as $to => $name) {
          if (!preg_match('/^\+\d{8,15}$/', $to)) {
            $skipped[] = [$to, 'invalid_e164'];
            continue;
          }

          if ($this->waIsOptedOut($to)) {
            $skipped[] = [$to, 'opt_out'];
            continue;
          }

          if (!$this->waWithin24h($to)) {
            $skipped[] = [$to, 'outside_24h_window'];
            continue;
          }

          $renderedBody = $body;
          if ($renderedBody !== '') {
            $renderedBody = WATemplate::render($renderedBody, [
              'name' => $name ?: $to,
              'phone' => $to,
            ]);
          }

          $this->dbF->setRow("INSERT INTO wa_queue (to_e164, rendered_body, media_url, status, schedule_at) 
          VALUES (?, ?, ?, 'queued', ?)", [$to, $renderedBody, ($media ?: null), $scheduleAt]);
          $ok++;
        }

        echo json_encode([
          "status" => "success",
          "message" => "Bulk enqueue complete",
          "enqueued" => $ok,
          "skipped" => $skipped
        ]);
      }
    }
  }

  public function fetchMetaLeadDetails()
  {
    if (isset($_POST["metaLeadId"])) {
      $metaLeadId = $_POST["metaLeadId"];

      $sqlGet = "SELECT * FROM meta_leads WHERE id = ?";
      $metaLeadData = $this->dbF->getRow($sqlGet, [$metaLeadId]);

      if ($this->dbF->rowCount) {
        $answers = $this->dbF->getRows("SELECT name, `value` FROM meta_lead_answers WHERE lead_id = ? ORDER BY id ASC", [$metaLeadId]);
        echo json_encode(["status" => "success", "lead" => $metaLeadData, "answers" => $answers]);
      } else {
        echo json_encode(["status" => "error", "message" => "Lead not found"]);
      }
    }
  }

  public function deleteMetaLead()
  {
    if (isset($_POST["deleteMetaLeadId"])) {
      $metaLeadId = $_POST["deleteMetaLeadId"];

      $sql = "DELETE FROM `meta_leads` WHERE `id` = '$metaLeadId'";
      $this->dbF->setRow($sql);

      if ($this->dbF->rowCount) {
        echo 1;
        $desc = "Meta Lead deleted: ID = {$metaLeadId}";
        $this->functions->setlog("Deleted", $this->functions->webUserName($_SESSION["webUser"]["id"], 'acc_name'), $this->webClass->webUserId(), $desc);
      } else {
        echo 0;
      }
    }
  }
  
  public function detectionDetailsAjax()
    {
    $matchedId = isset($_POST['matched_record_id']) ? (int)$_POST['matched_record_id'] : 0;
    if ($matchedId <= 0) {
        echo json_encode(['status' => 0, 'html' => '<div class="alert alert-danger">Invalid ID</div>']);
        exit;
    }

    $sql = "SELECT * FROM pfa_match_detections WHERE matched_record_id = '$matchedId' ORDER BY id ASC";
    $rows = $this->dbF->getRows($sql);

    if (empty($rows)) {
        echo json_encode(['status' => 1, 'html' => '<div class="alert alert-warning">No data found.</div>']);
        exit;
    }

    $html = '<div class="table-responsive">';
    $html .= '<table class="table table-bordered table-striped">';
    $html .= '<thead>
                <tr>
                  <th>ID</th>
                  <th>Search History ID</th>
                  <th>External ID</th>
                  <th>First Name</th>
                  <th>Middle</th>
                  <th>Surname</th>
                  <th>Full Name</th>
                  <th>Nationality</th>
                  <th>DOB</th>
                  <th>Confidence</th>
                  <th>Score</th>
                </tr>
              </thead><tbody>';

    foreach ($rows as $r) {
        $html .= '<tr>
          <td>'.(int)($r['id'] ?? 0).'</td>
          <td>'.htmlspecialchars($r['search_history_id'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_external_id'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_first_name'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_middle_name'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_surname'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_full_name'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_nationality'] ?? '').'</td>
          <td>'.htmlspecialchars($r['matched_dob'] ?? '').'</td>
          <td>'.htmlspecialchars($r['match_confidence'] ?? '').'</td>
          <td>'.htmlspecialchars($r['match_score'] ?? '').'</td>
        </tr>';
    }

    $html .= '</tbody></table></div>';

    echo json_encode(['status' => 1, 'html' => $html]);
    exit;
}

  public function saveReviewNoteAjax()
    {
    $matchedId = isset($_POST['matched_record_id']) ? (int)$_POST['matched_record_id'] : 0;
    $notes     = isset($_POST['review_notes']) ? trim($_POST['review_notes']) : '';
    if ($matchedId <= 0) {
        echo json_encode(['status' => 0, 'message' => 'Invalid matched_record_id']);
        exit;
    }
    // $notesEsc = $this->dbF->escapeString($notes);
    $sql = "UPDATE pfa_match_detections
            SET review_notes = '$notes'
            WHERE matched_record_id = '$matchedId'";

    $this->dbF->setRow($sql);
    echo json_encode(['status' => 1, 'message' => 'Saved']);
    exit;
    }
    
    public function updateDetectionStatusAjax()
    {
    $matchedId = isset($_POST['matched_record_id']) ? (int)$_POST['matched_record_id'] : 0;
    $status    = isset($_POST['status']) ? trim($_POST['status']) : '';
    if ($matchedId <= 0) {
        echo json_encode(['status'=>0,'message'=>'Invalid matched_record_id']); exit;
    }
    if (!in_array($status, ['Accept','Reject'], true)) {
        echo json_encode(['status'=>0,'message'=>'Invalid status']); exit;
    }
    $sql = "UPDATE pfa_match_detections
            SET status = '$status'
            WHERE matched_record_id = '$matchedId'";
    $this->dbF->setRow($sql);
    echo json_encode(['status'=>1]); exit;
}

    public function pfa_search_html()
{
    // DO NOT var_dump in ajax html response
    // var_dump($_POST);

    $term = isset($_POST['term']) ? trim((string)$_POST['term']) : '';
    $type = isset($_POST['search_type']) ? trim((string)$_POST['search_type']) : 'name';

    if ($term === '') {
        echo '<div class="alert alert-warning mb-0">Search term is required.</div>';
        exit;
    }

    // ---- Timing start
    $tStart = microtime(true);

    // ---- Prepare logging info
    $termEsc       = $this->escapeSql($term);
    $typeEsc       = $this->escapeSql($type);
    $normalizedEsc = $this->escapeSql($this->normalizeText($term));
    $userId        = (int)$this->webClass->webUserId();

    $userIpEsc     = $this->escapeSql($_SERVER['REMOTE_ADDR'] ?? '');
    $userAgentEsc  = $this->escapeSql($_SERVER['HTTP_USER_AGENT'] ?? '');
    $sessionIdEsc  = $this->escapeSql(session_id() ?: '');

    // If you want to log min/max too (optional)
    $minScore   = 60;
    $maxResults = 20;

    // ---- 1) INSERT history row with initial values
    $sqlHistoryInsert = "INSERT INTO pfa_search_history
        (search_term, search_type, normalized_search, min_score, max_results, total_candidates, matched_results, highest_score, user_ip, user_agent, session_id, search_duration_ms, user_id)
        VALUES
        ('$termEsc', '$typeEsc', '$normalizedEsc', '$minScore', '$maxResults', 0, 0, 0, '$userIpEsc', '$userAgentEsc', '$sessionIdEsc', 0, '$userId')";

    $this->dbF->setRow($sqlHistoryInsert);

    // Get inserted id (depends on your dbF implementation)
    $historyId = 0;
    if (method_exists($this->dbF, 'rowLastId')) {
        $historyId = (int)$this->dbF->rowLastId();
    } elseif (method_exists($this->dbF, 'lastInsertId')) {
        $historyId = (int)$this->dbF->lastInsertId();
    }

    // ---- 2) Run search
    $resp = $this->search($term, $type, [
        'min_score'   => $minScore,
        'max_results' => $maxResults
    ]);

    // ---- Timing end
    $durationMs = (int)round((microtime(true) - $tStart) * 1000);

    if (!isset($resp['success']) || $resp['success'] !== true) {
        // update duration even on failure
        if ($historyId > 0) {
            $this->dbF->setRow("UPDATE pfa_search_history SET search_duration_ms='$durationMs' WHERE id='$historyId'");
        }

        $err = htmlspecialchars($resp['error'] ?? 'Search failed');
        echo '<div class="alert alert-danger mb-0">'.$err.'</div>';
        exit;
    }

    $totalCandidates = (int)($resp['total_candidates'] ?? 0);
    $matchedResults  = (int)($resp['matched_results'] ?? 0);
    $results         = $resp['results'] ?? [];

    // ---- Highest score
    $highestScore = 0;
    if (!empty($results)) {
        foreach ($results as $r) {
            $s = (int)($r['match_score'] ?? 0);
            if ($s > $highestScore) $highestScore = $s;
        }
    }

    // ---- 3) UPDATE history with final values
    if ($historyId > 0) {
        $sqlHistoryUpdate = "UPDATE pfa_search_history
                             SET total_candidates = '$totalCandidates',
                                 matched_results  = '$matchedResults',
                                 highest_score    = '$highestScore',
                                 search_duration_ms = '$durationMs'
                             WHERE id = '$historyId'";
        $this->dbF->setRow($sqlHistoryUpdate);
    }

    // ---------- Output HTML ----------
    echo '
    <div class="pfa-summary">
      <div class="pfa-s-item">
        <div class="pfa-s-label">Search Term</div>
        <div class="pfa-s-value">'.htmlspecialchars($term).'</div>
      </div>
      <div class="pfa-s-item">
        <div class="pfa-s-label">Results Found</div>
        <div class="pfa-s-value small">'.$matchedResults.'</div>
      </div>
      <div class="pfa-s-item">
        <div class="pfa-s-label">Candidates Scanned</div>
        <div class="pfa-s-value small">'.$totalCandidates.'</div>
      </div>
    </div>';

    if (empty($results)) {
        echo '<div class="alert alert-warning mt-3 mb-0">No matches found.</div>';
        exit;
    }

    echo '
    <div class="table-responsive mt-3">
      <table id="pfaTable" class="table table-striped table-bordered" style="width:100%">
        <thead>
          <tr>
            <th>External ID</th>
            <th>Full Name</th>
            <th>Nationality</th>
            <th>DOB</th>
            <th>Status</th>
            <th>Deceased</th>
            <th>Score</th>
            <th>Confidence</th>
            <th>Matched Fields</th>
          </tr>
        </thead>
        <tbody>';

    foreach ($results as $r) {
        $first = htmlspecialchars($r['first_name'] ?? '');
        $mid   = htmlspecialchars($r['middle_name'] ?? '');
        $sur   = htmlspecialchars($r['surname'] ?? '');

        $full = trim($first.' '.$mid.' '.$sur);
        if ($full === '') $full = htmlspecialchars($r['single_string_name'] ?? '');

        $external = htmlspecialchars($r['external_id'] ?? ($r['id'] ?? ''));
        $nat      = htmlspecialchars($r['nationality'] ?? '');
        $dob      = htmlspecialchars($r['date_of_birth'] ?? '');
        $status   = htmlspecialchars($r['active_status'] ?? '');
        $deceased = htmlspecialchars($r['deceased'] ?? '');

        $score = (int)($r['match_score'] ?? 0);
        $conf  = htmlspecialchars($r['match_confidence'] ?? '');

        $fields = $r['matched_fields'] ?? [];
        $fieldsText = htmlspecialchars(is_array($fields) ? implode(', ', $fields) : (string)$fields);

        $badge = ($score >= 90 ? 'bg-success' : ($score >= 70 ? 'bg-warning' : 'bg-danger'));

        echo '<tr>
            <td>'.$external.'</td>
            <td>'.$full.'</td>
            <td>'.$nat.'</td>
            <td>'.$dob.'</td>
            <td>'.$status.'</td>
            <td>'.$deceased.'</td>
            <td><span class="badge '.$badge.'">'.$score.'%</span></td>
            <td>'.$conf.'</td>
            <td>'.$fieldsText.'</td>
        </tr>';
    }

    echo '
        </tbody>
      </table>
    </div>';

    exit;
}


/** Fallback escape for SQL strings (use your dbF escape if you have it) */
public function escapeSql($str)
{
    // Prefer: return $this->dbF->escapeString($str);
    return str_replace(["\\", "'"], ["\\\\", "\\'"], (string)$str);
}



    public function search($searchTerm, $searchType = 'name', $options = [])
    {
    $searchTerm = trim((string)$searchTerm);

    if ($searchTerm === '') {
        return ['success' => false, 'error' => 'Search term is required'];
    }

    // defaults
    $options = array_merge([
        'min_score'   => 60,
        'max_results' => 20,
        'pool_limit'  => 500
    ], $options);

    switch ($searchType) {
        case 'name':
            return $this->searchByName($searchTerm, $options);

        case 'full_name':
            return $this->searchByFullName($searchTerm, $options);

        case 'nationality':
            return $this->searchByNationality($searchTerm, $options);

        case 'all':
            return $this->searchAll2($searchTerm, $options);

        default:
            return ['success' => false, 'error' => 'Invalid search type'];
    }
}

    public function searchByName($searchTerm, $options)
    {
        $term = $this->sqlLikeSafe($searchTerm);
        $soundex = soundex($searchTerm);
        $limit = (int)$options['pool_limit'];
    
        $sql = "SELECT
                    id, external_id, first_name, middle_name, surname, single_string_name,
                    nationality, date_of_birth, active_status, deceased
                FROM pfa_records
                WHERE
                    first_name LIKE '%$term%' OR
                    surname LIKE '%$term%' OR
                    middle_name LIKE '%$term%' OR
                    single_string_name LIKE '%$term%' OR
                    SOUNDEX(first_name) = '$soundex' OR
                    SOUNDEX(surname) = '$soundex'
                LIMIT $limit";
    
        $candidates = $this->dbF->getRows($sql);
    
        $results = $this->scoreCandidates($searchTerm, $candidates, $options, 'name');
    
        return [
            'success'          => true,
            'search_term'      => $searchTerm,
            'search_type'      => 'name',
            'total_candidates' => count($candidates),
            'matched_results'  => count($results),
            'results'          => $results
        ];
    }

    public function searchByFullName($searchTerm, $options)
    {
        $term = $this->sqlLikeSafe($searchTerm);
        $parts = $this->parseNameParts($searchTerm);
        $limit = (int)$options['pool_limit'];
    
        $sql = "SELECT
                    id, external_id, first_name, middle_name, surname, single_string_name,
                    nationality, date_of_birth, active_status, deceased
                FROM pfa_records
                WHERE single_string_name LIKE '%$term%'";
    
        if (!empty($parts['first'])) {
            $first = $this->sqlLikeSafe($parts['first']);
            $sql .= " OR first_name LIKE '%$first%'";
        }
        if (!empty($parts['last'])) {
            $last = $this->sqlLikeSafe($parts['last']);
            $sql .= " OR surname LIKE '%$last%'";
        }
    
        $sql .= " LIMIT $limit";
    
        $candidates = $this->dbF->getRows($sql);
    
        $results = $this->scoreCandidates($searchTerm, $candidates, $options, 'full_name');
    
        return [
            'success'          => true,
            'search_term'      => $searchTerm,
            'search_type'      => 'full_name',
            'parsed_name'      => $parts,
            'total_candidates' => count($candidates),
            'matched_results'  => count($results),
            'results'          => $results
        ];
    }

    public function searchByNationality($searchTerm, $options)
    {
        $term = $this->sqlLikeSafe($searchTerm);
        $maxResults = (int)$options['max_results'];
    
        $sql = "SELECT
                    id, external_id, first_name, middle_name, surname, single_string_name,
                    nationality, date_of_birth, active_status, deceased
                FROM pfa_records
                WHERE nationality LIKE '%$term%'
                LIMIT $maxResults";
    
        $rows = $this->dbF->getRows($sql);
    
        // Add a simple score field so output format stays same
        $results = [];
        foreach ($rows as $r) {
            $r['match_score'] = 100;
            $r['match_confidence'] = 'Exact Match';
            $r['matched_fields'] = ['Nationality'];
            $results[] = $r;
        }
    
        return [
            'success'         => true,
            'search_term'     => $searchTerm,
            'search_type'     => 'nationality',
            'matched_results' => count($results),
            'results'         => $results
        ];
    }

    public function searchAll2($searchTerm, $options)
    {
        $term = $this->sqlLikeSafe($searchTerm);
        $limit = (int)$options['pool_limit'];
    
        $sql = "SELECT
                    id, external_id, first_name, middle_name, surname, single_string_name,
                    nationality, date_of_birth, active_status, deceased, occupation, address
                FROM pfa_records
                WHERE
                    first_name LIKE '%$term%' OR
                    middle_name LIKE '%$term%' OR
                    surname LIKE '%$term%' OR
                    single_string_name LIKE '%$term%' OR
                    nationality LIKE '%$term%' OR
                    occupation LIKE '%$term%' OR
                    address LIKE '%$term%'
                LIMIT $limit";
    
        $candidates = $this->dbF->getRows($sql);
    
        $results = $this->scoreCandidates($searchTerm, $candidates, $options, 'all');
    
        return [
            'success'          => true,
            'search_term'      => $searchTerm,
            'search_type'      => 'all',
            'total_candidates' => count($candidates),
            'matched_results'  => count($results),
            'results'          => $results
        ];
    }

    public function sqlLikeSafe($str)
    {
        $str = (string)$str;
        $str = str_replace(["\\", "%", "_"], ["\\\\", "\\%", "\\_"], $str);
        $str = str_replace(["'"], ["\\'"], $str);
        return $str;
    }

    public function parseNameParts($name)
    {
        $name = trim(preg_replace('/\s+/', ' ', (string)$name));
        $parts = $name === '' ? [] : explode(' ', $name);
    
        return [
            'first' => $parts[0] ?? '',
            'last'  => count($parts) > 1 ? $parts[count($parts) - 1] : ''
        ];
    }
    
    public function scoreCandidates($searchTerm, $candidates, $options, $mode)
    {
        $minScore = (int)$options['min_score'];
        $maxResults = (int)$options['max_results'];
    
        $needle = $this->normalizeText($searchTerm);
    
        $scored = [];
    
        foreach ($candidates as $r) {
            $first = (string)($r['first_name'] ?? '');
            $mid   = (string)($r['middle_name'] ?? '');
            $last  = (string)($r['surname'] ?? '');
            $full  = trim($first . ' ' . $mid . ' ' . $last);
            $single = (string)($r['single_string_name'] ?? '');
            $nat   = (string)($r['nationality'] ?? '');
    
            // Decide what to compare based on mode
            if ($mode === 'name') {
                $hay = $this->normalizeText($first . ' ' . $last . ' ' . $single);
            } elseif ($mode === 'full_name') {
                $hay = $this->normalizeText($full . ' ' . $single);
            } elseif ($mode === 'all') {
                $hay = $this->normalizeText($full . ' ' . $single . ' ' . $nat);
            } else {
                $hay = $this->normalizeText($full);
            }
    
            $score = $this->similarityScore($needle, $hay);
    
            if ($score < $minScore) continue;
    
            $matchedFields = [];
            if ($first !== '' && stripos($first, $searchTerm) !== false) $matchedFields[] = 'First Name';
            if ($mid   !== '' && stripos($mid,   $searchTerm) !== false) $matchedFields[] = 'Middle Name';
            if ($last  !== '' && stripos($last,  $searchTerm) !== false) $matchedFields[] = 'Surname';
            if ($single!== '' && stripos($single,$searchTerm) !== false) $matchedFields[] = 'Full Name';
            if ($nat   !== '' && stripos($nat,   $searchTerm) !== false) $matchedFields[] = 'Nationality';
    
            $r['match_score'] = $score;
            $r['match_confidence'] = ($score >= 95 ? 'Exact Match' : ($score >= 75 ? 'Medium' : 'Low'));
            $r['matched_fields'] = $matchedFields;
            $scored[] = $r;
        }
    
        usort($scored, function($a, $b){
            return ($b['match_score'] <=> $a['match_score']);
        });
    
        return array_slice($scored, 0, $maxResults);
    }

    public function normalizeText($s)
    {
        $s = mb_strtolower(trim((string)$s), 'UTF-8');
        $s = preg_replace('/\s+/', ' ', $s);
        return $s;
    }
    
    public function similarityScore($a, $b)
    {
        if ($a === '' || $b === '') return 0;
        if (strpos($b, $a) !== false) return 100;
    
        similar_text($a, $b, $pct);
        return (int)round($pct);
    }



}
