// DS-5: WRITE_LOCK guard is held across .await inside block_on().
// This is safe — block_on runs single-threaded, no concurrent tasks can deadlock.
#![allow(clippy::await_holding_lock)]
//! Note CRUD operations

use std::path::Path;

use sqlx::Row;

use super::helpers::sql::max_rows_per_statement;
use super::helpers::{NoteStats, NoteSummary, StoreError};
use super::{ReadWrite, Store};
use crate::nl::normalize_for_fts;
use crate::note::Note;
use crate::note::{SENTIMENT_NEGATIVE_THRESHOLD, SENTIMENT_POSITIVE_THRESHOLD};

/// Insert a batch of notes + FTS entries within an existing transaction.
///
/// Three batched statements per `max_rows_per_statement` chunk:
///   1. INSERT OR REPLACE INTO notes (9 binds/row) — multi-row via QueryBuilder.
///   2. DELETE FROM notes_fts WHERE id IN (?,?,...) — one statement per batch.
///   3. INSERT INTO notes_fts (2 binds/row) — multi-row via QueryBuilder.
///
/// FTS5 supports multi-row INSERT in this codebase — see
/// `async_helpers::upsert_fts_conditional` for the `chunks_fts` precedent.
async fn insert_notes_with_fts_batched(
    tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
    notes: &[Note],
    source_str: &str,
    file_mtime: i64,
    now: &str,
) -> Result<(), StoreError> {
    if notes.is_empty() {
        return Ok(());
    }

    // Empty blob for the embedding column: notes carry no embedding. The
    // column is retained for SQLite compatibility (no DROP COLUMN in older
    // versions).
    let empty_blob: &[u8] = &[];

    // Pre-serialize mentions JSON + FTS-normalized text once so the batch
    // loops below don't re-compute across chunk boundaries.
    let mentions_json: Vec<String> = notes
        .iter()
        .map(|n| serde_json::to_string(&n.mentions))
        .collect::<Result<Vec<_>, _>>()?;
    let fts_text: Vec<String> = notes.iter().map(|n| normalize_for_fts(&n.text)).collect();

    // Step 1: INSERT OR REPLACE INTO notes (10 cols per row, including kind).
    const NOTES_BATCH: usize = max_rows_per_statement(10);
    for (batch_idx, batch) in notes.chunks(NOTES_BATCH).enumerate() {
        let row_offset = batch_idx * NOTES_BATCH;
        let mut qb: sqlx::QueryBuilder<sqlx::Sqlite> = sqlx::QueryBuilder::new(
            "INSERT OR REPLACE INTO notes (id, text, sentiment, mentions, embedding, source_file, file_mtime, created_at, updated_at, kind)",
        );
        qb.push_values(batch.iter().enumerate(), |mut b, (i, note)| {
            b.push_bind(&note.id)
                .push_bind(&note.text)
                .push_bind(note.sentiment)
                .push_bind(&mentions_json[row_offset + i])
                .push_bind(empty_blob)
                .push_bind(source_str)
                .push_bind(file_mtime)
                .push_bind(now)
                .push_bind(now)
                .push_bind(&note.kind);
        });
        qb.build().execute(&mut **tx).await?;
    }

    // Step 2: DELETE FROM notes_fts WHERE id IN (?,?,...) — one statement per
    // batch replaces the per-note DELETE.
    for batch in notes.chunks(max_rows_per_statement(1)) {
        let placeholders = crate::store::helpers::make_placeholders(batch.len());
        let sql = format!("DELETE FROM notes_fts WHERE id IN ({})", placeholders);
        let mut q = sqlx::query(sqlx::AssertSqlSafe(sql.as_str()));
        for n in batch {
            q = q.bind(&n.id);
        }
        q.execute(&mut **tx).await?;
    }

    // Step 3: INSERT INTO notes_fts — multi-row via QueryBuilder.
    const FTS_BATCH: usize = max_rows_per_statement(2);
    for (batch_idx, batch) in notes.chunks(FTS_BATCH).enumerate() {
        let text_offset = batch_idx * FTS_BATCH;
        let mut qb: sqlx::QueryBuilder<sqlx::Sqlite> =
            sqlx::QueryBuilder::new("INSERT INTO notes_fts (id, text)");
        qb.push_values(batch.iter().enumerate(), |mut b, (i, note)| {
            b.push_bind(&note.id).push_bind(&fts_text[text_offset + i]);
        });
        qb.build().execute(&mut **tx).await?;
    }

    Ok(())
}

impl Store<ReadWrite> {
    /// Insert or update notes in batch
    pub fn upsert_notes_batch(
        &self,
        notes: &[Note],
        source_file: &Path,
        file_mtime: i64,
    ) -> Result<usize, StoreError> {
        let _span = tracing::info_span!("upsert_notes_batch", count = notes.len()).entered();
        let source_str = crate::normalize_path(source_file);
        tracing::debug!(
            source = %source_str,
            count = notes.len(),
            "upserting notes batch"
        );

        self.rt.block_on(async {
            let (_guard, mut tx) = self.begin_write().await?;

            let now = chrono::Utc::now().to_rfc3339();
            insert_notes_with_fts_batched(&mut tx, notes, &source_str, file_mtime, &now).await?;

            tx.commit().await?;
            self.invalidate_notes_cache();
            Ok(notes.len())
        })
    }

