aidaemon 0.11.5

//! Comprehensive tests for the aidaemon memory system.
//!
//! Organized by subsystem:
//! A. Fact canonical key normalization
//! B. Fact edge cases (special chars, long values, empty values)
//! C. Fact supersession chains
//! D. Fact privacy & channel scoping (deep tests)
//! E. Fact retrieval & search edge cases
//! F. Episode management
//! G. Procedures & error solutions (deeper coverage)
//! H. Behavior patterns
//! I. People intelligence CRUD
//! J. Memory decay
//! K. Retention cleanup
//! L. Concurrent / race conditions
//! M. Context window (additional edge cases)

use crate::memory::embeddings::EmbeddingService;
use crate::state::sqlite::SqliteStateStore;
use crate::traits::store_prelude::*;
use crate::traits::{BehaviorPattern, Episode, ErrorSolution, Person, Procedure};
use crate::types::FactPrivacy;
use chrono::{Duration, Utc};
use std::collections::HashMap;
use std::sync::Arc;

async fn setup_test_store() -> (SqliteStateStore, tempfile::NamedTempFile) {
    let db_file = tempfile::NamedTempFile::new().unwrap();
    let embedding_service = Arc::new(EmbeddingService::new().unwrap());
    let store = SqliteStateStore::new(
        db_file.path().to_str().unwrap(),
        100,
        None,
        embedding_service,
    )
    .await
    .unwrap();
    (store, db_file)
}

// ==================== A. Canonical Key Normalization ====================

