<?php
namespace App\Models;
use App\Models\BaseModel;
/**
* User Model
* Handles user data, profile management, and user-related operations
*/
class User extends BaseModel
{
protected string $table = 'users';
protected array $fillable = [
'email', 'password_hash', 'first_name', 'last_name', 'phone',
'date_of_birth', 'gender', 'role', 'email_verified',
'email_verification_token', 'password_reset_token', 'password_reset_expires',
'is_active'
];
protected array $hidden = [
'password_hash', 'password_reset_token', 'email_verification_token',
'login_attempts', 'locked_until'
];
protected array $casts = [
'email_verified' => 'boolean',
'is_active' => 'boolean',
'login_attempts' => 'integer'
];
/**
* Find user by email
*/
public function findByEmail(string $email): ?array
{
return $this->db->queryOne(
"SELECT * FROM users WHERE email = ?",
[$email]
);
}
/**
* Get user by email (alias for findByEmail)
*/
public function getByEmail(string $email): ?array
{
return $this->findByEmail($email);
}
/**
* Verify user password
*/
public function verifyPassword(int $userId, string $password): bool
{
$user = $this->db->queryOne(
"SELECT password_hash FROM users WHERE id = ?",
[$userId]
);
if (!$user) {
return false;
}
return password_verify($password, $user['password_hash']);
}
/**
* Get user profile with additional information
*/
public function getProfile(int $userId): ?array
{
$user = $this->find($userId);
if (!$user) {
return null;
}
// Remove sensitive fields
foreach ($this->hidden as $field) {
unset($user[$field]);
}
// Cast attributes
$user = $this->castAttributes($user);
// Add additional profile information
$user['total_orders'] = $this->getTotalOrders($userId);
$user['total_spent'] = $this->getTotalSpent($userId);
$user['wishlist_count'] = $this->getWishlistCount($userId);
$user['addresses_count'] = $this->getAddressesCount($userId);
return $user;
}
/**
* Update user profile
*/
public function updateProfile(int $userId, array $data): bool
{
// Filter allowed fields
$allowedFields = ['first_name', 'last_name', 'phone', 'date_of_birth', 'gender'];
$updateData = array_intersect_key($data, array_flip($allowedFields));
if (empty($updateData)) {
return true;
}
$updated = $this->db->execute(
"UPDATE users SET ".
implode(' = ?, ', array_keys($updateData))." = ?, updated_at = NOW() ".
"WHERE id = ?",
array_merge(array_values($updateData), [$userId])
);
return $updated > 0;
}
/**
* Get user's total orders count
*/
private function getTotalOrders(int $userId): int
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as total FROM orders WHERE user_id = ?",
[$userId]
);
return (int) ($result['total'] ?? 0);
}
/**
* Get user's total spent amount
*/
private function getTotalSpent(int $userId): float
{
$result = $this->db->queryOne(
"SELECT SUM(total_amount) as total
FROM orders
WHERE user_id = ? AND payment_status = 'paid'",
[$userId]
);
return (float) ($result['total'] ?? 0);
}
/**
* Get user's wishlist count
*/
private function getWishlistCount(int $userId): int
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as total FROM wishlists WHERE user_id = ?",
[$userId]
);
return (int) ($result['total'] ?? 0);
}
/**
* Get user's addresses count
*/
private function getAddressesCount(int $userId): int
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as total FROM user_addresses WHERE user_id = ?",
[$userId]
);
return (int) ($result['total'] ?? 0);
}
/**
* Get user's wishlist with product details
*/
public function getWishlist(int $userId): array
{
return $this->db->query(
"SELECT w.*, p.name, p.slug, p.base_price, p.compare_price, p.status,
(SELECT filename FROM product_images pi
WHERE pi.product_id = p.id AND pi.is_primary = 1 LIMIT 1) as primary_image
FROM wishlists w
JOIN products p ON w.product_id = p.id
WHERE w.user_id = ? AND p.status = 'active'
ORDER BY w.created_at DESC",
[$userId]
);
}
/**
* Add product to wishlist
*/
public function addToWishlist(int $userId, int $productId): bool
{
// Check if already in wishlist
$existing = $this->db->queryOne(
"SELECT id FROM wishlists WHERE user_id = ? AND product_id = ?",
[$userId, $productId]
);
if ($existing) {
return false; // Already in wishlist
}
// Add to wishlist
$this->db->execute(
"INSERT INTO wishlists (user_id, product_id, created_at) VALUES (?, ?, NOW())",
[$userId, $productId]
);
return true;
}
/**
* Remove product from wishlist
*/
public function removeFromWishlist(int $userId, int $productId): bool
{
$deleted = $this->db->execute(
"DELETE FROM wishlists WHERE user_id = ? AND product_id = ?",
[$userId, $productId]
);
return $deleted > 0;
}
/**
* Check if product is in user's wishlist
*/
public function isInWishlist(int $userId, int $productId): bool
{
$result = $this->db->queryOne(
"SELECT COUNT(*) as count FROM wishlists WHERE user_id = ? AND product_id = ?",
[$userId, $productId]
);
return (int) ($result['count'] ?? 0) > 0;
}
/**
* Get user's order history
*/
public function getOrderHistory(int $userId, int $limit = 20, int $offset = 0): array
{
$orders = $this->db->query(
"SELECT o.*,
COUNT(oi.id) as item_count,
SUM(oi.quantity) as total_items
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = ?
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT ? OFFSET ?",
[$userId, $limit, $offset]
);
// Get total count
$totalResult = $this->db->queryOne(
"SELECT COUNT(*) as total FROM orders WHERE user_id = ?",
[$userId]
);
return [
'orders' => $orders,
'total' => (int) ($totalResult['total'] ?? 0)
];
}
/**
* Get user's recent activity
*/
public function getRecentActivity(int $userId, int $limit = 10): array
{
return $this->db->query(
"SELECT 'order' as type, id, order_number as reference, status, created_at
FROM orders
WHERE user_id = ?
UNION ALL
SELECT 'wishlist' as type, w.id, p.name as reference, 'added' as status, w.created_at
FROM wishlists w
JOIN products p ON w.product_id = p.id
WHERE w.user_id = ?
ORDER BY created_at DESC
LIMIT ?",
[$userId, $userId, $limit]
);
}
/**
* Update user's last login
*/
public function updateLastLogin(int $userId): bool
{
$updated = $this->db->execute(
"UPDATE users SET last_login = NOW() WHERE id = ?",
[$userId]
);
return $updated > 0;
}
/**
* Activate user account
*/
public function activate(int $userId): bool
{
$updated = $this->db->execute(
"UPDATE users SET is_active = 1, updated_at = NOW() WHERE id = ?",
[$userId]
);
return $updated > 0;
}
/**
* Deactivate user account
*/
public function deactivate(int $userId): bool
{
$updated = $this->db->execute(
"UPDATE users SET is_active = 0, updated_at = NOW() WHERE id = ?",
[$userId]
);
return $updated > 0;
}
/**
* Verify user's email
*/
public function verifyEmail(int $userId): bool
{
$updated = $this->db->execute(
"UPDATE users SET email_verified = 1, email_verification_token = NULL, updated_at = NOW() WHERE id = ?",
[$userId]
);
return $updated > 0;
}
/**
* Set email verification token
*/
public function setEmailVerificationToken(int $userId, string $token): bool
{
$updated = $this->db->execute(
"UPDATE users SET email_verification_token = ?, updated_at = NOW() WHERE id = ?",
[$token, $userId]
);
return $updated > 0;
}
/**
* Set password reset token
*/
public function setPasswordResetToken(int $userId, string $token, string $expires): bool
{
$updated = $this->db->execute(
"UPDATE users SET password_reset_token = ?, password_reset_expires = ?, updated_at = NOW() WHERE id = ?",
[$token, $expires, $userId]
);
return $updated > 0;
}
/**
* Clear password reset token
*/
public function clearPasswordResetToken(int $userId): bool
{
$updated = $this->db->execute(
"UPDATE users SET password_reset_token = NULL, password_reset_expires = NULL, updated_at = NOW() WHERE id = ?",
[$userId]
);
return $updated > 0;
}
/**
* Update password
*/
public function updatePassword(int $userId, string $passwordHash): bool
{
$updated = $this->db->execute(
"UPDATE users SET password_hash = ?, login_attempts = 0, locked_until = NULL, updated_at = NOW() WHERE id = ?",
[$passwordHash, $userId]
);
return $updated > 0;
}
/**
* Increment login attempts
*/
public function incrementLoginAttempts(string $email): bool
{
$updated = $this->db->execute(
"UPDATE users
SET login_attempts = login_attempts + 1,
locked_until = CASE
WHEN login_attempts >= 4 THEN DATE_ADD(NOW(), INTERVAL 30 MINUTE)
ELSE locked_until
END,
updated_at = NOW()
WHERE email = ?",
[$email]
);
return $updated > 0;
}
/**
* Reset login attempts
*/
public function resetLoginAttempts(int $userId): bool
{
$updated = $this->db->execute(
"UPDATE users SET login_attempts = 0, locked_until = NULL, updated_at = NOW() WHERE id = ?",
[$userId]
);
return $updated > 0;
}
/**
* Check if user is locked
*/
public function isLocked(string $email): bool
{
$user = $this->db->queryOne(
"SELECT login_attempts, locked_until FROM users WHERE email = ?",
[$email]
);
if (!$user) {
return false;
}
// Check if account is locked
if ($user['locked_until'] && strtotime($user['locked_until']) > time()) {
return true;
}
// Check if too many attempts
return $user['login_attempts'] >= 5;
}
/**
* Get users with pagination (for admin)
*/
public function getUsers(array $filters = [], int $limit = 20, int $offset = 0): array
{
$where = ['1 = 1'];
$params = [];
// Role filter
if (!empty($filters['role'])) {
$where[] = 'role = ?';
$params[] = $filters['role'];
}
// Status filter
if (isset($filters['is_active'])) {
$where[] = 'is_active = ?';
$params[] = $filters['is_active'] ? 1 : 0;
}
// Email verified filter
if (isset($filters['email_verified'])) {
$where[] = 'email_verified = ?';
$params[] = $filters['email_verified'] ? 1 : 0;
}
// Search filter
if (!empty($filters['search'])) {
$where[] = '(first_name LIKE ? OR last_name LIKE ? OR email LIKE ?)';
$searchTerm = '%'.$filters['search'].'%';
$params[] = $searchTerm;
$params[] = $searchTerm;
$params[] = $searchTerm;
}
$whereClause = implode(' AND ', $where);
// Get total count
$countSql = "SELECT COUNT(*) as total FROM users WHERE $whereClause";
$totalResult = $this->db->queryOne($countSql, $params);
$total = (int) ($totalResult['total'] ?? 0);
// Get users
$sql = "SELECT id, email, first_name, last_name, phone, role, email_verified, is_active, last_login, created_at
FROM users
WHERE $whereClause
ORDER BY created_at DESC
LIMIT ? OFFSET ?";
$params[] = $limit;
$params[] = $offset;
$users = $this->db->query($sql, $params);
return [
'users' => $users,
'total' => $total
];
}
/**
* Get user statistics (for admin dashboard)
*/
public function getUserStats(): array
{
$stats = [];
// Total users
$result = $this->db->queryOne("SELECT COUNT(*) as total FROM users");
$stats['total_users'] = (int) ($result['total'] ?? 0);
// Active users
$result = $this->db->queryOne("SELECT COUNT(*) as total FROM users WHERE is_active = 1");
$stats['active_users'] = (int) ($result['total'] ?? 0);
// Verified users
$result = $this->db->queryOne("SELECT COUNT(*) as total FROM users WHERE email_verified = 1");
$stats['verified_users'] = (int) ($result['total'] ?? 0);
// New users this month
$result = $this->db->queryOne("SELECT COUNT(*) as total FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)");
$stats['new_users_this_month'] = (int) ($result['total'] ?? 0);
// Users by role
$roleStats = $this->db->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
$stats['users_by_role'] = [];
foreach ($roleStats as $roleStat) {
$stats['users_by_role'][$roleStat['role']] = (int) $roleStat['count'];
}
return $stats;
}
}