<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use RuntimeException;

final class AccessControlRepository
{
    /** @var string */
    private $hospitalCode;

    public function __construct(?string $hospitalCode = null)
    {
        $configured = strtolower(trim((string)(
            $hospitalCode ?: \config('access_control.control_branch', 'njch')
        )));
        $this->hospitalCode = $configured !== '' ? $configured : 'njch';
    }

    public function schemaReady(): bool
    {
        $row = Database::fetchOne($this->hospitalCode, "
            SELECT COUNT(*) table_count
            FROM USER_TABLES
            WHERE TABLE_NAME IN (
                'DASH_ACL_ROLES',
                'DASH_ACL_USER_ROLES',
                'DASH_ACL_ROLE_OVERRIDES',
                'DASH_ACL_USER_BRANCHES',
                'DASH_ACL_AUDIT'
            )
        ");

        return (int)($row['table_count'] ?? 0) === 5;
    }

    public function roleManagementReady(): bool
    {
        if (!$this->schemaReady()) {
            return false;
        }

        $columns = Database::fetchOne($this->hospitalCode, "
            SELECT COUNT(*) column_count
            FROM USER_TAB_COLUMNS
            WHERE TABLE_NAME = 'DASH_ACL_ROLES'
              AND COLUMN_NAME IN (
                  'ROLE_DESCRIPTION_EN',
                  'ROLE_DESCRIPTION_AR'
              )
        ");

        $indexes = Database::fetchOne($this->hospitalCode, "
            SELECT COUNT(*) index_count
            FROM USER_INDEXES
            WHERE INDEX_NAME = 'UX_DASH_ACL_USER_ONE_ROLE'
        ");

        return (int)($columns['column_count'] ?? 0) === 2
            && (int)($indexes['index_count'] ?? 0) === 1;
    }

    public function userHasActiveRole(string $username): bool
    {
        if (!$this->schemaReady()) {
            return false;
        }

        $row = Database::fetchOne($this->hospitalCode, "
            SELECT COUNT(*) role_count
            FROM DASH_ACL_USER_ROLES ur
            JOIN DASH_ACL_ROLES r
              ON r.ROLE_CODE = ur.ROLE_CODE
            WHERE UPPER(ur.USERNAME) = UPPER(:username)
              AND NVL(ur.IS_ACTIVE, 1) = 1
              AND NVL(r.IS_ACTIVE, 1) = 1
        ", ['username' => $username]);

        return (int)($row['role_count'] ?? 0) > 0;
    }

    /** @return array<string,mixed> */
    public function contextForUser(string $username): array
    {
        if (!$this->schemaReady()) {
            throw new RuntimeException('DASH_ACL_SCHEMA_NOT_READY');
        }

        $roles = Database::fetchAll($this->hospitalCode, "
            SELECT
                r.ROLE_CODE role_code,
                r.ROLE_NAME_EN role_name_en,
                r.ROLE_NAME_AR role_name_ar,
                r.POLICY_MODE policy_mode,
                NVL(r.AUTO_INCLUDE_NEW, 0) auto_include_new,
                NVL(r.ALL_BRANCHES, 0) all_branches,
                NVL(r.IS_PROTECTED, 0) is_protected
            FROM DASH_ACL_USER_ROLES ur
            JOIN DASH_ACL_ROLES r
              ON r.ROLE_CODE = ur.ROLE_CODE
            WHERE UPPER(ur.USERNAME) = UPPER(:username)
              AND NVL(ur.IS_ACTIVE, 1) = 1
              AND NVL(r.IS_ACTIVE, 1) = 1
            ORDER BY
                CASE r.ROLE_CODE
                    WHEN 'SITE_ADMIN' THEN 1
                    WHEN 'GENERAL_MANAGER' THEN 2
                    ELSE 9
                END,
                r.ROLE_CODE
        ", ['username' => $username]);

        $overrides = [];
        if ($roles !== []) {
            $roleCodes = array_values(array_unique(array_map(
                static function (array $row): string {
                    return (string)($row['role_code'] ?? '');
                },
                $roles
            )));

            $holders = [];
            $params = [];
            foreach ($roleCodes as $index => $roleCode) {
                $key = 'context_role_' . $index;
                $holders[] = ':' . $key;
                $params[$key] = $roleCode;
            }

            $rows = Database::fetchAll($this->hospitalCode, "
                SELECT
                    ROLE_CODE role_code,
                    PERMISSION_CODE permission_code,
                    EFFECT effect
                FROM DASH_ACL_ROLE_OVERRIDES
                WHERE ROLE_CODE IN (" . implode(', ', $holders) . ")
            ", $params);

            foreach ($rows as $row) {
                $roleCode = trim((string)($row['role_code'] ?? ''));
                $permission = trim((string)($row['permission_code'] ?? ''));
                $effect = strtoupper(trim((string)($row['effect'] ?? '')));

                if (
                    $roleCode !== ''
                    && $permission !== ''
                    && in_array($effect, ['ALLOW', 'DENY'], true)
                ) {
                    $overrides[$roleCode][$permission] = $effect;
                }
            }
        }

        $branchRows = Database::fetchAll($this->hospitalCode, "
            SELECT HOSPITAL_CODE hospital_code
            FROM DASH_ACL_USER_BRANCHES
            WHERE UPPER(USERNAME) = UPPER(:username)
              AND NVL(IS_ACTIVE, 1) = 1
            ORDER BY HOSPITAL_CODE
        ", ['username' => $username]);

        $branches = array_values(array_unique(array_filter(array_map(
            static function (array $row): string {
                return strtolower(trim((string)(
                    $row['hospital_code'] ?? ''
                )));
            },
            $branchRows
        ))));

        return [
            'source' => 'ACL',
            'schema_ready' => true,
            'roles' => $roles,
            'overrides' => $overrides,
            'branches' => $branches,
            'loaded_at' => date('c'),
        ];
    }

    /** @return array<int,array<string,mixed>> */
    public function listRoles(): array
    {
        $descriptionColumns = $this->roleManagementReady()
            ? "r.ROLE_DESCRIPTION_EN role_description_en,
               r.ROLE_DESCRIPTION_AR role_description_ar,"
            : "CAST(NULL AS VARCHAR2(500)) role_description_en,
               CAST(NULL AS VARCHAR2(500)) role_description_ar,";

        return Database::fetchAll($this->hospitalCode, "
            SELECT
                r.ROLE_CODE role_code,
                r.ROLE_NAME_EN role_name_en,
                r.ROLE_NAME_AR role_name_ar,
                {$descriptionColumns}
                r.POLICY_MODE policy_mode,
                NVL(r.AUTO_INCLUDE_NEW, 0) auto_include_new,
                NVL(r.ALL_BRANCHES, 0) all_branches,
                NVL(r.IS_SYSTEM_ROLE, 0) is_system_role,
                NVL(r.IS_PROTECTED, 0) is_protected,
                NVL(r.IS_ACTIVE, 1) is_active,
                (
                    SELECT COUNT(DISTINCT UPPER(ur.USERNAME))
                    FROM DASH_ACL_USER_ROLES ur
                    WHERE ur.ROLE_CODE = r.ROLE_CODE
                      AND NVL(ur.IS_ACTIVE, 1) = 1
                ) user_count,
                (
                    SELECT COUNT(*)
                    FROM DASH_ACL_ROLE_OVERRIDES ro
                    WHERE ro.ROLE_CODE = r.ROLE_CODE
                ) override_count
            FROM DASH_ACL_ROLES r
            ORDER BY
                CASE r.ROLE_CODE
                    WHEN 'SITE_ADMIN' THEN 1
                    WHEN 'GENERAL_MANAGER' THEN 2
                    ELSE 9
                END,
                NVL(r.IS_ACTIVE, 1) DESC,
                UPPER(r.ROLE_NAME_EN),
                r.ROLE_CODE
        ");
    }

    /** @return array<string,mixed>|null */
    public function roleByCode(string $roleCode): ?array
    {
        $roleCode = strtoupper(trim($roleCode));
        if ($roleCode === '') {
            return null;
        }

        foreach ($this->listRoles() as $role) {
            if ((string)($role['role_code'] ?? '') === $roleCode) {
                return $role;
            }
        }

        return null;
    }

    /** @return array<string,array<string,string>> */
    public function allRoleOverrides(): array
    {
        $rows = Database::fetchAll($this->hospitalCode, "
            SELECT
                ROLE_CODE role_code,
                PERMISSION_CODE permission_code,
                EFFECT effect
            FROM DASH_ACL_ROLE_OVERRIDES
            ORDER BY ROLE_CODE, PERMISSION_CODE
        ");

        $out = [];
        foreach ($rows as $row) {
            $roleCode = trim((string)($row['role_code'] ?? ''));
            $permission = trim((string)($row['permission_code'] ?? ''));
            $effect = strtoupper(trim((string)($row['effect'] ?? '')));

            if (
                $roleCode !== ''
                && $permission !== ''
                && in_array($effect, ['ALLOW', 'DENY'], true)
            ) {
                $out[$roleCode][$permission] = $effect;
            }
        }

        return $out;
    }

    /** @return array<string,array<string,string>> */
    public function roleOverrides(string $roleCode): array
    {
        $roleCode = strtoupper(trim($roleCode));
        return (array)($this->allRoleOverrides()[$roleCode] ?? []);
    }

    /** @return array<string,int> */
    public function accessCounts(): array
    {
        if (!$this->schemaReady()) {
            return [
                'site_admins' => 0,
                'general_managers' => 0,
                'configured_users' => 0,
                'active_roles' => 0,
                'custom_roles' => 0,
            ];
        }

        $row = Database::fetchOne($this->hospitalCode, "
            SELECT
                (
                    SELECT COUNT(DISTINCT UPPER(USERNAME))
                    FROM DASH_ACL_USER_ROLES
                    WHERE ROLE_CODE = 'SITE_ADMIN'
                      AND NVL(IS_ACTIVE, 1) = 1
                ) site_admins,
                (
                    SELECT COUNT(DISTINCT UPPER(USERNAME))
                    FROM DASH_ACL_USER_ROLES
                    WHERE ROLE_CODE = 'GENERAL_MANAGER'
                      AND NVL(IS_ACTIVE, 1) = 1
                ) general_managers,
                (
                    SELECT COUNT(DISTINCT UPPER(USERNAME))
                    FROM DASH_ACL_USER_ROLES
                    WHERE NVL(IS_ACTIVE, 1) = 1
                ) configured_users,
                (
                    SELECT COUNT(*)
                    FROM DASH_ACL_ROLES
                    WHERE NVL(IS_ACTIVE, 1) = 1
                ) active_roles,
                (
                    SELECT COUNT(*)
                    FROM DASH_ACL_ROLES
                    WHERE NVL(IS_ACTIVE, 1) = 1
                      AND NVL(IS_SYSTEM_ROLE, 0) = 0
                ) custom_roles
            FROM dual
        ");

        return [
            'site_admins' => (int)($row['site_admins'] ?? 0),
            'general_managers' => (int)($row['general_managers'] ?? 0),
            'configured_users' => (int)($row['configured_users'] ?? 0),
            'active_roles' => (int)($row['active_roles'] ?? 0),
            'custom_roles' => (int)($row['custom_roles'] ?? 0),
        ];
    }

    /** @return array<int,array<string,mixed>> */
    public function listDashboardUsers(): array
    {
        return Database::fetchAll($this->hospitalCode, "
            SELECT
                NAME username,
                NVL(DASHBOARD_ROLE, 0) dashboard_role,
                NVL(DASHBOARD_ROLE_FIN, 0) dashboard_role_fin,
                NVL(
                    DASHBOARD_ROLE_REVENUE,
                    NVL(DASHBOARD_ROLE, 0)
                ) dashboard_role_revenue
            FROM SECURITY_USERS
            WHERE NAME IS NOT NULL
              AND (
                    NVL(DASHBOARD_ROLE, 0) = 1
                    OR EXISTS (
                        SELECT 1
                        FROM DASH_ACL_USER_ROLES ur
                        WHERE UPPER(ur.USERNAME) =
                              UPPER(SECURITY_USERS.NAME)
                          AND NVL(ur.IS_ACTIVE, 1) = 1
                    )
              )
            ORDER BY UPPER(NAME)
        ");
    }

    /** @return array<int,array<string,mixed>> */
    public function userAssignments(): array
    {
        return Database::fetchAll($this->hospitalCode, "
            SELECT
                ur.USERNAME username,
                ur.ROLE_CODE role_code,
                LISTAGG(
                    ub.HOSPITAL_CODE,
                    ','
                ) WITHIN GROUP (
                    ORDER BY ub.HOSPITAL_CODE
                ) branches
            FROM DASH_ACL_USER_ROLES ur
            LEFT JOIN DASH_ACL_USER_BRANCHES ub
              ON UPPER(ub.USERNAME) = UPPER(ur.USERNAME)
             AND NVL(ub.IS_ACTIVE, 1) = 1
            WHERE NVL(ur.IS_ACTIVE, 1) = 1
            GROUP BY ur.USERNAME, ur.ROLE_CODE
            ORDER BY UPPER(ur.USERNAME), ur.ROLE_CODE
        ");
    }

    /** @return array<int,array<string,mixed>> */
    public function recentAudit(int $limit = 40): array
    {
        $limit = max(5, min(100, $limit));

        return Database::fetchAll($this->hospitalCode, "
            SELECT *
            FROM (
                SELECT
                    AUDIT_ID audit_id,
                    ACTOR_USERNAME actor_username,
                    ACTION_CODE action_code,
                    TARGET_TYPE target_type,
                    TARGET_KEY target_key,
                    OLD_VALUE old_value,
                    NEW_VALUE new_value,
                    CREATED_AT created_at
                FROM DASH_ACL_AUDIT
                ORDER BY AUDIT_ID DESC
            )
            WHERE ROWNUM <= :row_limit
        ", ['row_limit' => $limit]);
    }

    /**
     * @param array<string,string> $effects
     */
    public function saveRole(
        string $roleCode,
        string $nameEn,
        string $nameAr,
        string $descriptionEn,
        string $descriptionAr,
        string $policyMode,
        bool $allBranches,
        bool $isActive,
        array $effects,
        string $actor,
        bool $isNew
    ): void {
        if (!$this->roleManagementReady()) {
            throw new RuntimeException('ROLE_MANAGEMENT_UPGRADE_REQUIRED');
        }

        $roleCode = strtoupper(trim($roleCode));
        $nameEn = trim($nameEn);
        $nameAr = trim($nameAr);
        $descriptionEn = trim($descriptionEn);
        $descriptionAr = trim($descriptionAr);
        $policyMode = strtoupper(trim($policyMode));

        if (!preg_match('/^[A-Z][A-Z0-9_]{1,49}$/', $roleCode)) {
            throw new RuntimeException('INVALID_ROLE_CODE');
        }
        if ($nameEn === '') {
            throw new RuntimeException('ROLE_NAME_REQUIRED');
        }
        if (!in_array($policyMode, ['EXPLICIT', 'ALL_BUSINESS'], true)) {
            throw new RuntimeException('INVALID_ROLE_POLICY');
        }

        $existing = $this->roleByCode($roleCode);

        if ($isNew) {
            if ($existing !== null) {
                throw new RuntimeException('ROLE_CODE_ALREADY_EXISTS');
            }
            if (in_array($roleCode, ['SITE_ADMIN', 'GENERAL_MANAGER'], true)) {
                throw new RuntimeException('PROTECTED_ROLE_CODE_RESERVED');
            }
        } else {
            if ($existing === null) {
                throw new RuntimeException('ROLE_NOT_FOUND');
            }
            if ($roleCode === 'SITE_ADMIN') {
                throw new RuntimeException('SITE_ADMIN_POLICY_IS_IMMUTABLE');
            }
        }

        if ($roleCode === 'GENERAL_MANAGER') {
            $policyMode = 'ALL_BUSINESS';
            $isActive = true;
        }

        if (
            !$isActive
            && $existing !== null
            && (int)($existing['user_count'] ?? 0) > 0
        ) {
            throw new RuntimeException('ROLE_HAS_ASSIGNED_USERS');
        }

        $permissions = (array)\config('access_control.permissions', []);
        $cleanEffects = [];
        foreach ($effects as $permissionCode => $effect) {
            $permissionCode = trim((string)$permissionCode);
            $effect = strtoupper(trim((string)$effect));

            if (
                $permissionCode !== ''
                && isset($permissions[$permissionCode])
                && in_array($effect, ['ALLOW', 'DENY'], true)
            ) {
                $cleanEffects[$permissionCode] = $effect;
            }
        }

        $old = $existing === null
            ? null
            : [
                'role' => $existing,
                'overrides' => $this->roleOverrides($roleCode),
            ];

        $statements = [];

        if ($isNew) {
            $statements[] = [
                'sql' => "INSERT INTO DASH_ACL_ROLES (
                    ROLE_CODE,
                    ROLE_NAME_EN,
                    ROLE_NAME_AR,
                    ROLE_DESCRIPTION_EN,
                    ROLE_DESCRIPTION_AR,
                    POLICY_MODE,
                    AUTO_INCLUDE_NEW,
                    ALL_BRANCHES,
                    IS_SYSTEM_ROLE,
                    IS_PROTECTED,
                    IS_ACTIVE,
                    UPDATED_BY,
                    UPDATED_AT
                ) VALUES (
                    :role_code,
                    :name_en,
                    :name_ar,
                    :description_en,
                    :description_ar,
                    :policy_mode,
                    :auto_include_new,
                    :all_branches,
                    0,
                    0,
                    :is_active,
                    :actor,
                    SYSDATE
                )",
                'params' => [
                    'role_code' => $roleCode,
                    'name_en' => $nameEn,
                    'name_ar' => $nameAr,
                    'description_en' => $descriptionEn,
                    'description_ar' => $descriptionAr,
                    'policy_mode' => $policyMode,
                    'auto_include_new' => $policyMode === 'ALL_BUSINESS' ? 1 : 0,
                    'all_branches' => $allBranches ? 1 : 0,
                    'is_active' => $isActive ? 1 : 0,
                    'actor' => $actor,
                ],
            ];
        } else {
            $statements[] = [
                'sql' => "UPDATE DASH_ACL_ROLES
                    SET ROLE_NAME_EN = :name_en,
                        ROLE_NAME_AR = :name_ar,
                        ROLE_DESCRIPTION_EN = :description_en,
                        ROLE_DESCRIPTION_AR = :description_ar,
                        POLICY_MODE = :policy_mode,
                        AUTO_INCLUDE_NEW = :auto_include_new,
                        ALL_BRANCHES = :all_branches,
                        IS_ACTIVE = :is_active,
                        UPDATED_BY = :actor,
                        UPDATED_AT = SYSDATE
                    WHERE ROLE_CODE = :role_code",
                'params' => [
                    'name_en' => $nameEn,
                    'name_ar' => $nameAr,
                    'description_en' => $descriptionEn,
                    'description_ar' => $descriptionAr,
                    'policy_mode' => $policyMode,
                    'auto_include_new' => $policyMode === 'ALL_BUSINESS' ? 1 : 0,
                    'all_branches' => $allBranches ? 1 : 0,
                    'is_active' => $isActive ? 1 : 0,
                    'actor' => $actor,
                    'role_code' => $roleCode,
                ],
            ];
        }

        $statements[] = [
            'sql' => "DELETE FROM DASH_ACL_ROLE_OVERRIDES
                WHERE ROLE_CODE = :role_code",
            'params' => ['role_code' => $roleCode],
        ];

        foreach ($cleanEffects as $permissionCode => $effect) {
            $statements[] = [
                'sql' => "INSERT INTO DASH_ACL_ROLE_OVERRIDES (
                    ROLE_CODE,
                    PERMISSION_CODE,
                    EFFECT,
                    UPDATED_BY,
                    UPDATED_AT
                ) VALUES (
                    :role_code,
                    :permission_code,
                    :effect,
                    :actor,
                    SYSDATE
                )",
                'params' => [
                    'role_code' => $roleCode,
                    'permission_code' => $permissionCode,
                    'effect' => $effect,
                    'actor' => $actor,
                ],
            ];
        }

        $new = [
            'role_code' => $roleCode,
            'name_en' => $nameEn,
            'name_ar' => $nameAr,
            'description_en' => $descriptionEn,
            'description_ar' => $descriptionAr,
            'policy_mode' => $policyMode,
            'all_branches' => $allBranches,
            'is_active' => $isActive,
            'overrides' => $cleanEffects,
        ];

        $statements[] = $this->auditStatement(
            $actor,
            $isNew ? 'ROLE_CREATED' : 'ROLE_UPDATED',
            'ROLE',
            $roleCode,
            $old === null ? null : json_encode($old, JSON_UNESCAPED_SLASHES),
            json_encode($new, JSON_UNESCAPED_SLASHES)
        );

        Database::executeTransaction($this->hospitalCode, $statements);
    }

    /**
     * @param array<string,string> $effects
     */
    public function replaceRoleOverrides(
        string $roleCode,
        array $effects,
        string $actor
    ): void {
        $role = $this->roleByCode($roleCode);
        if ($role === null) {
            throw new RuntimeException('ROLE_NOT_FOUND');
        }

        $this->saveRole(
            (string)$role['role_code'],
            (string)$role['role_name_en'],
            (string)($role['role_name_ar'] ?? ''),
            (string)($role['role_description_en'] ?? ''),
            (string)($role['role_description_ar'] ?? ''),
            (string)$role['policy_mode'],
            (int)($role['all_branches'] ?? 0) === 1,
            (int)($role['is_active'] ?? 1) === 1,
            $effects,
            $actor,
            false
        );
    }

    /**
     * @param array<int,string> $branches
     */
    public function replaceUserAccess(
        string $username,
        string $roleCode,
        array $branches,
        string $actor
    ): void {
        $username = trim($username);
        $roleCode = strtoupper(trim($roleCode));
        $branches = array_values(array_unique(array_filter(array_map(
            'strtolower',
            array_map('trim', $branches)
        ))));

        if ($username === '') {
            throw new RuntimeException('USER_REQUIRED');
        }

        $identity = Database::fetchOne($this->hospitalCode, "
            SELECT
                CASE
                    WHEN EXISTS (
                        SELECT 1
                        FROM SECURITY_USERS
                        WHERE UPPER(NAME) = UPPER(:security_username)
                    )
                    OR EXISTS (
                        SELECT 1
                        FROM DASH_ACL_USER_ROLES
                        WHERE UPPER(USERNAME) = UPPER(:acl_username)
                    )
                    THEN 1
                    ELSE 0
                END identity_exists
            FROM dual
        ", [
            'security_username' => $username,
            'acl_username' => $username,
        ]);

        if ((int)($identity['identity_exists'] ?? 0) !== 1) {
            throw new RuntimeException('DASHBOARD_USER_NOT_FOUND');
        }

        $current = $this->contextForUser($username);
        $currentRoles = array_values(array_filter(array_map(
            static function (array $row): string {
                return (string)($row['role_code'] ?? '');
            },
            (array)($current['roles'] ?? [])
        )));

        $role = null;
        if ($roleCode !== '') {
            $role = $this->roleByCode($roleCode);
            if ($role === null || (int)($role['is_active'] ?? 0) !== 1) {
                throw new RuntimeException('ROLE_NOT_AVAILABLE');
            }
        }

        if (
            in_array('SITE_ADMIN', $currentRoles, true)
            && $roleCode !== 'SITE_ADMIN'
        ) {
            $row = Database::fetchOne($this->hospitalCode, "
                SELECT COUNT(DISTINCT UPPER(USERNAME)) admin_count
                FROM DASH_ACL_USER_ROLES
                WHERE ROLE_CODE = 'SITE_ADMIN'
                  AND NVL(IS_ACTIVE, 1) = 1
                  AND UPPER(USERNAME) <> UPPER(:username)
            ", ['username' => $username]);

            if ((int)($row['admin_count'] ?? 0) < 1) {
                throw new RuntimeException('AT_LEAST_ONE_SITE_ADMIN_REQUIRED');
            }
        }

        if ($roleCode === '' || $roleCode === 'SITE_ADMIN') {
            $branches = [];
        } else {
            foreach ($branches as $branch) {
                if (!in_array($branch, ['*', 'njch', 'mnh'], true)) {
                    throw new RuntimeException('INVALID_BRANCH_SCOPE');
                }
            }

            if ($branches === []) {
                throw new RuntimeException('ROLE_BRANCH_SCOPE_REQUIRED');
            }

            if (in_array('*', $branches, true)) {
                $branches = ['*'];
            }
        }

        $statements = [
            [
                'sql' => "DELETE FROM DASH_ACL_USER_ROLES
                    WHERE UPPER(USERNAME) = UPPER(:username)",
                'params' => ['username' => $username],
            ],
            [
                'sql' => "DELETE FROM DASH_ACL_USER_BRANCHES
                    WHERE UPPER(USERNAME) = UPPER(:username)",
                'params' => ['username' => $username],
            ],
        ];

        if ($roleCode !== '') {
            $statements[] = [
                'sql' => "INSERT INTO DASH_ACL_USER_ROLES (
                    USERNAME,
                    ROLE_CODE,
                    IS_ACTIVE,
                    ASSIGNED_BY,
                    ASSIGNED_AT
                ) VALUES (
                    :username,
                    :role_code,
                    1,
                    :actor,
                    SYSDATE
                )",
                'params' => [
                    'username' => $username,
                    'role_code' => $roleCode,
                    'actor' => $actor,
                ],
            ];
        }

        foreach ($branches as $branch) {
            $statements[] = [
                'sql' => "INSERT INTO DASH_ACL_USER_BRANCHES (
                    USERNAME,
                    HOSPITAL_CODE,
                    IS_ACTIVE,
                    UPDATED_BY,
                    UPDATED_AT
                ) VALUES (
                    :username,
                    :hospital_code,
                    1,
                    :actor,
                    SYSDATE
                )",
                'params' => [
                    'username' => $username,
                    'hospital_code' => $branch,
                    'actor' => $actor,
                ],
            ];
        }

        $statements[] = $this->auditStatement(
            $actor,
            $roleCode === '' ? 'USER_ROLE_CLEARED' : 'USER_ROLE_ASSIGNED',
            'USER',
            $username,
            json_encode([
                'roles' => $currentRoles,
                'branches' => (array)($current['branches'] ?? []),
            ], JSON_UNESCAPED_SLASHES),
            json_encode([
                'role' => $roleCode,
                'branches' => $branches,
            ], JSON_UNESCAPED_SLASHES)
        );

        Database::executeTransaction($this->hospitalCode, $statements);
    }

    /**
     * @return array{sql:string,params:array<string,mixed>}
     */
    private function auditStatement(
        string $actor,
        string $action,
        string $type,
        string $key,
        ?string $old,
        ?string $new
    ): array {
        return [
            'sql' => "INSERT INTO DASH_ACL_AUDIT (
                AUDIT_ID,
                ACTOR_USERNAME,
                ACTION_CODE,
                TARGET_TYPE,
                TARGET_KEY,
                OLD_VALUE,
                NEW_VALUE,
                CREATED_AT
            ) VALUES (
                DASH_ACL_AUDIT_SEQ.NEXTVAL,
                :actor,
                :action_code,
                :target_type,
                :target_key,
                :old_value,
                :new_value,
                SYSDATE
            )",
            'params' => [
                'actor' => $actor,
                'action_code' => $action,
                'target_type' => $type,
                'target_key' => $key,
                'old_value' => (string)$old,
                'new_value' => (string)$new,
            ],
        ];
    }
}
