database_functions.sql

-- Funções específicas para a funcionalidade de Alarme Kwh

-- Função para atualizar as configurações de alarme
CREATE OR REPLACE FUNCTION atualizar_alarme_kwh(
    p_usuario_id UUID,
    p_ativado BOOLEAN,
    p_limite_kwh DECIMAL(10, 2) DEFAULT NULL,
    p_notificacao_app BOOLEAN DEFAULT TRUE,
    p_notificacao_email BOOLEAN DEFAULT TRUE
) RETURNS BOOLEAN AS $$
DECLARE
    v_alarme_id UUID;
BEGIN
    -- Verificar se existe alarme para o usuário
    SELECT id INTO v_alarme_id FROM alarmes_kwh WHERE usuario_id = p_usuario_id;
    
    IF v_alarme_id IS NULL THEN
        -- Criar novo alarme se não existir
        INSERT INTO alarmes_kwh (
            usuario_id, 
            ativado, 
            limite_kwh, 
            notificacao_app, 
            notificacao_email
        )
        VALUES (
            p_usuario_id, 
            p_ativado, 
            p_limite_kwh, 
            p_notificacao_app, 
            p_notificacao_email
        );
    ELSE
        -- Atualizar alarme existente
        UPDATE alarmes_kwh
        SET 
            ativado = p_ativado,
            limite_kwh = CASE WHEN p_ativado THEN p_limite_kwh ELSE limite_kwh END,
            notificacao_app = p_notificacao_app,
            notificacao_email = p_notificacao_email
        WHERE id = v_alarme_id;
    END IF;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para verificar o consumo e gerar notificações de alarme
CREATE OR REPLACE FUNCTION verificar_alarme_kwh() RETURNS VOID AS $$
DECLARE
    r_alarme RECORD;
    v_consumo_atual DECIMAL;
    v_percentual INTEGER;
BEGIN
    -- Selecionar todos os alarmes ativos
    FOR r_alarme IN 
        SELECT a.id, a.usuario_id, a.limite_kwh
        FROM alarmes_kwh a
        WHERE a.ativado = TRUE AND a.limite_kwh > 0
    LOOP
        -- Obter o consumo atual do mês para o usuário
        SELECT COALESCE(SUM(valor_kwh), 0) INTO v_consumo_atual
        FROM consumo_energia
        WHERE 
            usuario_id = r_alarme.usuario_id AND
            data_leitura >= DATE_TRUNC('month', CURRENT_DATE) AND
            data_leitura <= CURRENT_DATE;
            
        -- Calcular o percentual do limite alcançado
        v_percentual := (v_consumo_atual / r_alarme.limite_kwh * 100)::INTEGER;
        
        -- Para cada percentual relevante (80%, 90%, 100%)
        IF v_percentual >= 80 THEN
            -- Verificar se já existe notificação para este percentual/alarme no mês atual
            IF NOT EXISTS (
                SELECT 1 FROM notificacoes_alarme
                WHERE 
                    alarme_id = r_alarme.id AND
                    percentual = CASE
                                    WHEN v_percentual >= 100 THEN 100
                                    WHEN v_percentual >= 90 THEN 90
                                    ELSE 80
                                 END AND
                    created_at >= DATE_TRUNC('month', CURRENT_DATE)
            ) THEN
                -- Inserir nova notificação
                INSERT INTO notificacoes_alarme (
                    usuario_id,
                    alarme_id,
                    percentual
                )
                VALUES (
                    r_alarme.usuario_id,
                    r_alarme.id,
                    CASE
                        WHEN v_percentual >= 100 THEN 100
                        WHEN v_percentual >= 90 THEN 90
                        ELSE 80
                    END
                );
                
                -- Aqui poderia disparar email se notificacao_email = true
                -- Este código dependeria de integração com serviço de email
            END IF;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger para verificar alarmes após inserção de novo consumo
CREATE OR REPLACE FUNCTION verificar_alarme_apos_consumo() RETURNS TRIGGER AS $$
BEGIN
    PERFORM verificar_alarme_kwh();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Criar o trigger
DROP TRIGGER IF EXISTS trigger_verificar_alarme_apos_consumo ON consumo_energia;
CREATE TRIGGER trigger_verificar_alarme_apos_consumo
AFTER INSERT OR UPDATE ON consumo_energia
FOR EACH STATEMENT EXECUTE FUNCTION verificar_alarme_apos_consumo();

