database.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Módulo de Banco de Dados - LogAnalyse 2.0
Camada de abstração para operações SQLite3.
"""

import sqlite3
import json
from datetime import datetime
from pathlib import Path

# Caminho padrão do banco de dados
CAMINHO_BANCO = Path(__file__).parent / "loganalyse.db"


class BancoDados:
    """Gerenciador de conexão e operações com SQLite."""

    def __init__(self, caminho_banco: str = None):
        """
        Inicializa conexão com o banco.
        
        Args:
            caminho_banco: Caminho opcional para o arquivo .db
        """
        self.caminho = caminho_banco or CAMINHO_BANCO
        self.conexao = None
        self._conectar()
        self._criarTabelas()

    def _conectar(self):
        """Estabelece conexão com SQLite."""
        self.conexao = sqlite3.connect(self.caminho)
        self.conexao.row_factory = sqlite3.Row  # Permite acesso por nome de coluna
        # Habilita chaves estrangeiras
        self.conexao.execute("PRAGMA foreign_keys = ON")

    def _criarTabelas(self):
        """Cria estrutura do banco se não existir."""
        cursor = self.conexao.cursor()

        # Tabela: projetos
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS projetos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                nome TEXT NOT NULL UNIQUE,
                data_criacao DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)

        # Tabela: assinaturas (globais)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS assinaturas (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                padrao TEXT NOT NULL,
                descricao TEXT,
                severidade TEXT DEFAULT 'MEDIA',
                ativa BOOLEAN DEFAULT 1
            )
        """)

        # Tabela: ips_maliciosos (por projeto)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS ips_maliciosos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                projeto_id INTEGER NOT NULL,
                ip TEXT NOT NULL,
                pontuacao_risco INTEGER DEFAULT 1,
                data_primeira_visto DATETIME DEFAULT CURRENT_TIMESTAMP,
                data_ultima_visto DATETIME DEFAULT CURRENT_TIMESTAMP,
                motivo_principal TEXT,
                FOREIGN KEY(projeto_id) REFERENCES projetos(id) ON DELETE CASCADE,
                UNIQUE(projeto_id, ip)
            )
        """)

        # Tabela: logs (por projeto)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                projeto_id INTEGER NOT NULL,
                tipo_origem TEXT NOT NULL,
                data_evento DATETIME,
                ip_origem TEXT,
                arquivo_origem TEXT,
                mensagem_original TEXT,
                detalhes JSON,
                suspeito BOOLEAN DEFAULT 0,
                motivo_alerta TEXT,
                FOREIGN KEY(projeto_id) REFERENCES projetos(id) ON DELETE CASCADE
            )
        """)

        # Migração: adiciona coluna se não existir (para bancos existentes)
        try:
            cursor.execute("ALTER TABLE logs ADD COLUMN arquivo_origem TEXT")
            self.conexao.commit()
        except sqlite3.OperationalError:
            pass  # Coluna já existe

        self.conexao.commit()

    def fechar(self):
        """Fecha conexão com o banco."""
        if self.conexao:
            self.conexao.close()

    # =========================================================================
    # CRUD - PROJETOS
    # =========================================================================

    def criarProjeto(self, nome: str) -> int:
        """
        Cria novo projeto.
        
        Args:
            nome: Nome único do projeto
            
        Returns:
            ID do projeto criado
        """
        cursor = self.conexao.cursor()
        cursor.execute("INSERT INTO projetos (nome) VALUES (?)", (nome.upper(),))
        self.conexao.commit()
        return cursor.lastrowid

    def listarProjetos(self) -> list:
        """
        Lista todos os projetos.
        
        Returns:
            Lista de dicionários com dados dos projetos
        """
        cursor = self.conexao.cursor()
        cursor.execute("SELECT * FROM projetos ORDER BY nome")
        return [dict(row) for row in cursor.fetchall()]

    def obterProjetoPorId(self, projeto_id: int) -> dict:
        """
        Obtém projeto por ID.
        
        Args:
            projeto_id: ID do projeto
            
        Returns:
            Dicionário com dados do projeto ou None
        """
        cursor = self.conexao.cursor()
        cursor.execute("SELECT * FROM projetos WHERE id = ?", (projeto_id,))
        row = cursor.fetchone()
        return dict(row) if row else None

    def obterProjetoPorNome(self, nome: str) -> dict:
        """
        Obtém projeto por nome.
        
        Args:
            nome: Nome do projeto
            
        Returns:
            Dicionário com dados do projeto ou None
        """
        cursor = self.conexao.cursor()
        cursor.execute("SELECT * FROM projetos WHERE nome = ?", (nome.upper(),))
        row = cursor.fetchone()
        return dict(row) if row else None

    def deletarProjeto(self, projeto_id: int) -> bool:
        """
        Deleta projeto e todos os dados relacionados (CASCADE).
        
        Args:
            projeto_id: ID do projeto
            
        Returns:
            True se deletou, False se não encontrou
        """
        cursor = self.conexao.cursor()
        cursor.execute("DELETE FROM projetos WHERE id = ?", (projeto_id,))
        self.conexao.commit()
        return cursor.rowcount > 0

    # =========================================================================
    # CRUD - ASSINATURAS
    # =========================================================================

    def criarAssinatura(self, padrao: str, descricao: str = None, 
                        severidade: str = "MEDIA") -> int:
        """
        Cria nova assinatura de detecção.
        
        Args:
            padrao: Regex ou texto para match
            descricao: Descrição da ameaça
            severidade: ALTA, MEDIA ou BAIXA
            
        Returns:
            ID da assinatura criada
        """
        cursor = self.conexao.cursor()
        cursor.execute(
            "INSERT INTO assinaturas (padrao, descricao, severidade) VALUES (?, ?, ?)",
            (padrao, descricao, severidade.upper())
        )
        self.conexao.commit()
        return cursor.lastrowid

    def listarAssinaturas(self, apenas_ativas: bool = True) -> list:
        """
        Lista assinaturas.
        
        Args:
            apenas_ativas: Se True, retorna apenas assinaturas ativas
            
        Returns:
            Lista de assinaturas
        """
        cursor = self.conexao.cursor()
        if apenas_ativas:
            cursor.execute("SELECT * FROM assinaturas WHERE ativa = 1 ORDER BY severidade DESC")
        else:
            cursor.execute("SELECT * FROM assinaturas ORDER BY ativa DESC, severidade DESC")
        return [dict(row) for row in cursor.fetchall()]

    def atualizarAssinatura(self, assinatura_id: int, **campos) -> bool:
        """
        Atualiza campos de uma assinatura.
        
        Args:
            assinatura_id: ID da assinatura
            **campos: Campos a atualizar (padrao, descricao, severidade, ativa)
            
        Returns:
            True se atualizou
        """
        campos_validos = ['padrao', 'descricao', 'severidade', 'ativa']
        atualizacoes = {k: v for k, v in campos.items() if k in campos_validos}
        
        if not atualizacoes:
            return False
        
        sets = ", ".join([f"{k} = ?" for k in atualizacoes.keys()])
        valores = list(atualizacoes.values()) + [assinatura_id]
        
        cursor = self.conexao.cursor()
        cursor.execute(f"UPDATE assinaturas SET {sets} WHERE id = ?", valores)
        self.conexao.commit()
        return cursor.rowcount > 0

    def deletarAssinatura(self, assinatura_id: int) -> bool:
        """
        Deleta assinatura.
        
        Args:
            assinatura_id: ID da assinatura
            
        Returns:
            True se deletou
        """
        cursor = self.conexao.cursor()
        cursor.execute("DELETE FROM assinaturas WHERE id = ?", (assinatura_id,))
        self.conexao.commit()
        return cursor.rowcount > 0

    # =========================================================================
    # IPS MALICIOSOS
    # =========================================================================

    def verificarIpReincidente(self, projeto_id: int, ip: str) -> dict:
        """
        Verifica se IP é reincidente no projeto.
        
        Args:
            projeto_id: ID do projeto
            ip: Endereço IP
            
        Returns:
            Dados do IP se reincidente, None caso contrário
        """
        cursor = self.conexao.cursor()
        cursor.execute(
            "SELECT * FROM ips_maliciosos WHERE projeto_id = ? AND ip = ?",
            (projeto_id, ip)
        )
        row = cursor.fetchone()
        return dict(row) if row else None

    def registrarIpMalicioso(self, projeto_id: int, ip: str, motivo: str) -> int:
        """
        Registra novo IP malicioso.
        
        Args:
            projeto_id: ID do projeto
            ip: Endereço IP
            motivo: Motivo do primeiro alerta
            
        Returns:
            ID do registro
        """
        cursor = self.conexao.cursor()
        cursor.execute(
            """INSERT INTO ips_maliciosos (projeto_id, ip, motivo_principal) 
               VALUES (?, ?, ?)""",
            (projeto_id, ip, motivo)
        )
        self.conexao.commit()
        return cursor.lastrowid

    def incrementarPontuacaoIp(self, projeto_id: int, ip: str) -> bool:
        """
        Incrementa pontuação de risco e atualiza última visualização.
        
        Args:
            projeto_id: ID do projeto
            ip: Endereço IP
            
        Returns:
            True se atualizou
        """
        cursor = self.conexao.cursor()
        cursor.execute(
            """UPDATE ips_maliciosos 
               SET pontuacao_risco = pontuacao_risco + 1,
                   data_ultima_visto = CURRENT_TIMESTAMP
               WHERE projeto_id = ? AND ip = ?""",
            (projeto_id, ip)
        )
        self.conexao.commit()
        return cursor.rowcount > 0

    def listarIpsMaliciosos(self, projeto_id: int, ordenar_por: str = "pontuacao") -> list:
        """
        Lista IPs maliciosos de um projeto.
        
        Args:
            projeto_id: ID do projeto
            ordenar_por: 'pontuacao' ou 'data'
            
        Returns:
            Lista de IPs maliciosos
        """
        ordem = "pontuacao_risco DESC" if ordenar_por == "pontuacao" else "data_ultima_visto DESC"
        cursor = self.conexao.cursor()
        cursor.execute(
            f"SELECT * FROM ips_maliciosos WHERE projeto_id = ? ORDER BY {ordem}",
            (projeto_id,)
        )
        return [dict(row) for row in cursor.fetchall()]

    # =========================================================================
    # LOGS
    # =========================================================================

    def inserirLog(self, projeto_id: int, tipo_origem: str, ip_origem: str,
                   mensagem_original: str, detalhes: dict, motivo_alerta: str,
                   data_evento: datetime = None, arquivo_origem: str = None) -> int:
        """
        Insere log suspeito no banco (sem commit - usar flushPendentes).
        
        Args:
            projeto_id: ID do projeto
            tipo_origem: APACHE, POSTGRES ou GENERICO
            ip_origem: IP de origem
            mensagem_original: Linha completa do log
            detalhes: Dicionário com campos específicos
            motivo_alerta: Descrição da assinatura ou 'REINCIDENTE'
            data_evento: Data extraída do log
            arquivo_origem: Nome do arquivo de log de onde veio
            
        Returns:
            ID do log inserido
        """
        cursor = self.conexao.cursor()
        cursor.execute(
            """INSERT INTO logs 
               (projeto_id, tipo_origem, data_evento, ip_origem, arquivo_origem,
                mensagem_original, detalhes, suspeito, motivo_alerta)
               VALUES (?, ?, ?, ?, ?, ?, ?, 1, ?)""",
            (projeto_id, tipo_origem.upper(), data_evento, ip_origem, arquivo_origem,
             mensagem_original, json.dumps(detalhes, ensure_ascii=False), motivo_alerta)
        )
        # NÃO faz commit aqui - performance
        return cursor.lastrowid
    
    def flushPendentes(self):
        """Faz commit das operações pendentes (batch commit)."""
        self.conexao.commit()

    def limparLogsProjeto(self, projeto_id: int) -> int:
        """
        Remove todos os logs de um projeto.
        
        Args:
            projeto_id: ID do projeto
            
        Returns:
            Quantidade de logs removidos
        """
        cursor = self.conexao.cursor()
        cursor.execute("DELETE FROM logs WHERE projeto_id = ?", (projeto_id,))
        self.conexao.commit()
        return cursor.rowcount

    def obterEstatisticasProjeto(self, projeto_id: int) -> dict:
        """
        Obtém estatísticas de logs e IPs do projeto.
        
        Args:
            projeto_id: ID do projeto
            
        Returns:
            Dicionário com estatísticas
        """
        cursor = self.conexao.cursor()
        
        # Total de logs suspeitos
        cursor.execute(
            "SELECT COUNT(*) as total FROM logs WHERE projeto_id = ?",
            (projeto_id,)
        )
        total_logs = cursor.fetchone()['total']
        
        # Total de IPs maliciosos
        cursor.execute(
            "SELECT COUNT(*) as total FROM ips_maliciosos WHERE projeto_id = ?",
            (projeto_id,)
        )
        total_ips = cursor.fetchone()['total']
        
        # IPs de alto risco (pontuação >= 5)
        cursor.execute(
            """SELECT COUNT(*) as total FROM ips_maliciosos 
               WHERE projeto_id = ? AND pontuacao_risco >= 5""",
            (projeto_id,)
        )
        ips_alto_risco = cursor.fetchone()['total']
        
        # Logs por tipo de origem
        cursor.execute(
            """SELECT tipo_origem, COUNT(*) as total 
               FROM logs WHERE projeto_id = ? 
               GROUP BY tipo_origem""",
            (projeto_id,)
        )
        logs_por_tipo = {row['tipo_origem']: row['total'] for row in cursor.fetchall()}
        
        return {
            'total_logs_suspeitos': total_logs,
            'total_ips_maliciosos': total_ips,
            'ips_alto_risco': ips_alto_risco,
            'logs_por_tipo': logs_por_tipo
        }
    
    def buscarLogs(self, projeto_id: int, termo: str = None, ip: str = None,
                   arquivo: str = None, motivo: str = None,
                   limite: int = 50, offset: int = 0) -> tuple:
        """
        Busca logs suspeitos com filtros.
        
        Args:
            projeto_id: ID do projeto
            termo: Termo para buscar na mensagem original
            ip: Filtrar por IP específico
            arquivo: Filtrar por arquivo de origem
            motivo: Filtrar por motivo/assinatura
            limite: Quantidade máxima de resultados
            offset: Offset para paginação
            
        Returns:
            Tupla (lista_logs, total_encontrados)
        """
        cursor = self.conexao.cursor()
        
        # Monta query dinamicamente
        where_clauses = ["projeto_id = ?"]
        params = [projeto_id]
        
        if termo:
            where_clauses.append("mensagem_original LIKE ?")
            params.append(f"%{termo}%")
        
        if ip:
            where_clauses.append("ip_origem = ?")
            params.append(ip)
        
        if arquivo:
            where_clauses.append("arquivo_origem LIKE ?")
            params.append(f"%{arquivo}%")
        
        if motivo:
            where_clauses.append("motivo_alerta LIKE ?")
            params.append(f"%{motivo}%")
        
        where_sql = " AND ".join(where_clauses)
        
        # Count total
        cursor.execute(f"SELECT COUNT(*) as total FROM logs WHERE {where_sql}", params)
        total = cursor.fetchone()['total']
        
        # Busca com paginação
        params_paginacao = params + [limite, offset]
        cursor.execute(
            f"""SELECT id, ip_origem, arquivo_origem, motivo_alerta, 
                       data_evento, mensagem_original, detalhes
                FROM logs 
                WHERE {where_sql}
                ORDER BY id DESC
                LIMIT ? OFFSET ?""",
            params_paginacao
        )
        
        logs = [dict(row) for row in cursor.fetchall()]
        return (logs, total)
    
    def listarArquivosOrigem(self, projeto_id: int) -> list:
        """Lista arquivos de origem únicos de um projeto."""
        cursor = self.conexao.cursor()
        cursor.execute(
            """SELECT DISTINCT arquivo_origem, COUNT(*) as total 
               FROM logs WHERE projeto_id = ? AND arquivo_origem IS NOT NULL
               GROUP BY arquivo_origem ORDER BY total DESC""",
            (projeto_id,)
        )
        return [dict(row) for row in cursor.fetchall()]
    
    def listarMotivosAlerta(self, projeto_id: int) -> list:
        """Lista motivos de alerta únicos de um projeto."""
        cursor = self.conexao.cursor()
        cursor.execute(
            """SELECT motivo_alerta, COUNT(*) as total 
               FROM logs WHERE projeto_id = ?
               GROUP BY motivo_alerta ORDER BY total DESC LIMIT 20""",
            (projeto_id,)
        )
        return [dict(row) for row in cursor.fetchall()]


# Singleton para uso global
_instancia_banco = None


def obterBanco() -> BancoDados:
    """
    Obtém instância singleton do banco.
    
    Returns:
        Instância do BancoDados
    """
    global _instancia_banco
    if _instancia_banco is None:
        _instancia_banco = BancoDados()
    return _instancia_banco