# Check if the SQLite database file exists
if not os.path.exists('work_hours.db'):
    print("Database file 'work_hours.db' not found. Creating a new one.")
    open('work_hours.db', 'w').close()  # Create an empty file

# Create or connect to the SQLite database
conn = sqlite3.connect('work_hours.db')
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE IF NOT EXISTS work_hours (
        user_id INTEGER,
        total_hours INTEGER,
        clock_in DATETIME,
        clock_out DATETIME
    )
''')
conn.commit()

@bot.tree.command()
async def inklokken(interaction:discord.Interaction):
    user_id = interaction.author.id
    clock_in_time = datetime.now()

    # Check if the user is already clocked in
    cursor.execute('SELECT * FROM work_hours WHERE user_id = ? AND clock_out IS NULL', (user_id,))
    existing_entry = cursor.fetchone()

    embed = discord.Embed(title="Gewerkte uren", color=kleur)
    amsterdam_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    embed.set_thumbnail(url=logo)
    embed.set_image(url=banner_logo)
    embed.set_footer(text=f"Tijd: {amsterdam_time}")

    if existing_entry:
        embed.add_field(name=f"{user_id} heeft:", value=f"is al ingeklokt", inline=False)
    else:
        cursor.execute('INSERT INTO work_hours (user_id, total_hours, clock_in) VALUES (?, 0, ?)', (user_id, clock_in_time))
        conn.commit()
        embed.add_field(name=f"{user_id} heeft:", value=f"succesvol ingeklokt", inline=False)

    await interaction.response.send_message(laadgif)
    time.sleep(4)
    await interaction.edit_original_response(embed=embed, content=None)

@bot.tree.command()
async def uitklokken(interaction:discord.Interaction):
    user_id = interaction.author.id
    clock_out_time = datetime.now()

    # Update the clock_out time for the latest entry of the user
    cursor.execute('UPDATE work_hours SET clock_out = ? WHERE user_id = ? AND clock_out IS NULL', (clock_out_time, user_id))

    # Calculate the hours worked and update the total_hours
    cursor.execute('SELECT clock_in, clock_out FROM work_hours WHERE user_id = ? AND clock_out IS NOT NULL', (user_id,))
    entries = cursor.fetchall()

    total_hours = 0
    for entry in entries:
        clock_in = datetime.strptime(entry[0], '%Y-%m-%d %H:%M:%S.%f')  # Add %f for microseconds
        clock_out = datetime.strptime(entry[1], '%Y-%m-%d %H:%M:%S.%f')
        total_hours += (clock_out - clock_in).total_seconds()

    cursor.execute('UPDATE work_hours SET total_hours = ? WHERE user_id = ?', (total_hours, user_id))
    conn.commit()

    embed = discord.Embed(title="Gewerkte uren", color=kleur)
    amsterdam_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    embed.set_thumbnail(url=logo)
    embed.set_image(url=banner_logo)
    embed.set_footer(text=f"Tijd: {amsterdam_time}")
    embed.add_field(name=f"{user_id} heeft:", value=f"succesvol uitgeklokt", inline=False)

    await interaction.response.send_message(laadgif)
    time.sleep(4)
    await interaction.edit_original_response(embed=embed, content=None)


@bot.tree.command()
async def check_hours(interaction:discord.Interaction):
    user_id = interaction.author.id

    # Retrieve the total hours worked for the user
    cursor.execute('SELECT total_hours FROM work_hours WHERE user_id = ?', (user_id,))
    total_hours = cursor.fetchone()

    embed = discord.Embed(title="Gewerkte uren", color=kleur)
    amsterdam_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    embed.set_thumbnail(url=logo)
    embed.set_image(url=banner_logo)
    embed.set_footer(text=f"Tijd: {amsterdam_time}")

    if total_hours is not None:
        hours, remainder = divmod(total_hours[0], 3600)
        minutes, seconds = divmod(remainder, 60)
        embed.add_field(name=f"{user_id} heeft:", value=f"{int(hours)} uur en {int(minutes)} minuten gewerkt", inline=False)
    else:
        embed.add_field(name=f"{user_id} heeft:", value=f"geen werkuren gevonden", inline=False)

    await interaction.response.send_message(laadgif)
    time.sleep(4)
    await interaction.edit_original_response(embed=embed, content=None)