local M = {}

M.dbName = "_data/blog.db"

function M.init()
    local mydb, err = db.open(M.dbName)
    if not mydb then
        return nil, err
    end

    local ok, err = mydb:exec([[
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug TEXT UNIQUE NOT NULL,
            title TEXT NOT NULL,
            author TEXT NOT NULL,
            content TEXT NOT NULL,
            created_at TEXT NOT NULL
        )
    ]])
    if not ok then
        mydb:close()
        return nil, err
    end

    local count, err = mydb:queryRow("SELECT COUNT(*) as total FROM posts")
    if not count or count.total == 0 then
        local now = os.date("%Y-%m-%d %H:%M:%S")
        mydb:exec(
            "INSERT INTO posts (slug, title, author, content, created_at) VALUES (?, ?, ?, ?, ?)",
            "getting-started", "Getting Started", "Admin",
            "Welcome to the blog! This is your first post.\n\nEdit this post or create new ones from the **New Post** form. Posts support **bold**, *italic*, and `inline code`.\n\n## Features\n\n- Jinja2 templates with inheritance\n- Markdown rendering\n- Lua back-end scripts\n- SQLite3 database",
            now)
        mydb:exec(
            "INSERT INTO posts (slug, title, author, content, created_at) VALUES (?, ?, ?, ?, ?)",
            "lua-scripts", "Working with Lua Scripts", "Admin",
            "Lua scripts power the back-end logic for this blog.\n\n## Request Handling\n\nDefine global functions named after HTTP methods:\n\n```lua\nfunction POST(req)\n    local form, err = req:form()\n    -- handle the form\n    return 303, nil, {['Location'] = '/'}\nend\n```\n\n## Database Access\n\n```lua\nlocal mydb, err = db.open('mydb.db')\nlocal rows, err = mydb:query('SELECT * FROM posts')\n```\n\nThe `fs`, `json`, `http`, and `os` modules are also available in the sandbox.",
            now)
        mydb:exec(
            "INSERT INTO posts (slug, title, author, content, created_at) VALUES (?, ?, ?, ?, ?)",
            "markdown-pages", "Markdown Pages", "Admin",
            "This blog supports Markdown files rendered inside Jinja2 templates.\n\nCreate a `.md` file with an annotation comment:\n\n    <!-- _base:content -->\n\n    # My Post\n\n    Markdown **content** here.\n\nThe annotation specifies the template name and block. The Markdown body is converted to HTML and injected into that block.\n\n## Why Markdown?\n\n- Write content without HTML\n- Render inside any template block\n- Full Jinja2 context available\n- Great for documentation and blog posts",
            now)
    end

    mydb:close()
    return true, nil
end

function M.openDB()
    local ok, err = M.init()
    if not ok then
        return nil, err
    end
    return db.open(M.dbName)
end

function M.allPosts()
    local mydb, err = M.openDB()
    if not mydb then
        return nil, err
    end
    local rows, err = mydb:query("SELECT * FROM posts ORDER BY id DESC")
    mydb:close()
    return rows, err
end

function M.postBySlug(slug)
    local mydb, err = M.openDB()
    if not mydb then
        return nil, err
    end
    local row, err = mydb:queryRow("SELECT * FROM posts WHERE slug = ?", slug)
    mydb:close()
    return row, err
end

function M.createPost(slug, title, author, content)
    local mydb, err = M.openDB()
    if not mydb then
        return nil, err
    end
    local ts = os.date("%Y-%m-%d %H:%M:%S")
    local ok, err = mydb:exec(
        "INSERT INTO posts (slug, title, author, content, created_at) VALUES (?, ?, ?, ?, ?)",
        slug, title, author, content, ts)
    mydb:close()
    return ok, err
end

function M.deletePost(id)
    local mydb, err = M.openDB()
    if not mydb then
        return nil, err
    end
    local ok, err = mydb:exec("DELETE FROM posts WHERE id = ?", id)
    mydb:close()
    return ok, err
end

_M = M