<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Schema;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable
{
    use HasApiTokens;
    use HasFactory;

    protected $table = 'users';

    protected $fillable = [
        'school_id',
        'firstname',
        'lastname',
        'gender',
        'cellphone',
        'email',
        'address_street',
        'apt',
        'city',
        'state',
        'zip',
        'accept_school_policy',
        'is_verified',
        'status',
        'is_suspended',
        'failed_attempts',
        'password',
        'created_at',
        'updated_at',
        'token',
        'account_id',
        'user_type',
        'semester',
        'school_year',
        'rfid_tag',
        'last_failed_at',
    ];

    protected $hidden = [
        'password',
        'token',
        'remember_token',
    ];

    protected $casts = [
        'school_id'             => 'integer',
        'failed_attempts'       => 'integer',
        'last_failed_at'        => 'datetime',
        'accept_school_policy'  => 'boolean',
        'is_suspended'          => 'boolean',
        'is_verified'           => 'boolean',
        'created_at'            => 'datetime',
        'updated_at'            => 'datetime',
    ];

    /* ============================================================
     * Relationships
     * ============================================================
     */

    public function roles(): BelongsToMany
    {
        // If user_roles has soft-deletes (deleted_at), filter it out.
        // If it does not, you can remove wherePivotNull('deleted_at').
        $relation = $this->belongsToMany(Role::class, 'user_roles', 'user_id', 'role_id')
            ->withTimestamps();

        $pivotColumns = [];
        if (Schema::hasColumn('user_roles', 'deleted_at')) {
            $pivotColumns[] = 'deleted_at';
            $relation->wherePivotNull('deleted_at');
        }
        if (Schema::hasColumn('user_roles', 'school_year')) {
            $pivotColumns[] = 'school_year';
        }

        if (!empty($pivotColumns)) {
            $relation->withPivot($pivotColumns);
        }

        return $relation;
    }

    public function teacherClasses(): HasMany
    {
        return $this->hasMany(TeacherClass::class, 'teacher_id');
    }

    /* ============================================================
     * Scopes: role filters
     * ============================================================
     */

    /**
     * Users who have a role name matching (case-insensitive) the given role name.
     * Uses EXISTS to avoid duplicates when user has multiple roles.
     */
    public function scopeHasRoleName(Builder $q, string $roleName): Builder
    {
        $roleName = strtolower(trim($roleName));

        return $q->whereExists(function ($sub) use ($roleName) {
            $sub->selectRaw('1')
                ->from('user_roles as ur')
                ->join('roles as r', 'r.id', '=', 'ur.role_id')
                ->whereColumn('ur.user_id', 'users.id')
                ->whereNull('ur.deleted_at')
                ->whereRaw('LOWER(r.name) = ?', [$roleName])
                ->where('r.is_active', 1);
        });
    }

    /**
     * Users who have ANY role in the provided set (case-insensitive).
     */
    public function scopeHasAnyRoleNames(Builder $q, array $roleNames): Builder
    {
        $names = array_values(array_unique(array_filter(array_map(
            fn($v) => strtolower(trim((string)$v)),
            $roleNames
        ))));

        if (empty($names)) {
            return $q->whereRaw('1=0');
        }

        // MySQL: LOWER(r.name) IN (...)
        $placeholders = implode(',', array_fill(0, count($names), '?'));

        return $q->whereExists(function ($sub) use ($names, $placeholders) {
            $sub->selectRaw('1')
                ->from('user_roles as ur')
                ->join('roles as r', 'r.id', '=', 'ur.role_id')
                ->whereColumn('ur.user_id', 'users.id')
                ->whereNull('ur.deleted_at')
                ->whereRaw("LOWER(r.name) IN ($placeholders)", $names)
                ->where('r.is_active', 1);
        });
    }

    /** Teachers only (TeacherModel behavior) */
    public function scopeTeachers(Builder $q): Builder
    {
        return $q->hasRoleName('teacher');
    }

    /** Teachers + Teacher Assistants (TeacherModel behavior) */
    public function scopeTeachersAndTAs(Builder $q): Builder
    {
        return $q->hasAnyRoleNames(['teacher', 'teacher_assistant']);
    }

    /* ============================================================
     * Basic getters (UserModel behavior)
     * ============================================================
     */

    /**
     * CI: getUsersWithRoles($sort='id',$order='asc')
     * Returns rows: users.id, firstname, lastname, email, roles.name as role
     * NOTE: If user has multiple roles, this will return multiple rows (same as CI join).
     */
    public static function getUsersWithRoles(string $sort = 'users.id', string $order = 'asc'): array
    {
        $order = strtolower($order) === 'desc' ? 'desc' : 'asc';

        // whitelist to prevent SQL injection
        $allowedSort = ['users.id','users.firstname','users.lastname','users.email','role'];
        if (!in_array($sort, $allowedSort, true)) $sort = 'users.id';

        $q = DB::table('users')
            ->select('users.id', 'users.firstname', 'users.lastname', 'users.email', DB::raw('roles.name as role'))
            ->leftJoin('user_roles', 'users.id', '=', 'user_roles.user_id')
            ->leftJoin('roles', 'user_roles.role_id', '=', 'roles.id');

        // sorting "role" alias requires orderByRaw
        if ($sort === 'role') {
            $q->orderByRaw('role ' . strtoupper($order));
        } else {
            $q->orderBy($sort, $order);
        }

        return $q->get()->map(fn($r) => (array) $r)->all();
    }

    /**
     * CI: getUnverifiedUsers() created more than 2 minutes ago
     */
    public static function getUnverifiedUsers(): array
    {
        return static::query()
            ->where('is_verified', 0)
            ->where('created_at', '<', now()->subSeconds(120))
            ->get()
            ->all();
    }

    /**
     * CI: findAll($limit,$offset,$sort,$order,$conditions)
     * Laravel version: returns array of rows.
     */
    public static function findAllCustom(
        int $limit = 0,
        int $offset = 0,
        string $sort = 'users.id',
        string $order = 'asc',
        array $conditions = []
    ): array {
        $order = strtolower($order) === 'desc' ? 'desc' : 'asc';

        // whitelist sort fields
        $allowedSort = [
            'users.id','users.firstname','users.lastname','users.email','users.cellphone',
            'users.school_year','users.status','users.created_at','users.updated_at'
        ];
        if (!in_array($sort, $allowedSort, true)) $sort = 'users.id';

        $q = static::query();

        if (!empty($conditions)) {
            $q->where($conditions);
        }

        $q->orderBy($sort, $order);

        if ($limit > 0) {
            $q->skip($offset)->take($limit);
        }

        return $q->get()->map(fn($u) => $u->toArray())->all();
    }

    /**
     * CI: getUserRole($user_id) -> string|null
     * Returns one role name (MIN for stability).
     */
    public static function getUserRoleName(int $userId): ?string
    {
        return DB::table('roles as r')
            ->join('user_roles as ur', 'ur.role_id', '=', 'r.id')
            ->where('ur.user_id', $userId)
            ->whereNull('ur.deleted_at')
            ->orderBy('r.priority', 'asc')
            ->orderBy('r.name', 'asc')
            ->value('r.name');
    }

    /**
     * CI: getAllUsers($sort,$order)
     */
    public static function getAllUsers(string $sort = 'users.id', string $order = 'asc')
    {
        $order = strtolower($order) === 'desc' ? 'desc' : 'asc';
        return static::query()->orderBy($sort, $order)->get();
    }

    /**
     * CI: getUserInfoById($userId) but safe fields (exclude password/token/is_verified)
     * Returns array with roles.
     */
    public static function getUserInfoById(int $userId): ?array
    {
        $user = static::query()
            ->select([
                'id','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','created_at','updated_at',
            ])
            ->where('id', $userId)
            ->first();

        if (!$user) return null;

        $roles = DB::table('user_roles as ur')
            ->select('r.id', 'r.name', 'r.slug')
            ->join('roles as r', 'r.id', '=', 'ur.role_id')
            ->where('ur.user_id', $userId)
            ->whereNull('ur.deleted_at')
            ->get()
            ->map(fn($r) => (array) $r)
            ->all();

        $arr = $user->toArray();
        $arr['roles'] = $roles;

        return $arr;
    }

    /**
     * CI: getUsersByRole($roleName)
     */
    public static function getUsersByRole(string $roleName): array
    {
        return DB::table('users')
            ->select('users.*')
            ->join('user_roles', 'user_roles.user_id', '=', 'users.id')
            ->join('roles', 'roles.id', '=', 'user_roles.role_id')
            ->where('roles.name', $roleName)
            ->whereNull('user_roles.deleted_at')
            ->get()
            ->map(fn($r) => (array) $r)
            ->all();
    }

    /**
     * CI: getUsersByRoleAndSchoolYear($roleName, $schoolYear)
     */
    public static function getUsersByRoleAndSchoolYear(string $roleName, string $schoolYear): array
    {
        return DB::table('users')
            ->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)
            ->whereNull('user_roles.deleted_at')
            ->get()
            ->map(fn($r) => (array) $r)
            ->all();
    }

    /**
     * CI: countAdminsBySchoolYear($schoolYear)
     * Excludes guest/teacher/teacher_assistant/parent.
     */
    public static function countAdminsBySchoolYear(string $schoolYear): int
    {
        // grouped query that flags whether each user has ANY non-excluded role
        $sub = DB::table('users as u')
            ->selectRaw('u.id, MAX(CASE WHEN r.slug NOT IN ("guest","teacher","teacher_assistant","parent") THEN 1 ELSE 0 END) as is_admin')
            ->join('user_roles as ur', 'ur.user_id', '=', 'u.id')
            ->join('roles as r', 'r.id', '=', 'ur.role_id')
            ->where('u.school_year', $schoolYear)
            ->where('r.is_active', 1)
            ->whereNull('ur.deleted_at')
            ->groupBy('u.id')
            ->having('is_admin', 1);

        return DB::query()->fromSub($sub, 't')->count();
    }

    public static function getSchoolIdByUserId(int $userId): ?int
    {
        $v = static::query()->where('id', $userId)->value('school_id');
        return $v === null ? null : (int) $v;
    }

    /**
     * CI: getParents()
     * Finds role "parent" case-insensitively then returns grouped users.
     */
    public static function getParents(): array
    {
        $parentRoleId = DB::table('roles')
            ->whereRaw('LOWER(name) = ?', ['parent'])
            ->value('id');

        if (!$parentRoleId) return [];

        return DB::table('users')
            ->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', $parentRoleId)
            ->whereNull('user_roles.deleted_at')
            ->groupBy('users.id', 'users.firstname', 'users.lastname', 'users.email', 'users.school_id')
            ->get()
            ->map(fn($r) => (array) $r)
            ->all();
    }

    /**
     * CI: getTeacher($user_id) -> teacher_first/teacher_last or null
     */
    public static function getTeacherNameParts(int $userId): ?array
    {
        $row = DB::table('users')
            ->selectRaw('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', $userId)
            ->whereRaw('LOWER(roles.name) = ?', ['teacher'])
            ->whereNull('user_roles.deleted_at')
            ->first();

        return $row ? (array) $row : null;
    }

    public static function getUsersBySchoolId(int $schoolId): array
    {
        return static::query()
            ->where('school_id', $schoolId)
            ->orderBy('lastname', 'asc')
            ->get()
            ->all();
    }

    /**
     * CI: getNoParentUsersWithRole(...) (as close as possible)
     * Returns: id, firstname, lastname, email, roles (comma string)
     */
    public static 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';

        $q = DB::table('users')
            ->selectRaw("
                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 as ur', 'users.id', '=', 'ur.user_id')
            ->join('roles as r', 'ur.role_id', '=', 'r.id')
            ->whereNull('ur.deleted_at')
            ->whereNotIn(DB::raw('LOWER(r.name)'), ['parent', 'guest']); // drop parent/guest

        if (!empty($selectedUserIds)) {
            $ids = array_values(array_unique(array_filter(array_map('intval', $selectedUserIds))));
            if (!empty($ids)) $q->whereIn('users.id', $ids);
        }

        if (!empty($schoolYear)) {
            // Prefer ur.school_year if it exists (role is year-scoped)
            $hasUrSchoolYear = false;
            try {
                $cols = DB::getSchemaBuilder()->getColumnListing('user_roles');
                $hasUrSchoolYear = in_array('school_year', $cols, true);
            } catch (\Throwable $e) {
                $hasUrSchoolYear = false;
            }

            if ($hasUrSchoolYear) {
                $q->where('ur.school_year', $schoolYear);
            } else {
                $escYear = DB::getPdo()->quote($schoolYear);

                $q->where(function ($w) use ($escYear) {
                    // A) teacher assignment that year
                    $w->whereRaw("
                        EXISTS (
                            SELECT 1
                            FROM teacher_class tc
                            WHERE tc.teacher_id = users.id
                              AND tc.school_year = {$escYear}
                        )
                    ");

                    // B) OR has any non-teacher-ish, non-parent role
                    $w->orWhereRaw("
                        EXISTS (
                            SELECT 1
                            FROM user_roles ur2
                            JOIN roles r2 ON r2.id = ur2.role_id
                            WHERE ur2.user_id = users.id
                              AND ur2.deleted_at IS NULL
                              AND LOWER(r2.name) NOT IN ('parent','ta')
                              AND LOWER(r2.name) NOT LIKE '%teacher%'
                        )
                    ");
                });
            }
        }

        $q->groupBy('users.id');

        if ($sort === 'roles') {
            $q->orderByRaw("roles {$order}");
        } else {
            $q->orderBy($sort, strtolower($order));
        }

        return $q->get()->map(fn($r) => (array) $r)->all();
    }

    /* ============================================================
     * TeacherModel logic merged in
     * ============================================================
     */

    /**
     * TeacherModel: getAllTeachers()
     * Returns users who have role "teacher" (case-insensitive).
     */
    public static function getAllTeachers(): array
    {
        return static::query()
            ->select('users.id', 'users.firstname', 'users.lastname', 'users.email', 'users.cellphone')
            ->teachers()
            ->orderBy('users.lastname', 'asc')
            ->orderBy('users.firstname', 'asc')
            ->get()
            ->map(fn($u) => $u->toArray())
            ->all();
    }

    /**
     * TeacherModel: getTeachersAndTAs()
     * Returns: id, firstname, lastname, email, cellphone, role
     * Picks MIN(role) for stability if user has both.
     */
    public static function getTeachersAndTAs(): array
    {
        return DB::table('users as u')
            ->selectRaw('u.id, u.firstname, u.lastname, u.email, u.cellphone, MIN(r.name) as role')
            ->join('user_roles as ur', 'ur.user_id', '=', 'u.id')
            ->join('roles as r', 'r.id', '=', 'ur.role_id')
            ->whereNull('ur.deleted_at')
            ->whereIn(DB::raw('LOWER(r.name)'), ['teacher', 'teacher_assistant'])
            ->groupBy('u.id', 'u.firstname', 'u.lastname', 'u.email', 'u.cellphone')
            ->orderBy('u.lastname', 'asc')
            ->orderBy('u.firstname', 'asc')
            ->get()
            ->map(fn($r) => (array) $r)
            ->all();
    }

    /**
     * TeacherModel: getTeachersWithAssignments()
     * Reads config_key=current_school_year and returns current_class + school_year.
     *
     * NOTE: your CI code joins class_section via classSection.id.
     * Here I join teacher_class.class_section_id => class_section.id to match that snippet.
     * If your real section table is classSection (not class_section), change the join accordingly.
     */
    public static function getTeachersWithAssignments(): array
    {
        $schoolYear = (string) (DB::table('configuration')
            ->where('config_key', 'current_school_year')
            ->value('config_value') ?? 'Unknown');

        return DB::table('users')
            ->selectRaw('
                users.id,
                users.firstname,
                users.lastname,
                users.email,
                users.cellphone,
                COALESCE(cs.class_section_name, "No Class Assigned") AS current_class,
                ? AS school_year
            ', [$schoolYear])
            ->leftJoin('teacher_class as tc', 'tc.teacher_id', '=', 'users.id')
            ->leftJoin('class_section as cs', 'tc.class_section_id', '=', 'cs.id')
            ->whereExists(function ($sub) {
                $sub->selectRaw('1')
                    ->from('user_roles as ur')
                    ->join('roles as r', 'r.id', '=', 'ur.role_id')
                    ->whereColumn('ur.user_id', 'users.id')
                    ->whereNull('ur.deleted_at')
                    ->whereRaw('LOWER(r.name) = ?', ['teacher'])
                    ->where('r.is_active', 1);
            })
            ->orderBy('users.lastname', 'asc')
            ->orderBy('users.firstname', 'asc')
            ->get()
            ->map(fn($r) => (array) $r)
            ->all();
    }

    /* ============================================================
     * Registration validation (converted to Laravel rules/messages)
     * ============================================================
     */

    public static function registrationRules(): array
    {
        return [
            'password'             => ['required', 'string', 'min:8', 'regex:/[A-Z]/', 'regex:/[a-z]/', 'regex:/[0-9]/', 'regex:/[\W_]/'],
            'lastname'             => ['required', 'string', 'max:120'],
            'firstname'            => ['required', 'string', 'max:120'],
            'cellphone'            => ['required', 'regex:/^\d{10}$/'],
            'email'                => ['required', 'email', 'max:255'],
            'address_street'       => ['required', 'string', 'max:255'],
            'city'                 => ['required', 'string', 'max:120'],
            'state'                => ['required', 'regex:/^[A-Z]{2}$/'],
            'zip'                  => ['required', 'regex:/^\d{5}(-\d{4})?$/'],
            'accept_school_policy' => ['required', 'in:1,true,on'],
        ];
    }

    public static function registrationMessages(): array
    {
        return [
            'password.regex' => 'Password must include uppercase, lowercase, number, and special character.',
            'cellphone.regex' => 'Cellphone must be a 10-digit number.',
            'zip.regex' => 'Invalid zip code format.',
            'state.regex' => 'Invalid state code.',
            'accept_school_policy.in' => 'You must accept the school policy.',
        ];
    }

    /* ============================================================
     * Small convenience: hash password automatically if set
     * ============================================================
     */
    public function setPasswordAttribute($value): void
    {
        if ($value === null || $value === '') return;

        // Avoid double-hashing
        $this->attributes['password'] = str_starts_with((string)$value, '$2y$')
            ? $value
            : Hash::make($value);
    }
}
