<?php

declare(strict_types=1);

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

use App\Core\Database;

$branch = strtolower(trim((string)($argv[1] ?? 'njch')));
$siteAdmin = trim((string)($argv[2] ?? '7124'));
$generalManagers = array_values(array_filter(array_map(
    'trim',
    explode(',', (string)($argv[3] ?? ''))
)));

$ddl = [
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE DASH_ACL_ROLES (
            ROLE_CODE VARCHAR2(50) PRIMARY KEY,
            ROLE_NAME_EN VARCHAR2(120) NOT NULL,
            ROLE_NAME_AR VARCHAR2(120),
            ROLE_DESCRIPTION_EN VARCHAR2(500),
            ROLE_DESCRIPTION_AR VARCHAR2(500),
            POLICY_MODE VARCHAR2(30) NOT NULL,
            AUTO_INCLUDE_NEW NUMBER(1) DEFAULT 0 NOT NULL,
            ALL_BRANCHES NUMBER(1) DEFAULT 0 NOT NULL,
            IS_SYSTEM_ROLE NUMBER(1) DEFAULT 0 NOT NULL,
            IS_PROTECTED NUMBER(1) DEFAULT 0 NOT NULL,
            IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,
            UPDATED_BY VARCHAR2(128),
            UPDATED_AT DATE DEFAULT SYSDATE NOT NULL
        )';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE '
            ALTER TABLE DASH_ACL_ROLES
            ADD ROLE_DESCRIPTION_EN VARCHAR2(500)
        ';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -1430 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE '
            ALTER TABLE DASH_ACL_ROLES
            ADD ROLE_DESCRIPTION_AR VARCHAR2(500)
        ';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -1430 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE DASH_ACL_USER_ROLES (
            USERNAME VARCHAR2(128) NOT NULL,
            ROLE_CODE VARCHAR2(50) NOT NULL,
            IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,
            ASSIGNED_BY VARCHAR2(128),
            ASSIGNED_AT DATE DEFAULT SYSDATE NOT NULL,
            CONSTRAINT PK_DASH_ACL_USER_ROLES
                PRIMARY KEY (USERNAME, ROLE_CODE),
            CONSTRAINT FK_DASH_ACL_UR_ROLE
                FOREIGN KEY (ROLE_CODE)
                REFERENCES DASH_ACL_ROLES(ROLE_CODE)
        )';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX
            UX_DASH_ACL_USER_ONE_ROLE
            ON DASH_ACL_USER_ROLES (UPPER(USERNAME))';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE DASH_ACL_ROLE_OVERRIDES (
            ROLE_CODE VARCHAR2(50) NOT NULL,
            PERMISSION_CODE VARCHAR2(150) NOT NULL,
            EFFECT VARCHAR2(10) NOT NULL,
            UPDATED_BY VARCHAR2(128),
            UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
            CONSTRAINT PK_DASH_ACL_ROLE_OVR
                PRIMARY KEY (ROLE_CODE, PERMISSION_CODE),
            CONSTRAINT FK_DASH_ACL_OVR_ROLE
                FOREIGN KEY (ROLE_CODE)
                REFERENCES DASH_ACL_ROLES(ROLE_CODE),
            CONSTRAINT CK_DASH_ACL_OVR_EFFECT
                CHECK (EFFECT IN (''ALLOW'', ''DENY''))
        )';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE DASH_ACL_USER_BRANCHES (
            USERNAME VARCHAR2(128) NOT NULL,
            HOSPITAL_CODE VARCHAR2(20) NOT NULL,
            IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,
            UPDATED_BY VARCHAR2(128),
            UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
            CONSTRAINT PK_DASH_ACL_USER_BRANCH
                PRIMARY KEY (USERNAME, HOSPITAL_CODE)
        )';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE TABLE DASH_ACL_AUDIT (
            AUDIT_ID NUMBER NOT NULL,
            ACTOR_USERNAME VARCHAR2(128) NOT NULL,
            ACTION_CODE VARCHAR2(80) NOT NULL,
            TARGET_TYPE VARCHAR2(40) NOT NULL,
            TARGET_KEY VARCHAR2(180) NOT NULL,
            OLD_VALUE VARCHAR2(4000),
            NEW_VALUE VARCHAR2(4000),
            CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
            CONSTRAINT PK_DASH_ACL_AUDIT
                PRIMARY KEY (AUDIT_ID)
        )';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
    "BEGIN
        EXECUTE IMMEDIATE 'CREATE SEQUENCE
            DASH_ACL_AUDIT_SEQ
            START WITH 1
            INCREMENT BY 1
            NOCACHE';
     EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN
                RAISE;
            END IF;
     END;",
];

