import sqlite3
import os

def connect_db():
    """
    Connects to the SQLite database file and returns the connection object.
    If the file does not exist, it prints an error message.
    """
    db_file = 'time_tracker.db'
    if not os.path.exists(db_file):
        print(f"Error: Database file '{db_file}' not found. Please run the database creation script first.")
        return None
    
    return sqlite3.connect(db_file)

def display_client_details(client_data):
    """
    Displays the details of a single client in a readable format.
    """
    print("\n--- Client Details ---")
    print(f"ID: {client_data[0]}")
    print(f"Name: {client_data[1]}")
    print(f"Street Address 1: {client_data[2]}")
    print(f"Street Address 2: {client_data[3]}")
    print(f"City: {client_data[4]}")
    print(f"State: {client_data[5]}")
    print(f"Zip Code: {client_data[6]}")
    print(f"Billing Rate: {client_data[7]:.2f}")
    print(f"Balance: {client_data[8]:.2f}")
    print(f"Active: {'Yes' if client_data[9] else 'No'}")
    print("----------------------\n")

def add_new_client():
    """
    Prompts the user for information to create a new client and adds it to the database.
    """
    conn = connect_db()
    if not conn:
        return

    try:
        cursor = conn.cursor()
        print("\n--- Add New Client ---")

        # Prompt for each field
        client_name = input("Enter client name: ")
        street_address_1 = input("Enter street address 1: ")
        street_address_2 = input("Enter street address 2 (optional): ")
        city = input("Enter city: ")
        state = input("Enter state: ")
        zip_code = input("Enter zip code: ")
        
        # Validate billing rate and balance
        while True:
            try:
                billing_rate = float(input("Enter billing rate: "))
                break
            except ValueError:
                print("Invalid input. Please enter a number for billing rate.")
        
        while True:
            try:
                balance = float(input("Enter starting balance: "))
                break
            except ValueError:
                print("Invalid input. Please enter a number for balance.")
                
        # Active status is boolean, use 1 or 0
        active_input = input("Is the client active? (yes/no): ").lower()
        active = 1 if active_input in ['yes', 'y'] else 0

        # Insert the new client into the database
        cursor.execute('''
            INSERT INTO clients (client_name, street_address_1, street_address_2, city, state, zip_code, billing_rate, balance, active)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (client_name, street_address_1, street_address_2, city, state, zip_code, billing_rate, balance, active))
        
        conn.commit()
        print("\nClient added successfully!")
        
        # Fetch the newly created client to display it
        last_id = cursor.lastrowid
        cursor.execute('SELECT * FROM clients WHERE client_id = ?', (last_id,))
        new_client = cursor.fetchone()
        display_client_details(new_client)

    except sqlite3.IntegrityError:
        print("\nError: A client with that name already exists. Please choose a unique name.")
    except sqlite3.Error as e:
        print(f"\nAn error occurred: {e}")
    finally:
        if conn:
            conn.close()

def edit_existing_client():
    """
    Displays a list of clients and allows the user to select and edit one.
    """
    conn = connect_db()
    if not conn:
        return

    try:
        cursor = conn.cursor()
        cursor.execute('SELECT client_id, client_name FROM clients')
        clients = cursor.fetchall()
        
        if not clients:
            print("No clients found to edit.")
            return

        print("\n--- Existing Clients ---")
        for client_id, client_name in clients:
            print(f"{client_id}: {client_name}")
        print("----------------------\n")

        while True:
            try:
                choice = input("Enter the ID of the client you want to edit (or 'back' to return to menu): ").strip().lower()
                if choice == 'back':
                    return

                client_id = int(choice)
                cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
                client_data = cursor.fetchone()

                if client_data:
                    break
                else:
                    print("Invalid client ID. Please try again.")
            except ValueError:
                print("Invalid input. Please enter a number or 'back'.")

        display_client_details(client_data)
        
        # The fields that can be edited, mapped to their database column names
        field_map = {
            '1': 'client_name',
            '2': 'street_address_1',
            '3': 'street_address_2',
            '4': 'city',
            '5': 'state',
            '6': 'zip_code',
            '7': 'billing_rate',
            '8': 'balance',
            '9': 'active'
        }
        
        print("\n--- Select a Field to Edit ---")
        print("1: Client Name")
        print("2: Street Address 1")
        print("3: Street Address 2")
        print("4: City")
        print("5: State")
        print("6: Zip Code")
        print("7: Billing Rate")
        print("8: Balance")
        print("9: Active Status")
        print("0: Cancel and Return to Main Menu")

        while True:
            edit_choice = input("Enter the number of the field you want to edit: ")
            if edit_choice == '0':
                print("Edit cancelled.")
                return

            column_name = field_map.get(edit_choice)
            if column_name:
                break
            else:
                print("Invalid choice. Please enter a valid number.")
        
        # Get the new value from the user, prepopulating with old value
        current_value_index = list(field_map.keys()).index(edit_choice) + 1
        
        # Handle special cases for data types
        if column_name in ['billing_rate', 'balance']:
            new_value_str = input(f"Enter new value for {column_name.replace('_', ' ')} (current: {client_data[current_value_index]}): ")
            if not new_value_str:
                new_value = client_data[current_value_index]
            else:
                try:
                    new_value = float(new_value_str)
                except ValueError:
                    print("Invalid input. Please enter a number.")
                    return
        elif column_name == 'active':
            new_value_input = input(f"Is the client active? (yes/no, current: {'Yes' if client_data[current_value_index] else 'No'}): ").lower()
            if not new_value_input:
                new_value = client_data[current_value_index]
            else:
                new_value = 1 if new_value_input in ['yes', 'y'] else 0
        else:
            new_value_input = input(f"Enter new value for {column_name.replace('_', ' ')} (current: {client_data[current_value_index]}): ")
            if not new_value_input:
                new_value = client_data[current_value_index]
            else:
                new_value = new_value_input

        # Update the database
        cursor.execute(f"UPDATE clients SET {column_name} = ? WHERE client_id = ?", (new_value, client_id))
        conn.commit()

        print("\nClient information updated successfully!")

        # Fetch and display the updated client details
        cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
        updated_client = cursor.fetchone()
        display_client_details(updated_client)

    except sqlite3.Error as e:
        print(f"\nAn error occurred: {e}")
    finally:
        if conn:
            conn.close()

def display_client_info():
    """
    Displays a list of clients and allows the user to select one to view its details.
    """
    conn = connect_db()
    if not conn:
        return
    
    try:
        cursor = conn.cursor()
        cursor.execute('SELECT client_id, client_name FROM clients ORDER BY client_name')
        clients = cursor.fetchall()
        
        if not clients:
            print("No clients found.")
            return

        print("\n--- Existing Clients ---")
        for client_id, client_name in clients:
            print(f"{client_id}: {client_name}")
        print("----------------------\n")

        while True:
            try:
                choice = input("Enter the ID of the client you want to view (or 'back' to return to menu): ").strip().lower()
                if choice == 'back':
                    return

                client_id = int(choice)
                cursor.execute('SELECT * FROM clients WHERE client_id = ?', (client_id,))
                client_data = cursor.fetchone()

                if client_data:
                    display_client_details(client_data)
                    break
                else:
                    print("Invalid client ID. Please try again.")
            except ValueError:
                print("Invalid input. Please enter a number or 'back'.")

    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
    finally:
        if conn:
            conn.close()

def main_menu():
    """
    Displays the main menu and handles user choices.
    """
    while True:
        print("\n--- Time Tracker Client Manager ---")
        print("1. Add a new client")
        print("2. Display client information")
        print("3. Edit an existing client")
        print("0. Exit")
        
        choice = input("Enter your choice: ").strip()

        if choice == '1':
            add_new_client()
        elif choice == '2':
            display_client_info()
        elif choice == '3':
            edit_existing_client()
        elif choice == '0':
            print("Exiting. Goodbye!")
            break
        else:
            print("Invalid choice. Please try again.")

if __name__ == "__main__":
    main_menu()