<?php
class User
{
    private $DB_SERVER = 'localhost';
    private $DB_USERNAME = 's2300379_g5pharmaease';
    private $DB_PASSWORD = '12345';
    private $DB_DATABASE = 's2300379_g5pharmaease';
    private $conn;
    public function __construct()
    {
        $this->conn = new mysqli($this->DB_SERVER, $this->DB_USERNAME, $this->DB_PASSWORD, $this->DB_DATABASE);
        if ($this->conn->connect_error) {
            die("Connection failed: " . $this->conn->connect_error);
        }
    }
    public function getConnection()
    {
        return $this->conn;
    }
    public function getAllUsers()
    {
        $sql = "SELECT * FROM user_records";
        $result = $this->conn->query($sql);
        return $result;
    }

    public function getUsersPerGender($gender)
    {
        $sql = "SELECT count(ID) FROM user_records WHERE gender = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $gender);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_row()[0];
    }

    public function getUsersPerStatus($status)
    {
        $sql = "SELECT count(ID) FROM user_records WHERE status = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $status);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_row()[0];
    }

    public function authenticateUser($email, $password, $role)
    {
        $table = ($role === 'Admin') ? 'admin_records' : 'user_records';
        $query = "SELECT id, password FROM $table WHERE email = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param('s', $email);
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($id, $dbPassword);

        $result = ['success' => false];
        if ($stmt->num_rows > 0) {
            $stmt->fetch();
            if ($password === $dbPassword) {
                $result = ['success' => true, 'id' => $id, 'role' => $role];
            }
        }

        $stmt->close();
        return $result;
    }

    function getPDFUsers()
    {
        $conn = new mysqli($this->DB_SERVER, $this->DB_USERNAME, $this->DB_PASSWORD, $this->DB_DATABASE);

        $query = "SELECT ID, Name, Email, Gender, Status FROM user_records";
        $result = $conn->query($query);

        $users = [];
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $users[] = $row;
            }
        }

        $conn->close();
        return $users;
    }
    public function closeConnection()
    {
        if ($this->conn) {
            $this->conn->close();
        }
    }
    public function getAllUsersWithLimit($limit, $offset, $searchTerm = '')
    {
        $query = "SELECT * FROM user_records";

        if (!empty($searchTerm)) {
            $searchTerm = $this->conn->real_escape_string($searchTerm);
            $query .= " WHERE Name LIKE '%$searchTerm%' 
                       OR Email LIKE '%$searchTerm%' 
                       OR ID LIKE '$searchTerm'";
        }

        $query .= " LIMIT $limit OFFSET $offset";

        $result = $this->conn->query($query);
        return $result;
    }

    public function getTotalUserCount($searchTerm = '')
    {
        $query = "SELECT COUNT(*) as total FROM user_records";

        if (!empty($searchTerm)) {
            $searchTerm = $this->conn->real_escape_string($searchTerm);
            $query .= " WHERE Name LIKE '%$searchTerm%' 
                       OR Email LIKE '%$searchTerm%' 
                       OR ID LIKE '$searchTerm'";
        }

        $result = $this->conn->query($query);
        return $result->fetch_assoc()['total'];
    }
    public function new_user($Name, $Email, $Password, $Status, $Gender)
    {

        $data = [
            [$Name, $Email, $Password, $Status, $Gender],
        ];
        $stmt = $this->conn->prepare("INSERT INTO user_records (Name, Email, Password, Status, Gender) VALUES (?,?,?,?,?)");
        try {

            foreach ($data as $row) {
                $stmt->execute($row);
            }
            $this->conn->commit();
        } catch (Exception $e) {
            $this->conn->rollback();
            throw $e;
        }

        return true;

    }
}
?>