openmemory 0.1.1

//! SQLite database layer for OpenMemory
//!
//! This module provides the database abstraction layer using rusqlite.
//! It handles schema creation, migrations, and all CRUD operations.

use crate::core::config::Config;
use crate::core::error::{Error, Result};
use crate::core::types::{MemRow, Sector, TemporalFact, VectorEntry, Waypoint};
use bytemuck::cast_slice;
use rusqlite::{params, Connection, OptionalExtension};
use std::path::Path;
use std::sync::{Arc, Mutex};

/// Database wrapper providing thread-safe access to SQLite
pub struct Database {
    conn: Arc<Mutex<Connection>>,
}

impl Database {
    /// Create a new database connection
    ///
    /// If the database file doesn't exist, it will be created along with the schema.
    pub fn new(config: &Config) -> Result<Self> {
        let db_path = &config.db_path;

        // Create parent directory if needed
        if let Some(parent) = db_path.parent() {
            if !parent.as_os_str().is_empty() && !parent.exists() {
                std::fs::create_dir_all(parent)?;
            }
        }

        let conn = if db_path.to_str() == Some(":memory:") {
            Connection::open_in_memory()?
        } else {
            Connection::open(db_path)?
        };

        let db = Self {
            conn: Arc::new(Mutex::new(conn)),
        };

        db.init_schema()?;
        db.set_pragmas()?;

        Ok(db)
    }

    /// Open a database at the given path
    pub fn open(path: impl AsRef<Path>) -> Result<Self> {
        let conn = Connection::open(path)?;
        let db = Self {
            conn: Arc::new(Mutex::new(conn)),
        };
        db.init_schema()?;
        db.set_pragmas()?;
        Ok(db)
    }

    /// Create an in-memory database (useful for testing)
    pub fn in_memory() -> Result<Self> {
        let conn = Connection::open_in_memory()?;
        let db = Self {
            conn: Arc::new(Mutex::new(conn)),
        };
        db.init_schema()?;
        db.set_pragmas()?;
        Ok(db)
    }

