import re
import psycopg2
from models import *
from helper import natsort, userrole, connroles, exproles, serbian_sort_key
from datetime import datetime


# DB CONN AND QUERY F
def get_db_connection():
    conn = psycopg2.connect(
            host="10.100.0.176",
            database="test",
            user="rssj_admin",
            password="r55j+ADMAX")
    return conn

def run(q, model=None, params=None):
    conn = get_db_connection()
    cur = conn.cursor()
    if params:
        cur.execute(q, params)
    else:
        cur.execute(q)
    res = cur.fetchall()
    cur.close()
    conn.close()
    if len(res) < 1:
        return []
    if model:
        return [model(*x) for x in res]
    return res

def exec(q, params=None):
    conn = get_db_connection()
    cur = conn.cursor()
    if params:
        cur.execute(q, params)
    else:
        cur.execute(q)
    conn.commit() 
    conn.close() 

def execret(q, params=None):
    conn = get_db_connection()
    cur = conn.cursor()
    if params:
        cur.execute(q, params)
    else:
        cur.execute(q)
    res = cur.fetchone()[0]
    conn.commit() 
    conn.close()
    return res


def get_activity():
    q = f"SELECT username, estatus, edate, COUNT(*) AS activity_count FROM rssj.estatus GROUP BY username, estatus, edate ORDER BY username, estatus, edate;"
    return run(q)


# ENTRY
def get_entry_for_id(id: int):
    q = f"SELECT * FROM rssj.entry where entry_id={id};"
    entry = run(q, model=Entry)[0]
    entry = expand_entry(entry)
    return entry

def entry_exist(id: int):
    q = f"SELECT * FROM rssj.entry where entry_id={id};"
    res = run(q, model=Entry)
    if res:
        return True
    return False

def myentry(eid: int):
    username, role = userrole()
    q = f"SELECT * FROM rssj.estatus where entry_id = {eid} AND username = '{username}' AND active = B'1';"
    return len(run(q)) > 0 or role == "redactor"

def save_sound(eid: int, params: dict):
    q=f'INSERT INTO rssj.pronunciation (entry_id, "soundFile", headword, username) VALUES ({eid}, %(sound)s, %(headword)s, %(username)s);'
    exec(q, params=params)

def delete_sound(pid: int):
    q=f'DELETE FROM rssj.pronunciation where "pronunciation_id"={pid};'
    exec(q,)

def save_entry_for_id(id: int, params: dict):
    if not params["homograph"]:
        q = f"UPDATE rssj.entry set headwords_acc[1]=%(head)s, headwords_acc_ijk[1]=%(head_ijk)s, pos=%(pos)s, homograph_number=NULL where entry_id={id};"
    else:
        q = f"UPDATE rssj.entry set headwords_acc[1]=%(head)s, headwords_acc_ijk[1]=%(head_ijk)s, pos=%(pos)s, homograph_number=%(homograph)s where entry_id={id};"
    
    exec(q, params=params)

def add_headword(eid: int, params: dict, ijk=False):
    if ijk:
        field = "headwords_acc_ijk"
    else:
        field = "headwords_acc"
    if params["id"]:
        q=f"UPDATE rssj.entry SET {field}[%(id)s] = %(val)s WHERE entry_id = {eid};"
    else:
        q=f"UPDATE rssj.entry SET {field} = array_append({field}, %(val)s) WHERE entry_id = {eid};"
    exec(q, params=params)

def rem_headword(eid: int, params: dict, ijk=False):
    if ijk:
        field = "headwords_acc_ijk"
    else:
        field = "headwords_acc"

    q=f"SELECT {field}[1] FROM rssj.entry WHERE entry_id = {eid};"
    hw = execret(q)

    q=f"UPDATE rssj.entry SET {field} = array_remove({field}, %(val)s) WHERE entry_id = {eid};"
    exec(q, params=params)

    if hw == params["val"]:
        q=f"UPDATE rssj.entry SET {field}[1] = %(val)s WHERE entry_id = {eid};"
        exec(q, params=params)


def get_senses_for_entry(id: int):
    q = f"SELECT * FROM rssj.sense WHERE entry_id = {id} AND listing_order > -1 ORDER BY listing_order ASC;"
    senses = run(q, model=Sense)
    sorted_labels = natsort([s.label for s in senses])
    return sorted(senses, key=lambda x: sorted_labels.index(x.label))

