import pyodbc
import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
#import ttkbootstrap
import threading

continue_search = True

datatype_mappings = {
    "Textual": ['char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext', 'uniqueidentifierstr'],
    "Numeric": ['tinyint', 'smallint', 'int', 'bigint', 'float', 'real', 'decimal', 'numeric'],
    "Date/Time": ['datetime', 'timestamp'],
    "Monetary": ['money', 'smallmoney']
}

def get_available_dsns():
    return list(pyodbc.dataSources().keys())
def connect_and_search(dsn_entry, uid_entry, password_entry, search_entry):
    global continue_search, condition
    DSN = dsn_entry.get()
    UID = uid_entry.get()
    pwd = password_entry.get()
    search_string = search_entry.get()

    # Check if any of the required fields are empty
    if not (DSN and UID and pwd and search_string):
        messagebox.showwarning("Warning",
                               "Please fill in all fields (DSN, UID, Password, and Search String) before searching.")
        return

    search_type = search_type_var.get()
    conn_str = f'DSN={DSN};UID={UID};pwd={pwd}'

    results_listbox.delete(0, tk.END)  # Clear the listbox before adding new results

    try:
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()

        # Get domain ids based on selected datatype
        datatype_category = datatype_var.get()
        datatypes_for_category = datatype_mappings.get(datatype_category, [])

        if not datatypes_for_category:
            messagebox.showwarning("Warning", f"No datatypes found for category: {datatype_category}")
            return

        cursor.execute("SELECT DISTINCT domain_id FROM SYS.SYSDOMAIN WHERE domain_name IN ({})".format(
            ', '.join(['?'] * len(datatypes_for_category))), datatypes_for_category)
        domain_ids = cursor.fetchall()

        # Insert domain ids at the top of the results listbox
        ids_str = ", ".join([str(id[0]) for id in domain_ids])
        results_listbox.insert(tk.END, f"Searching domain_id: {ids_str}")
        results_listbox.insert(tk.END, "")  # Blank line

        cursor.execute(
            "SELECT user_name(creator), table_name FROM SYS.SYSTABLE WHERE table_type = 'BASE' and creator>100")
        tables = cursor.fetchall()

        for owner, table_name in tables:
            if not continue_search:
                return  # Exit the function if continue_search is False
            cursor.execute(
                "SELECT c.column_name, c.domain_id FROM SYS.SYSCOLUMN c JOIN SYS.SYSTABLE t ON c.table_id = t.table_id WHERE t.table_name = ? AND t.creator = user_id(?)",
                table_name, owner)
            columns = cursor.fetchall()

            for column in columns:
                column_name, domain_id = column

                selected_datatype = datatype_var.get()
                if not is_datatype(cursor, domain_id, selected_datatype):
                    continue

                if search_type == "Exact":
                    condition = f"\"{column_name}\" = ?"
                elif search_type == "Like":
                    condition = f"\"{column_name}\" LIKE ?"
                elif search_type == "Regex":
                    condition = f"\"{column_name}\" REGEXP ?"
                search_param = search_string if search_type == "Exact" else f"%{search_string}%"

                query = f"SELECT '{owner}.{table_name}.{column_name}' AS Location, \"{column_name}\" AS Value FROM \"{owner}\".\"{table_name}\" WHERE {condition}"

                try:
                    cursor.execute(query, search_param)
                except pyodbc.DataError:
                    # This can capture data errors such as trying to search a string in a numeric column.
                    continue

                results = cursor.fetchall()

                for result in results:
                    results_listbox.insert(tk.END, str(result))

        conn.close()
    except Exception as e:
        messagebox.showerror("Error", str(e))


class TextSpinner:
    def __init__(self, master):
        self.master = master
        self.spinner_states = ['-', '\\', '|', '/']
        self.state = 0
        self.label = tk.Label(self.master, text=self.spinner_states[self.state], font=('Courier', 24))
        self.label.pack(pady=20)
        self.update_spinner()

    def update_spinner(self):
        # Update the displayed spinner state
        self.state = (self.state + 1) % len(self.spinner_states)
        self.label.config(text=self.spinner_states[self.state])
        # Schedule the next update
        self.master.after(200, self.update_spinner)


def is_datatype(cursor, domain_id, datatype_category):
    datatypes_for_category = datatype_mappings.get(datatype_category, [])
    cursor.execute("SELECT domain_name FROM SYS.SYSDOMAIN WHERE domain_id = ?", (domain_id,))
    domain_name = cursor.fetchone()[0]
    return domain_name.lower() in [datatype.lower() for datatype in datatypes_for_category]