/// Verify that canonicalize_fact_key normalizes "Dog Name" to "dog_name",
/// preventing duplicate facts from key variants.
#[tokio::test]
async fn test_canonical_key_prevents_duplicates() {
    let (store, _db) = setup_test_store().await;

    // Insert with "Dog Name"
    store
        .upsert_fact(
            "user",
            "Dog Name",
            "Bella",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // Upsert with "dog_name" — should supersede, not create duplicate
    store
        .upsert_fact("user", "dog_name", "Max", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    // Only one active fact should exist
    let facts = store.get_facts(Some("user")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(
        active.len(),
        1,
        "Should have exactly one active fact after canonical key match"
    );
    assert_eq!(active[0].value, "Max", "Latest value should be Max");
}

/// Verify that "DOG NAME", "dog name", "Dog_Name" all map to same canonical key.
#[tokio::test]
async fn test_canonical_key_case_insensitive_variants() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "DOG NAME", "V1", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "dog name", "V2", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "Dog_Name", "V3", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(
        active.len(),
        1,
        "All key variants should resolve to same canonical key"
    );
    assert_eq!(active[0].value, "V3");
}

/// Verify that apostrophe creates a separator in canonical key, so "dog's" != "dogs".
/// This is correct behavior: "my-dog's-name" → "my_dog_s_name" but "my dogs name" → "my_dogs_name".
#[tokio::test]
async fn test_canonical_key_punctuation_normalization() {
    let (store, _db) = setup_test_store().await;

    // "my-dog's-name" canonicalizes to "my_dog_s_name" (apostrophe = separator)
    store
        .upsert_fact(
            "user",
            "my-dog's-name",
            "Bella",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();
    // "my dogs name" canonicalizes to "my_dogs_name" (different from above)
    store
        .upsert_fact(
            "user",
            "my dogs name",
            "Max",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // These are DIFFERENT canonical keys, so both should be active
    let facts = store.get_facts(Some("user")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(
        active.len(),
        2,
        "Different canonical keys should create separate facts"
    );

    // But keys that differ only in separator type should match:
    // "my-dogs-name" → "my_dogs_name" same as "my dogs name" → "my_dogs_name"
    store
        .upsert_fact(
            "user",
            "my-dogs-name",
            "Cooper",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(
        active.len(),
        2,
        "my-dogs-name should supersede my dogs name (same canonical key)"
    );
    // The "my dogs name" fact should now have value "Cooper" (superseded by "my-dogs-name")
    let dogs_fact = active.iter().find(|f| {
        // Replicate canonical key logic: lowercase, non-alphanumeric → '_', collapse underscores
        let canon: String = f
            .key
            .to_lowercase()
            .chars()
            .map(|c| if c.is_alphanumeric() { c } else { '_' })
            .collect();
        let canon = canon.trim_matches('_').to_string();
        // Collapse consecutive underscores
        let mut prev = false;
        let canon: String = canon
            .chars()
            .filter(|&c| {
                if c == '_' {
                    if prev {
                        return false;
                    }
                    prev = true;
                } else {
                    prev = false;
                }
                true
            })
            .collect();
        canon == "my_dogs_name"
    });
    assert!(
        dogs_fact.is_some(),
        "Should have a fact with canonical key my_dogs_name"
    );
    assert_eq!(dogs_fact.unwrap().value, "Cooper");
}

// ==================== B. Fact Edge Cases ====================

/// Verify that a fact with an empty string value stores and retrieves correctly.
#[tokio::test]
async fn test_fact_empty_value() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "empty_field", "", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(facts.len(), 1);
    assert_eq!(facts[0].value, "");
}

/// Verify that a fact with a very long value (10KB) stores and retrieves without corruption.
#[tokio::test]
async fn test_fact_long_value() {
    let (store, _db) = setup_test_store().await;
    let long_value = "x".repeat(10_000);

    store
        .upsert_fact(
            "user",
            "long_val",
            &long_value,
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(facts.len(), 1);
    assert_eq!(facts[0].value.len(), 10_000);
    assert_eq!(facts[0].value, long_value);
}

/// Verify that Unicode, emoji, newlines, and quotes store correctly.
#[tokio::test]
async fn test_fact_special_characters() {
    let (store, _db) = setup_test_store().await;

    let test_cases = vec![
        ("unicode_name", "محمد", "Arabic name"),
        ("japanese_project", "プロジェクト光", "Japanese"),
        ("emoji_mood", "😀=great, 😐=okay, 😢=bad", "Emoji"),
        ("newlines", "line1\nline2\nline3", "Newlines"),
        ("quotes", r#"She said "hello" and it's fine"#, "Quotes"),
        (
            "sql_injection",
            "'; DROP TABLE facts; --",
            "SQL injection attempt",
        ),
    ];

    for (key, value, _label) in &test_cases {
        store
            .upsert_fact("test", key, value, "test", None, FactPrivacy::Global)
            .await
            .unwrap();
    }

    let facts = store.get_facts(Some("test")).await.unwrap();
    assert_eq!(
        facts.len(),
        test_cases.len(),
        "All special char facts should be stored"
    );

    for (key, value, label) in &test_cases {
        let found = facts.iter().find(|f| f.key == *key);
        assert!(found.is_some(), "{} fact not found", label);
        assert_eq!(found.unwrap().value, *value, "{} value corrupted", label);
    }
}

/// Verify fact source tracking — different sources stored correctly.
#[tokio::test]
async fn test_fact_source_tracking() {
    let (store, _db) = setup_test_store().await;

    let sources = vec![
        ("consolidation_fact", "consolidation"),
        ("progressive_fact", "progressive"),
        ("task_learning_fact", "task_learning"),
        ("manual_fact", "manual"),
    ];

    for (key, source) in &sources {
        store
            .upsert_fact("user", key, "value", source, None, FactPrivacy::Global)
            .await
            .unwrap();
    }

    let facts = store.get_facts(Some("user")).await.unwrap();
    for (key, source) in &sources {
        let found = facts.iter().find(|f| f.key == *key).unwrap();
        assert_eq!(found.source, *source, "Source mismatch for key {}", key);
    }
}

/// Verify bulk insertion of 100 facts — no silent drops.
#[tokio::test]
async fn test_bulk_fact_insertion() {
    let (store, _db) = setup_test_store().await;

    for i in 0..100 {
        store
            .upsert_fact(
                "bulk",
                &format!("key_{}", i),
                &format!("value_{}", i),
                "test",
                None,
                FactPrivacy::Global,
            )
            .await
            .unwrap();
    }

    let facts = store.get_facts(Some("bulk")).await.unwrap();
    assert_eq!(facts.len(), 100, "All 100 facts should be stored");
}

// ==================== C. Fact Supersession Chains ====================

/// Verify supersession chain: A→B→C, only C is active.
#[tokio::test]
async fn test_supersession_chain() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "pet", "cat", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "pet", "dog", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "pet", "hamster", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    // get_facts returns only active (non-superseded) facts
    let active_facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(active_facts.len(), 1, "Only one fact should be active");
    assert_eq!(
        active_facts[0].value, "hamster",
        "Latest value should be active"
    );

    // get_fact_history returns ALL versions including superseded
    let history = store.get_fact_history("user", "pet").await.unwrap();
    assert_eq!(history.len(), 3, "Should have 3 total versions in history");
    let superseded: Vec<_> = history
        .iter()
        .filter(|f| f.superseded_at.is_some())
        .collect();
    assert_eq!(superseded.len(), 2, "Two facts should be superseded");
}

/// Verify that upserting with same value doesn't create a new row.
#[tokio::test]
async fn test_upsert_same_value_no_supersession() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "stable",
            "unchanged",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();
    store
        .upsert_fact(
            "user",
            "stable",
            "unchanged",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(
        facts.len(),
        1,
        "Same value upsert should not create supersession history"
    );
    assert!(facts[0].superseded_at.is_none());
}

/// Verify get_all_active_facts count after mixed inserts and supersessions.
#[tokio::test]
async fn test_active_fact_count() {
    let (store, _db) = setup_test_store().await;

    // Insert 10 facts
    for i in 0..10 {
        store
            .upsert_fact(
                "user",
                &format!("k{}", i),
                &format!("v{}", i),
                "test",
                None,
                FactPrivacy::Global,
            )
            .await
            .unwrap();
    }

    // Supersede 3 of them
    for i in 0..3 {
        store
            .upsert_fact(
                "user",
                &format!("k{}", i),
                &format!("new_v{}", i),
                "test",
                None,
                FactPrivacy::Global,
            )
            .await
            .unwrap();
    }

    let facts = store.get_facts(Some("user")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(
        active.len(),
        10,
        "Should still have 10 active facts (superseded ones replaced)"
    );
}

// ==================== D. Fact Privacy & Channel Scoping ====================

/// Verify channel-scoped facts are isolated between channels.
#[tokio::test]
async fn test_channel_scoped_fact_isolation_deep() {
    let (store, _db) = setup_test_store().await;

    // Insert same key in different channels
    store
        .upsert_fact(
            "project",
            "current_task",
            "fix bug",
            "test",
            Some("telegram:123"),
            FactPrivacy::Channel,
        )
        .await
        .unwrap();
    store
        .upsert_fact(
            "project",
            "current_task",
            "deploy app",
            "test",
            Some("slack:456"),
            FactPrivacy::Channel,
        )
        .await
        .unwrap();

    // Both should be active — they're in different channels
    let facts = store.get_facts(Some("project")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    // Note: upsert_fact doesn't check channel_id for matching — it matches by category+key.
    // So the second upsert will supersede the first regardless of channel.
    // This is a design assumption test.
    assert!(!active.is_empty(), "At least one active fact should exist");
}

/// Verify Global facts are accessible from any context.
#[tokio::test]
async fn test_global_fact_accessible_everywhere() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "name", "Alice", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    // Should be retrievable with any channel context
    let facts_no_channel = store
        .get_relevant_facts_for_channel(
            "name",
            10,
            None,
            crate::types::ChannelVisibility::Private,
            true,
        )
        .await
        .unwrap();

    let facts_telegram = store
        .get_relevant_facts_for_channel(
            "name",
            10,
            Some("telegram:123"),
            crate::types::ChannelVisibility::Private,
            true,
        )
        .await
        .unwrap();

    // Global facts should be accessible from both contexts
    assert!(
        !facts_no_channel.is_empty() || !facts_telegram.is_empty(),
        "Global fact should be accessible from any channel"
    );
}

/// Verify Private facts are only accessible in OwnerDm context.
#[tokio::test]
async fn test_private_fact_dm_only() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "ssn",
            "123-45-6789",
            "test",
            Some("telegram:owner"),
            FactPrivacy::Private,
        )
        .await
        .unwrap();

    // Should be visible in OwnerDm
    let dm_facts = store
        .get_relevant_facts_for_channel(
            "ssn",
            10,
            Some("telegram:owner"),
            crate::types::ChannelVisibility::Private,
            true,
        )
        .await
        .unwrap();

    // Should NOT be visible in PublicExternal
    let public_facts = store
        .get_relevant_facts_for_channel(
            "ssn",
            10,
            Some("telegram:group"),
            crate::types::ChannelVisibility::PublicExternal,
            false,
        )
        .await
        .unwrap();

    // Private facts should be in DM results
    let has_ssn_dm = dm_facts.iter().any(|f| f.key == "ssn");
    let has_ssn_public = public_facts.iter().any(|f| f.key == "ssn");

    assert!(has_ssn_dm, "Private fact should be accessible in OwnerDm");
    assert!(
        !has_ssn_public,
        "Private fact should NOT be accessible in PublicExternal"
    );
}

/// Security regression: a non-owner (allowlisted Guest) DMing the bot must NOT
/// receive the owner's Private facts, even though a DM is `Private` visibility.
/// Only the owner gets the unfiltered graph in a DM.
#[tokio::test]
async fn test_private_fact_hidden_from_non_owner_dm() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "ssn",
            "123-45-6789",
            "test",
            Some("telegram:owner"),
            FactPrivacy::Private,
        )
        .await
        .unwrap();

    // Owner DM still sees the private fact (behavior preserved).
    let owner_dm = store
        .get_relevant_facts_for_channel(
            "ssn",
            10,
            Some("telegram:owner"),
            crate::types::ChannelVisibility::Private,
            true,
        )
        .await
        .unwrap();
    assert!(
        owner_dm.iter().any(|f| f.key == "ssn"),
        "owner DM should still see their private fact"
    );

    // Non-owner DM (Guest) must NOT see the owner's private fact.
    let guest_dm = store
        .get_relevant_facts_for_channel(
            "ssn",
            10,
            Some("telegram:guest"),
            crate::types::ChannelVisibility::Private,
            false,
        )
        .await
        .unwrap();
    assert!(
        !guest_dm.iter().any(|f| f.key == "ssn"),
        "SECURITY: guest DM must NOT receive the owner's Private fact"
    );
}

/// Verify null channel_id legacy facts are accessible (backward compat).
#[tokio::test]
async fn test_null_channel_legacy_facts() {
    let (store, _db) = setup_test_store().await;

    // Insert with no channel_id (legacy)
    store
        .upsert_fact(
            "user",
            "legacy_pref",
            "vim",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(facts.len(), 1);
    assert!(
        facts[0].channel_id.is_none(),
        "Legacy fact should have null channel_id"
    );
}

// ==================== E. Fact Retrieval & Search Edge Cases ====================

/// Regression: explicit semantic search must surface a near-synonym match that
/// falls just under the passive-injection threshold. "spouse" embeds at ~0.28
/// against a stored `partner_name` fact — below the 0.30 passive cutoff but above
/// the 0.22 explicit-search cutoff — so `search_facts_semantic` (the user-initiated
/// tool path) must return it, even though passive injection might not.
#[tokio::test]
async fn test_explicit_search_catches_near_threshold_synonym() {
    let (store, _db) = setup_test_store().await;
    store
        .upsert_fact(
            "user",
            "partner_name",
            "Aracely Zambrano",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let hits = store.search_facts_semantic("spouse", 10).await.unwrap();
    assert!(
        hits.iter().any(|(f, _)| f.key == "partner_name"),
        "explicit semantic search should surface the partner fact for the synonym 'spouse'; got {:?}",
        hits.iter().map(|(f, _)| f.key.as_str()).collect::<Vec<_>>()
    );
}

/// Verify search with zero results doesn't crash.
#[tokio::test]
async fn test_search_zero_results() {
    let (store, _db) = setup_test_store().await;

    let facts = store
        .get_relevant_facts("nonexistent_topic_xyz123", 10)
        .await
        .unwrap();
    assert!(facts.is_empty(), "Should return empty for no matches");
}

/// Verify search with special characters doesn't cause SQL errors.
#[tokio::test]
async fn test_search_special_characters() {
    let (store, _db) = setup_test_store().await;

    // Insert a fact first so the search has something to scan
    store
        .upsert_fact("tech", "language", "C++", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    // These should not crash
    let _ = store.get_relevant_facts("C++", 10).await.unwrap();
    let _ = store.get_relevant_facts("it's", 10).await.unwrap();
    let _ = store.get_relevant_facts("café", 10).await.unwrap();
    let _ = store.get_relevant_facts("🔥", 10).await.unwrap();
}

/// Verify search with common stopwords only returns empty (all filtered).
#[tokio::test]
async fn test_search_stopwords_only() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "name", "Alice", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    // Pure stopwords query — lexical tokens should all be filtered
    let _facts = store.get_relevant_facts("the is a an", 10).await.unwrap();
    // This may still return results via embedding search, which is fine.
    // The key is no crash.
}

/// Verify Top-K limiting works.
#[tokio::test]
async fn test_search_top_k_limiting() {
    let (store, _db) = setup_test_store().await;

    for i in 0..20 {
        store
            .upsert_fact(
                "user",
                &format!("pref_{}", i),
                &format!("value_{}", i),
                "test",
                None,
                FactPrivacy::Global,
            )
            .await
            .unwrap();
    }

    let facts = store.get_relevant_facts("preference", 5).await.unwrap();
    assert!(
        facts.len() <= 5,
        "Should return at most 5 results, got {}",
        facts.len()
    );
}

/// Verify very long search query doesn't timeout or OOM.
#[tokio::test]
async fn test_search_very_long_query() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "name", "Alice", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    let long_query = "what is ".repeat(100); // ~800 chars
    let _ = store.get_relevant_facts(&long_query, 10).await.unwrap();
}

// ==================== F. Episode Management ====================

/// Verify episode creation and retrieval with all fields.
#[tokio::test]
async fn test_episode_full_lifecycle() {
    let (store, _db) = setup_test_store().await;

    let episode = Episode {
        id: 0,
        session_id: "test-session".to_string(),
        summary: "Debugged a race condition in the memory system".to_string(),
        topics: Some(vec!["debugging".to_string(), "concurrency".to_string()]),
        emotional_tone: Some("productive".to_string()),
        outcome: Some("resolved".to_string()),
        importance: 0.8,
        recall_count: 0,
        last_recalled_at: None,
        message_count: 15,
        start_time: Utc::now() - Duration::hours(2),
        end_time: Utc::now() - Duration::hours(1),
        created_at: Utc::now(),
        channel_id: Some("telegram:123".to_string()),
    };

    let id = store.insert_episode(&episode).await.unwrap();
    assert!(id > 0, "Episode ID should be positive");

    // Verify recall increment
    store.increment_episode_recall(id).await.unwrap();
    let recent = store.get_recent_episodes(1).await.unwrap();
    assert_eq!(recent.len(), 1);
    assert_eq!(recent[0].recall_count, 1);
}

/// Verify episode embedding generation and semantic search.
#[tokio::test]
async fn test_episode_embedding_and_search() {
    let (store, _db) = setup_test_store().await;

    let ep1 = Episode {
        id: 0,
        session_id: "rust-session".to_string(),
        summary: "Built a new REST API with Rust and Actix web framework".to_string(),
        topics: Some(vec!["rust".to_string(), "api".to_string()]),
        emotional_tone: Some("productive".to_string()),
        outcome: Some("resolved".to_string()),
        importance: 0.7,
        recall_count: 0,
        last_recalled_at: None,
        message_count: 10,
        start_time: Utc::now() - Duration::hours(3),
        end_time: Utc::now() - Duration::hours(2),
        created_at: Utc::now(),
        channel_id: None,
    };

    let ep2 = Episode {
        id: 0,
        session_id: "cooking-session".to_string(),
        summary: "Discussed recipes for Italian pasta and pizza making techniques".to_string(),
        topics: Some(vec!["cooking".to_string(), "italian".to_string()]),
        emotional_tone: Some("casual".to_string()),
        outcome: Some("resolved".to_string()),
        importance: 0.5,
        recall_count: 0,
        last_recalled_at: None,
        message_count: 8,
        start_time: Utc::now() - Duration::hours(5),
        end_time: Utc::now() - Duration::hours(4),
        created_at: Utc::now() - Duration::hours(1),
        channel_id: None,
    };

    let _id1 = store.insert_episode(&ep1).await.unwrap();
    let _id2 = store.insert_episode(&ep2).await.unwrap();

    // Backfill embeddings
    let backfilled = store.backfill_episode_embeddings().await.unwrap();
    assert_eq!(backfilled, 2, "Should backfill 2 episodes");

    // Search for rust-related content
    let results = store
        .get_relevant_episodes("Rust web development", 5)
        .await
        .unwrap();
    assert!(!results.is_empty(), "Should find episodes for Rust query");
    // First result should be the Rust episode (higher similarity)
    if results.len() >= 2 {
        assert_eq!(
            results[0].session_id, "rust-session",
            "Rust episode should rank higher for Rust query"
        );
    }
}

/// Verify duplicate episode prevention via INSERT OR IGNORE.
#[tokio::test]
async fn test_episode_duplicate_prevention() {
    let (store, _db) = setup_test_store().await;

    let ep = Episode {
        id: 0,
        session_id: "dup-session".to_string(),
        summary: "First summary".to_string(),
        topics: None,
        emotional_tone: None,
        outcome: None,
        importance: 0.5,
        recall_count: 0,
        last_recalled_at: None,
        message_count: 5,
        start_time: Utc::now() - Duration::hours(1),
        end_time: Utc::now(),
        created_at: Utc::now(),
        channel_id: None,
    };

    let id1 = store.insert_episode(&ep).await.unwrap();
    // Inserting a second episode for a different session should work fine
    let ep2 = Episode {
        session_id: "other-session".to_string(),
        summary: "Second summary".to_string(),
        ..ep.clone()
    };
    let id2 = store.insert_episode(&ep2).await.unwrap();
    assert_ne!(
        id1, id2,
        "Different sessions should get different episode IDs"
    );
}

// ==================== G. Procedures & Error Solutions ====================

/// Verify procedure keyed name uniqueness for different step sequences.
#[tokio::test]
async fn test_procedure_keyed_name_uniqueness() {
    use crate::memory::procedures::generate_procedure_keyed_name;

    let name1 =
        generate_procedure_keyed_name("deploy", &["step1".to_string(), "step2".to_string()]);
    let name2 =
        generate_procedure_keyed_name("deploy", &["step3".to_string(), "step4".to_string()]);
    let name3 =
        generate_procedure_keyed_name("deploy", &["step1".to_string(), "step2".to_string()]);

    assert_ne!(
        name1, name2,
        "Different steps should produce different keyed names"
    );
    assert_eq!(
        name1, name3,
        "Same steps should produce same keyed name (deterministic)"
    );
    assert!(
        name1.starts_with("deploy-"),
        "Keyed name should start with base name"
    );
}

/// Verify procedure creation with outcome tracking.
#[tokio::test]
async fn test_procedure_outcome_tracking() {
    let (store, _db) = setup_test_store().await;

    let proc = Procedure {
        id: 0,
        name: "test-proc-abc123".to_string(),
        trigger_pattern: "deploy the app".to_string(),
        steps: vec!["step1".to_string(), "step2".to_string()],
        success_count: 1,
        failure_count: 0,
        avg_duration_secs: None,
        last_used_at: None,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let proc_id = store.upsert_procedure(&proc).await.unwrap();
    assert!(proc_id > 0, "Procedure ID should be positive");

    // Record successes
    store
        .update_procedure_outcome(proc_id, true, None)
        .await
        .unwrap();
    store
        .update_procedure_outcome(proc_id, true, None)
        .await
        .unwrap();
    store
        .update_procedure_outcome(proc_id, false, None)
        .await
        .unwrap();

    let procs = store
        .get_relevant_procedures("deploy the app", 10)
        .await
        .unwrap();
    let p = procs.iter().find(|p| p.name == "test-proc-abc123").unwrap();
    assert_eq!(
        p.success_count, 3,
        "Should have 3 successes (1 initial + 2)"
    );
    assert_eq!(p.failure_count, 1, "Should have 1 failure");
}

/// Verify error solution CRUD and retrieval.
#[tokio::test]
async fn test_error_solution_crud() {
    let (store, _db) = setup_test_store().await;

    let sol = ErrorSolution {
        id: 0,
        error_pattern: "ModuleNotFoundError: No module named 'flask'".to_string(),
        domain: Some("python".to_string()),
        solution_summary: "Install flask with pip".to_string(),
        solution_steps: Some(vec!["pip install flask".to_string()]),
        success_count: 1,
        failure_count: 0,
        last_used_at: None,
        created_at: Utc::now(),
    };

    store.insert_error_solution(&sol).await.unwrap();

    // Search by error pattern
    let solutions = store
        .get_relevant_error_solutions("ModuleNotFoundError flask", 10)
        .await
        .unwrap();
    assert!(
        !solutions.is_empty(),
        "Should find error solution by error pattern"
    );
    let s = &solutions[0];
    assert_eq!(s.success_count, 1);
    assert_eq!(s.domain, Some("python".to_string()));
}

// ==================== H. Behavior Patterns ====================

/// Verify behavior pattern insertion and confidence update.
#[tokio::test]
async fn test_behavior_pattern_crud() {
    let (store, _db) = setup_test_store().await;

    store
        .record_behavior_pattern(
            "sequence",
            "After terminal often use web_search",
            Some("terminal"),
            Some("web_search"),
            0.3,
            3,
        )
        .await
        .unwrap();

    let patterns = store.get_behavior_patterns(0.0).await.unwrap();
    assert!(!patterns.is_empty(), "Should have at least one pattern");

    let p = patterns
        .iter()
        .find(|p| p.description.contains("terminal"))
        .unwrap();
    assert_eq!(p.pattern_type, "sequence");
    assert!(p.confidence > 0.0);
}

/// Verify behavior pattern confidence can be updated via update_behavior_pattern.
#[tokio::test]
async fn test_behavior_pattern_confidence_update() {
    let (store, _db) = setup_test_store().await;

    let pattern = BehaviorPattern {
        id: 0,
        pattern_type: "sequence".to_string(),
        description: "After grep often use terminal".to_string(),
        trigger_context: Some("grep".to_string()),
        action: Some("terminal".to_string()),
        confidence: 0.5,
        occurrence_count: 5,
        last_seen_at: Some(Utc::now()),
        created_at: Utc::now(),
    };

    let pattern_id = store.insert_behavior_pattern(&pattern).await.unwrap();

    // update_behavior_pattern increments occurrence_count and adds confidence_delta
    store
        .update_behavior_pattern(pattern_id, 0.3)
        .await
        .unwrap();

    let patterns = store.get_behavior_patterns(0.0).await.unwrap();
    let p = patterns.iter().find(|p| p.id == pattern_id).unwrap();
    assert!(
        (p.confidence - 0.8).abs() < 0.01,
        "Confidence should be 0.5 + 0.3 = 0.8, got {}",
        p.confidence
    );
    assert_eq!(
        p.occurrence_count, 6,
        "Occurrence count should be incremented"
    );
}

// ==================== I. People Intelligence CRUD ====================

/// Verify person creation and retrieval.
#[tokio::test]
async fn test_person_create_and_retrieve() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Alice Johnson".to_string(),
        aliases: vec!["AJ".to_string()],
        relationship: Some("coworker".to_string()),
        platform_ids: HashMap::new(),
        notes: Some("Works in engineering".to_string()),
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let id = store.upsert_person(&person).await.unwrap();
    assert!(id > 0, "Person ID should be positive");

    let retrieved = store.get_person(id).await.unwrap();
    assert!(retrieved.is_some(), "Should retrieve created person");
    let p = retrieved.unwrap();
    assert_eq!(p.name, "Alice Johnson");
    assert_eq!(p.relationship.as_deref(), Some("coworker"));
}

/// Verify find_person_by_name is case-insensitive.
#[tokio::test]
async fn test_person_find_by_name_case_insensitive() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Bob Smith".to_string(),
        aliases: vec![],
        relationship: None,
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    store.upsert_person(&person).await.unwrap();

    // Case-insensitive lookup
    let found = store.find_person_by_name("bob smith").await.unwrap();
    assert!(found.is_some(), "Should find person by lowercase name");
    assert_eq!(found.unwrap().name, "Bob Smith");

    let found_upper = store.find_person_by_name("BOB SMITH").await.unwrap();
    assert!(
        found_upper.is_some(),
        "Should find person by uppercase name"
    );
}

/// Verify person fact CRUD operations.
#[tokio::test]
async fn test_person_fact_crud() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Charlie Brown".to_string(),
        aliases: vec![],
        relationship: Some("friend".to_string()),
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let person_id = store.upsert_person(&person).await.unwrap();

    // Add facts
    store
        .upsert_person_fact(person_id, "work", "company", "Google", "manual", 1.0)
        .await
        .unwrap();
    store
        .upsert_person_fact(person_id, "personal", "birthday", "March 15", "manual", 0.9)
        .await
        .unwrap();

    // Retrieve all facts
    let facts = store.get_person_facts(person_id, None).await.unwrap();
    assert_eq!(facts.len(), 2, "Should have 2 person facts");

    // Retrieve filtered by category
    let work_facts = store
        .get_person_facts(person_id, Some("work"))
        .await
        .unwrap();
    assert_eq!(work_facts.len(), 1);
    assert_eq!(work_facts[0].value, "Google");

    // Delete a fact
    let fact_id = facts.iter().find(|f| f.key == "birthday").unwrap().id;
    store.delete_person_fact(fact_id).await.unwrap();

    let remaining = store.get_person_facts(person_id, None).await.unwrap();
    assert_eq!(remaining.len(), 1, "Should have 1 fact after deletion");
}

/// Verify platform ID linking and lookup.
#[tokio::test]
async fn test_person_platform_id_linking() {
    let (store, _db) = setup_test_store().await;

    let mut platform_ids = HashMap::new();
    platform_ids.insert("telegram:12345".to_string(), "alice_tg".to_string());

    let person = Person {
        id: 0,
        name: "Alice".to_string(),
        aliases: vec![],
        relationship: None,
        platform_ids,
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let person_id = store.upsert_person(&person).await.unwrap();

    // Link additional platform ID
    store
        .link_platform_id(person_id, "slack:U789", "alice_slack")
        .await
        .unwrap();

    // Look up by telegram platform ID
    let found_tg = store
        .get_person_by_platform_id("telegram:12345")
        .await
        .unwrap();
    assert!(
        found_tg.is_some(),
        "Should find person by telegram platform ID"
    );
    assert_eq!(found_tg.unwrap().name, "Alice");

    // Look up by slack platform ID
    let found_slack = store.get_person_by_platform_id("slack:U789").await.unwrap();
    assert!(
        found_slack.is_some(),
        "Should find person by slack platform ID"
    );
}

/// Verify interaction tracking increments correctly.
#[tokio::test]
async fn test_person_interaction_tracking() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Diana".to_string(),
        aliases: vec![],
        relationship: None,
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let person_id = store.upsert_person(&person).await.unwrap();

    // Track 5 interactions
    for _ in 0..5 {
        store.touch_person_interaction(person_id).await.unwrap();
    }

    let p = store.get_person(person_id).await.unwrap().unwrap();
    assert_eq!(p.interaction_count, 5, "Should have 5 interactions");
    assert!(
        p.last_interaction_at.is_some(),
        "last_interaction_at should be set"
    );
}

/// Verify person deletion cascades to person facts.
#[tokio::test]
async fn test_person_deletion_cascades() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Eve".to_string(),
        aliases: vec![],
        relationship: None,
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let person_id = store.upsert_person(&person).await.unwrap();
    store
        .upsert_person_fact(person_id, "work", "role", "engineer", "manual", 1.0)
        .await
        .unwrap();

    // Verify fact exists
    let facts_before = store.get_person_facts(person_id, None).await.unwrap();
    assert_eq!(facts_before.len(), 1);

    // Delete person
    store.delete_person(person_id).await.unwrap();

    // Person should be gone
    let p = store.get_person(person_id).await.unwrap();
    assert!(p.is_none(), "Person should be deleted");

    // Person facts should also be gone
    let facts_after = store.get_person_facts(person_id, None).await.unwrap();
    assert!(
        facts_after.is_empty(),
        "Person facts should be cascaded on delete"
    );
}

/// Verify person fact confirmation sets confidence to 1.0.
#[tokio::test]
async fn test_person_fact_confirmation() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Frank".to_string(),
        aliases: vec![],
        relationship: None,
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let person_id = store.upsert_person(&person).await.unwrap();
    store
        .upsert_person_fact(person_id, "work", "company", "Acme", "consolidation", 0.7)
        .await
        .unwrap();

    let facts = store.get_person_facts(person_id, None).await.unwrap();
    let fact_id = facts[0].id;
    assert!(
        (facts[0].confidence - 0.7).abs() < 0.01,
        "Initial confidence should be 0.7"
    );

    // Confirm the fact
    store.confirm_person_fact(fact_id).await.unwrap();

    let facts_after = store.get_person_facts(person_id, None).await.unwrap();
    assert!(
        (facts_after[0].confidence - 1.0).abs() < 0.01,
        "Confirmed fact should have confidence 1.0, got {}",
        facts_after[0].confidence
    );
}