    /// Copy every note row from `parent` into this store, preserving the
    /// note id, text, sentiment, mentions, kind, source file, and mtime.
    ///
    /// Notes are project-level metadata keyed on their `mentions` (file +
    /// name); they do not change with uncommitted code edits. A faithful
    /// shadow of a parent index — e.g. the worktree search overlay's fresh
    /// `:memory:` store — must therefore carry the parent's notes so its
    /// note-boost index computes the same sentiment multiplier and records the
    /// same `note_boost` provenance the parent would. Without this the overlay
    /// store's notes table is empty, so a file that is both noted in the
    /// parent and edited in the worktree (masked out of the parent leg,
    /// re-served from the overlay leg) loses its note multiplier and its
    /// `note_boost` signal.
    ///
    /// The parent's SQLite notes are the source of truth, not `docs/notes.toml`
    /// — the file can be stale relative to notes added via `cqs notes add`
    /// since the last index. Notes copied here already satisfy the discrete-
    /// sentiment CHECK constraint (they were written through the same schema),
    /// so the re-insert cannot violate it.
    ///
    /// Returns the number of notes copied. A parent with no notes is a no-op.
    pub fn copy_notes_from<M>(&self, parent: &Store<M>) -> Result<usize, StoreError> {
        let _span = tracing::info_span!("copy_notes_from").entered();

        // Read the parent's full note rows, including source_file + file_mtime
        // so the shadow preserves them. Grouped by (source_file, file_mtime)
        // because the batched insert helper stamps one source/mtime per call;
        // today notes.toml is the only source, but the schema permits several.
        let rows = parent.rt.block_on(async {
            let rows: Vec<_> = sqlx::query(
                "SELECT id, text, sentiment, mentions, kind, source_file, file_mtime \
                 FROM notes ORDER BY source_file, created_at",
            )
            .fetch_all(&parent.pool)
            .await?;
            Ok::<_, StoreError>(rows)
        })?;

        if rows.is_empty() {
            return Ok(0);
        }

        // (source_file, file_mtime) → notes for that group, insertion order
        // preserved by the ORDER BY above.
        let mut groups: Vec<(String, i64, Vec<Note>)> = Vec::new();
        for row in &rows {
            let id: String = row.get(0);
            let text: String = row.get(1);
            let sentiment: f64 = row.get(2);
            let mentions_json: Option<String> = row.try_get(3).unwrap_or(None);
            let kind: Option<String> = row.try_get(4).unwrap_or(None);
            let source_file: String = row.get(5);
            let file_mtime: i64 = row.get(6);

            let mentions: Vec<String> = mentions_json
                .as_deref()
                .map(|j| {
                    serde_json::from_str(j).unwrap_or_else(|e| {
                        tracing::warn!(note_id = %id, error = %e, "Failed to deserialize note mentions during copy");
                        Vec::new()
                    })
                })
                .unwrap_or_default();

            let note = Note {
                id,
                text,
                sentiment: sentiment as f32,
                mentions,
                kind,
            };

            match groups
                .iter_mut()
                .find(|(sf, mt, _)| *sf == source_file && *mt == file_mtime)
            {
                Some((_, _, notes)) => notes.push(note),
                None => groups.push((source_file, file_mtime, vec![note])),
            }
        }

        let copied = rows.len();
        self.rt.block_on(async {
            let (_guard, mut tx) = self.begin_write().await?;
            let now = chrono::Utc::now().to_rfc3339();
            for (source_str, file_mtime, notes) in &groups {
                insert_notes_with_fts_batched(&mut tx, notes, source_str, *file_mtime, &now)
                    .await?;
            }
            tx.commit().await?;
            self.invalidate_notes_cache();
            Ok::<_, StoreError>(())
        })?;

        tracing::debug!(
            copied,
            groups = groups.len(),
            "copied parent notes into shadow store"
        );
        Ok(copied)
    }

