The size of the sidebar and the spacer bar is tied to the buttons with in the bars how can i change this. Python 3.12: import sys
import sqlite3
from pathlib import Path
import tkinter as tk
from tkinter import ttk, messagebox, font

MEDIA_DB = Path(__file__).parent / 'media.db'

if sys.platform.startswith('win'):
    try:
        import ctypes
        ctypes.windll.shcore.SetProcessDpiAwareness(1)
    except Exception as e:
        print("Error setting DPI awareness:", e)


def run_query(query: str, parameters: tuple = ()) -> list:
    """Execute a SQL query and return the results if it's a SELECT statement."""
    with sqlite3.connect(MEDIA_DB) as conn:
        cursor = conn.cursor()
        result = cursor.execute(query, parameters)
        if query.strip().lower().startswith("select"):
            return result.fetchall()
        conn.commit()


def create_tables() -> None:
    """Create the movies and series tables in the database if they do not exist."""
    run_query('''
        CREATE TABLE IF NOT EXISTS movies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            movie TEXT,
            year TEXT,
            present BOOLEAN,
            watched BOOLEAN,
            notes TEXT
        );
    ''')
    run_query('''
        CREATE TABLE IF NOT EXISTS series (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            series TEXT,
            seasons TEXT,
            year TEXT,
            present BOOLEAN,
            seasons_present TEXT,
            complete BOOLEAN,
            watched BOOLEAN,
            seasons_seen TEXT,
            notes TEXT
        );
    ''')