// ==================== J. Memory Decay ====================

/// Verify fact recall count decay for old unreferred facts.
#[tokio::test]
async fn test_fact_recall_decay() {
    let (store, _db) = setup_test_store().await;

    // Insert a fact
    store
        .upsert_fact("user", "old_pref", "vim", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    // Manually bump recall count
    let facts = store.get_facts(Some("user")).await.unwrap();
    let fact_id = facts[0].id;
    store.increment_fact_recall(fact_id).await.unwrap();
    store.increment_fact_recall(fact_id).await.unwrap();
    store.increment_fact_recall(fact_id).await.unwrap();

    // Verify recall count is 3
    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(facts[0].recall_count, 3);

    // Set last_recalled_at to 31 days ago to trigger decay
    let old_date = (Utc::now() - Duration::days(31)).to_rfc3339();
    sqlx::query("UPDATE facts SET last_recalled_at = ? WHERE id = ?")
        .bind(&old_date)
        .bind(fact_id)
        .execute(&store.pool())
        .await
        .unwrap();

    // Run decay
    let pool = store.pool();
    let cutoff = (Utc::now() - Duration::days(30)).to_rfc3339();
    sqlx::query(
        "UPDATE facts SET recall_count = MAX(0, recall_count - 1) \
         WHERE recall_count > 0 AND (last_recalled_at IS NULL OR last_recalled_at < ?)",
    )
    .bind(&cutoff)
    .execute(&pool)
    .await
    .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(
        facts[0].recall_count, 2,
        "Decay should reduce recall_count by 1"
    );
}

/// Verify recall_count=0 doesn't go negative after decay.
#[tokio::test]
async fn test_decay_no_negative_recall() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "zero_recall",
            "val",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // Recall count starts at 0, run decay
    let pool = store.pool();
    let cutoff = (Utc::now() - Duration::days(30)).to_rfc3339();
    sqlx::query(
        "UPDATE facts SET recall_count = MAX(0, recall_count - 1) \
         WHERE recall_count > 0 AND (last_recalled_at IS NULL OR last_recalled_at < ?)",
    )
    .bind(&cutoff)
    .execute(&pool)
    .await
    .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(
        facts[0].recall_count, 0,
        "Recall count should not go negative"
    );
}

