import json
import logging
from datetime import datetime
from collections import Counter

from soagila.db.sqlite_init import get_db
from soagila.utils.time_utils import get_current_time

logger = logging.getLogger("soagila.pmp.pattern")


async def detect_usage_patterns(user_id: str) -> dict:
    """
    Analyze user's historical access patterns for predictive priming.
    Returns detected patterns: common topics, active hours, session frequency.
    """
    db = await get_db()
    result = await db.execute(
        """
            SELECT topic_tags, created_at, access_count
            FROM episodic_memories
            WHERE user_id = ? AND NOT is_decayed
            ORDER BY created_at DESC
            LIMIT 200
        """,
        (user_id,),
    )
    rows = await result.fetchall()

    if not rows:
        return {
            "user_id": user_id,
            "patterns_detected": False,
            "common_topics": [],
            "active_hours": [],
            "avg_memories_per_day": 0,
        }

    # Analyze topics
    topic_counter = Counter()
    hour_counter = Counter()
    dates = set()

    for row in rows:
        tags = row[0] if row[0] else "[]"
        created = row[1]

        if isinstance(tags, str):
            tags = json.loads(tags)

        for tag in tags:
            topic_counter[tag] += 1

        if created:
            if isinstance(created, str):
                created = datetime.fromisoformat(created)
            hour_counter[created.hour] += 1
            dates.add(created.date())

    # Top topics
    common_topics = [
        {"topic": topic, "frequency": count}
        for topic, count in topic_counter.most_common(10)
    ]

    # Active hours (top 5)
    active_hours = [
        {"hour": hour, "frequency": count}
        for hour, count in hour_counter.most_common(5)
    ]

    # Average memories per day
    day_count = max(len(dates), 1)
    avg_per_day = round(len(rows) / day_count, 1)

    return {
        "user_id": user_id,
        "patterns_detected": True,
        "common_topics": common_topics,
        "active_hours": active_hours,
        "avg_memories_per_day": avg_per_day,
        "total_memories_analyzed": len(rows),
        "date_range_days": day_count,
    }


async def detect_topic_cooccurrence(user_id: str) -> dict:
    """Find topic pairs that co-occur within sessions or follow each other.

    Uses memory_relationships (FOLLOWED_BY, SAME_SESSION) + memory_topics
    to discover which topics tend to appear together.
    """
    db = await get_db()

    # Query topic pairs from related memories
    cursor = await db.execute("""
        SELECT mt_src.topic_name AS src_topic,
               mt_tgt.topic_name AS tgt_topic,
               COUNT(*) AS cnt
        FROM memory_relationships mr
        JOIN memory_topics mt_src ON mt_src.memory_id = mr.source_id
        JOIN memory_topics mt_tgt ON mt_tgt.memory_id = mr.target_id
        WHERE mr.relationship_type IN ('FOLLOWED_BY', 'SAME_SESSION', 'SAME_TOPIC')
          AND mt_src.topic_name != mt_tgt.topic_name
        GROUP BY mt_src.topic_name, mt_tgt.topic_name
        HAVING COUNT(*) >= 2
        ORDER BY cnt DESC
        LIMIT 30
    """)
    rows = await cursor.fetchall()

    cooccurrences = []
    for row in rows:
        cooccurrences.append({
            "topic_a": row[0],
            "topic_b": row[1],
            "count": row[2],
        })

    # Also check topic co-occurrence within same memory (multiple tags)
    cursor2 = await db.execute("""
        SELECT mt1.topic_name, mt2.topic_name, COUNT(*) as cnt
        FROM memory_topics mt1
        JOIN memory_topics mt2 ON mt1.memory_id = mt2.memory_id
        WHERE mt1.topic_name < mt2.topic_name
        GROUP BY mt1.topic_name, mt2.topic_name
        HAVING COUNT(*) >= 3
        ORDER BY cnt DESC
        LIMIT 20
    """)
    rows2 = await cursor2.fetchall()

    for row in rows2:
        cooccurrences.append({
            "topic_a": row[0],
            "topic_b": row[1],
            "count": row[2],
        })

    return {
        "user_id": user_id,
        "cooccurrences": cooccurrences,
        "total_pairs": len(cooccurrences),
    }


async def detect_time_topic_patterns(user_id: str) -> dict:
    """Map hour-of-day to most common topics.

    Returns which topics are most active at each hour, enabling
    time-based predictive priming.
    """
    db = await get_db()

    cursor = await db.execute("""
        SELECT topic_tags, created_at
        FROM episodic_memories
        WHERE user_id = ? AND NOT is_decayed AND topic_tags != '[]'
        ORDER BY created_at DESC
        LIMIT 200
    """, (user_id,))
    rows = await cursor.fetchall()

    # hour -> Counter of topics
    hour_topics: dict[int, Counter] = {}

    for row in rows:
        tags_raw = row[0] if row[0] else "[]"
        created = row[1]

        if isinstance(tags_raw, str):
            try:
                tags = json.loads(tags_raw)
            except (json.JSONDecodeError, TypeError):
                continue
        else:
            tags = tags_raw

        if not created or not tags:
            continue

        if isinstance(created, str):
            try:
                created = datetime.fromisoformat(created)
            except ValueError:
                continue

        hour = created.hour
        if hour not in hour_topics:
            hour_topics[hour] = Counter()
        for tag in tags:
            hour_topics[hour][tag] += 1

    # Build result: top 3 topics per hour
    result_hours = {}
    for hour, counter in sorted(hour_topics.items()):
        result_hours[hour] = [
            {"topic": t, "count": c} for t, c in counter.most_common(3)
        ]

    return {
        "user_id": user_id,
        "hour_topics": result_hours,
        "hours_with_data": len(result_hours),
    }
