"""
POC: Oracle async connectivity test — Săpt 1 Gate Correction 9

Tests native async oracledb.connect_async() vs sync baseline.
Decision gate: if async latency acceptable → keep async-native (ad-hoc pattern).
If degraded → use sync-facade with asyncio.run_in_executor.

Usage:
    cd /workspace/roa2web
    backend/venv/bin/python poc/hello_oracle_async.py
"""
import asyncio
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()


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

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

    async with conn.cursor() as cur:
        # Basic connectivity
        t1 = time.perf_counter()
        await cur.execute("SELECT 1 FROM DUAL")
        row = await cur.fetchone()
        t_dual = time.perf_counter() - t1
        print(f"[ASYNC] SELECT 1 FROM DUAL = {row[0]} ({t_dual*1000:.1f}ms)")

        # SYSDATE
        t2 = time.perf_counter()
        await cur.execute("SELECT SYSDATE FROM DUAL")
        row = await cur.fetchone()
        t_date = time.perf_counter() - t2
        print(f"[ASYNC] SELECT SYSDATE = {row[0]} ({t_date*1000:.1f}ms)")

        # DEV_ORDL access
        t3 = time.perf_counter()
        await cur.execute("SELECT COUNT(*) FROM MARIUSM_AUTO.DEV_ORDL WHERE ROWNUM <= 1")
        row = await cur.fetchone()
        t_table = time.perf_counter() - t3
        print(f"[ASYNC] SELECT COUNT(*) DEV_ORDL = {row[0]} ({t_table*1000:.1f}ms)")

        # Parallel queries test (proves concurrency benefit of async)
        print("[ASYNC] All queries via single cursor OK")

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


async def test_async_pool():
    """
    Test sync pool in async context — matches existing oracle_pool.py pattern.

    Existing codebase uses oracledb.create_pool() (sync) + pool.acquire() (sync)
    + sync cursors inside async def. This is the established sync-facade pattern.
    Gate Correction 9: keep this pattern for service_auto (consistency).
    """
    print("\n[POOL] Testing sync pool in async context (existing pattern)...")
    password = read_password()

    t0 = time.perf_counter()
    pool = oracledb.create_pool(
        user=USER,
        password=password,
        host=HOST,
        port=PORT,
        service_name=SERVICE_NAME,
        min=1,
        max=3,
        increment=1
    )
    t_pool = time.perf_counter() - t0
    print(f"[POOL] Pool created in {t_pool*1000:.1f}ms (min=1, max=3)")

    # sync acquire in async def — matches oracle_pool.py:165
    conn = pool.acquire()
    with conn.cursor() as cur:
        t1 = time.perf_counter()
        cur.execute("SELECT 1 FROM DUAL")
        row = cur.fetchone()
        t_q = time.perf_counter() - t1
        print(f"[POOL] Sync query via pool: {row[0]} ({t_q*1000:.1f}ms)")
    conn.close()

    pool.close()
    print(f"[POOL] Pool closed ✅")


async def main():
    await test_async_connect()
    await test_async_pool()

    print("\n" + "="*50)
    print("GATE Correction 9 Summary:")
    print("  If latencies above are <50ms → async-native OK")
    print("  If >200ms or errors → use sync-facade pattern")
    print("="*50)


if __name__ == "__main__":
    asyncio.run(main())