    /// Replace all notes for a source file in a single transaction.
    /// Atomically deletes existing notes and inserts new ones, preventing
    /// data loss if the process crashes mid-operation.
    pub fn replace_notes_for_file(
        &self,
        notes: &[Note],
        source_file: &Path,
        file_mtime: i64,
    ) -> Result<usize, StoreError> {
        let _span =
            tracing::info_span!("replace_notes_for_file", path = %source_file.display()).entered();
        let source_str = crate::normalize_path(source_file);
        tracing::debug!(
            source = %source_str,
            count = notes.len(),
            "replacing notes for file"
        );

        self.rt.block_on(async {
            let (_guard, mut tx) = self.begin_write().await?;

            // Step 1: Delete existing notes + FTS for this file
            sqlx::query(
                "DELETE FROM notes_fts WHERE id IN (SELECT id FROM notes WHERE source_file = ?1)",
            )
            .bind(&source_str)
            .execute(&mut *tx)
            .await?;

            sqlx::query("DELETE FROM notes WHERE source_file = ?1")
                .bind(&source_str)
                .execute(&mut *tx)
                .await?;

            // Step 2: Insert new notes + FTS (batched).
            let now = chrono::Utc::now().to_rfc3339();
            insert_notes_with_fts_batched(&mut tx, notes, &source_str, file_mtime, &now).await?;

            tx.commit().await?;
            self.invalidate_notes_cache();
            tracing::info!(source = %source_str, count = notes.len(), "Notes replaced successfully");
            Ok(notes.len())
        })
    }
}

impl<Mode> Store<Mode> {
    /// Check if notes file needs reindexing based on mtime.
    /// Returns `Ok(Some(mtime))` if reindex needed (with the file's current mtime),
    /// or `Ok(None)` if no reindex needed. This avoids reading file metadata twice.
    pub fn notes_need_reindex(&self, source_file: &Path) -> Result<Option<i64>, StoreError> {
        let _span =
            tracing::debug_span!("notes_need_reindex", path = %source_file.display()).entered();
        let current_mtime = crate::duration_to_mtime_millis(
            source_file
                .metadata()?
                .modified()?
                .duration_since(std::time::UNIX_EPOCH)
                .map_err(|_| StoreError::SystemTime)?,
        );

        self.rt.block_on(async {
            let row: Option<(i64,)> =
                sqlx::query_as("SELECT file_mtime FROM notes WHERE source_file = ?1 LIMIT 1")
                    .bind(crate::normalize_path(source_file))
                    .fetch_optional(&self.pool)
                    .await?;

            match row {
                Some((mtime,)) if mtime >= current_mtime => Ok(None),
                _ => Ok(Some(current_mtime)),
            }
        })
    }

    /// Retrieves the total count of notes stored in the database.
    /// This method executes a SQL COUNT query against the notes table and returns the total number of notes. If no notes exist, it returns 0.
    /// # Returns
    /// Returns a `Result` containing the count of notes as a `u64`, or a `StoreError` if the database query fails.
    /// # Errors
    /// Returns `StoreError` if the database query encounters an error or the connection fails.
    pub fn note_count(&self) -> Result<u64, StoreError> {
        let _span = tracing::debug_span!("note_count").entered();
        self.rt.block_on(async {
            let row: Option<(i64,)> = sqlx::query_as("SELECT COUNT(*) FROM notes")
                .fetch_optional(&self.pool)
                .await?;
            Ok(row.map(|(c,)| c as u64).unwrap_or(0))
        })
    }

    /// Content-identity digest over the notes table — a deterministic token
    /// that changes iff the notes change. Used by the worktree search overlay to
    /// fold notes into its cache identity (the `fingerprint`): a parent notes
    /// mutation must invalidate the overlay's LRU entry so the rebuilt shadow
    /// store carries the fresh sentiment, rather than serving a stale
    /// `note_boost` until the deferrable overlay-clear runs.
    ///
    /// Digests `(id, text, sentiment, mentions)` for every row, ordered by `id`
    /// so git/insert ordering cannot perturb the result. The field set is the
    /// note identity that determines a boost outcome — `sentiment` and
    /// `mentions` drive the multiplier directly, and `text` is folded in so a
    /// text-only `notes update` (which a reader would still expect to count as
    /// "the notes changed") also flips the token. Timestamp columns
    /// (`created_at` / `updated_at` / `file_mtime`) are deliberately excluded:
    /// they can churn without a meaningful note change and would over-invalidate.
    ///
    /// This is NOT `data_version`: `data_version` bumps on every index.db write
    /// (code reindex, chunk upserts, …), so folding it into the fingerprint would
    /// rebuild the overlay on unrelated parent changes even when the worktree
    /// delta is unchanged — a perf regression. This token changes only when the
    /// notes themselves change.
    ///
    /// An empty notes table yields a stable, fixed digest (the domain-separated
    /// header with no rows), so the clean / no-notes case is deterministic.
    pub fn notes_revision(&self) -> Result<[u8; 32], StoreError> {
        let _span = tracing::debug_span!("notes_revision").entered();
        self.rt.block_on(async {
            let rows: Vec<_> =
                sqlx::query("SELECT id, text, sentiment, mentions FROM notes ORDER BY id")
                    .fetch_all(&self.pool)
                    .await?;

            let mut hasher = blake3::Hasher::new();
            // Domain separation: distinguishes this preimage from any other
            // blake3 digest in the codebase and pins the field layout.
            hasher.update(b"cqs-notes-revision-v1\0");
            for row in &rows {
                let id: String = row.get(0);
                let text: String = row.get(1);
                let sentiment: f64 = row.get(2);
                // `mentions` is stored as a JSON array string; hash the raw
                // stored bytes (NULL → empty) — byte-stable and order-stable
                // because the column is written canonically.
                let mentions: Option<String> = row.try_get(3).unwrap_or(None);

                hasher.update(id.as_bytes());
                hasher.update(b"\0");
                hasher.update(text.as_bytes());
                hasher.update(b"\0");
                // Fixed-width little-endian bits of the f64 so two equal
                // sentiments always hash identically (avoids float-format drift).
                hasher.update(&sentiment.to_le_bytes());
                hasher.update(b"\0");
                hasher.update(mentions.as_deref().unwrap_or("").as_bytes());
                hasher.update(b"\0");
            }
            Ok(*hasher.finalize().as_bytes())
        })
    }

