Spaces:
Sleeping
Sleeping
File size: 6,501 Bytes
ef78361 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 | """Supabase action items & reports queries.
Module-level functions extracted from supabase_client.py.
All functions use get_supabase_client() from the parent module.
"""
import streamlit as st
def get_action_items(
campaign_id: int,
status: str | None = None,
category: str | None = None,
page: int = 1,
page_size: int = 50,
order_by: str = "created_at",
desc: bool = True,
) -> tuple[list[dict], int]:
"""Get action items for a campaign with filters and sorting.
Args:
order_by: Column to sort by (created_at, priority, category, status)
desc: True for descending, False for ascending
Returns (items, total_count).
"""
from core.supabase_client import get_supabase_client
try:
client = get_supabase_client()
query = (
client.table("action_items")
.select(
"id, campaign_id, trigger_rule_id, category, priority, label, "
"evidence, llm_recommendation, status, assignee_email, "
"created_at, completed_at",
count="planned",
)
.eq("campaign_id", campaign_id)
)
if status:
query = query.eq("status", status)
if category:
query = query.eq("category", category)
offset = (page - 1) * page_size
query = (
query.order(order_by, desc=desc)
.range(offset, offset + page_size - 1)
)
result = query.execute()
return result.data or [], result.count or 0
except Exception:
return [], 0
@st.cache_data(ttl=60)
def get_action_item_stats(campaign_id: int) -> dict:
"""Get action item stats via RPC (COUNT FILTER pattern)."""
_empty = {"pending": 0, "in_progress": 0, "completed": 0, "archived": 0, "total": 0}
try:
from core.supabase_client import get_supabase_client
client = get_supabase_client()
result = client.rpc(
"get_action_item_stats", {"p_campaign_id": campaign_id}
).execute()
data = result.data
if isinstance(data, list) and data:
data = data[0]
if isinstance(data, str):
import json
data = json.loads(data)
return data if isinstance(data, dict) else _empty
except Exception:
return _empty
def update_action_item_status(item_id: str, new_status: str) -> bool:
"""Update action item status. Returns True on success."""
try:
from core.supabase_client import get_supabase_client
client = get_supabase_client()
result = (
client.table("action_items")
.update({"status": new_status})
.eq("id", item_id)
.execute()
)
return bool(result.data)
except Exception:
return False
def delete_action_item(item_id: str) -> bool:
"""Delete action item. Returns True on success."""
try:
from core.supabase_client import get_supabase_client
client = get_supabase_client()
result = (
client.table("action_items")
.delete()
.eq("id", item_id)
.execute()
)
return bool(result.data)
except Exception:
return False
def save_action_items_batch(
campaign_id: int,
user_id: str,
items: list[dict],
report_id: str | None = None,
) -> dict:
"""Save action items with dedup (check existing active items).
Args:
campaign_id: Campaign ID
user_id: User UUID
items: List of dicts with trigger_rule_id, category, priority, label, evidence
report_id: Optional report UUID
Returns:
{"created": N, "skipped": N}
"""
try:
from core.supabase_client import get_supabase_client
client = get_supabase_client()
# Batch dedup check: single query instead of N queries
trigger_ids = [item["trigger_rule_id"] for item in items]
existing_result = (
client.table("action_items")
.select("trigger_rule_id")
.eq("campaign_id", campaign_id)
.in_("trigger_rule_id", trigger_ids)
.in_("status", ["pending", "in_progress"])
.execute()
)
existing_set = {r["trigger_rule_id"] for r in (existing_result.data or [])}
rows_to_insert = []
skipped = 0
for item in items:
if item["trigger_rule_id"] in existing_set:
skipped += 1
continue
rows_to_insert.append({
"campaign_id": campaign_id,
"user_id": user_id,
"report_id": report_id,
"trigger_rule_id": item["trigger_rule_id"],
"category": item["category"],
"priority": item["priority"],
"label": item["label"],
"evidence": item.get("evidence"),
"llm_recommendation": item.get("llm_recommendation"),
"status": "pending",
})
created = 0
if rows_to_insert:
client.table("action_items").insert(rows_to_insert).execute()
created = len(rows_to_insert)
return {"created": created, "skipped": skipped}
except Exception as e:
return {"created": 0, "skipped": 0, "error": str(e)}
def get_campaign_date_range(campaign_id: int) -> tuple[str, str] | None:
"""Get first and last data dates for a campaign via RPC (single query).
Returns:
Tuple of (first_date, last_date) as strings, or None if no data
"""
from core.supabase_client import get_supabase_client
client = get_supabase_client()
result = client.rpc("get_campaign_date_range_agg", {"p_campaign_id": campaign_id}).execute()
data = result.data
# PostgREST may wrap json return as [dict] or dict
if isinstance(data, list) and data:
data = data[0]
if isinstance(data, dict) and data.get("first_date") and data.get("last_date"):
return (data["first_date"], data["last_date"])
return None
def get_report_history_count(campaign_id: int) -> int:
"""Get total count of generated HTML reports for a campaign."""
from core.supabase_client import get_supabase_client
client = get_supabase_client()
result = (
client.table("html_reports")
.select("id", count="planned")
.eq("campaign_id", campaign_id)
.execute()
)
return result.count or 0
|