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

Usage:
    python postgresql_collector.py --host 10.0.1.50 --port 5432 \
        --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 psycopg2
    import psycopg2.extras
except ImportError:
    print("ERROR: psycopg2 not installed. Run: pip install psycopg2-binary")
    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 PostgreSQLCollector:
    def __init__(self, host, port, database, user, password, ssl=False):
        self.conn_params = {
            "host": host,
            "port": port,
            "dbname": database,
            "user": user,
            "password": password,
        }
        if ssl:
            self.conn_params["sslmode"] = "require"
        self.conn = None

    def connect(self):
        self.conn = psycopg2.connect(**self.conn_params)
        self.conn.autocommit = True

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

    def _query(self, sql):
        with self.conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
            cur.execute(sql)
            return cur.fetchall()

    def get_instance_facts(self):
        rows = self._query("""
            SELECT version() AS full_version,
                   current_setting('server_version') AS version,
                   pg_postmaster_start_time() AS start_time,
                   current_setting('max_connections') AS max_conn
        """)
        r = rows[0]
        uptime_min = None
        if r["start_time"]:
            delta = datetime.now(timezone.utc) - r["start_time"].astimezone(timezone.utc)
            uptime_min = int(delta.total_seconds() / 60)
        return {
            "version": r["version"],
            "edition": "Community",
            "build": r["version"],
            "fullVersion": r["full_version"],
            "uptime": uptime_min,
            "maxConnections": int(r["max_conn"]),
        }

    def get_databases(self):
        return self._query("""
            SELECT datname AS name, 'ONLINE' AS state,
                   pg_database_size(datname)/1048576 AS size_mb
            FROM pg_database WHERE datistemplate = false
        """)

    def get_telemetry(self):
        activity = self._query("""
            SELECT count(*) FILTER (WHERE state='active') AS active,
                   count(*) AS total
            FROM pg_stat_activity
        """)
        return {
            "connectionsActive": activity[0]["active"],
            "connectionsTotal": activity[0]["total"],
            "collectedAt": utcnow(),
        }

    def get_wait_events(self):
        return self._query("""
            SELECT wait_event_type, wait_event,
                   count(*) AS sessions_waiting
            FROM pg_stat_activity
            WHERE wait_event IS NOT NULL AND state = 'active'
            GROUP BY wait_event_type, wait_event
            ORDER BY count(*) DESC
            LIMIT 50
        """)

    def get_top_queries(self):
        try:
            return self._query("""
                SELECT queryid::text AS query_hash,
                       LEFT(query, 1000) AS query_text,
                       calls AS executions,
                       total_exec_time::bigint AS total_cpu_ms,
                       (mean_exec_time)::int AS avg_duration_ms,
                       shared_blks_hit + shared_blks_read AS total_reads
                FROM pg_stat_statements
                ORDER BY total_exec_time DESC
                LIMIT 50
            """)
        except Exception:
            return []

    def get_index_health(self):
        return self._query("""
            SELECT schemaname || '.' || relname AS table_name,
                   indexrelname AS index_name,
                   idx_scan AS scan_count,
                   pg_relation_size(indexrelid) AS index_bytes,
                   CASE WHEN idx_scan = 0 THEN true ELSE false END AS unused,
                   current_database() AS db_name
            FROM pg_stat_user_indexes
            WHERE pg_relation_size(indexrelid) > 1048576
            ORDER BY pg_relation_size(indexrelid) DESC
            LIMIT 100
        """)

    def get_replication_status(self):
        try:
            return self._query("""
                SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn,
                       replay_lsn, sync_state
                FROM pg_stat_replication
            """)
        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 PostgreSQL Collector")
    parser.add_argument("--host", required=True, help="PostgreSQL host")
    parser.add_argument("--port", type=int, default=5432)
    parser.add_argument("--database", default="postgres")
    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, help="Collection interval in seconds (0=once)")
    args = parser.parse_args()

    while True:
        print(f"\n[{utcnow()}] Collecting from {args.host}:{args.port} ...")
        collector = PostgreSQLCollector(
            args.host, args.port, args.database, 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": "PostgreSQL",
                "facts": facts,
            })

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

            waits = collector.get_wait_events()
            submit_to_api(args.api, args.api_key, "/api/ingest/wait-stats", {
                "serverName": server_name,
                "engine": "PostgreSQL",
                "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": "PostgreSQL",
                    "queries": [dict(r) for r in queries],
                })

            indexes = collector.get_index_health()
            submit_to_api(args.api, args.api_key, "/api/ingest/database-health", {
                "serverName": server_name,
                "engine": "PostgreSQL",
                "indexes": [dict(r) for r in indexes],
                "missingIndexes": [],
            })

            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()
