<?php
namespace App\Models;

use App\Core\Model;

/** Model tabel `users`. */
class User extends Model
{
    protected string $table = 'users';

    public function listing(array $f = [], int $page = 1, int $perPage = 50): array
    {
        $w = ['u.deleted_at IS NULL'];
        $p = [];
        if (!empty($f['cari'])) {
            $w[] = '(u.username LIKE :q OR u.name LIKE :q OR u.email LIKE :q)';
            $p['q'] = '%' . $f['cari'] . '%';
        }
        if (!empty($f['role_id'])) { $w[] = 'u.role_id = :r'; $p['r'] = (int)$f['role_id']; }
        if (isset($f['is_active']) && $f['is_active'] !== '') { $w[] = 'u.is_active = :a'; $p['a'] = (int)$f['is_active']; }
        $where = implode(' AND ', $w);
        $sql = 'SELECT u.*, r.name AS role_name, r.code AS role_code,
                       a.name AS area_name, bu.name AS business_unit_name,
                       s.name AS salesperson_name, co.name AS collection_officer_name
                  FROM users u
                  JOIN roles r ON r.id = u.role_id
                  LEFT JOIN areas a ON a.id = u.area_id
                  LEFT JOIN business_units bu ON bu.id = u.business_unit_id
                  LEFT JOIN salespersons s ON s.id = u.salesperson_id
                  LEFT JOIN collection_officers co ON co.id = u.collection_officer_id
                 WHERE ' . $where . ' ORDER BY u.name';
        return $this->paginate($sql, $p, $page, $perPage,
            'SELECT COUNT(*) FROM users u WHERE ' . $where);
    }

    public function extraRoles(int $userId): array
    {
        return $this->db()->column('SELECT role_id FROM user_roles WHERE user_id = ?', [$userId]);
    }

    public function setExtraRoles(int $userId, array $roleIds): void
    {
        $this->db()->exec('DELETE FROM user_roles WHERE user_id = ?', [$userId]);
        foreach (array_unique(array_map('intval', $roleIds)) as $rid) {
            if ($rid > 0) $this->db()->exec('INSERT IGNORE INTO user_roles (user_id, role_id) VALUES (?,?)', [$userId, $rid]);
        }
    }
}
