"""Semantic Memory (Tier 3) — SQLite-backed generalized patterns.

Adapted from PostgreSQL + Memgraph to use SQLite via soagila.db.sqlite_init.
Topics stored in topics + memory_topics tables.
"""

import json
import hashlib
import logging
from datetime import datetime, timezone

from soagila.db.sqlite_init import get_db
from soagila.db.event_bus import publish_event
from soagila.core.tcmg.memory_models import SemanticMemory
from soagila.core.tcmg.embedding_service import generate_embedding, cosine_similarity

logger = logging.getLogger("soagila.tcmg.semantic")

CHANNEL = "soagila:metrics"

# Canonical column list for SELECTs that feed ``_row_to_semantic``.
_SEMANTIC_COLUMNS = (
    "id, org_id, user_id, encrypted_content, encryption_key_ref, embedding, "
    "pattern_description, confidence_score, source_episodic_count, "
    "source_episodic_ids, pii_verified_clean, created_at"
)


def _decrypt_semantic(encrypted_content, key_ref: str | None) -> str:
    """Mirror of episodic_memory._decrypt_content, but importing lazily."""
    if encrypted_content is None:
        return ""
    raw = (
        bytes(encrypted_content)
        if isinstance(encrypted_content, (bytes, memoryview))
        else str(encrypted_content).encode("utf-8")
    )

    if key_ref in (None, "", "mvp-plaintext"):
        try:
            return raw.decode("utf-8")
        except UnicodeDecodeError:
            return ""

    try:
        from soagila.core.encryption.simulated_he import get_encryption_engine
        engine = get_encryption_engine()
        if not engine.key_exists(key_ref):
            return "[encrypted: key destroyed]"
        return engine.decrypt(raw, key_ref)
    except Exception as exc:  # noqa: BLE001
        logger.warning(f"Semantic decrypt failed for key_ref={key_ref}: {exc}")
        try:
            return raw.decode("utf-8")
        except UnicodeDecodeError:
            return "[decrypt failed]"


def _row_to_semantic(row) -> SemanticMemory:
    """Convert a SQLite row to a SemanticMemory instance.

    Expected column order (matches ``_SEMANTIC_COLUMNS``):
        id, org_id, user_id, encrypted_content, encryption_key_ref, embedding,
        pattern_description, confidence_score, source_episodic_count,
        source_episodic_ids, pii_verified_clean, created_at
    """
    key_ref = row[4] if len(row) > 4 else None
    content = _decrypt_semantic(row[3], key_ref)
    embedding = json.loads(row[5]) if row[5] else None
    source_ids = row[9] if row[9] else []
    if isinstance(source_ids, str):
        source_ids = json.loads(source_ids)

    return SemanticMemory(
        id=str(row[0]),
        org_id=str(row[1]),
        user_id=str(row[2]) if row[2] else "",
        content=content,
        embedding=embedding,
        pattern_description=row[6] or "",
        confidence_score=row[7] or 1.0,
        source_episodic_count=row[8] or 0,
        source_episodic_ids=source_ids,
        pii_verified_clean=bool(row[10]),
        created_at=row[11],
    )


async def store(memory: SemanticMemory) -> str:
    """Store a semantic memory in SQLite."""
    # Generate embedding
    embed_text = f"{memory.pattern_description} {memory.content}"
    embedding = generate_embedding(embed_text)

    db = await get_db()

    # Real AES-256-GCM encryption per memory — same pattern as episodic
    from soagila.core.encryption.simulated_he import get_encryption_engine
    engine = get_encryption_engine()
    encryption_key_ref = engine.generate_key("memory")
    encrypted_content = engine.encrypt(memory.content, encryption_key_ref)
    content_hash = hashlib.sha256(memory.content.encode()).hexdigest()
    now = datetime.now(timezone.utc).isoformat()

    await db.execute(
        """
        INSERT INTO semantic_memories (
            id, org_id, user_id, encrypted_content, encryption_key_ref,
            embedding, pattern_description, confidence_score,
            source_episodic_count, source_episodic_ids,
            pii_verified_clean, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            memory.id,
            memory.org_id,
            memory.user_id,
            encrypted_content,
            encryption_key_ref,
            json.dumps(embedding),
            memory.pattern_description,
            memory.confidence_score,
            memory.source_episodic_count,
            json.dumps(memory.source_episodic_ids),
            int(memory.pii_verified_clean),
            memory.created_at.isoformat() if isinstance(memory.created_at, datetime) else str(memory.created_at),
            now,
        ),
    )

    # Create topic entries and memory-topic links
    for tag in memory.topic_tags:
        await db.execute("INSERT OR IGNORE INTO topics (name) VALUES (?)", (tag,))
        await db.execute(
            "INSERT OR IGNORE INTO memory_topics (memory_id, topic_name) VALUES (?, ?)",
            (memory.id, tag),
        )

    await db.commit()

    # Publish event
    event = json.dumps({
        "type": "memory_stored",
        "tier": "semantic",
        "memory_id": memory.id,
        "user_id": memory.user_id,
        "org_id": memory.org_id,
        "pattern": memory.pattern_description,
        "confidence": memory.confidence_score,
        "source_count": memory.source_episodic_count,
        "timestamp": datetime.now(timezone.utc).isoformat(),
    })
    await publish_event(CHANNEL, event)

    logger.info(f"Stored semantic memory {memory.id}: {memory.pattern_description}")
    return memory.id


async def query(
    org_id: str,
    user_id: str | None,
    query_text: str,
    limit: int = 10,
) -> list[SemanticMemory]:
    """Query semantic memories using embedding similarity."""
    query_embedding = generate_embedding(query_text)

    db = await get_db()

    if user_id:
        cursor = await db.execute(
            f"""
            SELECT {_SEMANTIC_COLUMNS}
            FROM semantic_memories
            WHERE org_id = ? AND (user_id = ? OR user_id IS NULL)
            ORDER BY created_at DESC
            LIMIT ?
            """,
            (org_id, user_id, limit * 3),
        )
    else:
        cursor = await db.execute(
            f"""
            SELECT {_SEMANTIC_COLUMNS}
            FROM semantic_memories
            WHERE org_id = ?
            ORDER BY created_at DESC
            LIMIT ?
            """,
            (org_id, limit * 3),
        )
    rows = await cursor.fetchall()

    memories = []
    for row in rows:
        mem = _row_to_semantic(row)

        # Score by similarity
        if mem.embedding and query_embedding:
            sim = cosine_similarity(query_embedding, mem.embedding)
            mem.metadata["similarity_score"] = sim

        memories.append(mem)

    # Sort by similarity
    memories.sort(key=lambda m: m.metadata.get("similarity_score", 0), reverse=True)
    return memories[:limit]


async def get_user_profile(user_id: str) -> list[SemanticMemory]:
    """Get all semantic knowledge about a user."""
    db = await get_db()
    cursor = await db.execute(
        f"""
        SELECT {_SEMANTIC_COLUMNS}
        FROM semantic_memories
        WHERE user_id = ?
        ORDER BY confidence_score DESC
        """,
        (user_id,),
    )
    rows = await cursor.fetchall()

    return [_row_to_semantic(row) for row in rows]