    /// Get note statistics (total, warnings, patterns).
    /// Uses `SENTIMENT_NEGATIVE_THRESHOLD` (-0.3) and `SENTIMENT_POSITIVE_THRESHOLD` (0.3)
    /// to classify notes. These thresholds work with discrete sentiment values
    /// (-1, -0.5, 0, 0.5, 1) -- negative values (-1, -0.5) count as warnings,
    /// positive values (0.5, 1) count as patterns.
    pub fn note_stats(&self) -> Result<NoteStats, StoreError> {
        let _span = tracing::debug_span!("note_stats").entered();
        self.rt.block_on(async {
            let (total, warnings, patterns): (i64, i64, i64) = sqlx::query_as(
                "SELECT COUNT(*),
                        SUM(CASE WHEN sentiment < ?1 THEN 1 ELSE 0 END),
                        SUM(CASE WHEN sentiment > ?2 THEN 1 ELSE 0 END)
                 FROM notes",
            )
            .bind(SENTIMENT_NEGATIVE_THRESHOLD)
            .bind(SENTIMENT_POSITIVE_THRESHOLD)
            .fetch_one(&self.pool)
            .await?;

            Ok(NoteStats {
                total: total as u64,
                warnings: warnings as u64,
                patterns: patterns as u64,
            })
        })
    }

    /// List all notes with metadata (no embeddings).
    /// Returns `NoteSummary` for each note, useful for mention-based filtering
    /// without the cost of loading embeddings.
    pub fn list_notes_summaries(&self) -> Result<Vec<NoteSummary>, StoreError> {
        let _span = tracing::debug_span!("list_notes_summaries").entered();
        self.rt.block_on(async {
            let rows: Vec<_> = sqlx::query(
                "SELECT id, text, sentiment, mentions, kind FROM notes ORDER BY created_at",
            )
            .fetch_all(&self.pool)
            .await?;

            Ok(rows
                .into_iter()
                .map(|row| {
                    let id: String = row.get(0);
                    let text: String = row.get(1);
                    let sentiment: f64 = row.get(2);
                    let mentions_json: String = row.get(3);
                    let kind: Option<String> = row.try_get(4).unwrap_or(None);
                    let mentions: Vec<String> =
                        serde_json::from_str(&mentions_json).unwrap_or_else(|e| {
                            tracing::warn!(note_id = %id, error = %e, "Failed to deserialize note mentions");
                            Vec::new()
                        });
                    NoteSummary {
                        id,
                        text,
                        sentiment: sentiment as f32,
                        mentions,
                        kind,
                    }
                })
                .collect())
        })
    }
}

#[cfg(test)]
mod tests {
    use crate::note::Note;
    use crate::test_helpers::setup_store;
    use crate::Store;
    use std::path::Path;

    /// Creates a new Note with the specified id, text, and sentiment.
    /// # Arguments
    /// * `id` - A string slice representing the unique identifier for the note
    /// * `text` - A string slice containing the note's content
    /// * `sentiment` - A floating-point value representing the sentiment score of the note
    /// # Returns
    /// A new `Note` struct initialized with the provided id, text, and sentiment, and an empty mentions vector.
    fn make_note(id: &str, text: &str, sentiment: f32) -> Note {
        Note {
            id: id.to_string(),
            text: text.to_string(),
            sentiment,
            mentions: vec![],
            kind: None,
        }
    }
    // Sentiment threshold positioning relative to the discrete values is a
    // compile-time `const` assertion next to the constants in `crate::note`.