def connect_and_search_thread():
    global continue_search
    continue_search = True  # Reset the flag each time you start a new search

    # Show the loading popup
    loading_popup = tk.Toplevel(root)
    loading_popup.title("Loading")

    spinner = TextSpinner(loading_popup)

    loading_label = tk.Label(loading_popup, text="Searching... Please wait.")
    loading_label.pack(padx=50, pady=20)

    # Define stop_search function
    def stop_search():
        global continue_search
        continue_search = False
        results_listbox.insert(tk.END, "Search aborted...")

    loading_popup.protocol("WM_DELETE_WINDOW", stop_search)

    loading_popup.update()

    # Run the actual search function
    connect_and_search(dsn_entry, uid_entry, password_entry, search_string_entry)

    # Close the loading popup once done
    loading_popup.destroy()


def contains_only_digits_and_symbols(search_string):
    allowed_symbols = "-.:"  # List of allowed symbols
    for char in search_string:
        if not (char.isdigit() or char in allowed_symbols):
            return False
    return True


def is_valid_input_for_datatype(search_string, datatype):
    if datatype == "Textual":
        return isinstance(search_string, str)
    elif datatype == "Numeric":
        return search_string.isdigit()
    elif datatype == "Date/Time":
        return contains_only_digits_and_symbols(search_string)
    return True


def start_search():
    search_string = search_string_entry.get()
    selected_datatype = datatype_var.get()
    if not is_valid_input_for_datatype(search_string, selected_datatype):
        messagebox.showerror("Error",
                             f"Entered value '{search_string}' is not valid for datatype '{selected_datatype}'")
        return

    # Start the threaded search
    search_thread = threading.Thread(target=connect_and_search_thread)
    search_thread.start()


def display_company_info():
    DSN = dsn_entry.get()
    UID = uid_entry.get()
    pwd = password_entry.get()
    conn_str = f'DSN={DSN};UID={UID};pwd={pwd}'

    results_listbox.delete(0, tk.END)  # Clear the listbox before adding new results

    try:
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM company")
        results = cursor.fetchall()

        for result in results:
            results_listbox.insert(tk.END, str(result))

        cursor.close()
        conn.close()

    except Exception as e:
        messagebox.showerror("Error", str(e))


def display_domain_ids():
    DSN = dsn_entry.get()
    UID = uid_entry.get()
    pwd = password_entry.get()
    conn_str = f'DSN={DSN};UID={UID};pwd={pwd}'

    results_listbox.delete(0, tk.END)  # Clear the listbox before adding new results

    try:
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM SYSDOMAIN")
        results = cursor.fetchall()

        for result in results:
            results_listbox.insert(tk.END, str(result))

        cursor.close()
        conn.close()

    except Exception as e:
        messagebox.showerror("Error", str(e))


def copy_to_clipboard():
    try:
        selected_line = results_listbox.get(results_listbox.curselection())
        root.clipboard_clear()
        root.clipboard_append(selected_line)
        root.update()  # To ensure clipboard contents persist
    except:
        messagebox.showinfo("Info", "Please select a line to copy.")


def reset_results():
    results_listbox.delete(0, tk.END)


root = tk.Tk()
#style = ttkbootstrap.Style(theme="darkly")
root.geometry("800x600")
root.title('Search Interface')

# Variables for entry widgets
dsn_var = tk.StringVar()
available_dsns = get_available_dsns()
uid_var = tk.StringVar()
password_var = tk.StringVar()
search_string_var = tk.StringVar()

# for debugging
# dsn_var.set("DBName")
# uid_var.set("DBUser")
# search_string_var.set("SearchString")



def update_button_states(*args):
    # Check if DSN, UID, and password have values entered
    entries_filled = bool(dsn_var.get()) and bool(uid_var.get()) and bool(password_var.get())

    # Enable/Disable "Company Info" and "Domain Id's" buttons based on the entries
    company_info_button['state'] = 'normal' if entries_filled else 'disabled'
    domain_ids_button['state'] = 'normal' if entries_filled else 'disabled'

    # For the "Search" button, additionally check if a search string is entered
    search_button['state'] = 'normal' if entries_filled and bool(search_string_var.get()) else 'disabled'


# Labels and Entry widgets for DSN, UID, and password
ttk.Label(root, text="DSN:").grid(row=0, column=0, padx=20, pady=10, sticky='e')
dsn_entry = ttk.Combobox(root, textvariable=dsn_var, values=available_dsns, state="readonly")
dsn_entry.grid(row=0, column=1, padx=20, pady=10, sticky='ew')

ttk.Label(root, text="UID:").grid(row=1, column=0, padx=20, pady=10, sticky='e')
uid_entry = ttk.Entry(root, textvariable=uid_var)
uid_entry.grid(row=1, column=1, padx=20, pady=10, sticky='ew')

