dsp/classes/User.php

<?php

class User {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    /**
     * Registers a new user and their personal information in the database.
     * @param array $person_data An array of personal information.
     * @param array $user_data An array of user account data.
     * @return bool True on success, false on failure.
     */
    public function registerUser($person_data, $user_data)
    {
        // Start a transaction to ensure both person and user data are saved or neither is
        $this->pdo->beginTransaction();

        try {
            // Check for duplicate ID card, phone, or email before inserting
            $dupConditions = [];
            $dupParams = [];
            $duplicateLabels = [];
            if (!empty($person_data['id_card'])) {
                $dupConditions[] = "isp_idcard = :id_card";
                $dupParams[':id_card'] = $person_data['id_card'];
                $duplicateLabels[] = 'ID card';
            }
            if (!empty($person_data['phone_number'])) {
                $dupConditions[] = "isp_phone_number = :phone";
                $dupParams[':phone'] = $person_data['phone_number'];
                $duplicateLabels[] = 'phone number';
            }
            if (!empty($person_data['email'])) {
                $dupConditions[] = "isp_email = :email";
                $dupParams[':email'] = $person_data['email'];
                $duplicateLabels[] = 'email';
            }

            if (!empty($dupConditions)) {
                $check_sql = "SELECT pkisp_id FROM ist_tbl_people WHERE " . implode(' OR ', $dupConditions);
                $check_stmt = $this->pdo->prepare($check_sql);
                $check_stmt->execute($dupParams);

                if ($check_stmt->fetch(PDO::FETCH_ASSOC)) {
                    $this->pdo->rollBack();
                    $duplicateMessage = 'information';
                    if (!empty($duplicateLabels)) {
                        if (count($duplicateLabels) === 1) {
                            $duplicateMessage = $duplicateLabels[0];
                        } elseif (count($duplicateLabels) === 2) {
                            $duplicateMessage = implode(' or ', $duplicateLabels);
                        } else {
                            $last = array_pop($duplicateLabels);
                            $duplicateMessage = implode(', ', $duplicateLabels) . ", or {$last}";
                        }
                    }
                    set_message("A user with this {$duplicateMessage} already exists.", "danger");
                    return false;
                }
            }

            // Check for duplicate username
            $check_username_sql = "SELECT pkisu_id FROM ist_tbl_users WHERE isu_name = :username";
            $check_username_stmt = $this->pdo->prepare($check_username_sql);
            $check_username_stmt->execute([':username' => $user_data['username']]);
            
            if ($check_username_stmt->fetch(PDO::FETCH_ASSOC)) {
                $this->pdo->rollBack();
                set_message("This username is already taken. Please choose another one.", "danger");
                return false;
            }

            // 1. Insert into ist_tbl_people
            $person_sql = "
                INSERT INTO ist_tbl_people (
                    isp_idcard, isp_firstname_en, isp_lastname_en, isp_sex, 
                    isp_dob, isp_pob, isp_nationality, isp_marital_status, 
                    isp_phone_number, isp_email, isp_telegram, isp_note
                ) VALUES (
                    :id_card, :first_name_en, :last_name_en, :sex, 
                    :dob, :pob, :nationality, :marital_status, 
                    :phone_number, :email, :telegram, :note
                )
            ";
            $person_stmt = $this->pdo->prepare($person_sql);
            $person_stmt->execute([
                ':id_card' => ($person_data['id_card'] ?? '') !== '' ? $person_data['id_card'] : null,
                ':first_name_en' => $person_data['first_name_en'],
                ':last_name_en' => $person_data['last_name_en'],
                ':sex' => $person_data['sex'],
                ':dob' => $person_data['dob'],
                ':pob' => $person_data['pob'],
                ':nationality' => $person_data['nationality'],
                ':marital_status' => $person_data['marital_status'],
                ':phone_number' => $person_data['phone_number'],
                ':email' => $person_data['email'],
                ':telegram' => $person_data['telegram'],
                ':note' => $person_data['note']
            ]);
            
            // Get the ID of the newly inserted person record
            $person_id = $this->pdo->lastInsertId();

            // 2. Insert into ist_tbl_users
            $user_sql = "
                INSERT INTO ist_tbl_users (
                    fkisp_id_of, isu_name, isu_password, isu_status, isu_can_run_r
                ) VALUES (
                    :fkisp_id_of, :username, :password, :status, :can_run_r
                )
            ";

            $user_stmt = $this->pdo->prepare($user_sql);
            $user_stmt->execute([
                ':fkisp_id_of' => $person_id,
                ':username' => $user_data['username'],
                ':password' => password_hash($user_data['password'], PASSWORD_DEFAULT), // Hash the password
                ':status' => $user_data['status'],
                ':can_run_r' => empty($user_data['can_run_r']) ? 0 : 1
            ]);
            
            // Commit the transaction
            $this->pdo->commit();
            return true;

        } catch (PDOException $e) {
            // Roll back the transaction on any error
            $this->pdo->rollBack();
            // Log the detailed error
            error_log("Registration failed: " . $e->getMessage());
            set_message("Registration failed due to a database error. Please try again.", "danger");
            return false;
        }
    }

