"""Local SQLite memory; lexical retrieval with explicit provenance and expiry."""
from pathlib import Path
import json
import math
import re
import sqlite3
import time
import uuid


class Memory:
    def __init__(self, path):
        self.db = sqlite3.connect(str(path))
        self.db.execute("CREATE TABLE IF NOT EXISTS memory (id TEXT PRIMARY KEY, layer TEXT, text TEXT, source TEXT, confidence REAL, created REAL, expires REAL, provenance TEXT)")

    def put(self, text, source, confidence=1.0, layer="L2", ttl_seconds=None, provenance=None, record_id=None):
        if not text or not source or not math.isfinite(confidence) or not 0 <= confidence <= 1 or layer not in {f"L{i}" for i in range(6)}:
            raise ValueError("Memory requires text, source, valid confidence and layer")
        if ttl_seconds is not None and (not math.isfinite(ttl_seconds) or ttl_seconds <= 0):
            raise ValueError("TTL must be positive")
        rid, now = record_id or uuid.uuid4().hex, time.time()
        self.db.execute("INSERT OR REPLACE INTO memory VALUES (?,?,?,?,?,?,?,?)", (rid, layer, text, source, confidence, now, None if ttl_seconds is None else now + ttl_seconds, json.dumps(provenance or {})))
        self.db.commit()
        return rid

    def retrieve(self, query, limit=5, min_confidence=0.5):
        terms = set(re.findall(r"\w+", query.lower()))
        self.db.row_factory = sqlite3.Row
        rows = self.db.execute("SELECT * FROM memory WHERE (expires IS NULL OR expires>?) AND confidence>=?", (time.time(), min_confidence)).fetchall()
        scored = [(len(terms & set(re.findall(r"\w+", r["text"].lower()))), dict(r)) for r in rows]
        return [r for score, r in sorted(scored, key=lambda x: (x[0], x[1]["created"]), reverse=True) if score > 0][:max(0, limit)]

    def delete(self, record_id):
        self.db.execute("DELETE FROM memory WHERE id=?", (record_id,))
        self.db.commit()

    def expire(self):
        self.db.execute("DELETE FROM memory WHERE expires<=?", (time.time(),))
        self.db.commit()

    def close(self):
        self.db.close()