<?php

declare(strict_types=1);

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

use App\Core\Database;

$branch = strtolower(trim((string)($argv[1] ?? 'njch')));

try {
    $duplicates = Database::fetchOne($branch, "
        SELECT COUNT(*) duplicate_users
        FROM (
            SELECT UPPER(USERNAME)
            FROM DASH_ACL_USER_ROLES
            GROUP BY UPPER(USERNAME)
            HAVING COUNT(*) > 1
        )
    ");

    if ((int)($duplicates['duplicate_users'] ?? 0) > 0) {
        throw new RuntimeException('MULTIPLE_ROLE_ROWS_MUST_BE_RESOLVED');
    }

    $ddl = [
        "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 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;",
    ];

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

    Database::execute($branch, "
        UPDATE DASH_ACL_ROLES
        SET ROLE_DESCRIPTION_EN =
            CASE ROLE_CODE
                WHEN 'SITE_ADMIN' THEN
                    'Full system access including settings, security and all active branches.'
                WHEN 'GENERAL_MANAGER' THEN
                    'All business pages automatically; settings and security are excluded by default.'
                ELSE ROLE_DESCRIPTION_EN
            END,
            ROLE_DESCRIPTION_AR =
            CASE ROLE_CODE
                WHEN 'SITE_ADMIN' THEN
                    'صلاحيات كاملة للنظام والإعدادات والأمن وجميع الفروع النشطة.'
                WHEN 'GENERAL_MANAGER' THEN
                    'جميع صفحات العمل تلقائيًا مع استبعاد الإعدادات والأمن افتراضيًا.'
                ELSE ROLE_DESCRIPTION_AR
            END,
            UPDATED_BY = 'PATCH_P14_2',
            UPDATED_AT = SYSDATE
        WHERE ROLE_CODE IN (
            'SITE_ADMIN',
            'GENERAL_MANAGER'
        )
          AND (
            ROLE_DESCRIPTION_EN IS NULL
            OR ROLE_DESCRIPTION_AR IS NULL
          )
    ");

    $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'
          )
    ");

    $index = Database::fetchOne($branch, "
        SELECT COUNT(*) index_count
        FROM USER_INDEXES
        WHERE INDEX_NAME = 'UX_DASH_ACL_USER_ONE_ROLE'
    ");

    $checks = [
        'description_columns_ready' =>
            (int)($columns['column_count'] ?? 0) === 2,
        'one_role_unique_index_ready' =>
            (int)($index['index_count'] ?? 0) === 1,
        'duplicate_role_rows' =>
            (int)($duplicates['duplicate_users'] ?? 0) === 0,
    ];

    $failed = [];

    foreach ($checks as $name => $passed) {
        if (!$passed) {
            $failed[] = $name;
        }
    }

    $result = [
        'schema' => 'DDB_ACCESS_CONTROL_ROLE_UPGRADE_V1',
        'captured_at' => date(DATE_ATOM),
        'branch' => strtoupper($branch),
        'checks' => $checks,
        'failed' => $failed,
        'status' => $failed === [] ? 'PASS' : 'FAIL',
        'oracle_writes' => 'DDL_AND_CONTROL_METADATA_ONLY',
    ];
} catch (Throwable $error) {
    $result = [
        'schema' => 'DDB_ACCESS_CONTROL_ROLE_UPGRADE_V1',
        'captured_at' => date(DATE_ATOM),
        'branch' => strtoupper($branch),
        'status' => 'FAIL',
        'failed' => ['upgrade_execution'],
        'error' => preg_replace('/\s+/', ' ', (string)$error->getMessage()),
        'oracle_writes' => 'UPGRADE_ATTEMPT',
    ];
}

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

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