<?php
/**
 * Demo Oracle — ouverture de connexion OCI8.
 *
 * Clone de modules/oracle/lib/OracleOciConnect.php. TOUTES les fonctions sont
 * prefixees demo_ : le module de demonstration et le module complet peuvent
 * cohabiter sur la meme instance, et deux fonctions de meme nom declarees dans
 * la meme requete seraient une erreur fatale.
 *
 * Helper d'origine — reusable across report pages.
 * Guard: safe to include multiple times.
 */
require_once __DIR__ . '/DemoOracleDsn.php';

if (function_exists('demo_oracle_oci_open')) return;

/**
 * Open an OCI8 connection for the given oracle DB session entry.
 * Loads the password from the SQLite application DB.
 * On success, automatically initialises the session (NLS + application info).
 * On failure, sets $ociError = ['code'=>..., 'message'=>...] and returns false.
 *
 * @param array      $oraDb    Session data from 'oracle_selected_db'
 * @param object     $sqliteDb App database object (fetchOne method)
 * @param array|null $ociError Populated on failure
 * @param string     $module   dbms_application_info module name (defaults to app title)
 * @param string     $action   dbms_application_info action name
 * @return resource|false
 */
function demo_oracle_oci_open(
    array   $oraDb,
    object  $sqliteDb,
    ?array  &$ociError = null,
    string  $module    = '',
    string  $action    = ''
): mixed {
    $ociError = null;

    if (!function_exists('oci_connect')) {
        $ociError = ['code' => 0, 'message' => t('demo_oracle.oci8_missing')];
        return false;
    }

    /* Le mot de passe et le portefeuille sont relus en base : la session ne
       porte que ce qui est affichable. */
    $rec = $sqliteDb->fetchOne(
        "SELECT conn_type, host, port, service_name, tns_alias, wallet_path,
                password, sysdba
           FROM demo_oracle_databases WHERE id = ?",
        [(int)($oraDb['id'] ?? 0)]
    );
    if (!$rec) {
        $ociError = ['code' => 0, 'message' => t('demo_oracle.db_not_found')];
        return false;
    }

    $pass   = Crypto::decrypt($rec['password'] ?? '') ?? '';
    $sysdba = !empty($rec['sysdba']);
    $mode   = $sysdba ? OCI_SYSDBA : OCI_DEFAULT;
    $user   = $oraDb['username'] ?? '';

    /*
     * La chaine de connexion est construite par DemoOracleDsn, que le test de
     * connexion appelle aussi : les deux chemins ne peuvent pas diverger.
     * Elle couvre les deux types — direct, et portefeuille Oracle Cloud.
     */
    $built = DemoOracleDsn::build($rec);
    if (!$built['ok']) {
        $ociError = ['code' => 0, 'message' => $built['msg']];
        return false;
    }
    $dsn = $built['dsn'];

    $conn = @oci_connect($user, $pass, $dsn, 'AL32UTF8', $mode);
    if (!$conn) {
        $err      = oci_error();
        $ociError = is_array($err) ? $err : ['code' => 0, 'message' => t('demo_oracle.connect_error')];
        return false;
    }

    _demo_oci_init_session($conn, $module, $action);
    return $conn;
}

/**
 * Initialise a freshly opened OCI8 session:
 *  - NLS: SORT=BINARY, DATE_FORMAT and TIMESTAMP_FORMAT in ISO-8601
 *  - dbms_session.set_identifier  → app name as client identifier
 *  - dbms_application_info.set_module → module / action
 * Errors are silently ignored (best-effort; do not break the caller).
 */
function _demo_oci_init_session($conn, string $module, string $action): void
{
    $appName = Settings::getInstance()->get('app_title', 'Application');

    // NLS settings
    $nlsStmts = [
        "ALTER SESSION SET nls_sort             = BINARY",
        "ALTER SESSION SET nls_date_format       = 'YYYY-MM-DD\"T\"HH24:MI:SS'",
        "ALTER SESSION SET nls_timestamp_format  = 'YYYY-MM-DD\"T\"HH24:MI:SS'",
    ];
    foreach ($nlsStmts as $sql) {
        $s = @oci_parse($conn, $sql);
        if ($s) { @oci_execute($s, OCI_NO_AUTO_COMMIT); oci_free_statement($s); }
    }

    // Client identifier (visible in V$SESSION.CLIENT_IDENTIFIER)
    $clientId = $appName;
    $s = @oci_parse($conn, 'BEGIN dbms_session.set_identifier(client_id => :cid); END;');
    if ($s) {
        oci_bind_by_name($s, ':cid', $clientId, 64);
        @oci_execute($s, OCI_NO_AUTO_COMMIT);
        oci_free_statement($s);
    }

    // Module / action (visible in V$SESSION.MODULE, V$SESSION.ACTION)
    $mod = $module !== '' ? $module : $appName;
    $act = $action;
    $s = @oci_parse($conn, 'BEGIN dbms_application_info.set_module(module_name => :mod, action_name => :act); END;');
    if ($s) {
        oci_bind_by_name($s, ':mod', $mod, 48);
        oci_bind_by_name($s, ':act', $act, 32);
        @oci_execute($s, OCI_NO_AUTO_COMMIT);
        oci_free_statement($s);
    }
}
