#!/usr/bin/env python3

# ---
# Copyright (c) 2026 Carl L. Wuebker and Claude.ai

# Permission is hereby granted, free of charge, to any person obtaining a
# copy of this software and associated documentation files (the "Software"),
# to deal in the Software without restriction, including without limitation
# the rights to use, copy, modify, merge, publish, distribute, sublicense,
# and/or sell copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.

# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
# IN THE SOFTWARE.
# ---

# Note that Claude.ai Sonnet 4.5 and/or 4.6, guided by my testing & feedback,
# wrote the code.  I've minimally tested the code, so there still may be bugs.
# 16Mar26: Posted to https://wuebker.com

# spSheet7: A very-minimal python3/tkinter spreadsheet
# It supports entering text, numbers & the =SUM() function (+, -, *, etc. doesn't work)
# It's primarily a simple example of spSheet6b's user interface which is:
#   - At startup, the window initially shrinks to fit content
#   - The user can resize the window; the user's size becomes the new maximum window size
#   - When content is smaller than the max size, the lower right of the window shrinks to fit
#   - When content is larger than the max size, scrollbars appear
#   - Window shrink-to-fit fires on ButtonRelease (drag complete) if the WM delivers it,
#     otherwise falls back to a short debounce timer (150ms after the last Configure event).
#
#   Window-management fix vs spSheet7 (same fix as spSheet6b):
#   - Replaced integer _prog_resize_count with boolean _prog_resize_pending so that
#     multiple widget creations/deletions (append/delete row or col) cannot accumulate
#     the counter above 1 and cause subsequent user-drag Configure events to be silently
#     swallowed, preventing maxW/maxH updates and the snap-back from ever firing.
#   - Replaced the fixed 500ms timer with a ButtonRelease-1 + debounce hybrid.
#
# Architecture: each cell has TWO separate stores:
#   raw[(r,c)]  -- what the user typed (formula or value), never shown in cell
#   cell entry  -- always shows the computed result (or raw text if not formula)
# A formula bar above the grid shows raw[(r,c)] for the selected cell.
# Supports =SUM(A1:C3) over any rectangular range.

import tkinter as tk
import re
import string

MIN_WIDTH   = 100
MIN_HEIGHT  = 100
DEBOUNCE_MS = 150      # fallback: fires this long after the last Configure event
INIT_ROWS   = 10
INIT_COLS   = 5
COL_WIDTH   = 10       # characters

maxW = 9999
maxH = 9999
_prog_resize_pending = False   # True while waiting for WM to echo fit_window()
_pending_fit         = False   # True while a user drag needs a fit
_debounce_id         = None    # after() handle for the fallback debounce timer
_last_user_size      = (0, 0)

# --- cell stores ---
raw          = {}   # raw[(r,c)] = string typed by user ("=SUM(...)" or "42")
cell_widgets = {}   # cell_widgets[(r,c)] = tk.Entry showing computed result
result_vars  = {}   # result_vars[(r,c)] = StringVar bound to that Entry

# --- formula bar ---
formula_var     = None   # StringVar for the formula bar Entry
selected_cell   = (1, 1) # currently focused (row, col)


# ------------------------------------------------------------------
# Column helpers
# ------------------------------------------------------------------
def col_to_letter(c):
    s = ''
    c += 1
    while c:
        c, r = divmod(c - 1, 26)
        s = string.ascii_uppercase[r] + s
    return s

def letter_to_col(s):
    s = s.upper().strip()
    if not s or not all(ch in string.ascii_uppercase for ch in s):
        return None
    n = 0
    for ch in s:
        n = n * 26 + (ord(ch) - ord('A') + 1)
    return n - 1

