"""Plugin: per-channel persistent key-value notes (SQLite)."""

from __future__ import annotations

import logging
import sqlite3
from pathlib import Path

from derp.plugin import command

log = logging.getLogger(__name__)

_DB_PATH = Path("data/notes.db")
_MAX_LIST = 20

_conn: sqlite3.Connection | None = None


def _db() -> sqlite3.Connection:
    """Lazy-init the database connection and schema."""
    global _conn
    if _conn is not None:
        return _conn
    _DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    _conn = sqlite3.connect(str(_DB_PATH))
    _conn.execute("""
        CREATE TABLE IF NOT EXISTS notes (
            channel  TEXT NOT NULL,
            key      TEXT NOT NULL,
            value    TEXT NOT NULL,
            nick     TEXT NOT NULL,
            PRIMARY KEY (channel, key)
        )
    """)
    _conn.commit()
    return _conn


@command("note", help="Notes: !note set|get|del|list|clear")
async def cmd_note(bot, message):
    """Per-channel persistent key-value store.

    Usage:
        !note set <key> <value>     Store a note
        !note get <key>             Retrieve a note
        !note del <key>             Delete a note
        !note list                  List all keys
        !note clear                 Clear all notes for this channel (admin)
    """
    parts = message.text.split(None, 3)
    if len(parts) < 2:
        await bot.reply(message, "Usage: !note <set|get|del|list|clear> [args]")
        return

    sub = parts[1].lower()
    channel = message.target or "dm"

    if sub == "set":
        if len(parts) < 4:
            await bot.reply(message, "Usage: !note set <key> <value>")
            return
        key = parts[2].lower()
        value = parts[3]
        db = _db()
        db.execute(
            "INSERT OR REPLACE INTO notes (channel, key, value, nick) VALUES (?, ?, ?, ?)",
            (channel, key, value, message.nick or "?"),
        )
        db.commit()
        await bot.reply(message, f"{key}: saved")

    elif sub == "get":
        if len(parts) < 3:
            await bot.reply(message, "Usage: !note get <key>")
            return
        key = parts[2].lower()
        db = _db()
        row = db.execute(
            "SELECT value, nick FROM notes WHERE channel = ? AND key = ?",
            (channel, key),
        ).fetchone()
        if row:
            value, nick = row
            await bot.reply(message, f"{key}: {value} (set by {nick})")
        else:
            await bot.reply(message, f"{key}: not found")

    elif sub == "del":
        if len(parts) < 3:
            await bot.reply(message, "Usage: !note del <key>")
            return
        key = parts[2].lower()
        db = _db()
        cur = db.execute(
            "DELETE FROM notes WHERE channel = ? AND key = ?",
            (channel, key),
        )
        db.commit()
        if cur.rowcount:
            await bot.reply(message, f"{key}: deleted")
        else:
            await bot.reply(message, f"{key}: not found")

    elif sub == "list":
        db = _db()
        rows = db.execute(
            "SELECT key FROM notes WHERE channel = ? ORDER BY key LIMIT ?",
            (channel, _MAX_LIST),
        ).fetchall()
        if not rows:
            await bot.reply(message, "No notes")
            return
        keys = [r[0] for r in rows]
        total = db.execute(
            "SELECT COUNT(*) FROM notes WHERE channel = ?", (channel,),
        ).fetchone()[0]
        suffix = f" ({total} total)" if total > _MAX_LIST else ""
        await bot.reply(message, f"Notes: {', '.join(keys)}{suffix}")

    elif sub == "clear":
        if not bot._is_admin(message):
            await bot.reply(message, "Permission denied: clear requires admin")
            return
        db = _db()
        cur = db.execute("DELETE FROM notes WHERE channel = ?", (channel,))
        db.commit()
        await bot.reply(message, f"Cleared {cur.rowcount} notes")

    else:
        await bot.reply(message, "Usage: !note <set|get|del|list|clear> [args]")