package br.com.elotech.scripts;

public abstract class Script {

    private static StringBuilder builder;

    public static String scriptSelectRegistro(){
        builder = new StringBuilder();
        builder.append("SELECT")
               .append("    reg_nome")
               .append("   ,reg_sistema_codigo")
               .append("   ,reg_modulo")
               .append("   ,reg_validade")
               .append("   ,reg_codigo")
               .append("   ,reg_senha")
               .append("   ,trim(reg_hash) as reg_hash")
               .append("  FROM registro")
               .append(" WHERE reg_sistema_codigo = :codigo")
               .append(" ORDER BY reg_validade DESC");
        return builder.toString();
    }

    public static String scriptInsertRegistro(){
        builder = new StringBuilder();
        builder.append("INSERT INTO registro (reg_nome, reg_sistema_codigo, reg_modulo, reg_validade, reg_codigo, reg_senha, reg_hash) ")
               .append("     VALUES (:reg_nome, :reg_sistema_codigo, :reg_modulo, :reg_validade, :reg_codigo, :reg_senha, :reg_hash)");
        return builder.toString();
    }

    public static String scriptUpdateRegistro() {
        builder = new StringBuilder();
        builder.append("UPDATE registro ")
               .append("   SET reg_nome     = :reg_nome ")
               .append("      ,reg_modulo   = :reg_modulo")
               .append("      ,reg_validade = :reg_validade")
               .append("      ,reg_codigo   = :reg_codigo")
               .append("      ,reg_senha    = :reg_senha")
               .append("      ,reg_hash     = :reg_hash")
               .append(" WHERE reg_sistema_codigo=:reg_sistema_codigo");
        return builder.toString();
    }

    public static String bloqueioStep1(){
        builder = new StringBuilder();
        builder.append("CREATE OR REPLACE FUNCTION eval(expression text) returns integer as $body$  declare result integer;")
               .append(" BEGIN execute expression;")
               .append("    return 1;")
               .append("   END; $body$")
               .append(" language plpgsql;");
        return builder.toString();
    }

    public static String bloqueioStep2(){
        builder = new StringBuilder();
        builder.append("CREATE OR REPLACE FUNCTION proibir() returns trigger AS $BODY$ ")
               .append(" BEGIN")
               .append("    RAISE EXCEPTION 'Não tem permissão para Inserir, Alterar ou Apagar registros!';")
               .append("   END $BODY$ LANGUAGE plpgsql;");
        return builder.toString();
    }

    public static String bloqueioStep3(){
        builder = new StringBuilder();
        builder.append("SELECT distinct count(eval('DROP TRIGGER IF EXISTS PB' || a.tab_name || ' on ' || a.tab_name || '; CREATE TRIGGER PB' || a.tab_name || ")
               .append("            ' BEFORE INSERT OR UPDATE OR DELETE ON ' || a.tab_name || ' FOR EACH ROW EXECUTE PROCEDURE proibir();')) as qtd_bloqueados")
               .append("  FROM (SELECT quote_ident(table_name) as tab_name")
               .append("          FROM information_schema.tables")
               .append("         WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema IN ('social')")
               .append("           AND table_schema NOT LIKE 'pg_toast%' AND table_type = 'BASE TABLE') ")
               .append("    AS a WHERE tab_name NOT IN ('mais_acessados', 'logon', 'config');");
        return builder.toString();
    }

    public static String desbloqueio(){
        builder = new StringBuilder();
        builder.append("SELECT distinct count(eval('DROP TRIGGER IF EXISTS PB' || a.tab_name || ' ON ' || a.tab_name || ';')) as qtd_desbloqueados")
               .append("  FROM (SELECT quote_ident(table_name) as tab_name")
               .append("          FROM information_schema.tables")
               .append("         WHERE table_schema")
               .append("        NOT IN ('pg_catalog', 'information_schema')")
               .append("           AND table_schema IN ('social')")
               .append("           AND table_schema NOT LIKE 'pg_toast%' AND table_type = 'BASE TABLE'")
               .append(") AS a WHERE tab_name NOT IN ('mais_acessados', 'logon', 'config');");
        return builder.toString();
    }
}