    /// Set SQLite pragmas for performance
    fn set_pragmas(&self) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;
        conn.execute_batch(
            "
            PRAGMA journal_mode=WAL;
            PRAGMA synchronous=NORMAL;
            PRAGMA temp_store=MEMORY;
            PRAGMA cache_size=-8000;
            PRAGMA mmap_size=134217728;
            PRAGMA foreign_keys=OFF;
            PRAGMA wal_autocheckpoint=20000;
            PRAGMA locking_mode=NORMAL;
            PRAGMA busy_timeout=5000;
            ",
        )?;
        Ok(())
    }

    /// Initialize the database schema
    fn init_schema(&self) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        conn.execute_batch(
            "
            -- Memories table
            CREATE TABLE IF NOT EXISTS memories (
                id TEXT PRIMARY KEY,
                user_id TEXT,
                segment INTEGER DEFAULT 0,
                content TEXT NOT NULL,
                simhash TEXT,
                primary_sector TEXT NOT NULL,
                tags TEXT,
                meta TEXT,
                created_at INTEGER,
                updated_at INTEGER,
                last_seen_at INTEGER,
                salience REAL,
                decay_lambda REAL,
                version INTEGER DEFAULT 1,
                mean_dim INTEGER,
                mean_vec BLOB,
                compressed_vec BLOB,
                feedback_score REAL DEFAULT 0
            );

            -- Vectors table
            CREATE TABLE IF NOT EXISTS vectors (
                id TEXT NOT NULL,
                sector TEXT NOT NULL,
                user_id TEXT,
                v BLOB NOT NULL,
                dim INTEGER NOT NULL,
                PRIMARY KEY(id, sector)
            );

            -- Waypoints (graph edges) table
            CREATE TABLE IF NOT EXISTS waypoints (
                src_id TEXT,
                dst_id TEXT NOT NULL,
                user_id TEXT,
                weight REAL NOT NULL,
                created_at INTEGER,
                updated_at INTEGER,
                PRIMARY KEY(src_id, user_id)
            );

            -- Embedding logs table
            CREATE TABLE IF NOT EXISTS embed_logs (
                id TEXT PRIMARY KEY,
                model TEXT,
                status TEXT,
                ts INTEGER,
                err TEXT
            );

            -- Users table
            CREATE TABLE IF NOT EXISTS users (
                user_id TEXT PRIMARY KEY,
                summary TEXT,
                reflection_count INTEGER DEFAULT 0,
                created_at INTEGER,
                updated_at INTEGER
            );

            -- Stats table
            CREATE TABLE IF NOT EXISTS stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                type TEXT NOT NULL,
                count INTEGER DEFAULT 1,
                ts INTEGER NOT NULL
            );

            -- Temporal facts table
            CREATE TABLE IF NOT EXISTS temporal_facts (
                id TEXT PRIMARY KEY,
                subject TEXT NOT NULL,
                predicate TEXT NOT NULL,
                object TEXT NOT NULL,
                valid_from INTEGER NOT NULL,
                valid_to INTEGER,
                confidence REAL NOT NULL CHECK(confidence >= 0 AND confidence <= 1),
                last_updated INTEGER NOT NULL,
                metadata TEXT,
                UNIQUE(subject, predicate, object, valid_from)
            );

            -- Temporal edges table
            CREATE TABLE IF NOT EXISTS temporal_edges (
                id TEXT PRIMARY KEY,
                source_id TEXT NOT NULL,
                target_id TEXT NOT NULL,
                relation_type TEXT NOT NULL,
                valid_from INTEGER NOT NULL,
                valid_to INTEGER,
                weight REAL NOT NULL,
                metadata TEXT,
                FOREIGN KEY(source_id) REFERENCES temporal_facts(id),
                FOREIGN KEY(target_id) REFERENCES temporal_facts(id)
            );

            -- Indexes
            CREATE INDEX IF NOT EXISTS idx_memories_sector ON memories(primary_sector);
            CREATE INDEX IF NOT EXISTS idx_memories_segment ON memories(segment);
            CREATE INDEX IF NOT EXISTS idx_memories_simhash ON memories(simhash);
            CREATE INDEX IF NOT EXISTS idx_memories_ts ON memories(last_seen_at);
            CREATE INDEX IF NOT EXISTS idx_memories_user ON memories(user_id);
            CREATE INDEX IF NOT EXISTS idx_vectors_user ON vectors(user_id);
            CREATE INDEX IF NOT EXISTS idx_waypoints_src ON waypoints(src_id);
            CREATE INDEX IF NOT EXISTS idx_waypoints_dst ON waypoints(dst_id);
            CREATE INDEX IF NOT EXISTS idx_waypoints_user ON waypoints(user_id);
            CREATE INDEX IF NOT EXISTS idx_stats_ts ON stats(ts);
            CREATE INDEX IF NOT EXISTS idx_stats_type ON stats(type);
            CREATE INDEX IF NOT EXISTS idx_temporal_subject ON temporal_facts(subject);
            CREATE INDEX IF NOT EXISTS idx_temporal_predicate ON temporal_facts(predicate);
            CREATE INDEX IF NOT EXISTS idx_temporal_validity ON temporal_facts(valid_from, valid_to);
            CREATE INDEX IF NOT EXISTS idx_temporal_composite ON temporal_facts(subject, predicate, valid_from, valid_to);
            CREATE INDEX IF NOT EXISTS idx_edges_source ON temporal_edges(source_id);
            CREATE INDEX IF NOT EXISTS idx_edges_target ON temporal_edges(target_id);
            CREATE INDEX IF NOT EXISTS idx_edges_validity ON temporal_edges(valid_from, valid_to);
            ",
        )?;

        Ok(())
    }

    // =========================================================================
    // Memory Operations
    // =========================================================================

    /// Insert a new memory
    pub fn insert_memory(&self, mem: &MemRow, segment: i32, simhash: Option<&str>) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let tags_json = mem.tags.as_ref().map(|t| serde_json::to_string(t).ok()).flatten();
        let meta_json = mem.meta.as_ref().map(|m| serde_json::to_string(m).ok()).flatten();

        conn.execute(
            "INSERT INTO memories (
                id, user_id, segment, content, simhash, primary_sector,
                tags, meta, created_at, updated_at, last_seen_at,
                salience, decay_lambda, version, mean_dim, mean_vec,
                compressed_vec, feedback_score
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, NULL, NULL, 0)",
            params![
                mem.id,
                mem.user_id,
                segment,
                mem.content,
                simhash,
                mem.primary_sector.as_str(),
                tags_json,
                meta_json,
                mem.created_at,
                mem.updated_at,
                mem.last_seen_at,
                mem.salience,
                mem.decay_lambda,
                mem.version,
            ],
        )?;

        Ok(())
    }

    /// Get a memory by ID
    pub fn get_memory(&self, id: &str) -> Result<Option<MemRow>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let row = conn
            .query_row(
                "SELECT id, content, primary_sector, tags, meta, user_id,
                        created_at, updated_at, last_seen_at, salience,
                        decay_lambda, version
                 FROM memories WHERE id = ?",
                params![id],
                |row| {
                    Ok(MemRowRaw {
                        id: row.get(0)?,
                        content: row.get(1)?,
                        primary_sector: row.get(2)?,
                        tags: row.get(3)?,
                        meta: row.get(4)?,
                        user_id: row.get(5)?,
                        created_at: row.get(6)?,
                        updated_at: row.get(7)?,
                        last_seen_at: row.get(8)?,
                        salience: row.get(9)?,
                        decay_lambda: row.get(10)?,
                        version: row.get(11)?,
                    })
                },
            )
            .optional()?;

        Ok(row.map(|r| r.into_mem_row()))
    }

    /// Get memory by simhash
    pub fn get_memory_by_simhash(&self, simhash: &str) -> Result<Option<MemRow>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let row = conn
            .query_row(
                "SELECT id, content, primary_sector, tags, meta, user_id,
                        created_at, updated_at, last_seen_at, salience,
                        decay_lambda, version
                 FROM memories WHERE simhash = ?
                 ORDER BY salience DESC LIMIT 1",
                params![simhash],
                |row| {
                    Ok(MemRowRaw {
                        id: row.get(0)?,
                        content: row.get(1)?,
                        primary_sector: row.get(2)?,
                        tags: row.get(3)?,
                        meta: row.get(4)?,
                        user_id: row.get(5)?,
                        created_at: row.get(6)?,
                        updated_at: row.get(7)?,
                        last_seen_at: row.get(8)?,
                        salience: row.get(9)?,
                        decay_lambda: row.get(10)?,
                        version: row.get(11)?,
                    })
                },
            )
            .optional()?;

        Ok(row.map(|r| r.into_mem_row()))
    }

    /// Get all memories with pagination
    pub fn get_all_memories(&self, limit: usize, offset: usize) -> Result<Vec<MemRow>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let mut stmt = conn.prepare(
            "SELECT id, content, primary_sector, tags, meta, user_id,
                    created_at, updated_at, last_seen_at, salience,
                    decay_lambda, version
             FROM memories ORDER BY created_at DESC LIMIT ? OFFSET ?",
        )?;

        let rows = stmt.query_map(params![limit as i64, offset as i64], |row| {
            Ok(MemRowRaw {
                id: row.get(0)?,
                content: row.get(1)?,
                primary_sector: row.get(2)?,
                tags: row.get(3)?,
                meta: row.get(4)?,
                user_id: row.get(5)?,
                created_at: row.get(6)?,
                updated_at: row.get(7)?,
                last_seen_at: row.get(8)?,
                salience: row.get(9)?,
                decay_lambda: row.get(10)?,
                version: row.get(11)?,
            })
        })?;

        let mut result = Vec::new();
        for row in rows {
            result.push(row?.into_mem_row());
        }
        Ok(result)
    }

    /// Get memories by sector
    pub fn get_memories_by_sector(
        &self,
        sector: &Sector,
        limit: usize,
        offset: usize,
    ) -> Result<Vec<MemRow>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let mut stmt = conn.prepare(
            "SELECT id, content, primary_sector, tags, meta, user_id,
                    created_at, updated_at, last_seen_at, salience,
                    decay_lambda, version
             FROM memories WHERE primary_sector = ?
             ORDER BY created_at DESC LIMIT ? OFFSET ?",
        )?;

        let rows = stmt.query_map(params![sector.as_str(), limit as i64, offset as i64], |row| {
            Ok(MemRowRaw {
                id: row.get(0)?,
                content: row.get(1)?,
                primary_sector: row.get(2)?,
                tags: row.get(3)?,
                meta: row.get(4)?,
                user_id: row.get(5)?,
                created_at: row.get(6)?,
                updated_at: row.get(7)?,
                last_seen_at: row.get(8)?,
                salience: row.get(9)?,
                decay_lambda: row.get(10)?,
                version: row.get(11)?,
            })
        })?;

        let mut result = Vec::new();
        for row in rows {
            result.push(row?.into_mem_row());
        }
        Ok(result)
    }

    /// Get memories by user
    pub fn get_memories_by_user(
        &self,
        user_id: &str,
        limit: usize,
        offset: usize,
    ) -> Result<Vec<MemRow>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let mut stmt = conn.prepare(
            "SELECT id, content, primary_sector, tags, meta, user_id,
                    created_at, updated_at, last_seen_at, salience,
                    decay_lambda, version
             FROM memories WHERE user_id = ?
             ORDER BY created_at DESC LIMIT ? OFFSET ?",
        )?;

        let rows = stmt.query_map(params![user_id, limit as i64, offset as i64], |row| {
            Ok(MemRowRaw {
                id: row.get(0)?,
                content: row.get(1)?,
                primary_sector: row.get(2)?,
                tags: row.get(3)?,
                meta: row.get(4)?,
                user_id: row.get(5)?,
                created_at: row.get(6)?,
                updated_at: row.get(7)?,
                last_seen_at: row.get(8)?,
                salience: row.get(9)?,
                decay_lambda: row.get(10)?,
                version: row.get(11)?,
            })
        })?;

        let mut result = Vec::new();
        for row in rows {
            result.push(row?.into_mem_row());
        }
        Ok(result)
    }

    /// Update memory salience and last_seen_at
    pub fn update_memory_seen(
        &self,
        id: &str,
        last_seen_at: i64,
        salience: f64,
        updated_at: i64,
    ) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        conn.execute(
            "UPDATE memories SET last_seen_at = ?, salience = ?, updated_at = ? WHERE id = ?",
            params![last_seen_at, salience, updated_at, id],
        )?;

        Ok(())
    }

    /// Update memory content
    pub fn update_memory(
        &self,
        id: &str,
        content: &str,
        tags: Option<&Vec<String>>,
        meta: Option<&serde_json::Value>,
        updated_at: i64,
    ) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let tags_json = tags.map(|t| serde_json::to_string(t).ok()).flatten();
        let meta_json = meta.map(|m| serde_json::to_string(m).ok()).flatten();

        conn.execute(
            "UPDATE memories SET content = ?, tags = ?, meta = ?, updated_at = ?, version = version + 1 WHERE id = ?",
            params![content, tags_json, meta_json, updated_at, id],
        )?;

        Ok(())
    }

    /// Delete a memory
    pub fn delete_memory(&self, id: &str) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;
        conn.execute("DELETE FROM memories WHERE id = ?", params![id])?;
        Ok(())
    }

    /// Update feedback score using exponential moving average
    ///
    /// Formula: new_fb = cur_fb * 0.9 + score * 0.1
    pub fn update_feedback_score(&self, id: &str, score: f64) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        // Get current feedback score
        let cur_fb: f64 = conn
            .query_row(
                "SELECT feedback_score FROM memories WHERE id = ?",
                params![id],
                |row| row.get(0),
            )
            .unwrap_or(0.0);

        // Exponential moving average (matches JS implementation)
        let new_fb = cur_fb * 0.9 + score * 0.1;

        conn.execute(
            "UPDATE memories SET feedback_score = ? WHERE id = ?",
            params![new_fb, id],
        )?;

        Ok(())
    }

    /// Get feedback score for a memory
    pub fn get_feedback_score(&self, id: &str) -> Result<f64> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let score: f64 = conn
            .query_row(
                "SELECT feedback_score FROM memories WHERE id = ?",
                params![id],
                |row| row.get(0),
            )
            .unwrap_or(0.0);

        Ok(score)
    }

    // =========================================================================
    // Vector Operations
    // =========================================================================

    /// Insert a vector
    pub fn insert_vector(&self, entry: &VectorEntry) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let v_blob: Vec<u8> = cast_slice(&entry.vector).to_vec();

        conn.execute(
            "INSERT INTO vectors (id, sector, user_id, v, dim) VALUES (?, ?, ?, ?, ?)",
            params![
                entry.id,
                entry.sector.as_str(),
                entry.user_id,
                v_blob,
                entry.dim as i32,
            ],
        )?;

        Ok(())
    }

    /// Get a vector by ID and sector
    pub fn get_vector(&self, id: &str, sector: &Sector) -> Result<Option<Vec<f32>>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let row: Option<(Vec<u8>, i32)> = conn
            .query_row(
                "SELECT v, dim FROM vectors WHERE id = ? AND sector = ?",
                params![id, sector.as_str()],
                |row| Ok((row.get(0)?, row.get(1)?)),
            )
            .optional()?;

        Ok(row.map(|(blob, _dim)| blob_to_vec(&blob)))
    }

    /// Get all vectors for a memory ID
    pub fn get_vectors_by_id(&self, id: &str) -> Result<Vec<(Sector, Vec<f32>)>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let mut stmt = conn.prepare("SELECT sector, v, dim FROM vectors WHERE id = ?")?;

        let rows = stmt.query_map(params![id], |row| {
            let sector_str: String = row.get(0)?;
            let blob: Vec<u8> = row.get(1)?;
            Ok((sector_str, blob))
        })?;

        let mut result = Vec::new();
        for row in rows {
            let (sector_str, blob) = row?;
            if let Some(sector) = Sector::from_str(&sector_str) {
                result.push((sector, blob_to_vec(&blob)));
            }
        }
        Ok(result)
    }

    /// Get all vectors for a sector
    pub fn get_vectors_by_sector(&self, sector: &Sector) -> Result<Vec<(String, Vec<f32>)>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let mut stmt =
            conn.prepare("SELECT id, v, dim FROM vectors WHERE sector = ?")?;

        let rows = stmt.query_map(params![sector.as_str()], |row| {
            let id: String = row.get(0)?;
            let blob: Vec<u8> = row.get(1)?;
            Ok((id, blob))
        })?;

        let mut result = Vec::new();
        for row in rows {
            let (id, blob) = row?;
            result.push((id, blob_to_vec(&blob)));
        }
        Ok(result)
    }

    /// Delete all vectors for a memory
    pub fn delete_vectors(&self, id: &str) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;
        conn.execute("DELETE FROM vectors WHERE id = ?", params![id])?;
        Ok(())
    }

    // =========================================================================
    // Waypoint Operations
    // =========================================================================

    /// Insert or update a waypoint
    pub fn upsert_waypoint(&self, waypoint: &Waypoint) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        conn.execute(
            "INSERT OR REPLACE INTO waypoints (src_id, dst_id, user_id, weight, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?)",
            params![
                waypoint.src_id,
                waypoint.dst_id,
                None::<String>,
                waypoint.weight,
                waypoint.created_at,
                waypoint.updated_at,
            ],
        )?;

        Ok(())
    }

    /// Get neighbors of a node
    pub fn get_neighbors(&self, src_id: &str) -> Result<Vec<(String, f64)>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let mut stmt = conn.prepare(
            "SELECT dst_id, weight FROM waypoints WHERE src_id = ? ORDER BY weight DESC",
        )?;

        let rows = stmt.query_map(params![src_id], |row| {
            Ok((row.get(0)?, row.get(1)?))
        })?;

        let mut result = Vec::new();
        for row in rows {
            result.push(row?);
        }
        Ok(result)
    }

    /// Delete waypoints for a memory
    pub fn delete_waypoints(&self, id: &str) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;
        conn.execute(
            "DELETE FROM waypoints WHERE src_id = ? OR dst_id = ?",
            params![id, id],
        )?;
        Ok(())
    }

    /// Prune low-weight waypoints
    pub fn prune_waypoints(&self, threshold: f64) -> Result<usize> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;
        let count = conn.execute(
            "DELETE FROM waypoints WHERE weight < ?",
            params![threshold],
        )?;
        Ok(count)
    }

    // =========================================================================
    // Temporal Facts Operations
    // =========================================================================

    /// Insert a temporal fact
    pub fn insert_temporal_fact(&self, fact: &TemporalFact) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let meta_json = fact.metadata.as_ref().map(|m| serde_json::to_string(m).ok()).flatten();

        conn.execute(
            "INSERT INTO temporal_facts (id, subject, predicate, object, valid_from, valid_to, confidence, last_updated, metadata)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            params![
                fact.id,
                fact.subject,
                fact.predicate,
                fact.object,
                fact.valid_from,
                fact.valid_to,
                fact.confidence,
                fact.last_updated,
                meta_json,
            ],
        )?;

        Ok(())
    }

    /// Get a temporal fact by ID
    pub fn get_temporal_fact(&self, id: &str) -> Result<Option<TemporalFact>> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let row = conn
            .query_row(
                "SELECT id, subject, predicate, object, valid_from, valid_to, confidence, last_updated, metadata
                 FROM temporal_facts WHERE id = ?",
                params![id],
                |row| {
                    let meta_str: Option<String> = row.get(8)?;
                    Ok(TemporalFact {
                        id: row.get(0)?,
                        subject: row.get(1)?,
                        predicate: row.get(2)?,
                        object: row.get(3)?,
                        valid_from: row.get(4)?,
                        valid_to: row.get(5)?,
                        confidence: row.get(6)?,
                        last_updated: row.get(7)?,
                        metadata: meta_str.and_then(|s| serde_json::from_str(&s).ok()),
                    })
                },
            )
            .optional()?;

        Ok(row)
    }

    // =========================================================================
    // Stats Operations
    // =========================================================================

    /// Log a maintenance operation
    pub fn log_maintenance(&self, op_type: &str, count: i32) -> Result<()> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let now = std::time::SystemTime::now()
            .duration_since(std::time::UNIX_EPOCH)
            .map(|d| d.as_millis() as i64)
            .unwrap_or(0);

        conn.execute(
            "INSERT INTO stats (type, count, ts) VALUES (?, ?, ?)",
            params![op_type, count, now],
        )?;

        Ok(())
    }

    // =========================================================================
    // Transaction Support
    // =========================================================================

    /// Execute a function within a transaction
    pub fn transaction<T, F>(&self, f: F) -> Result<T>
    where
        F: FnOnce(&Connection) -> Result<T>,
    {
        let mut conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let tx = conn.transaction()?;
        let result = f(&tx)?;
        tx.commit()?;

        Ok(result)
    }

    // =========================================================================
    // Segment Operations
    // =========================================================================

    /// Get the count of memories in a segment
    pub fn get_segment_count(&self, segment: i32) -> Result<i64> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let count: i64 = conn.query_row(
            "SELECT COUNT(*) FROM memories WHERE segment = ?",
            params![segment],
            |row| row.get(0),
        )?;

        Ok(count)
    }

    /// Get the maximum segment number
    pub fn get_max_segment(&self) -> Result<i32> {
        let conn = self.conn.lock().map_err(|_| Error::internal("Lock poisoned"))?;

        let max: i32 = conn.query_row(
            "SELECT COALESCE(MAX(segment), 0) FROM memories",
            [],
            |row| row.get(0),
        )?;

        Ok(max)
    }
}