/// Verify behavior pattern confidence decay.
#[tokio::test]
async fn test_behavior_pattern_confidence_decay() {
    let (store, _db) = setup_test_store().await;

    let pattern = BehaviorPattern {
        id: 0,
        pattern_type: "sequence".to_string(),
        description: "After X often use Y".to_string(),
        trigger_context: Some("tool_x".to_string()),
        action: Some("tool_y".to_string()),
        confidence: 0.5,
        occurrence_count: 5,
        last_seen_at: Some(Utc::now() - Duration::days(31)),
        created_at: Utc::now() - Duration::days(60),
    };

    store.insert_behavior_pattern(&pattern).await.unwrap();

    // Simulate decay: reduce confidence by 0.05 for patterns not seen in 30 days
    let pool = store.pool();
    let cutoff = (Utc::now() - Duration::days(30)).to_rfc3339();
    sqlx::query(
        "UPDATE behavior_patterns SET confidence = MAX(0.1, confidence - 0.05) \
         WHERE confidence > 0.1 AND (last_seen_at IS NULL OR last_seen_at < ?)",
    )
    .bind(&cutoff)
    .execute(&pool)
    .await
    .unwrap();

    let patterns = store.get_behavior_patterns(0.0).await.unwrap();
    let p = patterns
        .iter()
        .find(|p| p.trigger_context.as_deref() == Some("tool_x"))
        .unwrap();
    assert!(
        (p.confidence - 0.45).abs() < 0.01,
        "Confidence should decay from 0.5 to 0.45, got {}",
        p.confidence
    );
}