def get_status_for_entry(id: int):
    q = f"SELECT DISTINCT ON (username, estatus, entry_id) estatus_id, username, estatus, edate, entry_id, active FROM rssj.estatus where entry_id={id};"
    return run(q, model=Status)

def get_notes_for_entry(id: int):
    q = f"SELECT * FROM rssj.note WHERE entry_id = {id};"
    return run(q, model=Note)

def add_note(user: str, eid: int, params: dict):
    q=f"INSERT INTO rssj.note (entry_id, type, note, author) VALUES ({eid}, %(type)s, %(note)s, '{user}');"
    exec(q, params=params)

def edit_note(eid: int, id: int, params: dict):
    q=f"UPDATE rssj.note SET (type, note) = (%(type)s, %(note)s) WHERE note_id={id} AND entry_id={eid};"
    exec(q, params=params)

def delete_note(eid: int, id: int):
    q=f"DELETE FROM rssj.note WHERE note_id={id} AND entry_id={eid};"
    exec(q)

def get_pronunciations_for_entry(id: int):
    q = f"SELECT * FROM rssj.pronunciation WHERE entry_id = {id};"
    return run(q, model=Pronunciation)

def expand_entry(entry: Entry, expand_senses=False):
    senses = get_senses_for_entry(entry.id)
    if expand_senses:
        senses = [expand_sense(x) for x in senses]
    if len(senses) < 1:
        senses = [create_empty_sense(entry.id)]
    statuses = get_status_for_entry(entry.id)
    pronunciations = get_pronunciations_for_entry(entry.id)
    expressions = get_expressions_for_sense(senses[0].id)
    notes = get_notes_for_entry(entry.id)
    entry.connect(senses, pronunciations, statuses, expressions, notes)
    return entry

# SENSE
def create_empty_sense(eid: int, ord: int = 99):
    if ord==99:
        try:
            ord = execret(f'select max(listing_order) from rssj.sense where entry_id = {eid};') + 1
        except:
            pass
    q = f'INSERT INTO rssj.sense (entry_id, listing_order) VALUES ({eid}, {ord}) RETURNING sense_id;'
    sid = execret(q)
    sid = sid
    sense = get_sense_for_id(sid)
    sense = expand_sense(sense)
    return sense

def get_sense_for_id(id: int):
    q = f"SELECT * FROM rssj.sense where sense_id={id};"
    sense = run(q, model=Sense)[0]
    sense = expand_sense(sense)
    return sense

def save_sense_for_id(id: int, params: dict):
    q = f"UPDATE rssj.sense set grammar_ijk=%(grammar_ijk)s, grammar=%(grammar)s where sense_id={id};"
    exec(q, params=params)

def save_senseo_for_id(id: int, params: dict):
    q = f"UPDATE rssj.sense set sense_label=%(label)s where sense_id={id};"
    exec(q, params=params)

def get_grammar_for_entry(id: int):
    q = f"SELECT * FROM rssj.grammar WHERE sense_id = {id};"
    return run(q, model=Grammar)

def get_defintions_for_entry(id: int):
    q = f"SELECT * FROM rssj.definition WHERE sense_id = {id} ORDER BY CASE WHEN type = 'описна' THEN 1 WHEN type = 'синонимна' THEN 2 ELSE 4 END;"
    return run(q, model=Definition)

def get_examples_for_entry(id: int):
    q = f"SELECT * FROM rssj.example WHERE sense_id = {id} ORDER BY example_id ASC;"
    return run(q, model=Example)

def get_expressions_for_sense(sid: int, filter=exproles):
    q = f"SELECT entry_id, member_sense_id, relation_id, role, sense_label FROM rssj.member JOIN rssj.sense on member_sense_id=sense_id WHERE relation_id IN (SELECT DISTINCT relation_id FROM rssj.member WHERE member_sense_id = {sid}) AND member_sense_id != {sid} AND role in {filter};"
    eids_sids_rids = run(q)
    exp = []
    for eid, sid, rid, role, label in eids_sids_rids:
        entry = get_entry_for_id(eid)
        if label:
            exp.append(Expression(eid, sid, entry.head + " " + label, rid, role))
        else:
            exp.append(Expression(eid, sid, entry.head, rid, role))
    exp = sorted(exp, key=lambda x: (x.role, serbian_sort_key(x.head)))
    return exp

def get_connected_for_sense(sid: int):
    return get_expressions_for_sense(sid, filter=connroles)