// Helper struct for raw database rows
struct MemRowRaw {
    id: String,
    content: String,
    primary_sector: String,
    tags: Option<String>,
    meta: Option<String>,
    user_id: Option<String>,
    created_at: i64,
    updated_at: i64,
    last_seen_at: i64,
    salience: f64,
    decay_lambda: f64,
    version: i32,
}

impl MemRowRaw {
    fn into_mem_row(self) -> MemRow {
        MemRow {
            id: self.id,
            content: self.content,
            primary_sector: Sector::from_str(&self.primary_sector).unwrap_or_default(),
            tags: self.tags.and_then(|s| serde_json::from_str(&s).ok()),
            meta: self.meta.and_then(|s| serde_json::from_str(&s).ok()),
            user_id: self.user_id,
            created_at: self.created_at,
            updated_at: self.updated_at,
            last_seen_at: self.last_seen_at,
            salience: self.salience,
            decay_lambda: self.decay_lambda,
            version: self.version,
        }
    }
}

/// Convert f32 slice to byte vector
#[allow(dead_code)]
fn vec_to_blob(v: &[f32]) -> Vec<u8> {
    cast_slice(v).to_vec()
}

/// Convert byte slice to f32 vector
fn blob_to_vec(blob: &[u8]) -> Vec<f32> {
    cast_slice(blob).to_vec()
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_in_memory_db() {
        let db = Database::in_memory().unwrap();

        // Test insert and get
        let mem = MemRow {
            id: "test-1".to_string(),
            content: "Test content".to_string(),
            primary_sector: Sector::Semantic,
            tags: Some(vec!["tag1".to_string()]),
            meta: None,
            user_id: None,
            created_at: 1000,
            updated_at: 1000,
            last_seen_at: 1000,
            salience: 0.5,
            decay_lambda: 0.005,
            version: 1,
        };

        db.insert_memory(&mem, 0, None).unwrap();

        let retrieved = db.get_memory("test-1").unwrap();
        assert!(retrieved.is_some());
        let retrieved = retrieved.unwrap();
        assert_eq!(retrieved.content, "Test content");
        assert_eq!(retrieved.primary_sector, Sector::Semantic);
    }

    #[test]
    fn test_vector_operations() {
        let db = Database::in_memory().unwrap();

        let entry = VectorEntry {
            id: "vec-1".to_string(),
            sector: Sector::Semantic,
            user_id: None,
            vector: vec![0.1, 0.2, 0.3, 0.4],
            dim: 4,
        };

        db.insert_vector(&entry).unwrap();

        let vec = db.get_vector("vec-1", &Sector::Semantic).unwrap();
        assert!(vec.is_some());
        let vec = vec.unwrap();
        assert_eq!(vec.len(), 4);
        assert!((vec[0] - 0.1).abs() < 1e-6);
    }

    #[test]
    fn test_blob_conversion() {
        let original = vec![1.0f32, 2.0, 3.0, 4.0];
        let blob = vec_to_blob(&original);
        let restored = blob_to_vec(&blob);
        assert_eq!(original, restored);
    }
}