Skip to content
1        10        20        30        40        50        60        70        80        90        100       110       120       130       140       150       1601use crate::db::Database;2use crate::models::{Tag, Ticket};34#[derive(Debug, Clone)]5pub struct TicketDetails {6    pub ticket: Ticket,7    pub tags: Vec<Tag>,8    /// `None` means open; closed tickets record either `done` or `not_planned`.9    pub closed_as: Option<String>,10}1112impl Database {13    pub async fn create_ticket(&self, ticket: &Ticket, tag_names: &[String]) -> Result<(), String> {14        let conn = self.conn().await?;15        conn.execute("BEGIN IMMEDIATE", ())16            .await17            .map_err(|e| e.to_string())?;18        let result: Result<(), String> = async {19            conn.execute(20                "INSERT INTO tickets (id, title, description, creator_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5)",21                turso::params![ticket.id.clone(), ticket.title.clone(), ticket.description.clone(), ticket.creator_id.clone(), ticket.created_at.clone()],22            ).await.map_err(|e| e.to_string())?;23            for raw_name in tag_names {24                let name = raw_name.trim();25                if name.is_empty() {26                    continue;27                }28                conn.execute(29                    "INSERT OR IGNORE INTO tags (id, name) VALUES (?1, ?2)",30                    turso::params![uuid::Uuid::new_v4().to_string(), name],31                )32                .await33                .map_err(|e| e.to_string())?;34                let mut rows = conn35                    .query("SELECT id FROM tags WHERE name=?1", turso::params![name])36                    .await37                    .map_err(|e| e.to_string())?;38                let row = rows39                    .next()40                    .await41                    .map_err(|e| e.to_string())?42                    .ok_or_else(|| "Tag could not be created".to_string())?;43                let tag_id: String = row.get(0).map_err(|e| e.to_string())?;44                conn.execute(45                    "INSERT OR IGNORE INTO ticket_tags (ticket_id, tag_id) VALUES (?1, ?2)",46                    turso::params![ticket.id.clone(), tag_id],47                )48                .await49                .map_err(|e| e.to_string())?;50            }51            Ok(())52        }53        .await;54        match result {55            Ok(()) => conn56                .execute("COMMIT", ())57                .await58                .map(|_| ())59                .map_err(|e| e.to_string()),60            Err(error) => {61                conn.execute("ROLLBACK", ())62                    .await63                    .map_err(|e| format!("{error} (rollback failed: {e})"))?;64                Err(error)65            }66        }67    }6869    pub async fn close_ticket(70        &self,71        ticket_id: &str,72        user_id: &str,73        outcome: &str,74    ) -> Result<bool, String> {75        if !matches!(outcome, "done" | "not_planned") {76            return Err("Ticket outcome must be done or not_planned".to_string());77        }78        let now = chrono::Utc::now().to_rfc3339();79        let changed = self80            .conn()81            .await?82            .execute(83                "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",84                turso::params![outcome, now, ticket_id, user_id],85            )86            .await87            .map_err(|e| e.to_string())?;88        Ok(changed > 0)89    }9091    pub async fn reopen_ticket(&self, ticket_id: &str, user_id: &str) -> Result<bool, String> {92        let changed = self93            .conn()94            .await?95            .execute(96                "UPDATE tickets SET closed_as=NULL, completed_at=NULL WHERE id=?1 AND creator_id=?2 AND closed_as IS NOT NULL",97                turso::params![ticket_id, user_id],98            )99            .await100            .map_err(|e| e.to_string())?;101        Ok(changed > 0)102    }103104    pub async fn done_ticket_count(&self, user_id: &str) -> Result<u64, String> {105        let mut rows = self106            .conn()107            .await?108            .query(109                "SELECT COUNT(*) FROM tickets WHERE creator_id=?1 AND closed_as='done'",110                turso::params![user_id],111            )112            .await113            .map_err(|e| e.to_string())?;114        count_from_rows(&mut rows, "Done ticket count").await115    }116117    pub async fn all_done_ticket_count(&self) -> Result<u64, String> {118        let mut rows = self119            .conn()120            .await?121            .query("SELECT COUNT(*) FROM tickets WHERE closed_as='done'", ())122            .await123            .map_err(|e| e.to_string())?;124        count_from_rows(&mut rows, "Ticket count").await125    }126127    /// Tickets are private to their creator. Anonymous visitors receive no records.128    pub async fn visible_tickets(129        &self,130        viewer: Option<&str>,131    ) -> Result<Vec<TicketDetails>, String> {132        let Some(viewer) = viewer else {133            return Ok(Vec::new());134        };135        let conn = self.conn().await?;136        let mut rows = conn137            .query(138                "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",139                turso::params![viewer],140            )141            .await142            .map_err(|e| e.to_string())?;143        let mut tickets = Vec::new();144        while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {145            let ticket = Ticket {146                id: row.get(0).map_err(|e| e.to_string())?,147                title: row.get(1).map_err(|e| e.to_string())?,148                description: row.get(2).map_err(|e| e.to_string())?,149                creator_id: row.get(3).map_err(|e| e.to_string())?,150                created_at: row.get(4).map_err(|e| e.to_string())?,151            };152            let closed_as: Option<String> = row.get(5).map_err(|e| e.to_string())?;153            let mut tag_rows = conn154                .query(155                    "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",156                    turso::params![ticket.id.clone()],157                )158                .await159                .map_err(|e| e.to_string())?;160            let mut tags = Vec::new();161            while let Some(tag) = tag_rows.next().await.map_err(|e| e.to_string())? {162                tags.push(Tag {163                    id: tag.get(0).map_err(|e| e.to_string())?,164                    name: tag.get(1).map_err(|e| e.to_string())?,165                });166            }167            tickets.push(TicketDetails {168                ticket,169                tags,170                closed_as,171            });172        }173        Ok(tickets)174    }175}176177async fn count_from_rows(rows: &mut turso::Rows, description: &str) -> Result<u64, String> {178    let row = rows179        .next()180        .await181        .map_err(|e| e.to_string())?182        .ok_or_else(|| format!("{description} returned no row"))?;183    let count: i64 = row.get(0).map_err(|e| e.to_string())?;184    u64::try_from(count.max(0)).map_err(|e| e.to_string())185}