"""
POC: Oracle OUT param probe — Săpt 2 Gate

Tests that oracledb cursor.var(oracledb.NUMBER) correctly round-trips an
Oracle OUT NUMBER parameter via cursor.callproc().

Steps:
    1. Connect (sync, same pattern as hello_oracle.py)
    2. CREATE OR REPLACE a minimal test procedure with OUT param
    3. callproc → assert OUT value == 42
    4. DROP the procedure (cleanup)
    5. Print timing summary

Usage:
    cd /workspace/roa2web
    backend/venv/bin/python poc/async_out_param_probe.py
"""
import time
import sys
import os

sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..', 'backend'))

import oracledb

HOST = "10.0.20.121"
PORT = 1521
SERVICE_NAME = "ROA"
USER = "CONTAFIN_ORACLE"
SECRETS_FILE = os.path.join(os.path.dirname(__file__), '..', 'backend', 'secrets', 'central.oracle_pass')


def read_password() -> str:
    with open(SECRETS_FILE) as f:
        return f.read().strip()


def test_out_param():
    print(f"[OUT_PROBE] Connecting to {HOST}:{PORT}/{SERVICE_NAME} as {USER}")
    password = read_password()

    t0 = time.perf_counter()
    conn = oracledb.connect(
        user=USER,
        password=password,
        host=HOST,
        port=PORT,
        service_name=SERVICE_NAME,
    )
    t_connect = time.perf_counter() - t0
    print(f"[OUT_PROBE] Connected in {t_connect*1000:.1f}ms")

    with conn.cursor() as cursor:
        # --- Step 1: CREATE the test procedure ---
        t1 = time.perf_counter()
        cursor.execute("""
            CREATE OR REPLACE PROCEDURE MARIUSM_AUTO.test_out(p OUT NUMBER) AS
            BEGIN
                p := 42;
            END;
        """)
        t_create = time.perf_counter() - t1
        print(f"[OUT_PROBE] CREATE PROCEDURE in {t_create*1000:.1f}ms")

        # --- Step 2: Call the procedure via callproc ---
        t2 = time.perf_counter()
        out_var = cursor.var(oracledb.NUMBER)
        cursor.callproc('MARIUSM_AUTO.test_out', [out_var])
        t_call = time.perf_counter() - t2
        result = out_var.getvalue()
        print(f"[OUT_PROBE] callproc → out_var.getvalue() = {result} ({t_call*1000:.1f}ms)")

        # --- Step 3: Assert ---
        assert result == 42, f"Expected 42, got {result}"
        print("[OUT_PROBE] ASSERT PASSED: OUT param == 42 ✅")

        # --- Step 4: DROP the procedure (cleanup) ---
        t3 = time.perf_counter()
        cursor.execute("DROP PROCEDURE MARIUSM_AUTO.test_out")
        t_drop = time.perf_counter() - t3
        print(f"[OUT_PROBE] DROP PROCEDURE in {t_drop*1000:.1f}ms — cleanup OK ✅")

    conn.close()
    total = time.perf_counter() - t0
    print(f"[OUT_PROBE] Total: {total*1000:.1f}ms ✅")

    return {
        "connect_ms": round(t_connect * 1000, 1),
        "create_ms": round(t_create * 1000, 1),
        "callproc_ms": round(t_call * 1000, 1),
        "drop_ms": round(t_drop * 1000, 1),
        "total_ms": round(total * 1000, 1),
        "out_value": result,
    }


if __name__ == "__main__":
    stats = test_out_param()
    print(f"\n[OUT_PROBE] Summary: connect={stats['connect_ms']}ms "
          f"callproc={stats['callproc_ms']}ms total={stats['total_ms']}ms")