ttk.Label(root, text="Password:").grid(row=2, column=0, padx=20, pady=10, sticky='e')
password_entry = ttk.Entry(root, show="*", textvariable=password_var)
password_entry.grid(row=2, column=1, padx=20, pady=10, sticky='ew')

ttk.Label(root, text="Search String:").grid(row=3, column=0, padx=20, pady=10, sticky='e')
search_string_entry = ttk.Entry(root, textvariable=search_string_var)
search_string_entry.grid(row=3, column=1, padx=20, pady=10, sticky='ew')

# Create a frame for the buttons
button_frame = ttk.Frame(root)
button_frame.grid(row=4, column=0, columnspan=3, pady=20)  # Spanning 3 columns to cover the width

# Create buttons directly within the button_frame
search_button = ttk.Button(button_frame, text="Search", command=start_search)
company_info_button = ttk.Button(button_frame, text="Company Info")
domain_ids_button = ttk.Button(button_frame, text="Domain Id's")

# Place the buttons inside the button_frame
search_button.grid(row=0, column=0, padx=10, sticky='e')
company_info_button.grid(row=0, column=1, padx=10, sticky='w')
domain_ids_button.grid(row=0, column=2, padx=10, sticky='w')

# Use a ttk frame to give the listbox a more refined boundary
listbox_frame = ttk.Frame(root, padding="10")
listbox_frame.grid(row=7, column=0, columnspan=3, padx=20, pady=10, sticky='nsew')

# Stylish Listbox (you can adjust the bg, fg, and font as you like)
results_listbox = tk.Listbox(listbox_frame, bg="white", fg="black", font=("Arial", 12))
scrollbar = ttk.Scrollbar(listbox_frame, orient="vertical", command=results_listbox.yview)
results_listbox.config(yscrollcommand=scrollbar.set)

results_listbox.grid(row=0, column=0, sticky='nsew')
scrollbar.grid(row=0, column=1, sticky='ns')

# Adjusting weights for dynamic resizing
listbox_frame.grid_rowconfigure(0, weight=1)
listbox_frame.grid_columnconfigure(0, weight=1)

# Adding search type and data type selectors
search_type_var = tk.StringVar()
search_type_var.set("Exact")  # Default value
search_type_options = ["Exact", "Like", "Regex"]
search_type_label = ttk.Label(root, text="Search Type:")
search_type_label.grid(row=5, column=0, padx=20, pady=10, sticky='e')
search_type_dropdown = ttk.Combobox(root, textvariable=search_type_var, values=search_type_options, state="readonly")
search_type_dropdown.grid(row=5, column=1, padx=20, pady=10, sticky='ew')

datatype_var = tk.StringVar()
datatype_var.set("Textual")  # Default value
datatype_options = ["Textual", "Numeric", "Date/Time", "Monetary"]
datatype_label = ttk.Label(root, text="Data Type:")
datatype_label.grid(row=6, column=0, padx=20, pady=10, sticky='e')
datatype_dropdown = ttk.Combobox(root, textvariable=datatype_var, values=datatype_options, state="readonly")
datatype_dropdown.grid(row=6, column=1, padx=20, pady=10, sticky='ew')

# Weight configuration for rows and columns that should expand
root.grid_rowconfigure(7, weight=1)  # This means row 7 (Listbox) will expand vertically
root.grid_columnconfigure(0, weight=1)  # This means column 0 will expand horizontally
root.grid_columnconfigure(1, weight=1)  # This means column 1 will expand horizontally

# Create a frame for the bottom buttons
bottom_button_frame = ttk.Frame(root)
bottom_button_frame.grid(row=9, column=0, columnspan=3)  # Assuming 3 columns span the main window

# Buttons below the listbox
copy_button = ttk.Button(bottom_button_frame, text="Copy to Clipboard", command=copy_to_clipboard,
                         padding=(10, 5, 10, 5))
reset_button = ttk.Button(bottom_button_frame, text="Reset Results", command=reset_results, padding=(10, 5, 10, 5))

# Place the buttons inside the bottom_button_frame
copy_button.grid(row=0, column=0, padx=10, pady=10, sticky='e')
reset_button.grid(row=0, column=1, padx=10, pady=10, sticky='w')

# Initially disable the buttons
company_info_button['state'] = 'disabled'
domain_ids_button['state'] = 'disabled'
search_button['state'] = 'disabled'

# Bind entry changes to the button state update function
dsn_var.trace_add('write', update_button_states)
uid_var.trace_add('write', update_button_states)
password_var.trace_add('write', update_button_states)
search_string_var.trace_add('write', update_button_states)

company_info_button.config(command=display_company_info)
domain_ids_button.config(command=display_domain_ids)

root.mainloop()