Basics

# ALl of our imports
import sqlite3
from csv import excel_tab


# Step 1 - Setup / Initialize Database
def get_connection(db_name):
    try:
        return sqlite3.connect(db_name)
    except Exception as e:
        print(f"Error: {e}")
        raise

# Step 2 - Create a Table in the Database

def create_table(connection):
    query = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE
    )
    """
    try:
        with connection:
            connection.execute(query)
        print("Table created successfully")
    except Exception as e:
        print(f"Error: {e}")


# Step 3 - Add User to Database
def insert_user(connection, name:str, age:int, email:str):
    query = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)" #The question makes allow us to parameterize the values.
    try:
        with connection:
            connection.execute(query, (name, age, email))
        print(f'User "{name}" inserted successfully')
    except Exception as e:
        print(f"Error: {e}")

# Step 4 - Query all Users in Database
def fetch_users(connection, condition: str = None) -> list[tuple]:
    query = "SELECT * FROM users" #Select everything (*) from users
    if condition:
        query += f" WHERE {condition}" #Where condition is true
    try:
        with connection:
            rows = connection.execute(query).fetchall()
        return rows
    except Exception as e:
        print(f"Error: {e}")

# Step 5 - Delete a User from the Database
def delete_user(connection, user_id:int):
    query = "DELETE FROM users WHERE id = ?" #? is a placeholder to parameterize information letting it be dynamic
    try:
        with connection:
            connection.execute(query, (user_id,))
        print(f"User {user_id} deleted successfully")
    except Exception as e:
        print(f"Error: {e}")

#Step 6 - Update an existing User
def update_user(connection, user_id:int, email:str):
    query = "UPDATE users SET email = ? WHERE id = ?" #Updates table and sets the email to become this wherever the ID matches this
    try:
        with connection:
            connection.execute(query, (email, user_id))
        print(f"User {user_id} updated successfully")
    except Exception as e:
        print(f"Error: {e}")

#Step 7 - Ability to add Multiple Users
def insert_users(connection, users:list[tuple[str, int, str]]):
    query = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)" #Insert into (table name)
    try:
        with connection:
            connection.executemany(query, users) #this executes as many times as there are elements in the users list
        print(f"{len(users)} users were inserted successfully")
    except Exception as e:
        print(f"Error: {e}")

#Main Function Wrapper
def main():
    connection = get_connection("subscribe.db")

    try:
        create_table(connection)


        start = input("Enter Option (Add, Delete, Update, Search, Add Many, View Details):").lower()
        if start == 'add':
            name = input("Enter Name: ")
            age = int(input("Enter Age: "))
            email = input("Enter Email: ")
            insert_user(connection, name, age, email)
        elif start == 'search':
            condition = input("Who are you looking for? (If all then enter nothing): ")
            rows = fetch_users(connection, condition)
            print(rows)
            print(f"This is all the data relating to {condition}")
        elif start == 'delete':
            user_id = int(input("Enter User ID: "))
            delete_user(connection, user_id)
        elif start == 'update':
            user_id = int(input("Enter User ID whose email you want to change: "))
            email = input("Enter New Email: ")
            update_user(connection, user_id, email)
        elif start == 'add many':
            users = [('Chandler', 29, "friends@none.com"),
                     ("Truman", 37, "truman@theshow.com"),
                     ("Olga", 68, "siberian_grandma@gmail.com")]
            insert_users(connection, users)



    finally:
        connection.close()

if __name__ == "__main__":
    looper = 1
    while looper == 1:
        main()