"""FlowDeck — Collections : properties. Découpe A28 de l'ancien app/routers/collections.py (2 622 lignes, 53 routes) — un module par concern, contrat inchangé. """ from __future__ import annotations import json import logging from fastapi import APIRouter, Body, HTTPException, Request from app.db import get_conn logger = logging.getLogger(__name__) router = APIRouter(tags=["collections"], prefix="/db") @router.post("/{collection_id}/property-groups/api") def set_property_groups_api(request: Request, collection_id: int, body: dict = Body(default={})): """API: (re)assign properties to collapsible groups in the table header. Body: ``{"groups": [{"name": "Basics", "property_ids": [1, 2]}]}``. Properties omitted from any group have their group cleared. Empty group names clear. """ groups = body.get("groups", []) with get_conn() as conn: coll = conn.execute("SELECT id FROM collections WHERE id=?", (collection_id,)).fetchone() if not coll: raise HTTPException(status_code=404, detail="Collection not found") conn.execute( "UPDATE collection_properties SET group_name='' WHERE collection_id=?", (collection_id,), ) for grp in groups: gname = (grp.get("name") or "").strip() if not gname: continue for pid in grp.get("property_ids", []) or []: conn.execute( "UPDATE collection_properties SET group_name=? WHERE id=? AND collection_id=?", (gname, pid, collection_id), ) conn.commit() return {"status": "updated"} @router.post("/{collection_id}/properties/api") def create_property_api(request: Request, collection_id: int, body: dict = Body(default={})): """API: create a new property on a collection.""" name = body.get("name", "").strip() if not name: raise HTTPException(status_code=400, detail="name is required") prop_type = body.get("prop_type", "text") options_json = json.dumps(body.get("options", [])) number_format = body.get("number_format", "number") required = int(body.get("required", False)) visible = int(body.get("visible_in_views", True)) validation_json = json.dumps(body.get("validation", {})) group_name = (body.get("group_name") or "").strip() with get_conn() as conn: coll = conn.execute("SELECT id FROM collections WHERE id=?", (collection_id,)).fetchone() if not coll: raise HTTPException(status_code=404, detail="Collection not found") max_pos = conn.execute( "SELECT COALESCE(MAX(position), -1) + 1 FROM collection_properties WHERE collection_id=?", (collection_id,), ).fetchone()[0] try: cur = conn.execute( """INSERT INTO collection_properties (collection_id, name, prop_type, options_json, number_format, position, required, visible_in_views, validation_json, group_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (collection_id, name, prop_type, options_json, number_format, max_pos, required, visible, validation_json, group_name), ) conn.commit() except Exception: raise HTTPException(status_code=409, detail=f"Property '{name}' already exists") from None return {"id": cur.lastrowid, "name": name, "prop_type": prop_type, "group_name": group_name, "status": "created"} @router.put("/properties/{prop_id}/api") def update_property_api(request: Request, prop_id: int, body: dict = Body(default={})): """API: update a property.""" with get_conn() as conn: existing = conn.execute( "SELECT * FROM collection_properties WHERE id=?", (prop_id,) ).fetchone() if not existing: raise HTTPException(status_code=404, detail="Property not found") name = body.get("name", existing["name"]) options_json = json.dumps(body.get("options", json.loads(existing["options_json"]))) number_format = body.get("number_format", existing["number_format"]) required = int(body.get("required", existing["required"])) visible = int(body.get("visible_in_views", existing["visible_in_views"])) if "validation" in body: validation_json = json.dumps(body.get("validation", {})) else: validation_json = existing["validation_json"] if "validation_json" in existing.keys() else "{}" group_name = body.get("group_name", existing["group_name"] if "group_name" in existing.keys() else "") conn.execute( """UPDATE collection_properties SET name=?, options_json=?, number_format=?, required=?, visible_in_views=?, validation_json=?, group_name=? WHERE id=?""", (name, options_json, number_format, required, visible, validation_json, group_name, prop_id), ) conn.commit() return {"id": prop_id, "status": "updated"} @router.delete("/properties/{prop_id}/api") def delete_property_api(request: Request, prop_id: int): """API: delete a property.""" with get_conn() as conn: existing = conn.execute( "SELECT * FROM collection_properties WHERE id=?", (prop_id,) ).fetchone() if not existing: raise HTTPException(status_code=404, detail="Property not found") conn.execute("DELETE FROM collection_properties WHERE id=?", (prop_id,)) conn.commit() return {"id": prop_id, "status": "deleted"} # ── Relations, Rollups, Formulas (v1.5.0) ── # ── Relations, Rollups, Formulas (v1.5.0) ── @router.post("/{collection_id}/properties/relation") def create_relation_property(request: Request, collection_id: int, body: dict = Body(default={})): """Create a relation property between two collections.""" name = body.get("name", "").strip() related_collection_id = body.get("related_collection_id") reverse_name = body.get("reverse_name", "").strip() if not name or not related_collection_id: raise HTTPException(status_code=400, detail="name and related_collection_id are required") with get_conn() as conn: # Verify both collections exist for cid in (collection_id, related_collection_id): if not conn.execute("SELECT id FROM collections WHERE id=?", (cid,)).fetchone(): raise HTTPException(status_code=404, detail=f"Collection {cid} not found") max_pos = conn.execute( "SELECT COALESCE(MAX(position), -1) + 1 FROM collection_properties WHERE collection_id=?", (collection_id,), ).fetchone()[0] cur = conn.execute( """INSERT INTO collection_properties (collection_id, name, prop_type, related_collection_id, reverse_name, position) VALUES (?, ?, 'relation', ?, ?, ?)""", (collection_id, name, related_collection_id, reverse_name, max_pos), ) prop_id = cur.lastrowid # Create reverse relation on the related collection if reverse_name: max_pos2 = conn.execute( "SELECT COALESCE(MAX(position), -1) + 1 FROM collection_properties WHERE collection_id=?", (related_collection_id,), ).fetchone()[0] conn.execute( """INSERT INTO collection_properties (collection_id, name, prop_type, related_collection_id, reverse_name, position) VALUES (?, ?, 'relation', ?, ?, ?)""", (related_collection_id, reverse_name, collection_id, name, max_pos2), ) conn.commit() return {"id": prop_id, "name": name, "prop_type": "relation", "status": "created"} @router.post("/{collection_id}/properties/relation/link") def link_pages(request: Request, collection_id: int, body: dict = Body(default={})): """Link two pages via a relation property.""" property_id = body.get("property_id") source_page_id = body.get("source_page_id") target_page_id = body.get("target_page_id") if not all([property_id, source_page_id, target_page_id]): raise HTTPException(status_code=400, detail="property_id, source_page_id, target_page_id required") with get_conn() as conn: # Get the relation property prop = conn.execute( "SELECT * FROM collection_properties WHERE id=? AND prop_type='relation'", (property_id,), ).fetchone() if not prop: raise HTTPException(status_code=404, detail="Relation property not found") # Update source page's property_values_json source = conn.execute( "SELECT property_values_json FROM collection_pages WHERE id=?", (source_page_id,), ).fetchone() if not source: raise HTTPException(status_code=404, detail="Source page not found") props = json.loads(source["property_values_json"]) current = props.get(str(property_id), []) if not isinstance(current, list): current = [] if target_page_id not in current: current.append(target_page_id) props[str(property_id)] = current conn.execute( "UPDATE collection_pages SET property_values_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (json.dumps(props), source_page_id), ) # Update reverse relation if exists if prop["reverse_name"]: reverse_prop = conn.execute( "SELECT id FROM collection_properties WHERE collection_id=? AND name=? AND prop_type='relation'", (prop["related_collection_id"], prop["reverse_name"]), ).fetchone() if reverse_prop: target = conn.execute( "SELECT property_values_json FROM collection_pages WHERE id=?", (target_page_id,), ).fetchone() if target: tprops = json.loads(target["property_values_json"]) tcurrent = tprops.get(str(reverse_prop["id"]), []) if not isinstance(tcurrent, list): tcurrent = [] if source_page_id not in tcurrent: tcurrent.append(source_page_id) tprops[str(reverse_prop["id"])] = tcurrent conn.execute( "UPDATE collection_pages SET property_values_json=? WHERE id=?", (json.dumps(tprops), target_page_id), ) conn.commit() return {"status": "linked", "source": source_page_id, "target": target_page_id} @router.post("/rollup/compute") def compute_rollup(request: Request, body: dict = Body(default={})): """Compute a rollup aggregation.""" collection_id = body.get("collection_id") relation_property_id = body.get("relation_property_id") target_property_id = body.get("target_property_id") page_id = body.get("page_id") rollup_function = body.get("function", "count") if not all([collection_id, relation_property_id, target_property_id, page_id]): raise HTTPException(status_code=400, detail="collection_id, relation_property_id, target_property_id, page_id required") from app.services.rollup_engine import RollupEngine engine = RollupEngine() result = engine.compute( collection_id, relation_property_id, target_property_id, page_id, rollup_function, ) return {"result": result, "function": rollup_function} @router.post("/formula/evaluate") def evaluate_formula(request: Request, body: dict = Body(default={})): """Evaluate a formula expression.""" expression = body.get("expression", "") context = body.get("context", {}) if not expression: raise HTTPException(status_code=400, detail="expression is required") from app.services.formula_engine import FormulaEngine engine = FormulaEngine() result = engine.evaluate(expression, context) return {"result": result, "expression": expression} # ── v1.7.0 View Management ──