# app.py
import sqlite3
import os
from datetime import datetime
from dotenv import load_dotenv
import gradio as gr
import markdown

# Optional: load .env (not required unless you use secrets)
load_dotenv()

DB_PATH = os.environ.get("BLOG_DB", "blog.db")

def get_conn():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            tags TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
        """
    )
    conn.commit()
    conn.close()

init_db()

# ---------- DB helpers ----------
def create_post(title, body, tags=""):
    now = datetime.utcnow().isoformat()
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO posts (title, body, tags, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
        (title, body, tags, now, now)
    )
    conn.commit()
    post_id = cur.lastrowid
    conn.close()
    return post_id

def update_post(post_id, title, body, tags=""):
    now = datetime.utcnow().isoformat()
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        "UPDATE posts SET title = ?, body = ?, tags = ?, updated_at = ? WHERE id = ?",
        (title, body, tags, now, post_id)
    )
    conn.commit()
    conn.close()

def delete_post(post_id):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("DELETE FROM posts WHERE id = ?", (post_id,))
    conn.commit()
    conn.close()

def get_post(post_id):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT * FROM posts WHERE id = ?", (post_id,))
    row = cur.fetchone()
    conn.close()
    return dict(row) if row else None

def list_posts():
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("SELECT id, title, tags, created_at, updated_at FROM posts ORDER BY created_at DESC")
    rows = cur.fetchall()
    conn.close()
    return [dict(r) for r in rows]

# ---------- Gradio app logic ----------
def new_post(title, body, tags):
    if not title or not body:
        return "Title and Body are required.", gr.update(value="", interactive=True)
    post_id = create_post(title, body, tags or "")
    return f"Post created (ID: {post_id})", gr.update(value="")  # Clear inputs

def edit_post_load(post_id_str):
    if not post_id_str:
        return "Please select a post ID.", "", "", ""
    try:
        pid = int(post_id_str)
    except ValueError:
        return "Invalid post ID.", "", "", ""
    post = get_post(pid)
    if not post:
        return f"No post with ID {pid}.", "", "", ""
    return f"Loaded post {pid} for editing.", post["title"], post["body"], post["tags"] or ""

def save_edited(post_id_str, title, body, tags):
    try:
        pid = int(post_id_str)
    except Exception:
        return "Invalid post ID."
    if not get_post(pid):
        return f"No post with ID {pid}."
    update_post(pid, title, body, tags or "")
    return f"Post {pid} updated."

def remove_post(post_id_str):
    try:
        pid = int(post_id_str)
    except Exception:
        return "Invalid post ID."
    if not get_post(pid):
        return f"No post with ID {pid}."
    delete_post(pid)
    return f"Post {pid} deleted."

def render_post_preview(title, body):
    # Convert markdown to HTML (safe for simple uses; for production use a sanitizer)
    html = markdown.markdown(body or "")
    header = f"<h2>{title or ''}</h2><hr>"
    return header + html

def export_markdown(post_id_str):
    try:
        pid = int(post_id_str)
    except Exception:
        return None, "Invalid post ID."
    post = get_post(pid)
    if not post:
        return None, f"No post with ID {pid}."
    md = f"# {post['title']}\n\n{post['body']}\n\n*Tags: {post['tags'] or ''}*\n\n_Created: {post['created_at']}_\n"
    filename = f"post_{pid}.md"
    path = os.path.join("/tmp", filename) if os.path.isdir("/tmp") else filename
    with open(path, "w", encoding="utf-8") as f:
        f.write(md)
    return path, f"Exported to {path}"

# ---------- UI ----------
with gr.Blocks(title="Simple Blog Writer") as demo:
    gr.Markdown("# ✍️ Simple Blog Writer (Gradio + SQLite)\nCreate, edit, view, and export posts.")
    with gr.Tabs():
        with gr.TabItem("Create Post"):
            title_in = gr.Textbox(label="Title")
            tags_in = gr.Textbox(label="Tags (comma separated)")
            body_in = gr.Textbox(label="Body (Markdown)", lines=12)
            create_btn = gr.Button("Create Post")
            create_out = gr.Textbox(label="Status", interactive=False)
            create_btn.click(new_post, inputs=[title_in, body_in, tags_in], outputs=[create_out, body_in])

        with gr.TabItem("List Posts"):
            posts_list = gr.Dataframe(headers=["id", "title", "tags", "created_at", "updated_at"], interactive=False)
            refresh_btn = gr.Button("Refresh List")
            refresh_msg = gr.Textbox(label="Message", interactive=False)

            def refresh_table():
                rows = list_posts()
                table = [[r["id"], r["title"], r["tags"], r["created_at"], r["updated_at"]] for r in rows]
                return table, f"Found {len(table)} posts."
            refresh_btn.click(refresh_table, outputs=[posts_list, refresh_msg])

        with gr.TabItem("Edit Post"):
            edit_id = gr.Textbox(label="Post ID to load")
            load_btn = gr.Button("Load for Edit")
            load_status = gr.Textbox(label="Load status", interactive=False)
            edit_title = gr.Textbox(label="Title")
            edit_tags = gr.Textbox(label="Tags")
            edit_body = gr.Textbox(label="Body (Markdown)", lines=12)
            save_btn = gr.Button("Save Changes")
            save_status = gr.Textbox(label="Save status", interactive=False)

            load_btn.click(edit_post_load, inputs=[edit_id], outputs=[load_status, edit_title, edit_body, edit_tags])
            save_btn.click(save_edited, inputs=[edit_id, edit_title, edit_body, edit_tags], outputs=[save_status])

        with gr.TabItem("View / Export"):
            view_id = gr.Textbox(label="Post ID to view")
            view_btn = gr.Button("View")
            view_out = gr.HTML()
            preview_btn = gr.Button("Preview (HTML)")
            preview_out = gr.HTML()
            export_btn = gr.Button("Export Markdown to file")
            export_file = gr.File(label="Download exported file")
            msg_box = gr.Textbox(label="Message", interactive=False)

            def view_plain(post_id_str):
                try:
                    pid = int(post_id_str)
                except:
                    return "Invalid post ID.", ""
                post = get_post(pid)
                if not post:
                    return f"No post with ID {pid}.", ""
                md = f"# {post['title']}\n\n{post['body']}\n\n*Tags: {post['tags'] or ''}*"
                return f"Showing post {pid}:", md
            view_btn.click(view_plain, inputs=[view_id], outputs=[msg_box, view_out])

            def preview_html(post_id_str):
                try:
                    pid = int(post_id_str)
                except:
                    return "Invalid ID."
                post = get_post(pid)
                if not post:
                    return "No post found."
                html = render_post_preview(post["title"], post["body"])
                return html
            preview_btn.click(preview_html, inputs=[view_id], outputs=[preview_out])

            def export_file_click(post_id_str):
                path, msg = export_markdown(post_id_str)
                if not path:
                    return None, msg
                return path, msg
            export_btn.click(export_file_click, inputs=[view_id], outputs=[export_file, msg_box])

    gr.Markdown("**Note:** This example has no authentication. Add login before using in production.")

if __name__ == "__main__":
    demo.launch(server_name="0.0.0.0", share=False)