import os
from typing import List, Dict

import gradio as gr
from dotenv import load_dotenv

from cron import start_scheduler, is_scheduler_running
from helper import (
    get_csv_path,
    get_current_time_epoch,
    read_csv_as_dicts,
    list_csv_files,
    save_csv_file,
    check_duplicate_files,
    delete_csv_file,
    execute_sql_query,
)
from tools import (
    append_row,
    build_datetime_epoch,
    list_tables,
    list_tasks,
    run_sql_query,
    update_row,
)


def load_csv_data() -> tuple[List[Dict], List[Dict], List[Dict]]:
    """Load data for the dashboard tables."""
    conversations = read_csv_as_dicts(get_csv_path("conversations"))
    tasks = read_csv_as_dicts(get_csv_path("tasks"))
    memories = read_csv_as_dicts(get_csv_path("memories"))
    return conversations, tasks, memories


def get_all_files():
    """Get all CSV files for the file downloader."""
    return list_csv_files()


def handle_file_upload(files):
    """Handle file upload. Blocks if duplicates are found."""
    if not files:
        return get_all_files(), ""

    duplicates = check_duplicate_files(files)

    if duplicates:
        error_msg = "❌ Files already exist:\n" + "\n".join(
            [f"  • {dup}" for dup in duplicates]
        )
        error_msg += "\n\nDelete them first before uploading."
        return get_all_files(), error_msg

    saved_paths = []
    for file_path in files:
        saved_paths.append(save_csv_file(file_path))

    return get_all_files(), f"✓ Uploaded {len(saved_paths)} file(s)"


def handle_deletion(filename):
    """Handle file deletion."""
    if not filename:
        return get_all_files(), "", "Select a file to delete"

    success, message = delete_csv_file(filename)
    return get_all_files(), "", message


