<?php

namespace App\Models;

use CodeIgniter\Model;

class UserProfileModel extends Model
{
    protected $table         = 'user_profiles';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $allowedFields = [
        'user_id', 'name', 'identity_number', 'study_program_id', 'role', 'status',
    ];

    public function findByUserId(int $user_id): ?array
    {
        return $this->where('user_id', $user_id)->first();
    }

    public function getWithUser(int $user_id): ?array
    {
        return $this->db->table('user_profiles up')
            ->select('up.*, ui.secret AS email, u.username, u.last_active, sp.name AS study_program_name')
            ->join('auth_identities ui', 'ui.user_id = up.user_id AND ui.type = "email_password"')
            ->join('users u', 'u.id = up.user_id')
            ->join('study_programs sp', 'sp.id = up.study_program_id', 'left')
            ->where('up.user_id', $user_id)
            ->get()->getRowArray();
    }

    public function getAllWithUsers(array $filters = []): array
    {
        $builder = $this->db->table('user_profiles up')
            ->select('up.*, ui.secret AS email, u.username, u.last_active, sp.name AS study_program_name')
            ->join('auth_identities ui', 'ui.user_id = up.user_id AND ui.type = "email_password"')
            ->join('users u', 'u.id = up.user_id')
            ->join('study_programs sp', 'sp.id = up.study_program_id', 'left');

        if (!empty($filters['role'])) {
            $builder->where('up.role', $filters['role']);
        }
        if (!empty($filters['status'])) {
            $builder->where('up.status', $filters['status']);
        }
        if (!empty($filters['search'])) {
            $builder->groupStart()
                ->like('up.name', $filters['search'])
                ->orLike('ui.secret', $filters['search'])
                ->orLike('up.identity_number', $filters['search'])
                ->groupEnd();
        }

        return $builder->orderBy('up.created_at', 'DESC')->get()->getResultArray();
    }
}
