import aiosqlite
import asyncio
import random
import sqlite3
import string
import threading
import time
import traceback
from builtins import *


async def main():
    data_insert = [("insert", (random_string(4), random_string(222), random_string(223))) for _ in range(2000)]
    data_delete = [("delete", random_string(4)) for _ in range(2000)]
    data_update = [("update", random_string(4)) for _ in range(2000)]
    data_select = [("select", random_string(4)) for _ in range(2000)]
    print(f"{threading.get_ident()}")
    async with aiosqlite.connect('test.db', isolation_level=None, timeout=30) as connection:
        await connection.execute('''CREATE TABLE IF NOT EXISTS MyTable(id TEXT, data1 TEXT, data2 TEXT)''')
        await connection.execute("CREATE INDEX IF NOT EXISTS report_uuid_search ON MyTable (data1)")
        await connection.execute("CREATE INDEX IF NOT EXISTS report_patient_search ON MyTable (data2)")

        data = data_insert + data_delete + data_update + data_select
        random.shuffle(data)
        for operation, item in data:
            start_time = time.time()
            # print(f'{threading.get_ident()} {operation} {item}')
            try:
                if operation == 'insert':
                    await try_connect(connection, "INSERT INTO MyTable VALUES (?, ?, ?)", item)
                elif operation == 'delete':
                    await try_connect(connection, "DELETE FROM MyTable WHERE id = ?", (item,))
                elif operation == 'update':
                    await try_connect(connection, "UPDATE MyTable SET data1 = 'NONE' WHERE id = ?", (item,))
                elif operation == 'select':
                    await try_connect(connection, "SELECT * FROM MyTable WHERE id = ?", (item,))
            except Exception as e:
                ms_time = (time.time() - start_time) * 1000
                print(f'EXCEPTION {e} {type(e).__name__} {threading.get_ident()} {operation} - took {ms_time} ms')
                traceback.print_exc()
            finally:
                ms_time = (time.time() - start_time) * 1000
                print(f'{threading.get_ident()} {operation} - took {ms_time} ms')
                if ms_time > 20000:
                    print(f'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH {operation} - took {ms_time} ms')
                elif ms_time > 5000:
                    print(f'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ {operation} - took {ms_time} ms')
                elif ms_time > 1000:
                    print(f'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF {operation} - took {ms_time} ms')
    print("Ending")


def random_string(length):
    return ''.join(random.choice(string.ascii_letters) for _ in range(length))


async def try_connect(connection, query: str, fmt):
    retries = 3
    while True:
        try:
            await connection.execute(query, fmt)
            print("AA")
            return
        except sqlite3.OperationalError as e:
            retries -= 1
            if retries == 0:
                print("GOT EXCEPTION, no more to try")
                raise e
            print(f"GOT EXCEPTION, trying again - retries number {retries}")
            await asyncio.sleep(2.0)
        except Exception as e:
            print(f"Unhandled EXCEPTION - {type(e).__name__}")
            raise e


asyncio.run(main())