use crate::db::Database;
use uuid::Uuid;
#[allow(dead_code)]
#[derive(Debug, Clone)]
pub struct Note {
pub id: String,
pub title: String,
pub content: String,
pub category: Option<String>,
pub created_at: String,
pub updated_at: String,
pub tags: Vec<String>,
}
impl Database {
pub async fn create_note(
&self,
title: &str,
content: &str,
category: Option<&str>,
tags: &[String],
) -> Result<String, String> {
let id = Uuid::new_v4().to_string();
let now = chrono::Utc::now().to_rfc3339();
let conn = self.conn().await?;
conn.execute(
"INSERT INTO notes (id, title, content, category, created_at, updated_at) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
turso::params![id.clone(), title, content, category.unwrap_or(""), now.clone(), now],
)
.await
.map_err(|e| e.to_string())?;
for tag in tags {
let clean_tag = tag.trim().to_lowercase();
if !clean_tag.is_empty() {
conn.execute(
"INSERT OR IGNORE INTO note_tags (note_id, tag) VALUES (?1, ?2)",
turso::params![id.clone(), clean_tag],
)
.await
.map_err(|e| e.to_string())?;
}
}
Ok(id)
}
pub async fn get_note_by_id(&self, id: &str) -> Result<Option<Note>, String> {
let conn = self.conn().await?;
let mut rows = conn
.query(
"SELECT id, title, content, category, created_at, updated_at FROM notes WHERE id = ?1",
turso::params![id],
)
.await
.map_err(|e| e.to_string())?;
if let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
let id: String = row.get(0).map_err(|e| e.to_string())?;
let title: String = row.get(1).map_err(|e| e.to_string())?;
let content: String = row.get(2).map_err(|e| e.to_string())?;
let category_raw: String = row.get(3).map_err(|e| e.to_string())?;
let created_at: String = row.get(4).map_err(|e| e.to_string())?;
let updated_at: String = row.get(5).map_err(|e| e.to_string())?;
let category = if category_raw.is_empty() {
None
} else {
Some(category_raw)
};
// Fetch tags
let mut tag_rows = conn
.query(
"SELECT tag FROM note_tags WHERE note_id = ?1",
turso::params![id.clone()],
)
.await
.map_err(|e| e.to_string())?;
let mut tags = Vec::new();
while let Some(tag_row) = tag_rows.next().await.map_err(|e| e.to_string())? {
let tag: String = tag_row.get(0).map_err(|e| e.to_string())?;
tags.push(tag);
}
Ok(Some(Note {
id,
title,
content,
category,
created_at,
updated_at,
tags,
}))
} else {
Ok(None)
}
}
pub async fn update_note(
&self,
id: &str,
title: &str,
content: &str,
category: Option<&str>,
tags: &[String],
) -> Result<(), String> {
let now = chrono::Utc::now().to_rfc3339();
let conn = self.conn().await?;
conn.execute(
"UPDATE notes SET title = ?1, content = ?2, category = ?3, updated_at = ?4 WHERE id = ?5",
turso::params![title, content, category.unwrap_or(""), now, id],
)
.await
.map_err(|e| e.to_string())?;
// Clear existing tags
conn.execute(
"DELETE FROM note_tags WHERE note_id = ?1",
turso::params![id],
)
.await
.map_err(|e| e.to_string())?;
// Add new tags
for tag in tags {
let clean_tag = tag.trim().to_lowercase();
if !clean_tag.is_empty() {
conn.execute(
"INSERT OR IGNORE INTO note_tags (note_id, tag) VALUES (?1, ?2)",
turso::params![id, clean_tag],
)
.await
.map_err(|e| e.to_string())?;
}
}
Ok(())
}
pub async fn delete_note(&self, id: &str) -> Result<(), String> {
let conn = self.conn().await?;
conn.execute("DELETE FROM notes WHERE id = ?1", turso::params![id])
.await
.map_err(|e| e.to_string())?;
conn.execute(
"DELETE FROM note_tags WHERE note_id = ?1",
turso::params![id],
)
.await
.map_err(|e| e.to_string())?;
Ok(())
}
pub async fn search_notes(
&self,
query: &str,
category_filter: Option<&str>,
tag_filter: Option<&str>,
) -> Result<Vec<Note>, String> {
let conn = self.conn().await?;
let q_param = if query.is_empty() {
"".to_string()
} else {
format!("%{query}%")
};
let cat_param = category_filter.unwrap_or("").to_string();
let tag_param = tag_filter.unwrap_or("").to_string();
let mut rows = conn
.query(
"SELECT id, title, content, category, created_at, updated_at FROM notes \
WHERE (?1 = '' OR title LIKE ?1 OR content LIKE ?1) \
AND (?2 = '' OR category = ?2) \
AND (?3 = '' OR EXISTS (SELECT 1 FROM note_tags WHERE note_id = id AND tag = ?3)) \
ORDER BY updated_at DESC",
turso::params![q_param, cat_param, tag_param],
)
.await
.map_err(|e| e.to_string())?;
let mut notes = Vec::new();
while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
let id: String = row.get(0).map_err(|e| e.to_string())?;
let title: String = row.get(1).map_err(|e| e.to_string())?;
let content: String = row.get(2).map_err(|e| e.to_string())?;
let category_raw: String = row.get(3).map_err(|e| e.to_string())?;
let created_at: String = row.get(4).map_err(|e| e.to_string())?;
let updated_at: String = row.get(5).map_err(|e| e.to_string())?;
let category = if category_raw.is_empty() {
None
} else {
Some(category_raw)
};
notes.push(Note {
id,
title,
content,
category,
created_at,
updated_at,
tags: Vec::new(), // Will fetch separately or just fetch later if needed, but wait! We can fetch tags for all of these notes, or just populate them. Actually, displaying the list of notes doesn't strictly require tags if we display title, content preview, and category. But we can fetch them for each note in a simple loop, since it's local sqlite and very fast. Let's fetch them!
});
}
// Fetch tags for each note in the search result
for note in &mut notes {
let mut tag_rows = conn
.query(
"SELECT tag FROM note_tags WHERE note_id = ?1",
turso::params![note.id.clone()],
)
.await
.map_err(|e| e.to_string())?;
while let Some(tag_row) = tag_rows.next().await.map_err(|e| e.to_string())? {
let tag: String = tag_row.get(0).map_err(|e| e.to_string())?;
note.tags.push(tag);
}
}
Ok(notes)
}
pub async fn get_all_categories(&self) -> Result<Vec<String>, String> {
let conn = self.conn().await?;
let mut rows = conn
.query("SELECT DISTINCT category FROM notes WHERE category != '' AND category IS NOT NULL ORDER BY category ASC", ())
.await
.map_err(|e| e.to_string())?;
let mut categories = Vec::new();
while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
let cat: String = row.get(0).map_err(|e| e.to_string())?;
categories.push(cat);
}
Ok(categories)
}
pub async fn get_all_tags(&self) -> Result<Vec<String>, String> {
let conn = self.conn().await?;
let mut rows = conn
.query(
"SELECT DISTINCT tag FROM note_tags WHERE tag != '' ORDER BY tag ASC",
(),
)
.await
.map_err(|e| e.to_string())?;
let mut tags = Vec::new();
while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
let tag: String = row.get(0).map_err(|e| e.to_string())?;
tags.push(tag);
}
Ok(tags)
}
}