"VLOOKUP": ("Vertical lookup, e.g., =VLOOKUP(100,A1:B10,2,FALSE).", "Leftmost column must be lookup column."),
"HLOOKUP": ("Horizontal lookup, e.g., =HLOOKUP(100,A1:Z10,2,FALSE).", "Looks across rows."),
"INDEX": ("Returns a cell's value, e.g., =INDEX(A1:B10,2,1).", "Use with MATCH."),
"MATCH": ("Finds position, e.g., =MATCH('x',A1:A10,0).", "Use 0 for exact."),

"CONCATENATE": ("Joins text, e.g., =CONCATENATE(A1,' ',B1).", "Use TEXTJOIN in new Excel."),
"TEXTJOIN": ("Joins with delimiter, e.g., =TEXTJOIN(',',TRUE,A1:A5).", "Ignores blanks."),
"LEFT": ("Left characters, e.g., =LEFT(A1,3).", "Extract prefixes."),
"RIGHT": ("Right characters, e.g., =RIGHT(A1,4).", "Extract suffixes."),
"LEN": ("Character count, e.g., =LEN(A1).", "Counts spaces."),
"TRIM": ("Remove extra spaces, e.g., =TRIM(A1).", "Good for cleaning data."),

"NOW": ("Current date & time, e.g., =NOW().", "Auto-updates."),
"TODAY": ("Today's date, e.g., =TODAY().", "Great for age formulas."),
"DATEDIF": ("Date difference, e.g., =DATEDIF(A1,B1,'Y').", "Y/M/D units."),

"IF": ("Returns based on condition, e.g., =IF(A1>10,'Yes','No').", "Use IFS for multiple."),
"IFS": ("Multiple conditions, e.g., =IFS(A1>10,'High',A1>5,'Med',TRUE,'Low').", "First match wins."),
"COUNTIF": ("Count matches, e.g., =COUNTIF(A1:A10,'Yes').", "Quick counts."),
"SUMIF": ("Sum if True, e.g., =SUMIF(A1:A10,'Yes',B1:B10).", "Match ranges properly."),

"UNIQUE": ("Unique values, e.g., =UNIQUE(A1:A10).", "Dynamic array."),
"FILTER": ("Filter range, e.g., =FILTER(A1:B10,A1:A10>5).", "Spills results."),
"PMT": ("Loan payment, e.g., =PMT(rate,nper,pv).", "Rate is per period."),

if not query:
    return "Please enter or select a formula.", "", ""

# Category filter
if category_choice and category_choice != "All":
    allowed = set(categories_map[category_choice])
else:
    allowed = set(ALL_FORMULAS)

# Exact match
if query in excel_formulas and query in allowed:
    desc, tip = excel_formulas[query]
    return f"**{query}** — {desc}", f"Tip: {tip}", f"{query}: {desc}"

# Suggestions
suggestions = [f for f in allowed if query in f][:10]
if suggestions:
    return (
        f"Formula **{query}** not found.",
        "Try: " + ", ".join(suggestions),
        ""
    )

return f"Formula **{query}** not found.", "", ""

# Header
with gr.Row():
    gr.Image(LOGO_FILE, width=90, height=90, show_label=False, elem_id="logo")
    gr.Markdown(f"## {APP_TITLE}\n{APP_DESC}")

# Main UI
with gr.Row():
    # Left side controls
    with gr.Column():
        gr.HTML("<div class='card'>")

        category = gr.Radio(
            ["All"] + list(categories_map.keys()),
            value="All",
            label="Category"
        )

        dropdown = gr.Dropdown(
            choices=ALL_FORMULAS,
            label="Select (autocomplete enabled)",
            allow_custom_value=True,
            interactive=True,
            searchable=True
        )

        text_input = gr.Textbox(
            label="Or type a formula",
            placeholder="SUM, VLOOKUP, IF …"
        )

        search_btn = gr.Button("Search")

        gr.HTML("</div>")

    # Right side results
    with gr.Column():
        gr.HTML("<div class='card'>")
        output_main = gr.Markdown("")
        output_tip = gr.Markdown("")
        output_copy = gr.Textbox(label="Copy result", interactive=False)
        gr.HTML("</div>")

# Event wiring
search_btn.click(
    get_formula_result,
    inputs=[text_input, dropdown, category],
    outputs=[output_main, output_tip, output_copy]
)

text_input.submit(
    get_formula_result,
    inputs=[text_input, dropdown, category],
    outputs=[output_main, output_tip, output_copy]
)

dropdown.change(
    get_formula_result,
    inputs=[text_input, dropdown, category],
    outputs=[output_main, output_tip, output_copy]
)