import psycopg2

try:
    connection = psycopg2.connect(user="postgres",
                                  password="eco123",
                                  host="localhost",
                                  port="5432",
                                  database="econexoes")
    cur = connection.cursor()

    usuarios = cur.execute('SELECT id,categoria FROM usuario')
    usuarios = cur.fetchall()
#################################Excluir Documentos e Documentos Condicionantes#####################################################################################################
    documentos_selected = []
    documentos_totais = cur.execute('SELECT id,usuario_id FROM documento')
    documentos_totais = cur.fetchall()

    for i in range(len(documentos_totais)):
        if documentos_totais[i][1] == None:
            cur.execute("DELETE from documento WHERE id = {}".format(
                documentos_totais[i][0]))

    for usuario in usuarios:
        if usuario[1] != 7 and usuario[1] != 8:
            documentos = cur.execute(
                'SELECT id FROM documento WHERE usuario_id = {}'.format(usuario[0]))
            documentos = cur.fetchall()
            if documentos != []:
                documentos_selected.append(documentos)

    for documento in documentos_selected:
        for doc in documento:
            cur.execute("DELETE from documentocondicionante WHERE documento_id = {}".format(
                doc[0]))
            cur.execute("DELETE from documento WHERE id = {}".format(
                doc[0]))
    print("Documentos Deletados com Sucesso")
#####################################Excluir Condicionantes#################################################################################################
    condicionantes_vazia = cur.execute(
        'SELECT id,operador from condicionante')
    condicionantes_vazia = cur.fetchall()

    for j in range(len(condicionantes_vazia)):
        if condicionantes_vazia[j][1] == None or condicionantes_vazia[j][1] == 0:
            cur.execute("DELETE from condicionante WHERE id = {}".format(
                condicionantes_vazia[j][0]))

    condicionantes_selected = []
    for usuario in usuarios:
        if usuario[1] != 7 and usuario[1] != 8:
            condicionantes = cur.execute(
                'SELECT id FROM condicionante WHERE operador = {}'.format(usuario[0]))
            condicionantes = cur.fetchall()
            if condicionantes != []:
                condicionantes_selected.append(condicionantes)

    for condicionante in condicionantes_selected:
        for cond in condicionante:
            cur.execute("DELETE from condicionante WHERE id = {}".format(
                cond[0]))
    print("Condicionantes Deletadas com Sucesso")
###################################Excluir órgãos##################################################################################################
    orgao_vazio = cur.execute(
        'SELECT id,operador,descricao from orgaoemissor')
    orgao_vazio = cur.fetchall()

    for k in range(len(orgao_vazio)):
        if orgao_vazio[k][1] == None or orgao_vazio[k][1] == 0:
            cur.execute("DELETE from orgaoemissor WHERE id = {}".format(
                orgao_vazio[k][0]))

    orgao_selected = []
    for usuario in usuarios:
        if usuario[1] != 7 and usuario[1] != 8:
            orgaos = cur.execute(
                'SELECT id FROM orgaoemissor WHERE operador = {}'.format(usuario[0]))
            orgaos = cur.fetchall()
            if orgaos != []:
                orgao_selected.append(orgaos)

    for orgao in orgao_selected:
        for org in orgao:
            cur.execute("DELETE from orgaoemissor WHERE id = {}".format(
                org[0]))
    print("Órgãos deletados com sucesso")
#################################Excluir Diretórios####################################################################################################
    clientes_selecionados = []
    for usuario in usuarios:
        if usuario[1] != 7 and usuario[1] != 8:
            clientes_usuarios = cur.execute(
                "SELECT cliente_id FROM clientes_usuarios WHERE usuario_id = {}".format(usuario[0]))
            clientes_usuarios = cur.fetchall()
            if clientes_usuarios != []:
                clientes_selecionados.append(clientes_usuarios)

    for cliente in clientes_selecionados:
        for cliente_ in cliente:
            diretorios = cur.execute(
                "SELECT nome, id,cliente_id FROM diretorio WHERE cliente_id = {}".format(cliente_[0]))
            diretorios = cur.fetchall()
            for orgaoSelect in orgao_vazio:
                for diretorio in diretorios:
                    if orgaoSelect[2] == diretorio[0]:
                        cur.execute(
                            "DELETE FROM diretorio WHERE id = {}".format(diretorio[1]))
    print("Diretórios deletados com sucesso")
#####################################################################################################################################
    connection.commit()
except (Exception, psycopg2.Error) as error:
    print("Failed to insert record into mobile table", error)

finally:
    # closing database connection.
    if connection:
        cur.close()
        connection.close()
        print("PostgreSQL connection is closed")