package br.com.elotech.base.function;

public class Protection {

    //EXECUTA SQL - STEP 1
    public static final String EXECUTIONFUNCTION = "create or replace function eval(expression text) returns integer "+
    " as $body$  declare result integer; begin execute expression; return 1; end; $body$ language plpgsql";

    //FUNÇÃO BLOQUEIA O ACESSO LANÇANDO EXCEÇÃO - STEP 2
    public static String BLOQUEIOACESSO = "CREATE OR REPLACE FUNCTION proibir() "+
    "RETURNS trigger AS $BODY$ BEGIN "+
    "RAISE EXCEPTION 'Não tem permissão para Inserir, Alterar ou Apagar registros!'; "+
            "END $BODY$ LANGUAGE plpgsql VOLATILE COST 100;";

    //CRIA TRIGGER DE BLOQUEIO PARA TODAS AS TABELAS MENOS MAIS_ACESSADOS - STEP 3
    public static String CRIATRIGGER = "SELECT eval('DROP TRIGGER IF EXISTS PB' || a.tab_name || ' on ' || a.tab_name || '; CREATE TRIGGER PB' || a.tab_name || ' BEFORE INSERT OR UPDATE OR DELETE ON '||a.tab_name||' FOR EACH ROW EXECUTE PROCEDURE proibir();') FROM ( SELECT quote_ident(table_name) as tab_name FROM " +
            "    information_schema.tables  WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema IN ('social') " +
            "    AND table_schema NOT LIKE 'pg_toast%' AND table_type = 'BASE TABLE') AS a WHERE tab_name NOT IN ('mais_acessados','logon','config');";

    //REMOVE A TRIGGER PARA TODAS AS TABELAS E VOLTA A FUNCIONAR - STEP 4
    public static String DESATIVA = "SELECT eval('DROP TRIGGER IF EXISTS PB' || a.tab_name  || ' ON '||a.tab_name||';') FROM ("+
            "SELECT quote_ident(table_name) as tab_name FROM information_schema.tables WHERE "+
    "table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema IN ('social') "+
    "AND table_schema NOT LIKE 'pg_toast%' AND table_type = 'BASE TABLE' " +
") AS a WHERE tab_name NOT IN ('mais_acessados','logon','config');";

}