"""Promotion Engine — promotes episodic clusters to semantic memories.

Adapted from Memgraph to use SQLite via soagila.db.sqlite_init.
"""

import re
import json
import logging
from datetime import datetime, timezone
from uuid import uuid4
from pydantic import BaseModel

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 import semantic_memory

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

CHANNEL = "soagila:metrics"
MIN_CLUSTER_SIZE = 5  # Minimum episodic memories to trigger promotion


class PromotionCandidate(BaseModel):
    topic: str
    count: int
    memory_ids: list[str]
    memory_contents: list[str]
    user_id: str
    org_id: str


class PromotionResult(BaseModel):
    candidates_found: int = 0
    promotions_executed: int = 0
    semantic_memories_created: list[str] = []
    patterns_discovered: list[str] = []


# =============================================
# PII STRIPPING
# =============================================

# Common PII patterns for regex-based stripping
PII_PATTERNS = [
    # Turkish phone numbers
    (r'\b0?[5]\d{9}\b', '[PHONE]'),
    (r'\+90\s?\d{3}\s?\d{3}\s?\d{2}\s?\d{2}', '[PHONE]'),
    # International phone
    (r'\+\d{1,3}[\s-]?\(?\d{1,4}\)?[\s-]?\d{3,4}[\s-]?\d{3,4}', '[PHONE]'),
    # Email addresses
    (r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b', '[EMAIL]'),
    # Credit card numbers
    (r'\b\d{4}[\s-]?\d{4}[\s-]?\d{4}[\s-]?\d{4}\b', '[CARD]'),
    # IBAN
    (r'\b[A-Z]{2}\d{2}[\s]?\d{4}[\s]?\d{4}[\s]?\d{4}[\s]?\d{4}[\s]?\d{2,4}\b', '[IBAN]'),
    # Turkish TC Kimlik No (11 digits)
    (r'\b\d{11}\b', '[TC_ID]'),
    # Dates (various formats)
    (r'\b\d{1,2}[./]\d{1,2}[./]\d{2,4}\b', '[DATE]'),
    (r'\b\d{4}-\d{2}-\d{2}\b', '[DATE]'),
    # Currency amounts (Turkish Lira, USD, EUR)
    (r'\b\d{1,3}(?:[.,]\d{3})*(?:[.,]\d{2})?\s?(?:TL|₺|USD|\$|EUR|€)\b', '[AMOUNT]'),
    (r'\b(?:TL|₺|USD|\$|EUR|€)\s?\d{1,3}(?:[.,]\d{3})*(?:[.,]\d{2})?\b', '[AMOUNT]'),
    # Specific monetary amounts
    (r'\b\d+(?:\.\d+)?\s*(?:milyon|milyar|bin|million|billion|thousand)\b', '[AMOUNT]'),
    # Person names (simple: capitalized words after common titles)
    (r'\b(?:Bay|Bayan|Mr|Mrs|Ms|Dr)\s+[A-ZÇĞİÖŞÜ][a-zçğıöşü]+(?:\s+[A-ZÇĞİÖŞÜ][a-zçğıöşü]+)*\b', '[PERSON]'),
    # Specific times
    (r'\b\d{1,2}:\d{2}(?::\d{2})?\b', '[TIME]'),
]

_compiled_patterns = [(re.compile(p, re.IGNORECASE), repl) for p, repl in PII_PATTERNS]


def strip_pii(text: str) -> str:
    """Remove PII from text using regex patterns."""
    result = text
    for pattern, replacement in _compiled_patterns:
        result = pattern.sub(replacement, result)
    return result


def extract_pattern(contents: list[str], topic: str) -> str:
    """Extract a generalized pattern description from episodic memory contents."""
    # Count common keywords (simple frequency analysis)
    word_freq: dict[str, int] = {}
    stop_words = {
        "bir", "ve", "ile", "için", "bu", "da", "de", "mi", "mı",
        "the", "a", "an", "and", "or", "for", "is", "are", "was",
        "in", "on", "at", "to", "of", "with", "that", "this",
        "ne", "nasıl", "neden", "hakkında", "about", "what", "how",
    }

    for content in contents:
        # Strip PII first
        cleaned = strip_pii(content).lower()
        words = re.findall(r'\b\w{3,}\b', cleaned)
        for word in words:
            if word not in stop_words and not word.startswith('['):
                word_freq[word] = word_freq.get(word, 0) + 1

    # Top keywords that appear in multiple memories
    common_keywords = sorted(
        [(w, c) for w, c in word_freq.items() if c >= 2],
        key=lambda x: x[1],
        reverse=True,
    )[:5]

    keywords_str = ", ".join([w for w, _ in common_keywords])

    # Generate pattern description
    pattern = f"User frequently discusses {topic}-related topics. Key themes: {keywords_str}."

    # Add sentiment hints based on common words
    negative_words = {"şikayet", "sorun", "problem", "yüksek", "pahalı", "complaint", "expensive", "issue", "high"}
    positive_words = {"memnun", "teşekkür", "iyi", "harika", "satisfied", "great", "good", "happy"}

    neg_count = sum(1 for w, _ in common_keywords if w in negative_words)
    pos_count = sum(1 for w, _ in common_keywords if w in positive_words)

    if neg_count > pos_count:
        pattern += f" Sentiment: tends to express dissatisfaction regarding {topic}."
    elif pos_count > neg_count:
        pattern += f" Sentiment: generally positive about {topic}."

    return pattern


# =============================================
# PROMOTION CHECK & EXECUTION
# =============================================

async def check_promotion_candidates(user_id: str) -> list[PromotionCandidate]:
    """Find episodic memory clusters that qualify for promotion to semantic."""
    db = await get_db()
    candidates = []

    # Use SQL JOIN on memory_topics with GROUP BY and HAVING COUNT >= MIN_CLUSTER_SIZE
    cursor = await db.execute(
        """
        SELECT mt.topic_name, COUNT(*) as cnt
        FROM memory_topics mt
        JOIN episodic_memories em ON em.id = mt.memory_id
        WHERE em.user_id = ? AND em.is_decayed = 0
        GROUP BY mt.topic_name
        HAVING COUNT(*) >= ?
        ORDER BY cnt DESC
        """,
        (user_id, MIN_CLUSTER_SIZE),
    )
    topic_rows = await cursor.fetchall()

    for topic_row in topic_rows:
        topic_name = topic_row[0]

        # Check if semantic memory already exists for this topic+user
        existing_cursor = await db.execute(
            """
            SELECT sm.id FROM semantic_memories sm
            JOIN memory_topics mt ON mt.memory_id = sm.id
            WHERE sm.user_id = ? AND mt.topic_name = ?
            LIMIT 1
            """,
            (user_id, topic_name),
        )
        existing_row = await existing_cursor.fetchone()

        if existing_row:
            continue  # Already promoted

        # Fetch the memory IDs and contents for this topic cluster
        mem_cursor = await db.execute(
            """
            SELECT em.id, em.encrypted_content, em.org_id
            FROM episodic_memories em
            JOIN memory_topics mt ON mt.memory_id = em.id
            WHERE mt.topic_name = ? AND em.user_id = ? AND em.is_decayed = 0
            """,
            (topic_name, user_id),
        )
        mem_rows = await mem_cursor.fetchall()

        memory_ids = []
        memory_contents = []
        org_id = ""
        for mr in mem_rows:
            memory_ids.append(str(mr[0]))
            content = mr[1].decode("utf-8") if isinstance(mr[1], (bytes, memoryview)) else str(mr[1])
            memory_contents.append(content)
            if not org_id:
                org_id = str(mr[2]) if mr[2] else ""

        candidates.append(PromotionCandidate(
            topic=topic_name,
            count=len(memory_ids),
            memory_ids=memory_ids,
            memory_contents=memory_contents,
            user_id=user_id,
            org_id=org_id,
        ))

    logger.info(f"Found {len(candidates)} promotion candidates for user {user_id}")
    return candidates


async def execute_promotion(candidate: PromotionCandidate) -> SemanticMemory:
    """Execute promotion: extract pattern, strip PII, create semantic memory."""
    # Extract generalized pattern
    pattern_desc = extract_pattern(candidate.memory_contents, candidate.topic)

    # Strip PII from pattern
    clean_pattern = strip_pii(pattern_desc)

    # Create content (PII-stripped summary)
    content = f"Generalized insight about {candidate.topic}: {clean_pattern}"

    # Calculate confidence based on cluster size
    confidence = min(0.5 + (candidate.count * 0.1), 1.0)

    # Create semantic memory
    mem = SemanticMemory(
        id=str(uuid4()),
        user_id=candidate.user_id,
        org_id=candidate.org_id,
        content=content,
        pattern_description=clean_pattern,
        confidence_score=confidence,
        source_episodic_count=candidate.count,
        source_episodic_ids=candidate.memory_ids,
        pii_verified_clean=True,
        topic_tags=[candidate.topic],
    )

    # Store
    await semantic_memory.store(mem)

    # Create PROMOTED_TO relationships in memory_relationships table
    db = await get_db()
    for ep_id in candidate.memory_ids:
        await db.execute(
            """
            INSERT OR IGNORE INTO memory_relationships (id, source_id, target_id, relationship_type)
            VALUES (?, ?, ?, ?)
            """,
            (str(uuid4()), ep_id, mem.id, "PROMOTED_TO"),
        )
    await db.commit()

    # Publish promotion event
    event = json.dumps({
        "type": "promotion_triggered",
        "from_memories": candidate.memory_ids,
        "to_semantic": mem.id,
        "pattern": clean_pattern,
        "topic": candidate.topic,
        "source_count": candidate.count,
        "confidence": confidence,
        "user_id": candidate.user_id,
        "timestamp": datetime.now(timezone.utc).isoformat(),
    })
    await publish_event(CHANNEL, event)

    logger.info(
        f"Promotion executed: {candidate.count} episodic -> semantic {mem.id} "
        f"(topic={candidate.topic}, confidence={confidence})"
    )

    return mem


async def run_promotion_check(user_id: str) -> PromotionResult:
    """Full promotion cycle: find candidates and execute promotions."""
    result = PromotionResult()

    candidates = await check_promotion_candidates(user_id)
    result.candidates_found = len(candidates)

    for candidate in candidates:
        sem_mem = await execute_promotion(candidate)
        result.promotions_executed += 1
        result.semantic_memories_created.append(sem_mem.id)
        result.patterns_discovered.append(sem_mem.pattern_description)

        # Memory Inheritance: promote to global pool if eligible
        try:
            from soagila.config import get_settings, get_project_hash, get_project_dir
            settings = get_settings()
            if settings.inheritance_enabled:
                from soagila.project_manager import is_feature_allowed
                allowed, _ = is_feature_allowed("inheritance")
                if allowed:
                    from soagila.core.inheritance.engine import promote_to_global
                    p_name = ""
                    try:
                        import json as _j
                        meta = get_project_dir() / "project.json"
                        if meta.exists():
                            p_name = _j.loads(meta.read_text()).get("name", "")
                    except Exception:
                        pass
                    await promote_to_global(sem_mem.id, get_project_hash(), p_name)
        except Exception as e:
            logger.debug(f"Global promotion skipped: {e}")

    return result