def parse_ref(ref):
    """'B3' -> (row, col) where row/col match internal indices (row 0 = header).
    Display row 1 == internal row 1."""
    m = re.fullmatch(r'([A-Za-z]+)(\d+)', ref.strip())
    if not m:
        return None
    col = letter_to_col(m.group(1))
    if col is None:
        return None
    col += 1   # grid col 0 = row headers, A=col1, B=col2, ...
    row = int(m.group(2))          # no -1: display row N == internal row N
    if col < 1 or row < 1:
        return None
    return (row, col)


# ------------------------------------------------------------------
# Evaluation
# ------------------------------------------------------------------
def get_numeric(row, col):
    if (row, col) not in raw:
        return None
    try:
        return float(raw[(row, col)])
    except ValueError:
        return None

def evaluate(row, col):
    """Return the string to display in cell (row, col)."""
    content = raw.get((row, col), '')
    if not content.startswith('='):
        return content
    formula = content[1:].strip()
    m = re.fullmatch(r'SUM\(\s*([A-Za-z]+\d+)\s*:\s*([A-Za-z]+\d+)\s*\)',
                     formula, re.IGNORECASE)
    if m:
        r1 = parse_ref(m.group(1))
        r2 = parse_ref(m.group(2))
        if r1 is None or r2 is None:
            return '#REF'
        row1 = min(r1[0], r2[0]);  row2 = max(r1[0], r2[0])
        col1 = min(r1[1], r2[1]);  col2 = max(r1[1], r2[1])
        total = 0.0
        for r in range(row1, row2 + 1):
            for c in range(col1, col2 + 1):
                v = get_numeric(r, c)
                if v is not None:
                    total += v
        return str(int(total)) if total == int(total) else str(total)
    return '#ERR'

def recalc_all():
    """Recompute every cell's display value."""
    for (r, c), var in result_vars.items():
        var.set(evaluate(r, c))


# ------------------------------------------------------------------
# Formula bar
# ------------------------------------------------------------------
def on_formula_bar_return(event):
    """User pressed Enter in the formula bar -- commit to selected cell."""
    r, c = selected_cell
    raw[(r, c)] = formula_var.get()
    recalc_all()
    # Return focus to the cell
    w = cell_widgets.get((r, c))
    if w:
        w.focus_set()
    return 'break'


# ------------------------------------------------------------------
# Cell focus / navigation
# ------------------------------------------------------------------
def select_cell(row, col):
    global selected_cell
    selected_cell = (row, col)
    formula_var.set(raw.get((row, col), ''))

def move_focus(row, col):
    nrows = max(r for r, c in raw) + 1
    ncols = max(c for r, c in raw) + 1
    row = max(1, min(row, nrows - 1))
    col = max(1, min(col, ncols - 1))
    w = cell_widgets.get((row, col))
    if w:
        w.focus_set()

def commit_cell(row, col, value):
    """Store value into raw[], recalc, update formula bar if still selected."""
    raw[(row, col)] = value
    recalc_all()
    if selected_cell == (row, col):
        formula_var.set(value)