    /// The notes-revision token is the overlay's notes cache-identity. Empty
    /// table → a stable, fixed digest; a sentiment change → a different digest;
    /// a text-only change → a different digest. These are the invalidation
    /// signals the overlay fingerprint folds in.
    #[test]
    fn notes_revision_tracks_note_changes() {
        let (store, _dir) = setup_store();
        let source = Path::new("notes.toml");

        // Empty table: stable across repeated reads, equal to a second empty
        // store's token (the clean / no-notes case).
        let empty = store.notes_revision().unwrap();
        assert_eq!(
            empty,
            store.notes_revision().unwrap(),
            "empty-table token must be stable"
        );
        let (other_empty_store, _d2) = setup_store();
        assert_eq!(
            empty,
            other_empty_store.notes_revision().unwrap(),
            "two empty notes tables must yield the same token"
        );

        // Seed a note: token moves off the empty digest.
        let mut note = make_note("n1", "load-bearing", -0.5);
        note.mentions = vec!["src/lib.rs".to_string()];
        store
            .upsert_notes_batch(&[note.clone()], source, 100)
            .unwrap();
        let after_seed = store.notes_revision().unwrap();
        assert_ne!(empty, after_seed, "seeding a note must move the token");

        // Sentiment change (the notes-coherence hazard's exact trigger): flips.
        let mut flipped = note.clone();
        flipped.sentiment = 0.5;
        store.upsert_notes_batch(&[flipped], source, 200).unwrap();
        let after_sentiment = store.notes_revision().unwrap();
        assert_ne!(
            after_seed, after_sentiment,
            "a sentiment change must flip the notes-revision token"
        );

        // Text-only change (a `notes update` that leaves sentiment+mentions):
        // still counts as "the notes changed".
        let mut retext = note;
        retext.sentiment = 0.5; // keep the sentiment from the prior step
        retext.text = "rewritten note text".to_string();
        store.upsert_notes_batch(&[retext], source, 300).unwrap();
        let after_text = store.notes_revision().unwrap();
        assert_ne!(
            after_sentiment, after_text,
            "a text-only change must flip the notes-revision token"
        );
    }

    /// Over-invalidation guard: a parent index write that does NOT touch notes
    /// must leave the notes-revision token unchanged. This is the property that
    /// distinguishes the targeted token from `data_version` (which bumps on
    /// every index.db write). Here a re-read with no intervening notes mutation
    /// returns the identical token, and re-inserting the byte-identical note set
    /// is a no-op for the token.
    #[test]
    fn notes_revision_stable_across_no_notes_change() {
        let (store, _dir) = setup_store();
        let source = Path::new("notes.toml");

        let mut a = make_note("a", "alpha", 0.5);
        a.mentions = vec!["src/a.rs".to_string()];
        let mut b = make_note("b", "beta", -1.0);
        b.mentions = vec!["src/b.rs".to_string()];
        store
            .upsert_notes_batch(&[a.clone(), b.clone()], source, 100)
            .unwrap();

        let token = store.notes_revision().unwrap();
        // Re-read with no mutation: identical.
        assert_eq!(
            token,
            store.notes_revision().unwrap(),
            "re-read without a notes change must return the same token"
        );

        // Re-insert the byte-identical note set (an idempotent write): the
        // content identity is unchanged, so the token must not move. (mtime is
        // deliberately excluded from the digest precisely so this holds.)
        store.upsert_notes_batch(&[a, b], source, 999).unwrap();
        assert_eq!(
            token,
            store.notes_revision().unwrap(),
            "re-writing identical notes (only mtime differs) must not move the token"
        );
    }

    /// Order independence: the token is computed `ORDER BY id`, so the insertion
    /// order of the same note set cannot perturb it.
    #[test]
    fn notes_revision_is_order_independent() {
        let (s1, _d1) = setup_store();
        let (s2, _d2) = setup_store();
        let source = Path::new("notes.toml");
        let a = make_note("a", "alpha", 0.5);
        let b = make_note("b", "beta", -0.5);
        s1.upsert_notes_batch(&[a.clone(), b.clone()], source, 100)
            .unwrap();
        s2.upsert_notes_batch(&[b, a], source, 100).unwrap();
        assert_eq!(
            s1.notes_revision().unwrap(),
            s2.notes_revision().unwrap(),
            "same note set in either insertion order must yield the same token"
        );
    }

    #[test]
    fn test_replace_notes_replaces_not_appends() {
        let (store, _dir) = setup_store();
        let source = Path::new("notes.toml");

        // Insert 2 notes
        let notes = vec![
            make_note("n1", "first", 0.0),
            make_note("n2", "second", 0.0),
        ];
        store.upsert_notes_batch(&notes, source, 100).unwrap();
        assert_eq!(store.note_count().unwrap(), 2);

        // Replace with 1 note
        let replacement = vec![make_note("n3", "replacement", 0.0)];
        store
            .replace_notes_for_file(&replacement, source, 200)
            .unwrap();
        assert_eq!(store.note_count().unwrap(), 1);
    }

    #[test]
    fn test_replace_notes_with_empty_deletes() {
        let (store, _dir) = setup_store();
        let source = Path::new("notes.toml");

        let notes = vec![
            make_note("n1", "first", 0.0),
            make_note("n2", "second", 0.5),
        ];
        store.upsert_notes_batch(&notes, source, 100).unwrap();
        assert_eq!(store.note_count().unwrap(), 2);

        // Replace with empty
        store.replace_notes_for_file(&[], source, 200).unwrap();
        assert_eq!(store.note_count().unwrap(), 0);
    }

