alrahma_sunday_school/app/Models/UserModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class UserModel extends Model
{
    protected $table = 'users'; // Define the table associated with this model
    protected $primaryKey = 'id'; // Define the primary key of the table
    protected $allowedFields = [
        'password',
        'account_id',
        'lastname',
        'firstname',
        'gender',
        'cellphone',
        'email',
        'address_street',
        'apt',
        'city',
        'state',
        'zip',
        'accept_school_policy',
        'user_type',
        'rfid_tag',
        'school_id',
        'failed_attempts',
        'last_failed_at',
        'semester',
        'school_year',
        'status',
        'is_suspended',
        'is_verified',
        'token',
        'updated_at',
        'created_at',
    ];

    protected $returnType = 'array'; // Specify the return type of the results
    protected $useTimestamps = true; // Enable automatic timestamps
    protected $createdField = 'created_at'; // Define the field name for the created timestamp
    protected $updatedField = 'updated_at'; // Define the field name for the updated timestamp

    // Existing methods remain unchanged

    /**
     * Get all users with their roles.
     */
    public function getUsersWithRoles($sort = 'id', $order = 'asc')
    {
        return $this->select('users.id, users.firstname, users.lastname, users.email, roles.name as role')
            ->join('user_roles', 'users.id = user_roles.user_id', 'left')
            ->join('roles', 'user_roles.role_id = roles.id', 'left')
            ->orderBy($sort, $order)
            ->findAll();
    }

    /**
     * Get unverified users created more than 2 minutes ago.
     */
    public function getUnverifiedUsers()
    {
        return $this->where('is_verified', 0)
            ->where('created_at <', date('Y-m-d H:i:s', time() - 120))
            ->findAll();
    }

    /**
     * Retrieve all users with optional sorting and filtering.
     */
    public function findAll($limit = 0, $offset = 0, $sort = 'id', $order = 'asc', $conditions = [])
    {
        $builder = $this->builder();

        // Apply additional conditions
        if (!empty($conditions)) {
            $builder->where($conditions);
        }

        // Apply sorting
        $builder->orderBy($sort, $order);

        // Apply limit and offset if provided
        if ($limit > 0) {
            $builder->limit($limit, $offset);
        }

        return $builder->get()->getResultArray();
    }

    /**
     * Validate registration input data.
     */
    public function validateRegistrationInput($data)
    {
        $errors = [];

        // Required fields validation
        $required_fields = ['password', 'lastname', 'firstname', 'cellphone', 'email', 'address_street', 'city', 'state', 'zip', 'accept_school_policy'];
        foreach ($required_fields as $field) {
            if (empty($data[$field])) {
                $errors[$field][] = ucfirst($field) . ' is required.';
            }
        }

        // Password validation
        if (!empty($data['password'])) {
            if (strlen($data['password']) < 8) {
                $errors['password'][] = 'Password must be at least 8 characters long.';
            }
            if (!preg_match('/[A-Z]/', $data['password'])) {
                $errors['password'][] = 'Password must contain at least one uppercase letter.';
            }
            if (!preg_match('/[a-z]/', $data['password'])) {
                $errors['password'][] = 'Password must contain at least one lowercase letter.';
            }
            if (!preg_match('/[0-9]/', $data['password'])) {
                $errors['password'][] = 'Password must contain at least one number.';
            }
            if (!preg_match('/[\W]/', $data['password'])) {
                $errors['password'][] = 'Password must contain at least one special character.';
            }
        }

        // Email validation
        if (!empty($data['email']) && !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
            $errors['email'][] = 'Invalid email format.';
        }

        // Phone validation (basic)
        if (!empty($data['cellphone']) && !preg_match('/^\d{10}$/', $data['cellphone'])) {
            $errors['cellphone'][] = 'Cellphone must be a 10-digit number.';
        }

        // Zip code validation
        if (!empty($data['zip']) && !preg_match('/^\d{5}(-\d{4})?$/', $data['zip'])) {
            $errors['zip'][] = 'Invalid zip code format.';
        }

        // State validation (example: 2-letter state code)
        if (!empty($data['state']) && !preg_match('/^[A-Z]{2}$/', $data['state'])) {
            $errors['state'][] = 'Invalid state code.';
        }

        // Accept school policy validation
        if (!isset($data['accept_school_policy']) || $data['accept_school_policy'] != '1') {
            $errors['accept_school_policy'][] = 'You must accept the school policy.';
        }

        return $errors;
    }

    /**
     * Get users by role.
     */
    /*
    public function getUsersByRole($role)
    {
        // Join with user_roles and roles tables to fetch users by role
        return $this->select('users.*, roles.name as role')
            ->join('user_roles', 'user_roles.user_id = users.id')
            ->join('roles', 'roles.id = user_roles.role_id')
            ->where('roles.name', $role)
            ->findAll();
    }*/

    /**
     * Get user's role name
     * 
     * @param int $user_id User ID
     * @return string|null Role name or null if not found
     */
    public function getUserRole($user_id)
    {
        $result = $this->select('roles.name')
            ->join('user_roles', 'user_roles.user_id = users.id')
            ->join('roles', 'roles.id = user_roles.role_id')
            ->where('users.id', $user_id)
            ->first();

        return $result ? $result['name'] : null;
    }
    /**
     * Get all users.
     */
    public function getAllUsers($sort = 'id', $order = 'asc')
    {
        return $this->select('users.*') // Select only the fields from the users table
            ->orderBy($sort, $order)
            ->findAll();
    }

    public function getUserInfoById($userId)
    {
        // Define a list of fields to return (excluding sensitive ones)
        $safeFields = array_filter($this->allowedFields, function ($field) {
            return !in_array($field, ['password', 'token', 'is_verified']);
        });

        // Get the main user information
        $user = $this->select($safeFields)
            ->where('id', $userId)
            ->first();

        if (!$user) {
            return null;
        }

        // Load the database connection
        $db = \Config\Database::connect();

        // Build query to join user_roles and roles
        $builder = $db->table('user_roles');
        $builder->select('roles.id, roles.name');
        $builder->join('roles', 'roles.id = user_roles.role_id');
        $builder->where('user_roles.user_id', $userId);
        $builder->where('user_roles.deleted_at', null);

        $roles = $builder->get();

        // Add roles to user array
        $user['roles'] = $roles;

        return $user;
    }

    public function getUsersByRole(string $roleName): array
    {
        return $this->select('users.*')
            ->join('user_roles', 'user_roles.user_id = users.id')
            ->join('roles', 'roles.id = user_roles.role_id')
            ->where('roles.name', $roleName)
            ->where('user_roles.deleted_at', null) // if you're using soft deletes
            ->findAll();
    }

    public function getUsersByRoleAndSchoolYear(string $roleName, string $schoolYear): array
    {
        return $this->select('users.*')
            ->join('user_roles', 'user_roles.user_id = users.id')
            ->join('roles', 'roles.id = user_roles.role_id')
            ->where('roles.name', $roleName)
            ->where('users.school_year', $schoolYear)
            ->where('user_roles.deleted_at', null)
            ->findAll();
    }

    public function countAdminsBySchoolYear(string $schoolYear): int
    {
        // Slugs to exclude from “admin”
        $excluded = ['guest', 'teacher', 'teacher_assistant', 'parent'];

        // Build a grouped query that flags whether each user has ANY non-excluded role
        $builder = $this->db->table('users u')
            ->select(
                // is_admin = 1 if the user has at least one role NOT in the excluded set
                'u.id, MAX(CASE WHEN r.slug NOT IN("guest","teacher","teacher_assistant","parent") THEN 1 ELSE 0 END) AS is_admin',
                false // don't escape the CASE expression
            )
            ->join('user_roles ur', 'ur.user_id = u.id', 'inner')
            ->join('roles r', 'r.id = ur.role_id', 'inner')
            ->where('u.school_year', $schoolYear)
            ->where('r.is_active', 1)
            ->groupBy('u.id')
            ->having('is_admin', 1);

        // Wrap as subquery to count rows without fetching them all
        $subSql = $builder->getCompiledSelect();
        $row    = $this->db->query("SELECT COUNT(*) AS total FROM ({$subSql}) t")->getRowArray();

        return (int)($row['total'] ?? 0);
    }



    public function getSchoolIdByUserId(int $userId): ?int
    {
        $result = $this->select('school_id')
            ->where('id', $userId)
            ->first();

        return $result['school_id'] ?? null;
    }

    public function getParents(): array
    {
        $db = \Config\Database::connect();

        // Get parent role ID
        $parentRole = $db->table('roles')
            ->select('id')
            ->where('LOWER(name)', 'parent')  // case-insensitive match
            ->get()
            ->getRow();

        if (!$parentRole) {
            return [];  // No parent role found
        }

        // Get users with parent role
        return $this->select('users.id, users.firstname, users.lastname, users.email, users.school_id')
            ->join('user_roles', 'user_roles.user_id = users.id')
            ->where('user_roles.role_id', $parentRole->id)
            ->groupBy('users.id, users.firstname, users.lastname, users.email, users.school_id') // group by all selected fields (for strict SQL modes)
            ->findAll();
    }


    public function getTeacher($user_id)
    {
        return $this->db->table($this->table)
            ->select('users.firstname AS teacher_first, users.lastname AS teacher_last')
            ->join('user_roles', 'user_roles.user_id = users.id')
            ->join('roles', 'roles.id = user_roles.role_id')
            ->where('users.id', $user_id)
            ->where('LOWER(roles.name) =', 'teacher')  // case-insensitive match
            ->get()
            ->getRowArray();
    }

    public function getUsersBySchoolId($schoolId)
    {
        return $this->where('school_id', $schoolId)
            ->orderBy('lastname', 'ASC')
            ->findAll();
    }
    public function getNoParentUsersWithRole(
        ?string $schoolYear = null,
        array $selectedUserIds = [],
        string $sort = 'users.lastname',
        string $order = 'asc'
    ): array {
        $order = (strtolower($order) === 'desc') ? 'DESC' : 'ASC';

        // Whitelist sort fields
        $allowedSort = ['users.id', 'users.firstname', 'users.lastname', 'users.email', 'roles'];
        if (!in_array($sort, $allowedSort, true)) {
            $sort = 'users.lastname';
        }

        $builder = $this->select("
        users.id AS id,
        users.firstname,
        users.lastname,
        users.email,
        GROUP_CONCAT(DISTINCT r.name ORDER BY r.name SEPARATOR ', ') AS roles
    ")
            ->join('user_roles ur', 'users.id = ur.user_id', 'inner')
            ->join('roles r', 'ur.role_id = r.id', 'inner')
            // drop parent and guest rows; users with only those vanish
            ->whereNotIn('r.name', ['parent', 'guest']);


        if (!empty($selectedUserIds)) {
            $builder->whereIn('users.id', $selectedUserIds);
        }

        if (!empty($schoolYear)) {
            // Prefer user_roles.school_year if present (true year-scoped roles)
            $userRolesFields = [];
            try {
                $userRolesFields = $this->db->getFieldNames('user_roles');
            } catch (\Throwable $e) {
                // ignore
            }

            if (in_array('school_year', $userRolesFields, true)) {
                $builder->where('ur.school_year', $schoolYear);
            } else {
                // Fallback: role-aware filter
                $escYear = $this->db->escape($schoolYear);

                // Include user if:
                //  (A) They are assigned as teacher/TA in teacher_class for that year
                //   OR
                //  (B) They have at least one non-teacher-ish (and non-parent) role (admin/staff/etc.)
                //
                // Teacher-ish detection: name contains 'teacher' OR equals 'ta'
                $builder->groupStart()
                    // A) has teacher assignment that year
                    ->where("
                    EXISTS (
                        SELECT 1
                        FROM teacher_class tc
                        WHERE tc.teacher_id = users.id
                          AND tc.school_year = {$escYear}
                    )
                ", null, false)
                    // B) OR has any non-teacher-ish, non-parent role
                    ->orWhere("
                    EXISTS (
                        SELECT 1
                        FROM user_roles ur2
                        JOIN roles r2 ON r2.id = ur2.role_id
                        WHERE ur2.user_id = users.id
                          AND LOWER(r2.name) NOT IN ('parent','ta')
                          AND LOWER(r2.name) NOT LIKE '%teacher%'
                    )
                ", null, false)
                    ->groupEnd();
            }
        }

        return $builder
            ->groupBy('users.id')
            ->orderBy($sort === 'roles' ? 'roles' : $sort, $order)
            ->findAll();
    }
}