# ------------------------------------------------------------------
# Cell creation
# ------------------------------------------------------------------
def make_cell(row, col):
    rvar = tk.StringVar()
    result_vars[(row, col)] = rvar
    raw[(row, col)] = ''

    entry = tk.Entry(frame, textvariable=rvar, width=COL_WIDTH,
                     justify='right', relief='sunken', bd=1,
                     state='readonly', readonlybackground='white')
    entry.grid(row=row, column=col, sticky=tk.EW, padx=1, pady=1)
    cell_widgets[(row, col)] = entry

    # Each cell has a hidden editor Entry that pops up over it when active
    editor_var = tk.StringVar()
    editor = tk.Entry(frame, textvariable=editor_var, width=COL_WIDTH,
                      justify='right', relief='sunken', bd=1)

    def start_edit(e, r=row, c=col):
        select_cell(r, c)
        # Show editor over the display entry
        editor_var.set(raw.get((r, c), ''))
        editor.grid(row=r, column=c, sticky=tk.EW, padx=1, pady=1)
        editor.lift()
        editor.focus_set()
        editor.selection_range(0, tk.END)

    def finish_edit(nav=None, r=row, c=col):
        val = editor_var.get()
        editor.grid_remove()
        commit_cell(r, c, val)
        if nav:
            move_focus(*nav)
        else:
            # Return focus to the display entry
            w = cell_widgets.get((r, c))
            if w:
                w.focus_set()

    def on_editor_key(e, r=row, c=col):
        if e.keysym == 'Return':
            finish_edit((r + 1, c))
            return 'break'
        if e.keysym == 'Tab':
            finish_edit((r, c + 1))
            return 'break'
        if e.keysym == 'ISO_Left_Tab':
            finish_edit((r, c - 1))
            return 'break'
        if e.keysym == 'Up':
            finish_edit((r - 1, c))
            return 'break'
        if e.keysym == 'Down':
            finish_edit((r + 1, c))
            return 'break'
        if e.keysym == 'Escape':
            editor_var.set(raw.get((r, c), ''))
            editor.grid_remove()
            w = cell_widgets.get((r, c))
            if w:
                w.focus_set()
            return 'break'

    def on_editor_focus_out(e, r=row, c=col):
        if editor.winfo_ismapped():
            finish_edit(None, r, c)

    editor.bind('<KeyPress>', on_editor_key)
    editor.bind('<FocusOut>', on_editor_focus_out)

    def on_display_focus_in(e, r=row, c=col):
        select_cell(r, c)

    def on_display_key(e, r=row, c=col):
        # Navigation keys move without editing
        if e.keysym in ('Tab', 'Return'):
            move_focus(r + 1, c)
            return 'break'
        if e.keysym == 'ISO_Left_Tab':
            move_focus(r, c - 1)
            return 'break'
        if e.keysym == 'Up':
            move_focus(r - 1, c)
            return 'break'
        if e.keysym == 'Down':
            move_focus(r + 1, c)
            return 'break'
        # Any printable character starts editing
        if e.char and e.char.isprintable():
            raw[(r, c)] = ''   # clear on fresh type
            editor_var.set(e.char)
            editor.grid(row=r, column=c, sticky=tk.EW, padx=1, pady=1)
            editor.lift()
            editor.focus_set()
            editor.icursor(tk.END)
            return 'break'

    entry.bind('<FocusIn>',  on_display_focus_in)
    entry.bind('<KeyPress>',  on_display_key)
    entry.bind('<Double-Button-1>', start_edit)
    entry.bind('<Button-1>', lambda e, r=row, c=col: (
        entry.focus_set(), select_cell(r, c)
    ))

    return entry


# ------------------------------------------------------------------
# Header helpers
# ------------------------------------------------------------------
def make_col_header(col):
    tk.Label(frame, text=col_to_letter(col - 1),
             bg='#b0b8c8', relief='groove', width=COL_WIDTH,
             anchor='center').grid(row=0, column=col,
                                   sticky=tk.EW, padx=1, pady=1)

def make_row_header(row):
    tk.Label(frame, text=str(row),
             bg='#b0b8c8', relief='groove', width=3,
             anchor='center').grid(row=row, column=0,
                                   sticky=tk.EW, padx=1, pady=1)


# ------------------------------------------------------------------
# Append / delete rows and columns
# ------------------------------------------------------------------
def current_dims():
    rows = {r for r, c in raw}
    cols = {c for r, c in raw}
    return max(rows) + 1, max(cols) + 1

def append_row():
    nrows, ncols = current_dims()
    make_row_header(nrows)
    for col in range(1, ncols):
        make_cell(nrows, col)

def delete_row():
    nrows, ncols = current_dims()
    if nrows <= 2:
        return
    row = nrows - 1
    for widget in frame.grid_slaves(row=row):
        widget.destroy()
    for col in range(ncols):
        raw.pop((row, col), None)
        result_vars.pop((row, col), None)
        cell_widgets.pop((row, col), None)

def append_col():
    nrows, ncols = current_dims()
    make_col_header(ncols)
    for row in range(1, nrows):
        make_cell(row, ncols)

