contract_cli/
db.rs

1// ═══════════════════════════════════════════════════════════════════════════
2// Database layer — operates over the shared finance-core SQLite.
3//
4// Tables read/written: issuers (shared), clients (shared, w/ V7 legal fields),
5// contracts (V7), contract_clauses (V7), number_series (shared).
6// ═══════════════════════════════════════════════════════════════════════════
7
8use rusqlite::{params, Connection, OptionalExtension};
9use serde::{Deserialize, Serialize};
10use std::path::Path;
11
12use crate::error::{AppError, Result};
13use crate::tax::Jurisdiction;
14
15pub use finance_core::entity::Issuer;
16
17pub fn open() -> Result<Connection> {
18    let paths = finance_core::paths::Paths::resolve()?;
19    Ok(finance_core::db::open(&paths)?)
20}
21
22pub fn open_at(path: &Path) -> Result<Connection> {
23    Ok(finance_core::db::open_at(path)?)
24}
25
26// ─── Issuers (shared with invoice-cli) ────────────────────────────────────
27
28fn addr_to_text(lines: &[String]) -> String {
29    lines.join("\n")
30}
31fn text_to_addr(s: &str) -> Vec<String> {
32    s.split('\n').map(|l| l.to_string()).collect()
33}
34
35pub fn issuer_create(conn: &Connection, issuer: &Issuer) -> Result<i64> {
36    conn.execute(
37        "INSERT INTO issuers (slug, name, legal_name, jurisdiction, tax_registered,
38                              tax_id, company_no, tagline, address, email, phone,
39                              bank_details, default_template,
40                              currency, symbol, number_format, logo_path,
41                              default_output_dir, default_notes)
42         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19)",
43        params![
44            issuer.slug,
45            issuer.name,
46            issuer.legal_name,
47            issuer.jurisdiction.as_str(),
48            issuer.tax_registered as i32,
49            issuer.tax_id,
50            issuer.company_no,
51            issuer.tagline,
52            addr_to_text(&issuer.address),
53            issuer.email,
54            issuer.phone,
55            issuer.bank_details,
56            issuer.default_template,
57            issuer.currency,
58            issuer.symbol,
59            issuer.number_format,
60            issuer.logo_path,
61            issuer.default_output_dir,
62            issuer.default_notes,
63        ],
64    )?;
65    Ok(conn.last_insert_rowid())
66}
67
68pub fn issuer_list(conn: &Connection) -> Result<Vec<Issuer>> {
69    let mut stmt = conn.prepare(
70        "SELECT id, slug, name, legal_name, jurisdiction, tax_registered,
71                tax_id, company_no, tagline, address, email, phone,
72                bank_details, default_template,
73                currency, symbol, number_format, logo_path,
74                default_output_dir, default_notes
75         FROM issuers ORDER BY slug",
76    )?;
77    let rows = stmt
78        .query_map([], |row| {
79            Ok(Issuer {
80                id: row.get(0)?,
81                slug: row.get(1)?,
82                name: row.get(2)?,
83                legal_name: row.get(3)?,
84                jurisdiction: Jurisdiction::from_str(&row.get::<_, String>(4)?)
85                    .unwrap_or(Jurisdiction::Custom),
86                tax_registered: row.get::<_, i32>(5)? != 0,
87                tax_id: row.get(6)?,
88                company_no: row.get(7)?,
89                tagline: row.get(8)?,
90                address: text_to_addr(&row.get::<_, String>(9)?),
91                email: row.get(10)?,
92                phone: row.get(11)?,
93                bank_details: row.get(12)?,
94                default_template: row.get(13)?,
95                currency: row.get(14)?,
96                symbol: row.get(15)?,
97                number_format: row.get(16)?,
98                logo_path: row.get(17)?,
99                default_output_dir: row.get(18)?,
100                default_notes: row.get(19)?,
101            })
102        })?
103        .collect::<std::result::Result<Vec<_>, _>>()?;
104    Ok(rows)
105}
106
107pub fn issuer_by_slug(conn: &Connection, slug: &str) -> Result<Issuer> {
108    for i in issuer_list(conn)? {
109        if i.slug == slug {
110            return Ok(i);
111        }
112    }
113    let lower = slug.to_lowercase();
114    let matches: Vec<Issuer> = issuer_list(conn)?
115        .into_iter()
116        .filter(|i| i.slug.contains(slug) || i.name.to_lowercase().contains(&lower))
117        .collect();
118    match matches.len() {
119        0 => Err(AppError::NotFound(format!("issuer '{slug}'"))),
120        1 => Ok(matches.into_iter().next().unwrap()),
121        _ => Err(AppError::Ambiguous(format!(
122            "issuer '{slug}' matches {}",
123            matches
124                .iter()
125                .map(|m| m.slug.as_str())
126                .collect::<Vec<_>>()
127                .join(", ")
128        ))),
129    }
130}
131
132pub fn issuer_update(conn: &Connection, issuer: &Issuer) -> Result<()> {
133    let affected = conn.execute(
134        "UPDATE issuers SET
135             name = ?1, legal_name = ?2, jurisdiction = ?3, tax_registered = ?4,
136             tax_id = ?5, company_no = ?6, tagline = ?7, address = ?8,
137             email = ?9, phone = ?10, bank_details = ?11, default_template = ?12,
138             currency = ?13, symbol = ?14, number_format = ?15, logo_path = ?16,
139             default_output_dir = ?17, default_notes = ?18
140         WHERE slug = ?19",
141        params![
142            issuer.name,
143            issuer.legal_name,
144            issuer.jurisdiction.as_str(),
145            issuer.tax_registered as i32,
146            issuer.tax_id,
147            issuer.company_no,
148            issuer.tagline,
149            addr_to_text(&issuer.address),
150            issuer.email,
151            issuer.phone,
152            issuer.bank_details,
153            issuer.default_template,
154            issuer.currency,
155            issuer.symbol,
156            issuer.number_format,
157            issuer.logo_path,
158            issuer.default_output_dir,
159            issuer.default_notes,
160            issuer.slug,
161        ],
162    )?;
163    if affected == 0 {
164        return Err(AppError::NotFound(format!("issuer '{}'", issuer.slug)));
165    }
166    Ok(())
167}
168
169pub fn issuer_delete(conn: &Connection, slug: &str) -> Result<()> {
170    let affected = conn.execute("DELETE FROM issuers WHERE slug = ?1", params![slug])?;
171    if affected == 0 {
172        return Err(AppError::NotFound(format!("issuer '{slug}'")));
173    }
174    Ok(())
175}
176
177// ─── Clients (shared, with V7 legal fields) ───────────────────────────────
178
179#[derive(Debug, Clone, Serialize, Deserialize)]
180pub struct Client {
181    pub id: i64,
182    pub slug: String,
183    pub name: String,
184    pub legal_name: Option<String>,
185    pub company_no: Option<String>,
186    pub legal_jurisdiction: Option<String>,
187    pub attn: Option<String>,
188    pub country: Option<String>,
189    pub tax_id: Option<String>,
190    pub address: Vec<String>,
191    pub email: Option<String>,
192    pub notes: Option<String>,
193    pub default_issuer_slug: Option<String>,
194    pub default_template: Option<String>,
195}
196
197pub fn client_create(conn: &Connection, c: &Client) -> Result<i64> {
198    conn.execute(
199        "INSERT INTO clients (slug, name, attn, country, tax_id, address, email, notes,
200                              default_issuer_slug, default_template,
201                              legal_name, company_no, legal_jurisdiction)
202         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)",
203        params![
204            c.slug,
205            c.name,
206            c.attn,
207            c.country,
208            c.tax_id,
209            addr_to_text(&c.address),
210            c.email,
211            c.notes,
212            c.default_issuer_slug,
213            c.default_template,
214            c.legal_name,
215            c.company_no,
216            c.legal_jurisdiction,
217        ],
218    )?;
219    Ok(conn.last_insert_rowid())
220}
221
222pub fn client_list(conn: &Connection) -> Result<Vec<Client>> {
223    let mut stmt = conn.prepare(
224        "SELECT id, slug, name, attn, country, tax_id, address, email, notes,
225                default_issuer_slug, default_template,
226                legal_name, company_no, legal_jurisdiction
227         FROM clients ORDER BY slug",
228    )?;
229    let rows = stmt
230        .query_map([], |row| {
231            Ok(Client {
232                id: row.get(0)?,
233                slug: row.get(1)?,
234                name: row.get(2)?,
235                attn: row.get(3)?,
236                country: row.get(4)?,
237                tax_id: row.get(5)?,
238                address: text_to_addr(&row.get::<_, String>(6)?),
239                email: row.get(7)?,
240                notes: row.get(8)?,
241                default_issuer_slug: row.get(9)?,
242                default_template: row.get(10)?,
243                legal_name: row.get(11)?,
244                company_no: row.get(12)?,
245                legal_jurisdiction: row.get(13)?,
246            })
247        })?
248        .collect::<std::result::Result<Vec<_>, _>>()?;
249    Ok(rows)
250}
251
252pub fn client_by_slug(conn: &Connection, slug: &str) -> Result<Client> {
253    for c in client_list(conn)? {
254        if c.slug == slug {
255            return Ok(c);
256        }
257    }
258    let lower = slug.to_lowercase();
259    let matches: Vec<Client> = client_list(conn)?
260        .into_iter()
261        .filter(|c| c.slug.contains(slug) || c.name.to_lowercase().contains(&lower))
262        .collect();
263    match matches.len() {
264        0 => Err(AppError::NotFound(format!("client '{slug}'"))),
265        1 => Ok(matches.into_iter().next().unwrap()),
266        _ => Err(AppError::Ambiguous(format!(
267            "client '{slug}' matches {}",
268            matches
269                .iter()
270                .map(|m| m.slug.as_str())
271                .collect::<Vec<_>>()
272                .join(", ")
273        ))),
274    }
275}
276
277pub fn client_update(conn: &Connection, c: &Client) -> Result<()> {
278    let affected = conn.execute(
279        "UPDATE clients SET
280             name = ?1, attn = ?2, country = ?3, tax_id = ?4, address = ?5,
281             email = ?6, notes = ?7, default_issuer_slug = ?8, default_template = ?9,
282             legal_name = ?10, company_no = ?11, legal_jurisdiction = ?12
283         WHERE slug = ?13",
284        params![
285            c.name,
286            c.attn,
287            c.country,
288            c.tax_id,
289            addr_to_text(&c.address),
290            c.email,
291            c.notes,
292            c.default_issuer_slug,
293            c.default_template,
294            c.legal_name,
295            c.company_no,
296            c.legal_jurisdiction,
297            c.slug,
298        ],
299    )?;
300    if affected == 0 {
301        return Err(AppError::NotFound(format!("client '{}'", c.slug)));
302    }
303    Ok(())
304}
305
306pub fn client_delete(conn: &Connection, slug: &str) -> Result<()> {
307    let affected = conn.execute("DELETE FROM clients WHERE slug = ?1", params![slug])?;
308    if affected == 0 {
309        return Err(AppError::NotFound(format!("client '{slug}'")));
310    }
311    Ok(())
312}
313
314// ─── Contracts ────────────────────────────────────────────────────────────
315
316#[derive(Debug, Clone, Serialize, Deserialize)]
317pub struct Contract {
318    pub id: i64,
319    pub number: String,
320    pub kind: String,
321    pub issuer_id: i64,
322    pub client_id: i64,
323    pub title: String,
324    pub effective_date: String,
325    pub end_date: Option<String>,
326    pub term_months: Option<i64>,
327    pub governing_law: String,
328    pub venue: Option<String>,
329    pub status: String,
330    pub sent_at: Option<String>,
331    pub signed_at: Option<String>,
332    pub terminated_at: Option<String>,
333    pub notes: Option<String>,
334    pub fee_type: Option<String>,
335    pub fee_amount_minor: Option<i64>,
336    pub fee_currency: Option<String>,
337    pub fee_schedule: Option<String>,
338    pub terms_json: String,
339    pub clause_pack: String,
340    pub clause_pack_version: String,
341    pub default_template: Option<String>,
342    pub signed_by_us_name: Option<String>,
343    pub signed_by_us_title: Option<String>,
344    pub signed_by_us_at: Option<String>,
345    pub signed_by_them_name: Option<String>,
346    pub signed_by_them_title: Option<String>,
347    pub signed_by_them_at: Option<String>,
348    pub created_at: String,
349    pub updated_at: String,
350}
351
352#[derive(Debug, Clone, Serialize, Deserialize)]
353pub struct ContractClauseRow {
354    pub id: i64,
355    pub contract_id: i64,
356    pub position: i64,
357    pub slug: String,
358    pub heading: Option<String>,
359    pub body: Option<String>,
360}
361
362const SELECT_CONTRACT: &str = "SELECT c.id, c.number, c.kind, c.issuer_id, c.client_id, c.title,
363        c.effective_date, c.end_date, c.term_months, c.governing_law, c.venue,
364        c.status, c.sent_at, c.signed_at, c.terminated_at, c.notes,
365        c.fee_type, c.fee_amount_minor, c.fee_currency, c.fee_schedule,
366        c.terms_json, c.clause_pack, c.clause_pack_version, c.default_template,
367        c.signed_by_us_name, c.signed_by_us_title, c.signed_by_us_at,
368        c.signed_by_them_name, c.signed_by_them_title, c.signed_by_them_at,
369        c.created_at, c.updated_at";
370
371fn row_to_contract(row: &rusqlite::Row) -> rusqlite::Result<Contract> {
372    Ok(Contract {
373        id: row.get(0)?,
374        number: row.get(1)?,
375        kind: row.get(2)?,
376        issuer_id: row.get(3)?,
377        client_id: row.get(4)?,
378        title: row.get(5)?,
379        effective_date: row.get(6)?,
380        end_date: row.get(7)?,
381        term_months: row.get(8)?,
382        governing_law: row.get(9)?,
383        venue: row.get(10)?,
384        status: row.get(11)?,
385        sent_at: row.get(12)?,
386        signed_at: row.get(13)?,
387        terminated_at: row.get(14)?,
388        notes: row.get(15)?,
389        fee_type: row.get(16)?,
390        fee_amount_minor: row.get(17)?,
391        fee_currency: row.get(18)?,
392        fee_schedule: row.get(19)?,
393        terms_json: row.get(20)?,
394        clause_pack: row.get(21)?,
395        clause_pack_version: row.get(22)?,
396        default_template: row.get(23)?,
397        signed_by_us_name: row.get(24)?,
398        signed_by_us_title: row.get(25)?,
399        signed_by_us_at: row.get(26)?,
400        signed_by_them_name: row.get(27)?,
401        signed_by_them_title: row.get(28)?,
402        signed_by_them_at: row.get(29)?,
403        created_at: row.get(30)?,
404        updated_at: row.get(31)?,
405    })
406}
407
408pub fn contract_create(conn: &mut Connection, c: &Contract, clauses: &[ContractClauseRow]) -> Result<i64> {
409    let tx = conn.transaction()?;
410    tx.execute(
411        "INSERT INTO contracts (number, kind, issuer_id, client_id, title,
412                                effective_date, end_date, term_months,
413                                governing_law, venue, status, notes,
414                                fee_type, fee_amount_minor, fee_currency, fee_schedule,
415                                terms_json, clause_pack, clause_pack_version, default_template)
416         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20)",
417        params![
418            c.number,
419            c.kind,
420            c.issuer_id,
421            c.client_id,
422            c.title,
423            c.effective_date,
424            c.end_date,
425            c.term_months,
426            c.governing_law,
427            c.venue,
428            c.status,
429            c.notes,
430            c.fee_type,
431            c.fee_amount_minor,
432            c.fee_currency,
433            c.fee_schedule,
434            c.terms_json,
435            c.clause_pack,
436            c.clause_pack_version,
437            c.default_template,
438        ],
439    )?;
440    let id = tx.last_insert_rowid();
441    for cl in clauses {
442        tx.execute(
443            "INSERT INTO contract_clauses (contract_id, position, slug, heading, body)
444             VALUES (?1, ?2, ?3, ?4, ?5)",
445            params![id, cl.position, cl.slug, cl.heading, cl.body],
446        )?;
447    }
448    tx.commit()?;
449    Ok(id)
450}
451
452pub fn contract_get(conn: &Connection, number: &str) -> Result<Contract> {
453    let sql = format!("{SELECT_CONTRACT} FROM contracts c WHERE c.number = ?1");
454    Ok(conn.query_row(&sql, params![number], row_to_contract)?)
455}
456
457pub fn contract_get_or_404(conn: &Connection, number: &str) -> Result<Contract> {
458    let sql = format!("{SELECT_CONTRACT} FROM contracts c WHERE c.number = ?1");
459    conn.query_row(&sql, params![number], row_to_contract)
460        .optional()?
461        .ok_or_else(|| AppError::NotFound(format!("contract '{number}'")))
462}
463
464pub fn contract_list(
465    conn: &Connection,
466    kind: Option<&str>,
467    status: Option<&str>,
468    issuer_slug: Option<&str>,
469) -> Result<Vec<Contract>> {
470    let mut sql = format!(
471        "{SELECT_CONTRACT} FROM contracts c JOIN issuers s ON s.id = c.issuer_id WHERE 1=1"
472    );
473    let mut p: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
474    if let Some(k) = kind {
475        sql.push_str(" AND c.kind = ?");
476        p.push(Box::new(k.to_string()));
477    }
478    if let Some(st) = status {
479        sql.push_str(" AND c.status = ?");
480        p.push(Box::new(st.to_string()));
481    }
482    if let Some(sl) = issuer_slug {
483        sql.push_str(" AND s.slug = ?");
484        p.push(Box::new(sl.to_string()));
485    }
486    sql.push_str(" ORDER BY c.effective_date DESC");
487    let mut stmt = conn.prepare(&sql)?;
488    let rows = stmt
489        .query_map(
490            rusqlite::params_from_iter(p.iter().map(|b| b.as_ref())),
491            row_to_contract,
492        )?
493        .collect::<std::result::Result<Vec<_>, _>>()?;
494    Ok(rows)
495}
496
497pub fn contract_update_draft(conn: &Connection, c: &Contract) -> Result<()> {
498    let status: Option<String> = conn
499        .query_row(
500            "SELECT status FROM contracts WHERE number = ?1",
501            params![c.number],
502            |r| r.get(0),
503        )
504        .optional()?;
505    let status = status.ok_or_else(|| AppError::NotFound(format!("contract '{}'", c.number)))?;
506    if status != "draft" {
507        return Err(AppError::InvalidInput(format!(
508            "contract '{}' is {status}, not draft — sent/signed contracts are immutable.",
509            c.number
510        )));
511    }
512    let now = chrono::Utc::now().to_rfc3339();
513    conn.execute(
514        "UPDATE contracts SET
515             client_id = ?1, title = ?2, effective_date = ?3, end_date = ?4,
516             term_months = ?5, governing_law = ?6, venue = ?7, notes = ?8,
517             fee_type = ?9, fee_amount_minor = ?10, fee_currency = ?11, fee_schedule = ?12,
518             terms_json = ?13, default_template = ?14, updated_at = ?15
519         WHERE number = ?16",
520        params![
521            c.client_id,
522            c.title,
523            c.effective_date,
524            c.end_date,
525            c.term_months,
526            c.governing_law,
527            c.venue,
528            c.notes,
529            c.fee_type,
530            c.fee_amount_minor,
531            c.fee_currency,
532            c.fee_schedule,
533            c.terms_json,
534            c.default_template,
535            now,
536            c.number,
537        ],
538    )?;
539    Ok(())
540}
541
542pub fn contract_set_status(conn: &Connection, number: &str, status: &str) -> Result<()> {
543    let valid = ["draft", "sent", "signed", "active", "expired", "terminated"];
544    if !valid.contains(&status) {
545        return Err(AppError::InvalidInput(format!(
546            "invalid status '{status}'. Expected one of: {}",
547            valid.join(", ")
548        )));
549    }
550    let now = chrono::Utc::now().to_rfc3339();
551    let affected = match status {
552        "sent" => conn.execute(
553            "UPDATE contracts SET status = ?1, sent_at = COALESCE(sent_at, ?2), updated_at = ?2 WHERE number = ?3",
554            params![status, now, number],
555        )?,
556        "signed" | "active" => conn.execute(
557            "UPDATE contracts SET status = ?1, signed_at = COALESCE(signed_at, ?2), updated_at = ?2 WHERE number = ?3",
558            params![status, now, number],
559        )?,
560        "terminated" => conn.execute(
561            "UPDATE contracts SET status = ?1, terminated_at = COALESCE(terminated_at, ?2), updated_at = ?2 WHERE number = ?3",
562            params![status, now, number],
563        )?,
564        _ => conn.execute(
565            "UPDATE contracts SET status = ?1, updated_at = ?2 WHERE number = ?3",
566            params![status, now, number],
567        )?,
568    };
569    if affected == 0 {
570        return Err(AppError::NotFound(format!("contract '{number}'")));
571    }
572    Ok(())
573}
574
575pub fn contract_record_signature(
576    conn: &Connection,
577    number: &str,
578    side: &str,
579    name: &str,
580    title: Option<&str>,
581    date_iso: &str,
582) -> Result<Contract> {
583    let now = chrono::Utc::now().to_rfc3339();
584    let affected = match side {
585        "us" => conn.execute(
586            "UPDATE contracts SET signed_by_us_name = ?1, signed_by_us_title = ?2, signed_by_us_at = ?3, updated_at = ?4 WHERE number = ?5",
587            params![name, title, date_iso, now, number],
588        )?,
589        "them" => conn.execute(
590            "UPDATE contracts SET signed_by_them_name = ?1, signed_by_them_title = ?2, signed_by_them_at = ?3, updated_at = ?4 WHERE number = ?5",
591            params![name, title, date_iso, now, number],
592        )?,
593        _ => {
594            return Err(AppError::InvalidInput(format!(
595                "side must be 'us' or 'them', got '{side}'"
596            )))
597        }
598    };
599    if affected == 0 {
600        return Err(AppError::NotFound(format!("contract '{number}'")));
601    }
602    // If both sides have signatures, auto-bump status to 'signed' (idempotent)
603    let c = contract_get(conn, number)?;
604    if c.signed_by_us_at.is_some()
605        && c.signed_by_them_at.is_some()
606        && (c.status == "draft" || c.status == "sent")
607    {
608        contract_set_status(conn, number, "signed")?;
609        return contract_get(conn, number);
610    }
611    Ok(c)
612}
613
614pub fn contract_delete(conn: &Connection, number: &str, force: bool) -> Result<()> {
615    let status: Option<String> = conn
616        .query_row(
617            "SELECT status FROM contracts WHERE number = ?1",
618            params![number],
619            |r| r.get(0),
620        )
621        .optional()?;
622    let status = status.ok_or_else(|| AppError::NotFound(format!("contract '{number}'")))?;
623    if status != "draft" && !force {
624        return Err(AppError::InvalidInput(format!(
625            "refusing to delete non-draft contract '{number}' (status='{status}') — pass --force"
626        )));
627    }
628    conn.execute("DELETE FROM contracts WHERE number = ?1", params![number])?;
629    Ok(())
630}
631
632// ─── Contract clauses CRUD ───────────────────────────────────────────────
633
634pub fn clauses_for(conn: &Connection, contract_id: i64) -> Result<Vec<ContractClauseRow>> {
635    let mut stmt = conn.prepare(
636        "SELECT id, contract_id, position, slug, heading, body
637         FROM contract_clauses WHERE contract_id = ?1 ORDER BY position",
638    )?;
639    let rows = stmt
640        .query_map(params![contract_id], |r| {
641            Ok(ContractClauseRow {
642                id: r.get(0)?,
643                contract_id: r.get(1)?,
644                position: r.get(2)?,
645                slug: r.get(3)?,
646                heading: r.get(4)?,
647                body: r.get(5)?,
648            })
649        })?
650        .collect::<std::result::Result<Vec<_>, _>>()?;
651    Ok(rows)
652}
653
654fn require_mutable(conn: &Connection, number: &str) -> Result<Contract> {
655    let c = contract_get_or_404(conn, number)?;
656    if c.status != "draft" {
657        return Err(AppError::InvalidInput(format!(
658            "contract '{number}' is {} — clauses can only be edited on draft.",
659            c.status
660        )));
661    }
662    Ok(c)
663}
664
665pub fn clause_add(
666    conn: &mut Connection,
667    number: &str,
668    slug: &str,
669    heading: Option<&str>,
670    body: Option<&str>,
671    position: Option<i64>,
672) -> Result<ContractClauseRow> {
673    let c = require_mutable(conn, number)?;
674    let tx = conn.transaction()?;
675    // disallow duplicates
676    let exists: Option<i64> = tx
677        .query_row(
678            "SELECT id FROM contract_clauses WHERE contract_id = ?1 AND slug = ?2",
679            params![c.id, slug],
680            |r| r.get(0),
681        )
682        .optional()?;
683    if exists.is_some() {
684        return Err(AppError::InvalidInput(format!(
685            "clause '{slug}' already on contract '{}'",
686            number
687        )));
688    }
689    let count: i64 = tx.query_row(
690        "SELECT COUNT(*) FROM contract_clauses WHERE contract_id = ?1",
691        params![c.id],
692        |r| r.get(0),
693    )?;
694    let insert_pos = position.unwrap_or(count).clamp(0, count);
695    // shift existing
696    tx.execute(
697        "UPDATE contract_clauses SET position = position + 1
698           WHERE contract_id = ?1 AND position >= ?2",
699        params![c.id, insert_pos],
700    )?;
701    tx.execute(
702        "INSERT INTO contract_clauses (contract_id, position, slug, heading, body)
703         VALUES (?1, ?2, ?3, ?4, ?5)",
704        params![c.id, insert_pos, slug, heading, body],
705    )?;
706    let id = tx.last_insert_rowid();
707    tx.commit()?;
708    Ok(ContractClauseRow {
709        id,
710        contract_id: c.id,
711        position: insert_pos,
712        slug: slug.to_string(),
713        heading: heading.map(str::to_string),
714        body: body.map(str::to_string),
715    })
716}
717
718pub fn clause_remove(conn: &mut Connection, number: &str, slug: &str) -> Result<()> {
719    let c = require_mutable(conn, number)?;
720    let tx = conn.transaction()?;
721    let pos: Option<i64> = tx
722        .query_row(
723            "SELECT position FROM contract_clauses WHERE contract_id = ?1 AND slug = ?2",
724            params![c.id, slug],
725            |r| r.get(0),
726        )
727        .optional()?;
728    let pos = pos.ok_or_else(|| AppError::NotFound(format!("clause '{slug}' on '{number}'")))?;
729    tx.execute(
730        "DELETE FROM contract_clauses WHERE contract_id = ?1 AND slug = ?2",
731        params![c.id, slug],
732    )?;
733    tx.execute(
734        "UPDATE contract_clauses SET position = position - 1
735           WHERE contract_id = ?1 AND position > ?2",
736        params![c.id, pos],
737    )?;
738    tx.commit()?;
739    Ok(())
740}
741
742pub fn clause_edit(
743    conn: &Connection,
744    number: &str,
745    slug: &str,
746    heading: Option<&str>,
747    body: Option<&str>,
748) -> Result<()> {
749    let c = require_mutable(conn, number)?;
750    let affected = conn.execute(
751        "UPDATE contract_clauses SET heading = COALESCE(?1, heading), body = COALESCE(?2, body)
752         WHERE contract_id = ?3 AND slug = ?4",
753        params![heading, body, c.id, slug],
754    )?;
755    if affected == 0 {
756        return Err(AppError::NotFound(format!(
757            "clause '{slug}' on contract '{number}'"
758        )));
759    }
760    Ok(())
761}
762
763pub fn clause_move(conn: &mut Connection, number: &str, slug: &str, new_pos: i64) -> Result<()> {
764    let c = require_mutable(conn, number)?;
765    let tx = conn.transaction()?;
766    let cur_pos: i64 = tx
767        .query_row(
768            "SELECT position FROM contract_clauses WHERE contract_id = ?1 AND slug = ?2",
769            params![c.id, slug],
770            |r| r.get(0),
771        )
772        .optional()?
773        .ok_or_else(|| AppError::NotFound(format!("clause '{slug}' on '{number}'")))?;
774    let count: i64 = tx.query_row(
775        "SELECT COUNT(*) FROM contract_clauses WHERE contract_id = ?1",
776        params![c.id],
777        |r| r.get(0),
778    )?;
779    let target = new_pos.clamp(0, count - 1);
780    if target == cur_pos {
781        tx.commit()?;
782        return Ok(());
783    }
784    if target > cur_pos {
785        tx.execute(
786            "UPDATE contract_clauses SET position = position - 1
787               WHERE contract_id = ?1 AND position > ?2 AND position <= ?3",
788            params![c.id, cur_pos, target],
789        )?;
790    } else {
791        tx.execute(
792            "UPDATE contract_clauses SET position = position + 1
793               WHERE contract_id = ?1 AND position >= ?2 AND position < ?3",
794            params![c.id, target, cur_pos],
795        )?;
796    }
797    tx.execute(
798        "UPDATE contract_clauses SET position = ?1 WHERE contract_id = ?2 AND slug = ?3",
799        params![target, c.id, slug],
800    )?;
801    tx.commit()?;
802    Ok(())
803}
804
805pub fn clauses_reset(
806    conn: &mut Connection,
807    number: &str,
808    fresh: &[ContractClauseRow],
809) -> Result<()> {
810    let c = require_mutable(conn, number)?;
811    let tx = conn.transaction()?;
812    tx.execute(
813        "DELETE FROM contract_clauses WHERE contract_id = ?1",
814        params![c.id],
815    )?;
816    for cl in fresh {
817        tx.execute(
818            "INSERT INTO contract_clauses (contract_id, position, slug, heading, body)
819             VALUES (?1, ?2, ?3, ?4, ?5)",
820            params![c.id, cl.position, cl.slug, cl.heading, cl.body],
821        )?;
822    }
823    tx.commit()?;
824    Ok(())
825}
826
827// ─── Numbering ────────────────────────────────────────────────────────────
828
829/// Generate the next contract number for an issuer/year/kind triple. Uses the
830/// shared `number_series` table with `kind = "contract.<kind>"` so contract
831/// sequences live separately from invoice/credit-note sequences.
832pub fn next_contract_number(
833    conn: &Connection,
834    issuer: &Issuer,
835    year: i32,
836    kind: &str,
837) -> Result<String> {
838    let series_kind = format!("contract.{kind}");
839    let seq: i64 = conn.query_row(
840        "INSERT INTO number_series (issuer_id, year, kind, next_seq)
841         VALUES (?1, ?2, ?3, 2)
842         ON CONFLICT(issuer_id, year, kind) DO UPDATE SET next_seq = next_seq + 1
843         RETURNING next_seq - 1",
844        params![issuer.id, year, series_kind],
845        |r| r.get(0),
846    )?;
847    let prefix = match kind {
848        "consulting" => "CTR",
849        "nda" => "NDA",
850        "msa" => "MSA",
851        "sow" => "SOW",
852        "service" => "SVC",
853        _ => "DOC",
854    };
855    Ok(format!("{prefix}-{}-{}-{:04}", issuer.slug, year, seq))
856}
contract_cli/db.rs

contract_cli/
db.rs