def remove_expression(sid: int, rid: int, eid: int, xsid: int):
    remove_connection(sid, rid, xsid)
    q = f"DELETE FROM rssj.sense WHERE sense_id = {xsid};"
    exec(q)
    drop_reserve_id(eid)
    q = f"DELETE FROM rssj.entry WHERE entry_id = {eid};"
    exec(q)

def remove_connection(sid: int, rid: int, xsid: int):
    q = f"DELETE FROM rssj.member WHERE relation_id={rid} AND (member_sense_id={sid} OR member_sense_id={xsid})"
    exec(q)

def change_expression_type(sid: int, rid: int, xsid: int, params: dict):
    params["type1"] = params["type"].split("|")[0]
    params["type2"] = params["type"].split("|")[1]
    q = f"UPDATE rssj.member SET role=%(type1)s WHERE relation_id={rid} AND member_sense_id={xsid};"
    exec(q, params=params)
    q = f"UPDATE rssj.member SET role=%(type2)s WHERE relation_id={rid} AND member_sense_id={sid};"
    exec(q, params=params)

def get_sense_from_string(input: str):
    if " " in input:
        entry, sense = input.split(" ", 1)
    else:
        entry = input
        sense = ""
    try:
        entry = search_string(entry, "exact2", full = None, x=1)[0]

        if sense:
            q = f"SELECT sense_id from rssj.sense WHERE entry_id = {entry.id} AND listing_order > -1 AND TRIM(sense_label) = %(sense)s;"
            sid = execret(q, params={"sense": sense.strip()})
        else:
            q = f"SELECT sense_id from rssj.sense WHERE entry_id = {entry.id} AND listing_order > -1 ORDER BY listing_order ASC"
            sid = execret(q)
        return sid, entry.id
    except:
        return None, None


def expand_sense(sense: Sense):
    username, role = userrole()
    grammars = get_grammar_for_entry(sense.id)
    definitions = get_defintions_for_entry(sense.id)
    examples = get_examples_for_entry(sense.id)
    connected = get_connected_for_sense(sense.id)
    if role == "redactor":
        my = True
    else:
        q = f"SELECT * FROM rssj.estatus where entry_id = {sense.eid} AND username = '{username}' AND active = B'1';"
        my = len(run(q)) > 0
    sense.connect(grammars, definitions, examples, connected, my)
    return sense


def add_gram_sense(sid: int, params: dict):
    if params["id"]:
        q=f"UPDATE rssj.grammar SET (sense_id, tag, text) = ({sid}, %(tag)s, %(text)s) WHERE grammar_id=%(id)s;"
    else:
        q=f"INSERT INTO rssj.grammar (sense_id, tag, text) VALUES ({sid}, %(tag)s, %(text)s);"
    exec(q, params=params)

def rem_gram_sense(gid: int):
    q=f"DELETE FROM rssj.grammar WHERE grammar_id = {gid};"
    exec(q)

def add_def_sense(sid: int, params: dict):
    if params["id"]:
        q=f"UPDATE rssj.definition SET (sense_id, type, text) = ({sid}, %(type)s, %(text)s) WHERE definition_id = %(id)s;"
    else:
        q=f"INSERT INTO rssj.definition (sense_id, type, text) VALUES ({sid}, %(type)s, %(text)s);"
    exec(q, params=params)

def rem_def_sense(did: int):  
    q=f"DELETE FROM rssj.definition where definition_id = {did};"
    exec(q)

def add_ex_sense(sid: int, params: dict):
    if params["id"]:
        q=f"UPDATE rssj.example SET (sense_id, type, text, label) = ({sid}, %(type)s, %(text)s, %(label)s) WHERE example_id = %(id)s;"
    else:
        q=f"INSERT INTO rssj.example (sense_id, type, text, label) VALUES ({sid}, %(type)s, %(text)s, %(label)s);"
    exec(q, params=params)

def rem_ex_sense(eid: int):  
    q=f"DELETE FROM rssj.example where example_id = {eid};"
    exec(q)

def rem_sense(sid: int, eid: int):
    q=f"UPDATE rssj.sense set listing_order = -1 where sense_id = {sid};"
    exec(q)

def add_val_to_sense(sid: int, t: str, params: dict):
    if t in ["collocations", "domains", "qualifiers"]:
        if params["id"]:
            q=f"UPDATE rssj.sense SET {t}[%(id)s] = %(val)s WHERE sense_id = {sid};"
        else:
            q=f"UPDATE rssj.sense SET {t} = array_append({t}, %(val)s) WHERE sense_id = {sid};"
        exec(q, params=params)

