app.py

"""
Simple Text Archive
Minimal text management with searchable table and SQLite persistence.
"""

import gradio as gr
import sqlite3
import pandas as pd
import os

DB_PATH = "simple_archive.db"


def init_db():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category TEXT NOT NULL,
            section TEXT NOT NULL,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_category ON items(category)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_section ON items(section)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_title ON items(title)")
    conn.commit()
    conn.close()


def add_item(category, section, title, content):
    if not all([category, section, title, content]):
        return "❌ All fields required!", load_table("", "", "")

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO items (category, section, title, content) VALUES (?, ?, ?, ?)",
        (category.strip(), section.strip(), title.strip(), content.strip()),
    )
    conn.commit()
    conn.close()

    return "✅ Added!", load_table("", "", "")


def load_table(category_filter, section_filter, title_filter):
    conn = sqlite3.connect(DB_PATH)

    query = """
        SELECT id, category, section, title, content,
               datetime(updated_at, 'localtime') as updated
        FROM items
        WHERE 1=1
    """
    params = []

    if category_filter:
        query += " AND category LIKE ?"
        params.append(f"%{category_filter}%")

    if section_filter:
        query += " AND section LIKE ?"
        params.append(f"%{section_filter}%")

    if title_filter:
        query += " AND title LIKE ?"
        params.append(f"%{title_filter}%")

    query += " ORDER BY updated_at DESC"

    df = pd.read_sql_query(query, conn, params=params)
    conn.close()

    if not df.empty:
        df["content"] = df["content"].apply(
            lambda x: (x[:100] + "...") if isinstance(x, str) and len(x) > 100 else x
        )

    return df


def update_item(item_id, category, section, title, content):
    if item_id is None or item_id == "":
        return "❌ Select an item first!", load_table("", "", "")

    if not all([category, section, title, content]):
        return "❌ All fields required!", load_table("", "", "")

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        """
        UPDATE items
        SET category=?,
            section=?,
            title=?,
            content=?,
            updated_at=CURRENT_TIMESTAMP
        WHERE id=?
        """,
        (category.strip(), section.strip(), title.strip(), content.strip(), int(item_id)),
    )
    conn.commit()
    conn.close()

    return "✅ Updated!", load_table("", "", "")


def delete_item(item_id):
    if item_id is None or item_id == "":
        return "❌ Select an item first!", load_table("", "", "")

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("DELETE FROM items WHERE id=?", (int(item_id),))
    conn.commit()
    conn.close()

    return "✅ Deleted!", load_table("", "", "")


def load_item_by_id(item_id):
    if item_id is None or item_id == "":
        return "", "", "", ""

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        "SELECT category, section, title, content FROM items WHERE id=?",
        (int(item_id),),
    )
    result = cursor.fetchone()
    conn.close()

    if result:
        return result[0], result[1], result[2], result[3]
    return "", "", "", ""


def get_char_count(text):
    return f"📊 {len(text)} chars" if text else "📊 0 chars"


def get_stats():
    if not os.path.exists(DB_PATH):
        return "💾 Database: not created yet"

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM items")
    total = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(DISTINCT category) FROM items")
    categories = cursor.fetchone()[0]
    conn.close()

    file_size_kb = os.path.getsize(DB_PATH) / 1024
    return f"💾 Database: {total} items, {categories} categories ({file_size_kb:.1f} KB)"


def handle_table_select(df: pd.DataFrame, evt: gr.SelectData):
    if df is None or len(df) == 0:
        return None
    row_idx = evt.index[0]
    try:
        return int(df.iloc[row_idx, 0])
    except Exception:
        return None


# Initialize
init_db()