def delete_col():
    nrows, ncols = current_dims()
    if ncols <= 2:
        return
    col = ncols - 1
    for widget in frame.grid_slaves(column=col):
        widget.destroy()
    for row in range(nrows):
        raw.pop((row, col), None)
        result_vars.pop((row, col), None)
        cell_widgets.pop((row, col), None)


# ------------------------------------------------------------------
# Window management (same logic as spSheet6b)
# ------------------------------------------------------------------
def update_scrollbars(win_w, win_h):
    cw = frame.winfo_reqwidth()
    ch = frame.winfo_reqheight()
    sb = vscroll.winfo_reqwidth()
    need_v = ch > win_h
    need_h = cw > win_w
    if need_v and not need_h:
        need_h = cw > (win_w - sb)
    if need_h and not need_v:
        need_v = ch > (win_h - sb)
    if need_v:
        vscroll.grid(row=1, column=1, sticky=tk.NS)
    else:
        vscroll.grid_remove()
    if need_h:
        hscroll.grid(row=2, column=0, sticky=tk.EW)
    else:
        hscroll.grid_remove()
    return need_v, need_h, sb

def on_root_configure(event):
    global maxW, maxH, _prog_resize_pending, _pending_fit
    global _debounce_id, _last_user_size
    if event.widget is not root:
        return
    if _prog_resize_pending:
        _prog_resize_pending = False
        return
    w, h = root.winfo_width(), root.winfo_height()
    if w <= 1 or h <= 1:
        return
    if (w, h) == _last_user_size:
        return
    _last_user_size = (w, h)
    maxW, maxH = w, h
    update_scrollbars(w, h)
    _pending_fit = True
    if _debounce_id is not None:
        root.after_cancel(_debounce_id)
    _debounce_id = root.after(DEBOUNCE_MS, _debounce_fit)

def _debounce_fit():
    """Fallback: called DEBOUNCE_MS after the last Configure event."""
    global _debounce_id, _pending_fit
    _debounce_id = None
    if _pending_fit:
        _pending_fit = False
        fit_window()

def on_button_release(event):
    """Preferred 'drag done' signal on X11; cancels debounce so only one fit fires."""
    global _pending_fit, _debounce_id
    if _pending_fit:
        _pending_fit = False
        if _debounce_id is not None:
            root.after_cancel(_debounce_id)
            _debounce_id = None
        fit_window()

def fit_window():
    global _prog_resize_pending
    root.update_idletasks()
    cw = frame.winfo_reqwidth()
    ch = frame.winfo_reqheight()
    need_v, need_h, sb = update_scrollbars(maxW, maxH)
    if need_v and need_h:
        win_w, win_h = maxW, maxH
    elif need_v:
        win_w = min(cw + sb, maxW)
        win_h = maxH
    elif need_h:
        win_w = maxW
        win_h = min(ch + sb, maxH)
    else:
        win_w = max(MIN_WIDTH,  cw)
        win_h = max(MIN_HEIGHT, ch)
    # Account for formula bar height
    canvas_w = win_w - (sb if need_v else 0)
    canvas_h = win_h - fbar_frame.winfo_reqheight() - (sb if need_h else 0)
    canvas.config(width=canvas_w, height=max(20, canvas_h))
    _prog_resize_pending = True
    root.geometry(f'{win_w}x{win_h}')

def on_frame_configure(event):
    canvas.config(scrollregion=canvas.bbox('all'))
    fit_window()


