from datetime import date, datetime from pathlib import Path import os import shutil import sqlite3 from flask import Flask, jsonify, render_template, request, send_file ROOT = Path(__file__).resolve().parents[1] DATA_DIR = ROOT / "data" UPLOAD_DIR = DATA_DIR / "uploads" DB_PATH = DATA_DIR / "opc.sqlite" WEIXIN_BASE = Path("/Users/mac/天机阁/地阁/慰心斋") DATA_DIR.mkdir(parents=True, exist_ok=True) UPLOAD_DIR.mkdir(parents=True, exist_ok=True) app = Flask( __name__, template_folder=str(ROOT / "templates"), static_folder=str(ROOT / "static"), ) def db(): conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row return conn def now(): return datetime.utcnow().isoformat() def rows(conn, sql, args=()): return [dict(row) for row in conn.execute(sql, args).fetchall()] def one(conn, sql, args=()): row = conn.execute(sql, args).fetchone() return dict(row) if row else None def init_db(): conn = db() conn.executescript( """ CREATE TABLE IF NOT EXISTS sales_leads ( id INTEGER PRIMARY KEY AUTOINCREMENT, target_customer TEXT NOT NULL, priority TEXT NOT NULL DEFAULT 'P1', status TEXT NOT NULL DEFAULT '待跟进', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS follow_up_records ( id INTEGER PRIMARY KEY AUTOINCREMENT, target_type TEXT NOT NULL, target_id INTEGER NOT NULL, followed_at TEXT NOT NULL DEFAULT '', follower TEXT NOT NULL DEFAULT '慰心', follow_up_method TEXT NOT NULL DEFAULT '记录', content TEXT NOT NULL DEFAULT '', next_action TEXT NOT NULL DEFAULT '', next_follow_up_at TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS business_proposals ( id INTEGER PRIMARY KEY AUTOINCREMENT, customer_or_project_name TEXT NOT NULL, version TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT '草稿', created_date TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS operation_projects ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_name TEXT NOT NULL, project_version TEXT NOT NULL DEFAULT 'v1.0', project_type TEXT NOT NULL DEFAULT 'opportunity', project_status TEXT NOT NULL DEFAULT '', current_stage TEXT NOT NULL DEFAULT '', owner TEXT NOT NULL DEFAULT '慰心', start_date TEXT NOT NULL DEFAULT '', end_date TEXT NOT NULL DEFAULT '', target_customer TEXT NOT NULL DEFAULT '', customer_need TEXT NOT NULL DEFAULT '', expected_contract_amount REAL NOT NULL DEFAULT 0, expected_sign_date TEXT NOT NULL DEFAULT '', sign_probability REAL NOT NULL DEFAULT 0, next_action TEXT NOT NULL DEFAULT '', related_business_proposal_id INTEGER, sop_file_id INTEGER, sop_stage TEXT NOT NULL DEFAULT '', execution_progress REAL NOT NULL DEFAULT 0, current_deliverable TEXT NOT NULL DEFAULT '', risks TEXT NOT NULL DEFAULT '', notes TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS product_versions ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_name TEXT NOT NULL, version TEXT NOT NULL, version_goal TEXT NOT NULL DEFAULT '', feature_list TEXT NOT NULL DEFAULT '', launch_date TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT '规划中', notes TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS finance_records ( id INTEGER PRIMARY KEY AUTOINCREMENT, month TEXT NOT NULL, project_name TEXT NOT NULL DEFAULT '科普（慰心斋）', record_type TEXT NOT NULL, category TEXT NOT NULL DEFAULT '', amount REAL NOT NULL DEFAULT 0, occurred_date TEXT NOT NULL DEFAULT '', notes TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS file_assets ( id INTEGER PRIMARY KEY AUTOINCREMENT, module TEXT NOT NULL, owner_id INTEGER NOT NULL, owner_version TEXT NOT NULL DEFAULT '', file_category TEXT NOT NULL DEFAULT '', file_name TEXT NOT NULL, file_type TEXT NOT NULL DEFAULT '', file_size INTEGER NOT NULL DEFAULT 0, file_path TEXT NOT NULL, is_external INTEGER NOT NULL DEFAULT 0, notes TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS project_tasks ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER NOT NULL, phase TEXT NOT NULL DEFAULT '', milestone TEXT NOT NULL DEFAULT '', task TEXT NOT NULL DEFAULT '', owner TEXT NOT NULL DEFAULT '', due_date TEXT NOT NULL DEFAULT '', blockers TEXT NOT NULL DEFAULT '', notes TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP ); """ ) # Schema migrations try: conn.execute("ALTER TABLE product_versions ADD COLUMN platform TEXT NOT NULL DEFAULT ''") except: pass if one(conn, "SELECT id FROM sales_leads LIMIT 1"): conn.close() return sales = [ ("齐鲁制药", "P0", "跟进中", "多产品线科普年度框架，需推进高层沟通。"), ("百利天恒", "P0", "方案中", "BL-B01D1 上市前医生教育机会，准备方案。"), ("信达生物", "P0", "已签约", "现有科普项目升级/续约，重点保障执行。"), ("三生制药", "P1", "待跟进", "多科室医生教育+患者科普机会。"), ("天广实生物", "P1", "待跟进", "血液肿瘤医生教育机会。"), ] for customer, priority, status, note in sales: cur = conn.execute( "INSERT INTO sales_leads (target_customer, priority, status) VALUES (?,?,?)", (customer, priority, status), ) conn.execute( "INSERT INTO follow_up_records (target_type,target_id,followed_at,content,next_action) VALUES (?,?,?,?,?)", ("sales", cur.lastrowid, date.today().isoformat(), note, "明确下一次沟通人和时间"), ) cur = conn.execute( "INSERT INTO business_proposals (customer_or_project_name,version,description,status,created_date) VALUES (?,?,?,?,?)", ("信达生物", "v1.5", "信达科普项目续约与报价方案", "已提交客户", "2026-05-28"), ) proposal_id = cur.lastrowid proposal_dir = WEIXIN_BASE / "2、业务方案/信达/v1.5" for category, names in { "方案": ["整体方案.pptx", "整体方案.pdf"], "成本": ["业务报价-2亿方案.xlsx", "业务报价-5250万方案.xlsx", "5、最新报价.xlsx"], "SOP": ["SOP.docx"], "财务流程": ["财务流程.docx"], }.items(): for name in names: add_file_index(conn, "proposal", proposal_id, "v1.5", category, proposal_dir / name, external=True) projects = [ ("圆心科技 科普文章项目", "v2026-文章", "execution", "SOP 执行中", "内容生产", 55, "文章内容生产与审核执行中"), ("圆心科技 科普视频项目", "v2026-视频", "execution", "SOP 执行中", "内容生产", 45, "视频脚本、拍摄与审核推进"), ("圆心科技 科普专访项目", "v2026-专访", "opportunity", "方案已提交", "商务推进", 0, "专访项目推动签约"), ] op_dir = WEIXIN_BASE / "3、运营方案" for name, version, kind, status, stage, progress, note in projects: cur = conn.execute( """INSERT INTO operation_projects (project_name,project_version,project_type,project_status,current_stage,target_customer,customer_need, expected_contract_amount,expected_sign_date,sign_probability,next_action,sop_stage,execution_progress,current_deliverable) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (name, version, kind, status, stage, "圆心科技", "科普内容项目执行与管理", 0 if kind == "execution" else 200, "2026-06", 100 if kind == "execution" else 70, "补齐版本要求文件并更新下一节点", stage, progress, note), ) conn.execute( "INSERT INTO follow_up_records (target_type,target_id,followed_at,content,next_action) VALUES (?,?,?,?,?)", ("operation", cur.lastrowid, date.today().isoformat(), note, "补齐版本要求文件并更新下一节点"), ) file_map = [ (1, "v2026-文章", "项目方案", "圆心科技--科普文章项目(1).pptx"), (2, "v2026-视频", "项目方案", "圆心科技-科普视频项目(1).pptx"), (3, "v2026-专访", "项目方案", "圆心科技-科普专访项目-2026年(1).pdf"), (1, "v2026-文章", "项目管理手册", "圆心科技《项目管理手册》-2026年.pdf"), (2, "v2026-视频", "审核标准", "科普项目-审核标准（文章-视频-音频）.pdf"), ] for project_id, version, category, filename in file_map: add_file_index(conn, "operation", project_id, version, category, op_dir / filename, external=True) products = [ ("妙手医生服务小程序", "v1.1", "视频任务增强 + 积分商城", "草稿箱、批量上传、积分商城、消息通知", "2026-Q3", "规划中", "科普平台"), ("数字化营销后台管理系统", "v1.2", "运营数据看板 + 智能审核", "医生活跃、任务完成率、AI 预审、渠道数据上报", "2026-Q3", "设计中", "真研平台"), ("妙手患者服务", "v0.5", "科普浏览 + 医生主页 MVP", "科普文章/视频浏览、医生主页、搜索", "2026-Q3", "规划中", "科普平台"), ("数字人内容平台", "v0.1", "基础数字人视频生成 MVP", "预设形象、AI 配音、脚本驱动、简单模板", "2026-Q3", "规划中", "科普平台"), ("渠道分发引擎", "v1.0", "六渠道统一分发", "分发 API、内容适配、分发排期、效果追踪", "2027-Q1", "规划中", "科普平台"), ] for product in products: cur = conn.execute( "INSERT INTO product_versions (product_name,version,version_goal,feature_list,launch_date,status,platform) VALUES (?,?,?,?,?,?,?)", product, ) conn.execute( "INSERT INTO follow_up_records (target_type,target_id,followed_at,content,next_action) VALUES (?,?,?,?,?)", ("product", cur.lastrowid, date.today().isoformat(), f"{product[0]} {product[1]}：{product[2]}", "按路线图推进"), ) for month, record_type, category, amount, notes in [ ("2026-05", "revenue", "信达生物续约确认收入", 120, "信达项目阶段确收"), ("2026-06", "revenue", "信达生物续约确认收入", 80, "信达项目尾款预估"), ("2026-05", "cost_expense", "内容生产", 32, "医生劳务与内容制作"), ("2026-05", "cost_expense", "运营管理", 16, "项目管理与渠道协同"), ("2026-06", "cost_expense", "渠道分发", 24, "投放与分发费用"), ]: conn.execute( "INSERT INTO finance_records (month,record_type,category,amount,occurred_date,notes) VALUES (?,?,?,?,?,?)", (month, record_type, category, amount, f"{month}-01", notes), ) # Seed project tasks for 信达科普文章项目 (project_id=1) tasks_seed = [ ("阶段1：渠道与商务确认", "商务对接", "合同签订", "Anna", "2026-06-30", "法务审核中", "合同签订后开始执行"), ("阶段1：渠道与商务确认", "官媒渠道确认", "沟通官媒确定", "段丽华", "2026-06-30", "官媒尽力推，以先达成合作为准", "集团支持"), ("阶段1：渠道与商务确认", "官媒渠道确认", "官媒合作签约", "段丽华", "2026-06-18", "", "官媒确认细节"), ("阶段2：系统与标准搭建", "系统开发上线", "音频专访系统开发上线", "戴敏/梁军营", "2026-06-18", "客户比较着急执行，需要技术的资源", ""), ("阶段2：系统与标准搭建", "系统开发上线", "精品视频系统开发上线", "戴敏/梁军营", "2026-06-25", "", ""), ("阶段2：系统与标准搭建", "标准与培训", "业务执行手册SOP", "胡龙飞", "2026-06-12", "", "系统开发上线"), ("阶段3：人员与审核入驻", "团队组建", "医学审核人员到位", "胡龙飞", "2026-06-15", "", "审核人员招聘"), ("阶段3：人员与审核入驻", "团队组建", "视频制作人员到位", "胡龙飞", "2026-06-18", "", "项目经理招聘"), ("阶段4：供应链与制作", "供应商准入", "准入拍摄/剪辑/主持人", "胡龙飞/侯亚凤", "2026-06-18", "", ""), ("阶段2：系统与标准搭建", "脚本生产及审核", "生产脚本", "军营", "2026-06-12", "脚本目前生产比较机械，需要提前准备", "细分标签领域完成"), ] for phase, milestone, task, owner, due_date, blockers, notes in tasks_seed: conn.execute( "INSERT INTO project_tasks (project_id,phase,milestone,task,owner,due_date,blockers,notes) VALUES (?,?,?,?,?,?,?,?)", (1, phase, milestone, task, owner, due_date, blockers, notes), ) conn.commit() conn.close() def add_file_index(conn, module, owner_id, owner_version, category, path, external=True): path = Path(path) if not path.exists(): return conn.execute( """INSERT INTO file_assets (module,owner_id,owner_version,file_category,file_name,file_type,file_size,file_path,is_external) VALUES (?,?,?,?,?,?,?,?,?)""", (module, owner_id, owner_version, category, path.name, path.suffix.lower().lstrip("."), path.stat().st_size, str(path), 1 if external else 0), ) def latest_followup(conn, target_type, target_id): row = one( conn, "SELECT content FROM follow_up_records WHERE target_type=? AND target_id=? ORDER BY followed_at DESC, id DESC LIMIT 1", (target_type, target_id), ) return row["content"] if row else "" def attach_common(conn, resource, items): target_map = {"sales": "sales", "proposals": "proposal", "operations": "operation", "products": "product"} for item in items: if resource in target_map: item["followups"] = rows( conn, "SELECT * FROM follow_up_records WHERE target_type=? AND target_id=? ORDER BY followed_at DESC, id DESC", (target_map[resource], item["id"]), ) item["latest_follow_up_record"] = item["followups"][0]["content"] if item["followups"] else "" if resource == "proposals": item["files"] = rows(conn, "SELECT * FROM file_assets WHERE module='proposal' AND owner_id=? ORDER BY id DESC", (item["id"],)) if resource == "operations": item["files"] = rows(conn, "SELECT * FROM file_assets WHERE module='operation' AND owner_id=? ORDER BY id DESC", (item["id"],)) return items def monthly_finance(conn, tenant="科普·无界"): from datetime import date today = date.today() # 6 months: 3 before + current + 2 after from dateutil.relativedelta import relativedelta start = today + relativedelta(months=-3) months = [] for i in range(6): m = start + relativedelta(months=i) months.append(m.strftime("%Y-%m")) data = [] for month in months: col_month = month.replace("-", "_") col_rev = f"rev_{col_month}" col_gross = f"gross_{col_month}" # Only project_finances has columns for 2026-06 through 2026-09 if month in ["2026-06", "2026-07", "2026-08", "2026-09"]: revenue = one(conn, f"SELECT COALESCE(SUM({col_rev}),0) AS v FROM project_finances WHERE tenant=?", (tenant,))["v"] gross = one(conn, f"SELECT COALESCE(SUM({col_gross}),0) AS v FROM project_finances WHERE tenant=?", (tenant,))["v"] else: revenue = 0 gross = 0 data.append({ "month": month, "revenue": revenue, "labor": 0, "expense": 0, "purchase": 0, "net_profit": gross, }) return data @app.route("/") def index(): return render_template("index.html") @app.route("/api/bootstrap") def bootstrap(): tenant = request.args.get("tenant", "科普·无界") conn = db() try: def q(sql, *args): return rows(conn, sql, args) sales = attach_common(conn, "sales", q("SELECT * FROM sales_leads WHERE tenant=? ORDER BY id DESC", tenant)) proposals = attach_common(conn, "proposals", q("SELECT * FROM business_proposals WHERE tenant=? ORDER BY id DESC", tenant)) operations = attach_common(conn, "operations", q("SELECT * FROM operation_projects WHERE tenant=? ORDER BY id DESC", tenant)) products = attach_common(conn, "products", q("SELECT * FROM product_versions WHERE tenant=? ORDER BY id DESC", tenant)) finance = q("SELECT * FROM finance_records WHERE tenant=? ORDER BY month DESC, id DESC", tenant) tasks = q("SELECT * FROM project_tasks WHERE tenant=? ORDER BY phase, sort_order, id", tenant) pfs = q("SELECT * FROM project_finances WHERE tenant=? ORDER BY id DESC", tenant) current_month = "2026-06" # Finance aggregates — from project_finances (project-based) def pf_sum(field): return sum(x[field] or 0 for x in pfs) rev_month = pf_sum("rev_2026_06") gross_month = pf_sum("gross_2026_06") rev_q2 = pf_sum("rev_2026_06") gross_q2 = pf_sum("gross_2026_06") rev_annual = rev_q2 gross_annual = gross_q2 # Contract aggregates — time-based signed_amount = sum(x["expected_contract_amount"] or 0 for x in operations if x["project_status"] == "已签约") from datetime import date today = date.today() def contract_in_period(op, start, end): if op["project_status"] != "已签约": return False try: d = date.fromisoformat(op["created_at"][:10]) return start <= d <= end except: return False signed_annual = sum(x["expected_contract_amount"] or 0 for x in operations if contract_in_period(x, date(2026,1,1), date(2026,12,31))) signed_q2 = sum(x["expected_contract_amount"] or 0 for x in operations if contract_in_period(x, date(2026,4,1), date(2026,6,30))) signed_month = sum(x["expected_contract_amount"] or 0 for x in operations if contract_in_period(x, date(2026,6,1), date(2026,6,30))) pipeline_amount = sum(x["expected_contract_amount"] or 0 for x in operations if x["project_status"] not in ["已签约","已丢单","已归档","已完成"]) signed_not_executed = sum(x["expected_contract_amount"] or 0 for x in operations if x["project_type"] == "execution" and x["execution_progress"] < 100) summary = { "project_name": "科普（慰心斋）", "metrics": { "p0_customers": len([x for x in sales if x["priority"] == "P0"]), "active_sales": len([x for x in sales if x["status"] in ["待跟进", "跟进中", "方案中", "商务谈判"]]), "execution_projects": len([x for x in operations if x["project_type"] == "execution"]), "risk_projects": len([x for x in operations if x["project_status"] == "有风险" or x["risks"]]), "monthly_revenue": rev_month, "monthly_net_profit": gross_month, "monthly_gross": gross_month, "upcoming_products": len([x for x in products if x["status"] in ["规划中", "设计中", "开发中", "测试中"]]), "total_projects": len(operations), "total_proposals": len(proposals), "total_products": len(products), # Extended finance metrics "signed_amount": signed_amount, "signed_annual": signed_annual, "signed_q2": signed_q2, "signed_month": signed_month, "pipeline_amount": pipeline_amount, "revenue_annual": rev_annual, "revenue_q2": rev_q2, "gross_annual": gross_annual, "gross_q2": gross_q2, "signed_not_executed": signed_not_executed, }, "recent": q("SELECT * FROM follow_up_records WHERE tenant=? ORDER BY id DESC LIMIT 8", tenant), "risks": [{"title": "执行提醒", "content": x["next_action"]} for x in operations if x["next_action"]][:5], } return jsonify({"summary": summary, "sales": sales, "proposals": proposals, "operations": operations, "products": products, "finance": finance, "projectFinances": pfs, "financeMonthly": monthly_finance(conn, tenant), "tasks": tasks, "tenant": tenant, "tenants": ["科普·无界","科研·无界","医患·无界"]}) finally: conn.close() TABLES = { "sales": ("sales_leads", ["target_customer", "priority", "status", "tenant"]), "proposals": ("business_proposals", ["customer_or_project_name", "version", "description", "status", "created_date", "tenant"]), "operations": ("operation_projects", ["project_name", "project_version", "project_type", "project_status", "current_stage", "owner", "target_customer", "customer_need", "expected_contract_amount", "expected_sign_date", "sign_probability", "next_action", "sop_stage", "execution_progress", "current_deliverable", "risks", "notes", "tenant"]), "products": ("product_versions", ["product_name", "version", "version_goal", "feature_list", "launch_date", "status", "platform", "notes", "tenant"]), "finance": ("finance_records", ["month", "project_name", "record_type", "category", "amount", "occurred_date", "notes", "tenant"]), "tasks": ("project_tasks", ["project_id", "phase", "milestone", "task", "owner", "due_date", "blockers", "notes", "status", "sort_order", "tenant"]), "projectFinances": ("project_finances", ["project_id", "tenant", "business_type", "customer_name", "sign_amount", "sign_month", "status", "sales_person", "rev_2026_06", "rev_2026_07", "rev_2026_08", "rev_2026_09", "gross_2026_06", "gross_2026_07", "gross_2026_08", "gross_2026_09"]), } @app.route("/api/", methods=["POST"]) def create_resource(resource): if resource not in TABLES: return jsonify({"error": "unknown resource"}), 404 table, cols = TABLES[resource] payload = request.get_json(force=True).get("data", {}) values = [payload.get(col, "") for col in cols] conn = db() try: cur = conn.execute(f"INSERT INTO {table} ({','.join(cols)}) VALUES ({','.join(['?'] * len(cols))})", values) conn.commit() return jsonify({"id": cur.lastrowid}) finally: conn.close() @app.route("/api//", methods=["PUT", "DELETE"]) def update_resource(resource, item_id): if resource not in TABLES: return jsonify({"error": "unknown resource"}), 404 table, cols = TABLES[resource] conn = db() try: if request.method == "DELETE": conn.execute(f"DELETE FROM {table} WHERE id=?", (item_id,)) conn.commit() return jsonify({"ok": True}) payload = request.get_json(force=True).get("data", {}) update_cols = [col for col in cols if col in payload] if update_cols: conn.execute( f"UPDATE {table} SET {','.join([col + '=?' for col in update_cols])}, updated_at=? WHERE id=?", [payload[col] for col in update_cols] + [now(), item_id], ) conn.commit() return jsonify({"ok": True}) finally: conn.close() @app.route("/api/followups//", methods=["POST"]) def add_followup(target_type, target_id): payload = request.get_json(force=True).get("data", {}) conn = db() try: conn.execute( """INSERT INTO follow_up_records (target_type,target_id,followed_at,follower,follow_up_method,content,next_action,next_follow_up_at) VALUES (?,?,?,?,?,?,?,?)""", ( target_type, target_id, payload.get("followed_at") or date.today().isoformat(), payload.get("follower") or "慰心", payload.get("follow_up_method") or "记录", payload.get("content") or "", payload.get("next_action") or "", payload.get("next_follow_up_at") or "", ), ) conn.commit() return jsonify({"ok": True}) finally: conn.close() @app.route("/api/followups/", methods=["DELETE"]) def delete_followup(followup_id): conn = db() try: cur = conn.execute("DELETE FROM follow_up_records WHERE id=?", (followup_id,)) conn.commit() if cur.rowcount == 0: return jsonify({"error": "not found"}), 404 return jsonify({"ok": True}) finally: conn.close() @app.route("/api/tasks/batch-sort", methods=["POST"]) def batch_sort_tasks(): conn = db() try: items = request.get_json(force=True).get("items", []) for item in items: conn.execute("UPDATE project_tasks SET sort_order=? WHERE id=?", (item["sort_order"], item["id"])) conn.commit() return jsonify({"ok": True}) finally: conn.close() @app.route("/api/files/upload", methods=["POST"]) def upload_file(): file = request.files["file"] module = request.form["module"] owner_id = int(request.form["owner_id"]) owner_version = request.form.get("owner_version", "") category = request.form.get("file_category", "") folder = UPLOAD_DIR / module / str(owner_id) folder.mkdir(parents=True, exist_ok=True) target = folder / file.filename file.save(target) conn = db() try: add_file_index(conn, module, owner_id, owner_version, category, target, external=False) conn.commit() return jsonify({"ok": True}) finally: conn.close() @app.route("/api/files//content") def file_content(file_id): conn = db() try: asset = one(conn, "SELECT * FROM file_assets WHERE id=?", (file_id,)) if not asset: return jsonify({"error": "not found"}), 404 path = Path(asset["file_path"]) if not path.exists(): return jsonify({"error": "missing"}), 404 return send_file(path, as_attachment=request.args.get("inline") == "false", download_name=asset["file_name"]) finally: conn.close() @app.route("/api/files/", methods=["DELETE"]) def delete_file(file_id): conn = db() try: asset = one(conn, "SELECT * FROM file_assets WHERE id=?", (file_id,)) if not asset: return jsonify({"error": "not found"}), 404 # Remove physical file from uploads/ if it was uploaded to our dir path = Path(asset["file_path"]) if path.exists() and str(UPLOAD_DIR) in str(path.resolve()): path.unlink(missing_ok=True) conn.execute("DELETE FROM file_assets WHERE id=?", (file_id,)) conn.commit() return jsonify({"ok": True}) finally: conn.close() @app.route("/api/health") def health(): return jsonify({"ok": True, "db": str(DB_PATH)}) init_db() if __name__ == "__main__": app.run(host="127.0.0.1", port=5177, debug=True)