    /**
     * Authenticates a user based on username and password.
     *
     * @param string $username The user's username.
     * @param string $password The user's plain-text password.
     * @return array|false User data (pkisu_id, fkisp_id_of, isu_name, isu_status) on success, false on failure.
     * @throws Exception If a database error occurs.
     */
    public function authenticateUser(string $username, string $password) {
        $sql = "SELECT pkisu_id, fkisp_id_of, isu_name, isu_password, isu_status, isu_can_run_r
                FROM ist_tbl_users
                WHERE isu_name = :username AND isu_status != 'Inactive'"; // Do not allow login for inactive users
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':username', $username);
            $stmt->execute();
            $user = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($user && password_verify($password, $user['isu_password'])) {
                // Remove password hash before returning user data
                unset($user['isu_password']);
                return $user;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Error authenticating user: " . $e->getMessage());
            throw new Exception("Authentication failed due to a server error. Please try again later.");
        }
    }

    /**
     * Retrieves full user details by user ID (pkisu_id).
     *
     * @param int $user_id The pkisu_id of the user.
     * @return array|false The combined user and person data, or false if not found.
     * @throws Exception If a database error occurs.
     */
    public function getUserDetails(int $user_id) {
        $sql = "SELECT u.*, p.*
                FROM ist_tbl_users u
                JOIN ist_tbl_people p ON u.fkisp_id_of = p.pkisp_id
                WHERE u.pkisu_id = :user_id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching user details for ID ($user_id): " . $e->getMessage());
            throw new Exception("Could not retrieve user details. Please try again later.");
        }
    }

    /**
     * Retrieves all users, optionally filtered by status and/or search query.
     *
     * @param string|null $search_query Optional search term for username, first name, last name, email, phone.
     * @param string|null $status_filter Optional status to filter by (e.g., 'Data Owner').
     * @return array An array of user data.
     * @throws Exception If a database error occurs.
     */
    public function getAllUsers(?string $search_query = null, ?string $status_filter = null): array {
        $sql = "SELECT u.pkisu_id, u.isu_name, u.isu_status, u.isu_reg_datetime, u.isu_mod_datetime,
                       u.isu_can_run_r,
                       p.isp_firstname_en, p.isp_lastname_en, p.isp_email, p.isp_phone_number
                FROM ist_tbl_users u
                JOIN ist_tbl_people p ON u.fkisp_id_of = p.pkisp_id";
        $conditions = [];
        $params = [];

        if ($status_filter) {
            $conditions[] = "u.isu_status = :status_filter";
            $params[':status_filter'] = $status_filter;
        }

        if ($search_query) {
            $search_term = '%' . $search_query . '%';
            $conditions[] = "(u.isu_name LIKE :search_query OR
                              p.isp_firstname_en LIKE :search_query OR
                              p.isp_lastname_en LIKE :search_query OR
                              p.isp_email LIKE :search_query OR
                              p.isp_phone_number LIKE :search_query)";
            $params[':search_query'] = $search_term;
        }

        if (!empty($conditions)) {
            $sql .= " WHERE " . implode(" AND ", $conditions);
        }

        $sql .= " ORDER BY u.isu_reg_datetime DESC";

        try {
            $stmt = $this->pdo->prepare($sql);
            foreach ($params as $key => &$val) {
                // Use PDO::PARAM_STR for all search/filter parameters, as they are strings
                $stmt->bindParam($key, $val, PDO::PARAM_STR);
            }
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching all users: " . $e->getMessage());
            throw new Exception("Could not retrieve user list. Please try again later.");
        }
    }

    /**
     * Gets the total count of registered users.
     *
     * @return int The total number of users.
     * @throws Exception If a database error occurs.
     */
    public function getTotalUsers(): int {
        $sql = "SELECT COUNT(*) FROM ist_tbl_users";
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchColumn();
        } catch (PDOException $e) {
            error_log("Error getting total users count: " . $e->getMessage());
            throw new Exception("Could not retrieve user count. Please try again later.");
        }
    }

    /**
     * Updates a user's status (role).
     *
     * @param int $user_id The ID of the user to update.
     * @param string $new_status The new status ('DAC Staff', 'Data Owner', 'Data User', 'Inactive').
     * @param int $mod_by The ID of the user performing the modification.
     * @return bool True on success.
     * @throws Exception If a database error occurs.
     */
    public function updateUserStatus(int $user_id, string $new_status, int $mod_by): bool {
        $sql = "UPDATE ist_tbl_users
                SET isu_status = :new_status, isu_mod_datetime = CURRENT_TIMESTAMP, isu_regby_id = :mod_by
                WHERE pkisu_id = :user_id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':new_status', $new_status);
            $stmt->bindParam(':mod_by', $mod_by, PDO::PARAM_INT);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            return $stmt->execute();
        } catch (PDOException $e) {
            error_log("Error updating user status (ID: $user_id): " . $e->getMessage());
            throw new Exception("Could not update user status. Please try again later.");
        }
    }

    /**
     * Grants or revokes R/Jupyter access for a user.
     *
     * @param int $user_id The pkisu_id of the user.
     * @param bool $can_run_r Whether the user should have access.
     * @param int $mod_by The ID of the admin performing the change.
     * @return bool True on success.
     * @throws Exception If a database error occurs.
     */
    public function updateUserRJupyterAccess(int $user_id, bool $can_run_r, int $mod_by): bool {
        $sql = "UPDATE ist_tbl_users
                SET isu_can_run_r = :can_run_r, isu_mod_datetime = CURRENT_TIMESTAMP, isu_regby_id = :mod_by
                WHERE pkisu_id = :user_id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $flag = $can_run_r ? 1 : 0;
            $stmt->bindParam(':can_run_r', $flag, PDO::PARAM_INT);
            $stmt->bindParam(':mod_by', $mod_by, PDO::PARAM_INT);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            return $stmt->execute();
        } catch (PDOException $e) {
            error_log("Error updating R/Jupyter access (ID: $user_id): " . $e->getMessage());
            throw new Exception("Could not update R/Jupyter access. Please try again later.");
        }
    }

    /**
     * Updates a user's personal information.
     *
     * @param int $person_id The pkisp_id of the person to update.
     * @param array $person_data Associative array with fields to update (e.g., isp_firstname_en, isp_phone_number).
     * @param int $mod_by The ID of the user performing the modification.
     * @return bool True on success.
     * @throws Exception If a database error occurs or duplicate entry.
     */
    public function updatePersonInfo(int $person_id, array $person_data, int $mod_by): bool {
        $setClauses = [];
        $params = [':person_id' => $person_id, ':mod_by' => $mod_by];

        foreach ($person_data as $key => $value) {
            // Only allow specific fields to be updated
            if (in_array($key, [
                'isp_idcard', 'isp_firstname_en', 'isp_lastname_en', 'isp_sex', 'isp_dob',
                'isp_pob', 'isp_nationality', 'isp_marital_status', 'isp_phone_number',
                'isp_email', 'isp_telegram', 'isp_note'
            ])) {
                $setClauses[] = "$key = :$key";
                $params[":$key"] = ($value === '' ? null : $value);
            }
        }

        if (empty($setClauses)) {
            return false; // No fields to update
        }

        $sql = "UPDATE ist_tbl_people
                SET " . implode(', ', $setClauses) . ", isp_mod_datetime = CURRENT_TIMESTAMP, isp_regby_id = :mod_by
                WHERE pkisp_id = :person_id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->rowCount() > 0;
        } catch (PDOException $e) {
            if ($e->getCode() == '23000') {
                throw new Exception("A duplicate entry was found for ID card, email, or phone number.");
            }
            error_log("Error updating person info (ID: $person_id): " . $e->getMessage());
            throw new Exception("Could not update personal information. Please try again later.");
        }
    }

    /**
     * Changes a user's password.
     *
     * @param int $user_id The pkisu_id of the user.
     * @param string $new_password The new plain-text password.
     * @param int $mod_by The ID of the user performing the modification.
     * @return bool True on success.
     * @throws Exception If password hashing fails or database error.
     */
    public function changePassword(int $user_id, string $new_password, int $mod_by): bool {
        $hashed_password = password_hash($new_password, PASSWORD_DEFAULT);
        if ($hashed_password === false) {
            throw new Exception("Failed to hash new password.");
        }

        $sql = "UPDATE ist_tbl_users
                SET isu_password = :hashed_password, isu_mod_datetime = CURRENT_TIMESTAMP, isu_regby_id = :mod_by
                WHERE pkisu_id = :user_id";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindParam(':hashed_password', $hashed_password);
            $stmt->bindParam(':mod_by', $mod_by, PDO::PARAM_INT);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            return $stmt->execute();
        } catch (PDOException $e) {
            error_log("Error changing password for user (ID: $user_id): " . $e->getMessage());
            throw new Exception("Could not change password. Please try again later.");
        }
    }
}