def rem_val_from_sense(sid: int, t: str, params: dict):
    if t in ["collocations", "domains", "qualifiers"]:
        q=f"UPDATE rssj.sense SET {t} = array_remove({t}, %(val)s) WHERE sense_id = {sid};"
        exec(q, params=params)


# BROWSED ENTRY

def get_x_entries(x: int = 20):
    q = f"SELECT headwords_acc[1], entry_id, homograph_number FROM rssj.entry WHERE rank is not NULL ORDER BY rank, frequency_phm DESC LIMIT {x};"
    return run(q, model=Browsed_entry)

def list_x_entries(x: int = 20):
    q = f"SELECT headwords_acc[1], entry_id, homograph_number FROM rssj.entry ORDER BY entry_id LIMIT {x};"
    return run(q, model=Browsed_entry)

def get_my(x: int = 10):
    username, role = userrole()
    q = f"SELECT entry_id FROM rssj.estatus WHERE estatus='editing' and active=B'1' and username='{username}' LIMIT {x};"
    res = run(q)
    if len(res)>0:
        return get_browsed_entries(res)
    return None

def get_edited(x: int = 20):
    q = f"SELECT entry_id, ARRAY_AGG(estatus ORDER BY estatus) AS estatus_list FROM rssj.estatus GROUP BY entry_id HAVING ARRAY_AGG(estatus ORDER BY estatus) @> ARRAY['edited'] AND NOT ARRAY_AGG(estatus ORDER BY estatus) && ARRAY['redacted'] LIMIT {x};"
    res = run(q)
    if len(res)>0:
        return get_browsed_entries(res)
    return None

def search_string(stri, type, full = None, x: int = 20):
    narr = " AND type='одредница'"
    if full == "on":
        narr = ""
    if type == "exact2":
        narr = ""
        q = f"SELECT distinct(entry_id) FROM(SELECT unnest(headwords_acc) head, homograph_number, entry_id, type FROM rssj.entry) x WHERE lower(concat(unaccent(head),homograph_number))=%(inp)s{narr} LIMIT {x};"
    elif type == "exact":
        q = f"SELECT distinct(entry_id) FROM(SELECT unnest(headwords_acc) head, homograph_number, entry_id, type FROM rssj.entry) x WHERE lower(concat(unaccent(head)))=%(inp)s{narr} LIMIT {x};"
    else:
        q = f"SELECT distinct(entry_id) FROM(SELECT unnest(headwords_acc) head, homograph_number, entry_id, type FROM rssj.entry) x WHERE lower(unaccent(head)) LIKE %(inp)s{narr} LIMIT {x};"
        if type == "begins_w":
            stri = '{}%'.format(stri)
        elif type == "ends_w":
            stri = '%{}'.format(stri)
        elif type == "contains":
            stri = '%{}%'.format(stri)
    res = run(q, params={"inp": stri.lower()})
    if len(res)>0:
        return get_browsed_entries(res)
    return None

def get_browsed_entries(ids: list):
    ids = ",".join([str(x[0]) for x in ids])
    q = f"SELECT headwords_acc[1], entry_id, homograph_number from rssj.entry where entry_id IN ({ids});"
    return run(q, model=Browsed_entry)

def try_reserve(user, id: int):
    q = f"SELECT * FROM rssj.estatus WHERE entry_id = {id} and active=b'1';"
    status = run(q, Status)
    if not status:
        dt = datetime.now()
        q=f"INSERT INTO rssj.estatus (username, estatus, edate, entry_id, active) VALUES ('{user}', 'editing', '{dt}', {id}, b'1');"
        exec(q)

def drop_reserve(user, id: int):
    q = f"DELETE FROM rssj.estatus WHERE entry_id = {id} and username = '{user}';"
    exec(q)

def drop_reserve_id(id: int):
    q = f"DELETE FROM rssj.estatus WHERE entry_id = {id};"
    exec(q)

def finish_edit(user, id: int):
    q = f"SELECT * FROM rssj.estatus WHERE entry_id = {id} and active=b'1' and username='{user}';"
    status = run(q, model=Status)
    if status:
        q=f"UPDATE rssj.estatus set active = b'0' WHERE entry_id = {id} and active=b'1' and username='{user}';"
        exec(q)
        dt = datetime.now()
        q=f"INSERT INTO rssj.estatus (username, estatus, edate, entry_id, active) VALUES ('{user}', 'edited', '{dt}', {id}, b'1');"
        exec(q)