    #[test]
    fn test_notes_need_reindex_stale() {
        let (store, dir) = setup_store();
        // Create a real temp file so metadata() works
        let notes_file = dir.path().join("notes.toml");
        std::fs::write(&notes_file, "# empty").unwrap();

        // Insert a note with an old mtime (0) so it's stale
        let notes = vec![make_note("n1", "old note", 0.0)];
        store.upsert_notes_batch(&notes, &notes_file, 0).unwrap();

        // Should return Some(current_mtime) because stored mtime (0) < file mtime
        let result = store.notes_need_reindex(&notes_file).unwrap();
        assert!(
            result.is_some(),
            "Should need reindex when stored mtime is old"
        );
    }

    #[test]
    fn test_notes_need_reindex_current() {
        let (store, dir) = setup_store();
        let notes_file = dir.path().join("notes.toml");
        std::fs::write(&notes_file, "# empty").unwrap();

        // Get the file's actual mtime
        let current_mtime = crate::duration_to_mtime_millis(
            notes_file
                .metadata()
                .unwrap()
                .modified()
                .unwrap()
                .duration_since(std::time::UNIX_EPOCH)
                .unwrap(),
        );

        // Insert with the current mtime
        let notes = vec![make_note("n1", "current note", 0.0)];
        store
            .upsert_notes_batch(&notes, &notes_file, current_mtime)
            .unwrap();

        // Should return None — no reindex needed
        let result = store.notes_need_reindex(&notes_file).unwrap();
        assert!(
            result.is_none(),
            "Should not need reindex when mtime matches"
        );
    }

    #[test]
    fn test_note_count() {
        let (store, _dir) = setup_store();
        let source = Path::new("notes.toml");

        assert_eq!(store.note_count().unwrap(), 0);

        let notes = vec![
            make_note("n1", "first", 0.0),
            make_note("n2", "second", -0.5),
            make_note("n3", "third", 1.0),
        ];
        store.upsert_notes_batch(&notes, source, 100).unwrap();
        assert_eq!(store.note_count().unwrap(), 3);
    }

    #[test]
    fn test_note_stats_sentiment() {
        let (store, _dir) = setup_store();
        let source = Path::new("notes.toml");

        // -1 = warning, 0 = neutral, 0.5 = pattern
        let notes = vec![
            make_note("n1", "pain point", -1.0),
            make_note("n2", "neutral obs", 0.0),
            make_note("n3", "good pattern", 0.5),
        ];
        store.upsert_notes_batch(&notes, source, 100).unwrap();

        let stats = store.note_stats().unwrap();
        assert_eq!(stats.total, 3);
        assert_eq!(stats.warnings, 1, "Only -1 should count as warning");
        assert_eq!(stats.patterns, 1, "Only 0.5 should count as pattern");
    }

    // ===== TC-ADV-14: upsert edge cases =====
    //
    // `upsert_notes_batch` binds sentiment as a raw f32, serializes
    // `mentions` via serde_json, and iterates every note inside one
    // transaction. Edge cases covered here:
    //   - NaN sentiment (SQLite stores it; downstream `note_stats` threshold
    //     comparisons must not misclassify).
    //   - Empty mentions vec round-trips as `[]` JSON.
    //   - Larger batches still commit in one transaction without partial
    //     writes or FTS desync.

    /// NaN sentiment is rejected by the SQLite `sentiment REAL NOT NULL`
    /// schema: sqlx binds a Rust NaN as SQL NULL, which fails the NOT NULL
    /// constraint. Document this behaviour so a future schema change (e.g.
    /// relaxing NOT NULL) doesn't silently start letting NaN land in the
    /// table, where `note_stats` threshold comparisons would misclassify.
    #[test]
    fn test_upsert_notes_nan_sentiment_rejected_by_schema() {
        let (store, _dir) = setup_store();
        let source = Path::new("nan.toml");

        let notes = vec![make_note("nan1", "nan sentiment note", f32::NAN)];
        let result = store.upsert_notes_batch(&notes, source, 100);

        let err = result.expect_err("NaN sentiment must fail the NOT NULL constraint");
        let msg = err.to_string();
        assert!(
            msg.contains("NOT NULL")
                || msg.contains("constraint")
                || msg.to_lowercase().contains("null"),
            "error should mention the NOT NULL rejection, got: {msg}"
        );

        // The failed transaction leaves no note behind — no partial write.
        assert_eq!(
            store.note_count().unwrap(),
            0,
            "failed NaN upsert must not have written a row"
        );
    }

