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

Tests direct sync connection to MARIUSM_AUTO (central server).
Measures latency for SELECT 1 FROM DUAL + real table access.

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

# Add backend to path so secrets/ is accessible
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..', 'backend'))

import oracledb

# Central server config (MARIUSM_AUTO)
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_sync_connect():
    print(f"[SYNC] 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"[SYNC] Connected in {t_connect*1000:.1f}ms")

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

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

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

        # DEV_TIP_DEVIZ enum check
        t4 = time.perf_counter()
        cur.execute("SELECT id_tip, denumire, inch_validare FROM MARIUSM_AUTO.DEV_TIP_DEVIZ ORDER BY id_tip")
        rows = cur.fetchall()
        t_enum = time.perf_counter() - t4
        print(f"[SYNC] DEV_TIP_DEVIZ ({t_enum*1000:.1f}ms):")
        for r in rows:
            print(f"       {r[0]:3} | {r[1]:<20} | inch_validare={r[2]}")

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


if __name__ == "__main__":
    test_sync_connect()