def finish_redact(user, id: int):
    q=f"UPDATE rssj.estatus set active = b'0' WHERE entry_id = {id} and active=b'1' and username='{user}';"
    exec(q)
    dt = datetime.now()
    q=f"INSERT INTO rssj.estatus (username, estatus, edate, entry_id, active) VALUES ('{user}', 'redacted', '{dt}', {id}, b'1');"
    exec(q)


def add_entry(user, id: int=None, stri: str=""):
    if id:
        old_entry = get_entry_for_id(id)
        oid = old_entry.id
        if old_entry.homograph:
            oid = oid - (old_entry.homograph-1)*1000000
        else:
            q=f"UPDATE rssj.entry SET homograph_number=1 where entry_id={id};"
            exec(q)
        homograph_taken = True
        i = 1
        while homograph_taken:
            try_id = i*1000000 + oid
            if not entry_exist(try_id):
                homograph_taken = False
            i += 1
        new_id = try_id

        hwa = [old_entry.head]
        if old_entry.headwords_acc:
            hwa = hwa + old_entry.headwords_acc.split(";")
        hwa = ",".join(hwa)

        hwa = ",".join(["\"" + x + "\"" for x in old_entry.headwords_acc.split(";")])
        pos = old_entry.pos
        et = old_entry.type
        rank = old_entry.rang
        freq = old_entry.freq

        dt = datetime.now()
        q=f"INSERT INTO rssj.entry (entry_id, headwords_acc, headwords, pos, homograph_number, type, rank, frequency_phm) VALUES ({new_id}, '{{{hwa}}}', '{{}}', '{pos}', {i}, '{et}', {rank}, {freq});"
        exec(q)
        q=f"INSERT INTO rssj.estatus (username, estatus, edate, entry_id, active) VALUES ('{user}', 'editing', '{dt}', {new_id}, b'1');"
        exec(q)
        return new_id
    else:
        new_id = get_maxid()
        dt = datetime.now()

        q = f"INSERT INTO rssj.entry (entry_id, headwords_acc, headwords, type) VALUES ({new_id}, %(hwa)s, %(hwa)s, 'одредница');"
        exec(q, params={"hwa": [stri]})

        new_sense = create_empty_sense(new_id, ord=0)
        new_sid = new_sense.id
        q = f"INSERT INTO rssj.estatus (username, estatus, edate, entry_id, active) VALUES ('{user}', 'editing', '{dt}', {new_id}, b'1');"
        exec(q)
        return new_id

def add_expression(user, sid: int, headword: str, role: str, etype: str = "subentry"):

    new_id = get_maxid()
    hwa = [headword]
    et = etype
    dt = datetime.now()
    if "повратни гл" in role:
        q = f"INSERT INTO rssj.entry (entry_id, headwords_acc, headwords, type, pos) VALUES ({new_id}, %(hwa)s, %(hwa)s, '{et}', 'гл.');"
    else:
        q = f"INSERT INTO rssj.entry (entry_id, headwords_acc, headwords, type) VALUES ({new_id}, %(hwa)s, %(hwa)s, '{et}');"

    exec(q, params={"hwa": hwa})

    new_sense = create_empty_sense(new_id, ord=0)
    new_sid = new_sense.id
    make_relation("изр.", {str(new_sid): role.split("|")[0], str(sid): role.split("|")[1]} )

    q = f"INSERT INTO rssj.estatus (username, estatus, edate, entry_id, active) VALUES ('{user}', 'editing', '{dt}', {new_id}, b'1');"
    exec(q)


def add_connection(sid: int, new_sid : int, role: str):
    make_relation("веза", {str(new_sid): role.split("|")[0], str(sid): role.split("|")[1]} )


def get_userstatuses():
    q = f"SELECT DISTINCT ON (username, estatus, s.entry_id) estatus_id, username, estatus, edate, s.entry_id, active, e.headwords_acc[1], e.homograph_number, e.pos, n.type as note, CASE WHEN s.entry_id IN (Select entry_id from rssj.sense where sense_id in (select member_sense_id from rssj.member where (role LIKE 'израз%' OR role LIKE 'пословица%') AND role NOT LIKE '%за')) THEN 'subentry' ELSE e.type END AS etype FROM rssj.estatus as s JOIN rssj.entry as e ON s.entry_id=e.entry_id LEFT JOIN rssj.note as n ON n.entry_id=e.entry_id;"
    return User_statuses(run(q, model=Status2))

