<?php

declare(strict_types=1);

require dirname(__DIR__) . '/app/bootstrap.php';

use App\Core\Database;

const R272_RELEASE = 'DIAR-6B.5-R2.7.2.1';
const R272_ROLE = 'SITE_DEVELOPER_SUPERUSER';
const R272_TRIGGER = 'DASH_ACL_LAST_ADMIN_GUARD';
const R272_ACTOR = 'DIAR6B5_R272_SUPER_ROLE';

$branch = 'njch';
$apply = in_array('--apply', $argv, true);
$validateInstalled = in_array('--validate-installed', $argv, true);
$rollback = in_array('--rollback', $argv, true);

/** @param array<string,mixed> $payload */
function r272RoleOutput(array $payload, int $code = 0): void
{
    echo json_encode(
        $payload,
        JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES
    ), PHP_EOL;
    exit($code);
}

/** @return array<string,int> */
function r272RoleReadiness(string $branch): array
{
    $tables = Database::fetchOne($branch, "SELECT COUNT(*) object_count
        FROM USER_TABLES
        WHERE TABLE_NAME IN (
            'DASH_ACL_ROLES','DASH_ACL_USER_ROLES',
            'DASH_ACL_ROLE_OVERRIDES','DASH_ACL_AUDIT'
        )");
    $columns = Database::fetchOne($branch, "SELECT COUNT(*) column_count
        FROM USER_TAB_COLUMNS
        WHERE TABLE_NAME='DASH_ACL_ROLES'
          AND COLUMN_NAME IN (
            'ROLE_DESCRIPTION_EN','ROLE_DESCRIPTION_AR','POLICY_MODE',
            'AUTO_INCLUDE_NEW','ALL_BRANCHES','IS_SYSTEM_ROLE',
            'IS_PROTECTED','IS_ACTIVE','UPDATED_BY','UPDATED_AT'
          )");
    $sequence = Database::fetchOne($branch, "SELECT COUNT(*) sequence_count
        FROM USER_SEQUENCES
        WHERE SEQUENCE_NAME='DASH_ACL_AUDIT_SEQ'");

    return [
        'tables' => (int)($tables['object_count'] ?? 0),
        'columns' => (int)($columns['column_count'] ?? 0),
        'audit_sequence' => (int)($sequence['sequence_count'] ?? 0),
    ];
}

function r272ActiveAdminCount(string $branch): int
{
    $row = Database::fetchOne($branch, "SELECT COUNT(*) admin_count
        FROM DASH_ACL_USER_ROLES ur
        JOIN DASH_ACL_ROLES r ON r.ROLE_CODE=ur.ROLE_CODE
        WHERE UPPER(ur.ROLE_CODE) IN ('SITE_ADMIN','SITE_DEVELOPER_SUPERUSER')
          AND NVL(ur.IS_ACTIVE,1)=1
          AND NVL(r.IS_ACTIVE,1)=1");
    return (int)($row['admin_count'] ?? 0);
}

function r272ActiveOriginalAdminCount(string $branch): int
{
    $row = Database::fetchOne($branch, "SELECT COUNT(*) admin_count
        FROM DASH_ACL_USER_ROLES ur
        JOIN DASH_ACL_ROLES r ON r.ROLE_CODE=ur.ROLE_CODE
        WHERE UPPER(ur.ROLE_CODE)='SITE_ADMIN'
          AND NVL(ur.IS_ACTIVE,1)=1
          AND NVL(r.IS_ACTIVE,1)=1");
    return (int)($row['admin_count'] ?? 0);
}

/** @return array<string,mixed> */
function r272InstalledState(string $branch): array
{
    $role = Database::fetchOne($branch, "SELECT COUNT(*) role_count
        FROM DASH_ACL_ROLES
        WHERE ROLE_CODE='SITE_DEVELOPER_SUPERUSER'
          AND UPPER(POLICY_MODE)='ALL'
          AND NVL(AUTO_INCLUDE_NEW,0)=1
          AND NVL(ALL_BRANCHES,0)=1
          AND NVL(IS_SYSTEM_ROLE,0)=1
          AND NVL(IS_PROTECTED,0)=1
          AND NVL(IS_ACTIVE,0)=1");
    $trigger = Database::fetchOne($branch, "SELECT COUNT(*) trigger_count
        FROM USER_TRIGGERS
        WHERE TRIGGER_NAME='DASH_ACL_LAST_ADMIN_GUARD'
          AND STATUS='ENABLED'");
    $assignments = Database::fetchOne($branch, "SELECT COUNT(*) assignment_count
        FROM DASH_ACL_USER_ROLES
        WHERE ROLE_CODE='SITE_DEVELOPER_SUPERUSER'
          AND NVL(IS_ACTIVE,1)=1");

    return [
        'role_exact' => (int)($role['role_count'] ?? 0) === 1,
        'last_admin_guard_enabled' => (int)($trigger['trigger_count'] ?? 0) === 1,
        'active_superuser_assignments' => (int)($assignments['assignment_count'] ?? 0),
        'active_site_admin_equivalents' => r272ActiveAdminCount($branch),
        'active_original_site_admins' => r272ActiveOriginalAdminCount($branch),
    ];
}

/** @return array<string,mixed>|null */
function r272ReadRole(string $branch)
{
    $row = Database::fetchOne($branch, "SELECT
        ROLE_CODE role_code,
        ROLE_NAME_EN role_name_en,
        ROLE_NAME_AR role_name_ar,
        ROLE_DESCRIPTION_EN role_description_en,
        ROLE_DESCRIPTION_AR role_description_ar,
        POLICY_MODE policy_mode,
        AUTO_INCLUDE_NEW auto_include_new,
        ALL_BRANCHES all_branches,
        IS_SYSTEM_ROLE is_system_role,
        IS_PROTECTED is_protected,
        IS_ACTIVE is_active,
        UPDATED_BY updated_by,
        TO_CHAR(UPDATED_AT,'YYYY-MM-DD HH24:MI:SS') updated_at
        FROM DASH_ACL_ROLES
        WHERE ROLE_CODE=:role_code", ['role_code' => R272_ROLE]);
    return is_array($row) && $row !== [] ? $row : null;
}

/** @return array<int,array<string,mixed>> */
function r272ReadOverrides(string $branch): array
{
    return Database::fetchAll($branch, "SELECT
        PERMISSION_CODE permission_code,
        EFFECT effect,
        UPDATED_BY updated_by,
        TO_CHAR(UPDATED_AT,'YYYY-MM-DD HH24:MI:SS') updated_at
        FROM DASH_ACL_ROLE_OVERRIDES
        WHERE ROLE_CODE=:role_code
        ORDER BY PERMISSION_CODE", ['role_code' => R272_ROLE]);
}

function r272BackupDirectory(): string
{
    return dirname(__DIR__) . '/storage/diar6b-role-backups';
}

function r272LatestPointer(): string
{
    return r272BackupDirectory() . '/DIAR_6B_5_R2_7_2_1_SUPER_ROLE_LATEST.txt';
}

function r272CreateTrigger(string $branch): void
{
    $sql = <<<'SQL'
BEGIN
  EXECUTE IMMEDIATE q'~CREATE OR REPLACE TRIGGER DASH_ACL_LAST_ADMIN_GUARD
FOR INSERT OR UPDATE OR DELETE ON DASH_ACL_USER_ROLES
COMPOUND TRIGGER
  AFTER STATEMENT IS
    v_admin_count NUMBER;
  BEGIN
    SELECT COUNT(*)
      INTO v_admin_count
      FROM DASH_ACL_USER_ROLES ur
      JOIN DASH_ACL_ROLES r ON r.ROLE_CODE=ur.ROLE_CODE
     WHERE UPPER(ur.ROLE_CODE) IN ('SITE_ADMIN','SITE_DEVELOPER_SUPERUSER')
       AND NVL(ur.IS_ACTIVE,1)=1
       AND NVL(r.IS_ACTIVE,1)=1;

    IF v_admin_count < 1 THEN
      RAISE_APPLICATION_ERROR(
        -20972,
        'At least one active site administrator must remain.'
      );
    END IF;
  END AFTER STATEMENT;
END DASH_ACL_LAST_ADMIN_GUARD;~';
END;
SQL;
    Database::executeTransaction($branch, [['sql' => $sql, 'params' => []]]);
}

function r272DropTrigger(string $branch): void
{
    $sql = "BEGIN
      EXECUTE IMMEDIATE 'DROP TRIGGER DASH_ACL_LAST_ADMIN_GUARD';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -4080 THEN RAISE; END IF;
    END;";
    Database::executeTransaction($branch, [['sql' => $sql, 'params' => []]]);
}

/** @param array<string,mixed> $role */
function r272RestoreRole(string $branch, array $role, array $overrides): void
{
    $statements = [[
        'sql' => "MERGE INTO DASH_ACL_ROLES t
            USING (SELECT :role_code role_code FROM dual) s
            ON (t.ROLE_CODE=s.role_code)
            WHEN MATCHED THEN UPDATE SET
                t.ROLE_NAME_EN=:name_en,
                t.ROLE_NAME_AR=:name_ar,
                t.ROLE_DESCRIPTION_EN=:description_en,
                t.ROLE_DESCRIPTION_AR=:description_ar,
                t.POLICY_MODE=:policy_mode,
                t.AUTO_INCLUDE_NEW=:auto_include_new,
                t.ALL_BRANCHES=:all_branches,
                t.IS_SYSTEM_ROLE=:is_system_role,
                t.IS_PROTECTED=:is_protected,
                t.IS_ACTIVE=:is_active,
                t.UPDATED_BY=:actor,
                t.UPDATED_AT=SYSDATE
            WHEN NOT MATCHED THEN INSERT (
                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,
                :is_system_role,:is_protected,:is_active,
                :actor,SYSDATE
            )",
        'params' => [
            'role_code' => (string)($role['role_code'] ?? R272_ROLE),
            'name_en' => (string)($role['role_name_en'] ?? ''),
            'name_ar' => (string)($role['role_name_ar'] ?? ''),
            'description_en' => (string)($role['role_description_en'] ?? ''),
            'description_ar' => (string)($role['role_description_ar'] ?? ''),
            'policy_mode' => (string)($role['policy_mode'] ?? 'EXPLICIT'),
            'auto_include_new' => (int)($role['auto_include_new'] ?? 0),
            'all_branches' => (int)($role['all_branches'] ?? 0),
            'is_system_role' => (int)($role['is_system_role'] ?? 0),
            'is_protected' => (int)($role['is_protected'] ?? 0),
            'is_active' => (int)($role['is_active'] ?? 1),
            'actor' => R272_ACTOR . '_ROLLBACK',
        ],
    ], [
        'sql' => "DELETE FROM DASH_ACL_ROLE_OVERRIDES WHERE ROLE_CODE=:role_code",
        'params' => ['role_code' => R272_ROLE],
    ]];

    foreach ($overrides as $override) {
        $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' => R272_ROLE,
                'permission_code' => (string)($override['permission_code'] ?? ''),
                'effect' => (string)($override['effect'] ?? 'ALLOW'),
                'actor' => R272_ACTOR . '_ROLLBACK',
            ],
        ];
    }
    Database::executeTransaction($branch, $statements);
}

try {
    $readiness = r272RoleReadiness($branch);
    $schemaReady = $readiness['tables'] === 4
        && $readiness['columns'] === 10
        && $readiness['audit_sequence'] === 1;

    if ($rollback) {
        if (!$schemaReady) {
            throw new RuntimeException('ACL_SCHEMA_NOT_READY');
        }
        $pointer = r272LatestPointer();
        $backupPath = trim((string)@file_get_contents($pointer));
        if ($backupPath === '' || !is_file($backupPath)) {
            throw new RuntimeException('SUPER_ROLE_BACKUP_NOT_FOUND');
        }
        $backup = json_decode((string)file_get_contents($backupPath), true);
        if (!is_array($backup)) {
            throw new RuntimeException('SUPER_ROLE_BACKUP_INVALID');
        }
        $state = r272InstalledState($branch);
        if ((int)$state['active_superuser_assignments'] > 0) {
            throw new RuntimeException(
                'REASSIGN_SUPERUSER_ACCOUNTS_BEFORE_ROLE_ROLLBACK'
            );
        }
        if ((int)$state['active_original_site_admins'] < 1) {
            throw new RuntimeException(
                'ROLLBACK_REQUIRES_ONE_ACTIVE_SITE_ADMIN_ASSIGNMENT'
            );
        }

        r272DropTrigger($branch);
        $oldRole = $backup['role'] ?? null;
        $oldOverrides = is_array($backup['overrides'] ?? null)
            ? $backup['overrides']
            : [];
        if (is_array($oldRole)) {
            r272RestoreRole($branch, $oldRole, $oldOverrides);
        } else {
            Database::executeTransaction($branch, [[
                'sql' => "DELETE FROM DASH_ACL_ROLE_OVERRIDES
                    WHERE ROLE_CODE=:role_code",
                'params' => ['role_code' => R272_ROLE],
            ], [
                'sql' => "DELETE FROM DASH_ACL_ROLES
                    WHERE ROLE_CODE=:role_code",
                'params' => ['role_code' => R272_ROLE],
            ]]);
        }

        r272RoleOutput([
            'result' => 'PASS',
            'mode' => 'ROLLBACK',
            'release' => R272_RELEASE,
            'restored_previous_role' => is_array($oldRole),
            'last_admin_guard' => 'REMOVED',
            'active_original_site_admins' => r272ActiveOriginalAdminCount($branch),
            'backup' => $backupPath,
        ]);
    }

    $activeAdmins = $schemaReady ? r272ActiveAdminCount($branch) : 0;
    $baseReady = $schemaReady && $activeAdmins >= 1;

    if ($validateInstalled) {
        $state = $schemaReady ? r272InstalledState($branch) : [
            'role_exact' => false,
            'last_admin_guard_enabled' => false,
            'active_superuser_assignments' => 0,
            'active_site_admin_equivalents' => 0,
            'active_original_site_admins' => 0,
        ];
        $ok = $schemaReady
            && !empty($state['role_exact'])
            && !empty($state['last_admin_guard_enabled'])
            && (int)$state['active_site_admin_equivalents'] >= 1;
        r272RoleOutput([
            'result' => $ok ? 'PASS' : 'FAIL',
            'mode' => 'VALIDATE_INSTALLED',
            'release' => R272_RELEASE,
            'readiness' => $readiness,
            'state' => $state,
            'required_minimum_site_administrators' => 1,
            'oracle_writes' => 'NONE',
        ], $ok ? 0 : 63);
    }

    if (!$apply) {
        r272RoleOutput([
            'result' => $baseReady ? 'PASS' : 'FAIL',
            'mode' => 'VALIDATE_CURRENT_ADMIN',
            'release' => R272_RELEASE,
            'readiness' => $readiness,
            'active_site_admin_equivalents' => $activeAdmins,
            'active_original_site_admins' => $schemaReady
                ? r272ActiveOriginalAdminCount($branch)
                : 0,
            'required_minimum' => 1,
            'oracle_writes' => 'NONE',
            'next_action' => $baseReady
                ? 'Install the protected full-access role and last-administrator guard.'
                : 'Keep or restore at least one active SITE_ADMIN assignment first.',
        ], $baseReady ? 0 : 61);
    }

    if (!$baseReady) {
        throw new RuntimeException(
            'AT_LEAST_ONE_ACTIVE_SITE_ADMIN_MUST_EXIST_BEFORE_INSTALL'
        );
    }

    $installed = r272InstalledState($branch);
    if (!empty($installed['role_exact'])
        && !empty($installed['last_admin_guard_enabled'])) {
        r272RoleOutput([
            'result' => 'PASS',
            'mode' => 'APPLY',
            'release' => R272_RELEASE,
            'idempotent' => true,
            'role_code' => R272_ROLE,
            'state' => $installed,
            'user_assignments' => 'NONE',
            'acl_writes' => 'NONE_ALREADY_INSTALLED',
        ]);
    }

    $existingTrigger = Database::fetchOne($branch, "SELECT COUNT(*) trigger_count
        FROM USER_TRIGGERS
        WHERE TRIGGER_NAME='DASH_ACL_LAST_ADMIN_GUARD'");
    if ((int)($existingTrigger['trigger_count'] ?? 0) > 0) {
        throw new RuntimeException('TRIGGER_NAME_CONFLICT');
    }

    $backupDir = r272BackupDirectory();
    if (!is_dir($backupDir)
        && !mkdir($backupDir, 0777, true)
        && !is_dir($backupDir)) {
        throw new RuntimeException('ROLE_BACKUP_DIRECTORY_CREATE_FAILED');
    }
    $backupPath = $backupDir
        . '/DIAR_6B_5_R2_7_2_1_super_role_'
        . date('Ymd_His') . '.json';
    $backup = [
        'release' => R272_RELEASE,
        'created_at' => date(DATE_ATOM),
        'role' => r272ReadRole($branch),
        'overrides' => r272ReadOverrides($branch),
        'trigger_existed' => false,
    ];
    if (file_put_contents(
        $backupPath,
        json_encode(
            $backup,
            JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES
        )
    ) === false) {
        throw new RuntimeException('ROLE_BACKUP_WRITE_FAILED');
    }

    $statements = [[
        'sql' => "MERGE INTO DASH_ACL_ROLES t
            USING (SELECT :role_code role_code FROM dual) s
            ON (t.ROLE_CODE=s.role_code)
            WHEN MATCHED THEN UPDATE SET
                t.ROLE_NAME_EN=:name_en,
                t.ROLE_NAME_AR=:name_ar,
                t.ROLE_DESCRIPTION_EN=:description_en,
                t.ROLE_DESCRIPTION_AR=:description_ar,
                t.POLICY_MODE='ALL',
                t.AUTO_INCLUDE_NEW=1,
                t.ALL_BRANCHES=1,
                t.IS_SYSTEM_ROLE=1,
                t.IS_PROTECTED=1,
                t.IS_ACTIVE=1,
                t.UPDATED_BY=:actor,
                t.UPDATED_AT=SYSDATE
            WHEN NOT MATCHED THEN INSERT (
                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,
                'ALL',1,1,1,1,1,:actor,SYSDATE
            )",
        'params' => [
            'role_code' => R272_ROLE,
            'name_en' => 'Site Developer & Full-Test Administrator',
            'name_ar' => 'مدير الموقع ومطور الاختبار الشامل',
            'description_en' => 'Protected full-site role for administration, development, diagnostics and complete acceptance testing.',
            'description_ar' => 'دور محمي بصلاحيات مطلقة لإدارة الموقع والتطوير والتشخيص والاختبار الشامل.',
            'actor' => R272_ACTOR,
        ],
    ], [
        'sql' => "DELETE FROM DASH_ACL_ROLE_OVERRIDES
            WHERE ROLE_CODE=:role_code",
        'params' => ['role_code' => R272_ROLE],
    ], [
        '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,
            'FULL_TEST_SUPER_ROLE_UPSERT','ROLE',:role_code,
            NULL,:new_value,SYSDATE
        )",
        'params' => [
            'actor' => R272_ACTOR,
            'role_code' => R272_ROLE,
            'new_value' => 'POLICY_MODE=ALL|AUTO_INCLUDE_NEW=1|ALL_BRANCHES=1|PROTECTED=1',
        ],
    ]];
    Database::executeTransaction($branch, $statements);
    r272CreateTrigger($branch);
    file_put_contents(r272LatestPointer(), $backupPath);

    $after = r272InstalledState($branch);
    $success = !empty($after['role_exact'])
        && !empty($after['last_admin_guard_enabled'])
        && (int)$after['active_site_admin_equivalents'] >= 1;
    if (!$success) {
        throw new RuntimeException('POST_INSTALL_VALIDATION_FAILED');
    }

    r272RoleOutput([
        'result' => 'PASS',
        'mode' => 'APPLY',
        'release' => R272_RELEASE,
        'idempotent' => false,
        'role_code' => R272_ROLE,
        'policy_mode' => 'ALL',
        'auto_include_new' => true,
        'all_branches' => true,
        'is_system_role' => true,
        'is_protected' => true,
        'state' => $after,
        'last_admin_guard' => 'ENABLED',
        'backup' => $backupPath,
        'user_assignments' => 'NONE',
        'acl_writes' => 'ROLE_AND_GUARD_ONLY',
        'operational_oracle_writes' => 'NONE',
    ]);
} catch (Throwable $e) {
    r272RoleOutput([
        'result' => 'FAIL',
        'release' => R272_RELEASE,
        'error' => preg_replace('/\s+/', ' ', $e->getMessage()),
    ], 62);
}