// ==================== K. Retention Cleanup ====================

/// Verify retention cleanup of superseded facts older than threshold.
#[tokio::test]
async fn test_retention_superseded_facts_cleanup() {
    let (store, _db) = setup_test_store().await;

    // Insert and supersede a fact
    store
        .upsert_fact(
            "user",
            "old_key",
            "old_val",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();
    store
        .upsert_fact(
            "user",
            "old_key",
            "new_val",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // Set superseded_at to 200 days ago
    let old_date = (Utc::now() - Duration::days(200)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE facts SET superseded_at = ? WHERE superseded_at IS NOT NULL")
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    // Run cleanup with 90-day threshold
    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool.clone(), config);
    let stats = retention.run_all().await.unwrap();

    assert!(
        stats.facts_deleted > 0,
        "Should have cleaned up old superseded facts"
    );

    // Active fact should still exist
    let facts = store.get_facts(Some("user")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(active.len(), 1, "Active fact should be preserved");
    assert_eq!(active[0].value, "new_val");
}

/// Verify retention preserves active (non-superseded) facts.
#[tokio::test]
async fn test_retention_preserves_active_facts() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "keep_me",
            "important",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // Make it old
    let old_date = (Utc::now() - Duration::days(300)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE facts SET created_at = ?, updated_at = ? WHERE superseded_at IS NULL")
        .bind(&old_date)
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool.clone(), config);
    let _stats = retention.run_all().await.unwrap();

    // Active fact should NOT be deleted
    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(
        facts.len(),
        1,
        "Active fact should be preserved even if old"
    );
    assert_eq!(facts[0].value, "important");
}

/// Verify retention cleanup of unreferenced episodes.
#[tokio::test]
async fn test_retention_unreferenced_episodes_cleanup() {
    let (store, _db) = setup_test_store().await;

    // Insert episode with recall_count=0
    let ep = Episode {
        id: 0,
        session_id: "old-session".to_string(),
        summary: "Old unreferenced episode".to_string(),
        topics: None,
        emotional_tone: None,
        outcome: None,
        importance: 0.3,
        recall_count: 0,
        last_recalled_at: None,
        message_count: 5,
        start_time: Utc::now() - Duration::days(400),
        end_time: Utc::now() - Duration::days(400),
        created_at: Utc::now() - Duration::days(400),
        channel_id: None,
    };
    store.insert_episode(&ep).await.unwrap();

    // Make it old enough for retention
    let old_date = (Utc::now() - Duration::days(400)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE episodes SET created_at = ?")
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool.clone(), config);
    let stats = retention.run_all().await.unwrap();

    assert!(
        stats.episodes_deleted > 0,
        "Should have cleaned up old unreferenced episode"
    );
}

/// Verify retention preserves recalled episodes.
#[tokio::test]
async fn test_retention_preserves_recalled_episodes() {
    let (store, _db) = setup_test_store().await;

    let ep = Episode {
        id: 0,
        session_id: "recalled-session".to_string(),
        summary: "Well-recalled episode".to_string(),
        topics: None,
        emotional_tone: None,
        outcome: None,
        importance: 0.7,
        recall_count: 5,
        last_recalled_at: Some(Utc::now()),
        message_count: 10,
        start_time: Utc::now() - Duration::days(400),
        end_time: Utc::now() - Duration::days(400),
        created_at: Utc::now() - Duration::days(400),
        channel_id: None,
    };
    let _ep_id = store.insert_episode(&ep).await.unwrap();

    // Make it old
    let old_date = (Utc::now() - Duration::days(400)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE episodes SET created_at = ?")
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool.clone(), config);
    let _stats = retention.run_all().await.unwrap();

    // Recalled episode should survive
    let episodes = store.get_recent_episodes(10).await.unwrap();
    assert!(
        episodes.iter().any(|e| e.session_id == "recalled-session"),
        "Recalled episode should be preserved"
    );
}

/// Verify retention cleanup of zero-success procedures.
#[tokio::test]
async fn test_retention_zero_success_procedures_cleanup() {
    let (store, _db) = setup_test_store().await;

    let proc = Procedure {
        id: 0,
        name: "failed-proc-xyz".to_string(),
        trigger_pattern: "do something bad".to_string(),
        steps: vec!["step1".to_string()],
        success_count: 0,
        failure_count: 3,
        avg_duration_secs: None,
        last_used_at: None,
        created_at: Utc::now() - Duration::days(200),
        updated_at: Utc::now() - Duration::days(200),
    };

    store.upsert_procedure(&proc).await.unwrap();

    // Make it old
    let old_date = (Utc::now() - Duration::days(200)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE procedures SET created_at = ?, updated_at = ?")
        .bind(&old_date)
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool.clone(), config);
    let stats = retention.run_all().await.unwrap();

    assert!(
        stats.procedures_deleted > 0,
        "Should have cleaned up zero-success procedure"
    );
}

/// Verify retention preserves successful procedures.
#[tokio::test]
async fn test_retention_preserves_successful_procedures() {
    let (store, _db) = setup_test_store().await;

    let proc = Procedure {
        id: 0,
        name: "good-proc-abc".to_string(),
        trigger_pattern: "deploy correctly".to_string(),
        steps: vec!["step1".to_string(), "step2".to_string()],
        success_count: 10,
        failure_count: 1,
        avg_duration_secs: None,
        last_used_at: None,
        created_at: Utc::now() - Duration::days(200),
        updated_at: Utc::now() - Duration::days(200),
    };

    store.upsert_procedure(&proc).await.unwrap();

    // Make it old
    let old_date = (Utc::now() - Duration::days(200)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE procedures SET created_at = ?, updated_at = ?")
        .bind(&old_date)
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool.clone(), config);
    retention.run_all().await.unwrap();

    let procs = store
        .get_relevant_procedures("deploy correctly", 10)
        .await
        .unwrap();
    assert!(
        procs.iter().any(|p| p.name == "good-proc-abc"),
        "Successful procedure should be preserved"
    );
}

// ==================== L. Concurrent / Race Conditions ====================

/// Verify concurrent fact upserts with same key don't cause DB errors.
#[tokio::test]
async fn test_concurrent_fact_upserts() {
    let (store, _db) = setup_test_store().await;
    let store = Arc::new(store);

    let mut handles = vec![];
    for i in 0..10 {
        let s = store.clone();
        handles.push(tokio::spawn(async move {
            s.upsert_fact(
                "concurrent",
                "shared_key",
                &format!("value_{}", i),
                "test",
                None,
                FactPrivacy::Global,
            )
            .await
        }));
    }

    // All should succeed (no DB errors)
    for handle in handles {
        let result = handle.await.unwrap();
        assert!(
            result.is_ok(),
            "Concurrent upsert should not error: {:?}",
            result.err()
        );
    }

    // Exactly one active fact should remain
    let facts = store.get_facts(Some("concurrent")).await.unwrap();
    let active: Vec<_> = facts.iter().filter(|f| f.superseded_at.is_none()).collect();
    assert_eq!(
        active.len(),
        1,
        "Exactly one active fact should remain after concurrent upserts"
    );
}

/// Verify SQL injection attempt is safely handled via parameterized queries.
#[tokio::test]
async fn test_sql_injection_safe() {
    let (store, _db) = setup_test_store().await;

    // SQL injection attempt in value
    store
        .upsert_fact(
            "user",
            "injection_test",
            "'; DROP TABLE facts; --",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // Verify the fact stored literally
    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(facts.len(), 1, "Facts table should still exist");
    assert_eq!(facts[0].value, "'; DROP TABLE facts; --");

    // SQL injection attempt in key
    store
        .upsert_fact(
            "user",
            "key'; DROP TABLE facts; --",
            "safe_value",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    // Table should still work
    let facts = store.get_facts(Some("user")).await.unwrap();
    assert!(
        facts.len() >= 2,
        "Should have both facts, table survived injection attempt"
    );
}

/// Verify database migration creates all tables on fresh DB.
#[tokio::test]
async fn test_fresh_db_migration() {
    let (store, _db) = setup_test_store().await;

    // All basic operations should work on a fresh database
    let facts = store.get_facts(None).await.unwrap();
    assert!(facts.is_empty(), "Fresh DB should have no facts");

    let episodes = store.get_recent_episodes(10).await.unwrap();
    assert!(episodes.is_empty(), "Fresh DB should have no episodes");

    let procs = store.get_relevant_procedures("anything", 10).await.unwrap();
    assert!(procs.is_empty(), "Fresh DB should have no procedures");

    let patterns = store.get_behavior_patterns(0.0).await.unwrap();
    assert!(patterns.is_empty(), "Fresh DB should have no patterns");

    let people = store.get_all_people().await.unwrap();
    assert!(people.is_empty(), "Fresh DB should have no people");
}

// ==================== M. Context Window & Scoring ====================

/// Verify the scoring module gives higher scores to messages with keywords.
#[tokio::test]
async fn test_message_scoring_keywords() {
    use crate::memory::scoring::score_message;
    use crate::traits::Message;

    let mut msg = Message {
        id: "1".into(),
        session_id: "s1".into(),
        role: "user".into(),
        content: Some("remember that my password is secret123".into()),
        tool_call_id: None,
        tool_name: None,
        tool_calls_json: None,
        created_at: Utc::now(),
        importance: 0.0,
        ..Message::runtime_defaults()
    };

    let score_high = score_message(&msg);

    msg.content = Some("I just walked around the block for some exercise today".into());
    let score_medium = score_message(&msg);

    assert!(
        score_high > score_medium,
        "Message with 'password' keyword should score higher than casual message"
    );
}

/// Verify user profile defaults and update.
#[tokio::test]
async fn test_user_profile_defaults() {
    let (store, _db) = setup_test_store().await;

    let profile = store.get_user_profile().await.unwrap();
    assert_eq!(profile.verbosity_preference, "medium");
    assert_eq!(profile.tone_preference, "neutral");
    assert!(profile.prefers_explanations);
}

/// Verify user profile can be updated.
#[tokio::test]
async fn test_user_profile_update() {
    let (store, _db) = setup_test_store().await;

    let mut profile = store.get_user_profile().await.unwrap();
    profile.verbosity_preference = "brief".to_string();
    profile.emoji_preference = "frequent".to_string();
    store.update_user_profile(&profile).await.unwrap();

    let updated = store.get_user_profile().await.unwrap();
    assert_eq!(updated.verbosity_preference, "brief");
    assert_eq!(updated.emoji_preference, "frequent");
}

/// Verify fact history retrieval (all versions including superseded).
#[tokio::test]
async fn test_fact_history_retrieval() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "color", "blue", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "color", "green", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "color", "purple", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    let history = store.get_fact_history("user", "color").await.unwrap();
    assert_eq!(history.len(), 3, "History should have all 3 versions");

    // Most recent first (ORDER BY created_at DESC)
    assert_eq!(history[0].value, "purple", "Most recent should be first");
    assert!(
        history[0].superseded_at.is_none(),
        "Latest should not be superseded"
    );
    assert!(
        history[1].superseded_at.is_some(),
        "Middle should be superseded"
    );
    assert!(
        history[2].superseded_at.is_some(),
        "Oldest should be superseded"
    );
}

/// Verify get_facts returns only active (non-superseded) facts.
#[tokio::test]
async fn test_get_facts_only_active() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "city", "NYC", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "city", "LA", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact("user", "country", "USA", "test", None, FactPrivacy::Global)
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    for f in &facts {
        assert!(
            f.superseded_at.is_none(),
            "get_facts should only return active facts, but got superseded fact: {} = {}",
            f.key,
            f.value
        );
    }
    assert_eq!(
        facts.len(),
        2,
        "Should have 2 active facts (city=LA, country=USA)"
    );
}

/// Verify retention on empty database doesn't crash.
#[tokio::test]
async fn test_retention_empty_database() {
    let (store, _db) = setup_test_store().await;

    let pool = store.pool();
    let config = crate::config::RetentionConfig::default();
    let retention = crate::memory::retention::RetentionManager::new(pool, config);
    let stats = retention.run_all().await.unwrap();

    assert_eq!(stats.total_deleted(), 0, "Nothing to delete on empty DB");
}

/// Verify error solution retrieval with multiple domains.
#[tokio::test]
async fn test_error_solution_multiple_domains() {
    let (store, _db) = setup_test_store().await;

    let sol1 = ErrorSolution {
        id: 0,
        error_pattern: "ImportError: No module named 'requests'".to_string(),
        domain: Some("python".to_string()),
        solution_summary: "pip install requests".to_string(),
        solution_steps: Some(vec!["pip install requests".to_string()]),
        success_count: 5,
        failure_count: 0,
        last_used_at: None,
        created_at: Utc::now(),
    };

    let sol2 = ErrorSolution {
        id: 0,
        error_pattern: "error[E0382]: borrow of moved value".to_string(),
        domain: Some("rust".to_string()),
        solution_summary: "Clone or use references".to_string(),
        solution_steps: Some(vec!["Use .clone() or borrow with &".to_string()]),
        success_count: 3,
        failure_count: 1,
        last_used_at: None,
        created_at: Utc::now(),
    };

    store.insert_error_solution(&sol1).await.unwrap();
    store.insert_error_solution(&sol2).await.unwrap();

    // Search for python-related error
    let python_results = store
        .get_relevant_error_solutions("ImportError module requests", 10)
        .await
        .unwrap();
    assert!(
        !python_results.is_empty(),
        "Should find python error solution"
    );

    // Search for rust-related error
    let rust_results = store
        .get_relevant_error_solutions("borrow of moved value", 10)
        .await
        .unwrap();
    assert!(!rust_results.is_empty(), "Should find rust error solution");
}

/// Verify procedure extraction from messages.
#[tokio::test]
async fn test_procedure_extraction_helpers() {
    use crate::memory::procedures::{
        extract_error_pattern, extract_trigger_pattern, generalize_procedure,
        generate_procedure_name,
    };

    // Test procedure name generation
    assert_eq!(
        generate_procedure_name("Build the Rust project"),
        "rust-build"
    );
    assert_eq!(generate_procedure_name("Run the test suite"), "run-tests");
    assert_eq!(generate_procedure_name("Deploy to production"), "deploy");

    // Test trigger pattern extraction
    let trigger = extract_trigger_pattern(
        "Build and deploy the Rust application. This is a long description that goes on and on.",
    );
    assert!(trigger.len() <= 100, "Trigger should be at most 100 chars");

    // Test path generalization
    let actions = vec![
        "terminal: cat /Users/dave/projects/aidaemon/src/main.rs".to_string(),
        "web_search: https://docs.rs/tokio/latest".to_string(),
    ];
    let generalized = generalize_procedure(&actions);
    assert!(
        generalized[0].contains("<path>"),
        "Path should be generalized"
    );
    // Note: The path regex runs before the URL regex, so a URL like
    // "https://docs.rs/tokio/latest" has its path portion captured first,
    // resulting in "web_search: https:<path>" rather than "web_search: <url>".
    // This is a known design choice in generalize_procedure().
    assert!(
        generalized[1].contains("<path>") || generalized[1].contains("<url>"),
        "URL/path should be generalized, got: {}",
        generalized[1]
    );

    // Test error pattern extraction
    let pattern = extract_error_pattern(
        "error[E0382]: borrow of moved value: `x` at /Users/dave/src/main.rs:42:10",
    );
    assert!(pattern.contains("E0382"), "Should preserve error code");
    assert!(!pattern.contains("/Users/dave"), "Should generalize path");
}

/// Verify stale person fact pruning works.
#[tokio::test]
async fn test_stale_person_fact_pruning() {
    let (store, _db) = setup_test_store().await;

    let person = Person {
        id: 0,
        name: "Grace".to_string(),
        aliases: vec![],
        relationship: None,
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: None,
        interaction_count: 0,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let person_id = store.upsert_person(&person).await.unwrap();

    // Add a low-confidence fact
    store
        .upsert_person_fact(
            person_id,
            "hobby",
            "gardening",
            "auto_observed",
            "consolidation",
            0.5,
        )
        .await
        .unwrap();

    // Make it old
    let old_date = (Utc::now() - Duration::days(200)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE person_facts SET created_at = ?, updated_at = ?")
        .bind(&old_date)
        .bind(&old_date)
        .execute(&pool)
        .await
        .unwrap();

    // Prune stale facts (retention 180 days)
    let pruned = store.prune_stale_person_facts(180).await.unwrap();
    assert!(pruned > 0, "Should prune stale low-confidence person fact");

    let facts = store.get_person_facts(person_id, None).await.unwrap();
    assert!(facts.is_empty(), "Stale fact should be pruned");
}

/// Verify get_all_people returns all people records.
#[tokio::test]
async fn test_get_all_people() {
    let (store, _db) = setup_test_store().await;

    for name in &["Alice", "Bob", "Charlie"] {
        let person = Person {
            id: 0,
            name: name.to_string(),
            aliases: vec![],
            relationship: None,
            platform_ids: HashMap::new(),
            notes: None,
            communication_style: None,
            language_preference: None,
            last_interaction_at: None,
            interaction_count: 0,
            created_at: Utc::now(),
            updated_at: Utc::now(),
        };
        store.upsert_person(&person).await.unwrap();
    }

    let people = store.get_all_people().await.unwrap();
    assert_eq!(people.len(), 3, "Should have 3 people");
}

/// Verify people needing reconnect (inactive for more than N days).
#[tokio::test]
async fn test_people_needing_reconnect() {
    let (store, _db) = setup_test_store().await;

    // Create person with old last interaction
    let person = Person {
        id: 0,
        name: "Old Friend".to_string(),
        aliases: vec![],
        relationship: Some("friend".to_string()),
        platform_ids: HashMap::new(),
        notes: None,
        communication_style: None,
        language_preference: None,
        last_interaction_at: Some(Utc::now() - Duration::days(60)),
        interaction_count: 10,
        created_at: Utc::now() - Duration::days(120),
        updated_at: Utc::now() - Duration::days(60),
    };

    let person_id = store.upsert_person(&person).await.unwrap();

    // Set last_interaction_at to 60 days ago
    let old_date = (Utc::now() - Duration::days(60)).to_rfc3339();
    let pool = store.pool();
    sqlx::query("UPDATE people SET last_interaction_at = ? WHERE id = ?")
        .bind(&old_date)
        .bind(person_id)
        .execute(&pool)
        .await
        .unwrap();

    let needing_reconnect = store.get_people_needing_reconnect(30).await.unwrap();
    assert!(
        needing_reconnect.iter().any(|p| p.name == "Old Friend"),
        "Person inactive for 60 days should need reconnect (threshold 30 days)"
    );
}

/// Verify fact deletion via soft-delete (superseding).
#[tokio::test]
async fn test_fact_soft_delete() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "to_delete",
            "value",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(facts.len(), 1);
    let fact_id = facts[0].id;

    store.delete_fact(fact_id).await.unwrap();

    let facts_after = store.get_facts(Some("user")).await.unwrap();
    assert!(
        facts_after.is_empty(),
        "Deleted fact should not appear in active facts"
    );
}

/// Verify fact privacy update.
#[tokio::test]
async fn test_fact_privacy_update() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact(
            "user",
            "email",
            "test@example.com",
            "test",
            None,
            FactPrivacy::Channel,
        )
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    let fact_id = facts[0].id;
    assert_eq!(facts[0].privacy, FactPrivacy::Channel);

    // Upgrade to Global
    store
        .update_fact_privacy(fact_id, FactPrivacy::Global)
        .await
        .unwrap();

    let facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(
        facts[0].privacy,
        FactPrivacy::Global,
        "Privacy should be updated to Global"
    );
}

/// Verify multiple facts across different categories.
#[tokio::test]
async fn test_facts_across_categories() {
    let (store, _db) = setup_test_store().await;

    store
        .upsert_fact("user", "name", "Alice", "test", None, FactPrivacy::Global)
        .await
        .unwrap();
    store
        .upsert_fact(
            "preference",
            "editor",
            "vim",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();
    store
        .upsert_fact(
            "technical",
            "language",
            "Rust",
            "test",
            None,
            FactPrivacy::Global,
        )
        .await
        .unwrap();

    let user_facts = store.get_facts(Some("user")).await.unwrap();
    assert_eq!(user_facts.len(), 1);

    let pref_facts = store.get_facts(Some("preference")).await.unwrap();
    assert_eq!(pref_facts.len(), 1);

    let tech_facts = store.get_facts(Some("technical")).await.unwrap();
    assert_eq!(tech_facts.len(), 1);

    // Get all facts
    let all_facts = store.get_facts(None).await.unwrap();
    assert_eq!(
        all_facts.len(),
        3,
        "Should have 3 facts across all categories"
    );
}

/// Verify skill promotion: a procedure with sufficient successes/rate would be promotable.
/// (Constants DEFAULT_MIN_SUCCESS=5, DEFAULT_MIN_RATE=0.8 are private, so we test
/// the behavior rather than the constants.)
#[tokio::test]
async fn test_procedure_promotion_eligibility_criteria() {
    let (store, _db) = setup_test_store().await;

    // Eligible procedure: 6 successes, 1 failure = 85.7% rate (> 80%)
    let proc = Procedure {
        id: 0,
        name: "promotable-proc".to_string(),
        trigger_pattern: "deploy correctly".to_string(),
        steps: vec!["step1".to_string(), "step2".to_string()],
        success_count: 6,
        failure_count: 1,
        avg_duration_secs: None,
        last_used_at: None,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let proc_id = store.upsert_procedure(&proc).await.unwrap();
    assert!(proc_id > 0);

    // Ineligible procedure: 3 successes (< 5 minimum)
    let proc2 = Procedure {
        id: 0,
        name: "not-promotable".to_string(),
        trigger_pattern: "something rare".to_string(),
        steps: vec!["step1".to_string()],
        success_count: 3,
        failure_count: 0,
        avg_duration_secs: None,
        last_used_at: None,
        created_at: Utc::now(),
        updated_at: Utc::now(),
    };

    let proc2_id = store.upsert_procedure(&proc2).await.unwrap();
    assert!(proc2_id > 0);

    // Both should be stored and retrievable
    let procs = store.get_relevant_procedures("deploy", 10).await.unwrap();
    assert!(
        procs.iter().any(|p| p.name == "promotable-proc"),
        "Promotable procedure should be stored"
    );
}

/// Verify context window token estimation.
#[tokio::test]
async fn test_context_window_token_estimation() {
    use crate::memory::context_window::estimate_tokens;

    assert_eq!(estimate_tokens(""), 0);
    assert_eq!(estimate_tokens("hi"), 0); // 2/4 = 0
    assert_eq!(estimate_tokens("hello world and more"), 5); // 20/4 = 5
    let long = "a".repeat(4000);
    assert_eq!(estimate_tokens(&long), 1000); // 4000/4 = 1000
}

/// Verify context window should_extract_facts filtering.
#[tokio::test]
async fn test_should_extract_facts_filtering() {
    use crate::memory::context_window::should_extract_facts;

    // Trivial messages should be filtered
    assert!(!should_extract_facts("ok"));
    assert!(!should_extract_facts("thanks"));
    assert!(!should_extract_facts("yes"));
    assert!(!should_extract_facts("lol"));
    assert!(!should_extract_facts("short")); // <20 chars

    // Meaningful messages should pass
    assert!(should_extract_facts(
        "My dog's name is Bella and she's a golden retriever"
    ));
    assert!(should_extract_facts(
        "I work at Acme Corp in the engineering department"
    ));
}