root
0f8ad86b4f
Create user_access_profiles as a denormalized access index over the existing roles and user_roles tables. Store primary_category plus is_admin, is_teacher, and is_parent flags so permission checks can use a single canonical source for broad user groups. Classify teacher and teacher_assistant/TA roles as teacher access, parent as parent access, and every other active staff role as admin access. Backfill all existing users during migration and keep profiles synchronized when role assignments are inserted, updated, or deleted. Expose the computed access profile in web sessions and auth API responses while preserving the existing detailed roles array and route-filter behavior. Add unit coverage for TA, staff-admin, and multi-role parent/teacher classification.
133 lines
5.1 KiB
PHP
133 lines
5.1 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Database\Migrations;
|
|
|
|
use CodeIgniter\Database\Migration;
|
|
|
|
final class CreateUserAccessProfiles extends Migration
|
|
{
|
|
public function up(): void
|
|
{
|
|
if (! $this->db->tableExists('user_access_profiles')) {
|
|
$this->forge->addField([
|
|
'id' => [
|
|
'type' => 'INT',
|
|
'constraint' => 11,
|
|
'unsigned' => true,
|
|
'auto_increment' => true,
|
|
],
|
|
'user_id' => [
|
|
'type' => 'INT',
|
|
'constraint' => 11,
|
|
'unsigned' => true,
|
|
],
|
|
'primary_category' => [
|
|
'type' => 'VARCHAR',
|
|
'constraint' => 20,
|
|
'default' => 'guest',
|
|
],
|
|
'is_admin' => [
|
|
'type' => 'TINYINT',
|
|
'constraint' => 1,
|
|
'default' => 0,
|
|
],
|
|
'is_teacher' => [
|
|
'type' => 'TINYINT',
|
|
'constraint' => 1,
|
|
'default' => 0,
|
|
],
|
|
'is_parent' => [
|
|
'type' => 'TINYINT',
|
|
'constraint' => 1,
|
|
'default' => 0,
|
|
],
|
|
'role_names' => [
|
|
'type' => 'TEXT',
|
|
'null' => true,
|
|
],
|
|
'created_at' => [
|
|
'type' => 'DATETIME',
|
|
'null' => true,
|
|
],
|
|
'updated_at' => [
|
|
'type' => 'DATETIME',
|
|
'null' => true,
|
|
],
|
|
]);
|
|
$this->forge->addKey('id', true);
|
|
$this->forge->addKey('user_id', false, true);
|
|
$this->forge->addKey('primary_category');
|
|
$this->forge->addKey('is_admin');
|
|
$this->forge->addKey('is_teacher');
|
|
$this->forge->addKey('is_parent');
|
|
$this->forge->createTable('user_access_profiles', true);
|
|
}
|
|
|
|
$this->backfillProfiles();
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
$this->forge->dropTable('user_access_profiles', true);
|
|
}
|
|
|
|
private function backfillProfiles(): void
|
|
{
|
|
if (
|
|
! $this->db->tableExists('users')
|
|
|| ! $this->db->tableExists('roles')
|
|
|| ! $this->db->tableExists('user_roles')
|
|
|| ! $this->db->tableExists('user_access_profiles')
|
|
) {
|
|
return;
|
|
}
|
|
|
|
$now = date('Y-m-d H:i:s');
|
|
$deletedFilter = $this->db->fieldExists('deleted_at', 'user_roles')
|
|
? 'AND ur.deleted_at IS NULL'
|
|
: '';
|
|
|
|
$sql = "
|
|
INSERT INTO user_access_profiles (
|
|
user_id,
|
|
primary_category,
|
|
is_admin,
|
|
is_teacher,
|
|
is_parent,
|
|
role_names,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
SELECT
|
|
u.id AS user_id,
|
|
CASE
|
|
WHEN MAX(CASE WHEN LOWER(REPLACE(REPLACE(COALESCE(r.slug, r.name), ' ', '_'), '-', '_')) NOT IN ('guest', 'parent', 'student', 'teacher', 'teacher_assistant', 'assistant_teacher', 'ta') THEN 1 ELSE 0 END) = 1 THEN 'admin'
|
|
WHEN MAX(CASE WHEN LOWER(REPLACE(REPLACE(COALESCE(r.slug, r.name), ' ', '_'), '-', '_')) IN ('teacher', 'teacher_assistant', 'assistant_teacher', 'ta') THEN 1 ELSE 0 END) = 1 THEN 'teacher'
|
|
WHEN MAX(CASE WHEN LOWER(REPLACE(REPLACE(COALESCE(r.slug, r.name), ' ', '_'), '-', '_')) = 'parent' THEN 1 ELSE 0 END) = 1 THEN 'parent'
|
|
ELSE 'guest'
|
|
END AS primary_category,
|
|
MAX(CASE WHEN LOWER(REPLACE(REPLACE(COALESCE(r.slug, r.name), ' ', '_'), '-', '_')) NOT IN ('guest', 'parent', 'student', 'teacher', 'teacher_assistant', 'assistant_teacher', 'ta') THEN 1 ELSE 0 END) AS is_admin,
|
|
MAX(CASE WHEN LOWER(REPLACE(REPLACE(COALESCE(r.slug, r.name), ' ', '_'), '-', '_')) IN ('teacher', 'teacher_assistant', 'assistant_teacher', 'ta') THEN 1 ELSE 0 END) AS is_teacher,
|
|
MAX(CASE WHEN LOWER(REPLACE(REPLACE(COALESCE(r.slug, r.name), ' ', '_'), '-', '_')) = 'parent' THEN 1 ELSE 0 END) AS is_parent,
|
|
GROUP_CONCAT(DISTINCT r.name ORDER BY COALESCE(r.priority, 999), r.name SEPARATOR ', ') AS role_names,
|
|
? AS created_at,
|
|
? AS updated_at
|
|
FROM users u
|
|
LEFT JOIN user_roles ur ON ur.user_id = u.id {$deletedFilter}
|
|
LEFT JOIN roles r ON r.id = ur.role_id AND COALESCE(r.is_active, 1) = 1
|
|
GROUP BY u.id
|
|
ON DUPLICATE KEY UPDATE
|
|
primary_category = VALUES(primary_category),
|
|
is_admin = VALUES(is_admin),
|
|
is_teacher = VALUES(is_teacher),
|
|
is_parent = VALUES(is_parent),
|
|
role_names = VALUES(role_names),
|
|
updated_at = VALUES(updated_at)
|
|
";
|
|
|
|
$this->db->query($sql, [$now, $now]);
|
|
}
|
|
}
|