"""
Sopel plugin: osterman - user tracking

Records join, part, quit, kick, ban, nick events to SQLite.
Derives db path from os_db_path set by protect.py.

On JOIN: sends a NOTICE greeting to the joining user.
  - First-time joiner: "Welcome to #channel, nick!"
  - Returning user:    "Welcome back, nick!"
  Controlled per channel via !set greet 1|0

  !seen  <nick>      - when was nick last seen
  !info / !i  <nick> - summary: first seen, known nicks, kick/ban count
  !log  / !l  <nick> - last N events (ACL/owner, PMed)
"""

import datetime
import os
import sqlite3
import time

from sopel import plugin


def _track_path(bot):
    base = bot.memory.get("os_db_path", "/var/osterman/db.json")
    return os.path.join(os.path.dirname(base), "track.db")


def _connect(bot):
    return sqlite3.connect(_track_path(bot))


def setup(bot):
    path = _track_path(bot)
    os.makedirs(os.path.dirname(path), exist_ok=True)
    with sqlite3.connect(path) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS events (
                id        INTEGER PRIMARY KEY AUTOINCREMENT,
                nick      TEXT    NOT NULL,
                type      TEXT    NOT NULL,
                channel   TEXT,
                detail    TEXT,
                timestamp INTEGER NOT NULL
            )
        """)
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_nick ON events(nick COLLATE NOCASE)"
        )
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_ts ON events(timestamp DESC)"
        )
        conn.commit()


def _log(bot, nick, type_, channel, detail):
    with _connect(bot) as conn:
        conn.execute(
            "INSERT INTO events (nick, type, channel, detail, timestamp)"
            " VALUES (?,?,?,?,?)",
            (nick, type_, channel, detail, int(time.time()))
        )
        conn.commit()


def _fmt_ts(ts):
    return datetime.datetime.fromtimestamp(ts).strftime("%m-%d %H:%M")


def _fmt_ago(ts):
    diff = int(time.time() - ts)
    if diff < 60:
        return "just now"
    elif diff < 3600:
        return f"{diff // 60}m ago"
    elif diff < 86400:
        return f"{diff // 3600}h ago"
    elif diff < 604800:
        return f"{diff // 86400}d ago"
    else:
        return f"{diff // 604800}w ago"


def _fmt_idle(seconds):
    seconds = int(seconds)
    if seconds < 60:
        return f"{seconds}s"
    elif seconds < 3600:
        return f"{seconds // 60}m"
    elif seconds < 86400:
        return f"{seconds // 3600}h"
    else:
        return f"{seconds // 86400}d"


def _greet_enabled(bot, channel):
    global_cfg = bot.memory.get("os_db", {}).get("config", {})
    chan_cfg    = (bot.memory.get("os_db", {})
                  .get("channels", {})
                  .get(channel.lower(), {})
                  .get("config", {}))
    return bool({**global_cfg, **chan_cfg}.get("greet", 1))


# --- Event handlers ---

@plugin.event("JOIN")
@plugin.require_chanmsg
def on_join(bot, trigger):
    if trigger.nick == bot.nick:
        return

    nick    = trigger.nick
    channel = str(trigger.sender)

    # Greeting check BEFORE logging so first-timers get the right message
    if _greet_enabled(bot, channel):
        try:
            with _connect(bot) as conn:
                seen = conn.execute(
                    "SELECT 1 FROM events WHERE nick = ? COLLATE NOCASE LIMIT 1",
                    (nick,)
                ).fetchone()
            if seen:
                bot.notice(f"Welcome back, {nick}!", nick)
            else:
                bot.notice(f"Welcome to {channel}, {nick}!", nick)
        except Exception:
            pass

    _log(bot, nick, "join", channel, None)


@plugin.event("PART")
@plugin.require_chanmsg
def on_part(bot, trigger):
    if trigger.nick == bot.nick:
        return
    reason = trigger.args[1] if len(trigger.args) > 1 else None
    _log(bot, trigger.nick, "part", str(trigger.sender), reason)


@plugin.event("QUIT")
def on_quit(bot, trigger):
    if trigger.nick == bot.nick:
        return
    reason = trigger.args[0] if trigger.args else None
    _log(bot, trigger.nick, "quit", None, reason)


@plugin.event("KICK")
def on_kick(bot, trigger):
    channel = str(trigger.sender)
    kicker  = trigger.nick
    victim  = trigger.args[1] if len(trigger.args) > 1 else None
    reason  = trigger.args[2] if len(trigger.args) > 2 else None
    if not victim:
        return
    detail = f"by {kicker}"
    if reason:
        detail += f": {reason}"
    _log(bot, victim, "kick", channel, detail)


@plugin.event("NICK")
def on_nick(bot, trigger):
    old_nick = trigger.nick
    new_nick = trigger.args[0] if trigger.args else None
    if not new_nick or old_nick == bot.nick:
        return
    _log(bot, old_nick, "nick", None, new_nick)


@plugin.event("MODE")
def on_mode_ban(bot, trigger):
    if not trigger.args or len(trigger.args) < 2:
        return
    channel  = trigger.args[0]
    mode_str = trigger.args[1]
    targets  = list(trigger.args[2:])
    actor    = trigger.nick
    adding   = True
    t_idx    = 0

    if not channel.startswith("#"):
        return

    for ch in mode_str:
        if ch == "+":
            adding = True
        elif ch == "-":
            adding = False
        elif ch == "b":
            if t_idx < len(targets):
                mask = targets[t_idx]
                t_idx += 1
                if adding:
                    nick_key = mask.split("!")[0] if "!" in mask and not mask.startswith("*") else mask
                    _log(bot, nick_key, "ban", channel, f"{mask} by {actor}")
        elif ch == "q":
            if t_idx < len(targets):
                mask     = targets[t_idx]
                t_idx   += 1
                action   = "mute" if adding else "unmute"
                nick_key = mask.split("!")[0] if "!" in mask and not mask.startswith("*") else mask
                _log(bot, nick_key, action, channel, f"{mask} by {actor}")
        elif ch in ("m", "i"):
            action = f"lock+{ch}" if adding else f"unlock-{ch}"
            _log(bot, actor, action, channel, None)


# --- Helpers shared by commands ---

def _is_owner(bot, trigger):
    return trigger.nick == bot.settings.core.owner


def _is_acl(bot, trigger):
    return trigger.nick in bot.memory.get("os_db", {}).get("acl", [])


# --- Commands ---

@plugin.command("seen")
def cmd_seen(bot, trigger):
    args    = (trigger.group(2) or "").split()
    in_chan = str(trigger.sender).startswith("#")
    authed  = _is_owner(bot, trigger) or _is_acl(bot, trigger)

    if not args:
        bot.notice("usage: !seen <nick>", trigger.nick)
        return

    target = args[0]

    if in_chan:
        channel = str(trigger.sender)
        chan    = bot.channels.get(channel)
        if chan and target in chan.users:
            last = bot.memory.get("os_last", {}).get((channel, target))
            idle_str = f", idle {_fmt_idle(time.time() - last)}" if last else ""
            bot.say(f"{target} is here{idle_str}", trigger.sender)
            return
        with _connect(bot) as conn:
            row = conn.execute(
                "SELECT type, timestamp FROM events"
                " WHERE nick = ? COLLATE NOCASE AND channel = ?"
                " ORDER BY timestamp DESC LIMIT 1",
                (target, channel)
            ).fetchone()
        if not row:
            bot.say(f"{target}: not seen in {channel}", trigger.sender)
            return
        type_, ts = row
        bot.say(f"{target} last seen {_fmt_ago(ts)} ({type_})", trigger.sender)

    elif authed:
        for chan_name, chan in bot.channels.items():
            if target in chan.users:
                last = bot.memory.get("os_last", {}).get((chan_name, target))
                idle_str = f", idle {_fmt_idle(time.time() - last)}" if last else ""
                bot.notice(f"{target} is in {chan_name}{idle_str}", trigger.nick)
                return
        with _connect(bot) as conn:
            row = conn.execute(
                "SELECT type, channel, timestamp FROM events"
                " WHERE nick = ? COLLATE NOCASE ORDER BY timestamp DESC LIMIT 1",
                (target,)
            ).fetchone()
        if not row:
            bot.notice(f"{target}: never seen", trigger.nick)
            return
        type_, chan_name, ts = row
        parts = [f"{target} last seen {_fmt_ago(ts)}"]
        if chan_name:
            parts.append(f"in {chan_name}")
        parts.append(f"({type_})")
        bot.notice(" ".join(parts), trigger.nick)

    else:
        bot.notice("use !seen in a channel", trigger.nick)


@plugin.command("info", "i")
def cmd_info(bot, trigger):
    args    = (trigger.group(2) or "").split()
    in_chan = str(trigger.sender).startswith("#")
    authed  = _is_owner(bot, trigger) or _is_acl(bot, trigger)

    if not args:
        bot.notice("usage: !info <nick>", trigger.nick)
        return

    if not in_chan and not authed:
        bot.notice("use !info in a channel", trigger.nick)
        return

    target  = args[0]
    channel = str(trigger.sender) if in_chan else None

    with _connect(bot) as conn:
        if channel:
            span = conn.execute(
                "SELECT MIN(timestamp), MAX(timestamp), COUNT(*) FROM events"
                " WHERE nick = ? COLLATE NOCASE AND channel = ?",
                (target, channel)
            ).fetchone()
        else:
            span = conn.execute(
                "SELECT MIN(timestamp), MAX(timestamp), COUNT(*) FROM events"
                " WHERE nick = ? COLLATE NOCASE",
                (target,)
            ).fetchone()

        if not span or span[0] is None:
            bot.notice(f"{target}: no records", trigger.nick)
            return

        first_ts, last_ts, total = span

        if channel:
            last_row = conn.execute(
                "SELECT type, channel, detail FROM events"
                " WHERE nick = ? COLLATE NOCASE AND channel = ?"
                " ORDER BY timestamp DESC LIMIT 1",
                (target, channel)
            ).fetchone()
            kb_count = conn.execute(
                "SELECT COUNT(*) FROM events"
                " WHERE nick = ? COLLATE NOCASE AND channel = ? AND type IN ('kick','ban')",
                (target, channel)
            ).fetchone()[0]
        else:
            last_row = conn.execute(
                "SELECT type, channel, detail FROM events"
                " WHERE nick = ? COLLATE NOCASE ORDER BY timestamp DESC LIMIT 1",
                (target,)
            ).fetchone()
            kb_count = conn.execute(
                "SELECT COUNT(*) FROM events"
                " WHERE nick = ? COLLATE NOCASE AND type IN ('kick','ban')",
                (target,)
            ).fetchone()[0]

        newnicks = [r[0] for r in conn.execute(
            "SELECT DISTINCT detail FROM events"
            " WHERE nick = ? COLLATE NOCASE AND type = 'nick' AND detail IS NOT NULL",
            (target,)
        ).fetchall()]

        oldnicks = [r[0] for r in conn.execute(
            "SELECT DISTINCT nick FROM events"
            " WHERE detail = ? COLLATE NOCASE AND type = 'nick'",
            (target,)
        ).fetchall()]

    bot.say(
        f"{target}: first seen {_fmt_ago(first_ts)}, last {_fmt_ago(last_ts)}, {total} events",
        trigger.nick
    )

    if last_row:
        type_, chan_name, detail = last_row
        last_str = type_
        if chan_name:
            last_str += f" in {chan_name}"
        if detail:
            last_str += f" ({detail})"
        bot.say(f"last: {last_str}", trigger.nick)

    all_nicks = sorted(set(newnicks + oldnicks) - {target})
    if all_nicks:
        bot.say(f"also known as: {', '.join(all_nicks[:6])}", trigger.nick)

    if kb_count:
        bot.say(f"kicked/banned: {kb_count}x", trigger.nick)


@plugin.command("log", "l")
def cmd_log(bot, trigger):
    if not _is_owner(bot, trigger) and not _is_acl(bot, trigger):
        return
    args = (trigger.group(2) or "").split()
    if not args:
        bot.say("usage: !log <nick> [limit]", trigger.nick)
        return
    target = args[0]

    cfg   = bot.memory.get("os_db", {}).get("config", {})
    limit = cfg.get("log_limit", 6)
    if len(args) > 1:
        try:
            limit = max(1, min(int(args[1]), 50))
        except ValueError:
            pass

    with _connect(bot) as conn:
        rows = conn.execute(
            "SELECT type, channel, detail, timestamp FROM events"
            " WHERE nick = ? COLLATE NOCASE ORDER BY timestamp DESC LIMIT ?",
            (target, limit)
        ).fetchall()

    if not rows:
        bot.say(f"{target}: no records", trigger.nick)
        return

    bot.say(f"-- {target} ({len(rows)} events) --", trigger.nick)
    for type_, channel, detail, ts in rows:
        content = channel or ""
        if detail:
            content = f"{content}  {detail}".strip()
        bot.say(f"[{_fmt_ts(ts)}] {type_:<6}  {content}", trigger.nick)