from __future__ import annotations

import json
import sqlite3
from pathlib import Path
from typing import Any, Dict, Iterable, List, Optional

from core.paths import database_path


class EventDatabase:
    """Minimal SQLite-backed event store for the ProcessMonitor project."""

    def __init__(self, db_path: Optional[str | Path] = None):
        base_path = Path(db_path) if db_path is not None else database_path()
        self.db_path = Path(base_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self.initialize_schema()

    def _connect(self) -> sqlite3.Connection:
        conn = sqlite3.connect(str(self.db_path))
        conn.row_factory = sqlite3.Row
        return conn

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def __del__(self):
        try:
            self.close()
        except Exception:
            pass

    def initialize_schema(self) -> None:
        schema_sql = """
        CREATE TABLE IF NOT EXISTS events (
            event_id TEXT PRIMARY KEY,
            timestamp TEXT NOT NULL,
            event_type TEXT NOT NULL,
            source TEXT NOT NULL,
            severity TEXT NOT NULL,
            confidence INTEGER DEFAULT 0,
            hostname TEXT,
            user_name TEXT,
            pid INTEGER,
            parent_pid INTEGER,
            process_name TEXT,
            path TEXT,
            command_line TEXT,
            hash_value TEXT,
            signer TEXT,
            signed INTEGER,
            local_ip TEXT,
            local_port INTEGER,
            remote_ip TEXT,
            remote_port INTEGER,
            domain TEXT,
            rule_id TEXT,
            risk_score INTEGER DEFAULT 0,
            tags TEXT,
            metadata TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS alerts (
            alert_id TEXT PRIMARY KEY,
            timestamp TEXT NOT NULL,
            severity TEXT NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            rule_id TEXT,
            status TEXT DEFAULT 'New',
            process_id INTEGER,
            event_id TEXT,
            analyst_notes TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS processes (
            pid INTEGER PRIMARY KEY,
            first_seen TEXT NOT NULL,
            last_seen TEXT NOT NULL,
            path TEXT,
            hash_value TEXT,
            signer TEXT,
            parent_pid INTEGER
        );

        CREATE TABLE IF NOT EXISTS rules (
            rule_id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            enabled INTEGER NOT NULL DEFAULT 1,
            severity TEXT NOT NULL,
            conditions TEXT NOT NULL,
            description TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS exclusions (
            exclusion_id TEXT PRIMARY KEY,
            type TEXT NOT NULL,
            value TEXT NOT NULL,
            reason TEXT,
            expiration TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS audit_log (
            audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT NOT NULL,
            action TEXT NOT NULL,
            actor TEXT,
            object TEXT,
            details TEXT
        );

        CREATE TABLE IF NOT EXISTS system_health (
            health_id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT NOT NULL,
            component TEXT NOT NULL,
            status TEXT NOT NULL,
            details TEXT
        );

        CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
        CREATE INDEX IF NOT EXISTS idx_events_event_type ON events(event_type);
        CREATE INDEX IF NOT EXISTS idx_events_pid ON events(pid);
        CREATE INDEX IF NOT EXISTS idx_events_process_name ON events(process_name);
        CREATE INDEX IF NOT EXISTS idx_events_severity ON events(severity);
        CREATE INDEX IF NOT EXISTS idx_events_hash ON events(hash_value);
        CREATE INDEX IF NOT EXISTS idx_events_ip ON events(remote_ip);
        CREATE INDEX IF NOT EXISTS idx_events_domain ON events(domain);
        """

        conn = self._connect()
        try:
            conn.executescript(schema_sql)
            conn.commit()
        finally:
            conn.close()

    def insert_event(self, event: Dict[str, Any]) -> str:
        event_id = event.get("event_id")
        if not event_id:
            raise ValueError("Events must contain an event_id")

        tags = json.dumps(event.get("tags") or [], ensure_ascii=False)
        metadata = json.dumps(event.get("metadata") or {}, ensure_ascii=False)

        conn = self._connect()
        try:
            conn.execute(
                """
                INSERT OR REPLACE INTO events (
                    event_id, timestamp, event_type, source, severity, confidence,
                    hostname, user_name, pid, parent_pid, process_name, path,
                    command_line, hash_value, signer, signed, local_ip, local_port,
                    remote_ip, remote_port, domain, rule_id, risk_score, tags, metadata
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    event.get("event_id"),
                    event.get("timestamp"),
                    event.get("event_type"),
                    event.get("source"),
                    event.get("severity"),
                    int(event.get("confidence") or 0),
                    event.get("hostname"),
                    event.get("user"),
                    event.get("pid"),
                    event.get("parent_pid"),
                    event.get("process_name"),
                    event.get("path"),
                    event.get("command_line"),
                    event.get("sha256"),
                    event.get("signer"),
                    1 if event.get("signed") is True else 0 if event.get("signed") is False else None,
                    event.get("local_ip"),
                    event.get("local_port"),
                    event.get("remote_ip"),
                    event.get("remote_port"),
                    event.get("domain"),
                    event.get("rule_id"),
                    int(event.get("risk_score") or 0),
                    tags,
                    metadata,
                ),
            )
            conn.commit()
        finally:
            conn.close()
        return event_id

    def insert_alert(self, alert: Dict[str, Any]) -> str:
        alert_id = alert.get("alert_id")
        if not alert_id:
            raise ValueError("Alerts must contain an alert_id")

        conn = self._connect()
        try:
            conn.execute(
                """
                INSERT OR REPLACE INTO alerts (
                    alert_id, timestamp, severity, title, description, rule_id,
                    status, process_id, event_id, analyst_notes
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    alert.get("alert_id"),
                    alert.get("timestamp"),
                    alert.get("severity"),
                    alert.get("title"),
                    alert.get("description"),
                    alert.get("rule_id"),
                    alert.get("status", "New"),
                    alert.get("process_id"),
                    alert.get("event_id"),
                    alert.get("analyst_notes"),
                ),
            )
            conn.commit()
        finally:
            conn.close()
        return alert_id

    def insert_process_snapshot(self, process: Dict[str, Any]) -> None:
        conn = self._connect()
        try:
            conn.execute(
                """
                INSERT INTO processes (pid, first_seen, last_seen, path, hash_value, signer, parent_pid)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(pid) DO UPDATE SET
                    last_seen = excluded.last_seen,
                    path = COALESCE(excluded.path, processes.path),
                    hash_value = COALESCE(excluded.hash_value, processes.hash_value),
                    signer = COALESCE(excluded.signer, processes.signer),
                    parent_pid = COALESCE(excluded.parent_pid, processes.parent_pid)
                """,
                (
                    process.get("pid"),
                    process.get("first_seen"),
                    process.get("last_seen"),
                    process.get("path"),
                    process.get("hash_value"),
                    process.get("signer"),
                    process.get("parent_pid"),
                ),
            )
            conn.commit()
        finally:
            conn.close()

    def insert_audit_event(self, action: str, actor: str, obj: str, details: str) -> None:
        conn = self._connect()
        try:
            conn.execute(
                "INSERT INTO audit_log (timestamp, action, actor, object, details) VALUES (datetime('now'), ?, ?, ?, ?)",
                (action, actor, obj, details),
            )
            conn.commit()
        finally:
            conn.close()

    def insert_system_health(self, component: str, status: str, details: str) -> None:
        conn = self._connect()
        try:
            conn.execute(
                "INSERT INTO system_health (timestamp, component, status, details) VALUES (datetime('now'), ?, ?, ?)",
                (component, status, details),
            )
            conn.commit()
        finally:
            conn.close()

    def list_system_health(self, limit: int = 100) -> List[Dict[str, Any]]:
        conn = self._connect()
        try:
            rows = conn.execute(
                "SELECT * FROM system_health ORDER BY timestamp DESC LIMIT ?",
                (limit,),
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            conn.close()

    def prune_events(self, retention_days: int = 30) -> int:
        if retention_days <= 0:
            return 0

        conn = self._connect()
        try:
            cursor = conn.execute(
                "DELETE FROM events WHERE datetime(timestamp) < datetime('now', '-' || ? || ' days')",
                (str(retention_days),),
            )
            conn.commit()
            return cursor.rowcount
        finally:
            conn.close()

    def list_events(self, limit: int = 100) -> List[Dict[str, Any]]:
        conn = self._connect()
        try:
            rows = conn.execute(
                "SELECT * FROM events ORDER BY timestamp DESC LIMIT ?",
                (limit,),
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            conn.close()

    def list_alerts(self, limit: int = 100) -> List[Dict[str, Any]]:
        conn = self._connect()
        try:
            rows = conn.execute(
                """
                SELECT a.*, e.process_name, e.pid, e.risk_score, e.source,
                       e.command_line, e.parent_pid, e.metadata
                FROM alerts AS a
                LEFT JOIN events AS e ON e.event_id = a.event_id
                ORDER BY a.timestamp DESC LIMIT ?
                """,
                (max(0, int(limit)),),
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            conn.close()

    def list_processes(self, limit: int = 500) -> List[Dict[str, Any]]:
        conn = self._connect()
        try:
            rows = conn.execute(
                "SELECT * FROM processes ORDER BY last_seen DESC LIMIT ?", (max(0, int(limit)),)
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            conn.close()

    def list_audit_entries(self, limit: int = 100) -> List[Dict[str, Any]]:
        conn = self._connect()
        try:
            rows = conn.execute(
                "SELECT * FROM audit_log ORDER BY audit_id DESC LIMIT ?", (max(0, int(limit)),)
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            conn.close()

    def get_dashboard_metrics(self) -> Dict[str, Any]:
        """Return the small read-only aggregate used by local dashboards."""
        conn = self._connect()
        try:
            event_count = conn.execute("SELECT COUNT(*) FROM events").fetchone()[0]
            alert_count = conn.execute("SELECT COUNT(*) FROM alerts").fetchone()[0]
            high_risk_count = conn.execute(
                "SELECT COUNT(*) FROM alerts WHERE lower(severity) IN ('high', 'critical')"
            ).fetchone()[0]
            process_count = conn.execute("SELECT COUNT(*) FROM processes").fetchone()[0]
            latest = conn.execute("SELECT MAX(timestamp) FROM events").fetchone()[0]
            return {
                "event_count": event_count,
                "alert_count": alert_count,
                "high_risk_count": high_risk_count,
                "process_count": process_count,
                "latest_event_timestamp": latest,
            }
        finally:
            conn.close()

    def table_exists(self, table_name: str) -> bool:
        conn = self._connect()
        try:
            row = conn.execute(
                "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?",
                (table_name,),
            ).fetchone()
            return row is not None
        finally:
            conn.close()

    def close(self) -> None:
        return None


if __name__ == "__main__":
    db = EventDatabase()
    print(f"Database initialized at: {db.db_path}")
