use crate::db::Database;
use crate::models::{Tag, Ticket};
#[derive(Debug, Clone)]
pub struct TicketDetails {
pub ticket: Ticket,
pub tags: Vec<Tag>,
/// `None` means open; closed tickets record either `done` or `not_planned`.
pub closed_as: Option<String>,
}
impl Database {
pub async fn create_ticket(&self, ticket: &Ticket, tag_names: &[String]) -> Result<(), String> {
let conn = self.conn().await?;
conn.execute("BEGIN IMMEDIATE", ())
.await
.map_err(|e| e.to_string())?;
let result: Result<(), String> = async {
conn.execute(
"INSERT INTO tickets (id, title, description, creator_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",
turso::params![ticket.id.clone(), ticket.title.clone(), ticket.description.clone(), ticket.creator_id.clone(), ticket.created_at.clone()],
).await.map_err(|e| e.to_string())?;
for raw_name in tag_names {
let name = raw_name.trim();
if name.is_empty() {
continue;
}
conn.execute(
"INSERT OR IGNORE INTO tags (id, name) VALUES (?1, ?2)",
turso::params![uuid::Uuid::new_v4().to_string(), name],
)
.await
.map_err(|e| e.to_string())?;
let mut rows = conn
.query("SELECT id FROM tags WHERE name=?1", turso::params![name])
.await
.map_err(|e| e.to_string())?;
let row = rows
.next()
.await
.map_err(|e| e.to_string())?
.ok_or_else(|| "Tag could not be created".to_string())?;
let tag_id: String = row.get(0).map_err(|e| e.to_string())?;
conn.execute(
"INSERT OR IGNORE INTO ticket_tags (ticket_id, tag_id) VALUES (?1, ?2)",
turso::params![ticket.id.clone(), tag_id],
)
.await
.map_err(|e| e.to_string())?;
}
Ok(())
}
.await;
match result {
Ok(()) => conn
.execute("COMMIT", ())
.await
.map(|_| ())
.map_err(|e| e.to_string()),
Err(error) => {
conn.execute("ROLLBACK", ())
.await
.map_err(|e| format!("{error} (rollback failed: {e})"))?;
Err(error)
}
}
}
pub async fn close_ticket(
&self,
ticket_id: &str,
user_id: &str,
outcome: &str,
) -> Result<bool, String> {
if !matches!(outcome, "done" | "not_planned") {
return Err("Ticket outcome must be done or not_planned".to_string());
}
let now = chrono::Utc::now().to_rfc3339();
let changed = self
.conn()
.await?
.execute(
"UPDATE tickets SET closed_as=?1, completed_at=CASE WHEN ?1='done' THEN ?2 ELSE NULL END WHERE id=?3 AND creator_id=?4 AND closed_as IS NULL",
turso::params![outcome, now, ticket_id, user_id],
)
.await
.map_err(|e| e.to_string())?;
Ok(changed > 0)
}
pub async fn reopen_ticket(&self, ticket_id: &str, user_id: &str) -> Result<bool, String> {
let changed = self
.conn()
.await?
.execute(
"UPDATE tickets SET closed_as=NULL, completed_at=NULL WHERE id=?1 AND creator_id=?2 AND closed_as IS NOT NULL",
turso::params![ticket_id, user_id],
)
.await
.map_err(|e| e.to_string())?;
Ok(changed > 0)
}
pub async fn done_ticket_count(&self, user_id: &str) -> Result<u64, String> {
let mut rows = self
.conn()
.await?
.query(
"SELECT COUNT(*) FROM tickets WHERE creator_id=?1 AND closed_as='done'",
turso::params![user_id],
)
.await
.map_err(|e| e.to_string())?;
count_from_rows(&mut rows, "Done ticket count").await
}
pub async fn all_done_ticket_count(&self) -> Result<u64, String> {
let mut rows = self
.conn()
.await?
.query("SELECT COUNT(*) FROM tickets WHERE closed_as='done'", ())
.await
.map_err(|e| e.to_string())?;
count_from_rows(&mut rows, "Ticket count").await
}
/// Tickets are private to their creator. Anonymous visitors receive no records.
pub async fn visible_tickets(
&self,
viewer: Option<&str>,
) -> Result<Vec<TicketDetails>, String> {
let Some(viewer) = viewer else {
return Ok(Vec::new());
};
let conn = self.conn().await?;
let mut rows = conn
.query(
"SELECT id, title, description, creator_id, created_at, closed_as FROM tickets WHERE creator_id=?1 ORDER BY (closed_as IS NOT NULL), created_at DESC",
turso::params![viewer],
)
.await
.map_err(|e| e.to_string())?;
let mut tickets = Vec::new();
while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {
let ticket = Ticket {
id: row.get(0).map_err(|e| e.to_string())?,
title: row.get(1).map_err(|e| e.to_string())?,
description: row.get(2).map_err(|e| e.to_string())?,
creator_id: row.get(3).map_err(|e| e.to_string())?,
created_at: row.get(4).map_err(|e| e.to_string())?,
};
let closed_as: Option<String> = row.get(5).map_err(|e| e.to_string())?;
let mut tag_rows = conn
.query(
"SELECT g.id, g.name FROM tags g JOIN ticket_tags tt ON tt.tag_id=g.id WHERE tt.ticket_id=?1 ORDER BY g.name",
turso::params![ticket.id.clone()],
)
.await
.map_err(|e| e.to_string())?;
let mut tags = Vec::new();
while let Some(tag) = tag_rows.next().await.map_err(|e| e.to_string())? {
tags.push(Tag {
id: tag.get(0).map_err(|e| e.to_string())?,
name: tag.get(1).map_err(|e| e.to_string())?,
});
}
tickets.push(TicketDetails {
ticket,
tags,
closed_as,
});
}
Ok(tickets)
}
}
async fn count_from_rows(rows: &mut turso::Rows, description: &str) -> Result<u64, String> {
let row = rows
.next()
.await
.map_err(|e| e.to_string())?
.ok_or_else(|| format!("{description} returned no row"))?;
let count: i64 = row.get(0).map_err(|e| e.to_string())?;
u64::try_from(count.max(0)).map_err(|e| e.to_string())
}