class MediaTracker(tk.Tk):
    """A Tkinter application to track movies and series."""

    BOOLEAN_COLUMNS = {
        'movies': ['Present', 'Watched'],
        'series': ['Present', 'Complete', 'Watched']
    }

    def __init__(self):
        super().__init__()
        self.configure(bg='white')
        self.title("Movies and Series Tracker")
        self.geometry("1200x600")
        self.custom_font = self._get_custom_font()
        self.current_table = None
        self.current_row_id = None
        self.tree = None
        self.scrollbar = None

        self._configure_style()
        self._configure_grid()
        self._create_widgets()
        self.show_movies()

    def _get_custom_font(self) -> font.Font:
        """Retrieve the custom font, defaulting to Arial if Literata is unavailable."""
        try:
            return font.Font(family='Literata', size=12)
        except:
            return font.Font(family='Arial', size=12)

    def _configure_style(self) -> None:
        """Configure the styles for Treeview and buttons."""
        font_line_space = self.custom_font.metrics('linespace')
        row_height = font_line_space + 4

        style = ttk.Style()
        style.theme_use("default")

        style.layout(
            'Custom.Treeview',
            [('Custom.Treeview.treearea', {'sticky': 'nsew'})]
        )
        style.configure(
            "Custom.Treeview",
            font=self.custom_font,
            rowheight=row_height,
            borderwidth=0,
            relief='flat',
            highlightthickness=0,
            background='white',
            fieldbackground='white'
        )
        style.configure(
            "Custom.Treeview.Heading",
            font=(self.custom_font.cget("family"), self.custom_font.cget("size"), "bold"),
            background='#F7F8FC',
            relief='solid',
            borderwidth=0,
            anchor='center'
        )
        style.map(
            "Custom.Treeview.Heading",
            background=[('active', 'white')],
            relief=[('active', 'solid')]
        )
        style.configure("Custom.TButton", font=self.custom_font)

    def _configure_grid(self) -> None:
        """Configure the grid layout for the main window."""
        self.grid_rowconfigure(1, weight=1)
        self.grid_columnconfigure(2, weight=1)

    def _create_widgets(self) -> None:
        """Create and layout all the widgets in the application."""
        self.header_canvas = tk.Canvas(self, bg='white', highlightthickness=0, height=100)
        self.header_canvas.grid(row=0, column=1, columnspan=3, sticky='ew')
        self.header_canvas.grid_propagate(False)
        self.update_header("Movies")
    
        sidebar = tk.Frame(self, width=150, bg='#F7F8FC')
        sidebar.grid(row=0, column=0, rowspan=2, sticky='ns')
        sidebar.grid_propagate(False)
        self.sidebar = sidebar
    
        spacer = tk.Frame(self, width=100, bg='white')
        spacer.grid(row=1, column=1, sticky='ns')
        spacer.grid_propagate(False)
        self.spacer = spacer
    
        content = tk.Frame(self, bg='white')
        content.grid(row=1, column=2, sticky='nsew')
        self.content = content
    
        self._create_sidebar_labels()
        self._create_buttons()

    def update_header(self, text: str) -> None:
        """Update the header with the given text."""
        self.header_canvas.delete("all")
        
        # Define a small font for the entire header text
        small_font = font.Font(family=self.custom_font.actual('family'), size=10)
    
        # Create the "Data >" part with the smaller font size
        self.header_canvas.create_text(
            10, 10,
            anchor='nw',
            text="Data >",
            fill='light grey',
            font=small_font
        )
    
        # Create the main text part (e.g., Movies or Series) with the same smaller font size
        self.header_canvas.create_text(
            80, 10,  # Adjust x-coordinate for spacing
            anchor='nw',
            text=text,
            fill='black',
            font=small_font
        )

    def _create_sidebar_labels(self) -> None:
        """Create labels for the sidebar to switch between Movies and Series."""
        self.create_sidebar_label("Movies", self.show_movies, pady=100)  # Added pady
        self.create_sidebar_label("Series", self.show_series, pady=0)  # Added pady
    
    def create_sidebar_label(self, text: str, command, pady=0) -> None:
        """Create a clickable label in the sidebar."""
        label = tk.Label(
            self.sidebar,
            text=text,
            bg='#F7F8FC',
            font=self.custom_font,
            cursor="hand2",
            anchor='w',
            padx=50,
            pady=0
        )
        label.pack(pady=(pady, 1), fill='x')  # Apply pady for initial space
        label.bind("<Enter>", lambda e: label.config(bg='#EAEDEF'))
        label.bind("<Leave>", lambda e: label.config(bg='#F7F8FC'))
        label.bind("<Button-1>", lambda e: command())

    def _create_buttons(self) -> None:
        """Create add and delete buttons in the spacer frame."""
        style = ttk.Style()
        style.configure("Square.TButton",
                        font=('Arial', 14),
                        padding=(5, 5),
                        width=3,
                        background='white',
                        borderwidth=0,
                        relief='flat')
        
        style.map("Square.TButton",
                  background=[('active', '#EAEDEF')])
        
        button_frame = tk.Frame(self.spacer, bg='white')
        button_frame.pack(expand=False, anchor='center', pady=(20, 0))
    
        add_button = ttk.Button(
            button_frame,
            text="+",
            command=self.add_entry,
            style="Square.TButton"
        )
        add_button.pack(pady=5, anchor='center')
    
        delete_button = ttk.Button(
            button_frame,
            text="-",
            command=self.delete_entry,
            style="Square.TButton"
        )
        delete_button.pack(pady=5, anchor='center')

    def clear_content(self) -> None:
        """Clear the content frame by destroying the tree and scrollbar if they exist."""
        for widget in [self.tree, self.scrollbar]:
            if widget:
                widget.destroy()
        self.tree = None
        self.scrollbar = None

    def setup_treeview(self, columns: list, headings: list) -> None:
        """Set up the Treeview widget with specified columns and headings."""
        self.tree = ttk.Treeview(
            self.content,
            columns=columns,
            show='headings',
            selectmode='browse',
            style="Custom.Treeview"
        )
        for col, head in zip(columns, headings):
            self.tree.heading(col, text=head, anchor='center')
            anchor = 'w' if head.lower() == 'notes' else 'center'
            width = 300 if head.lower() == 'notes' else 100 if head in self.BOOLEAN_COLUMNS.get(self.current_table, []) else 150
            self.tree.column(col, width=width, anchor=anchor)

        self.scrollbar = ttk.Scrollbar(
            self.content,
            orient="vertical",
            command=self.tree.yview
        )
        self.tree.configure(yscrollcommand=self.scrollbar.set)
        self.scrollbar.pack(side='right', fill='y')
        self.tree.pack(fill='both', expand=True)

        self.tree.bind('<<TreeviewSelect>>', self.on_row_select)
        self.tree.bind('<Button-1>', self.on_single_click)
        self.tree.bind("<Motion>", self.treeview_click_cursor)

    def load_movie_data(self) -> None:
        """Load and display movie data in the Treeview."""
        self.clear_content()
        columns = ["Movie", "Year", "Present", "Watched", "Notes"]
        self.setup_treeview(columns, columns)
        self.current_table = 'movies'
        self.update_header("Movies")
        for row in run_query('SELECT * FROM movies'):
            record_id, movie, year, present, watched, notes = row
            display_row = [
                movie,
                year,
                self.bool_to_checkbox(present),
                self.bool_to_checkbox(watched),
                notes
            ]
            self.tree.insert('', 'end', iid=str(record_id), values=display_row)

    def load_series_data(self) -> None:
        """Load and display series data in the Treeview."""
        self.clear_content()
        columns = ["Series", "Seasons", "Year", "Present",
                   "Seasons Present", "Complete", "Watched",
                   "Seasons Seen", "Notes"]
        self.setup_treeview(columns, columns)
        self.current_table = 'series'
        self.update_header("Series")
        for row in run_query('SELECT * FROM series'):
            (record_id, series, seasons, year, present, seasons_present,
             complete, watched, seasons_seen, notes) = row
            display_row = [
                series,
                seasons,
                year,
                self.bool_to_checkbox(present),
                seasons_present,
                self.bool_to_checkbox(complete),
                self.bool_to_checkbox(watched),
                seasons_seen,
                notes
            ]
            self.tree.insert('', 'end', iid=str(record_id), values=display_row)

    def show_movies(self) -> None:
        """Display the movies table."""
        self.load_movie_data()

    def show_series(self) -> None:
        """Display the series table."""
        self.load_series_data()

    def add_entry(self) -> None:
        """Add a new entry to the current table."""
        if self.current_table == 'movies':
            run_query(
                "INSERT INTO movies (movie, year, present, watched, notes) VALUES (?, ?, ?, ?, ?)",
                ('', '', False, False, '')
            )
            self.load_movie_data()
        elif self.current_table == 'series':
            run_query(
                """INSERT INTO series
                (series, seasons, year, present, seasons_present, complete, watched, seasons_seen, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                ('', '', '', False, '', False, False, '', '')
            )
            self.load_series_data()

    def delete_entry(self) -> None:
        """Delete the selected entry from the current table."""
        selected_item = self.tree.selection()
        if not selected_item:
            messagebox.showwarning("No selection", "Please select a row to delete.")
            return

        row_id = selected_item[0]
        run_query(f"DELETE FROM {self.current_table} WHERE id = ?", (row_id,))
        if self.current_table == 'movies':
            self.load_movie_data()
        elif self.current_table == 'series':
            self.load_series_data()

    def on_row_select(self, event) -> None:
        """Handle the selection of a row in the Treeview."""
        selected_item = self.tree.selection()
        self.current_row_id = selected_item[0] if selected_item else None

    def on_single_click(self, event) -> None:
        """Handle single click events for toggling boolean fields."""
        region = self.tree.identify("region", event.x, event.y)
        if region != "cell":
            return

        row_id = self.tree.identify_row(event.y)
        column = self.tree.identify_column(event.x)
        if not row_id or not column:
            return

        try:
            col_index = int(column.replace('#', '')) - 1
            column_name = self.tree['columns'][col_index]
        except (IndexError, ValueError):
            return

        if column_name in self.BOOLEAN_COLUMNS.get(self.current_table, []):
            current_value = self.tree.set(row_id, column_name)
            new_value = not self.checkbox_to_bool(current_value)
            self.tree.set(row_id, column_name, self.bool_to_checkbox(new_value))
            row_values = self.tree.item(row_id)['values']
            self.update_database(row_values)

    def update_database(self, row_values: list) -> None:
        """Update the database with the modified row values."""
        if self.current_table == 'movies':
            query = """
                UPDATE movies 
                SET movie = ?, year = ?, present = ?, watched = ?, notes = ? 
                WHERE id = ?
            """
            parameters = (
                row_values[0],
                row_values[1],
                self.checkbox_to_bool(row_values[2]),
                self.checkbox_to_bool(row_values[3]),
                row_values[4],
                self.current_row_id
            )
        elif self.current_table == 'series':
            query = """
                UPDATE series 
                SET series = ?, seasons = ?, year = ?, present = ?, seasons_present = ?, 
                    complete = ?, watched = ?, seasons_seen = ?, notes = ? 
                WHERE id = ?
            """
            parameters = (
                row_values[0],
                row_values[1],
                row_values[2],
                self.checkbox_to_bool(row_values[3]),
                row_values[4],
                self.checkbox_to_bool(row_values[5]),
                self.checkbox_to_bool(row_values[6]),
                row_values[7],
                row_values[8],
                self.current_row_id
            )
        run_query(query, parameters)

    def treeview_click_cursor(self, event) -> None:
        """Change the cursor to a hand when hovering over boolean columns."""
        region = self.tree.identify("region", event.x, event.y)
        if region == "cell":
            column = self.tree.identify_column(event.x)
            try:
                col_index = int(column.replace('#', '')) - 1
                column_name = self.tree['columns'][col_index]
                cursor = "hand2" if column_name in self.BOOLEAN_COLUMNS.get(self.current_table, []) else ""
                self.tree.configure(cursor=cursor)
            except (IndexError, ValueError):
                self.tree.configure(cursor="")
        else:
            self.tree.configure(cursor="")

    @staticmethod
    def bool_to_checkbox(value: bool) -> str:
        """Convert a boolean value to a checkbox symbol."""
        return '☑' if value else '☐'

    @staticmethod
    def checkbox_to_bool(value: str) -> bool:
        """Convert a checkbox symbol to a boolean value."""
        return value == '☑'


if __name__ == "__main__":
    create_tables()
    app = MediaTracker()
    app.mainloop()