"""SQLite-backed memory store — used when DATABASE_URL is not set.

SQLite does not support pgvector, so semantic search is disabled.
New columns are added for schema compatibility but embeddings are always NULL.
"""

import sqlite3
import uuid
from datetime import datetime, timezone

import aiosqlite

_DDL = """
CREATE TABLE IF NOT EXISTS memory_facts (
    id          TEXT PRIMARY KEY,
    category    TEXT NOT NULL,
    key         TEXT NOT NULL,
    value       TEXT NOT NULL,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL,
    source_session_id TEXT,
    source      TEXT NOT NULL DEFAULT 'conversation',
    confidence  INTEGER NOT NULL DEFAULT 70,
    expires_at  TEXT,
    last_verified_at TEXT,
    source_context TEXT,
    UNIQUE(category, key)
);

CREATE TABLE IF NOT EXISTS memory_summary (
    id          INTEGER PRIMARY KEY DEFAULT 1,
    content     TEXT NOT NULL,
    generated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS session_memory_state (
    session_id  TEXT PRIMARY KEY,
    extracted_at TEXT NOT NULL
);
"""


class SqliteMemoryStore:
    def __init__(self, db_path: str) -> None:
        self._db_path = db_path
        with sqlite3.connect(db_path) as conn:
            conn.executescript(_DDL)
            conn.commit()

    # ── Facts ────────────────────────────────────────────────────────────────

    async def upsert_fact(
        self,
        category: str,
        key: str,
        value: str,
        source_session_id: str | None = None,
        source: str = "conversation",
        confidence: int = 70,
        expires_at: datetime | None = None,
        source_context: str = "",
    ) -> None:
        now = datetime.now(timezone.utc).isoformat()
        expires_str = expires_at.isoformat() if expires_at else None
        async with aiosqlite.connect(self._db_path) as db:
            await db.execute(
                """INSERT INTO memory_facts
                    (id, category, key, value, created_at, updated_at, source_session_id,
                     source, confidence, expires_at, source_context)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                   ON CONFLICT(category, key) DO UPDATE SET
                       value              = excluded.value,
                       updated_at         = excluded.updated_at,
                       source_session_id  = excluded.source_session_id,
                       source             = excluded.source,
                       confidence         = excluded.confidence,
                       expires_at         = excluded.expires_at,
                       source_context     = excluded.source_context""",
                (
                    str(uuid.uuid4()), category, key, value, now, now,
                    source_session_id, source, confidence, expires_str, source_context,
                ),
            )
            await db.commit()

    async def search_facts(self, query: str, limit: int = 15) -> list[dict]:
        terms = [t for t in query.lower().split() if len(t) > 1]
        if not terms:
            return await self.get_all_facts(limit=limit)

        conditions = " OR ".join(
            ["(LOWER(category) LIKE ? OR LOWER(key) LIKE ? OR LOWER(value) LIKE ?)"] * len(terms)
        )
        params: list = [f"%{t}%" for t in terms for _ in range(3)]

        async with aiosqlite.connect(self._db_path) as db:
            async with db.execute(
                f"SELECT id, category, key, value, updated_at, source, confidence, "
                f"expires_at, source_context FROM memory_facts "
                f"WHERE {conditions} ORDER BY updated_at DESC LIMIT ?",
                params + [limit],
            ) as cur:
                rows = await cur.fetchall()
        return [_row_to_dict(r) for r in rows]

    async def delete_fact(self, key: str, category: str | None = None) -> int:
        async with aiosqlite.connect(self._db_path) as db:
            if category:
                cur = await db.execute(
                    "DELETE FROM memory_facts WHERE LOWER(key)=LOWER(?) AND LOWER(category)=LOWER(?)",
                    (key, category),
                )
            else:
                cur = await db.execute(
                    "DELETE FROM memory_facts WHERE LOWER(key)=LOWER(?)", (key,)
                )
            await db.commit()
            return cur.rowcount

    async def get_all_facts(self, limit: int | None = None) -> list[dict]:
        q = (
            "SELECT id, category, key, value, updated_at, source, confidence, "
            "expires_at, source_context FROM memory_facts ORDER BY category, updated_at DESC"
        )
        if limit:
            q += f" LIMIT {limit}"
        async with aiosqlite.connect(self._db_path) as db:
            async with db.execute(q) as cur:
                rows = await cur.fetchall()
        return [_row_to_dict(r) for r in rows]

    async def fact_count(self) -> int:
        async with aiosqlite.connect(self._db_path) as db:
            async with db.execute("SELECT COUNT(*) FROM memory_facts") as cur:
                row = await cur.fetchone()
        return row[0] if row else 0

    # ── Summary ───────────────────────────────────────────────────────────────

    async def get_summary(self) -> str | None:
        async with aiosqlite.connect(self._db_path) as db:
            async with db.execute("SELECT content FROM memory_summary WHERE id=1") as cur:
                row = await cur.fetchone()
        return row[0] if row else None

    async def set_summary(self, content: str) -> None:
        now = datetime.now(timezone.utc).isoformat()
        async with aiosqlite.connect(self._db_path) as db:
            await db.execute(
                """INSERT INTO memory_summary (id, content, generated_at) VALUES (1, ?, ?)
                   ON CONFLICT(id) DO UPDATE SET content=excluded.content, generated_at=excluded.generated_at""",
                (content, now),
            )
            await db.commit()

    # ── Session extraction tracking ───────────────────────────────────────────

    async def mark_session_extracted(self, session_id: str) -> None:
        now = datetime.now(timezone.utc).isoformat()
        async with aiosqlite.connect(self._db_path) as db:
            await db.execute(
                """INSERT INTO session_memory_state (session_id, extracted_at) VALUES (?, ?)
                   ON CONFLICT(session_id) DO UPDATE SET extracted_at=excluded.extracted_at""",
                (session_id, now),
            )
            await db.commit()

    async def get_extracted_at(self, session_id: str) -> str | None:
        async with aiosqlite.connect(self._db_path) as db:
            async with db.execute(
                "SELECT extracted_at FROM session_memory_state WHERE session_id=?", (session_id,)
            ) as cur:
                row = await cur.fetchone()
        return row[0] if row else None


def _row_to_dict(row: tuple) -> dict:
    return {
        "id": row[0],
        "category": row[1],
        "key": row[2],
        "value": row[3],
        "updated_at": row[4],
        "source": row[5] if len(row) > 5 else "conversation",
        "confidence": row[6] if len(row) > 6 else 70,
        "expires_at": row[7] if len(row) > 7 else None,
        "source_context": row[8] if len(row) > 8 else "",
    }