#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Parastoo Dashboard — Jira & Google Sheet Sync API Writes ONLY Parastoo's own tasks (Jira: parastoosamaavt / Sheet tab: Parastoo|Graphic). Uses Composio REST API directly (no heavy binary, no extra deps). """ import json, os, sys, time, subprocess, urllib.request, urllib.parse, urllib.error, uuid from datetime import datetime, timezone, timedelta from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer import team_hours_lib as TH JIRA_URL = os.environ.get("JIRA_URL", "https://jira.ekeepa-dev.ir") JIRA_PAT = os.environ.get("JIRA_PAT", "") JIRA_USER = os.environ.get("JIRA_USER", "parastoosamaavt") JIRA_PROJECT = os.environ.get("JIRA_PROJECT", "EMO") SHEET_ID = os.environ.get("SHEET_ID", "1a5IDPDSBsLZgF2pnB8GjkjATfCNEzvo_M1cxsm08eFo") SHEET_TAB = os.environ.get("SHEET_TAB", "Parastoo|Graphic") # Binary lives at $HOME/.composio/composio; it must run with cwd=$HOME COMPOSIO_BIN = os.environ.get("COMPOSIO_BIN", os.path.expanduser("~/.composio/composio")) COMPOSIO_CWD = os.environ.get("COMPOSIO_CWD", os.path.expanduser("~")) # The Google Sheets connected account that has WRITE access to the Parastoo tab COMPOSIO_SHEETS_ACCOUNT = os.environ.get("COMPOSIO_SHEETS_ACCOUNT", "googlesheets_binary-adieu") TEHRAN = timezone(timedelta(hours=3, minutes=30)) CAT_FA = { 'instagram': 'اینستاگرام', 'messenger': 'تلگرام و بله', 'linkedin': 'لینکدین و آپارات', 'design': 'طراحی گرافیک', 'video': 'تدوین ویدیو و AI', 'calendar': 'تقویم محتوایی', 'campaign': 'کمپین و گیمیفیکیشن', 'influencer': 'اینفلوئنسر مارکتینگ', 'analytics': 'تحلیل داده و گزارش', 'meeting': 'جلسات و هماهنگی', 'other': 'سایر' } def to_jalali(gy, gm, gd): g_d_m = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334] jy = 979 if gy > 1600 else 0 gy2 = gy - 621 if gy > 1600 else gy gy3 = gy2 + 1 if gm > 2 else gy2 days = (365 * gy2 + (gy3 + 3) // 4 - (gy3 + 99) // 100 + (gy3 + 399) // 400 - 80 + gd + g_d_m[gm - 1]) jy += 33 * (days // 12053); days %= 12053 jy += 4 * (days // 1461); days %= 1461 if days > 365: jy += (days - 1) // 365; days = (days - 1) % 365 jm = 1 + days // 31 if days < 186 else 7 + (days - 186) // 30 jd = 1 + (days % 31 if days < 186 else (days - 186) % 30) return jy, jm, jd def sheet_date(s): """ISO -> D/M/YYYY (Google Sheets interprets M/D/YYYY for en locale; use D/M for fa).""" try: y, m, d = (int(x) for x in str(s).split("-")) return f"{d}/{m}/{y}" except Exception: return str(s or "") def sheet_week(s): try: y, m, d = (int(x) for x in str(s).split("-")) return str(to_jalali(y, m, d)[1]) except Exception: return "" def iso_week(s): try: from datetime import date y, m, d = (int(x) for x in str(s).split("-")) return date(y, m, d).isocalendar()[1] except Exception: return 0 # ---------------- Jira ---------------- def jira(method, path, data=None): body = json.dumps(data).encode() if data is not None else None req = urllib.request.Request(JIRA_URL + path, data=body, method=method, headers={ "Authorization": f"Bearer {JIRA_PAT}", "Content-Type": "application/json"}) try: with urllib.request.urlopen(req, timeout=30) as r: c = r.read().decode() return json.loads(c) if c else {} except urllib.error.HTTPError as e: raise RuntimeError(f"Jira {e.code}: {e.read().decode()[:200]}") def jira_create(summary, desc, hours): p = {"fields": {"project": {"key": JIRA_PROJECT}, "summary": summary[:250], "description": desc or summary, "issuetype": {"name": "Task"}, "assignee": {"name": JIRA_USER}}} key = jira("POST", "/rest/api/2/issue", p).get("key") if key and hours and float(hours) > 0: try: jira("POST", f"/rest/api/2/issue/{key}/worklog", {"timeSpent": f"{float(hours)}h", "comment": "ثبت خودکار از داشبورد پرستو"}) except Exception as e: print("worklog warn:", e, file=sys.stderr) return key def jira_comment(key, text): try: jira("POST", f"/rest/api/2/issue/{key}/comment", {"body": text}) return True except Exception: return False def jira_close(key): tr = jira("GET", f"/rest/api/2/issue/{key}/transitions").get("transitions", []) for t in tr: nm = (t["name"] + " " + t.get("to", {}).get("name", "")).lower() if any(s in nm for s in ["done", "closed", "resolved", "بسته", "دان"]): jira("POST", f"/rest/api/2/issue/{key}/transitions", {"transition": {"id": t["id"]}}) return t.get("to", {}).get("name", t["name"]) return None def jira_ping(): """Lightweight reachability check: does the Jira API respond with a valid auth?""" try: q = urllib.parse.urlencode({"jql": "project = " + JIRA_PROJECT, "maxResults": 1, "fields": "summary"}) d = jira("GET", f"/rest/api/2/search?{q}") return d.get("total") is not None except Exception: return False def jira_list(maxr=60): q = urllib.parse.urlencode({"jql": f'assignee = "{JIRA_USER}" ORDER BY updated DESC', "maxResults": maxr, "fields": "summary,status,priority,updated"}) out = [] for i in jira("GET", f"/rest/api/2/search?{q}").get("issues", []): f = i["fields"] out.append({"key": i["key"], "summary": f.get("summary"), "status": (f.get("status") or {}).get("name"), "priority": (f.get("priority") or {}).get("name"), "updated": (f.get("updated") or "")[:10]}) return out # ---------------- Composio (Google Sheets) ---------------- def composio_execute(slug, arguments): """Run a Composio tool through the authenticated CLI (session-based auth).""" env = dict(os.environ) env["HOME"] = COMPOSIO_CWD env["PATH"] = os.path.dirname(COMPOSIO_BIN) + ":" + env.get("PATH", "") cmd = [COMPOSIO_BIN, "execute", slug, "-d", json.dumps(arguments)] if slug.startswith("GOOGLESHEETS") and COMPOSIO_SHEETS_ACCOUNT: cmd += ["--account", COMPOSIO_SHEETS_ACCOUNT] out, stderr, rc = "", "", 0 for attempt in range(2): r = subprocess.run(cmd, capture_output=True, text=True, timeout=150, env=env, cwd=COMPOSIO_CWD) out, stderr, rc = (r.stdout or "").strip(), (r.stderr or ""), r.returncode if out: break time.sleep(1.5) if not out: raise RuntimeError(f"Composio empty output after retry (exit {rc}): {stderr[:200]}") j = json.loads(out) if not j.get("successful", True): raise RuntimeError(f"Composio: {str(j.get('error'))[:250]}") return j.get("data", j) def sheet_rows(): d = composio_execute("GOOGLESHEETS_VALUES_GET", {"spreadsheet_id": SHEET_ID, "range": f"'{SHEET_TAB}'!A1:N2000"}) return d.get("values", []) if isinstance(d, dict) else [] def sheet_append(rows): """Append rows to the sheet. Uses an explicit UPDATE at `last_data_row + 1` rather than the APPEND endpoint. Reason: the sheet carries `-` formulas in column H far below the real data, and Google's APPEND treats those as part of the table, so it jumps ~40 rows past the last real entry. Writing directly keeps tasks contiguous with existing data. The range MUST span the same number of columns as the widest row, otherwise values drift into adjacent columns. Columns A..L = 12. """ if not rows: return None width = max((len(r) for r in rows), default=12) last_col = "ABCDEFGHIJKLMNOP"[:max(width, 10)][-1] # last COLUMN LETTER, not the slice start = sheet_last_data_row() + 1 end = start + len(rows) - 1 rng = f"'{SHEET_TAB}'!A{start}:{last_col}{end}" return composio_execute("GOOGLESHEETS_VALUES_UPDATE", { "spreadsheet_id": SHEET_ID, "range": rng, "value_input_option": "USER_ENTERED", "values": rows}) def sheet_last_data_row(): """Highest row index with an owner/task value. Bounded windows keep it to <=3 calls.""" last = 1 windows = [(650, 100), (750, 100), (850, 100)] for start, size in windows: try: d = composio_execute("GOOGLESHEETS_VALUES_GET", {"spreadsheet_id": SHEET_ID, "range": f"'{SHEET_TAB}'!A{start}:J{start + size - 1}"}) except Exception: continue vals = d.get("values", []) if isinstance(d, dict) else [] for i, row in enumerate(vals): if len(row) > 3 and (str(row[2]).strip() or str(row[1]).strip()): last = max(last, start + i) return last # ---------------- HTTP ---------------- # ---------------- SERVER-SIDE TASK STORE ---------------- # Lets the assistant (this chat / cron) push tasks that the dashboard merges into localStorage. STORE_PATH = os.environ.get("TASK_STORE", "/tmp/parastoo_store/tasks.json") def load_store(): try: with open(STORE_PATH, encoding="utf-8") as fh: data = json.load(fh) return data if isinstance(data, list) else [] except Exception: return [] def save_store(store): try: os.makedirs(os.path.dirname(STORE_PATH), exist_ok=True) tmp = STORE_PATH + ".tmp" with open(tmp, "w", encoding="utf-8") as fh: json.dump(store, fh, ensure_ascii=False, indent=1) os.replace(tmp, STORE_PATH) except Exception as exc: print("store save failed:", str(exc)[:150], flush=True) def build_sheet_rows(tasks): """Convert task dicts into sheet rows (A..L) matching the Parastoo|Graphic layout.""" rows = [] for t in tasks: if t.get("demo") or not str(t.get("title") or "").strip(): continue est, act = t.get("est") or 0, t.get("act") or 0 try: estf, actf = float(est or 0), float(act or 0) except Exception: estf, actf = 0.0, 0.0 e_status = "UnderEstimate" if estf > actf else ("OverEstimate" if estf < actf else "-") rows.append([ "", "", t.get("title", ""), "پرستو", sheet_date(t.get("date")), str(est or ""), str(act or ""), {"done": "Done", "prog": "NotDone"}.get(t.get("status"), "NotDone"), e_status, sheet_week(t.get("date")), (t.get("note") or "") + (f" [{t.get('jira')}]" if t.get("jira") else "") + (" [لحظه‌ای]" if t.get("adhoc") else ""), ]) return rows def sheet_tasks_today(target_date=None): """Read the Parastoo sheet, propagate inherited dates, return today's tasks. The sheet uses an 'inherited date' pattern: only the first row of each day has a date in column D; subsequent rows of the same day leave D empty.""" from datetime import datetime, timedelta if target_date is None: target_date = datetime.now(TEHRAN).strftime("%Y-%m-%d") # Normalize target to multiple formats for matching parts = target_date.split("-") ty, tm, td = int(parts[0]), int(parts[1]), int(parts[2]) patterns = [ f"{tm}/{td}/{ty}", # 9/22/2026 f"{td}/{tm}/{ty}", # 22/9/2026 f"{ty}-{tm:02d}-{td:02d}", # 2026-09-22 f"{tm:02d}/{td:02d}/{ty}", # 09/22/2026 ] # Read from row 650 onward (last ~200 rows of data) all_rows = [] for start in range(650, 900, 100): try: d = composio_execute("GOOGLESHEETS_VALUES_GET", { "spreadsheet_id": SHEET_ID, "range": f"\'{SHEET_TAB}\'!A{start}:N{start+99}"}) vals = (d or {}).get("values") or [] all_rows.extend([(start + i, r) for i, r in enumerate(vals)]) if len(vals) < 100: break except: break # Propagate dates: column D (index 3) carries the date current_date = "" dated_rows = [] for idx, row in all_rows: cells = row + [""] * (14 - len(row)) raw_date = str(cells[3]).strip() if raw_date and any(c.isdigit() for c in raw_date): current_date = raw_date task = str(cells[1]).strip() actual = str(cells[5]).strip() status = str(cells[6]).strip() if task: dated_rows.append({ "row": idx, "date": current_date, "task": task, "owner": str(cells[2]).strip() or "پرستو", "estimate": str(cells[4]).strip(), "actual": actual, "status": status, "estimation_status": str(cells[7]).strip(), "note": str(cells[9]).strip() if len(cells) > 9 else "", "clock_in": str(cells[10]).strip() if len(cells) > 10 else "", }) # Filter to today today_tasks = [r for r in dated_rows if any(p in r["date"] for p in patterns)] return {"ok": True, "date": target_date, "count": len(today_tasks), "tasks": today_tasks, "total_scanned": len(dated_rows)} class H(BaseHTTPRequestHandler): def _send(self, obj, code=200): b = json.dumps(obj, ensure_ascii=False).encode() self.send_response(code) for k, v in [("Content-Type", "application/json; charset=utf-8"), ("Content-Length", str(len(b))), ("Access-Control-Allow-Origin", "*"), ("Access-Control-Allow-Headers", "Content-Type"), ("Access-Control-Allow-Methods", "GET,POST,OPTIONS")]: self.send_header(k, v) self.end_headers() self.wfile.write(b) def do_OPTIONS(self): self._send({}) def do_GET(self): try: if self.path.startswith("/api/jira/list"): try: self._send({"ok": True, "issues": jira_list()}) except Exception as e: self._send({"ok": False, "error": str(e)[:200], "jira": False}) elif self.path.startswith("/api/sheet/today"): try: import urllib.parse as _up qs = _up.parse_qs(_up.urlparse(self.path).query) dt = qs.get("date", [None])[0] self._send(sheet_tasks_today(dt)) except Exception as e: self._send({"ok": False, "error": str(e)[:300]}, 500) elif self.path.startswith("/api/jira/health"): try: self._send({"ok": True, "jira": bool(JIRA_PAT), "reachable": jira_ping()}) except Exception as e: self._send({"ok": True, "jira": bool(JIRA_PAT), "reachable": False, "error": str(e)[:150]}) elif self.path.startswith("/api/team/hours"): days = 120 try: qp = urllib.parse.parse_qs(self.path.split("?", 1)[1]) days = max(1, min(int(qp.get("days", ["120"])[0]), 365)) except Exception: pass self._send(TH.sheet_team_cached(composio_execute, days, sheet_id=SHEET_ID)) elif self.path.startswith("/api/tasks/list"): st = load_store() dateq = None try: qp = urllib.parse.parse_qs(self.path.split("?", 1)[1]) dateq = qp.get("date", [None])[0] except Exception: pass if dateq: st = [t for t in st if t.get("date") == dateq] self._send({"ok": True, "count": len(st), "tasks": st, "at": datetime.now(TEHRAN).isoformat()}) elif self.path.startswith("/api/sheet/info"): self._send({"ok": True, "lastDataRow": sheet_last_data_row(), "tail": sheet_rows()[-4:]}) elif self.path.startswith("/health"): self._send({"ok": True, "service": "parastoo-sync", "jira": bool(JIRA_PAT), "composio": os.path.exists(COMPOSIO_BIN), "time": datetime.now(TEHRAN).isoformat()}) else: self._send({"ok": False, "error": "not found"}, 404) except Exception as e: self._send({"ok": False, "error": str(e)[:300]}, 500) def do_POST(self): try: ln = int(self.headers.get("Content-Length", 0)) body = json.loads(self.rfile.read(ln).decode() or "{}") except Exception: body = {} if self.path.startswith("/api/jira/push"): created, closed, failed = [], [], [] for t in body.get("tasks", [])[:40]: try: if t.get("jira"): st = jira_close(t["jira"]) if t.get("status") == "done" else None jira_comment(t["jira"], f"به‌روزرسانی از داشبورد پرستو — {t.get('status')} " f"({t.get('act') or 0} ساعت)") closed.append({"key": t["jira"], "status": st, "existing": True}) continue desc = (f"دسته: {CAT_FA.get(t.get('cat'), t.get('cat'))}\n" f"تاریخ: {t.get('date')}\n" f"برآورد: {t.get('est') or 0} ساعت\nواقعی: {t.get('act') or 0} ساعت\n" f"نوع: {'لحظه‌ای' if t.get('adhoc') else 'برنامه‌ریزی‌شده'}\n" f"یادداشت: {t.get('note') or '—'}") created.append({"key": jira_create(t.get("title", "Untitled"), desc, t.get("act")), "clientId": t.get("id")}) except Exception as e: failed.append({"title": t.get("title"), "error": str(e)[:180]}) self._send({"ok": True, "created": created, "closed": closed, "failed": failed, "at": datetime.now(TEHRAN).isoformat()}) elif self.path.startswith("/api/sheet/push"): rows = [] skipped = 0 for t in body.get("tasks", [])[:100]: # Guard: never write demo/sample rows or title-less rows into the real sheet if t.get("demo") or not str(t.get("title") or "").strip(): skipped += 1 continue est = t.get("est") or 0 act = t.get("act") or 0 try: estf, actf = float(est or 0), float(act or 0) except Exception: estf, actf = 0.0, 0.0 if estf > actf: e_status = "UnderEstimate" elif estf < actf: e_status = "OverEstimate" else: e_status = "-" # columns: J(marker) A(day) B(Task) C(Owner) D(Start Date) E(Est h) # F(Actual) G(Final Status) H(Estimation Status) I(Week) J(Note) rows.append([ "", "", t.get("title", ""), "پرستو", sheet_date(t.get("date")), str(est or ""), str(act or ""), {"done": "Done", "prog": "NotDone"}.get(t.get("status"), "NotDone"), e_status, sheet_week(t.get("date")), (t.get("note") or "") + (f" [{t.get('jira')}]" if t.get("jira") else "") + (" [لحظه‌ای]" if t.get("adhoc") else "") ]) try: last = sheet_last_data_row() r = sheet_append(rows) self._send({"ok": True, "appended": len(rows), "skipped": skipped, "afterRow": last, "updatedRange": (r or {}).get("updates", {}).get("updatedRange") if isinstance(r, dict) else None, "at": datetime.now(TEHRAN).isoformat()}) except Exception as e: self._send({"ok": False, "error": str(e)[:300]}, 500) elif self.path.startswith("/api/sheet/append-row"): try: self._send({"ok": True, "result": sheet_append([body.get("row", [])])}) except Exception as e: self._send({"ok": False, "error": str(e)[:300]}, 500) elif self.path.startswith("/api/tasks/add"): # Server-side task store: lets the assistant (from chat/cron) push tasks that the # dashboard then merges into its localStorage. Writes to the sheet too. try: incoming = body.get("tasks") or ([body] if body.get("title") else []) store = load_store() added, wrote_sheet = [], [] for t in incoming[:60]: title = str(t.get("title") or "").strip() if not title: continue rec = { "id": t.get("id") or ("s" + uuid.uuid4().hex[:10]), "title": title, "cat": t.get("cat") or "other", "date": t.get("date") or datetime.now(TEHRAN).strftime("%Y-%m-%d"), "est": float(t.get("est") or 0), "act": float(t.get("act") or 0), "status": t.get("status") or "todo", "prio": t.get("prio") or "medium", "adhoc": bool(t.get("adhoc")), "note": t.get("note") or "", "source": t.get("source") or "assistant", "created": datetime.now(TEHRAN).isoformat(), "synced_jira": False, "synced_sheet": False, } store.append(rec) added.append(rec) if not t.get("skip_sheet"): wrote_sheet.append(rec) if wrote_sheet: rows = build_sheet_rows(wrote_sheet) try: sheet_append(rows) for r in wrote_sheet: r["synced_sheet"] = True except Exception as she: print("sheet write failed:", str(she)[:150], flush=True) save_store(store) self._send({"ok": True, "added": len(added), "sheetWritten": len(wrote_sheet), "tasks": added, "at": datetime.now(TEHRAN).isoformat()}) except Exception as e: self._send({"ok": False, "error": str(e)[:300]}, 500) elif self.path.startswith("/api/tasks/ack"): # Dashboard calls this once it has merged server tasks into localStorage. try: ids = set(body.get("ids") or []) store = [t for t in load_store() if t.get("id") not in ids] save_store(store) self._send({"ok": True, "remaining": len(store)}) except Exception as e: self._send({"ok": False, "error": str(e)[:300]}, 500) else: self._send({"ok": False, "error": "not found"}, 404) def log_message(self, *a): pass def _prewarm(): """Warm the team-hours cache in the background so the first user request is instant.""" import time as _t _t.sleep(3) try: TH.sheet_team_cached(composio_execute, 120, sheet_id=SHEET_ID) print("team-hours cache prewarmed", flush=True) except Exception as exc: print("prewarm failed:", str(exc)[:120], flush=True) if __name__ == "__main__": port = int(os.environ.get("PORT", "8095")) print(f"parastoo-sync on :{port} | jira={'yes' if JIRA_PAT else 'NO'} " f"composio={'yes' if os.path.exists(COMPOSIO_BIN) else 'NO'}", flush=True) import threading as _th _th.Thread(target=_prewarm, daemon=True).start() ThreadingHTTPServer(("0.0.0.0", port), H).serve_forever()