try {
    foreach ($ddl as $statement) {
        Database::execute($branch, $statement);
    }

    $roles = [
        [
            'SITE_ADMIN',
            'Site Administrator',
            'مدير الموقع',
            'Full system access including settings, security and all active branches.',
            'صلاحيات كاملة للنظام والإعدادات والأمن وجميع الفروع النشطة.',
            'ALL',
            1,
            1,
            1,
            1,
        ],
        [
            'GENERAL_MANAGER',
            'General Manager / Owner',
            'المدير العام / صاحب العمل',
            'All business pages automatically; settings and security are excluded by default.',
            'جميع صفحات العمل تلقائيًا مع استبعاد الإعدادات والأمن افتراضيًا.',
            'ALL_BUSINESS',
            1,
            1,
            1,
            1,
        ],
    ];

    foreach ($roles as $role) {
        Database::execute($branch, "
            MERGE INTO DASH_ACL_ROLES target_role
            USING (
                SELECT :role_code role_code
                FROM dual
            ) source_role
            ON (
                target_role.ROLE_CODE =
                source_role.role_code
            )
            WHEN MATCHED THEN UPDATE 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_new,
                ALL_BRANCHES = :all_branches,
                IS_SYSTEM_ROLE = :is_system,
                IS_PROTECTED = :is_protected,
                IS_ACTIVE = 1,
                UPDATED_BY = 'PATCH_P14',
                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_new,
                :all_branches,
                :is_system,
                :is_protected,
                1,
                'PATCH_P14',
                SYSDATE
            )
        ", [
            'role_code' => $role[0],
            'name_en' => $role[1],
            'name_ar' => $role[2],
            'description_en' => $role[3],
            'description_ar' => $role[4],
            'policy_mode' => $role[5],
            'auto_new' => $role[6],
            'all_branches' => $role[7],
            'is_system' => $role[8],
            'is_protected' => $role[9],
        ]);
    }

    $assign = static function (
        string $username,
        string $roleCode
    ) use ($branch): void {
        if ($username === '') {
            return;
        }

        Database::execute($branch, "
            DELETE FROM DASH_ACL_USER_ROLES
            WHERE UPPER(USERNAME) = UPPER(:username)
        ", ['username' => $username]);

        Database::execute($branch, "
            INSERT INTO DASH_ACL_USER_ROLES (
                USERNAME,
                ROLE_CODE,
                IS_ACTIVE,
                ASSIGNED_BY,
                ASSIGNED_AT
            ) VALUES (
                :username,
                :role_code,
                1,
                'PATCH_P14',
                SYSDATE
            )
        ", [
            'username' => $username,
            'role_code' => $roleCode,
        ]);
    };

    $assign($siteAdmin, 'SITE_ADMIN');

    foreach ($generalManagers as $username) {
        if (strcasecmp($username, $siteAdmin) === 0) {
            continue;
        }

        $assign($username, 'GENERAL_MANAGER');

        Database::execute($branch, "
            MERGE INTO DASH_ACL_USER_BRANCHES target_branch
            USING (
                SELECT
                    :username username,
                    '*' hospital_code
                FROM dual
            ) source_branch
            ON (
                UPPER(target_branch.USERNAME) =
                    UPPER(source_branch.username)
                AND target_branch.HOSPITAL_CODE =
                    source_branch.hospital_code
            )
            WHEN MATCHED THEN UPDATE SET
                IS_ACTIVE = 1,
                UPDATED_BY = 'PATCH_P14',
                UPDATED_AT = SYSDATE
            WHEN NOT MATCHED THEN INSERT (
                USERNAME,
                HOSPITAL_CODE,
                IS_ACTIVE,
                UPDATED_BY,
                UPDATED_AT
            ) VALUES (
                :username,
                '*',
                1,
                'PATCH_P14',
                SYSDATE
            )
        ", ['username' => $username]);
    }

    Database::execute($branch, "
        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,
            'PATCH_P14',
            'SECURITY_SCHEMA_INSTALLED',
            'SYSTEM',
            'H11.0-P14.0',
            NULL,
            :new_value,
            SYSDATE
        )
    ", [
        'new_value' => json_encode([
            'site_admin' => $siteAdmin,
            'general_managers' => $generalManagers,
        ]),
    ]);

    $counts = Database::fetchOne($branch, "
        SELECT
            (SELECT COUNT(*) FROM DASH_ACL_ROLES)
                role_count,
            (
                SELECT COUNT(*)
                FROM DASH_ACL_USER_ROLES
                WHERE NVL(IS_ACTIVE, 1) = 1
            ) assignment_count
        FROM dual
    ");

    $result = [
        'schema' => 'DDB_ACCESS_CONTROL_INSTALL_V2',
        'status' => 'PASS',
        'branch' => strtoupper($branch),
        'site_admin' => $siteAdmin,
        'general_managers' => $generalManagers,
        'role_count' => (int)($counts['role_count'] ?? 0),
        'assignment_count' => (int)($counts['assignment_count'] ?? 0),
        'oracle_writes' => 'DDL_AND_CONTROL_DATA_ONLY',
    ];
} catch (Throwable $error) {
    $result = [
        'schema' => 'DDB_ACCESS_CONTROL_INSTALL_V2',
        'status' => 'FAIL',
        'branch' => strtoupper($branch),
        'error' => preg_replace(
            '/password=[^;\s]+/i',
            'password=[redacted]',
            preg_replace('/\s+/', ' ', $error->getMessage())
        ),
        'oracle_writes' => 'INSTALL_ATTEMPT',
    ];
}

echo json_encode(
    $result,
    JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES
), PHP_EOL;

exit(($result['status'] ?? 'FAIL') === 'PASS' ? 0 : 1);