    /// Since v29 the `notes.sentiment` CHECK pins the column to the five
    /// discrete values, so infinite sentiment (±Inf) — which pre-v29
    /// round-tripped through SQLite — is now rejected at the schema layer. The
    /// normal write path (`parse_notes_str`) clamps+snaps before reaching the
    /// store, so a raw `Note` carrying ±Inf can only arrive via a bypass; the
    /// CHECK is the backstop that keeps off-grid values out of the table.
    #[test]
    fn test_upsert_notes_infinity_sentiment_rejected_by_check() {
        let (store, _dir) = setup_store();
        let source = Path::new("inf.toml");

        for inf in [f32::INFINITY, f32::NEG_INFINITY] {
            let notes = vec![make_note("inf", "huge", inf)];
            let err = store
                .upsert_notes_batch(&notes, source, 100)
                .expect_err("±Inf sentiment must fail the v29 discrete-value CHECK");
            let msg = err.to_string().to_lowercase();
            assert!(
                msg.contains("check") || msg.contains("constraint"),
                "error should mention the CHECK rejection, got: {msg}"
            );
        }

        // No partial write survived either failed transaction.
        assert_eq!(
            store.note_count().unwrap(),
            0,
            "failed off-grid upsert must not have written a row"
        );
    }

    /// Empty mentions round-trip as `[]` and come back as an empty Vec.
    /// No serde error, no null/empty-string corruption.
    #[test]
    fn test_upsert_notes_empty_mentions_roundtrip() {
        let (store, _dir) = setup_store();
        let source = Path::new("empty_mentions.toml");

        let notes = vec![Note {
            id: "em1".to_string(),
            text: "note with no mentions".to_string(),
            sentiment: 0.5,
            mentions: vec![],
            kind: None,
        }];
        store.upsert_notes_batch(&notes, source, 100).unwrap();

        let summaries = store.list_notes_summaries().unwrap();
        assert_eq!(summaries.len(), 1);
        assert_eq!(summaries[0].id, "em1");
        assert!(
            summaries[0].mentions.is_empty(),
            "empty mentions must round-trip as empty Vec, got {:?}",
            summaries[0].mentions
        );
    }

    /// A multi-hundred batch commits in a single transaction; all rows
    /// land and the FTS counter agrees with the main table.
    #[test]
    fn test_upsert_notes_large_batch_single_transaction() {
        let (store, _dir) = setup_store();
        let source = Path::new("bulk.toml");

        // 500 notes — large enough to exercise the per-note INSERT loop
        // without making the test slow. Unique ids + text guarantee no
        // hash collision.
        let notes: Vec<Note> = (0..500)
            .map(|i| {
                make_note(
                    &format!("bulk_{i}"),
                    &format!("bulk note {i}"),
                    (i % 3) as f32 * 0.5 - 0.5, // -0.5, 0.0, 0.5 cycling
                )
            })
            .collect();

        let written = store
            .upsert_notes_batch(&notes, source, 100)
            .expect("large batch must commit");
        assert_eq!(written, 500);

        // Every row landed.
        assert_eq!(store.note_count().unwrap(), 500);

        // FTS table agrees with main table — no desync (the inner
        // INSERT-OR-REPLACE + FTS DELETE/INSERT contract).
        let summaries = store.list_notes_summaries().unwrap();
        assert_eq!(summaries.len(), 500);
    }

    fn note_with_mentions(id: &str, sentiment: f32, mentions: &[&str]) -> Note {
        Note {
            id: id.to_string(),
            text: format!("note {id}"),
            sentiment,
            mentions: mentions.iter().map(|s| s.to_string()).collect(),
            kind: None,
        }
    }

    /// The defect's core fix: a parent note crosses into a fresh shadow store
    /// (the worktree overlay's `:memory:` store) so the shadow's note-boost
    /// index computes the SAME multiplier the parent would. A `-0.5` note on a
    /// file must demote a chunk in that file in BOTH stores — the masked-and-
    /// re-served overlay hit can no longer skip the sentiment multiplier.
    #[test]
    fn test_copy_notes_from_crosses_boost_into_shadow() {
        let (parent, _pdir) = setup_store();
        let source = Path::new("docs/notes.toml");
        parent
            .upsert_notes_batch(
                &[note_with_mentions("note:0", -0.5, &["src/fragile.rs"])],
                source,
                100,
            )
            .unwrap();

        // The parent demotes a chunk in the noted file.
        let parent_boost = parent
            .cached_note_boost_index()
            .unwrap()
            .boost("src/fragile.rs", "do_thing");
        assert!(
            parent_boost < 1.0,
            "parent must demote the noted file, got {parent_boost}"
        );

        // A fresh shadow store starts empty — boost is the 1.0 no-op (this is
        // the bug: the overlay leg would lose the demotion here).
        let shadow = Store::open_memory().unwrap();
        shadow
            .init(&crate::store::helpers::ModelInfo::default())
            .unwrap();
        let before = shadow
            .cached_note_boost_index()
            .unwrap()
            .boost("src/fragile.rs", "do_thing");
        assert_eq!(before, 1.0, "empty shadow has no note opinion");

        // Copy the parent's notes into the shadow → the note crosses.
        let copied = shadow.copy_notes_from(&parent).unwrap();
        assert_eq!(copied, 1, "one note copied");
        assert_eq!(shadow.note_count().unwrap(), 1);

        // The shadow now computes the IDENTICAL boost the parent does.
        let after = shadow
            .cached_note_boost_index()
            .unwrap()
            .boost("src/fragile.rs", "do_thing");
        assert_eq!(
            after, parent_boost,
            "shadow boost must equal parent boost after copy"
        );

        // Metadata crossed faithfully (id, sentiment, mentions, kind).
        let summaries = shadow.list_notes_summaries().unwrap();
        assert_eq!(summaries.len(), 1);
        assert_eq!(summaries[0].id, "note:0");
        assert_eq!(summaries[0].sentiment, -0.5);
        assert_eq!(summaries[0].mentions, vec!["src/fragile.rs".to_string()]);

        // FTS crossed too — note keyword search works in the shadow.
        assert_eq!(
            shadow.list_notes_summaries().unwrap().len(),
            shadow.note_count().unwrap() as usize,
            "FTS and main table agree in the shadow"
        );
    }

