#!/usr/bin/env python3
"""
DBAOps Sovereign™ — Oracle Database Fleet Collector
Collects telemetry, instance facts, wait events, and top SQL
from Oracle instances and submits to the Sovereign API.

Usage:
    python oracle_collector.py --host 10.0.1.70 --port 1521 --service ORCL \
        --user sovereign_reader --password <pwd> \
        --api http://localhost:3041 --api-key <key>

Powered by RHOYLLE INNOVATION EMPIRE LLC
"""

import argparse
import json
import sys
import time
from datetime import datetime, timezone

try:
    import cx_Oracle
except ImportError:
    try:
        import oracledb as cx_Oracle
    except ImportError:
        print("ERROR: cx_Oracle or oracledb not installed. Run: pip install cx_Oracle")
        sys.exit(1)

try:
    import requests
except ImportError:
    print("ERROR: requests not installed. Run: pip install requests")
    sys.exit(1)


def utcnow():
    return datetime.now(timezone.utc).isoformat()


class OracleCollector:
    def __init__(self, host, port, service, user, password):
        self.dsn = cx_Oracle.makedsn(host, port, service_name=service)
        self.user = user
        self.password = password
        self.conn = None

    def connect(self):
        self.conn = cx_Oracle.connect(self.user, self.password, self.dsn)

    def close(self):
        if self.conn:
            self.conn.close()

    def _query(self, sql):
        cursor = self.conn.cursor()
        cursor.execute(sql)
        columns = [d[0].lower() for d in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]

    def get_instance_facts(self):
        rows = self._query("""
            SELECT BANNER AS full_version, VERSION_FULL AS version
            FROM V$VERSION WHERE ROWNUM = 1
        """)
        uptime = self._query("""
            SELECT ROUND((SYSDATE - STARTUP_TIME) * 24 * 60) AS uptime_min
            FROM V$INSTANCE
        """)
        r = rows[0] if rows else {}
        return {
            "version": r.get("version", r.get("full_version")),
            "edition": "Enterprise" if "Enterprise" in r.get("full_version", "") else "Standard",
            "build": r.get("version"),
            "uptime": uptime[0]["uptime_min"] if uptime else None,
        }

    def get_telemetry(self):
        cpu = self._query("""
            SELECT VALUE AS cpu_pct FROM V$SYSMETRIC
            WHERE METRIC_NAME = 'Host CPU Utilization (%)'
            AND ROWNUM = 1
        """)
        sessions = self._query("""
            SELECT COUNT(*) AS active FROM V$SESSION
            WHERE STATUS = 'ACTIVE' AND TYPE = 'USER'
        """)
        return {
            "cpuPercent": cpu[0]["cpu_pct"] if cpu else None,
            "connectionsActive": sessions[0]["active"] if sessions else 0,
            "collectedAt": utcnow(),
        }

    def get_wait_events(self):
        return self._query("""
            SELECT EVENT AS event_name, WAIT_CLASS AS wait_class,
                   TIME_WAITED_MICRO / 1000 AS wait_time_ms,
                   TOTAL_WAITS AS total_waits
            FROM V$SYSTEM_EVENT
            WHERE WAIT_CLASS != 'Idle'
            ORDER BY TIME_WAITED_MICRO DESC
            FETCH FIRST 50 ROWS ONLY
        """)

    def get_top_sql(self):
        return self._query("""
            SELECT SQL_ID AS sql_id,
                   SUBSTR(SQL_TEXT, 1, 1000) AS sql_text,
                   EXECUTIONS AS executions,
                   ROUND(CPU_TIME/1000) AS cpu_ms,
                   BUFFER_GETS AS buffer_gets,
                   ROUND(ELAPSED_TIME/1000) AS elapsed_ms
            FROM V$SQLSTATS
            ORDER BY CPU_TIME DESC
            FETCH FIRST 50 ROWS ONLY
        """)

    def get_tablespace_usage(self):
        return self._query("""
            SELECT TABLESPACE_NAME AS tablespace_name,
                   ROUND(USED_SPACE * 8192 / 1048576) AS used_mb,
                   ROUND(TABLESPACE_SIZE * 8192 / 1048576) AS total_mb,
                   ROUND(USED_PERCENT, 1) AS used_pct
            FROM DBA_TABLESPACE_USAGE_METRICS
            ORDER BY USED_PERCENT DESC
        """)


def submit_to_api(api_url, api_key, endpoint, payload):
    url = f"{api_url}{endpoint}"
    headers = {"Content-Type": "application/json"}
    if api_key:
        headers["X-Sovereign-Key"] = api_key
    try:
        resp = requests.post(url, json=payload, headers=headers, timeout=30)
        if resp.status_code >= 400:
            print(f"  WARNING: {endpoint} returned {resp.status_code}: {resp.text[:200]}")
        else:
            print(f"  OK: {endpoint}")
    except Exception as e:
        print(f"  ERROR: {endpoint} — {e}")


def main():
    parser = argparse.ArgumentParser(description="DBAOps Sovereign Oracle Collector")
    parser.add_argument("--host", required=True)
    parser.add_argument("--port", type=int, default=1521)
    parser.add_argument("--service", required=True, help="Oracle service name")
    parser.add_argument("--user", required=True)
    parser.add_argument("--password", required=True)
    parser.add_argument("--api", required=True, help="Sovereign API base URL")
    parser.add_argument("--api-key", default="")
    parser.add_argument("--interval", type=int, default=0)
    args = parser.parse_args()

    while True:
        print(f"\n[{utcnow()}] Collecting from {args.host}:{args.port}/{args.service} ...")
        collector = OracleCollector(args.host, args.port, args.service, args.user, args.password)
        try:
            collector.connect()
            server_name = f"{args.host}:{args.port}/{args.service}"

            facts = collector.get_instance_facts()
            submit_to_api(args.api, args.api_key, "/api/ingest/fleet-facts", {
                "serverName": server_name, "engine": "Oracle", "facts": facts,
            })

            telemetry = collector.get_telemetry()
            submit_to_api(args.api, args.api_key, "/api/telemetry/snapshot", {
                "serverName": server_name, "engine": "Oracle", **telemetry,
            })

            waits = collector.get_wait_events()
            submit_to_api(args.api, args.api_key, "/api/ingest/wait-stats", {
                "serverName": server_name, "engine": "Oracle",
                "waits": [dict(r) for r in waits],
            })

            queries = collector.get_top_sql()
            if queries:
                submit_to_api(args.api, args.api_key, "/api/ingest/query-stats", {
                    "serverName": server_name, "engine": "Oracle",
                    "queries": [dict(r) for r in queries],
                })

            print(f"  Collection complete for {server_name}")
        except Exception as e:
            print(f"  ERROR: {e}")
        finally:
            collector.close()

        if args.interval <= 0:
            break
        print(f"  Sleeping {args.interval}s ...")
        time.sleep(args.interval)


if __name__ == "__main__":
    main()