with gr.Blocks(title="Rowmind MCP Server") as demo:
    gr.Markdown("# Rowmind MCP Server & Dashboard")

    with gr.Tabs():
        with gr.Tab("About"):
            gr.HTML("""
            <style>
            .hero-wrapper {
                display: flex;
                justify-content: center;
                align-items: center;
                margin: 20px 0;
                padding: 0 20px;
            }
            .hero-wrapper img {
                max-height: 500px !important;
                max-width: 900px !important;
                width: 100% !important;
                height: auto !important;
                border-radius: 12px;
            }
            </style>
            <div class="hero-wrapper">
              <img src="https://res.cloudinary.com/dlsujsulr/image/upload/v1764116155/hero-img_xhljbx.png" alt="Rowmind Hero">
            </div>
            """)
            gr.Markdown("""
> ## ⚠️ CLONE REQUIRED FOR TESTING
>
> **This is a single-user, private-instance project.** The public demo is limited because exposing CSV files would let anyone delete or corrupt data. 
>
> **To test properly:** Clone the repo → Run locally or as a private Space → Connect your MCP client.
>
> This design ensures **your data stays yours**.

---

## What is this?

Rowmind is a small "memory layer" for your AI chat client. You talk to Claude, ChatGPT, Cursor... and they can save stuff for you. Tasks, expenses, random notes, whatever you want.

The twist? **Everything is stored in plain CSV files.** No fancy database. No cloud sync. Just files you can open with Excel or any text editor.

### Why CSV?

- **You own your data** — it's right there, on your disk
- **Easy to backup** — just copy the folder
- **Easy to debug** — open the file and see what's inside
- **Works offline** — no internet needed for storage
- **Hackable** — add columns, edit rows, do whatever

---

### How it works

""")
            # Placeholder for architecture diagram
            gr.Markdown("""
```
┌─────────────────┐          ┌─────────────────┐          ┌─────────────────┐
│   Your Chat     │   MCP    │    Rowmind      │  R/W     │   CSV Files     │
│   Client        │ <------> │    Server       │ <------> │   (your data)   │
│ (Claude, etc)   │ Protocol │   (this app)    │          │                 │
└─────────────────┘          └─────────────────┘          └─────────────────┘
```
*[TODO: Add nicer diagram image here]*
""")

            gr.Markdown("""
The AI uses **MCP protocol** to talk to this server. When you say "remind me to call mom tomorrow", the AI:

1. Calls `list_tables()` to see what tables exist
2. Picks the right one (probably `tasks`)
3. Calls `append_row()` to save it

---

### The dynamic part (this is the cool thing)

You can upload **any CSV** and the AI will automatically know how to use it. 

How? There's a special `tables.csv` that stores metadata about each table:

```
┌─────────────────────────────────────────────────────────────────────────┐
│                           tables.csv                                     │
├─────────────┬─────────────────┬─────────────────────────────────────────┤
│  table_name │   description   │            example_sentence             │
├─────────────┼─────────────────┼─────────────────────────────────────────┤
│  expenses   │ Daily spending  │ "I spent 15 dollars on lunch"           │
│  movies     │ Films to watch  │ "Add Inception to my movie list"        │
│  mood       │ How I feel      │ "Today I'm feeling tired"               │
│  recipes    │ Cooking ideas   │ "Save this pasta recipe"                │
│  ...        │ ...             │ ...                                     │
└─────────────┴─────────────────┴─────────────────────────────────────────┘
                                    ↓
                    AI reads this and understands:
                    "oh, when user talks about money → expenses.csv"
                    "when user mentions films → movies.csv"
```

So when you say "I spent 20 bucks on coffee", the AI:

1. Checks `tables.csv` to see what tables exist
2. Matches your message to the right table (expenses, because of the money context)
3. Appends a row to `expenses.csv`

> ## 🎯 **You can track _anything_.** 
> 
> Workouts, books, habits, dreams, whatever. Just upload a CSV with your columns and add a description. **The AI figures out the rest.**

---

### Features

| What | How |
|------|-----|
| **Tasks & Reminders** | Schedule one-time or recurring reminders. Get push notifications via ntfy |
| **Custom Tables** | Create any table you want — expenses, mood tracker, whatever |
| **SQL Queries** | Query your CSVs with DuckDB. Yes, real SQL on CSV files |
| **Table Metadata** | Add descriptions so the AI knows what each table is for |

---

### The notification thing

""")
            # Placeholder for notification flow diagram
            gr.Markdown("""
```
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  tasks.csv  │ --> │ Background  │ --> │    ntfy     │ --> 📱 Phone
│  (pending)  │     │   Worker    │     │   (push)    │
└─────────────┘     └─────────────┘     └─────────────┘
```
""")

            gr.Markdown("""
A background job checks every minute for due tasks and sends push notifications through [ntfy](https://ntfy.sh). 
No Firebase, no Apple stuff, just HTTP requests.

---

### Demo Videos

See Rowmind in action:
""")

            gr.HTML("""
            <style>
            .video-grid {
                display: grid;
                grid-template-columns: repeat(auto-fit, minmax(400px, 1fr));
                gap: 24px;
                margin: 20px 0;
            }
            .video-card {
                background: var(--background-fill-secondary);
                border-radius: 12px;
                overflow: hidden;
                border: 1px solid var(--border-color-primary);
            }
            .video-card video {
                width: 100%;
                display: block;
            }
            .video-card .video-info {
                padding: 16px;
            }
            .video-card h4 {
                margin: 0 0 8px 0;
                font-size: 1.1em;
            }
            .video-card p {
                margin: 0;
                color: var(--body-text-color-subdued);
                font-size: 0.9em;
            }
            </style>
            <div class="video-grid">
                <div class="video-card">
                    <video controls playsinline>
                        <source src="https://res.cloudinary.com/dlsujsulr/video/upload/v1764204051/ROWMIND_wbaotw.mov" type="video/mp4">
                    </video>
                    <div class="video-info">
                        <h4>📊 Dynamic CSV Tables</h4>
                        <p>Upload any CSV and the AI automatically learns how to use it. Query with SQL, append rows, update fields — all through natural conversation.</p>
                    </div>
                </div>
                <div class="video-card">
                    <video controls playsinline>
                        <source src="https://res.cloudinary.com/dlsujsulr/video/upload/v1764204070/rowmind_task_tjosia.mov" type="video/mp4">
                    </video>
                    <div class="video-info">
                        <h4>⏰ Tasks & Push Notifications</h4>
                        <p>Schedule one-time or recurring reminders. A background worker monitors due tasks and sends push notifications via ntfy — no Firebase needed.</p>
                    </div>
                </div>
            </div>
            """)

            gr.Markdown("""
---

### Privacy note

This demo on Hugging Face is **public** — anyone can see the data. 

For real use, duplicate this Space and make it private. Or run locally. The whole point is that YOUR data stays with YOU.

---

### Twitter

📢 [Original announcement on Twitter](https://x.com/turbopila/status/1991677115910222157)

---

*Built for the Gradio MCP Hackathon 🚀*
""")

        with gr.Tab("Dashboard"):
            refresh_btn = gr.Button("Refresh Data")

            @gr.render(triggers=[refresh_btn.click, demo.load])
            def render_dashboard():
                files = list_csv_files()
                if not files:
                    gr.Markdown("No CSV files found in /data")
                    return

                # Sort files for consistent order
                files.sort()

                # Create a grid layout (2 columns)
                for i in range(0, len(files), 2):
                    with gr.Row():
                        for j in range(2):
                            if i + j < len(files):
                                file_path = files[i + j]
                                filename = os.path.basename(file_path)
                                try:
                                    # Read CSV with DuckDB for better handling
                                    # Use table name (filename without extension) since execute_sql_query now loads tables
                                    table_name = os.path.splitext(filename)[0]
                                    data = execute_sql_query(
                                        f"SELECT * FROM {table_name}"
                                    )

                                    # Convert list of dicts to list of lists for Gradio
                                    if data:
                                        headers = list(data[0].keys())
                                        values = [
                                            [row.get(col) for col in headers]
                                            for row in data
                                        ]
                                    else:
                                        headers = []
                                        values = []

                                    with gr.Column():
                                        gr.Markdown(f"### {filename}")
                                        gr.Dataframe(
                                            value=values,
                                            headers=headers,
                                            interactive=False,
                                            wrap=True,
                                            label=filename,
                                        )
                                except Exception as e:
                                    with gr.Column():
                                        gr.Markdown(f"### {filename} (Error)")
                                        gr.Markdown(f"Error reading file: {e}")

        with gr.Tab("Data Management"):
            gr.Markdown("### Manage CSV Files")
            gr.Markdown(
                "Upload new CSV files or download existing ones. All files are saved in the `/data` directory."
            )
            gr.Markdown(
                "⚠️ **Note:** You cannot upload a file that already exists. Delete the existing file first."
            )

            # Status message box
            status_message = gr.Textbox(
                label="Status", interactive=False, lines=3, visible=True
            )

            with gr.Row():
                with gr.Column():
                    file_upload = gr.File(
                        label="Upload CSV", file_count="multiple", file_types=[".csv"]
                    )
                    upload_btn = gr.Button("Upload & Refresh")

                with gr.Column():
                    file_download = gr.File(
                        label="Download CSVs",
                        file_count="multiple",
                        interactive=False,
                    )
                    with gr.Row():
                        refresh_files_btn = gr.Button("Refresh File List")

            # File deletion section
            gr.Markdown("---")
            gr.Markdown("### Delete Files")
            gr.Markdown("Protected: tables.csv, tasks.csv")

            with gr.Row():
                delete_dropdown = gr.Dropdown(
                    label="Select file to delete", choices=[], interactive=True
                )
                delete_btn = gr.Button("Delete", variant="stop")

            def get_deletable_files():
                all_files = list_csv_files()
                filenames = [os.path.basename(f) for f in all_files]
                protected = ["tables.csv", "tasks.csv"]
                deletable = [f for f in filenames if f not in protected]
                return gr.Dropdown(choices=deletable)

            demo.load(get_all_files, outputs=[file_download], show_api=False)
            demo.load(get_deletable_files, outputs=[delete_dropdown], show_api=False)

            upload_btn.click(
                handle_file_upload,
                inputs=[file_upload],
                outputs=[file_download, status_message],
                show_api=False,
            ).then(get_deletable_files, outputs=[delete_dropdown], show_api=False)

            refresh_files_btn.click(
                get_all_files,
                outputs=[file_download],
                show_api=False,
            ).then(get_deletable_files, outputs=[delete_dropdown], show_api=False)

            file_upload.upload(
                handle_file_upload,
                inputs=[file_upload],
                outputs=[file_download, status_message],
                show_api=False,
            ).then(get_deletable_files, outputs=[delete_dropdown], show_api=False)

            delete_btn.click(
                handle_deletion,
                inputs=[delete_dropdown],
                outputs=[file_download, delete_dropdown, status_message],
                show_api=False,
            ).then(get_deletable_files, outputs=[delete_dropdown], show_api=False)

            # Metadata section
            gr.Markdown("---")
            gr.Markdown("### Table Metadata")
            gr.Markdown(
                "Add descriptions and examples to help the LLM understand your tables."
            )

            with gr.Row():
                with gr.Column():
                    meta_table_dropdown = gr.Dropdown(
                        label="Select Table", choices=[], interactive=True
                    )
                    meta_description = gr.Textbox(
                        label="Description",
                        placeholder="e.g., 'Personal expense tracking'",
                        lines=2,
                    )
                    meta_example = gr.Textbox(
                        label="Example Sentence",
                        placeholder="e.g., 'Today I spent 20 dollars on groceries'",
                        lines=2,
                    )
                    meta_columns_display = gr.Textbox(
                        label="Columns",
                        interactive=False,
                        placeholder="Columns will appear here",
                    )
                    save_metadata_btn = gr.Button("Save Metadata", variant="primary")
                    metadata_status = gr.Textbox(label="Status", interactive=False)

            def refresh_table_list():
                from helper import list_csv_files

                files = list_csv_files()
                tables = [os.path.basename(f).replace(".csv", "") for f in files]
                tables = [t for t in tables if t != "tables"]
                return gr.Dropdown(choices=tables)

            def load_table_metadata(table_name):
                if not table_name:
                    return "", "", "", ""

                from helper import get_table_metadata, get_csv_columns

                columns = get_csv_columns(table_name)
                columns_str = ", ".join(columns) if columns else "No columns found"

                metadata = get_table_metadata(table_name)
                if metadata:
                    return (
                        metadata.get("description", ""),
                        metadata.get("example_sentence", ""),
                        columns_str,
                        "",
                    )
                else:
                    return "", "", columns_str, ""

            def save_metadata(table_name, description, example):
                if not table_name:
                    return "Please select a table"

                from helper import save_table_metadata

                success = save_table_metadata(table_name, description, example)

                if success:
                    return f"✓ Metadata saved for '{table_name}'"
                else:
                    return "✗ Error saving metadata"

            demo.load(refresh_table_list, outputs=[meta_table_dropdown], show_api=False)

            upload_btn.click(lambda: None, outputs=None, show_api=False).then(
                refresh_table_list, outputs=[meta_table_dropdown], show_api=False
            )

            meta_table_dropdown.change(
                load_table_metadata,
                inputs=[meta_table_dropdown],
                outputs=[
                    meta_description,
                    meta_example,
                    meta_columns_display,
                    metadata_status,
                ],
                show_api=False,
            )

            save_metadata_btn.click(
                save_metadata,
                inputs=[meta_table_dropdown, meta_description, meta_example],
                outputs=[metadata_status],
                show_api=False,
            )

        with gr.Tab("Task Tools"):
            gr.Markdown("Task-specific tools exposed to MCP.")
            with gr.Tab("List Tasks"):
                status_dd = gr.Dropdown(
                    choices=["pending", "done", ""],
                    value="",
                    label="Status",
                    info="Leave empty to return all statuses.",
                )
                limit_slider = gr.Slider(1, 1000, value=10, step=1, label="Limit")
                tasks_out = gr.JSON(label="Tasks")
                gr.Button("List Tasks", variant="primary").click(
                    fn=list_tasks,
                    inputs=[status_dd, limit_slider],
                    outputs=tasks_out,
                    api_name="list_tasks",
                )

        with gr.Tab("Tools"):
            gr.Markdown(
                "Each tool below is a Gradio interface. Launching this app with `mcp_server=True` "
                "automatically exposes every interface as an MCP tool."
            )

            with gr.Tabs():
                with gr.Tab("Current Time"):
                    time_btn = gr.Button("Get Current Time", variant="primary")
                    time_out = gr.Number(label="Current time (epoch, seconds)")
                    time_btn.click(
                        fn=get_current_time_epoch,
                        inputs=[],
                        outputs=time_out,
                        api_name="current_time",
                    )

                with gr.Tab("Build Epoch"):
                    year = gr.Number(label="Year", value=2025)
                    month = gr.Number(label="Month", value=1)
                    day = gr.Number(label="Day", value=1)
                    hour = gr.Number(label="Hour (0-23)", value=0)
                    minute = gr.Number(label="Minute (0-59)", value=0)
                    epoch_out = gr.Number(label="Epoch UTC (seconds)")
                    gr.Button("Build Epoch", variant="primary").click(
                        fn=build_datetime_epoch,
                        inputs=[year, month, day, hour, minute],
                        outputs=epoch_out,
                        api_name="build_datetime_epoch",
                    )

                with gr.Tab("List Tables"):
                    list_tables_btn = gr.Button("List Tables", variant="primary")
                    tables_out = gr.JSON(label="Schemas")
                    list_tables_btn.click(
                        fn=list_tables,
                        inputs=[],
                        outputs=tables_out,
                        api_name="list_tables",
                    )

                with gr.Tab("Append Row"):
                    table_name_in = gr.Textbox(label="Table Name", placeholder="tasks")
                    row_json_in = gr.Textbox(
                        label="Row Data (JSON object)",
                        placeholder='{"description": "Buy milk", "status": "pending"}',
                        lines=6,
                    )
                    append_out = gr.JSON(label="Result")
                    gr.Button("Append Row", variant="primary").click(
                        fn=append_row,
                        inputs=[table_name_in, row_json_in],
                        outputs=append_out,
                        api_name="append_row",
                    )

                with gr.Tab("Run SQL Query"):
                    sql_query = gr.Textbox(
                        label="SQL Query",
                        placeholder="SELECT * FROM expenses WHERE amount > 100",
                        lines=3,
                        info="READ-ONLY. You can query any CSV file in /data using its name (e.g. 'expenses'). Do not use for INSERT/UPDATE.",
                    )
                    qt_out = gr.JSON(label="Result")
                    gr.Button("Run Query", variant="primary").click(
                        fn=run_sql_query,
                        inputs=[sql_query],
                        outputs=qt_out,
                        api_name="run_sql_query",
                    )

                with gr.Tab("Update Row"):
                    table_name_up = gr.Textbox(label="Table Name", placeholder="tasks")
                    row_id_up = gr.Number(
                        label="Row ID",
                        value=0,
                        precision=0,
                        info="The 0-based row index (rowid).",
                    )
                    field_up = gr.Textbox(label="Field to Update", placeholder="status")
                    new_val_up = gr.Textbox(label="New Value", placeholder="done")
                    update_out = gr.Textbox(label="Result")
                    gr.Button("Update Row", variant="primary").click(
                        fn=update_row,
                        inputs=[
                            table_name_up,
                            row_id_up,
                            field_up,
                            new_val_up,
                        ],
                        outputs=update_out,
                        api_name="update_row",
                    )

# Load environment variables
load_dotenv()

if __name__ == "__main__":
    # Start the background cron scheduler for task notifications
    if not is_scheduler_running():
        start_scheduler()

    demo.launch(mcp_server=True)