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

Usage:
    python mysql_collector.py --host 10.0.1.60 --port 3306 \
        --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 mysql.connector
except ImportError:
    print("ERROR: mysql-connector-python not installed. Run: pip install mysql-connector-python")
    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 MySQLCollector:
    def __init__(self, host, port, user, password, ssl=False):
        self.config = {
            "host": host,
            "port": port,
            "user": user,
            "password": password,
            "database": "information_schema",
        }
        if ssl:
            self.config["ssl_disabled"] = False
        self.conn = None

    def connect(self):
        self.conn = mysql.connector.connect(**self.config)

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

    def _query(self, sql):
        cursor = self.conn.cursor(dictionary=True)
        cursor.execute(sql)
        rows = cursor.fetchall()
        cursor.close()
        return rows

    def get_instance_facts(self):
        rows = self._query("SELECT VERSION() AS ver, @@hostname AS host_name")
        r = rows[0]
        uptime = self._query("SHOW STATUS LIKE 'Uptime'")
        uptime_min = int(int(uptime[0]["Value"]) / 60) if uptime else None
        return {
            "version": r["ver"],
            "edition": "Enterprise" if "enterprise" in r["ver"].lower() else "Community",
            "build": r["ver"],
            "uptime": uptime_min,
        }

    def get_telemetry(self):
        threads = self._query("SHOW STATUS LIKE 'Threads_running'")
        conns = self._query("SHOW STATUS LIKE 'Threads_connected'")
        return {
            "connectionsActive": int(threads[0]["Value"]) if threads else 0,
            "connectionsTotal": int(conns[0]["Value"]) if conns else 0,
            "collectedAt": utcnow(),
        }

    def get_databases(self):
        return self._query("""
            SELECT s.SCHEMA_NAME AS name, 'ONLINE' AS state,
                   ROUND(SUM(COALESCE(t.DATA_LENGTH,0) + COALESCE(t.INDEX_LENGTH,0))/1048576) AS size_mb
            FROM information_schema.SCHEMATA s
            LEFT JOIN information_schema.TABLES t ON s.SCHEMA_NAME = t.TABLE_SCHEMA
            WHERE s.SCHEMA_NAME NOT IN ('information_schema','performance_schema','mysql','sys')
            GROUP BY s.SCHEMA_NAME
        """)

    def get_wait_events(self):
        try:
            return self._query("""
                SELECT EVENT_NAME AS event_name,
                       SUM_TIMER_WAIT/1000000000 AS wait_time_ms,
                       COUNT_STAR AS count
                FROM performance_schema.events_waits_summary_global_by_event_name
                WHERE SUM_TIMER_WAIT > 0
                ORDER BY SUM_TIMER_WAIT DESC
                LIMIT 50
            """)
        except Exception:
            return []

    def get_top_queries(self):
        try:
            return self._query("""
                SELECT DIGEST AS query_hash,
                       LEFT(DIGEST_TEXT, 1000) AS query_text,
                       COUNT_STAR AS executions,
                       ROUND(SUM_TIMER_WAIT/1000000000) AS total_cpu_ms,
                       ROUND(AVG_TIMER_WAIT/1000000000) AS avg_duration_ms,
                       SUM_ROWS_EXAMINED AS total_reads
                FROM performance_schema.events_statements_summary_by_digest
                WHERE SCHEMA_NAME IS NOT NULL
                ORDER BY SUM_TIMER_WAIT DESC
                LIMIT 50
            """)
        except Exception:
            return []


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 MySQL Collector")
    parser.add_argument("--host", required=True)
    parser.add_argument("--port", type=int, default=3306)
    parser.add_argument("--user", required=True)
    parser.add_argument("--password", required=True)
    parser.add_argument("--ssl", action="store_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} ...")
        collector = MySQLCollector(args.host, args.port, args.user, args.password, args.ssl)
        try:
            collector.connect()
            server_name = f"{args.host}:{args.port}"

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

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

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

            queries = collector.get_top_queries()
            if queries:
                submit_to_api(args.api, args.api_key, "/api/ingest/query-stats", {
                    "serverName": server_name, "engine": "MySQL",
                    "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()