# ------------------------------------------------------------------
# Help
# ------------------------------------------------------------------
def show_help():
    win = tk.Toplevel(root)
    win.title('Formula Help')
    win.resizable(False, False)
    msg = (
        "Cell addresses:  column letter + row number\n"
        "  e.g.  A1  B3  C10\n\n"
        "Entering values:\n"
        "  Click a cell and type -- or double-click to edit in place.\n"
        "  Press Enter (move down) or Tab (move right) to confirm.\n"
        "  Press Escape to cancel an edit.\n\n"
        "Formulas  (must start with '='):\n"
        "  =SUM(A1:A5)   sum column A, rows 1-5\n"
        "  =SUM(A1:E1)   sum row 1, columns A-E\n"
        "  =SUM(A1:C3)   sum all numeric cells in rectangle\n\n"
        "Formula bar:\n"
        "  Shows the raw formula/value for the selected cell.\n"
        "  You can also edit directly in the bar and press Enter.\n"
    )
    tk.Label(win, text=msg, justify='left', font='TkFixedFont',
             padx=12, pady=10).pack()
    tk.Button(win, text='OK', command=win.destroy, width=8).pack(pady=(0,10))


# ------------------------------------------------------------------
# Main
# ------------------------------------------------------------------
if __name__ == '__main__':
    root = tk.Tk()
    root.title('tkinter Spreadsheet 7a')
    root.configure(bg='#d0d0d0')

    # --- Formula bar (row 0 of root grid) ---
    fbar_frame = tk.Frame(root, bg='#d0d0d0', pady=2)
    fbar_frame.grid(row=0, column=0, columnspan=2, sticky=tk.EW)
    tk.Label(fbar_frame, text='Formula:', bg='#d0d0d0').pack(side=tk.LEFT, padx=(4,2))
    formula_var = tk.StringVar()
    fbar_entry = tk.Entry(fbar_frame, textvariable=formula_var, relief='sunken')
    fbar_entry.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=(0,4))
    fbar_entry.bind('<Return>', on_formula_bar_return)

    # --- Canvas + scrollbars (rows 1-2 of root grid) ---
    canvas = tk.Canvas(root, bg='#e8e8e8', highlightthickness=0)
    canvas.grid(row=1, column=0)

    vscroll = tk.Scrollbar(root, orient=tk.VERTICAL,   command=canvas.yview)
    hscroll = tk.Scrollbar(root, orient=tk.HORIZONTAL, command=canvas.xview)
    canvas.config(yscrollcommand=vscroll.set, xscrollcommand=hscroll.set)

    frame = tk.Frame(canvas, bg='#e8e8e8')
    canvas.create_window((0, 0), window=frame, anchor='nw')

    # --- Menus ---
    menu_bar = tk.Menu(root)
    row_menu = tk.Menu(menu_bar, tearoff=0)
    row_menu.add_command(label='Append', command=append_row)
    row_menu.add_command(label='Delete', command=delete_row)
    row_menu.add_separator()
    row_menu.add_command(label='Exit', command=root.quit)
    menu_bar.add_cascade(label='Row', menu=row_menu)
    col_menu = tk.Menu(menu_bar, tearoff=0)
    col_menu.add_command(label='Append', command=append_col)
    col_menu.add_command(label='Delete', command=delete_col)
    menu_bar.add_cascade(label='Column', menu=col_menu)
    help_menu = tk.Menu(menu_bar, tearoff=0)
    help_menu.add_command(label='About formulas', command=show_help)
    menu_bar.add_cascade(label='Help', menu=help_menu)
    root.config(menu=menu_bar)

    # --- Build initial grid ---
    # Corner
    tk.Label(frame, text='', bg='#b0b8c8', relief='groove',
             width=3).grid(row=0, column=0, sticky=tk.EW, padx=1, pady=1)
    for col in range(1, INIT_COLS + 1):
        make_col_header(col)
    for row in range(1, INIT_ROWS + 1):
        make_row_header(row)
        for col in range(1, INIT_COLS + 1):
            make_cell(row, col)

    # Select top-left cell
    root.after(60, lambda: cell_widgets[(1,1)].focus_set())

    frame.bind('<Configure>', on_frame_configure)
    root.bind('<Configure>', on_root_configure)
    root.bind('<ButtonRelease-1>', on_button_release)
    root.after(50, fit_window)
    root.mainloop()