<?php
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

class User
{
    private $DB_SERVER = 'localhost';
    private $DB_USERNAME = 'root';
    private $DB_PASSWORD = '';
    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;
    }

    // Get all queue entries
    public function getAllQueues() {
        $sql = "SELECT ID, `Queue #`, Name, Email FROM queue ORDER BY `Queue #` ASC";
        return $this->conn->query($sql);
    }

    // Get current queue number
    public function getCurrentQueue() {
        $sql = "SELECT MIN(`Queue #`) as current_queue FROM queue";
        $result = $this->conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return intval($row['current_queue'] ?? 0);
        }
        return 0;
    }

    // Delete the current queue (lowest number)
    public function deleteCurrentQueue() {
        $currentQueue = $this->getCurrentQueue();
        if ($currentQueue <= 0) {
            return false;
        }
        $sql = "DELETE FROM queue WHERE `Queue #` = ? LIMIT 1";
        $stmt = $this->conn->prepare($sql);
        if (!$stmt) return false;
        $stmt->bind_param("i", $currentQueue);
        return $stmt->execute();
    }

    // Get queue debug info
    public function getQueueDebugInfo() {
        $currentQueue = $this->getCurrentQueue();
        $queueCount = 0;
        $sql = "SELECT COUNT(*) as count FROM queue";
        $result = $this->conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $queueCount = $row['count'];
        }
        $sql = "SHOW TABLES LIKE 'queue'";
        $tableExists = ($this->conn->query($sql)->num_rows > 0);
        $tableStructure = [];
        if ($tableExists) {
            $sql = "DESCRIBE queue";
            $result = $this->conn->query($sql);
            while ($row = $result->fetch_assoc()) {
                $tableStructure[] = $row;
            }
        }
        return [
            'current_queue' => $currentQueue,
            'queue_count' => $queueCount,
            'table_exists' => $tableExists,
            'table_structure' => $tableStructure,
            'connection_error' => $this->conn->error,
            'php_version' => phpversion()
        ];
    }

    public function getCartItemsForReceipt() {
        $formattedCartItems = [];
        if (isset($_SESSION['cart']) && !empty($_SESSION['cart'])) {
            foreach ($_SESSION['cart'] as $item) {
                // Assumes $_SESSION['cart'] items have 'name', 'price', and 'quantity' keys.
                // Please adjust these keys if your session cart structure is different.
                $name = $item['name'] ?? 'Unknown Item'; // Default if name is not set
                $price = isset($item['price']) ? floatval($item['price']) : 0.0;
                $quantity = isset($item['quantity']) ? intval($item['quantity']) : 0;
                $sub_total = $price * $quantity;

                $formattedCartItems[] = [
                    'name' => $name,
                    'quantity' => $quantity,
                    'price_each' => $price,
                    'sub_total' => $sub_total,
                ];
            }
        }
        return $formattedCartItems;
    }

    public function getUserDetailsByEmail($email) {
        $sql = "SELECT ID, Name, Email FROM user_records WHERE Email = ? LIMIT 1";
        $stmt = $this->conn->prepare($sql);
        if (!$stmt) {
            // Handle prepare error, log it, or return an error indicator
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return null;
        }
        $stmt->bind_param("s", $email);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result && $result->num_rows > 0) {
            return $result->fetch_assoc();
        }
        $stmt->close();
        return null; // User not found or error
    }

    public function getNameByQueueNumber($queueNumber) {
        if ($queueNumber <= 0) return null;
        $sql = "SELECT Name FROM queue WHERE `Queue #` = ? LIMIT 1";
        $stmt = $this->conn->prepare($sql);
        if (!$stmt) {
            error_log("Prepare failed for getNameByQueueNumber: (" . $this->conn->errno . ") " . $this->conn->error);
            return null;
        }
        $stmt->bind_param("i", $queueNumber);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result && $result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $stmt->close();
            return $row['Name'];
        }
        $stmt->close();
        return null;
    }

    /**
     * Checks if an email already exists in the user_records table.
     *
     * @param string $email The email to check.
     * @return bool True if the email exists, false otherwise.
     */
    public function isEmailRegistered(string $email): bool
    {
        $checkQuery = "SELECT id FROM user_records WHERE email = ?";
        $stmt = $this->conn->prepare($checkQuery);
        if (!$stmt) {
            error_log("Prepare failed for isEmailRegistered: (" . $this->conn->errno . ") " . $this->conn->error);
            return false; // Consider throwing an exception or handling error more robustly
        }
        $stmt->bind_param('s', $email);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    /**
     * Registers a new user in the user_records table.
     *
     * @param string $name Full name of the user.
     * @param string $email Email of the user.
     * @param string $password Password for the user (plain text).
     * @param string $phone Phone number of the user.
     * @return bool True if registration is successful, false otherwise.
     */
    public function registerNewUser(string $name, string $email, string $password, string $phone): bool
    {
        // Note: Storing plain text passwords is a security risk. Consider hashing.
        $insertQuery = "INSERT INTO user_records (name, email, password, phone) VALUES (?, ?, ?, ?)";
        $stmt = $this->conn->prepare($insertQuery);
        if (!$stmt) {
            error_log("Prepare failed for registerNewUser: (" . $this->conn->errno . ") " . $this->conn->error);
            return false; // Consider throwing an exception
        }
        $stmt->bind_param('ssss', $name, $email, $password, $phone);
        $success = $stmt->execute();
        if (!$success) {
            error_log("Execute failed for registerNewUser: (" . $stmt->errno . ") " . $stmt->error);
        }
        $stmt->close();
        return $success;
    }

    public function updateUserProfile($currentEmail, $newName, $newEmail, $newPhone, $newAddress, $newCity) {
        $sqlUpdate = "UPDATE user_records SET name = ?, email = ?, phone = ?, address = ?, city = ? WHERE email = ?";
        $stmt = $this->conn->prepare($sqlUpdate);
        if (!$stmt) {
            error_log("Prepare failed for updateUserProfile: (" . $this->conn->errno . ") " . $this->conn->error);
            return ['success' => false, 'message' => 'Database prepare statement failed.'];
        }
        $stmt->bind_param("ssssss", $newName, $newEmail, $newPhone, $newAddress, $newCity, $currentEmail);
        
        if ($stmt->execute()) {
            $affectedRows = $stmt->affected_rows;
            $stmt->close();
            if ($affectedRows > 0) {
                return ['success' => true, 'affected_rows' => $affectedRows];
            } else {
                // No rows affected could mean the data was the same, or user not found (though caught by WHERE)
                return ['success' => true, 'affected_rows' => 0, 'message' => 'No changes were made to the profile or user not found with the original email.']; 
            }
        } else {
            $error = $stmt->error;
            $stmt->close();
            error_log("Execute failed for updateUserProfile: " . $error);
            return ['success' => false, 'message' => 'Failed to update profile in database: ' . $error];
        }
    }

    public function addUserToQueue($name, $email) {
        // Determine the next queue number
        $sqlMax = "SELECT MAX(`Queue #`) as max_queue FROM queue";
        $resultMax = $this->conn->query($sqlMax);
        $nextQueueNumber = 1;
        if ($resultMax && $resultMax->num_rows > 0) {
            $rowMax = $resultMax->fetch_assoc();
            if ($rowMax['max_queue'] !== null) {
                $nextQueueNumber = intval($rowMax['max_queue']) + 1;
            }
        }

        $sqlInsert = "INSERT INTO queue (`Queue #`, Name, Email) VALUES (?, ?, ?)";
        $stmt = $this->conn->prepare($sqlInsert);
        if (!$stmt) {
            error_log("Prepare failed for addUserToQueue (insert): (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }
        $stmt->bind_param("iss", $nextQueueNumber, $name, $email);
        if ($stmt->execute()) {
            $stmt->close();
            return $nextQueueNumber; // Return the assigned queue number on success
        } else {
            error_log("Execute failed for addUserToQueue: (" . $stmt->errno . ") " . $stmt->error);
            $stmt->close();
            return false;
        }
    }
}

function getUserData() {
    // Default empty user array
    $user = [
        'first_name' => '',
        'last_name' => '',
        'phone' => '',
        'email' => '',
        'address' => '',
        'city' => ''
    ];

    // Check if user is logged in
    if (isset($_SESSION['user_email'])) {
        $userObj = new User();
        $conn = $userObj->getConnection();
        $email = $_SESSION['user_email'];

        // Adjust fields as per your database
        $sql = "SELECT name, email, phone, address, city FROM user_records WHERE email = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("s", $email);
        $stmt->execute();
        $stmt->bind_result($name, $email, $phone, $address, $city);

        if ($stmt->fetch()) {
            // Optionally split name if needed
            $parts = explode(" ", $name, 2);
            $user['first_name'] = $parts[0];
            $user['last_name'] = isset($parts[1]) ? $parts[1] : '';
            $user['email'] = $email;
            $user['phone'] = $phone;
            $user['address'] = $address;
            $user['city'] = $city;
        }

        $stmt->close();
        $userObj->closeConnection();
    }

    return $user;
}