import os
import sqlite3
import contextlib
import tkinter.filedialog


def scan_english_bibles():
    print('select folder')
    folder = tkinter.filedialog.askdirectory()
    for filename in os.listdir(folder):
        if not filename.endswith('.bdb'):
            continue
        with contextlib.closing(sqlite3.connect(os.path.join(folder, filename))) as connection:
            cursor = connection.execute("select count(*) from bible where btext like '%▷%';")
            print(filename, cursor.fetchone())


def update_english_bibles():
    print('select folder')
    folder = tkinter.filedialog.askdirectory()
    for filename in os.listdir(folder):
        if not filename.endswith('.bdb'):
            continue
        with contextlib.closing(sqlite3.connect(os.path.join(folder, filename))) as connection:
            cursor = connection.execute("update bible set btext = concat(substr(btext, 0, instr(btext, '▷')), '<span style=\"color:#777777;\">', substr(btext, instr(btext, '▷')), '</span>') where btext like '%<br>▷%' and btext not like '%</span>';")
            print(cursor.rowcount)
            connection.commit()


def scan_korean_bibles():
    print('select folder')
    folder = tkinter.filedialog.askdirectory()
    for filename in os.listdir(folder):
        if not filename.endswith('.bdb'):
            continue
        with contextlib.closing(sqlite3.connect(os.path.join(folder, filename))) as connection:
            cursor1 = connection.execute("select count(*) from bible where btext like '(%)%';")  # no good at all
            cursor2 = connection.execute("select count(*) from bible where btext like '[%]%';")  # not okay for a few
            cursor3 = connection.execute("select count(*) from bible where btext like '<%>%' and btext not like '<span %' and btext not like '<font %' and btext not like '<sup>%' and btext not like '<small>%' and btext not like '<_>%';")  # okay for select books
            cursor4 = connection.execute("select count(*) from bible where btext like '{%}%';")  # only in some files
            print(f"{filename:30}(*): {cursor1.fetchone()[0]:<5}  [*]: {cursor2.fetchone()[0]:<5}  <*>: {cursor3.fetchone()[0]:<5}  {{*}}: {cursor4.fetchone()[0]:<5}")


def update_korean_bibles():
    # print('Select bdb files')
    # filelist = tkinter.filedialog.askopenfilenames()
    # for filename in filelist:
        # with contextlib.closing(sqlite3.connect(filename)) as connection:
    print('select folder')
    folder = tkinter.filedialog.askdirectory()
    for filename in os.listdir(folder):
        if not filename.endswith('.bdb'):
            continue
        with contextlib.closing(sqlite3.connect(os.path.join(folder, filename))) as connection:
            print(f"{filename:20}", end=', ')
            square_bracket_count = connection.execute("select count(*) from bible where btext like '[%]%';").fetchone()[0]
            if square_bracket_count > 110:
                cursor1 = connection.execute("update bible set btext = concat('<span style=\"color:#996699;\">', substr(btext, 0, instr(btext, ']')+1), '</span>', substr(btext, instr(btext, ']')+1)) where btext like '[%]%';")
                print(f"{cursor1.rowcount:6}", end=', ')
            else:
                print(f"{'N/A':>6}", end=', ')
            angle_bracket_count = connection.execute("select count(*) from bible where btext like '<%>%' and btext not like '<span %' and btext not like '<font %' and btext not like '<sup>%' and btext not like '<small>%' and btext not like '<_>%';").fetchone()[0]
            if angle_bracket_count > 110:
                # cursor2 = connection.execute("update bible set btext = concat('<span style=\"color:#996699;\">', substr(btext, 0, instr(btext, '>')+1), '</span>', substr(btext, instr(btext, '>')+1)) where btext like '<%>%' and btext not like '<span %' and btext not like '<font %' and btext not like '<sup>%' and btext not like '<small>%' and btext not like '<_>%';")  # and substr(btext, 1, 6) != '<span '
                cursor2 = connection.execute("update bible set btext = concat('<span style=\"color:#996699;\">&lt;', substr(btext, 2, instr(btext, '>')-2), '&gt;</span>', substr(btext, instr(btext, '>')+1)) where btext like '<%>%' and btext not like '<span %' and btext not like '<font %' and btext not like '<sup>%' and btext not like '<small>%' and btext not like '<_>%';")  # and substr(btext, 1, 6) != '<span '
                print(f"{cursor2.rowcount:6},")
            else:
                print(f"{'N/A':>6}")
            connection.commit()


def main():
    # scan_english_bibles()
    update_english_bibles()
    # scan_korean_bibles()
    update_korean_bibles()


if __name__ == '__main__':
    main()