    /// Copying from a parent with no notes is a no-op — the clean-worktree /
    /// no-notes case must leave the shadow's empty notes table untouched and
    /// its boost at the 1.0 no-op.
    #[test]
    fn test_copy_notes_from_empty_parent_is_noop() {
        let (parent, _pdir) = setup_store();
        let shadow = Store::open_memory().unwrap();
        shadow
            .init(&crate::store::helpers::ModelInfo::default())
            .unwrap();

        let copied = shadow.copy_notes_from(&parent).unwrap();
        assert_eq!(copied, 0, "no notes to copy");
        assert_eq!(shadow.note_count().unwrap(), 0);
        assert_eq!(
            shadow
                .cached_note_boost_index()
                .unwrap()
                .boost("src/any.rs", "anything"),
            1.0,
            "empty copy leaves the no-op boost"
        );
    }

    /// Notes spanning multiple source files all cross, and a positive-sentiment
    /// note promotes its mention in the shadow — both boost directions survive
    /// the copy, not just demotion.
    #[test]
    fn test_copy_notes_from_multiple_sources_and_signs() {
        let (parent, _pdir) = setup_store();
        parent
            .upsert_notes_batch(
                &[note_with_mentions("note:0", -1.0, &["src/a.rs"])],
                Path::new("docs/notes.toml"),
                100,
            )
            .unwrap();
        parent
            .upsert_notes_batch(
                &[note_with_mentions("note:1", 1.0, &["src/b.rs"])],
                Path::new("design/notes.toml"),
                200,
            )
            .unwrap();

        let shadow = Store::open_memory().unwrap();
        shadow
            .init(&crate::store::helpers::ModelInfo::default())
            .unwrap();
        let copied = shadow.copy_notes_from(&parent).unwrap();
        assert_eq!(copied, 2, "both notes across both source files copied");
        assert_eq!(shadow.note_count().unwrap(), 2);

        let idx = shadow.cached_note_boost_index().unwrap();
        assert!(
            idx.boost("src/a.rs", "f") < 1.0,
            "negative note demotes in shadow"
        );
        assert!(
            idx.boost("src/b.rs", "g") > 1.0,
            "positive note promotes in shadow"
        );
    }

    /// All five discrete sentiment values survive the copy's f64→f32→REAL
    /// round-trip without tripping the discrete-value CHECK constraint on the
    /// re-insert. These values are exact in both f32 and f64, so the cast can't
    /// drift one off-grid — this pins that property so a future float-handling
    /// change can't silently start rejecting copied notes.
    #[test]
    fn test_copy_notes_from_preserves_discrete_sentiments() {
        let (parent, _pdir) = setup_store();
        let notes: Vec<Note> = [-1.0_f32, -0.5, 0.0, 0.5, 1.0]
            .iter()
            .enumerate()
            .map(|(i, &s)| note_with_mentions(&format!("note:{i}"), s, &["src/x.rs"]))
            .collect();
        parent
            .upsert_notes_batch(&notes, Path::new("docs/notes.toml"), 100)
            .unwrap();

        let shadow = Store::open_memory().unwrap();
        shadow
            .init(&crate::store::helpers::ModelInfo::default())
            .unwrap();
        let copied = shadow
            .copy_notes_from(&parent)
            .expect("copy must not violate the discrete-sentiment CHECK");
        assert_eq!(copied, 5);
        assert_eq!(shadow.note_count().unwrap(), 5);

        let mut sentiments: Vec<f32> = shadow
            .list_notes_summaries()
            .unwrap()
            .into_iter()
            .map(|n| n.sentiment)
            .collect();
        sentiments.sort_by(|a, b| a.partial_cmp(b).unwrap());
        assert_eq!(sentiments, vec![-1.0, -0.5, 0.0, 0.5, 1.0]);
    }
}
