import sqlite3
import time
import random
import os
import os.path

# Define a function to create a database and table
def create_db():
    if os.path.isfile('test.db'):
        os.remove('test.db')
        print("file removed")
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS test_table (
                        id INTEGER PRIMARY KEY,
                        value INTEGER)''')
    conn.commit()
    conn.close()
    print("test.db created")

# Function for testing insert performance
def test_insert(num_records):
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    start_time = time.time()
    for _ in range(num_records):
        cursor.execute('INSERT INTO test_table (value) VALUES (?)', (random.randint(1, 100),))
    conn.commit()
    end_time = time.time()
    print(f"Time to insert {num_records} records: {end_time - start_time:.4f} seconds")
    conn.close()
    return end_time - start_time

# Function for testing select performance
def test_select(num_records):
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    start_time = time.time()
    cursor.execute('SELECT value FROM test_table LIMIT ?', (num_records,))
    rows = cursor.fetchall()
    end_time = time.time()
    print(f"Time to select {num_records} records: {end_time - start_time:.4f} seconds")
    conn.close()
    return end_time - start_time

# Function for testing update performance
def test_update(num_updates):
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    start_time = time.time()
    for _ in range(num_updates):
        cursor.execute('UPDATE test_table SET value = ? WHERE id = ?',
                       (random.randint(1, 100), random.randint(1, num_updates)))
    conn.commit()
    end_time = time.time()
    print(f"Time to update {num_updates} records: {end_time - start_time:.4f} seconds")
    conn.close()
    return end_time - start_time

# Function for testing delete performance
def test_delete(num_deletes):
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    start_time = time.time()
    for _ in range(num_deletes):
        cursor.execute('DELETE FROM test_table WHERE id = ?',
                       (random.randint(1, num_deletes),))
    conn.commit()
    end_time = time.time()
    print(f"Time to delete {num_deletes} records: {end_time - start_time:.4f} seconds")
    conn.close()
    return end_time - start_time

# Main function to run the tests
def run_tests():
    res_ins = []
    res_select = []
    res_update = []
    res_delete = []
    print("New Run")
    for i in range(20):
        print("iteration: ", i)
        create_db()  # Make sure the database and table exist
        # Test Insert Performance
        res_ins.append(str(test_insert(10000000)).replace(".",","))
        # Test Select Performance
        res_select.append(str(test_select(10000000)).replace(".",","))
        # Test Update Performance
        res_update.append(str(test_update(5000000)).replace(".",","))
        # Test Delete Performance
        res_delete.append(str(test_delete(5000000)).replace(".",","))
    print("insert: ", ";".join(res_ins))
    print("select: ", ";".join(res_select))
    print("update: ", ";".join(res_update))
    print("delete: ", ";".join(res_delete))


if __name__ == '__main__':
    run_tests()