def get_maxid(bottom: int = 1000000):
    q = f"SELECT entry_id FROM rssj.entry ORDER BY entry_id DESC LIMIT 1"
    maxid = run(q)[0][0] + 1
    if maxid > bottom:
        return maxid
    return bottom

def empty_relation(t):
    q = f'INSERT INTO rssj.relation (type) VALUES (%(t)s) RETURNING relation_id;'
    rid = execret(q, params={"t": t})
    return rid 

def new_member(rel_id, sid, role):
    q = f'INSERT INTO rssj.member (relation_id, member_sense_id, role) VALUES ({rel_id}, %(sid)s, %(role)s);'
    exec(q, params={"sid": sid, "role": role}) 

def make_relation(t, sense_role_dict):
    rid = empty_relation(t)
    for sense in sense_role_dict:
        new_member(rid, int(sense), sense_role_dict[sense])

def get_all_headwords():
    q = f"SELECT headwords_acc[1] FROM rssj.entry WHERE entry_id in(SELECT entry_id FROM rssj.estatus WHERE estatus='edited' AND active=b'1');"
    return run(q)

def get_completed_entries():
    q = f"SELECT * FROM rssj.entry WHERE entry_id in(SELECT entry_id FROM rssj.estatus WHERE estatus='redacted' AND active=b'1') order by lower(unaccent(headwords_acc[1])), homograph_number;"
    return run(q, model=Entry)


def dump_sql_table(table_name, id_column, ids, type_filter=None):
    id_filter = "(" + ",".join([str(i) for i in ids])+ ")"
    result = ""
    tf = ""
    conn = get_db_connection()
    cursor = conn.cursor()
    if type_filter:
        if table_name in type_filter:
            tf = type_filter[table_name]
    q = f"SELECT * FROM {table_name} WHERE {id_column} IN {id_filter} {tf};"
    cursor.execute(q) 
    column_names = []
    columns_descr = cursor.description
    for c in columns_descr:
        column_names.append(c[0])
    insert_prefix = f"INSERT INTO {table_name} ({', '.join(column_names)}) VALUES "
    rows = cursor.fetchall()
    if not rows:
        return ""
    for row in rows:
        row_data = []
        for rd in row:
            if rd is None:
                row_data.append('NULL')
            elif isinstance(rd, datetime):
                row_data.append("'%s'" % (rd.strftime('%Y-%m-%d %H:%M:%S') ))
            else:
                row_data.append(repr(rd))
        result += f"{insert_prefix} ({', '.join(row_data)});\n"
    conn.commit() 
    conn.close()
    return result + "\n"

POS = run(f"select tag from rssj.catalog where category = 'врста речи';", model=str)
gram = run(f"select tag from rssj.catalog where category = 'граматика';", model=str)
dom = run(f"select tag from rssj.catalog where category = 'терминологија' order by 1 ASC;", model=str)
qual = run(f"select tag from rssj.catalog where category in ('стил', 'временски', 'нестандардно') order by 1 ASC;", model=str)
ety = run(f"select tag from rssj.catalog where category = 'етимологија' order by 1 ASC;", model=str)


def delete_entry_by_id(id):
    q = f"SELECT * FROM rssj.sense WHERE entry_id = {id};"
    senses = run(q, model=Sense)
    sense_tables = ["grammar", "example", "definition", "corpus_frequency"]
    entry_tables = ["sense", "pronunciation", "note", "estatus", "entry"]
    for s in senses:
        q = f"DELETE FROM rssj.member WHERE member_sense_id = {s.id};"
        exec(q)
        for tab in sense_tables:
            q = f"DELETE FROM rssj.{tab} WHERE sense_id = {s.id};"
            exec(q)
    for tab in entry_tables:
        q = f"DELETE FROM rssj.{tab} WHERE entry_id = {id};"
        exec(q)

def get_entries_for_delete():
    q = "select *  from rssj.entry where lower(headwords_acc_ijk[1]) like 'избацује се%'"
    return [str(x[0]) for x in run(q)]

def get_catalog():
    q=f"SELECT tag, name FROM rssj.catalog;"
    return run(q)