<?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]);
    }
}