# UI
with gr.Blocks(title="Text Archive") as app:
    gr.Markdown("# 📝 Simple Text Archive")

    with gr.Row():
        db_stats = gr.Markdown(get_stats())
        download_btn = gr.DownloadButton("💾 Download DB", value=DB_PATH, size="sm", scale=0)

    gr.Markdown("---")

    # Add Form
    with gr.Row():
        with gr.Column(scale=3):
            gr.Markdown("### ➕ Add New Item")
            with gr.Row():
                add_category = gr.Textbox(label="Category", placeholder="e.g., Python, Notes, SQL", scale=1)
                add_section = gr.Textbox(label="Section", placeholder="e.g., Functions, Snippets", scale=1)
                add_title = gr.Textbox(label="Title", placeholder="Short description", scale=2)

            add_content = gr.Textbox(label="Content", placeholder="Your text here...", lines=4)
            add_char_count = gr.Markdown("📊 0 chars")

            with gr.Row():
                add_btn = gr.Button("➕ Add Item", variant="primary", size="sm")
                add_status = gr.Textbox(label="", container=False, show_label=False, interactive=False)

    gr.Markdown("---")

    # Search/Filter
    gr.Markdown("### 🔍 Search & Filter")
    with gr.Row():
        search_category = gr.Textbox(label="🏷️ Category", placeholder="Filter...", scale=1)
        search_section = gr.Textbox(label="📁 Section", placeholder="Filter...", scale=1)
        search_title = gr.Textbox(label="📌 Title", placeholder="Filter...", scale=1)
        clear_btn = gr.Button("🔄 Clear", size="sm", scale=1)

    # Table
    gr.Markdown("### 📋 All Items. Click a row to load it into the edit form.")
    table = gr.Dataframe(
        value=load_table("", "", ""),
        label="",
        interactive=False,
        column_widths=["5%", "12%", "12%", "20%", "40%", "11%"],
    )

    gr.Markdown("---")

    # Edit Form
    gr.Markdown("### ✏️ Edit Selected Item")

    with gr.Row():
        edit_id = gr.Number(label="Item ID", precision=0, scale=1)
        load_id_btn = gr.Button("🔄 Load by ID", size="sm", scale=0)

    with gr.Row():
        edit_category = gr.Textbox(label="Category", scale=1)
        edit_section = gr.Textbox(label="Section", scale=1)
        edit_title = gr.Textbox(label="Title", scale=2)

    edit_content = gr.Textbox(label="Content", lines=6, max_lines=20)
    edit_char_count = gr.Markdown("📊 0 chars")

    with gr.Row():
        update_btn = gr.Button("💾 Save Changes", variant="primary", size="sm")
        delete_btn = gr.Button("🗑️ Delete", variant="stop", size="sm")
        edit_status = gr.Textbox(label="", container=False, show_label=False, interactive=False)

    # Events
    add_content.change(fn=get_char_count, inputs=add_content, outputs=add_char_count)
    edit_content.change(fn=get_char_count, inputs=edit_content, outputs=edit_char_count)

    add_btn.click(
        fn=add_item,
        inputs=[add_category, add_section, add_title, add_content],
        outputs=[add_status, table],
    ).then(
        fn=lambda: ("", "", "", "", "📊 0 chars"),
        outputs=[add_category, add_section, add_title, add_content, add_char_count],
    ).then(
        fn=get_stats,
        outputs=db_stats,
    )

    for search_box in [search_category, search_section, search_title]:
        search_box.change(
            fn=load_table,
            inputs=[search_category, search_section, search_title],
            outputs=table,
        )

    clear_btn.click(
        fn=lambda: ("", "", "", load_table("", "", "")),
        outputs=[search_category, search_section, search_title, table],
    )

    load_id_btn.click(
        fn=load_item_by_id,
        inputs=edit_id,
        outputs=[edit_category, edit_section, edit_title, edit_content],
    ).then(
        fn=get_char_count,
        inputs=edit_content,
        outputs=edit_char_count,
    )

    table.select(
        fn=handle_table_select,
        inputs=table,
        outputs=edit_id,
    ).then(
        fn=load_item_by_id,
        inputs=edit_id,
        outputs=[edit_category, edit_section, edit_title, edit_content],
    ).then(
        fn=get_char_count,
        inputs=edit_content,
        outputs=edit_char_count,
    )

    update_btn.click(
        fn=update_item,
        inputs=[edit_id, edit_category, edit_section, edit_title, edit_content],
        outputs=[edit_status, table],
    ).then(
        fn=get_stats,
        outputs=db_stats,
    )

    delete_btn.click(
        fn=delete_item,
        inputs=[edit_id],
        outputs=[edit_status, table],
    ).then(
        fn=lambda: (None, "", "", "", "", "📊 0 chars"),
        outputs=[edit_id, edit_category, edit_section, edit_title, edit_content, edit_char_count],
    ).then(
        fn=get_stats,
        outputs=db_stats,
    )

    app.load(fn=get_stats, outputs=db_stats)

if __name__ == "__main__":
    app.launch()