-- Função para marcar notificação como lida
CREATE OR REPLACE FUNCTION marcar_notificacao_como_lida(
    p_usuario_id UUID,
    p_notificacao_id UUID
) RETURNS BOOLEAN AS $$
BEGIN
    UPDATE notificacoes_alarme
    SET lida = TRUE
    WHERE id = p_notificacao_id
    AND usuario_id = p_usuario_id;
    
    RETURN FOUND;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para obter estatísticas de consumo do usuário
CREATE OR REPLACE FUNCTION obter_estatisticas_consumo(
    p_usuario_id UUID
) RETURNS TABLE (
    consumo_atual DECIMAL,
    limite_kwh DECIMAL,
    percentual INTEGER,
    economia_mes_anterior DECIMAL,
    previsao_fim_mes DECIMAL
) AS $$
DECLARE
    v_consumo_atual DECIMAL;
    v_consumo_mes_anterior DECIMAL;
    v_limite_kwh DECIMAL;
    v_percentual INTEGER;
    v_dia_atual INTEGER;
    v_dias_mes INTEGER;
    v_previsao DECIMAL;
BEGIN
    -- Obter consumo atual do mês
    SELECT COALESCE(SUM(valor_kwh), 0) INTO v_consumo_atual
    FROM consumo_energia
    WHERE 
        usuario_id = p_usuario_id AND
        data_leitura >= DATE_TRUNC('month', CURRENT_DATE) AND
        data_leitura <= CURRENT_DATE;
        
    -- Obter consumo do mês anterior
    SELECT COALESCE(SUM(valor_kwh), 0) INTO v_consumo_mes_anterior
    FROM consumo_energia
    WHERE 
        usuario_id = p_usuario_id AND
        data_leitura >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AND
        data_leitura < DATE_TRUNC('month', CURRENT_DATE);
    
    -- Obter limite do alarme
    SELECT limite_kwh INTO v_limite_kwh
    FROM alarmes_kwh
    WHERE usuario_id = p_usuario_id;
    
    -- Calcular percentual
    IF v_limite_kwh IS NOT NULL AND v_limite_kwh > 0 THEN
        v_percentual := (v_consumo_atual / v_limite_kwh * 100)::INTEGER;
    ELSE
        v_percentual := 0;
    END IF;
    
    -- Calcular previsão de consumo para o fim do mês
    v_dia_atual := EXTRACT(DAY FROM CURRENT_DATE)::INTEGER;
    v_dias_mes := EXTRACT(DAY FROM (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day'))::INTEGER;
    
    IF v_dia_atual > 0 THEN
        v_previsao := (v_consumo_atual / v_dia_atual) * v_dias_mes;
    ELSE
        v_previsao := v_consumo_atual;
    END IF;
    
    -- Retornar os dados calculados
    RETURN QUERY
    SELECT 
        v_consumo_atual AS consumo_atual,
        v_limite_kwh AS limite_kwh,
        v_percentual AS percentual,
        v_consumo_mes_anterior - v_consumo_atual AS economia_mes_anterior,
        v_previsao AS previsao_fim_mes;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para obter as configurações do alarme de um usuário
CREATE OR REPLACE FUNCTION obter_alarme_kwh(
    p_usuario_id UUID
) RETURNS TABLE (
    ativado BOOLEAN,
    limite_kwh DECIMAL,
    notificacao_app BOOLEAN,
    notificacao_email BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.ativado,
        a.limite_kwh,
        a.notificacao_app,
        a.notificacao_email
    FROM alarmes_kwh a
    WHERE a.usuario_id = p_usuario_id;
    
    -- Se não existir configuração, retornar valores padrão
    IF NOT FOUND THEN
        RETURN QUERY
        SELECT 
            FALSE AS ativado,
            300::DECIMAL AS limite_kwh,
            TRUE AS notificacao_app,
            TRUE AS notificacao_email;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Função para obter notificações de alarme de um usuário
CREATE OR REPLACE FUNCTION obter_notificacoes_alarme(
    p_usuario_id UUID,
    p_apenas_nao_lidas BOOLEAN DEFAULT FALSE
) RETURNS TABLE (
    id UUID,
    percentual INTEGER,
    lida BOOLEAN,
    data_criacao TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        n.id,
        n.percentual,
        n.lida,
        n.created_at
    FROM notificacoes_alarme n
    WHERE n.usuario_id = p_usuario_id
    AND (NOT p_apenas_nao_lidas OR NOT n.lida)
    ORDER BY n.created_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;