app/store_sqlite.py

# app/store_sqlite.py
import json
import os
import sqlite3
import threading
import time

WINDOW = 6          # เก็บกี่เทิร์นล่าสุด
TURN_TTL = 7200     # 2 ชม.
SESS_TTL = 86400    # 24 ชม.


def _pick_db_path() -> str:
    """หาโฟลเดอร์ที่เขียนได้จริง — Space ฟรีไม่มี /data"""
    cands = []
    if os.environ.get("DB_PATH"):
        cands.append(os.environ["DB_PATH"])
    cands += ["/data/npc.db", "/home/user/app/data/npc.db", "/tmp/npc.db"]
    for c in cands:
        try:
            d = os.path.dirname(c) or "."
            os.makedirs(d, exist_ok=True)
            probe = os.path.join(d, ".wtest")
            with open(probe, "w") as f:
                f.write("1")
            os.remove(probe)
            return c
        except Exception:
            continue
    return ":memory:"


DB_PATH = _pick_db_path()
print(f"[store] db path = {DB_PATH}", flush=True)

_conn = sqlite3.connect(DB_PATH, check_same_thread=False, timeout=5.0)
_conn.execute("PRAGMA journal_mode=WAL")      # อ่าน/เขียนพร้อมกันได้
_conn.execute("PRAGMA synchronous=NORMAL")    # เร็วขึ้น ~3x
_conn.execute("PRAGMA busy_timeout=5000")
_lock = threading.Lock()

_conn.executescript("""
CREATE TABLE IF NOT EXISTS turns (
  sid TEXT, seq INTEGER, u TEXT, a TEXT, beat TEXT, ts INTEGER,
  PRIMARY KEY (sid, seq));
CREATE TABLE IF NOT EXISTS sessions (
  sid TEXT PRIMARY KEY, pid TEXT, npc TEXT,
  summary TEXT DEFAULT '', n INTEGER DEFAULT 0, ts INTEGER);
CREATE TABLE IF NOT EXISTS players (
  pid TEXT PRIMARY KEY, name TEXT, facts TEXT DEFAULT '{}',
  rel TEXT DEFAULT '{}', quests TEXT DEFAULT '{}',
  v INTEGER DEFAULT 0, last_seen INTEGER);
CREATE TABLE IF NOT EXISTS rl (
  pid TEXT, minute INTEGER, n INTEGER, PRIMARY KEY (pid, minute));
CREATE INDEX IF NOT EXISTS ix_turns ON turns(sid, seq DESC);
""")
_conn.commit()


# ─────────── window ───────────
def push_turn(sid, pid, npc, u, a):
    now = int(time.time())
    with _lock, _conn:
        _conn.execute(
            "INSERT INTO sessions(sid,pid,npc,n,ts) VALUES(?,?,?,1,?) "
            "ON CONFLICT(sid) DO UPDATE SET n=n+1, ts=excluded.ts",
            (sid, pid, npc, now))
        n = _conn.execute("SELECT n FROM sessions WHERE sid=?", (sid,)).fetchone()[0]
        _conn.execute("INSERT OR REPLACE INTO turns VALUES(?,?,?,?,?,?)",
                      (sid, n, u, a, None, now))
        _conn.execute("DELETE FROM turns WHERE sid=? AND seq<=?", (sid, n - WINDOW))
    return n


def get_window(sid):
    rows = _conn.execute(
        "SELECT u,a,beat FROM turns WHERE sid=? ORDER BY seq ASC", (sid,)).fetchall()
    return [{"u": r[0], "a": r[1], "beat": r[2]} for r in rows]


def get_turn_count(sid):
    r = _conn.execute("SELECT n FROM sessions WHERE sid=?", (sid,)).fetchone()
    return r[0] if r else 0


def reset_session(sid):
    with _lock, _conn:
        _conn.execute("DELETE FROM turns WHERE sid=?", (sid,))
        _conn.execute("DELETE FROM sessions WHERE sid=?", (sid,))


# ─────────── summary ───────────
def get_summary(sid):
    r = _conn.execute("SELECT summary FROM sessions WHERE sid=?", (sid,)).fetchone()
    return (r[0] if r else "") or ""


def set_summary(sid, text):
    with _lock, _conn:
        _conn.execute("UPDATE sessions SET summary=? WHERE sid=?", (text, sid))


# ─────────── profile ───────────
def get_profile(pid):
    r = _conn.execute(
        "SELECT name,facts,rel,quests,v FROM players WHERE pid=?", (pid,)).fetchone()
    if not r:
        return {"pid": pid, "name": None, "facts": {}, "rel": {}, "quests": {}, "v": 0}
    return {"pid": pid, "name": r[0], "facts": json.loads(r[1]),
            "rel": json.loads(r[2]), "quests": json.loads(r[3]), "v": r[4]}


def save_profile(p):
    with _lock, _conn:
        _conn.execute(
            "INSERT INTO players(pid,name,facts,rel,quests,v,last_seen) "
            "VALUES(?,?,?,?,?,?,?) ON CONFLICT(pid) DO UPDATE SET "
            "name=excluded.name, facts=excluded.facts, rel=excluded.rel, "
            "quests=excluded.quests, v=players.v+1, last_seen=excluded.last_seen",
            (p["pid"], p.get("name"), json.dumps(p.get("facts", {})),
             json.dumps(p.get("rel", {})), json.dumps(p.get("quests", {})),
             p.get("v", 0) + 1, int(time.time())))


# ─────────── rate limit ───────────
def rate_limit(pid, limit=12):
    m = int(time.time() // 60)
    with _lock, _conn:
        _conn.execute("INSERT INTO rl(pid,minute,n) VALUES(?,?,1) "
                      "ON CONFLICT(pid,minute) DO UPDATE SET n=n+1", (pid, m))
        n = _conn.execute("SELECT n FROM rl WHERE pid=? AND minute=?",
                          (pid, m)).fetchone()[0]
    return n <= limit


# ─────────── janitor (แทน TTL ของ Redis) ───────────
def janitor():
    now = int(time.time())
    with _lock, _conn:
        _conn.execute("DELETE FROM rl WHERE minute < ?", (now // 60 - 2,))
        _conn.execute("DELETE FROM turns WHERE ts < ?", (now - TURN_TTL,))
        _conn.execute("DELETE FROM sessions WHERE ts < ?", (now - SESS_TTL,))


def stats():
    q = lambda s: _conn.execute(s).fetchone()[0]
    return {"db": DB_PATH, "turns": q("SELECT COUNT(*) FROM turns"),
            "sessions": q("SELECT COUNT(*) FROM sessions"),
            "players": q("SELECT COUNT(*) FROM players")}